from flask import Blueprint, request, jsonify from flask_jwt_extended import jwt_required, get_jwt_identity from db.db import get_db_connection from config.auth import role_required from datetime import datetime, date import json import mysql.connector import traceback quotation_bp = Blueprint('quotation', __name__) def safe_float(value, default=0.0): """Safely convert to float""" try: return float(value) if value not in (None, '', 'null') else default except (ValueError, TypeError): return default def safe_int(value, default=0): """Safely convert to int""" try: return int(value) if value not in (None, '', 'null') else default except (ValueError, TypeError): return default def get_available_batches_fifo(cursor, product_id, variation_id, warehouse_id, store_id): """ Get available batches for a product in FIFO order (oldest first) Returns list of batches with available stock ✅ FIXED: Removed grn_products table reference (doesn't exist) ✅ FIXED: Removed manufacture_date (doesn't exist in product_batches) ✅ Uses product_batches.grn_id directly (already exists in schema) ✅ Gets manufacturing_date from grn_items if needed """ query = """ SELECT pb.batch_id, pb.batch_number, pb.cost, pb.price, pb.expiration_date, COALESCE(ws.quantity, 0) as available_quantity, pb.grn_id, g.grn_code, g.grn_date, pb.created_on as batch_created_date FROM product_batches pb LEFT JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id AND ws.product_id = pb.product_id AND ws.warehouse_id = %s AND ws.store_id = %s """ params = [warehouse_id, store_id] # ✅ FIXED: Direct join to GRN using product_batches.grn_id (no grn_products needed) query += """ LEFT JOIN grn g ON pb.grn_id = g.grn_id WHERE pb.product_id = %s """ params.append(product_id) if variation_id: query += " AND pb.variation_id = %s" params.append(variation_id) else: query += " AND pb.variation_id IS NULL" # FIFO: Order by batch creation date (oldest first) # Using created_on instead of manufacture_date query += """ AND COALESCE(ws.quantity, 0) > 0 ORDER BY pb.created_on ASC, pb.batch_id ASC """ cursor.execute(query, params) return cursor.fetchall() def allocate_quantity_to_batches(batches, required_quantity): """ Allocate required quantity across available batches (FIFO) Returns list of allocations: [{batch_id, grn_id, quantity, cost, price, expiry}, ...] ✅ දැන් එක එක batch එකේ price එකත් save කරනවා """ allocations = [] remaining = required_quantity for batch in batches: if remaining <= 0: break available = safe_float(batch['available_quantity']) if available <= 0: continue # Check expiry if batch['expiration_date']: expiry_date = batch['expiration_date'] if isinstance(expiry_date, str): expiry_date = datetime.strptime(expiry_date, '%Y-%m-%d').date() if expiry_date < date.today(): continue # Skip expired batches # Allocate from this batch allocate_qty = min(remaining, available) allocations.append({ 'batch_id': batch['batch_id'], 'batch_number': batch['batch_number'], 'grn_id': batch.get('grn_id'), 'grn_code': batch.get('grn_code'), 'quantity': allocate_qty, 'cost': safe_float(batch['cost']), 'price': safe_float(batch['price']), # ✅ Batch price save කරනවා 'expiration_date': batch['expiration_date'] }) remaining -= allocate_qty return allocations, remaining # ========================================== # CREATE QUOTATION - WITH MULTI-BATCH FIFO + GRN TRACKING # ========================================== @quotation_bp.route('/create_quotation', methods=['POST']) @jwt_required() @role_required('admin', 'cashier') def create_quotation(): """ Create a new quotation with multi-batch FIFO allocation and GRN tracking ✅ Multi-batch FIFO allocation ✅ GRN tracking for each batch ✅ Proper stock validation ✅ Complete batch allocation details """ conn = None cursor = None try: data = request.get_json(force=True) if not data: return jsonify({'error': 'No data provided'}), 400 except Exception as e: return jsonify({'error': 'Invalid JSON data', 'message': str(e)}), 400 print("=" * 80) print("📝 CREATING NEW QUOTATION (MULTI-BATCH FIFO + GRN TRACKING)") print("=" * 80) print(json.dumps(data, indent=2, default=str)) print("=" * 80) # Extract quotation-level data customer_id = safe_int(data.get('customer_id')) warehouse_id = safe_int(data.get('warehouse_id')) if data.get('warehouse_id') else None store_id = safe_int(data.get('store_id', 1)) quotation_date = data.get('quotation_date') note = data.get('note', '') order_tax_percentage = safe_float(data.get('order_tax', 0)) order_discount = safe_float(data.get('discount', 0)) status = data.get('status', 'pending') products = data.get('products', []) user_id = get_jwt_identity() # Validation if not customer_id: return jsonify({'error': 'Customer is required'}), 400 if not store_id: return jsonify({'error': 'Store is required'}), 400 if not quotation_date: return jsonify({'error': 'Quotation date is required'}), 400 # Validate date try: quote_date_obj = datetime.strptime(quotation_date, '%Y-%m-%d').date() if quote_date_obj < date.today(): return jsonify({'error': 'Quotation date cannot be in the past'}), 400 except ValueError: return jsonify({'error': 'Invalid quotation date format. Use YYYY-MM-DD'}), 400 if not products or len(products) == 0: return jsonify({'error': 'At least one product is required'}), 400 try: conn = get_db_connection() if not conn: return jsonify({'error': 'Database connection failed'}), 500 cursor = conn.cursor(dictionary=True, buffered=True) conn.start_transaction() print("\n🔄 Transaction started") # Generate quotation code quotation_code = f"QT-{datetime.now().strftime('%Y%m%d%H%M%S')}" print(f"📝 Generated quotation code: {quotation_code}") # Validate customer cursor.execute("SELECT id, name FROM customers WHERE id = %s", (customer_id,)) customer = cursor.fetchone() if not customer: raise ValueError(f"Customer with ID {customer_id} not found") print(f"✅ Customer: {customer['name']}") # Validate store cursor.execute("SELECT id, store_name FROM stores WHERE id = %s AND is_active = 1", (store_id,)) store = cursor.fetchone() if not store: raise ValueError(f"Store with ID {store_id} not found or inactive") print(f"✅ Store: {store['store_name']}") # ========================================== # PROCESS AND CALCULATE EACH PRODUCT # ========================================== print(f"\n📦 Processing {len(products)} products...") processed_items = [] quotation_subtotal = 0.0 total_batch_allocations = 0 for idx, product_data in enumerate(products, 1): try: product_id = safe_int(product_data.get('product_id')) variation_id = safe_int(product_data.get('variation_id')) if product_data.get('variation_id') else None # Get warehouse_id (item-level or quotation-level) item_warehouse_id = safe_int(product_data.get('warehouse_id')) if product_data.get('warehouse_id') else warehouse_id if not item_warehouse_id: raise ValueError(f"Warehouse ID is required for product at position {idx}") # Validate warehouse exists cursor.execute("SELECT id, warehouse_name FROM warehouses WHERE id = %s AND is_active = 1", (item_warehouse_id,)) warehouse = cursor.fetchone() if not warehouse: raise ValueError(f"Warehouse ID {item_warehouse_id} not found or inactive") quantity = safe_float(product_data.get('quantity', 0)) # Item-level discount and tax discount_type = product_data.get('discount_type', 'fixed') product_discount_value = safe_float(product_data.get('product_discount', 0)) tax_type = product_data.get('tax_type', 'exclusive') product_tax_percentage = safe_float(product_data.get('product_tax', 0)) # Validation if not product_id: print(f" ⚠️ Skipping product {idx}: No product ID") continue if quantity <= 0: print(f" ⚠️ Skipping product {idx}: Invalid quantity ({quantity})") continue print(f"\n 📦 Product {idx}:") print(f" Product ID: {product_id}") print(f" Variation ID: {variation_id}") print(f" Warehouse ID: {item_warehouse_id}") print(f" Quantity: {quantity}") # ========================================== # GET PRODUCT/VARIATION DETAILS # ========================================== if variation_id: cursor.execute(""" SELECT pv.id as variation_id, pv.variation_sku, pv.variation_name, pv.variation_type, p.id as product_id, p.product_name, p.sku, p.tax_type as default_tax_type, p.product_tax as default_tax_percentage FROM product_variations pv JOIN products p ON pv.product_id = p.id WHERE pv.id = %s AND p.id = %s """, (variation_id, product_id)) product_info = cursor.fetchone() if not product_info: raise ValueError(f"Product variation {variation_id} not found") product_name = f"{product_info['product_name']} - {product_info['variation_name']}" product_sku = product_info['variation_sku'] else: cursor.execute(""" SELECT id as product_id, product_name, sku, tax_type as default_tax_type, product_tax as default_tax_percentage FROM products WHERE id = %s """, (product_id,)) product_info = cursor.fetchone() if not product_info: raise ValueError(f"Product {product_id} not found") product_name = product_info['product_name'] product_sku = product_info['sku'] print(f" Product: {product_name} ({product_sku})") # ========================================== # FIFO BATCH ALLOCATION WITH GRN TRACKING # ========================================== print(f" 🔍 Fetching available batches (FIFO)...") available_batches = get_available_batches_fifo( cursor, product_id, variation_id, item_warehouse_id, store_id ) if not available_batches or len(available_batches) == 0: raise ValueError( f"No stock available for {product_name} in warehouse {warehouse['warehouse_name']}" ) print(f" 📦 Found {len(available_batches)} available batches") # Allocate quantity across batches batch_allocations, remaining_qty = allocate_quantity_to_batches( available_batches, quantity ) if remaining_qty > 0: total_available = sum(safe_float(b['available_quantity']) for b in available_batches) raise ValueError( f"Insufficient stock for {product_name}. " f"Requested: {quantity}, Available: {total_available}, Short by: {remaining_qty}" ) print(f" ✅ Allocated across {len(batch_allocations)} batches:") for alloc in batch_allocations: print(f" - Batch {alloc['batch_number']}: {alloc['quantity']} units " f"@ LKR {alloc['price']:.2f} (GRN: {alloc.get('grn_code', 'N/A')})") total_batch_allocations += len(batch_allocations) # ========================================== # CALCULATE WEIGHTED AVERAGE PRICE & COST # ========================================== frontend_price = safe_float(product_data.get('price', 0)) if frontend_price > 0: # Use frontend price (customer specified) selling_price = frontend_price print(f" 💰 Using Frontend Price: LKR {selling_price:.2f}") else: # Calculate weighted average price from batches (FIFO) total_price_value = sum(alloc['price'] * alloc['quantity'] for alloc in batch_allocations) selling_price = total_price_value / quantity if quantity > 0 else 0 print(f" 💰 Weighted Average Price: LKR {selling_price:.2f}") print(f" (Calculated from {len(batch_allocations)} batches)") # Calculate weighted average cost total_cost = sum(alloc['cost'] * alloc['quantity'] for alloc in batch_allocations) avg_cost = total_cost / quantity if quantity > 0 else 0 print(f" 📊 Weighted Average Cost: LKR {avg_cost:.2f}") # ========================================== # CALCULATE DISCOUNT # ========================================== discount_per_unit = 0.0 if discount_type == 'percentage': discount_per_unit = (selling_price * product_discount_value) / 100 print(f" 💸 Discount: {product_discount_value}% = LKR {discount_per_unit:.2f} per unit") elif discount_type == 'fixed': discount_per_unit = product_discount_value print(f" 💸 Discount: LKR {discount_per_unit:.2f} per unit (fixed)") price_after_discount = max(selling_price - discount_per_unit, 0) total_discount = discount_per_unit * quantity print(f" 💵 Price after discount: LKR {price_after_discount:.2f}") # ========================================== # CALCULATE TAX # ========================================== if not tax_type or tax_type == 'null': tax_type = product_info['default_tax_type'] or 'exclusive' if product_tax_percentage == 0: product_tax_percentage = safe_float(product_info['default_tax_percentage']) tax_per_unit = 0.0 net_unit_price = price_after_discount if product_tax_percentage > 0: if tax_type == 'exclusive': tax_per_unit = (price_after_discount * product_tax_percentage) / 100 net_unit_price = price_after_discount print(f" 📈 Tax (Exclusive): {product_tax_percentage}% = LKR {tax_per_unit:.2f} per unit") elif tax_type == 'inclusive': tax_per_unit = (price_after_discount * product_tax_percentage) / (100 + product_tax_percentage) net_unit_price = price_after_discount - tax_per_unit print(f" 📈 Tax (Inclusive): {product_tax_percentage}% = LKR {tax_per_unit:.2f} per unit") total_tax = tax_per_unit * quantity # ========================================== # CALCULATE ITEM SUBTOTAL # ========================================== if tax_type == 'exclusive': item_subtotal = (net_unit_price * quantity) + total_tax else: item_subtotal = price_after_discount * quantity # Round to 2 decimal places item_subtotal = round(item_subtotal, 2) total_discount = round(total_discount, 2) total_tax = round(total_tax, 2) net_unit_price = round(net_unit_price, 2) print(f" 💰 Item Subtotal: LKR {item_subtotal:.2f}") quotation_subtotal += item_subtotal # Store processed item WITH batch allocations processed_items.append({ 'product_id': product_id, 'variation_id': variation_id, 'warehouse_id': item_warehouse_id, 'quantity': quantity, 'price': selling_price, 'net_unit_price': net_unit_price, 'discount_type': discount_type, 'product_discount': product_discount_value, 'tax_type': tax_type, 'product_tax': product_tax_percentage, 'discount': total_discount, 'tax': total_tax, 'subtotal': item_subtotal, 'batch_allocations': batch_allocations # ✅ Store allocations }) print(f" ✅ Product processed successfully") except Exception as item_error: print(f" ❌ Error processing product {idx}: {item_error}") raise item_error if len(processed_items) == 0: raise ValueError("No valid products were processed") # ========================================== # CALCULATE QUOTATION TOTALS # ========================================== print(f"\n💰 Calculating Quotation Totals...") print(f" Items Subtotal: LKR {quotation_subtotal:.2f}") quotation_subtotal = round(quotation_subtotal, 2) order_tax_amount = round((quotation_subtotal * order_tax_percentage) / 100, 2) order_discount_amount = round(order_discount, 2) print(f" Order Tax ({order_tax_percentage}%): LKR {order_tax_amount:.2f}") print(f" Order Discount: LKR {order_discount_amount:.2f}") grand_total = round(quotation_subtotal + order_tax_amount - order_discount_amount, 2) if grand_total < 0: raise ValueError("Grand total cannot be negative") print(f" 💵 Grand Total: LKR {grand_total:.2f}") # ========================================== # INSERT QUOTATION # ========================================== cursor.execute(""" INSERT INTO quotations ( quotation_code, customer_id, warehouse_id, store_id, quotation_date, note, subtotal, tax, discount, grand_total, status, created_by, created_at ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW()) """, ( quotation_code, customer_id, warehouse_id, store_id, quotation_date, note, quotation_subtotal, order_tax_amount, order_discount_amount, grand_total, status, user_id )) quotation_id = cursor.lastrowid print(f"\n✅ Quotation created with ID: {quotation_id}") # ========================================== # INSERT QUOTATION ITEMS + BATCH ALLOCATIONS # ========================================== print(f"\n📦 Inserting {len(processed_items)} quotation items with batch allocations...") for idx, item in enumerate(processed_items, 1): # Insert quotation item cursor.execute(""" INSERT INTO quotation_items ( quotation_id, product_id, variation_id, warehouse_id, quantity, price, net_unit_price, discount_type, product_discount, tax_type, product_tax, discount, tax, subtotal ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, ( quotation_id, item['product_id'], item['variation_id'], item['warehouse_id'], item['quantity'], item['price'], item['net_unit_price'], item['discount_type'], item['product_discount'], item['tax_type'], item['product_tax'], item['discount'], item['tax'], item['subtotal'] )) quotation_item_id = cursor.lastrowid print(f" ✅ Item {idx} inserted (ID: {quotation_item_id})") # ✅ INSERT BATCH ALLOCATIONS into quotation_product_batches for alloc_idx, allocation in enumerate(item['batch_allocations'], 1): cursor.execute(""" INSERT INTO quotation_product_batches ( quotation_item_id, batch_id, grn_id, quantity, cost, price, expiration_date, created_at ) VALUES (%s, %s, %s, %s, %s, %s, %s, NOW()) """, ( quotation_item_id, allocation['batch_id'], allocation.get('grn_id'), allocation['quantity'], allocation['cost'], allocation['price'], # ✅ Batch price save කරනවා allocation.get('expiration_date') )) print(f" ✅ Batch allocation {alloc_idx}: Batch {allocation['batch_number']}, " f"Qty: {allocation['quantity']}, Price: LKR {allocation['price']:.2f}, " f"GRN: {allocation.get('grn_code', 'N/A')}") # ========================================== # COMMIT TRANSACTION # ========================================== conn.commit() print(f"\n✅✅✅ Transaction committed successfully!") print("\n" + "=" * 80) print(f"✅ SUCCESS: Quotation {quotation_code} created") print(f" Total Items: {len(processed_items)}") print(f" Total Batch Allocations: {total_batch_allocations}") print(f" Grand Total: LKR {grand_total:.2f}") print("=" * 80) return jsonify({ 'success': True, 'message': 'Quotation created successfully with multi-batch FIFO allocation', 'quotation_id': quotation_id, 'quotation_code': quotation_code, 'subtotal': quotation_subtotal, 'tax': order_tax_amount, 'discount': order_discount_amount, 'grand_total': grand_total, 'items_count': len(processed_items), 'batch_allocations_count': total_batch_allocations }), 201 except ValueError as err: if conn: conn.rollback() print(f"\n❌ Validation Error: {err}") return jsonify({'error': str(err)}), 400 except mysql.connector.IntegrityError as err: if conn: conn.rollback() print(f"\n❌ Database Integrity Error: {err}") error_msg = str(err).lower() if 'duplicate entry' in error_msg: return jsonify({'error': 'Quotation code already exists. Please try again.'}), 409 elif 'fk_quotations_customer' in error_msg: return jsonify({'error': 'Invalid customer. Customer does not exist or has been deleted.'}), 400 elif 'fk_quotations_warehouse' in error_msg: return jsonify({'error': 'Invalid warehouse. Warehouse does not exist or is inactive.'}), 400 elif 'fk_quotations_store' in error_msg: return jsonify({'error': 'Invalid store. Store does not exist or is inactive.'}), 400 else: return jsonify({'error': f'Database constraint error: {str(err)}'}), 400 except mysql.connector.Error as err: if conn: conn.rollback() print(f"\n❌ Database Error: {err}") traceback.print_exc() return jsonify({'error': f'Database error: {str(err)}'}), 500 except Exception as e: if conn: conn.rollback() print(f"\n❌ Unexpected Error: {e}") traceback.print_exc() return jsonify({'error': f'Server error: {str(e)}'}), 500 finally: if cursor: cursor.close() if conn: conn.close() print("\n🔒 Database connection closed\n") # ========================================== # GET ALL QUOTATIONS # ========================================== @quotation_bp.route('/get_quotations', methods=['GET']) @jwt_required() @role_required('admin', 'cashier') def get_quotations(): """Get all quotations with pagination and filters""" try: page = int(request.args.get('page', 1)) per_page = int(request.args.get('per_page', 20)) status = request.args.get('status') warehouse_id = request.args.get('warehouse_id') store_id = request.args.get('store_id') offset = (page - 1) * per_page conn = get_db_connection() if not conn: return jsonify({'error': 'Database connection failed'}), 500 cursor = conn.cursor(dictionary=True) # Build WHERE clause where_conditions = [] params = [] if status: where_conditions.append("q.status = %s") params.append(status) if warehouse_id: where_conditions.append("q.warehouse_id = %s") params.append(warehouse_id) if store_id: where_conditions.append("q.store_id = %s") params.append(store_id) where_clause = " AND ".join(where_conditions) if where_conditions else "1=1" # Get total count count_sql = f""" SELECT COUNT(*) as total FROM quotations q WHERE {where_clause} """ cursor.execute(count_sql, params) total = cursor.fetchone()['total'] # Get quotations sql = f""" SELECT q.quotation_id, q.quotation_code, q.customer_id, c.name as customer_name, q.warehouse_id, w.warehouse_name, q.store_id, s.store_name, s.address as store_address, s.contact as store_phone, s.email as store_email, q.quotation_date, q.grand_total, q.status, q.created_at, u.name as created_by_name, (SELECT COUNT(*) FROM quotation_items qi WHERE qi.quotation_id = q.quotation_id) as total_items, (SELECT COUNT(*) FROM quotation_product_batches qpb JOIN quotation_items qi2 ON qpb.quotation_item_id = qi2.item_id WHERE qi2.quotation_id = q.quotation_id) as total_batch_allocations FROM quotations q LEFT JOIN customers c ON q.customer_id = c.id LEFT JOIN warehouses w ON q.warehouse_id = w.id LEFT JOIN stores s ON q.store_id = s.id LEFT JOIN users u ON q.created_by = u.id WHERE {where_clause} ORDER BY q.created_at DESC LIMIT %s OFFSET %s """ cursor.execute(sql, params + [per_page, offset]) quotations = cursor.fetchall() # Format dates for quotation in quotations: if quotation['quotation_date']: quotation['quotation_date'] = quotation['quotation_date'].isoformat() if quotation['created_at']: quotation['created_at'] = quotation['created_at'].strftime('%Y-%m-%d %H:%M:%S') return jsonify({ 'success': True, 'quotations': quotations, 'pagination': { 'page': page, 'per_page': per_page, 'total': total, 'pages': (total + per_page - 1) // per_page } }), 200 except Exception as e: print(f"❌ Error fetching quotations: {e}") traceback.print_exc() return jsonify({'error': str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ========================================== # GET SINGLE QUOTATION - WITH BATCH ALLOCATIONS (✅ FIXED) # ========================================== @quotation_bp.route('/get_quotation/', methods=['GET']) @jwt_required() @role_required('admin', 'manager', 'cashier') def get_quotation(quotation_id): """Get single quotation with all items and batch allocation details""" conn = get_db_connection() if not conn: return jsonify({'error': 'Database connection failed'}), 500 cursor = conn.cursor(dictionary=True) try: # Get quotation details cursor.execute(""" SELECT q.*, c.name as customer_name, c.contact as customer_contact, c.email as customer_email, c.address as customer_address, w.warehouse_name, s.store_name, s.address as store_address, s.contact as store_phone, s.email as store_email, u.name as created_by_name FROM quotations q LEFT JOIN customers c ON q.customer_id = c.id LEFT JOIN warehouses w ON q.warehouse_id = w.id LEFT JOIN stores s ON q.store_id = s.id LEFT JOIN users u ON q.created_by = u.id WHERE q.quotation_id = %s """, (quotation_id,)) quotation = cursor.fetchone() if not quotation: return jsonify({'error': 'Quotation not found'}), 404 # Format dates if quotation.get('quotation_date'): quotation['quotation_date'] = quotation['quotation_date'].isoformat() if quotation.get('created_at'): quotation['created_at'] = quotation['created_at'].strftime('%Y-%m-%d %H:%M:%S') if quotation.get('updated_at'): quotation['updated_at'] = quotation['updated_at'].strftime('%Y-%m-%d %H:%M:%S') # Get quotation items cursor.execute(""" SELECT qi.*, p.product_name, p.sku, p.product_type, pv.variation_name, pv.variation_type, pv.variation_sku, w.warehouse_name, u.unit_name as unit, u.unit_short, COALESCE(qi.net_unit_price, qi.price) as net_unit_price FROM quotation_items qi LEFT JOIN products p ON qi.product_id = p.id LEFT JOIN product_variations pv ON qi.variation_id = pv.id LEFT JOIN warehouses w ON qi.warehouse_id = w.id LEFT JOIN units u ON p.purchase_unit_id = u.id WHERE qi.quotation_id = %s ORDER BY qi.item_id """, (quotation_id,)) items = cursor.fetchall() # ✅ Get batch allocations for each item (FIXED: Removed pb.manufacture_date) for item in items: cursor.execute(""" SELECT qpb.quotation_product_batch_id, qpb.batch_id, qpb.grn_id, qpb.quantity, qpb.cost, qpb.price, qpb.expiration_date, pb.batch_number, g.grn_code, g.grn_date as grn_date, gi.manufacturing_date FROM quotation_product_batches qpb LEFT JOIN product_batches pb ON qpb.batch_id = pb.batch_id LEFT JOIN grn g ON qpb.grn_id = g.grn_id LEFT JOIN grn_items gi ON qpb.grn_id = gi.grn_id AND qpb.batch_id = gi.batch_id AND gi.product_id = %s WHERE qpb.quotation_item_id = %s ORDER BY qpb.quotation_product_batch_id """, (item['product_id'], item['item_id'])) batch_allocations = cursor.fetchall() # Format batch allocation data for batch in batch_allocations: if batch.get('expiration_date'): batch['expiration_date'] = batch['expiration_date'].isoformat() if batch.get('manufacturing_date'): batch['manufacturing_date'] = batch['manufacturing_date'].isoformat() if batch.get('grn_date'): batch['grn_date'] = batch['grn_date'].isoformat() # Convert Decimal to float for field in ['quantity', 'cost', 'price']: if batch.get(field) is not None: batch[field] = float(batch[field]) item['batch_allocations'] = batch_allocations item['batch_count'] = len(batch_allocations) # Get total available stock try: stock_query = """ SELECT COALESCE(SUM(quantity), 0) as total_stock FROM warehouse_stock WHERE product_id = %s AND store_id = %s """ params = [item['product_id'], quotation['store_id']] if item.get('warehouse_id'): stock_query += " AND warehouse_id = %s" params.append(item['warehouse_id']) if item.get('variation_id'): stock_query += " AND variation_id = %s" params.append(item['variation_id']) else: stock_query += " AND variation_id IS NULL" cursor.execute(stock_query, params) stock_result = cursor.fetchone() item['stock'] = stock_result['total_stock'] if stock_result else 0 except Exception as stock_err: print(f"⚠️ Warning: Could not fetch stock for item {item['item_id']}: {stock_err}") item['stock'] = 0 # Convert Decimal to float for JSON if not item.get('net_unit_price') or item['net_unit_price'] == 0: item['net_unit_price'] = item.get('price', 0) for field in ['net_unit_price', 'price', 'quantity', 'discount', 'tax', 'subtotal', 'product_discount', 'product_tax']: if item.get(field) is not None: item[field] = float(item[field]) quotation['items'] = items quotation['total_items'] = len(items) quotation['total_batch_allocations'] = sum(item.get('batch_count', 0) for item in items) print(f"✅ Fetched quotation {quotation.get('quotation_code')} with {len(items)} items " f"and {quotation['total_batch_allocations']} batch allocations") return jsonify({ 'success': True, 'quotation': quotation }), 200 except Exception as e: print(f"❌ Error fetching quotation: {e}") traceback.print_exc() return jsonify({'error': str(e)}), 500 finally: cursor.close() conn.close() # ========================================== # UPDATE QUOTATION - WITH BATCH ALLOCATIONS # ========================================== @quotation_bp.route('/update_quotation/', methods=['PUT']) @jwt_required() @role_required('admin', 'cashier') def update_quotation(quotation_id): """Update existing quotation with multi-batch support""" conn = None cursor = None try: data = request.get_json(force=True) if not data: return jsonify({'error': 'No data provided'}), 400 except Exception as e: return jsonify({'error': 'Invalid JSON data', 'message': str(e)}), 400 print(f"\n📝 Updating quotation ID: {quotation_id}") customer_id = safe_int(data.get('customer_id')) warehouse_id = safe_int(data.get('warehouse_id')) if data.get('warehouse_id') else None store_id = safe_int(data.get('store_id', 1)) quotation_date = data.get('quotation_date') note = data.get('note', '') order_tax = safe_float(data.get('order_tax', 0)) discount = safe_float(data.get('discount', 0)) status = data.get('status', 'pending') products = data.get('products', []) try: conn = get_db_connection() if not conn: return jsonify({'error': 'Database connection failed'}), 500 cursor = conn.cursor(dictionary=True, buffered=True) conn.start_transaction() cursor.execute("SELECT quotation_code FROM quotations WHERE quotation_id = %s", (quotation_id,)) existing = cursor.fetchone() if not existing: return jsonify({'error': 'Quotation not found'}), 404 quotation_code = existing['quotation_code'] # Process products with FIFO allocation processed_items = [] quotation_subtotal = 0.0 for product_data in products: product_id = safe_int(product_data.get('product_id')) variation_id = safe_int(product_data.get('variation_id')) if product_data.get('variation_id') else None item_warehouse_id = safe_int(product_data.get('warehouse_id')) if product_data.get('warehouse_id') else warehouse_id quantity = safe_float(product_data.get('quantity', 0)) if not product_id or quantity <= 0: continue # Get available batches and allocate available_batches = get_available_batches_fifo( cursor, product_id, variation_id, item_warehouse_id, store_id ) batch_allocations, remaining_qty = allocate_quantity_to_batches( available_batches, quantity ) if remaining_qty > 0: raise ValueError(f"Insufficient stock for product ID {product_id}") # Calculate prices and totals price = safe_float(product_data.get('price', 0)) if price == 0 and batch_allocations: price = safe_float(available_batches[0]['price']) discount_type = product_data.get('discount_type', 'fixed') product_discount = safe_float(product_data.get('product_discount', 0)) tax_type = product_data.get('tax_type', 'exclusive') product_tax = safe_float(product_data.get('product_tax', 0)) discount_per_unit = 0.0 if discount_type == 'percentage': discount_per_unit = (price * product_discount) / 100 else: discount_per_unit = product_discount price_after_discount = max(price - discount_per_unit, 0) total_discount = discount_per_unit * quantity tax_per_unit = 0.0 net_unit_price = price_after_discount if product_tax > 0: if tax_type == 'exclusive': tax_per_unit = (price_after_discount * product_tax) / 100 else: tax_per_unit = (price_after_discount * product_tax) / (100 + product_tax) net_unit_price = price_after_discount - tax_per_unit total_tax = tax_per_unit * quantity if tax_type == 'exclusive': item_subtotal = (net_unit_price * quantity) + total_tax else: item_subtotal = price_after_discount * quantity item_subtotal = round(item_subtotal, 2) quotation_subtotal += item_subtotal processed_items.append({ 'product_id': product_id, 'variation_id': variation_id, 'warehouse_id': item_warehouse_id, 'quantity': quantity, 'price': price, 'net_unit_price': round(net_unit_price, 2), 'discount_type': discount_type, 'product_discount': product_discount, 'tax_type': tax_type, 'product_tax': product_tax, 'discount': round(total_discount, 2), 'tax': round(total_tax, 2), 'subtotal': item_subtotal, 'batch_allocations': batch_allocations }) # Calculate totals quotation_subtotal = round(quotation_subtotal, 2) tax_amount = round((quotation_subtotal * order_tax) / 100, 2) discount_amount = round(discount, 2) grand_total = round(quotation_subtotal + tax_amount - discount_amount, 2) # Update quotation cursor.execute(""" UPDATE quotations SET customer_id = %s, warehouse_id = %s, store_id = %s, quotation_date = %s, note = %s, subtotal = %s, tax = %s, discount = %s, grand_total = %s, status = %s, updated_at = NOW() WHERE quotation_id = %s """, ( customer_id, warehouse_id, store_id, quotation_date, note, quotation_subtotal, tax_amount, discount_amount, grand_total, status, quotation_id )) # Delete old items and batch allocations (CASCADE will handle batch allocations) cursor.execute("DELETE FROM quotation_items WHERE quotation_id = %s", (quotation_id,)) # Insert new items with batch allocations for item in processed_items: cursor.execute(""" INSERT INTO quotation_items ( quotation_id, product_id, variation_id, warehouse_id, quantity, price, net_unit_price, discount_type, product_discount, tax_type, product_tax, discount, tax, subtotal ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, ( quotation_id, item['product_id'], item['variation_id'], item['warehouse_id'], item['quantity'], item['price'], item['net_unit_price'], item['discount_type'], item['product_discount'], item['tax_type'], item['product_tax'], item['discount'], item['tax'], item['subtotal'] )) quotation_item_id = cursor.lastrowid # Insert batch allocations for allocation in item['batch_allocations']: cursor.execute(""" INSERT INTO quotation_product_batches ( quotation_item_id, batch_id, grn_id, quantity, cost, price, expiration_date, created_at ) VALUES (%s, %s, %s, %s, %s, %s, %s, NOW()) """, ( quotation_item_id, allocation['batch_id'], allocation.get('grn_id'), allocation['quantity'], allocation['cost'], allocation['price'], # ✅ Batch price allocation.get('expiration_date') )) conn.commit() print(f"✅ Quotation {quotation_code} updated successfully with {len(processed_items)} items") return jsonify({ 'success': True, 'message': 'Quotation updated successfully', 'quotation_id': quotation_id, 'quotation_code': quotation_code }), 200 except Exception as e: if conn: conn.rollback() print(f"❌ Error updating quotation: {e}") traceback.print_exc() return jsonify({'error': str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ========================================== # DELETE QUOTATION # ========================================== @quotation_bp.route('/delete_quotation/', methods=['DELETE']) @jwt_required() @role_required('admin') def delete_quotation(quotation_id): """Delete quotation (admin only) - CASCADE will delete items and batch allocations""" conn = get_db_connection() if not conn: return jsonify({'error': 'Database connection failed'}), 500 cursor = conn.cursor(dictionary=True) try: cursor.execute("SELECT quotation_code FROM quotations WHERE quotation_id = %s", (quotation_id,)) quotation = cursor.fetchone() if not quotation: return jsonify({'error': 'Quotation not found'}), 404 quotation_code = quotation['quotation_code'] # Delete quotation (CASCADE will handle items and batch allocations) cursor.execute("DELETE FROM quotations WHERE quotation_id = %s", (quotation_id,)) conn.commit() print(f"✅ Quotation {quotation_code} deleted (with all items and batch allocations)") return jsonify({ 'success': True, 'message': 'Quotation deleted successfully', 'quotation_id': quotation_id, 'quotation_code': quotation_code }), 200 except Exception as e: conn.rollback() print(f"❌ Error deleting quotation: {e}") traceback.print_exc() return jsonify({'error': str(e)}), 500 finally: cursor.close() conn.close() # ========================================== # GET BATCH ALLOCATION DETAILS # ========================================== @quotation_bp.route('/get_quotation_batch_details/', methods=['GET']) @jwt_required() @role_required('admin', 'manager', 'cashier') def get_quotation_batch_details(quotation_id): """Get detailed batch allocation breakdown for a quotation""" conn = get_db_connection() if not conn: return jsonify({'error': 'Database connection failed'}), 500 cursor = conn.cursor(dictionary=True) try: cursor.execute(""" SELECT q.quotation_code, qi.item_id, p.product_name, pv.variation_name, qi.quantity as item_quantity, qi.price as item_price, qpb.batch_id, pb.batch_number, qpb.grn_id, g.grn_code, qpb.quantity as batch_quantity, qpb.cost as batch_cost, qpb.price as batch_price, qpb.expiration_date, pb.manufacture_date, (qpb.quantity * qpb.price) as batch_subtotal FROM quotations q JOIN quotation_items qi ON q.quotation_id = qi.quotation_id JOIN quotation_product_batches qpb ON qi.item_id = qpb.quotation_item_id JOIN products p ON qi.product_id = p.id LEFT JOIN product_variations pv ON qi.variation_id = pv.id LEFT JOIN product_batches pb ON qpb.batch_id = pb.batch_id LEFT JOIN grn g ON qpb.grn_id = g.grn_id WHERE q.quotation_id = %s ORDER BY qi.item_id, qpb.quotation_product_batch_id """, (quotation_id,)) batch_details = cursor.fetchall() if not batch_details: return jsonify({'error': 'Quotation not found or has no batch allocations'}), 404 # Format dates for detail in batch_details: if detail.get('expiration_date'): detail['expiration_date'] = detail['expiration_date'].isoformat() if detail.get('manufacture_date'): detail['manufacture_date'] = detail['manufacture_date'].isoformat() # Convert Decimal to float for field in ['item_quantity', 'item_price', 'batch_quantity', 'batch_cost', 'batch_price', 'batch_subtotal']: if detail.get(field) is not None: detail[field] = float(detail[field]) return jsonify({ 'success': True, 'quotation_code': batch_details[0]['quotation_code'], 'batch_allocations': batch_details, 'total_allocations': len(batch_details) }), 200 except Exception as e: print(f"❌ Error fetching batch details: {e}") traceback.print_exc() return jsonify({'error': str(e)}), 500 finally: cursor.close() conn.close() # ========================================== # CONVERT QUOTATION TO SALE - NEW ENDPOINT # ========================================== @quotation_bp.route('/convert_quotation_to_sale/', methods=['POST']) @jwt_required() @role_required('admin', 'cashier') def convert_quotation_to_sale(quotation_id): """ Convert an existing quotation to a sale invoice ✅ Validates quotation exists and can be converted ✅ Creates sale invoice with all items ✅ Maintains batch allocations (FIFO) ✅ Deducts stock from warehouse_stock ✅ Updates quotation status to 'accepted' and links to sale ✅ Prevents duplicate conversions """ conn = None cursor = None try: user_id = get_jwt_identity() conn = get_db_connection() if not conn: return jsonify({'error': 'Database connection failed'}), 500 cursor = conn.cursor(dictionary=True, buffered=True) conn.start_transaction() print(f"\n{'='*80}") print(f"🔄 CONVERTING QUOTATION {quotation_id} TO SALE") print(f"{'='*80}") # ========================================== # 1. GET QUOTATION DETAILS # ========================================== cursor.execute(""" SELECT q.*, c.name as customer_name, w.warehouse_name, s.store_name FROM quotations q LEFT JOIN customers c ON q.customer_id = c.id LEFT JOIN warehouses w ON q.warehouse_id = w.id LEFT JOIN stores s ON q.store_id = s.id WHERE q.quotation_id = %s """, (quotation_id,)) quotation = cursor.fetchone() if not quotation: return jsonify({'error': 'Quotation not found'}), 404 # ✅ Check if already converted if quotation.get('converted_to_sale_id'): return jsonify({ 'error': 'Quotation already converted to sale', 'invoice_id': quotation['converted_to_sale_id'] }), 400 # ✅ Validate quotation status if quotation['status'] not in ['sent', 'accepted', 'pending']: return jsonify({ 'error': f"Cannot convert quotation with status '{quotation['status']}'" }), 400 print(f"✅ Quotation: {quotation['quotation_code']}") print(f" Customer: {quotation.get('customer_name', 'Walk-in')}") print(f" Status: {quotation['status']}") print(f" Grand Total: LKR {quotation['grand_total']}") # ========================================== # 2. GET QUOTATION ITEMS WITH BATCH ALLOCATIONS # ========================================== cursor.execute(""" SELECT qi.*, p.product_name, p.sku, pv.variation_name, pv.variation_type, pv.variation_sku FROM quotation_items qi LEFT JOIN products p ON qi.product_id = p.id LEFT JOIN product_variations pv ON qi.variation_id = pv.id WHERE qi.quotation_id = %s ORDER BY qi.item_id """, (quotation_id,)) quotation_items = cursor.fetchall() if not quotation_items or len(quotation_items) == 0: return jsonify({'error': 'Quotation has no items'}), 400 print(f"\n📦 Processing {len(quotation_items)} items...") # Get batch allocations for each item items_with_batches = [] for item in quotation_items: cursor.execute(""" SELECT qpb.*, pb.batch_number, g.grn_code FROM quotation_product_batches qpb LEFT JOIN product_batches pb ON qpb.batch_id = pb.batch_id LEFT JOIN grn g ON qpb.grn_id = g.grn_id WHERE qpb.quotation_item_id = %s ORDER BY qpb.quotation_product_batch_id """, (item['item_id'],)) batch_allocations = cursor.fetchall() if not batch_allocations or len(batch_allocations) == 0: raise ValueError( f"No batch allocations found for item: {item.get('product_name', 'Unknown')}" ) item['batch_allocations'] = batch_allocations items_with_batches.append(item) print(f" ✅ Item: {item.get('product_name', 'Unknown')} - {len(batch_allocations)} batches") # ========================================== # 3. VALIDATE STOCK AVAILABILITY # ========================================== print(f"\n🔍 Validating stock availability...") for item in items_with_batches: for batch_alloc in item['batch_allocations']: cursor.execute(""" SELECT COALESCE(quantity, 0) as available_quantity FROM warehouse_stock WHERE product_id = %s AND warehouse_id = %s AND store_id = %s AND batch_id = %s """, ( item['product_id'], item['warehouse_id'], quotation['store_id'], batch_alloc['batch_id'] )) stock_check = cursor.fetchone() available = safe_float(stock_check['available_quantity']) if stock_check else 0 required = safe_float(batch_alloc['quantity']) if available < required: product_name = item.get('product_name', 'Unknown Product') batch_number = batch_alloc.get('batch_number', 'Unknown') raise ValueError( f"Insufficient stock for {product_name} (Batch: {batch_number}). " f"Required: {required}, Available: {available}" ) print(f"✅ All stock validated") # ========================================== # 4. CREATE SALE INVOICE # ========================================== invoice_code = f"INV-{datetime.now().strftime('%Y%m%d%H%M%S')}" # Calculate totals (use quotation totals) subtotal = safe_float(quotation['subtotal']) order_tax = safe_float(quotation['tax']) order_discount = safe_float(quotation['discount']) grand_total = safe_float(quotation['grand_total']) print(f"\n💰 Creating Sale Invoice: {invoice_code}") print(f" Subtotal: LKR {subtotal:.2f}") print(f" Tax: LKR {order_tax:.2f}") print(f" Discount: LKR {order_discount:.2f}") print(f" Grand Total: LKR {grand_total:.2f}") cursor.execute(""" INSERT INTO invoice_sale ( invoice_code, customer_id, warehouse_id, store_id, sale_date, invoiceTotal, tenderAmount, remainingAmount, payment_method, payment_status, tax, discount, status, note, cashier_user_id, created_at ) VALUES ( %s, %s, %s, %s, NOW(), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW() ) """, ( invoice_code, quotation['customer_id'], quotation['warehouse_id'], quotation['store_id'], grand_total, grand_total, # tenderAmount = grand_total (full payment) 0, # remainingAmount = 0 (paid in full) 'paid', # payment_method 'paid', # payment_status order_tax, order_discount, 'received', # status f"Converted from quotation: {quotation['quotation_code']}", user_id )) invoice_id = cursor.lastrowid print(f"✅ Invoice created with ID: {invoice_id}") # ========================================== # 5. CREATE SALE PRODUCTS & DEDUCT STOCK # ========================================== print(f"\n📦 Creating sale items and deducting stock...") for item in items_with_batches: # Insert sale_product cursor.execute(""" INSERT INTO sale_products ( invoice_id, product_id, variation_id, warehouse_id, quantity, price, unit_price, total, discount_type, product_discount, tax_type, product_tax, discount, tax, sale_unit, created_at ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW() ) """, ( invoice_id, item['product_id'], item['variation_id'], item['warehouse_id'], item['quantity'], item['price'], item['net_unit_price'], item['subtotal'], item['discount_type'], item['product_discount'], item['tax_type'], item['product_tax'], item['discount'], item['tax'], 'Unit' # Default unit )) sale_product_id = cursor.lastrowid product_name = item.get('product_name', 'Unknown') print(f" ✅ Sale product created: {product_name} (ID: {sale_product_id})") # ✅ Insert batch allocations into sale_product_items for batch_alloc in item['batch_allocations']: cursor.execute(""" INSERT INTO sale_product_items ( sale_product_id, batch_id, grn_id, quantity, cost, expiration_date, created_at ) VALUES (%s, %s, %s, %s, %s, %s, NOW()) """, ( sale_product_id, batch_alloc['batch_id'], batch_alloc['grn_id'], batch_alloc['quantity'], batch_alloc['cost'], batch_alloc.get('expiration_date') )) # ✅ DEDUCT STOCK from warehouse_stock cursor.execute(""" UPDATE warehouse_stock SET quantity = quantity - %s, updated_at = NOW() WHERE product_id = %s AND warehouse_id = %s AND store_id = %s AND batch_id = %s """, ( batch_alloc['quantity'], item['product_id'], item['warehouse_id'], quotation['store_id'], batch_alloc['batch_id'] )) if cursor.rowcount == 0: raise ValueError( f"Failed to deduct stock for batch {batch_alloc.get('batch_number', 'Unknown')}" ) print(f" ✅ Batch {batch_alloc.get('batch_number', 'N/A')}: " f"Deducted {batch_alloc['quantity']} units (GRN: {batch_alloc.get('grn_code', 'N/A')})") # ========================================== # 6. UPDATE QUOTATION # ========================================== cursor.execute(""" UPDATE quotations SET status = 'accepted', converted_to_sale_id = %s, updated_at = NOW() WHERE quotation_id = %s """, (invoice_id, quotation_id)) print(f"\n✅ Quotation updated: Status = 'accepted', Linked to Invoice {invoice_id}") # ========================================== # 7. LINK IN JUNCTION TABLE # ========================================== cursor.execute(""" INSERT INTO invoice_sale_for_quotations_link (invoice_id, quotation_id) VALUES (%s, %s) """, (invoice_id, quotation_id)) print(f"✅ Junction table updated") # ========================================== # 8. COMMIT TRANSACTION # ========================================== conn.commit() print(f"\n{'='*80}") print(f"✅✅✅ CONVERSION SUCCESSFUL!") print(f" Quotation: {quotation['quotation_code']}") print(f" Invoice: {invoice_code} (ID: {invoice_id})") print(f" Items Converted: {len(items_with_batches)}") print(f" Grand Total: LKR {grand_total:.2f}") print(f"{'='*80}\n") return jsonify({ 'success': True, 'message': f'Quotation {quotation["quotation_code"]} successfully converted to sale', 'invoice_id': invoice_id, 'invoice_code': invoice_code, 'quotation_code': quotation['quotation_code'], 'grand_total': grand_total }), 201 except ValueError as err: if conn: conn.rollback() print(f"\n❌ Validation Error: {err}") return jsonify({'error': str(err)}), 400 except mysql.connector.IntegrityError as err: if conn: conn.rollback() print(f"\n❌ Database Integrity Error: {err}") return jsonify({'error': f'Database constraint error: {str(err)}'}), 400 except mysql.connector.Error as err: if conn: conn.rollback() print(f"\n❌ Database Error: {err}") traceback.print_exc() return jsonify({'error': f'Database error: {str(err)}'}), 500 except Exception as e: if conn: conn.rollback() print(f"\n❌ Unexpected Error: {e}") traceback.print_exc() return jsonify({'error': f'Server error: {str(e)}'}), 500 finally: if cursor: cursor.close() if conn: conn.close() print("\n🔒 Database connection closed\n") # ========================================== # ADD THIS ENDPOINT TO quotation.py # ========================================== @quotation_bp.route('/quotations//mark_converted', methods=['PUT']) @jwt_required() @role_required('admin', 'cashier') def mark_quotation_as_converted(quotation_id): """ Mark a quotation as converted to sale This is called after a sale is successfully created from a quotation in POS Request Body: { "invoice_id": 123 // The ID of the created sale invoice } """ conn = None cursor = None try: data = request.get_json() invoice_id = data.get('invoice_id') if not invoice_id: return jsonify({'error': 'invoice_id is required'}), 400 conn = get_db_connection() if not conn: return jsonify({'error': 'Database connection failed'}), 500 cursor = conn.cursor(dictionary=True) conn.start_transaction() # Check if quotation exists cursor.execute(""" SELECT quotation_id, quotation_code, status, converted_to_sale_id FROM quotations WHERE quotation_id = %s """, (quotation_id,)) quotation = cursor.fetchone() if not quotation: return jsonify({'error': 'Quotation not found'}), 404 # Check if already converted if quotation['converted_to_sale_id']: return jsonify({ 'error': 'Quotation already converted', 'converted_to_sale_id': quotation['converted_to_sale_id'] }), 400 # Verify invoice exists cursor.execute(""" SELECT invoice_id, invoice_code FROM invoice_sale WHERE invoice_id = %s """, (invoice_id,)) invoice = cursor.fetchone() if not invoice: return jsonify({'error': 'Invoice not found'}), 404 # Update quotation cursor.execute(""" UPDATE quotations SET status = 'accepted', converted_to_sale_id = %s, updated_at = NOW() WHERE quotation_id = %s """, (invoice_id, quotation_id)) # Add to junction table try: cursor.execute(""" INSERT INTO invoice_sale_for_quotations_link (invoice_id, quotation_id) VALUES (%s, %s) """, (invoice_id, quotation_id)) except mysql.connector.IntegrityError: # Link already exists, that's okay pass conn.commit() print(f"✅ Quotation {quotation['quotation_code']} marked as converted to sale {invoice['invoice_code']}") return jsonify({ 'success': True, 'message': 'Quotation marked as converted successfully', 'quotation_id': quotation_id, 'quotation_code': quotation['quotation_code'], 'invoice_id': invoice_id, 'invoice_code': invoice['invoice_code'] }), 200 except mysql.connector.Error as err: if conn: conn.rollback() print(f"❌ Database Error: {err}") return jsonify({'error': f'Database error: {str(err)}'}), 500 except Exception as e: if conn: conn.rollback() print(f"❌ Error: {e}") traceback.print_exc() return jsonify({'error': str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ============================================ # ADD THIS TO quotation.py # ============================================ @quotation_bp.route('/quotation_submit_invoice', methods=['POST']) @jwt_required() @role_required('admin', 'cashier') def quotation_submit_invoice(): """ Create sale invoice from quotation data (loaded in POS). This endpoint is specifically for quotations loaded into POS via sessionStorage. ✅ Uses quotation batch allocations if available ✅ Falls back to FIFO if no batch allocations ✅ Updates quotation status to 'accepted' ✅ Links quotation to sale invoice ✅ Multi-warehouse support ✅ Multiple payment methods ✅ Price-aware stock deduction Expected JSON: { "quotation_id": 123, "invoice_code": null, "tenderAmount": 5000.00, "remainingAmount": 0.00, "invoiceTotal": 5000.00, "products": [...], "customer_id": 5, "discountValue": 100.00, "status": "received", "warehouse_id": 1, "store_id": 1, "payment_methods": [...], "payment_notes": "...", "payment_status": "paid" } """ conn = None cursor = None try: data = request.get_json() print("\n" + "="*80) print("📋 QUOTATION SUBMIT INVOICE - PROCESSING") print("="*80) print(json.dumps(data, indent=2, default=str)) print("="*80) # ========================================== # 1. EXTRACT AND VALIDATE DATA # ========================================== quotation_id = data.get('quotation_id') invoice_code = data.get('invoice_code') tenderAmount = float(data.get('tenderAmount', 0)) invoiceTotal = float(data.get('invoiceTotal', 0)) remainingAmount = float(data.get('remainingAmount', 0)) discount_value = float(data.get('discountValue', 0)) customer_id = data.get('customer_id') products = data.get('products', []) note = data.get('note', '') payment_notes = data.get('payment_notes', '') tax = float(data.get('tax', 0)) warehouse_id = data.get('warehouse_id') store_id = data.get('store_id') payment_status = data.get('payment_status', 'paid') payment_methods = data.get('payment_methods') # Validate required fields if not quotation_id: return jsonify({"error": "Quotation ID is required"}), 400 if not store_id: return jsonify({"error": "Store ID is required"}), 400 if not products or len(products) == 0: return jsonify({"error": "At least one product is required"}), 400 # Auto-determine status if payment_status == 'paid': status = 'received' else: status = 'suspended' # Get current user current_user = get_jwt_identity() # ========================================== # 2. DATABASE CONNECTION & TRANSACTION # ========================================== conn = get_db_connection() if not conn: return jsonify({"error": "Database connection failed"}), 500 cursor = conn.cursor(dictionary=True, buffered=True) cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED") conn.start_transaction() print("\n🔄 Transaction started") # ========================================== # 3. VERIFY QUOTATION EXISTS AND NOT ALREADY CONVERTED # ========================================== cursor.execute(""" SELECT quotation_id, quotation_code, customer_id, warehouse_id, store_id, status, converted_to_sale_id, grand_total FROM quotations WHERE quotation_id = %s """, (quotation_id,)) quotation = cursor.fetchone() if not quotation: raise ValueError(f"Quotation {quotation_id} not found") if quotation.get('converted_to_sale_id'): return jsonify({ 'error': 'Quotation already converted to sale', 'invoice_id': quotation['converted_to_sale_id'] }), 400 print(f"✅ Quotation: {quotation['quotation_code']}") print(f" Status: {quotation['status']}") print(f" Grand Total: Rs. {quotation['grand_total']}") # ========================================== # 4. GENERATE INVOICE CODE # ========================================== if not invoice_code: today_str = datetime.now().strftime("%Y%m%d") cursor.execute("SELECT COUNT(*) as count FROM invoice_sale WHERE DATE(created_at) = CURDATE()") count = cursor.fetchone()["count"] + 1 invoice_code = f"INV-{today_str}-{count:04d}" print(f"📝 Invoice Code: {invoice_code}") # ========================================== # 5. DETERMINE PRIMARY PAYMENT METHOD # ========================================== if not payment_methods or len(payment_methods) == 0: primary_payment_method = "unpaid" elif len(payment_methods) == 1: method_id, method_name = get_payment_method_id_and_name(cursor, payment_methods[0]['type']) primary_payment_method = method_name.lower() else: primary_payment_method = "multiple" print(f"💳 Primary Payment Method: {primary_payment_method}") # ========================================== # 6. CREATE INVOICE # ========================================== print("\n📄 Creating sale invoice...") cursor.execute(""" INSERT INTO invoice_sale ( invoice_code, tenderAmount, remainingAmount, invoiceTotal, payment_method, payment_status, customer_id, discount, status, note, tax, warehouse_id, store_id, cashier_user_id, created_at ) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,NOW()) """, ( invoice_code, tenderAmount, remainingAmount, invoiceTotal, primary_payment_method, payment_status, customer_id, discount_value, status, f"Converted from quotation: {quotation['quotation_code']}\n{note}\nPayment Notes: {payment_notes}" if payment_notes else f"Converted from quotation: {quotation['quotation_code']}\n{note}", tax, warehouse_id, store_id, current_user )) invoice_id = cursor.lastrowid print(f"✅ Invoice created (ID: {invoice_id})") # ========================================== # 7. INSERT PAYMENT METHODS # ========================================== if payment_methods and payment_status != 'unpaid': print(f"\n💰 Processing {len(payment_methods)} payment method(s)...") for idx, payment in enumerate(payment_methods, 1): try: payment_type_value = payment.get('type') payment_amount = float(payment.get('amount', 0)) if payment_amount > 0: method_id, method_name = get_payment_method_id_and_name(cursor, payment_type_value) print(f" [{idx}] {method_name} (ID: {method_id}): Rs. {payment_amount:.2f}") cursor.execute(""" INSERT INTO sale_payments ( sale_id, payment_method, amount, payment_date ) VALUES (%s, %s, %s, NOW()) """, (invoice_id, method_id, payment_amount)) payment_record_id = cursor.lastrowid print(f" ✅ Payment record created (ID: {payment_record_id})") except Exception as pe: print(f" ❌ Error processing payment {idx}: {pe}") raise # ========================================== # 8. GET QUOTATION ITEMS WITH BATCH ALLOCATIONS # ========================================== print(f"\n📦 Loading quotation items...") cursor.execute(""" SELECT qi.item_id, qi.product_id, qi.variation_id, qi.warehouse_id, qi.quantity, qi.price, qi.net_unit_price, qi.discount_type, qi.product_discount, qi.tax_type, qi.product_tax, qi.discount, qi.tax, qi.subtotal FROM quotation_items qi WHERE qi.quotation_id = %s ORDER BY qi.item_id """, (quotation_id,)) quotation_items = cursor.fetchall() if not quotation_items or len(quotation_items) == 0: raise ValueError("Quotation has no items") print(f"✅ Found {len(quotation_items)} quotation items") # ========================================== # 9. PROCESS EACH PRODUCT # ========================================== print(f"\n📦 Processing products and deducting stock...") for idx, q_item in enumerate(quotation_items, 1): product_id = int(q_item['product_id']) variation_id = int(q_item['variation_id']) if q_item['variation_id'] else None quantity = float(q_item['quantity']) selected_price = float(q_item['net_unit_price'] or q_item['price']) # Get warehouse from quotation item or fallback product_warehouse_id = q_item.get('warehouse_id') or warehouse_id if not product_warehouse_id: raise ValueError(f"Warehouse ID missing for product {product_id}") print(f"\n [{idx}/{len(quotation_items)}] Product {product_id}") print(f" Variation: {variation_id or 'None'}") print(f" Quantity: {quantity}") print(f" Price: Rs. {selected_price}") print(f" Warehouse: {product_warehouse_id}") # ========================================== # 9a. GET BATCH ALLOCATIONS FROM QUOTATION # ========================================== cursor.execute(""" SELECT qpb.batch_id, qpb.grn_id, qpb.quantity, qpb.cost, qpb.price, qpb.expiration_date, pb.batch_number FROM quotation_product_batches qpb LEFT JOIN product_batches pb ON qpb.batch_id = pb.batch_id WHERE qpb.quotation_item_id = %s ORDER BY qpb.quotation_product_batch_id """, (q_item['item_id'],)) quotation_batches = cursor.fetchall() # ========================================== # 9b. DETERMINE SALE UNIT # ========================================== # Try to get from product data sent from frontend frontend_product = next((p for p in products if int(str(p['product_id']).split('-')[0]) == product_id), None) if frontend_product and frontend_product.get('sales_unit'): sale_unit_identifier = get_unit_identifier(cursor, frontend_product['sales_unit']) else: # Get from product's default sale unit cursor.execute(""" SELECT u.unit_short FROM products p LEFT JOIN units u ON p.sale_unit_id = u.id WHERE p.id = %s """, (product_id,)) unit_result = cursor.fetchone() sale_unit_identifier = unit_result['unit_short'] if unit_result and unit_result['unit_short'] else 'Unit' print(f" Sale Unit: {sale_unit_identifier}") # ========================================== # 9c. INSERT SALE_PRODUCTS RECORD # ========================================== cursor.execute(""" INSERT INTO sale_products ( invoice_id, product_id, variation_id, warehouse_id, price, quantity, total, discount_type, product_discount, tax_type, product_tax, discount, tax, unit_price, sale_unit, created_at ) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,NOW()) """, ( invoice_id, product_id, variation_id, product_warehouse_id, selected_price, quantity, q_item['subtotal'], q_item['discount_type'], float(q_item['product_discount'] or 0), q_item['tax_type'], float(q_item['product_tax'] or 0), float(q_item['discount'] or 0), float(q_item['tax'] or 0), selected_price, sale_unit_identifier )) sale_product_id = cursor.lastrowid print(f" ✅ Sale product created (ID: {sale_product_id})") # ========================================== # 9d. STOCK DEDUCTION WITH BATCH TRACKING # ========================================== if quotation_batches and len(quotation_batches) > 0: # ✅ USE QUOTATION BATCH ALLOCATIONS print(f" 📦 Using {len(quotation_batches)} quotation batch allocations") for batch_alloc in quotation_batches: batch_id = batch_alloc['batch_id'] batch_qty = float(batch_alloc['quantity']) batch_cost = float(batch_alloc['cost'] or 0) grn_id = batch_alloc['grn_id'] expiry = batch_alloc['expiration_date'] print(f" • Batch {batch_alloc['batch_number']}: {batch_qty} units") # Deduct from product_batches cursor.execute(""" UPDATE product_batches SET remaining_quantity = remaining_quantity - %s WHERE batch_id = %s """, (batch_qty, batch_id)) if cursor.rowcount != 1: raise Exception(f"Failed to update batch {batch_id}") # Deduct from warehouse_stock cursor.execute(""" UPDATE warehouse_stock SET quantity = quantity - %s WHERE batch_id = %s AND warehouse_id = %s AND store_id = %s """, (batch_qty, batch_id, product_warehouse_id, store_id)) if cursor.rowcount != 1: raise Exception(f"Failed to update warehouse_stock for batch {batch_id}") # Insert sale_product_items for audit trail cursor.execute(""" INSERT INTO sale_product_items ( sale_product_id, batch_id, grn_id, quantity, cost, expiration_date, created_at ) VALUES (%s, %s, %s, %s, %s, %s, NOW()) """, ( sale_product_id, batch_id, grn_id, batch_qty, batch_cost, expiry )) print(f" ✅ Stock deducted and tracked") else: # ✅ FALLBACK TO FIFO print(f" ⚠️ No quotation batch allocations - using FIFO") batches_used = deduct_stock_from_batches_fifo_warehouse( cursor, product_id, variation_id, quantity, product_warehouse_id, store_id, sale_product_id, price=selected_price ) print(f" ✅ FIFO: Used {len(batches_used)} batches") for b in batches_used: print(f" • B{b['batch_id']}: {b['quantity']:.2f} units") # ========================================== # 10. UPDATE QUOTATION STATUS # ========================================== print(f"\n📝 Updating quotation status...") cursor.execute(""" UPDATE quotations SET status = 'accepted', converted_to_sale_id = %s, updated_at = NOW() WHERE quotation_id = %s """, (invoice_id, quotation_id)) print(f"✅ Quotation status updated to 'accepted'") # ========================================== # 11. LINK IN JUNCTION TABLE # ========================================== try: cursor.execute(""" INSERT INTO invoice_sale_for_quotations_link (invoice_id, quotation_id) VALUES (%s, %s) """, (invoice_id, quotation_id)) print(f"✅ Junction table updated") except Exception as link_err: print(f"⚠️ Junction table insert failed (may already exist): {link_err}") # ========================================== # 12. COMMIT TRANSACTION # ========================================== conn.commit() print("\n" + "="*80) print("✅✅✅ QUOTATION INVOICE CREATED SUCCESSFULLY") print("="*80) print(f" Quotation: {quotation['quotation_code']}") print(f" Invoice: {invoice_code} (ID: {invoice_id})") print(f" Products: {len(quotation_items)}") print(f" Grand Total: Rs. {invoiceTotal:.2f}") print(f" Payment Status: {payment_status}") print("="*80 + "\n") return jsonify({ "success": True, "message": f"Invoice created successfully from quotation {quotation['quotation_code']}", "invoice_id": invoice_id, "invoice_code": invoice_code, "quotation_id": quotation_id, "quotation_code": quotation['quotation_code'], "warehouse_id": warehouse_id, "store_id": store_id, "payment_status": payment_status, "invoice_status": status, "payment_method": primary_payment_method, "grand_total": invoiceTotal }), 201 except ValueError as err: if conn: conn.rollback() print(f"\n❌ Validation Error: {err}") return jsonify({'error': str(err)}), 400 except Exception as e: if conn: conn.rollback() print(f"\n❌ Error in quotation_submit_invoice: {e}") import traceback traceback.print_exc() return jsonify({'error': str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() print("\n🔒 Database connection closed\n") # ============================================ # HELPER FUNCTIONS (IF NOT ALREADY IN quotation.py) # ============================================ def get_payment_method_id_and_name(cursor, payment_type_id): """ Get payment method ID and name from ID with enhanced error handling. Args: cursor: Database cursor payment_type_id: Payment method ID (int or string) Returns: tuple: (method_id, method_name) or (1, 'Cash') as fallback """ try: method_id = int(payment_type_id) cursor.execute(""" SELECT id, method_name FROM payment_methods WHERE id = %s AND is_active = 1 """, (method_id,)) result = cursor.fetchone() if result: return result['id'], result['method_name'] print(f"⚠️ WARNING: Payment method ID {method_id} not found - defaulting to Cash") # Try to get Cash payment method cursor.execute(""" SELECT id, method_name FROM payment_methods WHERE LOWER(method_name) = 'cash' AND is_active = 1 LIMIT 1 """) cash_result = cursor.fetchone() if cash_result: return cash_result['id'], cash_result['method_name'] return 1, 'Cash' except (ValueError, TypeError) as e: print(f"⚠️ Error converting payment_type_id to int: {e} - defaulting to Cash") cursor.execute(""" SELECT id, method_name FROM payment_methods WHERE LOWER(method_name) = 'cash' AND is_active = 1 LIMIT 1 """) cash_result = cursor.fetchone() if cash_result: return cash_result['id'], cash_result['method_name'] return 1, 'Cash' def get_unit_identifier(cursor, unit_value): """ Get unit SHORT NAME from unit ID. Args: cursor: Database cursor unit_value: Unit ID (int or string) Returns: str: Unit short name (e.g., "kg", "pcs", "box") """ if unit_value is None: return None try: unit_id = int(unit_value) cursor.execute(""" SELECT unit_short FROM units WHERE id = %s """, (unit_id,)) result = cursor.fetchone() if result and result['unit_short']: return result['unit_short'] print(f"⚠️ WARNING: Unit ID {unit_id} not found - using ID as fallback") return str(unit_id) except (ValueError, TypeError): return str(unit_value) def deduct_stock_from_batches_fifo_warehouse(cursor, product_id, variation_id, quantity, warehouse_id, store_id, sale_product_id, price=None): """ Deduct stock using FIFO (fallback if no quotation batch allocations). Args: cursor: Database cursor product_id: Product ID variation_id: Variation ID (can be None) quantity: Quantity to deduct warehouse_id: Warehouse ID store_id: Store ID sale_product_id: Sale product ID price: Optional price filter Returns: list: Batches used """ remaining_qty = float(quantity) batches_used = [] # Build query if variation_id: query = """ SELECT pb.batch_id, pb.grn_id, pb.remaining_quantity, pb.cost, pb.price, pb.expiration_date, ws.id as warehouse_stock_id, ws.quantity as ws_qty FROM product_batches pb INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id WHERE pb.variation_id = %s AND pb.remaining_quantity > 0 AND ws.warehouse_id = %s AND ws.quantity > 0 """ params = [variation_id, warehouse_id] if price is not None: query += " AND pb.price = %s" params.append(float(price)) query += " ORDER BY pb.batch_id ASC FOR UPDATE" cursor.execute(query, tuple(params)) else: query = """ SELECT pb.batch_id, pb.grn_id, pb.remaining_quantity, pb.cost, pb.price, pb.expiration_date, ws.id as warehouse_stock_id, ws.quantity as ws_qty FROM product_batches pb INNER JOIN warehouse_stock ws ON pb.batch_id = ws.batch_id WHERE pb.product_id = %s AND (pb.variation_id IS NULL OR pb.variation_id = 0) AND pb.remaining_quantity > 0 AND ws.warehouse_id = %s AND ws.quantity > 0 """ params = [product_id, warehouse_id] if price is not None: query += " AND pb.price = %s" params.append(float(price)) query += " ORDER BY pb.batch_id ASC FOR UPDATE" cursor.execute(query, tuple(params)) batches = cursor.fetchall() if not batches: raise Exception(f"No stock found in warehouse {warehouse_id}") # FIFO deduction for batch in batches: if remaining_qty <= 0: break available_in_batch = min(float(batch['remaining_quantity']), float(batch['ws_qty'])) deduct_now = min(remaining_qty, available_in_batch) # Update product_batches cursor.execute(""" UPDATE product_batches SET remaining_quantity = remaining_quantity - %s WHERE batch_id = %s """, (deduct_now, batch['batch_id'])) # Update warehouse_stock cursor.execute(""" UPDATE warehouse_stock SET quantity = quantity - %s WHERE id = %s """, (deduct_now, batch['warehouse_stock_id'])) # Insert sale_product_items cursor.execute(""" INSERT INTO sale_product_items ( sale_product_id, batch_id, grn_id, quantity, cost, expiration_date ) VALUES (%s, %s, %s, %s, %s, %s) """, ( sale_product_id, batch['batch_id'], batch['grn_id'], deduct_now, batch['cost'], batch['expiration_date'] )) batches_used.append({ 'batch_id': batch['batch_id'], 'grn_id': batch['grn_id'], 'quantity': deduct_now, 'cost': batch['cost'], 'price': batch['price'] }) remaining_qty -= deduct_now if remaining_qty > 0.001: raise Exception(f"Insufficient stock. Short by {remaining_qty:.3f} units") return batches_used # ========================================== # ✅ ADD THIS NEW ENDPOINT TO quotation.py # Place it at the end of the file, before the last line # ========================================== @quotation_bp.route('/get_available_stock', methods=['GET']) @jwt_required() @role_required('admin', 'cashier') def get_available_stock(): """ Get real available stock for a product Query Parameters: - product_id (required): Product ID - warehouse_id (required): Warehouse ID - store_id (required): Store ID - variation_id (optional): Variation ID - price (optional): Price to match specific batches Returns: { "success": true, "available_stock": 150.00, "batches_count": 3, "product_name": "Product Name", "warehouse_name": "Warehouse 1" } """ conn = None cursor = None try: # Get query parameters product_id = request.args.get('product_id', type=int) warehouse_id = request.args.get('warehouse_id', type=int) store_id = request.args.get('store_id', type=int) variation_id = request.args.get('variation_id', type=int) price = request.args.get('price', type=float) # Validate required parameters if not product_id: return jsonify({'error': 'product_id is required'}), 400 if not warehouse_id: return jsonify({'error': 'warehouse_id is required'}), 400 if not store_id: return jsonify({'error': 'store_id is required'}), 400 conn = get_db_connection() if not conn: return jsonify({'error': 'Database connection failed'}), 500 cursor = conn.cursor(dictionary=True) print(f"\n{'='*60}") print(f"📊 GET AVAILABLE STOCK") print(f"{'='*60}") print(f" Product ID: {product_id}") print(f" Variation ID: {variation_id or 'None'}") print(f" Warehouse ID: {warehouse_id}") print(f" Store ID: {store_id}") print(f" Price Filter: {price or 'None'}") print(f"{'='*60}") # ========================================== # GET PRODUCT NAME # ========================================== if variation_id: cursor.execute(""" SELECT p.product_name, pv.variation_name, pv.variation_type FROM products p JOIN product_variations pv ON p.id = pv.product_id WHERE p.id = %s AND pv.id = %s """, (product_id, variation_id)) product_info = cursor.fetchone() if product_info: product_name = f"{product_info['product_name']} - {product_info['variation_name']}" else: product_name = f"Product {product_id} - Variation {variation_id}" else: cursor.execute(""" SELECT product_name FROM products WHERE id = %s """, (product_id,)) product_info = cursor.fetchone() if product_info: product_name = product_info['product_name'] else: product_name = f"Product {product_id}" # ========================================== # GET WAREHOUSE NAME # ========================================== cursor.execute(""" SELECT warehouse_name FROM warehouses WHERE id = %s """, (warehouse_id,)) warehouse_info = cursor.fetchone() warehouse_name = warehouse_info['warehouse_name'] if warehouse_info else f"Warehouse {warehouse_id}" # ========================================== # BUILD STOCK QUERY # ========================================== base_query = """ SELECT ws.id, ws.batch_id, ws.quantity, pb.price, pb.batch_number, pb.expiration_date FROM warehouse_stock ws INNER JOIN product_batches pb ON ws.batch_id = pb.batch_id WHERE ws.product_id = %s AND ws.warehouse_id = %s AND ws.store_id = %s AND ws.quantity > 0 """ params = [product_id, warehouse_id, store_id] # Add variation filter if variation_id: base_query += " AND ws.variation_id = %s" params.append(variation_id) else: base_query += " AND (ws.variation_id IS NULL OR ws.variation_id = 0)" # Add price filter (if provided) if price is not None: base_query += " AND pb.price = %s" params.append(price) # Check for expired batches base_query += """ AND (pb.expiration_date IS NULL OR pb.expiration_date >= CURDATE()) """ # Order by FIFO base_query += " ORDER BY pb.batch_id ASC" cursor.execute(base_query, params) stock_records = cursor.fetchall() # ========================================== # CALCULATE TOTAL AVAILABLE STOCK # ========================================== total_available = 0.0 batches_count = 0 batch_details = [] for record in stock_records: qty = safe_float(record['quantity']) total_available += qty batches_count += 1 batch_details.append({ 'batch_id': record['batch_id'], 'batch_number': record['batch_number'], 'quantity': qty, 'price': safe_float(record['price']), 'expiration_date': record['expiration_date'].isoformat() if record['expiration_date'] else None }) print(f"\n📦 Stock Summary:") print(f" Total Available: {total_available} units") print(f" Batches: {batches_count}") if batches_count > 0: print(f"\n Batch Details:") for batch in batch_details: print(f" • {batch['batch_number']}: {batch['quantity']} units @ Rs. {batch['price']:.2f}") print(f"{'='*60}\n") # ========================================== # RETURN RESPONSE # ========================================== return jsonify({ 'success': True, 'available_stock': total_available, 'batches_count': batches_count, 'product_id': product_id, 'variation_id': variation_id, 'product_name': product_name, 'warehouse_id': warehouse_id, 'warehouse_name': warehouse_name, 'store_id': store_id, 'price_filter': price, 'batches': batch_details }), 200 except Exception as e: print(f"\n❌ Error getting available stock: {e}") import traceback traceback.print_exc() return jsonify({ 'success': False, 'error': str(e), 'available_stock': 0 }), 500 finally: if cursor: cursor.close() if conn: conn.close()