DELIMITER $$

USE `profiles`$$

DROP PROCEDURE IF EXISTS `sp_usergroupmember`$$

CREATE DEFINER=`GaringArmando`@`%` PROCEDURE `sp_usergroupmember`(
           OUT $ReturnIsSuccess		BIGINT  -- For SP Calling
	  ,$JSON			LONGTEXT
)
BEGIN
	DECLARE $_UserGroupMemberID		VARCHAR(10)  DEFAULT NULL;
	DECLARE $_UserMainGroupID		VARCHAR(10)  DEFAULT NULL;
	DECLARE $_UserGroupID			VARCHAR(10)  DEFAULT NULL;
	DECLARE $_UserLoginID			VARCHAR(10)  DEFAULT NULL;
	DECLARE $_EffectiveDate 		VARCHAR(30)  DEFAULT NULL;
	DECLARE $_ExpiryDate			VARCHAR(30)  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     : Jan 22, 2023
				Time 		: 8:40PM
				
				Module Used:
				
				CALL sp_usergroupmember(
					   @ReturnIsSuccess
					  ,'{
						 "ProfileName": "Armando"
						,"Flag": "1"
					    }' 	
				);
				
				select * from M_UserGroupMember;
			*/
			SET $_View 	     	:= 'usergroupmember_1';
			SET @_ProfileName 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.ProfileName")));				
			SET @_UserMainGroupID 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupID")));	
			SET $_UserLoginID 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.UserLoginID")));	
			SET $_UserGroupID 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.GroupID")));	
			
			IF @_ProfileName 	= 'NULL' OR @_ProfileName	= '' THEN SET @_ProfileName 	  := NULL; END IF;
			IF @_UserMainGroupID 	= 'NULL' OR @_UserMainGroupID 	= '' THEN SET @_UserMainGroupID   := NULL; END IF;
			IF $_UserLoginID 	= 'NULL' OR $_UserLoginID 	= '' THEN SET $_UserLoginID 	  := NULL; END IF;
			IF $_UserGroupID 	= 'NULL' OR $_UserGroupID 	= '' THEN SET $_UserGroupID 	  := NULL; END IF;
				 
			SELECT 
			  A.UserGroupMemberID,
			  A.UserGroupID,
			  A.UserLoginID,
			  A.EffectiveDate,
			  DATE_FORMAT( A.EffectiveDate, '%b. %d, %Y') AS 'EffectiveDateText',
			  A.ExpiryDate,
			  DATE_FORMAT( A.ExpiryDate, '%b. %d, %Y') AS 'ExpiryDateText',
			  A.ReferenceTableStatusID		AS 'TableStatusID',
			  F.ReferenceLongDescription		AS 'TableStatus',
			  B.UserGroupName,
			  C.UserLoginName,
			  C.UserLoginPassword,
			  D.UserMainGroupID,
			  D.UserMainGroupName,
			  UPPER(CONCAT(CONCAT(TRIM(E.`ProfileFirstName`),' ',IFNULL(TRIM(E.`ProfileMiddleName`),'')),CONCAT(' ',TRIM(E.`ProfileLastName`),' ',IFNULL(TRIM(E.`ProfileExtName`),'')))) AS 'ProfileName',
			  E.ProfileFirstName,
			  E.ProfileMiddleName,
			  E.ProfileLastName,
			  E.ProfileExtName,
			  'Success'			AS 'Result',
			  $_View			AS 'View'			  
			FROM M_UserGroupMember			A
			  INNER JOIN M_UserGroup		B ON B.UserGroupID 	= A.UserGroupID
			  INNER JOIN M_UserLogin		C ON C.UserLoginID	= A.UserLoginID
			  INNER JOIN M_UserMainGroup		D ON D.UserMainGroupID	= B.UserMainGroupID		  
			  INNER JOIN M_Profile			E ON E.ProfileID 	= C.ProfileID
			  INNER JOIN M_Reference		F ON F.ReferenceID	= A.ReferenceTableStatusID
			WHERE A.`UserLoginID`			= IFNULL($_UserLoginID,A.`UserLoginID`)
			  AND A.`UserGroupID`			= IFNULL($_UserGroupID,A.`UserGroupID`)
			  AND D.`UserMainGroupID`		= IFNULL(@_UserMainGroupID,D.`UserMainGroupID`)
			  AND UPPER(CONCAT(CONCAT(TRIM(E.`ProfileFirstName`),' ',IFNULL(TRIM(E.`ProfileMiddleName`),'')),CONCAT(' ',TRIM(E.`ProfileLastName`),' ',IFNULL(TRIM(E.`ProfileExtName`),'')))) LIKE CONCAT('%',IFNULL(@_ProfileName, UPPER(CONCAT(CONCAT(TRIM(E.`ProfileFirstName`),' ',IFNULL(TRIM(E.`ProfileMiddleName`),'')),CONCAT(' ',TRIM(E.`ProfileLastName`),' ',IFNULL(TRIM(E.`ProfileExtName`),'')))) ),'%');
			  
			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
			Description     : display logins
			DateCreated     : Jan. 22, 2023
			Time 		: 8:00PM
			
			Module used:		
					
				
			CALL sp_usergroupmember(
				@ReturnIsSuccess		
				,'{
				      "SearchData": ""
				     ,"Flag": "2"
				  }' 	
				);
			*/
			
			SET $_View       := 'userlogin_2';
			SET @_SearchData := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.SearchData")));				
			
			IF @_SearchData	  = 'NULL' OR @_SearchData = '' THEN SET @_SearchData := NULL; END IF;
			
			SELECT 
				  A.UserLoginID,
				  A.ProfileID,
				  A.UserLoginName,
				  A.UserLoginPW_LastChanged,
				  A.ReferenceTableStatusID,
				  UPPER(CONCAT(CONCAT(TRIM(B.`ProfileFirstName`),' ',IFNULL(TRIM(B.`ProfileMiddleName`),'')),CONCAT(' ',TRIM(B.`ProfileLastName`),' ',IFNULL(TRIM(B.`ProfileExtName`),'')))) AS 'ProfileName',
				  DATE_FORMAT( B.ProfileBirthdate, '%b. %d, %Y') AS 'ProfileBirthdate',
				  DATE_FORMAT(NOW(),'%Y-%m-%d') AS 'CurrentDate',
				  C.ReferenceID			AS 'TableStatusID',
				  C.ReferenceLongDescription	AS 'TableStatus',
				  'Success'			AS 'Result',
				  $_View			AS 'View'
			FROM M_UserLogin		A
			INNER JOIN M_Profile		B ON B.ProfileID 	= A.ProfileID
			INNER JOIN M_Reference		C ON C.ReferenceID	= A.ReferenceTableStatusID
			WHERE 
				A.ReferenceTableStatusID = 1
			  AND UPPER(CONCAT(CONCAT(TRIM(B.`ProfileFirstName`),' ',IFNULL(TRIM(B.`ProfileMiddleName`),'')),CONCAT(' ',TRIM(B.`ProfileLastName`),' ',IFNULL(TRIM(B.`ProfileExtName`),'')))) LIKE CONCAT('%',IFNULL(@_ProfileName, UPPER(CONCAT(CONCAT(TRIM(B.`ProfileFirstName`),' ',IFNULL(TRIM(B.`ProfileMiddleName`),'')),CONCAT(' ',TRIM(B.`ProfileLastName`),' ',IFNULL(TRIM(B.`ProfileExtName`),'')))) ),'%');				
			  
			IF $_Delimiter 		 = 'SP' THEN 
			      SET $_ErrorNumber := 104;        -- return  para sa SP
			ELSE						
			      SET $_ErrorNumber := 105;						
			END IF;							 
		END; -- end of flag 2	
	    WHEN 3 THEN 
		BEGIN
			/*
				Author          : Armando Garing II
				Table Accessed  : 
				Description     : insert
				DateCreated     : August 08, 2022
				Time 		: 7:30PM
				Module Used:
				
				CALL sp_usergroupmember(
						    @ReturnIsSuccess
						,'{
						    "LoginIDs":
						    [
							{
							  "UserLoginID":"1"
							},
							{
							  "UserLoginID":"2"
							},
							{
							  "UserLoginID":"3"
							}							
						    ],
						    "TransactBy":"1",
						    "Flag":3						    
						}'
				);
			SELECT * FROM M_UserGroupMember order by UserGroupMemberID desc
			*/
			SET $_View := 'usergroupmember_3';

			SET @_Data = JSON_EXTRACT($JSON,'$.LoginIDs');
			SET @_DataLength = JSON_LENGTH(@_Data);
			SET @row_count = 0;
			SET @i = 0;
			
			CREATE TEMPORARY TABLE GroupMemberIDs(
			    UserGroupMemberID INT
			);
						
			WHILE @i < @_DataLength DO
							
				SET $_UserLoginID = JSON_UNQUOTE(JSON_EXTRACT(@_Data,CONCAT('$[',@i,'].UserLoginID'))); 				
				SET $_UserMainGroupID := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.MainGroupID")));		
				SET $_UserGroupID := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.GroupID")));		
				SET $_EffectiveDate := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.EffectiveDate")));
				SET $_ExpiryDate := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.ExpiryDate")));
	
				
				IF $_UserLoginID  = 'NULL' OR $_UserLoginID = '' THEN SET $_UserLoginID = NULL; END IF;	
				IF $_UserMainGroupID  = 'NULL' OR $_UserMainGroupID = '' THEN SET $_UserMainGroupID = NULL; END IF;	
				IF $_UserGroupID  = 'NULL' OR $_UserGroupID = '' THEN SET $_UserGroupID = NULL; END IF;	
				IF $_EffectiveDate  = 'NULL' OR $_EffectiveDate = '' THEN SET $_EffectiveDate = NULL; END IF;	
				IF $_ExpiryDate  = 'NULL' OR $_ExpiryDate = '' THEN SET $_ExpiryDate = NULL; END IF;	
				
				IF $_UserLoginID IS NULL THEN
					signal SQLSTATE '45000' SET message_text = 'UserLoginID is required.';	
				ELSEIF $_UserGroupID IS NULL THEN
					signal SQLSTATE '45000' SET message_text = 'UserGroupID is required.';
				ELSEIF $_EffectiveDate IS NULL THEN
					signal SQLSTATE '45000' SET message_text = 'EffectiveDate is required.';																	
				ELSE 
					-- insert kung wala pa na add ang loginid og groupid sa group member	
					IF NOT EXISTS( SELECT UserGroupMemberID FROM M_UserGroupMember WHERE UserGroupID = $_UserGroupID AND UserLoginID = $_UserLoginID ) THEN
						SET $_UserGroupMemberID = (SELECT IFNULL(MAX(`UserGroupMemberID`), 0) + 1 FROM M_UserGroupMember);					
						
						INSERT INTO `M_UserGroupMember`(
							 `UserGroupMemberID`
							,`UserGroupID`
							,`UserLoginID`
							,`EffectiveDate`
							,`ExpiryDate`
							,`AddedbyUserID`
						)VALUE(
							 $_UserGroupMemberID
							,$_UserGroupID
							,$_UserLoginID
							,$_EffectiveDate
							,$_ExpiryDate
							,$_TransactBy
						);
						
						IF ROW_COUNT() = 0 THEN								
							signal SQLSTATE '45000' SET message_text = 'Error on usergroupmember detail insert.';	
						END IF;
						
						INSERT INTO `GroupMemberIDs`(`UserGroupMemberID`)VALUE($_UserGroupMemberID);						
																	
					END IF;
					SET @row_count = @row_count + 1;
				END IF;						

				SET @i= @i + 1;						
			END WHILE;					

			IF @row_count = @_DataLength THEN
				IF $_Delimiter = 'SP' THEN 
				      SET $_ErrorNumber := 100;        -- return  para sa SP
				ELSE
				      SET $_ErrorNumber := 106;
				END IF;						
			ELSE
				signal SQLSTATE '45000' SET message_text = 'Error on loop';     							
			END IF;					
		END; -- end of flag 3					
	    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($_UserGroupMemberID 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.UserGroupMemberID,
			  A.UserGroupID,
			  A.UserLoginID,
			  A.EffectiveDate,
			  A.ExpiryDate,
			  A.ReferenceTableStatusID		AS 'TableStatusID',
			  F.ReferenceLongDescription		AS 'TableStatus',
			  B.UserGroupName,
			  C.UserLoginName,
			  C.UserLoginPassword,
			  D.UserMainGroupID,
			  D.UserMainGroupName,
			  UPPER(CONCAT(CONCAT(TRIM(E.`ProfileFirstName`),' ',IFNULL(TRIM(E.`ProfileMiddleName`),'')),CONCAT(' ',TRIM(E.`ProfileLastName`),' ',IFNULL(TRIM(E.`ProfileExtName`),'')))) AS 'ProfileName',
			  E.ProfileFirstName,
			  E.ProfileMiddleName,
			  E.ProfileLastName,
			  E.ProfileExtName,
			  'Success'			AS 'Result',
			  $_View			AS 'View'			  
			FROM M_UserGroupMember			A
			  INNER JOIN M_UserGroup		B ON B.UserGroupID 	= A.UserGroupID
			  INNER JOIN M_UserLogin		C ON C.UserLoginID	= A.UserLoginID
			  INNER JOIN M_UserMainGroup		D ON D.UserMainGroupID	= B.UserMainGroupID		  
			  INNER JOIN M_Profile			E ON E.ProfileID 	= C.ProfileID
			  INNER JOIN M_Reference		F ON F.ReferenceID	= A.ReferenceTableStatusID
			WHERE 
			      A.`UserGroupMemberID` = $_UserGroupMemberID;	        
		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;
	     WHEN 106  THEN -- return for multiple user inserted
	        BEGIN
			SET autocommit := 1;
			
			SELECT 
			  A.UserGroupMemberID,
			  A.UserGroupID,
			  A.UserLoginID,
			  A.EffectiveDate,
			  A.ExpiryDate,
			  DATE_FORMAT( A.EffectiveDate, '%b. %d, %Y') AS 'EffectiveDateText',
			  DATE_FORMAT( A.ExpiryDate, '%b. %d, %Y') AS 'ExpiryDateText',			  
			  A.ReferenceTableStatusID		AS 'TableStatusID',
			  F.ReferenceLongDescription		AS 'TableStatus',
			  B.UserGroupName,
			  C.UserLoginName,
			  C.UserLoginPassword,
			  D.UserMainGroupID,
			  D.UserMainGroupName,
			  UPPER(CONCAT(CONCAT(TRIM(E.`ProfileFirstName`),' ',IFNULL(TRIM(E.`ProfileMiddleName`),'')),CONCAT(' ',TRIM(E.`ProfileLastName`),' ',IFNULL(TRIM(E.`ProfileExtName`),'')))) AS 'ProfileName',
			  E.ProfileFirstName,
			  E.ProfileMiddleName,
			  E.ProfileLastName,
			  E.ProfileExtName,
			  'Success'			AS 'Result',
			  $_View			AS 'View'			  
			FROM M_UserGroupMember			A
			  INNER JOIN M_UserGroup		B ON B.UserGroupID 	= A.UserGroupID
			  INNER JOIN M_UserLogin		C ON C.UserLoginID	= A.UserLoginID
			  INNER JOIN M_UserMainGroup		D ON D.UserMainGroupID	= B.UserMainGroupID		  
			  INNER JOIN M_Profile			E ON E.ProfileID 	= C.ProfileID
			  INNER JOIN M_Reference		F ON F.ReferenceID	= A.ReferenceTableStatusID
			WHERE 
			      A.`UserGroupMemberID` IN(SELECT * FROM GroupMemberIDs);	        
			      
			DROP TEMPORARY TABLE GroupMemberIDs;      
		END;		
	     ELSE
		signal SQLSTATE '45000' SET message_text = 'ErrorNumber is required.';	       
	END CASE;  
	/*CATCH PARA SA MGA RETURNS END*/
END$$

DELIMITER ;