
DROP procedure IF EXISTS `liquidacionHijasIVA`;

DELIMITER $$
CREATE 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 r.person_id_owner, 6 as ordr, 2 as type, 4 as origin, pa.activity_id ,st.id, st.name, st.percent_iva,
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
from `system_taxes` as `st` cross join
  user_persons up on up.parent_person_id = madre_id
 left join `person_activities` as `pa` on pa.person_id = up.person_id
 left 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 
 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`
 where exists (select person_id from user_persons up where up.person_id = r.person_id_owner and up.parent_person_id = madre_id)
 group by 1,2,3,4,5,6,7,8
 )
 union (
 select r.person_id_owner, 4 as ordr, 1 as type, 2 as origin, pa.activity_id ,st.id, st.name, st.percent_iva,
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
from `system_taxes` as `st` cross join
  user_persons up on up.parent_person_id = madre_id
 left join `person_activities` as `pa` on pa.person_id = up.person_id
 left 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 
 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`
 where exists (select person_id from user_persons up where up.person_id = r.person_id_owner and up.parent_person_id = madre_id)
 group by 1,2,3,4,5,6,7,8
 )
 union (
 select r.person_id_owner, 5 as ordr, 2 as type, 3 as origin, pa.activity_id ,st.id, st.name, st.percent_iva,
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
from `system_taxes` as `st` cross join
  user_persons up on up.parent_person_id = madre_id
 left join `person_activities` as `pa` on pa.person_id = up.person_id
 left 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 
 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`
 where exists (select person_id from user_persons up where up.person_id = r.person_id_owner and up.parent_person_id = madre_id)
 group by 1,2,3,4,5,6,7,8
 )
 union (
 select r.person_id_owner, 1 as ordr, 1 as type, 1 as origin, pa.activity_id ,st.id, st.name, st.percent_iva,
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
from `system_taxes` as `st` cross join
  user_persons up on up.parent_person_id = madre_id
 left join `person_activities` as `pa` on pa.person_id = up.person_id
 left 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 
 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` and `tr`.`agroup_iva_module` = '1'
 left join `receipt_taxes` as `rt` on `r`.`id` = `rt`.`receipt_id`
 where exists (select person_id from user_persons up where up.person_id = r.person_id_owner and up.parent_person_id = madre_id)
 and `st`.`id` not in ('6', '7') group by 1,2,3,4,5,6,7,8
 ) 
 union (
 select r.person_id_owner, 2 as ordr, 1 as type, 1 as origin, pa.activity_id ,st.id, st.name, st.percent_iva,
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
from `system_taxes` as `st` cross join
  user_persons up on up.parent_person_id = madre_id
 left join `person_activities` as `pa` on pa.person_id = up.person_id
 left 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 
 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` and `tr`.`agroup_iva_module` = '2'
 left join `receipt_taxes` as `rt` on `r`.`id` = `rt`.`receipt_id`
 where exists (select person_id from user_persons up where up.person_id = r.person_id_owner and up.parent_person_id = madre_id)
 and `st`.`id` not in ('6', '7') group by 1,2,3,4,5,6,7,8
 )
 union (
 select r.person_id_owner, 3 as ordr, 1 as type, 1 as origin, pa.activity_id ,st.id, st.name, st.percent_iva,
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
from `system_taxes` as `st` cross join
  user_persons up on up.parent_person_id = madre_id
 left join `person_activities` as `pa` on pa.person_id = up.person_id
 left 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
 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`
 where `st`.`id` in ('6', '7') and person_id_owner is not null and pa.activity_id is not null group by 1,2,3,4,5,6,7,8
 )
 
 order by 1,2, `percent_iva` desc;


		-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
		-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 

		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 ;
            
           -- insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva', concat('cantidad de registros del cursor->', cantidad), now() );

			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
           -- REPEAT

            FETCH currents INTO personId, ordrV, typeV, originV, activityId, taxId, taxName, percentIva, taxabledAmount;
            
            IF NOT done THEN
				set paso := paso +1;
            /*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva',
	concat('personId->',personId, ',ordrV->', ordrV, ',typeV->', typeV, ', originV->', originV
    , ', activityId->', activityId, ', activityId->', activityId, ', taxId->', taxId, ', taxName->', taxName
    , ', percentIva->', percentIva, ', taxabledAmount->', taxabledAmount), 
	now() );*/
            
			-- por cada personId diferente registro en la tabla

			  IF difPerson <> personId THEN
              
              
              
              
              -- busco el period_id
             select id INTO periodId from periods where person_id = personId
                 and year = to_year and period_number = to_month;
                 
			-- busco el periodo anterior
            set prevPeriodId := null;
			select id into prevPeriodId from periods p
                    where p.person_id = personId 
                    and year = prevYear and period_number = prevMonth;
                    
			/*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva',
				concat('personId->',personId, ',periodId->', periodId, ',prevYear->', prevYear, ', prevMonth->', prevMonth
				, ', prevPeriodId->', prevPeriodId
                ), 
				now() );*/
			
            IF(prevPeriodId is not null) THEN
			-- busco el ultimo periodo de liquidacion de iva             
             select positive_amount, total_free_availability  INTO preVtechnical, preVfree_availability
				from period_liquidation_iva where person_id = personId and period_id=prevPeriodId;
            END IF;
	

			/*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva',
				concat('personId->',personId, ',periodId->', periodId, ',prevYear->', prevYear, ', prevMonth->', prevMonth
				, ', prevPeriodId->', prevPeriodId, ', preVtechnical->', preVtechnical
                , ', preVfree_availability->', preVfree_availability
                ), 
				now() );*/
                
			  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 log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva',
				concat('personId->',personId, ',periodId->', periodId, ',to_year->', to_year, ', to_month->', to_month
				, ', preVtechnical->', preVtechnical, ', preVfree_availability->', preVfree_availability
                ), 
				now() );*/

				set difPerson := personId;
                set debitValue := 0;
			    set creditValue := 0;
                
				/*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva',
				 concat('personId->',personId, ',periodId->', periodId, ',difPerson->', difPerson
                 , ', debitValue->', debitValue, ', creditValue->', creditValue
                 ), 
				 now() );*/
              
                
			  -- luego empiezo a registrar los period liquidation

				 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();
                 
              -- luego el registro del period liquidation iva
                 
                 insert into period_liquidation_iva (period_id, person_id, period_liquidation_id, created_at, updated_at)
				 values (periodId, personId, periodLiquidationId, now(), now());
                 SET periodLiquidationIvaId = LAST_INSERT_ID();
                 
				/*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva',
				 concat('personId->',personId, ',periodId->', periodId, ',periodLiquidationId->', periodLiquidationId
                 , ', periodLiquidationIvaId->', periodLiquidationIvaId
                 ), 
				 now() );*/
			  
              
			  END IF;
  
              
               -- taxValue, resultValue, debitValue, creditValue
               -- personId, ordrV, typeV, originV, activityId, taxId, taxName, percentIva, taxabledAmount
               set taxValue := (percentIva * taxabledAmount )/100;
               set debitValue := debitValue + (if(typeV=1, taxValue, 0));
			   set creditValue := creditValue + (if(typeV=2, taxValue, 0));
               
               /*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva',
				 concat('personId->',personId, ',taxValue->', taxValue, ',debitValue->', debitValue
                 , ', creditValue->', creditValue
                 ), 
				 now() );*/
            
            -- al final el detalle de cada unoen period liquidation iva detail
              

              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)
				values ( periodLiquidationId, periodLiquidationIvaId, periodId,
                personId, activityId, typeV, taxId, taxabledAmount, taxValue, now(), now(), ordrV) ;
                
                IF (periodLiquidationIvaId > 0) THEN
                /*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva'
					, concat('cambiando de empresa-------'), now() );*/
                
				-- actualizo el period liquidation iva, tiene que ser al inicio del cambio o al final de todi...
				   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 log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva'
					, concat('actualizando periodLiquidationIvaId->', periodLiquidationIvaId
						, ', creditValue->', creditValue, ', debitValue->', debitValue
                        , ', technicalV->', technicalV, ', totalRPV->', totalRPV, ', freeAvBalV->', freeAvBalV
                        ), now() );    */ 
                        
					UPDATE `period_liquidation_iva`
						SET `total_debit` = debitValue , `total_credit` = creditValue, `previous_technical` = preVtechnical
                         , `technical_balance` = technicalV, `previous_free_availability` = preVfree_availability
                         , `retention_amount` = retentionV, `perception_amount` = perceptionV, `compensation_amount` = compensationV
                         , `total_perceptions_retentions` = totalRPV, `free_availability_balance` = freeAvBalV
                         , `negative_amount` = negativeV, `positive_amount` = positiveV, `total_free_availability` = totalFAV
						WHERE `id` = periodLiquidationIvaId;
                        
					/*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva'
					, concat( 'UPDATE `period_liquidation_iva` SET `total_debit` = ',debitValue,' , `total_credit` = '
                    ,creditValue,', `previous_technical` = ',preVtechnical,', `technical_balance` = ',technicalV
                    ,', `previous_free_availability` = ',preVfree_availability,', `retention_amount` = ',retentionV
                    ,', `perception_amount` = ',perceptionV,', `compensation_amount` = ',compensationV
                    ,', `total_perceptions_retentions` = ',totalRPV,', `free_availability_balance` = ',freeAvBalV
                    ,', `negative_amount` = ',negativeV,', `positive_amount` = ',positiveV,', `total_free_availability` = '
                    ,totalFAV,' WHERE `id` = ',periodLiquidationIvaId,';'
                        ), now() ); */

                END IF;
                
                           
			
           
			/*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva',
	concat('sircrebAmount->',sircrebAmount, ',retentionAmount->', retentionAmount, ',perceptionAmount->', perceptionAmount,
    ', resultValue->', resultValue), 
	now() );            
			insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva',
	concat('sircrebAmount->',sircrebAmount, ',retentionAmount->', retentionAmount, ',perceptionAmount->', perceptionAmount,
    ', resultValue->', resultValue, ',negativeAmount->', negativeAmount), 
	now() );            
			insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva',
	concat('sircrebAmount->',sircrebAmount, ',retentionAmount->', retentionAmount, ',perceptionAmount->', perceptionAmount,
    ', resultValue->', resultValue, ',negativeAmount->', negativeAmount, ', positiveAmount->', positiveAmount), 
	now() );*/
    
			-- tengo que hacer aqui al final tambien la actualizacion dle ultimo registro
				/*ELSE
                  IF periodLiquidationIvaId > 0 THEN
                
				-- actualizo el period liquidation iva, tiene que ser al inicio del cambio o al final de todi...
				   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);
						
					UPDATE `dbtaxes`.`period_liquidation_iva`
						SET `total_debit` = debitValue, `total_credit` = creditValue, `previous_technical` = preVtechnical
                         , `technical_balance` = technicalV, `previous_free_availability` = preVfree_availability
                         , `retention_amount` = retentionV, `perception_amount` = perceptionV, `compensation_amount` = compensationV
                         , `total_perceptions_retentions` = totalRPV, `free_availability_balance` = freeAvBalV
                         , `negative_amount` = negativeV, `positive_amount` = positiveV, `total_free_availability` = totalFAV
						WHERE `id` = periodLiquidationIvaId;

				  END IF;*/
                  
                  /*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iva',
				 concat('fin del if, el valor de done->',done ), 
				 now() );*/
				END IF;


            
			-- UNTIL done END REPEAT;
            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 ;
