
IF NOT EXISTS
(
    SELECT 1
    FROM sys.columns
    WHERE name = 'ServerGroupId'
      AND object_id = OBJECT_ID('HB_VPS_tblVPSVSwitchHiddenFromPanel')
)
BEGIN
    ALTER TABLE HB_VPS_tblVPSVSwitchHiddenFromPanel
    ADD ServerGroupId INT NOT NULL DEFAULT(0)
END
GO

IF OBJECT_ID(N'CSP_HB_VPS_tblVPSVSwitchHiddenFromPanel_Insert', N'P') IS NOT NULL
    DROP PROCEDURE CSP_HB_VPS_tblVPSVSwitchHiddenFromPanel_Insert;
GO
CREATE PROCEDURE CSP_HB_VPS_tblVPSVSwitchHiddenFromPanel_Insert
(  
    @VSwitchName NVARCHAR(200),  
    @VSwitchGuid UNIQUEIDENTIFIER,
    @ServerGroupId INT
)  
AS  
BEGIN  

    INSERT INTO HB_VPS_tblVPSVSwitchHiddenFromPanel  
    (  
        VSwitchGuid,  
        VSwitchName,
        ServerGroupId
    )  
    VALUES  
    (  
        @VSwitchGuid,  
        @VSwitchName,
        @ServerGroupId
    );     

END  
GO

 
ALTER PROCEDURE CSP_HB_VPS_tblVPSVSwitchHiddenFromPanel_Delete  
(  
 @VSwitchGuid Uniqueidentifier ,
 @ServerGroupId INT
  
)  
AS  
BEGIN  
  
  DELETE HB_VPS_tblVPSVSwitchHiddenFromPanel  
  WHERE VSwitchGuid = @VSwitchGuid  AND ServerGroupId=@ServerGroupId
END  
GO

ALTER PROCEDURE CSP_HB_VPS_tblVPSVSwitchHiddenFromPanel_Get  
   @ServerGroupId INT
AS  
BEGIN  
    
  SELECT *  
  FROM HB_VPS_tblVPSVSwitchHiddenFromPanel  
  WHERE ServerGroupId=  @ServerGroupId
   
END  
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'SPR_GetDLMembershipPendingJobs')
BEGIn
	DROP PROC SPR_GetDLMembershipPendingJobs
END
GO

CREATE PROCEDURE SPR_GetDLMembershipPendingJobs
(
	@CompanyId INT,
	@CustomerId INT,
	@OrganizationId INT,
	@DLID INT
)
AS
BEGIN
	DECLARE @Add_Mailbox TINYINT = 7
	DECLARE @Add_Mail_Contact TINYINT = 14
	DECLARE @Add_Dist_List TINYINT = 15
	DECLARE @Add_Mail_User TINYINT = 80
	DECLARE @Remove_Mailbox TINYINT = 21
	DECLARE @Remove_Mail_Contact TINYINT = 58
	DECLARE @Remove_Distribution_List TINYINT =59
	DECLARE @Remove_MailUser TINYINT = 81
	DECLARE @Completed TINYINT = 3
	DECLARE @Cancelled TINYINT = 2

	DECLARE @DistListPrimaryEmail  NVARCHAR(255);
	SELECT @DistListPrimaryEmail  = EmailAddress FROM HB_tblExchangeDistributionListsEmailAddresses where DistListID=@DLID and IsPrimaryEmailAddress = 1
	
	SELECT *, CASE WHEN ActionType IN (@Add_Mailbox,
	@Add_Mail_Contact,
	@Add_Dist_List,
	@Add_Mail_User) THEN 'AddJob' ELSE 'RemoveJob' END AS [JobType]  FROM HB_tblExchangeGroupJobs
	WHERE 
	CompanyId = @CompanyId
	AND CustomerId = @CustomerId
	AND OrganizationId = @OrganizationId 
	AND 
	(
	[Comments] = @DLID
	OR [Parameters] LIKE '%×' + @DistListPrimaryEmail
	)
	AND ActionType IN (
	@Add_Mailbox,
	@Add_Mail_Contact,
	@Add_Dist_List,
	@Add_Mail_User,
	@Remove_Mailbox, 
	@Remove_Mail_Contact,
	@Remove_Distribution_List,
	@Remove_MailUser
	)
	AND StatusId NOT IN (@Completed, @Cancelled)
	AND IsCancelled = 0
