""" services.py — Business logic: cash position, low-stock detection, reconciliation. These are pure computation functions with no route handling. """ from datetime import date, timedelta from db import q, run, today, now def get_cash_position(outlet_id, date_str): events = q("SELECT * FROM cash_events WHERE outlet_id=? AND recorded_at >= ? AND recorded_at < date(?, '+1 day')",(outlet_id,date_str,date_str)) float_amt= sum(e['amount'] for e in events if e['event_type']=='FLOAT') till_exp = sum(e['amount'] for e in events if e['event_type']=='TILL_EXPENSE') drop = sum(e['amount'] for e in events if e['event_type']=='CASH_DROP') last_sub = q("""SELECT cash_declared FROM cashier_submissions WHERE outlet_id=? AND submitted_at >= ? AND submitted_at < date(?, '+1 day') ORDER BY submitted_at DESC LIMIT 1""",(outlet_id,date_str,date_str),one=True) declared = last_sub['cash_declared'] if last_sub else 0 cash_rev = declared - float_amt + drop + till_exp return {'float':float_amt,'till_exp':till_exp,'drop':drop,'declared':declared,'cash_revenue':cash_rev} def calc_payroll(): users = q("SELECT id,name,role,pay_period,pay_amount FROM users WHERE active=1") daily = weekly = monthly = 0.0 breakdown = [] for u in users: p, a = u['pay_period'], u['pay_amount'] if p == 'daily': d=a; w=a*5; m=a*22 elif p == 'weekly': d=a/5; w=a; m=a*4 else: d=a/22; w=a/4.33; m=a daily += d; weekly += w; monthly += m breakdown.append({'id':u['id'],'name':u['name'],'role':u['role'], 'pay_period':p,'pay_amount':a, 'daily':round(d,2),'weekly':round(w,2),'monthly':round(m,2)}) return {'daily':round(daily,2),'weekly':round(weekly,2),'monthly':round(monthly,2),'breakdown':breakdown} def get_outlet_low_stock(outlet_id, date_str, threshold=5): products = q("SELECT * FROM products WHERE active=1") low = [] for p in products: ti = q("""SELECT COALESCE(SUM(qty_in),0) as t FROM stock_events WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day') AND event_type IN ('OPENING_DISPATCH','TOPUP')""",(outlet_id,p['id'],date_str,date_str),one=True)['t'] if ti == 0: continue wt = q("""SELECT COALESCE(SUM(qty_out),0) as t FROM stock_events WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day') AND event_type='WASTAGE'""",(outlet_id,p['id'],date_str,date_str),one=True)['t'] last = q("""SELECT si.qty_remaining FROM submission_items si JOIN cashier_submissions cs ON si.submission_id=cs.id WHERE cs.outlet_id=? AND si.product_id=? AND cs.submitted_at >= ? AND cs.submitted_at < date(?, '+1 day') ORDER BY cs.submitted_at DESC LIMIT 1""",(outlet_id,p['id'],date_str),one=True) remaining = last['qty_remaining'] if last else max(0, ti - wt) pct = (remaining / ti * 100) if ti > 0 else 100 if remaining <= threshold or pct <= 20: low.append({'product': dict(p), 'remaining': remaining, 'total_in': ti, 'pct': round(pct)}) return low def _build_reconciliation(outlet_id, d): """Compute opening stock, received, remaining, expected sold, cash/mpesa, variance.""" products = q("SELECT id, price FROM products WHERE active=1") opening_stock = received_stock = remaining_stock = expected_sold_value = 0.0 for p in products: pid = p['id'] price = p['price'] or 0 # Opening = dispatched dispatched = q("""SELECT COALESCE(SUM(qty_in),0) as t FROM stock_events WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day') AND event_type IN ('OPENING_DISPATCH','TOPUP')""",(outlet_id, pid, d, d), one=True)['t'] # Received (confirmed by cashier) confirmed = q("""SELECT COALESCE(SUM(qty_received),0) as t FROM outlet_receipts WHERE outlet_id=? AND product_id=? AND received_at >= ? AND received_at < date(?, '+1 day')""",(outlet_id, pid, d, d), one=True)['t'] # Wastage wastage = q("""SELECT COALESCE(SUM(qty_out),0) as t FROM stock_events WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day') AND event_type='WASTAGE'""",(outlet_id, pid, d, d), one=True)['t'] # Remaining (last submission) last_sub = q("""SELECT si.qty_remaining FROM submission_items si JOIN cashier_submissions cs ON si.submission_id=cs.id WHERE cs.outlet_id=? AND si.product_id=? AND cs.submitted_at >= ? AND cs.submitted_at < date(?, '+1 day') ORDER BY cs.submitted_at DESC LIMIT 1""", (outlet_id, pid, d, d), one=True) remaining = last_sub['qty_remaining'] if last_sub else max(0, (confirmed or dispatched) - wastage) sold = max(0, (confirmed or dispatched) - wastage - remaining) opening_stock += dispatched * price received_stock += (confirmed or dispatched) * price remaining_stock += remaining * price expected_sold_value += sold * price # Cash last_sub_cash = q("""SELECT cash_declared FROM cashier_submissions WHERE outlet_id=? AND submitted_at >= ? AND submitted_at < date(?, '+1 day') ORDER BY submitted_at DESC LIMIT 1""",(outlet_id, d, d), one=True) cash_declared = last_sub_cash['cash_declared'] if last_sub_cash else 0 # MPesa manual_mpesa = q("""SELECT COALESCE(SUM(amount),0) as t FROM outlet_mpesa WHERE outlet_id=? AND recorded_at >= ? AND recorded_at < date(?, '+1 day')""",(outlet_id, d, d), one=True)['t'] auto_mpesa = q("""SELECT COALESCE(SUM(amount),0) as t FROM mpesa_transactions WHERE outlet_id=? AND created_at >= ? AND created_at < date(?, '+1 day')""",(outlet_id, d, d), one=True)['t'] mpesa_total = manual_mpesa + auto_mpesa total_collected = cash_declared + mpesa_total variance = total_collected - expected_sold_value return { 'outlet_id': outlet_id, 'date': d, 'opening_stock': round(opening_stock, 2), 'received_stock': round(received_stock, 2), 'remaining_stock': round(remaining_stock, 2), 'expected_sold': round(expected_sold_value, 2), 'cash_declared': round(cash_declared, 2), 'mpesa_total': round(mpesa_total, 2), 'total_collected': round(total_collected, 2), 'variance': round(variance, 2), 'is_locked': False, } def calc_expected_revenue(outlet_id, date_str): products = q("SELECT id FROM products WHERE active=1") total = 0.0 for p in products: pid = p['id'] ti = q("""SELECT COALESCE(SUM(qty_in),0) as t FROM stock_events WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day') AND event_type IN ('OPENING_DISPATCH','TOPUP')""",(outlet_id,pid,date_str,date_str),one=True)['t'] if ti == 0: continue wt = q("""SELECT COALESCE(SUM(qty_out),0) as t FROM stock_events WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day') AND event_type='WASTAGE'""",(outlet_id,pid,date_str,date_str),one=True)['t'] last_sub = q("""SELECT si.qty_remaining FROM submission_items si JOIN cashier_submissions cs ON si.submission_id=cs.id WHERE cs.outlet_id=? AND si.product_id=? AND cs.submitted_at >= ? AND cs.submitted_at < date(?, '+1 day') ORDER BY cs.submitted_at DESC LIMIT 1""",(outlet_id,pid,date_str),one=True) remaining = last_sub['qty_remaining'] if last_sub else 0 sold = max(0, ti - wt - remaining) price = q("SELECT price FROM products WHERE id=?", (pid,), one=True) if price: total += sold * price['price'] return total def cost_per_unit(product_id): recipe = q("""SELECT r.qty_used, r.yields, i.cost_per_unit FROM recipes r JOIN ingredients i ON r.ingredient_id=i.id WHERE r.product_id=?""",(product_id,)) return sum((row['qty_used']/row['yields']) * row['cost_per_unit'] for row in recipe) def calc_ingredient_cost(date_str): reqs = q("SELECT id FROM requisitions WHERE for_date=? AND status='approved'", (date_str,)) total = 0.0 for r in reqs: items = q("""SELECT ri.qty_requested, i.cost_per_unit FROM requisition_items ri JOIN ingredients i ON ri.ingredient_id=i.id WHERE ri.requisition_id=?""",(r['id'],)) for item in items: total += item['qty_requested'] * item['cost_per_unit'] return total