import sqlite3
import sys
# Add own module path
sys.path.append('S:/_my_modules')
# Import own modules
import funcdate
import funccsv
import funcfile
import funcmail
import funcsys
# Open the script log file ******************************************************
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C300_TEST_STUDENT_GENERAL_DEV")
funcfile.writelog("----------------------------------=--")
print("-------------------------")
print("C300_TEST_STUDENT_GENERAL")
print("-------------------------")
ilog_severity = 1
# 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
**************************************************************************** """
# Import python modules
import sqlite3
import sys
# Add own module path
sys.path.append('X:\\Python\\_my_modules')
# Import own modules
import funcdate
import funccsv
import funcfile
# Log
funcfile.writelog()
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: Report_vss_residences")
# Declare variables
so_path = "W:/" #Source database path
so_file = "Vss_residence.sqlite" #Source database
s_sql = "" #SQL statements
# 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)
so_curs.execute("ATTACH DATABASE 'W:/Vss.sqlite' AS 'VSS'")
# Import python modules
import datetime
import sqlite3
import sys
# Add own module path
sys.path.append('S:/_my_modules')
# Import own modules
import funcdate
import funccsv
import funcfile
# Open the script log file ******************************************************
funcfile.writelog()
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: Report_people_leave")
ilog_severity = 1
# Declare variables
so_path = "W:/People_leave/" #Source database path
so_file = "People_leave.sqlite" #Source database
s_sql = "" #SQL statements
# 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)
# Import python modules
import datetime
import sqlite3
import sys
# Add own module path
sys.path.append('S:/_my_modules')
# Import own modules
import funcdate
import funccsv
import funcfile
# Open the script log file ******************************************************
funcfile.writelog()
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: Report_people_conflict")
ilog_severity = 1
# Declare variables
so_path = "W:/People_conflict/" #Source database path
so_file = "People_conflict.sqlite" #Source database
s_sql = "" #SQL statements
# 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)
so_curs.execute("ATTACH DATABASE 'W:/People/People.sqlite' AS 'PEOPLE'")
# ADD OWN MODULE PATH
sys.path.append('S:/_my_modules')
# IMPORT PYTHON OBJECTS
import csv
import datetime
import sqlite3
# IMPORT OWN MODULES
import funcdate
import funccsv
import funcfile
# SCRIPT LOG FILE
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: REPORT_VSS_DEFERMENTS")
funcfile.writelog("-----------------------------")
print("---------------------")
print("REPORT_VSS_DEFERMENTS")
print("---------------------")
ilog_severity = 1
# DECLARE VARIABLES
# s_period = "prev"
# s_year = "2018"
so_path = "W:/Vss_deferment/" #Source database path
so_file = "Vss_deferment.sqlite" #Source database
re_path = "R:/Vss/"
ed_path = "S:/_external_data/"
s_sql = "" #SQL statements
import datetime
import sqlite3
import sys
# Add own module path
sys.path.append('S:/_my_modules')
#print(sys.path)
# Import own modules
import funcdate
import funccsv
import funcfile
# Open the script log file ******************************************************
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: B003_VSS_LISTS_DEV")
funcfile.writelog("--------------------------")
print("--------------")
print("B003_VSS_LISTS")
print("--------------")
ilog_severity = 1
# Declare variables
so_path = "W:/Vss/" #Source database path
re_path = "R:/Vss/" #Results
so_file = "Vss.sqlite" #Source database
ed_path = "S:/_external_data/"
s_sql = "" #SQL statements
# Open the SOURCE file
def Fabrik_formcreate(s_input="F",
s_fl="New FORM to setup",
s_db="Web_ia_joomla",
s_tb="ianwu_fabrik_forms"):
""" PARAMETERS *************************************************************
s_input = F=do not ask parameters(default) T=ask parameters
s_fl = Form label
s_db = Joomla database
s_tb = Joomla table name
*************************************************************************"""
""" INDEX ******************************************************************
ENVIRONMENT
INPUT
OPEN DATABASE
INSERT GROUP RECORD
*************************************************************************"""
print("FABRIK CREATE FORM STEP 2")
print("-------------------------")
print("ENVIRONMENT")
# IMPORT SYSTEM OBJECTS
import sys
# OWN MODULE PATH
sys.path.append('S:/_my_modules')
# IMPORT PYTHON OBJECTS
import pyodbc
# IMPORT FUNCTIONS
import funcfile
import funcmysql
# DECLARE VARIABLES
s_sql = ""
s_created_by = "854"
"""*************************************************************************
INPUT
*************************************************************************"""
print("INPUT")
# Input the joomla mysql fabrik DATABASE name
s_dbi = s_db
if s_input == "T":
print("")
print("Default:" + s_db)
s_dbi = input("Fabrik DATABASE name? ")
if s_dbi == "":
s_dbi = s_db
# Input the joomla mysql fabrik TABLE name
s_tbi = s_tb
if s_input == "T":
print("")
print("Default:" + s_tb)
s_tbi = input("Fabrik TABLE name? ")
if s_tbi == "":
s_tbi = s_tb
# Input the joomla mysql fabrik FORM name
s_fli = s_fl
if s_input == "T":
print("")
print("Default:" + s_fl)
s_fli = input("Fabrik FORM label? ")
if s_fli == "":
s_fli = s_fl
print("")
"""*************************************************************************
OPEN DATABASE
*************************************************************************"""
print("OPEN DATABASE")
funcfile.writelog("OPEN DATABASE")
# Connect to the oracle database
cnxn = funcmysql.mysql_open(s_dbi)
curs = cnxn.cursor()
funcfile.writelog("%t OPEN DATABASE: " + s_dbi)
"""*************************************************************************
INSERT FORM RECORD
*************************************************************************"""
print("INSERT FORM RECORD")
funcfile.writelog("INSERT FORM RECORD")
# INSERT FORM RECORD
s_sql = "INSERT INTO `" + s_tbi + "` (" + """
`label`,
`record_in_database`,
`error`,
`intro`,
`created`,
`created_by`,
`created_by_alias`,
`modified`,
`modified_by`,
`checked_out`,
`checked_out_time`,
`publish_up`,
`publish_down`,
`reset_button_label`,
`submit_button_label`,
`form_template`,
`view_only_template`,
`published`,
`private`,
`params`
""" + ") VALUES (" + """
'%LABEL%',
1,
'Some parts of your form have not been correctly filled in',
'',
NOW(),
%CREATED_BY%,
'Python',
'0000-00-00 00:00:00',
0,
0,
'0000-00-00 00:00:00',
'0000-00-00 00:00:00',
'0000-00-00 00:00:00',
'Reset',
'Save',
'bootstrap',
'bootstrap',
1,
0,
'{
\"outro\":\"\",
\"reset_button\":\"0\",
\"reset_button_label\":\"Reset\",
\"reset_button_class\":\"btn-warning\",
\"reset_icon\":\"\",
\"reset_icon_location\":\"before\",
\"copy_button\":\"0\",
\"copy_button_label\":\"Save as copy\",
\"copy_button_class\":\"\",
\"copy_icon\":\"\",
\"copy_icon_location\":\"before\",
\"goback_button\":\"0\",
\"goback_button_label\":\"Go back\",
\"goback_button_class\":\"\",
\"goback_icon\":\"\",
\"goback_icon_location\":\"before\",
\"apply_button\":\"0\",
\"apply_button_label\":\"Apply\",
\"apply_button_class\":\"\",
\"apply_icon\":\"\",
\"apply_icon_location\":\"before\",
\"delete_button\":\"0\",
\"delete_button_label\":\"Delete\",
\"delete_button_class\":\"btn-danger\",
\"delete_icon\":\"\",
\"delete_icon_location\":\"before\",
\"submit_button\":\"1\",
\"submit_button_label\":\"Save\",
\"save_button_class\":\"btn-primary\",
\"save_icon\":\"\",
\"save_icon_location\":\"before\",
\"submit_on_enter\":\"0\",
\"labels_above\":\"0\",
\"labels_above_details\":\"0\",
\"pdf_template\":\"admin\",
\"pdf_orientation\":\"portrait\",
\"pdf_size\":\"letter\",
\"show_title\":\"1\",
\"print\":\"\",
\"email\":\"\",
\"pdf\":\"\",
\"admin_form_template\":\"\",
\"admin_details_template\":\"\",
\"note\":\"\",
\"show_referring_table_releated_data\":\"0\",
\"tiplocation\":\"tip\",
\"process_jplugins\":\"2\",
\"ajax_validations\":\"0\",
\"ajax_validations_toggle_submit\":\"0\",
\"submit_success_msg\":\"\",
\"suppress_msgs\":\"0\",
\"show_loader_on_submit\":\"0\",
\"spoof_check\":\"1\",
\"multipage_save\":\"0\"
}'
""" + ");"
#print(s_sql) # DEBUG
s_sql = s_sql.replace("%LABEL%", s_fli)
s_sql = s_sql.replace("%CREATED_BY%", s_created_by)
curs.execute(s_sql)
cnxn.commit()
funcfile.writelog("%t INSERT RECORD: " + s_dbi + "." + s_tbi + ":" + s_fli)
# FORM DEFAULT PARAMETERS
"""
(
38,
'TEST List',
1,
'Some parts of your form have not been correctly filled in',
'',
'2019-03-20 05:14:04',
842,
'Albertjvr',
'0000-00-00 00:00:00',
0,
0,
'0000-00-00 00:00:00',
'0000-00-00 00:00:00',
'0000-00-00 00:00:00',
'',
'Save',
'bootstrap',
'bootstrap',
1,
0,
'{
\"outro\":\"\",
\"reset_button\":\"0\",
\"reset_button_label\":\"Reset\",
\"reset_button_class\":\"btn-warning\",
\"reset_icon\":\"\",
\"reset_icon_location\":\"before\",
\"copy_button\":\"0\",
\"copy_button_label\":\"Save as copy\",
\"copy_button_class\":\"\",
\"copy_icon\":\"\",
\"copy_icon_location\":\"before\",
\"goback_button\":\"0\",
\"goback_button_label\":\"Go back\",
\"goback_button_class\":\"\",
\"goback_icon\":\"\",
\"goback_icon_location\":\"before\",
\"apply_button\":\"0\",
\"apply_button_label\":\"Apply\",
\"apply_button_class\":\"\",
\"apply_icon\":\"\",
\"apply_icon_location\":\"before\",
\"delete_button\":\"0\",
\"delete_button_label\":\"Delete\",
\"delete_button_class\":\"btn-danger\",
\"delete_icon\":\"\",
\"delete_icon_location\":\"before\",
\"submit_button\":\"1\",
\"submit_button_label\":\"Save\",
\"save_button_class\":\"btn-primary\",
\"save_icon\":\"\",
\"save_icon_location\":\"before\",
\"submit_on_enter\":\"0\",
\"labels_above\":\"0\",
\"labels_above_details\":\"0\",
\"pdf_template\":\"admin\",
\"pdf_orientation\":\"portrait\",
\"pdf_size\":\"letter\",
\"show_title\":\"1\",
\"print\":\"\",
\"email\":\"\",
\"pdf\":\"\",
\"admin_form_template\":\"\",
\"admin_details_template\":\"\",
\"note\":\"\",
\"show_referring_table_releated_data\":\"0\",
\"tiplocation\":\"tip\",
\"process_jplugins\":\"2\",
\"ajax_validations\":\"0\",
\"ajax_validations_toggle_submit\":\"0\",
\"submit_success_msg\":\"\",
\"suppress_msgs\":\"0\",
\"show_loader_on_submit\":\"0\",
\"spoof_check\":\"1\",
\"multipage_save\":\"0\"
}'
);
"""
# GET NEWLY CREATED FORM NUMBER
curs.execute("SELECT " + s_tbi + ".id, " + s_tbi + ".label FROM " + s_tbi +
" WHERE " + s_tbi + ".label = '" + s_fli + "'")
for row in curs.fetchall():
print("Created list " + str(row[0]))
funcfile.writelog("%t FORM CREATED: " + str(row[0]))
i_return = row[0]
# RETURN NEWLY CREATED FORM NUMBER
return i_return
import sys
# OWN MODULE PATH
sys.path.append('S:/_my_modules')
sys.path.append('S:/')
# IMPORT MODULES
import funcdate
import funcfile
import funcsys
# OPEN THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C301_REPORT_STUDENT_DEFERMENT_PERI")
funcfile.writelog("------------------------------------------")
# OBTAIN DEFERMENT YEAR
print("Note")
print("----")
print("1. Period Students should exist in VSS for the year.")
print("2. VSS Transactional data should exist in VSS for the year.")
print("")
s_year = input("Deferments year? (yyyy) ")
print("")
funcfile.writelog("DEFERMENT YEAR " + s_year)
import C301_report_student_deferment
try:
C301_report_student_deferment.Studdeb_deferments('peri', s_year)
except Exception as e:
# Input the whether tables must be overwritten
print("")
print("Default:" + sd_droptable)
s_droptable = input("DROP Tables (y/n)? ")
if s_droptable == "":
s_droptable = sd_droptable
# Input the whether default fields should be added
print("")
print("Default:" + sd_addfields)
s_addfields = input("ADD default fields (y/n)? ")
if s_addfields == "":
s_addfields = sd_addfields
# Script log file
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: WEB_IA_NWU")
funcfile.writelog("------------------")
# Connect to the oracle database
cnxn = funcmysql.mysql_open(s_database)
curs = cnxn.cursor()
funcfile.writelog("%t OPEN DATABASE: " + s_database)
# Create FINDING table ************************************************************
if s_droptable == "y":
curs.execute("DROP TABLE IF EXISTS ia_finding")
funcfile.writelog("%t DROPPED TABLE: FINDING(ia_finding)")
s_sql = """
CREATE TABLE IF NOT EXISTS ia_finding (
`ia_find_auto` int(11) NOT NULL,
# Import python modules
import datetime
import sqlite3
import sys
# Add own module path
sys.path.append('S:/_my_modules')
# Import own modules
import funcdate
import funccsv
import funcfile
# Open the script log file ******************************************************
funcfile.writelog()
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: Report_people_leave_per_person")
ilog_severity = 1
# Declare variables
so_path = "W:/People_leave/" #Source database path
so_file = "People_leave.sqlite" #Source database
s_sql = "" #SQL statements
# 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)
# IMPORT FUNCTION FILES
import funcfile
import funcmysql
# DECLARE VARIABLES
sd_database = "Web_ia_joomla"
sd_group = 0
sd_form = 0
s_sql = ""
s_created_by = "854"
print("FABRIK BUILD BASIC TABLE SETUP")
print("------------------------------")
# OPEN THE SCRIPTE LOG FILE
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: FABRIK BUILD BASIC TABLE STRUCTURE")
funcfile.writelog("------------------------------------------")
"""*****************************************************************************
OPEN THE DATABASE
*****************************************************************************"""
print("OPEN THE DATABASE")
funcfile.writelog("OPEN THE DATABASE ")
# SET THE JOOMLA MYSQL FABRIK DATABASE NAME
s_database = ""
print("")
print("Default:"+sd_database)
#s_database = input("Fabrik DATABASE name? ")
if s_database == "":
def fabrik_groupcreate(b_input=False, s_db="Web_ia_joomla", s_tb="ianwu_fabrik_groups", s_lb="New GROUP to setup"):
"""
Create Joomla Fabrik group record
:param b_input: Input database parameters if True (Default=False)
:param s_db: Mysql database (Default=Web_ia_joomla)
:param s_tb: Mysql table (Default=ianwu_fabrik_groups)
:param s_lb: Fabrik group label (Default=New GROUP to setup)
:return:
"""
""" INDEX ******************************************************************
ENVIRONMENT
INPUT
OPEN DATABASE
INSERT GROUP RECORD
*************************************************************************"""
print("FABRIK CREATE GROUP STEP 1")
print("--------------------------")
print("ENVIRONMENT")
# IMPORT SYSTEM OBJECTS
import sys
# OWN MODULE PATH
sys.path.append('S:/_my_modules')
# IMPORT PYTHON OBJECTS
import pyodbc
# IMPORT FUNCTIONS
import funcfile
import funcmysql
# DECLARE VARIABLES
s_sql = ""
s_created_by = "854"
"""*************************************************************************
INPUT
*************************************************************************"""
print("INPUT")
# Input the joomla mysql fabrik DATABASE name
s_dbi = s_db
if b_input:
print("")
print("Default:"+s_db)
s_dbi = input("Fabrik DATABASE name? ")
if s_dbi == "":
s_dbi = s_db
# Input the joomla mysql fabrik TABLE name
s_tbi = s_tb
if b_input:
print("")
print("Default:"+s_tb)
s_tbi = input("Fabrik TABLE name? ")
if s_tbi == "":
s_tbi = s_tb
# Input the joomla mysql fabrik GROUP label
s_lbi = s_lb
if b_input:
print("")
print("Default:"+s_lb)
s_lbi = input("Fabrik GROUP label? ")
if s_lbi == "":
s_lbi = s_lb
print("")
"""*************************************************************************
OPEN DATABASE
*************************************************************************"""
print("OPEN DATABASE")
funcfile.writelog("OPEN DATABASE")
# Connect to the oracle database
cnxn = funcmysql.mysql_open(s_dbi)
curs = cnxn.cursor()
funcfile.writelog("%t OPEN DATABASE: " + s_dbi)
"""*************************************************************************
INSERT GROUP RECORD
*************************************************************************"""
print("INSERT GROUP RECORD")
funcfile.writelog("INSERT GROUP RECORD")
# INSERT GROUP RECORD
s_sql = "INSERT INTO `" + s_tbi + "` (" + """
`name`,
`css`,
`label`,
`published`,
`created`,
`created_by`,
`created_by_alias`,
`modified`,
`modified_by`,
`checked_out`,
`checked_out_time`,
`is_join`,
`private`,
`params`
""" + ") VALUES (" + """
'%LABEL%',
'',
'Add/Edit %LABEL%',
1,
NOW(),
%CREATED_BY%,
'Python',
'0000-00-00 00:00:00',
0,
0,
'0000-00-00 00:00:00',
0,
0,
'{
\"split_page\":\"0\",
\"list_view_and_query\":\"1\",
\"access\":\"1\",
\"intro\":\"\",
\"outro\":\"\",
\"repeat_group_button\":\"0\",
\"repeat_template\":\"repeatgroup\",
\"repeat_max\":\"\",
\"repeat_min\":\"\",
\"repeat_num_element\":\"\",
\"repeat_error_message\":\"\",
\"repeat_no_data_message\":\"\",
\"repeat_intro\":\"\",
\"repeat_add_access\":\"1\",
\"repeat_delete_access\":\"1\",
\"repeat_delete_access_user\":\"\",
\"repeat_copy_element_values\":\"0\",
\"group_columns\":\"1\",
\"group_column_widths\":\"\",
\"repeat_group_show_first\":\"1\",
\"random\":\"0\",
\"labels_above\":\"-1\",
\"labels_above_details\":\"-1\"
}'
""" + ");"
#print(s_sql) # DEBUG
s_sql = s_sql.replace("%LABEL%",s_lbi)
s_sql = s_sql.replace("%CREATED_BY%",s_created_by)
curs.execute(s_sql)
cnxn.commit()
funcfile.writelog("%t INSERT RECORD: "+s_dbi+"."+s_tbi+": "+s_lbi)
# GROUP DEFAULT PARAMETERS
"""
(
92,
'TEST List',
'',
'TEST List',
1,
'2019-03-20 05:14:04',
842,
'Albertjvr',
'0000-00-00 00:00:00',
0,
0,
'0000-00-00 00:00:00',
0,
0,
'{
\"split_page\":\"0\",
\"list_view_and_query\":\"1\",
\"access\":\"1\",\"intro\":\"\",
\"outro\":\"\",
\"repeat_group_button\":0,
\"repeat_template\":\"repeatgroup\",
\"repeat_max\":\"\",
\"repeat_min\":\"\",
\"repeat_num_element\":\"\",
\"repeat_error_message\":\"\",
\"repeat_no_data_message\":\"\",
\"repeat_intro\":\"\",
\"repeat_add_access\":\"1\",
\"repeat_delete_access\":\"1\",
\"repeat_delete_access_user\":\"\",
\"repeat_copy_element_values\":\"0\",
\"group_columns\":\"1\",
\"group_column_widths\":\"\",
\"repeat_group_show_first\":1,
\"random\":\"0\",
\"labels_above\":\"-1\",
\"labels_above_details\":\"-1\"
}'
);
"""
# GET NEWLY CREATED GROUP NUMBER
curs.execute("SELECT "+s_tbi+".id, "+s_tbi+".name FROM "+s_tbi+" WHERE "+s_tbi+".name = '" + s_lbi +"'")
for row in curs.fetchall():
print("Created group "+str(row[0]))
funcfile.writelog("%t GROUP CREATED: "+str(row[0]))
i_return = row[0]
# RETURN NEWLY CREATED GROUP NUMBER
return i_return
import sqlite3
import sys
# Add own module path
sys.path.append('S:/_my_modules')
# Import own modules
import funcfile
import funcstudent
# Open the script log file ******************************************************
print("----------------------------")
print("REPORT_VSS_STUDENT_LIST_PREV")
print("----------------------------")
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: REPORT_VSS_STUDENT_LIST_PREV")
funcfile.writelog("------------------------------------")
ilog_severity = 1
# Declare variables
so_path = "W:/Vss/" #Source database path
so_file = "Vss.sqlite" #Source database
re_path = "R:/Vss/" #Results
ed_path = "S:/_external_data/"
s_sql = "" #SQL statements
l_export = False
# Open the SOURCE file
with sqlite3.connect(so_path + so_file) as so_conn:
so_curs = so_conn.cursor()
import sys
# OWN MODULE PATH
sys.path.append('S:/_my_modules')
sys.path.append('S:/')
# IMPORT MODULES
import funcdate
import funcfile
import funcsys
# OPEN THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C301_REPORT_STUDENT_DEFERMENT_PREV")
funcfile.writelog("------------------------------------------")
import C301_report_student_deferment
try:
C301_report_student_deferment.Studdeb_deferments('prev',
funcdate.prev_year())
except Exception as e:
funcsys.ErrMessage(e)
# CLOSE THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("COMPLETED: C301_REPORT_STUDENT_DEFERMENT_PREV")
funcfile.writelog("---------------------------------------------")
def assign01(so_conn, s_table, s_from, s_to, s_on, s_mess):
"""
Function to build ASSIGNMENTS (X000_Assignment) for different date periods
:param so_conn: Connection string
:param s_table: Table name to create
:param s_from: Period start date
:param s_to: Period end date
:param s_on: On which date
:param s_mess: Print message
:return:
"""
# Print and connect
print(s_mess)
so_curs = so_conn.cursor()
# Build the table
s_sql = "CREATE TABLE " + s_table + " AS" + """
SELECT
ASSI.ASS_ID,
ASSI.PERSON_ID,
ASSI.ASSIGNMENT_NUMBER As ASS_NUMB,
ASSI.SERVICE_DATE_START As EMP_START,
ASSI.EFFECTIVE_START_DATE As ASS_START,
ASSI.EFFECTIVE_END_DATE As ASS_END,
ASSI.SERVICE_DATE_ACTUAL_TERMINATION As EMP_END,
ASSI.LEAVING_REASON,
ASSI.LEAVE_REASON_DESCRIP,
ASSI.LOCATION_DESCRIPTION,
ASSI.ORG_TYPE_DESC,
ASSI.OE_CODE,
ASSI.ORG_NAME,
ASSI.FACULTY,
ASSI.DIVISION,
ASSI.GRADE,
ASSI.GRADE_NAME,
ASSI.GRADE_CALC,
ASSI.POSITION_ID,
ASSI.POSITION,
ASSI.POSITION_NAME,
ASSI.JOB_NAME,
ASSI.JOB_SEGMENT_NAME,
ASSI.ACAD_SUPP,
ASSI.EMPLOYMENT_CATEGORY,
ASSI.LEAVE_CODE,
ASSI.SUPERVISOR_ID,
ASSI.ASS_WEEK_LEN,
ASSI.ASS_ATTRIBUTE2,
ASSI.PRIMARY_FLAG,
ASSI.MAILTO
FROM
X000_PER_ALL_ASSIGNMENTS ASSI
WHERE
(ASSI.EFFECTIVE_END_DATE >= Date('%FROM%') AND
ASSI.EFFECTIVE_END_DATE <= Date('%TO%')) OR
(ASSI.EFFECTIVE_START_DATE >= Date('%FROM%') AND
ASSI.EFFECTIVE_START_DATE <= Date('%TO%')) OR
(ASSI.EFFECTIVE_END_DATE >= Date('%FROM%') AND
ASSI.EFFECTIVE_START_DATE <= Date('%TO%'))
ORDER BY
ASSI.ASSIGNMENT_NUMBER,
ASSI.EFFECTIVE_START_DATE
"""
s_sql = s_sql.replace("%FROM%", s_from)
s_sql = s_sql.replace("%TO%", s_to)
so_curs.execute("DROP TABLE IF EXISTS " + s_table)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + s_table)
# Add column assignment lookup date
if "DATE_ASS_LOOKUP" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table +
" ADD COLUMN DATE_ASS_LOOKUP TEXT;")
s_sql = "UPDATE " + s_table + """
SET DATE_ASS_LOOKUP =
CASE
WHEN ASS_END > Date('%TO%') THEN Date('%TO%')
ELSE ASS_END
END
;"""
s_sql = s_sql.replace("%TO%", s_on)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: DATE_ASS_LOOKUP")
# Add column is assignment active
if "ASS_ACTIVE" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table +
" ADD COLUMN ASS_ACTIVE TEXT;")
s_sql = "UPDATE " + s_table + """
SET ASS_ACTIVE =
CASE
WHEN ORG_TYPE_DESC = 'Parent Organisation' THEN 'O'
WHEN EMP_START = EMP_END AND LEAVING_REASON = '' THEN 'Y'
WHEN EMP_END >= Date('%FR%') THEN 'Y'
WHEN INSTR(POSITION_NAME,'Pensioner') > 0 THEN 'P'
ELSE 'N'
END
;"""
s_sql = s_sql.replace("%FR%", s_from)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: ASS_ACTIVE")
# Add column people lookup
if "DATE_EMP_LOOKUP" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table +
" ADD COLUMN DATE_EMP_LOOKUP TEXT;")
s_sql = "UPDATE " + s_table + """
SET DATE_EMP_LOOKUP =
CASE
WHEN EMP_START = EMP_END AND LEAVING_REASON = '' THEN Date('%TO%')
WHEN EMP_END > Date('%TO%') THEN Date('%TO%')
ELSE EMP_END
END
;"""
s_sql = s_sql.replace("%TO%", s_on)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: DATE_EMP_LOOKUP")
# Add column is people active
if "EMP_ACTIVE" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table +
" ADD COLUMN EMP_ACTIVE TEXT;")
s_sql = "UPDATE " + s_table + """
SET EMP_ACTIVE =
CASE
WHEN ASS_ACTIVE = 'O' AND EMP_START <= Date('%ON%') AND DATE_EMP_LOOKUP >= Date('%ON%')
THEN 'O'
WHEN ASS_ACTIVE = 'P' AND EMP_START <= Date('%ON%') AND DATE_EMP_LOOKUP >= Date('%ON%')
THEN 'P'
WHEN ASS_ACTIVE = 'Y' AND EMP_START <= Date('%ON%') AND DATE_EMP_LOOKUP >= Date('%ON%')
THEN 'Y'
ELSE 'N'
END
;"""
s_sql = s_sql.replace("%ON%", s_on)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: EMP_ACTIVE")
return
import datetime
import sqlite3
import sys
# Add own module path
sys.path.append('S:/_my_modules')
# Import own modules
import funcdate
import funccsv
import funcfile
import funcpeople
# Open the script log file ******************************************************
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: REPORT_PEOPLE_SERVICE")
funcfile.writelog("-----------------------------")
print("PEOPLE SERVICE")
print("--------------")
ilog_severity = 1
# Declare variables
so_path = "W:/People/" #Source database path
so_file = "People.sqlite" #Source database
s_sql = "" #SQL statements
s_export = "True"
sd_acti = "Y"
# Open the SOURCE file
with sqlite3.connect(so_path+so_file) as so_conn:
def assign02(so_conn, s_table, s_source, s_mess):
"""
Function to build ASSIGNMENT for different date periods
:param so_conn: Connection string
:param s_table: Table name to create
:param s_source: Source table
:param s_mess: Print message
:return: Nothing
"""
# Print and connect
print(s_mess)
so_curs = so_conn.cursor()
# Build the table
s_sql = "CREATE TABLE " + s_table + " AS" + """
SELECT
X000_PER_ALL_PEOPLE.EMPLOYEE_NUMBER,
ASSI.ASS_ID,
ASSI.PERSON_ID,
ASSI.ASS_NUMB,
X000_PER_ALL_PEOPLE.FULL_NAME,
X000_PER_ALL_PEOPLE.KNOWN_NAME,
X000_PER_ALL_PEOPLE.DATE_OF_BIRTH,
ASSI.EMP_START,
ASSI.ASS_START,
ASSI.ASS_END,
ASSI.EMP_END,
ASSI.LEAVING_REASON,
ASSI.LEAVE_REASON_DESCRIP,
ASSI.LOCATION_DESCRIPTION,
ASSI.ORG_TYPE_DESC,
ASSI.OE_CODE,
ASSI.GRADE,
ASSI.GRADE_NAME,
ASSI.GRADE_CALC,
ASSI.POSITION_ID,
ASSI.POSITION,
ASSI.POSITION_NAME,
ASSI.ORG_NAME,
ASSI.JOB_NAME,
ASSI.JOB_SEGMENT_NAME,
ASSI.ACAD_SUPP,
ASSI.FACULTY,
ASSI.DIVISION,
ASSI.EMPLOYMENT_CATEGORY,
ASSI.LEAVE_CODE,
ASSI.SUPERVISOR_ID,
X000_PER_ALL_PEOPLE1.EMPLOYEE_NUMBER As SUPERVISOR,
ASSI.ASS_WEEK_LEN,
ASSI.ASS_ATTRIBUTE2,
X000_PER_ALL_PEOPLE.NATIONALITY,
X000_PER_ALL_PEOPLE.NATIONALITY_NAME,
X000_PER_ALL_PEOPLE.USER_PERSON_TYPE,
ASSI.PRIMARY_FLAG,
X000_PER_ALL_PEOPLE.CURRENT_EMPLOYEE_FLAG,
ASSI.DATE_ASS_LOOKUP,
ASSI.ASS_ACTIVE,
ASSI.DATE_EMP_LOOKUP,
ASSI.EMP_ACTIVE,
X000_COUNTS.COUNT_ASS,
X000_COUNTS.COUNT_PEO,
X000_COUNTS.COUNT_POS,
ASSI.MAILTO,
BANK.ACC_TYPE,
BANK.ACC_BRANCH,
BANK.ACC_NUMBER,
BANK.ACC_RELATION,
BANK.PPM_INFORMATION1 As ACC_SARS,
SEC.SEC_FULLPART_FLAG
FROM
%SOURCET% ASSI Left Join
X000_PER_ALL_PEOPLE ON X000_PER_ALL_PEOPLE.PERSON_ID = ASSI.PERSON_ID AND
X000_PER_ALL_PEOPLE.EFFECTIVE_START_DATE <= ASSI.DATE_ASS_LOOKUP AND
X000_PER_ALL_PEOPLE.EFFECTIVE_END_DATE >= ASSI.DATE_ASS_LOOKUP Left Join
X000_PER_ALL_PEOPLE X000_PER_ALL_PEOPLE1 ON X000_PER_ALL_PEOPLE1.PERSON_ID = ASSI.SUPERVISOR_ID AND
X000_PER_ALL_PEOPLE1.EFFECTIVE_START_DATE <= ASSI.DATE_ASS_LOOKUP AND
X000_PER_ALL_PEOPLE1.EFFECTIVE_END_DATE >= ASSI.DATE_ASS_LOOKUP Left Join
X000_COUNTS ON X000_COUNTS.PERSON_ID = ASSI.PERSON_ID Left Join
X000_PAY_ACCOUNTS BANK ON BANK.ASSIGNMENT_ID = ASSI.ASS_ID AND
BANK.ACC_TYPE <> 'BOND' AND
BANK.EFFECTIVE_START_DATE <= ASSI.DATE_ASS_LOOKUP AND
BANK.EFFECTIVE_END_DATE >= ASSI.DATE_ASS_LOOKUP Left Join
X001_ASSIGNMENT_SEC_CURR_YEAR SEC ON SEC.ASSIGNMENT_ID = ASSI.ASS_ID AND
SEC.SEC_DATE_FROM <= ASSI.DATE_ASS_LOOKUP AND
SEC.SEC_DATE_TO >= ASSI.DATE_ASS_LOOKUP
ORDER BY
X000_PER_ALL_PEOPLE.EMPLOYEE_NUMBER,
ASSI.EMP_START
"""
so_curs.execute("DROP TABLE IF EXISTS " + s_table)
s_sql = s_sql.replace("%SOURCET%", s_source)
so_curs.execute(s_sql)
so_conn.commit()
so_curs.execute("DROP TABLE IF EXISTS " + s_source)
funcfile.writelog("%t BUILD TABLE: " + s_table)
return
# Add own module path
sys.path.append('S:/_my_modules')
# Import own modules
import funcdate
import funccsv
import funcfile
import funcmail
import funcsys
import funcmysql
# Open the script log file ******************************************************
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: C200_REPORT_STUDDEB_RECON")
funcfile.writelog("---------------------------------")
print("-------------------------")
print("C200_REPORT_STUDDEB_RECON")
print("-------------------------")
ilog_severity = 1
# Declare variables
so_path = "W:/Kfs_vss_studdeb/" #Source database path
re_path = "R:/Debtorstud/" #Results
ed_path = "S:/_external_data/" #External data
so_file = "Kfs_vss_studdeb.sqlite" #Source database
s_sql = "" #SQL statements
l_mail = True
l_export = True
def people01(so_conn, s_table, s_source, s_peri, s_mess, s_acti):
"""
Function to build PEOPLE table from different assignments
:param so_conn: Connection string
:param s_table: Table name to create
:param s_source: Table source
:param s_peri: For which period
:param s_mess: Print and log message
:param s_acti: Should list include only active people = Y (or active assignments = N)
:return: Nothing
"""
# Print and connect
print(s_mess)
so_curs = so_conn.cursor()
# Use assignment or people date
if s_acti == "Y":
s_wher = "ASSI.EMP_ACTIVE = 'Y'"
else:
s_wher = "ASSI.ASS_ACTIVE = 'Y'"
# Create the people table
s_sql = "CREATE TABLE " + s_table + " As " + """
Select
ASSI.EMPLOYEE_NUMBER,
ASSI.ASS_ID,
ASSI.PERSON_ID,
ASSI.ASS_NUMB,
X000_PER_ALL_PEOPLE.PARTY_ID,
Upper(ASSI.FULL_NAME) As FULL_NAME,
'' As NAME_LIST,
'' As NAME_ADDR,
Upper(ASSI.KNOWN_NAME) As KNOWN_NAME,
CASE
WHEN ORG_NAME IS NULL THEN OE_CODE||': '||POSITION_NAME
ELSE ORG_NAME||': '||POSITION_NAME
END AS POSITION_FULL,
ASSI.DATE_OF_BIRTH,
Upper(X000_PER_ALL_PEOPLE.NATIONALITY) As NATIONALITY,
Upper(X000_PER_ALL_PEOPLE.NATIONALITY_NAME) As NATIONALITY_NAME,
X000_PER_ALL_PEOPLE.NATIONAL_IDENTIFIER As IDNO,
Upper(X000_PER_ALL_PEOPLE.PER_INFORMATION2) As PASSPORT,
Upper(X000_PER_ALL_PEOPLE.PER_INFORMATION3) As PERMIT,
X000_PER_ALL_PEOPLE.PER_INFORMATION8 As PERMIT_EXPIRE,
X000_PER_ALL_PEOPLE.TAX_NUMBER,
Case
When X000_PER_ALL_PEOPLE.SEX = 'F' Then 'FEMALE'
When X000_PER_ALL_PEOPLE.SEX = 'M' Then 'MALE'
Else 'OTHER'
End As SEX,
X000_PER_ALL_PEOPLE.MARITAL_STATUS,
X000_PER_ALL_PEOPLE.REGISTERED_DISABLED_FLAG As DISABLED,
X000_PER_ALL_PEOPLE.RACE_CODE,
Upper(X000_PER_ALL_PEOPLE.RACE_DESC) As RACE_DESC,
X000_PER_ALL_PEOPLE.LANG_CODE,
Upper(X000_PER_ALL_PEOPLE.LANG_DESC) As LANG_DESC,
X000_PER_ALL_PEOPLE.INT_MAIL,
Lower(X000_PER_ALL_PEOPLE.EMAIL_ADDRESS) As EMAIL_ADDRESS,
X000_PER_ALL_PEOPLE.CURRENT_EMPLOYEE_FLAG As CURR_EMPL_FLAG,
X000_PER_ALL_PEOPLE.USER_PERSON_TYPE,
ASSI.ASS_START,
ASSI.ASS_END,
ASSI.EMP_START,
ASSI.EMP_END,
ASSI.LEAVING_REASON,
Upper(ASSI.LEAVE_REASON_DESCRIP) As LEAVE_REASON_DESCRIP,
Upper(ASSI.LOCATION_DESCRIPTION) As LOCATION_DESCRIPTION,
Upper(ASSI.ORG_TYPE_DESC) As ORG_TYPE_DESC,
Upper(ASSI.OE_CODE) As OE_CODE,
Upper(ASSI.ORG_NAME) As ORG_NAME,
ASSI.PRIMARY_FLAG,
Upper(ASSI.ACAD_SUPP) As ACAD_SUPP,
Upper(ASSI.FACULTY) As FACULTY,
Upper(ASSI.DIVISION) As DIVISION,
Case
When EMPLOYMENT_CATEGORY = 'P' Then 'PERMANENT'
When EMPLOYMENT_CATEGORY = 'T' Then 'TEMPORARY'
Else 'OTHER'
End As EMPLOYMENT_CATEGORY,
ASSI.ASS_WEEK_LEN,
ASSI.LEAVE_CODE,
ASSI.GRADE,
Upper(ASSI.GRADE_NAME) As GRADE_NAME,
ASSI.GRADE_CALC,
ASSI.POSITION,
Upper(ASSI.POSITION_NAME) As POSITION_NAME,
Upper(ASSI.JOB_NAME) As JOB_NAME,
Upper(ASSI.JOB_SEGMENT_NAME) As JOB_SEGMENT_NAME,
ASSI.SUPERVISOR,
X000_PER_ALL_PEOPLE.TITLE_FULL,
X000_PER_ALL_PEOPLE.FIRST_NAME,
X000_PER_ALL_PEOPLE.MIDDLE_NAMES,
X000_PER_ALL_PEOPLE.LAST_NAME,
X000_PHONE_WORK_%PERIOD%_LIST.PHONE_WORK,
X000_PHONE_MOBI_%PERIOD%_LIST.PHONE_MOBI,
X000_PHONE_HOME_%PERIOD%_LIST.PHONE_HOME,
X000_ADDRESS_SARS.ADDRESS_SARS,
X000_ADDRESS_POST.ADDRESS_POST,
X000_ADDRESS_HOME.ADDRESS_HOME,
X000_ADDRESS_OTHE.ADDRESS_OTHE,
ASSI.COUNT_POS,
ASSI.COUNT_ASS,
ASSI.COUNT_PEO,
ASSI.DATE_ASS_LOOKUP,
ASSI.ASS_ACTIVE,
ASSI.DATE_EMP_LOOKUP,
ASSI.EMP_ACTIVE,
ASSI.MAILTO,
PER_PAY_PROPOSALS.PROPOSED_SALARY_N,
Upper(X000_PER_PEOPLE_TYPES.USER_PERSON_TYPE) As PERSON_TYPE,
Upper(ASSI.ACC_TYPE) As ACC_TYPE,
Upper(ASSI.ACC_BRANCH) As ACC_BRANCH,
ASSI.ACC_NUMBER,
Upper(ASSI.ACC_RELATION) As ACC_RELATION,
ASSI.ACC_SARS,
ASSI.SEC_FULLPART_FLAG
FROM
%SOURCET% ASSI
LEFT JOIN X000_PER_ALL_PEOPLE ON X000_PER_ALL_PEOPLE.PERSON_ID = ASSI.PERSON_ID AND
X000_PER_ALL_PEOPLE.EFFECTIVE_START_DATE <= ASSI.DATE_EMP_LOOKUP AND
X000_PER_ALL_PEOPLE.EFFECTIVE_END_DATE >= ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_PHONE_WORK_%PERIOD%_LIST ON X000_PHONE_WORK_%PERIOD%_LIST.PERSON_ID = ASSI.PERSON_ID
LEFT JOIN X000_PHONE_MOBI_%PERIOD%_LIST ON X000_PHONE_MOBI_%PERIOD%_LIST.PERSON_ID = ASSI.PERSON_ID
LEFT JOIN X000_PHONE_HOME_%PERIOD%_LIST ON X000_PHONE_HOME_%PERIOD%_LIST.PERSON_ID = ASSI.PERSON_ID
LEFT JOIN X000_ADDRESS_SARS ON X000_ADDRESS_SARS.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_SARS.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND X000_ADDRESS_SARS.DATE_TO >=
ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_ADDRESS_POST ON X000_ADDRESS_POST.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_POST.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND X000_ADDRESS_POST.DATE_TO >=
ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_ADDRESS_HOME ON X000_ADDRESS_HOME.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_HOME.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND X000_ADDRESS_HOME.DATE_TO >=
ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_ADDRESS_OTHE ON X000_ADDRESS_OTHE.PERSON_ID = ASSI.PERSON_ID AND
X000_ADDRESS_OTHE.DATE_FROM <= ASSI.DATE_EMP_LOOKUP AND
X000_ADDRESS_OTHE.DATE_TO >= ASSI.DATE_EMP_LOOKUP
LEFT JOIN PER_PAY_PROPOSALS ON PER_PAY_PROPOSALS.ASSIGNMENT_ID = ASSI.ASS_ID AND
PER_PAY_PROPOSALS.CHANGE_DATE <= ASSI.DATE_EMP_LOOKUP AND
PER_PAY_PROPOSALS.DATE_TO >= ASSI.DATE_EMP_LOOKUP
LEFT JOIN X000_PER_PEOPLE_TYPES ON X000_PER_PEOPLE_TYPES.PERSON_ID = ASSI.PERSON_ID AND
X000_PER_PEOPLE_TYPES.EFFECTIVE_START_DATE <= ASSI.DATE_EMP_LOOKUP AND
X000_PER_PEOPLE_TYPES.EFFECTIVE_END_DATE >= ASSI.DATE_EMP_LOOKUP
WHERE
""" + s_wher + """
GROUP BY
ASSI.EMPLOYEE_NUMBER
"""
so_curs.execute("DROP TABLE IF EXISTS " + s_table)
s_sql = s_sql.replace("%SOURCET%", s_source)
s_sql = s_sql.replace("%PERIOD%", s_peri)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: " + s_table)
# Add column initials
if "INITIALS" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table +
" ADD COLUMN INITIALS TEXT;")
s_sql = "UPDATE " + s_table + """
SET INITIALS =
CASE
WHEN INSTR(MIDDLE_NAMES,' ') > 1
THEN SUBSTR(FIRST_NAME,1,1) || SUBSTR(MIDDLE_NAMES,1,1) || TRIM(SUBSTR(MIDDLE_NAMES,INSTR(MIDDLE_NAMES,' '),2))
WHEN LENGTH(MIDDLE_NAMES) > 0 THEN
SUBSTR(FIRST_NAME,1,1) || SUBSTR(MIDDLE_NAMES,1,1)
ELSE SUBSTR(FIRST_NAME,1,1)
END
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: INITIALS")
so_curs.execute(
"UPDATE " + s_table +
" SET NAME_LIST = LAST_NAME||' '||TITLE_FULL||' '||INITIALS;")
so_conn.commit()
so_curs.execute(
"UPDATE " + s_table +
" SET NAME_ADDR = TITLE_FULL||' '||INITIALS||' '||LAST_NAME;")
so_conn.commit()
# Add column age
if "AGE" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table + " ADD COLUMN AGE INT;")
s_sql = "UPDATE " + s_table + """
SET AGE = cast( (strftime('%Y', 'now') - strftime('%Y', DATE_OF_BIRTH)) - (strftime('%m-%d', 'now') < strftime('%m-%d', DATE_OF_BIRTH)) As int)
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: AGE")
# Add column month
if "MONTH" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table + " ADD COLUMN MONTH INT;")
s_sql = "UPDATE " + s_table + """
SET MONTH = cast(strftime('%m', DATE_OF_BIRTH) As int)
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: MONTH")
# Add column day
if "DAY" not in funccsv.get_colnames_sqlite(so_curs, s_table):
so_curs.execute("ALTER TABLE " + s_table + " ADD COLUMN DAY INT;")
s_sql = "UPDATE " + s_table + """
SET DAY = cast(strftime('%d', DATE_OF_BIRTH) As int)
;"""
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t ADD COLUMN: DAY")
return
def Studentlist(so_conn,
re_path,
s_period='curr',
s_year='2019',
l_export=False):
# DECLARE VARIABLES
if s_period == 'prev':
s_year = funcdate.prev_year()
elif s_period == 'curr':
s_year = funcdate.cur_year()
so_curs = so_conn.cursor()
"""*************************************************************************
BUILD STUDENTS
*************************************************************************"""
print("BUILD " + s_year + " STUDENTS")
funcfile.writelog("BUILD " + s_year + " YEAR STUDENTS")
# BUILD STUDENT QUALIFICATION RESULTS
print("Build student qualification results...")
sr_file = "X001_Student_qual_result"
s_sql = "CREATE VIEW " + sr_file + " AS " + """
SELECT
RESULT.KBUSINESSENTITYID,
RESULT.KACADEMICPROGRAMID,
RESULT.KQUALFOSRESULTCODEID,
RESULT.RESULT,
RESULT.KRESULTYYYYMM,
RESULT.KSTUDQUALFOSRESULTID,
RESULT.FGRADUATIONCEREMONYID,
RESULT.FPOSTPONEMENTCODEID,
RESULT.POSTPONE_REAS,
RESULT.RESULTISSUEDATE,
RESULT.DISCONTINUEDATE,
RESULT.FDISCONTINUECODEID,
RESULT.DISCONTINUE_REAS,
RESULT.RESULTPASSDATE,
RESULT.FLANGUAGECODEID,
RESULT.ISSUESURNAME,
RESULT.CERTIFICATESEQNUMBER,
RESULT.AVGMARKACHIEVED,
RESULT.PROCESSSEQNUMBER,
RESULT.FRECEIPTID,
RESULT.FRECEIPTLINEID,
RESULT.ISINABSENTIA,
RESULT.FPROGRAMAPID,
RESULT.FISSUETYPECODEID,
RESULT.ISSUE_TYPE,
RESULT.DATEPRINTED,
RESULT.LOCKSTAMP,
RESULT.AUDITDATETIME,
RESULT.FAUDITSYSTEMFUNCTIONID,
RESULT.FAUDITUSERCODE,
RESULT.FAPPROVEDBYCODEID,
RESULT.FAPPROVEDBYUSERCODE,
RESULT.DATERESULTAPPROVED,
RESULT.FENROLMENTPRESENTATIONID,
RESULT.CERTDISPATCHDATE,
RESULT.CERTDISPATCHREFNO,
RESULT.ISSUEFIRSTNAMES
FROM
X000_Student_qual_result RESULT
WHERE
RESULT.KRESULTYYYYMM >= SubStr('%YEARB%',1,4)||SubStr('%YEARB%',6,2) AND
RESULT.KRESULTYYYYMM <= SubStr('%YEARE%',1,4)||SubStr('%YEARE%',6,2)
ORDER BY
RESULT.KBUSINESSENTITYID
"""
if s_period == 'prev':
s_sql = s_sql.replace("%YEARB%", funcdate.prev_yearbegin())
s_sql = s_sql.replace("%YEARE%", funcdate.prev_yearend())
elif s_period == 'peri':
s_sql = s_sql.replace("%YEARB%", s_year + "-01-01")
s_sql = s_sql.replace("%YEARE%", s_year + "-12-31")
else:
s_sql = s_sql.replace("%YEARB%", funcdate.cur_yearbegin())
s_sql = s_sql.replace("%YEARE%", funcdate.cur_yearend())
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: X001_Student_qual_result")
# BUILD STUDENT QUALIFICATION ONE
# QUALLEVELENROLSTUD + PRESENTOUENROLPRESENTCAT
print("Build student qualification step 1...")
s_sql = "CREATE VIEW X001aa_Stud_qual_enrol AS " + """
Select
QUAL.KSTUDBUSENTID,
QUAL.KENROLSTUDID,
QUAL.DATEQUALLEVELSTARTED,
QUAL.DATEENROL,
QUAL.STARTDATE,
QUAL.ENDDATE,
QUAL.ISHEMISSUBSIDY,
QUAL.ISMAINQUALLEVEL,
QUAL.ENROLACADEMICYEAR,
QUAL.ENROLHISTORYYEAR,
QUAL.FSTUDACTIVECODEID,
ACTIVE.LONG AS ACTIVE_IND,
QUAL.FENTRYLEVELCODEID,
ENTRY.LONG AS ENTRY_LEVEL,
ENROL.FENROLMENTCATEGORYCODEID,
ENROL.LONG AS ENROL_CAT,
ENROL.FPRESENTATIONCATEGORYCODEID,
PRESENT.LONG AS PRESENT_CAT,
QUAL.FBLACKLISTCODEID,
BLACK.LONG AS BLACKLIST,
QUAL.ISCONDITIONALREG,
QUAL.MARKSFINALISEDDATE,
ENROL.EXAMSUBMINIMUM,
QUAL.ISCUMLAUDE,
QUAL.FGRADCERTLANGUAGECODEID,
QUAL.ISPOSSIBLEGRADUATE,
QUAL.FGRADUATIONCEREMONYID,
QUAL.FACCEPTANCETESTCODEID,
QUAL.FENROLMENTPRESENTATIONID,
QUAL.FPROGRAMAPID,
ENROL.FQUALPRESENTINGOUID
From
QUALLEVELENROLSTUD_%PERIOD% QUAL Left Join
PRESENTOUENROLPRESENTCAT ENROL ON ENROL.KENROLMENTPRESENTATIONID = QUAL.FENROLMENTPRESENTATIONID Left Join
X000_Codedescription BLACK ON BLACK.KCODEDESCID = QUAL.FBLACKLISTCODEID Left Join
X000_Codedescription ACTIVE ON ACTIVE.KCODEDESCID = QUAL.FSTUDACTIVECODEID Left Join
X000_Codedescription ENTRY ON ENTRY.KCODEDESCID = QUAL.FENTRYLEVELCODEID Left Join
X000_Codedescription ENROL ON ENROL.KCODEDESCID = ENROL.FENROLMENTCATEGORYCODEID Inner Join
X000_Codedescription PRESENT ON PRESENT.KCODEDESCID = ENROL.FPRESENTATIONCATEGORYCODEID
Order By
QUAL.KSTUDBUSENTID,
QUAL.DATEQUALLEVELSTARTED
"""
s_sql = s_sql.replace("%PERIOD%", s_period)
so_curs.execute("DROP VIEW IF EXISTS X001aa_Stud_qual_enrol")
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: X001aa_Stud_qual_enrol")
# BUILD STUDENT QUALIFICATION TWO
# QUALLEVELENROLSTUD + PRESENTOUENROLPRESENTCAT
# QUALIFICATIONLEVEL
print("Build student qualification step 2...")
s_sql = "CREATE VIEW X001ab_Stud_qual_present AS " + """
Select
QUAL.KSTUDBUSENTID,
QUAL.KENROLSTUDID,
QUAL.DATEQUALLEVELSTARTED,
QUAL.DATEENROL,
QUAL.STARTDATE,
QUAL.ENDDATE,
QUALLEVE.QUALIFICATIONLEVEL,
QUAL.ISHEMISSUBSIDY,
QUAL.ISMAINQUALLEVEL,
QUAL.ENROLACADEMICYEAR,
QUAL.ENROLHISTORYYEAR,
QUAL.FSTUDACTIVECODEID,
QUAL.ACTIVE_IND,
QUAL.FENTRYLEVELCODEID,
QUAL.ENTRY_LEVEL,
QUAL.FENROLMENTCATEGORYCODEID,
QUAL.ENROL_CAT,
QUAL.FPRESENTATIONCATEGORYCODEID,
QUAL.PRESENT_CAT,
QUALLEVE.FFINALSTATUSCODEID,
QUALLEVE.STATUS_FINAL AS QUAL_LEVEL_STATUS_FINAL,
QUALLEVE.FLEVYCATEGORYCODEID,
QUALLEVE.LEVY_CATEGORY AS QUAL_LEVEL_LEVY_CAT,
QUAL.FBLACKLISTCODEID,
QUAL.BLACKLIST,
QUALPRES.FBUSINESSENTITYID,
ORG.FORGUNITNUMBER,
ORG.ORGUNIT_TYPE,
ORG.ORGUNIT_NAME,
ORG.FSITEORGUNITNUMBER,
QUAL.ISCONDITIONALREG,
QUAL.MARKSFINALISEDDATE,
QUAL.EXAMSUBMINIMUM,
QUAL.ISCUMLAUDE,
QUAL.FGRADCERTLANGUAGECODEID,
QUAL.ISPOSSIBLEGRADUATE,
QUAL.FGRADUATIONCEREMONYID,
QUAL.FACCEPTANCETESTCODEID,
QUAL.FENROLMENTPRESENTATIONID,
QUAL.FQUALPRESENTINGOUID,
QUAL.FPROGRAMAPID,
QUALPRES.FQUALLEVELAPID,
QUALLEVE.FFIELDOFSTUDYAPID,
QUALLEVE.STARTDATE AS QUAL_LEVEL_STARTDATE,
QUALLEVE.ENDDATE AS QUAL_LEVEL_ENDDATE
From
X001aa_Stud_qual_enrol QUAL Left Join
QUALLEVELPRESENTINGOU QUALPRES ON QUALPRES.KPRESENTINGOUID = QUAL.FQUALPRESENTINGOUID Left Join
X000_Qualification_level QUALLEVE ON QUALLEVE.KACADEMICPROGRAMID = QUALPRES.FQUALLEVELAPID Left Join
X000_Orgunitinstance ORG ON ORG.KBUSINESSENTITYID = QUALPRES.FBUSINESSENTITYID
"""
so_curs.execute("DROP VIEW IF EXISTS X001ab_Stud_qual_present")
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: X001ab_Stud_qual_present")
# BUILD STUDENT QUALIFICATION THREE
# QUALLEVELENROLSTUD + PRESENTOUENROLPRESENTCAT
# QUALIFICATIONLEVEL
print("Build student qualification step 3...")
s_sql = "CREATE VIEW X001ac_Stud_qual_fos AS " + """
SELECT
QUAL.KSTUDBUSENTID,
QUAL.KENROLSTUDID,
QUAL.DATEQUALLEVELSTARTED,
QUAL.DATEENROL,
QUAL.STARTDATE,
QUAL.ENDDATE,
PROG.QUALIFICATIONCODE,
FOS.QUALIFICATIONFIELDOFSTUDY,
QUAL.QUALIFICATIONLEVEL,
PROG.QUAL_TYPE,
QUAL.ISHEMISSUBSIDY,
QUAL.ISMAINQUALLEVEL,
QUAL.ENROLACADEMICYEAR,
QUAL.ENROLHISTORYYEAR,
PROG.MIN,
PROG.MIN_UNIT,
PROG.MAX,
PROG.MAX_UNIT,
QUAL.FSTUDACTIVECODEID,
QUAL.ACTIVE_IND,
QUAL.FENTRYLEVELCODEID,
QUAL.ENTRY_LEVEL,
QUAL.FENROLMENTCATEGORYCODEID,
QUAL.ENROL_CAT,
QUAL.FPRESENTATIONCATEGORYCODEID,
QUAL.PRESENT_CAT,
QUAL.FFINALSTATUSCODEID,
QUAL.QUAL_LEVEL_STATUS_FINAL,
QUAL.FLEVYCATEGORYCODEID,
QUAL.QUAL_LEVEL_LEVY_CAT,
PROG.CERT_TYPE,
PROG.LEVY_TYPE,
QUAL.FBLACKLISTCODEID,
QUAL.BLACKLIST,
FOS.FSELECTIONCODEID,
SEL.LONG AS FOS_SELECTION,
QUAL.FBUSINESSENTITYID,
QUAL.FORGUNITNUMBER,
QUAL.ORGUNIT_TYPE,
QUAL.ORGUNIT_NAME,
QUAL.FSITEORGUNITNUMBER,
QUAL.ISCONDITIONALREG,
QUAL.MARKSFINALISEDDATE,
QUAL.EXAMSUBMINIMUM,
QUAL.ISCUMLAUDE,
QUAL.FGRADCERTLANGUAGECODEID,
QUAL.ISPOSSIBLEGRADUATE,
QUAL.FGRADUATIONCEREMONYID,
QUAL.FACCEPTANCETESTCODEID,
QUAL.FENROLMENTPRESENTATIONID,
QUAL.FQUALPRESENTINGOUID,
QUAL.FQUALLEVELAPID,
QUAL.FFIELDOFSTUDYAPID,
QUAL.FPROGRAMAPID,
FOS.FQUALIFICATIONAPID
FROM
X001ab_Stud_qual_present QUAL Left Join
FIELDOFSTUDY FOS ON FOS.KACADEMICPROGRAMID = QUAL.FFIELDOFSTUDYAPID Left Join
X000_Codedescription SEL ON SEL.KCODEDESCID = FOS.FSELECTIONCODEID Left Join
X000_Qualification PROG ON PROG.KACADEMICPROGRAMID = FOS.FQUALIFICATIONAPID
"""
so_curs.execute("DROP VIEW IF EXISTS X001ac_Stud_qual_fos")
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: X001ac_Stud_qual_fos")
# BUILD STUDENT QUALIFICATION RESULTS FOUR
print("Build student qualification step 4...")
sr_file = "X001ad_Stud_qual_result"
s_sql = "CREATE VIEW " + sr_file + " AS " + """
SELECT
QUAL.KSTUDBUSENTID,
QUAL.KENROLSTUDID,
QUAL.DATEQUALLEVELSTARTED,
QUAL.DATEENROL,
QUAL.STARTDATE,
QUAL.ENDDATE,
QUAL.QUALIFICATIONCODE,
QUAL.QUALIFICATIONFIELDOFSTUDY,
QUAL.QUALIFICATIONLEVEL,
QUAL.QUAL_TYPE,
QUAL.ISHEMISSUBSIDY,
QUAL.ISMAINQUALLEVEL,
QUAL.ENROLACADEMICYEAR,
QUAL.ENROLHISTORYYEAR,
QUAL.MIN,
QUAL.MIN_UNIT,
QUAL.MAX,
QUAL.MAX_UNIT,
QUAL.FSTUDACTIVECODEID,
QUAL.ACTIVE_IND,
QUAL.FENTRYLEVELCODEID,
QUAL.ENTRY_LEVEL,
QUAL.FENROLMENTCATEGORYCODEID,
QUAL.ENROL_CAT,
QUAL.FPRESENTATIONCATEGORYCODEID,
QUAL.PRESENT_CAT,
QUAL.FFINALSTATUSCODEID,
QUAL.QUAL_LEVEL_STATUS_FINAL,
QUAL.FLEVYCATEGORYCODEID,
QUAL.QUAL_LEVEL_LEVY_CAT,
QUAL.CERT_TYPE,
QUAL.LEVY_TYPE,
QUAL.FBLACKLISTCODEID,
QUAL.BLACKLIST,
QUAL.FSELECTIONCODEID,
QUAL.FOS_SELECTION,
QUAL.FBUSINESSENTITYID,
QUAL.FORGUNITNUMBER,
QUAL.ORGUNIT_TYPE,
QUAL.ORGUNIT_NAME,
QUAL.FSITEORGUNITNUMBER,
QUAL.ISCONDITIONALREG,
QUAL.MARKSFINALISEDDATE,
QUAL.EXAMSUBMINIMUM,
QUAL.ISCUMLAUDE,
QUAL.FGRADCERTLANGUAGECODEID,
QUAL.ISPOSSIBLEGRADUATE,
QUAL.FGRADUATIONCEREMONYID,
QUAL.FACCEPTANCETESTCODEID,
QUAL.FENROLMENTPRESENTATIONID,
QUAL.FQUALPRESENTINGOUID,
QUAL.FQUALLEVELAPID,
QUAL.FFIELDOFSTUDYAPID,
QUAL.FPROGRAMAPID,
QUAL.FQUALIFICATIONAPID,
RESULT.KBUSINESSENTITYID,
RESULT.KACADEMICPROGRAMID,
RESULT.KQUALFOSRESULTCODEID,
RESULT.RESULT,
RESULT.KRESULTYYYYMM,
RESULT.KSTUDQUALFOSRESULTID,
RESULT.FGRADUATIONCEREMONYID AS FGRADUATIONCEREMONYID1,
RESULT.FPOSTPONEMENTCODEID,
RESULT.POSTPONE_REAS,
RESULT.RESULTISSUEDATE,
RESULT.DISCONTINUEDATE,
RESULT.FDISCONTINUECODEID,
RESULT.DISCONTINUE_REAS,
RESULT.RESULTPASSDATE,
RESULT.FLANGUAGECODEID,
RESULT.ISSUESURNAME,
RESULT.CERTIFICATESEQNUMBER,
RESULT.AVGMARKACHIEVED,
RESULT.PROCESSSEQNUMBER,
RESULT.FRECEIPTID,
RESULT.FRECEIPTLINEID,
RESULT.ISINABSENTIA,
RESULT.FPROGRAMAPID AS FPROGRAMAPID1,
RESULT.FISSUETYPECODEID,
RESULT.ISSUE_TYPE,
RESULT.DATEPRINTED,
RESULT.LOCKSTAMP,
RESULT.AUDITDATETIME,
RESULT.FAUDITSYSTEMFUNCTIONID,
RESULT.FAUDITUSERCODE,
RESULT.FAPPROVEDBYCODEID,
RESULT.FAPPROVEDBYUSERCODE,
RESULT.DATERESULTAPPROVED,
RESULT.FENROLMENTPRESENTATIONID AS FENROLMENTPRESENTATIONID1,
RESULT.CERTDISPATCHDATE,
RESULT.CERTDISPATCHREFNO,
RESULT.ISSUEFIRSTNAMES
FROM
X001ac_Stud_qual_fos QUAL Left Join
X001_Student_qual_result RESULT ON RESULT.KBUSINESSENTITYID = QUAL.KSTUDBUSENTID AND
RESULT.FPROGRAMAPID = QUAL.FPROGRAMAPID AND
RESULT.KACADEMICPROGRAMID = QUAL.FFIELDOFSTUDYAPID
"""
so_curs.execute("DROP VIEW IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD VIEW: X001ad_Stud_qual_result")
# BUILD STUDENT QUALIFICATION FINAL LIST
print("Build student qualification step 5...")
sr_file = "X001_Student_" + s_period
s_sql = "CREATE TABLE " + sr_file + " AS " + """
SELECT
QUAL.KSTUDBUSENTID,
QUAL.KENROLSTUDID,
QUAL.FORGUNITNUMBER,
QUAL.DATEQUALLEVELSTARTED,
QUAL.DATEENROL,
QUAL.STARTDATE,
QUAL.ENDDATE,
QUAL.DISCONTINUEDATE,
QUAL.RESULT,
QUAL.QUALIFICATIONCODE,
QUAL.QUALIFICATIONFIELDOFSTUDY,
QUAL.QUALIFICATIONLEVEL,
QUAL.QUAL_TYPE,
QUAL.ISHEMISSUBSIDY,
QUAL.ISMAINQUALLEVEL,
QUAL.ENROLACADEMICYEAR,
QUAL.ENROLHISTORYYEAR,
QUAL.MIN,
QUAL.MIN_UNIT,
QUAL.MAX,
QUAL.MAX_UNIT,
QUAL.FSTUDACTIVECODEID,
QUAL.ACTIVE_IND,
QUAL.FENTRYLEVELCODEID,
QUAL.ENTRY_LEVEL,
QUAL.FENROLMENTCATEGORYCODEID,
QUAL.ENROL_CAT,
QUAL.FPRESENTATIONCATEGORYCODEID,
QUAL.PRESENT_CAT,
QUAL.FFINALSTATUSCODEID,
QUAL.QUAL_LEVEL_STATUS_FINAL,
QUAL.FLEVYCATEGORYCODEID,
QUAL.QUAL_LEVEL_LEVY_CAT,
QUAL.CERT_TYPE,
QUAL.LEVY_TYPE,
QUAL.FBLACKLISTCODEID,
QUAL.BLACKLIST,
QUAL.FSELECTIONCODEID,
QUAL.FOS_SELECTION,
QUAL.DISCONTINUE_REAS,
QUAL.POSTPONE_REAS,
QUAL.FBUSINESSENTITYID,
QUAL.ORGUNIT_TYPE,
QUAL.ORGUNIT_NAME,
QUAL.FSITEORGUNITNUMBER,
QUAL.ISCONDITIONALREG,
QUAL.MARKSFINALISEDDATE,
QUAL.RESULTPASSDATE,
QUAL.RESULTISSUEDATE,
QUAL.EXAMSUBMINIMUM,
QUAL.ISCUMLAUDE,
QUAL.FGRADCERTLANGUAGECODEID,
QUAL.ISPOSSIBLEGRADUATE,
QUAL.FGRADUATIONCEREMONYID,
QUAL.FACCEPTANCETESTCODEID,
QUAL.FENROLMENTPRESENTATIONID,
QUAL.FQUALPRESENTINGOUID,
QUAL.FQUALLEVELAPID,
QUAL.FFIELDOFSTUDYAPID,
QUAL.FPROGRAMAPID,
QUAL.FQUALIFICATIONAPID,
PROGRAM.FFIELDOFSTUDYAPID AS FFIELDOFSTUDYAPID1,
PROGRAM.PROGRAMCODE
FROM
X001ad_Stud_qual_result QUAL Left Join
PROGRAM ON PROGRAM.KACADEMICPROGRAMID = QUAL.FPROGRAMAPID
ORDER BY
QUAL.KSTUDBUSENTID,
QUAL.DATEENROL
"""
so_curs.execute("DROP TABLE IF EXISTS " + sr_file)
so_curs.execute(s_sql)
so_conn.commit()
funcfile.writelog("%t BUILD TABLE: X001cx_Stud_qual")
# Export the data
if l_export == True:
print("Export students all...")
sr_filet = sr_file
sx_path = re_path + s_year + "/"
sx_file = "Student_001_all_"
#sx_filet = sx_file + funcdate.today()
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)
return
Script to RUN CREATE Joomla Fabrik form group record
Copyright (C) AB Janse van Rensburg 20190311
*****************************************************************************"""
# IMPORT SYSTEM OBJECTS
import sys
# ADD OWN MODULE PATH
sys.path.append('S:/_my_modules')
# IMPORT FUNCTIONS
import funcfile
import funcsys
# OPEN THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: FUNC_FABRIK_03_FORMGROUPCREATE_RUN")
funcfile.writelog("------------------------------------------")
# CALL THE FUNCTION
import Func_fabrik_03_formgroup
try:
i_gr = Func_fabrik_03_formgroup.Fabrik_formgroup()
print(i_gr)
except Exception as e:
funcsys.ErrMessage(e)
# CLOSE THE LOG WRITER
funcfile.writelog("Now")
funcfile.writelog("COMPLETED: FUNC_FABRIK_01_FORMGROUPCREATE_RUN")
funcfile.writelog("---------------------------------------------")
s_name = input("Fabrik element FIELD name? ")
# Input the joomla mysql fabrik element LABEL name
print("")
s_label = ""
while s_label == "":
s_label = input("Fabrik element LABEL name? ")
# Input the joomla mysql fabrik element ORDER number
print("")
s_auto = ""
while s_auto == "":
s_auto = input("Fabrik element ORDERING number? ")
# Script log file
funcfile.writelog("Now")
funcfile.writelog("SCRIPT: FABRIK_INSERT_05_ELEMENT_RADIOBUTTON")
funcfile.writelog("--------------------------------------------")
# Connect to the oracle database
cnxn = funcmysql.mysql_open(s_database)
curs = cnxn.cursor()
funcfile.writelog("%t OPEN DATABASE: " + s_database)
# Add default FINDING RATE data 1
s_sql = "INSERT INTO " + s_table + "(" + """
name,
group_id,
plugin,
label,
created,
def Fabrik_listcreate(s_input="F",
s_lb="New LIST to setup",
s_fo="0",
s_tt="0",
s_kf="0",
s_db="Web_ia_joomla",
s_tb="ianwu_fabrik_lists"):
""" PARAMETERS *************************************************************
s_input = F=do not ask parameters(default) T=ask parameters
s_lb = List label
s_db = Joomla database
s_tb = Joomla table name
*************************************************************************"""
""" INDEX ******************************************************************
ENVIRONMENT
INPUT
OPEN DATABASE
INSERT LIST RECORD
*************************************************************************"""
print("FABRIK CREATE LIST STEP 4")
print("--------------------------")
print("ENVIRONMENT")
# IMPORT SYSTEM OBJECTS
import sys
# OWN MODULE PATH
sys.path.append('S:/_my_modules')
# IMPORT PYTHON OBJECTS
import pyodbc
# IMPORT FUNCTIONS
import funcfile
import funcmysql
# DECLARE VARIABLES
s_sql = ""
s_created_by = "854"
"""*************************************************************************
INPUT
*************************************************************************"""
print("INPUT")
# INPUT THE JOOMLA MYSQL FABRIK DATABASE NAME
s_dbi = s_db
if s_input == "T":
print("")
print("Default:" + s_db)
s_dbi = input("Fabrik DATABASE name? ")
if s_dbi == "":
s_dbi = s_db
# INPUT THE JOOMLA MYSQL TABLE NAME
s_tbi = s_tb
if s_input == "T":
print("")
print("Default:" + s_tb)
s_tbi = input("Fabrik TABLE name? ")
if s_tbi == "":
s_tbi = s_tb
# INPUT THE JOOMLA MYSQL FABRIK LIST LABEL
s_lbi = s_lb
if s_input == "T":
print("")
print("Default:" + s_lb)
s_lbi = input("Fabrik LIST label? ")
if s_lbi == "":
s_lbi = s_lb
print("")
# INPUT THE JOOMLA MYSQL FABRIK FORM NUMBER
s_foi = s_fo
if s_input == "T" or s_foi == "0":
print("")
print("Default:" + s_fo)
while s_foi == "" or s_foi == "0":
s_foi = input("Fabrik FORM number? ")
print("")
# INPUT THE JOOMLA MYSQL FABRIK LIST TARGET TABLE NAME
s_tti = s_tt
if s_input == "T" or s_tti == "0":
print("")
print("Default:" + s_tt)
while s_tti == "" or s_tti == "0":
s_tti = input("Fabrik LIST target table name? ")
print("")
# INPUT THE JOOMLA MYSQL FABRIK LIST KEY FIELD
s_kfi = s_kf
if s_input == "T" or s_kfi == "0":
print("")
print("Default:" + s_kf)
while s_kfi == "" or s_kfi == "0":
s_kfi = input("Fabrik LIST key field? ")
print("")
"""*************************************************************************
OPEN DATABASE
*************************************************************************"""
print("OPEN DATABASE")
funcfile.writelog("OPEN DATABASE")
# Connect to the oracle database
cnxn = funcmysql.mysql_open(s_dbi)
curs = cnxn.cursor()
funcfile.writelog("%t OPEN DATABASE: " + s_dbi)
"""*************************************************************************
INSERT LIST RECORD
*************************************************************************"""
print("INSERT LIST RECORD")
funcfile.writelog("INSERT LIST RECORD")
# INSERT LIST RECORD
s_sql = "INSERT INTO `" + s_tbi + "` (" + """
`label`,
`introduction`,
`form_id`,
`db_table_name`,
`db_primary_key`,
`auto_inc`,
`connection_id`,
`created`,
`created_by`,
`created_by_alias`,
`modified`,
`modified_by`,
`checked_out`,
`checked_out_time`,
`published`,
`publish_up`,
`publish_down`,
`access`,
`hits`,
`rows_per_page`,
`template`,
`order_by`,
`order_dir`,
`filter_action`,
`group_by`,
`private`,
`params`
""" + ") VALUES (" + """
'%LABEL%',
'',
%FORM%,
'%TABLE_TARGET%',
'%TABLE_TARGET%.%KEY_FIELD%',
1,
2,
NOW(),
%CREATED_BY%,
'Python',
'0000-00-00 00:00:00',
0,
0,
'0000-00-00 00:00:00',
1,
'0000-00-00 00:00:00',
'0000-00-00 00:00:00',
1,
0,
10,
'bootstrap',
'[\"\"]',
'[\"ASC\"]',
'onchange',
'',
0,
'{
\"show-table-filters\":\"1\",
\"advanced-filter\":\"0\",
\"advanced-filter-default-statement\":\"=\",
\"search-mode\":\"0\",
\"search-mode-advanced\":\"0\",
\"search-mode-advanced-default\":\"all\",
\"search_elements\":\"\",
\"list_search_elements\":\"null\",
\"search-all-label\":\"All\",
\"require-filter\":\"0\",
\"filter-dropdown-method\":\"0\",
\"toggle_cols\":\"0\",
\"list_filter_cols\":\"1\",
\"empty_data_msg\":\"\",
\"outro\":\"\",
\"list_ajax\":\"0\",
\"show-table-add\":\"1\",
\"show-table-nav\":\"1\",
\"show_displaynum\":\"1\",
\"showall-records\":\"1\",
\"show-total\":\"1\",
\"sef-slug\":\"\",
\"show-table-picker\":\"1\",
\"admin_template\":\"\",
\"show-title\":\"1\",
\"pdf\":\"\",
\"pdf_template\":\"\",
\"pdf_orientation\":\"portrait\",
\"pdf_size\":\"a4\",
\"bootstrap_stripped_class\":\"1\",
\"bootstrap_bordered_class\":\"0\",
\"bootstrap_condensed_class\":\"1\",
\"bootstrap_hover_class\":\"1\",
\"responsive_elements\":\"\",
\"responsive_class\":\"\",
\"list_responsive_elements\":\"null\",
\"tabs_field\":\"\",
\"tabs_max\":\"10\",
\"tabs_all\":\"1\",
\"list_ajax_links\":\"0\",
\"actionMethod\":\"default\",
\"detailurl\":\"\",
\"detaillabel\":\"\",
\"list_detail_link_icon\":\"search\",
\"list_detail_link_target\":\"_self\",
\"editurl\":\"\",\"editlabel\":\"\",
\"list_edit_link_icon\":\"edit\",
\"checkboxLocation\":\"end\",
\"addurl\":\"\",
\"addlabel\":\"\",
\"list_add_icon\":\"plus\",
\"list_delete_icon\":\"delete\",
\"popup_width\":\"\",
\"popup_height\":\"\",
\"popup_offset_x\":\"\",
\"popup_offset_y\":\"\",
\"note\":\"\",
\"alter_existing_db_cols\":\"default\",
\"process-jplugins\":\"1\",
\"cloak_emails\":\"0\",
\"enable_single_sorting\":\"default\",
\"collation\":\"utf8_general_ci\",
\"force_collate\":\"\",
\"list_disable_caching\":\"0\",
\"distinct\":\"1\",
\"group_by_raw\":\"1\",
\"group_by_access\":\"1\",
\"group_by_order\":\"\",
\"group_by_template\":\"\",
\"group_by_order_dir\":\"ASC\",
\"group_by_start_collapsed\":\"0\",
\"group_by_collapse_others\":\"0\",
\"group_by_show_count\":\"1\",
\"menu_module_prefilters_override\":\"1\",
\"prefilter_query\":\"\",
\"join-display\":\"default\",
\"delete-joined-rows\":\"0\",
\"show_related_add\":\"0\",
\"show_related_info\":\"0\",
\"rss\":\"0\",
\"feed_title\":\"\",
\"feed_date\":\"\",
\"feed_image_src\":\"\",
\"rsslimit\":\"150\",
\"rsslimitmax\":\"2500\",
\"csv_import_frontend\":\"3\",
\"csv_export_frontend\":\"2\",
\"csvfullname\":\"0\",
\"csv_export_step\":\"100\",
\"newline_csv_export\":\"nl2br\",
\"csv_clean_html\":\"leave\",
\"csv_custom_qs\":\"\",
\"csv_frontend_selection\":\"0\",
\"incfilters\":\"0\",\"csv_format\":\"0\",
\"csv_which_elements\":\"selected\",
\"show_in_csv\":\"\",
\"csv_elements\":\"null\",
\"csv_include_data\":\"1\",
\"csv_include_raw_data\":\"1\",
\"csv_include_calculations\":\"0\",
\"csv_filename\":\"\",
\"csv_encoding\":\"\",
\"csv_double_quote\":\"1\",
\"csv_local_delimiter\":\"\",
\"csv_end_of_line\":\"n\",
\"open_archive_active\":\"0\",
\"open_archive_set_spec\":\"\",
\"open_archive_timestamp\":\"\",
\"open_archive_license\":\"http:\\/\\/creativecommons.org\\/licenses\\/by-nd\\/2.0\\/rdf\",
\"dublin_core_element\":\"\",
\"dublin_core_type\":\"dc:description.abstract\",
\"raw\":\"0\",
\"open_archive_elements\":\"null\",
\"search_use\":\"0\",
\"search_title\":\"\",
\"search_description\":\"\",
\"search_date\":\"\",
\"search_link_type\":\"details\",
\"dashboard\":\"0\",
\"dashboard_icon\":\"\",
\"allow_view_details\":\"1\",
\"allow_edit_details\":\"1\",
\"allow_edit_details2\":\"\",
\"allow_add\":\"1\",
\"allow_delete\":\"2\",
\"allow_delete2\":\"\",
\"allow_drop\":\"3\",
\"isview\":\"0\"
}'
""" + ");"
print(s_sql) # DEBUG
s_sql = s_sql.replace("%LABEL%", s_lbi)
s_sql = s_sql.replace("%CREATED_BY%", s_created_by)
s_sql = s_sql.replace("%FORM%", s_foi)
s_sql = s_sql.replace("%TABLE_TARGET%", s_tti)
s_sql = s_sql.replace("%KEY_FIELD%", s_kfi)
print(s_sql) # DEBUG
curs.execute(s_sql)
cnxn.commit()
funcfile.writelog("%t INSERT RECORD: " + s_dbi + "." + s_tbi + ": " +
s_lbi)
# LIST DEFAULT PARAMETERS
"""
(38,
'TEST List',
'',
38,
'ia_finding_6',
'ia_finding_6.ia_find_auto',
1,
2,
'2019-03-19 22:00:00',
0,
'',
'2019-03-20 05:14:28',
842,
0,
'0000-00-00 00:00:00',
1,
'0000-00-00 00:00:00',
'0000-00-00 00:00:00',
1,
0,
10,
'bootstrap',
'[\"\"]',
'[\"ASC\"]',
'onchange',
'',
0,
'{
\"show-table-filters\":\"1\",
\"advanced-filter\":\"0\",
\"advanced-filter-default-statement\":\"=\",
\"search-mode\":\"0\",
\"search-mode-advanced\":\"0\",
\"search-mode-advanced-default\":\"all\",
\"search_elements\":\"\",
\"list_search_elements\":\"null\",
\"search-all-label\":\"All\",
\"require-filter\":\"0\",
\"filter-dropdown-method\":\"0\",
\"toggle_cols\":\"0\",
\"list_filter_cols\":\"1\",
\"empty_data_msg\":\"\",
\"outro\":\"\",
\"list_ajax\":\"0\",
\"show-table-add\":\"1\",
\"show-table-nav\":\"1\",
\"show_displaynum\":\"1\",
\"showall-records\":\"0\",
\"show-total\":\"0\",
\"sef-slug\":\"\",
\"show-table-picker\":\"1\",
\"admin_template\":\"\",
\"show-title\":\"1\",
\"pdf\":\"\",
\"pdf_template\":\"\",
\"pdf_orientation\":\"portrait\",
\"pdf_size\":\"a4\",
\"bootstrap_stripped_class\":\"1\",
\"bootstrap_bordered_class\":\"0\",
\"bootstrap_condensed_class\":\"0\",
\"bootstrap_hover_class\":\"1\",
\"responsive_elements\":\"\",
\"responsive_class\":\"\",
\"list_responsive_elements\":\"null\",
\"tabs_field\":\"\",
\"tabs_max\":\"10\",
\"tabs_all\":\"1\",
\"list_ajax_links\":\"0\",
\"actionMethod\":\"default\",
\"detailurl\":\"\",
\"detaillabel\":\"\",
\"list_detail_link_icon\":\"search\",
\"list_detail_link_target\":\"_self\",
\"editurl\":\"\",\"editlabel\":\"\",
\"list_edit_link_icon\":\"edit\",
\"checkboxLocation\":\"end\",
\"addurl\":\"\",
\"addlabel\":\"\",
\"list_add_icon\":\"plus\",
\"list_delete_icon\":\"delete\",
\"popup_width\":\"\",
\"popup_height\":\"\",
\"popup_offset_x\":\"\",
\"popup_offset_y\":\"\",
\"note\":\"\",
\"alter_existing_db_cols\":\"default\",
\"process-jplugins\":\"1\",
\"cloak_emails\":\"0\",
\"enable_single_sorting\":\"default\",
\"collation\":\"utf8_general_ci\",
\"force_collate\":\"\",
\"list_disable_caching\":\"0\",
\"distinct\":\"1\",
\"group_by_raw\":\"1\",
\"group_by_access\":\"1\",
\"group_by_order\":\"\",
\"group_by_template\":\"\",
\"group_by_order_dir\":\"ASC\",
\"group_by_start_collapsed\":\"0\",
\"group_by_collapse_others\":\"0\",
\"group_by_show_count\":\"1\",
\"menu_module_prefilters_override\":\"1\",
\"prefilter_query\":\"\",
\"join-display\":\"default\",
\"delete-joined-rows\":\"0\",
\"show_related_add\":\"0\",
\"show_related_info\":\"0\",
\"rss\":\"0\",
\"feed_title\":\"\",
\"feed_date\":\"\",
\"feed_image_src\":\"\",
\"rsslimit\":\"150\",
\"rsslimitmax\":\"2500\",
\"csv_import_frontend\":\"3\",
\"csv_export_frontend\":\"2\",
\"csvfullname\":\"0\",
\"csv_export_step\":\"100\",
\"newline_csv_export\":\"nl2br\",
\"csv_clean_html\":\"leave\",
\"csv_custom_qs\":\"\",
\"csv_frontend_selection\":\"0\",
\"incfilters\":\"0\",\"csv_format\":\"0\",
\"csv_which_elements\":\"selected\",
\"show_in_csv\":\"\",
\"csv_elements\":\"null\",
\"csv_include_data\":\"1\",
\"csv_include_raw_data\":\"1\",
\"csv_include_calculations\":\"0\",
\"csv_filename\":\"\",
\"csv_encoding\":\"\",
\"csv_double_quote\":\"1\",
\"csv_local_delimiter\":\"\",
\"csv_end_of_line\":\"n\",
\"open_archive_active\":\"0\",
\"open_archive_set_spec\":\"\",
\"open_archive_timestamp\":\"\",
\"open_archive_license\":\"http:\\/\\/creativecommons.org\\/licenses\\/by-nd\\/2.0\\/rdf\",
\"dublin_core_element\":\"\",
\"dublin_core_type\":\"dc:description.abstract\",
\"raw\":\"0\",
\"open_archive_elements\":\"null\",
\"search_use\":\"0\",
\"search_title\":\"\",
\"search_description\":\"\",
\"search_date\":\"\",
\"search_link_type\":\"details\",
\"dashboard\":\"0\",
\"dashboard_icon\":\"\",
\"allow_view_details\":\"1\",
\"allow_edit_details\":\"1\",
\"allow_edit_details2\":\"\",
\"allow_add\":\"1\",
\"allow_delete\":\"2\",
\"allow_delete2\":\"\",
\"allow_drop\":\"3\",
\"isview\":\"0\"
}');
"""
# GET NEWLY CREATED LIST NUMBER
curs.execute("SELECT " + s_tbi + ".id, " + s_tbi + ".label FROM " + s_tbi +
" WHERE " + s_tbi + ".label = '" + s_lbi + "'")
for row in curs.fetchall():
print("Created list " + str(row[0]))
funcfile.writelog("%t LIST CREATED: " + str(row[0]))
i_return = row[0]
# RETURN NEWLY CREATED GROUP NUMBER
return i_return
