IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'CSP_GetSPUserByUPN')
BEGIN
	DROP PROCEDURE CSP_GetSPUserByUPN
END
GO  
CREATE PROCEDURE CSP_GetSPUserByUPN
(  
 @UPN NVARCHAR(200)  
)  
AS  
BEGIN  
 SELECT * FROM Hb_tblSPUsers WHERE UPN = @UPN   
END  
GO

Alter PROCEDURE SP_EM_GetServerForProvisioningByDomainId
(    
 @ADDomainId INT    
)    
AS        
BEGIN      
    
 If Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#RemoteServers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)        
 Begin        
  Drop Table #RemoteServers        
 End      
    
 Create Table #RemoteServers                  
 (                  
  [ID] int identity(1,1),      
  SGMemberID bigint,      
  SGroupID bigint,      
  ServerID bigint,                  
  ReferenceId nvarchar(1000) collate database_default,      
  ADDomain nvarchar(1000) collate database_default,      
  LoginName nvarchar(1000) collate database_default,      
  IP nvarchar(250) collate database_default,      
  DAGIPs ntext collate database_default,      
  ServiceTypeID int,      
  ServiceTypeName nvarchar(250) collate database_default,      
  ServiceTypeTransId nvarchar(250) collate database_default,      
  [Status] int,      
  ProviderID int,      
  ADDomainID int,      
  FQDN nvarchar(1000) collate database_default,       
  OverrideCredantials bit,      
  LoginPassword nvarchar(2000)  collate database_default    
 )      
    
 Declare @ServiceTypeName nvarchar(250)      
 Declare @ServiceTypeTransId nvarchar(250)      
    
 Select @ServiceTypeName=PackageTypeName,@ServiceTypeTransId=Trans_Id from HB_tblPackageTypes  Where PackageTypeID=19      
    
 Insert into #RemoteServers(SGMemberID, SGroupID, ServerID, ReferenceId, ADDomain, LoginName, IP, DAGIPs,ServiceTypeID,    
 ServiceTypeName,ServiceTypeTransId,    
 [Status],ProviderID,ADDomainID,FQDN,OverrideCredantials,LoginPassword)      
    
 select SGM.VPSServerID,SGM.VPSServerGroupID,SGM.VPSServerID, SGM.ReferenceID, '', ISNULL(SGM.Username, SG.Username) LoginName, SGM.IP, SGM.IP, '19',    
 @ServiceTypeName,@ServiceTypeTransId, SGM.Active, SG.Provider,SG.ADDomainID,'',SG.OverrideCredentials, SGM.Password      
 from HB_VPS_tblVPSServer SGM,HB_VPS_tblVPSServerGroups SG      
 Where SGM.VPSServerGroupID=SG.VPSServerGroupID and SG.Active=1 and SGM.Active=1 and SG.ADDomainID=@ADDomainId      
 and Exists (Select IsEnable from HB_tblModules Where ModuleId='6' and IsEnable='1')    
    
 DECLARE @ResultID int      
 DECLARE @DomainId int      
 Declare @LogonName nvarchar(1000)      
 Declare @DomainName nvarchar(1000)      
 DECLARE @OverrideCredantials bit      
 DECLARE @VPSServerGroupID int      
 DECLARE @LoginPassword nvarchar(2000)      
    
 DECLARE GetAllDomains Cursor FOR select [ID],ADDomainID,LoginName,ADDomain,OverrideCredantials,SGroupID,LoginPassword     
 from #RemoteServers      
    
 OPEN GetAllDomains      
  FETCH NEXT FROM GetAllDomains INTO @ResultID,@DomainId,@LogonName,@DomainName,@OverrideCredantials,@VPSServerGroupID,@LoginPassword      
  WHILE (@@FETCH_STATUS = 0)      
  BEGIN      
    
   if(((@LogonName is null) or (@LogonName='GqtktwCFe6FEAWBZujbz5w==~~~') or (@LogonName='')) and (@DomainId!='-1'))      
    (Select @LogonName=ADLogin, @LoginPassword=ADPassword, @DomainName=ADDomainName from HB_tblADirDomains Where ADDomainID=@DomainId)      
    
   if(@OverrideCredantials='1')      
    (Select @LogonName=UserName,@LoginPassword=Password from HB_VPS_tblVPSServerGroups Where VPSServerGroupID=@VPSServerGroupID)      
    
   Update #RemoteServers SET LoginName=@LogonName,LoginPassword=@LoginPassword, ADDomain=@DomainName Where [ID] = @ResultID      
    
   FETCH NEXT FROM GetAllDomains INTO @ResultID,@DomainId,@LogonName,@DomainName,@OverrideCredantials,@VPSServerGroupID,@LoginPassword      
  END      
     
 CLOSE GetAllDomains      
 DEALLOCATE GetAllDomains      
 
 DECLARE @PriorityServers NVARCHAR(MAX);
 SELECT @PriorityServers = RemoteServer FROM HB_tblADirDomains WHERE ADDomainID=@ADDomainId AND RemoteServer <> '';
 IF @PriorityServers IS NOT NULL AND @PriorityServers <> '' BEGIN
 DECLARE @Temp NVARCHAR(MAX);
 SET @Temp = '';
 SELECT @Temp = @Temp + ',' + [IP] FROM (
 SELECT [DATA] As [IP], FLOOR(ABS(CHECKSUM(NewId())) % 14) As Randomizer
 FROM Split(@PriorityServers, ',') 
 )t ORDER BY Randomizer
 SET @PriorityServers = SUBSTRING(@Temp,2,LEN(@Temp))
 END
 ELSE
 BEGIN
 SET @PriorityServers = ''
 END
 ;
 WITH Servers_CTE (IP,ServiceLogin,ServicePassword,SortOrder)         
 AS      
 (      
    
 SELECT @PriorityServers, ADLogin AS ServiceLogin, ADPassword AS ServicePassword, 1 As SortOrder FROM HB_tblADirDomains    
 Where ADDomainID=@ADDomainId AND RemoteServer <> ''
    
 UNION ALL      
    
  SELECT Top 10 HB_tblExchangeServerGroupsMembers.IP, HB_tblExchangeServerGroups.ServiceLogin,HB_tblExchangeServerGroups.ServicePassword,  2 As SortOrder
  FROM        HB_tblExchangeServerGroups INNER JOIN      
  HB_tblExchangeServerGroupsMembers ON HB_tblExchangeServerGroups.ServerGroupID = HB_tblExchangeServerGroupsMembers.ServerGroupID      
  WHERE (HB_tblExchangeServerGroups.ADDomainID = @ADDomainId) AND (HB_tblExchangeServerGroups.IsEnabled = 1) AND (HB_tblExchangeServerGroupsMembers.IsEnabled = 1)       
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='2' and IsEnable='1')  
  and ConnectionLatency > 1
   ORDER BY ((ActiveConnections + 1) * ConnectionLatency) ASC, ActiveConnections ASC, LastAccessDateStamp ASC

    
  UNION ALL      
    
  SELECT TOp 10  HB_tblExchangeServerGroupsMembers.DAGServers, HB_tblExchangeServerGroups.ServiceLogin,    
  HB_tblExchangeServerGroups.ServicePassword  ,  3 As SortOrder     
  FROM        HB_tblExchangeServerGroups INNER JOIN      
  HB_tblExchangeServerGroupsMembers ON HB_tblExchangeServerGroups.ServerGroupID = HB_tblExchangeServerGroupsMembers.ServerGroupID      
  WHERE  (HB_tblExchangeServerGroups.ADDomainID = @ADDomainId) AND (HB_tblExchangeServerGroups.IsEnabled = 1) AND (HB_tblExchangeServerGroupsMembers.IsEnabled = 1)       
  AND (HB_tblExchangeServerGroupsMembers.DAGServers != '')      
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='2' and IsEnable='1')      
  and ConnectionLatency > 1
   ORDER BY ((ActiveConnections + 1) * ConnectionLatency) ASC, ActiveConnections ASC, LastAccessDateStamp ASC

  UNION ALL      
    
  SELECT  HB_tblOCSServerGroupMembers.ServerIP IP, HB_tblOCSServerGroups.ServiceLogin, HB_tblOCSServerGroups.ServicePassword   ,  4 As SortOrder     
  FROM    HB_tblOCSServerGroups INNER JOIN      
  HB_tblOCSServerGroupMembers ON HB_tblOCSServerGroups.ServerGroupID = HB_tblOCSServerGroupMembers.ServerGroupID      
  WHERE  (HB_tblOCSServerGroups.ADDomainID = @ADDomainId) AND (HB_tblOCSServerGroups.IsEnabled = 1) AND (HB_tblOCSServerGroupMembers.IsEnabled = 1)       
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='5' and IsEnable='1')    
    
 UNION ALL    
    
 SELECT  HB_tblCRMServerGroupMembers.ServerIP IP, HB_tblCRMServerGroups.ServiceLogin, HB_tblCRMServerGroups.ServicePassword   ,  5 As SortOrder     
  FROM        HB_tblCRMServerGroups INNER JOIN      
  HB_tblCRMServerGroupMembers ON HB_tblCRMServerGroups.ServerGroupID = HB_tblCRMServerGroupMembers.ServerGroupID      
  WHERE     (HB_tblCRMServerGroups.ADDomainID = @ADDomainId) AND (HB_tblCRMServerGroups.IsEnabled = 1) AND (HB_tblCRMServerGroupMembers.IsEnabled = 1)      
 and Exists (Select IsEnable from HB_tblModules Where ModuleId='4' and IsEnable='1')    
    
  UNION ALL      
    
  SELECT  HB_tblSPServerGroupMembers.IP, HB_tblSPServerGroups.ServiceLogin, HB_tblSPServerGroups.ServicePassword   ,  6 As SortOrder     
  FROM  HB_tblSPServerGroups INNER JOIN      
  HB_tblSPServerGroupMembers ON HB_tblSPServerGroups.ServerGroupID = HB_tblSPServerGroupMembers.ServerGroupID      
  WHERE  (HB_tblSPServerGroups.ADDomainID = @ADDomainId) AND (HB_tblSPServerGroups.IsEnabled = 1) AND (HB_tblSPServerGroupMembers.IsEnabled = 1)      
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='1' and IsEnable='1')    
    
  UNION ALL      
    
  SELECT  S.IP, (SELECT ADLogin FROM HB_tblADirDomains Where ADDomainID=@ADDomainId), (SELECT ADPassword FROM HB_tblADirDomains Where ADDomainID=@ADDomainId), 7 As SortOrder     
  FROM  HB_tblWorkfolderServerGroup SG INNER JOIN      
  HB_tblWorkfolderServers S ON SG.ServerGroupID = S.ServerGroupID      
  WHERE  (SG.ADDomainID = @ADDomainId) AND (SG.IsEnabled = 1) AND (S.IsEnabled = 1)      
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='8' and IsEnable='1')    
    
  UNION ALL      
    
  SELECT  S.IP, (SELECT ADLogin FROM HB_tblADirDomains Where ADDomainID=@ADDomainId), (SELECT ADPassword FROM HB_tblADirDomains Where ADDomainID=@ADDomainId), 8 As SortOrder     
  FROM  Hb_tblRDS_ServerGroup SG INNER JOIN      
  Hb_tblRDS_Server S ON SG.RDServerGroupID = S.RDServerGroupID      
  WHERE  (SG.ADDomainID = @ADDomainId) AND (SG.[Enabled] = 1) AND (S.[Enabled] = 1)      
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='9' and IsEnable='1')    
       
  UNION ALL      
    
  SELECT IP, LoginName, LoginPassword ,  7 As SortOrder from #RemoteServers      
    
  )      
    
 SELECT Distinct [IP] , SortOrder FROM Servers_CTE  Where [IP] != '' order by SortOrder ASc    
    
 DROP TABLE #RemoteServers      
    
END    
GO


Alter PROCEDURE CSP_GetRandomExchangeServerIdBasedOnLastAccessedDateStamp   
(  
 @SGMemberId INT  
)  
As  
BEGIN

Update HB_tblExchangeServerGroupsMembers Set ActiveConnections=1 Where IsEnabled='1' and ActiveConnections > 11
and DATEADD(minute,60, LastAccessDateStamp ) < getdate()


