# 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!')