, 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
print('running GWS SKUs in {} batches'.format(num_lists))
# -*- coding: utf-8 -*-
"""
Created on Thu Jun 3 15:16:46 2021
@author: xcxg109
"""
import pandas as pd
import numpy as np
import settings_NUMERIC as settings
import time
from GWS_query import GWSQuery
gws = GWSQuery()
pd.options.mode.chained_assignment = None
prod_ids = """
SELECT id
FROM {}
WHERE "gtPartNumber" IN ({})
"""
numeric_vals = """
SELECT COUNT(tprod_value.id)
FROM taxonomy_product_attribute_value tprod_value
INNER JOIN taxonomy_attribute tax_att
Created on Tue Aug 4 09:28:25 2020
@author: xcxg109
"""
import pandas as pd
from GWS_query import GWSQuery
import file_data_GWS as fd
import time
import settings_NUMERIC as settings
from grainger_query import GraingerQuery
import WS_query_code as q
pd.options.mode.chained_assignment = None
gws = GWSQuery()
gcom = GraingerQuery()
gws_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,
# -*- coding: utf-8 -*-
#Created on Thu Apr 29 21:14:51 2021
#author: xcxg109
import pandas as pd
import numpy as np
import settings_NUMERIC as settings
import time
from GWS_query import GWSQuery
gws = GWSQuery()
pd.options.mode.chained_assignment = None
ws_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,
category.name,
tax.ancestors || tax.id,
tax.ancestor_names || tax.name
FROM taxonomy_category as category
@author: xcxg109
"""
import pandas as pd
import numpy as np
from GWS_query import GWSQuery
import file_data_GWS as fd
import time
import settings_NUMERIC as settings
from grainger_query import GraingerQuery
import WS_query_code as q
pd.options.mode.chained_assignment = None
gws = GWSQuery()
gcom = GraingerQuery()
gws_values_single = """
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,
# -*- coding: utf-8 -*-
"""
Created on Thu Sep 19 10:10:23 2019
@author: xcxg109
"""
import pandas as pd
from GWS_query import GWSQuery
from grainger_query import GraingerQuery
from queries_MATCH import gamut_basic_query, grainger_attr_query, grainger_name_query, grainger_basic_query
gcom = GraingerQuery()
gamut = GWSQuery()
def gamut_skus(grainger_skus):
"""get basic list of gamut SKUs to pull the related PIM nodes"""
gamut_sku_list = pd.DataFrame()
sku_list = grainger_skus['Grainger_SKU'].tolist()
if len(sku_list) > 20000:
num_lists = round(len(sku_list) / 20000, 0)
num_lists = int(num_lists)
if num_lists == 1:
num_lists = 2
print('running SKUs in {} batches'.format(num_lists))
Created on Thu Jul 30 13:32:56 2020
@author: xcxg109
"""
import pandas as pd
import numpy as np
from GWS_query import GWSQuery
import file_data_GWS as fd
import time
import math
import settings_NUMERIC as settings
pd.options.mode.chained_assignment = None
gws = GWSQuery()
gws_values_single = """
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,
category.name,
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,
category.name,
"""
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
gws = GWSQuery()
def get_stats(df):
"""return unique values for each attribute with a count of how many times each is used in the node"""
df['Count'] =1
stats = pd.DataFrame(df.groupby(['Grainger_Attribute_Name', 'Grainger_Attribute_Value'])['Count'].sum())
return stats
def get_ws_stats(df):
"""return unique values for each attribute with a count of how many times each is used in the node"""
df['Count'] =1
stats = pd.DataFrame(df.groupby(['WS_Attribute_Name', 'WS_Attribute_Value'])['Count'].sum())
return stats
"""
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, grainger_discontinued_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,
from collections import defaultdict
from grainger_query import GraingerQuery
from GWS_query import GWSQuery
from queries_WS import ws_attr_query, ws_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()
gws = GWSQuery()
ws_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,
# -*- coding: utf-8 -*-
"""
Created on Fri Jun 4 09:12:26 2021
@author: xcxg109
"""
import pandas as pd
import numpy as np
import settings_NUMERIC as settings
import time
from GWS_query import GWSQuery
gws = GWSQuery()
pd.options.mode.chained_assignment = None
prod_ids = """
SELECT id
FROM {}
WHERE "gtPartNumber" IN ({})
"""
all_vals = """
SELECT COUNT(tprod_value.id)
FROM taxonomy_product_attribute_value tprod_value
INNER JOIN taxonomy_attribute tax_att
ON tax_att.id = tprod_value.{}
# -*- 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
"""
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, grainger_discontinued_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,
@author: xcxg109
"""
import pandas as pd
import numpy as np
from GWS_query import GWSQuery
import file_data_GWS as fd
import time
import math
import settings_NUMERIC as settings
from grainger_query import GraingerQuery
import WS_query_code as q
pd.options.mode.chained_assignment = None
gws = GWSQuery()
gcom = GraingerQuery()
gws_values_single = """
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,
@author: xcxg109
"""
import pandas as pd
import numpy as np
from GWS_query import GWSQuery
import file_data_GWS as fd
import time
import settings_NUMERIC as settings
from grainger_query import GraingerQuery
import WS_query_code as q
pd.options.mode.chained_assignment = None
gws = GWSQuery()
gcom = GraingerQuery()
gws_values_single = """
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,
FULL OUTER JOIN taxonomy_product_attribute_value tprodvalue
ON tprod.id = tprodvalue."productId"
AND tax_att.id = tprodvalue."attributeId"
AND tprodvalue.deleted = 'false'
FULL OUTER JOIN supplier_product supplier
ON supplier.id = tprod."supplierProductId"
WHERE {} IN ({})
"""
pd.options.mode.chained_assignment = None
gws = GWSQuery()
def get_category_fill_rate(cat_df):
browsable_skus = cat_df
browsable_skus['Original_Value'].replace('', np.nan, inplace=True)
#calculate fill rates at the attribute / category level
total = browsable_skus['WS_SKU'].nunique()
print ('cat total = ', total)
browsable_skus = browsable_skus.drop_duplicates(subset=['WS_SKU', 'WS_Attr_ID'])
browsable_skus.dropna(subset=['Original_Value'], inplace=True)
browsable_skus['Category_Fill_Rate_%'] = (browsable_skus.groupby('WS_Attr_ID')['WS_Attr_ID'].apply(lambda x: x.notnull().mean))
"""
Spyder Editor
This is a temporary script file.
"""
#GWS New Workstation Test
from postgres_GWS import PostgresDatabase_GWS
from pathlib import Path
import settings_NUMERIC as settings
from GWS_query import GWSQuery
gws = GWSQuery()
moist = PostgresDatabase_GWS()
# no need for an open connection,
# as we're only doing a single query
#engine.dispose()
#def test_query(search, k):
multi_atts="""
WITH RECURSIVE tax AS (
SELECT id,
name,
ARRAY[]::INTEGER[] AS ancestors,
ARRAY[]::character varying[] AS ancestor_names
Created on Tue Apr 16 17:00:31 2019
@author: xcxg109
"""
import numpy as np
from GWS_query import GWSQuery
from grainger_query import GraingerQuery
from queries_WS import grainger_hier_query, grainger_discontinued_query, ws_hier_query
import file_data_GWS as fd
import pandas as pd
import settings_NUMERIC as settings
import time
gcom = GraingerQuery()
gws = GWSQuery()
GWS_cats = """
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,
category.name,
, 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']:
data_type = 'gws_query'
""" Search for values using LIKE, edit query for specific values """ import pandas as pd import numpy as np from GWS_query import GWSQuery import time gws = GWSQuery() pd.options.mode.chained_assignment = None #def test_query(search, k): 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, category.name, tax.ancestors || tax.id, tax.ancestor_names || tax.name FROM taxonomy_category as category INNER JOIN tax ON category."parentId" = tax.id
# -*- 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
FULL OUTER JOIN taxonomy_product_attribute_value tprodvalue
ON tprod.id = tprodvalue."productId"
AND tax_att.id = tprodvalue."attributeId"
AND tprodvalue.deleted = 'false'
FULL OUTER JOIN supplier_product supplier
ON supplier.id = tprod."supplierProductId"
WHERE {} IN ({})
"""
pd.options.mode.chained_assignment = None
gws = GWSQuery()
def get_category_fill_rate(cat_df):
browsable_skus = cat_df
browsable_skus['Original_Value'].replace('', np.nan, inplace=True)
#calculate fill rates at the attribute / category level
total = browsable_skus['WS_SKU'].nunique()
print ('cat total = ', total)
browsable_skus = browsable_skus.drop_duplicates(subset=['WS_SKU', 'WS_Attr_ID'])
browsable_skus.dropna(subset=['Original_Value'], inplace=True)
# browsable_skus['Category_Fill_Rate_%'] = (browsable_skus.groupby('WS_Attr_ID')['WS_Attr_ID'].transform('count')/total)*100
from collections import defaultdict
from GWS_query import GWSQuery
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()
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,
category.name,
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:
data_type = input("Search by: \n1. Blue (node)\n2. Yellow\n3. SKU\n4. Other ")
Created on Thu Jul 30 13:32:56 2020
@author: xcxg109
"""
import pandas as pd
import numpy as np
import re
import string
from collections import defaultdict
from GWS_query import GWSQuery
import file_data_GWS as fd
import time
import settings_NUMERIC as settings
gws = GWSQuery()
gws_values_single = """
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,
category.name,
# -*- coding: utf-8 -*-
"""
Created on Tue Jan 5 15:56:20 2021
@author: xcxg109
"""
import pandas as pd
import numpy as np
from GWS_query import GWSQuery
import os
from tkinter.filedialog import askdirectory
import glob
import time
gws = GWSQuery()
pd.options.mode.chained_assignment = None
ws_category="""
SELECT cat.name
FROM taxonomy_category as cat
WHERE {} IN ({})
"""
def get_col_widths(df):
#find maximum length of the index column
idx_max = max([len(str(s)) for s in df.index.values] + [len(str(df.index.name))])
#Then concatenate this to max of the lengths of column name and its values for each column
# -*- coding: utf-8 -*-
import time
"""
Spyder Editor
This is a temporary script file.
"""
#GWS New Workstation Test
from postgres_GWS import PostgresDatabase_GWS
from pathlib import Path
import settings_NUMERIC as settings
from GWS_query import GWSQuery
gws = GWSQuery()
moist = PostgresDatabase_GWS()
# no need for an open connection,
# as we're only doing a single query
#engine.dispose()
#def test_query(search, k):
test_q = """
--pulls Raw and Normalized values for WS Attributes
WITH RECURSIVE tax AS (
SELECT id,
name,
ARRAY[]::INTEGER[] AS ancestors,
ARRAY[]::character varying[] AS ancestor_names
FROM taxonomy_category as category