authen.public

Parameters

Name	Type	Mode
i_trace_id	uuid	IN
i_trace_id	uuid	IN
i_conn_string	text	IN

Definition

DECLARE
  v_interface_code text;
  v_property_id    uuid;
  v_state_rows     jsonb;
  v_rows           int := 0;
  v_conn_name      text;
  v_sql            text;
  v_result         text;
BEGIN
  v_conn_name := 'commit_rate_' || i_trace_id::text;

  IF i_conn_string IS NULL THEN
    RAISE EXCEPTION 'No property connection string provided';
  END IF;

  BEGIN
    EXECUTE format('SELECT dblink_connect(%L, %L)', v_conn_name, i_conn_string);

    -- อ่านข้อมูลจาก intf_sync_channel_logs บน property DB
    SELECT
        l.interface_code,
        (l.params->>'property_id')::uuid,
        (l.metadata->'state_rows')
    INTO
        v_interface_code,
        v_property_id,
        v_state_rows
    FROM dblink(v_conn_name, format($SQL$
        SELECT interface_code, params, metadata
        FROM intf_sync_channel_logs
        WHERE trace_id = '%s'
        ORDER BY id DESC
        LIMIT 1
    $SQL$, i_trace_id)) AS l(interface_code text, params jsonb, metadata jsonb);

    -- Debug: แสดงข้อมูลที่อ่านได้
    PERFORM public.sp_system_notify(
        'COMMIT RATE DEBUG READ',
        format('Trace: %s | interface_code: %s | property_id: %s | state_rows count: %s',
               i_trace_id,
               COALESCE(v_interface_code, '(null)'),
               COALESCE(v_property_id::text, '(null)'),
               CASE WHEN v_state_rows IS NULL THEN 'null'
                    WHEN jsonb_typeof(v_state_rows) <> 'array' THEN 'not_array'
                    ELSE jsonb_array_length(v_state_rows)::text
               END),
        NULL, NULL
    );

    IF v_interface_code IS NULL OR v_property_id IS NULL OR v_state_rows IS NULL
       OR jsonb_typeof(v_state_rows) <> 'array'
       OR jsonb_array_length(v_state_rows) = 0 THEN
      EXECUTE format('SELECT dblink_disconnect(%L)', v_conn_name);
      PERFORM public.sp_system_notify(
          'COMMIT RATE SKIP',
          format('Trace: %s | No state_rows to commit', i_trace_id),
          NULL, NULL
      );
      RETURN 0;
    END IF;

    -- Auto-create intf_rate_state บน property DB (ถ้ายังไม่มี)
    PERFORM dblink_exec(v_conn_name, $DDL$
        CREATE TABLE IF NOT EXISTS public.intf_rate_state (
            interface_code  text        NOT NULL,
            property_id     uuid        NOT NULL,
            rate_plan_id    text        NOT NULL,
            rate_date       date        NOT NULL,
            rate            integer     NOT NULL,
            updated_at      timestamptz NOT NULL DEFAULT now(),
            stop_sell       boolean,
            cta             boolean,
            ctd             boolean,
            min_stay        smallint,
            max_stay        smallint,
            PRIMARY KEY (interface_code, property_id, rate_plan_id, rate_date)
        )
    $DDL$);

    -- INSERT ลง property DB (state_rows มี rate_date เดี่ยว ไม่ใช่ range)
    v_sql := format($SQL$
        INSERT INTO public.intf_rate_state(
            interface_code, property_id, rate_plan_id, rate_date, rate, updated_at
        )
        SELECT %L, %L::uuid, x.rate_plan_id, x.rate_date, x.rate, now()
        FROM jsonb_to_recordset(%L::jsonb) AS x(
            rate_plan_id text,
            rate_date    date,
            rate         int
        )
        ON CONFLICT (interface_code, property_id, rate_plan_id, rate_date)
        DO UPDATE SET
            rate       = EXCLUDED.rate,
            updated_at = now()
    $SQL$,
        v_interface_code,
        v_property_id,
        v_state_rows
    );

    v_result := dblink_exec(v_conn_name, v_sql);
    -- v_result เช่น "INSERT 0 123"
    v_rows := COALESCE(split_part(v_result, ' ', 3)::int, 0);

    EXECUTE format('SELECT dblink_disconnect(%L)', v_conn_name);

  EXCEPTION WHEN OTHERS THEN
    BEGIN
      EXECUTE format('SELECT dblink_disconnect(%L)', v_conn_name);
    EXCEPTION WHEN OTHERS THEN
      NULL;
    END;
    RAISE EXCEPTION 'Failed to commit rate state to property DB: %', SQLERRM;
  END;

  PERFORM public.sp_system_notify(
      'COMMIT RATE SUCCESS',
      format('Trace: %s | interface_code: %s | property_id: %s | rows upserted: %s',
             i_trace_id, v_interface_code, v_property_id, v_rows),
      NULL, NULL
  );

  RETURN v_rows;
END;

sp_commit_rate_state

Parameters

Definition