Laporan ringkas dan eksport CSV untuk Excel

Laporan ringkas dan eksport CSV untuk Excel

Semua data yang anda kumpulkan setakat ini mempunyai satu tujuan: membantu pemilik kedai membuat keputusan.

Laporan ini dibina daripada data yang anda kumpulkan dalam bab gerakan stok. Keputusan itu jarang memerlukan senarai panjang. Ia memerlukan tiga atau empat nombor: berapa banyak barang, berapa nilainya, dan apa yang perlu dibeli semula.

Apa yang laporan perlu jawab

Sebelum menulis query, tentukan soalan. Untuk kedai kecil, tiga soalan sudah cukup:

• Berapa nilai stok yang saya pegang sekarang?
• Produk apa yang hampir habis?
• Berapa banyak pergerakan dalam seminggu terakhir?

Setiap soalan menjadi satu query, dan setiap query menjadi satu blok kecil pada halaman. Semua nombor di bawah dikira oleh MySQL, bukan oleh CFML, supaya ia kekal tepat walaupun data membesar.

Query ringkasan

<cfquery name="ringkasan" datasource="inventori">
    SELECT COUNT(*) AS jumlahProduk,
           COALESCE(SUM(stok), 0) AS jumlahUnit,
           COALESCE(SUM(stok * harga), 0) AS nilaiStok
    FROM produk
    WHERE is_active = 1
</cfquery>

COALESCE mengembalikan nilai pertama yang bukan NULL. Apabila jadual kosong, SUM memulangkan NULL, dan memaparkan NULL pada halaman kelihatan seperti pepijat. COALESCE(..., 0) menukarnya kepada sifar.

Perhatikan juga SUM(stok * harga): pendaraban berlaku sebelum penjumlahan, jadi ia mengira nilai setiap baris dahulu, kemudian menjumlahkannya.

Produk yang hampir habis

<cfquery name="hampirHabis" datasource="inventori">
    SELECT sku, nama, stok
    FROM produk
    WHERE is_active = 1 AND stok < 5
    ORDER BY stok ASC, nama
</cfquery>

Ambang 5 unit ialah keputusan perniagaan, bukan keputusan teknikal. Ia sepatutnya boleh diubah kemudian, dan cara paling mudah ialah menjadikannya tetapan dalam satu fail, bukan nombor yang tersembunyi dalam query.

ORDER BY stok ASC meletakkan yang paling kritikal di atas. nama sebagai penyusun kedua memastikan susunan stabil apabila dua produk mempunyai baki yang sama.

Gerakan tujuh hari terakhir

<cfquery name="gerakanTujuh" datasource="inventori">
    SELECT jenis, COUNT(*) AS bilangan, SUM(kuantiti) AS unit
    FROM gerakan_stok
    WHERE dicipta_pada >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    GROUP BY jenis
</cfquery>

GROUP BY jenis menghasilkan satu baris untuk masuk dan satu untuk keluar. Kemudian kita membaca dua baris itu ke dalam pemboleh ubah biasa:

<cfset unitMasuk = 0>
<cfset unitKeluar = 0>
<cfloop query="gerakanTujuh">
    <cfif jenis EQ "masuk"><cfset unitMasuk = unit></cfif>
    <cfif jenis EQ "keluar"><cfset unitKeluar = unit></cfif>
</cfloop>

Ini perlu kerana satu query tidak boleh memulangkan dua nilai bernama berbeza dalam satu baris. Alternatifnya ialah SUM(CASE WHEN jenis = 'masuk' THEN kuantiti END), yang lebih maju; gelung ini lebih mudah dibaca oleh pemula.

Eksport CSV

Pemilik kedai mahu membuka data dalam Excel, dan senarai produk yang anda bina dalam bab carian dan penapis sudah memaparkan data yang sama di skrin. CSV ialah cara paling mudah dan paling tahan lama: ia dibuka oleh Excel, LibreOffice, Google Sheets dan mana-mana alat analitik.

Fail laporan-csv.cfm membina baris, kemudian menghantarnya sebagai fail muat turun. Ia menggunakan pengawal log masuk daripada bab log masuk, kerana data stok tidak sepatutnya terbuka kepada orang luar.

<cfinclude template="includes/semakan-login.cfm">

<cfquery name="stok" datasource="inventori">
    SELECT sku, nama, harga, stok, (stok * harga) AS nilai
    FROM produk
    WHERE is_active = 1
    ORDER BY nama
</cfquery>

<cfset baris = []>
<cfset arrayAppend(baris, ["SKU", "Nama", "Harga", "Stok", "Nilai"])>
<cfloop query="stok">
    <cfset arrayAppend(baris, [
        sku,
        nama,
        numberFormat(harga, "0.00"),
        javacast("string", stok),
        numberFormat(nilai, "0.00")
    ])>
</cfloop>

<cfset csv = []>
<cfloop array="#baris#" index="b">
    <cfset arrayAppend(csv, '"' & arrayToList(b, '","') & '"')>
</cfloop>

<cfcontent type="text/csv; charset=utf-8" reset="true">
<cfheader name="Content-Disposition" value="attachment; filename=laporan-stok.csv">
<cfoutput>#chr(65279)##arrayToList(csv, chr(13) & chr(10))#</cfoutput>

Tiga baris terakhir melakukan kerja sebenar.

cfcontent menetapkan jenis kandungan dan mengosongkan apa-apa output sebelum ini. Tanpa reset="true", ruang kosong atau baris baru sebelum tag akan masuk ke dalam fail, dan Excel akan mengadu.

