from django.shortcuts import render import json from decimal import Decimal 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 * from claim_collect_upgrade.models import * from django.db import transaction as db_transaction from wholesale.models import * from django.db.models import F from slab.models import * from django.db.models.functions import Coalesce # ********************************************************************************************************************************* def claims(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}) 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, 'claim/details.html', {'company': company,'category':category,'supplier':supplier,'branch':branch}) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def claims_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') fyf_name = request.session.get('fyf') financial_year = calculate_financial_year(fyf_name) if user_type == 'stores': 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) claim_no = generate_serial_number(model= tm_claim_table,number_field ='claim_no',financial_year_field ='current_fy',financial_year = financial_year,branch_id= branch_id,prefix="") print(claim_no) return render(request, 'claim/add.html', {'company': company,'category':category,'supplier':supplier,'branch':branch,'claim_no':claim_no}) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def load_claim_details(request): from_date = request.POST.get('from_date') to_date = request.POST.get('to_date') supplier_id = int(request.POST.get('supplier_id') or 0) scheme_type = request.POST.get('scheme_type') mode = request.POST.get('mode') claim_id = int(request.POST.get('claim_id') or 0) claim_status_list = request.POST.getlist('claim_status[]') data = [] if not supplier_id: return JsonResponse({'data': []}) # ------------------------------- # 1. LOAD EXISTING CLAIMS (EDIT) # ------------------------------- existing_claim_map = {} def normalize_scheme(value): return str(value or "").strip().lower().replace(" ", "_") if mode == 'edit' and claim_id: existing_claims = tx_claim_table.objects.filter( tm_claim_id=claim_id, status=1, is_active=1 ).values('scheme_type', 'source_id', 'sales_id', 'amount') existing_claim_map = { (normalize_scheme(c['scheme_type']), c['source_id'], c['sales_id'] or 0): c['amount'] for c in existing_claims } # ------------------------------- # COMMON HELPERS # ------------------------------- def get_ean(brand, model, variant): item = item_table.objects.filter( brand_id=brand, model_id=model, variant_id=variant, status=1 ).first() return item.ean if item else "" # ------------------------------- # BASE QUERY # ------------------------------- base_query = Q(status=1) if supplier_id: base_query &= Q(supplier_id=supplier_id) if from_date and to_date: base_query &= Q(from_date__range=[from_date, to_date]) # ========================================================= # 2. PRICE DROP SCHEME # ========================================================= if not scheme_type or scheme_type == "price_drop": ph_query = price_history_table.objects.filter(base_query) for item in ph_query: key = (normalize_scheme('price_drop'), item.id, 0) # Check for specific match, then try fallback to empty scheme if not found existing_amount = existing_claim_map.get(key) if existing_amount is None: existing_amount = existing_claim_map.get(("", item.id, 0)) is_checked = 1 if existing_amount is not None else 0 # Filter by status UNLESS already checked in current claim if claim_status_list and not is_checked: if item.claim_status not in claim_status_list: continue elif not claim_status_list and not is_checked: # Default behavior if no filter (usually not reachable via UI defaults) if item.claim_status not in ['pending', 'partial', 'claimed']: continue target_total = item.debit_note or 0 already_claimed = item.claim_amount or 0 if is_checked: # Use exactly what was saved in the claim being edited amount = existing_amount else: # Show remaining balance for potential new selection amount = abs(float(target_total) - float(already_claimed)) # Fetch IMEIs for Price Drop imei_list = tx_price_history_table.objects.filter(tm_price_id=item.id, status=1).values_list('imei_no', flat=True) imei_str = ", ".join(filter(None, imei_list)) or "-" data.append({ "id": item.id, "scheme_name": "Price Drop", "price_no": item.price_no, "date": item.from_date.strftime("%d-%m-%Y"), "ean": get_ean(item.brand_id, item.model_id, item.variant_id), "imei_nos": imei_str, "amount": float(amount), "total_amount": float(target_total), "claim_amount": float(already_claimed), "type": "price_drop", "branch": getItemNameById(branch_table, item.branch_id), "sku_text": "", "branch_id": item.branch_id, "sales_id": 0, "source_id": item.id, "supplier": getItemNameById(supplier_table, item.supplier_id), "supplier_id": item.supplier_id, "claim_status": item.claim_status, "is_checked": is_checked }) # ========================================================= # 3. SPECIAL SCHEME # ========================================================= if not scheme_type or scheme_type == "special": relevant_schemes = item_scheme_table.objects.filter( supplier_id=supplier_id, status=1, mop_drop__gt=0 ) for scheme in relevant_schemes: sales_for_scheme = child_sales_order_table.objects.filter( special_id=scheme.id, is_active=1, status=1 ) for sale in sales_for_scheme: # Use Q object to build query dynamically and avoid invalid date range error master_q = Q(id=sale.tm_sales_id, status=1) if from_date and to_date: master_q &= Q(inv_date__range=[from_date, to_date]) master = sales_order_table.objects.filter(master_q).first() if not master: continue key = (normalize_scheme('special'), sale.id, master.id) # Check for specific match, then try fallback to empty scheme if not found existing_amount = existing_claim_map.get(key) if existing_amount is None: existing_amount = existing_claim_map.get(("", sale.id, master.id)) is_checked = 1 if existing_amount is not None else 0 target_total = Decimal(str(scheme.mop_after_drop or 0)) print('Scheme', target_total) already_claimed = Decimal(str(sale.claim_amount or 0)) if is_checked: amount = existing_amount else: amount = abs(float(target_total) - float(already_claimed)) status = 'pending' if already_claimed >= target_total: status = 'claimed' elif already_claimed > 0: status = 'partial' # Filter by status UNLESS already checked in current claim if claim_status_list and not is_checked: if status not in claim_status_list: continue item_info = item_table.objects.filter( brand_id=sale.brand_id, model_id=sale.model_id, variant_id=sale.variant_id, color_id=sale.color_id ).first() data.append({ "id": sale.id, "scheme_name": "Special Scheme", "price_no": master.inv_no, "date": master.inv_date.strftime("%d-%m-%Y"), "ean": sale.ean_number, "imei_nos": sale.imei_no or "-", "amount": float(amount), "total_amount": float(target_total), "mop_drop": float(scheme.mop_drop or 0), "claim_amount": float(already_claimed), "type": "special", "branch": getItemNameById(branch_table, master.branch_id), "sku_text": item_info.sku_text if item_info else "N/A", "branch_id": master.branch_id, "sales_id": master.id, "source_id": sale.id, "supplier_id": scheme.supplier_id, "supplier": getItemNameById(supplier_table, scheme.supplier_id), "claim_status": status, "is_checked": is_checked }) # --- WHOLESALE SPECIAL SCHEME --- w_sales_for_scheme = wholesale_child_sales_order_table.objects.filter( special_id=scheme.id, is_active=1, status=1 ) for w_sale in w_sales_for_scheme: w_master_q = Q(id=w_sale.tm_sales_id, status=1) if from_date and to_date: w_master_q &= Q(inv_date__range=[from_date, to_date]) w_master = wholesale_sales_order_table.objects.filter(w_master_q).first() if not w_master: continue key = (normalize_scheme('special'), w_sale.id, w_master.id) existing_amount = existing_claim_map.get(key) if existing_amount is None: existing_amount = existing_claim_map.get(("", w_sale.id, w_master.id)) is_checked = 1 if existing_amount is not None else 0 w_target = Decimal(str(w_sale.special_amount or 0)) w_claimed = Decimal(str(w_sale.claim_amount or 0)) if is_checked: amount = existing_amount else: amount = abs(float(w_target) - float(w_claimed)) w_status = 'pending' if w_claimed >= w_target: w_status = 'claimed' elif w_claimed > 0: w_status = 'partial' if claim_status_list and not is_checked: if w_status not in claim_status_list: continue item_info = item_table.objects.filter( brand_id=w_sale.brand_id, model_id=w_sale.model_id, variant_id=w_sale.variant_id, color_id=w_sale.color_id ).first() data.append({ "id": w_sale.id, "scheme_name": "Wholesale Special", "price_no": w_master.inv_no, "date": w_master.inv_date.strftime("%d-%m-%Y"), "ean": w_sale.ean_number, "imei_nos": w_sale.imei_no or "-", "amount": float(amount), "total_amount": float(w_target), "mop_drop": float(scheme.mop_drop or 0), "claim_amount": float(w_claimed), "type": "wholesale_special", "branch": getItemNameById(branch_table, w_master.branch_id), "sku_text": item_info.sku_text if item_info else "N/A", "branch_id": 0, "sales_id": w_master.id, "source_id": w_sale.id, "supplier_id": scheme.supplier_id, "supplier": getItemNameById(supplier_table, scheme.supplier_id), "claim_status": w_status, "is_checked": is_checked, "customer_name": w_master.customer_name }) # ========================================================= # 4. SLAB SALES SCHEME # ========================================================= if not scheme_type or scheme_type == "slab_sales": slab_query = slab_sales_table.objects.filter(base_query) for item in slab_query: key = (normalize_scheme('slab_sales'), item.id, item.id) existing_amount = existing_claim_map.get(key) if existing_amount is None: existing_amount = existing_claim_map.get(("", item.id, item.id)) if existing_amount is None: existing_amount = existing_claim_map.get(("", item.id, 0)) is_checked = 1 if existing_amount is not None else 0 # Filter by status UNLESS already checked in current claim if claim_status_list and not is_checked: if item.claim_status not in claim_status_list: continue target_total = Decimal(str(item.credit_note or 0)) already_claimed = Decimal(str(item.claim_amount or 0)) if is_checked: amount = existing_amount else: amount = abs(float(target_total) - float(already_claimed)) # Fetch IMEIs for Slab Sales # Since tx_slab_sales don't store IMEIs directly, we fetch from child_sales_order_table # matching the slab's criteria (branch, brand, date range) sales_imeis = child_sales_order_table.objects.filter( tm_sales_id__in=sales_order_table.objects.filter( inv_date__range=[item.from_date, item.to_date], branch_id=item.supply_branch_id, status=1, sales_status='approved' ), brand_id=item.brand_id, status=1 ).values_list('imei_no', flat=True) imei_str = ", ".join(filter(None, sales_imeis)) or "-" data.append({ "id": item.id, "scheme_name": "Slab Sales", "price_no": item.slab_no, "date": item.date.strftime("%d-%m-%Y"), "ean": "-", "imei_nos": imei_str, "amount": float(amount), "total_amount": float(target_total), "claim_amount": float(already_claimed), "type": "slab_sales", "branch": getItemNameById(branch_table, item.supply_branch_id), "sku_text": "", "branch_id": item.supply_branch_id, "sales_id": item.id, "source_id": item.id, "supplier": getItemNameById(supplier_table, item.supplier_id), "supplier_id": item.supplier_id, "claim_status": item.claim_status, "is_checked": is_checked }) return JsonResponse({'data': data}) def add_claim(request): if request.method != 'POST': return JsonResponse({'message': 'Invalid request'}, status=400) role_id = request.session.get('role_id') has_access, error_message = check_user_access(role_id, 'claim', "create") if not has_access: return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to add claim details'}) try: # 1. Fetch Request Data 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) from_date = request.POST.get('from_date') to_date = request.POST.get('to_date') supplier_id = int(request.POST.get('supplier_id') or 0) debit_no = request.POST.get('debit_note_no') or None debit_date = request.POST.get('debit_note_date') or None scheme_type = request.POST.get('scheme_type') or None total_selected_amt = Decimal(str(request.POST.get('total_amount') or 0.00)) actual_debit_amt = Decimal(str(request.POST.get('debit_amount') or 0.00)) remarks = request.POST.get('description') now = timezone.localtime(timezone.now()) items = json.loads(request.POST.get('items') or '[]') if not items: return JsonResponse({'message': 'warning', 'error_message': 'No items added'}) ratio = Decimal('0.00') if total_selected_amt > 0: ratio = actual_debit_amt / total_selected_amt # Collect distinct scheme names from items distinct_schemes = sorted(list(set(str(item.get('scheme_name') or '').strip().lower() for item in items if item.get('scheme_name')))) combined_scheme_type = ",".join(distinct_schemes) if distinct_schemes else scheme_type inv_no = generate_serial_number( model=tm_claim_table, number_field='claim_no', financial_year_field='current_fy', financial_year=financial_year, branch_id=branch_id, ) with db_transaction.atomic(): # 3. Create Master Claim Record main = tm_claim_table.objects.create( company_id=company_id, scheme_type=combined_scheme_type, branch_id=branch_id, current_fy=financial_year, supplier_id=supplier_id, debit_note_no=debit_no, debit_note_date=debit_date, from_date=from_date, to_date=to_date, claim_no=inv_no, date=now.date(), time=now.time(), total_amount=total_selected_amt, debit_amount=actual_debit_amt, balance=total_selected_amt - actual_debit_amt, remarks=remarks, created_by=user_id, employee_id=user_id, created_on=now, updated_on=now, status=1, is_active=1, ) for item in items: original_item_amt = Decimal(str(item.get('amount') or 0)) item_share = (original_item_amt * ratio).quantize(Decimal('0.01')) print(f"Original Amount: {original_item_amt}, Share: {item_share}") formatted_date = datetime.strptime(item['inv_date'], "%d-%m-%Y").strftime("%Y-%m-%d") # 4. Create Transaction Record tx_claim_table.objects.create( company_id=company_id, branch_id=branch_id, current_fy=financial_year, tm_claim_id=main.id, sales_id=item['sales_id'], supplier_id=supplier_id, supply_branch_id=item['branch_id'], inv_no=item['price_no'], inv_date=formatted_date, scheme_type=item.get('scheme_name'), # Use item's scheme name ean=item['ean'], amount=item['amount'], source_id=item['source_id'], status=1, is_active=1, created_by=user_id, updated_by=user_id, created_on=now, updated_on=now, ) scheme_name = str(item.get('scheme_name', '')).lower().strip() print(f"Scheme Name: {scheme_name}, Item Share: {item_share}") source_id = item.get('source_id') # 5. HANDLE PRICE DROP UPDATE if scheme_name == 'price_drop' and source_id: history_item = price_history_table.objects.filter(id=source_id).first() if history_item: current_claimed = Decimal(str(history_item.claim_amount or 0)) target_to_reach = Decimal(str(history_item.debit_note or 0)) new_total = current_claimed + item_share status_text = 'pending' if new_total >= target_to_reach: status_text = 'claimed' elif new_total > 0: status_text = 'partial' price_history_table.objects.filter(id=source_id).update( claim_amount=Coalesce(F('claim_amount'), Decimal('0.00')) + item_share, claim_status=status_text, is_claim=1 if status_text == 'claimed' else 0, updated_on=now ) # 6. HANDLE SPECIAL SCHEME UPDATE if scheme_name == 'special' and source_id: sales_record = child_sales_order_table.objects.filter(id=source_id).first() if sales_record: item_target = Decimal(str(sales_record.special_amount or 0)) item_new_total = Decimal(str(sales_record.claim_amount or 0)) + item_share is_fully_claimed = 1 if item_new_total >= item_target else 0 child_sales_order_table.objects.filter(id=source_id).update( claim_amount=item_new_total, is_claim=is_fully_claimed, claimed_on=now, updated_on=now ) # Update Master Scheme status if necessary if sales_record.special_id: update_master_scheme_status(sales_record.special_id, user_id, now,'special') # 6.5 HANDLE WHOLESALE SPECIAL SCHEME UPDATE if scheme_name == 'wholesale_special' and source_id: w_sale = wholesale_child_sales_order_table.objects.filter(id=source_id).first() if w_sale: w_target = Decimal(str(w_sale.special_amount or 0)) w_new_total = Decimal(str(w_sale.claim_amount or 0)) + item_share is_fully_claimed = 1 if w_new_total >= w_target else 0 wholesale_child_sales_order_table.objects.filter(id=source_id).update( claim_amount=w_new_total, is_claim=is_fully_claimed, claimed_on=now, updated_on=now ) if w_sale.special_id: update_master_scheme_status(w_sale.special_id, user_id, now, 'special') # 7. HANDLE SLAB SALES UPDATE if scheme_name == 'slab_sales' and source_id: slab_item = slab_sales_table.objects.filter(id=source_id).first() if slab_item: current_claimed = Decimal(str(slab_item.claim_amount or 0)) target_to_reach = Decimal(str(slab_item.credit_note or 0)) new_total = current_claimed + item_share status_text = 'pending' if new_total >= target_to_reach: status_text = 'claimed' elif new_total > 0: status_text = 'partial' slab_sales_table.objects.filter(id=source_id).update( claim_amount=Coalesce(F('claim_amount'), Decimal('0.00')) + item_share, claim_status=status_text, updated_on=now ) print(f"Slab Item ID: {source_id}, Current Claimed: {current_claimed}, Target: {target_to_reach}, New Total: {new_total}") return JsonResponse({'message': 'success'}) except Exception as e: return JsonResponse({'message': 'exception', 'error': str(e)}, status=500) # Helper function to clean up the loop logic def update_master_scheme_status(scheme_id, user_id, now, scheme_type): print(f"\n--- Updating Scheme Status ---") print(f"Scheme ID: {scheme_id}, Type: {scheme_type}") if scheme_type == 'special': # Retail Items retail_items = child_sales_order_table.objects.filter( special_id=scheme_id, is_active=1 ) # Wholesale Items wholesale_items = wholesale_child_sales_order_table.objects.filter( special_id=scheme_id, is_active=1 ) total = retail_items.count() + wholesale_items.count() claimed = retail_items.filter(is_claim=1).count() + wholesale_items.filter(is_claim=1).count() any_paid = retail_items.filter(claim_amount__gt=0).exists() or \ wholesale_items.filter(claim_amount__gt=0).exists() print("SPECIAL SCHEME (Retail + Wholesale)") print(f"Total Items : {total}") print(f"Claimed Items : {claimed}") print(f"Any Paid? : {any_paid}") elif scheme_type == 'upgrade': all_items = child_sales_order_table.objects.filter( upgrade_id=scheme_id, status=1 ) total = all_items.count() claimed = all_items.filter(is_upgrade_approved=1).count() any_paid = all_items.filter(upgrade_collect__gt=0).exists() print("UPGRADE SCHEME") print(f"Total Items : {total}") print(f"Approved : {claimed}") print(f"Any Paid? : {any_paid}") else: print("⚠️ Unknown scheme type") return status = 'pending' if total > 0: if claimed == total: status = 'claimed' elif any_paid: status = 'partial' print(f"Final Status : {status}") rows = item_scheme_table.objects.filter(id=scheme_id).update( claim_status=status, updated_on=now, updated_by=user_id ) print(f"Rows Updated: {rows}") print("✅ Scheme status updated successfully") def ajax_claim_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) from_date = request.POST.get('from_date') to_date = request.POST.get('to_date') has_access, error_message = check_user_access(role_id, 'claim', "read") if not has_access: return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to view the claim details'}) query = Q(status=1, branch_id=branch_id,current_fy=financial_year) if from_date and to_date: query &= Q(date__range=[from_date, to_date]) data = list(selectList(tm_claim_table, query).values()) scheme_map = { "price_drop": "Price Drop", "slab_sales": "Slab Sales", "special": "Special", } formatted = [] for index, item in enumerate(data): raw_schemes = str(item['scheme_type'] or '').split(',') display_schemes = [scheme_map.get(s.strip().lower(), s.strip().title()) for s in raw_schemes if s.strip()] scheme_display = ", ".join(display_schemes) if display_schemes else "-" formatted.append({ 'id': index + 1, 'action': ( '
' '' '' '
' ).format(item['id'], item['id'], item['id'], item['id']), 'debit_note_no' : item['debit_note_no'] if item['debit_note_no'] else '-', 'debit_note_date' : format_date_month_year(item['debit_note_date']) if item['debit_note_date'] else '-', 'total_amount' : format_amount(item['total_amount'])if item['total_amount'] else '-', 'debit' : format_amount(item['debit_amount']) if item['debit_amount'] else '-', 'balance' : format_amount(item['balance']) if item['balance'] else '-', 'remarks' : item['remarks'] if item['remarks'] else '-', 'supplier_name' : getItemNameById(supplier_table, item['supplier_id']), 'scheme' : scheme_display, 'status': 'Active' if item['is_active'] else 'Inactive' # Assuming is_active is a boolean field }) return JsonResponse({'data': formatted}) def claims_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}) 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) claim = select_row(tm_claim_table, {'id': decoded_id}) print('claim',claim) return render(request, 'claim/edit.html', {'company': company,'category':category,'supplier':supplier,'branch':branch,'claim':claim}) else: return HttpResponseRedirect("/") else: return HttpResponseRedirect("/") def update_claim(request): if request.method != 'POST': return JsonResponse({'message': 'Invalid request'}, status=400) role_id = request.session.get('role_id') has_access, error_message = check_user_access(role_id, 'claim', "create") if not has_access: return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to add claim details'}) try: claim_id = request.POST.get('claim_id') claim = select_row(tm_claim_table, {'id': claim_id}) if not claim: return JsonResponse({'message': 'warning', 'error_message': 'Claim not found'}) # 1. Fetch Request Data 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) debit_no = request.POST.get('debit_note_no') or None debit_date = request.POST.get('debit_note_date') or None total_selected_amt = Decimal(str(request.POST.get('total_amount') or 0.00)) actual_debit_amt = Decimal(str(request.POST.get('debit_amount') or 0.00)) scheme_type = request.POST.get('scheme_type') or None remarks = request.POST.get('description') now = timezone.localtime(timezone.now()) items = json.loads(request.POST.get('items') or '[]') if not items: return JsonResponse({'message': 'warning', 'error_message': 'No items added'}) ratio = Decimal('0.00') if total_selected_amt > 0: ratio = actual_debit_amt / total_selected_amt with db_transaction.atomic(): # ========================================================= # REVERT OLD DATA BEFORE APPLYING NEW UPDATES # ========================================================= old_tx_records = tx_claim_table.objects.filter(tm_claim_id=claim.id, status=1) for old_tx in old_tx_records: old_scheme = str(old_tx.scheme_type or '').lower().strip() # 1. Revert Price Drop if old_scheme == 'price_drop': # Just subtract. The loop below will recalculate the correct status. price_history_table.objects.filter(id=old_tx.source_id).update( claim_amount=Coalesce(F('claim_amount'), Decimal('0.00')) - old_tx.amount, claim_status='partial', is_claim=0, updated_on=now ) # 2. Revert Special Scheme elif old_scheme == 'special': child_sales_order_table.objects.filter(id=old_tx.source_id).update( claim_amount=F('claim_amount') - old_tx.amount, is_claim=0, updated_on=now ) # 3. Revert Slab Sales elif old_scheme == 'slab_sales' or old_scheme == 'slab sales': slab_sales_table.objects.filter(id=old_tx.source_id).update( claim_amount=Coalesce(F('claim_amount'), Decimal('0.00')) - old_tx.amount, claim_status='partial', updated_on=now ) # 4. Revert Wholesale Special elif old_scheme == 'wholesale_special': wholesale_child_sales_order_table.objects.filter(id=old_tx.source_id).update( claim_amount=F('claim_amount') - old_tx.amount, is_claim=0, updated_on=now ) # Clean up old transaction records old_tx_records.delete() # Process new items for item in items: original_item_amt = Decimal(str(item.get('amount') or 0)) item_share = (original_item_amt * ratio).quantize(Decimal('0.01')) formatted_date = datetime.strptime(item['inv_date'], "%d-%m-%Y").strftime("%Y-%m-%d") # 4. Create Transaction Record tx_claim_table.objects.create( company_id=company_id, branch_id=branch_id, current_fy=financial_year, tm_claim_id=claim.id, sales_id=item['sales_id'], supplier_id=claim.supplier_id, supply_branch_id=item['branch_id'], inv_no=item['price_no'], inv_date=formatted_date, scheme_type=item.get('scheme_name'), # Use item's scheme name ean=item['ean'], amount=item['amount'], # Save only the distributed share source_id=item['source_id'], status=1, is_active=1, created_by=user_id, updated_by=user_id, created_on=now, updated_on=now, ) scheme_name = str(item.get('scheme_name', '')).lower().strip() source_id = item.get('source_id') # 5. HANDLE PRICE DROP UPDATE if scheme_name == 'price_drop' and source_id: history_item = price_history_table.objects.filter(id=source_id).first() if history_item: current_claimed = Decimal(str(history_item.claim_amount or 0)) target_to_reach = Decimal(str(history_item.debit_note or 0)) new_total = current_claimed + item_share status_text = 'pending' if new_total >= target_to_reach: status_text = 'claimed' elif new_total > 0: status_text = 'partial' price_history_table.objects.filter(id=source_id).update( claim_amount=Coalesce(F('claim_amount'), Decimal('0.00')) + item_share, claim_status=status_text, is_claim=1 if status_text == 'claimed' else 0, updated_on=now ) # 6. HANDLE SPECIAL SCHEME UPDATE if scheme_name == 'special' and source_id: sales_record = child_sales_order_table.objects.filter(id=source_id).first() if sales_record: item_target = Decimal(str(sales_record.special_amount or 0)) item_new_total = Decimal(str(sales_record.claim_amount or 0)) + item_share is_fully_claimed = 1 if item_new_total >= item_target else 0 child_sales_order_table.objects.filter(id=source_id).update( claim_amount=item_new_total, is_claim=is_fully_claimed, claimed_on=now, updated_on=now ) # Update Master Scheme status if necessary if sales_record.special_id: update_master_scheme_status(sales_record.special_id, user_id, now,'special') # 6.5 HANDLE WHOLESALE SPECIAL SCHEME UPDATE if scheme_name == 'wholesale_special' and source_id: w_sale = wholesale_child_sales_order_table.objects.filter(id=source_id).first() if w_sale: w_target = Decimal(str(w_sale.special_amount or 0)) w_new_total = Decimal(str(w_sale.claim_amount or 0)) + item_share is_fully_claimed = 1 if w_new_total >= w_target else 0 wholesale_child_sales_order_table.objects.filter(id=source_id).update( claim_amount=w_new_total, is_claim=is_fully_claimed, claimed_on=now, updated_on=now ) if w_sale.special_id: update_master_scheme_status(w_sale.special_id, user_id, now, 'special') # 7. HANDLE SLAB SALES UPDATE if scheme_name == 'slab sales' or scheme_name == 'slab_sales': slab_item = slab_sales_table.objects.filter(id=source_id).first() if slab_item: current_claimed = Decimal(str(slab_item.claim_amount or 0)) target_to_reach = Decimal(str(slab_item.credit_note or 0)) new_total = current_claimed + item_share status_text = 'pending' if new_total >= target_to_reach: status_text = 'claimed' elif new_total > 0: status_text = 'partial' slab_sales_table.objects.filter(id=source_id).update( claim_amount=Coalesce(F('claim_amount'), Decimal('0.00')) + item_share, claim_status=status_text, updated_on=now ) # Update master claim record distinct_schemes = sorted(list(set(str(item.get('scheme_name') or '').strip().lower() for item in items if item.get('scheme_name')))) combined_scheme_type = ",".join(distinct_schemes) if distinct_schemes else scheme_type claim.scheme_type = combined_scheme_type claim.debit_note_no = debit_no claim.debit_note_date = debit_date claim.total_amount= total_selected_amt claim.debit_amount= actual_debit_amt claim.balance = total_selected_amt - actual_debit_amt claim.remarks = remarks claim.save() return JsonResponse({'message': 'success'}) except Exception as e: return JsonResponse({'message': 'exception', 'error': str(e)}, status=500)