class Dashboard(object):
"""docstring for UserFunctions"""
def __init__(self, user):
super(Dashboard, self).__init__()
self.dbconn = MysqlLib()
self.user = user
def getSerialsValidationInfo(self, query):
result = self.dbconn.custom_query(query)
return result
def generateSerialsReport(self, request_params):
if request_params['filter_type'] == "daily":
data = self.dbconn.custom_query(
"SELECT days_of_week.day AS days, COALESCE(COUNT(id), 0) AS total FROM ( SELECT 0 as day UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) days_of_week LEFT JOIN tbl_activity_log c ON HOUR(c.date_created) = days_of_week.day AND DATE(c.date_created) BETWEEN '{0}' AND '{1}' AND c.user_type = '{2}' GROUP BY days"
.format(request_params['from_date'],
request_params['end_date'],
request_params['user_type']))
elif request_params['filter_type'] == "weekly":
data = self.dbconn.custom_query(
"SELECT days_of_week.day AS days, COALESCE(COUNT(id), 0) AS total FROM ( SELECT 0 as day UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 ) days_of_week LEFT JOIN tbl_activity_log c ON WEEKDAY(c.date_created) = days_of_week.day AND DATE(c.date_created) BETWEEN '{0}' AND '{1}' AND c.user_type = '{2}' GROUP BY days"
.format(request_params['from_date'],
request_params['end_date'],
request_params['user_type']))
elif request_params['filter_type'] == "monthly":
data = self.dbconn.custom_query(
"SELECT days_of_week.day AS days, COALESCE(COUNT(id), 0) AS total FROM (SELECT 1 AS day UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) days_of_week LEFT JOIN tbl_activity_log c ON DAY(c.date_created) = days_of_week.day AND DATE(c.date_created) BETWEEN '{0}' AND '{1}' AND c.user_type = '{2}' GROUP BY days"
.format(request_params['from_date'],
request_params['end_date'],
request_params['user_type']))
else:
data = []
return data
def __init__(self, user):
super(Activities, self).__init__()
self.dbconn = MysqlLib()
self.user = user
self.API = ApiCalls()
class Activities(object):
"""docstring for UserFunctions"""
def __init__(self, user):
super(Activities, self).__init__()
self.dbconn = MysqlLib()
self.user = user
self.API = ApiCalls()
def getAllActivities(self, request_params):
where_con_list = []
where_con = ''
if 'status' in request_params and request_params['status'] != "" and request_params['status'] != "All":
where_con_list.append("status='{}' ".format(request_params['status']))
if 'user_msisdn' in request_params and request_params['user_msisdn'] != "" and request_params['user_msisdn'] != "All":
where_con_list.append("user_msisdn='{}' ".format(request_params['user_msisdn']))
where_con = " and ".join(where_con_list)
if where_con == "":
where_con = "1"
if request_params['fromdate'] == "" or request_params['todate'] == "":
print("Yess Herreeee")
data = self.dbconn.select_from_table_paged("tbl_activity_log", condition=" WHERE "+ where_con +" ORDER BY date_created DESC", offset=request_params['offset'], records=request_params['records'])
data_count = self.dbconn.select_count_table("tbl_activity_log", condition="WHERE "+ where_con)
else:
data = self.dbconn.select_from_table_paged("tbl_activity_log", condition=" WHERE "+ where_con +" AND date_created between '{0}' and '{1}' ORDER BY date_created DESC".format(request_params['fromdate'], request_params['todate']), offset=request_params['offset'], records=request_params['records'])
data_count = self.dbconn.select_count_table("tbl_activity_log", condition="WHERE "+ where_con)
return [data, data_count]
def getAllBlack(self, request_params):
where_con_list = []
where_con = ''
# if 'request_type' in request_params and request_params['request_type'] != "" and request_params['request_type'] != "All":
# where_con_list.append("request_type='{}' ".format(request_params['request_type']))
# if 'branch' in request_params and request_params['branch'] != "" and request_params['branch'] != "All":
# where_con_list.append("rbranch='{}' ".format(request_params['branch']))
where_con = " and ".join(where_con_list)
if where_con == "":
where_con = "1"
if request_params['fromdate'] == "" or request_params['todate'] == "":
print("Yess Herreeee")
data = self.dbconn.select_from_table_paged("tbl_black_list", condition=" WHERE "+ where_con +" ORDER BY date_blacklisted DESC", offset=request_params['offset'], records=request_params['records'])
data_count = self.dbconn.select_count_table("tbl_black_list", condition="WHERE "+ where_con)
else:
data = self.dbconn.select_from_table_paged("tbl_black_list", condition=" WHERE "+ where_con +" AND date_blacklisted between '{0}' and '{1}' ORDER BY date_blacklisted DESC".format(request_params['fromdate'], request_params['todate']), offset=request_params['offset'], records=request_params['records'])
data_count = self.dbconn.select_count_table("tbl_black_list", condition="WHERE "+ where_con)
return [data, data_count]
def getAllUploads(self, request_params):
where_con_list = []
where_con = ''
if 'branch' in request_params and request_params['branch'] != "" and request_params['branch'] != "All":
where_con_list.append("rbranch='{}' ".format(request_params['branch']))
where_con = " and ".join(where_con_list)
if where_con == "":
where_con = "1"
if request_params['fromdate'] == "" or request_params['todate'] == "":
print("Yess Herreeee")
data = self.dbconn.select_from_table_paged("tbl_uploads", condition=" WHERE "+ where_con +" ORDER BY upload_date DESC", offset=request_params['offset'], records=request_params['records'])
data_count = self.dbconn.select_count_table("tbl_uploads", condition="WHERE "+ where_con)
else:
data = self.dbconn.select_from_table_paged("tbl_uploads", condition=" WHERE "+ where_con +" AND upload_date between '{0}' and '{1}' ORDER BY upload_date DESC".format(request_params['fromdate'], request_params['todate']), offset=request_params['offset'], records=request_params['records'])
data_count = self.dbconn.select_count_table("tbl_uploads", condition="WHERE "+ where_con)
return [data, data_count]
def getAllCustomByBranch(self, request_params):
where_con_list = []
where_con = ''
if 'status' in request_params and request_params['status'] != "" and request_params['status'] != "All":
where_con_list.append("status='{}' ".format(request_params['status']))
if 'branch' in request_params and request_params['branch'] != "" and request_params['branch'] != "All":
where_con_list.append("rbranch='{}' ".format(request_params['branch']))
where_con = " and ".join(where_con_list)
if where_con == "":
where_con = "1"
if request_params['fromdate'] == "" or request_params['todate'] == "":
data = self.dbconn.select_from_table_paged("tbl_customers", ["first_name", "middle_name", "last_name", "gender", "status", "join_date", "id"], " WHERE "+ where_con +" ORDER BY join_date DESC", offset=request_params['offset'], records=request_params['records'])
data_count = self.dbconn.select_count_table("tbl_customers", condition="WHERE "+ where_con)
else:
data = self.dbconn.select_from_table_paged("tbl_customers", ["first_name", "middle_name", "last_name", "gender", "status", "join_date", "id"], " WHERE "+ where_con +" AND join_date between '{0}' and '{1}' ORDER BY join_date DESC".format(request_params['fromdate'], request_params['todate']), offset=request_params['offset'], records=request_params['records'])
data_count = self.dbconn.select_count_table("tbl_customers", condition="WHERE "+ where_con)
return data
def getCustomerActivities(self, customer_id):
data = self.dbconn.select_from_table_paged("tbl_activity_log", condition=" WHERE user_msisdn='{}' ORDER BY date_created DESC".format(customer_id))
return data
def getCustomerAccounts(self, customer_id):
data = self.dbconn.select_from_table_paged("tbl_customer_account", ["id", "account_number", "branch", "status"], " WHERE customer='{}'".format(customer_id))
return data
def getCustomerTransactions(self, customer_acc):
data = self.dbconn.select_from_table_paged("tbl_transactions", condition=" WHERE account_number='{0}' or des_act='{0}' ORDER BY request_time DESC".format(customer_acc))
return data
def getCustomerRequests(self, customer_id):
data = self.dbconn.select_from_table_paged("tbl_user_requests", condition=" WHERE customer_account='{}' ORDER BY request_date DESC".format(customer_id))
return data
def getNewCustomerRegistration(self, request_data):
data = self.dbconn.select_from_table_paged("tbl_details_change", condition=" WHERE customer_account='{}' AND customer_msisdn='{}' ORDER BY request_date DESC".format(request_data['customer_account'], request_data['customer_msisdn']))
return data
def getCustomerRequestsByRequestId(self, request_id):
data = self.dbconn.select_from_table_paged("tbl_user_requests", condition=" WHERE id={} ORDER BY request_date DESC".format(request_id))
return data
def addCustomerRequest(self, data):
response = self.dbconn.insert_in_table("tbl_user_requests", data)
return response
def updateCustomers(self, data):
admin = self.dbconn.update_table("tbl_customers", data, "WHERE id='{}'".format(data['id']))
return admin
def updateCustomerAccount(self, data):
admin = self.dbconn.update_table("tbl_customer_account", data, "WHERE id='{}'".format(data['id']))
return admin
def searchCustomers(self, request_params):
search_data = self.dbconn.search_table(request_params['search_param'], "tbl_customers", ["id", "first_name", "middle_name", "last_name", "gender", "status", "join_date"])
print(search_data)
return search_data
def searchCustomersReq(self, request_params):
search_data = self.dbconn.search_table(request_params['search_param'], "tbl_user_requests", ["id", "requested_by", "request_type", "customer_msisdn", "customer_account", "change_to", "change_from", "request_date", "approved_by", "approve_date", "branch"])
print(search_data)
return search_data
def getBranches(self):
data = self.dbconn.select_from_table("tbl_branches")
return data
def deleteCustomerRequest(self, request_id):
data = self.dbconn.delete_from_table("tbl_user_requests", condition=" WHERE id={}".format(request_id))
return data
def deleteCustomerNewCustomer(self, customer_account, customer_msisdn):
data = self.dbconn.delete_from_table("tbl_details_change", condition=" WHERE customer_account='{}' and customer_msisdn='{}'".format(customer_account, customer_msisdn))
return data
def addNewRegistrationRequest(self, data):
response = self.dbconn.insert_in_table("tbl_details_change", data)
return response
def insertBulkUpload(self, data):
admin = self.dbconn.insert_in_table("tbl_uploads",data)
return True
def updateBulkUpload(self, data):
admin = self.dbconn.update_table("tbl_uploads", data, "WHERE bulk_id='{}'".format(data['bulk_id']))
return admin
def getBulkUploadDetails(self, request_id):
data = self.dbconn.select_from_table_paged("tbl_uploads", condition=" WHERE bulk_id='{}'".format(request_id))
return data
#API CALLS
def sic_register_customer(self, user_data):
request_data = {
"action": "register",
"uniqueid": user_data['customer_account'],
"msisdn": user_data['customer_msisdn'],
"firstname": user_data['new_fname'],
"lastname": user_data['new_lname'],
"middlename": user_data['new_mname'],
"dob": user_data['dob'].strftime("%Y-%m-%d"),
"gender": user_data['new_gender'],
"region": user_data['region'],
"city": user_data['city'],
"requestedBy": self.user['username'],
"requestBranch": self.user['branch_id']
}
req_data = {'model':"bankClient", 'func':"register", 'args':request_data}
print(request_data)
encryptor = AESCipher()
encoded_data = encryptor.encrypt(json.dumps(req_data))
print(encoded_data)
# response_data = self.API.send_socket_data(encoded_data)
# response_data = self.API.request_api_raw_json(encoded_data, url=API_URL, method='post', headers={"Authorization": "21d39021fc624f309fd9d41332e34rt5f"})
response_data = b'/MuglLXPw5C22iTP3W7aBWdHYpaToXvBOmSLmxB0ClX/PxrGHSarwj3TtE95R7LD'
print(response_data)
resp_data = encryptor.decrypt(response_data)
print(resp_data)
resp_data = json.loads(resp_data.decode('UTF-8'))
print(resp_data)
print(type(resp_data))
# data = {'code': '00', 'msg': 'Registration successful.'}
# print(data)
return resp_data
def sic_bulk_register_customer(self, request_data):
req_data = {'model':"bankClient", 'func':"register", 'args':request_data}
print(request_data)
encryptor = AESCipher()
encoded_data = encryptor.encrypt(json.dumps(req_data))
print(encoded_data)
# response_data = self.API.send_socket_data(encoded_data)
# response_data = self.API.request_api_raw_json(encoded_data, url=API_URL, method='post', headers={"Authorization": "21d39021fc624f309fd9d41332e34rt5f"})
response_data = b'/MuglLXPw5C22iTP3W7aBWdHYpaToXvBOmSLmxB0ClX/PxrGHSarwj3TtE95R7LD'
print(response_data)
resp_data = encryptor.decrypt(response_data)
resp_data = json.loads(resp_data.decode('UTF-8'))
print(resp_data)
print(type(resp_data))
# data = {'code': '00', 'msg': 'Registration successful.'}
# print(data)
return resp_data
def sic_reset_customer_pin(self, user_data):
request_data = {
"action": "resetpin",
"msisdn": user_data['id'],
}
req_data = {'model':"bankClient", 'func':"resetpin", 'args':request_data}
print(request_data)
encryptor = AESCipher()
encoded_data = encryptor.encrypt(json.dumps(req_data))
print(encoded_data)
# response_data = self.API.send_socket_data(encoded_data)
# response_data = self.API.request_api_raw_json(encoded_data, url=API_URL, method='post', headers={"Authorization": "21d39021fc624f309fd9d41332e34rt5f"})
response_data = b'/MuglLXPw5C22iTP3W7aBWdHYpaToXvBOmSLmxB0ClX/PxrGHSarwj3TtE95R7LD'
print(response_data)
resp_data = encryptor.decrypt(response_data)
resp_data = json.loads(resp_data.decode('UTF-8'))
print(resp_data)
print(type(resp_data))
# data = {'code': '00', 'msg': 'Registration successful.'}
# print(data)
return resp_data
def sic_change_customer_status(self, user_data, status):
request_data = {
"action": "customerstatus",
"msisdn": user_data['id'],
"status": status
}
req_data = {'model':"bankClient", 'func':"customerstatus", 'args':request_data}
print(request_data)
encryptor = AESCipher()
encoded_data = encryptor.encrypt(json.dumps(req_data))
print(encoded_data)
# response_data = self.API.send_socket_data(encoded_data)
# response_data = self.API.request_api_raw_json(encoded_data, url=API_URL, method='post', headers={"Authorization": "21d39021fc624f309fd9d41332e34rt5f"})
response_data = b'/MuglLXPw5C22iTP3W7aBWdHYpaToXvBOmSLmxB0ClX/PxrGHSarwj3TtE95R7LD'
print(response_data)
resp_data = encryptor.decrypt(response_data)
resp_data = json.loads(resp_data.decode('UTF-8'))
print(resp_data)
print(type(resp_data))
# data = {'code': '00', 'msg': 'Registration successful.'}
# print(data)
return resp_data
def sic_change_customer_account_status(self, user_data, status):
request_data = {
"action": "accountstatus",
"uniqueid": user_data['change_to'],
"status": status
}
req_data = {'model':"bankClient", 'func':"accountstatus", 'args':request_data}
print(request_data)
encryptor = AESCipher()
encoded_data = encryptor.encrypt(json.dumps(req_data))
print(encoded_data)
# response_data = self.API.send_socket_data(encoded_data)
# response_data = self.API.request_api_raw_json(encoded_data, url=API_URL, method='post', headers={"Authorization": "21d39021fc624f309fd9d41332e34rt5f"})
response_data = b'/MuglLXPw5C22iTP3W7aBWdHYpaToXvBOmSLmxB0ClX/PxrGHSarwj3TtE95R7LD'
print(response_data)
resp_data = encryptor.decrypt(response_data)
resp_data = json.loads(resp_data.decode('UTF-8'))
print(resp_data)
print(type(resp_data))
# data = {'code': '00', 'msg': 'Registration successful.'}
# print(data)
return resp_data
def get_fusion_VA_details_by_holder_id(self, holder_id):
req_data = {'model':"FUSION", 'func':"getByHolderId", 'params':json.dumps({"holderId": holder_id})}
data = self.API.request_api(req_data, url=VA_URL, method='post', headers={"Authorization": "21d39021fc624f309fd9d41332e34rt5f"})
# data = {'msg': {'accountName': 'VA_GT', 'accountMaps': [{'balance': '0', 'entityType': 'WALLET', 'usage': 'COLLECTIONS', 'mapID': '143c0ce34bf44205bd4447ec049f1ba3', 'entityID': '55df143cfedc9bfd3bb99977', 'threshold': 1, 'mapName': 'TIGO_COLLECTIONS'}, {'balance': '5005.00', 'entityType': 'WALLET', 'usage': 'DISBURSEMENTS', 'mapID': 'edc0369efeed434f9f200fc46e766925', 'entityID': '55df143cfedc9bfd3bb99977', 'threshold': 10000, 'mapName': 'TIGO_DISBURSEMENTS'}, {'balance': '0', 'entityType': 'WALLET', 'usage': 'COLLECTIONS', 'mapID': '5ed7ab06054f4c4b916c3f91ca1dcf6a', 'entityID': '55df1563fedc9bfd3bb99978', 'threshold': 1, 'mapName': 'AIRTEL_COLLECTIONS'}, {'balance': '5005.00', 'entityType': 'WALLET', 'usage': 'DISBURSEMENTS', 'mapID': '18fe760f3c50468895612b0b1ffe7973', 'entityID': '55df1563fedc9bfd3bb99978', 'threshold': 10000, 'mapName': 'AIRTEL_DISBURSEMENTS'}, {'balance': '0', 'entityType': 'WALLET', 'usage': 'COLLECTIONS', 'mapID': 'a09b2fdb7737476ebba6517042eb6bf4', 'entityID': '55df15fa12ece2fa3b91c54f', 'threshold': 1, 'mapName': 'MTN_COLLECTIONS'}, {'balance': '1178.27', 'entityType': 'WALLET', 'usage': 'DISBURSEMENTS', 'mapID': '22e76033e6c44bf2b8e2790bc2cc8163', 'entityID': '55df15fa12ece2fa3b91c54f', 'threshold': 10000, 'mapName': 'MTN_DISBURSEMENTS'}, {'balance': '0', 'entityType': 'WALLET', 'usage': 'COLLECTIONS', 'mapID': 'fc38611e361b42eaadcb48e95b60fead', 'entityID': '57e3c7ba4100ec664fb15a87', 'threshold': 1, 'mapName': 'VODAFONE_COLLECTIONS'}, {'balance': '4965.00', 'entityType': 'WALLET', 'usage': 'DISBURSEMENTS', 'mapID': '4892f3dcca8649849af69dc10bb07346', 'entityID': '57e3c7ba4100ec664fb15a87', 'threshold': 10000, 'mapName': 'VODAFONE_DISBURSEMENTS'}], 'id': '58f2e0edcc1a613a5a22a45f', 'accountNumber': 'VA170416031141', 'holderID': '55e474a83b4ec3737fc0104e', 'enabled': True}, 'code': '00'}
# print(data)
return data
def fusion_register_customer(self, user_data):
route = "/user/bc/{}".format(FS_INST_APIKEY)
user_data['instID'] = FS_INST_ID
user_data['kuwaita'] = "kuongeza_benki"
print(user_data)
# data = self.API.request_api(user_data, route=route, method='post')
data = {'msg':"Customer registered Successfully.",
'code': '00',
'data': [],
}
return data
def fusion_deactivate_customer(self, user_data):
route = "/user/bc/{}".format(FS_INST_APIKEY)
user_data['instID'] = FS_INST_ID
user_data['kuwaita'] = "mabadiliko_akaunti"
user_data['status'] = "false"
print(user_data)
# data = self.API.request_api(user_data, route=route, method='post')
data = {'msg':"Customer registered Successfully.",
'code': '00',
'data': [],
}
return data
def fusion_activate_customer(self, user_data):
route = "/user/bc/{}".format(FS_INST_APIKEY)
user_data['instID'] = FS_INST_ID
user_data['kuwaita'] = "mabadiliko_akaunti"
user_data['status'] = "true"
print(user_data)
# data = self.API.request_api(user_data, route=route, method='post')
data = {'msg':"Customer registered Successfully.",
'code': '00',
'data': [],
}
return data
def __init__(self, user):
super(Dashboard, self).__init__()
self.dbconn = MysqlLib()
self.user = user
def __init__(self, user):
super(Administrator, self).__init__()
self.dbconn = MysqlLib()
self.user = user
class Dashboard(object):
"""docstring for UserFunctions"""
def __init__(self, user):
super(Dashboard, self).__init__()
self.dbconn = MysqlLib()
self.user = user
def getAdminAnalysis(self, request_params):
data = {
"total_trans": {
"num": 0
},
"successful": {
"num": 0
},
"fails": {
"num": 0
},
"initiated": {
"num": 0
},
"sdata": [],
"pie_data": [],
"bar_data": []
}
cursor = self.dbconn.getInstanceCursor()
query = "SELECT status, COUNT(id) as num FROM `tbl_activity_log` WHERE date_created BETWEEN '{0}' and '{1}' GROUP BY status".format(
request_params['start_date'], request_params['end_date'])
# query = "SELECT status, COUNT(id) as num, SUM(amount) as amount FROM `tbl_activity_log` WHERE date_created BETWEEN '{0}' and '{1}' GROUP BY status".format("2017-01-01 00:00:00", "2017-07-01 23:59:59")
print(query)
cursor.execute(query)
res = cursor.fetchall()
print(res)
for result in res:
data["total_trans"]["num"] = round(data["total_trans"]["num"] +
result['num'])
if result['status'] == 'PENDING':
data["initiated"]["num"] = result['num']
if result['status'] == 'FAILED':
data["fails"]["num"] = result['num']
if result['status'] == 'SUCCESSFUL':
data["successful"]["num"] = result['num']
data['sdata'] = res
return data
def getBranchAnalysis(self, request_params):
data = {
"successful": {
"num": 0
},
"fails": {
"num": 0
},
"initiated": {
"num": 0
},
"pie_data": [],
"bar_data": []
}
cursor = self.dbconn.getInstanceCursor()
query = "SELECT status, COUNT(tbl_transaction.id) as num FROM `tbl_transaction` INNER JOIN tbl_file_upload ON tbl_transaction.bulk_id=tbl_file_upload.bulk_id AND tbl_file_upload.merchant_id='{0}' WHERE transaction_date BETWEEN '{1}' and '{2}' GROUP BY status".format(
self.user['institution_data']['id'], request_params['fromdate'],
request_params['todate'])
print(query)
cursor.execute(query)
res = cursor.fetchall()
print(res)
for result in res:
if result['status'] == 1:
data["initiated"]["num"] = result['num']
data["initiated"]["amount"] = result['amount']
if result['status'] == 2:
data["fails"]["num"] = result['num']
data["fails"]["amount"] = result['amount']
if result['status'] == 3:
data["successful"]["num"] = result['num']
data["successful"]["amount"] = result['amount']
query = "SELECT receivingHouse, COUNT(tbl_transaction.id) as num, SUM(tbl_transaction.amount) as amount FROM `tbl_transaction` INNER JOIN tbl_file_upload ON tbl_transaction.bulk_id=tbl_file_upload.bulk_id AND tbl_file_upload.merchant_id='{0}' WHERE transaction_date BETWEEN '{1}' and '{2}' GROUP BY status".format(
self.user['institution_data']['id'], request_params['fromdate'],
request_params['todate'])
print(query)
cursor.execute(query)
res = cursor.fetchall()
print(res)
for result in res:
data["pie_data"].append({
"institution": result['receivingHouse'],
"number": result['num']
})
data["bar_data"].append({
"institution": result['receivingHouse'],
"number": result['amount']
})
print(data)
return data
class Administrator(object):
"""docstring for UserFunctions"""
def __init__(self, user):
super(Administrator, self).__init__()
self.dbconn = MysqlLib()
self.user = user
def getAdminByUsernameLogin(self, username):
# admin = self.dbconn.select_from_table('tbl_logins', condition = "WHERE username= '******'")
admin = self.dbconn.joint_select(
"tbl_logins", ["tbl_user_rights", "tbl_branches"], [
"tbl_logins.password", "tbl_logins.username",
"tbl_logins.user_right_id", "tbl_logins.first_name",
"tbl_logins.last_name", "tbl_logins.email",
"tbl_logins.msisdn", "tbl_logins.last_login",
"tbl_logins.pass_date", "tbl_logins.active",
"tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code",
"tbl_branches.branch_id"
], [
"tbl_logins.user_right_id=tbl_user_rights.id",
"tbl_logins.branch=tbl_branches.branch_code"
],
gen_condition="WHERE username= '******'")
return admin
def getAdminByUsername(self, username):
# admin = self.dbconn.select_from_table('tbl_logins', condition = "WHERE username= '******'")
admin = self.dbconn.joint_select(
"tbl_logins", ["tbl_user_rights", "tbl_branches"], [
"tbl_logins.username", "tbl_logins.user_right_id",
"tbl_logins.first_name", "tbl_logins.last_name",
"tbl_logins.email", "tbl_logins.msisdn",
"tbl_logins.last_login", "tbl_logins.pass_date",
"tbl_logins.active", "tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code",
"tbl_branches.branch_id"
], [
"tbl_logins.user_right_id=tbl_user_rights.id",
"tbl_logins.branch=tbl_branches.branch_code"
],
gen_condition="WHERE username= '******'")
return admin
def getAdminByUsername_chg(self, username):
# admin = self.dbconn.select_from_table('tbl_logins', condition = "WHERE username= '******'")
admin = self.dbconn.joint_select(
"tbl_logins", ["tbl_user_rights", "tbl_branches"], [
"tbl_logins.username", "tbl_logins.password",
"tbl_logins.user_right_id", "tbl_logins.first_name",
"tbl_logins.last_name", "tbl_logins.email",
"tbl_logins.msisdn", "tbl_logins.last_login",
"tbl_logins.pass_date", "tbl_logins.active",
"tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code",
"tbl_branches.branch_id"
], [
"tbl_logins.user_right_id=tbl_user_rights.id",
"tbl_logins.branch=tbl_branches.branch_code"
],
gen_condition="WHERE username= '******'")
return admin
def getAdminByMsisdn(self, msisdn):
# admin = self.dbconn.select_from_table('tbl_logins', condition = "WHERE msisdn= '"+ msisdn +"'")
admin = self.dbconn.joint_select(
"tbl_logins", ["tbl_user_rights", "tbl_branches"], [
"tbl_logins.username", "tbl_logins.user_right_id",
"tbl_logins.first_name", "tbl_logins.last_name",
"tbl_logins.email", "tbl_logins.msisdn",
"tbl_logins.last_login", "tbl_logins.pass_date",
"tbl_logins.active", "tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code",
"tbl_branches.branch_id"
], [
"tbl_logins.user_right_id=tbl_user_rights.id",
"tbl_logins.branch=tbl_branches.branch_code"
],
gen_condition="WHERE msisdn= '" + msisdn + "'")
return admin
def getAdminByEmail(self, email):
# admin = self.dbconn.select_from_table('tbl_logins', condition = "WHERE email= '"+ email +"'")
admin = self.dbconn.joint_select(
"tbl_logins", ["tbl_user_rights", "tbl_branches"], [
"tbl_logins.username", "tbl_logins.user_right_id",
"tbl_logins.first_name", "tbl_logins.last_name",
"tbl_logins.email", "tbl_logins.msisdn",
"tbl_logins.last_login", "tbl_logins.pass_date",
"tbl_logins.active", "tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code",
"tbl_branches.branch_id"
], [
"tbl_logins.user_right_id=tbl_user_rights.id",
"tbl_logins.branch=tbl_branches.branch_code"
],
gen_condition="WHERE email= '" + email + "'")
return admin
def getAllAdministrators(self, request_params):
where_con_list = []
where_con = ''
if 'active' in request_params != "" and request_params[
'active'] != "" and request_params['active'] != "All":
where_con_list.append("active='{}' ".format(
request_params['active']))
if 'user_right_id' in request_params != "" and request_params[
'user_right_id'] != "":
where_con_list.append("user_right_id='{}' ".format(
request_params['user_right_id']))
if 'branch' in request_params != "" and request_params[
'branch'] != "" and request_params['branch'] != "All":
where_con_list.append("branch='{}' ".format(
request_params['branch']))
where_con = " and ".join(where_con_list)
if where_con == "":
where_con = "1"
if request_params['fromdate'] == "" or request_params['todate'] == "":
data = self.dbconn.joint_select_paged(
"tbl_logins", ["tbl_user_rights", "tbl_branches"], [
"tbl_logins.username", "tbl_logins.user_right_id",
"tbl_logins.first_name", "tbl_logins.last_name",
"tbl_logins.email", "tbl_logins.msisdn",
"tbl_logins.last_login", "tbl_logins.active",
"tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code"
], [
"tbl_logins.user_right_id=tbl_user_rights.id",
"tbl_logins.branch=tbl_branches.branch_code"
],
"WHERE " + where_con + " ORDER BY created DESC",
offset=request_params['offset'],
records=request_params['records'])
else:
data = self.dbconn.joint_select_paged(
"tbl_logins", ["tbl_user_rights", "tbl_branches"], [
"tbl_logins.username", "tbl_logins.user_right_id",
"tbl_logins.first_name", "tbl_logins.last_name",
"tbl_logins.email", "tbl_logins.msisdn",
"tbl_logins.last_login", "tbl_logins.active",
"tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code"
], [
"tbl_logins.user_right_id=tbl_user_rights.id",
"tbl_logins.branch=tbl_branches.branch_code"
],
"WHERE " + where_con +
" and created between '{0}' and '{1}' ORDER BY created DESC".
format(request_params['fromdate'], request_params['todate']),
offset=request_params['offset'],
records=request_params['records'])
return data
def getAllAdministratorsByBranch(self, request_params):
where_con_list = []
where_con = ''
if 'active' in request_params != "" and request_params[
'active'] != "" and request_params['active'] != "All":
where_con_list.append("active='{}' ".format(
request_params['active']))
if 'user_right_id' in request_params != "" and request_params[
'user_right_id'] != "":
where_con_list.append("user_right_id='{}' ".format(
request_params['user_right_id']))
if 'branch' in request_params != "" and request_params[
'branch'] != "" and request_params['branch'] != "All":
where_con_list.append("branch='{}' ".format(
request_params['branch']))
where_con = " and ".join(where_con_list)
if where_con == "":
where_con = "1"
if request_params['fromdate'] == "" or request_params['todate'] == "":
data = self.dbconn.joint_select_paged(
"tbl_logins", ["tbl_user_rights", "tbl_branches"], [
"tbl_logins.username", "tbl_logins.user_right_id",
"tbl_logins.first_name", "tbl_logins.last_name",
"tbl_logins.email", "tbl_logins.msisdn",
"tbl_logins.last_login", "tbl_logins.active",
"tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code"
], [
"tbl_logins.user_right_id=tbl_user_rights.id",
"tbl_logins.branch=tbl_branches.branch_code"
],
"WHERE " + where_con +
" and tbl_logins.branch='{0}' ORDER BY created DESC".format(
self.user['branch_code']),
offset=request_params['offset'],
records=request_params['records'])
else:
data = self.dbconn.joint_select_paged(
"tbl_logins", ["tbl_user_rights", "tbl_branches"], [
"tbl_logins.username", "tbl_logins.user_right_id",
"tbl_logins.first_name", "tbl_logins.last_name",
"tbl_logins.email", "tbl_logins.msisdn",
"tbl_logins.last_login", "tbl_logins.active",
"tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code"
], [
"tbl_logins.user_right_id=tbl_user_rights.id",
"tbl_logins.branch=tbl_branches.branch_code"
],
"WHERE " + where_con +
" and tbl_logins.branch='{0}' and created between '{1}' and '{2}' ORDER BY created DESC"
.format(self.user['branch_code'], request_params['fromdate'],
request_params['todate']),
offset=request_params['offset'],
records=request_params['records'])
return data
def addAdministrator(self, data):
admin = self.dbconn.insert_in_table("tbl_logins", data)
return admin
def updateAdministrator(self, data):
admin = self.dbconn.update_table(
"tbl_logins", data, "WHERE username='******'".format(data['username']))
return admin
def getAdminGroups(self):
data = self.dbconn.select_from_table("tbl_user_rights",
condition="where id != 1")
return data
def addAdministratorGroup(self, data):
admin = self.dbconn.insert_in_table("tbl_user_rights", data)
return admin
def updateAdministratorGroup(self, data):
admin = self.dbconn.update_table("tbl_user_rights", data,
"WHERE id='{}'".format(data['id']))
return admin
def getBranches(self, request_params):
data = self.dbconn.select_from_table_paged(
"tbl_branches",
offset=request_params['offset'],
records=request_params['records'])
return data
def getBranchByCode(self, branch_code):
data = self.dbconn.select_from_table(
"tbl_branches",
condition="WHERE branch_code='{}'".format(branch_code))
return data
def searchAdmins(self, request_params):
search_data = self.dbconn.joint_table_search(
request_params['search_param'], "tbl_logins",
["tbl_branches", "tbl_user_rights"], [
"tbl_logins.username", "tbl_logins.user_right_id",
"tbl_logins.first_name", "tbl_logins.last_name",
"tbl_logins.email", "tbl_logins.msisdn",
"tbl_logins.last_login", "tbl_logins.active",
"tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code"
], [
"tbl_logins.branch=tbl_branches.branch_code",
"tbl_logins.user_right_id=tbl_user_rights.id"
])
return search_data
def searchAdminsBranch(self, request_params):
search_data = self.dbconn.joint_table_search(
request_params['search_param'],
"tbl_logins", ["tbl_branches", "tbl_user_rights"], [
"tbl_logins.username", "tbl_logins.user_right_id",
"tbl_logins.first_name", "tbl_logins.last_name",
"tbl_logins.email", "tbl_logins.msisdn",
"tbl_logins.last_login", "tbl_logins.active",
"tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code"
], [
"tbl_logins.branch=tbl_branches.branch_code",
"tbl_logins.user_right_id=tbl_user_rights.id"
],
gen_cond=" and tbl_logins.branch='{0}'".format(
self.user['branch_code']))
return search_data
def searchAdminsBranch(self, request_params):
search_data = self.dbconn.joint_table_search(
request_params['search_param'],
"tbl_logins", ["tbl_branches", "tbl_user_rights"], [
"tbl_logins.username", "tbl_logins.user_right_id",
"tbl_logins.first_name", "tbl_logins.last_name",
"tbl_logins.email", "tbl_logins.msisdn",
"tbl_logins.last_login", "tbl_logins.active",
"tbl_logins.status", "tbl_logins.created",
"tbl_user_rights.name", "tbl_user_rights.details",
"tbl_branches.branch_name", "tbl_branches.branch_code"
], [
"tbl_logins.branch=tbl_branches.branch_code",
"tbl_logins.user_right_id=tbl_user_rights.id"
],
gen_cond=" and tbl_logins.branch='{0}'".format(
self.user['branch_code']))
return search_data
def searchBranches(self, request_params):
search_data = self.dbconn.search_table(
request_params['search_param'], 'tbl_branches',
["id", "branch_id", "acronym", "branch_name", "branch_code"])
return search_data
def addAdminBranch(self, data):
admin = self.dbconn.insert_in_table("tbl_branches", data)
return admin
def removeAdminBranch(self, data):
admin = self.dbconn.delete_from_table(
"tbl_branches", "where branch_id={}".format(data['branch_id']))
return admin
def __init__(self, user):
super(LogModel, self).__init__()
self.dbconn = MysqlLib()
self.user = user
class LogModel(object):
"""docstring for UserFunctions"""
def __init__(self, user):
super(LogModel, self).__init__()
self.dbconn = MysqlLib()
self.user = user
def getAllTransactions(self, request_params):
where_con_list = []
where_con = ''
if "status" in request_params and request_params['status'] != "":
where_con_list.append("msg_stat='{}' ".format(
request_params['status']))
if "branch" in request_params and request_params['branch'] != "":
where_con_list.append("account_branch='{}' ".format(
request_params['branch']))
if "destination" in request_params and request_params[
'destination'] != "":
where_con_list.append("destination='{}' ".format(
request_params['destination']))
if "type" in request_params and request_params['type'] != "":
where_con_list.append("type='{}' ".format(request_params['type']))
if "tag" in request_params and request_params['tag'] != "":
where_con_list.append("fusion_tag='{}' ".format(
request_params['tag']))
where_con = " and ".join(where_con_list)
if where_con == "":
where_con = "1"
if request_params['fromdate'] == "" or request_params['todate'] == "":
data = self.dbconn.select_from_table_paged(
"tbl_transactions",
condition="WHERE " + where_con +
" ORDER BY response_time DESC",
offset=request_params['offset'],
records=request_params['records'])
data_count = self.dbconn.select_count_table("tbl_transactions",
condition="WHERE " +
where_con)
else:
data = self.dbconn.select_from_table_paged(
"tbl_transactions",
condition=" WHERE " + where_con +
" and response_time between '{0}' and '{1}' ORDER BY response_time DESC"
.format(request_params['fromdate'], request_params['todate']),
offset=request_params['offset'],
records=request_params['records'])
data_count = self.dbconn.select_count_table("tbl_transactions",
condition="WHERE " +
where_con)
return [data, data_count]
def getAllTransactionsByBranch(self, request_params):
where_con_list = []
where_con = ''
if request_params['status'] != "":
where_con_list.append("status='{}' ".format(
request_params['status']))
where_con = " and ".join(where_con_list)
if where_con == "":
where_con = "1"
if request_params['fromdate'] == "" or request_params['todate'] == "":
data = self.dbconn.select_from_table_paged(
"tbl_transactions", ["tbl_file_upload"], ["tbl_transaction.*"],
[
"tbl_transaction.bulk_id=tbl_file_upload.bulk_id AND tbl_file_upload.merchant_id='{}'"
.format(self.user['institution_data']['id'])
],
"WHERE " + where_con +
" ORDER BY transaction_date DESC".format(
request_params['fromdate'], request_params['todate']),
offset=request_params['offset'],
records=request_params['records'])
else:
data = self.dbconn.select_from_table_paged(
"tbl_transactions", ["tbl_file_upload"], ["tbl_transaction.*"],
[
"tbl_transaction.bulk_id=tbl_file_upload.bulk_id AND tbl_file_upload.merchant_id='{}'"
.format(self.user['institution_data']['id'])
],
"WHERE " + where_con +
" and transaction_date between '{0}' and '{1}' ORDER BY date_upload DESC"
.format(request_params['fromdate'], request_params['todate']),
offset=request_params['offset'],
records=request_params['records'])
return data
def getAllTransactionsfilter(self):
filter_data = {}
filter_data['branches'] = self.dbconn.select_distinct(
"tbl_transactions", "account_branch")
filter_data['destination'] = self.dbconn.select_distinct(
"tbl_transactions", "destination")
filter_data['type'] = self.dbconn.select_distinct(
"tbl_transactions", "type")
filter_data['status'] = self.dbconn.select_distinct(
"tbl_transactions", "msg_stat")
filter_data['tag'] = self.dbconn.select_distinct(
"tbl_transactions", "fusion_tag")
print(filter_data)
return filter_data
def searchTransactions(self, request_params):
search_data = self.dbconn.search_table(
request_params['search_param'], "tbl_transactions", [
'xref', 'reference', 'account_branch', 'processing_branch',
'account_number', 'des_act', 'destination', 'msisdn',
'msg_stat', 'fusion_tag', 'type', 'request_time',
'response_time'
])
return search_data
def getAllTransactionsfilterForBranch(self):
pass
def getBulkUploadDetailsByBulkId(self, bulk_id):
print(bulk_id)
data = self.dbconn.joint_select(
"tbl_file_upload", ["tbl_login", "tbl_file_upload_xdetails"], [
"tbl_file_upload.*",
"tbl_login.username",
"tbl_file_upload_xdetails.amount",
"tbl_login.institution_shortName",
], [
"tbl_file_upload.merchant_admin_id=tbl_login.id",
"tbl_file_upload_xdetails.bulk_id='{}'".format(bulk_id)
], "WHERE tbl_file_upload.bulk_id= \'" + bulk_id + "\'")
approval_data = self.dbconn.joint_select(
"tbl_file_upload_approval", ["tbl_login"],
["tbl_login.*", "tbl_file_upload_approval.*"],
["tbl_file_upload_approval.merchant_admin_id=tbl_login.id"],
gen_condition="WHERE tbl_file_upload_approval.bulk_id='" +
bulk_id + "'")
if approval_data == []:
data[0]['approval_data'] = []
else:
data[0]['approval_data'] = approval_data
return data
def insertBulkUpload(self, data):
admin = self.dbconn.insert_in_table("tbl_file_upload", data)
return True
def insertBulkUploadXtraDetails(self, data):
res = self.dbconn.insert_in_table("tbl_file_upload_xdetails", data)
return res
def updateAdminByUsername(self, username, data):
admin = self.dbconn.update_table("tbl_login", data,
"WHERE bulk_id='" + username + "'")
return True
def getValidationLog(self, request_params):
return True
def __init__(self, user):
super(Vouchers, self).__init__()
self.dbconn = MysqlLib()
self.user = user
self.API = ApiCalls()
def __init__(self, user):
super(Validator, self).__init__()
self.dbconn = MysqlLib()
self.user = user
class Validator(object):
"""docstring for UserFunctions"""
def __init__(self, user):
super(Validator, self).__init__()
self.dbconn = MysqlLib()
self.user = user
def getValidatorByMsisdn(self, msisdn):
validator = self.dbconn.select_from_table('tbl_validators',
condition="WHERE msisdn= '" +
msisdn + "'")
return validator
def getValidatorByEmail(self, email):
validator = self.dbconn.select_from_table('tbl_validators',
condition="WHERE email= '" +
email + "'")
return validator
def getAllValidators(self, request_params):
where_con = ""
if request_params['branch'] == "None":
where_con = "1"
else:
where_con = "branch= '" + request_params['branch'] + "'"
data = self.dbconn.select_from_table_paged(
"tbl_validators",
condition=" WHERE " + where_con + " ORDER BY date_created DESC",
offset=request_params['offset'],
records=request_params['records'])
data_count = self.dbconn.select_count_table("tbl_validators",
condition=" WHERE " +
where_con)
return [data, data_count]
def addValidator(self, data):
admin = self.dbconn.insert_in_table("tbl_validators", data)
return admin
def updateValidator(self, data):
admin = self.dbconn.update_table(
"tbl_validators", data, "WHERE email='{}'".format(data['email']))
return admin
def deleteValidator(self, request_id):
data = self.dbconn.delete_from_table(
"tbl_validators",
condition=" WHERE validator_id={}".format(request_id))
return data
def getBranches(self):
data = self.dbconn.select_from_table("tbl_branches")
return data
def getValidatorsHistory(self, request_params):
where_con = ""
if request_params['user_branch'] == "Non":
where_con = "1"
else:
where_con = "user_branch= '" + request_params['user_branch'] + "'"
if request_params['fromdate'] == "" or request_params['todate'] == "":
data = self.dbconn.select_from_table_paged(
"tbl_activity_log",
condition=" WHERE user_type='{}'".format(
request_params["user_type"]) + " AND " + where_con +
" ORDER BY date_created DESC",
offset=request_params['offset'],
records=request_params['records'])
data_count = self.dbconn.select_count_table(
"tbl_activity_log",
condition=" WHERE user_type='{}'".format(
request_params["user_type"]) + " AND " + where_con)
else:
data = self.dbconn.select_from_table_paged(
"tbl_activity_log",
condition=" WHERE user_type='{}'".format(
request_params["user_type"]) + " AND " + where_con +
" AND DATE(date_created) BETWEEN '{0}' AND '{1}' ORDER BY date_created DESC"
.format(request_params['fromdate'], request_params['todate']),
offset=request_params['offset'],
records=request_params['records'])
data_count = self.dbconn.select_count_table(
"tbl_activity_log",
condition=" WHERE user_type='{}'".format(
request_params["user_type"]) + " AND " + where_con +
" AND DATE(date_created) BETWEEN '{0}' AND '{1}'".format(
request_params['fromdate'], request_params['todate']))
return [data, data_count]
def searchValidatoinHistory(self, request_params):
data = self.dbconn.select_from_table(
"tbl_activity_log",
condition=" WHERE user_type='{}'".format(
request_params["user_type"]) +
" AND serial_no LIKE '%{}%'".format(
request_params['search_param']))
return data