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 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 sales.models import * from scheme.models import * # ********************************************************************************************************************************* def purchase_inward(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') 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' and is_ho == 1: company = select_row(company_table, {'id': 1}) category = selectList(category_table, order_by='name') if is_ho == 1: supplier = selectList(supplier_table, order_by='name' ) else: supplier = selectList(supplier_table, Q(is_global=1), order_by='name' ) branch = selectList(branch_table, order_by='name').exclude(id=branch_id) return render(request, 'purchase_inward/details.html', {'company': company,'category':category,'supplier':supplier,'branch':branch,'decoded_id':decoded_id}) elif user_type == 'wholesale': branch = selectList(branch_table, order_by='name').exclude(id=branch_id) return render(request, 'internal_wholesale/purchase/inward/details.html', { 'branch': branch, }) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def load_purchase_orders(request): supplier_id = request.GET.get('supplier_id') edit_id = int(request.GET.get('edit_id') or 0) branch_id = request.session.get('branch_id') valid_pos = [] # Base Query query = Q(status=1, branch_id=branch_id) if supplier_id: query &= Q(supplier_id=supplier_id) all_pos = purchase_order_table.objects.filter(query) for po in all_pos: po_items = child_purchase_order_table.objects.filter( tm_po_id=po.id, status=1, branch_id=branch_id ) include_po = False for item in po_items: inwarded_qty_qs = child_purchase_inward_table.objects.filter( po_id=po.id, ean=item.ean, 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, status=1, ) # Exclude current edit entry from inwarded qty if edit_id != 0: inwarded_qty_qs = inwarded_qty_qs.exclude(id=edit_id) inwarded_qty = inwarded_qty_qs.aggregate(total_qty=Sum('quantity'))['total_qty'] or 0 # If pending qty exists → include PO if inwarded_qty < item.quantity: include_po = True break if include_po: valid_pos.append({ 'id': f"{po.id}", 'po_no': po.po_no, }) return JsonResponse(valid_pos, safe=False) def get_po_details_by_pu_ids(pu_ids): """ pu_ids -> purchase_inward_table.id child table -> tm_pu_id (FK reference) returns -> { pu_id: { 'po_no': 'PO001, PO002', 'po_date': '01-01-2026, 05-01-2026' } } """ # 1️⃣ PU → PO mapping from child table child_rows = ( child_purchase_inward_table.objects .filter(tm_pu_id__in=pu_ids, po_id__isnull=False) .values('tm_pu_id', 'po_id') .distinct() ) po_ids = {row['po_id'] for row in child_rows} if not po_ids: return {} # 2️⃣ Fetch PO details po_map = { po.id: { 'po_no': po.po_no, 'po_date': po.po_date } for po in purchase_order_table.objects.filter(id__in=po_ids) } # 3️⃣ Build PU → PO details pu_po_map = {} for row in child_rows: pu_id = row['tm_pu_id'] po = po_map.get(row['po_id']) if not po: continue pu_po_map.setdefault(pu_id, { 'po_no': set(), 'po_date': set() }) pu_po_map[pu_id]['po_no'].add(po['po_no']) if po['po_date']: pu_po_map[pu_id]['po_date'].add( format_date_month_year(po['po_date']) ) # 4️⃣ Convert sets → strings return { pu_id: { 'po_no': ', '.join(sorted(v['po_no'])), 'po_date': ', '.join(sorted(v['po_date'])) } for pu_id, v in pu_po_map.items() } def ajax_inward_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_inward', "read" ) if not has_access: return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to view purchase inward details' }) supplier_id = request.POST.get('supplier') from_date = request.POST.get('from_date') to_date = request.POST.get('to_date') pu_id = request.POST.get('pu_id') 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 pu_id: query &= Q(id=pu_id) if from_date and to_date: query &= Q(pu_date__range=[from_date, to_date]) if keyword: query &= Q(pu_no__icontains=keyword) # 🔹 Fetch parent inward data data = list( selectList(purchase_inward_table, query).values() ) # 🔹 Collect PU IDs ONCE pu_ids = [item['id'] for item in data] # 🔹 Fetch PO numbers ONCE (from child table) po_details_map = get_po_details_by_pu_ids(pu_ids) formatted = [] for index, item in enumerate(data): action_buttons = ( f' ' f'' ) total_tax = item['total_cgst'] + item['total_sgst'] discount = item['total_discount'] + item['bill_discount'] po_details = po_details_map.get(item['id'], {}) formatted.append({ 'id': index + 1, 'action': action_buttons, 'inv_no': item['pu_no'] or '-', 'inv_date': format_date_month_year(item['pu_date']) if item['pu_date'] else '-', 'po_no': po_details.get('po_no', '-'), 'po_date': po_details.get('po_date', '-'), # ✅ HERE 'supplier_no': item['supplier_no'] or '-', 'supplier_date': format_date_month_year(item['supplier_date']) if item['supplier_date'] else '-', 'supplier': getItemNameById( supplier_table, item['supplier_id'] ) if item['supplier_id'] else '-', 'quantity': item['total_quantity'] or '-', 'sub_total': format_amount(item['sub_total']), 'discount': format_amount(discount), 'tax': format_amount(total_tax), 'tds': format_amount(item['total_tds']), 'round_off': format_amount(item['roundoff']), 'grand_total': format_amount(item['grand_total']), 'po_status': format_badge( item['pu_status'], mapping={ 'pending': 'badge text-bg-info', 'accepted': 'badge text-bg-success', 'rejected': 'badge text-bg-danger' }, label_mapping={ 'pending': 'Pending', 'accepted': 'Accepted', 'rejected': 'Rejected' } ), 'remarks': item['remarks'] or '-', 'status': ( 'Active' if item['is_active'] else 'Inactive' ) }) return JsonResponse({'data': formatted}) from django.http import JsonResponse from django.db.models import Sum from datetime import date from django.http import JsonResponse from django.db.models import Sum from decimal import Decimal as D def load_po_items(request): po_ids_str = request.GET.get('po_ids', '') po_ids = comma_separated(po_ids_str) or [] edit_id = int(request.GET.get('edit_id') or 0) items_data = [] all_fully_inwarded = True tx_items = child_purchase_order_table.objects.filter( tm_po_id__in=po_ids, status=1 ) if edit_id: tx_items = tx_items.exclude(id=edit_id) for item in tx_items: inwarded_qty = child_purchase_inward_table.objects.filter( po_id=item.tm_po_id, subcategory_id=item.subcategory_id or 0, ean=item.ean or '', 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, status=1 ).aggregate(total=Sum('quantity'))['total'] or 0 remaining_qty = (item.quantity or 0) - inwarded_qty if remaining_qty <= 0: continue all_fully_inwarded = False price_data = get_latest_price( item.brand_id, item.model_id, item.variant_id ) # Calculate dynamic prices if price_data: fsp_base = D(item.fsp) - D(price_data.get('fsp', 0)) wsp_base = D(item.wsp) - D(price_data.get('wsp', 0)) mop_price = D(item.mop) - D(price_data.get('mop', 0)) bop_price = D(item.bop) - D(price_data.get('bop', 0)) price_id = price_data['id'] else: fsp_base = D(item.fsp or 0) wsp_base = D(item.wsp or 0) mop_price = D(item.mop or 0) bop_price = D(item.bop or 0) price_id = 0 TAX_FACTOR = D('01.18') fsp_price = fsp_base * TAX_FACTOR wsp_price = wsp_base * TAX_FACTOR net_rate = D(item.rate or 0) GST_RATE = D('0.18') HALF_GST = D('0.09') # 1. Calculate ONLY tax tax_amount = (net_rate * GST_RATE).quantize(D('0.01')) # 2. Split tax cgst_amount = (net_rate * HALF_GST).quantize(D('0.01')) sgst_amount = (net_rate * HALF_GST).quantize(D('0.01')) # 3. Final amount (optional) total_amount = (net_rate + tax_amount).quantize(D('0.01')) # 4. Total Amount for the remaining quantity total_amount = net_rate * D(remaining_qty) rate = item.db_price base_rate = rate + (rate * 18 / 100) print('rate:', rate, 'base_rate:', base_rate) items_data.append({ "item_id": 0, "po_id": item.tm_po_id, "ean": item.ean or '', "hsn_code": item.hsn_code or '', "subcategory_id": item.subcategory_id or 0, "subcategory_name": getItemNameById(sub_category_table, item.subcategory_id or 0), "brand_id": item.brand_id, "brand_name": getItemNameById(brand_table, item.brand_id), "model_id": item.model_id, "model_name": getItemNameById(model_table, item.model_id), "variant_id": item.variant_id, "variant_name": getItemNameById(variant_table, item.variant_id), "color_id": item.color_id, "color_name": getItemNameById(color_table, item.color_id), # Pricing Fields "rate": float(item.rate), # Base Rate (Before Tax) "quantity": remaining_qty, "fsp_price": float(fsp_price), "wsp_price": float(wsp_price), "mop_price": float(mop_price), "bop_price": float(bop_price), # Tax Fields "discount_amount": 0, "discount_percent": 0, "net_rate": float(net_rate), # Net Rate (Inclusive) "cgst":0, # 9% "sgst": 0, # 9% "igst": 0, "tax_amount": 0, "amount": 0, # Total for row "db_price": float(item.db_price or 0), "price_id": price_id, "mrp": float(item.mrp or 0), "base_rate": float(base_rate or 0), }) if po_ids and all_fully_inwarded and edit_id == 0: return JsonResponse({ "success": False, "message": "All items for the selected PO(s) are fully inwarded.", "items": [] }) return JsonResponse({"success": True, "items": items_data}) from django.db.models import Sum from django.http import JsonResponse from django.http import JsonResponse def get_po_item_details(request): """ Fetch all active items for given PO(s) directly from child_purchase_order_table. No inward quantity checks; returns raw PO items. """ po_ids_str = request.GET.get('po_ids', '') po_ids = comma_separated(po_ids_str) or [] if not po_ids: return JsonResponse({ "message": "No PO IDs provided.", "items": [], "summary": {} }) items_data = [] total_qty = 0 sub_total = 0 po_items = child_purchase_order_table.objects.filter( tm_po_id__in=po_ids, status=1 ) for item in po_items: # Fetch item and UOM details item_obj = item_table.objects.filter(id=item.item_id).first() uom_obj = uom_table.objects.filter(id=item.uom_id).first() amount = round((item.quantity or 0) * (item.rate or 0), 2) items_data.append({ "item_id": item.item_id, "item_code": item_obj.sku if item_obj else 'N/A', "po_id": item.tm_po_id, "uom_id": item.uom_id, "item_name": item_obj.name if item_obj else 'N/A', "unit_name": uom_obj.name if uom_obj else 'N/A', "rate": item.rate, "quantity": item.quantity or 0, "amount": amount }) total_qty += item.quantity or 0 sub_total += amount roundoff = 0 grand_total = sub_total + roundoff return JsonResponse({ "items": items_data, "summary": { "total_qty": total_qty, "sub_total": round(sub_total, 2), "grand_total": round(grand_total, 2) } }) def purchase_inward_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') if user_type == 'stores': company = select_row(company_table, {'id': 1}) if is_ho == 1: supplier = selectList(supplier_table, order_by='name' ) else: supplier = selectList(supplier_table, Q(is_global=1), order_by='name' ) category = selectList(category_table) branch = selectList(branch_table, order_by='name').exclude(id=branch_id) brand = selectList(brand_table, order_by='name') uom = selectList(uom_table) item = selectList(item_table) employee = employee_list(request) subcategory = selectList(sub_category_table, order_by='name') return render(request, 'purchase_inward/add.html', {'company': company,'supplier':supplier,'branch':branch,'category':category,'brand':brand,'uom':uom, 'item':item,'employee':employee,'subcategory':subcategory}) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def prepare_batches(company_id, branch_id, is_ho, tm_pu_id, items): po_type = 'supplier' """ Batch number logic: 1. First rate of each item shares one common batch. 2. Second rate of each item (if any) shares another batch. 3. Third rate (if any) shares next batch, etc. 4. Sequence increases globally, skipping already used batch numbers. """ branch = select_row(branch_table, {'id':branch_id}) batch_map = {} # Decide prefix if is_ho == 1: prefix = "BM-" existing_batches = child_purchase_inward_table.objects.filter( company_id=company_id, status=1, batch_no__startswith=prefix ).values_list("batch_no", flat=True) elif po_type == "supplier": store_code = str(branch.branch_code).zfill(3) prefix = f"{store_code}/OTH-" existing_batches = child_purchase_inward_table.objects.filter( company_id=company_id, branch_id=branch_id, status=1, batch_no__startswith=prefix ).values_list("batch_no", flat=True) else: return {} # Find max sequence used so far globally existing_batches = child_purchase_inward_table.objects.filter( company_id=company_id, branch_id=branch_id, status=1, batch_no__startswith=prefix ).values_list("batch_no", flat=True) max_num = 0 for b in existing_batches: try: num = int(str(b).split("-")[1]) max_num = max(max_num, num) except: print(f"⚠️ Skipping invalid batch {b}") # Group items by item_id → collect all unique rates item_rates = {} for item in items: item_id = item["item_id"] rate = float(item["rate"]) item_rates.setdefault(item_id, []).append(rate) # Normalize to unique + stable order for item_id in item_rates: item_rates[item_id] = sorted(set(item_rates[item_id])) # Now align by "rate position" rate_position_batches = {} # pos → batch_no for item in items: item_id = item["item_id"] rate = float(item["rate"]) key = (item_id, rate) # find this rate's position for the item pos = item_rates[item_id].index(rate) # 0 = first, 1 = second, etc. if pos not in rate_position_batches: max_num += 1 rate_position_batches[pos] = f"{prefix}{max_num}" batch_map[key] = rate_position_batches[pos] return batch_map from django.db.models import Max from django.db import transaction import json from django.http import JsonResponse from django.utils import timezone def add_purchase_inward(request): if request.method == 'POST': try: 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') is_ho = request.session.get('is_ho') has_access, error_message = check_user_access(role_id, 'purchase_inward', "create") if not has_access: return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to add purchase inward details' }) supplier_date = request.POST.get('supplier_date') invoice_date = request.POST.get('invoice_date') supplier_id = request.POST.get('supplier_id') or 0 remarks = request.POST.get('description') total_qty = request.POST.get('total_quantity') sub_total = request.POST.get('sub_total') total_discount = request.POST.get('total_discount') bill_discount = request.POST.get('bill_discount') total_cgst = request.POST.get('total_tax_cgst') or 0 total_sgst = request.POST.get('total_tax_sgst') or 0 total_tds = request.POST.get('total_tds') or 0 total_amount = request.POST.get('total_amount') round_off = float(request.POST.get('round_off') or 0) grand_total = request.POST.get('grand_total') supplier_no = request.POST.get('supplier_no') or 0 employee_id = request.POST.get('employee_id') or user_id payment = request.POST.get('payment') cash = float(request.POST.get('cash') or 0) bank = float(request.POST.get('bank') or 0) balance = float(request.POST.get('balance') or 0) is_demo = int(request.POST.get('is_demo') or 0) items = json.loads(request.POST.get('items')) # --- Validation --- 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.'}) # Negative check negative_check_fields = ['total_quantity', 'sub_total', 'grand_total', 'cash', 'bank', 'balance'] has_negative, error_message = has_negative_values(request.POST, negative_check_fields) if has_negative: return JsonResponse({'message': 'infos', 'error_message': error_message}) # --- Generate PO No --- generated_po_no = generate_serial_number( model=purchase_inward_table, number_field='pu_no', financial_year_field='current_fy', financial_year=financial_year, branch_id=branch_id ) now = timezone.localtime(timezone.now()) # ------------------------------------------------- # 🔍 BRAND-WISE PURCHASE AMOUNT CALCULATION # ------------------------------------------------- brand_amount_map = {} for item in items: brand_id = int(item.get('brand_id') or 0) amount = float(item.get('amount') or 0) if not brand_id: continue brand_amount_map.setdefault(brand_id, 0) brand_amount_map[brand_id] += amount is_valid, error_message = validate_brand_maintenance_limit( branch_id=branch_id, financial_year=financial_year, tm_id=0, brand_amount_map=brand_amount_map ) if not is_valid: return JsonResponse({ 'message': 'warning', 'error_message': error_message }) with transaction.atomic(): # --- Create Main Purchase Inward --- main_obj = purchase_inward_table.objects.create( company_id=company_id, pu_date=invoice_date, supplier_date=supplier_date, supplier_no=supplier_no, pu_no=generated_po_no, num_series=generate_num_series(purchase_inward_table), current_fy=financial_year, branch_id=branch_id, supplier_id=supplier_id, remarks=remarks, total_quantity=total_qty, sub_total=sub_total, total_discount=total_discount, bill_discount=bill_discount, total_cgst=total_cgst, total_sgst=total_sgst, total_tds=total_tds, total_amount=total_amount, roundoff=round_off, grand_total=grand_total, payment_type=payment, cash=cash, bank=bank, balance=balance, pu_status='pending', is_active=1, status=1, is_demo=is_demo, time=now.strftime('%H:%M:%S'), created_on=now, updated_on=now, created_by=user_id, updated_by=user_id, employee_id=employee_id ) # --- Batch Map --- batch_map = prepare_batches(company_id, branch_id, is_ho, main_obj.id, items) for idx, item in enumerate(items, start=1): item_id = int(item['item_id']) rate = float(item['rate']) total_row_discount = float(item.get('discount_amt') or 0) tax_Amount = float(item.get('tax_amount') or 0) total_amount = float(item.get('amount') or 0) tax_cgst = float(item.get('tax_cgst') or 0) tax_sgst = float(item.get('tax_sgst') or 0) imei_list = item.get('imeis', []) divisor = len(imei_list) if len(imei_list) > 0 else 1 unit_discount = total_row_discount unit_tax = tax_Amount / divisor unit_amount = total_amount / divisor unit_cgst = tax_cgst / divisor unit_sgst = tax_sgst / divisor for imei_val in imei_list: imei_clean = str(imei_val).strip() if child_purchase_inward_table.objects.filter( imei_no=imei_clean, status=1 ).exists(): raise IntegrityError(f"Duplicate IMEI detected: {imei_clean}") batch_key = (item_id, rate) batch_no = batch_map.get(batch_key) child_purchase_inward_table.objects.create( company_id=company_id, current_fy=financial_year, batch_no=batch_no, tm_pu_id=main_obj.id, supplier_id=supplier_id, branch_id=branch_id, item_id=item_id, imei_no=imei_clean, ean=item.get('ean', ''), base_rate=float(item.get('base_rate') or 0), po_id=int(item.get('po_id') or 0), subcategory_id=int(item.get('subcategory_id') or 0), brand_id=int(item.get('brand_id') or 0), model_id=int(item.get('model_id') or 0), variant_id=int(item.get('variant_id') or 0), color_id=int(item.get('color_id') or 0), hsn_code=item.get('hsn_code', ''), rate=rate, net_rate=item.get('net_rate'), quantity=1, discount_percentage=float(item.get('discount_percent') or 0), discount_amount=round(unit_discount, 2), tax_percent = item.get('tax_percent') or 18, tax_amount=round(unit_tax, 2), tax_cgst=round(unit_cgst, 2), tax_sgst=round(unit_sgst, 2), tax_igst=0, amount=round(unit_amount, 2), is_active=1, status=1, is_demo=is_demo, created_on=now, updated_on=now, created_by=user_id, updated_by=user_id, mop=float(item.get('mop_price') or 0), bop=float(item.get('bop_price') or 0), wsp=float(item.get('wsp_price') or 0), fsp=float(item.get('fsp_price') or 0), db_price=float(item.get('db_price') or 0), mrp=float(item.get('mrp_price') or 0), price_list_id=int(item.get('price_list_id') or 0) ) return JsonResponse({'message': 'success'}) except IntegrityError as e: return JsonResponse({'message': 'exception', 'error': f'Database error: {e}'}, status=500) except Exception as e: return JsonResponse({'message': 'exception', 'error': f'Unexpected error: {e}'}, status=500) from django.db.models import Sum from decimal import Decimal from masters.models import * from django.db.models import Sum from decimal import Decimal from django.db.models import Sum from decimal import Decimal from decimal import Decimal, ROUND_HALF_UP from django.db.models import Sum def validate_brand_maintenance_limit( branch_id, financial_year, tm_id, # 0 for add, ID for edit brand_amount_map ): """ brand_amount_map = { brand_id: purchase_amount_for_that_brand } """ for brand_id, current_amount in brand_amount_map.items(): brand = brand_table.objects.filter( id=brand_id, status=1 ).only('maintenance_value').first() # If no brand or no maintenance limit → allow if not brand or not brand.maintenance_value: continue qs = child_purchase_inward_table.objects.filter( branch_id=branch_id, current_fy=financial_year, brand_id=brand_id, status=1, is_active=1 ) # ✅ Exclude current transaction while editing if tm_id: qs = qs.exclude(tm_pu_id=tm_id) existing_total = qs.aggregate( total=Sum('amount') )['total'] or 0 projected_total = Decimal(str(existing_total)) + Decimal(str(current_amount)) maintenance_limit = Decimal(str(brand.maintenance_value)) # 🔹 Format to 2 decimal places existing_total = Decimal(str(existing_total)).quantize(Decimal('0.00'), rounding=ROUND_HALF_UP) current_amount = Decimal(str(current_amount)).quantize(Decimal('0.00'), rounding=ROUND_HALF_UP) projected_total = projected_total.quantize(Decimal('0.00'), rounding=ROUND_HALF_UP) maintenance_limit = maintenance_limit.quantize(Decimal('0.00'), rounding=ROUND_HALF_UP) if projected_total > maintenance_limit: return False, ( f"Purchase limit exceeded for brand.\n" f"Limit: {maintenance_limit}\n" f"Used: {existing_total}\n" f"Attempted: {current_amount}" ) return True, None from django.db.models import Q def purchase_inward_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') decoded_id = decode_base64_id(encoded_id) if not decoded_id: return HttpResponse("ID parameter is missing") company = select_row(company_table, {'id': 1}) purchase_order = select_row(purchase_inward_table, {'id': decoded_id}) # base service list for the branch # rest of your lists if is_ho == 1: supplier = selectList(supplier_table, order_by='name') else: supplier = selectList(supplier_table, Q(is_global=1), order_by='name') branch = selectList(branch_table, order_by='name').exclude(id=branch_id) item = selectList(item_table) employee = employee_list(request) subcategory = selectList(sub_category_table, order_by='name') brand = selectList(brand_table, order_by='name') uom = selectList(uom_table) return render(request, 'purchase_inward/edit.html', { 'company': company, 'purchase': purchase_order, 'id': decoded_id, 'employee': employee, 'subcategory': subcategory, 'brand': brand, 'uom': uom, 'supplier': supplier, 'branch': branch, 'item': item, }) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") from django.db import connection from django.http import JsonResponse def ajax_pu_edit(request): fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) role_id = request.session.get('role_id') has_access, error_message = check_user_access(role_id, 'purchase_inward', "read") if not has_access: return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to view details' }) po_id = request.POST.get('po_id') print(po_id) if not po_id: return JsonResponse({'data': []}) query = """ SELECT item_id, ean, subcategory_id, hsn_code, brand_id, model_id, variant_id, color_id, rate, mrp, net_rate, db_price, discount_percentage, mop, bop, wsp, fsp, price_list_id, po_id, tm_pu_id, tax_percent, base_rate, SUM(quantity) as total_qty, SUM(amount) as total_amount, discount_amount as discount_amount, SUM(tax_amount) as tax_amount, SUM(tax_sgst) as tax_sgst, SUM(tax_cgst) as tax_cgst, SUM(tax_igst) as tax_igst, SUM(amount) as amount, GROUP_CONCAT(imei_no SEPARATOR ',') as imei_list, GROUP_CONCAT(id SEPARATOR ',') as tx_ids FROM tx_purchase_inward WHERE tm_pu_id = %s AND current_fy = %s AND status = 1 GROUP BY ean, subcategory_id, brand_id, model_id, variant_id, color_id, rate, db_price, mop, bop, price_list_id """ formatted = [] with connection.cursor() as cursor: cursor.execute(query, [po_id, financial_year]) columns = [col[0] for col in cursor.description] rows = [dict(zip(columns, row)) for row in cursor.fetchall()] # 🔹 Fetch PO numbers ONCE (from child table) po_ids = child_purchase_inward_table.objects.filter( tm_pu_id=po_id, status=1 ).values_list('po_id', flat=True).distinct() for index, item in enumerate(rows): formatted.append({ 'id': index + 1, 'item_id': item['item_id'] or 0, 'item': item['ean'], 'hsn_code': item['hsn_code'] or '-', 'po_id': item['po_id'] or 0, 'subcategory_id': item['subcategory_id'] or 0, 'subcategory_name': getItemNameById(sub_category_table, item['subcategory_id']) or '-', 'brand_id': item['brand_id'] or 0, 'brand_name': getItemNameById(brand_table, item['brand_id']) or '-', 'model_id': item['model_id'] or 0, 'model_name': getItemNameById(model_table, item['model_id']) or '-', 'variant_id': item['variant_id'] or 0, 'variant_name': getItemNameById(variant_table, item['variant_id']) or '-', 'color_id': item['color_id'] or 0, 'color_name': getItemNameById(color_table, item['color_id']) or '-', 'imei_list': item['imei_list'].split(',') if item['imei_list'] else [], 'qty': int(item['total_qty']) if item['total_qty'] else 0, # Send raw floats/decimals, no string formatting here 'rate': float(item['rate'] or 0), 'fsp_price': float(item['fsp'] or 0), 'wsp_price': float(item['wsp'] or 0), 'mop_price': float(item['mop'] or 0), 'bop_price': float(item['bop'] or 0), 'discount_amount': float(item['discount_amount'] or 0), 'discount_percent': float(item['discount_percentage'] or 0), 'net_rate': float(item['net_rate'] or 0), 'tax_cgst': float(item['tax_cgst'] or 0), 'tax_sgst': float(item['tax_sgst'] or 0), 'tax_igst': float(item['tax_igst'] or 0), 'amount': float(item['total_amount'] or 0), 'db_price': float(item['db_price'] or 0), 'tax_amount': float(item['tax_amount'] or 0), 'mrp': float(item['mrp'] or 0), 'tax_percent': float(item['tax_percent'] or 0), 'price_list_id': item['price_list_id'] or 0, 'tx_id': item['tx_ids'] or 0, 'tm_id': item['tm_pu_id'] or 0, 'base_rate': float(item['base_rate'] or 0), }) return JsonResponse({'data': formatted, 'po_id': list(po_ids)}) def edit_purchase_inward(request): if request.headers.get('x-requested-with') == 'XMLHttpRequest' and request.method == "POST": data = child_purchase_inward_table.objects.filter(id=request.POST.get('id')) return JsonResponse(data.values()[0]) def prepare_batches_for_edit(company_id, branch_id, is_ho, po_type, tm_id, items): """ Handle batch assignment logic when updating a purchase (edit screen). Rules: 1. If same item_id + same rate already exists in this tm_id → reuse that batch. 2. If same item_id but new rate → assign new batch. 3. If completely new item_id → assign new batch. 4. Ensure new batch numbers are unique and sequential globally. """ branch = select_row(branch_table, {'id': branch_id}) batch_map = {} # Prefix logic if is_ho == 1: prefix = "BM-" elif po_type == "supplier": store_code = str(branch.branch_code).zfill(3) prefix = f"{store_code}/OTH-" else: return {} # Collect all existing batches globally (for unique numbering) existing_batches = child_purchase_inward_table.objects.filter( company_id=company_id, branch_id=branch_id, status=1, batch_no__startswith=prefix ).values_list("batch_no", flat=True) max_num = 0 for b in existing_batches: try: num = int(str(b).split("-")[1]) max_num = max(max_num, num) except: print(f"⚠️ Skipping invalid batch {b}") # Collect existing rows for this tm_id existing_rows = child_purchase_inward_table.objects.filter( company_id=company_id, branch_id=branch_id, tm_pu_id=tm_id, status=1 ) existing_map = {} # (item_id, rate) → batch_no item_batches = {} # item_id → list of batch_no for row in existing_rows: existing_map[(row.item_id, float(row.rate))] = row.batch_no item_batches.setdefault(row.item_id, []).append(row.batch_no) # Now handle new items for item in items: item_id = item["item_id"] rate = float(item["rate"]) key = (item_id, rate) if key in existing_map: # Case 1: exact match found batch_map[key] = existing_map[key] elif item_id in item_batches: # Case 2: same item different rate → check if already exists matching = [bn for (iid, r), bn in existing_map.items() if iid == item_id and r == rate] if matching: batch_map[key] = matching[0] else: max_num += 1 new_batch = f"{prefix}{max_num}" batch_map[key] = new_batch else: # Case 3: completely new item if existing_map: # Reuse the first batch_no already linked to this tm_id first_existing_batch = next(iter(existing_map.values())) batch_map[key] = first_existing_batch else: # No existing rows at all → must create new batch max_num += 1 new_batch = f"{prefix}{max_num}" batch_map[key] = new_batch return batch_map from django.http import JsonResponse from django.utils import timezone import json import json from django.http import JsonResponse from django.utils import timezone from django.db import transaction import json from django.http import JsonResponse from django.utils import timezone from django.db import transaction @transaction.atomic def update_purchase_inward(request): if request.method != 'POST': return JsonResponse({'success': False, 'message': 'Invalid request'}) try: # 1. Environment & Request Setup user_id = request.session.get('user_id') company_id = request.session.get('company_id') branch_id = request.session.get('branch_id') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) now = timezone.localtime(timezone.now()) total_qty = request.POST.get('total_quantity') total_amount = request.POST.get('total_amount') print('$$', total_amount) total_discount = request.POST.get('total_discount') bill_discount = request.POST.get('bill_discount') total_cgst = request.POST.get('total_tax_cgst') total_sgst = request.POST.get('total_tax_sgst') sub_total = request.POST.get('sub_total') round_off = float(request.POST.get('round_off') or 0) grand_total = request.POST.get('grand_total') invoice_date = request.POST.get('invoice_date') supplier_date = request.POST.get('supplier_date') total_tds = request.POST.get('total_tds') or 0 balance = float(request.POST.get('balance') or 0) is_demo = int(request.POST.get('is_demo') or 0) supplier_date = request.POST.get('supplier_date') data = request.POST items_data = json.loads(data.get('items', '[]')) tm_id_parent = int(data.get('tm_id') or 0) # Get parent record purchase = select_row(purchase_inward_table, {'id': tm_id_parent}) child_purchase_inward_table.objects.filter( tm_pu_id=tm_id_parent, status=1 ).update(status=0, updated_on=now, updated_by=user_id) brand_amount_map = {} for item in items_data: brand_id = int(item.get('brand_id') or 0) amount = float(item.get('amount') or 0) if not brand_id: continue brand_amount_map.setdefault(brand_id, 0) brand_amount_map[brand_id] += amount is_valid, error_message = validate_brand_maintenance_limit( branch_id=branch_id, financial_year=financial_year, tm_id=tm_id_parent, brand_amount_map=brand_amount_map ) if not is_valid: return JsonResponse({ 'message': 'warning', 'error_message': error_message }) # 3. Process and Insert Rows for item in items_data: item_id = int(item['item_id']) rate = float(item['rate']) total_row_discount = float(item.get('discount_amt') or 0) tax_Amount = float(item.get('tax_amount') or 0) total_amounts = float(item.get('amount') or 0) tax_cgst = float(item.get('tax_cgst') or 0) tax_sgst = float(item.get('tax_sgst') or 0) imei_list = item.get('imeis', []) divisor = len(imei_list) if len(imei_list) > 0 else 1 unit_discount = total_row_discount unit_tax = tax_Amount / divisor unit_amount = total_amounts / divisor unit_cgst = tax_cgst / divisor unit_sgst = tax_sgst / divisor # Insert each IMEI as a brand new record for imei_val in imei_list: imei_clean = imei_val.strip().upper() # Duplication Check (Still required to prevent errors) if child_purchase_inward_table.objects.filter(imei_no__iexact=imei_clean, status=1).exists(): # If duplicate found, transaction rolls back everything return JsonResponse({'success': False, 'message': f'IMEI {imei_clean} already exists.'}) child_purchase_inward_table.objects.create( company_id=company_id, current_fy=financial_year, imei_no=imei_clean, supplier_id=purchase.supplier_id, tm_pu_id=purchase.id, branch_id=branch_id, item_id=item_id, base_rate=float(item.get('base_rate') or 0), ean=item.get('ean', ''), po_id=int(item.get('po_id') or 0), subcategory_id=int(item.get('subcategory_id') or 0), brand_id=int(item.get('brand_id') or 0), model_id=int(item.get('model_id') or 0), variant_id=int(item.get('variant_id') or 0), color_id=int(item.get('color_id') or 0), rate=rate, net_rate=item.get('net_rate'), quantity=1, discount_percentage=float(item.get('discount_percent') or 0), discount_amount=round(unit_discount, 2), tax_percent = item.get('tax_percent') or 18, hsn_code=item.get('hsn_code', ''), tax_amount=round(unit_tax, 2), tax_cgst=round(unit_cgst, 2), tax_sgst=round(unit_sgst, 2), tax_igst=0, amount=round(unit_amount, 2), is_active=1, status=1, is_demo=is_demo, created_on=now, updated_on=now, created_by=user_id, updated_by=user_id, mop=float(item.get('mop_price') or 0), bop=float(item.get('bop_price') or 0), wsp=float(item.get('wsp_price') or 0), fsp=float(item.get('fsp_price') or 0), db_price=float(item.get('db_price') or 0), mrp=float(item.get('mrp_price') or 0), price_list_id=int(item.get('price_list_id') or 0) ) # 4. Update Parent Totals purchase.total_quantity = total_qty purchase.supplier_date = supplier_date purchase.sub_total = sub_total purchase.total_discount = total_discount purchase.bill_discount = bill_discount purchase.total_cgst = total_cgst purchase.total_sgst = total_sgst purchase.total_tds = total_tds purchase.total_amount = total_amount purchase.roundoff = round_off purchase.grand_total = grand_total purchase.cash = float(data.get('cash', 0)) purchase.bank = float(data.get('bank', 0)) purchase.balance = balance purchase.pu_date = invoice_date purchase.is_demo = is_demo purchase.save() return JsonResponse({'success': True, 'message': 'success'}) except Exception as e: return JsonResponse({'success': False, 'message': str(e)}) def delete_tm_pu(request): if request.method != 'POST': return JsonResponse({'status': 'error', 'error_message': 'Invalid request method'}) data_id = request.POST.get('id') role_id = request.session.get('role_id') branch_id = request.session.get('branch_id') tm_purchase = select_row(purchase_inward_table, {'id': data_id}) has_access, error_message = check_user_access(role_id, 'purchase_inward', "delete") if not has_access: return JsonResponse({'status': 'permission_denied', 'error_message': error_message}) try: if tm_purchase.inward_type == 'service': service_rows = child_purchase_inward_table.objects.filter( tm_pu_id=data_id, inward_type='service', status=1, branch_id=branch_id ).values_list('service_id', flat=True) if tm_purchase.inward_type == 'accessory': # 🔍 1. Check if purchase return exists return_exists = child_purchase_return_table.objects.filter( pu_id=data_id, status=1, branch_id=branch_id ).exists() if return_exists: return JsonResponse({ 'message': 'warning', 'error_message': 'There was a purchase return against this inward, so it cannot be deleted.' }) # 🔍 2. Get all batch numbers from this inward inward_batches = child_purchase_inward_table.objects.filter( tm_pu_id=data_id, status=1, branch_id=branch_id ).values_list("batch_no", flat=True) if inward_batches: sales_exists = child_sales_order_table.objects.filter( branch_id=branch_id, status=1, batch_no__in=inward_batches ).exists() if sales_exists: return JsonResponse({ 'message': 'warning', 'error_message': 'There was sales against this purchase inward, so it cannot be deleted.' }) # 🔍 2b. Check sales returns sales_return_exists = child_sales_return_table.objects.filter( branch_id=branch_id, status=1, batch_no__in=inward_batches ).exists() if sales_return_exists: return JsonResponse({ 'message': 'warning', 'error_message': 'There was a sales and sales return against this inward, so it cannot be deleted.' }) # ✅ Mark purchase inward and child rows as inactive purchase_inward_table.objects.filter(id=data_id).update(status=0, is_active=0) child_purchase_inward_table.objects.filter(tm_pu_id=data_id).update(status=0, is_active=0) return JsonResponse({'message': 'yes'}) except Exception as e: return JsonResponse({'status': 'error', 'error_message': str(e)}) def edit_tm_inward(request): if request.headers.get('x-requested-with') == 'XMLHttpRequest' and request.method == "POST": data = purchase_inward_table.objects.filter(id=request.POST.get('id')) return JsonResponse(data.values()[0]) def ajax_tx_data(request): role_id = request.session.get('role_id') financial_year = request.session.get('financial_year') branch_id = request.session.get('branch_id') is_ho = request.session.get('is_ho') or 0 # 1 for HO has_access, error_message = check_user_access(role_id, 'purchase_inward', "read") if not has_access: return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to view purchase inward details'}) po_id = request.POST.get('po_id') data = list(selectList(child_purchase_inward_table, {'tm_pu_id': po_id}).values()) formatted = [] for index, item in enumerate(data): item_obj = item_table.objects.filter(id=item['item_id'], status=1).values('ean').first() if item['item_id'] else None action = '' if str(is_ho) == '1': action = f'' formatted.append({ 'id': index + 1, 'action': action, 'stock': item['imei_no'], 'item': item['ean'] if item_obj else '-', # ✅ With SKU 'qty': item['quantity'] if item.get('quantity') else '-', }) return JsonResponse({'data': formatted}) def purchase_barcode_view(request): if request.headers.get('x-requested-with') == 'XMLHttpRequest' and request.method == "POST": data = child_purchase_inward_table.objects.filter(id=request.POST.get('id')) return JsonResponse(data.values()[0]) def load_items(request): if request.method == 'POST': inward_type = request.POST.get('inward_type') if not inward_type: return JsonResponse({'items': []}) # Filter items only by inward_type items = item_table.objects.filter( item_type=inward_type, is_active=1, status=1 ).values('id', 'sku', 'name', 'item_type') return JsonResponse({'items': list(items)}) return JsonResponse({'error': 'Invalid request'}, status=400) def get_supplier_details(request): inward_type = request.GET.get('inward_type') if inward_type not in ['accessories', 'service']: return JsonResponse({'suppliers': []}) if inward_type == 'accessories': suppliers = supplier_table.objects.filter(is_accessory=1, status=1, is_active=1) else: # service suppliers = supplier_table.objects.filter(is_service=1, status=1, is_active=1) data = list(suppliers.values('id', 'name')) return JsonResponse({'suppliers': data}) from django.db.models import Sum from django.utils import timezone from django.http import JsonResponse def delete_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') item_row = child_purchase_inward_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_obj = purchase_inward_table.objects.filter( id=item_row.tm_pu_id, status=1 ).first() if not tm_obj: return JsonResponse({'success': False, 'message': 'Purchase master not found.'}) # 🛑 SERVICE CHECK # 🛑 PURCHASE RETURN CHECK if child_purchase_return_table.objects.filter( pu_id=item_row.tm_pu_id, item_id=item_row.item_id, status=1, branch_id=branch_id ).exists(): return JsonResponse({ 'success': False, 'message': 'Purchase return exists for this item. Cannot delete.' }) # 🛑 SALES CHECK if child_sales_order_table.objects.filter( branch_id=branch_id, status=1, batch_no=item_row.batch_no, item_id=item_row.item_id ).exists(): return JsonResponse({ 'success': False, 'message': 'Sales exist for this item. Cannot delete.' }) # 🛑 SALES RETURN CHECK if child_sales_return_table.objects.filter( branch_id=branch_id, status=1, batch_no=item_row.batch_no ).exists(): return JsonResponse({ 'success': False, 'message': 'Sales return exists for this item. Cannot delete.' }) # ✅ RECALCULATE TM TOTALS (EXCLUDING THIS TX) totals = child_purchase_inward_table.objects.filter( tm_pu_id=item_row.tm_pu_id, status=1 ).exclude(id=tx_id).aggregate( total_qty=Sum('quantity'), total_amt=Sum('total_amount'), sub_total=Sum('sub_total') ) total_qty = totals['total_qty'] or 0 total_amt = totals['total_amt'] or 0 sub_total = totals['sub_total'] 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 TABLE tm_obj.total_quantity = total_qty tm_obj.total_amount = total_amt tm_obj.sub_total = sub_total 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', 'sub_total', 'balance', 'updated_on', 'updated_by' ]) return JsonResponse({'success': True, 'message': 'Item deleted and purchase values updated successfully.'}) except Exception as e: return JsonResponse({'success': False, 'message': str(e)}) from django.http import JsonResponse from django.http import JsonResponse def check_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" imeis = [i.strip().upper() for i in imeis if i.strip()] if not imeis: return JsonResponse({"exists": False, "duplicate_imeis": []}) qs = child_purchase_inward_table.objects.filter(imei_no__in=imeis, status=1) # 🔥 Exclude all IDs belonging to the group being edited if edit_id_str and edit_id_str != '0': qs = qs.exclude(tm_pu_id=edit_id_str) duplicate_imeis = list(qs.values_list('imei_no', flat=True)) return JsonResponse({ "exists": bool(duplicate_imeis), "duplicate_imeis": duplicate_imeis }) from datetime import date from datetime import date from django.db.models import Q from datetime import date from django.http import JsonResponse def get_latest_price(brand_id, model_id, variant_id): today = date.today() return price_history_table.objects.filter( brand_id=brand_id, model_id=model_id, variant_id=variant_id, is_active=1, status=1, from_date__lte=today ).order_by('-from_date').values( 'id', 'mop', 'bop', 'fsp', 'wsp', 'from_date' ).first() from decimal import Decimal def D(val): return Decimal(val or 0) def load_purchase_details(request): 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') tax_percent = request.session.get('tax_percent') or 0 print(tax_percent) if not all([brand_id, model_id, variant_id, color_id]): return JsonResponse({ 'status': False, 'message': 'Missing parameters' }) item = item_table.objects.filter( brand_id=brand_id, model_id=model_id, variant_id=variant_id, color_id=color_id, status=1, is_active=1 ).first() if not item: return JsonResponse({ 'status': False, 'message': 'Item not found' }) # --------------------------------- # 🔥 PRICE RESOLUTION LOGIC # --------------------------------- price_data = get_latest_price( item.brand_id, item.model_id, item.variant_id ) if price_data: fsp_price = D(item.franchise_amount) - D(price_data['fsp']) wsp_price = D(item.wholesale_amount) - D(price_data['wsp']) mop_price = D(item.mop_price) - D(price_data['mop']) bop_price = D(item.bop_price) - D(price_data['bop']) price_id = price_data['id'] else: fsp_price = D(item.franchise_amount) wsp_price = D(item.wholesale_amount) mop_price = D(item.mop_price) bop_price = D(item.bop_price) price_id = 0 TAX_RATE = D('0.18') fsp_price = fsp_price + (fsp_price * TAX_RATE) wsp_price = wsp_price + (wsp_price * TAX_RATE) fsp_percent = item.franchise_price wsp_percent = item.wholesale_price mrp_price = D(item.mrp) tax_percent = D(str(request.session.get('tax_percent') or 0)) TAX_RATE = tax_percent / D('100') db_price_base = D(str(item.db_price)) db_price = db_price_base * (D('1') + TAX_RATE) print(tax_percent / D('100')) print('db_price',db_price) return JsonResponse({ 'status': True, # item info 'item_id': item.id, 'ean': item.ean, 'price_id': price_id, 'hsn_code': item.hsn_code, 'mop_price': format_amount(mop_price), 'bop_price': format_amount(bop_price), 'db_price': format_amount(db_price), 'rate': format_amount(item.db_price), 'fsp_price': format_amount(fsp_price), 'wsp_price': format_amount(wsp_price), 'fsp_percent': fsp_percent, 'wsp_percent': wsp_percent, 'mrp_price': format_amount(mrp_price), 'tax_percent': tax_percent, 'ean': item.ean, }) from django.http import JsonResponse from django.db.models import Q def get_ean_details(request): ean = request.POST.get('ean') if not ean: return JsonResponse({ 'status': False, 'message': 'EAN missing' }) item = item_table.objects.filter( ean__iexact=ean, status=1, is_active=1 ).values( 'id', 'brand_id', 'model_id', 'variant_id', 'color_id', 'sub_category_id' ).first() if not item: return JsonResponse({ 'status': False, 'message': 'No data found for this EAN.' }) return JsonResponse({ 'status': True, 'item_id': item['id'], 'brand_id': item['brand_id'], 'brand_name': getItemNameById(brand_table, item['brand_id']), 'model_id': item['model_id'], 'model_name': getItemNameById(model_table, item['model_id']), 'variant_id': item['variant_id'], 'variant_name': getItemNameById(variant_table, item['variant_id']), 'color_id': item['color_id'], 'color_name': getItemNameById(color_table, item['color_id']), 'sub_category_id': item['sub_category_id'], 'sub_category_name': getItemNameById(sub_category_table, item['sub_category_id']), })