8wBLnfZn

1BEGIN
2    DECLARE @SiteSettingsTopicTypeId INT
3    DECLARE @DocumentTopicTypeId INT
4    SELECT 
5        @DocumentTopicTypeId = CASE WHEN t.TypeName = N'Document' THEN TopicTypeId ELSE @DocumentTopicTypeId END,
6        @SiteSettingsTopicTypeId = CASE WHEN t.TypeName = N'SiteSettings' THEN TopicTypeId ELSE @SiteSettingsTopicTypeId END
7    FROM TopicType t
8
9    IF NOT EXISTS
10                (
11                    SELECT *
12                    FROM dbo.Topic t
13                    INNER JOIN dbo.Site s ON s.SiteId = t.SiteId AND s.SiteTypeId = 2 --system
14                    INNER JOIN dbo.TopicFields tf ON tf.TopicId = t.TopicId AND tf.FieldName = N'IzendaReports.DynamicDatasourceIncludedFields' AND ISNULL(tf.Fieldvalue, N'') = N''
15                    WHERE t.TopicName = N'Reports'AND t.TopicTypeId = @SiteSettingsTopicTypeId
16                )
17    BEGIN
18
19        DECLARE @ErrorCode INT
20        DECLARE @SystemUserId INT
21        SET @SystemUserId = dbo.GetDomainSystemUserId(1, null)
22        DECLARE @SiteId INT = (Select MIN(SiteId) FROM dbo.Site s WHERE s.SiteTypeId = 2 )--system
23        
24        IF NOT EXISTS (
25            SELECT *
26            FROM dbo.Topic t
27            WHERE t.SiteId = @SiteId AND t.TopicName = N'Reports'AND t.TopicTypeId = @SiteSettingsTopicTypeId 
28        )
29        BEGIN
30            CREATE TABLE #TopicsToCreate (VirtualTopicId INT, TopicName NVARCHAR(2000) PRIMARY KEY, ParentId INT, AutoHistory BIT DEFAULT(0), VersionId INT)
31            INSERT INTO #TopicsToCreate(TopicName, VirtualTopicId, ParentId)
32            SELECT 'Reports', 1, (SELECT MIN(TopicId) FROM Topic WHERE TopicTypeId = @SiteSettingsTopicTypeId AND ParentId IS NULL)
33
34            EXEC dbo.CreateTopics @SiteId = @SiteId,
35									      @UserId = @SystemUserId,
36									      @TopicTypeId = @SiteSettingsTopicTypeId,
37									      @TopicTypeName = N'SiteSettings',
38									      @TopicsXml = NULL,
39									      @FieldsXml = NULL,
40									      @ErrorCode = @ErrorCode OUT
41            DROP TABLE #TopicsToCreate
42        END 
43
44        CREATE TABLE #allTopicFields(FieldName NVARCHAR(150), FieldType INT, id INT IDENTITY(1,1))  
45        EXEC dbo.GetAllFormsFields @TopicTypeId = @DocumentTopicTypeId, @ResultTableName = N'#allTopicFields'
46
47        DECLARE @DynamicDatasourceIncludedFields NVARCHAR(MAX)
48        SET @DynamicDatasourceIncludedFields = ISNULL
49            (
50                (
51                    SELECT FieldName + N',' From #allTopicFields
52                    WHERE FieldName NOT IN (
53                    SELECT ef.Data
54                    FROM dbo.TopicFields tf
55                    INNER JOIN dbo.Topic t ON tf.TopicId = t.TopicId
56                        AND TopicName = N'Reports'
57                        AND TopicTypeId = @SiteSettingsTopicTypeId
58                    INNER JOIN dbo.Site s ON s.SiteId = t.SiteId
59                        AND s.SiteTypeId = 2 --system
60                    CROSS APPLY dbo.Split(tf.FieldValue, N',') ef 
61                    WHERE tf.FieldName = N'IzendaReports.DynamicDatasourceExcludedFields'
62                    )
63                    FOR XML PATH(''), TYPE
64                ).value('.', 'NVARCHAR(MAX)'), ''
65            );
66
67        IF ISNULL(@DynamicDatasourceIncludedFields, '') <> ''
68            SELECT @DynamicDatasourceIncludedFields = SUBSTRING(@DynamicDatasourceIncludedFields, 1, LEN(@DynamicDatasourceIncludedFields)-1)
69
70        CREATE TABLE #EditTopicFields_TopicFields
71            (
72                TopicId INT,
73                FieldId Int, 
74                FieldName NVARCHAR(50), 
75                Fieldvalue NVARCHAR(Max), 
76                FieldType INT, 
77                SortOrder INT IDENTITY(1,1),
78                UpdateType INT DEFAULT 1,
79                StringToReplace NVARCHAR(MAX),
80                Delimiter NVARCHAR(50),
81                UpdateReason NVARCHAR(MAX)
82                PRIMARY KEY (TopicId, FieldName)
83            )
84
85        INSERT INTO #EditTopicFields_TopicFields(TopicId, FieldId, FieldName, Fieldvalue, FieldType, UpdateReason)
86        SELECT t.TopicId, tf.FieldId, N'IzendaReports.DynamicDatasourceIncludedFields', @DynamicDatasourceIncludedFields, 8, N'' 
87        FROM dbo.Topic t
88        INNER JOIN dbo.Site s ON s.SiteId = t.SiteId AND s.SiteTypeId = 2 --system
89        LEFT JOIN dbo.TopicFields tf ON tf.TopicId = t.TopicId AND tf.FieldName = N'IzendaReports.DynamicDatasourceIncludedFields'
90        WHERE t.TopicName = N'Reports'AND t.TopicTypeId = @SiteSettingsTopicTypeId
91
92        CREATE TABLE #ErrorCodes
93        (
94            ObjectId INT,
95            ObjectName NVARCHAR(400),
96            ObjectType NCHAR(1),
97            ErrorCode INT
98        )
99
100        EXEC dbo.SaveTopicFields
101            @SiteId = @SiteId, -- int
102            @UserId = @SystemUserId, -- int
103            @UpdateSiteMap = 0, -- bit
104            @TopicFieldsInfo = NULL, -- xml
105            @SkipSecurity = 0, -- bit
106            @SkipTopicsArchive = 0, -- bit
107            @OutputTmpTableName = N'#ErrorCodes', -- nvarchar(150)
108            @ErrorCode = @ErrorCode OUT  -- int
109
110        DROP TABLE #allTopicFields
111        DROP TABLE #EditTopicFields_TopicFields
112        DROP TABLE #ErrorCodes
113    END 
114END
115GO