from collections import defaultdict from datetime import date from decimal import Decimal from django.db.models import Max, Min, Q from django.utils import timezone from rest_framework.generics import GenericAPIView from rest_framework.permissions import IsAuthenticated from rest_framework.response import Response from account.permissions import get_user_shop_filter from shop_management.models.data.account_transfer import AccountTransfer from shop_management.models.data.account_transaction import AccountTransaction from shop_management.models.data.shop import Shop from shop_management.models.data.shop_account import Account class CashFlowReportView(GenericAPIView): permission_classes = [IsAuthenticated] @staticmethod def _parse_date_param(value): try: return date.fromisoformat(value) if value else None except (TypeError, ValueError): return None def _resolve_dates(self, request, tx_queryset): today = timezone.localdate() start = self._parse_date_param(request.query_params.get("start_date")) end = self._parse_date_param(request.query_params.get("end_date")) if not start or not end: bounds = tx_queryset.aggregate( first_date=Min("transaction_date"), last_date=Max("transaction_date"), ) start = start or bounds["first_date"] or today end = end or bounds["last_date"] or today if end < start: start, end = end, start return start, end @staticmethod def _to_decimal(value): if value is None: return Decimal("0") return Decimal(str(value)) @staticmethod def _is_clearing_mirror(description): return "clearing mirror" in str(description or "").lower() @staticmethod def _is_system_clearing_account_name(name): text = str(name or "").strip().lower() return ( text.startswith("purchase ") and text.endswith(" clearing") ) or ( text.startswith("sale ") and text.endswith(" clearing") ) @staticmethod def _categorize_transaction(description, transaction_type): text = str(description or "") lowered = text.lower() if lowered.startswith("transfer in"): return "transfer_in" if lowered.startswith("transfer out"): return "transfer_out" if lowered.startswith("expense #"): return "expenses" if lowered.startswith("payroll #"): return "payroll" if lowered.startswith("advance #"): return "advance" if lowered.startswith("purchase payment #"): return "purchase_payments" if lowered.startswith("customer balance payment #"): return "invoice_payments" if lowered.startswith("customer loan #") or lowered.startswith("customer debtor #"): return "other_outflow" return "other_inflow" if transaction_type == "credit" else "other_outflow" @staticmethod def _empty_bucket(): return { "opening_balance": Decimal("0"), "credit_total": Decimal("0"), "debit_total": Decimal("0"), "inflow": Decimal("0"), "outflow": Decimal("0"), "net": Decimal("0"), "transfer_in": Decimal("0"), "transfer_out": Decimal("0"), "expenses": Decimal("0"), "payroll": Decimal("0"), "advance": Decimal("0"), "invoice_payments": Decimal("0"), "purchase_payments": Decimal("0"), "other_inflow": Decimal("0"), "other_outflow": Decimal("0"), "closing_balance": Decimal("0"), } @staticmethod def _transfer_description(transfer, direction): prefix = "Transfer out" if direction == "out" else "Transfer in" target = ( f"to {transfer.to_account.name}" if direction == "out" else f"from {transfer.from_account.name}" ) reference = f" | Ref: {transfer.reference}" if transfer.reference else "" note = f" | Note: {transfer.note}" if transfer.note else "" return f"{prefix} {target}{reference}{note}" @classmethod def _build_valid_transfer_signatures(cls, account_ids, end_date): transfers = AccountTransfer.objects.select_related( "from_account", "to_account", ).filter( Q(from_account_id__in=account_ids) | Q(to_account_id__in=account_ids), transfer_date__lte=end_date, ) valid_transfer_out = set() valid_transfer_in = set() for transfer in transfers: valid_transfer_out.add( ( transfer.from_account_id, transfer.amount, transfer.transfer_date, cls._transfer_description(transfer, "out"), ) ) valid_transfer_in.add( ( transfer.to_account_id, transfer.amount, transfer.transfer_date, cls._transfer_description(transfer, "in"), ) ) return valid_transfer_out, valid_transfer_in @staticmethod def _is_valid_transfer_transaction(txn, valid_transfer_out, valid_transfer_in): signature = ( txn.account_id, txn.amount, txn.transaction_date, txn.description or "", ) if txn.transaction_type == "debit": return signature in valid_transfer_out return signature in valid_transfer_in @staticmethod def _serialize_bucket(currency, bucket): return { "currency": currency, "opening_balance": str(bucket["opening_balance"].quantize(Decimal("0.01"))), "credit_total": str(bucket["credit_total"].quantize(Decimal("0.01"))), "debit_total": str(bucket["debit_total"].quantize(Decimal("0.01"))), "inflow": str(bucket["inflow"].quantize(Decimal("0.01"))), "outflow": str(bucket["outflow"].quantize(Decimal("0.01"))), "net": str(bucket["net"].quantize(Decimal("0.01"))), "transfer_in": str(bucket["transfer_in"].quantize(Decimal("0.01"))), "transfer_out": str(bucket["transfer_out"].quantize(Decimal("0.01"))), "expenses": str(bucket["expenses"].quantize(Decimal("0.01"))), "payroll": str(bucket["payroll"].quantize(Decimal("0.01"))), "advance": str(bucket["advance"].quantize(Decimal("0.01"))), "invoice_payments": str(bucket["invoice_payments"].quantize(Decimal("0.01"))), "purchase_payments": str(bucket["purchase_payments"].quantize(Decimal("0.01"))), "other_inflow": str(bucket["other_inflow"].quantize(Decimal("0.01"))), "other_outflow": str(bucket["other_outflow"].quantize(Decimal("0.01"))), "closing_balance": str(bucket["closing_balance"].quantize(Decimal("0.01"))), } def get(self, request, *args, **kwargs): user = request.user selected_shop = request.query_params.get("shop") selected_account = request.query_params.get("account") account_filtered_view = bool(selected_account) search = (request.query_params.get("search") or "").strip() allowed_shops = Shop.objects.filter(get_user_shop_filter(user)) accounts = Account.objects.select_related("shop", "currency").filter( shop__in=allowed_shops ) if selected_shop: accounts = accounts.filter(shop_id=selected_shop) if selected_account: accounts = accounts.filter(id=selected_account) accounts = accounts.exclude( Q(name__istartswith="Purchase ", name__iendswith=" Clearing") | Q(name__istartswith="Sale ", name__iendswith=" Clearing") ).order_by("shop__name", "name", "id") account_ids = list(accounts.values_list("id", flat=True)) if not account_ids: fallback_date = timezone.localdate() start_date = self._parse_date_param(request.query_params.get("start_date")) or fallback_date end_date = self._parse_date_param(request.query_params.get("end_date")) or fallback_date if end_date < start_date: start_date, end_date = end_date, start_date return Response( { "filters": { "start_date": start_date.isoformat(), "end_date": end_date.isoformat(), "shop": selected_shop or "", "account": selected_account or "", "search": search, }, "summary": [], "accounts": [], "daily": [], "records": [], } ) tx_queryset = AccountTransaction.objects.select_related( "account", "account__shop", "account_currency", "transaction_currency", ).filter(account_id__in=account_ids) start_date, end_date = self._resolve_dates(request, tx_queryset) search_filter = Q() if search: search_filter = ( Q(description__icontains=search) | Q(account__name__icontains=search) | Q(account__shop__name__icontains=search) | Q(account_currency__title__icontains=search) | Q(transaction_currency__title__icontains=search) ) opening_transactions = tx_queryset.filter(transaction_date__lt=start_date) period_transactions = tx_queryset.filter( transaction_date__gte=start_date, transaction_date__lte=end_date, ) if search: period_transactions = period_transactions.filter(search_filter) valid_transfer_out, valid_transfer_in = self._build_valid_transfer_signatures( account_ids, end_date ) opening_by_account = defaultdict(Decimal) for account in accounts: opening_by_account[account.id] = self._to_decimal(account.opening_balance) for txn in opening_transactions: if self._is_clearing_mirror(txn.description): continue if ( self._categorize_transaction(txn.description, txn.transaction_type) in ("transfer_in", "transfer_out") and not self._is_valid_transfer_transaction( txn, valid_transfer_out, valid_transfer_in ) ): continue amount = self._to_decimal(txn.converted_amount) if txn.transaction_type == "credit": opening_by_account[txn.account_id] += amount else: opening_by_account[txn.account_id] -= amount company_summary = defaultdict(self._empty_bucket) account_summaries = {} daily_summary = defaultdict(lambda: {"inflow": Decimal("0"), "outflow": Decimal("0"), "transfer_in": Decimal("0"), "transfer_out": Decimal("0")}) records = [] for account in accounts: currency = account.currency.title if account.currency else "Unknown" opening_balance = opening_by_account[account.id] company_summary[currency]["opening_balance"] += opening_balance account_summaries[account.id] = { "account_id": account.id, "account_name": account.name, "shop_id": account.shop_id, "shop_name": account.shop.name if account.shop else "--", "currency": currency, "opening_balance": opening_balance, "credit_total": Decimal("0"), "debit_total": Decimal("0"), "inflow": Decimal("0"), "outflow": Decimal("0"), "net": Decimal("0"), "transfer_in": Decimal("0"), "transfer_out": Decimal("0"), "expenses": Decimal("0"), "payroll": Decimal("0"), "advance": Decimal("0"), "invoice_payments": Decimal("0"), "purchase_payments": Decimal("0"), "other_inflow": Decimal("0"), "other_outflow": Decimal("0"), "closing_balance": opening_balance, } for txn in period_transactions.order_by("-transaction_date", "-id"): if self._is_clearing_mirror(txn.description): continue category = self._categorize_transaction(txn.description, txn.transaction_type) if category in ("transfer_in", "transfer_out") and not self._is_valid_transfer_transaction( txn, valid_transfer_out, valid_transfer_in ): continue currency = txn.account_currency.title if txn.account_currency else "Unknown" amount = self._to_decimal(txn.converted_amount) direction = "inflow" if txn.transaction_type == "credit" else "outflow" is_internal_transfer = category in ("transfer_in", "transfer_out") company_bucket = company_summary[currency] account_bucket = account_summaries[txn.account_id] day_key = (txn.transaction_date.isoformat(), currency) if txn.transaction_type == "credit": account_bucket["credit_total"] += amount company_bucket["credit_total"] += amount if is_internal_transfer: account_bucket["transfer_in"] += amount company_bucket["transfer_in"] += amount daily_summary[day_key]["transfer_in"] += amount else: account_bucket["inflow"] += amount company_bucket["inflow"] += amount daily_summary[day_key]["inflow"] += amount else: account_bucket["debit_total"] += amount company_bucket["debit_total"] += amount if is_internal_transfer: account_bucket["transfer_out"] += amount company_bucket["transfer_out"] += amount daily_summary[day_key]["transfer_out"] += amount else: account_bucket["outflow"] += amount company_bucket["outflow"] += amount daily_summary[day_key]["outflow"] += amount if category in company_bucket and not is_internal_transfer: company_bucket[category] += amount if category in account_bucket and not is_internal_transfer: account_bucket[category] += amount records.append( { "id": txn.id, "transaction_date": txn.transaction_date.isoformat(), "account_id": txn.account_id, "account_name": txn.account.name if txn.account else "--", "shop_id": txn.account.shop_id if txn.account and txn.account.shop_id else None, "shop_name": txn.account.shop.name if txn.account and txn.account.shop else "--", "account_currency": currency, "transaction_currency": txn.transaction_currency.title if txn.transaction_currency else "Unknown", "transaction_type": txn.transaction_type, "direction": direction, "category": category, "amount": str(self._to_decimal(txn.amount).quantize(Decimal("0.01"))), "converted_amount": str(amount.quantize(Decimal("0.01"))), "exchange_rate": str(self._to_decimal(txn.exchange_rate).quantize(Decimal("0.000001"))), "description": txn.description or "", } ) summary_rows = [] for currency, bucket in sorted(company_summary.items()): bucket["net"] = ( bucket["inflow"] - bucket["outflow"] + bucket["transfer_in"] - bucket["transfer_out"] ) bucket["closing_balance"] = bucket["opening_balance"] + bucket["net"] summary_rows.append(self._serialize_bucket(currency, bucket)) account_rows = [] for bucket in account_summaries.values(): bucket["net"] = ( bucket["inflow"] - bucket["outflow"] + bucket["transfer_in"] - bucket["transfer_out"] ) bucket["closing_balance"] = bucket["opening_balance"] + bucket["net"] account_rows.append( { **{k: v for k, v in bucket.items() if not isinstance(v, Decimal)}, "opening_balance": str(bucket["opening_balance"].quantize(Decimal("0.01"))), "credit_total": str(bucket["credit_total"].quantize(Decimal("0.01"))), "debit_total": str(bucket["debit_total"].quantize(Decimal("0.01"))), "inflow": str(bucket["inflow"].quantize(Decimal("0.01"))), "outflow": str(bucket["outflow"].quantize(Decimal("0.01"))), "net": str(bucket["net"].quantize(Decimal("0.01"))), "transfer_in": str(bucket["transfer_in"].quantize(Decimal("0.01"))), "transfer_out": str(bucket["transfer_out"].quantize(Decimal("0.01"))), "expenses": str(bucket["expenses"].quantize(Decimal("0.01"))), "payroll": str(bucket["payroll"].quantize(Decimal("0.01"))), "advance": str(bucket["advance"].quantize(Decimal("0.01"))), "invoice_payments": str(bucket["invoice_payments"].quantize(Decimal("0.01"))), "purchase_payments": str(bucket["purchase_payments"].quantize(Decimal("0.01"))), "other_inflow": str(bucket["other_inflow"].quantize(Decimal("0.01"))), "other_outflow": str(bucket["other_outflow"].quantize(Decimal("0.01"))), "closing_balance": str(bucket["closing_balance"].quantize(Decimal("0.01"))), } ) daily_rows = [] for (txn_date, currency), values in sorted(daily_summary.items(), reverse=True): net = ( values["inflow"] - values["outflow"] + values["transfer_in"] - values["transfer_out"] ) daily_rows.append( { "date": txn_date, "currency": currency, "inflow": str(values["inflow"].quantize(Decimal("0.01"))), "outflow": str(values["outflow"].quantize(Decimal("0.01"))), "transfer_in": str(values["transfer_in"].quantize(Decimal("0.01"))), "transfer_out": str(values["transfer_out"].quantize(Decimal("0.01"))), "net": str(net.quantize(Decimal("0.01"))), } ) return Response( { "filters": { "start_date": start_date.isoformat(), "end_date": end_date.isoformat(), "shop": selected_shop or "", "account": selected_account or "", "search": search, }, "summary": summary_rows, "accounts": account_rows, "daily": daily_rows, "records": records, } )