DELIMITER $$

USE `profiles`$$

DROP PROCEDURE IF EXISTS `sp_usergroup`$$

CREATE DEFINER=`GaringArmando`@`%` PROCEDURE `sp_usergroup`(
           OUT $ReturnIsSuccess		BIGINT  -- For SP Calling
	  ,$JSON			LONGTEXT
)
BEGIN
	DECLARE $_UserGroupID		VARCHAR(10)  DEFAULT NULL;
	DECLARE $_UserGroupCode		VARCHAR(50)  DEFAULT NULL;
	DECLARE $_UserMainGroupID	VARCHAR(10)  DEFAULT NULL;
	DECLARE $_UserGroupName		VARCHAR(500)  DEFAULT NULL;

	
	/*standard variables start*/	
	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; 
	/*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          : Armando Garing II	
				Table Accessed  : 
				Description     : select 
				DateCreated     : Sept 03, 2022
				Time 		: 8:40PM
				
				Module Used:
				
				CALL sp_usergroup(
					   @ReturnIsSuccess
					  ,'{
						"GroupName": ""
						,"MainGroupID": "4"
						,"Flag": "1"
						
					    }' 	
				);
				
				select * from M_UserGroup;
			*/
			SET $_View	 := 'usergroup_1';
			SET $_UserGroupName := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.GroupName")));				
			SET $_UserMainGroupID := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupID")));				
			
			IF $_UserGroupName = 'NULL' OR $_UserGroupName = '' THEN SET $_UserGroupName := NULL; END IF;
			IF $_UserMainGroupID = 'NULL' OR $_UserMainGroupID = '' THEN SET $_UserMainGroupID := NULL; END IF;
			

				SELECT 
					 A.`UserMainGroupID`
					,A.`UserGroupID`
					,A.`UserGroupName`
					,A.`UserGroupCode`
					,C.`UserMainGroupName`
					,B.`ReferenceID`		AS 'TableStatusID'
					,B.`ReferenceLongDescription`	AS 'TableStatus'
					,'1'				AS 'IsSuccess'
					,'Success'			AS 'Result'
				FROM 	`M_UserGroup` A
				JOIN 	`M_Reference` B ON B.`ReferenceID` = A.`ReferenceTableStatusID`
				JOIN 	`M_UserMainGroup` C ON C.`UserMainGroupID` = A.`UserMainGroupID`
				WHERE 
				      A.`UserGroupName` 		LIKE CONCAT(IFNULL($_UserGroupName, A.`UserGroupName` ),'%')
				AND   A.UserMainGroupID			= IFNULL($_UserMainGroupID, A.UserMainGroupID);			
							 
			IF $_Delimiter = 'SP' THEN 
			      SET $_ErrorNumber := 104;        -- return  para sa SP
			ELSE
			      SET $_ErrorNumber := 105;
			END IF;	
			
		END; -- end of flag 1	
	    WHEN 2 THEN 
		BEGIN
			/*
				Author          : Armando Garing II
				Table Accessed  : 
				Description     : insert
				DateCreated     : Jan 1, 2023
				Time 		: 9:30AM
				Module Used:
				
				CALL sp_usergroup(
					   @ReturnIsSuccess
					  ,'{
						 "GroupName": "momo123"
						,"MainGroupID": "19"
						,"Flag": "2"
					    }' 	
				);
				
				select * from M_UserMainGroup
				select * from M_UserGroup
			*/
			
			SET $_View 	      := 'usergroup_2';
			SET $_UserGroupName   := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.GroupName")));				
			SET $_UserMainGroupID := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupID")));				
			
			IF $_UserGroupName = 'NULL' OR $_UserGroupName = '' THEN SET $_UserGroupName := NULL; END IF;
			IF $_UserMainGroupID = 'NULL' OR $_UserMainGroupID = '' THEN SET $_UserMainGroupID := NULL; END IF;
			
			IF $_UserGroupName IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'UserGroupName is required.';
			ELSEIF $_UserMainGroupID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'UserMainGroupID is required.';				
			ELSE 
				SET $_UserGroupID  := (SELECT IFNULL(MAX(`UserGroupID`), 0) + 1 FROM `M_UserGroup`);
				SET @_UserMainGroupID  := (SELECT IFNULL(MAX(`UserMainGroupID`), 0) + 1 FROM `M_UserMainGroup`);
				SET @_UserGroupCode := CONCAT('GC',@_UserMainGroupID);
				
				INSERT INTO `M_UserGroup`(
					 `UserGroupID`
					,`UserGroupName`
					,`UserGroupCode`
					,`UserMainGroupID`
					,`AddedByUserID`
				) VALUES (
					 $_UserGroupID
					,$_UserGroupName
					,@_UserGroupCode
					,$_UserMainGroupID
					,$_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 group insert';     						
				END IF;	
			END IF;
		END; -- end of flag 2	
	    WHEN 3 THEN 
		BEGIN
			/*
				Author          : Armando Garing II
				Table Accessed  : 
				Description     : update
				DateCreated     : Jan 1, 2023
				Time 		: 9:30AM
				Module Used:
				
				CALL sp_usergroup(
					   @ReturnIsSuccess
					  ,'{
						"UserMainGroupName": ""
						,"MainGroupID": "13"
						,"Flag": "3"
					    }' 	
				);
				select * from `M_UserMainGroup`
				select * from `M_UserGroup`
			*/
			
			SET $_View	      := 'usergroup_3';
			SET $_UserMainGroupID := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupID")));			
			SET $_UserGroupName   := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.GroupName")));				
			SET $_UserGroupID     := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.GroupID")));				
			
			IF $_UserMainGroupID   = 'NULL' OR $_UserMainGroupID = '' THEN SET $_UserMainGroupID := NULL; END IF;
			IF $_UserGroupName     = 'NULL' OR $_UserGroupName = '' THEN SET $_UserGroupName := NULL; END IF;
			IF $_UserGroupID       = 'NULL' OR $_UserGroupID = '' THEN SET $_UserGroupID := NULL; END IF;
			
			IF $_UserMainGroupID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'UserMainGroupID is required.';
			ELSEIF $_UserGroupName IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'UserGroupName is required.';				
			ELSEIF $_UserGroupID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'UserGroupID is required.';								
			ELSE 
				
				UPDATE `M_UserGroup`
				SET
					 `UserGroupName` = IFNULL($_UserGroupName, UserGroupName)
					,`UserMainGroupID` = IFNULL($_UserMainGroupID, UserMainGroupID)
					,ReferenceTableStatusID = IFNULL($_ReferenceTableStatusID, ReferenceTableStatusID)
					,DateUpdated = NOW()
					,UpdatedByUserID = $_TransactBy
				WHERE
					`UserGroupID` = $_UserGroupID;
					
				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 group update';     						
				END IF;	
			END IF;
			
		END; -- end of flag 3	
	    WHEN 4 THEN 
		BEGIN
			/*
				Author          : Armando Garing II
				Table Accessed  : 
				Description     : update maingrou status
				DateCreated     : Jan 21, 2023
				Time 		: 9:30AM
				Module Used:
				
				CALL sp_usergroup(
					   @ReturnIsSuccess
					  ,'{
						 "GroupID": "17"
						,"ReferenceTableStatusID": "2"
						,"Flag": "4"
					    }' 	
				);
				select * from `M_UserGroup` where UserMainGroupID=17
			*/
			
			SET $_View	  := 'usergroup_4';
			SET $_UserGroupID := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.GroupID")));			
			
			IF $_UserGroupID   = 'NULL' OR $_UserGroupID = '' THEN SET $_UserGroupID := NULL; END IF;
			
			IF $_UserGroupID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'UserMainGroupID is required.';
			ELSE 
				 
				IF $_ReferenceTableStatusID = 1 THEN SET @_TableStatusID = 2; ELSE SET @_TableStatusID = 1;  END IF;
				
				UPDATE `M_UserGroup`
				SET
					 ReferenceTableStatusID = @_TableStatusID
					,DateUpdated = NOW()
					,UpdatedByUserID = $_TransactBy
				WHERE
					`UserGroupID` = $_UserGroupID;
					
				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
			/*
				Author          : Armando Garing II	
				Table Accessed  : 
				Description     : lookup 
				DateCreated     : Jan 22, 2023
				Time 		: 8:40PM
				
				Module Used:
				
				CALL sp_usergroup(
					   @ReturnIsSuccess
					  ,'{
						"Flag": "5"
					    }' 	
				);
				
				select * from M_System;
			*/
			SET $_View := 'usergroup_5';
			SET $_UserMainGroupID := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupID")));		
			
			IF $_UserMainGroupID   = 'NULL' OR $_UserMainGroupID = '' THEN SET $_UserMainGroupID := NULL; END IF;
			
			IF $_UserMainGroupID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'UserMainGroupID is required.';
			ELSE 
				SELECT 
					 A.`UserGroupID`
					,A.`UserGroupName`
					,'1'			AS 'IsSuccess'
					,'Success'		AS 'Result'
				FROM `M_UserGroup` A
				WHERE 
					A.`UserMainGroupID` = $_UserMainGroupID
					AND A.ReferenceTableStatusID = 1;			
								 
				IF $_Delimiter = 'SP' THEN 
				      SET $_ErrorNumber := 104;        -- return  para sa SP
				ELSE
				      SET $_ErrorNumber := 105;
				END IF;				
			END IF;				 

		END; -- end of flag 5		
	    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($_UserGroupID 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.`UserMainGroupID`
				,A.`UserGroupID`
				,A.`UserGroupName`
				,A.`UserGroupCode`
				,C.`UserMainGroupName`
				,B.`ReferenceID`		AS 'TableStatusID'
				,B.`ReferenceLongDescription`	AS 'TableStatus'
				,'1'				AS 'IsSuccess'
				,'Success'			AS 'Result'
				,$_View				AS 'View'
			FROM 	`M_UserGroup` 	A
			JOIN 	`M_Reference` 	B ON B.`ReferenceID` = A.`ReferenceTableStatusID`
			JOIN 	`M_UserMainGroup` C ON C.`UserMainGroupID` = A.`UserMainGroupID`			
			WHERE 
			      A.`UserGroupID` = $_UserGroupID;	        
		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 ;