def upload_LT_client_exit(self, df):
'''
(BeautifulUploaderAbstract, Dataframe) -> None
Uploads template LT Client Exit into the database.
'''
# get the number of rows in the dataframe
total_rows = df.shape[0]
# data starts at row 2
i = 2
column = df.iloc[1]
while (i < total_rows):
# get client id
client_id = df.iloc[i][3]
# get course code
course_code = df.iloc[i][5]
# check client_id and course_code exists in tables
key = (course_code, client_id)
row = df.iloc[i]
if (database_methods.check_id(key, 'client_data.db', "Client_Enrolment",
("(Course_Code, " +
"Client_Unique_ID_Value)")) and
not(database_methods.check_id(key, 'client_data.db', "Client_Exit",
("(Course_Code, " +
"Client_Unique_ID_Value)")))):
insert_client_exit.insert_client_exit(row, client_id,
course_code)
insert_client_exit.insert_CLB_level(column, row, client_id,
course_code)
insert_client_exit.update_child(row, client_id)
insert_client_exit.update_client_profile(client_id, row)
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_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_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, 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_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_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 update_child(row_values, client_id):
lst = ["Age", "Type_Of_Care"]
table = "Child"
prim = "(Client_Unique_ID_Value, Child)"
if (database_methods.check_id(client_id, 'client_data.db', "Client",
"Unique_ID_Value")):
insert_general.update_child(row_values, client_id, lst, table, prim, 1,
47, 56)
def insert_skills(column_values, row_values, client_id):
'''
Inserts clients skills into the Skills table.
'''
index_list = [(38, 44)]
if (database_methods.check_id(client_id, 'client_data.db', "Client",
"Unique_ID_Value")):
insert_general.update_skills(column_values, row_values, client_id,
index_list)
def insert_service_needs(column_values, row_values, service_id):
'''
Inserts rows into the Service_Needs table.
'''
if (not (database_methods.check_id(service_id, 'client_data.db',
"Service_Needs", "Service_ID"))):
insert_general.insert_3_value(column_values, row_values,
"Service_Needs", service_id, 31, 63)
insert_general.insert_3_value(column_values, row_values,
"Service_Needs", service_id, 71, 72)
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 update_client_profile(client_id, row_values):
index_list = [(0, 1), (4, 5), (9, 10)]
if (database_methods.check_id(client_id, 'client_data.db', "Client",
"Unique_ID_Value")):
insert_general.update_client_profile(row_values, client_id, index_list)
