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")