USE `dbxdb`;
DROP procedure IF EXISTS `contract_features_create_proc`;



USE `dbxdb`;
DROP procedure IF EXISTS `dbxdb`.`contract_features_create_proc`;
;



DELIMITER $$
USE `dbxdb`$$
CREATE PROCEDURE `contract_features_create_proc`(
IN _features MEDIUMTEXT CHARACTER SET UTF8 COLLATE utf8_general_ci,
IN _contractId VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci,
IN _customerId VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci,
IN _serviceTypeId VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci,
IN _defaultActionsEnabled VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci
)
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE featureId varchar(255) DEFAULT "";
DECLARE featuresList TEXT DEFAULT "";
DECLARE featureActionId varchar(255) DEFAULT "" ;
DECLARE entryStatus INTEGER DEFAULT 0 ;
DECLARE limitId varchar(255) DEFAULT "";
DECLARE tempLimitValue varchar(255) DEFAULT "";



DECLARE features CURSOR
FOR (select id from feature where FIND_IN_SET(id ,@features_list ));
DECLARE actions CURSOR
FOR (select id from featureaction where FIND_IN_SET(featureaction.id ,@validServicedefinitionActions ));
DECLARE limits CURSOR
FOR (select LimitType_id from actionlimit where actionlimit.Action_id = featureActionId );
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;

SET SESSION group_concat_max_len = 100000000;

SET @features_list = (select group_concat(feature.id SEPARATOR ",") from feature JOIN featureroletype ON (featureroletype.Feature_id =feature.id and featureroletype.RoleType_id = _serviceTypeId)
where (FIND_IN_SET(feature.id ,_features )) AND feature.Status_id = 'SID_FEATURE_ACTIVE') ;
SET @features_list = IF(@features_list is null, '', @features_list);



OPEN features;
getFeature: LOOP
FETCH features INTO featureId;
IF finished = 1 THEN
LEAVE getFeature;
else
SET @id = (SELECT LEFT(UUID(), 50));
INSERT INTO contractfeatures(id,contractId,coreCustomerId,featureId) VALUES
(@id,_contractId,_customerId,featureId);
set featuresList = CONCAT(featureId,",",featuresList);
ITERATE getFeature;
END IF;
END LOOP getFeature;
CLOSE features;

SET featuresList = (select SUBSTRING(featuresList FROM 1 FOR (CHAR_LENGTH(featuresList)-1)));
select featuresList;



SET finished = 0;



SET @validFIActions = (SELECT group_concat(distinct featureaction.id SEPARATOR ",") FROM featureaction WHERE
FIND_IN_SET(featureaction.Feature_id ,featuresList ) AND
featureaction.status = 'SID_ACTION_ACTIVE');



SET @servicedefinitionId = (SELECT servicedefinitionId from contract WHERE id = _contractId);




SET @validServicedefinitionActions = (SELECT group_concat(distinct servicedefinitionactionlimit.actionId SEPARATOR ",") FROM servicedefinitionactionlimit WHERE
servicedefinitionactionlimit.serviceDefinitionId = @servicedefinitionId AND
FIND_IN_SET(servicedefinitionactionlimit.actionId ,@validFIActions ));

If !ISNULL(_defaultActionsEnabled) AND _defaultActionsEnabled = 'true' THEN




OPEN actions;
getAction: LOOP
SET entryStatus = 0;
FETCH actions INTO featureActionId;
SET @featureId = (SELECT Feature_id FROM featureaction WHERE id = featureActionId );
IF finished = 1 THEN
LEAVE getAction;
else
OPEN limits;
getlimit: LOOP
FETCH limits INTO limitId;
IF finished = 1 THEN
LEAVE getlimit;
else

SET @limitvalue = (SELECT value FROM actionlimit WHERE Action_id = featureActionId
AND LimitType_id = limitId );
SET @limitATServiceDefinition = (SELECT servicedefinitionactionlimit.value FROM servicedefinitionactionlimit WHERE
servicedefinitionactionlimit.actionId = featureActionId AND
servicedefinitionactionlimit.limitTypeId = limitId AND
servicedefinitionactionlimit.serviceDefinitionId = @servicedefinitionId );
SET tempLimitValue = LEAST(@limitvalue,@limitATServiceDefinition);

