#!/opt/app/cacheDB/python/bin/python3
"""
A script for getting data objects from Oracle
References: http://www.juliandyke.com/Research/Development/UsingPythonWithOracle.php
"""
import logging
import sys
#sys.path.append('/opt/app/deap/oracle/instantclient_11_2/')
import cx_Oracle

# Set the logging level to DEBUG
logging.basicConfig(level=logging.INFO)

conn_info_stg = {'host_ip': '135.164.37.221',
             'port': 1521,
             'user': 'ecstg',
             'password': 'ecstg_vert',
             'database': 'ebrVdev'
             }

conn_info = {'host_ip': '135.164.53.134',
             'port': 1521,
             'user': 'ecstg',
             'password': 'ecstgedmo',
             'database': 'edmoprd'
             }


class OracleGetter():
    """ A class to get data from Vertica"""
    TEST_QUERY='SELECT * FROM NRT_ADOBE_LOG_CONTROL where FILE_ID = 171624083411'

    def __init__(self):
        """ The default constructor which creates the dsn"""
        self.dsn = cx_Oracle.makedsn(conn_info['host_ip'],conn_info['port'],conn_info['database'])

    def __str__(self):
        return " An Oracle getter for db {} on host {}".format(conn_info['database'],conn_info['host_ip'])

    def get_dbname(self):
        """ A method which returns the nameo the database"""
        return conn_info['database']

    def test_connection(self):
        """ A test method just to check connection to Vetica"""
        with cx_Oracle.connect(conn_info['user'],conn_info['password'],self.dsn) as connection:
            logging.info("Connected to {} on host{} ".format(conn_info['database'],conn_info['host_ip']))
            cur = connection.cursor()
            cur.execute(self.TEST_QUERY)
            for row in cur:
                print("The row is {}".format(row))

    def get_single_record_by_fileID(self,params=['171624083411']):
        """ A method which returns a process statistics for a single file for a given day"""
        result =[]
        query_params = {'FILE_ID':params[0]}
        query = """select FILE_ID,FILENAME,SERVER_NAME,PARSE_INPUT,VT_LOG_SUCCESS_ROWS,VT_LOG_PARTS_SUCCESS_ROWS
        from NRT_ADOBE_LOG_CONTROL
        where FILE_ID = :FILE_ID""".replace('\n',' ')
        with cx_Oracle.connect(conn_info['user'],conn_info['password'],self.dsn) as connection:
            logging.debug("Connected to {} on host{} ".format(conn_info['database'],conn_info['host_ip']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur:
                result.append(row)
        return(result)


    def get_count_files_by_date(self,params=['20170606','20170611']):
        """ A method which returns count of dev and prod files loaded per day"""
        result =[]
        query_params = {'START_DT':params[0],'END_DT':params[1]}
        query = """select DAY_ID,PRODUCTION_FILE_FLAG,COUNT(FILE_ID)
        from NRT_ADOBE_LOG_CONTROL
        where DAY_ID BETWEEN :START_DT and :END_DT
        group by DAY_ID,PRODUCTION_FILE_FLAG""".replace('\n',' ')
        with cx_Oracle.connect(conn_info['user'],conn_info['password'],self.dsn) as connection:
            logging.debug("Connected to {} on host{} ".format(conn_info['database'],conn_info['host_ip']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur:
                result.append(row)
        return(result)

    def get_records_by_date(self,params=['2017611']):
        """ A method which returns a process statistics for a single file for a given day"""
        result =[]
        query_params = {'DAY_ID':params[0]}
        query = """select FILE_ID,FILENAME,SERVER_NAME,PARSE_INPUT,VT_LOG_SUCCESS_ROWS,VT_LOG_PARTS_SUCCESS_ROWS
        from NRT_ADOBE_LOG_CONTROL
        where DAY_ID = :DAY_ID""".replace('\n',' ')
        with cx_Oracle.connect(conn_info['user'],conn_info['password'],self.dsn) as connection:
            logging.debug("Connected to {} on host{} ".format(conn_info['database'],conn_info['host_ip']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur:
                result.append(row)
        return(result)

    def get_records_timings_by_date(self,params=['2017822']):
        """ A method which returns timings on parsing and loading times per file between a date range"""
        result =[]
        query_params = {'DAY_ID':params[0]}
        query = """select DAY_ID,
        FILENAME,
        FILE_ID,
        CEIL((FILE_PARSED_DATE - PARSER_START_DATE )*24*60) as TIME_TO_PARSE_MINUTES,
        CEIL((VT_LOAD_DATE     - LOAD_START_DATE )*24*60)   as TIME_TO_LOAD_MINUTES ,
        VT_LOG_SUCCESS_ROWS                           as LOADED_LOG_ROWS ,
        VT_LOG_PARTS_SUCCESS_ROWS                     as LOADED_LOG_PARTS_ROWS
        from NRT_ADOBE_LOG_CONTROL
        where DAY_ID= :DAY_ID
        order by 5 desc
        """.replace('\n',' ')
        with cx_Oracle.connect(conn_info['user'],conn_info['password'],self.dsn) as connection:
            logging.info("Connected to {} on host{} ".format(conn_info['database'],conn_info['host_ip']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur:
                result.append(row)
        return(result)

    def getTenMinStats(self,params=['20180210']):
        """ A method which returns 5 min statistics starting from current time"""
        result =[]
        query_params = {'DAY_ID':params[0]}
        query = """
        select to_date((to_char(sysdate, 'mm/dd/yyyy')||' '||nvl(parser.time_interval,loader.time_interval)),'mm/dd/yyyy hh24:mi')+interval '10' minute as record_time,
        round(((to_date((to_char(sysdate, 'mm/dd/yyyy')||' '||nvl(parser.time_interval,loader.time_interval)),'mm/dd/yyyy hh24:mi'))+interval '10' minute - to_date('19700101 000000', 'YYYYMMDD HH24MISS'))*86400000) as udt  ,
        parser.time_interval , total_loaded_logs ,total_loaded_log_parts,
        total_parsed_logs,total_parsed_log_parts from 
        (select  substr(to_char(vt_load_Date, 'hh24:mi'), 1, 4) || '0' as time_interval,
        nvl(sum(vt_log_success_rows),0) as total_loaded_logs,
        nvl(sum(vt_log_parts_success_rows),0) as total_loaded_log_parts
        from nrt_Adobe_log_control
        where day_id =to_char(sysdate,'yyyymmdd')
        and vt_load_Date >= (sysdate-4/24)
        and process_name='Load'
        and process_status ='Complete'
        group by  substr(to_char(vt_load_Date, 'hh24:mi'), 1, 4) || '0'
        ) loader full outer join 
        (select  substr(to_char(file_parsed_Date, 'hh24:mi'), 1, 4) || '0' as time_interval,
        nvl(sum(parse_output_logs),0) as total_parsed_logs,
        nvl(sum(parse_output_nvp),0) as total_parsed_log_parts
        from nrt_Adobe_log_control
        where day_id =to_char(sysdate,'yyyymmdd')
        and file_parsed_Date >= (sysdate-4/24)
        and ( (process_name='Load'
        and process_status  in ('Complete','Start'))  OR 
        (process_name='Parser'
        and process_status  ='Complete'))
        group by  substr(to_char(file_parsed_Date, 'hh24:mi'), 1, 4) || '0') parser 
        on loader.time_interval = parser.time_interval
        where to_date((to_char(sysdate, 'mm/dd/yyyy')||' '||nvl(parser.time_interval,loader.time_interval)),'mm/dd/yyyy hh24:mi')+interval '10' minute <=sysdate
        order by record_time asc
        """.replace('\n',' ')
        with cx_Oracle.connect(conn_info['user'],conn_info['password'],self.dsn) as connection:
            logging.info("Connected to {} on host{} ".format(conn_info['database'],conn_info['host_ip']))
            cur = connection.cursor()
            #cur.execute(query,query_params)
            cur.execute(query)
            for row in cur:
                result.append(row)
        return(result)


    def getOneMinStats(self,params=['20180210']):
        """ A method which returns 5 min statistics starting from current time"""
        result =[]
        query_params = {'DAY_ID':params[0]}
        query = """
        select to_date((to_char(sysdate, 'mm/dd/yyyy')||' '||nvl(parser.time_interval,loader.time_interval)),'mm/dd/yyyy hh24:mi')+interval '10' minute as record_time,
        round(((to_date((to_char(sysdate, 'mm/dd/yyyy')||' '||nvl(parser.time_interval,loader.time_interval)),'mm/dd/yyyy hh24:mi'))+interval '10' minute - to_date('19700101 000000', 'YYYYMMDD HH24MISS'))*86400) as udt  ,
        parser.time_interval , total_loaded_logs ,total_loaded_log_parts,
        total_parsed_logs,total_parsed_log_parts from 
        (select  substr(to_char(vt_load_Date, 'hh24:mi'), 1, 4) || '0' as time_interval,
        nvl(sum(vt_log_success_rows),0) as total_loaded_logs,
        nvl(sum(vt_log_parts_success_rows),0) as total_loaded_log_parts
        from nrt_Adobe_log_control
        where day_id =to_char(sysdate,'yyyymmdd')
        and vt_load_Date >= (sysdate-1/144)
        and process_name='Load'
        and process_status ='Complete'
        group by  substr(to_char(vt_load_Date, 'hh24:mi'), 1, 4) || '0'
        ) loader full outer join 
        (select substr(to_char(file_parsed_Date, 'hh24:mi'), 1, 4) || '0' as time_interval,
        nvl(sum(parse_output_logs),0) as total_parsed_logs,
        nvl(sum(parse_output_nvp),0) as total_parsed_log_parts
        from nrt_Adobe_log_control
        where day_id =to_char(sysdate,'yyyymmdd')
        and file_parsed_Date >= (sysdate-1/144)
        and ( (process_name='Load'
        and process_status  in ('Complete','Start'))  OR 
        (process_name='Parser'
        and process_status  ='Complete'))
        group by  substr(to_char(file_parsed_Date, 'hh24:mi'), 1, 4) || '0') parser 
        on loader.time_interval = parser.time_interval
        where to_date((to_char(sysdate, 'mm/dd/yyyy')||' '||nvl(parser.time_interval,loader.time_interval)),'mm/dd/yyyy hh24:mi')+interval '10' minute <=sysdate
        order by record_time asc
        """.replace('\n',' ')
        with cx_Oracle.connect(conn_info['user'],conn_info['password'],self.dsn) as connection:
            logging.info("Connected to {} on host {} ".format(conn_info['database'],conn_info['host_ip']))
            logging.debug("One min stats , SQL -->  {} ".format(query))
            cur = connection.cursor()
            cur.execute(query)
            for row in cur:
                result.append(row)
        return(result)


if __name__ == '__main__':
    o = OracleGetter()
    res = o.getOneMinStats()
    #res = o.get_records_timings_by_date()
    for item in res:
        print(item)

