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_address(column_values, row_values): ''' Grabs a row and inserts the data for a row in the Address table. Takes in a dataframe read_excel object with iloc[row]. ''' # check if client exists in the database client_id = row_values[2] index_list = [(7, 15)] if (not (database_methods.check_id(client_id, 'client_data.db', "Client", "Unique_ID_Value"))): add_id = insert_general.insert_address(row_values, index_list) return add_id else: # if customer already exists, update their address with current data lst = [ "Street_Number", "Street_Name", "Street_Type", "Street_Direction", "Unit", "City", "Province", "Postal_Code" ] (col, data) = database_methods.update_lists(lst, column_values, row_values, index_list) add_id = database_methods.get_existing_id("client_data.db", "Client", "Address_ID", "Unique_ID_Value", client_id) query = database_methods.create_update_query(col, data, "Address", "ID", add_id) database_methods.update_query(query, "client_data.db")
def insert_client(column_values, row_values, id_value): ''' Check if client has a row in Client table (aka client has filled out the Client Profile file). If not, insert missing data into Client table. ''' # if client did not submit client profile form if (not (database_methods.check_id(id_value, db, "Client", "Unique_ID_Value"))): # extract the data and insert a row into Client table index = [(0, 1), (2, 5), (8, 9)] val = database_methods.fetch_values_list(row_values, index, []) # insert into Client table query = ( "INSERT INTO Client (Processing_Details, Unique_ID_Type, " + "Unique_ID_Value, Date_Of_Birth, Official_Language_Preference" + " VALUES (?, ?, ?, ?, ?, ?);") database_methods.execute_query(query, val, 'client_data.db') else: # if client is in the database, update data lst = [ "Processing_Details", "Date_Of_Birth", "Official_Language_Preference" ] index_list = [(0, 1), (4, 5), (8, 9)] (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", id_value) database_methods.update_query(query, 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_client_needs(column_values, row_values, id_value): ''' Inserts data into the Client_Needs table. Values are all Yes/No ''' index_list = [(11, 29), (30, 32), (33, 34), (41, 47), (48, 68)] table = "Client_Needs" prim = "(Client_Unique_ID_Value, Type)" lst = ["Value"] i = 0 while (i < len(index_list)): start = index_list[i][0] end = index_list[i][1] while (start < end): if (type(row_values[start] == str)): prim_val = "(" + id_value + ", '" + column_values[start] + "')" value = database_methods.get_existing_id( db, table, "Type", prim, prim_val) if (value is not None): (col, data) = database_methods.update_lists( lst, None, row_values, [(start, start + 1)]) query = database_methods.create_update_query( col, data, table, prim, prim_val) database_methods.update_query(query, db) else: insert_general.insert_3_value(column_values, row_values, table, id_value, start, start + 1) start += 1 i += 1 #if __name__ == "__main__": #df = pd.read_excel('file.xlsx') #column_values = df.iloc[1] #row_values = df.iloc[2]
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_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_client_profile(row_values, client_id, index_list): lst = [ "Processing_Details", "Date_Of_Birth", "Official_Language_Preference" ] if (database_methods.check_id(client_id, 'client_data.db', "Client", "Unique_ID_Value")): (col, data) = database_methods.update_lists(lst, None, row_values, index_list) query = database_methods.create_update_query(col, data, "Client", "Unique_ID_Value", client_id) database_methods.update_query(query, db)
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 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 update_skills(column_values, row_values, client_id, index_list): table = "Skills" prim = "(Client_Unique_ID_Value, Skill)" lst = ["Value"] i = 0 while (i < len(index_list)): start = index_list[i][0] end = index_list[i][1] while (start < end): if (type(row_values[start] == str)): prim_val = "(" + client_id + ", '" + column_values[start] + "')" value = database_methods.get_existing_id( db, table, "Skill", prim, prim_val) if (value is not None): (col, data) = database_methods.update_lists( lst, column_values, row_values, [(start, start + 1)]) query = database_methods.create_update_query( col, data, table, prim, prim_val) database_methods.update_query(query, db) else: insert_3_value(column_values, row_values, table, client_id, start, start + 1) start += 1 i += 1