
--настариваем базу
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
--done

alter database D10Task set trustworthy on

-- D10TaskUser пользователь по умолчанию, от имени которого происходит подключение приложения к БД 
use master
go
GRANT EXTERNAL ACCESS ASSEMBLY TO [D10TaskUser]
GRANT UNSAFE ASSEMBLY TO [D10TaskUser]

use D10Task
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false

if exists (select 1 from sys.assemblies where name = 'TCSqlFunctions')
begin
	if OBJECT_ID(N'dbo.tc_f_ChangeTimeZone') IS not NULL 
	begin
		DROP FUNCTION tc_f_ChangeTimeZone
	end
	if OBJECT_ID(N'dbo.tc_f_DiffWorkingMinutes') IS not NULL 
	begin
		DROP FUNCTION tc_f_DiffWorkingMinutes
	end

	if exists (select 1 from sys.assemblies where name = 'TCSqlFunctions.XmlSerializers')
	begin
		DROP ASSEMBLY [TCSqlFunctions.XmlSerializers]
	end

	DROP ASSEMBLY TCSqlFunctions
end
go


create ASSEMBLY TCSqlFunctions
FROM 'C:\Program Files (x86)\TCSQL\TCSqlFunctions.dll'
WITH PERMISSION_SET = UNSAFE;

create ASSEMBLY [TCSqlFunctions.XmlSerializers.dll]
FROM 'C:\Program Files (x86)\TCSQL\TCSqlFunctions.XmlSerializers.dll'
WITH PERMISSION_SET = UNSAFE;
go


CREATE FUNCTION tc_f_ChangeTimeZone(
	@dateTime datetime,
	@fromTimeZoneName nvarchar(100),
	@toTimeZoneName nvarchar(100))
RETURNS datetime
WITH EXECUTE AS CALLER
AS
	EXTERNAL NAME [TCSqlFunctions].[UserDefinedFunctions].[ChangeTimeZone]
go


create function tc_f_DiffWorkingMinutes( 
	@from datetime, 
	@to datetime)
RETURNS INT 
AS
	EXTERNAL NAME [TCSqlFunctions].[UserDefinedFunctions].[GetWorkingMinutes]
go