def test_inventory(self): data_map = { 'MFR_PRODUCT_ID': '00001957', 'LINE_NUMBER': None, 'BOXES': 0, 'ETL_FILE_ID': 201723, 'UNITS': -76, 'CASES': 84, 'CASE_GTIN': '00012345019572', 'FILLER': ' ', 'DISTRIBUTOR_ID': '1', 'ETL_INVENTORY_ID': 8215, 'COMMENTS': None, 'RECORD_TYPE': 'IR', 'MFR_ID': '5', 'INVENTORY_UNIT_MEAS_ID': None, 'INVENTORY_QTY': None} buff = field_metadata.format_line(self.meta.record_defs["inventory"], data_map) self.assertEqual(buff[0:10], "0000000001") # Distributor identification DISTRIBUTOR_ID self.assertEqual(buff[10:20], "0000000005") # Manufacturer ID self.assertEqual(buff[20:28], "00001957") # Manufacturer Product Id MFR_PRODUCT_ID self.assertEqual(buff[28:124], "".ljust(96)) self.assertEqual(buff[124:130], "000084") # cases self.assertEqual(buff[130:136], "000000") # boxes self.assertEqual(buff[136:142], "-00076") # units self.assertEqual(buff[142:156], "00012345019572") self.assertEqual(buff[156:168], "".ljust(12)) self.assertEqual(buff[168:170], 'IR')
def test_customer(self): logger.info("begin test_customer") fields_by_name = {} for field in self.meta.record_defs["customer"]: fields_by_name[field["field_name"]] = field crc = CustomerRecord() record = crc.record key_value = field_metadata.get_bind_map(self.record_types["CD"], record) self.assertEqual(key_value["SHIP_TO_CUST_ID"], crc.ship_to_id) self.assertEqual(key_value["CUST_NM"], crc.cust_name.rstrip()) self.assertEqual(key_value["ADDR_1"], crc.addr1.rstrip()) self.assertEqual(key_value["ADDR_2"], crc.addr2.rstrip()) self.assertEqual(key_value["CITY"], crc.city.rstrip()) self.assertEqual(key_value["STATE"], crc.state.rstrip()) self.assertEqual(key_value["POSTAL_CD"], crc.postal_code.rstrip()) self.assertEqual(key_value["TEL_NBR"], crc.phone.rstrip()) self.assertEqual(key_value["NATIONAL_ACCT_ID"], crc.nat.rstrip()) self.assertEqual(key_value["SPECIAL_FLG"], crc.flag.rstrip()) self.assertEqual(key_value["FILLER_1"].rstrip(), crc.filler_1.rstrip()) self.assertEqual(key_value["RECORD_TYPE"], crc.record_type.rstrip()) # x = FieldMetadata("SA", field_metadata.NUMERIC, length=2) # print x outrec = field_metadata.format_line(self.record_types["CD"], key_value) # rulers.print_rulers() # print (record) # print (outrec) logger.info(record) logger.info(outrec) self.assertEqual(record, outrec) logger.info("end test_customer")
def emit_customer(self, data_map): data_map["FILLER_00_05"] = " " # TODO should work in FieldMeta and not be required data_map["CLASS_OF_TRADE"] = " " data_map["FILLER_1"] = " " data_map["RECORD_TYPE"] = "SA" outrec = field_metadata.format_line(self.record_defs["customer"], data_map) self.emit(outrec)
def unload_inventory_tot(self, etl_file_id: int, compute: bool = False) -> None: """ Unload Customer Total :param etl_file_id: :param compute: boolean True -compute the totals rather than extract False - extract if exist else ignore :return: None """ logger = logging.getLogger("unload_inventory_tot") logger.setLevel(logging.DEBUG) if self.inventory_total_cursor is None: self.inventory_total_cursor = CursorHelper(self.connection.cursor()) sql = "select * from etl_inventory_tot where etl_file_id = %(ETL_FILE_ID)s" rows = self.inventory_total_cursor.execute(sql, {"ETL_FILE_ID": etl_file_id}) if self.inventory_total_column_names is None: self.inventory_total_column_names = [i[0].upper() for i in self.inventory_total_cursor.description] for row in rows: data_map = dict(zip(self.inventory_total_column_names, row)) for k, v in data_map.items(): print("inventory_tot k %s v %s type(v) %s" % (k, v, type(v))) data_map["HEADER"] = "9999999999" data_map["FILLER36"] = "".ljust(36) data_map["RECORD_TYPE"] = "IT" data_map["RECORD_CNT_REPORTED"] = int(data_map["RECORD_CNT_REPORTED"]) data_map["FILLER97"] = "".ljust(97) logger.debug("record definition %s" % self.record_defs["inventory_total"]) logger.debug("data_map = %s" % data_map) outrec = field_metadata.format_line(self.record_defs["inventory_total"], data_map, trace=True) self.emit(outrec)
def unload_sale_tot(self, etl_file_id: int, compute: bool = False) -> None: """ fetch etl_inventory :param etl_file_id: the primary key of etl_file being unloaded :param compute: True - compute from actual values False - extract only if record exists :return: None """ # TODO Compute if self.sale_total_cursor is None: self.sale_total_cursor = CursorHelper(self.connection.cursor()) sql = "select * from etl_sale_tot where etl_file_id = %(ETL_FILE_ID)s" rows = self.sale_total_cursor.execute(sql, {"ETL_FILE_ID": etl_file_id}) if self.sale_total_column_names is None: self.sale_total_column_names = [i[0].upper() for i in self.sale_total_cursor.description] for row in rows: data_map = dict(zip(self.sale_total_column_names, row)) data_map["HEADER"] = "9999999999" data_map["FILLER_28"] = "".ljust(28) data_map["SALES_REC_CNT"] = int(data_map["SALES_REC_CNT"]) data_map["SUM_EXT_NET_AMT"] = int(data_map["SUM_EXT_NET_AMT"]) * 100 data_map["FILLER_86"] = "".ljust(86) data_map["RECORD_TYPE"] = "ST" outrec = field_metadata.format_line(self.record_defs["sales_total"], data_map) self.emit(outrec)
def unload_customer_total(self, etl_file_id: int, compute: bool = False) -> None: """ Unload Customer Total :param etl_file_id: :param compute: True - compute from etl_customer records False - extract only if the record exists :return: None """ if self.customer_total_cursor is None: self.customer_total_cursor = CursorHelper(self.connection.cursor()) sql = "select * from etl_customer_tot where etl_file_id = %(ETL_FILE_ID)s" rows = self.customer_total_cursor.execute(sql, {"ETL_FILE_ID": etl_file_id}) if self.customer_total_column_names is None: self.customer_total_column_names = [i[0].upper() for i in self.customer_total_cursor.description] # print ("cursor columns: %s" % self.customer_column_names) for row in rows: data_map = dict(zip(self.customer_total_column_names, row)) data_map["HEADER"] = "9999999999" data_map["RECORD_TYPE"] = "CT" data_map["FILLER_127"] = "".ljust(127) data_map["CUSTOMER_COUNT"] = int(data_map["CUSTOMER_COUNT"]) data_map["FILLER_22"] = "".ljust(22) outrec = field_metadata.format_line(self.record_defs["customer_total"], data_map) self.emit(outrec)
def emit_sale(self, data_map: Dict[str, object]): # Union(str, int, datetime.datetime)]): """ Defines filler bind variables not return from the database and converts Decimal values to int where appropriate so that int formats will work The writes to the output file :param data_map: Bind parameters :return: """ data_map["FILLER_12"] = " " data_map["FILLER"] = " " data_map["RECORD_TYPE"] = "SA" data_map["EXTENDED_NET_AMT"] = int(data_map["EXTENDED_NET_AMT"] * 100) data_map["CASES_SHIPPED"] = int(data_map["CASES_SHIPPED"]) data_map["BOXES_SHIPPED"] = int(data_map["BOXES_SHIPPED"]) data_map["UNITS_SHIPPED"] = int(data_map["UNITS_SHIPPED"]) record = field_metadata.format_line(self.record_defs["sales"], data_map) try: self.emit(record) except Exception as e: msg = ("binds %s\n, record: %s exception: %s" % (data_map, record, e))
def format_customers(file, count): outfile = open(file, "w") definition = record_defs["customer"] print(ruler) for i in range(0, count): customer = get_customer() formatted = field_metadata.format_line(definition, customer) print(formatted) outfile.write(formatted) outfile.write("\n")
def test_inventory2(self): logger.info("begin test_inventory") MFR_PRODUCT_ID = '00001957' LINE_NUMBER = None BOXES = 0 ETL_FILE_ID = 201723 UNITS = -76 CASES = 84 CASE_GTIN = '00012345019572' FILLER = None DISTRIBUTOR_ID = '0000000001' ETL_INVENTORY_ID = 8215 COMMENTS = None RECORD_TYPE = 'IR' MFR_ID = '0000000005' INVENTORY_UNIT_MEAS_ID = None INVENTORY_QTY = None data_map = { 'MFR_PRODUCT_ID': MFR_PRODUCT_ID, 'LINE_NUMBER': LINE_NUMBER, 'BOXES': BOXES, 'ETL_FILE_ID': ETL_FILE_ID, 'UNITS': UNITS, 'CASES': CASES, 'CASE_GTIN': CASE_GTIN, 'FILLER': FILLER, 'DISTRIBUTOR_ID': DISTRIBUTOR_ID, 'ETL_INVENTORY_ID': ETL_INVENTORY_ID, 'COMMENTS': COMMENTS, 'RECORD_TYPE': RECORD_TYPE, 'MFR_ID': MFR_ID, 'INVENTORY_UNIT_MEAS_ID': None, 'INVENTORY_QTY': 'IR' } outrec = field_metadata.format_line(self.record_types["IR"], data_map) print(outrec) self.assertEqual(outrec[0:10], DISTRIBUTOR_ID) self.assertEqual(outrec[10:20], MFR_ID) self.assertEqual(outrec[20:28], MFR_PRODUCT_ID) self.assertEqual(outrec[28:124], "".ljust(96)) self.assertEqual(outrec[124:130], "000084") self.assertEqual(outrec[130:136], "000000") self.assertEqual(outrec[136:142], "-00076") self.assertEqual(outrec[142:156], CASE_GTIN) self.assertEqual(outrec[156:168], "".ljust(12)) self.assertEqual(outrec[168:170], RECORD_TYPE)
def emit_inventory(self, data_map: Dict[str, object]) -> None: # Union(str, int, datetime.datetime)]) -> None: data_map["RECORD_TYPE"] = "IT" data_map["CASES"] = int(data_map["CASES"]) data_map["BOXES"] = int(data_map["BOXES"]) data_map["UNITS"] = int(data_map["UNITS"]) data_map["FILLER"] = " " if data_map["CASE_GTIN"] is None: data_map["CASE_GTIN"] = "000000000000000" outrec = field_metadata.format_line(self.record_defs["inventory"], data_map) try: self.emit(outrec) except Exception as e: msg = "binds: %s\n outrec: %s %s" % (data_map, outrec, e) raise Exception(msg)
def test_it(self): data_map = { 'RECORD_TYPE': 'IT', 'RECORD_CNT_ACTUAL': None, 'FILLER97': ' ', 'ETL_FILE_ID': 1, 'FILLER36': ' ', 'RECORD_CNT_REPORTED': 608064, 'FILE_CREATION_DT': datetime.date(2016,8,28), 'LINE_NUMBER': 9630, 'INVENTORY_DT': datetime.date(2016,8,28), 'HEADER': '9999999999' } buff = field_metadata.format_line(self.meta.record_defs["inventory_total"], data_map) print(get_rulers()) print(buff)
def test_sale(self): fields_by_name = {} for field in self.meta.record_defs["customer"]: fields_by_name[field["field_name"]] = field sr = SalesRecord() record = sr.record # rulers.print_rulers() # print (record) record_def = self.record_types["SA"] # for field_def in record_def: # print (field_def) key_value = field_metadata.get_bind_map(record_def, record) self.assertEqual(key_value["DISTRIB_ID"], sr.distributor_id) self.assertEqual(key_value["MFR_ID"], sr.mfr_id) self.assertEqual(key_value["MFR_PRODUCT_ID"], sr.mfr_product_id) self.assertEqual(key_value["SHIP_TO_CUST_ID"], sr.ship_to_cust_id) self.assertEqual(key_value["INVOICE_CD"], sr.invoice_nbr) self.assertEqual(key_value["INVOICE_DT"], datetime.datetime.strptime(sr.invoice_dt, "%Y%m%d")) self.assertEqual( key_value["SHIP_DT"], datetime.datetime.strptime(sr.ship_dt, "%Y%m%d") ) self.assertEqual(key_value["EXTENDED_NET_AMT"], int(sr.extended_net)) # TODO need to divide by 100 self.assertEqual(key_value["DISTRIB_PRODUCT_REF"], sr.distrib_product_id) self.assertEqual(key_value["PRODUCT_DESCR"], sr.product_description.rstrip()) self.assertEqual(key_value["CASES_SHIPPED"], int(sr.cases_shipped)) self.assertEqual(key_value["BOXES_SHIPPED"], int(sr.boxes_shipped)) self.assertEqual(key_value["UNITS_SHIPPED"], int(sr.units_shipped)) self.assertEqual(key_value["CASE_GTIN"], sr.case_gtin) self.assertEqual(key_value["RECORD_TYPE"], sr.record_type) outrec = field_metadata.format_line(self.record_types["SA"], key_value) # rulers.print_rulers() self.sales_rec = outrec logger.info(record) logger.info(outrec) self.assertEqual(record, outrec)
def test_customer_total(self): logger.info("begin test_customer_total") # field_dictionary = field_metadata.get_field_dictionary(self.meta.record_defs["inventory"]) ct = CustomerTotalRecord() record = ct.record key_value = field_metadata.get_bind_map(self.record_types["CT"], record) self.assertEqual(key_value["HEADER"], ct.header) self.assertEqual(key_value["FILLER_127"], ct.filler127) self.assertEqual(key_value["CUSTOMER_COUNT"], int(ct.total_record_count)) self.assertEqual(key_value["FILLER_22"], ct.filler22) self.assertEqual(key_value["RECORD_TYPE"], ct.record_type) outrec = field_metadata.format_line(self.record_types["CT"], key_value) self.customer_total_rec = outrec # rulers.print_rulers() logger.info(record) logger.info(outrec) self.assertEqual(record, outrec) logger.info("end test_customer_total")
def test_sales_total(self): logger.info("begin test_sales_total") sales_tot = SalesTotalRecord() record = sales_tot.record key_value = field_metadata.get_bind_map(self.record_types["AT"], record) self.assertEqual(key_value["HEADER"], sales_tot.record_header) self.assertEqual(key_value["FILLER_28"], sales_tot.filler28) self.assertEqual(key_value["SALES_START_DT"], datetime.datetime.strptime(sales_tot.period_covered_start_date, '%Y%m%d')) self.assertEqual(key_value["SALES_END_DT"], datetime.datetime.strptime(sales_tot.period_covered_end_date, '%Y%m%d')) self.assertEqual(key_value["FILE_CREATE_DT"], datetime.datetime.strptime(sales_tot.file_creation_date, '%Y%m%d')) self.assertEqual(key_value["SALES_REC_CNT"], int(sales_tot.total_record_count)) self.assertEqual(key_value["SUM_EXT_NET_AMT"], int(sales_tot.total_amount)) self.assertEqual(key_value["RECORD_TYPE"], sales_tot.record_type) outrec = field_metadata.format_line(self.record_types["AT"], key_value) # rulers.print_rulers() self.sales_total_rec = outrec logger.info(record) logger.info(outrec) self.assertEqual(record, outrec) logger.info("end test_sales_total")
def test_inventory_total(self): data_map = { 'INVENTORY_DT': datetime.date(2017,7,4), 'FILE_CREATION_DT': datetime.date(2017,7,5), 'RECORD_CNT_REPORTED': 3, "HEADER" : "9999999999", "FILLER36" : "".ljust(36), "RECORD_TYPE" : "IT", "FILLER97" : "".ljust(97) } buff = field_metadata.format_line(self.meta.record_defs["inventory_total"], data_map) self.assertEqual(buff[0:10], "9999999999") self.assertEqual(buff[10:46], "".ljust(36)) self.assertEqual(buff[46:54], "20170704") self.assertEqual(buff[54:62], "20170705") self.assertEqual(buff[62:71], "000000003") self.assertEqual(buff[71:168], "".ljust(97)) self.assertEqual(buff[168:170], "IT")
def test_inventory_total(self): logger.info("begin test_inventory_total") itr = InventoryTotalRecord() record = itr.record key_value = field_metadata.get_bind_map(self.record_types["IT"], record) self.assertEqual(key_value["HEADER"], itr.header) self.assertEqual(len(key_value["FILLER36"]), 36) self.assertEqual(key_value["FILLER36"], itr.filler36) self.assertEqual(key_value["INVENTORY_DT"], datetime.datetime.strptime(itr.inventory_date, '%Y%m%d')) self.assertEqual(key_value["FILE_CREATION_DT"], datetime.datetime.strptime(itr.file_creation_date, '%Y%m%d')) self.assertEqual(key_value["RECORD_CNT_REPORTED"], int(itr.total_record_count)) self.assertEqual(key_value["FILLER97"], itr.filler97) self.assertEqual(key_value["RECORD_TYPE"], itr.record_type) outrec = field_metadata.format_line(self.record_types["IT"], key_value) self.inventory_total_rec = outrec # rulers.print_rulers() logger.info(record) logger.info(outrec) self.assertEqual(record, outrec) logger.info("end test_inventory_total")
def test_inventory(self): logger.info("begin test_inventory") ir = InventoryRecord() record = ir.record key_value = field_metadata.get_bind_map(self.record_types["IR"], record) self.assertEqual(key_value["DISTRIBUTOR_ID"], ir.distributor_id) self.assertEqual(key_value["MFR_PRODUCT_ID"], ir.mfr_product_id) self.assertEqual(key_value["COMMENTS"], ir.comments) self.assertEqual(key_value["CASES"], int(ir.cases)) self.assertEqual(key_value["BOXES"], int(ir.boxes)) self.assertEqual(key_value["UNITS"], int(ir.units)) self.assertEqual(key_value["CASE_GTIN"], ir.case_gtin) self.assertEqual(key_value["RECORD_TYPE"], ir.record_type) outrec = field_metadata.format_line(self.record_types["IR"], key_value) self.inventory_rec = outrec logger.info("about to print records") logger.info(record) logger.info(outrec) self.assertEqual(record, outrec) logger.info("end test_inventory")