DELIMITER $$

USE `dzi`$$

DROP PROCEDURE IF EXISTS `sp_address`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_address`(
           OUT $ReturnIsSuccess		BIGINT  -- For SP Calling
	  ,$JSON			LONGTEXT
)
BEGIN
	DECLARE $_AddressID			VARCHAR(20)    	DEFAULT NULL;
	DECLARE $_EntityID			VARCHAR(20)    	DEFAULT NULL;
	DECLARE $_AddressContactID		VARCHAR(20)    	DEFAULT NULL;
	DECLARE $_AddressTypeListID		VARCHAR(20)    	DEFAULT NULL;
	DECLARE $_UnitNoBldg	  		VARCHAR(500)    DEFAULT NULL;
	DECLARE $_Street			VARCHAR(1000)   DEFAULT NULL;
	DECLARE $_BarangayID			VARCHAR(20)    	DEFAULT NULL;
	DECLARE $_IsPrimary			CHAR(1)    	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
			/*
				Description     : select
		
				Module Used:
				
				CALL sp_address(
					   @ReturnIsSuccess		
					  ,'{
						 "EntityID": "12"
						,"AddressTypeListID": "1"
						,"UnitNoBldg": ""
						,"Street": ""
						,"BarangayID": "138"
						,"IsPrimary": "Y"
						,"TransactBy": "999999"
						,"Flag": "1"
						
					    }' 	
				);
			*/
			DECLARE $_AgentID  	VARCHAR(20)    DEFAULT NULL;
			
			SET $_View 		:= 'address_1';
			SET $_EntityID 		:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.EntityID")));
			SET $_AddressTypeListID := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.AddressTypeListID")));
			SET $_AddressContactID  := TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.AddressContactID")));
			SET $_UnitNoBldg 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.UnitNoBldg")));
			SET $_Street 		:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.Street")));
			SET $_BarangayID 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.BarangayID")));
			SET $_IsPrimary 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.IsPrimary")));
			
			
			IF $_EntityID 		= 'NULL' OR $_EntityID    	= '' THEN SET $_EntityID    	  := NULL; END IF;
			IF $_AddressTypeListID 	= 'NULL' OR $_AddressTypeListID = '' THEN SET $_AddressTypeListID := NULL; END IF;
			IF $_AddressContactID 	= 'NULL' OR $_AddressContactID  = '' THEN SET $_AddressContactID := NULL; END IF;
			IF $_UnitNoBldg 	= 'NULL' OR $_UnitNoBldg 	= '' THEN SET $_UnitNoBldg 	  := NULL; END IF;
			IF $_Street 		= 'NULL' OR $_Street    	= '' THEN SET $_Street    	  := NULL; END IF;
			IF $_BarangayID 	= 'NULL' OR $_BarangayID      	= '' THEN SET $_BarangayID        := NULL; END IF;
			IF $_IsPrimary 		= 'NULL' OR $_IsPrimary 	= '' THEN SET $_IsPrimary 	  := NULL; END IF;			
			
			IF $_AddressTypeListID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'Address Type List ID is required.';
			ELSEIF $_BarangayID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'Barangay ID is required.';				
			ELSEIF $_IsPrimary IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'IsPrimary is required.';								
			ELSEIF $_TransactBy IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'TransactBy is required.';												
			ELSE 
				SET $_AddressID  := (SELECT IFNULL(MAX(`AddressID`), 0) + 1 FROM `M_Address`);
				
				INSERT INTO `M_Address`(
					 `AddressID`
					,`EntityID`
					,`AddressContactID`
					,`AddressTypeListID`
					,`UnitNoBldg`
					,`Street`
					,`BarangayID`
					,`IsPrimary`
					,`AddedByUserID`
				) VALUES (
					 $_AddressID
					,$_EntityID
					,$_AddressContactID
					,$_AddressTypeListID
					,$_UnitNoBldg
					,$_Street
					,$_BarangayID
					,$_IsPrimary
					,$_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 1
	     WHEN 2 THEN 
		BEGIN
			/*
				Author          : Charls Catipay	
				Table Accessed  : M_Address
				Description     : update M_Address
				DateCreated     : April 9, 2022
				Time 		: 11:17PM
			
				Module Used:
				used in ay76 to update Profile's Address
				
				CALL sp_address(
					   @ReturnIsSuccess		
					  ,'{
						 "AddressID": "1"
						,"Flag":"2"
					    }' 	
				);
				
			*/
			SET $_View 		:= 'address_2';
			SET $_AddressID 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.AddressID")));
			SET $_BarangayID 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.BarangayID")));
			SET $_UnitNoBldg 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.UnitNoBldg")));
			SET $_Street 		:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.Street")));
			
			
			IF $_AddressID 		= 'NULL' OR $_AddressID 	= '' THEN SET $_AddressID	  := NULL; END IF;
			IF $_BarangayID 	= 'NULL' OR $_BarangayID      	= '' THEN SET $_BarangayID        := NULL; END IF;
			IF $_UnitNoBldg 	= 'NULL' OR $_UnitNoBldg 	= '' THEN SET $_UnitNoBldg 	  := NULL; END IF;
			IF $_Street 		= 'NULL' OR $_Street    	= '' THEN SET $_Street    	  := NULL; END IF;			
			
			IF $_AddressID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'Address ID is required.';
			ELSEIF $_BarangayID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'Barangay ID is required.';								
			ELSEIF $_TransactBy IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'TransactBy is required.';												
			ELSE 
				
				UPDATE `M_Address`
				SET
 					 `BarangayID` 			= IFNULL($_BarangayID, BarangayID)
 					,`UnitNoBldg`			= IFNULL($_UnitNoBldg, UnitNoBldg)
 					,`Street`			= IFNULL($_Street, Street)
					,DateUpdated 			= NOW()
					,`UpdatedbyUserID` 	= $_TransactBy
				WHERE
					AddressID = CAST($_AddressID AS INT);	
					
				
				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 address update';     						
				END IF;	
													
			END IF;
		
		END; -- end of flag 2
	     WHEN 3 THEN 
		BEGIN
			/*
				Author          : Arnalen Zoilo	
				Table Accessed  : M_Address
				Description     : Display address lookup
				DateCreated     : May 7, 2023
				Time 		: 4:25 PM
			
				Module Used:

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

			SET @_Search 	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.Search")));
			
			
			IF @_Search	= 'NULL' OR @_Search 	= '' THEN SET @_Search  := NULL; END IF;
			
			SELECT 
					 A.BarangayID
					,A.BarangayName
					,CONCAT(A.BarangayName,' ', B.CityMunicipalityName, ' ',C.ProvinceName, ' ',B.CityMunicipalityZipCode)
							AS 'Address'
					,1 		AS 'IsSuccess'
					,'Success'	AS 'Result'
			FROM M_Barangay A
			INNER JOIN M_CityMunicipality B ON A.CityMunicipalityID = B.CityMunicipalityID
			INNER JOIN M_Province C ON B.ProvinceID = C.ProvinceID
			WHERE CONCAT(A.BarangayName,B.CityMunicipalityName,C.ProvinceName) LIKE CONCAT('%',IFNULL(@_Search,CONCAT(A.BarangayName,B.CityMunicipalityName,C.ProvinceName)),'%')
			ORDER BY A.BarangayName;
			
			IF $_Delimiter = 'SP' THEN 
			      SET $_ErrorNumber := 104;        -- return  para sa SP
			ELSE
			      SET $_ErrorNumber := 105;
			END IF;	

		END; -- end of flag 3
	END CASE;  
	/*CATCH PARA SA MGA RETURNS START*/
	CASE $_ErrorNumber
	     WHEN 100   THEN -- sp return success
		SET autocommit 	       := 0;
		SET $ReturnIsSuccess   := CAST($_AddressID 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 
				 `AddressID`
				,`EntityID`
				,`AddressContactID`
				,`AddressTypeListID`
				,`UnitNoBldg`
				,`Street`
				,`BarangayID`
				,`IsPrimary`
				,`ReferenceTableStatusID`
				,1 				AS 'IsSuccess'
				,$_View 			AS 'View'
			FROM `M_Address`
			WHERE
				 `AddressID` = CAST($_AddressID 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 ;