-- Table structure for table `zones`
--

CREATE TABLE `zones` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(180) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sifere_coef` decimal(16,2) DEFAULT '0.00',
  `iibb_aliquot` decimal(16,2) DEFAULT '0.00',
  `iibb_perception_id` int(10) unsigned DEFAULT NULL,
  `retention_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sircreb_plan_cuenta_id` int(10) unsigned NOT NULL DEFAULT '1',
  `codigo_cm` int(11) NOT NULL DEFAULT '0',
  `codigo_afip` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `zones_name_unique` (`name`),
  KEY `zones_sircreb_plan_cuenta_id_foreign` (`sircreb_plan_cuenta_id`),
  CONSTRAINT `zones_sircreb_plan_cuenta_id_foreign` FOREIGN KEY (`sircreb_plan_cuenta_id`) REFERENCES `conta_plan_cuenta` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `zones`
--

INSERT INTO `zones` VALUES (1,'Córdoba',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','10',281,904,'3'),(2,'Corrientes',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','20',282,905,'4'),(3,'Chaco',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','26',279,906,'16'),(4,'Chubut',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','29',280,907,'17'),(5,'Entre Ríos',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','30',283,908,'5'),(6,'Formosa',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','35',284,909,'18'),(7,'Jujuy',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','37',285,910,'6'),(8,'La Pampa',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','38',286,911,'21'),(9,'La Rioja',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','47',287,912,'8'),(10,'Mendoza',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','49',288,913,'7'),(11,'Misiones',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','51',289,914,'19'),(12,'Neuquén',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','56',290,915,'20'),(13,'Río Negro',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','57',292,916,'22'),(14,'Salta',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','62',293,917,'9'),(15,'San Juan',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','64',294,918,'10'),(16,'San Luis',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','66',295,919,'11'),(17,'Santa Cruz',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','69',296,920,'23'),(18,'Santa Fe',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','70',297,921,'12'),(19,'Santiago del Estero',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','76',298,922,'13'),(20,'Tierra del Fuego',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','83',299,923,'24'),(21,'Tucuman',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','85',300,924,'14'),(22,'Capital Federal',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','01',277,901,'0'),(23,'Buenos Aires',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','02',276,902,'1'),(24,'Catamarca',0.00,0.00,NULL,NULL,NULL,'2022-02-10 10:47:39','09',278,903,'2'),(25,'Pascua Lama',0.00,0.00,NULL,NULL,NULL,NULL,'E3',291,0,NULL);

--
-- Dumping routines for database 'db27-42949985-8'
--
/*!50003 DROP PROCEDURE IF EXISTS `anticipos_ganancias` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `anticipos_ganancias`(anioUno int,anioDos int,anioTres int,anioCuatro int,
anioCinco int,anioSeis int,anioSiete int,anioOcho int,anioNueve int,anioDiez int,anioOnce int,anioDoce int,mesUno int,
mesDos int,mesTres int,mesCuatro int,mesCinco int,mesSeis int,mesSiete int,mesOcho int,mesNueve int,mesDiez int,
mesOnce int,mesDoce int, person_id int)
BEGIN

    SELECT 
        `types`.`id` AS `IdOperacion`,
        `types`.`name` AS `Operacion`,
        `receipts`.`person_id_owner` AS `IdEmpresa`,
        `b`.`document` AS `DocumentoEmpresa`,
        `b`.`field_name1` AS `NombreEmpresa`,
        SUM((CASE
            WHEN (`p`.`year` = anioUno) AND (`p`.`period_number` = mesUno) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `UNO`,
        SUM((CASE
            WHEN (`p`.`year` = anioDos) AND (`p`.`period_number` = mesDos) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `DOS`,
        SUM((CASE
            WHEN (`p`.`year` = anioTres) AND (`p`.`period_number` = mesTres) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `TRES`,
        SUM((CASE
            WHEN (`p`.`year` = anioCuatro) AND (`p`.`period_number` = mesCuatro) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `CUATRO`,
        SUM((CASE
            WHEN (`p`.`year` = anioCinco) AND (`p`.`period_number` = mesCinco) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `CINCO`,
        SUM((CASE
            WHEN (`p`.`year` = anioSeis) AND (`p`.`period_number` = mesSeis) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `SEIS`,
        SUM((CASE
            WHEN (`p`.`year` = anioSiete) AND (`p`.`period_number` = mesSiete) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `SIETE`,
        SUM((CASE
            WHEN (`p`.`year` = anioOcho) AND (`p`.`period_number` = mesOcho) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `OCHO`,
        SUM((CASE
            WHEN (`p`.`year` = anioNueve) AND (`p`.`period_number` = mesNueve) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `NUEVE`,
        SUM((CASE
            WHEN (`p`.`year` = anioDiez) AND (`p`.`period_number` = mesDiez) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `DIEZ`,
        SUM((CASE
            WHEN (`p`.`year` = anioOnce) AND (`p`.`period_number` = mesOnce) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `ONCE`,
        SUM((CASE
            WHEN (`p`.`year` = anioDoce) AND (`p`.`period_number` = mesDoce) THEN `receipts`.`amount`
            ELSE 0
        END)) AS `DOCE`
    FROM
        (((((`receipts`
        LEFT JOIN `persons` `b` ON ((`receipts`.`person_id_owner` = `b`.`id`)))
        LEFT JOIN `periods` `p` ON ((`receipts`.`period_id` = `p`.`id`)))
        LEFT JOIN `status` ON ((`receipts`.`status_id` = `status`.`id`)))
        LEFT JOIN `persons` ON ((`receipts`.`person_id_relationed` = `persons`.`id`)))
        LEFT JOIN `types` ON ((`receipts`.`type_id` = `types`.`id`)))
    WHERE
        (`types`.`id` IN (4 , 5, 6))
        and receipts.deleted_at is null
        and  `receipts`.`person_id_owner`= person_id
    GROUP BY `types`.`id` , `types`.`name` , `receipts`.`person_id_owner` , `b`.`document` , `b`.`field_name1`
    ORDER BY `types`.`id` , `receipts`.`person_id_owner`

;

  END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `asiento_sueldo` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `asiento_sueldo`(anioUno int,anioDos int,anioTres int,anioCuatro int,anioCinco int,
        anioSeis int,anioSiete int,anioOcho int,anioNueve int,anioDiez int,anioOnce int,anioDoce int,mesUno int,mesDos int,mesTres int,mesCuatro int,mesCinco int,mesSeis int,mesSiete int,mesOcho int,mesNueve int,mesDiez int,
        mesOnce int,mesDoce int, person_id int)
BEGIN
            
            SELECT
    `bld`.`person_id` AS `person_id`,
    `p`.`field_name1` AS `empresa`,
    SUM((CASE
             WHEN
                     (`periods`.`year` = anioUno)
                     AND (`periods`.`period_number` = mesUno)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `UNO`,

    SUM((CASE
             WHEN
                     (`periods`.`year` = anioDos)
                     AND (`periods`.`period_number` = mesDos)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `DOS`,

    SUM((CASE
             WHEN
                     (`periods`.`year` = anioTres)
                     AND (`periods`.`period_number` = mesTres)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `TRES`,

    SUM((CASE
             WHEN
                     (`periods`.`year` = anioCuatro)
                     AND (`periods`.`period_number` = mesCuatro)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `CUATRO`,

    SUM((CASE
             WHEN
                     (`periods`.`year` = anioCinco)
                     AND (`periods`.`period_number` = mesCinco)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `CINCO`,

    SUM((CASE
             WHEN
                     (`periods`.`year` = anioSeis)
                     AND (`periods`.`period_number` = mesSeis)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `SEIS`,

    SUM((CASE
             WHEN
                     (`periods`.`year` = anioSiete)
                     AND (`periods`.`period_number` = mesSiete)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `SIETE`,

    SUM((CASE
             WHEN
                     (`periods`.`year` = anioOcho)
                     AND (`periods`.`period_number` = mesOcho)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `OCHO`,

    SUM((CASE
             WHEN
                     (`periods`.`year` = anioNueve)
                     AND (`periods`.`period_number` = mesNueve)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `NUEVE`,

    SUM((CASE
             WHEN
                     (`periods`.`year` = anioDiez)
                     AND (`periods`.`period_number` = mesDiez)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `DIEZ`,

    SUM((CASE
             WHEN
                     (`periods`.`year` = anioOnce)
                     AND (`periods`.`period_number` = mesOnce)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `ONCE`,

    SUM((CASE
             WHEN
                     (`periods`.`year` = anioDoce)
                     AND (`periods`.`period_number` = mesDoce)
                 THEN
                 (CASE
                      WHEN
                          ((`pc`.`id` = 110)
                              OR (`pc`.`id` = 111)
                              OR (`pc`.`id` = 112))
                          THEN
                          `bld`.`monto`
                     END)
        END)) AS `DOCE`

FROM
    (((((`balance_libro_details` `bld`
        LEFT JOIN `conta_tipo_saldos` `ts` ON ((`bld`.`saldo_id` = `ts`.`id`)))
        LEFT JOIN `persons` `p` ON ((`bld`.`person_id` = `p`.`id`)))
        LEFT JOIN `periods` ON ((`bld`.`period_id` = `periods`.`id`)))
        LEFT JOIN `conta_tipo_asientos` `ta` ON ((`bld`.`conta_tipo_asiento_id` = `ta`.`id`)))
        LEFT JOIN `conta_plan_cuenta` `pc` ON ((`bld`.`plan_cuenta_id` = `pc`.`id`)))

where `bld`.deleted_at is null
	and `bld`.`person_id` = person_id

GROUP BY `bld`.`person_id` , `p`.`field_name1`;
        
        
          END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `assingFromPeriodsPerson` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `assingFromPeriodsPerson`(from_year int, from_month int, personId int)
BEGIN
          declare done int default 0;
          declare counter int default 0;
          declare endYear, initialMonth, person_ids, endMonth int;
          declare code_period varchar(20);
          DECLARE currents CURSOR FOR
            SELECT case when min(code) is not null then min(code) else pc.liquidation_start_period end as code, pc.person_id
              FROM person_configurations pc
              left join periods pd on pc.person_id = pd.person_id
              where pc.liquidation_start_period is not null
               and pc.person_id = personId
              GROUP BY person_id, pc.liquidation_start_period
          ;

          DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

          insert into log_debug (function_name, message, date) VALUES('assingFromPeriodsPerson', concat('ejecutando funcion'), now() );

          OPEN currents;
          REPEAT
            FETCH currents INTO code_period, person_ids;

            IF NOT done THEN
            set counter = 1;
            set endYear = CAST(SUBSTRING(code_period,1,4) AS SIGNED);
            set endMonth = 0;

            insert into log_debug (function_name, message, date) VALUES('assingFromPeriodsPerson', concat('Iterando cada empresa, Empres id->', person_ids,', periodo->', code_period), now() );


             while from_year <= endYear do
                insert into log_debug (function_name, message, date) VALUES('assingFromPeriodsPerson', concat('While de empresa, from_year->', from_year,', initialMonth->', initialMonth, ', contador->', counter), now() );

                set initialMonth = from_month;
                if(from_year!=endYear) then
                    set endMonth = 12;
                else
                    set endMonth = CAST(SUBSTRING(code_period,6,LENGTH(code_period)) AS SIGNED) -1;
                end if;
                
                if(counter!=1)then
                    set initialMonth = 1;
                else
                    set initialMonth = from_month;
                end if;

               insert Into periods (code, year, period_number, date_from, date_to, person_id)
                 select concat(from_year, '-', lpad(m.month, 2, '0')), from_year, m.month,
                  concat(from_year, '-', m.month, '-01'), concat(from_year, m.date_to),
                  p.id from persons p, months m
                  where p.id = person_ids
                  and (m.month >= initialMonth and m.month <= endMonth)
                   order by 2,3;
               set from_year = from_year + 1;
               set counter = counter +1;
             END WHILE;
            END IF;

          UNTIL done END REPEAT;

          CLOSE currents;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `assingMorePeriods` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `assingMorePeriods`(to_year int)
BEGIN
          declare done int default 0;
          declare counter int default 0;
          declare end_year, initial_month, person_ids, endMonth int;
          declare code_period varchar(20);
          DECLARE currents CURSOR FOR
        	SELECT case when max(code) is not null then max(code) else pc.liquidation_start_period end as code, pc.person_id
        	  FROM person_configurations pc
        	  left join periods pd on pc.person_id = pd.person_id
              where pc.liquidation_start_period is not null
              GROUP BY person_id, pc.liquidation_start_period
          ;

          DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

          insert into log_debug (function_name, message, date) VALUES('assingMorePeriods', concat('ejecutando funcion'), now() );

          OPEN currents;
          REPEAT
            FETCH currents INTO code_period, person_ids;

            IF NOT done THEN
        	set counter = 0;
        	set end_year = CAST(SUBSTRING(code_period,1,4) AS SIGNED);
            set initial_month = CAST(SUBSTRING(code_period,6,LENGTH(code_period)) AS SIGNED) + 1;

            insert into log_debug (function_name, message, date) VALUES('assingMorePeriods', concat('Iterando cada empresa, Empres id->', person_ids,', periodo->', code_period), now() );


        	 while end_year <= to_year do
        		insert into log_debug (function_name, message, date) VALUES('assingMorePeriods', concat('While de empresa, year->', end_year,', initial_month->', initial_month, ', contador->', counter), now() );

        		IF counter > 0 then set initial_month = 1; end if;
        		set endMonth = 12;

               insert Into periods (code, year, period_number, date_from, date_to, person_id)
                 select concat(end_year, '-', lpad(m.month, 2, '0')), end_year, m.month,
                  concat(end_year, '-', m.month, '-01'), concat(end_year, m.date_to),
                  p.id from persons p, months m
                  where p.id = person_ids
                  and (m.month >= initial_month and m.month <= endMonth)
                   order by 2,3;
               set end_year = end_year + 1;
               set counter = counter +1;
        	 END WHILE;
            END IF;

          UNTIL done END REPEAT;

          CLOSE currents;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `assingPeriodsToAllBusiness` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `assingPeriodsToAllBusiness`(from_code varchar(7), to_code varchar(7))
BEGIN
          declare done int default 0;
          declare counter int default 0;
          declare end_year, from_year, to_year, to_month, initial_month, person_ids, endMonth int;
          declare code_period varchar(20);
          DECLARE currents CURSOR FOR
        	SELECT case when max(code) is not null then max(code) else pc.liquidation_start_period end as code, pc.person_id
        	  FROM person_configurations pc
        	  left join periods pd on pc.person_id = pd.person_id
              where pc.liquidation_start_period is not null
              GROUP BY person_id, pc.liquidation_start_period
          ;

          DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

          insert into log_debug (function_name, message, date) VALUES('assingPeriodsToBusiness', concat('ejecutando funcion'), now() );

          OPEN currents;
          REPEAT
            FETCH currents INTO code_period, person_ids;

            IF NOT done THEN
        	set counter = 0;
        	set end_year = CAST(SUBSTRING(code_period,1,4) AS SIGNED);

            set from_year = CAST(SUBSTRING(from_code,1,4) AS SIGNED);
            
            set initial_month = CAST(SUBSTRING(from_code,6,2) AS SIGNED);

            set to_month = CAST(SUBSTRING(to_code,6,2) AS SIGNED);
            set to_year = CAST(SUBSTRING(to_code,1,4) AS SIGNED);

            

        	 while from_year <= to_year do
             
        	 

        		IF counter > 0 then
					set initial_month = 1;
				end if;

                if from_year < to_year then
					set endMonth = 12;
				else
					set endMonth = to_month;
                end if;

                insert into log_debug (function_name, message, date) VALUES('assingPeriodsToBusiness'
                  , concat('While de empresa, year->', from_year,', initial_month->', initial_month, ', endMonth->', endMonth, ', contador->', counter)
                  , now() );

               insert Into periods (code, year, period_number, date_from, date_to, person_id)

                  select t1.code, t1.year, t1.month, t1.day_from, t1.day_to, t1.person_id
					from
					(select concat(from_year, '-', lpad(m.month, 2, '0')) as code, from_year as year, m.month,
					  concat(from_year, '-', m.month, '-01') as day_from, concat(from_year, m.date_to) as day_to,
					  p.id as person_id, concat(p.id, from_year, '-', lpad(m.month, 2, '0')) as pcodigo
					  from persons p, months m
					  where p.id = person_ids
					  and (m.month >= initial_month and m.month <= endMonth) ) as t1
					   where t1.pcodigo not in
					   (select concat(pe.person_id, pe.code) from periods as pe where person_id=person_ids and year=from_year )

                   order by 2,3;
               set from_year = from_year + 1;
               
               set counter = counter +1;
        	 END WHILE;
            END IF;

          UNTIL done END REPEAT;

          CLOSE currents;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `assingPeriodsToBusiness` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `assingPeriodsToBusiness`(from_code varchar(7), to_code varchar(7), Pperson_id int)
BEGIN
          declare done int default 0;
          declare counter int default 0;
          declare end_year, from_year, to_year, to_month, initial_month, person_ids, endMonth int;
          declare code_period varchar(20);
          DECLARE currents CURSOR FOR
        	SELECT case when max(code) is not null then max(code) else pc.liquidation_start_period end as code, pc.person_id
        	  FROM person_configurations pc
        	  left join periods pd on pc.person_id = pd.person_id
              where pc.liquidation_start_period is not null
              and pd.person_id = Pperson_id
              GROUP BY person_id, pc.liquidation_start_period
          ;

          DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

          insert into log_debug (function_name, message, date) VALUES('assingPeriodsToBusiness', concat('ejecutando funcion'), now() );

          OPEN currents;
          REPEAT
            FETCH currents INTO code_period, person_ids;

            IF NOT done THEN
        	set counter = 0;
        	set end_year = CAST(SUBSTRING(code_period,1,4) AS SIGNED);

            set from_year = CAST(SUBSTRING(from_code,1,4) AS SIGNED);
            
            set initial_month = CAST(SUBSTRING(from_code,6,2) AS SIGNED);

            set to_month = CAST(SUBSTRING(to_code,6,2) AS SIGNED);
            set to_year = CAST(SUBSTRING(to_code,1,4) AS SIGNED);

            

        	 while from_year <= to_year do
             
        	 

        		IF counter > 0 then set initial_month = 1; end if;

                if from_year < to_year then set endMonth = 12; else set endMonth = to_month; end if;
                insert into log_debug (function_name, message, date) VALUES('assingPeriodsToBusiness'
                  , concat('While de empresa, year->', from_year,', initial_month->', initial_month, ', endMonth->', endMonth, ', contador->', counter)
                  , now() );
               insert Into periods (code, year, period_number, date_from, date_to, person_id)
                  select t1.code, t1.year, t1.month, t1.day_from, t1.day_to, t1.person_id
					from
					(select concat(from_year, '-', lpad(m.month, 2, '0')) as code, from_year as year, m.month,
					  concat(from_year, '-', m.month, '-01') as day_from, concat(from_year, m.date_to) as day_to,
					  p.id as person_id, concat(p.id, from_year, '-', lpad(m.month, 2, '0')) as pcodigo
					  from persons p, months m
					  where p.id = person_ids
					  and (m.month >= initial_month and m.month <= endMonth) ) as t1
					   where t1.pcodigo not in
					   (select concat(pe.person_id, pe.code) from periods as pe where person_id=person_ids and year=from_year )
                   order by 2,3;
               set from_year = from_year + 1;
               
               set counter = counter +1;
        	 END WHILE;
            END IF;
          UNTIL done END REPEAT;
          CLOSE currents;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CreacionUsuarioEmpresa` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `CreacionUsuarioEmpresa`(
            in usuario varchar(100),
            in email varchar(100),
            in person_id varchar(100),
            in parent_person_id varchar(100)
            )
