DELIMITER $$

USE `rsw_binary`$$

DROP PROCEDURE IF EXISTS `sp_autopayment`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_autopayment`(
           OUT $ReturnIsSuccess		BIGINT  -- For SP Calling
	  ,$JSON			LONGTEXT
)
BEGIN
DECLARE $_AccountID  		  	VARCHAR(10);
DECLARE $_TransactBy  		  	VARCHAR(10);
DECLARE $_ReferenceTableStatusID  	BIGINT(20);
DECLARE $_Flag 	 	  	  	VARCHAR(50);
DECLARE $_Delimeter 	  	  	VARCHAR(50) DEFAULT NULL;
DECLARE $_DelimiterData		  	LONGTEXT;
DECLARE $_View 	 	  	  	VARCHAR(50);
DECLARE $_ErrorMsg 	 	  	VARCHAR(1000);
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 $_Delimeter  		:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.Delimeter")));	
	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 $_Delimeter	  	    	= 'NULL' OR $_Delimeter   	      = '' THEN SET $_Delimeter  	     = 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          : Arnalen Zoilo
				Table Accessed  : 
				Description     : This is use payment using paymongo (
				DateCreated     : December 25, 2020
				Time 		: 5:56 PM
			
				Module Used:
				
				CALL sp_autopayment(
						   @ReturnIsSuccess		
						  ,'{
							 "AccountID": "1124"
							,"SponsorID": "2"
							,"PackageID": "12"
							,"PaymentRef": "dcvds-dse-e34"
							,"Amount": "2000"
							,"PaymentMethod": "GCASH"
							,"Delimeter": "NULL"
							,"TransactBy": "1"
							,"Flag": "1"
						    }' 	
				);
				
				
			*/
			SET $_View = 'autopayment_1';
			
			SET $_AccountID	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.AccountID")));
			SET @_SponsorID	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.SponsorID")));
			SET @_PackageID	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.PackageID")));
			SET @_Amount	:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.Amount")));	
			SET @_PaymentRef:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.PaymentRef")));
			SET @_PaymentMethod:= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.PaymentMethod")));			
			SET @_NetworkID= TRIM(JSON_UNQUOTE(JSON_EXTRACT($JSON,"$.NetworkID")));		
	
			IF $_AccountID	= 'NULL' OR $_AccountID = '' THEN SET $_AccountID := NULL; END IF;
			IF @_SponsorID	= 'NULL' OR @_SponsorID = '' THEN SET @_SponsorID := NULL; END IF;
			IF @_Amount	= 'NULL' OR @_Amount 	= '' THEN SET @_Amount	:= NULL; END IF;
			IF @_PaymentRef	= 'NULL' OR @_PaymentRef = '' THEN SET @_PaymentRef	:= NULL; END IF;
			IF @_PaymentMethod	= 'NULL' OR @_PaymentMethod = '' THEN SET @_PaymentMethod	:= NULL; END IF;
			IF @_NetworkID	= 'NULL' OR @_NetworkID = '' THEN SET @_NetworkID	:= NULL; END IF;
			
			IF $_AccountID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'AccountID is required.';
			ELSEIF @_SponsorID IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'IsMaintenance is required.';
			ELSEIF @_Amount IS NULL THEN 
				signal SQLSTATE '45000' SET message_text = 'Amount is required.';
			ELSEIF @_PackageID IS NULL THEN 
				signal SQLSTATE '45000' SET message_text = 'PackageID is required.';
			ELSEIF @_NetworkID IS NULL THEN 
				signal SQLSTATE '45000' SET message_text = 'NetworkID is required.';
			ELSEIF $_TransactBy IS NULL THEN
				signal SQLSTATE '45000' SET message_text = 'TransactBy is required.';	
			ELSE 
				SELECT `PackageDesc` 
					,`PackagePointsValue`
				INTO 	@_PackageDesc
					,@_PointValue
				FROM `M_Packages` 
				WHERE `PackageID` = @_PackageID;
				
				SET @_MemberID = (SELECT MemberID FROM MT_Account WHERE AccountID = $_AccountID);
				
				SELECT   D.BranchName
					,D.BranchID 
				INTO  @_BranchName
				     ,@_BranchID
				FROM `MT_Member` A
				JOIN `vw_entity`  C ON C.ProfileID = A.DZIProfileID
				JOIN `vw_branch`  D ON D.BranchID  = C.BranchID
				WHERE
				     A.MemberID = @_MemberID;
									     
				-- Payment insert
				SET @_PaymentID := (SELECT IFNULL(MAX(`PaymentID`), 0) + 1 FROM `T_Payment`);
				
				SET @_PaymentRef = CONCAT('PM-',@_PaymentMethod,' Ref# ',@_PaymentRef);
				
				INSERT INTO `T_Payment`(
					 `PaymentID`
					,`PackageID` 
					,`PaymentAmount`
					,`PaymentReference`
					,`PackageDesc`
					,`PackagePointsValue`
					,`ReferencePaymentStatusID`
					,`AccountID`
					,`AddedByUserID`
				)VALUES(
					 @_PaymentID
					,@_PackageID
					,@_Amount
					,@_PaymentRef
					,@_PackageDesc
					,@_PointValue
					,7
					,$_AccountID
					,$_TransactBy
				);
		
				IF ROW_COUNT() > 0 THEN
					SET @_DelimiterData := CONCAT('{
							 "PackageID": "',@_PackageID,'"
							,"BranchName": "',@_BranchName,'"
							,"TransactBy": "',$_TransactBy,'"
							,"Delimiter": "SP"
							,"Flag": "1"
					    }'); 
					CALL sp_code(
						   @CodeIsSuccess		
						  ,@_DelimiterData 	
					);
					IF @CodeIsSuccess > 0 THEN
						UPDATE `MT_Account`
						SET
							`CodeID` 		  = @CodeIsSuccess
							-- ,`ReferenceMemberTypeID`  = IFNULL($ReferenceMemberTypeID, ReferenceMemberTypeID)
							,`DateUpdated` 		  = NOW()
							,`UpdatedByUserID` 	  = $_TransactBy
						WHERE AccountID = $_AccountID;
						IF ROW_COUNT() > 0 THEN SET @AccountIsSuccess = $_AccountID; ELSE SET @AccountIsSuccess = 0; END IF;					
						-- Insert Dinhi ang Referral amount sa nag sponsor
						SELECT 
							 AccountNo 
							,ReferenceMemberTypeID
						INTO
							 @_AccountNo
							,@_MemberTypeID
						FROM MT_Account 
						WHERE AccountID =$_AccountID;
						SET @_DirectReferralAmount = (
								SELECT CASE WHEN IFNULL(`PackageReferralAmount`,0) > 0
										THEN `PackageReferralAmount`
									    ELSE (`PackageAmount` * (`PackagesReferralInPercent`/100))
									END
								FROM `M_Packages` WHERE `PackageID` = 	@_PackageID
						);
  					   
						SET @_MemberName = `fn_rsw_binary_membername`($_AccountID);
						
						IF @_DirectReferralAmount > 0 THEN 
							SET @_DelimiterData := CONCAT('{
								 "AccountID": "',@_SponsorID,'"
								,"SourceAccountID": "',$_AccountID,'"
								,"Amount": "',@_DirectReferralAmount,'"
								,"TransactBy": "',$_TransactBy,'"
								,"Delimeter": "SP"
								,"Flag": "2"
							}'); 
							CALL sp_referral(
								 @ReferralIsSuccess		
								,@_DelimiterData 	
							);
							IF @ReferralIsSuccess > 0 THEN
								SET @_IsTravelFund = (SELECT ReferralIsTravelFund FROM T_Referral WHERE ReferralID = @ReferralIsSuccess);
								IF @_IsTravelFund = 'Y' THEN 
									SET @_SourceName = `fn_rsw_binary_account`($_AccountID,NULL,2);
									SET @_Desc = CONCAT('Referral From ', @_SourceName);
									SET @_DelimiterData := CONCAT('{
										 "AccountID": "',@_SponsorID,'"
										,"Description": "',@_Desc,'"
										,"Amount": "',@_DirectReferralAmount,'"
										,"TransactionTypeID": "19"
										,"ReferralID": "',@ReferralIsSuccess,'"
										,"TransactBy": "',$_TransactBy,'"
										,"Delimeter": "SP"
										,"Flag": "2"
									}'); 
									CALL sp_travelfund(
										   @TravelFundIsSuccess		
										  ,@_DelimiterData 	
									);	
								END IF;	
								SET @_Reference = (CONCAT('Referral # ',@ReferralIsSuccess));
								SET @_Remarks = (CONCAT('Direct Referral from ',@_MemberName,' Acct#:',@_AccountNo));
								
								IF @_IsTravelFund = 'N' THEN 
									SET @_DelimiterData := CONCAT('{
										 "AccountIDFrom": "',$_AccountID,'"
										,"AccountIDTo": "',@_SponsorID,'"
										,"CashInAmount": "',@_DirectReferralAmount,'"
										,"CashInReference": "',@_Reference,'"
										,"CashInRemarks":"',@_Remarks,'"
										,"CashInDateReceived":""
										,"PaymentGatewayID":""
										,"ReferenceTransactionTypeID":"19"
										,"ReferenceCashInReasonCodeID":"42"
										,"ReferenceTransactionStatusID":"15"
										,"ReferenceFundTypeID":"39"
										,"Delimeter":"SP"
										,"TransactBy": "2" 
										,"Flag":"1"
									}'); 
									CALL sp_cashin(
										   @CashInIsSuccess		
										  ,@_DelimiterData 	
									);
									IF  @CashInIsSuccess > 0 THEN
										IF @_IsTravelFund = 'N' THEN
											SET @_DelimiterData := CONCAT('{
														 "ReferralID": "',@ReferralIsSuccess,'"
														,"CashInID": "',@CashInIsSuccess,'"
														,"TransactBy": "',$_TransactBy,'"
														,"Delimeter": "SP"
														,"Flag": "3"
											    }'); 
											CALL sp_referral(
												   @Referral2IsSuccess		
												  ,@_DelimiterData 	
											);
											-- select @Referral2IsSuccess '@Referral2IsSuccess';
											SET @_UpdateReferral = @Referral2IsSuccess;
											IF @_UpdateReferral	 > 0 THEN
												SET @CashInIsSuccess = 1; 			
											ELSE
												signal SQLSTATE '45000' SET message_text = 'error on referral update';
											END IF; -- IF @Referral2IsSuccess	 > 0 THEN
										ELSE 
											SET @CashInIsSuccess = 1;
											-- SET @Referral2IsSuccess = 1;
										END IF; -- IF @_IsTravelFund = 'N' THEN
									ELSE
										signal SQLSTATE '45000' SET message_text = 'error on cashin referral insert';
									END IF; -- IF  @CashInIsSuccess > 0 THEN
								ELSE
									SET @CashInIsSuccess = 1; 
								END IF; -- IF @_IsTravelFund = 'N' THEN
							ELSE 	
								signal SQLSTATE '45000' SET message_text = 'error on referral insert';
							END IF; -- IF @ReferralIsSuccess > 0 THEN
						ELSE
							SET @CashInIsSuccess = 1;
						END IF; -- IF @_DirectReferralAmount > 0 THEN 
						
						 
						IF @CashInIsSuccess != 0 THEN
							-- SET @_MemberID  = `fn_rsw_binary_account`($_AccountID,NULL,10);
							SET @_PaymentStatusID = (`fn_rsw_binary_reference`(NULL,'PSPD',1));
							
							IF @ReferralIsSuccess IS NULL THEN SET @ReferralIsSuccess = 'NULL'; END IF; 
							
							SET @_DelimiterData := CONCAT('{
									 "DZIBranchID": "',@_BranchID,'"
									,"MemberID": "',@_MemberID,'"
									,"AccountID": "',$_AccountID,'"
									,"PackageID": "',@_PackageID,'"
									,"ReferralID": "',@ReferralIsSuccess,'"
									,"PaymentStatusID": "',@_PaymentStatusID,'"
									,"TransactBy": "',$_TransactBy,'"
									,"Delimiter": "SP"
									,"Flag": "7"
								    }'); 
							CALL sp_membership(
								   @MembershipIsSuccess		
								  ,@_DelimiterData 	
							);
							IF @MembershipIsSuccess != 0 THEN
								SET @_group_code = 'TMCCM';
																			
								CALL dzi.sp_usergroupmember_iu_v2(
									   @UserGroupMemberIsSuccess	
									  ,NULL -- $UserGroupMemberID		
									  ,dzi.fn_dzi_usergroup(NULL,@_group_code,3) -- $UserGroupID  Binary Member TAKENOTE. DILI PAREHA OG ID SA LIVE		
									  ,fn_dzi_userlogin(@_MemberID,NULL,1) -- $UserLoginID			
									  ,NOW() -- $EffectiveDate 		
									  ,NULL -- $ExpiryDate 			 
									  ,NULL -- $TableStatusID		
									  ,$_TransactBy -- $TransactBy	        	
									  ,'SP' -- $Delimiters	        	
									  ,NULL -- $SearchValue			
									  ,1    -- $Flag   			   
								);													    
								-- End insert Membership Package
								-- CycleBounos Share Insert
								SET @_CycleAmount = (SELECT CASE WHEN `PackageDetailAmount` IS NOT NULL
												THEN `PackageDetailAmount`
												ELSE @_PackageAmount * (`PackageDetailPercent`/100)
											    END
											FROM `M_PackagesDetail` 
											WHERE `PackageID` = @_PackageID 
											AND `ReferenceTableStatusID` = 1
											AND `ChargesID` = (SELECT `ChargesID` FROM `M_Charges` WHERE  `ChargesName` = 'Cycle Bonus' AND `ReferenceTableStatusID` = 1 LIMIT 1)
										     );
										     
								SET @_DelimiterData := CONCAT('{
											 "AccountID": "',$_AccountID,'"
											,"Amount": "',@_CycleAmount,'"
											,"TransactionTypeID": "19"
											,"Desc": "Membership"
											,"TransactBy": "',$_TransactBy,'"
											,"Delimiter": "SP"
											,"Flag": "1"
								    }'); 
								CALL sp_cyclebonus(
									   @CycleBonusIsSuccess		
									  ,@_DelimiterData 	
								);
								IF @CycleBonusIsSuccess	 > 0 THEN
									-- Update `T_MembershipDetail` Cyclebonus
									UPDATE `T_MembershipDetail`
									SET `TableLookUpID` = @CycleBonusIsSuccess
									    ,`TableLookUpDescription` = 'T_CycleBonus'
									WHERE `MembershipID` = @MembershipIsSuccess	
									AND `ChargesID` = 19;
			
									-- insert to T_Group																
									SET @_DelimiterData := CONCAT('{
												 "AccountID": "',$_AccountID,'"
												,"SponsorID": "',@_SponsorID,'"
												,"TransactBy": "',$_TransactBy,'"
												,"Delimiter": "SP"
												,"Flag": "2"
									    }'); 
									CALL sp_group(
										   @GroupIsSuccess		
										  ,@_DelimiterData 	
									);
									IF @GroupIsSuccess > 0 THEN 
										-- Check if kinsay mo graduate para mahatagan ug bonus 
										SET @_UplineID = (SELECT `GroupUplineAccountID` FROM T_Group WHERE AccountID = $_AccountID);
										SET @_MatrixLoop = (SELECT `SystemSetupValue` FROM `M_SystemSetup` WHERE `SystemSetupItem` = 'MatrixLoop' AND `ReferenceTableStatusID` = 1 AND `SystemSetupEffDate` <= NOW());
										SET @_MatrixLevel = (SELECT `SystemSetupValue` FROM `M_SystemSetup` WHERE `SystemSetupItem` = 'MatrixLevel' AND `ReferenceTableStatusID` = 1 AND `SystemSetupEffDate` <= NOW());
										SET @_MatrixCompleteCount = (SELECT `SystemSetupValue` FROM `M_SystemSetup` WHERE `SystemSetupItem` = 'MatrixCompleteCount' AND `ReferenceTableStatusID` = 1 AND `SystemSetupEffDate` <= NOW());
										-- Get the graduateID 
										SET @_Loop = 1;
										
										WHILE @_MatrixLoop >= @_Loop AND  IFNULL(@_UplineID,0)  > 0 DO
											SET @_UplineID = (SELECT `GroupUplineAccountID` FROM T_Group WHERE AccountID = @_UplineID);
											SET @_IsGraduate = (SELECT `IsGraduate` FROM `MT_Account` WHERE `AccountID` = @_UplineID);
																												
											IF @_IsGraduate = 'Y'  THEN
													SET @_UplineID = (SELECT `GroupUplineAccountID` FROM T_Group WHERE AccountID = @_UplineID);
											ELSEIF @_IsGraduate = 'N'  AND  IFNULL(@_UplineID,0)  > 0  THEN
													SET @_DelimiterData := CONCAT('{
																"UplineID": "',@_UplineID,'"
																,"TeamCount": "',@_MatrixCompleteCount ,'"
																,"level": "',@_MatrixLevel ,'"
																,"Delimiter": "SP"
																,"Flag": "4"
													}');  
													CALL sp_groupcount(
																@GroupIsSuccess		
																,@_DelimiterData 	
													);
													
													IF @_MatrixCompleteCount  =  @GroupIsSuccess THEN
														SET @_AccountGraduate = @_UplineID;
														SET @_Loop  = @_Loop  + @_MatrixLoop;
													ELSE
														SET @_AccountGraduate = 0;
													END IF;
											ELSE
													SET @_Loop  = @_Loop  + @_MatrixLoop;		
													SET @_AccountGraduate = 0;											
											END IF;
											SET @_Loop = @_Loop + 1;
																									
										END WHILE;
										-- IF @_AccountGraduate  > 0 then hatagan ug bonus
										IF IFNULL(@_AccountGraduate,0) = 0 THEN
																
											SET @_success = 1;
										ELSE
											SET @_GraduateMemberID = `fn_rsw_binary_account`(@_AccountGraduate,NULL,10);
											SET @_GraduateSponsorID = (SELECT `AccountSponsorID` FROM T_Network WHERE AccountID = @_AccountGraduate );
											
											-- Update ang MT_Account nga graduate na
											UPDATE MT_Account 
											SET `IsGraduate` = 'Y'
											,`DateUpdated` = NOW()
											,`UpdatedByUserID` = $_TransactBy
											WHERE `AccountID` = @_AccountGraduate;
											
											IF ROW_COUNT() > 0 THEN
												SET @_CycleMatrixBonus = (SELECT `SystemSetupValue` FROM `M_SystemSetup` WHERE `SystemSetupItem` = 'CycleMatrixBonus' AND `SystemSetupEffDate` <= NOW());
												SET @_GraduateMemberID = (SELECT MemberID FROM MT_Account WHERE AccountID = @_AccountGraduate);
												SET @_GraduateDefaultID = (SELECT AccountID FROM MT_Account WHERE MemberID = @_GraduateMemberID AND `IsDefault` = 'Y' AND `ReferenceTableStatusID` = 1);
												
												SET @_DelimiterData := CONCAT('{
															 "AccountID": "',@_GraduateDefaultID,'"
															,"Amount": "',@_CycleMatrixBonus,'"
															,"TransactionTypeID": "20"
															,"Desc": "Complete Cycle Bonus"
															,"IsCompleteCycle": "Y"
															,"TransactBy": "',$_TransactBy,'"
															,"Delimiter": "SP"
															,"Flag": "1"
												    }'); 
									
												CALL sp_cyclebonus(
													   @CompleteIsSuccess		
													  ,@_DelimiterData 	
												);
												IF @CompleteIsSuccess > 0 THEN
													SET @_IsTravelFund = (SELECT `CycleBonusIsTravelFund` FROM `T_CycleBonus` WHERE `CycleBonusID` = @CompleteIsSuccess);
													IF @_IsTravelFund = 'Y' THEN 
														SET @_SourceName = `fn_rsw_binary_account`($_AccountID,NULL,2);
														SET @_CycleDesc = CONCAT('Account that complete the cycle is from ', @_SourceName);
														
														SET @_DelimiterData := CONCAT('{
															 "AccountID": "',@_GraduateDefaultID,'"
															,"Description": "',@_CycleDesc,'"
															,"Amount": "',@_CycleMatrixBonus,'"
															,"TransactionTypeID": "19"
															,"CycleBonusID": "',@CompleteIsSuccess,'"
															,"TransactBy": "',$_TransactBy,'"
															,"Delimeter": "SP"
															,"Flag": "2"
														}'); 
														CALL sp_travelfund(
															   @TravelFundIsSuccess		
															  ,@_DelimiterData 	
														);	
														IF @TravelFundIsSuccess > 0 THEN
															SET @_CycleIsPass = 1;
														ELSE
															signal SQLSTATE '45000' SET message_text = 'error on travelfund insert of cycle bonus';
														END IF;
													END IF;
													IF @_IsTravelFund = 'N' THEN
														SET @_CycleRef = CONCAT('CycleBonus No:',@CompleteIsSuccess);
																	
														SET @_DelimiterData := CONCAT('{
																		 "AccountIDFrom": "1"
																		,"AccountIDTo": "',@_GraduateDefaultID,'"
																		,"CashInAmount": "',@_CycleMatrixBonus,'"
																		,"CashInReference": "',@_CycleRef,'"
																		,"CashInRemarks":"Complete Cycle Bonus"
																		,"ReferenceTransactionTypeID":"19"
																		,"ReferenceCashInReasonCodeID":"203"
																		,"ReferenceTransactionStatusID":"15"
																		,"ReferenceFundTypeID":"39"
																		,"Delimeter":"SP"
																		,"TransactBy": "2" 
																		,"Flag":"1"
														}'); 
														CALL sp_cashin(
															 @CashInCycleIsSuccess		
															,@_DelimiterData 	
														);
														IF @CashInCycleIsSuccess > 0 THEN
															-- Update T_CycleBonus
															UPDATE `T_CycleBonus` 
															SET `CashInID` = @CashInCycleIsSuccess	
																,`DateUpdated` = NOW()
																,`UpdatedByUserID` = $_TransactBy
															WHERE `CycleBonusID` = @CompleteIsSuccess;
															
															IF ROW_COUNT() > 0 THEN
																SET @_CycleIsPass = 1;
															ELSE
																signal SQLSTATE '45000' SET message_text = 'error on cycle bonus update';
															END IF;
														ELSE
															signal SQLSTATE '45000' SET message_text = 'error on cashin insert of cycle bonus';
														END IF;
													ELSE
														SET @_CycleIsPass = 1;
													END IF;
							
													IF @_CycleIsPass = 1 THEN
																															
														SET @_PackageDesc = (SELECT `PackageDesc` FROM `M_Packages` WHERE `PackageID` = @_PackageID);
															     
														-- re entry 
														SET @_DelimiterData := CONCAT('{
																 "SponsorID": "',$_GraduateSponsorID,'"
																,"MemberID": "',$_GraduateMemberID,'"
																,"BranchName": "',$_BranchName,'"
																,"BranchID": "',@_BranchID,'"
																,"PackageID":"',@_PackageID,'"
																,"PackageAmount":"',@_PackageAmount,'"
																,"PackageDesc":"',@_PackageDesc,'"
																,"DirectReferral":"',@_DirectReferralAmount,'"
																,"Delimiter":"SP"
																,"TransactBy": "1" 
																,"Flag":"1"
														}'); 
														CALL sp_reentry(
															   @ReEntryIsSuccess		
															  ,@_DelimiterData 	
														);
														IF @ReEntryIsSuccess != 0 THEN
															-- check if naay nag matrix
															-- if naay nag graduate add din tawag sa re entry
															SET @_success = 1;
															-- select @ReEntryIsSuccess '@ReEntryIsSuccess';
														ELSE
															signal SQLSTATE '45000' SET message_text = 'error on reentry';
														END IF;
													ELSE
														signal SQLSTATE '45000' SET message_text = 'error on cycle bonus';
													END IF;
												ELSE
													signal SQLSTATE '45000' SET message_text = 'error on cycle bonus deduct';
												END IF; -- IF @CompleteIsSuccess > 0 THEN
											ELSE
												signal SQLSTATE '45000' SET message_text = 'error on account update graduate';
											END IF; -- IF ROW_COUNT() > 0 THEN
											
										END IF; -- IF IFNULL(@_AccountGraduate,0) = 0 THEN
										IF @_success = 1 THEN
											IF $_Delimeter = 'SP' THEN -- kung gitawag gekan sa SP
												SET $_ErrorMsg    := 'Success on enrollment';
												SET $_ErrorNumber := 100;
											ELSE
												SET $_ErrorMsg    := 'Success on enrollment';
												SET $_ErrorNumber := 103;			    				    
											END IF;
										ELSE
											signal SQLSTATE '45000' SET message_text = 'error on group insert';
										END IF;
										
									ELSE
										signal SQLSTATE '45000' SET message_text = 'error on group insert';
									END IF; -- IF @GroupIsSuccess	 > 0 THEN
								ELSE
									signal SQLSTATE '45000' SET message_text = 'error on cyclebonus insert';
								END IF; -- @CycleBonusIsSuccess	
							ELSE
								signal SQLSTATE '45000' SET message_text = 'error on membership insert';
							END IF; -- IF @MembershipIsSuccess != 0 THEN
						ELSE
							signal SQLSTATE '45000' SET message_text = 'error on referral insert';
						END IF; -- IF @CashInIsSuccess != 0 THEN
					ELSE
						signal SQLSTATE '45000' SET message_text = 'Error on generate code';
					END IF; -- IF @CodeIsSuccess > 0 THEN
				ELSE
					signal SQLSTATE '45000' SET message_text = 'Error on payment';    	
				END IF; -- PAYMENT INSERT
				
				/*IF ROW_COUNT() > 0 THEN
					SET $_ErrorMsg    := 'Success on update.';
					IF $_Delimeter = '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 sa $Flag 1
		
	    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   := $_AccountID;
	     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
			      `PaymentID` 		AS 'PaymentID'
			     ,`PaymentAmount`		AS 'Amount'
			     ,`PaymentReference`	AS 'Reference'
			     ,`PackageDesc` 		AS 'Package'
			     ,`PackagePointsValue` 	AS 'PointsValue'
			     ,`PaymentGatewayID`	AS 'GatewayID'
			     ,`AccountID` 		AS 'AccountID'
			     ,`ReferencePaymentStatusID`AS 'PaymentStatusID'
			     ,`CashInID`		AS 'CashInID'	
			     ,1       			AS 'IsSuccess'
			     ,$_ErrorMsg    		AS 'Result'
			     ,$_View   			AS 'View'
			FROM `T_Payment`
			WHERE
			     `PaymentID` = @_PaymentID;      
		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
	        BEGIN
			SET autocommit := 1;
			
			SELECT 
 				 $_ErrorMsg								AS 'ErrorMsg'		
				,'1' 									AS 'IsSuccess'
				,'Success' 								AS 'Result'
 				,$_View 								AS 'View';	
	        
		END; -- end sa $_ErrorNumber 106						
	     ELSE
		signal SQLSTATE '45000' SET message_text = 'ErrorNumber is required.';	         
	END CASE;  
	/*CATCH PARA SA MGA RETURNS END*/
		
 
END$$

DELIMITER ;