#! /usr/bin/env python
#  -*- coding: utf-8 -*-
#
# GUI module generated by PAGE version 4.19
#  in conjunction with Tcl version 8.6
#    Dec 07, 2018 10:09:38 AM CET  platform: Windows NT
import _constants
today = _constants.Current_date()

import datetime
from datetime import timedelta
import pandas as pd
import sqlite3
from tkinter import messagebox
from tkinter import *
import time


pd.set_option('display.max_columns', 500)

conn = sqlite3.connect(
    'C:\\Users\\chenqi\\polybox\\Qian\\1 Doctoral Research\\16.02-AMF\\P6 GUI development\\data\\Aturm.sqlite')
cur = conn.cursor()

df_MP = pd.read_sql_query('select * from manufacturing_process', conn)
df_ETOs = pd.read_sql_query('select * from Structural_Column_Material_Takeoff', conn)
df_changes_field = pd.read_sql_query('select * from changes_field', conn)
df_changes_production = pd.read_sql_query('select * from changes_production', conn)
df_tasks = pd.read_sql_query('select * from Tasks', conn)
df_transport = pd.read_sql_query('select * from transport_process', conn)
df_transport['release_for_transport_day'] = pd.to_datetime(df_transport['release_for_transport_day'])


import sys

try:
    import Tkinter as tk
except ImportError:
    import tkinter as tk

try:
    import ttk
    py3 = False
except ImportError:
    import tkinter.ttk as ttk
    py3 = True

import Productions_support

def vp_start_gui():
    '''Starting point when module is the main routine.'''
    global val, w, root
    root = tk.Tk()
    top = Toplevel1 (root)
    Productions_support.init(root, top)
    root.mainloop()

w = None
def create_Toplevel1(root, *args, **kwargs):
    '''Starting point when module is imported by another program.'''
    global w, w_win, rt
    rt = root
    w = tk.Toplevel (root)
    top = Toplevel1 (w)
    Productions_support.init(w, top, *args, **kwargs)
    return (w, top)

def destroy_Toplevel1():
    global w
    w.destroy()
    w = None

