CREATE OR REPLACE FUNCTION notas.__configuracion_01__get_data_config(
__p_id_sede integer)
RETURNS jsonb
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
---------------------CONSTANTES
ERROR_GENERICO CONSTANT CHARACTER VARYING := 'Hubo un error';
ANIO_LECTIVO CONSTANT CHARACTER VARYING := '2';
---------------------------------------------------------
__msj_excep CHARACTER VARYING;
__JSONB_RPTA JSONB DEFAULT '{}';
__cmb_sedes JSONB DEFAULT '[]';
__json_config_calif JSONB DEFAULT '[]';
__json_extem JSONB DEFAULT '[]';
__json_config_deudas JSONB DEFAULT '{}';
__json_config_conducta JSONB DEFAULT '{}';
__json_config_libreta JSONB DEFAULT '{}';
BEGIN
IF __p_id_sede IS NULL THEN
SELECT ARRAY_TO_JSON(
ARRAY_AGG(
JSONB_BUILD_OBJECT(
'nid_sede', nid_sede,
'desc_sede', desc_sede
) ORDER BY desc_sede
)
)
FROM sede
INTO __cmb_sedes
WHERE flg_extra IS NULL
AND nid_sede <> 0;
END IF;
IF __cmb_sedes IS NULL THEN
RAISE EXCEPTION USING ERRCODE = 'SMILE', MESSAGE = 'No hay sedes registradas';
END IF;
SELECT ARRAY_TO_JSON(
ARRAY_AGG(
JSONB_BUILD_OBJECT(
'year_id' , t.year,
'year_desc', t.year,
'niveles' , (SELECT ARRAY_TO_JSON(
ARRAY_AGG(
JSONB_BUILD_OBJECT(
'nid_nivel', n.nid_nivel,
'desc_nivel', n.desc_nivel
) ORDER BY n.nid_nivel
)
)
FROM (SELECT JSONB_ARRAY_ELEMENTS((s.json_config->>'niveles')::JSONB) AS niveles
FROM (SELECT JSONB_ARRAY_ELEMENTS(config_nivel_grado) AS json_config
FROM sede s
WHERE s.nid_sede = COALESCE(__p_id_sede, (__cmb_sedes->0->>'nid_sede')::INTEGER)) s
WHERE s.json_config->>'year' = t.year::TEXT
AND s.json_config->>'tipo_programa' = ANIO_LECTIVO) tn,
nivel n
WHERE n.nid_nivel = (tn.niveles->>'id_nivel')::INTEGER
)
) ORDER BY t.year DESC
)
)
FROM (SELECT year
FROM plan_inst.programa p
WHERE p._id_sede = COALESCE(__p_id_sede, (__cmb_sedes->0->>'nid_sede')::INTEGER)
GROUP BY year) t
INTO __json_config_calif;
SELECT ARRAY_TO_JSON(
ARRAY_AGG(
JSONB_BUILD_OBJECT(
'id_tipo_programa', t.id_tipo_programa,
'desc_tipo_programa', t.desc_tipo_programa,
'periodos', (SELECT ARRAY_TO_JSON(
ARRAY_AGG(
JSONB_BUILD_OBJECT(
'id_periodo', p.id_periodo,
'desc_periodo', p.desc_periodo
)ORDER BY p.orden
)
)
FROM plan_inst.periodo p
WHERE p._id_sede = COALESCE(__p_id_sede, (__cmb_sedes->0->>'nid_sede')::INTEGER)
AND p.year_acad = EXTRACT(YEAR FROM CURRENT_DATE)
AND p.tipo_programa = t.id_tipo_programa
)
)
)
)
FROM (SELECT tp.id_tipo_programa,
tp.desc_tipo_programa
FROM plan_inst.periodo p,
plan_inst.tipo_programa tp
WHERE p._id_sede = COALESCE(__p_id_sede, (__cmb_sedes->0->>'nid_sede')::INTEGER)
AND p.year_acad = EXTRACT(YEAR FROM CURRENT_DATE)
AND p.tipo_programa = tp.id_tipo_programa
GROUP BY tp.id_tipo_programa, tp.desc_tipo_programa
ORDER BY tp.id_tipo_programa) t
INTO __json_extem;
SELECT COALESCE((json_config->((SELECT pos-1 AS idx
FROM JSONB_ARRAY_ELEMENTS(json_config)
WITH ORDINALITY ARR(elem, pos)
WHERE (elem->>'nid_sede')::TEXT = __p_id_sede::TEXT)::INTEGER))::JSONB, JSONB_BUILD_OBJECT('disabled_deudores', false, 'cant_deudas', 1))
FROM notas.config_notas
INTO __json_config_deudas
WHERE id_config = 5;
SELECT ARRAY_TO_JSON(
ARRAY_AGG(
JSONB_BUILD_OBJECT(
'year_id' , t.year,
'year_desc', t.year,
'programas' , (SELECT ARRAY_TO_JSON(
ARRAY_AGG(
JSONB_BUILD_OBJECT(
'id_tipo_programa', tn.id_tipo_programa,
'desc_tipo_programa', tn.desc_tipo_programa
) ORDER BY tn.desc_tipo_programa
)
)
FROM (SELECT tp.id_tipo_programa,
tp.desc_tipo_programa
FROM plan_inst.periodo p,
plan_inst.tipo_programa tp
WHERE p._id_sede = COALESCE(__p_id_sede, (__cmb_sedes->0->>'nid_sede')::INTEGER)
AND p.year_acad = t.year
AND p.tipo_programa = tp.id_tipo_programa
GROUP BY tp.id_tipo_programa, tp.desc_tipo_programa
ORDER BY tp.id_tipo_programa) tn
)
) ORDER BY t.year DESC
)
)
FROM (SELECT year
FROM plan_inst.programa p
WHERE p._id_sede = COALESCE(__p_id_sede, (__cmb_sedes->0->>'nid_sede')::INTEGER)
GROUP BY year) t
INTO __json_config_conducta;
-- Configuración de libreta
WITH config_sede AS (
SELECT elem->'grados' AS grados,
pos-1 AS pos_sede
FROM JSONB_ARRAY_ELEMENTS((SELECT json_config FROM notas.config_notas WHERE id_config = 2))
WITH ORDINALITY ARR(elem,pos)
WHERE (elem->>'id_sede')::INTEGER = COALESCE(__p_id_sede, (__cmb_sedes->0->>'nid_sede')::INTEGER)
AND (elem->>'id_tipo_prog')::INTEGER = 2
), config_grado AS (
SELECT n.nid_nivel AS id_nivel,
n.desc_nivel,
g.nid_grado AS id_grado,
CONCAT(g.abvr,' ',n.desc_nivel) desc_grado,
elem->'config' AS config,
(elem->>'id_libreta')::INTEGER AS id_libreta,
(COALESCE(NULLIF(elem->>'grados_replicados','')::INTEGER[],'{}'::INTEGER[])) AS grados_replicados,
pos-1 AS pos_grado
FROM JSONB_ARRAY_ELEMENTS((SELECT grados FROM config_sede))
WITH ORDINALITY ARR(elem,pos),
grado g,
nivel n
WHERE g.nid_grado = (elem->>'id_grado')::INTEGER
AND n.nid_nivel = g.id_nivel
), group_nivel AS (
SELECT id_nivel,
desc_nivel,
(SELECT pos_sede FROM config_sede) AS pos_sede,
ARRAY_TO_JSON(
ARRAY_AGG(
JSON_BUILD_OBJECT(
'id_grado' , id_grado,
'desc_grado' , desc_grado,
'config' , config,
'grados_replicados' , grados_replicados,
'pos_grado' , pos_grado,
'id_libreta' , id_libreta
)
)
) AS grados
FROM config_grado
GROUP BY id_nivel, desc_nivel
)
SELECT ARRAY_TO_JSON(ARRAY_AGG(tab))::JSONB
INTO __json_config_libreta
FROM group_nivel tab;
RETURN JSONB_BUILD_OBJECT('status' , '0',
'__cmb_sedes' , __cmb_sedes,
'__json_config_calif' , __json_config_calif,
'__json_extem' , __json_extem,
'__json_config_deudas' , __json_config_deudas,
'__json_config_conducta', __json_config_conducta,
'__json_config_libreta' , __json_config_libreta);
EXCEPTION
WHEN SQLSTATE 'SMILE' THEN
__JSONB_RPTA = JSONB_BUILD_OBJECT('status', 1, 'msj' , SQLERRM);
RETURN __JSONB_RPTA;
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS __msj_excep = PG_EXCEPTION_CONTEXT;
__JSONB_RPTA := JSONB_BUILD_OBJECT('msj' , ERROR_GENERICO,
'status' , '1',
'stack_error' , CONCAT(SQLERRM, ' - ', __msj_excep));
RETURN __JSONB_RPTA;
END;
$BODY$;
Be the first to comment
You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.