from django.shortcuts import render import json from django.conf import settings from inventory.models import * from expenses.models import * from store.models import * from company.models import * from masters.models import * from supplier.models import * from material.models import * from scheme.models import * from django.db import IntegrityError, transaction import hashlib import os from datetime import datetime, time from django.utils import timezone from django.db.models import Max, F, Q, Sum import base64 from io import BytesIO from django.template.loader import get_template from django.http import HttpResponse, HttpResponseRedirect, JsonResponse, FileResponse from django.shortcuts import get_object_or_404 from django.templatetags.static import static from xhtml2pdf import pisa from num2words import num2words from django.contrib.staticfiles import finders from common.utils import * from django.db import connection import openpyxl from openpyxl.styles import Font from django.core.files.storage import default_storage from claim_collect_upgrade.models import * from voucher.models import * from decimal import Decimal from sales.models import * # ********************************************************************************************************************************** def format_hr_m(date): return date.strftime('%H:%M') if date else None def finance_ledger(request): if "user_id" in request.session: user_type = request.session.get("user_type") branch_id = request.session.get("branch_id") if user_type == "stores": # Get all finance companies for dropdown finances = selectList(finance_table, order_by="name") return render( request, "finance_ledger.html", { "finances": finances, }, ) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") # ********************************************************************************************************************************** def finance_ledger_view(request): branch_id = request.session.get('branch_id') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) role_id = request.session.get('role_id') has_access, _ = check_user_access(role_id, 'finance_ledger', "read") if not has_access: return JsonResponse({'message': 'permission'}) from_date = request.POST.get('from_date') to_date = request.POST.get('to_date') finance_id = request.POST.get('finance_id') # Finance ID is required if not finance_id or finance_id == "": return JsonResponse({ "data": [], "closing_balance": "0.00" }) finance_id = int(finance_id) all_rows = [] running_balance = Decimal("0.00") # Get opening balance res = get_opening_finance_balance(branch_id, financial_year, from_date, finance_id) running_balance = res["opening_balance"] receivable = res.get("receivable", Decimal("0.00")) payable = res.get("payable", Decimal("0.00")) # ----------------------- # Initial Row: Opening Balance # ----------------------- if running_balance != 0 or receivable != 0 or payable != 0: all_rows.append({ "date": from_date if from_date else financial_year['start_date'], "time": "00:00", "receipt": "", "type": "Opening Balance", "particulars": "Opening Balance (Receivable - Payable)", "credit": payable, # Liability/Payable count as Credit "debit": receivable, # Asset/Receivable count as Debit "balance": running_balance, "employee": "" }) # Collect all transactions res = get_finance_transactions(branch_id, financial_year, from_date, to_date, finance_id) all_rows.extend(res["rows"]) # Sort by datetime for row in all_rows: if "sort_datetime" not in row: # Handle potential None in date d_str = row["date"] if row["date"] else "1970-01-01" t_str = row["time"] if row["time"] else "00:00" row["sort_datetime"] = datetime.strptime(f"{d_str} {t_str}", "%Y-%m-%d %H:%M") all_rows.sort(key=lambda x: x["sort_datetime"]) # Format ledger with running balance formatted = [] # Recalculate running balance based on sorted rows # Logic: Debit (Sales) Increases Receivable (+), Credit (Collection) Decreases Receivable (-) current_balance = running_balance for idx, row in enumerate(all_rows, start=1): if row["type"] != "Opening Balance": debit = Decimal(row["debit"]) credit = Decimal(row["credit"]) current_balance += (debit - credit) row["balance"] = current_balance row["id"] = idx # Format date for display row["date"] = row["sort_datetime"].strftime("%Y-%m-%d %H:%M") row["employee"] = getItemNameById(employee_table, row["employee"]) if row["employee"] else "" # Format amounts row["credit"] = f"{Decimal(row['credit']):,.2f}" row["debit"] = f"{Decimal(row['debit']):,.2f}" # Balance formatting with DR/CR bal_val = Decimal(row["balance"]) dr_cr = "DR" if bal_val >= 0 else "CR" row["balance"] = f"{abs(bal_val):,.2f} {dr_cr}" # cleanup row.pop("sort_datetime", None) formatted.append(row) # Closing Balance dr_cr_total = "DR" if current_balance >= 0 else "CR" closing_balance = f"{abs(current_balance):.2f} {dr_cr_total}" return JsonResponse({ "data": formatted, "closing_balance": closing_balance }) # ********************************************************************************************************************************** def get_opening_finance_balance(branch_id, financial_year, from_date, finance_id): """Calculate opening finance balance""" # Simply get from master for now, or 0 # In a full implementation, you'd calculate backward from current balance or forward from year start fin = select_row(finance_table, {"id": finance_id, "f_type": "finance"}) opening_balance = Decimal("0.00") receivable = Decimal("0.00") payable = Decimal("0.00") if fin: # Net Receivable from Master receivable = Decimal(fin.receivable or 0) payable = Decimal(fin.payable or 0) opening_balance = receivable - payable return { "opening_balance": opening_balance, "receivable": receivable, "payable": payable } # ********************************************************************************************************************************** def get_finance_transactions(branch_id, financial_year, from_date, to_date, finance_id): """Collect all finance transactions""" rows = [] # Date filter date_filter = {} if from_date and to_date: date_filter["date__range"] = [from_date, to_date] # -------------------------------------------------- # 1️⃣ SALES (Debit - Receivable Increases) # -------------------------------------------------- # Sales where payment_type implies finance or specialized finance transaction logic? # Usually Sales with 'finance' payment mode OR specific finance_id link # Option A: Check all sales linked to this finance_id sales = transaction_table.objects.filter( branch_id=branch_id, current_fy=financial_year, status=1, is_active=1, finance_id=finance_id, **date_filter ) for sale in sales: sales_order = select_row(sales_order_table, {'id': sale.tm_sales_id}) inv_no = sales_order.inv_no if sales_order else "" rows.append({ "date": sale.date, "time": format_hr_m(sale.time or time(0, 0)), "receipt": inv_no, "type": "Sales", "particulars": f"Finance Sales - {inv_no}", "credit": Decimal("0.00"), "debit": Decimal(sale.amount or 0), # Amount Financed (Debit) "balance": Decimal("0.00"), "employee": sale.created_by or "" }) # -------------------------------------------------- # 2️⃣ SALES RETURN (Credit - Receivable Decreases) # -------------------------------------------------- returns = return_transaction_table.objects.filter( branch_id=branch_id, current_fy=financial_year, status=1, is_active=1, finance_id=finance_id, **date_filter ) for ret in returns: try: return_order = select_row(sales_return_table, {'id': ret.tm_return_id}) # Note: User previously corrected return_table -> sales_return_table in another file # If sales_return_table is the correct model, use it. # Safest is to try both or rely on project convention. # Assuming 'sales_return_table' based on previous user input context, # but standard name might be return_table. # Let's use the standard select_row logic. # Using 'sales_return_table' as per previous user correction context if not return_order: return_order = select_row(sales_return_table, {'id': ret.tm_return_id}) ret_no = return_order.sr_no if return_order and hasattr(return_order, 'sr_no') else (return_order.return_no if return_order else "") except: ret_no = "" rows.append({ "date": ret.date, "time": format_hr_m(ret.time or time(0, 0)), "receipt": ret_no, "type": "Sales Return", "particulars": f"Finance Return - {ret_no}", "credit": Decimal(ret.amount or 0), # Amount Returned (Credit) "debit": Decimal("0.00"), "balance": Decimal("0.00"), "employee": ret.created_by or "" }) # -------------------------------------------------- # 3️⃣ COLLECTIONS (Credit - Money In) # -------------------------------------------------- collections = tm_collection_table.objects.filter( branch_id=branch_id, current_fy=financial_year, status=1, is_active=1, finance_id=finance_id, **date_filter ) for coll in collections: rows.append({ "date": coll.date, "time": format_hr_m(coll.time or time(0, 0)), "receipt": coll.collection_no or "", "type": "Collection", "particulars": f"Collection - {coll.collection_no}", "credit": Decimal(coll.total_amount or 0), # Money Received (Credit) "debit": Decimal("0.00"), "balance": Decimal("0.00"), "employee": coll.created_by or "" }) # -------------------------------------------------- # 4️⃣ VOUCHERS # -------------------------------------------------- vouchers = voucher_table.objects.filter( branch_id=branch_id, current_fy=financial_year, status=1, is_active=1, finance_id=finance_id, **date_filter ) for vouch in vouchers: is_receipt = vouch.voucher_type in ["receipt", "cash_receipt", "bank_receipt"] is_payment = vouch.voucher_type in ["payment", "cash_payment", "bank_payment"] amount = Decimal(0) debit = Decimal(0) credit = Decimal(0) v_type = "" if is_receipt: # We received money from Finance Co -> Credit Receivable amount = Decimal(vouch.receivable_amount or 0) credit = amount v_type = "Receipt" elif is_payment: # We paid money to Finance Co -> Debit Receivable (or Reduce Payable if it was negative) amount = Decimal(vouch.payable_amount or 0) debit = amount v_type = "Payment" if amount > 0: rows.append({ "date": vouch.date, "time": format_hr_m(vouch.time or time(0, 0)), "receipt": vouch.voucher_no or "", "type": f"Voucher {v_type}", "particulars": f"Voucher - {vouch.voucher_type}", "credit": credit, "debit": debit, "balance": Decimal("0.00"), "employee": vouch.created_by or "" }) return { "rows": rows, }