Dear {applicant_name}{' & ' + co_applicant_name if co_applicant_name else ''},
" f"Greetings from Credit Fair!
" f"{amount_msg}" f" on {due_date} for your loan application no. " f"{loan_application_number}. Please ensure sufficient balance in your " f"{bank_name} account ending with {bank_last_4} at least one day before the due date." ) if bank_details: bank_details_html = ( "Bank Details:
" "Please note that it may take up to 3 working days for the payment to be reflected in our system " f"after it has been debited from your account.
" f"If you have any questions, you can reach us at +91-8928769161 or by replying to this email at " f"payments@creditfair.in.
" f"" f"" ) # Send the email try: result = mail_service.send_mail([consumer_email], msg, subject) logger.send_log_event(json.dumps({"timestamp": request_time.strftime("%Y-%m-%d %H:%M:%S"), "level": "INFO", "event": "NACH_EMAIL_RESPONSE", "application_id": application_id, "result": result})) return result except Exception as e: logger.send_log_event(json.dumps({"timestamp": request_time.strftime("%Y-%m-%d %H:%M:%S"), "level": "ERROR", "event": "NACH_EMAIL_SEND_FAILED", "application_id": application_id, "error": str(e)})) return False # -------------------------------------------------------------------------------------------------------------------------------------------------------------- # --- Non- NACH Reminder SMS --- def send_non_nach_Sms(applicant_name, total_amount, due_date, loan_application_number,non_nach_payment_link,mobile_number,application_id,merchant_name): # Get the payment Link from the admin panel side # Template ID - 1307166607387835447 sms_non_nach_msg = ( f'Dear {applicant_name}, An installment of Rs {total_amount} for your purchase from {merchant_name} is due on {due_date} for your loan application no. {loan_application_number}' f' Since your Auto Debit i.e. NACH mandate has not yet been registered, please pay the EMI using {non_nach_payment_link}. ' f'You can reach us on +91-8928769161 or payments@creditfair.in if you have any questions. Best Regards, Team Credit Fair') try: sms_non_nach_response = quick_smart_api(sms_non_nach_msg, mobile_number,1307166607387835447,application_id) if sms_non_nach_response == 200: return True else: return False except Exception as e: logger.send_log_event(f"SMS sending failed for App ID {application_id}: {str(e)}") return False # --- Non- NACH Reminder Email --- def send_non_nach_email(applicant_name, co_applicant_name, due_date, loan_application_number, non_nach_payment_link, is_pre_emi, is_processing_fee, is_regular_emi,pre_emi_amount,emi_amount,processing_fee_amount,merchant_name, lender_id, application_id,consumer_email): amount_msg = "" if is_pre_emi: amount_msg += f"Pre-EMI of Rs {pre_emi_amount:,.0f} will be debited." if is_regular_emi and is_regular_emi !=0: amount_msg += f" + Rs {emi_amount:,.0f} regular EMI fee will be debited." elif is_processing_fee: amount_msg += f"EMI of Rs {emi_amount:,.0f} + Rs {processing_fee_amount:,.0f} processing fee will be debited." elif is_regular_emi: amount_msg += f"Regular EMI of Rs {emi_amount:,.0f} will be debited." else: amount_msg += f"EMI details are unavailable at this moment." bank_details = "" if lender_id in (4, 5): bank_details = None else: bank_details = { 3: { 'Beneficiary account number': '918020009293418', 'Beneficiary name': 'Avanse Financial Services Ltd', 'IFSC code': 'UTIB0000028', 'Bank Name': 'Axis Bank' }, 7: { 'Beneficiary account number': '000705047644', 'Beneficiary name': 'Transactree Technologies Private Limited', 'IFSC code': 'ICIC0000007', 'Bank Name': 'ICICI BANK LTD' }, 4: { 'Beneficiary account number': '200999509663', 'Beneficiary name': 'Aditya Birla Finance LTD', 'IFSC code': 'INDB0000001', 'Bank Name': 'INDUSIND BANK LTD' }, 11: { 'Beneficiary account number': '409002234039', 'Beneficiary name': 'K M GLOBAL P2P FINANCE PRIVATE LIMITED COLLECTION ACCOUNT', 'IFSC code': 'RATN0000088', 'Bank Name': 'RBL Bank' }, 2: { 'Beneficiary account number': '10245910063', 'Beneficiary name': 'BLACKSOIL CAPITAL PRIVATE LIMITED', 'IFSC code': 'IDFB0040101', 'Bank Name': 'IDFC FIRST Bank' }, 20: { 'Beneficiary account number': '10245910063', 'Beneficiary name': 'BLACKSOIL CAPITAL PRIVATE LIMITED', 'IFSC code': 'IDFB0040101', 'Bank Name': 'IDFC FIRST Bank' } }.get(lender_id, { 'Beneficiary account number': '10245910063', 'Beneficiary name': 'BLACKSOIL CAPITAL PRIVATE LIMITED', 'IFSC code': 'IDFB0040101', 'Bank Name': 'IDFC FIRST Bank' }) subject = "Reminder: Upcoming EMI Debit Notification" msg = ( f"" f"" f"Dear {applicant_name}{' , ' + co_applicant_name if co_applicant_name else ''},
" f"Greetings from Credit Fair!
" f"An installment of Rs {amount_msg} for your purchase from " f"{merchant_name} is due on {due_date} for your loan application no. {loan_application_number}.
" ) # Only add bank details if not empty if bank_details: if isinstance(bank_details, dict): bank_details_html = ( "{bank_details}
" msg += ( f"Since your Auto Debit (NACH) mandate has not yet been registered, " f"please pay the EMI using the link below:
" f"" f"Please ignore this message if you have already paid the Installment. " f"It could take up to 3 working days to update payment in our system post debit from your account.
" f"You can reach us on +91-8928769161 or " f"payments@creditfair.in if you have any questions.
" f"" f"" ) result = mail_service.send_mail([consumer_email], msg, subject) try: result = mail_service.send_mail([consumer_email], msg, subject) logger.send_log_event(json.dumps({"timestamp": request_time.strftime("%Y-%m-%d %H:%M:%S"), "level": "INFO", "event": "NON_NACH_EMAIL_RESPONSE", "application_id": application_id, "result": result})) return result except Exception as e: logger.send_log_event(json.dumps({"timestamp": request_time.strftime("%Y-%m-%d %H:%M:%S"), "level": "ERROR", "event": "NON_NACH_EMAIL_SEND_FAILED", "application_id": application_id, "error": str(e)})) return False def process_application(application_id,due_date_for_query): # check_payment_query = """ # SELECT id FROM emi_payments # WHERE application_id = :application_id # AND emi_date = :due_date # AND payment_status = 'success' # LIMIT 1 # """ # df = execute_query(check_payment_query, params={"application_id": application_id, "due_date": due_date_for_query}) # payment_exists = df # if not payment_exists.empty: # payment_exists_response_logs = logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: For Application ID {application_id} Skipping Reminder since payment already received.") # print(f"Skipping {application_id}: Payment already received.") # return # STOP HERE # foreclosure_query = """ # select consumer_application.id application_id,foreclosed,settled # from consumer_application # left join ( # select application_id, # max(case when payment_for in ('Forclouser','Foreclosed','Foreclosure') then 1 else 0 end) foreclosed, # max(case when (payment_for in ('Write-off','settled_principal','settled_interest','settled_penal','settled_bounce', # 'written_off_principal','written_off_interest','written_off_bounce','written_off_penal')) then 1 else 0 end) settled, # max(case when payment_for = 'pre_part_payment' then 1 else 0 end) pre_part_payment # from payment_transactions # where (deleted_at is null and is_deleted_at is null and refunded_at is null) # group by 1 # )as payment_transactions_cte on payment_transactions_cte.application_id = consumer_application.id # where consumer_application.id = :application_id # """ # # foreclosure_query_result = execute_query(foreclosure_query, params={"application_id": application_id}) # if not foreclosure_query_result.empty: # row = foreclosure_query_result.iloc[0] # foreclosed = row["foreclosed"] # settled = row["settled"] # # If loan is foreclosed OR settled → skip # if foreclosed == 1 or settled == 1: # logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: For Application ID {application_id} Skipping reminder since Loan is foreclosed or settled") # application_results[application_id] = { # "Email": False, # "SMS": False, # "Both_Sent": False, # "Error": "Loan is foreclosed or settled" # } # return query = """ WITH emi_calc_cte AS ( SELECT * FROM ( SELECT application_id, processing_fee, offer_id, ROW_NUMBER() OVER (PARTITION BY application_id ORDER BY created_at DESC) row_num FROM emi_calculation WHERE application_id = :application_id ) yy WHERE row_num = 1 ), pro_forma_cte AS ( SELECT * FROM ( SELECT application_id, processing_fee_by_merchant, merchant_collected_advance_amount, loan_application_no, ROW_NUMBER() OVER (PARTITION BY application_id ORDER BY created_at DESC) row_num FROM pro_forma_invoice WHERE deleted_at IS NULL AND application_id = :application_id ) yy WHERE row_num = 1 ), payment_transactions_cte AS ( SELECT application_id, SUM(amount) AS pf_paid_amount FROM payment_transactions WHERE deleted_at IS NULL AND is_deleted_at IS NULL AND refunded_at IS NULL AND payment_for IN ('processing_fee', 'processing fee') AND application_id = :application_id GROUP BY 1 ), emi_payments_cte AS ( SELECT application_id, pre_emi_amount, emi_amount, DATE_FORMAT(due_date, '%%d-%%m-%%Y') due_date FROM emi_payments WHERE DATE(due_date) = DATE(:due_date) AND application_id = :application_id ), bank_details_cte AS ( SELECT * FROM ( SELECT application_id, account_number, bank_name, ROW_NUMBER() OVER (PARTITION BY application_id ORDER BY created_at DESC) row_num FROM bank WHERE applicant_type = 1 AND application_id = :application_id ) yy WHERE row_num = 1 ), mandate_cte AS ( SELECT * FROM ( SELECT consumer_application_id application_id, status, ROW_NUMBER() OVER (PARTITION BY consumer_application_id ORDER BY created_at DESC) row_num FROM mandate WHERE consumer_application_id = :application_id and (status in ('active','submitted','accepted')) ) yy WHERE row_num = 1 ), final AS ( SELECT ca.id application_id, ca.email, ca.mobile_number, ca.lender_id, ca.name applicant_name, ca.co_applicant_name, pfc.loan_application_no loan_application_number, epc.due_date, ecc.processing_fee, IFNULL(ptc.pf_paid_amount, 0) pf_paid_amount, m.merchant_name, IF(ecc.processing_fee > 0, IF(pfc.processing_fee_by_merchant = 1, 'collected by merchant', IF(mo.processing_fee_first_emi = 1, 'collected by creditfair in 1st emi', 'collected by creditfair upfront')), 'no processing fee') AS processing_fee_type, IFNULL(epc.pre_emi_amount, 0) pre_emi_amount, IF(IFNULL(epc.pre_emi_amount, 0) > 0, 1, NULL) is_pre_emi, IFNULL(epc.emi_amount, 0) emi_amount, IF(IFNULL(epc.emi_amount, 0) > 0, 1, NULL) is_regular_emi, bdc.account_number, bdc.bank_name, IF(mc.status IN ('active','accepted'), 'nach', 'non-nach') mandate_flag FROM consumer_application ca LEFT JOIN merchant m ON m.id = ca.merchant_id LEFT JOIN pro_forma_cte pfc ON pfc.application_id = ca.id LEFT JOIN emi_payments_cte epc ON epc.application_id = ca.id LEFT JOIN emi_calc_cte ecc ON ecc.application_id = ca.id LEFT JOIN payment_transactions_cte ptc ON ptc.application_id = ca.id LEFT JOIN merchant_offers mo ON mo.id = ecc.offer_id LEFT JOIN bank_details_cte bdc ON bdc.application_id = ca.id LEFT JOIN mandate_cte mc ON mc.application_id = ca.id WHERE ca.id = :application_id and ca.lender_id != 18 ) SELECT application_id, lender_id, applicant_name, email, mobile_number, co_applicant_name, due_date, merchant_name, IFNULL(IF(processing_fee_type IN ('collected by creditfair in 1st emi', 'collected by creditfair upfront'), GREATEST(processing_fee - pf_paid_amount, 0), 0), 0) processing_fee_amount, IF(IF(processing_fee_type IN ('collected by creditfair in 1st emi', 'collected by creditfair upfront'), GREATEST(processing_fee - pf_paid_amount, 0), 0) > 0, 1, NULL) is_processing_fee, pre_emi_amount, is_pre_emi, emi_amount, is_regular_emi, loan_application_number, account_number, bank_name, mandate_flag FROM final; """ try: result = execute_query(query, params={"application_id": application_id, "due_date": due_date_for_query}) if result.empty: consumer_detail_query = logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: For Application ID {application_id} unable to fetch the consumer details") return # Extract data from the result result = result.iloc[0] applicant_name = result.get("applicant_name", "") co_applicant_name = result.get("co_applicant_name", "") mobile_number = str(result.get("mobile_number", "")) consumer_email = str(result.get("email", "")) due_date = due_date_for_query processing_fee_amount = int(result.get("processing_fee_amount", 0)) is_processing_fee = result.get("is_processing_fee", False) pre_emi_amount = int(result.get("pre_emi_amount", 0)) is_pre_emi = result.get("is_pre_emi", False) emi_amount = int(result.get("emi_amount", 0)) is_regular_emi = result.get("is_regular_emi", False) total_amount = int(pre_emi_amount + emi_amount + processing_fee_amount) loan_application_number = result.get("loan_application_number", "") account_number = str(result.get("account_number", "")) bank_name = result.get("bank_name", "") lender_id = int(result.get("lender_id", 0)) mandate_flag = result.get("mandate_flag", "") merchant_name = result.get("merchant_name", "") account_number = cf_encrypt_decrypt.wall_decrypt(account_number, key=secret_key) bank_last_4 = account_number[-4:] if account_number else "" consumer_email = cf_encrypt_decrypt.wall_decrypt(consumer_email, key=secret_key) mobile_number = cf_encrypt_decrypt.wall_decrypt(mobile_number, key=secret_key) # Send reminders based on mandate status email_status = False sms_status = False if mandate_flag == "nach": email_status = send_nach_email(applicant_name, co_applicant_name, due_date, loan_application_number, bank_name, bank_last_4, is_pre_emi, is_processing_fee, is_regular_emi, pre_emi_amount, emi_amount, processing_fee_amount, lender_id, application_id,consumer_email) sms_status = send_nach_sms(applicant_name,total_amount, due_date, loan_application_number, bank_name,bank_last_4,mobile_number,application_id) logger.send_log_event(json.dumps({"timestamp": request_time.strftime("%Y-%m-%d %H:%M:%S"), "level": "INFO", "event": "NACH_MARKED", "application_id": application_id, "email_status": email_status, "email": consumer_email, "sms_status": sms_status, "mobile": mobile_number})) else: # Non - NACH reminders non_nach_payment_link = getpaymentlink(total_amount,consumer_email,lender_id,application_id) if non_nach_payment_link: email_status = send_non_nach_email(applicant_name, co_applicant_name, due_date, loan_application_number, non_nach_payment_link, is_pre_emi, is_processing_fee, is_regular_emi,pre_emi_amount,emi_amount,processing_fee_amount,merchant_name, lender_id, application_id,consumer_email) sms_status = send_non_nach_Sms(applicant_name, total_amount, due_date, loan_application_number,non_nach_payment_link,mobile_number,application_id,merchant_name) logger.send_log_event(json.dumps({"timestamp": request_time.strftime("%Y-%m-%d %H:%M:%S"), "level": "INFO", "event": "NON_NACH_MARKED", "application_id": application_id, "email_status": email_status, "email": consumer_email, "sms_status": sms_status, "mobile": mobile_number})) else: logger.send_log_event(json.dumps({"timestamp": request_time.strftime("%Y-%m-%d %H:%M:%S"), "level": "INFO", "event": "NON_NACH_MARKED_PAYMENT_LINK_FAILED", "application_id": application_id, "reason": "Unable to get payment link"})) event_type = "NACH_REMINDER" if mandate_flag == "nach" else "Non-NACH_REMINDER" application_results[application_id] = { "Application_ID": application_id, "current_date": today_date, "emi_due_date": due_date_for_query, "event": event_type, "email_id": consumer_email if consumer_email else None, "email_status": "Success" if email_status else "Failed", "mobile_no": mobile_number if mobile_number else None, "sms_status": "Success" if sms_status else "Failed" } except Exception as e: print(f"An error occurred while processing application {application_id}: {e}") logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: An error occurred while processing application {application_id}: {e}") return def fetch_application_ids(batch_size=5000): application_due_dates = {} excludedIdsArray = [228341,222400 ,226266,242326,229891,226772,226227,227647,252896,228173,152747,221328,261745,263575,270320,230169,210342,223015,261098,266704,229514,276299,252945,271217,261013,262162,260229,274219,272550,225788,269914,225477,263747,275617,228451,272324,271757,269527,205644,195375,267179,230190,225477,277037,266474,229049,270598,261827,248137,270809,274882,244832,255914] excluded_condition = f"AND ca.id NOT IN ({', '.join(map(str, excludedIdsArray))})" if excludedIdsArray else "" query = f""" SELECT ca.id AS application_id, next_emi.due_date AS next_due_date FROM consumer_application ca LEFT JOIN ( SELECT e.application_id, e.due_date, e.emi_date FROM emi_payments e WHERE e.emi_date = ( SELECT MIN(e2.emi_date) FROM emi_payments e2 WHERE e2.application_id = e.application_id AND e2.emi_date >= CURRENT_DATE ) ) next_emi ON next_emi.application_id = ca.id WHERE ca.is_deleted IS NULL AND ca.state IN ('disbursed', 'nach registered') AND ca.merchant_id NOT IN (6418, 6517, 6598, 6865, 6600, 6746, 6754, 8378) AND ca.lender_id NOT IN (10, 12, 16) AND ca.id <> 101 {excluded_condition} AND ca.id NOT IN ( SELECT application_id FROM emi_payments WHERE application_id IS NOT NULL GROUP BY application_id HAVING MAX(emi_date) < CURRENT_DATE ) AND next_emi.due_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 5 DAY AND NOT EXISTS ( SELECT 1 FROM emi_payments p WHERE p.application_id = ca.id AND p.payment_status = 'success' AND (p.emi_date = next_emi.emi_date OR p.due_date = next_emi.due_date) ) AND NOT EXISTS ( SELECT 1 FROM payment_transactions pt WHERE pt.application_id = ca.id AND pt.deleted_at IS NULL AND pt.is_deleted_at IS NULL AND pt.refunded_at IS NULL AND pt.payment_for IN ('Forclouser','Foreclosed','Foreclosure','foreclosure_bpi','foreclosure_interest', 'foreclosure_principal','foreclosure_bounce','foreclosure_penal','foreclosure_over_payment','Write-off','settled_principal', 'settled_interest','settled_penal','settled_bounce', 'written_off_principal','written_off_interest','written_off_bounce','written_off_penal') ) AND NOT EXISTS ( SELECT 1 FROM consumer_application_additional caa WHERE caa.application_id = ca.id AND caa.is_custody = 1 ) AND NOT EXISTS ( SELECT 1 FROM consumer_application_follow_ups cafu WHERE cafu.application_id = ca.id AND cafu.id = ( SELECT MAX(cafu2.id) FROM consumer_application_follow_ups cafu2 WHERE cafu2.application_id = ca.id AND cafu2.deleted_at IS NULL ) AND cafu.follow_up_status = 'stopped' AND cafu.deleted_at IS NULL ) """ result = execute_query(query) for start in range(0, len(result), batch_size): rows = result.iloc[start:start+batch_size] if rows.empty: break for _, row in rows.iterrows(): application_due_dates[row["application_id"]] = row["next_due_date"] # print(application_due_dates) formatted_application_due_dates = { app_id: due_date.strftime("%Y-%m-%d") if due_date else None for app_id, due_date in application_due_dates.items() } logger.send_log_event(json.dumps({"timestamp": request_time.strftime("%Y-%m-%d %H:%M:%S"),"level": "INFO","event": "REMINDER_APPLICATIONS_FETCHED","application_count": len(application_due_dates),"applications": formatted_application_due_dates})) return application_due_dates def send_internal_summary(application_results, total_application): recipients = ["vedant.jadhav@creditfair.in","ashish.gupta@creditfair.in","abhishek.shetty@creditfair.in","sunil@creditfair.in","rudraksha.vaity@creditfair.in","suyog@creditfair.in","disha.doshi@creditfair.in"] total_processed = len(application_results) success_both = sum(1 for v in application_results.values() if v.get("email_status") == "Success" and v.get("sms_status") == "Success") email_only = sum(1 for v in application_results.values() if v.get("email_status") == "Success" and v.get("sms_status") != "Success") sms_only = sum(1 for v in application_results.values() if v.get("sms_status") == "Success" and v.get("email_status") != "Success") failed_apps_count = sum(1 for v in application_results.values()if v.get("email_status") != "Success" and v.get("sms_status") != "Success") attachment_path = None subject = f"Advance Reminder Summary & Report - {today_date}" internal_msg = f"""| Metric | Value |
|---|---|
| Total applications fetched by query | {total_application} |
| Total processed (attempted) | {total_processed} |
| Both SMS & Email sent | {success_both} |
| Only Email sent | {email_only} |
| Only SMS sent | {sms_only} |
| Failed (neither sent) | {failed_apps_count} |
Deatiled Logs stream: {stream_name}
""" attachment_path = None file_is_valid = False if application_results: try: timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") attachment_path = f"Advance_Reminder_Report_{timestamp}.xlsx" df = pd.DataFrame(list(application_results.values())) column_order = ["Application_ID", "current_date", "emi_due_date", "event", "email_id", "email_status", "mobile_no", "sms_status"] df = df.reindex(columns=column_order) df.to_excel(attachment_path, index=False) if os.path.exists(attachment_path) and os.path.getsize(attachment_path) > 0: file_is_valid = True else: print("Excel file is empty (0 bytes).") except Exception as e: print(f"Error creating Excel: {e}") try: if file_is_valid: mail_service.send_mail_with_attachment( customer_mail=recipients, message=internal_msg, subject=subject, attachment_path=attachment_path ) else: mail_service.send_mail(recipients, internal_msg, subject) except Exception as e: print(f"Failed to send email: {e}") finally: if attachment_path and os.path.exists(attachment_path): os.remove(attachment_path) if __name__ == "__main__": application_data_map = fetch_application_ids() application_results = {} print(f"Fetched {len(application_data_map)} applications for processing.") logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: Fetched {len(application_data_map)} applications for processing List.") with ThreadPoolExecutor(max_workers=20) as executor: future_to_id = {executor.submit(process_application, app_id, due_date): app_id for app_id, due_date in application_data_map.items() if due_date is not None} for future in as_completed(future_to_id): app_id = future_to_id[future] due_date = application_data_map.get(app_id) if due_date is None: print(f"Skipping application {app_id}: due date is None") continue try: future.result() # print(f"Successfully processed application {app_id}") except Exception as exc: print(f"Application {app_id} generated an exception: {exc}") total_application = len(application_data_map) failed_apps = [app_id for app_id, v in application_results.items() if v.get("email_status") != "Success" and v.get("sms_status") != "Success"] total_failed = len(failed_apps) logger.send_log_event( f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: " f"Summary -> Total Application ID Fetched By query: {total_application}, Failed count: {total_failed}, Failed IDs: {failed_apps}") send_internal_summary(application_results, total_application)