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()
class ClientdbConnect(object):
def __init__(self, db):
self._db = db
self._c_db = None
self._conn = None
self._cursor = None
def get_client_database(self):
columns = [
"database_ip", "database_port", "database_username",
"database_password", "database_name"
]
condition = "client_id = %s"
rows = self._db.get_data(
"tbl_client_database", columns, condition, [self._client_id]
)
if len(rows) == 0:
raise not_found_error("E061")
else:
return rows[0]
def get_client_connect(self):
rows = self.get_client_database()
host = rows["database_ip"]
port = rows["database_port"]
username = rows["database_username"]
password = rows["database_password"]
database = rows["database_name"]
self._c_db = Database(
host, port,
username, password,
database
)
self._c_db.connect()
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_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_domains_for_user_bu(db, user_id):
_source_db_con = connectKnowledgeDB()
_source_db = Database(_source_db_con)
_source_db.begin()
result = _source_db.call_proc_with_multiresult_set(
'sp_tbl_domains_for_user', (user_id, ), 3)
result.pop(0)
return return_domains_bu(result)
def get_knowledge_connect(self):
print(KNOWLEDGE_DB_HOST, KNOWLEDGE_DB_PORT, KNOWLEDGE_DB_USERNAME,
KNOWLEDGE_DB_PASSWORD, KNOWLEDGE_DATABASE_NAME)
conn = mysql.connect(host=KNOWLEDGE_DB_HOST,
port=KNOWLEDGE_DB_PORT,
user=KNOWLEDGE_DB_USERNAME,
passwd=KNOWLEDGE_DB_PASSWORD,
db=KNOWLEDGE_DATABASE_NAME)
self._k_db = Database(conn)
def get_countries_for_user_bu(db, user_id):
_source_db_con = connectKnowledgeDB()
_source_db = Database(_source_db_con)
_source_db.begin()
result = _source_db.call_proc_with_multiresult_set("sp_countries_for_user",
[user_id], 2)
print "Result->> ", result
if len(result) > 1:
result = result[1]
return return_countries_bu(result)
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 get_client_connect(self, db_host, db_port, db_username, db_password,
db_name):
if db_host is None:
return
conn = mysql.connect(host=db_host,
port=db_port,
user=db_username,
passwd=db_password,
db=db_name)
print conn
self._k_db = Database(conn)
def get_client_connect(self):
rows = self.get_client_database()
host = rows["database_ip"]
port = rows["database_port"]
username = rows["database_username"]
password = rows["database_password"]
database = rows["database_name"]
self._c_db = Database(
host, port,
username, password,
database
)
self._c_db.connect()
def formulate_auto_deletion_data(request, client_id):
le_name = request.legal_entity_id
info = request.formulate_info.decode('base64')
info = json.loads(info)
deletion_info = request.extra_details.decode('base64')
deletion_info = json.loads(deletion_info)
unique_id = request.unique_code
try :
db_cons = Database.make_connection(
info["uname"], info["password"],
info["db_name"], info["ip_address"], info["ip_port"]
)
db = Database(db_cons)
db.begin()
result, deletion_date = db.perform_auto_deletion(le_name, deletion_info, unique_id)
print result, deletion_date
if result :
return fileprotocol.FormulateDownloadSuccess(str(deletion_date))
else :
return fileprotocol.FormulateDownloadFailed()
finally :
db.close()
db_cons.close()
def connect_knowledge_db():
try:
_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,
)
_source_db = Database(_source_db_con)
_source_db.begin()
return _source_db_con
except Exception, e:
print e
class KnowledgedbConnect(object):
def __init__(self):
self._k_db = None
def get_knowledge_connect(self):
print(KNOWLEDGE_DB_HOST, KNOWLEDGE_DB_PORT, KNOWLEDGE_DB_USERNAME,
KNOWLEDGE_DB_PASSWORD, KNOWLEDGE_DATABASE_NAME)
conn = mysql.connect(host=KNOWLEDGE_DB_HOST,
port=KNOWLEDGE_DB_PORT,
user=KNOWLEDGE_DB_USERNAME,
passwd=KNOWLEDGE_DB_PASSWORD,
db=KNOWLEDGE_DATABASE_NAME)
self._k_db = Database(conn)
def get_knowledge_close(self):
if self._k_db is not None:
self._k_db.close()
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()
class ClientdbConect(object):
def __init__(self):
self._k_db = None
def get_client_connect(self, db_host, db_port, db_username, db_password,
db_name):
if db_host is None:
return
conn = mysql.connect(host=db_host,
port=db_port,
user=db_username,
passwd=db_password,
db=db_name)
print conn
self._k_db = Database(conn)
def get_client_close(self):
if self._k_db is not None:
self._k_db.close()
def on_session_timeout():
print "session timeout"
_db_con_clr = before_first_request()
_db_clr = Database(_db_con_clr)
_db_clr.begin()
try:
_db_clr.clear_session(SESSION_CUTOFF)
_db_clr.commit()
_db_con_clr.close()
t = threading.Timer(500, on_session_timeout)
t.daemon = True
t.start()
except Exception, e:
logger.logKnowledge("error", "remove_old_session", str(e))
logger.logKnowledge("error", "remove_old_session",
str(traceback.format_exc()))
_db_clr.rollback()
def get_cliens_for_client_unit_bulk_upload(db, session_user):
_source_db_con = connect_knowledge_db()
_source_db = Database(_source_db_con)
_source_db.begin()
groups = _source_db.call_proc_with_multiresult_set(
"sp_client_groups_for_client_unit_bulk_upload", (session_user, ), 2)
_source_db.close()
print "groups"
print groups
return return_client_group(groups[1])
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_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
def process_contract_download(request, client_id):
le_name = request.legal_entity_id
info = request.formulate_info.decode('base64')
info = json.loads(info)
try :
db_cons = Database.make_connection(
info["uname"], info["password"],
info["db_name"], info["ip_address"], info["ip_port"]
)
db = Database(db_cons)
db.begin()
if db.perform_export(le_name) :
return fileprotocol.FormulateDownloadSuccess("")
else :
return fileprotocol.FormulateDownloadFailed()
finally :
db.close()
db_cons.close()
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()
def handle_api_request(self, unbound_method, request_data_type,
need_session_id):
is_bulk_upload = False
# self._ip_addess = request.remote_addr
self._ip_addess = request.headers.get("X-Real-Ip")
caller_name = request.headers.get("Caller-Name")
api_type = request.url
try:
if request_data_type == "knowledgeformat":
request_data = request
else:
request_data = self._parse_request(request_data_type)
if request_data is None:
raise ValueError("Request data is Null")
elif type(request_data) is str:
raise ValueError(request_data)
if "/api/bu" in api_type:
is_bulk_upload = True
_db_con = bulk_db_connect()
else:
_db_con = before_first_request()
_db = Database(_db_con)
_db.begin()
valid_session_data = None
session_user = None
_session_db = None
_session_db_con = None
print is_bulk_upload
if is_bulk_upload is True:
_session_db_con = before_first_request()
_session_db = Database(_session_db_con)
_session_db.begin()
valid_session_data, session_user = self.validate_user_rights(
request_data, _session_db, caller_name)
if valid_session_data is None:
session_user = UserInfo(_session_db, session_user)
_session_db.commit()
else:
valid_session_data, session_user = self.validate_user_rights(
request_data, _db, caller_name)
if valid_session_data is None:
if need_session_id is True:
response_data = unbound_method(self, request_data, _db,
session_user)
else:
response_data = unbound_method(self, request_data, _db)
else:
response_data = valid_session_data
if response_data is None or type(response_data) is bool:
_db.rollback()
raise fetch_error()
elif type(response_data) != technomasters.ClientCreationFailed:
_db.commit()
else:
_db.rollback()
_db.close()
_db_con.close()
return self.respond(response_data)
except Exception, e:
logger.logKnowledge("error", "handle_api_request", str(e))
logger.logKnowledge("error", "handle_api_request",
str(traceback.format_exc()))
if str(e).find("expected a") is False:
_db.rollback()
_db.close()
_db_con.close()
return self._send_response(str(e), 400)
def _initiate_connction(self):
con = Database.make_connection(self._group_db_info)
_db = Database(con)
return _db
def _initiate_connection(self, connection_param):
con = Database.make_connection(connection_param)
_db = Database(con)
return _db
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()
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"
]
