ALTER PROCEDURE [SP_EM_Lync_UpdatePhoneNumberForExtension]
(
	@NumberID	int
)
AS
	UPDATE HB_tblLyncPhoneNumbers
	SET IsExtensioned = '1', LyncUserId = '-1', AssignedTo=0
	WHERE NumberID = @NumberID
GO

ALTER PROCEDURE [SP_EM_OCS_GetUsersByOrganizationID_Lync]
(
	@OrganizationID	int,
	@SoldPackageId int,
	@SoldAddonId int,
	@FilterType int,
	@PhoneNumber nvarchar(50)
)
AS
Declare @SQL nvarchar(4000)
Declare @FType nvarchar(100)           
            
Set @SQL='
	SELECT HB_tblADirUsers.UPName as UserName, * from HB_tblLyncUsers,HB_tblADirUsers 
	WHERE HB_tblLyncUsers.ADUserId=HB_tblADirUsers.ADUserID'
Set @SQL= @SQL+' and OrgId = N'''+ replace(@OrganizationID,'''','''''')+''''      
If (@SoldPackageId!='0')            
	Set @SQL= @SQL+' and HB_tblLyncUsers.PackageAddonId LIKE N''P:'+ replace(@SoldPackageId,'''','''''')+''''      
else if (@SoldAddonId!='0')            
	Set @SQL= @SQL+' and HB_tblLyncUsers.PackageAddonId LIKE N''A:'+ replace(@SoldAddonId,'''','''''')+''''      
if (@PhoneNumber != '')
Begin
	if (@FilterType = 1)
		set @FType = ' = ''tel:' + @PhoneNumber + ''''
	else if (@FilterType = 2)
		set @FType = ' <> ''tel:' + @PhoneNumber + ''''
	else if (@FilterType = 3)
		set @FType = ' LIKE ''tel:' + @PhoneNumber + '%'''
	else if (@FilterType = 4)
		set @FType = ' LIKE ''%' + @PhoneNumber + ''''
	else if (@FilterType = 5)
		set @FType = ' LIKE ''%' + @PhoneNumber + '%'''
	else if (@FilterType = 6)
		set @FType = ' NOT LIKE ''%' + @PhoneNumber + '%'''
	Set @SQL = @SQL + ' and LineUri ' + @FType
End 
Exec (@SQL)
GO


IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'SP_EM_Lync_ResetExtensionBit') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [SP_EM_Lync_ResetExtensionBit]
GO

CREATE PROCEDURE [SP_EM_Lync_ResetExtensionBit]
(
	@Number	nvarchar(30)
)
AS
UPDATE HB_tblLyncPhoneNumbers set IsExtensioned='0' where (Number=@Number Or Number = '+' + @Number)
GO

ALTER PROCEDURE [SP_EM_Lync_UpdatePhoneNumberForExtension]
(
	@NumberID	int
)
AS
	UPDATE HB_tblLyncPhoneNumbers
	SET IsExtensioned = '1', LyncUserId = '-1', AssignedTo=0
	WHERE NumberID = @NumberID
GO

ALTER PROCEDURE [SP_EM_OCS_GetUsersByOrganizationID_Lync]
(
	@OrganizationID	int,
	@SoldPackageId int,
	@SoldAddonId int,
	@FilterType int,
	@PhoneNumber nvarchar(50)
)
AS
Declare @SQL nvarchar(4000)
Declare @FType nvarchar(100)           
            
Set @SQL='
	SELECT HB_tblADirUsers.UPName as UserName, * from HB_tblLyncUsers,HB_tblADirUsers 
	WHERE HB_tblLyncUsers.ADUserId=HB_tblADirUsers.ADUserID'
Set @SQL= @SQL+' and OrgId = N'''+ replace(@OrganizationID,'''','''''')+''''      
If (@SoldPackageId!='0')            
	Set @SQL= @SQL+' and HB_tblLyncUsers.PackageAddonId LIKE N''P:'+ replace(@SoldPackageId,'''','''''')+''''      
else if (@SoldAddonId!='0')            
	Set @SQL= @SQL+' and HB_tblLyncUsers.PackageAddonId LIKE N''A:'+ replace(@SoldAddonId,'''','''''')+''''      
if (@PhoneNumber != '')
Begin
	if (@FilterType = 1)
		set @FType = ' = ''tel:' + @PhoneNumber + ''''
	else if (@FilterType = 2)
		set @FType = ' <> ''tel:' + @PhoneNumber + ''''
	else if (@FilterType = 3)
		set @FType = ' LIKE ''tel:' + @PhoneNumber + '%'''
	else if (@FilterType = 4)
		set @FType = ' LIKE ''%' + @PhoneNumber + ''''
	else if (@FilterType = 5)
		set @FType = ' LIKE ''%' + @PhoneNumber + '%'''
	else if (@FilterType = 6)
		set @FType = ' NOT LIKE ''%' + @PhoneNumber + '%'''
	Set @SQL = @SQL + ' and LineUri ' + @FType
End 
Exec (@SQL)
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'SP_EM_Lync_ResetExtensionBit') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE SP_EM_Lync_ResetExtensionBit
GO
CREATE PROCEDURE [SP_EM_Lync_ResetExtensionBit]
(
	@Number	nvarchar(30)
)
AS
UPDATE HB_tblLyncPhoneNumbers set IsExtensioned='0' where (Number=@Number Or Number = '+' + @Number)
GO


Alter PROCEDURE [SP_EM_VPSProvidersGet]

AS 

BEGIN

       SELECT * FROM [HB_VPS_tblVPSProviders] 

END
GO

Update HB_VPS_tblVPSProviders set IsActive='1'
GO

IF NOT EXISTS (SELECT  * FROM sys.tables t , Sys.columns c Where t.object_id=c.object_id and c.name='Extension' and  t.name='HB_tblResponseGroupWorkflows')
BEGIN
	ALTER TABLE HB_tblResponseGroupWorkflows ADD Extension NVARCHAR(5) NULL DEFAULT('')
END
GO

Update HB_tblResponseGroupWorkflows
set Extension=''
GO

ALTER PROCEDURE [SP_EM_Insert_HB_tblResponseGroupWorkflows]
(
	@Identity [nvarchar](200),
	@OrgID [bigint],
	@WorkflowName [nvarchar](200),
	@Active [bit],
	@EnableForFederation [bit],
	@Anonymous [bit],
	@PrimaryUri [nvarchar](100),
	@LineUri [nvarchar](100),
	@Ext	[nvarchar](5),
	@DisplayNumber [nvarchar](100),
	@Description [nvarchar](4000),
	@IsManaged [bit],
	@ManagersByUri [nvarchar](4000),
	@Language [nvarchar](50),
	@EnableWelcomeMessage [bit],
	@WelcomeText [nvarchar](2000),
	@WelcomeAudioFile [nvarchar](200),
	@TimeZone [nvarchar](100),
	@BHoursID [nvarchar](1000),
	@EnableNBHoursMessage [bit],
	@NBHoursAction [int],
	@NBHoursActionValue [nvarchar](100),
	@HolidaySetID [nvarchar](2000),
	@EnableHolidayMessage [bit],
	@HolidayAction [int],
	@HolidayActionValue [nvarchar](100),
	@QueueID [nvarchar](2000),
	@OnholdAction [int],
	@OnholdAudioFile [nvarchar](200),
	@Template [nvarchar](100),
	@NBHoursText [nvarchar](2000),
	@NBHoursAudio [nvarchar](200),
	@HolidayText [nvarchar](2000),
	@HolidayAudio [nvarchar](200),
	@IVRText [nvarchar](2000),
	@IVRAudio [nvarchar](200)
)
AS 
INSERT INTO [HB_tblResponseGroupWorkflows] 
(
	[Identity],
	[OrgID],
	[WorkflowName],
	[Active],
	[EnableForFederation],
	[Anonymous],
	[PrimaryUri],
	[LineUri],
	[Extension],
	[DisplayNumber],
	[Description],
	[IsManaged],
	[ManagersByUri],
	[Language],
	[EnableWelcomeMessage],
	[WelcomeText],
	[WelcomeAudioFile],
	[TimeZone],
	[BHoursID],
	[EnableNBHoursMessage],
	[NBHoursAction],
	[NBHoursActionValue],
	[HolidaySetID],
	[EnableHolidayMessage],
	[HolidayAction],
	[HolidayActionValue],
	[QueueID],
	[OnholdAction],
	[OnholdAudioFile],
	[Template],
	[NBHoursText],
	[NBHoursAudio],
	[HolidayText],
	[HolidayAudio],
	[IVRText],
	[IVRAudio]
) 
VALUES 
(
	@Identity,
	 @OrgID,
	 @WorkflowName,
	 @Active,
	 @EnableForFederation,
	 @Anonymous,
	 @PrimaryUri,
	 @LineUri,
	 @Ext,
	 @DisplayNumber,
	 @Description,
	 @IsManaged,
	 @ManagersByUri,
	 @Language,
	 @EnableWelcomeMessage,
	 @WelcomeText,
	 @WelcomeAudioFile,
	 @TimeZone,
	 @BHoursID,
	 @EnableNBHoursMessage,
	 @NBHoursAction,
	 @NBHoursActionValue,
	 @HolidaySetID,
	 @EnableHolidayMessage,
	 @HolidayAction,
	 @HolidayActionValue,
	 @QueueID,
	 @OnholdAction,
	 @OnholdAudioFile,
	 @Template,
	 @NBHoursText,
	 @NBHoursAudio,
	 @HolidayText,
	 @HolidayAudio,
	 @IVRText,
	 @IVRAudio
)
Select @@Identity as RID
GO

ALTER PROCEDURE [SP_EM_Lync_GetAvailablePhoneNumbersByCustomerID_WorkFlow]
(
	@CustomerID	int
)
AS
	select HB_tblLyncPhoneNumbers.* from HB_tblLyncPhoneNumbers,HB_tblLyncPhoneNumbergroups
	where HB_tblLyncPhoneNumbers.phonegroupid=HB_tblLyncPhoneNumbergroups.groupid
	and HB_tblLyncPhoneNumbers.[status]=1
	and HB_tblLyncPhoneNumbers.assignedto=0
	and HB_tblLyncPhoneNumbergroups.customerid=@CustomerID
	and '+' + REPLACE(Number,'+','') not in (select LineUri from HB_tblResponseGroupWorkflows)
GO


IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'SP_EM_Lync_GetAvailablePhoneNumbersByCustomerID_EditWorkFlow') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [SP_EM_Lync_GetAvailablePhoneNumbersByCustomerID_EditWorkFlow]
GO

CREATE PROCEDURE [SP_EM_Lync_GetAvailablePhoneNumbersByCustomerID_EditWorkFlow]
(
	@CustomerID	int,
	@LineURI nvarchar(100)
)
AS
	select HB_tblLyncPhoneNumbers.* from HB_tblLyncPhoneNumbers,HB_tblLyncPhoneNumbergroups
	where HB_tblLyncPhoneNumbers.phonegroupid=HB_tblLyncPhoneNumbergroups.groupid
	and HB_tblLyncPhoneNumbers.[status]=1
	and HB_tblLyncPhoneNumbers.assignedto=0
	and HB_tblLyncPhoneNumbergroups.customerid=@CustomerID
	and '+' + REPLACE(Number,'+','') not in (select LineUri from HB_tblResponseGroupWorkflows where LineUri <> @LineURI)
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'SP_EM_Lync_MarkNumberAvailableByNumberId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [SP_EM_Lync_MarkNumberAvailableByNumberId]
GO


CREATE PROCEDURE [SP_EM_Lync_MarkNumberAvailableByNumberId]
(
	@NumberID	int
)
AS
	UPDATE HB_tblLyncPhoneNumbers
	SET AssignedTo = '0', LyncUserId='-1', IsExtensioned='0'
	WHERE NumberID=@NumberID
GO

ALTER PROCEDURE [SP_EM_Edit_HB_tblResponseGroupWorkflows]
(
	@Identity [nvarchar](200),
	@OrgID [bigint],
	@WorkflowName [nvarchar](200),
	@Active [bit],
	@EnableForFederation [bit],
	@Anonymous [bit],
	@PrimaryUri [nvarchar](100),
	@LineUri [nvarchar](100),
	@Extension [nvarchar](5),
	@DisplayNumber [nvarchar](100),
	@Description [nvarchar](4000),
	@IsManaged [bit],
	@ManagersByUri [nvarchar](4000),
	@Language [nvarchar](50),
	@EnableWelcomeMessage [bit],
	@WelcomeText [nvarchar](2000),
	@WelcomeAudioFile [nvarchar](200),
	@TimeZone [nvarchar](100),
	@BHoursID [nvarchar](1000),
	@EnableNBHoursMessage [bit],
	@NBHoursAction [int],
	@NBHoursActionValue [nvarchar](100),
	@HolidaySetID [nvarchar](2000),
	@EnableHolidayMessage [bit],
	@HolidayAction [int],
	@HolidayActionValue [nvarchar](100),
	@QueueID [nvarchar](2000),
	@OnholdAction [int],
	@OnholdAudioFile [nvarchar](200),
	@Template [nvarchar](100),
	@NBHoursText [nvarchar](2000),
	@NBHoursAudio [nvarchar](200),
	@HolidayText [nvarchar](2000),
	@HolidayAudio [nvarchar](200)
)
AS 
UPDATE [HB_tblResponseGroupWorkflows] 
SET [WorkflowName]=@WorkflowName,
	[Active]=@Active,
	[EnableForFederation]=@EnableForFederation,
	[Anonymous]=@Anonymous,
	[PrimaryUri]=@PrimaryUri,
	[LineUri]=@LineUri,
	[Extension]=@Extension,
	[DisplayNumber]=@DisplayNumber,
	[Description]=@Description,
	[IsManaged]=@IsManaged,
	[ManagersByUri]=@ManagersByUri,
	[Language]=@Language,
	[EnableWelcomeMessage]=@EnableWelcomeMessage,
	[WelcomeText]=@WelcomeText,
	[WelcomeAudioFile]=@WelcomeAudioFile,
	[TimeZone]=@TimeZone,
	[BHoursID]=@BHoursID,
	[EnableNBHoursMessage]=@EnableNBHoursMessage,
	[NBHoursAction]=@NBHoursAction,
	[NBHoursActionValue]=@NBHoursActionValue,
	[HolidaySetID]=@HolidaySetID,
	[EnableHolidayMessage]=@EnableHolidayMessage,
	[HolidayAction]=@HolidayAction,
	[HolidayActionValue]=@HolidayActionValue,
	[QueueID]=@QueueID,
	[OnholdAction]=@OnholdAction,
	[OnholdAudioFile]=@OnholdAudioFile,
	[Template]=@Template,
	[NBHoursText]=@NBHoursText,
	[NBHoursAudio]=@NBHoursAudio,
	[HolidayText]=@HolidayText,
	[HolidayAudio]=@HolidayAudio
WHERE [Identity]=@Identity AND [OrgId]=@OrgID
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'SP_EM_Lync_GetAllPhoneNumbers') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [SP_EM_Lync_GetAllPhoneNumbers]
GO


CREATE PROCEDURE [SP_EM_Lync_GetAllPhoneNumbers]
AS
SELECT * from HB_tblLyncPhoneNumbers
GO


IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'SP_EM_Lync_GetWorkflowByNumber') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [SP_EM_Lync_GetWorkflowByNumber]
GO

CREATE Procedure [SP_EM_Lync_GetWorkflowByNumber]
(
	@Number [nvarchar](100)
)
AS
Select * from HB_tblResponseGroupWorkflows Where [LineUri] = @Number
GO

DECLARE @CompanyId int
DECLARE GetAllCompanies Cursor FOR select CompanyId from tblCompanies
OPEN GetAllCompanies
	FETCH NEXT FROM GetAllCompanies INTO @CompanyId
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		Update HB_tblPackagesDetail set ResourceQuantity = '0' where ResourceID = (Select ResourceID from HB_tblResources where ResourceInternalID = '515' and CompanyId = @CompanyId)
		Update HB_tblPackagesSolddetail set ResourceQuantity = '0' where ResourceID = (Select ResourceID from HB_tblResources where ResourceInternalID = '515' and CompanyId = @CompanyId)
		Update HB_tblAddonsSoldDetail set ResourceQuantity = '0' where ResourceID = (Select ResourceID from HB_tblResources where ResourceInternalID = '515' and CompanyId = @CompanyId)
		Update HB_tblAddonTypeDetail set ResourceQuantity = '0' where ResourceID = (Select ResourceID from HB_tblResources where ResourceInternalID = '515' and CompanyId = @CompanyId)
		FETCH NEXT FROM GetAllCompanies INTO @CompanyId
	END
CLOSE GetAllCompanies
DEALLOCATE GetAllCompanies
GO

