# ============================================ # Import Libraries and Models # ============================================ from django.shortcuts import render, get_object_or_404, HttpResponseRedirect from django.http import HttpResponse, JsonResponse from django.conf import settings from django.utils import timezone from django.db.models import Max, F, Q from django.db.models.functions import Cast from django.db import connection from django.template.loader import get_template from django.templatetags.static import static from datetime import datetime from dateutil.parser import parse as parse_date import json import hashlib import os import base64 from io import BytesIO from xhtml2pdf import pisa from num2words import num2words from django.contrib.staticfiles import finders # Import common utilities and models from common.models import * from common.utils import * from masters.models import * from masters.forms import * from privilege.models import * from scheme.models import * from scheme.forms import * from supplier.models import supplier_table # ============================================ # Price Drop - Main View # ============================================ def price_drop(request): """ Display Price Drop listing page Only accessible to store users """ if 'user_id' in request.session: user_type = request.session.get('user_type') if user_type == 'stores': cmpy = select_row(company_table, {'id': 1}) category = selectList(category_table, order_by='name') sub_category = selectList(sub_category_table, order_by='name') uom = selectList(uom_table, order_by='name') variant = selectList(variant_table, order_by='name') model = selectList(model_table, order_by='name') tax = selectList(tax_table, order_by='name') brand = selectList(brand_table, order_by='name') color = selectList(color_table, order_by='name') return render(request, 'ppd/price_drop.html', { 'company': cmpy, 'category': category, 'sub_category': sub_category, 'uom': uom, 'brand': brand, 'tax': tax, 'variant': variant, 'model': model, 'color': color }) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") # ============================================ # Price Drop - Add Page # ============================================ def price_drop_addpage(request): """ Display Price Drop add/create page Generates serial number and fetches required data """ if 'user_id' in request.session: user_type = request.session.get('user_type') branch_id = request.session.get('branch_id') if user_type == 'stores': cmpy = select_row(company_table, {'id': 1}) category = selectList(category_table, order_by='name') sub_category = selectList(sub_category_table, order_by='name') uom = selectList(uom_table, order_by='name') variant = selectList(variant_table, order_by='name') model = selectList(model_table, order_by='name') tax = selectList(tax_table, order_by='name') brand = selectList(brand_table, order_by='name') color = selectList(color_table, order_by='name') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) supplier = selectList(supplier_table, order_by='name') # Generate Price Drop serial number inv_no = generate_serial_number( model=price_history_table, number_field='price_no', financial_year_field='current_fy', financial_year=financial_year, branch_id=branch_id, ) return render(request, 'ppd/add.html', { 'supplier': supplier, 'company': cmpy, 'category': category, 'sub_category': sub_category, 'uom': uom, 'brand': brand, 'tax': tax, 'variant': variant, 'model': model, 'color': color, 'inv_no': inv_no }) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def price_drop_editpage(request): """ Display Price Drop add/create page Generates serial number and fetches required data """ if 'user_id' in request.session: user_type = request.session.get('user_type') branch_id = request.session.get('branch_id') 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") cmpy = select_row(company_table, {'id': 1}) category = selectList(category_table, order_by='name') sub_category = selectList(sub_category_table, order_by='name') uom = selectList(uom_table, order_by='name') variant = selectList(variant_table, order_by='name') model = selectList(model_table, order_by='name') tax = selectList(tax_table, order_by='name') brand = selectList(brand_table, order_by='name') color = selectList(color_table, order_by='name') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) supplier = selectList(supplier_table, order_by='name') price_drop = select_row(price_history_table, {'id': decoded_id}) # Generate Price Drop serial number inv_no = generate_serial_number( model=price_history_table, number_field='price_no', financial_year_field='current_fy', financial_year=financial_year, branch_id=branch_id, ) return render(request, 'ppd/edit.html', { 'supplier': supplier, 'company': cmpy, 'category': category, 'sub_category': sub_category, 'uom': uom, 'brand': brand, 'tax': tax, 'variant': variant, 'model': model, 'color': color, 'inv_no': inv_no, 'price_drop': price_drop }) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") # ============================================ # AJAX - Price Drop View # ============================================ def ajax_price_view(request): """ AJAX endpoint to fetch price drop records with filters Applies role-based access control """ role_id = request.session.get('role_id') has_access, error_message = check_user_access(role_id, 'price_drop', "read") if not has_access: return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to view Price Drop details' }) # Get filter parameters brand_id = request.POST.get('brand_id') model_id = request.POST.get('model_id') variant_id = request.POST.get('variant_id') from_date = request.POST.get('from_date') to_date = request.POST.get('to_date') query = Q(status=1) # Apply filters to query if brand_id: query &= Q(brand_id=brand_id) if model_id: query &= Q(model_id=model_id) if variant_id: query &= Q(variant_id=variant_id) if from_date and to_date: query &= Q(from_date__range=[from_date, to_date]) # Fetch and format data data = list(selectList(price_history_table, query).values()) formatted = [ { 'id': index + 1, 'action': ' \ '.format(item['id'], item['id']), 'from_date': format_date_month_year(item['from_date']) if item['from_date'] else '-', 'supplier': getItemNameById(supplier_table, item['supplier_id']), 'brand': getItemNameById(brand_table, item['brand_id']), 'model': getItemNameById(model_table, item['model_id']), 'variant': getItemNameById(variant_table, item['variant_id']), 'ppd_fsp': format_amount(item['fsp']) if item['fsp'] else '-', 'ppd_wsp': format_amount(item['wsp']) if item['wsp'] else '-', 'ppd_mop': format_amount(item['mop']) if item['mop'] else '-', 'ppd_bop': format_amount(item['bop']) if item['bop'] else '-', 'credit': format_amount(item['credit_note']) if item['credit_note'] else '-', 'debit': format_amount(item['debit_note']) if item['debit_note'] else '-', 'claim': format_amount(item['claim_amount']) if item['claim_amount'] else '-', 'description': item['description'] if item['description'] else '-', 'price_no': item['price_no'] if item['price_no'] else '-', 'status': format_badge(item['claim_status'], mapping={ 'pending': 'badge text-bg-info', 'claimed': 'badge text-bg-success', 'partial': 'badge text-bg-warning' }, label_mapping={ 'pending': 'Pending', 'claimed': 'Claimed', 'partial': 'Partial' }), } for index, item in enumerate(data) ] return JsonResponse({'data': formatted}) # ============================================ # Add Price Drop Record # ============================================ def add_price_drop(request): """ Create new price drop record with multiple items Prevents duplicate price drop for same from_date """ if request.method == "POST": try: # ----------------------------- # MAIN FORM DATA # ----------------------------- supplier_id = request.POST.get('supplier_id') subcategory_id = request.POST.get('subcategory_id') brand_id = request.POST.get('brand_id') model_id = request.POST.get('model_id') variant_id = request.POST.get('variant_id') from_date = request.POST.get('sales_date') price_no = request.POST.get('price_drop_no') # ----------------------------- # PRICE FIELDS # ----------------------------- fsp = request.POST.get('fsp_price') or 0.00 wsp = request.POST.get('wsp_price') or 0.00 mop = request.POST.get('mop_price') or 0.00 bop = request.POST.get('bop_price') or 0.00 credit_note = request.POST.get('credit_note') or 0.00 debit_note = request.POST.get('debit_note') or 0.00 # ----------------------------- # ITEMS # ----------------------------- items_json = request.POST.get('items') items_list = json.loads(items_json) if items_json else [] # ----------------------------- # SESSION DATA # ----------------------------- current_time = timezone.localtime(timezone.now()) fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) branch_id = request.session.get('branch_id') user_id = request.session.get('user_id') # -------------------------------------------------- # 🚫 DUPLICATE PRICE DROP CHECK (IMPORTANT) # -------------------------------------------------- exists = price_history_table.objects.filter( branch_id=branch_id, subcategory_id=subcategory_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, from_date=from_date, status=1, is_active=1 ).exists() if exists: return JsonResponse({'message': 'warning', 'error_message': 'Price drop already exists for this date. Please edit the existing record'}) # -------------------------------------------------- # CREATE MASTER PRICE DROP # -------------------------------------------------- master_history = price_history_table.objects.create( supplier_id=supplier_id, subcategory_id=subcategory_id, brand_id=brand_id, branch_id=branch_id, model_id=model_id, variant_id=variant_id, from_date=from_date, price_no=price_no, date=current_time.date(), time=current_time.time(), current_fy = financial_year, fsp=fsp, wsp=wsp, mop=mop, bop=bop, created_on=current_time, created_by=user_id, employee_id=user_id, status=1, is_active=1, is_claim=1, credit_note=credit_note, debit_note=debit_note ) # -------------------------------------------------- # TRANSACTION ENTRIES # -------------------------------------------------- tx_entries = [] for item in items_list: tx_entries.append(tx_price_history_table( branch_id=branch_id, tm_price_id=master_history.id, supply_branch_id=item.get('branch_id'), supplier_id=supplier_id, store_type=item.get('store_type'), current_fy=financial_year, imei_no=item.get('imei'), is_applicable=int(item.get('is_applicable', 0)), fsp=item.get('fsp_price') or 0.00, fsp_discount=item.get('fsp_discount') or 0.00, fsp_after_discount=item.get('fsp_after') or 0.00, wsp=item.get('wsp_price') or 0.00, wsp_discount=item.get('wsp_discount') or 0.00, wsp_after_discount=item.get('wsp_after') or 0.00, mop=item.get('mop_price') or 0.00, mop_discount=item.get('mop_discount') or 0.00, mop_after_discount=item.get('mop_after') or 0.00, bop=item.get('bop_price') or 0.00, bop_discount=item.get('bop_discount') or 0.00, bop_after_discount=item.get('bop_after') or 0.00, credit_fsp=item.get('credit_fsp') or 0.00, credit_wsp=item.get('credit_wsp') or 0.00, created_on=current_time, created_by=user_id )) tx_price_history_table.objects.bulk_create(tx_entries) return JsonResponse({'message': 'success'}) except Exception as e: return JsonResponse({'status': 'error', 'message': str(e)}, status=400) return JsonResponse({'status': 'error', 'message': 'Invalid request method'}, status=405) # ============================================ # Edit Price Drop Record - Get Details # ============================================ def price_edit(request): """ Fetch price drop record details for editing AJAX endpoint for retrieving record data """ if request.headers.get('x-requested-with') == 'XMLHttpRequest' and request.method == "POST": data = price_history_table.objects.filter(id=request.POST.get('id')) return JsonResponse(data.values()[0]) # ============================================ # Update Price Drop Record # ============================================ def price_drop_update(request): """ Update existing price drop record Applies role-based access control for updates """ if request.method == "POST": try: category_id = request.POST.get('edit_id') category = price_history_table.objects.get(id=category_id) role_id = request.session.get('role_id') # Check user access for update operation has_access = check_user_access(role_id, 'price_drop', "update") if not has_access: return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to update Price Drop details' }) # Validate form form = PriceForm(request.POST, request.FILES, instance=category) if form.is_valid(): updated_category = form.save(commit=False) # Get updated values from form brand_id = request.POST.get('brand_id') or 0 model_id = request.POST.get('model_id') or 0 variant_id = request.POST.get('variant_id') or 0 from_date = parse_date(request.POST.get('from_date')) desc = request.POST.get('description') db_price = float(request.POST.get('dp_price')) or 0.00 # FSP (Fair Selling Price) fsp = request.POST.get('fsp') or 0 fsp_value = request.POST.get('fsp_value') or 0.00 fsp_discount = request.POST.get('fsp_discount') or 0.00 fsp_after_discount = request.POST.get('fsp_after_discount') or 0.00 # WSP (Wholesale Price) wsp = request.POST.get('wsp') or 0.0 wsp_value = request.POST.get('wsp_value') or 0.00 wsp_discount = request.POST.get('wsp_discount') or 0.00 wsp_after_discount = request.POST.get('wsp_after_discount') or 0.00 # MOP (Minimum Operating Price) mop_value = request.POST.get('mop_value') or 0.00 mop_discount = request.POST.get('mop_discount') or 0.00 mop_after_discount = request.POST.get('mop_after_discount') or 0.00 # BOP (Base Operating Price) bop_value = float(request.POST.get('bop_value')) or 0.00 bop_discount = request.POST.get('bop_discount') or 0.00 bop_after_discount = request.POST.get('bop_after_discount') or 0.00 is_active = request.POST.get('is_active') # ---------------------------------------- # 🚫 DUPLICATE PRICE DROP CHECK (EXCLUDE SELF) # ---------------------------------------- duplicate_exists = price_history_table.objects.filter( branch_id=category.branch_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, from_date=from_date, status=1, is_active=1 ).exclude(id=category_id).exists() if duplicate_exists: return JsonResponse({ 'message': 'error', 'error_message': 'Another price drop already exists for this date. Please change the date or edit the existing record.' }, status=400) # Update record with new values updated_category.brand_id = brand_id updated_category.model_id = model_id updated_category.variant_id = variant_id updated_category.from_date = from_date updated_category.db_price = db_price updated_category.fsp = fsp updated_category.fsp_value = fsp_value updated_category.fsp_discount = fsp_discount updated_category.fsp_after_discount = fsp_after_discount updated_category.wsp = wsp updated_category.wsp_value = wsp_value updated_category.wsp_discount = wsp_discount updated_category.wsp_after_discount = wsp_after_discount updated_category.mop_value = mop_value updated_category.mop_discount = mop_discount updated_category.mop_after_discount = mop_after_discount updated_category.bop_value = bop_value updated_category.bop_discount = bop_discount updated_category.bop_after_discount = bop_after_discount updated_category.is_active = is_active updated_category.description = desc updated_category.updated_by = request.session.get('user_id') updated_category.updated_on = format_datetime(date_time) updated_category.save() return JsonResponse({'message': 'success'}) else: errors = form.errors.as_json() return JsonResponse({'message': 'error', 'errors': errors}) except Exception as e: return JsonResponse({'message': 'exception', 'error': str(e)}) # ============================================ # Delete Price Drop Record # ============================================ def price_delete(request): """ Soft delete price drop record (sets status to 0) Applies role-based access control for deletion """ if request.method == 'POST': data_id = request.POST.get('id') role_id = request.session.get('role_id') has_access, error_message = check_user_access(role_id, 'price_drop', "delete") if not has_access: return JsonResponse({ 'message': 'permission', 'error_message': 'You do not have permission to delete price details' }) try: # Check price history record price_hist = price_history_table.objects.filter(id=data_id).first() if not price_hist: return JsonResponse({'message': 'no such data'}) # Claim amount check if price_hist.claim_amount > 0: return JsonResponse({ 'message': 'restricted', 'error_message': 'Deletion not allowed as claim amount is greater than 0' }) # Sales check for IMEIs with connection.cursor() as cursor: # Query to check if any IMEI from this price drop was sold after the from_date # We check tx_sales (s) joined with tm_sales (m) for the invoice date query = """ SELECT COUNT(*) FROM tx_price_history tph JOIN tx_sales ts ON ts.imei_no = tph.imei_no AND ts.status = 1 JOIN tm_sales tm ON tm.id = ts.tm_sales_id WHERE tph.tm_price_id = %s AND tph.status = 1 AND tm.inv_date >= %s """ cursor.execute(query, [data_id, price_hist.from_date]) count = cursor.fetchone()[0] if count > 0: return JsonResponse({ 'message': 'restricted', 'error_message': 'Deletion not allowed as some IMEIs have been sold after the price drop date' }) # Transfer check for IMEIs # Query to check if any IMEI from this price drop was transferred out after the from_date query_transfer = """ SELECT COUNT(*) FROM tx_price_history tph JOIN tx_material_out tmo ON tmo.imei_no = tph.imei_no AND tmo.status = 1 JOIN tm_material_out mmo ON mmo.id = tmo.tm_material_id WHERE tph.tm_price_id = %s AND tph.status = 1 AND mmo.transfer_date >= %s """ cursor.execute(query_transfer, [data_id, price_hist.from_date]) transfer_count = cursor.fetchone()[0] if transfer_count > 0: return JsonResponse({ 'message': 'restricted', 'error_message': 'Deletion not allowed as some IMEIs have been transferred out after the price drop date' }) # Proceed with soft delete price_history_table.objects.filter(id=data_id).update(status=0) tx_price_history_table.objects.filter(tm_price_id=data_id).update(status=0) return JsonResponse({'message': 'yes'}) except Exception as e: return JsonResponse({'message': 'exception', 'error': str(e)}) return JsonResponse({'message': 'Invalid request method'}) # ============================================ # Get Item Prices # ============================================ def get_item_prices(request): """ Fetch item prices based on brand, model, and variant Returns database price and various pricing tiers (FSP, WSP, MOP, BOP) """ brand_id = request.GET.get('brand_id') model_id = request.GET.get('model_id') variant_id = request.GET.get('variant_id') try: item = item_table.objects.filter( brand_id=brand_id, model_id=model_id, variant_id=variant_id, status=1 ).first() if not item: return JsonResponse({'status': False, 'message': 'Item not found'}) return JsonResponse({ 'status': True, 'db_price': item.db_price or 0, 'fsp_percent': item.franchise_price or 0, 'wsp_percent': item.wholesale_price or 0, 'mop_price': item.mop_price or 0, 'bop_price': item.bop_price or 0, }) except Exception as e: return JsonResponse({'status': False, 'message': str(e)}) # ************************************************************************************************************************************************** def get_price_drop_list(request): if request.method != 'POST': return JsonResponse({'status': 'error', 'message': 'Invalid Request'}) subcategory_id = request.POST.get('subcategory_id') brand_id = request.POST.get('brand_id') model_id = request.POST.get('model_id') variant_id = request.POST.get('variant_id') mode = request.POST.get('mode') edit_id = int(request.POST.get('edit_id') or 0) try: live_data = get_available_imei_stock(subcategory_id, brand_id, model_id, variant_id) data_dict = {str(item['imei_no']): {**item, 'is_preloaded': 0} for item in live_data} if edit_id: history_rows = get_edit_price_imeis(edit_id) for row in history_rows: imei = str(row['imei_no']) if imei in data_dict: live_sold = data_dict[imei]['is_sold'] data_dict[imei].update(row) data_dict[imei]['is_sold'] = live_sold else: data_dict[imei] = row final_data = list(data_dict.values()) # CHECK IF DATA IS EMPTY if not final_data: return JsonResponse({ 'status': 'info', 'message': 'No live stock available for this model.', 'data': [] }) return JsonResponse({'status': 'success', 'data': final_data}) except Exception as e: return JsonResponse({'status': 'error', 'message': str(e)}) def get_available_imei_stock(subcategory_id, brand_id, model_id, variant_id): """ Returns available AND sold IMEIs using a set-based union approach. Excluded: tx_material_out, tx_purchase_return. Included: opening_stock, tx_purchase_inward, tx_material_in, tx_sales_return. """ query = """ SELECT main_stock.imei_no, main_stock.branch_id, b.name AS branch_name, b.is_ho, b.is_store, b.is_franchise, main_stock.mop, main_stock.bop, main_stock.fsp, main_stock.wsp, main_stock.is_wholesale, CAST(0.00 AS DECIMAL(10,2)) AS mop_discount, CAST(0.00 AS DECIMAL(10,2)) AS bop_discount, CAST(0.00 AS DECIMAL(10,2)) AS fsp_discount, CAST(0.00 AS DECIMAL(10,2)) AS wsp_discount, main_stock.mop AS mop_after_discount, main_stock.bop AS bop_after_discount, main_stock.fsp AS fsp_after_discount, main_stock.wsp AS wsp_after_discount, item.sku_text, CASE WHEN (SELECT COUNT(*) FROM tx_sales s WHERE s.imei_no = main_stock.imei_no AND s.branch_id = main_stock.branch_id AND s.status=1 AND s.is_active=1) > (SELECT COUNT(*) FROM tx_sales_return sr WHERE sr.imei_no = main_stock.imei_no AND sr.branch_id = main_stock.branch_id AND sr.status=1 AND sr.is_active=1) THEN 1 ELSE 0 END AS is_sold FROM ( -- Union of all possible valid IMEIs for this model SELECT imei_no, branch_id, MAX(mop) as mop, MAX(bop) as bop, MAX(fsp) as fsp, MAX(wsp) as wsp, COUNT(*) as in_count, MAX(is_wholesale) as is_wholesale FROM ( SELECT imei_no, branch_id, mop, bop, fsp, wsp, 0 as is_wholesale FROM opening_stock WHERE subcategory_id=%s AND brand_id=%s AND model_id=%s AND variant_id=%s AND status=1 AND is_active=1 AND is_demo=0 UNION ALL SELECT imei_no, branch_id, mop, bop, fsp, wsp, 0 as is_wholesale FROM tx_purchase_inward WHERE subcategory_id=%s AND brand_id=%s AND model_id=%s AND variant_id=%s AND status=1 AND is_active=1 AND is_demo = 0 UNION ALL SELECT imei_no, branch_id, mop, bop, fsp, wsp, 0 as is_wholesale FROM tx_material_in WHERE subcategory_id=%s AND brand_id=%s AND model_id=%s AND variant_id=%s AND status=1 AND is_active=1 AND is_demo = 0 UNION ALL SELECT imei_no, branch_id, mop, bop, fsp, wsp, 0 as is_wholesale FROM tx_sales_return WHERE subcategory_id=%s AND brand_id=%s AND model_id=%s AND variant_id=%s AND status=1 AND is_active=1 UNION ALL SELECT imei_no, branch_id, mop, bop, fsp, wsp, 1 as is_wholesale FROM tx_wholesale_in WHERE subcategory_id=%s AND brand_id=%s AND model_id=%s AND variant_id=%s AND status=1 AND is_active=1 AND is_demo = 0 ) inward_pool GROUP BY imei_no, branch_id ) main_stock INNER JOIN branches b ON main_stock.branch_id = b.id LEFT JOIN m_items item ON item.sub_category_id = %s AND item.brand_id = %s AND item.model_id = %s AND item.variant_id = %s WHERE ( (SELECT COUNT(*) FROM tx_purchase_return pr JOIN tm_purchase_return tmpr ON pr.tm_return_id = tmpr.id WHERE pr.imei_no = main_stock.imei_no AND pr.branch_id = main_stock.branch_id AND pr.status=1 AND pr.is_active=1 AND LOWER(tmpr.pr_status) != 'reversed') + (SELECT COUNT(*) FROM tx_material_out mo JOIN tm_material_out tmmo ON mo.tm_material_id = tmmo.id WHERE mo.imei_no = main_stock.imei_no AND mo.branch_id = main_stock.branch_id AND mo.status=1 AND mo.is_active=1 AND LOWER(tmmo.outward_status) != 'rejected') ) < main_stock.in_count ORDER BY b.name, main_stock.imei_no """ params = [ subcategory_id, brand_id, model_id, variant_id, # opening subcategory_id, brand_id, model_id, variant_id, # purchase subcategory_id, brand_id, model_id, variant_id, # material_in subcategory_id, brand_id, model_id, variant_id, # sales_return subcategory_id, brand_id, model_id, variant_id, # wholesale_in subcategory_id, brand_id, model_id, variant_id # item join ] with connection.cursor() as cursor: cursor.execute(query, params) cols = [c[0] for c in cursor.description] return [dict(zip(cols, r)) for r in cursor.fetchall()] def get_edit_price_imeis(edit_id): query = """ SELECT DISTINCT tph.imei_no, tph.branch_id, b.name AS branch_name, b.is_ho, b.is_store, b.is_franchise, tph.mop AS mop, tph.bop AS bop, tph.fsp AS fsp, tph.wsp AS wsp, tph.mop_discount, tph.bop_discount, tph.fsp_discount, tph.wsp_discount, tph.mop_after_discount, tph.bop_after_discount, tph.fsp_after_discount, tph.wsp_after_discount, 'price_edit' AS source, item.sku_text, 1 AS is_preloaded, CASE WHEN (SELECT COUNT(*) FROM tx_sales s WHERE s.imei_no = tph.imei_no AND s.branch_id = tph.supply_branch_id AND s.status = 1) > (SELECT COUNT(*) FROM tx_sales_return sr WHERE sr.imei_no = tph.imei_no AND sr.branch_id = tph.supply_branch_id AND sr.status = 1) THEN 1 ELSE 0 END AS is_sold FROM tx_price_history tph INNER JOIN price_history ph ON ph.id = tph.tm_price_id LEFT JOIN branches b ON b.id = tph.supply_branch_id LEFT JOIN m_items item ON item.sub_category_id = ph.subcategory_id AND item.brand_id = ph.brand_id AND item.model_id = ph.model_id AND item.variant_id = ph.variant_id WHERE tph.tm_price_id = %s AND tph.status = 1 AND tph.is_active = 1 AND tph.imei_no IS NOT NULL """ with connection.cursor() as cursor: cursor.execute(query, [edit_id]) cols = [c[0] for c in cursor.description] return [dict(zip(cols, r)) for r in cursor.fetchall()] def update_price_drop(request): """ Create new price drop record with multiple items Handles main price drop record and transaction details """ if request.method == "POST": try: price_drop_id = request.POST.get('price_drop_id') price_drop = select_row(price_history_table, {'id': price_drop_id}) # Get price fields fsp = request.POST.get('fsp_price') or 0.00 wsp = request.POST.get('wsp_price') or 0.00 mop = request.POST.get('mop_price') or 0.00 bop = request.POST.get('bop_price') or 0.00 credit_note = request.POST.get('credit_note') or 0.00 debit_note = request.POST.get('debit_note') or 0.00 # Parse items list items_json = request.POST.get('items') items_list = json.loads(items_json) if items_json else [] # Get session data current_time = timezone.localtime(timezone.now()) fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) branch_id = request.session.get('branch_id') user_id = request.session.get('user_id') tx_price_history_table.objects.filter(tm_price_id=price_drop_id).delete() tx_entries = [] for item in items_list: tx_entries.append(tx_price_history_table( branch_id=branch_id, tm_price_id=price_drop_id, supply_branch_id=item.get('branch_id'), supplier_id=price_drop.supplier_id, store_type=item.get('store_type'), current_fy=financial_year, imei_no=item.get('imei'), is_applicable=int(item.get('is_applicable', 0)), fsp=item.get('fsp_price') or 0.00, fsp_discount=item.get('fsp_discount') or 0.00, fsp_after_discount=item.get('fsp_after') or 0.00, wsp=item.get('wsp_price') or 0.00, wsp_discount=item.get('wsp_discount') or 0.00, wsp_after_discount=item.get('wsp_after') or 0.00, mop=item.get('mop_price') or 0.00, mop_discount=item.get('mop_discount') or 0.00, mop_after_discount=item.get('mop_after') or 0.00, bop=item.get('bop_price') or 0.00, bop_discount=item.get('bop_discount') or 0.00, bop_after_discount=item.get('bop_after'), credit_fsp=item.get('credit_fsp') or 0.00, credit_wsp=item.get('credit_wsp') or 0.00, created_on=current_time, created_by=user_id )) # Bulk create transaction entries tx_price_history_table.objects.bulk_create(tx_entries) price_drop.fsp = fsp price_drop.wsp = wsp price_drop.mop = mop price_drop.bop = bop price_drop.credit_note = credit_note price_drop.debit_note = debit_note price_drop.save() return JsonResponse({'message': 'success'}) except Exception as e: return JsonResponse({'status': 'error', 'message': str(e)}, status=400) return JsonResponse({'status': 'error', 'message': 'Invalid request method'}, status=405)