""" Rebuild available stock qty from the movement ledger (physical qty, not currency). Correct qty for one product at a branch: real purchases - purchase returns + accepted transfers IN - real customer sales + sale returns - accepted transfers OUT - pending reserved OUT Transfer-generated purchases are excluded, including Payment-OFF rows that have no supplier and were created on the transfer date. Example: python manage.py heal_stock_quantities --revert python manage.py heal_stock_quantities python manage.py heal_stock_quantities --apply python manage.py heal_stock_quantities --add-opening python manage.py heal_stock_quantities --add-opening --apply """ from collections import defaultdict from datetime import date, timedelta from decimal import Decimal from django.core.management.base import BaseCommand from django.db import transaction from django.db.models import Sum from shop_management.models.data.product import Product from shop_management.models.data.purchase import Purchase, PurchaseItem from shop_management.models.data.purchase_return import PurchaseReturnItem from shop_management.models.data.sale import SaleItem from shop_management.models.data.sale_return import SaleReturnItem from shop_management.models.data.shop import Shop from shop_management.models.data.stock import Stock from shop_management.models.data.stock_transfer import StockTransfer from shop_management.models.data.supplier import Supplier from shop_management.models.data.utils import PaymentStatus from shop_management.management.commands._heal_stock_revert_data import ( REVERT_20260822_ALL_PRODUCTS, ) from shop_management.views.data.stock import normalize_currency_code DEFAULT_SHOPS = ("Kabul Main Branch", "Takhar Branch") OPENING_SUPPLIER_NAME = "Opening Stock" # Undo the 2026-08-22 --apply that double-counted transfer purchases. # (shop, product, currency, qty_before_apply, qty_after_apply) REVERT_20260822 = [ ("Kabul Main Branch", "80.450 PIN", "AFG", 52, 60), ("Kabul Main Branch", "80.550 PIN", "AFG", 32, 44), ("Kabul Main Branch", "BROCKEN LINKS", "AFG", 192, 212), ("Kabul Main Branch", "DH UNDERWIL FULL SPRING", "AFG", 28, 30), ("Kabul Main Branch", "DH UNDERWIL SHAFT", "AFG", 24, 26), ("Kabul Main Branch", "DX UNDERWIL FULL SPRING", "AFG", 18, 20), ("Kabul Main Branch", "DX UNDERWIL SHAFT", "AFG", 23, 25), ("Kabul Main Branch", "GREAS PUMP GLASS GARARAI", "AFG", 46, 56), ("Kabul Main Branch", "MASTER PIN", "AFG", 461, 501), ("Kabul Main Branch", "CHAIN PATY 16.5 KG", "USD", 146, 150), ("Kabul Main Branch", "DH TOP ROLLER", "USD", 19, 36), ("Kabul Main Branch", "DH UNDERWIL", "USD", 4, 7), ("Kabul Main Branch", "DX HYDROLIC PUMP", "USD", 14, 12), ("Kabul Main Branch", "DX TOP ROLLER", "USD", 54, 62), ("Kabul Main Branch", "FULL LINKS", "USD", 10, 6), ("Kabul Main Branch", "VCR BUCKET TEETH 25 NO", "USD", 53, 110), ("Takhar Branch", "7000 ENGINE OIL 1-LITER", "AFG", 24, 48), ("Takhar Branch", "7000 ENGINE OIL 25-LITER", "AFG", 32, 67), ("Takhar Branch", "7000 ENGINE OIL 5-LITER", "AFG", 19, 39), ("Takhar Branch", "80.450 PIN", "AFG", 11, 22), ("Takhar Branch", "80.550 PIN", "AFG", 18, 38), ("Takhar Branch", "BARACKET", "AFG", 7, 14), ("Takhar Branch", "BROCKEN LINKS", "AFG", 56, 26), ("Takhar Branch", "BUCKET LARGE KINCHI", "AFG", 2, 1), ("Takhar Branch", "BUCKET SMALL KINCHI", "AFG", 2, 4), ("Takhar Branch", "BUTTOM ROLER 33KG", "AFG", 10, 20), ("Takhar Branch", "BUTTOM ROLER 35KG", "AFG", 10, 20), ("Takhar Branch", "BUTTOM ROLLER NUT & BOLT", "AFG", 50, 100), ("Takhar Branch", "DH AIR FILTER", "AFG", 61, 177), ("Takhar Branch", "DH SEAT RUBBER", "AFG", 5, 10), ("Takhar Branch", "DH UNDERWIL FULL SPRING", "AFG", 15, 40), ("Takhar Branch", "DH UNDERWIL SHAFT", "AFG", 12, 34), ("Takhar Branch", "DX HYDROLIC WAPASI FILTER", "AFG", 50, 156), ("Takhar Branch", "DX SEAT RUBBER", "AFG", 5, 10), ("Takhar Branch", "DX UNDERWIL FULL SPRING", "AFG", 13, 36), ("Takhar Branch", "DX UNDERWIL SHAFT", "AFG", 7, 27), ("Takhar Branch", "GREAS PUMP GLASS GARARAI", "AFG", 30, 61), ("Takhar Branch", "L KEY LARGE BOULTS", "AFG", 50, 120), ("Takhar Branch", "LIMA 80-5MM", "AFG", 80, 160), ("Takhar Branch", "MASTER PIN", "AFG", 98, 166), ("Takhar Branch", "PILAT FILTER", "AFG", 54, 158), ("Takhar Branch", "SECTION FILTER", "AFG", 56, 163), ("Takhar Branch", "SWING MOTOR KIT", "AFG", 10, 20), ("Takhar Branch", "CHAIN PATY 16.5 KG", "USD", 50, 100), ("Takhar Branch", "DEVELON ENGINE FULL KIT", "USD", 8, 16), ("Takhar Branch", "DH ARM JIK", "USD", 2, 4), ("Takhar Branch", "DH BOOM JIK", "USD", 2, 4), ("Takhar Branch", "DH HYDROLIC PUMP", "USD", 10, 18), ("Takhar Branch", "DH TOP ROLLER", "USD", 13, 16), ("Takhar Branch", "DH UNDERWIL", "USD", 8, 12), ("Takhar Branch", "DX HYDROLIC PUMP", "USD", 10, 20), ("Takhar Branch", "DX TOP ROLLER", "USD", 29, 74), ("Takhar Branch", "DX UNDERWIL", "USD", 36, 41), ("Takhar Branch", "FULL LINKS", "USD", 5, 11), ("Takhar Branch", "SWING GIRA", "USD", 4, 10), ("Takhar Branch", "VCR BUCKET TEETH 25 NO", "USD", 40, 105), ] def _norm_name(value): return " ".join(str(value or "").strip().upper().split()) def _currency_code(currency): if not currency: return "UNKNOWN" return normalize_currency_code(getattr(currency, "title", None) or currency) or "UNKNOWN" def _is_transfer_supplier(name): return str(name or "").strip().lower().startswith("transfer from ") def _is_transfer_sale(sale): notes = str(getattr(sale, "notes", "") or "") customer_name = str(getattr(getattr(sale, "customer", None), "name", "") or "") return ( notes.startswith("Stock transfer sale for ") or customer_name.lower().startswith("transfer to ") ) class _Bucket(dict): def __init__(self): super().__init__() self.update( { "name": "", "purchase": 0, "purchase_return": 0, "sale": 0, "sale_return": 0, "transfer_in": 0, "transfer_out": 0, "pending_out": 0, "stocks": [], } ) @property def current(self): return sum(int(s.quantity or 0) for s in self["stocks"]) @property def expected(self): return ( int(self["purchase"]) - int(self["purchase_return"]) + int(self["transfer_in"]) + int(self["sale_return"]) - int(self["sale"]) - int(self["transfer_out"]) - int(self["pending_out"]) ) class Command(BaseCommand): help = ( "Rebuild stock qty from the movement ledger for every product. " "Dry-run unless --apply. Use --revert to undo the 22 Aug double-count apply." ) def add_arguments(self, parser): parser.add_argument( "--shop-name", action="append", dest="shop_names", help="Branch name (repeatable). Default: Kabul Main Branch and Takhar Branch.", ) parser.add_argument( "--all-shops", action="store_true", help="Include every branch. Do not use this for Badakhshan opening stock.", ) parser.add_argument( "--product-name", type=str, help="Exact product name (optional).", ) parser.add_argument( "--transferred-only", action="store_true", help="Only products that moved between Kabul and Takhar.", ) parser.add_argument( "--all-products", action="store_true", help="Audit every product at the branch (default).", ) parser.add_argument( "--show-ok", action="store_true", help="Also print products whose current qty already matches the ledger.", ) parser.add_argument( "--double-only", action="store_true", help="Only print products whose screen qty is exactly 2 times the bills.", ) parser.add_argument( "--simple", action="store_true", help="Print a simple stock vs buy/sale/transfer check. Report only.", ) parser.add_argument( "--apply-double", action="store_true", help=( "Only products where extra qty equals transfer IN (counted twice). " "Dry-run unless --apply." ), ) parser.add_argument( "--apply", action="store_true", help="Write corrected quantities. Without this flag, only print the report.", ) parser.add_argument( "--exact", action="store_true", help="Set qty exactly to the ledger (can lower). Default only RAISES missing qty.", ) parser.add_argument( "--revert", action="store_true", help="Undo the 22 Aug all-products apply (restore Badakhshan and Kabul/Takhar).", ) parser.add_argument( "--force", action="store_true", help="With --revert, restore even if current qty is no longer the applied value.", ) parser.add_argument( "--dry-run", action="store_true", help="Force report-only mode even if --apply / --revert is set.", ) parser.add_argument( "--add-opening", action="store_true", help=( "For sold-more-than-bought products, add an Opening Stock purchase " "so bills match the qty on the shelf. Does not change stock qty. " "Dry-run unless --apply." ), ) def handle(self, *args, **options): dry_run = bool(options.get("dry_run")) if options.get("revert"): self._revert(dry_run=dry_run, force=bool(options.get("force"))) return shop_names = options.get("shop_names") product_name = (options.get("product_name") or "").strip() only_transferred = bool(options.get("transferred_only")) and not options.get( "all_products" ) show_ok = options.get("show_ok") double_only = bool(options.get("double_only")) simple = bool(options.get("simple")) apply_double = bool(options.get("apply_double")) add_opening = bool(options.get("add_opening")) raise_only = not bool(options.get("exact")) apply_changes = bool(options.get("apply")) and not dry_run and not simple if add_opening: apply_changes = bool(options.get("apply")) and not dry_run simple = False apply_double = False self.stdout.write( self.style.NOTICE( "Add-opening: create Opening Stock purchases for sold-more-than-bought. " "Stock qty on the shelf stays the same." ) ) elif apply_double: apply_changes = bool(options.get("apply")) and not dry_run self.stdout.write( self.style.NOTICE( "Apply-double: only products where extra qty = transfer IN. " "Does not lower other leftover." ) ) elif simple: self.stdout.write( self.style.NOTICE( "Simple check: stock qty vs buy + transfer in - sold - transfer out. " "Transfer purchases/sales are counted once. Report only." ) ) elif double_only: self.stdout.write( self.style.NOTICE( "Double-only: print products where screen qty is 2 times the bills. Report only." ) ) elif raise_only: self.stdout.write( self.style.NOTICE( "Raise-only: will add missing qty, never lower opening stock." ) ) shops = [] if shop_names: names = shop_names elif options.get("all_shops"): names = None shops = list(Shop.objects.all().order_by("id")) if not shops: self.stderr.write(self.style.ERROR("No shops found.")) return self.stdout.write( self.style.NOTICE( "Healing every branch: " + ", ".join(s.name for s in shops) ) ) else: names = list(DEFAULT_SHOPS) if names: for name in names: shop = self._resolve_shop(name) if not shop: return shops.append(shop) transferred_names = set() if only_transferred and not product_name: transferred_names = self._kabul_takhar_product_names(shops) self.stdout.write( self.style.NOTICE( f"Kabul↔Takhar transferred products: {len(transferred_names)}" ) ) if not transferred_names: self.stderr.write( self.style.ERROR( "No Kabul↔Takhar transfers found. " "Drop --transferred-only or use --product-name." ) ) return mode = "APPLY" if apply_changes else "DRY-RUN" heading = "opening stock purchases" if add_opening else "stock qty heal" self.stdout.write(self.style.MIGRATE_HEADING(f"{mode}: {heading}")) totals = defaultdict(int) for shop in shops: shop_counts = self._heal_shop( shop, transferred_names=transferred_names, product_name=product_name, only_transferred=only_transferred and not product_name, show_ok=show_ok, apply_changes=apply_changes, raise_only=raise_only, double_only=double_only, simple=simple, apply_double=apply_double, add_opening=add_opening, ) for key, value in shop_counts.items(): totals[key] += value self.stdout.write("") self.stdout.write(self.style.SUCCESS("Summary:")) if add_opening: self.stdout.write(f" sold more than bought: {totals['negative']}") self.stdout.write( f" {'Created' if apply_changes else 'Would create'} opening bills: " f"{totals['updated']}" ) self.stdout.write( f" already had opening bill: {totals['opening_exists']}" ) if apply_changes: return self.stdout.write("") self.stdout.write( "If the ADD_OPENING lines look right, create the bills with:\n" " python manage.py heal_stock_quantities --add-opening --apply" ) return if apply_double: self.stdout.write(f" DOUBLE (extra qty = transfer in): {totals['double']}") self.stdout.write( f" {'Updated' if apply_changes else 'Would update'} groups: {totals['updated']}" ) if apply_changes: return self.stdout.write("") self.stdout.write( "If the DOUBLE lines look right, set stock with:\n" " python manage.py heal_stock_quantities --transferred-only --apply-double --apply" ) return if simple: self.stdout.write(f" OK (stock = buy + transfer - sale): {totals['ok']}") self.stdout.write( f" DOUBLE (extra qty same as transfer in): {totals['double']}" ) self.stdout.write(f" HIGH (stock more than bills, opening leftover): {totals['surplus']}") self.stdout.write(f" LOW (stock less than bills): {totals['mismatch']}") self.stdout.write(f" sold more than bought: {totals['negative']}") return if double_only: self.stdout.write(f" screen qty is 2 times bills: {totals['double']}") return self.stdout.write(f" mismatch groups: {totals['mismatch']}") self.stdout.write(f" already correct: {totals['ok']}") self.stdout.write(f" skipped (negative ledger): {totals['negative']}") self.stdout.write(f" skipped (opening / no invoices): {totals['opening']}") self.stdout.write(f" kept surplus (opening stock): {totals['surplus']}") self.stdout.write( f" {'Updated' if apply_changes else 'Would update'} groups: {totals['updated']}" ) if apply_changes: return self.stdout.write("") self.stdout.write( "If the RAISE lines look right, add missing qty with:\n" " python manage.py heal_stock_quantities --apply" ) def _resolve_shop(self, name): shop = Shop.objects.filter(name__iexact=name.strip()).first() if not shop: shop = Shop.objects.filter(name__icontains=name.strip()).first() if shop: return shop available = ", ".join(Shop.objects.values_list("name", flat=True)) or "(none)" self.stderr.write( self.style.ERROR(f"Shop not found: {name}. Available: {available}") ) return None def _revert(self, dry_run=False, force=False): mode = "DRY-RUN revert" if dry_run else "REVERT" self.stdout.write( self.style.MIGRATE_HEADING( f"{mode}: undo 22 Aug all-products apply (Badakhshan opening stock)" ) ) restored = 0 skipped = 0 for shop_name, product_name, old_qty, new_qty in REVERT_20260822_ALL_PRODUCTS: shop = Shop.objects.filter(name__iexact=shop_name).first() if not shop: self.stdout.write(self.style.ERROR(f"Shop missing: {shop_name}")) skipped += 1 continue stocks = list( Stock.objects.filter( shop=shop, is_deleted=False, purchase_item__product__name__iexact=product_name, ).order_by("-quantity", "-id") ) match = None for stock in stocks: if int(stock.quantity or 0) == int(new_qty): match = stock break if match is None and stocks: match = stocks[0] if match is None: self.stdout.write( self.style.ERROR(f" No stock row: {shop_name} | {product_name}") ) skipped += 1 continue current = int(match.quantity or 0) if current == int(old_qty): skipped += 1 continue if current != int(new_qty) and not force: self.stdout.write( self.style.WARNING( f" SKIP {shop_name} | {product_name} " f"current={current} (expected applied {new_qty}). " f"Use --force to restore {old_qty} anyway." ) ) skipped += 1 continue self.stdout.write( f" {'Would restore' if dry_run else 'RESTORE'} " f"{shop_name} | {product_name} {current} → {old_qty}" ) if not dry_run: match.quantity = int(old_qty) match.save(update_fields=["quantity", "updated_at"]) restored += 1 self.stdout.write("") self.stdout.write( self.style.SUCCESS( f"{'Would restore' if dry_run else 'Restored'} {restored} row(s); skipped {skipped}." ) ) if dry_run: self.stdout.write( "Run without --dry-run:\n python manage.py heal_stock_quantities --revert" ) def _kabul_takhar_product_names(self, shops): shop_ids = [s.id for s in shops] names = set() transfers = StockTransfer.objects.filter( status=StockTransfer.Status.ACCEPTED, supplier_branch_id__in=shop_ids, customer_branch_id__in=shop_ids, ).select_related( "product", "source_stock__purchase_item__product", "target_stock__purchase_item__product", ) for transfer in transfers: if transfer.supplier_branch_id == transfer.customer_branch_id: continue for name in self._transfer_names(transfer): if name: names.add(name) return names def _heal_shop( self, shop, transferred_names, product_name, only_transferred, show_ok, apply_changes, raise_only=True, double_only=False, simple=False, apply_double=False, add_opening=False, ): self.stdout.write("") self.stdout.write(self.style.MIGRATE_HEADING(f"=== {shop.name} (id={shop.id}) ===")) buckets = defaultdict(_Bucket) product_ids_by_name = defaultdict(list) products = Product.objects.filter(shop=shop) if product_name: products = products.filter(name__iexact=product_name) for product in products: name = _norm_name(product.name) if not name: continue if only_transferred and name not in transferred_names: continue product_ids_by_name[name].append(product.id) if product_name and not product_ids_by_name: self.stdout.write(self.style.WARNING("No matching product in this shop.")) return defaultdict(int) allowed_names = set(product_ids_by_name.keys()) if only_transferred: allowed_names = set(transferred_names) if product_name: allowed_names = {_norm_name(product_name)} skip_purchase_ids = self._transfer_generated_purchase_item_ids(shop) self._add_stocks(shop, buckets, allowed_names, product_name) self._add_purchases(shop, buckets, allowed_names, skip_purchase_ids) self._add_purchase_returns(shop, buckets, allowed_names, skip_purchase_ids) self._add_sales(shop, buckets, allowed_names) self._add_sale_returns(shop, buckets, allowed_names) self._add_transfers(shop, buckets, allowed_names) self._add_pending_out(shop, buckets, allowed_names) counts = defaultdict(int) updates = [] for name in sorted(buckets.keys()): bucket = buckets[name] if allowed_names and name not in allowed_names: continue if product_name and name != _norm_name(product_name): continue bucket["name"] = bucket["name"] or name current = bucket.current expected = bucket.expected has_movement = any( bucket[field] for field in ( "purchase", "purchase_return", "sale", "sale_return", "transfer_in", "transfer_out", "pending_out", ) ) if current == 0 and expected == 0 and not has_movement: continue if apply_double: extra = int(current) - int(expected) transfer_in = int(bucket["transfer_in"] or 0) if expected >= 0 and extra > 0 and transfer_in > 0 and extra == transfer_in: counts["double"] += 1 self._print_simple(bucket, "DOUBLE", current, expected, extra) updates.append((bucket, expected)) continue if simple: self._count_simple(bucket, current, expected, counts) continue is_double = expected > 0 and current == (2 * expected) if double_only: if is_double: counts["double"] += 1 self._print_row(bucket, "DOUBLE_QTY", current, expected) continue has_invoices = any( bucket[field] for field in ("purchase", "purchase_return", "sale", "sale_return") ) has_transfers = any( bucket[field] for field in ("transfer_in", "transfer_out", "pending_out") ) if not has_invoices and not has_transfers: counts["opening"] += 1 if show_ok: self._print_row(bucket, "SKIP_OPENING", current, expected) continue if expected == current: counts["ok"] += 1 if show_ok: self._print_row(bucket, "OK", current, expected) continue if expected < 0: needed = int(current) - int(expected) counts["negative"] += 1 if add_opening: extra = needed self._print_simple(bucket, "ADD_OPENING", current, expected, extra) self.stdout.write( f" opening buy qty={needed} " f"(so bills match shelf stock={current})" ) updates.append((bucket, needed)) else: self._print_row(bucket, "SKIP_NEGATIVE", current, expected) continue if add_opening: continue if raise_only and expected < current: counts["surplus"] += 1 self._print_row(bucket, "KEEP_SURPLUS", current, expected) continue counts["mismatch"] += 1 status = "RAISE" if expected > current else "MISMATCH" self._print_row(bucket, status, current, expected) updates.append((bucket, expected)) if add_opening: if apply_changes: created, skipped = self._apply_opening_bills(shop, updates) counts["updated"] = created counts["opening_exists"] += skipped else: counts["updated"] = len(updates) return counts if apply_changes: counts["updated"] = self._apply_updates(shop, updates) else: counts["updated"] = len(updates) return counts def _count_simple(self, bucket, current, expected, counts): extra = int(current) - int(expected) transfer_in = int(bucket["transfer_in"] or 0) if expected < 0: counts["negative"] += 1 self._print_simple(bucket, "SOLD_MORE", current, expected, extra) return if current == expected: counts["ok"] += 1 return if extra > 0 and transfer_in > 0 and extra == transfer_in: counts["double"] += 1 self._print_simple(bucket, "DOUBLE", current, expected, extra) return if extra > 0: counts["surplus"] += 1 self._print_simple(bucket, "HIGH", current, expected, extra) return counts["mismatch"] += 1 self._print_simple(bucket, "LOW", current, expected, extra) def _print_simple(self, bucket, status, current, expected, extra): note = "" if status == "DOUBLE": note = " | extra qty = transfer in (counted twice on stock)" elif status == "HIGH": note = " | extra qty (opening stock or leftover)" elif status == "LOW": note = " | missing qty vs bills" elif status == "SOLD_MORE": note = " | sold more than bought — need a purchase/opening bill" elif status == "ADD_OPENING": note = " | will add Opening Stock purchase; shelf qty stays the same" style = self.style.ERROR if status in ("DOUBLE", "SOLD_MORE", "LOW") else self.style.WARNING self.stdout.write( style( f"{status:9} | {bucket['name']} " f"| stock={current} should={expected} extra={extra} " f"| buy={bucket['purchase']} in={bucket['transfer_in']} " f"| sold={bucket['sale']} out={bucket['transfer_out']}" f"{note}" ) ) def _print_row(self, bucket, status, current, expected): style = self.style.SUCCESS if status == "OK" else self.style.WARNING if status == "SKIP_NEGATIVE": style = self.style.ERROR elif status in ("SKIP_OPENING", "KEEP_SURPLUS", "DOUBLE_QTY"): style = self.style.NOTICE self.stdout.write( style( f"{status:14} | {bucket['name']} " f"| current={current} expected={expected} " f"| buy={bucket['purchase']} in={bucket['transfer_in']} " f"| sold={bucket['sale']} out={bucket['transfer_out']} " f"| ret_in={bucket['sale_return']} ret_out={bucket['purchase_return']} " f"| pending={bucket['pending_out']} rows={len(bucket['stocks'])}" ) ) def _apply_opening_bills(self, shop, updates): created = 0 skipped = 0 for bucket, needed in updates: result = self._create_opening_purchase(shop, bucket, int(needed)) if result == "created": created += 1 else: skipped += 1 return created, skipped def _opening_supplier(self, shop, created_by): supplier = ( Supplier.objects.filter( shop=shop, name__iexact=OPENING_SUPPLIER_NAME, is_deleted=False, ) .order_by("id") .first() ) if supplier: return supplier return Supplier.objects.create( shop=shop, name=OPENING_SUPPLIER_NAME, created_by=created_by, ) def _create_opening_purchase(self, shop, bucket, needed): if needed <= 0: self.stdout.write( self.style.WARNING(f" SKIP {bucket['name']}: opening qty {needed}") ) return "skipped" rows = sorted( bucket["stocks"], key=lambda s: (int(s.quantity or 0), int(s.id or 0)), reverse=True, ) stock = rows[0] if rows else None template = getattr(stock, "purchase_item", None) if stock else None product = getattr(template, "product", None) if product is None: product = ( Product.objects.filter(shop=shop, name__iexact=bucket["name"]) .order_by("id") .first() ) if product is None: self.stdout.write( self.style.ERROR( f" SKIP {bucket['name']}: no product row in {shop.name}" ) ) return "skipped" created_by = None if stock is not None: created_by = stock.created_by if created_by is None and template is not None: created_by = template.created_by supplier = self._opening_supplier(shop, created_by) existing_qty = ( PurchaseItem.objects.filter( purchase__shop=shop, purchase__is_deleted=False, purchase__supplier=supplier, product=product, is_deleted=False, ).aggregate(total=Sum("quantity"))["total"] or 0 ) existing_qty = int(existing_qty) add_qty = needed - existing_qty if add_qty <= 0: self.stdout.write( f" SKIP {shop.name} | {bucket['name']}: " f"Opening Stock already has {existing_qty}" ) return "skipped" currency = None unit = None wholesale = None retail = None sale_price = None if template is not None: currency = template.currency or getattr(template.purchase, "currency", None) unit = template.unit wholesale = template.wholesale_price retail = template.retail_price sale_price = template.sale_price or template.retail_price if currency is None and stock is not None: currency = stock.currency purchase_date = date(2020, 1, 1) if stock is not None and stock.created_at: purchase_date = (stock.created_at.date() - timedelta(days=1)) if purchase_date.year < 2000: purchase_date = date(2020, 1, 1) with transaction.atomic(): purchase = Purchase.objects.create( shop=shop, supplier=supplier, currency=currency, purchase_date=purchase_date, amount=Decimal("0"), received_amount=Decimal("0"), status=PaymentStatus.COMPLETED, created_by=created_by, ) PurchaseItem.objects.create( purchase=purchase, currency=currency, product=product, unit=unit, quantity=add_qty, buy_price=Decimal("0"), wholesale_price=wholesale, retail_price=retail, sale_price=sale_price, created_by=created_by, ) self.stdout.write( self.style.SUCCESS( f" OPENING {shop.name} | {bucket['name']} " f"+{add_qty} buy (shelf qty unchanged)" ) ) return "created" def _apply_updates(self, shop, updates): applied = 0 for bucket, expected in updates: rows = sorted( bucket["stocks"], key=lambda s: (int(s.quantity or 0), int(s.id or 0)), reverse=True, ) with transaction.atomic(): if not rows: primary = self._create_stock_for_bucket(shop, bucket, expected) if primary is None: self.stdout.write( self.style.ERROR( f" No stock row to set for {bucket['name']} " f"(expected {expected}). Skipped." ) ) continue old_qty = 0 extra_count = 0 else: primary = Stock.objects.select_for_update().get(pk=rows[0].pk) old_qty = int(primary.quantity or 0) primary.quantity = int(expected) primary.save(update_fields=["quantity", "updated_at"]) extra_count = 0 for extra in rows[1:]: extra_row = Stock.objects.select_for_update().get(pk=extra.pk) extra_row.quantity = 0 extra_row.save(update_fields=["quantity", "updated_at"]) extra_count += 1 self.stdout.write( self.style.WARNING( f" SET {bucket['name']} {old_qty} -> {expected} " f"(extra rows zeroed: {extra_count})" ) ) applied += 1 return applied def _create_stock_for_bucket(self, shop, bucket, expected): name = bucket.get("name") or "" item = ( PurchaseItem.objects.filter( purchase__shop=shop, product__name__iexact=name, is_deleted=False, purchase__is_deleted=False, ) .select_related("purchase") .order_by("-id") .first() ) if item is None: return None stock = Stock.objects.create( shop=shop, purchase_item=item, quantity=int(expected), average_buy_price=item.buy_price or 0, wholesale_price=item.wholesale_price, retail_price=item.retail_price, sale_price=item.sale_price or item.retail_price or 0, created_by=item.created_by, ) bucket["stocks"].append(stock) return stock def _bucket(self, buckets, name, display_name=None): key = _norm_name(name) if not key: return None bucket = buckets[key] if display_name and not bucket["name"]: bucket["name"] = str(display_name).strip() elif not bucket["name"]: bucket["name"] = str(name).strip() return bucket def _transfer_generated_purchase_item_ids(self, shop): """Purchase lines that are the transfer itself, not an extra local buy.""" ids = set() inbound = list( StockTransfer.objects.filter( customer_branch=shop, status=StockTransfer.Status.ACCEPTED, ).select_related( "product", "target_stock__purchase_item__product", ) ) transfers_by_name_date = defaultdict(int) transfer_exact = [] for transfer in inbound: name = _norm_name(self._preferred_name(transfer, "in")) qty = int(transfer.transfer_quantity or 0) date = transfer.transfer_date or transfer.accepted_date if name: transfer_exact.append((name, date, qty)) if date: transfers_by_name_date[(name, date)] += qty purchase_item = getattr(getattr(transfer, "target_stock", None), "purchase_item", None) purchase = getattr(purchase_item, "purchase", None) # target_stock can later be consolidated and repointed at a real # local purchase. Only skip the current pointer when it is still a # recognizable transfer-generated document. if purchase_item and ( _is_transfer_supplier(getattr(getattr(purchase, "supplier", None), "name", "")) or str(getattr(purchase, "notes", "") or "").startswith("Stock-only transfer ") ): ids.add(purchase_item.id) items = list( PurchaseItem.objects.filter( purchase__shop=shop, is_deleted=False, purchase__is_deleted=False, ).select_related("product", "purchase", "purchase__supplier") ) used_exact = set() for item in items: if str(getattr(item.purchase, "notes", "") or "").startswith("Stock-only transfer "): ids.add(item.id) continue supplier_name = getattr(getattr(item.purchase, "supplier", None), "name", "") if _is_transfer_supplier(supplier_name): ids.add(item.id) continue name = _norm_name(getattr(item.product, "name", None)) date = getattr(item.purchase, "purchase_date", None) qty = int(item.quantity or 0) for index, (t_name, t_date, t_qty) in enumerate(transfer_exact): if index in used_exact: continue if name == t_name and date == t_date and qty == t_qty: ids.add(item.id) used_exact.add(index) break for item in items: if item.id in ids: continue supplier_name = str( getattr(getattr(item.purchase, "supplier", None), "name", "") or "" ).strip() if supplier_name: continue name = _norm_name(getattr(item.product, "name", None)) date = getattr(item.purchase, "purchase_date", None) if (name, date) in transfers_by_name_date: ids.add(item.id) return ids def _add_stocks(self, shop, buckets, allowed_names, product_name): stocks = ( Stock.objects.filter(shop=shop, is_deleted=False) .select_related( "purchase_item__product", "purchase_item__currency", "purchase_item__purchase__currency", ) .order_by("id") ) for stock in stocks: product = getattr(getattr(stock, "purchase_item", None), "product", None) name = getattr(product, "name", None) if not name: continue if product_name and _norm_name(name) != _norm_name(product_name): continue if allowed_names and _norm_name(name) not in allowed_names: continue bucket = self._bucket(buckets, name, name) if bucket is not None: bucket["stocks"].append(stock) def _add_purchases(self, shop, buckets, allowed_names, skip_ids): items = ( PurchaseItem.objects.filter( purchase__shop=shop, is_deleted=False, purchase__is_deleted=False, ).select_related("product", "purchase__supplier") ) for item in items: if item.id in skip_ids: continue name = getattr(item.product, "name", None) if not name or (allowed_names and _norm_name(name) not in allowed_names): continue bucket = self._bucket(buckets, name, name) if bucket is not None: bucket["purchase"] += int(item.quantity or 0) def _add_purchase_returns(self, shop, buckets, allowed_names, skip_ids): items = ( PurchaseReturnItem.objects.filter(purchase_return__shop=shop) .select_related( "purchase_item__product", "purchase_item", "purchase_item__purchase__supplier", ) ) for item in items: purchase_item = item.purchase_item if not purchase_item or purchase_item.id in skip_ids: continue supplier_name = getattr( getattr(getattr(purchase_item, "purchase", None), "supplier", None), "name", "", ) if _is_transfer_supplier(supplier_name): continue name = getattr(purchase_item.product, "name", None) if not name or (allowed_names and _norm_name(name) not in allowed_names): continue bucket = self._bucket(buckets, name, name) if bucket is not None: bucket["purchase_return"] += int(item.return_quantity or 0) def _add_sales(self, shop, buckets, allowed_names): items = SaleItem.objects.filter(sale__shop=shop).select_related( "product", "sale", "sale__customer" ) for item in items: if _is_transfer_sale(item.sale): continue name = getattr(item.product, "name", None) if not name or (allowed_names and _norm_name(name) not in allowed_names): continue bucket = self._bucket(buckets, name, name) if bucket is not None: bucket["sale"] += int(item.quantity or 0) def _add_sale_returns(self, shop, buckets, allowed_names): items = ( SaleReturnItem.objects.filter(sale_return__shop=shop) .select_related( "sale_item__product", "sale_item__sale", "sale_item__sale__customer", ) ) for item in items: sale_item = item.sale_item if not sale_item or not sale_item.sale: continue if _is_transfer_sale(sale_item.sale): continue name = getattr(sale_item.product, "name", None) if not name or (allowed_names and _norm_name(name) not in allowed_names): continue bucket = self._bucket(buckets, name, name) if bucket is not None: bucket["sale_return"] += int(item.return_quantity or 0) def _add_transfers(self, shop, buckets, allowed_names): incoming = StockTransfer.objects.filter( customer_branch=shop, status=StockTransfer.Status.ACCEPTED, ).select_related( "product", "source_stock__purchase_item__product", "target_stock__purchase_item__product", ) for transfer in incoming: name = self._preferred_name(transfer, direction="in") if not name or (allowed_names and _norm_name(name) not in allowed_names): continue bucket = self._bucket(buckets, name, name) if bucket is not None: bucket["transfer_in"] += int(transfer.transfer_quantity or 0) outgoing = StockTransfer.objects.filter( supplier_branch=shop, status=StockTransfer.Status.ACCEPTED, ).select_related( "product", "source_stock__purchase_item__product", "target_stock__purchase_item__product", ) for transfer in outgoing: name = self._preferred_name(transfer, direction="out") if not name or (allowed_names and _norm_name(name) not in allowed_names): continue bucket = self._bucket(buckets, name, name) if bucket is not None: bucket["transfer_out"] += int(transfer.transfer_quantity or 0) def _add_pending_out(self, shop, buckets, allowed_names): pending = StockTransfer.objects.filter( supplier_branch=shop, status=StockTransfer.Status.PENDING, source_quantity_reserved=True, ).select_related( "product", "source_stock__purchase_item__product", ) for transfer in pending: name = self._preferred_name(transfer, direction="out") if not name or (allowed_names and _norm_name(name) not in allowed_names): continue bucket = self._bucket(buckets, name, name) if bucket is not None: bucket["pending_out"] += int(transfer.transfer_quantity or 0) def _preferred_name(self, transfer, direction): if direction == "in": stock = transfer.target_stock else: stock = transfer.source_stock product = getattr(getattr(stock, "purchase_item", None), "product", None) if product and product.name: return product.name if transfer.product and transfer.product.name: return transfer.product.name return "" def _transfer_names(self, transfer): names = [] for product in ( getattr(transfer, "product", None), getattr(getattr(getattr(transfer, "source_stock", None), "purchase_item", None), "product", None), getattr(getattr(getattr(transfer, "target_stock", None), "purchase_item", None), "product", None), ): name = _norm_name(getattr(product, "name", None)) if name: names.append(name) return names