#! /usr/bin/env python
#  -*- coding: utf-8 -*-
#
# GUI module generated by PAGE version 4.19
#  in conjunction with Tcl version 8.6
#    Nov 25, 2018 08:39:54 PM 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_catalogue = pd.read_sql_query('select * from supplier_catalogue', conn)



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 Orders_support

def vp_start_gui():
    '''Starting point when module is the main routine.'''
    global val, w, root
    root = tk.Tk()
    top = Toplevel1 (root)
    Orders_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)
    Orders_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("1320x640+19+130")
        top.title("Orders")
        top.configure(background="#d9d9d9")
        top.wm_iconbitmap('logo.ico')

        self.Scrolledlistbox1 = ScrolledListBox(top)
        self.Scrolledlistbox1.place(relx=0.008, rely=0.125, relheight=0.398
                                    , relwidth=0.986)
        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)

        def generate_order_ID():
            input_element_ID = str(self.Entry1.get())
            for i in range(len(df_ETOs)):
                if df_ETOs.loc[i, 'element_ID'] == input_element_ID:
                    ele_family = df_ETOs.loc[i, 'Family']
                    ele_type = df_ETOs.loc[i, 'Type']
                    for j in range(len(df_catalogue)):
                        if ele_family==df_catalogue.loc[j,'Family'] and ele_type==df_catalogue.loc[j,'Type']:
                            a = df_ETOs.loc[i,'task_ID']
                            df_ETOs.loc[i, 'order_ID'] = a[-7:] + '_' + df_catalogue.loc[j,'catalogue_ID']
                            messagebox.showinfo("Notification", df_ETOs.loc[i,'order_ID'])
            df_ETOs.to_sql('Structural_Column_Material_Takeoff', conn, if_exists='replace', index=False)
            conn.commit()


        self.Button1 = tk.Button(top, command = generate_order_ID)
        self.Button1.place(relx=0.348, rely=0.031, height=24, width=207)
        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='''Generate order IDs''')
        self.Button1.configure(width=207)

        self.Label1 = tk.Label(top)
        self.Label1.place(relx=0.008, rely=0.031, height=21, width=194)
        self.Label1.configure(background="#bcd8d1")
        self.Label1.configure(disabledforeground="#a3a3a3")
        self.Label1.configure(foreground="#000000")
        self.Label1.configure(text='''Select element ID''')
        self.Label1.configure(width=194)

        self.Entry1 = tk.Entry(top)
        self.Entry1.place(relx=0.167, rely=0.031,height=20, relwidth=0.17)
        self.Entry1.configure(background="white")
        self.Entry1.configure(disabledforeground="#a3a3a3")
        self.Entry1.configure(font=font10)
        self.Entry1.configure(foreground="#000000")
        self.Entry1.configure(insertbackground="black")
        self.Entry1.configure(width=224)

        self.Label1_1 = tk.Label(top)
        self.Label1_1.place(relx=0.008, rely=0.078, height=21, width=194)
        self.Label1_1.configure(activebackground="#f9f9f9")
        self.Label1_1.configure(activeforeground="black")
        self.Label1_1.configure(background="#bcd8d1")
        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='''Enter look-ahead need date''')

        self.Entry2 = tk.Entry(top)
        self.Entry2.place(relx=0.167, rely=0.078,height=20, relwidth=0.17)
        self.Entry2.configure(background="white")
        self.Entry2.configure(disabledforeground="#a3a3a3")
        self.Entry2.configure(font=font10)
        self.Entry2.configure(foreground="#000000")
        self.Entry2.configure(insertbackground="black")
        self.Entry2.configure(width=224)


        def Query_look_ahead_need():
            self.Scrolledlistbox1.delete('0', 'END')
            tree_1 = ttk.Treeview(self.Scrolledlistbox1, columns=(1, 2, 3), 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.pack(fill='both')
            scroll = ttk.Scrollbar(self.Scrolledlistbox1, orient='vertical', command=tree_1.yview)
            tree_1.configure(yscrollcommand=scroll.set)

            Need_date = str(self.Entry2.get())
            Need_date = pd.to_datetime(Need_date)
            for i in range(len(df_tasks)):
                a = pd.to_datetime(df_tasks.loc[i,'Start_Date'])
                if a == Need_date:
                    b = df_tasks.loc[i,'task_ID']
                    b = b[-7:]
                    for j in range(len(df_ETOs)):
                        if b in df_ETOs.loc[j,'task_ID']:
                            tree_1.insert('','end', values = (df_ETOs.loc[j, 'order_ID'],df_ETOs.loc[j,'element_ID'],df_ETOs.loc[j,'Family']+' '+df_ETOs.loc[j,'Type']))

        self.Button2 = tk.Button(top,command = Query_look_ahead_need)
        self.Button2.place(relx=0.348, rely=0.078, height=24, width=207)
        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 orders''')
        self.Button2.configure(width=207)


        self.Label1_3 = tk.Label(top)
        self.Label1_3.place(relx=0.008, rely=0.547, height=21, width=194)
        self.Label1_3.configure(activebackground="#f9f9f9")
        self.Label1_3.configure(activeforeground="black")
        self.Label1_3.configure(background="#bcd8d1")
        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='''Enter Family or Type''')

        self.Entry1_4 = tk.Entry(top)
        self.Entry1_4.place(relx=0.167, rely=0.547,height=20, relwidth=0.17)
        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.Scrolledlistbox1_7 = ScrolledListBox(top)
        self.Scrolledlistbox1_7.place(relx=0.008, rely=0.594, relheight=0.383
                                      , relwidth=0.986)
        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)

        def Query_by_family():
            self.Scrolledlistbox1_7.delete('0', 'END')
            Family_name = str(self.Entry1_4.get())
            a = []
            c = []
            for i in range(len(df_ETOs)):
                if df_ETOs.loc[i,'Family'] == Family_name or df_ETOs.loc[i,'Type'] == Family_name:
                    a.append(df_ETOs.loc[i,'element_ID'])
                    c.append(df_ETOs.loc[i,'order_ID'])
                    c = list(set(c))
            for j in range(len(df_type_tracking)):
                if df_type_tracking.loc[j,'Family'] == Family_name or df_type_tracking.loc[j,'Type'] == Family_name:
                    b = df_type_tracking.loc[j,'Count']
            self.Scrolledlistbox1_7.insert(1, 'elements of this family or type:' + str(a))
            self.Scrolledlistbox1_7.insert(2, "total amount of this Type is: " + str(b))
            self.Scrolledlistbox1_7.insert(3, 'orders of this family or type:' + str(c))

        self.Button1_5 = tk.Button(top,command = Query_by_family)
        self.Button1_5.place(relx=0.348, rely=0.547, height=24, width=207)
        self.Button1_5.configure(activebackground="#ececec")
        self.Button1_5.configure(activeforeground="#000000")
        self.Button1_5.configure(background="#d9d9d9")
        self.Button1_5.configure(disabledforeground="#a3a3a3")
        self.Button1_5.configure(foreground="#000000")
        self.Button1_5.configure(highlightbackground="#d9d9d9")
        self.Button1_5.configure(highlightcolor="black")
        self.Button1_5.configure(pady="0")
        self.Button1_5.configure(text='''View orders''')



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





