from fastapi import FastAPI, File, UploadFile import os import traceback from fastapi import FastAPI, File, UploadFile, HTTPException, Request from pathlib import Path # Import Path from pathlib from copy import deepcopy import time import shutil # 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 aiofiles 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 async def upload_data(file_content, original_filename, file_name, notice_type, company_id, lawyer_id): try: upload_directory = "/var/www/NoticeGen/uploads/" file_directory = "/var/www/NoticeGen/uploads/files" # upload_directory = "uploads/" # file_directory = "uploads/files" # Get the current working directory os.makedirs(file_directory, exist_ok=True) filename, file_extension = os.path.splitext(original_filename) filename = file_name.replace(" ", "_") timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") new_filename = f"{filename}_{timestamp}{file_extension}" file_name = f"{filename}_{timestamp}" file_directory_link = "http://noticegen-backend.codesrv.com/uploads/files/" file_path = os.path.join(file_directory, new_filename) new_file_path = os.path.join(file_directory_link, 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 (file, created_at, updated_at, file_name, notice_type, company_id, lawyer_id) VALUES (%s, NOW(), NOW(), %s, %s, %s, %s) """ values = ( new_file_path, file_name, notice_type, company_id, lawyer_id ) cursor.execute(insert_query, values) conn.commit() file_list_id = cursor.lastrowid # return file_list_id cursor.close() conn.close() # Full path for the new directory directory_path = Path(upload_directory) / file_name os.makedirs(directory_path, exist_ok=True) # Create the directory await insert_file_data(file_list_id, file_path, directory_path, notice_type, company_id, lawyer_id) 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, directory_path, notice_type, company_id, lawyer_id): try: conn = await connect_db() cursor = conn.cursor() # Ensure to fetch all results from previous queries or use buffered cursors cursor = conn.cursor(buffered=True) # Adding buffered=True to handle unread results df = pd.read_excel(file_path) df = df.head(3) 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 = directory_path os.makedirs(output_dir, exist_ok=True) # base_url = config.BASE_URL base_url = "http://noticegen-backend.codesrv.com" for _, row in df.iterrows(): # template_path = '/var/www/NoticeGen/notices/template.html' language_of_notice = row.get('Language of Notice') print(f"Processing record for language: {language_of_notice}") # Map the language to the correct HTML template if language_of_notice == "English & Assami": template_path = os.path.abspath('notices/eng_assam.html') if notice_type == 'LRN Translation' else os.path.abspath('notices/pre_npa_assam.html') elif language_of_notice == "English & Bengali": template_path = os.path.abspath('notices/eng_ban.html') if notice_type == 'LRN Translation' else os.path.abspath('notices/pre_npa_bengali.html') elif language_of_notice == "English & Gujrathi" or language_of_notice == "English & Gujarathi": template_path = os.path.abspath('notices/eng_guj.html') if notice_type == 'LRN Translation' else os.path.abspath('notices/pre_npa_gujrati.html') elif language_of_notice == "English & Hindi": template_path = os.path.abspath('notices/eng_hindi.html') if notice_type == 'LRN Translation' else os.path.abspath('notices/pre_npa_hindi.html') elif language_of_notice == "English & Marathi": template_path = os.path.abspath('notices/eng_mar.html') if notice_type == 'LRN Translation' else os.path.abspath('notices/pre_npa_marathi.html') elif language_of_notice == "English & Punjabi": template_path = os.path.abspath('notices/eng_punj.html') if notice_type == 'LRN Translation' else os.path.abspath('notices/pre_npa_punjabi.html') else: # Default to a template if the language is not found template_path = os.path.abspath('notices/template.html') print(f"Template path: {template_path}") # template_path = 'notices/template.html' select_query = "SELECT id FROM notice_list WHERE cl_contract_id = %s and company_id = %s" cursor.execute(select_query, (row.get('CL CONTRACT ID'),company_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 # Generate a new, unique PDF for each row now = datetime.now() month = now.strftime("%B").upper() year = now.strftime("%y") timestamp = 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"/{directory_path}/{pdf_name}" # Format the reference number ref_no = f"LRN/{month}-{year}/{serial_number:05d}" lawyer_query = """ SELECT * from lawyers where id = %s; """ cursor.execute(lawyer_query, (lawyer_id,)) lawyer_data = cursor.fetchone() # Prepare the data for the template for each record template_data = { "lawyer_name": lawyer_data[1], "lawyer_sign": lawyer_data[4], "lender_name": row.get('LENDER NAME'), "ref_number": f"LRN/{now.strftime('%B').upper()}-{now.strftime('%y')}/{serial_number:05d}", "date": now.strftime("%d-%m-%Y"), "ad_line": row.get('BORROWER NAME'), "address": f"{row.get('BORROWER ADDRESS', '')}, {str(row.get('BORROWER PIN CODE', ''))[:6]}", "recipient_name": row.get('CO-BORROWER-1 NAME'), "recipient_address": f"{row.get('CO-BORROWER-1 ADDRESS')}, {row.get('CO-BORROWER-1 CITY')}, {row.get('CO-BORROWER-1 STATE')}, {str(row.get('CO-BORROWER-1 PIN CODE', ''))[:6]}", "subject": 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, "advocate": row.get('Advocate Name'), "loan_account_id": row.get('LK LOAN ACCOUNT ID'), "cache_bust": int(time.time()) } # # Find the position of '/uploads' in the directory path uploads_index = pdf_path.find('/uploads') # Create the relative path starting from '/uploads' if uploads_index != -1: relative_path = pdf_path[uploads_index:] else: raise ValueError("The directory path does not contain '/uploads'") # Generate the full URL by concatenating the base_url and the relative path pdf_url = f"{base_url}{relative_path}" print(pdf_url) # Render and generate the PDF try: env = Environment(loader=FileSystemLoader(os.path.dirname(template_path))) template = env.get_template(os.path.basename(template_path)) html_content = template.render(template_data) # Log the HTML content being passed to PDF generation for debugging print(f"Generating PDF for data: {template_data}") options = { 'disable-smart-shrinking': '', 'no-stop-slow-scripts': '', 'enable-local-file-access': '', 'encoding': 'UTF-8', 'margin-top': '10mm', 'margin-bottom': '10mm', 'margin-left': '10mm', 'margin-right': '10mm', } # path_to_wkhtmltopdf = "/usr/bin/wkhtmltopdf" config = pdfkit.configuration(wkhtmltopdf=path_to_wkhtmltopdf) pdfkit.from_string(html_content, pdf_path, configuration=config, options=options, verbose=True) 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}") print(traceback.format_exc()) # Insert or update the notice_list entry insert_query = """ INSERT INTO notice_list ( file_list_id, company_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, %s, NOW(), NOW(), 'First Notice Send' ) ON DUPLICATE KEY UPDATE lk_loan_account_id = VALUES(lk_loan_account_id), formatted_lan = VALUES(formatted_lan), lrn = VALUES(lrn), language_of_notice = VALUES(language_of_notice), advocate_name = VALUES(advocate_name), customer_name = VALUES(customer_name), zone = VALUES(zone), state = VALUES(state), location = VALUES(location), district = VALUES(district), ncm = VALUES(ncm), opn_dpd = VALUES(opn_dpd), drr_cut_off_date = VALUES(drr_cut_off_date), current_dpd = VALUES(current_dpd), current_pos = VALUES(current_pos), lender_name = VALUES(lender_name), co_lender_name = VALUES(co_lender_name), lender_colender_ratio = VALUES(lender_colender_ratio), current_month_best_dispo = VALUES(current_month_best_dispo), date_of_disb = VALUES(date_of_disb), mob = VALUES(mob), non_starter_flag = VALUES(non_starter_flag), loan_amount = VALUES(loan_amount), pos_amount_in_lakh = VALUES(pos_amount_in_lakh), emi_amount = VALUES(emi_amount), emi_frequency = VALUES(emi_frequency), overdue_emi_amount = VALUES(overdue_emi_amount), over_due_charges = VALUES(over_due_charges), total_overdue_amount = VALUES(total_overdue_amount), over_due_date = VALUES(over_due_date), tos_amount = VALUES(tos_amount), tos_date = VALUES(tos_date), payment_link = VALUES(payment_link), lk_contact_person_name = VALUES(lk_contact_person_name), email_id = VALUES(email_id), lk_contact_person_cell_no = VALUES(lk_contact_person_cell_no), borrower_name = VALUES(borrower_name), borrower_address = VALUES(borrower_address), borrower_city = VALUES(borrower_city), borrower_state = VALUES(borrower_state), borrower_pin_code = VALUES(borrower_pin_code), borrower_email_id = VALUES(borrower_email_id), borrower_mob_no = VALUES(borrower_mob_no), co_borrower_1_name = VALUES(co_borrower_1_name), co_borrower_1_address = VALUES(co_borrower_1_address), co_borrower_1_city = VALUES(co_borrower_1_city), co_borrower_1_state = VALUES(co_borrower_1_state), co_borrower_1_pin_code = VALUES(co_borrower_1_pin_code), co_borrower_1_email_id = VALUES(co_borrower_1_email_id), co_borrower_1_mob_no = VALUES(co_borrower_1_mob_no), primary_sms_whats_app_no = VALUES(primary_sms_whats_app_no), primary_email_id = VALUES(primary_email_id), secondary_sms_whats_app_no = VALUES(secondary_sms_whats_app_no), secondary_email_id = VALUES(secondary_email_id), date_of_issue = VALUES(date_of_issue), link = VALUES(link), updated_at = NOW(), status = 'Reissued' """ values = ( file_list_id, company_id, row.get('CL CONTRACT ID'), row.get('LK LOAN ACCOUNT ID'), row.get('FORMATTED LAN'), ref_no, 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'), now.strftime("%d-%m-%Y"), pdf_url ) cursor.execute(insert_query, values) conn.commit() except Exception as e: print(f"Error inserting file data: {e}") finally: cursor.close() conn.close() async def upload_lawyer_data(signature: UploadFile, lawyer_name: str, lawyer_email: str, lawyer_address: str): try: # Set up directories upload_directory = "/var/www/NoticeGen/uploads/" # file_directory = "/var/www/NoticeGen/uploads/files" # upload_directory = "uploads/" file_directory = os.path.join(upload_directory, "signature") os.makedirs(file_directory, exist_ok=True) # Prepare file name filename, file_extension = os.path.splitext(signature.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(file_directory, new_filename) file_directory_link = f"http://noticegen-backend.codesrv.com/uploads/signature/{new_filename}" # Save the file asynchronously async with aiofiles.open(file_path, "wb") as file: while content := await signature.read(1024): # Read in chunks to avoid loading the entire file into memory await file.write(content) # Update the database with the new information conn = await connect_db() cursor = conn.cursor() insert_query = """ INSERT INTO lawyers (name, email, address, signature, created_at, updated_at) VALUES (%s, %s, %s, %s, NOW(), NOW()) """ values = ( lawyer_name, lawyer_email, lawyer_address, file_directory_link ) cursor.execute(insert_query, values) conn.commit() cursor.close() conn.close() 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 update_lawyer_data( signature: UploadFile = None, lawyer_name: str = None, lawyer_email: str = None, lawyer_address: str = None, lawyer_id: int = None ): try: # Database connection setup conn = await connect_db() cursor = conn.cursor() # Fetch the current email for the given lawyer_id current_email_query = "SELECT email FROM lawyers WHERE id = %s" cursor.execute(current_email_query, (lawyer_id,)) current_email = cursor.fetchone()[0] # Fetch the current email # Check if the email is changing if lawyer_email and lawyer_email != current_email: # Check if the new email is already used by another lawyer email_check_query = "SELECT id FROM lawyers WHERE email = %s AND id != %s" cursor.execute(email_check_query, (lawyer_email, lawyer_id)) existing_lawyer = cursor.fetchone() if existing_lawyer: raise HTTPException(status_code=400, detail="Email already in use by another lawyer") update_fields = [] update_values = [] # Add fields to be updated if lawyer_name: update_fields.append("name = %s") update_values.append(lawyer_name) if lawyer_email and lawyer_email != current_email: update_fields.append("email = %s") update_values.append(lawyer_email) if lawyer_address: update_fields.append("address = %s") update_values.append(lawyer_address) if signature: # Handle file upload upload_directory = "/var/www/NoticeGen/uploads/" file_directory = os.path.join(upload_directory, "signature") os.makedirs(file_directory, exist_ok=True) filename, file_extension = os.path.splitext(signature.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(file_directory, new_filename) file_directory_link = f"http://noticegen-backend.codesrv.com/uploads/signature/{new_filename}" async with aiofiles.open(file_path, "wb") as file: while content := await signature.read(1024): await file.write(content) update_fields.append("signature = %s") update_values.append(file_directory_link) if not update_fields: raise HTTPException(status_code=400, detail="No valid fields provided for update") # Always update the `updated_at` field update_fields.append("updated_at = NOW()") # Append lawyer_id for the WHERE clause update_values.append(lawyer_id) update_query = f""" UPDATE lawyers SET {', '.join(update_fields)} WHERE id = %s """ cursor.execute(update_query, update_values) conn.commit() cursor.close() conn.close() return True except HTTPException as e: raise e except Exception as e: print(f"Error updating lawyer data: {e}") print(traceback.format_exc()) return False