authen.public

Parameters

Name	Type	Mode
i_report_id	integer	IN (DEFAULT NULL)
i_report_name	text	IN (DEFAULT NULL)
i_category	text	IN (DEFAULT NULL)
i_report_type	text	IN (DEFAULT NULL)
i_options	jsonb	IN (DEFAULT NULL)
i_path_url	text	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
i_report_filename	text	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT
report_id	integer	OUT
filename	text	OUT

Definition

declare
		_old_id_report integer;
		_old_report_name text;
		_old_report_filename text;
	begin
		/**
		 * Create by KenG (2021/02/17)
		 * This stored procedure insert or update report base on conditions
		 * - Incase i_report_id is null are new report 
		 * - Incase i_report_id is not null are old report that check id in reports table
		 */
		
		result_code = '0'; result_msg = 'Success'; msg_code = '00'; report_id = null; filename = null;
	
		/**
		 * ******Incase new report
		 */
		if i_report_id is null then
			-- Check report name if already has report name that alert to user change other report name
			_old_report_name = (select report_name from reports where report_name = i_report_name limit 1);
			if _old_report_name <> '' then
				result_code = '4';
				result_msg = 'This report name is repeated, Please change other report name!';
				msg_code = null;
				return; -- Return this function do not action below line anything
			end if;
		
			/*--Check filename if already has filename that alert to user change other report_filename
			_old_report_filename = (select report_filename from reports where report_filename = i_report_filename limit 1);
			if _old_report_filename <> '' then
				result_code = '4';
				result_msg = 'This report filename is repeated, Please change other report filename!';
				msg_code = null;
				return; -- Return this function do not action below line anything
			end if;*/
			
			-- Add new report
			insert into reports(report_name, category, report_type, "options", path_url, report_filename)
			values (i_report_name, i_category, i_report_type, i_options, i_path_url, i_report_filename)
			returning id into report_id;
			filename = i_report_filename;
			result_code = '0'; 
			result_msg = 'Created'; 
			msg_code = '00';
			return; -- Return this function do not action below line anything
			/*with add_new_report as (
				insert into reports(report_name, category, report_type, "options", path_url, report_filename)
				values (i_report_name, i_category, i_report_type, i_options, i_path_url, i_report_filename)
				returning *
			);
			report_id = (select id from add_new_report);
			filename = (select report_filename from add_new_report);
			return; -- Return this function do not action below line anything*/
		end if;

/**
		 * ******Incase old report
		 */
		_old_id_report = (select id from reports where id = i_report_id);
		-- Check exist report
		if _old_id_report is null then
			result_code = '4';
			result_msg = 'Do not exist this report, Please check id!';
			msg_code = null;
			return; -- Return this function do not action below line anything
		end if;
	
	
		-- Check report name if already has report name that alert to user change other report name
		_old_report_name = (select report_name from reports where id <> i_report_id and report_name = i_report_name limit 1);
		if _old_report_name <> '' then
			result_code = '4';
			result_msg = 'This report name is repeated, Please change other report name!';
			msg_code = null;
			return; -- Return this function do not action below line anything
		end if;
	
	
		/*--Check filename if already has filename that alert to user change other report_filename
		_old_report_filename = (select report_filename from reports where id <> i_report_id and report_filename = i_report_filename limit 1);
		if _old_report_filename <> '' then
			result_code = '4';
			result_msg = 'This report filename is repeated, Please change other report filename!';
			msg_code = null;
			return; -- Return this function do not action below line anything
		end if;		*/
	
	
		-- Update new value
		update reports ur
		set report_name = i_report_name, category = i_category,
			report_type = i_report_type, "options" = i_options, 
			path_url = i_path_url, report_filename = i_report_filename
		where ur.id = i_report_id;
	-- retrun id and filename of reports
	result_code = '0'; 
	result_msg = 'Updated'; 
	msg_code = '00';
	report_id = i_report_id;
	filename = i_report_filename;
	END;

sp_update_reports

Parameters

Definition