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