aonestar.public

Parameters

Name	Type	Mode
p_data	jsonb	IN

Definition

declare
        v_folio_id int4;
        v_folio_seq int4;        
        v_item_id int4;
        
        v_department record;
        v_folio_item record;
    
        v_result jsonb;
        
        system_date date = fn_system_date();
        
        close_result json;
        v_early_checkout bool;
        bk_folio_id int;
        bk_departure date;
        v_ooo_status char(3);
        v_sqlstate text;
        v_detail text;
        v_hint text;
        v_context text;
        v_msg_text text;
        reg record;
        log_title text;
        log_msg text;
        -- ใช้ตัวแปร messages ที่มีอยู่เดิม และเพิ่มเลข folio ที่ลบไม่ได้
        messages jsonb = $${
                        "30301": "Can not delete, there are active transaction in this folio"
                    }$$;
        dept_arr jsonb;
        
        v_folio_item_id int4;
        v_charge_to_folio_id int4;
        
        v_folio jsonb;
        v_sync_transactions bool;
        r record;
        v_transaction_by_item_id record;
       
        v_dept_id int4;
       
        rows_affected int;
        v_check_cant_delete int;
        -- เพิ่มตัวแปรสำหรับเก็บเลข folio ที่ลบไม่ได้
        v_undeletable_folios text[];
       
       
begin
    BEGIN
        v_folio_id := (p_data ->> 'folio_id')::int4;
        v_sync_transactions := coalesce((p_data ->>'sync_transactions')::bool, false);
       
        v_check_cant_delete := 0;
        -- Initialize array
        v_undeletable_folios := ARRAY[]::text[];
       
        -- Step 1. ลบ folio_details ทั้งหมดก่อน
        delete from folio_details 
        where folio_id = v_folio_id;
       
        -- Step 2. Process each folio for transfer case only (not delete)
        for i in 1..4 loop
            v_folio := p_data -> ('folio' || i::text);
            v_folio_seq := i;
           
           
            IF v_folio is not null and v_folio <> 'null'::jsonb THEN
                -- ถ้ามี folio ให้ update หรือ insert
                UPDATE folio_items 
                SET folio_name = coalesce(v_folio ->> 'folio_name', '')
                WHERE folio_id = v_folio_id
                AND folio_seq = v_folio_seq 
                AND closed = false;

GET DIAGNOSTICS rows_affected = ROW_COUNT;

IF rows_affected = 0 THEN
                    INSERT INTO folio_items (
                        folio_id,
                        folio_seq,
                        folio_name,
                        closed
                    ) VALUES (
                        v_folio_id,
                        v_folio_seq,
                        coalesce(v_folio ->> 'folio_name', ''),
                        false
                    );
                END IF;

--                RAISE NOTICE 'folio_name : % ', v_folio ->> 'folio_name';
               
                if (v_folio->'departments' is not null) then
                    dept_arr := v_folio -> 'departments';
                    
                    for v_dept_id in 
                        select (value->>'id')::int4 
                        from jsonb_array_elements(dept_arr)
                    loop
                        RAISE NOTICE 'folio_details dept id : % ', v_dept_id;

insert into folio_details(
                            folio_id,
                            folio_seq,
                            dept_id
                        ) values(
                            v_folio_id,
                            v_folio_seq,
                            v_dept_id
                        );
                       
                        if (v_sync_transactions = true) then
                            UPDATE transactions t
                            SET folio_item = (
                                SELECT fi.item_id 
                                FROM folio_items fi 
                                WHERE fi.folio_id = v_folio_id 
                                AND fi.folio_seq = v_folio_seq 
                                AND fi.closed = false
                                LIMIT 1
                            )
                            WHERE t.dept_id = v_dept_id 
                            AND t.folio_id = v_folio_id
                            AND EXISTS (
                                SELECT 1 
                                FROM folio_items fi 
                                WHERE t.folio_item = fi.item_id 
                                AND fi.closed = false
                            );
                        end if;
                    end loop;
--                else 
--                    RAISE NOTICE 'departments : [] ';
                end if;
