DELIMITER $$

USE `profiles`$$

DROP PROCEDURE IF EXISTS `sp_usermaingroup`$$

CREATE DEFINER=`GaringArmando`@`%` PROCEDURE `sp_usermaingroup`(
           OUT $ReturnIsSuccess		BIGINT  -- For SP Calling
	  ,$JSON			LONGTEXT
)
BEGIN
	DECLARE $_UserMainGroupID		VARCHAR(10)  DEFAULT NULL;
	DECLARE $_UserMainGroupName		VARCHAR(100)  DEFAULT NULL;
	DECLARE $_UserMainGroupDescription	VARCHAR(1000)  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_main_group(
					   @ReturnIsSuccess
					  ,'{
						"UserMainGroupName": ""
						,"Flag": "1"
					    }' 	
				);
				
				select * from M_System;
			*/
			SET $_View 	     	:= 'main_group_1';
			SET $_UserMainGroupName := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupName")));				
			
			IF $_UserMainGroupName 	= 'NULL' OR $_UserMainGroupName = '' THEN SET $_UserMainGroupName := NULL; END IF;
				 
			SELECT 
				 A.`UserMainGroupID`
				,A.`UserMainGroupName`
				,A.`UserMainGroupDescription`
				,B.`ReferenceID`		AS 'TableStatusID'
				,B.`ReferenceLongDescription`	AS 'TableStatus'
				,'1'				AS 'IsSuccess'
				,'Success'			AS 'Result'
			FROM 	`M_UserMainGroup` 		A
			JOIN 	`M_Reference` 			B ON B.`ReferenceID` 	 = A.`ReferenceTableStatusID`
			WHERE 
			      A.`UserMainGroupName` 		LIKE CONCAT(IFNULL($_UserMainGroupName, A.`UserMainGroupName` ),'%')
			AND   A.ReferenceTableStatusID		= IFNULL($_ReferenceTableStatusID, A.ReferenceTableStatusID);
			
		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_main_group(
					   @ReturnIsSuccess
					  ,'{
						"UserMainGroupName": ""
						,"Flag": "2"
					    }' 	
				);
			*/
			
			SET $_View 	     	:= 'main_group_2';
			SET $_UserMainGroupName := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupName")));				
			SET $_UserMainGroupDescription := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupDesc")));				
			
			IF $_UserMainGroupName = 'NULL' OR $_UserMainGroupName = '' THEN SET $_UserMainGroupName := NULL; END IF;
			IF $_UserMainGroupDescription = 'NULL' OR $_UserMainGroupDescription = '' THEN SET $_UserMainGroupDescription := NULL; END IF;
			
			IF $_UserMainGroupName IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'UserMainGroupName is required.';
			ELSE 
				SET $_UserMainGroupID  := (SELECT IFNULL(MAX(`UserMainGroupID`), 0) + 1 FROM `M_UserMainGroup`);
				
				INSERT INTO `M_UserMainGroup`(
					 `UserMainGroupID`
					,`UserMainGroupName`
					,`UserMainGroupDescription`
					,`AddedByUserID`
				) VALUES (
					 $_UserMainGroupID
					,$_UserMainGroupName
					,$_UserMainGroupDescription
					,$_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 account update';     						
				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_main_group(
					   @ReturnIsSuccess
					  ,'{
						"UserMainGroupName": ""
						,"MainGroupID": "13"
						,"Flag": "3"
					    }' 	
				);
				select * from `M_UserMainGroup`
			*/
			
			SET $_View 	     		:= 'main_group_3';
			SET $_UserMainGroupID 		:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupID")));			
			SET $_UserMainGroupName		:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupName")));				
			SET $_UserMainGroupDescription 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupDesc")));				
			
			IF $_UserMainGroupID		= 'NULL' OR $_UserMainGroupID = '' THEN SET $_UserMainGroupID := NULL; END IF;
			IF $_UserMainGroupName		= 'NULL' OR $_UserMainGroupName = '' THEN SET $_UserMainGroupName := NULL; END IF;
			IF $_UserMainGroupDescription	= 'NULL' OR $_UserMainGroupDescription = '' THEN SET $_UserMainGroupDescription := NULL; END IF;
			
			IF $_UserMainGroupID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'UserMainGroupID is required.';
			ELSEIF $_UserMainGroupName IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'UserMainGroupName is required.';				
			ELSE 
				
				UPDATE `M_UserMainGroup`
				SET
					 `UserMainGroupName` = IFNULL($_UserMainGroupName, UserMainGroupName)
					,`UserMainGroupDescription` = IFNULL($_UserMainGroupDescription, UserMainGroupDescription)
					,ReferenceTableStatusID = IFNULL($_ReferenceTableStatusID, ReferenceTableStatusID)
					,DateUpdated = NOW()
					,UpdatedByUserID = $_TransactBy
				WHERE
					`UserMainGroupID` = $_UserMainGroupID;
				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 maingrou status
				DateCreated     : Jan 21, 2023
				Time 		: 9:30AM
				Module Used:
				
				CALL sp_main_group(
					   @ReturnIsSuccess
					  ,'{
						 "MainGroupID": "17"
						,"ReferenceTableStatusID": "2"
						,"Flag": "4"
					    }' 	
				);
				select * from `M_UserMainGroup` where UserMainGroupID=17
			*/
			
			SET $_View		:= 'main_group_4';
			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 
				 
				IF $_ReferenceTableStatusID = 1 THEN SET @_TableStatusID = 2; ELSE SET @_TableStatusID = 1;  END IF;
				
				UPDATE `M_UserMainGroup`
				SET
					 ReferenceTableStatusID = @_TableStatusID
					,DateUpdated = NOW()
					,UpdatedByUserID = $_TransactBy
				WHERE
					`UserMainGroupID` = $_UserMainGroupID;
					
				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_usermaingroup(
					   @ReturnIsSuccess
					  ,'{
						"Flag": "5"
					    }' 	
				);
				
				select * from M_System;
			*/
			SET $_View 	     	:= 'usermaingroup_5';
				 
			SELECT 
				 A.`UserMainGroupID`
				,A.`UserMainGroupName`
				,'1'			AS 'IsSuccess'
				,'Success'		AS 'Result'
			FROM `M_UserMainGroup` A
			WHERE 
				A.ReferenceTableStatusID = 1;
			
		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($_UserMainGroupID 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.`UserMainGroupName`
				,A.`UserMainGroupDescription`
				,B.`ReferenceID`		AS 'TableStatusID'
				,B.`ReferenceLongDescription`	AS 'TableStatus'
				,'1'				AS 'IsSuccess'
				,'Success'			AS 'Result'
				,$_View				AS 'View'
			FROM 	`M_UserMainGroup` 		A
			JOIN 	`M_Reference` 			B ON B.`ReferenceID` 	 = A.`ReferenceTableStatusID`
			WHERE 
			      A.`UserMainGroupID` = $_UserMainGroupID;	        
		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 ;