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' As ORG,
Substr(a.LOCATION,1,3) As LOC,
a.EMPLOYEE,
a.CATEGORY,
a.PERSON_TYPE,
a.SUPERVISOR,
a.DECLARED,
Cast(Julianday('%TODAY%') - Julianday(a.DECLARATION_DATE) As Int) As DAYS_DECLARED
From
X003_dashboard_curr a
;"""
s_sql = s_sql.replace("%TODAY%", funcdate.today())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# SELECT TEST DATA
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.SUPERVISOR,
FIND.EMPLOYEE,
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
c.UNKNOWN_ID,
c.SARS_REPORT_ID,
c.REMUNERATION_BEFORE_ID,
c.PROJECT_ID,
c.SOURCE_CONTRACT_ID,
c.LAST_UPDATE_DATE,
c.LAST_UPDATED_BY,
c.LAST_UPDATE_LOGIN,
c.CREATED_BY,
c.CREATION_DATE
From
X000_CONTRACT c
Where
strftime('%Y-%m-%d', '%DATE%') between c.contract_from and c.contract_to
;"""
s_sql = s_sql.replace("%DATE%", funcdate.today())
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 CONTRACTS MASTER
print("Build contracts current...")
sr_file = "X004_CONTRACT_CURR_SUMM"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
c.ASSIGNMENT_ID,
Count(c.ASSIGNMENT_EXTRA_INFO_ID) As COUNT_ASSIGNMENT
From
X004_CONTRACT_CURR c
Group By
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 people_detail_list(so_conn,
s_table: str = 'X000_PEOPLE',
s_date: str = funcdate.today()) -> int:
"""
Function to build PEOPLE lists on any given date.
:param so_conn: object: Table connection object
:param s_table: str: Table name to create (Default=X000_PEOPLE)
:param s_date: str: List date (Default=Today)
:return: int: Table row count
"""
# IMPORT FUNCTIONS
from _my_modules import funcpayroll
# DECLARE VARIABLES
l_debug: bool = False
# OPEN THE DATABASE CURSOR
so_curs = so_conn.cursor()
# BUILD TOTAL ANNUAL PACKAGE
i_records = funcpayroll.payroll_element_screen_value(
so_conn, 'X000_PACKAGE', 'nwu total_package', 'annual amount', s_date)
if l_debug:
print(i_records)
# BUILD NRF ALLOWANCE
i_records = funcpayroll.payroll_element_screen_value(
so_conn, 'X000_NRF_ALLOWANCE', 'nwu allowance nrf', 'option', s_date)
if l_debug:
print(i_records)
# BUILD ACTUAL START DATE
i_records = funcpayroll.payroll_element_screen_value(
so_conn, 'X000_LONG_SERVICE_DATE', 'nwu long service award',
'long service date', s_date)
if l_debug:
print(i_records)
# BUILD PENSIONABLE SALARY
i_records = funcpayroll.payroll_element_screen_value(
so_conn, 'X000_PENSIONABLE_SALARY', 'nwu pensionable salary',
'pension ratio', s_date)
if l_debug:
print(i_records)
# BUILD CURRENT PEOPLE
if l_debug:
print("Build people list...")
print(s_table)
print(s_date)
sr_file = s_table
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
papf.employee_number employee_number,
papf.full_name name_full,
'' name_list,
'' name_address,
papf.attribute4 preferred_name,
case
when upper(titl.meaning) = 'MR.' then 'MR'
when upper(titl.meaning) = 'MRS.' then 'MRS'
when upper(titl.meaning) = 'MS.' then 'MS'
else upper(titl.meaning)
end as title,
case
when instr(papf.middle_names,' ') > 1 then
substr(papf.first_name,1,1) ||
substr(papf.middle_names,1,1) ||
trim(substr(papf.middle_names,instr(papf.middle_names,' '),2))
when length(papf.middle_names) > 0 then
substr(papf.first_name,1,1) || substr(papf.middle_names,1,1)
else substr(papf.first_name,1,1)
end as initials,
papf.last_name name_last,
papf.date_of_birth date_of_birth,
strftime('%Y','%DATE%') - strftime('%Y', papf.date_of_birth) as employee_age,
upper(sex.meaning) gender,
upper(race.meaning) race,
case
when papf.attribute3 = 'A' then 'AFRIKAANS'
else 'ENGLISH'
end as corr_language,
upper(mar.meaning) marital_status,
upper(dis.meaning) disabled,
upper(nat.meaning) nationality,
papf.national_identifier national_identifier,
upper(papf.per_information2) passport,
upper(papf.per_information3) permit,
Replace(Substr(papf.per_information8,1,10),'/','-') permit_expire,
ph.phone_number phone_work,
mo.phone_number phone_mobile,
papf.email_address email_address,
papf.attribute2 internal_box,
sars.address_sars address_sars,
post.address_post address_post,
Replace(Substr(lsd.element_value,1,10),'/','-') date_started,
papf.effective_start_date people_start_date,
papf.effective_end_date people_end_date,
paaf.effective_start_date assign_start_date,
paaf.effective_end_date assign_end_date,
pps.date_start service_start_date,
pps.actual_termination_date service_end_date,
pps.leaving_reason end_reason,
upper(ler.meaning) leaving_reason,
papf.person_id person_id,
paaf.assignment_id assignment_id,
paaf.assignment_number assignment_number,
upper(cat.meaning) assignment_category,
hrp.acad_supp as employee_category,
upper(ppt.user_person_type) user_person_type,
pg.grade grade,
pg.grade_calc grade_calc,
upper(pg.grade_name) grade_name,
ppg.segment1 leave_code,
paaf.ass_attribute1 type_of_shift,
paaf.ass_attribute2 joint_appt,
paaf.organization_id organization_id,
upper(hrl.location_code) location,
upper(hos.division) division,
upper(hos.faculty) faculty,
hro.oe_code oe_code,
upper(hro.org_name) organization,
upper(hro.org_type) organization_type,
upper(hro.org_type_desc) organization_description,
hro.org_head_person_id org_head_person_id,
head.employee_number oe_head_number,
head.full_name oe_head_name_name,
sup.employee_number supervisor_number,
sup.full_name supervisor_name,
paaf.position_id position_id,
hrp.position position,
hrp.max_persons max_persons,
upper(hrp.position_name) position_name,
hrp.parent_position_id parent_position_id,
upper(pj.job_name) job_name,
upper(pj.job_segment_name) job_segment_name,
ppb.name salary_basis,
cast(pack.element_value As Real) annual_package,
hrp.attribute1 account_cost,
upper(hrp.attribute2) account_allocate,
cast(hrp.attribute3 as int) account_part,
cast(pes.element_value As Real) pension_ratio,
nrf.element_value nrf_rated,
acc.acc_type account_type,
acc.acc_branch account_branch,
acc.acc_number account_number,
acc.acc_relation account_relation,
acc.ppm_information1 account_sars,
papf.last_update_date people_update_date,
papf.last_updated_by people_update_by,
paaf.last_update_date assignment_update_date,
paaf.last_updated_by assignment_update_by
FROM
per_all_people_f papf left join
per_all_assignments_f paaf on paaf.person_id = papf.person_id left join
per_periods_of_service pps on pps.person_id = papf.person_id and
paaf.effective_end_date between pps.date_start and
ifnull(pps.actual_termination_date, '4712-12-31') left join
X000_positions hrp on hrp.position_id = paaf.position_id and
paaf.effective_end_date between hrp.effective_start_date and hrp.effective_end_date left join
x000_organization hro on hro.organization_id = paaf.organization_id left join
x000_organization_struct hos on hos.org1 = paaf.organization_id left join
hr_locations_all hrl on hrl.location_id = paaf.location_id left join
per_all_people_f sup on sup.person_id = paaf.supervisor_id and
paaf.effective_end_date between sup.effective_start_date and sup.effective_end_date left join
per_all_people_f head on head.person_id = hro.org_head_person_id and
paaf.effective_end_date between head.effective_start_date and head.effective_end_date left join
per_person_type_usages_f pptuf on pptuf.person_id = papf.person_id and
paaf.effective_end_date between pptuf.effective_start_date and pptuf.effective_end_date left join
per_person_types ppt on pptuf.person_type_id = ppt.person_type_id left join
x000_grades pg on pg.grade_id = paaf.grade_id and
paaf.effective_end_date between pg.date_from and ifnull(pg.date_to, '4712-12-31') left join
x000_jobs pj on pj.job_id = hrp.job_id and
hrp.effective_end_date between pj.date_from and ifnull(pj.date_to, '4712-12-31') left join
pay_people_groups ppg on ppg.people_group_id = paaf.people_group_id and
ppg.enabled_flag = 'Y' left join
per_pay_bases ppb on ppb.pay_basis_id = paaf.pay_basis_id left join
hr_lookups cat on cat.lookup_type = 'EMP_CAT' and cat.lookup_code = paaf.employment_category left join
hr_lookups titl on titl.lookup_type = 'TITLE' and titl.lookup_code = papf.title left join
hr_lookups sex on sex.lookup_type = 'SEX' and sex.lookup_code = papf.sex and sex.enabled_flag = 'Y' left join
hr_lookups nat on nat.lookup_type = 'NATIONALITY' and nat.lookup_code = papf.nationality left join
hr_lookups race on race.lookup_type = 'ZA_RACE' and race.lookup_code = papf.per_information4 and
race.enabled_flag = 'Y' left join
hr_lookups mar on mar.lookup_type = 'MAR_STATUS' and mar.lookup_code = papf.marital_status and
mar.enabled_flag = 'Y' left join
hr_lookups dis on dis.lookup_type = 'REGISTERED_DISABLED' and
dis.lookup_code = papf.registered_disabled_flag left join
hr_lookups ler on ler.lookup_type = 'LEAV_REAS' and ler.lookup_code = pps.leaving_reason left join
X000_phone_work_latest ph on ph.parent_id = papf.person_id and
strftime('%Y-%m-%d', '%DATE%') between ph.date_from and ifnull(ph.date_to, '31-DEC-4712') left join
x000_phone_mobile_latest mo on mo.parent_id = papf.person_id and
strftime('%Y-%m-%d', '%DATE%') between mo.date_from and ifnull(mo.date_to, '31-DEC-4712') left join
x000_package pack on pack.employee_number = papf.employee_number left join
x000_nrf_allowance nrf on nrf.employee_number = papf.employee_number left join
x000_long_service_date lsd on lsd.employee_number = papf.employee_number left join
x000_pensionable_salary pes on pes.employee_number = papf.employee_number left join
x000_pay_accounts acc on acc.assignment_id = paaf.assignment_id and
acc.org_payment_method_id = 61 and
paaf.effective_end_date between acc.effective_start_date and acc.effective_end_date left join
x000_address_sars sars on sars.person_id = papf.person_id and
paaf.effective_end_date between sars.date_from and sars.date_to left join
x000_address_post post on post.person_id = papf.person_id and
paaf.effective_end_date between post.date_from and post.date_to
WHERE
paaf.assignment_status_type_id in (1) and
strftime('%Y-%m-%d', '%DATE%') between paaf.effective_start_date and paaf.effective_end_date and
paaf.effective_end_date between papf.effective_start_date and papf.effective_end_date and
ppt.user_person_type != 'Retiree'
ORDER BY
papf.employee_number
;"""
s_sql = s_sql.replace("%DATE%", s_date)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
so_curs.execute("Update " + sr_file +
" Set name_list = name_last||' '||title||' '||initials;")
so_conn.commit()
so_curs.execute(
"Update " + sr_file +
" Set name_address = title||' '||initials||' '||name_last;")
so_conn.commit()
# RETURN VALUE
i_return = funcsys.tablerowcount(so_curs, s_table)
return i_return
def payroll_element_screen_value(so_conn,
s_table: str = '',
s_element: str = '',
s_option: str = '',
s_date: str = '') -> int:
"""
Function to build table of PAYROLL ELEMENT
:param so_conn: object: Table connection object
:param s_table: str: Table name to create
:param s_element: str: Element name (lower case)
:param s_option: str: Element input value (lower case)
:param s_date: str: Element date
:return: int: Table row count
"""
# IMPORT SYSTEM MODULES
# import csv
# import sqlite3
# OPEN OWN MODULES
# from _my_modules import funccsv
from _my_modules import funcdate
from _my_modules import funcfile
from _my_modules import funcsys
# INDEX
"""
ENVIRONMENT
OPEN THE DATABASES
BEGIN OF SCRIPT
END OF SCRIPT
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# DECLARE VARIABLES
i_return: int = 0
l_debug: bool = False
ed_path = "S:/_external_data/" # External data path
re_path = "R:/People/" # Results path
l_export: bool = False
l_mail: bool = False
# OPEN THE LOG
if l_debug:
print("----------------------------------------------")
print("FUNCTION PAYROLL: PAYROLL_ELEMENT_SCREEN_VALUE")
print("----------------------------------------------")
funcfile.writelog("Now")
funcfile.writelog("FUNCTION PAYROLL: PAYROLL_ELEMENT_SCREEN_VALUE")
funcfile.writelog("----------------------------------------------")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
if l_debug:
print("OPEN THE DATABASES")
funcfile.writelog("OPEN THE DATABASES")
# OPEN THE WORKING DATABASE
so_curs = so_conn.cursor()
""" ****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
if l_debug:
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
# NO FILE NAME - TEST
if s_table == '':
s_table = 'A000_test'
# NO ELEMENT NAME OR INPUT VALUE
if s_element == '' and s_option == '':
sr_file = s_table
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
return i_return
# ELEMENT
if s_element != '':
s_element = "And Lower(petf.element_name) Like '%" + s_element + "%'"
# OPTION
if s_option != '':
s_option = "And Lower(pivf.name) Like '%" + s_option + "%'"
# NO DATE - TODAY
if s_date == '':
s_date = funcdate.today()
# BUILD THE ELEMENT LIST
if l_debug:
print("Obtain " + s_element + " - " + s_option)
sr_file = s_table
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
papf.employee_number,
--papf.full_name,
Max(peevf.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE,
peevf.EFFECTIVE_END_DATE,
Upper(petf.element_name) As ELEMENT_NAME,
Upper(pivf.name) As ELEMENT_OPTION,
Upper(peevf.screen_entry_value) As ELEMENT_VALUE
From
PER_ALL_PEOPLE_F papf,
PER_ALL_ASSIGNMENTS_F paaf,
PAY_ELEMENT_ENTRIES_F_CURR peef,
PAY_ELEMENT_TYPES_F petf,
PAY_ELEMENT_ENTRY_VALUES_F_CURR peevf,
PAY_INPUT_VALUES_F pivf
Where
papf.person_id = paaf.person_id And
'%DATE%' between papf.effective_start_date And papf.effective_end_date And
paaf.assignment_id = peef.assignment_id And
paaf.primary_flag = 'Y' And
paaf.assignment_status_type_id in (1) and
'%DATE%' between paaf.effective_start_date And paaf.effective_end_date And
paaf.effective_end_date between papf.effective_start_date and papf.effective_end_date and
peef.element_type_id = petf.element_type_id And
'%DATE%' between peef.effective_start_date And peef.effective_end_date And
peevf.element_entry_id = peef.element_entry_id And
pivf.element_type_id = petf.element_type_id And
pivf.input_value_id = peevf.input_value_id And
--peevf.screen_entry_value > 0 And
'%DATE%' between petf.effective_start_date And petf.effective_end_date
%ELEMENT%
%OPTION%
Group by
papf.employee_number
Order By
papf.employee_number,
peevf.effective_start_date
;"""
s_sql = s_sql.replace("%DATE%", s_date)
s_sql = s_sql.replace("%ELEMENT%", s_element)
s_sql = s_sql.replace("%OPTION%", s_option)
# print(s_sql)
so_curs.execute(s_sql)
so_conn.commit()
i_return = funcsys.tablerowcount(so_curs, s_table)
funcfile.writelog("%t BUILD TABLE: " + sr_file + ' (' + str(i_return) +
' RECORDS)')
""" ****************************************************************************
END OF SCRIPT
*****************************************************************************"""
if l_debug:
print("END OF SCRIPT")
funcfile.writelog("END OF SCRIPT")
# CLOSE THE WORKING DATABASE
# so_conn.close()
# CLOSE THE LOG
funcfile.writelog("---------------------------------------")
funcfile.writelog("COMPLETED: PAYROLL_ELEMENT_SCREEN_VALUE")
return i_return
def run_scripts(s_script: str = "a003",
s_parameter1: str = "",
s_parameter2: str = ""):
"""
SCRIPT TO RUN OTHER SCRIPTS
IT WILL USE GLOBAL l_run VARIABLES
:param s_script: Script to run
:param s_parameter1: Script first parameter
:param s_parameter2: Script second parameter
:return: bool: Successful or not
"""
# VARIABLES
l_return = False
l_debug = True
# DEBUG
if l_debug:
print("SCRIPT: " + s_script)
print("PARAM1: " + s_parameter1)
print("PARAM2: " + s_parameter2)
# SWITCH ON
funcconf.l_run_system = True
if s_script in "all|data|kfs":
funcconf.l_run_kfs_test = True
elif s_script in "all|data|people":
funcconf.l_run_people_test = True
elif s_script in "all|data|vss":
funcconf.l_run_vss_test = True
# GENERAL GROUP ****************************************************************
# UPDATE LOG
s_project: str = "A002_log"
if s_script in "a002|all|general":
import A002_log
try:
if s_script == 'a002' and s_parameter1[0:1] == 'y':
A002_log.log_capture(funcdate.yesterday(), True)
elif s_script == 'all':
A002_log.log_capture(funcdate.yesterday(), True)
elif s_script == 'general':
A002_log.log_capture(funcdate.yesterday(), True)
else:
A002_log.log_capture(funcdate.today(), False)
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
'NWUIACA:Fail:' + s_project,
'NWUIACA: Fail: ' + s_project)
# VACUUM TEST FINDING TABLES
s_project: str = "A003_table_vacuum"
if s_script in "a003|all|general":
import A003_table_vacuum
try:
A003_table_vacuum.table_vacuum()
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
'NWUIACA:Fail:' + s_project,
'NWUIACA: Fail: ' + s_project)
# BACKUP MYSQL
s_project: str = "B008_mysql_backup"
if s_script in "b008|all|general":
import B008_mysql_backup
try:
l_return = B008_mysql_backup.mysql_backup()
if l_return:
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
else:
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Fail:' + s_project,
'NWUIACA: Fail: ' + s_project)
except Exception as err:
l_return = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
'NWUIACA:Fail:' + s_project,
'NWUIACA: Fail: ' + s_project)
# IMPORT TEMP
s_project: str = "A001_oracle_to_sqlite(temp)"
if s_script in "a001":
if s_script == "a001" and s_parameter1[0:1] == "t":
import A001_oracle_to_sqlite
try:
A001_oracle_to_sqlite.oracle_to_sqlite()
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# PEOPLE GROUP *****************************************************************
# IMPORT PEOPLE
s_project: str = "A001_oracle_to_sqlite(people)"
if s_script in "a001|all|data|people":
if (s_script == "a001"
and s_parameter1 == "people") or (s_script
in "all|data|people"):
import A001_oracle_to_sqlite
try:
A001_oracle_to_sqlite.oracle_to_sqlite(
"000b_Table - people.csv", "PEOPLE")
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_people_test = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# PEOPLE MASTER LISTS
s_project: str = "B001_people_lists"
if funcconf.l_run_people_test:
if s_script in "b001|all|people":
import B001_people_lists
try:
B001_people_lists.people_lists()
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_people = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# PEOPLE LISTS
s_project: str = "C003_people_list_masterfile"
if funcconf.l_run_people_test:
if s_script in "c003|all|people":
import C003_people_list_masterfile
try:
C003_people_list_masterfile.people_list_masterfile()
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_people = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# PEOPLE TEST MASTER FILE
s_project: str = "C001_people_test_masterfile"
if funcconf.l_run_people_test:
if s_script in "c001|all|people":
import C001_people_test_masterfile
try:
C001_people_test_masterfile.people_test_masterfile()
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_people = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# PEOPLE TEST CONFLICT
s_project: str = "C002_people_test_conflict"
if funcconf.l_run_people_test:
if s_script in "c002|all|people":
import C002_people_test_conflict
try:
C002_people_test_conflict.people_test_conflict()
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_people = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# PEOPLE PAYROLL LISTS
if s_script in "b004|all|people":
import B004_payroll_lists
s_project: str = "B004_payroll_lists"
try:
B004_payroll_lists.payroll_lists()
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
'NWUIACA:Fail:' + s_project,
'NWUIACA: Fail: ' + s_project)
# VSS GROUP ********************************************************************
# IMPORT VSS
s_project: str = "A001_oracle_to_sqlite(vss)"
if s_script in "a001|all|data|vss":
if (s_script == "a001"
and s_parameter1 == "vss") or (s_script in "all|data|vss"):
import A001_oracle_to_sqlite
try:
A001_oracle_to_sqlite.oracle_to_sqlite("000b_Table - vss.csv",
"VSS")
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_vss_test = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# VSS LISTS
s_project: str = "B003_vss_lists"
if funcconf.l_run_vss_test:
if s_script in "b003|all|vss":
import B003_vss_lists
try:
B003_vss_lists.vss_lists()
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_vss = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# VSS PERIOD LISTS
s_project: str = "B007_vss_period_list"
if funcconf.l_run_vss_test:
if s_script in "b007|all|vss":
if s_script != "b007":
s_parameter1 = "curr"
if (s_parameter1 in "curr|prev") or (2015 <= int(s_parameter1) <=
int(funcdate.cur_year()) - 2):
import B007_vss_period_list
try:
B007_vss_period_list.vss_period_list(s_parameter1)
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_vss = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# VSS STUDENT DEFERMENT MASTER LISTS
s_project: str = "C301_report_student_deferment"
if funcconf.l_run_vss_test:
if s_script in "c301|all|vss":
if s_script != "c301":
s_parameter1 = "curr"
if (s_parameter1 in "curr|prev") or (2015 <= int(s_parameter1) <=
int(funcdate.cur_year()) - 2):
import C301_report_student_deferment
try:
C301_report_student_deferment.studdeb_deferments(
s_parameter1)
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_vss = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
#
# VSS STUDENT DEFERMENT MASTER LISTS
s_project: str = "C301_report_student_deferment"
if funcconf.l_run_vss_test:
if s_script in "c301|all|vss":
if s_script != "c301":
s_parameter1 = "curr"
if (s_parameter1 in "curr|prev") or (2015 <= int(s_parameter1) <=
int(funcdate.cur_year()) - 2):
import C301_report_student_deferment
try:
C301_report_student_deferment.studdeb_deferments(
s_parameter1)
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_vss = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# VSS STUDENT DEFERMENT MASTER LISTS
s_project: str = "C301_report_student_deferment"
if funcconf.l_run_vss_test:
if s_script in "c301|all|vss":
if s_script != "c301":
s_parameter1 = "curr"
if (s_parameter1 in "curr|prev") or (2015 <= int(s_parameter1) <=
int(funcdate.cur_year()) - 2):
import C301_report_student_deferment
try:
C301_report_student_deferment.studdeb_deferments(
s_parameter1)
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_vss = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# VSS STUDENT DEBTOR RECON
s_project: str = "C200_report_studdeb_recon"
if funcconf.l_run_vss_test:
if s_script in "c200|all|vss":
if s_script != "c200":
s_parameter1 = "curr"
# No open balances
# r_maf = 0
# r_pot = 0
# r_vaa = 0
# 2020 balances
r_maf = 48501952.09
r_pot = -12454680.98
r_vaa = 49976048.39
# 2019 balances
# r_maf = 66561452.48
# r_pot = -18340951.06
# r_vaa = 39482933.18
else:
r_maf = 0
r_pot = 0
r_vaa = 0
if s_parameter1 in "curr|prev":
import C200_report_studdeb_recon
try:
C200_report_studdeb_recon.report_studdeb_recon(
r_maf, r_pot, r_vaa, s_parameter1)
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_vss = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# VSS STUDENT MASTER FILE TESTS
s_project: str = "C300_test_student_general"
if funcconf.l_run_vss_test:
if s_script in "c300|all|vss":
import C300_test_student_general
try:
C300_test_student_general.test_student_general()
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_vss = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# VSS STUDENT FEE TESTS AND REPORTS
s_project: str = "C302_test_student_fee"
if funcconf.l_run_vss_test:
if s_script in "c302|all|vss":
if s_script != "c302":
s_parameter1 = "curr"
if s_parameter1 in "curr|prev":
import C302_test_student_fee
try:
C302_test_student_fee.student_fee(s_parameter1)
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_vss = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# KFS GROUP ********************************************************************
# IMPORT KFS
s_project: str = "A001_oracle_to_sqlite(kfs)"
if (s_script == "a001" and s_parameter1 == "kfs") or (s_script
in "all|data|kfs"):
import A001_oracle_to_sqlite
try:
A001_oracle_to_sqlite.oracle_to_sqlite("000b_Table - kfs.csv",
"KFS")
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_kfs_test = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
# KFS PERIOD LISTS
s_project: str = "B006_kfs_period_list"
if funcconf.l_run_kfs_test:
if s_script in "b006|all|kfs":
if s_script != "b006":
s_parameter1 = "curr"
if (s_parameter1 in "curr|prev") or (2015 <= int(s_parameter1) <=
int(funcdate.cur_year()) - 2):
import B006_kfs_period_list
try:
B006_kfs_period_list.kfs_period_list(s_parameter1)
l_return = True
if funcconf.l_mail_project:
funcmail.Mail('std_success_gmail',
'NWUIACA:Success:' + s_project,
'NWUIACA: Success: ' + s_project)
except Exception as err:
l_return = False
funcconf.l_run_kfs = False
funcsys.ErrMessage(err, funcconf.l_mail_project,
"NWUIACA:Fail:" + s_project,
"NWUIACA: Fail: " + s_project)
return l_return
print("Add current log to history...")
s_sql = "INSERT INTO " + sr_file + " SELECT * FROM X001ac_calc_time;"
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t COPY TABLE: " + sr_file)
"""*****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# COMMIT AND CLOSE DATABASE
so_conn.commit()
so_conn.close()
# CLOSE THE LOG WRITER *********************************************************
funcfile.writelog("-------------------")
funcfile.writelog("COMPLETED: A002_LOG")
return
if __name__ == '__main__':
try:
log_capture(funcdate.today(), False)
# log_capture(funcdate.yesterday(), True)
except Exception as e:
funcsys.ErrMessage(e)
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