END
GO

ALTER PROCEDURE [SP_EM_ExGetDListMembers]        
 (
	@DLID int        
 )
AS        
BEGIN 

	DECLARE @OrganizationId INT;
	SELECT @OrganizationId = OrganizationID FROM HB_tblExchangeDistributionLists WHERE DistListID = @DLID

	Delete From HB_tblExchangeDistributionListsMembers Where RecipientType='mailbox'      
	and MailboxId not in (Select Mailboxid from HB_tblExchangeMailboxes)       
      
	Delete From HB_tblExchangeDistributionListsMembers Where RecipientType='contact'      
	and MailboxId not in (Select mailContactId from HB_tblExchangeMailContacts)      
      
	Delete From HB_tblExchangeDistributionListsMembers Where RecipientType='distlist'      
	and MailboxId not in (Select DistListId from HB_tblExchangeDistributionLists)   
   
	Delete From HB_tblExchangeDistributionListsMembers Where RecipientType='mailuser'      
	and MailboxId not in (Select MailUserId from Hb_tblExchangeMailUsers)     
      
	UPDATE M
	SET 
	M.MemberDisplayName = mbx.MailBoxDisplayName, 
	M.MemberEmailAddress = 
	EA.EmailAddress 
	FROM HB_tblExchangeDistributionListsMembers M 
	INNER JOIN HB_tblExchangeMailboxes Mbx ON M.MailboxID = Mbx.MailboxID
	INNER JOIN HB_tblExchangeEmailAddresses EA ON Mbx.MailboxID = EA.MailBoxID AND EA.IsPrimaryEmailAddress = 1
	AND M.DistListID = @DLID AND Mbx.OrganizationID = @OrganizationId 
	AND M.RecipientType = 'mailbox' 
	AND (M.MemberDisplayName <> Mbx.MailBoxDisplayName OR M.MemberEmailAddress <> EA.EmailAddress)

	UPDATE M
	SET 
	M.MemberDisplayName = MC.MailContactDisplayName, 
	M.MemberEmailAddress = MC.ExternalEmailAddress 
	FROM HB_tblExchangeDistributionListsMembers M 
	INNER JOIN HB_tblExchangeMailContacts MC ON M.MailboxID = MC.MailContactID
	AND M.DistListID = @DLID
	AND M.RecipientType = 'contact' 
	AND (M.MemberDisplayName <> MC.MailContactDisplayName OR M.MemberEmailAddress <> MC.ExternalEmailAddress)
 
	UPDATE M
	SET 
	M.MemberDisplayName = Dls.DistListName, 
	M.MemberEmailAddress = 
	EA.EmailAddress 
	FROM HB_tblExchangeDistributionListsMembers M 
	INNER JOIN HB_tblExchangeDistributionLists Dls ON M.MailboxID = Dls.DistListID
	INNER JOIN HB_tblExchangeDistributionListsEmailAddresses EA ON Dls.DistListID = EA.DistListID AND EA.IsPrimaryEmailAddress = 1
	AND M.DistListID = @DLID AND Dls.OrganizationID = @OrganizationId 
	AND M.RecipientType = 'distlist' 
	AND (M.MemberDisplayName <> dls.DistListName OR M.MemberEmailAddress <> EA.EmailAddress)

	UPDATE M
	SET 
	M.MemberDisplayName = ADU.DisplayName, 
	M.MemberEmailAddress = 
	EA.EmailAddress 
	FROM HB_tblExchangeDistributionListsMembers M 
	INNER JOIN Hb_tblExchangeMailUsers MUs ON M.MailboxID = MUs.MailUserId
	INNER JOIN HB_tblADirUsers ADU ON MUs.ADUserId = ADU.ADUserID
	INNER JOIN HB_tblMailUserEmailAddresses EA ON MUs.MailUserId = EA.MailUserId AND EA.IsPrimaryEmailAddress = 1
	AND M.DistListID = @DLID AND MUs.OrganizationId = @OrganizationId
	AND M.RecipientType = 'mailuser' 
	AND (M.MemberDisplayName <> ADU.DisplayName OR M.MemberEmailAddress <> EA.EmailAddress)

	Select * from HB_tblExchangeDistributionListsMembers       
	where DistListId=@DLID    
	and (RecipientType!='mailbox' OR (RecipientType='mailbox' and MailboxID NOT IN (Select MailboxId from HB_tblExchangeMailboxes Where SoftDelete='1')))  
	Order By MemberDisplayName  

 END
 GO

 ALTER Procedure CSP_VPS_HB_tblVPSAccountsNICSettings_Get      
