import gradio as gr import sqlite3 import datetime import os import uuid import pandas as pd # For Excel export from PIL import Image, ImageDraw, ImageFont # For JPG receipt import tempfile DB_FILE = "pos_app.db" FONT_FILE = "DejaVuSansMono.ttf" RECEIPT_WIDTH = 300 RECEIPT_LINE_HEIGHT = 15 RECEIPT_MARGIN = 10 # --- Database Functions --- def init_db(): """Initialize the database and tables if they don't exist.""" conn = sqlite3.connect(DB_FILE); cursor = conn.cursor() cursor.execute("CREATE TABLE IF NOT EXISTS products (id TEXT PRIMARY KEY, name TEXT NOT NULL, cost_price REAL NOT NULL DEFAULT 0, price REAL NOT NULL, stock INTEGER NOT NULL)") cursor.execute("CREATE TABLE IF NOT EXISTS transactions (id TEXT PRIMARY KEY, timestamp TEXT NOT NULL, total REAL NOT NULL, payment_method TEXT, amount_paid REAL NOT NULL, change REAL NOT NULL)") cursor.execute("CREATE TABLE IF NOT EXISTS transaction_items (id INTEGER PRIMARY KEY AUTOINCREMENT, transaction_id TEXT NOT NULL, product_id TEXT NOT NULL, product_name TEXT NOT NULL, cost_at_transaction REAL NOT NULL DEFAULT 0, price_at_transaction REAL NOT NULL, quantity INTEGER NOT NULL, subtotal REAL NOT NULL, FOREIGN KEY (transaction_id) REFERENCES transactions(id), FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT)") cursor.execute("CREATE TABLE IF NOT EXISTS app_state (key TEXT PRIMARY KEY, value REAL NOT NULL)") cursor.execute("INSERT OR IGNORE INTO app_state (key, value) VALUES (?, ?)", ('current_cash_balance', 0.0)) cursor.execute("INSERT OR IGNORE INTO app_state (key, value) VALUES (?, ?)", ('starting_cash_balance', 0.0)) cursor.execute("INSERT OR IGNORE INTO app_state (key, value) VALUES (?, ?)", ('qris_total_received', 0.0)) cursor.execute("INSERT OR IGNORE INTO app_state (key, value) VALUES (?, ?)", ('card_total_received', 0.0)) conn.commit(); conn.close() def db_execute(query, params=()): conn = sqlite3.connect(DB_FILE); cursor = conn.cursor() try: cursor.execute(query, params); conn.commit(); return cursor.rowcount if query.strip().upper().startswith(("DELETE", "UPDATE")) else cursor.lastrowid except sqlite3.Error as e: print(f"DB Execute Error: {e}"); conn.rollback(); return None finally: conn.close() def db_query(query, params=()): conn = sqlite3.connect(DB_FILE); conn.row_factory = sqlite3.Row; cursor = conn.cursor() try: cursor.execute(query, params); rows = cursor.fetchall(); return [dict(row) for row in rows] except sqlite3.Error as e: print(f"DB Query Error: {e}"); return [] finally: conn.close() def get_app_state(key, default_value=0.0): result = db_query("SELECT value FROM app_state WHERE key = ?", (key,)); return result[0]['value'] if result else default_value def update_app_state(key, value): db_execute("INSERT OR REPLACE INTO app_state (key, value) VALUES (?, ?)", (key, value)); print(f"App State Updated: {key} = {value}") # --- Business Logic Functions --- def get_products_for_display_lol(): products = db_query("SELECT id, name, cost_price, price, stock FROM products ORDER BY name"); return [] if not products else [[p.get(k,0.0 if k in ('cost_price','price') else (0 if k=='stock' else '')) for k in ['id','name','cost_price','price','stock']] for p in products] def _validate_product_input(pid, n, cp_s, p_s, st_s, is_new=True): if not all([pid, n, cp_s, p_s, st_s]): return None, None, None, None, None, "Err: All fields req." try: cp=float(cp_s); ps=float(p_s); si=int(st_s) if cp < 0 or ps <= 0 or si < 0: return None,None,None,None,None, "Err: Invalid numbers." if is_new and db_query("SELECT id FROM products WHERE id = ?", (pid,)): return None,None,None,None,None, f"Err: ID '{pid}' exists." return pid, n, cp, ps, si, None # Valid except: return None, None, None, None, None, "Err: Cost/Price/Stock numbers invalid." def add_product(pid, n, cp_s, p_s, st_s): pid, n, cp, ps, si, err = _validate_product_input(pid, n, cp_s, p_s, st_s, is_new=True) if err: return err, gr.update(value=get_products_for_display_lol()) res = db_execute("INSERT INTO products VALUES (?,?,?,?,?)", (pid, n, cp, ps, si)) msg = f"OK: '{n}' added." if res is not None else "Err: Failed add." return msg, gr.update(value=get_products_for_display_lol()) def update_product(pid, n, cp_s, p_s, st_s): pid, n, cp, ps, si, err = _validate_product_input(pid, n, cp_s, p_s, st_s, is_new=False) if err: return err, gr.update(value=get_products_for_display_lol()) res = db_execute("UPDATE products SET name=?, cost_price=?, price=?, stock=? WHERE id=?", (n, cp, ps, si, pid)) msg = f"OK: '{pid}' updated." if res > 0 else (f"Warn: ID '{pid}' not found." if res==0 else "Err: Update failed.") return msg, gr.update(value=get_products_for_display_lol()) def delete_product(pid): if not pid: return "Err: ID required.", gr.update(value=get_products_for_display_lol()) msg = ""; q = "DELETE FROM products WHERE id = ?" try: res = db_execute(q, (pid,)); msg = f"OK: '{pid}' deleted." if res > 0 else (f"Err: ID '{pid}' not found." if res==0 else "Err: DB error.") except sqlite3.IntegrityError: msg = f"Err: Cannot delete '{pid}', has history." except Exception as e: msg = f"Err: {e}" return msg, gr.update(value=get_products_for_display_lol()) def load_product_for_edit(pid): if not pid: return "Enter ID to load.", "", "", "", "", "" p = get_product_details(pid) if p: return (f"Loaded '{p.get('name','')}'.", gr.update(value=p.get('id','')), gr.update(value=p.get('name','')), gr.update(value=p.get('cost_price',0.0)), gr.update(value=p.get('price',0.0)), gr.update(value=p.get('stock',0))) else: return f"Err: ID '{pid}' not found.", gr.update(value=pid), *[gr.update(value="")]*4 # **FIXED SyntaxError here** def get_product_choices_initial(): """Gets product choices as list[tuple] for initial dropdown load.""" products = db_query("SELECT id, name, price, stock FROM products WHERE stock > 0 ORDER BY name") choices = [] if products: try: # Correct indentation choices = [(f"{p.get('name', 'N/A')} (Stock: {p.get('stock', 0)}, Price: {p.get('price', 0):.2f})", p.get('id')) for p in products if isinstance(p, dict) and p.get('id')] except Exception as e: # Correct indentation print(f"Error formatting initial choices: {e}") choices = [] return choices def get_product_choices_for_update(): return gr.update(choices=get_product_choices_initial()) def get_product_details(pid): return db_query("SELECT * FROM products WHERE id = ?", (pid,))[0] if isinstance(pid, str) and pid else None def generate_receipt_image(txt): try: if not os.path.exists(FONT_FILE): print(f"Font Error:'{FONT_FILE}' missing."); return None font=ImageFont.truetype(FONT_FILE, size=12) except IOError: print(f"Font Error:Cannot load '{FONT_FILE}'."); return None lines=txt.strip().split('\n'); cw=7; maxl=max(len(l) for l in lines if l); iw=max(maxl*cw+2*RECEIPT_MARGIN, RECEIPT_WIDTH); ih=(len(lines)*RECEIPT_LINE_HEIGHT)+2*RECEIPT_MARGIN img=Image.new('RGB',(int(iw),int(ih)),color='white'); draw=ImageDraw.Draw(img); y=RECEIPT_MARGIN for l in lines: draw.text((RECEIPT_MARGIN,y),l,fill='black',font=font); y+=RECEIPT_LINE_HEIGHT try: with tempfile.NamedTemporaryFile(delete=False, suffix=".jpg", prefix="rcpt_") as f: img.save(f,format="JPEG"); return f.name except Exception as e: print(f"Image Save Error:{e}"); return None def process_sale(cart_items, payment_method, amount_paid_str): current_cash = get_app_state('current_cash_balance'); current_qris = get_app_state('qris_total_received'); current_card = get_app_state('card_total_received') fmt_curr_cash=format_balance(current_cash); fmt_qris=format_balance(current_qris); fmt_card=format_balance(current_card) # Format current state for potential early return if not cart_items: return "Cart empty.", "", None, fmt_curr_cash, fmt_qris, fmt_card if not payment_method: return "Select payment method.", "", None, fmt_curr_cash, fmt_qris, fmt_card total_amount = sum(item['subtotal'] for item in cart_items); amount_paid=0.0; change=0.0; new_cash=current_cash; new_qris=current_qris; new_card=current_card if payment_method == 'Cash': try: amount_paid = float(amount_paid_str) if amount_paid_str else 0.0; assert amount_paid >= 0 except: return "Invalid cash amount.", "", None, fmt_curr_cash, fmt_qris, fmt_card if amount_paid < total_amount: return "Amount insufficient.", "", None, fmt_curr_cash, fmt_qris, fmt_card change = amount_paid - total_amount; new_cash = current_cash + amount_paid - change elif payment_method == 'QRIS': amount_paid = total_amount; change = 0.0; new_qris = current_qris + total_amount elif payment_method == 'Card': amount_paid = total_amount; change = 0.0; new_card = current_card + total_amount else: return "Invalid payment method.", "", None, fmt_curr_cash, fmt_qris, fmt_card trx_id = f"TRX-{uuid.uuid4().hex[:8].upper()}"; ts = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") conn = sqlite3.connect(DB_FILE); cursor = conn.cursor() try: cursor.execute("BEGIN TRANSACTION"); cursor.execute("INSERT INTO transactions VALUES (?,?,?,?,?,?)", (trx_id, ts, total_amount, payment_method, amount_paid, change)) items_data=[]; stock_updates=[] for item in cart_items: cost = item.get('cost_price', 0.0); items_data.append((trx_id, item['id'], item['nama'], cost, item['harga'], item['jumlah'], item['subtotal'])) stock_row=cursor.execute("SELECT stock FROM products WHERE id = ?", (item['id'],)).fetchone(); cur_stock=stock_row[0] if stock_row else 0 if cur_stock < item['jumlah']: raise ValueError(f"Stock error: {item.get('nama','Item')}") stock_updates.append((item['jumlah'], item['id'])) cursor.executemany("INSERT INTO transaction_items VALUES (NULL,?,?,?,?,?,?,?)", items_data) cursor.executemany("UPDATE products SET stock = stock - ? WHERE id = ?", stock_updates) if payment_method == 'Cash': update_app_state('current_cash_balance', new_cash) elif payment_method == 'QRIS': update_app_state('qris_total_received', new_qris) elif payment_method == 'Card': update_app_state('card_total_received', new_card) conn.commit() receipt = f"-- Receipt --\nID: {trx_id}\nT: {ts}\nM: {payment_method}\n------------------------\n{'Name':<15} {'Qty':<3} {'Price':<8} {'Subtotal':<9}\n"; for i in cart_items: receipt += f"{i.get('nama','N/A')[:15]:<15} {i.get('jumlah',0):<3} {i.get('harga',0.0):<8.2f} {i.get('subtotal',0.0):<9.2f}\n" receipt += f"------------------------\nTotal : {total_amount:>10,.2f}\n"; if payment_method=='Cash': receipt += f"Paid : {amount_paid:>10,.2f}\nChange: {change:>10,.2f}\n" receipt += "------------------------\n Thank You! \n"; img_path = generate_receipt_image(receipt) status = f"OK! Change: {change:,.2f}" if payment_method=='Cash' else "OK!"; return status, receipt, img_path, format_balance(new_cash), format_balance(new_qris), format_balance(new_card) except Exception as e: conn.rollback(); print(f"Sale Error: {e}"); return f"Error: {e}", "", None, fmt_curr_cash, fmt_qris, fmt_card finally: conn.close() def generate_excel_report(): """Generates an Excel report with sales, items, stock, and financial summary.""" try: conn=sqlite3.connect(DB_FILE) sales = pd.read_sql_query("SELECT * FROM transactions ORDER BY timestamp DESC", conn) items = pd.read_sql_query("SELECT t.timestamp, ti.*, (ti.price_at_transaction - ti.cost_at_transaction) * ti.quantity as profit FROM transaction_items ti JOIN transactions t ON ti.transaction_id = t.id ORDER BY t.timestamp DESC", conn) stock = pd.read_sql_query("SELECT id, name, cost_price, price, stock FROM products ORDER BY name", conn) start_cash=get_app_state('starting_cash_balance'); current_cash=get_app_state('current_cash_balance') qris_total=get_app_state('qris_total_received'); card_total=get_app_state('card_total_received') conn.close() total_revenue=sales['total'].sum() if not sales.empty else 0; total_cogs=items['cost_at_transaction'].multiply(items['quantity']).sum() if not items.empty else 0 gross_profit=items['profit'].sum() if not items.empty else 0; net_cash_change=current_cash-start_cash; num_trx=len(sales) summary_data = {'Metric': ['Start Cash','Current Cash','Net Cash Chg','Total QRIS Rcvd','Total Card Rcvd','Total Revenue','Total COGS','Gross Profit','Num Transactions'], 'Amount': [f"{start_cash:,.2f}",f"{current_cash:,.2f}",f"{net_cash_change:,.2f}",f"{qris_total:,.2f}",f"{card_total:,.2f}",f"{total_revenue:,.2f}",f"{total_cogs:,.2f}",f"{gross_profit:,.2f}",num_trx]} summary_df = pd.DataFrame(summary_data) with tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx", prefix="rpt_") as f: excel_path = f.name with pd.ExcelWriter(excel_path, engine='openpyxl') as writer: summary_df.to_excel(writer, sheet_name='Financial Summary', index=False) sales.to_excel(writer, sheet_name='Sales Summary', index=False) items.to_excel(writer, sheet_name='Item Details', index=False) stock.to_excel(writer, sheet_name='Stock Levels', index=False) for sheet_name in writer.sheets: # Auto-adjust columns worksheet = writer.sheets[sheet_name] for col in worksheet.columns: # --- FIXED SYNTAX --- try: # Start try on new line max_length = 0; column_letter = col[0].column_letter for cell in col: if cell.value is not None: max_length = max(max_length, len(str(cell.value))) adjusted_width = max_length + 2 worksheet.column_dimensions[column_letter].width = adjusted_width except Exception as e: # Catch errors for specific columns # print(f"ColWidth Err: {e}") # Optional debug pass print(f"Excel report generated: {excel_path}"); return excel_path except Exception as e: print(f"Excel Error: {e}"); gr.Error(f"Report Error: {e}"); return None def add_item_to_cart(pid, qty, cart): if not isinstance(pid, str) or qty <= 0: gr.Warning("Invalid input."); return cart p=get_product_details(pid); if not p: gr.Warning(f"ID '{pid}' not found."); return cart if qty > p['stock']: gr.Warning(f"Stock low: {p['name']} ({p['stock']})."); return cart found=False for item in cart: if item['id'] == p['id']: nq=item['jumlah']+qty; if nq > p['stock']: gr.Warning(f"Cart qty ({nq}) exceeds stock: {p['name']} ({p['stock']})."); return cart item['jumlah']=nq; item['subtotal']=item['harga']*nq; found=True; gr.Info(f"Qty updated."); break if not found: cart.append({"id":p['id'], "nama":p['name'], "cost_price":p['cost_price'], "harga":p['price'], "jumlah":qty, "subtotal":p['price']*qty }); gr.Info(f"Item added.") return cart def display_cart_and_total(cart_state): total=0.0; data_lol=[] if cart_state: for item in cart_state: data_lol.append([item.get(k,'N/A' if k=='nama' else 0) for k in ['nama','jumlah','harga','subtotal']]); total += item.get('subtotal', 0.0) # Return gr.update for the DataFrame value and the formatted total string return gr.update(value=data_lol), f"Total: {total:,.2f}" def format_balance(balance=None, key=None): bal_value = 0.0 if balance is not None: bal_value = balance elif key is not None: bal_value = get_app_state(key) try: return f"Rp {float(bal_value):,.2f}".replace(",", "X").replace(".", ",").replace("X", ".") except: return "Rp Error" def reset_cashier_ui(): return [], 1, "Total: 0.00", None, "", get_product_choices_for_update() # cart, qty, total, payment_method, amount_paid, product_dropdown def handle_report_generation(): excel_path=generate_excel_report(); return gr.update(value=excel_path, visible=bool(excel_path)) def handle_set_starting_cash(new_start_cash_str): try: new_start = float(new_start_cash_str); assert new_start >= 0 update_app_state('starting_cash_balance', new_start) update_app_state('current_cash_balance', new_start) update_app_state('qris_total_received', 0.0); update_app_state('card_total_received', 0.0) gr.Info("Starting cash and totals reset!"); return format_balance(new_start), format_balance(new_start), format_balance(0.0), format_balance(0.0) # Update all 4 displays except: gr.Warning("Invalid starting cash."); start_b=get_app_state('starting_cash_balance'); curr_b=get_app_state('current_cash_balance'); qris_b=get_app_state('qris_total_received'); card_b=get_app_state('card_total_received'); return format_balance(start_b), format_balance(curr_b), format_balance(qris_b), format_balance(card_b) init_db() with gr.Blocks(title="POS App v1.6 (CRUD)", theme=gr.themes.Default()) as demo: gr.Markdown("# Simple Point of Sale Application") cart_state = gr.State([]) init_start_cash=get_app_state('starting_cash_balance'); init_curr_cash=get_app_state('current_cash_balance'); init_qris=get_app_state('qris_total_received'); init_card=get_app_state('card_total_received') with gr.Tabs(): with gr.TabItem("🛒 POS / Cashier"): with gr.Row(): starting_cash_display=gr.Textbox(label="Starting Cash", value=format_balance(init_start_cash), interactive=False) current_cash_display=gr.Textbox(label="Current Cash", value=format_balance(init_curr_cash), interactive=False) qris_balance_display=gr.Textbox(label="QRIS Total", value=format_balance(init_qris), interactive=False) card_balance_display=gr.Textbox(label="Card Total", value=format_balance(init_card), interactive=False) gr.Markdown("---") with gr.Row(): with gr.Column(scale=1): product_dropdown=gr.Dropdown(label="Select Product", choices=get_product_choices_initial(), interactive=True) quantity_input=gr.Number(label="Quantity", value=1, minimum=1, step=1, interactive=True) add_to_cart_btn=gr.Button("➕ Add to Cart", variant="secondary") payment_method_input=gr.Radio(label="Payment Method", choices=["Cash", "QRIS", "Card"], value=None, interactive=True) payment_input=gr.Textbox(label="Amount Paid (Cash Only)", interactive=True, type="text") process_payment_btn=gr.Button("💰 Process Payment", variant="primary") with gr.Column(scale=2): gr.Markdown("#### Shopping Cart") cart_display=gr.DataFrame(headers=["Name", "Qty", "Price", "Subtotal"], value=[], interactive=False, datatype=["str", "number", "number", "number"]) total_display=gr.Textbox("Total: 0.00", label="Total Amount", interactive=False) gr.Markdown("---") status_transaksi=gr.Textbox(label="Status", interactive=False) receipt_display=gr.Textbox(label="Receipt (Text)", lines=8, interactive=False) receipt_jpg_file=gr.File(label="Download Receipt (JPG)", visible=False, interactive=False) with gr.TabItem("📦 Product Management"): gr.Markdown("Use Product ID + Load/Delete. Fill fields + Save New/Update.") with gr.Row(): product_id_input = gr.Textbox(label="Product ID"); load_product_btn = gr.Button("🔍 Load") with gr.Row(): product_name_input = gr.Textbox(label="Name"); product_cost_price_input = gr.Textbox(label="Cost Price") with gr.Row(): product_price_input = gr.Textbox(label="Selling Price"); product_stock_input = gr.Textbox(label="Stock") with gr.Row(): save_new_product_btn=gr.Button("💾 Save New"); update_product_btn=gr.Button("🔄 Update",variant="primary"); delete_product_btn=gr.Button("❌ Delete",variant="stop") status_produk = gr.Textbox(label="Status", interactive=False) gr.Markdown("---"); gr.Markdown("#### Products List") product_display = gr.DataFrame(headers=["ID", "Name", "Cost", "Price", "Stock"], value=get_products_for_display_lol(), interactive=False, datatype=["str", "str", "number", "number", "number"]) with gr.TabItem("📊 Reports"): gr.Markdown("Generate financial & stock reports."); generate_report_btn = gr.Button("Generate Excel Report"); excel_report_file = gr.File(label="Download Report (Excel)", visible=False, interactive=False) with gr.TabItem("⚙️ Settings"): gr.Markdown("Set starting cash (resets current cash, QRIS, Card totals)."); set_starting_cash_input = gr.Textbox(label="Set Starting Cash (Modal Awal)", placeholder="Enter amount", type="text"); set_starting_cash_btn = gr.Button("Set Starting Cash / Reset Day") # --- Connect UI Actions to Functions --- # POS Tab Actions add_to_cart_btn.click(fn=add_item_to_cart, inputs=[product_dropdown, quantity_input, cart_state], outputs=[cart_state])\ .then(fn=display_cart_and_total, inputs=[cart_state], outputs=[cart_display, total_display]) process_payment_btn.click(fn=process_sale, inputs=[cart_state, payment_method_input, payment_input], outputs=[status_transaksi, receipt_display, receipt_jpg_file, current_cash_display, qris_balance_display, card_balance_display])\ .then(lambda img_path: gr.update(visible=bool(img_path)), inputs=[receipt_jpg_file], outputs=[receipt_jpg_file])\ .then(fn=reset_cashier_ui, inputs=[], outputs=[cart_state, quantity_input, total_display, payment_method_input, payment_input, product_dropdown])\ .then(fn=display_cart_and_total, inputs=[cart_state], outputs=[cart_display, total_display]) # Clear visual cart # Product Management Tab Actions load_product_btn.click(fn=load_product_for_edit, inputs=[product_id_input], outputs=[status_produk, product_id_input, product_name_input, product_cost_price_input, product_price_input, product_stock_input]) save_new_product_btn.click(fn=add_product, inputs=[product_id_input, product_name_input, product_cost_price_input, product_price_input, product_stock_input], outputs=[status_produk, product_display])\ .then(fn=get_product_choices_for_update, inputs=[], outputs=[product_dropdown]) # Refresh dropdown update_product_btn.click(fn=update_product, inputs=[product_id_input, product_name_input, product_cost_price_input, product_price_input, product_stock_input], outputs=[status_produk, product_display])\ .then(fn=get_product_choices_for_update, inputs=[], outputs=[product_dropdown]) # Refresh dropdown delete_product_btn.click(fn=delete_product, inputs=[product_id_input], outputs=[status_produk, product_display])\ .then(fn=get_product_choices_for_update, inputs=[], outputs=[product_dropdown]) # Refresh dropdown # Reporting Tab Actions generate_report_btn.click(fn=handle_report_generation, inputs=[], outputs=[excel_report_file]) # Settings Tab Actions set_starting_cash_btn.click(fn=handle_set_starting_cash, inputs=[set_starting_cash_input], outputs=[starting_cash_display, current_cash_display, qris_balance_display, card_balance_display]) if __name__ == "__main__": init_db(); print(f"DB:'{DB_FILE}'. Check permissions."); if not os.path.exists(FONT_FILE): print(f"WARN: Font '{FONT_FILE}' missing. JPG receipt disabled.") print("Launching Gradio..."); demo.launch()