/*
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
*/

/*
To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, one of the following two conditions must be met:

1. The assembly is strong name signed or Authenticode signed with a certificate. This strong name (or certificate) 
   is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with 
   EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies). 
2. The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or 
   UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON. 
*/

USE <DatabaseName, , >
GO

DROP FUNCTION Integration.Invoke
DROP FUNCTION Integration.InvokeQuery
DROP FUNCTION Integration.GetData
DROP FUNCTION System.GetFormattedEntity
DROP FUNCTION System.GetEntityAsXml
DROP FUNCTION System.GetEntityAsCsv
DROP FUNCTION System.GetFormattedDataset
DROP FUNCTION System.GetCleanStringASCII
DROP FUNCTION System.GetCleanXmlASCII
DROP FUNCTION Crm.Fn_GetFormattedMessages
DROP FUNCTION System.Fn_IsValidRsaId
DROP FUNCTION System.Fn_IsValidRsaCompanyRegNum
DROP FUNCTION System.Fn_GetDistanceBetweenPoints

DROP ASSEMBLY [Neo.Afx.SqlServer]
DROP ASSEMBLY [System.Xml.Linq]
DROP ASSEMBLY [System.Core]
GO

ALTER DATABASE <DatabaseName, , > SET TRUSTWORTHY ON
EXEC sp_changedbowner 'sa';

DECLARE @Version VARCHAR(50), @MajorVersion INT
SELECT 
    @Version = CAST(SERVERPROPERTY('productversion') AS VARCHAR(50))
    ,@MajorVersion = CAST(LEFT( @Version, CHARINDEX ( '.', @Version ) - 1 ) AS INT)

IF @MajorVersion = 9  -- SQL 2005
BEGIN
    CREATE ASSEMBLY [System.Core]
    AUTHORIZATION [dbo]
    FROM 'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
    WITH PERMISSION_SET = UNSAFE;

    CREATE ASSEMBLY [System.Xml.Linq]
    AUTHORIZATION [dbo]
    FROM 'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\System.Xml.Linq.dll'
    WITH PERMISSION_SET = UNSAFE;
END

CREATE ASSEMBLY [Neo.Afx.SqlServer]
AUTHORIZATION dbo  
FROM 'E:\MSSQL\CLR\Neo.Afx.SqlServer.dll' 
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

GO
CREATE FUNCTION Integration.Invoke
(
    @Name NVARCHAR(100)
    ,@QueryString NVARCHAR(MAX)
)
RETURNS XML
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Integration.DataSource].Invoke
GO
GRANT EXECUTE ON Integration.Invoke TO <LoginUserName, , >
GO

GO
CREATE FUNCTION Integration.InvokeQuery
(
    @Name NVARCHAR(100)
    ,@RequestQuery NVARCHAR(MAX)
)
RETURNS XML
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Integration.DataSource].InvokeQuery
GO
GRANT EXECUTE ON Integration.InvokeQuery TO <LoginUserName, , >
GO

GO
CREATE FUNCTION Integration.GetData
(
    @Name NVARCHAR(100)
    ,@QueryString NVARCHAR(MAX)
)
RETURNS XML
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Integration.DataSource].GetData
GO
GRANT EXECUTE ON Integration.GetData TO <LoginUserName, , >
GO

GO
CREATE FUNCTION System.GetFormattedEntity
(
    @EntityID INT
    ,@ItemID INT
    ,@FormatString NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Sys.EntityFormatter].GetFormattedEntity
GO
GRANT EXECUTE ON System.GetFormattedEntity TO <LoginUserName, , >
GO

GO
CREATE FUNCTION System.GetEntityAsXml
(
    @EntityID INT
    ,@ItemID INT
)
RETURNS XML
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Sys.EntityFormatter].GetEntityAsXml
GO
GRANT EXECUTE ON System.GetEntityAsXml TO <LoginUserName, , >
GO

GO
CREATE FUNCTION System.GetEntityAsCsv
(
    @EntityID INT
    ,@ItemID INT
)
RETURNS VARBINARY(MAX)
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Sys.EntityFormatter].GetEntityAsCsv
GO
GRANT EXECUTE ON System.GetEntityAsCsv TO <LoginUserName, , >
GO

GO
CREATE FUNCTION System.GetFormattedDataSet
(
    @sql NVARCHAR(MAX)
    ,@FormatString NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Sys.DatasetFormatter].GetFormattedDataSet
GO
GRANT EXECUTE ON System.GetFormattedDataSet TO <LoginUserName, , >
GO

GO
CREATE FUNCTION System.GetCleanStringASCII
(
    @input NVARCHAR(MAX)
    ,@cleanWith NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Sys.EntityFormatter].GetCleanStringASCII
GO
GRANT EXECUTE ON System.GetCleanStringASCII TO <LoginUserName, , >
GO

GO
CREATE FUNCTION System.GetCleanXmlASCII
(
    @xml XML
    ,@cleanWith NVARCHAR(MAX)
)
RETURNS XML
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Sys.EntityFormatter].GetCleanXmlASCII
GO
GRANT EXECUTE ON System.GetCleanXmlASCII TO <LoginUserName, , >
GO

GO
CREATE FUNCTION Crm.Fn_GetFormattedMessages
(
   @RecipientSql NVARCHAR(MAX), 
   @DataSql NVARCHAR(MAX), 
   @Subject NVARCHAR(MAX), 
   @MessageTemplateStart NVARCHAR(MAX), 
   @MessageTemplateData NVARCHAR(MAX), 
   @MessageTemplateEnd NVARCHAR(MAX)
)
RETURNS TABLE (
   Recipients NVARCHAR(MAX), 
   Cc NVARCHAR(MAX), 
   Bcc NVARCHAR(MAX), 
   Subject NVARCHAR(1000), 
   Message NVARCHAR(MAX)
)
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Crm.MessageFormatter].GetFormattedMessages
GO


CREATE FUNCTION System.Fn_IsValidRsaId
(
   @idNumber NVARCHAR(MAX) 
)
RETURNS BIT
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Validation.RsaIdValidation].IsValidRsaIdNumber
GO

CREATE FUNCTION System.Fn_IsValidRsaCompanyRegNum
(
   @companyRegNumber NVARCHAR(MAX) 
)
RETURNS BIT
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Validation.RsaCompanyRegNumberValidation].IsValidRsaCompanyRegNum
GO



CREATE FUNCTION System.Fn_GetDistanceBetweenPoints
(
   @fromLongitude FLOAT,
   @fromLatitude FLOAT,
   @toLongitude FLOAT,
   @toLatitude FLOAT
)
RETURNS FLOAT
AS EXTERNAL NAME [Neo.Afx.SqlServer].[Neo.Afx.Helpers.GeoLocationHelper].GetDistanceBetweenPoints
GO