(                    
@VPSID bigint                    
)                    
AS              
              
Declare @ServerGroupId bigint              
Declare @IsSharedPool BIT        
        
SET @ServerGroupId = (Select VPSServerGroupID from HB_VPS_tblVPSServer Where VPSServerID = (Select VPSServerID from HB_tblVPSAccounts       
where VPSId=@VPSID))                  
SET @IsSharedPool =   (select IsSingleVM from HB_tblVPSHosting INNER JOIN HB_tblVPSAccounts ON     
HB_tblVPSAccounts.VPSHostingId=HB_tblVPSHosting.VPSHostingID  INNER JOIN HB_tblPackagesSold ON     
HB_tblVPSHosting.SoldPackageId=HB_tblPackagesSold.SoldPackageId INNER JOIN HB_tblPackages ON     
HB_tblPackages.PackageID=HB_tblPackagesSold.PackageID where VPSId=@VPSID)         
  
  
Select distinct *  From [HB_tblVPSAccountsNICSettings]                     
inner join HB_VPS_tblVPSServerSubNets on HB_VPS_tblVPSServerSubNets.Subnet=[HB_tblVPSAccountsNICSettings].Subnet                     
and HB_VPS_tblVPSServerSubNets.Type=[HB_tblVPSAccountsNICSettings].Type and VPSServerGroupID=@ServerGroupId              
Where VPSId=@VPSID                     
and (SubnetId in (Select SubnetId from HB_VPS_tblVPSSubNetsIPs where VPSId=@VPSId and IP =     
Substring(HB_tblVPSAccountsNICSettings.IP + ',' , 0,Charindex(',', HB_tblVPSAccountsNICSettings.IP + ','))) )     
     
AND IsSharedVPSHostingPool = @IsSharedPool and HB_tblVPSAccountsNICSettings.Subnet!=''            
UNION ALL                    
Select HB_tblVPSAccountsNICSettings.*,'','','','','','','','','','','','','' from HB_tblVPSAccountsNICSettings                   
INNER JOIN   HB_tblVPSAccounts ON HB_tblVPSAccountsNICSettings.VPSId = HB_tblVPSAccounts.VPSId                     
INNER JOIN HB_tblVPSHosting ON HB_tblVPSHosting.VPSHostingID = HB_tblVPSAccounts.VPSHostingId                    
INNER JOIN HB_VPS_tblVPSServerGroups on HB_tblVPSHosting.VPSServerID = HB_VPS_tblVPSServerGroups.VPSServerGroupID                     
WHERE HB_tblVPSAccountsNICSettings.VPSId=@VPSID AND HVNEnabled = 1  and Type=0 and VPSServerGroupID=@ServerGroupId          
Union ALL      
Select HB_tblVPSAccountsNICSettings.*,'','','','','','','','','','','','','1' from HB_tblVPSAccountsNICSettings                   
INNER JOIN   HB_tblVPSAccounts ON HB_tblVPSAccountsNICSettings.VPSId = HB_tblVPSAccounts.VPSId                     
INNER JOIN HB_tblVPSHosting ON HB_tblVPSHosting.VPSHostingID = HB_tblVPSAccounts.VPSHostingId                    
INNER JOIN HB_VPS_tblVPSServerGroups on HB_tblVPSHosting.VPSServerID = HB_VPS_tblVPSServerGroups.VPSServerGroupID                     
WHERE HB_tblVPSAccountsNICSettings.VPSId=@VPSID AND HVNEnabled = 0  and Type=0 and VPSServerGroupID=@ServerGroupId        
and HB_tblVPSAccountsNICSettings.Subnet=''    
GO


