import pandas as pd from datetime import datetime from scripts.mysql_queries import settlement_validation_qry, settlement_reconcile_v2_query from scripts.foreclosure_breakup import function1 as foreclosure_func from utils import execute_query, insert_payment_transaction, get_cloudwatch_logger logger = get_cloudwatch_logger(stream_name="settlement_reconcile") import pytz, asyncio local_tz = pytz.timezone("Asia/Kolkata") now = datetime.now(local_tz).strftime('%Y-%m-%d %H:%M:%S') # pass all settlement & written - off records in payment_transactions table def safe_insert(amount, label, data): if round(amount) >= 1: data["amount"] = amount data["payment_for"] = label insert_payment_transaction(data) async def function1(data): application_id = data.get('application_id') # data['payment_date'] = datetime.strptime(data.get('payment_date'), "%d-%m-%Y").strftime("%Y-%m-%d") # data['payment_reconciliation_date'] = datetime.strptime(data.get('payment_reconciliation_date'), "%d-%m-%Y").strftime("%Y-%m-%d") \ # if data.get('payment_reconciliation_date') is not None else data.get('payment_date') amount = data.get('amount') is_verify_flag = data.get('is_verify') request = data secondary_status = data.get('secondary_status') logger.send_log_event(f"INFO: In settlement_reconcile_v2 function. Logging request: {request}") if secondary_status is None: return {"status": "FAILED", "message": "Secondary status not provided."} if secondary_status == 'settlement-in-progress': if amount <= 0: return {"status": "FAILED", "message": "Settlement amount should be greater than 0"} if is_verify_flag is None: return {"status": "FAILED", "message": "Verify flag not provided."} # Check if case is closed, cancelled, settled, foreclosed. validate_app_id_df = execute_query(settlement_validation_qry, params = {"application_ids": tuple([application_id])}) if isinstance(validate_app_id_df, str): return {"status": "ERROR", "message": validate_app_id_df} if isinstance(validate_app_id_df, pd.DataFrame) and validate_app_id_df.empty: return {"status": "FAILED", "message": f"No data found for application_id: {application_id}"} row = validate_app_id_df.iloc[0].fillna(0).to_dict() # Convert the single-row dataframe to a dictionary if row['app_status'] == 'closed': return {"status": "FAILED", "message": f"Application_id: {application_id} is closed. Settlement entry not allowed."} if row['app_status'] == 'cancelled': return {"status": "FAILED", "message": f"Application_id: {application_id} is cancelled. Settlement entry not allowed."} if row['foreclosed'] > 0: return {"status": "FAILED", "message": f"Application_id: {application_id} is foreclosed. Settlement entry not allowed."} if row['settled'] > 0: return {"status": "FAILED", "message": f"Application_id: {application_id} is already settled. Settlement entry not allowed."} # Call foreclosure breakup API & get POS, interest OS, Bounce, penal etc values foreclosure_response = await foreclosure_func(data = {"application_id": application_id}) if foreclosure_response.get('status') == 'Success': foreclosure_msg = foreclosure_response['message'] df_reconciled = pd.DataFrame(foreclosure_response.get("data", [])) else: return {"status": "ERROR", "message": foreclosure_response.get('message', 'Unknown error')} pre_emi_due = foreclosure_msg.get('Pre-EMI due') principal_outstanding = foreclosure_msg.get('Principal due') interest_outstanding = foreclosure_msg.get('Interest Outstanding') bounce_outstanding = foreclosure_msg.get('Bounce charges due') penal_outstanding = foreclosure_msg.get('Penal charges due') total_loan_outstanding = pre_emi_due + principal_outstanding + interest_outstanding + bounce_outstanding + penal_outstanding if amount >= total_loan_outstanding: return {"status": "FAILED", "message": f"Settlement amount({amount}) >= Total loan outstanding({round(total_loan_outstanding)})."} # Calculate settlement & Write off amounts original_settlement_amount = amount # Keep for reference if secondary_status == 'settled' and is_verify_flag == 1: # if settled status passed, then woff all amounts if round(principal_outstanding) > 0: logger.send_log_event(f"INFO: POS to Woff: {round(principal_outstanding)}") safe_insert(principal_outstanding, "written_off_principal", data) if round(interest_outstanding) > 0: logger.send_log_event(f"INFO: Interest to Woff: {round(interest_outstanding)}") safe_insert(interest_outstanding, "written_off_interest", data) if round(bounce_outstanding) > 0: logger.send_log_event(f"INFO: Bounce charges to Woff: {round(bounce_outstanding)}") safe_insert(bounce_outstanding, "written_off_bounce", data) if round(penal_outstanding) > 0: logger.send_log_event(f"INFO: Penal charges to Woff: {round(penal_outstanding)}") safe_insert(penal_outstanding, "written_off_penal", data) # Add unpaid bounce & penal amount as in existing charges_waived amount for respective emi_payment_id and update waive_off_logs accordingly updated_count = 0 logs_created = 0 for row in df_reconciled.itertuples(): charges_to_waive = float(row.charges_to_be_waived) total_paid = float(row.total_amount_paid) # Update emi_payments update_ep_query = f""" update emi_payments set charges_waived = charges_waived + GREATEST(0, FLOOR({charges_to_waive})), total_payable = (pre_emi_amount + emi_amount + bounce_charges + delay_penalty) - charges_waived, amount_due = total_payable - GREATEST(0, FLOOR({total_paid})), updated_at = '{now}' where application_id = {application_id} AND id = {row.emi_payment_id}; """ execute_query(update_ep_query) updated_count += 1 # Conditionally insert into waive_off_logs if charges_to_waive > 0: insert_waive_log_query = f""" insert into waive_off_logs ( emi_payment_id, waived_by, status, created_at, updated_at, amount, application_id ) VALUES ( {row.emi_payment_id}, 107, 'Active', '{now}', '{now}', {charges_to_waive}, {application_id} ); """ execute_query(insert_waive_log_query) logs_created += 1 if updated_count > 0 or logs_created > 0: logger.send_log_event(f"INFO: Updated emi_payments for {updated_count} rows for application_id: {application_id}.") logger.send_log_event(f"INFO: Inserted waive_off_logs for {logs_created} rows for application_id: {application_id}.") settlement_done_statement = f"Written-off entries created for application_id: {application_id}." return settlement_done_statement if secondary_status == 'settlement-in-progress' and is_verify_flag == 1: # ================== Reconcile block ======================================= # Allocate to pending Principal Outstanding if principal_outstanding > 0 and amount > 0: principal_settled = round(min(amount, principal_outstanding)) logger.send_log_event(f"INFO: Principal Settled: {principal_settled}") safe_insert(principal_settled, "settled_principal", data) amount -= principal_settled principal_outstanding -= principal_settled logger.send_log_event(f"INFO: Settlement amount after Principal Settlement: {amount}") logger.send_log_event(f"INFO: Principal Outstanding after Settlement: {round(principal_outstanding)}") # Allocate to pending Interest Outstanding if interest_outstanding > 0 and amount > 0: interest_settled =round(min(amount, interest_outstanding)) logger.send_log_event(f"INFO: Interest Settled: {interest_settled}") safe_insert(interest_settled, "settled_interest", data) amount -= interest_settled interest_outstanding -= interest_settled logger.send_log_event(f"INFO: Settlement amount after Interest Settlement: {amount}") logger.send_log_event(f"INFO: Interest Outstanding after Settlement: {round(interest_outstanding)}") # Allocate to pending Bounce Outstanding if bounce_outstanding > 0 and amount > 0: bounce_settled = round(min(amount, bounce_outstanding)) logger.send_log_event(f"INFO: Bounce Settled: {bounce_settled}") safe_insert(bounce_settled, "settled_bounce", data) amount -= bounce_settled bounce_outstanding -= bounce_settled logger.send_log_event(f"INFO: Settlement amount after Bounce Settlement: {amount}") logger.send_log_event(f"INFO: Bounce Outstanding after Settlement: {round(bounce_outstanding)}") # Allocate to pending Penal Outstanding if penal_outstanding > 0 and amount > 0: penal_settled = round(min(amount, penal_outstanding)) logger.send_log_event(f"INFO: Penal Settled: {penal_settled}") safe_insert(penal_settled, "settled_penal", data) amount -= penal_settled penal_outstanding -= penal_settled logger.send_log_event(f"INFO: Settlement amount after Penal Settlement: {amount}") logger.send_log_event(f"INFO: Penal Outstanding after Settlement: {round(penal_outstanding)}") settlement_in_progress_statement = f"Settlement entries for amount ₹{original_settlement_amount} created for application_id: {application_id}" return settlement_in_progress_statement # if __name__ == "__main__": # data = { # "application_id": 12560, # "payment_date": "28-01-2025", # "amount": 1000000, # "secondary_status": "settled" # } # print(asyncio.run(function1(data)))