202 lines
6.6 KiB
Python
202 lines
6.6 KiB
Python
|
from flask import Blueprint, render_template, request, jsonify, send_file
|
||
|
|
from flask_login import login_required, current_user
|
||
|
|
from model.Report import ReportHelper
|
||
|
|
from model.Log import LogHelper
|
||
|
|
import config
|
||
|
|
from datetime import datetime
|
||
|
|
import os
|
||
|
|
import openpyxl
|
||
|
|
from openpyxl.styles import Font
|
||
|
|
from model.ContractorInfo import ContractorInfo
|
||
|
|
|
||
|
|
|
||
|
|
report_bp = Blueprint("report", __name__)
|
||
|
|
|
||
|
|
|
||
|
|
# ---------------- Report Page ----------------
|
||
|
|
@report_bp.route("/report")
|
||
|
|
@login_required
|
||
|
|
def report_page():
|
||
|
|
return render_template("/report.html")
|
||
|
|
|
||
|
|
|
||
|
|
# ---------------- Search Contractor ----------------
|
||
|
|
@report_bp.route("/search_contractor", methods=["POST"])
|
||
|
|
@login_required
|
||
|
|
def search_contractor():
|
||
|
|
|
||
|
|
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")
|
||
|
|
|
||
|
|
LogHelper.log_action(
|
||
|
|
"Search Contractor",
|
||
|
|
f"User {current_user.id} Search contractor '{subcontractor_name}'"
|
||
|
|
)
|
||
|
|
|
||
|
|
data = ReportHelper.search_contractor(
|
||
|
|
subcontractor_name,
|
||
|
|
pmc_no,
|
||
|
|
state,
|
||
|
|
district,
|
||
|
|
block,
|
||
|
|
village,
|
||
|
|
year_from,
|
||
|
|
year_to
|
||
|
|
)
|
||
|
|
|
||
|
|
return jsonify(data)
|
||
|
|
|
||
|
|
|
||
|
|
# ---------------- Contractor Report ----------------
|
||
|
|
|
||
|
|
@report_bp.route('/contractor_report/<int:contractor_id>')
|
||
|
|
@login_required
|
||
|
|
def contractor_report(contractor_id):
|
||
|
|
|
||
|
|
data = ReportHelper.get_contractor_report(contractor_id)
|
||
|
|
|
||
|
|
return render_template(
|
||
|
|
'subcontractor_report.html',
|
||
|
|
contractor_id=contractor_id,
|
||
|
|
**data
|
||
|
|
)
|
||
|
|
|
||
|
|
class FilePathData:
|
||
|
|
downloadReportFolder = "static/download"
|
||
|
|
|
||
|
|
@report_bp.route('/download_report/<int:contractor_id>')
|
||
|
|
@login_required
|
||
|
|
def download_report(contractor_id):
|
||
|
|
try:
|
||
|
|
connection = config.get_db_connection()
|
||
|
|
cursor = connection.cursor(dictionary=True)
|
||
|
|
|
||
|
|
# -------- Contractor Info --------
|
||
|
|
contractor = ContractorInfo(contractor_id)
|
||
|
|
contInfo = contractor.contInfo
|
||
|
|
|
||
|
|
if not contInfo:
|
||
|
|
return "No contractor found", 404
|
||
|
|
|
||
|
|
# -------- Invoice Data --------
|
||
|
|
cursor.callproc('FetchInvoicesByContractor', [contractor_id])
|
||
|
|
|
||
|
|
invoices = []
|
||
|
|
for result in cursor.stored_results():
|
||
|
|
invoices.extend(result.fetchall())
|
||
|
|
|
||
|
|
if not invoices:
|
||
|
|
return "No invoice data found"
|
||
|
|
|
||
|
|
# -------- Create Workbook --------
|
||
|
|
workbook = openpyxl.Workbook()
|
||
|
|
sheet = workbook.active
|
||
|
|
sheet.title = "Contractor Report"
|
||
|
|
|
||
|
|
# ================= CONTRACTOR DETAILS =================
|
||
|
|
sheet.append(["SUB CONTRACTOR DETAILS"])
|
||
|
|
sheet.cell(row=sheet.max_row, column=1).font = Font(bold=True)
|
||
|
|
sheet.append([])
|
||
|
|
|
||
|
|
sheet.append(["Name", contInfo.get("Contractor_Name") or ""])
|
||
|
|
sheet.append(["Mobile No", contInfo.get("Mobile_No") or ""])
|
||
|
|
sheet.append(["Email", contInfo.get("Email") or ""])
|
||
|
|
sheet.append(["Village", contInfo.get("Village_Name") or ""])
|
||
|
|
sheet.append(["Block", contInfo.get("Block_Name") or ""])
|
||
|
|
sheet.append(["District", contInfo.get("District_Name") or ""])
|
||
|
|
sheet.append(["State", contInfo.get("State_Name") or ""])
|
||
|
|
sheet.append(["Address", contInfo.get("Address") or ""])
|
||
|
|
sheet.append(["GST No", contInfo.get("GST_No") or ""])
|
||
|
|
sheet.append(["PAN No", contInfo.get("PAN_No") or ""])
|
||
|
|
sheet.append([])
|
||
|
|
sheet.append([])
|
||
|
|
|
||
|
|
# ================= TABLE HEADERS =================
|
||
|
|
headers = [
|
||
|
|
"PMC No", "Village", "Invoice No", "Invoice Date", "Work Type","Invoice_Details",
|
||
|
|
"Basic Amount", "Debit Amount", "After Debit Amount",
|
||
|
|
"Amount", "GST Amount", "TDS Amount", "SD Amount",
|
||
|
|
"On Commission", "Hydro Testing", "Hold Amount",
|
||
|
|
"GST SD Amount", "Final Amount",
|
||
|
|
"Payment Amount", "TDS Payment",
|
||
|
|
"Total Amount", "UTR"
|
||
|
|
]
|
||
|
|
sheet.append(headers)
|
||
|
|
for col in range(1, len(headers) + 1):
|
||
|
|
sheet.cell(row=sheet.max_row, column=col).font = Font(bold=True)
|
||
|
|
|
||
|
|
# ================= DATA =================
|
||
|
|
total_final = 0
|
||
|
|
total_payment = 0
|
||
|
|
total_amount = 0
|
||
|
|
|
||
|
|
for inv in invoices:
|
||
|
|
row = [
|
||
|
|
inv.get("PMC_No"),
|
||
|
|
inv.get("Village_Name"),
|
||
|
|
inv.get("invoice_no"),
|
||
|
|
inv.get("Invoice_Date"),
|
||
|
|
inv.get("Work_Type"),
|
||
|
|
inv.get("Invoice_Details"),
|
||
|
|
inv.get("Basic_Amount"),
|
||
|
|
inv.get("Debit_Amount"),
|
||
|
|
inv.get("After_Debit_Amount"),
|
||
|
|
inv.get("Amount"),
|
||
|
|
inv.get("GST_Amount"),
|
||
|
|
inv.get("TDS_Amount"),
|
||
|
|
inv.get("SD_Amount"),
|
||
|
|
inv.get("On_Commission"),
|
||
|
|
inv.get("Hydro_Testing"),
|
||
|
|
inv.get("Hold_Amount"),
|
||
|
|
inv.get("GST_SD_Amount"),
|
||
|
|
inv.get("Final_Amount"),
|
||
|
|
inv.get("Payment_Amount"),
|
||
|
|
inv.get("TDS_Payment_Amount"),
|
||
|
|
inv.get("Total_Amount"),
|
||
|
|
inv.get("UTR")
|
||
|
|
]
|
||
|
|
|
||
|
|
total_final += float(inv.get("Final_Amount") or 0)
|
||
|
|
total_payment += float(inv.get("Payment_Amount") or 0)
|
||
|
|
total_amount += float(inv.get("Total_Amount") or 0)
|
||
|
|
|
||
|
|
sheet.append(row)
|
||
|
|
|
||
|
|
# ================= TOTAL ROW =================
|
||
|
|
sheet.append([])
|
||
|
|
sheet.append([
|
||
|
|
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
|
||
|
|
"TOTAL",
|
||
|
|
total_final,
|
||
|
|
total_payment,
|
||
|
|
"",
|
||
|
|
total_amount,
|
||
|
|
""
|
||
|
|
])
|
||
|
|
|
||
|
|
# ================= AUTO WIDTH =================
|
||
|
|
for column in sheet.columns:
|
||
|
|
max_length = 0
|
||
|
|
column_letter = column[0].column_letter
|
||
|
|
for cell in column:
|
||
|
|
if cell.value:
|
||
|
|
max_length = max(max_length, len(str(cell.value)))
|
||
|
|
sheet.column_dimensions[column_letter].width = max_length + 2
|
||
|
|
|
||
|
|
# ================= SAVE FILE =================
|
||
|
|
output_folder = "downloads"
|
||
|
|
os.makedirs(output_folder, exist_ok=True)
|
||
|
|
filename = f"Contractor_Report_{contInfo.get('Contractor_Name')}.xlsx"
|
||
|
|
output_file = os.path.join(output_folder, filename)
|
||
|
|
workbook.save(output_file)
|
||
|
|
|
||
|
|
return send_file(output_file, as_attachment=True)
|
||
|
|
|
||
|
|
except Exception as e:
|
||
|
|
return str(e)
|