aonestar.public

Parameters

Name	Type	Mode
p_folio_id	integer	IN
p_data	jsonb	IN

Definition

declare
    		v_folio_id int4;

v_folio_seq int4;
			
			v_item_id int4;
			
			v_folio record;
			
			v_department record;
			
			v_folio_item record;
			
			v_result jsonb;
			
			system_date date = fn_system_date();
			
			close_result json;
			
			_early_checkout bool;
			
			bk_folio_id int;
			
			bk_departure date;
			
			_ooo_status char(3);
			
			_sqlstate text;
			
			_detail text;
			
			_hint text;
			
			_context text;
			
			_msg_text text;
			
			reg record;
			
			log_title text;
			
			log_msg text;
--    messages    jsonb = $${
--        "30301": "Early checkout?",
--        "30302": "Charge schedule not posted.",
--        "30303": "Folio out of balance."
--    }$$;
			dept_arr  jsonb;
		
		
begin
	
	  -- Create by T.Supol 7-Nov-2024
	
	
	
	  -- get folio_id from input data
	  v_folio_id := (p_data->>'folio_id')::int4;
	  
	  -- 1. update folio
      update folio
      set split_room_charge = (p_data->>'split_room_charge')::boolean
      where id = v_folio_id;
     
      -- 2. delete some old folio item. if not send it in parameter
      	-- RAISE NOTICE 'JSON data folio1: %', p_data ->> 'folio1';
      	--RAISE NOTICE 'JSON data: ';
       
      if (p_data-> 'folio1') is not null then
         
         -- have data let run update statement

update folio_items
          set note = p_data->'folio1'->>'note',
               folio_name = p_data->'folio1'->>'folio_name',
               charge_to_folio = (p_data->'folio1'->>'charge_to_folio_id')::int4
          where folio_id = (p_data->>'folio_id') :: int4
          and folio_seq = (p_data->>'folio_seq') :: int4;
         
          raise notice 'note data: %', 'complete case have folio1 data';
        
      else 
       -- not have data in paremeter item level such as folio2 not send data (folio2=null)
       -- let delete old folio itme data
      	 
      	  delete from folio_items
      	  where folio_id = (p_data->>'folio_id') :: int4
          and folio_seq = 1;
          
          raise notice 'note data: %', 'complete case have folio1 data';
      
      end if;
     
     
      if (p_data-> 'folio2') is not null then
         
         -- have data let run update statement

update folio_items
          set note = p_data->'folio2'->>'note',
               folio_name = p_data->'folio2'->>'folio_name',
               charge_to_folio = (p_data->'folio2'->>'charge_to_folio_id')::int4
          where folio_id = (p_data->>'folio_id') :: int4
          and folio_seq = (p_data->>'folio_seq') :: int4;
         
          raise notice 'note data: %', 'complete case have folio1 data';
        
      else 
       -- not have data in paremeter item level such as folio2 not send data (folio2=null)
       -- let delete old folio itme data
      	 
      	  delete from folio_items
      	  where folio_id = (p_data->>'folio_id') :: int4
          and folio_seq = 2;
          
          raise notice 'note data: %', 'complete case have folio1 data';
      
      end if;
     
     
      if (p_data-> 'folio3') is not null then
         
         -- have data let run update statement

update folio_items
          set  note = p_data->'folio3'->>'note',
               folio_name = p_data->'folio3'->>'folio_name',
               charge_to_folio = (p_data->'folio3'->>'charge_to_folio_id')::int4
          where folio_id = (p_data->>'folio_id') :: int4
          and folio_seq = (p_data->>'folio_seq') :: int4;
         
          raise notice 'note data: %', 'complete case have folio1 data';
        
      else 
       -- not have data in paremeter item level such as folio2 not send data (folio2=null)
       -- let delete old folio itme data
      	 
      	  delete from folio_items
      	  where folio_id = (p_data->>'folio_id') :: int4
          and folio_seq = 1;
          
          raise notice 'note data: %', 'complete case have folio1 data';
      
      end if;
     
     
      if (p_data-> 'folio1') is not null then
         
         -- have data let run update statement

update folio_items
          set note = p_data->'folio1'->>'note',
               folio_name = p_data->'folio1'->>'folio_name',
               charge_to_folio = (p_data->'folio1'->>'charge_to_folio_id')::int4
          where folio_id = (p_data->>'folio_id') :: int4
          and folio_seq = (p_data->>'folio_seq') :: int4;
         
          raise notice 'note data: %', 'complete case have folio1 data';
        
      else 
       -- not have data in paremeter item level such as folio2 not send data (folio2=null)
       -- let delete old folio itme data
      	 
      	  delete from folio_items
      	  where folio_id = (p_data->>'folio_id') :: int4
          and folio_seq = 1;
          
          raise notice 'note data: %', 'complete case have folio1 data';
      
      end if;
      
     
    --  for i in 1..4 loop
    -- ดึงข้อมูล folio
	      
	    --   v_folio := p_data-> 'folio' || i::text;

-- ถ้ามีข้อมูล
--	    if v_folio is not null then
--	        -- ลบข้อมูลเก่า
--	        delete from folio_items
--	        where folio_id = v_folio_id 
--	        and folio_seq = (v_folio->>'folio_seq')::int;
--	    end if;
   -- end loop;
     
--      3. insert new folio item  1-4 item
--      for i in 1..4 loop
--	       v_folio := p_data->('folio' || i);
--	      
--	      if v_folio is not null and v_folio != 'null' then
--			     insert into folio_items(
--			     	note,
--			     	item_id,
--			     	folio_seq,
--			     	folio_name,
--			     	charge_to_folio_id,
--			     	charge_to_guest_name,
--			     	charge_to_room_number
--			     )
--			     values(
--			     	v_folio->>'note',
--			     	v_folio->>'item_id',
--			     	v_folio->>'folio_seq',
--			     	v_folio->>'folio_name',
--			     	v_folio->>'charge_to_folio_id',
--			     	v_folio->>'charge_to_guest_name',
--			     	v_folio->>'charge_to_room_number'
--	              );
--	              
--	    
--	  4. delete all old folio detail
--	             if v_folio->'departments' is not null then
--		             delete from folio_details 
--		                    where folio_id = v_folio_id 
--		                    and folio_seq = (v_folio->>'folio_seq');
--		             
--		             dept_arr := (json_extract_path(folio_rec.folio1, 'departments'));
--		            
--		             for v_department in 
--		                select * from json_array_elements(dept_arr)
--			            loop
--			               
--			                raise notice 'Folio1 - Department ID: %, Code: %, Name: %',
--			                    v_department.value->>'id',
--			                    v_department.value->>'code',
--			                    v_department.value->>'name';
--			                   
--			            insert into folio_details (
--	                        folio_id,
--	                        folio_seq,
--	                        dept_id
--	                    ) values (
--	                         v_folio_id,
--	                        (v_folio->>'folio_seq'),
--	                        (v_department->>'id')
--	                    );
--			                
--			               
--			          end loop;
--	             end if;
--	     end if;
--      	
--      end loop;

      return fn_result_success();

exception
when others then


    get STACKED diagnostics
        _msg_text = MESSAGE_TEXT,
        _sqlstate = RETURNED_SQLSTATE,
        _detail = PG_EXCEPTION_DETAIL,
        _hint = PG_EXCEPTION_HINT,
        _context = PG_EXCEPTION_CONTEXT;

return fn_handle_error(_sqlstate,
_msg_text,
_detail,
_hint,
_context,
'fn_save_folio_data', 
        p_data
    );
end;

sp_save_folio_data

Parameters

Definition