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
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
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