44 lines
1.4 KiB
PL/PgSQL
44 lines
1.4 KiB
PL/PgSQL
-- Fix: insert_it_service_request_with_number did not accept p_id, causing the
|
|
-- Flutter createRequest RPC call to fail because rpcParams always includes p_id
|
|
-- for offline-replay idempotency. Add p_id as an optional param; if supplied the
|
|
-- row uses that UUID (offline sync), otherwise a new one is generated (normal flow).
|
|
|
|
CREATE OR REPLACE FUNCTION insert_it_service_request_with_number(
|
|
p_event_name text,
|
|
p_services text[],
|
|
p_creator_id uuid,
|
|
p_id uuid DEFAULT NULL,
|
|
p_office_id uuid DEFAULT NULL,
|
|
p_requested_by text DEFAULT NULL,
|
|
p_requested_by_user_id uuid DEFAULT NULL,
|
|
p_status text DEFAULT 'draft'
|
|
)
|
|
RETURNS TABLE(id uuid, request_number text) AS $$
|
|
DECLARE
|
|
v_seq int;
|
|
v_id uuid;
|
|
v_number text;
|
|
BEGIN
|
|
SELECT COALESCE(MAX(
|
|
CAST(NULLIF(regexp_replace(r.request_number, '^ISR-\d{4}-', ''), '') AS int)
|
|
), 0) + 1
|
|
INTO v_seq
|
|
FROM it_service_requests r
|
|
WHERE r.request_number LIKE 'ISR-' || EXTRACT(YEAR FROM now())::text || '-%';
|
|
|
|
v_number := 'ISR-' || EXTRACT(YEAR FROM now())::text || '-' || LPAD(v_seq::text, 4, '0');
|
|
v_id := COALESCE(p_id, gen_random_uuid());
|
|
|
|
INSERT INTO it_service_requests (
|
|
id, request_number, event_name, services,
|
|
creator_id, office_id, requested_by, requested_by_user_id, status
|
|
)
|
|
VALUES (
|
|
v_id, v_number, p_event_name, p_services,
|
|
p_creator_id, p_office_id, p_requested_by, p_requested_by_user_id, p_status
|
|
);
|
|
|
|
RETURN QUERY SELECT v_id, v_number;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|