if(!isnull(tempLimitValue) AND tempLimitValue !='') THEN
SET @limitvalue = tempLimitValue;
END IF;

SET @id = (SELECT LEFT(UUID(), 50));
INSERT IGNORE INTO contractactionlimit(id,contractId,coreCustomerId,contractactionlimit.featureId,actionId,limitTypeId,value) VALUES
(@id,_contractId,_customerId,@featureId,featureActionId,limitId,@limitvalue);

SET entryStatus = 1;
ITERATE getlimit;
END IF;
END LOOP getlimit;
CLOSE limits;

SET finished = 0;
IF entryStatus = 0 THEN
SET @id = (SELECT LEFT(UUID(), 50));
INSERT IGNORE INTO contractactionlimit(id,contractId,coreCustomerId,contractactionlimit.featureId,actionId) VALUES
(@id,_contractId,_customerId,@featureId,featureActionId);
END IF;
ITERATE getAction;
END IF;
END LOOP getAction;
CLOSE actions;



END IF;



END$$


DELIMITER ;
;

    


USE `dbxdb`;
DROP procedure IF EXISTS `useractions_create_proc`;



USE `dbxdb`;
DROP procedure IF EXISTS `dbxdb`.`useractions_create_proc`;
;



DELIMITER $$
USE `dbxdb`$$
CREATE PROCEDURE `useractions_create_proc`(
IN _userId VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci,
IN _accountsCSV TEXT CHARACTER SET UTF8 COLLATE utf8_general_ci,
IN _coreCustomerId VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci,
IN _contractId VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci,
IN _groupId VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci
)
BEGIN



DECLARE finished INTEGER DEFAULT 0 ;
DECLARE featureActionId varchar(255) DEFAULT "" ;
DECLARE actionslist TEXT DEFAULT "" ;
DECLARE limitId varchar(255) DEFAULT "" ;
DECLARE entryStatus INTEGER DEFAULT 0 ;
DECLARE accountId varchar(255) DEFAULT "" ;
DECLARE actualLimitId varchar(255) DEFAULT "" ;



DECLARE accounts CURSOR
FOR (SELECT customeraccounts.Account_id FROM customeraccounts WHERE contractId = _contractId AND coreCustomerId = _coreCustomerId
AND Customer_id = _userId AND FIND_IN_SET(Account_id,_accountsCSV));
DECLARE actions CURSOR
FOR (select id from featureaction where FIND_IN_SET(id,@validActionsList) AND (featureaction.isAccountLevel = '1' OR featureaction.isAccountLevel = true ));
DECLARE nonaccountlevelactions CURSOR
FOR (select id from featureaction where FIND_IN_SET(id,@validActionsList) AND (featureaction.isAccountLevel = '0' OR featureaction.isAccountLevel = false ));
DECLARE limits CURSOR
FOR (select LimitType_id from actionlimit where Action_id = featureActionId );
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;




SET SESSION group_concat_max_len = 100000000;




IF(ISNULL(_accountsCSV) OR _accountsCSV='' ) THEN
SET _accountsCSV = (SELECT group_concat(distinct customeraccounts.Account_id SEPARATOR ",") FROM customeraccounts WHERE customeraccounts.Customer_id = _userId
AND customeraccounts.contractId = _contractId AND customeraccounts.coreCustomerId = _coreCustomerId );
END IF;

SET @serviceDefinitionId = (SELECT servicedefinitionId from contract WHERE id = _contractId);
SET @serviceType = (SELECT serviceType from servicedefinition WHERE id = @serviceDefinitionId);
IF(ISNULL(_groupId) OR _groupId='' ) THEN
SET _groupId = (SELECT Group_id FROM groupservicedefinition WHERE serviceDefinitionId = @serviceDefinitionId
AND (isDefaultGroup = true OR isDefaultGroup = '1'));
END IF;

SET @validFIActions = (SELECT group_concat(distinct id SEPARATOR ",") FROM featureaction);



