#!/usr/bin/env python3
#=====================================================================================
# Get student classes from an Oracle database
# studentId is passed via HTTP parameters  (e.g. url?studentId=99)
# format is also passed as parameter (e.g. url?studentId=99&format=json)
# Data returned as either JSON or XML, format:  
# { 
#     "header": { 
#         "studentId": "1", 
#         "date_time": "2017-10-15 22:18:32.671366" 
#     }, 
#     "data": [ 
#         { 
#             "FNAME": "Barbara", 
#             "LNAME": "Burns", 
#             "SSN": "000-01-0001", 
#             "SEX": "F", 
#             "COURSE_ID": "X52-9740", 
#             "DESCRIPTION": "Web Page Development with HTML", 
#             "PRICE": 1095 
#         }
#     ] 
# }
#===================================================================================== 
#<?xml version='1.0' ?> 
#<root> 
#    <row> 
#        <STUDENT_ID>1</STUDENT_ID> 
#        <FNAME>Barbara</FNAME> 
#        <LNAME>Burns</LNAME> 
#        <SSN>000-01-0001</SSN> 
#        <SEX>F</SEX> 
#        <COURSE_ID>X52-9740</COURSE_ID> 
#        <DESCRIPTION>Web Page Development with HTML</DESCRIPTION> 
#        <PRICE>1095</PRICE> 
#    </row> 
#</root>  
#===================================================================================== 
from datetime import datetime
import cgi                                  #cgi 
import cgitb                                #cgi with traceback error handling
import cx_Oracle as oradb                   #import Oracle DB driver
import os                                   #to add Oracle home to the path                                 
import json                                 #to convert dict/array to json string
cgitb.enable()

os.environ['ORACLE_HOME']='/u01/app/oracle/product/12.2.0/dbhome_1'     #add Oracle driver to path
 
print("Content-Type: text/plain \n")               #required http response header (w/ extra line)

elements = cgi.FieldStorage()                      #obtain the http parameters
stud_id  = elements.getvalue('studentId')          #get value for studentId from web   
format   = elements.getvalue('format') or 'JSON'   #get value for format (if none, default JSON)   

host = 'localhost'                        #the local host
port = '1521'                             #Oracle port number
db   = 'orcl'                             #the name of the oracle database
user = 'demo'                             #the userid
pswd = 'demo'                             #the password

connect_string = user+'/'+pswd+'@'+host+':'+port+'/'+db;     #connection string (user/pswd@host:port/db)    

try:
    conn   = oradb.connect(connect_string)      #open connection            
 
    cursor = conn.cursor()                      #create a cursor
 
    WHERE_CONDITION = " "                       #create an empty SQL WHERE condition
    if (stud_id):
        WHERE_CONDITION = " WHERE student_id = '"+ stud_id + "'"

    sql  = "SELECT student_id,fname,lname,ssn,sex,course_id,description,price " \
         + "  FROM student left join class on ssn=stu_ssn "                     \
         + "             left join course using(course_id)"                     \
         +   WHERE_CONDITION       

    result = cursor.execute(sql);               #execute the query

except oradb.DatabaseError as e:
    print("Could not establish connection", '<br>') 
    print( e, '<br>')
    exit()
 
colnames = []                           
for colInfo in cursor.description :         #loop thru cursor description property
    colname = colInfo[0]                    #column name is first element
    colnames.append(colname)                #append to array

results = cursor.fetchall()                 #get all the rows at once
cursor.close()                              #close the cursor/buffer
conn.close()                                #close the connection

response = {}                               #the overall response 
header   = {}                               #the header component
data     = []                               #the data component

now = datetime.now()                        #get current timestamp

if not (stud_id): stud_id = 'ALL'
    

#===================================================================================== 
# JSON output
#===================================================================================== 
if format.upper() == 'JSON':
    header['studentId'] = str(stud_id)
    header['date_time'] = str(now.date()) +' '+ str(now.time()) 

    for row in results:                          #loop thru the list of rows
        dict = {}                                #create a dictionary
        i=0
        for colvalue in row:                     #loop thru all columns
            colname       = colnames[i]          #get the column name
            if colvalue  == None:  colvalue = '' #if no value, Python puts None, change to ''
            dict[colname] = colvalue             #add to dictionary
            i += 1
        data.append(dict)                        #add dictionary to data array

    response['header'] = header                  #add heasers to the response
    response['data']   = data                    #add students info to the response

    json = json.dumps(response,indent=4)         #convert Python dict/array to json string
    print(json)                                  #send the json back

#===================================================================================== 
# XML output
#===================================================================================== 
if format.upper() == 'XML':    
    xml  = ""
    xml  = "<?xml version='1.0' ?>\n"
    xml += "<root> \n"

    for row in results:                                     #loop thru the data rows
        i=0
        xml += "    <row> \n"
        for colvalue in row:                                #loop thru each colName/colValue
            colname       = colnames[i]                     #get the column name
            xml +=  "        "                              #spacer
            xml += F"<{colname}>{colvalue}</{colname}> \n"  #write <colName>colValue</colName>
            i += 1
        xml += "    </row> \n"

    xml += "</root> \n"                
    print(xml)                                    #return the XML string