Update HB_tblExchangeServerGroupsMembers Set ActiveConnections=0 Where IsEnabled='1' and ActiveConnections > 0
and DATEADD(minute,120, LastAccessDateStamp ) < getdate()

 DECLARE @RandomSGMemberId INT;
 SET @RandomSGMemberId  = 
 (
  SELECT TOP (1) SGMemberId FROM HB_tblExchangeServerGroupsMembers   
  WHERE ServerGroupID = (SELECT ServerGroupId FROM HB_tblExchangeServerGroupsMembers WHERE SGMemberID=@SGMemberId )  
  AND IsEnabled='1' AND ActiveConnections < 11 
  And ConnectionLatency > 1
  ORDER BY ((ActiveConnections + 1) * ConnectionLatency) ASC, ActiveConnections ASC, LastAccessDateStamp ASC
 )
 
 IF @RandomSGMemberId IS NULL
 BEGIN
  SET @RandomSGMemberId  = 
  (
   SELECT TOP (1) SGMemberId FROM HB_tblExchangeServerGroupsMembers   
   WHERE ServerGroupID = (SELECT ServerGroupId FROM HB_tblExchangeServerGroupsMembers WHERE SGMemberID=@SGMemberId )  
   AND IsEnabled = '1' and ConnectionLatency > 1
   ORDER BY ((ActiveConnections + 1) * ConnectionLatency) ASC, ActiveConnections ASC, LastAccessDateStamp ASC
  )
 END

 SET @RandomSGMemberId = ISNULL(@RandomSGMemberId, @SGMemberId)

 UPDATE HB_tblExchangeServerGroupsMembers Set LastAccessDateStamp = GetDate() Where SGMemberID=@RandomSGMemberId  

 SELECT @RandomSGMemberId AS SGMemberId
END
GO



Alter Procedure CSP_GetALLADUsersNotMemberOfSGroup
(
 @SGroupId int,
 @HostedOrgId int
)
AS

Delete from Hb_tblADirSecurityGroupsMembers Where SGroupId=@SGroupId and IsDeletedManually = 0
and MemberType='user' and MemberLDAP not in (Select U.UserLDAPURL from HB_tblADirUsers U, HB_tblADirUsersDetail UD
Where HostedOrgID = @HostedOrgId and U.ADUserID = UD.ADUserID)

Select * from HB_tblADirUsers Where UserLDAPURL != '' and UserLDAPURL NOT IN (Select MemberLDAP from Hb_tblADirSecurityGroupsMembers where SGroupId = @SGroupId)
 and ADUserID in (Select ADUserId from HB_tblADirUsersDetail Where HostedOrgID = @HostedOrgId)
GO

 
Alter Procedure [SP_EM_Delete_ADUserFromDB]    
(    
 @ADUserId int  
)    
AS   
BEGIN
 Delete from Hb_tblADirSecurityGroupsMembers Where MemberType='user' and MemberLDAP = (Select UserLDAPURL from HB_tblADirUsers Where ADUserID=@ADUserId)
 Delete from Hb_tblCRMUsers where ADUserID = @ADUserId
 Delete from HB_tblExchangeMailboxes where ADUserID = @ADUserId
 Update Hb_tblO365Users set ADUserId = 0 where ADUserId = @ADUserId
 Delete from HB_tblSPUsers where ADUserID  = @ADUserId
 Delete from HB_tblLyncUsers where ADUserID = @ADUserId
 Delete from HB_tblADirUsersDetail where ADUserID = @ADUserId
 Delete from HB_tblADirUsers where ADUserID = @ADUserId
END
GO


Alter PROCEDURE SP_GetExchangeOrganizationSettings  
@CompanyId int, 
@OrganizationId int, 
@CustomerId int, 
@CustomerFilter nvarchar(500), 
@IsGlobalSettings bit,
@SubResellerId int
AS      
BEGIN  
IF @IsGlobalSettings = '1'  
BEGIN

	If exists (SELECT * FROM HB_tblExchangeOrganizationSettings Where CompanyId = @CompanyId and OrganizationId = 0 
	And (@SubResellerId <= 0 OR CompanyId IN (Select CompanyID from tblCustomers Where SubResellerId = @SubResellerId)))
		SELECT * FROM HB_tblExchangeOrganizationSettings Where CompanyId = @CompanyId and OrganizationId = 0 
		And (@SubResellerId <= 0 OR CompanyId IN (Select CompanyID from tblCustomers Where SubResellerId = @SubResellerId))
	Else
		If exists (SELECT * FROM HB_tblExchangeOrganizationSettings Where CompanyId = @CompanyId and OrganizationId = 0)
			SELECT * FROM HB_tblExchangeOrganizationSettings Where CompanyId = @CompanyId and OrganizationId = 0
		else
			SELECT * FROM HB_tblExchangeOrganizationSettings Where CompanyId = 1 and OrganizationId = 0
END  
ELSE  
BEGIN  
 SELECT O.*, C.FirstName, C.LastName, D.DomainName 
 FROM HB_tblExchangeOrganizationSettings O, tblCustomers C, HB_tblExchangeOrganizationDomains D  
 Where O.CustomerId = C.CustomerID And O.OrganizationId > 0   
 and D.OrganizationID=O.OrganizationId
 and D.IsDefaultDomain = 1
 And (@CompanyId <= 0 OR O.CompanyId = @CompanyId) AND (@OrganizationId <= 0 OR O.OrganizationId = @OrganizationId)  
 AND (@CustomerId <= 0 OR O.CustomerId = @CustomerId)  
 AND (@CustomerFilter = '' OR C.FirstName + ' ' + C.LastName LIKE '%'+@CustomerFilter+'%')  
 And (@SubResellerId <= 0 OR C.SubResellerId = @SubResellerId)
 ORDER BY O.OrganizationId ASC   
END  
END  
GO

ALTER PROCEDURE SPR_AD_InsertADirUserGroupJob      
(                
 @JobText NTEXT,                
 @CompanyId INT,                
 @CustomerId INT,                
 @ADUserId INT,       
 @HostedOrgId INT,                
 @ActionType INT,                
 @StatusId INT,                
 @TimeStampEntered DATETIME,                
 @TriesCount INT,                
 @LastTryTimeStamp DATETIME,                
 @AddedBy NVARCHAR(500),                
 @Comments NVARCHAR(MAX),      
 @Parameters NVARCHAR(MAX),      
 @AccountType TINYINT = 1,      
 @ActionData NVARCHAR(MAX) = ''      
)                
AS                
BEGIN    

 if @ActionType NOT IN (2, 6, 10, 12, 15, 18, 19)
 BEGIN
	 
	 DECLARE @UserUPN NVARCHAR(255);  
	 IF @ADUserId > 0  
	 BEGIN  
		SELECT @UserUPN = UPName from HB_tblADirUsers WHERE ADUserId = @ADUserId  
	 END  
	 SET @UserUPN = ISNULL(@UserUPN, '')  
  
	 IF @UserUPN <> '' AND @Parameters NOT LIKE '% - [[]'+ @UserUPN +']%'  
	 BEGIN  
		SET @Parameters = @Parameters + ' - ['+ @UserUPN  +']'  
	 END 
 END
  
 
 IF @ActionType IN (7 , 12)      
 BEGIN      
  DELETE HB_tblADirUserGroupJobs WHERE [CompanyId]=@CompanyId                
  AND [CustomerId]=@CustomerId                
  AND [ADUserId]=@ADUserId       
  AND [HostedOrgId] = @HostedOrgId       
  AND [ActionType]=@ActionType                
  AND [Parameters] = @Parameters       
  AND [AccountType] = @AccountType      
  AND StatusId IN (2,3) 
 END      
      
 
 IF @ActionType IN (5)      
 BEGIN      
  IF NOT EXISTS(Select * from [HB_tblADirUserGroupJobs] WHERE [CompanyId]=@CompanyId AND [CustomerId]=@CustomerId      
 AND [ADUserId]=@ADUserId AND [HostedOrgId] = @HostedOrgId AND [ActionType]=@ActionType AND [Comments]=@Comments      
 AND (StatusId!='2' AND StatusId!='3'))      
  BEGIN      
    INSERT INTO [HB_tblADirUserGroupJobs]      
 ([JobText],[CompanyId],[CustomerId],[ADUserId],[HostedOrgId],[ActionType],[StatusId],[TimeStampEntered],[TriesCount],[LastTryTimeStamp],[AddedBy],[Comments],      
  [LastLogEntry],[Parameters],[AccountType], [ActionData])                
 VALUES(@JobText ,@CompanyId ,@CustomerId ,@ADUserId, @HostedOrgId ,@ActionType,@StatusId,@TimeStampEntered,@TriesCount,@LastTryTimeStamp,@AddedBy,@Comments,      
  '',@Parameters, @AccountType, @ActionData)      
  END      
  ELSE      
  BEGIN      
     UPDATE [HB_tblADirUserGroupJobs] SET TriesCount=TriesCount      
  WHERE [CompanyId]=@CompanyId AND [CustomerId]=@CustomerId AND [ADUserId]=@ADUserId AND [HostedOrgId] = @HostedOrgId       
  AND [ActionType]=@ActionType AND [Comments]=@Comments AND (StatusId!='2' AND StatusId!='3')      
  END      
 END      
 ELSE      
 BEGIN      
  UPDATE [HB_tblADirUserGroupJobs] SET StatusId='2', Comments= Comments+' Old job being cancelled because similar new job added.'                
  WHERE [CompanyId]=@CompanyId                
  AND [CustomerId]=@CustomerId                
  AND [ADUserId]=@ADUserId       
  AND [HostedOrgId] = @HostedOrgId       
  AND [ActionType]=@ActionType                
  AND [Comments]=@Comments              
  AND (StatusId!='2' AND StatusId!='3')             
                 
  INSERT INTO [HB_tblADirUserGroupJobs]                
  ([JobText],[CompanyId],[CustomerId],[ADUserId],[HostedOrgId],[ActionType],[StatusId],[TimeStampEntered],[TriesCount],[LastTryTimeStamp],[AddedBy],[Comments],[LastLogEntry],[Parameters],[AccountType], [ActionData])                
  VALUES(@JobText ,@CompanyId ,@CustomerId ,@ADUserId, @HostedOrgId ,@ActionType,@StatusId,@TimeStampEntered,@TriesCount,@LastTryTimeStamp,@AddedBy,@Comments,'',@Parameters, @AccountType, @ActionData)      
 END    
    
 SELECT CAST(JobId AS NVARCHAR(50)) AS RID FROM [HB_tblADirUserGroupJobs]     
  WHERE [CompanyId]=@CompanyId                
  AND [CustomerId]=@CustomerId                
  AND [ADUserId]=@ADUserId       
  AND [HostedOrgId] = @HostedOrgId       
  AND [ActionType]=@ActionType                
  AND [Comments]=@Comments              
  AND (StatusId!='2' AND StatusId!='3')    
  ORDER BY TimeStampEntered DESC    
END    
GO




IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'CSP_UpdateMailBoxTemplateSettingForBasicType')
BEGIN
	DROP PROCEDURE CSP_UpdateMailBoxTemplateSettingForBasicType
END
GO
CREATE PROCEDURE CSP_UpdateMailBoxTemplateSettingForBasicType
AS
UPDATE Hb_tblExchangeMailBoxTemplates
SET OWAActiveSyncIntegrationEnabled = 0,
    OWAPublicFolderEnabled = 0
WHERE SPLAType = 1;

UPDATE d
SET d.OWAActiveSync = 0,
    d.OWAPublicFolder = 0
FROM HB_tblExchangeMailboxDetail d
INNER JOIN HB_tblExchangeMailboxes m
    ON m.MailboxID = d.MailboxID
INNER JOIN Hb_tblExchangeMailBoxTemplates t
    ON m.MailBoxTemplateId = t.MailBoxTemplateId
WHERE t.SPLAType = 1;
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'CSP_GETMailboxesToApplyBasicOWAPolicy')
BEGIN
	DROP PROCEDURE CSP_GETMailboxesToApplyBasicOWAPolicy
