mirror of
https://origin.cursor.com/mrdevmx/panels.git
synced 2026-10-09 13:23:17 +00:00
31a7c1cbf5
El ORDER BY del listado de personas refería la tabla w fuera del subquery (42P01). El frente del gafete ya no imprime Construcciones Arctec/Zendala: usa el nombre comercial de la empresa del proyecto. Co-authored-by: alberto.martinez <alberto.martinez@mrdev.mx>
80 lines
2.2 KiB
PL/PgSQL
80 lines
2.2 KiB
PL/PgSQL
--liquibase formatted sql
|
|
-- PANELS · core · fn_badge_job_get: ORDER BY del subquery de personas
|
|
|
|
--changeset panel:core-032-fn-badge-job-get-people-order splitStatements:false
|
|
CREATE OR REPLACE FUNCTION core.fn_badge_job_get(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = core
|
|
AS $$
|
|
DECLARE
|
|
v_job_id bigint := COALESCE(
|
|
NULLIF(payload->>'job_id', '')::bigint,
|
|
NULLIF(payload->>'id', '')::bigint
|
|
);
|
|
v_job jsonb;
|
|
v_people jsonb;
|
|
BEGIN
|
|
IF v_job_id IS NULL THEN
|
|
RETURN core.rpc_err(
|
|
'VALIDATION',
|
|
'fn_badge_job_get: job_id es obligatorio',
|
|
jsonb_build_object('fn', 'fn_badge_job_get', 'field', 'job_id')
|
|
);
|
|
END IF;
|
|
|
|
SELECT to_jsonb(x)
|
|
INTO v_job
|
|
FROM (
|
|
SELECT
|
|
j.*,
|
|
p.code AS project_code,
|
|
p.name AS project_name
|
|
FROM badge_jobs j
|
|
JOIN projects p ON p.id = j.project_id
|
|
WHERE j.id = v_job_id
|
|
) x;
|
|
|
|
IF v_job IS NULL THEN
|
|
RETURN core.rpc_err(
|
|
'NOT_FOUND',
|
|
format('fn_badge_job_get: job de gafetes id=%s no encontrado', v_job_id),
|
|
jsonb_build_object('fn', 'fn_badge_job_get', 'job_id', v_job_id)
|
|
);
|
|
END IF;
|
|
|
|
SELECT COALESCE(jsonb_agg(to_jsonb(p) ORDER BY p.last_name_p, p.first_name), '[]'::jsonb)
|
|
INTO v_people
|
|
FROM (
|
|
SELECT
|
|
bp.job_id,
|
|
bp.worker_id,
|
|
bp.delivered,
|
|
bp.delivered_at,
|
|
w.first_name,
|
|
w.last_name_p,
|
|
w.last_name_m,
|
|
w.position
|
|
FROM badge_job_people bp
|
|
JOIN workers w ON w.id = bp.worker_id
|
|
WHERE bp.job_id = v_job_id::integer
|
|
) p;
|
|
|
|
RETURN core.rpc_ok(
|
|
jsonb_build_object('job', v_job, 'people', v_people),
|
|
format('Job de gafetes #%s cargado: %s persona(s) en el lote',
|
|
v_job_id, jsonb_array_length(v_people)),
|
|
jsonb_build_object(
|
|
'fn', 'fn_badge_job_get',
|
|
'job_id', v_job_id,
|
|
'people_count', jsonb_array_length(v_people)
|
|
)
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_badge_job_get', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:core-032-fn-badge-job-get-grant endDelimiter:; splitStatements:true
|
|
GRANT EXECUTE ON FUNCTION core.fn_badge_job_get(jsonb) TO panels_core_app;
|