Payment_Reconciliation/model/Report.py

import config
from datetime import datetime
from flask import send_file
import openpyxl
from openpyxl.styles import Font, PatternFill

from model.FolderAndFile import FolderAndFile
from services.Generalservice import GeneralUse

class ReportHelper:
    isSuccess = False
    resultMessage = ""
    data=[]

    def __init__(self):
        self.isSuccess = False
        self.resultMessage = ""
        self.data = []


    @staticmethod
    def search_contractor(request):
        subcontractor_name = request.form.get("subcontractor_name")
        pmc_no = request.form.get("pmc_no")
        state = request.form.get("state")
        district = request.form.get("district")
        block = request.form.get("block")
        village = request.form.get("village")
        year_from = request.form.get("year_from")
        year_to = request.form.get("year_to")

        connection = config.get_db_connection()
        if not connection:
            return []

        cursor = connection.cursor(dictionary=True)

        try:
            data = GeneralUse.execute_sp(cursor,"search_contractor_info",[
                    subcontractor_name or None,
                    pmc_no or None,
                    state or None,
                    district or None,
                    block or None,
                    village or None,
                    year_from or None,
                    year_to or None
                ])

        except Exception as e:
            print(f"Error in search_contractor: {e}")
            data = []

        finally:
            cursor.close()
            connection.close()

        return data


    @staticmethod
    def get_contractor_info(contractor_id):
        from model.ContractorInfo import ContractorInfo
        contractor = ContractorInfo(contractor_id)
        return contractor.contInfo if contractor.contInfo else None


    # call this method for excel formate written 
    @staticmethod
    def generate_excel(contractor_id, contInfo, invoices, hold_types, hold_data,
                 credit_note_map, gst_release_map, output_file):
                 
        workbook = openpyxl.Workbook()
        sheet = workbook.active
        sheet.title = "Contractor Report"

        # Contractor Info
        for field, value in contInfo.items():
            sheet.append([field.replace("_", " "), value])
        sheet.append([])

        # Headers
        base_headers = ["PMC No", "Village", "Work Type", "Invoice Details", "Invoice Date", "Invoice No",
                        "Basic Amount", "Debit", "After Debit Amount", "GST (18%)", "Amount", "TDS (1%)",
                        "SD (5%)", "On Commission", "Hydro Testing", "GST SD Amount"]

        hold_headers = [ht['hold_type'] for ht in hold_types]

        payment_headers = ["Final Amount", "Payment Amount", "TDS Payment", "Total Paid", "UTR"]

        all_headers = base_headers + hold_headers + payment_headers
        sheet.append(all_headers)

        for cell in sheet[sheet.max_row]:
            cell.font = Font(bold=True)
            cell.fill = PatternFill(start_color="ADD8E6", end_color="ADD8E6", fill_type="solid")

        processed_gst_releases = set()
        appended_credit_keys = set()
        previous_pmc_no = None

        for inv in invoices:
            pmc_no = str(inv["PMC_No"]).strip()

            invoice_no = (
                inv["invoice_no"].replace(" ", "") if inv["invoice_no"] else ""
                if inv["invoice_no"] not in (None, "", 0)
                else ""
            )   
            key = (pmc_no)

            # Yellow separator
            if previous_pmc_no and pmc_no != previous_pmc_no:
                sheet.append([""] * len(all_headers))
                yellow_fill = PatternFill(start_color="FFFF99", end_color="FFFF99", fill_type="solid")
                for cell in sheet[sheet.max_row]:
                    cell.fill = yellow_fill

            previous_pmc_no = pmc_no

            # Invoice Row
            row = [
                pmc_no,
                inv.get("Village_Name", ""),
                inv.get("Work_Type", ""),
                inv.get("Invoice_Details", ""),
                inv.get("Invoice_Date", ""),
                # inv.get("invoice_no",""),
                invoice_no,
                inv.get("Basic_Amount", ""),
                inv.get("Debit_Amount", ""),
                inv.get("After_Debit_Amount", ""),
                inv.get("GST_Amount", ""),
                inv.get("Amount", ""),
                inv.get("TDS_Amount", ""),
                inv.get("SD_Amount", ""),
                inv.get("On_Commission", ""),
                inv.get("Hydro_Testing", ""),
                inv.get("GST_SD_Amount", "")
            ]

            # Hold values
            invoice_holds = hold_data.get(inv["Invoice_Id"], {})
            for ht_id in [ht['hold_type_id'] for ht in hold_types]:
                row.append(invoice_holds.get(ht_id, ""))

            # Payment values
            row += [
                inv.get("Final_Amount", ""),
                inv.get("Payment_Amount", ""),
                inv.get("TDS_Payment_Amount", ""),
                inv.get("Total_Amount", ""),
                inv.get("UTR", "")
            ]

            sheet.append(row)

            # GST Releases
            if key in gst_release_map and key not in processed_gst_releases:
                for gr in gst_release_map[key]:
                    gst_row = [
                        pmc_no, "", "", "GST Release Note", "", gr.get("Invoice_No", ""),
                        gr.get("Basic_Amount", ""), "", "", "", "", "", "", "", "", ""
                    ]

                    gst_row += [""] * len(hold_headers)

                    gst_row += [
                        gr.get("Final_Amount", ""),
                        "",
                        "",
                        gr.get("Total_Amount", ""),
                        gr.get("UTR", "")
                    ]

                    sheet.append(gst_row)

                processed_gst_releases.add(key)

            # Credit Notes
            if key in credit_note_map and key not in appended_credit_keys:
                for cn in credit_note_map[key]:
                    cn_row = [
                        pmc_no, "", "", cn.get("Invoice_Details", "Credit Note"), "",
                        cn.get("Invoice_No", ""),
                        cn.get("Basic_Amount", ""),
                        cn.get("Debit_Amount", ""),
                        cn.get("After_Debit_Amount", ""),
                        cn.get("GST_Amount", ""),
                        cn.get("Amount", ""),
                        "", "", "", "", ""
                    ]

                    cn_row += [""] * len(hold_headers)

                    cn_row += [
                        cn.get("Final_Amount", ""),
                        "",
                        "",
                        cn.get("Total_Amount", ""),
                        cn.get("UTR", "")
                    ]

                    sheet.append(cn_row)

                appended_credit_keys.add(key)

        # SAVE ONCE AT END
        workbook.save(output_file)