-- Function: sp_crud_caja_ingreso(character varying, integer, integer, character varying, character varying, character varying, character varying)

-- DROP FUNCTION sp_crud_caja_ingreso(character varying, integer, integer, character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION sp_crud_caja_ingreso(accion character varying, p_id_usuario integer, p_id_caja_ingreso integer, p_id_caja integer, p_saldo_inicial character varying, p_total_recaudado character varying, p_saldo_total character varying, p_estado character varying)
  RETURNS character varying AS
$BODY$
declare
	--id_caja_ingreso integer;
	cantidad_caja integer;
begin

	Case accion
		When 'i' then
			select count(id) into cantidad_caja from caja_ingresos where id_caja=p_id_caja And estado = '1';
			if cantidad_caja=0 Then
				Insert Into caja_ingresos (id_usuario, id_caja, saldo_inicial, total_recaudado,saldo_total,fecha_inicio,estado,created_at)
				Values (p_id_usuario,p_id_caja,to_number( p_saldo_inicial,'9999999999.99'),to_number( p_total_recaudado,'9999999999.99'),to_number( p_saldo_total,'9999999999.99'),now(),p_estado,now());
				p_id_caja_ingreso := (SELECT currval('caja_ingresos_id_seq'));
			End if;
		When 'u' then
			update caja_ingresos t1 set 
			estado=0,
			--total_recaudado=(select coalesce(Sum(fac_total),0) from facturas where fac_caja_id=p_id_caja),
			total_recaudado=(select coalesce(Sum(fac_total),0) from facturas fac where fac.fac_anulado='N' And fac.fac_caja_id=t1.id_caja And fac.fac_fecha >= t1.fecha_inicio And fac.fac_fecha <= (case when t1.fecha_fin is null then now() else t1.fecha_fin end)),
			--saldo_total=((select coalesce(Sum(fac_total),0) from facturas where fac_caja_id=p_id_caja)+saldo_inicial),
			saldo_total=((select coalesce(Sum(fac_total),0) from facturas fac where fac.fac_anulado='N' And fac.fac_caja_id=t1.id_caja And fac.fac_fecha >= t1.fecha_inicio And fac.fac_fecha <= (case when t1.fecha_fin is null then now() else t1.fecha_fin end))+t1.saldo_inicial),
			fecha_fin=now(),
			updated_at=now() 
			where id=p_id_caja_ingreso;
		When 'ul' then
			update caja_ingresos set 
			id_usuario_contabilidad=p_id_usuario,
			saldo_liquidado=to_number( p_saldo_total,'9999999999.99'),
			observacion=p_estado
			where id=p_id_caja_ingreso;
			
		End Case;
				
	return p_id_caja_ingreso;
	/*EXCEPTION
	WHEN OTHERS THEN
        id_caja_ingreso:=-1;
	return id_caja_ingreso;
	*/
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
