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]