#!/usr/bin/python3
#=========================================================================================
# Get data from an Oracle database
# Convert to CSV 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
csv       = convert.obj2csv(list_dict)         #convert list of dictionaries to CSV string
status    = fileIO.write(outfile, csv)         #write the CSV output to a file
print(csv)                                     #print the CSV 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") 

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