INSERT INTO `validacfdi` (`idValidacion`,`RFCEmisor`,`NombreEmisor`,`UUID`,`Total`,`FechaCFDI`,`tipoCambio`,`tipoMoneda`,`FechaEvento`,`RFCReceptor`,`XML`,`Estatus`,`Resultado`,`Serie`,`Folio`) VALUES 
 (57,'EAC030618A65','EURO AMERICANO DE CALZADO, S.A. DE C.V.','37618CC9-FAAB-2B43-9F05-266B85104ED2','80620.000000','2016-05-03 09:45:26','1.000000','MXN','2016-05-25 14:17:19','AOZ9903024A5','[encoded data, 14700 characters]','Rechazado','UHJ1ZWJhVG9kb3MNCi0tLS0tLS0tLS0tLS0tLS0tDQpbQ2VydGlmaWNhZG9WYWxpZG9dIEVsIFhNTCB0aWVuZSB1biBu+m1lcm8gZGUgc2VyaWUgZGVsIGNlcnRpZmljYWRvIGRpZmVyZW50ZSBhbCBkZWwgY2VydGlmaWNhZG8gaW5jcnVzdGFkbyBlbiBlbCBtaXNtby4=','FE','6096'),
 (58,'EAC030618A65','EURO AMERICANO DE CALZADO, S.A. DE C.V.','37618CC9-FAAB-2B43-9F05-266B85104ED2','80620.000000','2016-05-03 09:45:26','1.000000','MXN','2016-05-25 14:17:29','AOZ9903024A5','[encoded data, 14700 characters]','Rechazado','UHJ1ZWJhVG9kb3MNCi0tLS0tLS0tLS0tLS0tLS0tDQpbQ2VydGlmaWNhZG9WYWxpZG9dIEVsIFhNTCB0aWVuZSB1biBu+m1lcm8gZGUgc2VyaWUgZGVsIGNlcnRpZmljYWRvIGRpZmVyZW50ZSBhbCBkZWwgY2VydGlmaWNhZG8gaW5jcnVzdGFkbyBlbiBlbCBtaXNtby4=','FE','6096'),
 (59,'EAC030618A65','EURO AMERICANO DE CALZADO, S.A. DE C.V.','37618CC9-FAAB-2B43-9F05-266B85104ED2','80620.000000','2016-05-03 09:45:26','1.000000','MXN','2016-05-25 14:23:19','AOZ9903024A5','[encoded data, 14700 characters]','Aceptado','UHJ1ZWJhVG9kb3MNCi0tLS0tLS0tLS0tLS0tLS0tDQpEb2N1bWVudG8gVuFsaWRv','FE','6096'),
 (60,'LBE890926HMA','Laboratorios Becar, S.A. de C.V.','35CEC233-EA03-4B8C-BBF6-56D114F8EBA9','3630.800000','2015-08-10 11:06:57','1.00','MXN','2016-05-31 17:46:06','BF&831024CT0','[encoded data, 6244 characters]','Aceptado','UHJ1ZWJhVG9kb3N8UHJ1ZWJhRGVtbw0KLS0tLS0tLS0tLS0tLS0tLS0NCkRvY3VtZW50byBW4WxpZG8=','B','9991'),
 (61,'EME870831T20','EPSON México, S.A. de C.V.','9A5639AF-F8DE-0731-C70C-3AE3C3BBC74B','8935.380000','2015-11-10 08:46:28','1.0000','MXN','2016-05-31 17:47:23','GDA1212134L3','[encoded data, 5672 characters]','Rechazado','[encoded data, 8500 characters]','L','18248'),
 (62,'AARS890227FC0','SANDRA ARAUJO RAMIREZ','3123434B-F26E-438B-8E9C-95CEB81963BE','178170.780000','2016-05-02 17:49:26','1.00','MXN','2016-06-16 10:12:15','CPO8710026Z3','[encoded data, 152000 characters]','Rechazado','UHJ1ZWJhVG9kb3N8UHJ1ZWJhRGVtbw0KLS0tLS0tLS0tLS0tLS0tLS0NCltYU0RdIEVsIGRvY3VtZW50byBlcyBJTlbBTElETzoKV2FybmluZzogTm8gc2UgcHVlZGUgY2FyZ2FyIGVsIGVzcXVlbWEgcGFyYSBlbCBlc3BhY2lvIGRlIG5vbWJyZXMgJ2h0dHA6Ly93d3cuY2FwYWRlb3pvbm8uY29tLm14L2NmZC94c2QvJyAtIEVycm9yIGVuIGVsIHNlcnZpZG9yIHJlbW90bzogKDQwMSkgTm8gYXV0b3JpemFkby4=','A','440');