class Toplevel1:
    def __init__(self, top=None):
        '''This class configures and populates the toplevel window.
           top is the toplevel containing window.'''
        _bgcolor = '#d9d9d9'  # X11 color: 'gray85'
        _fgcolor = '#000000'  # X11 color: 'black'
        _compcolor = '#d9d9d9' # X11 color: 'gray85'
        _ana1color = '#d9d9d9' # X11 color: 'gray85' 
        _ana2color = '#ececec' # Closest X11 color: 'gray92' 
        font10 = "-family {Courier New} -size 10 -weight normal -slant"  \
            " roman -underline 0 -overstrike 0"
        font9 = "-family {Segoe UI} -size 9 -weight normal -slant "  \
            "roman -underline 0 -overstrike 0"
        self.style = ttk.Style()
        if sys.platform == "win32":
            self.style.theme_use('winnative')
        self.style.configure('.',background=_bgcolor)
        self.style.configure('.',foreground=_fgcolor)
        self.style.configure('.',font="TkDefaultFont")
        self.style.map('.',background=
            [('selected', _compcolor), ('active',_ana2color)])

        top.geometry("883x910+273+222")
        top.title("Material production")
        top.configure(background="#d9d9d9")
        top.configure(highlightbackground="#d9d9d9")
        top.configure(highlightcolor="black")
        top.wm_iconbitmap('logo.ico')

        self.Label1 = tk.Label(top)
        self.Label1.place(relx=0.011, rely=0.011, height=21, width=264)
        self.Label1.configure(activebackground="#f9f9f9")
        self.Label1.configure(activeforeground="#000000")
        self.Label1.configure(background="#b1d8cf")
        self.Label1.configure(disabledforeground="#a3a3a3")
        self.Label1.configure(foreground="#000000")
        self.Label1.configure(highlightbackground="#d9d9d9")
        self.Label1.configure(highlightcolor="black")
        self.Label1.configure(text='''Input order ID''')

        self.Entry1 = tk.Entry(top)
        self.Entry1.place(relx=0.317, rely=0.231,height=20, relwidth=0.31)
        self.Entry1.configure(background="white")
        self.Entry1.configure(disabledforeground="#a3a3a3")
        self.Entry1.configure(font=font10)
        self.Entry1.configure(foreground="#000000")
        self.Entry1.configure(highlightbackground="#d9d9d9")
        self.Entry1.configure(highlightcolor="black")
        self.Entry1.configure(insertbackground="black")
        self.Entry1.configure(selectbackground="#c4c4c4")
        self.Entry1.configure(selectforeground="black")

        self.Label1_1 = tk.Label(top)
        self.Label1_1.place(relx=0.011, rely=0.121, height=21, width=264)
        self.Label1_1.configure(activebackground="#f9f9f9")
        self.Label1_1.configure(activeforeground="#000000")
        self.Label1_1.configure(background="#b1d8cf")
        self.Label1_1.configure(disabledforeground="#a3a3a3")
        self.Label1_1.configure(foreground="#000000")
        self.Label1_1.configure(highlightbackground="#d9d9d9")
        self.Label1_1.configure(highlightcolor="black")
        self.Label1_1.configure(text='''Input manufacturing lead time''')

        self.Labelframe1 = tk.LabelFrame(top)
        self.Labelframe1.place(relx=0.011, rely=0.473, relheight=0.511
                , relwidth=0.974)
        self.Labelframe1.configure(relief='groove')
        self.Labelframe1.configure(foreground="black")
        self.Labelframe1.configure(text='''Track the production runs''')
        self.Labelframe1.configure(background="#d9d9d9")
        self.Labelframe1.configure(highlightbackground="#d9d9d9")
        self.Labelframe1.configure(highlightcolor="black")
        self.Labelframe1.configure(width=860)

        self.Scrolledlistbox1 = ScrolledListBox(self.Labelframe1)
        self.Scrolledlistbox1.place(relx=0.012, rely=0.129, relheight=0.376
                , relwidth=0.978, bordermode='ignore')
        self.Scrolledlistbox1.configure(background="white")
        self.Scrolledlistbox1.configure(disabledforeground="#a3a3a3")
        self.Scrolledlistbox1.configure(font=font10)
        self.Scrolledlistbox1.configure(foreground="black")
        self.Scrolledlistbox1.configure(highlightbackground="#d9d9d9")
        self.Scrolledlistbox1.configure(highlightcolor="#d9d9d9")
        self.Scrolledlistbox1.configure(selectbackground="#c4c4c4")
        self.Scrolledlistbox1.configure(selectforeground="black")
        self.Scrolledlistbox1.configure(width=10)

        self.Label1_3 = tk.Label(self.Labelframe1)
        self.Label1_3.place(relx=0.012, rely=0.065, height=21, width=154
                , bordermode='ignore')
        self.Label1_3.configure(activebackground="#f9f9f9")
        self.Label1_3.configure(activeforeground="#000000")
        self.Label1_3.configure(background="#b1d8cf")
        self.Label1_3.configure(disabledforeground="#a3a3a3")
        self.Label1_3.configure(foreground="#000000")
        self.Label1_3.configure(highlightbackground="#d9d9d9")
        self.Label1_3.configure(highlightcolor="black")
        self.Label1_3.configure(text='''track by order ID''')

        self.Entry3 = tk.Entry(self.Labelframe1)
        self.Entry3.place(relx=0.198, rely=0.065, height=24, relwidth=0.237
                , bordermode='ignore')
        self.Entry3.configure(background="white")
        self.Entry3.configure(disabledforeground="#a3a3a3")
        self.Entry3.configure(font=font10)
        self.Entry3.configure(foreground="#000000")
        self.Entry3.configure(highlightbackground="#d9d9d9")
        self.Entry3.configure(highlightcolor="black")
        self.Entry3.configure(insertbackground="black")
        self.Entry3.configure(selectbackground="#c4c4c4")
        self.Entry3.configure(selectforeground="black")

        def run_by_orderID():
            self.Scrolledlistbox1.delete('0', 'END')
            tree_1 = ttk.Treeview(self.Scrolledlistbox1, columns=(1, 2, 3, 4, 5), show='headings')
            tree_1.heading(1, text='order ID')
            tree_1.heading(2, text='element ID')
            tree_1.heading(3, text='family and type')
            tree_1.heading(4, text='supplier')
            tree_1.heading(5, text='current run')
            tree_1.pack(fill='both')
            scroll = ttk.Scrollbar(self.Scrolledlistbox1, orient='vertical', command=tree_1.yview)
            tree_1.configure(yscrollcommand=scroll.set)

            order_ID = self.Entry3.get()
            for i in range(len(df_MP)):
                for j in range(len(df_ETOs)):
                    if df_MP.loc[i, 'order_ID'] == order_ID and df_ETOs.loc[j, 'order_ID'] == order_ID:
                        tree_1.insert('', 'end', values = (df_MP.loc[i, 'order_ID'],df_ETOs.loc[j, 'element_ID'], df_ETOs.loc[j, 'Family'] + ' ' + df_ETOs.loc[j, 'Type'],
                                                           df_MP.loc[i, 'supplier_name'], df_MP.loc[i, 'production_run']))
                    else:
                        pass


        self.Button4 = tk.Button(self.Labelframe1, command = run_by_orderID)
        self.Button4.place(relx=0.500, rely=0.065, height=23, width=76
                           , bordermode='ignore')
        self.Button4.configure(activebackground="#ececec")
        self.Button4.configure(activeforeground="#000000")
        self.Button4.configure(background="#d9d9d9")
        self.Button4.configure(disabledforeground="#a3a3a3")
        self.Button4.configure(foreground="#000000")
        self.Button4.configure(highlightbackground="#d9d9d9")
        self.Button4.configure(highlightcolor="black")
        self.Button4.configure(pady="0")
        self.Button4.configure(text='''Query''')

        self.Label1_4 = tk.Label(self.Labelframe1)
        self.Label1_4.place(relx=0.012, rely=0.538, height=21, width=154
                , bordermode='ignore')
        self.Label1_4.configure(activebackground="#f9f9f9")
        self.Label1_4.configure(activeforeground="#000000")
        self.Label1_4.configure(background="#b1d8cf")
        self.Label1_4.configure(disabledforeground="#a3a3a3")
        self.Label1_4.configure(foreground="#000000")
        self.Label1_4.configure(highlightbackground="#d9d9d9")
        self.Label1_4.configure(highlightcolor="black")
        self.Label1_4.configure(text='''track by task ID''')

        self.Entry3_5 = tk.Entry(self.Labelframe1)
        self.Entry3_5.place(relx=0.198, rely=0.538, height=20, relwidth=0.237
                , bordermode='ignore')
        self.Entry3_5.configure(background="white")
        self.Entry3_5.configure(disabledforeground="#a3a3a3")
        self.Entry3_5.configure(font=font10)
        self.Entry3_5.configure(foreground="#000000")
        self.Entry3_5.configure(highlightbackground="#d9d9d9")
        self.Entry3_5.configure(highlightcolor="black")
        self.Entry3_5.configure(insertbackground="black")
        self.Entry3_5.configure(selectbackground="#c4c4c4")
        self.Entry3_5.configure(selectforeground="black")


        def run_by_taskID():
            self.Scrolledlistbox1_7.delete('0', 'END')
            tree_1 = ttk.Treeview(self.Scrolledlistbox1_7, columns=(1, 2, 3, 4, 5), show='headings')
            tree_1.heading(1, text='task ID')
            tree_1.heading(2, text='element ID')
            tree_1.heading(3, text='family and type')
            tree_1.heading(4, text='supplier')
            tree_1.heading(5, text='current run')
            tree_1.pack(fill='both')
            scroll = ttk.Scrollbar(self.Scrolledlistbox1_7, orient='vertical', command=tree_1.yview)
            tree_1.configure(yscrollcommand=scroll.set)

            task_ID = self.Entry3_5.get()
            for j in range(len(df_ETOs)):
                if df_ETOs.loc[j, 'task_ID'] == task_ID:
                    a = df_ETOs.loc[j, 'order_ID']
                    for i in range(len(df_MP)):
                        if df_MP.loc[i, 'order_ID'] == a:
                            tree_1.insert('', 'end', values = (df_ETOs.loc[j, 'task_ID'],df_ETOs.loc[j, 'element_ID'], df_ETOs.loc[j, 'Family'] + ' ' + df_ETOs.loc[j, 'Type'],
                                                           df_MP.loc[i, 'supplier_name'], df_MP.loc[i, 'production_run']))
                        else:
                            pass
                else:
                    pass

        self.Button5 = tk.Button(self.Labelframe1, command=run_by_taskID)
        self.Button5.place(relx=0.500, rely=0.538, height=23, width=76
                           , bordermode='ignore')
        self.Button5.configure(activebackground="#ececec")
        self.Button5.configure(activeforeground="#000000")
        self.Button5.configure(background="#d9d9d9")
        self.Button5.configure(disabledforeground="#a3a3a3")
        self.Button5.configure(foreground="#000000")
        self.Button5.configure(highlightbackground="#d9d9d9")
        self.Button5.configure(highlightcolor="black")
        self.Button5.configure(pady="0")
        self.Button5.configure(text='''Query''')

        self.Scrolledlistbox1_7 = ScrolledListBox(self.Labelframe1)
        self.Scrolledlistbox1_7.place(relx=0.012, rely=0.602, relheight=0.376
                , relwidth=0.978, bordermode='ignore')
        self.Scrolledlistbox1_7.configure(background="white")
        self.Scrolledlistbox1_7.configure(disabledforeground="#a3a3a3")
        self.Scrolledlistbox1_7.configure(font=font10)
        self.Scrolledlistbox1_7.configure(foreground="black")
        self.Scrolledlistbox1_7.configure(highlightbackground="#d9d9d9")
        self.Scrolledlistbox1_7.configure(highlightcolor="#d9d9d9")
        self.Scrolledlistbox1_7.configure(selectbackground="#c4c4c4")
        self.Scrolledlistbox1_7.configure(selectforeground="black")
        self.Scrolledlistbox1_7.configure(width=10)

        self.Entry1_3 = tk.Entry(top)
        self.Entry1_3.place(relx=0.317, rely=0.011,height=20, relwidth=0.31)
        self.Entry1_3.configure(background="white")
        self.Entry1_3.configure(disabledforeground="#a3a3a3")
        self.Entry1_3.configure(font=font10)
        self.Entry1_3.configure(foreground="#000000")
        self.Entry1_3.configure(highlightbackground="#d9d9d9")
        self.Entry1_3.configure(highlightcolor="black")
        self.Entry1_3.configure(insertbackground="black")
        self.Entry1_3.configure(selectbackground="#c4c4c4")
        self.Entry1_3.configure(selectforeground="black")

        self.Label1_2 = tk.Label(top)
        self.Label1_2.place(relx=0.011, rely=0.231, height=21, width=264)
        self.Label1_2.configure(activebackground="#f9f9f9")
        self.Label1_2.configure(activeforeground="#000000")
        self.Label1_2.configure(background="#b1d8cf")
        self.Label1_2.configure(disabledforeground="#a3a3a3")
        self.Label1_2.configure(foreground="#000000")
        self.Label1_2.configure(highlightbackground="#d9d9d9")
        self.Label1_2.configure(highlightcolor="black")
        self.Label1_2.configure(text='''Actual manufacturing lead time''')

        self.Entry1_4 = tk.Entry(top)
        self.Entry1_4.place(relx=0.317, rely=0.121,height=20, relwidth=0.31)
        self.Entry1_4.configure(background="white")
        self.Entry1_4.configure(disabledforeground="#a3a3a3")
        self.Entry1_4.configure(font=font10)
        self.Entry1_4.configure(foreground="#000000")
        self.Entry1_4.configure(highlightbackground="#d9d9d9")
        self.Entry1_4.configure(highlightcolor="black")
        self.Entry1_4.configure(insertbackground="black")
        self.Entry1_4.configure(selectbackground="#c4c4c4")
        self.Entry1_4.configure(selectforeground="black")

        self.Labelframe2 = tk.LabelFrame(top)
        self.Labelframe2.place(relx=0.657, rely=0.11, relheight=0.126
                , relwidth=0.317)
        self.Labelframe2.configure(relief='groove')
        self.Labelframe2.configure(foreground="black")
        self.Labelframe2.configure(text='''Forecasted Manufacturing lead time''')
        self.Labelframe2.configure(background="#d9d9d9")
        self.Labelframe2.configure(highlightbackground="#d9d9d9")
        self.Labelframe2.configure(highlightcolor="black")
        self.Labelframe2.configure(width=280)

        self.Text2 = tk.Text(self.Labelframe2)
        self.Text2.place(relx=0.036, rely=0.522, relheight=0.296, relwidth=0.943
                , bordermode='ignore')
        self.Text2.configure(background="white")
        self.Text2.configure(font=font9)
        self.Text2.configure(foreground="black")
        self.Text2.configure(highlightbackground="#d9d9d9")
        self.Text2.configure(highlightcolor="black")
        self.Text2.configure(insertbackground="black")
        self.Text2.configure(selectbackground="#c4c4c4")
        self.Text2.configure(selectforeground="black")
        self.Text2.configure(width=264)
        self.Text2.configure(wrap='word')


        def forecast_lt():
            messagebox.showinfo('Notification', 'computing process under construction')

        self.Button3 = tk.Button(self.Labelframe2, command=forecast_lt)
        self.Button3.place(relx=0.036, rely=0.261, height=23, width=76
                , bordermode='ignore')
        self.Button3.configure(activebackground="#ececec")
        self.Button3.configure(activeforeground="#000000")
        self.Button3.configure(background="#d9d9d9")
        self.Button3.configure(disabledforeground="#a3a3a3")
        self.Button3.configure(foreground="#000000")
        self.Button3.configure(highlightbackground="#d9d9d9")
        self.Button3.configure(highlightcolor="black")
        self.Button3.configure(pady="0")
        self.Button3.configure(text='''Query''')

        def view_pending_changes():
            input_order_ID = self.Entry1_3.get()
            pending_changes1 = []
            for i in range(len(df_ETOs)):
                if df_ETOs.loc[i, 'order_ID'] == input_order_ID:
                    a = df_ETOs.loc[i, 'element_ID']
                    for j1 in range(len(df_changes_field)):
                        if df_changes_field.loc[j1, 'element_ID']==a:
                            b1 = df_changes_field.loc[j1, 'change_status']
                            if b1 == 'Pending':
                                pending_changes1.append(df_changes_field.loc[j1, 'element_ID'])
                            else:
                                pass
                        else:
                            pass
                else:
                    pass
            messagebox.showinfo('notification','The following elements:' + str(pending_changes1) + 'request changes from field')

            pending_changes2 = []
            for i in range(len(df_ETOs)):
                if df_ETOs.loc[i, 'order_ID'] == input_order_ID:
                    a = df_ETOs.loc[i, 'element_ID']

                    for j2 in range(len(df_changes_production)):
                        if df_changes_production.loc[j2, 'element_ID'] == a:
                            b2 = df_changes_production.loc[j2, 'change_status']
                            if b2 == 'Pending':
                                pending_changes2.append(df_changes_production.loc[j2, 'element_ID'])
                            else:
                                pass
                        else:
                            pass
                else:
                    pass
            messagebox.showinfo('notification', 'The following elements:' + str(pending_changes2) + 'request changes from production')

        self.Button2 = tk.Button(top, command=view_pending_changes)
        self.Button2.place(relx=0.657, rely=0.011, height=23, width=286)
        self.Button2.configure(activebackground="#ececec")
        self.Button2.configure(activeforeground="#000000")
        self.Button2.configure(background="#d9d9d9")
        self.Button2.configure(disabledforeground="#a3a3a3")
        self.Button2.configure(foreground="#000000")
        self.Button2.configure(highlightbackground="#d9d9d9")
        self.Button2.configure(highlightcolor="black")
        self.Button2.configure(pady="0")
        self.Button2.configure(text='''view pending design changes''')





        def order_info():
            input_order_ID = self.Entry1_3.get()
            root1 = Tk()
            root1.title('Order information')
            root1.wm_iconbitmap('logo.ico')
            frame = Frame(root1)
            frame.pack()
            tree = ttk.Treeview(frame, columns=(1, 2, 3, 4, 5, 6, 7, 8), show='headings')
            tree.heading(1, text='order ID')
            tree.heading(2, text='element_ID')
            tree.heading(3, text='Family and Type')
            tree.heading(4, text='Material')
            tree.heading(5, text='Length')
            tree.heading(6, text='Load-bearing capacity')
            tree.heading(7, text='Need time')
            tree.heading(8, text= 'Supplier')
            tree.pack()

            scroll = ttk.Scrollbar(frame, orient="vertical", command=tree.yview)
            tree.configure(yscrollcommand=scroll.set)
            for i in range(len(df_ETOs)):
                if df_ETOs.loc[i, 'order_ID'] == input_order_ID:
                    a = df_ETOs.loc[i, 'task_ID']
                    for j in range(len(df_tasks)):
                        if a == df_tasks.loc[j, 'task_ID']:
                            need_time = df_tasks.loc[j, 'Start_Date']
                    for k in range(len(df_MP)):
                        if input_order_ID == df_MP.loc[k, 'order_ID']:
                            supplier = df_MP.loc[k, 'supplier_name']
                    tree.insert('', 'end', values=(df_ETOs.loc[i, 'order_ID'], df_ETOs.loc[i,'element_ID'], df_ETOs.loc[i, 'Family']+" "+df_ETOs.loc[i, 'Type'],
                                                   df_ETOs.loc[i, 'Material:_Name'], df_ETOs.loc[i, 'Length'], df_ETOs.loc[i, 'load-bearing_capacity'], str(need_time),str(supplier)))

            root1.mainloop()

        self.Button2_4 = tk.Button(top, command=order_info)
        self.Button2_4.place(relx=0.657, rely=0.044, height=23, width=286)
        self.Button2_4.configure(activebackground="#ececec")
        self.Button2_4.configure(activeforeground="#000000")
        self.Button2_4.configure(background="#d9d9d9")
        self.Button2_4.configure(disabledforeground="#a3a3a3")
        self.Button2_4.configure(foreground="#000000")
        self.Button2_4.configure(highlightbackground="#d9d9d9")
        self.Button2_4.configure(highlightcolor="black")
        self.Button2_4.configure(pady="0")
        self.Button2_4.configure(text='''view order information''')


        def update_leadtime():
            input_order_ID = self.Entry1_3.get()
            Input_leadtime = self.Entry1_4.get()
            Actual_leatime = self.Entry1.get()
            for i in range(len(df_MP)):
                if df_MP.loc[i,'order_ID']==input_order_ID:
                    df_MP.loc[i, 'm_leadtime']=Input_leadtime
                    df_MP.loc[i, 'a_leadtime']=Actual_leatime
                else:
                    pass
            df_MP.to_sql('manufacturing_process', conn, if_exists='replace', index=False)
            conn.commit()

        self.Button3_5 = tk.Button(top, command=update_leadtime)
        self.Button3_5.place(relx=0.317, rely=0.264, height=23, width=76)
        self.Button3_5.configure(activebackground="#ececec")
        self.Button3_5.configure(activeforeground="#000000")
        self.Button3_5.configure(background="#d9d9d9")
        self.Button3_5.configure(disabledforeground="#a3a3a3")
        self.Button3_5.configure(foreground="#000000")
        self.Button3_5.configure(highlightbackground="#d9d9d9")
        self.Button3_5.configure(highlightcolor="black")
        self.Button3_5.configure(pady="0")
        self.Button3_5.configure(text='''Update''')


        self.Entry2 = tk.Entry(top)                                                               # need for update the information of which production runs?
        self.Entry2.place(relx=0.700, rely=0.407, height=20, relwidth=0.225)
        self.Entry2.configure(background="white")
        self.Entry2.configure(disabledforeground="#a3a3a3")
        self.Entry2.configure(font=font10)
        self.Entry2.configure(foreground="#000000")
        self.Entry2.configure(highlightbackground="#d9d9d9")
        self.Entry2.configure(highlightcolor="black")
        self.Entry2.configure(insertbackground="black")
        self.Entry2.configure(selectbackground="#c4c4c4")
        self.Entry2.configure(selectforeground="black")

        self.Label1_3 = tk.Label(top)
        self.Label1_3.place(relx=0.700, rely=0.380, height=21, width=200)
        self.Label1_3.configure(activebackground="#f9f9f9")
        self.Label1_3.configure(activeforeground="#000000")
        self.Label1_3.configure(background="#b1d8cf")
        self.Label1_3.configure(disabledforeground="#a3a3a3")
        self.Label1_3.configure(foreground="#000000")
        self.Label1_3.configure(highlightbackground="#d9d9d9")
        self.Label1_3.configure(highlightcolor="black")
        self.Label1_3.configure(text='''Input production run''')

        def update_runs():
            input_order_ID = self.Entry1_3.get()
            runs = self.Entry2.get()
            for i in range(len(df_MP)):
                if df_MP.loc[i, 'order_ID'] == input_order_ID:
                    df_MP.loc[i, 'production_run'] = runs
                else:
                    pass
            df_MP.to_sql('manufacturing_process', conn, if_exists='replace', index=False)
            conn.commit()

        self.Button3_7 = tk.Button(top, command=update_runs)
        self.Button3_7.place(relx=0.700, rely=0.435, height=23, width=76)
        self.Button3_7.configure(activebackground="#ececec")
        self.Button3_7.configure(activeforeground="#000000")
        self.Button3_7.configure(background="#d9d9d9")
        self.Button3_7.configure(disabledforeground="#a3a3a3")
        self.Button3_7.configure(foreground="#000000")
        self.Button3_7.configure(highlightbackground="#d9d9d9")
        self.Button3_7.configure(highlightcolor="black")
        self.Button3_7.configure(pady="0")
        self.Button3_7.configure(text='''Update Runs''')




        self.Label1_3 = tk.Label(top)
        self.Label1_3.place(relx=0.011, rely=0.319, height=21, width=264)
        self.Label1_3.configure(activebackground="#f9f9f9")
        self.Label1_3.configure(activeforeground="#000000")
        self.Label1_3.configure(background="#b1d8cf")
        self.Label1_3.configure(disabledforeground="#a3a3a3")
        self.Label1_3.configure(foreground="#000000")
        self.Label1_3.configure(highlightbackground="#d9d9d9")
        self.Label1_3.configure(highlightcolor="black")
        self.Label1_3.configure(text='''Latest date: release-for-manufacturing''')

        self.Text3 = tk.Text(top)
        self.Text3.place(relx=0.317, rely=0.352, relheight=0.02, relwidth=0.315
                , bordermode='ignore')
        self.Text3.configure(background="white")
        self.Text3.configure(font=font9)
        self.Text3.configure(foreground="black")
        self.Text3.configure(highlightbackground="#d9d9d9")
        self.Text3.configure(highlightcolor="black")
        self.Text3.configure(insertbackground="black")
        self.Text3.configure(selectbackground="#c4c4c4")
        self.Text3.configure(selectforeground="black")
        self.Text3.configure(width=264)
        self.Text3.configure(wrap='word')


        def query_release():
            input_order_ID = self.Entry1_3.get()
            for i in range(len(df_MP)):
                if df_MP.loc[i, 'order_ID'] == input_order_ID:
                    a = df_MP.loc[i, 'release_for_production_day']
                    for j in range(len(df_transport)):
                        if df_transport.loc[j, 'order_ID'] == input_order_ID:
                            b = df_transport.loc[j, 'release_for_transport_day']
                            a = b - timedelta(days= float(df_MP.loc[i, 'm_leadtime']))                            # calculation is wrong!!!!!!!!!!!!!
                            print(a)
                        else:
                            pass
                else:
                    pass
            self.Text3.insert(END, a)



        self.Button3_6 = tk.Button(top,command=query_release)
        self.Button3_6.place(relx=0.317, rely=0.319, height=23, width=76)
        self.Button3_6.configure(activebackground="#ececec")
        self.Button3_6.configure(activeforeground="#000000")
        self.Button3_6.configure(background="#d9d9d9")
        self.Button3_6.configure(disabledforeground="#a3a3a3")
        self.Button3_6.configure(foreground="#000000")
        self.Button3_6.configure(highlightbackground="#d9d9d9")
        self.Button3_6.configure(highlightcolor="black")
        self.Button3_6.configure(pady="0")
        self.Button3_6.configure(text='''Query''')

        self.Entry1_5 = tk.Entry(top)
        self.Entry1_5.place(relx=0.317, rely=0.407,height=20, relwidth=0.31)
        self.Entry1_5.configure(background="white")
        self.Entry1_5.configure(disabledforeground="#a3a3a3")
        self.Entry1_5.configure(font=font10)
        self.Entry1_5.configure(foreground="#000000")
        self.Entry1_5.configure(highlightbackground="#d9d9d9")
        self.Entry1_5.configure(highlightcolor="black")
        self.Entry1_5.configure(insertbackground="black")
        self.Entry1_5.configure(selectbackground="#c4c4c4")
        self.Entry1_5.configure(selectforeground="black")


        def update_release():
            input_order_ID = self.Entry1_3.get()
            Actual_release = self.Entry1_5.get()
            for i in range(len(df_MP)):
                if input_order_ID == df_MP.loc[i, 'order_ID']:
                    df_MP.loc[i, 'Actual_release_date'] = Actual_release
                else:
                    pass
            df_MP.to_sql('manufacturing_process', conn, if_exists='replace', index=False)
            conn.commit()

        self.Button3_7 = tk.Button(top,command=update_release)
        self.Button3_7.place(relx=0.317, rely=0.44, height=23, width=76)
        self.Button3_7.configure(activebackground="#ececec")
        self.Button3_7.configure(activeforeground="#000000")
        self.Button3_7.configure(background="#d9d9d9")
        self.Button3_7.configure(disabledforeground="#a3a3a3")
        self.Button3_7.configure(foreground="#000000")
        self.Button3_7.configure(highlightbackground="#d9d9d9")
        self.Button3_7.configure(highlightcolor="black")
        self.Button3_7.configure(pady="0")
        self.Button3_7.configure(text='''Update''')

        self.TSeparator1 = ttk.Separator(top)
        self.TSeparator1.place(relx=0.011, rely=0.308, relwidth=0.963)

        self.Label1_4 = tk.Label(top)
        self.Label1_4.place(relx=0.011, rely=0.407, height=21, width=264)
        self.Label1_4.configure(activebackground="#f9f9f9")
        self.Label1_4.configure(activeforeground="#000000")
        self.Label1_4.configure(background="#b1d8cf")
        self.Label1_4.configure(disabledforeground="#a3a3a3")
        self.Label1_4.configure(foreground="#000000")
        self.Label1_4.configure(highlightbackground="#d9d9d9")
        self.Label1_4.configure(highlightcolor="black")
        self.Label1_4.configure(text='''Input date: release-for-manufacturing''')


