def robot_report_audit_sir(s_year: str = "", s_type: str = "", s_name: str = "", s_mail: str = ""):
"""
SEARCH VSS.PARTY FOR NAMES, NUMBERS AND ID'S
:param s_year: Working year
:param s_type: Report type
:param s_name: The name of the requester / recipient
:param s_mail: The requester mail address
:return: str: Info in message format
"""
from datetime import datetime
# DECLARE VARIABLES
l_debug: bool = False
"""*************************************************************************
ENVIRONMENT
*************************************************************************"""
if l_debug:
print("ENVIRONMENT")
# DECLARE VARIABLES
re_path: str = "R:/Audit/" # Results
l_mess: bool = funcconf.l_mess_project
# l_mess: bool = False
s_file: str = "Report_assignment_sir.csv"
s_message: str = "Special investigation work in progress (sir) report for year " + s_year + " "
# LOG
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: " + s_function.upper())
funcfile.writelog("-" * len("script: "+s_function))
if l_debug:
print(s_function.upper())
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator", "<b>" + s_function.upper() + "</b>")
"""****************************************************************************
OPEN THE DATABASES
****************************************************************************"""
if l_debug:
print("OPEN THE MYSQL DATABASE")
funcfile.writelog("OPEN THE MYSQL DATABASE")
# VARIABLES
s_source_database: str = "Web_ia_nwu"
# OPEN THE SOURCE FILE
ms_from_connection = funcmysql.mysql_open(s_source_database)
ms_from_cursor = ms_from_connection.cursor()
funcfile.writelog("%t OPEN MYSQL DATABASE: " + s_source_database)
"""*****************************************************************************
BUILD THE REPORT
*****************************************************************************"""
funcfile.writelog("BUILD THE REPORT")
if l_debug:
print("BUILD THE REPORT")
# BUILD THE HEADINGS
s_line: str = "AUDITOR,"
s_line += "YEAR,"
s_line += "ASSIGNMENT,"
s_line += "CASE_YEAR,"
s_line += "CASE_NUMBER,"
s_line += "BOX_YEAR,"
s_line += "BOX_NUMBER,"
s_line += "REPORTED_DATE,"
s_line += "DATE_21,"
s_line += "DATE_90,"
s_line += "START_DATE,"
s_line += "DUE_DATE,"
s_line += "COMPLETE_DATE,"
s_line += "TYPE,"
s_line += "PRIORITY,"
s_line += "ACCUSED,"
s_line += "ISSUE,"
s_line += "STATUS,"
s_line += "DEPARTMENT,"
s_line += "REFERENCE,"
s_line += "VALUE,"
s_line += "NOTES_OFFICIAL,"
s_line += "NOTES_OWN"
funcfile.writelog(s_line, re_path, s_file)
# BUILD THE WHERE CLAUSE
if s_year != "":
s_where = "ia_assi_year = " + s_year + " and ia.ia_assicate_auto = 9"
s_where += " or "
s_where += "ia_assi_year < " + s_year + " and ia.ia_assicate_auto = 9 and ia_assi_priority < 8"
else:
s_where = "ia_assi_year = " + funcdate.cur_year() + " and ia.ia_assicate_auto = 9"
s_where += " or "
s_where += "ia_assi_year < " + funcdate.cur_year() + " and ia.ia_assicate_auto = 9 and ia_assi_priority < 8"
# BUILD THE SQL QUERY
s_sql = """
SELECT
us.ia_user_name,
ia.ia_assi_year,
ia.ia_assi_name,
ia.ia_assi_si_caseyear,
ia.ia_assi_si_casenumber,
ia.ia_assi_si_boxyear,
ia.ia_assi_si_boxnumber,
ia.ia_assi_si_reportdate,
ia.ia_assi_si_report1date,
ia.ia_assi_si_report2date,
ia.ia_assi_startdate,
ia.ia_assi_completedate,
ia.ia_assi_finishdate,
at.ia_assitype_name,
ia.ia_assi_priority,
ia.ia_assi_si_accused,
ia.ia_assi_si_issue,
st.ia_assistat_name,
co.ia_assicond_name,
ia.ia_assi_si_reference,
ia.ia_assi_si_value,
ia.ia_assi_offi,
ia.ia_assi_desc,
ia.ia_assi_auto,
ia.ia_assicate_auto
FROM
ia_assignment ia Left Join
ia_user us On us.ia_user_sysid = ia.ia_user_sysid Left Join
ia_assignment_type at On at.ia_assitype_auto = ia.ia_assitype_auto Left Join
ia_assignment_status st On st.ia_assistat_auto = ia.ia_assistat_auto Left Join
ia_assignment_conducted co On co.ia_assicond_auto = ia.ia_assicond_auto
WHERE %WHERE%
ORDER BY
ia_user_name,
ia_assi_year,
ia_assi_completedate,
ia_assitype_name,
ia_assi_priority desc,
ia_assi_name
;
"""
s_sql = s_sql.replace("%WHERE%", s_where)
if l_debug:
print(s_sql)
# BUILD THE ASSIGNMENT RECORD
for row in ms_from_cursor.execute(s_sql).fetchall():
# USER NAME
if l_debug:
print(row[0])
s_line = '"' + row[0] + '",'
# YEAR
if l_debug:
print(row[1])
s_line += str(row[1]) + ","
# NAME
if l_debug:
print(row[2])
print(row[23])
s_line += '"' + row[2].replace(",", "") + ' (' + str(row[23]) + ')",'
# CASE YEAR
if l_debug:
print(row[3])
s_line += str(row[3]) + ","
# CASE NUMBER
if l_debug:
print(row[4])
s_line += str(row[4]) + ","
# BOX YEAR
if l_debug:
print(row[5])
s_line += str(row[5]) + ","
# BOX NUMBER
if l_debug:
print(row[6])
s_line += str(row[6]) + ","
# REPORTED DATE
if l_debug:
print(row[7])
if row[7]:
s_line += datetime.strftime(row[7], "%Y-%m-%d") + ","
else:
s_line += ","
# 21 DATE
if l_debug:
print(row[8])
s_line += '"' + row[8] + '",'
# 90 DATE
if l_debug:
print(row[9])
s_line += '"' + row[9] + '",'
# START DATE
if l_debug:
print(row[10])
if row[10]:
s_line += datetime.strftime(row[10], "%Y-%m-%d") + ","
else:
s_line += ","
# DUE DATE
if l_debug:
print(row[11])
if row[11]:
s_line += datetime.strftime(row[11], "%Y-%m-%d") + ","
else:
s_line += ","
# FINISH DATE
if l_debug:
print(row[12])
if row[12]:
s_line += datetime.strftime(row[12], "%Y-%m-%d") + ","
else:
s_line += ","
# TYPE
if l_debug:
print(row[13])
s_line += '"' + row[13] + '",'
# PRIORITY
if l_debug:
print(row[14])
s_priority: str = '"Inactive"'
if row[14] == "9":
s_priority = '"Closed"'
elif row[14] == "8":
s_priority = '"Continuous"'
elif row[14] == "4":
s_priority = '"Follow-up"'
elif row[14] == "3":
s_priority = '"High"'
elif row[14] == "2":
s_priority = '"Medium"'
elif row[14] == "1":
s_priority = '"Low"'
s_line += s_priority + ","
# ACCUSED
if l_debug:
print(row[15])
s_line += '"' + row[15] + '",'
# ISSUE
if l_debug:
print(row[16])
s_data = row[16].replace(",", "")
s_data = s_data.replace("'", "")
s_data = s_data.replace('"', "")
s_line += '"' + s_data + '",'
# STATUS
if l_debug:
print(row[17])
s_line += '"' + row[17] + '",'
# DEPARTMENT
if l_debug:
print(row[18])
s_line += '"' + row[18] + '",'
# REFERENCE
if l_debug:
print(row[19])
s_data = row[19].replace(",", "")
s_data = s_data.replace("'", "")
s_data = s_data.replace('"', "")
s_line += '"' + s_data + '",'
# VALUE
if l_debug:
print(row[20])
print(s_line)
s_line += str(row[20]) + ","
if l_debug:
print(s_line)
# NOTES_OFFICIAL
if l_debug:
print(row[21])
s_line += '"' + row[21] + '",'
# s_data = row[21].replace(",", "")
# s_data = s_data.replace("'", "")
# s_data = s_data.replace('"', "")
# s_line += '"' + s_data + '",'
# NOTES_OWN
if l_debug:
print(row[22])
s_data = row[22].replace(",", "")
s_data = s_data.replace("'", "")
s_data = s_data.replace('"', "")
s_line += '"' + s_data + '"'
# WRITE TO FILE
funcfile.writelog(s_line, re_path, s_file)
if l_debug:
print(s_line)
funcfile.writelog("%t Special investigation sir report requested by " + s_name)
s_report = "Include all special investigations for the year mentioned and "
s_report += "all previous special investigations with an unclosed priority."
# MAIL THE AUDIT REPORT
if s_name != "" and s_mail != "":
funcfile.writelog("%t Special investigation sir report mailed to " + s_mail)
if l_debug:
print("Send the report...")
s_body: str = "Attached please find special investigation progress (sir) report as requested for " + \
s_year + "."
s_body += "\n\r"
s_body += s_report
funcmail.send(s_name,
s_mail,
"E",
"Report special investigation progress (sir) " + s_year,
s_body,
re_path,
s_file)
s_message += " was mailed to " + s_mail
# POPULATE THE RETURN MESSAGE
s_return_message = s_message
# DELETE THE MAILED FILE
if funcfile.file_delete(re_path, s_file):
funcfile.writelog("%t Special investigation sir report deleted")
if l_debug:
print("Delete the report...")
"""*****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# CLOSE THE LOG WRITER
funcfile.writelog("-" * len("completed: "+s_function))
funcfile.writelog("COMPLETED: " + s_function.upper())
return s_return_message[0:4096]
def robot_report_audit_assignment(s_number: str = "",
s_name: str = "",
s_mail: str = ""):
"""
SEARCH VSS.PARTY FOR NAMES, NUMBERS AND ID'S
:param s_number: Assignment number
:param s_name: The name of the requester / recipient
:param s_mail: The requester mail address
:return: str: Info in message format
"""
# VARIABLES
# s_function: str = "D002_report_audit_assignment"
l_debug: bool = False
"""*************************************************************************
ENVIRONMENT
*************************************************************************"""
if l_debug:
print("ENVIRONMENT")
# DECLARE VARIABLES
re_path: str = "R:/Audit/" # Results
l_mess: bool = funcconf.l_mess_project
# l_mess: bool = False
s_file: str = "Report_assignment_" + s_number + ".html"
s_report: str = ""
s_message: str = "Audit assignment report (" + s_number + ") "
# LOG
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: " + s_function.upper())
funcfile.writelog("-" * len("script: " + s_function))
if l_debug:
print(s_function.upper())
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator",
"<b>" + s_function.upper() + "</b>")
"""****************************************************************************
OPEN THE DATABASES
****************************************************************************"""
if l_debug:
print("OPEN THE MYSQL DATABASE")
funcfile.writelog("OPEN THE MYSQL DATABASE")
# VARIABLES
s_source_database: str = "Web_ia_nwu"
# OPEN THE SOURCE FILE
ms_from_connection = funcmysql.mysql_open(s_source_database)
ms_from_cursor = ms_from_connection.cursor()
funcfile.writelog("%t OPEN MYSQL DATABASE: " + s_source_database)
"""*****************************************************************************
BUILD THE REPORT
*****************************************************************************"""
funcfile.writelog("BUILD THE REPORT")
if l_debug:
print("BUILD THE REPORT")
# BUILD THE ASSIGNMENT RECORD
for row in ms_from_cursor.execute("SELECT "
"ia_assi_name, "
"ia_assi_report "
"FROM ia_assignment "
"WHERE ia_assi_auto = " + s_number +
";").fetchall():
if l_debug:
print(row[0])
funcfile.writelog("%t Audit assignment report " + s_number +
" requested by " + s_name)
s_report = row[0]
s_message += s_report + " was mailed to " + s_mail
if row[1] != "":
funcfile.writelog(row[1], re_path, s_file)
# BUILD THE FINDING RECORD
for row in ms_from_cursor.execute("SELECT "
"ia_find_name, "
"ia_find_desc, "
"ia_find_criteria, "
"ia_find_procedure, "
"ia_find_condition, "
"ia_find_effect, "
"ia_find_cause, "
"ia_find_risk, "
"ia_find_recommend, "
"ia_find_comment, "
"ia_find_frequency, "
"ia_find_definition, "
"ia_find_reference "
"FROM ia_finding "
"WHERE ia_assi_auto = " + s_number +
";").fetchall():
if l_debug:
print(row[0])
# if row[0] != "":
# funcfile.writelog(row[0], re_path, s_file)
# DESCRIPTION
if row[1] != "":
funcfile.writelog(row[1], re_path, s_file)
# CRITERIA
if row[2] != "":
funcfile.writelog(row[2], re_path, s_file)
# PROCEDURE
if row[3] != "":
funcfile.writelog(row[3], re_path, s_file)
# CONDITION
if row[4] != "":
funcfile.writelog(row[4], re_path, s_file)
# EFFECT
if row[5] != "":
funcfile.writelog(row[5], re_path, s_file)
# CAUSE
if row[6] != "":
funcfile.writelog(row[6], re_path, s_file)
# RISK
if row[7] != "":
funcfile.writelog(row[7], re_path, s_file)
# RECOMMEND
if row[8] != "":
funcfile.writelog(row[8], re_path, s_file)
# COMMENT
if row[9] != "":
funcfile.writelog(row[9], re_path, s_file)
# FREQUENCY
if row[10] != "":
funcfile.writelog(row[10], re_path, s_file)
# DEFINITION
if row[11] != "":
funcfile.writelog(row[11], re_path, s_file)
# REFERENCE
if row[12] != "":
funcfile.writelog(row[12], re_path, s_file)
# MAIL THE AUDIT REPORT
if s_name != "" and s_mail != "":
funcfile.writelog("%t Audit assignment report " + s_number +
" mailed to " + s_mail)
if l_debug:
print("Send the report...")
s_body: str = "Attached please find audit assignment report as requested:\n\r"
s_body += "\n\r"
s_body += s_report
funcmail.send(s_name, s_mail, "E",
"Report audit assignment number (" + s_number + ")",
s_body, re_path, s_file)
# POPULATE THE RETURN MESSAGE
s_return_message = s_message
# DELETE THE MAILED FILE
if funcfile.file_delete(re_path, s_file):
funcfile.writelog("%t Audit assignment report " + s_number +
" deleted")
if l_debug:
print("Delete the report...")
"""*****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# CLOSE THE LOG WRITER
funcfile.writelog("-" * len("completed: " + s_function))
funcfile.writelog("COMPLETED: " + s_function.upper())
return s_return_message[0:4096]
def robot_report_person_leave(s_nwu: str = "",
s_name: str = "",
s_mail: str = ""):
"""
REPORT EMPLOYEE PERSON LEAVE
:param s_nwu: NWU Number
:param s_name: The name of the requester / recipient
:param s_mail: The requester mail address
:return: str: Info in message format
"""
# IMPORT PYTHON MODULES
import sqlite3
from datetime import datetime
# IMPORT OWN MODULES
from _my_modules import funccsv
from _my_modules import funcdate
from _my_modules import funcfile
from _my_modules import funcmail
from _my_modules import funcsms
from _my_modules import funcstat
# DECLARE VARIABLES
l_debug: bool = True
"""*************************************************************************
ENVIRONMENT
*************************************************************************"""
if l_debug:
print("ENVIRONMENT")
# DECLARE VARIABLES
s_description: str = "Leave report"
so_path: str = "W:/People_leave/" # Source database path
so_file: str = "People_leave.sqlite" # Source database
re_path: str = "R:/People/" + funcdate.cur_year() + "/" # Results
l_mess: bool = funcconf.l_mess_project
# l_mess: bool = False
l_mailed: bool = False
# LOG
if l_debug:
print(s_function.upper())
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: " + s_function.upper())
funcfile.writelog("-" * len("script: " + s_function))
funcfile.writelog("%t " + s_description + " for " + s_nwu +
" requested by " + s_name)
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator",
"<b>" + s_function.upper() + "</b>")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
funcfile.writelog("OPEN THE DATABASES")
if l_debug:
print("OPEN THE DATABASES")
# OPEN THE WORKING DATABASE
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
# ATTACH DATA SOURCES
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
""" ****************************************************************************
BUILD THE LEAVE REPORT
*****************************************************************************"""
funcfile.writelog("BUILD THE LEAVE REPORT")
if l_debug:
print("BUILD THE LEAVE REPORT")
# OBTAIN THE NAME OF THE PERSON
s_lookup_name = funcfile.get_field_value(
so_curs, "PEOPLE.X000_PEOPLE",
"name_address||' ('||preferred_name||')' ",
"employee_number = '" + s_nwu + "'")
if l_debug:
print("FIELD LOOKUP: " + s_name)
s_message: str = s_description + " for <b>" + s_lookup_name + '(' + s_nwu + ")</b>."
# BUILD THE TABLE
if l_debug:
print("Build leave table...")
s_file_prefix: str = "Y000_"
s_file_name: str = "report_leave_all"
sr_file = s_file_prefix + s_file_name
so_curs.execute("Drop table if exists " + sr_file)
s_sql = "Create table " + sr_file + " AS " + """
Select
aa.ABSENCE_ATTENDANCE_ID,
aa.EMPLOYEE_NUMBER,
pe.name_address As EMPLOYEE_NAME,
aa.BUSINESS_GROUP_ID,
aa.DATE_NOTIFICATION,
aa.DATE_START,
aa.DATE_END,
aa.ABSENCE_DAYS,
aa.ABSENCE_ATTENDANCE_TYPE_ID,
at.NAME AS LEAVE_TYPE,
aa.ABS_ATTENDANCE_REASON_ID,
ar.NAME AS LEAVE_REASON,
ar.MEANING AS REASON_DESCRIP,
aa.AUTHORISING_PERSON_ID,
ap.name_address AS AUTHORISE_NAME,
aa.ABSENCE_HOURS,
aa.OCCURRENCE,
aa.SSP1_ISSUED,
aa.PROGRAM_APPLICATION_ID,
aa.ATTRIBUTE1,
aa.ATTRIBUTE2,
aa.ATTRIBUTE3,
aa.ATTRIBUTE4,
aa.ATTRIBUTE5,
aa.LAST_UPDATE_DATE,
aa.LAST_UPDATED_BY,
aa.LAST_UPDATE_LOGIN,
aa.CREATED_BY,
aa.CREATION_DATE,
aa.REASON_FOR_NOTIFICATION_DELAY,
aa.ACCEPT_LATE_NOTIFICATION_FLAG,
aa.OBJECT_VERSION_NUMBER,
at.INPUT_VALUE_ID,
at.ABSENCE_CATEGORY,
at.MEANING AS TYPE_DESCRIP
FROM
X100_Per_absence_attendances aa Left Join
X102_Per_absence_attendance_types at ON at.ABSENCE_ATTENDANCE_TYPE_ID = aa.ABSENCE_ATTENDANCE_TYPE_ID Left Join
X101_Per_abs_attendance_reasons ar ON ar.ABS_ATTENDANCE_REASON_ID = aa.ABS_ATTENDANCE_REASON_ID Left Join
PEOPLE.X000_PEOPLE pe On pe.employee_number = aa.EMPLOYEE_NUMBER Left Join
PEOPLE.X000_PEOPLE ap On pe.person_id = aa.AUTHORISING_PERSON_ID
Where
aa.EMPLOYEE_NUMBER = '%PERSON%'
Order By
aa.EMPLOYEE_NUMBER,
aa.DATE_START,
aa.DATE_END
;"""
s_sql = s_sql.replace("%PERSON%", s_nwu)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# RECORDS FOUND
if funcsys.tablerowcount(so_curs, sr_file) > 0:
# BUILD THE MESSAGE
l_records = funcstat.stat_list(
so_curs, sr_file,
"DATE_START||' '||DATE_END||' ('||ABSENCE_DAYS||') '||LEAVE_TYPE")
s_message += '\n\n'
s_message += 'Leave periods for this and previous year:'
s_data: str = ''
for item in l_records:
s_data = ''
for element in item:
s_data += element
print(s_data)
print(funcdate.cur_year())
if funcdate.cur_year() in s_data or funcdate.prev_year() in s_data:
s_message += '\n'
s_message += s_data
# EXPORT RECORDS
print("Export findings...")
sx_path = re_path
sx_file = sr_file + "_"
sx_file_dated = sx_file + funcdate.today_file()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file, s_head)
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file_dated,
s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file_dated)
# MAIL THE REPORT
s_report = "All DIY leave included!"
if s_name != "" and s_mail != "":
l_mailed = True
funcfile.writelog("%t Leave report mailed to " + s_mail)
if l_debug:
print("Send the report...")
s_body: str = "Attached please find leave report for " + s_lookup_name + " (" + s_nwu + ")."
s_body += "\n\r"
s_body += s_report
funcmail.send(s_name, s_mail, "E", s_description + " for " + s_nwu,
s_body, re_path, sx_file_dated + ".csv")
# DELETE THE MAILED FILE
if funcfile.file_delete(re_path, sx_file_dated):
funcfile.writelog("%t Leave deleted")
if l_debug:
print("Delete the report...")
else:
s_message += "\n\n"
s_message += "No leave on record."
# POPULATE THE RETURN MESSAGE
if l_mailed:
s_message += "\n\n"
s_message += "Report was mailed to " + s_mail + "."
s_return_message = s_message
"""*****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# CLOSE THE LOG WRITER
funcfile.writelog("-" * len("completed: " + s_function))
funcfile.writelog("COMPLETED: " + s_function.upper())
return s_return_message[0:4096]
def robot_report_person_conflict(s_nwu: str = "",
s_name: str = "",
s_mail: str = ""):
"""
REPORT EMPLOYEE PERSON CONFLICT OF INTERESTS
:param s_nwu: NWU Number
:param s_name: The name of the requester / recipient
:param s_mail: The requester mail address
:return: str: Info in message format
"""
# IMPORT PYTHON MODULES
import sqlite3
from datetime import datetime
# IMPORT OWN MODULES
from _my_modules import funccsv
from _my_modules import funcdate
from _my_modules import funcfile
from _my_modules import funcmail
from _my_modules import funcsms
from _my_modules import funcstat
# DECLARE VARIABLES
l_debug: bool = True
"""*************************************************************************
ENVIRONMENT
*************************************************************************"""
if l_debug:
print("ENVIRONMENT")
# DECLARE VARIABLES
s_description: str = "Conflict of interest reports"
so_path: str = "W:/People_conflict/" # Source database path
so_file: str = "People_conflict.sqlite" # Source database
re_path: str = "R:/People/" + funcdate.cur_year() + "/" # Results
l_mess: bool = funcconf.l_mess_project
# l_mess: bool = False
l_mailed: bool = False
# LOG
if l_debug:
print(s_function.upper())
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: " + s_function.upper())
funcfile.writelog("-" * len("script: " + s_function))
funcfile.writelog("%t " + s_description + " for " + s_nwu +
" requested by " + s_name)
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator",
"<b>" + s_function.upper() + "</b>")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
funcfile.writelog("OPEN THE DATABASES")
if l_debug:
print("OPEN THE DATABASES")
# OPEN THE WORKING DATABASE
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
# ATTACH DATA SOURCES
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
""" ****************************************************************************
BUILD THE DECLARATIONS REPORT
*****************************************************************************"""
funcfile.writelog("BUILD THE DECLARATIONS REPORT")
if l_debug:
print("BUILD THE DECLARATIONS REPORT")
# OBTAIN THE NAME OF THE PERSON
s_lookup_name = funcfile.get_field_value(
so_curs, "PEOPLE.X000_PEOPLE",
"name_address||' ('||preferred_name||')' ",
"employee_number = '" + s_nwu + "'")
if l_debug:
print("FIELD LOOKUP: " + s_name)
s_message: str = s_description + " for <b>" + s_lookup_name + '(' + s_nwu + ")</b>."
# BUILD THE TABLE
if l_debug:
print("Build declarations table...")
s_file_prefix: str = "Y000_"
s_file_name: str = "report_declarations_all"
sr_file = s_file_prefix + s_file_name
so_curs.execute("Drop table if exists " + sr_file)
s_sql = "Create table " + sr_file + " AS " + """
Select
d.DECLARATION_ID,
d.EMPLOYEE_NUMBER,
p.name_full As NAME_FULL,
d.DECLARATION_DATE,
d.UNDERSTAND_POLICY_FLAG,
d.INTEREST_TO_DECLARE_FLAG,
d.FULL_DISCLOSURE_FLAG,
Upper(d.STATUS) As STATUS,
d.LINE_MANAGER,
m.name_full As MANAGER_NAME_FULL,
d.REJECTION_REASON,
d.CREATION_DATE,
d.AUDIT_USER,
d.LAST_UPDATE_DATE,
d.LAST_UPDATED_BY,
d.EXTERNAL_REFERENCE
From
X000_declarations_all d Left Join
PEOPLE.X000_PEOPLE p On p.employee_number = d.EMPLOYEE_NUMBER Left Join
PEOPLE.X000_PEOPLE m On m.employee_number = d.LINE_MANAGER
Where
d.EMPLOYEE_NUMBER = '%PERSON%'
Order By
d.LAST_UPDATE_DATE
;"""
s_sql = s_sql.replace("%PERSON%", s_nwu)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# RECORDS FOUND
if funcsys.tablerowcount(so_curs, sr_file) > 0:
# BUILD THE MESSAGE
l_records = funcstat.stat_list(
so_curs, sr_file,
"DECLARATION_DATE||' ('||INTEREST_TO_DECLARE_FLAG||') '||STATUS")
s_message += '\n\n'
s_message += 'Declarations on:'
for item in l_records:
s_message += '\n'
for element in item:
s_message += element
# EXPORT RECORDS
print("Export findings...")
sx_path = re_path
sx_file = sr_file + "_"
sx_file_dated = sx_file + funcdate.today_file()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file, s_head)
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file_dated,
s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file_dated)
# MAIL THE REPORT
s_report = "All DIY declarations included!"
if s_name != "" and s_mail != "":
l_mailed = True
funcfile.writelog("%t Declarations mailed to " + s_mail)
if l_debug:
print("Send the report...")
s_body: str = "Attached please find conflict of interest declarations for " + s_nwu + "."
s_body += "\n\r"
s_body += s_report
funcmail.send(s_name, s_mail, "E", s_description + " for " + s_nwu,
s_body, re_path, sx_file_dated + ".csv")
# DELETE THE MAILED FILE
if funcfile.file_delete(re_path, sx_file_dated):
funcfile.writelog("%t Declarations deleted")
if l_debug:
print("Delete the report...")
else:
s_message += "\n\n"
s_message += "No declarations on record."
""" ****************************************************************************
BUILD THE INTERESTS REPORT
*****************************************************************************"""
funcfile.writelog("BUILD THE INTERESTS REPORT")
if l_debug:
print("BUILD THE INTERESTS REPORT")
# BUILD THE TABLE
if l_debug:
print("Build interests table...")
s_file_prefix: str = "Y000_"
s_file_name: str = "report_interests_all"
sr_file = s_file_prefix + s_file_name
so_curs.execute("Drop table if exists " + sr_file)
s_sql = "Create table " + sr_file + " AS " + """
Select
i.INTEREST_ID,
i.DECLARATION_ID,
i.EMPLOYEE_NUMBER,
p.name_full As NAME_FULL,
i.DECLARATION_DATE,
i.CONFLICT_TYPE_ID,
Upper(i.CONFLICT_TYPE) As CONFLICT_TYPE,
i.INTEREST_TYPE_ID,
Upper(i.INTEREST_TYPE) As INTEREST_TYPE,
i.STATUS_ID,
Upper(i.INTEREST_STATUS) As INTEREST_STATUS,
i.PERC_SHARE_INTEREST,
Upper(i.ENTITY_NAME) As ENTITY_NAME,
i.ENTITY_REGISTRATION_NUMBER,
Upper(i.OFFICE_ADDRESS) As OFFICE_ADDRESS,
Upper(i.DESCRIPTION) As DESCRIPTION,
i.DIR_APPOINTMENT_DATE,
i.LINE_MANAGER,
m.name_full As MANAGER_NAME_FULL,
i.NEXT_LINE_MANAGER,
i.INDUSTRY_CLASS_ID,
Upper(i.INDUSTRY_TYPE) As INDUSTRY_TYPE,
i.TASK_PERF_AGREEMENT,
i.MITIGATION_AGREEMENT,
i.REJECTION_REASON,
i.CREATION_DATE,
i.AUDIT_USER,
i.LAST_UPDATE_DATE,
i.LAST_UPDATED_BY,
i.EXTERNAL_REFERENCE
From
X000_interests_all i Left Join
PEOPLE.X000_PEOPLE p On p.employee_number = i.EMPLOYEE_NUMBER Left Join
PEOPLE.X000_PEOPLE m On m.employee_number = i.LINE_MANAGER
Where
i.EMPLOYEE_NUMBER = '%PERSON%'
Order By
i.LAST_UPDATE_DATE
;"""
s_sql = s_sql.replace("%PERSON%", s_nwu)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# RECORDS FOUND
if funcsys.tablerowcount(so_curs, sr_file) > 0:
# BUILD THE MESSAGE
l_records = funcstat.stat_list(
so_curs, sr_file, "DECLARATION_DATE||' - '||INTEREST_STATUS")
s_message += '\n\n'
s_message += 'Interests declared:'
for item in l_records:
s_message += '\n'
for element in item:
s_message += element
# EXPORT RECORDS
print("Export findings...")
sx_path = re_path
sx_file = sr_file + "_"
sx_file_dated = sx_file + funcdate.today_file()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file, s_head)
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file_dated,
s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file_dated)
# MAIL THE REPORT
s_report = "All DIY interests included!"
if s_name != "" and s_mail != "":
l_mailed = True
funcfile.writelog("%t Interests mailed to " + s_mail)
if l_debug:
print("Send the report...")
s_body: str = "Attached please find conflict of interest interests for " + s_nwu + "."
s_body += "\n\r"
s_body += s_report
funcmail.send(s_name, s_mail, "E", s_description + " for " + s_nwu,
s_body, re_path, sx_file_dated + ".csv")
# DELETE THE MAILED FILE
if funcfile.file_delete(re_path, sx_file_dated):
funcfile.writelog("%t Interests deleted")
if l_debug:
print("Delete the report...")
else:
s_message += "\n\n"
s_message += "No interests on record."
# POPULATE THE RETURN MESSAGE
if l_mailed:
s_message += "\n\n"
s_message += "Reports were mailed to " + s_mail + "."
s_return_message = s_message
"""*****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# CLOSE THE LOG WRITER
funcfile.writelog("-" * len("completed: " + s_function))
funcfile.writelog("COMPLETED: " + s_function.upper())
return s_return_message[0:4096]