#!/usr/bin/python3
#=====================================================================================
# Get data from an Oracle database
# Convert to JSON output
# Write to output file
# Deliver the output thru the web (as a web service)
# Web Param is either sql=SQL_select_code, or file=file_containing_SQL, or 
# Batch argument is either SQL_select_code, or file_containing_SQL
# *Below, update the name of the output file (if need be) 
#===================================================================================== 
import sys                                 #to obtain command args
import os.path                             #to check existance of input file
import urllib.parse                        #for url decoding 
import cgi                                 #cgi for web 

sys.path.insert(0,'/home/s/sultans/web/python/demo/etl/util')
import oracle                              #import custom functions for Oracle
import fileIO                              #import custom functions for file I/O
import convert                             #import custom functions to convert format
import emailFunc                           #import custom function  to email
  
print("Content-Type: text/plain \n")       #required http response header (w/ extra line) for web

sql   = ''
file  = ''
param = cgi.FieldStorage()                 #get ONLINE web parameters (if any)
sql   = param.getvalue('sql')              #get value for web param sql=
file  = param.getvalue('file')             #get value for web param file=

#file = '/home/s/sultans/web/python/demo/data/input2.sql'    #commented out 

if len(sys.argv) > 1:                      #get BATCH command line arguments (if any)    
    arg = sys.argv[1]                      #your argument either sql_select or filename
    if arg.find('select') >= 0 \
    or arg.find('SELECT') >= 0:            #if arg contains select or SELECT
        sql  = arg                         #select statement is entered 
    else:                                  #else
        file = arg                         #filename is entered 
                
if file:                                   #if file= is requested
    sql = fileIO.read(file)                #read the content of the file (content should be SQL)

if not sql:                                #if no parameters provided at all 
    print("Please provide parameter sql select or file containing sql select")
    print("Or you can also pass after URL?sql=select or URL?file=filename")
    sys.exit()

sql = urllib.parse.unquote(sql)            #URL decode

db_name = 'demo'
outfile = '/home/s/sultans/web/python/demo/data/output.txt'

#===============================================================================================
# Main code
#===============================================================================================
list_dict = oracle.query(db_name,sql)          #excute Oracle query
json      = convert.obj2json(list_dict)        #convert list of dictionaries to JSON string
status    = fileIO.write(outfile, json)        #write the JSON output to a file
print(json)                                    #print the JSON string (send back to client)

if status == False:
    print('Error in processing')
    emailFunc.send("ss4922@nyu.edu",                         #send email from 
                   "sam.sultan@hbo.com",                     #to   
                   "message subject",
                   "this is the body of the message") 

#===============================================================================================