from flask import ( render_template, redirect, session, request, flash, send_file ) from extensions import app, mysql import os import uuid import pandas as pd from werkzeug.utils import secure_filename # ========================================================== # FUNGSI CEK AKSES ADMIN # ========================================================== def cek_akses_admin(): if "login" not in session: return False if session.get("role") != "admin": return False return True # ========================================================== # MASTER BARANG - ADMIN # ========================================================== @app.route("/admin/barang") def admin_barang(): if not cek_akses_admin(): return redirect("/login") cari = request.args.get("cari", "").strip() cursor = mysql.connection.cursor() if cari: cursor.execute( """ SELECT id, kode_barang, nama_barang, kategori, satuan_besar, satuan_kecil, isi_per_satuan, stok_kecil, harga_modal, harga_jual_besar, harga_jual_kecil FROM barang WHERE kode_barang LIKE %s OR nama_barang LIKE %s OR kategori LIKE %s ORDER BY nama_barang ASC """, ( f"%{cari}%", f"%{cari}%", f"%{cari}%" ) ) else: cursor.execute( """ SELECT id, kode_barang, nama_barang, kategori, satuan_besar, satuan_kecil, isi_per_satuan, stok_kecil, harga_modal, harga_jual_besar, harga_jual_kecil FROM barang ORDER BY nama_barang ASC """ ) data = cursor.fetchall() cursor.close() barang = [] for item in data: stok_kecil = item[7] isi_per_satuan = item[6] if isi_per_satuan > 0: stok_besar = stok_kecil // isi_per_satuan sisa_kecil = stok_kecil % isi_per_satuan else: stok_besar = 0 sisa_kecil = stok_kecil barang.append({ "id": item[0], "kode_barang": item[1], "nama_barang": item[2], "kategori": item[3], "satuan_besar": item[4], "satuan_kecil": item[5], "isi_per_satuan": item[6], "stok_kecil": item[7], "harga_modal": item[8], "harga_jual_besar": item[9], "harga_jual_kecil": item[10], "stok_besar": stok_besar, "sisa_kecil": sisa_kecil }) return render_template( "admin/barang.html", barang=barang, cari=cari ) # ========================================================== # TAMBAH BARANG MANUAL # ========================================================== @app.route("/admin/barang/tambah", methods=["GET", "POST"]) def tambah_barang(): if not cek_akses_admin(): return redirect("/login") if request.method == "POST": # -------------------------------------------------- # AMBIL DATA FORM # -------------------------------------------------- kode_barang = request.form.get("kode_barang", "").strip().upper() nama_barang = request.form.get("nama_barang", "").strip() kategori = request.form.get("kategori", "").strip() satuan_besar = request.form.get("satuan_besar", "").strip() satuan_kecil = request.form.get("satuan_kecil", "").strip() isi_per_satuan = request.form.get("isi_per_satuan", "").strip() harga_modal = request.form.get("harga_modal", "").strip() harga_jual_besar = request.form.get("harga_jual_besar", "").strip() harga_jual_kecil = request.form.get("harga_jual_kecil", "").strip() # -------------------------------------------------- # VALIDASI DATA WAJIB # -------------------------------------------------- if ( not kode_barang or not nama_barang or not kategori or not satuan_besar or not satuan_kecil or not isi_per_satuan or not harga_modal or not harga_jual_besar or not harga_jual_kecil ): flash( "Semua data barang wajib diisi.", "danger" ) return redirect("/admin/barang/tambah") # -------------------------------------------------- # VALIDASI ANGKA # -------------------------------------------------- try: isi_per_satuan = int(isi_per_satuan) harga_modal = float(harga_modal) harga_jual_besar = float(harga_jual_besar) harga_jual_kecil = float(harga_jual_kecil) except ValueError: flash( "Isi per satuan dan harga harus berupa angka.", "danger" ) return redirect("/admin/barang/tambah") # -------------------------------------------------- # VALIDASI ISI PER SATUAN # -------------------------------------------------- if isi_per_satuan <= 0: flash( "Isi per satuan besar harus lebih dari 0.", "danger" ) return redirect("/admin/barang/tambah") # -------------------------------------------------- # VALIDASI HARGA # -------------------------------------------------- if ( harga_modal < 0 or harga_jual_besar < 0 or harga_jual_kecil < 0 ): flash( "Harga tidak boleh bernilai negatif.", "danger" ) return redirect("/admin/barang/tambah") cursor = mysql.connection.cursor() # -------------------------------------------------- # CEK KODE BARANG DUPLIKAT # -------------------------------------------------- cursor.execute( """ SELECT id FROM barang WHERE kode_barang = %s LIMIT 1 """, (kode_barang,) ) barang_ada = cursor.fetchone() if barang_ada: cursor.close() flash( f"Kode barang {kode_barang} sudah digunakan.", "danger" ) return redirect("/admin/barang/tambah") # -------------------------------------------------- # SIMPAN BARANG # -------------------------------------------------- cursor.execute( """ INSERT INTO barang ( kode_barang, nama_barang, kategori, satuan_besar, satuan_kecil, isi_per_satuan, stok_kecil, harga_modal, harga_jual_besar, harga_jual_kecil ) VALUES ( %s, %s, %s, %s, %s, %s, 0, %s, %s, %s ) """, ( kode_barang, nama_barang, kategori, satuan_besar, satuan_kecil, isi_per_satuan, harga_modal, harga_jual_besar, harga_jual_kecil ) ) mysql.connection.commit() cursor.close() flash( f"Barang {nama_barang} berhasil ditambahkan.", "success" ) return redirect("/admin/barang") # ------------------------------------------------------ # TAMPILKAN FORM # ------------------------------------------------------ return render_template( "admin/tambah_barang.html" ) # ========================================================== # EDIT BARANG # ========================================================== @app.route("/admin/barang/edit/", methods=["GET", "POST"]) def edit_barang(id): if not cek_akses_admin(): return redirect("/login") cursor = mysql.connection.cursor() # ------------------------------------------------------ # CEK BARANG # ------------------------------------------------------ cursor.execute( """ SELECT id, kode_barang, nama_barang, kategori, satuan_besar, satuan_kecil, isi_per_satuan, stok_kecil, harga_modal, harga_jual_besar, harga_jual_kecil FROM barang WHERE id = %s LIMIT 1 """, (id,) ) data_barang = cursor.fetchone() if data_barang is None: cursor.close() flash( "Data barang tidak ditemukan.", "danger" ) return redirect("/admin/barang") # ====================================================== # JIKA FORM DISIMPAN # ====================================================== if request.method == "POST": kode_barang = request.form.get( "kode_barang", "" ).strip().upper() nama_barang = request.form.get( "nama_barang", "" ).strip() kategori = request.form.get( "kategori", "" ).strip() satuan_besar = request.form.get( "satuan_besar", "" ).strip() satuan_kecil = request.form.get( "satuan_kecil", "" ).strip() isi_per_satuan = request.form.get( "isi_per_satuan", "" ).strip() harga_modal = request.form.get( "harga_modal", "" ).strip() harga_jual_besar = request.form.get( "harga_jual_besar", "" ).strip() harga_jual_kecil = request.form.get( "harga_jual_kecil", "" ).strip() # -------------------------------------------------- # VALIDASI DATA KOSONG # -------------------------------------------------- if ( not kode_barang or not nama_barang or not kategori or not satuan_besar or not satuan_kecil or not isi_per_satuan or not harga_modal or not harga_jual_besar or not harga_jual_kecil ): cursor.close() flash( "Semua data barang wajib diisi.", "danger" ) return redirect( f"/admin/barang/edit/{id}" ) # -------------------------------------------------- # VALIDASI ANGKA # -------------------------------------------------- try: isi_per_satuan = int( isi_per_satuan ) harga_modal = float( harga_modal ) harga_jual_besar = float( harga_jual_besar ) harga_jual_kecil = float( harga_jual_kecil ) except ValueError: cursor.close() flash( "Isi per satuan dan harga harus berupa angka.", "danger" ) return redirect( f"/admin/barang/edit/{id}" ) if isi_per_satuan <= 0: cursor.close() flash( "Isi per satuan harus lebih dari 0.", "danger" ) return redirect( f"/admin/barang/edit/{id}" ) if ( harga_modal < 0 or harga_jual_besar < 0 or harga_jual_kecil < 0 ): cursor.close() flash( "Harga tidak boleh bernilai negatif.", "danger" ) return redirect( f"/admin/barang/edit/{id}" ) # -------------------------------------------------- # CEK KODE BARANG DUPLIKAT # Tetapi abaikan barang yang sedang diedit # -------------------------------------------------- cursor.execute( """ SELECT id FROM barang WHERE kode_barang = %s AND id != %s LIMIT 1 """, ( kode_barang, id ) ) kode_ada = cursor.fetchone() if kode_ada: cursor.close() flash( f"Kode barang {kode_barang} sudah digunakan barang lain.", "danger" ) return redirect( f"/admin/barang/edit/{id}" ) # -------------------------------------------------- # UPDATE DATA BARANG # -------------------------------------------------- cursor.execute( """ UPDATE barang SET kode_barang = %s, nama_barang = %s, kategori = %s, satuan_besar = %s, satuan_kecil = %s, isi_per_satuan = %s, harga_modal = %s, harga_jual_besar = %s, harga_jual_kecil = %s WHERE id = %s """, ( kode_barang, nama_barang, kategori, satuan_besar, satuan_kecil, isi_per_satuan, harga_modal, harga_jual_besar, harga_jual_kecil, id ) ) mysql.connection.commit() cursor.close() flash( f"Data barang {nama_barang} berhasil diperbarui.", "success" ) return redirect("/admin/barang") # ====================================================== # DATA UNTUK FORM EDIT # ====================================================== barang = { "id": data_barang[0], "kode_barang": data_barang[1], "nama_barang": data_barang[2], "kategori": data_barang[3], "satuan_besar": data_barang[4], "satuan_kecil": data_barang[5], "isi_per_satuan": data_barang[6], "stok_kecil": data_barang[7], "harga_modal": data_barang[8], "harga_jual_besar": data_barang[9], "harga_jual_kecil": data_barang[10] } cursor.close() return render_template( "admin/edit_barang.html", barang=barang ) # ========================================================== # HAPUS BARANG # ========================================================== @app.route("/admin/barang/hapus/", methods=["POST"]) def hapus_barang(id): if not cek_akses_admin(): return redirect("/login") cursor = mysql.connection.cursor() # ------------------------------------------------------ # CEK BARANG # ------------------------------------------------------ cursor.execute( """ SELECT id, nama_barang FROM barang WHERE id = %s LIMIT 1 """, (id,) ) barang = cursor.fetchone() if barang is None: cursor.close() flash( "Data barang tidak ditemukan.", "danger" ) return redirect("/admin/barang") nama_barang = barang[1] # ------------------------------------------------------ # CEK APAKAH SUDAH ADA TRANSAKSI MASUK # ------------------------------------------------------ cursor.execute( """ SELECT COUNT(*) FROM detail_transaksi_masuk WHERE barang_id = %s """, (id,) ) total_masuk = cursor.fetchone()[0] # ------------------------------------------------------ # CEK APAKAH SUDAH ADA TRANSAKSI KELUAR # ------------------------------------------------------ cursor.execute( """ SELECT COUNT(*) FROM detail_transaksi_keluar WHERE barang_id = %s """, (id,) ) total_keluar = cursor.fetchone()[0] # ------------------------------------------------------ # CEK APAKAH SUDAH ADA HASIL PREDIKSI # ------------------------------------------------------ cursor.execute( """ SELECT COUNT(*) FROM hasil_prediksi WHERE barang_id = %s """, (id,) ) total_prediksi = cursor.fetchone()[0] # ------------------------------------------------------ # JIKA SUDAH PUNYA HISTORI, JANGAN DIHAPUS # ------------------------------------------------------ if total_masuk > 0 or total_keluar > 0 or total_prediksi > 0: cursor.close() flash( "Barang tidak dapat dihapus karena sudah memiliki histori transaksi atau data prediksi.", "danger" ) return redirect("/admin/barang") # ------------------------------------------------------ # HAPUS BARANG # ------------------------------------------------------ cursor.execute( """ DELETE FROM barang WHERE id = %s """, (id,) ) mysql.connection.commit() cursor.close() flash( f"Barang {nama_barang} berhasil dihapus.", "success" ) return redirect("/admin/barang") # ========================================================== # IMPORT MASTER BARANG DARI EXCEL # TAHAP 1: UPLOAD + VALIDASI + PREVIEW # ========================================================== @app.route("/admin/barang/import", methods=["GET", "POST"]) def import_barang(): if not cek_akses_admin(): return redirect("/login") # ------------------------------------------------------ # JIKA HALAMAN DIBUKA BIASA # ------------------------------------------------------ if request.method == "GET": return render_template( "admin/import_barang.html", preview=None, nama_file=None, ada_error=False ) # ------------------------------------------------------ # CEK FILE # ------------------------------------------------------ if "file_excel" not in request.files: flash( "Silakan pilih file Excel terlebih dahulu.", "danger" ) return redirect("/admin/barang/import") file = request.files["file_excel"] if file.filename == "": flash( "File Excel belum dipilih.", "danger" ) return redirect("/admin/barang/import") # ------------------------------------------------------ # VALIDASI FORMAT FILE # ------------------------------------------------------ nama_asli = secure_filename(file.filename) if not nama_asli.lower().endswith(".xlsx"): flash( "Format file harus .xlsx", "danger" ) return redirect("/admin/barang/import") # ------------------------------------------------------ # SIMPAN FILE SEMENTARA # ------------------------------------------------------ base_dir = os.path.dirname( os.path.dirname( os.path.abspath(__file__) ) ) upload_folder = os.path.join( base_dir, "uploads" ) os.makedirs( upload_folder, exist_ok=True ) nama_file = ( f"{uuid.uuid4().hex}_" f"{nama_asli}" ) lokasi_file = os.path.join( upload_folder, nama_file ) file.save(lokasi_file) # ------------------------------------------------------ # BACA EXCEL # ------------------------------------------------------ try: df = pd.read_excel( lokasi_file, sheet_name="Master Barang", engine="openpyxl" ) except Exception as e: if os.path.exists(lokasi_file): os.remove(lokasi_file) flash( "File Excel tidak dapat dibaca. " "Pastikan menggunakan template Master Barang yang benar.", "danger" ) return redirect("/admin/barang/import") # ------------------------------------------------------ # KOLOM WAJIB # ------------------------------------------------------ kolom_wajib = [ "kode_barang", "nama_barang", "kategori", "satuan_besar", "satuan_kecil", "isi_per_satuan", "harga_modal", "harga_jual_besar", "harga_jual_kecil" ] # Hilangkan spasi pada nama kolom df.columns = [ str(kolom).strip() for kolom in df.columns ] kolom_tidak_ada = [ kolom for kolom in kolom_wajib if kolom not in df.columns ] if kolom_tidak_ada: if os.path.exists(lokasi_file): os.remove(lokasi_file) flash( "Format Excel tidak sesuai. " "Kolom berikut tidak ditemukan: " + ", ".join(kolom_tidak_ada), "danger" ) return redirect("/admin/barang/import") # ------------------------------------------------------ # AMBIL HANYA KOLOM YANG DIPERLUKAN # ------------------------------------------------------ df = df[kolom_wajib] # Hapus baris yang seluruhnya kosong df = df.dropna( how="all" ) if df.empty: if os.path.exists(lokasi_file): os.remove(lokasi_file) flash( "File Excel tidak memiliki data barang.", "danger" ) return redirect("/admin/barang/import") # ------------------------------------------------------ # PERSIAPAN VALIDASI # ------------------------------------------------------ preview = [] ada_error = False cursor = mysql.connection.cursor() kode_dalam_file = set() # ------------------------------------------------------ # VALIDASI SETIAP BARIS # ------------------------------------------------------ for index, row in df.iterrows(): nomor_baris = index + 2 error_baris = [] # ------------------------------ # AMBIL NILAI # ------------------------------ kode_barang = ( "" if pd.isna(row["kode_barang"]) else str(row["kode_barang"]).strip().upper() ) nama_barang = ( "" if pd.isna(row["nama_barang"]) else str(row["nama_barang"]).strip() ) kategori = ( "" if pd.isna(row["kategori"]) else str(row["kategori"]).strip() ) satuan_besar = ( "" if pd.isna(row["satuan_besar"]) else str(row["satuan_besar"]).strip() ) satuan_kecil = ( "" if pd.isna(row["satuan_kecil"]) else str(row["satuan_kecil"]).strip() ) # ------------------------------ # VALIDASI DATA TEXT # ------------------------------ if not kode_barang: error_baris.append( "Kode barang kosong" ) if not nama_barang: error_baris.append( "Nama barang kosong" ) if not kategori: error_baris.append( "Kategori kosong" ) if not satuan_besar: error_baris.append( "Satuan besar kosong" ) if not satuan_kecil: error_baris.append( "Satuan kecil kosong" ) # ------------------------------ # VALIDASI ANGKA # ------------------------------ try: isi_per_satuan = int( row["isi_per_satuan"] ) if isi_per_satuan <= 0: raise ValueError() except Exception: isi_per_satuan = 0 error_baris.append( "Isi per satuan harus angka lebih dari 0" ) try: harga_modal = float( row["harga_modal"] ) if harga_modal < 0: raise ValueError() except Exception: harga_modal = 0 error_baris.append( "Harga modal tidak valid" ) try: harga_jual_besar = float( row["harga_jual_besar"] ) if harga_jual_besar < 0: raise ValueError() except Exception: harga_jual_besar = 0 error_baris.append( "Harga jual besar tidak valid" ) try: harga_jual_kecil = float( row["harga_jual_kecil"] ) if harga_jual_kecil < 0: raise ValueError() except Exception: harga_jual_kecil = 0 error_baris.append( "Harga jual kecil tidak valid" ) # ------------------------------ # CEK KODE DUPLIKAT DI EXCEL # ------------------------------ if kode_barang: if kode_barang in kode_dalam_file: error_baris.append( "Kode barang duplikat di file Excel" ) else: kode_dalam_file.add( kode_barang ) # ------------------------------ # CEK KODE SUDAH ADA DI DATABASE # ------------------------------ if kode_barang: cursor.execute( """ SELECT id FROM barang WHERE kode_barang = %s LIMIT 1 """, (kode_barang,) ) barang_ada = cursor.fetchone() if barang_ada: error_baris.append( "Kode barang sudah ada di database" ) # ------------------------------ # STATUS BARIS # ------------------------------ if error_baris: status = "Error" ada_error = True else: status = "Valid" # ------------------------------ # DATA PREVIEW # ------------------------------ preview.append({ "nomor_baris": nomor_baris, "kode_barang": kode_barang, "nama_barang": nama_barang, "kategori": kategori, "satuan_besar": satuan_besar, "satuan_kecil": satuan_kecil, "isi_per_satuan": isi_per_satuan, "harga_modal": harga_modal, "harga_jual_besar": harga_jual_besar, "harga_jual_kecil": harga_jual_kecil, "status": status, "error": ", ".join(error_baris) }) cursor.close() # ------------------------------------------------------ # TAMPILKAN PREVIEW # ------------------------------------------------------ return render_template( "admin/import_barang.html", preview=preview, nama_file=nama_file, ada_error=ada_error ) # ========================================================== # SIMPAN HASIL IMPORT MASTER BARANG # ========================================================== @app.route("/admin/barang/import/simpan", methods=["POST"]) def simpan_import_barang(): if not cek_akses_admin(): return redirect("/login") # ------------------------------------------------------ # AMBIL NAMA FILE DARI FORM PREVIEW # ------------------------------------------------------ nama_file = request.form.get("nama_file", "").strip() if not nama_file: flash( "File import tidak ditemukan. Silakan upload ulang.", "danger" ) return redirect("/admin/barang/import") # ------------------------------------------------------ # AMANKAN NAMA FILE # ------------------------------------------------------ nama_file = os.path.basename(nama_file) base_dir = os.path.dirname( os.path.dirname( os.path.abspath(__file__) ) ) lokasi_file = os.path.join( base_dir, "uploads", nama_file ) # ------------------------------------------------------ # CEK FILE MASIH ADA # ------------------------------------------------------ if not os.path.exists(lokasi_file): flash( "File import sudah tidak tersedia. Silakan upload ulang.", "danger" ) return redirect("/admin/barang/import") # ------------------------------------------------------ # BACA ULANG FILE EXCEL # ------------------------------------------------------ try: df = pd.read_excel( lokasi_file, sheet_name="Master Barang", engine="openpyxl" ) except Exception: flash( "File Excel gagal dibaca. Silakan upload ulang.", "danger" ) return redirect("/admin/barang/import") # ------------------------------------------------------ # KOLOM WAJIB # ------------------------------------------------------ kolom_wajib = [ "kode_barang", "nama_barang", "kategori", "satuan_besar", "satuan_kecil", "isi_per_satuan", "harga_modal", "harga_jual_besar", "harga_jual_kecil" ] df.columns = [ str(kolom).strip() for kolom in df.columns ] # ------------------------------------------------------ # CEK STRUKTUR KOLOM # ------------------------------------------------------ for kolom in kolom_wajib: if kolom not in df.columns: flash( f"Kolom {kolom} tidak ditemukan. Import dibatalkan.", "danger" ) return redirect("/admin/barang/import") df = df[kolom_wajib] df = df.dropna( how="all" ) if df.empty: flash( "Tidak ada data barang yang dapat disimpan.", "danger" ) return redirect("/admin/barang/import") # ------------------------------------------------------ # VALIDASI ULANG SEMUA DATA # ------------------------------------------------------ data_simpan = [] kode_dalam_file = set() cursor = mysql.connection.cursor() try: for index, row in df.iterrows(): nomor_baris = index + 2 # ------------------------------------------------ # DATA TEXT # ------------------------------------------------ kode_barang = ( "" if pd.isna(row["kode_barang"]) else str(row["kode_barang"]).strip().upper() ) nama_barang = ( "" if pd.isna(row["nama_barang"]) else str(row["nama_barang"]).strip() ) kategori = ( "" if pd.isna(row["kategori"]) else str(row["kategori"]).strip() ) satuan_besar = ( "" if pd.isna(row["satuan_besar"]) else str(row["satuan_besar"]).strip() ) satuan_kecil = ( "" if pd.isna(row["satuan_kecil"]) else str(row["satuan_kecil"]).strip() ) # ------------------------------------------------ # CEK DATA WAJIB # ------------------------------------------------ if ( not kode_barang or not nama_barang or not kategori or not satuan_besar or not satuan_kecil ): raise ValueError( f"Data wajib pada baris Excel {nomor_baris} belum lengkap." ) # ------------------------------------------------ # KONVERSI ANGKA # ------------------------------------------------ try: isi_per_satuan = int( row["isi_per_satuan"] ) harga_modal = float( row["harga_modal"] ) harga_jual_besar = float( row["harga_jual_besar"] ) harga_jual_kecil = float( row["harga_jual_kecil"] ) except Exception: raise ValueError( f"Data angka pada baris Excel {nomor_baris} tidak valid." ) # ------------------------------------------------ # VALIDASI NILAI # ------------------------------------------------ if isi_per_satuan <= 0: raise ValueError( f"Isi per satuan pada baris Excel " f"{nomor_baris} harus lebih dari 0." ) if ( harga_modal < 0 or harga_jual_besar < 0 or harga_jual_kecil < 0 ): raise ValueError( f"Harga pada baris Excel " f"{nomor_baris} tidak boleh negatif." ) # ------------------------------------------------ # CEK DUPLIKAT DALAM FILE # ------------------------------------------------ if kode_barang in kode_dalam_file: raise ValueError( f"Kode {kode_barang} duplikat " f"di dalam file Excel." ) kode_dalam_file.add( kode_barang ) # ------------------------------------------------ # CEK DUPLIKAT DATABASE # ------------------------------------------------ cursor.execute( """ SELECT id FROM barang WHERE kode_barang = %s LIMIT 1 """, (kode_barang,) ) if cursor.fetchone(): raise ValueError( f"Kode barang {kode_barang} " f"sudah ada di database." ) # ------------------------------------------------ # MASUKKAN KE LIST SEMENTARA # BELUM INSERT DATABASE # ------------------------------------------------ data_simpan.append({ "kode_barang": kode_barang, "nama_barang": nama_barang, "kategori": kategori, "satuan_besar": satuan_besar, "satuan_kecil": satuan_kecil, "isi_per_satuan": isi_per_satuan, "harga_modal": harga_modal, "harga_jual_besar": harga_jual_besar, "harga_jual_kecil": harga_jual_kecil }) # ================================================== # SEMUA VALID → MULAI INSERT # ================================================== for item in data_simpan: cursor.execute( """ INSERT INTO barang ( kode_barang, nama_barang, kategori, satuan_besar, satuan_kecil, isi_per_satuan, stok_kecil, harga_modal, harga_jual_besar, harga_jual_kecil ) VALUES ( %s, %s, %s, %s, %s, %s, 0, %s, %s, %s ) """, ( item["kode_barang"], item["nama_barang"], item["kategori"], item["satuan_besar"], item["satuan_kecil"], item["isi_per_satuan"], item["harga_modal"], item["harga_jual_besar"], item["harga_jual_kecil"] ) ) # -------------------------------------------------- # SEMUA INSERT BERHASIL # -------------------------------------------------- mysql.connection.commit() jumlah_data = len( data_simpan ) cursor.close() # -------------------------------------------------- # HAPUS FILE SEMENTARA # -------------------------------------------------- try: if os.path.exists(lokasi_file): os.remove(lokasi_file) except Exception: pass flash( f"{jumlah_data} data barang berhasil diimport ke Master Barang.", "success" ) return redirect("/admin/barang") # ====================================================== # ERROR VALIDASI # ====================================================== except ValueError as e: mysql.connection.rollback() cursor.close() flash( str(e), "danger" ) return redirect("/admin/barang/import") # ====================================================== # ERROR DATABASE / ERROR LAINNYA # ====================================================== except Exception as e: mysql.connection.rollback() cursor.close() print( "ERROR IMPORT BARANG:", e ) flash( "Terjadi kesalahan saat menyimpan data. " "Seluruh proses import dibatalkan.", "danger" ) return redirect("/admin/barang/import") # ========================================================== # EXPORT MASTER BARANG KE EXCEL # ========================================================== @app.route("/admin/barang/export") def export_barang(): if not cek_akses_admin(): return redirect("/login") cursor = mysql.connection.cursor() cursor.execute( """ SELECT kode_barang, nama_barang, kategori, satuan_besar, satuan_kecil, isi_per_satuan, stok_kecil, harga_modal, harga_jual_besar, harga_jual_kecil FROM barang ORDER BY nama_barang ASC """ ) data = cursor.fetchall() cursor.close() # ------------------------------------------------------ # CEK DATA # ------------------------------------------------------ if not data: flash( "Belum ada data barang yang dapat diexport.", "warning" ) return redirect("/admin/barang") # ------------------------------------------------------ # SIAPKAN DATA EXCEL # ------------------------------------------------------ data_excel = [] for item in data: isi_per_satuan = int(item[5]) stok_kecil = int(item[6]) stok_besar = ( stok_kecil // isi_per_satuan if isi_per_satuan > 0 else 0 ) sisa_kecil = ( stok_kecil % isi_per_satuan if isi_per_satuan > 0 else stok_kecil ) data_excel.append({ "Kode Barang": item[0], "Nama Barang": item[1], "Kategori": item[2], "Satuan Besar": item[3], "Satuan Kecil": item[4], "Isi per Satuan Besar": isi_per_satuan, "Stok Satuan Besar": stok_besar, "Sisa Stok Kecil": sisa_kecil, "Total Stok Satuan Kecil": stok_kecil, "Harga Modal": float(item[7]), "Harga Jual Besar": float(item[8]), "Harga Jual Kecil": float(item[9]) }) df = pd.DataFrame( data_excel ) # ------------------------------------------------------ # LOKASI FILE # ------------------------------------------------------ base_dir = os.path.dirname( os.path.dirname( os.path.abspath(__file__) ) ) export_folder = os.path.join( base_dir, "uploads" ) os.makedirs( export_folder, exist_ok=True ) nama_file = "Master_Barang_Toko_Grosir_Cucu.xlsx" lokasi_file = os.path.join( export_folder, nama_file ) # ------------------------------------------------------ # BUAT FILE EXCEL # ------------------------------------------------------ with pd.ExcelWriter( lokasi_file, engine="openpyxl" ) as writer: df.to_excel( writer, sheet_name="Master Barang", index=False ) worksheet = writer.sheets[ "Master Barang" ] # ---------------------------------------------- # FREEZE HEADER # ---------------------------------------------- worksheet.freeze_panes = "A2" # ---------------------------------------------- # FORMAT HEADER # ---------------------------------------------- from openpyxl.styles import ( Font, PatternFill, Alignment ) header_fill = PatternFill( fill_type="solid", fgColor="198754" ) header_font = Font( color="FFFFFF", bold=True ) for cell in worksheet[1]: cell.fill = header_fill cell.font = header_font cell.alignment = Alignment( horizontal="center", vertical="center" ) # ---------------------------------------------- # FORMAT HARGA # ---------------------------------------------- for row in range( 2, worksheet.max_row + 1 ): worksheet.cell( row=row, column=10 ).number_format = '#,##0' worksheet.cell( row=row, column=11 ).number_format = '#,##0' worksheet.cell( row=row, column=12 ).number_format = '#,##0' # ---------------------------------------------- # LEBAR KOLOM # ---------------------------------------------- lebar_kolom = { "A": 15, "B": 28, "C": 18, "D": 16, "E": 16, "F": 22, "G": 20, "H": 18, "I": 24, "J": 18, "K": 20, "L": 20 } for kolom, lebar in lebar_kolom.items(): worksheet.column_dimensions[ kolom ].width = lebar # ---------------------------------------------- # AUTO FILTER # ---------------------------------------------- worksheet.auto_filter.ref = ( worksheet.dimensions ) # ------------------------------------------------------ # DOWNLOAD # ------------------------------------------------------ return send_file( lokasi_file, as_attachment=True, download_name=nama_file )