SET @validServiceDefinitionActions = (SELECT group_concat(distinct actionId SEPARATOR ",") FROM servicedefinitionactionlimit
WHERE serviceDefinitionId= @serviceDefinitionId AND FIND_IN_SET(actionId,@validFIActions));




SET _groupId = (SELECT Group_id FROM groupservicedefinition WHERE serviceDefinitionId = @serviceDefinitionId
AND Group_id = _groupId );



SET @validGroupActions = (SELECT group_concat(distinct Action_id SEPARATOR ",") FROM groupactionlimit WHERE Group_id = _groupId
AND FIND_IN_SET(Action_id,@validServiceDefinitionActions));

SET @validActionsList = (SELECT group_concat(distinct actionId SEPARATOR ",") FROM contractactionlimit WHERE contractId = _contractId
AND coreCustomerId = _coreCustomerId AND FIND_IN_SET(actionId,@validGroupActions));





OPEN accounts;
getAccount : LOOP
FETCH accounts INTO accountId;
IF finished = 1 THEN
LEAVE getAccount;
ELSE
OPEN actions;
getAction: LOOP
SET entryStatus = 0;
FETCH actions INTO featureActionId;
SET @featureId = (SELECT Feature_id FROM featureaction WHERE id = featureActionId );
IF finished = 1 THEN
LEAVE getAction;
else
OPEN limits;
getlimit: LOOP
FETCH limits INTO limitId;
IF finished = 1 THEN
LEAVE getlimit;
else
SET @limitvalue = (SELECT value FROM contractactionlimit WHERE actionId = featureActionId
AND limitTypeId = limitId AND contractId = _contractId AND coreCustomerId = _coreCustomerId);

if (limitId='MAX_TRANSACTION_LIMIT') THEN
SET actualLimitId = 'AUTO_DENIED_TRANSACTION_LIMIT';
ELSEIF (limitId='MIN_TRANSACTION_LIMIT') THEN
SET actualLimitId = 'PRE_APPROVED_TRANSACTION_LIMIT';
ELSEIF (limitId='DAILY_LIMIT') THEN
SET actualLimitId = 'PRE_APPROVED_DAILY_LIMIT';
SET @id = (SELECT LEFT(UUID(), 50));
INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,Account_id,isAllowed,LimitType_id,value) VALUES
(@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,accountId,true,actualLimitId,0.00);
SET actualLimitId = 'AUTO_DENIED_DAILY_LIMIT';
ELSEIF (limitId='WEEKLY_LIMIT') THEN
SET actualLimitId = 'PRE_APPROVED_WEEKLY_LIMIT';
SET @id = (SELECT LEFT(UUID(), 50));
INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,Account_id,isAllowed,LimitType_id,value) VALUES
(@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,accountId,true,actualLimitId,0.00);
SET actualLimitId = 'AUTO_DENIED_WEEKLY_LIMIT';
END IF;

SET @id = (SELECT LEFT(UUID(), 50));
INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,Account_id,isAllowed,LimitType_id,value) VALUES
(@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,accountId,true,actualLimitId,@limitvalue);

SET @id = (SELECT LEFT(UUID(), 50));
INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,Account_id,isAllowed,LimitType_id,value) VALUES
(@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,accountId,true,limitId,@limitvalue);

SET entryStatus = 1;
ITERATE getlimit;
END IF;
END LOOP getlimit;
CLOSE limits;

SET finished = 0;
IF entryStatus = 0 THEN
SET @id = (SELECT LEFT(UUID(), 50));
INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,Account_id,isAllowed) VALUES
(@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,accountId,true);
END IF;
ITERATE getAction;
END IF;
END LOOP getAction;
CLOSE actions;
SET finished = 0;
ITERATE getAccount;
END IF;
END LOOP getAccount;
CLOSE accounts;





SET finished = 0;
OPEN nonaccountlevelactions;
getAction: LOOP
FETCH nonaccountlevelactions INTO featureActionId;
SET @featureId = (SELECT Feature_id FROM featureaction WHERE id = featureActionId );
SELECT @featureId;
IF finished = 1 THEN
LEAVE getAction;
else
SET @id = (SELECT LEFT(UUID(), 50));
INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,isAllowed) VALUES
(@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,true);
END IF;
ITERATE getAction;
END LOOP getAction;
CLOSE nonaccountlevelactions;
END$$
DELIMITER ;
;


   


