#! /usr/bin/env python
#  -*- coding: utf-8 -*-
#
# GUI module generated by PAGE version 4.19
#  in conjunction with Tcl version 8.6
#    Nov 27, 2018 12:41:51 AM CET  platform: Windows NT

import datetime
from datetime import timedelta
import pandas as pd
import sqlite3
from tkinter import messagebox
from tkinter import *
from PIL import ImageTk,Image

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_type_tracking = pd.read_sql_query('select * from type_tracking', conn)
df_ETOs= pd.read_sql_query('select * from Structural_Column_Material_Takeoff', conn)
df_tasks= pd.read_sql_query('select * from Tasks', conn)
df_tasks['Start_Date'] = pd.to_datetime(df_tasks['Start_Date'])
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'])
df_manufacture = pd.read_sql_query('select * from manufacturing_process', conn)
df_manufacture['release_for_production_day'] = pd.to_datetime(df_manufacture['release_for_production_day'])

df_status = pd.read_sql_query('select * from status', conn)

test_today = df_tasks.loc[10,'Start_Date']

import pytz
today_datetime = datetime.datetime.now(pytz.timezone('Europe/Zurich'))
today_datetime = str(today_datetime)
today_datetime = pd.to_datetime(today_datetime[:19])
print(today_datetime > test_today)

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 Mstatus_support

