def update_moneygraph(start_date, end_date, city, manufacturer, cat, sub_cat):
"""function to generate a product income graph with the given inputs"""
query = "select SUM(Valor_TotalFactura) as ventas, c.Texto_breve_de_material1 from historicopedidos h left join tiendas t on h.tienda = t.tienda left join categorias c on h.Material = c.Material WHERE Valor_TotalFactura >0 AND Fecha_Pedido >= " + "\'" + start_date + "\'" + " AND Fecha_Pedido < " + "\'" + end_date + "\'" + ""
if city is not None:
if city == 'CALI':
centro = 2000
else:
centro = 3000
query = query + 'AND h.ce =' + str(centro)
if manufacturer is not None:
query = query + 'AND h.fabricante =' + str(manufacturer)
if cat is not None:
query = query + 'AND c.jerarquia_productos =' + str(cat)
if sub_cat is not None:
query = query + 'AND c.subcategoria =' + str(sub_cat)
df = run_query(
query +
"group by c.material, c.Texto_breve_de_material1 order by ventas DESC limit 10",
engine)
fig = px.bar(df,
x='texto_breve_de_material1',
y='ventas',
title="Income Analysis per Product",
labels={
'texto_breve_de_material1': 'Product',
'ventas': 'Income'
})
return fig
def update_last_updated(city, product):
try:
city_code = (2000 if city == 'Cali' else 3000)
sql = 'select * from model_metrics_log where city = ' + str(city_code) + ' and product = ' + str(
product) + ' order by forecast_datetime desc limit 1'
result = run_query(sql, engine)['forecast_datetime'].loc[0].strftime("%d/%m/%Y - %H:%M")
except:
result = ''
pass
return result
def update_rmse(city, product):
try:
city_code = (2000 if city == 'Cali' else 3000)
sql = 'select * from model_metrics_log where city = ' + str(city_code) + ' and product = ' + str(
product) + ' order by forecast_datetime desc limit 1'
result = str(np.round(run_query(sql, engine)['rmse'].values.tolist()[0], 2))
except:
result = ''
pass
return result
def update_ci_display_value(city, product):
try:
city_code = (2000 if city == 'Cali' else 3000)
df_temp = run_query('select * from forecast_results where product= ' + str(product) + ' and city= ' + str(
city_code) + ' order by ds desc', engine)
num = len(df_temp[(df_temp['y'] > df_temp['yhat_lower']) & (df_temp['y'] < df_temp['yhat_upper'])])
den = len(df_temp)
result = str(np.round(num / den, 2) * 100)
except:
result = ''
return result
def update_month_seasonality(city, product):
try:
city_code = (2000 if city == 'Cali' else 3000)
sql = 'select * from model_metrics_log where city = ' + str(city_code) + ' and product = ' + str(
product) + ' order by forecast_datetime desc limit 1'
temp = run_query(sql, engine)['monthly_seasonality_scale'].values.tolist()[0]
result = 'No' if temp == 0 else 'Weak' if temp == 1 else 'Strong'
result = result + ' Seasonality'
except:
result = ''
pass
return result
def update_manufacturers_list(product_type):
options = []
try:
new_list = run_query("select distinct fabricante, nombre_fabricante from historicopedidos h\
left join categorias c on h.material=c.material\
inner join (select distinct product from forecast_results) r on r.product=h.material \
where c.nombre_cat = upper('" + product_type + "')", engine).to_dict(orient='records')
options = []
for option in new_list:
options.append({"label": option['nombre_fabricante'], "value": option['fabricante']})
except:
print('Error updating list')
return options
def update_prod_list(product_type, manufacturer):
options = []
try:
new_list = run_query("select distinct r.product as material,c.texto_breve_de_material \
from forecast_results r \
inner join categorias c on c.material=r.product \
left join (select distinct material, fabricante from historicopedidos) h on r.product = h.material\
where c.nombre_cat = upper('" + product_type + "')\
and h.fabricante = " + str(manufacturer), engine).to_dict(orient='records')
for option in new_list:
options.append({"label": option['texto_breve_de_material'], "value": option['material']})
except:
print('Error updating list')
return options
def update_graph(city, product):
fig = go.Figure(layout=go.Layout(autosize=True))
try:
city_code = (2000 if city == 'Cali' else 3000)
df_plot = run_query('select * from forecast_results where product= ' + str(product) + ' and city= ' + str(
city_code) + ' order by ds desc', engine)
fig.add_trace(go.Scatter(x=df_plot['ds'], y=df_plot['yhat_upper'], name='y', mode='lines',
line=dict(width=0.5, color='rgb(210, 250, 255)'), showlegend=False))
fig.add_trace(go.Scatter(x=df_plot['ds'], y=df_plot['yhat_lower'], name='y', mode='lines', fill='tonexty',
line=dict(width=0.5, color='rgb(210, 250, 255)'), fillcolor='rgba(0, 0, 255,0.1)',
showlegend=False))
fig.add_trace(
go.Scatter(x=df_plot['ds'], y=df_plot['yhat'], name='yhat', mode='lines', line=dict(color='blue', width=4),
showlegend=False))
fig.add_trace(go.Scatter(x=df_plot['ds'], y=df_plot['y'], name='y', mode='markers', marker_color='black',
showlegend=False))
except:
pass
return fig
def update_manufacturers_list(product_type):
"""Take the product category and return a dictionary with the manufacturers available to just that category"""
options = []
try:
new_list = run_query(
"select DISTINCT ON (fabricante) fabricante, nombre_fabricante \
from historicopedidos h left join tiendas t on h.tienda = t.tienda left join categorias c on h.Material = c.Material \
where c.jerarquia_productos = " + str(product_type),
engine).dropna().to_dict(orient='records')
for option in new_list:
options.append({
"label": option['nombre_fabricante'],
"value": option['fabricante']
})
except:
pass
return options
def update_prod_list(product_type, manufacturer):
"""Take the filterer manufacturer and return a dictionary with the product subcategories"""
options = []
try:
new_list = run_query(
"select distinct MIN(c.subcategoria), c.nombre_sub \
from historicopedidos h left join tiendas t on h.tienda = t.tienda left join categorias c on h.Material = c.Material \
where c.jerarquia_productos = " + str(product_type) +
"and h.fabricante = " + str(manufacturer) +
" group by c.nombre_sub",
engine).dropna().to_dict(orient='records')
for option in new_list:
options.append({
"label": option['nombre_sub'],
"value": option['min']
})
except:
pass
return options
import dash_bootstrap_components as dbc
import dash_core_components as dcc
from scripts.utils import my_dash_components as mydbc
from dash.dependencies import Input, Output
import plotly.express as px
import plotly.graph_objects as go
from app import app
import numpy as np
import pandas as pd
import dash
from data import run_query, engine
city_lbl = ["Cali", "Medellin"]
prod_type_lbl = run_query('select distinct initcap(nombre_cat) as nombre_cat from categorias c\
inner join forecast_results r on c.material=r.product ', engine)['nombre_cat'] \
.values.tolist()
styles = {
'container': {
'position': 'fixed',
'display': 'flex',
'flex-direction': 'column',
'height': 'auto',
'width': '100%'
}
}
content = html.Div(className="col-sm",
children=[
dbc.Row(
import dash_html_components as html
import dash_bootstrap_components as dbc
import dash_core_components as dcc
from scripts.utils import my_dash_components as mydbc
from app import app
from dash.dependencies import Input, Output
from data import run_query, engine
import plotly.express as px
# Lists to be displayed on the dropdown
city_lbl = ["CALI", "MEDELLIN"]
prod_cat_lbl = run_query(
"select MIN(jerarquia_productos), nombre_cat from categorias group by nombre_cat",
engine).dropna()
product_type = []
manufacturer = []
styles = {
'container': {
'position': 'fixed',
'display': 'flex',
'flex-direction': 'column',
'height': 'auto',
'width': '100%'
}
}
content = html.Div([
dbc.Row([
dbc.Col(html.H5("City Warehouse")),
dbc.Col(html.H5("Category")),
def main():
# needs to run first
data.setup_db()
data.add_some_data()
data.run_query()
def main():
# needs to run first
data.setup_db()
data.add_some_data()
data.run_query()
def update_graph(start_date, end_date, city, manu, cat, sub_cat, Chart):
#try:
file = ""
fields = []
aliases = []
caption = ""
series = pd.DataFrame()
if Chart is None or city is None or city == "":
print("Chart Type is not selected")
#raise Exception("Chart Type is not selected")
else:
# start_date = '{:%x}'.format(date.fromisoformat(start_date.replace("/", "-")))
# end_date = '{:%x}'.format(date.fromisoformat(end_date.replace("/", "-")))
location = citylocation[city]
geodata = geopandas.read_file(
f"models/Geolocation/geojson_data/Comunas_{city}.geojson",
driver="GeoJSON")
if Chart == 'Stores Location':
query = f"""SELECT SUM(cantidad_pedido) as cantidad, T.tienda, SUM(h.valor_totalfactura) as ventas,
T.latitud, T.longitud, T.nombre_tienda, T.fecha_creacion, T.direccion_tienda,
T.telefono_1, t.nombre_tendero
from historicopedidos h
left join tiendas t on h.tienda = t.tienda
left join categorias c on h.Material = c.Material
WHERE Valor_TotalFactura > 0
-- AND Fecha_Pedido >= DATE '{start_date}'
-- AND Fecha_Pedido < DATE '{end_date}'
AND T.ciudad_tienda= '{city}'"""
if manu is not None and not manu == "":
query = query + f" AND h.fabricante='{manu}'"
if cat is not None and not cat == "":
query = query + f" AND c.nombre_cat =upper('{cat}')"
if sub_cat is not None and not sub_cat == "":
query = query + f" AND c.material ={sub_cat}"
query = query + """ GROUP BY T.tienda, T.latitud, T.longitud, T.nombre_tienda, T.fecha_creacion,
T.direccion_tienda, T.telefono_1, t.nombre_tendero
order by ventas DESC limit 50"""
series = run_query(query, engine)
series = series.dropna(subset=['latitud', 'longitud'])
series['fecha_creacion'] = series['fecha_creacion'].apply(
lambda x: x.strftime('%Y-%m-%d'))
fields = [
'index', 'nombre_tienda', 'tienda', 'fecha_creacion',
'direccion_tienda', 'cantidad', 'ventas'
]
aliases = [
'Posición', 'Nombre de Tienda', 'Código', "Fecha de Creación",
'Dirección', 'Cantidad', 'Valor de Ventas $'
]
caption = "Best Teate Stores"
m = point_map(series, location, fields, aliases, caption)
m.save("models/Geolocation/Maps/Map.html")
file = open("models/Geolocation/Maps/Map.html", "r").read()
else:
query = f"""SELECT SUM(cantidad_pedido) as cantidad, COUNT(DISTINCT T.tienda) as stores_count, T.COMUNA, SUM(h.valor_totalfactura) as total
from historicopedidos h
left join tiendas t on h.tienda = t.tienda
left join categorias c on h.Material = c.Material
WHERE Valor_TotalFactura > 0
AND Fecha_Pedido >= DATE '{start_date}'
AND Fecha_Pedido < DATE '{end_date}'
AND T.ciudad_tienda= '{city}'"""
if manu is not None and not manu == "":
query = query + f" AND h.fabricante='{manu}'"
if cat is not None and not cat == "":
query = query + f" AND c.nombre_cat =upper('{cat}')"
if sub_cat is not None and not sub_cat == "":
query = query + f" AND c.material ={sub_cat}"
query = query + " GROUP BY T.COMUNA"
series = run_query(query, engine)
series["comuna"] = series["comuna"].str.upper()
if Chart == 'Stores Heat Map':
fields = ["stores_count", "cantidad", "total"]
aliases = [
"Total Stores", "Total Sales Units",
"Total invoice value $"
]
caption = f"Total Number of Stores by location"
else:
fields = ["cantidad", "stores_count", "total"]
aliases = [
"Total Sales Units", "Total Stores",
"Total invoice value $"
]
caption = f"Total Number of Sales by location"
m = heatmap(series, geodata, location, fields, aliases, caption)
m.save("models/Geolocation/Maps/Map.html")
file = open("models/Geolocation/Maps/Map.html", "r").read()
#except:
# print("Chart Type is not selected")
# file = ""
return [file]
