aonestar.public

Parameters

Name	Type	Mode
rooms	int4[]	IN
new_status	character	IN (DEFAULT NULL)
new_hkstatus	character	IN (DEFAULT NULL)
reason_id	integer	IN (DEFAULT NULL)
start_date	date	IN (DEFAULT NULL)
end_date	date	IN (DEFAULT NULL)
remark	text	IN (DEFAULT NULL)
return_status	character	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)

Definition

declare 
	cur_status char(3);
	cur_hkstatus char(2);
	success_count int = 0;
	success bool;
	_room_id int;
	system_date date;
	cur_startdate date;
	cur_enddate date;
	room_number text;
	ooo_id int;
	log_text text;
	--log_new text;
	input_count int;
	current_to_ooo boolean = false;
	js_result json[] = '{}';
	changed_rooms int[] = '{}';
	event_data json;
	result_code TEXT;
	result_msg TEXT;
	result_data json;
	msg_code TEXT;
	_msg_text TEXT;
    _sqlstate   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT;	
    messages jsonb = $${
        "10600" : "Room %s has been changed.", 
        "10601" : "%s rooms have been changed.", 
        "10602" : "%s of %s rooms have been changed.",
        "10603" : "No room has been changed.",       
        "10604" : "Room %s is occupied",
        "10605" : "Please specify room status or maid status.",
        "10606" : "Cannot change status to occupied.", 
        "10607" : "Room %s, status is already the same.",
        "10608" : "Please specift reason",
        "10609" : "Please specift start date and end date",
        "10610" : "Please specify room number."
    }$$;
BEGIN
    CALL sp_check_endday_process(); 
	input_count = fn_length(rooms);
	
	CASE 
        WHEN input_count = 0 THEN
            RETURN fn_result_error('10610', messages->>'10610'); 
        WHEN new_status IS NULL AND new_hkstatus IS NULL THEN 
            RETURN fn_result_error('10605', messages->>'10605');    
        WHEN (new_status IS NOT NULL) AND (new_status = 'OCC') then  
            RETURN fn_result_error('10606', messages->>'10606'); 
        WHEN (new_status IS NOT NULL) AND (new_status in ('OOO','OOS','OOI')) AND (reason_id IS NULL) then  
            RETURN fn_result_error('10608', messages->>'10608'); 
        WHEN (new_status IS NOT NULL) AND (new_status in ('OOO','OOS','OOI')) AND (start_date IS NULL OR end_date IS NULL) then  
            RETURN fn_result_error('10609', messages->>'10609'); 
        ELSE 
	END CASE ;
	
	system_date = fn_system_date();
	
	FOR _room_id, room_number, cur_status, cur_hkstatus in 
		SELECT rm.id, rm.room_number, s.status, s.hk_status 
		FROM unnest(rooms) rm_id 
			INNER JOIN room rm ON rm.id = rm_id 
			INNER JOIN room_status s on s.room_id = rm_id
	LOOP 
	    result_code := '0'; result_msg := 'Success'; msg_code := '00';
		ooo_id := null; success := FALSE;
		
--		RAISE NOTICE '%: % -> %', room_number, COALESCE(cur_status,'')||COALESCE(cur_hkstatus,'') , COALESCE(new_status,cur_status)||COALESCE(new_hkstatus,cur_hkstatus);
		
		-- ROOM STATUS --
	    IF (cur_status = 'OCC')	and (new_status = 'VAC')  then
			msg_code   := '10604'; 
            result_msg := messages->>'10604';		
		ELSIF new_status = 'VAC' AND cur_status <> 'VAC' then 
		    -- Change to VAC
			log_text := cur_status;				
			if cur_status in ('OOO','OOS','OOI') then
				delete from out_of_order o 
				where (o.room_id = _room_id) and (system_date between o.start_date and o.end_date)
				returning fn_get_ooo_desc(id,1) into log_text;
			end if;		
            RAISE NOTICE '%: % -> %',  _room_id,  cur_status, new_status;
			update room_status s set status = 'VAC' where s.room_id = _room_id;
			success := TRUE;			
		
		ELSEIF new_status IN ('OOO','OOS','OOI') then 
		    -- Change to OOO, OOS, OOI
			select o.result_code, o.result_msg, o.msg_code, o.ooo_id--, fn_get_ooo_desc(o.ooo_id,1) 
			from sp_add_out_of_order(_room_id, new_status, reason_id, start_date, end_date, remark, return_status, user_name, 
			    replace_current => TRUE, in_loop => TRUE) as o
			into result_code, result_msg, msg_code, ooo_id; --, log_new; 

			success := success OR (ooo_id IS NOT NULL);	  
        ELSIF COALESCE(cur_status,'')||COALESCE(cur_hkstatus,'') = COALESCE(new_status,cur_status)||COALESCE(new_hkstatus,cur_hkstatus) then  
            msg_code   := '10607'; 
            result_msg := format(messages->>'10607', room_number);               
		END IF;
	
		-- MAID STATUS --
		IF (msg_code = '00') AND (new_hkstatus IS NOT NULL) AND (new_hkstatus IS DISTINCT FROM cur_hkstatus) THEN 
