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 int = 0;
 msg_title TEXT;
 report_list TEXT = '';
 recipients TEXT[] = null;
 url TEXT;
 sys_date date;
 prop_name TEXT;
 report_params jsonb;
 err_sqlstate TEXT;
 err_detail TEXT;
 err_hint TEXT;
 err_context TEXT;
 err_message TEXT;
 message TEXT = '';
 time_start timestamp;
 time_finish timestamp;
 execute_time text = '';
 access_token text;
 is_dev bool = is_dev_server();
 workshift varchar;
 shift_num int;
BEGIN
 SELECT br.*, p."name"::text AS prop_name, p.code::text AS prop_code, p.id as prop_id, p.prop_group_id
 FROM batch_report br LEFT JOIN property p ON p.id = br.prop_id 
 WHERE br.id = $1 
 INTO batch;

 -- FAIL access_token := fn_gen_access_token(user_name, lower(batch.prop_code)); 
 access_token := coalesce(fn_get_login_token(user_name, batch.prop_id), fn_get_config(batch.prop_id, 'REPORT_GEN_TOKEN'));

 raise notice 'Token: %', sys.jwt_verify(access_token);

 sys_date := fn_system_date(batch.prop_id);

 file_path := CASE batch.output_type
 WHEN 'file' THEN 'report/file/'--||lower(batch.batch_name)||'/'
 ||trim('/' from regexp_replace(batch.output_target, E'(\\{date\\}|<date>)', to_char(sys_date,'YYYYMMDD'), 'ig'))
 WHEN 'email' THEN 'report/temp/'||gen_random_uuid()||'/'||to_char(current_timestamp, 'YYYYMMDD-HH24MISS-MS')
 END;

 FOR item IN 
 SELECT bi.report_id, bi.option_values, r."options" as report_options, br.batch_type, br.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 batch_report br 
 left join batch_report_items bi on bi.batch_id = br.id 
 LEFT JOIN reports r ON r.id = bi.report_id 
 WHERE br.id = $1 and br.enabled = true and bi.enabled = true
 ORDER BY r.report_name, bi.duplicated, bi.item_id 
 LOOP 
 BEGIN 
 report_params := fn_parse_param_values(item.report_options, item.option_values, sys_date);
 
 -- Add current shift to params
			if item.batch_type = 'closeshift' then
 	workshift := 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;
 
 raise notice '%: %', item.report_id, item.report_name;
 raise notice 'Params: %', report_params::text;
 time_start := clock_timestamp();

 url := 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 => null 
 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;

 IF batch.output_type = 'email' THEN 
 recipients := string_to_array(batch.output_target, ',');
 END IF;

 msg_title := 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 sp_system_notify(format(E'Batch Error - %s (%s)\n', batch.batch_name, batch.prop_code), message);
 END IF;

 RETURN report_count;
END

sp_run_batch_report

Parameters

Definition