def main(json_params = None):
user_id = json_params['user_id']
client_id = json_params['client_id']
loan_amount = json_params['loan_amount']
loan_period = json_params['loan_period']
payment_frequency = json_params['payment_frequency']
payment_amount = json_params['payment_amount']
app_rate = json_params['app_rate']
vki_check = 0
mysql_settings = mysql_con.mysql_con()
db_connection = sql.connect(user=mysql_settings['user'], password=mysql_settings['password'],
host=mysql_settings['host'],
database=mysql_settings['database'])
db_cursor = db_connection.cursor()
sql_script = (' ').join(['select amount',
'from cc_contracts',
'where client_id = {0}'.format(client_id),
"and id = (select max(id) from cc_contracts where client_id = 1)"
])
db_cursor.execute(sql_script)
table_rows = db_cursor.fetchall()
df = pd.DataFrame(table_rows, columns=db_cursor.column_names)
db_connection.close()
if df.shape[0] > 0:
vki_check = df['amount'][0]
else:
vki_check = 0
return float(vki_check)
def main(response=None):
approved_rate = response['approved_rate']
approved_amount = response['approved_amount']
approved_rate_min = int(approved_rate * 365 * 100 - 15)
approved_rate_max = int(approved_rate * 365 * 100 + 15)
vki_check = 0
mysql_settings = mysql_con.mysql_con()
db_connection = sql.connect(user=mysql_settings['user'],
password=mysql_settings['password'],
host=mysql_settings['host'],
database=mysql_settings['database'])
db_cursor = db_connection.cursor()
sql_script = (' ').join([
'select min(id) as product_id', 'from cc_products', 'where active = 1',
'and max_credit_limit >= {0}'.format(approved_amount),
'and min_credit_limit <= {0}'.format(approved_amount),
'and interest_rate >= {0}'.format(approved_rate_min),
'and interest_rate <= {0}'.format(approved_rate_max)
])
db_cursor.execute(sql_script)
table_rows = db_cursor.fetchall()
df = pd.DataFrame(table_rows, columns=db_cursor.column_names)
db_connection.close()
if df.shape[0] > 0:
vki_check = df['product_id'][0]
else:
vki_check = 0
return vki_check
def main(json_params = None):
user_id = json_params['user_id']
client_id = json_params['client_id']
loan_amount = json_params['loan_amount']
loan_period = json_params['loan_period']
payment_frequency = json_params['payment_frequency']
payment_amount = json_params['payment_amount']
app_rate = json_params['app_rate']
vki_check = 0
mysql_settings = mysql_con.mysql_con()
db_connection = sql.connect(user=mysql_settings['user'], password=mysql_settings['password'],
host=mysql_settings['host'],
database=mysql_settings['database'])
db_cursor = db_connection.cursor()
sql_script = (' ').join(['select case when count(found_rows()) > 0 then 1 else 0 end as rule_check',
'from cc_contracts',
'where client_id = {0} and DATEDIFF(repayment_fact_date, repayment_plan_date) <= 3 and '.format(client_id),
"DATEDIFF(repayment_fact_date, repayment_plan_date) > 0 and datediff(now(),repayment_fact_date) <= 60"
])
db_cursor.execute(sql_script)
table_rows = db_cursor.fetchall()
df = pd.DataFrame(table_rows, columns=db_cursor.column_names)
db_connection.close()
vki_check = df['rule_check'][0]
return vki_check
def main(json_params = None):
user_id = json_params['user_id']
client_id = json_params['client_id']
loan_amount = json_params['loan_amount']
loan_period = json_params['loan_period']
payment_frequency = json_params['payment_frequency']
payment_amount = json_params['payment_amount']
app_rate = json_params['app_rate']
application_id = json_params['application_id']
vki_check = 0
mysql_settings = mysql_con.mysql_con()
db_connection = sql.connect(user=mysql_settings['user'], password=mysql_settings['password'],
host=mysql_settings['host'],
database=mysql_settings['database'])
db_cursor = db_connection.cursor()
sql_script = (' ').join(['select product_id',
'from cc_applications',
'where id = {0}'.format(application_id)
])
db_cursor.execute(sql_script)
table_rows = db_cursor.fetchall()
df = pd.DataFrame(table_rows, columns=db_cursor.column_names)
db_connection.close()
vki_check = df['product_id'][0]
return vki_check
def main(json_params=None):
user_id = json_params['user_id']
client_id = json_params['client_id']
loan_amount = json_params['loan_amount']
loan_period = json_params['loan_period']
payment_frequency = json_params['payment_frequency']
payment_amount = json_params['payment_amount']
app_rate = json_params['app_rate']
vki_check = 0
mysql_settings = mysql_con.mysql_con()
db_connection = sql.connect(user=mysql_settings['user'],
password=mysql_settings['password'],
host=mysql_settings['host'],
database=mysql_settings['database'])
db_cursor = db_connection.cursor()
sql_script = (' ').join([
'select case when count(found_rows()) > 1 then 1 else 0 end as rule_check',
'from cc_applications', 'where client_id = {0}'.format(client_id),
"and DATEDIFF(created_at, now()) > -7", "and status = 'REJECTED'"
])
db_cursor.execute(sql_script)
table_rows = db_cursor.fetchall()
df = pd.DataFrame(table_rows, columns=db_cursor.column_names)
db_connection.close()
vki_check = df['rule_check'][0]
return vki_check
def main(response=None):
status = 200
mysql_settings = mysql_con.mysql_con()
db_connection = sql.connect(user=mysql_settings['user'],
password=mysql_settings['password'],
host=mysql_settings['host'],
database=mysql_settings['database'])
db_cursor = db_connection.cursor()
mysql_scoring_result = (make_value(response,
key1='application_id',
key2=None,
key3=None,
key4=None,
value_type='int'),
make_value(response,
key1='approved_amount',
key2=None,
key3=None,
key4=None,
value_type='int'),
make_value(response,
key1='approved_frequency',
key2=None,
key3=None,
key4=None,
value_type='int'),
make_value(response,
key1='approved_period',
key2=None,
key3=None,
key4=None,
value_type='int'),
make_value(response,
key1='approved_product_id',
key2=None,
key3=None,
key4=None,
value_type='int'),
make_value(response,
key1='approved_rate',
key2=None,
key3=None,
key4=None,
value_type='int'),
make_value(response,
key1='client_id',
key2=None,
key3=None,
key4=None,
value_type='int'),
make_value(response,
key1='cnt_prev',
key2=None,
key3=None,
key4=None,
value_type='int'),
make_value(response,
key1='code',
key2=None,
key3=None,
key4=None,
value_type='int'),
make_value(response,
key1='reason',
key2=None,
key3=None,
key4=None,
value_type='str'),
make_value(response,
key1='result',
key2=None,
key3=None,
key4=None,
value_type='str'),
make_value(response,
key1='status',
key2=None,
key3=None,
key4=None,
value_type='str'),
make_value(response,
key1='user_id',
key2=None,
key3=None,
key4=None,
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='app_data',
key3='loan_amount',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='app_data',
key3='loan_period',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='app_data',
key3='payment_frequency',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='app_data',
key3='payment_amount',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='app_data',
key3='app_rate',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='app_data',
key3='product_id',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='vki_check',
key3='bad_amount',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='vki_check',
key3='bad_rate',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='vki_check',
key3='last_amount',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='vki_check',
key3='vki_01',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='vki_check',
key3='vki_02',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='vki_check',
key3='vki_03',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='vki_check',
key3='vki_04',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='vki_check',
key3='vki_05',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='vki_check',
key3='vki_06',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='vki_check',
key3='vki_07',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='vki_check',
key3='vki_08',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='nbki_pdl_score_check',
key3='score',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='nbki_pdl_score_check',
key3='exclusionCode',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='nbki_pdl_score_check',
key3='grey',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='nbki_pdl_score_check',
key3='pd',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='nbki_pdl_score_check',
key3='pd_check_error',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='fin_model_limits',
key3='fin_amount',
value_type='int'),
make_value(response,
key4=None,
key1='data',
key2='fin_model_limits',
key3='fin_rate',
value_type='int'))
sql_script = (' ').join([
'insert into scoring_main (application_id , approved_amount , approved_frequency , approved_period , approved_product_id , approved_rate,client_id,cnt_prev,code,reason,`result`,status,user_id,app_data_loan_amount,app_data_loan_period,app_data_payment_frequency,app_data_payment_amount,app_data_app_rate,app_data_product_id,vki_check_bad_amount,vki_check_bad_rate,vki_check_last_amount,vki_check_vki_01,vki_check_vki_02,vki_check_vki_03,vki_check_vki_04,vki_check_vki_05,vki_check_vki_06,vki_check_vki_07,vki_check_vki_08,nbki_score,nbki_exclusionCode,nbki_grey,nbki_pd,nbki_pd_check_error,model_fin_amount,model_fin_rate )',
"VALUES {0}".format(mysql_scoring_result)
])
print(sql_script)
try:
db_cursor.execute(sql_script)
db_connection.commit()
except Exception:
print('FIN_DB_INSERT', traceback.format_exc())
db_connection.rollback()
db_connection.close()
print('sql done!')
return status