END
GO
CREATE PROCEDURE CSP_GETMailboxesToApplyBasicOWAPolicy
AS
SELECT m.MailboxId,e.SGMemberID FROM HB_tblExchangeMailboxDetail d
INNER JOIN HB_tblExchangeMailboxes m ON m.MailboxID = d.MailboxID
INNER JOIN Hb_tblExchangeMailBoxTemplates t ON m.MailBoxTemplateId = t.MailBoxTemplateId
INNER JOIN HB_tblExchangeOrgOWAMailboxPolicy p ON p.OrganizationID = m.OrganizationID 
INNER JOIN HB_tblExchangeOrganizations o On O.OrganizationID = m.OrganizationID 
INNER JOIN HB_tblExchangeHosting e ON e.ExchHostingID = o.ExchHostingID 
WHERE t.SPLAType = 1 
and d.OWAMailboxPolicyName = p.PolicyName
and (p.ActiveSyncIntegrationEnabled != 0 OR p.PublicFoldersEnabled  != 0)
And m.[Status] != '1' and m.UserLDAPUrl != ''
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'CSP_UpdateOwaPolicyNameToApplyDefaultOWAPolicy')
BEGIN
	DROP PROCEDURE CSP_UpdateOwaPolicyNameToApplyDefaultOWAPolicy
END
GO
CREATE PROCEDURE CSP_UpdateOwaPolicyNameToApplyDefaultOWAPolicy
AS
UPDATE d
SET d.OWAMailboxPolicyName=''
FROM HB_tblExchangeMailboxDetail d
INNER JOIN HB_tblExchangeMailboxes m ON m.MailboxID = d.MailboxID
INNER JOIN Hb_tblExchangeMailBoxTemplates t ON m.MailBoxTemplateId = t.MailBoxTemplateId
INNER JOIN HB_tblExchangeOrgOWAMailboxPolicy p ON p.OrganizationID = m.OrganizationID 
INNER JOIN HB_tblExchangeOrganizations o On O.OrganizationID = m.OrganizationID 
INNER JOIN HB_tblExchangeHosting e ON e.ExchHostingID = o.ExchHostingID 
WHERE t.SPLAType IN (2,3,4,5) 
and d.OWAMailboxPolicyName = p.PolicyName AND  d.OWAMailboxPolicyName LIKE 'Default OWA Mailbox Policy For SG%'
And m.[Status] != '1' and m.UserLDAPUrl != ''
GO

  
Alter PROCEDURE [SP_EM_InserExchangeServerGroup]   
(   
 @ServerGroupName_1  [nvarchar](350),  @ProviderID_2  [int],  @ServerGroupFillStyle_3  [int], @ServerGroupPurposingType_4  [int],    
 @AutoProvisioning_5  [bit], @IsEnabled_6  [bit],  @CompanyId_7  [int],@LDAP [nvarchar](500),@ADDomainID int, @ServiceLogin nvarchar (255),   
 @ServicePassword nvarchar (255), @ProviderAccessDomain nvarchar(200), @AutoDiscoverURL nvarchar(500), @MXRecord nvarchar(1000),   
 @OWAURL nvarchar(500), @AutodiscoverredirectURL nvarchar(500), @OutlookAnywhereURL nvarchar(500), @OutlookdownloadURL nvarchar(500),   
 @EntouragedownloadURL nvarchar(500), @IncomingmailAddress nvarchar(500), @OutgoingMailAddress nvarchar(500),   
 @ExchangeProxysetting nvarchar(500), @PDC nvarchar(1000), @SenderPolicyFrameworkAddress nvarchar(1000)  
)    
AS   
 INSERT INTO [HB_tblExchangeServerGroups]([ServerGroupName], [ProviderID], [ServerGroupFillStyle], [ServerGroupPurposingType], [AutoProvisioning],   
 [IsEnabled], [CompanyId], [ResellerLDAPUrl], [ADDomainID], ServiceLogin, ServicePassword, ProviderAccessDomain, AutoDiscoverURL, MXRecord,   
 OWAURL, AutodiscoverredirectURL, OutlookAnywhereURL, OutlookdownloadURL, EntouragedownloadURL, IncomingmailAddress, OutgoingMailAddress,   
 ExchangeProxysetting, PDC, SenderPolicyFrameworkAddress)  
 VALUES(@ServerGroupName_1, @ProviderID_2, @ServerGroupFillStyle_3, @ServerGroupPurposingType_4, @AutoProvisioning_5, @IsEnabled_6,  
 @CompanyId_7, @LDAP, @ADDomainId, @ServiceLogin, @ServicePassword, @ProviderAccessDomain, @AutoDiscoverURL, @MXRecord, @OWAURL,  
 @AutodiscoverredirectURL, @OutlookAnywhereURL, @OutlookdownloadURL, @EntouragedownloadURL, @IncomingmailAddress , @OutgoingMailAddress,   
 @ExchangeProxysetting, @PDC, @SenderPolicyFrameworkAddress)       
  
 Declare @ServerGroupId int  
 SET @ServerGroupId = (Select SCOPE_IDENTITY() as RID)  
  
 IF @ProviderID_2 != 4   
 BEGIN  
  Declare @PolicyName nvarchar(300) = 'Default OWA Mailbox Policy For SG' + convert(nvarchar(10), @ServerGroupID)  
  
  INSERT INTO HB_tblExchangeSGOWAMailboxPolicy(ServerGroupId, PolicyName, AssignedToCustomerDisplay, AssignedToCustomerIds, IsDefault)  
  VALUES(@ServerGroupId, @PolicyName, ',All,', ',-1,', '1')  

  Set @PolicyName = ('Default Basic SPLA OWA Mailbox Policy For SG' + convert(nvarchar(10), @ServerGroupID))
  
	INSERT INTO HB_tblExchangeSGOWAMailboxPolicy(ServerGroupId, PolicyName, AssignedToCustomerDisplay, AssignedToCustomerIds, IsDefault)
	VALUES(@ServerGroupId, @PolicyName, ',All,', ',-1,', '0')
		
	Update HB_tblExchangeSGOWAMailboxPolicy Set
	ActiveSyncIntegrationEnabled = '0',
	PublicFoldersEnabled = '0',
	UMIntegrationEnabled = '0'
	Where PolicyName = ('Default Basic SPLA OWA Mailbox Policy For SG' + convert(nvarchar(10), @ServerGroupID))

 END  
  
 Select @ServerGroupId As RID   
 GO


 DECLARE @ServerGroupID int
DECLARE GetAllServerGroups Cursor FOR select ServerGroupID from HB_tblExchangeServerGroups where IsEnabled = 1
OPEN GetAllServerGroups
FETCH NEXT FROM GetAllServerGroups INTO @ServerGroupID
WHILE (@@FETCH_STATUS = 0)
BEGIN
Declare @PolicyName nvarchar(300) = 'Default Basic SPLA OWA Mailbox Policy For SG' + convert(nvarchar(10), @ServerGroupID)

IF NOT EXISTS(Select * from HB_tblExchangeSGOWAMailboxPolicy Where ServerGroupId = @ServerGroupID and PolicyName = @PolicyName)
INSERT INTO HB_tblExchangeSGOWAMailboxPolicy(ServerGroupId, PolicyName, AssignedToCustomerDisplay, AssignedToCustomerIds, IsDefault)
VALUES(@ServerGroupId, @PolicyName, ',All,', ',-1,', '0')

FETCH NEXT FROM GetAllServerGroups INTO @ServerGroupID
END
CLOSE GetAllServerGroups
DEALLOCATE GetAllServerGroups
GO

Update HB_tblExchangeSGOWAMailboxPolicy Set
ActiveSyncIntegrationEnabled = '0',
PublicFoldersEnabled = '0',
UMIntegrationEnabled = '0'
Where PolicyName like 'Default Basic SPLA OWA Mailbox Policy For SG%'
GO

DECLARE @OrganizationID int
Declare @ServerGroupID int
Declare @OrgInternalName nvarchar(200)
DECLARE GetAllOrgs Cursor FOR select O.OrganizationID, M.ServerGroupID, O.OrgInternalName
from HB_tblExchangeHosting H INNER JOIN HB_tblExchangeOrganizations O On H.ExchHostingID = O.ExchHostingID
INNER JOIN HB_tblExchangeServerGroupsMembers M On H.SGMemberID = M.SGMemberID
Where O.OrganizationName != 'Add New Organization' and H.IsCancelled = 0 and O.Status ='1'
OPEN GetAllOrgs
FETCH NEXT FROM GetAllOrgs INTO @OrganizationID, @ServerGroupID, @OrgInternalName
WHILE (@@FETCH_STATUS = 0)
BEGIN

Declare @PolicyName nvarchar(300) = 'Default Basic SPLA OWA Mailbox Policy For SG' + convert(nvarchar(10), @ServerGroupID)

IF NOT EXISTS(Select * from HB_tblExchangeOrgOWAMailboxPolicy Where OrganizationID = @OrganizationID and PolicyName = @PolicyName)
BEGIN
 INSERT INTO HB_tblExchangeOrgOWAMailboxPolicy(SGOWAPolicyId, OrganizationID, PolicyName, IsDefault)
 SELECT SGOWAPolicyId, @OrganizationID, PolicyName, '0' from HB_tblExchangeSGOWAMailboxPolicy
 Where ServerGroupId = @ServerGroupID and IsDefault = '0'
 and PolicyName = @PolicyName
END

FETCH NEXT FROM GetAllOrgs INTO @OrganizationID, @ServerGroupID, @OrgInternalName
END
CLOSE GetAllOrgs
DEALLOCATE GetAllOrgs
GO

Update HB_tblExchangeOrgOWAMailboxPolicy Set
ActiveSyncIntegrationEnabled = '0',
PublicFoldersEnabled = '0',
UMIntegrationEnabled = '0'
Where PolicyName like 'Default Basic SPLA OWA Mailbox Policy For SG%'
GO

ALTER PROCEDURE [SP_EM_AddExContactEmailAddresses]      
 (@ContactId  [int],        
  @EmailAddress_2  [nvarchar](300),       
  @IsPrimaryEmailAddress_3  [bit],      
  @DateStamp_4  [datetime],      
  @EmailType nvarchar(50))      
AS    
Declare @Id int
INSERT INTO [HB_tblContactEmailAddresses]([ContactId], [EmailAddress], [IsPrimaryEmailAddress], [DateStamp], EmailType)    
VALUES(@ContactId, @EmailAddress_2, @IsPrimaryEmailAddress_3, @DateStamp_4, @EmailType) 
SET @Id = SCOPE_IDENTITY()
SELECT @Id AS RID
GO



IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'CSP_GetSPUserByUPN')
BEGIN
	DROP PROCEDURE CSP_GetSPUserByUPN
END
GO  
CREATE PROCEDURE CSP_GetSPUserByUPN
(  
 @UPN NVARCHAR(200)  
)  
AS  
BEGIN  
 SELECT * FROM Hb_tblSPUsers WHERE UPN = @UPN   
END  
GO

