CREATE OR REPLACE FUNCTION mdm.codelco_get_inactive_materials(
stages integer[],
inactivities integer[],
organization integer DEFAULT NULL::integer,
creation_user uuid DEFAULT NULL::uuid,
material_id integer DEFAULT NULL::integer
)
RETURNS SETOF mdm.codelco_material_creation
LANGUAGE plpgsql
AS $function$
DECLARE
query text := 'SELECT * FROM mdm.codelco_material_creation WHERE ';
array_size integer := array_length(stages, 1);
inactivities_size integer := array_length(inactivities, 1);
position integer;
stage integer;
BEGIN
IF (stages IS NULL OR array_length(stages, 1) = 0) OR (inactivities IS NULL OR array_length(inactivities, 1) = 0) THEN
RAISE EXCEPTION 'Las listas no pueden ser null o vacias.';
RETURN;
ELSEIF array_size <> inactivities_size THEN
RAISE EXCEPTION 'Siempre debe de coincidir un stage con un dia de inactividad. El tamaño de las listas son diferentes.';
RETURN;
END IF;
-- Si material_id no es null siempre habrá un stage y un numero de inactividad al que pertenece
IF material_id IS NOT NULL THEN
query := query || '(id = ' || material_id ||' AND date_part(''day'', current_timestamp - updated_at)::integer >= ' || inactivities[1] || ')' ;
RETURN QUERY EXECUTE query;
RETURN;
END IF;
-- Solo hay un elemento en la lista
IF array_length(stages, 1) = 1 THEN
query := query || ' step_id = ' || stages[1];
IF organization IS NOT NULL THEN
query := query || ' AND organization_division_id = ' || organization;
END IF;
IF creation_user IS NOT NULL THEN
query := query || ' AND creation_user_id = ''' || creation_user || '''';
END IF;
query := query || ' AND date_part(''day'', current_timestamp - updated_at)::integer >= ' || inactivities[1];
ELSE
-- Hay más de un elemento en la lista
position := 1;
FOREACH stage IN ARRAY stages
LOOP
query := query || ' (step_id = ' || stage;
-- Si organizacion no es null se agrega a la query
IF organization IS NOT NULL THEN
query := query || ' AND organization_division_id = ' || organization;
END IF;
-- Si creation_user no es null se agrega a la query
IF creation_user IS NOT NULL THEN
query := query || ' AND creation_user_id = ''' || creation_user || '''';
END IF;
-- Si estoy en la ultima posicion del array no va el OR
IF stage = stages[array_size] THEN
query := query || ' AND date_part(''day'', current_timestamp - updated_at)::integer >= ' || inactivities[position] || ')' ;
ELSE
query := query || ' AND date_part(''day'', current_timestamp - updated_at)::integer >= ' || inactivities[position] || ') OR ' ;
END IF;
position := position + 1;
END LOOP;
END IF;
RETURN QUERY EXECUTE query;
END;
$function$