
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, 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
			 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 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
			 
			 order by 1,2,3, 4 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, activityId, periodId, creditValue, debitValue;
            
            IF NOT done THEN
				set paso := paso +1;
            
			-- 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;
	

                
			  set preVtechnical := (IF (preVtechnical is not null, preVtechnical, 0.00));
              set preVfree_availability := (IF (preVfree_availability is not null, preVfree_availability, 0.00));
  
                
			  -- 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
				   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();

            
            -- al final el detalle de cada uno en 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)
                
				(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;


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