USE `dbxdb`;
DROP procedure IF EXISTS `user_associated_corecustomeraccounts_info`;




DELIMITER $$
USE `dbxdb`$$
CREATE PROCEDURE `user_associated_corecustomeraccounts_info`(
    in customerId varchar(50) CHARACTER SET UTF8 COLLATE utf8_general_ci
)
BEGIN
    
    SET @implictCIF = (SELECT GROUP_CONCAT(contractcustomers.coreCustomerId SEPARATOR ",")
    FROM contractcustomers
    WHERE contractcustomers.customerId = customerId
    AND contractcustomers.autoSyncAccounts = '1' );
    
    SELECT contractcorecustomers.coreCustomerId , contractcorecustomers.contractId  from 
    contractcorecustomers where FIND_IN_SET(contractcorecustomers.coreCustomerId , @implictCIF) > 0;
    

    IF( @implictCIF) THEN
    
        SELECT customeraccounts.Account_id AS nonCIFAccounts
        FROM customeraccounts 
        WHERE customeraccounts.Customer_id = customerId
        AND FIND_IN_SET(customeraccounts.coreCustomerId , @implictCIF) = 0 ;
        
        SELECT contractaccounts.accountId AS contractaccounts
        FROM contractaccounts
        WHERE FIND_IN_SET(contractaccounts.coreCustomerId , @implictCIF) > 0 ; 
        
        SELECT excludedcontractaccounts.accountId AS excludedcontractaccounts
        FROM excludedcontractaccounts
        WHERE FIND_IN_SET(excludedcontractaccounts.coreCustomerId , @implictCIF) > 0 ; 
        
        SELECT customeraccounts.Account_id AS customeraccounts
        FROM customeraccounts
        WHERE FIND_IN_SET(customeraccounts.coreCustomerId , @implictCIF) > 0 
        AND customeraccounts.Customer_id = customerId; 
        
        SELECT excludedcustomeraccounts.Account_id AS excludedcustomeraccounts
        FROM excludedcustomeraccounts
        WHERE FIND_IN_SET(excludedcustomeraccounts.coreCustomerId , @implictCIF) > 0 
        AND excludedcustomeraccounts.Customer_id = customerId; 
    ELSE
        SELECT customeraccounts.Account_id AS nonCIFAccounts
        FROM customeraccounts 
        WHERE customeraccounts.Customer_id = customerId;
    END IF;
    
END$$
DELIMITER ;
;



    


USE `dbxdb`;
DROP procedure IF EXISTS `user_account_default_actions_create_proc`;





DELIMITER $$
USE `dbxdb`$$
CREATE PROCEDURE `user_account_default_actions_create_proc`(
IN _userId VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci, 
IN _accountsCSV TEXT CHARACTER SET UTF8 COLLATE utf8_general_ci, 
IN _coreCustomerId VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci,
IN _contractId VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci,  
IN _groupId VARCHAR(50) CHARACTER SET UTF8 COLLATE utf8_general_ci
)
BEGIN

 DECLARE finished INTEGER DEFAULT 0 ;
 DECLARE featureActionId varchar(255) DEFAULT "" ;
 DECLARE actionslist TEXT DEFAULT "" ;
 DECLARE limitId varchar(255) DEFAULT ""  ;
 DECLARE entryStatus INTEGER DEFAULT 0 ;
 DECLARE accountId varchar(255) DEFAULT "" ;
 DECLARE actualLimitId varchar(255) DEFAULT "" ;

DECLARE accounts CURSOR
         FOR (SELECT customeraccounts.Account_id FROM customeraccounts WHERE contractId = _contractId AND coreCustomerId = _coreCustomerId 
         AND Customer_id = _userId AND FIND_IN_SET(Account_id,_accountsCSV));
