DELIMITER $$

USE `dzi`$$

DROP PROCEDURE IF EXISTS `sp_contacttype`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contacttype`(
           OUT $ReturnIsSuccess		BIGINT  -- For SP Calling
	  ,$JSON			LONGTEXT
)
BEGIN
	DECLARE $_ContactTypeID			VARCHAR(20)    	DEFAULT NULL;
	DECLARE $_ContactTypeName		VARCHAR(50)    	DEFAULT NULL;
	DECLARE $_ContactTypeCode		VARCHAR(3)    	DEFAULT NULL;
	DECLARE $_ContactTypeGroupCode		VARCHAR(2)    	DEFAULT NULL;
	DECLARE $_ReferenceTableStatusID  	VARCHAR(20)    	DEFAULT NULL;
	
	/*standard variables start*/	
	DECLARE $_Delimiter 	  	  	VARCHAR(50)  	DEFAULT NULL;
	DECLARE $_ErrorMsg 	 	  	VARCHAR(1000);	
	DECLARE $_DelimiterData		  	LONGTEXT;
	DECLARE $_ErrorNumber	 	  	INT;
	DECLARE $_TransactBy  		  	VARCHAR(10)  	DEFAULT NULL;
	DECLARE $_View				VARCHAR(50);
	DECLARE $_Flag				VARCHAR(50)	DEFAULT NULL;	
	/*standard variables end*/
	
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	 BEGIN
	  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
	   @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
	  SET @full_error = @text;
	  SELECT '0' AS 'IsSuccess', @full_error AS 'Result';
	  ROLLBACK;
	END;	
	
	SET autocommit 		   := 0;
	SET max_sp_recursion_depth := 255;
	
	/*Do not remove default common parameter start*/
	SET $_ReferenceTableStatusID  	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.ReferenceTableStatusID")));
	SET $_Delimiter  		:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.Delimiter")));
	SET $_TransactBy 		:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.TransactBy")));
	SET $_Flag 	 		:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.Flag")));
	
	IF $_ReferenceTableStatusID 	= 'NULL' OR $_ReferenceTableStatusID  = '' THEN SET $_ReferenceTableStatusID = NULL; END IF;
	IF $_Delimiter	  	    	= 'NULL' OR $_Delimiter   	      = '' THEN SET $_Delimiter  	     = NULL; END IF;
	IF $_TransactBy   	    	= 'NULL' OR $_TransactBy  	      = '' THEN SET $_TransactBy 	     = NULL; END IF;
	IF $_Flag 	  	    	= 'NULL' OR $_Flag 	    	      = '' THEN SET $_Flag       	     = NULL; END IF;
	/*Do not remove default common parameter end*/
		
	CASE $_Flag
	     WHEN 1 THEN 
		BEGIN
			/*
				Author          : Arnalen Zoilo	
				Table Accessed  : M_Address
				Description     : Display address lookup
				DateCreated     : May 10, 2023
				Time 		: 4:25 PM
			
				Module Used:

				
				CALL sp_contacttype(
					   @ReturnIsSuccess		
					  ,'{
						 "Search": "Mabolo"
						,"Flag":"3"
					    }' 	
				);
				
			*/
			SET $_View 		:= 'contacttype_1';
			

			SET @_Search 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.Search")));
			
			
			IF @_Search	= 'NULL' OR @_Search 	= '' THEN SET @_Search  := NULL; END IF;
			
			SELECT 
				`ContactTypeID`
				,`ContactTypeName`
				,`ContactTypeCode`
				,`ContactTypeGroupCode`
				,`ReferenceTableStatusID`
				,1 				AS 'IsSuccess'
				,'Success'			AS 'Result'
			FROM `M_ContactType` 
			WHERE `ContactTypeName` LIKE CONCAT('%',IFNULL(@Search,`ContactTypeName`),'%')
			ORDER BY `ContactTypeID`;
			
			IF $_Delimiter = 'SP' THEN 
			      SET $_ErrorNumber := 104;        -- return  para sa SP
			ELSE
			      SET $_ErrorNumber := 105;
			END IF;	

		END; -- end of flag 1
	END CASE;  
	/*CATCH PARA SA MGA RETURNS START*/
	CASE $_ErrorNumber
	     WHEN 100   THEN -- sp return success
		SET autocommit 	       := 0;
		SET $ReturnIsSuccess   := $_ContactTypeID;
	     WHEN 101   THEN -- error return for SP
	        SET autocommit         := 0;
		SET $ReturnIsSuccess   := 0;
	     WHEN 102  THEN -- error return for model 
		SET autocommit 	       := 0;
		SELECT
		      0       		AS 'IsSuccess'
		     ,$_ErrorMsg    	AS 'Result';
		ROLLBACK;
	     WHEN 103  THEN
	        BEGIN
			SET autocommit := 1;	        
		END;
	     WHEN 104  THEN -- return for select within the sp
		SET autocommit := 0;
	     WHEN 105  THEN -- return for select where the query is coming from model
		SET autocommit := 1;
	     ELSE
		signal SQLSTATE '45000' SET message_text = 'ErrorNumber is required.';	         
	END CASE;  
	/*CATCH PARA SA MGA RETURNS END*/
END$$

DELIMITER ;