from fastapi import FastAPI, File, UploadFile import os import traceback # import sys # sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))) import config from datetime import datetime from models.connect import connect_db import pdfkit import pandas as pd import asyncio from jinja2 import Environment, FileSystemLoader app = FastAPI() # Specify the path to wkhtmltopdf if not in PATH # path_to_wkhtmltopdf = r"C:\Program Files\wkhtmltopdf\bin\wkhtmltopdf.exe" path_to_wkhtmltopdf = "/usr/bin/wkhtmltopdf" config = pdfkit.configuration(wkhtmltopdf=path_to_wkhtmltopdf) def safe_convert_to_int(value): try: return int(value) except ValueError: return None # or return a default value or np.nan def create_pdf_from_template(template_path, data, pdf_path): env = Environment(loader=FileSystemLoader(os.path.dirname(template_path))) template = env.get_template(os.path.basename(template_path)) html_content = template.render(data) options = { 'disable-smart-shrinking': '', 'no-stop-slow-scripts': '', 'enable-local-file-access': '', } pdfkit.from_string(html_content, pdf_path, configuration=config, options=options) async def upload_data(file_content, original_filename, file_name, notice_type): try: # upload_directory = "/var/www/NoticeGen/uploads/" # upload_directory = "uploads/" # Get the current working directory current_directory = os.getcwd() print(f"Current Directory: {current_directory}") # Define the upload directory (current directory in this case) upload_directory = current_directory os.makedirs(upload_directory, exist_ok=True) filename, file_extension = os.path.splitext(original_filename) filename = filename.replace(" ", "_") timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") new_filename = f"{filename}_{timestamp}{file_extension}" file_path = os.path.join(upload_directory, new_filename) print(file_path) with open(file_path, "wb") as file: file.write(file_content) conn = await connect_db() cursor = conn.cursor() insert_query = """ INSERT INTO file_list (filename, created_at, updated_at, file_name, notice_type) VALUES (%s, NOW(), NOW(), %s, %s) """ values = ( file_path, file_name, notice_type ) cursor.execute(insert_query, values) conn.commit() file_list_id = cursor.lastrowid cursor.close() conn.close() # await insert_file_data(file_list_id, file_path) return True except Exception as e: print(f"Error saving file: {e}") print(traceback.format_exc()) # Print full traceback for debugging return False async def insert_file_data(file_list_id, file_path): try: conn = await connect_db() cursor = conn.cursor() df = pd.read_excel(file_path) # df = df.head(1) df = df.where(pd.notnull(df), None) date_columns = ['DRR CUT OFF DATE', 'OVER DUE DATE', 'TOS Date', 'DATE OF DISB.'] for col in date_columns: # df[col] = pd.to_datetime(df[col], errors='coerce').dt.strftime('%Y-%m-%d') df[col] = pd.to_datetime(df[col], dayfirst=True, errors='coerce').dt.strftime('%Y-%m-%d') df = df.fillna('') output_dir = '/var/www/NoticeGen/generated_pdfs/' # output_dir = 'generated_pdfs/' os.makedirs(output_dir, exist_ok=True) template_path = '/var/www/NoticeGen/notices/template.html' # template_path = 'notices/template.html' # base_url = config.BASE_URL base_url = "http://noticegen-backend.codesrv.com" for _, row in df.iterrows(): select_query = "SELECT id FROM notice_list WHERE cl_contract_id = %s" cursor.execute(select_query, (row.get('CL CONTRACT ID'),)) existing_record = cursor.fetchone() if existing_record: # Record exists, use the existing ID as the serial number serial_number = existing_record[0] else: # Record doesn't exist, find the next incremental ID cursor.execute("SELECT MAX(id) FROM notice_list") max_id = cursor.fetchone()[0] serial_number = (max_id + 1) if max_id is not None else 1 now = datetime.now() month = now.strftime("%B").upper() year = now.strftime("%y") # Format the reference number ref_no = f"LRN/{month}-{year}/{serial_number:05d}" # Assuming 'row' is a dictionary or pandas Series with proper data retrieval borrower_address = row.get('BORROWER ADDRESS', '') borrower_pincode = row.get('BORROWER PIN CODE', '') # Check and convert the pin code to a string and slice to first 6 characters if necessary if borrower_pincode is not None and borrower_pincode != '': borrower_pincode = str(borrower_pincode)[:6] else: borrower_pincode = 'No PIN' # Default text if PIN code is missing or empty # Constructing the address string with the PIN code address = f"{borrower_address}, {borrower_pincode}" recipient_address = f"{row.get('CO-BORROWER-1 ADDRESS')}, {row.get('CO-BORROWER-1 CITY')}, {row.get('CO-BORROWER-1 STATE')}, {row.get('CO-BORROWER-1 ADDRESS', '')[:6]}" # Prepare data for the template template_data = { "ref_number": ref_no, "date": datetime.now().strftime("%d-%m-%Y"), "ad_line": row.get('BORROWER NAME'), "address": address, "recipient_name": row.get('CO-BORROWER-1 NAME'), "recipient_address": recipient_address, "subject": f"LOAN RECALL NOTICE - LOAN AGREEMENT NO. {row.get('FORMATTED LAN')}", "loan_amount": row.get('LOAN AMOUNT'), "loan_agreement_no": row.get('FORMATTED LAN'), "total_due": row.get('TOTAL OVERDUE AMOUNT'), "due_date": row.get('OVER DUE DATE'), "representative_name": row.get('LK CONTACT PERSON NAME'), "representative_contact": row.get('LK CONTACT PERSON CELL NO'), "base_url": base_url } timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") unique_id = row.get('CL CONTRACT ID') pdf_file = os.path.join(output_dir, f'{unique_id}_{timestamp}.pdf') pdf_name = f'{unique_id}_{timestamp}.pdf' pdf_path = f"/generated_pdfs/{pdf_name}" # print(pdf_file) try: create_pdf_from_template(template_path, template_data, pdf_file) print(f"Generated PDF for {row.get('CL CONTRACT ID')}") except Exception as e: print(f"Error generating PDF for {row.get('CL CONTRACT ID')}: {e}") if existing_record: update_query = """ UPDATE notice_list SET lk_loan_account_id = %s, formatted_lan = %s, lrn = %s, language_of_notice = %s, advocate_name = %s, customer_name = %s, zone = %s, state = %s, location = %s, district = %s, ncm = %s, opn_dpd = %s, drr_cut_off_date = %s, current_dpd = %s, current_pos = %s, lender_name = %s, co_lender_name = %s, lender_colender_ratio = %s, current_month_best_dispo = %s, date_of_disb = %s, mob = %s, non_starter_flag = %s, loan_amount = %s, pos_amount_in_lakh = %s, emi_amount = %s, emi_frequency = %s, overdue_emi_amount = %s, over_due_charges = %s, total_overdue_amount = %s, over_due_date = %s, tos_amount = %s, tos_date = %s, payment_link = %s, lk_contact_person_name = %s, email_id = %s, lk_contact_person_cell_no = %s, borrower_name = %s, borrower_address = %s, borrower_city = %s, borrower_state = %s, borrower_pin_code = %s, borrower_email_id = %s, borrower_mob_no = %s, co_borrower_1_name = %s, co_borrower_1_address = %s, co_borrower_1_city = %s, co_borrower_1_state = %s, co_borrower_1_pin_code = %s, co_borrower_1_email_id = %s, co_borrower_1_mob_no = %s, primary_sms_whats_app_no = %s, primary_email_id = %s, secondary_sms_whats_app_no = %s, secondary_email_id = %s, date_of_issue = %s, updated_at = NOW() WHERE id = %s """ values = ( row.get('LK LOAN ACCOUNT ID'), row.get('FORMATTED LAN'), row.get('LRN'), row.get('Language of Notice'), row.get('Advocate Name'), row.get('CUSTOMER NAME'), row.get('ZONE'), row.get('STATE'), row.get('LOCATION'), row.get('DISTRICT'), row.get('NCM'), row.get('OPN DPD'), row.get('DRR CUT OFF DATE'), row.get('CURRENT DPD'), row.get('CURRENT POS'), row.get('LENDER NAME'), row.get('CO-LENDER NAME'), row.get('LENDER-COLENDER RATIO'), row.get('CURRENT MONTH BEST DISPO'), row.get('DATE OF DISB.'), row.get('MOB'), row.get('NON-STARTER FLAG'), row.get('LOAN AMOUNT'), row.get('POS Amount in Lakh'), row.get('EMI AMOUNT'), row.get('EMI FREQUENCY'), row.get('OVERDUE EMI AMOUNT'), row.get('OVER DUE CHARGES'), row.get('TOTAL OVERDUE AMOUNT'), row.get('OVER DUE DATE'), row.get('TOS AMOUNT'), row.get('TOS Date'), row.get('PAYMENT LINK'), row.get('LK CONTACT PERSON NAME'), row.get('EMAIL ID'), row.get('LK CONTACT PERSON CELL NO'), row.get('BORROWER NAME'), row.get('BORROWER ADDRESS'), row.get('BORROWER CITY'), row.get('BORROWER STATE'), row.get('BORROWER PIN CODE'), row.get('BORROWER EMAIL ID'), row.get('BORROWER MOB NO'), row.get('CO-BORROWER-1 NAME'), row.get('CO-BORROWER-1 ADDRESS'), row.get('CO-BORROWER-1 CITY'), row.get('CO-BORROWER-1 STATE'), row.get('CO-BORROWER-1 PIN CODE'), row.get('CO-BORROWER-1 EMAIL ID'), row.get('CO-BORROWER-1 MOB NO'), row.get('Primary SMS/Whats App No'), row.get('Primary Email ID'), row.get('Secondary SMS/Whats App No'), row.get('Secondary Email ID'), row.get('date of issue'), existing_record[0], ) cursor.execute(update_query, values) insert_query = """ INSERT INTO notice_list_data ( notice_list_id, file_list_id, cl_contract_id, status, link, created_at, updated_at ) VALUES ( %s, %s, %s, %s, %s, NOW(), NOW() ) """ insert_values = ( existing_record[0], file_list_id, row.get('CL CONTRACT ID'), 'Reissued', pdf_path ) # print("Running SQL query:", insert_query) # print("Values:", insert_values) cursor.execute(insert_query, insert_values) else: insert_query = """ INSERT INTO notice_list ( file_list_id, cl_contract_id, lk_loan_account_id, formatted_lan, lrn, language_of_notice, advocate_name, customer_name, zone, state, location, district, ncm, opn_dpd, drr_cut_off_date, current_dpd, current_pos, lender_name, co_lender_name, lender_colender_ratio, current_month_best_dispo, date_of_disb, mob, non_starter_flag, loan_amount, pos_amount_in_lakh, emi_amount, emi_frequency, overdue_emi_amount, over_due_charges, total_overdue_amount, over_due_date, tos_amount, tos_date, payment_link, lk_contact_person_name, email_id, lk_contact_person_cell_no, borrower_name, borrower_address, borrower_city, borrower_state, borrower_pin_code, borrower_email_id, borrower_mob_no, co_borrower_1_name, co_borrower_1_address, co_borrower_1_city, co_borrower_1_state, co_borrower_1_pin_code, co_borrower_1_email_id, co_borrower_1_mob_no, primary_sms_whats_app_no, primary_email_id, secondary_sms_whats_app_no, secondary_email_id, date_of_issue, link, created_at, updated_at, status ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW(), 'First Notice Send' ) """ values = ( file_list_id, row.get('CL CONTRACT ID'), row.get('LK LOAN ACCOUNT ID'), row.get('FORMATTED LAN'), row.get('LRN'), row.get('Language of Notice'), row.get('Advocate Name'), row.get('CUSTOMER NAME'), row.get('ZONE'), row.get('STATE'), row.get('LOCATION'), row.get('DISTRICT'), row.get('NCM'), row.get('OPN DPD'), row.get('DRR CUT OFF DATE'), row.get('CURRENT DPD'), row.get('CURRENT POS'), row.get('LENDER NAME'), row.get('CO-LENDER NAME'), row.get('LENDER-COLENDER RATIO'), row.get('CURRENT MONTH BEST DISPO'), row.get('DATE OF DISB.'), row.get('MOB'), row.get('NON-STARTER FLAG'), row.get('LOAN AMOUNT'), row.get('POS Amount in Lakh'), row.get('EMI AMOUNT'), row.get('EMI FREQUENCY'), row.get('OVERDUE EMI AMOUNT'), row.get('OVER DUE CHARGES'), row.get('TOTAL OVERDUE AMOUNT'), row.get('OVER DUE DATE'), row.get('TOS AMOUNT'), row.get('TOS Date'), row.get('PAYMENT LINK'), row.get('LK CONTACT PERSON NAME'), row.get('EMAIL ID'), row.get('LK CONTACT PERSON CELL NO'), row.get('BORROWER NAME'), row.get('BORROWER ADDRESS'), row.get('BORROWER CITY'), row.get('BORROWER STATE'), row.get('BORROWER PIN CODE'), row.get('BORROWER EMAIL ID'), row.get('BORROWER MOB NO'), row.get('CO-BORROWER-1 NAME'), row.get('CO-BORROWER-1 ADDRESS'), row.get('CO-BORROWER-1 CITY'), row.get('CO-BORROWER-1 STATE'), row.get('CO-BORROWER-1 PIN CODE'), row.get('CO-BORROWER-1 EMAIL ID'), row.get('CO-BORROWER-1 MOB NO'), row.get('Primary SMS/Whats App No'), row.get('Primary Email ID'), row.get('Secondary SMS/Whats App No'), row.get('Secondary Email ID'), row.get('date of issue'), pdf_path ) cursor.execute(insert_query, values) conn.commit() except Exception as e: print(f"Error inserting file data: {e}") finally: cursor.close() conn.close()