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 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 from supplier.models import * from sales.models import * #```````````````````````````````````````````````````````````**PURCHASE RETURN**```````````````````````````````````````````````````````````````````````````````` def purchase_return(request): if 'user_id' in request.session: user_type = request.session.get('user_type') user_id = request.session.get('user_id') branch_id = request.session.get('branch_id') fyf_name = request.session.get('fyf') is_ho = request.session.get('is_ho') encoded_id = request.GET.get('id') or 0 if encoded_id != 0: decoded_id = decode_base64_id(encoded_id) or 0 else: decoded_id = 0 if user_type == 'stores': branch = selectList(branch_table, 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, 'purchase_return/details.html',{ 'supplier':supplier, 'branch':branch, 'decoded_id':decoded_id }) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def ajax_return_view(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, 'purchase_return', "read") if not has_access: return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to view purchase return details'}) supplier_id = request.POST.get('supplier') supply_branch_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() sales_id = request.POST.get('return_id') query = Q(status=1,branch_id=branch_id,current_fy=financial_year) if sales_id: query &= Q(id=sales_id) if supplier_id: query &= Q(supplier_id=supplier_id) if from_date and to_date: query &= Q(pr_date__range=[from_date, to_date]) data = list(selectList(purchase_return_table, query).values()) formatted = [] for index, item in enumerate(data): formatted.append({ 'id': index + 1, 'action': ( f' ' f'' ), 'po_no': item['pr_no'] if item['pr_no'] else '-', 'po_date': format_date_month_year(item['pr_date']), 'supplier': getItemNameById(supplier_table, item['supplier_id']) if item['supplier_id'] else '-', 'quantity': item['total_quantity'] if item['total_quantity'] else '-', 'amount': format_amount(item['total_amount']), 'cash': format_amount(item['cash']), 'bank': format_amount(item['bank']), 'balance': format_amount(item['balance']), 'pr_status': format_badge(item['pr_status'], mapping={ 'pending': 'badge text-bg-info', 'approved': 'badge text-bg-success', 'reversed': 'badge text-bg-danger' }, label_mapping={ 'pending': 'Pending', 'approved': 'Approved', 'reversed': 'Reversed' }), 'status': ( 'Active' if item['is_active'] else 'Inactive' ) }) return JsonResponse({'data': formatted}) def purchase_return_add(request): if 'user_id' in request.session: user_type = request.session.get('user_type') is_ho = request.session.get('is_ho') branch_id = request.session.get('branch_id') if user_type == 'stores': company = select_row(company_table, {'id': 1}) branch = selectList(branch_table, order_by='name').exclude(id=branch_id) item = selectList(item_table) supplier = selectList(supplier_table, order_by='name' ) employee = employee_list(request) fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) generated_po_no = generate_serial_number(model=purchase_return_table,number_field='pr_no',financial_year_field='current_fy',financial_year=financial_year,branch_id=branch_id) return render(request, 'purchase_return/add.html', {'company': company,'supplier':supplier,'branch':branch,'item':item, 'employee':employee,'generated_po_no':generated_po_no}) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") from django.http import JsonResponse from django.db import transaction, IntegrityError from django.utils import timezone import json from .models import purchase_return_table, child_purchase_return_table def add_purchase_return(request): if request.method != 'POST': return JsonResponse({'message': 'error', 'error_message': 'Invalid request method'}, status=405) try: # ------------------------- # Session & basic info # ------------------------- 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 # ------------------------- if not check_user_access(role_id, 'purchase_return', "create"): return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to add purchase return details' }) # ------------------------- # Form fields # ------------------------- supplier_id = request.POST.get('supplier_id') or 0 remarks = request.POST.get('description', '').strip() po_no = request.POST.get('po_no', '').strip() sub_total = request.POST.get('sub_total') or 0 total_cgst = request.POST.get('total_cgst') or 0 total_sgst = request.POST.get('total_sgst') or 0 total_tds = request.POST.get('total_tds') or 0 total_amount = request.POST.get('total_amount') or 0 total_qty = request.POST.get('total_quantity') or 0 grand_total = request.POST.get('grand_total') or 0 round_off = request.POST.get('round_off') or 0 payment = request.POST.get('payment', 'credit') cash = float(request.POST.get('cash') or 0) bank = float(request.POST.get('bank') or 0) employee_id = request.POST.get('employee_id') or user_id balance = request.POST.get('balance') or 0 items_json = request.POST.get('items', '[]') return_status = request.POST.get('return_status') or 'pending' items = json.loads(items_json) if not supplier_id: return JsonResponse({'message': 'warning', 'error_message': 'Please select a supplier.'}) if not items: return JsonResponse({'message': 'warning', 'error_message': 'Please add at least one item.'}) # ------------------------- # Validate items # ------------------------- valid_items = [] for idx, item in enumerate(items, start=1): try: inward_id = int(item.get('po_id', 0)) ean_no = item.get('ean', '') hsn_code = item.get('hsn_code', '') subcategory_id = int(item.get('subcategory_id', 0)) brand_id = int(item.get('brand_id', 0)) model_id = int(item.get('model_id', 0)) # corrected typo variant_id = int(item.get('variant_id', 0)) color_id = int(item.get('color_id', 0)) fsp_price = float(item.get('fsp_price', 0)) wsp_price = float(item.get('wsp_price', 0)) mop_price = float(item.get('mop_price', 0)) bop_price = float(item.get('bop_price', 0)) mrp = float(item.get('mrp', 0)) quantity = float(item.get('quantity', 1)) db_price = float(item.get('db_price', 0)) rate = float(item.get('rate', 0)) imei_no = item.get('imei_no', '') tx_cgst = float(item.get('tx_cgst', 0)) tx_sgst = float(item.get('tx_sgst', 0)) tx_igst = float(item.get('tx_igst', 0)) tax_percent = float(item.get('tax_percent', 0)) tax_amount = float(item.get('tax_amount', 0)) amount = float(item.get('amount', 0)) if inward_id == 0: return JsonResponse({ 'message': 'warning', 'error_message': f'Invalid Purchase Order for item #{idx}.' }) if not imei_no: return JsonResponse({ 'message': 'warning', 'error_message': f'IMEI is required for item #{idx}.' }) valid_items.append({ 'inward_id': inward_id, 'ean_no': ean_no, 'subcategory_id': subcategory_id, 'brand_id': brand_id, 'model_id': model_id, 'variant_id': variant_id, 'color_id': color_id, 'fsp': fsp_price, 'wsp': wsp_price, 'mop': mop_price, 'bop': bop_price, 'mrp': mrp, 'quantity': quantity, 'amount': amount, 'imei_no': imei_no, 'hsn_code': hsn_code, 'db_price': db_price, 'rate': rate, 'tx_cgst': tx_cgst, 'tx_sgst': tx_sgst, 'tx_igst': tx_igst, 'tax_percent': tax_percent, 'tax_amount': tax_amount, }) except Exception as e: return JsonResponse({ 'message': 'warning', 'error_message': f'Invalid data for item #{idx}: {str(e)}' }) # ------------------------- # Create main Purchase Return + children # ------------------------- with transaction.atomic(): pr_no = generate_serial_number( model=purchase_return_table, number_field='pr_no', financial_year_field='current_fy', financial_year=financial_year, branch_id=branch_id ) main_obj = purchase_return_table.objects.create( company_id=company_id, pr_date=request.POST.get('po_date'), pr_no=po_no, num_series=generate_num_series(purchase_return_table), current_fy=financial_year, branch_id=branch_id, supplier_id=supplier_id, remarks=remarks, total_quantity=total_qty, sub_total=sub_total, total_cgst=total_cgst, total_sgst=total_sgst, total_tds=total_tds, total_amount=total_amount, grand_total=grand_total, payment_type=payment, cash=cash, bank=bank, round_off=round_off, balance=grand_total, pr_status=return_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, time=date_times.strftime('%H:%M:%S') ) # Create child items for item in valid_items: child_purchase_return_table.objects.create( company_id=company_id, current_fy=financial_year, branch_id=branch_id, supplier_id=supplier_id, tm_return_id=main_obj.id, inward_id=item['inward_id'], imei_no=item['imei_no'], ean=item['ean_no'], 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'], hsn_code=item['hsn_code'], rate=item['rate'], amount=item['amount'], fsp=item['fsp'], wsp=item['wsp'], mop=item['mop'], bop=item['bop'], mrp=item['mrp'], db_price=item['db_price'], tax_percent=item['tax_percent'], tax_amount=item['tax_amount'], tax_cgst=item['tx_cgst'], tax_sgst=item['tx_sgst'], tax_igst=item['tx_igst'], is_active=1, status=1, quantity=1, created_on=date_times, updated_on=date_times, created_by=user_id, updated_by=user_id ) return JsonResponse({'message': 'success', 'pr_no': pr_no}) except IntegrityError as e: return JsonResponse({'message': 'exception', 'error': 'Database error: ' + str(e)}, status=500) except Exception as e: return JsonResponse({'message': 'exception', 'error': 'Error: ' + str(e)}, status=500) from django.http import JsonResponse from django.db.models import Sum from django.http import JsonResponse from django.db.models import Sum from material.models import * from django.http import JsonResponse from django.db.models import Sum def load_purchase_inward(request): supplier_id = request.GET.get('supplier_id') branch_id = request.session.get('branch_id') edit_id = request.GET.get('edit_id') try: edit_id = int(edit_id) except (TypeError, ValueError): edit_id = 0 # ----------------------------- # Base filters # ----------------------------- filters = { 'status': 1, 'branch_id': branch_id } if supplier_id: filters['supplier_id'] = supplier_id # ----------------------------- # Purchase Inward (PO list) # ----------------------------- pos = list( purchase_inward_table.objects .filter(**filters) .values('id', 'pu_no') ) if not pos: return JsonResponse([], safe=False) po_ids = [p['id'] for p in pos] # ----------------------------- # Child Purchase Inward Items # ----------------------------- tx_items = child_purchase_inward_table.objects.filter( status=1, branch_id=branch_id, tm_pu_id__in=po_ids ).values('imei_no', 'tm_pu_id') # Purchase Returns (exclude current edit) - Only count approved ones purchase_return_qs = child_purchase_return_table.objects.filter( status=1, branch_id=branch_id, inward_id__in=po_ids, tm_return_id__in=purchase_return_table.objects.filter( pr_status__iexact='approved' ).values_list('id', flat=True) ) if edit_id > 0: purchase_return_qs = purchase_return_qs.exclude( tm_return_id=edit_id ) purchase_returns = purchase_return_qs.values('imei_no').annotate( total_returned_qty=Sum('quantity') ) purchase_return_map = { r['imei_no']: r['total_returned_qty'] or 0 for r in purchase_returns } # ----------------------------- # Sales Out # ----------------------------- sold_imeis = set( child_sales_order_table.objects.filter( status=1, branch_id=branch_id, tm_sales_id__in=sales_order_table.objects.filter( branch_id=branch_id, status=1 ).values_list('id', flat=True) ).values_list('imei_no', flat=True) ) # ----------------------------- # Sales Returns (exclude current edit) # ----------------------------- sales_return_qs = child_sales_return_table.objects.filter( status=1, branch_id=branch_id, tm_return_id__in=sales_return_table.objects.filter( branch_id=branch_id, status=1 ).values_list('id', flat=True) ) if edit_id > 0: sales_return_qs = sales_return_qs.exclude( tm_return_id=edit_id ) returned_sales_imeis = set( sales_return_qs.values_list('imei_no', flat=True) ) # ----------------------------- # Material Out # ----------------------------- material_out_imeis = set( child_material_outward_table.objects.filter( status=1, branch_id=branch_id, material_po_id__in=po_ids ).values_list('imei_no', flat=True) ) # ----------------------------- # Material In # ----------------------------- material_in_imeis = set( child_material_inward_table.objects.filter( status=1, branch_id=branch_id, tm_material_id__in=po_ids ).values_list('imei_no', flat=True) ) # ----------------------------- # Calculate available stock per PO # ----------------------------- valid_po_ids = set() for item in tx_items: imei_no = item['imei_no'] po_id = item['tm_pu_id'] purchased_qty = 1 returned_qty = purchase_return_map.get(imei_no, 0) sold_qty = 1 if imei_no in sold_imeis else 0 sales_return_qty = 1 if imei_no in returned_sales_imeis else 0 material_out_qty = 1 if imei_no in material_out_imeis else 0 material_in_qty = 1 if imei_no in material_in_imeis else 0 available = ( purchased_qty - returned_qty - sold_qty + sales_return_qty - material_out_qty + material_in_qty ) if available > 0: valid_po_ids.add(po_id) # ----------------------------- # Final filtered PO list # ----------------------------- filtered_pos = [p for p in pos if p['id'] in valid_po_ids] return JsonResponse(filtered_pos, safe=False) def load_purchase_inward_edit(request): supplier_id = request.GET.get('supplier_id') store_id = request.GET.get('store_id') edit_id = request.GET.get('edit_id') or 0 branch_id = request.session.get('branch_id') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) filters = {'status': 1, 'branch_id': branch_id} if supplier_id: filters['supplier_id'] = supplier_id if store_id: filters['supply_branch_id'] = store_id pos = purchase_inward_table.objects.filter(**filters).values('id', 'batch_no') po_ids = [p['id'] for p in pos] tx_items = child_purchase_inward_table.objects.filter( status=1, branch_id=branch_id, tm_pu_id__in=po_ids ).values('id', 'batch_no', 'item_id', 'quantity', 'tm_pu_id') # current return entry (only if editing) current_return_qty = 0 if edit_id: current_return = child_purchase_return_table.objects.filter( id=edit_id, status=1, branch_id=branch_id ).first() if current_return: current_return_qty = current_return.quantity valid_po_ids = set() for t in tx_items: item_id = t['item_id'] batch_no = t['batch_no'] po_id = t['tm_pu_id'] # total inward qty for this PO + item + batch inward_qty = child_purchase_inward_table.objects.filter( status=1, branch_id=branch_id, tm_pu_id=po_id, item_id=item_id, batch_no=batch_no ).aggregate(total=Sum('quantity'))['total'] or 0 # total returned qty (excluding current edit row if any) - Only count approved ones returned_qty = child_purchase_return_table.objects.filter( status=1, branch_id=branch_id, pu_id=po_id, item_id=item_id, batch_no=batch_no, tm_return_id__in=purchase_return_table.objects.filter( pr_status__iexact='approved' ).values_list('id', flat=True) ).exclude(id=edit_id).aggregate(total=Sum('quantity'))['total'] or 0 available = inward_qty - returned_qty # allow if available > 0 OR it's the one being edited if available > 0 or str(po_id) == str(edit_id): valid_po_ids.add(po_id) filtered_pos = [p for p in pos if p['id'] in valid_po_ids] return JsonResponse(filtered_pos, safe=False) from django.db.models import Sum from django.http import JsonResponse def load_inward_items(request): branch_id = request.session.get('branch_id') po_id = request.GET.get('po_ids') edit_id = request.GET.get('edit_id') or 0 if not po_id: return JsonResponse({"items": [], "summary": {}}, status=400) try: po_id = int(po_id) except ValueError: return JsonResponse({"items": [], "summary": {}}, status=400) items_data = [] total_qty = 0 sub_total = 0 # Fetch related item transactions from inward tx_items = child_purchase_inward_table.objects.filter( tm_pu_id=po_id, status=1, branch_id=branch_id ) return_qs = child_purchase_return_table.objects.filter( status=1, branch_id=branch_id, pu_id=po_id, tm_return_id__in=purchase_return_table.objects.exclude( pr_status__iexact='reversed' ).values_list('id', flat=True) ) if int(edit_id) != 0: return_qs = return_qs.exclude(tm_return_id=edit_id) return_aggregation = return_qs.values('item_id', 'uom_id').annotate( total_returned_qty=Sum('quantity') ) # Quick lookup for returned qty returned_qty_map = { (r['item_id'], r['uom_id']): r['total_returned_qty'] or 0 for r in return_aggregation } for item in tx_items: item_obj = item_table.objects.filter(id=item.item_id).first() uom_obj = uom_table.objects.filter(id=item.uom_id).first() item_name = item_obj.name if item_obj else 'N/A' item_code = item_obj.sku if item_obj else 'N/A' unit_name = uom_obj.name if uom_obj else 'N/A' key = (item.item_id, item.uom_id) returned_qty = returned_qty_map.get(key, 0) inward_qty = item.quantity or 0 remaining_qty = max(inward_qty - returned_qty, 0) # ✅ Only include items that have remaining qty if remaining_qty > 0: items_data.append({ "item_id": item.item_id, "item_code": item_code, "uom_id": item.uom_id, "po_id": item.tm_pu_id, "item_name": item_name, "unit_name": unit_name, "rate": item.rate, "quantity": inward_qty, "amount": item.amount, "remaining_quantity": remaining_qty, "batch_no": item.batch_no }) total_qty += remaining_qty sub_total += (item.rate or 0) * remaining_qty # Round-off and grand total roundoff = 0 grand_total = sub_total + roundoff # Aggregate cash, bank, and balance from purchase_inward_table summary = purchase_inward_table.objects.filter( id=po_id, status=1 ).aggregate( total_cash=Sum('cash'), total_bank=Sum('bank'), total_balance=Sum('balance') ) cash = summary['total_cash'] or 0 bank = summary['total_bank'] or 0 balance = summary['total_balance'] or 0 return JsonResponse({ "items": items_data, "summary": { "total_qty": total_qty, "sub_total": round(sub_total, 2), "grand_total": round(grand_total, 2), "cash": cash, "bank": bank, "balance": balance } }) def load_inward_items_edit(request): branch_id = request.session.get('branch_id') po_id = request.GET.get('po_ids') edit_id = request.GET.get('edit_id') or 0 if not po_id: return JsonResponse({"items": [], "summary": {}}, status=400) try: po_id = int(po_id) except ValueError: return JsonResponse({"items": [], "summary": {}}, status=400) items_data = [] total_qty = 0 sub_total = 0 # Fetch related item transactions from inward tx_items = child_purchase_inward_table.objects.filter( tm_pu_id=po_id, status=1, branch_id=branch_id ) return_qs = child_purchase_return_table.objects.filter( status=1, branch_id=branch_id, pu_id=po_id, tm_return_id__in=purchase_return_table.objects.exclude( pr_status__iexact='reversed' ).values_list('id', flat=True) ) if int(edit_id) != 0: return_qs = return_qs.exclude(tm_return_id=edit_id) return_aggregation = return_qs.values('item_id', 'uom_id').annotate( total_returned_qty=Sum('quantity') ) # Quick lookup for returned qty returned_qty_map = { (r['item_id'], r['uom_id']): r['total_returned_qty'] or 0 for r in return_aggregation } for item in tx_items: item_obj = item_table.objects.filter(id=item.item_id).first() uom_obj = uom_table.objects.filter(id=item.uom_id).first() item_name = item_obj.name if item_obj else 'N/A' item_code = item_obj.sku if item_obj else 'N/A' unit_name = uom_obj.name if uom_obj else 'N/A' key = (item.item_id, item.uom_id) returned_qty = returned_qty_map.get(key, 0) inward_qty = item.quantity or 0 remaining_qty = max(inward_qty - returned_qty, 0) # ✅ Only include items that have remaining qty if remaining_qty > 0: items_data.append({ "item_id": item.item_id, "item_code": item_code, "uom_id": item.uom_id, "po_id": item.tm_pu_id, "item_name": item_name, "unit_name": unit_name, "rate": item.rate, "quantity": inward_qty, "amount": item.amount, "remaining_quantity": remaining_qty, "batch_no": item.batch_no }) total_qty += remaining_qty sub_total += (item.rate or 0) * remaining_qty # Round-off and grand total roundoff = 0 grand_total = sub_total + roundoff # Aggregate cash, bank, and balance from purchase_inward_table summary = purchase_inward_table.objects.filter( id=po_id, status=1 ).aggregate( total_cash=Sum('cash'), total_bank=Sum('bank'), total_balance=Sum('balance') ) cash = summary['total_cash'] or 0 bank = summary['total_bank'] or 0 balance = summary['total_balance'] or 0 return JsonResponse({ "items": items_data, "summary": { "total_qty": total_qty, "sub_total": round(sub_total, 2), "grand_total": round(grand_total, 2), "cash": cash, "bank": bank, "balance": balance } }) def purchase_return_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}) purchase_order = select_row(purchase_return_table, {'id':decoded_id}) branch = selectList(branch_table, order_by='name').exclude(id=branch_id) item = selectList(item_table) supplier = selectList(supplier_table, order_by='name' ) employee = employee_list(request) else: return HttpResponse("ID parameter is missing") return render(request, 'purchase_return/edit.html', {'company': company,'purchase':purchase_order,'id':decoded_id,'supplier':supplier,'branch':branch,'item':item,'employee':employee}) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def edit_purchase_return(request): if request.headers.get('x-requested-with') == 'XMLHttpRequest' and request.method == "POST": data = child_purchase_return_table.objects.filter(id=request.POST.get('id')) return JsonResponse(data.values()[0]) def update_purchase_return(request): if request.method != 'POST': return JsonResponse({'message': 'error', 'error_message': 'Invalid request method'}, status=405) try: # ------------------------- # Session & basic info # ------------------------- 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 # ------------------------- if not check_user_access(role_id, 'purchase_return', "create"): return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to add purchase return details' }) # ------------------------- # Form fields # ------------------------- supplier_id = request.POST.get('supplier_id') or 0 remarks = request.POST.get('description', '').strip() sub_total = request.POST.get('sub_total') or 0 total_cgst = request.POST.get('total_cgst') or 0 total_sgst = request.POST.get('total_sgst') or 0 total_tds = request.POST.get('total_tds') or 0 total_amount = request.POST.get('total_amount') or 0 total_qty = request.POST.get('total_quantity') or 0 grand_total = request.POST.get('grand_total') or 0 round_off = request.POST.get('round_off') or 0 payment = request.POST.get('payment', '') cash = float(request.POST.get('cash') or 0) bank = float(request.POST.get('bank') or 0) employee_id = request.POST.get('employee_id') or user_id balance = request.POST.get('balance') or 0 items_json = request.POST.get('items', '[]') return_id = request.POST.get('tm_return_id') or 0 return_status = request.POST.get('return_status') or 'pending' parent = select_row(purchase_return_table, {'id': return_id}) items = json.loads(items_json) # if not supplier_id: # return JsonResponse({'message': 'warning', 'error_message': 'Please select a supplier.'}) if not items: return JsonResponse({'message': 'warning', 'error_message': 'Please add at least one item.'}) # ------------------------- # Validate items # ------------------------- valid_items = [] for idx, item in enumerate(items, start=1): try: inward_id = int(item.get('po_id', 0)) ean_no = item.get('ean', '') hsn_code = item.get('hsn_code', '') subcategory_id = int(item.get('subcategory_id', 0)) brand_id = int(item.get('brand_id', 0)) model_id = int(item.get('model_id', 0)) # corrected typo variant_id = int(item.get('variant_id', 0)) color_id = int(item.get('color_id', 0)) fsp_price = float(item.get('fsp_price', 0)) wsp_price = float(item.get('wsp_price', 0)) mop_price = float(item.get('mop_price', 0)) bop_price = float(item.get('bop_price', 0)) mrp = float(item.get('mrp', 0)) quantity = float(item.get('quantity', 1)) db_price = float(item.get('db_price', 0)) rate = float(item.get('rate', 0)) imei_no = item.get('imei_no', '') tx_cgst = float(item.get('tx_cgst', 0)) tx_sgst = float(item.get('tx_sgst', 0)) tx_igst = float(item.get('tx_igst', 0)) tax_percent = float(item.get('tax_percent', 0)) tax_amount = float(item.get('tax_amount', 0)) amount = float(item.get('amount', 0)) if inward_id == 0: return JsonResponse({ 'message': 'warning', 'error_message': f'Invalid Purchase Order for item #{idx}.' }) if not imei_no: return JsonResponse({ 'message': 'warning', 'error_message': f'IMEI is required for item #{idx}.' }) valid_items.append({ 'inward_id': inward_id, 'ean_no': ean_no, 'subcategory_id': subcategory_id, 'brand_id': brand_id, 'model_id': model_id, 'variant_id': variant_id, 'color_id': color_id, 'fsp': fsp_price, 'wsp': wsp_price, 'mop': mop_price, 'bop': bop_price, 'mrp': mrp, 'quantity': quantity, 'amount': amount, 'imei_no': imei_no, 'hsn_code': hsn_code, 'db_price': db_price, 'rate': rate, 'tx_cgst': tx_cgst, 'tx_sgst': tx_sgst, 'tx_igst': tx_igst, 'tax_percent': tax_percent, 'tax_amount': tax_amount, }) except Exception as e: return JsonResponse({ 'message': 'warning', 'error_message': f'Invalid data for item #{idx}: {str(e)}' }) # ------------------------- # Create main Purchase Return + children # ------------------------- child_purchase_return_table.objects.filter( tm_return_id=return_id, status=1 ).update(status=0, updated_on=date_times, updated_by=user_id) # Create child items for item in valid_items: child_purchase_return_table.objects.create( company_id=company_id, current_fy=financial_year, branch_id=branch_id, supplier_id=parent.supplier_id, tm_return_id=parent.id, inward_id=item['inward_id'], imei_no=item['imei_no'], ean=item['ean_no'], 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'], hsn_code=item['hsn_code'], rate=item['rate'], amount=item['amount'], fsp=item['fsp'], wsp=item['wsp'], mop=item['mop'], bop=item['bop'], mrp=item['mrp'], db_price=item['db_price'], tax_percent=item['tax_percent'], tax_amount=item['tax_amount'], tax_cgst=item['tx_cgst'], tax_sgst=item['tx_sgst'], tax_igst=item['tx_igst'], is_active=1, status=1, quantity=1, created_on=date_times, updated_on=date_times, created_by=user_id, updated_by=user_id ) parent.total_quantity = total_qty parent.sub_total = sub_total parent.total_cgst = total_cgst parent.total_sgst = total_sgst parent.total_tds = total_tds parent.total_amount = total_amount parent.grand_total = grand_total parent.pr_status = return_status parent.payment_type = payment parent.cash = cash parent.bank = bank parent.round_off = round_off parent.balance = grand_total parent.save() return JsonResponse({'message': 'success'}) except IntegrityError as e: return JsonResponse({'message': 'exception', 'error': 'Database error: ' + str(e)}, status=500) except Exception as e: return JsonResponse({'message': 'exception', 'error': 'Error: ' + str(e)}, status=500) def ajax_pr_edit(request): # PU - Purchase Inward role_id = request.session.get('role_id') branch_id = request.session.get('branch_id') has_access, error_message = check_user_access(role_id, 'purchase_return', "read") fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) if not has_access: return JsonResponse({ 'message': 'warning', 'error_message': 'You do not have permission to view the purchase return details' }) po_id = request.POST.get('po_id') data = list(selectList(child_purchase_return_table, {'tm_return_id': po_id,'current_fy': financial_year}).values()) material_ids = ( child_purchase_return_table.objects .filter( tm_return_id=po_id, status=1, current_fy=financial_year ) .values_list('inward_id', flat=True) .distinct() ) print(list(material_ids)) formatted = [] pu_ids = set() # ✅ collect distinct pu_id for index, item in enumerate(data): formatted.append({ 'id': index + 1, 'action': ' \ '.format(item['id'], item['id']), 'subcategory_id': item['subcategory_id'] if item['subcategory_id'] else '-', 'subcategory_name': getItemNameById(sub_category_table, item['subcategory_id']) if item['subcategory_id'] else '-', 'brand_id': item['brand_id'] if item['brand_id'] else '-', 'brand_name': getItemNameById(brand_table, item['brand_id']) if item['brand_id'] else '-', 'model_id': item['model_id'] if item['model_id'] else '-', 'model_name': getItemNameById(model_table, item['model_id']) if item['model_id'] else '-', 'variant_id': item['variant_id'] if item['variant_id'] else '-', 'variant_name': getItemNameById(variant_table, item['variant_id']) if item['variant_id'] else '-', 'color_id': item['color_id'] if item['color_id'] else '-', 'color_name': getItemNameById(color_table, item['color_id']) if item['color_id'] else '-', 'amount': (item['amount']) if item['amount'] else '-', 'qty': item['quantity'] if item['quantity'] else '-', 'fsp': item['fsp'] if item['fsp'] else '-', 'wsp': item['wsp'] if item['wsp'] else '-', 'mop': item['mop'] if item['mop'] else '-', 'bop': item['bop'] if item['bop'] else '-', 'mrp': item['mrp'] if item['mrp'] else '-', 'db_price': item['db_price'] if item['db_price'] else '-', 'ean': item['ean'] if item['ean'] else '-', 'hsn': item['hsn_code'] if item['hsn_code'] else '-', 'imei_no': item['imei_no'] if item['imei_no'] else '-', 'inward_id': item['inward_id'] if item['inward_id'] else '-', 'rate': item['rate'] if item['rate'] else '-', 'tax_percent': item['tax_percent'] if item['tax_percent'] else '-', 'tax_amount': item['tax_amount'] if item['tax_amount'] else '-', 'tax_sgst': item['tax_sgst'] if item['tax_sgst'] else '-', 'tax_cgst': item['tax_cgst'] if item['tax_cgst'] else '-', 'tax_igst': item['tax_igst'] if item['tax_igst'] else '-', }) return JsonResponse({ 'data': formatted, 'pu_ids': list(material_ids) # ✅ send distinct PU IDs }) def delete_tm_purchase_return(request): if request.method != 'POST': return JsonResponse({'message': 'Invalid request method'}) data_id = request.POST.get('id') role_id = request.session.get('role_id') # ✅ Check user access has_access, error_message = check_user_access(role_id, 'purchase_return', "delete") if not has_access: return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to delete purchase details' }) try: action = request.POST.get('action', 'delete') # ✅ Check if any sales return exists for this purchase return # Note: The existing check below looks potentially buggy as it compares # Purchase Return ID with Sales Return ID (tm_return_id). # Keeping it but using correct role logic if needed. if action == 'delete': linked_sales_returns = child_sales_return_table.objects.filter( tm_return_id=data_id, status=1, is_active=1 ).exists() if linked_sales_returns: return JsonResponse({ 'message': 'error', 'error_message': 'Cannot delete. Sales return exists against this purchase return.' }) # ✅ Soft-delete the purchase return and its child entries purchase_return_table.objects.filter(id=data_id).update(status=0, is_active=0) child_purchase_return_table.objects.filter(tm_return_id=data_id).update(status=0, is_active=0) return JsonResponse({'message': 'yes'}) elif action == 'reverse': # ✅ Reverse the purchase return status purchase_return_table.objects.filter(id=data_id).update(pr_status='reversed') return JsonResponse({'message': 'reversed'}) else: return JsonResponse({'message': 'error', 'error_message': 'Invalid action'}) except Exception as e: return JsonResponse({'message': 'exception', 'error': str(e)}) def get_inward_item_details(request): branch_id = request.session.get('branch_id') edit_id = request.GET.get('edit_id') item_id = request.GET.get('item_id') po_id = request.GET.get('po_id') batch_no = request.GET.get('batch_no') if not item_id or not po_id: return JsonResponse({'success': False, 'message': 'Invalid request'}, status=400) inward_item = child_purchase_inward_table.objects.filter( tm_pu_id=po_id, item_id=item_id, branch_id=branch_id, batch_no=batch_no, status=1 ).first() if not inward_item: return JsonResponse({'success': False, 'message': 'Item not found'}, status=404) # ✅ Calculate available qty exclude_id = edit_id if edit_id and edit_id != "0" else None available_qty = get_available_qty(item_id, batch_no, po_id, branch_id, exclude_return_id=exclude_id) # ✅ Get UOM name uom_obj = uom_table.objects.filter(id=inward_item.uom_id).first() unit_name = uom_obj.name if uom_obj else 'N/A' return JsonResponse({ 'success': True, 'item': { 'item_id': inward_item.item_id, 'uom_id': inward_item.uom_id, 'unit_name': unit_name, 'rate': inward_item.rate, 'mrp': inward_item.mrp, 'inward_qty': inward_item.quantity, 'return_qty': (inward_item.quantity - available_qty), # already returned 'quantity': available_qty, # available to return 'amount': inward_item.amount, 'batch_no': inward_item.batch_no, 'po_id': inward_item.tm_pu_id, } }) def get_available_qty(item_id, batch_no, po_id, branch_id, exclude_return_id=None): """ Calculate available qty for a given item & PO in a branch. Excludes a specific return row if editing. """ # 🔹 Total inward qty for this batch inward_qty = child_purchase_inward_table.objects.filter( tm_pu_id=po_id, item_id=item_id, branch_id=branch_id, batch_no=batch_no, status=1 ).aggregate(total=Sum('quantity'))['total'] or 0 # 🔹 Total returned qty for this batch return_qs = child_purchase_return_table.objects.filter( pu_id=po_id, item_id=item_id, branch_id=branch_id, batch_no=batch_no, status=1, tm_return_id__in=purchase_return_table.objects.exclude(pr_status__iexact='reversed').values_list('id', flat=True) ) if exclude_return_id: return_qs = return_qs.exclude(id=exclude_return_id) return_qty = return_qs.aggregate(total=Sum('quantity'))['total'] or 0 # 🔹 Net available qty return inward_qty - return_qty from django.http import JsonResponse from django.http import JsonResponse def get_inward_item_by_sku(request): branch_id = request.session.get('branch_id') sku = request.GET.get('sku') po_id = request.GET.get('po_id') if not sku or not po_id: return JsonResponse({'success': False, 'message': 'Invalid request'}, status=400) # Step 1: Get inward items for the PO and branch inward_items = child_purchase_inward_table.objects.filter( tm_pu_id=po_id, branch_id=branch_id, status=1 ).select_related() # optional if you want related lookups if not inward_items.exists(): return JsonResponse({'success': False, 'message': 'No items found in PO'}, status=404) # Step 2: Get the item info item_ids = inward_items.values_list("item_id", flat=True).distinct() items = item_table.objects.filter(id__in=item_ids, status=1).values("id", "name", "sku") if not items.exists(): return JsonResponse({'success': False, 'message': 'No valid items found'}, status=404) # Step 3: Prepare the response with batch_no from child table item_list = [] selected_item_id = None batch_mapping = {} # item_id -> batch_no (or list if multiple batches) for ci in inward_items: # map item_id -> batch_no (store latest batch if multiple) batch_mapping[ci.item_id] = ci.batch_no for obj in items: item_id = obj["id"] item_data = { 'item_id': item_id, 'item_name': obj["name"], 'sku': obj["sku"], 'batch_no': batch_mapping.get(item_id) # add batch_no here } item_list.append(item_data) if obj["sku"] == sku: selected_item_id = item_id if selected_item_id is None: return JsonResponse({'success': False, 'message': f'SKU {sku} not found in this PO'}, status=404) return JsonResponse({ 'success': True, 'items': item_list, 'selected_item_id': selected_item_id }) from django.db.models import Sum from django.utils import timezone from django.http import JsonResponse def delete_tx_purchase_item(request): if request.method != 'POST': return JsonResponse({'success': False, 'message': 'Invalid request'}) try: tx_id = request.POST.get('tx_id') branch_id = request.session.get('branch_id') user_id = request.session.get('user_id') # ✅ CHILD ROW item_row = child_purchase_return_table.objects.filter( id=tx_id, status=1 ).first() if not item_row: return JsonResponse({ 'success': False, 'message': 'Item not found or already deleted.' }) # ✅ TM PURCHASE RETURN MASTER tm_obj = purchase_return_table.objects.filter( id=item_row.tm_return_id, status=1 ).first() if not tm_obj: return JsonResponse({ 'success': False, 'message': 'Purchase return master not found.' }) # 🛑 LINKED SALES RETURN CHECK return_exists = child_sales_return_table.objects.filter( return_id=item_row.tm_return_id, item_id=item_row.item_id, status=1, branch_id=branch_id, batch_no=item_row.batch_no ).exists() if return_exists: return JsonResponse({ 'success': False, 'message': 'Sales return exists for this item. Cannot delete.' }) # ✅ RECALCULATE TM TOTALS (EXCLUDING THIS TX) totals = child_purchase_return_table.objects.filter( tm_return_id=item_row.tm_return_id, status=1 ).exclude(id=tx_id).aggregate( total_qty=Sum('quantity'), total_amt=Sum('total_amount') ) total_qty = totals['total_qty'] or 0 total_amt = totals['total_amt'] or 0 cash = tm_obj.cash or 0 bank = tm_obj.bank or 0 new_balance = round(total_amt - (cash + bank), 2) # 🛑 BALANCE SAFETY CHECK if round(tm_obj.balance or 0, 2) != new_balance: return JsonResponse({ 'success': False, 'message': 'Balance mismatch. Please adjust Cash/Bank before deleting.' }) # ✅ SOFT DELETE CHILD item_row.status = 0 item_row.is_active = 0 item_row.updated_on = timezone.now() item_row.updated_by = user_id item_row.save(update_fields=[ 'status', 'is_active', 'updated_on', 'updated_by' ]) # ✅ UPDATE TM PURCHASE RETURN tm_obj.total_quantity = total_qty tm_obj.total_amount = total_amt tm_obj.balance = new_balance tm_obj.updated_on = timezone.now() tm_obj.updated_by = user_id tm_obj.save(update_fields=[ 'total_quantity', 'total_amount', 'balance', 'updated_on', 'updated_by' ]) return JsonResponse({ 'success': True, 'message': 'Purchase return item deleted and totals updated successfully.' }) except Exception as e: return JsonResponse({ 'success': False, 'message': str(e) }) #```````````````````````````````````````````````````````````**STOCK SUMMARY**```````````````````````````````````````````````````````````````````````````````` from django.http import JsonResponse from django.db.models import Sum from django.http import JsonResponse from django.db.models import Sum def load_imei_for_po(request): po_id = request.GET.get('po_id') branch_id = request.session.get('branch_id') edit_id = request.GET.get('edit_id') try: edit_id = int(edit_id) except (TypeError, ValueError): edit_id = 0 if not po_id: return JsonResponse([], safe=False) # ----------------------------- # Child purchase inward items # ----------------------------- tx_items = child_purchase_inward_table.objects.filter( status=1, branch_id=branch_id, tm_pu_id=po_id ).values_list('imei_no', flat=True) imeis = list(tx_items) if not imeis: return JsonResponse([], safe=False) # ----------------------------- # Purchase Returns (exclude current edit) # ----------------------------- purchase_return_qs = child_purchase_return_table.objects.filter( status=1, branch_id=branch_id, inward_id=po_id, imei_no__in=imeis, tm_return_id__in=purchase_return_table.objects.exclude(pr_status__iexact='reversed').values_list('id', flat=True) ) if edit_id > 0: purchase_return_qs = purchase_return_qs.exclude( tm_return_id=edit_id ) purchase_returns = purchase_return_qs.values('imei_no').annotate( total_returned_qty=Sum('quantity') ) purchase_return_map = { r['imei_no']: r['total_returned_qty'] or 0 for r in purchase_returns } # ----------------------------- # Sales Out # ----------------------------- sold_imeis = set( child_sales_order_table.objects.filter( status=1, branch_id=branch_id, imei_no__in=imeis, tm_sales_id__in=sales_order_table.objects.filter( branch_id=branch_id, status=1 ).values_list('id', flat=True) ).values_list('imei_no', flat=True) ) # ----------------------------- # Sales Returns # ----------------------------- sales_return_qs = child_sales_return_table.objects.filter( status=1, branch_id=branch_id, imei_no__in=imeis, tm_return_id__in=sales_return_table.objects.filter( branch_id=branch_id, status=1 ).values_list('id', flat=True) ) if edit_id > 0: sales_return_qs = sales_return_qs.exclude( tm_return_id=edit_id ) returned_sales_imeis = set( sales_return_qs.values_list('imei_no', flat=True) ) # ----------------------------- # Material Out # ----------------------------- material_out_imeis = set( child_material_outward_table.objects.filter( branch_id=branch_id, status=1, material_po_id=po_id, imei_no__in=imeis ).values_list('imei_no', flat=True) ) # ----------------------------- # Material In # ----------------------------- material_in_imeis = set( child_material_inward_table.objects.filter( branch_id=branch_id, status=1, tm_material_id=po_id, imei_no__in=imeis ).values_list('imei_no', flat=True) ) # ----------------------------- # Compute available IMEIs # ----------------------------- available_imeis = [] for imei_no in imeis: purchased_qty = 1 returned_qty = purchase_return_map.get(imei_no, 0) sold_qty = 1 if imei_no in sold_imeis else 0 sales_return_qty = 1 if imei_no in returned_sales_imeis else 0 material_out_qty = 1 if imei_no in material_out_imeis else 0 material_in_qty = 1 if imei_no in material_in_imeis else 0 available = ( purchased_qty - returned_qty - sold_qty + sales_return_qty - material_out_qty + material_in_qty ) if available > 0: available_imeis.append(imei_no) # ----------------------------- # Return for dropdown # ----------------------------- data = [{'id': i, 'text': i} for i in available_imeis] return JsonResponse(data, safe=False) def inward_imei_details(request): imei_no = request.GET.get('imei_no') branch_id = request.session.get('branch_id') if not imei_no: return JsonResponse({'error': 'IMEI required'}, status=400) # Get child purchase inward record item = child_purchase_inward_table.objects.filter( imei_no=imei_no, branch_id=branch_id, status=1 ).values( 'tm_pu_id', 'subcategory_id', 'brand_id', 'model_id', 'variant_id', 'color_id', 'amount', 'db_price', 'tax_cgst', 'tax_sgst', 'tax_igst', 'rate', 'mop', 'bop', 'fsp', 'wsp', 'mrp', 'ean', 'tax_percent', 'hsn_code' ).first() if not item: return JsonResponse({'error': 'Item not found'}, status=404) # Get purchase inward details pu_no = '' supplier_mismatch = False if item.get('tm_pu_id'): pu_record = purchase_inward_table.objects.filter(id=item['tm_pu_id']).values('pu_no', 'supplier_id').first() if pu_record: pu_no = pu_record['pu_no'] # Validate supplier if provided supplier_id = request.GET.get('supplier_id') if supplier_id and str(pu_record['supplier_id']) != str(supplier_id): supplier_mismatch = True if supplier_mismatch: return JsonResponse({'error': 'IMEI does not belong to the selected supplier'}, status=400) # Fetch names from respective tables subcategory_name = '' brand_name = '' model_name = '' variant_name = '' color_name = '' if item.get('subcategory_id'): subcat = sub_category_table.objects.filter(id=item['subcategory_id']).values('name').first() subcategory_name = subcat['name'] if subcat else '' if item.get('brand_id'): brand = brand_table.objects.filter(id=item['brand_id']).values('name').first() brand_name = brand['name'] if brand else '' if item.get('model_id'): model = model_table.objects.filter(id=item['model_id']).values('name').first() model_name = model['name'] if model else '' if item.get('variant_id'): variant = variant_table.objects.filter(id=item['variant_id']).values('name').first() variant_name = variant['name'] if variant else '' if item.get('color_id'): color = color_table.objects.filter(id=item['color_id']).values('name').first() color_name = color['name'] if color else '' # Prepare response response = { 'tm_pu_id': item.get('tm_pu_id'), 'pu_no': pu_no, 'subcategory_id': item.get('subcategory_id'), 'subcategory_name': subcategory_name, 'brand_id': item.get('brand_id'), 'brand_name': brand_name, 'model_id': item.get('model_id'), 'model_name': model_name, 'variant_id': item.get('variant_id'), 'variant_name': variant_name, 'color_id': item.get('color_id'), 'color_name': color_name, 'amount': format_amount(item.get('amount')), 'rate': format_amount(item.get('rate')), 'tax_cgst': format_amount(item.get('tax_cgst')), 'tax_sgst': format_amount(item.get('tax_sgst')), 'tax_igst': format_amount(item.get('tax_igst')), 'mop': format_amount(item.get('mop')), 'bop': format_amount(item.get('bop')), 'fsp': format_amount(item.get('fsp')), 'wsp': format_amount(item.get('wsp')), 'mrp': format_amount(item.get('mrp')), 'db_price': format_amount(item.get('db_price')), 'ean': item.get('ean'), 'tax_percent':item.get('tax_percent'), 'hsn_code': item.get('hsn_code'), } return JsonResponse(response) def check_return_imei_exists(request): imeis = request.POST.getlist('imeis[]') or request.POST.getlist('imeis') edit_id_str = request.POST.get('edit_id') # This is now "101,102,103" branch_id = request.session.get('branch_id') imeis = [i.strip().upper() for i in imeis if i.strip()] if not imeis: return JsonResponse({"exists": False, "duplicate_imeis": []}) qs = child_purchase_return_table.objects.filter(imei_no__in=imeis, status=1, branch_id=branch_id) # 🔥 Exclude all IDs belonging to the group being edited if edit_id_str and edit_id_str != '0': qs = qs.exclude(tm_return_id=edit_id_str) duplicate_imeis = list(qs.values_list('imei_no', flat=True)) return JsonResponse({ "exists": bool(duplicate_imeis), "duplicate_imeis": duplicate_imeis }) def get_supplier_imei_reference(request): """ Fetch IMEI reference table for purchase return. Shows available IMEIs for a supplier with exclusions. """ supplier_id = request.GET.get('supplier_id') branch_id = request.session.get('branch_id') edit_id = request.GET.get('edit_id') try: edit_id = int(edit_id) if edit_id else 0 except (TypeError, ValueError): edit_id = 0 if not supplier_id: return JsonResponse([], safe=False) # ----------------------------- # Fetch all IMEIs from purchase inward for this supplier # ----------------------------- inward_items = child_purchase_inward_table.objects.filter( status=1, branch_id=branch_id, supplier_id=supplier_id ).exclude( imei_no__isnull=True ).exclude( imei_no__exact='' ).values( 'imei_no', 'tm_pu_id', 'subcategory_id', 'brand_id', 'model_id', 'variant_id', 'color_id' ) if not inward_items: return JsonResponse([], safe=False) imei_list = [item['imei_no'] for item in inward_items] # Create lookup map for IMEI details imei_details_map = { item['imei_no']: { 'tm_pu_id': item['tm_pu_id'], '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'] } for item in inward_items } # ----------------------------- # EXCLUSION 1: Already in Purchase Return (except current edit) # ----------------------------- purchase_return_qs = child_purchase_return_table.objects.filter( status=1, branch_id=branch_id, imei_no__in=imei_list ) if edit_id > 0: purchase_return_qs = purchase_return_qs.exclude(tm_return_id=edit_id) excluded_purchase_return = set( purchase_return_qs.values_list('imei_no', flat=True) ) # ----------------------------- # EXCLUSION 2: Sold IMEIs # ----------------------------- sold_imeis = set( child_sales_order_table.objects.filter( status=1, branch_id=branch_id, imei_no__in=imei_list, tm_sales_id__in=sales_order_table.objects.filter( branch_id=branch_id, status=1 ).values_list('id', flat=True) ).values_list('imei_no', flat=True) ) # ----------------------------- # EXCLUSION 3: In Sales Return # ----------------------------- sales_return_imeis = set( child_sales_return_table.objects.filter( status=1, branch_id=branch_id, imei_no__in=imei_list, tm_return_id__in=sales_return_table.objects.filter( branch_id=branch_id, status=1 ).values_list('id', flat=True) ).values_list('imei_no', flat=True) ) # ----------------------------- # EXCLUSION 4: In Transfer Outward # ----------------------------- transfer_out_imeis = set( child_material_outward_table.objects.filter( status=1, branch_id=branch_id, imei_no__in=imei_list ).values_list('imei_no', flat=True) ) # ----------------------------- # EXCLUSION 5: In Transfer Inward # ----------------------------- transfer_in_imeis = set( child_material_inward_table.objects.filter( status=1, branch_id=branch_id, imei_no__in=imei_list ).values_list('imei_no', flat=True) ) # ----------------------------- # Compute available IMEIs # ----------------------------- excluded_imeis = ( excluded_purchase_return | sold_imeis | sales_return_imeis | transfer_out_imeis | transfer_in_imeis ) available_imeis = [imei for imei in imei_list if imei not in excluded_imeis] # Force include IMEIs from the current edit session if edit_id > 0: current_session_imeis = set( child_purchase_return_table.objects.filter( tm_return_id=edit_id, status=1, branch_id=branch_id ).values_list('imei_no', flat=True) ) # Add them only if they belong to this supplier (present in imei_list) for imei in current_session_imeis: if imei in imei_list and imei not in available_imeis: available_imeis.append(imei) if not available_imeis: return JsonResponse([], safe=False) # ----------------------------- # Build response with IMEI details # ----------------------------- result = [] # Get purchase inward numbers pu_map = {} pu_records = purchase_inward_table.objects.filter( id__in=[imei_details_map[imei]['tm_pu_id'] for imei in available_imeis] ).values('id', 'pu_no') for pu in pu_records: pu_map[pu['id']] = pu['pu_no'] for imei_no in available_imeis: details = imei_details_map.get(imei_no) if not details: continue # Build SKU text from item attributes sku_parts = [] if details['subcategory_id']: subcat = sub_category_table.objects.filter(id=details['subcategory_id']).values('name').first() if subcat: sku_parts.append(subcat['name']) if details['brand_id']: brand = brand_table.objects.filter(id=details['brand_id']).values('name').first() if brand: sku_parts.append(brand['name']) if details['model_id']: model = model_table.objects.filter(id=details['model_id']).values('name').first() if model: sku_parts.append(model['name']) if details['variant_id']: variant = variant_table.objects.filter(id=details['variant_id']).values('name').first() if variant: sku_parts.append(variant['name']) if details['color_id']: color = color_table.objects.filter(id=details['color_id']).values('name').first() if color: sku_parts.append(color['name']) item = select_row(item_table, {'sub_category_id': details['subcategory_id'], 'brand_id': details['brand_id'], 'model_id': details['model_id'], 'variant_id': details['variant_id'], 'color_id': details['color_id']}) sku_text = item.sku_text pu_no = pu_map.get(details['tm_pu_id'], 'N/A') result.append({ 'imei_no': imei_no, 'sku_text': sku_text, 'pu_no': pu_no, 'tm_pu_id': details['tm_pu_id'] }) return JsonResponse(result, safe=False)