import logging from datetime import datetime, timedelta from repository import Repository import sql_query_config from utils.php_encrypt_decrypt import encrypt from exceptions.custom_exceptions import CustomExceptions import config from utils.hashing import hashids class EMIRepository(Repository): def get_product_list(self, merchant_id: int): try: logging.debug('querying the database...') result = self.query_results.fetch_query_results(sql_query_config.products_query, column_name=('merchant_id',) , params=(merchant_id,)) return result except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def create_application(self, name, email, mobile, created_at, merchant_id, center_id, loan_amount, gender, pincode, vendor_id, product_id): try: logging.debug('inserting operations started...') consumer_duplicate_check = True consumer_application_row = self.query_results.fetch_query_results( sql_query_config.dedupe_consumer_application, column_name=('mobile_number', 'email',), params=(encrypt(str(mobile)), encrypt(str(email)),)) if consumer_application_row and not consumer_application_row[0]['is_deleted'] and config.DE_DUPE_CHECK: diff = datetime.now() - consumer_application_row[0]['created_at'] consumer_duplicate_check = False if diff.days < 180 else True if consumer_duplicate_check: logging.debug('dedupe check passed') encrypt_phone = encrypt(str(mobile)) encrypt_email = encrypt(email) gender_str = 'male' if gender == '1' else 'female' lender_id = 20 category_id = self.query_results.fetch_query_results(sql_query_config.merchant_category_join, column_name= ('id',), params=(merchant_id,)) user_id = self.query_results.insertion(False, statement=sql_query_config.insert_user, params=(name, email, mobile, created_at, merchant_id, center_id, gender_str, '1')) applicant_id = self.query_results.insertion(False, statement=sql_query_config.insert_customer_query, params=(name, encrypt_email, encrypt_phone, created_at, gender)) application_id = self.query_results.insertion(False, statement=sql_query_config.insert_consumer_application, params=(merchant_id, name, encrypt_email, encrypt_phone, loan_amount, user_id, applicant_id, gender_str.capitalize(), pincode, created_at, 'form completed', lender_id, loan_amount, 'EMI CHECKOUT', created_at, category_id[0]['id']), ) self.query_results.insertion(False, statement=sql_query_config.insert_consumer_application_element, params=( created_at, product_id, center_id, application_id, 'ENGLISH', vendor_id, 2, None)) self.query_results.insertion(True, statement=sql_query_config.insert_status_log, params=(application_id, user_id, 'form completed', created_at)) return True, application_id return False, consumer_application_row[0]['id'] except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_product_details(self, product_id): try: result = self.query_results.fetch_query_results(sql_query_config.products_query, column_name=('id',), params=(product_id,)) if result: return result[0]['name'], result[0]['warranty'] return None, None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_application_id(self, mobile): try: result = self.query_results.fetch_query_results(sql_query_config.consumer_application, column_name=('mobile_number',), params=(encrypt(str(mobile)),)) if result: return result[0]['id'] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_application_details_by_mobile(self, mobile): try: result = self.query_results.fetch_query_results(sql_query_config.consumer_application, column_name=('mobile_number',), params=(encrypt(str(mobile)),)) if result: return result[0] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def check_merchant_id(self, merchant_id): try: result = self.query_results.fetch_query_results(sql_query_config.merchant_query, column_name=('id',), params=(merchant_id,)) if result: return True, result[0]['merchant_name'] return False, None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def check_center_id(self, merchant_id, center_id): try: result = self.query_results.fetch_query_results(sql_query_config.merchant_location_check, column_name=('merchant_id', 'id'), params=(merchant_id, center_id)) if result: return True return False except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def insert_otp(self, mobile, otp, created_at): try: self.query_results.insertion(True, statement=sql_query_config.otp_manager_insert_query, params=(mobile, otp, created_at)) return True except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def check_user_id_by_mobile(self, mobile): try: logging.debug('querying the database...') result = self.query_results.fetch_query_results(sql_query_config.users_query, column_name=('mobile_number',), params=(str(mobile),)) if result: return result[0] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_user_name_by_id(self, user_id): try: result = self.query_results.fetch_query_results(sql_query_config.users_query, column_name=('id',), params=(user_id,)) if result: return result[0]['name'] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def validate_otp(self, mobile): try: logging.debug('querying the database...') result = self.query_results.fetch_query_results(sql_query_config.otp_manager_query, column_name=('mobile_number', 'deleted_at'), params=(mobile,)) if result: return result[0]['otp'] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def cooldown_time(self, mobile): try: logging.debug('querying the database...') result = self.query_results.fetch_query_results(sql_query_config.otp_manager_query_for_cooldown, column_name=('mobile_number', 'deleted_at'), params=(mobile,)) if result: return result[0]['created_at'] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def update_otp(self, mobile, time): try: self.query_results.updation(statement=sql_query_config.otp_manager_update_query, params=(time, time, mobile,)) return True except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def check_document(self, cif, file_type): try: result = self.query_results.fetch_query_results(sql_query_config.consumer_application_files_query, column_name=('consumer_application_id', 'file_type'), params=(cif, file_type)) if result: return True return False except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_document_by_file_type(self, app_id, keyword): try: status = self.query_results.fetch_query_results(sql_query_config.consumer_files_acct_aggregator, column_name=(), params=(app_id, keyword)) if status: return True return False except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def check_solar_files(self, app_id): try: status = self.query_results.fetch_query_results(sql_query_config.solar_consumer_files, column_name=('consumer_application_id', 'file_type'), params=(app_id,)) if status: return True return False except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def insert_document(self, cif, password, file_type, file_path, bank_name, account_type, user_id, owner=0): try: logging.debug('inserting operations started...') applicant_type = 1 self.query_results.insertion(statement=sql_query_config.consumer_application_files_insert_query, params=(cif, password, file_type, file_path, datetime.now().strftime("%Y-%m-%d, %H:%M:%S"), datetime.now().strftime("%Y-%m-%d, %H:%M:%S"), applicant_type, bank_name, account_type, owner, user_id)) return True except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def update_pan_dob(self, app_id, pan, dob): try: self.query_results.updation(statement=sql_query_config.update_consumer_application, params=(pan, dob, app_id)) return True except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_application_details(self, app_id): try: logging.debug('querying the database...') result = self.query_results.fetch_query_results(sql_query_config.consumer_application, column_name=('id',), params=(app_id,)) if result: return result[0] return None except Exception as ex: logging.exception("Error", ex) def get_kyc_details(self, app_id): try: logging.debug('querying the database...') result = self.query_results.fetch_query_results(sql_query_config.consumer_kyc_details, column_name=('ca.id',), params=(app_id,)) if result: return result[0] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_merchant_id(self, app_id): try: result = self.query_results.fetch_query_results(sql_query_config.consumer_application, column_name=('id',), params=(app_id,)) if result: return result[0]['merchant_id'] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_emi_calculation_details(self, app_id): try: result = self.query_results.fetch_query_results(sql_query_config.emi_calculation, column_name=('application_id',), params=(app_id,)) if result: return result[0] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def insert_cancellation_details(self, app_id, merchant_id, cancellation_interest_charges, cancellation_fixed_charges, total_amount_adjusted, total_amount_before_adjusted, adjusted_by, created_at, adjusted_to): try: logging.debug('inserting operations started...') application_id_exists = self.query_results.fetch_query_results(sql_query_config.cancellation_request, column_name=('application_id',), params=(app_id,)) if not application_id_exists: self.query_results.insertion(statement=sql_query_config.insert_cancellation_request, params=(app_id, merchant_id, cancellation_interest_charges, cancellation_fixed_charges, total_amount_adjusted, total_amount_before_adjusted, adjusted_by, created_at, adjusted_to)) return True except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_merchants(self, category_id): try: if category_id: query = sql_query_config.display_solar_merchants query_param = ('active', 0, None, 1, int(category_id)) else: query_param = ('active', 0, None, 1,) query = sql_query_config.display_merchants result = self.query_results.fetch_query_results(query, column_name=('status', 'pre_status', 'deleted_at', 'is_guest_active'), params=query_param) if result: return result return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_consumer_application_id(self, user_id): try: result = self.query_results.fetch_query_results(sql_query_config.consumer_application, column_name=('user_id',), params=(user_id,)) if result: return result[0] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_consumer_application_id_by_applicant_id(self, user_id): try: result = self.query_results.fetch_query_results(sql_query_config.consumer_application, column_name=('applicant_id',), params=(user_id,)) if result: return result[0] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_consumer_element_details(self, app_id): try: result = self.query_results.fetch_query_results(sql_query_config.consumer_application_element, column_name=('application_id',), params=(app_id,)) if result: return result[0] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_vendor_api_credentials(self, key, ): result = self.query_results.fetch_query_results(sql_query_config.vendor_api_credentials_query, column_name=('key',), params=(key,)) if result: return True, result return False, None def get_vendor_api_credentials_id(self, vendor_id): result = self.query_results.fetch_query_results(sql_query_config.vendor_api_credentials_query, column_name=('vendor_id',), params=(vendor_id,)) if result: return True, result[0] return False, None def insert_digio_vkyc(self, app_id, kid, url, signer_id, app_type): created_at = datetime.now() self.query_results.insertion(statement=sql_query_config.insert_digio_vkyc, params=(app_id, kid, url, 'incomplete', signer_id, created_at, app_type)) def check_digio_vkyc_exists(self, id, app_type): result = self.query_results.fetch_query_results(sql_query_config.check_digio_vkyc, column_name=( 'application_id', 'applicant_type', config.KYC_REQUEST_EXPIRY), params=(id, app_type)) if result: return True, result[0] return False, None def get_state(self, code): if code: result = self.query_results.fetch_query_results(sql_query_config.state, column_name=('code',), params=(code,)) return result[0].get('name') def get_pincode_details(self, pincode): """ Resolve a pincode to {districtname, statename} from previously captured addresses, falling back current -> permanent -> installation address. Returns the matching row dict or None. """ for statement in (sql_query_config.pincode_current_address, sql_query_config.pincode_permanent_address, sql_query_config.pincode_installation_address): result = self.query_results.fetch_query_results(statement, column_name=(), params=(pincode,)) if result and result[0].get('districtname') and result[0].get('statename'): return result[0] return None def insert_kyc_details(self, app_id, kid, app_type): created_at = datetime.now() self.query_results.insertion(statement=sql_query_config.insert_kyc_details, params=(app_id, kid, None, app_type, created_at)) def check_duplicate(self, mobile_number=None, email=None, pan_card=None, app_id=None, days=90): if mobile_number == '9898989898' or email == 'test1234@creditfair.in': return True curr_time = datetime.now() if days: dedupe_period = curr_time - timedelta(days=days) else: dedupe_period = '1970-01-01 00:00:00' status = None encrypt_mobile, encrypt_email, encrypt_pan = None, None, None if mobile_number: encrypt_mobile = encrypt(str(mobile_number)) if email: encrypt_email = encrypt(str(email)) if pan_card: encrypt_pan = encrypt(str(pan_card)) if encrypt_mobile: status = self.query_results.fetch_query_results(sql_query_config.dedupe_solar_consumer_application, column_name=('mobile_number', 'created_at',), params=(encrypt_mobile, dedupe_period)) if encrypt_email: status = self.query_results.fetch_query_results(sql_query_config.dedupe_solar_consumer_application, column_name=('email', 'created_at',), params=(encrypt_email, dedupe_period)) if encrypt_pan: status = self.query_results.fetch_query_results(sql_query_config.dedupe_solar_consumer_application, column_name=('pan_card', 'created_at',), params=(encrypt_pan, dedupe_period)) if app_id and status: old_application_id = status[0].get('id') if app_id == old_application_id: return True if status: return False return True def insert_ckyc(self, data, center_id, unique_id, updated_by): try: date_time = datetime.now().strftime("%Y-%m-%d, %H:%M:%S") status = "incomplete form" # updated_by = "Partner Link" lender_id = 20 logging.debug('inserting operations started...') encrypt_mobile = encrypt(data["data"]["mobile_number"]) encrypt_pan = encrypt(data["data"]["pan_number"]) if data["status"] == "OK" and data["kycStatus"] == "SUCCESS": permanent_state = self.get_state(data.get('data').get('perm_address_state')) current_state = self.get_state(data.get('data').get('current_address_state')) # encrypt_email = encrypt(data.get('data').get('email')) if data.get('data').get('email') else '' application_id = self.query_results.insertion(False, statement=sql_query_config.insert_consumer_application_ckyc, params=( data["data"]["full_name"], data["data"]["merchant_id"], data["data"]["category_id"], encrypt_mobile, data["data"]["gender"].capitalize(), data["data"]["current_address_line_1"], data["data"]["current_address_city"], data["data"]["current_pincode"], data["data"]["perm_address_line_1"], data["data"]["perm_address_city"], data["data"]["pincode"], encrypt_pan, data["data"]["dob"], date_time, status, lender_id, date_time, updated_by, permanent_state, current_state) ) else: application_id = self.query_results.insertion(False, statement=sql_query_config.insert_consumer_application_default_ckyc, params=(encrypt_mobile, data["data"]["merchant_id"], data["data"]["category_id"], encrypt_pan, data["data"]["dob"], date_time, status, lender_id, date_time, updated_by)) self.query_results.insertion(False, statement=sql_query_config.insert_consumer_application_element, params=( date_time, None, center_id, application_id, 'ENGLISH', None, 2, unique_id)) self.query_results.insertion(True, statement=sql_query_config.insert_status_log, params=(application_id, 107, status, date_time)) return True, application_id except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def consumer_name(self, application_id): result = self.query_results.fetch_query_results(sql_query_config.consumer_application, column_name=("id",), params=(application_id,)) return result[0]["name"], result[0]["mobile_number"], result[0]["email"], result[0]["merchant_id"] def check_company_present(self, company_name): result = self.query_results.fetch_query_results(sql_query_config.company_id, column_name=("company",), params=(company_name,)) if result: return result[0]["id"] else: return False def insert_company(self, company_name): created_at = datetime.now().strftime("%Y-%m-%d, %H:%M:%S") company_id = self.query_results.insertion(statement=sql_query_config.insert_company, params=("CAT D", company_name, created_at)) return company_id def update_employment_details(self, application_id, mobile, employement_type, net_monthyl_income, company_id, gst_number): updated_at = datetime.now().strftime("%Y-%m-%d, %H:%M:%S") self.query_results.updation(statement=sql_query_config.update_employee_details, params=( employement_type, net_monthyl_income, company_id, updated_at, application_id)) self.query_results.updation(statement=sql_query_config.update_employment_gst_company_no, params=(mobile, gst_number, updated_at, application_id)) def add_solar_application(self, unique_id, name, email, mobile_number, full_address, address_line_3, pincode, app_id, state, city, center_id): created_at = datetime.now() encrypt_email = encrypt(email) dedupe_status = self.check_duplicate(email=email) if dedupe_status: user_id = self.query_results.insertion(False, statement=sql_query_config.insert_user, params=(name, email, mobile_number, created_at, None, None, None, '1')) applicant_id = self.query_results.insertion(False, statement=sql_query_config.insert_customer_query, params=(name, encrypt_email, None, created_at, None)) self.query_results.updation(statement=sql_query_config.update_consumer_application_by_id, params=(full_address, user_id, pincode, name, encrypt_email, applicant_id, state.capitalize(), city.capitalize(), center_id, app_id)) self.query_results.insertion(True, statement=sql_query_config.insert_status_log, params=(app_id, user_id, 'form completed', created_at)) return True, app_id return False, app_id def save_product_details(self, app_id, project, loan_amount, kw_installation, inverter_to_be_installed, panel_to_be_installed, subsidy,project_sub_category=None, solar_power_type= None): self.query_results.updation(statement=sql_query_config.consumer_application_element_update, params=(project, kw_installation, panel_to_be_installed, inverter_to_be_installed, subsidy, eval(kw_installation) * 1000, loan_amount,project_sub_category,solar_power_type, app_id)) self.query_results.updation(statement=sql_query_config.consumer_application_product_update, params=(loan_amount, loan_amount, app_id)) return True def get_product_name(self, app_id): application_details = self.get_consumer_element_details(app_id) if application_details.get('product_name').isdigit(): product = self.get_product_details(application_details.get('product_name')) if len(product) > 1: product = product[0] else: product = application_details.get('product_name') return product def update_application_state(self, mobile, state, application_id): updated_at = datetime.now().strftime("%Y-%m-%d, %H:%M:%S") self.query_results.updation(statement=sql_query_config.update_application_state, params=(state, updated_at, application_id)) if mobile: user_id = self.check_user_id_by_mobile(mobile) user_id = user_id.get("id") else: application = self.get_application_details(application_id) user_id = application.get('user_id') self.query_results.insertion(True, statement=sql_query_config.insert_status_log, params=(application_id, user_id, state, updated_at)) def save_offer(self, app_id, offer_id): try: self.query_results.fetch_query_results(sql_query_config.update_application, column_name=("offer_id", "id",), params=(offer_id, app_id,)) self.save_initial_applied_tenure(offer_id, app_id) return True except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def save_initial_applied_tenure(self, offer_id, app_id): try: check_entry = self.query_results.fetch_query_results(sql_query_config.get_consumer_application_additional, column_name=("application_id",), params=(app_id,)) if check_entry: self.query_results.fetch_query_results(sql_query_config.update_initial_applied_tenure, column_name=("initial_applied_tenure", "id",), params=(offer_id, app_id,)) else: self.query_results.insertion(True, statement=sql_query_config.insert_into_consumer_application_additional, params=(offer_id, app_id, datetime.now().strftime("%Y-%m-%d, %H:%M:%S"))) except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_merchant_name(self, merchant_slug): try: result = self.query_results.fetch_query_results(sql_query_config.get_merchant_name, column_name=("slug",), params=(merchant_slug,)) return result except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_merchant_center_name(self, merchant_center_slug): try: result = self.query_results.fetch_query_results(sql_query_config.get_merchant_center_name, column_name=("slug",), params=(merchant_center_slug,)) return result except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def insert_to_queue(self, param, app_id, job): try: # check_exists = self.query_results.fetch_query_results( # sql_query_config.no_touch_queue_log.format(f"'%{job}%'", app_id), # column_name=(), params=()) # if not check_exists: result = self.query_results.insertion(True, statement=sql_query_config.insert_into_queue, params=( datetime.now().strftime("%Y-%m-%d, %H:%M:%S"), str(param),)) except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_merchant_id_by_app_id(self, application_id): try: result = self.query_results.fetch_query_results( sql_query_config.get_merchant_id_by_app_id, column_name=(), params=(application_id,)) return result[0]['merchant_id'] if result else None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_webhook_by_merchant_id(self, merchant_id): try: result = self.query_results.fetch_query_results( sql_query_config.get_webhook_by_merchant_id, column_name=(), params=(merchant_id,)) return result[0] if result else None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_loan_application_no(self, application_id): try: result = self.query_results.fetch_query_results( sql_query_config.get_loan_application_no, column_name=(), params=(application_id,)) return result[0]['loan_application_no'] if result else None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def update_monthly_bill(self, monthly_bill, app_id): try: self.query_results.updation(statement=sql_query_config.update_monthly_bill, params=(monthly_bill, app_id)) except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_approval_status_log(self, app_id): try: result_set = self.query_results.fetch_query_results(sql_query_config.approval_status_log, column_name=(), params=(app_id,)) return result_set except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_payment_log(self, app_id): try: result_set = self.query_results.fetch_query_results(sql_query_config.get_payment_log, column_name=(), params=(app_id,)) return True if result_set else False except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_payment_details(self, app_id, offer_id): try: result_set = self.query_results.fetch_query_results(sql_query_config.get_payment_details, column_name=(), params=(app_id, offer_id)) product_name = self.get_product_name(app_id) if result_set: result_set = result_set[0] details = {"product": product_name, "tenure": result_set.get("tenure"), "emi": result_set.get("emi"), "interest_rate": result_set.get("interest_rate"), "total": result_set.get("payable"), "processing_fee": result_set.get("processing_fee"), "up_front_from_customer": result_set.get("up_front_from_customer")} return details except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def update_provider_name(self, provider, other_provider, app_id): try: self.query_results.updation(statement=sql_query_config.update_provider_name, params=(provider, other_provider, app_id)) except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def create_loan_pan_dob(self, mobile_number, pan_number, dob, unique_id, loan_amount, merchant_id, center_id, product, source): try: created_at = datetime.now().strftime("%Y-%m-%d, %H:%M:%S") status = 'form completed' encrypt_mobile = encrypt(mobile_number) encrypt_pan = encrypt(pan_number) lender_id = 20 category_id = self.query_results.fetch_query_results(sql_query_config.merchant_category_join, column_name=('id',), params=(merchant_id,)) user_id = self.query_results.insertion(False, statement=sql_query_config.users_table_insert, params=(mobile_number, dob, created_at, merchant_id, center_id, '1')) applicant_id = self.query_results.insertion(False, statement=sql_query_config.customer_table_insert, params=(encrypt_mobile, encrypt_pan, dob, created_at,)) application_id = self.query_results.insertion(False, statement=sql_query_config.consumer_application_table_insert, params=(merchant_id, encrypt_mobile, encrypt_pan, dob, loan_amount, user_id, applicant_id, created_at, status, lender_id, loan_amount, source, created_at, category_id[0].get('id'),)) self.query_results.insertion(False, statement=sql_query_config.insert_consumer_application_element1, params=(application_id, center_id, product, created_at, 2, 'ENGLISH', unique_id)) self.query_results.insertion(True, statement=sql_query_config.insert_status_log, params=(application_id, 107, status, created_at)) return True, application_id, user_id except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def dedupe_update_application(self, request_body): try: dedupe_status = self.check_duplicate(email=request_body.get('email'), app_id=request_body.get('application_id')) gender_maping = {"MALE": 1, "FEMALE": 2} if dedupe_status: created_at = datetime.now() encrypt_email = encrypt(request_body.get('email')) address_lin_2 = "" if request_body.get("address_line_2") is None else request_body.get("address_line_2") full_address = request_body.get("address_line_1") + " " + address_lin_2 if request_body.get("middle_name") is None: name = request_body.get("first_name") + " " + request_body.get("last_name") else: name = (request_body.get("first_name") + " " + request_body.get("middle_name") + " " + request_body.get("last_name")) application_details = self.get_application_details(request_body.get("application_id")) self.query_results.updation(statement=sql_query_config.update_user, params=(name, request_body.get('email'), request_body.get("gender"), application_details.get("user_id"))) self.query_results.updation(statement=sql_query_config.update_customers, params=(name, encrypt_email, gender_maping[request_body.get("gender")], application_details.get("applicant_id"))) self.query_results.updation(statement=sql_query_config.update_consumer_element, params=(request_body.get("father_name"),request_body.get("type_of_customer"),request_body.get("type_of_entity"), request_body.get("application_id"))) self.query_results.updation(statement=sql_query_config.no_touch_update_consumer_application_by_id, params=(full_address, application_details.get("user_id"), request_body.get("pincode"), name, encrypt_email, application_details.get("applicant_id"), request_body.get("state").capitalize(), request_body.get("city").capitalize(), application_details.get("center_id"), request_body.get("address_status"), request_body.get('gender'), request_body.get("application_id"))) return True, "" return False, "Email already exist" except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def fetch_status_log(self, app_id, state): try: result = self.query_results.fetch_query_results(statement=sql_query_config.fetch_status_log, column_name=('application_id', 'status'), params=(app_id, state)) return result except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def update_ckyc_application_id(self, application_id, random_id): try: status = self.query_results.updation(statement=sql_query_config.update_ckyc_application_id, params=(application_id, random_id)) return status except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_merchant_category(self, merchant_id): try: result = self.query_results.fetch_query_results(sql_query_config.display_merchant_category, column_name=('id',), params=(merchant_id,)) if result: return result[0]["category_id"] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_merchant_from_mobile(self, phone): try: result = self.query_results.fetch_query_results(sql_query_config.get_merchant_from_mobile, column_name=(), params=(phone,)) if result: return result[0] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_merchant_by_sales_person_number(self, mobile): try: filter_value = f'%{str(mobile)}%' result = self.query_results.fetch_query_results(sql_query_config.get_merchant_by_rcp_mobile, column_name=(), params=(filter_value,)) if result: return result[0] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def check_file_exist(self, file_name, application_id): try: result = self.query_results.fetch_query_results(sql_query_config.check_document_exist, column_name=(), params=(application_id, file_name,)) if result: return True return False except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def store_installation_address(self, request_body): try: created_at = datetime.now() address_lin_2 = "" if request_body.get("address_line_2") is None else request_body.get("address_line_2") full_address = request_body.get("address_line_1") + " " + address_lin_2 self.query_results.updation(statement=sql_query_config.store_installation_address, params=(full_address, request_body.get("pincode"), request_body.get("state").capitalize(), request_body.get("city").capitalize(), request_body.get("address_status"), created_at, request_body.get("application_id"))) return True, "" except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def update_consumer_files(self, app_id, owner, updated_at, file_type): try: self.query_results.updation(statement=sql_query_config.update_consumer_files, params=(updated_at, owner, app_id, file_type,)) return True except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def fetch_credit_report(self, app_id): try: result = self.query_results.fetch_query_results(sql_query_config.fetch_credit_report, column_name=(), params=(app_id,)) file_result = self.query_results.fetch_query_results(sql_query_config.consumer_application_files_query, column_name=('consumer_application_id', 'file_type'), params=(app_id, 'equifax_report')) if result or file_result: return True return False except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def insert_pirimid_bank_analysis(self, app_id, tracking_id, request_body, response_body, applicant_type): try: timestamp = datetime.now().strftime("%Y-%m-%d, %H:%M:%S") self.query_results.insertion(statement=sql_query_config.pirimid_bank_analysis, params=(app_id, tracking_id, request_body, response_body, 107, timestamp, timestamp, applicant_type)) return True except Exception as ex: return False def get_consent_documents_details(self, merchant_id): try: result = self.query_results.fetch_query_results(sql_query_config.consent_document_query, column_name=('id',), params=(merchant_id,)) if result: return True, result[0] return False, None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def save_consent(self, application_id, latitude, longitude, status, user_ip): try: current_time = datetime.now().strftime("%Y-%m-%d, %H:%M:%S") self.query_results.insertion(statement=sql_query_config.insert_consent_log, params=(application_id, latitude, longitude, status, user_ip, current_time)) self.query_results.insertion(statement=sql_query_config.update_consent_columns, params=(application_id,)) return True except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_merchant_offer(self, offer_id): try: result = self.query_results.fetch_query_results(sql_query_config.get_merchant_offer, column_name=(), params=(offer_id,)) if result: return result[0] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_merchant_for_application(self, app_id): try: result = self.query_results.fetch_query_results(sql_query_config.get_merchant_for_application, column_name=(), params=(app_id,)) if result: return result[0] return None except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def is_agreement_signed(self, app_id): try: result = self.query_results.fetch_query_results(sql_query_config.check_agreement_signed, column_name=(), params=(app_id,)) return bool(result) except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def upsert_emi_calculation(self, app_id, offer_id, tenure, emi, interest_rate, remaining_emi, advance_emi, subvention, processing_fee, upfront_from_customer, emi_date, received_by_merchant, upfront_from_credit_fair, payable_by_customer, annualised_irr, payment_frequency, disbursement_method, subvention_cgst, subvention_sgst, subvention_igst, net_loan_amount, now): try: self.query_results.insertion( True, statement=sql_query_config.upsert_emi_calculation, params=( app_id, offer_id, tenure, emi, interest_rate, remaining_emi, advance_emi, subvention, processing_fee, upfront_from_customer, emi_date, received_by_merchant, upfront_from_credit_fair, payable_by_customer, annualised_irr, payment_frequency, disbursement_method, subvention_cgst, subvention_sgst, subvention_igst, net_loan_amount, now, now, # ON DUPLICATE KEY UPDATE values offer_id, tenure, emi, interest_rate, remaining_emi, advance_emi, subvention, processing_fee, upfront_from_customer, emi_date, received_by_merchant, upfront_from_credit_fair, payable_by_customer, annualised_irr, payment_frequency, disbursement_method, subvention_cgst, subvention_sgst, subvention_igst, net_loan_amount, now )) return True except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def insert_emi_calculation_lock(self, existing_emi, re_scheduled_by, approved_amount, now): try: self.query_results.insertion( True, statement=sql_query_config.insert_emi_calculation_lock, params=( existing_emi.get('application_id'), existing_emi.get('offer_id'), existing_emi.get('tenure'), existing_emi.get('emi'), existing_emi.get('interest_rate'), existing_emi.get('remaining_emi'), existing_emi.get('advance_emi'), existing_emi.get('subvention'), existing_emi.get('processing_fee'), existing_emi.get('upfront_from_customer'), existing_emi.get('emi_date'), existing_emi.get('received_by_merchant'), existing_emi.get('upfront_from_credit_fair'), existing_emi.get('payable_by_customer'), existing_emi.get('annualised_irr'), existing_emi.get('payment_frequency'), existing_emi.get('subvention_cgst', 0), existing_emi.get('subvention_sgst', 0), existing_emi.get('subvention_igst', 0), now, now, re_scheduled_by, approved_amount )) return True except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def update_application_offer_tenure(self, app_id, offer_id, tenure): try: now = datetime.now().strftime("%Y-%m-%d %H:%M:%S") self.query_results.updation(statement=sql_query_config.update_application_offer_tenure, params=(offer_id, tenure, now, app_id)) return True except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex) def get_pirimid_analysis_count(self, application_id): try: result = self.query_results.fetch_query_results(sql_query_config.count_pirimid_analysis, column_name=(), params=(application_id,)) if result: return result[0]['count'] return 0 except Exception as ex: return 0 def insert_comment(self, application_id, user_id, comment, show_consumer, show_merchant, created_at, updated_at, type): try: self.query_results.insertion(statement=sql_query_config.insert_comment, params=(application_id, user_id, comment, show_consumer, show_merchant, created_at, updated_at, type)) return True except Exception as ex: logging.exception("An error occurred while inserting comment") return False def insert_communication_logs(self, data): try: self.query_results.insertion(statement=sql_query_config.insert_communication_logs, params=data) return True except Exception as e: logging.exception("DB Insert Error:", e) return False def create_consent_document_job(self, application_id, latitude, longitude, file_name, repository): try: result = self.query_results.insertion(statement=sql_query_config.insert_consent_document_job, params=('mconnectConsent', '{"application_id": "' + str(application_id) + '", "applicant_type": "1"}', application_id, datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) return result except Exception as ex: raise CustomExceptions('CF_EXCEPT_01', ex)