def get_client_db_list(self):
try:
self._k_db.begin()
query = "select t1.client_id, t1.legal_entity_id, " + \
" t2.database_username, t2.database_password, t2.database_name, " + \
" t3.database_ip, t3.database_port, " + \
" t4.ip as file_ip, t4.port as file_port " + \
" from tbl_client_database as t1 " + \
" inner join tbl_client_database_info as t2 " + \
" on t1.client_database_id = t2.client_database_id and t2.is_group = 0 " + \
" inner join tbl_database_server as t3 " + \
" on t1.database_server_id = t3.database_server_id " + \
" inner join tbl_file_server as t4 on " + \
" t1.file_server_id = t4.file_server_id "
logProcessInfo("client_db_list", str(query))
rows = self._k_db.select_all(query)
self._k_db.commit()
if rows:
return rows
else:
return []
except Exception, e:
print e
logProcessError("get_clients", str(e))
self._k_db.rollback()
logProcessError("get_clients", str(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)
logProcessInfo("save_notification_user %s" % self.client_id,
q % v)
self.execute(q, v)
def update_assign_compliance_due_date(self, trigger_before, due_date,
unit_id, compliance_id):
query = "UPDATE tbl_assign_compliances set due_date= %s, " + \
" trigger_before_days= %s " + \
" WHERE unit_id = %s AND compliance_id = %s "
values = (due_date, trigger_before, unit_id, compliance_id)
logProcessInfo("update_assigne_compliance", query % values)
self.execute(query, values)
def get_email_id_for_users(self, user_id):
q = "SELECT employee_name, email_id from tbl_users where user_id = %s"
logProcessInfo("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
def check_service_provider_contract_period(self):
query = "UPDATE tbl_service_providers set is_active = 0 WHERE " + \
" contract_from >= now() and contract_to <= now() "
try:
self.execute(query)
logProcessInfo(
"check_service_provider_contract_period %s" % self.client_id,
str(query))
except Exception, e:
logProcessError(
"check_service_provider_contract_period %s" % self.client_id,
str(e))
def start_process(self):
if self._connection is None:
details = "%s, %s" % (self._c_db_ip, self._c_db_name)
logProcessInfo("connection not found %s" % self.client_id, details)
return
try:
self.begin()
self.run_deletion_process()
self.commit()
except Exception, e:
logProcessError("start_deletion_process %s" % self.client_id,
str(e))
logProcessError("start_deletion_process",
str(traceback.format_exc()))
logProcessError("start_deletion_process", (traceback.format_exc()))
print e
def start_process(self):
if self._connection is None:
details = "%s, %s" % (self._c_db_ip, self._c_db_name)
logProcessInfo("connection not found %s" % self.client_id, details)
return
try:
self.begin()
self.start_new_task()
self.check_service_provider_contract_period()
self.update_unit_wise_task_status()
self.update_user_wise_task_status()
self.update_duedate_in_calendar_view()
self.update_upcoming_in_calendar_view()
self.commit()
self.close()
except Exception, e:
logProcessError("start_process %s" % self.client_id, str(e))
logProcessError("start_process", str(traceback.format_exc()))
logProcessError("start_process", (traceback.format_exc()))
self.rollback()
self.close()
def begin_process(self):
logProcessInfo("AutoDeletionProcess", "Process Begin")
country_time_zones = sorted(countries)
country_list = self.get_countries()
print country_list
for c in country_list:
name = c["country_name"].replace(" ", "")
name = name.replace("_", "")
name = name.replace("-", "")
info = None
for ct in country_time_zones:
ct = ct.replace(" ", "")
if name.lower() == ct.lower():
info = countries.get(ct)
print info
break
if info:
current_date = return_date(
time_convertion(info.get("timezones")[0]))
print "country --", c["country_name"]
country_id = c["country_id"]
print current_date
client_info = self.get_client_db_list()
logProcessInfo("AutoDeletionProcess", "Process Begin")
for ci in client_info:
print ci["client_id"]
try:
delp = AutoDeletionStart(
ci["database_ip"], ci["database_username"],
ci["database_password"], ci["database_name"],
ci["database_port"], ci["client_id"],
ci["legal_entity_id"], current_date, country_id)
delp.start_process()
except Exception, e:
logProcessError("AutoDeletionProcess", e)
logProcessError("AutoDeletionProcess",
(traceback.format_exc()))
def save_in_compliance_history(self, unit_id, compliance_id, start_date,
due_date, next_due_date, assignee,
concurrence, approve):
# compliance_history_id = get_new_id(db, "tbl_compliance_history", "compliance_history_id")
if concurrence is not None:
values = (self.legal_entity_id, unit_id, compliance_id, start_date,
due_date, next_due_date, assignee, approve, concurrence)
query = "INSERT INTO tbl_compliance_history (legal_entity_id, unit_id, compliance_id, " + \
" start_date, due_date, next_due_date, completed_by, approved_by, concurred_by) " + \
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) "
else:
values = (self.legal_entity_id, unit_id, compliance_id, start_date,
due_date, next_due_date, assignee, approve)
query = "INSERT INTO tbl_compliance_history (legal_entity_id, unit_id, compliance_id, " + \
" start_date, due_date, next_due_date, completed_by, approved_by) " + \
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s) "
logProcessInfo("save_new_compliance %s" % self.client_id,
query % values)
compliance_history_id = self.execute_insert(query, values)
return compliance_history_id
def get_compliance_to_start(self):
query = "SELECT t1.country_id, t1.unit_id, t1.compliance_id, t1.statutory_dates, " + \
" t1.trigger_before_days, t1.due_date, t1.validity_date, " + \
" t2.document_name, t2.compliance_task, t2.frequency_id, t2.repeats_type_id," + \
" t2.repeats_every, (t1.due_date - INTERVAL t1.trigger_before_days DAY) start_date," + \
" t3.unit_id, t3.unit_code, t3.unit_name, t3.business_group_id, " + \
" t3.legal_entity_id, t3.division_id, t2.domain_id, " + \
" t1.assignee, t1.concurrence_person, t1.approval_person, " + \
" t1.compliance_id " + \
" from tbl_assign_compliances t1 " + \
" INNER JOIN tbl_legal_entities as t5 on t1.legal_entity_id = t5.legal_entity_id and t5.is_closed = 0 and t5.contract_to > %s " + \
" INNER JOIN tbl_units t3 on t1.unit_id = t3.unit_id and t3.is_closed = 0 " + \
" INNER JOIN tbl_compliances t2 on t1.compliance_id = t2.compliance_id " + \
" LEFT JOIN tbl_compliance_history t4 ON (t4.unit_id = t1.unit_id " + \
" AND t4.compliance_id = t1.compliance_id AND t2.frequency_id = 1)" + \
" WHERE (t1.due_date - INTERVAL t1.trigger_before_days DAY) <= %s " + \
" AND t1.is_active = 1 AND t2.is_active = 1 AND t2.frequency_id < 5 " + \
" AND t4.compliance_id is null "
logProcessInfo("compliance_to_start %s" % self.client_id,
query % (self.current_date, self.current_date))
rows = self.select_all(query, [self.current_date, self.current_date])
print query % (self.current_date, self.current_date)
return rows
def begin_process(self):
current_date = datetime.datetime.utcnow().date()
client_info = self.get_client_db_list()
logProcessInfo("DailyProcess", "process begin")
logProcessInfo("DailyProcess", str(current_date))
logProcessInfo("begin_process", client_info)
try:
for c in client_info:
print c
try:
task = AutoStart(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:
logProcessError("DailyProcess", e)
logProcessError("DailyProcess", (traceback.format_exc()))
except Exception, e:
print e
class AutoStart(Database):
def __init__(self, c_db_ip, c_db_username, c_db_password, c_db_name,
c_db_port, client_id, legal_entity_id, current_date):
super(AutoStart, self).__init__(c_db_ip, c_db_port, c_db_username,
c_db_password, c_db_name)
self._c_db_ip = c_db_ip
self._c_db_name = c_db_name
self.connect()
self.client_id = client_id
self.legal_entity_id = legal_entity_id
self.current_date = current_date
self.started_unit_id = []
self.started_user_id = []
def get_email_id_for_users(self, user_id):
q = "SELECT employee_name, email_id from tbl_users where user_id = %s"
logProcessInfo("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
def get_compliance_to_start(self):
query = "SELECT t1.country_id, t1.unit_id, t1.compliance_id, t1.statutory_dates, " + \
" t1.trigger_before_days, t1.due_date, t1.validity_date, " + \
" t2.document_name, t2.compliance_task, t2.frequency_id, t2.repeats_type_id," + \
" t2.repeats_every, (t1.due_date - INTERVAL t1.trigger_before_days DAY) start_date," + \
" t3.unit_id, t3.unit_code, t3.unit_name, t3.business_group_id, " + \
" t3.legal_entity_id, t3.division_id, t2.domain_id, " + \
" t1.assignee, t1.concurrence_person, t1.approval_person, " + \
" t1.compliance_id " + \
" from tbl_assign_compliances t1 " + \
" INNER JOIN tbl_legal_entities as t5 on t1.legal_entity_id = t5.legal_entity_id and t5.is_closed = 0 and t5.contract_to > %s " + \
" INNER JOIN tbl_units t3 on t1.unit_id = t3.unit_id and t3.is_closed = 0 " + \
" INNER JOIN tbl_compliances t2 on t1.compliance_id = t2.compliance_id " + \
" LEFT JOIN tbl_compliance_history t4 ON (t4.unit_id = t1.unit_id " + \
" AND t4.compliance_id = t1.compliance_id AND t2.frequency_id = 1)" + \
" WHERE (t1.due_date - INTERVAL t1.trigger_before_days DAY) <= %s " + \
" AND t1.is_active = 1 AND t2.is_active = 1 AND t2.frequency_id < 5 " + \
" AND t4.compliance_id is null "
logProcessInfo("compliance_to_start %s" % self.client_id,
query % (self.current_date, self.current_date))
rows = self.select_all(query, [self.current_date, self.current_date])
print query % (self.current_date, self.current_date)
return rows
def calculate_next_due_date(self, frequency, statutory_dates, repeat_type,
repeat_every, old_due_date):
# frequency 1: One Time, 2 : Periodical, 3 : Review, 4: On occurance
# repeat_type 1 : Days, 2 : Months, 3 : years
statutory_dates = json.loads(statutory_dates)
trigger_before_days = None
if statutory_dates == []:
statutory_dates = None
print "FREQUENCY----------", frequency
if frequency == 2 or frequency == 3 or frequency == 4:
repeat_every = int(repeat_every)
repeat_type = int(repeat_type)
if statutory_dates is None or len(statutory_dates) == 1:
if repeat_type == 1:
new_due_date = addDays(repeat_every, old_due_date)
elif repeat_type == 2:
new_due_date = addMonth(repeat_every, old_due_date)
elif repeat_type == 3:
new_due_date = addYears(repeat_every, old_due_date)
else:
print "repeat_type not matched"
new_due_date = old_due_date
return (new_due_date, trigger_before_days)
else:
temp_date = convert_string_to_date(str(old_due_date))
old_month = temp_date.month
repeat_type = int(repeat_type)
if repeat_type == 2:
for index, dat in enumerate(statutory_dates):
day = dat["statutory_date"]
month = dat["statutory_month"]
if month is not None and month == old_month:
if index == len(statutory_dates) - 1:
day = statutory_dates[0]["statutory_date"]
month = statutory_dates[0]["statutory_month"]
trigger_before_days = statutory_dates[0][
"trigger_before_days"]
new_due_date = create_new_date(
old_due_date, day, month)
break
else:
day = statutory_dates[index +
1]["statutory_date"]
month = statutory_dates[index +
1]["statutory_month"]
trigger_before_days = statutory_dates[
index + 1]["trigger_before_days"]
new_due_date = create_new_date(
old_due_date, day, month)
break
else:
if (month > old_month):
day = dat["statutory_date"]
month = dat["statutory_month"]
trigger_before_days = dat[
"trigger_before_days"]
new_due_date = create_new_date(
old_due_date, day, month)
break
else:
if index == len(statutory_dates) - 1:
day = statutory_dates[0]["statutory_date"]
month = statutory_dates[0][
"statutory_month"]
trigger_before_days = dat[
"trigger_before_days"]
new_due_date = create_new_date(
old_due_date, day, month)
break
return (new_due_date, trigger_before_days)
else:
return old_due_date, trigger_before_days
def save_in_compliance_history(self, unit_id, compliance_id, start_date,
due_date, next_due_date, assignee,
concurrence, approve):
# compliance_history_id = get_new_id(db, "tbl_compliance_history", "compliance_history_id")
if concurrence is not None:
values = (self.legal_entity_id, unit_id, compliance_id, start_date,
due_date, next_due_date, assignee, approve, concurrence)
query = "INSERT INTO tbl_compliance_history (legal_entity_id, unit_id, compliance_id, " + \
" start_date, due_date, next_due_date, completed_by, approved_by, concurred_by) " + \
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) "
else:
values = (self.legal_entity_id, unit_id, compliance_id, start_date,
due_date, next_due_date, assignee, approve)
query = "INSERT INTO tbl_compliance_history (legal_entity_id, unit_id, compliance_id, " + \
" start_date, due_date, next_due_date, completed_by, approved_by) " + \
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s) "
logProcessInfo("save_new_compliance %s" % self.client_id,
query % values)
compliance_history_id = self.execute_insert(query, values)
return compliance_history_id
def update_assign_compliance_due_date(self, trigger_before, due_date,
unit_id, compliance_id):
query = "UPDATE tbl_assign_compliances set due_date= %s, " + \
" trigger_before_days= %s " + \
" WHERE unit_id = %s AND compliance_id = %s "
values = (due_date, trigger_before, unit_id, compliance_id)
logProcessInfo("update_assigne_compliance", query % values)
self.execute(query, values)
def save_in_notification(self,
country_id,
domain_id,
business_group_id,
legal_entity_id,
division_id,
unit_id,
compliance_id,
assignee,
concurrence_person,
approval_person,
notification_text,
extra_details,
notification_type_id,
notify_to_all=True):
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)
logProcessInfo("save_notification_user %s" % self.client_id,
q % v)
self.execute(q, v)
# notification_id = get_new_id(db, "tbl_notifications_log", "notification_id")
created_on = datetime.datetime.now()
column = [
"country_id", "domain_id", "legal_entity_id", "unit_id",
"compliance_id", "assignee", "approval_person",
"notification_type_id", "notification_text", "extra_details",
"created_on"
]
values = [
country_id, domain_id, legal_entity_id, unit_id, compliance_id,
assignee, approval_person, notification_type_id, notification_text,
extra_details, created_on
]
if business_group_id is not None:
column.append("business_group_id")
values.append(business_group_id)
if division_id is not None:
column.append("division_id")
values.append(division_id)
if concurrence_person is not None:
column.append("concurrence_person")
values.append(concurrence_person)
notification_id = self.insert("tbl_notifications_log", column, values)
if notification_id is False:
return False
save_notification_users(notification_id, assignee)
if notify_to_all:
if approval_person is not None and assignee != approval_person:
save_notification_users(notification_id, approval_person)
if concurrence_person is not None or concurrence_person is not "NULL":
save_notification_users(notification_id, concurrence_person)
def actual_start_date(self, due_date, trigger_before):
if due_date is not None and trigger_before is not None:
d = due_date - timedelta(days=int(trigger_before))
return d
else:
return None
def is_this_first_task_of_year(self, unit_id, country_id, domain_id,
compliance_id):
q = "select month_from, month_to from tbl_client_configuration where country_id = %s and domain_id = %s"
c_row = self.select_one(q, [country_id, domain_id])
print "\n"
print q % (country_id, domain_id)
print c_row
years = []
if c_row["month_from"] == 1 and c_row["month_to"] == 12:
years = [getCurrentYear(), 1, getCurrentYear(), 2]
else:
years = [
getCurrentYear(), c_row["month_from"],
getCurrentYear() + 1, c_row["month_to"]
]
q1 = "select count(0) comp_count from tbl_compliance_history where unit_id = %s and compliance_id = %s " + \
" and due_date >= date(concat_ws('-',%s,%s,1)) " + \
" and due_date <= last_day(date(concat_ws('-',%s,%s,1))) "
param = [unit_id, compliance_id]
param.extend(years)
rows = self.select_one(q1, param)
print q1 % tuple(param)
print rows
if rows:
cnt = rows["comp_count"]
else:
cnt = 0
statutory_date = None
repeats_every = None
repeats_type_id = None
if cnt == 0:
print "count == 0"
q1 = "select repeats_type_id, repeats_every, statutory_date, trigger_before_days, due_date from tbl_compliance_dates where " + \
"compliance_id = %s and unit_id = %s "
d_rows = self.select_one(q1, [compliance_id, unit_id])
print q1 % (compliance_id, unit_id)
print d_rows
if d_rows:
repeats_type_id = d_rows["repeats_type_id"]
repeats_every = d_rows["repeats_every"]
statutory_date = d_rows["statutory_date"]
due_date = d_rows["due_date"]
trigger_days = d_rows["trigger_before_days"]
if statutory_date is not None:
qq = "update tbl_assign_compliances set statutory_dates = %s, trigger_before_days = %s, due_date = %s " + \
" where compliance_id = %s and unit_id = %s"
print qq % (statutory_date, trigger_days, due_date,
compliance_id, unit_id)
self.execute(qq, [
statutory_date, trigger_days, due_date, compliance_id,
unit_id
])
else:
q1 = "select repeats_type_id, repeats_every, statutory_dates, trigger_before_days, due_date from tbl_assign_compliances where " + \
"compliance_id = %s and unit_id = %s "
d_rows = self.select_one(q1, [compliance_id, unit_id])
if d_rows:
repeats_type_id = d_rows["repeats_type_id"]
repeats_every = d_rows["repeats_every"]
statutory_date = d_rows["statutory_dates"]
due_date = d_rows["due_date"]
trigger_days = d_rows["trigger_before_days"]
else:
q1 = "select repeats_type_id, repeats_every, statutory_dates, trigger_before_days, due_date from tbl_assign_compliances where " + \
"compliance_id = %s and unit_id = %s "
d_rows = self.select_one(q1, [compliance_id, unit_id])
if d_rows:
repeats_type_id = d_rows["repeats_type_id"]
repeats_every = d_rows["repeats_every"]
statutory_date = d_rows["statutory_dates"]
due_date = d_rows["due_date"]
trigger_days = d_rows["trigger_before_days"]
print cnt, statutory_date, repeats_every, repeats_type_id
return cnt, statutory_date, repeats_every, repeats_type_id
def start_new_task(self):
def notify(d, due_date, next_due_date, approval_person,
trigger_before):
start_date = self.actual_start_date(due_date, trigger_before)
compliance_history_id = self.save_in_compliance_history(
int(d["unit_id"]),
int(d["compliance_id"]), start_date, due_date, next_due_date,
int(d["assignee"]), d["concurrence_person"],
int(approval_person))
if compliance_history_id is False:
return False
else:
self.started_unit_id.append(d["unit_id"])
self.started_user_id.append(d["assignee"])
self.started_user_id.append(approval_person)
self.started_user_id.append(d["concurrence_person"])
if d["document_name"]:
compliance_name = d["document_name"] + " - " + d[
"compliance_task"]
else:
compliance_name = d["compliance_task"]
unit_name = d["unit_code"] + " - " + d["unit_name"]
notification_text = "Compliance task %s started" % (
compliance_name)
extra_details = " %s - Compliance Started" % (
compliance_history_id)
notification_type_id = 4 # 4 = messages
self.save_in_notification(d["country_id"], d["domain_id"],
d["business_group_id"],
d["legal_entity_id"], d["division_id"],
d["unit_id"], d["compliance_id"],
d["assignee"], d["concurrence_person"],
d["approval_person"], notification_text,
extra_details, notification_type_id)
a_name, assignee_email = self.get_email_id_for_users(d["assignee"])
notify_new_task = threading.Thread(
target=email.notify_compliance_start,
args=[
a_name, compliance_name, unit_name, d["due_date"],
assignee_email
])
notify_new_task.start()
return True
def start_next_due_date_task(d, due_date, approval_person):
next_due_date, trigger_before = self.calculate_next_due_date(
int(d["frequency_id"]), d["statutory_dates"],
d["repeats_type_id"], d["repeats_every"], due_date)
if trigger_before is None:
trigger_before = int(d["trigger_before_days"])
c_h_id = notify(d, due_date, next_due_date, approval_person,
trigger_before)
if c_h_id is False:
logProcessError(
"next_due_date_task %s" % self.client_id,
"compliance history save failed for %s" % (next_due_date))
logProcessError("next_due_date_task %s " % self.client_id,
str(d))
return None, trigger_before
return next_due_date, trigger_before
data = self.get_compliance_to_start()
count = 0
for d in data:
print d["unit_id"]
try:
approval_person = int(d["approval_person"])
if d["frequency_id"] == 1:
next_due_date = "0000-00-00"
trigger_before = d["trigger_before_days"]
if trigger_before is None:
continue
c_h_id = notify(d, d["due_date"], next_due_date,
approval_person, trigger_before)
if c_h_id is False:
# compliance history id is false so continue further
continue
else:
if d["frequency_id"] in [3, 4]:
cnt, statutory_date, repeats_every, repeats_type_id = self.is_this_first_task_of_year(
d["unit_id"], d["country_id"], d["domain_id"],
d["compliance_id"])
if statutory_date is None and repeats_every is None and repeats_type_id is None:
continue
else:
d["statutory_dates"] = statutory_date
d["repeats_type_id"] = repeats_type_id
d["repeats_every"] = repeats_every
next_due_date = trigger_before = None
due_date = d["due_date"]
print due_date
next_due_date, trigger_before = start_next_due_date_task(
d, due_date, approval_person)
print next_due_date
if next_due_date is not None:
self.update_assign_compliance_due_date(
trigger_before, next_due_date, d["unit_id"],
d["compliance_id"])
while (next_due_date - timedelta(days=trigger_before)
) <= self.current_date:
# start for next-due-date
next_due_date, trigger_before = start_next_due_date_task(
d, next_due_date, approval_person)
if next_due_date is None:
break
self.update_assign_compliance_due_date(
trigger_before, next_due_date, d["unit_id"],
d["compliance_id"])
count += 1
except Exception, e:
logProcessError("task_start_failed", str(e))
logProcessError("task_start_failed", d)
logProcessError("task_start_failed",
str(traceback.format_exc()))
continue
print_msg = " %s compliances started for client_id %s - %s" % (
count, self.client_id, self.current_date)
logProcessInfo("start_new_task %s" % self.client_id, str(print_msg))