-- Function: sp_crud_ingreso_vehiculo(character varying, integer, integer, integer, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying[], integer, integer, integer, integer, integer, integer)

-- DROP FUNCTION sp_crud_ingreso_vehiculo(character varying, integer, integer, integer, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying[], integer, integer, integer, integer, integer, integer);

CREATE OR REPLACE FUNCTION sp_crud_ingreso_vehiculo(accion character varying, p_id_ingreso_vehiculo integer, p_aten_numero integer, p_aten_establecimiento integer, p_placa character varying, p_ejes integer, p_empresa_transporte character varying, p_procedencia character varying, p_conductor character varying, p_dueno_carga_documento character varying, p_dueno_carga_nombre character varying, p_servicio character varying, p_peso_ingreso character varying, p_peso_salida character varying, p_descuento_por_hielo character varying, p_peso_a_cobrar character varying, p_estadia character varying, p_costo_de_carga character varying, p_permanencia character varying, p_sobre_estadia character varying, p_subtotal character varying, p_igv character varying, p_total character varying, p_direccion character varying, p_email character varying, p_estado character varying, p_producto character varying[], p_id_chofer integer, p_id_ubicacion integer, p_persona_id integer, p_id_caja integer, p_id_usuario integer, p_id_estacionamiento integer)
  RETURNS character varying 
LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS
$BODY$
declare
	id_ingreso_vehiculo integer;

v_procedencia varchar;

v_conductor varchar;

p_nombre_producto varchar;

p_peso varchar;

p_porcentaje integer;
p_nom_producto varchar[];

begin
case
	accion
	when 'i' then
select
	denominacion
into
	v_procedencia
from
	procedencias
where
	id = p_procedencia::integer;

select
	apellido_paterno || ' ' || apellido_materno || ' ' || nombres
into
	v_conductor
from
	personas
where
	id = p_conductor::integer;

insert
	into
	ingreso_vehiculos (aten_numero,
	aten_establecimiento,
	fecha_ingreso,
	placa,
	ejes,
	empresa_transporte,
	procedencia,
	conductor,
	dueno_carga_documento,
	dueno_carga_nombre,
	servicio,
	peso_ingreso,
	peso_salida,
	descuento_por_hielo,
	peso_a_cobrar,
	estadia,
	costo_de_carga,
	permanencia,
	sobre_estadia,
	subtotal,
	igv,
	total,
	direccion,
	email,
	estado,
	id_chofer,
	id_ubicacion,
	persona_id,
	id_caja,
	id_usuario,
	id_estacionamiento,
	created_at)
values (p_aten_numero,
p_aten_establecimiento,
now(),
p_placa,
p_ejes,
p_empresa_transporte,
v_procedencia,
v_conductor,
p_dueno_carga_documento,
p_dueno_carga_nombre,
p_servicio,
p_peso_ingreso,
p_peso_salida,
p_descuento_por_hielo,
p_peso_a_cobrar,
p_estadia,
p_costo_de_carga,
p_permanencia,
p_sobre_estadia,
p_subtotal,
p_igv,
p_total,
p_direccion,
p_email,
p_estado,
p_id_chofer,
p_id_ubicacion,
p_persona_id,
p_id_caja,
p_id_usuario,
p_id_estacionamiento,
now());

id_ingreso_vehiculo := (
select
	currval('ingreso_vehiculos_id_seq'));

if array_length(p_producto, 1)>0 then for i in array_lower(p_producto, 1) .. array_upper( p_producto, 1) loop p_nombre_producto := p_producto[i][1];

p_peso := p_producto[i][2];

p_porcentaje := p_producto[i][3];

insert
	into
	producto_cargas(ingreso_vehiculos_id,
	producto,
	peso,
	created_at,
	porcentaje)
values (id_ingreso_vehiculo,
p_nombre_producto,
to_number(p_peso, '9999999999.99'),
now(),
p_porcentaje);

end loop;

end if;
when 'u' then
	update
		ingreso_vehiculos
	set
		estado = p_estado,
		fecha_salida = now(),
		updated_at = now(),
		peso_salida = p_peso_salida,
		descuento_por_hielo = p_descuento_por_hielo,
		peso_a_cobrar = p_peso_a_cobrar,
		estadia = p_estadia,
		costo_de_carga = p_costo_de_carga,
		permanencia = p_permanencia,
		sobre_estadia = p_sobre_estadia,
		subtotal = p_subtotal,
		igv = p_igv,
		total = p_total,
		id_caja=p_id_caja, 
		id_usuario=p_id_usuario 
	where
		id = p_id_ingreso_vehiculo;

	id_ingreso_vehiculo := p_id_ingreso_vehiculo;

	--delete from producto_cargas where ingreso_vehiculos_id=p_id_ingreso_vehiculo;

	if array_length(p_producto, 1)>0 then 
		for i in array_lower(p_producto, 1) .. array_upper( p_producto, 1) loop 

			p_nombre_producto := p_producto[i][1];
			p_peso := p_producto[i][2];
			p_porcentaje := p_producto[i][3];
			p_nom_producto[i] = p_nombre_producto;

			if(select count(1) from producto_cargas where ingreso_vehiculos_id=id_ingreso_vehiculo And producto=p_nombre_producto)>0 Then
				update producto_cargas set 
				peso = to_number(p_peso, '9999999999.99'),
				porcentaje = p_porcentaje
				Where ingreso_vehiculos_id=id_ingreso_vehiculo And producto=p_nombre_producto;
			Else
				insert into producto_cargas(ingreso_vehiculos_id,producto,peso,created_at,porcentaje)
				values (id_ingreso_vehiculo,p_nombre_producto,to_number(p_peso, '9999999999.99'),now(),p_porcentaje);
			End If;
		end loop;
	end if;

	Delete From producto_cargas where ingreso_vehiculos_id=id_ingreso_vehiculo and Not producto = Any(p_nom_producto);

end
case;
	
	return id_ingreso_vehiculo;

/*EXCEPTION
	WHEN OTHERS THEN
        id_caja_ingreso:=-1;
	return id_caja_ingreso;
	*/
end;

$BODY$;
