authen.public

Parameters

Name	Type	Mode
batch_id	integer	IN
user_name	text	IN (DEFAULT NULL)

Definition

DECLARE
 batch record;
 item record;
 file_path text;
 report_count integer := 0;
 msg_title text;
 report_list text := '';
 recipients text[] := NULL;
 url text;
 sys_date date;
 report_params jsonb;
 err_sqlstate text;
 err_detail text;
 err_hint text;
 err_context text;
 err_message text;
 message text := '';
 time_start timestamp;
 execute_time text := '';
 access_token text;
 temp_log_id bigint;
 is_dev boolean := public.is_dev_server();
 workshift varchar;
 shift_num integer;
 authen_host text;
BEGIN
 authen_host := NULLIF(
 split_part(
 regexp_replace(
 public.fn_get_config(NULL::integer, 'API_URL'),
 '^https?://',
 '',
 'i'
 ),
 '/',
 1
 ),
 ''
 );

 IF authen_host IS NULL THEN
 RAISE EXCEPTION 'Global API_URL is required for authenticated Authen self-connection';
 END IF;
 SELECT br.*, p.name::text AS prop_name, p.code::text AS prop_code,
 p.id AS prop_id, p.prop_group_id
 INTO batch
 FROM public.batch_report br
 LEFT JOIN public.property p ON p.id = br.prop_id
 WHERE br.id = $1;

 IF batch.id IS NULL THEN
 RAISE EXCEPTION 'Batch report not found: %', batch_id;
 END IF;

 SELECT t.log_id
 INTO temp_log_id
 FROM public.dblink(
 public.fn_connection(current_database(), authen_host),
 format(
 'SELECT public.fn_create_temp_report_token(%L, %s)',
 user_name,
 batch.prop_id
 )
 ) AS t(log_id bigint);

 SELECT w.login_token::text
 INTO access_token
 FROM public.work_log w
 WHERE w.id = temp_log_id
 AND w.prop_id = batch.prop_id
 AND w.device_name = 'Background Report Export'
 AND w.logout_time IS NULL;

 IF access_token IS NULL THEN
 RAISE EXCEPTION 'Active temporary report token not found: %', temp_log_id;
 END IF;

 sys_date := public.fn_system_date(batch.prop_id);

 file_path := CASE batch.output_type
 WHEN 'file' THEN 'report/file/'
 || trim('/' FROM public.fn_format_date_path(batch.output_target, sys_date))
 WHEN 'email' THEN 'report/temp/' || gen_random_uuid() || '/'
 || to_char(current_timestamp, 'YYYYMMDD-HH24MISS-MS')
 END;

 FOR item IN
 WITH batch_items AS (
 SELECT bi.item_id, bi.report_id, bi.option_values, bi.report_alias,
 bi.enabled, bi.duplicated
 FROM public.batch_report_items bi
 WHERE bi.batch_id = $1
 ), backup_reports_missing AS (
 SELECT NULL::integer AS item_id, r.id AS report_id,
 NULL::jsonb AS option_values, NULL::text AS report_alias,
 true AS enabled, false AS duplicated
 FROM public.v_daily_reports r
 WHERE batch.batch_name = '@BACKUP'
 AND r.id NOT IN (SELECT ri.report_id FROM batch_items ri)
 ), report_items AS (
 SELECT * FROM batch_items
 UNION ALL
 SELECT * FROM backup_reports_missing
 )
 SELECT bi.report_id, bi.option_values, r.options AS report_options,
 batch.batch_type, batch.prop_id,
 COALESCE(bi.report_alias, r.report_name) AS report_name,
 CASE WHEN r.report_filename ILIKE '%.frx' THEN 'frx' ELSE 'jrxml' END AS template_type
 FROM report_items bi
 LEFT JOIN public.reports r ON r.id = bi.report_id
 WHERE batch.enabled = true
 AND bi.enabled = true
 ORDER BY r.report_name, bi.duplicated, bi.item_id NULLS LAST
 LOOP
 BEGIN
 report_params := public.fn_parse_param_values(
 item.report_options,
 item.option_values,
 sys_date
 );

 IF item.batch_type = 'closeshift' THEN
 workshift := public.fn_work_shift(item.prop_id, user_name);
 shift_num := CASE workshift
 WHEN 'A' THEN 1
 WHEN 'B' THEN 2
 WHEN 'C' THEN 3
 WHEN 'D' THEN 4
 ELSE 5
 END;
 IF item.report_id IN (29, 166) THEN
 report_params := report_params || jsonb_build_object('I_SHIFT', workshift);
 ELSE
 report_params := report_params || jsonb_build_object('I_SHIFT', shift_num);
 END IF;
 END IF;

 time_start := clock_timestamp();

 url := public.sp_gen_report_url(
 batch.prop_code,
 item.report_id,
 report_params::json,
 file_path,
 item.report_name || '.pdf',
 expire_secs => 7 * 24 * 60 * 60,
 template_type => item.template_type,
 access_token => access_token
 );

 IF is_dev THEN
 execute_time := format(
 '%s seconds',
 EXTRACT(EPOCH FROM (clock_timestamp() - time_start))::text
 );
 END IF;

 report_list := report_list || format(
 '<li><a href=%s>%s</a>&nbsp;%s</li>',
 COALESCE(url, ''),
 item.report_name,
 execute_time
 );
 report_count := report_count + 1;
 EXCEPTION WHEN OTHERS THEN
 GET STACKED DIAGNOSTICS
 err_sqlstate = RETURNED_SQLSTATE,
 err_message = MESSAGE_TEXT,
 err_detail = PG_EXCEPTION_DETAIL,
 err_hint = PG_EXCEPTION_HINT,
 err_context = PG_EXCEPTION_CONTEXT;
 execute_time := EXTRACT(EPOCH FROM (clock_timestamp() - time_start));
 report_list := report_list || format(
 '<li>%s&nbsp;%s seconds %s</li>',
 item.report_name,
 execute_time::text,
 err_message
 );
 message := message || 'report : ' || item.report_name || E'\n'
 || COALESCE('options : ' || item.option_values::text || E'\n', '')
 || COALESCE('message : ' || NULLIF(err_message, '') || E'\n\n', '');
 END;
 RAISE NOTICE '--------------------------------------';
 END LOOP;

 PERFORM t.closed
 FROM public.dblink(
 public.fn_connection(current_database(), authen_host),
 format('SELECT public.fn_close_temp_report_token(%s)', temp_log_id)
 ) AS t(closed boolean);
 temp_log_id := NULL;

 recipients := CASE batch.output_type
 WHEN 'file' THEN ARRAY['pisanu@newsoft.co.th', 'thosaporn@newsoft.co.th']
 WHEN 'email' THEN regexp_split_to_array(batch.output_target, '[,;]')
 END;

 msg_title := format('%s - %s', batch.batch_name, to_char(sys_date, 'FMDD Mon YYYY'));

 PERFORM msg.create_message(
 'batch-report',
 json_build_object(
 'TITLE', msg_title,
 'REPORT_LIST', report_list,
 'PREPARE_BY', COALESCE(user_name, 'Fromas')
 ),
 i_sender => batch.prop_name,
 i_recipients => recipients,
 i_subject => msg_title
 );

 IF message <> '' THEN
 PERFORM public.sp_system_notify(
 format(E'Batch Error - %s (%s)\n', batch.batch_name, batch.prop_code),
 message
 );
 END IF;

 RETURN report_count;
EXCEPTION WHEN OTHERS THEN
 IF temp_log_id IS NOT NULL THEN
 PERFORM t.closed
 FROM public.dblink(
 public.fn_connection(current_database(), authen_host),
 format('SELECT public.fn_close_temp_report_token(%s)', temp_log_id)
 ) AS t(closed boolean);
 END IF;
 RAISE;
END

sp_run_batch_report

Description

Parameters

Definition