"""Employee advance tracking — recorded in HR or synced from petty cash, recovered via payroll.""" from __future__ import annotations import re from datetime import date from decimal import Decimal from django.db import transaction from django.utils import timezone from human_resource.models import Deduction, PayrollSheet, StaffProfile from .journal_posting import PostingError from .models import ( ChartOfAccount, EmployeeAdvance, PayrollAdvanceRecovery, PettyCashDisbursement, PettyCashFund, ) from .posting_engine import post_balanced_lines, resolve_posting_rule ADVANCE_DEDUCTION_TITLE = "Employee Advance Recovery" DEFAULT_HR_RECOVERY_PERCENT = Decimal("25") RECOVERY_MODULES = {"full", "percentage", "fixed", "amount"} def _normalize_recovery_module(module: str) -> str: key = str(module or "fixed").strip().lower() if key == "amount": return "fixed" if key in RECOVERY_MODULES: return key return "fixed" def _decimal(value) -> Decimal: return Decimal(str(value or 0).replace(",", "")) def _quantize(value: Decimal) -> Decimal: return value.quantize(Decimal("0.01")) def get_or_create_advance_deduction(company_profile): far_future = date(2099, 12, 31) deduction, _created = Deduction.objects.get_or_create( company_profile=company_profile, deduction_title=ADVANCE_DEDUCTION_TITLE, recycle_bin=False, defaults={ "deduction_description": "Recovery of outstanding employee advances (petty cash or HR).", "deduction_type": "loan_repayment", "deduction_module": "fixed", "deduction_value": "0.00", "date_effective_from": date(2000, 1, 1), "date_effective_to": far_future, }, ) return deduction def _resolve_advances_account(*, branch) -> ChartOfAccount | None: if not branch: return None fund = PettyCashFund.objects.filter( company_branch=branch, is_active=True, employee_advances_account__isnull=False, ).first() if fund and fund.employee_advances_account_id: return fund.employee_advances_account from .posting_engine import _account_by_number for account_number in ("1150", "1200", "1300"): try: return _account_by_number(branch, account_number) except PostingError: continue return None def compute_installment_for_advance(advance: EmployeeAdvance) -> Decimal: """Per-payroll recovery slice for one advance (before net-pay cap).""" balance = _decimal(advance.balance_outstanding) if balance <= 0: return Decimal("0") module = advance.recovery_module or "fixed" value = _decimal(advance.recovery_value) if module == "percentage": pct = min(max(value, Decimal("0")), Decimal("100")) return _quantize(balance * pct / Decimal("100")) if module == "fixed": if value <= 0: return Decimal("0") return min(balance, value) return balance def compute_staff_recovery_installment(advances) -> Decimal: total = Decimal("0") for advance in advances: total += compute_installment_for_advance(advance) return _quantize(total) def _advance_row(adv: EmployeeAdvance) -> dict: balance = _decimal(adv.balance_outstanding) installment = compute_installment_for_advance(adv) return { "advance_id": str(adv.id), "reference_number": adv.reference_number, "description": adv.description, "source": adv.source, "original_amount": str(adv.original_amount), "balance_outstanding": str(balance), "recovery_module": adv.recovery_module, "recovery_value": str(adv.recovery_value), "payroll_installment": str(installment), "fund_name": adv.fund.fund_name if adv.fund else "", "disbursement_number": ( adv.petty_cash_disbursement.disbursement_number if adv.petty_cash_disbursement else "" ), "staff_id": str(adv.staff_profile_id), "staff_name": ( f"{adv.staff_profile.first_name or ''} {adv.staff_profile.last_name or ''}".strip() if adv.staff_profile else "" ), "status": adv.status, } def _ensure_staff_disbursement_advances(staff_id) -> None: disbursements = PettyCashDisbursement.objects.filter( request__requested_by_id=staff_id, employee_advance__isnull=True, ).select_related("request", "request__fund", "request__requested_by")[:50] for disbursement in disbursements: create_advance_from_disbursement(disbursement=disbursement) def create_advance_from_disbursement(*, disbursement: PettyCashDisbursement) -> EmployeeAdvance | None: req = disbursement.request if not req or not req.requested_by_id: return None fund = req.fund if not fund: return None company_profile = fund.company_branch.company_profile if fund.company_branch else None if not company_profile: return None get_or_create_advance_deduction(company_profile) advance, created = EmployeeAdvance.objects.get_or_create( petty_cash_disbursement=disbursement, defaults={ "staff_profile": req.requested_by, "company_branch": fund.company_branch, "fund": fund, "employee_advances_account": fund.employee_advances_account, "reference_number": disbursement.disbursement_number, "description": req.purpose or f"Petty cash advance {disbursement.disbursement_number}", "original_amount": disbursement.amount, "balance_outstanding": disbursement.amount, "source": "petty_cash", "recovery_module": "fixed", "recovery_value": disbursement.amount, "status": "open", }, ) if not created and advance.balance_outstanding < disbursement.amount: advance.original_amount = disbursement.amount advance.balance_outstanding = disbursement.amount advance.status = "open" advance.save(update_fields=[ "original_amount", "balance_outstanding", "status", "last_updated_on", ]) return advance @transaction.atomic def create_hr_employee_advance( *, staff: StaffProfile, amount, description: str, recorded_by: StaffProfile, recovery_module: str = "fixed", recovery_value=None, ) -> EmployeeAdvance: branch = staff.company_branch company_profile = branch.company_profile if branch else None if not company_profile: raise ValueError("Staff must belong to a branch with a company profile.") advance_amount = _decimal(amount) if advance_amount <= 0: raise ValueError("Advance amount must be positive.") module = _normalize_recovery_module(recovery_module) if recovery_value is None or str(recovery_value).strip() == "": if module == "percentage": value = DEFAULT_HR_RECOVERY_PERCENT elif module == "fixed": value = advance_amount else: value = advance_amount else: value = _decimal(recovery_value) if module == "percentage" and value <= 0: value = DEFAULT_HR_RECOVERY_PERCENT if module == "fixed" and value <= 0: value = advance_amount get_or_create_advance_deduction(company_profile) advances_account = _resolve_advances_account(branch=branch) stamp = re.sub(r"[^0-9]", "", timezone.now().strftime("%Y%m%d%H%M%S")) reference = f"HRADV{stamp}" return EmployeeAdvance.objects.create( staff_profile=staff, company_branch=branch, employee_advances_account=advances_account, source="hr", reference_number=reference, description=description or f"HR employee advance {reference}", original_amount=advance_amount, balance_outstanding=advance_amount, recovery_module=module, recovery_value=value, status="open", recorded_by=recorded_by, ) def update_advance_recovery_terms( *, advance: EmployeeAdvance, recovery_module: str, recovery_value, ) -> EmployeeAdvance: if advance.status in ("recovered", "settled"): raise ValueError("Cannot change recovery terms on a closed advance.") module = _normalize_recovery_module(recovery_module) value = _decimal(recovery_value) if module == "percentage": value = min(max(value, Decimal("0")), Decimal("100")) if value <= 0: value = DEFAULT_HR_RECOVERY_PERCENT elif module == "fixed" and value <= 0: raise ValueError("Recovery amount per payroll must be greater than zero.") advance.recovery_module = module advance.recovery_value = value advance.save(update_fields=["recovery_module", "recovery_value", "last_updated_on"]) return advance def apply_receipt_settlement(*, disbursement: PettyCashDisbursement, amount) -> None: try: advance = EmployeeAdvance.objects.get(petty_cash_disbursement=disbursement) except EmployeeAdvance.DoesNotExist: return settled = _decimal(amount) if settled <= 0: return advance.amount_settled_via_receipt = _decimal(advance.amount_settled_via_receipt) + settled advance.balance_outstanding = max( Decimal("0"), _decimal(advance.balance_outstanding) - settled, ) if advance.balance_outstanding <= 0: advance.balance_outstanding = Decimal("0") advance.status = "settled" elif advance.amount_recovered_via_payroll > 0 or advance.amount_settled_via_receipt > 0: advance.status = "partial" advance.save(update_fields=[ "amount_settled_via_receipt", "balance_outstanding", "status", "last_updated_on", ]) def get_staff_advance_payload(staff_id) -> dict: _ensure_staff_disbursement_advances(staff_id) advances = EmployeeAdvance.objects.filter( staff_profile_id=staff_id, balance_outstanding__gt=0, status__in=["open", "partial"], ).select_related( "fund", "petty_cash_disbursement", "staff_profile", ).order_by("created_on") rows = [] total = Decimal("0") installment_total = Decimal("0") for adv in advances: balance = _decimal(adv.balance_outstanding) total += balance row = _advance_row(adv) rows.append(row) installment_total += _decimal(row["payroll_installment"]) company_profile = None staff = StaffProfile.objects.filter(id=staff_id).select_related("company_branch__company_profile").first() if staff and staff.company_branch: company_profile = staff.company_branch.company_profile deduction_id = "" if company_profile and total > 0: deduction_id = str(get_or_create_advance_deduction(company_profile).id) return { "employee_advance_balance": str(total), "employee_advance_recovery_suggested": str(installment_total), "employee_advance_deduction_id": deduction_id, "employee_advances_list": rows, } def list_company_advances(*, company_profile, branch_id=None, include_closed=False) -> list[dict]: qs = EmployeeAdvance.objects.filter( company_branch__company_profile=company_profile, ).select_related( "staff_profile", "fund", "petty_cash_disbursement", "recorded_by", ).order_by("-created_on") if branch_id: qs = qs.filter(company_branch_id=int(branch_id)) if not include_closed: qs = qs.filter(balance_outstanding__gt=0, status__in=["open", "partial"]) return [_advance_row(adv) for adv in qs[:500]] def _advance_recovery_amount(deduction_instance_list, company_profile=None) -> Decimal: total = Decimal("0") advance_deduction_id = "" if company_profile: advance_deduction_id = str(get_or_create_advance_deduction(company_profile).id) for item in deduction_instance_list or []: title = str(item.get("deduction_title") or "").strip() ded_id = str(item.get("deduction_id") or "") if title == ADVANCE_DEDUCTION_TITLE or (advance_deduction_id and ded_id == advance_deduction_id): total += _decimal(item.get("deduction_value")) return total def schedule_advance_recoveries_for_payroll_instance( *, payroll_sheet: PayrollSheet, staff: StaffProfile, deduction_instance_list, company_profile, ) -> None: recovery_total = _advance_recovery_amount(deduction_instance_list, company_profile) if recovery_total <= 0: return advances = EmployeeAdvance.objects.filter( staff_profile=staff, balance_outstanding__gt=0, status__in=["open", "partial"], ).order_by("created_on") remaining = recovery_total for advance in advances: if remaining <= 0: break expected = compute_installment_for_advance(advance) slice_amount = min(_decimal(advance.balance_outstanding), expected, remaining) if slice_amount <= 0: continue PayrollAdvanceRecovery.objects.create( employee_advance=advance, payroll_sheet=payroll_sheet, staff_profile=staff, amount=slice_amount, status="scheduled", ) remaining -= slice_amount def reset_scheduled_recoveries_for_payroll_sheet(payroll_sheet: PayrollSheet) -> None: PayrollAdvanceRecovery.objects.filter( payroll_sheet=payroll_sheet, status="scheduled", ).update(status="cancelled") @transaction.atomic def recover_advances_for_payroll_payment(*, payroll_sheet: PayrollSheet, staff, credit_account) -> dict: """When payroll is paid, post GL to clear employee advances scheduled on this sheet.""" recoveries = PayrollAdvanceRecovery.objects.filter( payroll_sheet=payroll_sheet, status="scheduled", ).select_related( "employee_advance", "employee_advance__employee_advances_account", "employee_advance__fund", ) if not recoveries.exists(): return {"recovered": 0, "amount": "0.00"} total_recovered = Decimal("0") count = 0 branch = payroll_sheet.company_branch for recovery in recoveries: advance = recovery.employee_advance amount = _decimal(recovery.amount) if amount <= 0 or _decimal(advance.balance_outstanding) <= 0: recovery.status = "cancelled" recovery.save(update_fields=["status"]) continue amount = min(amount, _decimal(advance.balance_outstanding)) advances_account = advance.employee_advances_account if not advances_account and advance.fund: advances_account = advance.fund.employee_advances_account if not advances_account: advances_account = _resolve_advances_account(branch=branch) if not advances_account: continue mapping = resolve_posting_rule(branch, "payroll_posting", "payroll_accrual") debit_account = mapping.debit_account if mapping and mapping.debit_account_id else None if not debit_account: from .posting_engine import _account_by_number try: debit_account = _account_by_number(branch, "5100") except PostingError: debit_account = None if not debit_account: continue post_balanced_lines( branch=branch, staff=staff, lines=[ {"account": debit_account, "debit": amount, "credit": Decimal("0")}, {"account": advances_account, "debit": Decimal("0"), "credit": amount}, ], transaction_date=date.today(), description=f"Payroll advance recovery {payroll_sheet.payroll_sheet_number}", rel_type="payroll_advance_recovery", rel_id=str(recovery.id), reference=advance.reference_number, ) advance.amount_recovered_via_payroll = _decimal(advance.amount_recovered_via_payroll) + amount advance.balance_outstanding = _decimal(advance.balance_outstanding) - amount if advance.balance_outstanding <= 0: advance.balance_outstanding = Decimal("0") advance.status = "recovered" else: advance.status = "partial" advance.last_payroll_sheet = payroll_sheet advance.save(update_fields=[ "amount_recovered_via_payroll", "balance_outstanding", "status", "last_payroll_sheet", "last_updated_on", ]) recovery.status = "recovered" recovery.recovered_on = timezone.now() recovery.save(update_fields=["status", "recovered_on"]) total_recovered += amount count += 1 return {"recovered": count, "amount": str(total_recovered)}