from django.shortcuts import render import json from django.conf import settings from django.shortcuts import render 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 django.db import IntegrityError, transaction import hashlib import os from django.conf import settings from datetime import datetime from django.utils import timezone from django.db.models import Max, F, Q,Sum import base64 from django.shortcuts import render from io import BytesIO from django.conf import settings 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.http import HttpResponse from django.core.files.storage import default_storage #```````````````````````````````````````````````````````````**PURCHASE ORDER**```````````````````````````````````````````````````````````````````````````````` def material_request(request): if 'user_id' in request.session: user_type = request.session.get('user_type') branch_id = request.session.get('branch_id') is_ho = request.session.get('is_ho') if user_type == 'stores': branch = selectList(branch_table, {'is_ho':1},order_by='name').exclude(id=branch_id) if is_ho == 1: supplier = selectList(supplier_table, order_by='name' ) else: supplier = selectList(supplier_table, Q(is_global=1), order_by='name' ) return render(request, 'material_request/details.html',{'supplier':supplier,'branch':branch}) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def ajax_material_request(request): role_id = request.session.get('role_id') branch_id = request.session.get('branch_id') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) has_access, error_message = check_user_access(role_id, 'transfer_po', "read") if not has_access: return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to view Transfer Request details'}) supplier_id = request.POST.get('supplier') supply_id = request.POST.get('branch') from_date = request.POST.get('from_date') to_date = request.POST.get('to_date') keyword = request.POST.get('keyword_search', '').strip() query = Q(status=1, branch_id=branch_id, current_fy = financial_year) if supplier_id: query &= Q(supplier_id=supplier_id) if supply_id: query &= Q(supply_branch_id=supply_id) if from_date and to_date: query &= Q(po_date__range=[from_date, to_date]) if keyword: query &= Q(po_no__icontains=keyword) data = list(selectList(tm_material_request_table, query).values()) formatted = [] for index, item in enumerate(data): formatted.append({ 'id': index + 1, 'action': ' \ '.format(item['id'], item['id']), 'po_no': item['po_no'] if item['po_no'] else '-', 'po_date': format_date_month_year(item['po_date']), 'supply_branch': getItemNameById(branch_table, item['supply_branch_id']) if item['supply_branch_id'] else '-', 'quantity': item['total_quantity'] if item['total_quantity'] else '-', 'amount': format_amount(item['total_amount']), 'remarks': item['remarks'] if item['remarks'] else '-', 'status': 'Active' if item['is_active'] else 'Inactive' }) return JsonResponse({'data': formatted}) def material_request_add(request): if 'user_id' in request.session: user_type = request.session.get('user_type') branch_id = request.session.get('branch_id') is_ho = request.session.get('is_ho') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) if user_type == 'stores': company = select_row(company_table, {'id': 1}) employee = employee_list(request) branch = selectList(branch_table, {'is_ho':1}, order_by='name').exclude(id=branch_id) item = selectList(item_table ) if is_ho == 1: supplier = selectList(supplier_table, order_by='name' ) else: supplier = selectList(supplier_table, Q(is_global=1), order_by='name' ) employee = employee_list(request) brand = selectList(brand_table, order_by='name' ) sub_category = selectList(sub_category_table, order_by='name' ) generated_po_no = generate_serial_number(model=tm_material_request_table,number_field='po_no',financial_year_field='current_fy',financial_year=financial_year,branch_id=branch_id,prefix="PO_") return render(request, 'material_request/add.html', {'employee':employee,'company': company,'supplier':supplier,'branch':branch,'item':item,'employee':employee,'generated_po_no':generated_po_no,'brand':brand,'sub_category':sub_category}) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def add_material_request(request): if request.method == 'POST': try: print("๐ŸŸข add_material_request called") company_id = request.session.get('company_id') branch_id = request.session.get('branch_id') user_id = request.session.get('user_id') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) role_id = request.session.get('role_id') date_times = timezone.localtime(timezone.now()) # Permission check has_access = check_user_access(role_id, 'transfer_po', "create") if not has_access: print("โ›” Permission denied") return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to add Transfer Request details' }) po_date = request.POST.get('po_date') supply_branch_id = request.POST.get('supply_branch_id') or 0 remarks = request.POST.get('description') employee_id = request.POST.get('employee_id') or user_id total_qty = request.POST.get('total_quantity') sub_total = request.POST.get('sub_total') grand_total = request.POST.get('grand_total') round_off = request.POST.get('round_off') or 0 items_json = request.POST.get('items') items = json.loads(items_json) print(f"๐Ÿ“ฆ Items Count: {len(items)}") purchase_status = 'accepted' negative_check_fields = ['total_quantity', 'sub_total', 'grand_total'] has_negative, error_message = has_negative_values(request.POST, negative_check_fields) if has_negative: print("โš ๏ธ Negative value detected:", error_message) return JsonResponse({'message': 'infos', 'error_message': error_message}) if not supply_branch_id: print("โš ๏ธ supply_branch_id missing") return JsonResponse({ 'message': 'warning', 'error_message': 'Please select either a Supplier or a Supply Store.' }) if not items: print("โš ๏ธ No items provided") return JsonResponse({ 'message': 'warning', 'error_message': 'Please add at least one item to the order.' }) generated_po_no = generate_serial_number( model=tm_material_request_table, number_field='po_no', financial_year_field='current_fy', financial_year=financial_year, branch_id=branch_id, prefix="MO_" ) print("๐Ÿงพ Generated PO No:", generated_po_no) with transaction.atomic(): print("๐Ÿงฑ Creating main material request record") main_obj = tm_material_request_table.objects.create( company_id=company_id, po_date=po_date, time=date_times.strftime('%H:%M:%S'), po_no=generated_po_no, num_series=generate_num_series(tm_material_request_table), current_fy=financial_year, branch_id=branch_id, supply_branch_id=supply_branch_id, remarks=remarks if remarks else 'Transfer Request requested', total_quantity=total_qty, sub_total=sub_total, total_amount=grand_total, roundoff=round_off, po_status=purchase_status, is_active=1, status=1, created_on=date_times, updated_on=date_times, created_by=user_id, updated_by=user_id, employee_id=employee_id ) print("โœ… Main record created | ID:", main_obj.id) for idx, item in enumerate(items, start=1): print(f"โžก๏ธ Saving item {idx}:", item) tx_material_request_table.objects.create( company_id=company_id, current_fy=financial_year, branch_id=branch_id, supply_branch_id=supply_branch_id, tm_po_id=main_obj.id, subcategory_id=item['subcategory_id'] or 0, brand_id=item['brand_id'] or 0, model_id=item['model_id'] or 0, variant_id=item['variant_id'] or 0, color_id=item['color_id'] or 0, ean=item['ean_number'] or '', hsn_code=item['hsn_code'] or '', rate=item['rate'], quantity=item['quantity'], amount=item['amount'], mrp=item['mrp'], fsp=item['fsp'], wsp=item['wsp'], mop=item['mop'], bop=item['bop'], db_price=item['db_price'], is_active=1, status=1, created_on=date_times, updated_on=date_times, created_by=user_id, updated_by=user_id ) print("๐ŸŽ‰ All items saved successfully") return JsonResponse({'message': 'success'}) except IntegrityError as e: print("๐Ÿ’ฅ IntegrityError:", str(e)) return JsonResponse({ 'message': 'exception', 'error': 'Database error occurred: ' + str(e) }, status=500) except Exception as e: print("๐Ÿ”ฅ Exception:", str(e)) return JsonResponse({ 'message': 'exception', 'error': 'An error occurred: ' + str(e) }, status=500) def material_request_edit(request): if 'user_id' in request.session: user_type = request.session.get('user_type') branch_id = request.session.get('branch_id') is_ho = request.session.get('is_ho') if user_type == 'stores': encoded_id = request.GET.get('id', None) decoded_id = decode_base64_id(encoded_id) if decoded_id: company = select_row(company_table, {'id': 1}) material_request = select_row(tm_material_request_table, {'id':decoded_id}) if is_ho == 1: supplier = selectList(supplier_table, order_by='name' ) else: supplier = selectList(supplier_table, Q(is_global=1), order_by='name' ) brand = selectList(brand_table, order_by='name') subcategory = selectList(sub_category_table, order_by='name') employee = employee_list(request) branch = selectList(branch_table, order_by='name').exclude(id=branch_id) else: return HttpResponse("ID parameter is missing") return render(request, 'material_request/edit.html', {'employee':employee,'company': company,'material_request':material_request,'id':decoded_id,'supplier':supplier,'brand':brand,'subcategory':subcategory,'branch':branch}) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def ajax_material_request_edit(request): role_id = request.session.get('role_id') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) has_access, error_message = check_user_access(role_id, 'transfer_po', "read") if not has_access: return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to view the Transfer Request details'}) po_id = request.POST.get('po_id') data = list(selectList(tx_material_request_table,{'tm_po_id':po_id,'current_fy':financial_year}).values()) formatted = [] for index, item in enumerate(data): formatted.append({ 'id': index + 1, 'tx_id': item['id'], 'action': ''.format(item['id']), 'ean': item['ean'] if item['ean'] else '-', # โœ… With SKU 'subcategory_id': item['subcategory_id'], 'brand_id': item['brand_id'], 'model_id': item['model_id'], 'variant_id': item['variant_id'], 'color_id': item['color_id'], 'subcategory': getItemNameById(sub_category_table, item['subcategory_id']) if item['subcategory_id'] else '-', 'brand': getItemNameById(brand_table, item['brand_id']) if item['brand_id'] else '-', 'model': getItemNameById(model_table, item['model_id']) if item['model_id'] else '-', 'variant': getItemNameById(variant_table, item['variant_id']) if item['variant_id'] else '-', 'color': getItemNameById(color_table, item['color_id']) if item['color_id'] else '-', 'rate': format_amount(item['rate']) if item['rate'] else '-', 'qty': item['quantity'] if item['quantity'] else '-', 'hsn_code': item['hsn_code'] if item['hsn_code'] else '-', 'amt': format_amount(item['amount']) if item['amount'] else '-', }) return JsonResponse({'data': formatted}) def edit_material_request(request): if request.method == "POST": data = tx_material_request_table.objects.filter(id=request.POST.get('id')) return JsonResponse(data.values()[0]) def update_material_request(request): if request.method == 'POST': try: role_id = request.session.get('role_id') has_access, error_message = check_user_access(role_id, 'transfer_po', "update") if not has_access: return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to update the Transfer Request details'}) data = json.loads(request.body) def _to_float(value, default=0.0): try: if value in (None, ''): return default return float(value) except (TypeError, ValueError): return default def _to_int(value, default=0): try: if value in (None, ''): return default return int(value) except (TypeError, ValueError): return default user_id = request.session.get('user_id') po_id = data.get('tm_id') ean_number = data.get('ean_number') subcategory_id = data.get('subcategory_id') hsn_code = data.get('hsn_code') db_price = _to_float(data.get('db_price')) fsp = _to_float(data.get('fsp')) wsp = _to_float(data.get('wsp')) mop = _to_float(data.get('mop')) bop = _to_float(data.get('bop')) mrp = _to_float(data.get('mrp')) brand_id = data.get('brand_id') model_id = data.get('model_id') variant_id = data.get('variant_id') color_id = data.get('color_id') ean_number = data.get('ean_number') quantity = _to_int(data.get('quantity', 1), 1) rate = _to_float(data.get('rate')) edit_id = data.get('tx_id', 0) date_times = timezone.localtime(timezone.now()) amount = quantity * rate negative_check_fields = ['rate', 'quantity', 'amount'] has_negative, error_message = has_negative_values(request.POST, negative_check_fields) if has_negative: return JsonResponse({'message': 'infos', 'error_message': error_message}) purchase = select_row(tm_material_request_table, {'id': po_id}) if not purchase: return JsonResponse({'success': False, 'message': 'Purchase Order not found.'}) # # if purchase.po_status == 'accepted' or purchase.is_accepted == 1: # # return JsonResponse({'info': False, 'message': 'Cannot update. The Purchase Order is already accepted.'}) # inward_exists = selectList(child_purchase_inward_table, {'po_id': po_id}).exists() # if inward_exists: # return JsonResponse({'info': False, 'message': 'Cannot update. The Purchase Order has already been inwarded.'}) # โœ… Proceed with child table logic if edit_id and int(edit_id) != 0: item_row = tx_material_request_table.objects.filter(id=edit_id, tm_po_id=po_id).first() if item_row: item_row.subcategory_id = subcategory_id item_row.ean = ean_number item_row.brand_id = brand_id item_row.model_id = model_id item_row.variant_id = variant_id item_row.color_id = color_id item_row.quantity = quantity item_row.rate = rate item_row.hsn_code = hsn_code item_row.db_price = db_price item_row.fsp = fsp item_row.wsp = wsp item_row.mop = mop item_row.bop = bop item_row.mrp = mrp item_row.ean = ean_number item_row.amount = amount item_row.updated_on = date_times item_row.updated_by = user_id item_row.save() action = 'edited' else: return JsonResponse({'success': False, 'message': 'Item not found for editing.'}) else: existing_item = tx_material_request_table.objects.filter( tm_po_id=po_id, ean=ean_number,subcategory_id=subcategory_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, color_id=color_id, rate=rate ).first() if existing_item: existing_item.quantity += quantity existing_item.amount = existing_item.quantity * existing_item.rate existing_item.updated_on = date_times existing_item.updated_by = user_id existing_item.save() action = 'updated' else: tx_material_request_table.objects.create( company_id=purchase.company_id, current_fy=purchase.current_fy, branch_id=purchase.branch_id, supply_branch_id=purchase.supply_branch_id, tm_po_id=po_id, subcategory_id=subcategory_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, color_id=color_id, ean=ean_number, quantity=quantity, rate=rate, hsn_code=hsn_code, db_price=db_price, fsp=fsp, wsp=wsp, mop=mop, bop=bop, mrp=mrp, amount=amount, remarks='', created_on=date_times, updated_on=date_times, created_by=user_id, updated_by=user_id ) action = 'added' # โœ… Update parent totals (tm_po) totals = tx_material_request_table.objects.filter(tm_po_id=po_id).aggregate( total_qty=Sum('quantity'), total_amt=Sum('amount') ) purchase.total_quantity = totals['total_qty'] or 0 purchase.total_amount = totals['total_amt'] or 0 purchase.updated_on = date_times purchase.updated_by = user_id purchase.save() return JsonResponse({'success': True, 'action': action}) except Exception as e: return JsonResponse({'success': False, 'message': str(e)}) return JsonResponse({'success': False, 'message': 'Invalid request'}) def delete_tx_material_request(request): if request.method != 'POST': return JsonResponse({'message': 'Invalid request method'}) role_id = request.session.get('role_id') has_access, error_message = check_user_access(role_id, 'transfer_po', 'delete') if not has_access: return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to delete Transfer Request details' }) data_id = request.POST.get('id') if not data_id: return JsonResponse({'message': 'no such data'}) try: # ๐Ÿ”น Fetch request item request_item = tx_material_request_table.objects.filter( id=data_id, status=1 ).first() if not request_item: return JsonResponse({'message': 'no such data'}) po_id = request_item.tm_po_id # ๐Ÿ”’ Rule: If any outward exists โ†’ block delete if check_outward_exists_for_request(po_id): return JsonResponse({ 'message': 'infos', 'error_message': 'Outward has been created against this request. Deletion is blocked.' }) # ๐Ÿ”น Parent PO must exist parent_po = tm_material_request_table.objects.filter( id=po_id, status=1 ).first() if not parent_po: return JsonResponse({'message': 'no such data'}) # ๐Ÿ”น Soft delete tx_material_request_table.objects.filter(id=data_id).update(status=0) # ๐Ÿ”น Update summary after delete update_summary( tm_material_request_table, tx_material_request_table, 'tm_po_id', po_id ) return JsonResponse({'message': 'yes'}) except Exception as e: return JsonResponse({ 'message': 'exception', 'error': str(e) }) def check_outward_exists_for_request(request_id): """ Check if any outward exists against the given material request ID. Returns True if at least one outward record exists, False otherwise. Status does NOT matter - any outward blocks deletion. """ return child_material_outward_table.objects.filter( material_po_id=request_id, status=1 ).exists() def delete_material_request(request): if request.method == 'POST': data_id = request.POST.get('id') role_id = request.session.get('role_id') has_access, error_message = check_user_access(role_id, 'transfer_po', "delete") if not has_access: return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to delete Transfer Request details'}) try: po = tm_material_request_table.objects.filter(id=data_id, status=1).first() if not po: return JsonResponse({'message': 'no such data'}) # inward_exists = child_material_outward_table.objects.filter(po_id=data_id, status=1).exists() # if inward_exists: # return JsonResponse({'message': 'warning', 'error_message': 'Cannot delete. The Transfer Request has been outwarded by source branch.'}) # โŒ BLOCK: Outward exists (regardless of status) outward_exists = check_outward_exists_for_request(data_id) if outward_exists: return JsonResponse({ 'message': 'infos', 'error_message': 'Outward has been created against this request. Deletion is blocked.' }) updated = tm_material_request_table.objects.filter(id=data_id).update(status=0) updated = tx_material_request_table.objects.filter(tm_po_id=data_id).update(status=0) if updated: return JsonResponse({'message': 'yes'}) else: return JsonResponse({'message': 'no such data'}) except Exception as e: return JsonResponse({'message': 'exception', 'error': str(e)}) return JsonResponse({'message': 'Invalid request method'}) def get_ho_available_stock( subcategory_id=None, brand_id=None, model_id=None, variant_id=None, color_id=None, ): ho_branches = branch_table.objects.filter(is_ho=1, is_active=1) processed_imeis = set() available_rows = [] for ho in ho_branches: branch_id = ho.id base_q = Q(branch_id=branch_id, is_active=1, status=1) if subcategory_id: base_q &= Q(subcategory_id=subcategory_id) if brand_id: base_q &= Q(brand_id=brand_id) if model_id: base_q &= Q(model_id=model_id) if variant_id: base_q &= Q(variant_id=variant_id) if color_id: base_q &= Q(color_id=color_id) # ----------------------------- # INWARD SOURCES # ----------------------------- inward_qs = ( list(opening_stock_table.objects.filter(base_q)) + list(child_purchase_inward_table.objects.filter(base_q)) + list(child_material_inward_table.objects.filter(base_q)) + list(child_sales_return_table.objects.filter(base_q)) ) # ----------------------------- # OUTWARD IMEIS # ----------------------------- outward_imeis = set( child_material_outward_table.objects.filter( branch_id=branch_id, is_active=1, status=1 ).values_list('imei_no', flat=True) ).union( child_purchase_return_table.objects.filter( branch_id=branch_id, is_active=1, status=1 ).values_list('imei_no', flat=True) ) # ----------------------------- # FINAL AVAILABLE STOCK # ----------------------------- for row in inward_qs: if not row.imei_no: continue if row.imei_no in processed_imeis: continue if row.imei_no in outward_imeis: continue processed_imeis.add(row.imei_no) available_rows.append(row) return available_rows def load_material_request_price(request): if request.method != 'POST': return JsonResponse({'error': 'Invalid request'}, status=400) subcategory_id = request.POST.get('subcategory_id') brand_id = request.POST.get('brand_id') model_id = request.POST.get('model_id') variant_id = request.POST.get('variant_id') color_id = request.POST.get('color_id') # ----------------------------- # 1๏ธโƒฃ CHECK HO STOCK # ----------------------------- ho_stock = get_ho_available_stock( subcategory_id=subcategory_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, color_id=color_id, ) if not ho_stock: return JsonResponse({ 'status': False, 'message': 'Stock not available in Head Office' }) # Take latest HO inward for pricing ho_row = sorted( ho_stock, key=lambda x: x.created_on, reverse=True )[0] # ----------------------------- # 2๏ธโƒฃ ITEM MASTER FALLBACK # ----------------------------- item = item_table.objects.filter( sub_category_id=subcategory_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, color_id=color_id, is_active=1, status=1 ).first() return JsonResponse({ 'status': True, 'items': [{ 'ean': item.ean if item else '', 'hsn_code': item.hsn_code if item else '', 'fsp': ho_row.fsp or (item.franchise_amount if item else 0), 'wsp': ho_row.wsp or (item.wholesale_amount if item else 0), 'mop': ho_row.mop or (item.mop_price if item else 0), 'bop': ho_row.bop or (item.bop_price if item else 0), 'db_price': item.db_price if item else 0, 'available_stock': len(ho_stock) }] }) def ajax_material_request_stock_summary(request): branch_id = request.POST.get('supply_branch_id') brand_filter = request.POST.get('brand_id') model_filter = request.POST.get('model_id') if not branch_id: return JsonResponse({'status': False, 'message': 'Branch ID is missing'}) # Base filters base_filter = Q(branch_id=branch_id, status=1, is_active=1) if brand_filter: base_filter &= Q(brand_id=brand_filter) if model_filter: base_filter &= Q(model_id=model_filter) # ---- Step 1: Aggregate opening stock ---- opening_qs = ( opening_stock_table.objects.filter(base_filter) .values('subcategory_id', 'brand_id', 'model_id', 'variant_id', 'color_id') .annotate(total_opening=Sum('quantity')) ) opening_lookup = { (o['subcategory_id'], o['brand_id'], o['model_id'], o['variant_id'], o['color_id']): o['total_opening'] for o in opening_qs } # ---- Step 2: Aggregate purchase inward quantities ---- purchase_qs = ( child_purchase_inward_table.objects.filter(base_filter) .values('subcategory_id', 'brand_id', 'model_id', 'variant_id', 'color_id') .annotate(total_purchase=Sum('quantity')) ) purchase_lookup = { (p['subcategory_id'], p['brand_id'], p['model_id'], p['variant_id'], p['color_id']): p['total_purchase'] for p in purchase_qs } # ---- Step 3: Aggregate purchase return quantities ---- p_return_qs = ( child_purchase_return_table.objects.filter(base_filter) .values('subcategory_id', 'brand_id', 'model_id', 'variant_id', 'color_id') .annotate(total_p_return=Sum('quantity')) ) p_return_lookup = { (r['subcategory_id'], r['brand_id'], r['model_id'], r['variant_id'], r['color_id']): r['total_p_return'] for r in p_return_qs } # ---- Step 4: Aggregate material/transfer inward quantities ---- material_in_qs = ( child_material_inward_table.objects.filter(base_filter) .values('subcategory_id', 'brand_id', 'model_id', 'variant_id', 'color_id') .annotate(total_material_in=Sum('quantity')) ) material_in_lookup = { (m['subcategory_id'], m['brand_id'], m['model_id'], m['variant_id'], m['color_id']): m['total_material_in'] for m in material_in_qs } # ---- Step 5: Aggregate material/transfer outward quantities ---- # ๐Ÿšจ EXCLUDE REJECTED TRANSFERS material_out_qs = ( child_material_outward_table.objects.filter(base_filter) .exclude(tm_material_id__in=material_outward_table.objects.filter(outward_status='rejected').values_list('id', flat=True)) .values('subcategory_id', 'brand_id', 'model_id', 'variant_id', 'color_id') .annotate(total_material_out=Sum('quantity')) ) material_out_lookup = { (m['subcategory_id'], m['brand_id'], m['model_id'], m['variant_id'], m['color_id']): m['total_material_out'] for m in material_out_qs } # ---- Step 6: Aggregate sales quantities ---- sales_qs = ( child_sales_order_table.objects.filter(base_filter) .values('subcategory_id', 'brand_id', 'model_id', 'variant_id', 'color_id') .annotate(total_sales=Sum('quantity')) ) sales_lookup = { (s['subcategory_id'], s['brand_id'], s['model_id'], s['variant_id'], s['color_id']): s['total_sales'] for s in sales_qs } # ---- Step 7: Aggregate sales return quantities ---- s_return_qs = ( child_sales_return_table.objects.filter(base_filter) .values('subcategory_id', 'brand_id', 'model_id', 'variant_id', 'color_id') .annotate(total_s_return=Sum('quantity')) ) s_return_lookup = { (s['subcategory_id'], s['brand_id'], s['model_id'], s['variant_id'], s['color_id']): s['total_s_return'] for s in s_return_qs } # Combine all keys from all lookups to iterate over every relevant item combination all_keys = set(opening_lookup.keys()) | \ set(purchase_lookup.keys()) | \ set(p_return_lookup.keys()) | \ set(material_in_lookup.keys()) | \ set(material_out_lookup.keys()) | \ set(sales_lookup.keys()) | \ set(s_return_lookup.keys()) formatted = [] for key in all_keys: subcategory_id, brand_id, model_id, variant_id, color_id = key opening_qty = opening_lookup.get(key, 0) purchase_qty = purchase_lookup.get(key, 0) p_return_qty = p_return_lookup.get(key, 0) sales_qty = sales_lookup.get(key, 0) s_return_qty = s_return_lookup.get(key, 0) material_in_qty = material_in_lookup.get(key, 0) material_out_qty = material_out_lookup.get(key, 0) total_stock = ( (opening_qty or 0) + (purchase_qty or 0) + (material_in_qty or 0) - (p_return_qty or 0) - (sales_qty or 0) - (material_out_qty or 0) + (s_return_qty or 0) ) if total_stock > 0: # --- START PURCHASE-DRIVEN PRICING --- inward_filter = Q( branch_id=branch_id, subcategory_id=subcategory_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, color_id=color_id, status=1, is_active=1 ) # Fetch most recent inward from possible sources latest_purchase = child_purchase_inward_table.objects.filter(inward_filter).order_by('-created_on').first() latest_material = child_material_inward_table.objects.filter(inward_filter).order_by('-created_on').first() latest_opening = opening_stock_table.objects.filter(inward_filter).order_by('-created_on').first() latest_s_return = child_sales_return_table.objects.filter(inward_filter).order_by('-created_on').first() candidates = [c for c in [latest_purchase, latest_material, latest_opening, latest_s_return] if c] fsp = wsp = mop = bop = mrp = db_price = 0 ean = "-" hsn = "" if candidates: # Identify the single most recent record latest_row = max(candidates, key=lambda x: x.created_on) fsp = latest_row.fsp or 0 mop = latest_row.mop or 0 wsp = latest_row.wsp or 0 bop = latest_row.bop or 0 mrp = latest_row.mrp or 0 db_price = getattr(latest_row, 'db_price', 0) ean = getattr(latest_row, 'ean', '-') hsn = getattr(latest_row, 'hsn_code', '') # Apply price drop / override logic (consistent with IMEI stock summary) is_demo_val = 0 if hasattr(latest_row, 'is_demo'): is_demo_val = latest_row.is_demo if hasattr(latest_row, 'imei_no') and latest_row.imei_no: fsp, wsp, mop, bop = get_final_price_for_imei( branch_id=branch_id, subcategory_id=subcategory_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, imei_no=latest_row.imei_no, fsp=fsp, wsp=wsp, mop=mop, bop=bop ) # Fallback to master ONLY for missing values or if no inward found item = item_table.objects.filter( sub_category_id=subcategory_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, color_id=color_id ).first() if item: if not ean or ean == "-": ean = item.ean if not hsn: hsn = item.hsn_code if not fsp: fsp = item.franchise_amount if not mop: mop = item.mop_price if not wsp: wsp = item.wholesale_amount if not bop: bop = item.bop_price if not mrp: mrp = item.mrp if not db_price: db_price = item.db_price subcategory_name = getItemNameById(sub_category_table, subcategory_id) brand_name = getItemNameById(brand_table, brand_id) model_name = getItemNameById(model_table, model_id) variant_name = getItemNameById(variant_table, variant_id) color_name = getItemNameById(color_table, color_id) description = f"{brand_name} {model_name} {variant_name} {color_name}" formatted.append({ 'item_id': item.id if item else 0, 'subcategory_id': subcategory_id, 'brand_id': brand_id, 'model_id': model_id, 'variant_id': variant_id, 'color_id': color_id, 'ean': ean, 'subcategory': subcategory_name, 'brand': brand_name, 'model': model_name, 'variant': variant_name, 'color': color_name, 'description': description, 'stock': total_stock, 'fsp': fsp, 'rate': fsp, 'mop': mop, 'hsn_code': hsn, 'db_price': db_price, 'wsp': wsp, 'bop': bop, 'mrp': mrp, 'is_demo': is_demo_val }) return JsonResponse({'status': True, 'data': formatted})