from django.shortcuts import render # Create your views here. 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 inventory.models import * from masters.models import * from voucher.models import * from stock.models import * from sales.models import * from django.db import IntegrityError, transaction from collections import defaultdict 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 django.db.models import F, FloatField, ExpressionWrapper, Sum, Q, Case, When, Value import re from decimal import Decimal from django.db import connection from supplier.models import * from material.models import * from wholesale.models import * # ```````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````` def stock_summary(request): if 'user_id' in request.session: user_type = request.session.get('user_type') branch_id = request.session.get('branch_id') if user_type == 'stores': supplier = selectList(supplier_table,{'is_global':1} ,order_by='name') branch = selectList(branch_table,{'is_wholesale':0}, order_by='name').exclude(id=branch_id) category = selectList(category_table) sub_category = selectList(sub_category_table) brand = selectList(brand_table) return render(request, 'stock_summary.html',{'supplier':supplier,'branch':branch,'category':category,'sub_category':sub_category,'brand':brand }) elif user_type == 'wholesale': supplier = selectList(supplier_table,{'is_global':1} ,order_by='name') branch = selectList(branch_table, {'is_wholesale':1}, order_by='name').exclude(id=branch_id) category = selectList(category_table) sub_category = selectList(sub_category_table) brand = selectList(brand_table) return render(request, 'wholesale_stock_summary.html',{'supplier':supplier,'branch':branch,'category':category,'sub_category':sub_category,'brand':brand }) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def ajax_stock_summary(request): role_id = request.session.get('role_id') branch_id = request.session.get('branch_id') is_ho = request.session.get('is_ho') category_id = request.POST.get('category_id') subcategory_id = request.POST.get('subcategory') brand_id = request.POST.get('brand_id') keyword = request.POST.get('keyword_search', '').strip() has_access, error_message = check_user_access(role_id, 'stock_summary', "read") if not has_access: return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to view the stock summary' }) # ---- Fetch from v_stock_summary ---- query = "SELECT * FROM v_stock_summary WHERE branch_id = %s" params = [branch_id] if category_id: query += " AND category_id = %s" params.append(category_id) if subcategory_id: query += " AND subcategory_id = %s" params.append(subcategory_id) if brand_id: query += " AND brand_id = %s" params.append(brand_id) if keyword: query += " AND (sku LIKE %s OR description LIKE %s)" params.append(f'%{keyword}%') params.append(f'%{keyword}%') with connection.cursor() as cursor: cursor.execute(query, params) columns = [col[0] for col in cursor.description] rows = [dict(zip(columns, row)) for row in cursor.fetchall()] formatted = [] for idx, row in enumerate(rows, start=1): def make_stock_link(qty, type_, sub_id, b_id, m_id, v_id, c_id): type_func_map = { 'opening': 'opening_data', 'purchase': 'purchase_data', 'p_return': 'p_return_data', 'sales': 'sales_data', 's_return': 's_return_data' } func_name = type_func_map.get(type_, 'show_stock_detail') return ( f'' f'{qty}' ) formatted.append({ 'id': idx, 'ean': row['ean'], 'sku': row['sku'], 'description': row['description'], 'brand': row['brand'], 'model': row['model'], 'variant': row['variant'], 'color': row['color'], 'category': row['category'], 'sub_category': row['sub_category'], 'opening': make_stock_link(row['opening_stock'], 'opening', row['subcategory_id'], row['brand_id'], row['model_id'], row['variant_id'], row['color_id']), 'purchase': row['purchase'], 'p_return': row['purchase_return'], 't_in': row['transfer_in'], 't_out': row['transfer_out'], 'sales': row['sales'], 's_return': row['sales_return'], 'stock': row['available_stock'] }) return JsonResponse({ 'data': formatted, 'is_ho': is_ho }) def ajax_wholesale_stock_summary(request): role_id = request.session.get('role_id') branch_id = request.session.get('branch_id') is_ho = request.session.get('is_ho') category_id = request.POST.get('category_id') subcategory_id = request.POST.get('subcategory') brand_id = request.POST.get('brand_id') keyword = request.POST.get('keyword_search', '').strip() # Reuse stock_summary permission has_access, error_message = check_user_access(role_id, 'stock_summary', "read") if not has_access: return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to view the wholesale stock summary' }) # ---- Fetch from v_wholesale_stock_summary ---- query = "SELECT * FROM v_wholesale_stock_summary WHERE branch_id = %s" params = [branch_id] if category_id: query += " AND category_id = %s" params.append(category_id) if subcategory_id: query += " AND subcategory_id = %s" params.append(subcategory_id) if brand_id: query += " AND brand_id = %s" params.append(brand_id) if keyword: query += " AND (sku LIKE %s OR description LIKE %s)" params.append(f'%{keyword}%') params.append(f'%{keyword}%') with connection.cursor() as cursor: cursor.execute(query, params) columns = [col[0] for col in cursor.description] rows = [dict(zip(columns, row)) for row in cursor.fetchall()] # Merge in wholesale opening stock (available only) so summary matches IMEI list. sold_opening_imeis = set( wholesale_child_sales_order_table.objects.filter( branch_id=branch_id, status=1, is_active=1 ).exclude( imei_no__isnull=True ).exclude( imei_no='' ).values_list('imei_no', flat=True) ) opening_qs = wholesale_opening_stock_table.objects.filter( branch_id=branch_id, status=1, is_active=1 ) if subcategory_id: opening_qs = opening_qs.filter(subcategory_id=subcategory_id) if brand_id: opening_qs = opening_qs.filter(brand_id=brand_id) keyword_keys = None if keyword: keyword_keys = set( item_table.objects.filter( Q(sku__icontains=keyword) | Q(sku_text__icontains=keyword) ).values_list('sub_category_id', 'brand_id', 'model_id', 'variant_id', 'color_id') ) opening_agg = {} for o in opening_qs: imei = (o.imei_no or '').strip() if not imei or imei in sold_opening_imeis: continue key = (o.subcategory_id, o.brand_id, o.model_id, str(o.variant_id), o.color_id) if keyword_keys is not None and (o.subcategory_id, o.brand_id, o.model_id, o.variant_id, o.color_id) not in keyword_keys: continue opening_agg[key] = opening_agg.get(key, 0) + (o.quantity or 1) row_map = {} for row in rows: key = ( row.get('subcategory_id'), row.get('brand_id'), row.get('model_id'), str(row.get('variant_id')), row.get('color_id') ) row_map[key] = row # Add opening qty to existing rows or create new row if absent in view. for key, open_qty in opening_agg.items(): if key in row_map: r = row_map[key] r['opening_stock'] = (r.get('opening_stock') or 0) + open_qty r['available_stock'] = (r.get('available_stock') or 0) + open_qty else: sub_id, br_id, mo_id, va_id, co_id = key item_obj = item_table.objects.filter( sub_category_id=sub_id, brand_id=br_id, model_id=mo_id, variant_id=va_id, color_id=co_id, status=1, is_active=1 ).first() if not item_obj: continue rows.append({ 'ean': item_obj.ean or '-', 'sku': item_obj.sku or '-', 'description': item_obj.sku_text or '-', 'brand': getItemNameById(brand_table, br_id), 'model': getItemNameById(model_table, mo_id), 'variant': getItemNameById(variant_table, va_id), 'color': getItemNameById(color_table, co_id), 'category': getItemNameById(category_table, item_obj.category_id), 'sub_category': getItemNameById(sub_category_table, sub_id), 'subcategory_id': sub_id, 'brand_id': br_id, 'model_id': mo_id, 'variant_id': va_id, 'color_id': co_id, 'opening_stock': open_qty, 'purchase': 0, 'sales': 0, 'available_stock': open_qty }) formatted = [] for idx, row in enumerate(rows, start=1): formatted.append({ 'id': idx, 'ean': row['ean'], 'sku': row['sku'], 'description': row['description'], 'brand': row['brand'], 'model': row['model'], 'variant': row['variant'], 'color': row['color'], 'category': row['category'], 'sub_category': row['sub_category'], 'opening': row['opening_stock'], 'purchase': row['purchase'], 'sales': row['sales'], 'stock': row['available_stock'] }) return JsonResponse({ 'data': formatted, 'is_ho': is_ho }) def load_purchase_imeis(request): subcategoryId = request.POST.get('subcategory_id') brandId = request.POST.get('brand_id') modelId = request.POST.get('model_id') variantId = request.POST.get('variant_id') colorId = request.POST.get('color_id') branch_id = request.session.get('branch_id') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) item_master = item_table.objects.filter(sub_category_id=subcategoryId, brand_id=brandId, model_id=modelId, variant_id=variantId, color_id=colorId).first() item_name = item_master.sku_text if item_master else '-' purchase_details = child_purchase_inward_table.objects.filter( subcategory_id=subcategoryId, brand_id=brandId, model_id=modelId, variant_id=variantId, color_id=colorId, status=1, branch_id=branch_id, current_fy=financial_year, ) formatted = [] for idx, p_item in enumerate(purchase_details): tm_purchase = select_row(purchase_inward_table, {'id': p_item.tm_pu_id}) formatted.append({ 'id': idx + 1, 'pu_no': tm_purchase.pu_no, 'pu_date':format_date_month_year(tm_purchase.pu_date), 'imei_no': p_item.imei_no, 'fsp': format_amount(p_item.fsp or 0), 'wsp': format_amount(p_item.wsp or 0), 'mop': format_amount(p_item.mop or 0), 'bop': format_amount(p_item.bop or 0), 'mrp': format_amount(p_item.mrp or 0) }) # 3. Return the 'formatted' list, not the raw QuerySet return JsonResponse({ 'data': formatted, 'item_name': item_name }) def load_opening_imeis(request): subcategoryId = request.POST.get('subcategory_id') brandId = request.POST.get('brand_id') modelId = request.POST.get('model_id') variantId = request.POST.get('variant_id') colorId = request.POST.get('color_id') branch_id = request.session.get('branch_id') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) item_master = item_table.objects.filter(sub_category_id=subcategoryId, brand_id=brandId, model_id=modelId, variant_id=variantId, color_id=colorId).first() item_name = item_master.sku_text if item_master else '-' purchase_details = opening_stock_table.objects.filter( subcategory_id=subcategoryId, brand_id=brandId, model_id=modelId, variant_id=variantId, color_id=colorId, status=1, branch_id=branch_id, current_fy=financial_year, ) print('purchase_details', len(purchase_details)) formatted = [] for idx, p_item in enumerate(purchase_details): formatted.append({ 'id': idx + 1, 'pu_no': p_item.stock_no, 'pu_date':format_date_month_year(p_item.date), 'imei_no': p_item.imei_no, 'fsp': format_amount(p_item.fsp or 0), 'wsp': format_amount(p_item.wsp or 0), 'mop': format_amount(p_item.mop or 0), 'bop': format_amount(p_item.bop or 0), 'mrp': format_amount(p_item.mrp or 0) }) # 3. Return the 'formatted' list, not the raw QuerySet return JsonResponse({ 'data': formatted, 'item_name': item_name }) def load_sales_imeis(request): subcategoryId = request.POST.get('subcategory_id') brandId = request.POST.get('brand_id') modelId = request.POST.get('model_id') variantId = request.POST.get('variant_id') colorId = request.POST.get('color_id') branch_id = request.session.get('branch_id') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) # 1. Get Item Name item_master = item_table.objects.filter(sub_category_id=subcategoryId, brand_id=brandId, model_id=modelId, variant_id=variantId, color_id=colorId).first() item_name = item_master.sku_text if item_master else '-' # 2. Fetch Sales Details (Renamed variable for clarity) sales_details = child_sales_order_table.objects.filter( subcategory_id=subcategoryId, brand_id=brandId, model_id=modelId, variant_id=variantId, color_id=colorId, status=1, branch_id=branch_id, current_fy=financial_year, ).order_by('-id') formatted = [] for idx, s_item in enumerate(sales_details): tm_sales = select_row(sales_order_table, {'id': s_item.tm_sales_id}) formatted.append({ 'id': idx + 1, 'imei_no': s_item.imei_no, 'inv_no': tm_sales.inv_no, 'inv_date':format_date_month_year(tm_sales.inv_date), 'rate': format_amount(s_item.rate or 0), 'amount': format_amount(s_item.amount or 0), 'tax_cgst': format_amount(s_item.tax_cgst or 0), 'tax_sgst': format_amount(s_item.tax_sgst or 0), 'bop': format_amount(s_item.bop or 0), 'mrp': format_amount(s_item.mrp or 0), }) return JsonResponse({ 'data': formatted, 'item_name': item_name }) from django.db.models import Sum, Q from django.http import JsonResponse from datetime import date def download_sample_excel(request): wb = openpyxl.Workbook() ws = wb.active ws.title = "Stock Sample" user_type = (request.session.get('user_type') or '').strip().lower() is_ho = int(request.session.get('is_ho') or 0) # Show FSP/WSP only for HO stores. Hide for branch stores and wholesale users. show_fsp_wsp = (user_type == 'stores' and is_ho == 1) # Headers headers = ['S.NO', 'Sub Category', 'Brand', 'Model', 'Variant', 'Color', 'IMEI', 'Purchase Price', 'MRP'] if show_fsp_wsp: headers.extend(['FSP', 'WSP']) headers.extend(['MOP', 'BOP']) ws.append(headers) for cell in ws[1]: cell.font = Font(bold=True) items = selectList(item_table, order_by='sku') sno = 1 # 🔥 S.NO starts here for item in items: sub_category_name = getItemNameById(sub_category_table, item.sub_category_id) if item.sub_category_id else '' brand_name = getItemNameById(brand_table, item.brand_id) if item.brand_id else '' model_name = getItemNameById(model_table, item.model_id) if item.model_id else '' variant_name = getItemNameById(variant_table, item.variant_id) if item.variant_id else '' color_name = getItemNameById(color_table, item.color_id) if item.color_id else '' row = [ sno, # ✅ S.NO sub_category_name, brand_name, model_name, variant_name, color_name, '', # IMEI (blank for sample) item.db_price, # Purchase Price item.mrp, ] if show_fsp_wsp: row.extend([ item.franchise_amount, # FSP item.wholesale_amount, # WSP ]) row.extend([ item.mop_price, item.bop_price, ]) ws.append(row) sno += 1 # ⏭️ increment today = format_date_month_year(date_time) filename = f'opening_stock_{today}.xlsx' response = HttpResponse( content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = f'attachment; filename="{filename}"' wb.save(response) return response def generate_opening_batch_no(branch_id, current_fy, prefix="OPN", padding=3): last_entry = opening_stock_table.objects.filter( branch_id=branch_id, current_fy=current_fy, status=1 ).order_by('-id').first() if last_entry and last_entry.stock_no: match = re.search(rf"{prefix}(\d+)$", last_entry.stock_no) if match: last_number = int(match.group(1)) new_number = last_number + 1 else: new_number = 1 else: new_number = 1 return f"{prefix}{str(new_number).zfill(padding)}" def get_id_by_name(model, name_field, name_value): if not name_value: return None obj = model.objects.filter( **{name_field: name_value}, status=1, is_active=1 ).first() return obj.id if obj else None def import_opening_stock(request): if request.method != 'POST' or 'file' not in request.FILES: return JsonResponse({'message': 'Invalid request'}, status=400) try: excel_file = request.FILES['file'] company_id = request.session.get('company_id') branch_id = request.session.get('branch_id') user_id = request.session.get('user_id') is_ho = request.session.get('is_ho') fyf_name = request.session.get('fyf') current_fy = calculate_financial_year(fyf_name) if not all([company_id, branch_id, user_id, current_fy]): return JsonResponse({'message': 'Session data missing'}, status=400) date_times = timezone.localtime(timezone.now()) filename = default_storage.save(f"stock_excel/{excel_file.name}", excel_file) file_path = os.path.join(settings.MEDIA_ROOT, filename) stock_log = stock_log_table.objects.create( company_id=company_id, branch_id=branch_id, current_fy=current_fy, uploaded_by=user_id, uploaded_on=format_datetime(date_times), excel_file=filename, created_on=format_datetime(date_times), updated_on=format_datetime(date_times), created_by=user_id, updated_by=user_id, status=1, is_active=1 ) wb = openpyxl.load_workbook(file_path) sheet = wb.active rows = list(sheet.iter_rows(min_row=2, values_only=True)) opening_stock_entries = [] skipped_rows = [] skipped_imeis = [] for row_no, row in enumerate(rows, start=2): try: ( _sno, sub_category, brand, model, variant, color, imei, purchase_price, mrp, fsp, wsp, mop, bop ) = row except Exception: skipped_rows.append(f"Row {row_no}: column mismatch") continue if not imei: skipped_rows.append(f"Row {row_no}: IMEI missing") continue imei = str(imei).strip() # 🚫 comma-separated IMEI if ',' in imei: skipped_imeis.append(f"{imei} (Row {row_no}: multiple IMEIs)") continue # 🚫 IMEI already exists anywhere if imei_exists(imei, branch_id): skipped_imeis.append(f"{imei} (already exists)") continue # ❌ MASTER LOOKUPS (NO CREATE) subcategory_id = get_id_by_name(sub_category_table, 'name', sub_category) brand_id = get_id_by_name(brand_table, 'name', brand) model_id = get_id_by_name(model_table, 'name', model) variant_id = get_id_by_name(variant_table, 'name', variant) color_id = get_id_by_name(color_table, 'name', color) missing = [] if not subcategory_id: missing.append('SubCategory') if not brand_id: missing.append('Brand') if not model_id: missing.append('Model') if not variant_id: missing.append('Variant') if not color_id: missing.append('Color') if missing: skipped_imeis.append( f"{imei} (Row {row_no}: missing {', '.join(missing)})" ) continue generated_no = generate_opening_batch_no( branch_id=branch_id, current_fy=current_fy, prefix="OPN", padding=3 ) branch_code = 'BM' if is_ho == 1 else str(branch_id).zfill(3) stock_no = f"{branch_code}/{generated_no}" data = select_row( item_table, { 'sub_category_id': subcategory_id, 'brand_id': brand_id, 'model_id': model_id, 'variant_id': variant_id, 'color_id': color_id } ) if not data: skipped_imeis.append( f"{imei} (Row {row_no}: item master not found)" ) continue entry = opening_stock_table( company_id=company_id, branch_id=branch_id, current_fy=current_fy, ean=data.ean, hsn_code=data.hsn_code, date=format_date(date_times), time=format_time(date_times), stock_no=stock_no, subcategory_id=subcategory_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, color_id=color_id, imei_no=imei, quantity=1, db_price=float(purchase_price or 0), mrp=float(mrp or 0), fsp=float(fsp or 0), wsp=float(wsp or 0), mop=float(mop or 0), bop=float(bop or 0), employee_id=user_id, stock_log_id=stock_log.id, created_on=format_datetime(date_times), updated_on=format_datetime(date_times), updated_by=user_id, created_by=user_id, status=1, is_active=1 ) opening_stock_entries.append(entry) if opening_stock_entries: opening_stock_table.objects.bulk_create(opening_stock_entries, batch_size=500) return JsonResponse({ 'message': f'{len(opening_stock_entries)} opening stock records imported successfully', 'skipped_rows': skipped_rows or None, 'skipped_imeis': skipped_imeis or None }) except Exception as e: return JsonResponse({'message': str(e)}, status=500) def imei_exists(imei, branch_id): """ Returns True if IMEI already exists in: - opening_stock - purchase inward - material / transfer inward for the given branch """ # 1️⃣ Opening Stock if opening_stock_table.objects.filter( imei_no=imei, branch_id=branch_id, status=1, is_active=1 ).exists(): return True # 2️⃣ Purchase Inward if child_purchase_inward_table.objects.filter( imei_no=imei, status=1, is_active=1, tm_pu_id__in=purchase_inward_table.objects.filter( branch_id=branch_id, status=1, is_active=1 ).values_list('id', flat=True) ).exists(): return True # 3️⃣ Transfer / Material Inward if child_material_inward_table.objects.filter( imei_no=imei, status=1, is_active=1, tm_material_id__in=material_inward_table.objects.filter( branch_id=branch_id, status=1, is_active=1 ).values_list('id', flat=True) ).exists(): return True return False