# order_items_lists = []
# for date_range in date_ranges:
# order_items_list = get_orders_by_date_range(date_range)
# order_items_lists.append(order_items_list)
order_items = []
for x in order_items_lists:
order_items.extend(x)
print('Connecting to database...', end='')
dc = DatabaseConnection()
print('Done!')
print('Inserting into tables...')
print(' bi_vtex_order_items')
dc.execute('TRUNCATE TABLE bi_vtex_order_items;')
dc.insert_dict('bi_vtex_order_items', order_items, print_only=False)
# skus_to_reduce = dc.select("""
# SELECT
# voi.vtex_sku as sku_id,
# voi.quantity as ordered_quantity,
# voi.status as status
# from dbo.vtex_order_items voi
# where status not in ('Faturado', 'Cancelado')
# order by order_sequence
# ;
# """, strip=True, dict_format=True)
ideal_stock_cost += coverage_previous_cogs
row = dimension_dict[year_month]
prev_row = dimension_dict[add_months(year_month,-1)]
row['quantidade_estoque'] = max(ideal_stock_quantity,prev_row['quantidade_estoque']-row['itens'])
row['custo_estoque'] = max(ideal_stock_cost,prev_row['custo_estoque']-row['custo_total'])
row['quantidade_recebida'] = max(0,row['quantidade_estoque'] - prev_row['quantidade_estoque'] + row['itens'])
row['custo_recebido'] = max(0,row['custo_estoque'] - prev_row['custo_estoque'] + row['custo_total'])
print('Done!')
print('Updating database...',end='')
if update_results:
# Drop current Table:
drop_query = """
IF OBJECT_ID('%s') IS NOT NULL
DROP TABLE %s;
""" % (output_table,output_table)
dc.execute(drop_query)
# Create new Table:
create_table_dimensions = ''
for dimension in dimensions:
create_table_dimensions += '%s VARCHAR(50),\n' % dimension
create_table_metrics = ''
for metric in metrics:
create_table_metrics += '%s %s,\n' % (metric['name'],metric['format'])
create_table_query = """
CREATE TABLE %(output_schema)s.%(output_table)s (
anomes INT,
%(create_table_dimensions)s
%(create_table_metrics)s
PRIMARY KEY (%(primary_keys)s)
);""" % {
""" % product_code
it4_item_id = dc.select(query, strip=True)
if it4_item_id:
it4_item_id = it4_item_id[0][0]
if int(product_id) != int(it4_item_id):
query = """
UPDATE dbo.IT4Produto
SET IdProduto='%s'
WHERE IdItem='%s'
;
""" % (product_id, it4_item_id)
print(query)
dc.execute(query)
else:
product_params = {
'product_id': product_id,
'product_code': product_code,
'description': soup.find('a:Name').text,
'key_words': soup.find('a:KeyWords').text,
}
query = """
INSERT INTO dbo.IT4Produto
(IdProduto, Descricao, DescricaoLink, DescricaoDetalhada, DescricaoMetaTag, PalavrasSubstitutivas, Categorias, IdMarca, IdDepartamento, RefProduto, DataCadastro, DataLancamento)
VALUES('%(product_id)s', '%(description)s', '%(key_words)s', '%(description)s', '%(description)s', '%(key_words)s', '2', 1, 1, '%(product_code)s', null, null)
;
""" % product_params
from shadow_database import DatabaseConnection
import sys
query_file = sys.argv[1]
print('Running Query %s' % query_file)
dc = DatabaseConnection()
query = open(query_file).read()
print(query)
query_result = dc.execute(query)
print('Done!')
product_checks = p.map(waterfall, products_to_check)
# for x in products_to_check:
# product_checks.append(f(x))
products_to_search = []
for product_check in product_checks:
product_check['imagem_hd'] = False
if not product_check['imagem_vtex']:
products_to_search.append(product_check)
found_products = search_directory(products_to_search)
product_check_dict = make_dict(product_checks, None,
['produto', 'cor_produto'])
try:
for found_product in found_products:
set_in_dict(product_check_dict, True, [
found_product['produto'], found_product['cor_produto'],
'imagem_hd'
])
except Exception as e:
pass
dc.execute('TRUNCATE TABLE bi_vtex_product_waterfall;')
dc.insert_dict(
'bi_vtex_product_waterfall',
list(for_each_leaf(product_check_dict, depth=2, return_keys=False)))
end = time.time()
print(end - start)
product_price_insert_query_values_list,
columns=product_price_columns,
print_only=PRINT_ONLY)
product_size_columns = [
'PRODUTO', 'COR_PRODUTO', 'CODIGO_BARRA', 'GRADE', 'TAMANHO',
'CODIGO_BARRA_PADRAO', 'TIPO_COD_BAR'
]
dc.insert('PRODUTOS_BARRA',
product_size_insert_query_values_list,
columns=product_size_columns,
print_only=PRINT_ONLY)
sequence_code_update_query = '\n'.join(sequence_code_update_query_list)
if PRINT_ONLY:
print(sequence_code_update_query)
else:
dc.execute(sequence_code_update_query)
product_categorization_columns = ['produto', 'attr_key', 'attr_value']
dc.insert('sw_product_attributes',
categorization_insert_query_values_list,
columns=product_categorization_columns,
print_only=PRINT_ONLY)
if product_price_update_query_values_list:
product_price_update_query = '\n'.join(
product_price_update_query_values_list)
if PRINT_ONLY:
print(product_price_update_query)
else:
dc.execute(product_price_update_query)
if not PRINT_ONLY:
MAX(ps.CODIGO_BARRA) as ean
FROM produtos_barra ps
LEFT JOIN dbo.W_ESTOQUE_DISPONIVEL_SKU e on e.codigo_barra = ps.CODIGO_BARRA
where e.estoque_disponivel > 0
-- and ps.produto = '23.11.0244'
-- and ps.COR_PRODUTO = '32'
group by ps.produto, ps.COR_PRODUTO-- , LEFT(ps.CODIGO_BARRA, LEN(ps.CODIGO_BARRA)-LEN(ps.grade))
;
"""
skus_for_image = dc.select(skus_for_image, strip=True, dict_format=True)
# images = []
# for sku in skus_for_image:
# images.append(get_image_url(sku))
with Pool(50) as p:
images = p.map(get_image_url, skus_for_image)
images = [x for x in images if x]
# brute-force remove repeated images
images = list(set(images))
dc = DatabaseConnection()
print('Inserting into tables...')
print(' bi_sku_images')
dc.execute('TRUNCATE TABLE bi_sku_images;')
dc.insert('bi_sku_images', images, print_only=False)
# result.append(get_product_info(x))
# Multi Threading:
with Pool(20) as p:
result = p.map(get_product_info, product_ref_ids)
for result_thread in result:
for key in product_info:
product_info[key].extend(result_thread[key])
print('Done!')
print('Inserting into tables...')
print(' bi_vtex_product_items')
dc.execute('TRUNCATE TABLE bi_vtex_product_items;')
dc.insert('bi_vtex_product_items', product_info["product_items"])
print(' bi_vtex_products')
dc.execute('TRUNCATE TABLE bi_vtex_products;')
dc.insert('bi_vtex_products', product_info["products"])
print(' bi_vtex_product_categories')
dc.execute('TRUNCATE TABLE bi_vtex_product_categories;')
dc.insert('bi_vtex_product_categories', product_info["product_categories"])
print(' bi_vtex_product_item_images')
dc.execute('TRUNCATE TABLE bi_vtex_product_item_images;')
dc.insert('bi_vtex_product_item_images', product_info["product_images"])
print('Done!')