# The following code is added to facilitate the Scrolled widgets you specified.
class AutoScroll(object):
    '''Configure the scrollbars for a widget.'''

    def __init__(self, master):
        #  Rozen. Added the try-except clauses so that this class
        #  could be used for scrolled entry widget for which vertical
        #  scrolling is not supported. 5/7/14.
        try:
            vsb = ttk.Scrollbar(master, orient='vertical', command=self.yview)
        except:
            pass
        hsb = ttk.Scrollbar(master, orient='horizontal', command=self.xview)

        #self.configure(yscrollcommand=_autoscroll(vsb),
        #    xscrollcommand=_autoscroll(hsb))
        try:
            self.configure(yscrollcommand=self._autoscroll(vsb))
        except:
            pass
        self.configure(xscrollcommand=self._autoscroll(hsb))

        self.grid(column=0, row=0, sticky='nsew')
        try:
            vsb.grid(column=1, row=0, sticky='ns')
        except:
            pass
        hsb.grid(column=0, row=1, sticky='ew')

        master.grid_columnconfigure(0, weight=1)
        master.grid_rowconfigure(0, weight=1)

        # Copy geometry methods of master  (taken from ScrolledText.py)
        if py3:
            methods = tk.Pack.__dict__.keys() | tk.Grid.__dict__.keys() \
                  | tk.Place.__dict__.keys()
        else:
            methods = tk.Pack.__dict__.keys() + tk.Grid.__dict__.keys() \
                  + tk.Place.__dict__.keys()

        for meth in methods:
            if meth[0] != '_' and meth not in ('config', 'configure'):
                setattr(self, meth, getattr(master, meth))

    @staticmethod
    def _autoscroll(sbar):
        '''Hide and show scrollbar as needed.'''
        def wrapped(first, last):
            first, last = float(first), float(last)
            if first <= 0 and last >= 1:
                sbar.grid_remove()
            else:
                sbar.grid()
            sbar.set(first, last)
        return wrapped

    def __str__(self):
        return str(self.master)

