ALTER PROCEDURE SPR_EM_GetTaxationSettings      
(      
 @PackageId numeric,      
 @CustomerId numeric,      
 @CompanyId numeric = -1      
)      
AS      
BEGIN    
     
 DECLARE @CCountryId int, @CStateId int,@T1Title nvarchar(100),@T2Title nvarchar(100);    
 SELECT @CCountryId = CountryId, @CStateId = StateId, @CompanyId = CompanyID FROM tblCustomers 
 WHERE CustomerId = @CustomerId AND CompanyID = CASE WHEN @CompanyId > 0 THEN @CompanyId ELSE CompanyID END  
 SELECT TOP 1 @T1Title = Title, @T2Title = T2Title FROM HB_tblTaxRates 
 WHERE CountryId = @CCountryId AND (StateId = @CStateId OR StateId < 1) AND CompanyId = @CompanyId
 ORDER BY StateId DESC  
      
 SELECT DISTINCT C.IsTaxable CustomerTaxable,TR.Rate,TR.Type,TR.T2Type,TR.T2Rate,      
 TR.IsT2Compound,TR.Active,0 PackageID,'' PackageName,1 ProductTaxable,S.DisableTaxCollection CompanyTaxDisabled,@T1Title T1Title, @T2Title T2Title,TR.StateId      
 FROM Hb_tblTaxRates TR       
 INNER JOIN tblCustomers C ON C.CompanyId = TR.CompanyId       
 AND TR.CountryId = C.CountryId       
 AND C.StateId = CASE WHEN TR.Stateid < 1 THEN C.StateId ELSE TR.StateId END           
 INNER JOIN HB_tblSystemSettings  S ON S.CompanyId = C.CompanyId AND TR.CompanyId = S.CompanyId     
 WHERE C.CustomerId = @CustomerId     
 AND C.CompanyId = CASE WHEN @CompanyId < 1 THEN C.CompanyId ELSE @CompanyId END      
 GROUP BY C.IsTaxable,TR.Rate,TR.Type,TR.T2Type,TR.T2Rate,TR.IsT2Compound,TR.Active,S.DisableTaxCollection,TR.StateId  
 ORDER BY TR.StateId DESC  
END 
GO

ALTER PROCEDURE SP_EM_CRM_GetOrganizationReport  
@OrgUniqueName nvarchar (100),      
@CustomerName nvarchar (100),      
@OrgDispName nvarchar (100),      
@CompanyId int,    
@CustomerId nvarchar(10)       
AS      
BEGIN      
if @CompanyId < 1 or @CompanyId = ''      
begin      
 Set @CompanyId = null      
end      
select      
 Hb_tblCRMOrganizations.OrgID,      
 Hb_tblCRMOrganizations.OrgUniqueName as OrganizationName,      
 tblCustomers.FirstName+' '+tblCustomers.LastName as Customer,
 [tblCustomers].CustomerNumber,      
  tblCompanies.FirstName+' '+tblCompanies.LastName as [Owner],      
 Hb_tblCRMOrganizations.OrgDispName as DisplayName,      
 Hb_tblCRMOrganizations.OrgSiteURL as OrganizationUrl,      
 (SELECT COUNT(Hb_tblCRMUsers.UserID) FROM Hb_tblCRMUsers WHERE Hb_tblCRMUsers.OrgId = Hb_tblCRMOrganizations.OrgId and Hb_tblCRMUsers.Disabled = 0) as ActiveUsers,      
 (SELECT COUNT(Hb_tblCRMUsers.UserID) FROM Hb_tblCRMUsers WHERE Hb_tblCRMUsers.OrgId = Hb_tblCRMOrganizations.OrgId and Hb_tblCRMUsers.Disabled = 1) as InActiveUsers,      
 (SELECT COUNT(Hb_tblCRMUsers.UserID) FROM Hb_tblCRMUsers WHERE Hb_tblCRMUsers.OrgId = Hb_tblCRMOrganizations.OrgId) as TotalUsers,      
 (SELECT COUNT(HB_tblCRMOrganizationDomains.DomainID) FROM Hb_tblCRMOrganizationDomains WHERE HB_tblCRMOrganizationDomains.OrgID = Hb_tblCRMOrganizations.OrgID) as Domains
 FROM       
 Hb_tblCRMOrganizations,      
 [tblCustomers],  
 HB_tblCRMHosting,      
 tblCompanies,      
 HB_tblPackagesSold,      
 HB_tblPackages      
 WHERE tblCustomers.CustomerId=HB_tblCRMHosting.CustomerId      
 AND HB_tblCRMHosting.CRMId=Hb_tblCRMOrganizations.CRMId      
 AND HB_tblCRMHosting.CompanyId=tblCompanies.CompanyId      
 AND Hb_tblCRMOrganizations.CRMId=HB_tblCRMHosting.CRMId      
 AND HB_tblCRMHosting.SoldPackageId=HB_tblPackagesSold.SoldPackageId      
 AND HB_tblPackagesSold.PackageID=HB_tblPackages.PackageID      
 AND HB_tblPackages.ResellerPackage=0      
 AND Hb_tblCRMOrganizations.OrgDispName like CASE @OrgDispName WHEN N'' THEN Hb_tblCRMOrganizations.OrgDispName ELSE '%'+@OrgDispName+ '%' END  
 AND Hb_tblCRMOrganizations.OrgUniqueName like CASE @OrgUniqueName WHEN N'' THEN Hb_tblCRMOrganizations.OrgUniqueName ELSE '%'+@OrgUniqueName +'%' END    
 AND tblCustomers.FirstName+' '+tblCustomers.LastName like CASE @CustomerName WHEN N'' THEN tblCustomers.FirstName+' '+tblCustomers.LastName ELSE '%'+@CustomerName+'%' END  
 AND HB_tblCRMHosting.CompanyId = CASE WHEN @CompanyId IS NULL OR @CompanyId < 1 THEN HB_tblCRMHosting.CompanyId ELSE @CompanyId END  
 AND tblCustomers.CustomerID = CASE WHEN @CustomerId = N'' OR @CustomerId Is Null OR @CustomerId < 1 THEN tblCustomers.CustomerID ELSE @CustomerId END          
END 
GO

ALTER Procedure [SP_EM_EXGetMailBoxReport]                     
(                    
@Owner nvarchar(10),                    
@OrgName nvarchar (255),                    
@Name nvarchar (200) ,        
@CustomerId nvarchar(10)                   
)                    
As                 
              
              
Update HB_tblExchangeMailboxDetail              
Set IssueWarningAt='-1' Where    IssueWarningAt='unlimited'              
                    
              
Update HB_tblExchangeMailboxDetail              
Set ProhibitSendAt='-1' Where    ProhibitSendAt='unlimited'              
              
Update HB_tblExchangeMailboxDetail              
Set ProhibitSendRecAt='-1' Where    ProhibitSendAt='unlimited'                 
                    