--            else 
--                -- ถ้า folio เป็น null (จะลบ)
--                -- เช็คว่า folio_item นี้มี transaction หรือไม่
--                IF EXISTS (
--                    SELECT 1
--                    FROM folio_items fi 
--                    WHERE fi.folio_id = v_folio_id 
--                    AND fi.folio_seq = v_folio_seq
--                    AND fi.closed = false
--                    AND EXISTS (
--                        SELECT 1 
--                        FROM transactions t 
--                        WHERE t.folio_item = fi.item_id
--                    )
--                ) THEN
----                     ถ้ามี transaction ให้ข้ามไป (ไม่ลบ)
----                    v_check_cant_delete := 1;
----                     เก็บเลข folio ที่ลบไม่ได้
----                    v_undeletable_folios := array_append(v_undeletable_folios, i::text);
----                    CONTINUE;
--                
--                -- ถ้ามี transaction ให้ทำการ update ย้าย folio_item ไปใบใหม่ตามที่ UI กำหนด
--                	select fi.item_id into v_folio_item_id
--                	from folio_items fi
--                	where fi.folio_id = v_folio_id 
--                    AND fi.folio_seq = v_folio_seq
--                    AND fi.closed = false
--                    limit 1; 
--                
--            		update transactions t
--            		set t.folio_item = v_folio_item_id
--            		where t.folio_item
--                
----                ELSE
----                    -- ถ้าไม่มี transaction ให้ลบได้
----                    DELETE FROM folio_items fi 
----                    WHERE fi.folio_id = v_folio_id 
----                    AND fi.folio_seq = v_folio_seq
----                    AND fi.closed = false;
--                END IF;
            end if;
        end loop;
        
       -- Step 3. Process each folio for delete case only
        for i in 1..4 loop
             v_folio := p_data -> ('folio' || i::text);
             v_folio_seq := i;
           
           
--             IF v_folio is not null and v_folio <> 'null'::jsonb then
--             else 
--                -- ถ้า folio เป็น null (จะลบ)
--                -- เช็คว่า folio_item นี้มี transaction หรือไม่
--                IF EXISTS (
--                    SELECT 1
--                    FROM folio_items fi 
--                    WHERE fi.folio_id = v_folio_id 
--                    AND fi.folio_seq = v_folio_seq
--                    AND fi.closed = false
--                    AND EXISTS (
--                        SELECT 1 
--                        FROM transactions t 
--                        WHERE t.folio_item = fi.item_id
--                    )
--                ) THEN
--                ELSE
--                    -- ถ้าไม่มี transaction ให้ลบได้
--                    DELETE FROM folio_items fi 
--                    WHERE fi.folio_id = v_folio_id 
--                    AND fi.folio_seq = v_folio_seq
--                    AND fi.closed = false;
--                END IF;
--             end if;
            
            if v_folio is null or v_folio = 'null'::jsonb then
			    -- โค้ดที่ต้องการทำงานเมื่อเงื่อนไขเป็นจริง
--                if  exists (
--					select 1
--					from folio_items fi
--					where fi.folio_id = v_folio_id
--					and fi.folio_seq = v_folio_seq
--					and fi.closed = false
--					and exists (
--								select 1
--								from transactions t
--								where t.folio_item = fi.item_id
--								)
--					) then
--					else
--					-- ถ้าไม่มี transaction ให้ลบได้
--							delete from folio_items fi
--							where fi.folio_id = v_folio_id
--							and fi.folio_seq = v_folio_seq
--							and fi.closed = false;
--				end if;
            
           		 if not exists (
					select 1
						from folio_items fi
						where fi.folio_id = v_folio_id
						and fi.folio_seq = v_folio_seq
						and fi.closed = false
						and exists (
								select 1
								from transactions t
								where t.folio_item = fi.item_id
								)
				 ) then
						-- ถ้าไม่มี transaction ให้ลบได้
						delete from folio_items fi
						where fi.folio_id = v_folio_id
						and fi.folio_seq = v_folio_seq
						and fi.closed = false;

end if;
               
			end if;
           
           
        end loop;
       
       -- Step 4. Return result
       
