138 lines
6.0 KiB
Python
138 lines
6.0 KiB
Python
import config
|
|
from flask_login import current_user
|
|
from model.Log import LogHelper
|
|
|
|
from services.Generalservice import GeneralUse
|
|
from model.FolderAndFile import FolderAndFile
|
|
from model.Report import ReportHelper
|
|
|
|
class PmcReport:
|
|
data=[]
|
|
|
|
# @staticmethod
|
|
# def get_pmc_report(pmc_no):
|
|
|
|
# connection = config.get_db_connection()
|
|
# cursor = connection.cursor(dictionary=True, buffered=True)
|
|
|
|
# try:
|
|
# pmc_info = GeneralUse.execute_sp(cursor, 'GetContractorInfoByPmcNo', [pmc_no], True)
|
|
# cursor.callproc("Get_pmc_hold_types", (pmc_no, pmc_info["Contractor_Id"]))
|
|
# hold_types = next(cursor.stored_results()).fetchall()
|
|
|
|
# # Extract hold_type_ids
|
|
# hold_type_ids = [ht['hold_type_id'] for ht in hold_types]
|
|
|
|
# invoices = []
|
|
# hold_type_ids_str = ",".join(map(str, hold_type_ids))
|
|
# cursor.callproc('GetInvoices_WithHold',[pmc_no, pmc_info["Contractor_Id"], hold_type_ids_str])
|
|
# for result in cursor.stored_results():
|
|
# invoices = result.fetchall()
|
|
|
|
# gst_rel = GeneralUse.execute_sp(cursor, 'GetGSTReleaseByPMC', [pmc_no])
|
|
|
|
# hold_release = GeneralUse.execute_sp(cursor, 'GetHoldReleaseByPMC', [pmc_no])
|
|
|
|
# credit_note = GeneralUse.execute_sp(cursor, 'GetCreditNoteByPMC', [pmc_no])
|
|
|
|
# payments = GeneralUse.execute_sp(cursor, 'GetPaymentsByPMC', [pmc_no])
|
|
|
|
# totals = {
|
|
# "sum_invo_basic_amt": sum(row.get('Basic_Amount', 0) or 0 for row in invoices),
|
|
# "sum_invo_debit_amt": sum(row.get('Debit_Amount', 0) or 0 for row in invoices),
|
|
# "sum_invo_after_debit_amt": sum(row.get('After_Debit_Amount', 0) or 0 for row in invoices),
|
|
# "sum_invo_amt": sum(row.get('Amount', 0) or 0 for row in invoices),
|
|
# "sum_invo_gst_amt": sum(row.get('GST_Amount', 0) or 0 for row in invoices),
|
|
# "sum_invo_tds_amt": sum(row.get('TDS_Amount', 0) or 0 for row in invoices),
|
|
# "sum_invo_ds_amt": sum(row.get('SD_Amount', 0) or 0 for row in invoices),
|
|
# "sum_invo_on_commission": sum(row.get('On_Commission', 0) or 0 for row in invoices),
|
|
# "sum_invo_hydro_test": sum(row.get('Hydro_Testing', 0) or 0 for row in invoices),
|
|
# "sum_invo_gst_sd_amt": sum(row.get('GST_SD_Amount', 0) or 0 for row in invoices),
|
|
# "sum_invo_final_amt": sum(row.get('Final_Amount', 0) or 0 for row in invoices),
|
|
# "sum_invo_hold_amt": sum(row.get('hold_amount', 0) or 0 for row in invoices),
|
|
# "sum_gst_basic_amt": sum(row.get('basic_amount', 0) or 0 for row in gst_rel),
|
|
# "sum_gst_final_amt": sum(row.get('final_amount', 0) or 0 for row in gst_rel),
|
|
# "sum_pay_payment_amt": sum(row.get('Payment_Amount', 0) or 0 for row in payments),
|
|
# "sum_pay_tds_payment_amt": sum(row.get('TDS_Payment_Amount', 0) or 0 for row in payments),
|
|
# "sum_pay_total_amt": sum(row.get('Total_amount', 0) or 0 for row in payments)
|
|
# }
|
|
|
|
# return {
|
|
# "info": pmc_info,
|
|
# "invoices": invoices,
|
|
# "hold_types": hold_types,
|
|
# "gst_rel": gst_rel,
|
|
# "payments": payments,
|
|
# "credit_note": credit_note,
|
|
# "hold_release": hold_release,
|
|
# "total": totals
|
|
# }
|
|
|
|
# finally:
|
|
# cursor.close()
|
|
# connection.close()
|
|
|
|
|
|
# @staticmethod
|
|
# def download_pmc_report(pmc_no):
|
|
|
|
# connection = config.get_db_connection()
|
|
# if not connection:
|
|
# return None
|
|
|
|
# cursor = connection.cursor(dictionary=True)
|
|
|
|
# try:
|
|
# filename = f"PMC_Report_{pmc_no}.xlsx"
|
|
|
|
# output_folder = FolderAndFile.get_download_folder()
|
|
# output_file = FolderAndFile.get_download_path(filename)
|
|
|
|
# contractor_info = GeneralUse.execute_sp(cursor, 'GetContractorInfoByPmcNo', [pmc_no])
|
|
|
|
# contractor_info = contractor_info[0] if contractor_info else None
|
|
# print("contractor_info:::",contractor_info)
|
|
|
|
# if not contractor_info:
|
|
# return None
|
|
|
|
# hold_types = GeneralUse.execute_sp(cursor, 'GetHoldTypesByContractor',[contractor_info["Contractor_Id"]])
|
|
|
|
# hold_type_map = {ht['hold_type_id']: ht['hold_type'] for ht in hold_types}
|
|
|
|
# invoices = GeneralUse.execute_sp(cursor, 'GetInvoicesByContractorOrPMCNo', [None,pmc_no])
|
|
|
|
# credit_notes = GeneralUse.execute_sp(cursor, 'NewGetCreditNotesByPMCNo', [pmc_no])
|
|
# credit_note_map = {}
|
|
# for cn in credit_notes:
|
|
|
|
# key = (str(cn['PMC_No']).strip())
|
|
# credit_note_map.setdefault(key, []).append(cn)
|
|
|
|
# hold_amounts = GeneralUse.execute_sp(cursor, 'GetHoldAmountsByContractor', [contractor_info["Contractor_Id"]])
|
|
|
|
# gst_releases = GeneralUse.execute_sp(cursor, 'GetGSTReleaseDetailsByPMC', [pmc_no])
|
|
# gst_release_map = {}
|
|
# for gr in gst_releases:
|
|
# key = (str(gr['PMC_No']).strip())
|
|
# gst_release_map.setdefault(key, []).append(gr)
|
|
|
|
# # ================= DATA MAPPING =================
|
|
# hold_data = {}
|
|
# for h in hold_amounts:
|
|
# hold_data.setdefault(h['Invoice_Id'], {})[h['hold_type_id']] = h['hold_amount']
|
|
|
|
# # ================= LOG =================
|
|
# LogHelper.log_action("Download PMC Report",f"User {current_user.id} Download PMC Report '{pmc_no}'")
|
|
|
|
# ReportHelper.generate_excel(
|
|
# 0, contractor_info, invoices, hold_types, hold_data,
|
|
# credit_note_map,gst_release_map, output_file)
|
|
|
|
# return output_folder, filename
|
|
|
|
# finally:
|
|
# cursor.close()
|
|
# connection.close()
|
|
|