Alter PROCEDURE SP_EM_GetServerForProvisioningByDomainId
(    
 @ADDomainId INT    
)    
AS        
BEGIN      
    
 If Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#RemoteServers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)        
 Begin        
  Drop Table #RemoteServers        
 End      
    
 Create Table #RemoteServers                  
 (                  
  [ID] int identity(1,1),      
  SGMemberID bigint,      
  SGroupID bigint,      
  ServerID bigint,                  
  ReferenceId nvarchar(1000) collate database_default,      
  ADDomain nvarchar(1000) collate database_default,      
  LoginName nvarchar(1000) collate database_default,      
  IP nvarchar(250) collate database_default,      
  DAGIPs ntext collate database_default,      
  ServiceTypeID int,      
  ServiceTypeName nvarchar(250) collate database_default,      
  ServiceTypeTransId nvarchar(250) collate database_default,      
  [Status] int,      
  ProviderID int,      
  ADDomainID int,      
  FQDN nvarchar(1000) collate database_default,       
  OverrideCredantials bit,      
  LoginPassword nvarchar(2000)  collate database_default    
 )      
    
 Declare @ServiceTypeName nvarchar(250)      
 Declare @ServiceTypeTransId nvarchar(250)      
    
 Select @ServiceTypeName=PackageTypeName,@ServiceTypeTransId=Trans_Id from HB_tblPackageTypes  Where PackageTypeID=19      
    
 Insert into #RemoteServers(SGMemberID, SGroupID, ServerID, ReferenceId, ADDomain, LoginName, IP, DAGIPs,ServiceTypeID,    
 ServiceTypeName,ServiceTypeTransId,    
 [Status],ProviderID,ADDomainID,FQDN,OverrideCredantials,LoginPassword)      
    
 select SGM.VPSServerID,SGM.VPSServerGroupID,SGM.VPSServerID, SGM.ReferenceID, '', ISNULL(SGM.Username, SG.Username) LoginName, SGM.IP, SGM.IP, '19',    
 @ServiceTypeName,@ServiceTypeTransId, SGM.Active, SG.Provider,SG.ADDomainID,'',SG.OverrideCredentials, SGM.Password      
 from HB_VPS_tblVPSServer SGM,HB_VPS_tblVPSServerGroups SG      
 Where SGM.VPSServerGroupID=SG.VPSServerGroupID and SG.Active=1 and SGM.Active=1 and SG.ADDomainID=@ADDomainId      
 and Exists (Select IsEnable from HB_tblModules Where ModuleId='6' and IsEnable='1')    
    
 DECLARE @ResultID int      
 DECLARE @DomainId int      
 Declare @LogonName nvarchar(1000)      
 Declare @DomainName nvarchar(1000)      
 DECLARE @OverrideCredantials bit      
 DECLARE @VPSServerGroupID int      
 DECLARE @LoginPassword nvarchar(2000)      
    
 DECLARE GetAllDomains Cursor FOR select [ID],ADDomainID,LoginName,ADDomain,OverrideCredantials,SGroupID,LoginPassword     
 from #RemoteServers      
    
 OPEN GetAllDomains      
  FETCH NEXT FROM GetAllDomains INTO @ResultID,@DomainId,@LogonName,@DomainName,@OverrideCredantials,@VPSServerGroupID,@LoginPassword      
  WHILE (@@FETCH_STATUS = 0)      
  BEGIN      
    
   if(((@LogonName is null) or (@LogonName='GqtktwCFe6FEAWBZujbz5w==~~~') or (@LogonName='')) and (@DomainId!='-1'))      
    (Select @LogonName=ADLogin, @LoginPassword=ADPassword, @DomainName=ADDomainName from HB_tblADirDomains Where ADDomainID=@DomainId)      
    
   if(@OverrideCredantials='1')      
    (Select @LogonName=UserName,@LoginPassword=Password from HB_VPS_tblVPSServerGroups Where VPSServerGroupID=@VPSServerGroupID)      
    
   Update #RemoteServers SET LoginName=@LogonName,LoginPassword=@LoginPassword, ADDomain=@DomainName Where [ID] = @ResultID      
    
   FETCH NEXT FROM GetAllDomains INTO @ResultID,@DomainId,@LogonName,@DomainName,@OverrideCredantials,@VPSServerGroupID,@LoginPassword      
  END      
     
 CLOSE GetAllDomains      
 DEALLOCATE GetAllDomains      
 
 DECLARE @PriorityServers NVARCHAR(MAX);
 SELECT @PriorityServers = RemoteServer FROM HB_tblADirDomains WHERE ADDomainID=@ADDomainId AND RemoteServer <> '';
 IF @PriorityServers IS NOT NULL AND @PriorityServers <> '' BEGIN
 DECLARE @Temp NVARCHAR(MAX);
 SET @Temp = '';
 SELECT @Temp = @Temp + ',' + [IP] FROM (
 SELECT [DATA] As [IP], FLOOR(ABS(CHECKSUM(NewId())) % 14) As Randomizer
 FROM Split(@PriorityServers, ',') 
 )t ORDER BY Randomizer
 SET @PriorityServers = SUBSTRING(@Temp,2,LEN(@Temp))
 END
 ELSE
 BEGIN
 SET @PriorityServers = ''
 END
 ;
 WITH Servers_CTE (IP,ServiceLogin,ServicePassword,SortOrder)         
 AS      
 (      
    
 SELECT @PriorityServers, ADLogin AS ServiceLogin, ADPassword AS ServicePassword, 1 As SortOrder FROM HB_tblADirDomains    
 Where ADDomainID=@ADDomainId AND RemoteServer <> ''
    
 UNION ALL      
    
  SELECT Top 10 HB_tblExchangeServerGroupsMembers.IP, HB_tblExchangeServerGroups.ServiceLogin,HB_tblExchangeServerGroups.ServicePassword,  2 As SortOrder
  FROM        HB_tblExchangeServerGroups INNER JOIN      
  HB_tblExchangeServerGroupsMembers ON HB_tblExchangeServerGroups.ServerGroupID = HB_tblExchangeServerGroupsMembers.ServerGroupID      
  WHERE (HB_tblExchangeServerGroups.ADDomainID = @ADDomainId) AND (HB_tblExchangeServerGroups.IsEnabled = 1) AND (HB_tblExchangeServerGroupsMembers.IsEnabled = 1)       
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='2' and IsEnable='1')  
  and ConnectionLatency > 1
   ORDER BY ((ActiveConnections + 1) * ConnectionLatency) ASC, ActiveConnections ASC, LastAccessDateStamp ASC

    
  UNION ALL      
    
  SELECT TOp 10  HB_tblExchangeServerGroupsMembers.DAGServers, HB_tblExchangeServerGroups.ServiceLogin,    
  HB_tblExchangeServerGroups.ServicePassword  ,  3 As SortOrder     
  FROM        HB_tblExchangeServerGroups INNER JOIN      
  HB_tblExchangeServerGroupsMembers ON HB_tblExchangeServerGroups.ServerGroupID = HB_tblExchangeServerGroupsMembers.ServerGroupID      
  WHERE  (HB_tblExchangeServerGroups.ADDomainID = @ADDomainId) AND (HB_tblExchangeServerGroups.IsEnabled = 1) AND (HB_tblExchangeServerGroupsMembers.IsEnabled = 1)       
  AND (HB_tblExchangeServerGroupsMembers.DAGServers != '')      
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='2' and IsEnable='1')      
  and ConnectionLatency > 1
   ORDER BY ((ActiveConnections + 1) * ConnectionLatency) ASC, ActiveConnections ASC, LastAccessDateStamp ASC

  UNION ALL      
    
  SELECT  HB_tblOCSServerGroupMembers.ServerIP IP, HB_tblOCSServerGroups.ServiceLogin, HB_tblOCSServerGroups.ServicePassword   ,  4 As SortOrder     
  FROM    HB_tblOCSServerGroups INNER JOIN      
  HB_tblOCSServerGroupMembers ON HB_tblOCSServerGroups.ServerGroupID = HB_tblOCSServerGroupMembers.ServerGroupID      
  WHERE  (HB_tblOCSServerGroups.ADDomainID = @ADDomainId) AND (HB_tblOCSServerGroups.IsEnabled = 1) AND (HB_tblOCSServerGroupMembers.IsEnabled = 1)       
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='5' and IsEnable='1')    
    
 UNION ALL    
    
 SELECT  HB_tblCRMServerGroupMembers.ServerIP IP, HB_tblCRMServerGroups.ServiceLogin, HB_tblCRMServerGroups.ServicePassword   ,  5 As SortOrder     
  FROM        HB_tblCRMServerGroups INNER JOIN      
  HB_tblCRMServerGroupMembers ON HB_tblCRMServerGroups.ServerGroupID = HB_tblCRMServerGroupMembers.ServerGroupID      
  WHERE     (HB_tblCRMServerGroups.ADDomainID = @ADDomainId) AND (HB_tblCRMServerGroups.IsEnabled = 1) AND (HB_tblCRMServerGroupMembers.IsEnabled = 1)      
 and Exists (Select IsEnable from HB_tblModules Where ModuleId='4' and IsEnable='1')    
    
  UNION ALL      
    
  SELECT  HB_tblSPServerGroupMembers.IP, HB_tblSPServerGroups.ServiceLogin, HB_tblSPServerGroups.ServicePassword   ,  6 As SortOrder     
  FROM  HB_tblSPServerGroups INNER JOIN      
  HB_tblSPServerGroupMembers ON HB_tblSPServerGroups.ServerGroupID = HB_tblSPServerGroupMembers.ServerGroupID      
  WHERE  (HB_tblSPServerGroups.ADDomainID = @ADDomainId) AND (HB_tblSPServerGroups.IsEnabled = 1) AND (HB_tblSPServerGroupMembers.IsEnabled = 1)      
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='1' and IsEnable='1')    
    
  UNION ALL      
    
  SELECT  S.IP, (SELECT ADLogin FROM HB_tblADirDomains Where ADDomainID=@ADDomainId), (SELECT ADPassword FROM HB_tblADirDomains Where ADDomainID=@ADDomainId), 7 As SortOrder     
  FROM  HB_tblWorkfolderServerGroup SG INNER JOIN      
  HB_tblWorkfolderServers S ON SG.ServerGroupID = S.ServerGroupID      
  WHERE  (SG.ADDomainID = @ADDomainId) AND (SG.IsEnabled = 1) AND (S.IsEnabled = 1)      
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='8' and IsEnable='1')    
    
  UNION ALL      
    
  SELECT  S.IP, (SELECT ADLogin FROM HB_tblADirDomains Where ADDomainID=@ADDomainId), (SELECT ADPassword FROM HB_tblADirDomains Where ADDomainID=@ADDomainId), 8 As SortOrder     
  FROM  Hb_tblRDS_ServerGroup SG INNER JOIN      
  Hb_tblRDS_Server S ON SG.RDServerGroupID = S.RDServerGroupID      
  WHERE  (SG.ADDomainID = @ADDomainId) AND (SG.[Enabled] = 1) AND (S.[Enabled] = 1)      
  and Exists (Select IsEnable from HB_tblModules Where ModuleId='9' and IsEnable='1')    
       
  UNION ALL      
    
  SELECT IP, LoginName, LoginPassword ,  7 As SortOrder from #RemoteServers      
    
  )      
    
 SELECT Distinct [IP] , SortOrder FROM Servers_CTE  Where [IP] != '' order by SortOrder ASc    
    
 DROP TABLE #RemoteServers      
    
END    
GO


Alter PROCEDURE CSP_GetRandomExchangeServerIdBasedOnLastAccessedDateStamp   
(  
 @SGMemberId INT  
)  
As  
BEGIN

Update HB_tblExchangeServerGroupsMembers Set ActiveConnections=1 Where IsEnabled='1' and ActiveConnections > 11
and DATEADD(minute,60, LastAccessDateStamp ) < getdate()


Update HB_tblExchangeServerGroupsMembers Set ActiveConnections=0 Where IsEnabled='1' and ActiveConnections > 0
and DATEADD(minute,120, LastAccessDateStamp ) < getdate()

 DECLARE @RandomSGMemberId INT;
 SET @RandomSGMemberId  = 
 (
  SELECT TOP (1) SGMemberId FROM HB_tblExchangeServerGroupsMembers   
  WHERE ServerGroupID = (SELECT ServerGroupId FROM HB_tblExchangeServerGroupsMembers WHERE SGMemberID=@SGMemberId )  
  AND IsEnabled='1' AND ActiveConnections < 11 
  And ConnectionLatency > 1
  ORDER BY ((ActiveConnections + 1) * ConnectionLatency) ASC, ActiveConnections ASC, LastAccessDateStamp ASC
 )
 
 IF @RandomSGMemberId IS NULL
 BEGIN
  SET @RandomSGMemberId  = 
  (
   SELECT TOP (1) SGMemberId FROM HB_tblExchangeServerGroupsMembers   
   WHERE ServerGroupID = (SELECT ServerGroupId FROM HB_tblExchangeServerGroupsMembers WHERE SGMemberID=@SGMemberId )  
   AND IsEnabled = '1' and ConnectionLatency > 1
   ORDER BY ((ActiveConnections + 1) * ConnectionLatency) ASC, ActiveConnections ASC, LastAccessDateStamp ASC
  )
 END

 SET @RandomSGMemberId = ISNULL(@RandomSGMemberId, @SGMemberId)

 UPDATE HB_tblExchangeServerGroupsMembers Set LastAccessDateStamp = GetDate() Where SGMemberID=@RandomSGMemberId  

 SELECT @RandomSGMemberId AS SGMemberId
END
GO

Alter Procedure CSP_GetALLADUsersNotMemberOfSGroup
(
 @SGroupId int,
 @HostedOrgId int
)
AS

Delete from Hb_tblADirSecurityGroupsMembers Where SGroupId=@SGroupId and IsDeletedManually = 0
and MemberType='user' and MemberLDAP not in (Select U.UserLDAPURL from HB_tblADirUsers U, HB_tblADirUsersDetail UD
Where HostedOrgID = @HostedOrgId and U.ADUserID = UD.ADUserID)

