def get_supervisor(o_cursor, s_source="HR", s_key=""):
"""
Function order and set last finding
:param o_cursor: Database cursor
:param s_source: Table to read lookups
:param s_key: Officer search key
:return: Number of records
"""
# DECLARE / BUILD VARIABLES
# SET PREVIOUS FINDINGS
sr_file = "Z001ag_supervisor"
o_cursor.execute("DROP TABLE IF EXISTS " + sr_file)
if l_debug:
print("Import reporting supervisors for mail purposes...")
s_sql = "Create Table " + sr_file + " As" + """
Select
LOOKUP.LOOKUP,
Upper(LOOKUP.LOOKUP_CODE) AS CAMPUS,
LOOKUP.LOOKUP_DESCRIPTION AS EMPLOYEE_NUMBER,
PEOPLE.NAME_ADDR,
PEOPLE.EMAIL_ADDRESS
FROM
%TABLE% LOOKUP Left Join
PEOPLE.X002_PEOPLE_CURR PEOPLE ON PEOPLE.EMPLOYEE_NUMBER = LOOKUP.LOOKUP_DESCRIPTION
WHERE
LOOKUP.LOOKUP = '%KEY%'
;"""
s_sql = s_sql.replace("%KEY%", s_key)
if s_source == "VSS":
s_sql = s_sql.replace("%TABLE%", "VSS.X000_OWN_LOOKUPS")
elif s_source == "KFS":
s_sql = s_sql.replace("%TABLE%", "KFS.X000_OWN_KFS_LOOKUPS")
else:
s_sql = s_sql.replace("%TABLE%", "PEOPLE.X000_OWN_HR_LOOKUPS")
o_cursor.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
return funcsys.tablerowcount(o_cursor, sr_file)
def set_previous_finding(o_cursor):
"""
Function order and set last finding
:param o_cursor: Database cursor
:return: Number of records
"""
# SET PREVIOUS FINDINGS
sr_file = "Z001ab_setprev"
o_cursor.execute("DROP TABLE IF EXISTS " + sr_file)
if l_debug:
print("Obtain the latest previous finding...")
s_sql = "Create Table " + sr_file + " As" + """
Select
GET.PROCESS,
GET.FIELD1,
GET.FIELD2,
GET.FIELD3,
GET.FIELD4,
GET.FIELD5,
Max(GET.DATE_REPORTED) As DATE_REPORTED,
GET.DATE_RETEST,
GET.REMARK
From
Z001aa_getprev GET
Group By
GET.FIELD1,
GET.FIELD2,
GET.FIELD3,
GET.FIELD4,
GET.FIELD5
;"""
o_cursor.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
return funcsys.tablerowcount(o_cursor, sr_file)
def payroll_lists():
"""
Script to build payroll lists
:return: Nothing
"""
""" CONTENTS ***************************************************************
ELEMENTS
BALANCES
SECONDARY ASSIGNMENTS
*************************************************************************"""
# Declare variables
so_path: str = "W:/People_payroll/" # Source database path
so_file: str = "People_payroll.sqlite" # Source database
re_path = "R:/People/" #Results
ed_path = "S:/_external_data/"
s_sql = "" #SQL statements
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B004_PAYROLL_LISTS")
funcfile.writelog("--------------------------")
print("------------------")
print("B004_PAYROLL_LISTS")
print("------------------")
# MESSAGE
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator", "<b>B004 Payroll lists</b>")
# Open the SOURCE file
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
# Attach data sources
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
"""*************************************************************************
START
*************************************************************************"""
"""*************************************************************************
ELEMENTS CURRENT
*************************************************************************"""
print("---------- ELEMENTS CURRENT ----------")
# Build the current element list *******************************************
print("Build the current element list...")
sr_file = "X000aa_element_list_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
PEE.ASSIGNMENT_ID,
PEE.ELEMENT_ENTRY_ID,
PEE.EFFECTIVE_START_DATE,
PEE.EFFECTIVE_END_DATE,
PEE.ELEMENT_LINK_ID,
PEE.CREATOR_TYPE,
PEE.ENTRY_TYPE,
PEE.ELEMENT_TYPE_ID,
PEV.ELEMENT_ENTRY_VALUE_ID,
PEV.INPUT_VALUE_ID,
PEV.SCREEN_ENTRY_VALUE,
PET.ELEMENT_NAME,
PET.REPORTING_NAME,
PET.DESCRIPTION
FROM
PAY_ELEMENT_ENTRIES_F_CURR PEE LEFT JOIN
PAY_ELEMENT_ENTRY_VALUES_F_CURR PEV ON PEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID AND
PEV.EFFECTIVE_START_DATE <= PEE.EFFECTIVE_START_DATE AND
PEV.EFFECTIVE_END_DATE >= PEE.EFFECTIVE_START_DATE LEFT JOIN
PAY_ELEMENT_TYPES_F PET ON PET.ELEMENT_TYPE_ID = PEE.ELEMENT_TYPE_ID AND
PET.EFFECTIVE_START_DATE <= PEE.EFFECTIVE_START_DATE AND
PET.EFFECTIVE_END_DATE >= PEE.EFFECTIVE_START_DATE
;"""
so_curs.execute("DROP TABLE IF EXISTS X000aa_element_list")
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
# s_sql = s_sql.replace("%PMONTH%",funcdate.prev_month())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b>" + str(i) + "</b> Elements")
# Extract the NWU TOTAL PACKAGE element for export *********************
print("Extract the nwu total package element...")
sr_file = "X001aa_element_package_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X000aa_element_list_curr.ASSIGNMENT_ID,
X000aa_element_list_curr.EFFECTIVE_START_DATE,
X000aa_element_list_curr.INPUT_VALUE_ID,
X000aa_element_list_curr.SCREEN_ENTRY_VALUE,
X000aa_element_list_curr.ELEMENT_NAME,
SUBSTR(PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER,1,8) AS EMPL_NUMB
FROM
X000aa_element_list_curr
LEFT JOIN PEOPLE.PER_ALL_ASSIGNMENTS_F ON PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID = X000aa_element_list_curr.ASSIGNMENT_ID AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE <= Date('%TODAY%') AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE >= Date('%TODAY%')
WHERE
X000aa_element_list_curr.INPUT_VALUE_ID = 691 AND
X000aa_element_list_curr.EFFECTIVE_START_DATE <= Date('%TODAY%') AND
X000aa_element_list_curr.EFFECTIVE_END_DATE >= Date('%TODAY%')
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%TODAY%", funcdate.today())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Build the NWU TOTAL PACKAGE export file **************************************
print("Build the nwu total package element export file...")
sr_file = "X001ax_element_package_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X001aa_element_package_curr.EMPL_NUMB,
X001aa_element_package_curr.EFFECTIVE_START_DATE AS DATE,
CAST(X001aa_element_package_curr.SCREEN_ENTRY_VALUE AS REAL) AS PACKAGE
FROM
X001aa_element_package_curr
ORDER BY
X001aa_element_package_curr.EMPL_NUMB
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export the data
print("Export packages...")
sr_filet = sr_file
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = "Payroll_001ax_package_"
sx_filet = sx_file + funcdate.cur_monthendfile()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_filet)
funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_file, s_head)
funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_filet, s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
"""*************************************************************************
ELEMENTS PREVIOUS
*************************************************************************"""
print("---------- ELEMENTS PREVIOUS ----------")
# Build the previous element list *******************************************
print("Build the previous element list...")
sr_file = "X000aa_element_list_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
PAY_ELEMENT_ENTRIES_F_PREV.ASSIGNMENT_ID,
PAY_ELEMENT_ENTRIES_F_PREV.ELEMENT_ENTRY_ID,
PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_START_DATE,
PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_END_DATE,
PAY_ELEMENT_ENTRIES_F_PREV.ELEMENT_LINK_ID,
PAY_ELEMENT_ENTRIES_F_PREV.CREATOR_TYPE,
PAY_ELEMENT_ENTRIES_F_PREV.ENTRY_TYPE,
PAY_ELEMENT_ENTRIES_F_PREV.ELEMENT_TYPE_ID,
PAY_ELEMENT_ENTRY_VALUES_F_PREV.ELEMENT_ENTRY_VALUE_ID,
PAY_ELEMENT_ENTRY_VALUES_F_PREV.INPUT_VALUE_ID,
PAY_ELEMENT_ENTRY_VALUES_F_PREV.SCREEN_ENTRY_VALUE,
PAY_ELEMENT_TYPES_F.ELEMENT_NAME,
PAY_ELEMENT_TYPES_F.REPORTING_NAME,
PAY_ELEMENT_TYPES_F.DESCRIPTION
FROM
PAY_ELEMENT_ENTRIES_F_PREV
LEFT JOIN PAY_ELEMENT_ENTRY_VALUES_F_PREV ON PAY_ELEMENT_ENTRY_VALUES_F_PREV.ELEMENT_ENTRY_ID =
PAY_ELEMENT_ENTRIES_F_PREV.ELEMENT_ENTRY_ID AND PAY_ELEMENT_ENTRY_VALUES_F_PREV.EFFECTIVE_START_DATE <=
PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_START_DATE AND PAY_ELEMENT_ENTRY_VALUES_F_PREV.EFFECTIVE_END_DATE >= PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_START_DATE
LEFT JOIN PAY_ELEMENT_TYPES_F ON PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID = PAY_ELEMENT_ENTRIES_F_PREV.ELEMENT_TYPE_ID AND
PAY_ELEMENT_TYPES_F.EFFECTIVE_START_DATE <= PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_START_DATE AND PAY_ELEMENT_TYPES_F.EFFECTIVE_END_DATE >=
PAY_ELEMENT_ENTRIES_F_PREV.EFFECTIVE_START_DATE
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Extract the previous NWU TOTAL PACKAGE element for export ****************
print("Extract the previous nwu total package element...")
sr_file = "X001aa_element_package_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X000aa_element_list_prev.ASSIGNMENT_ID,
X000aa_element_list_prev.EFFECTIVE_START_DATE,
X000aa_element_list_prev.INPUT_VALUE_ID,
X000aa_element_list_prev.SCREEN_ENTRY_VALUE,
X000aa_element_list_prev.ELEMENT_NAME,
SUBSTR(PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER,1,8) AS EMPL_NUMB
FROM
X000aa_element_list_prev
LEFT JOIN PEOPLE.PER_ALL_ASSIGNMENTS_F ON PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID = X000aa_element_list_prev.ASSIGNMENT_ID AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE <= Date('%PYEARE%') AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE >= Date('%PYEARE%')
WHERE
X000aa_element_list_prev.INPUT_VALUE_ID = 691 AND
X000aa_element_list_prev.EFFECTIVE_START_DATE <= Date('%PYEARE%') AND
X000aa_element_list_prev.EFFECTIVE_END_DATE >= Date('%PYEARE%')
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%PYEARE%", funcdate.prev_yearend())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Build the previous NWU TOTAL PACKAGE export file *************************
print("Build the previous nwu total package element export file...")
sr_file = "X001ax_element_package_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X001aa_element_package_prev.EMPL_NUMB,
X001aa_element_package_prev.EFFECTIVE_START_DATE AS DATE,
CAST(X001aa_element_package_prev.SCREEN_ENTRY_VALUE AS REAL) AS PACKAGE
FROM
X001aa_element_package_prev
ORDER BY
X001aa_element_package_prev.EMPL_NUMB
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export the data
print("Export previous packages...")
sr_filet = sr_file
sx_path = re_path + funcdate.prev_year() + "/"
sx_file = "Payroll_001ax_package_"
# sx_filet = sx_file + funcdate.prev_monthendfile()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_filet)
funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_file, s_head)
# funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_filet, s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
"""*************************************************************************
BALANCES CURRENT
*************************************************************************"""
print("---------- BALANCES CURRENT ----------")
# Build the balances list ******************************************************
print("Build the balances list...")
sr_file = "X000aa_balance_list_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
PAY_RUN_BALANCES_CURR.ASSIGNMENT_ID,
PAY_RUN_BALANCES_CURR.EFFECTIVE_DATE,
PAY_RUN_BALANCES_CURR.BALANCE_VALUE,
PAY_RUN_BALANCES_CURR.RUN_BALANCE_ID,
PAY_RUN_BALANCES_CURR.DEFINED_BALANCE_ID,
PAY_DEFINED_BALANCES.BALANCE_TYPE_ID,
PAY_BALANCE_TYPES.BALANCE_NAME,
PAY_BALANCE_TYPES.REPORTING_NAME,
PAY_BALANCE_TYPES.BALANCE_UOM,
PAY_BALANCE_TYPES.BALANCE_CATEGORY_ID
FROM
PAY_RUN_BALANCES_CURR
LEFT JOIN PAY_DEFINED_BALANCES ON PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID = PAY_RUN_BALANCES_CURR.DEFINED_BALANCE_ID
LEFT JOIN PAY_BALANCE_TYPES ON PAY_BALANCE_TYPES.BALANCE_TYPE_ID = PAY_DEFINED_BALANCES.BALANCE_TYPE_ID
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b>" + str(i) + "</b> Balances")
# Extract the NWU INCOME PER MONTH balance for export **************************
print("Extract the nwu total income balance...")
sr_file = "X002aa_balance_totalincome_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X000aa_balance_list_curr.ASSIGNMENT_ID,
X000aa_balance_list_curr.EFFECTIVE_DATE,
X000aa_balance_list_curr.DEFINED_BALANCE_ID,
X000aa_balance_list_curr.BALANCE_VALUE,
X000aa_balance_list_curr.BALANCE_NAME,
X000aa_balance_list_curr.REPORTING_NAME,
SUBSTR(PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER,1,8) AS EMPL_NUMB
FROM
X000aa_balance_list_curr
LEFT JOIN PEOPLE.PER_ALL_ASSIGNMENTS_F ON PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID = X000aa_balance_list_curr.ASSIGNMENT_ID AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE <= Date('%PMONTHEND%') AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE >= Date('%PMONTHEND%')
WHERE
X000aa_balance_list_curr.DEFINED_BALANCE_ID = 16264 AND
X000aa_balance_list_curr.EFFECTIVE_DATE = Date('%PMONTHEND%')
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%PMONTHEND%", funcdate.prev_monthend())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Build the NWU TOTAL INCOME export file ***************************************
print("Build the nwu total income balance export file...")
sr_file = "X002ax_balance_totalincome_curr"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X002aa_balance_totalincome_curr.EMPL_NUMB,
X002aa_balance_totalincome_curr.EFFECTIVE_DATE AS DATE,
CAST(X002aa_balance_totalincome_curr.BALANCE_VALUE AS REAL) AS INCOME
FROM
X002aa_balance_totalincome_curr
ORDER BY
X002aa_balance_totalincome_curr.EMPL_NUMB
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export the data
print("Export incomes...")
sr_filet = sr_file
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = "Payroll_002ax_income_total_"
sx_filet = sx_file + funcdate.prev_monthendfile()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_filet)
funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_file, s_head)
funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_filet, s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
"""*************************************************************************
BALANCES PREVIOUS
*************************************************************************"""
print("---------- BALANCES PREVIOUS ----------")
# Build the previous balances list *********************************************
print("Build the previous balances list...")
sr_file = "X000aa_balance_list_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
PAY_RUN_BALANCES_PREV.ASSIGNMENT_ID,
PAY_RUN_BALANCES_PREV.EFFECTIVE_DATE,
PAY_RUN_BALANCES_PREV.BALANCE_VALUE,
PAY_RUN_BALANCES_PREV.RUN_BALANCE_ID,
PAY_RUN_BALANCES_PREV.DEFINED_BALANCE_ID,
PAY_DEFINED_BALANCES.BALANCE_TYPE_ID,
PAY_BALANCE_TYPES.BALANCE_NAME,
PAY_BALANCE_TYPES.REPORTING_NAME,
PAY_BALANCE_TYPES.BALANCE_UOM,
PAY_BALANCE_TYPES.BALANCE_CATEGORY_ID
FROM
PAY_RUN_BALANCES_PREV
LEFT JOIN PAY_DEFINED_BALANCES ON PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID = PAY_RUN_BALANCES_PREV.DEFINED_BALANCE_ID
LEFT JOIN PAY_BALANCE_TYPES ON PAY_BALANCE_TYPES.BALANCE_TYPE_ID = PAY_DEFINED_BALANCES.BALANCE_TYPE_ID
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Extract the previous NWU INCOME PER MONTH balance for export *****************
print("Extract the previous nwu total income balance...")
sr_file = "X002aa_balance_totalincome_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X000aa_balance_list_prev.ASSIGNMENT_ID,
X000aa_balance_list_prev.EFFECTIVE_DATE,
X000aa_balance_list_prev.DEFINED_BALANCE_ID,
X000aa_balance_list_prev.BALANCE_VALUE,
X000aa_balance_list_prev.BALANCE_NAME,
X000aa_balance_list_prev.REPORTING_NAME,
SUBSTR(PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER,1,8) AS EMPL_NUMB
FROM
X000aa_balance_list_prev
LEFT JOIN PEOPLE.PER_ALL_ASSIGNMENTS_F ON PEOPLE.PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID = X000aa_balance_list_prev.ASSIGNMENT_ID AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE <= Date('%PYEARE%') AND
PEOPLE.PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE >= Date('%PYEARE%')
WHERE
X000aa_balance_list_prev.DEFINED_BALANCE_ID = 16264 AND
X000aa_balance_list_prev.EFFECTIVE_DATE = Date('%PYEARE%')
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%PYEARE%", funcdate.prev_yearend())
# print(s_sql)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Build the previous NWU TOTAL INCOME export file ******************************
print("Build the previous nwu total income balance export file...")
sr_file = "X002ax_balance_totalincome_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
X002aa_balance_totalincome_prev.EMPL_NUMB,
X002aa_balance_totalincome_prev.EFFECTIVE_DATE AS DATE,
CAST(X002aa_balance_totalincome_prev.BALANCE_VALUE AS REAL) AS INCOME
FROM
X002aa_balance_totalincome_prev
ORDER BY
X002aa_balance_totalincome_prev.EMPL_NUMB
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export the data
print("Export previous incomes...")
sr_filet = sr_file
sx_path = re_path + funcdate.prev_year() + "/"
sx_file = "Payroll_002ax_income_total_"
# sx_filet = sx_file + funcdate.prev_monthendfile()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_filet)
funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_file, s_head)
# funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_filet, s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
"""*************************************************************************
SECONDARY ASSIGNMENTS
*************************************************************************"""
print("---------- SECONDARY ASSIGNMENTS ----------")
# Build previous secondary assignments *************************************
print("Build previous secondary assignments...")
sr_file = "X000aa_sec_assignment_prev"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.ASSIGNMENT_EXTRA_INFO_ID,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.ASSIGNMENT_ID,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION1,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION2,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION3,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION4,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION5,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION6,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION7,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION8,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION9,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION10,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION11,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION12 AS DATE_FROM,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION13 AS DATE_TO,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION14,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION15,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION16,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION17,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION18,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION19,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION20,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION21,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION22,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION23,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION24,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION25,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION26,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION27,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION28,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION29,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.AEI_INFORMATION30,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.LAST_UPDATE_DATE,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.LAST_UPDATED_BY,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.LAST_UPDATE_LOGIN,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.CREATED_BY,
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC.CREATION_DATE
FROM
PEOPLE.PER_ASSIGNMENT_EXTRA_INFO_SEC
WHERE
DATE_FROM <= Date('2018-12-31') AND
DATE_TO >= Date('2018-12-31')
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute("DROP TABLE IF EXISTS X000aa_sec_assignment_prev")
s_sql = s_sql.replace("%PYEARE%", funcdate.prev_yearend())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
"""*****************************************************************************
End OF SCRIPT
*****************************************************************************"""
print("END OF SCRIPT")
funcfile.writelog("END OF SCRIPT")
# COMMIT DATA
so_conn.commit()
# CLOSE THE DATABASE CONNECTION
so_conn.close()
# CLOSE THE LOG WRITER
funcfile.writelog("-----------------------------")
funcfile.writelog("COMPLETED: B004_PAYROLL_LISTS")
return
def kfs_lists():
"""
Script to build standard KFS lists
:return: Nothing
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# DECLARE VARIABLES
l_debug: bool = False
ed_path = "S:/_external_data/" # external data path
so_file = "Kfs.sqlite" # Source database
so_path = "W:/Kfs/" # Source database path
l_vacuum = False # Vacuum database
# OPEN THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B002_KFS_LISTS")
funcfile.writelog("----------------------")
print("--------------")
print("B002_KFS_LISTS")
print("--------------")
# MESSAGE
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator", "<b>B002 Kfs lists</b>")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
print("OPEN THE DATABASES")
funcfile.writelog("OPEN THE DATABASES")
# OPEN THE WORKING DATABASE
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
# ATTACH DATA SOURCES
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
""" ****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
"""*****************************************************************************
IMPORT OWN LOOKUPS
*****************************************************************************"""
funcfile.writelog("Import own lookups...")
if l_debug:
print("Import own lookups...")
tb_name = "X000_Own_kfs_lookups"
so_curs.execute("DROP TABLE IF EXISTS " + tb_name)
so_curs.execute("CREATE TABLE " + tb_name + "(LOOKUP TEXT,LOOKUP_CODE TEXT,LOOKUP_DESCRIPTION TEXT)")
co = open(ed_path + "001_own_kfs_lookups.csv", newline=None)
co_reader = csv.reader(co)
for row in co_reader:
if row[0] == "LOOKUP":
continue
else:
s_cols = "INSERT INTO " + tb_name + " VALUES('" + row[0] + "','" + row[1] + "','" + row[2] + "')"
so_curs.execute(s_cols)
so_conn.commit()
# CLOSE THE TEXT FILE
co.close()
funcfile.writelog("%t IMPORT TABLE: " + tb_name)
""" ****************************************************************************
ORGANIZATION MASTER LIST
*****************************************************************************"""
print("ORGANIZATION MASTER LIST")
funcfile.writelog("ORGANIZATION MASTER LIST")
# BUILD ORGANIZATION LIST
print("Build organization...")
sr_file = "X000_Organization"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
ORG.FIN_COA_CD,
ORG.ORG_CD,
ORG.ORG_TYP_CD,
TYP.ORG_TYP_NM,
ORG.ORG_NM,
ORG.ORG_BEGIN_DT,
ORG.ORG_END_DT,
ORG.OBJ_ID,
ORG.VER_NBR,
ORG.ORG_MGR_UNVL_ID,
ORG.RC_CD,
ORG.ORG_PHYS_CMP_CD,
ORG.ORG_DFLT_ACCT_NBR,
ORG.ORG_LN1_ADDR,
ORG.ORG_LN2_ADDR,
ORG.ORG_CITY_NM,
ORG.ORG_STATE_CD,
ORG.ORG_ZIP_CD,
ORG.ORG_CNTRY_CD,
ORG.RPTS_TO_FIN_COA_CD,
ORG.RPTS_TO_ORG_CD,
ORG.ORG_ACTIVE_CD,
ORG.ORG_IN_FP_CD,
ORG.ORG_PLNT_ACCT_NBR,
ORG.CMP_PLNT_ACCT_NBR,
ORG.ORG_PLNT_COA_CD,
ORG.CMP_PLNT_COA_CD,
ORG.ORG_LVL
From
CA_ORG_T ORG Left Join
CA_ORG_TYPE_T TYP ON TYP.ORG_TYP_CD = ORG.ORG_TYP_CD
Order By
ORG.FIN_COA_CD,
ORG.ORG_LVL,
TYP.ORG_TYP_NM,
ORG.ORG_NM,
ORG.ORG_BEGIN_DT,
ORG.ORG_END_DT
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: Organization list")
# BUILD ORGANIZATION STRUCTURE
print("Build organization structure...")
sr_file = "X000_Organization_struct"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
ORS.FIN_COA_CD,
ORS.ORG_CD,
ORS.ORG_TYP_CD,
ORS.ORG_TYP_NM,
ORS.ORG_NM,
ORS.ORG_MGR_UNVL_ID,
ORS.ORG_LVL,
ORA.FIN_COA_CD AS FIN_COA_CD1,
ORA.ORG_CD AS ORG_CD1,
ORA.ORG_TYP_CD AS ORG_TYP_CD1,
ORA.ORG_TYP_NM AS ORG_TYP_NM1,
ORA.ORG_NM AS ORG_NM1,
ORA.ORG_MGR_UNVL_ID AS ORG_MGR_UNVL_ID1,
ORA.ORG_LVL AS ORG_LVL1,
ORB.FIN_COA_CD AS FIN_COA_CD2,
ORB.ORG_CD AS ORG_CD2,
ORB.ORG_TYP_CD AS ORG_TYP_CD2,
ORB.ORG_TYP_NM AS ORG_TYP_NM2,
ORB.ORG_NM AS ORG_NM2,
ORB.ORG_MGR_UNVL_ID AS ORG_MGR_UNVL_ID2,
ORB.ORG_LVL AS ORG_LVL2,
ORC.FIN_COA_CD AS FIN_COA_CD3,
ORC.ORG_CD AS ORG_CD3,
ORC.ORG_TYP_CD AS ORG_TYP_CD3,
ORC.ORG_TYP_NM AS ORG_TYP_NM3,
ORC.ORG_NM AS ORG_NM3,
ORC.ORG_MGR_UNVL_ID AS ORG_MGR_UNVL_ID3,
ORC.ORG_LVL AS ORG_LVL3,
ORD.FIN_COA_CD AS FIN_COA_CD4,
ORD.ORG_CD AS ORG_CD4,
ORD.ORG_TYP_CD AS ORG_TYP_CD4,
ORD.ORG_TYP_NM AS ORG_TYP_NM4,
ORD.ORG_NM AS ORG_NM4,
ORD.ORG_MGR_UNVL_ID AS ORG_MGR_UNVL_ID4,
ORD.ORG_LVL AS ORG_LVL4,
ORE.FIN_COA_CD AS FIN_COA_CD5,
ORE.ORG_CD AS ORG_CD5,
ORE.ORG_TYP_CD AS ORG_TYP_CD5,
ORE.ORG_TYP_NM AS ORG_TYP_NM5,
ORE.ORG_NM AS ORG_NM5,
ORE.ORG_MGR_UNVL_ID AS ORG_MGR_UNVL_ID5,
ORE.ORG_LVL AS ORG_LVL5,
ORF.FIN_COA_CD AS FIN_COA_CD6,
ORF.ORG_CD AS ORG_CD6,
ORF.ORG_TYP_CD AS ORG_TYP_CD6,
ORF.ORG_TYP_NM AS ORG_TYP_NM6,
ORF.ORG_NM AS ORG_NM6,
ORF.ORG_MGR_UNVL_ID AS ORG_MGR_UNVL_ID6,
ORF.ORG_LVL AS ORG_LVL6,
ORG.FIN_COA_CD AS FIN_COA_CD7,
ORG.ORG_CD AS ORG_CD7,
ORG.ORG_TYP_CD AS ORG_TYP_CD7,
ORG.ORG_TYP_NM AS ORG_TYP_NM7,
ORG.ORG_NM AS ORG_NM7,
ORG.ORG_MGR_UNVL_ID AS ORG_MGR_UNVL_ID7,
ORG.ORG_LVL AS ORG_LVL7
From
X000_Organization ORS Left Join
X000_Organization ORA ON ORA.FIN_COA_CD = ORS.RPTS_TO_FIN_COA_CD And
ORA.ORG_CD = ORS.RPTS_TO_ORG_CD Left Join
X000_Organization ORB ON ORB.FIN_COA_CD = ORA.RPTS_TO_FIN_COA_CD AND
ORB.ORG_CD = ORA.RPTS_TO_ORG_CD Left Join
X000_Organization ORC ON ORC.FIN_COA_CD = ORB.RPTS_TO_FIN_COA_CD AND
ORC.ORG_CD = ORB.RPTS_TO_ORG_CD Left Join
X000_Organization ORD ON ORD.FIN_COA_CD = ORC.RPTS_TO_FIN_COA_CD AND
ORD.ORG_CD = ORC.RPTS_TO_ORG_CD Left Join
X000_Organization ORE ON ORE.FIN_COA_CD = ORD.RPTS_TO_FIN_COA_CD AND
ORE.ORG_CD = ORD.RPTS_TO_ORG_CD Left Join
X000_Organization ORF ON ORF.FIN_COA_CD = ORE.RPTS_TO_FIN_COA_CD AND
ORF.ORG_CD = ORE.RPTS_TO_ORG_CD Left Join
X000_Organization ORG ON ORG.FIN_COA_CD = ORF.RPTS_TO_FIN_COA_CD AND
ORG.ORG_CD = ORF.RPTS_TO_ORG_CD
Where
ORS.ORG_BEGIN_DT >= Date("2018-01-01")
Order By
ORG.ORG_LVL,
ORG.ORG_NM
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: Organization structure")
""" ****************************************************************************
ACCOUNT MASTER LIST
*****************************************************************************"""
print("ACCOUNT MASTER LIST")
funcfile.writelog("ACCOUNT MASTER LIST")
# BUILD ACCOUNT LIST
print("Build account list...")
sr_file = "X000_Account"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
ACC.FIN_COA_CD,
ACC.ACCOUNT_NBR,
TYP.ACCT_TYP_NM,
ORG.ORG_NM,
ACC.ACCOUNT_NM,
ACC.ACCT_FSC_OFC_UID,
ACC.ACCT_SPVSR_UNVL_ID,
ACC.ACCT_MGR_UNVL_ID,
ACC.ORG_CD,
ACC.ACCT_TYP_CD,
ACC.ACCT_PHYS_CMP_CD,
ACC.ACCT_FRNG_BNFT_CD,
ACC.FIN_HGH_ED_FUNC_CD,
ACC.SUB_FUND_GRP_CD,
ACC.ACCT_RSTRC_STAT_CD,
ACC.ACCT_RSTRC_STAT_DT,
ACC.ACCT_CITY_NM,
ACC.ACCT_STATE_CD,
ACC.ACCT_STREET_ADDR,
ACC.ACCT_ZIP_CD,
ACC.RPTS_TO_FIN_COA_CD,
ACC.RPTS_TO_ACCT_NBR,
ACC.ACCT_CREATE_DT,
ACC.ACCT_EFFECT_DT,
ACC.ACCT_EXPIRATION_DT,
ACC.CONT_FIN_COA_CD,
ACC.CONT_ACCOUNT_NBR,
ACC.ACCT_CLOSED_IND,
ACC.OBJ_ID,
ACC.VER_NBR
From
CA_ACCOUNT_T ACC Left Join
X000_Organization ORG ON ORG.FIN_COA_CD = ACC.FIN_COA_CD AND ORG.ORG_CD = ACC.ORG_CD Left Join
CA_ACCOUNT_TYPE_T TYP ON TYP.ACCT_TYP_CD = ACC.ACCT_TYP_CD
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: Account list")
""" ****************************************************************************
VENDOR MASTER LIST
*****************************************************************************"""
print("VENDOR MASTER LIST")
funcfile.writelog("VENDOR MASTER LIST")
# BUILD TABLE WITH VENDOR REMITTANCE ADDRESSES
# BUILD TABLE WITH VENDOR PURCHASE ORDER ADDRESSES
# JOIN VENDOR RM AND PO ADDRESSES
# BUILD VENDOR BANK ACCOUNT TABLE
# BUILD CONTACT NAMES EMAIL PHONE MOBILE LIST
# BUILD VENDOR PHONE MOBILE LIST
# BUILD VENDOR MASTER CONTACT LIST
# BUILD VENDOR MASTER COMBINED CONTACT LIST
# UPDATE NUMBERS COLUMN WITH MOBILE
# UPDATE NUMBERS COLUMN WITH PHONEC
# UPDATE NUMBERS COLUMN WITH MOBILEC
# UPDATE NUMBERS REMOVE SPECIAL CHARACTERS FROM NUMBERS
# TRIM UNWANTED CHARACTERS
# BUILD VENDOR TABLE
# BUILD TABLE WITH VENDOR REMITTANCE ADDRESSES
print("Build vendor remittance addresses...")
sr_file = "X001aa_vendor_rm_address"
s_sql = "CREATE VIEW " + sr_file + " AS " + """
Select
CAST(TRIM(UPPER(ADDR.VNDR_HDR_GNRTD_ID))||'-'||TRIM(UPPER(ADDR.VNDR_DTL_ASND_ID)) AS
TEXT) VENDOR_ID,
ADDR.VNDR_ST_CD,
ADDR.VNDR_CNTRY_CD,
ADDR.VNDR_ADDR_EMAIL_ADDR,
ADDR.VNDR_B2B_URL_ADDR,
ADDR.VNDR_FAX_NBR,
TRIM(UPPER(ADDR.VNDR_DFLT_ADDR_IND))||'~'||
TRIM(UPPER(ADDR.VNDR_ATTN_NM))||'~'||
TRIM(UPPER(ADDR.VNDR_LN1_ADDR))||'~'||
TRIM(UPPER(ADDR.VNDR_LN2_ADDR))||'~'||
TRIM(UPPER(ADDR.VNDR_CTY_NM))||'~'||
TRIM(UPPER(ADDR.VNDR_ZIP_CD))||'~'||
TRIM(UPPER(ADDR.VNDR_CNTRY_CD))
ADDRESS_RM
From
PUR_VNDR_ADDR_T ADDR
Where
ADDR.VNDR_ADDR_TYP_CD = 'RM' And
ADDR.VNDR_DFLT_ADDR_IND = 'Y'
Group By
ADDR.VNDR_HDR_GNRTD_ID,
ADDR.VNDR_DTL_ASND_ID
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# BUILD TABLE WITH VENDOR PURCHASE ORDER ADDRESSES
print("Build vendor purchase order addresses...")
sr_file = "X001ab_vendor_po_address"
s_sql = "CREATE VIEW " + sr_file + " AS " + """
Select
CAST(TRIM(UPPER(ADDR.VNDR_HDR_GNRTD_ID))||'-'||TRIM(UPPER(ADDR.VNDR_DTL_ASND_ID))
AS TEXT) VENDOR_ID,
ADDR.VNDR_ST_CD,
ADDR.VNDR_CNTRY_CD,
ADDR.VNDR_ADDR_EMAIL_ADDR,
ADDR.VNDR_B2B_URL_ADDR,
ADDR.VNDR_FAX_NBR,
TRIM(UPPER(ADDR.VNDR_DFLT_ADDR_IND))||'~'||
TRIM(UPPER(ADDR.VNDR_ATTN_NM))||'~'||
TRIM(UPPER(ADDR.VNDR_LN1_ADDR))||'~'||
TRIM(UPPER(ADDR.VNDR_LN2_ADDR))||'~'||
TRIM(UPPER(ADDR.VNDR_CTY_NM))||'~'||
TRIM(UPPER(ADDR.VNDR_ZIP_CD))||'~'||
TRIM(UPPER(ADDR.VNDR_CNTRY_CD))
ADDRESS_PO
From
PUR_VNDR_ADDR_T ADDR
Where
ADDR.VNDR_ADDR_TYP_CD = 'PO' And
ADDR.VNDR_DFLT_ADDR_IND = 'Y'
Group By
ADDR.VNDR_HDR_GNRTD_ID,
ADDR.VNDR_DTL_ASND_ID
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# JOIN VENDOR RM AND PO ADDRESSES
print("Build vendor address master file...")
sr_file = "X001ac_vendor_address_comb"
s_sql = "CREATE VIEW " + sr_file + " AS " + """
Select
VENDOR.VNDR_ID As VENDOR_ID,
Case
When ADDRM.VNDR_ST_CD <> '' Then ADDRM.VNDR_ST_CD
Else ADDPO.VNDR_ST_CD
End as STATE_CD,
Case
When ADDRM.VNDR_CNTRY_CD <> '' Then ADDRM.VNDR_CNTRY_CD
Else ADDPO.VNDR_CNTRY_CD
End as COUNTRY_CD,
Case
When ADDRM.VNDR_ADDR_EMAIL_ADDR <> '' Then
Lower(ADDRM.VNDR_ADDR_EMAIL_ADDR)
Else Lower(ADDPO.VNDR_ADDR_EMAIL_ADDR)
End as EMAIL,
Case
When ADDRM.VNDR_B2B_URL_ADDR <> '' Then Lower(ADDRM.VNDR_B2B_URL_ADDR)
Else Lower(ADDPO.VNDR_B2B_URL_ADDR)
End as URL,
Case
When ADDRM.VNDR_FAX_NBR <> '' Then ADDRM.VNDR_FAX_NBR
Else ADDPO.VNDR_FAX_NBR
End as FAX,
Case
When ADDRM.ADDRESS_RM <> '' Then Upper(ADDRM.ADDRESS_RM)
Else Upper(ADDPO.ADDRESS_PO)
End as ADDRESS
From
PUR_VNDR_DTL_T VENDOR Left Join
X001aa_vendor_rm_address ADDRM On ADDRM.VENDOR_ID = VENDOR.VNDR_ID Left Join
X001ab_vendor_po_address ADDPO On ADDPO.VENDOR_ID = VENDOR.VNDR_ID
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# BUILD VENDOR BANK ACCOUNT TABLE
print("Build vendor bank account table...")
sr_file = "X001ad_vendor_bankacc"
s_sql = "CREATE VIEW " + sr_file + " AS " + """
Select Distinct
BANK.PAYEE_ID_NBR As VENDOR_ID,
STUD.BNK_ACCT_NBR As STUD_BANK,
STUDB.BNK_BRANCH_CD As STUD_BRANCH,
STUD.PAYEE_ID_TYP_CD As STUD_TYPE,
STUD.PAYEE_EMAIL_ADDR As STUD_MAIL,
VEND.BNK_ACCT_NBR As VEND_BANK,
VENDB.BNK_BRANCH_CD As VEND_BRANCH,
VEND.PAYEE_ID_TYP_CD As VEND_TYPE,
VEND.PAYEE_EMAIL_ADDR As VEND_MAIL,
EMPL.BNK_ACCT_NBR As EMPL_BANK,
EMPLB.BNK_BRANCH_CD As EMPL_BRANCH,
EMPL.PAYEE_ID_TYP_CD As EMPL_TYPE,
EMPL.PAYEE_EMAIL_ADDR As EMPL_MAIL
From
PDP_PAYEE_ACH_ACCT_T BANK
Left Join PDP_PAYEE_ACH_ACCT_T STUD On STUD.PAYEE_ID_NBR = BANK.PAYEE_ID_NBR And STUD.PAYEE_ID_TYP_CD = 'S' And
STUD.ROW_ACTV_IND = 'Y'
Left Join PDP_PAYEE_ACH_ACCT_EXT_T STUDB On STUDB.ACH_ACCT_GNRTD_ID = STUD.ACH_ACCT_GNRTD_ID
Left Join PDP_PAYEE_ACH_ACCT_T VEND On VEND.PAYEE_ID_NBR = BANK.PAYEE_ID_NBR And VEND.PAYEE_ID_TYP_CD = 'V' And
VEND.ROW_ACTV_IND = 'Y'
Left Join PDP_PAYEE_ACH_ACCT_EXT_T VENDB On VENDB.ACH_ACCT_GNRTD_ID = VEND.ACH_ACCT_GNRTD_ID
Left Join PDP_PAYEE_ACH_ACCT_T EMPL On EMPL.PAYEE_ID_NBR = BANK.PAYEE_ID_NBR And EMPL.PAYEE_ID_TYP_CD = 'E' And
EMPL.ROW_ACTV_IND = 'Y'
Left Join PDP_PAYEE_ACH_ACCT_EXT_T EMPLB On EMPLB.ACH_ACCT_GNRTD_ID = EMPL.ACH_ACCT_GNRTD_ID
Where
BANK.ROW_ACTV_IND = 'Y'
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# BUILD CONTACT NAMES EMAIL PHONE MOBILE LIST
print("Build contact email phone and mobile list...")
sr_file = "X001ae_vendor_contact"
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
s_sql = "Create View " + sr_file + " As" + """
Select
CONT.VNDR_HDR_GNRTD_ID,
CONT.VNDR_CNTCT_GNRTD_ID,
CONT.VNDR_CNTCT_TYP_CD As CONTACT_TYPE,
Trim(Upper(CONT.VNDR_CNTCT_NM)) As CONTACT,
Trim(Upper(CONT.VNDR_ATTN_NM)) As ATTENTION,
Lower(CONT.VNDR_CNTCT_EMAIL_ADDR) As EMAIL,
PHON.VNDR_PHN_NBR As PHONE,
MOBI.VNDR_PHN_NBR As MOBILE
From
PUR_VNDR_CNTCT_T CONT Left Join
PUR_VNDR_CNTCT_PHN_NBR_T PHON On PHON.VNDR_CNTCT_GNRTD_ID = CONT.VNDR_CNTCT_GNRTD_ID And PHON.VNDR_PHN_TYP_CD = 'PH' Left Join
PUR_VNDR_CNTCT_PHN_NBR_T MOBI On MOBI.VNDR_CNTCT_GNRTD_ID = CONT.VNDR_CNTCT_GNRTD_ID And MOBI.VNDR_PHN_TYP_CD = 'MB'
Group By
CONT.VNDR_HDR_GNRTD_ID
Order By
CONT.VNDR_HDR_GNRTD_ID,
CONTACT_TYPE
;"""
# s_sql = s_sql.replace("%PERIOD%", s_period)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# BUILD VENDOR PHONE MOBILE LIST
print("Build vendor phone and mobile list...")
sr_file = "X001af_vendor_phone"
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
s_sql = "Create View " + sr_file + " As" + """
Select
PHON.VNDR_HDR_GNRTD_ID,
PHON.VNDR_PHN_GNRTD_ID,
LAND.VNDR_PHN_NBR As PHONE,
MOBI.VNDR_PHN_NBR As MOBILE
From
PUR_VNDR_PHN_NBR_T PHON Left Join
PUR_VNDR_PHN_NBR_T LAND On LAND.VNDR_PHN_GNRTD_ID = PHON.VNDR_PHN_GNRTD_ID And LAND.VNDR_PHN_TYP_CD = 'PH' Left Join
PUR_VNDR_PHN_NBR_T MOBI On MOBI.VNDR_PHN_GNRTD_ID = PHON.VNDR_PHN_GNRTD_ID And MOBI.VNDR_PHN_TYP_CD = 'MB'
Group By
PHON.VNDR_HDR_GNRTD_ID
Order By
PHON.VNDR_HDR_GNRTD_ID
;"""
# s_sql = s_sql.replace("%PERIOD%", s_period)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# BUILD VENDOR MASTER CONTACT LIST
print("Build vendor master contact list...")
sr_file = "X001ag_contact_comb"
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
s_sql = "Create View " + sr_file + " As" + """
Select
VEND.VNDR_ID As VENDOR_ID,
PHON.PHONE,
PHON.MOBILE,
CONT.CONTACT,
CONT.ATTENTION,
CONT.EMAIL,
CONT.PHONE As PHONEC,
CONT.MOBILE As MOBILEC
From
PUR_VNDR_DTL_T VEND Left Join
X001af_vendor_phone PHON On PHON.VNDR_HDR_GNRTD_ID = VEND.VNDR_HDR_GNRTD_ID Left Join
X001ae_vendor_contact CONT On CONT.VNDR_HDR_GNRTD_ID = VEND.VNDR_HDR_GNRTD_ID
;"""
# s_sql = s_sql.replace("%PERIOD%", s_period)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# BUILD VENDOR MASTER COMBINED CONTACT LIST
print("Build vendor master combined contact list...")
sr_file = "X000_Contact"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "Create Table " + sr_file + " As" + """
Select
CONT.VENDOR_ID AS VENDOR_ID,
CONT.CONTACT,
CONT.ATTENTION,
CONT.EMAIL,
CONT.PHONE,
CONT.MOBILE,
CONT.PHONEC AS PHONEC,
CONT.MOBILEC AS MOBILEC,
CASE
WHEN CONT.PHONE != '' THEN Replace(Trim(CONT.PHONE),' ','') || '~'
ELSE ''
END As NUMBERS
From
X001ag_contact_comb CONT
Order By
VENDOR_ID
;"""
# s_sql = s_sql.replace("%PERIOD%", s_period)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# UPDATE NUMBERS COLUMN WITH MOBILE
print("Update numbers with mobile...")
so_curs.execute("Update X000_Contact " + """
Set NUMBERS =
Case
When Trim(MOBILE) != '' And Instr(NUMBERS, Replace(Trim(MOBILE), ' ', '')) != 0 THEN NUMBERS
When Trim(MOBILE) != '' THEN NUMBERS || Replace(Trim(MOBILE),' ','') || '~'
Else NUMBERS
End
;""")
# UPDATE NUMBERS COLUMN WITH PHONEC
print("Update numbers with phonec...")
so_curs.execute("Update X000_Contact " + """
Set NUMBERS =
Case
When Trim(PHONEC) != '' And Instr(NUMBERS, Replace(Trim(PHONEC), ' ', '')) != 0 THEN NUMBERS
When Trim(PHONEC) != '' THEN NUMBERS || Replace(Trim(PHONEC),' ','') || '~'
Else NUMBERS
End
;""")
# UPDATE NUMBERS COLUMN WITH MOBILEC
print("Update numbers with mobilec...")
so_curs.execute("Update X000_Contact " + """
Set NUMBERS =
Case
When Trim(MOBILEC) != '' And Instr(NUMBERS, Replace(Trim(MOBILEC), ' ', '')) != 0 THEN NUMBERS
When Trim(MOBILEC) != '' THEN NUMBERS || Replace(Trim(MOBILEC),' ','') || '~'
Else NUMBERS
End
;""")
# UPDATE NUMBERS REMOVE SPECIAL CHARACTERS FROM NUMBERS
for i in range(5):
print("Remove special characters...")
so_curs.execute("Update X000_Contact " + """
Set NUMBERS =
Case
When NUMBERS Like('%-%') Then Replace(NUMBERS, '-', '')
When NUMBERS Like('%(%') Then Replace(NUMBERS, '(', '')
When NUMBERS Like('%)%') Then Replace(NUMBERS, ')', '')
When NUMBERS Like('%*%') Then Replace(NUMBERS, '*', '')
When NUMBERS Like('%;%') Then Replace(NUMBERS, ';', '')
When NUMBERS Like('%.%') Then Replace(NUMBERS, '.', '')
When NUMBERS Like('%+27%') Then Replace(NUMBERS, '+27', '0')
When NUMBERS Like('%UNKNOWN%') Then Replace(NUMBERS, 'UNKNOWN'||'~', '')
When NUMBERS Like('%geennommerbeskikbaar%') Then Replace(NUMBERS, 'geennommerbeskikbaar'||'~', '')
When NUMBERS Like('%NONE%') Then Replace(NUMBERS, 'NONE'||'~', '')
When NUMBERS Like('%N/A%') Then Replace(NUMBERS, 'N/A'||'~', '')
When NUMBERS Like('%Fon:%') Then Replace(NUMBERS, 'Fon:', '')
When NUMBERS Like('%Tel:%') Then Replace(NUMBERS, 'Tel:', '')
When Trim(EMAIL) != '' And Instr(NUMBERS,EMAIL) > 0 THEN Replace(NUMBERS, EMAIL || '~', '')
When NUMBERS Like('%O%') Then Replace(NUMBERS, 'O', '0')
Else NUMBERS
End
;""")
# TRIM UNWANTED CHARACTERS
print("Trim unwanted characters...")
so_curs.execute("Update X000_Contact " + """
Set NUMBERS = Trim(NUMBERS,'~')
;""")
# BUILD VENDOR TABLE
print("Build vendor master file...")
sr_file = "X000_Vendor"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
DETAIL.VNDR_ID As VENDOR_ID,
UPPER(DETAIL.VNDR_NM) AS VNDR_NM,
DETAIL.VNDR_URL_ADDR,
HEADER.VNDR_TAX_NBR,
BANK.VEND_BANK,
BANK.VEND_BRANCH,
BANK.VEND_MAIL,
BANK.EMPL_BANK,
BANK.STUD_BANK,
CONT.NUMBERS,
ADDR.FAX,
ADDR.EMAIL,
CONT.CONTACT,
CONT.ATTENTION,
CONT.EMAIL AS EMAIL_CONTACT,
ADDR.ADDRESS,
ADDR.URL,
ADDR.STATE_CD,
ADDR.COUNTRY_CD,
HEADER.VNDR_TAX_TYP_CD,
HEADER.VNDR_TYP_CD,
DETAIL.VNDR_PMT_TERM_CD,
DETAIL.VNDR_SHP_TTL_CD,
DETAIL.VNDR_PARENT_IND,
DETAIL.VNDR_1ST_LST_NM_IND,
DETAIL.COLLECT_TAX_IND,
HEADER.VNDR_FRGN_IND,
DETAIL.VNDR_CNFM_IND,
DETAIL.VNDR_PRPYMT_IND,
DETAIL.VNDR_CCRD_IND,
DETAIL.DOBJ_MAINT_CD_ACTV_IND,
DETAIL.VNDR_INACTV_REAS_CD
From
PUR_VNDR_DTL_T DETAIL Left Join
PUR_VNDR_HDR_T HEADER On HEADER.VNDR_HDR_GNRTD_ID = DETAIL.VNDR_HDR_GNRTD_ID Left Join
X001ac_vendor_address_comb ADDR On ADDR.VENDOR_ID = DETAIL.VNDR_ID Left Join
X001ad_vendor_bankacc BANK On BANK.VENDOR_ID = DETAIL.VNDR_ID Left Join
X000_Contact CONT On CONT.VENDOR_ID = DETAIL.VNDR_ID
Order by
VNDR_NM,
VENDOR_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)
""" ****************************************************************************
DOCUMENT MASTER LIST
*****************************************************************************"""
print("DOCUMENTS MASTER LIST")
funcfile.writelog("DOCUMENTS MASTER LIST")
# BUILD DOCS MASTER LIST
print("Build docs master list...")
sr_file = "X000_Document"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
DOC.DOC_HDR_ID,
DOC.DOC_TYP_ID,
TYP.DOC_TYP_NM,
TYP.LBL
From
KREW_DOC_HDR_T DOC Inner Join
KREW_DOC_TYP_T TYP On TYP.DOC_TYP_ID = DOC.DOC_TYP_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)
# MESSAGE
if funcconf.l_mess_project:
sr_file = "X000_Account"
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator", "<b> " + str(i) + "</b> " + " Accounts")
sr_file = "X000_Vendor"
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator", "<b> " + str(i) + "</b> " + " Vendors")
""" ****************************************************************************
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: B002_KFS_LISTS")
return
def people01(so_conn, s_table, s_source, s_peri, s_mess, s_acti):
"""
Function to build PEOPLE table from different assignments
:param so_conn: Connection string
:param s_table: Table name to create
:param s_source: Table source
:param s_peri: For which period
:param s_mess: Print and log message
:param s_acti: Should list include only active people = Y (or active assignments = N)
:return: int: Number of people
"""
# Print and connect
print(s_mess)
so_curs = so_conn.cursor()
# Use assignment or people date
if s_acti == "Y":
s_wher = "ASSI.EMP_ACTIVE = 'Y'"
else:
s_wher = "ASSI.ASS_ACTIVE = 'Y'"
# Create the people table
s_sql = "CREATE TABLE " + s_table + " As " + """
Select
ASSI.EMPLOYEE_NUMBER,
ASSI.ASS_ID,
ASSI.PERSON_ID,
ASSI.ASS_NUMB,
X000_PER_ALL_PEOPLE.PARTY_ID,
Upper(ASSI.FULL_NAME) As FULL_NAME,
'' As NAME_LIST,
'' As NAME_ADDR,
Upper(ASSI.KNOWN_NAME) As KNOWN_NAME,
CASE
WHEN ORG_NAME IS NULL THEN OE_CODE||': '||POSITION_NAME
ELSE ORG_NAME||': '||POSITION_NAME
END AS POSITION_FULL,
ASSI.DATE_OF_BIRTH,
Upper(X000_PER_ALL_PEOPLE.NATIONALITY) As NATIONALITY,
Upper(X000_PER_ALL_PEOPLE.NATIONALITY_NAME) As NATIONALITY_NAME,
X000_PER_ALL_PEOPLE.NATIONAL_IDENTIFIER As IDNO,
Upper(X000_PER_ALL_PEOPLE.PER_INFORMATION2) As PASSPORT,
Upper(X000_PER_ALL_PEOPLE.PER_INFORMATION3) As PERMIT,
X000_PER_ALL_PEOPLE.PER_INFORMATION8 As PERMIT_EXPIRE,
X000_PER_ALL_PEOPLE.TAX_NUMBER,
Case
When X000_PER_ALL_PEOPLE.SEX = 'F' Then 'FEMALE'
When X000_PER_ALL_PEOPLE.SEX = 'M' Then 'MALE'
Else 'OTHER'
End As SEX,
X000_PER_ALL_PEOPLE.MARITAL_STATUS,
X000_PER_ALL_PEOPLE.REGISTERED_DISABLED_FLAG As DISABLED,
X000_PER_ALL_PEOPLE.RACE_CODE,
Upper(X000_PER_ALL_PEOPLE.RACE_DESC) As RACE_DESC,
X000_PER_ALL_PEOPLE.LANG_CODE,
Upper(X000_PER_ALL_PEOPLE.LANG_DESC) As LANG_DESC,
X000_PER_ALL_PEOPLE.INT_MAIL,
Lower(X000_PER_ALL_PEOPLE.EMAIL_ADDRESS) As EMAIL_ADDRESS,
X000_PER_ALL_PEOPLE.CURRENT_EMPLOYEE_FLAG As CURR_EMPL_FLAG,
X000_PER_ALL_PEOPLE.USER_PERSON_TYPE,
ASSI.ASS_START,
ASSI.ASS_END,
ASSI.EMP_START,
ASSI.EMP_END,
ASSI.LEAVING_REASON,
Upper(ASSI.LEAVE_REASON_DESCRIP) As LEAVE_REASON_DESCRIP,
Upper(ASSI.LOCATION_DESCRIPTION) As LOCATION_DESCRIPTION,
Upper(ASSI.ORG_TYPE_DESC) As ORG_TYPE_DESC,
Upper(ASSI.OE_CODE) As OE_CODE,
Upper(ASSI.ORG_NAME) As ORG_NAME,
ASSI.PRIMARY_FLAG,
Case
When Upper(ASSI.ACAD_SUPP) = 'ACADEMIC' Then Upper(ASSI.ACAD_SUPP)
When Upper(ASSI.ACAD_SUPP) = 'SUPPORT' Then Upper(ASSI.ACAD_SUPP)
Else 'OTHER'
End as ACAD_SUPP,
Upper(ASSI.FACULTY) As FACULTY,
Upper(ASSI.DIVISION) As DIVISION,
Case
When EMPLOYMENT_CATEGORY = 'P' Then 'PERMANENT'
When EMPLOYMENT_CATEGORY = 'T' Then 'TEMPORARY'
Else 'OTHER'
End As EMPLOYMENT_CATEGORY,
ASSI.ASS_WEEK_LEN,
ASSI.LEAVE_CODE,
ASSI.GRADE,
Upper(ASSI.GRADE_NAME) As GRADE_NAME,
ASSI.GRADE_CALC,
ASSI.POSITION,
Upper(ASSI.POSITION_NAME) As POSITION_NAME,
Upper(ASSI.JOB_NAME) As JOB_NAME,
Upper(ASSI.JOB_SEGMENT_NAME) As JOB_SEGMENT_NAME,
ASSI.SUPERVISOR,
X000_PER_ALL_PEOPLE.TITLE_FULL,
X000_PER_ALL_PEOPLE.FIRST_NAME,
X000_PER_ALL_PEOPLE.MIDDLE_NAMES,
X000_PER_ALL_PEOPLE.LAST_NAME,
X000_PHONE_WORK_%PERIOD%_LIST.PHONE_WORK,
X000_PHONE_MOBI_%PERIOD%_LIST.PHONE_MOBI,
X000_PHONE_HOME_%PERIOD%_LIST.PHONE_HOME,
X000_ADDRESS_SARS.ADDRESS_SARS,
X000_ADDRESS_POST.ADDRESS_POST,
X000_ADDRESS_HOME.ADDRESS_HOME,
X000_ADDRESS_OTHE.ADDRESS_OTHE,
ASSI.COUNT_POS,
ASSI.COUNT_ASS,
ASSI.COUNT_PEO,
ASSI.DATE_ASS_LOOKUP,
ASSI.ASS_ACTIVE,
ASSI.DATE_EMP_LOOKUP,
ASSI.EMP_ACTIVE,
ASSI.MAILTO,
PER_PAY_PROPOSALS.PROPOSED_SALARY_N,
Upper(X000_PER_PEOPLE_TYPES.USER_PERSON_TYPE) As PERSON_TYPE,
Upper(ASSI.ACC_TYPE) As ACC_TYPE,
Upper(ASSI.ACC_BRANCH) As ACC_BRANCH,
ASSI.ACC_NUMBER,
Upper(ASSI.ACC_RELATION) As ACC_RELATION,
ASSI.ACC_SARS,
ASSI.SEC_FULLPART_FLAG
FROM
%SOURCET% ASSI
LEFT JOIN X000_PER_ALL_PEOPLE ON X000_PER_ALL_PEOPLE.PERSON_ID = ASSI.PERSON_ID AND
X000_PER_ALL_PEOPLE.EFFECTIVE_START_DATE <= ASSI.DATE_EMP_LOOKUP AND
X000_PER_ALL_PEOPLE.EFFECTIVE_END_DATE >= ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_PHONE_WORK_%PERIOD%_LIST ON X000_PHONE_WORK_%PERIOD%_LIST.PERSON_ID = ASSI.PERSON_ID
LEFT JOIN X000_PHONE_MOBI_%PERIOD%_LIST ON X000_PHONE_MOBI_%PERIOD%_LIST.PERSON_ID = ASSI.PERSON_ID
LEFT JOIN X000_PHONE_HOME_%PERIOD%_LIST ON X000_PHONE_HOME_%PERIOD%_LIST.PERSON_ID = ASSI.PERSON_ID
LEFT JOIN X000_ADDRESS_SARS ON X000_ADDRESS_SARS.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_SARS.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND X000_ADDRESS_SARS.DATE_TO >=
ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_ADDRESS_POST ON X000_ADDRESS_POST.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_POST.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND X000_ADDRESS_POST.DATE_TO >=
ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_ADDRESS_HOME ON X000_ADDRESS_HOME.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_HOME.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND X000_ADDRESS_HOME.DATE_TO >=
ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_ADDRESS_OTHE ON X000_ADDRESS_OTHE.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_OTHE.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND
X000_ADDRESS_OTHE.DATE_TO >= ASSI.DATE_EMP_LOOKUP
LEFT JOIN PER_PAY_PROPOSALS ON PER_PAY_PROPOSALS.ASSIGNMENT_ID = ASSI.ASS_ID AND
PER_PAY_PROPOSALS.CHANGE_DATE <= ASSI.DATE_EMP_LOOKUP AND
PER_PAY_PROPOSALS.DATE_TO >= ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_PER_PEOPLE_TYPES ON X000_PER_PEOPLE_TYPES.PERSON_ID = ASSI.PERSON_ID AND
X000_PER_PEOPLE_TYPES.EFFECTIVE_START_DATE <= ASSI.DATE_EMP_LOOKUP AND
X000_PER_PEOPLE_TYPES.EFFECTIVE_END_DATE >= ASSI.DATE_EMP_LOOKUP
WHERE
""" + s_wher + """
GROUP BY
ASSI.EMPLOYEE_NUMBER
"""
so_curs.execute("DROP TABLE IF EXISTS " + s_table)
s_sql = s_sql.replace("%SOURCET%", s_source)
s_sql = s_sql.replace("%PERIOD%", s_peri)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + s_table)
# Add column initials
if "INITIALS" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table +
" ADD COLUMN INITIALS TEXT;")
s_sql = "UPDATE " + s_table + """
SET INITIALS =
CASE
WHEN INSTR(MIDDLE_NAMES,' ') > 1
THEN SUBSTR(FIRST_NAME,1,1) ||
SUBSTR(MIDDLE_NAMES,1,1) ||
TRIM(SUBSTR(MIDDLE_NAMES,INSTR(MIDDLE_NAMES,' '),2))
WHEN LENGTH(MIDDLE_NAMES) > 0 THEN
SUBSTR(FIRST_NAME,1,1) || SUBSTR(MIDDLE_NAMES,1,1)
ELSE SUBSTR(FIRST_NAME,1,1)
END
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: INITIALS")
so_curs.execute(
"UPDATE " + s_table +
" SET NAME_LIST = LAST_NAME||' '||TITLE_FULL||' '||INITIALS;")
so_conn.commit()
so_curs.execute(
"UPDATE " + s_table +
" SET NAME_ADDR = TITLE_FULL||' '||INITIALS||' '||LAST_NAME;")
so_conn.commit()
# Add column age
if "AGE" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table + " ADD COLUMN AGE INT;")
s_sql = "UPDATE " + s_table + """
SET AGE = strftime('%Y', 'now') - strftime('%Y', DATE_OF_BIRTH)
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: AGE")
# Add column month
if "MONTH" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table + " ADD COLUMN MONTH INT;")
s_sql = "UPDATE " + s_table + """
SET MONTH = cast(strftime('%m', DATE_OF_BIRTH) As int)
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: MONTH")
# Add column day
if "DAY" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table + " ADD COLUMN DAY INT;")
s_sql = "UPDATE " + s_table + """
SET DAY = cast(strftime('%d', DATE_OF_BIRTH) As int)
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: DAY")
return funcsys.tablerowcount(so_curs, s_table)
def 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 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
Where
FIND.DECLARED = 'PENDING' And
FIND.SUPERVISOR Is Not Null And
FIND.DAYS_DECLARED > 30
Order By
FIND.SUPERVISOR,
FIND.EMPLOYEE
;"""
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
s_sql = s_sql.replace("%FILEN%", "a_" + s_file_name)
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) + " " + s_finding +
" finding(s)")
# GET PREVIOUS FINDINGS
if i_finding_before > 0:
functest.get_previous_finding(so_curs, ed_path, s_report_file, s_finding,
"TTTTT")
so_conn.commit()
# SET PREVIOUS FINDINGS
if i_finding_before > 0:
functest.set_previous_finding(so_curs)
so_conn.commit()
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 get_previous_finding(o_cursor, s_path, s_source, s_key, s_format="ITTTT"):
"""
Function to fetch previous findings
:param o_cursor: Database cursor
:param s_path: Source file path
:param s_source: Source file name
:param s_key: Source file key
:param s_format: Source file format
:return: Number of records (findings)
"""
if l_debug:
print("Import previously reported findings...")
# DECLARE / BUILD VARIABLES
s_key = s_key.lower()
s_formatt: str = "(PROCESS TEXT, "
for i in range(5):
if l_debug:
print(s_format[i:i + 1])
if s_format[i:i + 1] == "I":
s_formatt += "FIELD" + str(i + 1) + " INT, "
elif s_format[i:i + 1] == "R":
s_formatt += "FIELD" + str(i + 1) + " REAL, "
else:
s_formatt += "FIELD" + str(i + 1) + " TEXT, "
s_formatt += "DATE_REPORTED TEXT, DATE_RETEST TEXT, REMARK TEXT)"
if l_debug:
print(s_formatt)
sr_file = "Z001aa_getprev"
o_cursor.execute("DROP TABLE IF EXISTS " + sr_file)
o_cursor.execute("CREATE TABLE " + sr_file + s_formatt)
co = open(s_path + s_source, "r")
co_reader = csv.reader(co)
# READ THE TEXT DATA INTO THE TABLE
for row in co_reader:
# Populate the column variables
if row[0] == "PROCESS":
continue
elif row[0] != s_key:
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] + "')"
if l_debug:
print(s_cols)
o_cursor.execute(s_cols)
# ClOSE THE SOURCE FILE
co.close()
funcfile.writelog("%t IMPORT TABLE: " + s_path + s_source + "(" + sr_file +
")")
return funcsys.tablerowcount(o_cursor, sr_file)
def people_list_masterfile():
"""
Script to build PEOPLE master file lists
:return: Nothing
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# DECLARE VARIABLES
so_path = "W:/People/" # Source database path
so_file = "People_list_masterfile.sqlite" # Source database
# OPEN THE LOG
print("---------------------------")
print("C003_PEOPLE_LIST_MASTERFILE")
print("---------------------------")
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C003_PEOPLE_LIST_MASTERFILE")
funcfile.writelog("-----------------------------------")
# MESSAGE
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator", "<b>C003 People lists</b>")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
print("OPEN THE DATABASES")
funcfile.writelog("OPEN THE DATABASES")
# OPEN THE WORKING DATABASE
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
# ATTACH DATA SOURCES
so_curs.execute("ATTACH DATABASE '" + so_path +
"People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
""" ****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
""" ****************************************************************************
LIST ENTRY EXIT DATES MASTER
*****************************************************************************"""
print("ENTRY EXIT DATES MASTER")
funcfile.writelog("ENTRY EXIT DATES MASTER")
# BUILD PEOPLE START AND END DATE MASTER TABLE
print("Build table for entry date analysis...")
sr_file: str = "X001_People_start_end_master"
s_sql = "CREATE TABLE " + sr_file + " As " + """
Select
PEOP.EMPLOYEE_NUMBER AS EMP,
PEOP.NAME_LIST AS NAME,
CASE
WHEN PEOP.NATIONALITY_NAME = 'SOUTH AFRICA' THEN PEOP.NATIONALITY_NAME
ELSE 'FOREIGN'
END AS NATIONALITY,
PEOP.SEX As GENDER,
PEOP.RACE_DESC AS RACE,
'START' As DATE_TYPE,
PEOP.EMP_START As DATE,
'' As LEAVING_REASON,
'' As LEAVE_REASON_DESCRIP,
PEOP.LOCATION_DESCRIPTION AS CAMPUS,
PEOP.ACAD_SUPP,
CASE
WHEN PEOP.FACULTY <> '' THEN PEOP.FACULTY
ELSE 'SUPPORT'
END AS FACULTY,
PEOP.EMPLOYMENT_CATEGORY AS PERM_TEMP,
PEOP.DIVISION,
PEOP.GRADE,
Substr(PEOP.GRADE_CALC,1,3) As GRADE_CALC,
PEOP.POSITION_NAME,
PEOP.JOB_NAME,
PEOP.PERSON_TYPE,
Strftime('%m', PEOP.EMP_START) As MONTH,
Cast(1 As Int) As COUNT
From
PEOPLE.X002_PEOPLE_CURR_YEAR PEOP
Where
Substr(PEOP.LEAVE_REASON_DESCRIP,1,6) <> 'AD HOC' And
Substr(PEOP.PERSON_TYPE,1,6) <> 'AD HOC' And
Strftime('%Y', PEOP.EMP_START) = '%CYEAR%'
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%CYEAR%", funcdate.cur_year())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: X001_People_start_end_master")
# BUILD PEOPLE START AND END DATE MASTER TABLE
print("Insert into table exit date analysis...")
sr_file: str = "X001_People_start_end_master"
s_sql = "INSERT INTO " + sr_file + " " + """
Select
PEOP.EMPLOYEE_NUMBER AS EMP,
PEOP.NAME_LIST AS NAME,
CASE
WHEN PEOP.NATIONALITY_NAME = 'SOUTH AFRICA' THEN PEOP.NATIONALITY_NAME
ELSE 'FOREIGN'
END AS NATIONALITY,
PEOP.SEX AS GENDER,
PEOP.RACE_DESC AS RACE,
'END' As DATE_TYPE,
PEOP.EMP_END As DATE,
PEOP.LEAVING_REASON,
PEOP.LEAVE_REASON_DESCRIP,
PEOP.LOCATION_DESCRIPTION AS CAMPUS,
PEOP.ACAD_SUPP,
CASE
WHEN PEOP.FACULTY <> '' THEN PEOP.FACULTY
ELSE 'SUPPORT'
END AS FACULTY,
PEOP.EMPLOYMENT_CATEGORY AS PERM_TEMP,
PEOP.DIVISION,
PEOP.GRADE,
Substr(PEOP.GRADE_CALC,1,3) As GRADE_CALC,
PEOP.POSITION_NAME,
PEOP.JOB_NAME,
PEOP.PERSON_TYPE,
Strftime('%m', PEOP.EMP_END) As MONTH,
Cast(-1 As Int) As COUNT
From
PEOPLE.X002_PEOPLE_CURR_YEAR PEOP
Where
Substr(PEOP.LEAVE_REASON_DESCRIP,1,6) <> 'AD HOC' And
Substr(PEOP.PERSON_TYPE,1,6) <> 'AD HOC' And
Strftime('%Y', PEOP.EMP_END) = '%CYEAR%' And
PEOP.EMP_END < Date('%TODAY%')
"""
s_sql = s_sql.replace("%CYEAR%", funcdate.cur_year())
s_sql = s_sql.replace("%TODAY%", funcdate.cur_monthend())
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: X001_People_start_end_master")
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b>" + str(i) + "</b> Exits")
""" ****************************************************************************
LIST AGE MASTER
*****************************************************************************"""
print("AGE MASTER")
funcfile.writelog("AGE MASTER")
# BUILD PEOPLE START AND END DATE MASTER TABLE
print("Build table for age analysis...")
sr_file: str = "X002_People_age_master"
s_sql = "CREATE TABLE " + sr_file + " As " + """
Select
PEOP.EMPLOYEE_NUMBER AS EMP,
PEOP.NAME_ADDR AS NAME,
PEOP.EMPLOYMENT_CATEGORY AS PERM_TEMP,
PEOP.ACAD_SUPP,
PEOP.SEX As GENDER,
PEOP.RACE_DESC AS RACE,
PEOP.PERSON_TYPE,
PEOP.GRADE,
Substr(PEOP.GRADE_CALC,1,3) As GRADE_CALC,
CASE
WHEN PEOP.NATIONALITY_NAME = 'SOUTH AFRICA' THEN PEOP.NATIONALITY_NAME
ELSE 'FOREIGN'
END AS NATIONALITY,
PEOP.NATIONALITY_NAME,
PEOP.LOCATION_DESCRIPTION AS CAMPUS,
CASE
WHEN PEOP.FACULTY <> '' THEN PEOP.FACULTY
ELSE 'SUPPORT'
END AS FACULTY,
CASE
WHEN PEOP.FACULTY <> '' THEN PEOP.FACULTY
WHEN PEOP.DIVISION <> '' THEN PEOP.DIVISION
ELSE 'SUPPORT'
END AS WORKPLACE,
PEOP.DIVISION,
PEOP.ORG_NAME,
PEOP.POSITION_NAME,
PEOP.JOB_NAME,
PEOP.DATE_OF_BIRTH,
PEOP.AGE,
CASE
When PEOP.AGE >= 10 And PEOP.AGE <= 20 Then '00-20'
When PEOP.AGE >= 21 And PEOP.AGE <= 25 Then '21-25'
When PEOP.AGE >= 26 And PEOP.AGE <= 30 Then '26-30'
When PEOP.AGE >= 31 And PEOP.AGE <= 35 Then '31-35'
When PEOP.AGE >= 36 And PEOP.AGE <= 40 Then '36-40'
When PEOP.AGE >= 41 And PEOP.AGE <= 45 Then '41-45'
When PEOP.AGE >= 46 And PEOP.AGE <= 50 Then '46-50'
When PEOP.AGE >= 51 And PEOP.AGE <= 55 Then '51-55'
When PEOP.AGE >= 56 And PEOP.AGE <= 60 Then '56-60'
When PEOP.AGE >= 61 And PEOP.AGE <= 65 Then '61-65'
When PEOP.AGE >= 66 And PEOP.AGE <= 70 Then '66-70'
Else '71-99'
END As AGE_GROUP,
Cast(1 As Int) As COUNT,
PEOP.EMAIL_ADDRESS,
CASE
When PEOP.KNOWN_NAME = '' Then 'COLLEAGUE'
Else Replace(Lower(PEOP.TITLE_FULL),'.','')||' '||PEOP.KNOWN_NAME
END AS KNOWN_NAME
From
PEOPLE.X002_PEOPLE_CURR PEOP
Where
Substr(PEOP.PERSON_TYPE,1,6) <> 'AD HOC'
Order By
PEOP.AGE Desc
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: X002_People_age_master")
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b>" + str(i) + "</b> Age groups")
""" ****************************************************************************
END OF SCRIPT
*****************************************************************************"""
print("END OF SCRIPT")
funcfile.writelog("END OF SCRIPT")
# CLOSE THE WORKING DATABASE
so_conn.close()
# CLOSE THE LOG
funcfile.writelog("--------------------------------------")
funcfile.writelog("COMPLETED: C003_PEOPLE_LIST_MASTERFILE")
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 log_capture(s_date=funcdate.yesterday(), l_history=False):
"""
Import and report project log file
:param s_date: Log date to import (default = Yesterday)
:param l_history: Add log to log history (default = False)
:return: Nothing
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# DECLARE VARIABLES
so_path = "W:/Admin/" # Source database path
so_file = "Admin.sqlite" # Source database
ld_path = "S:/Logs/"
# DECLARE SCRIPT VARIABLES
l_debug: bool = False
l_record: bool = True
s_date_file: str = s_date.replace("-", "")
s_time: str = ""
s_script: str = ""
s_base: str = ""
s_action: str = ""
s_object: str = ""
l_vacuum: bool = False
# SCRIPT LOG FILE
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: A002_LOG")
funcfile.writelog("----------------")
if l_debug:
print("--------")
print("A002_LOG")
print("--------")
# MESSAGE
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator", "<b>A002 Log history</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)
"""*****************************************************************************
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")
# IMPORT THE LOG FILE
if l_debug:
print("Import log file...")
sr_file = "X001aa_import_log"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(
"CREATE TABLE " + sr_file + """
(LOG TEXT,
LOG_DATE TEXT,
LOG_TIME TEXT,
SCRIPT TEXT,
DATABASE TEXT,
ACTION TEXT,
OBJECT TEXT)
""")
# OPEN THE LOG TEXT FILE
if l_debug:
print(ld_path + "Python_log_" + s_date_file + ".txt")
co = open(ld_path + "Python_log_" + s_date_file + ".txt", "r", encoding='utf8')
co_reader = csv.reader(co)
# READ THE LOG
for row in co_reader:
# row[0] = Log record
# 1 = Log date s_date
# 2 = Log time s_time
# 3 = Script s_script
# 4 = Database s_base
# 5 = Action s_action
# 6 = Object s_object
# TEST IF INDEX VALID
if l_debug:
print(row)
try:
s = row[0]
except IndexError:
continue
if l_debug:
print(row)
# UNRAVEL THE LOF RECORD LINE
s_data = row[0].replace("'", "")
s_data = s_data.replace('"', "")
s_data = s_data.replace(",", "")
if s_data == "":
l_record = False
elif s_data.find("ERROR:") >= 0:
s_time = s_data[0:8]
s_action = "ERROR"
s_object = s_data[16:100].upper()
elif s_data[0:10] == s_date:
l_record = False
elif s_data[0:1] == "-":
l_record = False
elif s_data.find(":") == 2:
s_time = s_data[0:8]
if s_data.find(":", 9) > 0:
s_action = s_data[9:s_data.find(":", 9)].upper()
s_object = s_data[s_data.find(":", 9) + 2:100].upper()
elif s_data.find("SCRIPT:") == 0:
if s_data.find(":", 8) > 0:
s_script = s_data[8:s_data.find(":", 8)].upper()
s_action = "SCRIPT"
s_object = s_data[s_data.find(":", 8) + 2:100].upper()
else:
s_script = s_data[8:100].upper()
s_action = "SCRIPT"
s_object = s_data[8:100].upper()
elif s_data.find("OPEN DATABASE:") == 0:
s_base = s_data[15:100].upper()
s_action = "OPEN DATABASE"
s_object = s_base
elif s_data.find(":", 9) > 0:
s_action = s_data[0:s_data.find(":")].upper()
s_object = s_data[s_data.find(":") + 2:100].upper()
else:
s_action = "HEADER"
s_object = s_data[0:100].upper()
# SAVE THE RECORD
if l_record:
s_cols = "INSERT INTO " + sr_file + " VALUES(" \
"'" + s_data + "'," \
"'" + s_date + "'," \
"'" + s_time + "'," \
"'" + s_script + "'," \
"'" + s_base + "'," \
"'" + s_action + "'," \
"'" + s_object + "'" \
")"
# SHOW SQL SCRIPT BEFORE EXECUTION
if l_debug:
print(s_cols)
so_curs.execute(s_cols)
# RESET VARIABLES
l_record = True
s_action = ""
s_object = ""
so_conn.commit()
# BUILD THE LOG TABLE
if l_debug:
print("Build the log table...")
sr_file = "X001ab_sort_log"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
Rowid As ID,
Rowid + 1 As ID2,
LOG.LOG_DATE,
LOG.LOG_TIME,
LOG.SCRIPT,
LOG."DATABASE",
LOG."ACTION",
LOG.OBJECT
From
X001aa_import_log LOG
;"""
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 TO ADMIN
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator", " <b> " + str(i) + " " + s_date_file + "</b> Records")
# CLOSE THE LOG TEXT FILE
co.close()
funcfile.writelog("%t IMPORT TABLE: " + ld_path + s_date_file + " (" + sr_file + ")")
# CALCULATE TIMES
if l_debug:
print("Calculate times...")
sr_file = "X001ac_calc_time"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
a.LOG_DATE,
a.LOG_TIME,
Cast(strftime('%s',b.LOG_DATE||' '||b.LOG_TIME) - strftime('%s',a.LOG_DATE||' '||a.LOG_TIME) As INT) As LOG_SECOND,
time(strftime('%s',b.LOG_DATE||' '||b.LOG_TIME) - strftime('%s',a.LOG_DATE||' '||a.LOG_TIME), 'unixepoch') As
LOG_ELAPSED,
a.SCRIPT,
a.DATABASE,
a.ACTION,
a.OBJECT
FROM
X001ab_sort_log a Left Join
X001ab_sort_log b On b.ID = a.ID2
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# ISOLATE AUTO TIMES
if l_debug:
print("Isolate auto times...")
sr_file = "X001ad_auto_time"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
LOG.*
FROM
X001ac_calc_time LOG
WHERE
(LOG.LOG_SECOND <= 3600 And LOG.LOG_TIME >= '20:00:00' And LOG.LOG_TIME <= '23:59:59') Or
(LOG.LOG_SECOND <= 3600 And LOG.LOG_TIME >= '02:00:00' And LOG.LOG_TIME <= '06:59:59')
;"""
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 CURRENT LOG TO HISTORY
if l_history:
sr_file = "X002aa_log_history"
so_curs.execute(
"CREATE TABLE IF NOT EXISTS " + sr_file + """
(LOG_DATE TEXT,
LOG_TIME TEXT,
LOG_SECOND INT,
LOG_ELAPSED TEXT,
SCRIPT TEXT,
DATABASE TEXT,
ACTION TEXT,
OBJECT TEXT)
""")
if l_debug:
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
def studentlist(so_conn, re_path, s_period='curr', l_export=False):
"""
Script to build STUDENT list
:param so_conn: Database connection
:param re_path: Results path
:param s_period: Calculation period
:param s_year: Financial year
:param l_export: Export results
:return: Nothing
"""
# DECLARE VARIABLES
if s_period == 'prev':
s_year = funcdate.prev_year()
else:
s_year = funcdate.cur_year()
so_curs = so_conn.cursor()
"""*************************************************************************
BUILD STUDENTS
*************************************************************************"""
print("BUILD " + s_year + " STUDENTS")
funcfile.writelog("BUILD " + s_year + " YEAR STUDENTS")
# BUILD STUDENT LIST
print("Build student list...")
sr_file = "X001_Student"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
STUD.KSTUDBUSENTID,
STUD.DATEQUALLEVELSTARTED,
STUD.DATEENROL,
STUD.STARTDATE,
STUD.ENDDATE,
STUD.MARKSFINALISEDDATE,
RESU.RESULTISSUEDATE,
RESU.RESULTPASSDATE,
QUAL.QUALIFICATION,
QUAL.QUALIFICATION_NAME,
QUAL.QUALIFICATION_TYPE As QUAL_TYPE,
Upper(ACTI.LONG) AS ACTIVE_IND,
Upper(ENTR.LONG) AS ENTRY_LEVEL,
Upper(BLAC.LONG) AS BLACKLIST,
QUAL.ENROL_CATEGORY As ENROL_CAT,
QUAL.PRESENT_CATEGORY As PRESENT_CAT,
QUAL.FINAL_STATUS As STATUS_FINAL,
QUAL.LEVY_CATEGORY,
QUAL.CERT_TYPE,
QUAL.LEVY_TYPE,
QUAL.FOS_SELECTION,
QUAL.FOS_SELECTION As LONG,
QUAL.FBUSINESSENTITYID,
QUAL.SITEID,
QUAL.CAMPUS,
QUAL.ORGUNIT_MANAGER,
QUAL.ORGUNIT_NAME,
QUAL.ORGUNIT_TYPE,
RESU.KSTUDQUALFOSRESULTID,
RESU.DISCONTINUEDATE,
RESU.FDISCONTINUECODEID,
RESU.RESULT,
RESU.DISCONTINUE_REAS,
RESU.POSTPONE_REAS,
RESU.FPOSTPONEMENTCODEID,
RESU.FGRADUATIONCEREMONYID,
GRAD.CEREMONY,
GRAD.CEREMONYDATETIME,
QUAL.QUALIFICATIONCODE,
QUAL.QUALIFICATIONFIELDOFSTUDY,
QUAL.QUALIFICATIONLEVEL,
STUD.ENROLACADEMICYEAR,
STUD.ENROLHISTORYYEAR,
QUAL.MIN,
QUAL.MIN_UNIT,
QUAL.MAX,
QUAL.MAX_UNIT,
STUD.ISHEMISSUBSIDY,
STUD.ISMAINQUALLEVEL,
STUD.ISCONDITIONALREG,
STUD.ISCUMLAUDE,
STUD.ISPOSSIBLEGRADUATE,
STUD.FACCEPTANCETESTCODEID,
QUAL.ISVERIFICATIONREQUIRED,
QUAL.EXAMSUBMINIMUM,
QUAL.ISVATAPPLICABLE,
QUAL.ISPRESENTEDBEFOREAPPROVAL,
QUAL.ISDIRECTED,
QUAL.SITEID As FSITEORGUNITNUMBER,
STUD.KENROLSTUDID,
QUAL.FQUALLEVELAPID,
QUAL.KENROLMENTPRESENTATIONID,
STUD.FENROLMENTPRESENTATIONID,
QUAL.FOS_KACADEMICPROGRAMID,
STUD.FPROGRAMAPID,
QUAL.FENROLMENTCATEGORYCODEID,
QUAL.FPRESENTATIONCATEGORYCODEID,
Case
When STUD.ENROLHISTORYYEAR > 6 Then 6
Else STUD.ENROLHISTORYYEAR
End As FEEHISTORYYEAR,
strftime("%Y", STUD.STARTDATE) - strftime("%Y", STUD.DATEQUALLEVELSTARTED) + 1 As CALCHISTORYYEAR
From
QUALLEVELENROLSTUD STUD Left Join
VSS.X000_Codedescription BLAC ON BLAC.KCODEDESCID = STUD.FBLACKLISTCODEID Left Join
VSS.X000_Codedescription ACTI ON ACTI.KCODEDESCID = STUD.FSTUDACTIVECODEID Left Join
VSS.X000_Codedescription ENTR ON ENTR.KCODEDESCID = STUD.FENTRYLEVELCODEID Left Join
VSS.X000_Qualifications QUAL On QUAL.KENROLMENTPRESENTATIONID = STUD.FENROLMENTPRESENTATIONID Left Join
VSS.X000_Student_qualfos_result RESU ON RESU.KBUSINESSENTITYID = STUD.KSTUDBUSENTID And
RESU.KACADEMICPROGRAMID = QUAL.FOS_KACADEMICPROGRAMID And
Strftime('%Y', RESU.DISCONTINUEDATE) = '%YEAR%' Left Join
Vss.X000_Gradceremony GRAD On GRAD.KGRADUATIONCEREMONYID = RESU.FGRADUATIONCEREMONYID
Order By
STUD.KSTUDBUSENTID
;"""
# s_sql = s_sql.replace("%PERIOD%", s_period)
s_sql = s_sql.replace("%YEAR%", s_year)
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# MESSAGE
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b>" + str(i) + "</b> Students")
# Export the data
if l_export:
print("Export students all...")
sr_filet = sr_file
sx_path = re_path + s_year + "/"
sx_file = "Student_001_all_"
s_head = funccsv.get_colnames_sqlite(so_conn, sr_filet)
funccsv.write_data(so_conn, "main", sr_filet, sx_path, sx_file, s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
"""*************************************************************************
BUILD STUDENT MODULES
*************************************************************************"""
print("BUILD " + s_year + " STUDENT MODULES")
funcfile.writelog("BUILD " + s_year + " YEAR STUDENT MODULES")
# BUILD STUDENT LIST
print("Build student list...")
sr_file = "X001_Student_module"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
MENR.KENROLSTUDID,
MENR.KSTUDBUSENTID,
MENR.ACADEMICYEAR,
MENR.DATEENROL,
MENR.STARTDATE,
MENR.ENDDATE,
STUD.FQUALLEVELAPID,
STUD.QUALIFICATION,
MODU.FMODULEAPID,
MODU.MODULE,
MODU.MODULE_NAME,
MODU.FENROLMENTCATEGORYCODEID,
MODU.ENROL_CATEGORY,
MODU.FPRESENTATIONCATEGORYCODEID,
MODU.PRESENT_CATEGORY,
MODU.FCOURSEGROUPCODEID,
MODU.COURSEGROUP,
MENR.FMODULETYPECODEID,
Upper(TYPE.LONG) As MODULE_TYPE,
MENR.DATEDISCONTINUED,
MENR.FCOMPLETEREASONCODEID,
Upper(REAS.LONG) As COMPLETE_REASON,
Trim(MODR.PART_RESU) As PART_RESU,
MODR.DATEACHIEVED As DATE_RESU,
MODU.FBUSINESSENTITYID,
MODU.SITEID,
MODU.CAMPUS,
MODU.ORGUNIT_TYPE,
MODU.ORGUNIT_NAME,
MODU.ORGUNIT_MANAGER,
MENR.ISCONDITIONALREG,
MENR.ISNEWENROLMENT,
MENR.ISPROCESSEDONLINE,
MENR.ISREPEATINGMODULE,
MENR.ISEXEMPTION,
MODU.ISEXAMMODULE,
MODU.ISRESEARCHMODULE,
MENR.ISDISCOUNTED,
MODU.EXAMSUBMINIMUM,
MENR.FSTUDYCENTREMODAPID,
MENR.FENROLMENTPRESENTATIONID,
MENR.FEXAMCENTREMODAPID,
MENR.FPRESENTATIONLANGUAGEID,
MENR.FMODPERIODENROLPRESCATID,
MENR.FACKTYPECODEID,
MENR.FACKSTUDBUSENTID,
MENR.FACKENROLSTUDID,
MENR.FACKMODENROLSTUDID,
MENR.FACKMODSTUDBUSENTID,
MENR.AUDITDATETIME As MENROL_AUDITDATETIME,
MENR.FAUDITSYSTEMFUNCTIONID As MENROL_SYSID,
MENR.FAUDITUSERCODE As MENROL_USERCODE,
MENR.REGALLOWED,
MODU.KENROLMENTPRESENTATIONID,
MODU.COURSECODE,
MODU.COURSELEVEL,
MODU.COURSEMODULE,
MODU.COURSESEMESTER
From
MODULEENROLSTUD MENR Inner Join
VSS.X000_Modules MODU On MODU.KENROLMENTPRESENTATIONID = MENR.FENROLMENTPRESENTATIONID Left Join
VSS.X000_Codedescription TYPE On TYPE.KCODEDESCID = MENR.FMODULETYPECODEID Left Join
VSS.X000_Codedescription REAS On REAS.KCODEDESCID = MENR.FCOMPLETEREASONCODEID Left Join
X001_Student STUD On STUD.KSTUDBUSENTID = MENR.KSTUDBUSENTID And
STUD.KENROLSTUDID = MENR.FQUALLEVELENROLSTUDID Left Join
X000_Student_module_result_participate MODR On MODR.KSTUDBUSENTID = MENR.KSTUDBUSENTID And
MODR.KENROLSTUDID = MENR.KENROLSTUDID
;"""
# s_sql = s_sql.replace("%PERIOD%", s_period)
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# MESSAGE
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b>" + str(i) + "</b> Modules")
return
def robot_report_person_conflict(s_nwu: str = "",
s_name: str = "",
s_mail: str = ""):
"""
REPORT EMPLOYEE PERSON CONFLICT OF INTERESTS
:param s_nwu: NWU Number
:param s_name: The name of the requester / recipient
:param s_mail: The requester mail address
:return: str: Info in message format
"""
# IMPORT PYTHON MODULES
import sqlite3
from datetime import datetime
# IMPORT OWN MODULES
from _my_modules import funccsv
from _my_modules import funcdate
from _my_modules import funcfile
from _my_modules import funcmail
from _my_modules import funcsms
from _my_modules import funcstat
# DECLARE VARIABLES
l_debug: bool = True
"""*************************************************************************
ENVIRONMENT
*************************************************************************"""
if l_debug:
print("ENVIRONMENT")
# DECLARE VARIABLES
s_description: str = "Conflict of interest reports"
so_path: str = "W:/People_conflict/" # Source database path
so_file: str = "People_conflict.sqlite" # Source database
re_path: str = "R:/People/" + funcdate.cur_year() + "/" # Results
l_mess: bool = funcconf.l_mess_project
# l_mess: bool = False
l_mailed: bool = False
# LOG
if l_debug:
print(s_function.upper())
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: " + s_function.upper())
funcfile.writelog("-" * len("script: " + s_function))
funcfile.writelog("%t " + s_description + " for " + s_nwu +
" requested by " + s_name)
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator",
"<b>" + s_function.upper() + "</b>")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
funcfile.writelog("OPEN THE DATABASES")
if l_debug:
print("OPEN THE DATABASES")
# OPEN THE WORKING DATABASE
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
# ATTACH DATA SOURCES
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
""" ****************************************************************************
BUILD THE DECLARATIONS REPORT
*****************************************************************************"""
funcfile.writelog("BUILD THE DECLARATIONS REPORT")
if l_debug:
print("BUILD THE DECLARATIONS REPORT")
# OBTAIN THE NAME OF THE PERSON
s_lookup_name = funcfile.get_field_value(
so_curs, "PEOPLE.X000_PEOPLE",
"name_address||' ('||preferred_name||')' ",
"employee_number = '" + s_nwu + "'")
if l_debug:
print("FIELD LOOKUP: " + s_name)
s_message: str = s_description + " for <b>" + s_lookup_name + '(' + s_nwu + ")</b>."
# BUILD THE TABLE
if l_debug:
print("Build declarations table...")
s_file_prefix: str = "Y000_"
s_file_name: str = "report_declarations_all"
sr_file = s_file_prefix + s_file_name
so_curs.execute("Drop table if exists " + sr_file)
s_sql = "Create table " + sr_file + " AS " + """
Select
d.DECLARATION_ID,
d.EMPLOYEE_NUMBER,
p.name_full As NAME_FULL,
d.DECLARATION_DATE,
d.UNDERSTAND_POLICY_FLAG,
d.INTEREST_TO_DECLARE_FLAG,
d.FULL_DISCLOSURE_FLAG,
Upper(d.STATUS) As STATUS,
d.LINE_MANAGER,
m.name_full As MANAGER_NAME_FULL,
d.REJECTION_REASON,
d.CREATION_DATE,
d.AUDIT_USER,
d.LAST_UPDATE_DATE,
d.LAST_UPDATED_BY,
d.EXTERNAL_REFERENCE
From
X000_declarations_all d Left Join
PEOPLE.X000_PEOPLE p On p.employee_number = d.EMPLOYEE_NUMBER Left Join
PEOPLE.X000_PEOPLE m On m.employee_number = d.LINE_MANAGER
Where
d.EMPLOYEE_NUMBER = '%PERSON%'
Order By
d.LAST_UPDATE_DATE
;"""
s_sql = s_sql.replace("%PERSON%", s_nwu)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# RECORDS FOUND
if funcsys.tablerowcount(so_curs, sr_file) > 0:
# BUILD THE MESSAGE
l_records = funcstat.stat_list(
so_curs, sr_file,
"DECLARATION_DATE||' ('||INTEREST_TO_DECLARE_FLAG||') '||STATUS")
s_message += '\n\n'
s_message += 'Declarations on:'
for item in l_records:
s_message += '\n'
for element in item:
s_message += element
# EXPORT RECORDS
print("Export findings...")
sx_path = re_path
sx_file = sr_file + "_"
sx_file_dated = sx_file + funcdate.today_file()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file, s_head)
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file_dated,
s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file_dated)
# MAIL THE REPORT
s_report = "All DIY declarations included!"
if s_name != "" and s_mail != "":
l_mailed = True
funcfile.writelog("%t Declarations mailed to " + s_mail)
if l_debug:
print("Send the report...")
s_body: str = "Attached please find conflict of interest declarations for " + s_nwu + "."
s_body += "\n\r"
s_body += s_report
funcmail.send(s_name, s_mail, "E", s_description + " for " + s_nwu,
s_body, re_path, sx_file_dated + ".csv")
# DELETE THE MAILED FILE
if funcfile.file_delete(re_path, sx_file_dated):
funcfile.writelog("%t Declarations deleted")
if l_debug:
print("Delete the report...")
else:
s_message += "\n\n"
s_message += "No declarations on record."
""" ****************************************************************************
BUILD THE INTERESTS REPORT
*****************************************************************************"""
funcfile.writelog("BUILD THE INTERESTS REPORT")
if l_debug:
print("BUILD THE INTERESTS REPORT")
# BUILD THE TABLE
if l_debug:
print("Build interests table...")
s_file_prefix: str = "Y000_"
s_file_name: str = "report_interests_all"
sr_file = s_file_prefix + s_file_name
so_curs.execute("Drop table if exists " + sr_file)
s_sql = "Create table " + sr_file + " AS " + """
Select
i.INTEREST_ID,
i.DECLARATION_ID,
i.EMPLOYEE_NUMBER,
p.name_full As NAME_FULL,
i.DECLARATION_DATE,
i.CONFLICT_TYPE_ID,
Upper(i.CONFLICT_TYPE) As CONFLICT_TYPE,
i.INTEREST_TYPE_ID,
Upper(i.INTEREST_TYPE) As INTEREST_TYPE,
i.STATUS_ID,
Upper(i.INTEREST_STATUS) As INTEREST_STATUS,
i.PERC_SHARE_INTEREST,
Upper(i.ENTITY_NAME) As ENTITY_NAME,
i.ENTITY_REGISTRATION_NUMBER,
Upper(i.OFFICE_ADDRESS) As OFFICE_ADDRESS,
Upper(i.DESCRIPTION) As DESCRIPTION,
i.DIR_APPOINTMENT_DATE,
i.LINE_MANAGER,
m.name_full As MANAGER_NAME_FULL,
i.NEXT_LINE_MANAGER,
i.INDUSTRY_CLASS_ID,
Upper(i.INDUSTRY_TYPE) As INDUSTRY_TYPE,
i.TASK_PERF_AGREEMENT,
i.MITIGATION_AGREEMENT,
i.REJECTION_REASON,
i.CREATION_DATE,
i.AUDIT_USER,
i.LAST_UPDATE_DATE,
i.LAST_UPDATED_BY,
i.EXTERNAL_REFERENCE
From
X000_interests_all i Left Join
PEOPLE.X000_PEOPLE p On p.employee_number = i.EMPLOYEE_NUMBER Left Join
PEOPLE.X000_PEOPLE m On m.employee_number = i.LINE_MANAGER
Where
i.EMPLOYEE_NUMBER = '%PERSON%'
Order By
i.LAST_UPDATE_DATE
;"""
s_sql = s_sql.replace("%PERSON%", s_nwu)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# RECORDS FOUND
if funcsys.tablerowcount(so_curs, sr_file) > 0:
# BUILD THE MESSAGE
l_records = funcstat.stat_list(
so_curs, sr_file, "DECLARATION_DATE||' - '||INTEREST_STATUS")
s_message += '\n\n'
s_message += 'Interests declared:'
for item in l_records:
s_message += '\n'
for element in item:
s_message += element
# EXPORT RECORDS
print("Export findings...")
sx_path = re_path
sx_file = sr_file + "_"
sx_file_dated = sx_file + funcdate.today_file()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file, s_head)
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file_dated,
s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file_dated)
# MAIL THE REPORT
s_report = "All DIY interests included!"
if s_name != "" and s_mail != "":
l_mailed = True
funcfile.writelog("%t Interests mailed to " + s_mail)
if l_debug:
print("Send the report...")
s_body: str = "Attached please find conflict of interest interests for " + s_nwu + "."
s_body += "\n\r"
s_body += s_report
funcmail.send(s_name, s_mail, "E", s_description + " for " + s_nwu,
s_body, re_path, sx_file_dated + ".csv")
# DELETE THE MAILED FILE
if funcfile.file_delete(re_path, sx_file_dated):
funcfile.writelog("%t Interests deleted")
if l_debug:
print("Delete the report...")
else:
s_message += "\n\n"
s_message += "No interests on record."
# POPULATE THE RETURN MESSAGE
if l_mailed:
s_message += "\n\n"
s_message += "Reports were mailed to " + s_mail + "."
s_return_message = s_message
"""*****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# CLOSE THE LOG WRITER
funcfile.writelog("-" * len("completed: " + s_function))
funcfile.writelog("COMPLETED: " + s_function.upper())
return s_return_message[0:4096]
def kfs_period_list(s_period="curr"):
"""
Script to build standard KFS lists
:type s_period: str: The financial period (curr, prev or year)
:return: Nothing
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# DECLARE VARIABLES
l_debug: bool = True
l_export: bool = True
so_path = "W:/Kfs/" # Source database path
re_path = "R:/Kfs/" # Results path
# ed_path = "S:/_external_data/" # external data path
if s_period == "curr":
s_year = funcdate.cur_year()
so_file = "Kfs_curr.sqlite" # Source database
elif s_period == "prev":
s_year = funcdate.prev_year()
so_file = "Kfs_prev.sqlite" # Source database
else:
s_year = s_period
so_file = "Kfs_" + s_year + ".sqlite" # Source database
# OPEN THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B006_KFS_PERIOD_LIST")
funcfile.writelog("----------------------------")
print("--------------------")
print("B006_KFS_PERIOD_LIST")
print("--------------------")
# MESSAGE
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator",
"<b>B006 Kfs " + s_year + " period lists</b>")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
print("OPEN THE DATABASES")
funcfile.writelog("OPEN THE DATABASES")
# OPEN THE WORKING DATABASE
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
# ATTACH DATA SOURCES
so_curs.execute("ATTACH DATABASE 'W:/Kfs/Kfs.sqlite' AS 'KFS'")
funcfile.writelog("%t ATTACH DATABASE: KFS.SQLITE")
if s_period == "curr":
so_curs.execute(
"ATTACH DATABASE 'W:/Kfs/Kfs_prev.sqlite' AS 'KFSPREV'")
funcfile.writelog("%t ATTACH DATABASE: KFS_PREV.SQLITE")
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
""" ****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
""" ****************************************************************************
GL TRANSACTION LIST
*****************************************************************************"""
print("GL TRANSACTION LIST")
funcfile.writelog("GL TRANSACTION LIST")
# BUILD GL TRANSACTION LIST
print("Build gl transaction list...")
sr_file = "X000_GL_trans"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
GLC.UNIV_FISCAL_YR,
GLC.UNIV_FISCAL_PRD_CD,
GLC.CALC_COST_STRING,
ACC.ORG_NM,
ACC.ACCOUNT_NM,
OBJ.FIN_OBJ_CD_NM,
GLC.TRANSACTION_DT,
GLC.FDOC_NBR,
GLC.CALC_AMOUNT,
GLC.TRN_LDGR_ENTR_DESC,
ACC.ACCT_TYP_NM,
GLC.TRN_POST_DT,
GLC."TIMESTAMP",
GLC.FIN_COA_CD,
GLC.ACCOUNT_NBR,
GLC.FIN_OBJECT_CD,
GLC.FIN_BALANCE_TYP_CD,
GLC.FIN_OBJ_TYP_CD,
GLC.FDOC_TYP_CD,
GLC.FS_ORIGIN_CD,
ORI.FS_DATABASE_DESC,
GLC.TRN_ENTR_SEQ_NBR,
GLC.FDOC_REF_TYP_CD,
GLC.FS_REF_ORIGIN_CD,
GLC.FDOC_REF_NBR,
GLC.FDOC_REVERSAL_DT,
GLC.TRN_ENCUM_UPDT_CD
FROM
GL_ENTRY_T GLC Left Join
KFS.X000_Account ACC ON ACC.FIN_COA_CD = GLC.FIN_COA_CD AND ACC.ACCOUNT_NBR = GLC.ACCOUNT_NBR Left Join
KFS.CA_OBJECT_CODE_T OBJ ON OBJ.UNIV_FISCAL_YR = GLC.UNIV_FISCAL_YR AND
OBJ.FIN_COA_CD = GLC.FIN_COA_CD AND
OBJ.FIN_OBJECT_CD = GLC.FIN_OBJECT_CD Left Join
KFS.FS_ORIGIN_CODE_T ORI ON ORI.FS_ORIGIN_CD = GLC.FS_ORIGIN_CD
ORDER BY
GLC.CALC_COST_STRING,
GLC.UNIV_FISCAL_PRD_CD
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: GL Transaction list")
# MESSAGE
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b> " + str(i) + "</b> GL transactions")
""" ****************************************************************************
PAYMENT SUMMARY LIST
*****************************************************************************"""
print("PAYMENT SUMMARY LIST")
funcfile.writelog("PAYMENT SUMMARY LIST")
# BUILD PAYMENTS SUMMARY LIST
print("Build payments...")
sr_file = "X000_Payments"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
DET.CUST_PMT_DOC_NBR As EDOC,
PAY.PMT_DT,
DET.REQS_NBR,
DET.PO_NBR,
Case
When PR.FDOC_NBR Is Not Null Then PR.INV_NBR
When DV.FDOC_NBR Is Not Null Then DV.INV_NBR
Else DET.INV_NBR
End As INV_NBR,
Case
When PR.FDOC_NBR Is Not Null Then PR.INV_DT
When DV.FDOC_NBR Is Not Null Then DV.INV_DATE
Else DET.INV_DT
End As INV_DT,
DET.ORIG_INV_AMT,
PAY.PAYEE_ID AS PAYEE_ID,
PAY.PAYEE_ID_TYP_CD AS PAYEE_TYPE,
PAY.PMT_PAYEE_NM As PAYEE_NAME,
CASE
WHEN PAY.PAYEE_ID_TYP_CD = 'E' THEN 'EM'
WHEN PAY.PAYEE_ID_TYP_CD = 'S' THEN 'ST'
WHEN PAY.PAYEE_ID_TYP_CD = 'V' AND PAY.PAYEE_OWNR_CD = '' THEN 'OT'
ELSE PAY.PAYEE_OWNR_CD
END As PAYEE_OWNR_CD_CALC,
DET.NET_PMT_AMT,
PAY.PMT_GRP_ID,
PAY.DISB_NBR,
PAY.DISB_TS,
PAY.PMT_STAT_CD,
DOC.DOC_TYP_NM As DOC_TYPE,
Upper(DOC.LBL) As DOC_LABEL,
PAY.PMT_TXBL_IND,
PAY.ADV_EMAIL_ADDR,
PAY.PMT_FIL_ID,
PAY.PROC_ID,
DET.PMT_DTL_ID
From
PDP_PMT_GRP_T PAY Left Join
KFS.PDP_PMT_DTL_T DET On DET.PMT_GRP_ID = PAY.PMT_GRP_ID Left Join
KFS.AP_PMT_RQST_T PR On PR.FDOC_NBR = DET.CUST_PMT_DOC_NBR Left Join
KFS.FP_DV_PAYEE_DTL_EXT_T DV On DV.FDOC_NBR = DET.CUST_PMT_DOC_NBR Left Join
KFS.X000_Document DOC On DOC.DOC_HDR_ID = DET.CUST_PMT_DOC_NBR
Where
DET.CUST_PMT_DOC_NBR Is Not NULL
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# MESSAGE
if funcconf.l_mess_project:
i = funcsys.tablerowcount(so_curs, sr_file)
funcsms.send_telegram("", "administrator",
"<b> " + str(i) + "</b> Payments")
""" ****************************************************************************
PAYMENT INITIATE LIST
*****************************************************************************"""
print("PAYMENT INITIATE LIST")
funcfile.writelog("PAYMENT INITIATE LIST")
# BUILD INITIATOR LIST
print("Build initiate list...")
sr_file = "X000_Initiate"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select Distinct
ROUTE.ACTN_TKN_ID,
ROUTE.DOC_HDR_ID,
ROUTE.ACTN_DT,
ROUTE.PRNCPL_ID,
CASE
WHEN ROUTE.PRNCPL_ID = '26807815' THEN 'KFS WORKFLOW SYSTEM USER'
WHEN PERSON.NAME_ADDR IS NULL THEN 'UNKNOWN'
ELSE PERSON.NAME_ADDR
END AS NAME_ADDR,
ROUTE.ACTN_CD,
CASE
WHEN ROUTE.ACTN_CD = 'C' THEN 'COMPLETED'
ELSE 'OTHER'
END AS ACTN,
ROUTE.ANNOTN,
PERSON.ORG_NAME
From
KREW_ACTN_TKN_T_COM ROUTE Left Join
PEOPLE.X002_PEOPLE_CURR_YEAR PERSON On PERSON.EMPLOYEE_NUMBER = ROUTE.PRNCPL_ID
Order By
ROUTE.ACTN_DT,
ROUTE.ACTN_TKN_ID
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD UNIQUE INITIATOR LIST
print("Build unique initiate list...")
sr_file = "X000_Initiate_unique"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select Distinct
ROUTE.ACTN_TKN_ID,
ROUTE.DOC_HDR_ID,
Min(ROUTE.ACTN_DT) As ACTN_DT,
ROUTE.PRNCPL_ID,
CASE
WHEN ROUTE.PRNCPL_ID = '26807815' THEN 'KFS WORKFLOW SYSTEM USER'
WHEN PERSON.NAME_ADDR IS NULL THEN 'UNKNOWN'
ELSE PERSON.NAME_ADDR
END AS NAME_ADDR,
ROUTE.ACTN_CD,
CASE
WHEN ROUTE.ACTN_CD = 'C' THEN 'COMPLETED'
ELSE 'OTHER'
END AS ACTN,
ROUTE.ANNOTN,
Count(ROUTE.DOC_VER_NBR) As COM_COUNT,
PERSON.ORG_NAME
From
KREW_ACTN_TKN_T_COM ROUTE Left Join
PEOPLE.X002_PEOPLE_CURR_YEAR PERSON On PERSON.EMPLOYEE_NUMBER = ROUTE.PRNCPL_ID
Group By
ROUTE.DOC_HDR_ID
Order By
ROUTE.ACTN_DT,
ROUTE.ACTN_TKN_ID
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
PAYMENT APPROVE LIST
*****************************************************************************"""
print("PAYMENT APPROVE LIST")
funcfile.writelog("PAYMENT APPROVE LIST")
# BUILD CURR APPROVALS ALL APPROVERS
print("Build approve list...")
sr_file = "X000_Approve"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select Distinct
ROUTE.ACTN_TKN_ID,
ROUTE.DOC_HDR_ID,
ROUTE.ACTN_DT,
ROUTE.PRNCPL_ID,
CASE
WHEN ROUTE.PRNCPL_ID = '26807815' THEN 'KFS WORKFLOW SYSTEM USER'
WHEN PERSON.NAME_ADDR IS NULL THEN 'UNKNOWN'
ELSE PERSON.NAME_ADDR
END AS NAME_ADDR,
ROUTE.ACTN_CD,
CASE
WHEN ROUTE.ACTN_CD = 'a' THEN 'SUPER USER APPROVED'
WHEN ROUTE.ACTN_CD = 'A' THEN 'APPROVED'
WHEN ROUTE.ACTN_CD = 'B' THEN 'BLANKET APPROVED'
WHEN ROUTE.ACTN_CD = 'r' THEN 'SUPER USER ROUTE LEVEL APPROVED'
WHEN ROUTE.ACTN_CD = 'R' THEN 'SUPER USER ROUTE LEVEL APPROVED'
WHEN ROUTE.ACTN_CD = 'v' THEN 'SUPER USER APPROVED'
ELSE 'OTHER'
END AS ACTN,
ROUTE.ANNOTN,
PERSON.ORG_NAME
From
KREW_ACTN_TKN_T_APP ROUTE Left Join
PEOPLE.X002_PEOPLE_CURR_YEAR PERSON On PERSON.EMPLOYEE_NUMBER = ROUTE.PRNCPL_ID
Order By
ROUTE.ACTN_DT,
ROUTE.ACTN_TKN_ID
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD CURR APPROVALS UNIQUE LIST OF LAST APPROVER
print("Build unique approve list...")
sr_file = "X000_Approve_unique"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select Distinct
ROUTE.ACTN_TKN_ID,
ROUTE.DOC_HDR_ID,
Min(ROUTE.ACTN_DT) As ACTN_DT,
ROUTE.PRNCPL_ID,
CASE
WHEN ROUTE.PRNCPL_ID = '26807815' THEN 'KFS WORKFLOW SYSTEM USER'
WHEN PERSON.NAME_ADDR IS NULL THEN 'UNKNOWN'
ELSE PERSON.NAME_ADDR
END AS NAME_ADDR,
ROUTE.ACTN_CD,
CASE
WHEN ROUTE.ACTN_CD = 'a' THEN 'SUPER USER APPROVED'
WHEN ROUTE.ACTN_CD = 'A' THEN 'APPROVED'
WHEN ROUTE.ACTN_CD = 'B' THEN 'BLANKET APPROVED'
WHEN ROUTE.ACTN_CD = 'r' THEN 'SUPER USER ROUTE LEVEL APPROVED'
WHEN ROUTE.ACTN_CD = 'R' THEN 'SUPER USER ROUTE LEVEL APPROVED'
WHEN ROUTE.ACTN_CD = 'v' THEN 'SUPER USER APPROVED'
ELSE 'OTHER'
END AS ACTN,
ROUTE.ANNOTN,
Count(ROUTE.DOC_VER_NBR) As APP_COUNT,
PERSON.ORG_NAME
From
KREW_ACTN_TKN_T_APP ROUTE Left Join
PEOPLE.X002_PEOPLE_CURR_YEAR PERSON On PERSON.EMPLOYEE_NUMBER = ROUTE.PRNCPL_ID
Group By
ROUTE.DOC_HDR_ID
Having
ORG_NAME Is Not NULL And
ORG_NAME Not In ('NWU PURCHASE AND PAYMENTS')
Order By
ROUTE.ACTN_DT,
ROUTE.ACTN_TKN_ID
"""
"""
Having
ORG_NAME Is Not NULL
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
PAYMENT ACCOUNTING LINE
*****************************************************************************"""
print("PAYMENT ACCOUNTING LINE")
funcfile.writelog("PAYMENT ACCOUNTING LINE")
# BUILD ACCOUNTING LINES
print("Build account lines...")
sr_file = "X000_Account_line"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
LINE.FDOC_NBR,
LINE.FDOC_LINE_NBR,
LINE.FDOC_POST_YR,
Trim(LINE.FIN_COA_CD)||'.'||Trim(LINE.ACCOUNT_NBR)||'.'||Trim(LINE.FIN_OBJECT_CD) As COST_STRING,
Case
When LINE.FDOC_LINE_DBCR_CD = "C" Then LINE.FDOC_LINE_AMT * -1
Else LINE.FDOC_LINE_AMT
End As AMOUNT,
LINE.VATABLE,
LINE.FDOC_LINE_DESC,
ACC.ORG_NM,
ACC.ACCOUNT_NM,
OBJ.FIN_OBJ_CD_NM,
ACC.ACCT_TYP_NM,
ACC.ACCT_FSC_OFC_UID
From
FP_ACCT_LINES_T LINE Left Join
KFS.X000_Account ACC ON ACC.FIN_COA_CD = LINE.FIN_COA_CD And
ACC.ACCOUNT_NBR = LINE.ACCOUNT_NBR Left Join
KFS.CA_OBJECT_CODE_T OBJ ON OBJ.UNIV_FISCAL_YR = LINE.FDOC_POST_YR And
OBJ.FIN_COA_CD = LINE.FIN_COA_CD And
OBJ.FIN_OBJECT_CD = LINE.FIN_OBJECT_CD
Order By
LINE.FDOC_NBR,
LINE.FDOC_LINE_NBR,
LINE.FDOC_LINE_AMT
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD UNIQUE ACCOUNT LINE
print("Build unique account lines...")
sr_file = "X000_Account_line_unique"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
FIND.FDOC_NBR,
FIND.FDOC_POST_YR,
FIND.COST_STRING,
Max(FIND.AMOUNT) As AMOUNT,
FIND.FDOC_LINE_DESC,
Count(FIND.VATABLE) As COUNT_LINES
From
X000_Account_line FIND
Group By
FIND.FDOC_NBR
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
PAYMENT NOTES
*****************************************************************************"""
print("PAYMENT NOTES")
funcfile.writelog("PAYMENT NOTES")
# BUILD PAYMENT NOTE
print("Build payment note...")
sr_file = "X000_Note"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAY.EDOC,
HDR.FDOC_DESC As NOTE_DESC,
NTE.TXT As NOTE_TXT
From
X000_Payments PAY Left Join
KFS.KRNS_DOC_HDR_T HDR On HDR.DOC_HDR_ID = PAY.EDOC Inner Join
KFS.KRNS_NTE_T NTE On NTE.RMT_OBJ_ID = HDR.OBJ_ID Left Join
KFS.KRNS_ATT_T ATT On ATT.NTE_ID = NTE.NTE_ID
Where
ATT.FILE_NM IS NULL
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD PAYMENT UNIQUE NOTE
print("Build payment unique note...")
sr_file = "X000_Note_unique"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
NTE.EDOC,
Count(NTE.EDOC) As NOTE_COUNT,
NTE.NOTE_DESC,
NTE.NOTE_TXT
From
X000_Note NTE
Group By
NTE.EDOC
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
PAYMENT ATTACHMENTS
*****************************************************************************"""
print("PAYMENT ATTACHMENTS")
funcfile.writelog("PAYMENT ATTACHMENTS")
# BUILD PAYMENT ATTACHMENTS
print("Build payment attachments...")
sr_file = "X000_Attachment"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAY.EDOC,
HDR.FDOC_DESC As ATTACH_DESC,
NTE.TXT As ATTACH_TXT,
ATT.FILE_NM As ATTACH_FILE
From
X000_Payments PAY Left Join
KFS.KRNS_DOC_HDR_T HDR On HDR.DOC_HDR_ID = PAY.EDOC Inner Join
KFS.KRNS_NTE_T NTE On NTE.RMT_OBJ_ID = HDR.OBJ_ID Inner Join
KFS.KRNS_ATT_T ATT On ATT.NTE_ID = NTE.NTE_ID
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD PAYMENT UNIQUE ATTACHMENT
print("Build payment unique attachment...")
sr_file = "X000_Attachment_unique"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
ATT.EDOC,
Count(ATT.EDOC) As ATTACH_COUNT,
ATT.ATTACH_DESC,
ATT.ATTACH_TXT,
ATT.ATTACH_FILE
From
X000_Attachment ATT
Group By
ATT.EDOC
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
PAYMENTS DETAIL
*****************************************************************************"""
print("PAYMENTS DETAIL")
funcfile.writelog("PAYMENTS DETAIL")
# PAYMENT LIST WITH DETAIL
print("Build payment report...")
sr_file = "X001aa_Report_payments"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAY.EDOC,
PAY.EDOC As CUST_PMT_DOC_NBR,
PAY.PMT_GRP_ID,
PAY.PAYEE_ID,
PAY.PAYEE_TYPE,
TYP.PAYEE_TYP_DESC,
PAY.PAYEE_OWNR_CD_CALC,
PTC.LOOKUP_DESCRIPTION As PAYEE_OWNR_DESC,
PAY.PAYEE_NAME,
PAY.PAYEE_ID As VENDOR_ID,
VEN.VNDR_TYP_CD As VENDOR_TYPE,
CASE
WHEN PAY.PAYEE_OWNR_CD_CALC = 'EM' THEN 'EM'
WHEN PAY.PAYEE_OWNR_CD_CALC = 'ST' THEN 'ST'
ELSE VEN.VNDR_TYP_CD
END As VENDOR_TYPE_CALC,
VEN.VNDR_NM AS VENDOR_NAME,
VEN.VNDR_URL_ADDR AS VENDOR_REG_NR,
VEN.VNDR_TAX_NBR AS VENDOR_TAX_NR,
PEE.BNK_ACCT_NBR AS VENDOR_BANK_NR,
PAY.DOC_TYPE,
PAY.DOC_LABEL,
PAY.REQS_NBR,
PAY.PO_NBR,
PAY.INV_NBR,
PAY.INV_DT,
PAY.ORIG_INV_AMT,
PAY.NET_PMT_AMT,
PAY.PMT_DTL_ID,
PAY.PMT_DT,
PAY.PMT_STAT_CD,
Upper(STA.PMT_STAT_CD_DESC) AS PAYMENT_STATUS,
PAY.DISB_NBR,
PAY.DISB_TS,
INI.PRNCPL_ID AS COMPLETE_EMP_NO,
INI.NAME_ADDR AS COMPLETE_EMP_NAME,
INI.ACTN_DT AS COMPLETE_DATE,
INI.ACTN AS COMPLETE_STATUS,
Cast(INI.COM_COUNT As INT) As I_COUNT,
INI.ANNOTN AS COMPLETE_NOTE,
APP.PRNCPL_ID AS APPROVE_EMP_NO,
APP.NAME_ADDR AS APPROVE_EMP_NAME,
APP.ACTN_DT AS APPROVE_DATE,
APP.ACTN AS APPROVE_STATUS,
Cast(APP.APP_COUNT As INT) As A_COUNT,
APP.ANNOTN AS APPROVE_NOTE,
CASE
WHEN ACC.COUNT_LINES = 1 THEN ACC.COST_STRING
ELSE Cast(ACC.COUNT_LINES As TEXT)
END As ACC_COST_STRING,
ACC.FDOC_LINE_DESC As ACC_DESC,
NTE.NOTE_DESC,
NTE.NOTE_TXT,
NTE.NOTE_COUNT,
ATT.ATTACH_DESC,
ATT.ATTACH_TXT,
ATT.ATTACH_FILE,
ATT.ATTACH_COUNT
From
X000_Payments PAY Left Join
KFS.X000_Vendor VEN On VEN.VENDOR_ID = PAY.PAYEE_ID Left Join
KFS.PDP_PAYEE_ACH_ACCT_T PEE On PEE.PAYEE_ID_NBR = PAY.PAYEE_ID And
PEE.PAYEE_ID_TYP_CD = PAY.PAYEE_TYPE Left Join
KFS.PDP_PAYEE_TYP_T TYP ON TYP.PAYEE_TYP_CD = PAY.PAYEE_TYPE Left Join
KFS.X000_OWN_KFS_LOOKUPS PTC on PTC.LOOKUP_CODE = PAY.PAYEE_OWNR_CD_CALC And
PTC.LOOKUP = "PAYEE OWNER TYPE" Left Join
KFS.PDP_PMT_STAT_CD_T STA On STA.PMT_STAT_CD = PAY.PMT_STAT_CD Left Join
X000_Initiate_unique INI On INI.DOC_HDR_ID = PAY.EDOC Left Join
X000_Approve_unique APP On APP.DOC_HDR_ID = PAY.EDOC Left Join
X000_Account_line_unique ACC On ACC.FDOC_NBR = PAY.EDOC Left Join
X000_Note_unique NTE On NTE.EDOC = PAY.EDOC Left Join
X000_Attachment_unique ATT On ATT.EDOC = PAY.EDOC
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PAYMENT LIST WITH ALL INITIATORS
print("Build payments initiate...")
sr_file = "X001ab_Report_payments_initiate"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAYMENT.EDOC,
PAYMENT.CUST_PMT_DOC_NBR,
PAYMENT.PMT_GRP_ID,
PAYMENT.VENDOR_ID,
PAYMENT.PAYEE_NAME,
PAYMENT.PAYEE_TYPE,
PAYMENT.PAYEE_TYP_DESC,
PAYMENT.VENDOR_NAME,
PAYMENT.VENDOR_REG_NR,
PAYMENT.VENDOR_TAX_NR,
PAYMENT.VENDOR_BANK_NR,
PAYMENT.VENDOR_TYPE,
PAYMENT.VENDOR_TYPE_CALC,
PAYMENT.DISB_NBR,
PAYMENT.DISB_TS,
PAYMENT.PMT_DTL_ID,
PAYMENT.PMT_DT,
PAYMENT.PMT_STAT_CD,
PAYMENT.PAYMENT_STATUS,
PAYMENT.INV_NBR,
PAYMENT.REQS_NBR,
PAYMENT.PO_NBR,
PAYMENT.INV_DT,
PAYMENT.ORIG_INV_AMT,
PAYMENT.NET_PMT_AMT,
DOC.DOC_TYP_NM As DOC_TYPE,
Upper(DOC.LBL) As DOC_LABEL,
INIT.PRNCPL_ID AS INIT_EMP_NO,
INIT.NAME_ADDR AS INIT_EMP_NAME,
INIT.ACTN_DT AS INIT_DATE,
INIT.ACTN AS INIT_STATUS,
INIT.ANNOTN AS NOTE,
APPROVE.PRNCPL_ID AS APPROVE_EMP_NO,
APPROVE.NAME_ADDR AS APPROVE_EMP_NAME,
APPROVE.ACTN_DT AS APPROVE_DATE,
APPROVE.ACTN AS APPROVE_STATUS,
Cast(APPROVE.APP_COUNT As INT) As A_COUNT,
APPROVE.ANNOTN AS APPROVE_NOTE,
CASE
WHEN ACC.COUNT_LINES = 1 THEN ACC.COST_STRING
ELSE Cast(ACC.COUNT_LINES As TEXT)
END As ACC_COST_STRING,
ACC.FDOC_LINE_DESC As ACC_DESC
From
X001aa_Report_payments PAYMENT Left Join
KFS.X000_Document DOC On DOC.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Account_line_unique ACC On ACC.FDOC_NBR = PAYMENT.EDOC Left Join
X000_Initiate INIT On INIT.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Approve_unique APPROVE On APPROVE.DOC_HDR_ID = PAYMENT.EDOC
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PAYMENT LIST WITH ALL APPROVERS
print("Build payments approved...")
sr_file = "X001ac_Report_payments_approve"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAYMENT.EDOC,
PAYMENT.CUST_PMT_DOC_NBR,
PAYMENT.PMT_GRP_ID,
PAYMENT.VENDOR_ID,
PAYMENT.PAYEE_NAME,
PAYMENT.PAYEE_TYPE,
PAYMENT.PAYEE_TYP_DESC,
PAYMENT.VENDOR_NAME,
PAYMENT.VENDOR_REG_NR,
PAYMENT.VENDOR_TAX_NR,
PAYMENT.VENDOR_BANK_NR,
PAYMENT.VENDOR_TYPE,
PAYMENT.VENDOR_TYPE_CALC,
PAYMENT.DISB_NBR,
PAYMENT.DISB_TS,
PAYMENT.PMT_DTL_ID,
PAYMENT.PMT_DT,
PAYMENT.PMT_STAT_CD,
PAYMENT.PAYMENT_STATUS,
PAYMENT.INV_NBR,
PAYMENT.REQS_NBR,
PAYMENT.PO_NBR,
PAYMENT.INV_DT,
PAYMENT.ORIG_INV_AMT,
PAYMENT.NET_PMT_AMT,
DOC.DOC_TYP_NM As DOC_TYPE,
Upper(DOC.LBL) As DOC_LABEL,
INITIATE.PRNCPL_ID AS COMPLETE_EMP_NO,
INITIATE.NAME_ADDR AS COMPLETE_EMP_NAME,
INITIATE.ACTN_DT AS COMPLETE_DATE,
INITIATE.ACTN AS COMPLETE_STATUS,
Cast(INITIATE.COM_COUNT As INT) As I_COUNT,
APPROVE.PRNCPL_ID AS APPROVE_EMP_NO,
APPROVE.NAME_ADDR AS APPROVE_EMP_NAME,
APPROVE.ACTN_DT AS APPROVE_DATE,
APPROVE.ACTN AS APPROVE_STATUS,
PAYMENT.A_COUNT,
APPROVE.ANNOTN AS NOTE,
CASE
WHEN ACC.COUNT_LINES = 1 THEN ACC.COST_STRING
ELSE Cast(ACC.COUNT_LINES As TEXT)
END As ACC_COST_STRING,
ACC.FDOC_LINE_DESC As ACC_DESC
From
X001aa_Report_payments PAYMENT Left Join
KFS.X000_Document DOC On DOC.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Account_line_unique ACC On ACC.FDOC_NBR = PAYMENT.EDOC Left Join
X000_Approve APPROVE On APPROVE.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Initiate_unique INITIATE On INITIATE.DOC_HDR_ID = PAYMENT.EDOC
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PAYMENT LIST WITH ALL ACCOUNT LINES
print("Build payments account line...")
sr_file = "X001ad_Report_payments_accroute"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAYMENT.EDOC,
PAYMENT.CUST_PMT_DOC_NBR,
PAYMENT.PMT_GRP_ID,
PAYMENT.VENDOR_ID,
PAYMENT.PAYEE_NAME,
PAYMENT.PAYEE_TYPE,
PAYMENT.PAYEE_TYP_DESC,
PAYMENT.VENDOR_NAME,
PAYMENT.VENDOR_REG_NR,
PAYMENT.VENDOR_TAX_NR,
PAYMENT.VENDOR_BANK_NR,
PAYMENT.VENDOR_TYPE,
PAYMENT.VENDOR_TYPE_CALC,
PAYMENT.DISB_NBR,
PAYMENT.DISB_TS,
PAYMENT.PMT_DT,
PAYMENT.PMT_STAT_CD,
PAYMENT.PAYMENT_STATUS,
PAYMENT.INV_NBR,
PAYMENT.REQS_NBR,
PAYMENT.PO_NBR,
PAYMENT.INV_DT,
PAYMENT.ORIG_INV_AMT,
PAYMENT.NET_PMT_AMT,
DOC.DOC_TYP_NM As DOC_TYPE,
Upper(DOC.LBL) As DOC_LABEL,
INIT.PRNCPL_ID AS INIT_EMP_NO,
INIT.NAME_ADDR AS INIT_EMP_NAME,
INIT.ACTN_DT AS INIT_DATE,
INIT.ACTN AS INIT_STATUS,
Cast(INIT.COM_COUNT As INT) As I_COUNT,
INIT.ANNOTN AS COMPLETE_NOTE,
APPROVE.PRNCPL_ID AS APPROVE_EMP_NO,
APPROVE.NAME_ADDR AS APPROVE_EMP_NAME,
APPROVE.ACTN_DT AS APPROVE_DATE,
APPROVE.ACTN AS APPROVE_STATUS,
Cast(APPROVE.APP_COUNT As INT) As A_COUNT,
APPROVE.ANNOTN AS APPROVE_NOTE,
ACC.FDOC_LINE_NBR As ACC_LINE,
ACC.COST_STRING As ACC_COST_STRING,
ACC.AMOUNT As ACC_AMOUNT,
ACC.FDOC_LINE_DESC As ACC_DESC,
ACC.ORG_NM,
ACC.ACCOUNT_NM,
ACC.FIN_OBJ_CD_NM,
ACC.ACCT_TYP_NM,
ACC.ACCT_FSC_OFC_UID
From
X001aa_Report_payments PAYMENT Left Join
KFS.X000_Document DOC On DOC.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Account_line ACC On ACC.FDOC_NBR = PAYMENT.EDOC Left Join
X000_Initiate_unique INIT On INIT.DOC_HDR_ID = PAYMENT.EDOC Left Join
X000_Approve_unique APPROVE On APPROVE.DOC_HDR_ID = PAYMENT.EDOC
Order By
VENDOR_NAME,
PAYEE_NAME,
PMT_DT,
EDOC,
FDOC_LINE_NBR
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
"""*****************************************************************************
PAYMENT REPORTS
*****************************************************************************"""
funcfile.writelog("PAYMENTS SUMMARY")
if l_debug:
print("PAYMENTS SUMMARY")
# VENDOR PAYMENT ANNUAL TOTALS
# NB NOTE REFERENCED IN TESTS - DO NOT CHANGE FIELD NAMES
# PER VENDOR LAST PAYMENT DATE
# TOTAL AMOUNT PAID TO EACH VENDOR
# THE NUMBER OF PAYMENTS
if l_debug:
print("Build vendor payments summary...")
sr_file = "X002aa_Report_payments_summary"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'%PERIOD_TEXT%' As YEAR,
PAY.VENDOR_ID,
PAY.PAYEE_NAME,
PAY.VENDOR_NAME,
PAY.PAYEE_TYPE,
PAY.PAYEE_TYP_DESC As PAYEE_TYPE_DESC,
PAY.PAYEE_OWNR_CD_CALC As OWNER_TYPE,
PAY.PAYEE_OWNR_DESC As OWNER_TYPE_DESC,
PAY.VENDOR_TYPE_CALC As VENDOR_TYPE,
VTY.LOOKUP_DESCRIPTION As VENDOR_TYPE_DESC,
Max(PAY.PMT_DT) As LAST_PMT_DT,
Sum(PAY.NET_PMT_AMT) As NET_PMT_AMT,
Count(PAY.VENDOR_ID) As TRAN_COUNT
From
X001aa_Report_payments PAY Left Join
KFS.X000_OWN_KFS_LOOKUPS VTY on VTY.LOOKUP_CODE = PAY.VENDOR_TYPE_CALC And
VTY.LOOKUP = "VENDOR TYPE CALC"
Group By
PAY.VENDOR_ID
"""
if s_period == "curr":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'CURRENT')
elif s_period == "prev":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'PREVIOUS')
else:
s_sql = s_sql.replace("%PERIOD_TEXT%", s_year)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PAYEE TYPE PER MONTH
if l_debug:
print("Build payee type payment summary per month...")
sr_file = "X002ab_Report_payments_typemon"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'%PERIOD_TEXT%' As YEAR,
PAYM.PAYEE_TYPE,
PAYM.PAYEE_TYP_DESC As PAYEE_DESC,
PAYM.PAYEE_OWNR_CD_CALC As OWNER_TYPE,
PAYM.PAYEE_OWNR_DESC As OWNER_DESC,
PAYM.VENDOR_TYPE_CALC As VENDOR_TYPE,
LOOK.LOOKUP_DESCRIPTION As VENDOR_DESC,
PAYM.DOC_TYPE,
PAYM.DOC_LABEL,
SubStr(PAYM.PMT_DT, 6, 2) As MONTH,
Cast(Count(PAYM.NET_PMT_AMT) As INT) As TRAN_COUNT,
Cast(Total(PAYM.NET_PMT_AMT) As REAL) As AMOUNT_SUM
From
X001aa_Report_payments PAYM Left Join
KFS.X000_Own_kfs_lookups LOOK On LOOK.LOOKUP_CODE = PAYM.VENDOR_TYPE_CALC And
LOOK.LOOKUP = "VENDOR TYPE CALC"
Group By
PAYM.PAYEE_TYPE,
PAYM.PAYEE_OWNR_CD_CALC,
PAYM.VENDOR_TYPE_CALC,
PAYM.DOC_TYPE,
SubStr(PAYM.PMT_DT, 6, 2)
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if s_period == "curr":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'CURRENT')
elif s_period == "prev":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'PREVIOUS')
else:
s_sql = s_sql.replace("%PERIOD_TEXT%", s_year)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PAYMENT TYPE ANNUAL SUMMARY TOTALS
if l_debug:
print("Payment type annual summary totals...")
sr_file = "X002ac_Report_payment_type_summary"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'%PERIOD_TEXT%' As YEAR,
REP.PAYEE_TYPE,
REP.PAYEE_TYP_DESC As PAYEE_TYPE_DESC,
REP.VENDOR_TYPE_CALC As VENDOR_TYPE,
VTY.LOOKUP_DESCRIPTION As VENDOR_TYPE_DESC,
REP.PAYEE_OWNR_CD_CALC As OWNER_TYPE,
REP.PAYEE_OWNR_DESC As OWNER_TYPE_DESC,
REP.DOC_TYPE,
REP.DOC_LABEL As DOC_TYPE_DESC,
Count(REP.EDOC) As TRAN_COUNT,
Total(REP.NET_PMT_AMT) As TRAN_TOTAL
From
X001aa_Report_payments REP Left Join
KFS.X000_OWN_KFS_LOOKUPS VTY on VTY.LOOKUP_CODE = REP.VENDOR_TYPE_CALC And
VTY.LOOKUP = "VENDOR TYPE CALC"
Group By
REP.PAYEE_TYPE,
REP.VENDOR_TYPE_CALC,
REP.PAYEE_OWNR_CD_CALC,
REP.DOC_TYPE
"""
if s_period == "curr":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'CURRENT')
elif s_period == "prev":
s_sql = s_sql.replace("%PERIOD_TEXT%", 'PREVIOUS')
else:
s_sql = s_sql.replace("%PERIOD_TEXT%", s_year)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# PREVIOUS MONTH PAYMENT DOCUMENT TYPE SUMMARY
if l_debug:
print("Monthly payment document type summary...")
sr_file = "X002ad_Report_payment_doctype_summary"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
SubStr(X001aa_Report_payments.VENDOR_ID, 1, 8) As VENDOR_ID,
X001aa_Report_payments.PAYEE_NAME As VENDOR_NAME,
X001aa_Report_payments.VENDOR_TYPE_CALC As VENDOR_TYPE,
X001aa_Report_payments.DOC_TYPE,
X001aa_Report_payments.DOC_LABEL,
Max(X001aa_Report_payments.DISB_TS) As LAST_DISB_DT,
Count(X001aa_Report_payments.EDOC) As TRAN_COUNT,
Total(X001aa_Report_payments.NET_PMT_AMT) As TRAN_TOTAL
From
X001aa_Report_payments
Where
SubStr(X001aa_Report_payments.DISB_TS,6,2) = "%PMONTH%"
Group By
SubStr(X001aa_Report_payments.VENDOR_ID, 1, 8),
X001aa_Report_payments.DOC_TYPE
Order By
TRAN_TOTAL Desc
"""
s_sql = s_sql.replace("%PMONTH%", funcdate.prev_month())
funcdate.prev_month()
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_export:
# Data export
sx_path = re_path + s_year + "/"
sx_file = "Creditor_report_002ad_doc_type_summ_"
sx_file_dated = sx_file + funcdate.prev_month()
if l_debug:
print("Export current year assignments..." + sx_path +
sx_file_dated)
# Read the header data
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# Write the data
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file, s_head)
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file_dated,
s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file_dated)
""" ****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
so_conn.commit()
so_conn.close()
# Close the log writer *********************************************************
funcfile.writelog("-------------------------------")
funcfile.writelog("COMPLETED: B006_KFS_PERIOD_LIST")
return
def 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 vss_period_list(s_period="curr"):
"""
Script to build standard KFS lists
:type s_period: str: The financial period (curr, prev or year)
:return: Nothing
"""
"""*************************************************************************
ENVIRONMENT
*************************************************************************"""
print("ENVIRONMENT")
funcfile.writelog("ENVIRONMENT")
# DECLARE VARIABLES
l_debug: bool = True
so_path: str = "W:/Vss/" # Source database path
if s_period == "curr":
s_year = funcdate.cur_year()
so_file = "Vss_curr.sqlite" # Source database
elif s_period == "prev":
s_year = funcdate.prev_year()
so_file = "Vss_prev.sqlite" # Source database
else:
s_year = s_period
so_file = "Vss_" + s_year + ".sqlite" # Source database
re_path: str = "R:/Vss/" # Results
ed_path: str = "S:/_external_data/" # External data location
s_sql: str = "" # SQL statements
l_export: bool = False # Export files
# LOG
print("--------------------")
print("B007_VSS_PERIOD_LIST")
print("--------------------")
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B007_VSS_PERIOD_LIST")
funcfile.writelog("----------------------------")
# MESSAGE
if funcconf.l_mess_project:
funcsms.send_telegram(
"", "administrator",
"<b>B007 Student " + s_year + " period lists</b>")
# OPEN 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:/Vss/Vss.sqlite' AS 'VSS'")
funcfile.writelog("%t ATTACH DATABASE: VSS.SQLITE")
"""*************************************************************************
BUILD STANDARD LOOKUP TABLES
*************************************************************************"""
print("BUILD STANDARD LOOKUP TABLES")
funcfile.writelog("BUILD STANDARD LOOKUP TABLES")
# CREATE STUDENT MODULE RESULT MASTER
sr_file = "X000_Student_module_result"
print("Build student module result master...")
s_sql = "Create Table " + sr_file + " As " + """
Select
RESM.KFINALMODULERESULTID,
RESM.KENROLSTUDID,
RESM.KSTUDBUSENTID,
RESM.KSTUDASSESID,
RESM.FRESULTMASTERID,
RESM.FRESULTCODEID,
Upper(RESC.LONG) As RESULT_TYPE,
RESM.FMARKTYPECODEID,
Upper(MARC.LONG) As MARK_TYPE,
RESM.DATEACHIEVED,
RESM.MARKACHIEVED,
RESM.ISPARTICIPATIONMARK,
RESM.OPPNO,
RESM.ISMARKCHANGED,
RESM.LOCKSTAMP,
RESM.AUDITDATETIME,
RESM.FAUDITSYSTEMFUNCTIONID,
RESM.FAUDITUSERCODE
From
FINALMODULERESULT RESM Left Join
VSS.X000_Codedescription RESC On RESC.KCODEDESCID = RESM.FRESULTCODEID Left Join
VSS.X000_Codedescription MARC On MARC.KCODEDESCID = RESM.FMARKTYPECODEID
Order By
KSTUDBUSENTID,
KENROLSTUDID,
DATEACHIEVED
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# CREATE STUDENT MODULE PARTICIPATION RESULT MASTER
sr_file = "X000_Student_module_result_participate"
print("Build student module participation result master...")
s_sql = "Create View " + sr_file + " As " + """
Select
RESU.KENROLSTUDID,
RESU.KSTUDBUSENTID,
Case
When Cast(RESU.MARKACHIEVED As INT) != 0 And RESU.FRESULTCODEID = 0 Then ' MARK (' || Trim(RESU.MARKACHIEVED) || ')'
When Cast(RESU.MARKACHIEVED As INT) != 0 Then Trim(RESU.RESULT_TYPE) || ' (' || Trim(RESU.MARKACHIEVED) || ')'
When RESU.FRESULTCODEID = 0 Then 'NONE'
Else RESU.RESULT_TYPE
End As PART_RESU,
Max(RESU.DATEACHIEVED) As DATEACHIEVED
From
X000_Student_module_result RESU
Where
RESU.MARK_TYPE Like ('PART%')
Group By
RESU.KENROLSTUDID
;"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# CREATE STUDENT MODULE EXAM RESULT MASTER
sr_file = "X000_Student_module_result_exam"
print("Build student module exam result master...")
s_sql = "Create View " + sr_file + " As " + """
Select
RESU.KENROLSTUDID,
RESU.KSTUDBUSENTID,
Case
When Cast(RESU.MARKACHIEVED As INT) != 0 And RESU.FRESULTCODEID = 0 Then ' MARK (' || Trim(RESU.MARKACHIEVED) || ')'
When Cast(RESU.MARKACHIEVED As INT) != 0 Then Trim(RESU.RESULT_TYPE) || ' (' || Trim(RESU.MARKACHIEVED) || ')'
When RESU.FRESULTCODEID = 0 Then 'NONE'
Else RESU.RESULT_TYPE
End As EXAM_RESU,
Max(RESU.DATEACHIEVED) As DATEACHIEVED
From
X000_Student_module_result RESU
Where
RESU.MARK_TYPE Like ('FINAL EXAM%')
Group By
RESU.KENROLSTUDID
;"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: " + sr_file)
# CREATE STUDENT MODULE FINA RESULT MASTER
sr_file = "X000_Student_module_result_final"
print("Build student module final result master...")
s_sql = "Create View " + sr_file + " As " + """
Select
RESU.KENROLSTUDID,
RESU.KSTUDBUSENTID,
Case
When Cast(RESU.MARKACHIEVED As INT) != 0 And RESU.FRESULTCODEID = 0 Then ' MARK (' || Trim(RESU.MARKACHIEVED) || ')'
When Cast(RESU.MARKACHIEVED As INT) != 0 Then Trim(RESU.RESULT_TYPE) || ' (' || Trim(RESU.MARKACHIEVED) || ')'
When RESU.FRESULTCODEID = 0 Then 'NONE'
Else RESU.RESULT_TYPE
End As FINAL_RESU,
Max(RESU.DATEACHIEVED) As DATEACHIEVED
From
X000_Student_module_result RESU
Where
RESU.MARK_TYPE Like ('FINAL MARK%')
Group By
RESU.KENROLSTUDID
;"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: " + sr_file)
"""*************************************************************************
BUILD STUDENTS
*************************************************************************"""
print("BUILD STUDENTS")
funcfile.writelog("BUILD STUDENTS")
funcstudent.studentlist(so_conn, re_path, s_period, True)
"""*************************************************************************
STUDENT ACCOUNT TRANSACTIONS
*************************************************************************"""
# BUILD CURRENT YEAR TRANSACTIONS
print("Build transactions...")
sr_file = "X010_Studytrans"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
TRAN.KACCTRANSID,
TRAN.FACCID,
STUD.FBUSENTID,
TRAN.FSERVICESITE,
TRAN.FDEBTCOLLECTIONSITE,
TRAN.TRANSDATE,
TRAN.AMOUNT,
TRAN.FTRANSMASTERID,
MAST.TRANSCODE,
MAST.DESCRIPTION_E,
MAST.DESCRIPTION_A,
TRAN.TRANSDATETIME,
TRAN.MONTHENDDATE,
TRAN.POSTDATEDTRANSDATE,
TRAN.FFINAIDSITEID,
BURS.FINAIDCODE,
BURS.FINAIDNAME,
BURS.FINAIDNAAM,
TRAN.FRESIDENCELOGID,
TRAN.FLEVYLOGID,
TRAN.FQUALLEVELAPID,
QUAL.QUALIFICATION,
QUAL.QUALIFICATION_NAME,
TRAN.FMODAPID,
MODU.FENROLMENTCATEGORYCODEID As ENROL_ID,
MODU.ENROL_CATEGORY,
MODU.MODULE,
MODU.MODULE_NAME,
TRAN.FPROGAPID,
TRAN.FENROLPRESID,
TRAN.FRESIDENCEID,
TRAN.FRECEIPTID,
TRAN.FROOMTYPECODEID,
TRAN.REFERENCENO,
TRAN.FSUBACCTYPECODEID,
TRAN.FDEPOSITCODEID,
TRAN.FDEPOSITTYPECODEID,
TRAN.FVARIABLEAMOUNTTYPECODEID,
TRAN.FDEPOSITTRANSTYPECODEID,
TRAN.RESIDENCETRANSTYPE,
TRAN.FSTUDYTRANSTYPECODEID,
TRAN.ISSHOWN,
TRAN.ISCREATEDMANUALLY,
TRAN.FTRANSINSTID,
TRAN.FMONTHENDORGUNITNO,
TRAN.LOCKSTAMP,
TRAN.AUDITDATETIME,
TRAN.FAUDITSYSTEMFUNCTIONID,
SYSF.SYSTEM_DESC,
TRAN.FAUDITUSERCODE,
USER.FUSERBUSINESSENTITYID,
TRAN.FORIGINSYSTEMFUNCTIONID,
TRAN.FPAYMENTREQUESTID
From
STUDYTRANS TRAN Left Join
VSS.STUDACC STUD ON STUD.KACCID = TRAN.FACCID Left Join
VSS.X000_Transmaster MAST ON MAST.KTRANSMASTERID = TRAN.FTRANSMASTERID Left Join
VSS.X000_Qualifications QUAL On QUAL.KENROLMENTPRESENTATIONID = TRAN.FENROLPRESID Left Join
VSS.X000_Modules MODU On MODU.KENROLMENTPRESENTATIONID = TRAN.FENROLPRESID Left Join
VSS.X004_Bursaries BURS ON BURS.KFINAIDSITEID = TRAN.FFINAIDSITEID Left Join
VSS.SYSTEMUSER USER ON USER.KUSERCODE = TRAN.FAUDITUSERCODE Left Join
VSS.X000_Systemfunction SYSF On SYSF.KSYSTEMFUNCTIONID = TRAN.FAUDITSYSTEMFUNCTIONID
Order By
TRAN.TRANSDATETIME
"""
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> Transactions")
# Close the connection *********************************************************
so_conn.close()
# Close the log writer *********************************************************
funcfile.writelog("-------------------------")
funcfile.writelog("COMPLETED: B007_VSS_PERIOD_LIST")
return
def robot_report_person_leave(s_nwu: str = "",
s_name: str = "",
s_mail: str = ""):
"""
REPORT EMPLOYEE PERSON LEAVE
:param s_nwu: NWU Number
:param s_name: The name of the requester / recipient
:param s_mail: The requester mail address
:return: str: Info in message format
"""
# IMPORT PYTHON MODULES
import sqlite3
from datetime import datetime
# IMPORT OWN MODULES
from _my_modules import funccsv
from _my_modules import funcdate
from _my_modules import funcfile
from _my_modules import funcmail
from _my_modules import funcsms
from _my_modules import funcstat
# DECLARE VARIABLES
l_debug: bool = True
"""*************************************************************************
ENVIRONMENT
*************************************************************************"""
if l_debug:
print("ENVIRONMENT")
# DECLARE VARIABLES
s_description: str = "Leave report"
so_path: str = "W:/People_leave/" # Source database path
so_file: str = "People_leave.sqlite" # Source database
re_path: str = "R:/People/" + funcdate.cur_year() + "/" # Results
l_mess: bool = funcconf.l_mess_project
# l_mess: bool = False
l_mailed: bool = False
# LOG
if l_debug:
print(s_function.upper())
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: " + s_function.upper())
funcfile.writelog("-" * len("script: " + s_function))
funcfile.writelog("%t " + s_description + " for " + s_nwu +
" requested by " + s_name)
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator",
"<b>" + s_function.upper() + "</b>")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
funcfile.writelog("OPEN THE DATABASES")
if l_debug:
print("OPEN THE DATABASES")
# OPEN THE WORKING DATABASE
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
# ATTACH DATA SOURCES
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
""" ****************************************************************************
BUILD THE LEAVE REPORT
*****************************************************************************"""
funcfile.writelog("BUILD THE LEAVE REPORT")
if l_debug:
print("BUILD THE LEAVE REPORT")
# OBTAIN THE NAME OF THE PERSON
s_lookup_name = funcfile.get_field_value(
so_curs, "PEOPLE.X000_PEOPLE",
"name_address||' ('||preferred_name||')' ",
"employee_number = '" + s_nwu + "'")
if l_debug:
print("FIELD LOOKUP: " + s_name)
s_message: str = s_description + " for <b>" + s_lookup_name + '(' + s_nwu + ")</b>."
# BUILD THE TABLE
if l_debug:
print("Build leave table...")
s_file_prefix: str = "Y000_"
s_file_name: str = "report_leave_all"
sr_file = s_file_prefix + s_file_name
so_curs.execute("Drop table if exists " + sr_file)
s_sql = "Create table " + sr_file + " AS " + """
Select
aa.ABSENCE_ATTENDANCE_ID,
aa.EMPLOYEE_NUMBER,
pe.name_address As EMPLOYEE_NAME,
aa.BUSINESS_GROUP_ID,
aa.DATE_NOTIFICATION,
aa.DATE_START,
aa.DATE_END,
aa.ABSENCE_DAYS,
aa.ABSENCE_ATTENDANCE_TYPE_ID,
at.NAME AS LEAVE_TYPE,
aa.ABS_ATTENDANCE_REASON_ID,
ar.NAME AS LEAVE_REASON,
ar.MEANING AS REASON_DESCRIP,
aa.AUTHORISING_PERSON_ID,
ap.name_address AS AUTHORISE_NAME,
aa.ABSENCE_HOURS,
aa.OCCURRENCE,
aa.SSP1_ISSUED,
aa.PROGRAM_APPLICATION_ID,
aa.ATTRIBUTE1,
aa.ATTRIBUTE2,
aa.ATTRIBUTE3,
aa.ATTRIBUTE4,
aa.ATTRIBUTE5,
aa.LAST_UPDATE_DATE,
aa.LAST_UPDATED_BY,
aa.LAST_UPDATE_LOGIN,
aa.CREATED_BY,
aa.CREATION_DATE,
aa.REASON_FOR_NOTIFICATION_DELAY,
aa.ACCEPT_LATE_NOTIFICATION_FLAG,
aa.OBJECT_VERSION_NUMBER,
at.INPUT_VALUE_ID,
at.ABSENCE_CATEGORY,
at.MEANING AS TYPE_DESCRIP
FROM
X100_Per_absence_attendances aa Left Join
X102_Per_absence_attendance_types at ON at.ABSENCE_ATTENDANCE_TYPE_ID = aa.ABSENCE_ATTENDANCE_TYPE_ID Left Join
X101_Per_abs_attendance_reasons ar ON ar.ABS_ATTENDANCE_REASON_ID = aa.ABS_ATTENDANCE_REASON_ID Left Join
PEOPLE.X000_PEOPLE pe On pe.employee_number = aa.EMPLOYEE_NUMBER Left Join
PEOPLE.X000_PEOPLE ap On pe.person_id = aa.AUTHORISING_PERSON_ID
Where
aa.EMPLOYEE_NUMBER = '%PERSON%'
Order By
aa.EMPLOYEE_NUMBER,
aa.DATE_START,
aa.DATE_END
;"""
s_sql = s_sql.replace("%PERSON%", s_nwu)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# RECORDS FOUND
if funcsys.tablerowcount(so_curs, sr_file) > 0:
# BUILD THE MESSAGE
l_records = funcstat.stat_list(
so_curs, sr_file,
"DATE_START||' '||DATE_END||' ('||ABSENCE_DAYS||') '||LEAVE_TYPE")
s_message += '\n\n'
s_message += 'Leave periods for this and previous year:'
s_data: str = ''
for item in l_records:
s_data = ''
for element in item:
s_data += element
print(s_data)
print(funcdate.cur_year())
if funcdate.cur_year() in s_data or funcdate.prev_year() in s_data:
s_message += '\n'
s_message += s_data
# EXPORT RECORDS
print("Export findings...")
sx_path = re_path
sx_file = sr_file + "_"
sx_file_dated = sx_file + funcdate.today_file()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file, s_head)
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file_dated,
s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file_dated)
# MAIL THE REPORT
s_report = "All DIY leave included!"
if s_name != "" and s_mail != "":
l_mailed = True
funcfile.writelog("%t Leave report mailed to " + s_mail)
if l_debug:
print("Send the report...")
s_body: str = "Attached please find leave report for " + s_lookup_name + " (" + s_nwu + ")."
s_body += "\n\r"
s_body += s_report
funcmail.send(s_name, s_mail, "E", s_description + " for " + s_nwu,
s_body, re_path, sx_file_dated + ".csv")
# DELETE THE MAILED FILE
if funcfile.file_delete(re_path, sx_file_dated):
funcfile.writelog("%t Leave deleted")
if l_debug:
print("Delete the report...")
else:
s_message += "\n\n"
s_message += "No leave on record."
# POPULATE THE RETURN MESSAGE
if l_mailed:
s_message += "\n\n"
s_message += "Report was mailed to " + s_mail + "."
s_return_message = s_message
"""*****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# CLOSE THE LOG WRITER
funcfile.writelog("-" * len("completed: " + s_function))
funcfile.writelog("COMPLETED: " + s_function.upper())
return s_return_message[0:4096]
def gl_test_transactions():
"""
Script to test GL transactions
:return: int
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# DECLARE VARIABLES
so_path = "W:/Kfs/" # Source database path
so_file = "Kfs_test_gl_transaction.sqlite" # Source database
ed_path = "S:/_external_data/" # External data path
re_path = "R:/Kfs/" # Results path
l_debug: bool = False
l_export = False
l_mess: bool = funcconf.l_mess_project
# l_mess: bool = False
l_record = True
# OPEN THE SCRIPT LOG FILE
if l_debug:
print("-------------------------")
print("C202_GL_TEST_TRANSACTIONS")
print("-------------------------")
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C202_GL_TEST_TRANSACTIONS")
funcfile.writelog("---------------------------------")
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator",
"<b>C202 Kfs gl transaction tests</b>")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
if l_debug:
print("OPEN THE DATABASES")
funcfile.writelog("OPEN THE DATABASES")
# OPEN THE WORKING DATABASE
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
# ATTACH DATA SOURCES
so_curs.execute("ATTACH DATABASE 'W:/Kfs/Kfs.sqlite' AS 'KFS'")
funcfile.writelog("%t ATTACH DATABASE: KFS.SQLITE")
so_curs.execute("ATTACH DATABASE 'W:/Kfs/Kfs_curr.sqlite' AS 'KFSCURR'")
funcfile.writelog("%t ATTACH DATABASE: KFS_CURR.SQLITE")
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
so_curs.execute("ATTACH DATABASE 'W:/Vss/Vss_curr.sqlite' AS 'VSSCURR'")
funcfile.writelog("%t ATTACH DATABASE: VSS_CURR.SQLITE")
""" ****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
if l_debug:
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
""" ****************************************************************************
PROFESSIONAL FEES GL MASTER FILE
*****************************************************************************"""
if l_debug:
print("PROFESSIONAL FEES GL MASTER FILE")
funcfile.writelog("PROFESSIONAL FEES GL MASTER FILE")
# OBTAIN GL PROFESSIONAL FEE TRANSACTIONS
if l_debug:
print("Obtain gl professional (2056) fee transactions...")
sr_file: str = "X001_gl_professional_fee"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
GL.*,
ACC.ACCT_MGR_UNVL_ID As ACC_MGR,
ACC.ACCT_SPVSR_UNVL_ID As ACC_SUP,
ACC.ACCT_FSC_OFC_UID As ACC_FIS,
CASE
WHEN ACC.ACCT_PHYS_CMP_CD = 'P' THEN 'POTCHEFSTROOM'
WHEN ACC.ACCT_PHYS_CMP_CD = 'V' THEN 'VAAL TRIANGLE'
WHEN ACC.ACCT_PHYS_CMP_CD = 'M' THEN 'MAFIKENG'
ELSE 'NWU'
END As ACC_CAMPUS
From
KFSCURR.X000_GL_trans GL Left Join
KFS.X000_Account ACC On ACC.ACCOUNT_NBR = GL.ACCOUNT_NBR
Where
GL.FS_DATABASE_DESC = 'KFS' And
Instr(GL.CALC_COST_STRING, '.2056') > 0
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# ADD PAYMENT DETAILS TO GL TRANSACTIONS
if l_debug:
print("Add payment details to transactions...")
sr_file: str = "X001_gl_professional_fee_pay"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
GL.*,
PAY.VENDOR_ID,
PAY.PAYEE_NAME As STUDENT_NAME,
PAY.INV_NBR,
PAY.PAYEE_TYP_DESC,
PAY.COMPLETE_EMP_NO As EMP_INI,
PAY.APPROVE_EMP_NO As EMP_APP
From
X001_gl_professional_fee GL Inner Join
KFSCURR.X001aa_Report_payments PAY On PAY.CUST_PMT_DOC_NBR = GL.FDOC_NBR And
PAY.NET_PMT_AMT = GL.CALC_AMOUNT
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
TEST PROFESSIONAL FEES PAID TO STUDENTS
*****************************************************************************"""
if l_debug:
print("PROFESSIONAL FEES PAID TO STUDENTS")
funcfile.writelog("PROFESSIONAL FEES PAID TO STUDENTS")
# DECLARE VARIABLES
i_finding_after: int = 0
# OBTAIN TEST DATA
if l_debug:
print("Obtain test data...")
sr_file: str = "X001aa_professional_fee_student"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
GL.*,
STUD.KSTUDBUSENTID As STUDENT,
CASE
WHEN STUD.FSITEORGUNITNUMBER = -1 THEN 'POT'
WHEN STUD.FSITEORGUNITNUMBER = -2 THEN 'VAA'
WHEN STUD.FSITEORGUNITNUMBER = -9 THEN 'MAF'
ELSE 'OTH'
END As LOC
From
X001_gl_professional_fee_pay GL Inner Join
VSSCURR.X001_student STUD On Substr(GL.VENDOR_ID,1,8) = STUD.KSTUDBUSENTID And
STUD.ISMAINQUALLEVEL = '1'
Order By
GL.TIMESTAMP
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# IDENTIFY FINDINGS
if l_debug:
print("Identify findings...")
sr_file = "X001ab_findings"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'NWU' As ORG,
CURR.LOC,
CURR.STUDENT,
CURR.FDOC_NBR,
CURR.CALC_COST_STRING,
EMP_INI,
ACC_MGR
From
X001aa_professional_fee_student CURR
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# COUNT THE NUMBER OF FINDINGS
i_finding_before: int = funcsys.tablerowcount(so_curs, sr_file)
if l_debug:
print("*** Found " + str(i_finding_before) + " exceptions ***")
funcfile.writelog("%t FINDING: " + str(i_finding_before) +
" PROF FEE PAID TO STUDENT finding(s)")
# GET PREVIOUS FINDINGS
sr_file = "X001ac_get_previous"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if l_debug:
print("Import previously reported findings...")
so_curs.execute("CREATE TABLE " + sr_file + """
(PROCESS TEXT,
FIELD1 INT,
FIELD2 TEXT,
FIELD3 TEXT,
FIELD4 TEXT,
FIELD5 TEXT,
DATE_REPORTED TEXT,
DATE_RETEST TEXT,
DATE_MAILED TEXT)
""")
s_cols = ""
co = open(ed_path + "202_reported.txt", "r")
co_reader = csv.reader(co)
# Read the COLUMN database data
for row in co_reader:
# Populate the column variables
if row[0] == "PROCESS":
continue
elif row[0] != "prof fee paid to student":
continue
else:
s_cols = "INSERT INTO " + sr_file + " VALUES('" + row[0] + "','" + row[1] + "','" + row[2] + "','" + \
row[
3] + "','" + row[4] + "','" + row[5] + "','" + row[6] + "','" + row[7] + "','" + row[
8] + "')"
so_curs.execute(s_cols)
so_conn.commit()
# Close the imported data file
co.close()
funcfile.writelog("%t IMPORT TABLE: " + ed_path +
"001_reported.txt (" + sr_file + ")")
# ADD PREVIOUS FINDINGS
sr_file = "X001ad_add_previous"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if l_debug:
print("Join previously reported to current findings...")
s_sql = "CREATE TABLE " + sr_file + " AS" + """
Select
FIND.*,
'prof fee paid to student' AS PROCESS,
'%TODAY%' AS DATE_REPORTED,
'%DAYS%' AS DATE_RETEST,
PREV.PROCESS AS PREV_PROCESS,
PREV.DATE_REPORTED AS PREV_DATE_REPORTED,
PREV.DATE_RETEST AS PREV_DATE_RETEST,
PREV.DATE_MAILED
From
X001ab_findings FIND Left Join
X001ac_get_previous PREV ON PREV.FIELD1 = FIND.STUDENT AND
PREV.FIELD2 = FIND.FDOC_NBR And
PREV.FIELD3 = FIND.CALC_COST_STRING And
PREV.DATE_RETEST >= Date('%TODAY%')
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = s_sql.replace("%TODAY%", funcdate.today())
s_sql = s_sql.replace("%DAYS%", funcdate.today_plusdays(366))
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD LIST TO UPDATE FINDINGS
# NOTE ADD CODE
sr_file = "X001ae_new_previous"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PREV.PROCESS,
PREV.STUDENT AS FIELD1,
PREV.FDOC_NBR AS FIELD2,
PREV.CALC_COST_STRING AS FIELD3,
'' AS FIELD4,
'' AS FIELD5,
PREV.DATE_REPORTED,
PREV.DATE_RETEST,
PREV.DATE_MAILED
From
X001ad_add_previous PREV
Where
PREV.PREV_PROCESS Is Null
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export findings to previous reported file
i_finding_after = funcsys.tablerowcount(so_curs, sr_file)
if i_finding_after > 0:
if l_debug:
print("*** " + str(i_finding_after) +
" Finding(s) to report ***")
sx_path = ed_path
sx_file = "202_reported"
# Read the header data
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# Write the data
if l_record:
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file,
s_head, "a", ".txt")
funcfile.writelog("%t FINDING: " + str(i_finding_after) +
" new finding(s) to export")
funcfile.writelog("%t EXPORT DATA: " + sr_file)
if l_mess:
s_desc = "Professional fee student"
funcsms.send_telegram(
'', 'administrator', '<b>' + str(i_finding_before) + '/' +
str(i_finding_after) + '</b> ' + s_desc)
else:
if l_debug:
print("*** No new findings to report ***")
funcfile.writelog("%t FINDING: No new findings to export")
# IMPORT OFFICERS FOR MAIL REPORTING PURPOSES
sr_file = "X001af_officer"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if i_finding_after > 0:
if l_debug:
print("Import reporting officers for mail purposes...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
OFFICER.LOOKUP,
OFFICER.LOOKUP_CODE AS CAMPUS,
OFFICER.LOOKUP_DESCRIPTION AS EMPLOYEE_NUMBER,
PEOP.NAME_ADDR As NAME,
PEOP.EMAIL_ADDRESS
From
PEOPLE.X000_OWN_HR_LOOKUPS OFFICER Left Join
PEOPLE.X002_PEOPLE_CURR PEOP ON
PEOP.EMPLOYEE_NUMBER = OFFICER.LOOKUP_DESCRIPTION
Where
OFFICER.LOOKUP = 'TEST_GL_OBJECT_PROF_FEE_PAID_TO_STUDENT_OFFICER'
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# IMPORT SUPERVISORS FOR MAIL REPORTING PURPOSES
sr_file = "X001ag_supervisor"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0 and i_finding_after > 0:
if l_debug:
print("Import reporting supervisors for mail purposes...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
SUPERVISOR.LOOKUP,
SUPERVISOR.LOOKUP_CODE AS CAMPUS,
SUPERVISOR.LOOKUP_DESCRIPTION AS EMPLOYEE_NUMBER,
PEOP.NAME_ADDR As NAME,
PEOP.EMAIL_ADDRESS
From
PEOPLE.X000_OWN_HR_LOOKUPS SUPERVISOR Left Join
PEOPLE.X002_PEOPLE_CURR PEOP ON
PEOP.EMPLOYEE_NUMBER = SUPERVISOR.LOOKUP_DESCRIPTION
Where
SUPERVISOR.LOOKUP = 'TEST_GL_OBJECT_PROF_FEE_PAID_TO_STUDENT_SUPERVISOR'
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# ADD CONTACT DETAILS TO FINDINGS
sr_file = "X001ah_detail"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0 and i_finding_after > 0:
if l_debug:
print("Add contact details to findings...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PREV.ORG,
PREV.LOC,
PREV.STUDENT,
MASTER.STUDENT_NAME,
PREV.FDOC_NBR,
MASTER.TRANSACTION_DT,
MASTER.CALC_AMOUNT,
MASTER.TRN_LDGR_ENTR_DESC,
MASTER.PAYEE_TYP_DESC,
MASTER.INV_NBR,
PREV.CALC_COST_STRING,
MASTER.ORG_NM,
MASTER.ACCOUNT_NM,
CAMP_OFF.EMPLOYEE_NUMBER As CAMP_OFF_NUMB,
CAMP_OFF.NAME As CAMP_OFF_NAME,
CASE
WHEN CAMP_OFF.EMPLOYEE_NUMBER <> '' THEN CAMP_OFF.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE CAMP_OFF.EMAIL_ADDRESS
END As CAMP_OFF_MAIL,
CAMP_OFF.EMAIL_ADDRESS As CAMP_OFF_MAIL2,
CAMP_SUP.EMPLOYEE_NUMBER As CAMP_SUP_NUMB,
CAMP_SUP.NAME As CAMP_SUP_NAME,
CASE
WHEN CAMP_SUP.EMPLOYEE_NUMBER <> '' THEN CAMP_SUP.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE CAMP_SUP.EMAIL_ADDRESS
END As CAMP_SUP_MAIL,
CAMP_SUP.EMAIL_ADDRESS As CAMP_SUP_MAIL2,
ORG_OFF.EMPLOYEE_NUMBER As ORG_OFF_NUMB,
ORG_OFF.NAME As ORG_OFF_NAME,
CASE
WHEN ORG_OFF.EMPLOYEE_NUMBER <> '' THEN ORG_OFF.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE ORG_OFF.EMAIL_ADDRESS
END As ORG_OFF_MAIL,
ORG_OFF.EMAIL_ADDRESS As ORG_OFF_MAIL2,
ORG_SUP.EMPLOYEE_NUMBER As ORG_SUP_NUMB,
ORG_SUP.NAME As ORG_SUP_NAME,
CASE
WHEN ORG_SUP.EMPLOYEE_NUMBER <> '' THEN ORG_SUP.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE ORG_SUP.EMAIL_ADDRESS
END As ORG_SUP_MAIL,
ORG_SUP.EMAIL_ADDRESS As ORG_SUP_MAIL2,
PREV.EMP_INI,
INI.NAME_ADDR As INAME,
PREV.EMP_INI||'@nwu.ac.za' As IMAIL,
INI.EMAIL_ADDRESS As IMAIL2,
PREV.ACC_MGR,
ACCM.NAME_ADDR As ANAME,
PREV.ACC_MGR||'@nwu.ac.za' As AMAIL,
ACCM.EMAIL_ADDRESS As AMAIL2
From
X001ad_add_previous PREV
Left Join X001af_officer CAMP_OFF On CAMP_OFF.CAMPUS = PREV.LOC
Left Join X001af_officer ORG_OFF On ORG_OFF.CAMPUS = PREV.ORG
Left Join X001ag_supervisor CAMP_SUP On CAMP_SUP.CAMPUS = PREV.LOC
Left Join X001ag_supervisor ORG_SUP On ORG_SUP.CAMPUS = PREV.ORG
Left Join X001aa_professional_fee_student MASTER On MASTER.STUDENT = PREV.STUDENT And
MASTER.FDOC_NBR = PREV.FDOC_NBR And
MASTER.CALC_COST_STRING = PREV.CALC_COST_STRING
Left Join PEOPLE.X002_PEOPLE_CURR INI On INI.EMPLOYEE_NUMBER = PREV.EMP_INI
Left Join PEOPLE.X002_PEOPLE_CURR ACCM On ACCM.EMPLOYEE_NUMBER = PREV.ACC_MGR
Where
PREV.PREV_PROCESS IS NULL
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD THE FINAL TABLE FOR EXPORT AND REPORT
sr_file = "X001ax_professional_fee_student"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if l_debug:
print("Build the final report")
if i_finding_before > 0 and i_finding_after > 0:
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'PROFESSIONAL FEE PAID TO STUDENT' As Audit_finding,
FIND.STUDENT As Student,
FIND.STUDENT_NAME As Name,
FIND.FDOC_NBR As Edoc,
FIND.TRANSACTION_DT As Date,
FIND.INV_NBR As Invoice,
FIND.CALC_AMOUNT As Amount,
FIND.PAYEE_TYP_DESC As Vendor_type,
CASE
WHEN Instr(FIND.TRN_LDGR_ENTR_DESC,'<VATI-0>') > 0 THEN Substr(FIND.TRN_LDGR_ENTR_DESC,9)
ELSE FIND.TRN_LDGR_ENTR_DESC
END As Description,
FIND.CALC_COST_STRING As Account,
FIND.ORG_NM As Organization,
FIND.ACCOUNT_NM As Account_name,
FIND.EMP_INI As Initiator,
FIND.INAME As Initiator_name,
FIND.IMAIL As Initiator_mail,
FIND.ACC_MGR As Acc_manager,
FIND.ANAME As Acc_manager_name,
FIND.AMAIL As Acc_manager_mail,
FIND.CAMP_OFF_NAME AS Responsible_Officer,
FIND.CAMP_OFF_NUMB AS Responsible_Officer_Numb,
FIND.CAMP_OFF_MAIL AS Responsible_Officer_Mail,
FIND.CAMP_SUP_NAME AS Supervisor,
FIND.CAMP_SUP_NUMB AS Supervisor_Numb,
FIND.CAMP_SUP_MAIL AS Supervisor_Mail,
FIND.ORG_OFF_NAME AS Org_Officer,
FIND.ORG_OFF_NUMB AS Org_Officer_Numb,
FIND.ORG_OFF_MAIL AS Org_Officer_Mail,
FIND.ORG_SUP_NAME AS Org_Supervisor,
FIND.ORG_SUP_NUMB AS Org_Supervisor_Numb,
FIND.ORG_SUP_MAIL AS Org_Supervisor_Mail
From
X001ah_detail FIND
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export findings
if l_export and funcsys.tablerowcount(so_curs, sr_file) > 0:
if l_debug:
print("Export findings...")
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = "Gltran_test_001ax_professional_fee_student_"
sx_file_dated = sx_file + funcdate.today_file()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file,
s_head)
funccsv.write_data(so_conn, "main", sr_file, sx_path,
sx_file_dated, s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
else:
s_sql = "CREATE TABLE " + sr_file + " (" + """
BLANK TEXT
);"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
"""*****************************************************************************
IA ACTUAL VS BUDGET
*****************************************************************************"""
# FILES NEEDED
# X000_GL_trans
# DEFAULT TRANSACTION OWNER PEOPLE
# DECLARE TEST VARIABLES
i_finding_before = 0
i_finding_after = 0
s_description = "IA Actual vs budget"
s_file_prefix: str = "X002a"
s_file_name: str = "ia_actual_vs_budget"
s_finding: str = "IA ACTUAL VS BUDGET"
s_report_file: str = "202_reported.txt"
# OBTAIN TEST RUN FLAG
if functest.get_test_flag(so_curs, "KFS", "TEST " + s_finding,
"RUN") == "FALSE":
if l_debug:
print('TEST DISABLED')
funcfile.writelog("TEST " + s_finding + " DISABLED")
else:
# LOG
funcfile.writelog("TEST " + s_finding)
if l_debug:
print("TEST " + s_finding)
# OBTAIN MASTER DATA
if l_debug:
print("Obtain master data...")
sr_file: str = s_file_prefix + "a_" + s_file_name
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "Create Table " + sr_file + " As " + """
Select
'NWU' ORG,
GL.FIN_COA_CD As LOC,
GL.ORG_NM,
GL.ACCOUNT_NM,
GL.CALC_COST_STRING,
GL.FIN_OBJ_CD_NM,
GL.FIN_BALANCE_TYP_CD,
Count(GL.FDOC_NBR) As Count_FDOC_NBR,
Total(GL.CALC_AMOUNT) As Total_CALC_AMOUNT
From
X000_GL_trans GL
Where
GL.ACCOUNT_NM Like ("(4532%")
Group By
GL.ACCOUNT_NM,
GL.CALC_COST_STRING,
GL.FIN_OBJ_CD_NM,
GL.FIN_BALANCE_TYP_CD
Order By
GL.ACCOUNT_NM,
GL.FIN_OBJ_CD_NM
;"""
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# IDENTIFY FINDINGS
if l_debug:
print("Identify findings...")
sr_file = s_file_prefix + "b_finding"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
GL.ORG,
GL.LOC,
GL.ORG_NM,
GL.ACCOUNT_NM,
GL.CALC_COST_STRING,
GL.FIN_OBJ_CD_NM,
Cast(Case
When ACT.Total_CALC_AMOUNT is null Then '0'
When ACT.Total_CALC_AMOUNT = '' Then '0'
Else ACT.Total_CALC_AMOUNT
End As Int) As ACTUAL,
Cast(Case
When BUD.Total_CALC_AMOUNT is null Then '0'
When BUD.Total_CALC_AMOUNT = '' Then '0'
Else BUD.Total_CALC_AMOUNT
End As Int) As BUDGET,
cast( ACT.Total_CALC_AMOUNT/BUD.Total_CALC_AMOUNT*100 As Real) As PERCENT
From
%FILEP%%FILEN% GL Left Join
%FILEP%%FILEN% ACT On ACT.CALC_COST_STRING = GL.CALC_COST_STRING
And ACT.FIN_BALANCE_TYP_CD = "AC" Left Join
%FILEP%%FILEN% BUD On BUD.CALC_COST_STRING = GL.CALC_COST_STRING
And BUD.FIN_BALANCE_TYP_CD = "CB"
Group By
GL.CALC_COST_STRING
Order By
GL.FIN_OBJ_CD_NM
;"""
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
s_sql = s_sql.replace("%FILEN%", "a_" + s_file_name)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# COUNT THE NUMBER OF FINDINGS
if l_debug:
print("Count the number of findings...")
i_finding_before: int = funcsys.tablerowcount(so_curs, sr_file)
funcfile.writelog("%t FINDING: " + str(i_finding_before) + " " +
s_finding + " finding(s)")
if l_debug:
print("*** Found " + str(i_finding_before) + " exceptions ***")
# GET PREVIOUS FINDINGS
if i_finding_before > 0:
functest.get_previous_finding(so_curs, ed_path, s_report_file,
s_finding, "TIITT")
if l_debug:
so_conn.commit()
# SET PREVIOUS FINDINGS
if i_finding_before > 0:
functest.set_previous_finding(so_curs)
if l_debug:
so_conn.commit()
# ADD PREVIOUS FINDINGS
sr_file = s_file_prefix + "d_addprev"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if l_debug:
print("Join previously reported to current findings...")
s_sql = "CREATE TABLE " + sr_file + " AS" + """
Select
FIND.*,
Lower('%FINDING%') AS PROCESS,
'%TODAY%' AS DATE_REPORTED,
'%DATETEST%' AS DATE_RETEST,
PREV.PROCESS AS PREV_PROCESS,
PREV.DATE_REPORTED AS PREV_DATE_REPORTED,
PREV.DATE_RETEST AS PREV_DATE_RETEST,
PREV.REMARK
From
%FILEP%b_finding FIND Left Join
Z001ab_setprev PREV ON PREV.FIELD1 = FIND.CALC_COST_STRING And
PREV.FIELD2 = FIND.ACTUAL And
PREV.FIELD3 = FIND.BUDGET
;"""
s_sql = s_sql.replace("%FINDING%", s_finding)
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
s_sql = s_sql.replace("%TODAY%", funcdate.today())
s_sql = s_sql.replace("%DATETEST%", funcdate.cur_monthendnext(0))
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# BUILD LIST TO UPDATE FINDINGS
sr_file = s_file_prefix + "e_newprev"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0:
if l_debug:
print("Build list to update findings...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PREV.PROCESS,
PREV.CALC_COST_STRING AS FIELD1,
PREV.ACTUAL AS FIELD2,
PREV.BUDGET AS FIELD3,
'' AS FIELD4,
'' AS FIELD5,
PREV.DATE_REPORTED,
PREV.DATE_RETEST,
PREV.REMARK
From
%FILEP%d_addprev PREV
Where
PREV.PREV_PROCESS Is Null Or
PREV.DATE_REPORTED > PREV.PREV_DATE_RETEST And PREV.REMARK = ""
;"""
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
if l_debug:
so_conn.commit()
# Export findings to previous reported file
i_finding_after = funcsys.tablerowcount(so_curs, sr_file)
if i_finding_after > 0:
if l_debug:
print("*** " + str(i_finding_after) +
" Finding(s) to report ***")
sx_path = ed_path
sx_file = s_report_file[:-4]
# Read the header data
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
# Write the data
if l_record:
funccsv.write_data(so_conn, "main", sr_file, sx_path,
sx_file, s_head, "a", ".txt")
funcfile.writelog("%t FINDING: " + str(i_finding_after) +
" new finding(s) to export")
funcfile.writelog("%t EXPORT DATA: " + sr_file)
if l_mess:
funcsms.send_telegram(
'', 'administrator', '<b>' + str(i_finding_before) +
'/' + str(i_finding_after) + '</b> ' + s_description)
else:
funcfile.writelog("%t FINDING: No new findings to export")
if l_debug:
print("*** No new findings to report ***")
# IMPORT OFFICERS FOR MAIL REPORTING PURPOSES
if i_finding_before > 0 and i_finding_after > 0:
functest.get_officer(so_curs, "KFS",
"TEST " + s_finding + " OFFICER")
so_conn.commit()
# IMPORT SUPERVISORS FOR MAIL REPORTING PURPOSES
if i_finding_before > 0 and i_finding_after > 0:
functest.get_supervisor(so_curs, "KFS",
"TEST " + s_finding + " SUPERVISOR")
so_conn.commit()
# ADD CONTACT DETAILS TO FINDINGS
sr_file = s_file_prefix + "h_detail"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0 and i_finding_after > 0:
if l_debug:
print("Add contact details to findings...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PREV.ORG,
PREV.LOC,
PREV.ORG_NM,
PREV.ACCOUNT_NM,
PREV.CALC_COST_STRING,
PREV.FIN_OBJ_CD_NM,
PREV.ACTUAL,
PREV.BUDGET,
PREV.PERCENT,
CAMP_OFF.EMPLOYEE_NUMBER AS CAMP_OFF_NUMB,
CAMP_OFF.NAME_ADDR AS CAMP_OFF_NAME,
CAMP_OFF.EMAIL_ADDRESS AS CAMP_OFF_MAIL1,
CASE
WHEN CAMP_OFF.EMPLOYEE_NUMBER != '' THEN CAMP_OFF.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE CAMP_OFF.EMAIL_ADDRESS
END AS CAMP_OFF_MAIL2,
CAMP_SUP.EMPLOYEE_NUMBER AS CAMP_SUP_NUMB,
CAMP_SUP.NAME_ADDR AS CAMP_SUP_NAME,
CAMP_SUP.EMAIL_ADDRESS AS CAMP_SUP_MAIL1,
CASE
WHEN CAMP_SUP.EMPLOYEE_NUMBER != '' THEN CAMP_SUP.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE CAMP_SUP.EMAIL_ADDRESS
END AS CAMP_SUP_MAIL2,
ORG_OFF.EMPLOYEE_NUMBER AS ORG_OFF_NUMB,
ORG_OFF.NAME_ADDR AS ORG_OFF_NAME,
ORG_OFF.EMAIL_ADDRESS AS ORG_OFF_MAIL1,
CASE
WHEN ORG_OFF.EMPLOYEE_NUMBER != '' THEN ORG_OFF.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE ORG_OFF.EMAIL_ADDRESS
END AS ORG_OFF_MAIL2,
ORG_SUP.EMPLOYEE_NUMBER AS ORG_SUP_NUMB,
ORG_SUP.NAME_ADDR AS ORG_SUP_NAME,
ORG_SUP.EMAIL_ADDRESS AS ORG_SUP_MAIL1,
CASE
WHEN ORG_SUP.EMPLOYEE_NUMBER != '' THEN ORG_SUP.EMPLOYEE_NUMBER||'@nwu.ac.za'
ELSE ORG_SUP.EMAIL_ADDRESS
END AS ORG_SUP_MAIL2,
AUD_OFF.EMPLOYEE_NUMBER As AUD_OFF_NUMB,
AUD_OFF.NAME_ADDR As AUD_OFF_NAME,
AUD_OFF.EMAIL_ADDRESS As AUD_OFF_MAIL,
AUD_SUP.EMPLOYEE_NUMBER As AUD_SUP_NUMB,
AUD_SUP.NAME_ADDR As AUD_SUP_NAME,
AUD_SUP.EMAIL_ADDRESS As AUD_SUP_MAIL
From
%FILEP%d_addprev PREV Left Join
Z001af_officer CAMP_OFF On CAMP_OFF.CAMPUS = PREV.LOC Left Join
Z001af_officer ORG_OFF On ORG_OFF.CAMPUS = PREV.ORG Left Join
Z001af_officer AUD_OFF On AUD_OFF.CAMPUS = 'AUD' Left Join
Z001ag_supervisor CAMP_SUP On CAMP_SUP.CAMPUS = PREV.LOC Left Join
Z001ag_supervisor ORG_SUP On ORG_SUP.CAMPUS = PREV.ORG Left Join
Z001ag_supervisor AUD_SUP On AUD_SUP.CAMPUS = 'AUD'
Where
PREV.PREV_PROCESS Is Null Or
PREV.DATE_REPORTED > PREV.PREV_DATE_RETEST And PREV.REMARK = ""
;"""
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
s_sql = s_sql.replace("%FILEN%", s_file_name)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD THE FINAL TABLE FOR EXPORT AND REPORT
sr_file = s_file_prefix + "x_" + s_file_name
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if i_finding_before > 0 and i_finding_after > 0:
if l_debug:
print("Build the final report")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
'%FIND%' As Audit_finding,
FIND.ORG As Organization,
FIND.LOC As Campus,
FIND.ORG_NM As Division,
FIND.ACCOUNT_NM As Account,
FIND.CALC_COST_STRING As Cost_string,
FIND.FIN_OBJ_CD_NM As Object_name,
FIND.ACTUAL As R_Actual,
FIND.BUDGET As R_Budget,
FIND.PERCENT As Per_Used,
FIND.CAMP_OFF_NAME AS Responsible_officer,
FIND.CAMP_OFF_NUMB AS Responsible_officer_numb,
FIND.CAMP_OFF_MAIL1 AS Responsible_officer_mail,
FIND.CAMP_OFF_MAIL2 AS Responsible_officer_mail_alt,
FIND.CAMP_SUP_NAME AS Supervisor,
FIND.CAMP_SUP_NUMB AS Supervisor_numb,
FIND.CAMP_SUP_MAIL1 AS Supervisor_mail,
FIND.ORG_OFF_NAME AS Org_officer,
FIND.ORG_OFF_NUMB AS Org_officer_numb,
FIND.ORG_OFF_MAIL1 AS Org_officer_mail,
FIND.ORG_SUP_NAME AS Org_supervisor,
FIND.ORG_SUP_NUMB AS Org_supervisor_numb,
FIND.ORG_SUP_MAIL1 AS Org_supervisor_mail,
FIND.AUD_OFF_NAME AS Audit_officer,
FIND.AUD_OFF_NUMB AS Audit_officer_numb,
FIND.AUD_OFF_MAIL AS Audit_officer_mail,
FIND.AUD_SUP_NAME AS Audit_supervisor,
FIND.AUD_SUP_NUMB AS Audit_supervisor_numb,
FIND.AUD_SUP_MAIL AS Audit_supervisor_mail
From
%FILEP%h_detail FIND
;"""
s_sql = s_sql.replace("%FIND%", s_finding)
s_sql = s_sql.replace("%FILEP%", s_file_prefix)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# Export findings
if l_export and funcsys.tablerowcount(so_curs, sr_file) > 0:
if l_debug:
print("Export findings...")
sx_path = re_path + funcdate.cur_year() + "/"
sx_file = s_file_prefix + "_" + s_finding.lower() + "_"
sx_file_dated = sx_file + funcdate.today_file()
s_head = funccsv.get_colnames_sqlite(so_conn, sr_file)
funccsv.write_data(so_conn, "main", sr_file, sx_path, sx_file,
s_head)
funccsv.write_data(so_conn, "main", sr_file, sx_path,
sx_file_dated, s_head)
funcfile.writelog("%t EXPORT DATA: " + sx_path + sx_file)
else:
s_sql = "CREATE TABLE " + sr_file + " (" + """
BLANK TEXT
);"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
""" ****************************************************************************
END OF SCRIPT
*****************************************************************************"""
if l_debug:
print("END OF SCRIPT")
funcfile.writelog("END OF SCRIPT")
# CLOSE THE DATABASE CONNECTION
so_conn.commit()
so_conn.close()
# CLOSE THE LOG WRITER
funcfile.writelog("------------------------------------")
funcfile.writelog("COMPLETED: C202_GL_TEST_TRANSACTIONS")
return
def robot_name(s_list: str = "a", s_main: str = "", s_secondary: str = ""):
"""
SEARCH VSS.PARTY FOR NAMES, NUMBERS AND ID'S
:param s_list: List filter
:param s_main: Main search parameter
:param s_secondary: Secondary search parameter
:return: str: Info in message format
"""
# VARIABLES
s_main = s_main.replace(".", " ")
s_return_message: str = ""
l_debug: bool = True
"""*************************************************************************
ENVIRONMENT
*************************************************************************"""
funcfile.writelog("ENVIRONMENT")
if l_debug:
print("ENVIRONMENT")
# DECLARE VARIABLES
so_path: str = "W:/Vss/" # Source database path
so_file: str = "Vss.sqlite" # Source database
re_path: str = "R:/Vss/" # Results
ed_path: str = "S:/_external_data/" # External data location
s_sql: str = "" # SQL statements
l_export: bool = False # Export files
# l_mess: bool = funcconf.l_mess_project
l_mess: bool = False
# LOG
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: D001_ROBOT_NAME")
funcfile.writelog("-----------------------")
if l_debug:
print("---------------")
print("D001_ROBOT_NAME")
print("---------------")
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator", "<b>D001 ROBOT NAME</b>")
# OPEN DATABASE
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
# ATTACH DATA SOURCES
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
funcfile.writelog("%t ATTACH DATABASE: PEOPLE.SQLITE")
so_curs.execute("ATTACH DATABASE 'W:/Kfs/Kfs_curr.sqlite' AS 'KFSCURR'")
funcfile.writelog("%t ATTACH DATABASE: VSS_CURR.SQLITE")
so_curs.execute("ATTACH DATABASE 'W:/Vss/Vss_curr.sqlite' AS 'VSSCURR'")
funcfile.writelog("%t ATTACH DATABASE: VSS_CURR.SQLITE")
"""*****************************************************************************
DO THE SEARCH
*****************************************************************************"""
funcfile.writelog("DO THE SEARCH")
if l_debug:
print("DO THE SEARCH")
# BUILD THE WHERE CLAUSE
s_where = "(%MAIN1% %SECONDARY% %LIST%) or (%MAIN2% %SECONDARY% %LIST%) or (%MAIN3% %SECONDARY% %LIST%)"
# MAIN PARAMETER FULL_NAME
if s_main[0:1] == "*":
s_main1 = "PAR.FULL_NAME != ''"
else:
s_main1 = "PAR.FULL_NAME Like('%" + s_main.upper() + "%')"
# MAIN PARAMETER NWU NUMBER
if s_main[0:1] == "*":
s_main2 = "PAR.KBUSINESSENTITYID > 0"
else:
s_main2 = "CAST(PAR.KBUSINESSENTITYID AS TEXT) Like('%" + s_main.upper(
) + "%')"
# MAIN PARAMETER ID NUMBER
if s_main[0:1] == "*":
s_main3 = "PAR.IDNO != ''"
else:
s_main3 = "PAR.IDNO Like('%" + s_main.upper() + "%')"
# SECONDARY PARAMETER NICKNAME
if s_secondary[0:1] == "*":
s_secondary1 = ""
else:
s_secondary1 = "and PAR.NICKNAME Like('%" + s_secondary.upper() + "%')"
# TODO Sort out payee only searches. Take very long with no end result
# LIST PARAMETER
if s_list[0:1] == "e": # Employee
s_list1 = "and PEO.EMPLOYEE_NUMBER Is Not Null"
# elif s_list[0:1] == "p": # Payee
# s_list1 = "and KFS.VENDOR_ID Is Not Null"
elif s_list[0:1] == "s": # Student
s_list1 = "and STU.KSTUDBUSENTID Is Not Null"
elif s_list[0:1] == "v": # Vendor
s_list1 = "and PAR.PARTYTYPE = 2"
else:
s_list1 = ""
s_where = s_where.replace("%MAIN1%", s_main1)
s_where = s_where.replace("%MAIN2%", s_main2)
s_where = s_where.replace("%MAIN3%", s_main3)
s_where = s_where.replace("%SECONDARY%", s_secondary1)
s_where = s_where.replace("%LIST%", s_list1)
if l_debug:
print(s_where)
# LOCATE THE RECORDS
sr_file = "X000_Party_lookup"
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
if s_where != "":
funcfile.writelog("PARTY LOOKUP")
if l_debug:
print("Party lookup...")
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
PAR.KBUSINESSENTITYID,
PAR.FULL_NAME,
PAR.TITLE,
PAR.NICKNAME,
PAR.MAIDENNAME,
PAR.IDNO,
PAR.PASSPORT,
PAR.STUDYPERMIT,
PAR.NATIONALITY,
CASE
WHEN PEO.EMPLOYEE_NUMBER Is Not Null THEN True
ELSE False
END AS EMPLOYEE,
CASE
WHEN STU.KSTUDBUSENTID Is Not Null THEN True
ELSE False
END AS STUDENT,
CASE
WHEN KFS.VENDOR_ID Is Not Null THEN True
ELSE False
END AS VENDOR,
PAR.PARTYTYPE,
KFS.YEAR,
KFS.VENDOR_ID
From
X000_Party PAR Left Join
PEOPLE.X002_PEOPLE_CURR PEO On PEO.EMPLOYEE_NUMBER = CAST(PAR.KBUSINESSENTITYID AS TEXT) Left Join
VSSCURR.X001_Student STU On STU.KSTUDBUSENTID = PAR.KBUSINESSENTITYID Left Join
KFSCURR.X002aa_Report_payments_summary KFS ON SUBSTR(KFS.VENDOR_ID,1,8) = CAST(PAR.KBUSINESSENTITYID AS TEXT)
Where
%WHERE%
Order By
PAR.SURNAME,
PAR.INITIALS,
PAR.NICKNAME,
PAR.IDNO Desc
"""
s_sql = s_sql.replace("%WHERE%", s_where)
so_curs.execute(s_sql)
funcfile.writelog("%t BUILD TABLE: " + sr_file)
else:
s_sql = "CREATE TABLE " + sr_file + " (" + """
BLANK TEXT
);"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# BUILD MESSAGE FROM DATA
i_records = funcsys.tablerowcount(so_curs, sr_file)
if l_debug:
print(i_records)
if i_records > 0:
"""
row[0] = KBUSINESSENTITYID
row[1] = FULL_NAME
row[2] = TITLE
row[3] = NICKNAME
row[4] = MAIDENNAME
row[5] = IDNO
row[6] = PASSPORT
row[7] = STUDYPERMIT
row[8] = NATIONALITY
row[9] = EMPLOYEE
row[10] = STUDENT
row[11] = VENDOR
row[12] = PARTYTYPE
"""
s_return_message = ""
for row in so_curs.execute("SELECT * from " + sr_file).fetchall():
s_return_message += "\n"
if row[12] == 2: # Do not split vendor name
s_return_message += "<b>" + row[1] + "</b>"
if row[3]:
s_return_message += "\n " + row[3]
else: # Split name
s_return_message += "<b>" + row[1][0:row[1].find(")") +
1] + "</b>\n"
if " " + row[1][row[1].find(")") + 2:]:
s_return_message += " " + row[1][row[1].find(")") + 2:]
if row[9] == 1 or row[10] == 1 or row[11] == 1:
s_return_message += "\n"
if row[9] == 1:
s_return_message += " -employee"
if row[10] == 1:
s_return_message += " -student"
if row[11] == 1:
s_return_message += " -payee"
s_return_message += "\n /nwu " + str(row[0])
if row[5]: # Show only if not null
s_return_message += "\n /id " + str(row[5])
if len(s_return_message) > 3900:
break
if len(s_return_message) > 3900:
s_return_message = "<b>Found " + str(i_records) + " records.</b>\n\n" + \
"Found too many records to display. Please refine your search!\n\n" + \
s_return_message
else:
s_return_message = "<b>Found " + str(
i_records) + " records.</b>\n\n" + s_return_message
"""*****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# COMMIT DATA
so_conn.commit()
# CLOSE THE DATABASE CONNECTION
so_conn.close()
# CLOSE THE LOG WRITER
funcfile.writelog("--------------------------")
funcfile.writelog("COMPLETED: D001_ROBOT_NAME")
return s_return_message[0:4096]
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
