from django.shortcuts import render from django.db.models import Q, Count from django.http import HttpResponseRedirect, JsonResponse from sales.models import * from company.models import * from masters.models import * from common.utils import * from inventory.models import * from datetime import datetime import calendar def sales_imei_report(request): if 'user_id' in request.session: branch_id = request.session.get('branch_id') branch = selectList(branch_table, order_by='name') brands = selectList(brand_table, order_by='name') return render(request, 'sales_imei_report.html', { 'branch': branch, 'brands': brands, }) else: return HttpResponseRedirect("/") def ajax_sales_imei_report(request): role_id = request.session.get('role_id') is_ho = request.session.get('is_ho') 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, 'sales_imei_summary', "read") if not has_access: return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to view this report' }) month_str = request.POST.get('month') # YYYY-MM branch_id_post = request.POST.get('branch_id') brand_id = request.POST.get('brand_id') # Date Range from Month if month_str: year, month = map(int, month_str.split('-')) _, last_day = calendar.monthrange(year, month) from_date = f"{year}-{month:02d}-01" to_date = f"{year}-{month:02d}-{last_day}" else: # Default to current month if not provided today = datetime.now() year, month = today.year, today.month _, last_day = calendar.monthrange(year, month) from_date = f"{year}-{month:02d}-01" to_date = f"{year}-{month:02d}-{last_day}" # 1. Filter Sales sales_q = Q(status=1, current_fy=financial_year, sales_status='approved') if not is_ho: sales_q &= Q(branch_id=branch_id) if branch_id_post: sales_q &= Q(branch_id=branch_id_post) if from_date and to_date: sales_q &= Q(inv_date__range=[from_date, to_date]) tm_sales_ids = list(sales_order_table.objects.filter(sales_q).values_list('id', flat=True)) if not tm_sales_ids: return JsonResponse({'data': []}) # 2. Filter Sales Items (tx_sales) tx_q = Q(status=1, tm_sales_id__in=tm_sales_ids) if brand_id: tx_q &= Q(brand_id=brand_id) sales_items = child_sales_order_table.objects.filter(tx_q).values( 'branch_id', 'brand_id', 'model_id', 'variant_id', 'subcategory_id' ).annotate(total_qty=Count('id')) # 3. Filter Returns (tx_sales_return) # We need to subtract items that were returned FOR THIS SPECIFIC SALE # Get return items linked to these sales return_items = child_sales_return_table.objects.filter( status=1, sales_id__in=tm_sales_ids # Linked to the filtered sales ).values( 'branch_id', 'brand_id', 'model_id', 'variant_id', 'subcategory_id' ).annotate(return_qty=Count('id')) # Convert returns to a map dictionary for easy lookup return_map = {} for r in return_items: key = (r['branch_id'], r['brand_id'], r['model_id'], r['variant_id'], r['subcategory_id']) return_map[key] = r['return_qty'] # Get Sales Order details map # tm_sales_details = sales_order_table.objects.filter(id__in=tm_sales_ids).values( # 'id', 'inv_no', 'inv_date', 'branch_id' # ) # tm_map = {s['id']: s for s in tm_sales_details} formatted = [] counter = 1 for item in sales_items: key = (item['branch_id'], item['brand_id'], item['model_id'], item['variant_id'], item['subcategory_id']) total_qty = item['total_qty'] return_qty = return_map.get(key, 0) net_qty = total_qty - return_qty if net_qty > 0: formatted.append({ 'id': counter, 'branch_id': item['branch_id'], 'branch_name': getItemNameById(branch_table, item['branch_id']), 'subcategory': getItemNameById(sub_category_table, item['subcategory_id']), 'brand': getItemNameById(brand_table, item['brand_id']), 'model': getItemNameById(model_table, item['model_id']), 'variant': getItemNameById(variant_table, item['variant_id']), 'total_qty': net_qty, # Metadata for modal 'brand_id': item['brand_id'], 'model_id': item['model_id'], 'variant_id': item['variant_id'], 'month': month_str, }) counter += 1 return JsonResponse({'data': formatted}) def ajax_sales_imei_details(request): role_id = request.session.get('role_id') has_access, error_message = check_user_access(role_id, 'sales_imei_summary', "read") if not has_access: return JsonResponse({'data': []}) month_str = request.POST.get('month') branch_id = request.POST.get('branch_id') brand_id = request.POST.get('brand_id') model_id = request.POST.get('model_id') variant_id = request.POST.get('variant_id') if not all([month_str, branch_id, brand_id, model_id, variant_id]): return JsonResponse({'data': []}) # Date Range from Month year, month = map(int, month_str.split('-')) _, last_day = calendar.monthrange(year, month) from_date = f"{year}-{month:02d}-01" to_date = f"{year}-{month:02d}-{last_day}" # 1. Get Approved Sales IDs sales_q = Q(status=1, branch_id=branch_id, inv_date__range=[from_date, to_date], sales_status='approved') tm_sales_qs = sales_order_table.objects.filter(sales_q) tm_sales_ids = list(tm_sales_qs.values_list('id', flat=True)) tm_map = {s['id']: s for s in tm_sales_qs.values('id', 'inv_no', 'inv_date')} if not tm_sales_ids: return JsonResponse({'data': []}) # 2. Filter Items matching group details_q = Q(status=1, tm_sales_id__in=tm_sales_ids) if brand_id: details_q &= Q(brand_id=brand_id) if model_id: details_q &= Q(model_id=model_id) if variant_id: details_q &= Q(variant_id=variant_id) items = child_sales_order_table.objects.filter(details_q).values( 'id', 'tm_sales_id', 'imei_no', 'color_id', 'amount' ) # 3. Exclude returned items returned_items = child_sales_return_table.objects.filter( status=1, sales_id__in=tm_sales_ids, brand_id=brand_id, model_id=model_id, variant_id=variant_id ).values_list('imei_no', 'sales_id') returned_set = set(returned_items) formatted = [] # Fetch names once brand_name = getItemNameById(brand_table, brand_id) model_name = getItemNameById(model_table, model_id) variant_name = getItemNameById(variant_table, variant_id) for item in items: # Check if this specific IMEI in this sale was returned if (item['imei_no'], item['tm_sales_id']) in returned_set: continue tm = tm_map.get(item['tm_sales_id'], {}) color_name = getItemNameById(color_table, item['color_id']) formatted.append({ 'inv_date': tm.get('inv_date').strftime('%d-%m-%Y') if tm.get('inv_date') else '', 'inv_no': tm.get('inv_no', ''), 'imei': item['imei_no'], 'brand': brand_name, 'model': model_name, 'variant': variant_name, 'color': color_name, 'amount': f"{item['amount']:.2f}" }) return JsonResponse({'data': formatted})