--        for i in 1..4 loop
--            v_folio := p_data -> ('folio' || i::text);
--            v_folio_seq := i;
--           
--            RAISE NOTICE 'Processing folio: %', i;
--            
--            IF v_folio is not null and v_folio <> 'null'::jsonb THEN
--                -- ถ้ามี folio ให้ update หรือ insert
--                UPDATE folio_items 
--                SET folio_name = coalesce(v_folio ->> 'folio_name', '')
--                WHERE folio_id = v_folio_id
--                AND folio_seq = v_folio_seq 
--                AND closed = false;
--
--                GET DIAGNOSTICS rows_affected = ROW_COUNT;
--
--                IF rows_affected = 0 THEN
--                    INSERT INTO folio_items (
--                        folio_id,
--                        folio_seq,
--                        folio_name,
--                        closed
--                    ) VALUES (
--                        v_folio_id,
--                        v_folio_seq,
--                        coalesce(v_folio ->> 'folio_name', ''),
--                        false
--                    );
--                END IF;
--
--                RAISE NOTICE 'folio_name : % ', v_folio ->> 'folio_name';
--               
--                if (v_folio->'departments' is not null) then
--                    dept_arr := v_folio -> 'departments';
--                    
--                    for v_dept_id in 
--                        select (value->>'id')::int4 
--                        from jsonb_array_elements(dept_arr)
--                    loop
--                        RAISE NOTICE 'folio_details dept id : % ', v_dept_id;
--
--                        insert into folio_details(
--                            folio_id,
--                            folio_seq,
--                            dept_id
--                        ) values(
--                            v_folio_id,
--                            v_folio_seq,
--                            v_dept_id
--                        );
--                       
--                        if (v_sync_transactions = true) then
--                            UPDATE transactions t
--                            SET folio_item = (
--                                SELECT fi.item_id 
--                                FROM folio_items fi 
--                                WHERE fi.folio_id = v_folio_id 
--                                AND fi.folio_seq = v_folio_seq 
--                                AND fi.closed = false
--                                LIMIT 1
--                            )
--                            WHERE t.dept_id = v_dept_id 
--                            AND t.folio_id = v_folio_id
--                            AND EXISTS (
--                                SELECT 1 
--                                FROM folio_items fi 
--                                WHERE t.folio_item = fi.item_id 
--                                AND fi.closed = false
--                            );
--                        end if;
--                    end loop;
--                else 
--                    RAISE NOTICE 'departments : [] ';
--                end if;
--            else 
--                -- ถ้า folio เป็น null (จะลบ)
--                -- เช็คว่า folio_item นี้มี transaction หรือไม่
--                IF EXISTS (
--                    SELECT 1
--                    FROM folio_items fi 
--                    WHERE fi.folio_id = v_folio_id 
--                    AND fi.folio_seq = v_folio_seq
--                    AND fi.closed = false
--                    AND EXISTS (
--                        SELECT 1 
--                        FROM transactions t 
--                        WHERE t.folio_item = fi.item_id
--                    )
--                ) THEN
----                     ถ้ามี transaction ให้ข้ามไป (ไม่ลบ)
----                    v_check_cant_delete := 1;
----                     เก็บเลข folio ที่ลบไม่ได้
----                    v_undeletable_folios := array_append(v_undeletable_folios, i::text);
----                    CONTINUE;
--                
--                -- ถ้ามี transaction ให้ทำการ update ย้าย folio_item ไปใบใหม่ตามที่ UI กำหนด
--                	select fi.item_id into v_folio_item_id
--                	from folio_items fi
--                	where fi.folio_id = v_folio_id 
--                    AND fi.folio_seq = v_folio_seq
--                    AND fi.closed = false
--                    limit 1; 
--                
--            		update transactions t
--            		set t.folio_item = v_folio_item_id
--            		where t.folio_item
--                
--                ELSE
--                    -- ถ้าไม่มี transaction ให้ลบได้
--                    DELETE FROM folio_items fi 
--                    WHERE fi.folio_id = v_folio_id 
--                    AND fi.folio_seq = v_folio_seq
--                    AND fi.closed = false;
--                END IF;
--            end if;
--        end loop;
--
--        -- ถ้ามี folio ที่ลบไม่ได้ ให้แก้ไขข้อความใน messages
--        IF array_length(v_undeletable_folios, 1) > 0 THEN
--            messages := jsonb_set(messages, 
--                '{30301}', 
--                to_jsonb('Can not delete folio No. ' || array_to_string(v_undeletable_folios, ', ') || 
--                        ', there are active transaction')
--            );
--        END IF;

select * into v_result 
        from sp_get_guest_folio_pattern_json(v_folio_id);
       
        if v_check_cant_delete = 1 then
            -- ใช้ข้อความจาก messages
            RAISE EXCEPTION '%', messages->>'30301';
        end if;
        
        return fn_result_success(v_result);

EXCEPTION
        when others then
            get STACKED diagnostics
                v_msg_text = MESSAGE_TEXT,
                v_sqlstate = RETURNED_SQLSTATE,
                v_detail = PG_EXCEPTION_DETAIL,
                v_hint = PG_EXCEPTION_HINT,
                v_context = PG_EXCEPTION_CONTEXT;
        
            return fn_handle_error(
                v_sqlstate,
                v_msg_text,
                v_detail,
                v_hint,
                v_context,
                'sp_save_guest_folio_pattern', 
                p_data
            );
    END;   
end;