
DROP procedure IF EXISTS `liquidacionHijasIIBB`;

DELIMITER $$
CREATE  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
		 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 person_configurations pc where settle_calc_by_coef in (1,2)
			and pz.person_id = pc.person_id)
           and 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)
		 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;
            
            -- consulto si a la configuracion de la persona 
        
			select settle_calc_by_coef into confSettleV from person_configurations where person_id = personId;
            
			/*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iibb',
	concat('personId->',personId, ',confSettleV->', confSettleV), 
	now() ); */

        
			-- es es settle_calc_by_coef===1 uso el baseAmount general
            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;
                  
				/*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iibb',
	concat('baseEqu->',baseEqu, ',baseAmount->', baseAmount), 
	now() ); */

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

            END IF;
            
			 set taxValue := (baseAmount * sifereCoef * (iibbAliquot/100));
             set previousPeriodBalance := (IF (previousPeriodBalance > 0, previousPeriodBalance, 0.00));
             -- error por aqui que da un nulo
             /*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iibb',
	concat('sircrebAmount->',sircrebAmount, ',retentionAmount->', retentionAmount, ',perceptionAmount->', perceptionAmount), 
	now() ); */
            set resultValue := taxValue - (previousPeriodBalance + sircrebAmount + retentionAmount + perceptionAmount);
			/*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iibb',
	concat('resultValue->',resultValue), 
	now() );   */
             set negativeAmount := (IF (resultValue > 0, resultValue, 0.00));
             set positiveAmount := (IF (resultValue < 0 , resultValue*(-1) ,0.00));
             set balanceFinal := (IF (positiveAmount > 0 , 1 ,2));
             
           
			/*insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iibb',
	concat('sircrebAmount->',sircrebAmount, ',retentionAmount->', retentionAmount, ',perceptionAmount->', perceptionAmount,
    ', resultValue->', resultValue), 
	now() );            
			insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iibb',
	concat('sircrebAmount->',sircrebAmount, ',retentionAmount->', retentionAmount, ',perceptionAmount->', perceptionAmount,
    ', resultValue->', resultValue, ',negativeAmount->', negativeAmount), 
	now() );            
			insert into log_debug (function_name, message, date) VALUES('liquidacion_hijas_iibb',
	concat('sircrebAmount->',sircrebAmount, ',retentionAmount->', retentionAmount, ',perceptionAmount->', perceptionAmount,
    ', resultValue->', resultValue, ',negativeAmount->', negativeAmount, ', positiveAmount->', positiveAmount), 
	now() );*/
	
            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 ;
