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]
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)
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 = 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:
if l_debug:
print("*** No new findings to report ***")
funcfile.writelog("%t FINDING: No new findings to export")
def test_student_general():
"""
SCRIPT TO TEST STUDENT MASTER FILE
:return: Nothing
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# Declare variables
so_path = "W:/Vss_general/" # Source database path
re_path = "R:/Vss/" # Results
ed_path = "S:/_external_data/"
so_file = "Vss_general.sqlite" # Source database
s_sql = "" # SQL statements
l_mail = True
l_export = True
# OPEN THE LOG
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C300_TEST_STUDENT_GENERAL")
funcfile.writelog("---------------------------------")
print("-------------------------")
print("C300_TEST_STUDENT_GENERAL")
print("-------------------------")
# MESSAGE
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator", "<b>C300 Student master file tests</b>")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
print("OPEN THE DATABASES")
funcfile.writelog("OPEN THE DATABASES")
# Open the SOURCE file
with sqlite3.connect(so_path+so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
# ATTACH VSS DATABASE
print("Attach vss database...")
so_curs.execute("ATTACH DATABASE 'W:/Vss/Vss.sqlite' AS 'VSS'")
funcfile.writelog("%t ATTACH DATABASE: Vss.sqlite")
so_curs.execute("ATTACH DATABASE 'W:/Vss/Vss_curr.sqlite' AS 'VSSCURR'")
funcfile.writelog("%t ATTACH DATABASE: Vss_curr.sqlite")
so_curs.execute("ATTACH DATABASE 'W:/Vss/Vss_prev.sqlite' AS 'VSSPREV'")
funcfile.writelog("%t ATTACH DATABASE: Vss_prev.sqlite")
""" ****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
funcfile.writelog("%t ---------- STUDENT ID NUMBER LIST ----------")
# Import vss transactions from VSS.SQLITE *********************************
print("IDNo list import vss transactions from VSS.SQLITE...")
sr_file = "X001aa_impo_vsstran"
s_sql = "CREATE TABLE "+sr_file+" AS " + """
SELECT
TRAN.FBUSENTID AS STUDENT,
TRAN.FDEBTCOLLECTIONSITE AS CAMPUS,
SUBSTR(TRAN.TRANSDATE,1,4) AS YEAR
FROM
VSSCURR.X010_Studytrans TRAN
GROUP BY
TRAN.FBUSENTID
ORDER BY
TRAN.FBUSENTID
;"""
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 previously reported findings ******************************************
print("IDNo list import previously reported findings...")
tb_name = "X001ac_impo_reported"
so_curs.execute("DROP TABLE IF EXISTS " + tb_name)
so_curs.execute("CREATE TABLE " + tb_name + "(PROCESS TEXT,FIELD1 INT,FIELD2 TEXT,FIELD3 TEXT,FIELD4 TEXT,FIELD5 TEXT,DATE_REPORTED TEXT,DATE_RETEST TEXT)")
co = open(ed_path + "300_reported.txt", newline=None)
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] != "idno_list":
continue
else:
s_cols = "INSERT INTO " + tb_name + " VALUES('" + row[0] + "','" + row[1] + "','" + row[2] + "','" + row[3] + "','" + row[4] + "','" + row[5] + "','" + row[6] + "','" + row[7] + "')"
so_curs.execute(s_cols)
so_conn.commit()
# Close the imported data file
co.close()
funcfile.writelog("%t IMPORT TABLE: " + ed_path + "300_reported.txt (" + tb_name + ")" )
# Join the tran and party data *********************************************
print("IDNo list join the vss tran and party data...")
sr_file = "X001ba_join_tran_vss"
s_sql = "CREATE TABLE "+sr_file+" AS " + """
SELECT
VSS.X000_party.IDNO,
TRAN.STUDENT,
VSS.X000_Party.FULL_NAME AS NAME,
TRAN.YEAR,
TRAN.CAMPUS,
Trim(VSS.X000_Party.FIRSTNAMES) AS FIRSTNAME,
VSS.X000_Party.INITIALS,
VSS.X000_Party.SURNAME,
VSS.X000_Party.TITLE,
VSS.X000_Party.DATEOFBIRTH,
VSS.X000_Party.GENDER,
VSS.X000_Party.NATIONALITY,
VSS.X000_Party.POPULATION,
VSS.X000_Party.RACE,
VSS.X000_Party.FAUDITUSERCODE AS PARTY_AUDITDATETIME,
VSS.X000_Party.AUDITDATETIME AS PARTY_AUDITUSERCODE
FROM
X001aa_impo_vsstran TRAN Inner Join
VSS.X000_Party ON VSS.X000_Party.KBUSINESSENTITYID = TRAN.STUDENT AND
Length(Trim(VSS.X000_Party.IDNO)) = 13
ORDER BY
TRAN.STUDENT
;"""
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 previous reported ID list to current ID list *****************************
print("IDNo list join the previously reported id number list...")
sr_file = "X001ca_join_prev_reported"
s_sql = "CREATE TABLE " + sr_file + " AS" + """
SELECT
TRAN.IDNO,
TRAN.STUDENT,
TRAN.NAME,
TRAN.YEAR,
TRAN.CAMPUS,
TRAN.FIRSTNAME,
TRAN.INITIALS,
TRAN.SURNAME,
TRAN.TITLE,
TRAN.DATEOFBIRTH,
TRAN.GENDER,
TRAN.NATIONALITY,
TRAN.POPULATION,
TRAN.RACE,
TRAN.PARTY_AUDITDATETIME,
TRAN.PARTY_AUDITUSERCODE,
IMPO.PROCESS AS PREV_PROCESS,
IMPO.DATE_REPORTED AS PREV_DATE_REPORTED,
IMPO.DATE_RETEST AS PREV_DATE_RETEST
FROM
X001ba_join_tran_vss TRAN Left Join
X001ac_impo_reported IMPO ON IMPO.FIELD1 = TRAN.STUDENT AND IMPO.DATE_RETEST >= Date('%TODAY%')
;"""
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)
# Add columns used to export new ID list
print("IDNo list add prev reported columns...")
so_curs.execute("ALTER TABLE "+sr_file+" ADD COLUMN PROCESS TEXT;")
so_curs.execute("ALTER TABLE "+sr_file+" ADD COLUMN FIELD2 TEXT;")
so_curs.execute("ALTER TABLE "+sr_file+" ADD COLUMN DATE_REPORTED TEXT;")
so_curs.execute("ALTER TABLE "+sr_file+" ADD COLUMN DATE_RETEST TEXT;")
so_curs.execute("UPDATE "+sr_file+" SET PROCESS = 'idno_list'")
s_sql = "UPDATE "+sr_file+" SET DATE_REPORTED = '%TODAY%'"
s_sql = s_sql.replace("%TODAY%",funcdate.today())
so_curs.execute(s_sql)
s_sql = "UPDATE "+sr_file+" SET DATE_RETEST = '%NYEARB%'"
s_sql = s_sql.replace("%NYEARB%",funcdate.next_yearbegin())
so_curs.execute(s_sql)
so_conn.commit()
# Build the final ytd ID list report table *****************************************
print("IDNo list build the ytd ID list to export...")
sr_file = "X001da_report_idlist"
s_sql = "CREATE TABLE " + sr_file + " AS" + """
SELECT
PREV.IDNO,
PREV.STUDENT,
PREV.NAME,
PREV.YEAR,
PREV.CAMPUS,
PREV.FIRSTNAME,
PREV.INITIALS,
PREV.SURNAME,
PREV.TITLE,
PREV.DATEOFBIRTH,
PREV.GENDER,
PREV.NATIONALITY,
PREV.POPULATION,
PREV.RACE
FROM
X001ca_join_prev_reported PREV
ORDER BY
PREV.STUDENT
;"""
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 funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator", "<b> " + str(i) + "</b> " + " Student id numbers")
# Export the data
if l_export == True and funcsys.tablerowcount(so_curs,sr_file) > 0:
print("IDNo list export ytd ID list...")
sr_filet = sr_file
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = "Idno_001a_list_ytd_"
sx_filet = sx_file + funcdate.prev_monthendfile()
# Read the header data
s_head = funccsv.get_colnames_sqlite(so_conn, sr_filet)
# Write the data
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)
# Build the final ytd ID list report table *****************************************
print("IDNo list build the current ID list to export...")
sr_file = "X001da_report_idlist"
s_sql = "CREATE TABLE " + sr_file + " AS" + """
SELECT
PREV.IDNO,
PREV.STUDENT,
PREV.NAME,
PREV.YEAR,
PREV.CAMPUS,
PREV.FIRSTNAME,
PREV.INITIALS,
PREV.SURNAME,
PREV.TITLE,
PREV.DATEOFBIRTH,
PREV.GENDER,
PREV.NATIONALITY,
PREV.POPULATION,
PREV.RACE
FROM
X001ca_join_prev_reported PREV
WHERE
StrfTime('%m', PREV.PREV_DATE_REPORTED) = StrfTime('%m', 'now') OR
StrfTime('%m', PREV.DATE_REPORTED) = StrfTime('%m', 'now') AND PREV.PREV_PROCESS IS NULL
ORDER BY
PREV.STUDENT
;"""
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 the data
if l_export == True and funcsys.tablerowcount(so_curs,sr_file) > 0:
print("IDNo list export current ID lists...")
sr_filet = sr_file
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = "Idno_001b_list_curr_"
sx_filet = sx_file + funcdate.cur_month()
# Read the header data
s_head = funccsv.get_colnames_sqlite(so_conn, sr_filet)
# Write the data
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)
# Build new ID list file to export to external previous reported file **********
print("IDNo list add new id number list to previous reported...")
sr_file = "X001ea_prev_reported"
s_sql = "CREATE TABLE "+sr_file+" AS " + """
SELECT
PREV.PROCESS,
PREV.STUDENT AS FIELD1,
PREV.FIELD2,
PREV.FIELD2 AS FIELD3,
PREV.FIELD2 AS FIELD4,
PREV.FIELD2 AS FIELD5,
PREV.DATE_REPORTED,
PREV.DATE_RETEST
FROM
X001ca_join_prev_reported 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 the new ID list to previous reported file
if funcsys.tablerowcount(so_curs,sr_file) > 0:
print("IDNo list export the new data to previously reported file...")
sr_filet = sr_file
sx_path = ed_path
sx_file = "300_reported"
# Read the header data
s_head = funccsv.get_colnames_sqlite(so_conn, sr_filet)
# Write the data
funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_file, s_head,"a",".txt")
funcfile.writelog("%t EXPORT DATA: "+sr_file)
else:
print("IDNo list no new data to previously reported file...")
funcfile.writelog("%t EXPORT DATA: No new data to export")
if l_mail:
funcmail.Mail("vss_list_idno_ytd")
if l_mail:
funcmail.Mail("vss_list_idno_curr")
# MESSAGE
# if funcconf.l_mess_project:
# funcsms.send_telegram("", "administrator", "<b>VSS STUDENT</b> master file tests end.")
"""*****************************************************************************
End OF SCRIPT
*****************************************************************************"""
print("END OF SCRIPT")
funcfile.writelog("END OF SCRIPT")
# COMMIT DATA
so_conn.commit()
# CLOSE THE DATABASE CONNECTION
so_conn.close()
# CLOSE THE LOG WRITER
funcfile.writelog("------------------------------------")
funcfile.writelog("COMPLETED: C300_TEST_STUDENT_GENERAL")
return
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
def payroll_lists():
"""
Script to build payroll lists
:return: Nothing
"""
""" CONTENTS ***************************************************************
ELEMENTS
BALANCES
SECONDARY ASSIGNMENTS
*************************************************************************"""
# Declare variables
so_path: str = "W:/People_payroll/" # Source database path
so_file: str = "People_payroll.sqlite" # Source database
re_path = "R:/People/" #Results
ed_path = "S:/_external_data/"
s_sql = "" #SQL statements
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B004_PAYROLL_LISTS")
funcfile.writelog("--------------------------")
print("------------------")
print("B004_PAYROLL_LISTS")
print("------------------")
# MESSAGE
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator", "<b>B004 Payroll lists</b>")
# Open the SOURCE file
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")
"""*************************************************************************
START
*************************************************************************"""
"""*************************************************************************
ELEMENTS CURRENT
*************************************************************************"""
print("---------- ELEMENTS CURRENT ----------")
# Build the current element list *******************************************
print("Build the current element list...")
sr_file = "X000aa_element_list_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
PEE.ASSIGNMENT_ID,
PEE.ELEMENT_ENTRY_ID,
PEE.EFFECTIVE_START_DATE,
PEE.EFFECTIVE_END_DATE,
PEE.ELEMENT_LINK_ID,
PEE.CREATOR_TYPE,
PEE.ENTRY_TYPE,
PEE.ELEMENT_TYPE_ID,
PEV.ELEMENT_ENTRY_VALUE_ID,
PEV.INPUT_VALUE_ID,
PEV.SCREEN_ENTRY_VALUE,
PET.ELEMENT_NAME,
PET.REPORTING_NAME,
PET.DESCRIPTION
FROM
PAY_ELEMENT_ENTRIES_F_CURR PEE LEFT JOIN
PAY_ELEMENT_ENTRY_VALUES_F_CURR PEV ON PEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND
PEV.EFFECTIVE_START_DATE <= PEE.EFFECTIVE_START_DATE AND
PEV.EFFECTIVE_END_DATE >= PEE.EFFECTIVE_START_DATE LEFT JOIN
PAY_ELEMENT_TYPES_F PET ON PET.ELEMENT_TYPE_ID = PEE.ELEMENT_TYPE_ID AND
PET.EFFECTIVE_START_DATE <= PEE.EFFECTIVE_START_DATE AND
PET.EFFECTIVE_END_DATE >= PEE.EFFECTIVE_START_DATE
;"""
so_curs.execute("DROP TABLE IF EXISTS X000aa_element_list")
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
# s_sql = s_sql.replace("%PMONTH%",funcdate.prev_month())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b>" + str(i) + "</b> Elements")
# Extract the NWU TOTAL PACKAGE element for export *********************
print("Extract the nwu total package element...")
sr_file = "X001aa_element_package_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X000aa_element_list_curr.ASSIGNMENT_ID,
X000aa_element_list_curr.EFFECTIVE_START_DATE,
X000aa_element_list_curr.INPUT_VALUE_ID,
X000aa_element_list_curr.SCREEN_ENTRY_VALUE,
X000aa_element_list_curr.ELEMENT_NAME,
SUBSTR(PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER,1,8) AS EMPL_NUMB
FROM
X000aa_element_list_curr
LEFT JOIN PEOPLE.PER_ALL_ASSIGNMENTS_F ON PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID = X000aa_element_list_curr.ASSIGNMENT_ID AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE <= Date('%TODAY%') AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE >= Date('%TODAY%')
WHERE
X000aa_element_list_curr.INPUT_VALUE_ID = 691 AND
X000aa_element_list_curr.EFFECTIVE_START_DATE <= Date('%TODAY%') AND
X000aa_element_list_curr.EFFECTIVE_END_DATE >= Date('%TODAY%')
;"""
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)
# Build the NWU TOTAL PACKAGE export file **************************************
print("Build the nwu total package element export file...")
sr_file = "X001ax_element_package_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X001aa_element_package_curr.EMPL_NUMB,
X001aa_element_package_curr.EFFECTIVE_START_DATE AS DATE,
CAST(X001aa_element_package_curr.SCREEN_ENTRY_VALUE AS REAL) AS PACKAGE
FROM
X001aa_element_package_curr
ORDER BY
X001aa_element_package_curr.EMPL_NUMB
;"""
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 the data
print("Export packages...")
sr_filet = sr_file
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = "Payroll_001ax_package_"
sx_filet = sx_file + funcdate.cur_monthendfile()
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)
"""*************************************************************************
ELEMENTS PREVIOUS
*************************************************************************"""
print("---------- ELEMENTS PREVIOUS ----------")
# Build the previous element list *******************************************
print("Build the previous element list...")
sr_file = "X000aa_element_list_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
PAY_ELEMENT_ENTRIES_F_PREV.ASSIGNMENT_ID,
PAY_ELEMENT_ENTRIES_F_PREV.ELEMENT_ENTRY_ID,
PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_START_DATE,
PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_END_DATE,
PAY_ELEMENT_ENTRIES_F_PREV.ELEMENT_LINK_ID,
PAY_ELEMENT_ENTRIES_F_PREV.CREATOR_TYPE,
PAY_ELEMENT_ENTRIES_F_PREV.ENTRY_TYPE,
PAY_ELEMENT_ENTRIES_F_PREV.ELEMENT_TYPE_ID,
PAY_ELEMENT_ENTRY_VALUES_F_PREV.ELEMENT_ENTRY_VALUE_ID,
PAY_ELEMENT_ENTRY_VALUES_F_PREV.INPUT_VALUE_ID,
PAY_ELEMENT_ENTRY_VALUES_F_PREV.SCREEN_ENTRY_VALUE,
PAY_ELEMENT_TYPES_F.ELEMENT_NAME,
PAY_ELEMENT_TYPES_F.REPORTING_NAME,
PAY_ELEMENT_TYPES_F.DESCRIPTION
FROM
PAY_ELEMENT_ENTRIES_F_PREV
LEFT JOIN PAY_ELEMENT_ENTRY_VALUES_F_PREV ON PAY_ELEMENT_ENTRY_VALUES_F_PREV.ELEMENT_ENTRY_ID =
PAY_ELEMENT_ENTRIES_F_PREV.ELEMENT_ENTRY_ID AND PAY_ELEMENT_ENTRY_VALUES_F_PREV.EFFECTIVE_START_DATE <=
PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_START_DATE AND PAY_ELEMENT_ENTRY_VALUES_F_PREV.EFFECTIVE_END_DATE >= PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_START_DATE
LEFT JOIN PAY_ELEMENT_TYPES_F ON PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID = PAY_ELEMENT_ENTRIES_F_PREV.ELEMENT_TYPE_ID AND
PAY_ELEMENT_TYPES_F.EFFECTIVE_START_DATE <= PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_START_DATE AND PAY_ELEMENT_TYPES_F.EFFECTIVE_END_DATE >=
PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_START_DATE
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Extract the previous NWU TOTAL PACKAGE element for export ****************
print("Extract the previous nwu total package element...")
sr_file = "X001aa_element_package_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X000aa_element_list_prev.ASSIGNMENT_ID,
X000aa_element_list_prev.EFFECTIVE_START_DATE,
X000aa_element_list_prev.INPUT_VALUE_ID,
X000aa_element_list_prev.SCREEN_ENTRY_VALUE,
X000aa_element_list_prev.ELEMENT_NAME,
SUBSTR(PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER,1,8) AS EMPL_NUMB
FROM
X000aa_element_list_prev
LEFT JOIN PEOPLE.PER_ALL_ASSIGNMENTS_F ON PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID = X000aa_element_list_prev.ASSIGNMENT_ID AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE <= Date('%PYEARE%') AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE >= Date('%PYEARE%')
WHERE
X000aa_element_list_prev.INPUT_VALUE_ID = 691 AND
X000aa_element_list_prev.EFFECTIVE_START_DATE <= Date('%PYEARE%') AND
X000aa_element_list_prev.EFFECTIVE_END_DATE >= Date('%PYEARE%')
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%PYEARE%", funcdate.prev_yearend())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Build the previous NWU TOTAL PACKAGE export file *************************
print("Build the previous nwu total package element export file...")
sr_file = "X001ax_element_package_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X001aa_element_package_prev.EMPL_NUMB,
X001aa_element_package_prev.EFFECTIVE_START_DATE AS DATE,
CAST(X001aa_element_package_prev.SCREEN_ENTRY_VALUE AS REAL) AS PACKAGE
FROM
X001aa_element_package_prev
ORDER BY
X001aa_element_package_prev.EMPL_NUMB
;"""
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 the data
print("Export previous packages...")
sr_filet = sr_file
sx_path = re_path + funcdate.prev_year() + "/"
sx_file = "Payroll_001ax_package_"
# sx_filet = sx_file + funcdate.prev_monthendfile()
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)
"""*************************************************************************
BALANCES CURRENT
*************************************************************************"""
print("---------- BALANCES CURRENT ----------")
# Build the balances list ******************************************************
print("Build the balances list...")
sr_file = "X000aa_balance_list_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
PAY_RUN_BALANCES_CURR.ASSIGNMENT_ID,
PAY_RUN_BALANCES_CURR.EFFECTIVE_DATE,
PAY_RUN_BALANCES_CURR.BALANCE_VALUE,
PAY_RUN_BALANCES_CURR.RUN_BALANCE_ID,
PAY_RUN_BALANCES_CURR.DEFINED_BALANCE_ID,
PAY_DEFINED_BALANCES.BALANCE_TYPE_ID,
PAY_BALANCE_TYPES.BALANCE_NAME,
PAY_BALANCE_TYPES.REPORTING_NAME,
PAY_BALANCE_TYPES.BALANCE_UOM,
PAY_BALANCE_TYPES.BALANCE_CATEGORY_ID
FROM
PAY_RUN_BALANCES_CURR
LEFT JOIN PAY_DEFINED_BALANCES ON PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID = PAY_RUN_BALANCES_CURR.DEFINED_BALANCE_ID
LEFT JOIN PAY_BALANCE_TYPES ON PAY_BALANCE_TYPES.BALANCE_TYPE_ID = PAY_DEFINED_BALANCES.BALANCE_TYPE_ID
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b>" + str(i) + "</b> Balances")
# Extract the NWU INCOME PER MONTH balance for export **************************
print("Extract the nwu total income balance...")
sr_file = "X002aa_balance_totalincome_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X000aa_balance_list_curr.ASSIGNMENT_ID,
X000aa_balance_list_curr.EFFECTIVE_DATE,
X000aa_balance_list_curr.DEFINED_BALANCE_ID,
X000aa_balance_list_curr.BALANCE_VALUE,
X000aa_balance_list_curr.BALANCE_NAME,
X000aa_balance_list_curr.REPORTING_NAME,
SUBSTR(PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER,1,8) AS EMPL_NUMB
FROM
X000aa_balance_list_curr
LEFT JOIN PEOPLE.PER_ALL_ASSIGNMENTS_F ON PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID = X000aa_balance_list_curr.ASSIGNMENT_ID AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE <= Date('%PMONTHEND%') AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE >= Date('%PMONTHEND%')
WHERE
X000aa_balance_list_curr.DEFINED_BALANCE_ID = 16264 AND
X000aa_balance_list_curr.EFFECTIVE_DATE = Date('%PMONTHEND%')
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%PMONTHEND%", funcdate.prev_monthend())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Build the NWU TOTAL INCOME export file ***************************************
print("Build the nwu total income balance export file...")
sr_file = "X002ax_balance_totalincome_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X002aa_balance_totalincome_curr.EMPL_NUMB,
X002aa_balance_totalincome_curr.EFFECTIVE_DATE AS DATE,
CAST(X002aa_balance_totalincome_curr.BALANCE_VALUE AS REAL) AS INCOME
FROM
X002aa_balance_totalincome_curr
ORDER BY
X002aa_balance_totalincome_curr.EMPL_NUMB
;"""
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 the data
print("Export incomes...")
sr_filet = sr_file
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = "Payroll_002ax_income_total_"
sx_filet = sx_file + funcdate.prev_monthendfile()
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)
"""*************************************************************************
BALANCES PREVIOUS
*************************************************************************"""
print("---------- BALANCES PREVIOUS ----------")
# Build the previous balances list *********************************************
print("Build the previous balances list...")
sr_file = "X000aa_balance_list_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
PAY_RUN_BALANCES_PREV.ASSIGNMENT_ID,
PAY_RUN_BALANCES_PREV.EFFECTIVE_DATE,
PAY_RUN_BALANCES_PREV.BALANCE_VALUE,
PAY_RUN_BALANCES_PREV.RUN_BALANCE_ID,
PAY_RUN_BALANCES_PREV.DEFINED_BALANCE_ID,
PAY_DEFINED_BALANCES.BALANCE_TYPE_ID,
PAY_BALANCE_TYPES.BALANCE_NAME,
PAY_BALANCE_TYPES.REPORTING_NAME,
PAY_BALANCE_TYPES.BALANCE_UOM,
PAY_BALANCE_TYPES.BALANCE_CATEGORY_ID
FROM
PAY_RUN_BALANCES_PREV
LEFT JOIN PAY_DEFINED_BALANCES ON PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID = PAY_RUN_BALANCES_PREV.DEFINED_BALANCE_ID
LEFT JOIN PAY_BALANCE_TYPES ON PAY_BALANCE_TYPES.BALANCE_TYPE_ID = PAY_DEFINED_BALANCES.BALANCE_TYPE_ID
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Extract the previous NWU INCOME PER MONTH balance for export *****************
print("Extract the previous nwu total income balance...")
sr_file = "X002aa_balance_totalincome_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X000aa_balance_list_prev.ASSIGNMENT_ID,
X000aa_balance_list_prev.EFFECTIVE_DATE,
X000aa_balance_list_prev.DEFINED_BALANCE_ID,
X000aa_balance_list_prev.BALANCE_VALUE,
X000aa_balance_list_prev.BALANCE_NAME,
X000aa_balance_list_prev.REPORTING_NAME,
SUBSTR(PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER,1,8) AS EMPL_NUMB
FROM
X000aa_balance_list_prev
LEFT JOIN PEOPLE.PER_ALL_ASSIGNMENTS_F ON PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID = X000aa_balance_list_prev.ASSIGNMENT_ID AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE <= Date('%PYEARE%') AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE >= Date('%PYEARE%')
WHERE
X000aa_balance_list_prev.DEFINED_BALANCE_ID = 16264 AND
X000aa_balance_list_prev.EFFECTIVE_DATE = Date('%PYEARE%')
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%PYEARE%", funcdate.prev_yearend())
# print(s_sql)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Build the previous NWU TOTAL INCOME export file ******************************
print("Build the previous nwu total income balance export file...")
sr_file = "X002ax_balance_totalincome_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X002aa_balance_totalincome_prev.EMPL_NUMB,
X002aa_balance_totalincome_prev.EFFECTIVE_DATE AS DATE,
CAST(X002aa_balance_totalincome_prev.BALANCE_VALUE AS REAL) AS INCOME
FROM
X002aa_balance_totalincome_prev
ORDER BY
X002aa_balance_totalincome_prev.EMPL_NUMB
;"""
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 the data
print("Export previous incomes...")
sr_filet = sr_file
sx_path = re_path + funcdate.prev_year() + "/"
sx_file = "Payroll_002ax_income_total_"
# sx_filet = sx_file + funcdate.prev_monthendfile()
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)
"""*************************************************************************
SECONDARY ASSIGNMENTS
*************************************************************************"""
print("---------- SECONDARY ASSIGNMENTS ----------")
# Build previous secondary assignments *************************************
print("Build previous secondary assignments...")
sr_file = "X000aa_sec_assignment_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.ASSIGNMENT_EXTRA_INFO_ID,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.ASSIGNMENT_ID,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION1,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION2,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION3,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION4,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION5,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION6,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION7,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION8,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION9,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION10,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION11,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION12 AS DATE_FROM,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION13 AS DATE_TO,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION14,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION15,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION16,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION17,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION18,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION19,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION20,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION21,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION22,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION23,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION24,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION25,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION26,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION27,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION28,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION29,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION30,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.LAST_UPDATE_DATE,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.LAST_UPDATED_BY,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.LAST_UPDATE_LOGIN,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.CREATED_BY,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.CREATION_DATE
FROM
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC
WHERE
DATE_FROM <= Date('2018-12-31') AND
DATE_TO >= Date('2018-12-31')
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute("DROP TABLE IF EXISTS X000aa_sec_assignment_prev")
s_sql = s_sql.replace("%PYEARE%", funcdate.prev_yearend())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
"""*****************************************************************************
End OF SCRIPT
*****************************************************************************"""
print("END OF SCRIPT")
funcfile.writelog("END OF SCRIPT")
# COMMIT DATA
so_conn.commit()
# CLOSE THE DATABASE CONNECTION
so_conn.close()
# CLOSE THE LOG WRITER
funcfile.writelog("-----------------------------")
funcfile.writelog("COMPLETED: B004_PAYROLL_LISTS")
return
def kfs_period_list(s_period="curr"):
"""
Script to build standard KFS lists
:type s_period: str: The financial period (curr, prev or year)
:return: Nothing
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# DECLARE VARIABLES
l_debug: bool = True
l_export: bool = True
so_path = "W:/Kfs/" # Source database path
re_path = "R:/Kfs/" # Results path
# ed_path = "S:/_external_data/" # external data path
if s_period == "curr":
s_year = funcdate.cur_year()
so_file = "Kfs_curr.sqlite" # Source database
elif s_period == "prev":
s_year = funcdate.prev_year()
so_file = "Kfs_prev.sqlite" # Source database
else:
s_year = s_period
so_file = "Kfs_" + s_year + ".sqlite" # Source database
# OPEN THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B006_KFS_PERIOD_LIST")
funcfile.writelog("----------------------------")
print("--------------------")
print("B006_KFS_PERIOD_LIST")
print("--------------------")
# MESSAGE
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator",
"<b>B006 Kfs " + s_year + " period lists</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 'W:/Kfs/Kfs.sqlite' AS 'KFS'")
funcfile.writelog("%t ATTACH DATABASE: KFS.SQLITE")
if s_period == "curr":
so_curs.execute(
"ATTACH DATABASE 'W:/Kfs/Kfs_prev.sqlite' AS 'KFSPREV'")
funcfile.writelog("%t ATTACH DATABASE: KFS_PREV.SQLITE")
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
""" ****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
""" ****************************************************************************
GL TRANSACTION LIST
*****************************************************************************"""
print("GL TRANSACTION LIST")
funcfile.writelog("GL TRANSACTION LIST")
# BUILD GL TRANSACTION LIST
print("Build gl transaction list...")
sr_file = "X000_GL_trans"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
GLC.UNIV_FISCAL_YR,
GLC.UNIV_FISCAL_PRD_CD,
GLC.CALC_COST_STRING,
ACC.ORG_NM,
ACC.ACCOUNT_NM,
OBJ.FIN_OBJ_CD_NM,
GLC.TRANSACTION_DT,
GLC.FDOC_NBR,
GLC.CALC_AMOUNT,
GLC.TRN_LDGR_ENTR_DESC,
ACC.ACCT_TYP_NM,
GLC.TRN_POST_DT,
GLC."TIMESTAMP",
GLC.FIN_COA_CD,
GLC.ACCOUNT_NBR,
GLC.FIN_OBJECT_CD,
GLC.FIN_BALANCE_TYP_CD,
GLC.FIN_OBJ_TYP_CD,
GLC.FDOC_TYP_CD,
GLC.FS_ORIGIN_CD,
ORI.FS_DATABASE_DESC,
GLC.TRN_ENTR_SEQ_NBR,
GLC.FDOC_REF_TYP_CD,
GLC.FS_REF_ORIGIN_CD,
GLC.FDOC_REF_NBR,
GLC.FDOC_REVERSAL_DT,
GLC.TRN_ENCUM_UPDT_CD
FROM
GL_ENTRY_T GLC Left Join
KFS.X000_Account ACC ON ACC.FIN_COA_CD = GLC.FIN_COA_CD AND ACC.ACCOUNT_NBR = GLC.ACCOUNT_NBR Left Join
KFS.CA_OBJECT_CODE_T OBJ ON OBJ.UNIV_FISCAL_YR = GLC.UNIV_FISCAL_YR AND
OBJ.FIN_COA_CD = GLC.FIN_COA_CD AND
OBJ.FIN_OBJECT_CD = GLC.FIN_OBJECT_CD Left Join
KFS.FS_ORIGIN_CODE_T ORI ON ORI.FS_ORIGIN_CD = GLC.FS_ORIGIN_CD
ORDER BY
GLC.CALC_COST_STRING,
GLC.UNIV_FISCAL_PRD_CD
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: GL Transaction list")
# MESSAGE
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b> " + str(i) + "</b> GL transactions")
""" ****************************************************************************
PAYMENT SUMMARY LIST
*****************************************************************************"""
print("PAYMENT SUMMARY LIST")
funcfile.writelog("PAYMENT SUMMARY LIST")
# BUILD PAYMENTS SUMMARY LIST
print("Build payments...")
sr_file = "X000_Payments"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
DET.CUST_PMT_DOC_NBR As EDOC,
PAY.PMT_DT,
DET.REQS_NBR,
DET.PO_NBR,
Case
When PR.FDOC_NBR Is Not Null Then PR.INV_NBR
When DV.FDOC_NBR Is Not Null Then DV.INV_NBR
Else DET.INV_NBR
End As INV_NBR,
Case
When PR.FDOC_NBR Is Not Null Then PR.INV_DT
When DV.FDOC_NBR Is Not Null Then DV.INV_DATE
Else DET.INV_DT
End As INV_DT,
DET.ORIG_INV_AMT,
PAY.PAYEE_ID AS PAYEE_ID,
PAY.PAYEE_ID_TYP_CD AS PAYEE_TYPE,
PAY.PMT_PAYEE_NM As PAYEE_NAME,
CASE
WHEN PAY.PAYEE_ID_TYP_CD = 'E' THEN 'EM'
WHEN PAY.PAYEE_ID_TYP_CD = 'S' THEN 'ST'
WHEN PAY.PAYEE_ID_TYP_CD = 'V' AND PAY.PAYEE_OWNR_CD = '' THEN 'OT'
ELSE PAY.PAYEE_OWNR_CD
END As PAYEE_OWNR_CD_CALC,
DET.NET_PMT_AMT,
PAY.PMT_GRP_ID,
PAY.DISB_NBR,
PAY.DISB_TS,
PAY.PMT_STAT_CD,
DOC.DOC_TYP_NM As DOC_TYPE,
Upper(DOC.LBL) As DOC_LABEL,
PAY.PMT_TXBL_IND,
PAY.ADV_EMAIL_ADDR,
PAY.PMT_FIL_ID,
PAY.PROC_ID,
DET.PMT_DTL_ID
From
PDP_PMT_GRP_T PAY Left Join
KFS.PDP_PMT_DTL_T DET On DET.PMT_GRP_ID = PAY.PMT_GRP_ID Left Join
KFS.AP_PMT_RQST_T PR On PR.FDOC_NBR = DET.CUST_PMT_DOC_NBR Left Join
KFS.FP_DV_PAYEE_DTL_EXT_T DV On DV.FDOC_NBR = DET.CUST_PMT_DOC_NBR Left Join
KFS.X000_Document DOC On DOC.DOC_HDR_ID = DET.CUST_PMT_DOC_NBR
Where
DET.CUST_PMT_DOC_NBR Is Not 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)
# MESSAGE
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b> " + str(i) + "</b> Payments")
""" ****************************************************************************
PAYMENT INITIATE LIST
*****************************************************************************"""
print("PAYMENT INITIATE LIST")
funcfile.writelog("PAYMENT INITIATE LIST")
# BUILD INITIATOR LIST
print("Build initiate list...")
sr_file = "X000_Initiate"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select Distinct
ROUTE.ACTN_TKN_ID,
ROUTE.DOC_HDR_ID,
ROUTE.ACTN_DT,
ROUTE.PRNCPL_ID,
CASE
WHEN ROUTE.PRNCPL_ID = '26807815' THEN 'KFS WORKFLOW SYSTEM USER'
WHEN PERSON.NAME_ADDR IS NULL THEN 'UNKNOWN'
ELSE PERSON.NAME_ADDR
END AS NAME_ADDR,
ROUTE.ACTN_CD,
CASE
WHEN ROUTE.ACTN_CD = 'C' THEN 'COMPLETED'
ELSE 'OTHER'
END AS ACTN,
ROUTE.ANNOTN,
PERSON.ORG_NAME
From
KREW_ACTN_TKN_T_COM ROUTE Left Join
PEOPLE.X002_PEOPLE_CURR_YEAR PERSON On PERSON.EMPLOYEE_NUMBER = ROUTE.PRNCPL_ID
Order By
ROUTE.ACTN_DT,
ROUTE.ACTN_TKN_ID
"""
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 UNIQUE INITIATOR LIST
print("Build unique initiate list...")
sr_file = "X000_Initiate_unique"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select Distinct
ROUTE.ACTN_TKN_ID,
ROUTE.DOC_HDR_ID,
Min(ROUTE.ACTN_DT) As ACTN_DT,
ROUTE.PRNCPL_ID,
CASE
WHEN ROUTE.PRNCPL_ID = '26807815' THEN 'KFS WORKFLOW SYSTEM USER'
WHEN PERSON.NAME_ADDR IS NULL THEN 'UNKNOWN'
ELSE PERSON.NAME_ADDR
END AS NAME_ADDR,
ROUTE.ACTN_CD,
CASE
WHEN ROUTE.ACTN_CD = 'C' THEN 'COMPLETED'
ELSE 'OTHER'
END AS ACTN,
ROUTE.ANNOTN,
Count(ROUTE.DOC_VER_NBR) As COM_COUNT,
PERSON.ORG_NAME
From
KREW_ACTN_TKN_T_COM ROUTE Left Join
PEOPLE.X002_PEOPLE_CURR_YEAR PERSON On PERSON.EMPLOYEE_NUMBER = ROUTE.PRNCPL_ID
Group By
ROUTE.DOC_HDR_ID
Order By
ROUTE.ACTN_DT,
ROUTE.ACTN_TKN_ID
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
PAYMENT APPROVE LIST
*****************************************************************************"""
print("PAYMENT APPROVE LIST")
funcfile.writelog("PAYMENT APPROVE LIST")
# BUILD CURR APPROVALS ALL APPROVERS
print("Build approve list...")
sr_file = "X000_Approve"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select Distinct
ROUTE.ACTN_TKN_ID,
ROUTE.DOC_HDR_ID,
ROUTE.ACTN_DT,
ROUTE.PRNCPL_ID,
CASE
WHEN ROUTE.PRNCPL_ID = '26807815' THEN 'KFS WORKFLOW SYSTEM USER'
WHEN PERSON.NAME_ADDR IS NULL THEN 'UNKNOWN'
ELSE PERSON.NAME_ADDR
END AS NAME_ADDR,
ROUTE.ACTN_CD,
CASE
WHEN ROUTE.ACTN_CD = 'a' THEN 'SUPER USER APPROVED'
WHEN ROUTE.ACTN_CD = 'A' THEN 'APPROVED'
WHEN ROUTE.ACTN_CD = 'B' THEN 'BLANKET APPROVED'
WHEN ROUTE.ACTN_CD = 'r' THEN 'SUPER USER ROUTE LEVEL APPROVED'
WHEN ROUTE.ACTN_CD = 'R' THEN 'SUPER USER ROUTE LEVEL APPROVED'
WHEN ROUTE.ACTN_CD = 'v' THEN 'SUPER USER APPROVED'
ELSE 'OTHER'
END AS ACTN,
ROUTE.ANNOTN,
PERSON.ORG_NAME
From
KREW_ACTN_TKN_T_APP ROUTE Left Join
PEOPLE.X002_PEOPLE_CURR_YEAR PERSON On PERSON.EMPLOYEE_NUMBER = ROUTE.PRNCPL_ID
Order By
ROUTE.ACTN_DT,
ROUTE.ACTN_TKN_ID
"""
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 CURR APPROVALS UNIQUE LIST OF LAST APPROVER
print("Build unique approve list...")
sr_file = "X000_Approve_unique"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select Distinct
ROUTE.ACTN_TKN_ID,
ROUTE.DOC_HDR_ID,
Min(ROUTE.ACTN_DT) As ACTN_DT,
ROUTE.PRNCPL_ID,
CASE
WHEN ROUTE.PRNCPL_ID = '26807815' THEN 'KFS WORKFLOW SYSTEM USER'
WHEN PERSON.NAME_ADDR IS NULL THEN 'UNKNOWN'
ELSE PERSON.NAME_ADDR
END AS NAME_ADDR,
ROUTE.ACTN_CD,
CASE
WHEN ROUTE.ACTN_CD = 'a' THEN 'SUPER USER APPROVED'
WHEN ROUTE.ACTN_CD = 'A' THEN 'APPROVED'
WHEN ROUTE.ACTN_CD = 'B' THEN 'BLANKET APPROVED'
WHEN ROUTE.ACTN_CD = 'r' THEN 'SUPER USER ROUTE LEVEL APPROVED'
WHEN ROUTE.ACTN_CD = 'R' THEN 'SUPER USER ROUTE LEVEL APPROVED'
WHEN ROUTE.ACTN_CD = 'v' THEN 'SUPER USER APPROVED'
ELSE 'OTHER'
END AS ACTN,
ROUTE.ANNOTN,
Count(ROUTE.DOC_VER_NBR) As APP_COUNT,
PERSON.ORG_NAME
From
KREW_ACTN_TKN_T_APP ROUTE Left Join
PEOPLE.X002_PEOPLE_CURR_YEAR PERSON On PERSON.EMPLOYEE_NUMBER = ROUTE.PRNCPL_ID
Group By
ROUTE.DOC_HDR_ID
Having
ORG_NAME Is Not NULL And
ORG_NAME Not In ('NWU PURCHASE AND PAYMENTS')
Order By
ROUTE.ACTN_DT,
ROUTE.ACTN_TKN_ID
"""
"""
Having
ORG_NAME Is Not 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)
""" ****************************************************************************
PAYMENT ACCOUNTING LINE
*****************************************************************************"""
print("PAYMENT ACCOUNTING LINE")
funcfile.writelog("PAYMENT ACCOUNTING LINE")
# BUILD ACCOUNTING LINES
print("Build account lines...")
sr_file = "X000_Account_line"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
LINE.FDOC_NBR,
LINE.FDOC_LINE_NBR,
LINE.FDOC_POST_YR,
Trim(LINE.FIN_COA_CD)||'.'||Trim(LINE.ACCOUNT_NBR)||'.'||Trim(LINE.FIN_OBJECT_CD) As COST_STRING,
Case
When LINE.FDOC_LINE_DBCR_CD = "C" Then LINE.FDOC_LINE_AMT * -1
Else LINE.FDOC_LINE_AMT
End As AMOUNT,
LINE.VATABLE,
LINE.FDOC_LINE_DESC,
ACC.ORG_NM,
ACC.ACCOUNT_NM,
OBJ.FIN_OBJ_CD_NM,
ACC.ACCT_TYP_NM,
ACC.ACCT_FSC_OFC_UID
From
FP_ACCT_LINES_T LINE Left Join
KFS.X000_Account ACC ON ACC.FIN_COA_CD = LINE.FIN_COA_CD And
ACC.ACCOUNT_NBR = LINE.ACCOUNT_NBR Left Join
KFS.CA_OBJECT_CODE_T OBJ ON OBJ.UNIV_FISCAL_YR = LINE.FDOC_POST_YR And
OBJ.FIN_COA_CD = LINE.FIN_COA_CD And
OBJ.FIN_OBJECT_CD = LINE.FIN_OBJECT_CD
Order By
LINE.FDOC_NBR,
LINE.FDOC_LINE_NBR,
LINE.FDOC_LINE_AMT
"""
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 UNIQUE ACCOUNT LINE
print("Build unique account lines...")
sr_file = "X000_Account_line_unique"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
FIND.FDOC_NBR,
FIND.FDOC_POST_YR,
FIND.COST_STRING,
Max(FIND.AMOUNT) As AMOUNT,
FIND.FDOC_LINE_DESC,
Count(FIND.VATABLE) As COUNT_LINES
From
X000_Account_line FIND
Group By
FIND.FDOC_NBR
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
PAYMENT NOTES
*****************************************************************************"""
print("PAYMENT NOTES")
funcfile.writelog("PAYMENT NOTES")
# BUILD PAYMENT NOTE
print("Build payment note...")
sr_file = "X000_Note"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAY.EDOC,
HDR.FDOC_DESC As NOTE_DESC,
NTE.TXT As NOTE_TXT
From
X000_Payments PAY Left Join
KFS.KRNS_DOC_HDR_T HDR On HDR.DOC_HDR_ID = PAY.EDOC Inner Join
KFS.KRNS_NTE_T NTE On NTE.RMT_OBJ_ID = HDR.OBJ_ID Left Join
KFS.KRNS_ATT_T ATT On ATT.NTE_ID = NTE.NTE_ID
Where
ATT.FILE_NM 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 PAYMENT UNIQUE NOTE
print("Build payment unique note...")
sr_file = "X000_Note_unique"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
NTE.EDOC,
Count(NTE.EDOC) As NOTE_COUNT,
NTE.NOTE_DESC,
NTE.NOTE_TXT
From
X000_Note NTE
Group By
NTE.EDOC
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
PAYMENT ATTACHMENTS
*****************************************************************************"""
print("PAYMENT ATTACHMENTS")
funcfile.writelog("PAYMENT ATTACHMENTS")
# BUILD PAYMENT ATTACHMENTS
print("Build payment attachments...")
sr_file = "X000_Attachment"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAY.EDOC,
HDR.FDOC_DESC As ATTACH_DESC,
NTE.TXT As ATTACH_TXT,
ATT.FILE_NM As ATTACH_FILE
From
X000_Payments PAY Left Join
KFS.KRNS_DOC_HDR_T HDR On HDR.DOC_HDR_ID = PAY.EDOC Inner Join
KFS.KRNS_NTE_T NTE On NTE.RMT_OBJ_ID = HDR.OBJ_ID Inner Join
KFS.KRNS_ATT_T ATT On ATT.NTE_ID = NTE.NTE_ID
"""
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 PAYMENT UNIQUE ATTACHMENT
print("Build payment unique attachment...")
sr_file = "X000_Attachment_unique"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
ATT.EDOC,
Count(ATT.EDOC) As ATTACH_COUNT,
ATT.ATTACH_DESC,
ATT.ATTACH_TXT,
ATT.ATTACH_FILE
From
X000_Attachment ATT
Group By
ATT.EDOC
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
PAYMENTS DETAIL
*****************************************************************************"""
print("PAYMENTS DETAIL")
funcfile.writelog("PAYMENTS DETAIL")
# PAYMENT LIST WITH DETAIL
print("Build payment report...")
sr_file = "X001aa_Report_payments"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAY.EDOC,
PAY.EDOC As CUST_PMT_DOC_NBR,
PAY.PMT_GRP_ID,
PAY.PAYEE_ID,
PAY.PAYEE_TYPE,
TYP.PAYEE_TYP_DESC,
PAY.PAYEE_OWNR_CD_CALC,
PTC.LOOKUP_DESCRIPTION As PAYEE_OWNR_DESC,
PAY.PAYEE_NAME,
PAY.PAYEE_ID As VENDOR_ID,
VEN.VNDR_TYP_CD As VENDOR_TYPE,
CASE
WHEN PAY.PAYEE_OWNR_CD_CALC = 'EM' THEN 'EM'
WHEN PAY.PAYEE_OWNR_CD_CALC = 'ST' THEN 'ST'
ELSE VEN.VNDR_TYP_CD
END As VENDOR_TYPE_CALC,
VEN.VNDR_NM AS VENDOR_NAME,
VEN.VNDR_URL_ADDR AS VENDOR_REG_NR,
VEN.VNDR_TAX_NBR AS VENDOR_TAX_NR,
PEE.BNK_ACCT_NBR AS VENDOR_BANK_NR,
PAY.DOC_TYPE,
PAY.DOC_LABEL,
PAY.REQS_NBR,
PAY.PO_NBR,
PAY.INV_NBR,
PAY.INV_DT,
PAY.ORIG_INV_AMT,
PAY.NET_PMT_AMT,
PAY.PMT_DTL_ID,
PAY.PMT_DT,
PAY.PMT_STAT_CD,
Upper(STA.PMT_STAT_CD_DESC) AS PAYMENT_STATUS,
PAY.DISB_NBR,
PAY.DISB_TS,
INI.PRNCPL_ID AS COMPLETE_EMP_NO,
INI.NAME_ADDR AS COMPLETE_EMP_NAME,
INI.ACTN_DT AS COMPLETE_DATE,
INI.ACTN AS COMPLETE_STATUS,
Cast(INI.COM_COUNT As INT) As I_COUNT,
INI.ANNOTN AS COMPLETE_NOTE,
APP.PRNCPL_ID AS APPROVE_EMP_NO,
APP.NAME_ADDR AS APPROVE_EMP_NAME,
APP.ACTN_DT AS APPROVE_DATE,
APP.ACTN AS APPROVE_STATUS,
Cast(APP.APP_COUNT As INT) As A_COUNT,
APP.ANNOTN AS APPROVE_NOTE,
CASE
WHEN ACC.COUNT_LINES = 1 THEN ACC.COST_STRING
ELSE Cast(ACC.COUNT_LINES As TEXT)
END As ACC_COST_STRING,
ACC.FDOC_LINE_DESC As ACC_DESC,
NTE.NOTE_DESC,
NTE.NOTE_TXT,
NTE.NOTE_COUNT,
ATT.ATTACH_DESC,
ATT.ATTACH_TXT,
ATT.ATTACH_FILE,
ATT.ATTACH_COUNT
From
X000_Payments PAY Left Join
KFS.X000_Vendor VEN On VEN.VENDOR_ID = PAY.PAYEE_ID Left Join
KFS.PDP_PAYEE_ACH_ACCT_T PEE On PEE.PAYEE_ID_NBR = PAY.PAYEE_ID And
PEE.PAYEE_ID_TYP_CD = PAY.PAYEE_TYPE Left Join
KFS.PDP_PAYEE_TYP_T TYP ON TYP.PAYEE_TYP_CD = PAY.PAYEE_TYPE Left Join
KFS.X000_OWN_KFS_LOOKUPS PTC on PTC.LOOKUP_CODE = PAY.PAYEE_OWNR_CD_CALC And
PTC.LOOKUP = "PAYEE OWNER TYPE" Left Join
KFS.PDP_PMT_STAT_CD_T STA On STA.PMT_STAT_CD = PAY.PMT_STAT_CD Left Join
X000_Initiate_unique INI On INI.DOC_HDR_ID = PAY.EDOC Left Join
X000_Approve_unique APP On APP.DOC_HDR_ID = PAY.EDOC Left Join
X000_Account_line_unique ACC On ACC.FDOC_NBR = PAY.EDOC Left Join
X000_Note_unique NTE On NTE.EDOC = PAY.EDOC Left Join
X000_Attachment_unique ATT On ATT.EDOC = PAY.EDOC
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PAYMENT LIST WITH ALL INITIATORS
print("Build payments initiate...")
sr_file = "X001ab_Report_payments_initiate"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAYMENT.EDOC,
PAYMENT.CUST_PMT_DOC_NBR,
PAYMENT.PMT_GRP_ID,
PAYMENT.VENDOR_ID,
PAYMENT.PAYEE_NAME,
PAYMENT.PAYEE_TYPE,
PAYMENT.PAYEE_TYP_DESC,
PAYMENT.VENDOR_NAME,
PAYMENT.VENDOR_REG_NR,
PAYMENT.VENDOR_TAX_NR,
PAYMENT.VENDOR_BANK_NR,
PAYMENT.VENDOR_TYPE,
PAYMENT.VENDOR_TYPE_CALC,
PAYMENT.DISB_NBR,
PAYMENT.DISB_TS,
PAYMENT.PMT_DTL_ID,
PAYMENT.PMT_DT,
PAYMENT.PMT_STAT_CD,
PAYMENT.PAYMENT_STATUS,
PAYMENT.INV_NBR,
PAYMENT.REQS_NBR,
PAYMENT.PO_NBR,
PAYMENT.INV_DT,
PAYMENT.ORIG_INV_AMT,
PAYMENT.NET_PMT_AMT,
DOC.DOC_TYP_NM As DOC_TYPE,
Upper(DOC.LBL) As DOC_LABEL,
INIT.PRNCPL_ID AS INIT_EMP_NO,
INIT.NAME_ADDR AS INIT_EMP_NAME,
INIT.ACTN_DT AS INIT_DATE,
INIT.ACTN AS INIT_STATUS,
INIT.ANNOTN AS NOTE,
APPROVE.PRNCPL_ID AS APPROVE_EMP_NO,
APPROVE.NAME_ADDR AS APPROVE_EMP_NAME,
APPROVE.ACTN_DT AS APPROVE_DATE,
APPROVE.ACTN AS APPROVE_STATUS,
Cast(APPROVE.APP_COUNT As INT) As A_COUNT,
APPROVE.ANNOTN AS APPROVE_NOTE,
CASE
WHEN ACC.COUNT_LINES = 1 THEN ACC.COST_STRING
ELSE Cast(ACC.COUNT_LINES As TEXT)
END As ACC_COST_STRING,
ACC.FDOC_LINE_DESC As ACC_DESC
From
X001aa_Report_payments PAYMENT Left Join
KFS.X000_Document DOC On DOC.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Account_line_unique ACC On ACC.FDOC_NBR = PAYMENT.EDOC Left Join
X000_Initiate INIT On INIT.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Approve_unique APPROVE On APPROVE.DOC_HDR_ID = PAYMENT.EDOC
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PAYMENT LIST WITH ALL APPROVERS
print("Build payments approved...")
sr_file = "X001ac_Report_payments_approve"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAYMENT.EDOC,
PAYMENT.CUST_PMT_DOC_NBR,
PAYMENT.PMT_GRP_ID,
PAYMENT.VENDOR_ID,
PAYMENT.PAYEE_NAME,
PAYMENT.PAYEE_TYPE,
PAYMENT.PAYEE_TYP_DESC,
PAYMENT.VENDOR_NAME,
PAYMENT.VENDOR_REG_NR,
PAYMENT.VENDOR_TAX_NR,
PAYMENT.VENDOR_BANK_NR,
PAYMENT.VENDOR_TYPE,
PAYMENT.VENDOR_TYPE_CALC,
PAYMENT.DISB_NBR,
PAYMENT.DISB_TS,
PAYMENT.PMT_DTL_ID,
PAYMENT.PMT_DT,
PAYMENT.PMT_STAT_CD,
PAYMENT.PAYMENT_STATUS,
PAYMENT.INV_NBR,
PAYMENT.REQS_NBR,
PAYMENT.PO_NBR,
PAYMENT.INV_DT,
PAYMENT.ORIG_INV_AMT,
PAYMENT.NET_PMT_AMT,
DOC.DOC_TYP_NM As DOC_TYPE,
Upper(DOC.LBL) As DOC_LABEL,
INITIATE.PRNCPL_ID AS COMPLETE_EMP_NO,
INITIATE.NAME_ADDR AS COMPLETE_EMP_NAME,
INITIATE.ACTN_DT AS COMPLETE_DATE,
INITIATE.ACTN AS COMPLETE_STATUS,
Cast(INITIATE.COM_COUNT As INT) As I_COUNT,
APPROVE.PRNCPL_ID AS APPROVE_EMP_NO,
APPROVE.NAME_ADDR AS APPROVE_EMP_NAME,
APPROVE.ACTN_DT AS APPROVE_DATE,
APPROVE.ACTN AS APPROVE_STATUS,
PAYMENT.A_COUNT,
APPROVE.ANNOTN AS NOTE,
CASE
WHEN ACC.COUNT_LINES = 1 THEN ACC.COST_STRING
ELSE Cast(ACC.COUNT_LINES As TEXT)
END As ACC_COST_STRING,
ACC.FDOC_LINE_DESC As ACC_DESC
From
X001aa_Report_payments PAYMENT Left Join
KFS.X000_Document DOC On DOC.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Account_line_unique ACC On ACC.FDOC_NBR = PAYMENT.EDOC Left Join
X000_Approve APPROVE On APPROVE.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Initiate_unique INITIATE On INITIATE.DOC_HDR_ID = PAYMENT.EDOC
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PAYMENT LIST WITH ALL ACCOUNT LINES
print("Build payments account line...")
sr_file = "X001ad_Report_payments_accroute"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAYMENT.EDOC,
PAYMENT.CUST_PMT_DOC_NBR,
PAYMENT.PMT_GRP_ID,
PAYMENT.VENDOR_ID,
PAYMENT.PAYEE_NAME,
PAYMENT.PAYEE_TYPE,
PAYMENT.PAYEE_TYP_DESC,
PAYMENT.VENDOR_NAME,
PAYMENT.VENDOR_REG_NR,
PAYMENT.VENDOR_TAX_NR,
PAYMENT.VENDOR_BANK_NR,
PAYMENT.VENDOR_TYPE,
PAYMENT.VENDOR_TYPE_CALC,
PAYMENT.DISB_NBR,
PAYMENT.DISB_TS,
PAYMENT.PMT_DT,
PAYMENT.PMT_STAT_CD,
PAYMENT.PAYMENT_STATUS,
PAYMENT.INV_NBR,
PAYMENT.REQS_NBR,
PAYMENT.PO_NBR,
PAYMENT.INV_DT,
PAYMENT.ORIG_INV_AMT,
PAYMENT.NET_PMT_AMT,
DOC.DOC_TYP_NM As DOC_TYPE,
Upper(DOC.LBL) As DOC_LABEL,
INIT.PRNCPL_ID AS INIT_EMP_NO,
INIT.NAME_ADDR AS INIT_EMP_NAME,
INIT.ACTN_DT AS INIT_DATE,
INIT.ACTN AS INIT_STATUS,
Cast(INIT.COM_COUNT As INT) As I_COUNT,
INIT.ANNOTN AS COMPLETE_NOTE,
APPROVE.PRNCPL_ID AS APPROVE_EMP_NO,
APPROVE.NAME_ADDR AS APPROVE_EMP_NAME,
APPROVE.ACTN_DT AS APPROVE_DATE,
APPROVE.ACTN AS APPROVE_STATUS,
Cast(APPROVE.APP_COUNT As INT) As A_COUNT,
APPROVE.ANNOTN AS APPROVE_NOTE,
ACC.FDOC_LINE_NBR As ACC_LINE,
ACC.COST_STRING As ACC_COST_STRING,
ACC.AMOUNT As ACC_AMOUNT,
ACC.FDOC_LINE_DESC As ACC_DESC,
ACC.ORG_NM,
ACC.ACCOUNT_NM,
ACC.FIN_OBJ_CD_NM,
ACC.ACCT_TYP_NM,
ACC.ACCT_FSC_OFC_UID
From
X001aa_Report_payments PAYMENT Left Join
KFS.X000_Document DOC On DOC.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Account_line ACC On ACC.FDOC_NBR = PAYMENT.EDOC Left Join
X000_Initiate_unique INIT On INIT.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Approve_unique APPROVE On APPROVE.DOC_HDR_ID = PAYMENT.EDOC
Order By
VENDOR_NAME,
PAYEE_NAME,
PMT_DT,
EDOC,
FDOC_LINE_NBR
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
"""*****************************************************************************
PAYMENT REPORTS
*****************************************************************************"""
funcfile.writelog("PAYMENTS SUMMARY")
if l_debug:
print("PAYMENTS SUMMARY")
# VENDOR PAYMENT ANNUAL TOTALS
# NB NOTE REFERENCED IN TESTS - DO NOT CHANGE FIELD NAMES
# PER VENDOR LAST PAYMENT DATE
# TOTAL AMOUNT PAID TO EACH VENDOR
# THE NUMBER OF PAYMENTS
if l_debug:
print("Build vendor payments summary...")
sr_file = "X002aa_Report_payments_summary"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'%PERIOD_TEXT%' As YEAR,
PAY.VENDOR_ID,
PAY.PAYEE_NAME,
PAY.VENDOR_NAME,
PAY.PAYEE_TYPE,
PAY.PAYEE_TYP_DESC As PAYEE_TYPE_DESC,
PAY.PAYEE_OWNR_CD_CALC As OWNER_TYPE,
PAY.PAYEE_OWNR_DESC As OWNER_TYPE_DESC,
PAY.VENDOR_TYPE_CALC As VENDOR_TYPE,
VTY.LOOKUP_DESCRIPTION As VENDOR_TYPE_DESC,
Max(PAY.PMT_DT) As LAST_PMT_DT,
Sum(PAY.NET_PMT_AMT) As NET_PMT_AMT,
Count(PAY.VENDOR_ID) As TRAN_COUNT
From
X001aa_Report_payments PAY Left Join
KFS.X000_OWN_KFS_LOOKUPS VTY on VTY.LOOKUP_CODE = PAY.VENDOR_TYPE_CALC And
VTY.LOOKUP = "VENDOR TYPE CALC"
Group By
PAY.VENDOR_ID
"""
if s_period == "curr":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'CURRENT')
elif s_period == "prev":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'PREVIOUS')
else:
s_sql = s_sql.replace("%PERIOD_TEXT%", s_year)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PAYEE TYPE PER MONTH
if l_debug:
print("Build payee type payment summary per month...")
sr_file = "X002ab_Report_payments_typemon"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'%PERIOD_TEXT%' As YEAR,
PAYM.PAYEE_TYPE,
PAYM.PAYEE_TYP_DESC As PAYEE_DESC,
PAYM.PAYEE_OWNR_CD_CALC As OWNER_TYPE,
PAYM.PAYEE_OWNR_DESC As OWNER_DESC,
PAYM.VENDOR_TYPE_CALC As VENDOR_TYPE,
LOOK.LOOKUP_DESCRIPTION As VENDOR_DESC,
PAYM.DOC_TYPE,
PAYM.DOC_LABEL,
SubStr(PAYM.PMT_DT, 6, 2) As MONTH,
Cast(Count(PAYM.NET_PMT_AMT) As INT) As TRAN_COUNT,
Cast(Total(PAYM.NET_PMT_AMT) As REAL) As AMOUNT_SUM
From
X001aa_Report_payments PAYM Left Join
KFS.X000_Own_kfs_lookups LOOK On LOOK.LOOKUP_CODE = PAYM.VENDOR_TYPE_CALC And
LOOK.LOOKUP = "VENDOR TYPE CALC"
Group By
PAYM.PAYEE_TYPE,
PAYM.PAYEE_OWNR_CD_CALC,
PAYM.VENDOR_TYPE_CALC,
PAYM.DOC_TYPE,
SubStr(PAYM.PMT_DT, 6, 2)
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if s_period == "curr":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'CURRENT')
elif s_period == "prev":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'PREVIOUS')
else:
s_sql = s_sql.replace("%PERIOD_TEXT%", s_year)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PAYMENT TYPE ANNUAL SUMMARY TOTALS
if l_debug:
print("Payment type annual summary totals...")
sr_file = "X002ac_Report_payment_type_summary"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'%PERIOD_TEXT%' As YEAR,
REP.PAYEE_TYPE,
REP.PAYEE_TYP_DESC As PAYEE_TYPE_DESC,
REP.VENDOR_TYPE_CALC As VENDOR_TYPE,
VTY.LOOKUP_DESCRIPTION As VENDOR_TYPE_DESC,
REP.PAYEE_OWNR_CD_CALC As OWNER_TYPE,
REP.PAYEE_OWNR_DESC As OWNER_TYPE_DESC,
REP.DOC_TYPE,
REP.DOC_LABEL As DOC_TYPE_DESC,
Count(REP.EDOC) As TRAN_COUNT,
Total(REP.NET_PMT_AMT) As TRAN_TOTAL
From
X001aa_Report_payments REP Left Join
KFS.X000_OWN_KFS_LOOKUPS VTY on VTY.LOOKUP_CODE = REP.VENDOR_TYPE_CALC And
VTY.LOOKUP = "VENDOR TYPE CALC"
Group By
REP.PAYEE_TYPE,
REP.VENDOR_TYPE_CALC,
REP.PAYEE_OWNR_CD_CALC,
REP.DOC_TYPE
"""
if s_period == "curr":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'CURRENT')
elif s_period == "prev":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'PREVIOUS')
else:
s_sql = s_sql.replace("%PERIOD_TEXT%", s_year)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PREVIOUS MONTH PAYMENT DOCUMENT TYPE SUMMARY
if l_debug:
print("Monthly payment document type summary...")
sr_file = "X002ad_Report_payment_doctype_summary"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
SubStr(X001aa_Report_payments.VENDOR_ID, 1, 8) As VENDOR_ID,
X001aa_Report_payments.PAYEE_NAME As VENDOR_NAME,
X001aa_Report_payments.VENDOR_TYPE_CALC As VENDOR_TYPE,
X001aa_Report_payments.DOC_TYPE,
X001aa_Report_payments.DOC_LABEL,
Max(X001aa_Report_payments.DISB_TS) As LAST_DISB_DT,
Count(X001aa_Report_payments.EDOC) As TRAN_COUNT,
Total(X001aa_Report_payments.NET_PMT_AMT) As TRAN_TOTAL
From
X001aa_Report_payments
Where
SubStr(X001aa_Report_payments.DISB_TS,6,2) = "%PMONTH%"
Group By
SubStr(X001aa_Report_payments.VENDOR_ID, 1, 8),
X001aa_Report_payments.DOC_TYPE
Order By
TRAN_TOTAL Desc
"""
s_sql = s_sql.replace("%PMONTH%", funcdate.prev_month())
funcdate.prev_month()
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_export:
# Data export
sx_path = re_path + s_year + "/"
sx_file = "Creditor_report_002ad_doc_type_summ_"
sx_file_dated = sx_file + funcdate.prev_month()
if l_debug:
print("Export current year assignments..." + sx_path +
sx_file_dated)
# 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)
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)
""" ****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
so_conn.commit()
so_conn.close()
# Close the log writer *********************************************************
funcfile.writelog("-------------------------------")
funcfile.writelog("COMPLETED: B006_KFS_PERIOD_LIST")
return
def assign01(so_conn, s_table, s_from, s_to, s_on, s_mess):
"""
Function to build ASSIGNMENTS (X000_Assignment) for different date periods
:param so_conn: Connection string
:param s_table: Table name to create
:param s_from: Period start date
:param s_to: Period end date
:param s_on: On which date
:param s_mess: Print message
:return:
"""
# Print and connect
print(s_mess)
so_curs = so_conn.cursor()
# Build the table
s_sql = "CREATE TABLE " + s_table + " AS" + """
SELECT
ASSI.ASS_ID,
ASSI.PERSON_ID,
ASSI.ASSIGNMENT_NUMBER As ASS_NUMB,
ASSI.SERVICE_DATE_START As EMP_START,
ASSI.EFFECTIVE_START_DATE As ASS_START,
ASSI.EFFECTIVE_END_DATE As ASS_END,
ASSI.SERVICE_DATE_ACTUAL_TERMINATION As EMP_END,
ASSI.LEAVING_REASON,
ASSI.LEAVE_REASON_DESCRIP,
ASSI.LOCATION_DESCRIPTION,
ASSI.ORG_TYPE_DESC,
ASSI.OE_CODE,
ASSI.ORG_NAME,
ASSI.FACULTY,
ASSI.DIVISION,
ASSI.GRADE,
ASSI.GRADE_NAME,
ASSI.GRADE_CALC,
ASSI.POSITION_ID,
ASSI.POSITION,
ASSI.POSITION_NAME,
ASSI.JOB_NAME,
ASSI.JOB_SEGMENT_NAME,
ASSI.ACAD_SUPP,
ASSI.EMPLOYMENT_CATEGORY,
ASSI.LEAVE_CODE,
ASSI.SUPERVISOR_ID,
ASSI.ASS_WEEK_LEN,
ASSI.ASS_ATTRIBUTE2,
ASSI.PRIMARY_FLAG,
ASSI.MAILTO
FROM
X000_PER_ALL_ASSIGNMENTS ASSI
WHERE
(ASSI.EFFECTIVE_END_DATE >= Date('%FROM%') AND
ASSI.EFFECTIVE_END_DATE <= Date('%TO%')) OR
(ASSI.EFFECTIVE_START_DATE >= Date('%FROM%') AND
ASSI.EFFECTIVE_START_DATE <= Date('%TO%')) OR
(ASSI.EFFECTIVE_END_DATE >= Date('%FROM%') AND
ASSI.EFFECTIVE_START_DATE <= Date('%TO%'))
ORDER BY
ASSI.ASSIGNMENT_NUMBER,
ASSI.EFFECTIVE_START_DATE
"""
s_sql = s_sql.replace("%FROM%", s_from)
s_sql = s_sql.replace("%TO%", s_to)
so_curs.execute("DROP TABLE IF EXISTS " + s_table)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + s_table)
# Add column assignment lookup date
if "DATE_ASS_LOOKUP" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table +
" ADD COLUMN DATE_ASS_LOOKUP TEXT;")
s_sql = "UPDATE " + s_table + """
SET DATE_ASS_LOOKUP =
CASE
WHEN ASS_END > Date('%TO%') THEN Date('%TO%')
ELSE ASS_END
END
;"""
s_sql = s_sql.replace("%TO%", s_on)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: DATE_ASS_LOOKUP")
# Add column is assignment active
if "ASS_ACTIVE" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table +
" ADD COLUMN ASS_ACTIVE TEXT;")
s_sql = "UPDATE " + s_table + """
SET ASS_ACTIVE =
CASE
WHEN ORG_TYPE_DESC = 'Parent Organisation' THEN 'O'
WHEN INSTR(POSITION_NAME,'Pensioner') > 0 THEN 'P'
WHEN ASS_START <= Date('%ON%') AND ASS_END >= Date('%ON%') THEN 'Y'
ELSE 'N'
END
;"""
s_sql = s_sql.replace("%ON%", s_on)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: ASS_ACTIVE")
# Add column people lookup
if "DATE_EMP_LOOKUP" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table +
" ADD COLUMN DATE_EMP_LOOKUP TEXT;")
s_sql = "UPDATE " + s_table + """
SET DATE_EMP_LOOKUP =
CASE
WHEN EMP_START = EMP_END AND LEAVING_REASON = '' THEN Date('%TO%')
WHEN EMP_END > Date('%TO%') THEN Date('%TO%')
ELSE EMP_END
END
;"""
s_sql = s_sql.replace("%TO%", s_on)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: DATE_EMP_LOOKUP")
# Add column is people active
if "EMP_ACTIVE" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table +
" ADD COLUMN EMP_ACTIVE TEXT;")
s_sql = "UPDATE " + s_table + """
SET EMP_ACTIVE =
CASE
WHEN ASS_ACTIVE = 'O' AND EMP_START <= Date('%ON%') AND DATE_EMP_LOOKUP >= Date('%ON%')
THEN 'O'
WHEN ASS_ACTIVE = 'P' AND EMP_START <= Date('%ON%') AND DATE_EMP_LOOKUP >= Date('%ON%')
THEN 'P'
WHEN ASS_ACTIVE = 'Y' AND EMP_START <= Date('%ON%') AND DATE_EMP_LOOKUP >= Date('%ON%')
THEN 'Y'
ELSE 'N'
END
;"""
s_sql = s_sql.replace("%ON%", s_on)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: EMP_ACTIVE")
return
def people01(so_conn, s_table, s_source, s_peri, s_mess, s_acti):
"""
Function to build PEOPLE table from different assignments
:param so_conn: Connection string
:param s_table: Table name to create
:param s_source: Table source
:param s_peri: For which period
:param s_mess: Print and log message
:param s_acti: Should list include only active people = Y (or active assignments = N)
:return: int: Number of people
"""
# Print and connect
print(s_mess)
so_curs = so_conn.cursor()
# Use assignment or people date
if s_acti == "Y":
s_wher = "ASSI.EMP_ACTIVE = 'Y'"
else:
s_wher = "ASSI.ASS_ACTIVE = 'Y'"
# Create the people table
s_sql = "CREATE TABLE " + s_table + " As " + """
Select
ASSI.EMPLOYEE_NUMBER,
ASSI.ASS_ID,
ASSI.PERSON_ID,
ASSI.ASS_NUMB,
X000_PER_ALL_PEOPLE.PARTY_ID,
Upper(ASSI.FULL_NAME) As FULL_NAME,
'' As NAME_LIST,
'' As NAME_ADDR,
Upper(ASSI.KNOWN_NAME) As KNOWN_NAME,
CASE
WHEN ORG_NAME IS NULL THEN OE_CODE||': '||POSITION_NAME
ELSE ORG_NAME||': '||POSITION_NAME
END AS POSITION_FULL,
ASSI.DATE_OF_BIRTH,
Upper(X000_PER_ALL_PEOPLE.NATIONALITY) As NATIONALITY,
Upper(X000_PER_ALL_PEOPLE.NATIONALITY_NAME) As NATIONALITY_NAME,
X000_PER_ALL_PEOPLE.NATIONAL_IDENTIFIER As IDNO,
Upper(X000_PER_ALL_PEOPLE.PER_INFORMATION2) As PASSPORT,
Upper(X000_PER_ALL_PEOPLE.PER_INFORMATION3) As PERMIT,
X000_PER_ALL_PEOPLE.PER_INFORMATION8 As PERMIT_EXPIRE,
X000_PER_ALL_PEOPLE.TAX_NUMBER,
Case
When X000_PER_ALL_PEOPLE.SEX = 'F' Then 'FEMALE'
When X000_PER_ALL_PEOPLE.SEX = 'M' Then 'MALE'
Else 'OTHER'
End As SEX,
X000_PER_ALL_PEOPLE.MARITAL_STATUS,
X000_PER_ALL_PEOPLE.REGISTERED_DISABLED_FLAG As DISABLED,
X000_PER_ALL_PEOPLE.RACE_CODE,
Upper(X000_PER_ALL_PEOPLE.RACE_DESC) As RACE_DESC,
X000_PER_ALL_PEOPLE.LANG_CODE,
Upper(X000_PER_ALL_PEOPLE.LANG_DESC) As LANG_DESC,
X000_PER_ALL_PEOPLE.INT_MAIL,
Lower(X000_PER_ALL_PEOPLE.EMAIL_ADDRESS) As EMAIL_ADDRESS,
X000_PER_ALL_PEOPLE.CURRENT_EMPLOYEE_FLAG As CURR_EMPL_FLAG,
X000_PER_ALL_PEOPLE.USER_PERSON_TYPE,
ASSI.ASS_START,
ASSI.ASS_END,
ASSI.EMP_START,
ASSI.EMP_END,
ASSI.LEAVING_REASON,
Upper(ASSI.LEAVE_REASON_DESCRIP) As LEAVE_REASON_DESCRIP,
Upper(ASSI.LOCATION_DESCRIPTION) As LOCATION_DESCRIPTION,
Upper(ASSI.ORG_TYPE_DESC) As ORG_TYPE_DESC,
Upper(ASSI.OE_CODE) As OE_CODE,
Upper(ASSI.ORG_NAME) As ORG_NAME,
ASSI.PRIMARY_FLAG,
Case
When Upper(ASSI.ACAD_SUPP) = 'ACADEMIC' Then Upper(ASSI.ACAD_SUPP)
When Upper(ASSI.ACAD_SUPP) = 'SUPPORT' Then Upper(ASSI.ACAD_SUPP)
Else 'OTHER'
End as ACAD_SUPP,
Upper(ASSI.FACULTY) As FACULTY,
Upper(ASSI.DIVISION) As DIVISION,
Case
When EMPLOYMENT_CATEGORY = 'P' Then 'PERMANENT'
When EMPLOYMENT_CATEGORY = 'T' Then 'TEMPORARY'
Else 'OTHER'
End As EMPLOYMENT_CATEGORY,
ASSI.ASS_WEEK_LEN,
ASSI.LEAVE_CODE,
ASSI.GRADE,
Upper(ASSI.GRADE_NAME) As GRADE_NAME,
ASSI.GRADE_CALC,
ASSI.POSITION,
Upper(ASSI.POSITION_NAME) As POSITION_NAME,
Upper(ASSI.JOB_NAME) As JOB_NAME,
Upper(ASSI.JOB_SEGMENT_NAME) As JOB_SEGMENT_NAME,
ASSI.SUPERVISOR,
X000_PER_ALL_PEOPLE.TITLE_FULL,
X000_PER_ALL_PEOPLE.FIRST_NAME,
X000_PER_ALL_PEOPLE.MIDDLE_NAMES,
X000_PER_ALL_PEOPLE.LAST_NAME,
X000_PHONE_WORK_%PERIOD%_LIST.PHONE_WORK,
X000_PHONE_MOBI_%PERIOD%_LIST.PHONE_MOBI,
X000_PHONE_HOME_%PERIOD%_LIST.PHONE_HOME,
X000_ADDRESS_SARS.ADDRESS_SARS,
X000_ADDRESS_POST.ADDRESS_POST,
X000_ADDRESS_HOME.ADDRESS_HOME,
X000_ADDRESS_OTHE.ADDRESS_OTHE,
ASSI.COUNT_POS,
ASSI.COUNT_ASS,
ASSI.COUNT_PEO,
ASSI.DATE_ASS_LOOKUP,
ASSI.ASS_ACTIVE,
ASSI.DATE_EMP_LOOKUP,
ASSI.EMP_ACTIVE,
ASSI.MAILTO,
PER_PAY_PROPOSALS.PROPOSED_SALARY_N,
Upper(X000_PER_PEOPLE_TYPES.USER_PERSON_TYPE) As PERSON_TYPE,
Upper(ASSI.ACC_TYPE) As ACC_TYPE,
Upper(ASSI.ACC_BRANCH) As ACC_BRANCH,
ASSI.ACC_NUMBER,
Upper(ASSI.ACC_RELATION) As ACC_RELATION,
ASSI.ACC_SARS,
ASSI.SEC_FULLPART_FLAG
FROM
%SOURCET% ASSI
LEFT JOIN X000_PER_ALL_PEOPLE ON X000_PER_ALL_PEOPLE.PERSON_ID = ASSI.PERSON_ID AND
X000_PER_ALL_PEOPLE.EFFECTIVE_START_DATE <= ASSI.DATE_EMP_LOOKUP AND
X000_PER_ALL_PEOPLE.EFFECTIVE_END_DATE >= ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_PHONE_WORK_%PERIOD%_LIST ON X000_PHONE_WORK_%PERIOD%_LIST.PERSON_ID = ASSI.PERSON_ID
LEFT JOIN X000_PHONE_MOBI_%PERIOD%_LIST ON X000_PHONE_MOBI_%PERIOD%_LIST.PERSON_ID = ASSI.PERSON_ID
LEFT JOIN X000_PHONE_HOME_%PERIOD%_LIST ON X000_PHONE_HOME_%PERIOD%_LIST.PERSON_ID = ASSI.PERSON_ID
LEFT JOIN X000_ADDRESS_SARS ON X000_ADDRESS_SARS.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_SARS.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND X000_ADDRESS_SARS.DATE_TO >=
ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_ADDRESS_POST ON X000_ADDRESS_POST.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_POST.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND X000_ADDRESS_POST.DATE_TO >=
ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_ADDRESS_HOME ON X000_ADDRESS_HOME.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_HOME.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND X000_ADDRESS_HOME.DATE_TO >=
ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_ADDRESS_OTHE ON X000_ADDRESS_OTHE.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_OTHE.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND
X000_ADDRESS_OTHE.DATE_TO >= ASSI.DATE_EMP_LOOKUP
LEFT JOIN PER_PAY_PROPOSALS ON PER_PAY_PROPOSALS.ASSIGNMENT_ID = ASSI.ASS_ID AND
PER_PAY_PROPOSALS.CHANGE_DATE <= ASSI.DATE_EMP_LOOKUP AND
PER_PAY_PROPOSALS.DATE_TO >= ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_PER_PEOPLE_TYPES ON X000_PER_PEOPLE_TYPES.PERSON_ID = ASSI.PERSON_ID AND
X000_PER_PEOPLE_TYPES.EFFECTIVE_START_DATE <= ASSI.DATE_EMP_LOOKUP AND
X000_PER_PEOPLE_TYPES.EFFECTIVE_END_DATE >= ASSI.DATE_EMP_LOOKUP
WHERE
""" + s_wher + """
GROUP BY
ASSI.EMPLOYEE_NUMBER
"""
so_curs.execute("DROP TABLE IF EXISTS " + s_table)
s_sql = s_sql.replace("%SOURCET%", s_source)
s_sql = s_sql.replace("%PERIOD%", s_peri)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + s_table)
# Add column initials
if "INITIALS" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table +
" ADD COLUMN INITIALS TEXT;")
s_sql = "UPDATE " + s_table + """
SET INITIALS =
CASE
WHEN INSTR(MIDDLE_NAMES,' ') > 1
THEN SUBSTR(FIRST_NAME,1,1) ||
SUBSTR(MIDDLE_NAMES,1,1) ||
TRIM(SUBSTR(MIDDLE_NAMES,INSTR(MIDDLE_NAMES,' '),2))
WHEN LENGTH(MIDDLE_NAMES) > 0 THEN
SUBSTR(FIRST_NAME,1,1) || SUBSTR(MIDDLE_NAMES,1,1)
ELSE SUBSTR(FIRST_NAME,1,1)
END
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: INITIALS")
so_curs.execute(
"UPDATE " + s_table +
" SET NAME_LIST = LAST_NAME||' '||TITLE_FULL||' '||INITIALS;")
so_conn.commit()
so_curs.execute(
"UPDATE " + s_table +
" SET NAME_ADDR = TITLE_FULL||' '||INITIALS||' '||LAST_NAME;")
so_conn.commit()
# Add column age
if "AGE" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table + " ADD COLUMN AGE INT;")
s_sql = "UPDATE " + s_table + """
SET AGE = strftime('%Y', 'now') - strftime('%Y', DATE_OF_BIRTH)
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: AGE")
# Add column month
if "MONTH" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table + " ADD COLUMN MONTH INT;")
s_sql = "UPDATE " + s_table + """
SET MONTH = cast(strftime('%m', DATE_OF_BIRTH) As int)
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: MONTH")
# Add column day
if "DAY" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table + " ADD COLUMN DAY INT;")
s_sql = "UPDATE " + s_table + """
SET DAY = cast(strftime('%d', DATE_OF_BIRTH) As int)
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: DAY")
return funcsys.tablerowcount(so_curs, s_table)
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 studentlist(so_conn, re_path, s_period='curr', l_export=False):
"""
Script to build STUDENT list
:param so_conn: Database connection
:param re_path: Results path
:param s_period: Calculation period
:param s_year: Financial year
:param l_export: Export results
:return: Nothing
"""
# DECLARE VARIABLES
if s_period == 'prev':
s_year = funcdate.prev_year()
else:
s_year = funcdate.cur_year()
so_curs = so_conn.cursor()
"""*************************************************************************
BUILD STUDENTS
*************************************************************************"""
print("BUILD " + s_year + " STUDENTS")
funcfile.writelog("BUILD " + s_year + " YEAR STUDENTS")
# BUILD STUDENT LIST
print("Build student list...")
sr_file = "X001_Student"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
STUD.KSTUDBUSENTID,
STUD.DATEQUALLEVELSTARTED,
STUD.DATEENROL,
STUD.STARTDATE,
STUD.ENDDATE,
STUD.MARKSFINALISEDDATE,
RESU.RESULTISSUEDATE,
RESU.RESULTPASSDATE,
QUAL.QUALIFICATION,
QUAL.QUALIFICATION_NAME,
QUAL.QUALIFICATION_TYPE As QUAL_TYPE,
Upper(ACTI.LONG) AS ACTIVE_IND,
Upper(ENTR.LONG) AS ENTRY_LEVEL,
Upper(BLAC.LONG) AS BLACKLIST,
QUAL.ENROL_CATEGORY As ENROL_CAT,
QUAL.PRESENT_CATEGORY As PRESENT_CAT,
QUAL.FINAL_STATUS As STATUS_FINAL,
QUAL.LEVY_CATEGORY,
QUAL.CERT_TYPE,
QUAL.LEVY_TYPE,
QUAL.FOS_SELECTION,
QUAL.FOS_SELECTION As LONG,
QUAL.FBUSINESSENTITYID,
QUAL.SITEID,
QUAL.CAMPUS,
QUAL.ORGUNIT_MANAGER,
QUAL.ORGUNIT_NAME,
QUAL.ORGUNIT_TYPE,
RESU.KSTUDQUALFOSRESULTID,
RESU.DISCONTINUEDATE,
RESU.FDISCONTINUECODEID,
RESU.RESULT,
RESU.DISCONTINUE_REAS,
RESU.POSTPONE_REAS,
RESU.FPOSTPONEMENTCODEID,
RESU.FGRADUATIONCEREMONYID,
GRAD.CEREMONY,
GRAD.CEREMONYDATETIME,
QUAL.QUALIFICATIONCODE,
QUAL.QUALIFICATIONFIELDOFSTUDY,
QUAL.QUALIFICATIONLEVEL,
STUD.ENROLACADEMICYEAR,
STUD.ENROLHISTORYYEAR,
QUAL.MIN,
QUAL.MIN_UNIT,
QUAL.MAX,
QUAL.MAX_UNIT,
STUD.ISHEMISSUBSIDY,
STUD.ISMAINQUALLEVEL,
STUD.ISCONDITIONALREG,
STUD.ISCUMLAUDE,
STUD.ISPOSSIBLEGRADUATE,
STUD.FACCEPTANCETESTCODEID,
QUAL.ISVERIFICATIONREQUIRED,
QUAL.EXAMSUBMINIMUM,
QUAL.ISVATAPPLICABLE,
QUAL.ISPRESENTEDBEFOREAPPROVAL,
QUAL.ISDIRECTED,
QUAL.SITEID As FSITEORGUNITNUMBER,
STUD.KENROLSTUDID,
QUAL.FQUALLEVELAPID,
QUAL.KENROLMENTPRESENTATIONID,
STUD.FENROLMENTPRESENTATIONID,
QUAL.FOS_KACADEMICPROGRAMID,
STUD.FPROGRAMAPID,
QUAL.FENROLMENTCATEGORYCODEID,
QUAL.FPRESENTATIONCATEGORYCODEID,
Case
When STUD.ENROLHISTORYYEAR > 6 Then 6
Else STUD.ENROLHISTORYYEAR
End As FEEHISTORYYEAR,
strftime("%Y", STUD.STARTDATE) - strftime("%Y", STUD.DATEQUALLEVELSTARTED) + 1 As CALCHISTORYYEAR
From
QUALLEVELENROLSTUD STUD Left Join
VSS.X000_Codedescription BLAC ON BLAC.KCODEDESCID = STUD.FBLACKLISTCODEID Left Join
VSS.X000_Codedescription ACTI ON ACTI.KCODEDESCID = STUD.FSTUDACTIVECODEID Left Join
VSS.X000_Codedescription ENTR ON ENTR.KCODEDESCID = STUD.FENTRYLEVELCODEID Left Join
VSS.X000_Qualifications QUAL On QUAL.KENROLMENTPRESENTATIONID = STUD.FENROLMENTPRESENTATIONID Left Join
VSS.X000_Student_qualfos_result RESU ON RESU.KBUSINESSENTITYID = STUD.KSTUDBUSENTID And
RESU.KACADEMICPROGRAMID = QUAL.FOS_KACADEMICPROGRAMID And
Strftime('%Y', RESU.DISCONTINUEDATE) = '%YEAR%' Left Join
Vss.X000_Gradceremony GRAD On GRAD.KGRADUATIONCEREMONYID = RESU.FGRADUATIONCEREMONYID
Order By
STUD.KSTUDBUSENTID
;"""
# s_sql = s_sql.replace("%PERIOD%", s_period)
s_sql = s_sql.replace("%YEAR%", s_year)
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 funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b>" + str(i) + "</b> Students")
# Export the data
if l_export:
print("Export students all...")
sr_filet = sr_file
sx_path = re_path + s_year + "/"
sx_file = "Student_001_all_"
s_head = funccsv.get_colnames_sqlite(so_conn, sr_filet)
funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_file, s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
"""*************************************************************************
BUILD STUDENT MODULES
*************************************************************************"""
print("BUILD " + s_year + " STUDENT MODULES")
funcfile.writelog("BUILD " + s_year + " YEAR STUDENT MODULES")
# BUILD STUDENT LIST
print("Build student list...")
sr_file = "X001_Student_module"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
MENR.KENROLSTUDID,
MENR.KSTUDBUSENTID,
MENR.ACADEMICYEAR,
MENR.DATEENROL,
MENR.STARTDATE,
MENR.ENDDATE,
STUD.FQUALLEVELAPID,
STUD.QUALIFICATION,
MODU.FMODULEAPID,
MODU.MODULE,
MODU.MODULE_NAME,
MODU.FENROLMENTCATEGORYCODEID,
MODU.ENROL_CATEGORY,
MODU.FPRESENTATIONCATEGORYCODEID,
MODU.PRESENT_CATEGORY,
MODU.FCOURSEGROUPCODEID,
MODU.COURSEGROUP,
MENR.FMODULETYPECODEID,
Upper(TYPE.LONG) As MODULE_TYPE,
MENR.DATEDISCONTINUED,
MENR.FCOMPLETEREASONCODEID,
Upper(REAS.LONG) As COMPLETE_REASON,
Trim(MODR.PART_RESU) As PART_RESU,
MODR.DATEACHIEVED As DATE_RESU,
MODU.FBUSINESSENTITYID,
MODU.SITEID,
MODU.CAMPUS,
MODU.ORGUNIT_TYPE,
MODU.ORGUNIT_NAME,
MODU.ORGUNIT_MANAGER,
MENR.ISCONDITIONALREG,
MENR.ISNEWENROLMENT,
MENR.ISPROCESSEDONLINE,
MENR.ISREPEATINGMODULE,
MENR.ISEXEMPTION,
MODU.ISEXAMMODULE,
MODU.ISRESEARCHMODULE,
MENR.ISDISCOUNTED,
MODU.EXAMSUBMINIMUM,
MENR.FSTUDYCENTREMODAPID,
MENR.FENROLMENTPRESENTATIONID,
MENR.FEXAMCENTREMODAPID,
MENR.FPRESENTATIONLANGUAGEID,
MENR.FMODPERIODENROLPRESCATID,
MENR.FACKTYPECODEID,
MENR.FACKSTUDBUSENTID,
MENR.FACKENROLSTUDID,
MENR.FACKMODENROLSTUDID,
MENR.FACKMODSTUDBUSENTID,
MENR.AUDITDATETIME As MENROL_AUDITDATETIME,
MENR.FAUDITSYSTEMFUNCTIONID As MENROL_SYSID,
MENR.FAUDITUSERCODE As MENROL_USERCODE,
MENR.REGALLOWED,
MODU.KENROLMENTPRESENTATIONID,
MODU.COURSECODE,
MODU.COURSELEVEL,
MODU.COURSEMODULE,
MODU.COURSESEMESTER
From
MODULEENROLSTUD MENR Inner Join
VSS.X000_Modules MODU On MODU.KENROLMENTPRESENTATIONID = MENR.FENROLMENTPRESENTATIONID Left Join
VSS.X000_Codedescription TYPE On TYPE.KCODEDESCID = MENR.FMODULETYPECODEID Left Join
VSS.X000_Codedescription REAS On REAS.KCODEDESCID = MENR.FCOMPLETEREASONCODEID Left Join
X001_Student STUD On STUD.KSTUDBUSENTID = MENR.KSTUDBUSENTID And
STUD.KENROLSTUDID = MENR.FQUALLEVELENROLSTUDID Left Join
X000_Student_module_result_participate MODR On MODR.KSTUDBUSENTID = MENR.KSTUDBUSENTID And
MODR.KENROLSTUDID = MENR.KENROLSTUDID
;"""
# s_sql = s_sql.replace("%PERIOD%", s_period)
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 funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b>" + str(i) + "</b> Modules")
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
def studdeb_deferments(s_period="curr"):
"""
Script to build standard STUDENT DEBTOR DEFERMENT lists
:type s_period: str: The financial period (curr, prev or year)
:return: Nothing
"""
# TODO Convert student deferments to vss_deferments_yyyy.sqlite from vss_deferment.sqlite
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# DECLARE VARIABLES
l_debug: bool = True
so_path = "W:/Vss_deferment/" # Source database path
if s_period == "curr":
s_year = funcdate.cur_year()
#so_file = "Vss_deferment_curr.sqlite" # Source database
so_file = "Vss_deferment.sqlite" # Source database
elif s_period == "prev":
s_year = funcdate.prev_year()
#so_file = "Vss_deferment_prev.sqlite" # Source database
so_file = "Vss_deferment.sqlite" # Source database
else:
s_year = s_period
# so_file = "Vss_deferment_" + s_year + ".sqlite" # Source database
so_file = "Vss_deferment.sqlite" # Source database
ed_path = "S:/_external_data/" # External data path
l_export: bool = True
l_mail = False
# SCRIPT LOG FILE
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: REPORT_VSS_DEFERMENTS")
funcfile.writelog("-----------------------------")
print("---------------------")
print("REPORT_VSS_DEFERMENTS")
print("---------------------")
# MESSAGE
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator",
"<b>C301 Student " + s_year + " deferments</b>")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
print("OPEN THE DATABASES")
funcfile.writelog("OPEN THE DATABASES")
# OPEN SQLITE SOURCE table
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 VSS DATABASE
print("Attach vss database...")
so_curs.execute("ATTACH DATABASE 'W:/Vss/Vss.sqlite' AS 'VSS'")
funcfile.writelog("%t ATTACH DATABASE: Vss.sqlite")
so_curs.execute("ATTACH DATABASE 'W:/Vss/Vss_curr.sqlite' AS 'VSSCURR'")
funcfile.writelog("%t ATTACH DATABASE: Vss_curr.sqlite")
so_curs.execute("ATTACH DATABASE 'W:/Vss/Vss_prev.sqlite' AS 'VSSPREV'")
funcfile.writelog("%t ATTACH DATABASE: Vss_prev.sqlite")
""" ****************************************************************************
TEMPORARY AREA
*****************************************************************************"""
print("TEMPORARY AREA")
funcfile.writelog("TEMPORARY AREA")
so_curs.execute("DROP TABLE IF EXISTS X000_Deferments_curr")
so_curs.execute("DROP TABLE IF EXISTS X000_Deferments_prev")
so_curs.execute("DROP TABLE IF EXISTS X000_Students_curr")
so_curs.execute("DROP TABLE IF EXISTS X000_Tran_balopen_curr")
so_curs.execute("DROP TABLE IF EXISTS X000_Tran_feereg_curr")
so_curs.execute("DROP TABLE IF EXISTS X001_DEFERMENTS_CURR")
so_curs.execute("DROP TABLE IF EXISTS X001_DEFERMENTS_PREV")
""" ****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
""" ****************************************************************************
BUILD DEFERMENTS
*****************************************************************************"""
print("BUILD DEFERMENTS")
funcfile.writelog("BUILD DEFERMENTS")
# ADD DESCRIPTIONS TO DEFERMENTS
print("Build deferments...")
sr_file = "X000_Deferments"
s_sql = "CREATE TABLE " + sr_file + " AS" + """
SELECT
DEFER.KACCDEFERMENTID,
DEFER.FACCID,
STUDACC.FBUSENTID,
DEFER.DATEARRANGED,
USER.FUSERBUSINESSENTITYID,
DEFER.STARTDATE,
DEFER.ENDDATE,
DEFER.TOTALAMOUNT,
SUBACC.CODESHORTDESCRIPTION AS SUBACCOUNTTYPE,
TYPE.CODESHORTDESCRIPTION AS DEFERMENTTYPE,
REAS.CODESHORTDESCRIPTION AS DEFERMENTREASON,
DEFER.NOTE,
DEFER.FAUDITUSERCODE,
DEFER.AUDITDATETIME,
DEFER.FAUDITSYSTEMFUNCTIONID
FROM
ACCDEFERMENT DEFER
LEFT JOIN VSS.CODEDESCRIPTION SUBACC ON SUBACC.KCODEDESCID = DEFER.FSUBACCTYPECODEID
LEFT JOIN VSS.CODEDESCRIPTION TYPE ON TYPE.KCODEDESCID = DEFER.FDEFERMENTTYPECODEID
LEFT JOIN VSS.CODEDESCRIPTION REAS ON REAS.KCODEDESCID = DEFER.FDEFERMENTREASONCODEID
LEFT JOIN VSS.STUDACC STUDACC ON STUDACC.KACCID = DEFER.FACCID
LEFT JOIN VSS.SYSTEMUSER USER ON USER.KUSERCODE = DEFER.FAUDITUSERCODE
WHERE
SUBACC.KSYSTEMLANGUAGECODEID = 3 AND
TYPE.KSYSTEMLANGUAGECODEID = 3 AND
REAS.KSYSTEMLANGUAGECODEID = 3
ORDER BY
STUDACC.FBUSENTID,
DEFER.AUDITDATETIME
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD THE PERIOD DEFERMENT LIST
print("Select the deferment period...")
sr_file = "X000_Deferments_select"
s_sql = "CREATE TABLE " + sr_file + " AS" + """
SELECT
DEFER.KACCDEFERMENTID,
DEFER.FBUSENTID AS 'STUDENT',
SITE.FDEBTCOLLECTIONSITE AS 'CAMPUS',
DEFER.DATEARRANGED,
DEFER.FUSERBUSINESSENTITYID AS 'EMPLOYEE',
DEFER.STARTDATE AS 'DATESTART',
DEFER.ENDDATE AS 'DATEEND',
DEFER.TOTALAMOUNT,
DEFER.SUBACCOUNTTYPE,
DEFER.DEFERMENTTYPE,
DEFER.DEFERMENTREASON,
DEFER.NOTE,
DEFER.FAUDITUSERCODE,
DEFER.AUDITDATETIME,
DEFER.FAUDITSYSTEMFUNCTIONID,
SITE.FADMISSIONSITE,
SITE.FMAINQUALSITE
FROM
X000_DEFERMENTS DEFER
LEFT JOIN STUDENTSITE SITE ON SITE.KSTUDENTBUSENTID = DEFER.FBUSENTID
WHERE
SITE.KSTARTDATETIME <= DEFER.DATEARRANGED AND
SITE.ENDDATETIME > DEFER.DATEARRANGED AND
DEFER.STARTDATE >= Date('%YEARB%') AND
DEFER.ENDDATE <= Date('%YEARE%')
"""
if s_period == "curr":
s_sql = s_sql.replace("%YEARB%", funcdate.cur_yearbegin())
s_sql = s_sql.replace("%YEARE%", funcdate.cur_yearend())
elif s_period == "prev":
s_sql = s_sql.replace("%YEARB%", funcdate.prev_yearbegin())
s_sql = s_sql.replace("%YEARE%", funcdate.prev_yearend())
else:
s_sql = s_sql.replace("%YEARB%", s_year + "-01-01")
s_sql = s_sql.replace("%YEARE%", s_year + "-12-31")
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# MESSAGE
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b>" + str(i) + "</b> Deferments")
# Export the declaration data
if l_export:
if s_period == "curr":
sx_path = "R:/Debtorstud/" + funcdate.cur_year() + "/"
elif s_period == "prev":
sx_path = "R:/Debtorstud/" + funcdate.prev_year() + "/"
else:
sx_path = "R:/Debtorstud/" + s_year + "/"
sx_file = "Deferment_000_list_"
print("Export data..." + sx_path + sx_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)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
""" ****************************************************************************
OBTAIN STUDENTS
*****************************************************************************"""
print("OBTAIN STUDENTS")
funcfile.writelog("OBTAIN STUDENTS")
# OBTAIN THE LIST STUDENTS
print("Obtain the registered students...")
sr_file = "X000_Students"
s_sql = "CREATE TABLE " + sr_file + " AS" + """
SELECT
STUD.*,
CASE
WHEN DATEENROL < STARTDATE THEN STARTDATE
ELSE DATEENROL
END AS DATEENROL_CALC
FROM
%VSS%.X001_Student STUD
WHERE
UPPER(STUD.QUAL_TYPE) Not Like '%SHORT COURSE%' AND
STUD.ISMAINQUALLEVEL = 1 AND
UPPER(STUD.ACTIVE_IND) = 'ACTIVE'
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if s_period == "prev":
s_sql = s_sql.replace("%VSS%", "VSSPREV")
else:
s_sql = s_sql.replace("%VSS%", "VSSCURR")
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
OBTAIN STUDENT TRANSACTIONS AND CALCULATE BALANCES
*****************************************************************************"""
print("OBTAIN STUDENT TRANSACTIONS")
funcfile.writelog("OBTAIN STUDENT TRANSACTIONS")
# OBTAIN STUDENT ACCOUNT TRANSACTIONS
print("Import student transactions...")
sr_file = "X000_Transaction"
s_sql = "CREATE TABLE " + sr_file + " AS" + """
Select
TRAN.FBUSENTID As STUDENT,
CASE
WHEN TRAN.FDEBTCOLLECTIONSITE = '-9' THEN 'MAFIKENG'
WHEN TRAN.FDEBTCOLLECTIONSITE = '-2' THEN 'VAAL TRIANGLE'
ELSE 'POTCHEFSTROOM'
END AS CAMPUS,
TRAN.TRANSDATE,
TRAN.TRANSDATETIME,
CASE
WHEN SUBSTR(TRAN.TRANSDATE,6,5)='01-01' AND INSTR('001z031z061',TRAN.TRANSCODE)>0 THEN '00'
WHEN strftime('%Y',TRAN.TRANSDATE)>strftime('%Y',TRAN.POSTDATEDTRANSDATE) THEN strftime('%m',TRAN.TRANSDATE)
ELSE strftime('%m',TRAN.TRANSDATE)
END AS MONTH,
TRAN.TRANSCODE,
TRAN.AMOUNT,
CASE
WHEN TRAN.AMOUNT > 0 THEN TRAN.AMOUNT
ELSE 0.00
END AS AMOUNT_DT,
CASE
WHEN TRAN.AMOUNT < 0 THEN TRAN.AMOUNT
ELSE 0.00
END AS AMOUNT_CR,
TRAN.DESCRIPTION_E As TRANSDESC
FROM
%VSS%.X010_Studytrans TRAN
WHERE
TRAN.TRANSCODE <> ''
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if s_period == "prev":
s_sql = s_sql.replace("%VSS%", "VSSPREV")
else:
s_sql = s_sql.replace("%VSS%", "VSSCURR")
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# CALCULATE THE STUDENT ACCOUNT OPENING BALANCE
print("Calculate the account opening balance...")
sr_file = "X001aa_Trans_balopen"
s_sql = "CREATE VIEW " + sr_file + " AS" + """
SELECT
TRAN.STUDENT,
CAST(ROUND(TOTAL(TRAN.AMOUNT),2) AS REAL) AS BAL_OPEN
FROM
X000_Transaction TRAN
WHERE
TRAN.MONTH = '00'
GROUP BY
TRAN.STUDENT
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# CALCULATE THE REGISTRATION FEES LEVIED
print("Calculate the registration fee transactions...")
sr_file = "X001ab_Trans_feereg"
s_sql = "CREATE VIEW " + sr_file + " AS" + """
SELECT
TRAN.STUDENT,
CAST(ROUND(TOTAL(TRAN.AMOUNT),2) AS REAL) AS FEE_REG
FROM
X000_Transaction TRAN
WHERE
TRAN.TRANSCODE = "002" Or
TRAN.TRANSCODE = "095"
GROUP BY
TRAN.STUDENT
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# ADD THE REGISTRATION DATE TO THE LIST OF TRANSACTIONS
print("Add the registration date to the list of transactions...")
sr_file = "X001ac_Trans_addreg"
s_sql = "CREATE VIEW " + sr_file + " AS" + """
SELECT
TRAN.*,
STUD.DATEENROL_CALC
FROM
X000_Transaction TRAN
INNER JOIN X000_Students STUD ON STUD.KSTUDBUSENTID = TRAN.STUDENT
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# CALCULATE THE STUDENT ACCOUNT BALANCE ON REGISTRATION DATE
print("Calculate the account balance on registration date...")
sr_file = "X001ad_Trans_balreg"
s_sql = "CREATE VIEW " + sr_file + " AS" + """
SELECT
TRAN.STUDENT,
CAST(ROUND(TOTAL(TRAN.AMOUNT),2) AS REAL) AS BAL_REG
FROM
X001ac_Trans_addreg TRAN
WHERE
TRAN.TRANSDATE <= TRAN.DATEENROL_CALC
GROUP BY
TRAN.STUDENT
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# CALCULATE THE STUDENT ACCOUNT CREDIT TRANSACTIONS BEFORE REGISTRATION
print("Calculate the credits after registration date...")
sr_file = "X001ae_Trans_crebefreg"
s_sql = "CREATE VIEW " + sr_file + " AS" + """
SELECT
TRAN.STUDENT,
CAST(ROUND(TOTAL(TRAN.AMOUNT_CR),2) AS REAL) AS CRE_REG_BEFORE
FROM
X001ac_Trans_addreg TRAN
WHERE
TRAN.MONTH <> '00' AND
TRAN.TRANSDATE <= TRAN.DATEENROL_CALC
GROUP BY
TRAN.STUDENT
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute("DROP VIEW IF EXISTS X001ae_Trans_crereg")
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# CALCULATE THE STUDENT ACCOUNT CREDIT TRANSACTIONS AFTER REGISTRATION
print("Calculate the credits after registration date...")
sr_file = "X001af_Trans_creaftreg"
s_sql = "CREATE VIEW " + sr_file + " AS" + """
SELECT
TRAN.STUDENT,
CAST(ROUND(TOTAL(TRAN.AMOUNT_CR),2) AS REAL) AS CRE_REG_AFTER
FROM
X001ac_Trans_addreg TRAN
WHERE
TRAN.MONTH <> '00' AND
TRAN.TRANSDATE > TRAN.DATEENROL_CALC
GROUP BY
TRAN.STUDENT
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute("DROP VIEW IF EXISTS X001ae_Trans_crereg")
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# CALCULATE THE STUDENT ACCOUNT BALANCE
print("Calculate the account balance...")
sr_file = "X001ag_Trans_balance"
s_sql = "CREATE VIEW " + sr_file + " AS" + """
SELECT
TRAN.STUDENT,
CAST(ROUND(TOTAL(TRAN.AMOUNT),2) AS REAL) AS BAL_CUR
FROM
X000_Transaction TRAN
GROUP BY
TRAN.STUDENT
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# CALCULATE THE DEFERMENT DATE
print("Calculate the deferment date per student...")
sr_file = "X002aa_Defer_date"
s_sql = "CREATE VIEW " + sr_file + " AS" + """
Select
DEFER.STUDENT,
DEFER.DATEEND
From
X000_Deferments_select DEFER
Group By
DEFER.STUDENT
Order By
DEFER.DATEEND
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# CALCULATE THE STUDENT ACCOUNT CREDIT TRANSACTIONS BEFORE DEFERMENT DATE
print("Calculate the credits up to deferment date...")
sr_file = "X002ab_Trans_crebefdef"
s_sql = "CREATE VIEW " + sr_file + " AS" + """
Select
TRAN.STUDENT,
Cast(Round(Total(TRAN.AMOUNT_CR),2) As REAL) As CRE_DEF_BEFORE
From
X000_Transaction TRAN Inner Join
X002aa_Defer_date DDATE On DDATE.STUDENT = TRAN.STUDENT
Where
TRAN.MONTH <> '00' And
TRAN.TRANSDATE <= DDATE.DATEEND
Group By
TRAN.STUDENT
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute("DROP VIEW IF EXISTS X001ae_Trans_crereg")
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
""" ****************************************************************************
ADD BALANCES TO STUDENTS
*****************************************************************************"""
print("ADD BALANCES TO STUDENTS")
funcfile.writelog("ADD BALANCES TO STUDENTS")
# ADD THE BALANCES TO THE LIST OF REGISTERED STUDENTS
print("Add the calculated balances to the students list...")
sr_file = "X001aa_Students"
s_sql = "CREATE TABLE " + sr_file + " AS" + """
Select
STUD.*,
BOPEN.BAL_OPEN,
CBREG.CRE_REG_BEFORE,
CAST(0 AS REAL) AS BAL_REG_CALC,
BREG.BAL_REG,
CAREG.CRE_REG_AFTER,
CAST(0 AS REAL) AS BAL_CRE_CALC,
BAL.BAL_CUR,
FEE.FEE_REG,
CBDEF.CRE_DEF_BEFORE,
CAST(0 AS REAL) AS BAL_DEF_CALC
From
X000_Students STUD Left Join
X001aa_Trans_balopen BOPEN ON BOPEN.STUDENT = STUD.KSTUDBUSENTID Left Join
X001ad_Trans_balreg BREG ON BREG.STUDENT = STUD.KSTUDBUSENTID Left Join
X001ae_Trans_crebefreg CBREG ON CBREG.STUDENT = STUD.KSTUDBUSENTID Left Join
X001af_Trans_creaftreg CAREG ON CAREG.STUDENT = STUD.KSTUDBUSENTID Left Join
X001ab_Trans_feereg FEE ON FEE.STUDENT = STUD.KSTUDBUSENTID Left Join
X001ag_Trans_balance BAL ON BAL.STUDENT = STUD.KSTUDBUSENTID Left Join
X002ab_Trans_crebefdef CBDEF ON CBDEF.STUDENT = STUD.KSTUDBUSENTID
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Calc balance after credits up to registration
print("Add column bal_reg_calc...")
so_curs.execute("UPDATE " + sr_file + """
SET BAL_REG_CALC =
CASE
WHEN TYPEOF(BAL_OPEN) = "null" AND TYPEOF(CRE_REG_BEFORE) = "null" THEN 0
WHEN TYPEOF(BAL_OPEN) = "null" THEN CRE_REG_BEFORE
WHEN TYPEOF(CRE_REG_BEFORE) = "null" THEN BAL_OPEN
ELSE BAL_OPEN + CRE_REG_BEFORE
END
;""")
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: bal_reg_calc")
# Calc balance including all credits
print("Add column bal_cre_calc...")
so_curs.execute("UPDATE " + sr_file + """
SET BAL_CRE_CALC =
CASE
WHEN TYPEOF(CRE_REG_AFTER) = "null" THEN BAL_REG_CALC
ELSE BAL_REG_CALC + CRE_REG_AFTER
END
;""")
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: bal_cre_calc")
# Calc balance after credits up to registration
print("Add column bal_def_calc...")
so_curs.execute("UPDATE " + sr_file + """
SET BAL_DEF_CALC =
CASE
WHEN TYPEOF(BAL_OPEN) = "null" AND TYPEOF(CRE_DEF_BEFORE) = "null" THEN BAL_CRE_CALC
WHEN TYPEOF(BAL_OPEN) = "null" THEN CRE_DEF_BEFORE
WHEN TYPEOF(CRE_DEF_BEFORE) = "null" THEN BAL_CRE_CALC
ELSE BAL_CRE_CALC
END
;""")
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: bal_def_calc")
# CALCULATE THE STUDENT ACCOUNT CREDIT TRANSACTIONS BEFORE REGISTRATION
print("Join students and deferments...")
sr_file = "X001ab_Students_deferment"
s_sql = "CREATE TABLE " + sr_file + " AS" + """
Select
X001aa_Students.*,
X000_Deferments_select.*
From
X001aa_Students Left Join
X000_Deferments_select On X000_Deferments_select.STUDENT = X001aa_Students.KSTUDBUSENTID
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
""" ****************************************************************************
CALCULATE DEFERMENT STATUS
*****************************************************************************"""
print("CALCULATE DEFERMENT STATUS")
funcfile.writelog("CALCULATE DEFERMENT STATUS")
# CALCULATE THE DEFERMENT TYPE
print("Calculate the deferment type...")
so_curs.execute("ALTER TABLE " + sr_file + " ADD COLUMN DEFER_TYPE INT;")
s_sql = "UPDATE " + sr_file + """
SET DEFER_TYPE =
CASE
WHEN BAL_REG_CALC <= 0 THEN 0
WHEN BAL_REG_CALC > 0 And BAL_REG_CALC <= 1000 THEN 1
WHEN BAL_REG_CALC > 1000 And BAL_DEF_CALC <= 0 THEN 2
WHEN BAL_REG_CALC > 1000 And STUDENT IS NULL THEN 3
WHEN BAL_REG_CALC > 1000 And DATEEND = '%YEARE%' THEN 6
WHEN BAL_REG_CALC > 1000 And DATEEND >= '%TODAY%' THEN 5
WHEN BAL_REG_CALC > 1000 And BAL_DEF_CALC > 0 THEN 4
WHEN BAL_REG_CALC > 1000 THEN 7
ELSE 8
END;"""
if s_period == "curr":
s_sql = s_sql.replace("%YEARE%", funcdate.cur_yearend())
elif s_period == "prev":
s_sql = s_sql.replace("%YEARE%", funcdate.prev_yearend())
else:
s_sql = s_sql.replace("%YEARE%", s_year + "-12-31")
s_sql = s_sql.replace("%TODAY%", funcdate.today())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: DEFER_TYPE")
# CALCULATE THE DEFERMENT TYPE
print("Calculate the deferment type description...")
so_curs.execute("ALTER TABLE " + sr_file +
" ADD COLUMN DEFER_TYPE_DESC TEXT;")
s_sql = "UPDATE " + sr_file + """
SET DEFER_TYPE_DESC =
CASE
WHEN DEFER_TYPE = 0 THEN 'CREDIT ACCOUNT WITH REGISTRATION'
WHEN DEFER_TYPE = 1 THEN 'ACCOUNT LESS THAN R1000 WITH REGISTRATION'
WHEN DEFER_TYPE = 2 THEN 'ACCOUNT SETTLED ON AGREEMENT DATE'
WHEN DEFER_TYPE = 3 THEN 'REGISTERED WITHOUT AGREEMENT'
WHEN DEFER_TYPE = 4 THEN 'ACCOUNT IN ARREARS ON AGREEMENT DATE'
WHEN DEFER_TYPE = 5 THEN 'FUTURE AGREEMENT DATE'
WHEN DEFER_TYPE = 6 THEN 'FULL YEAR DEFERMENT'
ELSE 'OTHER'
END;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: DEFER_TYPE_DESC")
""" ****************************************************************************
BUILD THE FINAL DEFERMENTS
*****************************************************************************"""
print("BUILD THE FINAL DEFERMENTS")
funcfile.writelog("BUILD THE FINAL DEFERMENTS")
# FINAL DEFERMENTS TABLE
print("Build the final deferments table...")
sr_file = "X001ax_Deferments_final_" + s_period
s_sql = "CREATE TABLE " + sr_file + " AS" + """
Select
Case
When DEFER.FSITEORGUNITNUMBER = -9 Then 'MAFIKENG'
When DEFER.FSITEORGUNITNUMBER = -2 Then 'VAAL TRIANGLE'
Else 'POTCHEFSTROOM'
End As CAMPUS,
DEFER.KSTUDBUSENTID As STUDENT_VSS,
DEFER.DATEQUALLEVELSTARTED As DATEQUALSTART,
DEFER.DATEENROL_CALC AS DATEENROL,
DEFER.DEFER_TYPE,
DEFER.DEFER_TYPE_DESC,
DEFER.BAL_OPEN,
DEFER.CRE_REG_BEFORE,
DEFER.BAL_REG_CALC,
DEFER.CRE_DEF_BEFORE,
DEFER.BAL_DEF_CALC,
DEFER.CRE_REG_AFTER,
DEFER.BAL_CRE_CALC,
DEFER.BAL_CUR,
Upper(DEFER.QUALIFICATIONCODE || ' ' || DEFER.QUALIFICATIONFIELDOFSTUDY || ' ' || DEFER.QUALIFICATIONLEVEL) As
QUALIFICATION,
Upper(DEFER.QUAL_TYPE) As QUAL_TYPE,
DEFER.ISMAINQUALLEVEL As MAIN_IND,
DEFER.ENROLACADEMICYEAR As YEAR_ACAD,
DEFER.ENROLHISTORYYEAR As YEAR_HIST,
Upper(DEFER.ENTRY_LEVEL) As ENTRY_LEVEL,
Upper(DEFER.ENROL_CAT) As ENROL_CAT,
Upper(DEFER.PRESENT_CAT) As PRESENT_CAT,
Upper(DEFER.STATUS_FINAL) As STATUS_FINAL,
Upper(DEFER.LEVY_CATEGORY) As LEVY_CATEGORY,
Upper(DEFER.ORGUNIT_NAME) As ORGUNIT_NAME,
DEFER.FSITEORGUNITNUMBER As CAMPUS_CODE,
DEFER.STUDENT As STUDENT_DEF,
DEFER.DATEARRANGED,
DEFER.DATESTART,
DEFER.DATEEND,
DEFER.TOTALAMOUNT,
Upper(DEFER.SUBACCOUNTTYPE) As SUBACCOUNTTYPE,
Upper(DEFER.DEFERMENTTYPE) As DEFERMENTTYPE,
Upper(DEFER.DEFERMENTREASON) As DEFERMENTREASON,
Upper(DEFER.NOTE) As NOTE,
DEFER.FADMISSIONSITE,
DEFER.FMAINQUALSITE,
DEFER.EMPLOYEE,
DEFER.FAUDITUSERCODE,
DEFER.AUDITDATETIME,
DEFER.DISCONTINUEDATE,
Upper(DEFER.DISCONTINUE_REAS) As DISCONTINUE_REAS
From
X001ab_Students_deferment DEFER
Order By
DEFER.FSITEORGUNITNUMBER,
DEFER.DEFER_TYPE,
BAL_CUR
"""
""" DEFER COLUMNS AVAILABLE BUT NOT USED ***********************************
DEFER.DATEENROL,
DEFER.STARTDATE,
DEFER.ENDDATE,
Upper(DEFER.ACTIVE_IND) As ACTIVE_IND,
DEFER.BAL_REG,
*************************************************************************"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export the declaration data
if l_export:
if s_period == "curr":
sx_path = "R:/Debtorstud/" + funcdate.cur_year() + "/"
elif s_period == "prev":
sx_path = "R:/Debtorstud/" + funcdate.prev_year() + "/"
else:
sx_path = "R:/Debtorstud/" + s_year + "/"
sx_file = "Deferment_001_student_"
print("Export data..." + sx_path + sx_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)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
# SUMMARIZE
print("Summarize registrations with accounts...")
sr_file = "X001ac_Students_deferment_summ_" + s_period
s_sql = "CREATE TABLE " + sr_file + " AS" + """
Select
'%YEAR%' As YEAR,
DEFER.CAMPUS,
DEFER.DEFER_TYPE,
DEFER.DEFER_TYPE_DESC,
Cast(Count(DEFER.STUDENT_VSS) As INT) As STUD_COUNT,
Cast(Round(Total(DEFER.BAL_REG_CALC),2) As REAL) As BAL_REG_DATE,
Cast(Round(Sum(DEFER.BAL_DEF_CALC),2) As REAL) As BAL_DEF_DATE,
Cast(Round(Sum(DEFER.BAL_CUR),2) As REAL) As BAL_CUR
From
X001ax_Deferments_final_%PERIOD% DEFER
Group By
DEFER.DEFER_TYPE,
DEFER.DEFER_TYPE_DESC,
DEFER.CAMPUS
Order By
DEFER.CAMPUS,
DEFER.DEFER_TYPE
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%PERIOD%", s_period)
s_sql = s_sql.replace("%YEAR%", s_year)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export the summary
if l_export:
if s_period == "curr":
sx_path = "R:/Debtorstud/" + funcdate.cur_year() + "/"
elif s_period == "prev":
sx_path = "R:/Debtorstud/" + funcdate.prev_year() + "/"
else:
sx_path = "R:/Debtorstud/" + s_year + "/"
sx_file = "Deferment_001_summary_"
print("Export data..." + sx_path + sx_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)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
# CREATE DUPLICATE SUMMARY FILE TO RECEIVE SUMMARY FOR PREVIOUS YEARS
print("Summ previous years...")
sr_file = "X001ad_Deferment_summ"
s_sql = "Create Table " + sr_file + " As " + """
Select
*
From
X001ac_Students_deferment_summ_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)
# GET PREVIOUS YEAR SUMMARIES
sr_file = "X001ad_Deferment_summ"
print("Import previous deferment summaries...")
co = open(ed_path + "301_Deferment_summ.csv", "r")
co_reader = csv.reader(co)
# Read the COLUMN database data
for row in co_reader:
# Populate the column variables
if row[0] == "YEAR":
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] + "')"
so_curs.execute(s_cols)
so_conn.commit()
# Close the impoted data file
co.close()
funcfile.writelog("%t IMPORT TABLE: " + ed_path +
"301_Deferment_summ.csv (" + sr_file + ")")
# CREATE DUPLICATE SUMMARY FILE TO RECEIVE SUMMARY FOR PREVIOUS YEARS
print("Sort previous years...")
sr_file = "X001ae_Deferment_summ_sort"
s_sql = "Create Table " + sr_file + " As " + """
Select
*
From
X001ad_Deferment_summ
Order By
DEFER_TYPE,
CAMPUS,
YEAR Desc
;"""
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
*****************************************************************************"""
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: REPORT_VSS_DEFERMENTS")
return
