import os from openpyxl import load_workbook from openpyxl.styles import PatternFill from openpyxl.styles import Border, Side from openpyxl.worksheet.worksheet import Worksheet from openpyxl.workbook import Workbook from copy import copy from openpyxl.utils.cell import coordinate_from_string, column_index_from_string from openpyxl.worksheet.cell_range import CellRange from openpyxl.cell import MergedCell from orders.models import CustomOrderTracking, Discount, Orders, Payment, OrderProducts, DeliverySlot from products.models import SKU, Products from accounts.models import Users from shops.models import Shop, ProductionUnit import logging import subprocess from openpyxl.styles import Font, Alignment from pathlib import Path from num2words import num2words class OutputPDFNotFoundError(Exception): def __init__(self, value: str) -> None: self.value = value self.message = "Output pdf file not found, please check the output path." super().__init__(f"{self.message}: {self.value}") class ErrorConvertingPDF(Exception): def __init__(self, value: str) -> None: self.value = value self.message = "Output pdf file generation failed!." super().__init__(f"{self.message}: {self.value}") class OuptputExcelNotFoundError(Exception): def __init__(self, value: str) -> None: self.value = value self.message = "Output excel file not found, please check the output path." super().__init__(f"{self.message}: {self.value}") class TemplateNotFoundError(Exception): def __init__(self, value: str) -> None: self.value = value self.message = "Invalid template path, file not found." super().__init__(f"{self.message}: {self.value}") class OutputFolderCreationError(Exception): def __init__(self) -> None: self.value = "Please check permission issues" self.message = "Error creating Output Folders" super().__init__(f"{self.message}: {self.value}") class TemplateFileAlreadyOpened(Exception): def __init__(self, value: str) -> None: self.value = value self.message = "Template file already opened, please close the file and try again." super().__init__(f"{self.message}: {self.value}") class DataNotFoundError(Exception): def __init__(self, value: str) -> None: self.value = value self.message = "Data Not Found" super().__init__(f"{self.message}: {self.value}") def _check_template_exist(template_path) -> None: if not os.path.exists(template_path): raise TemplateNotFoundError(template_path) def _fetch_order_data(order_id: str) -> Orders: order = Orders.objects.get(order_ID=order_id) if not order: raise DataNotFoundError(f"No data found for order ID: {order_id}") return order def _fetch_invoice_details(order: Orders) -> dict: payment = Payment.objects.filter(order=order).first() if not payment: raise DataNotFoundError(f"No payment data found for order ID: {order.order_ID}") invoice_details = { "Invoice Number": payment.order.order_ID, "Invoice Date": payment.payment_date.strftime("%Y-%m-%d") if payment.payment_date else "N/A" } return invoice_details def _fetch_additional_details(order: Orders) -> dict: try: delivery_slot_date = order.delivery_slot_date.strftime("%Y-%m-%d") if order.delivery_slot_date else "N/A" delivery_slot_time = order.delivery_slot_time.strftime("%H:%M") if order.delivery_slot_time else "N/A" slot = f"{delivery_slot_date} {delivery_slot_time}" if delivery_slot_time != "N/A" else delivery_slot_date payable_amount = f"₹{order.grand_total:.2f}" if order.grand_total else "₹0" payment_mode = order.payment_mode.mode_name if order.payment_mode else "N/A" source = str(order.store_uuid.unit_name) if order.store_uuid else "" if order.order_type != "Custom Orders": no_of_items = OrderProducts.objects.filter(order=order).count() else: no_of_items = 1 additional_details = { "Order No": order.order_ID, "Slot": slot, "Payable Amount": payable_amount, "Payment Mode": payment_mode, "Source": source, "No of items": no_of_items } logging.info(f"Additional details: {additional_details}") return additional_details except Exception as e: logging.error(f"Error fetching additional details: {e}") raise DataNotFoundError(f"No additional details found for order ID: {order.order_ID}") def _fetch_other_charges(order: Orders) -> dict: try: handling_charges = f"₹{order.coupon_savings:.2f}" if order.coupon_savings else "₹0" delivery_charges = f"₹{order.delivery_charges:.2f}" if order.delivery_charges else "₹0" discount = f"₹{order.discount:.2f}" if order.discount else "₹0" total_amount = f"₹{order.grand_total:.2f}" if order.grand_total else "₹0" other_charges = { "Handling Charges": handling_charges, "Delivery Charges": delivery_charges, "Discount": discount, "Total Amount": total_amount } return other_charges except: raise DataNotFoundError(f"No other charges found for order ID: {order.order_ID}") from django.db.models import Q def _item_details(order: Orders) -> list: """ Returns item details with the updated calculation logic: - Gross Value = Unit Price (MRP) × Quantity - Discount = discount_per_unit × Quantity (cumulative for all units) - Total Tax = (Gross Value − Discount) × (CGST + SGST + CESS) / 100 - Total Value = Gross Value − Discount + Total Tax """ try: if order.order_type != "Custom Orders": items = OrderProducts.objects.filter(order=order) item_details = [] for item in items: sku = item.sku product = sku.product c_gst = float(sku.product.c_gst or 0) # stored as percentage, e.g. 9.0 s_gst = float(sku.product.s_gst or 0) cess = float(sku.product.cess or 0) # ── Resolve applicable discount ────────────────────────────── applicable_discount = None discounts = Discount.objects.filter( Q(DiscountOn="Category", ApplicableCategory__in=[product.item_category]) | Q(DiscountOn="SubCategory", ApplicableSubCategory__in=[product.item_sub_category]) | Q(DiscountOn="Product", ApplicableProduct__in=[product]) | Q(DiscountOn="Sku", ApplicableSku__in=[sku]) ) for discount in discounts: if discount.DiscountOn == 'Category' and product.item_category in discount.ApplicableCategory.all(): applicable_discount = discount break elif discount.DiscountOn == 'SubCategory' and product.item_sub_category in discount.ApplicableSubCategory.all(): applicable_discount = discount break elif discount.DiscountOn == 'Product' and product in discount.ApplicableProduct.all(): applicable_discount = discount break elif discount.DiscountOn == 'Sku' and sku in discount.ApplicableSku.all(): applicable_discount = discount break mrp = float(sku.sku_mrp) quantity = float(item.quantity) # Discount per unit (₹); total discount = per-unit × quantity if applicable_discount: discount_percent = float(applicable_discount.DiscountPercentage) discount_per_unit = round(mrp * discount_percent / 100, 2) else: discount_per_unit = 0.0 # ── New calculation logic ──────────────────────────────────── gross_value = round(mrp * quantity, 2) total_discount = round(discount_per_unit * quantity, 2) tax_rate = (c_gst + s_gst + cess) / 100 # e.g. 0.19 total_tax = round((gross_value - total_discount) * tax_rate, 2) total_value = round(gross_value - total_discount + total_tax, 2) # Item description with unit details if sku.sku_quantity and sku.sku_unit: item_desc = f"{sku.sku_name}-{sku.sku_quantity}{sku.sku_unit}" else: item_desc = sku.sku_name item_detail = { "Item Description": item_desc, "HSN Code": sku.sku_code, "Quantity": str(int(quantity) if quantity.is_integer() else quantity), "Unit Price": f"{mrp:.2f}", "Gross Value": f"{gross_value:.2f}", "Discount": f"{total_discount:.2f}", "CGST Rate(%) Amount": c_gst, "SGST Rate Amount": s_gst, "CESS Amount": cess, "Total Tax": f"{total_tax:.2f}", "Total Value": f"{total_value:.2f}", } item_details.append(item_detail) return item_details else: # ── Custom orders ──────────────────────────────────────────────── custom_items = CustomOrderTracking.objects.filter(order_id=order) order_obj = Orders.objects.get(order_ID=order.order_ID) discount_val = float(order_obj.discount) if order_obj.discount else 0.0 item_details = [] for item in custom_items: qty_unit = item.quantity_unit if item.quantity_unit else "" qty_display = f"{item.quantity} {qty_unit}".strip() gross_value = float(order_obj.grand_total or 0) # No GST for custom orders total_tax = 0.0 total_value = round(gross_value - discount_val + total_tax, 2) item_detail = { "Item Description": "Custom Order Item", "HSN Code": "N/A", "Quantity": qty_display, "Unit Price": f"{gross_value:.2f}", "Gross Value": f"{gross_value:.2f}", "Discount": f"{discount_val:.2f}", "CGST Rate(%) Amount": 0.00, "SGST Rate Amount": 0.00, "CESS Amount": 0.00, "Total Tax": f"{total_tax:.2f}", "Total Value": f"{total_value:.2f}", } item_details.append(item_detail) return item_details except Exception as e: raise DataNotFoundError( f"No item details found for order ID: {order.order_ID} :: {e}" ) def _total(order: Orders) -> dict: try: total = order.sub_total + order.taxes_and_charges except: total = 0 totals = {"Total": order.grand_total, "Sub Total": total} return totals def _fetch_shop_details(order: Orders) -> dict: try: if order.order_type == "Local Orders": shop = order.store_uuid if shop: shop_address_lines = [] if shop.unit_location: shop_address_lines.append(shop.unit_location) if shop.street: shop_address_lines.append(shop.street) if shop.city: shop_address_lines.append(shop.city) if shop.district: shop_address_lines.append(shop.district) if shop.state_or_province: shop_address_lines.append(shop.state_or_province) if shop.pin_code: shop_address_lines.append(str(shop.pin_code)) shop_address = "\n".join(shop_address_lines) else: shop_address = "N/A" shop_details = { "Shop Name": shop.unit_name if shop else "N/A", "Shop Address": shop_address, "Shop Phone": shop.contact_no if shop else "N/A", "Shop Email": shop.email if shop else "N/A", "Shop GSTIN": shop.gstin if shop and hasattr(shop, 'gstin') else "N/A", } elif order.order_type == "Long Distance Orders": production_unit = order.pu_uuid if production_unit: production_unit_address_lines = [] if production_unit.pu_location: production_unit_address_lines.append(production_unit.pu_location) if production_unit.street: production_unit_address_lines.append(production_unit.street) if production_unit.city: production_unit_address_lines.append(production_unit.city) if production_unit.district: production_unit_address_lines.append(production_unit.district) if production_unit.state_or_province: production_unit_address_lines.append(production_unit.state_or_province) if production_unit.pin_code: production_unit_address_lines.append(str(production_unit.pin_code)) production_unit_address = "\n".join(production_unit_address_lines) else: production_unit_address = "N/A" shop_details = { "Shop Name": production_unit.pu_name if production_unit else "N/A", "Shop Address": production_unit_address, "Shop Phone": production_unit.contact_no if production_unit else "N/A", "Shop Email": production_unit.email if production_unit else "N/A", "Shop GSTIN": production_unit.gstin if production_unit and hasattr(production_unit, 'gstin') else "N/A", } else: shop = order.store_uuid if shop: shop_address_lines = [] if shop.unit_location: shop_address_lines.append(shop.unit_location) if shop.street: shop_address_lines.append(shop.street) if shop.city: shop_address_lines.append(shop.city) if shop.district: shop_address_lines.append(shop.district) if shop.state_or_province: shop_address_lines.append(shop.state_or_province) if shop.pin_code: shop_address_lines.append(str(shop.pin_code)) shop_address = "\n".join(shop_address_lines) else: shop_address = "N/A" shop_details = { "Shop Name": shop.unit_name if shop else "N/A", "Shop Address": shop_address, "Shop Phone": shop.contact_no if shop else "N/A", "Shop Email": shop.email if shop else "N/A", "Shop GSTIN": shop.gstin if shop and hasattr(shop, 'gstin') else "N/A", } return shop_details except Exception as e: raise DataNotFoundError(f"No shop details found for order ID: {order.order_ID} :: {e}") def _fetch_user_details(order: Orders) -> dict: try: user = order.user_uuid if not user: raise DataNotFoundError(f"No user details found for order ID: {order.order_ID}") if order.order_type != "Pick Up" and order.drop_address: user_address_lines = [] if order.drop_address.house_number_or_name: user_address_lines.append(order.drop_address.house_number_or_name) if order.drop_address.street: user_address_lines.append(order.drop_address.street) if order.drop_address.land_mark: user_address_lines.append(order.drop_address.land_mark) if order.drop_address.city: user_address_lines.append(order.drop_address.city) if order.drop_address.district: user_address_lines.append(order.drop_address.district) if order.drop_address.state_or_province: user_address_lines.append(order.drop_address.state_or_province) if order.drop_address.pin_code: user_address_lines.append(str(order.drop_address.pin_code)) user_address = "\n".join(user_address_lines) user_name = order.drop_address.name if order.drop_address.name else "" contact_number = order.drop_address.contact_number if order.drop_address.contact_number else user.phone_number if user.phone_number else "N/A" else: user_address = "Pick Up" user_name = f"{user.first_name} {user.last_name}".strip() if user.first_name or user.last_name else "" contact_number = user.phone_number if user.phone_number else "N/A" user_details = { "User Name": user_name, "User Address": user_address, "User Phone": contact_number, "User Email": user.email if user.email else "N/A" } return user_details except Exception as e: raise DataNotFoundError(f"No user details found for order ID: {order.order_ID}::{e}") def _serialize_order_data(order_id: str) -> dict: try: order_data = _fetch_order_data(order_id) invoice_details = _fetch_invoice_details(order_data) additional_details = _fetch_additional_details(order_data) other_charges = _fetch_other_charges(order_data) item_details = _item_details(order_data) logging.info(f"Item Details for order {order_id}: {item_details}") user_details = _fetch_user_details(order_data) shop_details = _fetch_shop_details(order_data) totals = _total(order_data) if not order_data: raise DataNotFoundError(f"No data found for order ID: {order_id}") return { "invoice_details": invoice_details, "additonal_details": additional_details, "other_charges": other_charges, "item_details": item_details, "shop_details": shop_details, "user_details": user_details, "totals": totals } except Exception as e: raise DataNotFoundError(f"No data found for order ID: {order_id} :: {e}") def _check_output_dir() -> None: if not os.path.exists('./media/output'): try: os.mkdir('./media/output') os.mkdir('./media/output/excel') os.mkdir('./media/output/pdf') except Exception as e: raise OutputFolderCreationError() else: if not os.path.exists('./media/output/excel'): try: os.mkdir('./media/output/excel') except: raise OutputFolderCreationError() if not os.path.exists('./media/output/pdf'): try: os.mkdir('./media/output/pdf') except: raise OutputFolderCreationError() def _open_excel(template_path) -> Workbook: try: wb = load_workbook(template_path) return wb except: raise TemplateFileAlreadyOpened(template_path) def _save_excel(wb: Workbook, output_excel: str, order_id: str) -> None: if not wb: raise ValueError("Workbook is not opened. Please open the workbook before saving.") wb.save(output_excel + f'/{order_id}.xlsx') wb.close() logging.info(f"Excel file saved at {output_excel}") def _safe_write_cell(worksheet: Worksheet, row: int, column: int, value) -> None: """Safely write to a cell, unmerging merged cells before writing.""" try: cell = worksheet.cell(row=row, column=column) if isinstance(cell, MergedCell): ranges_to_unmerge = [] for merged_range in worksheet.merged_cells.ranges: if (merged_range.min_row <= row <= merged_range.max_row and merged_range.min_col <= column <= merged_range.max_col): ranges_to_unmerge.append(merged_range) for merged_range in ranges_to_unmerge: worksheet.unmerge_cells(str(merged_range)) cell = worksheet.cell(row=row, column=column) cell.value = value except Exception as e: logging.error(f"Error writing to cell ({row}, {column}): {e}") try: worksheet.cell(row=row, column=column, value=value) except: pass def _write_invoice_details(worksheet: Worksheet, invoice_details: dict) -> None: logging.info(f"Invoice Details: {invoice_details}") if not invoice_details: raise DataNotFoundError("No invoice details provided to write to the excel file.") invoice_number = invoice_details.get("Invoice Number", "N/A") invoice_date = invoice_details.get("Invoice Date", "N/A") # Invoice Number → P4 (col 16), Invoice Date → P5 (col 16) _safe_write_cell(worksheet, 4, 16, invoice_number) _safe_write_cell(worksheet, 5, 16, invoice_date) def _write_additional_details(worksheet: Worksheet, additional_details: dict) -> None: if not additional_details: raise DataNotFoundError("No additional details provided to write to the excel file.") order_no = additional_details.get("Order No", "N/A") mode = Orders.objects.get(order_ID=order_no).payment_mode.mode_name if Orders.objects.get(order_ID=order_no).payment_mode else "N/A" slot = additional_details.get("Slot", "N/A") payable_amount = additional_details.get("Payable Amount", "N/A") payment_mode = additional_details.get("Payment Mode", mode) source = additional_details.get("Source", "N/A") no_of_items = additional_details.get("No of items", "N/A") # Values → P column (col 16) rows 8-13 _safe_write_cell(worksheet, 8, 16, order_no) _safe_write_cell(worksheet, 9, 16, slot) _safe_write_cell(worksheet, 10, 16, payable_amount) _safe_write_cell(worksheet, 11, 16, payment_mode) _safe_write_cell(worksheet, 12, 16, source) _safe_write_cell(worksheet, 13, 16, no_of_items) logging.info(f"Written additional details: Order No={order_no}, Slot={slot}, " f"Amount={payable_amount}, Mode={payment_mode}, Source={source}, Items={no_of_items}") def _write_other_charges(worksheet: Worksheet, other_charges: dict, row_offset: int = 0) -> None: if not other_charges: raise DataNotFoundError("No other charges provided to write to the excel file.") delivery_charges = other_charges.get("Delivery Charges", "₹0") discount = other_charges.get("Discount", "₹0") handling_charges = other_charges.get("Handling Charges", "₹0") # Base rows (1-item template): row 20=Delivery label, 21=value; # row 23=Discount label, 24=value; row 26=Coupon label, 27=value. # Shift by row_offset for every extra item row inserted. _safe_write_cell(worksheet, 21 + row_offset, 15, delivery_charges) _safe_write_cell(worksheet, 24 + row_offset, 15, discount) _safe_write_cell(worksheet, 27 + row_offset, 15, handling_charges) logging.info(f"Written other charges: Delivery={delivery_charges}, Discount={discount}, Coupon={handling_charges}") def _write_row_total(worksheet: Worksheet, total_row: int, grand_total: float) -> None: """Write the grand-total summary row immediately after all item rows.""" thin = Side(style='thin') full_border = Border(top=thin, bottom=thin, left=thin, right=thin) # Merge E(5) through O(15) for the "Total" label label_range = f"E{total_row}:O{total_row}" try: worksheet.merge_cells(label_range) except Exception: pass label_cell = worksheet.cell(row=total_row, column=5) label_cell.value = "Total" label_cell.font = Font(bold=True) label_cell.alignment = Alignment(horizontal='right', vertical='center') label_cell.border = full_border # Grand total → P(16) value_cell = worksheet.cell(row=total_row, column=16) value_cell.value = f"₹{grand_total:.2f}" value_cell.font = Font(bold=True) value_cell.alignment = Alignment(horizontal='center', vertical='center') value_cell.border = full_border worksheet.row_dimensions[total_row].height = 20 def _write_item_details(worksheet: Worksheet, item_details: list, grand_total: float = 0.0) -> None: """ Write item rows using the updated template column layout: E(5) = Sl No. F(6) = Item Description G(7) = SKU Code H(8) = Quantity I(9) = Unit Price* J(10) = Gross Value* (Unit Price × Quantity) K(11) = Discount* (discount_per_unit × Quantity) L(12) = CGST Rate(%) Amount M(13) = SGST/UTGST Rate(%) Amount N(14) = CESS Amount O(15) = Total Tax ((Gross Value − Discount) × tax_rate) P(16) = TOTAL Value (Gross Value − Discount + Total Tax) """ if not item_details: raise DataNotFoundError("No item details provided to write to the excel file.") start_row = 16 # first data row in the template for idx, item in enumerate(item_details, start=1): current_row = start_row + idx - 1 item_description = item.get("Item Description", "N/A") hsn_code = item.get("HSN Code", "N/A") quantity = item.get("Quantity", "N/A") unit_price = item.get("Unit Price", "N/A") gross_value = item.get("Gross Value", "N/A") discount = item.get("Discount", "0.00") cgst_rate = float(item.get("CGST Rate(%) Amount", 0.0)) sgst_rate = float(item.get("SGST Rate Amount", 0.0)) cess_amount = float(item.get("CESS Amount", 0.0)) total_tax = item.get("Total Tax", "0.00") total_value = item.get("Total Value", "N/A") _safe_write_cell(worksheet, current_row, 5, idx) _safe_write_cell(worksheet, current_row, 6, item_description) _safe_write_cell(worksheet, current_row, 7, hsn_code) _safe_write_cell(worksheet, current_row, 8, quantity) _safe_write_cell(worksheet, current_row, 9, unit_price) _safe_write_cell(worksheet, current_row, 10, gross_value) _safe_write_cell(worksheet, current_row, 11, f"₹{discount}" if not str(discount).startswith('₹') else discount) _safe_write_cell(worksheet, current_row, 12, f"{cgst_rate}%") _safe_write_cell(worksheet, current_row, 13, f"{sgst_rate}%") _safe_write_cell(worksheet, current_row, 14, f"{cess_amount}%") _safe_write_cell(worksheet, current_row, 15, total_tax) _safe_write_cell(worksheet, current_row, 16, total_value) # Centre-align all item columns for col in range(5, 17): cell = worksheet.cell(row=current_row, column=col) cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) # Grand-total row immediately after the last item row total_row = start_row + len(item_details) _write_row_total(worksheet, total_row, grand_total) def _write_shop_details(worksheet: Worksheet, shop_details: dict) -> None: if not shop_details: raise DataNotFoundError("No shop details provided to write to the excel file.") shop_name = shop_details.get("Shop Name", "N/A") shop_address = shop_details.get("Shop Address", "N/A") shop_phone = shop_details.get("Shop Phone", "N/A") shop_email = shop_details.get("Shop Email", "N/A") shop_gstin = shop_details.get("Shop GSTIN", "") lines = ["Details of Supplier", ""] lines.append(shop_name) if shop_address and shop_address != "N/A": lines.extend(shop_address.split("\n")) if shop_phone and shop_phone != "N/A": lines.append(shop_phone) if shop_email and shop_email != "N/A": lines.append(shop_email) if shop_gstin and shop_gstin not in ("N/A", ""): lines.append(f"GSTIN: {shop_gstin}") _safe_write_cell(worksheet, 4, 5, "\n".join(lines)) cell = worksheet.cell(row=4, column=5) cell.alignment = Alignment(horizontal='left', vertical='top', wrap_text=True) def _convert_to_words(total: float) -> str: try: rupees = int(total) paise = int(round((total - rupees) * 100)) words = f"Rupees {num2words(rupees, lang='en_IN').capitalize()}" if paise > 0: words += f" and {num2words(paise, lang='en_IN')} paise" words += " only" return words except: return f"Rupees {total:.2f} only" def _write_totals(worksheet: Worksheet, totals: dict, row_offset: int = 0) -> None: if not totals: raise DataNotFoundError("No total details provided to write to the excel file.") item_details_total = totals.get("Total", 0) text = _convert_to_words(item_details_total) # Base rows (1-item template): 28 = figure, 29 = words. fig_row = 28 + row_offset wrd_row = 29 + row_offset _safe_write_cell(worksheet, fig_row, 12, f"₹{item_details_total:.2f}") _safe_write_cell(worksheet, wrd_row, 12, text) for row, col in [(fig_row, 12), (wrd_row, 12)]: cell = worksheet.cell(row=row, column=col) cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True) logging.info(f"Written totals: Amount={item_details_total}, Words={text}") def _write_user_details(worksheet: Worksheet, user_details: dict) -> None: if not user_details: raise DataNotFoundError("No user details provided to write to the excel file.") user_name = user_details.get("User Name", "N/A") user_address = user_details.get("User Address", "N/A") user_phone = user_details.get("User Phone", "N/A") user_email = user_details.get("User Email", "N/A") lines = ["Bill to/Ship to", ""] if user_name and user_name.strip(): lines.append(user_name.strip()) if user_address and user_address.strip(): lines.extend(user_address.strip().split("\n")) if user_phone and user_phone.strip(): lines.append(user_phone.strip()) if user_email and user_email.strip(): lines.append(user_email.strip()) _safe_write_cell(worksheet, 4, 9, "\n".join(lines)) cell = worksheet.cell(row=4, column=9) cell.alignment = Alignment(horizontal='left', vertical='top', wrap_text=True) logging.info(f"Written user details: {user_name}, {user_phone}") def _shift_merged_cells(ws: Worksheet, start_row: int, offset: int) -> None: new_ranges = [] for cr in list(ws.merged_cells.ranges): if cr.min_row >= start_row: ws.merged_cells.ranges.remove(cr) new_cr = CellRange( min_row=cr.min_row + offset, max_row=cr.max_row + offset, min_col=cr.min_col, max_col=cr.max_col ) new_ranges.append(new_cr) for r in new_ranges: ws.merge_cells(str(r)) def _add_row(ws: Worksheet, row: int) -> None: _shift_merged_cells(ws, row, 1) ws.insert_rows(idx=row) def _copy_row(ws: Worksheet, from_row: int, to_row: int) -> None: max_col = ws.max_column thin_border = Border( bottom=Side(style='thin'), left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin') ) for col in range(1, max_col + 1): source_cell = ws.cell(row=from_row, column=col) target_cell = ws.cell(row=to_row, column=col) if not isinstance(source_cell, MergedCell): target_cell.value = source_cell.value if source_cell.has_style: target_cell.font = copy(source_cell.font) target_cell.fill = copy(source_cell.fill) target_cell.protection = copy(source_cell.protection) target_cell.alignment = copy(source_cell.alignment) if 5 <= col <= 16: target_cell.border = thin_border target_cell.comment = copy(source_cell.comment) target_cell.hyperlink = copy(source_cell.hyperlink) ws.row_dimensions[to_row].height = 40 def _convert_to_pdf(output_excel: str, order_id: str, pdf_dir: str) -> None: try: output_file = os.path.join(output_excel, f"{order_id}.xlsx") libreoffice_home = '/var/www/.cache/.config/libreoffice/4/user' file_path = Path(output_file).resolve() pdf_dir = Path(pdf_dir).resolve() pdf_dir.mkdir(parents=True, exist_ok=True) if not file_path.exists(): raise ErrorConvertingPDF(f"Input file not found:{file_path}") command = ( f"libreoffice --headless --norestore --nolockcheck " f"--convert-to pdf --outdir '{pdf_dir}' '{file_path}'" ) env = os.environ.copy() if os.geteuid() == 0: logging.info("Running as Root User") env['XDG_RUNTIME_DIR'] = '/tmp/libreoffice-profile/' env['HOME'] = libreoffice_home os.makedirs(env['XDG_RUNTIME_DIR'], exist_ok=True) os.chmod(env['XDG_RUNTIME_DIR'], 0o700) try: subprocess.check_output( command, shell=True, executable="/bin/bash", stderr=subprocess.STDOUT, env=env ) logging.info("PDF conversion successful") except Exception as e: logging.error(f"Error in PDF Conversion: {e}") raise ErrorConvertingPDF(str(e)) except Exception as e: logging.error(f"Error converting excel to pdf: {e}") raise ErrorConvertingPDF(f"Error converting excel to pdf: {e}") def _check_ouptput_excel_exist(order_id: str, output_excel: str) -> None: output_file = os.path.join(output_excel, f"{order_id}.xlsx") if not os.path.exists(output_file): raise OuptputExcelNotFoundError(f"Output excel file does not exist: {output_file}") logging.info(f"Output excel file exists: {output_file}") def _check_ouptput_pdf_exist(order_id: str, output_pdf: str) -> None: output_file = os.path.join(output_pdf, f"{order_id}.pdf") if not os.path.exists(output_file): raise OutputPDFNotFoundError(f"Output pdf file does not exist: {output_file}") logging.info(f"Output pdf file exists: {output_file}") def template_writer(order_id: str, template_path: str) -> None: logging.info("Excel Writer Module") _check_template_exist(template_path) _check_output_dir() wb = _open_excel(template_path) worksheet = wb['Sheet2'] worksheet.sheet_view.showGridLines = False data = _serialize_order_data(order_id) logging.info(f"Data Details: {data}") invoice_details = data.get("invoice_details", {}) additional_details = data.get("additonal_details", {}) other_charges = data.get("other_charges", {}) shop_details = data.get("shop_details", {}) user_details = data.get("user_details", {}) total_calculated_charges = data.get("totals", {}) # Write header sections _write_invoice_details(worksheet, invoice_details) _write_shop_details(worksheet, shop_details) _write_user_details(worksheet, user_details) _write_additional_details(worksheet, additional_details) # Insert extra rows for multi-item orders before writing data item_details = data.get("item_details", []) row_offset = max(0, len(item_details) - 1) if row_offset > 0: for i in range(1, len(item_details)): _add_row(worksheet, 16 + i) _copy_row(worksheet, 16, 16 + i) grand_total = float(total_calculated_charges.get("Total", 0) or 0) _write_item_details(worksheet, item_details, grand_total=grand_total) _write_other_charges(worksheet, other_charges, row_offset=row_offset) _write_totals(worksheet, total_calculated_charges, row_offset=row_offset) # Save and convert to PDF output_excel = 'media/output/excel' pdf_dir = 'media/output/pdf' _save_excel(wb, output_excel, order_id) _check_ouptput_excel_exist(order_id, output_excel) _convert_to_pdf(output_excel, order_id, pdf_dir) _check_ouptput_pdf_exist(order_id, pdf_dir) logging.info("Excel Writer Module Executed Successfully")