aonestar.public

Parameters

Name	Type	Mode
user_name	text	IN
endday_data	jsonb	INOUT
test_mode	boolean	IN (DEFAULT false)

Definition

DECLARE 
    system_date date = fn_system_date();
    first_task text;
    idx int = 0;
    tasks jsonb;
    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;
    check_details jsonb;
    current_status char(1);
    execute_time numeric(8,3) = 0;
    old_online_shift t_shift;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    _prop_name TEXT;
BEGIN
    TEST_MODE := FALSE;

    _prop_name = (SELECT name FROM property_info LIMIT 1);
    
    PERFORM sp_system_notify(
       'End Day Start', --||_prop_name, 
        format(E'Prop : %s\nDate : %s\nUser : %s\nTime : %s', 
               _prop_name, 
               to_char(system_date,'DD-MM-YYYY'), 
               user_name, to_char(clock_timestamp(), 
               'DD-MM-YYYY HH24:MI:SS'))
    );

    user_name := COALESCE(user_name, fn_current_user());

    IF TEST_MODE THEN
        DELETE FROM endday WHERE endday_date = system_date;
    END IF;

    
    endday_data := sp_get_endday_process_list_js();

--    IF endday_data->>'status' in ('success','processing') THEN 
--        EXIT;
--    END IF;

    current_status := (SELECT status FROM endday WHERE endday_date = system_date);
    tasks := endday_data->'tasks'; 

    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);
        PERFORM sp_set_param('SYSTEM', 'ENDDAY_RUNNING', TRUE);
        PERFORM sp_set_session('ENDDAY_USER', user_name);
       
        PERFORM sp_authen_logout_all(user_name);
        PERFORM sp_enqueue_realtime('*', null, 'endday_start', json_build_object('user_name', user_name, 'start_time', clock_timestamp()));
    END IF;

    task_count = jsonb_array_length(tasks);
    old_online_shift = fn_sys_param('ONLINE_SHIFT', 'X');

    RAISE NOTICE 'endday: ** start ** (current status = %, count = %, test_mode = %)', COALESCE(current_status,'Init'), task_count, test_mode;
 
    PERFORM sp_change_online_shift('X');
    COMMIT;

    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_array_elements(tasks) AS t(task_object)
                LEFT JOIN endday_tasks et ON et.id = t->>'id'
            WHERE (t->>'status' = 'pending') OR (t->>'type' = 'precheck')
            ORDER BY 1
        )
        LOOP
            task_obj = task.task_object;
            task_start = clock_timestamp(); 
            RAISE NOTICE 'endday: execute [%] %', task.id, IIF(task.function_name IS NULL, '', '*')||task.name;
            PERFORM sp_update_endday_task(system_date, idx, task.task_object, 'processing');

            -- Inform users that end day starting 
            IF task.id = first_task THEN
                PERFORM sp_authen_logout_all(user_name);
                perform sp_enqueue_realtime('*', null, 'endday_start', json_build_object('user_name', user_name, 'start_time', task_start));
            END IF;
            
                
            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');
                --WHEN 'C02' THEN CALL sp_endday_check_non_extensions(task_status, task_msg, details); 
                    
                ELSE      
                    task_status = 'success';  
                    task_msg = iif(task."type" = 'precheck', 'Passed', COALESCE(task.function_name, sleep_ms::TEXT||' records generated.'));
                    IF COALESCE(task.function_name,'') <> '' THEN
                        EXECUTE format('CALL %I(%L, NULL, NULL)', task.function_name, user_name) INTO task_status, task_msg;  
                       --EXECUTE format('Select status, msg from %I()', task.function_name) INTO task_status, task_msg;  
                    ELSE
                       CALL sp_test_endday_task(task.task_object, task.id='P99', task_status, task_msg); 
                    END IF;
            END CASE;
            
            task_end = clock_timestamp();
            finished = (idx = task_count-1) OR (task_status = 'error');
            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;
       -- 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;    
            RAISE NOTICE 'endday: error - %', task_msg;
            ROLLBACK;
        
            task_status = 'error';
            check_details = (CASE WHEN _sqlstate = '90002' THEN _detail ELSE NULL END)::jsonb;   
            PERFORM sp_update_endday_task(system_date, idx, task_obj, task_status, task_msg, task_start, task_end, TRUE, check_details);
            PERFORM sp_log_error('sp_execute_endday', task_msg, _detail, _sqlstate, _hint, _context, force_write=>TRUE, notify_admin=>(_sqlstate <> '90002')); 
    END; 

    IF TEST_MODE THEN 
        ROLLBACK;
        RAISE NOTICE 'endday: rollback (test mode)';
    ELSE
        COMMIT;
    END IF;

    /* Finalize end day process */
    BEGIN
        IF finished and (task_status = 'success') then
           -- Force logout current user
            PERFORM sp_authen_logout_all(reset_shift := true); 
        END IF;
        PERFORM sp_change_online_shift(iif(task_status = 'error', old_online_shift, 'A'));
        PERFORM sp_set_param('SYSTEM', 'ENDDAY_RUNNING', FALSE);
        PERFORM sp_set_session('ENDDAY_USER', NULL::text);
    
        endday_data = sp_get_endday_process_list_js(system_date); 
        execute_time = EXTRACT(EPOCH FROM ((endday_data->>'finish_time')::timestamp - (endday_data->>'start_time')::timestamp));
        RAISE NOTICE 'endday: finish (%, % seconds)', task_status, execute_time; 
    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;    
            RAISE NOTICE 'endday: error - %', task_msg;
            ROLLBACK;
        
            task_status = 'warn';
            check_details = (CASE WHEN _sqlstate = '90002' THEN _detail ELSE NULL END)::jsonb;   
           -- PERFORM sp_update_endday_task(system_date, idx, task_obj, task_status, task_msg, task_start, task_end, TRUE, check_details);
            PERFORM sp_log_warning('sp_execute_endday', 'Finalize', _detail, _sqlstate, _hint, _context, force_write=>TRUE, notify_admin=>TRUE); 
    END; 

    -- Notify admin when endday finish
    PERFORM sp_system_notify(
       'End Day Finish ('||task_status||')',-- - '||_prop_name, 
        format(E'Prop : %s\nDate : %s\nUser : %s\nTime : %s (%s seconds)\n%s%s', 
              _prop_name,  
              to_char(system_date,'DD-MM-YYYY'), 
              user_name, 
              to_char(clock_timestamp(), 'DD-MM-YYYY HH24:MI:SS'), 
              execute_time,
              IIF(task_status = 'success', 'Success', 'Error'),
              IIF(task_status = 'success', '', ': '||task_msg)    
        ) 
    );   
END;