cfheader menetapkan nama fail. Tanpa attachment, pelayar akan memaparkan teks CSV dan bukannya memuat turunnya.

chr(65279) ialah BOM UTF-8. Ia tidak kelihatan, tetapi ia memberitahu Excel bahawa fail ini menggunakan UTF-8. Tanpanya, huruf seperti é atau simbol mata wang boleh muncul sebagai aksara pelik.

Mengapa setiap medan dibungkus dengan petikan

Kalau nama produk mengandungi koma, contohnya "Beras 5kg, gred A", fail CSV tanpa petikan akan terpecah kepada dua lajur. Pembungkusan setiap medan dengan " mengelakkan itu.

Fail CSV tidak mempunyai cara standard untuk mengelak petikan di dalam medan. Untuk data yang mungkin mengandungi ", gantikan dengan dua petikan berganda sebelum membungkus. Untuk tutorial ini, data kita tidak mengandungi petikan, tetapi biasakan menyemak.

Hasil sebenar pada rig ujian kami: 13 produk, 135 unit, nilai RM942.30.

Ujian yang saya jalankan

Pada rig dengan 13 produk aktif dan 135 unit:

• Laporan memaparkan 13 produk, 135 unit, dan nilai RM942.30. Angka ini sepadan dengan SELECT COUNT(*), SUM(stok), SUM(stok*harga) yang dijalankan terus pada MySQL.
• Tujuh hari terakhir menunjukkan 7 unit masuk dan 3 unit keluar.
• Jadual "hampir habis" memaparkan 5 produk.
• Fail CSV mempunyai 14 baris (satu tajuk dan 13 data), 655 bait, dengan Content-Type: text/csv, Content-Disposition: attachment, dan BOM di hadapan.

Dua kesilapan yang saya buat semasa menulis bab ini

Pertama, saya menulis #arrayToList(csv, ...)# di luar <cfoutput>. Fail yang dimuat turun mengandungi teks itu, bukan data. Ini kali keempat pepijat yang sama dalam siri ini, dan sebab itu saya menambah pemeriksaan automatik pada aliran kerja penulisan.

Kedua, saya menulis stok LT 5 di dalam <cfquery> dan bukannya stok < 5. Halaman gagal dengan ralat sintaks SQL. Operator LT dan GT hanya untuk <cfif>; di dalam SQL, gunakan operator SQL biasa.

Bila laporan perlu berubah

Setiap laporan mempunyai umur. Apabila kedai membuka cawangan kedua, "nilai stok" perlu dipecahkan mengikut cawangan. Apabila jumlah produk melebihi beberapa ribu, jumlah keseluruhan perlu dikira sekali sehari dan disimpan, bukan dikira semula pada setiap muat halaman.

Corak yang baik ialah membina query ringkasan sebagai satu tempat, kemudian membungkusnya dalam fungsi atau komponen apabila ia digunakan di lebih daripada satu halaman. Untuk masa ini, satu halaman sudah memadai, dan anda boleh mengubahnya tanpa menjejaskan halaman lain.

Ralat lazim

Excel memaparkan semua data dalam satu lajur. Pemisah yang digunakan bukan koma, atau medan tidak dibungkus dengan betul.

Aksara berloghat menjadi pelik. BOM tiada, atau charset=utf-8 tidak ditetapkan pada cfcontent.

Fail mengandungi HTML halaman. cfcontent reset="true" tiada, atau anda memanggil cfinclude susun atur sebelum eksport.

Nilai wang berubah sedikit. Anda menggunakan FLOAT dan bukan DECIMAL. Semak skema yang anda bina dalam bab pangkalan data; lajur harga sepatutnya DECIMAL(12,2).

Laporan menunjukkan sifar walaupun ada data. Anda menapis is_active = 1, tetapi semua produk diarkibkan.

Apa yang anda sudah ada

Anda mempunyai laporan yang boleh dibaca dalam pelayar, dan fail yang boleh dibuka dalam Excel. Itu cukup untuk pengurusan harian kedai kecil.

Bab seterusnya menutup beberapa lubang keselamatan yang sengaja ditangguhkan: token borang, had cubaan log masuk, dan pengasingan peranan.

Soalan Lazim

Boleh saya menjadualkan laporan ini dihantar setiap pagi? Boleh, dan Lucee menyediakan penjadual tugas. Untuk versi ini, muat turun manual sudah memadai.

Kenapa tidak guna PDF? PDF lebih sukar dijana dan lebih sukar dianalisis. CSV membolehkan pemilik kedai mengira sendiri, menapis, dan mencantum dengan data lain.

Boleh saya menambah lajur kategori? Boleh, sebaik sahaja jadual kategori wujud. Tambah LEFT JOIN dan satu lajur baharu.

Adakah CSV selamat daripada suntikan formula? Tidak. Kalau sel bermula dengan =, +, - atau @, Excel boleh menganggapnya sebagai formula. Untuk data daripada pengguna, tambah apostrof di hadapan nilai tersebut. Ini penting apabila data datang daripada orang luar.

Berapa banyak baris sebelum CSV menjadi perlahan? Untuk beberapa ribu baris, ia pantas. Untuk ratusan ribu, bina fail secara berperingkat atau gunakan aliran, bukan membina satu rentetan besar dalam ingatan.

Sumber

• Rujukan cfcontent: docs.lucee.org/reference/tags/content.html
• Rujukan cfheader: docs.lucee.org/reference/tags/header.html
• Fungsi agregat MySQL: dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html
• BOM UTF-8 dan Excel: learn.microsoft.com/en-us/office/