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)