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