DELIMITER $$

USE `dzi`$$

DROP PROCEDURE IF EXISTS `sp_entityname`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_entityname`(
           OUT $ReturnIsSuccess		BIGINT  -- For SP Calling
	  ,$JSON			LONGTEXT
)
BEGIN
	DECLARE $_EntityNameID			VARCHAR(10)  DEFAULT NULL;
	DECLARE $_EntityNameCode		VARCHAR(10)  DEFAULT NULL;
	DECLARE $_EntityName			VARCHAR(100)  DEFAULT NULL;
	DECLARE $_EntityNameDate		VARCHAR(50)  DEFAULT NULL;
	
	DECLARE $_ReferenceTableStatusID  	VARCHAR(10)  DEFAULT NULL;
	DECLARE $_Delimiter 	  	  	VARCHAR(50)  DEFAULT NULL;
	DECLARE $_TransactBy  		  	VARCHAR(10)  DEFAULT NULL;
	DECLARE $_Flag 	 	  	  	VARCHAR(50)  DEFAULT NULL;
	DECLARE $_View 	 	  	  	VARCHAR(50);
	DECLARE $_ErrorMsg 	 	  	VARCHAR(1000);
	DECLARE $_DelimiterData		  	LONGTEXT;
	DECLARE $_ErrorNumber	 	  	INT; 
	
	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
			/*
				Description     : select
		
				Module Used:
				
				CALL sp_entityname(
					   @ReturnIsSuccess		
					  ,'{
						 "EntityName": "Test"
						,"Flag": "1"
					    }' 	
				);
			*/
			SET $_View       := 'entityname_1';
			SET $_EntityName := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.EntityName")));
			
			IF $_EntityName   = 'NULL' OR $_EntityName = '' THEN SET $_EntityName := NULL; END IF;
			
			SELECT 
				 A.`EntityNameID` 		AS 'EntityNameID'
				,A.`EntityNameCode` 		AS 'EntityNameCode'
				,A.`EntityName`			
				,A.`ReferenceTableStatusID` 
				,B.ReferenceLongDescription	AS 'EntityStatus'
				,'1' 				AS 'IsSuccess'
				,'Success' 			AS 'Result' 
				,$_View				AS 'View'
			FROM `M_EntityName` A
			INNER JOIN M_Reference B ON A.ReferenceTableStatusID = B.ReferenceID
			WHERE 
				A.`EntityName` LIKE CONCAT( '%',IFNULL(TRIM($_EntityName), A.`EntityName`), '%' );	
		
		END; -- end of flag 1
	    WHEN 2 THEN 
		BEGIN
			/*
				Author          : Armando Garing II
				Table Accessed  : 
				Description     : insert
				DateCreated     : February 24, 2023
				Time 		: 7:30PM
				Module Used:
				
				CALL sp_entityname(
					   @ReturnIsSuccess
					  ,'{
						 "EntityName": "My EntityName"
						,"TransactBy": "1"
						,"ReferenceTableStatusID": "1"
						,"Flag": "2"
					    }' 
				);
				
				select * from M_EntityName order by EntityNameID desc
			*/
			
			SET $_EntityName 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.EntityName")));
			SET $_EntityNameDate 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.EntityNameDate")));
			
			IF $_EntityName         = 'NULL' OR $_EntityName     = '' THEN SET $_EntityName      := NULL; END IF;
			IF $_EntityNameDate 	= 'NULL' OR $_EntityNameDate = '' THEN SET $_EntityNameDate  := NULL; END IF;
			
			IF $_EntityName IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'EntityName is required.';
			ELSE 
				SET $_EntityNameID  := (SELECT IFNULL(MAX(`EntityNameID`), 0) + 1 FROM `M_EntityName`);
				
				SET @_EntityNameCode = CONCAT('EN',$_EntityNameID);
				
				INSERT INTO `M_EntityName`(
					 `EntityNameID`					
					,`EntityNameCode`
					,`EntityName`
					,`EntityNameDate`
					,`AddedByUserID`
				) VALUES (
					 $_EntityNameID
					,@_EntityNameCode
					,$_EntityName
					,$_EntityNameDate
					,$_TransactBy
				);	
				
				IF ROW_COUNT() > 0 THEN
					IF $_Delimiter = 'SP' THEN 
					      SET $_ErrorNumber := 100;        -- return  para sa SP
					ELSE
					      SET $_ErrorNumber := 103;
					END IF;	
				ELSE
					signal SQLSTATE '45000' SET message_text = 'Error on entityname insert';     						
				END IF;	
			END IF;
			
		END; -- end of flag 2		
	    WHEN 3 THEN 
		BEGIN
			/*
				Author          : Armando Garing II
				Table Accessed  : 
				Description     : update 
				DateCreated     : February 24, 2023
				Time 		: 8:40PM
				
				Module Used:
				
				CALL sp_entityname(
					   @ReturnIsSuccess
					  ,'{
   						 "EntityNameID": "25"
						,"EntityName": "My Entity Name"   						 
 						,"EntityNameDate": "2020-12-12"
						,"TransactBy": "1"
						,"Flag": "3"
					    }' 	
				);
				
				select * from M_EntityName where EntityNameID=25;
			*/
			
			SET $_View 		:= 'entityname_3';
			SET $_EntityNameID 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.EntityNameID")));
			SET $_EntityName 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.EntityName")));
			SET $_EntityNameDate	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.EntityNameDate")));
			
			IF $_EntityNameID 	= 'NULL' OR $_EntityNameID = '' THEN SET $_EntityNameID  := NULL; END IF;			
			IF $_EntityName 	= 'NULL' OR $_EntityName = '' THEN SET $_EntityName  := NULL; END IF;
			IF $_EntityNameDate 	= 'NULL' OR $_EntityNameDate = '' THEN SET $_EntityNameDate := NULL; END IF;
			
			IF $_EntityNameID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'EntityNameID is required.';
			ELSEIF $_TransactBy IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'TransactBy is required.';				
			ELSE 
				UPDATE `M_EntityName`
				SET
					 `EntityName` 		= IFNULL($_EntityName, EntityName)
					,`EntityNameDate`	= IFNULL($_EntityNameDate, EntityNameDate)
					,ReferenceTableStatusID = IFNULL($_ReferenceTableStatusID, ReferenceTableStatusID)
					,DateUpdated 		= NOW()
					,UpdatedByUserID 	= $_TransactBy
				WHERE
					EntityNameID = $_EntityNameID;
				IF ROW_COUNT() > 0 THEN
					IF $_Delimiter = 'SP' THEN 
					      SET $_ErrorNumber := 100; -- return  para sa SP
					ELSE
					      SET $_ErrorNumber := 103;
					END IF;	
				ELSE
					signal SQLSTATE '45000' SET message_text = 'Error on account update';     						
				END IF;	
			END IF;
			
		END; -- end of flag 3	
	    WHEN 4 THEN 
		BEGIN
			/*
				Author          : Armando Garing II
				Table Accessed  : 
				Description     : update entity name status
				DateCreated     : Feb 25, 2023
				Time 		: 9:30AM
				Module Used:
				
				CALL sp_entityname(
					   @ReturnIsSuccess
					  ,'{
						 "EntityNameID": "17"
						,"ReferenceTableStatusID": "2"
						,"Flag": "4"
					    }' 	
				);
				select * from `M_EntityName` where UserMainGroupID=17
			*/
			
			SET $_View	  := 'entityname_4';
			SET $_EntityNameID := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.EntityNameID")));			
			
			IF $_EntityNameID   = 'NULL' OR $_EntityNameID = '' THEN SET $_EntityNameID := NULL; END IF;
			
			IF $_EntityNameID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'EntityNameID is required.';
			ELSE 
				 
				IF $_ReferenceTableStatusID = 1 THEN SET @_TableStatusID = 2; ELSE SET @_TableStatusID = 1;  END IF;
				
				UPDATE `M_EntityName`
				SET
					 ReferenceTableStatusID = @_TableStatusID
					,DateUpdated = NOW()
					,UpdatedByUserID = $_TransactBy
				WHERE
					`EntityNameID` = $_EntityNameID;
					
				IF ROW_COUNT() > 0 THEN
					IF $_Delimiter = 'SP' THEN 
					      SET $_ErrorNumber := 100;        -- return  para sa SP
					ELSE
					      SET $_ErrorNumber := 103;
					END IF;	
				ELSE
					signal SQLSTATE '45000' SET message_text = 'Error on account update';     						
				END IF;	
			END IF;
		END; -- end of flag 4	
	    WHEN 5 THEN 
		BEGIN
			/*
				Description     : select
		
				Module Used:
				
				CALL sp_entityname(
					   @ReturnIsSuccess		
					  ,'{
						 "EntityName": "Test"
						,"Flag": "1"
					    }' 	
				);
			*/
			SET $_View       := 'entityname_5';
			SET $_EntityName := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.EntityName")));
			
			IF $_EntityName   = 'NULL' OR $_EntityName = '' THEN SET $_EntityName := NULL; END IF;
			
			SELECT 
				 A.`EntityNameID` 		AS 'EntityNameID'
				,A.`EntityNameCode` 		AS 'EntityNameCode'
				,A.`EntityName`			
				,A.`ReferenceTableStatusID` 
				,B.ReferenceLongDescription	AS 'EntityStatus'
				,'1' 				AS 'IsSuccess'
				,'Success' 			AS 'Result' 
				,$_View				AS 'View'
			FROM `M_EntityName` A
			INNER JOIN M_Reference B ON A.ReferenceTableStatusID = B.ReferenceID
			WHERE 
				A.`EntityName` LIKE CONCAT( '%',IFNULL(TRIM($_EntityName), A.`EntityName`), '%' );	
		
		END; -- end of flag 5	
	    WHEN 6 THEN 
		BEGIN
			/*
				Author          : Arnalen Zoilo	
				Table Accessed  : M_EntityName
				Description     : Display Client Information
				DateCreated     : May 10, 2023
				Time 		: 9:49 PM
			
				Module Used:

				
				CALL sp_entityname(
					   @ReturnIsSuccess		
					  ,'{
						 "EntityName": "Mabolo"
						,"Flag":"6"
					    }' 	
				);
			*/
			SET $_View       := 'entityname_6';
			SET $_EntityName := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.EntityName")));
			
			IF $_EntityName   = 'NULL' OR $_EntityName = '' THEN SET $_EntityName := NULL; END IF;
			
			SELECT 
				 A.`EntityNameID`
				,A.`EntityNameCode`
				,A.`EntityName`
				,B.`BranchID`
				,B.`BranchCode`
				,B.`BranchName`
				,B.`BarangayID`
				,CONCAT(IFNULL(B.`Street`,''),' ', IFNULL(fn_address(B.`BarangayID`,NULL,1),''))	AS 'Address'
				,IFNULL(CONCAT(E.`ProfileFirstName`,' ',E.`ProfileLastName`,' ',IFNULL(E.`ProfileExtName`,'')),'') 	AS 'ContactPerson'
				,1 			AS 'IsSuccess'
			FROM `M_EntityName` A
			INNER JOIN `M_Branch` B ON A.`EntityNameID` = B.`EntityNameID` AND B.`ReferenceTableStatusID` =1 
			INNER JOIN `M_Entity` C ON B.`BranchID` = C.`BranchID` AND C.`ReferenceTableStatusID` = 1
			LEFT JOIN `MT_ContactPerson` D ON B.`BranchID` = D.`BranchID` AND D.`ReferenceTableStatusID` = 1
			LEFT JOIN `M_Profile` E ON D.`ProfileID` = E.`ProfileID` AND E.`ReferenceTableStatusID` = 1
			WHERE 
				A.`EntityName` LIKE CONCAT( '%',IFNULL(TRIM($_EntityName), A.`EntityName`), '%' );	
		
		END; -- end of flag 6	
	    ELSE
			signal SQLSTATE '45000' SET message_text = 'Flag is required.';				
	END CASE;  
	
	/*CATCH PARA SA MGA RETURNS START*/
	CASE $_ErrorNumber
	     WHEN 100   THEN -- sp return success
		SET autocommit 	       := 0;
		SET $ReturnIsSuccess   := CAST($_EntityNameID AS INT);
	     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;
			
			SELECT 
				 A.`EntityNameID` 		AS 'EntityNameID'
				,A.`EntityNameCode` 		AS 'EntityNameCode'
				,A.`EntityName`			
				,A.`ReferenceTableStatusID` 
				,B.ReferenceLongDescription	AS 'EntityStatus'
				,'1' 				AS 'IsSuccess'
				,'Success' 			AS 'Result' 
				,$_View				AS 'View'
			FROM `M_EntityName` A
			INNER JOIN M_Reference B ON A.ReferenceTableStatusID = B.ReferenceID
			WHERE
			      A.`EntityNameID` = CAST($_EntityNameID AS INT);	        
		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 ;