BEGIN

            START TRANSACTION;

            
            insert into tusers (loginUser, claveUser, emailUser, user_type_id)
            values (usuario, "$2y$10$STkVOGUoqFuysS6UTcIU5udWXGfivahbCF4Fou7lLGPORBsiyctG.", email, 2);

            
            insert into tusermodules (idUser, idModule)
            select u.idUser, m.idModule
            from tusers u, tmodules m
            where u.loginUser= usuario
            and m.idModule in (16,19,21,26,27);

            
            insert into user_persons (user_id, person_id,parent_person_id)
            select u.idUser, p.id, parent_person_id
            from tusers u, persons p
            where u.loginUser= usuario
            and p.id = person_id;
            

            COMMIT;

            END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `cta_cte_cliente` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `cta_cte_cliente`(person_id Int)
BEGIN
SELECT
        `cta`.`id` as `id_cta_cte`,
        `ta`.`id` AS `tipo_asiento_id`,
        `conta_t_a`.`nombre` AS `operacion`,
        `cta`.`saldo_id` AS `saldo_id`,
        `cta`.`comentario` AS `comentario`,
        `cta`.`person_id`,
        `cta`.`person_id_relationed`,
        `periods`.`id` as `period_id`,
        `periods`.`code` as `periodo_code`,
        `ta`.`nombre` AS `asiento`,
        `cta`.`cta_cte_tesoreria_cuenta_id` AS `cuenta_tesoreria_id`,
        `cta_tesoreria`.`nombre` AS `cuenta`,
        `cta`.`fecha` AS `fecha`,
        `p`.`field_name1` AS `empresa_titular`,
        `p`.`document` AS `empresa_titular_cuit`,
        `zone_titular`.`name` AS `empresa_titular_zone`,
        `zone_titular`.`id` AS `empresa_titular_zone_id`,
        `per_rela`.`field_name1` AS `empresa_relacionada`,
        `per_rela`.`document` AS `empresa_relacionada_cuit`,
        `per_rela`.`address` AS `empresa_relacionada_direccion`,
        `per_rela`.`address` AS `empresa_relacionada_direccion`,
        `zone_rela`.`name` AS `empresa_relacionada_zone`,
        `zone_rela`.`id` AS `empresa_relacionada_zone_id`,
        `cta`.`file`,
        (CASE
            WHEN (`cta`.`saldo_id` = 1) THEN `cta`.`total`
            ELSE 0
        END) AS `DEBE`,
        (CASE
            WHEN (`cta`.`saldo_id` = 2) THEN `cta`.`total`
            ELSE 0
        END) AS `HABER`,
        `cta`.`total`
    FROM `cta_cte_cliente` `cta`
        JOIN `conta_tipo_saldos` `ts` ON `cta`.`saldo_id` = `ts`.`id`
        JOIN `persons` `p` ON `cta`.`person_id` = `p`.`id`
        JOIN `persons` `per_rela` ON `cta`.`person_id_relationed` = `per_rela`.`id`
        JOIN `periods` ON `cta`.`period_id` = `periods`.`id`
        LEFT JOIN `zones` `zone_titular` ON `p`.`zone_id` = `zone_titular`.`id`
        LEFT JOIN `zones` `zone_rela` ON `per_rela`.`zone_id` = `zone_rela`.`id`
        JOIN `conta_tipo_asientos` `ta` ON `cta`.`tipo_asiento_id` = `ta`.`id`
        JOIN `cta_cte_tesoreria_cuentas` `cta_tesoreria` ON `cta`.`cta_cte_tesoreria_cuenta_id` = `cta_tesoreria`.`id`
        JOIN `conta_tipo_asientos` `conta_t_a` ON `cta`.`tipo_asiento_id` = `conta_t_a`.`id`
    
    WHERE
        `cta`.`person_id` = person_id
        and (`cta`.tipo_asiento_id = 20 or `cta`.tipo_asiento_id = 21 )
        
    ORDER BY `cta`.`id` , `cta`.`id` , `cta`.`saldo_id` , `p`.`field_name1`;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `delete_liquidation_status1` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `delete_liquidation_status1`()
BEGIN
    
    
    delete from period_liquidation_iva_details where period_liquidation_id in
    (
      SELECT id from
        (
        select * FROM period_liquidations where status =1
        ) foo
    );

    
    delete FROM period_liquidation_iva where period_liquidation_id in
    (
      SELECT id FROM period_liquidations where status =1
    );

    
    delete FROM period_liquidation_details where period_liquidation_id in
    (
    SELECT id FROM period_liquidations where status =1
    );

    
    delete FROM period_liquidations where status =1;

  END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `iibb_gasto` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `iibb_gasto`(anioUno int,anioDos int,anioTres int,anioCuatro int,anioCinco int,
        anioSeis int,anioSiete int,anioOcho int,anioNueve int,anioDiez int,anioOnce int,anioDoce int,mesUno int,mesDos int,
        mesTres int,mesCuatro int,mesCinco int,mesSeis int,mesSiete int,mesOcho int,mesNueve int,mesDiez int,
        mesOnce int,mesDoce int, person_id int)
