FIND.AUD_SUP_NUMB AS Audit_supervisor_numb,
FIND.AUD_SUP_MAIL AS Audit_supervisor_mail
From
%FILEP%h_detail FIND
;"""
s_sql = s_sql.replace("%FIND%", s_finding)
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export findings
if l_export and funcsys.tablerowcount(so_curs, sr_file) > 0:
print("Export findings...")
sx_path = re_path
sx_file = s_file_prefix + "_" + s_finding.lower() + "_"
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)
else:
s_sql = "CREATE TABLE " + sr_file + " (" + """
BLANK TEXT
);"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
"""*****************************************************************************
END OF SCRIPT
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 Mail(s_trigger, s_subject='', s_body=''):
"""
Function to PREPARE email using parameters from 000m_Mail.csv
:rtype: str
:param s_trigger: Mail function trigger
:param s_subject: Mail subject
:param s_body: Mail body
:return: Text message to indicate successful mail send
"""
# OPEN THE SCRIPT LOG FILE
funcfile.writelog("%t DEFINITION: Send mail " + s_trigger)
# DECLARE VARIABLES
sl_path = "S:/_external_data/"
print("Send email " + s_trigger)
# Read the mail parameters from the 000_Mail.csv file """
co = open(sl_path + "000_mail.csv", "rU")
co_reader = csv.reader(co)
# Read the COLUMN database data
for row in co_reader:
# Populate the local variables
send_mail = False
# Populate the column variables
if row[0] != s_trigger:
continue
elif row[1] == "X":
continue
elif funcstr.isNotBlank(row[9]):
if row[9] == funcdate.cur_daystrip():
send_mail = True
elif row[9] == funcdate.today_dayname():
send_mail = True
else:
send_mail = True
if send_mail:
# Build the mail parameters from the 000m_Mail.csv file
to_name = row[2]
to_address = row[3]
mail_language = row[4]
if s_subject == '':
mail_subject = row[5]
else:
mail_subject = s_subject
if s_body == '':
mail_body = row[6]
else:
mail_body = s_body
file_path = row[7]
file_path = file_path.replace("%PYEAR%", funcdate.prev_year())
file_path = file_path.replace("%PMONTH%", funcdate.prev_month())
file_path = file_path.replace("%CYEAR%", funcdate.cur_year())
file_path = file_path.replace("%CMONTH%", funcdate.cur_month())
file_path = file_path.replace("%TODAY%", funcdate.today_file())
file_name = row[8]
file_name = file_name.replace("%PYEAR%", funcdate.prev_year())
file_name = file_name.replace("%PMONTH%", funcdate.prev_month())
file_name = file_name.replace("%CYEAR%", funcdate.cur_year())
file_name = file_name.replace("%CMONTH%", funcdate.cur_month())
file_name = file_name.replace("%TODAY%", funcdate.today_file())
# Send the mail
s_result = send(to_name, to_address, mail_language, mail_subject, mail_body, file_path, file_name)
# Mail result log
if s_result == "Successfully sent email":
print("MAIL SUCCESS: " + to_address + " (" + to_name + ")")
funcfile.writelog("%t MAIL SUCCESS: " + to_address + " (" + to_name + ")")
else:
print("MAIL FAIL: " + to_address + " (" + to_name + ")")
print("FAIL REASON: " + s_result)
funcfile.writelog("%t MAIL FAIL: " + to_address + " (" + to_name + ")")
funcfile.writelog("%t REASON FAIL: " + s_result)
# Close the imported data file
co.close()
def report_people_list_election(s_file_name: str = '',
s_date: str = '',
s_assign: str = '',
s_category: str = '',
s_division: str = '',
s_faculty: str = '') -> int:
"""
Function to list and export people.
param: s_date: str People active on which date
param: s_assign: str Assignment category (x)all (p)ermanent (t)emporary
param: s_category: str Employee category (x)all (a)cademic (s)upport
param: s_faculty: str Faculty (ec)onomic (ed)ucation (en)gineering (he)ealth (hu)manities (la)w (na)tural (th)eology
param: s_division: str Division
return: int: Table row count
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# FUNCTION WIDE VARIABLES
i_return: int = 0
ed_path: str = "S:/_external_data/" # External data path
re_path: str = "R:/People/"
so_path: str = "W:/People/" # Source database path
so_file: str = "People.sqlite"
l_debug: bool = False
l_mail: bool = funcconf.l_mail_project
l_mail: bool = False
l_mess: bool = funcconf.l_mess_project
l_mess: bool = True
l_export: bool = True
# 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 + "</b>")
"""************************************************************************
OPEN THE DATABASES
************************************************************************"""
funcfile.writelog("OPEN THE DATABASES")
if l_debug:
print("OPEN THE DATABASES")
# OPEN SQLITE SOURCE table
if l_debug:
print("Open sqlite 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_payroll/People_payroll.sqlite' AS 'PAYROLL'"
)
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
"""************************************************************************
TEMPORARY AREA
************************************************************************"""
funcfile.writelog("TEMPORARY AREA")
if l_debug:
print("TEMPORARY AREA")
"""************************************************************************
BEGIN OF SCRIPT
************************************************************************"""
funcfile.writelog("BEGIN OF SCRIPT")
if l_debug:
print("BEGIN OF SCRIPT")
# ASK THE QUESTIONS
if s_file_name == '':
s_file_name = 'X000_PEOPLE_LIST'
# PEOPLE ON WHICH DATE
if s_date == '':
print()
s_date = input("People on which date? (yyyy-mm-dd) ")
if s_date == '':
s_date = funcdate.today()
# ASSIGNMENT CATEGORY
if s_assign == '':
print()
s_assign = input(
"Assignment category? (x)all (p)ermanent (t)emporary ")
# EMPLOYEE CATEGORY
if s_category == '':
print()
s_category = input("Employee category? (x)all (a)cademic (s)upport ")
# DIVISION
if s_division == '':
print()
s_division = input("Division? ")
# FACULTY
if s_faculty == '':
print()
print('Faculty (ec)onomic management sciences')
print(' (ed)ucation')
print(' (en)gineering')
print(' (he)alth sciences')
print(' (hu)manities')
print(' (la)w')
print(' (na)tural agricultural sciences')
print(' (th)eology')
s_faculty = input("Faculty? ")
# DISPLAY THE INPUT
if l_debug:
print('')
print('VALUES')
print(' File name: ', s_file_name)
print(' Date: ', s_date)
print('Assignment category: ', s_assign)
print(' Employee category: ', s_category)
print(' Division: ', s_division)
print(' Faculty: ', s_faculty)
# BUILD THE SELECTED VALUE
s_selected: str = ''
if s_assign == 'p':
s_selected += " and p.user_person_type in (" \
"'FIXED TERM APPOINTMENT'," \
"'PERMANENT APPOINTMENT'," \
"'EXTRAORDINARY APPOINTMENT'," \
"'TEMP FIXED TERM CONTRACT'," \
"'TEMPORARY APPOINTMENT'" \
")"
if s_assign == 't':
s_selected += " and p.user_person_type not in (" \
"'FIXED TERM APPOINTMENT'," \
"'PERMANENT APPOINTMENT'," \
"'EXTRAORDINARY APPOINTMENT'," \
"'TEMP FIXED TERM CONTRACT'," \
"'TEMPORARY APPOINTMENT'" \
")"
if s_category == 'a':
s_selected += " and p.employee_category like('ACADEMIC')"
if s_category == 's':
s_selected += " and p.employee_category like('SUPPORT')"
if s_division != '':
s_selected += " and p.division like('%" + s_division.upper() + "%')"
if s_faculty != '':
s_selected += " and p.faculty like('%" + s_faculty.upper() + "%')"
# BUILD LIST OF DATED PEOPLE
print('')
print('Build dated list of people...')
i_count = funcpeople.people_detail_list(so_conn, s_file_name, s_date)
if l_debug:
print(i_count, ' records')
print('')
print("Build selected list of people...")
sr_file = s_file_name + '_SELECTED'
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'' as nominated,
case
when p.service_end_date is null then True
when p.service_end_date >= strftime('%Y-%m-%d','%DATE%','+3 years') then True
else False
end as may_be_nominated,
case
when p.service_end_date is null then True
when p.assign_end_date >= strftime('%Y-%m-%d',p.assign_start_date,'+3 months','-1 days') then True
else False
end as may_vote,
p.email_address,
p.employee_number ||'@nwu.ac.za' as calc_email,
p.name_address as first_name,
p.employee_number as username,
substr(cast(random() as text),19,-4) as password,
p.employee_number as user1,
'' as user2,
p.position_name,
p.title ||' '|| p.initials ||' ('||p.preferred_name ||') '||p.name_last ||' - '||p.organization as name_long,
p.nrf_rated as nrf_rating,
p.name_last,
p.employee_age,
p.gender,
p.race,
p.assignment_category,
p.employee_category,
p.user_person_type,
p.location,
p.faculty,
p.organization,
p.assign_start_date,
p.assign_end_date,
p.service_end_date
From
%FILE% p
Where
p.employee_number Is Not Null
%SELECTION%
;"""
s_sql = s_sql.replace("%FILE%", s_file_name)
s_sql = s_sql.replace("%SELECTION%", s_selected)
s_sql = s_sql.replace("%DATE%", s_date)
# print(s_sql)
so_curs.execute(s_sql)
so_conn.commit()
i_return = funcsys.tablerowcount(so_curs, sr_file)
funcfile.writelog("%t BUILD TABLE: " + sr_file + ' (' + str(i_return) +
' RECORDS)')
if l_export:
# EXPORT TABLE
sr_file = s_file_name + '_SELECTED'
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = "People_000_all_selected_"
sx_file_dated = sx_file + s_date.replace(
'-', '') + '_' + funcdate.today_file()
if l_debug:
print("Export selected people..." + sx_path + sx_file)
# Read the header data
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# Write the data
# funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file, s_head)
# funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
# Write the data dated
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)
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator",
" " + str(i_return) + " records selected")
"""************************************************************************
END OF SCRIPT
************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# CLOSE THE DATABASE CONNECTION
so_conn.commit()
so_conn.close()
# CLOSE THE LOG WRITER
funcfile.writelog("-" * len("completed: " + s_function))
funcfile.writelog("COMPLETED: " + s_function.upper())
return i_return
Upper(petf.element_name) Like 'NWU LONG SERVICE AWARD' And
Date('%TODAY%') Between peef.effective_start_date And peef.effective_end_date And
Date('%TODAY%') Between per.effective_start_date And per.effective_end_date And
Date('%TODAY%') Between paaf.effective_start_date And paaf.effective_end_date And
Date('%TODAY%') Between petf.effective_start_date And petf.effective_end_date And
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%TODAY%", funcdate.today())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
print("Export findings...")
sr_filet = sr_file
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = "People_payroll_hours_"
sx_filet = sx_file + funcdate.today_file()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_filet)
funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_file, s_head)
funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_filet, s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
""" ****************************************************************************
END OF SCRIPT
*****************************************************************************"""
print("END OF SCRIPT")
funcfile.writelog("END OF SCRIPT")
# CLOSE THE WORKING DATABASE
so_conn.close()
# CLOSE THE LOG
funcfile.writelog("------------------------------------------")
def gl_test_transactions():
"""
Script to test GL transactions
:return: int
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# DECLARE VARIABLES
so_path = "W:/Kfs/" # Source database path
so_file = "Kfs_test_gl_transaction.sqlite" # Source database
ed_path = "S:/_external_data/" # External data path
re_path = "R:/Kfs/" # Results path
l_debug: bool = False
l_export = False
l_mess: bool = funcconf.l_mess_project
# l_mess: bool = False
l_record = True
# OPEN THE SCRIPT LOG FILE
if l_debug:
print("-------------------------")
print("C202_GL_TEST_TRANSACTIONS")
print("-------------------------")
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C202_GL_TEST_TRANSACTIONS")
funcfile.writelog("---------------------------------")
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator",
"<b>C202 Kfs gl transaction tests</b>")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
if l_debug:
print("OPEN THE DATABASES")
funcfile.writelog("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:/Kfs/Kfs.sqlite' AS 'KFS'")
funcfile.writelog("%t ATTACH DATABASE: KFS.SQLITE")
so_curs.execute("ATTACH DATABASE 'W:/Kfs/Kfs_curr.sqlite' AS 'KFSCURR'")
funcfile.writelog("%t ATTACH DATABASE: KFS_CURR.SQLITE")
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
so_curs.execute("ATTACH DATABASE 'W:/Vss/Vss_curr.sqlite' AS 'VSSCURR'")
funcfile.writelog("%t ATTACH DATABASE: VSS_CURR.SQLITE")
""" ****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
if l_debug:
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
""" ****************************************************************************
PROFESSIONAL FEES GL MASTER FILE
*****************************************************************************"""
if l_debug:
print("PROFESSIONAL FEES GL MASTER FILE")
funcfile.writelog("PROFESSIONAL FEES GL MASTER FILE")
# OBTAIN GL PROFESSIONAL FEE TRANSACTIONS
if l_debug:
print("Obtain gl professional (2056) fee transactions...")
sr_file: str = "X001_gl_professional_fee"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
GL.*,
ACC.ACCT_MGR_UNVL_ID As ACC_MGR,
ACC.ACCT_SPVSR_UNVL_ID As ACC_SUP,
ACC.ACCT_FSC_OFC_UID As ACC_FIS,
CASE
WHEN ACC.ACCT_PHYS_CMP_CD = 'P' THEN 'POTCHEFSTROOM'
WHEN ACC.ACCT_PHYS_CMP_CD = 'V' THEN 'VAAL TRIANGLE'
WHEN ACC.ACCT_PHYS_CMP_CD = 'M' THEN 'MAFIKENG'
ELSE 'NWU'
END As ACC_CAMPUS
From
KFSCURR.X000_GL_trans GL Left Join
KFS.X000_Account ACC On ACC.ACCOUNT_NBR = GL.ACCOUNT_NBR
Where
GL.FS_DATABASE_DESC = 'KFS' And
Instr(GL.CALC_COST_STRING, '.2056') > 0
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# ADD PAYMENT DETAILS TO GL TRANSACTIONS
if l_debug:
print("Add payment details to transactions...")
sr_file: str = "X001_gl_professional_fee_pay"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
GL.*,
PAY.VENDOR_ID,
PAY.PAYEE_NAME As STUDENT_NAME,
PAY.INV_NBR,
PAY.PAYEE_TYP_DESC,
PAY.COMPLETE_EMP_NO As EMP_INI,
PAY.APPROVE_EMP_NO As EMP_APP
From
X001_gl_professional_fee GL Inner Join
KFSCURR.X001aa_Report_payments PAY On PAY.CUST_PMT_DOC_NBR = GL.FDOC_NBR And
PAY.NET_PMT_AMT = GL.CALC_AMOUNT
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
TEST PROFESSIONAL FEES PAID TO STUDENTS
*****************************************************************************"""
if l_debug:
print("PROFESSIONAL FEES PAID TO STUDENTS")
funcfile.writelog("PROFESSIONAL FEES PAID TO STUDENTS")
# DECLARE VARIABLES
i_finding_after: int = 0
# OBTAIN TEST DATA
if l_debug:
print("Obtain test data...")
sr_file: str = "X001aa_professional_fee_student"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
GL.*,
STUD.KSTUDBUSENTID As STUDENT,
CASE
WHEN STUD.FSITEORGUNITNUMBER = -1 THEN 'POT'
WHEN STUD.FSITEORGUNITNUMBER = -2 THEN 'VAA'
WHEN STUD.FSITEORGUNITNUMBER = -9 THEN 'MAF'
ELSE 'OTH'
END As LOC
From
X001_gl_professional_fee_pay GL Inner Join
VSSCURR.X001_student STUD On Substr(GL.VENDOR_ID,1,8) = STUD.KSTUDBUSENTID And
STUD.ISMAINQUALLEVEL = '1'
Order By
GL.TIMESTAMP
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# IDENTIFY FINDINGS
if l_debug:
print("Identify findings...")
sr_file = "X001ab_findings"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'NWU' As ORG,
CURR.LOC,
CURR.STUDENT,
CURR.FDOC_NBR,
CURR.CALC_COST_STRING,
EMP_INI,
ACC_MGR
From
X001aa_professional_fee_student CURR
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# COUNT THE NUMBER OF FINDINGS
i_finding_before: int = funcsys.tablerowcount(so_curs, sr_file)
if l_debug:
print("*** Found " + str(i_finding_before) + " exceptions ***")
funcfile.writelog("%t FINDING: " + str(i_finding_before) +
" PROF FEE PAID TO STUDENT finding(s)")
# GET PREVIOUS FINDINGS
sr_file = "X001ac_get_previous"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if l_debug:
print("Import previously reported findings...")
so_curs.execute("CREATE TABLE " + sr_file + """
(PROCESS TEXT,
FIELD1 INT,
FIELD2 TEXT,
FIELD3 TEXT,
FIELD4 TEXT,
FIELD5 TEXT,
DATE_REPORTED TEXT,
DATE_RETEST TEXT,
DATE_MAILED TEXT)
""")
s_cols = ""
co = open(ed_path + "202_reported.txt", "r")
co_reader = csv.reader(co)
# Read the COLUMN database data
for row in co_reader:
# Populate the column variables
if row[0] == "PROCESS":
continue
elif row[0] != "prof fee paid to student":
continue
else:
s_cols = "INSERT INTO " + sr_file + " VALUES('" + row[0] + "','" + row[1] + "','" + row[2] + "','" + \
row[
3] + "','" + row[4] + "','" + row[5] + "','" + row[6] + "','" + row[7] + "','" + row[
8] + "')"
so_curs.execute(s_cols)
so_conn.commit()
# Close the imported data file
co.close()
funcfile.writelog("%t IMPORT TABLE: " + ed_path +
"001_reported.txt (" + sr_file + ")")
# ADD PREVIOUS FINDINGS
sr_file = "X001ad_add_previous"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if l_debug:
print("Join previously reported to current findings...")
s_sql = "CREATE TABLE " + sr_file + " AS" + """
Select
FIND.*,
'prof fee paid to student' AS PROCESS,
'%TODAY%' AS DATE_REPORTED,
'%DAYS%' AS DATE_RETEST,
PREV.PROCESS AS PREV_PROCESS,
PREV.DATE_REPORTED AS PREV_DATE_REPORTED,
PREV.DATE_RETEST AS PREV_DATE_RETEST,
PREV.DATE_MAILED
From
X001ab_findings FIND Left Join
X001ac_get_previous PREV ON PREV.FIELD1 = FIND.STUDENT AND
PREV.FIELD2 = FIND.FDOC_NBR And
PREV.FIELD3 = FIND.CALC_COST_STRING And
PREV.DATE_RETEST >= Date('%TODAY%')
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%TODAY%", funcdate.today())
s_sql = s_sql.replace("%DAYS%", funcdate.today_plusdays(366))
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD LIST TO UPDATE FINDINGS
# NOTE ADD CODE
sr_file = "X001ae_new_previous"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PREV.PROCESS,
PREV.STUDENT AS FIELD1,
PREV.FDOC_NBR AS FIELD2,
PREV.CALC_COST_STRING AS FIELD3,
'' AS FIELD4,
'' AS FIELD5,
PREV.DATE_REPORTED,
PREV.DATE_RETEST,
PREV.DATE_MAILED
From
X001ad_add_previous PREV
Where
PREV.PREV_PROCESS Is Null
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export findings to previous reported file
i_finding_after = funcsys.tablerowcount(so_curs, sr_file)
if i_finding_after > 0:
if l_debug:
print("*** " + str(i_finding_after) +
" Finding(s) to report ***")
sx_path = ed_path
sx_file = "202_reported"
# Read the header data
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# Write the data
if l_record:
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file,
s_head, "a", ".txt")
funcfile.writelog("%t FINDING: " + str(i_finding_after) +
" new finding(s) to export")
funcfile.writelog("%t EXPORT DATA: " + sr_file)
if l_mess:
s_desc = "Professional fee student"
funcsms.send_telegram(
'', 'administrator', '<b>' + str(i_finding_before) + '/' +
str(i_finding_after) + '</b> ' + s_desc)
else:
if l_debug:
print("*** No new findings to report ***")
funcfile.writelog("%t FINDING: No new findings to export")
# IMPORT OFFICERS FOR MAIL REPORTING PURPOSES
sr_file = "X001af_officer"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if i_finding_after > 0:
if l_debug:
print("Import reporting officers for mail purposes...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
OFFICER.LOOKUP,
OFFICER.LOOKUP_CODE AS CAMPUS,
OFFICER.LOOKUP_DESCRIPTION AS EMPLOYEE_NUMBER,
PEOP.NAME_ADDR As NAME,
PEOP.EMAIL_ADDRESS
From
PEOPLE.X000_OWN_HR_LOOKUPS OFFICER Left Join
PEOPLE.X002_PEOPLE_CURR PEOP ON
PEOP.EMPLOYEE_NUMBER = OFFICER.LOOKUP_DESCRIPTION
Where
OFFICER.LOOKUP = 'TEST_GL_OBJECT_PROF_FEE_PAID_TO_STUDENT_OFFICER'
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# IMPORT SUPERVISORS FOR MAIL REPORTING PURPOSES
sr_file = "X001ag_supervisor"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0 and i_finding_after > 0:
if l_debug:
print("Import reporting supervisors for mail purposes...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
SUPERVISOR.LOOKUP,
SUPERVISOR.LOOKUP_CODE AS CAMPUS,
SUPERVISOR.LOOKUP_DESCRIPTION AS EMPLOYEE_NUMBER,
PEOP.NAME_ADDR As NAME,
PEOP.EMAIL_ADDRESS
From
PEOPLE.X000_OWN_HR_LOOKUPS SUPERVISOR Left Join
PEOPLE.X002_PEOPLE_CURR PEOP ON
PEOP.EMPLOYEE_NUMBER = SUPERVISOR.LOOKUP_DESCRIPTION
Where
SUPERVISOR.LOOKUP = 'TEST_GL_OBJECT_PROF_FEE_PAID_TO_STUDENT_SUPERVISOR'
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# ADD CONTACT DETAILS TO FINDINGS
sr_file = "X001ah_detail"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0 and i_finding_after > 0:
if l_debug:
print("Add contact details to findings...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PREV.ORG,
PREV.LOC,
PREV.STUDENT,
MASTER.STUDENT_NAME,
PREV.FDOC_NBR,
MASTER.TRANSACTION_DT,
MASTER.CALC_AMOUNT,
MASTER.TRN_LDGR_ENTR_DESC,
MASTER.PAYEE_TYP_DESC,
MASTER.INV_NBR,
PREV.CALC_COST_STRING,
MASTER.ORG_NM,
MASTER.ACCOUNT_NM,
CAMP_OFF.EMPLOYEE_NUMBER As CAMP_OFF_NUMB,
CAMP_OFF.NAME As CAMP_OFF_NAME,
CASE
WHEN CAMP_OFF.EMPLOYEE_NUMBER <> '' THEN CAMP_OFF.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE CAMP_OFF.EMAIL_ADDRESS
END As CAMP_OFF_MAIL,
CAMP_OFF.EMAIL_ADDRESS As CAMP_OFF_MAIL2,
CAMP_SUP.EMPLOYEE_NUMBER As CAMP_SUP_NUMB,
CAMP_SUP.NAME As CAMP_SUP_NAME,
CASE
WHEN CAMP_SUP.EMPLOYEE_NUMBER <> '' THEN CAMP_SUP.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE CAMP_SUP.EMAIL_ADDRESS
END As CAMP_SUP_MAIL,
CAMP_SUP.EMAIL_ADDRESS As CAMP_SUP_MAIL2,
ORG_OFF.EMPLOYEE_NUMBER As ORG_OFF_NUMB,
ORG_OFF.NAME As ORG_OFF_NAME,
CASE
WHEN ORG_OFF.EMPLOYEE_NUMBER <> '' THEN ORG_OFF.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE ORG_OFF.EMAIL_ADDRESS
END As ORG_OFF_MAIL,
ORG_OFF.EMAIL_ADDRESS As ORG_OFF_MAIL2,
ORG_SUP.EMPLOYEE_NUMBER As ORG_SUP_NUMB,
ORG_SUP.NAME As ORG_SUP_NAME,
CASE
WHEN ORG_SUP.EMPLOYEE_NUMBER <> '' THEN ORG_SUP.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE ORG_SUP.EMAIL_ADDRESS
END As ORG_SUP_MAIL,
ORG_SUP.EMAIL_ADDRESS As ORG_SUP_MAIL2,
PREV.EMP_INI,
INI.NAME_ADDR As INAME,
PREV.EMP_INI||'@nwu.ac.za' As IMAIL,
INI.EMAIL_ADDRESS As IMAIL2,
PREV.ACC_MGR,
ACCM.NAME_ADDR As ANAME,
PREV.ACC_MGR||'@nwu.ac.za' As AMAIL,
ACCM.EMAIL_ADDRESS As AMAIL2
From
X001ad_add_previous PREV
Left Join X001af_officer CAMP_OFF On CAMP_OFF.CAMPUS = PREV.LOC
Left Join X001af_officer ORG_OFF On ORG_OFF.CAMPUS = PREV.ORG
Left Join X001ag_supervisor CAMP_SUP On CAMP_SUP.CAMPUS = PREV.LOC
Left Join X001ag_supervisor ORG_SUP On ORG_SUP.CAMPUS = PREV.ORG
Left Join X001aa_professional_fee_student MASTER On MASTER.STUDENT = PREV.STUDENT And
MASTER.FDOC_NBR = PREV.FDOC_NBR And
MASTER.CALC_COST_STRING = PREV.CALC_COST_STRING
Left Join PEOPLE.X002_PEOPLE_CURR INI On INI.EMPLOYEE_NUMBER = PREV.EMP_INI
Left Join PEOPLE.X002_PEOPLE_CURR ACCM On ACCM.EMPLOYEE_NUMBER = PREV.ACC_MGR
Where
PREV.PREV_PROCESS IS NULL
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD THE FINAL TABLE FOR EXPORT AND REPORT
sr_file = "X001ax_professional_fee_student"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if l_debug:
print("Build the final report")
if i_finding_before > 0 and i_finding_after > 0:
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'PROFESSIONAL FEE PAID TO STUDENT' As Audit_finding,
FIND.STUDENT As Student,
FIND.STUDENT_NAME As Name,
FIND.FDOC_NBR As Edoc,
FIND.TRANSACTION_DT As Date,
FIND.INV_NBR As Invoice,
FIND.CALC_AMOUNT As Amount,
FIND.PAYEE_TYP_DESC As Vendor_type,
CASE
WHEN Instr(FIND.TRN_LDGR_ENTR_DESC,'<VATI-0>') > 0 THEN Substr(FIND.TRN_LDGR_ENTR_DESC,9)
ELSE FIND.TRN_LDGR_ENTR_DESC
END As Description,
FIND.CALC_COST_STRING As Account,
FIND.ORG_NM As Organization,
FIND.ACCOUNT_NM As Account_name,
FIND.EMP_INI As Initiator,
FIND.INAME As Initiator_name,
FIND.IMAIL As Initiator_mail,
FIND.ACC_MGR As Acc_manager,
FIND.ANAME As Acc_manager_name,
FIND.AMAIL As Acc_manager_mail,
FIND.CAMP_OFF_NAME AS Responsible_Officer,
FIND.CAMP_OFF_NUMB AS Responsible_Officer_Numb,
FIND.CAMP_OFF_MAIL AS Responsible_Officer_Mail,
FIND.CAMP_SUP_NAME AS Supervisor,
FIND.CAMP_SUP_NUMB AS Supervisor_Numb,
FIND.CAMP_SUP_MAIL AS Supervisor_Mail,
FIND.ORG_OFF_NAME AS Org_Officer,
FIND.ORG_OFF_NUMB AS Org_Officer_Numb,
FIND.ORG_OFF_MAIL AS Org_Officer_Mail,
FIND.ORG_SUP_NAME AS Org_Supervisor,
FIND.ORG_SUP_NUMB AS Org_Supervisor_Numb,
FIND.ORG_SUP_MAIL AS Org_Supervisor_Mail
From
X001ah_detail FIND
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export findings
if l_export and funcsys.tablerowcount(so_curs, sr_file) > 0:
if l_debug:
print("Export findings...")
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = "Gltran_test_001ax_professional_fee_student_"
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)
else:
s_sql = "CREATE TABLE " + sr_file + " (" + """
BLANK TEXT
);"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
"""*****************************************************************************
IA ACTUAL VS BUDGET
*****************************************************************************"""
# FILES NEEDED
# X000_GL_trans
# DEFAULT TRANSACTION OWNER PEOPLE
# DECLARE TEST VARIABLES
i_finding_before = 0
i_finding_after = 0
s_description = "IA Actual vs budget"
s_file_prefix: str = "X002a"
s_file_name: str = "ia_actual_vs_budget"
s_finding: str = "IA ACTUAL VS BUDGET"
s_report_file: str = "202_reported.txt"
# OBTAIN TEST RUN FLAG
if functest.get_test_flag(so_curs, "KFS", "TEST " + s_finding,
"RUN") == "FALSE":
if l_debug:
print('TEST DISABLED')
funcfile.writelog("TEST " + s_finding + " DISABLED")
else:
# LOG
funcfile.writelog("TEST " + s_finding)
if l_debug:
print("TEST " + s_finding)
# OBTAIN MASTER DATA
if l_debug:
print("Obtain master data...")
sr_file: str = s_file_prefix + "a_" + s_file_name
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "Create Table " + sr_file + " As " + """
Select
'NWU' ORG,
GL.FIN_COA_CD As LOC,
GL.ORG_NM,
GL.ACCOUNT_NM,
GL.CALC_COST_STRING,
GL.FIN_OBJ_CD_NM,
GL.FIN_BALANCE_TYP_CD,
Count(GL.FDOC_NBR) As Count_FDOC_NBR,
Total(GL.CALC_AMOUNT) As Total_CALC_AMOUNT
From
X000_GL_trans GL
Where
GL.ACCOUNT_NM Like ("(4532%")
Group By
GL.ACCOUNT_NM,
GL.CALC_COST_STRING,
GL.FIN_OBJ_CD_NM,
GL.FIN_BALANCE_TYP_CD
Order By
GL.ACCOUNT_NM,
GL.FIN_OBJ_CD_NM
;"""
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# IDENTIFY FINDINGS
if l_debug:
print("Identify findings...")
sr_file = s_file_prefix + "b_finding"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
GL.ORG,
GL.LOC,
GL.ORG_NM,
GL.ACCOUNT_NM,
GL.CALC_COST_STRING,
GL.FIN_OBJ_CD_NM,
Cast(Case
When ACT.Total_CALC_AMOUNT is null Then '0'
When ACT.Total_CALC_AMOUNT = '' Then '0'
Else ACT.Total_CALC_AMOUNT
End As Int) As ACTUAL,
Cast(Case
When BUD.Total_CALC_AMOUNT is null Then '0'
When BUD.Total_CALC_AMOUNT = '' Then '0'
Else BUD.Total_CALC_AMOUNT
End As Int) As BUDGET,
cast( ACT.Total_CALC_AMOUNT/BUD.Total_CALC_AMOUNT*100 As Real) As PERCENT
From
%FILEP%%FILEN% GL Left Join
%FILEP%%FILEN% ACT On ACT.CALC_COST_STRING = GL.CALC_COST_STRING
And ACT.FIN_BALANCE_TYP_CD = "AC" Left Join
%FILEP%%FILEN% BUD On BUD.CALC_COST_STRING = GL.CALC_COST_STRING
And BUD.FIN_BALANCE_TYP_CD = "CB"
Group By
GL.CALC_COST_STRING
Order By
GL.FIN_OBJ_CD_NM
;"""
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
s_sql = s_sql.replace("%FILEN%", "a_" + s_file_name)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# COUNT THE NUMBER OF FINDINGS
if l_debug:
print("Count the number of findings...")
i_finding_before: int = funcsys.tablerowcount(so_curs, sr_file)
funcfile.writelog("%t FINDING: " + str(i_finding_before) + " " +
s_finding + " finding(s)")
if l_debug:
print("*** Found " + str(i_finding_before) + " exceptions ***")
# GET PREVIOUS FINDINGS
if i_finding_before > 0:
functest.get_previous_finding(so_curs, ed_path, s_report_file,
s_finding, "TIITT")
if l_debug:
so_conn.commit()
# SET PREVIOUS FINDINGS
if i_finding_before > 0:
functest.set_previous_finding(so_curs)
if l_debug:
so_conn.commit()
# ADD PREVIOUS FINDINGS
sr_file = s_file_prefix + "d_addprev"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if l_debug:
print("Join previously reported to current findings...")
s_sql = "CREATE TABLE " + sr_file + " AS" + """
Select
FIND.*,
Lower('%FINDING%') AS PROCESS,
'%TODAY%' AS DATE_REPORTED,
'%DATETEST%' AS DATE_RETEST,
PREV.PROCESS AS PREV_PROCESS,
PREV.DATE_REPORTED AS PREV_DATE_REPORTED,
PREV.DATE_RETEST AS PREV_DATE_RETEST,
PREV.REMARK
From
%FILEP%b_finding FIND Left Join
Z001ab_setprev PREV ON PREV.FIELD1 = FIND.CALC_COST_STRING And
PREV.FIELD2 = FIND.ACTUAL And
PREV.FIELD3 = FIND.BUDGET
;"""
s_sql = s_sql.replace("%FINDING%", s_finding)
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
s_sql = s_sql.replace("%TODAY%", funcdate.today())
s_sql = s_sql.replace("%DATETEST%", funcdate.cur_monthendnext(0))
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# BUILD LIST TO UPDATE FINDINGS
sr_file = s_file_prefix + "e_newprev"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if l_debug:
print("Build list to update findings...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PREV.PROCESS,
PREV.CALC_COST_STRING AS FIELD1,
PREV.ACTUAL AS FIELD2,
PREV.BUDGET AS FIELD3,
'' AS FIELD4,
'' AS FIELD5,
PREV.DATE_REPORTED,
PREV.DATE_RETEST,
PREV.REMARK
From
%FILEP%d_addprev PREV
Where
PREV.PREV_PROCESS Is Null Or
PREV.DATE_REPORTED > PREV.PREV_DATE_RETEST And PREV.REMARK = ""
;"""
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# Export findings to previous reported file
i_finding_after = funcsys.tablerowcount(so_curs, sr_file)
if i_finding_after > 0:
if l_debug:
print("*** " + str(i_finding_after) +
" Finding(s) to report ***")
sx_path = ed_path
sx_file = s_report_file[:-4]
# Read the header data
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# Write the data
if l_record:
funccsv.write_data(so_conn, "main", sr_file, sx_path,
sx_file, s_head, "a", ".txt")
funcfile.writelog("%t FINDING: " + str(i_finding_after) +
" new finding(s) to export")
funcfile.writelog("%t EXPORT DATA: " + sr_file)
if l_mess:
funcsms.send_telegram(
'', 'administrator', '<b>' + str(i_finding_before) +
'/' + str(i_finding_after) + '</b> ' + s_description)
else:
funcfile.writelog("%t FINDING: No new findings to export")
if l_debug:
print("*** No new findings to report ***")
# IMPORT OFFICERS FOR MAIL REPORTING PURPOSES
if i_finding_before > 0 and i_finding_after > 0:
functest.get_officer(so_curs, "KFS",
"TEST " + s_finding + " OFFICER")
so_conn.commit()
# IMPORT SUPERVISORS FOR MAIL REPORTING PURPOSES
if i_finding_before > 0 and i_finding_after > 0:
functest.get_supervisor(so_curs, "KFS",
"TEST " + s_finding + " SUPERVISOR")
so_conn.commit()
# ADD CONTACT DETAILS TO FINDINGS
sr_file = s_file_prefix + "h_detail"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0 and i_finding_after > 0:
if l_debug:
print("Add contact details to findings...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PREV.ORG,
PREV.LOC,
PREV.ORG_NM,
PREV.ACCOUNT_NM,
PREV.CALC_COST_STRING,
PREV.FIN_OBJ_CD_NM,
PREV.ACTUAL,
PREV.BUDGET,
PREV.PERCENT,
CAMP_OFF.EMPLOYEE_NUMBER AS CAMP_OFF_NUMB,
CAMP_OFF.NAME_ADDR AS CAMP_OFF_NAME,
CAMP_OFF.EMAIL_ADDRESS AS CAMP_OFF_MAIL1,
CASE
WHEN CAMP_OFF.EMPLOYEE_NUMBER != '' THEN CAMP_OFF.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE CAMP_OFF.EMAIL_ADDRESS
END AS CAMP_OFF_MAIL2,
CAMP_SUP.EMPLOYEE_NUMBER AS CAMP_SUP_NUMB,
CAMP_SUP.NAME_ADDR AS CAMP_SUP_NAME,
CAMP_SUP.EMAIL_ADDRESS AS CAMP_SUP_MAIL1,
CASE
WHEN CAMP_SUP.EMPLOYEE_NUMBER != '' THEN CAMP_SUP.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE CAMP_SUP.EMAIL_ADDRESS
END AS CAMP_SUP_MAIL2,
ORG_OFF.EMPLOYEE_NUMBER AS ORG_OFF_NUMB,
ORG_OFF.NAME_ADDR AS ORG_OFF_NAME,
ORG_OFF.EMAIL_ADDRESS AS ORG_OFF_MAIL1,
CASE
WHEN ORG_OFF.EMPLOYEE_NUMBER != '' THEN ORG_OFF.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE ORG_OFF.EMAIL_ADDRESS
END AS ORG_OFF_MAIL2,
ORG_SUP.EMPLOYEE_NUMBER AS ORG_SUP_NUMB,
ORG_SUP.NAME_ADDR AS ORG_SUP_NAME,
ORG_SUP.EMAIL_ADDRESS AS ORG_SUP_MAIL1,
CASE
WHEN ORG_SUP.EMPLOYEE_NUMBER != '' THEN ORG_SUP.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE ORG_SUP.EMAIL_ADDRESS
END AS ORG_SUP_MAIL2,
AUD_OFF.EMPLOYEE_NUMBER As AUD_OFF_NUMB,
AUD_OFF.NAME_ADDR As AUD_OFF_NAME,
AUD_OFF.EMAIL_ADDRESS As AUD_OFF_MAIL,
AUD_SUP.EMPLOYEE_NUMBER As AUD_SUP_NUMB,
AUD_SUP.NAME_ADDR As AUD_SUP_NAME,
AUD_SUP.EMAIL_ADDRESS As AUD_SUP_MAIL
From
%FILEP%d_addprev PREV Left Join
Z001af_officer CAMP_OFF On CAMP_OFF.CAMPUS = PREV.LOC Left Join
Z001af_officer ORG_OFF On ORG_OFF.CAMPUS = PREV.ORG Left Join
Z001af_officer AUD_OFF On AUD_OFF.CAMPUS = 'AUD' Left Join
Z001ag_supervisor CAMP_SUP On CAMP_SUP.CAMPUS = PREV.LOC Left Join
Z001ag_supervisor ORG_SUP On ORG_SUP.CAMPUS = PREV.ORG Left Join
Z001ag_supervisor AUD_SUP On AUD_SUP.CAMPUS = 'AUD'
Where
PREV.PREV_PROCESS Is Null Or
PREV.DATE_REPORTED > PREV.PREV_DATE_RETEST And PREV.REMARK = ""
;"""
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
s_sql = s_sql.replace("%FILEN%", s_file_name)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD THE FINAL TABLE FOR EXPORT AND REPORT
sr_file = s_file_prefix + "x_" + s_file_name
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0 and i_finding_after > 0:
if l_debug:
print("Build the final report")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'%FIND%' As Audit_finding,
FIND.ORG As Organization,
FIND.LOC As Campus,
FIND.ORG_NM As Division,
FIND.ACCOUNT_NM As Account,
FIND.CALC_COST_STRING As Cost_string,
FIND.FIN_OBJ_CD_NM As Object_name,
FIND.ACTUAL As R_Actual,
FIND.BUDGET As R_Budget,
FIND.PERCENT As Per_Used,
FIND.CAMP_OFF_NAME AS Responsible_officer,
FIND.CAMP_OFF_NUMB AS Responsible_officer_numb,
FIND.CAMP_OFF_MAIL1 AS Responsible_officer_mail,
FIND.CAMP_OFF_MAIL2 AS Responsible_officer_mail_alt,
FIND.CAMP_SUP_NAME AS Supervisor,
FIND.CAMP_SUP_NUMB AS Supervisor_numb,
FIND.CAMP_SUP_MAIL1 AS Supervisor_mail,
FIND.ORG_OFF_NAME AS Org_officer,
FIND.ORG_OFF_NUMB AS Org_officer_numb,
FIND.ORG_OFF_MAIL1 AS Org_officer_mail,
FIND.ORG_SUP_NAME AS Org_supervisor,
FIND.ORG_SUP_NUMB AS Org_supervisor_numb,
FIND.ORG_SUP_MAIL1 AS Org_supervisor_mail,
FIND.AUD_OFF_NAME AS Audit_officer,
FIND.AUD_OFF_NUMB AS Audit_officer_numb,
FIND.AUD_OFF_MAIL AS Audit_officer_mail,
FIND.AUD_SUP_NAME AS Audit_supervisor,
FIND.AUD_SUP_NUMB AS Audit_supervisor_numb,
FIND.AUD_SUP_MAIL AS Audit_supervisor_mail
From
%FILEP%h_detail FIND
;"""
s_sql = s_sql.replace("%FIND%", s_finding)
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export findings
if l_export and funcsys.tablerowcount(so_curs, sr_file) > 0:
if l_debug:
print("Export findings...")
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = s_file_prefix + "_" + s_finding.lower() + "_"
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)
else:
s_sql = "CREATE TABLE " + sr_file + " (" + """
BLANK TEXT
);"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
END OF SCRIPT
*****************************************************************************"""
if l_debug:
print("END OF SCRIPT")
funcfile.writelog("END OF SCRIPT")
# CLOSE THE DATABASE CONNECTION
so_conn.commit()
so_conn.close()
# CLOSE THE LOG WRITER
funcfile.writelog("------------------------------------")
funcfile.writelog("COMPLETED: C202_GL_TEST_TRANSACTIONS")
return
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]
def people_test_masterfile_xdev():
"""
Script to test multiple PEOPLE MASTER FILE items
:return: Nothing
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# DECLARE VARIABLES
so_path = "W:/People/" # Source database path
re_path = "R:/People/" # Results path
ed_path = "S:/_external_data/" # external data path
so_file = "People_test_masterfile.sqlite" # Source database
s_sql = "" # SQL statements
l_debug: bool = True # Display statements on screen
l_export: bool = True # Export findings to text file
l_mail: bool = funcconf.l_mail_project
l_mail: bool = False # Send email messages
l_mess: bool = funcconf.l_mess_project
l_mess: bool = False # Send communicator messages
l_record: bool = False # Record findings for future use
i_finding_before: int = 0
i_finding_after: int = 0
# 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
*****************************************************************************"""
print("OPEN THE DATABASES")
funcfile.writelog("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 '" + so_path +
"People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
so_curs.execute(
"ATTACH DATABASE 'W:/People_payroll/People_payroll.sqlite' AS 'PAYROLL'"
)
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
""" ****************************************************************************
TEMPORARY SCRIPT
*****************************************************************************"""
# TODO Delete after first run
s_file_prefix: str = "X003e"
sr_file: str = s_file_prefix + "a_permit_expire"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
sr_file: str = s_file_prefix + "a_work_permit_expire"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
sr_file: str = s_file_prefix + "b_detail"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
sr_file: str = s_file_prefix + "f_officer"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
sr_file: str = s_file_prefix + "g_supervisor"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
sr_file: str = s_file_prefix + "h_contact"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
sr_file: str = s_file_prefix + "x_work_permit_expire"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
""" ****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
if l_debug:
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
""" ****************************************************************************
MASTER FILE LISTS
*****************************************************************************"""
"""*****************************************************************************
TEST FOREIGN EMPLOYEE WORK PERMIT EXPIRED
*****************************************************************************"""
# FILES NEEDED
# X000_PEOPLE
# DEFAULT TRANSACTION OWNER PEOPLE
# 21022402 MS AC COERTZEN for permanent employees
# 20742010 MRS N BOTHA for temporary employees
# Exclude 12795631 MR R VAN DEN BERG
# Exclude 13277294 MRS MC STRYDOM
# DECLARE TEST VARIABLES
i_finding_before = 0
i_finding_after = 0
s_description = "Work permit expired"
s_file_prefix: str = "X003e"
s_file_name: str = "work_permit_expired"
s_finding: str = "EMPLOYEE WORK PERMIT EXPIRED"
s_report_file: str = "001_reported.txt"
# OBTAIN TEST RUN FLAG
if functest.get_test_flag(so_curs, "HR", "TEST " + s_finding,
"RUN") == "FALSE":
if l_debug:
print('TEST DISABLED')
funcfile.writelog("TEST " + s_finding + " DISABLED")
else:
# LOG
funcfile.writelog("TEST " + s_finding)
if l_debug:
print("TEST " + s_finding)
# OBTAIN MASTER DATA
if l_debug:
print("Obtain master data...")
sr_file: str = s_file_prefix + "a_" + s_file_name
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "Create Table " + sr_file + " As " + """
Select
'NWU' ORG,
Substr(p.location,1,3) LOC,
p.employee_number EMPLOYEE_NUMBER,
p.nationality NATIONALITY,
p.national_identifier IDNO,
p.passport PASSPORT,
p.permit PERMIT,
p.permit_expire PERMIT_EXPIRE,
Case
When p.permit Like('PRP%') Then '0 PRP PERMIT'
When p.position_name Like('EXTRA%') And p.permit = '' Then '0 EXTRAORDINARY POSITION'
When p.position_name Like('EXTRA%') And p.permit != '' Then '1 PERMIT EXPIRED EXTRAORDINARY'
When p.passport != '' And p.permit_expire >= Date('1900-01-01') And p.permit_expire < Date('%TODAY%')
Then '1 PERMIT EXPIRED'
When p.passport != '' And p.permit_expire >= Date('1900-01-01') And p.permit_expire < Date('%MONTH%')
Then '1 PERMIT EXPIRE SOON'
When p.passport != '' And p.permit_expire = '' Then '1 BLANK PERMIT EXPIRY DATE'
Else '0 PERMIT EXPIRE IN FUTURE'
End as VALID,
p.position_name POSITION,
p.assignment_category ASSIGNMENT_CATEGORY,
Case
When pu.EMPLOYEE_NUMBER Is Not Null And
pu.EMPLOYEE_NUMBER Not In ('12795631','13277294') And
pu.ORG_NAME Like('NWU P&C REMUNERATION%') Then
pu.EMPLOYEE_NUMBER
When p.assignment_category = 'PERMANENT' Then '21022402'
Else '20742010'
End As TRAN_OWNER,
p.assignment_update_by As ASSIGN_USER_ID,
au.EMPLOYEE_NUMBER As ASSIGN_UPDATE,
au.NAME_ADDR As ASSIGN_UPDATE_NAME,
p.people_update_by As PEOPLE_USER_ID,
pu.EMPLOYEE_NUMBER As PEOPLE_UPDATE,
pu.NAME_ADDR As PEOPLE_UPDATE_NAME
From
X000_PEOPLE p Left Join
X000_USER_CURR au On au.USER_ID = p.assignment_update_by Left join
X000_USER_CURR pu On pu.USER_ID = p.people_update_by
Where
p.national_identifier = ''
Order By
VALID,
EMPLOYEE_NUMBER
;"""
s_sql = s_sql.replace("%TODAY%", funcdate.today())
s_sql = s_sql.replace("%MONTH%", funcdate.cur_monthendnext())
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# IDENTIFY FINDINGS
if l_debug:
print("Identify findings...")
sr_file = s_file_prefix + "b_finding"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
FIND.ORG,
FIND.LOC,
FIND.EMPLOYEE_NUMBER,
FIND.TRAN_OWNER,
FIND.ASSIGNMENT_CATEGORY,
FIND.VALID
From
%FILEP%%FILEN% FIND
Where
FIND.VALID Like ('1%')
;"""
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
s_sql = s_sql.replace("%FILEN%", "a_" + s_file_name)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# COUNT THE NUMBER OF FINDINGS
if l_debug:
print("Count the number of findings...")
i_finding_before: int = funcsys.tablerowcount(so_curs, sr_file)
funcfile.writelog("%t FINDING: " + str(i_finding_before) + " " +
s_finding + " finding(s)")
if l_debug:
print("*** Found " + str(i_finding_before) + " exceptions ***")
# GET PREVIOUS FINDINGS
if i_finding_before > 0:
functest.get_previous_finding(so_curs, ed_path, s_report_file,
s_finding, "TTTTT")
if l_debug:
so_conn.commit()
# SET PREVIOUS FINDINGS
if i_finding_before > 0:
functest.set_previous_finding(so_curs)
if l_debug:
so_conn.commit()
# ADD PREVIOUS FINDINGS
sr_file = s_file_prefix + "d_addprev"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if l_debug:
print("Join previously reported to current findings...")
s_sql = "CREATE TABLE " + sr_file + " AS" + """
Select
FIND.*,
Lower('%FINDING%') AS PROCESS,
'%TODAY%' AS DATE_REPORTED,
'%DATETEST%' AS DATE_RETEST,
PREV.PROCESS AS PREV_PROCESS,
PREV.DATE_REPORTED AS PREV_DATE_REPORTED,
PREV.DATE_RETEST AS PREV_DATE_RETEST,
PREV.REMARK
From
%FILEP%b_finding FIND Left Join
Z001ab_setprev PREV ON PREV.FIELD1 = FIND.EMPLOYEE_NUMBER
;"""
s_sql = s_sql.replace("%FINDING%", s_finding)
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
s_sql = s_sql.replace("%TODAY%", funcdate.today())
s_sql = s_sql.replace("%DATETEST%", funcdate.cur_monthendnext())
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# BUILD LIST TO UPDATE FINDINGS
sr_file = s_file_prefix + "e_newprev"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if l_debug:
print("Build list to update findings...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PREV.PROCESS,
PREV.EMPLOYEE_NUMBER AS FIELD1,
'' AS FIELD2,
'' AS FIELD3,
'' AS FIELD4,
'' AS FIELD5,
PREV.DATE_REPORTED,
PREV.DATE_RETEST,
PREV.REMARK
From
%FILEP%d_addprev PREV
Where
PREV.PREV_PROCESS Is Null Or
PREV.DATE_REPORTED > PREV.PREV_DATE_RETEST And PREV.REMARK = ""
;"""
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# Export findings to previous reported file
i_finding_after = funcsys.tablerowcount(so_curs, sr_file)
if i_finding_after > 0:
if l_debug:
print("*** " + str(i_finding_after) +
" Finding(s) to report ***")
sx_path = ed_path
sx_file = s_report_file[:-4]
# Read the header data
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# Write the data
if l_record:
funccsv.write_data(so_conn, "main", sr_file, sx_path,
sx_file, s_head, "a", ".txt")
funcfile.writelog("%t FINDING: " + str(i_finding_after) +
" new finding(s) to export")
funcfile.writelog("%t EXPORT DATA: " + sr_file)
if l_mess:
funcsms.send_telegram(
'', 'administrator', '<b>' + str(i_finding_before) +
'/' + str(i_finding_after) + '</b> ' + s_description)
else:
funcfile.writelog("%t FINDING: No new findings to export")
if l_debug:
print("*** No new findings to report ***")
# IMPORT OFFICERS FOR MAIL REPORTING PURPOSES
if i_finding_before > 0 and i_finding_after > 0:
functest.get_officer(so_curs, "HR",
"TEST " + s_finding + " OFFICER")
so_conn.commit()
# IMPORT SUPERVISORS FOR MAIL REPORTING PURPOSES
if i_finding_before > 0 and i_finding_after > 0:
functest.get_supervisor(so_curs, "HR",
"TEST " + s_finding + " SUPERVISOR")
so_conn.commit()
# ADD CONTACT DETAILS TO FINDINGS
sr_file = s_file_prefix + "h_detail"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0 and i_finding_after > 0:
if l_debug:
print("Add contact details to findings...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
Substr(PREV.VALID,3,40) As VALID,
PREV.ORG,
PREV.LOC,
PREV.EMPLOYEE_NUMBER,
PEOP.name_address As NAME_ADDRESS,
MAST.NATIONALITY,
MAST.PASSPORT,
MAST.PERMIT,
MAST.PERMIT_EXPIRE,
MAST.POSITION,
PREV.ASSIGNMENT_CATEGORY,
OWNR.EMPLOYEE_NUMBER AS TRAN_OWNER_NUMB,
OWNR.name_address AS TRAN_OWNER_NAME,
OWNR.EMAIL_ADDRESS AS TRAN_OWNER_MAIL1,
CASE
WHEN OWNR.EMPLOYEE_NUMBER != '' THEN OWNR.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE OWNR.EMAIL_ADDRESS
END AS TRAN_OWNER_MAIL2,
CAMP_OFF.EMPLOYEE_NUMBER AS CAMP_OFF_NUMB,
CAMP_OFF.NAME_ADDR AS CAMP_OFF_NAME,
CAMP_OFF.EMAIL_ADDRESS AS CAMP_OFF_MAIL1,
CASE
WHEN CAMP_OFF.EMPLOYEE_NUMBER != '' THEN CAMP_OFF.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE CAMP_OFF.EMAIL_ADDRESS
END AS CAMP_OFF_MAIL2,
CAMP_SUP.EMPLOYEE_NUMBER AS CAMP_SUP_NUMB,
CAMP_SUP.NAME_ADDR AS CAMP_SUP_NAME,
CAMP_SUP.EMAIL_ADDRESS AS CAMP_SUP_MAIL1,
CASE
WHEN CAMP_SUP.EMPLOYEE_NUMBER != '' THEN CAMP_SUP.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE CAMP_SUP.EMAIL_ADDRESS
END AS CAMP_SUP_MAIL2,
ORG_OFF.EMPLOYEE_NUMBER AS ORG_OFF_NUMB,
ORG_OFF.NAME_ADDR AS ORG_OFF_NAME,
ORG_OFF.EMAIL_ADDRESS AS ORG_OFF_MAIL1,
CASE
WHEN ORG_OFF.EMPLOYEE_NUMBER != '' THEN ORG_OFF.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE ORG_OFF.EMAIL_ADDRESS
END AS ORG_OFF_MAIL2,
ORG_SUP.EMPLOYEE_NUMBER AS ORG_SUP_NUMB,
ORG_SUP.NAME_ADDR AS ORG_SUP_NAME,
ORG_SUP.EMAIL_ADDRESS AS ORG_SUP_MAIL1,
CASE
WHEN ORG_SUP.EMPLOYEE_NUMBER != '' THEN ORG_SUP.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE ORG_SUP.EMAIL_ADDRESS
END AS ORG_SUP_MAIL2,
AUD_OFF.EMPLOYEE_NUMBER As AUD_OFF_NUMB,
AUD_OFF.NAME_ADDR As AUD_OFF_NAME,
AUD_OFF.EMAIL_ADDRESS As AUD_OFF_MAIL,
AUD_SUP.EMPLOYEE_NUMBER As AUD_SUP_NUMB,
AUD_SUP.NAME_ADDR As AUD_SUP_NAME,
AUD_SUP.EMAIL_ADDRESS As AUD_SUP_MAIL
From
%FILEP%d_addprev PREV Left Join
%FILEP%a_%FILEN% MAST On MAST.EMPLOYEE_NUMBER = PREV.EMPLOYEE_NUMBER Left Join
PEOPLE.X000_PEOPLE PEOP ON PEOP.EMPLOYEE_NUMBER = PREV.EMPLOYEE_NUMBER Left Join
PEOPLE.X000_PEOPLE OWNR ON OWNR.EMPLOYEE_NUMBER = PREV.TRAN_OWNER Left Join
Z001af_officer CAMP_OFF On CAMP_OFF.CAMPUS = PREV.ASSIGNMENT_CATEGORY Left Join
Z001af_officer ORG_OFF On ORG_OFF.CAMPUS = PREV.ORG Left Join
Z001af_officer AUD_OFF On AUD_OFF.CAMPUS = 'AUD' Left Join
Z001ag_supervisor CAMP_SUP On CAMP_SUP.CAMPUS = PREV.LOC Left Join
Z001ag_supervisor ORG_SUP On ORG_SUP.CAMPUS = PREV.ORG Left Join
Z001ag_supervisor AUD_SUP On AUD_SUP.CAMPUS = 'AUD'
Where
PREV.PREV_PROCESS Is Null Or
PREV.DATE_REPORTED > PREV.PREV_DATE_RETEST And PREV.REMARK = ""
;"""
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
s_sql = s_sql.replace("%FILEN%", s_file_name)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD THE FINAL TABLE FOR EXPORT AND REPORT
sr_file = s_file_prefix + "x_" + s_file_name
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0 and i_finding_after > 0:
if l_debug:
print("Build the final report")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'%FIND%' As Audit_finding,
FIND.VALID As Reason,
FIND.EMPLOYEE_NUMBER As Employee,
FIND.NAME_ADDRESS As Name,
FIND.NATIONALITY As Nationality,
FIND.PASSPORT As Passport,
FIND.PERMIT As Permit,
FIND.PERMIT_EXPIRE As Permit_expire,
FIND.POSITION As Position,
FIND.ASSIGNMENT_CATEGORY As Ass_category,
FIND.ORG As Organization,
FIND.LOC As Campus,
FIND.TRAN_OWNER_NAME AS Responsible_officer,
FIND.TRAN_OWNER_NUMB AS Responsible_officer_numb,
FIND.TRAN_OWNER_MAIL1 AS Responsible_officer_mail,
FIND.TRAN_OWNER_MAIL2 AS Responsible_officer_mail_alt,
FIND.CAMP_OFF_NAME AS Responsible_officer_2,
FIND.CAMP_OFF_NUMB AS Responsible_officer_2_numb,
FIND.CAMP_OFF_MAIL1 AS Responsible_officer_2_mail,
FIND.CAMP_OFF_MAIL2 AS Responsible_officer_2_mail_alt,
FIND.CAMP_SUP_NAME AS Supervisor,
FIND.CAMP_SUP_NUMB AS Supervisor_numb,
FIND.CAMP_SUP_MAIL1 AS Supervisor_mail,
FIND.ORG_OFF_NAME AS Org_officer,
FIND.ORG_OFF_NUMB AS Org_officer_numb,
FIND.ORG_OFF_MAIL1 AS Org_officer_mail,
FIND.ORG_SUP_NAME AS Org_supervisor,
FIND.ORG_SUP_NUMB AS Org_supervisor_numb,
FIND.ORG_SUP_MAIL1 AS Org_supervisor_mail,
FIND.AUD_OFF_NAME AS Audit_officer,
FIND.AUD_OFF_NUMB AS Audit_officer_numb,
FIND.AUD_OFF_MAIL AS Audit_officer_mail,
FIND.AUD_SUP_NAME AS Audit_supervisor,
FIND.AUD_SUP_NUMB AS Audit_supervisor_numb,
FIND.AUD_SUP_MAIL AS Audit_supervisor_mail
From
%FILEP%h_detail FIND
;"""
s_sql = s_sql.replace("%FIND%", s_finding)
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export findings
if l_export and funcsys.tablerowcount(so_curs, sr_file) > 0:
if l_debug:
print("Export findings...")
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = s_file_prefix + "_" + s_finding.lower() + "_"
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)
else:
s_sql = "CREATE TABLE " + sr_file + " (" + """
BLANK TEXT
);"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# MESSAGE
if l_mess:
funcsms.send_telegram(
"", "administrator",
"Finished <b>" + s_function.upper() + "</b> tests.")
""" ****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# CLOSE THE DATABASE CONNECTION
so_conn.commit()
so_conn.close()
# CLOSE THE LOG
funcfile.writelog("-" * len("completed: " + s_function))
funcfile.writelog("COMPLETED: " + s_function.upper())
return