def test_wholesaler_feedback_selects_latest_opt_out_row(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the all_la_feedback view will return multiple opt out entries for one person
build_clean_deliveries_outcome_as_table(scenario_builder)
OPT_OUT_CODE = '3'
feedback_data = [
clean_deliveries_outcome_row(id='1',
deldate=n_days_ago(5),
outcome=OPT_OUT_CODE,
comment=''),
clean_deliveries_outcome_row(id='1',
deldate=n_days_ago(1),
outcome=OPT_OUT_CODE,
comment='most recent'),
clean_deliveries_outcome_row(id='1',
deldate=n_days_ago(4),
outcome=OPT_OUT_CODE,
comment=''),
]
scenario_builder.insert_multiple_into_arbitrary_table(
"clean_deliveries_outcome_staging", feedback_data)
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/latest_wholesaler_opt_out_stack_TEMPLATE.sql')
results = presto_transaction(query)
# THEN
# only 1 result is returned and it is the most recent
assert len(results) == 1
assert results[0][3] == 'most recent'
def test_full_submission_record_appends_both_opt_in_and_opt_out_if_present():
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the tables are all built and reset
build_and_reset_data_sources(scenario_builder)
submission_data = [latest_submission_row('1', 'NO', n_days_ago(n=1))]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", submission_data)
opt_in_data = [
latest_la_feedback_opt_in_row('1', 'F003', n_days_ago(1),
'expect opt in')
]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_la_feedback_to_continue_boxes", opt_in_data)
opt_out_data = [
latest_la_feedback_opt_out_row('1', 'F002', n_days_ago(1),
'expect opt out')
]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_la_feedback_to_stop_boxes", opt_out_data)
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = read_query('sql_to_build/full_submission_record.sql')
results = presto_transaction(query)
# THEN
# the results are 1 lines long and includes all the codes
assert len(results) == 1
assert 'F002' in results[0]
assert 'F003' in results[0]
assert 'expect opt in' in results[0]
assert 'expect opt out' in results[0]
def test_latest_feedback_opt_out_returns_single_opt_out_row_if_two_on_the_same_day(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the all_la_feedback view will return feedback for one person at multiple times
# including two lots of opt out feedback on the most recent occasion
test_nhs_number = f'{random_nhs_number()}'
feedback_data = [
all_la_feedback_row(test_nhs_number, 'YF19', n_days_ago(n=10), ""),
all_la_feedback_row(test_nhs_number, 'D001', n_days_ago(n=9), ""),
all_la_feedback_row(test_nhs_number, 'RM19', n_days_ago(n=8), ""),
all_la_feedback_row(test_nhs_number, 'W003', n_days_ago(n=6),
"either this one"),
all_la_feedback_row(test_nhs_number, 'YF19', n_days_ago(n=6), ""),
all_la_feedback_row(test_nhs_number, 'W004', n_days_ago(n=6),
"or this one")
]
scenario_builder.insert_multiple_into_arbitrary_table(
"all_la_feedback", feedback_data)
# WHEN
# we build the latest feedback opt out stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/latest_la_feedback_opt_out_stack_TEMPLATE.sql')
results = presto_transaction(query)
# THEN
# the results for test_number is 1 lines long and has one of the feedback results from 6 days ago
results_for_nhs_number = [
result for result in results if result[0] == test_nhs_number
]
assert len(results_for_nhs_number) == 1
assert results_for_nhs_number[0][
1] == 'W004' or results_for_nhs_number[0][1] == 'W003'
def test_submissions_feed_through_to_final_query(tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# a simple situation where users have registered on the web or ivr
build_and_reset_data_sources(scenario_builder)
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", [
latest_submission_row('1', 'NO', n_days_ago(n=1)),
latest_submission_row('2', 'NO', n_days_ago(n=1)),
latest_submission_row('3', 'NO', n_days_ago(n=1)),
])
scenario_builder.insert_multiple_into_arbitrary_table(
"nhs_clean_staging", [
nhs_clean_staging_row(1),
nhs_clean_staging_row(2),
nhs_clean_staging_row(3),
])
scenario_builder.insert_multiple_into_arbitrary_table(
"clean_latest_address_staging", [
clean_latest_address_row(1),
clean_latest_address_row(2),
clean_latest_address_row(3)
])
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/wholesaler_latest_user_submission_test_stack_TEMPLATE.sql'
)
results = presto_transaction(query)
# THEN
# there are 3 results returned
assert len(results) == 3
def reset_ivr_data(self):
with pg_connect() as con:
create_command = f'CREATE TABLE IF NOT EXISTS "ivr_clean_staging" (' \
f'ivr_nhs_number TEXT,' \
f'ivr_postcode TEXT,' \
f'ivr_dob TEXT,' \
f'ivr_customer_calling_number TEXT,' \
f'ivr_current_item_id TEXT,' \
f'ivr_transfer TEXT,' \
f'ivr_fallback_time TEXT,' \
f'ivr_nhs_known TEXT,' \
f'ivr_contact_id TEXT,' \
f'ivr_preferred_phone_number TEXT,' \
f'ivr_postal_code_verified TEXT,' \
f'ivr_delivery_supplies TEXT,' \
f'ivr_phone_number_calls TEXT,' \
f'ivr_carry_supplies TEXT,' \
f'ivr_have_help TEXT,' \
f'ivr_call_timestamp TEXT,' \
f'ivr_unmet_needs TEXT)'
con.run(create_command)
con.run('DELETE FROM "ivr_clean_staging"')
con.commit()
def test_la_feedback_opt_in_stack_selects_opt_out_feedback_codes(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the all_la_feedback view will return mixed feedback codes
build_all_la_feedback_as_table(scenario_builder)
opt_in_codes = ['W006', 'F003']
feedback_data = [
all_la_feedback_row('test_1_1', 'W006', n_days_ago(n=10), ""),
all_la_feedback_row('test_1_2', 'W003', n_days_ago(n=10),
"opt out code"),
all_la_feedback_row('test_1_3', 'F003', n_days_ago(n=10), ""),
all_la_feedback_row('test_1_4', 'D001', n_days_ago(n=10),
"opt out code"),
all_la_feedback_row('test_1_5', 'W004', n_days_ago(n=10),
"opt out code")
]
scenario_builder.insert_multiple_into_arbitrary_table(
"all_la_feedback", feedback_data)
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/latest_la_feedback_opt_in_stack_TEMPLATE.sql')
results = presto_transaction(query)
# THEN
# the results are 3 lines long and include all the codes
assert len(results) == 2
codes = [result[1] for result in results]
assert set(codes) == set(opt_in_codes)
def reset_web_data(self):
with pg_connect() as con:
create_command = f'CREATE TABLE IF NOT EXISTS "web_clean_staging" (' \
f'live_in_england TEXT,' \
f'first_name TEXT,' \
f'middle_name TEXT,' \
f'last_name TEXT,' \
f'city TEXT,' \
f'address_l1 TEXT,' \
f'address_l2 TEXT,' \
f'county TEXT,' \
f'postcode TEXT,' \
f'nhs_number TEXT,' \
f'carry_supplies TEXT,' \
f'reference_id TEXT,' \
f'dob_day TEXT,' \
f'dob_month TEXT,' \
f'dob_year TEXT,' \
f'full_dob TEXT,' \
f'phone_number_calls TEXT,' \
f'phone_number_texts TEXT,' \
f'contact TEXT,' \
f'know_nhs_number TEXT,' \
f'check_answers_seen TEXT,' \
f'nhs_letter TEXT,' \
f'basic_care_needs TEXT,' \
f'dietary_requirements TEXT,' \
f'medical_conditions TEXT,' \
f'essential_supplies TEXT,' \
f'updated_at NUMERIC,' \
f'referenceid TEXT,' \
f'unixtimestamp NUMERIC,' \
f'created_at NUMERIC)'
con.run(create_command)
con.run('DELETE FROM "web_clean_staging"')
con.commit()
def reset_nhs_data(self):
with pg_connect() as con:
create_command = f'CREATE TABLE IF NOT EXISTS "nhs_clean_staging" (' \
f'nhs_nhs_number TEXT,' \
f'nhs_dob TEXT,' \
f'nhs_patient_title VARCHAR(256),' \
f'nhs_patients_first_name TEXT,' \
f'nhs_patients_other_name TEXT,' \
f'nhs_patients_surname TEXT,' \
f'nhs_patients_address_line1 TEXT,' \
f'nhs_patients_address_line2 TEXT,' \
f'nhs_patients_address_line3 TEXT,' \
f'nhs_patients_address_line4 TEXT,' \
f'nhs_patients_address_line5 TEXT,' \
f'nhs_postcode TEXT,' \
f'nhs_practice_code TEXT,' \
f'nhs_practice_name TEXT,' \
f'nhs_contact_telephone TEXT,' \
f'nhs_deceased TEXT,' \
f'nhs_data_source TEXT,' \
f'nhs_inception_date TEXT)'
con.run(create_command)
con.run('DELETE FROM "nhs_clean_staging"')
con.commit()
def test_latest_submission_picks_most_recent_submission():
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
table_schema = {
'provenance': 'TEXT',
'nhs_number': 'TEXT',
'submission_time': 'TEXT',
'has_access_to_essential_supplies': 'TEXT',
'is_able_to_carry_supplies': 'TEXT',
'email_address': 'TEXT',
'phone_number_calls': 'TEXT',
'phone_number_texts': 'TEXT'
}
scenario_builder.build_arbitrary_table("all_submissions", table_schema)
submission_data = [
{
'provenance': 'IVR',
'nhs_number': '123',
'submission_time': n_days_ago(n=5),
'has_access_to_essential_supplies': 'YES',
'is_able_to_carry_supplies': 'YES',
'email_address': '*****@*****.**',
'phone_number_calls': '12345',
'phone_number_texts': '12345'
},
{
'provenance': 'WEB',
'nhs_number': '123',
'submission_time': n_days_ago(n=1),
'has_access_to_essential_supplies': 'NO',
'is_able_to_carry_supplies': 'no',
'email_address': 'test_mail@test_mail.com',
'phone_number_calls': '0891505050',
'phone_number_texts': '0891505050'
},
{
'provenance': 'IVR',
'nhs_number': '123',
'submission_time': n_days_ago(n=3),
'has_access_to_essential_supplies': 'YES',
'is_able_to_carry_supplies': 'YES',
'email_address': '*****@*****.**',
'phone_number_calls': '12345',
'phone_number_texts': '12345'
}, {
'provenance': 'IVR',
'nhs_number': '123',
'submission_time': n_days_ago(n=4),
'has_access_to_essential_supplies': 'YES',
'is_able_to_carry_supplies': 'YES',
'email_address': '*****@*****.**',
'phone_number_calls': '12345',
'phone_number_texts': '12345'
}
]
expected_row = submission_data[1]
scenario_builder.insert_multiple_into_arbitrary_table(
"all_submissions", submission_data
)
with open('sql_to_build/latest_submission.sql', 'r') as f:
query = f.read()
results = presto_transaction(query)
assert len(results) == 1
assert all((item in list(expected_row.values()) for item in results[0]))