BEGIN
        
            SELECT 
        `pld`.`person_id` AS `IdEmpresa`,
        `b`.`field_name1` AS `NombreEmpresa`,
                        SUM((CASE
                    WHEN 
                        (`p`.`year` = anioUno)
                        AND (`p`.`period_number` = mesUno)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `UNO`,
                SUM((CASE
                    WHEN 
                        (`p`.`year` = anioDos)
                        AND (`p`.`period_number` = mesDos)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `DOS`,
                SUM((CASE
                    WHEN 
                        (`p`.`year` = anioTres)
                        AND (`p`.`period_number` = mesTres)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `TRES`,
                SUM((CASE
                    WHEN 
                        (`p`.`year` = anioCuatro)
                        AND (`p`.`period_number` = mesCuatro)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `CUATRO`,
                SUM((CASE
                    WHEN 
                        (`p`.`year` = anioCinco)
                        AND (`p`.`period_number` = mesCinco)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `CINCO`,
                SUM((CASE
                    WHEN 
                        (`p`.`year` = anioSeis)
                        AND (`p`.`period_number` = mesSeis)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `SEIS`,
                SUM((CASE
                    WHEN 
                        (`p`.`year` = anioSiete)
                        AND (`p`.`period_number` = mesSiete)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `SIETE`,
                SUM((CASE
                    WHEN 
                        (`p`.`year` = anioOcho)
                        AND (`p`.`period_number` = mesOcho)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `OCHO`,
                SUM((CASE
                    WHEN 
                        (`p`.`year` = anioNueve)
                        AND (`p`.`period_number` = mesNueve)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `NUEVE`,
                SUM((CASE
                    WHEN 
                        (`p`.`year` = anioDiez)
                        AND (`p`.`period_number` = mesDiez)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `DIEZ`,
                SUM((CASE
                    WHEN 
                        (`p`.`year` = anioOnce)
                        AND (`p`.`period_number` = mesOnce)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `ONCE`,
                SUM((CASE
                    WHEN 
                        (`p`.`year` = anioDoce)
                        AND (`p`.`period_number` = mesDoce)
                            THEN `pld`.`tax_amount`
                    ELSE 0
                END)) AS `DOCE`

    FROM
        (((((((`period_liquidation_details` `pld`
        LEFT JOIN `persons` `b` ON ((`pld`.`person_id` = `b`.`id`)))
        LEFT JOIN `person_liquidators` ON ((`b`.`id` = `person_liquidators`.`person_id`)))
        LEFT JOIN `liquidators` ON ((`person_liquidators`.`liquidator_id` = `liquidators`.`id`)))
        LEFT JOIN `person_configurations` ON ((`pld`.`person_id` = `person_configurations`.`person_id`)))
        LEFT JOIN `periods` `p` ON ((`pld`.`period_id` = `p`.`id`)))
        LEFT JOIN `period_liquidations` `pl` ON ((`pld`.`period_liquidation_id` = `pl`.`id`)))
        LEFT JOIN `zones` `z` ON ((`pld`.`zone_id` = `z`.`id`)))
    WHERE
        `pld`.person_id = person_id
    ORDER BY `p`.`code` , `b`.`field_name1`;
          END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `liquidacionesIIBB` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `liquidacionesIIBB`(person_id int)
BEGIN
        
            SELECT 
                `pl`.`status` AS `Status`,
                `pld`.`id` AS `IdLiquidacionIIBB`,
                `pld`.`period_liquidation_id` AS `period_liquidation_id`,
                `liquidators`.`name` AS `liquidador`,
                `pld`.`period_id` AS `IdPeriodo`,
                `pld`.`person_id` AS `IdEmpresa`,
                `b`.`document` AS `DocumentoEmpresa`,
                `b`.`field_name1` AS `NombreEmpresa`,
                `p`.`year` AS `anio`,
                `p`.`period_number` AS `mes`,
                `p`.`date_from` AS `Desde`,
                `p`.`date_to` AS `Hasta`,
                `p`.`code` AS `Periodo`,
                `z`.`name` AS `Zona`,
                `pld`.`coef` AS `coeficiente`,
                `pld`.`aliquot` AS `alicuota`,
                `pld`.`base_amount` AS `baseImponible`,
                `pld`.`tax_amount` AS `impuesto`,
                `pld`.`previous_period_balance` AS `saldoAnterior`,
                `pld`.`sircreb_amount` AS `sircreb`,
                `pld`.`perception_amount` AS `percepciones`,
                `pld`.`retention_amount` AS `retenciones`,
                (SELECT 
                        `plia`.`negative_amount`
                    FROM
                        `period_liquidation_details` `plia`
                    WHERE
                        ((`pld`.`zone_id` = `plia`.`zone_id`)
                            AND (`plia`.`period_id` = (SELECT 
                                `pa`.`id`
                            FROM
                                `periods` `pa`
                            WHERE
                                ((`pa`.`person_id` = `b`.`id`)
                                    AND (`pa`.`period_number` = (CASE
                                    WHEN (`p`.`period_number` > 1) THEN (`p`.`period_number` - 1)
                                    ELSE 12
                                END))
                                    AND (`pa`.`year` = (CASE
                                    WHEN (`p`.`period_number` = 1) THEN (`p`.`year` - 1)
                                    ELSE `p`.`year`
                                END))))))) AS `A_PAGAR_ANTERIOR`,
                `pld`.`negative_amount` AS `A_Pagar`,
                `pld`.`positive_amount` AS `A_Favor`
            FROM
                (((((((`period_liquidation_details` `pld`
                LEFT JOIN `persons` `b` ON ((`pld`.`person_id` = `b`.`id`)))
                LEFT JOIN `person_liquidators` ON ((`b`.`id` = `person_liquidators`.`person_id`)))
                LEFT JOIN `liquidators` ON ((`person_liquidators`.`liquidator_id` = `liquidators`.`id`)))
                LEFT JOIN `person_configurations` ON ((`pld`.`person_id` = `person_configurations`.`person_id`)))
                LEFT JOIN `periods` `p` ON ((`pld`.`period_id` = `p`.`id`)))
                LEFT JOIN `period_liquidations` `pl` ON ((`pld`.`period_liquidation_id` = `pl`.`id`)))
                LEFT JOIN `zones` `z` ON ((`pld`.`zone_id` = `z`.`id`)))
            WHERE
                `pld`.`person_id` = person_id
            ORDER BY  `p`.`date_from` desc , `b`.`field_name1`;
        
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `liquidacionesIVA` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `liquidacionesIVA`(person_id Int)
BEGIN

SELECT 
        `pl`.`status` AS `Status`,
        `period_liquidation_iva`.`id` AS `IdLiquidacionIVA`,
        `period_liquidation_iva`.`period_liquidation_id` AS `period_liquidation_id`,
        `liquidators`.`name` AS `liquidador`,
        `period_liquidation_iva`.`period_id` AS `IdPeriodo`,
        `period_liquidation_iva`.`person_id` AS `IdEmpresa`,
        `b`.`document` AS `DocumentoEmpresa`,
        `b`.`field_name1` AS `NombreEmpresa`,
        `p`.`date_from` AS `Desde`,
        `p`.`date_to` AS `Hasta`,
        `p`.`code` AS `Periodo`,
        `period_liquidation_iva`.`total_debit` AS `total_debit`,
        `period_liquidation_iva`.`total_credit` AS `total_credit`,
        `period_liquidation_iva`.`previous_technical` AS `previous_technical`,
        `period_liquidation_iva`.`previous_free_availability` AS `previous_free_availability`,
        `period_liquidation_iva`.`retention_amount` AS `retention_amount`,
        `period_liquidation_iva`.`perception_amount` AS `perception_amount`,
        `period_liquidation_iva`.`compensation_amount` AS `compensation_amount`,
        `period_liquidation_iva`.`total_perceptions_retentions` AS `total_perceptions_retentions`,
        `period_liquidation_iva`.`free_availability_balance` AS `free_availability_balance`,
        (SELECT 
                (CASE
                        WHEN ISNULL(`plia`.`negative_amount`) THEN 0
                        ELSE `plia`.`negative_amount`
                    END)
            FROM
                `period_liquidation_iva` `plia`
            WHERE
                (`plia`.`period_id` = (SELECT 
                        `p`.`id`
                    FROM
                        `periods` `p`
                    WHERE
                        ((`p`.`person_id` = `b`.`id`)
                            AND (`p`.`period_number` = (CASE
                            WHEN (`p`.`period_number` > 1) THEN (`p`.`period_number` - 1)
                            ELSE 12
                        END))
                            AND (`p`.`year` = (CASE
                            WHEN (`p`.`period_number` = 1) THEN (`p`.`year` - 1)
                            ELSE `p`.`year`
                        END)))))) AS `A_PAGAR_ANTERIOR`,
        `period_liquidation_iva`.`negative_amount` AS `A_PAGAR`,
        `period_liquidation_iva`.`positive_amount` AS `SaldoTecnicoAFavor`,
        `period_liquidation_iva`.`total_free_availability` AS `SaldoLibreDisponibilidadAFavor`
    FROM
        ((((((`period_liquidation_iva`
        LEFT JOIN `persons` `b` ON ((`period_liquidation_iva`.`person_id` = `b`.`id`)))
        LEFT JOIN `periods` `p` ON ((`period_liquidation_iva`.`period_id` = `p`.`id`)))
        LEFT JOIN `period_liquidations` `pl` ON ((`period_liquidation_iva`.`period_liquidation_id` = `pl`.`id`)))
        LEFT JOIN `person_liquidators` ON ((`b`.`id` = `person_liquidators`.`person_id`)))
        LEFT JOIN `liquidators` ON ((`person_liquidators`.`liquidator_id` = `liquidators`.`id`)))
        LEFT JOIN `person_configurations` ON ((`period_liquidation_iva`.`person_id` = `person_configurations`.`person_id`)))
    WHERE
        `period_liquidation_iva`.`person_id` = person_id
    ORDER BY `p`.`date_from` desc , `b`.`field_name1`;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `liquidacionHijasIIBB` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `liquidacionHijasIIBB`(to_year int, to_month int, madre_id int)
BEGIN
  	declare done int default 0;
  	declare personId, yearPeriod, monthPeriod, zoneId, periodId int;
  	declare positiveAmount, negativeAmount, baseAmount, sircrebAmount, retentionAmount, perceptionAmount, previousPeriodBalance decimal(20,2) default 0;
      declare taxValue, resultValue decimal(20,2) default 0.00;
      declare sifereCoef, iibbAliquot decimal(8,4) default 0.00;
  	declare periodCode, personName varchar(200);
      declare difPerson, difPeriod int default 0;
      declare prevYear, prevMonth, balanceFinal int default 0;
      declare confSettleV int default 0;
      declare posEqu, negEqu, baseEqu decimal(20,2) default 0.00;

       DECLARE currents CURSOR FOR

       select pz.person_id, pe.field_name1, p.code, p.year, p.period_number, pz.zone_id, p.id as period_id, pz.sifere_coef, pz.iibb_aliquot,
   		sum(case when tr.credit=0 and r.type_id=1 and rt.taxable_iibb=2 and r.zone_id=pz.zone_id
   		 and rt.tax_id<1000 then rt.amount else 0 end) as positive_amount,
   		sum(case when tr.credit=1 and r.type_id=1 and rt.taxable_iibb=2 and r.zone_id=pz.zone_id
   		 and rt.tax_id<1000 then rt.amount else 0 end) as negative_amount,
   		sum(case when tr.credit=0 and r.type_id=1 and rt.taxable_iibb=2 and r.zone_id=pz.zone_id
   		 and rt.tax_id<1000 then rt.amount when tr.credit=1 and r.type_id=1 and
   		rt.taxable_iibb=2 and r.zone_id=pz.zone_id and rt.tax_id<1000 then - rt.amount else 0 end) as base_amount,
   		sum(case when r.type_id=4 and r.zone_id=pz.zone_id then r.amount else 0 end) as sircreb_amount,
   		sum(case when r.type_id=3 and ret.zone_id = pz.zone_id then r.amount else 0 end) as retention_amount,
   		sum(case when r.type_id=2 and rt.tax_id>1000 and ot.section='iibb'
   		 and ot.zone_id = pz.zone_id then rt.amount else 0 end) as perception_amount,
   		(select case when positive_amount>0 then positive_amount else 0 end as positive_amount
   		from period_liquidation_details pa  where pa.zone_id=pz.zone_id
   		and pa.period_id = (
   			select id from periods where year = prevYear and period_number = prevMonth
   				and person_id = pz.person_id
               )  ) as previous_period_balance
   		from person_zones as pz
   		 inner join periods as p on pz.person_id = p.person_id and year = to_year and period_number = to_month
   		 inner join persons as pe on pz.person_id = pe.id
 			 inner join person_configurations pc on p.person_id = pc.person_id and pc.obligation_iibb in (2,3,4) and active =2
   		 left join receipts as r on r.person_id_owner = pz.person_id and r.status_id = '1'
   		 left join retention_types as ret on ret.id = r.retention_type_id
   		 left join receipt_types as tr on r.type_receipt_id = tr.id
   		 left join receipt_taxes as rt on r.id = rt.receipt_id
   		 left join other_taxes as ot on rt.tax_id = ot.id
   		 where exists ( select person_id  from user_persons up where pz.person_id = up.person_id
   			and parent_person_id = madre_id)
        and not exists (select id from period_liquidations pl where pz.person_id = pl.person_id and p.id = pl.period_id and apply_to='iibb')
   		 group by 1,2,3,4,5,6,7,8,9
   		 order by 1,2,3,4,5,6,7,8,9;


  		DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  		insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iibb', concat('inicio de la ejecucion de la funcion'), now() );

  		OPEN currents;
  			set resultValue := 0.00;
  			IF to_month>1 THEN
  				set prevYear := to_year;
  				set prevMonth := to_month-1;
  			ELSE
  				set prevYear := to_year-1;
  				set prevMonth := 12;
  			END IF;
  			REPEAT

              FETCH currents INTO personId, personName, periodCode, yearPeriod, monthPeriod, zoneId, periodId, sifereCoef, iibbAliquot,
  			positiveAmount, negativeAmount, baseAmount, sircrebAmount, retentionAmount, perceptionAmount, previousPeriodBalance;

              

  			select settle_calc_by_coef into confSettleV from person_configurations where person_id = personId;

  			


  			
              IF (confSettleV = 1) THEN
  			  select sum(case when tr.credit=0 and r.type_id=1 and rt.taxable_iibb=2 and rt.tax_id<1000 then rt.amount else 0 end) as positive,
                  sum(case when tr.credit=1 and r.type_id=1 and rt.taxable_iibb=2 and rt.tax_id<1000 then rt.amount else 0 end) as negative,
                  sum(case when tr.credit=0 and r.type_id=1 and rt.taxable_iibb=2	and rt.tax_id<1000 then rt.amount when tr.credit=1 and r.type_id=1 and
  				rt.taxable_iibb=2 and rt.tax_id<1000 then - rt.amount else 0 end) as base
                  into posEqu, negEqu, baseEqu
                  from `receipts` as `r`
                   left join `receipt_types` as `tr` on `r`.`type_receipt_id` = `tr`.`id`
                   left join `receipt_taxes` as `rt` on `r`.`id` = `rt`.`receipt_id`
                   left join `retention_types` as `ret` on `ret`.`id` = `r`.`retention_type_id`
  				 where `r`.`person_id_owner` = personId and r.period_id = periodId
  				  and exists (select person_id from person_zones pz where pz.person_id = r.person_id_owner)
                    and `r`.`status_id` = '1' limit 1;

  				

  			  set baseAmount := (IF (baseEqu > 0, baseEqu, baseAmount));

              END IF;

  			 set taxValue := (baseAmount * sifereCoef * (iibbAliquot/100));
               set previousPeriodBalance := (IF (previousPeriodBalance > 0, previousPeriodBalance, 0.00));
               
               
              set resultValue := taxValue - (previousPeriodBalance + sircrebAmount + retentionAmount + perceptionAmount);
  			
               set negativeAmount := (IF (resultValue > 0, resultValue, 0.00));
               set positiveAmount := (IF (resultValue < 0 , resultValue*(-1) ,0.00));
               set balanceFinal := (IF (positiveAmount > 0 , 1 ,2));


  			

              IF NOT done THEN
  			  IF previousPeriodBalance is null THEN
  				set previousPeriodBalance:=0;
  			  END IF;

  			  IF difPerson <> personId and difPeriod <> periodId THEN

  				set difPerson := personId;
  				set difPeriod := periodId;

  				 insert into period_liquidations (apply_to, period_id, person_id, status, created_at, updated_at)
  				 values ('iibb', periodId, personId, 1, now(), now());

  			  END IF;

                insert into period_liquidation_details (`period_liquidation_id`,`period_id`,`person_id`,`zone_id`,`coef`,`aliquot`,`base_amount`,`tax_amount`,
  				`previous_period_balance`,`sircreb_amount`,`perception_amount`,`retention_amount`,`positive_amount`,`negative_amount`,`balance`,`created_at`,`updated_at`)
  				select pl.id, periodId, personId, zoneId, sifereCoef, iibbAliquot, baseAmount, taxValue, previousPeriodBalance, sircrebAmount, perceptionAmount,
  					retentionAmount, positiveAmount, negativeAmount, balanceFinal, now(), now()
                  from period_liquidations pl where apply_to = 'iibb' and period_id = periodId and person_id = personId;


  			END IF;



  			UNTIL done END REPEAT;

  	  CLOSE currents;
  	insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iibb', concat('fin de la ejecucion de la funcion'), now() );
  END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `liquidacionHijasIVA` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `liquidacionHijasIVA`(to_year int, to_month int, madre_id int)
BEGIN
	declare cantidad, paso int default 0;
    declare done boolean default false;
	declare personId, activityId, periodId int;
	declare ordrV, typeV, originV, taxId, percentIva, taxabledAmount decimal(20,2) default 0;
    declare taxName varchar(100) default '';
    declare taxValue, resultValue, debitValue, creditValue  decimal(20,2) default 0.00;
    declare sifereCoef, iibbAliquot decimal(8,4) default 0.00;
	declare periodCode, personName varchar(200);
    declare difPerson int default 0;
    declare prevYear, prevMonth, prevPeriodId int default 0;
    declare preVtechnical, preVfree_availability, technicalV decimal(20,2) default 0.00;
    declare retentionV, perceptionV, compensationV, totalRPV, freeAvBalV, negativeV, positiveV, totalFAV  decimal(20,2) default 0.00;
    declare periodLiquidationId, periodLiquidationIvaId int default 0;

     DECLARE currents CURSOR FOR

     select up.person_id, pa.activity_id, p.id as period_id,
 			sum(case when r.type_id=2 and tr.credit=0 and rt.tax_id<1000
 			then (rt.amount*st.percent_iva)/100 else 0 end) as creditValue,
 			sum(case when r.type_id=1 and tr.credit=0 and rt.tax_id<1000
 			then (rt.amount*st.percent_iva)/100 else 0 end) as debitValue
 			from
 			  user_persons up
 			 inner join person_activities as pa on pa.person_id = up.person_id
 			 inner join periods p on up.person_id = p.person_id
 				and year = to_year and period_number = to_month and p.person_id = pa.person_id
			 inner join person_configurations pc on p.person_id = pc.person_id and pc.obligation_iva=1  and active =2
 			 left join receipts as r on  r.status_id = '1' and r.period_id = p.id
 			 left join receipt_types as tr on r.type_receipt_id = tr.id
 			 left join receipt_taxes as rt on r.id = rt.receipt_id
 			 left join system_taxes as st on st.id=rt.tax_id
 			 where up.parent_person_id = madre_id
 			 
         and not exists (select id from period_liquidations pl where p.person_id = pl.person_id and p.id = pl.period_id and apply_to='iva')
 			 group by 1,2,3

 			 order by 1,2,3, 4 desc;


		
		

		insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva', concat('inicio de la ejecucion de la funcion'), now() );

		OPEN currents;
			select FOUND_ROWS() into cantidad ;

           

			IF to_month>1 THEN
				set prevYear := to_year;
				set prevMonth := to_month-1;
			ELSE
				set prevYear := to_year-1;
				set prevMonth := 12;
			END IF;

            WHILE paso < cantidad DO
           

            FETCH currents INTO personId, activityId, periodId, creditValue, debitValue;

            IF NOT done THEN
				set paso := paso +1;

			
            set prevPeriodId := null;
			select id into prevPeriodId from periods p
                    where p.person_id = personId
                    and year = prevYear and period_number = prevMonth;

			

            IF(prevPeriodId is not null) THEN
			
             select positive_amount, total_free_availability  INTO preVtechnical, preVfree_availability
				from period_liquidation_iva where person_id = personId and period_id=prevPeriodId;
            END IF;



			  set preVtechnical := (IF (preVtechnical is not null, preVtechnical, 0.00));
              set preVfree_availability := (IF (preVfree_availability is not null, preVfree_availability, 0.00));


			  

				 insert into period_liquidations (apply_to, period_id, person_id, status, created_at, updated_at)
				 values ('iva', periodId, personId, 1, now(), now());
                 SET periodLiquidationId = LAST_INSERT_ID();


              
				   set preVtechnical = If(preVtechnical is not null, preVtechnical, 0);
				   set technicalV = (creditValue + preVtechnical - debitValue);
				   set preVfree_availability = If(preVfree_availability is not null, preVfree_availability, 0);

				   set retentionV := If (retentionV is not null, retentionV, 0);
				   set perceptionV := If (perceptionV is not null, perceptionV, 0);
				   set compensationV := If (compensationV is not null, compensationV, 0);
				   set totalRPV := (retentionV + perceptionV);
				   set freeAvBalV := totalRPV + preVfree_availability - compensationV;

				   set negativeV := IF(technicalV+freeAvBalV>0, 0, (technicalV+freeAvBalV)*(-1));
				   set positiveV := IF(technicalV>0, technicalV, 0);
				   set totalFAV := IF((technicalV+freeAvBalV>0), IF ((technicalV>0), freeAvBalV, (technicalV+freeAvBalV)) , 0);

                 insert into period_liquidation_iva (period_id, person_id, period_liquidation_id, created_at, updated_at
					, total_debit, total_credit, previous_technical, technical_balance, previous_free_availability
					, retention_amount, perception_amount, compensation_amount, total_perceptions_retentions, free_availability_balance
					, negative_amount, positive_amount, total_free_availability)
				 values (periodId, personId, periodLiquidationId, now(), now()
					, debitValue, creditValue, preVtechnical, technicalV, preVfree_availability
                    , retentionV, perceptionV, compensationV, totalRPV, freeAvBalV
                    , negativeV, positiveV, totalFAV);
                 SET periodLiquidationIvaId = LAST_INSERT_ID();


            


             insert into period_liquidation_iva_details (period_liquidation_id,period_liquidation_iva_id,period_id,
				person_id, activity_id, type, tax_id, taxabled_amount, iva_amount, created_at, updated_at, ordr)

				(select periodLiquidationId, periodLiquidationIvaId, periodId, personId
					, pa.activity_id, 2 as type, st.id,
					sum(case when r.type_id=2 and tr.credit=0 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) as taxabled_amount,
					(sum(case when r.type_id=2 and tr.credit=0 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) * st.percent_iva) / 100 as tax_value, now(), now(), 6 as ordr
					from `system_taxes` as `st` cross join `person_activities` as `pa` left join `receipts` as `r` on `r`.`period_id` = periodID and `r`.`status_id` = '1' left join `receipt_types` as `tr` on `r`.`type_receipt_id` = `tr`.`id` left join `receipt_taxes` as `rt` on `r`.`id` = `rt`.`receipt_id` where `pa`.`person_id` = personId group by 1,2,3,4,5,6,7,10,11,12)
                    union
				(select periodLiquidationId, periodLiquidationIvaId, periodId, personId
					, pa.activity_id, 1 as type, st.id,
					sum(case when r.type_id=2 and tr.credit=1 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) as taxabled_amount,
                    (sum(case when r.type_id=2 and tr.credit=1 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) * st.percent_iva) / 100 as tax_value, now(), now(), 4 as ordr
					from `system_taxes` as `st` cross join `person_activities` as `pa` left join `receipts` as `r` on `r`.`period_id` = periodId and `r`.`status_id` = '1' left join `receipt_types` as `tr` on `r`.`type_receipt_id` = `tr`.`id` left join `receipt_taxes` as `rt` on `r`.`id` = `rt`.`receipt_id` where `pa`.`person_id` = personId group by 1,2,3,4,5,6,7,10,11,12)
                    union
				(select periodLiquidationId, periodLiquidationIvaId, periodId, personId
					, pa.activity_id, 2 as type, st.id,
					sum(case when r.type_id=1 and tr.credit=1 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) as taxabled_amount,
                    (sum(case when r.type_id=1 and tr.credit=1 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) * st.percent_iva) /100 as tax_value, now(), now(), 5 as ordr
					from `system_taxes` as `st` cross join `person_activities` as `pa` left join `receipts` as `r` on `r`.`period_id` = periodId and `r`.`status_id` = '1' left join `receipt_types` as `tr` on `r`.`type_receipt_id` = `tr`.`id` left join `receipt_taxes` as `rt` on `r`.`id` = `rt`.`receipt_id` where `pa`.`person_id` = personId group by 1,2,3,4,5,6,7,10,11,12)
					union
				(select periodLiquidationId, periodLiquidationIvaId, periodId, personId
					, pa.activity_id, 1 as type, st.id,
					sum(case when r.type_id=1 and tr.credit=0 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) as taxabled_amount,
                    (sum(case when r.type_id=1 and tr.credit=0 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) * st.percent_iva) / 100 as tax_value, now(), now(), 1 as ordr
					from `system_taxes` as `st` cross join `person_activities` as `pa` left join `receipts` as `r` on `r`.`period_id` = periodId and `r`.`status_id` = '1' left join `receipt_types` as `tr` on `r`.`type_receipt_id` = `tr`.`id` and `tr`.`agroup_iva_module` = '1' left join `receipt_taxes` as `rt` on `r`.`id` = `rt`.`receipt_id` where `pa`.`person_id` = personId and `st`.`id` not in ('6', '7') group by 1,2,3,4,5,6,7,10,11,12)
                    union
				(select periodLiquidationId, periodLiquidationIvaId, periodId, personId
					, pa.activity_id, 1 as type, st.id,
					sum(case when r.type_id=1 and tr.credit=0 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) as taxabled_amount,
                    (sum(case when r.type_id=1 and tr.credit=0 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) * st.percent_iva) / 100 as tax_value, now(), now(), 2 as ordr
					from `system_taxes` as `st` cross join `person_activities` as `pa` left join `receipts` as `r` on `r`.`period_id` = periodId and `r`.`status_id` = '1' left join `receipt_types` as `tr` on `r`.`type_receipt_id` = `tr`.`id` and `tr`.`agroup_iva_module` = '2' left join `receipt_taxes` as `rt` on `r`.`id` = `rt`.`receipt_id` where `pa`.`person_id` = personId and `st`.`id` not in ('6', '7') group by 1,2,3,4,5,6,7,10,11,12)
                    union
				(select  periodLiquidationId, periodLiquidationIvaId, periodId, personId
					, pa.activity_id, 1 as type, st.id,
					sum(case when r.type_id=1 and tr.credit=0 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) as taxabled_amount,
                    (sum(case when r.type_id=1 and tr.credit=0 and rt.tax_id<1000 and st.id=rt.tax_id
					then rt.amount else 0 end) * st.percent_iva) / 100 as tax_value, now(), now(), 3 as ordr
					from `system_taxes` as `st` cross join `person_activities` as `pa` left join `receipts` as `r` on `r`.`period_id` = periodId and `r`.`status_id` = '1' left join `receipt_types` as `tr` on `r`.`type_receipt_id` = `tr`.`id` left join `receipt_taxes` as `rt` on `r`.`id` = `rt`.`receipt_id` where `pa`.`person_id` = personId and `st`.`id` in ('6', '7') group by 1,2,3,4,5,6,7,10,11,12)
				order by 12, 6

					;

				END IF;



			
            END WHILE;

	  CLOSE currents;
	insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva', concat('fin de la ejecucion de la funcion'), now() );
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `monotributistas` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `monotributistas`(operacion int,anioUno int,anioDos int,anioTres int,anioCuatro int,anioCinco int,anioSeis int,anioSiete int,anioOcho int,anioNueve int,anioDiez int,anioOnce int,anioDoce int,mesUno int,mesDos int,mesTres int,mesCuatro int,mesCinco int,mesSeis int,mesSiete int,mesOcho int,
        mesNueve int,mesDiez int,mesOnce int,mesDoce int, person_id varchar(50))
BEGIN
        
            SELECT 
                `types`.`id` AS `IdOperacion`,
                `types`.`name` AS `Operacion`,
                `receipts`.`person_id_owner` AS `person_id`,
                `b`.`document` AS `cuit`,
                `b`.`field_name1` AS `empresa`,
                vpi.impuesto_id,vpi.importe,m.categoria,m.facAnual,
                
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioUno)
                        AND (`p`.`period_number` = mesUno)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `UNO`,
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioDos)
                        AND (`p`.`period_number` = mesDos)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `DOS`,
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioTres)
                        AND (`p`.`period_number` = mesTres)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `TRES`,
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioCuatro)
                        AND (`p`.`period_number` = mesCuatro)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `CUATRO`,
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioCinco)
                        AND (`p`.`period_number` = mesCinco)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `CINCO`,
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioSeis)
                        AND (`p`.`period_number` = mesSeis)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `SEIS`,
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioSiete)
                        AND (`p`.`period_number` = mesSiete)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `SIETE`,
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioOcho)
                        AND (`p`.`period_number` = mesOcho)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `OCHO`,
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioNueve)
                        AND (`p`.`period_number` = mesNueve)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `NUEVE`,
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioDiez)
                        AND (`p`.`period_number` = mesDiez)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `DIEZ`,
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioOnce)
                        AND (`p`.`period_number` = mesOnce)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `ONCE`,
                SUM((CASE
                    WHEN
                        (`vpi`.`vto_id` = 37)
                        AND (`p`.`year` = anioDoce)
                        AND (`p`.`period_number` = mesDoce)
                    THEN
                        ((CASE
                            WHEN (`receipts`.`type_receipt_id` = 9) THEN -(1)
                            ELSE 1
                        END) * `receipts`.`amount`)
                    ELSE 0
                END)) AS `DOCE`
            FROM
                `receipts`
                LEFT JOIN `persons` `b` ON `receipts`.`person_id_owner` = `b`.`id`
                LEFT JOIN `periods` `p` ON `receipts`.`period_id` = `p`.`id`
                LEFT JOIN `receipt_types` ON `receipts`.`type_receipt_id` = `receipt_types`.`id`
                LEFT JOIN `status` ON `receipts`.`status_id` = `status`.`id`
                LEFT JOIN `persons` ON `receipts`.`person_id_relationed` = `persons`.`id`
                LEFT JOIN `types` ON `receipts`.`type_id` = `types`.`id`
                
                join vto_person_impuesto as vpi on b.id= vpi.person_id
                inner join vto_categoria_imp as vci on vpi.categoria_imp_id = vci.id
                inner join categorias_monotributo as m on vci.cat_mono_id = m.id
                
            WHERE receipts.type_id = operacion 
                and vpi.impuesto_id = 2
                and receipts.person_id_owner like person_id
                and receipts.deleted_at is null
                
            group by 1,2,3,4,5,6,7,8,9
            ORDER BY empresa;
             END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `procedureComprobantes` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `procedureComprobantes`(
          in operacion varchar(35),
          in periodoID varchar(35),
          in periodo varchar(20),
          in empresa varchar(20),
          in date_from varchar(20),
          in date_to varchar(20)
        )
BEGIN

        SELECT
   receipts.id as IdComprobante,
     persons.id as IdProveedorCliente,
     receipts.person_id_owner as IdEmpresa,
     b.document as DocumentoEmpresa,
     b.field_name1 as NombreEmpresa,
     periods.id as IdPeriodo,
   periods.date_from as Desde,
     periods.date_to as Hasta,
   periods.code as Periodo,
     types.name as Operacion,
   persons.field_name1 as Proveedor,
     persons.document_type as TipoDocumento,
     persons.document as Documento,
     zones.name as Zona,
     status.name as Estado,
     activities.name as Actividad,
     receipts.receipt_date as FechaComprobante,
     receipts.reference as Referencia,
     retention_types.name as TipoDeRetencion,
     receipt_types.name as TipoComprobante,
     receipts.code_ticket as Suc,
     receipts.number as Numero,
     receipts.amount as Total,
   sum(case when receipt_taxes.tax_id=3 then (receipt_taxes.amount) else 0 end) as gravado_27,
   sum(case when receipt_taxes.tax_id=3 then (receipt_taxes.tax_amount) else 0 end) as IVA_27,
     sum(case when receipt_taxes.tax_id=2 then (receipt_taxes.amount) else 0 end) as gravado_21,
   sum(case when receipt_taxes.tax_id=2 then (receipt_taxes.tax_amount) else 0 end) as IVA_21,
   sum(case when receipt_taxes.tax_id=1 then (receipt_taxes.amount) else 0 end) as gravado_105,
   sum(case when receipt_taxes.tax_id=1 then (receipt_taxes.tax_amount) else 0 end) as IVA_105,
     sum(case when receipt_taxes.tax_id=5 then (receipt_taxes.amount) else 0 end) as gravado_5,
   sum(case when receipt_taxes.tax_id=5 then (receipt_taxes.tax_amount) else 0 end) as IVA_5,
     sum(case when receipt_taxes.tax_id=6 then (receipt_taxes.amount) else 0 end) as gravado_0,
   sum(case when receipt_taxes.tax_id=6 then (receipt_taxes.tax_amount) else 0 end) as IVA_0,
     sum(case when receipt_taxes.tax_id=4 then (receipt_taxes.amount) else 0 end) as gravado_2_5,
   sum(case when receipt_taxes.tax_id=4 then (receipt_taxes.tax_amount) else 0 end) as IVA_2_5,
     sum(case when receipt_taxes.tax_id=7 then (receipt_taxes.amount) else 0 end) as no_gravado,
     sum(case when receipt_taxes.tax_id=1000 then (receipt_taxes.amount) else 0 end) as percepIva,
     sum(case when receipt_taxes.tax_id=1001 then (receipt_taxes.amount) else 0 end) as Imps_Internos,
   sum(case when receipt_taxes.tax_id=1002 then (receipt_taxes.amount) else 0 end) as Imp_Gasoil,
   sum(case when receipt_taxes.tax_id=1003 then (receipt_taxes.amount) else 0 end) as Imp_Hidricos,
   sum(case when receipt_taxes.tax_id=1004 then (receipt_taxes.amount) else 0 end) as ITC,
   sum(case when receipt_taxes.tax_id=1005 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Buenos_Aires,
   sum(case when receipt_taxes.tax_id=1006 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Capital_Federal,
   sum(case when receipt_taxes.tax_id=1007 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Catamarca,
   sum(case when receipt_taxes.tax_id=1008 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Chaco,
   sum(case when receipt_taxes.tax_id=1009 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Chubut,
   sum(case when receipt_taxes.tax_id=1010 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Cordoba,
   sum(case when receipt_taxes.tax_id=1011 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Corrientes,
   sum(case when receipt_taxes.tax_id=1012 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Entre_Rios,
   sum(case when receipt_taxes.tax_id=1013 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Formosa,
   sum(case when receipt_taxes.tax_id=1014 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Jujuy,
   sum(case when receipt_taxes.tax_id=1015 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_La_Pampa,
   sum(case when receipt_taxes.tax_id=1016 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_La_Rioja,
   sum(case when receipt_taxes.tax_id=1017 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Mendoza,
   sum(case when receipt_taxes.tax_id=1018 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Misiones,
   sum(case when receipt_taxes.tax_id=1019 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Neuquen,
   sum(case when receipt_taxes.tax_id=1020 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Pascua_Lama,
   sum(case when receipt_taxes.tax_id=1021 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Rio_Negro,
   sum(case when receipt_taxes.tax_id=1022 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Salta,
   sum(case when receipt_taxes.tax_id=1023 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_San_Juan,
   sum(case when receipt_taxes.tax_id=1024 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_San_Luis,
   sum(case when receipt_taxes.tax_id=1025 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Santa_Cruz,
   sum(case when receipt_taxes.tax_id=1026 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Santa_Fe,
   sum(case when receipt_taxes.tax_id=1027 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Santiago_del_Estero,
   sum(case when receipt_taxes.tax_id=1028 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Tierra_del_Fuego,
   sum(case when receipt_taxes.tax_id=1029 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Tucuman

 from receipts
 left join persons as b on receipts.person_id_owner = b.id
 left join periods on receipts.period_id = periods.id
 left join receipt_types on receipts.type_receipt_id = receipt_types.id
 left join retention_types on receipts.retention_type_id =retention_types.id
 left join status on receipts.status_id = status.id
 left join persons on receipts.person_id_relationed = persons.id
 left join receipt_taxes on receipts.id = receipt_taxes.receipt_id
 left join zones on receipts.zone_id = zones.id
 left join activities on receipts.activity_id = activities.id
 left join types on receipts.type_id = types.id
 left join system_taxes on  receipt_taxes.tax_id = system_taxes.id
 left join other_taxes on  receipt_taxes.tax_id = other_taxes.id

 where 	receipts.type_id LIKE operacion
   and periods.id LIKE '%'
     and periods.code LIKE periodo
   and receipts.person_id_owner LIKE empresa
   and periods.date_from LIKE '%'
     and periods.date_to LIKE '%'

 group by  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23
 order by Periodo,persons.document, code_ticket,number
        ;
        END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `procedure_eliminar_comprobantes_masivamente` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `procedure_eliminar_comprobantes_masivamente`(

in operacion varchar(35),
in empresa varchar(20),
in desde date,
in hasta date

)
BEGIN


DELETE from receipt_taxes where id IN
    (
    SELECT * FROM
        (
        SELECT receipt_taxes.id as eliminar
        FROM receipt_taxes
        WHERE receipt_taxes.receipt_id IN
            (
            
            SELECT IdComprobante
                from
                (
               
                SELECT
                    receipts.id as IdComprobante,
                    persons.id as IdProveedorCliente,
                    receipts.person_id_owner as Empresa,
                    periods.id as IdPeriodo,
                    periods.date_from as Desde,
                    periods.date_to as Hasta,
                    periods.code as Periodo,
                    types.name as Operacion,
                    persons.field_name1 as Proveedor,
                    receipts.number as Numero,
                    receipt_taxes.id as IDreceipt_taxes

                from receipts
                left join periods on receipts.period_id = periods.id
                left join receipt_types on receipts.type_receipt_id = receipt_types.id
                left join retention_types on receipts.retention_type_id =retention_types.id
                left join status on receipts.status_id = status.id
                left join persons on receipts.person_id_relationed = persons.id
                left join receipt_taxes on receipts.id = receipt_taxes.receipt_id
                left join zones on receipts.zone_id = zones.id
                left join activities on receipts.activity_id = activities.id
                left join types on receipts.type_id = types.id
                left join system_taxes on  receipt_taxes.tax_id = system_taxes.id
                left join other_taxes on  receipt_taxes.tax_id = other_taxes.id

                where   receipts.type_id LIKE operacion
                    and receipts.person_id_owner LIKE empresa
                    and periods.date_from between desde and hasta

                group by  1,2,3,4,5,6,7,8,9,10,11
                )foo
            )
        )
        as tablanueva
    );


        DELETE from receipts where id IN
            (
            
            SELECT IdComprobante
                from
                (
               
                SELECT
                    receipts.id as IdComprobante,
                    persons.id as IdProveedorCliente,
                    receipts.person_id_owner as Empresa,
                    periods.id as IdPeriodo,
                    periods.date_from as Desde,
                    periods.date_to as Hasta,
                    periods.code as Periodo,
                    types.name as Operacion,
                    persons.field_name1 as Proveedor,
                    receipts.number as Numero,
                    receipt_taxes.id as IDreceipt_taxes

                from receipts
                left join periods on receipts.period_id = periods.id
                left join receipt_types on receipts.type_receipt_id = receipt_types.id
                left join retention_types on receipts.retention_type_id =retention_types.id
                left join status on receipts.status_id = status.id
                left join persons on receipts.person_id_relationed = persons.id
                left join receipt_taxes on receipts.id = receipt_taxes.receipt_id
                left join zones on receipts.zone_id = zones.id
                left join activities on receipts.activity_id = activities.id
                left join types on receipts.type_id = types.id
                left join system_taxes on  receipt_taxes.tax_id = system_taxes.id
                left join other_taxes on  receipt_taxes.tax_id = other_taxes.id

                where   receipts.type_id LIKE operacion
                    and receipts.person_id_owner LIKE empresa
                    and periods.date_from between desde and hasta

                group by  1,2,3,4,5,6,7,8,9,10,11
                )foo
            )
    ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proyecGanancias` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `proyecGanancias`(anioUno int,anioDos int,anioTres int,anioCuatro int,anioCinco int,anioSeis int,anioSiete int,anioOcho int,anioNueve int,anioDiez int,anioOnce int,anioDoce int,mesUno int,mesDos int,mesTres int,mesCuatro int,mesCinco int,mesSeis int,mesSiete int,mesOcho int,mesNueve int,mesDiez int,mesOnce int,mesDoce int,person_id int)
BEGIN
            SELECT 
                `types`.`id` AS `IdOperacion`,
                `types`.`name` AS `Operacion`,
                `receipts`.`person_id_owner` AS `IdEmpresa`,
                `b`.`document` AS `DocumentoEmpresa`,
                `b`.`field_name1` AS `NombreEmpresa`,
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioUno)
                            AND (`p`.`period_number` = mesUno))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'UNO',
        
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioDos)
                            AND (`p`.`period_number` = mesDos))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'DOS',
        
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioTres)
                            AND (`p`.`period_number` = mesTres))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'TRES',
        
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioCuatro)
                            AND (`p`.`period_number` = mesCuatro))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'CUATRO',
        
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioCinco)
                            AND (`p`.`period_number` = mesCinco))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'CINCO',
        
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioSeis)
                            AND (`p`.`period_number` = mesSeis))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'SEIS',
        
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioSiete)
                            AND (`p`.`period_number` = mesSiete))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'SIETE',
        
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioOcho)
                            AND (`p`.`period_number` = mesOcho))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'OCHO',
        
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioNueve)
                            AND (`p`.`period_number` = mesNueve))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'NUEVE',
        
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioDiez)
                            AND (`p`.`period_number` = mesDiez))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'DIEZ',
        
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioOnce)
                            AND (`p`.`period_number` = mesOnce))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'ONCE',
        
                SUM((CASE
                    WHEN
                        (((`receipt_taxes`.`tax_id` = 3)
                            OR (`receipt_taxes`.`tax_id` = 2)
                            OR (`receipt_taxes`.`tax_id` = 1)
                            OR (`receipt_taxes`.`tax_id` = 7)
                            OR (`receipt_taxes`.`tax_id` = 6)
                            OR (`receipt_taxes`.`tax_id` = 5)
                            OR (`receipt_taxes`.`tax_id` = 4))
                            AND (`p`.`year` = anioDoce)
                            AND (`p`.`period_number` = mesDoce))
                    THEN
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * `receipt_taxes`.`amount`)
                    ELSE 0
                END)) AS 'DOCE'
                
            FROM
                (((((((((`receipts`
                LEFT JOIN `persons` `b` ON ((`receipts`.`person_id_owner` = `b`.`id`)))
                LEFT JOIN `periods` `p` ON ((`receipts`.`period_id` = `p`.`id`)))
                LEFT JOIN `receipt_types` ON ((`receipts`.`type_receipt_id` = `receipt_types`.`id`)))
                LEFT JOIN `status` ON ((`receipts`.`status_id` = `status`.`id`)))
                LEFT JOIN `persons` ON ((`receipts`.`person_id_relationed` = `persons`.`id`)))
                LEFT JOIN `receipt_taxes` ON ((`receipts`.`id` = `receipt_taxes`.`receipt_id`)))
                LEFT JOIN `types` ON ((`receipts`.`type_id` = `types`.`id`)))
                LEFT JOIN `system_taxes` ON ((`receipt_taxes`.`tax_id` = `system_taxes`.`id`)))
                LEFT JOIN `other_taxes` ON ((`receipt_taxes`.`tax_id` = `other_taxes`.`id`)))
            WHERE `receipts`.`person_id_owner` = person_id
                and (`types`.`id` IN (1 , 2))
            GROUP BY `types`.`id` , `types`.`name` , `receipts`.`person_id_owner` , `b`.`document` , `b`.`field_name1`
            ORDER BY `types`.`id` , `p`.`year` , `receipts`.`person_id_owner`
            
            ;
        
        
          END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `receiptsForTXTIIBB` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `receiptsForTXTIIBB`(person_id int,
        IdTipoComprobante int,IdPeriodo int)
BEGIN
                SELECT 
                        `receipts`.`id` AS `IdComprobante`,
                        `receipts`.`type_id` AS `IdTipoComprobante`,
                        `persons`.`id` AS `IdProveedorCliente`,
                        `receipts`.`person_id_owner` AS `IdEmpresa`,
                        `b`.`document` AS `DocumentoEmpresa`,
                        `b`.`field_name1` AS `NombreEmpresa`,
                        `p`.`id` AS `IdPeriodo`,
                        `p`.`date_from` AS `Desde`,
                        `p`.`date_to` AS `Hasta`,
                        `p`.`code` AS `Periodo`,
                        `p`.`year` AS `anioPeriodo`,
                        `p`.`period_number` AS `mesPeriodo`,
                        `types`.`id` AS `IdOperacion`,
                        `types`.`name` AS `Operacion`,
                        `persons`.`field_name1` AS `Proveedor`,
                        `persons`.`document_type` AS `TipoDocumento`,
                        `persons`.`document` AS `Documento`,
                        `zones`.`name` AS `Zona`,
                        `status`.`name` AS `Estado`,
                        `activities`.`name` AS `Actividad`,
                        DATE_FORMAT(`receipts`.`receipt_date`,'%d/%m/%Y') AS FechaComprobante,
                        `receipts`.`reference` AS `Referencia`,
                        `retention_types`.`name` AS `TipoDeRetencion`,
                        `receipt_types`.`name` AS `TipoComprobante`,
                        `receipts`.`code_ticket` AS `Suc`,
                        `receipts`.`number` AS `Numero`,
                        (CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(`receipts`.`amount`)
                            ELSE `receipts`.`amount`
                        END) AS `Total`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 3) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `gravado_27`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 3) THEN `receipt_taxes`.`tax_amount`
                            ELSE 0
                        END))) AS `IVA_27`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 2) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `gravado_21`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 2) THEN `receipt_taxes`.`tax_amount`
                            ELSE 0
                        END))) AS `IVA_21`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `gravado_105`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1) THEN `receipt_taxes`.`tax_amount`
                            ELSE 0
                        END))) AS `IVA_105`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 5) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `gravado_5`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 5) THEN `receipt_taxes`.`tax_amount`
                            ELSE 0
                        END))) AS `IVA_5`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 6) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `gravado_0`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 6) THEN `receipt_taxes`.`tax_amount`
                            ELSE 0
                        END))) AS `IVA_0`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 4) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `gravado_2_5`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 4) THEN `receipt_taxes`.`tax_amount`
                            ELSE 0
                        END))) AS `IVA_2_5`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 7) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `no_gravado`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1000) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `percepIva`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1001) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Imps_Internos`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1002) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Imp_Gasoil`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1003) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Imp_Hidricos`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1004) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `ITC`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1005) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Buenos_Aires`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1006) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Capital_Federal`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1007) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Catamarca`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1008) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Chaco`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1009) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Chubut`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1010) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Cordoba`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1011) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Corrientes`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1012) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Entre_Rios`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1013) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Formosa`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1014) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Jujuy`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1015) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_La_Pampa`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1016) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_La_Rioja`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1017) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Mendoza`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1018) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Misiones`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1019) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Neuquen`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1020) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Pascua_Lama`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1021) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Rio_Negro`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1022) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Salta`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1023) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_San_Juan`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1024) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_San_Luis`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1025) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Santa_Cruz`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1026) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Santa_Fe`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1027) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Santiago_del_Estero`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1028) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Tierra_del_Fuego`,
                        ((CASE
                            WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                            ELSE 1
                        END) * SUM((CASE
                            WHEN (`receipt_taxes`.`tax_id` = 1029) THEN `receipt_taxes`.`amount`
                            ELSE 0
                        END))) AS `Perc_IIBB_Tucuman`
                    FROM
                        ((((((((((((`receipts`
                        LEFT JOIN `persons` `b` ON ((`receipts`.`person_id_owner` = `b`.`id`)))
                        LEFT JOIN `periods` `p` ON ((`receipts`.`period_id` = `p`.`id`)))
                        LEFT JOIN `receipt_types` ON ((`receipts`.`type_receipt_id` = `receipt_types`.`id`)))
                        LEFT JOIN `retention_types` ON ((`receipts`.`retention_type_id` = `retention_types`.`id`)))
                        LEFT JOIN `status` ON ((`receipts`.`status_id` = `status`.`id`)))
                        LEFT JOIN `persons` ON ((`receipts`.`person_id_relationed` = `persons`.`id`)))
                        LEFT JOIN `receipt_taxes` ON ((`receipts`.`id` = `receipt_taxes`.`receipt_id`)))
                        LEFT JOIN `zones` ON ((`receipts`.`zone_id` = `zones`.`id`)))
                        LEFT JOIN `activities` ON ((`receipts`.`activity_id` = `activities`.`id`)))
                        LEFT JOIN `types` ON ((`receipts`.`type_id` = `types`.`id`)))
                        LEFT JOIN `system_taxes` ON ((`receipt_taxes`.`tax_id` = `system_taxes`.`id`)))
                        LEFT JOIN `other_taxes` ON ((`receipt_taxes`.`tax_id` = `other_taxes`.`id`)))
                        where `receipts`.`person_id_owner` = person_id
                        and  `receipts`.`type_id` = IdTipoComprobante
                        and `receipts`.`period_id` = IdPeriodo
                    GROUP BY `receipts`.`id` , `receipts`.`type_id` , `persons`.`id` , `receipts`.`person_id_owner` , `b`.`document` , `b`.`field_name1` , `p`.`id` , `p`.`date_from` , `p`.`date_to` , `p`.`code` , `p`.`year` , `p`.`period_number` , `types`.`id` , `p`.`code` , `types`.`name` , `persons`.`field_name1` , `persons`.`document_type` , `persons`.`document` , `zones`.`name` , `status`.`name` , `activities`.`name` , `receipts`.`receipt_date` , `receipts`.`reference` , `retention_types`.`name` , `receipt_types`.`name` , `receipts`.`code_ticket` , `receipts`.`number` , `receipts`.`amount`
                    ORDER BY `p`.`code` , `persons`.`document` , `receipts`.`code_ticket` , `receipts`.`number`;
                END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `retenciones` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `retenciones`(anioUno int,anioDos int,anioTres int,anioCuatro int,anioCinco int,
        anioSeis int,anioSiete int,anioOcho int,anioNueve int,anioDiez int,anioOnce int,anioDoce int,mesUno int,mesDos int,
        mesTres int,mesCuatro int,mesCinco int,mesSeis int,mesSiete int,mesOcho int,mesNueve int,mesDiez int,mesOnce int,
        mesDoce int, person_id int)
BEGIN
        
        SELECT 
                `types`.`id` AS `IdOperacion`,
                `types`.`name` AS `Operacion`,
                `receipts`.`person_id_owner` AS `IdEmpresa`,
                `b`.`document` AS `DocumentoEmpresa`,
                `b`.`field_name1` AS `NombreEmpresa`,
                SUM((CASE
                    WHEN
                        ((`p`.`year` = anioUno)
                            AND (`p`.`period_number` = mesUno)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `UNO`,
                SUM((CASE
                    WHEN
                       ((`p`.`year` = anioDos)
                            AND (`p`.`period_number` = mesDos)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `DOS`,
                SUM((CASE
                    WHEN
                       ((`p`.`year` = anioTres)
                            AND (`p`.`period_number` = mesTres)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `TRES`,
                SUM((CASE
                    WHEN
                       ((`p`.`year` = anioCuatro)
                            AND (`p`.`period_number` = mesCuatro)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `CUATRO`,
                SUM((CASE
                    WHEN
                       ((`p`.`year` = anioCinco)
                            AND (`p`.`period_number` = mesCinco)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `CINCO`,
                SUM((CASE
                    WHEN
                       ((`p`.`year` = anioSeis)
                            AND (`p`.`period_number` = mesSeis)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `SEIS`,
                SUM((CASE
                    WHEN
                       ((`p`.`year` = anioSiete)
                            AND (`p`.`period_number` = mesSiete)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `SIETE`,
                SUM((CASE
                    WHEN
                       ((`p`.`year` = anioOcho)
                            AND (`p`.`period_number` = mesOcho)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `OCHO`,
                SUM((CASE
                    WHEN
                       ((`p`.`year` = anioNueve)
                            AND (`p`.`period_number` = mesNueve)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `NUEVE`,
                SUM((CASE
                    WHEN
                       ((`p`.`year` = anioDiez)
                            AND (`p`.`period_number` = mesDiez)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `DIEZ`,
                SUM((CASE
                    WHEN
                       ((`p`.`year` = anioOnce)
                            AND (`p`.`period_number` = mesOnce)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `ONCE`,
                SUM((CASE
                    WHEN
                       ((`p`.`year` = anioDoce)
                            AND (`p`.`period_number` = mesDoce)
                            AND (`receipts`.`retention_type_id` = 1))
                    THEN
                        `receipts`.`amount`
                    ELSE 0
                END)) AS `DOCE`
            FROM
                (((((`receipts`
                LEFT JOIN `persons` `b` ON ((`receipts`.`person_id_owner` = `b`.`id`)))
                LEFT JOIN `periods` `p` ON ((`receipts`.`period_id` = `p`.`id`)))
                LEFT JOIN `status` ON ((`receipts`.`status_id` = `status`.`id`)))
                LEFT JOIN `persons` ON ((`receipts`.`person_id_relationed` = `persons`.`id`)))
                LEFT JOIN `types` ON ((`receipts`.`type_id` = `types`.`id`)))
            WHERE
                `types`.`id` = 3
                and receipts.deleted_at is null
                and `receipts`.`person_id_owner`= person_id
            GROUP BY `types`.`id` , `types`.`name` , `receipts`.`person_id_owner` , `b`.`document` , `b`.`field_name1` 
            ORDER BY `types`.`id`, `receipts`.`person_id_owner`
        
        ;
        
          END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPAsientosContables` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `SPAsientosContables`(person_id int, plan_cuenta_id varchar(20), desde date, hasta date)
BEGIN

SELECT 
    `bl`.`id` AS `balance_libro_id`,
    `pe`.`id` AS `period_id`,
    `pe`.`code` AS `periodo`,
    `pe`.`date_from` AS `Desde`,
    `bl`.`receipt_id` AS `receipt_id`,
    `bl`.`nf_cta_cte_id` AS `nf_cta_cte_id`,
    `bl`.`period_liquidation_id` AS `period_liquidation_id`,
    `bl`.`fecha_registracion` AS `fecha`,
    `bl`.`conta_tipo_asiento_id` AS `conta_tipo_asiento_id`,
    `bl`.`cta_cte_cliente_id` AS `cta_cte_cliente_id`,
    `ta`.`nombre` AS `asiento_de`,
    (case 
        when (`pc3`.`tipo_cuenta_id` = 1) then `pc3`.`nombre`
            else `pc4`.`nombre`
    END) AS `Rubro`,
    `bld`.`plan_cuenta_id` AS `plan_cuenta_id`,
    (CASE
        WHEN (`bld`.`saldo_id` = 1) THEN 'DEUDOR'
        ELSE 'ACREEDOR'
    END) AS `SALDO`,
    `bld`.`saldo_id` AS `saldo_id`,
    `bld`.`comentario` AS `comentario`,
    `bld`.`person_id` AS `person_id`,
    `ta`.`nombre` AS `asiento`,
    `pc`.`nombre` AS `cuenta`,
    `p`.`field_name1` AS `empresa`,
    sum((CASE
        WHEN (`bld`.`saldo_id` = 1) THEN `bld`.`monto`
        ELSE 0
    END)) AS `DEBE`,
    sum((CASE
        WHEN (`bld`.`saldo_id` = 2) THEN `bld`.`monto`
        ELSE 0
    END)) AS `HABER`
FROM
    `balance_libro_details` `bld`
    JOIN `balance_libro` `bl` ON `bld`.`balance_libro_id` = `bl`.`id`
    JOIN `conta_tipo_saldos` `ts` ON `bld`.`saldo_id` = `ts`.`id`
    JOIN `persons` `p` ON `bld`.`person_id` = `p`.`id`
    JOIN `periods` `pe` ON `bld`.`period_id` = `pe`.`id`
    JOIN `conta_tipo_asientos` `ta` ON `bl`.`conta_tipo_asiento_id` = `ta`.`id`
    left JOIN `conta_plan_cuenta` `pc` ON `bld`.`plan_cuenta_id` = `pc`.`id`
    left JOIN `conta_plan_cuenta` `pc1` ON `pc`.`padre` = `pc1`.`codigo`
    left JOIN `conta_plan_cuenta` `pc2` ON `pc1`.`padre` = `pc2`.`codigo`
    left JOIN `conta_plan_cuenta` `pc3` ON `pc2`.`padre` = `pc3`.`codigo`
    left JOIN `conta_plan_cuenta` `pc4` ON `pc3`.`padre` = `pc4`.`codigo`
where bl.deleted_at is null
and bld.person_id like person_id
and bld.plan_cuenta_id like plan_cuenta_id
and `pe`.`date_from` between desde and hasta
group by `bl`.`id`,bl.conta_tipo_asiento_id,bld.plan_cuenta_id
ORDER BY `pe`.`date_from` ,`bl`.`id` , `ta`.orden , `bld`.`saldo_id`;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPAsientsSumasYSaldos` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `SPAsientsSumasYSaldos`(person_id int, desde date, hasta date)
BEGIN

    SELECT 
    `pe`.`code` AS `periodo`,
    `pe`.`date_from` AS `Desde`,
    pc4.nombre as nivel1,
    pc3.nombre as nivel2,
    pc2.nombre as nivel3,
    pc1.nombre as nivel4,
    pc.nombre as nivel5,
    pc.tipo_cuenta_id,
    `bld`.`plan_cuenta_id` AS `plan_cuenta_id`,
    `bld`.`person_id` AS `person_id`,
    `pc`.`nombre` AS `cuenta`,
    sum((CASE
        WHEN (`bld`.`saldo_id` = 1) THEN `bld`.`monto`
        WHEN (`bld`.`saldo_id` = 2) THEN `bld`.`monto`*(-1)
    END)) AS `SALDO`
FROM
    `balance_libro_details` `bld`
    JOIN `balance_libro` `bl` ON `bld`.`balance_libro_id` = `bl`.`id`
    JOIN `conta_tipo_saldos` `ts` ON `bld`.`saldo_id` = `ts`.`id`
    JOIN `persons` `p` ON `bld`.`person_id` = `p`.`id`
    JOIN `periods` `pe` ON `bld`.`period_id` = `pe`.`id`
    JOIN `conta_tipo_asientos` `ta` ON `bl`.`conta_tipo_asiento_id` = `ta`.`id`
    left JOIN `conta_plan_cuenta` `pc` ON `bld`.`plan_cuenta_id` = `pc`.`id`
    left JOIN `conta_plan_cuenta` `pc1` ON `pc`.`padre` = `pc1`.`codigo`
    left JOIN `conta_plan_cuenta` `pc2` ON `pc1`.`padre` = `pc2`.`codigo`
    left JOIN `conta_plan_cuenta` `pc3` ON `pc2`.`padre` = `pc3`.`codigo`
    left JOIN `conta_plan_cuenta` `pc4` ON `pc3`.`padre` = `pc4`.`codigo`
where bl.deleted_at is null
and bld.person_id = person_id
and `pe`.`date_from` between desde and hasta
group by `bld`.plan_cuenta_id
order by pc.codigo;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPUpdateHonorario` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `SPUpdateHonorario`()
begin
        declare no_more_rows boolean default false;
        declare empresa_honorario_id, empresa_impuesto_id, IDImpuesto int;
        declare total int default 0;
        declare importe_honorario, importe_impuesto, importe_actualizado decimal(20,2) default 0.00;
        declare cursor1 cursor for select person_id, honorario from person_configurations where active = 2 and honorario >0;
        declare cursor2 cursor for
                SELECT idPersonVto,person_id,importe FROM vto_person_impuesto where impuesto_id=39;
        declare continue handler for not found 
        set no_more_rows := true;

        open cursor1;
        LOOP1: loop
                fetch cursor1 INTO empresa_honorario_id, importe_honorario;
                if no_more_rows then
                    close cursor1;
                    leave LOOP1;
                end if;
                open cursor2;
                LOOP2: loop
                        fetch cursor2 INTO IDImpuesto, empresa_impuesto_id, importe_impuesto;
                        if no_more_rows then
                                set no_more_rows := false;
                                close cursor2;
                                leave LOOP2;
                        end if;
                        if empresa_honorario_id = empresa_impuesto_id then
                                if importe_honorario != importe_impuesto then
                                        set total = total +1;
                                        set importe_actualizado := importe_honorario;
                                        Update vto_person_impuesto set importe = importe_actualizado where idPersonVto = IDImpuesto;
                                end if;

                        end if;

                end loop LOOP2;
        end loop LOOP1;
        select total;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPVtoImpuestosMail` */;
DELIMITER ;;
CREATE DEFINER=`forge`@`%` PROCEDURE `SPVtoImpuestosMail`(anio int,mes int)
BEGIN
    SELECT p.id,pc.person_id_titular as titular,p.field_name1 as empresa,imp.name as impuesto,cat.categoria,vto.anticipo,
        DATE_FORMAT(CONCAT(`vto`.`anio`,'-',`vto`.`mes`,'-',`vto`.`dia`),'%Y-%m-%d')AS `vencimiento`,pi.importe
        FROM vto_person_impuesto pi
        join persons p on pi.person_id = p.id
        left join vto_impuestos imp on pi.impuesto_id = imp.id
        left join vto_categoria_imp cat on pi.categoria_imp_id = cat.id
        left join vto_vencimiento vto on pi.vto_id = vto.id
        left join person_configurations pc on pi.person_id = pc.person_id
        where pc.active =2
        and anio = anio
        and vto.mes = mes
    union

    SELECT per.id,pc1.person_id_titular as titular, per.field_name1 as empresa, pic.impuesto, '' as categoria, pic.anticipo, pic.vencimiento,pic.importe
    FROM vto_person_impuestos_creados pic
    join persons per on pic.person_id = per.id
    join person_configurations pc1 on pic.person_id = pc1.person_id
    where YEAR(vencimiento) = anio and month(vencimiento) = mes

    order by id,vencimiento;



  END ;;
DELIMITER ;

--
-- Final view structure for view `v_business`
--

/*!50001 DROP TABLE IF EXISTS `v_business`*/;
/*!50001 DROP VIEW IF EXISTS `v_business`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_business` AS select (case when (`pc`.`active` = 1) then 'INACTIVA' else 'ACTIVA' end) AS `Status`,`p`.`id` AS `id`,`p`.`field_name1` AS `field_name1`,`p`.`field_name2` AS `field_name2`,`p`.`document_type` AS `document_type`,`p`.`document` AS `document`,`pa`.`id` AS `id_administrador`,`pa`.`field_name1` AS `administrador`,`pa`.`document` AS `cuit_administrador`,`pt`.`id` AS `id_titular`,`pt`.`field_name1` AS `titular`,`pt`.`document` AS `cuit_titular`,`p`.`country_id` AS `country_id`,`p`.`state_id` AS `state_id`,`p`.`zone_id` AS `zone_id`,`p`.`created_at` AS `created_at`,`p`.`updated_at` AS `updated_at`,`p`.`activity_id` AS `activity_id`,`p`.`address` AS `address`,`p`.`person_type_id` AS `person_type_id`,`pc`.`id` AS `person_configuration_id`,`pc`.`settle_calc_by_coef` AS `settle_calc_by_coef`,`pc`.`active` AS `active`,`pc`.`month_close` AS `month_close`,`pc`.`iva_condition_id` AS `iva_condition_id`,`pc`.`obligation_buy` AS `obligation_buy`,`pc`.`obligation_electronic_receipt` AS `obligation_electronic_receipt`,`pc`.`obligation_iibb` AS `obligation_iibb`,`pc`.`obligation_iva` AS `obligation_iva`,`pc`.`obligation_other_taxes` AS `obligation_other_taxes`,`pc`.`obligation_salaries` AS `obligation_salaries`,`pc`.`obligation_sell` AS `obligation_sell`,`pc`.`liquidation_start_period` AS `liquidation_start_period` from (((`persons` `p` join `person_configurations` `pc` on((`p`.`id` = `pc`.`person_id`))) join `persons` `pt` on((`pc`.`person_id_titular` = `pt`.`id`))) join `persons` `pa` on((`pc`.`person_id_administrador` = `pa`.`id`))) */;

--
-- Final view structure for view `v_consultacomprobantes`
--

/*!50001 DROP TABLE IF EXISTS `v_consultacomprobantes`*/;
/*!50001 DROP VIEW IF EXISTS `v_consultacomprobantes`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_consultacomprobantes` AS select `receipts`.`id` AS `IdComprobante`,`receipts`.`type_id` AS `IdTipoComprobante`,`persons`.`id` AS `IdProveedorCliente`,`receipts`.`person_id_owner` AS `IdEmpresa`,`b`.`document` AS `DocumentoEmpresa`,`b`.`field_name1` AS `NombreEmpresa`,`p`.`id` AS `IdPeriodo`,`p`.`date_from` AS `Desde`,`p`.`date_to` AS `Hasta`,`p`.`code` AS `Periodo`,`p`.`year` AS `anioPeriodo`,`p`.`period_number` AS `mesPeriodo`,`types`.`id` AS `IdOperacion`,`types`.`name` AS `Operacion`,`persons`.`field_name1` AS `Proveedor`,`persons`.`document_type` AS `TipoDocumento`,`persons`.`document` AS `Documento`,`zones`.`name` AS `Zona`,`status`.`name` AS `Estado`,`activities`.`name` AS `Actividad`,`receipts`.`receipt_date` AS `FechaComprobante`,`receipts`.`reference` AS `Referencia`,`retention_types`.`name` AS `TipoDeRetencion`,`receipt_types`.`name` AS `TipoComprobante`,`receipts`.`code_ticket` AS `Suc`,`receipts`.`number` AS `Numero`,(case when (`receipt_types`.`credit` = 1) then -(`receipts`.`amount`) else `receipts`.`amount` end) AS `Total`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 3) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_27`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 3) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_27`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 2) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_21`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 2) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_21`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_105`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_105`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 5) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_5`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 5) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_5`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 6) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_0`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 6) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_0`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 4) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_2_5`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 4) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_2_5`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 7) then `receipt_taxes`.`amount` else 0 end))) AS `no_gravado`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1000) then `receipt_taxes`.`amount` else 0 end))) AS `percepIva`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1001) then `receipt_taxes`.`amount` else 0 end))) AS `Imps_Internos`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1002) then `receipt_taxes`.`amount` else 0 end))) AS `Imp_Gasoil`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1003) then `receipt_taxes`.`amount` else 0 end))) AS `Imp_Hidricos`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1004) then `receipt_taxes`.`amount` else 0 end))) AS `ITC`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1005) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Buenos_Aires`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1006) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Capital_Federal`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1007) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Catamarca`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1008) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Chaco`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1009) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Chubut`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1010) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Cordoba`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1011) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Corrientes`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1012) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Entre_Rios`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1013) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Formosa`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1014) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Jujuy`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1015) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_La_Pampa`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1016) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_La_Rioja`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1017) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Mendoza`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1018) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Misiones`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1019) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Neuquen`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1020) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Pascua_Lama`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1021) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Rio_Negro`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1022) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Salta`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1023) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_San_Juan`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1024) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_San_Luis`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1025) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Santa_Cruz`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1026) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Santa_Fe`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1027) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Santiago_del_Estero`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1028) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Tierra_del_Fuego`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1029) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB_Tucuman` from ((((((((((((`receipts` left join `persons` `b` on((`receipts`.`person_id_owner` = `b`.`id`))) left join `periods` `p` on((`receipts`.`period_id` = `p`.`id`))) left join `receipt_types` on((`receipts`.`type_receipt_id` = `receipt_types`.`id`))) left join `retention_types` on((`receipts`.`retention_type_id` = `retention_types`.`id`))) left join `status` on((`receipts`.`status_id` = `status`.`id`))) left join `persons` on((`receipts`.`person_id_relationed` = `persons`.`id`))) left join `receipt_taxes` on((`receipts`.`id` = `receipt_taxes`.`receipt_id`))) left join `zones` on((`receipts`.`zone_id` = `zones`.`id`))) left join `activities` on((`receipts`.`activity_id` = `activities`.`id`))) left join `types` on((`receipts`.`type_id` = `types`.`id`))) left join `system_taxes` on((`receipt_taxes`.`tax_id` = `system_taxes`.`id`))) left join `other_taxes` on((`receipt_taxes`.`tax_id` = `other_taxes`.`id`))) group by `receipts`.`id`,`receipts`.`type_id`,`persons`.`id`,`receipts`.`person_id_owner`,`b`.`document`,`b`.`field_name1`,`p`.`id`,`p`.`date_from`,`p`.`date_to`,`p`.`code`,`p`.`year`,`p`.`period_number`,`types`.`id`,`p`.`code`,`types`.`name`,`persons`.`field_name1`,`persons`.`document_type`,`persons`.`document`,`zones`.`name`,`status`.`name`,`activities`.`name`,`receipts`.`receipt_date`,`receipts`.`reference`,`retention_types`.`name`,`receipt_types`.`name`,`receipts`.`code_ticket`,`receipts`.`number`,`receipts`.`amount` order by `p`.`code`,`persons`.`document`,`receipts`.`code_ticket`,`receipts`.`number` */;

--
-- Final view structure for view `v_consultaproyeccionganancias`
--

/*!50001 DROP TABLE IF EXISTS `v_consultaproyeccionganancias`*/;
/*!50001 DROP VIEW IF EXISTS `v_consultaproyeccionganancias`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_consultaproyeccionganancias` AS select `types`.`id` AS `IdOperacion`,`types`.`name` AS `Operacion`,`receipts`.`person_id_owner` AS `IdEmpresa`,`b`.`document` AS `DocumentoEmpresa`,`b`.`field_name1` AS `NombreEmpresa`,`p`.`year` AS `anioPeriodo`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 1)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `UNO`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 2)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `DOS`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 3)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `TRES`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 4)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `CUATRO`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 5)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `CINCO`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 6)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `SEIS`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 7)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `SIETE`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 8)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `OCHO`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 9)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `NUEVE`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 10)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `DIEZ`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 11)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `ONCE`,sum((case when (((`receipt_taxes`.`tax_id` = 3) or (`receipt_taxes`.`tax_id` = 2) or (`receipt_taxes`.`tax_id` = 1) or (`receipt_taxes`.`tax_id` = 7) or (`receipt_taxes`.`tax_id` = 6) or (`receipt_taxes`.`tax_id` = 5) or (`receipt_taxes`.`tax_id` = 4)) and (`p`.`period_number` = 12)) then ((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * `receipt_taxes`.`amount`) else 0 end)) AS `DOCE` from (((((((((`receipts` left join `persons` `b` on((`receipts`.`person_id_owner` = `b`.`id`))) left join `periods` `p` on((`receipts`.`period_id` = `p`.`id`))) left join `receipt_types` on((`receipts`.`type_receipt_id` = `receipt_types`.`id`))) left join `status` on((`receipts`.`status_id` = `status`.`id`))) left join `persons` on((`receipts`.`person_id_relationed` = `persons`.`id`))) left join `receipt_taxes` on((`receipts`.`id` = `receipt_taxes`.`receipt_id`))) left join `types` on((`receipts`.`type_id` = `types`.`id`))) left join `system_taxes` on((`receipt_taxes`.`tax_id` = `system_taxes`.`id`))) left join `other_taxes` on((`receipt_taxes`.`tax_id` = `other_taxes`.`id`))) where (`types`.`id` in (1,2)) group by `types`.`id`,`types`.`name`,`receipts`.`person_id_owner`,`b`.`document`,`b`.`field_name1`,`p`.`year` order by `types`.`id`,`p`.`year`,`receipts`.`person_id_owner` */;

--
-- Final view structure for view `v_cta_cte_cheques`
--

/*!50001 DROP TABLE IF EXISTS `v_cta_cte_cheques`*/;
/*!50001 DROP VIEW IF EXISTS `v_cta_cte_cheques`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_cta_cte_cheques` AS select `as`.`id` AS `conta_asiento_id`,`as`.`fecha_registracion` AS `fecha`,`asd`.`id` AS `conta_asiento_detail_id`,`asd`.`tipo_asiento_id` AS `tipo_asiento_id`,`ta`.`nombre` AS `registrado_en`,`asd`.`cta_cte_tesoreria_cuenta_id` AS `plan_cuenta_id`,(case when (`asd`.`saldo_id` = 1) then 'DEUDOR' else 'ACREEDOR' end) AS `SALDO`,`asd`.`saldo_id` AS `saldo_id`,`as`.`comentario` AS `comentario`,`asd`.`person_id` AS `person_id`,`ta`.`nombre` AS `asiento`,`pc1`.`nombre` AS `destino`,`tpc`.`nombre` AS `cuenta`,`p`.`field_name1` AS `empresa`,`ch`.`titular` AS `titular`,`ch`.`numero` AS `numero`,`ch`.`comentario` AS `comentarioCheque`,`ch`.`fecha_cobro` AS `fecha_cobro`,`ch`.`status` AS `status`,`ban`.`nombre` AS `banco`,(case when (`ch`.`abierto_cruzado` = 1) then 'ABIERTO' else 'CRUZADO' end) AS `abierto_cruzado`,sum((case when (`asd`.`saldo_id` = 1) then `asd`.`monto` else 0 end)) AS `DEBE`,sum((case when (`asd`.`saldo_id` = 2) then `asd`.`monto` else 0 end)) AS `HABER` from ((((((((`conta_asientos_details` `asd` join `conta_asientos` `as` on((`asd`.`conta_asiento_id` = `as`.`id`))) join `conta_tipo_saldos` `ts` on((`asd`.`saldo_id` = `ts`.`id`))) left join `persons` `p` on((`asd`.`person_id` = `p`.`id`))) join `conta_tipo_asientos` `ta` on((`asd`.`tipo_asiento_id` = `ta`.`id`))) join `cta_cte_tesoreria_cuentas` `tpc` on((`asd`.`cta_cte_tesoreria_cuenta_id` = `tpc`.`id`))) left join `ctacte_cheques` `ch` on((`as`.`id` = `ch`.`asiento_id`))) left join `cta_cte_tesoreria_cuentas` `pc1` on((`ch`.`cta_cte_tesoreria_cuenta_id` = `pc1`.`id`))) left join `ctacte_bancos` `ban` on((`ch`.`banco_id` = `ban`.`id`))) where (isnull(`asd`.`deleted_at`) and (`asd`.`cta_cte_tesoreria_cuenta_id` = 176)) group by `asd`.`id` order by `as`.`id`,`asd`.`id`,`asd`.`saldo_id`,`p`.`field_name1`,`tpc`.`nombre` */;

--
-- Final view structure for view `v_cta_cte_clientes`
--

/*!50001 DROP TABLE IF EXISTS `v_cta_cte_clientes`*/;
/*!50001 DROP VIEW IF EXISTS `v_cta_cte_clientes`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_cta_cte_clientes` AS select `as`.`id` AS `conta_asiento_id`,`as`.`fecha_registracion` AS `fecha`,`asd`.`id` AS `conta_asiento_detail_id`,`asd`.`tipo_asiento_id` AS `tipo_asiento_id`,`ta`.`nombre` AS `registrado_en`,`asd`.`cta_cte_tesoreria_cuenta_id` AS `cta_cte_tesoreria_cuenta_id`,(case when (`asd`.`saldo_id` = 1) then 'DEUDOR' else 'ACREEDOR' end) AS `SALDO`,`asd`.`saldo_id` AS `saldo_id`,`as`.`comentario` AS `comentario`,`asd`.`person_id` AS `person_id`,`ta`.`nombre` AS `asiento`,`tpc`.`nombre` AS `cuenta`,`p`.`field_name1` AS `empresa`,(case when (`asd`.`saldo_id` = 1) then `asd`.`monto` else 0 end) AS `DEBE`,(case when (`asd`.`saldo_id` = 2) then `asd`.`monto` else 0 end) AS `HABER` from (((((`conta_asientos_details` `asd` join `conta_asientos` `as` on((`asd`.`conta_asiento_id` = `as`.`id`))) join `conta_tipo_saldos` `ts` on((`asd`.`saldo_id` = `ts`.`id`))) join `persons` `p` on((`asd`.`person_id` = `p`.`id`))) join `conta_tipo_asientos` `ta` on((`asd`.`tipo_asiento_id` = `ta`.`id`))) join `cta_cte_tesoreria_cuentas` `tpc` on((`asd`.`cta_cte_tesoreria_cuenta_id` = `tpc`.`id`))) where (isnull(`asd`.`deleted_at`) and (`tpc`.`id` = 13)) order by `as`.`id`,`asd`.`id`,`asd`.`saldo_id`,`p`.`field_name1` */;

--
-- Final view structure for view `v_iibb_reg_simpl`
--

/*!50001 DROP TABLE IF EXISTS `v_iibb_reg_simpl`*/;
/*!50001 DROP VIEW IF EXISTS `v_iibb_reg_simpl`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_iibb_reg_simpl` AS select `types`.`id` AS `IdOperacion`,`types`.`name` AS `Operacion`,`receipts`.`person_id_owner` AS `person_id`,`b`.`document` AS `cuit`,`b`.`field_name1` AS `empresa`,`p`.`year` AS `anioPeriodo`,`vpi`.`impuesto_id` AS `impuesto_id`,`vpi`.`importe` AS `importe`,`m`.`categoria` AS `categoria`,`m`.`facAnual` AS `facAnual`,sum(((case when (`vpi`.`impuesto_id` = 13) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end) / 6)) AS `TOTAL`,sum((case when ((`vpi`.`vto_id` = 1110) and ((`p`.`period_number` = 1) or (`p`.`period_number` = 2) or (`p`.`period_number` = 3) or (`p`.`period_number` = 4))) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `Q1`,sum((case when ((`vpi`.`vto_id` = 1110) and ((`p`.`period_number` = 5) or (`p`.`period_number` = 6) or (`p`.`period_number` = 7) or (`p`.`period_number` = 8))) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `Q2`,sum((case when ((`vpi`.`vto_id` = 1110) and ((`p`.`period_number` = 9) or (`p`.`period_number` = 10) or (`p`.`period_number` = 11) or (`p`.`period_number` = 12))) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `Q3`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 1)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `UNO`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 2)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `DOS`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 3)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `TRES`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 4)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `CUATRO`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 5)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `CINCO`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 6)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `SEIS`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 7)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `SIETE`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 8)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `OCHO`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 9)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `NUEVE`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 10)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `DIEZ`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 11)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `ONCE`,sum((case when ((`vpi`.`vto_id` = 1110) and (`p`.`period_number` = 12)) then ((case when (`receipts`.`type_receipt_id` = 9) then -(1) else 1 end) * `receipts`.`amount`) else 0 end)) AS `DOCE` from ((((((((((((`receipts` left join `persons` `b` on((`receipts`.`person_id_owner` = `b`.`id`))) left join `periods` `p` on((`receipts`.`period_id` = `p`.`id`))) left join `receipt_types` on((`receipts`.`type_receipt_id` = `receipt_types`.`id`))) left join `status` on((`receipts`.`status_id` = `status`.`id`))) left join `persons` on((`receipts`.`person_id_relationed` = `persons`.`id`))) left join `receipt_taxes` on((`receipts`.`id` = `receipt_taxes`.`receipt_id`))) left join `types` on((`receipts`.`type_id` = `types`.`id`))) left join `system_taxes` on((`receipt_taxes`.`tax_id` = `system_taxes`.`id`))) left join `other_taxes` on((`receipt_taxes`.`tax_id` = `other_taxes`.`id`))) join `vto_person_impuesto` `vpi` on((`b`.`id` = `vpi`.`person_id`))) join `vto_categoria_imp` `vci` on((`vpi`.`categoria_imp_id` = `vci`.`id`))) join `categorias_iibb` `m` on((`vci`.`cat_mono_id` = `m`.`id`))) where ((`receipts`.`type_id` = 1) and (`vpi`.`impuesto_id` = 13)) group by `types`.`id`,`types`.`name`,`receipts`.`person_id_owner`,`b`.`document`,`b`.`field_name1`,`p`.`year`,`vpi`.`impuesto_id`,`vpi`.`importe`,`m`.`categoria`,`m`.`facAnual` order by `empresa` */;

--
-- Final view structure for view `v_ingresos_brutos_gasto`
--

/*!50001 DROP TABLE IF EXISTS `v_ingresos_brutos_gasto`*/;
/*!50001 DROP VIEW IF EXISTS `v_ingresos_brutos_gasto`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_ingresos_brutos_gasto` AS select `v_liquidaciones_iibb`.`anio` AS `anio`,`v_liquidaciones_iibb`.`IdEmpresa` AS `IdEmpresa`,`v_liquidaciones_iibb`.`NombreEmpresa` AS `NombreEmpresa`,sum((case when (`v_liquidaciones_iibb`.`mes` = 1) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `UNO`,sum((case when (`v_liquidaciones_iibb`.`mes` = 2) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `DOS`,sum((case when (`v_liquidaciones_iibb`.`mes` = 3) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `TRES`,sum((case when (`v_liquidaciones_iibb`.`mes` = 4) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `CUATRO`,sum((case when (`v_liquidaciones_iibb`.`mes` = 5) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `CINCO`,sum((case when (`v_liquidaciones_iibb`.`mes` = 6) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `SEIS`,sum((case when (`v_liquidaciones_iibb`.`mes` = 7) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `SIETE`,sum((case when (`v_liquidaciones_iibb`.`mes` = 8) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `OCHO`,sum((case when (`v_liquidaciones_iibb`.`mes` = 9) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `NUEVE`,sum((case when (`v_liquidaciones_iibb`.`mes` = 10) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `DIEZ`,sum((case when (`v_liquidaciones_iibb`.`mes` = 11) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `ONCE`,sum((case when (`v_liquidaciones_iibb`.`mes` = 12) then `v_liquidaciones_iibb`.`impuesto` else 0 end)) AS `DOCE` from `v_liquidaciones_iibb` group by `v_liquidaciones_iibb`.`anio`,`v_liquidaciones_iibb`.`IdEmpresa`,`v_liquidaciones_iibb`.`NombreEmpresa` */;

--
-- Final view structure for view `v_liquidaciones`
--

/*!50001 DROP TABLE IF EXISTS `v_liquidaciones`*/;
/*!50001 DROP VIEW IF EXISTS `v_liquidaciones`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_liquidaciones` AS select `period_liquidations`.`id` AS `Idperiod_liquidations`,`period_liquidations`.`apply_to` AS `apply_to`,`period_liquidations`.`status` AS `Status`,`period_liquidations`.`period_id` AS `IdPeriodo`,`period_liquidations`.`person_id` AS `IdEmpresa`,`b`.`document` AS `DocumentoEmpresa`,`b`.`field_name1` AS `NombreEmpresa`,`p`.`date_from` AS `Desde`,`p`.`date_to` AS `Hasta`,`p`.`code` AS `Periodo` from ((`period_liquidations` left join `persons` `b` on((`period_liquidations`.`person_id` = `b`.`id`))) left join `periods` `p` on((`period_liquidations`.`period_id` = `p`.`id`))) order by `period_liquidations`.`apply_to`,`p`.`code`,`b`.`field_name1` */;

--
-- Final view structure for view `v_liquidaciones_iibb`
--

/*!50001 DROP TABLE IF EXISTS `v_liquidaciones_iibb`*/;
/*!50001 DROP VIEW IF EXISTS `v_liquidaciones_iibb`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_liquidaciones_iibb` AS select `pl`.`status` AS `Status`,`pld`.`id` AS `IdLiquidacionIIBB`,`pld`.`period_liquidation_id` AS `period_liquidation_id`,`liquidators`.`name` AS `liquidador`,`pld`.`period_id` AS `IdPeriodo`,`pld`.`person_id` AS `IdEmpresa`,`b`.`document` AS `DocumentoEmpresa`,`b`.`field_name1` AS `NombreEmpresa`,`p`.`year` AS `anio`,`p`.`period_number` AS `mes`,`p`.`date_from` AS `Desde`,`p`.`date_to` AS `Hasta`,`p`.`code` AS `Periodo`,`z`.`name` AS `Zona`,`pld`.`coef` AS `coeficiente`,`pld`.`aliquot` AS `alicuota`,`pld`.`base_amount` AS `baseImponible`,`pld`.`tax_amount` AS `impuesto`,`pld`.`previous_period_balance` AS `saldoAnterior`,`pld`.`sircreb_amount` AS `sircreb`,`pld`.`perception_amount` AS `percepciones`,`pld`.`retention_amount` AS `retenciones`,(select `plia`.`negative_amount` from `period_liquidation_details` `plia` where ((`pld`.`zone_id` = `plia`.`zone_id`) and (`plia`.`period_id` = (select `pa`.`id` from `periods` `pa` where ((`pa`.`person_id` = `b`.`id`) and (`pa`.`period_number` = (case when (`p`.`period_number` > 1) then (`p`.`period_number` - 1) else 12 end)) and (`pa`.`year` = (case when (`p`.`period_number` = 1) then (`p`.`year` - 1) else `p`.`year` end))))))) AS `A_PAGAR_ANTERIOR`,`pld`.`negative_amount` AS `A_Pagar`,`pld`.`positive_amount` AS `A_Favor` from (((((((`period_liquidation_details` `pld` join `persons` `b` on((`pld`.`person_id` = `b`.`id`))) join `person_liquidators` on((`b`.`id` = `person_liquidators`.`person_id`))) join `liquidators` on((`person_liquidators`.`liquidator_id` = `liquidators`.`id`))) join `person_configurations` on((`pld`.`person_id` = `person_configurations`.`person_id`))) left join `periods` `p` on((`pld`.`period_id` = `p`.`id`))) left join `period_liquidations` `pl` on((`pld`.`period_liquidation_id` = `pl`.`id`))) left join `zones` `z` on((`pld`.`zone_id` = `z`.`id`))) where (`person_configurations`.`active` = 2) order by `p`.`code`,`b`.`field_name1` */;

--
-- Final view structure for view `v_liquidaciones_iva`
--

/*!50001 DROP TABLE IF EXISTS `v_liquidaciones_iva`*/;
/*!50001 DROP VIEW IF EXISTS `v_liquidaciones_iva`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_liquidaciones_iva` AS select `pl`.`status` AS `Status`,`period_liquidation_iva`.`id` AS `IdLiquidacionIVA`,`period_liquidation_iva`.`period_liquidation_id` AS `period_liquidation_id`,`liquidators`.`name` AS `liquidador`,`period_liquidation_iva`.`period_id` AS `IdPeriodo`,`period_liquidation_iva`.`person_id` AS `IdEmpresa`,`b`.`document` AS `DocumentoEmpresa`,`b`.`field_name1` AS `NombreEmpresa`,`p`.`date_from` AS `Desde`,`p`.`date_to` AS `Hasta`,`p`.`code` AS `Periodo`,`period_liquidation_iva`.`total_debit` AS `total_debit`,`period_liquidation_iva`.`total_credit` AS `total_credit`,`period_liquidation_iva`.`previous_technical` AS `previous_technical`,`period_liquidation_iva`.`previous_free_availability` AS `previous_free_availability`,`period_liquidation_iva`.`retention_amount` AS `retention_amount`,`period_liquidation_iva`.`perception_amount` AS `perception_amount`,`period_liquidation_iva`.`compensation_amount` AS `compensation_amount`,`period_liquidation_iva`.`total_perceptions_retentions` AS `total_perceptions_retentions`,`period_liquidation_iva`.`free_availability_balance` AS `free_availability_balance`,(select (case when isnull(`plia`.`negative_amount`) then 0 else `plia`.`negative_amount` end) from `period_liquidation_iva` `plia` where (`plia`.`period_id` = (select `p`.`id` from `periods` `p` where ((`p`.`person_id` = `b`.`id`) and (`p`.`period_number` = (case when (`p`.`period_number` > 1) then (`p`.`period_number` - 1) else 12 end)) and (`p`.`year` = (case when (`p`.`period_number` = 1) then (`p`.`year` - 1) else `p`.`year` end)))))) AS `A_PAGAR_ANTERIOR`,`period_liquidation_iva`.`negative_amount` AS `A_PAGAR`,`period_liquidation_iva`.`positive_amount` AS `SaldoTecnicoAFavor`,`period_liquidation_iva`.`total_free_availability` AS `SaldoLibreDisponibilidadAFavor` from ((((((`period_liquidation_iva` join `persons` `b` on((`period_liquidation_iva`.`person_id` = `b`.`id`))) join `periods` `p` on((`period_liquidation_iva`.`period_id` = `p`.`id`))) join `period_liquidations` `pl` on((`period_liquidation_iva`.`period_liquidation_id` = `pl`.`id`))) join `person_liquidators` on((`b`.`id` = `person_liquidators`.`person_id`))) join `liquidators` on((`person_liquidators`.`liquidator_id` = `liquidators`.`id`))) join `person_configurations` on((`period_liquidation_iva`.`person_id` = `person_configurations`.`person_id`))) where ((`person_configurations`.`active` = 2) and isnull(`pl`.`deleted_at`)) order by `p`.`code`,`b`.`field_name1` */;

--
-- Final view structure for view `v_pdf_consultacomprobantes`
--

/*!50001 DROP TABLE IF EXISTS `v_pdf_consultacomprobantes`*/;
/*!50001 DROP VIEW IF EXISTS `v_pdf_consultacomprobantes`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_pdf_consultacomprobantes` AS select `receipts`.`id` AS `IdComprobante`,`receipts`.`type_id` AS `IdTipoComprobante`,`persons`.`id` AS `IdProveedorCliente`,`receipts`.`person_id_owner` AS `IdEmpresa`,`b`.`document` AS `DocumentoEmpresa`,`b`.`field_name1` AS `NombreEmpresa`,`p`.`id` AS `IdPeriodo`,`p`.`date_from` AS `Desde`,`p`.`date_to` AS `Hasta`,`p`.`code` AS `Periodo`,`p`.`year` AS `anioPeriodo`,`p`.`period_number` AS `mesPeriodo`,`types`.`id` AS `IdOperacion`,`types`.`name` AS `Operacion`,`persons`.`field_name1` AS `Proveedor`,`persons`.`document_type` AS `TipoDocumento`,`persons`.`document` AS `Documento`,`zones`.`name` AS `Zona`,`status`.`name` AS `Estado`,`activities`.`name` AS `Actividad`,`receipts`.`receipt_date` AS `FechaComprobante`,`receipts`.`reference` AS `Referencia`,`retention_types`.`name` AS `TipoDeRetencion`,`receipt_types`.`name` AS `TipoComprobante`,`receipts`.`code_ticket` AS `Suc`,`receipts`.`number` AS `Numero`,(case when (`receipt_types`.`credit` = 1) then -(`receipts`.`amount`) else `receipts`.`amount` end) AS `Total`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 3) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_27`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 3) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_27`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 2) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_21`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 2) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_21`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_105`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_105`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 5) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_5`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 5) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_5`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 6) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_0`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 6) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_0`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 4) then `receipt_taxes`.`amount` else 0 end))) AS `gravado_2_5`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 4) then `receipt_taxes`.`tax_amount` else 0 end))) AS `IVA_2_5`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 7) then `receipt_taxes`.`amount` else 0 end))) AS `no_gravado`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1000) then `receipt_taxes`.`amount` else 0 end))) AS `percepIva`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1001) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1002) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1003) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1004) then `receipt_taxes`.`amount` else 0 end))) AS `Imps_Internos`,((case when (`receipt_types`.`credit` = 1) then -(1) else 1 end) * sum((case when (`receipt_taxes`.`tax_id` = 1005) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1006) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1007) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1008) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1009) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1010) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1011) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1012) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1013) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1014) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1015) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1016) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1017) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1018) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1019) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1020) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1021) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1022) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1023) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1024) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1025) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1026) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1027) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1028) then `receipt_taxes`.`amount` when (`receipt_taxes`.`tax_id` = 1029) then `receipt_taxes`.`amount` else 0 end))) AS `Perc_IIBB` from ((((((((((((`receipts` left join `persons` `b` on((`receipts`.`person_id_owner` = `b`.`id`))) left join `periods` `p` on((`receipts`.`period_id` = `p`.`id`))) left join `receipt_types` on((`receipts`.`type_receipt_id` = `receipt_types`.`id`))) left join `retention_types` on((`receipts`.`retention_type_id` = `retention_types`.`id`))) left join `status` on((`receipts`.`status_id` = `status`.`id`))) left join `persons` on((`receipts`.`person_id_relationed` = `persons`.`id`))) left join `receipt_taxes` on((`receipts`.`id` = `receipt_taxes`.`receipt_id`))) left join `zones` on((`receipts`.`zone_id` = `zones`.`id`))) left join `activities` on((`receipts`.`activity_id` = `activities`.`id`))) left join `types` on((`receipts`.`type_id` = `types`.`id`))) left join `system_taxes` on((`receipt_taxes`.`tax_id` = `system_taxes`.`id`))) left join `other_taxes` on((`receipt_taxes`.`tax_id` = `other_taxes`.`id`))) where isnull(`receipts`.`deleted_at`) group by `receipts`.`id`,`receipts`.`type_id`,`persons`.`id`,`receipts`.`person_id_owner`,`b`.`document`,`b`.`field_name1`,`p`.`id`,`p`.`date_from`,`p`.`date_to`,`p`.`code`,`p`.`year`,`p`.`period_number`,`types`.`id`,`p`.`code`,`types`.`name`,`persons`.`field_name1`,`persons`.`document_type`,`persons`.`document`,`zones`.`name`,`status`.`name`,`activities`.`name`,`receipts`.`receipt_date`,`receipts`.`reference`,`retention_types`.`name`,`receipt_types`.`name`,`receipts`.`code_ticket`,`receipts`.`number`,`receipts`.`amount` order by `p`.`code`,`persons`.`document`,`receipts`.`code_ticket`,`receipts`.`number` */;

--
-- Final view structure for view `v_retenciones_ganancias`
--

/*!50001 DROP TABLE IF EXISTS `v_retenciones_ganancias`*/;
/*!50001 DROP VIEW IF EXISTS `v_retenciones_ganancias`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_retenciones_ganancias` AS select `types`.`id` AS `IdOperacion`,`types`.`name` AS `Operacion`,`receipts`.`person_id_owner` AS `IdEmpresa`,`b`.`document` AS `DocumentoEmpresa`,`b`.`field_name1` AS `NombreEmpresa`,`p`.`year` AS `anioPeriodo`,sum((case when ((`p`.`period_number` = 1) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `UNO`,sum((case when ((`p`.`period_number` = 2) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `DOS`,sum((case when ((`p`.`period_number` = 3) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `TRES`,sum((case when ((`p`.`period_number` = 4) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `CUATRO`,sum((case when ((`p`.`period_number` = 5) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `CINCO`,sum((case when ((`p`.`period_number` = 6) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `SEIS`,sum((case when ((`p`.`period_number` = 7) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `SIETE`,sum((case when ((`p`.`period_number` = 8) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `OCHO`,sum((case when ((`p`.`period_number` = 9) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `NUEVE`,sum((case when ((`p`.`period_number` = 10) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `DIEZ`,sum((case when ((`p`.`period_number` = 11) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `ONCE`,sum((case when ((`p`.`period_number` = 12) and (`receipts`.`retention_type_id` = 1)) then `receipts`.`amount` else 0 end)) AS `DOCE` from (((((`receipts` left join `persons` `b` on((`receipts`.`person_id_owner` = `b`.`id`))) left join `periods` `p` on((`receipts`.`period_id` = `p`.`id`))) left join `status` on((`receipts`.`status_id` = `status`.`id`))) left join `persons` on((`receipts`.`person_id_relationed` = `persons`.`id`))) left join `types` on((`receipts`.`type_id` = `types`.`id`))) where (`types`.`id` = 3) group by `types`.`id`,`types`.`name`,`receipts`.`person_id_owner`,`b`.`document`,`b`.`field_name1`,`p`.`year` order by `types`.`id`,`p`.`year`,`receipts`.`person_id_owner` */;

--
-- Final view structure for view `v_sircreb_comp_anticipos_ganan`
--

/*!50001 DROP TABLE IF EXISTS `v_sircreb_comp_anticipos_ganan`*/;
/*!50001 DROP VIEW IF EXISTS `v_sircreb_comp_anticipos_ganan`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_sircreb_comp_anticipos_ganan` AS select `types`.`id` AS `IdOperacion`,`types`.`name` AS `Operacion`,`receipts`.`person_id_owner` AS `IdEmpresa`,`b`.`document` AS `DocumentoEmpresa`,`b`.`field_name1` AS `NombreEmpresa`,`p`.`year` AS `anioPeriodo`,sum((case when (`p`.`period_number` = 1) then `receipts`.`amount` else 0 end)) AS `UNO`,sum((case when (`p`.`period_number` = 2) then `receipts`.`amount` else 0 end)) AS `DOS`,sum((case when (`p`.`period_number` = 3) then `receipts`.`amount` else 0 end)) AS `TRES`,sum((case when (`p`.`period_number` = 4) then `receipts`.`amount` else 0 end)) AS `CUATRO`,sum((case when (`p`.`period_number` = 5) then `receipts`.`amount` else 0 end)) AS `CINCO`,sum((case when (`p`.`period_number` = 6) then `receipts`.`amount` else 0 end)) AS `SEIS`,sum((case when (`p`.`period_number` = 7) then `receipts`.`amount` else 0 end)) AS `SIETE`,sum((case when (`p`.`period_number` = 8) then `receipts`.`amount` else 0 end)) AS `OCHO`,sum((case when (`p`.`period_number` = 9) then `receipts`.`amount` else 0 end)) AS `NUEVE`,sum((case when (`p`.`period_number` = 10) then `receipts`.`amount` else 0 end)) AS `DIEZ`,sum((case when (`p`.`period_number` = 11) then `receipts`.`amount` else 0 end)) AS `ONCE`,sum((case when (`p`.`period_number` = 12) then `receipts`.`amount` else 0 end)) AS `DOCE` from (((((`receipts` left join `persons` `b` on((`receipts`.`person_id_owner` = `b`.`id`))) left join `periods` `p` on((`receipts`.`period_id` = `p`.`id`))) left join `status` on((`receipts`.`status_id` = `status`.`id`))) left join `persons` on((`receipts`.`person_id_relationed` = `persons`.`id`))) left join `types` on((`receipts`.`type_id` = `types`.`id`))) where (`types`.`id` in (4,5,6)) group by `types`.`id`,`types`.`name`,`receipts`.`person_id_owner`,`b`.`document`,`b`.`field_name1`,`p`.`year` order by `types`.`id`,`p`.`year`,`receipts`.`person_id_owner` */;

--
-- Final view structure for view `v_vencimiento_impuestos`
--

/*!50001 DROP TABLE IF EXISTS `v_vencimiento_impuestos`*/;
/*!50001 DROP VIEW IF EXISTS `v_vencimiento_impuestos`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_vencimiento_impuestos` AS select `vto_p`.`idPersonVto` AS `idPersonVto`,`p`.`id` AS `person_id`,`l`.`name` AS `liquidador`,`p`.`field_name1` AS `empresa`,`p`.`document` AS `cuit`,`imp`.`name` AS `impuesto`,`cat`.`categoria` AS `categoria`,`vto_p`.`impuesto_id` AS `impuesto_id`,`vto_p`.`categoria_imp_id` AS `categoria_imp_id`,date_format(str_to_date(concat(`vto`.`anio`,'/',`vto`.`mes`,'/',`vto`.`dia`),'%Y/%m/%d'),'%d-%m-%Y') AS `vencimiento`,`vto_p`.`vto_id` AS `vto_id`,`vto`.`anticipo` AS `anticipo`,`vto_p`.`importe` AS `importe`,`vto_p`.`comentario` AS `comentario`,monthname(str_to_date(concat(`vto`.`anio`,'/',`vto`.`mes`,'/',`vto`.`dia`),'%Y/%m/%d')) AS `mes`,month(str_to_date(concat(`vto`.`anio`,'/',`vto`.`mes`,'/',`vto`.`dia`),'%Y/%m/%d')) AS `NumMes` from (((((((`vto_person_impuesto` `vto_p` join `person_configurations` `pc` on((`vto_p`.`person_id` = `pc`.`person_id`))) join `persons` `p` on((`pc`.`person_id` = `p`.`id`))) join `vto_vencimiento` `vto` on((`vto_p`.`vto_id` = `vto`.`id`))) join `vto_impuestos` `imp` on((`vto`.`impuesto_id` = `imp`.`id`))) left join `vto_categoria_imp` `cat` on((`vto_p`.`categoria_imp_id` = `cat`.`id`))) left join `person_liquidators` `pl` on((`p`.`id` = `pl`.`person_id`))) left join `liquidators` `l` on((`pl`.`liquidator_id` = `l`.`id`))) where (`pc`.`active` = 2) order by date_format(str_to_date(concat(`vto`.`anio`,'/',`vto`.`mes`,'/',`vto`.`dia`),'%Y/%m/%d'),'%d-%m-%Y'),`imp`.`name` */;

--
-- Final view structure for view `v_vencimientosanuales`
--

/*!50001 DROP TABLE IF EXISTS `v_vencimientosanuales`*/;
/*!50001 DROP VIEW IF EXISTS `v_vencimientosanuales`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`forge`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `v_vencimientosanuales` AS select `imp`.`id` AS `impuesto_id`,`imp`.`name` AS `impuesto`,`vto`.`anio` AS `anio`,`vto`.`mes` AS `mes`,`vto`.`dia` AS `dia`,`vto`.`anticipo` AS `anticipo`,`imp`.`status` AS `status`,`vto_fin_cuit`.`grupo` AS `grupo`,`vto_fin_cuit`.`id` AS `IdFinCuit` from (((`vto_vencimiento` `vto` left join `vto_impuestos` `imp` on((`vto`.`impuesto_id` = `imp`.`id`))) left join `vto_imp_fin_cuit` on((`vto`.`finCuit_id` = `vto_imp_fin_cuit`.`idImpFinCuit`))) left join `vto_fin_cuit` on((`vto_imp_fin_cuit`.`finCuit_id` = `vto_fin_cuit`.`id`))) order by `imp`.`name`,`vto`.`anio`,`vto`.`mes`,`vto`.`dia` */;


