· 8 years ago · May 23, 2018, 11:40 AM
1USE [iConIR3x_NewEngland]
2GO
3
4/****** Object: StoredProcedure [dbo].[validateMappingAgainstDestSnapshot] Script Date: 5/23/2018 7:36:20 AM ******/
5DROP PROCEDURE [dbo].[validateMappingAgainstDestSnapshot]
6GO
7
8/****** Object: StoredProcedure [dbo].[validateMappingAgainstDestSnapshot] Script Date: 5/23/2018 7:36:20 AM ******/
9SET ANSI_NULLS ON
10GO
11
12SET QUOTED_IDENTIFIER ON
13GO
14
15
16create procedure [dbo].[validateMappingAgainstDestSnapshot]
17 @system varchar(50) = null,
18 @database varchar(100) = null,
19 @linkedServer varchar(100) = null
20as
21begin
22 set nocount on;
23 if @database is not null
24 begin
25 exec raiseMessage 'validateMappingAgainstDestSnapshot: Taking new DestStructureSnapshot'
26 exec takeDestStructureSnapshot @database=@database, @linkedServer=@linkedServer
27 end
28 else
29 exec raiseMessage 'validateMappingAgainstDestSnapshot: @database not specificed. Bypassing takeDestStructureSnapshot'
30
31 select top 1 Snapshot as ShapshotDate from DestStructureSnapshotObjectType;
32
33 select 'Invalid Drawer' as Status,
34 DestDrawer, COUNT(*) as Mappings
35 from MapDocument
36 where System=isnull(@system,System)
37 and DestDrawer not in('Do Not Migrate','omit','delete')
38 and DestDrawer not in(
39 select locationName
40 from DestStructureSnapshotLocations
41 )
42 group by DestDrawer;
43
44 select 'Invalid FileType' as Status,
45 DestFileType, COUNT(*) as Mappings
46 from MapDocument
47 where System=isnull(@system,System)
48 and DestFileType not in('Do Not Migrate','omit','delete')
49 and DestFileType not in(
50 select typeProgrammaticName
51 from DestStructureSnapshotObjectType
52 where typeClassID=-3
53 )
54 group by DestFileType;
55
56 ;with FolderTypes as(
57 select distinct --DestFolderPath,
58 case
59 when DestFolderPath like '%\\%' then substring(DestFolderPath,1,charindex('\\',DestFolderPath,1)-1)
60 else DestFolderPath
61 end as CurrentNode,
62 case
63 when DestFolderPath like '%\\%' then substring(DestFolderPath,Charindex('\\',DestFolderPath,1)+2,10000)
64 else null
65 end as Remaining
66 from MapDocument
67 where System=isnull(@system,System)
68 union all
69 select CurrentNode, null as Remaining
70 from FolderTypes
71 where Remaining is not null
72 ),
73 MappedFolders as(
74 select
75 case
76 when 0<charindex(';',CurrentNode)
77 then SUBSTRING(CurrentNode,1,CHARINDEX(';',CurrentNode)-1)
78 else CurrentNode
79 end as FolderType
80 from FolderTypes
81 where CurrentNode not in('Do Not Migrate','omit','delete')
82 )
83 select 'Invalid FolderType' as Status,
84 FolderType
85 from MappedFolders
86 where MappedFolders.FolderType not in(
87 select typeName
88 from DestStructureSnapshotObjectType
89 where typeClassID=-2
90 )
91 group by FolderType;
92
93 select 'Invalid DocType' as Status,
94 DestDocType
95 from MapDocument
96 where System=isnull(@system,System)
97 and DestDocType not in('Do Not Migrate','omit','delete')
98 and DestDocType not in(
99 select typeProgrammaticName
100 from DestStructureSnapshotObjectType
101 where typeClassID=-1
102 )
103 group by DestDocType;
104
105 -- Show page marks that may not be allowed on the file type
106 /* Note that this is a fuzzy logic scenario and CURRENTLY VERY EXPERIMENTAL. There is not any file type in
107 the mark mapping, so there is not
108 a definitive method to determine what file type a mark needs to be migrated to from the mapping alone. In
109 order to do that, the processPageMarks must be executed first and the file types must be pulled from ProcessPage.
110 This query (below) doesn't do that. It takes a distinct list of all file types in the MapDocument table and
111 cross joins that to all of the page marks. This will return false negatives if the client doesnt' intend to add
112 all page marks to all file types.
113 The solution to validating mark mapping may be to first pre-process all marks and file types for pages that have marks.
114 */
115 select *
116 from MapMark
117 cross join (
118 select distinct
119 MapDocument.system,
120 MapDocument.DestFileType ,
121 DestStructureSnapshotObjectType.typeid
122 from MapDocument
123 join DestStructureSnapshotObjectType on MapDocument.DestFileType=DestStructureSnapshotObjectType.typeName
124 where typeClassID=-3
125 ) MapDestFileType
126 where MapMark.System=MapDestFileType.System
127 and exists(
128 select *
129 from DestStructureSnapshotMarkAllow
130 where MapMark.MarkType=DestStructureSnapshotMarkAllow.markType
131 and MapMark.DestMarkID=DestStructureSnapshotMarkAllow.pageMarkID
132 and MapDestFileType.typeid=DestStructureSnapshotMarkAllow.typeid
133 )
134 order by DestMarkDesc
135
136
137 -- Validate Flows
138 select 'Invalid Flow' as Status,
139 *
140 from MapTask
141 where System=isnull(@system,System)
142 and DestFlowProgName not in('','Do Not Migrate')
143 and not exists(
144 select *
145 from DestStructureSnapshotWorkFlow
146 where MapTask.DestFlowProgName=DestStructureSnapshotWorkFlow.FlowProgName
147 )
148 order by DestFlowProgName
149 -- Validate Steps
150 select 'Invalid Step' as Status,
151 *
152 from MapTask
153 where System=isnull(@system,System)
154 and DestFlowProgName not in('','Do Not Migrate')
155 and not exists(
156 select *
157 from DestStructureSnapshotWorkFlow
158 where MapTask.DestStepProgName=DestStructureSnapshotWorkFlow.StepProgName
159 )
160 order by DestStepProgName
161 -- Validate Flow\Step combinations - may be invalid due to invalid flow or step as noted by prior queries
162 select 'Invalid Flow\Step Combination' as Status,
163 *
164 from MapTask
165 where System=isnull(@system,System)
166 and DestFlowProgName<>'Do Not Migrate'
167 and not exists(
168 select *
169 from DestStructureSnapshotWorkFlow
170 where MapTask.DestFlowProgName=DestStructureSnapshotWorkFlow.FlowProgName
171 and MapTask.DestStepProgName=DestStructureSnapshotWorkFlow.StepProgName
172 )
173 and DestFlowProgName<>''
174 order by DestFlowProgName, DestStepProgName
175
176 -- TODO: Validate WF User Mappings
177 -- select 'Invalid DocType' as Status,
178 -- *
179 -- from bleh
180 -- where bleh
181end
182
183
184GO