INSERT INTO `validacfdi` (`idValidacion`,`RFCEmisor`,`NombreEmisor`,`UUID`,`Total`,`FechaCFDI`,`tipoCambio`,`tipoMoneda`,`FechaEvento`,`RFCReceptor`,`XML`,`Estatus`,`Resultado`,`Serie`,`Folio`) VALUES 
 (63,'EAC030618A65','EURO AMERICANO DE CALZADO, S.A. DE C.V.','37618CC9-FAAB-2B43-9F05-266B85104ED2','80620.000000','2016-05-03 09:45:26','1.000000','MXN','2016-06-16 10:12:37','AOZ9903024A5','[encoded data, 14700 characters]','Aceptado','UHJ1ZWJhVG9kb3MNCi0tLS0tLS0tLS0tLS0tLS0tDQpEb2N1bWVudG8gVuFsaWRv','FE','6096'),
 (64,'LBE890926HMA','Laboratorios Becar, S.A. de C.V.','35CEC233-EA03-4B8C-BBF6-56D114F8EBA9','3630.800000','2015-08-10 11:06:57','1.00','MXN','2016-06-17 09:40:43','BF&831024CT0','[encoded data, 6244 characters]','Rechazado','DQotLS0tLS0tLS0tLS0tLS0tLQ0KRWwgcmVjZXB0b3IgeWEgbm8gY3VlbnRhIGNvbiBjculkaXRvcyBkaXNwb25pYmxlcyBjb21vIERFTU8sIGZhdm9yIGRlIGNvbXVuaWNhcnNlIGEgdmVudGFzQGRpc3hpaS5jb20u','B','9991'),
 (65,'EME870831T20','EPSON México, S.A. de C.V.','9A5639AF-F8DE-0731-C70C-3AE3C3BBC74B','8935.380000','2015-11-10 08:46:28','1.0000','MXN','2016-06-17 09:41:25','GDA1212134L3','[encoded data, 5672 characters]','Rechazado','UHJ1ZWJhVG9kb3N8UHJ1ZWJhRGVtbw0KLS0tLS0tLS0tLS0tLS0tLS0NCltTZWxsb0VtaXNvcl0gRWwgc2VsbG8gZGVsIGNvbXByb2JhbnRlIG5vIGNvcnJlc3BvbmRlIGNvbiBsYSBpbmZvcm1hY2nzbiBkZWwgWE1MIFtTZWxsbyBJbnbhbGlkb10=','L','18248'),
 (66,'EAC030618A65','EURO AMERICANO DE CALZADO, S.A. DE C.V.','37618CC9-FAAB-2B43-9F05-266B85104ED2','80620.000000','2016-05-03 09:45:26','1.000000','MXN','2016-08-26 12:11:05','AOZ9903024A5','[encoded data, 14700 characters]','Rechazado','DQotLS0tLS0tLS0tLS0tLS0tLQ0KVXN1YXJpbyB5L28gUGFzc3dvcmQgZXMgaW5jb3JyZWN0by4=','FE','6096');


--
-- Definition of procedure `ListaEmisoresPermitidos`
--

DROP PROCEDURE IF EXISTS `ListaEmisoresPermitidos`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`%` PROCEDURE `ListaEmisoresPermitidos`(in tcPerfil char(30) )
BEGIN
declare iRestxEmi int;
select count(*) from RestxEmi where RestxEmi.perfil = tcPerfil COLLATE utf8_spanish_ci into iRestxEmi;
if iRestxEmi > 0 then
	select EMISOR.RFC, Emisor.IDemisor FROM EMISOR where Emisor.IDemisor in (select idEmisor from RestxEmi where RestxEmi.perfil = tcPerfil COLLATE utf8_spanish_ci);
else
    select EMISOR.RFC, Emisor.IDemisor FROM EMISOR;
end if; 
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `ListaTDoctosPermitidos`
--

DROP PROCEDURE IF EXISTS `ListaTDoctosPermitidos`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`%` PROCEDURE `ListaTDoctosPermitidos`(in tcPerfil char(30))
BEGIN
declare iRest int;
select count(*) from RestxEmi where RestxEmi.perfil = tcPerfil COLLATE utf8_spanish_ci into iRest;
if iRest > 0 then
   	select count(*) from ResEmiTDocto where ResEmiTdocto.perfil  = tcPerfil COLLATE utf8_spanish_ci into iRest; 
	if iRest > 0 then    
		SELECT t.tipodocto,t.idemisor,t.filtroa,t.filtrob,t.filtroc FROM tdoctos as t join (select rtd.idEmisor,rtd.tipoDocto from ResEmiTDocto as rtd where rtd.perfil  = tcPerfil COLLATE utf8_spanish_ci) as RTDocto on RTDocto.idEmisor = t.idEmisor and RTDocto.tipoDocto = t.TipoDocto ;
    else
		SELECT tipodocto,idemisor,filtroa,filtrob,filtroc FROM tdoctos where idEmisor in (select idEmisor from RestxEmi where RestxEmi.perfil = tcPerfil COLLATE utf8_spanish_ci);
    end if;
else
    SELECT tipodocto,idemisor,filtroa,filtrob,filtroc FROM tdoctos;
end if; 
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `LoggerEnviaEmail`
--

DROP PROCEDURE IF EXISTS `LoggerEnviaEmail`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `LoggerEnviaEmail`(in tcTO varchar(254), in tcSubject varchar(254), tnNoEnvios TINYINT(2))
BEGIN

    declare lnNoEnvios int;
    declare lnRows int;

    select count(cTo) into lnRows from logenviaemail where cTo COLLATE utf8_spanish2_ci=tcTo COLLATE utf8_spanish2_ci and cSubject COLLATE utf8_spanish2_ci=tcSubject COLLATE utf8_spanish2_ci;
if lnRows < 1 then
    INSERT INTO logenviaemail (cTo,cSubject,nNoEnvios) VALUES (tcTo,tcSubject,1);
    select 1 into lnNoEnvios;
else
 select nNoEnvios+1 into lnNoEnvios from logenviaemail where cTo COLLATE utf8_spanish2_ci=tcTo COLLATE utf8_spanish2_ci and cSubject COLLATE utf8_spanish2_ci=tcSubject COLLATE utf8_spanish2_ci;
    if lnNoEnvios <= tnNoEnvios then
  update logenviaemail set nNoEnvios = lnNoEnvios where cTo COLLATE utf8_spanish2_ci=tcTo COLLATE utf8_spanish2_ci and cSubject COLLATE utf8_spanish2_ci =tcSubject COLLATE utf8_spanish2_ci;
 else
  select -1 into lnNoEnvios;
    end if;
end if;

    select lnNoEnvios;


END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;