def vp_start_gui():
    '''Starting point when module is the main routine.'''
    global val, w, root
    root = tk.Tk()
    top = Toplevel1 (root)
    Mstatus_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)
    Mstatus_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"
        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.map('.',background=
            [('selected', _compcolor), ('active',_ana2color)])

        top.geometry("960x572+214+126")
        top.title("Material status")
        top.configure(background="#d9d9d9")
        top.configure(highlightbackground="#d9d9d9")
        top.configure(highlightcolor="black")
        top.wm_iconbitmap('logo.ico')

        self.Labelframe1 = tk.LabelFrame(top)
        self.Labelframe1.place(relx=0.021, rely=0.035, relheight=0.253
                , relwidth=0.969)
        self.Labelframe1.configure(relief='groove')
        self.Labelframe1.configure(foreground="black")
        self.Labelframe1.configure(text='''Query by element ID''')
        self.Labelframe1.configure(background="#d9d9d9")
        self.Labelframe1.configure(highlightbackground="#d9d9d9")
        self.Labelframe1.configure(highlightcolor="black")
        self.Labelframe1.configure(width=930)

        self.Label1 = tk.Label(self.Labelframe1)
        self.Label1.place(relx=0.011, rely=0.138, height=21, width=204
                , bordermode='ignore')
        self.Label1.configure(activebackground="#f9f9f9")
        self.Label1.configure(activeforeground="black")
        self.Label1.configure(background="#c1d8af")
        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 element ID''')

        self.Entry1 = tk.Entry(self.Labelframe1)
        self.Entry1.place(relx=0.247, rely=0.138, height=20, relwidth=0.241
                , bordermode='ignore')
        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.Scrolledlistbox1 = ScrolledListBox(self.Labelframe1)
        self.Scrolledlistbox1.place(relx=0.011, rely=0.414, relheight=0.448
                , relwidth=0.98, 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)








        for i in range(len(df_ETOs)):                                  # link element with manufacture/transport release date, then determine which status
            for j in range(len(df_transport)):
                if df_ETOs.loc[i, 'order_ID'] == df_transport.loc[j, 'order_ID']:
                    df_ETOs.loc[i, 'rel_transport'] = df_transport.loc[j, 'release_for_transport_day']
                else:
                    pass

        for i in range(len(df_ETOs)):
            for j in range(len(df_manufacture)):
                if df_ETOs.loc[i, 'order_ID'] == df_manufacture.loc[j, 'order_ID']:
                    df_ETOs.loc[i, 'rel_manufacture'] = df_manufacture.loc[j, 'release_for_production_day']
                else:
                    pass

        for i in range(len(df_ETOs)):
            if df_ETOs.loc[i, 'rel_manufacture'] <= test_today and df_ETOs.loc[i, 'rel_transport'] > test_today:
                df_ETOs.loc[i, 'Status'] = 'wait for delivery - store in consolidation center'
            elif df_ETOs.loc[i, 'rel_manufacture'] > test_today:
                df_ETOs.loc[i, 'Status'] = 'wait for manufacture - design coordination'
            elif df_ETOs.loc[i, 'rel_manufacture'] == test_today:
                df_ETOs.loc[i, 'Status'] = 'release for manufacture - design freeze'
            elif df_ETOs.loc[i, 'rel_transport'] == test_today:
                df_ETOs.loc[i, 'Status'] = 'release for delivery'
            elif df_ETOs.loc[i, 'rel_transport'] <= test_today:
                df_ETOs.loc[i, 'Status'] = 'onsite'
            else:
                pass

        for i in range(len(df_ETOs)):
            for j in range(len(df_status)):
                if df_status.loc[j, 'element_ID'] == df_ETOs.loc[i,'element_ID']:
                    df_status.loc[j, 'status'] = df_ETOs.loc[i, 'Status']
                else:
                    pass
        df_status.to_sql('status', conn, if_exists='replace', index=False)










        def Query_by_element_ID():
            self.Scrolledlistbox1.delete('0','END')
            input_element_ID = str(self.Entry1.get())
            self.Scrolledlistbox1.delete('0', 'END')

            tree = ttk.Treeview(self.Scrolledlistbox1,columns = (1,2,3),show = 'headings')
            tree.heading(1, text='element ID')
            tree.heading(2, text='Family and Type')
            tree.heading(3, text='Status')
            tree.pack(fill='both')
            scroll = ttk.Scrollbar(self.Scrolledlistbox1, orient="vertical", command=tree.yview)
            tree.configure(yscrollcommand=scroll.set)


            for i in range(len(df_ETOs)):
                if input_element_ID == df_ETOs.loc[i,'element_ID']:
                    tree.insert('','end',values=(df_ETOs.loc[i, 'element_ID'], df_ETOs.loc[i, 'Family']+' '+df_ETOs.loc[i, 'Type'], df_ETOs.loc[i,'Status']))
                else:
                    pass


        self.Button1 = tk.Button(self.Labelframe1, command = Query_by_element_ID)
        self.Button1.place(relx=0.505, rely=0.138, height=24, width=97
                , bordermode='ignore')
        self.Button1.configure(activebackground="#ececec")
        self.Button1.configure(activeforeground="#000000")
        self.Button1.configure(background="#d9d9d9")
        self.Button1.configure(disabledforeground="#a3a3a3")
        self.Button1.configure(foreground="#000000")
        self.Button1.configure(highlightbackground="#d9d9d9")
        self.Button1.configure(highlightcolor="black")
        self.Button1.configure(pady="0")
        self.Button1.configure(text='''Query''')
        self.Button1.configure(width=97)

        self.Labelframe1_1 = tk.LabelFrame(top)
        self.Labelframe1_1.place(relx=0.021, rely=0.297, relheight=0.341
                , relwidth=0.969)
        self.Labelframe1_1.configure(relief='groove')
        self.Labelframe1_1.configure(foreground="black")
        self.Labelframe1_1.configure(text='''Query by order ID''')
        self.Labelframe1_1.configure(background="#d9d9d9")
        self.Labelframe1_1.configure(highlightbackground="#d9d9d9")
        self.Labelframe1_1.configure(highlightcolor="black")
        self.Labelframe1_1.configure(width=930)

        self.Entry1_4 = tk.Entry(self.Labelframe1_1)
        self.Entry1_4.place(relx=0.247, rely=0.154, height=20, relwidth=0.241
                , bordermode='ignore')
        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.Scrolledlistbox2 = ScrolledListBox(self.Labelframe1_1)
        self.Scrolledlistbox2.place(relx=0.011, rely=0.308, relheight=0.641
                , relwidth=0.98, bordermode='ignore')
        self.Scrolledlistbox2.configure(background="white")
        self.Scrolledlistbox2.configure(disabledforeground="#a3a3a3")
        self.Scrolledlistbox2.configure(font=font10)
        self.Scrolledlistbox2.configure(foreground="black")
        self.Scrolledlistbox2.configure(highlightbackground="#d9d9d9")
        self.Scrolledlistbox2.configure(highlightcolor="#d9d9d9")
        self.Scrolledlistbox2.configure(selectbackground="#c4c4c4")
        self.Scrolledlistbox2.configure(selectforeground="black")
        self.Scrolledlistbox2.configure(width=10)

        def Query_by_order_ID():
            self.Scrolledlistbox2.delete('0', 'END')
            input_order_ID = self.Entry1_4.get()

            tree = ttk.Treeview(self.Scrolledlistbox2, columns=(1, 2, 3, 4), show='headings')
            tree.heading(1, text='order ID')
            tree.heading(2, text='RFID')
            tree.heading(3, text='Family and Type')
            tree.heading(4, text='Status')
            tree.pack(fill='both')
            scroll = ttk.Scrollbar(self.Scrolledlistbox2, orient="vertical", command=tree.yview)
            tree.configure(yscrollcommand=scroll.set)

            for i in range(len(df_ETOs)):
                if input_order_ID == df_ETOs.loc[i, 'order_ID']:
                    tree.insert('', 'end', values=(df_ETOs.loc[i,'order_ID'],df_ETOs.loc[i,'RFID'], df_ETOs.loc[i, 'Family']+' '+df_ETOs.loc[i, 'Type'],df_ETOs.loc[i, 'Status']))

        self.Button1_3 = tk.Button(self.Labelframe1_1,command = Query_by_order_ID)
        self.Button1_3.place(relx=0.505, rely=0.154, height=24, width=97
                , bordermode='ignore')
        self.Button1_3.configure(activebackground="#ececec")
        self.Button1_3.configure(activeforeground="#000000")
        self.Button1_3.configure(background="#d9d9d9")
        self.Button1_3.configure(disabledforeground="#a3a3a3")
        self.Button1_3.configure(foreground="#000000")
        self.Button1_3.configure(highlightbackground="#d9d9d9")
        self.Button1_3.configure(highlightcolor="black")
        self.Button1_3.configure(pady="0")
        self.Button1_3.configure(text='''Query''')
        self.Button1_3.configure(width=97)

        self.Label1_3 = tk.Label(top)
        self.Label1_3.place(relx=0.031, rely=0.35, height=21, width=204)
        self.Label1_3.configure(activebackground="#f9f9f9")
        self.Label1_3.configure(activeforeground="black")
        self.Label1_3.configure(background="#c1d8af")
        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 order ID''')

        self.Labelframe1_2 = tk.LabelFrame(top)
        self.Labelframe1_2.place(relx=0.021, rely=0.647, relheight=0.323
                , relwidth=0.969)
        self.Labelframe1_2.configure(relief='groove')
        self.Labelframe1_2.configure(foreground="black")
        self.Labelframe1_2.configure(text='''Query by task ID''')
        self.Labelframe1_2.configure(background="#d9d9d9")
        self.Labelframe1_2.configure(highlightbackground="#d9d9d9")
        self.Labelframe1_2.configure(highlightcolor="black")
        self.Labelframe1_2.configure(width=930)

        self.Entry1_3 = tk.Entry(self.Labelframe1_2)
        self.Entry1_3.place(relx=0.247, rely=0.162, height=20, relwidth=0.241
                , bordermode='ignore')
        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_4 = tk.Label(self.Labelframe1_2)
        self.Label1_4.place(relx=0.011, rely=0.162, height=21, width=204
                , bordermode='ignore')
        self.Label1_4.configure(activebackground="#f9f9f9")
        self.Label1_4.configure(activeforeground="black")
        self.Label1_4.configure(background="#c1d8af")
        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 task ID''')

        self.Scrolledlistbox3 = ScrolledListBox(self.Labelframe1_2)
        self.Scrolledlistbox3.place(relx=0.011, rely=0.324, relheight=0.622
                , relwidth=0.98, bordermode='ignore')
        self.Scrolledlistbox3.configure(background="white")
        self.Scrolledlistbox3.configure(disabledforeground="#a3a3a3")
        self.Scrolledlistbox3.configure(font=font10)
        self.Scrolledlistbox3.configure(foreground="black")
        self.Scrolledlistbox3.configure(highlightbackground="#d9d9d9")
        self.Scrolledlistbox3.configure(highlightcolor="#d9d9d9")
        self.Scrolledlistbox3.configure(selectbackground="#c4c4c4")
        self.Scrolledlistbox3.configure(selectforeground="black")
        self.Scrolledlistbox3.configure(width=10)

        def Query_by_task_ID():
            self.Scrolledlistbox3.delete('0', 'END')
            input_task_ID = str(self.Entry1_3.get())

            tree = ttk.Treeview(self.Scrolledlistbox3, columns=(1, 2, 3, 4), show='headings')
            tree.heading(1, text='task ID')
            tree.heading(2, text='element ID')
            tree.heading(3, text='Family and Type')
            tree.heading(4, text='Status')
            tree.pack(fill='both')
            scroll = ttk.Scrollbar(self.Scrolledlistbox3, orient="vertical", command=tree.yview)
            tree.configure(yscrollcommand=scroll.set)

            for i in range(len(df_ETOs)):
                if input_task_ID == df_ETOs.loc[i, 'task_ID']:
                    tree.insert('','end',values=(df_ETOs.loc[i,'task_ID'], df_ETOs.loc[i,'element_ID'],df_ETOs.loc[i,'Family']+' '+df_ETOs.loc[i,'Type'], df_ETOs.loc[i,'Status']))


        self.Button1_4 = tk.Button(self.Labelframe1_2,command = Query_by_task_ID)
        self.Button1_4.place(relx=0.505, rely=0.162, height=24, width=97
                , bordermode='ignore')
        self.Button1_4.configure(activebackground="#ececec")
        self.Button1_4.configure(activeforeground="#000000")
        self.Button1_4.configure(background="#d9d9d9")
        self.Button1_4.configure(disabledforeground="#a3a3a3")
        self.Button1_4.configure(foreground="#000000")
        self.Button1_4.configure(highlightbackground="#d9d9d9")
        self.Button1_4.configure(highlightcolor="black")
        self.Button1_4.configure(pady="0")
        self.Button1_4.configure(text='''Query''')

# 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()





