def handle(self, *args, **kwargs):
base_path = os.path.join(
settings.APPS_ROOT, "frontend", "management", "commands", "measure_sql"
)
client = Client("measures")
for table_name in [
"dmd_objs_with_form_route",
"opioid_total_ome",
"practice_data_all_low_priority",
"pregabalin_total_mg",
"vw__median_price_per_unit",
"vw__ghost_generic_measure",
# This references pregabalin_total_mg, so must come afterwards
"gaba_total_ddd",
]:
path = os.path.join(base_path, table_name + ".sql")
with open(path, "r") as sql_file:
sql = sql_file.read()
try:
client.create_table_with_view(table_name, sql, False)
except Conflict:
client.delete_table(table_name)
client.create_table_with_view(table_name, sql, False)
# cmpa_products is a table that has been created and managed by Rich.
schema = build_schema(
("bnf_code", "STRING"), ("bnf_name", "STRING"), ("type", "STRING")
)
client.get_or_create_table("cmpa_products", schema)
class CommandsFunctionalTestCase(TestCase):
fixtures = ["presentations", "importlog"]
def setUp(self):
"""Create a raw_prescribing_data table such as is expected to exist by
ADQ calculation code.
"""
raw_data_path = ("frontend/tests/fixtures/commands/" +
"convert_hscic_prescribing/2016_01/" +
"EPD_201601.csv")
year_and_month = ImportLog.objects.latest_in_category(
"prescribing").current_at.strftime("%Y_%m")
self.table_name = "raw_prescribing_data_{}".format(year_and_month)
self.client = Client("tmp_eu")
t1 = self.client.get_or_create_table(self.table_name,
RAW_PRESCRIBING_SCHEMA_V2)
t1.insert_rows_from_csv(raw_data_path,
RAW_PRESCRIBING_SCHEMA_V2,
skip_leading_rows=1)
call_command("import_adqs")
def test_import_adqs_functional(self):
# These all have ADQs in the raw data
for p in Presentation.objects.filter(bnf_code__regex="0202010B0.*AB"):
self.assertEqual(p.adq_per_quantity, 1.0)
# This doesn't exist in the raw data
p = Presentation.objects.get(bnf_code="0204000I0AAAZAZ")
self.assertEqual(p.adq_per_quantity, None)
def tearDown(self):
try:
self.client.delete_table(self.table_name)
except NotFound:
pass
class CommandsFunctionalTestCase(TestCase):
fixtures = ['presentations', 'importlog']
def setUp(self):
"""Create a raw_prescribing_data table such as is expected to exist by
ADQ calculation code.
"""
raw_data_path = 'frontend/tests/fixtures/commands/' +\
'convert_hscic_prescribing/2016_01/' +\
'Detailed_Prescribing_Information.csv'
year_and_month = ImportLog.objects.latest_in_category(
'prescribing').current_at.strftime("%Y_%m")
self.table_name = 'raw_prescribing_data_{}'.format(year_and_month)
self.client = Client('tmp_eu')
t1 = self.client.get_or_create_table(
self.table_name, RAW_PRESCRIBING_SCHEMA)
t1.insert_rows_from_csv(raw_data_path, skip_leading_rows=1)
call_command('import_adqs')
def test_import_adqs_functional(self):
# These all have ADQs in the raw data
for p in Presentation.objects.filter(bnf_code__regex='0202010B0.*AB'):
self.assertEqual(p.adq_per_quantity, 1.0)
# This doesn't exist in the raw data
p = Presentation.objects.get(bnf_code='0204000I0AAAZAZ')
self.assertEqual(p.adq_per_quantity, None)
def tearDown(self):
try:
self.client.delete_table(self.table_name)
except NotFound:
pass
def handle(self, *args, **kwargs):
base_path = os.path.join(settings.APPS_ROOT, 'frontend', 'management',
'commands', 'measure_sql')
client = Client("measures")
for table_name in [
'opioid_total_ome',
'practice_data_all_low_priority',
'pregabalin_total_mg',
'vw__median_price_per_unit',
'vw__ghost_generic_measure',
]:
path = os.path.join(base_path, table_name + '.sql')
with open(path, "r") as sql_file:
sql = sql_file.read()
try:
client.create_table_with_view(table_name, sql, False)
except Conflict:
client.delete_table(table_name)
client.create_table_with_view(table_name, sql, False)
# cmpa_products is a table that has been created and managed by Rich.
schema = build_schema(
('bnf_code', 'STRING'),
('bnf_name', 'STRING'),
('type', 'STRING'),
)
client.get_or_create_table('cmpa_products', schema)
def handle(self, *args, **kwargs):
sql = """
SELECT
prescribing.sha AS sha,
ccgs.regional_team_id AS regional_team,
ccgs.stp_id AS stp,
practices.ccg_id AS pct,
prescribing.practice AS practice,
COALESCE(bnf_map.current_bnf_code, prescribing.bnf_code)
AS bnf_code,
prescribing.bnf_name AS bnf_name,
prescribing.items AS items,
prescribing.net_cost AS net_cost,
prescribing.actual_cost AS actual_cost,
prescribing.quantity AS quantity,
prescribing.month AS month
FROM
{project}.{hscic}.prescribing AS prescribing
LEFT JOIN
{project}.{hscic}.bnf_map AS bnf_map
ON
bnf_map.former_bnf_code = prescribing.bnf_code
INNER JOIN
{project}.{hscic}.practices AS practices
ON practices.code = prescribing.practice
INNER JOIN
{project}.{hscic}.ccgs AS ccgs
ON practices.ccg_id = ccgs.code
"""
client = Client('hscic')
for table_name, legacy in [
('normalised_prescribing_legacy', True),
('normalised_prescribing_standard', False),
]:
if kwargs['recreate']:
try:
client.delete_table(table_name)
except NotFound:
pass
client.create_table_with_view(table_name, sql, legacy)
def setUpClass(cls):
if 'SKIP_BQ_LOAD' not in os.environ:
# Create local test data from fixtures, then upload this to a
# test project in bigquery
call_command('loaddata',
'frontend/tests/fixtures/orgs.json',
verbosity=0)
call_command('loaddata',
'frontend/tests/fixtures/practices.json',
verbosity=0)
call_command('loaddata',
'frontend/tests/fixtures/practice_listsizes.json',
verbosity=0)
client = Client('hscic')
table = client.get_or_create_table('prescribing',
PRESCRIBING_SCHEMA)
prescribing_fixture_path = os.path.join(
'frontend', 'tests', 'fixtures', 'commands',
'prescribing_bigquery_views_fixture.csv')
table.insert_rows_from_csv(prescribing_fixture_path)
table = client.get_or_create_table('ccgs', CCG_SCHEMA)
columns = [field.name for field in CCG_SCHEMA]
table.insert_rows_from_pg(PCT, columns, ccgs_transform)
table = client.get_or_create_table('practices', PRACTICE_SCHEMA)
columns = [field.name for field in PRACTICE_SCHEMA]
table.insert_rows_from_pg(Practice, columns)
table = client.get_or_create_table('practice_statistics',
PRACTICE_STATISTICS_SCHEMA)
columns = [field.name for field in PRACTICE_STATISTICS_SCHEMA]
columns[0] = 'date'
columns[-1] = 'practice_id'
table.insert_rows_from_pg(PracticeStatistics, columns,
statistics_transform)
sql = """
SELECT
prescribing.sha AS sha,
practices.ccg_id AS pct,
prescribing.practice AS practice,
prescribing.bnf_code AS bnf_code,
prescribing.bnf_name AS bnf_name,
prescribing.items AS items,
prescribing.net_cost AS net_cost,
prescribing.actual_cost AS actual_cost,
prescribing.quantity AS quantity,
prescribing.month AS month
FROM
{project}.{hscic}.prescribing AS prescribing
INNER JOIN
{project}.{hscic}.practices AS practices
ON practices.code = prescribing.practice
"""
try:
client.delete_table('normalised_prescribing_standard')
except NotFound:
pass
client.create_table_with_view('normalised_prescribing_standard',
sql, False)
client = StorageClient()
bucket = client.get_bucket()
for blob in bucket.list_blobs(prefix='hscic/views/vw__'):
blob.delete()
else:
assert 'BQ_NONCE' in os.environ, 'Set BQ_NONCE to reuse BQ data'
ImportLog.objects.create(category='prescribing',
current_at='2015-10-01')
# Create view tables and indexes
with open('frontend/management/commands/replace_matviews.sql') as f:
with connection.cursor() as c:
c.execute(f.read())