def ecolab_rule(self):
# noinspection PyPep8
stmt = UploadMono.__table__.update().\
where(UploadMono.__table__.c.ContrPrice.isnot(None)).\
values({'ContrCost': UploadMono.__table__.c.ContrPrice * .9})
mysql_engine.execute(stmt)
def check_upload_table(self, checked_dict):
id_list = [int(key) for key in checked_dict.keys()]
# noinspection PyComparisonWithNone,PyPep8
update_select = self.match_table.update().where(and_(
self.match_table.c.ID.in_(id_list),
self.match_table.c.VPN_Match.isnot(None))
).values({'Selected': True})
mysql_engine.execute(update_select)
def scheduled_daily(self):
from cep_price_console.db_management.server_utils import mssql_test_creds
if mssql_test_creds():
from cep_price_console.db_management.server_utils import mysql_engine, mssql_session_maker
from cep_price_console.db_management.ddi_data_warehouse import DDIDataWarehouse, aging_definition, \
ar_history, branch, buy_line, cust_category, customer, division, inventory, inventory_history, \
invoice_detail, invoice_header, invoice_summary, invoice_variance, price_group, prod_line, product, \
purchase_order_detail, purchase_order_header, salesman, ship_to, ship_via, territory, user, vm, \
vm_category, \
warehouse, webcat
from cep_price_console.db_management.mssql_database import MSSQL_Database
table_list = [
aging_definition,
ar_history,
branch,
buy_line,
cust_category,
customer,
division,
inventory,
inventory_history,
invoice_detail,
invoice_header,
invoice_summary,
invoice_variance,
price_group,
prod_line,
product,
purchase_order_detail,
purchase_order_header,
salesman,
ship_to,
ship_via,
territory,
user,
vm,
vm_category,
warehouse,
webcat]
DDIDataWarehouse.metadata.drop_all(tables=table_list)
DDIDataWarehouse.metadata.create_all(tables=table_list)
mssql_session = mssql_session_maker()
for table_obj in MSSQL_Database.metadata.tables.values():
query_first = mssql_session.query(table_obj).first()
if query_first is not None:
query = mssql_session.query(table_obj)
# noinspection PyProtectedMember
mysql_engine.execute(
DDIDataWarehouse.metadata.tables[
"DDIDataWarehouse.{}".format(table_obj.name)].insert(),
[row._asdict() for row in query]
)
else:
self.logger.error("Some error", exc_true=True)
raise ValueError
def remove_cep_cntr_info(self):
stmt = update(UploadMono). \
values(CEPCntrNum=None,
Vendor_ID=None,
VendCntrNum=None)
mysql_engine.execute(stmt)
stmt = UploadMono.__table__.update().where(
UploadMono.__table__.c.ExpDate == self.cep_cntr_match.get(
"Expiration_Date")).values({'ExpDate': None})
mysql_engine.execute(stmt)
def schema_exists(self, schema_name):
try:
mysql_engine.execute(
"SHOW CREATE SCHEMA `{0}`;".format(schema_name)).scalar()
CntrUploadModel.logger.log(
logging.NOTSET, "Schema Exists: {0}".format(schema_name))
return True
except exc.DBAPIError:
CntrUploadModel.logger.log(
logging.NOTSET,
"Schema Does Not Exist: {0}".format(schema_name))
return False
def insert_vpn_matches_cull_zeros(self):
print(self.vpn.upload_col.name)
match_dict = self.match_query_dict()
for k, v in match_dict.items():
print(k)
print(v.name)
vpn_match_insert = self.match_table.update().where(
self.match_table.c.Vend_Part_Num == match_dict.get("VPN_Match")
).values(match_dict)
mysql_engine.execute(vpn_match_insert)
self.zero_cull(upload_column_label="VPN_Match")
self.match_type_append(upload_column_label="VPN_Match", cep_column_label="Vend_Part_Num")
def match_type_append(self, upload_column_label, cep_column_label):
# noinspection PyComparisonWithNone,PyPep8
match_type = self.match_table.update().where(and_(
self.match_table.c[upload_column_label].isnot(None),
self.match_table.c.Match_Types.isnot(None))
).values({'Match_Types': (self.match_table.c.Match_Types + ", {}".format(cep_column_label))})
mysql_engine.execute(match_type)
# noinspection PyComparisonWithNone,PyPep8
match_type = self.match_table.update().where(and_(
self.match_table.c[upload_column_label].isnot(None),
self.match_table.c.Match_Types.is_(None))
).values({'Match_Types': "{}".format(cep_column_label)})
mysql_engine.execute(match_type)
def all_match_gui_query(self):
self.create_match_table()
self.fill_match_table()
self.insert_vpn_matches_cull_zeros()
self.insert_matches_cull_zeros(upload_column_label='Vend_UPC_Num_Match', cep_column_label='Vend_UPC_Num')
self.insert_matches_cull_zeros(upload_column_label='UPC_Match', cep_column_label='UPC')
self.insert_matches_cull_zeros(upload_column_label='Mfg_Num_Match', cep_column_label='Mfg_Num')
# VPN_Match_Label = self.case_generator(cep_col=self.match_table.c.VPN_Match, label_text="VPN")
mfg_num_zero_cull = self.match_table.update().where(
self.match_table.c.Mfg_Num_Match == "0"
).values({'Mfg_Num_Match': None})
mysql_engine.execute(mfg_num_zero_cull)
# Vend_UPC_Num_Label = self.case_generator(cep_col=self.match_table.c.Vend_UPC_Num_Match, label_text="Vend_UPC")
# Mfg_Num_Match = self.case_generator(cep_col=self.match_table.c.Mfg_Num_Match, label_text="Mfg Num")
# UPC_Label = self.case_generator(cep_col=self.match_table.c.UPC_Match, label_text="UPC")
# noinspection PyCallingNonCallable
session = mysql_session_maker()
query = session.query(
self.match_table.c.ID.label("ID_1"),
self.match_table.c.ID.label("ID_2"),
self.match_table.c.Prod_Num.label("CEP_Value"),
self.match_table.c.Match_Types.label("Uploaded_Value"),
# functions.concat(VPN_Match_Label, Vend_UPC_Num_Label, Mfg_Num_Match, UPC_Label).label("Uploaded_Value"),
literal_column("'Match:'", String(length=50)).label("Match_Type"))
union_queries = [self.match_gui_query(cep_col=self.match_table.c.Vend_Part_Num,
upload_col=self.match_table.c.VPN_Match,
match_desc="Vendor Part Number Match",
id_suffix="a")]
if self.upc.upload_col is not None:
union_queries.append(self.match_gui_query(cep_col=self.match_table.c.Vend_UPC_Num,
upload_col=self.match_table.c.Vend_UPC_Num_Match,
match_desc="Vendor UPC Match",
id_suffix="b"))
union_queries.append(self.match_gui_query(cep_col=self.match_table.c.UPC,
upload_col=self.match_table.c.UPC_Match,
match_desc="UPC Match",
id_suffix="c"))
if self.mfg_part_number.upload_col is not None:
union_queries.append(self.match_gui_query(cep_col=self.match_table.c.Mfg_Num,
upload_col=self.match_table.c.Mfg_Num_Match,
match_desc="Mfg Part Number Match",
id_suffix="d"))
return query.union(*union_queries)
def populate_upload_matched_tbl(self):
self.table_create_if_not_exists("pythontest", "upload_matched",
"UploadMatched")
query = self.session.query(
UploadMono.__table__.c.UploadMono_ID,
UploadMono.__table__.c.UploadMulti_ID,
UploadMono.__table__.c.VendCntrNum,
UploadMono.__table__.c.CEPCntrNum,
UploadMono.__table__.c.Vendor_ID,
UploadMono.__table__.c.VendProdNum,
ProductMatching.__table__.c.CEP_Part_Num,
UploadMono.__table__.c.CustProdNum,
UploadMono.__table__.c.QBreak,
UploadMono.__table__.c.UOM,
UploadMono.__table__.c.ContrPrice,
UploadMono.__table__.c.ContrCost,
UploadMono.__table__.c.EffDate,
UploadMono.__table__.c.ExpDate,
UploadMono.__table__.c.Period,
).filter(
and_(
UploadMono.__table__.c.Vendor_ID ==
ProductMatching.__table__.c.Vendor_ID,
UploadMono.__table__.c.VendProdNum ==
ProductMatching.__table__.c.VendProdNum))
# ).outerjoin(ProductMatching,
# and_(UploadMono.__table__.c.Vendor_ID == ProductMatching.__table__.c.Vendor_ID,
# UploadMono.__table__.c.VendProdNum == ProductMatching.__table__.c.VendProdNum))
insert = UploadMatched.__table__.insert().from_select([
UploadMatched.__table__.c.UploadMono_ID,
UploadMatched.__table__.c.UploadMulti_ID,
UploadMatched.__table__.c.VendCntrNum,
UploadMatched.__table__.c.CEPCntrNum,
UploadMatched.__table__.c.Vendor_ID,
UploadMatched.__table__.c.VendProdNum,
UploadMatched.__table__.c.CEPProdNum,
UploadMatched.__table__.c.CustProdNum,
UploadMatched.__table__.c.QBreak, UploadMatched.__table__.c.UOM,
UploadMatched.__table__.c.ContrPrice,
UploadMatched.__table__.c.ContrCost,
UploadMatched.__table__.c.EffDate,
UploadMatched.__table__.c.ExpDate, UploadMatched.__table__.c.Period
], query)
mysql_engine.execute(insert)
def insert_matches(self, upload_column_label, cep_column_label):
# noinspection PyCallingNonCallable
session = mysql_session_maker()
match_dict = self.match_query_dict()
# noinspection PyPep8
match_query = session.query(*match_dict.values()).join(
self.table,
self.match_table.c[cep_column_label] == match_dict.get(upload_column_label)
).filter(and_(match_dict.get(upload_column_label).isnot(None),
match_dict.get(upload_column_label) != "",
match_dict.get(upload_column_label) != "0",
not_(session.query(exists().where(and_(
self.match_table.c.Prod_Num == self.match_table.c.Prod_Num,
self.match_table.c.VPN_Match == match_dict.get('VPN_Match')))).scalar())))
insert = self.match_table.insert().from_select([*match_dict.keys()], match_query)
mysql_engine.execute(insert)
def append_cep_cntr_info(self):
import cep_price_console.db_management.ARW_PRF_Mapping as ARW_PRF_Mapping
cep_cntr = self.session.query(
ARW_PRF_Mapping.cntr_header_01_current.__table__.c.Cntr_Num,
ARW_PRF_Mapping.cntr_header_01_current.__table__.c.Vend_Cntr_Num,
ARW_PRF_Mapping.cntr_header_01_current.__table__.c.Vend_Num
).filter(ARW_PRF_Mapping.cntr_header_01_current.__table__.c.Cntr_Num ==
self.cep_cntr_match.get("CEP_Contract_Number")).first()
stmt = update(UploadMono). \
values(CEPCntrNum=cep_cntr.Cntr_Num,
Vendor_ID=cep_cntr.Vend_Num)
mysql_engine.execute(stmt)
# noinspection PyPep8
stmt = update(UploadMono). \
where(UploadMono.__table__.c.VendCntrNum.is_(None)). \
values(VendCntrNum=cep_cntr.Vend_Cntr_Num)
mysql_engine.execute(stmt)
# noinspection PyPep8
stmt = UploadMono.__table__.update().where(
UploadMono.__table__.c.ExpDate.is_(None)).values(
{'ExpDate': self.cep_cntr_match.get("Expiration_Date")})
mysql_engine.execute(stmt)
def populate_prod_sel_tbl(self):
mysql_session_maker.close_all()
self.session = mysql_session_maker()
self.table_create_if_not_exists("pythontest", "cntr_prodmatching",
"ProductMatching")
import cep_price_console.db_management.ARW_PRF_Mapping as ARW_PRF_Mapping
query_1 = self.session.query(
UploadMono.__table__.c.Vendor_ID,
UploadMono.__table__.c.VendProdNum,
UploadMono.__table__.c.CustProdNum,
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Prod_Num
).outerjoin(ARW_PRF_Mapping.prod_vend_01_current.__table__,
and_(
UploadMono.__table__.c.Vendor_ID ==
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Num,
UploadMono.__table__.c.VendProdNum ==
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Part_Num)). \
distinct(UploadMono.__table__.c.VendProdNum)
insert = ProductMatching.__table__.insert().from_select([
ProductMatching.__table__.c.Vendor_ID,
ProductMatching.__table__.c.VendProdNum,
ProductMatching.__table__.c.CustProdNum,
ProductMatching.__table__.c.CEP_Part_Num
], query_1)
mysql_engine.execute(insert)
self.session.commit()
query_2 = self.session.query(ProductMatching).all()
for row in query_2:
update_stmt = update(ProductMatching). \
where(ProductMatching.__table__.c.VendProdNum == row.VendProdNum). \
values(Count=self.session.query(func.count(ProductMatching.__table__.c.VendProdNum))
.filter(ProductMatching.__table__.c.VendProdNum == row.VendProdNum).scalar()
)
mysql_engine.execute(update_stmt)
def upload_contract(self):
self.table_create_if_not_exists("pythontest", "upload_multi",
"UploadMulti")
dict_of_lists = {}
dict_of_static = {}
upload_id_list = list(self.upload_map_dict.keys())
for upload_id in reversed(upload_id_list):
upload_obj = self.upload_map_dict[upload_id]
if upload_obj.ws_col_obj is not None:
CntrUploadModel.logger.log(
logging.DEBUG,
"Upload Mapping with a Worksheet Column: {0}. Appending to list."
.format(str(upload_id)))
upload_col_cell_list = list(upload_obj.ws_col_obj.col_cells)
upload_col_cell_list.sort(
key=lambda x: int(x.row_num_one_indexed))
dict_of_lists[upload_id] = upload_col_cell_list
elif upload_obj.static_value is not None:
CntrUploadModel.logger.log(
logging.DEBUG,
"Upload Mapping with a static value: {0}. Appending to list."
.format(str(upload_id)))
dict_of_static[upload_id] = upload_obj.static_value
else:
CntrUploadModel.logger.log(
logging.NOTSET,
"Upload Mapping without a Worksheet Column: {0}. Skipping."
.format(str(upload_id)))
if bool(dict_of_lists):
dict_upload_obj = {}
row_num_list = list(
sorted(self.wb.row_dict.keys(), key=lambda x: int(x)))
for row_num in reversed(row_num_list):
upload_dict = {}
for upload_id, upload_col_cell_list in dict_of_lists.items():
for cell in reversed(upload_col_cell_list):
if str(row_num) == str(cell.row_num_one_indexed):
upload_dict[upload_id] = cell.formatted_value
CntrUploadModel.logger.log(
logging.NOTSET,
"Row number: {0}, Cell Row Number: {1}, Upload ID: {2}, Value: {3}. Removing cell"
.format(str(row_num),
str(cell.row_num_one_indexed),
str(upload_id),
str(cell.formatted_value)))
upload_col_cell_list.remove(cell)
break
else:
CntrUploadModel.logger.log(
logging.NOTSET,
"No Match: Row number: {0}, Cell Row Number: {1}, Upload ID: {2}, Value: {3}."
.format(str(row_num),
str(cell.row_num_one_indexed),
str(upload_id),
str(cell.formatted_value)))
if bool(dict_of_static):
for static_upload_id, static_upload_value in dict_of_static.items(
):
upload_dict[static_upload_id] = static_upload_value
CntrUploadModel.logger.log(
logging.DEBUG, "Upload Dictionary: {0}".format("".join(
str(key) + str(value)
for key, value in upload_dict.items())))
upload_obj = UploadMulti(**upload_dict)
CntrUploadModel.logger.log(
logging.NOTSET, "Upload Object: {0}".format(upload_obj))
# Append this object to the object dictionary
dict_upload_obj[row_num] = upload_obj
CntrUploadModel.logger.log(logging.DEBUG,
"Create a list of dictionary values")
list_upload_obj = list(dict_upload_obj.values())
CntrUploadModel.logger.log(
logging.DEBUG, "Add the list of new objects to the session")
self.session.add_all(list_upload_obj)
self.session.commit()
# Delete empty rows from the multi-period landing
CntrUploadModel.logger.log(
logging.DEBUG,
"Delete empty rows from the multi-period landing")
# noinspection PyComparisonWithNone,PyComparisonWithNone,PyComparisonWithNone,PyComparisonWithNone,
# PyComparisonWithNone,PyComparisonWithNone,PyComparisonWithNone,PyComparisonWithNone,PyComparisonWithNone,
# PyComparisonWithNone,PyComparisonWithNone,PyComparisonWithNone,PyPep8
# noinspection PyPep8
del_stmt = UploadMulti.__table__.delete().where(
and_(UploadMulti.__table__.c.P1_ContrPrice.is_(None),
UploadMulti.__table__.c.P1_ContrCost.is_(None),
UploadMulti.__table__.c.P1_EffDate.is_(None),
UploadMulti.__table__.c.P1_ExpDate.is_(None),
UploadMulti.__table__.c.P2_ContrPrice.is_(None),
UploadMulti.__table__.c.P2_ContrCost.is_(None),
UploadMulti.__table__.c.P2_EffDate.is_(None),
UploadMulti.__table__.c.P2_ExpDate.is_(None),
UploadMulti.__table__.c.P3_ContrPrice.is_(None),
UploadMulti.__table__.c.P3_ContrCost.is_(None),
UploadMulti.__table__.c.P3_EffDate.is_(None),
UploadMulti.__table__.c.P3_ExpDate.is_(None)))
mysql_engine.execute(del_stmt)
CntrUploadModel.logger.log(
logging.DEBUG,
"GP Contracts often have an 'catch all' expiration date in the "
"final period that should be applied to another period")
# noinspection PyPep8
stmt = UploadMulti.__table__.update().where(
(and_(UploadMulti.__table__.c.P1_ContrPrice.is_(None),
UploadMulti.__table__.c.P1_ContrCost.is_(None),
UploadMulti.__table__.c.P1_EffDate.is_(None)))).values(
{'P1_ExpDate': None})
mysql_engine.execute(stmt)
CntrUploadModel.logger.log(logging.DEBUG, "Misplaced exp date 1")
# noinspection PyPep8
stmt = UploadMulti.__table__.update().where(
(and_(UploadMulti.__table__.c.P1_EffDate.isnot(None),
UploadMulti.__table__.c.P1_ExpDate.is_(None),
UploadMulti.__table__.c.P2_EffDate.is_(None),
UploadMulti.__table__.c.P2_ExpDate.isnot(None),
UploadMulti.__table__.c.P3_EffDate.is_(None),
UploadMulti.__table__.c.P3_ExpDate.is_(None)))).values({
'P1_ExpDate':
UploadMulti.__table__.c.P2_ExpDate,
'P2_ExpDate':
None
})
mysql_engine.execute(stmt)
CntrUploadModel.logger.log(logging.DEBUG, "Misplaced exp date 2")
# noinspection PyPep8
stmt = UploadMulti.__table__.update().where((and_(
UploadMulti.__table__.c.P1_EffDate.isnot(None),
UploadMulti.__table__.c.P1_ExpDate.is_(None),
UploadMulti.__table__.c.P2_EffDate.is_(None),
UploadMulti.__table__.c.P2_ExpDate.is_(None),
UploadMulti.__table__.c.P3_EffDate.is_(None),
UploadMulti.__table__.c.P3_ExpDate.isnot(None)))).values({
'P1_ExpDate':
UploadMulti.__table__.c.P3_ExpDate,
'P3_ExpDate':
None
})
mysql_engine.execute(stmt)
CntrUploadModel.logger.log(logging.DEBUG, "Misplaced exp date 3")
# noinspection PyPep8
stmt = UploadMulti.__table__.update().where((and_(
UploadMulti.__table__.c.P1_EffDate.is_(None),
UploadMulti.__table__.c.P1_ExpDate.is_(None),
UploadMulti.__table__.c.P2_EffDate.isnot(None),
UploadMulti.__table__.c.P2_ExpDate.is_(None),
UploadMulti.__table__.c.P3_EffDate.is_(None),
UploadMulti.__table__.c.P3_ExpDate.isnot(None)))).values({
'P2_ExpDate':
UploadMulti.__table__.c.P3_ExpDate,
'P3_ExpDate':
None
})
mysql_engine.execute(stmt)
self.session.commit()
mysql_session_maker.close_all()
self.session = mysql_session_maker()
self.table_create_if_not_exists("pythontest", "upload_mono",
"UploadMono")
# noinspection PyPep8
period_1 = self.session.query(
UploadMulti.__table__.c.ID.label("Upload_ID"),
UploadMulti.__table__.c.VendCntrNum.label("VendCntrNum"),
UploadMulti.__table__.c.VendProdNum.label("VendProdNum"),
UploadMulti.__table__.c.CustProdNum.label("CustProdNum"),
UploadMulti.__table__.c.QBreak.label("QBreak"),
UploadMulti.__table__.c.UOM.label("UOM"),
UploadMulti.__table__.c.P1_ContrPrice.label("ContrPrice"),
UploadMulti.__table__.c.P1_ContrCost.label("ContrCost"),
UploadMulti.__table__.c.P1_EffDate.label("EffDate"),
UploadMulti.__table__.c.P1_ExpDate.label("ExpDate")).filter(
not_(
and_(UploadMulti.__table__.c.P1_ContrPrice.is_(None),
UploadMulti.__table__.c.P1_ContrCost.is_(None),
UploadMulti.__table__.c.P1_EffDate.is_(None),
UploadMulti.__table__.c.P1_ExpDate.is_(None))))
insert_1 = UploadMono.__table__.insert().from_select([
UploadMono.__table__.c.UploadMulti_ID,
UploadMono.__table__.c.VendCntrNum,
UploadMono.__table__.c.VendProdNum,
UploadMono.__table__.c.CustProdNum,
UploadMono.__table__.c.QBreak, UploadMono.__table__.c.UOM,
UploadMono.__table__.c.ContrPrice,
UploadMono.__table__.c.ContrCost,
UploadMono.__table__.c.EffDate, UploadMono.__table__.c.ExpDate
], period_1)
mysql_engine.execute(insert_1)
# noinspection PyPep8
period_2 = self.session.query(
UploadMulti.__table__.c.ID.label("Upload_ID"),
UploadMulti.__table__.c.VendCntrNum.label("VendCntrNum"),
UploadMulti.__table__.c.VendProdNum.label("VendProdNum"),
UploadMulti.__table__.c.CustProdNum.label("CustProdNum"),
UploadMulti.__table__.c.QBreak.label("QBreak"),
UploadMulti.__table__.c.UOM.label("UOM"),
UploadMulti.__table__.c.P2_ContrPrice.label("ContrPrice"),
UploadMulti.__table__.c.P2_ContrCost.label("ContrCost"),
UploadMulti.__table__.c.P2_EffDate.label("EffDate"),
UploadMulti.__table__.c.P2_ExpDate.label("ExpDate")).filter(
not_(
and_(UploadMulti.__table__.c.P2_ContrPrice.is_(None),
UploadMulti.__table__.c.P2_ContrCost.is_(None),
UploadMulti.__table__.c.P2_EffDate.is_(None),
UploadMulti.__table__.c.P2_ExpDate.is_(None))))
insert_2 = UploadMono.__table__.insert().from_select([
UploadMono.__table__.c.UploadMulti_ID,
UploadMono.__table__.c.VendCntrNum,
UploadMono.__table__.c.VendProdNum,
UploadMono.__table__.c.CustProdNum,
UploadMono.__table__.c.QBreak, UploadMono.__table__.c.UOM,
UploadMono.__table__.c.ContrPrice,
UploadMono.__table__.c.ContrCost,
UploadMono.__table__.c.EffDate, UploadMono.__table__.c.ExpDate
], period_2)
mysql_engine.execute(insert_2)
# noinspection PyPep8
period_3 = self.session.query(
UploadMulti.__table__.c.ID.label("Upload_ID"),
UploadMulti.__table__.c.VendCntrNum.label("VendCntrNum"),
UploadMulti.__table__.c.VendProdNum.label("VendProdNum"),
UploadMulti.__table__.c.CustProdNum.label("CustProdNum"),
UploadMulti.__table__.c.QBreak.label("QBreak"),
UploadMulti.__table__.c.UOM.label("UOM"),
UploadMulti.__table__.c.P3_ContrPrice.label("ContrPrice"),
UploadMulti.__table__.c.P3_ContrCost.label("ContrCost"),
UploadMulti.__table__.c.P3_EffDate.label("EffDate"),
UploadMulti.__table__.c.P3_ExpDate.label("ExpDate")).filter(
not_(
and_(UploadMulti.__table__.c.P3_ContrPrice.is_(None),
UploadMulti.__table__.c.P3_ContrCost.is_(None),
UploadMulti.__table__.c.P3_EffDate.is_(None),
UploadMulti.__table__.c.P3_ExpDate.is_(None))))
insert_3 = UploadMono.__table__.insert().from_select([
UploadMono.__table__.c.UploadMulti_ID,
UploadMono.__table__.c.VendCntrNum,
UploadMono.__table__.c.VendProdNum,
UploadMono.__table__.c.CustProdNum,
UploadMono.__table__.c.QBreak, UploadMono.__table__.c.UOM,
UploadMono.__table__.c.ContrPrice,
UploadMono.__table__.c.ContrCost,
UploadMono.__table__.c.EffDate, UploadMono.__table__.c.ExpDate
], period_3)
mysql_engine.execute(insert_3)
del_stmt = UploadMono.__table__.delete().where(
UploadMono.__table__.c.ExpDate < datetime.date.today())
mysql_engine.execute(del_stmt)
# noinspection PyPep8
del_stmt = UploadMono.__table__.delete().where(
UploadMono.__table__.c.VendProdNum.is_(None))
mysql_engine.execute(del_stmt)
self.session.commit()
# noinspection PyPep8
null_period = self.session.query(UploadMono.__table__.c.UploadMono_ID).\
filter(UploadMono.__table__.c.Period.is_(None))
period_cnt = 0
while null_period.count() > 0:
period_cnt += 1
print("Len: {0}".format(null_period.count()))
for _ in null_period.all():
print("Row:")
for field in _:
print(field)
# noinspection PyPep8
no_period = \
select([UploadMono.__table__.c.VendCntrNum,
UploadMono.__table__.c.VendProdNum,
UploadMono.__table__.c.QBreak,
UploadMono.__table__.c.UOM,
func.min(UploadMono.__table__.c.EffDate).label('mindate')])\
.group_by(UploadMono.__table__.c.VendCntrNum,
UploadMono.__table__.c.VendProdNum,
UploadMono.__table__.c.QBreak,
UploadMono.__table__.c.UOM)\
.where(UploadMono.__table__.c.Period.is_(None))\
.alias("no_period")
for _ in mysql_engine.execute(no_period):
print(_)
# print("Period {0}".format(period_cnt))
# for _ in no_period:
# print(_) cast(UploadMono.__table__.c.EffDate, Date) == cast(no_period.c.mindate, Date)
# ,
# UploadMono.__table__.c.VendProdNum == no_period.c.VendProdNum,
# UploadMono.__table__.c.QBreak == no_period.c.QBreak,
# UploadMono.__table__.c.UOM == no_period.c.UOM
# noinspection PyPep8
append_period = UploadMono.__table__.update().where(and_(
or_(
and_(
UploadMono.__table__.c.VendCntrNum.is_(None),
no_period.c.VendCntrNum.is_(None)),
UploadMono.__table__.c.VendCntrNum == no_period.c.VendCntrNum),
or_(
and_(
UploadMono.__table__.c.VendProdNum.is_(None),
no_period.c.VendProdNum.is_(None)),
UploadMono.__table__.c.VendProdNum == no_period.c.VendProdNum),
or_(
and_(
UploadMono.__table__.c.QBreak.is_(None),
no_period.c.QBreak.is_(None)),
UploadMono.__table__.c.QBreak == no_period.c.QBreak),
or_(
and_(
UploadMono.__table__.c.UOM.is_(None),
no_period.c.UOM.is_(None)),
UploadMono.__table__.c.UOM == no_period.c.UOM),
or_(
and_(
UploadMono.__table__.c.EffDate.is_(None),
no_period.c.mindate.is_(None)),
UploadMono.__table__.c.EffDate == no_period.c.mindate))).\
values(Period=period_cnt)
mysql_engine.execute(append_period)
self.session.commit()
if period_cnt > 1:
print("period count over 1")
monoalias = aliased(UploadMono)
# noinspection PyPep8
exp_date_query = self.session.query(
UploadMono.__table__.c.UploadMono_ID,
UploadMono.__table__.c.VendCntrNum,
UploadMono.__table__.c.VendProdNum,
UploadMono.__table__.c.QBreak,
UploadMono.__table__.c.UOM,
UploadMono.__table__.c.ExpDate,
monoalias.EffDate,
UploadMono.__table__.c.Period,
monoalias.Period,
).join(
monoalias,
and_(
or_(
monoalias.VendCntrNum ==
UploadMono.__table__.c.VendCntrNum,
and_(
monoalias.VendCntrNum.is_(None),
UploadMono.__table__.c.VendCntrNum.is_(
None))),
or_(
monoalias.VendProdNum ==
UploadMono.__table__.c.VendProdNum,
and_(
monoalias.VendProdNum.is_(None),
UploadMono.__table__.c.VendProdNum.is_(
None))),
or_(
monoalias.QBreak ==
UploadMono.__table__.c.QBreak,
and_(monoalias.QBreak.is_(None),
UploadMono.__table__.c.QBreak.is_(None))),
or_(
monoalias.UOM == UploadMono.__table__.c.UOM,
and_(monoalias.UOM.is_(None),
UploadMono.__table__.c.UOM.is_(None))),
or_(
monoalias.Period ==
UploadMono.__table__.c.Period + 1,
and_(monoalias.Period.is_(None),
UploadMono.__table__.c.Period.is_(
None))))).all()
for row in exp_date_query:
if row.EffDate is not None:
CntrUploadModel.logger.log(
logging.DEBUG,
"Row: {{0}}".format(row.EffDate))
stmt = UploadMono.__table__.update().where(
UploadMono.__table__.c.UploadMono_ID ==
row.UploadMono_ID).values({
'ExpDate':
row.EffDate - datetime.timedelta(days=1)
})
mysql_engine.execute(stmt)
self.session.commit()
else:
CntrUploadModel.logger.error(
"No Upload Mapping with a Worksheet Column. Function skipped.")
def zero_cull(self, upload_column_label):
zero_cull = self.match_table.update().where(
self.match_table.c[upload_column_label] == "0"
).values({upload_column_label: None})
mysql_engine.execute(zero_cull)
def fill_match_table(self):
import cep_price_console.db_management.ARW_PRF_Mapping as ARW_PRF_Mapping
# noinspection PyCallingNonCallable
session = mysql_session_maker()
if self.all_primary_vend:
all_primary_query = session.query(
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Prod_Num.label("Prod_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Part_Num.label("Vend_Part_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_UPC_Num.label("Vend_UPC_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Num.label("Vend_Num"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Mfg_No.label("Mfg_Num"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.UPC.label("UPC"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Primary_Vend.label("Primary_Vend")
).join(
ARW_PRF_Mapping.prod_main_01_current.__table__,
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Prod_Num ==
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Prod_Num
).filter(
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Num ==
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Primary_Vend
)
primary_insert = self.match_table.insert().from_select([
"Prod_Num",
"Vend_Part_Num",
"Vend_UPC_Num",
"Vend_Num",
"Mfg_Num",
"UPC",
"Primary_Vend",
"C1_Cost",
"Display_UOM",
"Status"
], all_primary_query)
mysql_engine.execute(primary_insert)
elif self.all_vend_chk_box:
all_vendors_query = session.query(
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Prod_Num.label("Prod_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Part_Num.label("Vend_Part_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_UPC_Num.label("Vend_UPC_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Num.label("Vend_Num"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Mfg_No.label("Mfg_Num"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.UPC.label("UPC"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Primary_Vend.label("Primary_Vend"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.C1_Cost.label("C1_Cost"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Display_UOM.label("Display_UOM"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Status.label("Status")
).join(
ARW_PRF_Mapping.prod_main_01_current.__table__,
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Prod_Num ==
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Prod_Num
)
all_vendors_insert = self.match_table.insert().from_select([
"Prod_Num",
"Vend_Part_Num",
"Vend_UPC_Num",
"Vend_Num",
"Mfg_Num",
"UPC",
"Primary_Vend",
"C1_Cost",
"Display_UOM",
"Status"
], all_vendors_query)
mysql_engine.execute(all_vendors_insert)
elif self.sel_list:
for parameter in self.sel_list:
vend_num = parameter.var.get()[:5]
primary = False
if 'selected' in parameter.prim_chk_box.state():
primary = True
param_query = session.query(
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Prod_Num.label("Prod_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Part_Num.label("Vend_Part_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_UPC_Num.label("Vend_UPC_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Num.label("Vend_Num"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Mfg_No.label("Mfg_Num"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.UPC.label("UPC"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Primary_Vend.label("Primary_Vend"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.C1_Cost.label("C1_Cost"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Display_UOM.label("Display_UOM"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Status.label("Status")
).join(
ARW_PRF_Mapping.prod_main_01_current.__table__,
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Prod_Num ==
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Prod_Num
).filter(and_(
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Primary_Vend == vend_num,
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Num == vend_num)
)
secondary = False
if 'selected' in parameter.sec_chk_box.state():
secondary = True
param_query = session.query(
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Prod_Num.label("Prod_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Part_Num.label("Vend_Part_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_UPC_Num.label("Vend_UPC_Num"),
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Num.label("Vend_Num"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Mfg_No.label("Mfg_Num"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.UPC.label("UPC"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Primary_Vend.label("Primary_Vend"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.C1_Cost.label("C1_Cost"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Display_UOM.label("Display_UOM"),
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Status.label("Status")
).join(
ARW_PRF_Mapping.prod_main_01_current.__table__,
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Prod_Num ==
ARW_PRF_Mapping.prod_main_01_current.__table__.c.Prod_Num
).filter(
ARW_PRF_Mapping.prod_vend_01_current.__table__.c.Vend_Num == vend_num
)
print("Vend ID: {}, Primary: {}, Secondary: {}".format(vend_num, primary, secondary))
insert = self.match_table.insert().from_select([
"Prod_Num",
"Vend_Part_Num",
"Vend_UPC_Num",
"Vend_Num",
"Mfg_Num",
"UPC",
"Primary_Vend",
"C1_Cost",
"Display_UOM",
"Status"
], param_query)
mysql_engine.execute(insert)
def schema_create(self, schema_name):
CntrUploadModel.logger.log(logging.NOTSET,
"Creating Schema: {0}".format(schema_name))
mysql_engine.execute(CreateSchema(schema_name))
def delete_unchecked_prod_matches(self, prod_sel_lst):
delete = ProductMatching.__table__.delete().where(
~ProductMatching.__table__.c.Prod_Match_ID.in_(prod_sel_lst))
mysql_engine.execute(delete)
