def get_context_data(self, **kwargs):
context = super(StockPosition, self).get_context_data(**kwargs)
ean = self.request.GET.get('ean')
context['ean'] = ean
if ean:
dc = DatabaseConnection()
ean_position = dc.select("""
SELECT
ps.codigo_barra as ean,
dsp.position as position,
LEFT(ps.codigo_barra, LEN(ps.codigo_barra) - LEN(ps.grade))
from dbo.produtos_barra ps
left join dbo.bi_django_stock_position dsp on dsp.product_color = LEFT(ps.codigo_barra, LEN(ps.codigo_barra) - LEN(ps.grade))
where ps.codigo_barra = '%s'
;
""" % ean,
strip=True,
dict_format=True)
if not ean_position:
context['position'] = 'Produto não existe'
else:
ean_position = ean_position[0]
context['position'] = ean_position['position']
return context
def get_and_set_data(worksheet_tuple):
print('Updating sheet: %s...' % worksheet_tuple[1], end='')
dc = DatabaseConnection()
worksheet = workbook.worksheet(worksheet_tuple[1])
query = open(worksheet_tuple[0]).read().replace('%', '%%').replace(
'%%(', '%(') % {
'date_to': DATE_TO
}
query_result = dc.select(query, strip=True)
columns = [column[0] for column in dc.cursor.description]
shadow_google_spreadsheet.update_cells_with_dict(worksheet, columns,
query_result)
print('Done!')
<tem:idProduct>%(product_id)s</tem:idProduct>
</tem:ProductGet>
</soapenv:Body>
</soapenv:Envelope>
""" % {
'product_id': product_id
}
soup = post_to_webservice("http://tempuri.org/IService/ProductGet",
soap_productget)
if not soup:
raise Exception('error: soap_productget %s' % product_id)
product_code = soup.find('a:RefId').text
dc = DatabaseConnection()
query = """
SELECT
IdItem
from IT4Produto
where RefProduto='%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'
def get_trustable_parent(dict, initial_loop_level,dimension,metric,year_month_from,year_month_to,min_len=0,min_value=0):
metric_list = []
loop_level = initial_loop_level
dimension_level = dimension
for i in range(0,initial_loop_level):
dimension_level = get_parent_dimension(dimension_level)
while loop_level <= len(dimensions) and (metric_list == [] or max(metric_list) <= min_value or len(metric_list) < min_len):
metric_list = get_metric_list(result_dict[loop_level][dimension_level],year_month,metric,year_month_from=year_month_from,year_month_to=year_month_to)
if loop_level <= len(dimensions) and (metric_list == [] or max(metric_list) <= min_value or len(metric_list) < min_len):
loop_level += 1
dimension_level = get_parent_dimension(dimension_level)
return loop_level, dimension_level
print('Connecting to database...',end='')
dc = DatabaseConnection()
print('Done!')
print('Getting database information...',end='')
result = dc.select(query % {
'dimensions':',\n'.join(['cs.%s' % dimension for dimension in dimensions]),
'metrics':',\n'.join(['SUM(cs.%s) as %s' % (metric,metric) for metric in metrics_list_for_query]),
})
print('Done!')
if result:
print('Creating result dict structure...',end='')
# Creates Result dict structure:
result_dict = {}
for level in range(0,len(dimensions)+1):
result_dict[level] = {}
voi.order_sequence,
voi.client_email,
voi.client_name,
voi.payment_method_group,
voi.ean,
voi.name,
voi.vtex_sku
from bi_vtex_order_items voi
where
voi.order_sequence in ('561973','561857','561766','561549','561528','561304','561194','560970','560865','560840','560018','562757','562703','562633','562551','562301')
-- voi.order_sequence in ('561268')
;
"""
dc = DatabaseConnection()
orders_to_cancel = dc.select(query, strip=True, dict_format=True)
orders_to_cancel = make_dict(orders_to_cancel,
None, ['order_sequence'],
repeated_key='append')
for missing_products in orders_to_cancel.values():
order = missing_products[0]
subject = 'Informações do Pedido #%s' % order['order_sequence']
missing_products_text = []
for product in missing_products:
missing_products_text.append(
'%s\n%s (ref %s)' %
(product['name'], product['vtex_sku'], product['ean']))
from shadow_database import DatabaseConnection
from shadow_vtex.vtex import try_to_request, authenticated_request
from shadow_helpers.helpers import set_in_dict
# from shadow_database.shadow_helpers import make_dict, get_from_dict
from bs4 import BeautifulSoup as Soup
from datetime import datetime, timedelta
from multiprocessing import Pool
import os, fnmatch, shutil, requests, csv, json, requests
def clean_float_str(value):
return round(float(str(value).strip().replace(',', '.')), 2)
dc = DatabaseConnection()
# =================== atualiza para o preco do csv
products = ('35.02.0774', )
product_filter = ','.join(["'%s'" % x for x in products])
def update_price_vtex(sku):
original_price = clean_float_str(sku['original_price'])
cost = clean_float_str(sku['cost'])
sale_price = clean_float_str(sku['current_sale_price'])
data = {
"basePrice":
original_price,
"costPrice":
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!')
from shadow_database import DatabaseConnection # from shadow_database.shadow_helpers import make_dict, get_from_dict import os, fnmatch, shutil, requests, csv, json, requests from bs4 import BeautifulSoup as Soup from datetime import datetime, timedelta dc = DatabaseConnection() query = """ SELECT voi.order_sequence, voi.created_at, voi.ean, voi.name, COALESCE(vpi.imagem,'') as imagem, voi.courier, voi.city, voi.postal_code, voi.neighborhood, voi.street, voi.street_number, voi.complement, voi.client_name, voi.quantity as quantidade_venda, CASE WHEN p.fabricante = 'CATIVA' THEN 'CATIVA' ELSE 'ESTOQUE' END as grupo_posicao, CASE WHEN p.fabricante = 'CATIVA' THEN p.refer_fabricante ELSE '' END as posicoes, COALESCE(e.estoque,0) as estoque, voi.status FROM bi_vtex_order_items voi LEFT JOIN ( SELECT
row_to_insert['grupo_produto'],
row_to_insert['subgrupo_produto'], 'products'
],
repeated_key='append')
else:
shadow_helpers.set_in_dict(
invalid_product_dict,
row_to_insert, [
row_to_insert['grupo_produto'],
row_to_insert['subgrupo_produto'], 'products'
],
repeated_key='append')
if product_dict:
current_timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
dc = DatabaseConnection()
subgroup_filter_list = []
for group in product_dict:
for subgroup in product_dict[group]:
subgroup_filter_list.append(
"(ps.grupo_produto = '%s' and ps.subgrupo_produto = '%s')" %
(group, subgroup))
query = """
SELECT
RTRIM(ps.grupo_produto) as grupo_produto,
RTRIM(ps.subgrupo_produto) as subgrupo_produto,
RTRIM(pg.codigo_grupo) as cod_grupo,
RTRIM(ps.codigo_subgrupo) as cod_subgrupo,
ps.codigo_sequencial as sequence_code
def handle(self, *args, **kwargs):
current_tz = timezone.get_current_timezone()
db_order_items = OrderItem.objects.all().prefetch_related('order')
db_orders = Order.objects.all()
existing_order_items_dict = make_dict(db_order_items, None,
['order__vtex_id', 'ean'])
existing_orders_dict = make_dict(db_orders, None, ['vtex_id'])
dc = DatabaseConnection()
order_item_infos = dc.select("""
SELECT
voi.*,
coalesce(e.estoque_disponivel, 0) as stock_warehouse,
dsp.position as warehouse_position
from dbo.bi_vtex_order_items voi
left join w_estoque_disponivel_sku e on e.codigo_barra = voi.ean and e.filial = 'e-commerce'
left join dbo.produtos_barra ps on ps.codigo_barra = voi.ean
left join dbo.bi_django_stock_position dsp on dsp.product_color = LEFT(voi.ean, LEN(voi.ean) - LEN(ps.grade))
where voi.created_at > '2018-12-26 10:03:33'
;
""",
strip=True,
dict_format=True)
order_infos = make_dict(order_item_infos, None, ['order_id'])
order_dict = {}
for order_info in order_infos.values():
order = get_from_dict(existing_orders_dict,
[order_info['order_id']])
if not order:
order = Order()
order.vtex_id = order_info['order_id']
order.vtex_created_at = current_tz.normalize(
order_info['created_at'].astimezone(current_tz))
if order_info['invoiced_at']:
order.vtex_invoiced_at = current_tz.normalize(
order_info['invoiced_at'].astimezone(current_tz))
if order_info['paid_at']:
order.vtex_paid_at = current_tz.normalize(
order_info['paid_at'].astimezone(current_tz))
order.sequence = order_info['order_sequence']
order.client_name = order_info['client_name']
order.cpf = order_info['cpf']
order.status = order_info['status']
order.total_product_price = order_info['total_product_price']
order.total_shipping_price = order_info['total_shipping_price']
order.courier = order_info['courier']
order.city = order_info['city']
order.neighborhood = order_info['neighborhood']
order.state = order_info['state']
order.postal_code = order_info['postal_code']
order.street_number = order_info['street_number']
order.payment_method_group = order_info['payment_method_group']
order_dict[order.vtex_id] = order
with atomic():
# OrderItem.objects.all().delete()
# Order.objects.all().delete()
for order in order_dict.values():
order.save()
order_items_to_create = []
order_items_to_update = []
for order_item_info in order_item_infos:
order_item = get_from_dict(
existing_order_items_dict,
[order_item_info['order_id'], order_item_info['ean']])
if order_item:
order_items_to_update.append(order_item)
else:
order_item = OrderItem()
order = order_dict[order_item_info['order_id']]
order_item.order_id = order.id
order_items_to_create.append(order_item)
order_item.ean = order_item_info['ean']
order_item.vtex_sku = order_item_info['vtex_sku']
order_item.vtex_product_id = order_item_info['vtex_product_id']
order_item.quantity = order_item_info['quantity']
order_item.product_name = order_item_info['name']
order_item.unit_sale_price = order_item_info['price']
order_item.image_link = order_item_info['image_link']
order_item.stock_warehouse = order_item_info['stock_warehouse']
order_item.invoiced_quantity = order_item_info[
'invoiced_quantity']
order_item.warehouse_position = order_item_info[
'warehouse_position']
OrderItem.objects.bulk_create(order_items_to_create, batch_size=99)
for order_item in order_items_to_update:
order_item.save()
get_sku_id_url = 'http://marciamello.vtexcommercestable.com.br/api/catalog_system/pvt/sku/stockkeepingunitbyean/%s' % sku[
'ean']
response = try_to_request("GET",
get_sku_id_url,
headers=api_connection_config)
if not response:
return make_table_row(sku, None)
json_response = json.loads(response.text)
image = json_response['Images'][0]['ImageUrl']
print(image)
return make_table_row(sku, image)
if __name__ == '__main__':
dc = DatabaseConnection()
skus_for_image = """
SELECT
ps.produto,
ps.cor_produto,
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))
;
"""
from shadow_database import DatabaseConnection
from shadow_vtex.vtex import update_vtex_product
import os, fnmatch, shutil, requests, csv
import requests
from bs4 import BeautifulSoup as Soup
dc = DatabaseConnection()
# query = """
# SELECT
# v_item.product_id,
# p.produto as produto
# from dbo.PRODUTOS_BARRA ps
# inner join dbo.PRODUTOS p on p.produto = ps.produto
# inner join dbo.PRODUTO_CORES pc on pc.produto = p.produto and ps.COR_PRODUTO = pc.COR_PRODUTO
# inner join w_estoque_disponivel_sku e on e.codigo_barra = ps.CODIGO_BARRA and e.filial = 'e-commerce'
# inner join dbo.bi_vtex_product_items v_item on v_item.ean = ps.codigo_barra
# where 1=1
# and p.produto in ('23.09.0059','31.05.0003','35.02.0338','31.02.0110','35.02.0767','24.03.0030','22.02.0243','35.04.0025','25.05.0006','24.04.0521','37.05.0026','35.02.0599','22.12.0607','35.01.0489','37.05.0019','35.09.0612','22.07.0256','34.02.0032','32.05.0007')
# group by v_item.product_id, p.produto
# ;"""
query = """
SELECT
ppc.produto,
MAX(vp.product_id) as product_id,
SUM(ppc.potencial_receita_sorting) as potencial_receita_sorting
from bi_potencial_produto_cor ppc
inner join produtos p on p.produto = ppc.produto
inner join bi_vtex_products vp on vp.produto = ppc.produto
inner join bi_disponibilidade_produto_cor dpc on
import requests
import os, fnmatch, shutil
import csv
from bs4 import BeautifulSoup as Soup
from shadow_database import DatabaseConnection
dc = DatabaseConnection()
def search_directory(products_tested):
filename_to_product = {}
already_found_images = set()
found_products = []
paths = ["C:\\Users\\Felipe\\Downloads\\mm\\fotos\\maisa_nov", "D:\\"]
for product in products_tested:
for n in range(1,5):
filename = '%s.%s_0%s.jpg' % (product['prod_code'].replace(".",""), product['cod_color'], n)
filename_to_product[filename] = product
filename = '%s.%s_0%s.jpg' % (product['prod_code'].replace(".",""), str(product['cod_color']).zfill(2), n)
filename_to_product[filename] = product
for path in paths:
for folder, subs, files in os.walk(path):
for filename in files:
if filename in filename_to_product and filename not in already_found_images:
already_found_images.add(filename)
filepath = os.path.abspath(os.path.join(folder, filename))
shutil.copy(filepath,'C:\\Users\\Felipe\\Downloads\\mm\\fotos\\upar3\\')
# print(filepath)
if __name__ == '__main__':
query = """
SELECT DISTINCT
e.produto
FROM w_estoque_disponivel_sku e
INNER JOIN produtos p on e.produto = p.produto
WHERE
p.grupo_produto != 'GIFTCARD' and
e.estoque_disponivel > 0 and
e.filial = 'E-COMMERCE'
order by e.produto
"""
print('Connecting to database...', end='')
dc = DatabaseConnection()
print('Done!')
print('Getting products from database...', end='')
product_ref_ids = dc.select(query, dict_format=True, strip=True)
print('Done!')
print('Getting product info from vtex...', end='')
product_info = {
"product_categories": [],
"product_items": [],
"product_images": [],
"products": []
}
from shadow_database import DatabaseConnection from shadow_helpers.helpers import set_in_dict from shadow_vtex.vtex import post_to_webservice dc = DatabaseConnection() import os, fnmatch, shutil, requests, csv import requests from bs4 import BeautifulSoup as Soup from multiprocessing import Pool, Manager # -------------------------------------------------------------------------------------------------------------------------------- def f(product_info): ean = product_info['ean'] if not product_info['vtex_color']: return 'sem cor: %s' % ean print(product_info) sku_size = product_info['size'] sku_color = product_info['vtex_color'] # -------------------------------------------------------------------------------------------------------------------------------- soap_skuget = """<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/"> <soapenv:Header/> <soapenv:Body> <tem:StockKeepingUnitGetByEan> <!--Optional:--> <tem:EAN13>%s</tem:EAN13> </tem:StockKeepingUnitGetByEan> </soapenv:Body> </soapenv:Envelope>""" % (ean)
# except Exception as e:
# print(e)
# # print(filepath)
product = filename_to_product[filename]
set_in_dict(found_products, 1,
[product['produto'], product['cor_produto']])
return found_products
from multiprocessing import Pool
if __name__ == '__main__':
start = time.time()
dc = DatabaseConnection()
# product_filter = "and p.griffe = 'NAKD'"
product_filter = "e.estoque_disponivel > 0 and e.filial='e-commerce'"
# product_filter = "(p.produto = '22.01.0007')"
query = """
SELECT
p.produto as prod_code,
ps.COR_PRODUTO as cod_color,
MAX(ps.CODIGO_BARRA) as ean,
MAX(LEFT(ps.CODIGO_BARRA, LEN(ps.CODIGO_BARRA) - LEN(ps.grade))) as produto_cor_code,
SUM(e.estoque_disponivel) as stock
from dbo.PRODUTOS p
INNER JOIN dbo.PRODUTO_CORES pc on pc.produto = p.produto
INNER JOIN dbo.PRODUTOS_BARRA ps on ps.produto = p.produto and ps.COR_PRODUTO = pc.COR_PRODUTO
number_threads = min(len(date_ranges), 20)
with Pool(number_threads) as p:
order_items_lists = p.map(get_orders_by_date_range, date_ranges)
# 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')
{
'query_file': 'daily_sales_stock_data_wo_store.sql',
'worksheet_name': 'StockDataWOStore'
},
{
'query_file': 'daily_sales_receivings_data.sql',
'worksheet_name': 'ReceivingsData'
},
{
'query_file': 'daily_sales_receivings_data_wo_store.sql',
'worksheet_name': 'ReceivingsDataWOStore'
},
]
workbook = shadow_google_spreadsheet.open(WORKBOOK_NAME)
dc = DatabaseConnection()
def get_and_set_data(worksheet_dict):
print('Updating sheet: %s...' % worksheet_dict['worksheet_name'], end='')
worksheet = workbook.worksheet(worksheet_dict['worksheet_name'])
query = open(worksheet_dict['query_file']).read()
query_result = dc.select(query, strip=True)
columns = [column[0] for column in dc.cursor.description]
shadow_google_spreadsheet.update_cells_with_dict(worksheet, columns,
query_result)
print('Done!')
if __name__ == '__main__':
def get_queryset(self):
ean = self.request.GET.get('ean')
if not ean:
return []
else:
dc = DatabaseConnection()
sku_history = dc.select("""
SELECT
t.*
from (
SELECT distinct
ps.codigo_barra as ean,
'saida' as action,
case
when lsp.DATA_PARA_TRANSFERENCIA > ls.DATA_PARA_TRANSFERENCIA then lsp.DATA_PARA_TRANSFERENCIA
else ls.DATA_PARA_TRANSFERENCIA
end as transf_date,
lsp.QTDE_SAIDA as qnt,
ls.FILIAL_DESTINO as filial,
ls.NUMERO_NF_TRANSFERENCIA as nf
FROM loja_saidas ls
INNER JOIN loja_saidas_produto lsp on lsp.ROMANEIO_PRODUTO = ls.ROMANEIO_PRODUTO
inner join produtos_barra ps on ps.produto = lsp.produto and ps.COR_PRODUTO = lsp.COR_PRODUTO
where 1=1
and ls.filial = 'e-commerce'
and case
when lsp.DATA_PARA_TRANSFERENCIA > ls.DATA_PARA_TRANSFERENCIA then lsp.DATA_PARA_TRANSFERENCIA
else ls.DATA_PARA_TRANSFERENCIA
end >= DATEADD(day, -30, GETDATE())
UNION
SELECT distinct
ps.codigo_barra,
'entrada',
case
when lep.DATA_PARA_TRANSFERENCIA > le.DATA_PARA_TRANSFERENCIA then lep.DATA_PARA_TRANSFERENCIA
else le.DATA_PARA_TRANSFERENCIA
end,
lep.QTDE_ENTRADA,
le.FILIAL_ORIGEM,
le.NUMERO_NF_TRANSFERENCIA
FROM loja_entradas le
INNER JOIN loja_entradas_produto lep on lep.ROMANEIO_PRODUTO = le.ROMANEIO_PRODUTO
inner join produtos_barra ps on ps.produto = lep.produto and ps.COR_PRODUTO = lep.COR_PRODUTO
where 1=1
and le.filial = 'e-commerce'
and case
when lep.DATA_PARA_TRANSFERENCIA > le.DATA_PARA_TRANSFERENCIA then lep.DATA_PARA_TRANSFERENCIA
else le.DATA_PARA_TRANSFERENCIA
end >= DATEADD(day, -30, GETDATE())
UNION
SELECT
lvp.CODIGO_BARRA,
'venda',
data_venda,
lvp.qtde,
'venda',
'venda'
FROM dbo.LOJA_VENDA_PRODUTO lvp
inner join dbo.FILIAIS fil on fil.cod_filial = lvp.codigo_filial
INNER JOIN produtos p on lvp.produto = p.produto
where 1=1
and fil.filial = 'e-commerce'
and data_venda >= DATEADD(day, -30, GETDATE())
and p.grupo_produto != 'GIFTCARD'
and lvp.qtde > 0
) t
where t.ean = '%s'
order by t.transf_date
;
""" % ean,
strip=True,
dict_format=True)
return sku_history
set_stock_url,
headers=api_connection_config,
data='{"quantity": %s}' % true_quantity)
if not response:
print('ERROR: %s' % sku_id)
# else:
# print(response.text)
if __name__ == '__main__':
fixes_dict = Manager().dict()
import time
start_time = time.time()
dc = DatabaseConnection()
divergence_query = """
SELECT
count(*) as divergences,
sum(mudanca_detectada) as detected,
count(*) - sum(mudanca_detectada) as undetected
from (
select
COALESCE(e.codigo_barra, vpi.ean) as ean,
vpi.item_id,
COALESCE(e.estoque_disponivel, 0) as linx,
vpi.stock_quantity as vtex,
case when COALESCE(e.codigo_barra, vpi.ean) in (
SELECT distinct
ps.codigo_barra