Declare @SQL nvarchar(4000)                    
                    
Set @SQL='Select tblCustomers.FirstName+'' ''+tblCustomers.LastName as Customer,
tblCustomers.CustomerNumber  , tblCustomers.CustomerId,                     
HB_tblExchangeOrganizations.OrganizationID, Hb_tblExchangeHosting.ExchHostingID, OrganizationName,                     
HB_tblExchangeMailboxes.MailBoxId, MailBoxDisplayName+'' [''+EmailAddress+'']'' as MailBox, CurrentUsage, CurrentUsageTimeStamp,                     
IssueWarningAt, ProhibitSendAt, ProhibitSendRecAt,                     
tblCompanies.FirstName+'' ''+tblCompanies.LastName as [Owner], tblCompanies.CompanyId,    
OWAUMIntegration, OWAActiveSync, OWANonCachedMode, OWAPublicFolder,                
MAPIAccess, OutLookCAL, HB_tblPackagesSold.EduDiscount, PopAccess, IMAPAccess, ExchangeActiveSync, OWAHTTPAccess,        
UMEnabled, ArchiveDBName, MailboxType, HB_tblExchangeMailboxes.Disabled,   
Wireless= (CASE WHEN exists(Select UserId from HB_tblWirelessAccounts    
 Where mailboxId= HB_tblExchangeMailboxes.MailboxId)      
        THEN ''1''  
        ELSE ''0'' End),  
