from flask import Blueprint, request, jsonify, send_from_directory from flask_jwt_extended import jwt_required from db.db import get_db_connection from config.auth import role_required from datetime import datetime from datetime import timedelta from decimal import Decimal from datetime import date import traceback from mysql.connector import Error report_bp = Blueprint('report', __name__) @report_bp.route('/cash_today_summary', methods=['GET']) @jwt_required() @role_required('admin','cashier') def cash_today_summary(): try: conn = get_db_connection() cursor = conn.cursor() today = date.today() # Doctor Fees (from received invoices) cursor.execute(""" SELECT SUM(ds.total) FROM doctor_charges_sale ds JOIN invoice_sale i ON ds.invoice_id = i.invoice_id WHERE DATE(i.sale_date) = %s AND i.status = 'received' """, (today,)) doctor_fees = cursor.fetchone()[0] or Decimal(0) # Medicine Fees (from received invoices) cursor.execute(""" SELECT SUM(sp.total) FROM sale_products sp JOIN invoice_sale i ON sp.invoice_id = i.invoice_id WHERE DATE(i.sale_date) = %s AND i.status = 'received' """, (today,)) medicine_fees = cursor.fetchone()[0] or Decimal(0) # Total Sale total_sale = float(doctor_fees) + float(medicine_fees) # Today's Expenses cursor.execute(""" SELECT SUM(amount) FROM expenses WHERE DATE(date) = %s """, (today,)) total_expense = cursor.fetchone()[0] or Decimal(0) # Cash Payment (received invoices only) cursor.execute(""" SELECT SUM(invoiceTotal) FROM invoice_sale WHERE payment_method = 'paid' AND DATE(sale_date) = %s AND status = 'received' """, (today,)) cash_payment_total = cursor.fetchone()[0] or Decimal(0) # Today's Return Amount from sale_return table cursor.execute(""" SELECT SUM(total_return_amount) FROM sale_return WHERE DATE(return_date) = %s """, (today,)) total_return_amount = cursor.fetchone()[0] or Decimal(0) # Final Total Cash = Sales - Expenses - Returns total_cash = total_sale - float(total_expense) - float(total_return_amount) summary = { "date": today.strftime('%Y-%m-%d'), "doctor_fees": round(float(doctor_fees), 2), "medicine_fees": round(float(medicine_fees), 2), "total_sale": round(total_sale, 2), "cash_payment": round(float(cash_payment_total), 2), "total_expense": round(float(total_expense), 2), "total_return_amount": round(float(total_return_amount), 2), "total_cash": round(total_cash, 2) } cursor.close() conn.close() return jsonify(summary), 200 except Exception as e: return jsonify({"error": str(e)}), 500 @report_bp.route('/get_close_cash_summary', methods=['GET']) @jwt_required() @role_required('admin','cashier') def get_close_cash_summary(): try: conn = get_db_connection() cursor = conn.cursor() # Doctor fees cursor.execute("SELECT SUM(ds.total) FROM doctor_charges_sale ds") doctor_fees = cursor.fetchone()[0] or Decimal(0) # Medicine fees cursor.execute("SELECT SUM(sp.total) FROM sale_products sp") medicine_fees = cursor.fetchone()[0] or Decimal(0) # Total sale total_sale = float(doctor_fees) + float(medicine_fees) # Expenses cursor.execute("SELECT SUM(amount) FROM expenses") total_expense = cursor.fetchone()[0] or Decimal(0) # Total Cash total_cash = total_sale - float(total_expense) # Paid invoices cursor.execute(""" SELECT SUM(invoiceTotal) FROM invoice_sale WHERE payment_method = 'paid' """) cash_payment_total = cursor.fetchone()[0] or Decimal(0) # Register closed flag register_closed = total_cash < 1 summary = { "doctor_fees": float(doctor_fees), "medicine_fees": float(medicine_fees), "total_sale": float(total_sale), "total_expense": float(total_expense), "total_cash": float(total_cash), "cash_payment_total": float(cash_payment_total), "register_closed": register_closed } cursor.close() conn.close() return jsonify(summary), 200 except Exception as e: return jsonify({"error": str(e)}), 500 # ✅ CORRECTED: Using new schema columns @report_bp.route('/get_register_status', methods=['GET']) @jwt_required() @role_required('admin','cashier') def get_register_status(): user_id = request.args.get('user_id') conn = get_db_connection() cursor = conn.cursor() # ✅ FIXED: opening_cash instead of cash_in_hand cursor.execute(""" SELECT id, login_time, opening_cash FROM close_register_logs WHERE user_id = %s AND status = 'open' ORDER BY id DESC LIMIT 1 """, (user_id,)) row = cursor.fetchone() cursor.close() conn.close() if row: return jsonify({ 'success': True, 'data': { 'register_id': row[0], 'login_time': row[1].strftime('%Y-%m-%d %H:%M:%S'), 'opening_cash': float(row[2]) # ✅ Changed from cash_in_hand } }) else: return jsonify({'success': False, 'message': 'No open register found'}) # ✅ CORRECTED: Using new cash_transactions table @report_bp.route('/add_cash_in_hand', methods=['POST']) @jwt_required() @role_required('admin', 'cashier') def add_cash_in_hand(): try: data = request.json user_id = data.get('user_id') amount = data.get('cash_in_hand') # amount to add transaction_type = data.get('transaction_type', 'add') # 'add' or 'remove' reason = data.get('reason') note = data.get('note') if user_id is None or amount is None: return jsonify({'success': False, 'message': 'Missing required fields'}), 400 conn = get_db_connection() cursor = conn.cursor() # ✅ Check if register is open cursor.execute(""" SELECT id, store_id, opening_cash, total_cash_added, total_cash_removed FROM close_register_logs WHERE user_id=%s AND status='open' ORDER BY id DESC LIMIT 1 """, (user_id,)) register = cursor.fetchone() if not register: cursor.close() conn.close() return jsonify({ 'success': False, 'message': 'No open register found. Please open register first.' }), 400 register_id = register[0] store_id = register[1] total_cash_added = float(register[3] or 0) total_cash_removed = float(register[4] or 0) # ✅ Insert into cash_transactions table cursor.execute(""" INSERT INTO cash_transactions ( register_log_id, user_id, store_id, transaction_type, amount, reason, note, created_by ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) """, (register_id, user_id, store_id, transaction_type, amount, reason, note, user_id)) # ✅ Update close_register_logs totals if transaction_type == 'add': new_total_added = total_cash_added + float(amount) cursor.execute(""" UPDATE close_register_logs SET total_cash_added = %s WHERE id = %s """, (new_total_added, register_id)) elif transaction_type == 'remove': new_total_removed = total_cash_removed + float(amount) cursor.execute(""" UPDATE close_register_logs SET total_cash_removed = %s WHERE id = %s """, (new_total_removed, register_id)) conn.commit() cursor.close() conn.close() return jsonify({ 'success': True, 'message': f'Cash {transaction_type} recorded successfully' }), 200 except Exception as e: return jsonify({'success': False, 'message': str(e)}), 500 # ✅ CORRECTED: Using new schema columns @report_bp.route('/open_register', methods=['POST']) @jwt_required() @role_required('admin','cashier') def open_register(): try: data = request.json user_id = data.get('user_id') opening_cash = data.get('cash_in_hand') # ✅ Will be stored as opening_cash if not user_id or opening_cash is None: return jsonify({ 'success': False, 'message': 'User ID and opening cash are required' }), 400 conn = get_db_connection() if not conn: return jsonify({'success': False, 'message': 'Database connection failed'}), 500 cursor = conn.cursor(dictionary=True) # Get user's store_id cursor.execute("SELECT store_id FROM users WHERE id = %s", (user_id,)) user = cursor.fetchone() if not user: cursor.close() conn.close() return jsonify({'success': False, 'message': 'User not found'}), 404 store_id = user['store_id'] if not store_id: cursor.close() conn.close() return jsonify({ 'success': False, 'message': 'User is not assigned to any store' }), 400 # Check if already an open register cursor.execute(""" SELECT id FROM close_register_logs WHERE user_id = %s AND status = 'open' ORDER BY id DESC LIMIT 1 """, (user_id,)) existing = cursor.fetchone() if existing: cursor.close() conn.close() return jsonify({ 'success': False, 'message': 'Register already open for this user' }), 409 # ✅ Insert with new schema columns cursor.execute(""" INSERT INTO close_register_logs ( store_id, user_id, login_time, opening_cash, opening_balance, status ) VALUES (%s, %s, %s, %s, %s, 'open') """, (store_id, user_id, datetime.now(), opening_cash, opening_cash)) conn.commit() register_id = cursor.lastrowid # ✅ Create opening transaction record cursor.execute(""" INSERT INTO cash_transactions ( register_log_id, user_id, store_id, transaction_type, amount, reason, created_by ) VALUES (%s, %s, %s, 'opening', %s, 'Register opened', %s) """, (register_id, user_id, store_id, opening_cash, user_id)) conn.commit() cursor.close() conn.close() return jsonify({ 'success': True, 'message': 'Register opened successfully', 'register_id': register_id }), 200 except Exception as e: print(f"❌ Error opening register: {e}") return jsonify({ 'success': False, 'message': f'Error opening register: {str(e)}' }), 500 # ✅ CORRECTED: Using new schema columns @report_bp.route('/check_register_open', methods=['GET']) @jwt_required() @role_required('admin','cashier') def check_register_open(): try: user_id = request.args.get('user_id') if not user_id: return jsonify({'error': 'User ID required'}), 400 conn = get_db_connection() cursor = conn.cursor(dictionary=True) # ✅ FIXED: opening_cash instead of cash_in_hand cursor.execute(""" SELECT id, store_id, opening_cash, login_time FROM close_register_logs WHERE user_id = %s AND status = 'open' ORDER BY id DESC LIMIT 1 """, (user_id,)) register = cursor.fetchone() cursor.close() conn.close() if register: return jsonify({ 'open': True, 'register': register }), 200 else: return jsonify({'open': False}), 200 except Exception as e: print(f"❌ Error checking register: {e}") return jsonify({'error': str(e)}), 500 # @report_bp.route('/close_register', methods=['POST']) # @jwt_required() # @role_required('admin','user') # def close_register(): # data = request.json # user_id = data['user_id'] # cash_in_hand_while_closing = data['cash_in_hand_while_closing'] # total_cash = data['total_cash'] # total_sale_amount = data['total_sale_amount'] # doctor_fees = data['doctor_fees'] # medical_fees = data['medical_fees'] # sale_return_amount = data['sale_return_amount'] # total_expense = data['total_expense'] # notes = data.get('notes', '') # conn = get_db_connection() # cursor = conn.cursor() # cursor.execute(""" # UPDATE close_register_logs # SET logout_time=%s, # cash_in_hand_while_closing=%s, # total_cash=%s, # total_sale_amount=%s, # doctor_fees=%s, # medical_fees=%s, # sale_return_amount=%s, # total_expense=%s, # notes=%s, # status='close' # WHERE user_id=%s AND logout_time IS NULL # """, ( # datetime.now(), # cash_in_hand_while_closing, # total_cash, # total_sale_amount, # doctor_fees, # medical_fees, # sale_return_amount, # total_expense, # notes, # user_id # )) # conn.commit() # cursor.close() # conn.close() # return jsonify({'success': True, 'message': 'Register closed successfully'}) # @report_bp.route('/get_cash_summary', methods=['GET']) # @jwt_required() # @role_required('admin', 'user') # def get_cash_summary(): # try: # user_id = request.args.get('user_id') # if not user_id: # return jsonify({'error': 'Missing user_id'}), 400 # conn = get_db_connection() # cursor = conn.cursor() # # ✅ Get open register session # cursor.execute(""" # SELECT id, cash_in_hand, login_time, logout_time, status # FROM close_register_logs # WHERE user_id = %s AND status = 'open' # """, (user_id,)) # register = cursor.fetchone() # if not register: # return jsonify({'error': 'Open register not found'}), 404 # register_id, cash_in_hand, login_time, logout_time, status = register # cash_in_hand = float(cash_in_hand or 0.0) # end_time = logout_time or datetime.now() # # ✅ Doctor Fees # cursor.execute(""" # SELECT IFNULL(SUM(total), 0) # FROM doctor_charges_sale # WHERE created_at BETWEEN %s AND %s # """, (login_time, end_time)) # doctor_fees = float(cursor.fetchone()[0]) # # ✅ Medicine Fees # cursor.execute(""" # SELECT IFNULL(SUM(total), 0) # FROM sale_products # WHERE created_at BETWEEN %s AND %s # """, (login_time, end_time)) # medicine_fees = float(cursor.fetchone()[0]) # # ✅ Total Sale (received + suspended invoices) # cursor.execute(""" # SELECT IFNULL(SUM(invoiceTotal), 0) # FROM invoice_sale # WHERE created_at BETWEEN %s AND %s # AND status IN ('received', 'suspended') # """, (login_time, end_time)) # total_sale = float(cursor.fetchone()[0]) # # ✅ Sale Returns # cursor.execute(""" # SELECT IFNULL(SUM(total_return_amount), 0) # FROM sale_return # WHERE created_at BETWEEN %s AND %s # """, (login_time, end_time)) # total_return_amount = float(cursor.fetchone()[0]) # # ✅ Expenses # cursor.execute(""" # SELECT IFNULL(SUM(amount), 0) # FROM expenses # WHERE created_at BETWEEN %s AND %s # """, (login_time, end_time)) # total_expense = float(cursor.fetchone()[0]) # # ✅ Cash Payments (paid → invoiceTotal, partial → tenderAmount) # # Include partial payments even if status = 'suspended' # cursor.execute(""" # SELECT IFNULL(SUM( # CASE # WHEN payment_method = 'paid' AND status = 'received' THEN invoiceTotal # WHEN payment_method = 'partial' AND status IN ('received', 'suspended') THEN tenderAmount # ELSE 0 # END # ), 0) # FROM invoice_sale # WHERE created_at BETWEEN %s AND %s # """, (login_time, end_time)) # cash_payment_total = float(cursor.fetchone()[0]) # # ✅ Calculate total cash # total_cash = cash_payment_total - total_expense # cursor.close() # conn.close() # # ✅ JSON Response # return jsonify({ # "status": status, # "cash_in_hand": cash_in_hand, # "doctor_fees": doctor_fees, # "medicine_fees": medicine_fees, # "total_sale": total_sale, # "total_return_amount": total_return_amount, # "total_expense": total_expense, # "cash_payment_total": cash_payment_total, # "total_cash": total_cash # }) # except Exception as e: # print("Summary error:", e) # return jsonify({"error": str(e)}), 500 # @report_bp.route('/get_today_summary', methods=['GET']) # @jwt_required() # @role_required('admin', 'cashier', 'manager') # def get_today_summary(): # try: # conn = get_db_connection() # cursor = conn.cursor(dictionary=True) # today = date.today() # start_time = datetime.combine(today, datetime.min.time()) # end_time = datetime.combine(today, datetime.max.time()) # # ============================================ # # 1. TOTAL SALES BY STATUS # # ============================================ # cursor.execute(""" # SELECT # IFNULL(SUM(CASE WHEN status = 'received' THEN invoiceTotal ELSE 0 END), 0) as received_sales, # IFNULL(SUM(CASE WHEN status = 'suspended' THEN invoiceTotal ELSE 0 END), 0) as suspended_sales, # IFNULL(SUM(invoiceTotal), 0) as total_sales, # COUNT(CASE WHEN status = 'received' THEN 1 END) as received_count, # COUNT(CASE WHEN status = 'suspended' THEN 1 END) as suspended_count # FROM invoice_sale # WHERE created_at BETWEEN %s AND %s # """, (start_time, end_time)) # sales_data = cursor.fetchone() # # ============================================ # # 2. PAYMENT METHODS BREAKDOWN # # ============================================ # cursor.execute(""" # SELECT # pm.method_name, # IFNULL(SUM(sp.amount), 0) as total_amount, # COUNT(sp.id) as transaction_count # FROM sale_payments sp # INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id # LEFT JOIN payment_methods pm ON sp.payment_method = pm.id # WHERE inv.created_at BETWEEN %s AND %s # GROUP BY pm.method_name # ORDER BY total_amount DESC # """, (start_time, end_time)) # payment_methods = cursor.fetchall() # # ============================================ # # 3. PAYMENT STATUS BREAKDOWN # # ============================================ # cursor.execute(""" # SELECT # payment_status, # COUNT(*) as count, # IFNULL(SUM(invoiceTotal), 0) as total_amount, # IFNULL(SUM(tenderAmount), 0) as paid_amount, # IFNULL(SUM(remainingAmount), 0) as due_amount # FROM invoice_sale # WHERE created_at BETWEEN %s AND %s # GROUP BY payment_status # """, (start_time, end_time)) # payment_status_breakdown = cursor.fetchall() # # ============================================ # # 4. TOTAL CASH COLLECTED (from sale_payments) # # ============================================ # cursor.execute(""" # SELECT IFNULL(SUM(sp.amount), 0) as total_cash # FROM sale_payments sp # INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id # INNER JOIN payment_methods pm ON sp.payment_method = pm.id # WHERE inv.created_at BETWEEN %s AND %s # AND LOWER(pm.method_name) = 'cash' # """, (start_time, end_time)) # cash_payment = float(cursor.fetchone()['total_cash']) # # ============================================ # # 5. CARD PAYMENTS # # ============================================ # cursor.execute(""" # SELECT IFNULL(SUM(sp.amount), 0) as total_card # FROM sale_payments sp # INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id # INNER JOIN payment_methods pm ON sp.payment_method = pm.id # WHERE inv.created_at BETWEEN %s AND %s # AND LOWER(pm.method_name) IN ('card', 'credit card', 'debit card', 'credit/debit card') # """, (start_time, end_time)) # card_payment = float(cursor.fetchone()['total_card']) # # ============================================ # # 6. SALE RETURNS # # ============================================ # cursor.execute(""" # SELECT # IFNULL(SUM(total_return_amount), 0) as total_return, # COUNT(*) as return_count # FROM sale_return # WHERE created_at BETWEEN %s AND %s # """, (start_time, end_time)) # return_data = cursor.fetchone() # # ============================================ # # 7. EXPENSES # # ============================================ # cursor.execute(""" # SELECT # IFNULL(SUM(amount), 0) as total_expense, # COUNT(*) as expense_count # FROM expenses # WHERE created_at BETWEEN %s AND %s # """, (start_time, end_time)) # expense_data = cursor.fetchone() # # ============================================ # # 8. TOTAL PAYMENTS COLLECTED (all methods) # # ============================================ # cursor.execute(""" # SELECT IFNULL(SUM(sp.amount), 0) as total_payments # FROM sale_payments sp # INNER JOIN invoice_sale inv ON sp.sale_id = inv.invoice_id # WHERE inv.created_at BETWEEN %s AND %s # """, (start_time, end_time)) # total_payments_collected = float(cursor.fetchone()['total_payments']) # cursor.close() # conn.close() # # ============================================ # # FINAL CALCULATIONS # # ============================================ # total_sale = float(sales_data['total_sales']) # received_sales = float(sales_data['received_sales']) # suspended_sales = float(sales_data['suspended_sales']) # sale_return = float(return_data['total_return']) # total_expense = float(expense_data['total_expense']) # # Net cash = Cash payments - Returns - Expenses # net_cash = cash_payment - sale_return - total_expense # return jsonify({ # "date": today.strftime("%Y-%m-%d"), # # Sales Overview # "total_sale": total_sale, # "received_sales": received_sales, # "suspended_sales": suspended_sales, # "received_count": sales_data['received_count'], # "suspended_count": sales_data['suspended_count'], # # Payment Methods Breakdown # "payment_methods": payment_methods, # # Payment Status # "payment_status": payment_status_breakdown, # # Totals by Payment Type # "cash_payment": cash_payment, # "card_payment": card_payment, # "total_payments_collected": total_payments_collected, # # Deductions # "sale_return": sale_return, # "return_count": return_data['return_count'], # "total_expense": total_expense, # "expense_count": expense_data['expense_count'], # # Final Totals # "net_cash": net_cash, # "total_cash_in_hand": cash_payment - sale_return # }) # except Exception as e: # print(f"❌ Error in today summary: {e}") # return jsonify({"error": str(e)}), 500 # =========================== # 📊 GET ALL CLOSED REGISTER LOGS # =========================== @report_bp.route('/get_closed_register_logs', methods=['GET']) @jwt_required() @role_required('admin', 'manager') def get_closed_register_logs(): """ ✅ Fetch all closed register sessions with complete details """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: # Get all closed register logs with user info cursor.execute(""" SELECT crl.id, crl.store_id, crl.user_id, crl.login_time, crl.logout_time, crl.opening_cash, crl.total_cash_added, crl.total_cash_removed, crl.counted_cash, crl.opening_balance, crl.closing_balance, crl.expected_cash, crl.actual_cash, crl.cash_difference, crl.total_cash, crl.total_card_sales, crl.total_sale_amount, crl.sale_return_amount, crl.total_expense, crl.notes, crl.status, crl.created_at, u.name AS user_name, u.email AS user_email, s.store_name FROM close_register_logs crl LEFT JOIN users u ON crl.user_id = u.id LEFT JOIN stores s ON crl.store_id = s.id WHERE crl.status = 'close' ORDER BY crl.logout_time DESC """) rows = cursor.fetchall() # Convert Decimal to float for JSON serialization for row in rows: for key, value in row.items(): if isinstance(value, Decimal): row[key] = float(value) elif isinstance(value, datetime): row[key] = value.strftime('%Y-%m-%d %H:%M:%S') return jsonify({ 'success': True, 'logs': rows, 'total': len(rows) }), 200 except Exception as e: print(f"❌ Error fetching register logs: {e}") return jsonify({ 'success': False, 'message': str(e) }), 500 finally: cursor.close() conn.close() @report_bp.route('/get_register_details/', methods=['GET']) @jwt_required() @role_required('admin', 'manager', 'cashier') def get_register_details(register_id): conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: # ── Register log info ──────────────────────────────────────────── cursor.execute(""" SELECT crl.*, u.name AS user_name, u.email AS user_email, s.store_name FROM close_register_logs crl LEFT JOIN users u ON crl.user_id = u.id LEFT JOIN stores s ON crl.store_id = s.id WHERE crl.id = %s """, (register_id,)) register_log = cursor.fetchone() if not register_log: return jsonify({'success': False, 'message': 'Register log not found'}), 404 login_time = register_log['login_time'] logout_time = register_log['logout_time'] store_id = register_log['store_id'] # ✅ FIX: scope all queries to this store # ── Sales totals scoped to this store ─────────────────────────── cursor.execute(""" SELECT COALESCE(SUM(invoiceTotal), 0) AS total_sales, COALESCE(SUM(CASE WHEN payment_status='paid' THEN invoiceTotal ELSE 0 END), 0) AS fees_received, COALESCE(SUM(CASE WHEN payment_status='unpaid' THEN invoiceTotal ELSE 0 END), 0) AS fees_unpaid, COALESCE(SUM(CASE WHEN payment_status='partial' THEN invoiceTotal ELSE 0 END), 0) AS fees_partial, COUNT(*) AS total_invoices, COUNT(CASE WHEN payment_status='unpaid' THEN 1 END) AS unpaid_count, COUNT(CASE WHEN payment_status='partial' THEN 1 END) AS partial_count FROM invoice_sale WHERE store_id = %s AND sale_date BETWEEN %s AND %s """, (store_id, login_time, logout_time)) sales_data = cursor.fetchone() # ── Payments received, broken down by method ───────────────────── # ✅ FIX: LOWER() on method_name so 'Cash' / 'cash' both match later cursor.execute(""" SELECT pm.method_name, COALESCE(SUM(sp.amount), 0) AS total FROM sale_payments sp JOIN invoice_sale iv ON sp.sale_id = iv.invoice_id JOIN payment_methods pm ON sp.payment_method = pm.id WHERE iv.store_id = %s AND iv.sale_date BETWEEN %s AND %s GROUP BY pm.method_name """, (store_id, login_time, logout_time)) payments_by_method = {row['method_name']: float(row['total']) for row in cursor.fetchall()} # ── Due amounts still outstanding ──────────────────────────────── cursor.execute(""" SELECT COALESCE(SUM(CASE WHEN payment_status='unpaid' THEN invoiceTotal ELSE 0 END), 0) AS due_unpaid, COALESCE(SUM(CASE WHEN payment_status='partial' THEN remainingAmount ELSE 0 END), 0) AS due_partial FROM invoice_sale WHERE store_id = %s AND sale_date BETWEEN %s AND %s """, (store_id, login_time, logout_time)) due_data = cursor.fetchone() # ── Returns scoped to this store ───────────────────────────────── # ✅ FIX: was missing store_id filter cursor.execute(""" SELECT COALESCE(SUM(total_return_amount), 0) AS total_returns FROM sale_return WHERE store_id = %s AND return_date BETWEEN %s AND %s """, (store_id, login_time, logout_time)) returns_data = cursor.fetchone() # ── Expenses scoped to this store ──────────────────────────────── # ✅ FIX: was missing store_id filter cursor.execute(""" SELECT COALESCE(SUM(amount), 0) AS total_expenses FROM expenses WHERE store_id = %s AND date BETWEEN %s AND %s """, (store_id, login_time, logout_time)) expenses_data = cursor.fetchone() # ── Cash transactions (add / remove) ───────────────────────────── cursor.execute(""" SELECT COALESCE(SUM(CASE WHEN transaction_type='add' THEN amount ELSE 0 END), 0) AS total_added, COALESCE(SUM(CASE WHEN transaction_type='remove' THEN amount ELSE 0 END), 0) AS total_removed FROM cash_transactions WHERE register_log_id = %s """, (register_id,)) cash_tx = cursor.fetchone() # ── Calculations ───────────────────────────────────────────────── opening_cash = float(register_log.get('opening_cash') or 0) total_added = float(cash_tx.get('total_added') or 0) total_removed = float(cash_tx.get('total_removed') or 0) cash_in_hand = opening_cash + total_added - total_removed total_returns = float(returns_data.get('total_returns') or 0) total_expenses = float(expenses_data.get('total_expenses') or 0) total_payments = sum(payments_by_method.values()) # ✅ Use DB-saved values directly — these were recorded at register close time # expected_cash → close_register_logs.expected_cash (saved when cashier closed) # actual_cash → close_register_logs.actual_cash (saved when cashier counted) # cash_difference → close_register_logs.cash_difference (generated: actual - expected) expected_cash = float(register_log.get('expected_cash') or 0) actual_cash = float(register_log.get('actual_cash') or 0) cash_diff = float(register_log.get('cash_difference') or 0) # ── Serialise datetimes / Decimals ─────────────────────────────── for key, value in register_log.items(): if isinstance(value, Decimal): register_log[key] = float(value) elif isinstance(value, datetime): register_log[key] = value.strftime('%Y-%m-%d %H:%M:%S') return jsonify({ 'success': True, 'register_log': register_log, 'breakdown': { 'opening_cash': opening_cash, 'cash_added': total_added, 'cash_removed': total_removed, 'cash_in_hand': cash_in_hand, 'total_sales': float(sales_data.get('total_sales') or 0), 'fees_received': float(sales_data.get('fees_received') or 0), 'fees_unpaid': float(sales_data.get('fees_unpaid') or 0), 'fees_partial': float(sales_data.get('fees_partial') or 0), 'unpaid_count': int(sales_data.get('unpaid_count') or 0), 'partial_count': int(sales_data.get('partial_count') or 0), 'due_unpaid': float(due_data.get('due_unpaid') or 0), 'due_partial': float(due_data.get('due_partial') or 0), 'payments_by_method': payments_by_method, 'total_payments': total_payments, 'total_returns': total_returns, 'total_expenses': total_expenses, # ✅ These 3 come directly from DB — saved at register close time 'expected_cash': expected_cash, 'actual_cash': actual_cash, 'cash_difference': cash_diff, 'total_invoices': int(sales_data.get('total_invoices') or 0), } }), 200 except Exception as e: print(f"❌ Error: {e}") return jsonify({'success': False, 'message': str(e)}), 500 finally: cursor.close() conn.close() # =========================== # 📋 GET INVOICES FOR REGISTER SESSION # =========================== @report_bp.route('/get_register_invoices/', methods=['GET']) @jwt_required() @role_required('admin', 'manager') def get_register_invoices(register_id): """ ✅ Get all invoices during a specific register session """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: # Get register time range cursor.execute(""" SELECT login_time, logout_time FROM close_register_logs WHERE id = %s """, (register_id,)) session = cursor.fetchone() if not session: return jsonify({ 'success': False, 'message': 'Register session not found' }), 404 login_time = session['login_time'] logout_time = session['logout_time'] # Get all invoices in this time range cursor.execute(""" SELECT i.invoice_id, i.invoice_code, i.tenderAmount, i.remainingAmount, i.invoiceTotal, i.payment_method, i.payment_status, i.sale_date, i.status, i.discount, i.tax, c.name AS customer_name, c.contact AS customer_contact, u.name AS cashier_name FROM invoice_sale i LEFT JOIN customers c ON i.customer_id = c.id LEFT JOIN users u ON i.cashier_user_id = u.id WHERE i.sale_date BETWEEN %s AND %s ORDER BY i.sale_date DESC """, (login_time, logout_time)) invoices = cursor.fetchall() # Get products for each invoice for invoice in invoices: cursor.execute(""" SELECT p.product_name, pv.variation_name, pv.variation_type, sp.price, sp.quantity, sp.total, sp.discount, sp.tax FROM sale_products sp JOIN products p ON sp.product_id = p.id LEFT JOIN product_variations pv ON sp.variation_id = pv.id WHERE sp.invoice_id = %s """, (invoice['invoice_id'],)) invoice['products'] = cursor.fetchall() # Convert Decimals for key, value in invoice.items(): if isinstance(value, Decimal): invoice[key] = float(value) elif isinstance(value, datetime): invoice[key] = value.strftime('%Y-%m-%d %H:%M:%S') for product in invoice['products']: for key, value in product.items(): if isinstance(value, Decimal): product[key] = float(value) return jsonify({ 'success': True, 'invoices': invoices, 'total': len(invoices) }), 200 except Exception as e: print(f"❌ Error fetching register invoices: {e}") return jsonify({ 'success': False, 'message': str(e) }), 500 finally: cursor.close() conn.close() # =========================== # 🔄 GET SALE RETURNS FOR REGISTER # =========================== @report_bp.route('/get_register_returns/', methods=['GET']) @jwt_required() @role_required('admin', 'manager') def get_register_returns(register_id): """ ✅ Get all sale returns during a register session """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: # Get time range cursor.execute(""" SELECT login_time, logout_time FROM close_register_logs WHERE id = %s """, (register_id,)) session = cursor.fetchone() if not session: return jsonify({ 'success': False, 'message': 'Register session not found' }), 404 login_time = session['login_time'] logout_time = session['logout_time'] # Get returns cursor.execute(""" SELECT sr.*, i.invoice_code, c.name AS customer_name FROM sale_return sr JOIN invoice_sale i ON sr.invoice_id = i.invoice_id LEFT JOIN customers c ON sr.customer_id = c.id WHERE sr.return_date BETWEEN %s AND %s ORDER BY sr.return_date DESC """, (login_time, logout_time)) returns = cursor.fetchall() # Get return products for ret in returns: cursor.execute(""" SELECT p.product_name, pv.variation_name, pv.variation_type, srp.return_price, srp.return_quantity, srp.return_total FROM sale_return_products srp JOIN products p ON srp.product_id = p.id LEFT JOIN product_variations pv ON srp.variation_id = pv.id WHERE srp.return_id = %s """, (ret['return_id'],)) ret['products'] = cursor.fetchall() # Convert Decimals for key, value in ret.items(): if isinstance(value, Decimal): ret[key] = float(value) elif isinstance(value, datetime): ret[key] = value.strftime('%Y-%m-%d %H:%M:%S') for product in ret['products']: for key, value in product.items(): if isinstance(value, Decimal): product[key] = float(value) return jsonify({ 'success': True, 'returns': returns, 'total': len(returns) }), 200 except Exception as e: print(f"❌ Error fetching register returns: {e}") return jsonify({ 'success': False, 'message': str(e) }), 500 finally: cursor.close() conn.close() # =========================== # 📊 GET CASH TRANSACTIONS # =========================== @report_bp.route('/get_register_cash_transactions/', methods=['GET']) @jwt_required() @role_required('admin', 'manager', 'cashier') def get_register_cash_transactions(register_id): """ ✅ Get all cash add/remove transactions for a register session """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: cursor.execute(""" SELECT ct.*, u.name AS created_by_name FROM cash_transactions ct LEFT JOIN users u ON ct.created_by = u.id WHERE ct.register_log_id = %s ORDER BY ct.created_at ASC """, (register_id,)) transactions = cursor.fetchall() for txn in transactions: for key, value in txn.items(): if isinstance(value, Decimal): txn[key] = float(value) elif isinstance(value, datetime): txn[key] = value.strftime('%Y-%m-%d %H:%M:%S') return jsonify({ 'success': True, 'transactions': transactions, 'total': len(transactions) }), 200 except Exception as e: print(f"❌ Error fetching cash transactions: {e}") return jsonify({ 'success': False, 'message': str(e) }), 500 finally: cursor.close() conn.close() # =========================== # 📈 LEGACY ENDPOINTS (For Compatibility) # =========================== @report_bp.route('/invoices_for_all_closed_registers', methods=['GET']) @jwt_required() @role_required('admin') def invoices_for_all_closed_registers(): """ ⚠️ LEGACY: Get all invoices from all closed registers Consider using /get_register_invoices/ instead """ try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT login_time, logout_time, id FROM close_register_logs WHERE status = 'close' AND logout_time IS NOT NULL ORDER BY logout_time DESC """) closed_sessions = cursor.fetchall() all_invoice_details = [] for session in closed_sessions: login_time = session['login_time'] logout_time = session['logout_time'] register_id = session['id'] cursor.execute(""" SELECT i.invoice_id, i.invoice_code, i.tenderAmount, i.remainingAmount, i.invoiceTotal, i.payment_method, i.sale_date, i.status, c.name AS customer_name FROM invoice_sale i LEFT JOIN customers c ON i.customer_id = c.id WHERE i.sale_date BETWEEN %s AND %s """, (login_time, logout_time)) invoices = cursor.fetchall() for invoice in invoices: cursor.execute(""" SELECT p.product_name, sp.price, sp.quantity, sp.total FROM sale_products sp JOIN products p ON sp.product_id = p.id WHERE sp.invoice_id = %s """, (invoice['invoice_id'],)) products = cursor.fetchall() invoice_data = { "register_id": register_id, "invoice_id": invoice['invoice_id'], "invoice_code": invoice['invoice_code'], "tenderAmount": float(invoice['tenderAmount']) if invoice['tenderAmount'] else 0, "remainingAmount": float(invoice['remainingAmount']) if invoice['remainingAmount'] else 0, "invoiceTotal": float(invoice['invoiceTotal']) if invoice['invoiceTotal'] else 0, "payment_method": invoice['payment_method'], "customer_name": invoice['customer_name'], "sale_date": invoice['sale_date'].strftime('%Y-%m-%d %H:%M:%S') if invoice['sale_date'] else '', "status": invoice['status'], "products": [ { "product_name": p['product_name'], "price": float(p['price']) if p['price'] else 0, "quantity": float(p['quantity']) if p['quantity'] else 0, "total": float(p['total']) if p['total'] else 0 } for p in products ] } all_invoice_details.append(invoice_data) cursor.close() conn.close() return jsonify({"invoices": all_invoice_details}), 200 except Exception as e: print("❌ Error:", e) return jsonify({"error": str(e)}), 500 @report_bp.route('/view_all_sale_returns_in_closed_registers', methods=['GET']) @jwt_required() @role_required('admin') def view_all_sale_returns_in_closed_registers(): """ ⚠️ LEGACY: Get all returns from all closed registers Consider using /get_register_returns/ instead """ try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT login_time, logout_time, id FROM close_register_logs WHERE status = 'close' AND logout_time IS NOT NULL """) closed_sessions = cursor.fetchall() all_returns = [] for session in closed_sessions: login_time = session['login_time'] logout_time = session['logout_time'] register_id = session['id'] cursor.execute(""" SELECT sr.*, i.invoice_code, c.name AS customer_name FROM sale_return sr JOIN invoice_sale i ON sr.invoice_id = i.invoice_id LEFT JOIN customers c ON sr.customer_id = c.id WHERE sr.return_date BETWEEN %s AND %s ORDER BY sr.return_date DESC """, (login_time, logout_time)) returns = cursor.fetchall() for r in returns: r['register_id'] = register_id if r.get('return_date') and isinstance(r['return_date'], datetime): r['return_date'] = r['return_date'].strftime('%Y-%m-%d %H:%M:%S') cursor.execute(""" SELECT p.product_name, pv.variation_name, srp.return_quantity, srp.return_price, srp.return_total FROM sale_return_products srp JOIN products p ON srp.product_id = p.id LEFT JOIN product_variations pv ON srp.variation_id = pv.id WHERE srp.return_id = %s """, (r['return_id'],)) r['products'] = cursor.fetchall() # Convert Decimals for key, value in r.items(): if isinstance(value, Decimal): r[key] = float(value) for product in r['products']: for key, value in product.items(): if isinstance(value, Decimal): product[key] = float(value) all_returns.append(r) cursor.close() conn.close() return jsonify(all_returns), 200 except Exception as e: print("❌ Error:", e) return jsonify({"error": str(e)}), 500 @report_bp.route('/view_product_alert', methods=['GET']) @jwt_required() @role_required('admin') def view_product_alert(): """ Fetch products with low stock or out-of-stock per location. Rules: - Single products : show if SUM(ws.quantity) <= p.stock_alert (includes 0) - Variable products: show if SUM(ws.quantity) <= pv.variation_stock_alert (includes 0) - Grouped by product + store + warehouse location - Out-of-stock (qty = 0) rows ARE included """ conn = get_db_connection() if conn is None: return jsonify({'error': 'Database connection failed'}), 500 cursor = conn.cursor(dictionary=True) try: results = [] # ============================================================ # SINGLE PRODUCTS # Show every (product × store × warehouse) location where # total warehouse_stock.quantity <= p.stock_alert # Including zero-stock locations. # ============================================================ cursor.execute(""" SELECT p.id, p.product_name, p.sku, p.product_type, p.stock_alert, u.unit_short AS unit, MIN(pb.cost) AS min_cost, MAX(pb.cost) AS max_cost, MIN(pb.price) AS min_price, MAX(pb.price) AS max_price, SUM(pb.remaining_quantity) AS total_remaining_quantity, SUM(COALESCE(ws.quantity, 0)) AS total_warehouse_quantity, GROUP_CONCAT(DISTINCT pb.batch_number ORDER BY pb.batch_number SEPARATOR ', ') AS batch_numbers, GROUP_CONCAT(DISTINCT g.grn_code ORDER BY g.grn_code SEPARATOR ', ') AS grn_codes, MIN(pb.expiration_date) AS earliest_expiry, COALESCE(s.store_name, '-') AS store_name, COALESCE(w.warehouse_name, '-') AS warehouse_name, s.id AS store_id, w.id AS warehouse_id FROM products p LEFT JOIN units u ON p.sale_unit_id = u.id INNER JOIN product_batches pb ON pb.product_id = p.id AND pb.variation_id IS NULL LEFT JOIN grn g ON pb.grn_id = g.grn_id LEFT JOIN warehouse_stock ws ON ws.batch_id = pb.batch_id LEFT JOIN stores s ON ws.store_id = s.id LEFT JOIN warehouses w ON ws.warehouse_id = w.id WHERE p.product_type = 'single' GROUP BY p.id, p.product_name, p.sku, p.stock_alert, u.unit_short, s.id, s.store_name, w.id, w.warehouse_name HAVING SUM(COALESCE(ws.quantity, 0)) <= p.stock_alert ORDER BY p.product_name, s.store_name, w.warehouse_name """) single_products = cursor.fetchall() for product in single_products: qty = float(product['total_warehouse_quantity'] or 0) alert_threshold = float(product['stock_alert'] or 0) min_cost = float(product['min_cost'] or 0) max_cost = float(product['max_cost'] or 0) min_price = float(product['min_price'] or 0) max_price = float(product['max_price'] or 0) cost_display = (f"{min_cost}" if min_cost == max_cost else f"{min_cost} - {max_cost}") price_display = (f"{min_price}" if min_price == max_price else f"{min_price} - {max_price}") results.append({ "product_code" : product['sku'], "product_name" : product['product_name'], "batch_numbers" : product['batch_numbers'] or '-', "grn_codes" : product['grn_codes'] or '-', "cost" : cost_display, "price" : price_display, "quantity" : qty, "remaining_quantity" : float(product['total_remaining_quantity'] or 0), "alert_quantity" : alert_threshold, "unit" : product['unit'] or '-', "store_name" : product['store_name'], "warehouse_name" : product['warehouse_name'], "expiration_date" : (str(product['earliest_expiry']) if product['earliest_expiry'] else None), "stock_alert_flag" : True, "is_out_of_stock" : qty == 0, "variation_name" : None, "variation_type" : None, "parent_product_name": None, "is_parent_row" : False, }) # ============================================================ # VARIABLE PRODUCTS # Step 1 – get unique parent products that have at least one # low-stock / zero-stock variation at any location. # Step 2 – for each parent, fetch all low-stock variation rows # grouped by variation × store × warehouse. # ============================================================ # Step 1: distinct parents with at least one alert variation cursor.execute(""" SELECT DISTINCT p.id, p.product_name, p.sku, p.stock_alert, u.unit_short AS unit FROM products p LEFT JOIN units u ON p.sale_unit_id = u.id INNER JOIN product_variations pv ON pv.product_id = p.id INNER JOIN product_batches pb ON pb.variation_id = pv.id LEFT JOIN warehouse_stock ws ON ws.batch_id = pb.batch_id WHERE p.product_type = 'variable' GROUP BY p.id, p.product_name, p.sku, p.stock_alert, u.unit_short, pv.id, pv.variation_stock_alert HAVING SUM(COALESCE(ws.quantity, 0)) <= pv.variation_stock_alert """) variable_parents = cursor.fetchall() added_parent_headers = set() for parent in variable_parents: # Step 2: low-stock variation rows for this parent cursor.execute(""" SELECT pv.id AS variation_id, pv.variation_name, pv.variation_type, pv.variation_sku, pv.variation_stock_alert, MIN(pb.cost) AS min_cost, MAX(pb.cost) AS max_cost, MIN(pb.price) AS min_price, MAX(pb.price) AS max_price, SUM(pb.remaining_quantity) AS total_remaining_quantity, SUM(COALESCE(ws.quantity, 0)) AS total_warehouse_quantity, GROUP_CONCAT(DISTINCT pb.batch_number ORDER BY pb.batch_number SEPARATOR ', ') AS batch_numbers, GROUP_CONCAT(DISTINCT g.grn_code ORDER BY g.grn_code SEPARATOR ', ') AS grn_codes, MIN(pb.expiration_date) AS earliest_expiry, COALESCE(s.store_name, '-') AS store_name, COALESCE(w.warehouse_name, '-') AS warehouse_name, s.id AS store_id, w.id AS warehouse_id FROM product_variations pv INNER JOIN product_batches pb ON pb.variation_id = pv.id LEFT JOIN grn g ON pb.grn_id = g.grn_id LEFT JOIN warehouse_stock ws ON ws.batch_id = pb.batch_id LEFT JOIN stores s ON ws.store_id = s.id LEFT JOIN warehouses w ON ws.warehouse_id = w.id WHERE pv.product_id = %s GROUP BY pv.id, pv.variation_name, pv.variation_type, pv.variation_sku, pv.variation_stock_alert, s.id, s.store_name, w.id, w.warehouse_name HAVING SUM(COALESCE(ws.quantity, 0)) <= pv.variation_stock_alert ORDER BY pv.variation_name, s.store_name, w.warehouse_name """, (parent['id'],)) variations = cursor.fetchall() if not variations: continue # Parent header row (shown once per variable product) if parent['id'] not in added_parent_headers: results.append({ "product_code" : parent['sku'], "product_name" : parent['product_name'], "batch_numbers" : '-', "grn_codes" : '-', "cost" : '-', "price" : '-', "quantity" : None, "remaining_quantity" : None, "alert_quantity" : None, "unit" : parent['unit'] or '-', "store_name" : '-', "warehouse_name" : '-', "expiration_date" : None, "stock_alert_flag" : False, "is_out_of_stock" : False, "variation_name" : None, "variation_type" : None, "parent_product_name": None, "is_parent_row" : True, }) added_parent_headers.add(parent['id']) # Variation rows for v in variations: qty = float(v['total_warehouse_quantity'] or 0) alert_threshold = float(v['variation_stock_alert'] or 0) min_cost = float(v['min_cost'] or 0) max_cost = float(v['max_cost'] or 0) min_price = float(v['min_price'] or 0) max_price = float(v['max_price'] or 0) cost_display = (f"{min_cost}" if min_cost == max_cost else f"{min_cost} - {max_cost}") price_display = (f"{min_price}" if min_price == max_price else f"{min_price} - {max_price}") results.append({ "product_code" : v['variation_sku'], "product_name" : (f"{parent['product_name']} - " f"{v['variation_name']} " f"({v['variation_type']})"), "batch_numbers" : v['batch_numbers'] or '-', "grn_codes" : v['grn_codes'] or '-', "cost" : cost_display, "price" : price_display, "quantity" : qty, "remaining_quantity" : float(v['total_remaining_quantity'] or 0), "alert_quantity" : alert_threshold, "unit" : parent['unit'] or '-', "store_name" : v['store_name'], "warehouse_name" : v['warehouse_name'], "expiration_date" : (str(v['earliest_expiry']) if v['earliest_expiry'] else None), "stock_alert_flag" : True, "is_out_of_stock" : qty == 0, "variation_name" : v['variation_name'], "variation_type" : v['variation_type'], "parent_product_name": parent['product_name'], "is_parent_row" : False, }) return jsonify(results), 200 except Exception as err: print(f"Error fetching product alerts: {err}") import traceback traceback.print_exc() return jsonify({'error': 'Failed to fetch product alerts'}), 500 finally: cursor.close() conn.close() # ============================================================ # STOCK QUANTITY REPORT # ============================================================ @report_bp.route('/view_product_stock', methods=['GET']) @jwt_required() @role_required('admin') def view_product_stock(): """ Get stock levels for all products across all warehouses. Cost & Price always taken from product_batches table (never from product_variations). Now includes batch_breakdown list per product row for the Price Range modal. """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: warehouse_id = request.args.get('warehouse_id', type=int) category_id = request.args.get('category_id', type=int) low_stock_only = request.args.get('low_stock_only', type=int) query = """ SELECT p.id AS product_id, p.product_name, p.sku AS product_code, p.product_type, p.stock_alert, w.id AS warehouse_id, w.warehouse_name, c.category_name AS category, pv.id AS variation_id, pv.variation_name, pv.variation_type, pv.variation_sku, pv.variation_stock_alert, SUM(COALESCE(ws.quantity, 0)) AS quantity, u.unit_short AS unit, u.unit_name, CASE WHEN pv.id IS NOT NULL THEN CASE WHEN SUM(COALESCE(ws.quantity, 0)) <= COALESCE(pv.variation_stock_alert, 0) THEN 1 ELSE 0 END ELSE CASE WHEN SUM(COALESCE(ws.quantity, 0)) <= p.stock_alert THEN 1 ELSE 0 END END AS stock_alert_flag FROM products p LEFT JOIN categories c ON p.category_id = c.id LEFT JOIN product_variations pv ON p.id = pv.product_id LEFT JOIN warehouse_stock ws ON p.id = ws.product_id AND (pv.id IS NULL OR ws.variation_id = pv.id) LEFT JOIN warehouses w ON ws.warehouse_id = w.id LEFT JOIN units u ON p.sale_unit_id = u.id WHERE 1=1 """ params = [] if warehouse_id: query += " AND ws.warehouse_id = %s" params.append(warehouse_id) if category_id: query += " AND p.category_id = %s" params.append(category_id) query += """ GROUP BY p.id, p.product_name, p.sku, p.product_type, p.stock_alert, w.id, w.warehouse_name, c.category_name, pv.id, pv.variation_name, pv.variation_type, pv.variation_sku, pv.variation_stock_alert, u.unit_short, u.unit_name """ query += " HAVING SUM(COALESCE(ws.quantity, 0)) > 0" query += " ORDER BY p.product_name, pv.variation_name" cursor.execute(query, params) rows = cursor.fetchall() products = [] seen_products = {} for row in rows: product_key = f"{row['product_id']}_{row['warehouse_id']}" # ---------------------------------------------------------------- # Batch query — ALWAYS use product_batches for cost & price # Groups by cost+price, sums quantity per unique cost/price pair # ---------------------------------------------------------------- batch_query = """ SELECT pb.cost, pb.price, SUM(ws.quantity) AS quantity, u.unit_short AS unit FROM product_batches pb INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id LEFT JOIN products p ON pb.product_id = p.id LEFT JOIN units u ON p.sale_unit_id = u.id WHERE pb.product_id = %s AND ws.warehouse_id = %s AND pb.remaining_quantity > 0 AND ws.quantity > 0 """ batch_params = [row["product_id"], row["warehouse_id"]] if row["variation_id"]: batch_query += " AND pb.variation_id = %s AND ws.variation_id = %s" batch_params.extend([row["variation_id"], row["variation_id"]]) else: batch_query += " AND pb.variation_id IS NULL AND ws.variation_id IS NULL" batch_query += " GROUP BY pb.cost, pb.price, u.unit_short ORDER BY pb.cost ASC" cursor.execute(batch_query, batch_params) batches = cursor.fetchall() # Build batch_breakdown list for the modal table batch_breakdown = [] for b in batches: batch_breakdown.append({ "cost": round(float(b["cost"]), 2) if b["cost"] is not None else 0.0, "price": round(float(b["price"]), 2) if b["price"] is not None else 0.0, "quantity": round(float(b["quantity"]), 2) if b["quantity"] is not None else 0.0, "unit": b["unit"] or row["unit"] or "" }) # Build cost / price display strings from batches only costs = sorted(set([b["cost"] for b in batch_breakdown])) prices = sorted(set([b["price"] for b in batch_breakdown])) cost_display = " - ".join([f"{c:.2f}" for c in costs]) if costs else "-" price_display = " - ".join([f"{p:.2f}" for p in prices]) if prices else "-" # ---------------------------------------------------------------- # Parent row for variable products (header row — no cost/price/qty) # ---------------------------------------------------------------- if row["product_type"] == "variable" and product_key not in seen_products: products.append({ "product_id": row["product_id"], "product_name": row["product_name"], "product_code": row["product_code"], "product_type": row["product_type"], "cost_display": "-", "price_display": "-", "batch_breakdown": [], "warehouse_id": row["warehouse_id"], "warehouse_name": row["warehouse_name"], "category": row["category"], "variation_id": None, "variation_name": None, "variation_type": None, "quantity": None, "unit": row["unit"] or "", "unit_name": row["unit_name"] or "", "stock_alert": row["stock_alert"], "stock_alert_flag": False }) seen_products[product_key] = True # Build product display name if row["variation_name"]: product_display = f"↳ {row['variation_name']}" if row["variation_type"]: product_display += f" ({row['variation_type']})" product_code = row["variation_sku"] or row["product_code"] else: product_display = row["product_name"] product_code = row["product_code"] products.append({ "product_id": row["product_id"], "product_name": product_display, "product_code": product_code, "product_type": row["product_type"], "cost_display": cost_display, "price_display": price_display, "batch_breakdown": batch_breakdown, "warehouse_id": row["warehouse_id"], "warehouse_name": row["warehouse_name"], "category": row["category"], "variation_id": row["variation_id"], "variation_name": row["variation_name"], "variation_type": row["variation_type"], "quantity": round(float(row["quantity"]), 2) if row["quantity"] else 0.0, "unit": row["unit"] or "", "unit_name": row["unit_name"] or "", "stock_alert": row["stock_alert"], "stock_alert_flag": bool(row["stock_alert_flag"]) }) if low_stock_only: products = [p for p in products if p["stock_alert_flag"]] return jsonify(products), 200 except Exception as e: print(f"❌ Error in view_product_stock: {e}") import traceback traceback.print_exc() return jsonify({"error": str(e)}), 500 finally: cursor.close() conn.close() # ------------------------------------------------------------------- # 5️⃣ GET GRN RECORDS BY PRODUCT (with warehouse + variation filter) # ------------------------------------------------------------------- @report_bp.route('/get_grn_by_product/', methods=['GET']) @jwt_required() @role_required('admin') def get_grn_by_product(product_id): """ Get all GRN (Goods Received Notes) records for a specific product. Includes per-item details from grn_items joined to grn and product_batches. Query Params: - variation_id (optional int) - warehouse_id (optional int) - start (optional date string YYYY-MM-DD) - end (optional date string YYYY-MM-DD) """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: variation_id = request.args.get('variation_id', type=int) warehouse_id = request.args.get('warehouse_id', type=int) start_date = request.args.get('start') end_date = request.args.get('end') query = """ SELECT -- GRN header g.grn_id, g.grn_code, g.grn_date, g.status AS grn_status, g.purchase_order_id, g.invoice_number, g.invoice_date, g.vehicle_number, g.driver_name, g.driver_contact, g.note AS grn_note, -- Supplier & Warehouse s.supplier_name, w.warehouse_name, g.warehouse_id, -- Received by user u.name AS received_by_name, -- GRN Item details gi.grn_item_id, gi.received_quantity, gi.rejected_quantity, gi.accepted_quantity, gi.unit_price, gi.batch_price, gi.subtotal, gi.purchase_unit, gi.expiration_date, gi.manufacturing_date, gi.quality_check, gi.rejection_reason, gi.note AS item_note, -- Product p.product_name, p.sku AS product_sku, -- Variation pv.variation_name, pv.variation_type, pv.variation_sku, -- Batch pb.batch_id, pb.batch_number, pb.quantity AS batch_total_quantity, pb.remaining_quantity, pb.cost AS batch_cost, pb.price AS batch_price_sell FROM grn_items gi JOIN grn g ON gi.grn_id = g.grn_id JOIN products p ON gi.product_id = p.id LEFT JOIN product_variations pv ON gi.variation_id = pv.id LEFT JOIN suppliers s ON g.supplier_id = s.id LEFT JOIN warehouses w ON g.warehouse_id = w.id LEFT JOIN users u ON g.received_by = u.id LEFT JOIN product_batches pb ON pb.grn_id = g.grn_id AND pb.product_id = gi.product_id AND (gi.variation_id IS NULL OR pb.variation_id = gi.variation_id) WHERE gi.product_id = %s """ params = [product_id] # ✅ Warehouse filter if warehouse_id: query += " AND g.warehouse_id = %s" params.append(warehouse_id) # ✅ Variation filter if variation_id: query += " AND gi.variation_id = %s" params.append(variation_id) # ✅ Date range filter if start_date and end_date: query += " AND g.grn_date BETWEEN %s AND %s" params.extend([start_date, end_date]) query += " ORDER BY g.grn_date DESC, g.grn_id DESC" cursor.execute(query, params) rows = cursor.fetchall() results = [] for row in rows: # Format dates grn_date = row['grn_date'].strftime("%Y-%m-%d") if row.get('grn_date') else None invoice_date_fmt = row['invoice_date'].strftime("%Y-%m-%d") if row.get('invoice_date') else None expiration_fmt = row['expiration_date'].strftime("%Y-%m-%d") if row.get('expiration_date') else None manufacturing_fmt = row['manufacturing_date'].strftime("%Y-%m-%d") if row.get('manufacturing_date') else None results.append({ # GRN header "grn_id": row["grn_id"], "grn_code": row["grn_code"], "grn_date": grn_date, "grn_status": row["grn_status"], "purchase_order_id": row["purchase_order_id"], "invoice_number": row["invoice_number"], "invoice_date": invoice_date_fmt, "vehicle_number": row["vehicle_number"], "driver_name": row["driver_name"], "driver_contact": row["driver_contact"], "note": row["grn_note"], # Parties "supplier_name": row["supplier_name"] or "N/A", "warehouse_name": row["warehouse_name"] or "N/A", "warehouse_id": row["warehouse_id"], "received_by_name": row["received_by_name"], # GRN Item "grn_item_id": row["grn_item_id"], "received_quantity": float(row["received_quantity"] or 0), "rejected_quantity": float(row["rejected_quantity"] or 0), "accepted_quantity": float(row["accepted_quantity"] or 0), "unit_price": float(row["unit_price"] or 0), "batch_price": float(row["batch_price"] or 0), "subtotal": float(row["subtotal"] or 0), "purchase_unit": row["purchase_unit"], "expiration_date": expiration_fmt, "manufacturing_date": manufacturing_fmt, "quality_check": row["quality_check"] or "pending", "rejection_reason": row["rejection_reason"], "item_note": row["item_note"], # Product "product_name": row["product_name"], "product_sku": row["product_sku"], # Variation "variation_name": row["variation_name"], "variation_type": row["variation_type"], "variation_sku": row["variation_sku"], # Batch "batch_id": row["batch_id"], "batch_number": row["batch_number"], "batch_total_quantity": float(row["batch_total_quantity"] or 0), "remaining_quantity": float(row["remaining_quantity"] or 0), "batch_cost": float(row["batch_cost"] or 0), "batch_price_sell": float(row["batch_price_sell"] or 0), }) return jsonify(results), 200 except Exception as e: print(f"❌ Error in get_grn_by_product: {e}") import traceback traceback.print_exc() return jsonify({"error": str(e)}), 500 finally: cursor.close() conn.close() @report_bp.route('/view_product_stock/', methods=['GET']) @jwt_required() @role_required('admin') def view_product_purchases(product_id): """ Get purchase history for a specific product ✅ Supports warehouse filtering ✅ Supports variation filtering ✅ Date range filtering ✅ Display name formatted (Single & Variable products) """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: variation_id = request.args.get('variation_id', type=int) warehouse_id = request.args.get('warehouse_id', type=int) start_date = request.args.get('start') end_date = request.args.get('end') query = """ SELECT po.order_id, po.created_on AS order_date, po.status AS purchase_status, po.warehouse_id, s.supplier_name, w.warehouse_name, -- Product info p.id AS product_id, p.product_name, p.sku AS product_sku, -- Variation info pv.id AS variation_id, pv.variation_name, pv.variation_type, pv.variation_sku, -- Display name CASE WHEN oi.variation_id IS NOT NULL THEN CONCAT( p.product_name, ' (', p.sku, ')', ' - ', pv.variation_name, ' - ', pv.variation_type, ' (', pv.variation_sku, ')' ) ELSE CONCAT(p.product_name, ' (', p.sku, ')') END AS display_product_name, -- Order item info oi.quantity AS ordered_quantity, oi.unit_price, oi.subtotal, oi.purchase_unit, -- Batch info pb.batch_id, pb.batch_number, pb.quantity AS batch_quantity, pb.remaining_quantity, pb.cost AS batch_cost, pb.price AS batch_price, pb.expiration_date, -- GRN info g.grn_id, g.grn_code, g.grn_date, g.status AS grn_status, -- Unit info u.unit_short AS unit, u.unit_name AS unit_name FROM purchase_orders po INNER JOIN order_items oi ON po.order_id = oi.order_id INNER JOIN products p ON oi.product_id = p.id LEFT JOIN product_variations pv ON oi.variation_id = pv.id LEFT JOIN suppliers s ON po.supplier_id = s.id LEFT JOIN warehouses w ON po.warehouse_id = w.id LEFT JOIN units u ON p.sale_unit_id = u.id LEFT JOIN product_batches pb ON pb.purchase_order_id = po.order_id AND pb.product_id = oi.product_id AND (oi.variation_id IS NULL OR pb.variation_id = oi.variation_id) LEFT JOIN grn g ON pb.grn_id = g.grn_id WHERE oi.product_id = %s """ params = [product_id] if warehouse_id: query += " AND po.warehouse_id = %s" params.append(warehouse_id) if variation_id: query += " AND oi.variation_id = %s" params.append(variation_id) if start_date and end_date: query += " AND DATE(po.created_on) BETWEEN %s AND %s" params.extend([start_date, end_date]) query += " ORDER BY po.created_on DESC, pb.batch_number" cursor.execute(query, params) rows = cursor.fetchall() purchases = [] for row in rows: purchases.append({ "order_id": row["order_id"], "order_date": row["order_date"].strftime("%Y-%m-%d %H:%M:%S") if row["order_date"] else None, "purchase_status": row["purchase_status"], "status": row["purchase_status"], "supplier_name": row["supplier_name"] or "N/A", "warehouse_id": row["warehouse_id"], "warehouse_name": row["warehouse_name"], "product_id": row["product_id"], "product_name": row["product_name"], "product_sku": row["product_sku"], "display_product_name": row["display_product_name"], "variation_id": row["variation_id"], "variation_name": row["variation_name"], "variation_type": row["variation_type"], "variation_sku": row["variation_sku"], "quantity": float(row["ordered_quantity"]) if row["ordered_quantity"] else 0.0, "ordered_quantity": float(row["ordered_quantity"]) if row["ordered_quantity"] else 0.0, "unit_price": float(row["unit_price"]) if row["unit_price"] else 0.0, "subtotal": float(row["subtotal"]) if row["subtotal"] else 0.0, "purchase_unit": row["purchase_unit"], "batch_id": row["batch_id"], "batch_number": row["batch_number"], "batch_quantity": float(row["batch_quantity"]) if row["batch_quantity"] else 0.0, "remaining_quantity": float(row["remaining_quantity"]) if row["remaining_quantity"] else 0.0, "batch_cost": float(row["batch_cost"]) if row["batch_cost"] else 0.0, "batch_price": float(row["batch_price"]) if row["batch_price"] else 0.0, "expiration_date": row["expiration_date"].strftime("%Y-%m-%d") if row["expiration_date"] else None, "grn_id": row["grn_id"], "grn_code": row["grn_code"], "grn_date": row["grn_date"].strftime("%Y-%m-%d") if row["grn_date"] else None, "grn_status": row["grn_status"], "unit": row["unit"] or "", "unit_name": row["unit_name"] or "" }) return jsonify(purchases), 200 except Exception as e: print(f"❌ Error in view_product_purchases: {e}") import traceback traceback.print_exc() return jsonify({"error": str(e)}), 500 finally: cursor.close() conn.close() @report_bp.route('/view_product_stock_purchase_return/', methods=['GET']) @jwt_required() @role_required('admin') def view_product_purchase_returns(product_id): """ Get purchase return history for a specific product ✅ Supports warehouse filtering ✅ Display name formatted (Single & Variable products) """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: variation_id = request.args.get('variation_id', type=int) warehouse_id = request.args.get('warehouse_id', type=int) start_date = request.args.get('start') end_date = request.args.get('end') query = """ SELECT pr.return_id, pr.warehouse_id, po.order_id AS purchase_id, CASE WHEN pri.variation_id IS NOT NULL THEN CONCAT( p.product_name, ' (', p.sku, ')', ' - ', v.variation_name, ' - ', v.variation_type, ' (', v.variation_sku, ')' ) ELSE CONCAT(p.product_name, ' (', p.sku, ')') END AS display_product_name, p.product_name, p.sku, v.variation_name, v.variation_type, v.variation_sku, s.supplier_name, w.warehouse_name, pri.quantity, pri.unit_price, pri.discount, pri.tax, pri.subtotal, pr.return_status, pr.return_total, pr.created_on FROM purchase_returns pr JOIN purchase_orders po ON pr.order_id = po.order_id JOIN suppliers s ON pr.supplier_id = s.id LEFT JOIN warehouses w ON pr.warehouse_id = w.id JOIN purchase_return_items pri ON pr.return_id = pri.return_id JOIN products p ON pri.product_id = p.id LEFT JOIN product_variations v ON pri.variation_id = v.id WHERE pri.product_id = %s """ params = [product_id] if warehouse_id: query += " AND pr.warehouse_id = %s" params.append(warehouse_id) if variation_id: query += " AND pri.variation_id = %s" params.append(variation_id) if start_date and end_date: query += " AND pr.created_on BETWEEN %s AND %s" params.extend([start_date + " 00:00:00", end_date + " 23:59:59"]) query += " ORDER BY pr.created_on DESC" cursor.execute(query, params) data = cursor.fetchall() for row in data: if row.get("created_on") and isinstance(row["created_on"], datetime): row["created_on"] = row["created_on"].strftime("%Y/%m/%d %I:%M:%S %p") return jsonify(data), 200 except Exception as e: print("❌ Error in view_product_purchase_returns:", str(e)) return jsonify({"error": str(e)}), 500 finally: cursor.close() conn.close() @report_bp.route('/get_sales_by_product/', methods=['GET']) @jwt_required() @role_required('admin') def get_sales_by_product(product_id): """ Get sales history for a specific product ✅ Supports warehouse filtering ✅ Display name formatted (Single & Variable products) """ variation_id = request.args.get('variation_id', default=None, type=int) warehouse_id = request.args.get('warehouse_id', default=None, type=int) start_date = request.args.get('start') end_date = request.args.get('end') try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) query = """ SELECT inv.invoice_code, inv.sale_date, inv.warehouse_id, sp.product_id, sp.variation_id, sp.price, sp.quantity, sp.total, sp.sale_unit, sp.discount AS product_discount, sp.tax AS product_tax, CASE WHEN sp.variation_id IS NOT NULL THEN CONCAT( p.product_name, ' (', p.sku, ')', ' - ', v.variation_name, ' - ', v.variation_type, ' (', v.variation_sku, ')' ) ELSE CONCAT(p.product_name, ' (', p.sku, ')') END AS display_product_name, p.product_name, p.sku, v.variation_name, v.variation_type, v.variation_sku, w.warehouse_name, c.name AS customer_name FROM sale_products sp JOIN invoice_sale inv ON sp.invoice_id = inv.invoice_id JOIN products p ON sp.product_id = p.id LEFT JOIN product_variations v ON sp.variation_id = v.id LEFT JOIN warehouses w ON inv.warehouse_id = w.id LEFT JOIN customers c ON inv.customer_id = c.id WHERE sp.product_id = %s """ params = [product_id] if warehouse_id: query += " AND inv.warehouse_id = %s" params.append(warehouse_id) if variation_id: query += " AND sp.variation_id = %s" params.append(variation_id) if start_date and end_date: query += " AND inv.sale_date BETWEEN %s AND %s" params.extend([start_date + " 00:00:00", end_date + " 23:59:59"]) query += " ORDER BY inv.sale_date DESC" cursor.execute(query, params) results = cursor.fetchall() for row in results: if row.get('sale_date'): row['sale_date'] = datetime.strftime(row['sale_date'], "%Y/%m/%d %I:%M:%S %p") return jsonify(results), 200 except Exception as e: print("❌ Error in get_sales_by_product:", e) return jsonify({"error": str(e)}), 500 finally: cursor.close() conn.close() @report_bp.route('/get_sales_return_by_product/', methods=['GET']) @jwt_required() @role_required('admin') def get_sales_return_by_product(product_id): """ Get sales return history for a specific product ✅ Supports warehouse filtering ✅ Display name formatted (Single & Variable products) """ variation_id = request.args.get('variation_id', default=None, type=int) warehouse_id = request.args.get('warehouse_id', default=None, type=int) start_date = request.args.get('start') end_date = request.args.get('end') try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) query = """ SELECT sr.return_id, sr.return_date, sr.warehouse_id, inv.invoice_code, CASE WHEN srp.variation_id IS NOT NULL THEN CONCAT( p.product_name, ' (', p.sku, ')', ' - ', v.variation_name, ' - ', v.variation_type, ' (', v.variation_sku, ')' ) ELSE CONCAT(p.product_name, ' (', p.sku, ')') END AS display_product_name, p.product_name, p.sku, v.variation_name, v.variation_type, v.variation_sku, w.warehouse_name, c.name AS customer_name, srp.return_quantity, srp.return_price, srp.return_total, srp.discount, srp.tax FROM sale_return_products srp JOIN sale_return sr ON srp.return_id = sr.return_id JOIN invoice_sale inv ON sr.invoice_id = inv.invoice_id JOIN products p ON srp.product_id = p.id LEFT JOIN product_variations v ON srp.variation_id = v.id LEFT JOIN warehouses w ON sr.warehouse_id = w.id LEFT JOIN customers c ON sr.customer_id = c.id WHERE srp.product_id = %s """ params = [product_id] if warehouse_id: query += " AND sr.warehouse_id = %s" params.append(warehouse_id) if variation_id: query += " AND srp.variation_id = %s" params.append(variation_id) if start_date and end_date: query += " AND sr.return_date BETWEEN %s AND %s" params.extend([start_date + " 00:00:00", end_date + " 23:59:59"]) query += " ORDER BY sr.return_date DESC" cursor.execute(query, params) results = cursor.fetchall() for row in results: if row.get('return_date'): row['return_date'] = datetime.strftime(row['return_date'], "%Y/%m/%d %I:%M:%S %p") return jsonify(results), 200 except Exception as e: print("❌ Error in get_sales_return_by_product:", e) return jsonify({"error": str(e)}), 500 finally: cursor.close() conn.close() # ============================================ # 1️⃣6️⃣ DASHBOARD PRODUCT ALERT # ============================================ @report_bp.route('/dashboard_view_product_alert', methods=['GET']) @jwt_required() @role_required('admin', 'user', 'cashier') def dashboard_view_product_alert(): """ Get products with low stock alert ✅ Fixed: Using correct column names from schema ✅ Format: Product Name - Variation Name (Variation Type) """ conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) # ✅ Single products cursor.execute(""" SELECT p.id, p.sku as product_code, p.product_name, NULL as variation_name, NULL as variation_type, COALESCE(SUM(ws.quantity), 0) as quantity, p.stock_alert as alert_quantity, u.unit_name as unit FROM products p LEFT JOIN warehouse_stock ws ON p.id = ws.product_id AND ws.variation_id IS NULL LEFT JOIN units u ON p.sale_unit_id = u.id WHERE p.product_type = 'single' GROUP BY p.id, p.sku, p.product_name, p.stock_alert, u.unit_name HAVING quantity <= p.stock_alert UNION ALL SELECT pv.id, pv.variation_sku as product_code, p.product_name, pv.variation_name, pv.variation_type, COALESCE(SUM(ws.quantity), 0) as quantity, pv.variation_stock_alert as alert_quantity, u.unit_name as unit FROM product_variations pv JOIN products p ON pv.product_id = p.id LEFT JOIN warehouse_stock ws ON pv.id = ws.variation_id LEFT JOIN units u ON p.sale_unit_id = u.id WHERE p.product_type = 'variable' GROUP BY pv.id, pv.variation_sku, p.product_name, pv.variation_name, pv.variation_type, pv.variation_stock_alert, u.unit_name HAVING quantity <= pv.variation_stock_alert ORDER BY quantity ASC LIMIT 10 """) products = cursor.fetchall() result = [] for product in products: # ✅ Format display name based on product type if product['variation_name'] and product['variation_type']: # Variable product: Product Name - Variation Name (Variation Type) display_name = f"{product['product_name']} - {product['variation_name']} ({product['variation_type']})" else: # Single product: Product Name only display_name = product['product_name'] result.append({ "product_id": product['id'], "product_code": product['product_code'] or 'N/A', "product_name": display_name, "quantity": float(product['quantity'] or 0), "alert_quantity": float(product['alert_quantity'] or 0), "unit": product['unit'] or 'units' }) return jsonify(result), 200 except Exception as e: logger.error(f"❌ Error in dashboard_view_product_alert: {e}") traceback.print_exc() return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # =========================== # 1️⃣ SUPPLIER SUMMARY (Updated) # =========================== @report_bp.route('/get_supplier_summary', methods=['GET']) @jwt_required() @role_required('admin') def get_supplier_summary(): """ Get supplier summary with both Purchase Orders and GRN data Enhanced version with store/warehouse filtering support """ conn = None cursor = None try: # Get optional filters from query params store_id = request.args.get('store_id', type=int) warehouse_id = request.args.get('warehouse_id', type=int) conn = get_db_connection() cursor = conn.cursor() # Build dynamic query with filters base_query = """ SELECT s.id AS supplier_id, s.supplier_name, s.supplier_contact, -- Purchase Order Stats COUNT(DISTINCT po.order_id) AS purchase_count, COALESCE(SUM(po.grand_total), 0) AS total_purchase, -- GRN Stats COUNT(DISTINCT g.grn_id) AS grn_count, COALESCE(SUM(g.grand_total), 0) AS total_grn FROM suppliers s LEFT JOIN purchase_orders po ON s.id = po.supplier_id LEFT JOIN grn g ON s.id = g.supplier_id """ # Add filters conditions = [] params = [] if store_id: conditions.append("(po.store_id = %s OR g.store_id = %s)") params.extend([store_id, store_id]) if warehouse_id: conditions.append("(po.warehouse_id = %s OR g.warehouse_id = %s)") params.extend([warehouse_id, warehouse_id]) if conditions: base_query += " WHERE " + " AND ".join(conditions) base_query += """ GROUP BY s.id, s.supplier_name, s.supplier_contact ORDER BY total_purchase DESC """ cursor.execute(base_query, params) rows = cursor.fetchall() # Format response data = [] for row in rows: data.append({ "supplier_id": row[0], "supplier_name": row[1], "contact_number": row[2] if row[2] else "-", "purchase_count": row[3] if row[3] else 0, "total_purchase": float(row[4]) if row[4] else 0.0, "grn_count": row[5] if row[5] else 0, "total_grn": float(row[6]) if row[6] else 0.0 }) return jsonify(data), 200 except Error as db_err: print(f"❌ Database error: {db_err}") return jsonify({"error": str(db_err)}), 500 except Exception as e: print(f"❌ Error: {e}") return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # =========================== # 2️⃣ SUPPLIER ORDERS (Updated with variation_type) # =========================== @report_bp.route('/get_supplier_orders/', methods=['GET']) @jwt_required() @role_required('admin') def get_supplier_orders(supplier_id): """ Get all purchase orders for supplier with variation details """ conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor() cursor.execute(""" SELECT po.order_id, po.note, po.order_tax, po.discount AS order_discount, po.status, po.grn_status, po.grand_total, po.payment_status, po.paid_amount, po.due_amount, po.created_on AS order_date, oi.product_id, oi.variation_id, oi.quantity, oi.discount AS item_discount, oi.tax AS item_tax, oi.subtotal, oi.unit_price, oi.net_unit_cost, oi.selling_price, p.product_name, p.sku, v.variation_name, v.variation_type, v.variation_sku, v.variation_cost, v.variation_price, s.supplier_name FROM purchase_orders po LEFT JOIN order_items oi ON po.order_id = oi.order_id LEFT JOIN products p ON oi.product_id = p.id LEFT JOIN product_variations v ON oi.variation_id = v.id LEFT JOIN suppliers s ON po.supplier_id = s.id WHERE po.supplier_id = %s ORDER BY po.order_id DESC """, (supplier_id,)) rows = cursor.fetchall() orders = {} for row in rows: order_id = row[0] if order_id not in orders: orders[order_id] = { 'order_id': order_id, 'note': row[1], 'order_tax': float(row[2]) if row[2] else 0.0, 'order_discount': float(row[3]) if row[3] else 0.0, 'status': row[4], 'grn_status': row[5], 'grand_total': float(row[6]) if row[6] else 0.0, 'payment_status': row[7], 'paid_amount': float(row[8]) if row[8] else 0.0, 'due_amount': float(row[9]) if row[9] else 0.0, 'order_date': row[10].strftime("%Y-%m-%d %H:%M:%S") if row[10] else None, 'supplier_name': row[27], 'items': [] } if row[11] is not None: # product_id exists orders[order_id]['items'].append({ 'product_id': row[11], 'variation_id': row[12], 'quantity': float(row[13]) if row[13] else 0.0, 'item_discount': float(row[14]) if row[14] else 0.0, 'item_tax': float(row[15]) if row[15] else 0.0, 'subtotal': float(row[16]) if row[16] else 0.0, 'unit_price': float(row[17]) if row[17] else 0.0, 'net_unit_cost': float(row[18]) if row[18] else 0.0, 'selling_price': float(row[19]) if row[19] else 0.0, 'product_name': row[20], 'sku': row[21], 'variation_name': row[22], 'variation_type': row[23], # ✅ Added variation_type 'variation_sku': row[24], 'variation_cost': float(row[25]) if row[25] else 0.0, 'variation_price': float(row[26]) if row[26] else 0.0 }) return jsonify(list(orders.values())), 200 except Exception as e: print(f"❌ Error: {e}") return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # =========================== # 3️⃣ SUPPLIER PURCHASE RETURNS (✅ Updated with variation_type) # =========================== @report_bp.route('/get_supplier_return_orders/', methods=['GET']) @jwt_required() @role_required('admin') def get_supplier_return_orders(supplier_id): """ Get purchase returns for supplier with variation details """ conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT pr.return_id, po.order_id AS purchase_id, s.supplier_name, pr.return_status, pr.return_total, pr.created_on, po.grand_total AS purchase_total, (po.grand_total - pr.return_total) AS due_amount, pri.product_id, pri.variation_id, pri.quantity, pri.discount AS item_discount, pri.tax AS item_tax, pri.subtotal, p.product_name, p.sku, v.variation_name, v.variation_type, v.variation_sku FROM purchase_returns pr JOIN purchase_orders po ON pr.order_id = po.order_id JOIN suppliers s ON pr.supplier_id = s.id LEFT JOIN purchase_return_items pri ON pr.return_id = pri.return_id LEFT JOIN products p ON pri.product_id = p.id LEFT JOIN product_variations v ON pri.variation_id = v.id WHERE pr.supplier_id = %s ORDER BY pr.created_on DESC """, (supplier_id,)) rows = cursor.fetchall() returns = {} for row in rows: return_id = row['return_id'] if return_id not in returns: returns[return_id] = { 'return_id': return_id, 'purchase_id': row['purchase_id'], 'supplier_name': row['supplier_name'], 'return_status': row['return_status'], 'return_total': float(row['return_total']) if row['return_total'] else 0.0, 'created_on': row['created_on'].strftime("%Y-%m-%d %H:%M:%S") if row['created_on'] else None, 'purchase_total': float(row['purchase_total']) if row['purchase_total'] else 0.0, 'due_amount': float(row['due_amount']) if row['due_amount'] else 0.0, 'items': [] } if row['product_id'] is not None: returns[return_id]['items'].append({ 'product_id': row['product_id'], 'variation_id': row['variation_id'], 'quantity': float(row['quantity']) if row['quantity'] else 0.0, 'item_discount': float(row['item_discount']) if row['item_discount'] else 0.0, 'item_tax': float(row['item_tax']) if row['item_tax'] else 0.0, 'subtotal': float(row['subtotal']) if row['subtotal'] else 0.0, 'product_name': row['product_name'], 'sku': row['sku'], # ✅ Added SKU 'variation_name': row['variation_name'], 'variation_type': row['variation_type'], # ✅ Added variation_type 'variation_sku': row['variation_sku'] # ✅ Added variation_sku }) return jsonify(list(returns.values())), 200 except Exception as e: print(f"❌ Error: {e}") return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # =========================== # 4️⃣ SUPPLIER GRN RECORDS (Updated with variation_type) # =========================== @report_bp.route('/get_supplier_grn/', methods=['GET']) @jwt_required() @role_required('admin') def get_supplier_grn(supplier_id): """ Get all GRN records for a supplier with variation details """ conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT g.grn_id, g.grn_code, g.purchase_order_id, g.grn_date, g.invoice_number, g.status, g.grand_total, g.payment_status, g.paid_amount, g.due_amount, po.order_id AS po_number, s.supplier_name, gi.product_id, gi.received_quantity, gi.accepted_quantity, gi.rejected_quantity, gi.unit_price, gi.subtotal, p.product_name, p.sku, v.variation_name, v.variation_type, v.variation_sku FROM grn g JOIN suppliers s ON g.supplier_id = s.id LEFT JOIN purchase_orders po ON g.purchase_order_id = po.order_id LEFT JOIN grn_items gi ON g.grn_id = gi.grn_id LEFT JOIN products p ON gi.product_id = p.id LEFT JOIN product_variations v ON gi.variation_id = v.id WHERE g.supplier_id = %s ORDER BY g.grn_date DESC """, (supplier_id,)) rows = cursor.fetchall() grns = {} for row in rows: grn_id = row['grn_id'] if grn_id not in grns: grns[grn_id] = { 'grn_id': grn_id, 'grn_code': row['grn_code'], 'purchase_order_id': row['purchase_order_id'], 'po_number': row['po_number'], 'grn_date': row['grn_date'].strftime("%Y-%m-%d") if row['grn_date'] else None, 'invoice_number': row['invoice_number'], 'status': row['status'], 'grand_total': float(row['grand_total']) if row['grand_total'] else 0.0, 'payment_status': row['payment_status'], 'paid_amount': float(row['paid_amount']) if row['paid_amount'] else 0.0, 'due_amount': float(row['due_amount']) if row['due_amount'] else 0.0, 'supplier_name': row['supplier_name'], 'items': [] } if row['product_id'] is not None: grns[grn_id]['items'].append({ 'product_id': row['product_id'], 'product_name': row['product_name'], 'sku': row['sku'], # ✅ Added SKU 'variation_name': row['variation_name'], 'variation_type': row['variation_type'], # ✅ Added variation_type 'variation_sku': row['variation_sku'], # ✅ Added variation_sku 'received_quantity': float(row['received_quantity']) if row['received_quantity'] else 0.0, 'accepted_quantity': float(row['accepted_quantity']) if row['accepted_quantity'] else 0.0, 'rejected_quantity': float(row['rejected_quantity']) if row['rejected_quantity'] else 0.0, 'unit_price': float(row['unit_price']) if row['unit_price'] else 0.0, 'subtotal': float(row['subtotal']) if row['subtotal'] else 0.0 }) return jsonify(list(grns.values())), 200 except Exception as e: print(f"❌ Error: {e}") return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() @report_bp.route('/get_customer_summary', methods=['GET']) @jwt_required() @role_required('admin') def get_customer_summary(): """ Get customer summary with total invoices, sales, and breakdown. This is a POS (Point of Sale) system for hardware/retail sales. - Uses 'customers' table - Uses 'invoice_sale' table for sales transactions - Sale products are in 'sale_products' table - Supports product variations, batch tracking, and warehouse management """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: # Main query: customers who have invoices cursor.execute(""" SELECT c.id AS customer_id, c.name AS customer_name, c.contact AS contact_number, c.email AS email, COUNT(DISTINCT i.invoice_id) AS total_invoices, IFNULL(SUM(i.invoiceTotal), 0) AS total_sales, IFNULL(SUM(i.discount), 0) AS total_discount, IFNULL(SUM(i.tax), 0) AS total_tax FROM customers c INNER JOIN invoice_sale i ON i.customer_id = c.id WHERE i.payment_status IN ('paid', 'partial', 'unpaid') GROUP BY c.id, c.name, c.contact, c.email HAVING total_invoices > 0 ORDER BY total_sales DESC """) customers = cursor.fetchall() # Calculate detailed breakdown for each customer for c in customers: customer_id = c['customer_id'] # Get total hardware/products sold to this customer cursor.execute(""" SELECT IFNULL(SUM(sp.total), 0) AS total_products_value, IFNULL(SUM(sp.quantity), 0) AS total_items_sold FROM sale_products sp INNER JOIN invoice_sale i ON sp.invoice_id = i.invoice_id WHERE i.customer_id = %s """, (customer_id,)) product_data = cursor.fetchone() c['total_products_value'] = float(product_data['total_products_value']) c['total_items_sold'] = float(product_data['total_items_sold']) # Get payment status breakdown cursor.execute(""" SELECT payment_status, COUNT(*) AS count, SUM(invoiceTotal) AS amount FROM invoice_sale WHERE customer_id = %s GROUP BY payment_status """, (customer_id,)) payment_breakdown = cursor.fetchall() c['payment_breakdown'] = { row['payment_status']: { 'count': row['count'], 'amount': float(row['amount']) } for row in payment_breakdown } # Calculate outstanding amount (unpaid + partial) cursor.execute(""" SELECT IFNULL(SUM(remainingAmount), 0) AS outstanding_amount FROM invoice_sale WHERE customer_id = %s AND payment_status IN ('partial', 'unpaid') """, (customer_id,)) c['outstanding_amount'] = float(cursor.fetchone()['outstanding_amount']) # Get last purchase date cursor.execute(""" SELECT MAX(sale_date) AS last_purchase_date FROM invoice_sale WHERE customer_id = %s """, (customer_id,)) last_purchase = cursor.fetchone()['last_purchase_date'] c['last_purchase_date'] = last_purchase.isoformat() if last_purchase else None # Ensure numeric fields are float c['total_sales'] = float(c['total_sales']) c['total_discount'] = float(c['total_discount']) c['total_tax'] = float(c['total_tax']) return jsonify({ "success": True, "customers": customers, "total_customers": len(customers) }), 200 except Exception as e: print("Error fetching customer summary:", e) import traceback traceback.print_exc() return jsonify({ "success": False, "error": str(e) }), 500 finally: cursor.close() conn.close() @report_bp.route('/get_customer_sales/', methods=['GET']) @jwt_required() @role_required('admin', 'manager', 'cashier') def get_customer_sales(customer_id): """ Get all sales/invoices for a specific customer in hardware POS system. Shows ALL statuses: received, suspended, cancelled, etc. Returns: - Invoice details (all statuses included) - Product totals - Payment information - Status breakdown """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: # ✅ Fetch ALL invoices for this customer - NO status filter query = """ SELECT i.invoice_id, i.invoice_code, c.name AS customer_name, c.contact, c.email, i.status, i.invoiceTotal AS grand_total, i.payment_status, i.payment_method, i.tenderAmount, i.remainingAmount, i.discount, i.tax, i.sale_date, s.store_name, w.warehouse_name, u.name AS cashier_name FROM invoice_sale i JOIN customers c ON i.customer_id = c.id LEFT JOIN stores s ON i.store_id = s.id LEFT JOIN warehouses w ON i.warehouse_id = w.id LEFT JOIN users u ON i.cashier_user_id = u.id WHERE c.id = %s ORDER BY i.sale_date DESC """ cursor.execute(query, (customer_id,)) sales = cursor.fetchall() if not sales: return jsonify({ "success": True, "sales": [], "total_sales": 0, "message": "No sales found for this customer" }), 200 # For each invoice, fetch product details and totals for s in sales: invoice_id = s['invoice_id'] # ✅ Get total products sold cursor.execute(""" SELECT IFNULL(SUM(sp.total), 0) AS total_products, IFNULL(SUM(sp.quantity), 0) AS total_quantity, IFNULL(SUM(sp.discount), 0) AS product_discount, IFNULL(SUM(sp.tax), 0) AS product_tax FROM sale_products sp WHERE sp.invoice_id = %s """, (invoice_id,)) product_data = cursor.fetchone() s['total_products'] = float(product_data['total_products']) s['total_quantity'] = float(product_data['total_quantity']) s['product_discount'] = float(product_data['product_discount']) s['product_tax'] = float(product_data['product_tax']) # ✅ Get product details for this invoice cursor.execute(""" SELECT sp.sale_product_id, p.product_name, pv.variation_name, sp.quantity, sp.price, sp.unit_price, sp.total, sp.discount, sp.tax, sp.sale_unit FROM sale_products sp JOIN products p ON sp.product_id = p.id LEFT JOIN product_variations pv ON sp.variation_id = pv.id WHERE sp.invoice_id = %s """, (invoice_id,)) products = cursor.fetchall() # Convert numeric fields in products for p in products: p['quantity'] = float(p.get('quantity', 0)) p['price'] = float(p.get('price', 0)) p['unit_price'] = float(p.get('unit_price', 0)) p['total'] = float(p.get('total', 0)) p['discount'] = float(p.get('discount', 0)) if p.get('discount') else 0.0 p['tax'] = float(p.get('tax', 0)) if p.get('tax') else 0.0 s['products'] = products # ✅ Get payment breakdown if multiple payment methods used cursor.execute(""" SELECT pm.method_name, spm.amount, spm.transaction_reference, spm.payment_date FROM sale_payments spm JOIN payment_methods pm ON spm.payment_method = pm.id WHERE spm.sale_id = %s ORDER BY spm.payment_date """, (invoice_id,)) payments = cursor.fetchall() # Convert payment amounts for payment in payments: payment['amount'] = float(payment.get('amount', 0)) if payment.get('payment_date'): payment['payment_date'] = payment['payment_date'].strftime('%Y-%m-%d %H:%M:%S') s['payments'] = payments # Ensure numeric fields are float s['grand_total'] = float(s['grand_total']) s['tenderAmount'] = float(s.get('tenderAmount', 0)) s['remainingAmount'] = float(s.get('remainingAmount', 0)) s['discount'] = float(s.get('discount', 0)) s['tax'] = float(s.get('tax', 0)) # Format date if s.get('sale_date'): s['sale_date'] = s['sale_date'].strftime('%Y-%m-%d %H:%M:%S') # ✅ Calculate status breakdown status_counts = {} status_totals = {} for s in sales: status = s['status'] status_counts[status] = status_counts.get(status, 0) + 1 status_totals[status] = status_totals.get(status, 0.0) + float(s['grand_total']) return jsonify({ "success": True, "sales": sales, "total_sales": len(sales), "status_breakdown": { "counts": status_counts, "totals": {k: round(v, 2) for k, v in status_totals.items()} } }), 200 except Exception as e: print("❌ Error fetching customer sales:", e) import traceback traceback.print_exc() return jsonify({ "success": False, "error": str(e) }), 500 finally: cursor.close() conn.close() @report_bp.route('/get_customer_sales_return/', methods=['GET']) @jwt_required() @role_required('admin', 'manager') def get_customer_sales_return(customer_id): """ Get all sale returns for a specific customer in hardware POS system. Shows ALL statuses. Returns: - Return details (all statuses) - Returned products - Return totals """ try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) # ✅ Fetch ALL sale returns for this customer - NO status filter query = """ SELECT sr.return_id, sr.return_date, sr.invoice_id, sr.reason, sr.total_return_amount, sr.order_tax, sr.order_discount, sr.status, inv.invoice_code, c.name AS customer_name, c.contact, s.store_name, w.warehouse_name FROM sale_return sr JOIN invoice_sale inv ON sr.invoice_id = inv.invoice_id JOIN customers c ON sr.customer_id = c.id LEFT JOIN stores s ON sr.store_id = s.id LEFT JOIN warehouses w ON sr.warehouse_id = w.id WHERE c.id = %s ORDER BY sr.return_date DESC """ cursor.execute(query, (customer_id,)) sale_returns = cursor.fetchall() if not sale_returns: return jsonify({ "success": True, "sale_returns": [], "total_returns": 0, "message": "No returns found for this customer" }), 200 # Add returned products and calculate totals for r in sale_returns: # Format date if r.get('return_date'): r['return_date'] = r['return_date'].strftime('%Y-%m-%d %H:%M:%S') # ✅ Fetch returned products for this return cursor.execute(""" SELECT srp.return_product_id, p.product_name, pv.variation_name, srp.return_quantity, srp.return_price, srp.return_total, srp.unit_price, srp.sale_unit, srp.discount, srp.tax, srp.product_discount, srp.product_tax FROM sale_return_products srp JOIN products p ON srp.product_id = p.id LEFT JOIN product_variations pv ON srp.variation_id = pv.id WHERE srp.return_id = %s """, (r['return_id'],)) products = cursor.fetchall() # ✅ Calculate totals from returned products total_products = 0 total_quantity = 0 total_discount = 0 total_tax = 0 for p in products: return_total = float(p.get('return_total', 0)) return_qty = float(p.get('return_quantity', 0)) discount = float(p.get('discount', 0)) if p.get('discount') else 0.0 tax = float(p.get('tax', 0)) if p.get('tax') else 0.0 total_products += return_total total_quantity += return_qty total_discount += discount total_tax += tax # Ensure all numeric fields are float p['return_total'] = return_total p['return_quantity'] = return_qty p['return_price'] = float(p.get('return_price', 0)) p['unit_price'] = float(p.get('unit_price', 0)) p['discount'] = discount p['tax'] = tax p['product_discount'] = float(p.get('product_discount', 0)) if p.get('product_discount') else 0.0 p['product_tax'] = float(p.get('product_tax', 0)) if p.get('product_tax') else 0.0 r['products'] = products r['total_products'] = round(total_products, 2) r['total_quantity'] = round(total_quantity, 2) r['total_discount'] = round(total_discount, 2) r['total_tax'] = round(total_tax, 2) # Ensure numeric fields are float r['total_return_amount'] = float(r.get('total_return_amount', 0)) r['order_tax'] = float(r.get('order_tax', 0)) r['order_discount'] = float(r.get('order_discount', 0)) # Calculate grand total r['grand_total'] = r['total_return_amount'] # ✅ Calculate status breakdown status_counts = {} status_totals = {} for r in sale_returns: status = r['status'] status_counts[status] = status_counts.get(status, 0) + 1 status_totals[status] = status_totals.get(status, 0.0) + float(r['total_return_amount']) return jsonify({ "success": True, "sale_returns": sale_returns, "total_returns": len(sale_returns), "status_breakdown": { "counts": status_counts, "totals": {k: round(v, 2) for k, v in status_totals.items()} } }), 200 except Exception as e: print("❌ Error fetching customer sale returns:", e) import traceback traceback.print_exc() return jsonify({ "success": False, "error": str(e) }), 500 finally: cursor.close() conn.close() # ✅ Optional: Filter by specific status @report_bp.route('/get_customer_sales//status/', methods=['GET']) @jwt_required() @role_required('admin', 'manager', 'cashier') def get_customer_sales_by_status(customer_id, status): """ Get customer sales filtered by specific status. Usage: /get_customer_sales/1/status/received /get_customer_sales/1/status/suspended """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: query = """ SELECT i.invoice_id, i.invoice_code, i.status, i.invoiceTotal AS grand_total, i.payment_status, i.sale_date, c.name AS customer_name FROM invoice_sale i JOIN customers c ON i.customer_id = c.id WHERE c.id = %s AND i.status = %s ORDER BY i.sale_date DESC """ cursor.execute(query, (customer_id, status)) sales = cursor.fetchall() return jsonify({ "success": True, "sales": sales, "total_sales": len(sales), "filtered_by_status": status }), 200 except Exception as e: return jsonify({ "success": False, "error": str(e) }), 500 finally: cursor.close() conn.close() @report_bp.route('/get_best_customer_summary', methods=['GET']) @jwt_required() @role_required('admin') def get_best_customer_summary(): conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: cursor.execute(""" SELECT c.id AS customer_id, c.name AS customer_name, c.contact AS contact_number, COUNT(DISTINCT i.invoice_id) AS sale_count, IFNULL(SUM(i.invoiceTotal), 0) AS total_sales FROM customers c JOIN invoice_sale i ON i.customer_id = c.id GROUP BY c.id, c.name, c.contact HAVING total_sales > 0 ORDER BY total_sales DESC """) # ✅ LIMIT 10 අයින් කළා customers = cursor.fetchall() processed_customers = [] for c in customers: processed_customers.append({ "Name": c['customer_name'] if c['customer_name'] else 'Walk-in Customer', "Phone": c['contact_number'] if c['contact_number'] else 'N/A', "Sale Count": c['sale_count'], "Total Amount": float(c['total_sales']) }) return jsonify({"best_customers": processed_customers}), 200 except Exception as e: print("Error fetching best customers:", e) return jsonify({"error": str(e)}), 500 finally: cursor.close() conn.close() @report_bp.route('/dashboard_get_best_customer_summary', methods=['GET']) @jwt_required() @role_required('admin', 'user', 'cashier') def dashboard_get_best_customer_summary(): """ Get top 5 customers by total sales ✅ Fixed: Changed 'phone' to 'contact' (correct column name in customers table) """ conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT c.id, c.name AS Name, c.email, c.contact, COUNT(DISTINCT i.invoice_id) as total_invoices, SUM(i.invoiceTotal) as total_amount FROM customers c JOIN invoice_sale i ON c.id = i.customer_id WHERE i.status = 'received' GROUP BY c.id, c.name, c.email, c.contact ORDER BY total_amount DESC LIMIT 5 """) customers = cursor.fetchall() result = [] for customer in customers: result.append({ "customer_id": customer['id'], "Name": customer['Name'], "email": customer['email'], "contact": customer['contact'], # ✅ Changed from 'phone' to 'contact' "total_invoices": customer['total_invoices'], "Total Amount": float(customer['total_amount'] or 0) }) return jsonify({ "message": "Best customers fetched successfully", "best_customers": result }), 200 except Exception as e: logger.error(f"❌ Error in dashboard_get_best_customer_summary: {e}") traceback.print_exc() return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() @report_bp.route('/all_purchase_return_details', methods=['GET']) @jwt_required() @role_required('admin') def all_purchase_return_details(): try: start_date = request.args.get('start_date') end_date = request.args.get('end_date') if not start_date or not end_date: return jsonify({"error": "start_date and end_date are required"}), 400 conn = get_db_connection() if not conn: return jsonify({"error": "Failed to connect to database"}), 500 cursor = conn.cursor() # Fetch purchase return details in date range query = """ SELECT DATE(pr.created_on) AS return_date, p.product_name, pri.quantity, pri.subtotal, pri.product_discount, pri.product_tax FROM purchase_return_items pri JOIN products p ON pri.product_id = p.id JOIN purchase_returns pr ON pri.return_id = pr.return_id WHERE DATE(pr.created_on) BETWEEN %s AND %s ORDER BY pr.created_on DESC; """ cursor.execute(query, (start_date, end_date)) results = cursor.fetchall() # Total purchase return amount in date range total_query = """ SELECT SUM(pri.subtotal) FROM purchase_return_items pri JOIN purchase_returns pr ON pri.return_id = pr.return_id WHERE DATE(pr.created_on) BETWEEN %s AND %s; """ cursor.execute(total_query, (start_date, end_date)) total_amount = cursor.fetchone()[0] or 0.0 # Format results for JSON purchase_return_details = [] for row in results: purchase_return_details.append({ "return_date": str(row[0]), "product_name": row[1], "quantity": row[2], "subtotal": str(Decimal(row[3])), "product_discount": str(Decimal(row[4])), "product_tax": str(Decimal(row[5])) }) cursor.close() conn.close() return jsonify({ "total_purchase_amount": str(Decimal(total_amount)), "purchase_return_details": purchase_return_details }), 200 except Exception as e: print(f"Error: {str(e)}") return jsonify({"error": "An error occurred while fetching purchase return details"}), 500 # ============================================ # 1️⃣2️⃣ DASHBOARD LAST 7 DAYS SALES # ============================================ @report_bp.route('/dashboard_last_7_days_sales', methods=['GET']) @jwt_required() @role_required('admin', 'cashier') def dashboard_last_7_days_sales(): """ Get last 7 days sales data for dashboard charts """ conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) status_filter = request.args.get('status') # Dates today = datetime.today() seven_days_ago = today - timedelta(days=7) # Base query base_query = """ SELECT i.invoice_id, i.invoice_code, i.tenderAmount, i.remainingAmount, i.invoiceTotal, i.payment_method, i.customer_id, c.name AS customer_name, i.sale_date, i.status, COALESCE((SELECT SUM(sp.total) FROM sale_products sp WHERE sp.invoice_id = i.invoice_id), 0) AS total_medicine_amount FROM invoice_sale i LEFT JOIN customers c ON i.customer_id = c.id WHERE i.sale_date >= %s """ if status_filter: base_query += " AND i.status = %s" cursor.execute(base_query + " ORDER BY i.sale_date DESC", (seven_days_ago, status_filter)) else: cursor.execute(base_query + " ORDER BY i.sale_date DESC", (seven_days_ago,)) invoices = cursor.fetchall() all_invoice_details = [] total_medicine_per_day = {} # Daily totals for last 7 days for day in range(7): day_date = today - timedelta(days=day) day_start = day_date.replace(hour=0, minute=0, second=0, microsecond=0) day_end = day_date.replace(hour=23, minute=59, second=59, microsecond=999999) cursor.execute(""" SELECT COALESCE(SUM(sp.total), 0) as total FROM sale_products sp JOIN invoice_sale i ON sp.invoice_id = i.invoice_id WHERE i.sale_date BETWEEN %s AND %s """, (day_start, day_end)) result = cursor.fetchone() total_medicine_per_day[day_date.strftime('%Y-%m-%d')] = float(result['total'] or 0) # Today's totals today_start = today.replace(hour=0, minute=0, second=0, microsecond=0) today_end = today.replace(hour=23, minute=59, second=59, microsecond=999999) cursor.execute(""" SELECT COALESCE(SUM(sp.total), 0) as total FROM sale_products sp JOIN invoice_sale i ON sp.invoice_id = i.invoice_id WHERE i.sale_date BETWEEN %s AND %s """, (today_start, today_end)) result = cursor.fetchone() total_medicine_today = float(result['total'] or 0) # Build invoice details for invoice in invoices: # Products cursor.execute(""" SELECT p.product_name, sp.price, sp.quantity, sp.total FROM sale_products sp JOIN products p ON sp.product_id = p.id WHERE sp.invoice_id = %s """, (invoice['invoice_id'],)) products = cursor.fetchall() invoice_detail = { "invoice_id": invoice['invoice_id'], "invoice_code": invoice['invoice_code'], "tenderAmount": float(invoice['tenderAmount'] or 0), "remainingAmount": float(invoice['remainingAmount'] or 0), "invoiceTotal": float(invoice['invoiceTotal'] or 0), "payment_method": invoice['payment_method'], "customer_id": invoice['customer_id'], "customer_name": invoice['customer_name'], "sale_date": format_datetime(invoice['sale_date']), "status": invoice['status'], "total_medicine_amount": float(invoice['total_medicine_amount'] or 0), "products": [ {"product_name": p['product_name'], "price": float(p['price'] or 0), "quantity": float(p['quantity'] or 0), "total": float(p['total'] or 0)} for p in products ] } all_invoice_details.append(invoice_detail) return jsonify({ "invoices": all_invoice_details, "total_medicine_today": total_medicine_today, "total_medicine_per_day": total_medicine_per_day }), 200 except Exception as e: logger.error(f"❌ Error in dashboard_last_7_days_sales: {e}") traceback.print_exc() return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ============================================ # 1️⃣3️⃣ LAST 7 DAYS PURCHASE ORDERS # ============================================ @report_bp.route('/last_7day_get_all_submit_order', methods=['GET']) @jwt_required() @role_required('admin', 'cashier') def last_7day_get_all_submit_order(): """ Get last 7 days purchase orders for dashboard """ conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) # Query to fetch orders and items cursor.execute(""" SELECT po.order_id, po.supplier_id, s.supplier_name, po.note, po.order_tax, po.discount, po.status, po.grand_total, po.created_on AS order_date, oi.product_id, oi.variation_id, oi.quantity, oi.discount AS item_discount, oi.tax AS item_tax, oi.subtotal, p.product_name, p.sku, v.variation_name, v.variation_sku, v.variation_cost, v.variation_price, po.payment_status FROM purchase_orders po LEFT JOIN order_items oi ON po.order_id = oi.order_id LEFT JOIN products p ON oi.product_id = p.id LEFT JOIN suppliers s ON po.supplier_id = s.id LEFT JOIN product_variations v ON oi.variation_id = v.id ORDER BY po.order_id DESC """) rows = cursor.fetchall() orders = {} for row in rows: order_id = row['order_id'] if order_id not in orders: orders[order_id] = { 'order_id': order_id, 'supplier_id': row['supplier_id'], 'supplier_name': row['supplier_name'], 'note': row['note'], 'order_tax': float(row['order_tax'] or 0), 'discount': float(row['discount'] or 0), 'status': row['status'], 'grand_total': float(row['grand_total'] or 0), 'order_date': format_datetime(row['order_date']), 'payment_status': row['payment_status'], 'items': [] } # Only add item if product_id is not None if row['product_id'] is not None: orders[order_id]['items'].append({ 'product_id': row['product_id'], 'variation_id': row['variation_id'], 'quantity': float(row['quantity'] or 0), 'discount': float(row['item_discount'] or 0), 'tax': float(row['item_tax'] or 0), 'subtotal': float(row['subtotal'] or 0), 'product_name': row['product_name'], 'sku': row['sku'], 'variation_name': row['variation_name'], 'variation_sku': row['variation_sku'], 'variation_cost': float(row['variation_cost'] or 0), 'variation_price': float(row['variation_price'] or 0) }) # Calculate total purchase amounts for each day in the last 7 days total_purchase_per_day = {} today = datetime.today() for day in range(7): day_date = today - timedelta(days=day) day_start = day_date.replace(hour=0, minute=0, second=0, microsecond=0) day_end = day_date.replace(hour=23, minute=59, second=59, microsecond=999999) cursor.execute(""" SELECT COALESCE(SUM(po.grand_total), 0) as total FROM purchase_orders po WHERE po.created_on >= %s AND po.created_on <= %s """, (day_start, day_end)) result = cursor.fetchone() total_for_day = result['total'] or 0 total_purchase_per_day[day_date.strftime('%Y-%m-%d')] = float(total_for_day) return jsonify({ "orders": list(orders.values()), "total_purchase_per_day": total_purchase_per_day }), 200 except Exception as e: logger.error(f"❌ Error in last_7day_get_all_submit_order: {e}") traceback.print_exc() return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() """ ============================================ PROFIT & LOSS REPORT - COMPLETE BACKEND WITH GRN TRACKING ============================================ Complete backend API for Profit & Loss calculations with GRN integration Author: Claude AI Date: 2026-02-09 Version: 3.0.0 Features: - ✅ FIFO cost tracking using sale_product_items + GRN - ✅ Currency formatting with commas - ✅ GRN tracking and traceability - ✅ Batch-level cost tracking - ✅ Comprehensive error handling - ✅ Date range filtering - ✅ All P&L metrics with GRN details ============================================ """ from flask import Blueprint, request, jsonify from flask_jwt_extended import jwt_required, get_jwt_identity from decimal import Decimal, ROUND_HALF_UP from datetime import datetime, date, timedelta import traceback import mysql.connector from mysql.connector import Error from functools import wraps import logging # ============================================ # LOGGING CONFIGURATION # ============================================ logging.basicConfig( level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s' ) logger = logging.getLogger(__name__) # ============================================ # UTILITY FUNCTIONS # ============================================ def format_currency(amount): """Format decimal to string with commas - Shows exact value without rounding""" if amount is None: return "0.00" try: # Convert to float to handle Decimal objects amount_float = float(amount) # Format with many decimal places, then strip trailing zeros formatted = f"{amount_float:.10f}".rstrip('0').rstrip('.') # Split into integer and decimal parts if '.' in formatted: integer_part, decimal_part = formatted.split('.') # Ensure at least 2 decimal places if len(decimal_part) < 2: decimal_part = decimal_part.ljust(2, '0') else: integer_part = formatted decimal_part = "00" # Add commas to integer part integer_with_commas = "{:,}".format(int(float(integer_part))) return f"{integer_with_commas}.{decimal_part}" except (ValueError, TypeError): return "0.00" def to_decimal(value): """Safely convert to Decimal - No rounding, exact value""" if value is None: return Decimal('0.00') try: # Return exact value without rounding return Decimal(str(value)) except: return Decimal('0.00') def format_datetime(dt): """Format datetime to string""" if dt is None: return None if isinstance(dt, str): return dt return dt.strftime('%Y-%m-%d %H:%M:%S') # ============================================ # 1️⃣ TOTAL SALES # ============================================ @report_bp.route('/total_sales', methods=['GET']) @jwt_required() @role_required('admin', 'cashier', 'manager') def total_sales(): """ Calculate total sales from invoice_sale and sale_products """ conn = None cursor = None try: start_date = request.args.get('start_date') end_date = request.args.get('end_date') conn = get_db_connection() cursor = conn.cursor(dictionary=True) query = """ SELECT COALESCE(SUM(sp.total), 0) as total_sales FROM sale_products sp JOIN invoice_sale isale ON sp.invoice_id = isale.invoice_id WHERE isale.status IN ('received', 'suspended') """ params = [] if start_date and end_date: query += " AND DATE(isale.sale_date) BETWEEN %s AND %s" params.extend([start_date, end_date]) elif start_date: query += " AND DATE(isale.sale_date) >= %s" params.append(start_date) elif end_date: query += " AND DATE(isale.sale_date) <= %s" params.append(end_date) cursor.execute(query, tuple(params)) result = cursor.fetchone() total_sales = to_decimal(result['total_sales']) return jsonify({ "success": True, "total_sales": float(total_sales), "total_sales_formatted": format_currency(total_sales), "start_date": start_date, "end_date": end_date }), 200 except Exception as e: logger.error(f"❌ Error in total_sales: {e}") traceback.print_exc() return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ============================================ # 2️⃣ TOTAL SALE RETURNS # ============================================ @report_bp.route('/total_sale_returns', methods=['GET']) @jwt_required() @role_required('admin', 'cashier', 'manager') def total_sale_returns(): """Calculate total sale returns""" conn = None cursor = None try: start_date = request.args.get('start_date') end_date = request.args.get('end_date') conn = get_db_connection() cursor = conn.cursor(dictionary=True) query = """ SELECT COALESCE(SUM(srp.return_total), 0) as total_sale_returns FROM sale_return_products srp JOIN sale_return sr ON srp.return_id = sr.return_id WHERE 1=1 """ params = [] if start_date and end_date: query += " AND DATE(sr.return_date) BETWEEN %s AND %s" params.extend([start_date, end_date]) elif start_date: query += " AND DATE(sr.return_date) >= %s" params.append(start_date) elif end_date: query += " AND DATE(sr.return_date) <= %s" params.append(end_date) cursor.execute(query, tuple(params)) result = cursor.fetchone() total_returns = to_decimal(result['total_sale_returns']) return jsonify({ "success": True, "total_sale_returns": float(total_returns), "total_sale_returns_formatted": format_currency(total_returns), "start_date": start_date, "end_date": end_date }), 200 except Exception as e: logger.error(f"❌ Error in total_sale_returns: {e}") traceback.print_exc() return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ============================================ # 3️⃣ TOTAL PURCHASES # ============================================ @report_bp.route('/total_submit_order', methods=['GET']) @jwt_required() @role_required('admin', 'manager') def total_submit_order(): """Calculate total purchases from purchase_orders""" conn = None cursor = None try: start_date = request.args.get('start_date') end_date = request.args.get('end_date') conn = get_db_connection() cursor = conn.cursor(dictionary=True) query = """ SELECT COALESCE(SUM(grand_total), 0) AS total_purchases FROM purchase_orders WHERE 1=1 """ params = [] if start_date and end_date: query += " AND DATE(created_on) BETWEEN %s AND %s" params.extend([start_date, end_date]) elif start_date: query += " AND DATE(created_on) >= %s" params.append(start_date) elif end_date: query += " AND DATE(created_on) <= %s" params.append(end_date) cursor.execute(query, tuple(params)) result = cursor.fetchone() total = to_decimal(result['total_purchases']) return jsonify({ 'success': True, 'total_purchases': float(total), 'total_purchases_formatted': format_currency(total), 'start_date': start_date, 'end_date': end_date }), 200 except Exception as e: logger.error(f"❌ Error in total_submit_order: {e}") traceback.print_exc() return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ============================================ # 4️⃣ TOTAL PURCHASE RETURNS # ============================================ @report_bp.route('/all_purchase_return', methods=['GET']) @jwt_required() @role_required('admin', 'manager') def all_purchase_return(): """Calculate total purchase returns""" conn = None cursor = None try: start_date = request.args.get('start_date') end_date = request.args.get('end_date') conn = get_db_connection() cursor = conn.cursor(dictionary=True) query = """ SELECT COALESCE(SUM(pri.subtotal), 0) as total_purchase_amount FROM purchase_return_items pri JOIN purchase_returns pr ON pri.return_id = pr.return_id WHERE 1=1 """ params = [] if start_date and end_date: query += " AND DATE(pr.created_on) BETWEEN %s AND %s" params.extend([start_date, end_date]) elif start_date: query += " AND DATE(pr.created_on) >= %s" params.append(start_date) elif end_date: query += " AND DATE(pr.created_on) <= %s" params.append(end_date) cursor.execute(query, tuple(params)) result = cursor.fetchone() total = to_decimal(result['total_purchase_amount']) return jsonify({ 'success': True, 'total_purchase_amount': float(total), 'total_purchase_amount_formatted': format_currency(total), 'start_date': start_date, 'end_date': end_date }), 200 except Exception as e: logger.error(f"❌ Error in all_purchase_return_details: {e}") traceback.print_exc() return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ============================================ # 5️⃣ TOTAL EXPENSES # ============================================ @report_bp.route('/total_expenses', methods=['GET']) @jwt_required() @role_required('admin', 'cashier', 'manager') def total_expenses(): """Calculate total expenses""" conn = None cursor = None try: start_date = request.args.get('start_date') end_date = request.args.get('end_date') conn = get_db_connection() cursor = conn.cursor(dictionary=True) query = """ SELECT COALESCE(SUM(amount), 0) as total_expenses FROM expenses WHERE 1=1 """ params = [] if start_date and end_date: query += " AND DATE(date) BETWEEN %s AND %s" params.extend([start_date, end_date]) elif start_date: query += " AND DATE(date) >= %s" params.append(start_date) elif end_date: query += " AND DATE(date) <= %s" params.append(end_date) cursor.execute(query, tuple(params)) result = cursor.fetchone() total_expenses = to_decimal(result['total_expenses']) return jsonify({ "success": True, "total_expenses": float(total_expenses), "total_expenses_formatted": format_currency(total_expenses), "start_date": start_date, "end_date": end_date }), 200 except Exception as e: logger.error(f"❌ Error in total_expenses: {e}") traceback.print_exc() return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ============================================ # 6️⃣ TOTAL SALE PRODUCT COST WITH GRN TRACKING (CRITICAL) # ============================================ @report_bp.route('/total_sale_product_cost_all', methods=['GET']) @jwt_required() @role_required('admin', 'cashier', 'manager') def total_sale_product_cost_all(): """ ✅ CRITICAL: Calculate total product cost using FIFO with GRN tracking Uses sale_product_items table for accurate batch-level cost tracking """ conn = None cursor = None try: start_date = request.args.get('start_date') end_date = request.args.get('end_date') conn = get_db_connection() cursor = conn.cursor(dictionary=True) # ✅ Main query to get invoices with FIFO costs from sale_product_items query = """ SELECT isale.invoice_id, isale.invoice_code, isale.sale_date, isale.invoiceTotal, isale.payment_method, isale.status, COALESCE(SUM(spi.quantity * spi.cost), 0) as total_product_cost FROM invoice_sale isale LEFT JOIN sale_products sp ON isale.invoice_id = sp.invoice_id LEFT JOIN sale_product_items spi ON sp.sale_product_id = spi.sale_product_id WHERE isale.status IN ('received', 'suspended') """ params = [] if start_date and end_date: query += " AND DATE(isale.sale_date) BETWEEN %s AND %s" params.extend([start_date, end_date]) elif start_date: query += " AND DATE(isale.sale_date) >= %s" params.append(start_date) elif end_date: query += " AND DATE(isale.sale_date) <= %s" params.append(end_date) query += " GROUP BY isale.invoice_id ORDER BY isale.sale_date DESC" cursor.execute(query, tuple(params)) invoices = cursor.fetchall() total_all_product_cost = Decimal('0.00') invoice_details = [] for invoice in invoices: invoice_cost = to_decimal(invoice['total_product_cost']) total_all_product_cost += invoice_cost # ✅ Get detailed product breakdown with GRN tracking cursor.execute(""" SELECT p.product_name, pv.variation_name, sp.price as selling_price, sp.quantity as total_quantity, spi.sale_product_item_id, spi.batch_id, spi.grn_id, spi.quantity as batch_quantity, spi.cost as batch_cost, spi.expiration_date as batch_expiry, pb.batch_number, grn.grn_code, grn.grn_date, (spi.quantity * spi.cost) as item_total_cost FROM sale_products sp JOIN products p ON sp.product_id = p.id LEFT JOIN product_variations pv ON sp.variation_id = pv.id LEFT JOIN sale_product_items spi ON sp.sale_product_id = spi.sale_product_id LEFT JOIN product_batches pb ON spi.batch_id = pb.batch_id LEFT JOIN grn ON spi.grn_id = grn.grn_id WHERE sp.invoice_id = %s ORDER BY sp.sale_product_id, spi.sale_product_item_id """, (invoice['invoice_id'],)) products = cursor.fetchall() product_list = [] for prod in products: product_list.append({ "product_name": prod['product_name'], "variation": prod['variation_name'] or 'N/A', "selling_price": float(prod['selling_price'] or 0), "total_quantity": float(prod['total_quantity'] or 0), "grn_tracking": { "grn_id": prod['grn_id'], "grn_code": prod['grn_code'], "grn_date": format_datetime(prod['grn_date']), "batch_id": prod['batch_id'], "batch_number": prod['batch_number'], "batch_quantity": float(prod['batch_quantity'] or 0), "batch_cost": float(prod['batch_cost'] or 0), "batch_expiry": str(prod['batch_expiry']) if prod['batch_expiry'] else None, "item_total_cost": float(prod['item_total_cost'] or 0) } }) invoice_details.append({ "invoice_id": invoice['invoice_id'], "invoice_code": invoice['invoice_code'], "sale_date": format_datetime(invoice['sale_date']), "invoiceTotal": float(invoice['invoiceTotal'] or 0), "payment_method": invoice['payment_method'], "status": invoice['status'], "total_product_cost": float(invoice_cost), "total_product_cost_formatted": format_currency(invoice_cost), "products": product_list }) return jsonify({ "success": True, "invoices": invoice_details, "total_all_sale_product_cost": float(total_all_product_cost), "total_all_sale_product_cost_formatted": format_currency(total_all_product_cost), "filtered": bool(start_date and end_date), "start_date": start_date, "end_date": end_date }), 200 except Exception as e: logger.error(f"❌ Error in total_sale_product_cost_all: {e}") traceback.print_exc() return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ============================================ # 7️⃣ PAYMENTS RECEIVED # ============================================ @report_bp.route('/payments_received', methods=['GET']) @jwt_required() @role_required('admin', 'manager') def payments_received(): """ Calculate total payments received from: 1. Sales (received + partial) 2. Purchase Returns """ conn = None cursor = None try: start_date = request.args.get('start_date') end_date = request.args.get('end_date') conn = get_db_connection() cursor = conn.cursor(dictionary=True) # Sales payments query_sales = """ SELECT COALESCE(SUM( CASE WHEN isale.status = 'received' THEN isale.invoiceTotal WHEN isale.status = 'suspended' AND isale.payment_status = 'partial' THEN isale.tenderAmount WHEN isale.status = 'suspended' AND isale.payment_status = 'paid' THEN isale.invoiceTotal ELSE 0 END ), 0) AS total_received FROM invoice_sale AS isale WHERE ( isale.status = 'received' OR (isale.status = 'suspended' AND isale.payment_status IN ('partial', 'paid')) ) """ params_sales = [] if start_date and end_date: query_sales += " AND DATE(isale.sale_date) BETWEEN %s AND %s" params_sales.extend([start_date, end_date]) elif start_date: query_sales += " AND DATE(isale.sale_date) >= %s" params_sales.append(start_date) elif end_date: query_sales += " AND DATE(isale.sale_date) <= %s" params_sales.append(end_date) cursor.execute(query_sales, tuple(params_sales)) sales_result = cursor.fetchone() total_sales = to_decimal(sales_result['total_received']) # Purchase returns query_returns = """ SELECT COALESCE(SUM(pri.subtotal), 0) as total_returns FROM purchase_return_items pri JOIN purchase_returns pr ON pri.return_id = pr.return_id WHERE 1=1 """ params_returns = [] if start_date and end_date: query_returns += " AND DATE(pr.created_on) BETWEEN %s AND %s" params_returns.extend([start_date, end_date]) elif start_date: query_returns += " AND DATE(pr.created_on) >= %s" params_returns.append(start_date) elif end_date: query_returns += " AND DATE(pr.created_on) <= %s" params_returns.append(end_date) cursor.execute(query_returns, tuple(params_returns)) returns_result = cursor.fetchone() total_returns = to_decimal(returns_result['total_returns']) total_payments_received = total_sales + total_returns return jsonify({ "success": True, "total_payments_received": float(total_payments_received), "total_payments_received_formatted": format_currency(total_payments_received), "sales_received": float(total_sales), "sales_received_formatted": format_currency(total_sales), "purchase_returns": float(total_returns), "purchase_returns_formatted": format_currency(total_returns), "start_date": start_date, "end_date": end_date }), 200 except Exception as e: logger.error(f"❌ Error in payments_received: {e}") traceback.print_exc() return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() @report_bp.route('/profit_loss_summary', methods=['GET']) @jwt_required() @role_required('admin', 'manager') def profit_loss_summary(): """ ✅ Complete Profit & Loss Summary - Total Sales : sale_products.total (received/suspended invoices) - Sales Returns : sale_return_products.return_total - Total Purchases : grn grand_total (actually RECEIVED stock only) - Purchase Returns : purchase_return_items.subtotal - Expenses : expenses.amount - COGS : sale_product_items (FIFO batch cost) - Revenue : Sales - Sales Returns - Gross Profit : Revenue - COGS - Net Profit : Gross Profit - Expenses - Cash Collected : tenderAmount from received/partial invoices """ conn = None cursor = None try: start_date = request.args.get('start_date') end_date = request.args.get('end_date') if not start_date or not end_date: return jsonify({ 'success': False, 'error': 'start_date and end_date are required' }), 400 conn = get_db_connection() cursor = conn.cursor(dictionary=True) # ───────────────────────────────────────── # 1️⃣ TOTAL SALES # sale_products.total (includes tax/discount already) # only received / suspended invoices # ───────────────────────────────────────── cursor.execute(""" SELECT COALESCE(SUM(sp.total), 0) AS total FROM sale_products sp JOIN invoice_sale isale ON sp.invoice_id = isale.invoice_id WHERE isale.status IN ('received', 'suspended') AND DATE(isale.sale_date) BETWEEN %s AND %s """, (start_date, end_date)) total_sales = to_decimal(cursor.fetchone()['total']) # ───────────────────────────────────────── # 2️⃣ SALES RETURNS # ───────────────────────────────────────── cursor.execute(""" SELECT COALESCE(SUM(srp.return_total), 0) AS total FROM sale_return_products srp JOIN sale_return sr ON srp.return_id = sr.return_id WHERE DATE(sr.return_date) BETWEEN %s AND %s """, (start_date, end_date)) sales_returns = to_decimal(cursor.fetchone()['total']) # ───────────────────────────────────────── # 3️⃣ TOTAL PURCHASES # ✅ Use GRN grand_total (actually received stock) # NOT purchase_orders (only ordered, may not be received) # ───────────────────────────────────────── cursor.execute(""" SELECT COALESCE(SUM(g.grand_total), 0) AS total FROM grn g WHERE g.status IN ('completed', 'partial') AND DATE(g.grn_date) BETWEEN %s AND %s """, (start_date, end_date)) total_purchases = to_decimal(cursor.fetchone()['total']) # ───────────────────────────────────────── # 4️⃣ PURCHASE RETURNS # ───────────────────────────────────────── cursor.execute(""" SELECT COALESCE(SUM(pri.subtotal), 0) AS total FROM purchase_return_items pri JOIN purchase_returns pr ON pri.return_id = pr.return_id WHERE DATE(pr.created_on) BETWEEN %s AND %s """, (start_date, end_date)) purchase_returns = to_decimal(cursor.fetchone()['total']) # ───────────────────────────────────────── # 5️⃣ EXPENSES # ───────────────────────────────────────── cursor.execute(""" SELECT COALESCE(SUM(amount), 0) AS total FROM expenses WHERE DATE(date) BETWEEN %s AND %s """, (start_date, end_date)) total_expenses = to_decimal(cursor.fetchone()['total']) # ───────────────────────────────────────── # 6️⃣ COGS (Cost of Goods Sold) # ✅ FIFO via sale_product_items # quantity * cost per batch # ───────────────────────────────────────── cursor.execute(""" SELECT COALESCE(SUM(spi.quantity * spi.cost), 0) AS total FROM sale_product_items spi JOIN sale_products sp ON spi.sale_product_id = sp.sale_product_id JOIN invoice_sale isale ON sp.invoice_id = isale.invoice_id WHERE isale.status IN ('received', 'suspended') AND DATE(isale.sale_date) BETWEEN %s AND %s """, (start_date, end_date)) cogs = to_decimal(cursor.fetchone()['total']) # ───────────────────────────────────────── # 7️⃣ CASH COLLECTED # ✅ tenderAmount from received invoices # + partial payments from suspended invoices # NOT purchase_returns (වෙනම metric එකක්) # ───────────────────────────────────────── cursor.execute(""" SELECT COALESCE(SUM( CASE WHEN isale.status = 'received' THEN isale.tenderAmount WHEN isale.status = 'suspended' AND isale.payment_status = 'partial' THEN isale.tenderAmount WHEN isale.status = 'suspended' AND isale.payment_status = 'paid' THEN isale.tenderAmount ELSE 0 END ), 0) AS total FROM invoice_sale isale WHERE isale.status IN ('received', 'suspended') AND isale.payment_status IN ('paid', 'partial') AND DATE(isale.sale_date) BETWEEN %s AND %s """, (start_date, end_date)) cash_collected = to_decimal(cursor.fetchone()['total']) # ───────────────────────────────────────── # 8️⃣ CALCULATIONS # ───────────────────────────────────────── revenue = total_sales - sales_returns gross_profit = revenue - cogs net_profit = gross_profit - total_expenses # ───────────────────────────────────────── # 9️⃣ RESPONSE # ───────────────────────────────────────── return jsonify({ "success": True, "data": { "sales": { "value": float(total_sales), "formatted": format_currency(total_sales) }, "sales_returns": { "value": float(sales_returns), "formatted": format_currency(sales_returns) }, "purchases": { "value": float(total_purchases), "formatted": format_currency(total_purchases), "description": "Total stock actually received via GRN (completed/partial)" }, "purchase_returns": { "value": float(purchase_returns), "formatted": format_currency(purchase_returns) }, "expenses": { "value": float(total_expenses), "formatted": format_currency(total_expenses) }, "product_cost": { "value": float(cogs), "formatted": format_currency(cogs), "description": "Cost of Goods Sold — FIFO batch tracking via sale_product_items" }, "revenue": { "value": float(revenue), "formatted": format_currency(revenue), "calculation": f"Sales ({format_currency(total_sales)}) - Sales Returns ({format_currency(sales_returns)})" }, "gross_profit": { "value": float(gross_profit), "formatted": format_currency(gross_profit), "calculation": f"Revenue ({format_currency(revenue)}) - COGS ({format_currency(cogs)})" }, "net_profit": { "value": float(net_profit), "formatted": format_currency(net_profit), "calculation": f"Gross Profit ({format_currency(gross_profit)}) - Expenses ({format_currency(total_expenses)})" }, "payments_received": { "value": float(cash_collected), "formatted": format_currency(cash_collected), "description": "Actual cash/payments collected from customers" } }, "period": { "start_date": start_date, "end_date": end_date } }), 200 except Exception as e: logger.error(f"❌ Error in profit_loss_summary: {e}") traceback.print_exc() return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ============================================ # 9️⃣ GRN COST ANALYSIS (NEW - GRN Specific) # ============================================ @report_bp.route('/grn_cost_analysis', methods=['GET']) @jwt_required() @role_required('admin', 'manager') def grn_cost_analysis(): """ ✅ NEW: Analyze product costs grouped by GRN Shows which GRNs contributed to sales and their costs """ conn = None cursor = None try: start_date = request.args.get('start_date') end_date = request.args.get('end_date') conn = get_db_connection() cursor = conn.cursor(dictionary=True) query = """ SELECT grn.grn_id, grn.grn_code, grn.grn_date, grn.supplier_id, s.supplier_name, COUNT(DISTINCT spi.sale_product_item_id) as times_used_in_sales, SUM(spi.quantity) as total_quantity_sold, SUM(spi.quantity * spi.cost) as total_cost_contribution, MIN(spi.cost) as min_cost, MAX(spi.cost) as max_cost, AVG(spi.cost) as avg_cost FROM grn JOIN suppliers s ON grn.supplier_id = s.id LEFT JOIN sale_product_items spi ON grn.grn_id = spi.grn_id LEFT JOIN sale_products sp ON spi.sale_product_id = sp.sale_product_id LEFT JOIN invoice_sale isale ON sp.invoice_id = isale.invoice_id WHERE grn.status = 'completed' """ params = [] if start_date and end_date: query += " AND DATE(isale.sale_date) BETWEEN %s AND %s" params.extend([start_date, end_date]) query += " GROUP BY grn.grn_id ORDER BY total_cost_contribution DESC" cursor.execute(query, tuple(params)) grn_analysis = cursor.fetchall() grn_list = [] for grn in grn_analysis: grn_list.append({ "grn_id": grn['grn_id'], "grn_code": grn['grn_code'], "grn_date": format_datetime(grn['grn_date']), "supplier_name": grn['supplier_name'], "times_used_in_sales": grn['times_used_in_sales'] or 0, "total_quantity_sold": float(grn['total_quantity_sold'] or 0), "total_cost_contribution": float(grn['total_cost_contribution'] or 0), "total_cost_contribution_formatted": format_currency(grn['total_cost_contribution']), "min_cost": float(grn['min_cost'] or 0), "max_cost": float(grn['max_cost'] or 0), "avg_cost": float(grn['avg_cost'] or 0) }) total_grn_cost = sum(float(g['total_cost_contribution'] or 0) for g in grn_analysis) return jsonify({ "success": True, "grn_analysis": grn_list, "summary": { "total_grns": len(grn_list), "total_cost_contribution": float(total_grn_cost), "total_cost_contribution_formatted": format_currency(total_grn_cost) }, "start_date": start_date, "end_date": end_date }), 200 except Exception as e: logger.error(f"❌ Error in grn_cost_analysis: {e}") traceback.print_exc() return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ============================================ # 🔟 TODAY'S REPORTS # ============================================ @report_bp.route('/total_sales_today', methods=['GET']) @jwt_required() @role_required('admin', 'cashier', 'manager') def total_sales_today(): """Today's total sales""" conn = None cursor = None try: today = date.today().isoformat() conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT COALESCE(SUM(sp.total), 0) as total_sales_today FROM sale_products sp JOIN invoice_sale isale ON sp.invoice_id = isale.invoice_id WHERE DATE(isale.sale_date) = %s AND isale.status IN ('received', 'suspended') """, (today,)) result = cursor.fetchone() total = to_decimal(result['total_sales_today']) return jsonify({ "success": True, "total_sales_today": float(total), "total_sales_today_formatted": format_currency(total), "date": today }), 200 except Exception as e: logger.error(f"❌ Error: {e}") return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() @report_bp.route('/today_total_received', methods=['GET']) @jwt_required() @role_required('admin', 'cashier', 'manager') def today_total_received(): """Today's received sales""" conn = None cursor = None try: today = date.today().isoformat() conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT COALESCE(SUM(sp.total), 0) as total_received FROM sale_products sp JOIN invoice_sale isale ON sp.invoice_id = isale.invoice_id WHERE DATE(isale.sale_date) = %s AND isale.status = 'received' """, (today,)) result = cursor.fetchone() total = to_decimal(result['total_received']) return jsonify({ "success": True, "today_total_received": float(total), "today_total_received_formatted": format_currency(total), "date": today }), 200 except Exception as e: logger.error(f"❌ Error: {e}") return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() @report_bp.route('/today_total_submit_order', methods=['GET']) @jwt_required() @role_required('admin', 'manager') def today_total_submit_order(): """Today's total purchases""" conn = None cursor = None try: today = date.today().isoformat() conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT COALESCE(SUM(grand_total), 0) AS total_purchases FROM purchase_orders WHERE DATE(created_on) = %s """, (today,)) result = cursor.fetchone() total = to_decimal(result['total_purchases']) return jsonify({ 'success': True, 'total_purchases': float(total), 'total_purchases_formatted': format_currency(total), 'date': today }), 200 except Exception as e: logger.error(f"❌ Error: {e}") return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() @report_bp.route('/today_total_expenses', methods=['GET']) @jwt_required() @role_required('admin', 'cashier', 'manager') def today_total_expenses(): """Today's total expenses""" conn = None cursor = None try: today = date.today().isoformat() conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT COALESCE(SUM(amount), 0) AS total_expenses FROM expenses WHERE DATE(date) = %s """, (today,)) result = cursor.fetchone() total = to_decimal(result['total_expenses']) return jsonify({ 'success': True, 'total_expenses': float(total), 'total_expenses_formatted': format_currency(total), 'date': today }), 200 except Exception as e: logger.error(f"❌ Error: {e}") return jsonify({"success": False, "error": str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # report.py වලට add කරන්න @report_bp.route('/get_adjustments_by_product/', methods=['GET']) @jwt_required() @role_required('admin') def get_adjustments_by_product(product_id): """ Get all stock adjustments for a specific product. Query Params: - variation_id (optional int) - start (optional date string YYYY-MM-DD) - end (optional date string YYYY-MM-DD) """ conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: variation_id = request.args.get('variation_id', type=int) start_date = request.args.get('start') end_date = request.args.get('end') query = """ SELECT sa.adjustment_id, sa.adjustment_code, sa.adjustment_type, sa.reason, sa.note, sa.created_at, -- Item details sai.quantity, sai.adjustment_type AS item_adjustment_type, sai.note AS item_note, -- Product p.product_name, p.sku AS product_sku, -- Variation pv.variation_name, pv.variation_type, pv.variation_sku, -- Batch pb.batch_id, pb.batch_number, pb.remaining_quantity, -- Warehouse & Store w.warehouse_name, s.store_name, -- User u.name AS created_by_name FROM stock_adjustment_items sai JOIN stock_adjustments sa ON sai.adjustment_id = sa.adjustment_id JOIN products p ON sai.product_id = p.id LEFT JOIN product_variations pv ON sai.variation_id = pv.id LEFT JOIN product_batches pb ON sai.batch_id = pb.batch_id LEFT JOIN warehouses w ON sa.warehouse_id = w.id LEFT JOIN stores s ON sa.store_id = s.id LEFT JOIN users u ON sa.created_by = u.id WHERE sai.product_id = %s """ params = [product_id] if variation_id: query += " AND sai.variation_id = %s" params.append(variation_id) if start_date and end_date: query += " AND DATE(sa.created_at) BETWEEN %s AND %s" params.extend([start_date, end_date]) query += " ORDER BY sa.created_at DESC" cursor.execute(query, params) rows = cursor.fetchall() results = [] for row in rows: results.append({ "adjustment_id": row["adjustment_id"], "adjustment_code": row["adjustment_code"], "adjustment_type": row["adjustment_type"], "item_adjustment_type": row["item_adjustment_type"], "reason": row["reason"], "note": row["note"], "item_note": row["item_note"], "created_at": row["created_at"].strftime("%Y-%m-%d %H:%M:%S") if row["created_at"] else None, "quantity": float(row["quantity"] or 0), "product_name": row["product_name"], "product_sku": row["product_sku"], "variation_name": row["variation_name"], "variation_type": row["variation_type"], "variation_sku": row["variation_sku"], "batch_id": row["batch_id"], "batch_number": row["batch_number"], "remaining_quantity": float(row["remaining_quantity"] or 0), "warehouse_name": row["warehouse_name"] or "N/A", "store_name": row["store_name"] or "N/A", "created_by_name": row["created_by_name"], }) return jsonify(results), 200 except Exception as e: print(f"❌ Error in get_adjustments_by_product: {e}") import traceback traceback.print_exc() return jsonify({"error": str(e)}), 500 finally: cursor.close() conn.close()