def get_statutory_notifications_report_data(db, request_data):
country_id = request_data.country_id
domain_id = request_data.domain_id
level_1_statutory_id = request_data.statutory_id_optional
from_date = request_data.from_date_optional
to_date = request_data.to_date_optional
from_count = request_data.from_count
page_count = request_data.page_count
if from_date is not None:
from_date = string_to_datetime(from_date).date()
if to_date is not None:
to_date = string_to_datetime(to_date) + datetime.timedelta(days=1)
to_date = to_date.date()
statutory_notifictions_list = db.call_proc(
"sp_statutory_notification_details", (country_id, domain_id,
level_1_statutory_id, from_date, to_date, from_count, page_count)
)
statutory_notifictions_list = return_statutory_notifications(
statutory_notifictions_list
)
return (
statutory_notifictions_list
)
def save_completed_task_data(db, csv_id, csv_data, client_id):
try:
columns = [
"csv_past_id", "Legal_Entity", "Domain", "Unit_Code", "Unit_Name",
"perimary_legislation", "Secondary_Legislation",
"compliance_task_name", "Compliance_Description",
"Compliance_Frequency", "Statutory_Date", "Due_Date", "Assignee",
"Completion_Date", "document_name"
]
values = []
for idx, d in enumerate(csv_data):
values.append(
(csv_id, d["Legal_Entity"], d["Domain"], d["Unit_Code"],
d["Unit_Name"], d["Primary_Legislation"],
d["Secondary_Legislation"], d["Compliance_Task"],
d["Compliance_Description"],
d["Compliance_Frequency"], d["Statutory_Date"],
string_to_datetime(d["Due_Date"]), d["Assignee"],
string_to_datetime(d["Completion_Date"]), d["Document_Name"]))
if values:
db.bulk_insert("tbl_bulk_past_data", columns, values)
return True
else:
return False
except Exception, e:
print "e>>", str(e)
print "Exception>>", Exception
raise ValueError("Transaction failed")
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_country_domain_timelines(db, country_ids, domain_ids, years):
print years
country_wise_timelines = []
for country_id in country_ids:
domain_wise_timeline = []
for domain_id in domain_ids:
columns = "month_from, month_to"
condition = "country_id = %s and domain_id = %s "
condition_val = [country_id, domain_id]
rows = db.get_data(tblClientConfigurations, columns, condition,
condition_val)
if len(rows) > 0:
month_from = rows[0]["month_from"]
month_to = rows[0]["month_to"]
start_end_dates = []
for year in years:
start_year = year
end_year = year + 1
start_date_string = None
end_date_string = None
start_date_string = "1-%s-%s" % (
db.string_months[month_from], start_year)
start_date = string_to_datetime(start_date_string)
if get_current_date() >= start_date.date():
# current year running
pass
else:
start_year = year - 1
end_year = year
start_date_string = "1-%s-%s" % (
db.string_months[month_from], start_year)
start_date = string_to_datetime(start_date_string)
end_date_string = "%s-%s-%s" % (
db.end_day_of_month[month_to],
db.string_months[month_to], end_year)
end_date = string_to_datetime(end_date_string)
r = relativedelta.relativedelta(
convert_datetime_to_date(end_date),
convert_datetime_to_date(start_date))
if r.years > 0:
end_date = (end_date -
relativedelta.relativedelta(years=1))
start_end_dates.append({
"year": year,
"start_date": start_date,
"end_date": end_date
})
domain_wise_timeline.append([domain_id, start_end_dates])
country_wise_timelines.append([country_id, domain_wise_timeline])
print country_wise_timelines
return country_wise_timelines
def get_domainwise_agreement_report_count(
db, country_id, client_id, business_group_id,
legal_entity_id, domain_id, contract_from, contract_to, session_user
):
if contract_from is not None:
contract_from = string_to_datetime(contract_from).date()
if contract_to is not None:
contract_to = string_to_datetime(contract_to).date()
domainwise_agreement_list_count = db.call_proc(
"sp_domainwise_agreement_details_count", (country_id, client_id, business_group_id,
legal_entity_id, domain_id, contract_from, contract_to, session_user)
)
return domainwise_agreement_list_count[0]["total_record"]
def get_country_domain_timelines_dict(db,
country_ids,
domain_ids,
years,
client_id=None):
country_wise_timelines = {}
for country_id in country_ids:
domain_wise_timeline = {}
for domain_id in domain_ids:
columns = "month_from, month_to"
condition = "country_id = %s and domain_id = %s "
condition_val = [country_id, domain_id]
rows = db.get_data(tblClientConfigurations, columns, condition,
condition_val)
if len(rows) > 0:
month_from = rows[0]["month_from"]
month_to = rows[0]["month_to"]
start_end_dates = {}
for year in years:
year = int(year)
start_year = year
end_year = year + 1
start_date_string = None
end_date_string = None
start_date_string = "1-%s-%s" % (
db.string_months[month_from], start_year)
start_date = string_to_datetime(start_date_string)
end_date_string = "%s-%s-%s" % (
db.end_day_of_month[month_to],
db.string_months[month_to], end_year)
end_date = string_to_datetime(end_date_string)
r = relativedelta.relativedelta(
convert_datetime_to_date(end_date),
convert_datetime_to_date(start_date))
if r.years > 0:
end_date = (end_date -
relativedelta.relativedelta(years=1))
start_end_dates[year] = {
"start_date": start_date,
"end_date": end_date
}
domain_wise_timeline[domain_id] = start_end_dates
country_wise_timelines[country_id] = domain_wise_timeline
return country_wise_timelines
def get_domainwise_agreement_report(db, country_id, client_id, business_group_id,
legal_entity_id, domain_id, contract_from, contract_to,
from_count, page_count, session_user):
if contract_from is not None:
contract_from = string_to_datetime(contract_from).date()
if contract_to is not None:
contract_to = string_to_datetime(contract_to).date()
domainwise_agreement_list = db.call_proc(
"sp_domainwise_agreement_details", (country_id, client_id, business_group_id,
legal_entity_id, domain_id, contract_from, contract_to, from_count, page_count, session_user)
)
domainwise_agreement_list = return_domainwise_agreement_report(
domainwise_agreement_list
)
return (
domainwise_agreement_list
)
def get_statutory_notifications_report_count(
db, request_data
):
country_id = request_data.country_id
domain_id = request_data.domain_id
level_1_statutory_id = request_data.statutory_id_optional
from_date = request_data.from_date_optional
to_date = request_data.to_date_optional
if from_date is not None:
from_date = string_to_datetime(from_date).date()
if to_date is not None:
to_date = string_to_datetime(to_date) + datetime.timedelta(days=1)
to_date = to_date.date()
statutory_notifictions_list_count = db.call_proc(
"sp_statutory_notification_details_count", (
country_id, domain_id, level_1_statutory_id, from_date, to_date
)
)
return statutory_notifictions_list_count[0]["total_record"]
def save_completed_task_current_year_csv(db, completed_task, session_user):
columns = [
"client_id", "legal_entity_id", "domain_id", "unit_id_id",
"client_group", "csv_name", "uploaded_by", "uploaded_on",
"total_records", "total_documents", "uploaded_documents",
"upload_status"
]
# print "completed_task[7]>>", completed_task[7]
# print "string_to_datetime(completed_task[7])>>", string_to_datetime(completed_task[7])
values = [
completed_task[0], completed_task[1], completed_task[2],
completed_task[3], completed_task[4], completed_task[5],
completed_task[6],
string_to_datetime(completed_task[7]), completed_task[8],
completed_task[9], completed_task[10], completed_task[11]
]
completed_task_id = db.insert("tbl_bulk_past_data_csv", columns, values)
return completed_task_id
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_client_details_report(
db, request, user_id
):
country_id = request.country_id
client_id = request.client_id
legal_entity_id = request.legal_entity_id
client_details_none_values = request.u_m_none
bgrp_id = domain_id = org_id = unit_id = from_date = to_date = unit_status = None
if client_details_none_values.find(",") > 0:
bgrp_id = client_details_none_values.split(",")[0]
domain_id = client_details_none_values.split(",")[2]
org_id = client_details_none_values.split(",")[3]
unit_id = client_details_none_values.split(",")[1]
from_date = client_details_none_values.split(",")[4]
if from_date == '%' :
from_date = None
else:
from_date = string_to_datetime(from_date).date()
to_date = client_details_none_values.split(",")[5]
if to_date == '%' :
to_date = None
else:
to_date = string_to_datetime(to_date).date()
# if to_date is not None or from_date != 'null':
# print "b"
# to_date = string_to_datetime(from_date).date()
unit_status = client_details_none_values.split(",")[6]
client_details_dataset = []
expected_result = 3
print "dates"
print from_date, to_date
client_details_dataset = db.call_proc_with_multiresult_set("sp_client_details_report_export_unitlist", (
user_id, country_id, client_id, legal_entity_id, bgrp_id, domain_id, org_id,
unit_id, from_date, to_date, unit_status), expected_result)
unit_details = unit_domains = {}
units_list = []
if(len(client_details_dataset) > 0):
if(len(client_details_dataset[1]) > 0):
unit_details = client_details_dataset[1]
if(len(client_details_dataset[2]) > 0):
unit_domains = client_details_dataset[2]
for units in unit_details:
unit_code = units.get("unit_code")
unit_name = units.get("unit_name")+"|"+str(units.get("closed_days"))
division_name = units.get("division_name")
if units.get("division_name") is None:
division_name = "-Nil-"
category_name = units.get("category_name")
if units.get("category_name") is None:
category_name = "-Nil-"
created_by = units.get("emp_code_name")
created_on = units.get("created_on")
# domain_names = []
# for domain in unit_domains:
# if (units.get("unit_id") == domain.get("unit_id")):
# domain_names.append(domain.get("domain_name")+"-"+domain.get("organisation_name"))
# d_o_names = ",".join(domain_names)
domain_names = []
last = object()
org_names = None
for domain in unit_domains:
if (units.get("unit_id") == domain.get("unit_id")):
if last != domain.get("domain_name") :
if org_names is not None:
domain_names.append(last + " - " + org_names + "\n")
org_names = None
last = domain.get("domain_name")
if org_names is None:
org_names = domain.get("organisation_name")
else:
org_names = org_names + "," + domain.get("organisation_name")
else:
if org_names is None:
org_names = domain.get("organisation_name")
else:
org_names = org_names + "," + domain.get("organisation_name")
if org_names is not None:
domain_names.append(last + " - " + org_names + "\n")
d_o_names = ",".join(domain_names)
units_list.append(technoreports.ClientUnitList(
int(units.get("country_id")), int(units.get("client_id")), int(units.get("legal_entity_id")),
units.get("business_group_id"), int(units.get("unit_id")), unit_code, unit_name,
units.get("address"), int(units.get("postal_code")), bool(units.get("is_active")),
units.get("closed_on"), units.get("check_date"), created_by, created_on, division_name,
category_name, d_o_names
))
return units_list
