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