def insert_client(column_values, row_values, address_id):
'''
Inserts data into the Client table. Takes in a dataframe read_excel
object with iloc[row], address id, and agency name.
'''
# get client id and check if it exists in the database, if not
# insert the client
client_id = row_values[2]
if (not (database_methods.check_id(client_id, 'client_data.db', "Client",
"Unique_ID_Value"))):
# get the values of columns 0-6
v1 = database_methods.fetch_values(0, 7, row_values, [])
# get the values of columns 16-17
val = database_methods.fetch_values(15, 17, row_values, v1)
# add address_id
val.append(address_id)
insert_helper.insert_row(val, "Client")
else:
#if client already exists in the database, update their data
lst = [
"Processing_Details", "Date_Of_Birth", "Phone", "Email",
"Email_Address", "Official_Language_Preference", "Consent_Future"
]
index_list = [(0, 1), (3, 7), (15, 17)]
(col, data) = database_methods.update_lists(lst, column_values,
row_values, index_list)
query = database_methods.create_update_query(col, data, "Client",
"Unique_ID_Value",
client_id)
database_methods.update_query(query, "client_data.db")
def insert_referral(column_values, row_values, id_value):
'''
Inserts data into the Referral table. Gets client unique identifier value
and a dataframe read_excel object with iloc[row]. Returns 1 if information
has been inserted and 0 otherwise.
'''
# check client has been inserted already
insert_client(column_values, row_values, id_value)
index = [(1, 2), (5, 8), (9, 11), (39, 41), (47, 48), (68, 69), (89, 92)]
if (not (database_methods.check_id(id_value, db, "Referral",
"Client_Unique_ID_Value"))):
val = [id_value]
val = database_methods.fetch_values_list(row_values, index, val)
insert_helper.insert_row(val, "Referral")
return 1
else:
# client has been inserted, update data
lst = [
"Update_Record_ID", "Service_Postal_Code", "Assessment_Start_Date",
"Service_Language", "Where_Service_Received", "Referred_By",
"Canadian_Citizen_Intention", "Support_Services_Required",
"Non_IRCC_Services", "Support_Services_Received",
"Settlement_Plan_Completed", "Assessment_End_Date",
"Reason_For_Update"
]
(col, data) = database_methods.update_lists(lst, column_values,
row_values, index)
query = database_methods.create_update_query(col, data, "Referral",
"Client_Unique_ID_Value",
id_value)
database_methods.update_query(query, db)
return 0
def insert_find_employment(row_values, id_value):
'''
Inserts data into the Find_Employment table.
'''
val = [id_value]
table = "Find_Employment"
prim = "Client_Unique_ID_Value"
lst = [
"Find_Employment_R", "Timeframe", "Min_One_Year_Exp",
"NOC_Level_Intention", "Obtain_Credentials_License"
]
# check if "Find employment" column is "Yes"
if (row_values[33] == "Yes"):
value = database_methods.check_id(id_value, db, table, prim)
if (value == 1):
(col,
data) = database_methods.update_lists(lst, None, row_values,
[(34, 39)])
query = database_methods.create_update_query(
col, data, table, prim, id_value)
database_methods.update_query(query, db)
else:
# get columns 34-38
val = database_methods.fetch_values(34, 39, row_values, val)
insert_helper.insert_row(val, table)
def insert_address(row_values, index):
address_id = database_methods.get_id("Address") + 1
val = [address_id]
val = database_methods.fetch_values_list(row_values, index, val)
# insert into table
insert_helper.insert_row(val, "Address")
return address_id
def insert_info_and_ori(row_values, service_id, target_id):
'''
Inserts a row in the Info_and_Orientation table.
'''
index = [(5, 8), (9, 16), (69, 70), (93, 94)]
val = [service_id, target_id]
val = database_methods.fetch_values_list(row_values, index, val)
insert_helper.insert_row(val, "Info_and_Orientation")
def insert_employment(row_values, service_id):
'''
Inserts a row in the Employment_Service table.
'''
index = [(5, 9), (10, 18), (66, 68)]
val = [service_id]
val = database_methods.fetch_values_list(row_values, index, val)
insert_helper.insert_row(val, "Employment_Service")
def insert_community_conn(row_values, service_id, target_id):
'''
Inserts a row in the Community_Connections table.
'''
val = [service_id, target_id]
index = [(5, 7), (8, 17), (32, 37), (65, 67)]
val = database_methods.fetch_values_list(row_values, index, val)
insert_helper.insert_row(val, "Community_Connections")
def insert_CLB_level(column_values, row_values, client_id, course_code):
# check if any CLB levels are filled in
i = 9
while (i < 13):
val = [client_id, course_code]
if (type(row_values[i]) == str):
val.append(column_values[i])
val.append(row_values[i])
insert_helper.insert_row(val, "Client_CLB_Level")
i += 1
def insert_client_service(service_id, client_id, month, year):
'''
Inserts a row in the Client_Attends_Service table.
'''
val = [service_id, client_id, month, year]
if (database_methods.check_id(client_id, 'client_data.db', "Client",
"Unique_ID_Value")
and database_methods.check_id(service_id, 'client_data.db',
"Service", "ID")):
insert_helper.insert_row(val, "Client_Attends_Service")
def insert_long_term(row_values, service_id):
'''
Inserts a row in the Long_Term_Intervention table.
'''
long_term_id = database_methods.get_id("Long_Term_Intervention") + 1
val = [long_term_id, service_id]
# check if long term intervention was recieved
if (type(row_values[18]) == str):
val = database_methods.fetch_values(18, 29, row_values, val)
insert_helper.insert_row(val, "Long_Term_Intervention")
def insert_target_group(row_values, start, end):
'''
Insert a row into the Target_Group table.
'''
# get id to insert
target_id = database_methods.get_id("Target_Group") + 1
val = [target_id]
val = database_methods.fetch_values(start, end, row_values, val)
insert_helper.insert_row(val, "Target_Group")
return target_id
def insert_service(row_values, skill_idx, service_type):
'''
Insert a row in the Service table.
'''
service_id = database_methods.get_id("Service") + 1
val = [service_id]
val = database_methods.fetch_values(skill_idx, skill_idx + 1, row_values,
val)
val.append(service_type)
insert_helper.insert_row(val, "Service")
return service_id
def insert_client_enrol(row_values, client_id, course_code):
key = (course_code, client_id)
# check if client id and course code exists in the database
if ((database_methods.check_id(client_id, 'client_data.db', "Client",
"Unique_ID_Value")) and
(database_methods.check_course(course_code, 'client_data.db')) and
(not(database_methods.check_id(key, 'client_data.db', "Client_Enrolment",
("(Course_Code, " +
"Client_Unique_ID_Value)"))))):
val = [course_code, client_id]
val.append(row_values[7])
insert_helper.insert_row(val, "Client_Enrolment")
def insert_3_value(column_values, row_values, table, item_id, start, end):
'''
For inserting tables with (id, column_name, value) rows.
'''
i = start
while (i < end):
val = [item_id]
if (type(row_values[i]) == str and row_values[i] != "N/A"):
val.append(column_values[i])
val.append(row_values[i])
insert_helper.insert_row(val, table)
i += 1
def new_child(row_values, id_value):
'''
Inserts child information in the Child table.
'''
# check child fields are not empty and insert
i = 1
j = 70
while (j < 79):
if (type(row_values[j]) == str or type(row_values[j + 1]) == str):
val = [id_value, i]
val = database_methods.fetch_values(j, j + 2, row_values, val)
insert_helper.insert_row(val, "Child")
i += 1
j += 2
def insert_short_term(row_values, service_id):
'''
Inserts a row in the Short_Term_Intervention table.
'''
i = 35
end = 44
while (i < end):
short_term_id = database_methods.get_id("Short_Term_Intervention") + 1
val = [short_term_id, service_id]
# check if short term intervention received column is not empty
if (type(row_values[i]) == str):
val.append(row_values[i])
val.append(row_values[i + 1])
insert_helper.insert_row(val, "Short_Term_Intervention")
i += 2
def insert_trans_int(row_values, id_value):
'''
Inserts information in the Translation_Interpretation table.
'''
table = "Translation_Interpretation"
prim = "(Client_Unique_ID_Value, Type)"
trans_id = ("(" + id_value + ", 'Translation')")
int_id = ("(" + id_value + ", 'Interpretation')")
val = [id_value]
lst = ["'Between'", "'And'"]
# check if "Translation" is Yes
if (row_values[82] == "Yes"):
value = database_methods.get_existing_id(db, table, "Type", prim,
trans_id)
if (value is not None):
(col,
data) = database_methods.update_lists(lst, None, row_values,
[(83, 85)])
query = database_methods.create_update_query(
col, data, table, prim, trans_id)
database_methods.update_query(query, db)
else:
val.append("Translation")
# get columns 83-84
val = database_methods.fetch_values(83, 85, row_values, val)
insert_helper.insert_row(val, table)
val = [id_value]
# check if "Interpretation" is Yes
if (row_values[85] == "Yes"):
value = database_methods.get_existing_id(db, table, "Type", prim,
int_id)
if (value is not None):
(col,
data) = database_methods.update_lists(lst, None, row_values,
[(86, 88)])
query = database_methods.create_update_query(
col, data, table, prim, int_id)
database_methods.update_query(query, db)
else:
val.append("Interpretation")
val = database_methods.fetch_values(86, 88, row_values, val)
insert_helper.insert_row(val, table)
def update_child(row_values, id_value, lst, table, prim, i, j, last):
while (j < last):
if (type(row_values[j]) == str or type(row_values[j + 1]) == str):
child_id = database_methods.get_existing_id(
db, table, table, prim, ("(" + id_value + "," + str(i) + ")"))
if (child_id is not None):
#update current child
(col,
data) = database_methods.update_lists(lst, None, row_values,
[(j, j + 2)])
query = database_methods.create_update_query(
col, data, table, prim,
("(" + id_value + "," + str(i) + ")"))
database_methods.update_query(query, db)
else:
#insert new child into the database
val = [id_value, i]
val = database_methods.fetch_values(j, j + 2, row_values, val)
insert_helper.insert_row(val, table)
i += 1
j += 2
def insert_improve_skills(column_names, row_values, id_value):
'''
Inserts data into the Improve_Skills table.
'''
val = [id_value]
table = "Improve_Skills"
prim = "(Client_Unique_ID_Value, Improve)"
lst = ["'Description'"]
if (type(row_values[29]) == str):
prim_val = "(" + id_value + ", '" + column_names[29] + "')"
value = database_methods.get_existing_id(db, table, "Improve", prim,
prim_val)
if (value is not None):
(col,
data) = database_methods.update_lists(lst, None, row_values,
[(29, 30)])
query = database_methods.create_update_query(
col, data, table, prim, prim_val)
database_methods.update_query(query, db)
else:
val.append(column_names[29])
val = database_methods.fetch_values(29, 30, row_values, val)
insert_helper.insert_row(val, table)
val = [id_value]
if (type(row_values[32]) == str):
prim_val = "(" + id_value + ", '" + column_names[32] + "')"
value = database_methods.get_existing_id(db, table, "Improve", prim,
prim_val)
if (value is not None):
(col,
data) = database_methods.update_lists(lst, None, row_values,
[(32, 33)])
query = database_methods.create_update_query(
col, data, table, prim, prim_val)
database_methods.update_query(query, db)
else:
val.append(column_names[32])
val = database_methods.fetch_values(32, 33, row_values, val)
insert_helper.insert_row(val, table)
def insert_client_exit(row_values, client_id, course_code):
index = [(6, 9), (13, 16)]
val = [client_id, course_code]
val = database_methods.fetch_values_list(row_values, index, val)
insert_helper.insert_row(val, "Client_Exit")
def insert_course(row_values, target_group_id):
index = [(2, 14), (17, 19), (25, 31), (45, 48)]
val = database_methods.fetch_values_list(row_values, index, [])
val.append(target_group_id)
insert_helper.insert_row(val, "LT_Course")
def insert_instructor(row_values, course_code, add_id):
index = [(48, 49), (57, 60)]
val = [course_code, add_id]
val = database_methods.fetch_values_list(row_values, index, val)
insert_helper.insert_row(val, "Instructor")