def _create_container(func):
    '''Creates a ttk Frame with a given master, and use this new frame to
    place the scrollbars and the widget.'''
    def wrapped(cls, master, **kw):
        container = ttk.Frame(master)
        container.bind('<Enter>', lambda e: _bound_to_mousewheel(e, container))
        container.bind('<Leave>', lambda e: _unbound_to_mousewheel(e, container))
        return func(cls, container, **kw)
    return wrapped

class ScrolledListBox(AutoScroll, tk.Listbox):
    '''A standard Tkinter Text widget with scrollbars that will
    automatically show/hide as needed.'''
    @_create_container
    def __init__(self, master, **kw):
        tk.Listbox.__init__(self, master, **kw)
        AutoScroll.__init__(self, master)

import platform
def _bound_to_mousewheel(event, widget):
    child = widget.winfo_children()[0]
    if platform.system() == 'Windows' or platform.system() == 'Darwin':
        child.bind_all('<MouseWheel>', lambda e: _on_mousewheel(e, child))
        child.bind_all('<Shift-MouseWheel>', lambda e: _on_shiftmouse(e, child))
    else:
        child.bind_all('<Button-4>', lambda e: _on_mousewheel(e, child))
        child.bind_all('<Button-5>', lambda e: _on_mousewheel(e, child))
        child.bind_all('<Shift-Button-4>', lambda e: _on_shiftmouse(e, child))
        child.bind_all('<Shift-Button-5>', lambda e: _on_shiftmouse(e, child))

