"""Utility functions to support snowflake connection in tests.""" from collections import OrderedDict from datetime import datetime, timedelta from snowflake_connector.snowflake_conn import ( get_session, set_default_sessionmaker, ) from reporting.constants.physical_reporting import ( CA_PLANT_CODE, CA_SUPPLY_CHAIN, US_PLANT_CODE, US_SUPPLY_CHAIN, ) from reporting.models.ca_physical_product_view import CAPhysicalProductView from reporting.models.ca_physical_retailer_view import CAPhysicalRetailerView from reporting.models.uk_physical_product_view import UKPhysicalProductView from reporting.models.us_physical_product_view import USPhysicalProductView from reporting.models.us_physical_product_view_coop import ( USPhysicalProductViewCoop, ) from reporting.models.us_physical_retailer_view import USPhysicalRetailerView from reporting.models.us_physical_retailer_view_coop import ( USPhysicalRetailerViewCoop, ) from tests.consts import snowflake as snowflake_constants from tests.utils import queries def execute_sql(*args): """Run a series of queries safely within a test schema.""" with get_session() as session: for query in args: session.execute(query) def create_snowflake_test_setup(): """Initialize test schema in snowflake and set config to use.""" set_default_sessionmaker( { 'database': snowflake_constants.TEST_DATABASE, 'schema': snowflake_constants.TEST_SCHEMA, } ) execute_sql( queries.DROP_UNIQUE_TEST_SCHEMA, queries.CREATE_UNIQUE_TEST_SCHEMA ) set_default_sessionmaker( { 'database': snowflake_constants.TEST_DATABASE, 'schema': snowflake_constants.UNIQUE_TEST_SCHEMA, } ) def us_ca_data( local_product_cd, test_id, vendor_id, subaccount_id, supply_chain_id ): """Canada/US Data.""" data = generic_data(test_id, vendor_id, subaccount_id) data.update( { 'local_product_cd': '{}'.format(local_product_cd), 'supply_chain_id': supply_chain_id, 'article_no': '{}'.format(test_id), 'wholesale_price': 3, 'exclusive_for': 'For me', 'orchard_price': '11.20', 'open_orders': 1, 'boxlot': 30, 'backorders': 1, 'day_1_s': 1, 'day_1_r': 1, 'day_1_s_dollars': 1, 'day_1_r_dollars': 1, 'five_day_s': 1, 'five_day_r': 1, 'five_day_s_dollars': 1, 'five_day_r_dollars': 1, 'mtds': 1, 'mtdr': 1, 'mtds_dollars': 1, 'mtdr_dollars': 1, 'three_month_s': 1, 'three_month_r': 1, 'three_month_s_dollars': 1, 'three_month_r_dollars': 1, 'twelve_month_s': 1, 'twelve_month_r': 1, 'twelve_month_s_dollars': 1, 'twelve_month_r_dollars': 1, 'twenty_four_month_s': 1, 'twenty_four_month_r': 1, 'twenty_four_month_s_dollars': 1, 'twenty_four_month_r_dollars': 1, 'cytds': 1, 'cytdr': 1, 'cytds_dollars': 1, 'cytdr_dollars': 1, 'rtds': 1, 'rtdr': 1, 'rtds_dollars': 1, 'rtdr_dollars': 1, 'on_hand': 0, 'available': 0, 'purchase_orders': 0, 'potential': 0, 'returns_in_process': 0, 'on_hold': 0, 'product_status': 'Active Catalog{}'.format(test_id), 'returnability': 'Y', 'returns_disposition': 'S', 'returns_disposition_override': 'S', } ) if supply_chain_id == US_SUPPLY_CHAIN: data.update( { 'is_generic_product': 0, 'open_scrap': 1, 'overstock_12_month_qt': 2, 'overstock_24_month_qt': 3, } ) return data def us_data_coop( local_product_cd, test_id, vendor_id, subaccount_id, supply_chain_id ): """US Product View Coop Data.""" data = us_ca_data( local_product_cd, test_id, vendor_id, subaccount_id, supply_chain_id ) data['coop_expired_amount'] = 1 data['coop_open_amount'] = 2 data['coop_closed_amount'] = 3 return data def uk_data(number, vendor_id, subaccount_id): """UK Data.""" data = generic_data(number, vendor_id, subaccount_id) data.update( { 'on_hand': 0, 'available': 1, 'allocated': 1, 'faulty': 1, 'consignment': 1, } ) return data def generic_data(number, vendor_id, subaccount_id): """Metadata used by all product view.""" return { 'vendor_id': vendor_id, 'subacct_id': subaccount_id, 'artist': 'Artist{}'.format(number), 'product_name': 'Product Name{}'.format(number), 'label_name': 'Label{}'.format(number), 'sub_label': 'SubLabel{}'.format(number), 'genre': 'Genre{}'.format(number), 'sub_genre': 'SubGenre{}'.format(number), 'product_code': 'ProductCode{}'.format(number), 'upc_ean': '{}'.format(number), 'release_date': datetime(2016, 1, number), 'product_type': 'CD{}'.format(number), 'product_format': 'Album', 'units_per_set': 1, 'display_configuration': '1 x CD Album', 'exclusive': 'N', } def retailer_data( number='1', vendor_id='1', subaccount_id='1', retailer='Retailer1', retailer_code='1', ): """Retailer generic data.""" return { 'local_product_cd': '77712{}'.format(number), 'artist': 'Artist{}'.format(number), 'product_name': 'ProductName{}'.format(number), 'label_name': 'Label{}'.format(number), 'sub_label': 'SubLabel{}'.format(number), 'product_code': 'ProductCode{}'.format(number), 'upc_ean': '{}'.format(number), 'release_date': datetime(2016, 1, number), 'product_type': 'CD{}'.format(number), 'product_format': 'Album', 'units_per_set': 1, 'display_configuration': '1 x CD Album', 'exclusive': 'N', 'retailer': retailer, 'retailer_code': retailer_code, 'open_orders': 1, 'backorders': 1, 'first_4_week_s': 1, 'five_day_s': 1, 'five_day_r': 1, 'last_4_week_s': 1, 'last_4_week_r': 1, 'mtds': 1, 'mtdr': 1, 'cytds': 1, 'cytdr': 1, 'cumtds': 1, 'cumtdr': 1, 'article_no': '{}'.format(number), 'vendor_id': vendor_id, 'subacct_id': subaccount_id, } def us_retailer_data(**args): """US Retailer data.""" data = retailer_data(**args) data.update({'is_generic_product': 0}) return data def us_retailer_data_coop(**args): """US Retailer Coop data.""" data = us_retailer_data(**args) data.update( { 'coop_expired_amount': 1, 'coop_open_amount': 2, 'coop_closed_amount': 3, } ) return data def physical_reporting_rtd_data(): """RTD data with open orders for physical reporting top open orders tests. Matches the metadata row: local_product_cd='709422', vendor_id=21945. order_qt > 0 so the top open orders query's WHERE s.order_qt <> 0 includes this row. """ data = OrderedDict(product_rtd_data()) data['order_qt'] = 500 data['order_am'] = 4500.00 data['order_us_am'] = 4500.00 return data def physical_reporting_yesterday_shipments_rtd_data(): """RTD data with yesterday shipments for top yesterday shipments tests. Matches the metadata row: local_product_cd='709422', vendor_id=21945. day1_ship_qt > 0 so the top yesterday shipments query's WHERE s.day1_ship_qt <> 0 includes this row. """ data = OrderedDict(product_rtd_data()) data['day1_ship_qt'] = 150 data['day1_ship_am'] = 1200.00 data['day1_ship_us_am'] = 1200.00 return data def physical_reporting_mtd_shipments_rtd_data(): """RTD data with MTD shipments for top MTD shipments tests. Matches the metadata row: local_product_cd='709422', vendor_id=21945. mtd_ship_qt > 0 so the top MTD shipments query's WHERE s.mtd_ship_qt <> 0 includes this row. """ data = OrderedDict(product_rtd_data()) data['mtd_ship_qt'] = 200 data['mtd_ship_am'] = 1800.00 data['mtd_ship_us_am'] = 1800.00 return data def physical_reporting_label_subaccount_summary_rtd_data(): """RTD data for label/subaccount summary tests. Matches the metadata row: local_product_cd='709422', vendor_id=21945. The label/subaccount summary query has no quantity filter; all rows for the vendor are included in the aggregated result. mtd_ship_qt is zeroed out to avoid affecting the top MTD shipments test. """ data = OrderedDict(product_rtd_data()) data['mtd_ship_qt'] = 0 data['mtd_ship_am'] = 0 data['mtd_ship_us_am'] = 0 return data def physical_reporting_product_detail_rtd_data(): """RTD data for product detail tests. Matches the metadata row: local_product_cd='709422', vendor_id=21945. The product detail query has no quantity filter; all rows for the vendor are included in the aggregated result. All fields used by the product detail query are zeroed out to avoid affecting the label/subaccount summary and other physical reporting tests. """ data = OrderedDict(product_rtd_data()) data['mtd_ship_qt'] = 0 data['mtd_ship_am'] = 0 data['mtd_ship_us_am'] = 0 data['cytd_ship_qt'] = 0 data['cytd_ship_am'] = 0 data['cytd_ship_us_am'] = 0 data['cytd_return_qt'] = 0 data['cytd_return_am'] = 0 data['cytd_return_us_am'] = 0 data['rtd_ship_qt'] = 0 data['rtd_ship_am'] = 0 data['rtd_ship_us_am'] = 0 data['rtd_return_qt'] = 0 data['rtd_return_am'] = 0 data['rtd_return_us_am'] = 0 return data def product_rtd_data(): """Product RTD data.""" return OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', '709422'), ('first_4week_qt', 1200), ('second_4week_qt', 16), ('day1_ship_qt', 0), ('day1_ship_am', 0), ('day1_ship_us_am', 0), ('day1_return_qt', 0), ('day1_return_am', 0), ('day1_return_us_am', 0), ('day2_ship_qt', 0), ('day2_ship_am', 0), ('day2_ship_us_am', 0), ('day2_return_qt', 0), ('day2_return_am', 0), ('day2_return_us_am', 0), ('day3_ship_qt', 2), ('day3_ship_am', 14.94), ('day3_ship_us_am', 14.94), ('day3_return_qt', 0), ('day3_return_am', 0), ('day3_return_us_am', 0), ('day4_ship_qt', 0), ('day4_ship_am', 0), ('day4_ship_us_am', 0), ('day4_return_qt', 0), ('day4_return_am', 0), ('day4_return_us_am', 0), ('day5_ship_qt', 0), ('day5_ship_am', 0), ('day5_ship_us_am', 0), ('day5_return_qt', 0), ('day5_return_am', 0), ('day5_return_us_am', 0), ('week1_ship_qt', 0), ('week1_ship_am', 0), ('week1_ship_us_am', 0), ('week1_return_qt', 0), ('week1_return_am', 0), ('week1_return_us_am', 0), ('week2_ship_qt', 5), ('week2_ship_am', 38.88), ('week2_ship_us_am', 38.88), ('week2_return_qt', 0), ('week2_return_am', 0), ('week2_return_us_am', 0), ('week3_ship_qt', 0), ('week3_ship_am', 0), ('week3_ship_us_am', 0), ('week3_return_qt', 0), ('week3_return_am', 0), ('week3_return_us_am', 0), ('week4_ship_qt', 3), ('week4_ship_am', 25.47), ('week4_ship_us_am', 25.47), ('week4_return_qt', -1), ('week4_return_am', -9), ('week4_return_us_am', -9), ('mtd_ship_qt', 2), ('mtd_ship_am', 14.94), ('mtd_ship_us_am', 14.94), ('mtd_return_qt', 0), ('mtd_return_am', 0), ('mtd_return_us_am', 0), ('ytd_ship_qt', 59), ('ytd_ship_am', 489.69), ('ytd_ship_us_am', 489.69), ('ytd_return_qt', -15), ('ytd_return_am', -133.47), ('ytd_return_us_am', -133.47), ('cytd_ship_qt', 10), ('cytd_ship_am', 79.29), ('cytd_ship_us_am', 79.29), ('cytd_return_qt', -1), ('cytd_return_am', -9), ('cytd_return_us_am', -9), ('last_12_mtd_ship_qt', 61), ('last_12_mtd_ship_am', 507.69), ('last_12_mtd_ship_us_am', 507.69), ('last_12_mtd_return_qt', -16), ('last_12_mtd_return_am', -142.47), ('last_12_mtd_return_us_am', -142.47), ('last_24_mtd_ship_qt', 172), ('last_24_mtd_ship_am', 1471.5), ('last_24_mtd_ship_us_am', 1471.5), ('last_24_mtd_return_qt', -87), ('last_24_mtd_return_am', -753.12), ('last_24_mtd_return_us_am', -753.12), ('last_12_mth_ship_qt', 68), ('last_12_mth_ship_am', 573.75), ('last_12_mth_ship_us_am', 573.75), ('last_12_mth_return_qt', -28), ('last_12_mth_return_am', -249.57), ('last_12_mth_return_us_am', -249.57), ('last_24_mth_ship_qt', 202), ('last_24_mth_ship_am', 1706.94), ('last_24_mth_ship_us_am', 1706.94), ('last_24_mth_return_qt', -106), ('last_24_mth_return_am', -919.14), ('last_24_mth_return_us_am', -919.14), ('rtd_ship_qt', 1699), ('rtd_ship_am', 14475.06), ('rtd_ship_us_am', 14475.06), ('rtd_return_qt', -279), ('rtd_return_am', -2327.04), ('rtd_return_us_am', -2327.04), ('prior_yr_ship_qt', 112), ('prior_yr_ship_am', 972.81), ('prior_yr_ship_us_am', 972.81), ('prior_yr_return_qt', -69), ('prior_yr_return_am', -593.1), ('prior_yr_return_us_am', -593.1), ('prior_cyr_ship_qt', 66), ('prior_cyr_ship_am', 563.4), ('prior_cyr_ship_us_am', 563.4), ('prior_cyr_return_qt', -28), ('prior_cyr_return_am', -249.57), ('prior_cyr_return_us_am', -249.57), ('order_qt', 0), ('order_am', 0), ('order_us_am', 0), ('back_order_qt', 0), ('back_order_am', 0), ('back_order_us_am', 0), ] ) def current_period_data(): """Return the current period reporting date.""" return OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('current_reporting_dt', '2018-02-01'), ] ) def single_product_monthly_data(): """Single product monthly sales data.""" test_data = [] for x in range(3): local_product_cd = str(709422 + x) test_data.extend( ( OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2018-01-01'), ('ship_qt', 17), ('ship_am', 139.54), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-12-01'), ('ship_qt', 30), ('ship_am', 223.6), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-11-01'), ('ship_qt', 24), ('ship_am', 170.47), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-10-01'), ('ship_qt', 30), ('ship_am', 194.16), ('ship_us_am', 139.54), ('return_qt', -20), ('return_am', -185.08), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-09-01'), ('ship_qt', 10), ('ship_am', 78.67), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-08-01'), ('ship_qt', 27), ('ship_am', 204.86), ('ship_us_am', 139.54), ('return_qt', -1), ('return_am', -8.72), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-07-01'), ('ship_qt', 38), ('ship_am', 314.4), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-06-01'), ('ship_qt', 26), ('ship_am', 214.02), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-05-01'), ('ship_qt', 21), ('ship_am', 186.29), ('ship_us_am', 139.54), ('return_qt', -2), ('return_am', -18.41), ('return_us_am', -18.41), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-04-01'), ('ship_qt', 33), ('ship_am', 255.15), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', -18.41), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-03-01'), ('ship_qt', 27), ('ship_am', 224.02), ('ship_us_am', 139.54), ('return_qt', -3), ('return_am', -28.1), ('return_us_am', -18.41), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-02-01'), ('ship_qt', 27), ('ship_am', 195.03), ('ship_us_am', 139.54), ('return_qt', -1), ('return_am', -5.81), ('return_us_am', -18.41), ('coop_generic_product_in', 0), ('coop_expired_label_am', 1.20), ('coop_open_label_am', 2.50), ('coop_closed_label_am', 3.00), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2018-01-01'), ('ship_qt', 17), ('ship_am', 139.54), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-12-01'), ('ship_qt', 30), ('ship_am', 223.6), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-11-01'), ('ship_qt', 24), ('ship_am', 170.47), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-10-01'), ('ship_qt', 30), ('ship_am', 194.16), ('ship_us_am', 139.54), ('return_qt', -20), ('return_am', -185.08), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-09-01'), ('ship_qt', 10), ('ship_am', 78.67), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-08-01'), ('ship_qt', 27), ('ship_am', 204.86), ('ship_us_am', 139.54), ('return_qt', -1), ('return_am', -8.72), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-07-01'), ('ship_qt', 38), ('ship_am', 314.4), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-06-01'), ('ship_qt', 26), ('ship_am', 214.02), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', 0), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-05-01'), ('ship_qt', 21), ('ship_am', 186.29), ('ship_us_am', 139.54), ('return_qt', -2), ('return_am', -18.41), ('return_us_am', -18.41), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-04-01'), ('ship_qt', 33), ('ship_am', 255.15), ('ship_us_am', 139.54), ('return_qt', 0), ('return_am', 0), ('return_us_am', -18.41), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-03-01'), ('ship_qt', 27), ('ship_am', 224.02), ('ship_us_am', 139.54), ('return_qt', -3), ('return_am', -28.1), ('return_us_am', -18.41), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), OrderedDict( [ ('supply_chain_id', CA_SUPPLY_CHAIN), ('local_product_cd', local_product_cd), ('reporting_dt', '2017-02-01'), ('ship_qt', 27), ('ship_am', 195.03), ('ship_us_am', 139.54), ('return_qt', -1), ('return_am', -5.81), ('return_us_am', -18.41), ('coop_generic_product_in', 0), ('coop_expired_label_am', 0), ('coop_open_label_am', 0), ('coop_closed_label_am', 0), ] ), ) ) return test_data def eu_point_of_sale_by_country_data(): """Point of sale by country data.""" test_data = [] vendor_id = 21989 subacct_id = 7551 label_name = 'Thirty Tigers' sub_label = 'Captain Potato' country = 'France' first_day_of_release_week = datetime.strptime('2018-08-03', '%Y-%m-%d') for x in range(3): weekly_scans = x all_time_scans = 10 + x test_data.append( OrderedDict( [ ('vendor_id', vendor_id), ('subacct_id', subacct_id), ('"Label Name"', label_name), ('"Sub Label"', sub_label), ('"Country"', country), ( '"First Day of Release Week"', datetime.strftime( first_day_of_release_week + timedelta(weeks=x), '%Y-%m-%d', ), ), ('"Weekly Scans"', weekly_scans), ('"All Time Scans"', all_time_scans), ] ) ) return test_data def eu_point_of_sale_by_product_data(): """Point of sale by product data.""" test_data = [] vendor_id = 21989 subacct_id = 25037 label_name = 'Transmit Sound' sub_label = 'Transmit Sound' first_day_of_release_week = datetime.strptime('2018-08-03', '%Y-%m-%d') for x in range(3): weekly_scans = x all_time_scans = 10 + x test_data.append( OrderedDict( [ ('vendor_id', vendor_id), ('subacct_id', subacct_id), ('"Label Name"', label_name), ('"Sub Label"', sub_label), ('"Product Name"', 'Okemah and the Melody of Riot'), ('"Artist"', 'Son Volt'), ('"Label Name"', 'Transmit Sound'), ('"Sub Label"', 'Transmit Sound'), ('"Genre"', 'Rock'), ('"Sub Genre"', 'Americana'), ('"Product Code"', 'TS2019CD'), ('"UPC/EAN"', '752830541474'), ('"Release Date"', '2018-08-31'), ('"Product Type"', 'CD'), ('"Format"', 'Album'), ('"Units Per Set"', '2'), ('"Display Configuration"', '2 x CD Album'), ('"Exclusive"', 'N'), ('"Exclusive For"', ''), ('"Orchard Price"', '12.00'), ('"Wholesale Price"', '0'), ( '"First Day of Release Week"', datetime.strftime( first_day_of_release_week + timedelta(weeks=x), '%Y-%m-%d', ), ), ('"Weekly Scans"', weekly_scans), ('"All Time Scans"', all_time_scans), ] ) ) return test_data def eu_point_of_sale_by_product_country_data(): """Point of sale by product and country data.""" test_data = [] vendor_id = 21989 subacct_id = 25037 label_name = 'Transmit Sound' sub_label = 'Transmit Sound' first_day_of_release_week = datetime.strptime('2018-08-03', '%Y-%m-%d') for x in range(3): weekly_scans = x all_time_scans = 10 + x test_data.append( OrderedDict( [ ('vendor_id', vendor_id), ('subacct_id', subacct_id), ('"Label Name"', label_name), ('"Sub Label"', sub_label), ('"Product Name"', 'Okemah and the Melody of Riot'), ('"Artist"', 'Son Volt'), ('"Label Name"', 'Transmit Sound'), ('"Sub Label"', 'Transmit Sound'), ('"Genre"', 'Rock'), ('"Sub Genre"', 'Americana'), ('"Product Code"', 'TS2019CD'), ('"UPC/EAN"', '752830541474'), ('"Release Date"', '2018-08-31'), ('"Product Type"', 'CD'), ('"Format"', 'Album'), ('"Units Per Set"', '2'), ('"Display Configuration"', '2 x CD Album'), ('"Exclusive"', 'N'), ('"Exclusive For"', ''), ('"Orchard Price"', '12.00'), ('"Wholesale Price"', '0'), ('"Country"', 'France'), ( '"First Day of Release Week"', datetime.strftime( first_day_of_release_week + timedelta(weeks=x), '%Y-%m-%d', ), ), ('"Weekly Scans"', weekly_scans), ('"All Time Scans"', all_time_scans), ] ) ) return test_data def single_product_metadata_data(): """Single product metadata view data.""" return OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', '709422'), ('artist_nm', 'good riddance'), ('product_nm', 'peace in our time'), ('label_nm', 'fat wreck chords'), ('label_id', 21945), ('sublabel_nm', 'fat wreck chords'), ('genre_nm', 'punk'), ('subgenre_nm', 'punk'), ('product_cd', 709422), ('upc_cd', 751097094228), ('release_dt', '2015-04-21'), ('product_type', 'cd'), ('product_format', 'album'), ('units_per_set', 1), ('display_configuration', '1 x cd album'), ('status_cd', 31), ('status_nm', 'active catalog'), ('cutout_dt', '2015-04-21'), ('returns_allowed_in', 'y'), ('exclusive_product_in', 'n'), ('series_cd', 13.98), ('red_price_cd', 'cd13'), ('wholesales_pr', 9), ('gras_prod_no', 75109709422), ('inventory_category_cd', 7), ('profit_code', 'null'), ('article_no', 709422), ('fin_label_cd', 'd093'), ('fin_label_profit_cd', 'd093'), ('sap_profit_center_cd', 'us3443'), ('sap_company_cd', 2963), ('exclusive_for', ''), ('price_cd', 9), ('store_pricing_tier_nm', 'cd13'), ('vendor_id', 21945), ('subacct_id', 7483), ('release_id', 2151331), ] ) def single_product_inventory_data(): """Single product inventory data.""" return OrderedDict( [ ('supply_chain_id', US_SUPPLY_CHAIN), ('local_product_cd', '709422'), ('on_hand_qt', 248), ('available_qt', 248), ('all_order_qt', 0), ('rdo_backorder_qt', 0), ('fut_dated_qt', 0), ('open_po_qt', 0), ('rework_qt', 0), ('whse_rework_qt', 0), ('rip_qt', 38), ('ip_xfr_due_in_qt', 0), ('ip_in_tran_qt', 0), ('ip_xfr_due_out_qt', 0), ('hold_qt', 0), ('returns_disp_cd', 'k'), ('returns_disp_override_cd', 'n'), ('open_scrap_qt', 1), ('overstock_12_mtd_qt', 20), ('overstock_24_mtd_qt', 11), ] ) def get_plant_code(supply_chain_id): """Get plant code from supply chain.""" if supply_chain_id == US_SUPPLY_CHAIN: return US_PLANT_CODE else: return CA_PLANT_CODE def sap_perpetual_inventory_data(): """Sap perpetual inventory data.""" test_data = [] for data in single_product_monthly_data(): test_data.append( OrderedDict( [ ('supply_chain_id', data['supply_chain_id']), ('local_label_cd', ''), ('local_product_cd', data['local_product_cd']), ('plant', get_plant_code(data['supply_chain_id'])), ('period', ''), ('category_cde', ''), ('prefix', ''), ('selection', ''), ('suffix', ''), ('material_type', ''), ('ipc_rate', 0), ('unit_open_bal', 0), ('unit_receipts', 11), ('unit_gross_shp', 3), ('unit_gross_ret', 11), ('unit_ret_scrap', 2), ('unit_whs_scrap', 0), ('unit_shrinkage', 0), ('unit_rework', 0), ('unit_reval', 1), ('unit_other', 2), ('unit_total', 3), ('units_perset', ''), ('config_code', ''), ('artist', ''), ('title', ''), ('release_date', ''), ('sap_company', ''), ('label', ''), ('repertoire', ''), ('art_number', ''), ('art_prepack_ind', ''), ('alternate_catlg_id', ''), ('sony_bmg_item_in', ''), ('sap_map_code', ''), ('gas_grs_prefix', ''), ('gas_grs_core', ''), ('super_ref_no', ''), ('album_tie_in_ref_no', ''), ('project_id_ref_no', ''), ('license_type', ''), ('sap_company_code', ''), ('costed_flag', ''), ('sap_profit_center', ''), ('sap_profit_center_desc', ''), ('flor', ''), ('flpc', ''), ('flp', ''), ('reporting_dt', data['reporting_dt']), ('open_scrap_qt', 3), ('overstock_12_mth_qt', 2), ('overstock_24_mth_qt', 3), ] ) ) return test_data def create_and_populate_test_tables(): """Create reporting table and populate data for testing.""" create_and_populate_reporting_test_tables() create_and_populate_single_product_overview_test_tables() create_and_populate_point_of_sale_test_tables() def format_cols_for_sql(data): """Format columns for SQL string.""" return { 'values': tuple(data.values()), 'columns': '({})'.format(', '.join(data.keys())), } def create_and_populate_point_of_sale_test_tables(): """Create and populate point of sale views.""" execute_sql(queries.CREATE_TABLE_EU_POINT_OF_SALE_BY_COUNTRY_VIEW) execute_sql(queries.CREATE_TABLE_EU_POINT_OF_SALE_BY_PRODUCT_VIEW) execute_sql(queries.CREATE_TABLE_EU_POINT_OF_SALE_BY_PRODUCT_COUNTRY_VIEW) with get_session() as session: by_country_view = list( tuple(t.values()) for t in eu_point_of_sale_by_country_data() ) by_country_view_columns = '({})'.format( ', '.join(eu_point_of_sale_by_country_data()[0].keys()) ) session.execute( queries.insert_into( 'EU_POINT_OF_SALE_BY_COUNTRY_VIEW', { 'values': str(by_country_view).strip('[]'), 'columns': by_country_view_columns, }, ) ) by_product_view = list( tuple(t.values()) for t in eu_point_of_sale_by_product_data() ) by_product_view_columns = '({})'.format( ', '.join(eu_point_of_sale_by_product_data()[0].keys()) ) session.execute( queries.insert_into( 'EU_POINT_OF_SALE_BY_PRODUCT_VIEW', { 'values': str(by_product_view).strip('[]'), 'columns': by_product_view_columns, }, ) ) by_product_country_view = list( tuple(t.values()) for t in eu_point_of_sale_by_product_country_data() ) by_product_country_view_columns = '({})'.format( ', '.join(eu_point_of_sale_by_product_country_data()[0].keys()) ) session.execute( queries.insert_into( 'EU_POINT_OF_SALE_BY_PRODUCT_COUNTRY_VIEW', { 'values': str(by_product_country_view).strip('[]'), 'columns': by_product_country_view_columns, }, ) ) session.flush() def create_and_populate_single_product_overview_test_tables(): """Create and populate single product overview tables.""" execute_sql(queries.CREATE_TABLE_PHYSICAL_PRODUCT_SALES_RTD_VIEW) execute_sql(queries.CREATE_TABLE_PHYSICAL_PRODUCT_SALES_BY_MONTH_VIEW) execute_sql(queries.CREATE_TABLE_PHYSICAL_CURRENT_PERIOD_VIEW) execute_sql(queries.CREATE_PHYSICAL_PRODUCT_INVENTORY_VIEW) execute_sql(queries.CREATE_PHYSICAL_PRODUCT_METADATA_VIEW) execute_sql(queries.CREATE_SAP_PERPETUAL_INVENTORY_VIEW) with get_session() as session: # Rtd view rtd_data = format_cols_for_sql(product_rtd_data()) session.execute( queries.insert_into('PHYSICAL_PRODUCT_SALES_RTD_VIEW', rtd_data) ) # Physical reporting RTD row — order_qt > 0 for top open orders query pr_rtd = format_cols_for_sql(physical_reporting_rtd_data()) session.execute( queries.insert_into('PHYSICAL_PRODUCT_SALES_RTD_VIEW', pr_rtd) ) # Physical reporting RTD row — day1_ship_qt > 0 # for top yesterday shipments query pr_yesterday_rtd = format_cols_for_sql( physical_reporting_yesterday_shipments_rtd_data() ) session.execute( queries.insert_into( 'PHYSICAL_PRODUCT_SALES_RTD_VIEW', pr_yesterday_rtd ) ) # Physical reporting RTD row — mtd_ship_qt > 0 # for top MTD shipments query pr_mtd_rtd = format_cols_for_sql( physical_reporting_mtd_shipments_rtd_data() ) session.execute( queries.insert_into( 'PHYSICAL_PRODUCT_SALES_RTD_VIEW', pr_mtd_rtd ) ) # Physical reporting RTD row — no filter, always included # for label/subaccount summary query pr_label_subaccount_rtd = format_cols_for_sql( physical_reporting_label_subaccount_summary_rtd_data() ) session.execute( queries.insert_into( 'PHYSICAL_PRODUCT_SALES_RTD_VIEW', pr_label_subaccount_rtd ) ) # Physical reporting RTD row — no filter, always included # for product detail query; all query fields zeroed to avoid # affecting other physical reporting tests pr_product_detail_rtd = format_cols_for_sql( physical_reporting_product_detail_rtd_data() ) session.execute( queries.insert_into( 'PHYSICAL_PRODUCT_SALES_RTD_VIEW', pr_product_detail_rtd ) ) # Sales by month view sales_by_month_view = list( tuple(t.values()) for t in single_product_monthly_data() ) sales_by_month_columns = '({})'.format( ', '.join(single_product_monthly_data()[0].keys()) ) session.execute( queries.insert_into( 'PHYSICAL_PRODUCT_SALES_BY_MONTH_VIEW', { 'values': str(sales_by_month_view).strip('[]'), 'columns': sales_by_month_columns, }, ) ) # Current period view curr_period = format_cols_for_sql(current_period_data()) session.execute( queries.insert_into('PHYSICAL_CURRENT_PERIOD_VIEW', curr_period) ) # Inventory view inventor_data = format_cols_for_sql(single_product_inventory_data()) session.execute( queries.insert_into( 'PHYSICAL_PRODUCT_INVENTORY_VIEW', inventor_data ) ) # Metadata view — single product overview / product detail tests metadata = format_cols_for_sql(single_product_metadata_data()) session.execute( queries.insert_into('PHYSICAL_PRODUCT_METADATA_VIEW', metadata) ) # Second metadata row with vendor_id=22221 / subacct_id=33034 # for the top open orders, yesterday shipments, MTD shipments, # and label/subaccount summary persister tests. pr_meta = OrderedDict(single_product_metadata_data()) pr_meta['vendor_id'] = 22221 pr_meta['subacct_id'] = 33034 session.execute( queries.insert_into( 'PHYSICAL_PRODUCT_METADATA_VIEW', format_cols_for_sql(pr_meta), ) ) # Perpetual inventory data for historical views. sap_perpetual_values = list( tuple(t.values()) for t in sap_perpetual_inventory_data() ) sap_perpetual_columns = '({})'.format( ', '.join(sap_perpetual_inventory_data()[0].keys()) ) session.execute( queries.insert_into( 'SAP_PERPETUAL_INVENTORY', { 'values': str(sap_perpetual_values).strip('[]'), 'columns': sap_perpetual_columns, }, ) ) session.flush() def add_coop_product_value(session): """Set the generic coop value for all records in the db.""" sql = """UPDATE US_PHYSICAL_PRODUCT_VIEW SET COOP_GENERIC_PRODUCT_IN = 0; """ session.execute(sql) def create_and_populate_reporting_test_tables(): """Create physical reporting table and pouplate data for testing.""" execute_sql( queries.CREATE_TABLE_US_PHYSICAL_PRODUCT_VIEW, queries.CREATE_TABLE_CA_PHYSICAL_PRODUCT_VIEW, queries.CREATE_TABLE_UK_PHYSICAL_PRODUCT_VIEW, queries.CREATE_TABLE_US_PHYSICAL_RETAILER_VIEW, queries.CREATE_TABLE_CA_PHYSICAL_RETAILER_VIEW, ) with get_session() as session: records = list() ca_products = [ ['709422', 1, '1', '1', CA_SUPPLY_CHAIN], ['709423', 2, '1', '2', CA_SUPPLY_CHAIN], ['709424', 3, '2', '3', CA_SUPPLY_CHAIN], ] for ca_product in ca_products: records.append(CAPhysicalProductView(**us_ca_data(*ca_product))) us_products = [ ['709422', 1, '1', '1', US_SUPPLY_CHAIN], ['709423', 2, '1', '2', US_SUPPLY_CHAIN], ['709424', 3, '2', '3', US_SUPPLY_CHAIN], ] for us_product in us_products: records.append(USPhysicalProductView(**us_ca_data(*us_product))) us_coop_products = [ ['709425', 3, '3', '1', US_SUPPLY_CHAIN], ['709426', 4, '3', '2', US_SUPPLY_CHAIN], ['709427', 5, '4', '3', US_SUPPLY_CHAIN], ] for us_coop_product in us_coop_products: records.append( USPhysicalProductViewCoop(**us_data_coop(*us_coop_product)) ) add_coop_product_value(session) uk_products = [[1, '1', '1'], [2, '1', '2'], [3, '2', '3']] for uk_product in uk_products: records.append(UKPhysicalProductView(**uk_data(*uk_product))) us_retailer_products = [ { 'number': 1, 'vendor_id': '1', 'subaccount_id': '1', 'retailer': 'Retailer1', 'retailer_code': '1', }, { 'number': 2, 'vendor_id': '1', 'subaccount_id': '2', 'retailer': 'Retailer2', 'retailer_code': '2', }, { 'number': 3, 'vendor_id': '2', 'subaccount_id': '3', 'retailer': 'Retailer3', 'retailer_code': '3', }, { 'number': 4, 'vendor_id': '3', 'subaccount_id': '4', 'retailer': 'Retailer4', 'retailer_code': '4', }, { 'number': 5, 'vendor_id': '3', 'subaccount_id': '4', 'retailer': 'Retailer4', 'retailer_code': '4', }, { 'number': 6, 'vendor_id': '3', 'subaccount_id': '4', 'retailer': 'Retailer5', 'retailer_code': '5', }, ] for us_retailer_product in us_retailer_products: records.append( USPhysicalRetailerView( **us_retailer_data(**us_retailer_product) ) ) us_coop_retailer_prouducts = [ { 'number': 7, 'vendor_id': '7', 'subaccount_id': '7', 'retailer': 'Retailer7', 'retailer_code': '7', }, { 'number': 8, 'vendor_id': '7', 'subaccount_id': '8', 'retailer': 'Retailer8', 'retailer_code': '8', }, { 'number': 9, 'vendor_id': '9', 'subaccount_id': '9', 'retailer': 'Retailer9', 'retailer_code': '9', }, ] for us_coop_retailer_product in us_coop_retailer_prouducts: records.append( USPhysicalRetailerViewCoop( **us_retailer_data_coop(**us_coop_retailer_product) ) ) ca_retailer_products = [ { 'number': 1, 'vendor_id': '1', 'subaccount_id': '1', 'retailer': 'Retailer1', 'retailer_code': '1', }, { 'number': 2, 'vendor_id': '1', 'subaccount_id': '2', 'retailer': 'Retailer2', 'retailer_code': '2', }, { 'number': 3, 'vendor_id': '2', 'subaccount_id': '3', 'retailer': 'Retailer3', 'retailer_code': '3', }, { 'number': 4, 'vendor_id': '3', 'subaccount_id': '4', 'retailer': 'Retailer4', 'retailer_code': '4', }, { 'number': 5, 'vendor_id': '3', 'subaccount_id': '4', 'retailer': 'Retailer4', 'retailer_code': '4', }, { 'number': 6, 'vendor_id': '3', 'subaccount_id': '4', 'retailer': 'Retailer5', 'retailer_code': '5', }, ] for ca_retailer_product in ca_retailer_products: records.append( CAPhysicalRetailerView(**retailer_data(**ca_retailer_product)) ) session.add_all(records) session.flush() def remove_snowflake_test_setup(): """Remove test schema and all corresponding tables.""" execute_sql(queries.DROP_UNIQUE_TEST_SCHEMA)