#!/usr/bin/env python3
#============================================================================================
# Call oracle2json.py?param (or other Web Services)
# param1 is either sql=SQL_code, or file=file_containing_SQL (web based or command line)
# param2 is also a destination table= to insert the data in
# get the returned data from the Web Service
# insert the returned data into an Oracle database table
# below, identify the database name (actually the db connect string)
#============================================================================================
import sys                                 #to obtain command args
import os.path                             #to check if file exists
import urllib.request                      #urllib.request module allows fetching of urls 
import urllib.parse                        #urllib.parse module allows you to url encode
import cgi                                 #import cgi 
import cgitb                               #import cgi traceback for error handling
cgitb.enable()

sys.path.insert(0,'/home/s/sultans/web/python/demo/etl/util')
import oracle                              #import custom functions for Oracle
import convert                             #import custom functions to convert format
import emailFunc                           #import custom function  to email

db_name = 'demo'                           #identify the db connect string

print("Content-Type: text/html \n")        #required cgi response header(for web only)

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=
table = param.getvalue('table') or 'temp_table'   #if nothing is entered use 'temp_table'        

if len(sys.argv) > 1:                      #get BATCH command line arguments (if any)    
    arg = sys.argv[1]                      #input argument (either sql= or file= ) 
    if arg.find('sql')==0:                 #if arg starts with sql
        (type,sql)  = arg.split('=',1)     #sql arg is provided. split on first '=' 
    if arg.find('file')==0:                #if arg starts with file
        (type,file) = arg.split('=',1)     #file arg is provided. split on first '='  
if len(sys.argv) > 2:                      #get BATCH command line arguments (if any)    
    arg = sys.argv[2]                      #input argument table=
    if arg.find('table')==0:               #if arg starts with table
        (type,table)= arg.split('=',1)     #table arg is provided. split on first '='  

url  = "https://workshop.sps.nyu.edu/~sultans/python/demo/etl/oracle2json.py"    #Choose the JSON web service
#url = "https://workshop.sps.nyu.edu/~sultans/python/demo/etl/oracle2xml.py"    #Choose the XML web service
#url = "https://workshop.sps.nyu.edu/~sultans/python/demo/etl/oracle2csv.py"    #Choose the CSV web service

#-------------------------------------------------------------------------------------
# Function to validate entry fields 
#-------------------------------------------------------------------------------------
def validate():
                                                                                
    if not sql and not file:                   #if no sql or file paramters provided  
        print("Please provide paramater sql=SQL_select or file=file_containing_SQL and table=destination_table")
        sys.exit()
    if sql and file:                                
        print("Please enter either SQL or a file containing SQL, not both")
        sys.exit()
    if file and not os.path.isfile(file):                #if file is entered, check if exists
        print("File does not exist")
        sys.exit()

#-------------------------------------------------------------------------------------
#encode64: encrypt user:pswd to base64
#          this is not needed if your website is not htaccess secured
#-------------------------------------------------------------------------------------
def encode64(input):
    import base64                              #encode base64

    input = input.replace('Basic','')          #remove 'Basic'
    input = input.strip()                      #trim whitespace
    bArray      = input.encode()               #convert the string to byte array
    encoded_arr = base64.b64encode(bArray)     #encode it base64
    encoded_str = encoded_arr.decode()         #convert the byte array back to string
    encoded_str = 'Basic ' + encoded_str       #re-add 'Basic'
    return(encoded_str)

authValue    = 'Basic student:123'
encryptValue = encode64(authValue)             # Basic c3R1ZGVudDoxMjM=

#-------------------------------------------------------------------------------------
# main code
#-------------------------------------------------------------------------------------

validate()                                    #validate the input

if sql: 
    sql2 = urllib.parse.quote(sql)            #URL encode the SQL
    url += '?sql='  + sql2                    #the full url with ?sql=SQL  
if file: 
    url += '?file=' + file                    #the full url with ?file=filename 

try:
    req  = urllib.request.Request(url)              #create a request object
    req.add_header('Authorization', encryptValue)   #add 'Authorization: value' header (optional) 
    resp = urllib.request.urlopen(req)              #send the request
    data = resp.read()                              #read the returned data
    data = data.decode('utf-8')                     #convert it from array of bytes to string 
except:
    print(F"<p style='color:red'>Error calling web service</p>{url}")
    sys.exit() 

try:
    if 'json' in url:
        list_dict = convert.json2obj(data)      #convert JSON to Python list of dictionaries
    if 'xml' in url:
        list_dict = convert.xml2obj(data)       #convert XML to Python list of dictionaries
    if 'csv' in url: 
        list_dict = convert.csv2obj(data)       #convert CSV to Python list of dictionaries
except:
    print(F"<p style='color:red'>Error converting your returned data</p>{data}")
    sys.exit() 

try:
    oracle.dropTable(db_name, table)                                #drop table
    oracle.createTable(db_name, table, list_dict)                   #create table
    insert_count = oracle.insert(db_name, table, list_dict)         #insert into the database table
    print("<h1>Python program calling a Python Web Service</h1>")
    print(F"<h3>{insert_count} rows inserted in table {table}</h3>")
except:
    print(F"<p style='color:red'>Error inserting your data in table {table}</p>") 


##### Below is optional display of returned data #####

print("DATA RETURNED as a list of dictionaries...")
print("<pre>")
for dict in list_dict:
    for key in dict:
        value = dict[key]
        value = str(value) 
        print ('    {:12s} {:s} {:s}'.format(key, '==>', value ))
    print()
print('</pre>')

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