--			RAISE NOTICE '%: % -> %', _room_id, cur_hkstatus, new_hkstatus;
			update room_status rs 
			set hk_status = new_hkstatus
			where rs.room_id = _room_id;
			success := TRUE;
		END IF;
	
	    IF success THEN 
	    	changed_rooms := changed_rooms || _room_id;	
	        success_count := success_count + 1;
	    ELSE 
	       js_result := js_result || json_build_object(
		                                  'room_id', _room_id, 
		                                  'result_code', '4', 
		                                  'result_msg', result_msg, 
		                                  'msg_code', msg_code, 
		                                  'ooo_id', ooo_id);
		END IF;
	
		-- LOG --
		IF success AND new_status NOT IN ('OOO','OOS','OOI') then 
			perform sp_log_roomstatus(_room_id, user_name, cur_status, new_status, cur_hkstatus, new_hkstatus);
		END IF;
	END LOOP;
	
	if success_count > 0 then
		event_data = sp_get_room_details_js(changed_rooms, true);
		perform sp_enqueue_realtime('*', fn_current_propcode(), 'room_status', event_data);	
		--perform sp_sys_log('room_status event', changed_rooms::text);		
	end if;

	
	result_data = array_to_json(js_result);

	CASE 
		when (success_count = 0) then 
		  RETURN fn_result_warning(result_data, '10603', messages->>'10603');            --> ALL room failed  
		when (success_count = input_count) and (success_count = 1) then 
		  RETURN fn_result_success('10600', messages->>'10600', room_number);             --> One room success
		when (success_count = input_count) and (success_count > 1) then
          RETURN fn_result_success('10601', messages->>'10601', success_count::text) ;    --> All rooms success  
		when (success_count < input_count) then 
          RETURN fn_result_warning(result_data, '10602', messages->>'10602', success_count::text, input_count::text);  --> Some rooms failed
          
	END CASE;
	
	--IF (fn_length(rooms) = 1) AND (js_result)
	
--	if char_length(msg_code) = 2 then
--		msg_code = '106'||msg_code;
--		result_msg = CASE msg_code 
--			WHEN '10600' THEN sys_msg_format(msg_code, 'Room %s has been changed.', room_number)
--			WHEN '10601' THEN sys_msg_format(msg_code, '%s rooms have been changed.', success_count::text)
--			WHEN '10602' THEN sys_msg_format(msg_code, '%s of %s rooms have been changed.', success_count::text, input_count::text)
--			WHEN '10603' THEN sys_msg(msg_code, 'No room has been changed.')
---- 			WHEN '10604' THEN sys_msg_format(msg_code, 'Room %s is occupied on this period.', room_number)
--			WHEN '10605' THEN sys_msg(msg_code, 'Please specify room status or maid status.')
--			when '10609' then sys_msg_format(msg_code, 'Invalid status (%s -> %s).', cur_status, new_status)
-- 			WHEN '10610' THEN sys_msg(msg_code, 'No room to be changed.')
--		end;
--	end if;
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_room_status', 
        jsonb_build_object(
            'rooms',rooms, 
            'new_status', new_status,
            'new_hkstatus', new_hkstatus,
            'reason_id', reason_id,
            'start_date', start_date,
            'end_date', end_date,
            'remark', remark,
            'return_status', return_status,
            'user_name', user_name)
        ); 
END

sp_change_room_status2

Parameters

Definition