METCO_DESCARGA_DF/sim/memsim.py

import os
import glob
import sqlite3
import time
import logging
from datetime import datetime

from selenium import webdriver
from selenium.webdriver.chrome.options import Options

from sim.config import Configuration
from sim.utils.unzipfile import unzipfile_and_remove
from sim.utils.common import Validation
from sim.utils.exceptions import CertificadosIncorrectos
from sim.utils.mailsender import Emailer
import sim.utils.logconfig
from dotenv import load_dotenv
import pyodbc
from sim.filemanager import move_file_to_server

def download_files():
    load_dotenv()
    rootLogger = logging.getLogger(__name__)
    rootLogger.info("Iniciando proceso de descarga de Facturas")
    start = time.time()

    config = Configuration()
    val = Validation()
    mail = Emailer(config.SMTP_SERVER,config.SMTP_PORT,config.AUTH_USER,config.AUTH_PASS)

    downloadFilePath = config.DOWNLOAD_PATH
    chromedriver = config.DRIVER_PATH
    exts = [".pdf", ".xml"]

    url = "https://memsim.cenace.gob.mx/produccion/participantes/LOGIN/"

    try:

        options = Options()
        options.add_argument("start-maximized")
        prefs = {"download.default_directory": downloadFilePath,
                 "download.prompt_for_download": False,
                 "download.directory_upgrade": True,
                 "safebrowsing.enabled":True,
                 "plugins.always_open_pdf_externally": True}
        options.add_experimental_option("prefs", prefs)

        driver = webdriver.Chrome(executable_path=chromedriver, chrome_options=options)
        #driver.implicitly_wait(10)

        rootLogger.info("Ingresando a la url: {0}".format(url))

        driver.get(url)
        rootLogger.info("Iniciando proceso de certificación y login...")
        val.validate_element_by_id(driver,"uploadCerfile0")
        driver.find_element_by_id("uploadCerfile0").send_keys(
            os.path.join(config.CERT_PATH,"carlosmauriciojuarez-mexicanenergytrading.cer"))

        
        val.validate_element_by_id(driver,"uploadKeyfile0")
        driver.find_element_by_id("uploadKeyfile0").send_keys(
            os.path.join(config.CERT_PATH,"carlosmauriciojuarez-mexicanenergytrading_user.key"))

        val.validate_element_by_id(driver,"txtPrivateKey")
        driver.find_element_by_id("txtPrivateKey").send_keys(config.PRIVATE_KEY)
        time.sleep(1)
        driver.find_element_by_id("btnEnviar").click()

        if not val.check_element_by_id(driver, "txtUsuario"):
            raise CertificadosIncorrectos(driver.find_element_by_id("lblCertificadoIncorrecto").text)
        
        driver.find_element_by_id("txtUsuario").send_keys(config.USER_NAME)
        driver.find_element_by_id("txtPassword").send_keys(config.PASSWORD)
        driver.find_element_by_id("Button1").click()
        rootLogger.info("Inicio de sesión satisfactorio...")

        rootLogger.info("Ingreso a la sección de Estados de Cuenta y Facturas...")

        driver.find_element_by_css_selector(
            "#ctl07 > ul:nth-child(1) > li:nth-child(4) > a:nth-child(1) > span:nth-child(1)").click()
        
        driver.find_element_by_css_selector("#FACT_Consultar > span:nth-child(1)").click()
        
        
        val.validate_element_by_tag_name(driver, "iframe")
        driver.switch_to.frame(driver.find_element_by_tag_name("iframe"))

        driver.find_element_by_css_selector("li.rtLI:nth-child(2) > ul:nth-child(2) > li:nth-child(1) > div:nth-child(1) > span:nth-child(2)").click()

        val.validate_element_not_present(driver, "RadAjaxLoadingPanel1RadAjaxPanel1")
        val.validate_element_by_id(driver, "Div7")

        ecd_div = driver.find_element_by_id("Div7")
        trs = ecd_div.find_elements_by_tag_name("tr")

        regs = []
        
        rootLogger.info("Leyendo listado de DF en la página web...")

        for tr in trs:
            tr_id = tr.get_property("id")
            if tr_id != "":
                tds = tr.find_elements_by_tag_name("td")
                df_name = tds[0].get_attribute("innerHTML")
                xml_id = tds[1].find_element_by_tag_name("input").get_property("id")
                pdf_id = tds[2].find_element_by_tag_name("input").get_property("id")
                update = tds[3].get_attribute("innerHTML")

                rootLogger.debug(" DF: {0} | actualizacion: {1}".format(df_name, update))
                ecd_info = [df_name, xml_id, pdf_id, update]

                regs.append(ecd_info)

        num_regs = len(regs)

        rootLogger.info("Se encontraton {0} registros".format(num_regs))
        i = 1
        d = 0
        e = 0
        scroll=1
        downloaded_dfs = []

        for reg in regs:
            df_name = reg[0]
            rootLogger.info("{0} de {1} -- Descargando información del Estado de Cuenta: {2} ...".format(i, num_regs, df_name))
            conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=192.168.98.207;'
                      'Database=PRD_ORION;'
                      'UID='+config.DBUSER+';'
                      'PWD='+config.DBPASS+';')

            cursor = conn.cursor()
            rootLogger.info("Buscando en la base de datos...")
            cursor.execute("SELECT * FROM MEXICO.DF_DOWNLOAD_LOG WHERE NOMBRE_FACTURA = '{0}'".format(df_name))
            data = cursor.fetchall()

            if len(data) == 0:
                rootLogger.info("No se encontró en la base de datos... Se inicia la Descarga!")

                filepath = downloadFilePath + "\\" + df_name
                rootLogger.info("filepath: " + filepath)
                try:
                    driver.find_element_by_id(reg[1]).click()
                except:
                    driver.execute_script("document.getElementById(\"RadGrid6_GridData\").scroll(0,"+str(175*scroll)+")")
                    scroll=scroll+1
                    driver.find_element_by_id(reg[1]).click()
                
                val.validate_download(filepath+".xml")

                try:
                    driver.find_element_by_id(reg[2]).click()
                except:
                    driver.execute_script("document.getElementById(\"RadGrid6_GridData\").scroll(0,"+str(175*scroll)+")")
                    driver.find_element_by_id(reg[2]).click()
                
                val.validate_download(filepath+".pdf")
                
                flag = True

                for ext in exts:
                    if not os.path.exists(filepath + ext):
                        flag = False

                        for filename in glob.glob(filepath + "*"):
                            os.remove(filename)

                        for filename in glob.glob(downloadFilePath + "*.tmp"):
                            os.remove(filename)
                        e = e + 1
                        break

                if flag:
                    rootLogger.info("Archivos Descargados satisfactoriamente... df_name: "+df_name)
                    downloaded_dfs.append(df_name)
                    rootLogger.info("Moviendo archivos al servidor...")
                    move_file_to_server(df_name)
                    for filename in glob.glob(filepath + "*"):
                        os.remove(filename)
                    rootLogger.info("Guardando información en la base de datos...")
                    cursor.execute(
                        "INSERT INTO MEXICO.DF_DOWNLOAD_LOG(NOMBRE_FACTURA) values ('{0}')".format(df_name))

                    d = d + 1

            else:
                rootLogger.info("Los archivos ya se descargaron...")

            conn.commit()
            conn.close()

            i = i + 1

        driver.switch_to.default_content()

        driver.find_element_by_id("btnCerrarSesion").click()

        driver.quit()

        end = time.time()
        total_time = end - start
        mins, secs = divmod(total_time, 60)
        hours, mins = divmod(mins, 60)

        rootLogger.info("Se descargaron un total de {0} Documentos Fiscales".format(d))
        rootLogger.info("Falló la descarga de {0} Documentos Fiscales".format(e))
        rootLogger.info("El proceso finalizón en {0:.0f}:{1:.0f}:{2:.2f} ".format(hours, mins, secs))

        
        if d > 0:
            mail.send_success_email('robot@mercadoselectricos.com.sv','francisco.huezo@mercadoselectricos.com.sv',
                            "Proceso de Descarga finalizado","Descarga de archivos finalizada",
                            type='success', report_count= d)

        return downloaded_dfs

    except Exception as ex:
        template = "An exception of type {0} occurred. Arguments:\n{1!r}"
        for filename in glob.glob(downloadFilePath+"\\" + "*"):
            os.remove(filename)
        message = template.format(type(ex).__name__, ex.args)
        rootLogger.error(message, exc_info=True)
        driver.quit()
        mail.send_warning_email('robot@mercadoselectricos.com.sv', 'francisco.huezo@mercadoselectricos.com.sv',
                        "Proceso de Descarga finalizado", "Error en el proceso de descarga de archivos")

        return []

if __name__ == "__main__":
    download_files()