def ErrMessage(e, l_mail=False, s_subject='', s_body=''):
"""
Error message handler.
:param e: Error object
:param l_mail: Send mail and message
:param s_subject: Mail subject
:param s_body: Mail body
:return: Nothing
"""
# IMPORT OWN MODULES
from _my_modules import funcconf
from _my_modules import funcfile
from _my_modules import funcmail
from _my_modules import funcsms
# DECLARE VARIABLES
s_mess = str(e).replace("'", "")
s_mess = s_mess.replace('"', '')
s_mess = s_mess.replace(':', '')
s_mess = s_mess.replace('[', '')
s_mess = s_mess.replace(']', '')
s_mess = s_mess.replace('(', '')
s_mess = s_mess.replace(')', '')
if not funcconf.l_mail_project:
l_mail = False
# DISPLAY
print(s_mess)
print("E: ", e)
print("TYPE(E): ", type(e))
print("TYPE(E)NAME: ", type(e).__name__)
print("JOIN(E.ARGS: ", e.args)
# DEFINE THE ERROR TEMPLATE AND MESSAGE
template = "Exception type {0} occurred. Arguments:\n{1!r}"
message = template.format(type(e).__name__, e.args)
print("MESSAGE: ", message)
# SEND MAIL
if l_mail and s_subject != '' and s_body != '':
# s_body1 = s_body + '\n' + type(e).__name__ + '\n' + "".join(e.args)
s_body1 = s_body + '\n' + s_mess
funcmail.Mail('std_fail_gmail', s_subject, s_body1)
# SEND MESSAGE
if funcconf.l_mess_project:
# s_body1 = s_body + ' | ' + type(e).__name__ + ' | ' + "".join(e.args)
s_body1 = s_body + ' | ' + s_mess
funcsms.send_telegram('', 'administrator', s_body1)
# WRITE ERROR TO LOG
funcfile.writelog("%t ERROR: " + type(e).__name__)
# funcfile.writelog("%t ERROR: " + "".join(e.args))
funcfile.writelog("%t ERROR: " + s_mess)
return
def ErrMessage(e, l_mail=False, s_subject='', s_body=''):
print(type(e))
print(e)
template = "An exception of type {0} occurred. Arguments:\n{1!r}"
message = template.format(type(e).__name__, e.args)
if l_mail and s_subject != '' and s_body != '':
s_body = s_body + '\n' + type(e).__name__ + '\n' + "".join(e.args)
funcmail.Mail('std_fail_gmail', s_subject, s_body)
funcfile.writelog("%t ERROR: " + type(e).__name__)
funcfile.writelog("%t ERROR: " + "".join(e.args))
return
def get_field_value(o_cursor, s_table='', s_field='', s_where=''):
"""
Function to obtain a field value from any table.
:param o_cursor: object: Database cursor
:param s_table: str: Table to query
:param s_field: str: Field value to return
:param s_where: str: Where clause
:return: str: Value of lookup field
"""
# IMPORT OWN MODULES
from _my_modules import funcfile
# DECLARE VARIABLES
l_debug: bool = False
# SET PREVIOUS FINDINGS
if l_debug:
print("Field value lookup...")
s_sql = """
Select
t.%FIELD%
FROM
%TABLE% t
WHERE
t.%WHERE%
;"""
s_sql = s_sql.replace("%TABLE%", s_table)
s_sql = s_sql.replace("%FIELD%", s_field)
s_sql = s_sql.replace("%WHERE%", s_where)
if l_debug:
print(s_sql)
t_return = o_cursor.execute(s_sql).fetchone()
print(t_return)
if t_return is not None:
s_return = str(t_return[0])
else:
s_return = "UNKNOWN "
funcfile.writelog("%t OBTAIN TABLE FIELD VALUE: " + s_table + " " +
s_field + " " + s_where)
return s_return
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 get_test_flag(o_cursor, s_source="HR", s_key="", s_code=""):
"""
Function to obtain a test flag.
:param o_cursor: object: Database cursor
:param s_source: str: Table to read lookups
:param s_key: str: Test search key
:param s_code: str: Test lookup code
:return: str: Value of lookup description
"""
# SET PREVIOUS FINDINGS
if l_debug:
print("Obtain a test flag flag...")
s_sql = """
Select
l.LOOKUP_DESCRIPTION
FROM
%TABLE% l
WHERE
l.LOOKUP = '%KEY%' And
l.LOOKUP_CODE = '%CODE%'
;"""
s_sql = s_sql.replace("%KEY%", s_key)
s_sql = s_sql.replace("%CODE%", s_code)
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")
t_return = o_cursor.execute(s_sql).fetchone()
s_return = str(t_return[0])
funcfile.writelog("%t OBTAIN TEST FLAG: " + s_key + " " + s_code + " " +
s_return)
return s_return
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)
# IMPORT MODULES
import C302_test_student_fee
from _my_modules import funcfile
from _my_modules import funcsys
# OPEN THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C302_TEST_STUDENT_FEE_RUN_PREV")
funcfile.writelog("--------------------------------------")
try:
C302_test_student_fee.student_fee("prev")
except Exception as e:
funcsys.ErrMessage(e)
# CLOSE THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("COMPLETED: C302_TEST_STUDENT_FEE_RUN_PREV")
funcfile.writelog("-----------------------------------------")
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]
re_path: str = "R:/People/" + funcdate.cur_year() + "/" # Results path
ed_path: str = "S:/_external_data/" # external data path
so_file: str = "People_conflict.sqlite" # Source database
s_sql: str = "" # SQL statements
l_debug: bool = True
l_export: bool = False
l_mess: bool = False
l_mail: bool = False
l_record: bool = False
# OPEN THE SCRIPT LOG FILE
if l_debug:
print("-----------------------------")
print("C002_PEOPLE_TEST_CONFLICT_DEV")
print("-----------------------------")
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C002_PEOPLE_TEST_CONFLICT_DEV")
funcfile.writelog("-------------------------------------")
if l_mess:
funcsms.send_telegram('', 'administrator',
'Testing employee <b>conflict of interest</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:
def mysql_lists(s_database):
"""
Function to populate the mysql databases
:param s_database:
:return:
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# Declare variables
s_schema: str = ""
if s_database == "Web_ia_nwu":
s_schema = "Ia_nwu"
elif s_database == "Web_ia_joomla":
s_schema = "Ia_joomla"
elif s_database == "Mysql_ia_server":
s_schema = "nwuiaca"
# l_export = True
# l_mail = True
# l_vacuum = False
# Script log file
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B005_MYSQL_LISTS")
funcfile.writelog("-------------------------")
print("-----------------")
print("B005_MYSQL_LISTS")
print("-----------------")
# MESSAGE
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator", "<b>B005 Mysql server (" + s_schema + ")</b>")
"""*****************************************************************************
OPEN THE MYSQL DATABASES
*****************************************************************************"""
print("OPEN THE MYSQL DATABASES")
funcfile.writelog("OPEN THE MYSQL DATABASES")
# Open the MYSQL DESTINATION table
ms_cnxn = funcmysql.mysql_open(s_database)
ms_curs = ms_cnxn.cursor()
funcfile.writelog("%t OPEN MYSQL DATABASE: " + s_database)
""" ****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
"""*****************************************************************************
OPEN THE PEOPLE DATABASES
*****************************************************************************"""
print("OPEN THE PEOPLE DATABASES")
funcfile.writelog("OPEN THE PEOPLE DATABASES")
# Open the SQLITE SOURCE file
so_path = "W:/People/" # Source database path
so_file = "People.sqlite" # Source database
with sqlite3.connect(so_path+so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
"""*****************************************************************************
EXPORT CURRENT PEOPLE
*****************************************************************************"""
print("EXPORT CURRENT PEOPLE")
funcfile.writelog("EXPORT CURRENT PEOPLE")
# EXPORT CURRENT PEOPLE TO MYSQL
print("Build mysql current people...")
ms_curs.execute("DROP TABLE IF EXISTS ia_people")
funcfile.writelog("%t DROP MYSQL TABLE: PEOPLE (ia_people)")
# ia_find_1_auto INT(11) NOT NULL AUTO_INCREMENT,
s_sql = """
CREATE TABLE IF NOT EXISTS ia_people (
ia_find_auto INT(11) NOT NULL,
people_employee_number VARCHAR(20),
people_ass_id INT(11),
people_person_id INT(11),
people_ass_numb VARCHAR(30),
people_party_id INT(11),
people_full_name VARCHAR(150),
people_name_list VARCHAR(150),
people_name_addr VARCHAR(150),
people_known_name VARCHAR(150),
people_position_full VARCHAR(150),
people_date_of_birth DATE,
people_nationality VARCHAR(30),
people_nationality_name VARCHAR(150),
people_idno VARCHAR(150),
people_passport VARCHAR(150),
people_permit VARCHAR(150),
people_permit_expire DATE,
people_tax_number VARCHAR(150),
people_sex VARCHAR(30),
people_marital_status VARCHAR(30),
people_disabled VARCHAR(30),
people_race_code VARCHAR(30),
people_race_desc VARCHAR(150),
people_lang_code VARCHAR(30),
people_lang_desc VARCHAR(150),
people_int_mail VARCHAR(30),
people_email_address VARCHAR(150),
people_curr_empl_flag VARCHAR(1),
people_user_person_type VARCHAR(30),
people_ass_start DATE,
people_ass_end DATE,
people_emp_start DATE,
people_emp_end DATE,
people_leaving_reason VARCHAR(30),
people_leave_reason_descrip VARCHAR(150),
people_location_description VARCHAR(150),
people_org_type_desc VARCHAR(150),
people_oe_code VARCHAR(30),
people_org_name VARCHAR(150),
people_primary_flag VARCHAR(1),
people_acad_supp VARCHAR(30),
people_faculty VARCHAR(150),
people_division VARCHAR(150),
people_employment_category VARCHAR(150),
people_ass_week_len INT(2),
people_leave_code VARCHAR(30),
people_grade VARCHAR(30),
people_grade_name VARCHAR(150),
people_grade_calc VARCHAR(30),
people_position VARCHAR(30),
people_position_name VARCHAR(150),
people_job_name VARCHAR(150),
people_job_segment_name VARCHAR(150),
people_supervisor INT(11),
people_title_full VARCHAR(30),
people_first_name VARCHAR(150),
people_middle_names VARCHAR(150),
people_last_name VARCHAR(150),
people_phone_work VARCHAR(30),
people_phone_mobi VARCHAR(30),
people_phone_home VARCHAR(30),
people_address_sars VARCHAR(250),
people_address_post VARCHAR(250),
people_address_home VARCHAR(250),
people_address_othe VARCHAR(250),
people_count_pos INT(11),
people_count_ass INT(11),
people_count_peo INT(11),
people_date_ass_lookup DATE,
people_ass_active VARCHAR(1),
people_date_emp_lookup DATE,
people_emp_active VARCHAR(1),
people_mailto VARCHAR(150),
people_proposed_salary_n DECIMAL(12,2),
people_person_type VARCHAR(150),
people_acc_type VARCHAR(30),
people_acc_branch VARCHAR(30),
people_acc_number VARCHAR(30),
people_acc_sars VARCHAR(1),
people_acc_relation VARCHAR(30),
people_sec_fullpart_flag VARCHAR(1),
people_initials VARCHAR(30),
people_age INT(3),
people_month INT(2),
people_day INT(2),
PRIMARY KEY (people_employee_number),
INDEX fb_order_people_full_name_INDEX (people_full_name),
INDEX fb_order_people_known_name_INDEX (people_known_name)
)
ENGINE = InnoDB
CHARSET=utf8mb4
COLLATE utf8mb4_unicode_ci
COMMENT = 'Table to store detailed people data'
""" + ";"
ms_curs.execute(s_sql)
funcfile.writelog("%t CREATE MYSQL TABLE: PEOPLE (ia_people)")
# Obtain the new mysql table column types
print("Build mysql current people columns types...")
funcfile.writelog("GET COLUMN TYPE: People")
a_cols = funcmysql.get_coltypes_mysql_list(ms_curs, s_schema, "ia_people")
# print(a_cols)
# Open the SOURCE file to obtain column headings
print("Build mysql current people columns...")
funcfile.writelog("GET COLUMN NAME: People")
s_head = funcmysql.get_colnames_sqlite_text(so_curs, "X002_PEOPLE_CURR", "people_")
s_head = "(`ia_find_auto`, " + s_head.rstrip(", ") + ")"
# print(s_head)
# Open the SOURCE file to obtain the data
print("Insert mysql current people...")
with sqlite3.connect(so_path+so_file) as rs_conn:
rs_conn.row_factory = sqlite3.Row
rs_curs = rs_conn.cursor()
rs_curs.execute("SELECT * FROM X002_PEOPLE_CURR")
rows = rs_curs.fetchall()
i_tota = 0
i_coun = 0
for row in rows:
s_data = funcmysql.convert_sqlite_mysql(row, a_cols, 1, 1)
# print(s_data)
s_sql = "INSERT IGNORE INTO `ia_people` " + s_head + " VALUES " + s_data + ";"
ms_curs.execute(s_sql)
i_tota = i_tota + 1
i_coun = i_coun + 1
if i_coun == 100:
ms_cnxn.commit()
i_coun = 0
# Close the ROW Connection
ms_cnxn.commit()
rs_conn.close()
print("Inserted " + str(i_tota) + " mysql current people...")
funcfile.writelog("%t POPULATE MYSQL: " + str(i_tota) + " PEOPLE CURRENT rows (ia_people)")
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator", "<b> " + str(i_tota) + "</b> People upload")
# Update MYSQL PEOPLE TO WEB FINDING mail trigger ******************************
# if s_database == "Web_ia_nwu":
# print("Update mysql current people mail trigger...")
# s_sql = """
# UPDATE `ia_finding` SET
# `ia_find_updated` = '1',
# `ia_find_r1_send` = '0',
# `ia_find_updatedate` = now()
# WHERE `ia_finding`.`ia_find_auto` = 1
# """ + ";"
# ms_curs.execute(s_sql)
# ms_cnxn.commit()
# funcfile.writelog("%t UPDATE MYSQL TRIGGER: FINDING 1 (people current)")
# Update MYSQL PEOPLE TO WEB FINDING mail trigger ******************************
# if s_database == "Web_ia_nwu":
# print("Update mysql current people summary mail trigger...")
# s_sql = """
# UPDATE `ia_finding` SET
# `ia_find_updated` = '1',
# `ia_find_r1_send` = '0',
# `ia_find_updatedate` = now()
# WHERE `ia_finding`.`ia_find_auto` = 2
# """ + ";"
# ms_curs.execute(s_sql)
# ms_cnxn.commit()
# funcfile.writelog("%t UPDATE MYSQL TRIGGER: FINDING 2 (people current summary)")
# Update MYSQL PEOPLE TO WEB FINDING mail trigger ******************************
# if s_database == "Web_ia_nwu":
# print("Update mysql current people birthday mail trigger...")
# s_sql = """
# UPDATE `ia_finding` SET
# `ia_find_updated` = '1',
# `ia_find_r1_send` = '0',
# `ia_find_updatedate` = now()
# WHERE `ia_finding`.`ia_find_auto` = 3
# """ + ";"
# ms_curs.execute(s_sql)
# ms_cnxn.commit()
# funcfile.writelog("%t UPDATE MYSQL TRIGGER: FINDING 3 (people current birthdays)")
"""*****************************************************************************
EXPORT CURRENT PEOPLE STRUCTURE
*****************************************************************************"""
print("EXPORT CURRENT PEOPLE STRUCTURE")
funcfile.writelog("EXPORT CURRENT PEOPLE STRUCTURE")
# EXPORT CURRENT PEOPLE STRUCTURE
print("Build mysql current people structure...")
ms_curs.execute("DROP TABLE IF EXISTS ia_people_struct")
funcfile.writelog("%t DROP MYSQL TABLE: PEOPLE_STRUCT (ia_people_struct)")
# ia_find_1_auto INT(11) NOT NULL AUTO_INCREMENT,
s_sql = """
CREATE TABLE IF NOT EXISTS ia_people_struct (
ia_find_auto INT(11) NOT NULL,
struct_employee_one VARCHAR(20),
struct_name_list_one VARCHAR(150),
struct_known_name_one VARCHAR(150),
struct_position_full_one VARCHAR(150),
struct_location_description_one VARCHAR(150),
struct_division_one VARCHAR(150),
struct_faculty_one VARCHAR(150),
struct_email_address_one VARCHAR(150),
struct_phone_work_one VARCHAR(30),
struct_phone_mobi_one VARCHAR(30),
struct_phone_home_one VARCHAR(30),
struct_org_name_one VARCHAR(150),
struct_grade_calc_one VARCHAR(150),
struct_employee_two VARCHAR(20),
struct_name_list_two VARCHAR(150),
struct_known_name_two VARCHAR(150),
struct_position_full_two VARCHAR(150),
struct_location_description_two VARCHAR(150),
struct_division_two VARCHAR(150),
struct_faculty_two VARCHAR(150),
struct_email_address_two VARCHAR(150),
struct_phone_work_two VARCHAR(30),
struct_phone_mobi_two VARCHAR(30),
struct_phone_home_two VARCHAR(30),
struct_employee_three VARCHAR(20),
struct_name_list_three VARCHAR(150),
struct_known_name_three VARCHAR(150),
struct_position_full_three VARCHAR(150),
struct_location_description_three VARCHAR(150),
struct_division_three VARCHAR(150),
struct_faculty_three VARCHAR(150),
struct_email_address_three VARCHAR(150),
struct_phone_work_three VARCHAR(30),
struct_phone_mobi_three VARCHAR(30),
struct_phone_home_three VARCHAR(30),
PRIMARY KEY (struct_employee_one)
)
ENGINE = InnoDB
CHARSET=utf8mb4
COLLATE utf8mb4_unicode_ci
COMMENT = 'Table to store detailed people structure data'
""" + ";"
ms_curs.execute(s_sql)
funcfile.writelog("%t CREATE MYSQL TABLE: PEOPLE_STRUCT (ia_people_struct)")
# Obtain the new mysql table column types
print("Build mysql current people structure columns types...")
funcfile.writelog("GET COLUMN TYPE: People")
a_cols = funcmysql.get_coltypes_mysql_list(ms_curs, s_schema, "ia_people_struct")
# print(a_cols)
# Open the SOURCE file to obtain column headings
print("Build mysql current people structure columns...")
funcfile.writelog("GET COLUMN NAME: People org structure")
s_head = funcmysql.get_colnames_sqlite_text(so_curs, "X003_PEOPLE_ORGA_REF", "struct_")
s_head = "(`ia_find_auto`, " + s_head.rstrip(", ") + ")"
# print(s_head)
# Open the SOURCE file to obtain the data
print("Insert mysql current people structure...")
with sqlite3.connect(so_path+so_file) as rs_conn:
rs_conn.row_factory = sqlite3.Row
rs_curs = rs_conn.cursor()
rs_curs.execute("SELECT * FROM X003_PEOPLE_ORGA_REF")
rows = rs_curs.fetchall()
i_tota = 0
i_coun = 0
for row in rows:
s_data = funcmysql.convert_sqlite_mysql(row, a_cols, 4, 1)
s_sql = "INSERT IGNORE INTO `ia_people_struct` " + s_head + " VALUES " + s_data + ";"
ms_curs.execute(s_sql)
i_tota = i_tota + 1
i_coun = i_coun + 1
if i_coun == 100:
# print(i_coun)
ms_cnxn.commit()
i_coun = 0
# Close the ROW Connection
ms_cnxn.commit()
rs_conn.close()
print("Inserted " + str(i_tota) + " mysql current people...")
funcfile.writelog("%t POPULATE MYSQL: " + str(i_tota) + " PEOPLE STRUCTURE rows (ia_people_struct)")
# Update MYSQL PEOPLE TO WEB FINDING mail trigger ******************************
# if s_database == "Web_ia_nwu":
# print("Update mysql current people hierarchy mail trigger...")
# s_sql = """
# UPDATE `ia_finding` SET
# `ia_find_updated` = '1',
# `ia_find_r1_send` = '0',
# `ia_find_updatedate` = now()
# WHERE `ia_finding`.`ia_find_auto` = 4
# """ + ";"
# ms_curs.execute(s_sql)
# ms_cnxn.commit()
# funcfile.writelog("%t UPDATE MYSQL TRIGGER: FINDING 4 (people current hierarchy)")
# CLOSE PEOPLE DATABASE
so_conn.commit()
so_conn.close()
"""*****************************************************************************
OPEN THE KFS VSS STUDDEB DATABASES
*****************************************************************************"""
print("OPEN THE PEOPLE DATABASES")
funcfile.writelog("OPEN THE PEOPLE DATABASES")
# Open the SQLITE SOURCE file
so_path = "W:/Kfs_vss_studdeb/" # Source database path
so_file = "Kfs_vss_studdeb.sqlite" # Source database
with sqlite3.connect(so_path+so_file) as so_conn:
so_curs = so_conn.cursor()
funcfile.writelog("OPEN DATABASE: " + so_file)
"""*****************************************************************************
EXPORT STUD DEBTOR MONTHLY BALANCES
*****************************************************************************"""
print("EXPORT STUD DEBTOR MONTHLY BALANCES")
funcfile.writelog("EXPORT STUD DEBTOR MONTHLY BALANCES")
# Create MYSQL VSS GL MONTHLY BALANCES TO WEB table ****************************
print("Build mysql vss gl monthly balances...")
ms_curs.execute("DROP TABLE IF EXISTS ia_finding_5")
funcfile.writelog("%t DROP MYSQL TABLE: ia_finding_5")
s_sql = """
CREATE TABLE IF NOT EXISTS ia_finding_5 (
ia_find_auto INT(11),
ia_find5_auto INT(11) AUTO_INCREMENT,
ia_find5_campus VARCHAR(20),
ia_find5_month VARCHAR(2),
ia_find5_current VARCHAR(1),
ia_find5_vss_tran_dt DECIMAL(20,2),
ia_find5_vss_tran_ct DECIMAL(20,2),
ia_find5_vss_tran DECIMAL(20,2),
ia_find5_vss_runbal DECIMAL(20,2),
ia_find5_gl_tran DECIMAL(20,2),
ia_find5_gl_runbal DECIMAL(20,2),
ia_find5_diff DECIMAL(20,2),
ia_find5_move DECIMAL(20,2),
ia_find5_officer_camp VARCHAR(10),
ia_find5_officer_name_camp VARCHAR(50),
ia_find5_officer_mail_camp VARCHAR(100),
ia_find5_officer_org VARCHAR(10),
ia_find5_officer_name_org VARCHAR(50),
ia_find5_officer_mail_org VARCHAR(100),
ia_find5_supervisor_camp VARCHAR(10),
ia_find5_supervisor_name_camp VARCHAR(50),
ia_find5_supervisor_mail_camp VARCHAR(100),
ia_find5_supervisor_org VARCHAR(10),
ia_find5_supervisor_name_org VARCHAR(50),
ia_find5_supervisor_mail_org VARCHAR(100),
PRIMARY KEY (ia_find5_auto),
INDEX fb_order_ia_find5_campus_INDEX (ia_find5_campus),
INDEX fb_order_ia_find5_month_INDEX (ia_find5_month)
)
ENGINE = InnoDB
CHARSET=utf8mb4
COLLATE utf8mb4_unicode_ci
COMMENT = 'Table to store vss and gl monthly balances'
""" + ";"
ms_curs.execute(s_sql)
funcfile.writelog("%t CREATE MYSQL TABLE: ia_finding_5 (vss gl monthly balances per campus per month)")
# Obtain the new mysql table column types
print("Build mysql current people structure columns types...")
funcfile.writelog("GET COLUMN TYPE: People")
a_cols = funcmysql.get_coltypes_mysql_list(ms_curs, s_schema, "ia_finding_5")
# print(a_cols)
# Open the SOURCE file to obtain column headings
print("Build mysql vss gl monthly balance columns...")
funcfile.writelog("GET COLUMN NAME: ia_finding_5")
s_head = funcmysql.get_colnames_sqlite_text(so_curs, "X002ex_vss_gl_balance_month", "ia_find5_")
s_head = "(`ia_find_auto`, " + s_head.rstrip(", ") + ")"
# print(s_head)
# Open the SOURCE file to obtain the data
print("Insert mysql vss gl monthly balance data...")
with sqlite3.connect(so_path+so_file) as rs_conn:
rs_conn.row_factory = sqlite3.Row
rs_curs = rs_conn.cursor()
rs_curs.execute("SELECT * FROM X002ex_vss_gl_balance_month")
rows = rs_curs.fetchall()
i_tota = 0
i_coun = 0
for row in rows:
s_data = funcmysql.convert_sqlite_mysql(row, a_cols, 5, 2)
s_sql = "INSERT IGNORE INTO `ia_finding_5` " + s_head + " VALUES " + s_data + ";"
ms_curs.execute(s_sql)
i_tota = i_tota + 1
i_coun = i_coun + 1
if i_coun == 100:
ms_cnxn.commit()
i_coun = 0
ms_cnxn.commit()
print("Inserted " + str(i_tota) + " rows...")
funcfile.writelog("%t POPULATE MYSQL: " + str(i_tota) + " STUD DEBT MONTHLY BAL rows (ia_finding_5)")
if funcconf.l_mess_project:
funcsms.send_telegram("", "administrator", "<b> " + str(i_tota) + "</b> Student debtor balance upload")
"""*****************************************************************************
EXPORT STUD DEBTOR COMPARISON CAMPUS MONTH SUMMARY
*****************************************************************************"""
print("EXPORT STUD DEBTOR COMPARISON CAMPUS MONTH SUMMARY")
funcfile.writelog("EXPORT STUD DEBTOR COMPARISON CAMPUS MONTH SUMMARY")
# EXPORT MYSQL VSS GL COMPARISON PER CAMPUS PER MONTH TO WEB
print("Build mysql vss gl comparison campus month...")
ms_curs.execute("DROP TABLE IF EXISTS ia_finding_6")
funcfile.writelog("%t DROP MYSQL TABLE: ia_finding_6")
s_sql = """
CREATE TABLE IF NOT EXISTS ia_finding_6 (
ia_find_auto INT(11),
ia_find6_auto INT(11) AUTO_INCREMENT,
ia_find6_campus VARCHAR(20),
ia_find6_month VARCHAR(2),
ia_find6_trancode VARCHAR(5),
ia_find6_vss_description VARCHAR(150),
ia_find6_vss_amount DECIMAL(20,2),
ia_find6_gl_description VARCHAR(150),
ia_find6_gl_amount DECIMAL(20,2),
ia_find6_diff DECIMAL(20,2),
ia_find6_matched VARCHAR(2),
ia_find6_period VARCHAR(7),
ia_find6_current VARCHAR(1),
PRIMARY KEY (ia_find6_auto),
INDEX fb_order_ia_find6_campus_INDEX (ia_find6_campus),
INDEX fb_order_ia_find6_month_INDEX (ia_find6_month)
)
ENGINE = InnoDB
CHARSET=utf8mb4
COLLATE utf8mb4_unicode_ci
COMMENT = 'Table to store vss and gl monthly comparisons'
""" + ";"
ms_curs.execute(s_sql)
funcfile.writelog("%t CREATE MYSQL TABLE: ia_finding_6 (vss gl comparison per campus per month)")
# Obtain the new mysql table column types
print("Build mysql current people structure columns types...")
funcfile.writelog("GET COLUMN TYPE: People")
a_cols = funcmysql.get_coltypes_mysql_list(ms_curs, s_schema, "ia_finding_6")
# print(a_cols)
# Open the SOURCE file to obtain column headings
print("Build mysql vss gl comparison columns...")
funcfile.writelog("GET COLUMN NAME: ia_finding_6")
s_head = funcmysql.get_colnames_sqlite_text(so_curs, "X003ax_vss_gl_join", "ia_find6_")
s_head = "(ia_find_auto, " + s_head.rstrip(", ") + ")"
# print(s_head)
# Open the SOURCE file to obtain the data
print("Insert mysql vss gl comparison data...")
with sqlite3.connect(so_path+so_file) as rs_conn:
rs_conn.row_factory = sqlite3.Row
rs_curs = rs_conn.cursor()
rs_curs.execute("SELECT * FROM X003ax_vss_gl_join")
rows = rs_curs.fetchall()
i_tota = 0
i_coun = 0
for row in rows:
s_data = funcmysql.convert_sqlite_mysql(row, a_cols, 6, 2)
s_sql = "INSERT IGNORE INTO `ia_finding_6` " + s_head + " VALUES " + s_data + ";"
ms_curs.execute(s_sql)
i_tota = i_tota + 1
i_coun = i_coun + 1
if i_coun == 100:
ms_cnxn.commit()
i_coun = 0
ms_cnxn.commit()
print("Inserted " + str(i_tota) + " rows...")
funcfile.writelog("%t POPULATE MYSQL: " + str(i_tota) + " STUD DEBT COMPARISON rows (ia_finding_6)")
# CLOSE PEOPLE DATABASE
so_conn.commit()
so_conn.close()
"""*****************************************************************************
END OF SCRIPT
*****************************************************************************"""
print("END OF SCRIPT")
funcfile.writelog("END OF SCRIPT")
# CLOSE MYSQL DATABASES
ms_cnxn.commit()
ms_cnxn.close()
# CLOSE THE LOG WRITER
funcfile.writelog("---------------------------")
funcfile.writelog("COMPLETED: B005_MYSQL_LISTS")
return
def report_studdeb_recon(dopenmaf: float = 0,
dopenpot: float = 0,
dopenvaa: float = 0,
s_period: str = "curr"):
"""
STUDENT DEBTOR RECONCILIATIONS
:param dopenmaf: int: Mafiking Campus opening balance
:param dopenpot: int: Potchefstroom opening balance
:param dopenvaa: int: Vaal Campus opening balance
:param s_period: str: Period indication curr, prev or year
:return: Null
"""
""" PARAMETERS *************************************************************
dopenmaf = GL Opening balances for Mafikeng campus
dopenpot = GL Opening balances for Potchefstroom campus
dopenvaa = GL Opening balances for Vaal Triangle campus
Notes:
1. When new financial year start, GL does not contain opening balances.
Opening balances are the inserted manually here, until the are inserted
into the GL by journal, usually at the end of March. This was the case
for the 2019 financial year
*************************************************************************"""
"""*************************************************************************
ENVIRONMENT
*************************************************************************"""
# DECLARE VARIABLES
l_debug: bool = True
so_path: str = "W:/Kfs_vss_studdeb/" # Source database path
if s_period == "curr":
s_year: str = funcdate.cur_year()
so_file: str = "Kfs_vss_studdeb.sqlite" # Source database
s_kfs: str = "KFSCURR"
s_vss: str = "VSSCURR"
elif s_period == "prev":
s_year = funcdate.prev_year()
so_file = "Kfs_vss_studdeb_prev.sqlite" # Source database
s_kfs = "KFSPREV"
s_vss = "VSSPREV"
else:
s_year = s_period
so_file = "Kfs_vss_studdeb_" + s_year + ".sqlite" # Source database
s_kfs = ""
s_vss = ""
re_path = "R:/Debtorstud/" # Results
ed_path = "S:/_external_data/" # External data
# l_mess: bool = funcconf.l_mess_project
l_mess: bool = False
l_export = True
l_record = True
s_burs_code = '042z052z381z500' # Current bursary transaction codes
# Open the script log file ******************************************************
print("-------------------------")
print("C200_REPORT_STUDDEB_RECON")
print("-------------------------")
print("ENVIRONMENT")
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C200_REPORT_STUDDEB_RECON")
funcfile.writelog("---------------------------------")
funcfile.writelog("ENVIRONMENT")
# MESSAGE
if l_mess:
funcsms.send_telegram('', 'administrator',
'<b>C200 Student debtor reconciliations</b>')
"""*************************************************************************
OPEN DATABASES
*************************************************************************"""
print("OPEN DATABASES")
funcfile.writelog("OPEN 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 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: KFS_CURR.SQLITE")
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:/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")
"""*************************************************************************"""
# TODO DELETE SCRIPT AFTER FIRST RUN
so_curs.execute("DROP TABLE IF EXISTS X002dd_vss_closing_open_differ")
so_curs.execute("DROP TABLE IF EXISTS X002de_vss_differ_type")
so_curs.execute("DROP TABLE IF EXISTS X002df_vss_differ_join")
so_curs.execute("DROP TABLE IF EXISTS X002dg_vss_differ_close_open_differ")
so_curs.execute(
"DROP TABLE IF EXISTS X002dh_vss_differ_campus_differ_zerobal")
so_curs.execute("DROP TABLE IF EXISTS X002di_vss_differ_campus_differ_bal")
# JOIN PREVIOUS BALANCE AND CURRENT OPENING BALANCE
print("Join previous balance and current opening balance...")
sr_file = "X002dc_vss_prevbal_curopen"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
CLOS.STUDENT,
CLOS.CAMPUS As CAMPUS_CLOS,
CLOS.BALANCE As BAL_CLOS,
OPEN.CAMPUS As CAMPUS_OPEN,
OPEN.BALANCE As BAL_OPEN,
0.00 AS DIFF_BAL,
0 AS TYPE
From
X002da_vss_student_balance_clos CLOS lEFT Join
X002da_vss_student_balance_open OPEN ON OPEN.CAMPUS = OPEN.CAMPUS AND
OPEN.STUDENT = CLOS.STUDENT
Where
CLOS.BALANCE <> 0
;"""
"""
And OPEN.CAMPUS = CLOS.CAMPUS
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# CHANGE BALANCE FROM NULL TO 0.00
so_curs.execute("UPDATE X002dc_vss_prevbal_curopen " + """
SET BAL_OPEN =
CASE
WHEN BAL_OPEN IS NULL THEN 0.00
ELSE BAL_OPEN
END
;""")
so_conn.commit()
# CALCULATE BALANCE DIFFERENCE
print("Calculate balance difference...")
so_curs.execute("UPDATE X002dc_vss_prevbal_curopen " + """
SET DIFF_BAL = Cast(round(BAL_OPEN - BAL_CLOS,2) As REAL)
;""")
so_conn.commit()
# DETERMINE THE DIFFERENCE TYPE
# 1 = No difference
# 2 = Campus the same, but balance differ
# 3 = Campus differ, but balance the same
# 4 = Campus differ, and balance differ
so_curs.execute("UPDATE X002dc_vss_prevbal_curopen " + """
SET TYPE =
CASE
WHEN CAMPUS_OPEN = CAMPUS_CLOS AND DIFF_BAL = 0.00 THEN 1
WHEN BAL_CLOS = 0.00 AND CAMPUS_OPEN IS NULL THEN 1
WHEN CAMPUS_OPEN = CAMPUS_CLOS AND DIFF_BAL <> 0.00 THEN 4
WHEN CAMPUS_OPEN IS NULL AND DIFF_BAL <> 0.00 THEN 2
WHEN CAMPUS_OPEN <> CAMPUS_CLOS AND DIFF_BAL = 0.00 THEN 3
WHEN CAMPUS_OPEN <> CAMPUS_CLOS AND DIFF_BAL <> 0.00 THEN 4
ELSE TYPE
END
;""")
so_conn.commit()
# SUMMARIZE SAME CAMPUS BALANCE DIFFER
print("Summarize same campus where balance differ (Type=2)...")
sr_file = "X002dd_vss_campus_same_bal_differ"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
Count(x002vpc.STUDENT) As COUNT,
x002vpc.CAMPUS_CLOS,
Total(x002vpc.BAL_CLOS) As BAL_CLOS,
Total(x002vpc.BAL_OPEN) As BAL_OPEN,
Total(x002vpc.DIFF_BAL) As DIFF
From
X002dc_vss_prevbal_curopen x002vpc
Where
x002vpc.TYPE = 2
Group By
x002vpc.CAMPUS_CLOS
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# SUMMARIZE SAME DIFFERENT CAMPUS BALANCE SAME
print("Summarize campus different balance same (Type=3)...")
sr_file = "X002de_vss_campus_differ_bal_same"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
Count(x002vpc.STUDENT) As COUNT,
x002vpc.CAMPUS_CLOS,
Total(x002vpc.BAL_CLOS) As BAL_CLOS,
x002vpc.CAMPUS_OPEN,
Total(x002vpc.BAL_OPEN) As BAL_OPEN,
Total(x002vpc.DIFF_BAL) As DIFF
From
X002dc_vss_prevbal_curopen x002vpc
Where
x002vpc.TYPE = 3
Group By
x002vpc.CAMPUS_CLOS,
x002vpc.CAMPUS_OPEN
;"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + sr_file)
# SUMMARIZE DIFFERENT CAMPUS DIFFERENT BALANCE
print("Summarize campus different balance different (Type=4)...")
sr_file = "X002df_vss_campus_differ_bal_differ"
s_sql = "CREATE TABLE " + sr_file + " AS " + """
Select
Count(x002vpc.STUDENT) As COUNT,
x002vpc.CAMPUS_CLOS,
Total(x002vpc.BAL_CLOS) As BAL_CLOS,
x002vpc.CAMPUS_OPEN,
Total(x002vpc.BAL_OPEN) As BAL_OPEN,
Total(x002vpc.DIFF_BAL) As DIFF
From
X002dc_vss_prevbal_curopen x002vpc
Where
x002vpc.TYPE = 4
Group By
x002vpc.CAMPUS_CLOS,
x002vpc.CAMPUS_OPEN
;"""
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")
# COMMIT DATA
so_conn.commit()
# CLOSE THE DATABASE CONNECTION
so_conn.close()
# CLOSE THE LOG WRITER
funcfile.writelog("------------------------------------")
funcfile.writelog("COMPLETED: C200_REPORT_STUDDEB_RECON")
return
Copyright: Albert J v Rensburg
"""
# IMPORT PYTHON MODULES
import csv
import datetime
import sqlite3
# IMPORT OWN MODULES
from _my_modules import funcdate
from _my_modules import funccsv
from _my_modules import funcfile
from _my_modules import funcpayroll
# OPEN THE LOG FILE
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B004_PAYROLL_LISTS_DEV")
funcfile.writelog("------------------------------")
print("----------------------")
print("B004_PAYROLL_LISTS_DEV")
print("----------------------")
# Declare variables
so_path = "W:/People_payroll/" # Source database path
so_file = "People_payroll.sqlite" # Source database
re_path = "R:/People/People/" # Results
ed_path = "S:/_external_data/"
# OPEN THE SOURCE FILE
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
from _my_modules import funcpayroll
from _my_modules import funcpeople
from _my_modules import funcsms
from _my_modules import funcsys
""" INDEX
ENVIRONMENT
OPEN THE DATABASES
BEGIN OF SCRIPT
End OF SCRIPT
"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# SCRIPT LOG
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B001_PEOPLE_LISTS_DEV")
funcfile.writelog("-----------------------------")
print("---------------------")
print("B001_PEOPLE_LISTS_DEV")
print("---------------------")
# DECLARE VARIABLES
so_path = "W:/People/" # Source database path
so_file = "People.sqlite" # Source database
sr_file: str = "" # Current sqlite table
re_path = "R:/People/" # Results path
l_debug: bool = True
l_export: bool = True
l_mail: bool = True
l_vacuum: bool = False
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
from _my_modules import funcsys
""" INDEX **********************************************************************
ENVIRONMENT
OPEN THE DATABASES
BEGIN OF SCRIPT
END OF SCRIPT
*****************************************************************************"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# OPEN THE LOG
print("-------------------------------")
print("C001_PEOPLE_TEST_MASTERFILE_DEV")
print("-------------------------------")
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C001_PEOPLE_TEST_MASTERFILE_DEV")
funcfile.writelog("---------------------------------------")
# DECLARE VARIABLES
ed_path = "S:/_external_data/" # External data path
so_path = "W:/People/" # Source database path
so_file = "People_test_masterfile.sqlite" # Source database
re_path = "R:/People/" # Results path
l_export: bool = False
l_mail: bool = False
l_record: bool = False
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
print("OPEN THE DATABASES")
# DECLARE VARIABLES
l_debug: bool = True
so_path: str = "W:/Kfs/" # Source database path
so_file: str = "Kfs_test_creditor.sqlite" # Source database
re_path: str = "R:/Kfs/" # Results path
ed_path: str = "S:/_external_data/" # External data path
l_export: bool = True
# l_mail: bool = funcconf.l_mail_project
l_mail: bool = False
# l_mess: bool = funcconf.l_mess_project
l_mess: bool = True
l_record: bool = False
# OPEN THE SCRIPT LOG FILE
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C201_CREDITOR_TEST_DEV")
funcfile.writelog("------------------------------")
if l_debug:
print("----------------------")
print("C201_CREDITOR_TEST_DEV")
print("----------------------")
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
funcfile.writelog("OPEN THE DATABASES")
if l_debug:
print("OPEN THE DATABASES")
# OPEN THE WORKING DATABASE
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 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 robot_report_audit_sir(s_year: str = "", s_type: str = "", s_name: str = "", s_mail: str = ""):
"""
SEARCH VSS.PARTY FOR NAMES, NUMBERS AND ID'S
:param s_year: Working year
:param s_type: Report type
:param s_name: The name of the requester / recipient
:param s_mail: The requester mail address
:return: str: Info in message format
"""
from datetime import datetime
# DECLARE VARIABLES
l_debug: bool = False
"""*************************************************************************
ENVIRONMENT
*************************************************************************"""
if l_debug:
print("ENVIRONMENT")
# DECLARE VARIABLES
re_path: str = "R:/Audit/" # Results
l_mess: bool = funcconf.l_mess_project
# l_mess: bool = False
s_file: str = "Report_assignment_sir.csv"
s_message: str = "Special investigation work in progress (sir) report for year " + s_year + " "
# LOG
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: " + s_function.upper())
funcfile.writelog("-" * len("script: "+s_function))
if l_debug:
print(s_function.upper())
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator", "<b>" + s_function.upper() + "</b>")
"""****************************************************************************
OPEN THE DATABASES
****************************************************************************"""
if l_debug:
print("OPEN THE MYSQL DATABASE")
funcfile.writelog("OPEN THE MYSQL DATABASE")
# VARIABLES
s_source_database: str = "Web_ia_nwu"
# OPEN THE SOURCE FILE
ms_from_connection = funcmysql.mysql_open(s_source_database)
ms_from_cursor = ms_from_connection.cursor()
funcfile.writelog("%t OPEN MYSQL DATABASE: " + s_source_database)
"""*****************************************************************************
BUILD THE REPORT
*****************************************************************************"""
funcfile.writelog("BUILD THE REPORT")
if l_debug:
print("BUILD THE REPORT")
# BUILD THE HEADINGS
s_line: str = "AUDITOR,"
s_line += "YEAR,"
s_line += "ASSIGNMENT,"
s_line += "CASE_YEAR,"
s_line += "CASE_NUMBER,"
s_line += "BOX_YEAR,"
s_line += "BOX_NUMBER,"
s_line += "REPORTED_DATE,"
s_line += "DATE_21,"
s_line += "DATE_90,"
s_line += "START_DATE,"
s_line += "DUE_DATE,"
s_line += "COMPLETE_DATE,"
s_line += "TYPE,"
s_line += "PRIORITY,"
s_line += "ACCUSED,"
s_line += "ISSUE,"
s_line += "STATUS,"
s_line += "DEPARTMENT,"
s_line += "REFERENCE,"
s_line += "VALUE,"
s_line += "NOTES_OFFICIAL,"
s_line += "NOTES_OWN"
funcfile.writelog(s_line, re_path, s_file)
# BUILD THE WHERE CLAUSE
if s_year != "":
s_where = "ia_assi_year = " + s_year + " and ia.ia_assicate_auto = 9"
s_where += " or "
s_where += "ia_assi_year < " + s_year + " and ia.ia_assicate_auto = 9 and ia_assi_priority < 8"
else:
s_where = "ia_assi_year = " + funcdate.cur_year() + " and ia.ia_assicate_auto = 9"
s_where += " or "
s_where += "ia_assi_year < " + funcdate.cur_year() + " and ia.ia_assicate_auto = 9 and ia_assi_priority < 8"
# BUILD THE SQL QUERY
s_sql = """
SELECT
us.ia_user_name,
ia.ia_assi_year,
ia.ia_assi_name,
ia.ia_assi_si_caseyear,
ia.ia_assi_si_casenumber,
ia.ia_assi_si_boxyear,
ia.ia_assi_si_boxnumber,
ia.ia_assi_si_reportdate,
ia.ia_assi_si_report1date,
ia.ia_assi_si_report2date,
ia.ia_assi_startdate,
ia.ia_assi_completedate,
ia.ia_assi_finishdate,
at.ia_assitype_name,
ia.ia_assi_priority,
ia.ia_assi_si_accused,
ia.ia_assi_si_issue,
st.ia_assistat_name,
co.ia_assicond_name,
ia.ia_assi_si_reference,
ia.ia_assi_si_value,
ia.ia_assi_offi,
ia.ia_assi_desc,
ia.ia_assi_auto,
ia.ia_assicate_auto
FROM
ia_assignment ia Left Join
ia_user us On us.ia_user_sysid = ia.ia_user_sysid Left Join
ia_assignment_type at On at.ia_assitype_auto = ia.ia_assitype_auto Left Join
ia_assignment_status st On st.ia_assistat_auto = ia.ia_assistat_auto Left Join
ia_assignment_conducted co On co.ia_assicond_auto = ia.ia_assicond_auto
WHERE %WHERE%
ORDER BY
ia_user_name,
ia_assi_year,
ia_assi_completedate,
ia_assitype_name,
ia_assi_priority desc,
ia_assi_name
;
"""
s_sql = s_sql.replace("%WHERE%", s_where)
if l_debug:
print(s_sql)
# BUILD THE ASSIGNMENT RECORD
for row in ms_from_cursor.execute(s_sql).fetchall():
# USER NAME
if l_debug:
print(row[0])
s_line = '"' + row[0] + '",'
# YEAR
if l_debug:
print(row[1])
s_line += str(row[1]) + ","
# NAME
if l_debug:
print(row[2])
print(row[23])
s_line += '"' + row[2].replace(",", "") + ' (' + str(row[23]) + ')",'
# CASE YEAR
if l_debug:
print(row[3])
s_line += str(row[3]) + ","
# CASE NUMBER
if l_debug:
print(row[4])
s_line += str(row[4]) + ","
# BOX YEAR
if l_debug:
print(row[5])
s_line += str(row[5]) + ","
# BOX NUMBER
if l_debug:
print(row[6])
s_line += str(row[6]) + ","
# REPORTED DATE
if l_debug:
print(row[7])
if row[7]:
s_line += datetime.strftime(row[7], "%Y-%m-%d") + ","
else:
s_line += ","
# 21 DATE
if l_debug:
print(row[8])
s_line += '"' + row[8] + '",'
# 90 DATE
if l_debug:
print(row[9])
s_line += '"' + row[9] + '",'
# START DATE
if l_debug:
print(row[10])
if row[10]:
s_line += datetime.strftime(row[10], "%Y-%m-%d") + ","
else:
s_line += ","
# DUE DATE
if l_debug:
print(row[11])
if row[11]:
s_line += datetime.strftime(row[11], "%Y-%m-%d") + ","
else:
s_line += ","
# FINISH DATE
if l_debug:
print(row[12])
if row[12]:
s_line += datetime.strftime(row[12], "%Y-%m-%d") + ","
else:
s_line += ","
# TYPE
if l_debug:
print(row[13])
s_line += '"' + row[13] + '",'
# PRIORITY
if l_debug:
print(row[14])
s_priority: str = '"Inactive"'
if row[14] == "9":
s_priority = '"Closed"'
elif row[14] == "8":
s_priority = '"Continuous"'
elif row[14] == "4":
s_priority = '"Follow-up"'
elif row[14] == "3":
s_priority = '"High"'
elif row[14] == "2":
s_priority = '"Medium"'
elif row[14] == "1":
s_priority = '"Low"'
s_line += s_priority + ","
# ACCUSED
if l_debug:
print(row[15])
s_line += '"' + row[15] + '",'
# ISSUE
if l_debug:
print(row[16])
s_data = row[16].replace(",", "")
s_data = s_data.replace("'", "")
s_data = s_data.replace('"', "")
s_line += '"' + s_data + '",'
# STATUS
if l_debug:
print(row[17])
s_line += '"' + row[17] + '",'
# DEPARTMENT
if l_debug:
print(row[18])
s_line += '"' + row[18] + '",'
# REFERENCE
if l_debug:
print(row[19])
s_data = row[19].replace(",", "")
s_data = s_data.replace("'", "")
s_data = s_data.replace('"', "")
s_line += '"' + s_data + '",'
# VALUE
if l_debug:
print(row[20])
print(s_line)
s_line += str(row[20]) + ","
if l_debug:
print(s_line)
# NOTES_OFFICIAL
if l_debug:
print(row[21])
s_line += '"' + row[21] + '",'
# s_data = row[21].replace(",", "")
# s_data = s_data.replace("'", "")
# s_data = s_data.replace('"', "")
# s_line += '"' + s_data + '",'
# NOTES_OWN
if l_debug:
print(row[22])
s_data = row[22].replace(",", "")
s_data = s_data.replace("'", "")
s_data = s_data.replace('"', "")
s_line += '"' + s_data + '"'
# WRITE TO FILE
funcfile.writelog(s_line, re_path, s_file)
if l_debug:
print(s_line)
funcfile.writelog("%t Special investigation sir report requested by " + s_name)
s_report = "Include all special investigations for the year mentioned and "
s_report += "all previous special investigations with an unclosed priority."
# MAIL THE AUDIT REPORT
if s_name != "" and s_mail != "":
funcfile.writelog("%t Special investigation sir report mailed to " + s_mail)
if l_debug:
print("Send the report...")
s_body: str = "Attached please find special investigation progress (sir) report as requested for " + \
s_year + "."
s_body += "\n\r"
s_body += s_report
funcmail.send(s_name,
s_mail,
"E",
"Report special investigation progress (sir) " + s_year,
s_body,
re_path,
s_file)
s_message += " was mailed to " + s_mail
# POPULATE THE RETURN MESSAGE
s_return_message = s_message
# DELETE THE MAILED FILE
if funcfile.file_delete(re_path, s_file):
funcfile.writelog("%t Special investigation sir report deleted")
if l_debug:
print("Delete the report...")
"""*****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# CLOSE THE LOG WRITER
funcfile.writelog("-" * len("completed: "+s_function))
funcfile.writelog("COMPLETED: " + s_function.upper())
return s_return_message[0:4096]
def 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
s_period = "prev"
else:
s_period = s_year
# ASK QUESTIONS - WHICH TABLE
print("")
print("(S)ingle all payments")
print("(I)nitiator details")
print("(A)pprover details")
print("(C)ost string details")
print("")
s_tabl = input("Which table? (SIAC) ").upper()
print("")
# OPEN THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: REPORT KFS PAYMENTS CURRENT")
funcfile.writelog("-----------------------------------")
print("---------------------------")
print("REPORT KFS PAYMENTS CURRENT")
print("---------------------------")
# DECLARE VARIABLES
so_path = "W:/Kfs/" # Source database path
so_file = "Kfs_" + s_period + ".sqlite" # Source database
l_export: bool = True
s_sql = "" # SQL statements
"""*****************************************************************************
OPEN THE DATABASES
*****************************************************************************"""
print("OPEN THE DATABASES")
s_period: str = "curr"
s_year: str = s_period
so_path = "W:/Kfs/" # Source database path
if s_period == "curr":
so_file = "Kfs_curr.sqlite" # Source database
s_year = funcdate.cur_year()
elif s_period == "prev":
so_file = "Kfs_prev.sqlite" # Source database
s_year = funcdate.prev_year()
else:
so_file = "Kfs_" + s_year + ".sqlite" # Source database
re_path = "R:/Kfs/" # Results path
ed_path = "S:/_external_data/" # external data path
# OPEN THE SCRIPT LOG FILE
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B006_KFS_PERIOD_LIST_DEV")
funcfile.writelog("--------------------------------")
if l_debug:
print("------------------------")
print("B006_KFS_PERIOD_LIST_DEV")
print("------------------------")
"""*****************************************************************************
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:
def Mail(s_trigger, s_subject='', s_body=''):
"""
Function to PREPARE email using parameters from 000m_Mail.csv
:rtype: str
:param s_trigger: Mail function trigger
:param s_subject: Mail subject
:param s_body: Mail body
:return: Text message to indicate successful mail send
"""
# OPEN THE SCRIPT LOG FILE
funcfile.writelog("%t DEFINITION: Send mail " + s_trigger)
# DECLARE VARIABLES
sl_path = "S:/_external_data/"
print("Send email " + s_trigger)
# Read the mail parameters from the 000_Mail.csv file """
co = open(sl_path + "000_mail.csv", "rU")
co_reader = csv.reader(co)
# Read the COLUMN database data
for row in co_reader:
# Populate the local variables
send_mail = False
# Populate the column variables
if row[0] != s_trigger:
continue
elif row[1] == "X":
continue
elif funcstr.isNotBlank(row[9]):
if row[9] == funcdate.cur_daystrip():
send_mail = True
elif row[9] == funcdate.today_dayname():
send_mail = True
else:
send_mail = True
if send_mail:
# Build the mail parameters from the 000m_Mail.csv file
to_name = row[2]
to_address = row[3]
mail_language = row[4]
if s_subject == '':
mail_subject = row[5]
else:
mail_subject = s_subject
if s_body == '':
mail_body = row[6]
else:
mail_body = s_body
file_path = row[7]
file_path = file_path.replace("%PYEAR%", funcdate.prev_year())
file_path = file_path.replace("%PMONTH%", funcdate.prev_month())
file_path = file_path.replace("%CYEAR%", funcdate.cur_year())
file_path = file_path.replace("%CMONTH%", funcdate.cur_month())
file_path = file_path.replace("%TODAY%", funcdate.today_file())
file_name = row[8]
file_name = file_name.replace("%PYEAR%", funcdate.prev_year())
file_name = file_name.replace("%PMONTH%", funcdate.prev_month())
file_name = file_name.replace("%CYEAR%", funcdate.cur_year())
file_name = file_name.replace("%CMONTH%", funcdate.cur_month())
file_name = file_name.replace("%TODAY%", funcdate.today_file())
# Send the mail
s_result = send(to_name, to_address, mail_language, mail_subject, mail_body, file_path, file_name)
# Mail result log
if s_result == "Successfully sent email":
print("MAIL SUCCESS: " + to_address + " (" + to_name + ")")
funcfile.writelog("%t MAIL SUCCESS: " + to_address + " (" + to_name + ")")
else:
print("MAIL FAIL: " + to_address + " (" + to_name + ")")
print("FAIL REASON: " + s_result)
funcfile.writelog("%t MAIL FAIL: " + to_address + " (" + to_name + ")")
funcfile.writelog("%t REASON FAIL: " + s_result)
# Close the imported data file
co.close()
def robot_report_audit_assignment(s_number: str = "",
s_name: str = "",
s_mail: str = ""):
"""
SEARCH VSS.PARTY FOR NAMES, NUMBERS AND ID'S
:param s_number: Assignment number
:param s_name: The name of the requester / recipient
:param s_mail: The requester mail address
:return: str: Info in message format
"""
# VARIABLES
# s_function: str = "D002_report_audit_assignment"
l_debug: bool = False
"""*************************************************************************
ENVIRONMENT
*************************************************************************"""
if l_debug:
print("ENVIRONMENT")
# DECLARE VARIABLES
re_path: str = "R:/Audit/" # Results
l_mess: bool = funcconf.l_mess_project
# l_mess: bool = False
s_file: str = "Report_assignment_" + s_number + ".html"
s_report: str = ""
s_message: str = "Audit assignment report (" + s_number + ") "
# LOG
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: " + s_function.upper())
funcfile.writelog("-" * len("script: " + s_function))
if l_debug:
print(s_function.upper())
# MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator",
"<b>" + s_function.upper() + "</b>")
"""****************************************************************************
OPEN THE DATABASES
****************************************************************************"""
if l_debug:
print("OPEN THE MYSQL DATABASE")
funcfile.writelog("OPEN THE MYSQL DATABASE")
# VARIABLES
s_source_database: str = "Web_ia_nwu"
# OPEN THE SOURCE FILE
ms_from_connection = funcmysql.mysql_open(s_source_database)
ms_from_cursor = ms_from_connection.cursor()
funcfile.writelog("%t OPEN MYSQL DATABASE: " + s_source_database)
"""*****************************************************************************
BUILD THE REPORT
*****************************************************************************"""
funcfile.writelog("BUILD THE REPORT")
if l_debug:
print("BUILD THE REPORT")
# BUILD THE ASSIGNMENT RECORD
for row in ms_from_cursor.execute("SELECT "
"ia_assi_name, "
"ia_assi_report "
"FROM ia_assignment "
"WHERE ia_assi_auto = " + s_number +
";").fetchall():
if l_debug:
print(row[0])
funcfile.writelog("%t Audit assignment report " + s_number +
" requested by " + s_name)
s_report = row[0]
s_message += s_report + " was mailed to " + s_mail
if row[1] != "":
funcfile.writelog(row[1], re_path, s_file)
# BUILD THE FINDING RECORD
for row in ms_from_cursor.execute("SELECT "
"ia_find_name, "
"ia_find_desc, "
"ia_find_criteria, "
"ia_find_procedure, "
"ia_find_condition, "
"ia_find_effect, "
"ia_find_cause, "
"ia_find_risk, "
"ia_find_recommend, "
"ia_find_comment, "
"ia_find_frequency, "
"ia_find_definition, "
"ia_find_reference "
"FROM ia_finding "
"WHERE ia_assi_auto = " + s_number +
";").fetchall():
if l_debug:
print(row[0])
# if row[0] != "":
# funcfile.writelog(row[0], re_path, s_file)
# DESCRIPTION
if row[1] != "":
funcfile.writelog(row[1], re_path, s_file)
# CRITERIA
if row[2] != "":
funcfile.writelog(row[2], re_path, s_file)
# PROCEDURE
if row[3] != "":
funcfile.writelog(row[3], re_path, s_file)
# CONDITION
if row[4] != "":
funcfile.writelog(row[4], re_path, s_file)
# EFFECT
if row[5] != "":
funcfile.writelog(row[5], re_path, s_file)
# CAUSE
if row[6] != "":
funcfile.writelog(row[6], re_path, s_file)
# RISK
if row[7] != "":
funcfile.writelog(row[7], re_path, s_file)
# RECOMMEND
if row[8] != "":
funcfile.writelog(row[8], re_path, s_file)
# COMMENT
if row[9] != "":
funcfile.writelog(row[9], re_path, s_file)
# FREQUENCY
if row[10] != "":
funcfile.writelog(row[10], re_path, s_file)
# DEFINITION
if row[11] != "":
funcfile.writelog(row[11], re_path, s_file)
# REFERENCE
if row[12] != "":
funcfile.writelog(row[12], re_path, s_file)
# MAIL THE AUDIT REPORT
if s_name != "" and s_mail != "":
funcfile.writelog("%t Audit assignment report " + s_number +
" mailed to " + s_mail)
if l_debug:
print("Send the report...")
s_body: str = "Attached please find audit assignment report as requested:\n\r"
s_body += "\n\r"
s_body += s_report
funcmail.send(s_name, s_mail, "E",
"Report audit assignment number (" + s_number + ")",
s_body, re_path, s_file)
# POPULATE THE RETURN MESSAGE
s_return_message = s_message
# DELETE THE MAILED FILE
if funcfile.file_delete(re_path, s_file):
funcfile.writelog("%t Audit assignment report " + s_number +
" deleted")
if l_debug:
print("Delete the report...")
"""*****************************************************************************
END OF SCRIPT
*****************************************************************************"""
funcfile.writelog("END OF SCRIPT")
if l_debug:
print("END OF SCRIPT")
# CLOSE THE LOG WRITER
funcfile.writelog("-" * len("completed: " + s_function))
funcfile.writelog("COMPLETED: " + s_function.upper())
return s_return_message[0:4096]
def 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 = True
l_export = False
# l_mess: bool = funcconf.l_mess_project
l_mess: bool = False
l_record = False
# 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")
""" ****************************************************************************
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 oracle_to_sqlite(s_table="000b_Table - temp.csv", s_tables="TEMP"):
"""
Script to import data from oracle.
:param s_table: Table name of files to convert
:param s_tables: Table set name
:return: Nothing
"""
# DECLARE VARIABLES
l_mess: bool = funcconf.l_mess_project
i_mess: int = 0
sl_path = "S:/"
so_dsn = "" # Source file name
so_usr = "" # Source file user name
so_pwd = "" # Source file password
de_fil = "" # Destination file name
de_pat = "" # Destination file path
tb_own = "" # Table owner
tb_nam = "" # Table name
tb_whe = "" # Table filter / where clause
tb_ord = "" # Table sort
tb_alt = "" # Table alternative name
tb_sch = "" # Table schedule
co_nam = "" # Column name
co_ana = "" # Column alternate name
co_typ = "" # Column type
co_len = 0 # Column width
co_dec = 0 # Column decimals
sco_nam = "" # Column string with actual names
sco_ana = "" # Column string with alternate names
sco_dro = "" # Column drop table
ssql_create = "" # Sql to create a table
# LOG
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: A001_ORACLE_TO_SQLITE")
funcfile.writelog("-----------------------------")
funcfile.writelog("TABLESET: " + s_table.upper())
# MESSAGE TO ADMIN
if funcconf.l_mess_project:
funcsms.send_telegram('', 'administrator',
'<b>A001 ' + s_tables + ' Oracle import</b>')
# DATABASE from text ***********************************************************
# Read the database parameters from the 01_Database.csv file
db = open(sl_path + "000a_Database.csv", newline=None)
db_reader = csv.reader(db)
# Read the DATABASE database data
for row in db_reader:
# Populate the database variables
if row[0] == "DESC":
continue
else:
funcfile.writelog("OPEN DATABASE: " + row[0])
print("--------")
print("DATABASE: " + row[0])
print("--------")
so_dsn = row[1]
so_usr = row[2]
so_pwd = row[3]
de_fil = row[4]
de_pat = row[5]
de_sch = row[6]
so_dri = row[7]
so_dnq = row[8]
i_mess = 0
# Open the source ORACLE database
# print("DSN="+so_dsn+";PWD="+so_pwd)
# Driver={Oracle in OraClient11g_home1}; DBQ=myserver.mydomain.com:1521/mySid; Uid=myUsername; Pwd=myPassword;
# with cx_Oracle.connect(so_usr, so_pwd, so_dsn, ) as so_con:
# with pyodbc.connect("DSN="+so_dsn+"; PWD="+so_pwd) as so_con:
# with cx_Oracle.connect(so_usr, so_pwd, so_dsn, ) as so_con:
# print(so_dsn)
# print(so_usr)
# print(so_pwd)
# print(so_dri)
# print(so_dnq)
# print(de_pat)
# print(de_fil)
# s_conn = "Driver={"+so_dri+"}; DBQ="+so_dnq+"; UID="+so_usr+"; PWD="+so_pwd+";"
s_conn = "DSN=" + so_dsn + "; PWD=" + so_pwd
with pyodbc.connect(s_conn) as so_con:
so_cur = so_con.cursor()
# Open the destination SQLite database
# print(de_pat+de_fil)
with sqlite3.connect(de_pat + de_fil) as de_con:
de_cur = de_con.cursor()
# TABLE info from text *****************************************************
# Read the table parameters from the 02_Table.csv file
tb = open(sl_path + s_table, newline=None)
tb_reader = csv.reader(tb)
# Read the TABLE database data
for row in tb_reader:
tb_own = "" # Table owner
tb_nam = "" # Table name
tb_whe = "" # SQL where clause
tb_ord = "" # SQL sort clause
tb_alt = "" # Table alternative name
tb_sch = "" # Table schedule
tb_extract = False
# Populate the table variables
if row[0] == "DESC":
continue
else:
if row[1] != de_fil:
# Ignore the header
continue
elif funcstr.isNotBlank(row[7]):
if row[7] == "X":
# Do not do
continue
elif row[7] == funcdate.today_dayname():
# Do if table schedule = day of week
tb_extract = True
elif row[7] == funcdate.cur_daystrip():
# Do if table schedule = day of month
tb_extract = True
else:
continue
else:
tb_extract = True
if tb_extract:
tb_own = row[2] # Table owner
tb_nam = row[3] # Table name
tb_whe = row[4] # SQL where clause
tb_ord = row[5] # SQL sort clause
tb_alt = row[6] # Table alternative name
tb_sch = row[7] # Table schedule
if l_mess and i_mess == 0:
i_mess += 1
if funcconf.l_mess_project:
funcsms.send_telegram('', 'administrator',
de_fil.lower())
else:
continue
# COLUMN info from text ************************************************
# Read the table parameters from the 02_Table.csv file """
co = open(sl_path + "000c_Column.csv", newline=None)
co_reader = csv.reader(co)
# Read the COLUMN database data
sco_nam = ""
sco_lst = ""
lco_lst = []
sty_nam = ""
sty_lst = ""
lty_lst = []
sma_nam = ""
sma_lst = ""
lma_lst = []
sco_ana = ""
sco_dro = ""
for row in co_reader:
# Populate the column variables
if row[0] == "DESC":
continue
else:
if row[1] != so_dsn:
continue
else:
if row[2] != tb_nam:
continue
else:
# print("COLUMN: " + row[4])
# Populate variables
co_nam = row[4] # Column name
co_ana = row[8] # Column alternate name
co_typ = row[5] # Column type
co_len = row[6] # Column width
co_dec = row[7] # Column decimals
# Populate column string with actual names
sco_nam += ''.join(row[4]) + ", "
sco_lst += row[4] + " "
# Populate column string with comlumn type
sty_nam += ''.join(row[5]) + ", "
sty_lst += row[5] + " "
# Populate column string with begin end date marker
s_startdate = """
STARTDATE*
STARTDATETIME*
KSTARTDATETIME*
DATE_FROM*
START_DATE_ACTIVE
"""
s_enddate = """
ENDDATE*
ENDDATETIME*
KSTARTDATETIME*
DATE_TO*
END_DATE_ACTIVE
"""
if row[4] in s_startdate:
sma_nam += "B, "
sma_lst += "B "
elif row[4] in s_enddate:
sma_nam += "E, "
sma_lst += "E "
else:
sma_nam += "N, "
sma_lst += "N "
# Populate column string with alternate names
if co_typ == "NUMBER" and co_dec != "0":
sco_ana = sco_ana + ''.join(row[8]) + " REAL,"
elif co_typ == "NUMBER":
sco_ana = sco_ana + ''.join(
row[8]) + " INTEGER,"
elif co_typ == "REAL":
sco_ana = sco_ana + ''.join(row[8]) + " REAL,"
else:
sco_ana = sco_ana + ''.join(row[8]) + " TEXT,"
# Create the sql create table variable
sco_nam = sco_nam.rstrip(", ")
lco_lst = sco_lst.split()
sty_nam = sty_nam.rstrip(", ")
lty_lst = sty_lst.split()
sma_nam = sma_nam.rstrip(", ")
lma_lst = sma_lst.split()
if tb_alt != "":
if "%CYEAR%" in tb_alt:
tb_alt = tb_alt.replace("%CYEAR%", funcdate.cur_year())
if "%CMONTH%" in tb_alt:
tb_alt = tb_alt.replace("%CMONTH%", funcdate.cur_month())
if "%PYEAR%" in tb_alt:
tb_alt = tb_alt.replace("%PYEAR%", funcdate.prev_year())
if "%PMONTH%" in tb_alt:
tb_alt = tb_alt.replace("%PMONTH%", funcdate.prev_month())
ssql_dro = "DROP TABLE IF EXISTS " + tb_alt
ssql_create = "CREATE TABLE " + tb_alt + "(" + sco_ana + ")"
else:
ssql_dro = "DROP TABLE IF EXISTS " + tb_nam
ssql_create = "CREATE TABLE " + tb_nam + "(" + sco_ana + ")"
ssql_create = ssql_create.replace(",)", ")", 1)
# print(ssql_create)
funcfile.writelog("%t WRITE TABLE: " + tb_nam + "(" + tb_alt + ")")
print("TABLE: " + tb_nam + "(" + tb_alt + ")")
co.close()
# Create the DESTINATION table
de_cur.execute(ssql_dro)
de_cur.execute(ssql_create)
# Write the data
ssql_str = "SELECT "
ssql_str += sco_nam
ssql_str += " FROM "
if tb_own == "":
ssql_str += tb_nam
else:
ssql_str += tb_own + "." + tb_nam
if tb_whe != "":
if "%CYEAR%" in tb_whe:
tb_whe = tb_whe.replace("%CYEAR%", funcdate.cur_year())
if "%CYEARB%" in tb_whe:
tb_whe = tb_whe.replace("%CYEARB%",
funcdate.cur_yearbegin())
if "%CYEARE%" in tb_whe:
tb_whe = tb_whe.replace("%CYEARE%", funcdate.cur_yearend())
if "%CMONTHB%" in tb_whe:
tb_whe = tb_whe.replace("%CMONTHB%",
funcdate.cur_monthbegin())
if "%CMONTHE%" in tb_whe:
tb_whe = tb_whe.replace("%CMONTHE%",
funcdate.cur_monthend())
if "%PYEAR%" in tb_whe:
tb_whe = tb_whe.replace("%PYEAR%", funcdate.prev_year())
if "%PMONTHB%" in tb_whe:
tb_whe = tb_whe.replace("%PMONTHB%",
funcdate.prev_monthbegin())
if "%PMONTHE%" in tb_whe:
tb_whe = tb_whe.replace("%PMONTHE%",
funcdate.prev_monthend())
if "%PYEARB%" in tb_whe:
tb_whe = tb_whe.replace("%PYEARB%",
funcdate.prev_yearbegin())
if "%PYEARE%" in tb_whe:
tb_whe = tb_whe.replace("%PYEARE%",
funcdate.prev_yearend())
ssql_str = ssql_str + " " + tb_whe
if tb_ord != "":
ssql_str = ssql_str + " ORDER BY " + tb_ord
# print(ssql_str)
# print("Name")
# print(lco_lst)
# print("Type")
# print(lty_lst)
# print("Marker")
# print(lma_lst)
for result in funcsys.ResultIter(so_cur.execute(ssql_str)):
c_text = ""
c_test = ""
c_data = "("
i = 0
for item in result:
if lty_lst[i] == "DATE":
if str(item) == "None" or str(item) == "":
if lma_lst[i] == "B":
c_test = "0001-01-01"
elif lma_lst[i] == "E":
c_test = "4712-12-31"
else:
c_test = str(item)
c_test = c_test[0:10]
c_data += "'" + c_test + "',"
elif lty_lst[i] == "DATETIME":
if str(item) == "None" or str(item) == "":
if lma_lst[i] == "B":
c_test = "0001-01-01 00:00:00"
elif lma_lst[i] == "E":
c_test = "4712-12-31 23:59:59"
else:
c_test = str(item)
c_data += "'" + c_test + "',"
elif lty_lst[i] == "NUMBER":
c_test = str(item)
c_test = c_test.replace("None", "0")
c_test = c_test.replace(".0", "")
c_data += "'" + c_test + "',"
else:
c_test = str(item)
c_test = c_test.replace(",", "")
c_test = c_test.replace("'", "")
c_test = c_test.replace('"', '')
c_test = c_test.replace("None", "")
c_data += "'" + c_test + "',"
i += 1
c_data += ")"
c_data = c_data.replace(",)", ")", 1)
if tb_alt != "":
c_text = 'INSERT INTO ' + tb_alt + ' VALUES' + c_data
else:
c_text = 'INSERT INTO ' + tb_nam + ' VALUES' + c_data
# print(c_text)
de_cur.execute(c_text)
de_con.commit()
# Wait a few seconds for log file to close
# time.sleep(10)
# Close 02_Table.csv
tb.close()
# Display the number of tables in the destination
for table in de_cur.execute(
"SELECT name FROM sqlite_master WHERE type = 'table'"):
print("TABLE in DESTINATION: ", table[0])
# Display the number of tables in the destination
for table in de_cur.execute(
"SELECT name FROM sqlite_master WHERE type = 'view'"):
print("VIEW in DESTINATION: ", table[0])
# Close the destination
de_con.close()
# Close the source
so_con.close()
# Close 01_Database.csv
db.close()
# Close the log writer *********************************************************
funcfile.writelog("--------------------------------")
funcfile.writelog("COMPLETED: A001_ORACLE_TO_SQLITE")
return
"""
Script to EXECUTE the VSS LISTS script.
"""
# IMPORT OWN MODULES
from _my_modules import funcfile
from _my_modules import funcsys
import B007_vss_period_list
# OPEN THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B007_RUN_VSS_PERIOD_LIST")
funcfile.writelog("--------------------------------")
# RUN KFS LISTS
try:
B007_vss_period_list.vss_period_list("prev")
except Exception as e:
funcsys.ErrMessage(e)
# CLOSE THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("COMPLETED: B007_VSS_PERIOD_LIST")
funcfile.writelog("-------------------------------")
from _my_modules import funcdate
from _my_modules import funcfile
from _my_modules import funcstudent
""" INDEX **********************************************************************
ENVIRONMENT
OPEN THE DATABASES
TEMPORARY AREA
BEGIN OF SCRIPT
END OF SCRIPT
*****************************************************************************"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# SCRIPT LOG FILE
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B003_VSS_LISTS_XDEV")
funcfile.writelog("---------------------------")
print("-------------------")
print("B003_VSS_LISTS_XDEV")
print("-------------------")
# DECLARE VARIABLES
ed_path = "S:/_external_data/" # External data path
so_path = "W:/Vss/" # Source database path
so_file = "Vss.sqlite" # Source database
re_path = "R:/Vss/"
l_vacuum: bool = False
s_period: str = "curr"
s_year: str = "2019"
"""*****************************************************************************
from _my_modules import funcsms
""" INDEX **********************************************************************
ENVIRONMENT
BEGIN OF SCRIPT
END OF SCRIPT
*****************************************************************************"""
"""*****************************************************************************
ENVIRONMENT
*****************************************************************************"""
# DECLARE VARIABLES
l_mess: bool = True
i_counter: int = 0
# SCRIPT LOG FILE
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: A003_TABLE_VACUUM")
funcfile.writelog("-------------------------")
print("-----------------")
print("A003_TABLE_VACUUM")
print("-----------------")
# SEND MESSAGE
if l_mess:
funcsms.send_telegram("", "administrator",
"<b>Vacuming</b> the test finding database tables.")
"""*****************************************************************************
BEGIN OF SCRIPT
*****************************************************************************"""
print("BEGIN OF SCRIPT")
funcfile.writelog("BEGIN OF SCRIPT")
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