DECLARE actions CURSOR 
      FOR (select id from featureaction where FIND_IN_SET(id,@validActionsList) AND (featureaction.isAccountLevel = '1' OR featureaction.isAccountLevel = true ));
DECLARE limits CURSOR 
        FOR (select LimitType_id from actionlimit where Action_id  = featureActionId );
DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

SET SESSION group_concat_max_len = 100000000;

IF(ISNULL(_accountsCSV) OR _accountsCSV='' ) THEN
SET _accountsCSV = (SELECT group_concat(distinct customeraccounts.Account_id SEPARATOR ",") FROM customeraccounts WHERE customeraccounts.Customer_id = _userId
              AND customeraccounts.contractId = _contractId AND  customeraccounts.coreCustomerId = _coreCustomerId );
END IF;
 
SET @serviceDefinitionId = (SELECT servicedefinitionId from contract WHERE id = _contractId);
SET @serviceType = (SELECT serviceType from servicedefinition WHERE id = @serviceDefinitionId);
IF(ISNULL(_groupId) OR _groupId='' ) THEN
SET _groupId = (SELECT Group_id FROM groupservicedefinition WHERE serviceDefinitionId = @serviceDefinitionId 
                       AND (isDefaultGroup = true OR isDefaultGroup = '1'));
END IF;
                        
SET @validFIActions = (SELECT group_concat(distinct id SEPARATOR ",") FROM featureaction);

SET @validServiceDefinitionActions = (SELECT group_concat(distinct actionId SEPARATOR ",") FROM servicedefinitionactionlimit
                        WHERE serviceDefinitionId= @serviceDefinitionId AND FIND_IN_SET(actionId,@validFIActions));

SET _groupId = (SELECT Group_id FROM groupservicedefinition WHERE serviceDefinitionId = @serviceDefinitionId 
                        AND  Group_id = _groupId );


SET @validGroupActions = (SELECT group_concat(distinct Action_id SEPARATOR ",") FROM groupactionlimit WHERE Group_id = _groupId 
                                AND FIND_IN_SET(Action_id,@validServiceDefinitionActions));
                                
SET @validActionsList = (SELECT group_concat(distinct actionId SEPARATOR ",") FROM contractactionlimit WHERE contractId = _contractId
                                AND coreCustomerId = _coreCustomerId AND FIND_IN_SET(actionId,@validGroupActions));
  

OPEN accounts; 
getAccount : LOOP
FETCH accounts INTO accountId;
IF finished = 1 THEN 
    LEAVE getAccount;
