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): 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)
def handle(self, *args, **kwargs): try: Client("hscic").create_storage_backed_table( "raw_prescribing", RAW_PRESCRIBING_SCHEMA, "hscic/prescribing/20*Detailed_Prescribing_Information.csv", ) except Conflict: pass 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", "vw__herbal_list", # This references pregabalin_total_mg, so must come afterwards "gaba_total_ddd", ]: self.recreate_table(client, table_name) self.recreate_table(Client("hscic"), "raw_prescribing_normalised") # 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 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/ccgs.json', verbosity=0) call_command('loaddata', 'frontend/tests/fixtures/practices.json', verbosity=0) call_command('loaddata', 'frontend/tests/fixtures/practice_listsizes.json', verbosity=0) prescribing_fixture_path = os.path.join( 'frontend', 'tests', 'fixtures', 'commands', 'prescribing_bigquery_views_fixture.csv' ) client = Client('hscic') for table_name in [ 'normalised_prescribing_standard', 'normalised_prescribing_legacy']: table = client.get_or_create_table( table_name, PRESCRIBING_SCHEMA ) 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( '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 ) client = StorageClient() bucket = client.get_bucket() for blob in bucket.list_blobs(prefix='hscic/views/vw__'): blob.delete() ImportLog.objects.create( category='prescribing', current_at='2015-10-01') # Create view tables and indexes with open( 'frontend/management/commands/replace_matviews.sql', 'r') as f: with connection.cursor() as c: c.execute(f.read())
def setUpTestData(cls): fixtures_base_path = os.path.join( 'frontend', 'tests', 'fixtures', 'commands', ) client = Client('hscic') prescribing_fixture_path = os.path.join( fixtures_base_path, 'prescribing_bigquery_fixture.csv' ) table = client.get_or_create_table( 'normalised_prescribing_standard', bq_schemas.PRESCRIBING_SCHEMA ) table.insert_rows_from_csv(prescribing_fixture_path) practices_fixture_path = os.path.join( fixtures_base_path, 'practices.csv' ) table = client.get_or_create_table( 'practices', bq_schemas.PRACTICE_SCHEMA ) columns = [field.name for field in bq_schemas.PRACTICE_SCHEMA] table.insert_rows_from_csv(practices_fixture_path) tariff_path = os.path.join(fixtures_base_path, 'tariff_fixture.csv') table = client.get_or_create_table('tariff', bq_schemas.TARIFF_SCHEMA) table.insert_rows_from_csv(tariff_path) bnf_path = os.path.join( fixtures_base_path, 'bnf_codes_for_ppu_savings.csv' ) table = client.get_or_create_table('bnf', bq_schemas.BNF_SCHEMA) table.insert_rows_from_csv(bnf_path) month = date(2015, 9, 1) dummy_substitutions = pd.read_csv( os.path.join(fixtures_base_path, 'ppu_substitutions.csv')) with patch( 'frontend.management.commands.import_ppu_savings.pd.read_csv', return_value=dummy_substitutions): import_ppu_savings.Command().handle( month=month, min_ccg_saving=0, min_practice_saving=0, limit=1 )
def reimport_all(self): last_imported = ImportLog.objects.latest_in_category( 'prescribing').current_at self.date = last_imported - relativedelta(years=5) client = Client('tmp_eu') while self.date <= last_imported: date_str = self.date.strftime('%Y-%m-%d') sql = ('SELECT pct AS pct_id, practice AS practice_id, ' 'bnf_code AS presentation_code, items AS total_items, ' 'net_cost, actual_cost, quantity, ' 'FORMAT_TIMESTAMP("%%Y_%%m_%%d", month) AS processing_date ' 'FROM {hscic}.normalised_prescribing_standard ' "WHERE month = '%s'" % date_str) table_name = "prescribing_%s" % date_str.replace('-', '_') table = client.get_or_create_table(table_name) table.insert_rows_from_query(sql) exporter = TableExporter(table, 'tmp/{}-*'.format(table_name)) exporter.export_to_storage() with tempfile.NamedTemporaryFile(mode='wb') as tmpfile: logger.info("Importing data for %s" % self.date) exporter.download_from_storage_and_unzip(tmpfile) with transaction.atomic(): self.drop_partition() self.create_partition() self.import_prescriptions(tmpfile.name) self.create_partition_indexes() self.add_parent_trigger() self.date += relativedelta(months=1)
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
def set_up_bq(): """Set up BQ datasets and tables.""" try: Client("measures").create_dataset() except Conflict: pass client = Client("hscic") client.get_or_create_table("ccgs", schemas.CCG_SCHEMA) client.get_or_create_table("practices", schemas.PRACTICE_SCHEMA) client.get_or_create_table("normalised_prescribing", schemas.PRESCRIBING_SCHEMA) client.get_or_create_table("practice_statistics", schemas.PRACTICE_STATISTICS_SCHEMA) client.get_or_create_table("presentation", schemas.PRESENTATION_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/' +\ '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 set_up_bq(): '''Set up BQ datasets and tables.''' Client('measures').create_dataset() client = Client('hscic') client.get_or_create_table('ccgs', schemas.CCG_SCHEMA) client.get_or_create_table('practices', schemas.PRACTICE_SCHEMA) client.get_or_create_table('normalised_prescribing_standard', schemas.PRESCRIBING_SCHEMA) client.get_or_create_table('practice_statistics', schemas.PRACTICE_STATISTICS_SCHEMA)
def handle(self, *args, **options): """ Compute and store cost savings for the specified month. Deletes any existing data for that month. """ if not options["month"]: last_prescribing = ImportLog.objects.latest_in_category( "prescribing" ).current_at options["month"] = last_prescribing log = ImportLog.objects.latest_in_category("ppu") if log is not None: if options["month"] <= log.current_at: raise argparse.ArgumentTypeError("Couldn't infer date") with transaction.atomic(): # Create custom presentations for our overrides, if they don't exist. # See https://github.com/ebmdatalab/price-per-dose/issues/1. Presentation.objects.get_or_create( bnf_code="0601060D0AAA0A0", name="Glucose Blood Testing Reagents", is_generic=True, ) Presentation.objects.get_or_create( bnf_code="0601060U0AAA0A0", name="Urine Testing Reagents", is_generic=True, ) PPUSaving.objects.filter(date=options["month"]).delete() for entity_type in ["pct", "practice"]: result = get_savings(entity_type, options["month"]) for row in result.itertuples(): d = row._asdict() if d["price_per_unit"]: PPUSaving.objects.create( date=options["month"], presentation_id=d["Index"], lowest_decile=d["lowest_decile"], quantity=d["quantity"], price_per_unit=d["price_per_unit"], possible_savings=d["possible_savings"], formulation_swap=d["formulation_swap"] or None, pct_id=d.get("pct", None), practice_id=d.get("practice", None), ) ImportLog.objects.create( category="ppu", filename="n/a", current_at=options["month"] ) client = Client("hscic") table = client.get_or_create_table("ppu_savings", PPU_SAVING_SCHEMA) table.insert_rows_from_pg( PPUSaving, PPU_SAVING_SCHEMA, transformer=ppu_savings_transform )
def handle(self, *args, **options): ''' Compute and store cost savings for the specified month. Deletes any existing data for that month. ''' if not options['month']: last_prescribing = ImportLog.objects.latest_in_category( 'prescribing').current_at options['month'] = last_prescribing log = ImportLog.objects.latest_in_category('ppu') if log is not None: if options['month'] <= log.current_at: raise argparse.ArgumentTypeError("Couldn't infer date") with transaction.atomic(): # Create custom presentations for our overrides, if they don't exist. # See https://github.com/ebmdatalab/price-per-dose/issues/1. Presentation.objects.get_or_create( bnf_code='0601060D0AAA0A0', name='Glucose Blood Testing Reagents', is_generic=True) Presentation.objects.get_or_create(bnf_code='0601060U0AAA0A0', name='Urine Testing Reagents', is_generic=True) PPUSaving.objects.filter(date=options['month']).delete() for entity_type in ['pct', 'practice']: result = get_savings(entity_type, options['month']) for row in result.itertuples(): d = row._asdict() if d['price_per_unit']: PPUSaving.objects.create( date=options['month'], presentation_id=d['Index'], lowest_decile=d['lowest_decile'], quantity=d['quantity'], price_per_unit=d['price_per_unit'], possible_savings=d['possible_savings'], formulation_swap=d['formulation_swap'] or None, pct_id=d.get('pct', None), practice_id=d.get('practice', None)) ImportLog.objects.create(category='ppu', filename='n/a', current_at=options['month']) client = Client('hscic') table = client.get_or_create_table('ppu_savings', PPU_SAVING_SCHEMA) columns = [field.name for field in PPU_SAVING_SCHEMA] table.insert_rows_from_pg(PPUSaving, columns, ppu_savings_transform)
def create_bigquery_table(): """Create a table in bigquery of all BNF codes for presentations that are no longer current, along with the BNF code of their latest incarnation """ # output a row for each presentation and its ultimate replacement with tempfile.NamedTemporaryFile(mode='r+b') as csv_file: writer = csv.writer(csv_file) for p in Presentation.objects.filter(replaced_by__isnull=False): writer.writerow([p.bnf_code, p.current_version.bnf_code]) csv_file.seek(0) client = Client('hscic') table = client.get_or_create_table('bnf_map', schemas.BNF_MAP_SCHEMA) table.insert_rows_from_csv(csv_file.name)
def handle(self, *args, **kwargs): # Ensure that we'll use the test BQ instance assert settings.BQ_PROJECT == "ebmdatalabtest", settings.BQ_PROJECT # Ensure we won't pick up any unexpected models for model in [ Measure, MeasureGlobal, MeasureValue, Practice, Prescription, PCT, STP, RegionalTeam, ]: assert model.objects.count() == 0, model # Delete any ImportLogs that were created by migrations ImportLog.objects.all().delete() # Create a bunch of RegionalTeams, STPs, CCGs, Practices for regtm_ix in range(2): regtm = RegionalTeam.objects.create( code="Y0{}".format(regtm_ix), name="Region {}".format(regtm_ix)) for stp_ix in range(2): stp = STP.objects.create( ons_code="E000000{}{}".format(regtm_ix, stp_ix), name="STP {}/{}".format(regtm_ix, stp_ix), ) pcns = [] for pcn_ix in range(2): pcn = PCN.objects.create( code="E00000{}{}{}".format(regtm_ix, stp_ix, pcn_ix), name="PCN {}/{}/{}".format(regtm_ix, stp_ix, pcn_ix), ) pcns.append(pcn) # Function to return next PCN, looping round forever get_next_pcn = itertools.cycle(pcns).__next__ for ccg_ix in range(2): ccg = PCT.objects.create( regional_team=regtm, stp=stp, code="{}{}{}".format(regtm_ix, stp_ix, ccg_ix).replace("0", "A"), name="CCG {}/{}/{}".format(regtm_ix, stp_ix, ccg_ix), org_type="CCG", ) for prac_ix in range(2): Practice.objects.create( ccg=ccg, pcn=get_next_pcn(), code="P0{}{}{}{}".format(regtm_ix, stp_ix, ccg_ix, prac_ix), name="Practice {}/{}/{}/{}".format( regtm_ix, stp_ix, ccg_ix, prac_ix), setting=4, address1="", address2="", address3="", address4="", address5="", postcode="", ) # import_measures uses this ImportLog to work out which months it # should import data. ImportLog.objects.create(category="prescribing", current_at="2018-08-01") # The practice, CCG etc dashboards use this date ImportLog.objects.create(category="dashboard_data", current_at="2018-08-01") # Set up BQ, and upload STPs, CCGs, Practices. Client("measures").create_dataset() client = Client("hscic") table = client.get_or_create_table("ccgs", schemas.CCG_SCHEMA) table.insert_rows_from_pg(PCT, schemas.CCG_SCHEMA, transformer=schemas.ccgs_transform) table = client.get_or_create_table("practices", schemas.PRACTICE_SCHEMA) table.insert_rows_from_pg(Practice, schemas.PRACTICE_SCHEMA) # Create measures definitions and record the BNF codes used bnf_codes = [] for ix in range(5): numerator_bnf_codes_filter = ["0{}01".format(ix)] denominator_bnf_codes_filter = ["0{}".format(ix)] if ix in [0, 1]: measure_id = "core_{}".format(ix) name = "Core measure {}".format(ix) tags = ["core"] tags_focus = None elif ix in [2, 3]: measure_id = "lp_{}".format(ix) name = "LP measure {}".format(ix) tags = ["lowpriority"] tags_focus = None else: assert ix == 4 measure_id = "lpzomnibus" name = "LP omnibus measure" tags = ["core"] tags_focus = ["lowpriority"] numerator_bnf_codes_filter = ["0201", "0301"] denominator_bnf_codes_filter = ["02", "03"] measure_definition = { "name": name, "title": "{} Title".format(ix), "description": "{} description".format(name), "why_it_matters": "Why {} matters".format(name), "url": "http://example.com/measure-{}".format(measure_id), "numerator_short": "Numerator for {}".format(measure_id), "numerator_type": "bnf_quantity", "numerator_bnf_codes_filter": numerator_bnf_codes_filter, "denominator_short": "Denominator for {}".format(measure_id), "denominator_type": "bnf_quantity", "denominator_bnf_codes_filter": denominator_bnf_codes_filter, "is_cost_based": True, "is_percentage": True, "low_is_good": True, "tags": tags, "tags_focus": tags_focus, } path = os.path.join(settings.MEASURE_DEFINITIONS_PATH, "{}.json".format(measure_id)) with open(path, "w") as f: json.dump(measure_definition, f, indent=2) bnf_codes.append("0{}0000000000000".format(ix)) bnf_codes.append("0{}0100000000000".format(ix)) # Generate random prescribing data. We don't currently save this to the # database as it would make the fixture too big and isn't needed. # Later we create the minimal prescribing needed by the MatrixStore. prescribing_rows = [] timestamps = [ "2018-0{}-01 00:00:00 UTC".format(month) for month in [1, 2, 3, 4, 5, 6, 7, 8] ] for practice_ix, practice in enumerate(Practice.objects.all()): for month, timestamp in enumerate(timestamps, start=1): # 0 <= practice_ix <= 15; 1 <= month <= 8 item_ratio = (22 + practice_ix - 2 * month + randint(-5, 5)) / 43.0 assert 0 < item_ratio < 1 numerator_items = 100 + randint(0, 100) denominator_items = int(numerator_items / item_ratio) for bnf_code_ix, bnf_code in enumerate(bnf_codes): if bnf_code_ix % 2 == 0: items = denominator_items else: items = numerator_items quantity = 28 * items unit_cost = 1 + bnf_code_ix actual_cost = unit_cost * quantity # We don't care about net_cost. net_cost = actual_cost row = [ "sha", # This value doesn't matter. practice.ccg_id, practice.code, bnf_code, "bnf_name", # This value doesn't matter items, net_cost, actual_cost, quantity, timestamp, ] prescribing_rows.append(row) # Create the minimal amount of prescribing necessary for the # MatrixStore to build and for the homepages to load. This means # at least one prescription for each practice. for practice in Practice.objects.all(): bnf_code = bnf_codes[-1] timestamp = timestamps[-1] items = 10 quantity = 500 net_cost = 100 actual_cost = 95 row = [ "sha", # This value doesn't matter. practice.ccg_id, practice.code, bnf_code, "bnf_name", # This value doesn't matter items, net_cost, actual_cost, quantity, timestamp, ] prescribing_rows.append(row) # Unlike the measure prescribing we created earlier this # prescribing needs to be written to the database so it gets # included in the fixture we create Prescription.objects.create( practice_id=row[2], pct_id=row[1], presentation_code=row[3], total_items=row[5], net_cost=row[6], actual_cost=row[7], quantity=row[8], processing_date=row[9][:10], ) # Upload presentations to BigQuery: the new measures system requires them table = client.get_or_create_table("presentation", schemas.PRESENTATION_SCHEMA) with tempfile.NamedTemporaryFile(mode="wt", encoding="utf8", newline="") as f: writer = csv.DictWriter( f, [field.name for field in schemas.PRESENTATION_SCHEMA]) for bnf_code in bnf_codes: writer.writerow({"bnf_code": bnf_code}) f.seek(0) table.insert_rows_from_csv(f.name, schemas.PRESENTATION_SCHEMA) # In production, normalised_prescribing is actually a view, # but for the tests it's much easier to set it up as a normal table. table = client.get_or_create_table("normalised_prescribing", schemas.PRESCRIBING_SCHEMA) # Upload prescribing_rows to normalised_prescribing. with tempfile.NamedTemporaryFile(mode="wt", encoding="utf8", newline="") as f: writer = csv.writer(f) for row in prescribing_rows: writer.writerow(row) f.seek(0) table.insert_rows_from_csv(f.name, schemas.PRESCRIBING_SCHEMA) # Create some dummy prescribing data in the MatrixStore. factory = DataFactory() month = factory.create_months("2018-10-01", 1)[0] practice = factory.create_practices(1)[0] for bnf_code in bnf_codes: presentation = factory.create_presentation(bnf_code) factory.create_prescription(presentation, practice, month) # Do the work. with patched_global_matrixstore_from_data_factory(factory): call_command("import_measures", measure="core_0,core_1,lp_2,lp_3,lpzomnibus") # Clean up. for ix in range(5): if ix in [0, 1]: measure_id = "core_{}".format(ix) elif ix in [2, 3]: measure_id = "lp_{}".format(ix) else: assert ix == 4 measure_id = "lpzomnibus" path = os.path.join(settings.MEASURE_DEFINITIONS_PATH, "{}.json".format(measure_id)) os.remove(path) # Dump the fixtures. fixture_path = os.path.join("frontend", "tests", "fixtures", "functional-measures-dont-edit.json") call_command("dumpdata", "frontend", indent=2, output=fixture_path)
def handle(self, *args, **kwargs): # Ensure that we'll use the test BQ instance assert settings.BQ_PROJECT == 'ebmdatalabtest', settings.BQ_PROJECT # Ensure we won't pick up any unexpected models for model in [ Measure, MeasureGlobal, MeasureValue, Practice, PCT, STP, RegionalTeam ]: assert model.objects.count() == 0, model # Delete any ImportLogs that were created by migrations ImportLog.objects.all().delete() # Create a bunch of RegionalTeams, STPs, CCGs, Practices for regtm_ix in range(2): regtm = RegionalTeam.objects.create( code='Y0{}'.format(regtm_ix), name='Region {}'.format(regtm_ix), ) for stp_ix in range(2): stp = STP.objects.create( ons_code='E000000{}{}'.format(regtm_ix, stp_ix), name='STP {}/{}'.format(regtm_ix, stp_ix), ) for ccg_ix in range(2): ccg = PCT.objects.create( regional_team=regtm, stp=stp, code='{}{}{}'.format(regtm_ix, stp_ix, ccg_ix).replace('0', 'A'), name='CCG {}/{}/{}'.format(regtm_ix, stp_ix, ccg_ix), org_type='CCG', ) for prac_ix in range(2): Practice.objects.create( ccg=ccg, code='P0{}{}{}{}'.format(regtm_ix, stp_ix, ccg_ix, prac_ix), name='Practice {}/{}/{}/{}'.format( regtm_ix, stp_ix, ccg_ix, prac_ix), setting=4, address1='', address2='', address3='', address4='', address5='', postcode='', ) # import_measures uses this ImportLog to work out which months it # should import data. ImportLog.objects.create( category='prescribing', current_at='2018-08-01', ) # Practice and CCG homepages need this to work out which PPU savings to # show. ImportLog.objects.create( category='ppu', current_at='2018-08-01', ) # Set up BQ, and upload STPs, CCGs, Practices. Client('measures').create_dataset() client = Client('hscic') table = client.get_or_create_table('ccgs', schemas.CCG_SCHEMA) columns = [field.name for field in schemas.CCG_SCHEMA] table.insert_rows_from_pg(PCT, columns, schemas.ccgs_transform) table = client.get_or_create_table('practices', schemas.PRACTICE_SCHEMA) columns = [field.name for field in schemas.PRACTICE_SCHEMA] table.insert_rows_from_pg(Practice, columns) # Create measures definitions and record the BNF codes used measure_definitions_path = os.path.join(settings.APPS_ROOT, 'frontend', 'management', 'commands', 'measure_definitions') # lpzomnibus is a real measure, and we don't want to overwrite its # definition. os.rename( os.path.join(measure_definitions_path, 'lpzomnibus.json'), os.path.join(measure_definitions_path, 'lpzomnibus.json.bak'), ) bnf_codes = [] for ix in range(5): numerator_where = "bnf_code LIKE '0{}01%'".format(ix), denominator_where = "bnf_code LIKE '0{}%'".format(ix), if ix in [0, 1]: measure_id = 'core_{}'.format(ix) name = 'Core measure {}'.format(ix) tags = ['core'] tags_focus = None elif ix in [2, 3]: measure_id = 'lp_{}'.format(ix) name = 'LP measure {}'.format(ix) tags = ['lowpriority'] tags_focus = None else: assert ix == 4 measure_id = 'lpzomnibus' name = 'LP omnibus measure' tags = ['core'] tags_focus = ['lowpriority'] numerator_where = "bnf_code LIKE '0201%' OR bnf_code LIKE'0301%'", denominator_where = "bnf_code LIKE '02%' OR bnf_code LIKE'03%'", measure_definition = { 'name': name, 'title': '{} Title'.format(ix), 'description': '{} description'.format(name), 'why_it_matters': 'Why {} matters'.format(name), 'url': 'http://example.com/measure-{}'.format(measure_id), 'numerator_short': 'Numerator for {}'.format(measure_id), 'numerator_from': '{hscic}.normalised_prescribing_standard', 'numerator_where': numerator_where, 'numerator_columns': 'SUM(quantity) AS numerator', 'denominator_short': 'Denominator for {}'.format(measure_id), 'denominator_from': '{hscic}.normalised_prescribing_standard', 'denominator_where': denominator_where, 'denominator_columns': 'SUM(quantity) AS denominator', 'is_cost_based': True, 'is_percentage': True, 'low_is_good': True, 'tags': tags, 'tags_focus': tags_focus, } path = os.path.join(measure_definitions_path, '{}.json'.format(measure_id)) with open(path, 'w') as f: json.dump(measure_definition, f, indent=2) bnf_codes.append('0{}0000000000000'.format(ix)) bnf_codes.append('0{}0100000000000'.format(ix)) # Generate random prescribing data. This data is never saved to the # database. prescribing_rows = [] for practice_ix, practice in enumerate(Practice.objects.all()): for month in [1, 2, 3, 4, 5, 6, 7, 8]: timestamp = '2018-0{}-01 00:00:00 UTC'.format(month) # 0 <= practice_ix <= 15; 1 <= month <= 8 item_ratio = (22 + practice_ix - 2 * month + randint(-5, 5)) / 43.0 assert 0 < item_ratio < 1 numerator_items = 100 + randint(0, 100) denominator_items = int(numerator_items / item_ratio) for bnf_code_ix, bnf_code in enumerate(bnf_codes): if bnf_code_ix % 2 == 0: items = denominator_items else: items = numerator_items quantity = 28 * items unit_cost = 1 + bnf_code_ix actual_cost = unit_cost * quantity # We don't care about net_cost. net_cost = actual_cost row = [ 'sha', # This value doesn't matter. practice.ccg.regional_team_id, practice.ccg.stp_id, practice.ccg_id, practice.code, bnf_code, 'bnf_name', # This value doesn't matter items, net_cost, actual_cost, quantity, timestamp, ] prescribing_rows.append(row) # In production, normalised_prescribing_standard is actually a view, # but for the tests it's much easier to set it up as a normal table. table = client.get_or_create_table('normalised_prescribing_standard', schemas.PRESCRIBING_SCHEMA) # Upload prescribing_rows to normalised_prescribing_standard. with tempfile.NamedTemporaryFile() as f: writer = csv.writer(f) for row in prescribing_rows: writer.writerow(row) f.seek(0) table.insert_rows_from_csv(f.name) # Do the work. call_command('import_measures', measure='core_0,core_1,lp_2,lp_3,lpzomnibus') # Clean up. for ix in range(5): numerator_where = "bnf_code LIKE '0{}01%'".format(ix), denominator_where = "bnf_code LIKE '0{}%'".format(ix), if ix in [0, 1]: measure_id = 'core_{}'.format(ix) elif ix in [2, 3]: measure_id = 'lp_{}'.format(ix) else: assert ix == 4 measure_id = 'lpzomnibus' path = os.path.join(measure_definitions_path, '{}.json'.format(measure_id)) os.remove(path) os.rename( os.path.join(measure_definitions_path, 'lpzomnibus.json.bak'), os.path.join(measure_definitions_path, 'lpzomnibus.json'), ) # Dump the fixtures. fixture_path = os.path.join('frontend', 'tests', 'fixtures', 'functional-measures.json') call_command('dumpdata', 'frontend', indent=2, output=fixture_path)
def setUpTestData(cls): bassetlaw = PCT.objects.create(code='02Q', org_type='CCG') lincs_west = PCT.objects.create(code='04D', org_type='CCG') lincs_east = PCT.objects.create(code='03T', org_type='CCG', open_date='2013-04-01', close_date='2015-01-01') Chemical.objects.create(bnf_code='0703021Q0', chem_name='Desogestrel') Chemical.objects.create(bnf_code='0408010A0', chem_name='Levetiracetam') Practice.objects.create(code='C84001', ccg=bassetlaw, name='LARWOOD SURGERY', setting=4) Practice.objects.create(code='C84024', ccg=bassetlaw, name='NEWGATE MEDICAL GROUP', setting=4) Practice.objects.create(code='B82005', ccg=bassetlaw, name='PRIORY MEDICAL GROUP', setting=4, open_date='2015-01-01') Practice.objects.create(code='B82010', ccg=bassetlaw, name='RIPON SPA SURGERY', setting=4) Practice.objects.create(code='A85017', ccg=bassetlaw, name='BEWICK ROAD SURGERY', setting=4) Practice.objects.create(code='A86030', ccg=bassetlaw, name='BETTS AVENUE MEDICAL GROUP', setting=4) Practice.objects.create(code='C83051', ccg=lincs_west, name='ABBEY MEDICAL PRACTICE', setting=4) Practice.objects.create(code='C83019', ccg=lincs_east, name='BEACON MEDICAL PRACTICE', setting=4) # Ensure we only include open practices in our calculations. Practice.objects.create(code='B82008', ccg=bassetlaw, name='NORTH SURGERY', setting=4, open_date='2010-04-01', close_date='2012-01-01') # Ensure we only include standard practices in our calculations. Practice.objects.create(code='Y00581', ccg=bassetlaw, name='BASSETLAW DRUG & ALCOHOL SERVICE', setting=1) fixtures_base_path = os.path.join( 'frontend', 'tests', 'fixtures', 'commands', ) client = Client('hscic') prescribing_fixture_path = os.path.join( fixtures_base_path, 'prescribing_bigquery_fixture.csv' ) table = client.get_or_create_table( 'normalised_prescribing_standard', bq_schemas.PRESCRIBING_SCHEMA ) table.insert_rows_from_csv(prescribing_fixture_path) practices_fixture_path = os.path.join( fixtures_base_path, 'practices.csv' ) table = client.get_or_create_table( 'practices', bq_schemas.PRACTICE_SCHEMA ) table.insert_rows_from_csv(practices_fixture_path) tariff_path = os.path.join(fixtures_base_path, 'tariff_fixture.csv') table = client.get_or_create_table('tariff', bq_schemas.TARIFF_SCHEMA) table.insert_rows_from_csv(tariff_path) bnf_path = os.path.join( fixtures_base_path, 'bnf_codes_for_ppu_savings.csv' ) table = client.get_or_create_table('bnf', bq_schemas.BNF_SCHEMA) table.insert_rows_from_csv(bnf_path) month = date(2015, 9, 1) dummy_substitutions = pd.read_csv( os.path.join(fixtures_base_path, 'ppu_substitutions.csv')) with patch( 'frontend.management.commands.import_ppu_savings.pd.read_csv', return_value=dummy_substitutions): import_ppu_savings.Command().handle(month=month)
def setUp(self): client = BQClient("hscic") client.get_or_create_table("prescribing", PRESCRIBING_SCHEMA)
def test_the_lot(self): client = Client("test") archive_client = Client("archive") orig_schema = build_schema(("a", "STRING"), ("b", "INTEGER")) schema = build_schema(("a", "INTEGER"), ("b", "STRING")) headers = ["a", "b"] rows = [(1, "apple"), (2, "banana"), (3, "coconut")] t1 = client.get_or_create_table("t1", orig_schema) t1_qname = t1.qualified_name # Test Table.insert_rows_from_csv t1.insert_rows_from_csv("gcutils/tests/test_table.csv", schema) self.assertEqual(sorted(t1.get_rows()), rows) # Test Table.insert_rows_from_query t2 = client.get_table("t2") sql = "SELECT * FROM {} WHERE a > 1".format(t1_qname) t2.insert_rows_from_query(sql) self.assertEqual(sorted(t2.get_rows()), rows[1:]) # Test Client.query sql = "SELECT * FROM {} WHERE a > 2".format(t1_qname) results = client.query(sql) self.assertEqual(sorted(results.rows), rows[2:]) # Test Client.query_into_dataframe sql = "SELECT * FROM {} WHERE a > 2".format(t1_qname) df = client.query_into_dataframe(sql) self.assertEqual(df.values.tolist(), [list(rows[2])]) # Test TableExporter.export_to_storage and # TableExporter.download_from_storage_and_unzip t1_exporter = TableExporter(t1, self.storage_prefix + "test_table-") t1_exporter.export_to_storage() with tempfile.NamedTemporaryFile(mode="r+") as f: t1_exporter.download_from_storage_and_unzip(f) f.seek(0) reader = csv.reader(f) data = [next(reader)] + sorted(reader) self.assertEqual(data, [list(map(str, row)) for row in [headers] + rows]) # Test Table.insert_rows_from_storage storage_path = self.storage_prefix + "test_table.csv" self.upload_to_storage("gcutils/tests/test_table.csv", storage_path) t2.insert_rows_from_storage(storage_path) self.assertEqual(sorted(t2.get_rows()), rows) # Test Client.create_storage_backed_table storage_path = self.storage_prefix + "test_table_headers.csv" self.upload_to_storage("gcutils/tests/test_table_headers.csv", storage_path) schema = build_schema(("a", "INTEGER"), ("b", "STRING")) t3 = client.create_storage_backed_table("t3", schema, storage_path) results = client.query("SELECT * FROM {}".format(t3.qualified_name)) self.assertEqual(sorted(results.rows), rows) self.upload_to_storage("gcutils/tests/test_table_headers_2.csv", storage_path) results = client.query("SELECT * FROM {}".format(t3.qualified_name)) self.assertEqual(sorted(results.rows), rows + [(4, "damson")]) # Test Client.create_table_with_view sql = "SELECT * FROM {{project}}.{} WHERE a > 1".format(t1_qname) t4 = client.create_table_with_view("t4", sql, False) results = client.query("SELECT * FROM {}".format(t4.qualified_name)) self.assertEqual(sorted(results.rows), rows[1:]) # Test Table.copy_to_new_dataset t1.copy_to_new_dataset("archive") t1_archived = archive_client.get_table("t1") self.assertEqual(sorted(t1_archived.get_rows()), rows) self.assertEqual(sorted(t1.get_rows()), rows) # Test Table.move_to_new_dataset t2.move_to_new_dataset("archive") t2_archived = archive_client.get_table("t2") self.assertEqual(sorted(t2_archived.get_rows()), rows) with self.assertRaises(NotFound): list(t2.get_rows()) # Test Client.insert_rows_from_pg PCT.objects.create(code="ABC", name="CCG 1") PCT.objects.create(code="XYZ", name="CCG 2") def transformer(row): return [ord(row[0][0]), row[1]] t1.insert_rows_from_pg( PCT, build_schema(("code", "INTEGER"), ("name", "STRING")), transformer=transformer, ) self.assertEqual(sorted(t1.get_rows()), [(65, "CCG 1"), (88, "CCG 2")]) # Test Table.delete_all_rows t1.delete_all_rows() self.assertEqual(list(t1.get_rows()), [])
def setUpTestData(cls): bassetlaw = PCT.objects.create(code="02Q", org_type="CCG") lincs_west = PCT.objects.create(code="04D", org_type="CCG") lincs_east = PCT.objects.create(code="03T", org_type="CCG", open_date="2013-04-01", close_date="2015-01-01") Chemical.objects.create(bnf_code="0703021Q0", chem_name="Desogestrel") Chemical.objects.create(bnf_code="0408010A0", chem_name="Levetiracetam") Practice.objects.create(code="C84001", ccg=bassetlaw, name="LARWOOD SURGERY", setting=4) Practice.objects.create(code="C84024", ccg=bassetlaw, name="NEWGATE MEDICAL GROUP", setting=4) Practice.objects.create( code="B82005", ccg=bassetlaw, name="PRIORY MEDICAL GROUP", setting=4, open_date="2015-01-01", ) Practice.objects.create(code="B82010", ccg=bassetlaw, name="RIPON SPA SURGERY", setting=4) Practice.objects.create(code="A85017", ccg=bassetlaw, name="BEWICK ROAD SURGERY", setting=4) Practice.objects.create(code="A86030", ccg=bassetlaw, name="BETTS AVENUE MEDICAL GROUP", setting=4) Practice.objects.create(code="C83051", ccg=lincs_west, name="ABBEY MEDICAL PRACTICE", setting=4) Practice.objects.create(code="C83019", ccg=lincs_east, name="BEACON MEDICAL PRACTICE", setting=4) # Ensure we only include open practices in our calculations. Practice.objects.create( code="B82008", ccg=bassetlaw, name="NORTH SURGERY", setting=4, open_date="2010-04-01", close_date="2012-01-01", ) # Ensure we only include standard practices in our calculations. Practice.objects.create( code="Y00581", ccg=bassetlaw, name="BASSETLAW DRUG & ALCOHOL SERVICE", setting=1, ) fixtures_base_path = os.path.join("frontend", "tests", "fixtures", "commands") client = Client("hscic") prescribing_fixture_path = os.path.join( fixtures_base_path, "prescribing_bigquery_fixture.csv") table = client.get_or_create_table("normalised_prescribing_standard", bq_schemas.PRESCRIBING_SCHEMA) table.insert_rows_from_csv(prescribing_fixture_path, bq_schemas.PRESCRIBING_SCHEMA) practices_fixture_path = os.path.join(fixtures_base_path, "practices.csv") table = client.get_or_create_table("practices", bq_schemas.PRACTICE_SCHEMA) table.insert_rows_from_csv(practices_fixture_path, bq_schemas.PRACTICE_SCHEMA) tariff_path = os.path.join(fixtures_base_path, "tariff_fixture.csv") table = client.get_or_create_table("tariff", bq_schemas.TARIFF_SCHEMA) table.insert_rows_from_csv(tariff_path, bq_schemas.TARIFF_SCHEMA) bnf_path = os.path.join(fixtures_base_path, "bnf_codes_for_ppu_savings.csv") table = client.get_or_create_table("bnf", bq_schemas.BNF_SCHEMA) table.insert_rows_from_csv(bnf_path, bq_schemas.BNF_SCHEMA) month = date(2015, 9, 1) dummy_substitutions = pd.read_csv( os.path.join(fixtures_base_path, "ppu_substitutions.csv")) with patch( "frontend.management.commands.import_ppu_savings.pd.read_csv", return_value=dummy_substitutions, ): import_ppu_savings.Command().handle(month=month)
def setUp(self): client = BQClient('hscic') table = client.get_or_create_table('prescribing', PRESCRIBING_SCHEMA)
def handle(self, *args, **kwargs): s = requests.Session() # Get URLs keyed by the date (year-month) they're for urls = set(self.iter_dataset_urls(s)) urls_by_month = dict(self.iter_months(urls)) # set up the BigQuery client, dataset, and table client = Client(dataset_key="scmd") self.ensure_dataset_exists(client) table = client.get_or_create_table("scmd", schema=SCHEMA) # look for existing months in BigQuery sql = "SELECT DISTINCT year_month FROM {};".format( table.qualified_name) known_dates = [r[0] for r in client.query(sql).rows] # convert the datetime.dates the query gives us to strings since # that's what we're dealing with elsewhere. known_months = {d.strftime("%Y-%m") for d in known_dates} print(known_months) missing_months = set(urls_by_month.keys()) - known_months print("Missing months: {}".format(", ".join(sorted(missing_months)))) pending_urls = {m: urls_by_month[m] for m in missing_months} # abort if there's nothing to get if not pending_urls: print("no pending urls, aborting") return # grab missing months for month, url in sorted(pending_urls.items()): print("{} | Getting: {}".format(month, url)) r = s.get(url) r.raise_for_status() print("{} | Downloaded: {}".format(month, url)) # read CSV into memory decoded_content = r.content.decode("utf-8") reader = csv.reader(decoded_content.splitlines(), delimiter=",") # remove headers next(reader) # use a tempfile so we can write the CSV to disk with necessary # changes (adding days to the year-month dates at time of writing), # before uploading from that file to BigQuery. with tempfile.NamedTemporaryFile(mode="w+") as f: writer = csv.writer(f, delimiter=",") for line in reader: # Convert year-month dates to year-month-day line[0] = line[0] + "-01" writer.writerow(line) print("{} | Wrote: {}".format(month, f.name)) # rewind the file so we can read it into BigQuery f.seek(0) # insert into BigQuery table.insert_rows_from_csv(f.name, SCHEMA, write_disposition="WRITE_APPEND") print("{} | Ingested into BigQuery".format(month))
def test_the_lot(self): client = Client('test') schema = build_schema( ('a', 'INTEGER'), ('b', 'STRING'), ) headers = ['a', 'b'] rows = [ (1, 'apple'), (2, 'banana'), (3, 'coconut'), ] t1 = client.get_or_create_table('t1', schema) t1_qname = t1.qualified_name # Test Table.insert_rows_from_csv t1.insert_rows_from_csv('gcutils/tests/test_table.csv') self.assertEqual(sorted(t1.get_rows()), rows) # Test Table.insert_rows_from_query t2 = client.get_table('t2') sql = 'SELECT * FROM {} WHERE a > 1'.format(t1_qname) t2.insert_rows_from_query(sql) self.assertEqual(sorted(t2.get_rows()), rows[1:]) # Test Client.query sql = 'SELECT * FROM {} WHERE a > 2'.format(t1_qname) results = client.query(sql) self.assertEqual(sorted(results.rows), rows[2:]) # Test Client.query_into_dataframe sql = 'SELECT * FROM {} WHERE a > 2'.format(t1_qname) df = client.query_into_dataframe(sql) self.assertEqual(df.values.tolist(), [list(rows[2])]) # Test TableExporter.export_to_storage and # TableExporter.download_from_storage_and_unzip t1_exporter = TableExporter(t1, self.storage_prefix + 'test_table-') t1_exporter.export_to_storage() with tempfile.NamedTemporaryFile(mode='r+') as f: t1_exporter.download_from_storage_and_unzip(f) f.seek(0) reader = csv.reader(f) data = [reader.next()] + sorted(reader) self.assertEqual(data, [map(str, row) for row in [headers] + rows]) # Test Table.insert_rows_from_storage storage_path = self.storage_prefix + 'test_table.csv' self.upload_to_storage('gcutils/tests/test_table.csv', storage_path) t2.insert_rows_from_storage(storage_path) self.assertEqual(sorted(t2.get_rows()), rows) # Test Client.create_storage_backed_table storage_path = self.storage_prefix + 'test_table_headers.csv' self.upload_to_storage( 'gcutils/tests/test_table_headers.csv', storage_path ) schema = [ {'name': 'a', 'type': 'integer'}, {'name': 'b', 'type': 'string'}, ] t3 = client.create_storage_backed_table( 't3', schema, storage_path ) results = client.query('SELECT * FROM {}'.format(t3.qualified_name)) self.assertEqual(sorted(results.rows), rows) self.upload_to_storage( 'gcutils/tests/test_table_headers_2.csv', storage_path ) results = client.query('SELECT * FROM {}'.format(t3.qualified_name)) self.assertEqual(sorted(results.rows), rows + [(4, u'damson')]) # Test Client.create_table_with_view sql = 'SELECT * FROM {{project}}.{} WHERE a > 1'.format(t1_qname) t4 = client.create_table_with_view('t4', sql, False) results = client.query('SELECT * FROM {}'.format(t4.qualified_name)) self.assertEqual(sorted(results.rows), rows[1:]) # Test Client.insert_rows_from_pg PCT.objects.create(code='ABC', name='CCG 1') PCT.objects.create(code='XYZ', name='CCG 2') def transformer(row): return [ord(row[0][0]), row[1]] t1.insert_rows_from_pg(PCT, ['code', 'name'], transformer) self.assertEqual(sorted(t1.get_rows()), [(65, 'CCG 1'), (88, 'CCG 2')]) # Test Table.delete_all_rows t1.delete_all_rows() self.assertEqual(list(t1.get_rows()), [])
def _createData(cls): bassetlaw = PCT.objects.create(code='02Q', org_type='CCG') lincs_west = PCT.objects.create(code='04D', org_type='CCG') lincs_east = PCT.objects.create(code='03T', org_type='CCG', open_date='2013-04-01', close_date='2015-01-01') Chemical.objects.create(bnf_code='0703021Q0', chem_name='Desogestrel') Chemical.objects.create(bnf_code='0408010A0', chem_name='Levetiracetam') Practice.objects.create(code='C84001', ccg=bassetlaw, name='LARWOOD SURGERY', setting=4) Practice.objects.create(code='C84024', ccg=bassetlaw, name='NEWGATE MEDICAL GROUP', setting=4) Practice.objects.create(code='B82005', ccg=bassetlaw, name='PRIORY MEDICAL GROUP', setting=4, open_date='2015-01-01') Practice.objects.create(code='B82010', ccg=bassetlaw, name='RIPON SPA SURGERY', setting=4) Practice.objects.create(code='A85017', ccg=bassetlaw, name='BEWICK ROAD SURGERY', setting=4) Practice.objects.create(code='A86030', ccg=bassetlaw, name='BETTS AVENUE MEDICAL GROUP', setting=4) Practice.objects.create(code='C83051', ccg=lincs_west, name='ABBEY MEDICAL PRACTICE', setting=4) Practice.objects.create(code='C83019', ccg=lincs_east, name='BEACON MEDICAL PRACTICE', setting=4) # Ensure we only include open practices in our calculations. Practice.objects.create(code='B82008', ccg=bassetlaw, name='NORTH SURGERY', setting=4, open_date='2010-04-01', close_date='2012-01-01') # Ensure we only include standard practices in our calculations. Practice.objects.create(code='Y00581', ccg=bassetlaw, name='BASSETLAW DRUG & ALCOHOL SERVICE', setting=1) args = [] if 'SKIP_BQ_LOAD' not in os.environ: fixtures_path = os.path.join( 'frontend', 'tests', 'fixtures', 'commands') prescribing_fixture_path = os.path.join( fixtures_path, 'prescribing_bigquery_fixture.csv' ) # TODO Make this a table with a view (see # generate_presentation_replacements), and put it in the correct # dataset ('hscic', not 'measures'). client = Client('measures') table = client.get_or_create_table( 'normalised_prescribing_legacy', PRESCRIBING_SCHEMA ) table.insert_rows_from_csv(prescribing_fixture_path) practices_fixture_path = os.path.join( fixtures_path, 'practices.csv' ) client = Client('hscic') table = client.get_or_create_table('practices', PRACTICE_SCHEMA) columns = [field.name for field in PRACTICE_SCHEMA] table.insert_rows_from_csv(practices_fixture_path) ccgs_fixture_path = os.path.join( fixtures_path, 'ccgs.csv' ) table = client.get_or_create_table('ccgs', CCG_SCHEMA) table.insert_rows_from_csv(ccgs_fixture_path) month = '2015-09-01' measure_id = 'cerazette' args = [] opts = { 'month': month, 'measure': measure_id, 'v': 3 } with patch('frontend.management.commands.import_measures' '.parse_measures', new=MagicMock(return_value=test_measures())): call_command('import_measures', *args, **opts)
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())
def setUpTestData(cls): regional_team_54 = RegionalTeam.objects.create(code='Y54') regional_team_55 = RegionalTeam.objects.create(code='Y55') stp_1 = STP.objects.create(ons_code='E00000001') stp_2 = STP.objects.create(ons_code='E00000002') bassetlaw = PCT.objects.create(code='02Q', org_type='CCG', stp=stp_1, regional_team=regional_team_54) lincs_west = PCT.objects.create(code='04D', org_type='CCG', stp=stp_2, regional_team=regional_team_55) lincs_east = PCT.objects.create(code='03T', org_type='CCG', open_date='2013-04-01', close_date='2015-01-01', stp=stp_2, regional_team=regional_team_55) Chemical.objects.create(bnf_code='0703021Q0', chem_name='Desogestrel') Chemical.objects.create(bnf_code='0408010A0', chem_name='Levetiracetam') Practice.objects.create(code='C84001', ccg=bassetlaw, name='LARWOOD SURGERY', setting=4) Practice.objects.create(code='C84024', ccg=bassetlaw, name='NEWGATE MEDICAL GROUP', setting=4) Practice.objects.create(code='B82005', ccg=bassetlaw, name='PRIORY MEDICAL GROUP', setting=4, open_date='2015-01-01') Practice.objects.create(code='B82010', ccg=bassetlaw, name='RIPON SPA SURGERY', setting=4) Practice.objects.create(code='A85017', ccg=bassetlaw, name='BEWICK ROAD SURGERY', setting=4) Practice.objects.create(code='A86030', ccg=bassetlaw, name='BETTS AVENUE MEDICAL GROUP', setting=4) Practice.objects.create(code='C83051', ccg=lincs_west, name='ABBEY MEDICAL PRACTICE', setting=4) Practice.objects.create(code='C83019', ccg=lincs_east, name='BEACON MEDICAL PRACTICE', setting=4) # Ensure we only include open practices in our calculations. Practice.objects.create(code='B82008', ccg=bassetlaw, name='NORTH SURGERY', setting=4, open_date='2010-04-01', close_date='2012-01-01') # Ensure we only include standard practices in our calculations. Practice.objects.create(code='Y00581', ccg=bassetlaw, name='BASSETLAW DRUG & ALCOHOL SERVICE', setting=1) measure = Measure.objects.create( id='cerazette', name='Cerazette vs. Desogestrel', title='Prescribing of...', tags=['core'], numerator_bnf_codes=[], ) # We expect this MeasureValue to be deleted because it is older than # five years. MeasureValue.objects.create( measure=measure, pct_id='02Q', month='2000-01-01', ) # We expect this MeasureValue to be unchanged MeasureValue.objects.create( measure=measure, pct_id='02Q', month='2015-08-01', ) # We expect this MeasureValue to be updated MeasureValue.objects.create( measure=measure, pct_id='02Q', month='2015-09-01', ) ImportLog.objects.create( category='prescribing', current_at='2018-04-01', filename='/tmp/prescribing.csv', ) if 'SKIP_BQ_LOAD' in os.environ: assert 'BQ_NONCE' in os.environ, "Specify BQ_NONCE to reuse fixtures" if 'SKIP_BQ_LOAD' not in os.environ: fixtures_path = os.path.join('frontend', 'tests', 'fixtures', 'commands') prescribing_fixture_path = os.path.join( fixtures_path, 'prescribing_bigquery_fixture.csv') # TODO Make this a table with a view (see # generate_presentation_replacements), and put it in the correct # dataset ('hscic', not 'measures'). client = Client('measures') table = client.get_or_create_table('normalised_prescribing_legacy', PRESCRIBING_SCHEMA) table.insert_rows_from_csv(prescribing_fixture_path) practices_fixture_path = os.path.join(fixtures_path, 'practices.csv') client = Client('hscic') table = client.get_or_create_table('practices', PRACTICE_SCHEMA) table.insert_rows_from_csv(practices_fixture_path) ccgs_fixture_path = os.path.join(fixtures_path, 'ccgs.csv') table = client.get_or_create_table('ccgs', CCG_SCHEMA) table.insert_rows_from_csv(ccgs_fixture_path) opts = {'month': '2015-09-01', 'measure': 'cerazette', 'v': 3} with patch(MODULE + '.get_measure_definition_paths', new=working_measure_files): call_command('import_measures', **opts)