import cx_Oracle
import re
import os
import csv
import sys
import getopt
import ftplib
import ntpath

#os.environ['NLS_LANG'] = "en_US.ISO8859-1"
#os.environ['NLS_LANG'] = ".UTF8"

def main(argv):
    inputfile = ''
    outputfile = ''
    usage = 'test.py -i <inputfile> -o <outputfile>'
    flist = []
    try:
        opts, args = getopt.getopt(argv,"hi:o:",["ifile=","ofile="])
    except getopt.GetoptError:
        print usage
        sys.exit(2)
    for opt, arg in opts:
        if opt == '-h':
            print 'test.py -i <inputfile> -o <outputfile>'
            sys.exit()
        elif opt in ("-i", "--ifile"):
            inputfile = arg
        elif opt in ("-o", "--ofile"):
            outputfile = arg
        elif len(sys.argv) != 2:
            print usage
            sys.exit(2)
    flist.append(inputfile)
    flist.append(outputfile)
    return flist

class Extractor(object):

    def __init__(self, iput, oput):
        self.oracle_server = "my_oracle_server"
        self.oracle_password = "my_oracle_password"
        self.oracle_port = "1521"
        self.oracle_sid = "MYSID"
        self.oracle_user = "MYUSER"
        self.ftp_server = 'my_ftp_psw'
        self.ftp_user = 'my_ftp_user'
        self.ftp_passwort = 'my_ftp_passwort'
        self.sql_file = os.path.join(os.getcwd(),iput)
        self.csv_file = os.path.join(os.getcwd(),oput)
        self.dsnStr = cx_Oracle.makedsn(self.oracle_server, self.oracle_port, self.oracle_sid)
        self.connection = cx_Oracle.connect(user=self.oracle_user, password=self.oracle_password, dsn=self.dsnStr)
        self.cursor = self.connection.cursor()

    def execute_sql(self, file):
        self.f = open(file)
        self.sql_statement = self.f.read()
        self.cursor.execute(self.sql_statement)

    def extract_from_db(self):
        self.column_list = [i[0] for i in self.cursor.description]
        self.columns = ';'.join(self.column_list)
        with open(self.csv_file,'w') as outfile:
            outfile.write(self.columns+"n")
            writer = csv.writer(outfile, delimiter =";")
            for row in self.cursor:
                writer.writerow(row)

    def transfer_file(self):
        print "Transferring " + self.csv_file + " ..."
        self.session = ftplib.FTP(self.ftp_server, self.ftp_user, self.ftp_passwort )
        self.file = open(self.csv_file,'rb')
        self.session.storlines('STOR ' + ntpath.basename(self.csv_file), self.file)     # send the file
        self.file.close()
        self.session.quit()
        print "Complete."


if __name__ == '__main__':
    plist=main(sys.argv[1:]) # very inept, doesn't not exit as intended when number of arguments does not equal 2
    ext = Extractor(plist[0], plist[1])
    ext.execute_sql(ext.sql_file)
    ext.extract_from_db()
    ext.cursor.close()
    ext.connection.close()
    ext.transfer_file()