################################################################################
# Create a Tkinter screen to access a SQLite database
################################################################################
from   tkinter import *			    #import entire tkinter framework
from   tkinter import messagebox    #import popup dialogs 
import sql_local_exec               #accesses a local database
import sql_web_service              #calls a web service to access a server db  
import sql_col_width                #determines necessary width for each column 

### Methods to be called #######################################################

def display_results(result):                        #result is a list of dictionaries
    output.config(state='normal')                   #enable the output widget
    output.delete('1.0',END)                        #delete previous output
    if not result:
        output.insert(END,'No data to display')
        return
    num_rows = 0
    col_widths = sql_col_width.max_width(result)    #determine the max width for each column
    for colname in result[0].keys():                #get column names
        width   = col_widths[colname]               #get the max width of this column
        colname = colname + " "*width               #concatenate ' ' up to widest necessary                      
        colname = colname[0:width]                  #substring up to widest necessary
        output.insert(END,colname.upper()+' ')      #display column name in uppercase
    output.insert(END,'\n')
    for row in result:                              #iterate thru all rows
        num_rows +=1
        for (colname, value) in row.items():        #iterate thru each row 
            width = col_widths[colname]             #get the max width of this column 
            value = str(value) + " "*width          #concatenate ' ' up to widest necessary
            value = value[0:width]                  #substring up to widest necessary
            output.insert(END,value+' ')            #display column value 
        output.insert(END,'\n')
    output.config(state='disabled')                 #disable the output widget
    messageText.set(F'--- Results {num_rows} Rows ---')
 

def submit():                                       #method called upon submit button
    user   = userEntry.get()                        #get values entered on screen
    pswd   = pswdEntry.get()
    db     = dbEntry.get()
    stmt   = sql.get('1.0',END)
    if not (user and pswd and db and stmt):
        messagebox.showerror("Error","Please enter userid, password, database, and SQL statement") #display a popup dialog            
        return
    server = srvChoice.get()    
    if server=='local':
        result = sql_local_exec.process(db, stmt)               #call sql local module <- list of dictionaries 
    if server=='server':
        result = sql_web_service.call(user, pswd, db, stmt)     #call sql web service <- list of dictionaries
    display_results(result)                                     #call display results (above)     


def reset():                                        #method called upon reset button
    userEntry.set('')                               #clear the entry fields
    pswdEntry.set('')
    dbEntry.set('')
    sql.delete('1.0',END)                           #delete content of sql stmt field
    local.select()                                  #select local server

### Create the Window #######################################################

win = Tk( )                                         #create a window
win.title("Enter your SQL Statement")
win.geometry("800x615")

bgColor = 'lightyellow'

frame1 = Frame(win, bg=bgColor, bd=10, cursor='arrow', 
                    highlightthickness=3, highlightbackground='red') 

userEntry   = StringVar()                           #variables to capture the user input
pswdEntry   = StringVar()
dbEntry     = StringVar()
srvChoice   = StringVar()
messageText = StringVar()

label1 = Label(frame1, text="user/pswd/db", bg=bgColor)          #screen components
user   = Entry(frame1, width=18, textvariable=userEntry)
slash1 = Label(frame1, text="/", bg=bgColor)
pswd   = Entry(frame1, width=18, textvariable=pswdEntry, show='*')
slash2 = Label(frame1, text="/", bg=bgColor)
db     = Entry(frame1, width=18, textvariable=dbEntry)
space1 = Label(frame1, width=30, bg=bgColor)

label2 = Label(frame1, text="sql statement", bg=bgColor)
sql    = Text( frame1, width=76, height=8)

local  = Radiobutton(frame1, text="local", variable=srvChoice, value="local",  bg=bgColor)
server = Radiobutton(frame1, text="server",variable=srvChoice, value="server", bg=bgColor)
local.select()

button1 = Button(frame1, width=9, text="Submit", cursor='hand2', command=submit)
button2 = Button(frame1, width=9, text="Reset",  cursor='hand2', command=reset)

message = Label(frame1, width=45, textvariable=messageText, bg=bgColor)

outFrame   = Frame(frame1)
output     = Text(outFrame, width=74, height=16, wrap="none", state='disabled', bg='#dddddd') 
outScrollH = Scrollbar(outFrame, orient="vertical",   command=output.yview)
outScrollV = Scrollbar(outFrame, orient="horizontal", command=output.xview)
output.config(yscrollcommand=outScrollH.set)
output.config(xscrollcommand=outScrollV.set)
outScrollH.pack(side="right",  fill="y")
outScrollV.pack(side="bottom", fill="x")
output.pack()

label1.grid(  row=0,column=0,padx=5,pady=8,sticky=E)            #place components in grid
user.grid(    row=0,column=1,padx=5,pady=8) 
slash1.grid(  row=0,column=2,padx=1,pady=8) 
pswd.grid(    row=0,column=3,padx=5,pady=8) 
slash2.grid(  row=0,column=4,padx=1,pady=8) 
db.grid(      row=0,column=5,padx=5,pady=8) 
space1.grid(  row=0,column=6,padx=5,pady=8) 
label2.grid(  row=1,column=0,padx=5,pady=8,sticky=NE)
sql.grid(     row=1,column=1,padx=5,pady=8,columnspan=6) 
local.grid(   row=2,column=1,padx=5,pady=8,sticky=W) 
server.grid(  row=2,column=3,padx=5,pady=8,sticky=W) 
button1.grid( row=3,column=0,padx=5,pady=8,sticky=E)
button2.grid( row=3,column=1,padx=5,pady=5,sticky=W)
message.grid( row=3,column=2,padx=5,pady=5,sticky=SW,columnspan=5)
outFrame.grid(row=4,column=1,padx=5,pady=8,columnspan=6)

frame1.pack()                                                   #display the frame
win.mainloop()                                                  #display the window