def test_run_tpt(self):
# Expected to return True if there is no error
self.assertTrue(create_user(), True)
# Expected to return True for TPT export
self.assertTrue(tdtestpy.run_single_tpt(tpt_export, tpt_export_log, tpt_directory, dbs_name, "tdtestpy_unit_test_tpt", \
"tdtestpy_unit_test_tpt", "test.dat", "none"), True)
# Expected to return True for TPT streams
self.assertTrue(tdtestpy.run_single_tpt(tpt_stream, tpt_stream_log, tpt_directory, dbs_name, "tdtestpy_unit_test_tpt", \
"tdtestpy_unit_test_tpt", "test.dat", "none"), True)
# Expected to return False, wrong password
self.assertFalse(tdtestpy.run_single_tpt(tpt_export, tpt_incorrect_password_log, tpt_directory, dbs_name, "tdtestpy_unit_test_tpt", \
"tdtestpy_unit_test_tpt_wrong_password", "wrong_password.dat", "none"), False)
# Expected to return False, table does not exists
self.assertFalse(tdtestpy.run_single_tpt(tpt_failed, tpt_failed_log, tpt_directory, dbs_name, "tdtestpy_unit_test_tpt", \
"tdtestpy_unit_test_tpt", "failed.dat", "none"), False)
# Expected to return True for TPT export and streams using named pipe
export_result, stream_result = tdtestpy.run_tpt_named_pipe(tpt_export, tpt_export_pipe_log, tpt_stream, tpt_stream_pipe_log, \
tpt_directory, dbs_name, "tdtestpy_unit_test_tpt", "tdtestpy_unit_test_tpt", \
"test_pipe.dat", "none")
self.assertTrue((export_result), True)
self.assertTrue((stream_result), True)
# Expected to return False for TPT export and True for streams using named pipe
export_result2, stream_result2 = tdtestpy.run_tpt_named_pipe(tpt_failed, tpt_export_pipe_log_failed, tpt_stream, tpt_stream_pipe_log_no_row, \
tpt_directory, dbs_name, "tdtestpy_unit_test_tpt", "tdtestpy_unit_test_tpt", \
"test_pipe.dat", "none")
self.assertFalse((export_result2), False)
self.assertTrue((stream_result2), True)
def run_dml_item_inventory_plan(dbs_name, user_name, ignore_errors, run_result=None):
try:
with udaExec.connect(method="odbc", system=dbs_name, username=user_name, password=user_name) as db_connect:
with db_connect.cursor() as cursor:
# Run end isolated loading just in case prior run failed
item_inv_plan_eil = user_name.strip() + "_ITEM_INV_PLAN_LDI"
cursor.execute("END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=%s;'" % (item_inv_plan_eil), ignoreErrors=[9887])
# Delete so we can use TPT streams to load it back
cursor.execute("delete ITEM_INVENTORY_PLAN_LDI where ITEM_INVENTORY_PLAN_DT < '1970-11-28'", ignoreErrors=ignore_errors)
logging.info ("TPT export for ITEM_INVENTORY_PLAN_LDI started")
if not tdtestpy.run_single_tpt(item_inventory_plan_export_file, item_inventory_plan_export_log, directory_path, \
dbs_name, user_name, user_name, item_inventory_plan_data_file, tracelevel):
msg = "TPT export for ITEM_INVENTORY_PLAN_LDI failed, please review " + item_inventory_plan_export_log
logging.info ("TPT export for ITEM_INVENTORY_PLAN_LDI completed with failure")
tdtestpy.copy_file (item_inventory_plan_export_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT export for ITEM_INVENTORY_PLAN_LDI completed successful")
tdtestpy.copy_file (item_inventory_plan_export_log, passed_dir)
logging.info ("TPT stream for ITEM_INVENTORY_PLAN_LDI started")
if not tdtestpy.run_single_tpt(item_inventory_plan_stream_file, item_inventory_plan_stream_log, directory_path, \
dbs_name, user_name, user_name, item_inventory_plan_data_file, tracelevel):
msg = "TPT stream for ITEM_INVENTORY_PLAN_LDI failed, please review " + item_inventory_plan_stream_log
logging.info ("TPT stream for ITEM_INVENTORY_PLAN_LDI completed with failure")
tdtestpy.copy_file (item_inventory_plan_stream_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT stream for ITEM_INVENTORY_PLAN_LDI completed successful")
tdtestpy.copy_file (item_inventory_plan_stream_log, passed_dir)
# Delete data file if TPT export and stream ran successful
tdtestpy.delete_one_file (item_inventory_plan_data_file_full_path)
cursor.execute("Select ITEM_INVENTORY_PLAN_DT, LOCATION_ID, ITEM_ID from ITEM_INVENTORY_PLAN_LDI sample 100")
result_set = cursor.fetchall()
index_combo_list = []
for row in result_set:
# Created sublist
combo_id = [row["ITEM_INVENTORY_PLAN_DT"], row["LOCATION_ID"], row["ITEM_ID"]]
# Append sublist to main list
index_combo_list.append(combo_id)
# Extract sublist from main list and replace index
for index_combo in index_combo_list:
cursor.execute("update ITEM_INVENTORY_PLAN_LDI set PLAN_ON_HAND_QTY = 0 \
where ITEM_INVENTORY_PLAN_DT = '%s' and LOCATION_ID = %s and ITEM_ID = '%s'" % (index_combo[0], \
index_combo[1], index_combo[2]), ignoreErrors=ignore_errors)
cursor.execute("delete ITEM_INVENTORY_PLAN_LDI \
where ITEM_INVENTORY_PLAN_DT = '%s' and LOCATION_ID = %s and ITEM_ID = '%s'" % (index_combo[0], \
index_combo[1], index_combo[2]), ignoreErrors=ignore_errors)
# Trim down main list to 20 elements
new_index_combo_list = index_combo_list[0:20]
for new_index_combo in new_index_combo_list:
cursor.execute("insert into ITEM_INVENTORY_PLAN_LDI select * from sit_ldi_pll_stage.ITEM_INVENTORY_PLAN_LDI \
where ITEM_INVENTORY_PLAN_DT = '%s' and LOCATION_ID = %s and ITEM_ID = '%s'" % (new_index_combo[0], \
new_index_combo[1], new_index_combo[2]), ignoreErrors=ignore_errors)
cursor.execute("update ITEM_INVENTORY_PLAN_LDI set PLAN_ON_HAND_RETAIL_AMT = PLAN_ON_HAND_QTY \
where ITEM_INVENTORY_PLAN_DT = '%s' and LOCATION_ID = %s and ITEM_ID = '%s'" % (new_index_combo[0], \
new_index_combo[1], new_index_combo[2]), ignoreErrors=ignore_errors)
cursor.execute("MERGE into ITEM_INVENTORY_PLAN_LDI as t1 \
using sit_ldi_pll_stage.ITEM_INVENTORY_PLAN_LDI as t2 \
on t1.ITEM_INVENTORY_PLAN_DT = t2.ITEM_INVENTORY_PLAN_DT \
and t1.LOCATION_ID = t2.LOCATION_ID \
and t1.ITEM_ID = t2.ITEM_ID \
WHEN MATCHED THEN \
UPDATE SET PLAN_ON_HAND_RETAIL_AMT = t2.PLAN_ON_HAND_RETAIL_AMT \
WHEN NOT MATCHED THEN \
insert (ITEM_INVENTORY_PLAN_DT, LOCATION_ID, ITEM_ID, PLAN_ON_HAND_QTY, PLAN_ON_HAND_RETAIL_AMT) \
values (t2.ITEM_INVENTORY_PLAN_DT, t2.LOCATION_ID, t2.ITEM_ID, t2.PLAN_ON_HAND_QTY, \
t2.PLAN_ON_HAND_RETAIL_AMT)", ignoreErrors=ignore_errors)
# Remove logically deleted rows to free up space
cursor.execute("END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=%s;'" % (item_inv_plan_eil), ignoreErrors=[9887])
cursor.execute("ALTER TABLE ITEM_INVENTORY_PLAN_LDI RELEASE DELETED ROWS AND RESET LOAD IDENTITY", ignoreErrors=ignore_errors)
except Exception as e:
if run_result is None:
return str(e)
else:
run_result.put(str(e))
if run_result is None:
return True
else:
run_result.put(True)
def run_dml_item(dbs_name, user_name, item_available, ignore_errors, run_result=None):
try:
with udaExec.connect(method="odbc", system=dbs_name, username=user_name, password=user_name) as db_connect:
with db_connect.cursor() as cursor:
# Run end isolated loading just in case prior run failed
item_eil = user_name.strip() + "_ITEM_LDI"
cursor.execute("END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=%s;'" % (item_eil), ignoreErrors=[9887])
cursor.execute("delete item_ldi \
where ITEM_AVAILABLE = '%s' \
and ITEM_ID not in (select ITEM_ID from ITEM_INVENTORY_LDI) \
and ITEM_ID not in (select ITEM_ID from ITEM_INVENTORY_PLAN_LDI) \
and ITEM_ID not in (select RETURNED_ITEM_ID from RETURN_TRANSACTION_LINE_LDI) \
and ITEM_ID not in (select ITEM_ID from SALES_TRANSACTION_LINE_PLL) \
and ITEM_ID not in (select ITEM_ID from ITEM_PRICE_HISTORY)" % (item_available[0]), ignoreErrors=ignore_errors)
cursor.execute("update item_ldi set ITEM_DESC = 'Product being recall and taking off store shelves' \
where ITEM_AVAILABLE = '%s'" % (item_available[0]), ignoreErrors=ignore_errors)
cursor.execute("MERGE into item_ldi as i1 \
using sit_ldi_pll_stage.item_ldi as i2 \
on i1.ITEM_ID = i2.ITEM_ID and i1.ITEM_LEVEL = i2.ITEM_LEVEL \
WHEN MATCHED THEN \
UPDATE SET ITEM_DESC = i2.ITEM_DESC \
WHEN NOT MATCHED THEN \
insert (ITEM_ID, ITEM_NAME, ITEM_LEVEL, ITEM_DESC, ITEM_SUBCLASS_CD, \
ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD, \
ITEM_AVAILABLE, PRODUCT_IMEI, ITEM_JSON, ITEM_XML) \
values (i2.ITEM_ID, i2.ITEM_NAME, i2.ITEM_LEVEL, i2.ITEM_DESC, \
i2.ITEM_SUBCLASS_CD, i2.ITEM_TYPE_CD, i2.INVENTORY_IND, i2.VENDOR_PARTY_ID, \
i2.COMMODITY_CD, i2.BRAND_CD, i2.ITEM_AVAILABLE, i2.PRODUCT_IMEI, i2.ITEM_JSON, \
i2.ITEM_XML)", ignoreErrors=ignore_errors)
cursor.execute("update item_ldi set INVENTORY_IND = 'HQO' \
where ITEM_AVAILABLE = ''", ignoreErrors=ignore_errors)
cursor.execute("delete item_ldi \
where ITEM_AVAILABLE = '' \
and ITEM_LEVEL < 15 \
and ITEM_ID not in (select ITEM_ID from ITEM_INVENTORY_LDI) \
and ITEM_ID not in (select ITEM_ID from ITEM_INVENTORY_PLAN_LDI) \
and ITEM_ID not in (select RETURNED_ITEM_ID from RETURN_TRANSACTION_LINE_LDI) \
and ITEM_ID not in (select ITEM_ID from SALES_TRANSACTION_LINE_PLL) \
and ITEM_ID not in (select ITEM_ID from ITEM_PRICE_HISTORY)", ignoreErrors=ignore_errors)
logging.info ("TPT export for item_ldi started")
if not tdtestpy.run_single_tpt(item_ldi_export_file, item_ldi_export_log, directory_path, \
dbs_name, user_name, user_name, item_ldi_data_file, tracelevel):
msg = "TPT export for item_ldi failed, please review " + item_ldi_export_log
logging.info ("TPT export for item_ldi completed with failure")
tdtestpy.copy_file (item_ldi_export_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT export for item_ldi completed successful")
tdtestpy.copy_file (item_ldi_export_log, passed_dir)
logging.info ("TPT stream for item_ldi started")
if not tdtestpy.run_single_tpt(item_ldi_stream_file, item_ldi_stream_log, directory_path, \
dbs_name, user_name, user_name, item_ldi_data_file, tracelevel):
msg = "TPT stream for item_ldi failed, please review " + item_ldi_stream_log
logging.info ("TPT stream for item_ldi completed with failure")
tdtestpy.copy_file (item_ldi_stream_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT stream for item_ldi completed successful")
tdtestpy.copy_file (item_ldi_stream_log, passed_dir)
# Delete data file if TPT export and stream ran successful
tdtestpy.delete_one_file (item_ldi_data_file_full_path)
# Remove logically deleted rows to free up space
cursor.execute("END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=%s;'" % (item_eil), ignoreErrors=[9887])
cursor.execute("ALTER TABLE ITEM_INVENTORY_PLAN_LDI RELEASE DELETED ROWS AND RESET LOAD IDENTITY", ignoreErrors=ignore_errors)
except Exception as e:
if run_result is None:
return str(e)
else:
run_result.put(str(e))
if run_result is None:
return True
else:
run_result.put(True)
def run_dml_return_trans_line(dbs_name, user_name, TRAN_LINE_STATUS_CD_LIST, ignore_errors, run_result=None):
try:
with udaExec.connect(method="odbc", system=dbs_name, username=user_name, password=user_name) as db_connect:
with db_connect.cursor() as cursor:
# Run end isolated loading just in case prior run failed
return_trans_line_eil = user_name.strip() + "_RETURN_TRANS_LINE_LDI"
cursor.execute("END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=%s;'" % (return_trans_line_eil), ignoreErrors=[9887])
cursor.execute("update RETURN_TRANSACTION_LINE_LDI set TRAN_LINE_STATUS_CD = '%s' \
where TRAN_LINE_STATUS_CD = '%s'" % (TRAN_LINE_STATUS_CD_LIST[0], TRAN_LINE_STATUS_CD_LIST[1]), \
ignoreErrors=ignore_errors)
cursor.execute("delete RETURN_TRANSACTION_LINE_LDI \
where TRAN_LINE_STATUS_CD in ('A', 'B')", ignoreErrors=ignore_errors)
logging.info ("TPT export for RETURN_TRANSACTION_LINE_LDI started")
if not tdtestpy.run_single_tpt(return_transaction_line_export_file, return_transaction_line_export_log, directory_path, \
dbs_name, user_name, user_name, return_transaction_line_data_file, tracelevel):
msg = "TPT export for RETURN_TRANSACTION_LINE_LDI failed, please review " + return_transaction_line_export_log
logging.info ("TPT export for RETURN_TRANSACTION_LINE_LDI completed with failure")
tdtestpy.copy_file (return_transaction_line_export_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT export for RETURN_TRANSACTION_LINE_LDI completed successful")
tdtestpy.copy_file (return_transaction_line_export_log, passed_dir)
logging.info ("TPT stream for RETURN_TRANSACTION_LINE_LDI started")
if not tdtestpy.run_single_tpt(return_transaction_line_stream_file, return_transaction_line_stream_log, directory_path, \
dbs_name, user_name, user_name, return_transaction_line_data_file, tracelevel):
msg = "TPT stream for RETURN_TRANSACTION_LINE_LDI failed, please review " + return_transaction_line_stream_log
logging.info ("TPT stream for RETURN_TRANSACTION_LINE_LDI completed with failure")
tdtestpy.copy_file (return_transaction_line_stream_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT stream for RETURN_TRANSACTION_LINE_LDI completed successful")
tdtestpy.copy_file (return_transaction_line_stream_log, passed_dir)
# Delete data file if TPT export and stream ran successful
tdtestpy.delete_one_file (return_transaction_line_data_file_full_path)
cursor.execute("delete RETURN_TRANSACTION_LINE_LDI \
where TRAN_LINE_STATUS_CD = '%s'" % (TRAN_LINE_STATUS_CD_LIST[0]), ignoreErrors=ignore_errors)
cursor.execute("insert into RETURN_TRANSACTION_LINE_LDI select * from sit_ldi_pll_stage.RETURN_TRANSACTION_LINE_LDI \
where TRAN_LINE_STATUS_CD in ('%s', '%s')" % (TRAN_LINE_STATUS_CD_LIST[0], TRAN_LINE_STATUS_CD_LIST[1]), ignoreErrors=ignore_errors)
# Remove logically deleted rows to free up space
cursor.execute("END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=%s;'" % (return_trans_line_eil), ignoreErrors=[9887])
cursor.execute("ALTER TABLE RETURN_TRANSACTION_LINE_LDI RELEASE DELETED ROWS AND RESET LOAD IDENTITY", ignoreErrors=ignore_errors)
except Exception as e:
if run_result is None:
return str(e)
else:
run_result.put(str(e))
if run_result is None:
return True
else:
run_result.put(True)
def run_dml_item_inventory(dbs_name, user_name, location_id_list, ignore_errors, run_result=None):
try:
with udaExec.connect(method="odbc", system=dbs_name, username=user_name, password=user_name) as db_connect:
with db_connect.cursor() as cursor:
# Run end isolated loading just in case prior run failed
item_inv_eil = user_name.strip() + "_ITEM_INV_LDI"
cursor.execute("END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=%s;'" % (item_inv_eil), ignoreErrors=[9887])
cursor.execute("delete ITEM_INVENTORY_LDI \
where ITEM_INV_DT < '1975-01-01'", ignoreErrors=ignore_errors)
logging.info ("TPT export for ITEM_INVENTORY_LDI started")
if not tdtestpy.run_single_tpt(item_inventory_export_file, item_inventory_export_log, directory_path, \
dbs_name, user_name, user_name, item_inventory_data_file, tracelevel):
msg = "TPT export for ITEM_INVENTORY_LDI failed, please review " + item_inventory_export_log
logging.info ("TPT export for ITEM_INVENTORY_LDI completed with failure")
tdtestpy.copy_file (item_inventory_export_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT export for ITEM_INVENTORY_LDI completed successful")
tdtestpy.copy_file (item_inventory_export_log, passed_dir)
logging.info ("TPT stream for ITEM_INVENTORY_LDI started")
if not tdtestpy.run_single_tpt(item_inventory_stream_file, item_inventory_stream_log, directory_path, \
dbs_name, user_name, user_name, item_inventory_data_file, tracelevel):
msg = "TPT stream for ITEM_INVENTORY_LDI failed, please review " + item_inventory_stream_log
logging.info ("TPT stream for ITEM_INVENTORY_LDI completed with failure")
tdtestpy.copy_file (item_inventory_stream_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT stream for ITEM_INVENTORY_LDI completed successful")
tdtestpy.copy_file (item_inventory_stream_log, passed_dir)
# Delete data file if TPT export and stream ran successful
tdtestpy.delete_one_file (item_inventory_data_file_full_path)
for location_id in location_id_list:
cursor.execute("update ITEM_INVENTORY_LDI set ON_HAND_AT_RETAIL_AMT = 10000 \
where LOCATION_ID = %s" % (location_id), ignoreErrors=ignore_errors)
cursor.execute("delete ITEM_INVENTORY_LDI \
where LOCATION_ID = %s" % (location_id), ignoreErrors=ignore_errors)
location_id_set = str(tuple(location_id_list))
cursor.execute("insert into ITEM_INVENTORY_LDI select * from sit_ldi_pll_stage.ITEM_INVENTORY_LDI \
where LOCATION_ID in %s" % (location_id_set), ignoreErrors=ignore_errors)
# Remove logically deleted rows to free up space
cursor.execute("END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=%s;'" % (item_inv_eil), ignoreErrors=[9887])
cursor.execute("ALTER TABLE ITEM_INVENTORY_LDI RELEASE DELETED ROWS AND RESET LOAD IDENTITY", ignoreErrors=ignore_errors)
except Exception as e:
if run_result is None:
return str(e)
else:
run_result.put(str(e))
if run_result is None:
return True
else:
run_result.put(True)
def run_dml_transaction1(dbs_name, user_name, ignore_errors, run_result=None):
try:
with udaExec.connect(method="odbc", system=dbs_name, username=user_name, password=user_name) as db_connect:
with db_connect.cursor() as cursor:
# SALES_TRANSACTION_PLL table
cursor.execute("delete SALES_TRANSACTION_PLL where TRAN_DATE between '1970-01-01' and '1980-01-01'", ignoreErrors=ignore_errors)
logging.info ("TPT export for sale_trans_pll started")
if not tdtestpy.run_single_tpt(sale_trans_pll_export_file, sale_trans_pll_export_log, directory_path, \
dbs_name, user_name, user_name, sale_trans_pll_data_file, tracelevel):
msg = "TPT export for sale_trans_pll failed, please review " + sale_trans_pll_export_log
logging.info ("TPT export for sale_trans_pll completed with failure")
tdtestpy.copy_file (sale_trans_pll_export_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT export for sale_trans_pll completed successful")
tdtestpy.copy_file (sale_trans_pll_export_log, passed_dir)
logging.info ("TPT stream for sale_trans_pll started")
if not tdtestpy.run_single_tpt(sale_trans_pll_stream_file, sale_trans_pll_stream_log, directory_path, \
dbs_name, user_name, user_name, sale_trans_pll_data_file, tracelevel):
msg = "TPT stream for sale_trans_pll failed, please review " + sale_trans_pll_stream_log
logging.info ("TPT stream for sale_trans_pll completed with failure")
tdtestpy.copy_file (sale_trans_pll_stream_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT stream for sale_trans_pll completed successful")
tdtestpy.copy_file (sale_trans_pll_stream_log, passed_dir)
# Delete data file if TPT export and stream ran successful
tdtestpy.delete_one_file (sale_trans_pll_data_file_full_path)
# SALES_TRANSACTION_LINE_PLL table
cursor.execute("update SALES_TRANSACTION_LINE_PLL set TRAN_LINE_DATE = current_date, UNIT_COST_AMT = 12.10 \
where LOCATION < 50", ignoreErrors=ignore_errors)
cursor.execute("delete SALES_TRANSACTION_LINE_PLL where LOCATION < 50", ignoreErrors=ignore_errors)
cursor.execute("insert into SALES_TRANSACTION_LINE_PLL select * from sit_ldi_pll_stage.SALES_TRANSACTION_LINE_PLL \
where LOCATION < 50", ignoreErrors=ignore_errors)
# PARTY_PLL table
cursor.execute("delete PARTY_PLL where PARTY_STATE BETWEEN 'A' AND 'B'", ignoreErrors=ignore_errors)
cursor.execute("insert into PARTY_PLL select * from sit_ldi_pll_stage.PARTY_PLL \
where PARTY_STATE BETWEEN 'A' AND 'B'", ignoreErrors=ignore_errors)
# LOCATION_PLL table
cursor.execute("delete LOCATION_PLL where LOCATION_EFFECTIVE_DT < '1980-01-01'", ignoreErrors=ignore_errors)
cursor.execute("insert into LOCATION_PLL select * from sit_ldi_pll_stage.LOCATION_PLL \
where LOCATION_EFFECTIVE_DT < '1980-01-01'", ignoreErrors=ignore_errors)
except Exception as e:
if run_result is None:
return str(e)
else:
run_result.put(str(e))
if run_result is None:
return True
else:
run_result.put(True)
def run_dml_transaction4(dbs_name, user_name, ignore_errors, run_result=None):
try:
with udaExec.connect(method="odbc", system=dbs_name, username=user_name, password=user_name) as db_connect:
with db_connect.cursor() as cursor:
# SALES_TRANSACTION_PLL table
cursor.execute("insert into SALES_TRANSACTION_PLL select * from sit_ldi_pll_stage.SALES_TRANSACTION_PLL \
where TRAN_DATE > '2010-01-01'", ignoreErrors=ignore_errors)
cursor.execute("update SALES_TRANSACTION_PLL set VISIT_ID = 123, TRAN_STATUS_CD = 'T' \
where TRAN_DATE > '2010-01-01'", ignoreErrors=ignore_errors)
cursor.execute("delete SALES_TRANSACTION_PLL where TRAN_DATE > '2010-01-01'", ignoreErrors=ignore_errors)
cursor.execute("insert into SALES_TRANSACTION_PLL select * from sit_ldi_pll_stage.SALES_TRANSACTION_PLL \
where TRAN_DATE > '2010-01-01'", ignoreErrors=ignore_errors)
# SALES_TRANSACTION_LINE_PLL table
cursor.execute("delete SALES_TRANSACTION_LINE_PLL where LOCATION between 331 and 350", ignoreErrors=ignore_errors)
cursor.execute("insert into SALES_TRANSACTION_LINE_PLL select * from sit_ldi_pll_stage.SALES_TRANSACTION_LINE_PLL \
where LOCATION between 331 and 350", ignoreErrors=ignore_errors)
# PARTY_PLL table
cursor.execute("delete PARTY_PLL where PARTY_STATE = 'N'", ignoreErrors=ignore_errors)
logging.info ("TPT export for party_pll started")
if not tdtestpy.run_single_tpt(party_pll_export_file, party_pll_export_log, directory_path, \
dbs_name, user_name, user_name, party_pll_data_file, tracelevel):
msg = "TPT export for party_pll failed, please review " + party_pll_export_log
logging.info ("TPT export for party_pll completed with failure")
tdtestpy.copy_file (party_pll_export_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT export for party_pll completed successful")
tdtestpy.copy_file (party_pll_export_log, passed_dir)
logging.info ("TPT stream for party_pll started")
if not tdtestpy.run_single_tpt(party_pll_stream_file, party_pll_stream_log, directory_path, \
dbs_name, user_name, user_name, party_pll_data_file, tracelevel):
msg = "TPT stream for party_pll failed, please review " + party_pll_stream_log
logging.info ("TPT stream for party_pll completed with failure")
tdtestpy.copy_file (party_pll_stream_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT stream for party_pll completed successful")
tdtestpy.copy_file (party_pll_stream_log, passed_dir)
# Delete data file if TPT export and stream ran successful
tdtestpy.delete_one_file (party_pll_data_file_full_path)
cursor.execute("MERGE into PARTY_PLL as t1 \
using (select * from sit_ldi_pll_stage.PARTY_PLL where PARTY_STATE in ('N', 'O', 'P', 'S', 'T', 'W')) as t2 \
on t1.PARTY_ID = t2.PARTY_ID \
and t1.PARTY_STATE = t2.PARTY_STATE \
and t1.PARTY_CITY = t2.PARTY_CITY \
WHEN MATCHED THEN \
UPDATE SET LOCATION_POINT = t2.LOCATION_POINT, ACTIVE_AREA = t2.ACTIVE_AREA, ACTIVE_LINES = t2.ACTIVE_LINES,\
KEY_LINE = t2.KEY_LINE, KEY_POINTS = t2.KEY_POINTS, ALL_RELATED_GEO = t2.ALL_RELATED_GEO \
WHEN NOT MATCHED THEN \
insert (PARTY_ID, PARTY_TYPE_CD, PARTY_FIRSTNAME, PARTY_LASTNAME, PARTY_STREET_ADDRESS, \
PARTY_CITY, PARTY_STATE, PARTY_ZIP, PARTY_INFO_SOURCE_TYPE_CD, PARTY_START_DT, \
PARTY_FIRST_PURCHASE_DT, LOCATION_POINT, ACTIVE_AREA, ACTIVE_LINES, KEY_LINE, KEY_POINTS, ALL_RELATED_GEO) \
values (t2.PARTY_ID, t2.PARTY_TYPE_CD, t2.PARTY_FIRSTNAME, t2.PARTY_LASTNAME, t2.PARTY_STREET_ADDRESS, \
t2.PARTY_CITY, t2.PARTY_STATE, t2.PARTY_ZIP, t2.PARTY_INFO_SOURCE_TYPE_CD, t2.PARTY_START_DT, \
t2.PARTY_FIRST_PURCHASE_DT, t2.LOCATION_POINT, t2.ACTIVE_AREA, t2.ACTIVE_LINES, t2.KEY_LINE, \
t2.KEY_POINTS, t2.ALL_RELATED_GEO)", ignoreErrors=ignore_errors)
# LOCATION_PLL table
cursor.execute("update LOCATION_PLL set CHANNEL_CD = '12345', CHAIN_CD = 'ABCD', DISTRICT_CD = '456789' \
where LOCATION_EFFECTIVE_DT between '1990-02-01' and '2000-01-01'", ignoreErrors=ignore_errors)
cursor.execute("delete LOCATION_PLL where LOCATION_EFFECTIVE_DT between '1990-02-01' and '2000-01-01'", ignoreErrors=ignore_errors)
cursor.execute("insert into LOCATION_PLL select * from sit_ldi_pll_stage.LOCATION_PLL \
where LOCATION_EFFECTIVE_DT between '1990-02-01' and '2000-01-01'", ignoreErrors=ignore_errors)
except Exception as e:
if run_result is None:
return str(e)
else:
run_result.put(str(e))
if run_result is None:
return True
else:
run_result.put(True)
def run_dml_transaction2(dbs_name, user_name, ignore_errors, run_result=None):
try:
with udaExec.connect(method="odbc", system=dbs_name, username=user_name, password=user_name) as db_connect:
with db_connect.cursor() as cursor:
# SALES_TRANSACTION_PLL table
cursor.execute("update SALES_TRANSACTION_PLL set TRANS_YEAR = '2016', TRAN_TYPE_CD = 'T' \
where TRAN_DATE between '1980-02-01' and '1990-01-01'", ignoreErrors=ignore_errors)
cursor.execute("delete SALES_TRANSACTION_PLL where TRAN_DATE between '1980-02-01' and '1990-01-01'", ignoreErrors=ignore_errors)
cursor.execute("insert into SALES_TRANSACTION_PLL select * from sit_ldi_pll_stage.SALES_TRANSACTION_PLL \
where TRAN_DATE between '1980-02-01' and '1990-01-01'", ignoreErrors=ignore_errors)
# SALES_TRANSACTION_LINE_PLL table
cursor.execute("delete SALES_TRANSACTION_LINE_PLL where LOCATION > 480", ignoreErrors=ignore_errors)
logging.info ("TPT export for sale_trans_line_pll started")
if not tdtestpy.run_single_tpt(sale_trans_line_pll_export_file, sale_trans_line_pll_export_log, directory_path, \
dbs_name, user_name, user_name, sale_trans_line_pll_data_file, tracelevel):
msg = "TPT export for sale_trans_line_pll failed, please review " + sale_trans_line_pll_export_log
logging.info ("TPT export for sale_trans_line_pll completed with failure")
tdtestpy.copy_file (sale_trans_line_pll_export_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT export for sale_trans_line_pll completed successful")
tdtestpy.copy_file (sale_trans_line_pll_export_log, passed_dir)
logging.info ("TPT stream for sale_trans_line_pll started")
if not tdtestpy.run_single_tpt(sale_trans_line_pll_stream_file, sale_trans_line_pll_stream_log, directory_path, \
dbs_name, user_name, user_name, sale_trans_line_pll_data_file, tracelevel):
msg = "TPT stream for sale_trans_line_pll failed, please review " + sale_trans_line_pll_stream_log
logging.info ("TPT stream for sale_trans_line_pll completed with failure")
tdtestpy.copy_file (sale_trans_line_pll_stream_log, faileddir)
if run_result is None:
return msg
else:
run_result.put(msg)
logging.info ("TPT stream for sale_trans_line_pll completed successful")
tdtestpy.copy_file (sale_trans_line_pll_stream_log, passed_dir)
# Delete data file if TPT export and stream ran successful
tdtestpy.delete_one_file (sale_trans_line_pll_data_file_full_path)
# PARTY_PLL table
cursor.execute("delete PARTY_PLL where PARTY_STATE BETWEEN 'C' AND 'K' \
and PARTY_START_DT < '2012-01-01'", ignoreErrors=ignore_errors)
cursor.execute("update PARTY_PLL set PARTY_START_DT = '2016-01-01' \
where PARTY_STATE BETWEEN 'C' AND 'K'", ignoreErrors=ignore_errors)
cursor.execute("MERGE into PARTY_PLL as t1 \
using (select * from sit_ldi_pll_stage.PARTY_PLL where PARTY_STATE BETWEEN 'C' AND 'K') as t2 \
on t1.PARTY_ID = t2.PARTY_ID \
and t1.PARTY_STATE = t2.PARTY_STATE \
and t1.PARTY_CITY = t2.PARTY_CITY \
WHEN MATCHED THEN \
UPDATE SET PARTY_START_DT = t2.PARTY_START_DT \
WHEN NOT MATCHED THEN \
insert (PARTY_ID, PARTY_TYPE_CD, PARTY_FIRSTNAME, PARTY_LASTNAME, PARTY_STREET_ADDRESS, \
PARTY_CITY, PARTY_STATE, PARTY_ZIP, PARTY_INFO_SOURCE_TYPE_CD, PARTY_START_DT, \
PARTY_FIRST_PURCHASE_DT, LOCATION_POINT, ACTIVE_AREA, ACTIVE_LINES, KEY_LINE, KEY_POINTS, ALL_RELATED_GEO) \
values (t2.PARTY_ID, t2.PARTY_TYPE_CD, t2.PARTY_FIRSTNAME, t2.PARTY_LASTNAME, t2.PARTY_STREET_ADDRESS, \
t2.PARTY_CITY, t2.PARTY_STATE, t2.PARTY_ZIP, t2.PARTY_INFO_SOURCE_TYPE_CD, t2.PARTY_START_DT, \
t2.PARTY_FIRST_PURCHASE_DT, t2.LOCATION_POINT, t2.ACTIVE_AREA, t2.ACTIVE_LINES, t2.KEY_LINE, \
t2.KEY_POINTS, t2.ALL_RELATED_GEO)", ignoreErrors=ignore_errors)
# LOCATION_PLL table
cursor.execute("update LOCATION_PLL set CHAIN_CD = 'This is test' \
where LOCATION_EFFECTIVE_DT between '1980-02-01' and '1990-01-01'", ignoreErrors=ignore_errors)
cursor.execute("delete LOCATION_PLL where LOCATION_EFFECTIVE_DT between '1980-02-01' and '1990-01-01'", ignoreErrors=ignore_errors)
cursor.execute("insert into LOCATION_PLL select * from sit_ldi_pll_stage.LOCATION_PLL \
where LOCATION_EFFECTIVE_DT between '1980-02-01' and '1990-01-01'", ignoreErrors=ignore_errors)
except Exception as e:
if run_result is None:
return str(e)
else:
run_result.put(str(e))
if run_result is None:
return True
else:
run_result.put(True)