HB_tblExchangeMailboxes.MailboxTemplateID
FROm HB_tblExchangeOrganizations, Hb_tblExchangeHosting, tblCompanies, tblCustomers,                     
HB_tblPackagesSold, HB_tblPackages, HB_tblExchangeMailboxes, HB_tblExchangeMailboxDetail, HB_tblExchangeEmailAddresses  
Where HB_tblExchangeOrganizations.ExchHostingID=Hb_tblExchangeHosting.ExchHostingID                    
and Hb_tblExchangeHosting.CompanyId=tblCompanies.CompanyId                    
and Hb_tblExchangeHosting.CustomerId=tblCustomers.CustomerId                    
and HB_tblPackagesSold.SoldPackageId=Hb_tblExchangeHosting.SoldPackageId                    
and HB_tblPackages.PackageId =HB_tblPackagesSold.PackageId and ResellerPackage=''0''                    
and IsPrimaryEmailAddress=''1''  
and HB_tblExchangeMailboxes.MailBoxId= HB_tblExchangeMailboxDetail.MailBoxId                    
and HB_tblExchangeMailboxes.MailBoxId= HB_tblExchangeEmailAddresses.MailBoxId                    
and HB_tblExchangeOrganizations.OrganizationId=HB_tblExchangeMailboxes.OrganizationId                     
and (HB_tblExchangeOrganizations.OrganizationName like ''%''+N'''+ replace(@OrgName,'''','''''')+'''+''%''                    
or HB_tblExchangeOrganizations.OrgInternalName like ''%''+N'''+ replace(@OrgName,'''','''''')+'''+''%'')                     
and tblCustomers.FirstName+'' ''+tblCustomers.LastName like ''%''+N'''+ replace(@Name,'''','''''')+'''+''%'''                    
                    
If (@Owner!='0')                    
Set @SQL= @SQL+' and tblCompanies.CompanyId = '''+@Owner+''''           
        
If (@CustomerId!='0')                    
Set @SQL= @SQL+' and tblCustomers.CustomerId = '''+@CustomerId+''''                  
                    
Exec (@SQL)
GO

Alter PROCEDURE [SP_EM_SP_GetOrganizationReport]

(    

 @Title nvarchar(200),             

 @Url nvarchar (500),              

 @CustomerName nvarchar (100),    

 @CompanyId int,    

 @SGMemberId int,

 @CustomerId nvarchar(10)

)

AS 

BEGIN

	SELECT HB_tblSPOrganizations.OrgId, HB_tblSPOrganizations.OrganizationName, tblCustomers.FirstName + ' ' + tblCustomers.LastName AS Customer, CustomerNumber, 

		   tblCompanies.FirstName + ' ' + tblCompanies.LastName AS Owner, HB_tblSPOrganizations.Title, HB_tblSPOrganizations.Url AS SiteUrl, 

		   HB_tblSPOrganizations.MaxStorage, HB_tblSPOrganizations.PortalStorageInMB, HB_tblSPOrganizations.TimeStamps, COUNT(HB_tblSPUsers.UserID)AS UsedUsers,

		   (SELECT COUNT(DomainId) FROM Hb_tblSPOrganizationDomains WHERE (SPOrgId = HB_tblSPOrganizations.OrgId)) AS ActiveDomains,

		   ISNULL(Hb_tblSPServerFeaturePacks.FPTitle,'N/A') FeaturePack

	FROM   tblCompanies INNER JOIN

		   HB_tblSPHosting ON tblCompanies.CompanyID = HB_tblSPHosting.CompanyId INNER JOIN

		   tblCustomers ON HB_tblSPHosting.CustomerID = tblCustomers.CustomerID INNER JOIN

		   HB_tblSPUsers INNER JOIN

		   HB_tblSPOrganizations ON HB_tblSPUsers.OrgID = HB_tblSPOrganizations.OrgId ON HB_tblSPHosting.SPId = HB_tblSPOrganizations.SPId INNER JOIN

		   HB_tblPackagesSold ON HB_tblSPHosting.SoldPackageId = HB_tblPackagesSold.SoldPackageID INNER JOIN

		   HB_tblPackages ON HB_tblPackagesSold.PackageID = HB_tblPackages.PackageID INNER JOIN

		   Hb_tblSPOrganizationDomains AS Hb_tblSPOrganizationDomains_1 ON HB_tblSPOrganizations.OrgId = Hb_tblSPOrganizationDomains_1.SPOrgId LEFT OUTER JOIN

		   Hb_tblSPServerFeaturePacks ON HB_tblSPHosting.FPId = Hb_tblSPServerFeaturePacks.FPId

	WHERE  (HB_tblPackages.ResellerPackage = 0)

	AND HB_tblSPOrganizations.Title like CASE WHEN @Title = N'' OR @Title Is Null THEN HB_tblSPOrganizations.Title ELSE N'%' + @Title + '%' END

	AND HB_tblSPOrganizations.Url like CASE WHEN @Url = N'' OR @Url IS NULL THEN HB_tblSPOrganizations.Url ELSE  N'%'+@Url +'%' END 

	AND tblCustomers.FirstName+' '+tblCustomers.LastName like CASE 

	WHEN @CustomerName = N'' OR @CustomerName IS NULL 

	THEN tblCustomers.FirstName+' '+tblCustomers.LastName ELSE N'%'+ @CustomerName+'%' END   

	AND HB_tblSPHosting.CompanyId = CASE WHEN @CompanyId = N'' OR @CompanyId Is NULL OR @CompanyId < 1 THEN  HB_tblSPHosting.CompanyId ELSE @CompanyId END

	AND HB_tblSPHosting.SGMemberId = CASE WHEN @SGMemberId = N'' OR @SGMemberId IS NULL OR @SGMemberId < 1 THEN HB_tblSPHosting.SGMemberId ELSE @SGMemberId END

	AND tblCustomers.CustomerID = CASE WHEN @CustomerId = N'' OR @CustomerId Is Null OR @CustomerId < 1 THEN tblCustomers.CustomerID ELSE @CustomerId END    

	GROUP BY HB_tblSPOrganizations.OrgId, HB_tblSPOrganizations.OrganizationName, tblCompanies.FirstName, tblCompanies.LastName, HB_tblSPOrganizations.Title, 

			 HB_tblSPOrganizations.Url, tblCustomers.FirstName, HB_tblSPOrganizations.MaxStorage, HB_tblSPOrganizations.PortalStorageInMB, 

			 HB_tblSPOrganizations.TimeStamps, tblCustomers.LastName, ISNULL(Hb_tblSPServerFeaturePacks.FPTitle,'N/A'), CustomerNumber

END
GO

Alter PROCEDURE [SP_EM_Delete_tblCustomer]   

 @CustomerId int   

 AS   

 Delete HB_tblCRMGroupJobs Where [CustomerID] = @CustomerID 
 Delete HB_tblExchangeGroupJobs Where [CustomerID] = @CustomerID 
Delete HB_tblLyncGroupJobs Where [CustomerID] = @CustomerID 
Delete HB_tblSharePointGroupJobs Where [CustomerID] = @CustomerID 


 DELETE [HB_tblHosting] WHERE  [CustomerID] = @CustomerID   

 DELETE [HB_tblResellerHosting] WHERE  [CustomerID] = @CustomerID   

 DELETE [HB_tblDedicatedHosting] WHERE  [CustomerID] = @CustomerID   

 DELETE [HB_tblSSLCert] WHERE  [CustomerID] = @CustomerID   

 DELETE [HB_tblDomainRegistrations] WHERE  [CustomerID] = @CustomerID   

 DELETE [HB_tblOCSHosting] WHERE  [CustomerID] = @CustomerID  

 DELETE [HB_tblVPSHosting] WHERE  [CustomerID] = @CustomerID  

 DELETE [HB_tblExchangeHosting] WHERE  [CustomerID] = @CustomerID   

 DELETE [HB_tblSPHosting] WHERE  [CustomerID] = @CustomerID   

 DELETE [HB_tblCRMHosting] WHERE  [CustomerID] = @CustomerID   

 DELETE [HB_tblADirHostedOrganizations] WHERE  [CustomerID] = @CustomerID   

 DELETE HB_tblADirUsers WHERE  [CustomerID] = @CustomerID   



 DELETE [HB_tblAddonsSold] WHERE  [CustomerID] = @CustomerID   

 DELETE [HB_tblPackages] WHERE  [CustomerID] = @CustomerID   

 DELETE [HB_tblInvoices] WHERE  [CustomerID] = @CustomerID   

 DELETE [tblAccountContacts] WHERE [CustomerID]=@CustomerID  

 DELETE [HB_tblCustomerTheme] WHERE  [CID] = @CustomerID  

 Declare @Email nvarchar(250)   

 Set @Email=(Select PrimaryEmail From tblCustomers Where CustomerID=@CustomerID)   

 Delete [HB_tblTickets] Where [SenderEmail] = @Email Or  [ReplyTo] = @Email   

 DELETE [tblCustomers] WHERE  [CustomerID] = @CustomerID  
GO

ALTER PROCEDURE [SP_EM_OCS_GetProviderIdByOCSHostingId]
(
       @OCSHostingID int
)
AS
       select distinct Hb_tblPackagesSold.PackageID, HB_tblOCSServerGroups.ProviderId 
       from HB_tblOCSServerGroups ,Hb_tblPackagesSold,HB_tblOCSPackagesToServerGroup, 
       HB_tblOCSHosting
       where HB_tblOCSServerGroups.ServerGroupID = 
       HB_tblOCSPackagesToServerGroup.servergroupid
       and HB_tblOCSPackagesToServerGroup.PackageID = Hb_tblPackagesSold.PackageID
       and Hb_tblPackagesSold.SoldPackageID = HB_tblOCSHosting.SoldPackageID
       and HB_tblOCSHosting.OCSHostingID=@OCSHostingID
GO

ALter Procedure CSP_GetTempData

(

@Type int,

@DataIdentity Nvarchar (1000)

)

AS

Delete from Hb_tblTempData where [Type]='2' and Hb_tblTempData.DateStamp < (getdate()- 1)

Select * from Hb_tblTempData Where DataIdentity =@DataIdentity and [Type]=@Type
GO




Alter Procedure CSP_InsertTempData

(

@Type int,

@DataIdentity Nvarchar (1000),

@Data ntext,

@DateStamp datetime

)

AS

Delete Hb_tblTempData Where [Type]='2' and DataIdentity=@DataIdentity

Insert into Hb_tblTempData ([Type], DataIdentity , Data , DateStamp)

Values (@Type, @DataIdentity , @Data , @DateStamp)

GO

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

CREATE PROCEDURE [SP_EM_Lync_GetLinkedADUser]    
(    
@ADDomainId int,    
@CustomerId int,    
@HostedOrgId int
)    
As   
select HB_tblADirUsers.ADUserID, UPName, DisplayName, LinkedMasterAccount From HB_tblADirUsers,HB_tblExchangeMailboxes
where HB_tblADirUsers.ADUserID=HB_tblExchangeMailboxes.ADUserID
and HB_tblExchangeMailboxes.MailboxType = 'Linked Mailbox'
and ADDomainID = @ADDomainId and CustomerId = @CustomerId 
And HB_tblADirUsers.ADUserID Not In     
(Select ADUserId From HB_tblADirUsersDetail Where HostedSvcID = 4)    
And HB_tblADirUsers.ADUserId in(Select ADUserId From HB_tblADirUsersDetail Where HostedOrgId = @HostedOrgId) 
and HB_tblADirUsers.UserLdapURL!=''
GO

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS C INNER JOIN SYSOBJECTS O ON C.id = O.id AND O.name = 'HB_tblLyncUsers' AND C.name = 'ObjectSID')
BEGIN
Alter Table HB_tblLyncUsers
	ADD ObjectSID nvarchar(500) default('')
END
GO

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS C INNER JOIN SYSOBJECTS O ON C.id = O.id AND O.name = 'HB_tblLyncUsers' AND C.name = 'MasterAccount')
BEGIN
Alter Table HB_tblLyncUsers
	ADD MasterAccount nvarchar(200) default('')
END
GO

ALTER PROCEDURE [SP_EM_OCS_Insert_HB_tblLyncUsers]
(
	@ADUserID					int,
	@OrgID 						bigint,
	@PackageAddonID				nvarchar(50),
	@DateCreated				datetime,
	@Notes						nvarchar(4000),
	@Email						nvarchar(255),
	@LineURI					nvarchar(50),
	@LineServerURI				nvarchar(255),
	@Status						int,
	@LineURIExt					nvarchar(10),
	@ObjectSID					nvarchar(500),
	@MasterAccount				nvarchar(200)
)
AS
	INSERT INTO [HB_tblLyncUsers] 
	( 
	ADUserID,
	OrgID,
	PackageAddonID,
	DateCreated,
	Notes,
	Email,
	LineURI,
	LineServerURI,
	[Status], LineURIExt,
	ObjectSID, MasterAccount
	)  
	VALUES 
	( 
	@ADUserID,
	@OrgID,
	@PackageAddonID,
	@DateCreated,
	@Notes,
	@Email,
	@LineURI,
	@LineServerURI,
	@Status, @LineURIExt,
	@ObjectSID, @MasterAccount
	)

	Declare @UserId bigint 
	Set @UserId=(Select @@Identity)
	
	Select @UserId  as RID
GO

Alter PROCEDURE [SP_EM_W_DeleteWirelessAccount]  
 (@UserId_1  [bigint])  
  
AS 

If ((Select ProviderId from [HB_tblWirelessAccounts]WHERE   
 ( [UserId]  = @UserId_1)  ) = '4')
Begin

Delete [HB_tblWirelessAccounts] Where ProviderId='4' and MailboxId=(
Select MailboxId from [HB_tblWirelessAccounts]WHERE   
 ( [UserId]  = @UserId_1) ) 
 and [UserId]  != @UserId_1

End
 



DELETE [HB_tblWirelessAccounts]   
  
WHERE   
 ( [UserId]  = @UserId_1)  
 
GO


Alter PROCEDURE [SP_EM_W_GetWirelessAccountsByOrgId]    
(    
@OrganizationId nvarchar(10),    
@Name nvarchar (200),    
@Email nvarchar (300),    
@ProviderId nvarchar(10),    
@Status nvarchar(10)    
)    
As    
    
Declare @SQL nvarchar (4000)    
    
Set @SQL='    
Declare @CompanyId int     
'    
    
Set @SQL=@SQL+'   


 
;WITH CTE_HB_tblWirelessAccounts AS
(
   SELECT  HB_tblWirelessAccounts.UserId, HB_tblWirelessAccounts.MailBoxId, HB_tblWirelessAccounts.ProviderId, HB_tblWirelessAccounts.SGMemberId, HB_tblWirelessAccounts.CompanyId, 
                      HB_tblWirelessAccounts.DeviceType, HB_tblWirelessAccounts.PhoneNumber, HB_tblWirelessAccounts.ClientSoftwareVersion, HB_tblWirelessAccounts.OTAPIN, 
                      HB_tblWirelessAccounts.OTANumericPIN, HB_tblWirelessAccounts.ITPolicyStatus, HB_tblWirelessAccounts.ITPolicySent, HB_tblWirelessAccounts.ITPolicyRecieved, 
                      HB_tblWirelessAccounts.ForwardedMessages, HB_tblWirelessAccounts.SendMessages, HB_tblWirelessAccounts.PendingDataPackets, HB_tblWirelessAccounts.FilteredMessages, 
                      HB_tblWirelessAccounts.ExpiredMessages, HB_tblWirelessAccounts.LastForwardTime, HB_tblWirelessAccounts.LastSendTime, HB_tblWirelessAccounts.LastContactTime, 
                      HB_tblWirelessAccounts.LastResult, HB_tblWirelessAccounts.IsEnabled, HB_tblWirelessAccounts.DeviceStatus, HB_tblWirelessAccounts.LastUpdated, 
                       HB_tblExchangeMailboxes.ADUserID, HB_tblExchangeMailboxes.OrganizationID, HB_tblExchangeMailboxes.MailboxType, 
                      HB_tblExchangeMailboxes.MailBoxDisplayName, HB_tblExchangeMailboxes.SAMAccountName, HB_tblExchangeMailboxes.MailBoxPassword,  
                      HB_tblExchangeMailboxes.UserLDAPUrl, HB_tblExchangeMailboxes.ExchangeAlias, HB_tblExchangeMailboxes.HideFromOtherALs,  
                      HB_tblExchangeMailboxes.UPN, HB_tblExchangeMailboxes.MailBoxGUID, HB_tblExchangeMailboxes.Status,
                      HB_tblExchangeMailboxes.Disabled, HB_tblExchangeMailboxes.MailBoxDbName, HB_tblExchangeMailboxes.DateCreated, 
                      HB_tblExchangeMailboxes.ManagerID, HB_tblExchangeMailboxes.CurrentUsage, HB_tblExchangeMailboxes.CurrentUsageTimeStamp, HB_tblExchangeMailboxes.SubscriptionSuspended, 
                      HB_tblExchangeMailboxes.LitigationHold, HB_tblExchangeMailboxes.LinkedDomainController, HB_tblExchangeMailboxes.LinkedMasterAccount, HB_tblExchangeMailboxes.LinkedAdminLogin, 
                      HB_tblExchangeMailboxes.LinkedAdminPassword, HB_tblExchangeMailboxes.LangCode, HB_tblExchangeMailboxes.MailBoxTemplateId, HB_tblExchangeMailboxes.TimeZone, 
                      HB_tblExchangeMailboxes.OldStatus, HB_tblExchangeMailboxes.MobilePolicyId, HB_tblExchangeEmailAddresses.EmailAddressID, 
                      HB_tblExchangeEmailAddresses.EmailAddress, HB_tblExchangeEmailAddresses.IsPrimaryEmailAddress, HB_tblExchangeEmailAddresses.DateStamp, 
                       HB_tblWirelessProviders.Name, HB_tblWirelessProviders.Trans_Id, HB_tblWirelessProviders.IntegrationStatus
          ,
         ROW_NUMBER() OVER (PARTITION BY HB_tblWirelessAccounts.MailBoxId, HB_tblWirelessAccounts.ProviderId 
         ORDER BY UserId Asc) AS rn
   FROM 

 (HB_tblExchangeEmailAddresses Join HB_tblExchangeMailBoxes    
On HB_tblExchangeMailboxes.MailBoxId=HB_tblExchangeEmailAddresses.MailBoxId And IsPrimaryEmailAddress=''1'' 
And OrganizationId='''+@OrganizationId+'''    
and HB_tblExchangeMailBoxes.MailboxType=''User Mailbox''    
'    
    
if @Name !=''    
Begin    
Set @SQL=@SQL+' and MailBoxDisplayName like ''%''+'''+replace(@Name,'''','''''')+'''+''%'''    
End    
    
if @Email !=''    
Begin    
Set @SQL=@SQL+' and EmailAddress like ''%''+'''+replace(@Email,'''','''''')+'''+''%'''    
End    
    
if @Status='3'    
Begin    
 Set @SQL=@SQL+' and HB_tblExchangeMailboxes.MailBoxId Not In(Select MailBoxId From HB_tblWirelessAccounts)'    
End    
else if @Status='1'    
Begin    
 Set @SQL=@SQL+' and HB_tblExchangeMailboxes.MailBoxId  In(Select MailBoxId From HB_tblWirelessAccounts Where DeviceStatus='''+@Status+''')'    
End    
else if @Status='2'    
Begin    
 Set @SQL=@SQL+' and HB_tblExchangeMailboxes.MailBoxId In(Select MailBoxId From HB_tblWirelessAccounts Where DeviceStatus='''+@Status+''')'    
End    
    
    
Set @SQL=@SQL+'    
)    
Left JOIN (HB_tblWirelessAccounts Join HB_tblWirelessProviders On HB_tblWirelessAccounts.ProviderId=HB_tblWirelessProviders.ProviderId)    
ON HB_tblExchangeMailBoxes.MailBoxId=HB_tblWirelessAccounts.MailBoxId    
'    
    
if @Status!='-1'    
Begin    
 if @Status!='3'    
 Begin    
  Set @SQL=@SQL+' and DeviceStatus='''+@Status+''''    
 End    
End    
    
if @ProviderId!='-1'    
Begin    
Set @SQL=@SQL+' and HB_tblWirelessAccounts.ProviderId='''+@ProviderId+''''    
End    
      


Set @SQL=@SQL+' ) 
Select *
FROM CTE_HB_tblWirelessAccounts
     

WHERE rn = 1 and UserId is not null '


Set @SQL= @SQL + ' Order By MailBoxDisplayName'      
    
EXEC (@SQL)    
GO










Alter PROCEDURE [SP_EM_W_GetWirelessAccounts]    
(    
@CompanyId nvarchar(10),    
@Name nvarchar (200),    
@Email nvarchar (300),    
@ProviderId nvarchar(10),    
@Status nvarchar(10)    
)    
As    
    
Declare @SQL nvarchar (4000)    
    
Set @SQL='    
Declare @CompanyId int     
'    
    
Set @SQL=@SQL+'  Set @CompanyId='''+@CompanyId+''''    
    
Set @SQL=@SQL+'   


;WITH CTE_HB_tblWirelessAccounts AS
(
   SELECT  HB_tblWirelessAccounts.UserId, HB_tblWirelessAccounts.MailBoxId, HB_tblWirelessAccounts.ProviderId, HB_tblWirelessAccounts.SGMemberId, HB_tblWirelessAccounts.CompanyId, 
                      HB_tblWirelessAccounts.DeviceType, HB_tblWirelessAccounts.PhoneNumber, HB_tblWirelessAccounts.ClientSoftwareVersion, HB_tblWirelessAccounts.OTAPIN, 
                      HB_tblWirelessAccounts.OTANumericPIN, HB_tblWirelessAccounts.ITPolicyStatus, HB_tblWirelessAccounts.ITPolicySent, HB_tblWirelessAccounts.ITPolicyRecieved, 
                      HB_tblWirelessAccounts.ForwardedMessages, HB_tblWirelessAccounts.SendMessages, HB_tblWirelessAccounts.PendingDataPackets, HB_tblWirelessAccounts.FilteredMessages, 
                      HB_tblWirelessAccounts.ExpiredMessages, HB_tblWirelessAccounts.LastForwardTime, HB_tblWirelessAccounts.LastSendTime, HB_tblWirelessAccounts.LastContactTime, 
                      HB_tblWirelessAccounts.LastResult, HB_tblWirelessAccounts.IsEnabled, HB_tblWirelessAccounts.DeviceStatus, HB_tblWirelessAccounts.LastUpdated, 
                       HB_tblExchangeMailboxes.ADUserID, HB_tblExchangeMailboxes.OrganizationID, HB_tblExchangeMailboxes.MailboxType, 
                      HB_tblExchangeMailboxes.MailBoxDisplayName, HB_tblExchangeMailboxes.SAMAccountName, HB_tblExchangeMailboxes.MailBoxPassword,  
                      HB_tblExchangeMailboxes.UserLDAPUrl, HB_tblExchangeMailboxes.ExchangeAlias, HB_tblExchangeMailboxes.HideFromOtherALs,  
                      HB_tblExchangeMailboxes.UPN, HB_tblExchangeMailboxes.MailBoxGUID, HB_tblExchangeMailboxes.MailBoxDbName, HB_tblExchangeMailboxes.DateCreated, 
                      HB_tblExchangeMailboxes.ManagerID, HB_tblExchangeMailboxes.CurrentUsage, HB_tblExchangeMailboxes.CurrentUsageTimeStamp, HB_tblExchangeMailboxes.SubscriptionSuspended, 
                      HB_tblExchangeMailboxes.LitigationHold, HB_tblExchangeMailboxes.LinkedDomainController, HB_tblExchangeMailboxes.LinkedMasterAccount, HB_tblExchangeMailboxes.LinkedAdminLogin, 
                      HB_tblExchangeMailboxes.LinkedAdminPassword, HB_tblExchangeMailboxes.LangCode, HB_tblExchangeMailboxes.MailBoxTemplateId, HB_tblExchangeMailboxes.TimeZone, 
                      HB_tblExchangeMailboxes.OldStatus, HB_tblExchangeMailboxes.MobilePolicyId, HB_tblExchangeEmailAddresses.EmailAddressID, 
                      HB_tblExchangeEmailAddresses.EmailAddress, HB_tblExchangeEmailAddresses.IsPrimaryEmailAddress, HB_tblExchangeEmailAddresses.DateStamp, 
                       HB_tblWirelessProviders.Name, HB_tblWirelessProviders.Trans_Id, HB_tblWirelessProviders.IntegrationStatus
          ,
         ROW_NUMBER() OVER (PARTITION BY HB_tblWirelessAccounts.MailBoxId, HB_tblWirelessAccounts.ProviderId 
         ORDER BY UserId Asc) AS rn
   FROM HB_tblWirelessAccounts, HB_tblExchangeMailboxes, HB_tblExchangeEmailAddresses,HB_tblWirelessProviders    

WHERE 
    
 HB_tblWirelessAccounts.CompanyId=@CompanyId    
and HB_tblWirelessAccounts.MailBoxId=HB_tblExchangeMailboxes.MailBoxId    
and HB_tblExchangeMailboxes.MailBoxId=HB_tblExchangeEmailAddresses.MailBoxId    
and IsPrimaryEmailAddress=''1''     
and HB_tblWirelessAccounts.ProviderId=HB_tblWirelessProviders.ProviderId    
 '    
    
if @Name !=''    
Begin    
Set @SQL=@SQL+' and MailBoxDisplayName like ''%''+N'''+replace(@Name,'''','''''')+'''+''%'''    
End    
    
if @Email !=''    
Begin    
Set @SQL=@SQL+' and EmailAddress like ''%''+N'''+replace(@Email,'''','''''')+'''+''%'''    
End    
    
if @Status!='-1'    
Begin    
Set @SQL=@SQL+' and DeviceStatus='''+@Status+''''    
End    
    
if @ProviderId!='-1'    
Begin    
Set @SQL=@SQL+' and HB_tblWirelessAccounts.ProviderId='''+@ProviderId+''''    
End    

Set @SQL=@SQL+' ) 
Select *
FROM CTE_HB_tblWirelessAccounts
     

WHERE rn = 1 '
    
EXEC (@SQL)  
GO



Alter PROCEDURE [SP_EM_W_GetBBProvisionedServerByMBID]    
  
(@MailboxId int,  
 @ProviderID int)    
AS    
  
Select * from HB_tblWirelessServerGroups,HB_tblWirelessServerGroupsMembers    
where HB_tblWirelessServerGroupsMembers.SGMemberID =(Select SGMemberID from Hb_tblWirelessAccounts where   
UserId=(Select top 1 UserId From Hb_tblWirelessAccounts Where MailBoxId=@MailboxId and ProviderID = @ProviderID))    
and HB_tblWirelessServerGroups.ServerGroupId=HB_tblWirelessServerGroupsMembers.ServerGroupId  
GO

  
Alter PROCEDURE [SP_EM_DeleteExMailBox]       
(@MailboxID_1 [int])       
      
AS       
      
Declare @MailboxType nvarchar (50)    
    
Set @MailboxType = (Select MailBoxType from HB_tblExchangeMailBoxes Where MailboxId=@MailboxID_1)    
  
Declare @ADUserId int       
Set @ADUserId=(Select ADUserId From HB_tblExchangeMailBoxes Where MailBoxID=@MailboxID_1)       
   
If ( @MailboxType ='User Mailbox' or @MailboxType ='Linked Mailbox')    
    
Begin    
      
      
 If (@ADUserId !='0')       
 Begin       
      
 if Not exists (Select * From HB_tblADirUsersDetail Where ADUserId=@ADUserId and HostedSvcID='0')      
 Begin       
   Insert Into HB_tblADirUsersDetail(ADUserId, HostedOrgId,HostedSvcId,DateStamp)      
   Select top(1) ADUserId, HostedOrgId,'0',DateStamp From HB_tblADirUsersDetail      
   Where ADUserId=@ADUserId and HostedSvcID='1'      
  End      
      
  Delete From HB_tblADirUsersDetail Where ADUserId=@ADUserId and HostedSvcID='1'      
       
 End       
     
End    
Else    
Begin       
    
 Delete From HB_tblADirUsers Where ADUserId=@ADUserId     
    
End    
  
Delete From HB_tblADirUsers Where ADUserId=@ADUserId and UserLDAPURL=''   
  
If (  @MailboxType ='Linked Mailbox')    
Begin    
  
Delete From HB_tblADirUsers Where ADUserId=@ADUserId And not exists (Select ADUSerId from HB_tblLyncUSers  
Where ADUserId=@ADUSerId)  
  
End  
    
Declare @SGMemberId int      
      
Set @SGMemberId= (Select SGMemberId FRom  HB_tblExchangeHosting Where ExchHostingID=      
(Select ExchHostingID from HB_tblExchangeOrganizations Where OrganizationID=(      
Select OrganizationID From HB_tblExchangeMailboxes Where mailboxId=@MailBoxID_1      
)))      
      
Update HB_tblExchangeServerGroupsMembers Set MailBoxLoad= ((Select Count(MailBoxId) from HB_tblExchangeMailboxes      
Where MailBoxId in (Select MailBoxId from HB_tblExchangeMailboxes Where OrganizationID in      
 (Select OrganizationID from HB_tblExchangeOrganizations Where ExchHostingID in       
 (Select ExchHostingId from HB_tblExchangeHosting Where SGMemberId In      
  (Select SGMemberId from HB_tblExchangeServerGroupsMembers Where ServerGroupId=      
  (Select ServerGroupId  From HB_tblExchangeServerGroupsMembers Where SGMemberId=@SGMemberId)      
  )       
  )      
  )       
  )      
  and MailBoxDBName=(Select MailBoxDbName From [HB_tblExchangeMailboxes] Where [MailboxID] = @MailboxID_1) )-1)      
  Where SGMemberId=@SGMemberId      
      
      
DELETE [HB_tblExchangeMailboxes]       
      
WHERE       
( [MailboxID] = @MailboxID_1)      
GO

ALTER PROCEDURE [SP_EM_OCS_GetProviderIdByOCSHostingId]
(
                @OCSHostingID int
)
AS
                select distinct Hb_tblPackagesSold.PackageID, HB_tblOCSServerGroups.* 
                from Hb_tblPackagesSold, HB_tblOCSServerGroups, HB_tblOCSServerGroupMembers, HB_tblOCSHosting
                where HB_tblOCSServerGroups.ServerGroupID = HB_tblOCSServerGroupMembers.ServerGroupID
                and HB_tblOCSServerGroupMembers.SGMemberId = HB_tblOCSHosting.SGMemberID
                and HB_tblOCSHosting.SoldPackageID = Hb_tblPackagesSold.SoldPackageID
                and HB_tblOCSHosting.OCSHostingID=@OCSHostingID
GO

ALTER PROCEDURE [SP_EM_OCS_GetHostingInfo]
(
                @OCSHostingID                int
                
)
AS

SELECT  HB_tblOCSHosting.*, tblCustomers.*, HB_tblOCSServerGroups.ProviderID
from HB_tblOCSHosting,tblCustomers,HB_tblOCSServerGroups, HB_tblOCSServerGroupMembers,HB_tblPackagesSold
where HB_tblOCSServerGroups.ServerGroupID = HB_tblOCSServerGroupMembers.ServerGroupID
and HB_tblOCSServerGroupMembers.SGMemberId = HB_tblOCSHosting.SGMemberID
and HB_tblOCSHosting.SoldPackageID = HB_tblPackagesSold.SoldPackageID
and HB_tblOCSHosting.CustomerID = tblCustomers.CustomerID
and HB_tblOCSHosting.OCSHostingID=@OCSHostingID
GO
  
Alter Procedure CSP_Mailbox_GetDefaultLinkedMailboxSettings  
(@OrganizationId int)  
AS  
Select * FROm HB_tblExchangeMailboxes Where LinkedMasterAccount !=''  
and OrganizationId=@OrganizationId
Order By MailBoxId Desc  
GO

Alter PROCEDURE [SP_EM_OCS_GetHostingInfo]  
(  
 @OCSHostingID int  
   
)  
AS  

If ((Select SGMemberId from HB_tblOCSHosting Where OCSHostingID=@OCSHostingId)='0')

Begin

SELECT  HB_tblOCSHosting.*, tblCustomers.*, HB_tblOCSServerGroups.ProviderID
 from HB_tblOCSHosting,tblCustomers,HB_tblOCSServerGroups,HB_tblPackagesSold,HB_tblOCSPackagesToServerGroup
 where OCSHostingID=@OCSHostingID
 and HB_tblOCSHosting.SoldPackageID=HB_tblPackagesSold.SoldPackageID
 and HB_tblPackagesSold.PackageID = HB_tblOCSPackagesToServerGroup.PackageID
 and HB_tblOCSPackagesToServerGroup.ServerGroupID = HB_tblOCSServerGroups.ServerGroupID
 and HB_tblOCSHosting.CustomerID = tblCustomers.CustomerID

End
Else

Begin 
 SELECT  HB_tblOCSHosting.*, tblCustomers.*, HB_tblOCSServerGroups.ProviderID  
 from HB_tblOCSHosting,tblCustomers,HB_tblOCSServerGroups, HB_tblOCSServerGroupMembers,HB_tblPackagesSold  
 where HB_tblOCSServerGroups.ServerGroupID = HB_tblOCSServerGroupMembers.ServerGroupID  
 and HB_tblOCSServerGroupMembers.SGMemberId = HB_tblOCSHosting.SGMemberID  
 and HB_tblOCSHosting.SoldPackageID = HB_tblPackagesSold.SoldPackageID  
 and HB_tblOCSHosting.CustomerID = tblCustomers.CustomerID  
 and HB_tblOCSHosting.OCSHostingID=@OCSHostingID  
End 
GO


Alter PROCEDURE [SP_EM_W_GetWirelessAccountsByOrgId]    
(    
@OrganizationId nvarchar(10),    
@Name nvarchar (200),    
@Email nvarchar (300),    
@ProviderId nvarchar(10),    
@Status nvarchar(10)    
)    
As    
    
Declare @SQL nvarchar (4000)    
    
Set @SQL='    
Declare @CompanyId int     
'    
    
Set @SQL=@SQL+'   


 
;WITH CTE_HB_tblWirelessAccounts AS
(
   SELECT  HB_tblWirelessAccounts.UserId, HB_tblWirelessAccounts.MailBoxId, HB_tblWirelessAccounts.ProviderId, HB_tblWirelessAccounts.SGMemberId, HB_tblWirelessAccounts.CompanyId, 
                      HB_tblWirelessAccounts.DeviceType, HB_tblWirelessAccounts.PhoneNumber, HB_tblWirelessAccounts.ClientSoftwareVersion, HB_tblWirelessAccounts.OTAPIN, 
                      HB_tblWirelessAccounts.OTANumericPIN, HB_tblWirelessAccounts.ITPolicyStatus, HB_tblWirelessAccounts.ITPolicySent, HB_tblWirelessAccounts.ITPolicyRecieved, 
                      HB_tblWirelessAccounts.ForwardedMessages, HB_tblWirelessAccounts.SendMessages, HB_tblWirelessAccounts.PendingDataPackets, HB_tblWirelessAccounts.FilteredMessages, 
                      HB_tblWirelessAccounts.ExpiredMessages, HB_tblWirelessAccounts.LastForwardTime, HB_tblWirelessAccounts.LastSendTime, HB_tblWirelessAccounts.LastContactTime, 
                      HB_tblWirelessAccounts.LastResult, HB_tblWirelessAccounts.IsEnabled, HB_tblWirelessAccounts.DeviceStatus, HB_tblWirelessAccounts.LastUpdated, 
                       HB_tblExchangeMailboxes.ADUserID, HB_tblExchangeMailboxes.OrganizationID, HB_tblExchangeMailboxes.MailboxType, 
                      HB_tblExchangeMailboxes.MailBoxDisplayName, HB_tblExchangeMailboxes.SAMAccountName, HB_tblExchangeMailboxes.MailBoxPassword,  
                      HB_tblExchangeMailboxes.UserLDAPUrl, HB_tblExchangeMailboxes.ExchangeAlias, HB_tblExchangeMailboxes.HideFromOtherALs,  
                      HB_tblExchangeMailboxes.UPN, HB_tblExchangeMailboxes.MailBoxGUID, HB_tblExchangeMailboxes.Status,
                      HB_tblExchangeMailboxes.Disabled, HB_tblExchangeMailboxes.MailBoxDbName, 
                      HB_tblExchangeMailboxes.ManagerID, HB_tblExchangeMailboxes.CurrentUsage, HB_tblExchangeMailboxes.CurrentUsageTimeStamp, HB_tblExchangeMailboxes.SubscriptionSuspended, 
                      HB_tblExchangeMailboxes.LitigationHold, HB_tblExchangeMailboxes.LinkedDomainController, HB_tblExchangeMailboxes.LinkedMasterAccount, HB_tblExchangeMailboxes.LinkedAdminLogin, 
                      HB_tblExchangeMailboxes.LinkedAdminPassword, HB_tblExchangeMailboxes.LangCode, HB_tblExchangeMailboxes.MailBoxTemplateId, HB_tblExchangeMailboxes.TimeZone, 
                       HB_tblExchangeEmailAddresses.EmailAddressID, 
                      HB_tblExchangeEmailAddresses.EmailAddress, HB_tblExchangeEmailAddresses.IsPrimaryEmailAddress, HB_tblExchangeEmailAddresses.DateStamp, 
                       HB_tblWirelessProviders.Name, HB_tblWirelessProviders.Trans_Id, HB_tblWirelessProviders.IntegrationStatus
          ,
         ROW_NUMBER() OVER (PARTITION BY HB_tblWirelessAccounts.MailBoxId, HB_tblWirelessAccounts.ProviderId 
         ORDER BY UserId Asc) AS rn
   FROM 

 (HB_tblExchangeEmailAddresses Join HB_tblExchangeMailBoxes    
On HB_tblExchangeMailboxes.MailBoxId=HB_tblExchangeEmailAddresses.MailBoxId And IsPrimaryEmailAddress=''1'' 
And OrganizationId='''+@OrganizationId+'''    
and HB_tblExchangeMailBoxes.MailboxType=''User Mailbox''    
'    
    
if @Name !=''    
Begin    
Set @SQL=@SQL+' and MailBoxDisplayName like ''%''+'''+replace(@Name,'''','''''')+'''+''%'''    
End    
    
if @Email !=''    
Begin    
Set @SQL=@SQL+' and EmailAddress like ''%''+'''+replace(@Email,'''','''''')+'''+''%'''    
End    
    
if @Status='3'    
Begin    
 Set @SQL=@SQL+' and HB_tblExchangeMailboxes.MailBoxId Not In(Select MailBoxId From HB_tblWirelessAccounts)'    
End    
else if @Status='1'    
Begin    
 Set @SQL=@SQL+' and HB_tblExchangeMailboxes.MailBoxId  In(Select MailBoxId From HB_tblWirelessAccounts Where DeviceStatus='''+@Status+''')'    
End    
else if @Status='2'    
Begin    
 Set @SQL=@SQL+' and HB_tblExchangeMailboxes.MailBoxId In(Select MailBoxId From HB_tblWirelessAccounts Where DeviceStatus='''+@Status+''')'    
End    
    
    
Set @SQL=@SQL+'    
)    
Left JOIN (HB_tblWirelessAccounts Join HB_tblWirelessProviders On HB_tblWirelessAccounts.ProviderId=HB_tblWirelessProviders.ProviderId)    
ON HB_tblExchangeMailBoxes.MailBoxId=HB_tblWirelessAccounts.MailBoxId    
'    
    
if @Status!='-1'    
Begin    
 if @Status!='3'    
 Begin    
  Set @SQL=@SQL+' and DeviceStatus='''+@Status+''''    
 End    
End    
    
if @ProviderId!='-1'    
Begin    
Set @SQL=@SQL+' and HB_tblWirelessAccounts.ProviderId='''+@ProviderId+''''    
End    
      


Set @SQL=@SQL+' ) 
Select *
FROM CTE_HB_tblWirelessAccounts
     

WHERE rn = 1 and UserId is not null '


Set @SQL= @SQL + ' Order By MailBoxDisplayName'      
    
EXEC (@SQL)    
GO

Alter PROCEDURE [SP_EM_W_GetWirelessAccounts]    
(    
@CompanyId nvarchar(10),    
@Name nvarchar (200),    
@Email nvarchar (300),    
@ProviderId nvarchar(10),    
@Status nvarchar(10)    
)    
As    
    
Declare @SQL nvarchar (4000)    
    
Set @SQL='    
Declare @CompanyId int     
'    
    
Set @SQL=@SQL+'  Set @CompanyId='''+@CompanyId+''''    
    
Set @SQL=@SQL+'   


;WITH CTE_HB_tblWirelessAccounts AS
(
   SELECT  HB_tblWirelessAccounts.UserId, HB_tblWirelessAccounts.MailBoxId, HB_tblWirelessAccounts.ProviderId, HB_tblWirelessAccounts.SGMemberId, HB_tblWirelessAccounts.CompanyId, 
                      HB_tblWirelessAccounts.DeviceType, HB_tblWirelessAccounts.PhoneNumber, HB_tblWirelessAccounts.ClientSoftwareVersion, HB_tblWirelessAccounts.OTAPIN, 
                      HB_tblWirelessAccounts.OTANumericPIN, HB_tblWirelessAccounts.ITPolicyStatus, HB_tblWirelessAccounts.ITPolicySent, HB_tblWirelessAccounts.ITPolicyRecieved, 
                      HB_tblWirelessAccounts.ForwardedMessages, HB_tblWirelessAccounts.SendMessages, HB_tblWirelessAccounts.PendingDataPackets, HB_tblWirelessAccounts.FilteredMessages, 
                      HB_tblWirelessAccounts.ExpiredMessages, HB_tblWirelessAccounts.LastForwardTime, HB_tblWirelessAccounts.LastSendTime, HB_tblWirelessAccounts.LastContactTime, 
                      HB_tblWirelessAccounts.LastResult, HB_tblWirelessAccounts.IsEnabled, HB_tblWirelessAccounts.DeviceStatus, HB_tblWirelessAccounts.LastUpdated, 
                       HB_tblExchangeMailboxes.ADUserID, HB_tblExchangeMailboxes.OrganizationID, HB_tblExchangeMailboxes.MailboxType, 
                      HB_tblExchangeMailboxes.MailBoxDisplayName, HB_tblExchangeMailboxes.SAMAccountName, HB_tblExchangeMailboxes.MailBoxPassword,  
                      HB_tblExchangeMailboxes.UserLDAPUrl, HB_tblExchangeMailboxes.ExchangeAlias, HB_tblExchangeMailboxes.HideFromOtherALs,  
                      HB_tblExchangeMailboxes.UPN, HB_tblExchangeMailboxes.MailBoxGUID, HB_tblExchangeMailboxes.MailBoxDbName, 
                      HB_tblExchangeMailboxes.ManagerID, HB_tblExchangeMailboxes.CurrentUsage, HB_tblExchangeMailboxes.CurrentUsageTimeStamp, HB_tblExchangeMailboxes.SubscriptionSuspended, 
                      HB_tblExchangeMailboxes.LitigationHold, HB_tblExchangeMailboxes.LinkedDomainController, HB_tblExchangeMailboxes.LinkedMasterAccount, HB_tblExchangeMailboxes.LinkedAdminLogin, 
                      HB_tblExchangeMailboxes.LinkedAdminPassword, HB_tblExchangeMailboxes.LangCode, HB_tblExchangeMailboxes.MailBoxTemplateId, HB_tblExchangeMailboxes.TimeZone, 
                       HB_tblExchangeEmailAddresses.EmailAddressID, 
                      HB_tblExchangeEmailAddresses.EmailAddress, HB_tblExchangeEmailAddresses.IsPrimaryEmailAddress, HB_tblExchangeEmailAddresses.DateStamp, 
                       HB_tblWirelessProviders.Name, HB_tblWirelessProviders.Trans_Id, HB_tblWirelessProviders.IntegrationStatus
          ,
         ROW_NUMBER() OVER (PARTITION BY HB_tblWirelessAccounts.MailBoxId, HB_tblWirelessAccounts.ProviderId 
         ORDER BY UserId Asc) AS rn
   FROM HB_tblWirelessAccounts, HB_tblExchangeMailboxes, HB_tblExchangeEmailAddresses,HB_tblWirelessProviders    

WHERE 
    
 HB_tblWirelessAccounts.CompanyId=@CompanyId    
and HB_tblWirelessAccounts.MailBoxId=HB_tblExchangeMailboxes.MailBoxId    
and HB_tblExchangeMailboxes.MailBoxId=HB_tblExchangeEmailAddresses.MailBoxId    
and IsPrimaryEmailAddress=''1''     
and HB_tblWirelessAccounts.ProviderId=HB_tblWirelessProviders.ProviderId    
 '    
    
if @Name !=''    
Begin    
Set @SQL=@SQL+' and MailBoxDisplayName like ''%''+N'''+replace(@Name,'''','''''')+'''+''%'''    
End    
    
if @Email !=''    
Begin    
Set @SQL=@SQL+' and EmailAddress like ''%''+N'''+replace(@Email,'''','''''')+'''+''%'''    
End    
    
if @Status!='-1'    
Begin    
Set @SQL=@SQL+' and DeviceStatus='''+@Status+''''    
End    
    
if @ProviderId!='-1'    
Begin    
Set @SQL=@SQL+' and HB_tblWirelessAccounts.ProviderId='''+@ProviderId+''''    
End    

Set @SQL=@SQL+' ) 
Select *
FROM CTE_HB_tblWirelessAccounts
     

WHERE rn = 1 '
    
EXEC (@SQL)  
GO
