def connectClientDB(le_id): try: _source_knowledge_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, ) query = "select t1.client_database_id, t1.database_name, t1.database_username, t1.database_password, t3.database_ip, database_port from tbl_client_database_info as t1 inner join tbl_client_database as t2 on t2.client_database_id = t1.client_database_id inner join tbl_database_server as t3 on t3.database_server_id = t2.database_server_id where t1.db_owner_id = %s and t1.is_group = 0;" param = [le_id] _source_knowledge_db = Database(_source_knowledge_db_con) _source_knowledge_db.begin() result = _source_knowledge_db.select_all(query, param) print "Result---->>> ", result if len(result) > 0: for row in result: dhost = row["database_ip"] uname = row["database_username"] pwd = row["database_password"] port = row["database_port"] db_name = row["database_name"] _source_db_con = mysql.connector.connect( user=uname, password=pwd, host=dhost, database=db_name, port=port, autocommit=False, ) print "source db con >>>>> ", _source_db_con _source_client_db = Database(_source_db_con) _source_client_db.begin() return _source_client_db except Exception, e: print "Connection Exception Caught" print e
def get_file_stats(csvid): file_status = None c_db_con = bulkupload_db_connect() _db_check = Database(c_db_con) try: _db_check.begin() # data = _db_check.call_proc( # "sp_pastdata_get_file_download_status", [csvid] # ) query = "select file_download_status from " \ "tbl_bulk_past_data_csv where csv_past_id = %s" param = [csvid] data = _db_check.select_all(query, param) print "DAta -> ", data if len(data) > 0: file_status = data[0].get("file_download_status") except Exception, e: print e _db_check.rollback()
class SourceDB(object): def __init__(self): self._source_db = None self._source_db_con = None self._knowledge_db = None self._knowledge_db_con = None # self.Client_Group = {} self.Legal_Entity = {} self.Domain = {} self.Unit_Code = {} self.Unit_Name = {} self.Statutories = {} self.Compliance_Task = {} self.Compliance_Description = {} self.Compliance_Frequency = {} self.Assignee = {} # self.connect_source_db() self._validation_method_maps = {} self.statusCheckMethods() self._csv_column_name = [] self.csv_column_fields() self._doc_names = [] # self.get_doc_names() def connect_source_db(self, legal_entity_id): self._knowledge_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._knowledge_db = Database(self._knowledge_db_con) self._knowledge_db.begin() query = "select t1.client_database_id, t1.database_name, t1.database_username, t1.database_password, t3.database_ip, database_port from tbl_client_database_info as t1 inner join tbl_client_database as t2 on t2.client_database_id = t1.client_database_id inner join tbl_database_server as t3 on t3.database_server_id = t2.database_server_id where t1.db_owner_id = %s and t1.is_group = 0;" param = [legal_entity_id] result = self._knowledge_db.select_all(query, param) if len(result) > 0: for row in result: dhost = row["database_ip"] uname = row["database_username"] pwd = row["database_password"] port = row["database_port"] db_name = row["database_name"] self._source_db_con = mysql.connector.connect( user=uname, password=pwd, host=dhost, database=db_name, port=port, autocommit=False, ) # self._source_db_con = mysql.connector.connect( # user=KNOWLEDGE_DB_USERNAME, # password=KNOWLEDGE_DB_PASSWORD, # host=KNOWLEDGE_DB_HOST, # database="compfie_le_att_1", # 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, legal_entity_id): self.connect_source_db(legal_entity_id) self.get_legal_entities() self.get_domains() self.get_unit_code() self.get_unit_name() self.get_primary_legislation() self.get_secondary_legislation() self.get_compliance_task() self.get_compliance_description() self.get_compliance_frequency() self.get_assignee() def get_legal_entities(self): query = "SELECT legal_entity_id, legal_entity_name, is_closed FROM tbl_legal_entities;" rows = self._source_db.select_all(query) for d in rows : self.Legal_Entity[d["legal_entity_name"]] = d def get_domains(self): query = "SELECT domain_id, domain_name, is_active FROM tbl_domains" rows = self._source_db.select_all(query) for d in rows : self.Domain[d["domain_name"]] = d def get_unit_code(self): query = "SELECT unit_id, client_id, legal_entity_id, unit_code, unit_name, is_closed FROM tbl_units" rows = self._source_db.select_all(query) for d in rows: self.Unit_Code[d["unit_code"]] = d def get_unit_name(self): query = "SELECT unit_id, client_id, legal_entity_id, unit_code, unit_name, is_closed FROM tbl_units" rows = self._source_db.select_all(query) for d in rows: self.Unit_Name[d["unit_name"]] = d def get_primary_legislation(self): query = "select trim(SUBSTRING_INDEX(SUBSTRING_INDEX((TRIM(TRAILING '\"]' FROM TRIM(LEADING '[\"' FROM t.statutory_mapping))),'>>',1),'>>',- 1)) AS primary_legislation, trim(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(TRIM(TRAILING '\"]' FROM TRIM(LEADING '[\"' FROM t.statutory_mapping)),'>>'),'>>',2),'>>',- 1)) AS secondary_legislation from tbl_compliances t" rows = self._source_db.select_all(query) for d in rows: self.Statutories[d["primary_legislation"]] = d def get_secondary_legislation(self): query = "select trim(SUBSTRING_INDEX(SUBSTRING_INDEX((TRIM(TRAILING '\"]' FROM TRIM(LEADING '[\"' FROM t.statutory_mapping))),'>>',1),'>>',- 1)) AS primary_legislation, trim(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(TRIM(TRAILING '\"]' FROM TRIM(LEADING '[\"' FROM t.statutory_mapping)),'>>'),'>>',2),'>>',- 1)) AS secondary_legislation from tbl_compliances t;" rows = self._source_db.select_all(query) for d in rows: self.Statutories[d["secondary_legislation"]] = d def get_compliance_task(self): query = "SELECT compliance_id, statutory_provision, case when ifnull(document_name,'') = '' then trim(compliance_task) else trim(Concat_ws(' - ',document_name, compliance_task)) end AS compliance_task, compliance_description, is_active from tbl_compliances" rows = self._source_db.select_all(query) for d in rows: self.Compliance_Task[d["compliance_task"]] = d def get_compliance_description(self): query = "SELECT compliance_id, statutory_provision, compliance_task, compliance_description, is_active from tbl_compliances" rows = self._source_db.select_all(query) for d in rows: self.Compliance_Description[d["compliance_description"]] = d def get_compliance_frequency(self): query = "select frequency_id, frequency from tbl_compliance_frequency " + \ " where frequency_id in (2,3)" rows = self._source_db.select_all(query) for d in rows: self.Compliance_Frequency[d["frequency"]] = d def get_assignee(self): query = "SELECT Distinct assignee as ID, employee_code, employee_name, " + \ " CONCAT_WS(' - ', employee_code, employee_name) As Assignee " + \ " FROM tbl_assign_compliances ac INNER JOIN tbl_users u ON (ac.assignee = u.user_id)" rows = self._source_db.select_all(query) for d in rows: self.Assignee[d["Assignee"]] = 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" elif status_name == "is_closed" : if data.get("is_closed") == 0 : return "Status Inactive" return True def return_unit_domain_id(self, domain_name, unit_name): query = "SELECT domain_id from tbl_domains " + \ "where domain_name = '%s'" % domain_name rows = self._source_db.select_all(query) domain_id = rows[0]["domain_id"] query = "SELECT unit_id from tbl_units " + \ "where unit_name = '%s'" % unit_name rows = self._source_db.select_all(query) unit_id = rows[0]["unit_id"] return unit_id, domain_id def check_due_date( self, due_date, domain_name, unit_name, level_1_statutory_name ): (unit_id, domain_id) = self.return_unit_domain_id( domain_name, unit_name) rows = return_past_due_dates( self._source_db, domain_id, unit_id, level_1_statutory_name ) print "rows: %s" % rows due_dates = calculate_final_due_dates( self._source_db, rows, domain_id, unit_id ) due_date = datetime.datetime.strptime(due_date, "%d-%b-%Y") due_date = due_date.date().strftime("%Y-%m-%d") if due_date in due_dates[0]: return True else: return "Not Found" def check_completion_date( self, completion_date, statutory_date, due_date ): statu_array = statutory_date.split() trigger_before_days_string = statu_array[len(statu_array)-1] trigger_before_days = int( trigger_before_days_string.strip(")(") ) due_date = datetime.datetime.strptime( due_date, "%d-%b-%Y") start_date = due_date.date() - timedelta(days=trigger_before_days) completion_date = datetime.datetime.strptime( completion_date, "%d-%b-%Y") if completion_date.date() < start_date: return "Should be greater than Start Date" else: return True # def check_client_group(self, group_name): # return self.check_base(True, self.Client_Group, group_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_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_primary_legislation(self, statutories): return self.check_base(False, self.Statutories, statutories, None) def check_secondary_legislation(self, statutories): return self.check_base(False, self.Statutories, statutories, 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(True, self.Compliance_Description, compliance_description, None) def check_frequency(self, frequency): return self.check_base(False, self.Compliance_Frequency, frequency, None) def check_assignee(self, assignee): return self.check_base(False, self.Assignee, assignee, None) def is_two_levels_of_approval(_source_db): query = "SELECT two_levels_of_approval FROM tbl_reminder_settings" rows = _source_db.select_all(query) return bool(rows[0]["two_levels_of_approval"]) def save_completed_task_data(self, data, legal_entity_id, session_user): # self.connect_source_db(legal_entity_id) is_two_level = False compliance_id = "" unit_id = "" # created_on = get_date_time() # "documents", columns = [] # columns = [ # "legal_entity_id", "unit_id", "compliance_id", "start_date", # "due_date", "completion_date", "completed_by", # "completed_on", # "approve_status", "approved_by", "approved_on", "current_status" # ] values = [] for idx, d in enumerate(data): self.connect_source_db(legal_entity_id) columns = [ "legal_entity_id", "unit_id", "compliance_id", "start_date", "due_date", "completion_date", "completed_by", "completed_on", "approve_status", "approved_by", "approved_on", "current_status" ] # Compliance ID cName = [d["compliance_task_name"], d["compliance_task_name"], d["compliance_description"]] q = "SELECT compliance_id FROM tbl_compliances where " + \ " case when ifnull(document_name,'') = '' then compliance_task = TRIM(%s) " + \ " else concat(document_name,' - ',compliance_task) = " + \ " TRIM(%s) end AND compliance_description = TRIM(%s) LIMIT 1 " compliance_id = self._source_db.select_all(q, cName) compliance_id = compliance_id[0]["compliance_id"] completion_date = d["completion_date"] # Unit ID unitCode = [d["unit_code"]] print "unitCode >>>> ", unitCode q = "select unit_id from tbl_units where unit_code = TRIM(%s)" unit_id = self._source_db.select_all(q, unitCode) print "unit_id ->> ", unit_id unit_id = unit_id[0]["unit_id"] # assignee_id assignee = [d["assignee"]] q = " SELECT distinct ac.assignee as ID, u.employee_code, " + \ " u.employee_name, " + \ " CONCAT_WS(' - ', u.employee_code, u.employee_name) As Assignee " + \ " FROM tbl_assign_compliances ac INNER JOIN tbl_users u " + \ " ON (ac.assignee = u.user_id) where " + \ " CONCAT_WS(' - ', u.employee_code, u.employee_name)=TRIM(%s)" assignee_id = self._source_db.select_all(q, assignee) assignee_id = assignee_id[0]["ID"] #Check two level of approval query = "SELECT two_levels_of_approval FROM tbl_reminder_settings" rows = self._source_db.select_all(query) if int(rows[0]["two_levels_of_approval"]) == 1: is_two_level = True else: is_two_level = False # Getting Approval and Concurrence Persons concur_approve_columns = "approval_person, country_id, domain_id" if is_two_level: concur_approve_columns += ", concurrence_person" condition = "compliance_id = %s and unit_id = %s " tblAssignCompliances = "tbl_assign_compliances" rows = self._source_db.get_data( tblAssignCompliances, concur_approve_columns, condition, [compliance_id, unit_id] ) concurred_by = 0 approved_by = 0 if rows: approved_by = rows[0]["approval_person"] country_id = rows[0]["country_id"] domain_id = rows[0]["domain_id"] users = [assignee_id, approved_by] if is_two_level: concurred_by = rows[0]["concurrence_person"] users.append(concurred_by) values = [ legal_entity_id, unit_id, compliance_id, get_date_time(), d["due_date"], completion_date, assignee_id, completion_date, 1, approved_by, completion_date, 3] if d["document_name"] != "" : columns.append("documents") values.append(d["document_name"]) if is_two_level: columns.append("concurrence_status") columns.append("concurred_by") columns.append("concurred_on") values.append(1) values.append(concurred_by) values.append(completion_date) if values : self._source_db.insert("tbl_compliance_history", columns, values) # added for aparajtha # clienttransaction.update_user_wise_task_status(self._source_db, users) self._source_db.commit() return True # main db related validation mapped with field name def statusCheckMethods(self): self._validation_method_maps = { "Legal_Entity": self.check_legal_entity, "Domain": self.check_domain, "Unit_Code": self.check_unit_code, "Unit_Name": self.check_unit_name, "Primary_Legislation": self.check_primary_legislation, "Secondary_Legislation": self.check_secondary_legislation, "Compliance_Task": self.check_compliance_task, "Compliance_Description": self.check_compliance_description, "Compliance_Frequency": self.check_frequency, "Assignee": self.check_assignee, "Due_Date": self.check_due_date, "Completion_Date": self.check_completion_date } def csv_column_fields(self): self._csv_column_name = [ "Legal_Entity", "Domain", "Unit_Code", "Unit_Name", "Primary_Legislation", "Secondary_Legislation", "Compliance_Task", "Compliance_Description", "Compliance_Frequency", "Statutory_Date", "Due_Date", "Assignee", "Completion_Date", "Document_Name" ]