#!/usr/bin/python
#=====================================================================================
# Get data from a database
# Display to an html form
# Allow filtering through the data
#=====================================================================================
import pymysql as mydb                      #Mysql 3x database driver
import re                                   #regular expressions
import HTTPparam
 
data = []                                   #2 dim array

print("Content-Type: text/html \n")         #required http response header (w/ extra line)

'''===================================================================================
read_data: read data from a database
==================================================================================='''
def read_data():
 
    global ice_vanil,ice_choco,ice_straw,ice_pecan,ice_rocky,ice_frnch,ice_pstch
    global top_fudge,top_sprkl,top_nuts,top_cream

    ice_vanil = HTTPparam.getValue('ice_vanil') or ""        #if nothing entered, replace with ""   
    ice_choco = HTTPparam.getValue('ice_choco') or ""   
    ice_straw = HTTPparam.getValue('ice_straw') or ""   
    ice_pecan = HTTPparam.getValue('ice_pecan') or ""   
    ice_rocky = HTTPparam.getValue('ice_rocky') or ""   
    ice_frnch = HTTPparam.getValue('ice_frnch') or ""   
    ice_pstch = HTTPparam.getValue('ice_pstch') or ""   

    top_fudge = HTTPparam.getValue('top_fudge') or ""   
    top_sprkl = HTTPparam.getValue('top_sprkl') or ""   
    top_nuts  = HTTPparam.getValue('top_nuts')  or ""   
    top_cream = HTTPparam.getValue('top_cream') or ""   

    host   = 'localhost'               #the local host
    port   =  3306                     #MySql port number
    userid = 'demo2'                   #the userid
    pswd   = 'demo2'                   #the password    
    db     = 'demo2'                   #the name of the mysql database
 
    try:
        conn = mydb.connect(host=host,user=userid,password=pswd,database=db)     #connect to host
     
        cursor = conn.cursor()                                                   #create a cursor
     
        filter = "" 
        if ice_vanil : filter += "flavor  like '%" + ice_vanil + "%' AND "       #add filters as requested                         
        if ice_choco : filter += "flavor  like '%" + ice_choco + "%' AND "                         
        if ice_straw : filter += "flavor  like '%" + ice_straw + "%' AND "                         
        if ice_pecan : filter += "flavor  like '%" + ice_pecan + "%' AND "                         
        if ice_rocky : filter += "flavor  like '%" + ice_rocky + "%' AND "                         
        if ice_frnch : filter += "flavor  like '%" + ice_frnch + "%' AND "                         
        if ice_pstch : filter += "flavor  like '%" + ice_pstch + "%' AND "                         
        if top_fudge : filter += "topping like '%" + top_fudge + "%' AND "                         
        if top_sprkl : filter += "topping like '%" + top_sprkl + "%' AND "                         
        if top_nuts  : filter += "topping like '%" + top_nuts  + "%' AND "                         
        if top_cream : filter += "topping like '%" + top_cream + "%' AND "
        filter += "1=1"                                                         #catch all in case no filter                         

        sql  = "SELECT firstname,lastname,address,flavor,topping,creditCard,order_id " \
             + "FROM cust_order "     \
             + "WHERE 1=1 "           \
             + "AND ("+ filter + ") " \
             + "ORDER BY order_id"
        
#       print(sql)                                            #debugging
         
        result = cursor.execute(sql);                         #execute the query
     
    except mydb.Error as e:
        errorNum = e.args[0]
        errorMsg = e.args[1]
        error = 'Database Error - ' + str(errorNum) + errorMsg
        return      

    i=0
    row = cursor.fetchone()                         #get first row 
    while row is not None:
        data.append(row)
        i+=1
        row = cursor.fetchone()                     #get next row
     
    cursor.close()                                  #close the cursor/buffer
    conn.close()                                    #close the connection

