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()