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_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_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_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 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_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 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 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