WITH config AS (
SELECT (WITH grados AS (
SELECT (elem1->>'id_grado')::INTEGER AS id_grado,
(elem1->>'id_libreta')::INTEGER AS id_libreta,
(SELECT JSONB_BUILD_OBJECT(
'areas', elem1->'config'->>'areas',
'firma1', elem1->'config'->>'firma1',
'firma2', elem1->'config'->>'firma2',
'is_logo', (elem1->'config'->>'is_logo')::BOOLEAN,
'is_escudo', (elem1->'config'->>'is_escudo')::BOOLEAN,
'conducta', (
SELECT JSONB_BUILD_OBJECT(
'active', (elem1->'config'->'conducta'->>'active')::BOOLEAN,
'detalle', (elem1->'config'->'conducta'->>'detalle')::BOOLEAN,
'leyenda', (elem1->'config'->'conducta'->>'leyenda')::BOOLEAN
)
),
'eva_ppff', (
SELECT JSONB_BUILD_OBJECT(
'active', (elem1->'config'->'eva_ppff'->>'active')::BOOLEAN,
'detalle', (elem1->'config'->'eva_ppff'->>'detalle')::BOOLEAN,
'leyenda', (elem1->'config'->'eva_ppff'->>'leyenda')::BOOLEAN
)
),
'detalle_asistencia', (
SELECT JSONB_BUILD_OBJECT(
'temprano', true,
'tardanza', true,
'falta', true,
'tardanza_justificada', true,
'falta_justificada', true
)
),
'asistencia', (elem1->'config'->>'asistencia')::BOOLEAN,
'asignaturas', (elem1->'config'->>'asignaturas')::BOOLEAN,
'competencias', (elem1->'config'->>'competencias')::BOOLEAN,
'height_calif', (elem1->'config'->>'height_calif'),
'orden_merito', (elem1->'config'->>'orden_merito')::BOOLEAN,
'ocultar_prom_final', (elem1->'config'->>'ocultar_prom_final')::BOOLEAN,
'info_adicional', (
SELECT JSONB_BUILD_OBJECT(
'dre' , (SELECT JSONB_BUILD_OBJECT('value', null, 'active', FALSE)),
'ugel' , (SELECT JSONB_BUILD_OBJECT('value', null, 'active', FALSE)),
'cod_local' , (SELECT JSONB_BUILD_OBJECT('value', null, 'active', FALSE)),
'cod_modular' , (SELECT JSONB_BUILD_OBJECT('value', null, 'active', FALSE))
)
),
'padding_top_head', (elem1->'config'->>'padding_top_head'),
'cant_letras_x_fila', (elem1->'config'->>'cant_letras_x_fila')::INTEGER,
'orientacion', 'portrait',
'desc_reporte' , (elem1->'config'->>'desc_reporte'),
'color_template', (elem1->'config'->>'color_template'),
'desc_comentario', (elem1->'config'->>'desc_comentario')
)) AS config
FROM JSONB_ARRAY_ELEMENTS(elem->'grados')
WITH ORDINALITY ARR(elem1, pos1)
)
SELECT ARRAY_TO_JSON(ARRAY_AGG(tab))::JSONB
FROM grados tab
) AS grados,
(elem->>'id_sede')::integer AS id_sede,
(elem->>'id_tipo_prog')::integer AS id_tipo_prog
FROM JSONB_ARRAY_ELEMENTS((SELECT json_config FROM notas.config_notas WHERE id_config = 2))
WITH ORDINALITY ARR(elem, pos)
)
UPDATE notas.config_notas SET json_config = (SELECT ARRAY_TO_JSON(ARRAY_AGG(tab))::JSONB FROM config tab) WHERE id_config = 2;
UPDATE notas.libreta SET config = config || JSONB_BUILD_OBJECT(
'detalle_asistencia', (
SELECT JSONB_BUILD_OBJECT(
'temprano', true,
'tardanza', true,
'falta', true,
'tardanza_justificada', true,
'falta_justificada', true
)
)
);
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.