Bring Drag & Drop to Dash with Dashboard Engine. 💫 Learn how at our next webinar!

Dash sql input,output question

Hello people,

I’m doing a project and I need some help regarding some inputs.I have an sql query that I made that lists how long some pc’s don’t work by day,week and month.Now I have made a full front dash app but without any outpust as I don’t really understand them and the dcc.Store part.My question is what do I put into my input and output regarding my sql query,and what into the dcc.store so it gives me the graphs?The code is below.

import dash_bootstrap_components as dbc
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output, State
import datetime
#import connect
import pandas as pd
from app import app
from datetime import datetime as dt
from datetime import timedelta
import dash
import plotly.graph_objects as go
import distutils.dist
import utils
import constants
import mssql_queries
import mssql_conn
import mysql_queries
import base64
import io
import flask 
import os
import sys
from waitress import serve
import json
#import kaleido
import plotly
import base64
from app import app
import utils
import mysql_queries
import mssql_queries
import mssql_conn
import constants

def convert_timedelta(timedelta):
    total_seconds = timedelta.total_seconds()
    hours = int(total_seconds // 3600)
    minutes = int(total_seconds // 60 % 60)
    seconds = int(total_seconds % 60)
    if hours < 10:
        hours = "0" + str(hours)
    if minutes < 10:
        minutes = "0" + str(minutes)
    if seconds < 10:
        seconds = "0" + str(seconds)
    return str(hours) + ":" + str(minutes) + ":" + str(seconds)

def convert_timedelta(timedelta):
    total_seconds = timedelta.total_seconds()
    hours = int(total_seconds // 3600)
    minutes = int(total_seconds // 60 % 60)
    seconds = int(total_seconds % 60)
    if hours < 10:
        hours = "0" + str(hours)
    if minutes < 10:
        minutes = "0" + str(minutes)
    if seconds < 10:
        seconds = "0" + str(seconds)
    return str(hours) + ":" + str(minutes) + ":" + str(seconds)

def convert_time_to_string(timedelta):
    total_seconds = timedelta
    hours = int(total_seconds // 3600)
    minutes = int(total_seconds // 60 % 60)
    seconds = int(total_seconds % 60)
    if hours < 10:
        hours = "0" + str(hours)
    if minutes < 10:
        minutes = "0" + str(minutes)
    if seconds < 10:
        seconds = "0" + str(seconds)
    return str(hours) + ":" + str(minutes) + ":" + str(seconds)

def convert_time_to_string_to_minutes(time_string):
    indices = time_string.split(":")
    indices = [int(i) for i in indices]
    total_time = int(indices[0] * 60 + indices[1] + indices[2] / 60)
    return total_time
    

controls = dbc.FormGroup(
    [
        dbc.Row([
            dbc.Col(
            html.Div(["Start Time: ", constants.start_date_picker],
                    ),
                ),
            dbc.Col(
                html.Div([ constants.start_time_picker ])
            )
        ],style={"marginBottom":"0.5em"}),
                dbc.Row([
            dbc.Col(
            html.Div(["End Time: ", constants.end_date_picker]),
                ),
            dbc.Col(constants.end_time_picker 
                ),
        ]),
     html.Hr(),
    dbc.Row([
        dbc.Col([
                    html.Div(["Theoretical Cycle Time (in s):"]),
            ]),
        dbc.Col([
                   dcc.Input(id="taktzeit", type="text", persistence=True, required=True),
            ])
    ],style={"marginBottom":"0.5em"}
    ),
    dbc.Row([
        dbc.Col([
                    html.Div(["Theoretical Efficiency (in %)"])
            ]),
        dbc.Col([
                   dcc.Input(id="effizienz", type="text", persistence=True, required=True)
            ])
    ],style={"marginBottom":"0.5em"}),   
    # html.Hr(),
    # dbc.Row([
        # dbc.Col([
        #     # html.Div(["Planned Qty"]),
        # ]),
    # dbc.Col([
    # dcc.Input(id="planned_qty", type="text", persistence=True, required=True),
    # ])
    # ],style={"marginBottom":"0.5em"}
    #  ),
    # html.Hr(),
    dbc.Row([
         dbc.Col([
                     html.Div(["Database"])
             ]),
         dbc.Col([
                     constants.database
             ])
     ],style={"marginBottom":"1em"}),          
    dbc.Row([
        dbc.Col([
                    html.Div(["Type"])
            ]),
        dbc.Col([
                    constants.part_type_selection
            ])
    ],style={"marginBottom":"1em"}),
    dbc.Row([
        # dbc.Col([
        #             html.Div(["Worker Name"])
        #     ]),
        # dbc.Col([
        #             constants.worker_selection
        #     ])
    ],style={"marginBottom":"1em"}),
    dbc.Row([
        dbc.Col([
                    html.Div(["Language"])
            ]),
        dbc.Col([
                    constants.language
            ])
    ],style={"marginBottom":"0.5em"}),
    html.Hr(),
    dbc.Button(
        id='submit_button',
        n_clicks=0,
        children='Submit',
        color='primary',
        block=True
        ),
    html.Hr(),
    html.Div(id="alert7"),
    html.Div(id="alert12"),
    html.Div(id="alert13"),
    html.Div(id="link7"),
    html.Div(id="link12")
    ]
)

sidebar = html.Div(
    [
        html.H2('APP7', style=constants.TEXT_STYLE),
        # html.H3('Parameters', style=constants.TEXT_STYLE),
        html.Hr(),
        controls
    ],
    style=constants.SIDEBAR_STYLE,
)

content = html.Div(
     [
         dcc.Tabs([
             dcc.Tab(label='Report', children=[
                 dcc.Store(id="filtered_query_output"),
                 html.Div(id="Day", style={"display" : "none"}),
                 dcc.Store(id="Month"),
                 html.Div(id='Week', style={'display': 'none'}),
                 html.Div(id='file_dates', style={'display': 'none'}),
                 dcc.Store(id="pareto_graph"),
                 dcc.Store(id="production_graph"),
                 html.Div(id="table1"),
                 dcc.Store(id="Timestamp"),
                 dcc.Store(id="Day"),
                 dcc.Store(id="MONTH"),
                 dcc.Store(id="excel_data_error_log"),
                 html.Hr(),
                 dbc.Row([
                     dbc.Col([
                         html.Div(children=html.Strong(id='Day'))
                     ]),
                     dbc.Col([
                         html.Div(children=html.Strong(id='Month'))
                     ])
                 ],style={"marginBottom":"0.5em"}),
                 dbc.Row([
                     dbc.Col([
                     html.Div(children=html.Strong(id='Day'))
                     ]),
                     dbc.Col([
                         html.Div(children=html.Strong(id='Month'))
                     ]),
                     dbc.Col([
                         html.Div(children=html.Strong(id='Week'))
                     ])    
                 ])
             ]),
         ]),               
         dcc.Tabs([
             dcc.Tab(label='Bar Chart', children=[
                 dcc.Store(id="Day"),
                 dcc.Store(id="Month"),
                 dcc.Store(id="Week"),
                 dcc.Graph(id="bar_chart", style={"height":800}),
                 dbc.Row([
                     dbc.Col([
                         html.Div(children=html.Strong(id='Day'))
                     ]),
                     dbc.Col([
                         html.Div(children=html.Strong(id='MONTH'))
                     ]),
                     dbc.Col([
                         html.Div(children=html.Strong(id="Week"))
                     ])
                 ])
             ]),
             dcc.Tab(label='Pie Chart', children=[
                 dcc.Graph(id="pie_chart", style={"height":800}),
             ]),
         ]),
     ],
     style=constants.CONTENT_STYLE,
     id="content"
)




layout = html.Div([sidebar, content])

layout = html.Div([sidebar, content])

def convert_time(timedelta):
    hours = int(timedelta // 3600)
    minutes = int(timedelta // 60 % 60)
    seconds = int(timedelta % 60)
    if hours < 10:
        hours = "0" + str(hours)
    if minutes < 10:
        minutes = "0" + str(minutes)
    if seconds < 10:
        seconds = "0" + str(seconds)
    return str(hours) + ":" + str(minutes) + ":" + str(seconds)

def boolean_helper(selector):
    if len(selector) > 1:
        return True
    else:
         return False




def download_sheet(output, start_date_picker, start_time_picker, end_date_picker, end_time_picker, database):
    if not output:
         return None
    df = pd.read_json(output, orient='split')
    df["Timestamp"] = df["Timestamp"].dt.tz_localize(None)
    start_time = datetime.datetime.strptime(start_date_picker, "%Y-%m-%d") + datetime.timedelta(hours=int(start_time_picker[0:2]))
    end_time = datetime.datetime.strptime(end_date_picker, "%Y-%m-%d") + datetime.timedelta(hours=int(end_time_picker[0:2]))
    xlsx_io = io.BytesIO()
    writer = pd.ExcelWriter(xlsx_io, engine='xlsxwriter', options={"remove_timezone" : True})
    df.to_excel(writer, sheet_name="App7")
    writer.save()
    xlsx_io.seek(0)
    # https://en.wikipedia.org/wiki/Data_URI_scheme
    media_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    data = base64.b64encode(xlsx_io.read()).decode("utf-8")
    href_data_downloadable = f'data:{media_type};base64,{data}'
    file_name = "App7-{0}-{1}_{2}.xlsx".format(str(database), start_time, end_time)
    return html.A("Download Excel Sheet", download=file_name