from django.shortcuts import render from django.http import JsonResponse from django.db.models import Q from inventory.models import * from sales.models import * from material.models import * from stock.models import * from masters.models import * from common.utils import selectList, getItemNameById, format_amount, get_final_price_for_imei from masters.models import * from store.models import * def stock_finder(request): if 'user_id' not in request.session: return render(request, 'common/403.html') # Or redirect to login user_type = request.session.get('user_type') branch_id = request.session.get('branch_id') is_ho = request.session.get('is_ho') # Fetch branches for filter (only if HO) if is_ho: branches = selectList(branch_table, order_by='name') else: branches = selectList(branch_table, order_by='name') context = { 'branch': branches, } return render(request, 'stock_finder.html', context) def stock_finder_view(request): keyword = request.POST.get('model_keyword', '').strip() branch_filter_id = request.POST.get('branch_id') if not keyword: return JsonResponse({'status': False, 'message': 'Please enter a model name to search.'}) matching_models = model_table.objects.filter(name__icontains=keyword, status=1, is_active=1).values_list('id', flat=True) if not matching_models: return JsonResponse({'status': False, 'message': 'No models found matching the keyword.', 'data': []}) if branch_filter_id: target_branch_ids = [int(branch_filter_id)] else: target_branch_ids = list(branch_table.objects.filter(status=1).values_list('id', flat=True)) results = [] base_filter = Q(status=1, is_active=1, model_id__in=matching_models) purchases = child_purchase_inward_table.objects.filter(base_filter, branch_id__in=target_branch_ids).exclude(imei_no__isnull=True).exclude(imei_no='') mat_inwards = child_material_inward_table.objects.filter(base_filter, branch_id__in=target_branch_ids).exclude(imei_no__isnull=True).exclude(imei_no='') opening_stocks = opening_stock_table.objects.filter(base_filter, branch_id__in=target_branch_ids).exclude(imei_no__isnull=True).exclude(imei_no='') sales_orders = child_sales_order_table.objects.filter(base_filter, branch_id__in=target_branch_ids).exclude(imei_no__isnull=True).exclude(imei_no='') pur_returns = child_purchase_return_table.objects.filter(base_filter, branch_id__in=target_branch_ids).exclude(imei_no__isnull=True).exclude(imei_no='') mat_outwards = child_material_outward_table.objects.filter(base_filter, branch_id__in=target_branch_ids).exclude(imei_no__isnull=True).exclude(imei_no='') sales_returns = child_sales_return_table.objects.filter(base_filter, branch_id__in=target_branch_ids).exclude(imei_no__isnull=True).exclude(imei_no='') stock_map = {} # branch_id -> { key -> set(imeis) } def get_key(row): return (row.subcategory_id, row.brand_id, row.model_id, row.variant_id, row.color_id) # 1. ADD All INWARDS to stock_map for qs_name, qs in [("Purchases", purchases), ("Mat In", mat_inwards), ("Opening Stock", opening_stocks)]: for row in qs: b_id = row.branch_id if b_id not in stock_map: stock_map[b_id] = {} k = get_key(row) if k not in stock_map[b_id]: stock_map[b_id][k] = set() stock_map[b_id][k].add(row.imei_no) # 2. Add Sales Returns (items came back) for row in sales_returns: b_id = row.branch_id if b_id not in stock_map: stock_map[b_id] = {} k = get_key(row) if k not in stock_map[b_id]: stock_map[b_id][k] = set() stock_map[b_id][k].add(row.imei_no) for b_id, items in stock_map.items(): for k, imeis in items.items(): pass sold_map = {} for qs_name, qs in [("Sales", sales_orders), ("Pur Returns", pur_returns), ("Mat Out", mat_outwards)]: for row in qs: b_id = row.branch_id if b_id not in sold_map: sold_map[b_id] = set() sold_map[b_id].add(row.imei_no) sales_return_map = {} for row in sales_returns: b_id = row.branch_id if b_id not in sales_return_map: sales_return_map[b_id] = set() sales_return_map[b_id].add(row.imei_no) # Now adjust sold_map: sold = sold - returned for b_id, sold_set in sold_map.items(): if b_id in sales_return_map: sold_map[b_id] = sold_set - sales_return_map[b_id] # 4. Final Calculation and Formatting row_id = 1 # Cache names to avoid DB hits in loop branch_names = {b.id: b.name for b in branch_table.objects.filter(id__in=target_branch_ids)} subcat_names = {} brand_names = {} model_names = {} variant_names = {} color_names = {} def get_cached_name(id_val, cache, model_class): if id_val not in cache: try: cache[id_val] = model_class.objects.get(id=id_val).name except: cache[id_val] = '-' return cache[id_val] for b_id, branch_stock in stock_map.items(): if b_id not in branch_names: continue # Should not happen given logic sold_set = sold_map.get(b_id, set()) for key, imei_set in branch_stock.items(): # key = (subcat_id, brand_id, model_id, variant_id, color_id) # available = Candidates - ActualSold available_imeis = imei_set - sold_set count = len(available_imeis) if count > 0: # Fetch names subcat_id, brand_id, model_id, variant_id, color_id = key # FSP calculation sample_imei = list(available_imeis)[0] # Fetch source row for efficient price access source_row = child_purchase_inward_table.objects.filter(imei_no=sample_imei, branch_id=b_id).first() if not source_row: source_row = child_material_inward_table.objects.filter(imei_no=sample_imei, branch_id=b_id).first() if not source_row: source_row = opening_stock_table.objects.filter(imei_no=sample_imei, branch_id=b_id).first() if not source_row: source_row = child_sales_return_table.objects.filter(imei_no=sample_imei, branch_id=b_id).first() p_fsp = 0 p_wsp = 0 p_mop = 0 p_bop = 0 if source_row: p_fsp = getattr(source_row, 'fsp', 0) p_wsp = getattr(source_row, 'wsp', 0) p_mop = getattr(source_row, 'mop', 0) p_bop = getattr(source_row, 'bop', 0) else: item_obj = item_table.objects.filter( sub_category_id=subcat_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, color_id=color_id, status=1 ).first() if item_obj: p_mop = item_obj.mop_price or 0 final_fsp, _, _, _ = get_final_price_for_imei( branch_id=b_id, subcategory_id=subcat_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, imei_no=sample_imei, fsp=p_fsp, wsp=p_wsp, mop=p_mop, bop=p_bop ) results.append({ 'id': row_id, 'branch_name': branch_names[b_id], 'subcategory_name': get_cached_name(subcat_id, subcat_names, sub_category_table), 'brand_name': get_cached_name(brand_id, brand_names, brand_table), 'model_name': get_cached_name(model_id, model_names, model_table), 'variant_name': get_cached_name(variant_id, variant_names, variant_table), 'color_name': get_cached_name(color_id, color_names, color_table), 'available_stock': count, 'fsp': format_amount(final_fsp) }) row_id += 1 if not results: return JsonResponse({'status': False, 'message': 'Stock not available', 'data': []}) return JsonResponse({'status': True, 'data': results}) def load_model_name(request): query = request.GET.get('term', '').strip() customers = model_table.objects.filter( Q(name__icontains=query), status=1, is_active=1 ) data = [ { 'id': c.id, 'name': c.name, } for c in customers ] return JsonResponse(data, safe=False)