aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN
new_pattern_id	integer	IN
user_name	text	IN (DEFAULT NULL)

Definition

DECLARE 
    item record;
    old_pattern_id int;
    old_pattern TEXT;
    new_pattern TEXT;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
BEGIN 
    CALL sp_check_endday_process();
    CALL sp_check_folio(folio_id);

    -- Mark current active folio items as temp   
    UPDATE folio_items fi
    SET folio_seq = -fi.folio_seq
    WHERE fi.folio_id = $1 AND NOT fi.closed;
    
    -- Copy items from folio pattern
    INSERT INTO folio_items(folio_id, folio_seq, folio_name)
    SELECT $1, f.folio_seq, f."name" 
    FROM folio_pattern_seq f
    WHERE f.pattern_id = $2;

    -- Replace item's details from folio pattern
    DELETE FROM folio_details d WHERE d.folio_id = $1; 
    INSERT INTO folio_details(folio_id, folio_seq, dept_id)
    SELECT $1, d.folio_seq, d.dept_id
    FROM folio_pattern_details d
    WHERE d.pattern_id = $2;

    -- Generate default folio items (Master and Extra) if no item exists in folio pattern 
    IF NOT EXISTS(SELECT * FROM folio_items fi WHERE fi.folio_id = $1 AND fi.folio_seq < 0) THEN 
        INSERT INTO folio_items(folio_id, folio_seq, folio_name) VALUES ($1, 1, 'Master'); 
        INSERT INTO folio_items(folio_id, folio_seq, folio_name) VALUES ($1, 2, 'Extra'); 
    END IF; 

    -- Add all departments to folio sequence 1, if no department assigned to folio pattern  
    IF NOT EXISTS(SELECT * FROM folio_details d WHERE d.folio_id = $1) THEN 
        INSERT INTO folio_details(folio_id, folio_seq, dept_id)
        SELECT NEW.id, 1, d.id
        FROM department d WHERE post_type = 'P';
    END IF;     

    -- Move existing post transactions
    WITH trn AS (
        SELECT tr.id, new_item.item_id AS new_folio_item  
        FROM transactions tr 
            LEFT JOIN folio_items fi ON fi.item_id = tr.folio_item
            LEFT JOIN folio_pattern_details fd ON fd.pattern_id = $2 AND fd.dept_id = tr.dept_id
            LEFT JOIN folio_items new_item ON new_item.folio_id = tr.folio_id AND new_item.folio_seq = fd.folio_seq AND NOT new_item.closed
        WHERE tr.folio_id = $1 AND tr.post_type = 'P' AND NOT fi.closed
    )
    UPDATE transactions t 
    SET folio_item = COALESCE(trn.new_folio_item, t.folio_item) 
    FROM trn
    WHERE t.id = trn.id;

    -- Move payment and remaining post transactions
    WITH trn AS (
        SELECT tr.id, new_item.item_id AS new_folio_item
        FROM transactions tr 
            LEFT JOIN folio_items fi ON fi.item_id = tr.folio_item
            LEFT JOIN folio_items new_item ON new_item.folio_id = tr.folio_id AND new_item.folio_seq = abs(fi.folio_seq) AND NOT new_item.closed
        WHERE tr.folio_id = $1 AND NOT fi.closed AND fi.folio_seq < 0
    )     
    UPDATE transactions t 
    SET folio_item = COALESCE(trn.new_folio_item, t.folio_item) 
    FROM trn
    WHERE t.id = trn.id;

    -- Remove/reset temp folio items 
    FOR item IN 
        SELECT fi.item_id, fi.folio_seq
        FROM folio_items fi 
        WHERE fi.folio_id = $1 AND NOT fi.closed --AND fi.folio_seq < 0   
    LOOP
        IF item.folio_seq < 0 THEN 
            IF EXISTS(SELECT * FROM transactions t WHERE t.folio_item = item.item_id) THEN 
                UPDATE folio_items fi SET folio_seq = abs(fi.folio_seq) WHERE fi.item_id = item.item_id;
            ELSE 
                DELETE FROM folio_items WHERE item_id = item.item_id;
            END IF;
        END IF;
    
        PERFORM sp_recalc_folio_balance(item.item_id);
    END LOOP;
    
    SELECT f.folio_pattern_id, fp."name" 
    FROM folio f LEFT JOIN folio_pattern fp ON fp.id = f.folio_pattern_id 
    WHERE f.id = $1 
    INTO old_pattern_id, old_pattern;

    new_pattern := (SELECT name FROM folio_pattern fp WHERE fp.id = new_pattern_id);
    IF (old_pattern_id IS DISTINCT FROM new_pattern_id) THEN 
        UPDATE folio f SET folio_pattern_id = new_pattern_id WHERE f.id = $1;
    END IF;
    

    PERFORM sp_log_cashier($1, null, 'Change folio pattern', old_pattern||' -> '||new_pattern, user_name);
    
    RETURN fn_result_success();
EXCEPTION  
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _sqlstate = RETURNED_SQLSTATE,
        _msg_text = MESSAGE_TEXT,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT;    
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_change_folio_pattern', 
        jsonb_build_object('folio_id', $1, 'new_pattern_id', $2)
    );
END