def _unbound_to_mousewheel(event, widget):
    if platform.system() == 'Windows' or platform.system() == 'Darwin':
        widget.unbind_all('<MouseWheel>')
        widget.unbind_all('<Shift-MouseWheel>')
    else:
        widget.unbind_all('<Button-4>')
        widget.unbind_all('<Button-5>')
        widget.unbind_all('<Shift-Button-4>')
        widget.unbind_all('<Shift-Button-5>')

def _on_mousewheel(event, widget):
    if platform.system() == 'Windows':
        widget.yview_scroll(-1*int(event.delta/120),'units')
    elif platform.system() == 'Darwin':
        widget.yview_scroll(-1*int(event.delta),'units')
    else:
        if event.num == 4:
            widget.yview_scroll(-1, 'units')
        elif event.num == 5:
            widget.yview_scroll(1, 'units')

def _on_shiftmouse(event, widget):
    if platform.system() == 'Windows':
        widget.xview_scroll(-1*int(event.delta/120), 'units')
    elif platform.system() == 'Darwin':
        widget.xview_scroll(-1*int(event.delta), 'units')
    else:
        if event.num == 4:
            widget.xview_scroll(-1, 'units')
        elif event.num == 5:
            widget.xview_scroll(1, 'units')

if __name__ == '__main__':
    vp_start_gui()