Select * from HB_tblADirUsers Where UserLDAPURL != '' and UserLDAPURL NOT IN (Select MemberLDAP from Hb_tblADirSecurityGroupsMembers where SGroupId = @SGroupId)
 and ADUserID in (Select ADUserId from HB_tblADirUsersDetail Where HostedOrgID = @HostedOrgId)
GO

 
Alter Procedure [SP_EM_Delete_ADUserFromDB]    
(    
 @ADUserId int  
)    
AS   
BEGIN
 Delete from Hb_tblADirSecurityGroupsMembers Where MemberType='user' and MemberLDAP = (Select UserLDAPURL from HB_tblADirUsers Where ADUserID=@ADUserId)
 Delete from Hb_tblCRMUsers where ADUserID = @ADUserId
 Delete from HB_tblExchangeMailboxes where ADUserID = @ADUserId
 Update Hb_tblO365Users set ADUserId = 0 where ADUserId = @ADUserId
 Delete from HB_tblSPUsers where ADUserID  = @ADUserId
 Delete from HB_tblLyncUsers where ADUserID = @ADUserId
 Delete from HB_tblADirUsersDetail where ADUserID = @ADUserId
 Delete from HB_tblADirUsers where ADUserID = @ADUserId
END
GO



IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'CSP_AddExchangeOrganizationOWAPolicy')
BEGIN
	DROP PROCEDURE CSP_AddExchangeOrganizationOWAPolicy
END
GO  

Create Procedure CSP_AddExchangeOrganizationOWAPolicy
(
@OrgId int,
@CustomerId int,
@ServerGroupID int
)
As

IF NOT EXISTS(Select * from HB_tblExchangeOrgOWAMailboxPolicy Where OrganizationID = @OrgId)
Begin

  INSERT INTO HB_tblExchangeOrgOWAMailboxPolicy( OrganizationID, SGOWAPolicyId, IsDefault, PolicyName, AllowOfflineOn, InstantMessagingType, IsPending, InstantMessagingEnabled, TextMessagingEnabled, ActiveSyncIntegrationEnabled, AllowCopyContactsToDeviceAddressBook, ContactsEnabled, 
                  AllAddressListsEnabled, JournalEnabled, NotesEnabled, RulesEnabled, RecoverDeletedItemsEnabled, ChangePasswordEnabled, JunkEmailEnabled, DirectFileAccessOnPrivateComputersEnabled, DirectFileAccessOnPublicComputersEnabled, CalendarEnabled, ThemeSelectionEnabled, 
                  PremiumClientEnabled, SignaturesEnabled, WeatherEnabled, PlacesEnabled, LocalEventsEnabled, InterestingCalendarsEnabled, RemindersAndNotificationsEnabled, SpellCheckerEnabled, SearchFoldersEnabled, UMIntegrationEnabled, PublicFoldersEnabled, TasksEnabled, 
                  UNCAccessOnPrivateComputersEnabled, UNCAccessOnPublicComputersEnabled, WSSAccessOnPrivateComputersEnabled, WSSAccessOnPublicComputersEnabled, OldPolicyName)  
  SELECT @OrgId, SGOWAPolicyId, IsDefault , PolicyName,
  AllowOfflineOn, InstantMessagingType, IsPending, InstantMessagingEnabled, TextMessagingEnabled, ActiveSyncIntegrationEnabled, AllowCopyContactsToDeviceAddressBook, ContactsEnabled, 
                  AllAddressListsEnabled, JournalEnabled, NotesEnabled, RulesEnabled, RecoverDeletedItemsEnabled, ChangePasswordEnabled, JunkEmailEnabled, DirectFileAccessOnPrivateComputersEnabled, DirectFileAccessOnPublicComputersEnabled, CalendarEnabled, ThemeSelectionEnabled, 
                  PremiumClientEnabled, SignaturesEnabled, WeatherEnabled, PlacesEnabled, LocalEventsEnabled, InterestingCalendarsEnabled, RemindersAndNotificationsEnabled, SpellCheckerEnabled, SearchFoldersEnabled, UMIntegrationEnabled, PublicFoldersEnabled, TasksEnabled, 
                  UNCAccessOnPrivateComputersEnabled, UNCAccessOnPublicComputersEnabled, WSSAccessOnPrivateComputersEnabled, WSSAccessOnPublicComputersEnabled, ''
  from HB_tblExchangeSGOWAMailboxPolicy  
  Where ServerGroupId = @ServerGroupID and   
  (AssignedToCustomerIds = ',-1,' OR AssignedToCustomerIds LIKE '%,' + convert(nvarchar(10),@CustomerId) + ',%')  
End 

  IF NOT EXISTS(Select * from HB_tblExchangeOrgOWAMailboxPolicy Where OrganizationID = @OrgId)  
  BEGIN  
      INSERT INTO HB_tblExchangeOrgOWAMailboxPolicy( OrganizationID, SGOWAPolicyId, IsDefault, PolicyName, AllowOfflineOn, InstantMessagingType, IsPending, InstantMessagingEnabled, TextMessagingEnabled, ActiveSyncIntegrationEnabled, AllowCopyContactsToDeviceAddressBook, ContactsEnabled, 
                  AllAddressListsEnabled, JournalEnabled, NotesEnabled, RulesEnabled, RecoverDeletedItemsEnabled, ChangePasswordEnabled, JunkEmailEnabled, DirectFileAccessOnPrivateComputersEnabled, DirectFileAccessOnPublicComputersEnabled, CalendarEnabled, ThemeSelectionEnabled, 
                  PremiumClientEnabled, SignaturesEnabled, WeatherEnabled, PlacesEnabled, LocalEventsEnabled, InterestingCalendarsEnabled, RemindersAndNotificationsEnabled, SpellCheckerEnabled, SearchFoldersEnabled, UMIntegrationEnabled, PublicFoldersEnabled, TasksEnabled, 
                  UNCAccessOnPrivateComputersEnabled, UNCAccessOnPublicComputersEnabled, WSSAccessOnPrivateComputersEnabled, WSSAccessOnPublicComputersEnabled, OldPolicyName)  
  SELECT @OrgId, SGOWAPolicyId, IsDefault , PolicyName,
  AllowOfflineOn, InstantMessagingType, IsPending, InstantMessagingEnabled, TextMessagingEnabled, ActiveSyncIntegrationEnabled, AllowCopyContactsToDeviceAddressBook, ContactsEnabled, 
                  AllAddressListsEnabled, JournalEnabled, NotesEnabled, RulesEnabled, RecoverDeletedItemsEnabled, ChangePasswordEnabled, JunkEmailEnabled, DirectFileAccessOnPrivateComputersEnabled, DirectFileAccessOnPublicComputersEnabled, CalendarEnabled, ThemeSelectionEnabled, 
                  PremiumClientEnabled, SignaturesEnabled, WeatherEnabled, PlacesEnabled, LocalEventsEnabled, InterestingCalendarsEnabled, RemindersAndNotificationsEnabled, SpellCheckerEnabled, SearchFoldersEnabled, UMIntegrationEnabled, PublicFoldersEnabled, TasksEnabled, 
                  UNCAccessOnPrivateComputersEnabled, UNCAccessOnPublicComputersEnabled, WSSAccessOnPrivateComputersEnabled, WSSAccessOnPublicComputersEnabled, ''
  from HB_tblExchangeSGOWAMailboxPolicy  
    Where ServerGroupId = @ServerGroupID and (IsDefault = '1'  or PolicyName like 'Default Basic SPLA OWA Mailbox Policy For SG%')
  END  
  
  Update HB_tblExchangeOrgOWAMailboxPolicy SET IsDefault = '1' Where OrganizationID = @OrgId   
   and OrgOWAPolicyId = (Select Min(OrgOWAPolicyId) from HB_tblExchangeOrgOWAMailboxPolicy Where OrganizationID = @OrgId)  
   and Not EXISTS(Select * from HB_tblExchangeOrgOWAMailboxPolicy Where OrganizationID = @OrgId and IsDefault = '1')  
   

GO

 
Alter PROCEDURE SP_EM_AddExchangeOrganization   
(  
@ExchHostingID_1  [int], @OrganizationName_2  [nvarchar](255), @OrganizationLDAP_3  [nvarchar](255), @Address1_4  [nvarchar](50),  
@Address2_5  [nvarchar](50), @City_6  [nvarchar](50), @State_7  [nvarchar](50), @Zip_8  [nvarchar](50), @Country_9  [nvarchar](50),  
@Status_10  [int], @Description_11  [nvarchar](500), @OwningOrganizationID_12  [int], @OrgInternalName [nvarchar](200),   
@HostedOrgID int, @ADDomainId int, @DomainName [nvarchar](255), @ALName nvarchar(500), @GALName nvarchar(500),  
@OABName nvarchar(500), @RALName nvarchar(500), @ABPName nvarchar(500), @OrgGUID nvarchar(100)    
)   
AS   
BEGIN
 INSERT INTO [HB_tblExchangeOrganizations]([ExchHostingID], [OrganizationName], [OrganizationLDAP], [Address1], [Address2],  
  [City], [State], [Zip], [Country], [Status], [Description], [OwningOrganizationID], [OrgInternalName],  
  ALName, GALName, OABName, RALName, ABPName)   
 VALUES(@ExchHostingID_1, @OrganizationName_2, @OrganizationLDAP_3, @Address1_4, @Address2_5,  
  @City_6, @State_7, @Zip_8, @Country_9, @Status_10, @Description_11, @OwningOrganizationID_12, @OrgInternalName,  
  @AlName, @GALName, @OABName, @RALName, @ABPName)   
  
 Declare @OrgId int   
 Set @OrgId=(Select SCOPE_IDENTITY())      
  
 declare @isResellerPackage bit   
 set @isResellerPackage = (select ResellerPackage from Hb_tblPackages, Hb_tblPackagesSold, Hb_tblExchangeHosting   
  where Hb_tblExchangeHosting.ExchHostingId = @ExchHostingID_1 and Hb_tblPackagesSold.SoldPackageId = Hb_tblExchangeHosting.SoldPackageId   
  and Hb_tblPackages.PackageId = Hb_tblPackagesSold.PackageId)  
  
 IF @isResellerPackage = 0   
 BEGIN      
   
  Declare @ServerGroupID int  
  Declare @CustomerId int  
   
  Select @CustomerId = H.CustomerID from HB_tblExchangeHosting H Where H.ExchHostingID = @ExchHostingID_1   
  
   Select @ServerGroupId = ServerGroupID from HB_tblExchangePackagesToServerGroup where PackageID = (  
   Select PackageID from HB_tblPackagesSold where SoldPackageID=(Select SoldPackageID from HB_tblExchangeHosting where ExchHostingID=@ExchHostingID_1))  
  
  Exec CSP_AddExchangeOrganizationOWAPolicy @OrgId, @CustomerId, @ServerGroupID

  If @HostedOrgId!='0'  
  Begin  
    Update HB_tblExchangeOrganizations Set HostedOrgID=@HostedOrgID Where OrganizationID=@OrgId  
    Exec SP_EM_Insert_HB_tblADirHostedOrganizationsDetail @HostedOrgID,'1'     
  End  
  Else  
  Begin  
    Exec SP_EM_Insert_HB_tblADirHostedOrganizations @CustomerID, @ADDomainId,@OrganizationName_2, @OrgInternalName,@OrganizationLDAP_3 ,@OrgGUID      
    
    Set @HostedOrgId=(Select top(1) HostedOrgId FRom [HB_tblADirHostedOrganizations] Where AdDOmainId= @ADDomainId    
   and CustomerId= @CustomerID and OrganizationName=@OrganizationName_2 order by HostedOrgId desc)  
  
    Exec SP_EM_Insert_HB_tblADirHostedOrganizationsDetail @HostedOrgId,'1'  
  
    Update HB_tblExchangeOrganizations Set HostedOrgID=@HostedOrgID Where OrganizationID=@OrgId  
    update hb_tblAdirHostedOrganizations set orgSuffixes = @DomainName where HostedOrgID=@HostedOrgId   
   
  UPDATE O 
  SET 
  O.MustChangePassword = D.UserMustChangePassword, 
  O.CannotChangePassword = D.UserCantChangePassword, 
  O.PasswordNeverExpires = D.PasswordNeverExpires  
  FROM HB_tblADirHostedOrganizations  O INNER JOIN [HB_tblADirDomains] D 
  ON O.ADDomainId = D.ADDomainID AND D.ADDomainID = @ADDomainId
  WHERE 
  O.HostedOrgID = @HostedOrgID
  End  
 END 
 
 SELECT @OrgId as RID 