Alter PROCEDURE [CSP_GetAllAllowedVHDSizeWithTypes]               
(                
@VPSHostingId int         
)                
AS                
BEGIN          
Declare @ResourceId int          
Set @ResourceId=(Select ResourceId From HB_tblResources  Where ResourceInternalId=903)                         
Declare @SoldPackageId int                        
Set @SoldPackageId=(Select SoldPackageID From HB_tblVPSHosting Where VPSHostingID =@VPSHostingId)          
        
 Select SUm(ResourceQuantity) as ResourceQuantity ,PrimaryStorageTypeId ,VPSStorageTypeName   , [Type]
 From        
 (        
 SELECT ResourceQuantity  ,PrimaryStorageTypeId,ISNULL(VPSStorageTypeName,'Not set') AS VPSStorageTypeName, 1 As [Type]
 from HB_tblPackages        
 Inner join HB_tblPackagesSold on HB_tblPackagesSold.PackageID = HB_tblPackages.PackageID        
 Inner join HB_tblPackagesSoldDetail on HB_tblPackagesSoldDetail.SoldPackageID = HB_tblPackagesSold.SoldPackageID        
 Left outer join HB_tblVPSStorageTypes On HB_tblVPSStorageTypes.VPSStorageTypeId = HB_tblPackages.PrimaryStorageTypeId      
 Where HB_tblPackagesSoldDetail.SoldPackageID=@SoldPackageId And ResourceID=@ResourceId   
Union ALl        
Select Quantity*ResourceQuantity ,StorageTypeId,ISNULL(VPSStorageTypeName,'Not set'), 1 As [Type]
From HB_tblAddonsSoldDetail        
inner join HB_tblAddonsSold  on HB_tblAddonsSold.SoldAddOnId= HB_tblAddonsSoldDetail.SoldAddOnId          
inner join HB_tblAddons on HB_tblAddons.AddOnID = HB_tblAddonsSold.AddOnId        
inner join HB_tblAddonTypes on HB_tblAddonTypes.AddOnTypeId = HB_tblAddons.AddOnTypeID        
Left outer join HB_tblVPSStorageTypes On HB_tblVPSStorageTypes.VPSStorageTypeId = HB_tblAddonTypes.StorageTypeId      
Where  SoldToAccountId=@VPSHostingId and HB_tblAddonTypes.PackageTypeID='19' and IsPending='0' and IsCancelled='0' And ResourceId=@ResourceId          
) AS tbl        
Where VPSStorageTypeName !=''    
Group by PrimaryStorageTypeId  ,VPSStorageTypeName, [Type]
End     
GO


ALTER PROCEDURE SPR_GetADirUsersByHostedOrgId            
(            
 @HostedOrgId INT            
)            
AS            
BEGIN            
 SELECT Distinct U.*,ORG.HostedOrgID, ORG.OrganizationName, ORG.ADDomainId,
 ISNULL((Select U2.UPName from HB_tblADirUsers U2 Where U2.HOrgId = U.HOrgId and U.DirectManagerLDAP != '' and U2.UserLDAPURL = U.DirectManagerLDAP), '') As Manager
 FROM HB_tblADirHostedOrganizations ORG             
 INNER JOIN HB_tblADirUsersDetail UD ON ORG.HostedOrgID = UD.HostedOrgID             
 INNER JOIN HB_tblADirUsers U ON U.ADUserID = UD.ADUserID            
 WHERE ORG.HostedOrgID = @HostedOrgId            
 order by DisplayName asc, UPName asc  
END    
GO

INSERT INTO HB_tblAddonTypeDetail (AddonTypeId, ResourceId, ResourceQuantity)
SELECT 
    at.AddonTypeId,
    r.ResourceId,
    0
FROM HB_tblAddonTypes at
INNER JOIN HB_tblResources r 
    ON r.ResourceInternalId = 925
WHERE at.PackageTypeID = 19
AND NOT EXISTS (
    SELECT 1 
    FROM HB_tblAddonTypeDetail d
    WHERE d.AddonTypeId = at.AddonTypeId
      AND d.ResourceId = r.ResourceId
);
GO