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())