aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_run_endday_tasks_xxx
Parameters
Name
Type
Mode
tasks
jsonb
IN
user_name
text
IN (DEFAULT NULL)
Definition
DECLARE system_date date = fn_system_date(); idx int = 0; task record; task_obj jsonb; task_count int; task_start timestamp; task_end timestamp; task_status TEXT; task_msg TEXT; sleep_ms int; finished bool; current_status char(1); first_task int; -- rec record; _sqlstate TEXT; _detail TEXT; _hint TEXT; _context TEXT; _msg_text TEXT; BEGIN user_name = COALESCE(user_name, fn_current_user()); RAISE NOTICE 'endday: Start endday tasks (current status = %)', current_status; IF tasks IS NULL THEN DELETE FROM endday WHERE endday_date = system_date; current_status = (SELECT status FROM endday WHERE endday_date = system_date); tasks = sp_get_endday_process_list_js()->'tasks'; --IF endday_data->>'status' in ('pending','processing','error') THEN -- --IF current_status IS NULL OR current_status = 'E' THEN DELETE FROM endday WHERE endday_date = system_date; INSERT INTO endday(endday_user, start_time, tasks) VALUES (user_name, clock_timestamp() , tasks); -- --END IF; PERFORM sp_authen_logout_all(skip_current_user := true); PERFORM sp_system_notify('End Day Start', '123456'); COMMIT; ELSE current_status = (SELECT status FROM endday WHERE endday_date = system_date); END IF; task_count = jsonb_array_length(tasks); --IF current_status IS NULL OR current_status = 'E' THEN RAISE NOTICE 'endday: task_count = %', task_count; first_task := (SELECT t.id FROM endday_tasks t WHERE t.enabled and t.task_type = 'task' ORDER BY id LIMIT 1); BEGIN -- begin transaction FOR task IN ( SELECT task_object->>'id' AS id, task_object->>'type' AS "type", task_object->>'name' AS name, task_object->>'status' AS status, task_object->>'message' AS message, et.function_name, task_object --FROM jsonb_to_recordset(tasks) AS t(id TEXT, TYPE TEXT, name TEXT, status TEXT, message TEXT) FROM jsonb_array_elements(tasks) t(task_object) LEFT JOIN endday_tasks et ON et.id = t->>id ) LOOP task_obj = task.task_object; RAISE NOTICE 'endday task: %-%', task.id, task.name; --PERFORM sp_prop_exec(NULL::text, format('CALL sp_endday_update_task(%L,%L,%L)', system_date, idx, 'processing')); PERFORM sp_update_endday_task(system_date, idx, task.task_object, 'processing'); -- BEGIN PERFORM sp_log_info('endday', task.id||': '||task.name); task_start = clock_timestamp(); CASE task.id WHEN 'C01' THEN task_msg = CASE current_status WHEN 'P' THEN 'End day process is already running.' WHEN 'S' THEN 'End day process is already done.' ELSE iif(current_date <= system_date, 'Duplicate end day.', 'Passed') END; task_status = iif(task_msg = 'Passed', 'success', 'error'); --PERFORM pg_sleep(1); --WHEN 'P13' THEN -- RAISE EXCEPTION SQLSTATE '90002' USING MESSAGE = 'Test error P13'; ELSE PERFORM pg_sleep(0.2); task_status = 'success'; task_msg = iif(task."type" = 'precheck', 'Passed', COALESCE(task.function_name, sleep_ms::TEXT||' records generated.')); -- Inform users that end day starting IF task.id = first_task THEN perform sp_enqueue_realtime('*', null, 'endday_start', json_build_object('user_name', user_name, 'start_time', task_start)); END IF; IF COALESCE(task.function_name,'') <> '' THEN EXECUTE format('Select status, msg from %I()', task.function_name) INTO task_status, task_msg; END IF; END CASE; -- EXCEPTION -- WHEN OTHERS THEN -- GET STACKED DIAGNOSTICS -- task_msg = MESSAGE_TEXT, -- _sqlstate = RETURNED_SQLSTATE, -- _detail = PG_EXCEPTION_DETAIL, -- _hint = PG_EXCEPTION_HINT, -- _context = PG_EXCEPTION_CONTEXT; -- task_status = 'error'; -- ROLLBACK; -- PERFORM sp_log_error('endday - '||task.name, task_msg, _detail, _sqlstate, _hint, _context); -- END; -- task_end = clock_timestamp(); finished = (idx = (task_count-1)) OR (task_status = 'error'); --PERFORM sp_prop_exec(NULL::text, format('CALL sp_endday_update_task(%L,%L,%L,%L,%L,%L,%L)', system_date, idx, task_status, task_msg, task_start, task_end, finished)); PERFORM sp_update_endday_task(system_date, idx, task_obj, task_status, task_msg, task_start, task_end, finished); idx = idx + 1; EXIT WHEN (task_status = 'error'); END LOOP; if finished and (task_status = 'success') then -- Force logout current user PERFORM sp_authen_logout_all(skip_current_user := false); end if; -- COMMIT; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS _sqlstate = RETURNED_SQLSTATE, task_msg = MESSAGE_TEXT, _detail = PG_EXCEPTION_DETAIL, _hint = PG_EXCEPTION_HINT, _context = PG_EXCEPTION_CONTEXT; ROLLBACK; task_status = 'error'; PERFORM sp_log_error('sp_run_endday_tasks', task_msg, _detail, _sqlstate, _hint, _context); --PERFORM sp_prop_exec(NULL::text, format('CALL sp_endday_update_task(%L,%L,%L,%L,%L,%L,%L)', system_date, idx, task_status, task_msg, task_start, task_end, finished)); PERFORM sp_update_endday_task(system_date, idx, task_obj, task_status, task_msg, task_start, task_end, true); --RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_run_endday_tasks'); END; END;