aonestar.public

Parameters

Name	Type	Mode
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;
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);
    
    -- อ่านข้อมูลจาก aonestar.intf_sync_channel_logs
    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);
    
    -- ปิด connection
    EXECUTE format('SELECT dblink_disconnect(%L)', v_conn_name);
    
  EXCEPTION WHEN OTHERS THEN
    -- ปิด connection ถ้ามี error
    BEGIN
      EXECUTE format('SELECT dblink_disconnect(%L)', v_conn_name);
    EXCEPTION WHEN OTHERS THEN
      NULL;
    END;
    
    RAISE EXCEPTION 'Failed to read from remote intf_sync_channel_logs: %', SQLERRM;
  END;

  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
    RETURN 0;
  END IF;

  INSERT INTO public.intf_rate_state(
      interface_code, property_id, rate_plan_id, rate_date,
      rate, updated_at
  )
  SELECT
      v_interface_code,
      v_property_id,
      x.rate_plan_id,
      x.rate_date,
      x.rate,
      now()
  FROM jsonb_to_recordset(v_state_rows) 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();

  GET DIAGNOSTICS v_rows = ROW_COUNT;
  RETURN v_rows;
END;

sp_commit_rate_state

Parameters

Definition