def save_allocated_db_env(db, request, session_user):
is_valid = validated_env_before_save(db, request)
if is_valid is not True:
raise fetch_run_error(is_valid)
client_id = request.client_id
legal_entity_id = request.legal_entity_id
client_db_id = request.client_database_id
print client_db_id
db_server_id = request.db_server_id
machine_id = request.machine_id
le_db_server_id = request.le_db_server_id
file_server_id = request.file_server_id
client_ids = request.console_cl_ids
legal_entity_ids = request.console_le_ids
f_legal_entity_ids = request.console_f_le_ids
le_legal_entity_ids = request.console_le_le_ids
print "new"
print request.new_le_le_ids
data = db.call_proc("sp_legal_entity_name_by_id", (legal_entity_id,))
#
# To save allocated database environment
# Parameters : client id, legal entity id, database ip, client server id
# Return : List of allocated database environment details
#
# try:
#
# To get legal entity name by it's id to save activity
# Parameters : legal entity id
# Return : Returns legal entity name
#
print "legal_entity_id : %s" % legal_entity_id
data = db.call_proc("sp_legal_entity_name_by_id", (legal_entity_id,))
print "data: %s" % data
if client_db_id is None:
try :
client_db_id = db.call_insert_proc(
"sp_clientdatabase_save",
(client_id, legal_entity_id, machine_id, db_server_id, le_db_server_id, file_server_id,
client_ids, legal_entity_ids, session_user, get_date_time())
)
create_db_process(db, client_db_id, client_id, legal_entity_id, db_server_id, le_db_server_id)
# Notification Message
current_time_stamp = str(get_date_time())
db.call_insert_proc("sp_allocate_server_message_save", (session_user, "Save", client_id, legal_entity_id, data[0]["legal_entity_name"], current_time_stamp))
except Exception, e :
print e
raise Exception(e)
def save_division_category(db, request, session_user):
print "save division"
print request.division_category
for div_catg in request.division_category:
div_result = update_division(db, div_catg.client_id,
div_catg.division_id,
div_catg.division_name,
div_catg.business_group_id,
div_catg.legal_entity_id, session_user)
if div_catg.cg.split("|")[0].find("--") >= 0:
catg_result = True
category_id = None
else:
category_id = div_catg.cg.split("|")[1]
category_name = div_catg.cg.split("|")[0]
catg_result = update_category(db, div_catg.client_id,
div_catg.division_id, category_id,
div_catg.business_group_id,
div_catg.legal_entity_id,
category_name, session_user)
args = [
div_catg.client_id, div_catg.legal_entity_id, div_catg.division_id,
category_id, session_user,
str(get_date_time())
]
unit_status_update = db.call_update_proc("sp_tbl_units_update_status",
args)
if div_result is False or catg_result is False:
if div_result is False:
return technomasters.DivisionNameAlreadyExists()
elif catg_result is False:
return technomasters.CategoryNameAlreadyExists()
return technomasters.SaveDivisionCategorySuccess()
def save_industry(db, country_ids, domain_ids, industry_name, user_id):
# table_name = "tbl_industries"
created_on = get_date_time()
# columns = ["country_id", "domain_id", "industry_name", "created_by", "created_on"]
values = [
country_ids, domain_ids, industry_name,
str(user_id),
str(created_on)
]
new_id = db.call_insert_proc("sp_industry_master_saveindustry", values)
if new_id is False:
raise process_error("E001")
else:
msg_text = "Organization Name \"" + industry_name + "\" Added "
u_cg_id = [3, 5, 7]
for cg_id in u_cg_id:
users_id = []
result = db.call_proc("sp_users_under_user_category", (cg_id, ))
for user in result:
users_id.append(user["user_id"])
if len(users_id) > 0:
db.save_toast_messages(cg_id, "Organization Type Created",
msg_text, None, users_id, user_id)
action = "New Organization type %s added" % (industry_name)
db.save_activity(user_id, frmOrganizationMaster, action)
return True
def send_groupadmin_registration_mail(db, request, user_id):
group_mode = request.grp_mode
email_id = request.email_id
emp_name = request.username
group_name = request.group_name
current_time_stamp = get_date_time()
legal_entity_name = request.legal_entity_name
insert_result = False
try:
notify_grp_admin_thread = threading.Thread(
target=notify_grp_admin_mail,
args=[group_mode, email_id, group_name, legal_entity_name])
notify_grp_admin_thread.start()
result = db.call_insert_proc(
"sp_tbl_groupadmin_email_notification_insert",
(group_mode, int(request.client_id), int(request.legal_entity_id),
email_id, 1, current_time_stamp, int(user_id)))
if result > 0:
insert_result = True
if group_mode == "unit":
message_text = 'Registartion Email for unit has been sent successfully under the group - %s' % group_name
db.save_activity(user_id, frmGroupAdminRegistraionEmail,
message_text)
else:
message_text = 'Registartion Email for assigned statutory has been sent successfully under the group - %s' % group_name
db.save_activity(user_id, frmGroupAdminRegistraionEmail,
message_text)
except Exception, e:
print "Error with group admin registration"
print e
def save_client_server(db, request, session_user):
#
# To save client server
# Parameters : Client server id, Client server name, ip, port
# Return : returns last inserted id
#
# try:
is_valid = validate_application_server_before_save(request)
if is_valid is not True:
raise fetch_run_error(is_valid)
print "args"
print request.client_server_id, request.client_server_name, request.ip, request.port
new_id = db.call_insert_proc(
"sp_machines_save", (
request.client_server_id, request.client_server_name,
request.ip, request.port, session_user, get_date_time()
)
)
print "new_id"
print new_id
action = "New %s added" % (request.client_server_name)
print action
if request.client_server_id is not None:
action = "%s updated" % (request.client_server_name)
db.save_activity(session_user, frmConfigureApplicationServer, action)
def file_server_entry_process(db, request, user_id):
#
# To save client server
# Parameters : Client server id, Client server name, ip, port
# Return : returns last inserted id
#
# try:
is_valid = validate_file_server_before_save(request)
if is_valid is not True:
raise fetch_run_error(is_valid)
print "args"
print request.file_server_id, request.file_server_name, request.ip, request.port
new_id = db.call_insert_proc(
"sp_file_server_entry", (
request.file_server_id, request.file_server_name,
request.ip, request.port, user_id, get_date_time()
)
)
print "new_id"
print new_id
action = "New File Server %s added" % (request.file_server_name)
print action
if request.file_server_id is not None:
action = "File Server %s updated" % (request.file_server_name)
db.save_activity(user_id, frmConfigureFileServer, action)
def save_statutory_compliances(db, client_id, legal_entity_id, domain_id, data,
unit_id, status, user_id, csid):
value_list = []
for r in data:
if r.unit_id == unit_id:
remarks = r.remarks
if remarks is None:
remarks = ''
value_list.append(
(csid, client_id, legal_entity_id, unit_id, domain_id,
r.level_1_id, r.status, str(remarks),
r.compliance_id, r.compliance_status, status, user_id,
get_date_time(), status))
table = "tbl_client_compliances"
column = [
"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_approved"
]
update_column = [
"statutory_id", "statutory_applicable_status", "remarks",
"compliance_id", "compliance_applicable_status", "is_saved",
"saved_by", "saved_on", "is_approved"
]
result = db.on_duplicate_key_update(table, ",".join(column), value_list,
update_column)
if result is False:
raise process_error("E088")
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
def save_activity(
self,
user_id,
form_id,
action,
legal_entity_id=None,
unit_id=None,
):
if len(action) > 500:
action = action[0:500]
created_on = get_date_time()
# if legal_entity_id is None :
# legal_entity_id = ''
# if unit_id is None :
# unit_id = ''
tblUsers = "tbl_users"
column = ["user_category_id, client_id"]
condition_val = "user_id= %s" % user_id
rows = self.get_data(tblUsers, column, condition_val)
client_id = rows[0]["client_id"]
category_id = rows[0]["user_category_id"]
query = " INSERT INTO tbl_activity_log " + \
" (client_id, legal_entity_id, unit_id, user_category_id, " + \
" user_id, form_id, action, created_on) " + \
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s) "
values = [
client_id, legal_entity_id, unit_id, category_id, user_id, form_id,
action, created_on
]
self.execute(query, values)
return True
def update_assigned_statutory(db, client_statutory_id, client_id,
compliances_list, unit_ids, units,
level_1_statutory_compliance, submission_type,
session_user):
if (submission_type == "save"):
columns = [
"statutory_applicable_status", "compliance_applicable_status",
"is_saved", "saved_by", "saved_on"
]
else:
columns = [
"statutory_applicable_status", "compliance_applicable_status",
"is_submitted", "submitted_by", "submitted_on"
]
value_list = []
conditions = []
for compliances in compliances_list:
for unit_id in unit_ids:
compliance_id = compliances.compliance_id
compliance_applicable_status = compliances.compliance_applicability_status
statutory_applicable_status = compliances.statutory_applicability_status
value_list.append(
(statutory_applicable_status, compliance_applicable_status, 1,
session_user, get_date_time()))
conditions.append("client_statutory_id=%s and compliance_id=%s" %
(client_statutory_id, compliance_id))
db.bulk_update(tblClientCompliances, columns, value_list, conditions)
def save_unit_closure_data(self, db, user_id, unit_id, remarks, action_mode, le_id):
current_time_stamp = get_date_time()
self._closed_on = current_time_stamp
self._unit_id = unit_id
columns = ["is_closed", "closed_on", "closed_by", "closed_remarks"]
values = []
if action_mode == "close":
values = [1, current_time_stamp, user_id, remarks]
condition_val = "unit_id= %s"
values.append(unit_id)
result = db.update("tbl_units", columns, values, condition_val)
# notification log
self.save_units_notifications(db, unit_id, user_id, action_mode, remarks, le_id)
uce = UnitClosureExport(db, self._unit_id, self._closed_on)
export_link = uce.perform_export()
if export_link is not None :
self.save_in_group_db(uce, export_link)
elif action_mode == "reactive":
values = [0, current_time_stamp, user_id, remarks]
condition_val = "unit_id= %s"
values.append(unit_id)
result = db.update("tbl_units", columns, values, condition_val)
# notification log
self.save_units_notifications(db, unit_id, user_id, action_mode, remarks, le_id)
def save_login_failure(db, user_id, session_user_ip):
print "user_id:", user_id
print "session_user_ip", session_user_ip
result = db.call_proc_with_multiresult_set(
"sp_save_login_failure",
[user_id, session_user_ip, get_date_time()], 2)
print "line@234"
print "result: ", result
saved_info = result[1][0]
return saved_info
def save_messages(self, _db, c_id, le_id, msg):
created_on = get_date_time()
q1 = "INSERT INTO tbl_notifications_log (country_id, legal_entity_id, notification_type_id, " + \
"notification_text, extra_details, created_on) VALUES (%s, %s, 4, %s, %s, %s)"
try :
_db.execute(q1, [c_id, le_id, msg, self._extra_details, created_on])
qry = "select notification_id from tbl_notifications_log where legal_entity_id = %s and notification_type_id = 4 and extra_details = %s"
row = _db.select_one(qry, [self._le_id, self._extra_details])
notification_id = row["notification_id"]
q2 ="INSERT INTO tbl_notifications_user_log (notification_id, user_id, read_status, updated_on) " + \
"VALUES (%s, 1, 0, %s)"
_db.execute(q2, [notification_id, created_on])
except Exception, e :
print e
def save_activity(self, user_id, form_id, action):
if len(action) > 500:
action = action[0:500]
created_on = get_date_time()
q = "select user_category_id from tbl_user_login_details where user_id = %s"
row = self.select_one(q, [user_id])
user_cat_id = row.get("user_category_id")
query = " INSERT INTO tbl_activity_log " + \
" (user_category_id, user_id, form_id, action, created_on) " + \
" VALUES (%s, %s, %s, %s, %s) "
self.execute(query,
(user_cat_id, user_id, form_id, action, created_on))
return True
def save_toast_messages(self, user_cat_id, message_head, message_text,
link, users_to_notify, session_user):
created_on = get_date_time()
m1 = "INSERT INTO tbl_messages (user_category_id, message_heading, message_text, " + \
"link, created_by, created_on) values (%s, %s, %s, %s, %s, %s)"
msg_id = self.execute_insert(m1, [
user_cat_id, message_head, message_text, link, session_user,
created_on
])
if msg_id is False or msg_id == 0:
raise fetch_error()
m2 = "INSERT INTO tbl_message_users (message_id, user_id) values (%s, %s)"
for u in users_to_notify:
self.execute(m2, [msg_id, u])
return True
def save_geography(db, geography_level_id, geography_name, parent_ids,
parent_names, user_id):
created_on = get_date_time()
values = [
geography_name,
int(geography_level_id), parent_ids, parent_names,
int(user_id),
str(created_on)
]
new_id = db.call_insert_proc("sp_save_geography_master", values)
if new_id is False:
raise process_error("E012")
else:
action = "Geography name %s added " % (geography_name)
notify_geography_actions(db, action, geography_level_id, parent_ids,
parent_names, 0, [3, 5, 7], user_id)
return True
def save_statutory(db, name, level_id, parent_ids, parent_names, user_id):
table_name = "tbl_statutories"
created_on = get_date_time()
columns = [
"statutory_name", "level_id", "parent_ids", "parent_names",
"created_by", "created_on"
]
values = [
name,
int(level_id), parent_ids, parent_names,
int(user_id),
str(created_on)
]
new_id = db.insert(table_name, columns, values)
if new_id is False:
raise process_error("E015")
else:
action = "Statutory - %s added" % name
db.save_activity(user_id, frmStatutoryMapping, action)
return True
def save_db_server(db, request, session_user):
#
# To save database server
# Parameters : Db server name, ip, port, username, password
# Return : returns last inserted row id
#
# try:
is_valid = validate_database_server_before_save(db, request)
if is_valid is not True:
raise fetch_run_error(is_valid)
db.call_insert_proc(
"sp_databaseserver_save", (
request.db_server_id, request.db_server_name, request.ip,
request.port, request.username, request.password, session_user,
get_date_time()
)
)
if request.db_server_id is None:
action = "New Database server %s added" % (request.db_server_name)
else:
action = "Database server %s updated" % (request.db_server_name)
db.save_activity(session_user, frmConfigureDatabaseServer, action)
def save_statutory_nature(db, nature_name, country_id, user_id):
created_on = get_date_time()
# columns = ["statutory_nature_name", "country_id", "created_by", "created_on"]
values = [nature_name, country_id, user_id, str(created_on)]
new_id = db.call_insert_proc("sp_statutorynature_savestatutorynature",
values)
if new_id is False:
raise process_error("E004")
else:
msg_text = "Statutory Nature \"" + nature_name + "\" Added "
u_cg_id = [3, 4, 5, 6, 7]
for cg_id in u_cg_id:
users_id = []
result = db.call_proc("sp_users_under_user_category", (cg_id, ))
for user in result:
users_id.append(user["user_id"])
if len(users_id) > 0:
db.save_toast_messages(cg_id, "Statutory Nature Created",
msg_text, None, users_id, user_id)
action = "New Statutory Nature %s added" % (nature_name)
db.save_activity(user_id, frmStatutoryNatureMaster, action)
return True
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
def make_rejection(self, declined_info, user_id):
try:
created_on = get_date_time()
count = len(declined_info.keys())
for k, v in declined_info.items():
remarks = ",".join(v)
q = "update tbl_bulk_assign_statutory set " + \
"action = 3, remarks = %s where " + \
"bulk_assign_statutory_id = %s"
self._db.execute(q, [remarks, k])
q1 = "update tbl_bulk_assign_statutory_csv set " + \
" declined_count = %s, approve_status = 1, " + \
" approved_by = %s, approved_on = %s, " + \
" total_rejected_records = (select count(0) from " + \
" tbl_bulk_assign_statutory as t WHERE t.action = 2 and " + \
" t.csv_assign_statutory_id = %s) WHERE " + \
" csv_assign_statutory_id = %s"
self._db.execute(
q1, [count, user_id, created_on, self._csv_id, self._csv_id])
except Exception, e:
raise (e)
def save_client_statutories(db, request, user_id):
status = request.submission_type
client_id = request.client_id
legal_entity_id = request.legal_entity_id
domain_name = request.domain_name
domain_id = request.domain_id
unit_ids = request.unit_ids
le_name = request.legal_entity_name
bg_name = request.b_grp_name
group_name = get_group_by_id(db, client_id)
admin_users_id = []
res = db.call_proc("sp_users_under_user_category", (1, ))
for user in res:
admin_users_id.append(user["user_id"])
extra_text = ''
if bg_name is not None:
extra_text = group_name + ' group, ' + bg_name + ' business group, ' + le_name + ' legal entity, '
else:
extra_text = group_name + ' group, ' + le_name + ' legal entity, '
is_update = True
comps = request.compliances_applicablity_status
q = "INSERT INTO tbl_client_statutories(client_id, unit_id, domain_id, status)" + \
" values (%s, %s, %s, %s)"
saved_unit = []
for c in comps:
# print c.client_statutory_id
if c.unit_id in saved_unit:
continue
if c.client_statutory_id is None:
is_update = False
csid = db.execute_insert(q,
[client_id, c.unit_id, domain_id, status])
if csid is False:
raise process_error("E088")
else:
# db.execute(q1_update, [status, c.client_statutory_id])
csid = c.client_statutory_id
saved_unit.append(c.unit_id)
save_statutory_compliances(db, client_id, legal_entity_id, domain_id,
comps, c.unit_id, status, user_id, csid)
unit_name = c.unit_name
if status == 2:
domain_users_id = []
res = db.call_proc("sp_user_by_unit_id", (7, c.unit_id))
for user in res:
domain_users_id.append(user["user_id"])
if is_update == True:
msg = "Statutes are updated for the Unit %s in %s %s domain " % (
unit_name, extra_text, domain_name)
else:
msg = "Statutes are assigned for the Unit %s in %s %s domain " % (
unit_name, extra_text, domain_name)
if len(domain_users_id) > 0:
db.save_toast_messages(7, "Assign Statutory", msg, None,
domain_users_id, user_id)
if len(admin_users_id) > 0:
db.save_toast_messages(1, "Assign Statutory", msg, None,
admin_users_id, user_id)
db.save_activity(user_id, frmAssignStatutory, msg)
if status == 1:
for u in unit_ids:
q1_cs_update = "UPDATE tbl_client_statutories set status = %s where " + \
" client_statutory_id in ( select client_statutory_id from tbl_client_compliances " + \
" where unit_id = %s and domain_id = %s )"
db.execute(q1_cs_update, [status, u, domain_id])
if status == 2:
for u in unit_ids:
q1_cs_update = "UPDATE tbl_client_statutories set status = %s where " + \
" client_statutory_id in ( select client_statutory_id from tbl_client_compliances " + \
" where unit_id = %s and domain_id = %s )"
db.execute(q1_cs_update, [status, u, domain_id])
q1_cc_update = "UPDATE tbl_client_compliances set is_approved = %s, submitted_by = %s, submitted_on = %s where is_approved NOT IN (3, 5) and unit_id = %s and domain_id = %s"
db.execute(q1_cc_update,
[status, user_id,
get_date_time(), u, domain_id])
return True
def save_approve_statutories(db, request, user_id):
unit_id = request.unit_id
domain_id = request.domain_id
client_statutory_id = request.client_statutory_id
compliance_ids = request.compliance_ids
s_s = request.submission_type
reason = request.remarks
unit_name = request.unit_name
domain_name = request.domain_name
# 3 : approve 4: reject
group_name = request.group_name
le_name = request.legal_entity_name
bg_name = request.business_group_name
extra_text = ''
if bg_name is not None:
extra_text = group_name + ' group, ' + bg_name + ' business group, ' + le_name + ' legal entity, '
else:
extra_text = group_name + ' group, ' + le_name + ' legal entity, '
admin_users_id = []
res = db.call_proc("sp_users_under_user_category", (1, ))
for user in res:
admin_users_id.append(user["user_id"])
domain_users_id = []
res = db.call_proc("sp_user_by_unit_id", (8, unit_id))
for user in res:
domain_users_id.append(user["user_id"])
if s_s not in (3, 4):
raise process_edrror("E089")
if reason is None:
reason = ''
q = "update tbl_client_statutories set reason = %s, status = %s where" + \
" unit_id = %s and client_statutory_id = %s and domain_id = %s"
params = [reason, s_s, unit_id, client_statutory_id, domain_id]
db.execute(q, params)
if s_s == 4:
for c in compliance_ids:
# reject selected compliances
q1 = "UPDATE tbl_client_compliances set is_approved=%s, approved_by=%s, "+ \
" approved_on=%s" + \
" where unit_id = %s and domain_id = %s and compliance_id = %s"
db.execute(q1,
[4, user_id,
get_date_time(), unit_id, domain_id, c])
msg = "Assgined statutories has been rejected for unit %s in %s %s domain with following reason %s" % (
unit_name, extra_text, domain_name, reason)
else:
# is_approve = 5 when the compliance is applicable or not applicable
q1 = "UPDATE tbl_client_compliances set is_submitted=%s, approved_by=%s, " + \
" approved_on=%s, is_approved=%s where compliance_applicable_status != 3 " + \
" and client_statutory_id = %s"
db.execute(q1, [1, user_id, get_date_time(), 5, client_statutory_id])
# is_approve = 3 when the compliance is not at all applicable because it has to reshow domain users
q1 = "UPDATE tbl_client_compliances set is_approved=%s, " + \
" approved_by=%s, approved_on=%s where " + \
" compliance_applicable_status = 3 and client_statutory_id = %s"
db.execute(q1, [3, user_id, get_date_time(), client_statutory_id])
msg = "Assgined statutories has been approved for unit %s in %s %s domain " % (
unit_name, extra_text, domain_name)
if len(domain_users_id) > 0:
db.save_toast_messages(8, "Approve Assigned Statutory", msg, None,
domain_users_id, user_id)
if len(admin_users_id) > 0:
db.save_toast_messages(1, "Approve Assigned Statutory", msg, None,
admin_users_id, user_id)
db.save_activity(user_id, frmApproveAssignedStatutory, msg)
return True
def save_geography_levels(db, country_id, levels, insertValText, user_id):
table_name = "tbl_geography_levels"
created_on = get_date_time()
newlist = sorted(levels, key=lambda k: k.level_position, reverse=True)
result = False
d_l_id = None
for n in newlist:
if n.is_remove is True:
result = delete_grography_level(db, n.level_id)
if result:
d_l_id = n.level_position
break
else:
continue
if result:
return knowledgemaster.LevelShouldNotbeEmpty(d_l_id)
for level in sorted(levels, key=lambda k: k.level_id, reverse=True):
name = level.level_name
position = level.level_position
if level.is_remove:
continue
if level.level_id is not None:
values = [level.level_id, name, position, user_id]
if (db.call_update_proc("sp_update_geographylevel_master",
values)):
action = "Geography levels updated"
db.save_activity(user_id, frmGeographyLevelMaster, action)
else:
raise process_error("E011")
else:
values = [
name, position,
int(country_id),
int(user_id),
str(created_on)
]
new_id = db.call_insert_proc("sp_save_geographylevel_master",
values)
if new_id is not False:
action = "New Geography levels added"
db.save_activity(user_id, frmGeographyLevelMaster, action)
else:
raise process_error("E010")
if insertValText is not None:
if insertValText.find(",") >= 0:
u_cg_id = [3, 4, 5, 6]
for cg_id in u_cg_id:
users_id = []
result = db.call_proc("sp_users_under_user_category",
(cg_id, ))
for user in result:
users_id.append(user["user_id"])
if len(users_id) > 0:
split_text = insertValText.split(",")
for s_text in split_text:
if s_text is not None and s_text != "null":
db.save_toast_messages(cg_id,
"Geography Level Inserted",
s_text, None, users_id,
user_id)
else:
u_cg_id = [3, 4, 5, 6]
for cg_id in u_cg_id:
users_id = []
result = db.call_proc("sp_users_under_user_category",
(cg_id, ))
for user in result:
users_id.append(user["user_id"])
if len(users_id) > 0:
db.save_toast_messages(cg_id, "Geography Level Inserted",
insertValText, None, users_id,
user_id)
return knowledgemaster.SaveGeographyLevelSuccess()
def save_statutory_levels(db, country_id, domain_id, levels, user_id):
table_name = "tbl_statutory_levels"
created_on = get_date_time()
newlist = sorted(levels, key=lambda k: k.level_position, reverse=True)
result = False
s_l_id = None
for n in newlist:
if n.is_remove is True:
result = delete_statutory_level(db, n.level_id)
if result:
s_l_id = n.level_position
break
else:
continue
if result:
return knowledgemaster.LevelShouldNotbeEmpty(s_l_id)
for level in levels:
name = level.level_name
position = level.level_position
values = []
if level.is_remove:
continue
if level.level_id is None:
columns = [
"level_position", "level_name", "country_id", "domain_id",
"created_by", "created_on"
]
values = [
int(country_id),
int(domain_id), position, name,
int(user_id),
str(created_on)
]
new_id = db.call_insert_proc("sp_insert_statutory_level", values)
if new_id is not False:
c_name = get_country_by_id(db, country_id)
d_name = get_domain_by_id(db, domain_id)
msg_text = "Statutory Level \"" + name + "\" is inserted under Country - " + c_name + " ,Domain - " + d_name
u_cg_id = [3, 4, 5, 7]
for cg_id in u_cg_id:
users_id = []
result = db.call_proc("sp_users_under_user_category",
(cg_id, ))
for user in result:
users_id.append(user["user_id"])
if len(users_id) > 0:
db.save_toast_messages(cg_id, "Statutory Level Added",
msg_text, None, users_id,
user_id)
action = "New Statutory levels added"
db.save_activity(user_id, frmStatutoryLevelMaster, action)
else:
raise process_error("E007")
else:
values = [position, name, level.level_id, user_id]
if (db.call_update_proc("sp_update_statutory_levels", values)):
c_name = get_country_by_id(db, country_id)
d_name = get_domain_by_id(db, domain_id)
msg_text = "Statutory Level \"" + name + "\" is inserted under Country - " + c_name + " ,Domain - " + d_name
u_cg_id = [3, 4, 5, 7]
for cg_id in u_cg_id:
users_id = []
result = db.call_proc("sp_users_under_user_category",
(cg_id, ))
for user in result:
users_id.append(user["user_id"])
if len(users_id) > 0:
db.save_toast_messages(cg_id,
"Statutory Level Updated",
msg_text, None, users_id,
user_id)
action = "Statutory levels updated"
db.save_activity(user_id, frmStatutoryLevelMaster, action)
else:
raise process_error("E008")
return knowledgemaster.SaveStatutoryLevelSuccess()
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
def save_assigned_statutory(db, client_statutory_id, client_id,
compliances_list, unit_ids, units,
level_1_statutory_compliance, submission_type,
session_user):
### Inserting in client statutories
column = ["client_id", "unit_id"]
value_list = []
for unit_id in unit_ids:
value_list.append((client_id, unit_id))
db.bulk_insert(tblClientStatutories, column, value_list)
compliance_ids_list = []
for level_1 in level_1_statutory_compliance:
compliance_ids_list = compliance_ids_list + level_1_statutory_compliance[
level_1]
compliance_domains, client_statutory_ids = db.call_proc_with_multiresult_set(
"sp_clientstatutories_by_client_unit", (
client_id,
",".join(str(x) for x in unit_ids),
",".join(str(x) for x in compliance_ids_list),
), 2)
compliance_domain_maps = {}
client_domain_statutories = {}
unit_id_map = {}
### To pick domain id with compliance id
for c in compliance_domains:
compliance_domain_maps[c["compliance_id"]] = c["domain_id"]
### To pick client stautory id with client id and unit id
for s in client_statutory_ids:
client_id = s["client_id"]
unit_id = s["unit_id"]
if client_id not in client_domain_statutories:
client_domain_statutories[client_id] = {}
if unit_id not in client_domain_statutories[client_id]:
client_domain_statutories[client_id][unit_id] = s[
"client_statutory_id"]
### To pick unit values with unit id
for u in units:
unit_id_map[u.unit_id] = u
### Inserting in client compliances table
if (submission_type == "save"):
compliance_columns = [
"client_statutory_id", "client_id", "legal_entity_id", "unit_id",
"domain_id", "statutory_id", "statutory_applicable_status",
"compliance_id", "compliance_applicable_status", "is_saved",
"saved_by", "saved_on"
]
else:
compliance_columns = [
"client_statutory_id", "client_id", "legal_entity_id", "unit_id",
"domain_id", "statutory_id", "statutory_applicable_status",
"compliance_id", "compliance_applicable_status", "is_submitted",
"submitted_by", "submitted_on"
]
compliance_values_list = []
for compliances in compliances_list:
for unit_id in unit_ids:
client_statutory_id = client_domain_statutories[client_id][unit_id]
compliance_id = compliances.compliance_id
compliance_applicable_status = compliances.compliance_applicability_status
statutory_applicable_status = compliances.statutory_applicability_status
level_1_statutory_id = None
for level_1 in level_1_statutory_compliance:
if compliance_id in level_1_statutory_compliance[level_1]:
level_1_statutory_id = level_1
break
compliance_values_list.append(
(client_statutory_id, client_id,
unit_id_map[unit_id].legal_entity_id, unit_id,
compliance_domain_maps[compliance_id], level_1_statutory_id,
statutory_applicable_status,
compliance_id, compliance_applicable_status, 1, session_user,
get_date_time()))
db.bulk_insert(tblClientCompliances, compliance_columns,
compliance_values_list)
def approve_client_group(db, request, session_user):
client_group_approval_details = request.client_group_approval_details
current_time_stamp = get_date_time()
columns = ["is_approved", "reason", "approved_by", "approved_on"]
values = []
conditions = []
client_ids = []
le_ids = []
approval_status = False
approved_entity = ''
rejected_entity = ''
text = ''
old_client_id = 0
techno_manager_id = []
group_name = ''
console_users_id = []
res = db.call_proc("sp_users_under_user_category", (2, ))
for user in res:
console_users_id.append(user["user_id"])
for detail in client_group_approval_details:
client_id = detail.client_id
entity_id = detail.entity_id
entity_name = detail.entity_name
approval_status = detail.approval_status
reason = detail.reason
le_ids.append(entity_id)
if old_client_id != client_id:
client_ids.append(client_id)
old_client_id = client_id
group_name = get_group_by_id(db, client_id)
rows = db.call_proc("sp_get_techno_manager_id_by_client",
(client_id, ))
for r in rows:
techno_manager_id.append(int(r["user_id"]))
value_tuple = (1 if approval_status is True else 2, reason,
session_user, current_time_stamp)
values.append(value_tuple)
condition = "legal_entity_id=%s" % (entity_id)
conditions.append(condition)
if (approval_status is True):
approved_entity = approved_entity + entity_name + ' '
if len(console_users_id) > 0:
db.save_toast_messages(
2, "Approve Client Group", entity_name +
" for the Group \"" + group_name + "\" has been approved",
None, console_users_id, session_user)
if len(techno_manager_id) > 0:
db.save_toast_messages(
5, "Approve Client Group", entity_name +
" for the Group \"" + group_name + "\" has been approved",
None, techno_manager_id, session_user)
else:
rejected_entity = rejected_entity + entity_name + ' '
if len(techno_manager_id) > 0:
db.save_toast_messages(
5, "Approve Client Group",
entity_name + " for the Group \"" + group_name +
"\" has been rejected for the reason \"" + reason + "\"",
None, techno_manager_id, session_user)
result = db.bulk_update("tbl_legal_entities", columns, values, conditions)
for le_id in le_ids:
db.call_update_proc("sp_legal_entity_contract_history_delete",
(le_id, ))
db.call_update_proc("sp_legal_entity_domain_history_delete", (le_id, ))
for client_id in client_ids:
db.call_update_proc("sp_client_group_history_delete", (client_id, ))
if approved_entity != '':
text = approved_entity + " has been Approved. "
if rejected_entity != '':
text = approved_entity + rejected_entity + "has been Rejected."
#
# sp_activity_log_save
# Arguments : user id, form id, action, time of action
# Results : Returns activity log id
#
if result:
db.call_insert_proc(
"sp_activity_log_save",
(session_user, frmApproveClientGroup, text, current_time_stamp))
return result
else:
db.call_insert_proc(
"sp_activity_log_save",
(session_user, frmApproveClientGroup,
return_Knowledge_message("E072"), current_time_stamp))
raise process_error("E072")
def approve_unit(db, request, session_user):
unit_approval_details = request.unit_approval_details
legal_entity_name = None
current_time_stamp = get_date_time()
columns = [
"is_approved", "approved_by", "approved_on", "remarks", "updated_by",
"updated_on"
]
values = []
conditions = []
admin_users_id = []
res = db.call_proc("sp_users_under_user_category", (1, ))
for user in res:
admin_users_id.append(user["user_id"])
for detail in unit_approval_details:
legal_entity_name = detail.legal_entity_name
unit_id = detail.unit_id
approval_status = detail.approval_status
reason = detail.reason
value_tuple = (1 if approval_status is True else 2, session_user,
current_time_stamp, reason, session_user,
current_time_stamp)
values.append(value_tuple)
condition = "unit_id=%s" % (unit_id)
conditions.append(condition)
u_name_rows = db.call_proc("sp_unitname_by_id", [unit_id])
u_name = u_name_rows[0]["unit_name"]
techno_executive_id = []
techno_exe_rows = db.call_proc("sp_get_techno_executive_id_by_unit",
(unit_id, ))
for r in techno_exe_rows:
techno_executive_id.append(int(r["user_id"]))
if (approval_status is True):
if len(admin_users_id) > 0:
# db.save_toast_messages(1, "Approve Client Unit", u_name + " for the Group \""+ group_name + "\" in " + legal_entity_name +"has been approved", None, admin_users_id, session_user)
db.save_toast_messages(
1, "Approve Client Unit",
u_name + " for the Legal entity \"" + legal_entity_name +
"\" has been approved", None, admin_users_id, session_user)
if len(techno_executive_id) > 0:
db.save_toast_messages(
6, "Approve Client Unit",
u_name + " for the Legal entity \"" + legal_entity_name +
"\" has been approved", None, techno_executive_id,
session_user)
else:
if len(admin_users_id) > 0:
# db.save_toast_messages(1, "Approve Client Unit", u_name + " for the Group \""+ group_name + "\" in " + legal_entity_name +"has been rejected", None, admin_users_id, session_user)
db.save_toast_messages(
1, "Approve Client Unit",
u_name + " for the Legal entity \"" + legal_entity_name +
"\" has been rejected", None, admin_users_id, session_user)
if len(techno_executive_id) > 0:
db.save_toast_messages(
6, "Approve Client Unit",
u_name + " for the Legal entity \"" + legal_entity_name +
"\" has been rejected", None, techno_executive_id,
session_user)
result = db.bulk_update(tblUnits, columns, values, conditions)
#
# sp_activity_log_save
# Arguments : user id, form id, action, time of action
# Results : Returns activity log id
#
if result:
db.call_insert_proc(
"sp_activity_log_save",
(session_user, frmClientUnitApproval, "Approved Unit" if
(approval_status is True) else "Rejected Unit",
current_time_stamp))
return result
else:
db.call_insert_proc(
"sp_activity_log_save",
(session_user, frmClientUnitApproval,
return_Knowledge_message("E072"), current_time_stamp))
raise process_error("E072")
create_db_process(db, client_db_id, client_id, legal_entity_id, db_server_id, le_db_server_id)
# Notification Message
current_time_stamp = str(get_date_time())
db.call_insert_proc("sp_allocate_server_message_save", (session_user, "Save", client_id, legal_entity_id, data[0]["legal_entity_name"], current_time_stamp))
except Exception, e :
print e
raise Exception(e)
else:
db.call_insert_proc(
"sp_clientdatabase_update",
(client_db_id, client_id, legal_entity_id, machine_id, db_server_id, le_db_server_id, file_server_id,
client_ids, legal_entity_ids, request.old_grp_app_id, request.old_grp_db_s_id, request.old_le_db_s_id,
request.old_le_f_s_id, request.new_cl_ids, request.new_grp_le_ids, request.new_le_le_ids,
request.new_le_f_s_ids, session_user, get_date_time(), f_legal_entity_ids, le_legal_entity_ids)
)
# Notification Message
current_time_stamp = str(get_date_time())
db.call_insert_proc("sp_allocate_server_message_save", (session_user, "Update", client_id, legal_entity_id, data[0]["legal_entity_name"], current_time_stamp))
#
# To get legal entity name by it's id to save activity
# Parameters : legal entity id
# Return : Returns legal entity name
#
action = "Allocated database environment for %s " % (
data[0]["legal_entity_name"])
db.save_activity(session_user, frmAllocateDatabaseEnvironment, action)
return True
# perform db creation
