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