import os
import logging.handlers
import csv
from datetime import datetime
from pyutl.oracle import OraConn, cur_as_dict
import os.path as path

HOME_DIR = os.path.dirname(os.path.abspath(__file__))
SQL_DIR = os.path.join(HOME_DIR, 'sql')
LOG_DIR = path.abspath(path.join(__file__, "../logs/"))
REP_DIR = path.abspath(path.join(__file__, "../reportes/"))
DBLOG_FILE = os.path.join(LOG_DIR, 'proceso.log')


def generar():    

    with open(os.path.join(SQL_DIR, f'reporte.sql')) as vv:
        queryvv = vv.read()
        logger.info('Obtenemos Qry base')

    with OraConn('<ConexionDB>', ret="cursor") as con:
        logger.info('Inicio ejecucion de consulta')
        resvv = cur_as_dict(con.execute(queryvv))
        logger.info('Fin ejecucion de consulta')

    return resvv


def exportarCSV(datos, nombreReporte):
        csv_file = f'{nombreReporte}'".csv"
        ruta = REP_DIR + "/" + csv_file
        quote = "'"
        columnas = []
        for key in datos[0]:
            columnas.append(key)
        logger.info('Inicio exportacion de DATA {0}'.format(ruta))            
        try:
            with open(ruta, 'w') as csvfile:
                writer = csv.DictWriter(csvfile,
                                        fieldnames=columnas,
                                        quoting=csv.QUOTE_NONE,
                                        escapechar='\\')
                writer.writeheader()
                writer.writerows(datos)
            logging.info("Reporte generado correctamente, ubicacion: %s", ruta)
        except IOError:
            logger.info('Error exportacion de DATA')    
            print("I/O error")


def run():
    logger.info('Ejecutamos extraccion de data')
    LOG_FILENAME = datetime.now().strftime("%Y%m%d_%H%M%S")
    data = generar()
    exportarCSV(data,'ExportData_'+LOG_FILENAME)


if __name__ == '__main__':
    format = '%(asctime)s %(module)s %(message)s'
    logging.basicConfig(filename=DBLOG_FILE, filemode='w', format=format, level=logging.INFO,
                        datefmt="%H:%M:%S")
    logger = logging.getLogger(__name__)
    logger.info('Iniciamos reportes ')
    run()