CREATE OR REPLACE FUNCTION f_au_liquidacion() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	total	numeric;
	dias	numeric;
BEGIN
    if int4ne(NEW.l_estado,OLD.l_estado) THEN 
	if int4ne(NEW.l_estado,999) THEN 
	    update compliquid set cl_estado=NEW.l_estado where cl_idliq=NEW.l_id;
	    delete from liqui where lq_idliq=NEW.l_id;
	    delete from compliq where clq_idliq=NEW.l_id;
	    delete from auxint where ai_idliq=NEW.l_id;
	END IF;

      else

	update compliquid set cl_estado=NEW.l_estado where cl_idliq=NEW.l_id;
	update liqui set lq_estado=NEW.l_estado where lq_idliq=NEW.l_id;
	update compliq set clq_estado=NEW.l_estado where clq_idliq=NEW.l_id;
    END IF;

RETURN NEW;
END;$$;

ALTER FUNCTION public.f_au_liquidacion() OWNER TO sanjor;




drop TRIGGER au_liquidacion ON liquidacion;

CREATE TRIGGER au_liquidacion
    AFTER UPDATE ON liquidacion
    FOR EACH ROW
    EXECUTE PROCEDURE f_au_liquidacion();




CREATE OR REPLACE FUNCTION f_bd_liquidacion() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    DECLARE
    cuantos	int4;
    BEGIN
	delete from compliquid where cl_idliq=OLD.l_id;
	delete from liqui where lq_idliq=OLD.l_id;
	delete from compliq where clq_idliq=OLD.l_id;
	delete from auxint where ai_idliq=OLD.l_id;
    RETURN OLD;
    END;$$;


ALTER FUNCTION public.f_bd_liquidacion() OWNER TO sanjor;



drop  TRIGGER bd_liquidacion ON liquidacion;

CREATE TRIGGER bd_liquidacion
    BEFORE DELETE ON liquidacion
    FOR EACH ROW
    EXECUTE PROCEDURE f_bd_liquidacion();









CREATE OR REPLACE FUNCTION ultcie(integer) RETURNS date
    LANGUAGE sql
    AS $_$select l_feccie from liquidacion where l_estado=1 and l_centro= $1 order by 1 desc limit 1;$_$;

ALTER FUNCTION public.ultcie(integer) OWNER TO sanjor;


CREATE OR REPLACE FUNCTION cargocint(integer) RETURNS integer
    LANGUAGE sql
    AS $_$select car_genint from cargos where car_id= $1 ;$_$;

ALTER FUNCTION public.cargocint(integer) OWNER TO sanjor;


CREATE OR REPLACE FUNCTION intercentro(integer) RETURNS numeric
    LANGUAGE sql
    AS $_$select int_valor from interes where int_centro= $1 order by int_fecha desc limit 1;$_$;

ALTER FUNCTION public.intercentro(integer) OWNER TO sanjor;







