aonestar.public

Parameters

Name	Type	Mode
i_followup_id	integer	IN (DEFAULT NULL)
i_booking_id	integer	IN (DEFAULT NULL)
i_register_id	integer	IN (DEFAULT NULL)
i_assign_user	text	IN (DEFAULT NULL)

Definition

declare json_result json;
	declare current_username text;
	BEGIN
		/*
		 * Create by KenG (2020/03/12)
		 * # Return json
		 * # Condition return
		 * - In case 1: all value null will return all data followup table
		 * - In case 2: if have followup_id only -> will return followup and comments where id of followup table
		 * - In case 3: if have booking_id only -> will return followup and comments where ref_type = booking and ref_id = booking_id
		 * - In case 4: if have register_id only -> will return followup and comments where ref_type = register and ref_id = register_id
		 * - In case 5: if have assign_user only -> will return followup and comments where assign_users that have param assign_user (get all followup that assign that user)
		 */
		
		current_username = fn_current_user();
		--call sp_user_log(9, current_username);
		
		if ((i_followup_id is null) and (i_booking_id is null) and (i_register_id is null) and (i_assign_user is null)) then
		---------------------
		--- In case 1: ------
		---------------------
			select json_agg(dataset)
			from (
				select fp.id, fp.ref_type, fp.ref_id, fp.title, fp.message, fp.followup_category_id, fpc."name" as followup_category_name, 
					   fp.followup_status_id, fps."name" as followup_status_name, alert_date, assign_users,
						(
							select coalesce(array_agg(json_build_object('id', id, 'name', name)), '{}') from alert_page where id = any(alert_pages_id)
						) as alert_pages_id,
						exists(
							select id from followup_seen where followup_id = fp.id and seen_username = current_username
						) as seen,
						(
							select coalesce(array_agg(json_build_object('user', seen_username, 'time', seen_time)), '{}') from followup_seen where followup_id = fp.id
						) as users_seen, create_user, create_time, modify_time, profile_user
				from followup fp 
				left join followup_category fpc on fp.followup_category_id = fpc.id
				left join followup_status fps on fp.followup_status_id = fps.id /*, lateral fn_current_user() as current_username*/
				order by fp.id desc
				limit 200
			) as dataset into json_result;
		elseif ((i_followup_id is not null) and (i_booking_id is null) and (i_register_id is null) and (i_assign_user is null)) then
		---------------------
		--- In case 2: ------
		---------------------
			select json_agg(dataset)
			from (
				select fp.id, fp.ref_type, fp.ref_id, fp.title, fp.message, fp.followup_category_id, fpc."name" as followup_category_name, 
					   fp.followup_status_id, fps."name" as followup_status_name, alert_date, assign_users,
						(
							select coalesce(array_agg(json_build_object('id', id, 'name', name)), '{}') from alert_page where id = any(alert_pages_id)
						) as alert_pages_id,
						exists(
							select id from followup_seen where followup_id = fp.id and seen_username = current_username
						) as seen,
						(
							select coalesce(array_agg(json_build_object('user', seen_username, 'time', seen_time)), '{}') from followup_seen where followup_id = fp.id
						) as users_seen, create_user, create_time, modify_time, profile_user,
						(
							select coalesce(array_agg(json_build_object('id', id, 'message', message, 'create_user', create_user, 'create_time', create_time, 'modify_time', modify_time, 'profile_user', profile_user)), '{}') from followup_comments where followup_id = fp.id
						) as followup_comments
				from followup fp 
				left join followup_category fpc on fp.followup_category_id = fpc.id
				left join followup_status fps on fp.followup_status_id = fps.id
				where fp.id = i_followup_id
			) as dataset into json_result;
		elseif ((i_followup_id is null) and (i_booking_id is not null) and (i_register_id is null) and (i_assign_user is null)) then
		---------------------
		--- In case 3: ------
		---------------------
			select json_agg(dataset)
			from (
				select fp.id, fp.ref_type, fp.ref_id, fp.title, fp.message, fp.followup_category_id, fpc."name" as followup_category_name, 
					   fp.followup_status_id, fps."name" as followup_status_name, alert_date, assign_users,
						(
							select coalesce(array_agg(json_build_object('id', id, 'name', name)), '{}') from alert_page where id = any(alert_pages_id)
						) as alert_pages_id,
						exists(
							select id from followup_seen where followup_id = fp.id and seen_username = current_username
						) as seen,
						(
							select coalesce(array_agg(json_build_object('user', seen_username, 'time', seen_time)), '{}') from followup_seen where followup_id = fp.id
						) as users_seen, create_user, create_time, modify_time, profile_user,
						(
							select coalesce(array_agg(json_build_object('id', id, 'message', message, 'create_user', create_user, 'create_time', create_time, 'modify_time', modify_time, 'profile_user', profile_user)), '{}') from followup_comments where followup_id = fp.id
						) as followup_comments
				from followup fp 
				left join followup_category fpc on fp.followup_category_id = fpc.id
				left join followup_status fps on fp.followup_status_id = fps.id
				where fp.ref_type = 'booking' and fp.ref_id = i_booking_id
			) as dataset into json_result;
		elseif ((i_followup_id is null) and (i_booking_id is null) and (i_register_id is not null) and (i_assign_user is null)) then
		---------------------
		--- In case 4: ------
		---------------------
			select json_agg(dataset)
			from (
				select fp.id, fp.ref_type, fp.ref_id, fp.title, fp.message, fp.followup_category_id, fpc."name" as followup_category_name, 
					   fp.followup_status_id, fps."name" as followup_status_name, alert_date, assign_users,
						(
							select coalesce(array_agg(json_build_object('id', id, 'name', name)), '{}') from alert_page where id = any(alert_pages_id)
						) as alert_pages_id,
						exists(
							select id from followup_seen where followup_id = fp.id and seen_username = current_username
						) as seen,
						(
							select coalesce(array_agg(json_build_object('user', seen_username, 'time', seen_time)), '{}') from followup_seen where followup_id = fp.id
						) as users_seen, create_user, create_time, modify_time, profile_user,
						(
							select coalesce(array_agg(json_build_object('id', id, 'message', message, 'create_user', create_user, 'create_time', create_time, 'modify_time', modify_time, 'profile_user', profile_user)), '{}') from followup_comments where followup_id = fp.id
						) as followup_comments
				from followup fp 
				left join followup_category fpc on fp.followup_category_id = fpc.id
				left join followup_status fps on fp.followup_status_id = fps.id
				where fp.ref_type = 'register' and fp.ref_id = i_register_id
			) as dataset into json_result;
		elseif ((i_followup_id is null) and (i_booking_id is null) and (i_register_id is null) and (i_assign_user is not null)) then
		---------------------
		--- In case 5: ------
		---------------------
			select json_agg(dataset)
			from (
				select fp.id, fp.ref_type, fp.ref_id, fp.title, fp.message, fp.followup_category_id, fpc."name" as followup_category_name, 
					   fp.followup_status_id, fps."name" as followup_status_name, alert_date, assign_users,
						(
							select coalesce(array_agg(json_build_object('id', id, 'name', name)), '{}') from alert_page where id = any(alert_pages_id)
						) as alert_pages_id,
						exists(
							select id from followup_seen where followup_id = fp.id and seen_username = current_username
						) as seen,
						(
							select coalesce(array_agg(json_build_object('user', seen_username, 'time', seen_time)), '{}') from followup_seen where followup_id = fp.id
						) as users_seen, create_user, create_time, modify_time, profile_user,
						(
							select coalesce(array_agg(json_build_object('id', id, 'message', message, 'create_user', create_user, 'create_time', create_time, 'modify_time', modify_time, 'profile_user', profile_user)), '{}') from followup_comments where followup_id = fp.id
						) as followup_comments
				from followup fp 
				left join followup_category fpc on fp.followup_category_id = fpc.id
				left join followup_status fps on fp.followup_status_id = fps.id
				where i_assign_user = any(fp.assign_users)
			) as dataset into json_result;		
		end if;
	return coalesce(json_result, '[]');
	END;

sp_get_followup_js

Description

Parameters

Definition