ELSE
  OPEN actions; 
  getAction: LOOP
        SET entryStatus = 0;
        FETCH actions INTO featureActionId;
        SET @featureId = (SELECT Feature_id FROM featureaction WHERE id = featureActionId );
       SET @limitGroupId = (SELECT limitgroupId FROM featureaction WHERE id = featureActionId );
        IF finished = 1 THEN 
            LEAVE getAction;
        else
            OPEN limits; 
            getlimit: LOOP
            FETCH limits INTO limitId;
            IF finished = 1 THEN 
               LEAVE getlimit;
            else
               SET @limitvalue = (SELECT value FROM contractactionlimit WHERE actionId = featureActionId  
               AND limitTypeId = limitId AND contractId = _contractId AND coreCustomerId = _coreCustomerId);
    
               if (limitId='MAX_TRANSACTION_LIMIT') THEN 
               	SET actualLimitId = 'PRE_APPROVED_TRANSACTION_LIMIT';
				SET @id = (SELECT LEFT(UUID(), 50));
				INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,Account_id,isAllowed,limitGroupId,LimitType_id,value) VALUES
				(@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,accountId,true,@limitGroupId,actualLimitId,0.00);
               	SET actualLimitId = 'AUTO_DENIED_TRANSACTION_LIMIT';
               ELSEIF (limitId='DAILY_LIMIT') THEN 
               	SET actualLimitId = 'PRE_APPROVED_DAILY_LIMIT';
                SET @id = (SELECT LEFT(UUID(), 50));
                INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,Account_id,isAllowed,limitGroupId,LimitType_id,value) VALUES
                (@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,accountId,true,@limitGroupId,actualLimitId,0.00);
                SET actualLimitId = 'AUTO_DENIED_DAILY_LIMIT';
               ELSEIF (limitId='WEEKLY_LIMIT') THEN 
               	SET actualLimitId = 'PRE_APPROVED_WEEKLY_LIMIT';
                SET @id = (SELECT LEFT(UUID(), 50));
               	INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,Account_id,isAllowed,limitGroupId,LimitType_id,value) VALUES
                (@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,accountId,true,@limitGroupId,actualLimitId,0.00);
               	SET actualLimitId = 'AUTO_DENIED_WEEKLY_LIMIT';
               END IF;
               
              if (limitId!='MIN_TRANSACTION_LIMIT') THEN
               SET @id = (SELECT LEFT(UUID(), 50));
               INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,Account_id,isAllowed,limitGroupId,LimitType_id,value) VALUES
               (@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,accountId,true,@limitGroupId,actualLimitId,@limitvalue);
                
                SET @id = (SELECT LEFT(UUID(), 50));
                INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,Account_id,isAllowed,limitGroupId,LimitType_id,value) VALUES
                (@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,accountId,true,@limitGroupId,limitId,@limitvalue);
				END IF;
                SET entryStatus = 1;
               ITERATE  getlimit;
            END IF;
                END LOOP getlimit;
                CLOSE limits;
                
            SET finished = 0;
            IF entryStatus = 0 THEN
                  SET @id = (SELECT LEFT(UUID(), 50));
                   INSERT IGNORE INTO customeraction(id,RoleType_id,Customer_id,contractId,coreCustomerId,featureId,Action_id,Account_id,isAllowed) VALUES
                (@id,@serviceType,_userId,_contractId,_coreCustomerId,@featureId,featureActionId,accountId,true);
            END IF;
            ITERATE  getAction;
        END IF;
  END LOOP getAction;
  CLOSE actions;
  SET finished = 0;
  ITERATE  getAccount;
 END IF;
END LOOP getAccount;
CLOSE accounts;

END$$
DELIMITER ;
;



    


USE `dbxdb`;
DROP procedure IF EXISTS `get_associated_contractaccounts_proc`;





DELIMITER $$
USE `dbxdb`$$
CREATE PROCEDURE `get_associated_contractaccounts_proc`(
IN _accountIdList TEXT CHARACTER SET UTF8 COLLATE utf8_general_ci
)
BEGIN

 

    SET SESSION group_concat_max_len = 1000000;
    
    SET @accountIdList = (SELECT group_concat(accountId SEPARATOR ',') from contractaccounts WHERE FIND_IN_SET(accountId,_accountIdList));
    SET @excludedaccountIdList = (SELECT group_concat(accountId SEPARATOR ',') from excludedcontractaccounts WHERE FIND_IN_SET(accountId ,_accountIdList));

 

    select @accountIdList As accountIdList;
    select @excludedaccountIdList As excludedaccountIdList;

 

END$$


DELIMITER ;
;



    


USE `dbxdb`;
DROP procedure IF EXISTS `membership_relative_customer_get_proc`;





DELIMITER $$
USE `dbxdb`$$
CREATE PROCEDURE `membership_relative_customer_get_proc`(
in _id varchar(50) CHARACTER SET UTF8 
)
BEGIN

    select  membership.id , 
			membership.name , 
            membership.firstName , 
            membership.lastName , 
            membership.phone , 
            membership.email , 
			membership.dateOfBirth, 
			membership.taxId,
			membership.faxId,
            membership.industry, 
			membership.isBusinessType,
            membershiprelation.relationshipId,
            membershiprelation.relationshipName,
            address.addressLine1 ,
			address.addressLine2 , 
            address.cityName , 
            address.country , 
            address.zipCode , 
            address.state 
			from 
            membership 
			LEFT JOIN address on (membership.addressId = address.id)
            JOIN membershiprelation on (membershiprelation.relatedMebershipId  = membership.id)
			where membership.id in (select membershiprelation.relatedMebershipId  from membershiprelation where membershiprelation.membershipId = _id)
            and membershiprelation.membershipId  = _id;
      
END$$