def get_all_compliance_frequency(): _source_db_con = connectKnowledgeDB() _source_db = Database(_source_db_con) _source_db.begin() result = _source_db.call_proc('sp_bu_compliance_frequency') result.pop(0) return result
def get_pending_mapping_list(db, cid, did, uploaded_by, session_user): csv_data = [] _source_db_con = connectKnowledgeDB() _source_db = Database(_source_db_con) _source_db.begin() result = _source_db.call_proc("sp_bu_get_mapped_knowledge_executives", [session_user.user_id(), cid, did]) print "result >>" print result print len(result) _source_db_con.close() mapped_executives = '' if len(result) != 0: mapped_executives = ",".join(str(r["child_user_id"]) for r in result) print "mapped_executives-> ", mapped_executives if uploaded_by is None: uploaded_by = mapped_executives data = db.call_proc("sp_pending_statutory_mapping_csv_list", [uploaded_by, cid, did]) for d in data: # file_name = d["csv_name"].split('.') # remove_code = file_name[0].split('_') # csv_name = "%s.%s" % ('_'.join(remove_code[:-1]), file_name[1]) upload_on = d["uploaded_on"].strftime("%d-%b-%Y %H:%M") csv_data.append( bu_sm.PendingCsvList(d["csv_id"], d["csv_name"], d["uploaded_by"], upload_on, d["total_records"], d["approve_count"], d["rej_count"], d["csv_name"], d["declined_count"])) return csv_data
def get_knowledge_executive_bu(db, manager_id): _source_db_con = connectKnowledgeDB() _source_db = Database(_source_db_con) _source_db.begin() result = _source_db.call_proc("sp_know_executive_info", [manager_id]) user_info = {} for r in result: userid = r.get("child_user_id") u = user_info.get(userid) emp_name = "%s - %s" % (r.get("employee_code"), r.get("employee_name")) if u is None: u = bu_sm.KExecutiveInfo([r.get("country_id")], [r.get("domain_id")], emp_name, r.get("child_user_id")) user_info[userid] = u else: c_ids = user_info.get(userid).c_ids c_ids.append(r.get("country_id")) d_ids = user_info.get(userid).d_ids d_ids.append(r.get("domain_id")) user_info[userid].c_ids = c_ids user_info[userid].d_ids = d_ids return user_info.values()
def get_techno_users_list(db, utype, user_id): _source_db_con = connect_knowledge_db() _source_db = Database(_source_db_con) _source_db.begin() techno_users = [] data = _source_db.call_proc("sp_techno_users_info", [utype, user_id]) _source_db.close() for d in data: emp_code_name = "%s - %s" %\ (d.get("employee_code"), d.get("employee_name")) techno_users.append( bu_cu.TechnoInfo(int(d.get("group_id")), d.get("user_id"), emp_code_name)) return techno_users
class SourceDB(object): def __init__(self): self._source_db = None self._source_db_con = None self._client_group_ = {} self._country_ = {} self._legal_entity_ = {} self._domain = {} self._unit_location = {} self._unit_code = {} self._unit_name = {} self._statutories = {} self._child_statutories = {} self._statutory_provision = {} self._compliance_task = {} self._compliance_description = {} self._organisation = {} self._applicable_status = {} self.connect_source_db() self._validation_maps = {} self.statusCheckMethods() self._csv_column_name = [] self.csv_column_fields() def connect_source_db(self): self._source_db_con = mysql.connector.connect( user=KNOWLEDGE_DB_USERNAME, password=KNOWLEDGE_DB_PASSWORD, host=KNOWLEDGE_DB_HOST, database=KNOWLEDGE_DATABASE_NAME, port=KNOWLEDGE_DB_PORT, autocommit=False, ) self._source_db = Database(self._source_db_con) self._source_db.begin() def close_source_db(self): self._source_db.close() self.__source_db_con.close() def init_values(self, user_id, client_id, country_id, legal_entity_id): self.get_client_groups(user_id) self.get_countries(user_id, legal_entity_id) self.get_legal_entities(user_id, client_id, country_id) self.get_domains(user_id) self.get_unit_location() self.get_unit_code(legal_entity_id) self.get_unit_name(legal_entity_id) self.get_statutories() self.get_child_statutories() self.get_statutory_provision() self.get_compliance_task() self.get_compliance_description() self.get_organisation(country_id) self.get_applicable_status() def get_client_groups(self, user_id): data = self._source_db.call_proc("sp_bu_as_user_groups", [user_id]) for d in data: self._client_group_[d["group_name"]] = d def get_countries(self, user_id, legal_entity_id): data = self._source_db.call_proc("sp_bu_as_user_countries", [user_id, legal_entity_id]) for d in data: self._country_[d["country_name"]] = d def get_legal_entities(self, user_id, client_id, country_id): data = self._source_db.call_proc("sp_bu_as_user_legal_entities", [user_id, client_id, country_id]) for d in data: self._legal_entity_[d["legal_entity_name"]] = d def get_domains(self, user_id): data = self._source_db.call_proc("sp_bu_as_user_domains", [user_id]) for d in data: self._domain[d["domain_name"]] = d def get_unit_location(self): data = self._source_db.call_proc("sp_bu_client_unit_geographies") for d in data: self._unit_location[d["geography_name"]] = d def get_unit_code(self, legal_entity_id): data = self._source_db.call_proc("sp_bu_unit_code_and_name", [legal_entity_id]) for d in data: self._unit_code[d["unit_code"]] = d def get_unit_name(self, legal_entity_id): data = self._source_db.call_proc("sp_bu_unit_code_and_name", [legal_entity_id]) for d in data: self._unit_name[d["unit_name"]] = d def get_statutories(self): data = self._source_db.call_proc("sp_bu_level_one_statutories") for d in data: self._statutories[d["statutory_name"]] = d def get_child_statutories(self): data = self._source_db.call_proc("sp_bu_chils_level_statutories") for d in data: self._child_statutories[d["statutory_name"]] = d def get_statutory_provision(self): data = self._source_db.call_proc("sp_bu_compliance_info") for d in data: self._statutory_provision[d["statutory_provision"]] = d def get_compliance_task(self): data = self._source_db.call_proc("sp_bu_compliance_info") for d in data: self._compliance_task[d["compliance_task"]] = d def get_compliance_description(self): data = self._source_db.call_proc("sp_bu_compliance_info") for d in data: self._compliance_description[d["compliance_description"]] = d def get_organisation(self, country_id): data = self._source_db.call_proc("sp_bu_organization_all", [country_id]) for d in data: self._organisation[d["organisation_name"] + '-' + d["domain_name"]] = d def get_applicable_status(self): data = [{ 'applicable_status': 'applicable' }, { 'applicable_status': 'not applicable' }, { 'applicable_status': 'do not show' }] for d in data: self._applicable_status[d["applicable_status"]] = d def check_base(self, check_status, store, key_name, status_name): data = store.get(key_name) if data is None: return "Not found" if check_status is True: if status_name is None: if data.get("is_active") == 0: return "Status Inactive" if data.get("is_closed") == 1: return "Status Inactive" if data.get("is_approved") == 0: return "Status Inactive" return True def check_client_group(self, group_name): return self.check_base(True, self._client_group_, group_name, None) def check_country(self, country_name): return self.check_base(True, self._country_, country_name, None) def check_legal_entity(self, legal_entity_name): return self.check_base(True, self._legal_entity_, legal_entity_name, None) def check_domain(self, domain_name): return self.check_base(True, self._domain, domain_name, None) def check_unit_location(self, geography_name): return self.check_base(True, self._unit_location, geography_name, None) def check_unit_code(self, unit_code): return self.check_base(True, self._unit_code, unit_code, None) def check_unit_name(self, unit_name): return self.check_base(True, self._unit_name, unit_name, None) def check_statutories(self, statutories): return self.check_base(False, self._statutories, statutories, None) def check_statutory_provision(self, statutory_provision): return self.check_base(False, self._statutory_provision, statutory_provision, None) def check_compliance_task(self, compliance_task): return self.check_base(True, self._compliance_task, compliance_task, None) def check_compliance_description(self, compliance_description): return self.check_base(False, self._compliance_description, compliance_description, None) def check_organisation(self, organisation_name): return self.check_base(True, self._organisation, organisation_name, None) def check_applicable_status(self, applicable_status): return self.check_base(False, self._applicable_status, applicable_status.lower(), None) def check_child_statutories(self, child_statutories): return self.check_base(False, self._child_statutories, child_statutories, None) # save client statutories data in tbl_client_statutories main db def save_client_statutories_data(self, cl_id, u_id, d_id, user_id, is_rejected): created_on = get_date_time() status = 3 if is_rejected is True: status = 4 client_statutory_value = [ int(cl_id), int(u_id), int(d_id), status, int(user_id), str(created_on) ] q = "INSERT INTO tbl_client_statutories (client_id, unit_id, " + \ " domain_id, status, approved_by, approved_on) values " + \ " (%s, %s, %s, %s, %s, %s)" client_statutory_id = self._source_db.execute_insert( q, client_statutory_value) if client_statutory_id is False: raise process_error("E018") return client_statutory_id # check rejected compliance is available or not in child table def get_client_compliance_rejected_status(self, legal_entity, domain, unit_code, csv_id): res = self._db.call_proc("sp_check_client_compliance_rejected_status", [legal_entity, domain, unit_code, csv_id]) if len(res) > 0: return True else: return False # save client compliance data in tbl_client_compliances main db def save_client_compliances_data(self, cl_id, le_id, u_id, d_id, cs_id, data, user_id, client_id_, is_rejected, saved_by, saved_on): created_on = get_date_time() columns = [ "client_statutory_id", "client_id", "legal_entity_id", "unit_id", "domain_id", "statutory_id", "statutory_applicable_status", "remarks", "compliance_id", "compliance_applicable_status", "is_saved", "saved_by", "saved_on", "is_submitted", "submitted_by", "submitted_on", "is_approved", "approved_by", "approved_on", "updated_by", "updated_on" ] values = [] for idx, d in enumerate(data): approval_status = 0 submitted_status = 0 if is_rejected is True and d["action"] == 1: approval_status = 2 elif d["Compliance_Applicable_Status"] == 3 and d["action"] == 1: approval_status = 3 elif d["Compliance_Applicable_Status"] != 3 and d["action"] == 1: approval_status = 99 submitted_status = 1 else: approval_status = 4 statu_id = self._statutories.get( d["Primary_Legislation"]).get("statutory_id") comp_id = None c_ids = self._source_db.call_proc( "sp_bu_get_compliance_id_by_name", [ d["Compliance_Task"], d["Compliance_Description"], d["Statutory_Provision"], client_id_, d_id, d["Primary_Legislation"], d["Secondary_Legislation"] ]) for c_id in c_ids: comp_id = c_id["compliance_id"] values.append( (int(cs_id), cl_id, le_id, u_id, d_id, statu_id, d["Statutory_Applicable_Status"], d["Statutory_remarks"], comp_id, d["Compliance_Applicable_Status"], 1, saved_by, saved_on, submitted_status, saved_by, saved_on, approval_status, int(user_id), created_on, int(user_id), created_on)) if values: self._source_db.bulk_insert("tbl_client_compliances", columns, values) q = "update tbl_client_compliances set is_approved = 5 " + \ "where is_approved = 99 and client_statutory_id = %s" params = [int(cs_id)] self._source_db.execute(q, params) return True else: return False # main db related validation mapped with field name def statusCheckMethods(self): self._validation_maps = { "Client_Group": self.check_client_group, "Country": self.check_country, "Legal_Entity": self.check_legal_entity, "Domain": self.check_domain, "Unit_Location": self.check_unit_location, "Unit_Code": self.check_unit_code, "Unit_Name": self.check_unit_name, "Primary_Legislation": self.check_statutories, "Secondary_Legislation": self.check_child_statutories, "Statutory_Provision": self.check_statutory_provision, "Compliance_Task": self.check_compliance_task, "Compliance_Description": self.check_compliance_description, "Organization": self.check_organisation, "Statutory_Applicable_Status": self.check_applicable_status, "Compliance_Applicable_Status": self.check_applicable_status } # declare csv column field name def csv_column_fields(self): self._csv_column_name = [ "S.No", "Client_Group", "Country", "Legal_Entity", "Domain", "Organization", "Unit_Code", "Unit_Name", "Unit_Location", "Primary_Legislation", "Secondary_Legislation", "Statutory_Provision", "Compliance_Task", "Compliance_Description", "Statutory_Applicable_Status", "Statutory_remarks", "Compliance_Applicable_Status" ] # check duplicate compliance for same unit in csv def check_compliance_task_name_duplicate(self, data): domain_name = data.get("Domain") unit_code = data.get("Unit_Code") statutory_provision = data.get("Statutory_Provision") task_name = data.get("Compliance_Task") compliance_description = data.get("Compliance_Description") p_legislation = data.get("Primary_Legislation") s_legislation = data.get("Secondary_Legislation") l_entity = data.get("Legal_Entity") res = self._db.call_proc("sp_check_duplicate_compliance_for_unit", [ domain_name, unit_code, statutory_provision, task_name, compliance_description, p_legislation, s_legislation, l_entity ]) if len(res) > 0: return False else: return True # check duplicate compliance in already existing knowledge table def check_compliance_task_name_duplicate_in_knowledge( self, data, country_id): domain_name = data.get("Domain") unit_code = data.get("Unit_Code") statutory_provision = data.get("Statutory_Provision") task_name = data.get("Compliance_Task") compliance_description = data.get("Compliance_Description") p_legislation = data.get("Primary_Legislation") s_legislation = data.get("Secondary_Legislation") unit_id = self._unit_code.get(unit_code).get("unit_id") domain_id = self._domain.get(domain_name).get("domain_id") c_ids = self._source_db.call_proc("sp_bu_get_compliance_id_by_name", [ task_name, compliance_description, statutory_provision, country_id, domain_id, p_legislation, s_legislation ]) comp_id = c_ids[0]["compliance_id"] res = self._source_db.call_proc( "sp_bu_check_duplicate_compliance_for_unit", [domain_id, unit_id, comp_id]) if len(res) > 0: return False else: return True # save domain executive notification message def save_executive_message(self, a_type, csv_name, clientgroup, legalentity, createdby, unitids, reason, declined_count): admin_users_id = [] res = self._source_db.call_proc("sp_users_under_user_category", (1, )) for user in res: admin_users_id.append(user["user_id"]) domain_users_id = [] res = self._source_db.call_proc("sp_bu_user_by_unit_ids", (8, unitids)) for user in res: domain_users_id.append(user["user_id"]) if a_type == 1: action_type = "approved" else: action_type = "rejected with following reason %s" % (reason) if declined_count > 0: action_type = "declined" declined_text = "%s records" % (declined_count) msg = "Assign statutory file %s of %s - %s %s has been %s" % ( csv_name, clientgroup, legalentity, declined_text, action_type) else: msg = "Assign statutory file %s of %s - %s has been %s" % ( csv_name, clientgroup, legalentity, action_type) if len(domain_users_id) > 0: self._source_db.save_toast_messages( 8, "Approve Assign Statutory Bulk Upload", msg, None, domain_users_id, createdby) if len(admin_users_id) > 0: self._source_db.save_toast_messages( 1, "Approve Assign Statutory Bulk Upload", msg, None, admin_users_id, createdby) self._source_db.save_activity(createdby, frmApproveAssignStatutoryBulkUpload, msg) # save domain manager notification message def save_manager_message(self, csv_name, domainname, unitname, createdby, unitids): admin_users_id = [] res = self._source_db.call_proc("sp_users_under_user_category", (1, )) for user in res: admin_users_id.append(user["user_id"]) domain_users_id = [] res = self._source_db.call_proc("sp_bu_user_by_unit_ids", (7, unitids)) for user in res: domain_users_id.append(user["user_id"]) msg = "Assign statutory file %s of %s - %s uploaded for your %s " % ( csv_name, unitname, domainname, 'approval') if len(domain_users_id) > 0: self._source_db.save_toast_messages( 7, "Assign Statutory Bulk Upload", msg, None, domain_users_id, createdby) if len(admin_users_id) > 0: self._source_db.save_toast_messages( 1, "Assign Statutory Bulk Upload", msg, None, admin_users_id, createdby) self._source_db.save_activity(createdby, frmAssignStatutoryBulkUpload, msg) # get country_id and legal_entity_id by legal_entity_name def get_init_info(self): client_id = 0 country_id = 0 legal_entity_id = 0 for k, v in groupby( self._source_data, key=lambda s: (s["Client_Group"], s["Country"], s["Legal_Entity"])): grouped_list = list(v) group_name = grouped_list[0].get("Client_Group") country_name = grouped_list[0].get("Country") legal_entity_name = grouped_list[0].get("Legal_Entity") group_result = self._source_db.call_proc( "sp_bu_get_group_id_by_name", [group_name]) if len(group_result) > 0: client_id = group_result[0]["client_id"] country_result = self._source_db.call_proc( "sp_bu_get_country_id_by_name", [country_name]) if len(country_result) > 0: country_id = country_result[0]["country_id"] le_result = self._source_db.call_proc( "sp_bu_get_legal_entity_id_by_name", [client_id, country_id, legal_entity_name]) if len(le_result) > 0: legal_entity_id = le_result[0]["legal_entity_id"] return client_id, country_id, legal_entity_id # commit database after execute query def source_commit(self): self._source_db.commit()