def _get_account_summary_db(lower_min, lower_max, conn):
status = 0
jsString = None
try:
sql = """
SELECT
`id`,`account_code`,`account_name`,`balance`,CONCAT("'",`date_created`,"'") AS date_created
FROM
`tbl_account`
LIMIT %i, %i
""" % (int(lower_min), int(lower_max))
params = ()
recordset = db.retrieve_all_data_params(conn, sql, params)
jsonArray = ast.literal_eval(json.dumps(recordset))
jsonArraySize = len(jsonArray)
jsString = '{"Result":"OK","Records":' + str(json.dumps(
recordset)) + ',"TotalRecordCount":' + str(jsonArraySize) + '}'
except Exception, e:
logger.error(e)
raise
def _mark_loan_request_processed_db(_id, msisdn, ref_no, conn):
processZ = 0
processO = 1
taskFlag = 2
jsString = None
idateNow = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
try:
sql = """
UPDATE
`tbl_loan_request`
SET
`is_processed` = %s,
`date_modified` = '%s',
`task_flag` = %s
WHERE
`is_processed` = %s AND `id` = %s AND `msisdn` = '%s' AND `reference_no` = '%s'
""" % (processO, idateNow, taskFlag, processZ, _id, msisdn, ref_no)
params = ()
db.execute_query(conn, sql, params)
jsString = {"Result": "OK"}
conn.commit()
except Exception, e:
logger.error(e)
raise
def _loan_approval_operation_db(reference_no, msisdn, approved_by, conn):
processZ = 0
taskFlag = 2
idateNow = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
jsString = None
try:
sql = """
UPDATE
`tbl_loan_request`
SET
`task_flag` = %s,
`approved_by` = '%s',
`date_modified` = '%s'
WHERE
`reference_no` = '%s' AND `msisdn` = %s AND `is_processed` = %s
""" % (taskFlag, approved_by, idateNow, reference_no, msisdn, processZ)
params = ()
#db.retrieve_all_data_params(conn, qry, params)
db.execute_query(conn, sql, params)
jsString = {"RESULT": "SUCCESS", "MESSAGE": "Approval complete."}
conn.commit()
except Exception, e:
logger.error(e)
print(e)
raise
def create_connection():
try:
connection = MySQLdb.connect(host=mysql_params['host'],\
user=mysql_params['user'], passwd=mysql_params['passwd'],\
db=mysql_params['db'], cursorclass=MySQLdb.cursors.DictCursor)
except (MySQLdb.Error, e):
logger.error(e)
raise
return connection
def _record_loan_fee_db(msisdn, reference_no, loan_fee, account_name, conn):
jsString = None
try:
sql = """CALL """ + mysql_params[
'db'] + """.`sProcLogLoanFee`(%s,%s,%s,%s)"""
params = (msisdn, reference_no, loan_fee, account_name)
output = db.retrieve_all_data_params(conn, sql, params)
for data in output:
jsString = json.loads(data.get('_JSON'))
conn.commit()
except Exception, e:
logger.error(e)
raise
def _mpesa_receipt_db(msisdn, amount, conn):
jsString = None
try:
sql = """CALL """ + mysql_params[
'db'] + """.`sProcCheckoutOperation`(%s,%s)"""
params = (
msisdn,
amount,
)
output = db.retrieve_all_data_params(conn, sql, params)
for data in output:
jsString = json.loads(data.get('_JSON'))
conn.commit()
except Exception, e:
logger.error(e)
raise
def _registration_db(msisdn, passwd, conn):
jsString = None
try:
sql = """CALL """ + mysql_params[
'db'] + """.`sProcActivateWallet`(%s,%s)"""
params = (
msisdn,
passwd,
)
output = db.retrieve_all_data_params(conn, sql, params)
for data in output:
jsString = json.loads(data.get('_JSON'))
conn.commit()
except Exception, e:
logger.error(e)
raise
def _dispatch_loan_db(reference_no, msisdn, amount, loan_amount,
repayment_amount, interest_amount, loan_duration,
notify_1, conn):
jsString = None
try:
sql = """CALL """ + mysql_params[
'db'] + """.`sProcLoanDispatch`(%s,%s,%s,%s,%s,%s,%s,%s)"""
params = (reference_no, msisdn, amount, loan_amount, repayment_amount,
interest_amount, loan_duration, notify_1)
output = db.retrieve_all_data_params(conn, sql, params)
for data in output:
jsString = json.loads(data.get('_JSON'))
conn.commit()
except Exception, e:
logger.error(e)
raise
def _get_debtor_list_db(search, lower_min, lower_max, conn):
status = 0
jsString = None
try:
if (search == '0'):
sql = """
SELECT
`id`,`reference_no`,`msisdn`,`amount_requested`,`amount_disbursed`,`repayment_amount`,CONCAT("'",`repayment_date`,"'") AS repayment_date,CONCAT("'",`date_created`,"'") AS date_created
FROM
`tbl_debtor`
WHERE
`is_archived` = %s
ORDER BY
`date_created` DESC
LIMIT %i, %i
""" % (status, int(lower_min), int(lower_max))
else:
sql = """
SELECT
`id`,`reference_no`,`msisdn`,`amount_requested`,`amount_disbursed`,`repayment_amount`,CONCAT("'",`repayment_date`,"'") AS repayment_date,CONCAT("'",`date_created`,"'") AS date_created
FROM
`tbl_debtor`
WHERE
`is_archived` = %s AND `msisdn` = '%s'
ORDER BY
`date_created` DESC
LIMIT %i, %i
""" % (status, search, int(lower_min), int(lower_max))
params = ()
recordset = db.retrieve_all_data_params(conn, sql, params)
jsonArray = ast.literal_eval(json.dumps(recordset))
jsonArraySize = len(jsonArray)
jsString = '{"Result":"OK","Records":' + str(json.dumps(
recordset)) + ',"TotalRecordCount":' + str(jsonArraySize) + '}'
except Exception, e:
logger.error(e)
raise
def _queue_loan_db(reference_no, msisdn, amount, approved_by, conn):
jsString = None
try:
sql = """CALL """ + mysql_params[
'db'] + """.`sProcQueueLoan`(%s,%s,%s,%s)"""
params = (
reference_no,
msisdn,
amount,
approved_by,
)
output = db.retrieve_all_data_params(conn, sql, params)
for data in output:
jsString = json.loads(data.get('_JSON'))
conn.commit()
except Exception, e:
logger.error(e)
raise
def _loan_request_list_db(search, lower_min, lower_max, conn):
process = 0
jsString = None
try:
if (search == '0'):
sql = """
SELECT
`reference_no`,`msisdn`,`amount`,`requested_by`,IFNULL(`task_flag`,0) AS task_flag,CONCAT("'",`date_created`,"'") AS date_created,CONCAT("'",`date_modified`,"'") AS date_modified
FROM
`tbl_loan_request`
WHERE
`is_processed` = %s
ORDER BY `date_created` DESC
LIMIT %i, %i
""" % (process, int(lower_min), int(lower_max))
else:
sql = """
SELECT
`reference_no`,`msisdn`,`amount`,`requested_by`,IFNULL(`task_flag`,0) AS task_flag,CONCAT("'",`date_created`,"'") AS date_created,CONCAT("'",`date_modified`,"'") AS date_modified
FROM
`tbl_loan_request`
WHERE
`is_processed` = %s AND `msisdn` = '%s'
ORDER BY `date_created` DESC
LIMIT %i, %i
""" % (process, search, int(lower_min), int(lower_max))
params = ()
recordset = db.retrieve_all_data_params(conn, sql, params)
jsonArray = ast.literal_eval(json.dumps(recordset))
jsonArraySize = len(jsonArray)
jsString = '{"Result":"OK","Records":' + str(json.dumps(
recordset)) + ',"TotalRecordCount":' + str(jsonArraySize) + '}'
except Exception, e:
logger.error(e)
raise
def _statement_db(msisdn, conn, limit=50):
status = 0
jsString = None
try:
sql = """
SELECT
`reference_no`,`cr`,`dr`,`balance`,CONCAT("'",`date_created`,"'") AS date_created
FROM
`tbl_wallet_transaction`
WHERE
`is_archived` = %s AND `msisdn` = %s
LIMIT %s
"""
params = (status, msisdn, limit)
recordset = db.retrieve_all_data_params(conn, sql, params)
jsString = json.dumps(recordset)
except Exception, e:
logger.error(e)
raise
def _get_loan_payout_db(conn, limit=1000):
process = 0
status = 0
jsString = None
try:
sql = """
SELECT
`id`,`reference_no`,`msisdn`,`amount`
FROM
`tbl_loan_payout`
WHERE
`is_processed` = %s AND `status` = %s
LIMIT %s
"""
params = (process, status, limit)
recordset = db.retrieve_all_data_params(conn, sql, params)
jsString = json.dumps(recordset)
except Exception, e:
logger.error(e)
raise
def _get_loan_request_db(conn, limit=1000):
processZ = 0
taskFlag = 2
jsString = None
try:
sql = """
SELECT
`id`,`msisdn`,`reference_no`,`amount`,`approved_by`,CONCAT("'",`date_created`,"'") AS date_created
FROM
`tbl_loan_request`
WHERE
`is_processed` = %s AND `task_flag` = %s
LIMIT %s
"""
params = (processZ, taskFlag, limit)
recordset = db.retrieve_all_data_params(conn, sql, params)
jsString = json.dumps(recordset)
except Exception, e:
logger.error(e)
raise
def close_connection(connection):
try:
connection.close()
except (MySQLdb.Error, e):
logger.error(e)
raise
def _get_accounts_db(code, search, lower_min, lower_max, conn):
status = 0
jsString = None
try:
if (str(code) == 'GEN'):
if (search == '0'):
sql = """
SELECT
`account_code`,`reference_no`,`msisdn`,`cr`,`dr`,`balance`,`narration`,CONCAT("'",`date_created`,"'") AS date_created
FROM
`tbl_transaction`
WHERE
`is_archived` = %s AND `account_code` != '%s'
ORDER BY
`date_created` DESC
LIMIT %i, %i
""" % (status, str(code), int(lower_min), int(lower_max))
else:
sql = """
SELECT
`account_code`,`reference_no`,`msisdn`,`cr`,`dr`,`balance`,`narration`,CONCAT("'",`date_created`,"'") AS date_created
FROM
`tbl_transaction`
WHERE
`is_archived` = %s AND `account_code` != '%s' AND `msisdn` = '%s'
ORDER BY
`date_created` DESC
LIMIT %i, %i
""" % (status, str(code), search, int(lower_min), int(lower_max))
elif (str(code) == 'STMT'):
sql = """
SELECT
'SELF' AS account_code,`reference_no`,`msisdn`,`cr`,`dr`,`balance`,`narration`,CONCAT("'",`date_created`,"'") AS date_created
FROM
`tbl_wallet_transaction`
WHERE
`is_archived` = %s AND `msisdn` = '%s'
ORDER BY
`date_created` DESC
LIMIT %i, %i
""" % (status, search, int(lower_min), int(lower_max))
else:
if (search == '0'):
sql = """
SELECT
`account_code`,`reference_no`,`msisdn`,`cr`,`dr`,`balance`,`narration`,CONCAT("'",`date_created`,"'") AS date_created
FROM
`tbl_transaction`
WHERE
`is_archived` = %s AND `account_code` = '%s'
ORDER BY
`date_created` DESC
LIMIT %i, %i
""" % (status, str(code), int(lower_min), int(lower_max))
else:
sql = """
SELECT
`account_code`,`reference_no`,`msisdn`,`cr`,`dr`,`balance`,`narration`,CONCAT("'",`date_created`,"'") AS date_created
FROM
`tbl_transaction`
WHERE
`is_archived` = %s AND `account_code` = '%s' AND `msisdn` = '%s'
ORDER BY
`date_created` DESC
LIMIT %i, %i
""" % (status, str(code), search, int(lower_min), int(lower_max))
params = ()
recordset = db.retrieve_all_data_params(conn, sql, params)
jsonArray = ast.literal_eval(json.dumps(recordset))
jsonArraySize = len(jsonArray)
jsString = '{"Result":"OK","Records":' + str(json.dumps(
recordset)) + ',"TotalRecordCount":' + str(jsonArraySize) + '}'
except Exception, e:
logger.error(e)
raise