END
GO

Alter PROCEDURE SPR_GetExchangeOrgOWAMailboxPolicy 
(
@OrgOWAPolicyId BIGINT, 
@OrganizationID INT, 
@PolicyName     NVARCHAR(300), 
@IsDefault      INT 
) 
AS

If not Exists (Select 1 from HB_tblExchangeOrgOWAMailboxPolicy Where OrganizationID = @OrganizationID and @OrganizationID > 0 and IsDefault='1')
Begin
	Declare @CustomerId int
	Declare @ServerGroupId int

	Set @CustomerId = (Select CustomerId from HB_tblADirHostedOrganizations Where HostedOrgID = (Select HostedOrgID from HB_tblExchangeOrganizations Where OrganizationID=@OrganizationID))

	Set @ServerGroupId  = (Select ServerGroupId from HB_tblExchangeServerGroupsMembers Where SGMemberID = (select SGMemberID from HB_tblExchangeHosting 
	where ExchHostingID = (Select ExchHostingID from HB_tblExchangeOrganizations where OrganizationID=@OrganizationID)))
	
	Exec CSP_AddExchangeOrganizationOWAPolicy @OrganizationID, @CustomerId, @ServerGroupID

End 

SELECT P.*,  
 (CASE WHEN EXISTS(SELECT M.MailboxId FROM HB_tblExchangeMailboxes M INNER JOIN HB_tblExchangeMailboxDetail D ON M.MailboxId = D.MailboxID
  WHERE D.OWAMailboxPolicyName = P.PolicyName AND P.OrganizationID = M.OrganizationID    )           
  THEN 1 ELSE 0 END) AS InUse    
FROM HB_tblExchangeOrgOWAMailboxPolicy P
WHERE (@PolicyName = '' OR P.PolicyName LIKE '%' + @PolicyName + '%')
AND (@OrgOWAPolicyId <= 0 OR OrgOWAPolicyId = @OrgOWAPolicyId) AND (@OrganizationID <= 0 OR P.OrganizationID = @OrganizationID)         
AND (@IsDefault < 0 OR P.IsDefault = @IsDefault)
Order By P.PolicyName ASC
GO


Alter PROCEDURE CSP_UpdateMailBoxTemplateSettingForBasicType
AS
UPDATE Hb_tblExchangeMailBoxTemplates
SET OWAActiveSyncIntegrationEnabled = 0,
    OWAPublicFolderEnabled = 0
WHERE SPLAType = 1
and (OWAActiveSyncIntegrationEnabled = 1 or OWAPublicFolderEnabled = 1);

UPDATE d
SET d.OWAActiveSync = 0,
    d.OWAPublicFolder = 0
FROM HB_tblExchangeMailboxDetail d
INNER JOIN HB_tblExchangeMailboxes m
    ON m.MailboxID = d.MailboxID
INNER JOIN Hb_tblExchangeMailBoxTemplates t
    ON m.MailBoxTemplateId = t.MailBoxTemplateId
WHERE t.SPLAType = 1
and (d.OWAActiveSync = 1 or d.OWAPublicFolder = 1);
GO

Alter PROCEDURE CSP_UpdateOwaPolicyNameToApplyDefaultOWAPolicy
AS
UPDATE d
SET d.OWAMailboxPolicyName=''
FROM HB_tblExchangeMailboxDetail d
INNER JOIN HB_tblExchangeMailboxes m ON m.MailboxID = d.MailboxID
INNER JOIN Hb_tblExchangeMailBoxTemplates t ON m.MailBoxTemplateId = t.MailBoxTemplateId
INNER JOIN HB_tblExchangeOrgOWAMailboxPolicy p ON p.OrganizationID = m.OrganizationID 
INNER JOIN HB_tblExchangeOrganizations o On O.OrganizationID = m.OrganizationID 
INNER JOIN HB_tblExchangeHosting e ON e.ExchHostingID = o.ExchHostingID 
WHERE t.SPLAType IN (2,3,4,5) 
and d.OWAMailboxPolicyName = p.PolicyName AND  d.OWAMailboxPolicyName LIKE 'Default Basic SPLA OWA Mailbox Policy For SG%'
And m.[Status] != '1' and m.UserLDAPUrl != ''
GO


IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'CSP_GETAPIExchangeJobsOfaMailBoxByActionType')
BEGIN
	DROP PROCEDURE CSP_GETAPIExchangeJobsOfaMailBoxByActionType
END
GO 
CREATE PROCEDURE CSP_GETAPIExchangeJobsOfaMailBoxByActionType
@ActionType INT,
@Mailbox NVARCHAR(200)
AS
;WITH Split AS
(
    SELECT j.JobId,
           s.Item,
           ROW_NUMBER() OVER (PARTITION BY j.JobId ORDER BY (SELECT NULL)) AS rn
    FROM HB_tblExchangeGroupJobs j
    CROSS APPLY MachSplit(j.Parameters, N'×') s
    WHERE j.AddedBy = 'API Call'
      AND j.StatusId != 1
      AND j.ActionType = @ActionType
)
SELECT JobId,
       LTRIM(RTRIM(Item)) AS Mailbox
FROM Split
WHERE rn > 1  
ORDER BY JobId, rn;
GO


  
Alter PROCEDURE SPR_GetNumberOfJobsProcessedForOrgInLastMinute         
(                  
 @ServiceType INT ,        
 @OrgId int                 
)                  
AS                  
BEGIN            

 IF @ServiceType = 3        
 BEGIN        
  SELECT 0 As JobsCount        
 END        
 ELSE        
 BEGIN           
  DECLARE @JobTable NVARCHAR(500);          
  DECLARE @OrgPartitionColName NVARCHAR(200);            
  DECLARE @SQL NVARCHAR(MAX);                  
       
 SET @OrgPartitionColName = CASE @ServiceType WHEN 1 THEN 'HostingId' WHEN  3 THEN 'HostedOrgId' WHEN 21 THEN 'TenantId' WHEN 24 THEN 'SIPHostingId' ELSE 'OrganizationId' END                
          
  SET @JobTable = CASE @ServiceType                   
  WHEN 16 THEN 'HB_tblExchangeGroupJobs'                  
  WHEN 14 THEN 'HB_tblCRMGroupJobs'                  
  WHEN 17 THEN 'HB_tblSharePointGroupJobs'                  
  WHEN 18 THEN 'HB_tblLyncGroupJobs'                  
  WHEN 3 THEN 'HB_tblADirUserGroupJobs'                  
  WHEN 19 THEN 'HB_tblVPSGroupJobs'                  
  WHEN 21 THEN 'HB_tblO365Jobs'            
  WHEN 24 THEN 'HB_tblSIPGroupJobs'  
  When 1 THEN 'HB_tblGeneralGroupJobs'  
  ELSE 'UnHandledExtension'                   
  END               
  SET @SQL = ' Select Count(*) As JobsCount From ' + @JobTable         
  + ' Where ' + cast(@OrgPartitionColName as nvarchar(100)) + ' = ' + cast(@OrgId as nvarchar(10))        
        
  If (@JobTable = 'HB_tblO365Jobs')        
  Begin        
   Set @SQL = @SQL + ' AND (InProcess=''1'')'          
  End        
  Else        
  Begin        
   Set @SQL = @SQL + ' AND ((LastTryTimeStamp > dateadd(Second,10,getdate()) and TriesCount > 0) Or InProcess=''1'')'          
  End        
  Exec(@SQL)          
 END       
 
 

END 
GO

