def process_onoccurrence_transaction_list(db, request, session_user):
compliance_id = request.compliance_id
unit_id = request.unit_id
resultRows = getLastTransaction_Onoccurrence(db, compliance_id, unit_id)
transactionList = []
for row in resultRows:
compliance_hisory_id = int(row["compliance_history_id"])
compliance_id = int(row["compliance_id"])
compliance_task = row["compliance_task"]
on_statutory = row["statutory"]
on_unit = row["unit"]
compliance_description = row["compliance_description"]
start_date = datetime_to_string(row["start_date"])
assignee_name = row["assignee"]
completion_date = datetime_to_string_time(row["completion_date"])
concurrer_name = row["concurr"]
concurred_on = datetime_to_string_time(row["concurred_on"])
approver_name = row["approver"]
approver_on = datetime_to_string_time(row["approved_on"])
on_compliance_status = row["compliance_status"]
transactionList.append(
clientcore.GetOnoccurrencce_Last_Transaction(
compliance_hisory_id, compliance_id, compliance_task,
on_statutory, on_unit, compliance_description, start_date,
assignee_name, completion_date, concurrer_name, concurred_on,
approver_name, approver_on, on_compliance_status))
return transactionList
def get_audit_trail_filters(db):
user_categories = get_user_cetegories_audit_trail(db)
expected_result = 4
result = db.call_proc_with_multiresult_set('sp_countries_for_audit_trails',
(), expected_result)
countries = result[0]
audit_trail_countries = []
for row in countries:
user_id = row["user_id"]
user_category_id = row["user_category_id"]
country_id = row["country_id"]
country_name = row["country_name"]
audit_trail_countries.append(
generalprotocol.AuditTrailCountries(user_id, user_category_id,
country_id, country_name))
forms_list = return_forms(result[1])
users = return_users(result[2])
forms_log = result[3]
audit_trail_details = []
for row in forms_log:
user_id = row["user_id"]
user_category_id = row["user_category_id"]
form_id = row["form_id"]
action = row["action"]
date = datetime_to_string_time(row["created_on"])
audit_trail_details.append(
generalprotocol.AuditTrail(user_id, user_category_id, form_id,
action, date))
return generalprotocol.GetAuditTrailFilterSuccess(user_categories,
audit_trail_countries,
forms_list, users,
audit_trail_details)
def process_get_service_providers(db, request, session_user):
service_provider_list = get_service_provider_details_list(db)
current_date_time = get_date_time_in_date()
str_current_date_time = datetime_to_string_time(current_date_time)
return clientmasters.GetServiceProvidersSuccess(
service_providers=service_provider_list,
current_date=str_current_date_time)
def get_audit_trails(db, session_user, from_count, to_count, from_date,
to_date, user_id, form_id, category_id, client_id,
legal_entity_id, unit_id):
if user_id is None:
user_id = '%'
if form_id is None:
form_id = '%'
if category_id is None:
category_id = '%'
if unit_id is None:
unit_id = '%'
if legal_entity_id is None:
legal_entity_id = '%'
from_date = string_to_datetime(from_date).date()
to_date = string_to_datetime(to_date).date()
args = [
from_date, to_date, user_id, form_id, category_id, from_count, to_count
]
expected_result = 2
print client_id
print args
if client_id is None:
result = db.call_proc_with_multiresult_set('sp_get_audit_trails', args,
expected_result)
else:
args = [
from_date, to_date, user_id, form_id, category_id, client_id,
legal_entity_id, unit_id, from_count, to_count
]
result = db.call_proc_with_multiresult_set(
'sp_get_client_audit_trails', args, expected_result)
'''
'sp_get_audit_trails' this procedure will return four result-set which are Forms, Users, Activity_log_data and Activity_log total
'''
activity_log = result[0]
total = result[1]
assert len(total) > 0
c_total = total[0]["total"]
audit_trail_details = []
for row in activity_log:
user_id = row["user_id"]
user_category_id = row["user_category_id"]
form_id = row["form_id"]
action = row["action"]
date = datetime_to_string_time(row["created_on"])
audit_trail_details.append(
generalprotocol.AuditTrail(user_id, user_category_id, form_id,
action, date))
return generalprotocol.GetAuditTrailSuccess(audit_trail_details, c_total)
def get_statutory_notifications(db, from_count, page_count, session_user):
args = [from_count, page_count, session_user]
rows = db.call_proc('sp_get_statutory_notifications', args)
get_statutory_notifications = []
for row in rows:
get_statutory_notifications.append(
generalprotocol.StatutoryNotification(
row["notification_id"], row["user_id"], row["compliance_id"],
row["notification_text"], row["created_by"],
datetime_to_string_time(row["created_on"]),
bool(row["read_status"])))
return get_statutory_notifications
def get_messages(db, from_count, page_count, session_user):
expected_result = 2
args = [from_count, page_count, session_user]
rows = db.call_proc_with_multiresult_set('sp_get_messages', args,
expected_result)
messages = []
for row in rows[1]:
messages.append(
generalprotocol.Message(
row["message_id"], row["message_heading"], row["message_text"],
row["link"], row["created_by"],
datetime_to_string_time(row["created_on"])))
return messages
def fetch_data_to_export(self):
q = "select distinct t1.compliance_task, t1.document_name, t1.statutory_provision, t1.compliance_description, " + \
" t1.penal_consequences,t2.start_date, t2.due_date, t2.completion_date, t2.validity_date, " + \
" t2.remarks, (select group_concat(employee_code, ' - ', employee_name) from tbl_users where user_id = t2.completed_by) as assignee, " + \
" t2.completed_on, " + \
" (select group_concat(employee_code,' - ', employee_name) from tbl_users where user_id = t2.concurred_by) as concur, " + \
" t2.concurred_on, " + \
" (select group_concat(ifnull(employee_code, 'Administrator'), ' - ', employee_name) from tbl_users where user_id = t2.approved_by) as approver, " + \
" t2.approved_on, t2.documents, " + \
" t3.country_id, t3.client_id, t3.legal_entity_id, t3.unit_id, t1.domain_id " + \
" from tbl_compliances as t1 " + \
" inner join tbl_compliance_history as t2 on t1.compliance_id = t2.compliance_id " + \
" inner join tbl_units as t3 on t2.unit_id = t3.unit_id where t2.unit_id = %s " + \
" order by t2.start_date, t2.due_date"
rows = self.db.select_all(q, [self.unit_id])
print self.unit_id
data = []
for r in rows:
if r["due_date"] is None:
continue
url_column = ""
if r["documents"] is not None:
year = r["start_date"].year
month = "%s%s" % (string_months[r["start_date"].month],
str(year))
file_path = "%s/%s/%s" % (r["domain_id"], year, month)
url_column = "%s/%s" % (file_path, r["documents"])
data.append([
r.get("compliance_task"),
r.get("document_name"),
r.get("statutory_provision"),
r.get("compliance_description"),
r.get("penal_consequences"),
datetime_to_string_time(r.get("start_date")),
datetime_to_string_time(r.get("due_date")),
"" if r.get("completion_date") is None else
datetime_to_string_time(r.get("completion_date")),
"" if r.get("validity_date") is None else
datetime_to_string_time(r.get("validity_date")),
"" if r.get("remarks") is None else r.get("remarks"),
r.get("assignee"), "" if r.get("completed_on") is None else
datetime_to_string_time(r.get("completed_on")),
r.get("concur"), "" if r.get("concurred_on") is None else
datetime_to_string_time(r.get("concurred_on")),
r.get("approver"), "" if r.get("approved_on") is None else
datetime_to_string_time(r.get("approved_on")), url_column,
"" if r.get("documents") is None else r.get("documents")
])
return data
def process_get_on_occurrence_compliances(db, request, session_user):
unit_id = request.unit_id
to_count = RECORD_DISPLAY_COUNT
user_domain_ids = get_user_domains(db, session_user)
user_unit_ids = get_user_unit_ids(db, session_user)
compliances = get_on_occurrence_compliances_for_user(
db, session_user, user_domain_ids, user_unit_ids, unit_id,
request.start_count, to_count)
total_count = get_on_occurrence_compliance_count(db, session_user,
user_domain_ids,
user_unit_ids, unit_id)
current_date_time = get_date_time_in_date()
str_current_date_time = datetime_to_string_time(current_date_time)
return clientuser.GetOnOccurrenceCompliancesSuccess(
compliances=compliances,
current_date=str_current_date_time,
total_count=total_count)
def process_get_current_compliance_detail(db, request, session_user):
unit_id = request.unit_id
current_start_count = request.current_start_count
cal_view = request.cal_view
cal_date = request.cal_date
to_count = RECORD_DISPLAY_COUNT
current_compliances_list = get_current_compliances_list(
db, unit_id, current_start_count, to_count, session_user, cal_view,
cal_date)
current_date_time = get_date_time_in_date()
str_current_date_time = datetime_to_string_time(current_date_time)
inprogress_count = get_inprogress_count(db, session_user, unit_id,
cal_view, cal_date)
overdue_count = get_overdue_count(db, session_user, unit_id, cal_view,
cal_date)
return clientuser.GetCurrentComplianceDetailSuccess(
current_compliances=current_compliances_list,
current_date=str_current_date_time,
overdue_count=overdue_count,
inprogress_count=inprogress_count)
def process_get_compliance_approval_list(db, request, session_user):
unit_id = request.unit_id
to_count = RECORD_DISPLAY_COUNT
compliance_approval_list, count = get_compliance_approval_list(
db, unit_id, request.start_count, to_count, session_user
)
total_count = get_compliance_approval_count(db, session_user, unit_id)
approval_status = [
clientcore.COMPLIANCE_APPROVAL_STATUS("Concur"),
clientcore.COMPLIANCE_APPROVAL_STATUS("Reject Concurrence"),
clientcore.COMPLIANCE_APPROVAL_STATUS("Approve"),
clientcore.COMPLIANCE_APPROVAL_STATUS("Reject Approval"),
clientcore.COMPLIANCE_APPROVAL_STATUS("Rectify Concurrence"),
clientcore.COMPLIANCE_APPROVAL_STATUS("Rectify Approval")
]
current_date_time = get_date_time_in_date()
str_current_date_time = datetime_to_string_time(current_date_time)
return clienttransactions.GetComplianceApprovalListSuccess(
approval_list=compliance_approval_list,
approval_status=approval_status,
current_date=str_current_date_time,
total_count=total_count
)
def get_client_login_trace(db, session_user, from_count, to_count, from_date,
to_date, user_id, client_id):
# Initialize Connection to client LE DB
client_audit_trail_details = []
args = [client_id, None]
result = db.call_proc("sp_get_le_db_server_details", args, None)
total_record = 0
print 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"]
try:
cnx_pool = mysql.connector.connect(
user=uname,
password=pwd,
host=dhost,
database=db_name,
port=port,
autocommit=False,
)
c = cnx_pool.cursor(dictionary=True, buffered=True)
select_qry = "select t1.user_id, t1.form_id, t1.action, t1.created_on, (select " + \
"employee_name from tbl_users where user_id " + \
"= t1.user_id) as user_name, (select employee_code from tbl_users " + \
"where user_id = t1.user_id) as emp_code, (select user_category_name from tbl_user_category " + \
"where user_category_id = t1.user_category_id) as user_category_name from tbl_activity_log as t1 where "
where_clause = "t1.form_id = 0 "
condition_val = []
if user_id is not None:
where_clause = where_clause + "and t1.user_id = %s "
condition_val.append(user_id)
if from_date is not None and to_date is not None:
from_date = string_to_datetime(from_date).date()
to_date = string_to_datetime(to_date).date()
where_clause = where_clause + " and t1.created_on >= " + \
" date(%s) and t1.created_on < " + \
" DATE_ADD(%s, INTERVAL 1 DAY) "
condition_val.extend([from_date, to_date])
elif from_date is not None and to_date is None:
from_date = string_to_datetime(from_date).date()
where_clause = where_clause + " and t1.created_on >= " + \
" date(%s) and t1.created_on < " + \
" date(curdate()) "
condition_val.append(from_date)
elif from_date is None and to_date is not None:
to_date = string_to_datetime(to_date).date()
where_clause = where_clause + " and t1.created_on < " + \
" DATE_ADD(%s, INTERVAL 1 DAY) "
condition_val.append(to_date)
where_clause = where_clause + "order by t1.created_on desc limit %s, %s;"
condition_val.extend([int(from_count), int(to_count)])
query = select_qry + where_clause
print "qry"
activity_log = c.execute(query, condition_val)
activity_log = c.fetchall()
condition_val = []
where_clause = None
if from_count == 0:
select_qry = "select count(*) as total_record " + \
"from tbl_activity_log as t1 where "
where_clause = "t1.form_id = 0 "
if user_id is not None:
where_clause = where_clause + "and t1.user_id = %s "
condition_val.append(user_id)
if from_date is not None and to_date is not None:
where_clause = where_clause + " and t1.created_on >= " + \
" date(%s) and t1.created_on < " + \
" DATE_ADD(%s, INTERVAL 1 DAY) "
condition_val.extend([from_date, to_date])
elif from_date is not None and to_date is None:
where_clause = where_clause + " and t1.created_on >= " + \
" date(%s) and t1.created_on < " + \
" date(curdate()) "
condition_val.append(from_date)
elif from_date is None and to_date is not None:
where_clause = where_clause + " and t1.created_on < " + \
" DATE_ADD(%s, INTERVAL 1 DAY) "
condition_val.append(to_date)
query = select_qry + where_clause
print "qry"
print query
result = c.execute(query, condition_val)
result = c.fetchall()
print result
total_record = result[0]["total_record"]
else:
total_record = 0
for row in activity_log:
user_id = row["user_id"]
form_id = row["form_id"]
action = row["action"]
date = datetime_to_string_time(row["created_on"])
user_category_name = row["user_category_name"]
user_name = None
if row["emp_code"] is not None:
user_name = row["emp_code"] + " - " + row["user_name"]
else:
user_name = row["user_name"]
client_audit_trail_details.append(
generalprotocol.ClientAuditTrail(
user_id, form_id, action, date, user_category_name,
user_name))
return generalprotocol.GetClientAuditTrailSuccess(
client_audit_trail_details, total_record)
c.close()
connection.commit()
connection.close()
return True
except Exception, e:
print e
return generalprotocol.DatabaseConnectionFailure()
def get_onoccurance_compliance_to_notify(self):
q = "SELECT ch.compliance_history_id,ch.compliance_id,com.compliance_task,com.document_name,ch.unit_id, " + \
" ch.start_date,ch.due_date, IF(TIMESTAMPDIFF(HOUR, " + \
"DATE_FORMAT((DATE_SUB(ch.due_date,INTERVAL (TIMESTAMPDIFF(SECOND,ch.start_date,ch.due_date) / 2) SECOND)),'%Y-%m-%d %H:%i'),due_date) <= 24, " + \
" IF(TIMESTAMPDIFF(SECOND, " + \
" DATE_FORMAT((DATE_SUB(ch.due_date, " + \
" INTERVAL (TIMESTAMPDIFF(SECOND, " + \
" ch.start_date, " + \
" ch.due_date) / 2) SECOND)), " + \
" '%Y-%m-%d %H:%i'), " + \
" due_date) <= 60, " + \
" CONCAT(TIMESTAMPDIFF(SECOND, " + \
" DATE_FORMAT((DATE_SUB(ch.due_date, " + \
" INTERVAL (TIMESTAMPDIFF(SECOND, " + \
" ch.start_date, " + \
" ch.due_date) / 2) SECOND)), " + \
" '%Y-%m-%d %H:%i'), " + \
" due_date), " + \
" ' seconds'), " + \
" CONCAT(TIMESTAMPDIFF(HOUR, " + \
" DATE_FORMAT((DATE_SUB(ch.due_date, " + \
" INTERVAL (TIMESTAMPDIFF(SECOND, " + \
" ch.start_date, " + \
" ch.due_date) / 2) SECOND)), " + \
" '%Y-%m-%d %H:%i'), " + \
" due_date), " + \
" ' hours')), " + \
" CONCAT(TIMESTAMPDIFF(DAY, " + \
" DATE_FORMAT((DATE_SUB(ch.due_date, " + \
" INTERVAL (TIMESTAMPDIFF(SECOND, " + \
" ch.start_date, " + \
" ch.due_date) / 2) SECOND)), " + \
" '%Y-%m-%d %H:%i'), " + \
" due_date), " + \
" ' days')) AS hours_left, " + \
"usr.email_id, usr.employee_code, usr.employee_name, u.country_id, " + \
" com.domain_id, u.business_group_id, u.legal_entity_id, u.division_id, u.unit_code, " + \
" u.unit_name,ch.completed_by,ch.concurred_by,ch.approved_by " + \
" FROM " + \
" tbl_compliance_history ch " + \
" INNER JOIN " + \
" tbl_compliances AS com ON ch.compliance_id = com.compliance_id " + \
" INNER JOIN " + \
" tbl_users AS usr ON ch.completed_by = usr.user_id " + \
" INNER JOIN " + \
" tbl_units AS u ON ch.unit_id = u.unit_id " + \
" WHERE " + \
" com.frequency_id = 5 " + \
" AND ch.current_status < 3 " + \
" AND DATE_FORMAT((DATE_SUB(ch.due_date, " + \
" INTERVAL (TIMESTAMPDIFF(SECOND, " + \
" ch.start_date, " + \
" ch.due_date) / 2) SECOND)), " + \
" '%Y-%m-%d %H:%i') >= DATE_FORMAT(CONVERT_TZ(UTC_TIMESTAMP, '+00:00', '+05:15'), " + \
" '%Y-%m-%d %H:%i') " + \
" AND DATE_FORMAT((DATE_SUB(ch.due_date, " + \
" INTERVAL (TIMESTAMPDIFF(SECOND, " + \
" ch.start_date, " + \
" ch.due_date) / 2) SECOND)), " + \
" '%Y-%m-%d %H:%i') <= DATE_FORMAT(CONVERT_TZ(UTC_TIMESTAMP, '+00:00', '+05:30'), " + \
" '%Y-%m-%d %H:%i')"
email = EmailHandler()
rows = self.select_all(q)
for r in rows:
country_id = r["country_id"]
domain_id = r["domain_id"]
ch_id = r["compliance_history_id"]
comp_id = r["compliance_id"]
c_task = r["compliance_task"]
doc_name = r["document_name"]
cname = c_task
if doc_name is not None:
cname = "%s - %s" % (doc_name, c_task)
unit_id = r["unit_id"]
due_date = r["due_date"]
left = r["hours_left"]
email_id = r["email_id"]
emp_name = r["employee_name"]
emp_code = r["employee_code"]
if emp_code is not None:
name = "%s - %s" % (emp_code, emp_name)
else:
name = emp_name
assignee = r["completed_by"]
concurr = r["concurred_by"]
approver = r["approved_by"]
bg_id = r["business_group_id"]
div_id = r["division_id"]
le_id = r["legal_entity_id"]
unit_name = "%s - %s" % (r["unit_code"], r["unit_name"])
notification_text = "%s left to complete %s task on due date %s" % (
left, cname, datetime_to_string_time(due_date))
extra_details = " %s - Reminder" % (ch_id)
self.save_in_notification(country_id, domain_id, bg_id, le_id,
div_id, unit_id, comp_id, assignee,
concurr, approver, notification_text,
extra_details, 2)
email.notify_occurrence_to_assignee
notify_occur_compliance = threading.Thread(
target=email.notify_occurrence_to_assignee,
args=[name, left, cname, unit_name, email_id])
notify_occur_compliance.start()
def approve_statutory_mapping_list(db, user_id):
result = db.call_proc_with_multiresult_set(
"sp_tbl_statutory_mapping_approve_list", [user_id], 3)
mappings = result[0]
orgs = result[1]
geo_info = result[2]
def get_orgs(map_id):
orgname = []
for o in orgs:
if o["statutory_mapping_id"] == map_id:
orgname.append(o["organisation_name"])
return orgname
def get_geos(map_id):
geo_names = []
for g in geo_info:
if g["statutory_mapping_id"] == map_id:
geo_names.append(g["parent_names"] + ">>" +
g["geography_name"])
return geo_names
compliance = []
mapped = {}
for m in mappings:
map_id = m["statutory_mapping_id"]
if mapped.get(map_id) is None:
compliance = []
if m["document_name"] is None:
c_name = m["compliance_task"]
else:
c_name = m["document_name"] + " - " + m["compliance_task"]
orgname = get_orgs(map_id)
geo_names = get_geos(map_id)
c_on = datetime_to_string_time(m["created_on"])
u_on = None
if m["updated_by"] is not None:
u_on = datetime_to_string_time(m["updated_on"])
map_text = json.loads(m["statutory_mapping"])
map_text = ", ".join(map_text)
statutory_dates = m["statutory_dates"]
statutory_dates = json.loads(statutory_dates)
date_list = []
for date in statutory_dates:
s_date = core.StatutoryDate(date["statutory_date"],
date["statutory_month"],
date["trigger_before_days"],
date.get("repeat_by"))
date_list.append(s_date)
summary, dates = make_summary(date_list, m["frequency_id"], m)
if dates is not None:
summary += dates
compliance.append(
mobile.MappingComplianceInfo(
m["compliance_id"], c_name, bool(m["is_active"]),
m["created_by"], c_on, m["updated_by"], u_on,
m["statutory_provision"], m["compliance_description"],
m["penal_consequences"], m["freq_name"], summary,
m["reference_link"], ", ".join(geo_names)))
mapped[map_id] = mobile.MappingApproveInfo(map_id, m["country_name"],
m["domain_name"],
m["statutory_nature_name"],
orgname, map_text,
compliance)
return mapped.values()