from django.core.management.base import BaseCommand
from django.utils.timezone import now
from django.http import JsonResponse
from datetime import datetime
from django.shortcuts import render, get_object_or_404
from privilege.models import *
from common.models import *
from company.models import *
from masters.models import *
from store.models import *
from employee.models import *
from django.db.models import Max
from django.db.models import Max, F, Q
from django.utils import timezone
import base64
import re
from datetime import datetime, timedelta
from common.cash_bank_utils import *
# ***************************************************************************************************************************************************************
def week_of_month(dt):
first_day = dt.replace(day=1)
dom = dt.day
adjusted_dom = dom + first_day.weekday()
return int((adjusted_dom - 1) / 7) + 1
from datetime import datetime
def parse_datetime(value):
if value:
try:
return datetime.strptime(value, "%Y-%m-%d %H:%M:%S")
except ValueError:
return None
return None
# DATE FORMAT
def format_datetime(date):
return date.strftime('%Y-%m-%d %H:%M:%S') if date else None
def format_date(date):
return date.strftime('%Y-%m-%d') if date else None
def format_time(date):
return date.strftime('%H:%M:%S') if date else None
def format_hr_m(date):
return date.strftime('%H:%M') if date else None
from datetime import datetime
def format_date_month_year(date):
if not date:
return None
if isinstance(date, str):
date = datetime.strptime(date, '%Y-%m-%d').date()
return date.strftime('%d-%m-%Y')
def format_date_time_month_year(date):
if not date:
return None
if isinstance(date, str):
try:
date = datetime.strptime(date, '%Y-%m-%d %H:%M:%S')
except ValueError:
date = datetime.strptime(date, '%Y-%m-%d').date()
return date.strftime('%d-%m-%Y %H:%M:%S')
def get_current_time():
dt = timezone.localtime(timezone.now()) # IST aware datetime
return dt.strftime('%H:%M:%S') # or format_time(dt) if you have custom formatting
current_time = get_current_time()
def get_current_datetime():
return timezone.localtime(timezone.now()) # returns datetime object
date_time = get_current_datetime()
def normalize_date(date_str):
if not date_str or date_str in ["0000-00-00", "0000-00-00 00:00:00"]:
return None
return date_str
def parse_date(date_str):
"""
Tries to parse various common date formats and convert to YYYY-MM-DD.
Returns None if parsing fails.
"""
if not date_str:
return None
date_formats = ['%Y-%m-%d', '%d-%m-%Y', '%m/%d/%Y', '%d/%m/%Y', '%Y/%m/%d']
for fmt in date_formats:
try:
return datetime.strptime(date_str, fmt).date()
except ValueError:
continue
return None # If no format matched
def get_prev_date(date_obj=None):
if not date_obj:
date_obj = timezone.now().date()
return date_obj - timedelta(days=1)
# ***************************************************************************************************************************************************************
def format_amount(value):
try:
if value is None:
return "0.00" # or '-' if you prefer blank for None
return f"{float(value):.2f}"
except (ValueError, TypeError):
return "0.00"
def format_number(value):
if value is None:
return 0
return int(value) if value == int(value) else round(value, 2)
# ***************************************************************************************************************************************************************
# get name against id
def getItemNameById(tbl, cat_id):
try:
category = tbl.objects.get(id=cat_id).name
return category
except tbl.DoesNotExist:
return '-'
except Exception as e:
return '-'
def getItemIdByName(tbl, name):
try:
return tbl.objects.get(name=name).id
except tbl.DoesNotExist:
return 0
except Exception:
return 0
# get sku
def getItemObjectById(table, item_id):
return table.objects.filter(id=item_id, status=1).values('name', 'sku').first()
# ***************************************************************************************************************************************************************
# get name with badge
def getNameByBadge(tbl, cat_id):
try:
name = tbl.objects.get(id=cat_id).name
return f'{name}'
except tbl.DoesNotExist:
return 'NA'
except Exception:
return 'NA'
# ***************************************************************************************************************************************************************
#DISPLAY DATA IN BADGE PRIVILEGE
def format_badge(value, mapping=None, label_mapping=None, default_class='badge text-bg-secondary', empty_display='-'):
"""
Returns an HTML badge with customizable CSS and label.
Args:
value (str): The internal value.
mapping (dict): Maps values to CSS class names.
label_mapping (dict): Maps values to display labels.
default_class (str): Fallback CSS class.
empty_display (str): Display if value is None or empty.
Returns:
str: HTML badge string.
"""
if not value:
return empty_display
css_class = mapping.get(value, default_class) if mapping else default_class
label = label_mapping.get(value, value) if label_mapping else value
return f'{label}'
# ***************************************************************************************************************************************************************
# USER PRIVILEGE
def check_user_access(role_id, keyword, action_type):
try:
module = module_table.objects.get(keyword=keyword, status=1)
except module_table.DoesNotExist:
return False, "Module not found"
try:
privilege = privilege_table.objects.get(
role_id=role_id,
module_id=module.id,
is_active=1,
status=1
)
except privilege_table.DoesNotExist:
return False, "Access denied"
action_type = action_type.lower()
if action_type not in ['create', 'read', 'update', 'delete']:
return False, "Invalid action type"
if action_type == 'create' and not privilege.is_create:
return False, "You don’t have permission to create these details."
elif action_type == 'read' and not privilege.is_read:
return False, "You don’t have permission to read these details."
elif action_type == 'update' and not privilege.is_update:
return False, "You don’t have permission to update these details."
elif action_type == 'delete' and not privilege.is_delete:
return False, "You don’t have permission to delete these details."
return True, "Access granted"
# ***************************************************************************************************************************************************************
#NUM SERIES
def generate_num_series(model, field_name='num_series', padding=4):
max_value = model.objects.aggregate(max_val=Max(field_name))['max_val']
try:
current = int(max_value) if max_value else 0
except ValueError:
current = 0
return str(current + 1).zfill(padding)
# ***************************************************************************************************************************************************************
#select a single row based on table and where condition
def selectList(tbl, whr=None, order_by='-id', status_filter=True, fields=None):
"""
Fetch multiple rows from the table `tbl` based on the condition `whr` (can be dict or Q object),
and optional `order_by` argument. Always includes condition status=1 by default.
category = selectList(category_table, {'some_field': some_value}, order_by='name')
"""
query = Q(status=1) if status_filter else Q()
if whr:
if isinstance(whr, dict):
query &= Q(**whr)
elif isinstance(whr, Q):
query &= whr
else:
raise ValueError("Invalid 'whr' argument. Must be dict, Q object, or None.")
queryset = tbl.objects.filter(query).order_by(order_by)
if fields:
return queryset.values(*fields)
return queryset
from django.db.models import Q
def select_row(tbl, whr=None, exclude=None):
"""
Fetch a single row from the given table `tbl` with the condition `whr`.
Always includes condition status=1 by default.
Optionally supports exclude conditions.
Args:
tbl: Django model/table.
whr: dict | Q | None -> filter conditions.
exclude: dict | Q | None -> exclude conditions.
"""
query = Q(status=1)
# Apply filters
if whr is not None:
if isinstance(whr, dict):
query &= Q(**whr)
elif isinstance(whr, Q):
query &= whr
else:
raise ValueError("Invalid 'whr' argument. Must be dict, Q object, or None.")
qs = tbl.objects.filter(query)
# Apply excludes
if exclude is not None:
if isinstance(exclude, dict):
qs = qs.exclude(**exclude)
elif isinstance(exclude, Q):
qs = qs.exclude(exclude)
else:
raise ValueError("Invalid 'exclude' argument. Must be dict, Q object, or None.")
return qs.first()
def employee_list(request):
branch_id = request.session.get('branch_id')
user_id = request.session.get('user_id')
employees = selectList(employee_table, {'branch_id': branch_id})
for emp in employees:
emp.selected = (emp.id == user_id) # Assuming employee_table has a user_id field linked to auth user
return employees
# ***************************************************************************************************************************************************************
#DECODE ID*
def decode_base64_id(encoded_id):
try:
decoded_bytes = base64.b64decode(encoded_id)
return decoded_bytes.decode('utf-8')
except (base64.binascii.Error, UnicodeDecodeError, AttributeError):
return None
# ***************************************************************************************************************************************************************
# EMPLOYEE CODE
def generate_company_code(company_id):
prefix = 'EMP'
company = company_table.objects.filter(id=company_id, status=1).first()
if company and company.company_code:
prefix = company.company_code
latest = employee_table.objects.filter(employee_code__startswith=prefix + "-") \
.aggregate(Max('num_series'))
last_number = latest['num_series__max']
next_number = int(last_number) + 1 if last_number and last_number.isdigit() else 1
num_series = str(next_number).zfill(3)
employee_code = f"{prefix}-{num_series}"
return employee_code
# ***************************************************************************************************************************************************************
def generate_branch_code(branch_id):
prefix = 'EMP'
branch = branch_table.objects.filter(id=branch_id, status=1).first()
if branch and branch.prefix:
prefix = branch.prefix
latest = employee_table.objects.filter(employee_code__startswith=prefix + "-") \
.aggregate(Max('num_series'))
last_number = latest['num_series__max']
next_number = int(last_number) + 1 if last_number and last_number.isdigit() else 1
num_series = str(next_number).zfill(3)
employee_code = f"{prefix}-{num_series}"
return employee_code
def generate_wholesale_employee_code(branch_id=None):
"""
Generate wholesale employee code in WS-001 format without duplication.
Scope is wholesale stores (is_wholesale=1, store_type='wholesale').
"""
wholesale_branch_ids = list(
branch_table.objects.filter(is_wholesale=1, store_type='wholesale', status=1)
.values_list('id', flat=True)
)
employee_query = employee_table.objects.filter(status=1)
if wholesale_branch_ids:
employee_query = employee_query.filter(
Q(branch_id__in=wholesale_branch_ids) |
Q(is_wholesale=1, store_type='wholesale')
)
else:
employee_query = employee_query.filter(Q(is_wholesale=1, store_type='wholesale'))
existing_codes = employee_query.exclude(employee_code__isnull=True).exclude(employee_code='') \
.values_list('employee_code', flat=True)
used_numbers = set()
for code in existing_codes:
match = re.fullmatch(r'WS-(\d{3,})', str(code).strip())
if match:
used_numbers.add(int(match.group(1)))
next_number = 1
while next_number in used_numbers:
next_number += 1
return f"WS-{next_number:03d}"
# ***************************************************************************************************************************************************************
#PURCHASE CODE
from django.db.models import Max
from django.db.models import Max
def generate_serial_number(model, number_field, financial_year_field, financial_year, branch_id, prefix="", padding=3):
# Filter per branch and financial year
filter_kwargs = {
financial_year_field: financial_year,
"branch_id": branch_id,
"status": 1
}
max_kwargs = {f"{number_field}__max": Max(number_field)}
latest_number = model.objects.filter(**filter_kwargs).aggregate(**max_kwargs)[f"{number_field}__max"]
if latest_number:
try:
# Strip prefix if present
numeric_part = latest_number.replace(prefix, "") if prefix else latest_number
last_series = int(numeric_part)
new_series = last_series + 1
except ValueError:
new_series = 1
else:
new_series = 1
formatted_series = f"{new_series:0{padding}}"
new_serial = f"{prefix}{formatted_series}"
return new_serial
# ***************************************************************************************************************************************************************
from django.db.models import Max, F, Q,Sum
from django.utils import timezone
def update_summary(parent_model, child_model, parent_id_field, parent_id, quantity_field='quantity', amount_field='amount', status_field='status'):
try:
filter_kwargs = {
parent_id_field: parent_id,
status_field: 1 # assuming 1 means active
}
summary = child_model.objects.filter(**filter_kwargs).aggregate(
total_quantity=Sum(quantity_field),
total_amount=Sum(amount_field)
)
total_quantity = summary['total_quantity'] or 0
total_amount = summary['total_amount'] or 0
# Fetch the parent instance
parent_instance = parent_model.objects.filter(id=parent_id).first()
if parent_instance:
cash = parent_instance.cash or 0
bank = parent_instance.bank or 0
balance = total_amount - (cash + bank)
# Update the parent record
parent_instance.total_quantity = total_quantity
parent_instance.total_amount = total_amount
parent_instance.balance = balance
parent_instance.updated_on = timezone.now()
parent_instance.save()
else:
print(f"Parent record with id={parent_id} not found.")
except Exception as e:
print(f"Error updating summary for {parent_model.__name__} id={parent_id}: {str(e)}")
# ***************************************************************************************************************************************************************
#FINANCIAL YEAR
def calculate_financial_year(fyf_name):
"""
Calculate the current financial year based on the given fiscal year name.
The fiscal year name format is expected to be 'YYYY - YYYY'.
financial_year2 = calculate_financial_year(fyf_name)
"""
if fyf_name and '-' in fyf_name:
fyf_start, fyf_end = fyf_name.split('-')
fyf_start = int(fyf_start.strip())
fyf_end = int(fyf_end.strip())
today = datetime.now()
month = today.month
if month >= 3:
return 2025
else:
return 2025
return None # Return None if the format is incorrect
# ***************************************************************************************************************************************************************
def comma_separated(id_string):
"""
Convert a comma-separated string into a list of integers.
Handles empty strings and strips whitespace.
"""
if not id_string:
return []
return [int(i.strip()) for i in id_string.split(',') if i.strip().isdigit()]
# ***************************************************************************************************************************************************************
# ***************************************************************************************************************************************************************
from datetime import datetime, time
from django.utils.timezone import now
# ***************************************************************************************************************************************************************
def has_negative_values(data, fields):
"""
Checks if any given field in `data` has a negative value.
`data` is a dictionary (e.g., request.POST).
"""
for field in fields:
try:
value = float(data.get(field, 0))
if value < 0:
return True, f"{field.replace('_', ' ').title()} cannot be negative."
except (ValueError, TypeError):
continue
return False, ""
def parse_po_ids(po_ids_raw):
"""
Parses comma-separated PO IDs into a list of integers.
"""
if not po_ids_raw:
return []
return [int(pid.strip()) for pid in str(po_ids_raw).split(',') if pid.strip().isdigit()]
# ***************************************************************************************************************************************************************
from inventory.models import *
from sales.models import *
from stock.models import *
def get_stock_detail(item_id, sales_id, batch_no, branch_id, financial_year):
# ✅ FETCH ALL OPENING STOCK ROWS
stock_qs = opening_stock_table.objects.filter(
stock_no=batch_no,
item_id=item_id,
branch_id=branch_id,
current_fy=financial_year,
status=1
)
# ✅ SUM OPENING STOCK AND TAKE LATEST MRP
opening_stock_total = stock_qs.aggregate(
total=Sum('opening_stock')
)['total'] or 0
latest_opening_row = stock_qs.order_by('-created_on').first()
opening_mrp = latest_opening_row.mrp if latest_opening_row else 0
# ✅ SALES
sales_qs = child_sales_order_table.objects.filter(
batch_no=batch_no,
item_id=item_id,
branch_id=branch_id,
status=1
)
if sales_id and sales_id != 0:
sales_qs = sales_qs.exclude(tm_sales_id=sales_id)
sales = sales_qs.aggregate(total=Sum('quantity'))['total'] or 0
# ✅ SALES RETURN
sales_return = child_sales_return_table.objects.filter(
item_id=item_id,
sales_id__in=child_sales_order_table.objects.filter(
batch_no=batch_no
).values_list('tm_sales_id', flat=True),
branch_id=branch_id,
status=1
).aggregate(total=Sum('quantity'))['total'] or 0
# ✅ IF OPENING STOCK EXISTS → USE IT
if opening_stock_total > 0:
available = opening_stock_total - sales + sales_return
return {
'available': available,
'stock_for_sale': available,
'purchase': 0,
'purchase_return': 0,
'sales': sales,
'sales_return': sales_return,
'mrp': opening_mrp,
}
# ------------------------------------------------------------
# ✅ FALLBACK TO PURCHASE LOGIC (NO OPENING STOCK FOUND)
# ------------------------------------------------------------
purchase_qty = child_purchase_inward_table.objects.filter(
item_id=item_id,
branch_id=branch_id,
batch_no=batch_no,
status=1
).aggregate(total=Sum('quantity'))['total'] or 0
last_purchase = child_purchase_inward_table.objects.filter(
item_id=item_id,
branch_id=branch_id,
batch_no=batch_no,
status=1
).order_by('-created_on').first()
mrp = last_purchase.mrp if last_purchase else 0
purchase_return = child_purchase_return_table.objects.filter(
item_id=item_id,
batch_no=batch_no,
branch_id=branch_id,
status=1
).aggregate(total=Sum('quantity'))['total'] or 0
sales_qty = sales
net_available = purchase_qty - purchase_return - sales_qty + sales_return
return {
'available': net_available,
'stock_for_sale': net_available,
'purchase': purchase_qty,
'purchase_return': purchase_return,
'sales': sales_qty,
'sales_return': sales_return,
'year': financial_year,
'mrp': mrp,
}
def get_available_stock(item_id, branch_id, sales_id=0):
opening_stock = opening_stock_table.objects.filter(
item_id=item_id,
branch_id=branch_id,
status=1
).aggregate(qty=Sum('opening_stock'))['qty'] or 0
total_inward_qty = child_purchase_inward_table.objects.filter(
item_id=item_id,
branch_id=branch_id,
status=1
).aggregate(qty=Sum('quantity'))['qty'] or 0
total_return_qty = child_purchase_return_table.objects.filter(
item_id=item_id,
branch_id=branch_id,
status=1
).aggregate(qty=Sum('quantity'))['qty'] or 0
total_sales_qty_query = child_sales_order_table.objects.filter(
item_id=item_id,
branch_id=branch_id,
status=1
)
# 🔹 If editing, exclude the current sales entry
if sales_id and sales_id != 0:
total_sales_qty_query = total_sales_qty_query.exclude(tm_sales_id=sales_id)
total_sales_qty = total_sales_qty_query.aggregate(qty=Sum('quantity'))['qty'] or 0
total_sales_return_qty = child_sales_return_table.objects.filter(
item_id=item_id,
branch_id=branch_id,
status=1
).aggregate(qty=Sum('quantity'))['qty'] or 0
available_stock = opening_stock + total_inward_qty - total_return_qty - total_sales_qty + total_sales_return_qty
return available_stock
from django.db.models import Sum
def get_stock_detailed(item_id, sales_id, serial_no, rate, branch_id, financial_year):
serial_no = serial_no.strip().upper() if serial_no else None
# -----------------------------------
# OPENING STOCK (by SERIAL)
# -----------------------------------
stock_qs = opening_stock_table.objects.filter(
stock_no=serial_no,
item_id=item_id,
branch_id=branch_id,
current_fy=financial_year,
status=1,
purchase_price=rate
)
opening_stock_total = stock_qs.aggregate(
total=Sum('opening_stock')
)['total'] or 0
latest_opening_row = stock_qs.order_by('-created_on').first()
opening_mrp = latest_opening_row.mrp if latest_opening_row else 0
# -----------------------------------
# SALES (by SERIAL)
# -----------------------------------
sales_qs = child_sales_order_table.objects.filter(
serial_no=serial_no,
item_id=item_id,
branch_id=branch_id,
status=1,
purchase_price=rate
)
if sales_id:
sales_qs = sales_qs.exclude(tm_sales_id=sales_id)
sales = sales_qs.aggregate(
total=Sum('quantity')
)['total'] or 0
# -----------------------------------
# SALES RETURN (by SERIAL)
# -----------------------------------
sales_return = child_sales_return_table.objects.filter(
item_id=item_id,
batch_no=serial_no,
branch_id=branch_id,
status=1
).aggregate(
total=Sum('quantity')
)['total'] or 0
# -----------------------------------
# IF OPENING STOCK EXISTS → USE IT
# -----------------------------------
if opening_stock_total > 0:
available = opening_stock_total - sales + sales_return
return {
'available': available,
'stock_for_sale': available,
'purchase': 0,
'purchase_return': 0,
'sales': sales,
'sales_return': sales_return,
'mrp': opening_mrp,
}
# -----------------------------------
# PURCHASE INWARD (by SERIAL)
# -----------------------------------
purchase_qty = child_purchase_inward_table.objects.filter(
item_id=item_id,
branch_id=branch_id,
serial_no=serial_no,
status=1,
rate=rate
).aggregate(
total=Sum('quantity')
)['total'] or 0
last_purchase = child_purchase_inward_table.objects.filter(
item_id=item_id,
branch_id=branch_id,
serial_no=serial_no,
status=1
).order_by('-created_on').first()
mrp = last_purchase.mrp if last_purchase else 0
# -----------------------------------
# PURCHASE RETURN (by SERIAL)
# -----------------------------------
purchase_return = child_purchase_return_table.objects.filter(
item_id=item_id,
batch_no=serial_no,
branch_id=branch_id,
status=1
).aggregate(
total=Sum('quantity')
)['total'] or 0
net_available = purchase_qty - purchase_return - sales + sales_return
return {
'available': net_available,
'stock_for_sale': net_available,
'purchase': purchase_qty,
'purchase_return': purchase_return,
'sales': sales,
'sales_return': sales_return,
'year': financial_year,
'mrp': mrp,
}
from customer.models import *
def get_or_create_customer(name, phone, company_id, branch_id, user_id):
if not phone:
return None
customer = customer_table.objects.filter(phone=phone, status=1).first()
if customer:
return customer.id
new_customer = customer_table.objects.create(
name=name,
phone=phone,
customer_type='retail',
email='',
address_line1='',
address_line2='',
city='Tirupur',
state='Tamil Nadu',
pincode='0',
mobile='',
remarks='',
company_id=company_id,
branch_id=branch_id,
is_active=1,
status=1,
created_by=user_id,
updated_by=user_id,
created_on=timezone.now(),
updated_on=timezone.now()
)
return new_customer.id
def get_sales_batches(batch_no, rate, sales_id, item_id, branch_id, financial_year, customer_id, store_id, customer):
items = []
is_opening_stock = False
parts = str(batch_no).split('/')
if len(parts) > 1 and parts[1].upper().startswith("OPN"):
is_opening_stock = True
elif str(batch_no).upper().startswith("OPN"):
is_opening_stock = True
# --- OPENING STOCK CASE ---
if is_opening_stock:
stock_entry = opening_stock_table.objects.filter(
stock_no=batch_no,
branch_id=branch_id,
current_fy=financial_year,
status=1,
item_id=item_id,
purchase_price=rate
).first() # <-- use .first() to get single object
if not stock_entry:
return items
try:
item = item_table.objects.get(id=stock_entry.item_id)
except item_table.DoesNotExist:
return items
try:
uom = uom_table.objects.get(id=item.uom_id)
uom_name = uom.name
except uom_table.DoesNotExist:
uom_name = ''
# --- Selling Price Calculation ---
selling_price = 0
if customer is not None:
customer_obj = select_row(customer_table, {'name': customer})
rate_type = customer_obj.customer_type if customer_obj else 'retail'
if rate_type == 'wholesale':
wholesale_percent = item.wholesale_price or 0
base_rate = stock_entry.purchase_price
selling_price = base_rate + (base_rate * wholesale_percent / 100)
else:
selling_price = stock_entry.purchase_price
elif store_id != 0:
base_rate = item.selling_price or 0
franchise_percent = item.franchise_price or 0
selling_price = base_rate + (base_rate * franchise_percent / 100)
stock_info = get_stock_detailed(item.id, sales_id, batch_no,rate, branch_id, financial_year)
items.append({
'item_id': item.id,
'item_name': item.name,
'quantity': stock_info.get('available', 0),
'rate': selling_price,
'uom_id': item.uom_id,
'purchase_price': stock_entry.purchase_price,
'uom_name': uom_name,
'mrp': stock_entry.mrp
})
# --- PURCHASE INWARD CASE ---
else:
item_rows = child_purchase_inward_table.objects.filter(
batch_no=batch_no,
branch_id=branch_id,
current_fy=financial_year,
item_id=item_id,
status=1
).values('item_id', 'uom_id', 'rate', 'mrp').annotate(total_quantity=Sum('quantity'))
for row in item_rows:
stock_info = get_stock_detailed(row['item_id'], sales_id, batch_no, rate, branch_id, financial_year)
try:
item = item_table.objects.get(id=row['item_id'])
except item_table.DoesNotExist:
continue
try:
uom = uom_table.objects.get(id=row['uom_id'])
uom_name = uom.name
except uom_table.DoesNotExist:
uom_name = ''
base_rate = float(row.get('rate') or 0)
base_mrp = float(row.get('mrp') or 0)
selling_price = 0
if customer:
customer_obj = select_row(customer_table, {'name': customer})
rate_type = customer_obj.customer_type if customer_obj else 'retail'
if rate_type == 'wholesale':
wholesale_percent = item.wholesale_price or 0
selling_price = base_rate + (base_rate * wholesale_percent / 100)
else: # retail or default
selling_price = base_mrp
elif store_id:
franchise_percent = item.franchise_price or 0
selling_price = base_rate + (base_rate * franchise_percent / 100)
items.append({
'item_id': row['item_id'],
'item_name': item.name,
'quantity': stock_info.get('available', 0),
'rate': selling_price,
'uom_id': row['uom_id'],
'uom_name': uom_name,
'purchase_price': base_rate,
'mrp': base_mrp,
})
return items
def get_items_by_batch_no(batch_no, sales_id, item_id, branch_id, financial_year, customer_id, store_id, customer):
items = []
# Normalize detection for OPN formats
is_opening_stock = False
parts = str(batch_no).split('/')
# Case 1: prefixed like BM/OPN0001
if len(parts) > 1 and parts[1].upper().startswith("OPN"):
is_opening_stock = True
# Case 2: plain OPN0001
elif str(batch_no).upper().startswith("OPN"):
is_opening_stock = True
if is_opening_stock:
stock_entry = opening_stock_table.objects.filter(
stock_no=batch_no,
branch_id=branch_id,
current_fy=financial_year,
status=1,
item_id=item_id
).first()
if not stock_entry:
return items
try:
item = item_table.objects.get(id=stock_entry.item_id)
except item_table.DoesNotExist:
return items
try:
uom = uom_table.objects.get(id=item.uom_id)
uom_name = uom.name
except uom_table.DoesNotExist:
uom_name = ''
# --- Selling Price Calculation ---
selling_price = 0
if customer is not None:
customer_obj = select_row(customer_table, {'name': customer})
rate_type = customer_obj.customer_type if customer_obj else 'retail'
if rate_type == 'wholesale':
wholesale_percent = item.wholesale_price or 0
base_rate = stock_entry.purchase_price
selling_price = base_rate + (base_rate * wholesale_percent / 100)
else:
selling_price = stock_entry.purchase_price
elif store_id != 0:
base_rate = item.selling_price or 0
franchise_percent = item.franchise_price or 0
selling_price = base_rate + (base_rate * franchise_percent / 100)
stock_info = get_stock_detail(item.id, sales_id, batch_no, branch_id, financial_year)
items.append({
'item_id': item.id,
'item_name': item.name,
'quantity': stock_info.get('available', 0),
'rate': selling_price,
'uom_id': item.uom_id,
'purchase_price': stock_entry.purchase_price,
'uom_name': uom_name,
'mrp': stock_entry.mrp
})
# 🔹 Purchase inward case
else:
item_rows = child_purchase_inward_table.objects.filter(
batch_no=batch_no,
branch_id=branch_id,
current_fy=financial_year,
item_id=item_id,
status=1
).values('item_id', 'uom_id', 'rate', 'mrp').annotate(total_quantity=Sum('quantity'))
for row in item_rows:
stock_info = get_stock_detail(row['item_id'], sales_id, batch_no, branch_id, financial_year)
try:
item = item_table.objects.get(id=row['item_id'])
except item_table.DoesNotExist:
continue
try:
uom = uom_table.objects.get(id=row['uom_id'])
uom_name = uom.name
except uom_table.DoesNotExist:
uom_name = ''
base_rate = float(row.get('rate', 0) or 0)
base_mrp = float(row.get('mrp', 0) or 0)
selling_price = 0
if customer:
customer_obj = select_row(customer_table, {'name': customer})
rate_type = customer_obj.customer_type if customer_obj else 'retail'
if rate_type == 'wholesale':
wholesale_percent = item.wholesale_price or 0
selling_price = base_rate + (base_rate * wholesale_percent / 100)
elif rate_type == 'retail':
selling_price = base_mrp
else:
selling_price = base_mrp
elif store_id:
franchise_percent = item.franchise_price or 0
selling_price = base_rate + (base_rate * franchise_percent / 100)
items.append({
'item_id': row['item_id'],
'item_name': item.name,
'quantity': stock_info.get('available', 0),
'rate': selling_price,
'uom_id': row['uom_id'],
'uom_name': uom_name,
'purchase_price': base_rate,
'mrp':base_mrp,
})
return items
# utils.py
def generate_code(model, prefix, code_field="code", status_field="status"):
last_obj = model.objects.filter(**{status_field: 1}) \
.order_by(f"-{code_field}") \
.first()
if not last_obj:
new_number = 1
else:
last_code = getattr(last_obj, code_field) # Example: BK023
try:
last_num = int(last_code.replace(prefix, "")) # Extract "023"
new_number = last_num + 1
except:
new_number = 1
return f"{prefix}{new_number:03d}" # BK001
from scheme.models import *
from datetime import date
from datetime import date
def get_final_price_for_imei(branch_id, subcategory_id, brand_id, model_id, variant_id, imei_no, fsp, wsp, mop, bop):
"""
Returns final prices considering any active price drop for the given IMEI
"""
today = date.today()
# 1️⃣ Fetch active price drops (checking all branches for global/IMEI-specific drops)
price_drops = price_history_table.objects.filter(
status=1,
from_date__lte=today,
subcategory_id=subcategory_id,
brand_id=brand_id,
model_id=model_id,
variant_id=variant_id
).order_by('-from_date', '-id')
for price_drop in price_drops:
# 2️⃣ Check IMEI-specific override in TX table for this drop
tx_price = tx_price_history_table.objects.filter(
tm_price_id=price_drop.id,
imei_no=imei_no,
is_active=1,
status=1
).first()
if tx_price:
return (
tx_price.fsp_after_discount,
tx_price.wsp_after_discount,
tx_price.mop_after_discount,
tx_price.bop_after_discount
)
return fsp, wsp, mop, bop
def get_ean_hsn(row):
"""
Returns a tuple (ean, hsn_code) for the given row.
If row has ean or hsn_code, use that.
Otherwise, fetch from item_table using subcategory, brand, model, variant, color.
"""
ean = getattr(row, 'ean', '') or ''
hsn = getattr(row, 'hsn_code', '') or ''
shelf_life = ''
if not ean or not hsn or not shelf_life:
item = item_table.objects.filter(
sub_category_id=row.subcategory_id,
brand_id=row.brand_id,
model_id=row.model_id,
variant_id=row.variant_id,
color_id=row.color_id,
status=1,
is_active=1
).first()
if item:
ean = item.ean or ean
hsn = item.hsn_code or hsn
shelf_life = item.cell_life or ''
return ean, hsn, shelf_life
def get_cash_and_bank_balance(branch_id, financial_year, bank_id=None):
"""
Returns cash balance, total bank balance, and selected bank balance
"""
current_balance = get_current_balance(branch_id, financial_year)
cash_balance = current_balance.get('cash_balance', Decimal('0.00'))
total_bank_balance = current_balance.get('bank_balance', Decimal('0.00'))
bank_balance_by_id = {
bank['bank_id']: bank.get('balance', Decimal('0.00'))
for bank in current_balance.get('bank_wise', [])
}
selected_bank_balance = Decimal('0.00')
if bank_id:
selected_bank_balance = bank_balance_by_id.get(
int(bank_id), Decimal('0.00')
)
return {
'cash_balance': cash_balance,
'total_bank_balance': total_bank_balance,
'selected_bank_balance': selected_bank_balance,
'bank_balance_by_id': bank_balance_by_id
}