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]