ALTER PROCEDURE SP_EM_SearchOrgUser  
(  
 @HostedOrgId int,                      
 @ADDomainId  INT,                                    
 @OrgName   nvarchar(255),                               
 @CompanyId INT,                                   
 @DisplayName NVARCHAR(255),                              
 @CustomerId INT,                              
 @CustomerName NVARCHAR(200),                            
 @CustomerCompanyName NVARCHAR(128),                          
 @UserPrincipalName NVARCHAR(255),                         
 @DomainName NVARCHAR(255),                                    
 @PageNo   INT,                                    
 @PageSize  INT,                                    
 @SortExpression NVARCHAR(1000),                                    
 @SortDirection VARCHAR(10) ,                      
 @SubsFilterOption int ,                      
 @SAMAccountName nvarchar(200),                      
 @Address  nvarchar(1000),                      
 @City   nvarchar(200),                        
 @State   nvarchar(200),                       
 @Zip   nvarchar(50),                      
 @CountryId  int,                      
 @Phone   nvarchar(50),                      
 @JobTitle  nvarchar(200),                      
 @Company  nvarchar(200),                      
 @Department nvarchar(200),                      
 @Office  nvarchar(1000),                      
 @SubResellerId INT,                      
 @ADSyncEnabled bit,                      
 @PackageTypeId int,                  
 @Locked BIGINT,    
 @StatusId int  
)  
AS                
BEGIN                
   
 Update HB_tblADirUsers SET HOrgId = ISNULL((Select TOP 1 HostedOrgId from HB_tblADirUsersDetail AD Where AD.ADUserID = HB_tblADirUsers.ADUserID), 0)    
 Where HOrgId = 0    
   
 EXEC CSP_UpdateHostedOrgExtendedData  
 EXEC CSP_UpdateHostedUserExtendedData  
  
  EXEC SPR_HandleEscapeCharsAndApostrophe @Text = @CustomerName OUTPUT           
              
   IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[#temp_UserData]') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)                                    
   DROP TABLE #temp_UserData                             
                              
   
   CREATE TABLE #temp_UserData                                    
   (                                     
    ID     INT IDENTITY (1,1) ,                
    ADUserId   INT,                                   
    ADUserName   NVARCHAR(500),                                    
    LogonName   NVARCHAR(500),                                    
    DisplayName   NVARCHAR(500),                                    
    [Password]   NVARCHAR(500),                                    
    UserLDAP   NVARCHAR(4000),                                    
    ADDomainId   INT,                                      
    ADDomainName  NVARCHAR(500),                                    
    HostedOrgId   INT,                                    
    OrganizationName NVARCHAR(300),                                    
    OrganizationLDAP NVARCHAR(4000),                                    
    CustomerId   INT,                                    
    CompanyId INT,                      
    CustDisplayName  NVARCHAR(200),                        
    CustCompanyName  NVARCHAR(200),                                        
    MaxID    INT DEFAULT (0),                                    
    LoginDisable  BIT DEFAULT (0),                              
    UPName nvarchar (500) ,                    
    UserDisabled bit,                                 
    OrgGUID nvarchar(100),                      
    OnPremisesUserSID nvarchar(100),                      
    PackageTypeId int,                  
    Locked bit,    
    SyncEnabled bit   ,                        
    [Status]     int, 
    LogonHoursTemplate nvarchar(4000),                      
    ADServiceData nvarchar(max) default(''),                      
    EXServiceData nvarchar(max) default(''),                      
    SPServiceData nvarchar(max) default(''),                      
    CRMServiceData nvarchar(max) default(''),                      
    OCSServiceData nvarchar(max) default(''),                      
  CSPServiceData nvarchar(max) default(''),                      
    WFHServiceData nvarchar(max) default(''),                      
    RDSServiceData nvarchar(max) default(''),    
    AlternateEmail nvarchar(max) default(''),  
 MailUserId NVARCHAR(50) default('')   
   )                       
                       
   If (ISNUMERIC(@OrgName) = '1')                      
   Begin                      
   Set @OrgName= IsNull((Select OrganizationName from HB_tblADirHostedOrganizations Where HostedOrgId = @OrgName), @OrgName)                       
   End    
    
 Declare @SortExp nvarchar(200)            
 Set @SortExp = @SortExpression + ' ' + @SortDirection    
    
 Declare @Where nvarchar(max)            
 SET @Where = ' FROM HB_tblADirUsers ADU, HB_tblADirDomains AD, HB_tblADirHostedOrganizations HO, tblCustomers C, HB_tblADirHostedUserExtendedData ED     
  WHERE ADU.ADDomainID = AD.ADDomainID AND  ADU.HOrgId = HO.HostedOrgID  AND  ADU.CustomerID = C.CustomerID and ED.ADUserId = ADU.ADUserId    
  '    
     
 If(@HostedOrgID > 0)    
 SET @Where = @Where + ' And ADU.HOrgId =  ' + CONVERT(NVARCHAR(10), @HostedOrgID)    
    
 If(@ADSyncEnabled = '1')    
 SET @Where = @Where + ' And ADU.OnPremisesUserSID != '''' '    
    
 If(@CustomerId > 0)    
 SET @Where = @Where + ' And ADU.CustomerID = ' + CONVERT(NVARCHAR(10), @CustomerId)    
    
 If(@CompanyID > 0)    
 SET @Where = @Where + ' And C.CompanyID = ' + CONVERT(NVARCHAR(10), @CompanyID)    
    
 If(@ADDomainId > 0)    
 SET @Where = @Where + ' And HO.ADDomainId = ' + CONVERT(NVARCHAR(10), @ADDomainId)    
    
 If(@OrgName != '')    
 BEGIN    
 SET @OrgName = Replace(@OrgName, '[','[[]')    
 SET @Where = @Where + ' and (HO.OrganizationName like ''%''+N'''+ replace(@OrgName,'''','''''')+'''+''%'' OR HO.OrganizationInternalName like ''%''+N'''+ replace(@OrgName,'''','''''')+'''+''%''    
  OR HO.OrgGUID like ''%''+N'''+ replace(@OrgName,'''','''''')+'''+''%'' ) '                  
 END    
    
 If(@DisplayName != '')    
 SET @Where = @Where + ' And ADU.DisplayName like ''%''+N'''+ replace(@DisplayName,'''','''''')+'''+''%'' '    
    
 If(@CustomerName != '')    
 SET @Where = @Where + ' And (C.FirstName+'' ''+C.LastName) LIKE ''%''+N'''+ replace(@CustomerName,'''','''''')+'''+''%'' '    
    
 If(@CustomerCompanyName != '')    
 SET @Where = @Where + ' And C.CompanyName like ''%''+N'''+ replace(@CustomerCompanyName,'''','''''')+'''+''%'' '    
    
 If(@UserPrincipalName != '')    
 SET @Where = @Where + ' And (ADU.UPName like ''%''+N'''+ replace(@UserPrincipalName,'''','''''')+'''+''%''     
  OR ADU.AlternateEmail like ''%''+N'''+ replace(@UserPrincipalName,'''','''''')+'''+''%''    
  OR ED.AlternateEmail LIKE ''%''+N'''+ replace(@UserPrincipalName,'''','''''')+'''+''%'' ) '    
    
 If(@DomainName != '')    
 SET @Where = @Where + ' And ADU.UPName like ''%''+N'''+ replace(@DomainName,'''','''''')+'''+''%'' '    
    
 If(@SAMAccountName != '')    
 SET @Where = @Where + ' And ADU.DomainUserName like ''%''+N'''+ replace(@SAMAccountName,'''','''''')+'''+''%'' '    
    
 If(@Address != '')    
 SET @Where = @Where + ' And ADU.[Address] like ''%''+N'''+ replace(@Address,'''','''''')+'''+''%'' '    
    
 If(@City != '')    
 SET @Where = @Where + ' And ADU.City like ''%''+N'''+ replace(@City,'''','''''')+'''+''%'' '    
    
 If(@State != '')    
 SET @Where = @Where + ' And ADU.[State] like ''%''+N'''+ replace(@State,'''','''''')+'''+''%'' '    
    
 If(@Zip != '')    
 SET @Where = @Where + ' And ADU.Zip like ''%''+N'''+ replace(@Zip,'''','''''')+'''+''%'' '    
    
 If(@CountryId > 0)    
 SET @Where = @Where + ' And ADU.Country = ' + CONVERT(NVARCHAR(10), @CountryId)    
    
 If(@Phone != '')    
 SET @Where = @Where + ' And (ADU.HomePhone like ''%''+N'''+ replace(@Phone,'''','''''')+'''+''%'' OR ADU.BusinessPhone like ''%''+N'''+ replace(@Phone,'''','''''')+'''+''%''     
  OR ADU.MobilePhone like ''%''+N'''+ replace(@Phone,'''','''''')+'''+''%'') '    
    
 If(@JobTitle != '')    
 SET @Where = @Where + ' And ADU.JobTitle like ''%''+N'''+ replace(@JobTitle,'''','''''')+'''+''%'' '    
    
 If(@Company != '')    
 SET @Where = @Where + ' And ADU.Company like ''%''+N'''+ replace(@Company,'''','''''')+'''+''%'' '    
    
 If(@Department != '')    
 SET @Where = @Where + ' And ADU.Department like ''%''+N'''+ replace(@Department,'''','''''')+'''+''%'' '    
    
 If(@Office != '')    
 SET @Where = @Where + ' And ADU.Office like ''%''+N'''+ replace(@Office,'''','''''')+'''+''%'' '    
    
 If(@SubResellerId > 0)    
 SET @Where = @Where + ' And C.SubResellerId = ' + CONVERT(NVARCHAR(10), @SubResellerId)    
    
 If(@Locked >= 0)    
 SET @Where = @Where + ' And ADU.Locked = ' + CONVERT(NVARCHAR(10), @Locked)    
    
 If(@StatusId > 0)    
 BEGIN    
  if(@StatusId = 1)    
  SET @Where = @Where + ' And ADU.UserDisabled = 0 '    
  else if(@StatusId = 2)    
  SET @Where = @Where + ' And ADU.UserDisabled = 1 '    
  else if(@StatusId = 3)    
  SET @Where = @Where + ' And ADU.LoginDisable = 1 '    
  else if(@StatusId = 4)    
  SET @Where = @Where + ' And ADU.LoginDisable = 0 '    
  else if(@StatusId = 5)    
  SET @Where = @Where + ' And EXISTS(SELECT TOP 1 HB_tblADirUserGroupJobs.ADUserId FROM HB_tblADirUserGroupJobs WHERE StatusId     
  NOT IN (2,3) AND HB_tblADirUserGroupJobs.ADUserId = ADU.ADUserID) '    
 END    
    
 DECLARE @SQL NVARCHAR(max)     
    
 
 SET @SQL = 'INSERT INTO #temp_UserData (     
   ADUserId, ADUserName, DisplayName, LogonName, [Password], UserLDAP, ADDomainId, ADDomainName, HostedOrgId, OrganizationName,     
   OrganizationLDAP, CustomerId, CustDisplayName, CustCompanyName, LoginDisable, UPName,     
   UserDisabled,  CompanyId, OrgGUID, OnPremisesUserSID,PackageTypeId,Locked, SyncEnabled, [Status], LogonHoursTemplate, ADServiceData,    
   EXServiceData, SPServiceData, CRMServiceData, OCSServiceData, CSPServiceData, WFHServiceData, RDSServiceData, AlternateEmail      
   )     
   SELECT DISTINCT     
  ADU.ADUserID, ADU.DomainUserName, ADU.DisplayName, ADU.DomainUserName, ADU.[Password], ADU.UserLDAPURL, ADU.ADDomainID, AD.ADDomainName,     
  ADU.HOrgId, HO.OrganizationName, HO.OrganizationLDAP, ADU.CustomerID,     
  CAST(C.CustomerID AS VARCHAR) + '' - '' + C.FirstName + '' '' + C.LastName, C.CompanyName, ADU.LoginDisable, ADU.UPName,     
  ADU.UserDisabled, C.CompanyId, HO.OrgGUID,  OnPremisesUserSID, ' + convert(nvarchar(10), @PackageTypeId) + ' AS PackageTypeId, Locked, ''0'', ''0'',    
  LogonHoursTemplate, ADServiceData, EXServiceData, SPServiceData, CRMServiceData, OCSServiceData, CSPServiceData, WFHServiceData, RDSServiceData, ED.AlternateEmail     
  ' + @Where + ' ORDER BY ' + @SortExp     
      
    
 EXEC sp_executesql @SQL, N'     
  @HostedOrgId int,    
  @ADDomainId INT,    
  @OrgName nvarchar(255),     
  @CompanyId INT,     
  @DisplayName NVARCHAR(255),    
  @CustomerId INT,    
  @CustomerName NVARCHAR(200),    
  @CustomerCompanyName NVARCHAR(128),    
  @UserPrincipalName NVARCHAR(255),     
  @DomainName NVARCHAR(255),    
  @PageNo INT,    
  @PageSize INT,    
  @SortExpression NVARCHAR(1000),    
  @SortDirection VARCHAR(10),    
  @SubsFilterOption int,    
  @SAMAccountName nvarchar(200),    
  @Address nvarchar(1000),    
  @City nvarchar(200),    
  @State nvarchar(200),     
  @Zip nvarchar(50),    
  @CountryId int,    
  @Phone nvarchar(50),    
  @JobTitle nvarchar(200),    
  @Company nvarchar(200),    
  @Department nvarchar(200),    
  @Office nvarchar(1000),    
  @SubResellerId INT,    
  @ADSyncEnabled bit,    
  @PackageTypeId int,    
  @Locked BIGINT,    
  @StatusId int',     
  @HostedOrgId,    
 @ADDomainId,    
 @OrgName,    
@CompanyId,    
 @DisplayName,    
 @CustomerId,    
 @CustomerName,    
 @CustomerCompanyName,    
 @UserPrincipalName,    
 @DomainName,    
 @PageNo,    
 @PageSize,    
 @SortExpression,    
 @SortDirection,    
 @SubsFilterOption,    
 @SAMAccountName,    
 @Address,    
 @City,    
 @State,    
 @Zip,    
 @CountryId,    
 @Phone,    
 @JobTitle,    
 @Company,    
 @Department,    
 @Office,    
 @SubResellerId,    
 @ADSyncEnabled,    
 @PackageTypeId,    
 @Locked,    
 @StatusId    
    
 Update #temp_UserData Set SyncEnabled ='1'     
 Where Exists (Select ADUserId from HB_tblADirUsers Where ADUserId=#temp_UserData.ADUserId and OnPremisesUserSID!='')    
    
 Update #temp_UserData Set [Status] = '1' Where UserLDAP != ''    
 Update #temp_UserData Set [Status] = '1' Where Status='0' and not Exists (SELECT TOP 1 HB_tblADirUserGroupJobs.ADUserId FROM HB_tblADirUserGroupJobs     
 WHERE StatusId in (0,1) AND HB_tblADirUserGroupJobs.ADUserId = #temp_UserData.ADUserID)    
    
 If(@SubsFilterOption != 0)     
 Begin            
  Delete from #temp_UserData where ID not in    
  (Select ID from #temp_UserData Where (@SubsFilterOption = 1 AND EXServiceData = '' AND OCSServiceData = '' AND SPServiceData = ''     
  AND CRMServiceData = '' AND CSPServiceData = '' and RDSServiceData = '' and WFHServiceData = '')         
    OR (@SubsFilterOption = 2 AND (EXServiceData != '' OR OCSServiceData != '' OR SPServiceData != '' OR CRMServiceData != '' OR CSPServiceData != '' OR RDSServiceData != ''     
    OR WFHServiceData != ''))                      
    OR (@SubsFilterOption = 3 AND EXServiceData != '')                      
    OR (@SubsFilterOption = 4 AND OCSServiceData != '')                      
    OR (@SubsFilterOption = 5 AND SPServiceData != '')                      
    OR (@SubsFilterOption = 6 AND CRMServiceData != '')                      
    OR (@SubsFilterOption = 7 AND CSPServiceData != '')                      
    OR (@SubsFilterOption = 8 AND ADServiceData != '')                      
    OR (@SubsFilterOption = 9 AND RDSServiceData != '')                      
    OR (@SubsFilterOption = 10 AND WFHServiceData != '')                      
    )                      
 End     
  
 UPDATE #temp_UserData  
 SET #temp_UserData.MailUserId = CONVERT(NVARCHAR, MU.MailUserId) + '^' + CONVERT(NVARCHAR, MU.OrganizationId)  
 FROM #temp_UserData TUS, Hb_tblExchangeMailUsers MU  
 JOIN HB_tblADirUsers ADU  
 ON MU.ADUserId = ADU.ADUserID  
 WHERE MU.ADUserId = TUS.ADUserId;  
  
 UPDATE #temp_UserData WITH (ROWLOCK) SET #temp_UserData.MaxID = (SELECT Count(ID) FROM #temp_UserData)     
                                     
 DECLARE @Offset  INT                                    
                                    
 IF (@PageNo > 0)                                    
 BEGIN                                    
  SET @Offset = (@PageNo-1) * @PageSize;                                    
 END                                    
                                    
 SET @SQL = ''    
                      
 If (@PageSize !='-1')                      
 Begin                    
  SET @SQL =  'SELECT TOP '+ CONVERT(VARCHAR, @PageSize) +' * FROM #temp_UserData WHERE ID NOT IN (SELECT ID FROM ' +                
   '(SELECT TOP '+ CONVERT(VARCHAR, ISNULL(@Offset, 0)) +' ID FROM #temp_UserData ORDER BY '+@SortExpression+' '+@SortDirection + ' ) as Tbl1) '        
      
 End                      
 Else                      
 begin                      
  SET @SQL =  ' SELECT  *  FROM #temp_UserData'    
 End                  
    
   SET @SQL = @SQL + ' ORDER BY '+@SortExpression+' '+@SortDirection        
                    
 EXEC (@SQL)           
                             
 DROP TABLE #temp_UserData                      
    
END   
GO


Alter PROCEDURE CSP_GETMailboxesToApplyBasicOWAPolicy
AS
SELECT m.MailboxId,e.SGMemberID FROM HB_tblExchangeMailboxDetail d
INNER JOIN HB_tblExchangeMailboxes m ON m.MailboxID = d.MailboxID
INNER JOIN Hb_tblExchangeMailBoxTemplates t ON m.MailBoxTemplateId = t.MailBoxTemplateId
INNER JOIN HB_tblExchangeOrganizations o On O.OrganizationID = m.OrganizationID 
INNER JOIN HB_tblExchangeHosting e ON e.ExchHostingID = o.ExchHostingID 
WHERE t.SPLAType = 1 
And m.[Status] != '1' and m.UserLDAPUrl != ''
and d.OWAMailboxPolicyName not like 'Default Basic SPLA OWA Mailbox Policy For SG%'
Union
SELECT m.MailboxId,e.SGMemberID FROM HB_tblExchangeMailboxDetail d
INNER JOIN HB_tblExchangeMailboxes m ON m.MailboxID = d.MailboxID
INNER JOIN HB_tblExchangeOrganizations o On O.OrganizationID = m.OrganizationID 
INNER JOIN HB_tblExchangeHosting e ON e.ExchHostingID = o.ExchHostingID 
WHERE m.[Status] != '1' and m.UserLDAPUrl != ''
and d.OWAMailboxPolicyName not like 'Default Basic SPLA OWA Mailbox Policy For SG%'
And (d.EnterpriseTemplate = '0' 
AND d.UMEnabled = '0' 
AND d.JournalingMailboxId = '0'        
AND d.ArchiveDBName = '' 
AND d.MAPIAccess = '0'          
AND d.EWSAccess = '0' 
AND d.OWANonCachedMode = '0'         
AND d.ExchangeActivesync = '0'          
AND d.OutLookCAL = '0'       
AND d.InPlaceHold = '0'          
AND d.LitigationHoldEnabled = '0')
GO


Alter PROCEDURE CSP_GETMailboxesToApplyBasicOWAPolicy
AS

Update HB_tblExchangeMailboxDetail Set OWAPublicFolder='0', OWAActiveSync='0', OWAUMIntegration='0'
where OWAMailboxPolicyName like 'Default Basic SPLA OWA Mailbox Policy For SG%'
and (OWAPublicFolder='1' or OWAActiveSync='1' or OWAUMIntegration='1')

SELECT m.MailboxId,e.SGMemberID FROM HB_tblExchangeMailboxDetail d
INNER JOIN HB_tblExchangeMailboxes m ON m.MailboxID = d.MailboxID
INNER JOIN Hb_tblExchangeMailBoxTemplates t ON m.MailBoxTemplateId = t.MailBoxTemplateId
INNER JOIN HB_tblExchangeOrganizations o On O.OrganizationID = m.OrganizationID 
INNER JOIN HB_tblExchangeHosting e ON e.ExchHostingID = o.ExchHostingID 
WHERE t.SPLAType = 1 
And m.[Status] != '1' and m.UserLDAPUrl != ''
and d.OWAMailboxPolicyName not like 'Default Basic SPLA OWA Mailbox Policy For SG%'
Union
SELECT m.MailboxId,e.SGMemberID FROM HB_tblExchangeMailboxDetail d
INNER JOIN HB_tblExchangeMailboxes m ON m.MailboxID = d.MailboxID
INNER JOIN HB_tblExchangeOrganizations o On O.OrganizationID = m.OrganizationID 
INNER JOIN HB_tblExchangeHosting e ON e.ExchHostingID = o.ExchHostingID 
WHERE m.[Status] != '1' and m.UserLDAPUrl != ''
and d.OWAMailboxPolicyName not like 'Default Basic SPLA OWA Mailbox Policy For SG%'
And (d.EnterpriseTemplate = '0' 
AND d.UMEnabled = '0' 
AND d.JournalingMailboxId = '0'        
AND d.ArchiveDBName = '' 
AND d.MAPIAccess = '0'          
AND d.EWSAccess = '0' 
AND d.OWANonCachedMode = '0'         
AND d.ExchangeActivesync = '0'          
AND d.OutLookCAL = '0'       
AND d.InPlaceHold = '0'          
AND d.LitigationHoldEnabled = '0')
and MailBoxTemplateId = 0
GO



Alter PROCEDURE CSP_UpdateOwaPolicyNameToApplyDefaultOWAPolicy
AS

Update HB_tblExchangeSGOWAMailboxPolicy Set IsDefault = '0' where PolicyName like 'Default Basic SPLA OWA Mailbox Policy For SG%' and IsDefault='1'
Update HB_tblExchangeOrgOWAMailboxPolicy Set IsDefault = '0' where PolicyName like 'Default Basic SPLA OWA Mailbox Policy For SG%' and IsDefault='1'

Declare @ServerGroupID int
Declare @PolicyName nvarchar(300) 
Declare @SGPolicyId int

DECLARE GetAllServerGroups Cursor FOR select ServerGroupid
from HB_tblExchangeServerGroups where IsEnabled='1'
and Exists (select ServerGroupId from HB_tblExchangeServerGroupsMembers where IsEnabled=1 and ServerGroupID = HB_tblExchangeServerGroups.ServerGroupID)
and ProviderID != '4'

OPEN GetAllServerGroups
FETCH NEXT FROM GetAllServerGroups INTO @ServerGroupID
WHILE (@@FETCH_STATUS = 0)
BEGIN

	 Set @PolicyName = 'Default OWA Mailbox Policy For SG' + convert(nvarchar(10), @ServerGroupID)  

	If not Exists (Select * from HB_tblExchangeSGOWAMailboxPolicy where ServerGroupId=@ServerGroupID and PolicyName = @PolicyName)
	Begin
	  INSERT INTO HB_tblExchangeSGOWAMailboxPolicy(ServerGroupId, PolicyName, AssignedToCustomerDisplay, AssignedToCustomerIds, IsDefault)  
	  VALUES(@ServerGroupId, @PolicyName, ',All,', ',-1,', '1')  
	End


	Set @PolicyName = ('Default Basic SPLA OWA Mailbox Policy For SG' + convert(nvarchar(10), @ServerGroupID))
	If not Exists (Select * from HB_tblExchangeSGOWAMailboxPolicy where ServerGroupId=@ServerGroupID and PolicyName = @PolicyName)
	Begin  
		INSERT INTO HB_tblExchangeSGOWAMailboxPolicy(ServerGroupId, PolicyName, AssignedToCustomerDisplay, AssignedToCustomerIds, IsDefault)
		VALUES(@ServerGroupId, @PolicyName, ',All,', ',-1,', '0')
		
		Update HB_tblExchangeSGOWAMailboxPolicy Set
		ActiveSyncIntegrationEnabled = '0',
		PublicFoldersEnabled = '0',
		UMIntegrationEnabled = '0'
		Where PolicyName = @PolicyName

	End

	If not exists (Select * from HB_tblExchangeSGOWAMailboxPolicy where ServerGroupId=@ServerGroupID  and IsDefault='1')
	Begin
		Update HB_tblExchangeSGOWAMailboxPolicy Set IsDefault='1' where PolicyName='Default OWA Mailbox Policy For SG' + convert(nvarchar(10), @ServerGroupID)
		and ServerGroupId=@ServerGroupID
	End

FETCH NEXT FROM GetAllServerGroups INTO @ServerGroupId
END
CLOSE GetAllServerGroups
DEALLOCATE GetAllServerGroups


DECLARE @OrganizationID int
Declare @OrgInternalName nvarchar(200)
DECLARE GetAllOrgs Cursor FOR select O.OrganizationID, M.ServerGroupID, O.OrgInternalName
from HB_tblExchangeHosting H INNER JOIN HB_tblExchangeOrganizations O On H.ExchHostingID = O.ExchHostingID
INNER JOIN HB_tblExchangeServerGroupsMembers M On H.SGMemberID = M.SGMemberID
INNER JOIN HB_tblExchangeServerGroups SG On M.ServerGroupID = SG.ServerGroupID
Where O.OrganizationName != 'Add New Organization' and H.IsCancelled = 0 and O.Status ='1' and SG.ProviderID !='4'
OPEN GetAllOrgs
FETCH NEXT FROM GetAllOrgs INTO @OrganizationID, @ServerGroupID, @OrgInternalName
WHILE (@@FETCH_STATUS = 0)
BEGIN

	Set @PolicyName = 'Default OWA Mailbox Policy For SG' + convert(nvarchar(10), @ServerGroupID)  

	If not Exists (Select * from HB_tblExchangeOrgOWAMailboxPolicy where OrganizationID=@OrganizationID and PolicyName = @PolicyName)
	Begin

	  Set @SGPolicyId = (Select SGOWAPolicyId from HB_tblExchangeSGOWAMailboxPolicy Where ServerGroupId=@ServerGroupID And PolicyName=@PolicyName)

	  INSERT INTO HB_tblExchangeOrgOWAMailboxPolicy (SGOWAPolicyId, OrganizationID, PolicyName, IsDefault)
	  VALUES(@SGPolicyId, @OrganizationID, @PolicyName, '0')  
	End

	Set @PolicyName = 'Default Basic SPLA OWA Mailbox Policy For SG' + convert(nvarchar(10), @ServerGroupID)

	IF NOT EXISTS(Select * from HB_tblExchangeOrgOWAMailboxPolicy Where OrganizationID = @OrganizationID and PolicyName = @PolicyName)
	BEGIN
	 INSERT INTO HB_tblExchangeOrgOWAMailboxPolicy(SGOWAPolicyId, OrganizationID, PolicyName, IsDefault, ActiveSyncIntegrationEnabled, PublicFoldersEnabled, UMIntegrationEnabled)
	 SELECT SGOWAPolicyId, @OrganizationID, PolicyName, '0', '0', '0', '0' from HB_tblExchangeSGOWAMailboxPolicy
	 Where ServerGroupId = @ServerGroupID and IsDefault = '0' and PolicyName = @PolicyName
	END

	If not exists (Select * from HB_tblExchangeOrgOWAMailboxPolicy where OrganizationID = @OrganizationID and  IsDefault='1')
	Begin
		Update HB_tblExchangeOrgOWAMailboxPolicy Set IsDefault='1' where PolicyName='Default OWA Mailbox Policy For SG' + convert(nvarchar(10), @ServerGroupID)
		and OrganizationID = @OrganizationID 
	End

FETCH NEXT FROM GetAllOrgs INTO @OrganizationID, @ServerGroupID, @OrgInternalName
END
CLOSE GetAllOrgs
DEALLOCATE GetAllOrgs

UPDATE d
SET d.OWAMailboxPolicyName=''
FROM HB_tblExchangeMailboxDetail d
INNER JOIN HB_tblExchangeMailboxes m ON m.MailboxID = d.MailboxID
INNER JOIN Hb_tblExchangeMailBoxTemplates t ON m.MailBoxTemplateId = t.MailBoxTemplateId
INNER JOIN HB_tblExchangeOrganizations o On O.OrganizationID = m.OrganizationID 
INNER JOIN HB_tblExchangeHosting e ON e.ExchHostingID = o.ExchHostingID 
WHERE t.SPLAType IN (2,3,4,5) 
and d.OWAMailboxPolicyName LIKE 'Default Basic SPLA OWA Mailbox Policy For SG%'
And m.[Status] != '1' and m.UserLDAPUrl != ''
GO
