def begin_process(self):
current_date = datetime.datetime.now()
logNotifyInfo("current_date", current_date)
current_time = return_hour_minute(current_date)
# if current_time != NOTIFY_TIME :
# logNotifyInfo("current_time", current_time)
# logNotifyInfo("NOTIFY_TIME", NOTIFY_TIME)
# return
client_info = self.get_client_db_list()
for c in client_info:
try :
le_id = c["legal_entity_id"]
deletion_period = self.get_deletion_period(le_id)
task = AutoNotify(
c["database_ip"], c["database_username"],
c["database_password"], c["database_name"],
c["database_port"], c["client_id"], le_id,
current_date,
c["file_ip"], c["file_port"], deletion_period
)
print task
task.start_process()
except Exception, e :
print e
logNotifyError("DailyProcess", e)
logNotifyError("DailyProcess", (traceback.format_exc()))
def save_notification_users(notification_id, user_id):
if user_id is not "NULL" and user_id is not None :
q = "INSERT INTO tbl_notifications_user_log(notification_id, user_id) " + \
" VALUES (%s, %s) "
v = (notification_id, user_id)
logNotifyInfo("save_notification_users", q % v)
self.execute(q, v)
def get_email_id_for_users(self, user_id):
q = "SELECT employee_name, email_id from tbl_users where user_id = %s"
logNotifyInfo("user_email_id", q % (user_id))
row = self.select_one(q, [user_id])
if row :
return row["employee_name"], row["email_id"]
else :
return None, None
def reminder_to_assignee(self, client_info, compliance_info):
current_date = get_current_date()
print "Reminder to assignee ", self.legal_entity_id, " - ", current_date
try :
# print "begin process to remind inprogress compliance task %s " % (current_date)
# client_info = get_client_settings(db)
if client_info :
# reminder_interval = int(client_info["assignee_reminder"])
# logNotifyInfo("reminder_to_assignee", reminder_interval)
count = 0
for c in compliance_info:
if c["due_date"] is None :
continue
# if c["due_date"].date() < current_date.date() :
# print "skipped due_date because due_date crossed", c["due_date"].date()
# logNotifyInfo("skipped due_date", c["due_date"].date())
# continue
days_left = abs((c["due_date"].date() - current_date.date()).days) + 1
if c["document_name"] not in (None, "None", "") :
compliance_name = c["document_name"] + " - " + c["compliance_task"]
else :
compliance_name = c["compliance_task"]
# date_diff = abs((current_date.date() - c["start_date"].date()).days)
# if date_diff == 0:
# logNotifyInfo("skipped due_date reminder days is 0", current_date.date())
# logNotifyInfo("skipped due_date", c["start_date"].date())
# logNotifyInfo("skipped date_diff ", 0)
# continue
# print date_diff
# print (date_diff % reminder_interval)
notification_text = "%s day(s) left to complete %s task" % (days_left, compliance_name)
extra_details = " %s - Reminder" % (c["compliance_history_id"])
# if (date_diff % reminder_interval) == 0 :
self.save_in_notification(
c["country_id"], c["domain_id"], c["business_group_id"], c["legal_entity_id"],
c["division_id"], c["unit_id"], c["compliance_id"], c["assignee"],
c["concurrence_person"], c["approval_person"],
notification_text, extra_details, notification_type_id=2, notify_to_all=False
)
a_name, assignee_email = self.get_email_id_for_users(c["assignee"])
email.notify_to_assignee(
a_name, days_left, compliance_name,
c["unit_name"], assignee_email
)
count += 1
logNotifyInfo("reminder_to_assignee ", "%s compliances remind to assignee" % (count))
except Exception, e:
print e
print(traceback.format_exc())
def get_client_settings(self):
query = "SELECT assignee_reminder, escalation_reminder_in_advance, escalation_reminder, reassign_service_provider, " + \
" c.email_id " + \
" FROM tbl_reminder_settings as r" + \
" INNER JOIN tbl_client_groups as c on c.client_id = r.client_id " +\
" where legal_entity_id = %s "
logNotifyInfo("client_settings", query % (self.legal_entity_id))
rows = self.select_one(query, [self.legal_entity_id])
if rows :
return rows
else :
return
def get_inprogress_compliances(self):
query = "SELECT distinct t1.compliance_history_id, t1.unit_id, t1.compliance_id, t1.start_date, " + \
" t1.due_date, t3.document_name, t3.compliance_task, " + \
" t2.assignee, t2.concurrence_person, t2.approval_person, t4.unit_code, t4.unit_name, " + \
" t4.business_group_id, t4.legal_entity_id, t4.division_id, t2.country_id, " + \
" t3.domain_id, t3.frequency_id FROM " + \
" tbl_compliance_history t1 INNER JOIN tbl_assign_compliances t2 on " + \
" t1.compliance_id = t2.compliance_id " + \
" INNER JOIN tbl_compliances t3 on t1.compliance_id = t3.compliance_id " + \
" INNER JOIN tbl_units t4 on t1.unit_id = t4.unit_id WHERE " + \
" IFNULL(t1.approve_status, 0) != 1 "
logNotifyInfo("get_inprogress_compliances", query)
rows = self.select_all(query)
return rows
def escalation_reminder_in_advance(self):
q = "select ch.compliance_history_id, ch.unit_id, ch.compliance_id, ch.start_date, " + \
"ch.due_date, c.document_name, c.compliance_task, " + \
" ch.completed_by as assignee, ch.concurred_by as concurrence_person, ch.approved_by as approval_person, " + \
" u.unit_code, u.unit_name, u.business_group_id, u.legal_entity_id, " + \
" u.division_id, u.country_id, c.domain_id, c.frequency_id " + \
" from tbl_compliance_history as ch " + \
" inner join tbl_compliances as c on ch.compliance_id = c.compliance_id " + \
" inner join tbl_units as u on ch.unit_id = u.unit_id " + \
" left join tbl_reminder_settings as rs on ch.legal_entity_id = rs.legal_entity_id " + \
" Where ch.current_status < 3 and" + \
" date_sub(ch.due_date, INTERVAL rs.escalation_reminder_in_advance DAY) = date(CONVERT_TZ(UTC_TIMESTAMP,'+00:00','+05:30'));"
logNotifyInfo("get_inprogress_compliances", q)
rows = self.select_all(q)
return rows
def escalation_reminder_after_due_date(self):
q = "select ch.compliance_history_id, ch.unit_id, ch.compliance_id, ch.start_date, " + \
"ch.due_date, c.document_name, c.compliance_task, " + \
"ch.completed_by as assignee, ch.concurred_by as concurrence_person, ch.approved_by as approval_person, " + \
" u.unit_code, u.unit_name, u.business_group_id, u.legal_entity_id, " + \
" u.division_id, u.country_id, c.domain_id, c.frequency_id, c.statutory_mapping, " + \
" le.legal_entity_name, u.geography_name " + \
" from tbl_compliance_history as ch " + \
" inner join tbl_compliances as c on ch.compliance_id = c.compliance_id " + \
" inner join tbl_units as u on ch.unit_id = u.unit_id " + \
" inner join tbl_legal_entities as le on u.legal_entity_id = le.legal_entity_id " + \
" left join tbl_reminder_settings as rs on ch.legal_entity_id = rs.legal_entity_id " + \
" Where ch.current_status < 3 and" + \
" date(ch.due_date) < date(CONVERT_TZ(UTC_TIMESTAMP,'+00:00','+05:30')) " + \
" AND MOD(datediff(CONVERT_TZ(UTC_TIMESTAMP,'+00:00','+05:30'),ch.due_date),rs.escalation_reminder) = 0 "
logNotifyInfo("get_inprogress_compliances", q)
rows = self.select_all(q)
return rows
def begin_process(self):
current_date = datetime.datetime.now()
logNotifyInfo("current_date", current_date)
current_time = return_hour_minute(current_date)
client_info = self.get_client_db_list()
# print client_info
for c in client_info:
try:
print "-------------------------------"
print "DATABASE>>", c
print "-------------------------------"
task = AutoNotify(c["database_ip"], c["database_username"],
c["database_password"], c["database_name"],
c["database_port"], c["client_id"],
c["legal_entity_id"], current_date)
task.start_process()
except Exception, e:
print e
logNotifyError("AuditTrailAutoDeleteion", e)
logNotifyError("AuditTrailAutoDeleteion",
(traceback.format_exc()))
def notify_escalation_to_all(self, client_info, compliance_info):
current_date = get_current_date()
print "Reminder escalation to all ", self.legal_entity_id, " - ", current_date
logNotifyInfo("escalation_to_all", "begin process to notify escalations to all %s" % (current_date))
# escalation_interval = int(client_info["escalation_reminder"])
cnt = 0
print compliance_info
for c in compliance_info :
if c["due_date"] is None :
continue
maps = json.loads(c["statutory_mapping"])[0]
if c["document_name"] not in (None, "None", "") :
compliance_name = c["document_name"] + " - " + c["compliance_task"]
else :
compliance_name = c["compliance_task"]
compliance_name = '''"%s" under "%s"''' % (compliance_name, maps)
uname = "%s & %s & %s" % (c["unit_code"], c["unit_name"], c["geography_name"])
a_name, assignee_email = self.get_email_id_for_users(c["assignee"])
over_due_days = abs((current_date.date() - c["due_date"].date()).days) + 1
notification_text = "%s for %s is %s day(s) overdue by %s " % (compliance_name, uname, over_due_days, a_name)
if c["frequency_id"] == 5 :
notification_text = "On Occurrence Task - %s" % (notification_text)
print notification_text
extra_details = " %s - Escalation" % (c["compliance_history_id"])
# if (over_due_days % escalation_interval) == 0 :
self.save_in_notification(
c["country_id"], c["domain_id"], c["business_group_id"],
c["legal_entity_id"], c["division_id"], c["unit_id"], c["compliance_id"],
c["assignee"], c["concurrence_person"], c["approval_person"], notification_text,
extra_details, notification_type_id=3
)
cc_person = []
concurrence_person = c["concurrence_person"]
concur_msg = ''
if concurrence_person == 0 :
concurrence_person = None
if concurrence_person is not None :
c_name, concurrence_email = self.get_email_id_for_users(concurrence_person)
cc_person.append(concurrence_email)
concur_msg += '''"%s" for concurrence ''' % (c_name)
ap_name, approval_email = self.get_email_id_for_users(c["approval_person"])
cc_person.append(approval_email)
concur_msg += '''and "%s" for approval''' % (ap_name)
le_name = self.get_legal_entity_name(self.legal_entity_id)
message = '''
<p>Greetings from Compfie</p> \
<p>We wish to notify that the "%s" assigned to %s with %s \
for the "%s & %s" was expected to complete on or before %s. Now it
has crossed due date and delayed for %s days. \
This is a gentle reminder to rush up and comply \
the same in order to reduce the risk.</p> \
<p>Always keep on track of reminders, messages, \
escalations and statutory notifications and stay compliant.</p> \
<p align="left">Thanks & regards,</p> \
<p align="left">Compfie Administrator</p> \
''' % (compliance_name, concur_msg, le_name, uname, c["due_date"], over_due_days)
email.notify_escalation(a_name, message, assignee_email, cc_person)
cnt += 1
logNotifyInfo("escalation count", cnt)
def reminder_before_due_date(self, client_info, compliance_info):
current_date = get_current_date()
print "Reminder before due date ", self.legal_entity_id, " - ", current_date
logNotifyInfo("before_due_date", "begin process to remind inprogress compliance task to all %s " % (current_date))
# reminder_interval = int(client_info["escalation_reminder_in_advance"])
cnt = 0
for c in compliance_info:
if c["due_date"] is None :
continue
if c["document_name"] not in (None, "None", "") :
compliance_name = c["document_name"] + " - " + c["compliance_task"]
else :
compliance_name = c["compliance_task"]
# if c["due_date"].date() < current_date.date() :
# logNotifyInfo("skipped due_date crossed current date", c["due_date"])
# continue
days_left = abs((c["due_date"].date() - current_date.date()).days) + 1
notification_text = "%s day(s) left to complete %s task" % (days_left, compliance_name)
extra_details = " %s - Reminder" % (c["compliance_history_id"])
# if days_left == reminder_interval:
self.save_in_notification(
c["country_id"], c["domain_id"], c["business_group_id"],
c["legal_entity_id"], c["division_id"], c["unit_id"], c["compliance_id"],
c["assignee"], c["concurrence_person"], c["approval_person"], notification_text,
extra_details, notification_type_id=2
)
uname = "%s & %s & %s" % (c["unit_code"], c["unit_name"], c["geography_name"])
compliance_name = '''"%s" under "%s"''' % (compliance_name, maps)
a_name, assignee_email = self.get_email_id_for_users(c["assignee"])
cc_person = []
concurrence_person = c["concurrence_person"]
if concurrence_person == 0 :
concurrence_person = None
if concurrence_person is not None :
c_name, concurrence_email = self.get_email_id_for_users(concurrence_person)
cc_person.append(concurrence_email)
concur_msg += '''"%s" for concurrence''' % (c_name)
ap_name, approval_email = self.get_email_id_for_users(c["approval_person"])
cc_person.append(approval_email)
concur_msg += '''and "%s" for approval''' % (ap_name)
le_name = self.get_legal_entity_name(self.legal_entity_id)
message = '''
<p>Dear %s</p> \
<p>Greetings from Compfie</p> \
<p>We wish to notify that the "compliance_name" assigned to "%s" with %s \
for the "%s & %s" falls due on %s. \
This is a gentle reminder to rush up and comply \
the same in order to reduce the risk.</p> \
<p>Always keep on track of reminders, messages, \
escalations and statutory notifications and stay compliant.</p> \
<p align="left">Thanks & regards,</p> \
<p align="left">Compfie Administrator</p> \
''' % (a_name, compliance_name, concur_msg, le_name, uname, c["due_date"])
email.notify_before_due_date(
a_name, days_left, compliance_name,
c["unit_name"],
assignee_email, cc_person
)
cnt += 1
logNotifyInfo("before_due_date", "%s compliance remind before due date" % cnt)