CREATE OR REPLACE FUNCTION liquido(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
    DECLARE
	idliq		 ALIAS FOR $1; 
	codemp		 integer;
	idptov		 integer;
	centro		 integer;
	division	 integer;
	anio		 integer;
	mes		 integer;
	feccie		 date;
	fecvto		 date;
	comprob		 integer;
	numero		 integer;
	operador	 integer;
	idcopr		 integer;
	importe		 numeric(15,2);
	retval 		 integer;
	registro	 record;
	regi		 record;
	regis		 record;
    BEGIN

    retval:=0;

    select l_codemp,l_idptov,l_centro,l_division,l_anio,l_mes,l_feccie,l_fecvto,l_comprob,l_numero,l_operador 
	into codemp,idptov,centro,division,anio,mes,feccie,fecvto,comprob,numero,operador from liquidacion where l_id=idliq; 


    for regis in select * from comprop where cmpr_centro=centro and cmpr_estado=1 
	loop
	    insert into compliq (clq_codemp,clq_idptov,clq_centro,clq_division,clq_anio,clq_mes,clq_fecha,clq_fecvto,clq_comprob,clq_numero,clq_operad,clq_rubcar,clq_cargo,clq_leyen,clq_canti,clq_interes,clq_precio,clq_importe,clq_idcopr)
		values (codemp,idptov,centro,division,anio,mes,feccie,fecvto,comprob,numero,operador,regis.cmpr_rubcar,regis.cmpr_cargo,regis.cmpr_leyen,regis.cmpr_canti,0,regis.cmpr_precio,regis.cmpr_importe,regis.cmpr_idcopr);
	end loop;


    for registro in select * from compliquid where cl_idliq=idliq
	loop
	for regi in select * from coprop where cop_centro=centro and cop_estado=1
	    loop
		if int4eq(registro.cl_porc,1) then
		    importe:=registro.cl_importe * regi.cop_porc1;
		  else
		    if int4eq(registro.cl_porc,2) then
			importe:=registro.cl_importe * regi.cop_porc2;
		      else
			if int4eq(registro.cl_porc,3) then
			    importe:=registro.cl_importe * regi.cop_porc3;
			  else
			    if int4eq(registro.cl_porc,4) then
				importe:=registro.cl_importe * regi.cop_porc4;
			      else
				if int4eq(registro.cl_porc,5) then
				    importe:=registro.cl_importe * regi.cop_porc5;
				end if;
			    end if;
			end if;
		    end if;
		end if;

		if numeric_ne(importe,0) then
		    insert into compliq (clq_codemp,clq_idptov,clq_centro,clq_division,clq_anio,clq_mes,clq_fecha,clq_fecvto,clq_comprob,clq_numero,clq_operad,clq_rubcar,clq_cargo,clq_leyen,clq_canti,clq_interes,clq_precio,clq_importe,clq_idcopr)
			values (codemp,idptov,centro,division,anio,mes,feccie,fecvto,comprob,numero,operador,registro.cl_rubcar,registro.cl_cargo,registro.cl_leyen,1,0,importe,importe,regi.cop_id);
		end if;

	    importe:=0;
	    end loop;
	end loop;

    select calc_intliq(idliq) into retval;

    RETURN retval;
    END;$_$;


ALTER FUNCTION public.liquido(integer) OWNER TO sanjor;







CREATE OR REPLACE FUNCTION calc_intliq(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
    DECLARE
	idliq		 ALIAS FOR $1; 
	codemp		 integer;
	idptov		 integer;
	centro		 integer;
	division	 integer;
	anio		 integer;
	mes		 integer;
	feccie		 date;
	fecvto		 date;
	comprob		 integer;
	numero		 integer;
	operador	 integer;
	idultcie	 integer;
	impgia		 numeric(15,2);
	cobranz		 numeric(15,2);
	import		 numeric(15,2);
	tasaint		 numeric(15,5);
	interes		 numeric(15,2);

	rubro		 integer;
	cargo		 integer;
	leyenda		 character varying(240);

	importe		 numeric(15,2);
	retval 		 integer;
	registro	 record;
	regi		 record;
	regis		 record;
BEGIN

retval:=1;
importe:=0;

select l_codemp,l_idptov,l_centro,l_division,l_anio,l_mes,l_feccie,l_fecvto,l_comprob,l_numero,l_operador 
    into codemp,idptov,centro,division,anio,mes,feccie,fecvto,comprob,numero,operador from liquidacion where l_id=idliq; 

select l_id into idultcie from liquidacion where l_id!=idliq and l_estado=1 order by l_feccie desc limit 1;

select intercentro(centro) into tasaint;
if tasaint is null then
    tasaint:=0;
end if;

select vari_valor into cargo from varambi where int4eq(idptov,vari_idptov) and int4eq(codemp,vari_codemp) and bpchareq('CARGOINT',vari_codigo);
if found then
    select car_rubcar, car_leyen into rubro, leyenda from cargos where car_id=cargo;

    if idultcie is not null then
	for regi in select * from auxint where ai_idliq=idultcie
	    loop
		impgia:=regi.impgi;
		if impgia is null then
		    impgia:=0;
		end if;

		select sum(clq_importe) into cobranz from compliq where clq_importe<0 and clq_anio=anio and clq_mes=mes and clq_estado=1 and cargocint(clq_cargo)=1 and clq_idcopr=regi.ai_idcopr;
		if cobranz is null then
		    cobranz:=0;
		end if;

		import:= impgia - cobranz ;

		if numeric_gt(import,0) then
		    interes:=import * tasaint / 100;
		end if;

		if numeric_gt(interes,0) then
		    insert into compliq (clq_codemp,clq_idptov,clq_centro,clq_division,clq_anio,clq_mes,clq_fecha,clq_fecvto,clq_comprob,clq_numero,clq_operad,clq_rubcar,clq_cargo,clq_leyen,clq_canti,clq_interes,clq_precio,clq_importe,clq_idcopr)
			values (codemp,idptov,centro,division,anio,mes,feccie,fecvto,comprob,numero,operador,rubro,cargo,leyenda,1,interes,1,interes,regi.ai_idcopr);
		end if;

	    interes:=0;
	    end loop;
    end if;
end if;

RETURN retval;
END;$_$;


ALTER FUNCTION public.calc_intliq(integer) OWNER TO sanjor;





