# from distutils.util import strtobool
from optparse import OptionParser
import cx_Oracle

def lsp2ora_init():

    exitCode = 0
    
    parser = OptionParser()
    parser.add_option("-s","--statement"
                        , dest = "statement"
                        , help = "SQL Statement"
                        , default = "")
    parser.add_option("-c","--connectionhost"
                        , dest = "connectionhost"
                        , help = "DB Connection Host"
                        , default = "")
    parser.add_option("-p","--port"
                        , dest = "port"
                        , help = "DB Connection Port"
                        , default = "")
    parser.add_option("-i","--SSID"
                        , dest = "ssid"
                        , help = "DB SSID"
                        , default = "")           
    parser.add_option("-u","--user"
                        , dest = "user"
                        , help = "Database username"
                        , default = "")
    parser.add_option("-w","--password"
                        , dest = "password"
                        , help = "Database password"
                        , default = "")
    parser.add_option("-r","--result-file"
                        , dest = "result_file"
                        , help = "Result file containing the fetched database informations."
                        , default = "")

    (options, args) = parser.parse_args()
    
    # Check parameter: Result File
    result_file = None
    if options.result_file == "":
        print("Result file is not specified.")
        exitCode = 1
    else:
        result_file = options.result_file

    # Check parameter: SQL Statement
    statement = None
    if options.statement == "":
        print("Missing SQL statement")
        if result_file is not None:
            with open(result_file, 'a') as file:
                file.write('###@ERROR Missing SQL statement')
        exitCode = 2
    else:
        statement = options.statement
        
    # Check parameter: Database host
    host = None
    if options.connectionhost == "":
        print("Database host not specified")
        if result_file is not None:
            with open(result_file, 'a') as file:
                file.write('###@ERROR Database host not specified')
        exitCode = 3
    else:
        host = options.connectionhost
        
    # Check parameter: Database port
    port = None
    if options.port == "":
        print("Database port not specified")
        if result_file is not None:
            with open(result_file, 'a') as file:
                file.write('###@ERROR Database port not specified')
        exitCode = 4
    else:
        port = options.port
        
    # Check parameter: Database SSID
    ssid = None
    if options.ssid == "":
        print("Database SSID not specified")
        if result_file is not None:
            with open(result_file, 'a') as file:
                file.write('###@ERROR Database SSID not specified')
        exitCode = 5
    else:
        ssid = options.ssid

    # Check parameter: Database user
    user = None
    if options.user == "":
        print("Database user not specified")
        if result_file is not None:
            with open(result_file, 'a') as file:
                file.write('###@ERROR Database user not specified')
        exitCode = 6
    else:
        user = options.user
        
    # Check parameter: Database password
    password = None
    if options.password == "":
        print("Database password not specified")
        if result_file is not None:
            with open(result_file, 'a') as file:
                file.write('###@ERROR Database password not specified')
        exitCode = 7
    else:
        password = options.password

    # Check argument parameter ELID
    if exitCode > 0:
        print("exit")
        sys.exit(exitCode)
    else:
        print("all parameters valid")
        execSqlStatement(statement, host, port, ssid, user, password, result_file)

        
def execSqlStatement(statement, host, port, ssid, user, password, result_file):

    connection = None
      
    try:
        dsn_tns = cx_Oracle.makedsn(host, port, ssid)
        connection = cx_Oracle.connect(user, password, dsn_tns)
    except (Exception) as error:
        with open(result_file, 'a') as file:
            file.write('###@ERROR Failed to connect to database ' + str(error))
            
    retval = None
    cursor = None

    try:
        cursor = connection.cursor()
        cursor.execute(statement)
        retval = cursor.fetchall()
        cursor.close()
    except (Exception) as error:
        print(str(error))
        with open(result_file, 'a') as file:
            file.write('###@ERROR Failed to execute sql statement ' + str(error))
        retval = None
        if cursor is not None:
            cursor.close()
    with open(result_file, 'a') as file:  
        for l in retval:
            print(l)
            file.write(str(l) + "\n")

    connection.close()
        

if __name__ == "__main__":
    lsp2ora_init()