'''===================================================================================
display: display data on the html page
==================================================================================='''
def display():

    global ice_vanil,ice_choco,ice_straw,ice_pecan,ice_rocky,ice_frnch,ice_pstch
    global top_fudge,top_sprkl,top_nuts,top_cream
    
    print("""
        <html>
        <head>
        <title>Retrieve data from database</title>    
        <style>
            a          {text-decoration:none; color:brown}
            #table2    {font:10pt arial; border:solid 2px black; border-collapse:collapse; margin-left:70px; background-color:#eeeeee; } 
            #table2 th {border:solid 1px black; padding-left:5px; padding-right:5px; vertical-align:top;}
            #table2 td {border:solid 1px black; padding-left:5px; padding-right:5px; vertical-align:top;}
        </style>
        <body bgcolor=lightyellow>
        <h1><center>The Ice Cream Shop</center></h1>
        <form action=getFromDBfilter.cgi method=GET>
        <table><tr><td><b>Flavor(s)......
    """)
    print("<td><input type=checkbox name=ice_vanil value='vanilla'", end='') 
    if ice_vanil :  print('checked',end='') 
    print('>Vanilla')
    print("<td><input type=checkbox name=ice_choco value='chocolate'", end='')
    if ice_choco:  print('checked',end='') 
    print('>Chocolate')
    print("<td><input type=checkbox name=ice_straw value='strawberry'", end='')
    if ice_straw:  print('checked',end='') 
    print('>Strawberry')
    print('<tr><td>')
    print("<td><input type=checkbox name=ice_pecan value='butter-pecan'", end='')
    if ice_pecan:  print('checked',end='') 
    print('>Butter Pecan')
    print("<td><input type=checkbox name=ice_rocky value='rocky-road'", end='')
    if ice_rocky:  print('checked',end='') 
    print('>Rocky Road')
    print("<td><input type=checkbox name=ice_frnch value='french-vanilla'", end='')
    if ice_frnch:  print('checked',end='') 
    print('>French Vanilla')
    print("<td><input type=checkbox name=ice_pstch value='pistachio'", end='')
    if ice_pstch:  print('checked',end='') 
    print('>Pistachio')
    print('<tr><td><b>Topping(s)...')
    print("<td><input type=checkbox name=top_fudge value='hotFudge'", end='')
    if top_fudge:  print('checked',end='') 
    print('>Hot Fudge')
    print("<td><input type=checkbox name=top_sprkl value='sprinkles'", end='')
    if top_sprkl:  print('checked',end='') 
    print('>Sprinkles')
    print("<td><input type=checkbox name=top_nuts  value='nuts'", end='')
    if top_nuts:   print('checked',end='') 
    print('>Nuts')
    print("<td><input type=checkbox name=top_cream value='whippedCream'", end='')
    if top_cream:  print('checked',end='') 
    print('>Whipped Cream')
    print('</table>')
    print("""
        <br>
        <input type=submit value='   Filter   '> 
        </form>
        <table id=table2>
        <tr bgcolor=tan>
    """) 
                                   
    print("<th>Order#<th>Name<th>Address<th>Flavors<th>Toppings<th>Credit Card ")

    for row in data:                                    #loop thru the data rows
        firstname  = row[0]                             #get each column 
        lastname   = row[1]
        address    = row[2]
        flavor     = row[3]
        topping    = row[4]
        creditCard = row[5]
        order_id   = row[6]

        name = firstname +' '+ lastname
        addr = re.sub(r"\n", "<br>", address)           #replace \n with <br>
        flav = re.sub(r",",  "<br>", flavor)            #replace ,  with <br>
        top  = re.sub(r",",  "<br>", topping)           #replace ,  with <br>
        
        print("<tr>", end='')
        print("<td>"+ str(order_id) +"<td>"+ name +"<td>"+ addr +"<td>"+ flav +"<td>"+ top +"<td>"+ creditCard ,end='')
        print("</tr>")

    print("</table>")
    print("</body>")
    print("</html>")

'''===================================================================================
main code
==================================================================================='''
read_data();
display();



#=== link to see the python code ================================================
import os, sys
sys.path.insert(0,'/home/staff/sultan/public_html/cgi-bin/python')
import zCode                          #import func to display the Python code
filename = os.path.abspath(__file__)  #get absolute file name 
zCode.display(filename)               #call it
#================================================================================