import pandas as pd """CODE TO SWITCH BETWEEN ORIGINAL FLAVOR GAMUT AND GWS""" #from gamut_query import GamutQuery from GWS_query import GWSQuery #from GWS_TOOLBOX_query import GWSQuery """ """ from grainger_query import GraingerQuery from queries_WS import gws_hier_query, gws_attr_query, STEP_ETL_query, \ gamut_attr_query, grainger_attr_ETL_query, ETL_nodes_query import WS_query_code as q import time """CODE TO SWITCH BETWEEN ORIGINAL FLAVOR GAMUT, TOOLBOX, AND GWS""" #gws = GamutQuery() gws = GWSQuery() """ """ gcom = GraingerQuery() pd.options.mode.chained_assignment = None #variation of the basic query designed to include discontinued items # EXCLUDE: (1) discontinued by Grainger (DG); (2) discontinued by Vendor (DV); (3) Customer Specific Inventory (CS) # EXCLUDE: RMC = L15 (Zoro only products), RMC = blank (Canada only, Mexico only products) # EXCLUDE: Supplier No = 20009997, 20201557, 20201186 (7-Combo products) def gws_data(df): sku_list = df['Grainger_SKU'].tolist() gws_skus = ", ".join("'" + str(i) + "'" for i in sku_list) """CODE TO SWITCH BETWEEN ORIGINAL FLAVOR GAMUT AND GWS""" # gws_df = gamut.gamut_q(gamut_hier_query, 'tprod."supplierSku"', gws_skus)
Created on Tue Apr 16 17:00:31 2019 @author: xcxg109 """ import settings_NUMERIC as settings import pandas as pd from GWS_query import GWSQuery from grainger_query import GraingerQuery import file_data_GWS as fd from queries_WS import grainger_basic_query, gws_hier_query, STEP_ETL_query import WS_query_code as q import time gws = GWSQuery() gcom = GraingerQuery() gws_basic_query=""" WITH RECURSIVE tax AS ( SELECT id, name, ARRAY[]::INTEGER[] AS ancestors, ARRAY[]::character varying[] AS ancestor_names FROM taxonomy_category as category WHERE "parentId" IS NULL AND category.deleted = false UNION ALL SELECT category.id,
, tprod."categoryId" AS "PIM_Node_ID" , tax.name as "PIM_Node_Name" , tprod."gtPartNumber" as "WS_SKU" , tprod.id as "PIM_SKU_ID" , tprod.status as "PIM_Status" FROM taxonomy_product tprod INNER JOIN tax ON tax.id = tprod."categoryId" -- AND tprod.status = 3 WHERE {} IN ({}) """ gcom = GraingerQuery() gws = GWSQuery() def gws_data(grainger_df): gws_sku_list = pd.DataFrame() sku_list = grainger_df['STEP_SKU'].tolist() if len(sku_list)>4000: num_lists = round(len(sku_list)/4000, 0) num_lists = int(num_lists) if num_lists == 1: num_lists = 2
SELECT gws_category_id , step_category_ids FROM pi_mappings ) pi FULL OUTER JOIN taxonomy_product tprod ON tprod."categoryId" = pi.gws_category_id INNER JOIN tax ON tax.id = tprod."categoryId" WHERE {}= ANY (pi.step_category_ids) """ gcom = GraingerQuery() gws = GWSQuery() def search_type(): """choose which type of data to import -- impacts which querries will be run""" while True: try: data_type = input("Search by: \n1. Grainger Blue (node) \n2. GWS ") if data_type in [ '1', 'node', 'Node', 'NODE', 'blue', 'Blue', 'BLUE', 'b', 'B' ]: data_type = 'grainger_query' break elif data_type in ['2', 'gws', 'Gws', 'GWS', 'g', 'G']:
# -*- coding: utf-8 -*- """ Created on Tue Feb 23 16:37:27 2021 @author: xcxg109 """ from GWS_query import GWSQuery from grainger_query import GraingerQuery import file_data_GWS as fd import pandas as pd import numpy as np import settings_NUMERIC as settings import time gcom = GraingerQuery() gws = GWSQuery() STEP_query = """ SELECT item.MATERIAL_NO AS STEP_SKU , cat.SEGMENT_ID AS Segment_ID , cat.SEGMENT_NAME AS Segment_Name , cat.FAMILY_ID AS Family_ID , cat.FAMILY_NAME AS Family_Name , cat.CATEGORY_ID AS Category_ID , cat.CATEGORY_NAME AS Category_Name , item.SUPPLIER_NO , item.RELATIONSHIP_MANAGER_CODE , item.PM_CODE , item.SALES_STATUS , item.PRICING_FLAG , item.PRICER_FIRST_EFFECTIVE_DATE
""" FLIP between GWS and sandbox""" #from GWS_query import GWSQuery from GWS_TOOLBOX_query import GWS_TOOLBOX_Query """""" from grainger_query import GraingerQuery from queries_PIM import gamut_hier_query import pandas as pd import file_data as fd import settings import time gcom = GraingerQuery() """ FLIP between GWS and sandbox""" #gamut = GWSQuery() gamut = GWS_TOOLBOX_Query() """ FLIP between GWS and sandbox""" #variation of the basic query designed to include discontinued items grainger_discontinued_query=""" SELECT item.MATERIAL_NO AS Grainger_SKU , cat.SEGMENT_ID AS Segment_ID , cat.SEGMENT_NAME AS Segment_Name , cat.FAMILY_ID AS Family_ID
@author: xcxg109 """ import pandas as pd import numpy as np import WS_query_code as q from grainger_query import GraingerQuery import file_data_GWS as fd import settings_NUMERIC as settings import time pd.options.mode.chained_assignment = None gcom = GraingerQuery() basic_hier_query=""" SELECT cat.CATEGORY_ID AS Category_ID , item.SUPPLIER_NO AS Supplier_ID , item.MATERIAL_NO AS Grainger_SKU FROM PRD_DWH_VIEW_MTRL.ITEM_V AS item INNER JOIN PRD_DWH_VIEW_MTRL.CATEGORY_V AS cat ON cat.CATEGORY_ID = item.CATEGORY_ID AND item.DELETED_FLAG = 'N' AND item.PRODUCT_APPROVED_US_FLAG = 'Y' AND item.PM_CODE NOT IN ('R9', 'R4') AND item.PM_CODE NOT IN ('UA','UB','UC','UD','UE','UF','UG','UH','UJ','UK','UL','UM','UN','UT','UZ')
""" Created on Tue Mar 5 12:40:34 2019 @author: xcxg109 """ import pandas as pd import numpy as np import re from grainger_query import GraingerQuery from GWS_query import GWSQuery from queries_WS import grainger_attr_query, grainger_value_query, ws_attr_values import file_data_GWS as fd import settings_NUMERIC as settings import time gcom = GraingerQuery() gws = GWSQuery() gws_attr_values=""" WITH RECURSIVE tax AS ( SELECT id, name, ARRAY[]::INTEGER[] AS ancestors, ARRAY[]::character varying[] AS ancestor_names FROM taxonomy_category as category WHERE "parentId" IS NULL AND category.deleted = false UNION ALL SELECT category.id,
ON cat.CATEGORY_ID = item_attr.CATEGORY_ID AND item_attr.DELETED_FLAG = 'N' INNER JOIN PRD_DWH_VIEW_MTRL.MAT_DESCRIPTOR_V AS attr ON attr.DESCRIPTOR_ID = item_attr.DESCRIPTOR_ID INNER JOIN PRD_DWH_VIEW_LMT.Prod_Yellow_Heir_Class_View AS yellow ON yellow.PRODUCT_ID = item.MATERIAL_NO FULL OUTER JOIN PRD_DWH_VIEW_LMT.Yellow_Heir_Flattend_view AS flat ON yellow.PROD_CLASS_ID = flat.Heir_End_Class_Code WHERE {} IN ({}) """ gcom = GraingerQuery() def data_out(df): if df.empty == False: outfile = 'C:/Users/xcxg109/NonDriveFiles/STEP_Blue_Yellow_lastChance.xlsx' writer = pd.ExcelWriter(outfile, engine='xlsxwriter') df.to_excel(writer, sheet_name="Category", startrow=0, startcol=0, index=False)
""" Created on Tue Oct 1 10:57:44 2019 @author: xcxg109 """ import file_data_att as fd import settings import pandas as pd from gamut_query_15 import GamutQuery_15 from grainger_query import GraingerQuery from queries_PIM import gamut_attr_query, grainger_basic_query import query_code as q gamut = GamutQuery_15() gcom = GraingerQuery() def gamut_data(grainger_df): sku_list = grainger_df['Grainger_SKU'].tolist() gamut_skus = ", ".join("'" + str(i) + "'" for i in sku_list) gamut_df = gamut.gamut_q15(gamut_attr_query, 'tprod."supplierSku"', gamut_skus) return gamut_df gamut_df = pd.DataFrame() grainger_df = pd.DataFrame() search_level = 'tax.id'
import pandas as pd """CODE TO SWITCH BETWEEN ORIGINAL FLAVOR GAMUT AND GWS""" #from gamut_query import GamutQuery from GWS_query import GWSQuery #from GWS_TOOLBOX_query import GWSQuery """ """ from grainger_query import GraingerQuery from queries_NUMERIC import gws_hier_query, gws_attr_query, \ gamut_attr_query, grainger_attr_ETL_query import query_code as q import time """CODE TO SWITCH BETWEEN ORIGINAL FLAVOR GAMUT, TOOLBOX, AND GWS""" #gws = GamutQuery() gws = GWSQuery() """ """ gcom = GraingerQuery() STEP_ETL_query = """ SELECT item.MATERIAL_NO AS Grainger_SKU , cat.SEGMENT_ID AS Segment_ID , cat.SEGMENT_NAME AS Segment_Name , cat.FAMILY_ID AS Family_ID , cat.FAMILY_NAME AS Family_Name , cat.CATEGORY_ID AS Category_ID , cat.CATEGORY_NAME AS Category_Name , item.RELATIONSHIP_MANAGER_CODE , item.PM_CODE , item.SALES_STATUS FROM PRD_DWH_VIEW_LMT.ITEM_V AS item
""" import file_data_att as fd import settings import pandas as pd from gamut_query import GamutQuery from grainger_query import GraingerQuery from queries_PIM import gamut_hier_query, grainger_basic_query, \ grainger_discontinued_query import query_code as q import time gamut = GamutQuery() gcom = GraingerQuery() def gamut_data(grainger_df): sku_list = grainger_df['Grainger_SKU'].tolist() gamut_skus = ", ".join("'" + str(i) + "'" for i in sku_list) gamut_df = gamut.gamut_q(gamut_hier_query, 'tprod."supplierSku"', gamut_skus) return gamut_df def grainger_data(gamut_df): sku_list = gamut_df['supplierSku'].tolist() grainger_skus = ", ".join("'" + str(i) + "'" for i in sku_list)
# -*- coding: utf-8 -*- """ Created on Tue Mar 9 22:37:20 2021 @author: xcxg109 """ import pandas as pd import numpy as np from GWS_query import GWSQuery from grainger_query import GraingerQuery import file_data_GWS as fd import time gcom = GraingerQuery() gws = GWSQuery() grainger_value_query = """ SELECT item.MATERIAL_NO AS STEP_SKU , cat.SEGMENT_ID AS Segment_ID , cat.SEGMENT_NAME AS Segment_Name , cat.FAMILY_ID As Family_ID , cat.FAMILY_NAME AS Family_Name , cat.CATEGORY_ID AS Category_ID , cat.CATEGORY_NAME AS Category_Name , item.SUPPLIER_NO , item.RELATIONSHIP_MANAGER_CODE , item.PM_CODE , item.SALES_STATUS , item.PRICING_FLAG , item.PRICER_FIRST_EFFECTIVE_DATE
# -*- coding: utf-8 -*- """ Created on Tue Nov 10 20:34:02 2020 @author: xcxg109 """ import pandas as pd import WS_query_code as q from grainger_query import GraingerQuery import file_data_GWS as fd import settings_NUMERIC as settings import time gcom = GraingerQuery() basic_hier_query=""" SELECT cat.CATEGORY_ID AS Category_ID , item.SUPPLIER_NO FROM PRD_DWH_VIEW_MTRL.ITEM_V AS item INNER JOIN PRD_DWH_VIEW_MTRL.CATEGORY_V AS cat ON cat.CATEGORY_ID = item.CATEGORY_ID WHERE {} IN ({}) """ grainger_attr_query="""
""" Created on Thur Aug 20 2020 @author: xcxg109 """ from GWS_query import GWSQuery from grainger_query import GraingerQuery from queries_WS import ws_short_query, grainger_short_query, grainger_short_values import pandas as pd import file_data_GWS as fd import settings_NUMERIC as settings import time gcom = GraingerQuery() gws = GWSQuery() def gws_data(grainger_df): sku_list = grainger_df['WS_SKU'].tolist() gws_skus = ", ".join("'" + str(i) + "'" for i in sku_list) gws_df = gws.gws_q(gws_short_query, 'tprod."gtPartNumber"', gws_skus) return gws_df def search_type(): """choose which type of data to import -- impacts which querries will be run""" while True: try:
Created on Fri Aug 16 16:39:52 2019 @author: xcxg109 """ import pandas as pd from gamut_query_15 import GamutQuery_15 from grainger_query import GraingerQuery from queries_PIM import gamut_basic_query, grainger_attr_query, gamut_attr_query import attribute_data_pull as pull import file_data_att as fd import settings pd.options.mode.chained_assignment = None gcom = GraingerQuery() gamut = GamutQuery_15() def gamut_skus(grainger_skus): """get basic list of gamut SKUs to pull the related PIM nodes""" sku_list = grainger_skus['Grainger_SKU'].tolist() gamut_skus = ", ".join("'" + str(i) + "'" for i in sku_list) gamut_sku_list = gamut.gamut_q15(gamut_basic_query, 'tprod."supplierSku"', gamut_skus) return gamut_sku_list def gamut_atts(node): """pull gamut attributes based on the PIM node list created by gamut_skus"""
import string import re from collections import defaultdict from grainger_query import GraingerQuery from queries_WS import gws_attr_query, gws_attr_values, grainger_attr_ETL_query, grainger_attr_ALL_query import data_process as process import WS_query_code as q import file_data_GWS as fd from typing import Dict import settings_NUMERIC as settings import time import memory_clear as mem pd.options.mode.chained_assignment = None gcom = GraingerQuery() def match_category(df): """compare data colected from matching file (match_df) with grainger and gws data pulls and create a column to tell analysts whether attributes from the two systems have been matched""" df['Matching'] = 'no' for row in df.itertuples(): grainger_string = str(row.Grainger_Attribute_Name) gws_string = str(row.Gamut_Attribute_Name) # gws_string = str(row.GWS_Attribute_Name) if (grainger_string) == (gws_string):
def generate_data(): gcom = GraingerQuery() #request the type of data to pull: blue or yellow, SKUs or node, single entry or read from file data_type = fd.search_type() search_level = 'cat.CATEGORY_ID' #if Blue is chosen, determine the level to pull L1 (segment), L2 (family), or L1 (category) if data_type == 'node': search_level = fd.blue_search_level() #ask user for node number/SKU or pull from file if desired search_data = fd.data_in(data_type, settings.directory_name) sku_status = skus_to_pull( ) #determine whether or not to include discontinued items in the data pull grainger_df = pd.DataFrame() if data_type == 'node': for k in search_data: if sku_status == 'filtered': temp_df = gcom.grainger_q(grainger_basic_query, search_level, k) temp_df = gcom.grainger_q(grainger_basic_query, search_level, k) elif sku_status == 'all': temp_df = gcom.grainger_q(grainger_discontinued_query, search_level, k) grainger_df = pd.concat([grainger_df, temp_df], axis=0) print(k) elif data_type == 'yellow': for k in search_data: if isinstance(k, int): #k.isdigit() == True: pass else: k = "'" + str(k) + "'" if sku_status == 'filtered': temp_df = gcom.grainger_q(grainger_basic_query, 'yellow.PROD_CLASS_ID', k) elif sku_status == 'all': temp_df = gcom.grainger_q(grainger_discontinued_query, 'yellow.PROD_CLASS_ID', k) grainger_df = pd.concat([grainger_df, temp_df], axis=0) print(k) elif data_type == 'sku': sku_str = ", ".join("'" + str(i) + "'" for i in search_data) if sku_status == 'filtered': grainger_df = gcom.grainger_q(grainger_basic_query, 'item.MATERIAL_NO', sku_str) elif sku_status == 'all': grainger_df = gcom.grainger_q(grainger_discontinued_query, 'item.MATERIAL_NO', sku_str) elif data_type == 'supplier': for k in search_data: if sku_status == 'filtered': temp_df = gcom.grainger_q(grainger_basic_query, 'supplier.SUPPLIER_NO', k) elif sku_status == 'all': temp_df = gcom.grainger_q(grainger_discontinued_query, 'supplier.SUPPLIER_NO', k) grainger_df = pd.concat([grainger_df, temp_df], axis=0) print(k) return [grainger_df, search_level]