DT0u7Hgg

1--  Version 2.4.04
2if (db_name() <> 'iConIR3x')
3  begin
4    raiserror('Database Name must be "iConIR3x".  This script should only be run on that!',16,1)
5  end
6
7ALTER DATABASE iConIR3x SET RECOVERY SIMPLE;
8
9ALTER DATABASE iConIR3x
10  MODIFY FILE (NAME=iConIR3x, FILEGROWTH=500MB);
11
12ALTER DATABASE iConIR3x
13  MODIFY FILE (NAME=iConIR3x_Log, FILEGROWTH=200MB);  
14
15Alter DATABASE iConIR3x SET ALLOW_SNAPSHOT_ISOLATION ON
16Alter DATABASE iConIR3x SET READ_COMMITTED_SNAPSHOT ON 
17  
18GO
19SET ANSI_NULLS ON
20GO
21SET QUOTED_IDENTIFIER ON
22GO
23SET ANSI_PADDING ON
24GO
25GO
26IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[showVersion]') AND type in (N'P', N'PC'))
27  Drop Procedure [dbo].[showVersion]
28GO
29  Create Procedure [dbo].[showVersion]
30  as
31  begin
32    raiserror('Current Icon Script Build is Version: 2.4.04',10,1)
33    select Version = '2.4.04'
34  end
35GO
36IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Config3xMarkLookup]') AND type in (N'U'))
37Begin
38  --Config3xMarkLookup is intended to be a static lookup table in the IR3x component of the MigrationFramework.
39create table Config3xMarkLookup(
40  color int,
41  colorName varchar(50)
42  )
43end -- Used to close the "begin" block that is created by the build script. The remaining part of the script should be executed regardless if the ConfigMigration table exists before the test for existance.
44
45begin -- This "begin" will not have a corresponding "end" in this script because it will be added by the buildInstall.ps1.
46
47set nocount on;
48  -- Populate the table
49merge Config3xMarkLookup
50using (
51	values
52		(1 ,'RED'        ),
53		(2 ,'BLACK'      ),
54		(3 ,'MAROON'     ),
55		(4 ,'GREEN'      ),
56		(5 ,'OLIVE GREEN'),
57		(6 ,'NAVY BLUE'  ),
58		(7 ,'PURPLE'     ),
59		(8 ,'TEAL'       ),
60		(9 ,'DARK GREY'  ),
61		(10,'LIGHT GRAY' ),
62		(11,'NEON GREEN' ),
63		(12,'YELLOW'     ),
64		(13,'BLUE'       ),
65		(14,'FUSCHIA'    ),
66		(15,'AQUA'       ),
67		(16,'WHITE'      )
68	)as Marks(color,colorName)
69	on Config3xMarkLookup.color=Marks.color
70when matched then update set Config3xMarkLookup.colorName=Marks.colorName
71when not matched by target then insert(color,colorName) values(Marks.color,Marks.colorName);
72End
73GO
74IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigDeviceList]') AND type in (N'U'))
75Begin
76CREATE TABLE [dbo].[ConfigDeviceList]
77(
78	[System] [varchar](50) NOT NULL,
79	[OldDevicePath] [varchar](200) NOT NULL,
80	[NewDevicePath] [varchar](200) NULL,
81PRIMARY KEY CLUSTERED 
82(
83	[System] ASC,
84	[OldDevicePath] ASC
85)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
86) ON [PRIMARY]
87End
88GO
89IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigMigration]') AND type in (N'U'))
90Begin
91create table dbo.ConfigMigration
92(	
93	ConfigKey varchar(500),
94	ConfigValue varchar(500),
95	ConfigDescription varchar(max),	-- Description for the key\value pair. Please provice usage context such as what procedure\function\area of the migration process will use this and what the effect of changing the value would be.
96	ConfigValueDefault varchar(500)
97);
98end -- Used to close the "begin" block that is created by the build script. The remaining part of the script should be executed regardless if the ConfigMigration table exists before the test for existance.
99else 
100begin
101	if not exists(
102		select * 
103		from INFORMATION_SCHEMA.columns 
104		where TABLE_SCHEMA='dbo' 
105			and table_name='ConfigMigration' 
106			and COLUMN_NAME='ConfigDescription'
107		)
108		alter table ConfigMigration add ConfigDescription varchar(max);
109	if not exists(
110		select * 
111		from INFORMATION_SCHEMA.columns 
112		where TABLE_SCHEMA='dbo' 
113			and table_name='ConfigMigration' 
114			and COLUMN_NAME='ConfigValueDefault'
115		)
116		alter table ConfigMigration add ConfigValueDefault varchar(500);
117end
118
119begin -- This "begin" will not have a corresponding "end" in this script because it will be added by the buildInstall.ps1.
120	-- Insert values to ConfigMigration	
121	/* Use a temp table to insert the initial values into and only insert the records to ConfigMigration if the configkey record 
122		doesn't already exist. This will prevent record duplication or changing of intentionally set values. 
123	*/
124	if(object_id('tempdb..#configMigration') is not null)
125		drop table #configMigration;
126	create table #configMigration
127	(	
128		ConfigKey varchar(500),
129		ConfigValue varchar(500),
130		ConfigDescription varchar(max)	-- Description for the key\value pair. Please provice context such as what procedure\function\area of the migration process will use this. Specify the default value.
131	);
132
133	set nocount on
134		-- Initial ConfigMigration key\value pairs
135	insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription) 
136		values('FolderTaskPagePath',
137			'\\ws-image\e$\FolderTaskPage.txt',
138			'ProcessPages: Path to page to use for tasks that are attached directly to folders.');
139	insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription) 
140	  values('FileTaskPagePath',
141		'\\ws-image\e$\FileTaskPage.txt',
142		'ProcessPages: Path to page to use for tasks that are attached directly to files');
143		-- Path to page to use for tasks that are attached directly to documents when no pages in them.  
144	insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription) 
145	  values('EmptyDocumentTaskPagePath',
146		'\\ws-image\e$\EmptyDocumentTaskPage.txt',
147		'ProcessPages: Path to page to use for tasks that are attached directly to documents when no pages in them.'); 
148	insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription) 
149	  values('TakeSnapshot_GernerateSnapshotBatches',
150		'FALSE',
151		'TakeSnapshot: Determines if the SnapshotBatches table is generated by the procedure. "TRUE"=generated; "FALSE"=not generated.'); 
152	insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription) 
153	  values('process_ExecuteProcessAttributesProc',
154		'TRUE',
155		'process (stored proc): Determines if the processAttributes proc will be executed. "TRUE"=Executed; "FALSE"=not executed; Set to TRUE by default so that it will throw the "could not file stored procedure..." error to make the user consider if this should be handled or not.'); 
156	insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription) 
157	  values('defaultMigrationPriority',
158		9,
159		'Value to be used as the default migration priority in loadDocuments and loadFiles stored procs.'); 
160    
161	/*	--Legacy\concept configs. Keeping these here for now for examples and potentially resurecting their purpose.
162	insert ConfigMigration(ConfigKey,ConfigValue) 
163		values('mapDrawerDocuments_Map_FolderTypePathsAttrs_Max_IRAttributeField',15)	-- Max IRAttribute field to map against. Ex. 0: Do not map against IRAttributeX fields, 1: IRAttribute1, 5:IRAttribute5
164	insert ConfigMigration(ConfigKey,ConfigValue) 
165		values('mapDrawerDocuments_Map_IRFileName',1)-- Map against IRFileName or no. Ex. 0: Do NOT map against IRFileName field, 1: DO map against IRFileName
166	insert ConfigMigration(ConfigKey,ConfigValue) 
167		values('mapDrawerDocuments_Map_DocTypeAttrs_Max_IRAttributeField',15)	-- Max IRAttribute field to map against. Ex. 0: Do not map against IRAttributeX fields, 1: IRAttribute1, 5:IRAttribute5
168	*/
169	insert ConfigMigration(
170		ConfigKey,
171		ConfigValue,
172		ConfigDescription,
173		ConfigValueDefault
174		)
175	select ConfigKey,
176		ConfigValue,
177		ConfigDescription,
178		ConfigValue as ConfigValueDefault	-- using initial value
179	from #ConfigMigration
180	where not exists(
181		select *
182		from ConfigMigration
183		where #ConfigMigration.ConfigKey=ConfigMigration.ConfigKey
184		);
185--"end" is implied and will be added by the build script. Do not uncomment this last line.
186End
187GO
188IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigDrawerList]') AND type in (N'U'))
189Begin
190  CREATE TABLE [dbo].[ConfigDrawerList]
191  (
192    [system] [varchar](50) NOT NULL,
193    [drawer] [varchar](100) NOT NULL,    
194    [purge] varchar(10) not null,
195    [purgedate] datetime null,    
196    Constraint [PK_ConfigDrawer] Primary Key Clustered 
197    (
198      system,
199      drawer,
200      purge
201    )
202  ) ON [PRIMARY]
203
204  ALTER TABLE [dbo].[ConfigDrawerList]
205    ADD CONSTRAINT chkPurge CHECK ([purge] in ('All','None','Files','Date'))
206End
207GO
208IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigPurgeList]') AND type in (N'U'))
209Begin
210  CREATE TABLE [dbo].[ConfigPurgeList]
211  (
212    [System] varchar(20) NOT NULL,
213    [SrcDrawer] [varchar](4) NOT NULL,
214    [SrcFileNumber] [varchar](50) NOT NULL,
215   CONSTRAINT [PK_ConfigPurgeList] PRIMARY KEY CLUSTERED 
216    (
217      [System] ASC,
218      [SrcDrawer] ASC,
219      [SrcFileNumber] ASC
220    )
221    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
222  ) ON [PRIMARY]
223End
224GO
225IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigStatus]') AND type in (N'U'))
226Begin
227  CREATE TABLE [dbo].[ConfigStatus]
228  (
229       [Status] [int] NOT NULL,
230       [Description] [varchar](255) NOT NULL,
231  CONSTRAINT [PK_ConfigStatus] PRIMARY KEY CLUSTERED 
232  (
233     [Status] ASC
234  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
235  ) ON [PRIMARY]
236  set nocount on
237  INSERT INTO ConfigStatus (Status, Description) VALUES (-9, 'Purge')
238  INSERT INTO ConfigStatus (Status, Description) VALUES (-5, 'On Hold')
239  INSERT INTO ConfigStatus (Status, Description) VALUES (-2, 'Loaded')
240  INSERT INTO ConfigStatus (Status, Description) VALUES (-1, 'Errored')
241  INSERT INTO ConfigStatus (Status, Description) VALUES (0, 'Pending')
242  INSERT INTO ConfigStatus (Status, Description) VALUES (1, 'Success')
243  INSERT INTO ConfigStatus (Status, Description) VALUES (2, 'Processing')
244End
245GO
246IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigPriority]') AND type in (N'U'))
247Begin
248  CREATE TABLE [dbo].[ConfigPriority]
249  (
250    [System] varchar(20) NOT NULL,
251    [Priority] int NOT NULL,
252    [Rule] varchar(10) NOT NULL,
253    [Description] varchar(200) NOT NULL,    
254    [SrcDrawer] [varchar](4) NULL,
255    [SrcFileNumber] [varchar](50) NULL,
256    [filedate] datetime NULL    
257  )
258  
259  Alter table [dbo].[ConfigPriority]  
260    ADD CONSTRAINT chkConfigPriority_Rule CHECK ([rule] in ('Drawer','DocDate','TaskDocs','TaskFiles','File','Sample'))
261End
262GO
263IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigPageCorrection]') AND type in (N'U'))
264Begin
265  CREATE TABLE [dbo].[ConfigPageCorrection]
266  (
267    [fileid] varchar(100) NOT NULL,
268    [OldFilePath] varchar(255) NOT NULL,
269    [NewFilePath] varchar(255) NULL    
270   CONSTRAINT [PK_ConfigPageCorrection] PRIMARY KEY CLUSTERED 
271    (
272      [fileid] ASC,
273      [OldFilePath] ASC      
274    )
275    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
276  ) ON [PRIMARY]
277End
278GO
279IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPage]') AND type in (N'U'))
280Begin
281CREATE TABLE [dbo].[MigrationPage]
282(
283   [ID] [bigint] IDENTITY(1,1) NOT NULL,
284   [DocumentID] varchar(100) NOT NULL,
285   [FileID] varchar(100) NOT NULL,
286   [FilePath] [varchar](255) NULL,
287   [AnnotationsPath] [varchar](255) NULL,
288   [FilePathExists] [int] NULL,
289   [IRDrawer] [varchar](100) NULL,
290   [IRFileType] [varchar](100) NULL,
291   [IRFileNumber] [varchar](40) NULL,
292   [IRFileName] [varchar](100) NULL,
293   [IRFolderType] [varchar](255) NULL,
294   [IRDocType] [varchar](100) NULL,
295   [IRDocID] [int] NULL,
296   [IRPageMark] [int] NULL,
297   [IRDocDate] [datetime] NULL,
298   [IRPageDescription] [varchar](255) NULL,
299   [IRAttribute1] [varchar](200) NULL,
300   [IRAttribute2] [varchar](200) NULL,
301   [IRAttribute3] [varchar](200) NULL,
302   [IRAttribute4] [varchar](200) NULL,
303   [IRAttribute5] [varchar](200) NULL,
304   [IRAttribute6] [varchar](200) NULL,
305   [IRAttribute7] [varchar](200) NULL,
306   [IRAttribute8] [varchar](200) NULL,
307   [IRAttribute9] [varchar](200) NULL,
308   [IRAttribute10] [varchar](200) NULL,
309   [IRAttribute11] [varchar](200) NULL,
310   [IRAttribute12] [varchar](200) NULL,
311   [IRAttribute13] [varchar](200) NULL,
312   [IRAttribute14] [varchar](200) NULL,
313   [IRAttribute15] [varchar](200) NULL,
314   [Orientation] [int] NOT NULL,   
315   [PageNumber] [int] NULL,
316   [System] [varchar](20) NOT NULL,
317   [SrcDrawer] [varchar](4) NOT NULL,
318   [SrcFileType] [varchar] (255) NULL,
319   [SrcFileNumber] [varchar](40) NULL,
320   [SrcFileName] varchar(120) NULL,   
321   [SrcFolderPath] [varchar](200) NOT NULL,
322   [SrcDocType] [varchar](4) NOT NULL,
323   [SrcDocID] int NULL,
324   [SrcDocDate] datetime NULL,
325   [SrcUserKey1] [varchar](50) NULL,
326   [SrcTempDin] [varchar](50) NOT NULL,
327   [SrcPageMark] [int] NULL,
328   [SrcArchiveStatus] [varchar](1) NULL,
329   [SrcDeviceID] [int] NULL, 
330   [SrcDrive] [varchar](15) NULL,
331   [SrcAMedia] [varchar](1) NULL,
332   [SrcADrive] [varchar](15) NULL,   
333   [SrcFormat] [varchar](10) NULL,
334   [SrcFormat2] [varchar](10) NULL,     
335  CONSTRAINT [PK_MigrationPage] PRIMARY KEY CLUSTERED 
336  (
337    [ID] ASC
338  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
339) ON [PRIMARY]
340End
341GO
342IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationDocument]') AND type in (N'U'))
343Begin
344CREATE TABLE dbo.MigrationDocument
345(
346  [ID] varchar(300) NOT NULL,
347  [FileID] varchar(300) not NULL,
348  [Status] [int] NOT NULL,
349  [MachineName] [varchar](100) NULL,
350  [Priority] [int] NULL,
351  [bookmark] [uniqueidentifier] NULL,
352  [Timestamp] [datetime] NULL,  
353  [System] varchar(50) NOT NULL,
354  [SrcDrawerID] int,
355  [SrcDrawer] [varchar](100) NOT NULL,  
356  [SrcFileID] int,
357  [SrcFileNumber] [varchar] (300) NOT NULL,
358  [SrcFileName] varchar(255),
359  [SrcFileType] varchar(255),
360  [SrcDocID]   [int],
361  [SrcDocType] varchar(255),
362  [SrcDocDate] [datetime],
363  [SrcCreateDate] [datetime],  
364  [SrcAttributes] varchar(4000),
365  [PageCount] int not null,
366  [ExtCount] int not null,
367  [HoldReason] varchar(100) null,  
368  CONSTRAINT [PK_MigrationDocument] PRIMARY KEY CLUSTERED 
369  (
370    [ID] ASC
371  )
372  WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
373) 
374ON [PRIMARY]
375End
376GO
377IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationFile]') AND type in (N'U'))
378Begin
379create table dbo.MigrationFile
380(  
381  ID varchar(300) NOT NULL,
382  Status int,
383  Priority int,
384  PageCount int,
385  DocumentCount int,
386  TaskCount int,
387  System varchar(50),  
388  SrcDrawerID int,
389  SrcDrawer varchar(50),
390  SrcFileID int,
391  SrcFileNumber varchar(255),
392  SrcFileName varchar(255),
393  SrcUserData1 varchar(50),
394  SrcUserData2 varchar(50),
395  SrcUserData3 varchar(50),
396  SrcUserData4 varchar(50),
397  SrcUserData5 varchar(50),
398  SrcFileType varchar(255),
399  SrcFileOwner varchar(10),
400  SrcFileMark1 int,
401  SrcFileMark2 int,
402  SrcFileMark3 int,
403  CreateDate datetime,
404  MostRecentDocDate datetime,
405  Attributes varchar(5000),
406  CONSTRAINT [PK_MigrationFile] PRIMARY KEY CLUSTERED 
407  (
408    [ID] ASC
409  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
410) ON [PRIMARY]
411End
412GO
413IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationFUP]') AND type in (N'U'))
414Begin
415CREATE TABLE [dbo].[MigrationFUP](
416	[ID] [bigint] IDENTITY(1,1) NOT NULL,
417	[Status] [int] NOT NULL CONSTRAINT [DF_MigrationFUPFile_Status]  DEFAULT ((0)),
418	[FileID] [varchar](100) NOT NULL,
419	[IRDrawer] [varchar](50) NOT NULL,
420	[IRFileType] [varchar](50) NOT NULL,
421	[IRFileNumber] [varchar](50) NOT NULL,
422	[IRFileName] [varchar](50) NULL,
423	[IRFileOwnerAttrName] [varchar](255) NULL,
424	[IRFileMarkID1] [int] NULL,
425	[IRFileMarkID2] [int] NULL,
426	[IRFileMarkID3] [int] NULL,
427  [IRAttributeName1] [varchar](255) NULL,
428	[IRAttributeValue1] [varchar](255) NULL,
429	[IRAttributeName2] [varchar](255) NULL,
430	[IRAttributeValue2] [varchar](255) NULL,
431	[IRAttributeName3] [varchar](255) NULL,
432	[IRAttributeValue3] [varchar](255) NULL,
433	[IRAttributeName4] [varchar](255) NULL,
434	[IRAttributeValue4] [varchar](255) NULL,
435	[IRAttributeName5] [varchar](255) NULL,	
436  [IRAttributeValue5] [varchar](255) NULL,	
437  [IRAttributeName6] [varchar](255) NULL,
438	[IRAttributeValue6] [varchar](255) NULL,
439	[IRAttributeName7] [varchar](255) NULL,
440	[IRAttributeValue7] [varchar](255) NULL,
441	[IRAttributeName8] [varchar](255) NULL,
442	[IRAttributeValue8] [varchar](255) NULL,
443	[IRAttributeName9] [varchar](255) NULL,
444	[IRAttributeValue9] [varchar](255) NULL,
445	[IRAttributeName10] [varchar](255) NULL,  
446  [IRAttributeValue10] [varchar](255) NULL,
447  [System] [varchar](50) NOT NULL,
448	[SrcDrawer] [varchar](100) NOT NULL,
449	[SrcFileMark1] [int] NULL,
450	[SrcFileMark2] [int] NULL,
451	[SrcFileMark3] [int] NULL,
452	[SrcFileOwner] [varchar](50) NULL,	
453  [SrcAttributeName1] [varchar](255) NULL,
454	[SrcAttributeValue1] [varchar](255) NULL,
455	[SrcAttributeName2] [varchar](255) NULL,
456	[SrcAttributeValue2] [varchar](255) NULL,
457	[SrcAttributeName3] [varchar](255) NULL,
458	[SrcAttributeValue3] [varchar](255) NULL,
459	[SrcAttributeName4] [varchar](255) NULL,
460	[SrcAttributeValue4] [varchar](255) NULL,
461	[SrcAttributeName5] [varchar](255) NULL,	
462  [SrcAttributeValue5] [varchar](255) NULL,	
463  [SrcAttributeName6] [varchar](255) NULL,
464	[SrcAttributeValue6] [varchar](255) NULL,
465	[SrcAttributeName7] [varchar](255) NULL,
466	[SrcAttributeValue7] [varchar](255) NULL,
467	[SrcAttributeName8] [varchar](255) NULL,
468	[SrcAttributeValue8] [varchar](255) NULL,
469	[SrcAttributeName9] [varchar](255) NULL,
470	[SrcAttributeValue9] [varchar](255) NULL,
471	[SrcAttributeName10] [varchar](255) NULL,  
472  [SrcAttributeValue10] [varchar](255) NULL,	
473	[FUPString] [char](3000) NULL,
474	[Batch] [int] Default 0 NOT NULL,
475 CONSTRAINT [PK_MigrationFUPFile] PRIMARY KEY CLUSTERED 
476(
477	[ID] ASC
478)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
479) ON [PRIMARY]
480End
481GO
482IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationTask]') AND type in (N'U'))
483Begin
484  CREATE TABLE [dbo].[MigrationTask]
485  (
486    [ID] [bigint] IDENTITY(1,1) NOT NULL,
487    [FileID] varchar(300) NOT NULL,
488    [DocumentID] varchar(300) NOT NULL,
489    [PageID] [bigint] NOT NULL,
490    [FlowID] [varchar](255) NULL,
491    [StepID] [varchar](255) NULL,
492    [Description] [varchar](255) NULL,
493    [AssignedToUserID] [varchar](50) NULL,
494    [Priority] [int] NOT NULL,
495    [AvailableDate] [datetime] NOT NULL,
496    [System] varchar(50) NOT NULL,
497    [SrcDrawer] varchar(255) NOT NULL,
498    [SrcTaskID] [varchar](50) NOT NULL,
499    [SrcFlowID] [varchar] (255) NOT NULL,
500    [SrcStepID] [varchar] (255) NOT NULL,
501    [SrcExtraKey] [varchar](255) NULL,
502    [SrcAssignedToUserID] varchar(50) NULL,
503  CONSTRAINT [PK_MigrationTask] PRIMARY KEY CLUSTERED 
504  (
505    [ID] ASC
506  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
507) ON [PRIMARY]
508End
509GO
510IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPath]') AND type in (N'U'))
511Begin
512CREATE TABLE [dbo].[MigrationPath]
513(
514  System varchar(40) NOT NULL,
515  storage varchar(20) NOT NULL,
516  ID varchar(20) NOT NULL,
517  Path varchar(400) NOT NULL,   
518  CONSTRAINT [PK_MigrationPath] PRIMARY KEY CLUSTERED 
519  (
520    [System] ASC,
521    [storage] ASC,
522    [ID] ASC
523  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
524) ON [PRIMARY]
525End
526GO
527IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationType]') AND type in (N'U'))
528Begin
529CREATE TABLE [dbo].[MigrationType]
530(
531  System varchar(40) NOT NULL,  
532  Kind varchar(10) NOT NULL,
533  Drawer varchar(50) NOT NULL constraint MigrationType_Drawer_Default default '',
534  ItemType varchar(255) NOT NULL,
535  Description varchar(255) NOT NULL,
536  CONSTRAINT [PK_MigrationType] PRIMARY KEY CLUSTERED 
537  (
538    System ASC,  
539    Kind ASC,
540    Drawer,
541    ItemType ASC    
542  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
543) ON [PRIMARY]
544End
545GO
546IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogMessage]') AND type in (N'U'))
547Begin
548  CREATE TABLE [dbo].[LogMessage](
549         [ID] [int] IDENTITY(1,1) NOT NULL,
550         [messagecode] [int] NOT NULL,
551         [datetimeoccurance] [datetime] NOT NULL,
552         [MachineName] [varchar](100) NULL,
553    CONSTRAINT [pk_LogMessage] PRIMARY KEY CLUSTERED 
554    (
555           [ID] ASC
556    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
557  ) ON [PRIMARY]
558End
559GO
560IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogProcess]') AND type in (N'U'))
561Begin
562  CREATE TABLE [dbo].[LogProcess](
563    [ID] [int] IDENTITY(1,1) NOT NULL,
564    [Name] [varchar](100) NOT NULL,
565    [System] [varchar](50) NULL,
566    [Drawer] [varchar](4) NULL,
567    [OtherParam1] [varchar](50) NULL,
568    [OtherParam2] [varchar](50) NULL,
569    [OtherParam3] [varchar](50) NULL,
570    [StartTime] [datetime] NOT NULL,
571    [EndTime] [datetime] NULL,
572    [Duration] [int] NULL,
573    [isPhase] bit NULL,
574    CONSTRAINT [PK_LogProcess] PRIMARY KEY CLUSTERED 
575  (
576    [ID] ASC
577  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
578) ON [PRIMARY]
579end
580go
581IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogProcessTime]') AND type in (N'V'))
582	drop view LogProcessTime;
583go
584create view LogProcessTime
585as
586select *, 
587  LogProcess.Duration as Durationms,
588  LogProcess.Duration/86400000 as DurationDayPart,
589  CONVERT(varchar,dateadd(ms,LogProcess.Duration,0),114) as DurationTimePart
590from LogProcess
591go
592begin
593  print ''  -- keeping begin\end block open as hack for build script.
594  
595
596End
597GO
598IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogProcessHistory]') AND type in (N'U'))
599Begin
600CREATE TABLE [dbo].[LogProcessHistory](
601  [ArchiveDate] datetime not null,
602	[ID] [int] NOT NULL,
603	[Name] [varchar](100) NOT NULL,
604	[System] [varchar](50) NULL,
605	[Drawer] [varchar](4) NULL,
606	[OtherParam1] [varchar](50) NULL,
607	[OtherParam2] [varchar](50) NULL,
608	[OtherParam3] [varchar](50) NULL,
609	[StartTime] [datetime] NOT NULL,
610	[EndTime] [datetime] NULL,
611	[Duration] [int] NULL,
612  [isPhase] bit NULL,
613) ON [PRIMARY]
614End
615GO
616IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogProgress]') AND type in (N'U'))
617Begin
618CREATE TABLE [dbo].[LogProgress]
619  (
620    [ID] [int] IDENTITY(1,1) NOT NULL,
621    [DocumentID] varchar(100) NOT NULL,
622    [doccount] [int] NULL,
623    [pagecount] [int] NULL,
624    [duration] [int] NULL,
625    [success] [int] NULL,
626    [status] [varchar](max) NULL,
627    [errormessage] [varchar](max) NULL,
628    [MachineName] [varchar](100) NULL,
629    [TimeStamp] [datetime] NULL,
630	CONSTRAINT [PK_LogProgress] PRIMARY KEY CLUSTERED 
631    (
632       [ID] ASC
633    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
634) ON [PRIMARY]
635
636  CREATE NONCLUSTERED INDEX IX_LogProgress_DocumentID ON dbo.LogProgress
637  (
638    DocumentID ASC
639  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
640End
641GO
642IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogProgressHistory]') AND type in (N'U'))
643Begin
644  CREATE TABLE [dbo].[LogProgressHistory]
645  (  ArchiveDate datetime not null,
646     [ID] [int] not null,
647     [DocumentID] varchar(100) NULL,
648     [doccount] [int] NULL,
649     [pagecount] [int] NULL,
650     [duration] [int] NULL,
651     [success] [int] NULL,
652     [status] [varchar](max) NULL,
653     [errormessage] [varchar](max) NULL,
654     [MachineName] [varchar](100) NULL,
655     [TimeStamp] [datetime] NULL
656)
657End
658GO
659IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogQueueCounts]') AND type in (N'U'))
660Begin
661  CREATE TABLE [dbo].[LogQueueCounts](
662       [status] [int] NULL,
663       [count] [int] NULL
664) ON [PRIMARY]
665insert into LogQueueCounts (status, count) values (0,0)
666insert into LogQueueCounts (status, count) values (1,0)
667insert into LogQueueCounts (status, count) values (2,0)
668insert into LogQueueCounts (status, count) values (-1,0)
669End
670GO
671IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapDocument]') AND type in (N'U'))
672Begin
673CREATE TABLE [dbo].[MapDocument]
674(
675	[System] [varchar](50) NOT NULL,
676	[SrcDrawer] [varchar](100) NOT NULL,
677	[SrcFileType] [varchar](255) NOT NULL,
678	[SrcDrawerDesc] [varchar](255) NOT NULL,
679	[SrcDrawerOwner] [varchar](50) NOT NULL,
680	[SrcDocType] [varchar](50) NOT NULL,
681	[SrcDocName] [varchar](100) NOT NULL,
682	[SrcFolderPath] [varchar](2000) NOT NULL,
683	[SrcFolderDesc] [varchar](255) NOT NULL,  
684	[DestDrawer] [varchar](50) NOT NULL,
685	[DestFileType] [varchar](50) NOT NULL,
686	[DestDocType] [varchar](50) NOT NULL,
687	[DestFolderPath] [varchar](4000) NOT NULL	
688) ON [PRIMARY]
689End
690GO
691IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapFileTypeAttribute]') AND type in (N'U'))
692Begin
693CREATE TABLE [dbo].[MapFileTypeAttribute](
694	[System] [varchar](50) NOT NULL,
695	[srcDrawer] [varchar](4) NOT NULL,
696	[srcAttr1Name] [varchar](50) NULL,
697	[srcAttr2Name] [varchar](50) NULL,
698	[srcAttr3Name] [varchar](50) NULL,
699	[srcAttr4Name] [varchar](50) NULL,
700	[srcAttr5Name] [varchar](50) NULL,
701	[DestFileType] [varchar](50) NOT NULL,
702	[DestAttrName1] [varchar](50) NULL,
703	[DestAttrName2] [varchar](50) NULL,
704	[DestAttrName3] [varchar](50) NULL,
705	[DestAttrName4] [varchar](50) NULL,
706	[DestAttrName5] [varchar](50) NULL
707	) ON [PRIMARY]
708
709
710
711
712End
713GO
714IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapTask]') AND type in (N'U'))
715Begin
716  CREATE TABLE [dbo].MapTask
717  (
718    [System] varchar(20) NOT NULL,
719    [srcFlowid] [varchar](255) NOT NULL,
720    [srcFlowName] [varchar](255) NOT NULL,	  
721    [srcStepid] [varchar](255) NOT NULL,
722    [srcStepName] [varchar](255) NOT NULL,	  
723    [DestFlowProgName] [varchar](50) NOT NULL,
724    [DestStepProgName] [varchar](50) NOT NULL
725  ) ON [PRIMARY]
726End
727GO
728IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapTaskAttribute]') AND type in (N'U'))
729Begin
730CREATE TABLE dbo.MapTaskAttribute
731(
732   [SourceSystem] varchar (50) NOT NULL,
733   [Sourcev3Prompt] varchar (50) NOT NULL,
734   [Sourcev3RecordNo] varchar (50) NOT NULL,
735   [Sourcev3FieldNo] varchar (50) NOT NULL,
736   [Sourcev3AttributeValue] varchar (50) NOT NULL,
737   [DestAttributeName] varchar (50) NOT NULL,
738   [DestAttributeProgrammaticName] varchar (50) NOT NULL,
739   [DestAttributeValue] varchar (50) NOT NULL
740) ON [PRIMARY]
741End
742GO
743IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapMark]') AND type in (N'U'))
744Begin
745CREATE TABLE dbo.MapMark
746(
747	System varchar(50) NOT NULL,
748	SrcDrawer varchar(255) NOT NULL,
749	MarkType varchar(50) NOT NULL		--  Value to be 'page' or 'file'	
750		constraint DEFAULT_MapMark_MarkType_page default('page') ,	
751	SrcMarkID varchar(255) NOT NULL,
752	SrcMarkDesc varchar(255) NOT NULL,
753	DestMarkID int NULL,	-- changed to int to reflect actual dest datatype in PageMarkDef and what Icon currently expects for input
754	DestMarkDesc varchar(255) NULL,
755	DestMarkProgrammaticName varchar(255) NULL,
756	DestFileType varchar(255) NULL
757) ON [PRIMARY];
758alter table MapMark
759	add constraint chk_MapMark_MarkType check(MarkType in('page','file'));
760alter table MapMark
761	add constraint UNIQUE_MapMark_SrcColumns unique(
762		System,
763		SrcDrawer,
764		MarkType,
765		SrcMarkID
766	);
767End
768GO
769IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapUser]') AND type in (N'U'))
770Begin
771  CREATE TABLE [dbo].MapUser
772  (
773     [System] varchar (50) NOT NULL,
774     [SrcUserID] varchar (50) NOT NULL,
775     [SrcUserName] varchar (50) NOT NULL,
776     [DestAccountID] varchar (50) NOT NULL,
777     [DestUserName] varchar (50) NOT NULL
778  ) ON [PRIMARY]
779End
780GO
781IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[analyzeMigrationFile]') AND type in (N'P', N'PC'))
782  Drop Procedure [dbo].[analyzeMigrationFile]
783GO
784create procedure analyzeMigrationFileStatuses
785  @fileNumber varchar(255)
786as
787begin
788  set nocount on;
789    --General Migration File Summary
790  if object_id('tempdb..#File') is not null drop table #File
791  select System, SrcDrawer, SrcFileNumber, ID as FileID, Status, Priority, PageCount, DocumentCount, TaskCount
792  into #File
793  from MigrationFile where SrcFileNumber=@filenumber;
794
795    --Base File Analsysis table
796  if object_id('tempdb..#FileAnalysis') is not null drop table #FileAnalysis
797  ;with Tasks as(
798    select FileID, DocumentID, SrcTaskID
799    from MigrationTask
800    where FileID in(select ID from MigrationFile where SrcFileNumber=@filenumber)
801    ),
802  ActiveTasks as(
803    select DocumentID, count(*) as Tasks
804    from MigrationTask
805    where FlowID<>'Do Not Migrate'
806    group by DocumentID
807    ),
808  PurgeTasks as(
809    select DocumentID, count(*) as Tasks
810    from MigrationTask
811    where FlowID='Do Not Migrate'
812    group by DocumentID
813    )
814  select MigrationDocument.System, 
815    MigrationDocument.SrcDrawer, 
816    MigrationDocument.SrcFileNumber, 
817    MigrationDocument.ID as DocumentID, 
818    FileID, 
819    MigrationDocument.Status, 
820    MigrationDocument.Priority, 
821    MigrationDocument.TimeStamp as DocMigrationStatusDate,
822    SrcDocDate, 
823    MigrationDocument.PageCount, 
824    isnull(ActiveTasks.Tasks,0) as ActiveTasks,
825    isnull(PurgeTasks.Tasks,0) as PurgeTasks,
826    MigrationDocument.HoldReason, 
827    LogProgress.errormessage
828  into #FileAnalysis
829  from MigrationDocument
830  left join ActiveTasks on MigrationDocument.ID=ActiveTasks.DocumentID
831  left join PurgeTasks on MigrationDocument.ID=PurgeTasks.DocumentID
832  join MigrationFile on MigrationDocument.FileID=MigrationFile.ID
833  left join LogProgress on MigrationDocument.ID=LogProgress.DocumentID
834  where MigrationFile.SrcFileNumber=@filenumber
835
836    -- Display results
837  select * from #File
838  select * 
839  from #FileAnalysis 
840  order by 
841    case status 
842      when -1 then 0 
843      when -5 then 1 
844      when 2  then 2 
845      when 0  then 3 
846      when 1  then 4 
847      when -9 then 100 
848      else 5 
849    end ASC, -- Enumerated so that errors and holds come first followed by Migrating, Pending, Migrated(Success), everything else but Purge, and Purge last
850    errormessage, 
851    HoldReason
852
853     -- 
854  if object_id('tempdb..#ErroredDocs') is not null drop table #ErroredDocs
855  SELECT id,
856    documentid,
857    filepath,
858    annotationspath,
859    irdrawer,
860    irfiletype,
861    irfilenumber,
862    irfilename,
863    irfoldertype,
864    irdoctype,
865    irdocdate,
866    irpagedescription,
867    irpagemark,
868    pagenumber
869    Orientation,
870    SrcDrawer,
871    SrcFolderPath,
872    SrcDocType
873  into #ErroredDocs
874  FROM MigrationPage (nolock)
875  WHERE documentid in(select DocumentID from #FileAnalysis where Status=-1)
876  ORDER BY documentid,pagenumber
877
878  if exists(select * from #ErroredDocs)
879    select * from #ErroredDocs
880  else
881    exec raiseMessage 'There were no errored documents in file %s',@arg1=@filenumber;
882
883  if exists(select * from #FileAnalysis where Status=-1 and (ActiveTasks>0 or PurgeTasks>0))
884  begin
885    if object_id('tempdb..#ErroredTasks') is not null drop table #ErroredTasks
886    SELECT
887      #FileAnalysis.FileID,
888      #FileAnalysis.DocumentID,
889      #FileAnalysis.Status as DocMigStatus,
890      MigrationTask.flowid, 
891      MigrationTask.stepid, 
892      MigrationTask.description, 
893      MigrationTask.assignedtouserid as userid, 
894      MigrationTask.priority, 
895      MigrationTask.availabledate, 
896      MigrationTask.SrcTaskID,
897      MigrationTask.SrcFlowID,
898      MigrationTask.SrcStepID,
899      #FileAnalysis.HoldReason,
900      #FileAnalysis.errormessage
901    into #ErroredTasks
902    FROM MigrationTask (nolock)
903    join #FileAnalysis on MigrationTask.DocumentID=#FileAnalysis.DocumentID
904    WHERE MigrationTask.DocumentID in(select DocumentID from #FileAnalysis where Status=-1)
905        --and flowid <> 'Do Not Migrate'
906        
907    select * from #ErroredTasks
908  end
909  else
910    exec raiseMessage 'There were no errored tasks in file %s',@arg1=@filenumber;
911end  
912GO
913IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[logStart]') AND type in (N'P', N'PC'))
914  Drop Procedure [dbo].[logStart]
915GO
916Create Procedure dbo.logStart
917  @name varchar(100),
918  @system varchar(20),
919  @drawer varchar(4) = null,
920  @param1 varchar(100) = null,
921  @param2 varchar(100) = null,
922  @param3 varchar(100) = null,
923  @ID int output
924as
925begin
926  set nocount on
927  insert into LogProcess (
928		Name, 
929		System, 
930		Drawer, 
931		OtherParam1, 
932		OtherParam2, 
933		OtherParam3, 
934		StartTime, 
935		isPhase
936		)
937  values (
938	@name, 
939	@system, 
940	@drawer, 
941	left(@param1,50), 
942	left(@param2,50), 
943	left(@param3,50), 
944	getDate(), 
945	0
946	)	
947
948  set @ID = @@IDENTITY
949end
950GO
951IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[logEnd]') AND type in (N'P', N'PC'))
952  Drop Procedure [dbo].[logEnd]
953GO
954Create Procedure dbo.logEnd
955  @ID int
956as
957begin
958  set nocount on
959  update LogProcess
960     set EndTime = getDate(), Duration = datediff(MILLISECOND,StartTime,getDate())
961   where ID = @ID
962
963end
964GO
965IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[logPhaseStart]') AND type in (N'P', N'PC'))
966  Drop Procedure [dbo].[logPhaseStart]
967GO
968Create Procedure dbo.logPhaseStart
969  @name varchar(100)  
970as
971begin
972  set nocount on
973  insert into LogProcess
974    (Name, StartTime, isPhase) values (@name, getDate(),1)
975    
976end
977GO
978GO
979IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[logPhaseEnd]') AND type in (N'P', N'PC'))
980  Drop Procedure [dbo].[logPhaseEnd]
981GO
982Create Procedure dbo.logPhaseEnd
983  @name varchar(100)
984as
985begin
986  set nocount on
987  
988  declare @count int
989  select @count = count(*) from LogProcess where Name = @name
990  
991  if (@count = 0)
992  begin
993    raiserror('No Phase: %s ... was ever started',1,16,@name)
994    return
995  end
996  
997  if (@count > 1)
998  begin
999    raiserror('Check you Script! Phase: %s ... has been started more than once.',1,16,@name)
1000    return
1001  end
1002  
1003  update LogProcess
1004     set EndTime = getDate(), Duration = datediff(MILLISECOND,StartTime,getDate())
1005   where Name = @name
1006end
1007GO
1008IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dropIndexes]') AND type in (N'P', N'PC'))
1009  Drop Procedure [dbo].[dropIndexes]
1010GO
1011create procedure [dbo].[dropIndexes]
1012as
1013begin
1014  RAISERROR ('Dropping all Migration Data Table Indexes', 10, 1) WITH NOWAIT      
1015  declare @logid int, @ProcName varchar(50)
1016  set @ProcName = Object_Name(@@PROCID)
1017  exec logStart @procname, null, null, @ID = @logid output
1018  
1019	declare @sql nvarchar(1000)
1020	declare @tablename nvarchar(100), @indexname nvarchar(200) 
1021	declare iCursor cursor local static for
1022	  select ind.name, tab.name
1023		from sys.indexes as ind inner join sys.tables as tab
1024		  on ind.object_id = tab.object_id
1025		 where tab.name like 'Migration%'
1026		   and tab.type = 'U'
1027		   and ind.is_primary_key = 0
1028	open iCursor
1029	fetch from iCursor into @indexname, @tablename
1030	while (@@fetch_status = 0)
1031	begin
1032	  set @sql = 'DROP INDEX [' + @indexname + '] ON [dbo].[' + @tablename + ']'
1033	  exec (@sql)
1034	  fetch from iCursor into @indexname, @tablename
1035	end
1036	close iCursor
1037	deallocate iCursor
1038
1039 exec logEnd @logid     
1040end
1041
1042
1043GO
1044IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prepareIndexes]') AND type in (N'P', N'PC'))
1045  Drop Procedure [dbo].[prepareIndexes]
1046GO
1047Create Procedure [dbo].[prepareIndexes]
1048  @table varchar(50) = null,
1049  @index varchar(100) = null
1050as
1051begin
1052  declare @logid int, @ProcName varchar(50)
1053  set @ProcName = Object_Name(@@PROCID)
1054  exec logStart @procname, null, null, @param1 = @table, @param2 = @index, @ID = @logid output
1055  
1056  /* *******************************************  Table: MigrationPage *****************************************************
1057    Indexes:
1058      
1059      
1060  ****************************************************************************************************************** */
1061  if (isNull(@table,'MigrationPage') = 'MigrationPage')
1062  begin
1063    
1064    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPage]') AND name = N'IX_MigrationPage_tempdin')
1065      and isNull(@index,'IX_MigrationPage_tempdin') = 'IX_MigrationPage_tempdin'
1066    begin
1067      raiserror ('Building Index IX_MigrationPage_tempdin on MigrationPage',10,1) with nowait            
1068        CREATE NONCLUSTERED INDEX IX_MigrationPage_tempdin ON dbo.MigrationPage 
1069        (
1070          SrcTempDin ASC
1071        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
1072    end
1073    
1074    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPage]') AND name = N'IX_MigrationPage_DocumentID')
1075      and isNull(@index,'IX_MigrationPage_DocumentID') = 'IX_MigrationPage_DocumentID'
1076    begin
1077      raiserror ('Building Index IX_MigrationPage_DocumentID on MigrationPage',10,1) with nowait            
1078      CREATE NONCLUSTERED INDEX IX_MigrationPage_DocumentID ON dbo.MigrationPage 
1079      (
1080        DocumentID ASC,
1081        pagenumber ASC
1082      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
1083    end
1084    
1085    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPage]') AND name = N'IX_MigrationPage_FileID')
1086      and isNull(@index,'IX_MigrationPage_FileID') = 'IX_MigrationPage_FileID'
1087    begin
1088      raiserror ('Building Index IX_MigrationPage_FileID on MigrationPage',10,1) with nowait            
1089      CREATE NONCLUSTERED INDEX IX_MigrationPage_FileID ON dbo.MigrationPage 
1090      (
1091        FileID ASC
1092      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
1093    end
1094    
1095    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPage]') AND name = N'IX_MigrationPage_MappingSample')
1096      and isNull(@index,'IX_MigrationPage_MappingSample') = 'IX_MigrationPage_MappingSample'
1097    begin
1098      raiserror ('Building Index IX_MigrationPage_MappingSample on MigrationPage',10,1) with nowait            
1099      CREATE NONCLUSTERED INDEX IX_MigrationPage_MappingSample ON dbo.MigrationPage 
1100      (
1101          [System] ASC,
1102          [SrcDrawer] ASC,
1103          [SrcFileNumber] ASC,
1104          [SrcFolderPath] ASC,
1105          [SrcDocType] ASC
1106      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
1107    end
1108      
1109    
1110  end ----------------------------- End Table: MigrationPage ----------------------------------------
1111  
1112  /* *******************************************  Table: MigrationFile *****************************************************
1113    Indexes:
1114      IX_MigrationFile_SystemDrawerFile
1115      
1116  ****************************************************************************************************************** */
1117  if (isNull(@table,'MigrationFile') = 'MigrationFile')
1118  begin  
1119     ----------------------------  Index: IX_MigrationFile_SystemDrawerFile ------------------------------------------------------
1120      IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationFile]') AND name = N'IX_MigrationFile_SystemDrawerFile')
1121        and isNull(@index,'IX_MigrationFile_SystemDrawerFile') = 'IX_MigrationFile_SystemDrawerFile'
1122      begin
1123        raiserror ('Building Index IX_MigrationFile_SystemDrawerFile on MigrationFile',10,1) with nowait  
1124      
1125        CREATE NONCLUSTERED INDEX [IX_MigrationFile_SystemDrawerFile] ON [dbo].[MigrationFile]
1126        (
1127          [System] ASC,
1128          [Srcdrawer] ASC,
1129          [Srcfilenumber] ASC
1130        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1131      end  
1132      
1133      IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationFile]') AND name = N'IX_MigrationFile_PriorityStatus')
1134        and isNull(@index,'IX_MigrationFile_PriorityStatus') = 'IX_MigrationFile_PriorityStatus'
1135      begin
1136        raiserror ('Building Index IX_MigrationFile_PriorityStatus on MigrationFile',10,1) with nowait  
1137      
1138        CREATE NONCLUSTERED INDEX [IX_MigrationFile_PriorityStatus] ON [dbo].[MigrationFile]
1139        (
1140          [Priority] ASC,
1141          Status ASC
1142        )
1143        
1144            /* 
1145              The following check for and drop of can be removed in a later version. Leave in code at least through year 2016.
1146              
1147              This index is superceded by IX_MigrationFile_PriorityStatus which improves Process performance especially when 
1148              there are a large number of priority values. The status column needs to be covered by the index since it is used 
1149              in a query predicate in Process.sql. Without status being part of the index, an extreme count (many hundreds of 
1150              thousands) of reads are generatedUsing by said query.
1151              Query: select top 1 @priority = priority from MigrationFile where priority < @stopAtPriority and status = 0 order by priority
1152            */              
1153        IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationFile]') AND name = N'IX_MigrationFile_Priority')
1154          and isNull(@index,'IX_MigrationFile_Priority') = 'IX_MigrationFile_Priority'
1155        begin
1156          raiserror ('Dropping Legacy Index IX_MigrationFile_Priority on MigrationFile',10,1) with nowait  
1157        
1158          drop INDEX [IX_MigrationFile_Priority] ON [dbo].[MigrationFile]
1159        end         
1160      end  
1161  end ----------------------------- End Table: MigrationFile ----------------------------------------
1162  
1163  /* *******************************************  Table: MigrationDocument *****************************************************
1164    Indexes:
1165      IX_MigrationDocument_SystemDrawerFileDoc
1166      
1167  ****************************************************************************************************************** */
1168  if (isNull(@table,'MigrationDocument') = 'MigrationDocument')
1169  begin  
1170   
1171    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationDocument]') AND name = N'IX_MigrationDocument_Priority')
1172      and isNull(@index,'IX_MigrationDocument_Priority') = 'IX_MigrationDocument_Priority'
1173    begin
1174      raiserror ('Building Index IX_MigrationDocument_Priority on MigrationDocument',10,1) with nowait  
1175      CREATE NONCLUSTERED INDEX IX_MigrationDocument_Priority ON dbo.MigrationDocument 
1176      (
1177        Priority ASC
1178      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
1179    end
1180    
1181    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationDocument]') AND name = N'IX_MigrationDocument_StatusPriority')
1182      and isNull(@index,'IX_MigrationDocument_StatusPriority') = 'IX_MigrationDocument_StatusPriority'
1183    begin
1184      raiserror ('Building Index IX_MigrationDocument_StatusPriority on MigrationDocument',10,1) with nowait      
1185      CREATE NONCLUSTERED INDEX IX_MigrationDocument_StatusPriority ON dbo.MigrationDocument 
1186      (
1187        Status ASC,
1188        Priority ASC
1189      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
1190    end
1191    
1192    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationDocument]') AND name = N'IX_MigrationDocument_Bookmark')
1193      and isNull(@index,'IX_MigrationDocument_Bookmark') = 'IX_MigrationDocument_Bookmark'
1194    begin
1195      raiserror ('Building Index IX_MigrationDocument_Bookmark on MigrationDocument',10,1) with nowait  
1196      CREATE NONCLUSTERED INDEX IX_MigrationDocument_Bookmark ON dbo.MigrationDocument
1197      (
1198        BookMark ASC
1199      )
1200      INCLUDE ( ID) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
1201    end
1202    
1203    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationDocument]') AND name = N'IX_MigrationDocument_FileID')
1204      and isNull(@index,'IX_MigrationDocument_FileID') = 'IX_MigrationDocument_FileID'
1205    begin
1206      raiserror ('Building Index IX_MigrationDocument_FileID on MigrationDocument',10,1) with nowait            
1207      CREATE NONCLUSTERED INDEX IX_MigrationDocument_FileID ON dbo.MigrationDocument 
1208      (
1209        FileID ASC
1210      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
1211    end
1212    
1213  end ----------------------------- End Table: MigrationDocument ----------------------------------------
1214  
1215    /* *******************************************  Table: MigrationTask *****************************************************
1216    Indexes:
1217      IX_MigrationPage_SystemDrawerFileDoc
1218      
1219  ****************************************************************************************************************** */
1220  if (isNull(@table,'MigrationTask') = 'MigrationTask')
1221  begin
1222    
1223    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationTask]') AND name = N'IX_MigrationTask_DocumentID')
1224      and isNull(@index,'IX_MigrationTask_DocumentID') = 'IX_MigrationTask_DocumentID'
1225    begin
1226      raiserror ('Building Index IX_MigrationTask_DocumentID on MigrationTask',10,1) with nowait            
1227      CREATE NONCLUSTERED INDEX IX_MigrationTask_DocumentID ON dbo.MigrationTask 
1228      (
1229        DocumentID ASC
1230      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
1231    end
1232    
1233    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationTask]') AND name = N'IX_MigrationTask_PageID')
1234      and isNull(@index,'IX_MigrationTask_PageID') = 'IX_MigrationTask_PageID'
1235    begin
1236      raiserror ('Building Index IX_MigrationTask_PageID on MigrationTask',10,1) with nowait            
1237      CREATE NONCLUSTERED INDEX IX_MigrationTask_PageID ON dbo.MigrationTask 
1238      (
1239        PageID ASC
1240      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
1241    end
1242    
1243    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationTask]') AND name = N'IX_MigrationTask_FileID')
1244      and isNull(@index,'IX_MigrationTask_FileID') = 'IX_MigrationTask_FileID'
1245    begin
1246      raiserror ('Building Index IX_MigrationTask_FileID on MigrationTask',10,1) with nowait            
1247      CREATE NONCLUSTERED INDEX IX_MigrationTask_FileID ON dbo.MigrationTask 
1248      (
1249        FileID ASC
1250      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
1251    end
1252      
1253    
1254  end ----------------------------- End Table: MigrationTask ----------------------------------------
1255  
1256  
1257  /* *********************************************  Snapshot Indexes - Only run when requested ***************************************** */
1258  if (@table = 'SnapshotDocument3x' and @index = 'ndx_Document')
1259  begin        
1260    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SnapshotDocument3x]') AND name = N'ndx_Document')
1261    begin
1262      raiserror ('Building Index ndx_Document on SnapshotDocument3x',10,1) with nowait  
1263      CREATE NONCLUSTERED INDEX [ndx_Document] ON [dbo].[SnapshotDocument3x] 
1264      (
1265             [drawer] ASC,
1266             [foldernumber] ASC,
1267             [docid] ASC
1268      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
1269    end
1270  end
1271  
1272  if (@table = 'SnapshotFolder3x' and @index = 'ndx_Folder')
1273  begin    
1274    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SnapshotFolder3x]') AND name = N'ndx_Folder')
1275    begin
1276      raiserror ('Building Index ndx_Folder on SnapshotFolder3x',10,1) with nowait  
1277      CREATE CLUSTERED INDEX [ndx_Folder] ON [dbo].[SnapshotFolder3x] 
1278      (
1279        [DRAWER] ASC,
1280	      [FOLDERNUMBER] ASC
1281     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1282
1283    end
1284  end
1285  
1286  exec logEnd @logid
1287end
1288GO
1289IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dropData]') AND type in (N'P', N'PC'))
1290  Drop Procedure [dbo].[dropData]
1291GO
1292Create Procedure dbo.dropData
1293  @system varchar(20) = null,
1294  @drawer varchar(4) = null,
1295  @override varchar(8) = null
1296as
1297begin
1298  set nocount on
1299  declare @logid int, @ProcName varchar(50)
1300  set @ProcName = Object_Name(@@PROCID)
1301  exec logStart @procname, @system, @drawer, @ID = @logid output
1302  
1303  if (@system is null and @drawer is null)
1304    begin
1305      RAISERROR ('  Clearing out all document and task data', 10, 1) WITH NOWAIT      
1306      if exists(select * from MigrationDocument where status >= 0) and @override <> 'override'
1307      begin
1308        RAISERROR ('  Migration is Underway!  You must Override to delete all content in the system!', 10, 1) WITH NOWAIT      
1309        return
1310      end
1311      exec dropIndexes
1312      if (object_id('MigrationPage') is not null)
1313        truncate table MigrationPage
1314      if (object_id('MigrationDocument') is not null)
1315        truncate table MigrationDocument
1316      if (object_id('MigrationFile') is not null)
1317        truncate table MigrationFile
1318      if (object_id('MigrationTask') is not null)
1319        truncate table MigrationTask
1320      if (object_id('MigrationPath') is not null)
1321        truncate table MigrationPath
1322      if (object_id('MigrationType') is not null)
1323        truncate table MigrationType
1324      insert into LogProgressHistory select getDate(), * from LogProgress
1325      truncate table LogProgress
1326      insert into LogProcessHistory select getDate(), * from LogProcess
1327      truncate table LogProcess
1328      truncate table LogMessage
1329    end  
1330  else if (@system is not null and @drawer is null)
1331    begin
1332      RAISERROR ('  Clearing out all Data from System: %s', 10, 1, @system) WITH NOWAIT
1333      if (object_id('MigrationTask') is not null)
1334        delete from MigrationTask where System = @system      
1335      if (object_id('MigrationFile') is not null)
1336        delete from MigrationFile where System = @system
1337      if (object_id('MigrationDocument') is not null)
1338        delete from MigrationDocument where System = @system
1339      if (object_id('MigrationPage') is not null)
1340        delete from MigrationPage where System = @system
1341      if (object_id('MigrationPath') is not null)
1342        delete from MigrationPath where System = @system
1343      if (object_id('MigrationType') is not null)
1344        delete from MigrationType where System = @system
1345    end
1346  else
1347    begin
1348      RAISERROR ('  Clearing out Data from System: %s Drawer: %s', 10, 1, @system, @drawer) WITH NOWAIT
1349      if (object_id('MigrationTask') is not null)
1350        delete from MigrationTask where System = @system and SrcDrawer = @drawer
1351      if (object_id('MigrationFile') is not null)
1352        delete from MigrationFile where System = @system and SrcDrawer = @drawer
1353      if (object_id('MigrationDocument') is not null)
1354        delete from MigrationDocument where System = @system and SrcDrawer = @drawer
1355      if (object_id('MigrationPage') is not null)
1356        delete from MigrationPage where System = @system and SrcDrawer = @drawer      
1357    end
1358  exec logEnd @logid
1359end
1360GO
1361IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[takeSnapshot]') AND type in (N'P', N'PC'))
1362  Drop Procedure [dbo].[takeSnapshot]
1363GO
1364CREATE procedure [dbo].[takeSnapshot]
1365    @database varchar(50),
1366    @linkedServer varchar(50) = null,
1367    @oracle bit = 0
1368  as
1369  begin
1370  declare @logid int, @ProcName varchar(50)
1371  set @ProcName = Object_Name(@@PROCID)
1372  exec logStart @procname, null, @ID = @logid output
1373  
1374  declare @sql varchar(5000)
1375  declare @table varchar(50)
1376  
1377  if object_id('SnapshotDocument3x') is not null
1378  begin
1379    Raiserror('SnapShot already Exists... You must drop or archive the current snapshot first',16,1)
1380    return
1381  end
1382  
1383  if (@oracle = 0)
1384    set @database = @database + '.dbo'  
1385  
1386  RAISERROR ('   Taking New Device Snapshot', 10, 1) WITH NOWAIT    
1387    set @table = 'SnapshotDevices3x'
1388    set @sql = 'select            
1389            DEVICEID,
1390            DEVICEPATH,
1391            DEFAULTIND,
1392            SYSTEMIND,
1393            DEARCHIVEIND          
1394          from ' + @database + '.DEVICES WITH (NOLOCK)'	    
1395    
1396    if (@linkedServer is not null)
1397      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1398                 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1399    else
1400      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1401                 ' from (' + @sql + ') as T'
1402      
1403    exec (@sql)
1404
1405  RAISERROR ('   Taking New CDS Snapshot', 10, 1) WITH NOWAIT    
1406  
1407    set @table = 'SnapshotCds3x'
1408    set @sql = 'select 
1409              VOLID,
1410              DATECREATED,
1411              TIMECREATED,
1412              STATUS,
1413              JUKEBOXID,
1414              PATH,
1415              PATH2            
1416            from ' + @database + '.CDS WITH (NOLOCK)'	    
1417    
1418    if (@linkedServer is not null)
1419      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1420                 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1421    else
1422      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1423                 ' from (' + @sql + ') as T'
1424      
1425    exec (@sql)
1426
1427	RAISERROR ('   Taking New DocStructure Snapshot', 10, 1) WITH NOWAIT    
1428    
1429    set @table = 'SnapshotDocStruct3x'
1430    set @sql = 'select DOCDEF.DRAWER, 
1431                      DOCTYPE = DOCTYPE.DOCTYPE,
1432                      DocDesc = DOCTYPE.DESCRIPTION,
1433                      DOCDEF.PACKAGETYPE,
1434                      FolderDesc = FOLDERTYPE.DESCRIPTION          
1435        from ' + @database + '.IRDOCDEF DOCDEF
1436         inner join ' + @database + '.DOCTYPES DOCTYPE
1437            on DOCDEF.DOCTYPE = DOCTYPE.DOCTYPE
1438         inner join ' + @database + '.PACKTYPE FOLDERTYPE
1439            on DOCDEF.PACKAGETYPE = FOLDERTYPE.PACKAGETYPE'             
1440    
1441    if (@linkedServer is not null)
1442      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1443                 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1444    else
1445      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1446                 ' from (' + @sql + ') as T'
1447      
1448    exec (@sql)
1449
1450  RAISERROR ('   Taking New PageMarks Snapshot', 10, 1) WITH NOWAIT    
1451    
1452    set @table = 'SnapshotPageMarks3x'
1453    set @sql = 'select * from ' + @database + '.IRPAGEMARKS WITH (NOLOCK)' 
1454    
1455    if (@linkedServer is not null)
1456      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1457                 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1458    else
1459      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1460                 ' from (' + @sql + ') as T'
1461      
1462    exec (@sql)
1463
1464	RAISERROR ('   Taking New Users Snapshot', 10, 1) WITH NOWAIT    
1465    
1466    set @table = 'SnapshotUsers3x'
1467    set @sql = 'select USERID, USERNAME              
1468              from ' + @database + '.USERDEFINITION WITH (NOLOCK)'	    
1469    
1470    if (@linkedServer is not null)
1471      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1472                 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1473    else
1474      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1475                 ' from (' + @sql + ') as T'
1476      
1477    exec (@sql)
1478
1479	RAISERROR ('   Taking New Workflows Snapshot', 10, 1) WITH NOWAIT    
1480    
1481    set @table = 'SnapshotWorkflows3x'
1482    set @sql = 'select FlowID = FLOWDEF.FLOWID,
1483		                  FlowName = FLOWDEF.FLOWNAME,
1484		                  FlowDesc = FLOWDEF.DESCRIPTION,
1485		                  StepID = STEPDEF.STEPID,
1486		                  StepDesc = STEPDEF.DESCRIPTION            
1487            from ' + @database + '.STEPDEFINITION STEPDEF WITH (NOLOCK)
1488			inner join ' + @database + '.FLOWDEFINITION FLOWDEF WITH (NOLOCK)
1489			on STEPDEF.FLOWID = FLOWDEF.FLOWID'
1490    
1491    if (@linkedServer is not null)
1492      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1493                 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1494    else
1495      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1496                 ' from (' + @sql + ') as T'
1497      
1498    exec (@sql)
1499  
1500  RAISERROR ('   Taking New Task Snapshot', 10, 1) WITH NOWAIT 
1501  
1502    set @table = 'SnapshotTask3x'
1503    set @sql = 'select TASKID,
1504                       FLOWID,
1505                       STEPID,
1506                       USERID,
1507                       USERKEY,
1508                       USERKEY2,
1509                       PRIORITY,
1510                       AVAILABLEDATE,
1511                       DESCRIPTION,
1512                       DRAWER,
1513                       DATE_INITIATED              
1514                from ' + @database + '.TASK WITH (NOLOCK)'
1515    
1516    if (@linkedServer is not null)
1517      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1518                 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1519    else
1520      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1521                 ' from (' + @sql + ') as T'
1522      
1523    exec (@sql)
1524
1525  RAISERROR ('   Taking New Drawer Snapshot', 10, 1) WITH NOWAIT    
1526    
1527    set @table = 'SnapshotDrawer3x'
1528    set @sql = 'select DRAWER, DESCRIPTION      
1529          from ' + @database + '.DRAWER WITH (NOLOCK)'
1530    
1531    if (@linkedServer is not null)
1532      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1533                 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1534    else
1535      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1536                 ' from (' + @sql + ') as T'
1537      
1538    exec (@sql)
1539    
1540  RAISERROR ('   Taking New Folder Snapshot', 10, 1) WITH NOWAIT    
1541     
1542     set @table = 'SnapshotFolder3x'
1543     set @sql = 'select DRAWER, FOLDERNUMBER, FOLDERNAME, USERDATA1, USERDATA2, USERDATA3, USERDATA4, USERDATA5, USERID
1544	, CAST(ISNULL(MARKEDIND,0) % 256 AS int)  as FileMark1
1545	, CAST(ISNULL(((MARKEDIND - (MARKEDIND % 256)) / 256 % 256),0) % 256 AS int)  as FileMark2
1546	, CAST(ISNULL(((MARKEDIND - (MARKEDIND% 65536)) / 65536),0) % 256 AS int) as FileMark3	      
1547      from ' + @database + '.FOLDER WITH (NOLOCK)'
1548    
1549    if (@linkedServer is not null)
1550      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1551                 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1552    else
1553      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1554                 ' from (' + @sql + ') as T'
1555      
1556    exec (@sql)     
1557    
1558  RAISERROR ('   Taking New Document Snapshot... This could take a while', 10, 1) WITH NOWAIT   
1559    
1560    set @table = 'SnapshotDocument3x'
1561    set @sql = 'select DRAWER,
1562                        FOLDERNUMBER,
1563                        PACKAGETYPE,
1564                        DOCTYPE,
1565                        USERKEY1,
1566                        DOCDATE,
1567                        DOCID,
1568                        MARKEDIND,
1569                        DSPPAGENUMBER,
1570                        REASON,
1571                        TEMPDIN,
1572                        ORIENTATION,
1573                        FORMAT,
1574                        FORMAT2,
1575                        ARCHIVESTATUS,
1576                        DEVICEID,
1577                        DRIVE,
1578                        AMEDIA,
1579                        ADRIVE, ' +
1580                        Case
1581                          when @oracle = 1 then 'STATUS = CASE DRAWER WHEN ''DEL*'' THEN ''D'' ELSE STATUS END,'
1582                          else 'STATUS = CASE WHEN DRAWER = ''DEL*'' THEN ''D'' ELSE STATUS END,'
1583                        end +
1584                        'DATECAPTURED,
1585                        FILENAME,
1586                        VISIBLE = CASE STATUS WHEN ''D'' THEN CAST(0 AS BIT) WHEN ''M'' THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END
1587        from ' + @database + '.DOCUMENT WITH (NOLOCK)'
1588	    
1589    if (@linkedServer is not null)
1590      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1591                 ' from openquery(' + @linkedServer + ', ''' + replace(@sql,'''','''''') + ''') as T'
1592    else
1593      set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
1594                 ' from (' + @sql + ') as T'
1595
1596    exec (@sql)        
1597	
1598  RAISERROR ('   Setting Doc in DEL* Drawer to Not Visible', 10, 1) WITH NOWAIT  	
1599	UPDATE SnapshotDocument3x SET VISIBLE = 0 WHERE Drawer = 'DEL*' 
1600	RAISERROR ('   Updating Invalid DocDates to DateCaptured... This could take a few minutes', 10, 1) WITH NOWAIT  
1601	UPDATE SnapshotDocument3x SET SnapshotDocument3x.DocDate = SnapshotDocument3x.DateCaptured  FROM SnapshotDocument3x  WHERE ISDATE(SnapshotDocument3x.docdate) = 0
1602	RAISERROR ('   Updating NULL Orientation columns to 0 ... This could take a few minutes', 10, 1) WITH NOWAIT  
1603	UPDATE SnapshotDocument3x SET Orientation = 0 WHERE ORIENTATION IS NULL  
1604	RAISERROR ('   Updating Invalid Archive Status U to A ... This could take a few minutes', 10, 1) WITH NOWAIT  
1605	UPDATE SnapshotDocument3x  SET ARCHIVESTATUS = 'A' WHERE ARCHIVESTATUS = 'U'
1606	RAISERROR ('   Updating NULL DocType with "XXXX" String value', 10, 1) WITH NOWAIT  
1607	UPDATE SnapshotDocument3x  SET DOCTYPE = 'XXXX' WHERE DOCTYPE IS NULL
1608  
1609  RAISERROR ('..Complete', 10, 1) WITH NOWAIT  
1610  exec logEnd @logid
1611 end 
1612GO
1613IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[archiveSnapshot]') AND type in (N'P', N'PC'))
1614  Drop Procedure [dbo].[archiveSnapshot]
1615GO
1616create procedure [dbo].[archiveSnapshot]
1617  @system varchar(50)
1618as
1619begin
1620  if not exists(select * from sys.tables where name like 'Snapshot%')
1621    begin
1622      Raiserror('No current Snapshot exists!',16,1)
1623      return
1624    end
1625  
1626  if exists(select * from sys.tables where name like 'z' + @system + '_Snapshot%')
1627    begin
1628      Raiserror('There is already an archived version of that Snapshot',16,1)
1629      return
1630    end
1631
1632  declare @archiveName varchar(100)
1633  declare @tablename varchar(100)
1634  declare tCursor cursor local static for
1635    select name from sys.tables where name like 'Snapshot%'
1636  open tCursor
1637  fetch from tCursor into @tablename
1638  while (@@FETCH_STATUS = 0)
1639  begin
1640    set @archiveName = 'z' + @system + '_' + @tablename
1641    exec sp_rename @tablename, @archiveName  
1642    fetch from tCursor into @tablename
1643  end
1644  close tCursor
1645  deallocate tCursor
1646  
1647end
1648GO
1649IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[restoreSnapshot]') AND type in (N'P', N'PC'))
1650  Drop Procedure [dbo].[restoreSnapshot]
1651GO
1652create procedure [dbo].[restoreSnapshot]
1653  @system varchar(50)
1654as
1655begin
1656  if not exists(select * from sys.tables where name like 'z' + @system + '_Snapshot%')
1657    begin
1658      Raiserror('No such Archived Snapshot exists!',16,1)
1659      return
1660    end
1661  
1662  if exists(select * from sys.tables where name like 'Snapshot%')
1663    begin
1664      Raiserror('There is already an active Snapshot',16,1)
1665      return
1666    end
1667
1668  declare @archiveName varchar(100)
1669  declare @tablename varchar(100)
1670  declare aCursor cursor local static for
1671    select name from sys.tables where name like 'z' + @system + '_Snapshot%'
1672  open aCursor
1673  fetch from aCursor into @archivename
1674  while (@@FETCH_STATUS = 0)
1675  begin
1676    set @tablename = right(@archivename,len(@archivename)-len(@system)-2)
1677    exec sp_rename @archivename, @tableName
1678    fetch from aCursor into @archivename
1679  end
1680  close aCursor
1681  deallocate aCursor
1682  
1683end
1684GO
1685IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dropSnapshot]') AND type in (N'P', N'PC'))
1686  Drop Procedure [dbo].[dropSnapshot]
1687GO
1688create Procedure [dbo].[dropSnapshot]  
1689	@system varchar(50) = null -- null be default so that the active snapshot will be dropped by default.
1690as
1691begin
1692	-- Check to see if there is a current snapshot to drop. This is just for notification. 
1693	if not exists(
1694		select * 
1695		from sys.tables 
1696		where name like isnull('z' + @system + '_','') + 'Snapshot%'  -- If @system is null, then the expression inside the isnull function will evaluate to null which will trigger the isnull function to return the empty string which in turn causes the query to return the records of the active snapshot.
1697		)
1698		begin
1699			if @system is null Raiserror('No current active Snapshot exists!',16,1)
1700			else Raiserror('No archived "%s" Snapshot exists!',16,1,@system)
1701			return
1702		end
1703	else
1704	begin	
1705		declare @tablename varchar(100)
1706		declare @sql varchar(5000)
1707		declare tCursor cursor local static for
1708			select '['+name+']'
1709			from sys.tables 
1710			where name like isnull('z' + @system + '_','') + 'Snapshot%' -- If @system is null, then the expression inside the isnull function will evaluate to null which will trigger the isnull function to return the empty string which in turn causes the query to return the records of the active snapshot.
1711		open tCursor
1712		fetch from tCursor into @tablename
1713		while (@@FETCH_STATUS = 0)
1714		begin
1715			set @sql = 'drop table ' + @tablename
1716			exec (@sql) 
1717			fetch from tCursor into @tablename
1718		end
1719		close tCursor
1720		deallocate tCursor 
1721	end
1722end
1723GO
1724IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadPages]') AND type in (N'P', N'PC'))
1725  Drop Procedure [dbo].[loadPages]
1726GO
1727Create Procedure dbo.loadPages
1728  @system varchar(20),
1729  @drawer varchar(4) = null ,
1730  @loadLockMode varchar(50) = 'Tablock'  -- Allowed values: Auto, Tablock, NoTablock. Setting default to Tablock to maintain behaviour prior to 2.4.02
1731as
1732begin
1733  set nocount on
1734  
1735  Set Transaction Isolation Level Read Uncommitted
1736     
1737  declare @logid int, @ProcName varchar(50)
1738  set @ProcName = Object_Name(@@PROCID)  
1739  exec logStart @procname, @system, @drawer,@param1=@loadLockMode, @ID = @logid output
1740  
1741    /* @loadLockMode is intended to control the lock behavior of the insert into MigrationPage.
1742      with(tablock) is used to attempt to influence the insert into optimized minimal logging
1743      mode. However, this will not happen if there is data in MigPage since it has a clustered
1744      index. It may be preferable to load data into MigPage while processing already loaded data.
1745      Tablock will not allow these operations concurrently. 
1746    */
1747  if(@loadLockMode not in('Auto','Tablock','NoTablock'))
1748  begin
1749    raiserror('Invalid value specified for @loadLockMode: %s',11,1,@loadLockMode);
1750    exec logEnd @logid;
1751    return;
1752  end
1753  
1754  exec prepareIndexes @table = 'SnapshotDocument3x', @index = 'ndx_Document'
1755  exec prepareIndexes @table = 'SnapshotFolder3x', @index = 'ndx_Folder'
1756  
1757  if (not exists(select ID from MigrationDocument where System = @system and SrcDrawer = isNull(@drawer,SrcDrawer) and Status = 1))
1758  begin
1759      -- Conditionally set the @loadLockMode if 'Auto' was specified in proc call
1760    declare @originalLoadLockMode varchar(50)=@loadLockMode
1761    if (not exists(select top 1 ID from MigrationDocument) and @loadLockMode='Auto') 
1762      set @loadLockMode='Tablock';
1763    else set @loadLockMode='NoTablock';
1764    raiserror(  'LoadLockMode was specified as %s; Effective loadLockMode is %s',0,1,@originalLoadLockMode,@loadLockMode)
1765    
1766    if (not exists(select ID from MigrationDocument where Status = 0))
1767    begin
1768        exec dbo.dropIndexes
1769    end
1770    exec dropData @system, @drawer
1771  end
1772  else
1773  begin
1774    RAISERROR('  Can not reload System since migration already in Progress!',16,1)
1775    exec logEnd @logid;
1776    RETURN
1777  end
1778      
1779  if (@drawer is null)
1780  begin
1781    raiserror ('Loading Pages for all Drawers in the ConfigDrawerList from System: %s',10,1,@system) with nowait  
1782    
1783    if(@loadLockMode in('Auto','Tablock'))
1784      insert into MigrationPage with (tablock)  -- TABLOCK for Auto and Tablock @loadLockMode values
1785          (FileID, DocumentID, System, SrcDrawer, IRFileNumber, SrcFolderPath, SrcDocType, 
1786          SrcUserKey1, IRDocID, SrcPageMark, SrcDocDate, PageNumber,
1787          IRPageDescription, SrcTempDin, Orientation,
1788          SrcArchiveStatus, SrcDeviceID, SrcDrive, SrcAMedia, SrcADrive, SrcFileName,
1789          SrcFormat, SrcFormat2, SrcFileNumber)
1790        select 
1791          FileID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber),
1792          DocumentID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber) + '_' + CAST(docid as varchar),
1793          System = @system,
1794          SrcDrawer = SnapshotDocument3x.drawer,
1795          IRFileNumber = rtrim(SnapshotDocument3x.foldernumber),
1796          SrcFolderPath = packagetype,
1797          SrcDocType = doctype,
1798          SrcUserKey1 = userkey1,
1799          IRDocID = docid,
1800          SrcPageMark = markedind,
1801          SrcDocDate = docdate,
1802          PageNumber = dsppagenumber,
1803          IRPageDescription = rtrim(reason),
1804          SrcTempDin = tempdin,
1805          Orientation = orientation,        
1806          SrcArchiveStatus = archivestatus,
1807          SrcDeviceID = deviceid,
1808          SrcDrive = drive,
1809          SrcAMedia = amedia,
1810          SrcADrive = adrive,
1811          SrcFilename = SnapshotDocument3x.filename,
1812          SrcFormat = format,
1813          SrcFormat2 = format2,
1814          SrcFileNumber = RTRIM(SnapshotDocument3x.foldernumber)
1815        from SnapshotDocument3x 
1816        inner join ConfigDrawerList 
1817          on SnapshotDocument3x.Drawer = ConfigDrawerList.Drawer
1818        where ConfigDrawerList.system = @system
1819         and status not in ('M','D') and purge <> 'All'
1820    else  -- Assumes @loadLockMode='NoTablock'
1821      insert into MigrationPage  -- No TABLOCK for other @loadLockMode values
1822          (FileID, DocumentID, System, SrcDrawer, IRFileNumber, SrcFolderPath, SrcDocType, 
1823          SrcUserKey1, IRDocID, SrcPageMark, SrcDocDate, PageNumber,
1824          IRPageDescription, SrcTempDin, Orientation,
1825          SrcArchiveStatus, SrcDeviceID, SrcDrive, SrcAMedia, SrcADrive, SrcFileName,
1826          SrcFormat, SrcFormat2, SrcFileNumber)
1827        select 
1828          FileID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber),
1829          DocumentID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber) + '_' + CAST(docid as varchar),
1830          System = @system,
1831          SrcDrawer = SnapshotDocument3x.drawer,
1832          IRFileNumber = rtrim(SnapshotDocument3x.foldernumber),
1833          SrcFolderPath = packagetype,
1834          SrcDocType = doctype,
1835          SrcUserKey1 = userkey1,
1836          IRDocID = docid,
1837          SrcPageMark = markedind,
1838          SrcDocDate = docdate,
1839          PageNumber = dsppagenumber,
1840          IRPageDescription = rtrim(reason),
1841          SrcTempDin = tempdin,
1842          Orientation = orientation,        
1843          SrcArchiveStatus = archivestatus,
1844          SrcDeviceID = deviceid,
1845          SrcDrive = drive,
1846          SrcAMedia = amedia,
1847          SrcADrive = adrive,
1848          SrcFilename = SnapshotDocument3x.filename,
1849          SrcFormat = format,
1850          SrcFormat2 = format2,
1851          SrcFileNumber = RTRIM(SnapshotDocument3x.foldernumber)
1852        from SnapshotDocument3x 
1853        inner join ConfigDrawerList 
1854          on SnapshotDocument3x.Drawer = ConfigDrawerList.Drawer
1855        where ConfigDrawerList.system = @system
1856         and status not in ('M','D') and purge <> 'All'
1857  end
1858  else
1859  begin
1860    raiserror ('Loading Pages for Drawer: %s from System: %s',10,1,@drawer,@system) with nowait  
1861    if not exists(select * from ConfigDrawerList where system = @system and drawer = @drawer)
1862    begin
1863      raiserror ('  Drawer is not in the ConfigDrawerList',16,1) with nowait  
1864      exec logEnd @logid;
1865      return
1866    end
1867    
1868    if(@loadLockMode in('Auto','Tablock'))
1869      insert into MigrationPage with (tablock)  -- TABLOCK for Auto and Tablock @loadLockMode values
1870        (FileID, DocumentID, System, SrcDrawer, IRFileNumber, SrcFolderPath, SrcDocType, 
1871        SrcUserKey1, IRDocID, SrcPageMark, SrcDocDate, PageNumber,
1872        IRPageDescription, SrcTempDin, Orientation,
1873        SrcArchiveStatus, SrcDeviceID, SrcDrive, SrcAMedia, SrcADrive, SrcFileName,
1874        SrcFormat, SrcFormat2, SrcFileNumber)
1875      select 
1876        FileID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber),
1877        DocumentID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber) + '_' + CAST(docid as varchar),
1878        System = @system,
1879        SrcDrawer = SnapshotDocument3x.drawer,
1880        IRFileNumber = rtrim(SnapshotDocument3x.foldernumber),
1881        SrcFolderPath = packagetype,
1882        SrcDocType = doctype,
1883        SrcUserKey1 = userkey1,
1884        IRDocID = docid,
1885        SrcPageMark = markedind,
1886        SrcDocDate = docdate,
1887        PageNumber = dsppagenumber,
1888        IRPageDescription = rtrim(reason),
1889        SrcTempDin = tempdin,
1890        Orientation = orientation,        
1891        SrcArchiveStatus = archivestatus,
1892        SrcDeviceID = deviceid,
1893        SrcDrive = drive,
1894        SrcAMedia = amedia,
1895        SrcADrive = adrive,
1896        SrcFilename = SnapshotDocument3x.filename,
1897        SrcFormat = format,
1898        SrcFormat2 = format2,
1899        SrcFileNumber = RTRIM(SnapshotDocument3x.foldernumber)
1900      from SnapshotDocument3x 
1901      inner join ConfigDrawerList 
1902        on SnapshotDocument3x.Drawer = ConfigDrawerList.Drawer
1903      where ConfigDrawerList.system = @system
1904       and status not in ('M','D') and purge <> 'All'
1905       and SnapshotDocument3x.drawer = @drawer
1906    else  -- Assumes @loadLockMode='NoTablock'
1907      insert into MigrationPage with (tablock)  -- No TABLOCK for other @loadLockMode values
1908        (FileID, DocumentID, System, SrcDrawer, IRFileNumber, SrcFolderPath, SrcDocType, 
1909        SrcUserKey1, IRDocID, SrcPageMark, SrcDocDate, PageNumber,
1910        IRPageDescription, SrcTempDin, Orientation,
1911        SrcArchiveStatus, SrcDeviceID, SrcDrive, SrcAMedia, SrcADrive, SrcFileName,
1912        SrcFormat, SrcFormat2, SrcFileNumber)
1913      select 
1914        FileID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber),
1915        DocumentID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber) + '_' + CAST(docid as varchar),
1916        System = @system,
1917        SrcDrawer = SnapshotDocument3x.drawer,
1918        IRFileNumber = rtrim(SnapshotDocument3x.foldernumber),
1919        SrcFolderPath = packagetype,
1920        SrcDocType = doctype,
1921        SrcUserKey1 = userkey1,
1922        IRDocID = docid,
1923        SrcPageMark = markedind,
1924        SrcDocDate = docdate,
1925        PageNumber = dsppagenumber,
1926        IRPageDescription = rtrim(reason),
1927        SrcTempDin = tempdin,
1928        Orientation = orientation,        
1929        SrcArchiveStatus = archivestatus,
1930        SrcDeviceID = deviceid,
1931        SrcDrive = drive,
1932        SrcAMedia = amedia,
1933        SrcADrive = adrive,
1934        SrcFilename = SnapshotDocument3x.filename,
1935        SrcFormat = format,
1936        SrcFormat2 = format2,
1937        SrcFileNumber = RTRIM(SnapshotDocument3x.foldernumber)
1938      from SnapshotDocument3x 
1939      inner join ConfigDrawerList 
1940        on SnapshotDocument3x.Drawer = ConfigDrawerList.Drawer
1941      where ConfigDrawerList.system = @system        
1942       and status not in ('M','D') and purge <> 'All'
1943       and SnapshotDocument3x.drawer = @drawer
1944  end 
1945  exec logEnd @logid 
1946end
1947GO
1948IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadDocuments]') AND type in (N'P', N'PC'))
1949  Drop Procedure [dbo].[loadDocuments]
1950GO
1951Create Procedure dbo.loadDocuments
1952  @system varchar(20),
1953  @drawer varchar(4) = null
1954as
1955begin
1956  set nocount on
1957  Set Transaction Isolation Level Read Uncommitted  
1958  
1959  declare @logid int, @ProcName varchar(50)
1960  set @ProcName = Object_Name(@@PROCID)
1961  exec logStart @procname, @system, @drawer, @ID = @logid output
1962  
1963  declare @defaultPriority int;
1964  select @defaultPriority=configValue from ConfigMigration where configKey='defaultMigrationPriority';
1965  
1966  if (@drawer is null)
1967  begin
1968    Raiserror ('Loading All Documents for System: %s',10,1,@system) with nowait
1969    
1970    insert into MigrationDocument with (tablock)(
1971		ID, 
1972		FileID, 
1973		Status, 
1974		SrcDocDate, 
1975		SrcDocID, 
1976		System, 
1977		SrcDrawer, 
1978		SrcFileNumber, 
1979		Priority, 
1980		PageCount,
1981		ExtCount
1982	)
1983    select 
1984		ID = DocumentID, 
1985		FileID, 
1986		Status = -2, 
1987		SrcDocDate = max(SrcDocDate), 
1988		SrcDocID, 
1989		System = @system, 
1990		SrcDrawer, 
1991		SrcFileNumber, 
1992		Priority = @defaultPriority, 
1993		PageCount = count(ID), 
1994		ExtCount = count(distinct SrcFormat+SrcFormat2)      -- ACM: ExtCount doesn't allow nulls
1995      from MigrationPage
1996     where System = @system
1997     group by DocumentID, FileID, SrcDocID, System, SrcDrawer, SrcFileNumber
1998  end
1999  else
2000  begin
2001    Raiserror ('Loading Documents for Drawer: %s from System: %s',10,1,@drawer,@system) with nowait  
2002    
2003    insert into MigrationDocument with (tablock)(
2004		ID, 
2005		FileID, 
2006		Status, 
2007		SrcDocDate, 
2008		SrcDocID, 
2009		System, 
2010		SrcDrawer, 
2011		SrcFileNumber, 
2012		Priority, 
2013		PageCount,
2014		ExtCount
2015	)
2016    select 
2017		ID = DocumentID, 
2018		FileID, 
2019		Status = -2, 
2020		SrcDocDate = max(SrcDocDate), 
2021		SrcDocID, 
2022		System = @system, 
2023		SrcDrawer, 
2024		SrcFileNumber, 
2025		Priority = @defaultPriority, 
2026		PageCount = count(ID), 
2027		ExtCount = count(distinct SrcFormat+SrcFormat2)      -- ACM: ExtCount doesn't allow nulls 
2028	  from MigrationPage
2029     where System = @system and SrcDrawer = @drawer
2030     group by DocumentID, FileID, SrcDocID, System, SrcDrawer, SrcFileNumber
2031       
2032  end   
2033  exec logEnd @logid    
2034end
2035GO
2036IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadFiles]') AND type in (N'P', N'PC'))
2037  Drop Procedure [dbo].[loadFiles]
2038GO
2039Create Procedure dbo.loadFiles
2040  @system varchar(20),
2041  @drawer varchar(4) = null
2042as
2043begin
2044  set nocount on
2045  Set Transaction Isolation Level Read Uncommitted
2046     
2047  declare @logid int, @ProcName varchar(50)
2048  set @ProcName = Object_Name(@@PROCID)  
2049  exec logStart @procname, @system, @drawer, @ID = @logid output
2050  
2051  declare @defaultPriority int;
2052  select @defaultPriority=configValue from ConfigMigration where configKey='defaultMigrationPriority';
2053
2054  exec prepareIndexes @table = 'SnapshotFolder3x', @index = 'ndx_Folder'
2055  
2056  if (@drawer is null)
2057  begin
2058    raiserror ('Loading Files for all Drawers in the ConfigDrawerList from System: %s',10,1,@system) with nowait  
2059    insert into MigrationFile
2060      (ID, System, SrcDrawer, SrcFileNumber, status, priority, mostrecentdocdate, pagecount, documentcount, taskcount)
2061    select FileID, System, SrcDrawer, SrcFileNumber, -2, @defaultPriority, max(SrcDocDate), sum(pagecount), count(ID), 0
2062      from MigrationDocument where System = @system
2063	   group by FileID, System, SrcDrawer, SrcFileNumber, status, priority
2064   
2065    exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2066    
2067    RAISERROR ('  Updating the Folder Name and Attributes', 10, 1) WITH NOWAIT
2068     -- Update Folder Name on every record    
2069     update MigrationFile
2070        set SrcFileName = SnapshotFolder3x.foldername,
2071            SrcUserData1 = SnapshotFolder3x.userdata1,
2072            SrcUserData2 = SnapshotFolder3x.userdata2,
2073            SrcUserData3 = SnapshotFolder3x.userdata3,
2074            SrcUserData4 = SnapshotFolder3x.userdata4,
2075            SrcUserData5 = SnapshotFolder3x.userdata5,
2076            SrcFileOwner = SnapshotFolder3x.userid,
2077			SrcFileMark1 = SnapshotFolder3x.FileMark1,
2078			SrcFileMark2 = SnapshotFolder3x.FileMark2,
2079			SrcFileMark3 = SnapshotFolder3x.FileMark3
2080       from SnapshotFolder3x
2081      where MigrationFile.SrcFileNumber = SnapshotFolder3x.foldernumber
2082        and MigrationFile.SrcDrawer = SnapshotFolder3x.drawer
2083        and MigrationFile.System = @system
2084  end
2085  else
2086  begin
2087    raiserror ('Loading Files for Drawer: %s from System: %s',10,1,@drawer,@system) with nowait  
2088    insert into MigrationFile
2089      (ID, System, SrcDrawer, SrcFileNumber, status, priority, mostrecentdocdate, pagecount, documentcount, taskcount)
2090    select FileID, System, SrcDrawer, SrcFileNumber, -2, @defaultPriority, max(Srcdocdate), sum(pagecount), count(ID), 0
2091      from MigrationDocument where System = @system and SrcDrawer = @drawer
2092	  group by FileID, System, SrcDrawer, SrcFileNumber, status, priority
2093   
2094   RAISERROR ('  Updating the Folder Name and Attributes', 10, 1) WITH NOWAIT   
2095   update MigrationFile
2096      set SrcFileName = SnapshotFolder3x.foldername,
2097          SrcUserData1 = SnapshotFolder3x.userdata1,
2098          SrcUserData2 = SnapshotFolder3x.userdata2,
2099          SrcUserData3 = SnapshotFolder3x.userdata3,
2100          SrcUserData4 = SnapshotFolder3x.userdata4,
2101          SrcUserData5 = SnapshotFolder3x.userdata5,
2102          SrcFileOwner = SnapshotFolder3x.userid,
2103		  SrcFileMark1 = SnapshotFolder3x.FileMark1,
2104		  SrcFileMark2 = SnapshotFolder3x.FileMark2,
2105		  SrcFileMark3 = SnapshotFolder3x.FileMark3
2106     from SnapshotFolder3x
2107    where MigrationFile.SrcFileNumber = SnapshotFolder3x.foldernumber
2108      and MigrationFile.SrcDrawer = SnapshotFolder3x.drawer
2109      and MigrationFile.System = @system
2110      and MigrationFile.SrcDrawer = @drawer
2111  end
2112  
2113  exec logEnd @logid
2114end
2115GO
2116IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadTasks]') AND type in (N'P', N'PC'))
2117  Drop Procedure [dbo].[loadTasks]
2118GO
2119Create Procedure dbo.loadTasks
2120  @system varchar(20),
2121  @drawer varchar(4) = null
2122as
2123begin
2124  set nocount on
2125  Set Transaction Isolation Level Read Uncommitted
2126  
2127  declare @logid int, @ProcName varchar(50)
2128  set @ProcName = Object_Name(@@PROCID)
2129  exec logStart @ProcName, @system, @drawer, @ID = @logid output  
2130  
2131  exec prepareIndexes @table = 'MigrationPage', @index = 'IX_MigrationPage_tempdin'
2132  
2133  if (@drawer is null)
2134  begin
2135    raiserror ('Loading Tasks for all Drawers in the ConfigDrawerList from System: %s',10,1,@system) with nowait   
2136    insert into MigrationTask
2137      (FileID, DocumentID, PageID, Priority, AvailableDate, System ,SrcDrawer,
2138       SrcTaskID, SrcFlowID, SrcStepID, SrcExtraKey, SrcAssignedToUserID,Description)
2139    select 
2140		 FileID 			= MigrationPage.FileID,
2141         DocumentID 		= MigrationPage.DocumentID,
2142         PageID 			= MigrationPage.ID,
2143         Priority 			= SnapshotTask3x.Priority,
2144         AvailableDate 		= SnapshotTask3x.AvailableDate,
2145         System 			= MigrationPage.System,
2146         SrcDrawer 			= MigrationPage.SrcDrawer,
2147         SrcTaskID 			= SnapshotTask3x.taskid,
2148         SrcFlowID 			= SnapshotTask3x.FlowID,
2149         SrcStepID 			= SnapshotTask3x.StepID,
2150         SrcExtraKey 		= SnapshotTask3x.UserKey2,
2151         SrcAssignedToUserID = SnapshotTask3x.userid,
2152		 Description 		= SnapshotTask3x.Description
2153      from SnapshotTask3x
2154      inner join MigrationPage on SrcTempDin = SnapshotTask3x.UserKey2
2155     where MigrationPage.System = @system
2156     
2157    update MigrationFile
2158       set TaskCount = t.TaskCount
2159      from (select fileid, count(*) as TaskCount from MigrationTask
2160             where System = @system
2161             group by fileid) t
2162     where MigrationFile.ID = t.FileID
2163       and MigrationFile.System = @system
2164  end
2165  else
2166  begin
2167    raiserror ('Loading Tasks for Drawer: %s from System: %s',10,1,@drawer,@system) with nowait  
2168    insert into MigrationTask
2169      (FileID, DocumentID, PageID, Priority, AvailableDate, System ,SrcDrawer,
2170       SrcTaskID, SrcFlowID, SrcStepID, SrcExtraKey, SrcAssignedToUserID,Description)
2171    select 
2172		 FileID 			= MigrationPage.FileID,
2173         DocumentID 		= MigrationPage.DocumentID,
2174         PageID 			= MigrationPage.ID,
2175         Priority 			= SnapshotTask3x.Priority,
2176         AvailableDate 		= SnapshotTask3x.AvailableDate,
2177         System 			= MigrationPage.System,
2178         SrcDrawer 			= MigrationPage.SrcDrawer,
2179         SrcTaskID 			= SnapshotTask3x.taskid,
2180         SrcFlowID 			= SnapshotTask3x.FlowID,
2181         SrcStepID 			= SnapshotTask3x.StepID,
2182         SrcExtraKey 		= SnapshotTask3x.UserKey2,
2183         SrcAssignedToUserID = SnapshotTask3x.userid,
2184		 Description 		= SnapshotTask3x.Description
2185      from SnapshotTask3x
2186      inner join MigrationPage on SrcTempDin = SnapshotTask3x.UserKey2
2187     where MigrationPage.System = @system
2188       and MigrationPage.SrcDrawer = @drawer
2189       
2190    update MigrationFile
2191       set TaskCount = t.TaskCount
2192      from (select fileid, count(*) as TaskCount from MigrationTask
2193             where System = @system
2194             group by fileid) t
2195     where MigrationFile.ID = t.FileID
2196       and MigrationFile.System = @system
2197       and MigrationFile.SrcDrawer = @drawer
2198  end
2199  exec logEnd @logid
2200end
2201GO
2202IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadPaths]') AND type in (N'P', N'PC'))
2203  Drop Procedure [dbo].[loadPaths]
2204GO
2205Create Procedure dbo.loadPaths
2206  @system varchar(50)
2207as
2208begin
2209  set nocount on
2210  Set Transaction Isolation Level Read Uncommitted
2211     
2212  declare @logid int, @ProcName varchar(50)
2213  set @ProcName = Object_Name(@@PROCID)  
2214  exec logStart @procname, @system, null, @ID = @logid output
2215  raiserror ('Loading Device and Nearline Paths from Snapshot: %s',10,1,@system) with nowait   
2216  
2217  delete from MigrationPath where System = @system
2218  
2219  insert into MigrationPath
2220  select @system, 'Active', DeviceID, DevicePath from SnapshotDevices3x
2221
2222  insert into MigrationPath
2223  select @system, 'Nearline', Volid, Path + VolID from SnapshotCds3x
2224  
2225  exec logEnd @logid 
2226end
2227GO
2228IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadTypes]') AND type in (N'P', N'PC'))
2229  Drop Procedure [dbo].[loadTypes]
2230GO
2231Create Procedure dbo.loadTypes
2232  @system varchar(50)
2233as
2234begin
2235  set nocount on
2236  Set Transaction Isolation Level Read Uncommitted
2237     
2238  declare @logid int, @ProcName varchar(50)
2239  set @ProcName = Object_Name(@@PROCID)  
2240  exec logStart @procname, @system, null, @ID = @logid output
2241  raiserror ('Loading Document and Folder Types from Snapshot: %s',10,1,@system) with nowait   
2242  
2243  delete from MigrationType where System = @system
2244  
2245  insert into MigrationType(system,kind,itemType,description)
2246  select distinct @system, 'Document', doctype, docdesc from SnapshotDocStruct3x
2247
2248  insert into MigrationType(system,kind,itemType,description)
2249  select distinct @system, 'Folder', packagetype, folderdesc from SnapshotDocStruct3x
2250  
2251  insert into MigrationType(system,kind,itemType,description)
2252  select distinct @system, 'Flow', FlowID, FlowDesc from SnapshotWorkflows3x
2253
2254  insert into MigrationType(system,kind,itemType,description)
2255  select distinct @system, 'Step', cast(FlowID as varchar) + '_' + cast(StepID as varchar), StepDesc from SnapshotWorkflows3x
2256  
2257  insert into MigrationType(system,kind,itemType,description)
2258  select distinct @system, 'User', userid, username from SnapshotUsers3x
2259  
2260    -- Page Marks: part 1 - Page marks by drawer and global page marks
2261  ;with AllDrawers as(
2262    select distinct drawer  -- Using all drawers to make sure that the global page marks are enabled for all drawers 
2263    from SnapshotDrawer3x 
2264    ),
2265  GlobalMarks as(
2266    select color, description
2267    from SnapshotPageMarks3x
2268    where drawer=''
2269    ),
2270  DrawerPageMarks as(	
2271    select drawer, color, description
2272    from SnapshotPageMarks3x
2273    where drawer<>''
2274    )
2275  insert into MigrationType(
2276    system,
2277    kind,
2278    drawer,
2279    ItemType,
2280    Description
2281    )
2282  select @system,
2283    'PageMark' as Kind,
2284    drawer, 
2285    color as ItemType, 
2286    description
2287  from DrawerPageMarks
2288  union all 
2289  select @system,
2290    'PageMark' as Kind,
2291    AllDrawers.drawer, 
2292    GlobalMarks.color as ItemType, 
2293    GlobalMarks.description
2294  from AllDrawers   -- Using all drawers to make sure that the global page marks are available for all drawers
2295  cross join GlobalMarks
2296  where not exists(
2297    select *
2298    from DrawerPageMarks
2299    where AllDrawers.drawer=DrawerPageMarks.drawer
2300      and GlobalMarks.color=DrawerPageMarks.color
2301      )
2302      
2303    -- Page Marks: part 2 - Page marks without descriptions 
2304  merge MigrationType
2305  using(
2306    select SnapshotDrawer3x.drawer,
2307      config3xMarkLookup.color,
2308      '<No Description - '+config3xMarkLookup.colorName+'>' as description
2309    from SnapshotDrawer3x
2310    cross join config3xMarkLookup
2311    )as NoDescriptionMarks
2312    on MigrationType.system=@system 
2313      and MigrationType.kind='PageMark'
2314      and MigrationType.drawer=NoDescriptionMarks.drawer 
2315      and MigrationType.ItemType=NoDescriptionMarks.color
2316  when not matched by target then insert(
2317    system,
2318    Kind,
2319    drawer,
2320    ItemType,
2321    Description
2322    )
2323    values(
2324      @system,
2325      'PageMark',
2326      NoDescriptionMarks.drawer,
2327      NoDescriptionMarks.color,
2328      NoDescriptionMarks.description
2329      );
2330  
2331  exec logEnd @logid 
2332end
2333GO
2334IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[load]') AND type in (N'P', N'PC'))
2335  Drop Procedure [dbo].[load]
2336GO
2337Create Procedure dbo.load
2338  @system varchar(20),
2339  @drawer varchar(4) = null,
2340  @loadLockMode varchar(50) = 'Tablock'
2341as
2342begin
2343  set nocount on
2344  Set Transaction Isolation Level Read Uncommitted
2345     
2346  declare @logid int, @ProcName varchar(50)
2347  set @ProcName = Object_Name(@@PROCID)  
2348  exec logStart @procname, @system, @drawer, @ID = @logid output
2349  
2350  if(@loadLockMode not in('Auto','Tablock','NoTablock'))
2351  begin
2352    raiserror('Invalid value specified for @loadLockMode: %s',11,1,@loadLockMode);
2353    exec logEnd @logid
2354    return;
2355  end
2356  
2357  exec loadPages @system, @drawer
2358  exec loadDocuments @system, @drawer
2359  exec loadFiles @system, @drawer
2360  exec loadTasks @system, @drawer
2361  exec loadPaths @system
2362  exec loadTypes @system
2363  
2364  exec logEnd @logid 
2365end
2366GO
2367IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prepareFiles]') AND type in (N'P', N'PC'))
2368  Drop Procedure [dbo].[prepareFiles]
2369GO
2370CREATE Procedure [dbo].[prepareFiles]
2371  @system varchar(50) = null,
2372  @drawer varchar(100) = null,
2373  @filenumber varchar(100) = null
2374as
2375begin
2376  set nocount on
2377  
2378  declare @logid int, @ProcName varchar(50)
2379  set @ProcName = Object_Name(@@PROCID)
2380  exec logStart @procname, @system, @drawer, @param1 = @filenumber, @ID = @logid output
2381  Raiserror ('    Preparing Files',10,1) with nowait
2382  create table #Files (ID varchar(100), status int, primary key(ID))
2383  
2384  if (@system is null)
2385    insert into #Files select ID, status from MigrationFile where status = -2
2386  else if @drawer is null
2387    insert into #Files select ID, status from MigrationFile where status = -2 and System = @system
2388  else if @filenumber is null
2389    insert into #Files select ID, status from MigrationFile where status = -2 and System = @system and SrcDrawer = @drawer
2390  else
2391    insert into #Files select ID, status from MigrationFile where status = -2 and System = @system and SrcDrawer = @drawer and SrcFileNumber = @filenumber
2392    
2393  update MigrationFile
2394     set status = 0
2395    from #Files
2396   where MigrationFile.ID = #Files.ID  
2397  
2398  exec logEnd @logid
2399end
2400GO
2401IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prepare]') AND type in (N'P', N'PC'))
2402  Drop Procedure [dbo].[prepare]
2403GO
2404CREATE Procedure [dbo].[prepare]
2405  @system varchar(50) = null  
2406as
2407begin
2408  set nocount on
2409  
2410  declare @logid int, @ProcName varchar(50)
2411  set @ProcName = Object_Name(@@PROCID)
2412  exec logStart @procname, @system, null, @ID = @logid output
2413  
2414  if (@system is null)
2415    Raiserror ('    Preparing System for Processing',10,1) with nowait
2416  else
2417    Raiserror ('    Preparing System for Processing: %s',10,1,@system) with nowait
2418  
2419  exec prepareIndexes  
2420  exec prepareFiles @system
2421
2422  exec logEnd @logid
2423end
2424GO
2425IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeTaskDocs]') AND type in (N'P', N'PC'))
2426  Drop Procedure [dbo].[prioritizeTaskDocs]
2427GO
2428CREATE procedure [dbo].[prioritizeTaskDocs]
2429  @system varchar(50),
2430  @drawer varchar(100) = null,
2431  @priority int,
2432  @override bit = 0  -- By default we do not lower a priority that is already set higher.  1 = override any priority
2433as
2434begin
2435  set nocount on
2436
2437  declare @logid int, @ProcName varchar(50)
2438  set @ProcName = Object_Name(@@PROCID)
2439  exec logStart @ProcName, @system, @drawer, @ID = @logid output
2440  
2441  exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2442  exec prepareIndexes @table = 'MigrationDocument', @index = 'IX_MigrationDocument_FileID'
2443  
2444  --create table #Files (id varchar(100), Priority int, primary key(id))
2445  create table #TaskDocs (FileID varchar(100), DocumentID varchar(100), Priority int);
2446  
2447  if (@drawer is null)
2448  begin
2449    Raiserror('  Setting Documents with Tasks for System %s to Priority: %d',10,1,@system, @priority) with nowait
2450    
2451    insert #TaskDocs(
2452      FileID,
2453      DocumentID,
2454      Priority
2455    )
2456    select distinct
2457      MigrationTask.FileID,
2458      MigrationTask.DocumentID,
2459      MigrationFile.priority
2460    from MigrationTask
2461    join MigrationFile on MigrationTask.FileID=MigrationFile.ID
2462    where MigrationFile.System=@System;
2463  end
2464  else
2465  begin
2466    Raiserror('  Setting Documents with Tasks for Drawer %s on System %s to Priority: %d',10,1,@drawer,@system, @priority) with nowait
2467    
2468    insert #TaskDocs(
2469      FileID,
2470      DocumentID,
2471      Priority
2472    )
2473    select distinct
2474      MigrationTask.FileID,
2475      MigrationTask.DocumentID,
2476      MigrationFile.priority
2477    from MigrationTask
2478    join MigrationFile on MigrationTask.FileID=MigrationFile.ID
2479    where MigrationFile.System=@System
2480      and MigrationFile.SrcDrawer=@drawer;
2481  end  
2482 
2483  update #TaskDocs set
2484      Priority=
2485        case
2486          when @override=1 then @priority
2487          when #TaskDocs.Priority>@priority then @priority
2488          else #TaskDocs.Priority
2489        end
2490  from #TaskDocs
2491   
2492   update MigrationDocument
2493      set Priority = #TaskDocs.priority
2494     from #TaskDocs
2495    where MigrationDocument.ID = #TaskDocs.DocumentID
2496  
2497  exec logEnd @logid
2498end
2499
2500
2501GO
2502IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeSample]') AND type in (N'P', N'PC'))
2503  Drop Procedure [dbo].[prioritizeSample]
2504GO
2505create procedure [dbo].[prioritizeSample]
2506  @system varchar(50),
2507  @drawer varchar(100) = null,
2508  @priority int,
2509  @override bit = 0  -- By default we do not lower a priority that is already set higher.  1 = override any priority
2510as
2511begin
2512  set nocount on
2513
2514  declare @logid int, @ProcName varchar(50)
2515  set @ProcName = Object_Name(@@PROCID)
2516  exec logStart @ProcName, @system, @drawer, @ID = @logid output
2517  
2518  exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2519  exec prepareIndexes @table = 'MigrationPage', @index = 'IX_MigrationPage_MappingSample'
2520
2521  if (@drawer is null)
2522    Raiserror('  Setting Sample Combination Files for System: %s to Priority: %d',10,1,@system, @priority) with nowait
2523  else
2524    Raiserror('  Setting Sample Combination Files for System: %s, Drawer: %s to Priority: %d',10,1,@system,@drawer, @priority) with nowait
2525    
2526  select fileid = min(fileid), System, SrcDrawer, SrcFolderPath, SrcDocType
2527    into #SampleDocCombinations
2528    from MigrationPage where System = @system and SrcDrawer = isNull(@drawer,SrcDrawer)
2529    group by System, SrcDrawer, SrcFolderPath, SrcDocType
2530
2531  select distinct fileid    
2532    into #filelist
2533    from #SampleDocCombinations
2534  
2535  if (object_id('MigrationTask') is not null)  -- Only do this if we are migrating tasks
2536  begin  
2537    select fileid = min(fileid), System, SrcDrawer, SrcFlowID, SrcStepID, SrcAssignedToUserID
2538      into #SampleTaskCombinations
2539      from MigrationTask where System = @system and SrcDrawer = isNull(@drawer,SrcDrawer)
2540     group by System, SrcDrawer, SrcFlowID, SrcStepID, SrcAssignedToUserID
2541    
2542    insert into #filelist
2543    select distinct fileid from #SampleTaskCombinations    
2544  end
2545          
2546  select id, priority into #files from MigrationFile where id in (select fileid from #filelist)
2547    
2548    update #Files
2549       set Priority = case 
2550                        when @override = 1 then @priority
2551                        when @priority < Priority then @priority 
2552                        else Priority
2553                      end
2554  
2555  update MigrationFile
2556     set Priority = #Files.priority
2557    from #Files
2558   where MigrationFile.ID = #Files.ID
2559
2560   update MigrationDocument
2561      set Priority = #Files.priority
2562     from #Files
2563    where MigrationDocument.FileID = #Files.ID       
2564  
2565  exec logEnd @logid
2566end
2567
2568
2569GO
2570IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeTaskFiles]') AND type in (N'P', N'PC'))
2571  Drop Procedure [dbo].[prioritizeTaskFiles]
2572GO
2573CREATE procedure [dbo].[prioritizeTaskFiles]
2574  @system varchar(50),
2575  @drawer varchar(100) = null,
2576  @priority int,
2577  @override bit = 0  -- By default we do not lower a priority that is already set higher.  1 = override any priority
2578as
2579begin
2580  set nocount on
2581
2582  declare @logid int, @ProcName varchar(50)
2583  set @ProcName = Object_Name(@@PROCID)
2584  exec logStart @ProcName, @system, @drawer, @ID = @logid output
2585  
2586  exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2587  exec prepareIndexes @table = 'MigrationDocument', @index = 'IX_MigrationDocument_FileID'
2588  
2589  create table #Files (id varchar(100), Priority int, primary key(id))
2590
2591  if (@drawer is null)
2592  begin
2593    Raiserror('  Setting Files with Tasks for System %s to Priority: %d',10,1,@system, @priority) with nowait
2594    insert into #Files
2595    select id, priority from MigrationFile where TaskCount > 0 and System = @system
2596  end
2597  else
2598  begin
2599    Raiserror('  Setting Files with Tasks for Drawer %s on System %s to Priority: %d',10,1,@drawer,@system, @priority) with nowait
2600    insert into #Files
2601    select id, priority from MigrationFile where TaskCount > 0 and System = @system and SrcDrawer = @drawer        
2602  end  
2603  
2604  update #Files
2605       set Priority = case 
2606                        when @override = 1 then @priority
2607                        when @priority < Priority then @priority 
2608                        else Priority
2609                      end
2610  
2611  update MigrationFile
2612     set Priority = #Files.priority
2613    from #Files
2614   where MigrationFile.ID = #Files.ID
2615   
2616   update MigrationDocument
2617      set Priority = #Files.priority
2618     from #Files
2619    where MigrationDocument.FileID = #Files.ID
2620  
2621  exec logEnd @logid
2622end
2623
2624
2625GO
2626IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeDocDate]') AND type in (N'P', N'PC'))
2627  Drop Procedure [dbo].[prioritizeDocDate]
2628GO
2629CREATE procedure [dbo].[prioritizeDocDate]
2630  @system varchar(50),
2631  @drawer varchar(100) = null,
2632  @docdate datetime,
2633  @priority int,
2634  @override bit = 0  -- By default we do not lower a priority that is already set higher.  1 = override any priority
2635as
2636begin
2637  set nocount on
2638  declare @strDate varchar(12)
2639  set @strDate = CONVERT(varchar(12),@docdate,101)
2640  
2641  declare @logid int, @ProcName varchar(50)
2642  set @ProcName = Object_Name(@@PROCID)
2643  exec logStart @ProcName, @system, @drawer, @param1 = @strdate, @ID = @logid output
2644  
2645  exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2646  exec prepareIndexes @table = 'MigrationDocument', @index = 'IX_MigrationDocument_FileID'
2647  
2648  create table #Files (id varchar(100), Priority int, primary key(id))
2649
2650  if (@drawer is null)
2651  begin
2652    Raiserror('  Setting Files with Document Date < %s for System: %s to Priority: %d',10,1,@strdate, @system, @priority) with nowait
2653    insert into #Files
2654    select id, priority from MigrationFile where MostRecentDocDate >= @docdate and System = @system     
2655  end
2656  else
2657  begin
2658    Raiserror('  Setting Files with Document Date < %s for System: %s, Drawer: %s to Priority: %d',10,1,@strdate, @system, @drawer, @priority) with nowait
2659    insert into #Files
2660    select id, priority from MigrationFile where MostRecentDocDate >= @docdate and System = @system and SrcDrawer = @drawer        
2661  end  
2662  
2663  update #Files
2664       set Priority = case 
2665                        when @override = 1 then @priority
2666                        when @priority < Priority then @priority 
2667                        else Priority
2668                      end
2669  
2670  update MigrationFile
2671     set Priority = #Files.priority
2672    from #Files
2673   where MigrationFile.ID = #Files.ID
2674   
2675   update MigrationDocument
2676      set Priority = #Files.priority
2677     from #Files
2678    where MigrationDocument.FileID = #Files.ID
2679  
2680  exec logEnd @logid
2681end
2682GO
2683IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeDrawer]') AND type in (N'P', N'PC'))
2684  Drop Procedure [dbo].[prioritizeDrawer]
2685GO
2686create procedure [dbo].[prioritizeDrawer]
2687  @system varchar(50),
2688  @drawer varchar(100),  
2689  @priority int,
2690  @override bit = 0  -- By default we do not lower a priority that is already set higher.  1 = override any priority
2691as
2692begin
2693  set nocount on
2694  
2695  declare @logid int, @ProcName varchar(50)
2696  set @ProcName = Object_Name(@@PROCID)
2697  exec logStart @ProcName, @system, @drawer, @ID = @logid output
2698  
2699  exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2700  exec prepareIndexes @table = 'MigrationDocument', @index = 'IX_MigrationDocument_FileID'
2701  
2702  create table #Files (id varchar(100), Priority int, primary key(id))
2703
2704  Raiserror('  Setting Files for System: %s in Drawer: %s to Priority: %d',10,1, @system, @drawer, @priority) with nowait
2705  insert into #Files
2706  select id, priority from MigrationFile where System = @system and SrcDrawer = @drawer  
2707  
2708  update #Files
2709       set Priority = case 
2710                        when @override = 1 then @priority
2711                        when @priority < Priority then @priority 
2712                        else Priority
2713                      end
2714  
2715  update MigrationFile
2716     set Priority = #Files.priority
2717    from #Files
2718   where MigrationFile.ID = #Files.ID
2719   
2720   update MigrationDocument
2721      set Priority = #Files.priority
2722     from #Files
2723    where MigrationDocument.FileID = #Files.ID  
2724  
2725  exec logEnd @logid
2726end
2727GO
2728IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeFile]') AND type in (N'P', N'PC'))
2729  Drop Procedure [dbo].[prioritizeFile]
2730GO
2731CREATE procedure [dbo].[prioritizeFile]
2732  @system varchar(50),
2733  @drawer varchar(100),  
2734  @filenumber varchar(255),
2735  @priority int,
2736  @override bit = 0  -- By default we do not lower a priority that is already set higher.  1 = override any priority
2737as
2738begin
2739  set nocount on
2740  
2741  declare @logid int, @ProcName varchar(50)
2742  set @ProcName = Object_Name(@@PROCID)
2743  exec logStart @ProcName, @system, @drawer, @ID = @logid output
2744  
2745  Raiserror('  Setting Files for System: %s, Drawer: %s, File Number: %s to Priority: %d',10,1, @system, @drawer, @filenumber, @priority) with nowait
2746  
2747  exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2748  exec prepareIndexes @table = 'MigrationDocument', @index = 'IX_MigrationDocument_FileID'
2749  
2750  create table #Files (id varchar(100), Priority int, primary key(id))
2751
2752  insert into #Files
2753  select id, priority from MigrationFile where System = @system and SrcDrawer = @drawer and SrcFileNumber = @filenumber
2754  
2755  update #Files
2756       set Priority = case 
2757                        when @override = 1 then @priority
2758                        when @priority < Priority then @priority 
2759                        else Priority
2760                      end
2761  
2762  update MigrationFile
2763     set Priority = #Files.priority
2764    from #Files
2765   where MigrationFile.ID = #Files.ID
2766   
2767   update MigrationDocument
2768      set Priority = #Files.priority
2769     from #Files
2770    where MigrationDocument.FileID = #Files.ID  
2771  
2772  
2773  exec logEnd @logid
2774end
2775GO
2776IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritize]') AND type in (N'P', N'PC'))
2777  Drop Procedure [dbo].[prioritize]
2778GO
2779CREATE procedure [dbo].[prioritize]
2780  @system varchar(50),
2781  @drawer varchar(100) = null  
2782as
2783begin
2784  set nocount on
2785
2786  declare @logid int, @ProcName varchar(50)
2787  set @ProcName = Object_Name(@@PROCID)
2788  exec logStart @ProcName, @system, @drawer, @ID = @logid output
2789  
2790  exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2791  if (@drawer is null)
2792    Raiserror('  Prioritizing Files for System: %s',10,1,@system) with nowait
2793  else
2794    Raiserror('  Prioritizing Files for System: %s',10,1,@system) with nowait
2795    
2796  select * into #rules from ConfigPriority where System = @system
2797  if (@drawer is not null)
2798    delete from #rules where SrcDrawer <> @drawer
2799  
2800  declare @priority int, @filedate datetime, @filenumber varchar(100), @rule varchar(15)
2801  declare @description varchar(200)
2802
2803  declare pCursor cursor local static for
2804    select Priority, [Rule], System, SrcDrawer, SrcFileNumber, filedate, description
2805     from #rules order by Priority
2806  open pCursor
2807  fetch from pCursor into @priority, @rule, @system, @drawer, @filenumber, @filedate, @description
2808  while @@fetch_status = 0
2809  begin
2810    Raiserror('    %s',10,1,@description) with nowait
2811    if @rule = 'Drawer'
2812      exec prioritizeDrawer @system, @drawer, @priority
2813    else if @rule = 'Sample'
2814      exec prioritizeSample @system, @drawer, @priority
2815    else if @rule = 'TaskFiles'
2816      exec prioritizeTaskFiles @system, @drawer, @priority
2817    else if @rule = 'TaskDocs'
2818      exec prioritizeTaskDocs @system, @drawer, @priority
2819    else if @rule = 'DocDate'
2820      exec prioritizeDocDate @system, @drawer, @filedate, @priority
2821    else if @rule = 'File'
2822      exec prioritizeFile @system, @drawer, @filenumber, @priority
2823  
2824    fetch from pCursor into @priority, @rule, @system, @drawer, @filenumber, @filedate, @description
2825  end
2826  close pCursor
2827  deallocate pCursor
2828  
2829  exec logEnd @logid
2830end
2831
2832
2833GO
2834IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[purge]') AND type in (N'P', N'PC'))
2835  Drop Procedure [dbo].[purge]
2836GO
2837CREATE Procedure [dbo].[purge]
2838  @system varchar(50) = null,
2839  @drawer varchar(100) = null,
2840  @filenumber varchar(255) = null
2841as
2842begin
2843  set nocount on
2844    
2845  declare @logid int, @ProcName varchar(50)
2846  set @ProcName = Object_Name(@@PROCID)
2847  exec logStart @procname, @system, @drawer, @param1 = @filenumber, @ID = @logid output  
2848  
2849  if exists(select * from ConfigDrawerList 
2850    where system = isNull(@system,system) and drawer = isNull(@drawer,drawer)
2851      and purge in ('Date','Files'))
2852  begin
2853    Raiserror ('    Marking Files for Purge',10,1) with nowait
2854    update MigrationFile
2855       set status = -9
2856      from ConfigDrawerList, ConfigPurgeList
2857     where MigrationFile.System = ConfigDrawerList.system
2858       and MigrationFile.SrcDrawer = ConfigDrawerList.drawer
2859       and MigrationFile.System = ConfigPurgeList.System
2860       and MigrationFile.SrcDrawer = ConfigPurgeList.SrcDrawer
2861       and MigrationFile.SrcFileNumber = ConfigPurgeList.SrcFileNumber
2862       and ConfigDrawerList.purge = 'Files'
2863       and MigrationFile.System = isNull(@system,MigrationFile.System)
2864       and MigrationFile.SrcDrawer = isNull(@drawer,MigrationFile.SrcDrawer)
2865       and MigrationFile.SrcFileNumber = isNull(@filenumber, MigrationFile.SrcFileNumber)
2866       
2867    update MigrationFile
2868       set status = -9
2869     from ConfigDrawerList
2870    where MigrationFile.System = ConfigDrawerList.system
2871      and MigrationFile.SrcDrawer = ConfigDrawerList.drawer
2872      and MigrationFile.MostRecentDocDate < ConfigDrawerList.purgedate
2873      and ConfigDrawerList.purge = 'Date'
2874      and ConfigDrawerList.purgedate is not null
2875      and MigrationFile.System = isNull(@system,MigrationFile.System)
2876      and MigrationFile.SrcDrawer = isNull(@drawer,MigrationFile.SrcDrawer)
2877      and MigrationFile.SrcFileNumber = isNull(@filenumber, MigrationFile.SrcFileNumber)
2878      
2879    -- Update all the Documents
2880    update MigrationDocument
2881       set status = -9
2882      from MigrationFile
2883     where MigrationDocument.FileID = MigrationFile.ID
2884       and MigrationFile.status = -9
2885       
2886  end
2887  else
2888    Raiserror ('    No Matching Purge Directives',10,1) with nowait
2889  
2890  exec logEnd @logid
2891end
2892GO
2893IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[processPages]') AND type in (N'P', N'PC'))
2894  Drop Procedure [dbo].[processPages]
2895GO
2896create Procedure [dbo].[processPages]
2897  @fileid varchar(100),
2898   -- preferNearline: If set to 1, file paths will prefer the nearline
2899  --                 path on active images if nearline image is available.
2900  @preferNearline bit = 0,
2901  @processAttributes bit = 0,	-- Default to 1 to try to execute the processAttributes stored proc. 
2902  @debug bit = 0
2903as
2904begin
2905  set nocount on
2906  Set Transaction Isolation Level Read Uncommitted
2907  
2908  declare @system varchar(40), @drawer varchar(4), @fileno varchar(50), @filename varchar(100)
2909  select @system = System, @drawer = SrcDrawer, @fileno = SrcFileNumber, @filename = SrcFileName
2910    from MigrationFile where ID = @fileid
2911  
2912  declare @logid int, @ProcName varchar(50)
2913  set @ProcName = Object_Name(@@PROCID)
2914  exec logStart @procname, @system, @drawer, @param1 = @fileno, @param2 = @fileid, @ID = @logid output
2915  
2916  if (@debug >= 1)
2917	Raiserror ('  Processing Documents for System: %s, Drawer: %s, File: %s',10,1,@system,@drawer,@fileno) with nowait
2918  
2919  update MigrationFile set status = 2 where ID = @fileid
2920  
2921  if (object_id('tempdb..#Pages') is not null)
2922    drop table #Pages
2923  select MigrationPage.*,
2924	MigrationFile.SrcUserData1,
2925	MigrationFile.SrcUserData2,
2926	MigrationFile.SrcUserData3,
2927	MigrationFile.SrcUserData4,
2928	MigrationFile.SrcUserData5,
2929	MigrationFile.SrcFileMark1,
2930	MigrationFile.SrcFileMark2,
2931	MigrationFile.SrcFileMark3
2932  into #Pages 
2933  from MigrationPage 
2934  join MigrationFile on MigrationPage.System=MigrationFile.System	-- Using IX_MigrationFile_SystemDrawerFile index
2935		and MigrationPage.SrcDrawer=MigrationFile.SrcDrawer
2936		and MigrationPage.SrcFileNumber=MigrationFile.SrcFileNumber
2937  where MigrationPage.FileID = @fileid  	--Using IX_MigrationPage_FileID
2938  
2939  if (@debug >= 2)
2940    RAISERROR ('  Updating the File Path based on ArchiveStatus', 10, 1) WITH NOWAIT 
2941  
2942  if (@preferNearline = 1)  -- use nearline path for active images if available.
2943  begin
2944    if (@debug >= 2)
2945      raiserror ('  NEARLINE is Preferred to Active',10,1) with nowait
2946    if (@debug >= 2)
2947      raiserror ('    Computing FilePaths for Images on Nearline!',10,1) with nowait
2948    update #Pages
2949       set FilePath = MigrationPath.path + SrcFileName + '.' + SrcFormat + ISNULL(SrcFormat2,'')               
2950      from MigrationPath
2951     where MigrationPath.ID = #Pages.SrcDrive
2952       and len(rtrim(#Pages.SrcDrive)) > 0
2953       and #Pages.SrcArchiveStatus in ('A','I')
2954       and MigrationPath.System = @system
2955       and MigrationPath.storage = 'Nearline'       
2956
2957    if (@debug >= 2)
2958      raiserror ('    Computing FilePaths for Images on Active!',10,1) with nowait
2959    update #Pages
2960       set FilePath = MigrationPath.path + SrcFileName + '.' + SrcFormat + ISNULL(SrcFormat2,'')
2961     from MigrationPath
2962    where MigrationPath.ID = #Pages.SrcDeviceID
2963      and #Pages.SrcArchiveStatus in ('A','I')  
2964      and len(rtrim(#Pages.SrcDrive)) = 0
2965      and MigrationPath.System = @system
2966      and MigrationPath.storage = 'Active'      
2967    end
2968  else -- Use the active file path for active images.
2969  begin
2970    if (@debug >= 2)
2971      raiserror ('    Computing FilePaths for Images on Active!',10,1) with nowait
2972    update #Pages
2973       set FilePath = MigrationPath.path + SrcFileName + '.' + SrcFormat + ISNULL(SrcFormat2,'')
2974     from MigrationPath
2975    where MigrationPath.id = #Pages.SrcDeviceID
2976      and #Pages.SrcArchiveStatus in ('A','I')  
2977      and MigrationPath.System = @system
2978      and MigrationPath.storage = 'Active'      
2979  end
2980       
2981   -- These are archived images so we have to go to nearline   
2982  if (@debug >= 2)
2983      raiserror ('    Computing FilePaths for Archived Images on Nearline!',10,1) with nowait               
2984  update #Pages
2985     set FilePath = MigrationPath.path + SrcFileName + '.' + SrcFormat + ISNULL(SrcFormat2,'')               
2986   from MigrationPath
2987  where MigrationPath.ID = #Pages.SrcDrive
2988    and #Pages.SrcArchiveStatus in ('C','R')
2989    and MigrationPath.System = @system
2990    and MigrationPath.storage = 'Nearline'
2991
2992  -- Populate Annotations Table for annotations on Nearline
2993  if (@debug >= 2)
2994      raiserror ('    Computing FilePaths for Archived Annotations on Nearline!',10,1) with nowait               
2995     -- For Annotations that have been sent to nearline.
2996  update #Pages
2997     set AnnotationsPath = MigrationPath.Path + #Pages.SrcFileName + '.ann'
2998   from MigrationPath
2999  where MigrationPath.ID = #Pages.SrcADrive
3000    and len(#Pages.SrcADrive) > 0
3001    and #Pages.SrcAMedia <> 'D'
3002    and #Pages.SrcArchiveStatus in ('C','R')
3003    and MigrationPath.System = @system
3004    and MigrationPath.storage = 'Nearline'    
3005      
3006  -- For Annotations that have not gone to Nearline (ie. Still with Active Image)
3007  if (@debug >= 2)
3008      raiserror ('    Computing FilePaths for Annotations on Active!',10,1) with nowait               
3009  update #Pages
3010     set AnnotationsPath = MigrationPath.path + SrcFileName + '.ann'
3011    from MigrationPath
3012  where MigrationPath.ID = #Pages.SrcDeviceID
3013    and len(SrcAMedia) > 0
3014    and (#Pages.SrcArchiveStatus in ('A','I') or #Pages.SrcAMedia = 'D')
3015    and MigrationPath.System = @system
3016    and MigrationPath.storage = 'Active'    
3017  
3018  -- Fix Page Errors
3019  if exists(select * from ConfigPageCorrection where fileid = @fileid)
3020  begin
3021    if (@debug >= 2) 
3022      Raiserror ('    Fixing Page Errors',10,1) with nowait 
3023
3024    update #Pages
3025       set FilePath = NewFilePath
3026      from ConfigPageCorrection
3027     where #Pages.FilePath = ConfigPageCorrection.OldFilePath
3028       and ConfigPageCorrection.fileid = @fileid
3029       
3030    update #Pages
3031       set AnnotationsPath = NewFilePath
3032      from ConfigPageCorrection
3033     where #Pages.AnnotationsPath = ConfigPageCorrection.OldFilePath
3034       and ConfigPageCorrection.fileid = @fileid        
3035  end
3036  
3037  -- Populate Attributes
3038  if @processAttributes=1
3039  begin
3040    if (@debug >= 2)
3041        raiserror ('    Updating Attributes',10,1) with nowait  
3042    exec processAttributes @system, @drawer, @fileid, @debug
3043  end
3044  
3045  -- Apply file mappings to loaded data in Migration
3046  update #Pages
3047     set IRDrawer = MapDocument.DestDrawer,
3048         IRFileType = MapDocument.DestFileType,
3049         IRFolderType = MapDocument.DestFolderPath,
3050         IRDocType = MapDocument.DestDocType
3051    from MapDocument
3052  where #Pages.System = MapDocument.System
3053    and #Pages.SrcDrawer = MapDocument.SrcDrawer
3054    and #Pages.SrcFolderPath = MapDocument.SrcFolderPath
3055    and #Pages.SrcDocType = MapDocument.SrcDocType      
3056	
3057  if (@debug >= 2)
3058    Raiserror ('    Parameter Replacement Folder Type Paths',10,1) with nowait 
3059  declare @maxAttribute int, @currentAttribute int, @sql nvarchar(300), @attrib varchar(20)
3060  set @maxAttribute = 15
3061  set @currentAttribute = 1
3062  
3063  while @currentAttribute <= @maxAttribute
3064  begin
3065    set @attrib = 'IRAttribute' + cast(@currentAttribute as varchar)
3066    if exists(select * from #Pages where IRFolderType like '%${' + @attrib + '}%')
3067    begin
3068      if (@debug >= 2)
3069        Raiserror ('      %s',10,1,@attrib) with nowait 
3070      set @sql = 'update #Pages set IRFolderType = REPLACE(IRFolderType,''${' + @attrib + '}'', ISNULL(' + @attrib + ','''')) ' + 
3071                 ' where IRFolderType like ''%${' + @attrib + '}%'' and ' + @attrib + ' is not null'      
3072      exec (@sql)
3073    end
3074    set @currentAttribute = @currentAttribute + 1
3075 end
3076
3077 if exists(select * from #Pages where IRFolderType like '%${IRFileName}%')
3078 begin
3079   if (@debug >= 2)
3080     Raiserror ('      IRFileName',10,1,@attrib) with nowait 
3081   update #Pages set IRFolderType = REPLACE(IRFolderType,'${IRFileName}',IRFileName) where IRFolderType like '%${IRFileName}%'
3082 end
3083
3084  /* Parameter Replacement for DocTypes */
3085  if (@debug >= 2)
3086    Raiserror ('    Parameter Replacement Document Types',10,1) with nowait 
3087  set @currentAttribute = 1
3088  
3089  while @currentAttribute <= @maxAttribute
3090  begin
3091    set @attrib = 'IRAttribute' + cast(@currentAttribute as varchar)
3092    if exists(select * from #Pages where IRDocType like '%${' + @attrib + '}%')
3093    begin
3094      if (@debug >= 1)
3095		Raiserror ('      %s',10,1,@attrib) with nowait 
3096      set @sql = 'update #Pages set IRDocType = REPLACE(IRDocType,''${' + @attrib + '}'', ISNULL(' + @attrib + ','''')) ' + 
3097                 ' where IRDocType like ''%${' + @attrib + '}%'' and ' + @attrib + ' is not null'
3098      exec (@sql)
3099    end
3100    set @currentAttribute = @currentAttribute + 1
3101  end
3102  
3103  -- Page Marks
3104  if (@debug >= 2)
3105    Raiserror ('    Mapping PageMarks',10,1) with nowait 
3106  update #Pages
3107     set IRPageMark = MapMark.DestMarkID
3108  from MapMark
3109 where MapMark.System = #Pages.System
3110   and MapMark.SrcDrawer = #Pages.SrcDrawer
3111   and MapMark.MarkType = 'page'
3112   and MapMark.SrcMarkID = #Pages.SrcPageMark
3113   and #Pages.SrcPageMark > 0
3114   and len(MapMark.DestMarkID) > 0  -- Excludes mappings we intentionally suppress.
3115  
3116  -- Update the MigrationPage Table
3117  update MigrationPage
3118     set FilePath = #Pages.FilePath,
3119         AnnotationsPath = #Pages.AnnotationsPath,
3120         IRFileName = @filename,
3121         IRDrawer = #Pages.IRDrawer,
3122         IRFileType = #Pages.IRFileType,
3123         IRFileNumber = #Pages.IRFileNumber,
3124         IRFolderType = #Pages.IRFolderType,
3125         IRDocType = #Pages.IRDocType,
3126         IRPageMark = #Pages.IRPageMark,
3127         IRDocDate = #Pages.SrcDocDate,   -- Just bring doc date over as same date value for now
3128         IRAttribute1 = #Pages.IRAttribute1,
3129         IRAttribute2 = #Pages.IRAttribute2,
3130         IRAttribute3 = #Pages.IRAttribute3,
3131         IRAttribute4 = #Pages.IRAttribute4,
3132         IRAttribute5 = #Pages.IRAttribute5,
3133         IRAttribute6 = #Pages.IRAttribute6,
3134         IRAttribute7 = #Pages.IRAttribute7,
3135         IRAttribute8 = #Pages.IRAttribute8,
3136         IRAttribute9 = #Pages.IRAttribute9,
3137         IRAttribute10 = #Pages.IRAttribute10,
3138         IRAttribute11 = #Pages.IRAttribute11,
3139         IRAttribute12 = #Pages.IRAttribute12,
3140         IRAttribute13 = #Pages.IRAttribute13,
3141         IRAttribute14 = #Pages.IRAttribute14,
3142         IRAttribute15 = #Pages.IRAttribute15
3143    from #Pages         
3144   where MigrationPage.FileID = @fileid
3145     and MigrationPage.ID = #Pages.ID
3146   
3147   -- Process the FUP File Entries
3148   /* 
3149	   -- commenting out code block due to numerous errors with the FUP part of the procecude.
3150			-- Last error thrown before trying to run this before commenting it out:
3151				-- Msg 515, Level 16, State 2, Procedure processPages, Line 276
3152				-- Cannot insert the value NULL into column 'IRDrawer', table 'tempdb.dbo.#FUPRecords_________________________________________________________________________________________________________00000000009F'; column does not allow nulls. INSERT fails.
3153				--
3154			-- Inserting into #FUPRecords will error out the proc if there is no mapping for the current records
3155				-- This is because null values are attempting to be inserted into NOT NULL columns.
3156				
3157				
3158	   CREATE TABLE #FUPRecords
3159	(	
3160		[System] [varchar](100) NOT NULL,
3161		[SrcDrawer] [varchar](4) NOT NULL,
3162		[FileID] [varchar](100) NOT NULL,
3163		[IRDrawer] [varchar](50) NOT NULL,		-- If these IR columns are specified as NOT NULL, then the entire procedure will fail if the current records doesn't have any mapping.
3164		[IRFileType] [varchar](50) NOT NULL,
3165		[IRFileNumber] [varchar](50) NOT NULL,
3166		[IRFileName] [varchar](50) NULL,
3167		[SrcUserData1] [varchar](10) NULL,
3168		[SrcUserData2] [varchar](20) NULL,
3169		[SrcUserData3] [varchar](30) NULL,
3170		[SrcUserData4] [varchar](40) NULL,
3171		[SrcUserData5] [varchar](50) NULL,
3172		[SrcFileOwner] [varchar](50) NULL,
3173		[SrcFileMark1] [int] NULL,
3174		[SrcFileMark2] [int] NULL,
3175		[SrcFileMark3] [int] NULL,
3176		[UserData1AttrName] [varchar](50) NULL,
3177		[UserData2AttrName] [varchar](50) NULL,
3178		[UserData3AttrName] [varchar](50) NULL,
3179		[UserData4AttrName] [varchar](50) NULL,
3180		[UserData5AttrName] [varchar](50) NULL,
3181		[FileOwnerAttrName] [varchar](50) NULL,
3182		[FileMark1ID] [int] NULL,
3183		[FileMark2ID] [int] NULL,
3184		[FileMark3ID] [int] NULL)	
3185	 
3186	insert into #FUPRecords
3187	(
3188		System, 
3189		SrcDrawer, 
3190		FileID, 
3191		IRDrawer, 
3192		IRFileType, 
3193		IRFileNumber, 
3194		IRFileName,
3195		SrcUserData1, 
3196		SrcUserData2, 
3197		SrcUserData3, 
3198		SrcUserData4, 
3199		SrcUserData5, 
3200		SrcFileOwner, 
3201		SrcFileMark1, 
3202		SrcFileMark2, 
3203		SrcFileMark3
3204	)    
3205	select distinct * 
3206	  from (
3207			select 
3208				#Pages.System, 
3209				#Pages.SrcDrawer, 
3210				#Pages.Fileid, 
3211				#Pages.IRDrawer, 
3212				#Pages.IRFileType, 
3213				#Pages.IRFileNumber, 
3214				IRFileName = @filename, 
3215				MigrationFile.SrcUserData1, 
3216				MigrationFile.SrcUserData2, 
3217				MigrationFile.SrcUserData3, 
3218				MigrationFile.SrcUserData4, 
3219				MigrationFile.Srcuserdata5, 
3220				MigrationFile.Srcfileowner, 
3221				MigrationFile.SrcFileMark1, 
3222				MigrationFile.SrcFileMark2, 
3223				MigrationFile.SrcFileMark3
3224			from #Pages
3225			inner join MigrationFile on #Pages.FileID = MigrationFile.ID
3226		) pagelist
3227	
3228	
3229	-- Map File Level Attributes
3230	update #FUPRecords
3231	   set UserData1AttrName = MapFileTypeAttribute.DestAttrName1,
3232		   UserData2AttrName = MapFileTypeAttribute.DestAttrName2,
3233		   UserData3AttrName = MapFileTypeAttribute.DestAttrName3,
3234		   UserData4AttrName = MapFileTypeAttribute.DestAttrName4,
3235		   UserData5AttrName = MapFileTypeAttribute.DestAttrName5
3236	  from MapFileTypeAttribute
3237	 where #FUPRecords.irFileType = MapFileTypeAttribute.DestFileType
3238
3239    -- Update Records if no filemark is set
3240	update #FUPRecords set FileMark1ID = 0 where SrcFileMark1 = 0
3241	update #FUPRecords set FileMark2ID = 0 where SrcFileMark2 = 0
3242	update #FUPRecords set FileMark3ID = 0 where SrcFileMark3 = 0
3243
3244	-- Map the FileMarks
3245	update #FUPRecords
3246	   set FileMark1ID = DestMarkID
3247	  from MapMark
3248	 where #FUPRecords.IRFileType = MapMark.DestFileType
3249	   and MapMark.MarkType='file'
3250	   and #FUPRecords.SrcFileMark1 = SrcMarkID 
3251
3252	update #FUPRecords
3253	   set FileMark2ID = DestMarkID
3254	  from MapMark
3255	 where #FUPRecords.IRFileType = MapMark.DestFileType
3256	   and MapMark.MarkType='file'
3257	   and #FUPRecords.SrcFileMark2 = SrcMarkID 
3258
3259	update #FUPRecords
3260	   set FileMark3ID = DestMarkID
3261	  from MapMark
3262	 where #FUPRecords.IRFileType = MapMark.DestFileType
3263	   and MapMark.MarkType='file'
3264	   and #FUPRecords.SrcFileMark3 = SrcMarkID
3265
3266	-- Insert new FUP Record
3267	insert into MigrationFUP(
3268		Status, 
3269		System, 
3270		SrcDrawer, 
3271		FileID, 
3272		IRDrawer, 
3273		IRFileType, 
3274		IRFileNumber, 
3275		IRFileName,
3276		IRAttributeValue1, 
3277		IRAttributeValue2, 
3278		IRAttributeValue3, 
3279		IRAttributeValue4, 
3280		IRAttributeValue5, 
3281		SrcFileOwner, 
3282		SrcFileMark1, 
3283		SrcFileMark2, 
3284		SrcFileMark3,
3285		IRAttributeName1, 
3286		IRAttributeName2, 
3287		IRAttributeName3, 
3288		IRAttributeName4, 
3289		IRAttributeName5, 
3290		FileOwnerAttrName, 
3291		IRFileMarkID1, 
3292		IRFileMarkID2, 
3293		IRFileMarkID3
3294	)
3295	select 
3296		Status = 0,  
3297		System, 
3298		SrcDrawer, 
3299		FileID, 
3300		IRDrawer, 
3301		IRFileType, 
3302		IRFileNumber, 
3303		IRFileName,
3304		IRAttributeValue1 = SrcUserData1, 
3305		IRAttributeValue2 = SrcUserData2, 
3306		IRAttributeValue3 = SrcUserData3, 
3307		IRAttributeValue4 = SrcUserData4, 
3308		IRAttributeValue5 = SrcUserData5, 
3309		SrcFileOwner, 
3310		SrcFileMark1, 
3311		SrcFileMark2, 
3312		SrcFileMark3,
3313		IRAttributeName1 = UserData1AttrName, 
3314		IRAttributeName2 = UserData2AttrName, 
3315		IRAttributeName3 = UserData3AttrName, 
3316		IRAttributeName4 = UserData4AttrName, 
3317		IRAttributeName5 = UserData5AttrName, 
3318		FileOwnerAttrName, 
3319		IRFileMarkID1 = FileMark1ID, 
3320		IRFileMarkID2 = FileMark2ID, 
3321		IRFileMarkID3 = FileMark3ID		
3322	from #FUPRecords
3323	-- Done with: Process the FUP File Entries
3324	   
3325	*/	-- End of commented out code block for the FUP processing
3326
3327
3328   -- Process Pages Put on Hold During Mapping
3329   select MigrationDocument.ID, Status, HoldReason 
3330     into #Documents
3331     from MigrationDocument, #Pages
3332    where #Pages.DocumentID = MigrationDocument.ID
3333   
3334   -- Reset all previous items marked for Purge (they might be okay now)
3335   update #Documents set status = -2 where status = -9   
3336   -- If anything was marked "Do Not Migrate"... then set it to purge.   
3337   update #Documents 
3338      set status = -9 
3339    where ID in (select distinct DocumentID from #Pages where IRDrawer = 'Do Not Migrate')
3340   
3341   -- Place unmapped documents on hold  
3342  update #Documents
3343     set status = -2
3344   where ID in (select DocumentID from #Pages)  
3345     and HoldReason in ('Unmapped Document','Some Pages have no Image File Path')
3346   
3347  update #Documents
3348     set status = -5, HoldReason = 'Unmapped Document'
3349   where ID in (select DocumentID from #Pages where IRDrawer is null or IRDrawer='')  
3350   
3351  update #Documents
3352     set status = -5, HoldReason = 'Unmapped Document (Intentional SrcDrawer={$HOLD})'
3353   where ID in (select DocumentID from #Pages where IRDrawer like'%{$HOLD}%')
3354  
3355  update #Documents
3356     set status = -5, HoldReason = 'Some Pages have no Image File Path'
3357   where ID in (select DocumentID from #Pages where FilePath is null)
3358  
3359  -- Make the document update
3360  update MigrationDocument
3361     set status = #Documents.status,         
3362         HoldReason = #Documents.HoldReason
3363    from #Documents
3364   where MigrationDocument.ID = #Documents.ID
3365    
3366  exec logEnd @logid
3367  
3368end
3369
3370GO
3371IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[processTasks]') AND type in (N'P', N'PC'))
3372  Drop Procedure [dbo].[processTasks]
3373GO
3374Create Procedure [dbo].[processTasks]
3375  @fileid varchar(100) = null,
3376  @drawer varchar(4) = null,
3377  @system varchar(255) = null,
3378  @debug int = 0
3379as
3380begin
3381  if @debug>0
3382	raiserror('Starting procedure processTasks. DEBUG level specified as %d',0,1,@debug);
3383	
3384  if @debug=0
3385	set nocount on
3386	
3387  declare @rowCount int;
3388  declare @fileno varchar(50)
3389  
3390  if @fileid is not null	-- These values will retain their current values or remain null if @fileid is null
3391	select @system = System, @drawer = SrcDrawer, @fileno = SrcFileNumber from MigrationFile where ID = @fileid
3392
3393  declare @logid int, @ProcName varchar(50)
3394  set @ProcName = Object_Name(@@PROCID)
3395  exec logStart @procname, @system, @drawer, @param1 = @fileno, @param2 = @fileid, @ID = @logid output
3396  
3397  if (@debug >= 1)
3398	Raiserror ('  Processing Tasks for System: %s, Drawer: %s, File: %s',10,1,@system,@drawer,@fileno) with nowait
3399  
3400  if @fileid is not null	-- Only update the status to 2 if there was a fileid specified. This proc should run less than a few minutes, so setting the files to 2 may not be necessary.
3401	update MigrationFile set status = 2 where ID = @fileid
3402  
3403  if @debug >= 2
3404	raiserror('DEBUG: Variable values prior to insert into #Tasks: @fileid=%s; @drawer=%s; @system=%s',0,1,@fileid,@drawer,@system);
3405  
3406  select * 
3407  into #Tasks 
3408  from MigrationTask 
3409  where FileID 		= isnull(@fileid,FileID)
3410	and SrcDrawer 	= isnull(@drawer,SrcDrawer)
3411	and System		= isnull(@system,System);
3412  set @rowCount=@@ROWCOUNT;
3413  if (@debug >= 1)
3414	raiserror('Rows inserted to temp #Tasks: %d',0,1,@rowCount)
3415
3416  update #Tasks
3417     set #Tasks.flowid = MapTask.DestFlowProgName,
3418         #Tasks.stepid = MapTask.DestStepProgName
3419    from MapTask
3420  where MapTask.System = #Tasks.System
3421    and MapTask.SrcFlowID = #Tasks.SrcFlowID
3422    and MapTask.SrcStepid = #Tasks.SrcStepID
3423
3424    
3425  if (@debug >= 2)
3426    Raiserror ('  Task Assignee Mappings for System: %s, Drawer: %s, File: %s',10,1,@system,@drawer,@fileno) with nowait
3427  update #Tasks
3428     set #Tasks.AssignedToUserID = MapUser.DestAccountID
3429    from MapUser
3430   where MapUser.System = #Tasks.System
3431     and #Tasks.SrcAssignedToUserID = MapUser.SrcUserID          
3432  
3433  update #Tasks
3434     set #Tasks.AssignedToUserID = ''
3435    from MapUser
3436   where MapUser.System = #Tasks.System
3437     and #Tasks.SrcAssignedToUserID is null
3438    
3439    -- Set any null assigned to userid values or mapped {$Unassigned} values to be unassigned (empty string)
3440  update #Tasks set
3441    AssignedToUserID=''
3442  from #Tasks
3443  where #Tasks.SrcAssignedToUserID is null 
3444      or #Tasks.AssignedToUserID='{$Unassigned}'
3445  
3446  if (@debug >= 2)	-- Show #Tasks prior to updating MigrationTask
3447	select * from #Tasks;
3448  
3449  update MigrationTask
3450     set MigrationTask.AssignedToUserID = #Tasks.AssignedToUserID,
3451         MigrationTask.FlowID = #Tasks.FlowID,
3452         MigrationTask.StepID = #Tasks.StepID
3453    from #Tasks
3454   where MigrationTask.ID = #Tasks.ID     
3455   
3456   -- Reset any mappings that were on hold for unmapped tasks before (they might be fixed)
3457   update MigrationDocument
3458      set status = -2
3459     where ID in (select DocumentID from #Tasks)
3460       and HoldReason in ('Unmapped Task', 'Unmapped Task Assignee')
3461
3462   -- Place Unmapped task documents on Hold.
3463   update MigrationDocument
3464     set status = -5, HoldReason = 'Unmapped Task'
3465   where ID in (select DocumentID from #Tasks where FlowID is null)
3466     and status = -2
3467
3468  update MigrationDocument
3469     set status = -5, HoldReason = 'Unmapped Task Assignee'
3470   where ID in (select DocumentID from #Tasks where AssignedToUserID is null)
3471     and status = -2
3472  
3473  exec logEnd @logid 
3474end
3475GO
3476IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[processDocuments]') AND type in (N'P', N'PC'))
3477  Drop Procedure [dbo].[processDocuments]
3478GO
3479Create Procedure dbo.processDocuments
3480  @fileid varchar(100),
3481  @debug bit = 0
3482as
3483begin
3484  set nocount on
3485  Set Transaction Isolation Level Read Uncommitted
3486  
3487  declare @system varchar(40), @drawer varchar(4), @fileno varchar(50)
3488  select @system = System, @drawer = SrcDrawer, @fileno = SrcFileNumber from MigrationFile where ID = @fileid
3489  
3490  declare @logid int, @ProcName varchar(50)
3491  set @ProcName = Object_Name(@@PROCID)  
3492  exec logStart @procname, @system, @drawer, @param1 = @fileno, @param2 = @fileid, @ID = @logid output
3493  
3494  if (@debug >= 1)
3495	Raiserror ('  Setting File and Documents to Migrate -> System: %s, Drawer: %s, File: %s',10,1,@system,@drawer,@fileno) with nowait
3496  
3497  update MigrationFile set status = 2 where ID = @fileid  
3498     
3499  -- Make the document update
3500  update MigrationDocument
3501     set status = 0
3502    where FileID = @fileid
3503      and status = -2    
3504   
3505  update MigrationFile set status = 1 where ID = @fileid
3506  
3507  exec logEnd @logid 
3508end
3509GO
3510IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[process]') AND type in (N'P', N'PC'))
3511  Drop Procedure [dbo].[process]
3512GO
3513create procedure [dbo].[process]
3514  @system varchar(50) = null,
3515  @drawer varchar(100) = null,
3516  @filenumber varchar(255) = null,
3517  @stopAtPriority int = 10, -- System will process until it reaches this Priority (default: 10 - All of them)
3518  @maxFiles int = 1000000000,  -- Maximum number of files to process (1,000,000,000 = Unlimited)
3519  @pause numeric(2,1) = null, -- Time to wait between files in seconds
3520  @preferNearline bit = 0, -- preferNearline: If set to 1, file paths will prefer the nearline
3521  @processAttributesOverride bit = null,	-- If this is null, the key\value from ConfigMigration will be used to determine the @processAttributes param value when calling exec process;
3522  @debug bit = 1                -- Kept at 1 to simulate prior behaviour after minor @debug changes in process* procs. Specify @debug=0 for mostly silent operation.
3523as
3524begin
3525  set nocount on
3526  
3527  declare @logid int, @ProcName varchar(50)
3528  set @ProcName = Object_Name(@@PROCID)
3529  
3530  raiserror ('Processing Files for Migration...',10,1,@system) with nowait  
3531  
3532  -- Fix any Files that were left in Pending when you stopped the process before
3533  update MigrationFile set status = 0 where status = 2
3534  
3535  declare @fileCount int, @delay varchar(30)
3536  set @fileCount = 0
3537  if (@pause is not null)
3538    set @delay = '0:00:0' + cast(@pause as varchar)
3539  declare @fileid varchar(100)
3540  
3541  declare @basesql nvarchar(1000), @sql nvarchar(1000)
3542  set @basesql = 'select top 1 @id = ID from MigrationFile where '
3543  if (@system is not null)
3544    set @basesql = @basesql + ' System = ''' + @system + ''' and '
3545  if (@drawer is not null)
3546    set @basesql = @basesql + ' SrcDrawer = ''' + @drawer + ''' and '
3547  if (@filenumber is not null)
3548	set @basesql = @basesql + ' SrcFileNumber = ''' + @filenumber + ''' and '
3549	
3550  declare @processAttributes bit = 1;
3551  if @processAttributesOverride is null  
3552		select @processAttributes = case ConfigValue when 'TRUE' then 1 when 'FALSE' then 0 else -9 end 
3553		from ConfigMigration 
3554		where ConfigKey='process_ExecuteProcessAttributesProc';
3555  else set @processAttributes = @processAttributesOverride;
3556
3557  declare @priority int
3558  set @priority = null
3559  select top 1 @priority = priority from MigrationFile where priority < @stopAtPriority and status = 0 order by priority
3560  set @sql = @basesql + ' status = 0 and priority = ' + cast(@priority as nvarchar)
3561  while @priority is not null and @filecount < @maxFiles
3562  begin
3563	  set @fileid = null
3564	  EXECUTE sp_executesql @sql, N'@id varchar(100) OUTPUT', @id = @fileid OUTPUT
3565	  while @fileid is not null and @fileCount < @maxFiles
3566	  begin
3567      exec logStart @ProcName, @system, @drawer, @param1 = @fileid, @ID = @logid output
3568      set @fileCount = @fileCount + 1
3569      
3570      exec processPages @fileid = @fileid, @processAttributes = @processAttributes, @debug = @debug
3571      exec processTasks @fileid, @debug
3572      exec processDocuments @fileid, @debug
3573      
3574      set @fileid = null
3575      EXECUTE sp_executesql @sql, N'@id varchar(100) OUTPUT', @id = @fileid OUTPUT
3576      if (@delay is not null)
3577        waitfor delay @delay
3578      exec logEnd @logid
3579	  end  
3580    set @priority = null
3581    select top 1 @priority = priority from MigrationFile where priority < @stopAtPriority and status = 0 order by priority
3582	  set @sql = @basesql + ' status = 0 and priority = ' + cast(@priority as nvarchar)
3583    
3584  end
3585end
3586GO
3587IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[raiseMessage]') AND type in (N'P', N'PC'))
3588  Drop Procedure [dbo].[raiseMessage]
3589GO
3590create procedure raiseMessage
3591	@message varchar(4000),
3592	@severity int = 10,
3593	@state int = 1,
3594	@arg1 varchar(1000) = '',
3595	@arg2 varchar(1000) = '',
3596	@rowCount int = null,
3597	@nestedDepth int = 0,
3598	@arg3 varchar(1000) = '',
3599	@arg4 varchar(1000) = '',
3600	@arg5 varchar(1000) = '',
3601	@suppressDateTimeStamp int = 0
3602as
3603	/*
3604		Description: raiseMessage is intended to facilitate succinct on screen status logging for long running and complex 
3605			scripts in SSMS by generating date\time stamps for each entry, rows affected integrated into the message where 
3606			applicable, nesting the messages to differentiate sub-processes, etc. raiseMessage leverages raiserror and thus 
3607			all of the parameters for raiserror is supported - up to five arguments (currently). 
3608		
3609		Parameters
3610		@message is the main message that you want to display
3611			Remark (snippet from MSDN): " If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated. Note that substitution parameters consume more characters than the output shows because of internal storage behavior. For example, the substitution parameter of %d with an assigned value of 2 actually produces one character in the message string but also internally takes up three additional characters of storage. This storage requirement decreases the number of available characters for message output."
3612		@severity - defaulted to 10 to default as a message instead of error. 
3613		@state defaulted 1. See MSDN documentation on RAISERROR for usage.
3614		@rowCount is used to pass in the @@ROWCOUNT auto variable
3615		@nestedDepth auto-indents the message between the time stamp and the message text
3616		@arg1-@arg5 are optional arguments that aligns with the [ ,argument [ ,...n ] ] RAISERROR syntax.
3617		
3618		Example usage:
3619
3620			set nocount on
3621			exec raiseMessage @message='Starting: raiseMessage Demo.sql', @nestedDepth=0
3622			exec raiseMessage @message='Begining Process', @nestedDepth=1
3623			exec raiseMessage @message='Selecting rows from FooBar', @nestedDepth=2
3624			select * from FooBar
3625			exec raiseMessage @message='Finished selecting rows from FooBar', @rowCount=@@ROWCOUNT, @nestedDepth=2
3626			exec raiseMessage @message='Done', @nestedDepth=0
3627			exec raiseMessage '%s the %s %s %s, %s.', 10, 1, 'Open', 'pod', 99999, 0, 'bay', 'door', 'HAL'
3628			exec raiseMessage @message='I''m sorry, Dave, but I can''t do that.', 
3629								@severity=15,
3630								@state=1,
3631								@arg1='Dave',
3632								@nestedDepth=1
3633			
3634			/* -- Example Message pane output
3635		2016-01-11 13:11:27: Starting: raiseMessage Demo.sql
3636		2016-01-11 13:11:27:     Begining Process
3637		2016-01-11 13:11:27:         Selecting rows from FooBar
3638		2016-01-11 13:11:27:         Finished selecting rows from FooBar - Rows Affected: 1234
3639		2016-01-11 13:11:27: Done
3640		2016-01-11 13:11:27: Open the pod bay door, HAL. - Rows Affected: 99999
3641		Msg 50000, Level 15, State 1, Procedure raiseMessage, Line 53
3642		2016-01-11 13:11:27:     I'm sorry, Dave, but I can't do that.
3643			*/		
3644	*/
3645begin
3646	declare @formattedMessage varchar(4000)
3647	set @formattedMessage = 
3648		case @suppressDateTimeStamp
3649			when 0
3650				then convert(varchar,getdate(),120) + ': '
3651			else ''
3652		end
3653		+ replicate(' ',@nestedDepth*4) 
3654		+ @message 
3655		+ case 
3656			when @rowcount is not null 
3657				then ' - Rows Affected: ' + CAST(@rowcount as varchar(50)) 
3658			else '' 
3659		end;
3660		
3661	raiserror(@formattedMessage,@severity,@state,@arg1,@arg2,@arg3,@arg4,@arg5) with nowait;
3662	if len(@formattedMessage)>2000 raiserror('The prior raiseMessage call may have truncated the message due to constraints of RAISERROR.',10,1)
3663end
3664go
3665
3666
3667GO
3668IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportDoNotMigrateTasks]') AND type in (N'P', N'PC'))
3669  Drop Procedure [dbo].[reportDoNotMigrateTasks]
3670GO
3671create procedure [dbo].[reportDoNotMigrateTasks]  
3672	/* Procedure: reportDoNotMigrateTasks
3673		Returns pages with tasks where the tasks are mapped to "Do Not Migrate" to prevent them from migrating.
3674	*/
3675  @system varchar(20) = null,
3676  @drawer varchar(4) = null
3677as
3678begin
3679	select MigrationDocument.System,
3680		MigrationDocument.id as DocumentID,
3681		ConfigStatus.Description as DocumentMigrationStatus,
3682		MigrationPage.SrcDrawer,
3683		MigrationPage.SrcFileNumber,
3684		MigrationPage.SrcFolderPath,
3685		MigrationPage.SrcDocType,
3686		MigrationPage.SrcTempDin,
3687		MigrationPage.IRDrawer,
3688		MigrationPage.IRFileType,
3689		MigrationPage.IRFileNumber,
3690		MigrationPage.IRFileName,
3691		MigrationPage.IRFolderType,
3692		MigrationPage.IRDocType,
3693		MigrationPage.PageNumber,
3694		MigrationPage.IRPageDescription,
3695		MigrationTask.SrcTaskID,
3696		MigrationTask.SrcFlowID,
3697		MigrationTask.SrcStepID,
3698		SnapshotWorkflows3x.FlowName,
3699		SnapshotWorkflows3x.StepDesc,
3700		MigrationTask.SrcAssignedToUserID
3701	from MigrationDocument
3702	join MigrationPage on MigrationDocument.id=MigrationPage.DocumentID
3703	join MigrationTask on MigrationPage.ID=MigrationTask.PageID
3704	join ConfigStatus on MigrationDocument.status=ConfigStatus.Status
3705	join SnapshotWorkflows3x on MigrationTask.SrcFlowID=SnapshotWorkflows3x.FlowID
3706		and MigrationTask.SrcStepID=SnapshotWorkflows3x.StepID
3707	where MigrationTask.FlowID='Do Not Migrate'
3708		and MigrationDocument.system=isnull(@system,MigrationDocument.system)
3709		and MigrationDocument.Srcdrawer=isnull(@drawer,MigrationDocument.Srcdrawer)      
3710end
3711
3712GO
3713
3714GO
3715IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportErrors]') AND type in (N'P', N'PC'))
3716  Drop Procedure [dbo].[reportErrors]
3717GO
3718create procedure dbo.reportErrors
3719  @errortype varchar(1000) = null,
3720  @priority int = null
3721as
3722begin
3723
3724  if (@errortype is null)
3725    begin      
3726      SELECT left(LogProgress.errormessage,50) as errormessage, Documents = COUNT(*) , Pages = SUM(MigrationDocument.pagecount) 
3727        into #ErrorDocs
3728        FROM LogProgress
3729               INNER JOIN MigrationDocument ON MigrationDocument.ID = LogProgress.DocumentID
3730        WHERE MigrationDocument.Status = -1 AND LogProgress.success = 0
3731          and MigrationDocument.Priority<=isnull(@Priority,MigrationDocument.Priority)
3732        GROUP BY left(LogProgress.errormessage,50)
3733        ORDER BY Documents DESC
3734
3735        select left(LogProgress.errormessage,50) as errormessage, Tasks = COUNT(*)  
3736          into #ErrorTasks
3737          from LogProgress, MigrationTask, MigrationDocument
3738        where MigrationDocument.ID = LogProgress.DocumentID 
3739          and LogProgress.DocumentID = MigrationTask.DocumentID           
3740          and LogProgress.errormessage is not null
3741          and MigrationDocument.Priority<=isnull(@Priority,MigrationDocument.Priority)
3742        group by left(LogProgress.errormessage,50)
3743        
3744      select #ErrorDocs.*, Tasks = ISNULL(Tasks,0)
3745        from #ErrorDocs left join #ErrorTasks on #ErrorDocs.errormessage = #ErrorTasks.errormessage
3746	   order by Documents desc
3747    end
3748  else
3749    begin
3750      SELECT errormessage, Documents = COUNT(*) , Pages = SUM(MigrationDocument.pagecount) 
3751        into #ErrorDocs2
3752        FROM LogProgress
3753               INNER JOIN MigrationDocument ON MigrationDocument.ID = LogProgress.DocumentID
3754        WHERE MigrationDocument.Status = -1 AND LogProgress.success = 0 and errormessage like @errortype + '%'
3755          and MigrationDocument.Priority<=isnull(@Priority,MigrationDocument.Priority)
3756        GROUP BY LogProgress.errormessage
3757        ORDER BY Documents DESC
3758
3759      select errormessage, Tasks = COUNT(*)  
3760        into #ErrorTasks2
3761        from LogProgress, MigrationTask
3762      where LogProgress.DocumentID = MigrationTask.DocumentID           
3763        and LogProgress.errormessage like @errortype + '%'
3764      group by LogProgress.errormessage      
3765      
3766      select #ErrorDocs2.*, Tasks = ISNULL(Tasks,0)
3767        from #ErrorDocs2 left join #ErrorTasks2 on #ErrorDocs2.errormessage = #ErrorTasks2.errormessage
3768       order by Documents desc
3769    end
3770    
3771  select MigrationDocument.FileID, LogProgress.DocumentID, System, SrcDrawer, SrcFileNumber, Errormessage
3772  from MigrationDocument, LogProgress
3773  where MigrationDocument.ID = LogProgress.Documentid
3774    and LogProgress.errormessage is not null
3775    and LogProgress.errormessage like isNull(@errortype,'%') + '%'
3776    and MigrationDocument.Status = -1
3777    and MigrationDocument.Priority<=isnull(@Priority,MigrationDocument.Priority)
3778  
3779end
3780GO
3781IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportHolds]') AND type in (N'P', N'PC'))
3782  Drop Procedure [dbo].[reportHolds]
3783GO
3784create procedure reportHolds
3785as
3786begin
3787  select System, HoldReason, Count(*) as DocCount
3788    from MigrationDocument
3789   where status = -5
3790   group by System, HoldReason
3791   order by DocCount desc
3792end
3793GO
3794IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportPerformance]') AND type in (N'P', N'PC'))
3795  Drop Procedure [dbo].[reportPerformance]
3796GO
3797create procedure [dbo].[reportPerformance]
3798  @Migration bit = 0,
3799  @Process bit = 0,
3800  @History bit = 0
3801as
3802begin
3803   if object_id('tempdb..#reportM') is not null
3804      drop table #reportM
3805  if object_id('tempdb..#reportP') is not null
3806      drop table #reportP      
3807
3808  if (@Migration = 1)
3809  begin
3810    select 
3811      [Machine Name] = case when (grouping(machinename)=1) then 'All Machines' else machinename end, 
3812      MinTimeStamp = MIN(timestamp), 
3813      MaxTimeStamp = MAX(timestamp),
3814      Docs = COUNT(MigrationDocument.id),
3815      DocsPerHour = case 
3816                      when max(timestamp) > min(timestamp) 
3817                        then round(COUNT(distinct MigrationDocument.id)/cast(MAX(timestamp)-MIN(timestamp) as float)/24.0,0)
3818                      else 0.0 
3819                    end,
3820       Pages = Sum(PageCount),
3821       PagesPerDoc = case 
3822                       when count(MigrationDocument.ID) > 0 
3823                         then round(cast(sum(PageCount) as float)/cast(count(MigrationDocument.id) as float),1)
3824               else 0.0
3825               end,
3826       PagesPerHour = case 
3827                      when max(timestamp) > min(timestamp) 
3828                        then round(cast(sum(PageCount) as float)/(cast(MAX(timestamp)-MIN(timestamp) as float)*24),0)
3829                      else 0.0
3830                     end                      
3831       into #reportM
3832      from MigrationDocument with(nolock)
3833    where status in (1,-1) and timestamp>DATEADD(MI,-5, GETDATE())
3834    group by machinename with rollup
3835
3836    declare @DocsLeft int, @PagesLeft int
3837    select @DocsLeft = count(ID), @PagesLeft = sum(PageCount) from MigrationDocument where status = 0 
3838    
3839    --select count(distinct [Machine Name]) from #reportM where [Machine Name]<>'All Machines'
3840    update #reportM set [Machine Name]=[Machine Name]+' ('+cast((select count(distinct [Machine Name]) from #reportM where [Machine Name]<>'All Machines') as varchar(50))+' Machines Reporting)' where [Machine Name]='All Machines';
3841
3842     select *,
3843       DocHoursLeft = case when DocsPerHour = 0 then 0 else round(cast(@DocsLeft as float)/ cast(DocsPerHour as float),2) end,
3844       PageHoursLeft = case when PagesPerHour = 0 then 0 else round(cast(@PagesLeft as float)/ cast(PagesPerHour as float),2) end
3845       from #reportM 
3846       order by
3847        case when [Machine Name] like 'All Machines (% Machines Reporting)' then 0 else 1 end asc, --Force the "All Machines" total to be the first record in the results
3848        [Machine Name] asc  -- Secondary sort for the actual machine names to put them in order
3849  end
3850  
3851  if (@Process = 1)
3852  begin
3853      select 
3854         StartTime = min(StartTime),
3855         EndTime = max(EndTime),
3856         Files = count(MigrationFile.ID),
3857         Documents = sum(DocumentCount),
3858         Pages = sum(PageCount),
3859         Tasks = sum(TaskCount)
3860       into #reportP
3861       from LogProcess, MigrationFile
3862      where LogProcess.OtherParam1 = MigrationFile.ID and name = 'process' 
3863       and EndTime > DATEADD(MI,-5,getDate())
3864       and StartTime > DATEADD(MI,-5,getDate())
3865       and MigrationFile.status = 1
3866
3867     select Process = '', StartTime, EndTime,
3868         Files = replace(convert(varchar,cast(Files as money),1),'.00',''),
3869         FilesPerHour = replace(convert(varchar,cast(round(cast(Files as float) / cast(EndTime - StartTime as float) / 24.0,0) as money),1),'.00',''),
3870         Documents = replace(convert(varchar,cast(Documents as money),1),'.00',''),
3871         DocsPerHour = replace(convert(varchar,cast(round(cast(Documents as float) / cast(EndTime - StartTime as float) / 24.0,0) as money),1),'.00',''),
3872         Pages = replace(convert(varchar,cast(Pages as money),1),'.00',''), 
3873         PagesPerHour = replace(convert(varchar,cast(round(cast(Pages as float) / cast(EndTime - StartTime as float) / 24.0,0) as money),1),'.00','')
3874      from #reportP
3875  end
3876
3877  if (@History = 1)
3878  begin
3879
3880      select 
3881        Process = '',
3882        [Hour] = dateadd(mi, datepart(minute, EndTime) / 60 * 60, dateadd(hh, datediff(hh, 0, EndTime), 0)),
3883        Files = replace(convert(varchar,cast(Count(*) as money),1),'.00',''),
3884        Documents = replace(convert(varchar,cast(Sum(DocumentCount) as money),1),'.00',''),
3885        Pages = replace(convert(varchar,cast(Sum(PageCount) as money),1),'.00','') 
3886       into #Processed
3887       from LogProcess, MigrationFile
3888      where LogProcess.OtherParam1 = MigrationFile.ID and name = 'process'  
3889       and MigrationFile.status = 1
3890      group by dateadd(mi, datepart(minute, EndTime) / 60 * 60, dateadd(hh, datediff(hh, 0, EndTime), 0))    
3891
3892    select Migration = '', 
3893           [Hour] = dateadd(mi, datepart(minute, cq.timestamp) / 60 * 60, dateadd(hh, datediff(hh, 0, cq.timestamp), 0)),
3894           Documents = replace(convert(varchar,cast(Count(*) as money),1),'.00',''),
3895           Pages = replace(convert(varchar,cast(Sum(PageCount) as money),1),'.00','')
3896      into #Migrated     
3897    from MigrationDocument cq
3898    where status = 1
3899    group by dateadd(mi, datepart(minute, cq.timestamp) / 60 * 60, dateadd(hh, datediff(hh, 0, cq.timestamp), 0))
3900    
3901    select [Hour] = isNull(#Processed.[Hour],#Migrated.[Hour]),
3902           ProcessedFiles = isNull(#Processed.Files,'0'),
3903           ProcessedDocs = isNull(#Processed.Documents,'0'),
3904           MigratedDocs = isNull(#Migrated.Documents,'0'),
3905           ProcessedPages = isNull(#Processed.Pages,'0'),
3906           MigratedPages = isNull(#Migrated.Pages,'0')
3907      from #Processed full join #Migrated
3908        on #Processed.[Hour] = #Migrated.[Hour]
3909      order by [Hour] desc
3910      
3911  end
3912end
3913
3914
3915GO
3916IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportProcess]') AND type in (N'P', N'PC'))
3917  Drop Procedure [dbo].[reportProcess]
3918GO
3919create Procedure dbo.reportProcess
3920  @Name varchar(100) = null,
3921  @phasesOnly bit = 0,
3922  @showHistory bit = 0
3923as
3924begin
3925  select top 20 ID, Name, 
3926    [Duration (sec)] = cast(round(Duration / 1000.0,1) as numeric(10,1)), 
3927    [Duration (min)] = cast(round(Duration / 1000.0 / 60.0,1) as numeric(10,1)),
3928    StartTime, EndTime,
3929    System, Drawer, OtherParam1, OtherParam2, OtherParam3
3930   from LogProcess 
3931   where Name = isNull(@Name, Name)
3932     and isPhase = case when @phasesOnly = 1 then 1 else isPhase end   
3933   order by ID desc
3934   
3935  select Name, max(StartTime) as LastExecuted,
3936    cast(round(sum(Duration) / 1000.0 / 60.0,1) as numeric(10,1)) as [Duration (min)] 
3937    from LogProcess
3938   where Name = isNull(@Name, Name)    
3939     and isPhase = case when @phasesOnly = 1 then 1 else isPhase end   
3940    group by Name
3941  order by LastExecuted desc
3942
3943  if (@showHistory = 1)
3944  begin
3945    select *, 
3946      [Duration (sec)] = cast(round(Duration / 1000.0,1) as numeric(10,1)), 
3947      [Duration (min)] = cast(round(Duration / 1000.0 / 60.0,1) as numeric(10,1))
3948     from LogProcessHistory
3949      where Name = isNull(@Name, Name)  
3950      order by ID desc
3951  end
3952 end
3953GO
3954IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportStatus]') AND type in (N'P', N'PC'))
3955  Drop Procedure [dbo].[reportStatus]
3956GO
3957create procedure [dbo].[reportStatus]  
3958  @system varchar(50) = null,
3959  @drawer varchar(100) = null,
3960  @includePriority bit = 1
3961as
3962begin
3963
3964  declare @totalDocs float, @totalFiles float
3965  
3966  select @totalDocs = COUNT(*) from MigrationDocument
3967    where SrcDrawer = ISNULL(@drawer,SrcDrawer)
3968      and System = isnull(@system,System)    
3969      
3970  select @totalFiles = COUNT(*) from MigrationFile
3971   where SrcDrawer = ISNULL(@drawer,SrcDrawer)
3972     and System = isnull(@system,System)        
3973     
3974   select
3975     Priority,     
3976     Status = ConfigStatus.Description + ' (' + cast(MigrationDocument.Status as varchar) + ')',
3977     Documents = COUNT(*)
3978     into #reportMigration
3979     from MigrationDocument, ConfigStatus
3980    where MigrationDocument.status = ConfigStatus.status
3981      and SrcDrawer = ISNULL(@drawer,SrcDrawer)
3982      and System = isNull(@system,System)    
3983     group by Priority, MigrationDocument.Status, ConfigStatus.Description      
3984    
3985  select
3986   Priority,   
3987   Status = ConfigStatus.Description + ' (' + cast(MigrationFile.Status as varchar) + ')',
3988   Files = COUNT(*)
3989   into #reportProcess
3990   from MigrationFile, ConfigStatus
3991  where MigrationFile.status = ConfigStatus.status
3992    and SrcDrawer = ISNULL(@drawer,SrcDrawer)
3993    and System = isNull(@system,System)    
3994   group by Priority, MigrationFile.Status, ConfigStatus.Description 
3995   
3996   select 
3997     Priority = isNull(#reportMigration.Priority,#reportProcess.priority),
3998     Status = isNull(#reportMigration.Status,#reportProcess.Status),
3999	 Files = isNull(#reportProcess.Files,0),
4000	 Documents = isNull(#reportMigration.Documents,0)
4001	 into #report
4002     from #reportMigration	 
4003	full join #reportProcess
4004	   on #reportMigration.Priority = #reportProcess.Priority
4005      and #reportMigration.Status = #reportProcess.Status
4006
4007  if(@includePriority=1)
4008  select Priority = case when (Grouping(Priority) = 1) then 'All' else cast(Priority as varchar(4)) end,
4009       Status = case               
4010	              when (GROUPING(Status) = 1) then '-------------------' 
4011				  else Status 
4012				end,
4013       [Files] = sum(Files),       
4014	   Documents = sum(Documents),
4015	   [PercentFiles] = cast(sum(round((CAST(Files as float) / @totalFiles) * 100.0,2)) as varchar) + '%',
4016       [PercentDocs] = cast(sum(round((CAST(Documents as float) / @totalDocs) * 100.0,4)) as varchar) + '%'
4017   from #report
4018  group by Priority, Status with rollup
4019  else
4020    select Status = case               
4021                    when (GROUPING(Status) = 1) then 'All' 
4022                    else cast(Status as varchar(50)) 
4023                  end,
4024         [Files] = sum(Files),       
4025       Documents = sum(Documents),
4026       [PercentFiles] = cast(sum(round((CAST(Files as float) / @totalFiles) * 100.0,2)) as varchar) + '%',
4027         [PercentDocs] = cast(sum(round((CAST(Documents as float) / @totalDocs) * 100.0,4)) as varchar) + '%'
4028     from #report
4029    group by Status with rollup
4030  
4031	  
4032  
4033     
4034end
4035
4036GO
4037
4038GO
4039IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportSnapshotStats]') AND type in (N'P', N'PC'))
4040  Drop Procedure [dbo].[reportSnapshotStats]
4041GO
4042create procedure dbo.reportSnapshotStats
4043  @system varchar(50),
4044  @migrationdate datetime = null
4045as
4046begin
4047  set nocount on
4048  Set Transaction Isolation Level Read Uncommitted
4049     
4050  declare @logid int, @ProcName varchar(50)
4051  set @ProcName = Object_Name(@@PROCID)  
4052  exec logStart @procname, @system, @ID = @logid output
4053
4054  if (@migrationdate is null)
4055    set @migrationdate = cast(Convert(varchar(10),getDate(),110) as datetime)    
4056  
4057  exec prepareIndexes @table = 'SnapshotDocument3x', @index = 'ndx_Document'
4058  
4059  declare @generateReconcileStats bit, @generateUniverseStats bit
4060  declare @generatePostStats bit, @generateMigrateStats bit
4061  declare @generateVisibleStats bit, @generateHiddenStats bit
4062
4063  set @generateReconcileStats = 1
4064  set @generateUniverseStats = 1
4065  set @generateVisibleStats = 1
4066  set @generateHiddenStats = 1
4067  set @generateMigrateStats = 1
4068  set @generatePostStats = 1
4069
4070  
4071    RAISERROR ('Computing Stats', 10, 1) WITH NOWAIT 
4072    
4073     
4074    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SnapshotDocument3x]') AND name = N'ndx_Document')
4075    begin
4076      RAISERROR ('   Adding Index', 10, 1) WITH NOWAIT
4077
4078      CREATE NONCLUSTERED INDEX ndx_Document ON dbo.SnapshotDocument3x
4079      (
4080        drawer ASC,
4081        foldernumber ASC,
4082        docid ASC
4083      )
4084    end
4085    
4086    RAISERROR ('  Documents', 10, 1) WITH NOWAIT 
4087    if Object_id('tempdb..#documents') is not null  
4088      drop table #documents
4089        
4090    select drawer, foldernumber, docid, 
4091           Pages = count(*),
4092           PagesV = sum(case when visible = 1 then 1 else 0 end),
4093           PagesH = sum(case when visible = 0 then 1 else 0 end),
4094           PagesM = sum(case when visible = 1 and DateCaptured <= convert(varchar(8),@migrationdate,112) then 1 else 0 end),
4095           PagesPost = sum(case when visible = 1 and DateCaptured > convert(varchar(8),@migrationdate,112) then 1 else 0 end),
4096           Annotations = sum(case when len(amedia) > 0 then 1 else 0 end),
4097           AnnotationsV = sum(case when len(amedia) > 0 and amedia <> 'N' and visible = 1 then 1 else 0 end),
4098           AnnotationsH = sum(case when len(amedia) > 0 and (amedia = 'N' or visible = 0) then 1 else 0 end),
4099           AnnotationsM = sum(case when len(amedia) > 0 and amedia <> 'N' and visible = 1 and DateCaptured <= convert(varchar(8),@migrationdate,112) then 1 else 0 end),
4100           AnnotationsPost = sum(case when len(amedia) > 0 and amedia <> 'N' and visible = 1 and DateCaptured > convert(varchar(8),@migrationdate,112) then 1 else 0 end)           
4101      into #documents 
4102      from SnapshotDocument3x     
4103     group by drawer, foldernumber, docid   
4104    
4105    RAISERROR ('  Files', 10, 1) WITH NOWAIT 
4106    if Object_id('tempdb..#files') is not null  
4107      drop table #files
4108     
4109    select drawer, foldernumber, 
4110           Documents = count(*),
4111           DocumentsV = sum(case when PagesV > 0 then 1 else 0 end),
4112           DocumentsH = sum(case when PagesV = 0 then 1 else 0 end),
4113           DocumentsM = sum(case when PagesM > 0 then 1 else 0 end),
4114           DocumentsPost = sum(case when PagesM = 0 and PagesPost > 0 then 1 else 0 end),
4115           Pages = sum(Pages),
4116           PagesV = sum(PagesV),
4117           PagesH = sum(PagesH),
4118           PagesM = sum(PagesM),
4119           PagesPost = sum(PagesPost),
4120           Annotations = sum(Annotations),
4121           AnnotationsV = sum(AnnotationsV),
4122           AnnotationsH = sum(AnnotationsH),
4123           AnnotationsM = sum(AnnotationsM),
4124           AnnotationsPost = sum(AnnotationsPost)
4125      into #files
4126      from #documents     
4127     group by drawer, foldernumber
4128    
4129    RAISERROR ('  Drawers', 10, 1) WITH NOWAIT 
4130    if Object_id('tempdb..#drawers') is not null  
4131      drop table #drawers
4132     
4133    select drawer, 
4134           Files = count(*),
4135           FilesV = isNull(sum(case when DocumentsV > 0 then 1 else 0 end),0),
4136           FilesH = isNull(sum(case when DocumentsV = 0 then 1 else 0 end),0),
4137           FilesM = isNull(sum(case when DocumentsM > 0 then 1 else 0 end),0),
4138           FilesPost = isNull(sum(case when DocumentsM = 0 and DocumentsPost > 0 then 1 else 0 end),0),
4139           Documents = isNull(sum(Documents),0),
4140           DocumentsV = isNull(sum(DocumentsV),0),
4141           DocumentsH = isNull(sum(DocumentsH),0),
4142           DocumentsM = isNull(sum(DocumentsM),0),
4143           DocumentsPost = isNull(sum(DocumentsPost),0),
4144           Pages = isNull(sum(Pages),0),
4145           PagesV = isNull(sum(PagesV),0),
4146           PagesH = isNull(sum(PagesH),0),
4147           PagesM = isNull(sum(PagesM),0),
4148           PagesPost = isNull(sum(PagesPost),0),
4149           Annotations = sum(Annotations),
4150           AnnotationsV = sum(AnnotationsV),
4151           AnnotationsH = sum(AnnotationsH),
4152           AnnotationsM = sum(AnnotationsM),
4153           AnnotationsPost = sum(AnnotationsPost)
4154      into #drawers
4155      from #files
4156     group by drawer
4157     
4158     RAISERROR ('  Tasks', 10, 1) WITH NOWAIT
4159     if Object_id('tempdb..#snapshot') is not null  
4160      drop table #tasksnapshot 
4161      
4162     select * into #tasksnapshot from SnapShotTask3x
4163     
4164     RAISERROR ('..Complete', 10, 1) WITH NOWAIT 
4165    
4166  if (@generateReconcileStats = 1)
4167  begin  
4168    RAISERROR ('Generate Reconciliation', 10, 1) WITH NOWAIT 
4169    if Object_id('tempdb..#Reconcile') is not null  
4170      drop table #Reconcile
4171      
4172    select top 1 SnapShot from SnapShotDocument3x
4173    
4174    select orderno = 1,
4175           [Line Item] = cast('Total' as varchar(10)),
4176           Drawers = count(*),
4177           Files = sum(FilesV) + sum(FilesH),
4178           Documents = sum(DocumentsV) + sum(DocumentsH),
4179           Pages = sum(PagesV) + sum(PagesH),
4180           Annotations = sum(AnnotationsV) + sum(AnnotationsH)
4181      into #Reconcile
4182      from #drawers
4183    
4184    insert into #Reconcile
4185    select orderno = 2,
4186           [Line Item] = 'Hidden',
4187           Drawers = sum(case when FilesV = 0 then 1 else 0 end) * -1,
4188           Files = sum(FilesH) * -1,
4189           Documents = sum(DocumentsH) * -1,
4190           Pages = sum(PagesH) * -1,
4191           Annotations = sum(AnnotationsH) * -1
4192       from #drawers
4193    
4194    insert into #Reconcile
4195    select orderno = 3,
4196           [Line Item] = 'Visible',
4197           Drawers = sum(case when FilesV > 0 then 1 else 0 end),
4198           Files = sum(FilesV),
4199           Documents = sum(DocumentsV),
4200           Pages = sum(PagesV),
4201           Annotations = sum(AnnotationsV)
4202       from #drawers
4203     
4204     select [Line Item], Drawers, Files, Documents, Pages, Annotations
4205       from #Reconcile
4206      order by orderno
4207    
4208    select Status, 
4209      Visibility = (case when status in ('D','M') then 'Hidden' else 'Visible' end),
4210      Pages = count(*)
4211      from SnapshotDocument3x
4212     group by Status
4213    
4214    RAISERROR ('..Complete', 10, 1) WITH NOWAIT 
4215  end
4216
4217  if (@generateUniverseStats = 1)
4218  begin
4219
4220    RAISERROR ('Generate Universe', 10, 1) WITH NOWAIT 
4221    if Object_id('tempdb..#tasksU') is not null
4222      drop table #tasksU
4223        
4224    -- Get the number of files in the drawer
4225
4226     
4227    select drawer, COUNT(*) as Tasks
4228     into #tasksU
4229     from #tasksnapshot 
4230     group by drawer 
4231
4232     select [3xUniverse] = ' ',#drawers.Drawer as Name, Description = isNull(Drawer.Description,'Does Not Exist'),
4233      [Files] = isNull(Files,0), [Documents] = isNull(Documents,0), [Pages] = isNull(Pages,0), isNull(Tasks,0) as Tasks,
4234      isNull(Annotations,0) as Annotations
4235     from #drawers 
4236     left join SnapshotDrawer3x as Drawer on Drawer.drawer = #drawers.drawer
4237     left join #tasksU on Drawer.drawer = #tasksU.drawer 
4238     order by Name
4239     RAISERROR ('..Complete', 10, 1) WITH NOWAIT 
4240  end
4241
4242  if (@generateVisibleStats = 1)
4243  begin
4244    RAISERROR ('Generate Visible Stats', 10, 1) WITH NOWAIT
4245    if Object_id('tempdb..#tasksV') is not null
4246      drop table #tasksV
4247        
4248    -- Get the number of files in the drawer
4249
4250     
4251    select drawer, COUNT(*) as Tasks
4252     into #tasksV
4253     from #tasksnapshot 
4254     group by drawer 
4255
4256     select [3xVisible] = ' ',#drawers.Drawer as Name, Description = isNull(Drawer.Description,'Does Not Exist'),
4257      [Files] = isNull(FilesV,0), [Documents] = isNull(DocumentsV,0), [Pages] = isNull(PagesV,0), isNull(Tasks,0) as Tasks,
4258      Annotations = isNull(AnnotationsV,0)
4259     from #drawers 
4260     left join SnapshotDrawer3x as Drawer on Drawer.drawer = #drawers.drawer
4261     left join #tasksV on Drawer.drawer = #tasksV.drawer
4262     where isNull(FilesV,0) > 0 
4263     order by Name
4264     RAISERROR ('..Complete', 10, 1) WITH NOWAIT
4265  end
4266
4267  if (@generateHiddenStats = 1)
4268  begin
4269    RAISERROR ('Generate Hidden Stats', 10, 1) WITH NOWAIT
4270     select [3xHidden] = ' ',#drawers.Drawer as Name, Description = isNull(Drawer.Description,'Does Not Exist'),
4271      [Files] = isNull(FilesH,0), [Documents] = isNull(DocumentsH,0), [Pages] = isNull(PagesH,0), 0 as Tasks,
4272      Annotations = isNull(AnnotationsH,0)
4273     from #drawers 
4274     left join SnapshotDrawer3x as Drawer on Drawer.drawer = #drawers.drawer   
4275     order by Name
4276   RAISERROR ('..Complete', 10, 1) WITH NOWAIT
4277  end
4278
4279
4280  if (@generateMigrateStats = 1)
4281  begin
4282    RAISERROR ('Generate Migration Stats', 10, 1) WITH NOWAIT
4283    if Object_id('tempdb..#tasks') is not null
4284      drop table #tasks
4285        
4286    -- Get the number of files in the drawer
4287
4288     
4289    select drawer, COUNT(*) as Tasks
4290     into #tasks
4291     from #tasksnapshot where convert(varchar(8),date_initiated,112) <= convert(varchar(8),@migrationdate,112)
4292     group by drawer 
4293
4294     select [3xMigrate] = ' ',#drawers.Drawer as Name, Description = isNull(Drawer.Description,'Does Not Exist'),
4295      [Files] = isNull(FilesM,0), [Documents] = isNull(DocumentsM,0), [Pages] = isNull(PagesM,0), isNull(Tasks,0) as Tasks,
4296      Annotations = isNull(AnnotationsM,0)
4297     from #drawers 
4298     left join SnapshotDrawer3x as Drawer on Drawer.drawer = #drawers.drawer
4299     left join #tasks on Drawer.drawer = #tasks.drawer
4300     where isNull(FilesM,0) > 0
4301     order by Name
4302     RAISERROR ('..Complete', 10, 1) WITH NOWAIT
4303  end
4304
4305  /****************  Check for Post-Migration Activity *********************/
4306
4307  if (@generatePostStats = 1)
4308  begin
4309    RAISERROR ('Generate Post Migration Stats', 10, 1) WITH NOWAIT
4310    if Object_id('tempdb..#posttasks') is not null
4311      drop table #posttasks
4312     
4313    select drawer, COUNT(*) as Tasks
4314     into #posttasks
4315     from #tasksnapshot where convert(varchar(8),date_initiated,112) > convert(varchar(8),@migrationdate,112)
4316     group by drawer
4317
4318     select [3xPost] = ' ', #drawers.Drawer as Name, Description = isNull(Drawer.Description,'Does Not Exist'),
4319       [Files] = isNull(FilesPost,0), [Documents] = isNull(DocumentsPost,0), [Pages] = isNull(PagesPost,0), isNull(Tasks,0) as Tasks,
4320       Annotations = isNull(AnnotationsPost,0)
4321     from #drawers 
4322     inner join SnapshotDrawer3x as Drawer on Drawer.drawer = #drawers.drawer
4323     left join #posttasks on Drawer.drawer = #posttasks.drawer 
4324     order by Name
4325     RAISERROR ('..Complete', 10, 1) WITH NOWAIT
4326  end
4327  exec logEnd @logid
4328end
4329GO
4330IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportLoadStats]') AND type in (N'P', N'PC'))
4331  Drop Procedure [dbo].[reportLoadStats]
4332GO
4333Create Procedure dbo.reportLoadStats  
4334  @system varchar(50) = null  -- System to run stats for (set to NULL for all)
4335as
4336begin  
4337  
4338select Drawer = srcDrawer, 
4339       Files = count(distinct FileID), 
4340       Documents = count(distinct DocumentID), 
4341       Pages = count(*),
4342       Tasks = (select count(*) from MigrationTask where MigrationTask.srcDrawer = MigrationPage.srcDrawer),
4343       Annotations = sum(case when len(SrcADrive) > 0 or len(SrcAMedia) > 0 then 1 else 0 end)  
4344  from MigrationPage  
4345  group by srcDrawer
4346  
4347end
4348GO
4349GO
4350IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reprocessFileErrors]') AND type in (N'P', N'PC'))
4351  Drop Procedure [dbo].[reprocessFileErrors]
4352GO
4353Create procedure [dbo].[reprocessFileErrors]
4354  @errortype varchar(1000),
4355  @DocumentID varchar(100) = null,
4356  @processAttributesOverride bit = null,	-- If this is null, the key\value from ConfigMigration will be used to determine the @processAttributes param value when calling exec process;
4357  @debug bit = 0
4358as
4359begin
4360  set nocount on
4361  
4362  declare @logid int, @ProcName varchar(50)
4363  set @ProcName = Object_Name(@@PROCID)
4364  declare @errorparam varchar(50)
4365  set @errorparam = left(@errortype,50)
4366  exec logStart @procname, @system = null, @param1 = @errorparam, @ID = @logid output
4367  Raiserror ('    Reprocessing Errors of type: %s',10,1, @errortype) with nowait
4368  	
4369  declare @processAttributes bit = 1;
4370  if @processAttributesOverride is null  
4371		select @processAttributes = case ConfigValue when 'TRUE' then 1 when 'FALSE' then 0 else -9 end 
4372		from ConfigMigration 
4373		where ConfigKey='process_ExecuteProcessAttributesProc';
4374  else set @processAttributes = @processAttributesOverride;
4375  
4376  -- get the list of all the documents with errors of this type.
4377  SELECT LogID = LogProgress.ID,
4378         DocumentID = MigrationDocument.ID, 
4379         FileID = MigrationDocument.FileID,
4380         System = MigrationDocument.System
4381    INTO #ErrorList
4382    FROM LogProgress INNER JOIN MigrationDocument 
4383         ON MigrationDocument.ID = LogProgress.DocumentID
4384   WHERE MigrationDocument.Status = -1 AND LogProgress.success = 0 and errormessage like @errortype + '%'     
4385     AND MigrationDocument.ID = isNull(@DocumentID,MigrationDocument.ID)
4386  
4387  -- Move all the old errors to LogProgressHistory
4388  insert into LogProgressHistory
4389   select getDate(), * from LogProgress
4390    where LogProgress.ID in (select LogID from #ErrorList)
4391  
4392  delete from LogProgress where LogProgress.ID in (select LogID from #ErrorList)
4393  
4394  -- Reset the errored documents back to loaded status so they can be reprocessed.  
4395  update MigrationDocument
4396     set status = -2
4397    from #ErrorList
4398   where MigrationDocument.ID = #ErrorList.DocumentID
4399  
4400  -- Reset the IRDrawer to null so that we can verify if they are mapped correctly later.  
4401  update MigrationPage
4402     set IRDrawer = null
4403    from #ErrorList
4404   where MigrationPage.DocumentID = #ErrorList.DocumentID 
4405
4406   declare @system varchar(50)
4407   
4408   -- Now go through each distinct file and process them.  We do NOT change the status of the file
4409   --    since we might still be processing the initial load and don't want that process to pick these up.
4410   declare @fileid varchar(100), @fileLogID int, @filecount int, @doccount int
4411   set @filecount = 0
4412   select @doccount = count(distinct DocumentID) from #ErrorList
4413   declare fileCursor cursor local static for
4414     select distinct fileid, System from #ErrorList
4415   open fileCursor
4416   fetch from fileCursor into @fileid, @system
4417   while @@fetch_status = 0
4418   begin   
4419     exec logStart @procname, @system = @system, @param1 = @fileid, @param2 = @errorparam, @ID = @fileLogID output
4420     Raiserror ('    Reprocessing Errors of type: %s',10,1, @errortype) with nowait
4421     exec processPages @fileid = @fileid, @processAttributes = @processAttributes, @debug = @debug
4422     exec processTasks @fileid = @fileid, @debug = @debug
4423     exec processDocuments @fileid = @fileid, @debug = @debug
4424     exec logEnd @fileLogID
4425     set @filecount = @filecount + 1
4426     fetch from fileCursor into @fileid, @system
4427   end
4428   close fileCursor
4429   deallocate fileCursor  
4430  
4431  Raiserror ('        **********  Completed Reprocessing Errors!  ',10,1) with nowait
4432  Raiserror ('        Total Number of Files: %d',10,1, @filecount) with nowait
4433  Raiserror ('        Total Number of Documents: %d',10,1, @doccount) with nowait
4434  exec logEnd @logid
4435end
4436
4437GO
4438IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reprocessFileHolds]') AND type in (N'P', N'PC'))
4439  Drop Procedure [dbo].[reprocessFileHolds]
4440GO
4441Create procedure [dbo].[reprocessFileHolds]
4442  @holdReason varchar(1000),
4443  @DocumentID varchar(100) = null,
4444  @processAttributesOverride bit = null,	-- If this is null, the key\value from ConfigMigration will be used to determine the @processAttributes param value when calling exec process;
4445  @debug bit = 0
4446as
4447begin
4448  set nocount on
4449  
4450  declare @logid int, @ProcName varchar(50)
4451  set @ProcName = Object_Name(@@PROCID)
4452  declare @holdParam varchar(50)
4453  set @holdParam = left(@holdReason,50)
4454  exec logStart @procname, @system = null, @param1 = @holdParam, @ID = @logid output
4455  Raiserror ('    Reprocessing Hold Reason of type: %s',10,1, @holdReason) with nowait
4456  	
4457  declare @processAttributes bit = 1;
4458  if @processAttributesOverride is null  
4459		select @processAttributes = case ConfigValue when 'TRUE' then 1 when 'FALSE' then 0 else -9 end 
4460		from ConfigMigration 
4461		where ConfigKey='process_ExecuteProcessAttributesProc';
4462  else set @processAttributes = @processAttributesOverride;
4463  
4464  -- get the list of all the documents with errors of this type.
4465  SELECT LogID = LogProgress.ID,
4466         DocumentID = MigrationDocument.ID, 
4467         FileID = MigrationDocument.FileID,
4468         System = MigrationDocument.System
4469    INTO #ErrorList
4470    from MigrationDocument
4471    left join LogProgress on MigrationDocument.ID=LogProgress.DocumentID
4472      and LogProgress.errormessage is not null
4473   WHERE MigrationDocument.Status = -5     
4474     AND MigrationDocument.ID = isNull(@DocumentID,MigrationDocument.ID)
4475  
4476  -- Move any error LogProgress for these docs to LogProgressHistory
4477  insert into LogProgressHistory
4478   select getDate(), * from LogProgress
4479    where LogProgress.ID in (select LogID from #ErrorList)
4480  
4481  delete from LogProgress where LogProgress.ID in (select LogID from #ErrorList)
4482  
4483  -- Reset the hold reasons for these docs
4484  update MigrationDocument
4485    set HoldReason=null
4486  where ID in(select DocumentID from #ErrorList)
4487  
4488  -- Reset the errored documents back to loaded status so they can be reprocessed.  
4489  update MigrationDocument
4490     set status = -2
4491    from #ErrorList
4492   where MigrationDocument.ID = #ErrorList.DocumentID
4493  
4494  -- Reset the IRDrawer to null so that we can verify if they are mapped correctly later.  
4495  update MigrationPage
4496     set IRDrawer = null
4497    from #ErrorList
4498   where MigrationPage.DocumentID = #ErrorList.DocumentID 
4499
4500   declare @system varchar(50)
4501   
4502   -- Now go through each distinct file and process them.  We do NOT change the status of the file
4503   --    since we might still be processing the initial load and don't want that process to pick these up.
4504   declare @fileid varchar(100), @fileLogID int, @filecount int, @doccount int
4505   set @filecount = 0
4506   select @doccount = count(distinct DocumentID) from #ErrorList
4507   declare fileCursor cursor local static for
4508     select distinct fileid, System from #ErrorList
4509   open fileCursor
4510   fetch from fileCursor into @fileid, @system
4511   while @@fetch_status = 0
4512   begin   
4513     exec logStart @procname, @system = @system, @param1 = @fileid, @param2 = @holdParam, @ID = @fileLogID output
4514     Raiserror ('    Reprocessing Holds with Hold Reason of: %s',10,1, @holdReason) with nowait
4515     exec processPages @fileid = @fileid, @processAttributes = @processAttributes, @debug = @debug
4516     exec processTasks @fileid = @fileid, @debug = @debug
4517     exec processDocuments @fileid = @fileid, @debug = @debug
4518     exec logEnd @fileLogID
4519     set @filecount = @filecount + 1
4520     fetch from fileCursor into @fileid, @system
4521   end
4522   close fileCursor
4523   deallocate fileCursor  
4524  
4525  Raiserror ('        **********  Completed Reprocessing Errors!  ',10,1) with nowait
4526  Raiserror ('        Total Number of Files: %d',10,1, @filecount) with nowait
4527  Raiserror ('        Total Number of Documents: %d',10,1, @doccount) with nowait
4528  exec logEnd @logid
4529end
4530
4531GO
4532IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[resubmitDocumentErrors]') AND type in (N'P', N'PC'))
4533  Drop Procedure [dbo].[resubmitDocumentErrors]
4534GO
4535create procedure resubmitDocumentErrors
4536  @errortype varchar(1000)
4537as
4538begin
4539	select DocumentID into #docs
4540	 from LogProgress where success = 0 and errormessage like @errortype + '%'
4541
4542	delete from LogProgress where DocumentID in (select DocumentID from #docs)
4543
4544	update MigrationDocument set status = 0 where ID in (select DocumentID from #docs)
4545end
4546GO
4547IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[validateMappings]') AND type in (N'P', N'PC'))
4548  Drop Procedure [dbo].[validateMappings]
4549GO
4550create Procedure dbo.validateMappings
4551  @system varchar(50) = null,
4552  @drawer varchar(100) = null,
4553  @validationMode varchar(100) = 'PostProcess',	-- Value should be either PreProcess or PostProcess
4554						-- PreProcess: Validates the mapping prior to executing process
4555						-- PostProcess: Validates the mapping after executing process
4556  @validateDocs bit = 1,
4557  @validatePageMarks bit = 1,
4558  @validateTasks bit = 1,
4559  @validateWorkFlowUsers bit = 1
4560as
4561begin
4562  set nocount on
4563  Set Transaction Isolation Level Read Uncommitted
4564     
4565  declare @logid int, @ProcName varchar(50)
4566  set @ProcName = Object_Name(@@PROCID)  
4567  exec logStart @procname, @system, @drawer, @ID = @logid output
4568  
4569  if @validationMode not in('PostProcess','PreProcess')
4570  begin
4571	raiserror('validateMappings ERROR: @validationMode must be either PostProcess or PreProcess',15,1)
4572	return
4573  end
4574
4575  --exec prepareIndexes @table = 'MigrationPage', @index = 'IX_MigrationPage_MappingSample'
4576  --exec prepareIndexes @table = 'MigrationPage', @index = 'IX_MigrationPage_DocumentID'
4577  
4578	if @validateDocs=1
4579	begin
4580	  RAISERROR ('   Validating Document Mappings', 10, 1) WITH NOWAIT    
4581
4582	  -- PAGE Mappings
4583	  select MigrationPage.System, MigrationPage.SrcDrawer, MigrationPage.SrcFileType,
4584			 MigrationPage.SrcFolderPath, MigrationPage.SrcDocType, 
4585			 FileNumber = min(MigrationPage.SrcFileNumber), PageCount = count(MigrationPage.ID)
4586		into #SampleDocs
4587		from MigrationDocument, MigrationPage
4588		where MigrationDocument.System = isNull(@system, MigrationDocument.System)
4589		and MigrationDocument.SrcDrawer = isNull(@drawer,MigrationDocument.SrcDrawer)
4590		and MigrationPage.DocumentID = MigrationDocument.ID and MigrationDocument.status > -9
4591		group by MigrationPage.System, MigrationPage.SrcDrawer, MigrationPage.SrcFileType, MigrationPage.SrcFolderPath, MigrationPage.SrcDocType
4592
4593	  select System, SrcDrawer, SrcFolderPath, SrcDocType 
4594		into #DocMap
4595		from MapDocument
4596	   where System = isNull(@system,System) and SrcDrawer = isNull(@drawer,SrcDrawer)
4597	   
4598	   create table #UnMappedDocs(	-- Using Create Table instead of select into due to SQL Parser thinking that I'm trying to create the temp table twice when trying to select into in the if else.
4599			[Status] varchar(500),
4600			[Source System] varchar(500),
4601			[Source Drawer] varchar(500),
4602			[Source File Type] varchar(500),
4603			[Source Drawer Description] varchar(500),
4604			[Source Drawer Owner] varchar(500),
4605			[Source Document Type] varchar(500),
4606			[Source Document Name] varchar(500),
4607			[Source Folder Type] varchar(500),
4608			[Source Folder Description] varchar(500),
4609			[No of Pages] varchar(500),
4610			[Sample File] varchar(500)
4611			)
4612
4613		if @validationMode='PostProcess'
4614		begin
4615			insert #UnMappedDocs
4616			select distinct 
4617			 [Status] = 'Missing',
4618			 [Source System] = #SampleDocs.System,
4619			 [Source Drawer] = #SampleDocs.SrcDrawer,
4620			 [Source File Type] = isnull(#SampleDocs.SrcFileType,''), 
4621			   [Source Drawer Description] = isNull((select top 1 Description from MigrationType 
4622										where System = #SampleDocs.System and Kind = 'Location' and ItemType = #SampleDocs.SrcDrawer),''),
4623			   [Source Drawer Owner] = cast('' as varchar(50)), 
4624			 [Source Document Type] = #SampleDocs.SrcDocType,
4625			 [Source Document Name] = isNull((select top 1 Description from MigrationType 
4626										where System = #SampleDocs.System and Kind = 'Document' and ItemType = #SampleDocs.SrcDocType),''),
4627			 [Source Folder Type] = #SampleDocs.SrcFolderPath,
4628			 [Source Folder Description] = isNull((select top 1 Description from MigrationType
4629											 where System = #SampleDocs.System and Kind = 'Folder' and ItemType = #SampleDocs.SrcFolderPath),''),
4630			 [No of Pages] = PageCount,
4631			 [Sample File] = '''' + FileNumber
4632			 from #SampleDocs
4633			 full join #DocMap on #SampleDocs.System = #DocMap.System
4634			  and #SampleDocs.SrcDrawer = #DocMap.SrcDrawer
4635			  and #SampleDocs.SrcFolderPath = #DocMap.SrcFolderPath
4636			  and #SampleDocs.SrcDocType = #DocMap.SrcDocType
4637			where #DocMap.System is null
4638		end
4639		else
4640		begin
4641			insert #UnMappedDocs
4642			select distinct 
4643			 [Status] = 'Missing',
4644			 [Source System] = #SampleDocs.System,
4645			 [Source Drawer] = #SampleDocs.SrcDrawer,
4646			 [Source File Type] = isnull(#SampleDocs.SrcFileType,''), 
4647			   [Source Drawer Description] = isNull((select top 1 Description from MigrationType 
4648										where System = #SampleDocs.System and Kind = 'Location' and ItemType = #SampleDocs.SrcDrawer),''),
4649			   [Source Drawer Owner] = cast('' as varchar(50)), 
4650			 [Source Document Type] = #SampleDocs.SrcDocType,
4651			 [Source Document Name] = isNull((select top 1 Description from MigrationType 
4652										where System = #SampleDocs.System and Kind = 'Document' and ItemType = #SampleDocs.SrcDocType),''),
4653			 [Source Folder Type] = #SampleDocs.SrcFolderPath,
4654			 [Source Folder Description] = isNull((select top 1 Description from MigrationType
4655											 where System = #SampleDocs.System and Kind = 'Folder' and ItemType = #SampleDocs.SrcFolderPath),''),
4656			 [No of Pages] = PageCount,
4657			 [Sample File] = '''' + FileNumber
4658			 from #SampleDocs
4659			 where not exists(
4660				select *
4661				from #DocMap
4662				where #SampleDocs.System = #DocMap.System
4663				  and #SampleDocs.SrcDrawer = #DocMap.SrcDrawer
4664				  and #SampleDocs.SrcFolderPath = #DocMap.SrcFolderPath
4665				  and #SampleDocs.SrcDocType = #DocMap.SrcDocType
4666				)
4667		end
4668
4669		if exists(select * from #UnMappedDocs)
4670		begin
4671		  raiserror ('ERROR: Some Pages did not have a valid Document Mapping',12,1) with nowait
4672		  select * from #UnMappedDocs
4673		  order by [Source Drawer], [Source Document Type]
4674		end
4675		else
4676		begin
4677		  Raiserror('      ******All Documents have a Mapping!',10,1)
4678		end
4679	end
4680	else raiserror('		Skipping Document Mapping Validation', 10,1);
4681
4682	if @validatePageMarks=1
4683	begin
4684	   RAISERROR ('   Validating PageMark Mappings', 10, 1) WITH NOWAIT    
4685	   -- PageMARK mappings
4686	  
4687	  select System, 
4688      SrcDrawer, 
4689      SrcPageMark, 
4690      PageCount = count(*), 
4691      SampleFile = max(MigrationPage.SrcFileNumber)
4692		into #SampleMarks
4693		from MigrationPage
4694		where System = isNull(@system,System) 
4695      and SrcDrawer = isNull(@drawer,SrcDrawer)
4696		  and SrcPageMark <> '0' 
4697      and SrcPageMark is not null	  
4698		group by System, SrcDrawer, SrcPageMark
4699
4700		create table #UnmappedPageMarks(
4701			[Status] varchar(500),
4702			System varchar(500),
4703			SrcDrawer varchar(500),
4704			SrcMark varchar(500),
4705			SrcDescription varchar(500),
4706			NoOfPages varchar(500),
4707      SampleFile varchar(255)
4708		)
4709
4710		if @validationMode='PostProcess'
4711			insert #UnmappedPageMarks
4712      select Status = 'Missing',
4713        System = #SampleMarks.System,
4714        SrcDrawer = #SampleMarks.SrcDrawer,
4715        SrcMark = #SampleMarks.SrcPageMark,
4716        SrcDescription = MigrationType.Description,
4717        NoOfPages = PageCount,
4718        SampleFile
4719      from #SampleMarks 
4720      left join MigrationType
4721        on #SampleMarks.SrcPageMark=MigrationType.ItemType
4722        and #SampleMarks.SrcDrawer=MigrationType.Drawer
4723        and #SampleMarks.system=MigrationType.system
4724      where MigrationType.kind='PageMark'
4725        and not exists(
4726          select *
4727          from MapMark
4728          where System = isNull(@system,System) 
4729            and SrcDrawer = isNull(@drawer,SrcDrawer)
4730            and MapMark.MarkType='page'
4731            and #SampleMarks.System = MapMark.System
4732            and #SampleMarks.SrcDrawer = MapMark.SrcDrawer
4733            and #SampleMarks.SrcPageMark = MapMark.SrcMarkID
4734            )
4735		else		
4736			insert #UnmappedPageMarks
4737      select Status = 'Missing',
4738        System = #SampleMarks.System,
4739        SrcDrawer = #SampleMarks.SrcDrawer,
4740        SrcMark = #SampleMarks.SrcPageMark,
4741        SrcDescription = MigrationType.Description,
4742        NoOfPages = PageCount,
4743        SampleFile
4744      from #SampleMarks 
4745      left join MigrationType
4746        on #SampleMarks.SrcPageMark=MigrationType.ItemType
4747        and #SampleMarks.SrcDrawer=MigrationType.Drawer
4748        and #SampleMarks.system=MigrationType.system
4749      where MigrationType.kind='PageMark'
4750
4751    if exists(select * from #UnmappedPageMarks)
4752		begin
4753		  raiserror ('ERROR: Some PageMarks did not have a valid Mapping',12,1) with nowait
4754      select Status as Status,
4755        System as 'Source System',
4756        SrcDrawer as 'Source Drawer',
4757        SrcMark as 'Source Mark',
4758        SrcDescription as 'Source Description',
4759        NoOfPages as 'No of Pages',
4760        SampleFile as 'Sample File'
4761      from #UnmappedPageMarks
4762      order by Status,
4763        System,
4764        SrcDrawer,
4765        SrcMark
4766		end
4767	  else
4768		raiserror ('    PageMarks are Mapped Successfully!',10,1) with nowait 
4769	end
4770	else raiserror('	Skipping PageMark Mapping Validation',10,1);
4771	
4772	if @validateTasks=1
4773	begin
4774	  -- Look for Unmapped Tasks
4775		RAISERROR ('   Validating Task Mappings', 10, 1) WITH NOWAIT    
4776	  select System, SrcFlowID, SrcStepID, COUNT(*) as TaskCount
4777		into #SampleTasks
4778	   from MigrationTask
4779	  where System = isNull(@system,System)
4780	   group by System, SrcFlowID, SrcStepID
4781
4782	  select System, SrcFlowID, SrcStepID
4783		into #TaskMap
4784		from MapTask
4785		where System = isNull(@system,System)
4786		
4787		create table #UnmappedTasks(
4788			[Status] varchar(500),
4789			[Source System] varchar(500),
4790			[Source Flow ID] varchar(500),
4791			[Source Flow Name] varchar(500),
4792			[Source Flow Description] varchar(500),   
4793			[Source Step ID] varchar(500),
4794			[Step Description] varchar(500),    
4795			[No of Tasks] varchar(500)
4796		);
4797		
4798		if @validationMode='PostProcess'
4799			insert #UnmappedTasks
4800			select Status = 'Missing',
4801				[Source System] = #SampleTasks.System,
4802				[Source Flow ID] = #SampleTasks.SrcFlowID,
4803				[Source Flow Name] = (select top 1 Description from MigrationType 
4804								where System = isNull(@system,System) and Kind = 'Flow' and ItemType = #SampleTasks.SrcFlowID),
4805				[Source Flow Description] = (select top 1 Description from MigrationType 
4806									   where System = isNull(@system,System) and Kind = 'Flow' and ItemType = #SampleTasks.SrcFlowID),     
4807				[Source Step ID] = #SampleTasks.SrcStepID,
4808				[Step Description] = (select top 1 Description from MigrationType 
4809								where System = isNull(@system,System) and Kind = 'Step' 
4810								  and ItemType = cast(#SampleTasks.SrcFlowID as varchar) + '_' + cast(#SampleTasks.SrcStepID as varchar)),     
4811				[No of Tasks] = TaskCount
4812			from #SampleTasks 
4813			full join #TaskMap on #SampleTasks.System = #TaskMap.System		 
4814				and #SampleTasks.SrcFlowID = #TaskMap.srcFlowid
4815				and #SampleTasks.SrcStepID = #TaskMap.srcStepid
4816			where #TaskMap.System is null       
4817		else
4818			insert #UnmappedTasks
4819			select Status = 'Missing',
4820			[Source System] = #SampleTasks.System,
4821			[Source Flow ID] = #SampleTasks.SrcFlowID,
4822			[Source Flow Name] = (select top 1 Description from MigrationType 
4823								where System = isNull(@system,System) and Kind = 'Flow' and ItemType = #SampleTasks.SrcFlowID),
4824			[Source Flow Description] = (select top 1 Description from MigrationType 
4825									   where System = isNull(@system,System) and Kind = 'Flow' and ItemType = #SampleTasks.SrcFlowID),     
4826			[Source Step ID] = #SampleTasks.SrcStepID,
4827			[Step Description] = (select top 1 Description from MigrationType 
4828								where System = isNull(@system,System) and Kind = 'Step' 
4829								  and ItemType = cast(#SampleTasks.SrcFlowID as varchar) + '_' + cast(#SampleTasks.SrcStepID as varchar)),     
4830			[No of Tasks] = TaskCount
4831			from #SampleTasks 
4832			where not exists(
4833				select *
4834				from #TaskMap
4835				where #SampleTasks.System = #TaskMap.System		 
4836				 and #SampleTasks.SrcFlowID = #TaskMap.srcFlowid
4837				 and #SampleTasks.SrcStepID = #TaskMap.srcStepid
4838				)
4839
4840	  if exists(select * from #UnmappedTasks)
4841	  begin
4842		  raiserror ('ERROR: Some Tasks did not have a valid workflow Mapping',12,1) with nowait
4843		  select * from #UnmappedTasks
4844	  end
4845	  else
4846		  raiserror('**********  No Unmapped Tasks!',10,1) with nowait
4847	end
4848	else raiserror('	Skipping PageMark Mapping Validation',10,1);
4849	
4850	if @validateWorkFlowUsers=1
4851	begin
4852	  -- Task Assigned User Checking
4853	  RAISERROR ('   Validating User Mappings', 10, 1) WITH NOWAIT    
4854	  select System, SrcAssignedToUserID, count(*) as TaskCount
4855		into #SampleUsers
4856		from MigrationTask
4857	   where SrcAssignedToUserID is not null and System = isNull(@system,System)
4858	   group by System, SrcAssignedToUserID
4859
4860	  select System, SrcUserID
4861		into #UserMap
4862		from MapUser
4863	   where System = isNull(@system,System)
4864	   
4865		create table #UnmappedUsers(
4866			Status varchar(500),
4867			[Source System] varchar(500),
4868			[Source User ID] varchar(500),
4869			[Source User Name] varchar(500),
4870			[No. Of Tasks Assigned] varchar(500)
4871		)
4872
4873		if @validationMode='PostProcess'
4874			insert #UnmappedUsers
4875			select Status = 'Missing',
4876				[Source System] = #SampleUsers.System,
4877				[Source User ID] = #SampleUsers.SrcAssignedToUserID,
4878				[Source User Name] = (select top 1 description from MigrationType 
4879								where System = isNull(@system,System) and Kind = 'User' and ItemType = #SampleUsers.SrcAssignedToUserID),
4880				[No. Of Tasks Assigned] = TaskCount
4881			from #SampleUsers 
4882			full join #UserMap on #SampleUsers.System = #UserMap.System		 
4883				and #SampleUsers.SrcAssignedToUserID = #UserMap.SrcUserID	  
4884			where #UserMap.System is null
4885		else
4886			insert #UnmappedUsers
4887			select Status = 'Missing',
4888				[Source System] = #SampleUsers.System,
4889				[Source User ID] = #SampleUsers.SrcAssignedToUserID,
4890				[Source User Name] = (select top 1 description from MigrationType 
4891								where System = isNull(@system,System) and Kind = 'User' and ItemType = #SampleUsers.SrcAssignedToUserID),
4892				[No. Of Tasks Assigned] = TaskCount
4893			from #SampleUsers 
4894			where not exists(
4895				select *
4896				from #UserMap 
4897				where #SampleUsers.System = #UserMap.System
4898					and #SampleUsers.SrcAssignedToUserID = #UserMap.SrcUserID	  
4899				)
4900
4901	   if exists(select * from #UnmappedUsers)
4902		begin
4903		  raiserror ('ERROR: Some Tasks did not have a valid ''assigned to'' user mapping',12,1) with nowait
4904		  select * from #UnmappedUsers
4905		  order by [Source User ID]      
4906		end
4907	  else
4908		raiserror ('    Task ''assigned to'' users are Mapped Successfully!',10,1) with nowait
4909	end
4910	else raiserror('	Skipping PageMark Mapping Validation',10,1);
4911  
4912
4913  exec logEnd @logid 
4914end
4915GO
4916IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[configureDefaults]') AND type in (N'P', N'PC'))
4917  Drop Procedure [dbo].[configureDefaults]
4918GO
4919CREATE Procedure [dbo].[configureDefaults]
4920  @system varchar(50),
4921  @estMigrationDate datetime  
4922as
4923begin
4924  set nocount on
4925  Set Transaction Isolation Level Read Uncommitted
4926     
4927  declare @logid int, @ProcName varchar(50)
4928  set @ProcName = Object_Name(@@PROCID)  
4929  exec logStart @procname, @system, @ID = @logid output
4930  
4931  if exists(select * from ConfigDrawerList where system = @system)
4932     or exists(select * from ConfigPriority where System = @system)
4933  begin
4934    raiserror('  System: %s already has some configuration.',1,18,@system)
4935    return
4936  end
4937  
4938  if (db_name() in ('IconIRNet','ExtractIRNet'))
4939  begin
4940    insert into ConfigDrawerList
4941    select distinct system = @system, Name, 'None', null from SnapshotLocation   
4942  end
4943  if (db_name() = 'IconIR3x')
4944  begin
4945    insert into ConfigDrawerList
4946    select distinct system = @system, drawer, 'None', null from SnapshotDocument3x
4947     where drawer <> 'DEL*'
4948  end
4949
4950  insert into ConfigPriority
4951    (Priority, [Rule], Description, System)
4952  values (2,'Sample','Sample of each Mapping Combination',@system)
4953
4954  insert into ConfigPriority
4955    (Priority, [Rule], Description, System)
4956  values (3,'TaskFiles','All Files with Active Tasks',@system)
4957
4958  declare @date datetime
4959
4960  set @date = dateAdd(Month,-6,@estMigrationDate)
4961  insert into ConfigPriority
4962    (Priority, [Rule], Description, System, filedate)
4963  values (4,'DocDate','All Files Modified in the last 6 months',@system,@date)
4964
4965  set @date = dateAdd(Year,-1,@estMigrationDate)
4966  insert into ConfigPriority
4967    (Priority, [Rule], Description, System, filedate)
4968  values (5,'DocDate','All Remaining Files Modified in the last year',@system,@date)
4969
4970  set @date = dateAdd(YEAR,-2,@estMigrationDate)
4971  insert into ConfigPriority
4972    (Priority, [Rule], Description, System, filedate)
4973  values (6,'DocDate','All Remaining Files Modified in the last 2 years',@system,@date)
4974
4975  set @date = dateAdd(YEAR,-3,@estMigrationDate)
4976  insert into ConfigPriority
4977    (Priority, [Rule], Description, System, filedate)
4978  values (7,'DocDate','All Remaining Files Modified in the last 3 years',@system,@date)
4979
4980  set @date = dateAdd(Month,-5,@estMigrationDate)
4981  insert into ConfigPriority
4982    (Priority, [Rule], Description, System, filedate)
4983  values (8,'DocDate','All Remaining Files Modified in the last 5 years',@system,@date)
4984  
4985  if (db_name() = 'MigrationIRNet')
4986  begin
4987    insert into ConfigDeviceList
4988    select distinct system = @system, uncpath, null from SnapshotDevice
4989  end
4990  
4991  exec logEnd @logid
4992  
4993end
4994GO
4995IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[takeDestStructureSnapshot]') AND type in (N'P', N'PC'))
4996  Drop Procedure [dbo].[takeDestStructureSnapshot]
4997GO
4998CREATE procedure [dbo].[takeDestStructureSnapshot]
4999    @database varchar(50),
5000    @linkedServer varchar(50) = null,
5001    @oracle bit = 0,
5002	@debug bit = 0,
5003	@execute bit = 1
5004		/* This procedure will be a work in progress and should allow verification of the current mapping
5005			against the current destination database. Currently, only the verification for PageMarks are implemented
5006		*/
5007  as
5008  begin
5009  declare @logid int, @ProcName varchar(50)
5010  set @ProcName = Object_Name(@@PROCID)
5011  exec logStart @procname, null, @ID = @logid output
5012  
5013  declare @sql varchar(5000)
5014  declare @table varchar(50)
5015  declare @debugMessage varchar(5000)
5016  
5017  if (@oracle = 0)
5018    set @database = @database + '.dbo' 
5019  
5020	
5021    set @table = 'DestStructureSnapshotObjectType'
5022		RAISERROR ('   Taking New %s', 10, 1,@table) WITH NOWAIT;
5023		set @sql = '
5024			select typeid 			as typeid, 
5025				classid 			as typeClassID, 
5026				name 				as typeName, 
5027				description 		as typeDescription, 
5028				lastmodified 		as typeLastModified,  
5029				programmaticname 	as typeProgrammaticName
5030			from ' + @database + '.ObjectType WITH (NOLOCK)'
5031			
5032		if (@linkedServer is not null)
5033		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5034					 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5035		else
5036		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5037					 ' from (' + @sql + ') as T'
5038		
5039		if (@debug>0)
5040		begin
5041			set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5042			raiserror(@debugMessage,0,1,@table,@sql)
5043		end	
5044		if (@execute=1)
5045		begin
5046			if exists(select * from information_schema.tables where table_name=@table) 
5047				exec (N'drop table '+@table)
5048			exec (@sql) 
5049		end
5050  
5051    set @table = 'DestStructureSnapshotAttributeDef'
5052		RAISERROR ('   Taking New %s', 10, 1,@table) WITH NOWAIT;
5053		set @sql = '
5054			select attributeid 	as attributeid, 
5055				name 			as attrName, 
5056				type 			as attrType, 
5057				description 	as attrDescription, 
5058				displayname		as attrDisplayname
5059			from ' + @database + '.AttributeDef WITH (NOLOCK)'
5060			
5061		if (@linkedServer is not null)
5062		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5063					 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5064		else
5065		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5066					 ' from (' + @sql + ') as T'
5067		
5068		if (@debug>0)
5069		begin
5070			set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5071			raiserror(@debugMessage,0,1,@table,@sql)
5072		end	
5073		if (@execute=1)
5074		begin
5075			if exists(select * from information_schema.tables where table_name=@table) 
5076				exec (N'drop table '+@table)
5077			exec (@sql) 
5078		end
5079  
5080    set @table = 'DestStructureSnapshotObjectAttributeRules'
5081		RAISERROR ('   Taking New %s', 10, 1,@table) WITH NOWAIT;
5082		set @sql = '
5083			select ObjectType.typeid 		as typeid, 
5084				ObjectType.classid 			as typeClassID, 
5085				ObjectType.name 			as TypeName,
5086				AttributeDef.attributeid 	as attributeid, 
5087				AttributeDef.description 	as attrDescription, 
5088				AttributeDef.displayname	as attrDisplayname
5089			from ' + @database + '.ObjectAttributeRules WITH (NOLOCK)
5090			join ' + @database + '.ObjectType WITH (NOLOCK) on ObjectAttributeRules.typeid=ObjectType.typeid
5091			join ' + @database + '.AttributeDef WITH (NOLOCK) on ObjectAttributeRules.attributeid=AttributeDef.attributeid'
5092			
5093		if (@linkedServer is not null)
5094		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5095					 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5096		else
5097		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5098					 ' from (' + @sql + ') as T'
5099		
5100		if (@debug>0)
5101		begin
5102			set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5103			raiserror(@debugMessage,0,1,@table,@sql)
5104		end	
5105		if (@execute=1)
5106		begin
5107			if exists(select * from information_schema.tables where table_name=@table) 
5108				exec (N'drop table '+@table)
5109			exec (@sql) 
5110		end 
5111  
5112    set @table = 'DestStructureSnapshotWorkFlow'
5113		RAISERROR ('   Taking New %s', 10, 1,@table) WITH NOWAIT;
5114		set @sql = '
5115			select FlowDef.flowid,
5116				FlowDef.flowname,
5117				FlowDef.flowalias as FlowProgName,
5118				StepRootDef.steprootid,
5119				StepRootDef.stepname,
5120				StepRootDef.stepalias as StepProgName
5121			from ' + @database + '.FlowDef WITH (NOLOCK)
5122			join ' + @database + '.StepRootDef
5123				on FlowDef.flowid=StepRootDef.flowid'
5124			
5125		if (@linkedServer is not null)
5126		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5127					 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5128		else
5129		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5130					 ' from (' + @sql + ') as T'
5131		
5132		if (@debug>0)
5133		begin
5134			set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5135			raiserror(@debugMessage,0,1,@table,@sql)
5136		end	
5137		if (@execute=1)
5138		begin
5139			if exists(select * from information_schema.tables where table_name=@table) 
5140				exec (N'drop table '+@table)
5141			exec (@sql) 
5142		end
5143  
5144    set @table = 'DestStructureSnapshotMarkAllow'
5145		RAISERROR ('   Taking New %s', 10, 1,@table) WITH NOWAIT;
5146		set @sql = '
5147			select ''''page''''						as markType,
5148				PageMarkDef.pagemarkid			as pageMarkID,
5149				PageMarkDef.description			as pageMarkDescription,
5150				PageMarkDef.programmaticname	as pageMarkProgName,
5151				ObjectType.typeid				as typeid,
5152				ObjectType.name					as typeName,
5153				ObjectType.classid				as typeClassid,
5154				ObjectType.programmaticname		as typeProgName
5155			from ' + @database + '.PageMarkAllow with(nolock)
5156			join ' + @database + '.PageMarkDef with(nolock) on PageMarkAllow.pagemarkid=PageMarkDef.pagemarkid
5157			join ' + @database + '.ObjectType with(nolock) on PageMarkAllow.typeid=ObjectType.typeid
5158			union all
5159			select ''''file''''						as markType,
5160				FileMarkDef.filemarkid			as fileMarkID,
5161				FileMarkDef.description			as fileMarkDescription,
5162				FileMarkDef.programmaticname	as fileMarkProgName,
5163				ObjectType.typeid				as typeid,
5164				ObjectType.name					as typeName,
5165				ObjectType.classid				as typeClassid,
5166				ObjectType.programmaticname		as typeProgName
5167			from ' + @database + '.FileMarkAllow with(nolock)
5168			join ' + @database + '.FileMarkDef with(nolock) on FileMarkAllow.filemarkid=FileMarkDef.filemarkid
5169			join ' + @database + '.ObjectType with(nolock) on FileMarkAllow.typeid=ObjectType.typeid'
5170			
5171		if (@linkedServer is not null)
5172		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5173					 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5174		else
5175		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5176					 ' from (' + @sql + ') as T'
5177		
5178		if (@debug>0)
5179		begin
5180			set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5181			raiserror(@debugMessage,0,1,@table,@sql)
5182		end	
5183		if (@execute=1)
5184		begin
5185			if exists(select * from information_schema.tables where table_name=@table) 
5186				exec (N'drop table '+@table)
5187			exec (@sql) 
5188		end
5189  
5190    set @table = 'DestStructureSnapshotMarkDef'
5191		RAISERROR ('   Taking New %s', 10, 1,@table) WITH NOWAIT;
5192		set @sql = '
5193			select ''''page''''						as markType,
5194				PageMarkDef.pagemarkid			as MarkID,
5195				PageMarkDef.description			as MarkDescription,
5196				PageMarkDef.color				as MarkColor,
5197				PageMarkDef.programmaticname	as MarkProgrammaticName
5198			from ' + @database + '.PageMarkDef with(nolock)
5199			union all
5200			select ''''file''''						as markType,
5201				FileMarkDef.filemarkid			as MarkID,
5202				FileMarkDef.description			as MarkDescription,
5203				FileMarkDef.color				as MarkColor,
5204				FileMarkDef.programmaticname	as MarkProgrammaticName
5205			from ' + @database + '.FileMarkDef with(nolock)'
5206			
5207		if (@linkedServer is not null)
5208		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5209					 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5210		else
5211		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5212					 ' from (' + @sql + ') as T'
5213		
5214		if (@debug>0)
5215		begin
5216			set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5217			raiserror(@debugMessage,0,1,@table,@sql)
5218		end	
5219		if (@execute=1)
5220		begin
5221			if exists(select * from information_schema.tables where table_name=@table) 
5222				exec (N'drop table '+@table)
5223			exec (@sql) 
5224		end
5225  
5226    set @table = 'DestStructureSnapshotSecurityAccount'
5227		RAISERROR ('   Taking New %s', 10, 1,@table) WITH NOWAIT;
5228		set @sql = '
5229			select accountid,
5230				externalid,
5231				name,
5232				description,
5233				accounttype,
5234				disabled
5235			from ' + @database + '.SecurityAccount with(nolock)'
5236			
5237		if (@linkedServer is not null)
5238		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5239					 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5240		else
5241		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5242					 ' from (' + @sql + ') as T'
5243		
5244		if (@debug>0)
5245		begin
5246			set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5247			raiserror(@debugMessage,0,1,@table,@sql)
5248		end	
5249		if (@execute=1)
5250		begin
5251			if exists(select * from information_schema.tables where table_name=@table) 
5252				exec (N'drop table '+@table)
5253			exec (@sql) 
5254		end
5255  
5256    set @table = 'DestStructureSnapshotTypeRules'	-- What file types are allowed in drawers
5257		RAISERROR ('   Taking New %s', 10, 1,@table) WITH NOWAIT;
5258		set @sql = '
5259			select TypeRules.parenttypeid as parentTypeID,
5260				ParentType.classid as ParentClassid, 
5261				ParentType.name as ParentName, 
5262				ParentType.description as ParentDescription,
5263				TypeRules.childtypeid as childTypeID,
5264				ChildType.classid as ChildClassid, 
5265				ChildType.name as ChildName, 
5266				ChildType.description as ChildDescription
5267			from ' + @database + '.TypeRules with(nolock)
5268			join ' + @database + '.ObjectType as ParentType with(nolock) on TypeRules.parenttypeid=ParentType.typeid
5269			join ' + @database + '.ObjectType as ChildType with(nolock) on TypeRules.childtypeid=ChildType.typeid'
5270			
5271		if (@linkedServer is not null)
5272		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5273					 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5274		else
5275		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5276					 ' from (' + @sql + ') as T'
5277		
5278		if (@debug>0)
5279		begin
5280			set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5281			raiserror(@debugMessage,0,1,@table,@sql)
5282		end	
5283		if (@execute=1)
5284		begin
5285			if exists(select * from information_schema.tables where table_name=@table) 
5286				exec (N'drop table '+@table)
5287			exec (@sql) 
5288		end
5289  
5290    set @table = 'DestStructureSnapshotLocations'
5291		RAISERROR ('   Taking New %s', 10, 1,@table) WITH NOWAIT;
5292		set @sql = '
5293			select Locations.locationid,
5294				Locations.parentid,
5295				Locations.name as locationName,
5296				ObjectType.typeid as locationTypeID,
5297				ObjectType.classid as typeClassID,
5298				ObjectType.name as typeName,
5299				ObjectType.programmaticName as typeProgName
5300			from ' + @database + '.Locations
5301			join ' + @database + '.ObjectLink on Locations.locationid=ObjectLink.objectid
5302			join ' + @database + '.ObjectType on ObjectLink.typeid=ObjectType.typeid'
5303			
5304		if (@linkedServer is not null)
5305		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5306					 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5307		else
5308		  set @sql = 'select SnapShot = getDate(), * into dbo.' + @table + 
5309					 ' from (' + @sql + ') as T'
5310		
5311		if (@debug>0)
5312		begin
5313			set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5314			raiserror(@debugMessage,0,1,@table,@sql)
5315		end	
5316		if (@execute=1)
5317		begin
5318			if exists(select * from information_schema.tables where table_name=@table) 
5319				exec (N'drop table '+@table)
5320			exec (@sql) 
5321		end
5322  
5323  RAISERROR ('..Complete', 10, 1) WITH NOWAIT  
5324  exec logEnd @logid
5325 end 
5326GO
5327IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[validateMappingAgainstDestSnapshot]') AND type in (N'P', N'PC'))
5328  Drop Procedure [dbo].[validateMappingAgainstDestSnapshot]
5329GO
5330create procedure validateMappingAgainstDestSnapshot
5331  @system varchar(50) = null,
5332  @database varchar(100) = null,
5333  @linkedServer varchar(100) = null
5334as
5335begin
5336  set nocount on;
5337  if @database is not null
5338  begin
5339    exec raiseMessage 'validateMappingAgainstDestSnapshot: Taking new DestStructureSnapshot'
5340    exec takeDestStructureSnapshot @database=@database, @linkedServer=@linkedServer
5341  end
5342  else
5343    exec raiseMessage 'validateMappingAgainstDestSnapshot: @database not spedificed. Bypassing takeDestStructureSnapshot'
5344  
5345  select top 1 Snapshot as ShapshotDate from DestStructureSnapshotObjectType;
5346  
5347  select 'Invalid Drawer' as Status, 
5348    DestDrawer, COUNT(*) as Mappings
5349  from MapDocument
5350  where System=isnull(@system,System)
5351    and DestDrawer not in('Do Not Migrate','omit','delete')
5352    and DestDrawer not in(
5353    select locationName
5354    from DestStructureSnapshotLocations
5355    )
5356  group by DestDrawer;
5357  
5358  select 'Invalid FileType' as Status, 
5359    DestFileType, COUNT(*) as Mappings
5360  from MapDocument
5361  where System=isnull(@system,System)
5362    and DestFileType not in('Do Not Migrate','omit','delete')
5363    and DestFileType not in(
5364      select typeName
5365      from DestStructureSnapshotObjectType
5366      where typeClassID=-3
5367      )
5368  group by DestFileType;
5369
5370  ;with FolderTypes as(
5371    select distinct --DestFolderPath,
5372      case
5373        when DestFolderPath like '%\\%' then substring(DestFolderPath,1,charindex('\\',DestFolderPath,1)-1)
5374        else DestFolderPath
5375      end as CurrentNode,
5376      case 
5377        when DestFolderPath like '%\\%' then substring(DestFolderPath,Charindex('\\',DestFolderPath,1)+2,10000)
5378        else null
5379      end as Remaining
5380    from MapDocument
5381    where System=isnull(@system,System)
5382    union all
5383    select CurrentNode, null as Remaining
5384    from FolderTypes
5385    where Remaining is not null
5386    ),
5387  MappedFolders as(
5388    select 
5389      case 
5390        when 0<charindex(';',CurrentNode)
5391          then SUBSTRING(CurrentNode,1,CHARINDEX(';',CurrentNode)-1)
5392        else CurrentNode 
5393      end as FolderType
5394    from FolderTypes
5395    where CurrentNode not in('Do Not Migrate','omit','delete')
5396  )
5397  select 'Invalid FolderType' as Status, 
5398    FolderType
5399  from MappedFolders
5400  where MappedFolders.FolderType not in(
5401    select typeName
5402    from DestStructureSnapshotObjectType
5403    where typeClassID=-2
5404    )
5405  group by FolderType;
5406
5407  select 'Invalid DocType' as Status, 
5408     DestDocType
5409  from MapDocument
5410  where System=isnull(@system,System)
5411    and DestDocType not in('Do Not Migrate','omit','delete')
5412    and DestDocType not in(
5413      select typeName
5414      from DestStructureSnapshotObjectType
5415      where typeClassID=-1
5416      )
5417  group by DestDocType;
5418
5419    -- Show page marks that may not be allowed on the file type
5420      /* Note that this is a fuzzy logic scenario and CURRENTLY VERY EXPERIMENTAL. There is not any file type in 
5421        the mark mapping, so there is not 
5422        a definitive method to determine what file type a mark needs to be migrated to from the mapping alone. In 
5423        order to do that, the processPageMarks must be executed first and the file types must be pulled from ProcessPage.
5424        This query (below) doesn't do that. It takes a distinct list of all file types in the MapDocument table and
5425        cross joins that to all of the page marks. This will return false negatives if the client doesnt' intend to add 
5426        all page marks to all file types.
5427        The solution to validating mark mapping may be to first pre-process all marks and file types for pages that have marks.
5428      */
5429  select *
5430  from MapMark
5431  cross join (
5432    select distinct 
5433      MapDocument.system, 
5434      MapDocument.DestFileType ,
5435      DestStructureSnapshotObjectType.typeid
5436    from MapDocument
5437    join DestStructureSnapshotObjectType on MapDocument.DestFileType=DestStructureSnapshotObjectType.typeName
5438    where typeClassID=-3
5439    ) MapDestFileType
5440  where MapMark.System=MapDestFileType.System
5441    and  exists(
5442    select *
5443    from DestStructureSnapshotMarkAllow
5444    where MapMark.MarkType=DestStructureSnapshotMarkAllow.markType
5445      and MapMark.DestMarkID=DestStructureSnapshotMarkAllow.pageMarkID
5446      and MapDestFileType.typeid=DestStructureSnapshotMarkAllow.typeid
5447      )
5448  order by DestMarkDesc	
5449
5450
5451    -- Validate Flows
5452  select 'Invalid Flow' as Status, 
5453      *
5454  from MapTask
5455  where System=isnull(@system,System)
5456    and DestFlowProgName not in('','Do Not Migrate')
5457    and not exists(
5458      select *
5459      from DestStructureSnapshotWorkFlow
5460      where MapTask.DestFlowProgName=DestStructureSnapshotWorkFlow.FlowProgName
5461      )
5462  order by DestFlowProgName
5463    -- Validate Steps
5464  select 'Invalid Step' as Status, 
5465      *
5466  from MapTask
5467  where System=isnull(@system,System)
5468    and DestFlowProgName not in('','Do Not Migrate')
5469    and not exists(
5470      select *
5471      from DestStructureSnapshotWorkFlow
5472      where MapTask.DestStepProgName=DestStructureSnapshotWorkFlow.StepProgName
5473      )
5474  order by DestStepProgName
5475    -- Validate Flow\Step combinations - may be invalid due to invalid flow or step as noted by prior queries
5476  select 'Invalid Flow\Step Combination' as Status, 
5477      * 
5478  from MapTask
5479  where System=isnull(@system,System)
5480    and DestFlowProgName<>'Do Not Migrate' 
5481    and not exists(
5482    select *
5483    from DestStructureSnapshotWorkFlow
5484    where MapTask.DestFlowProgName=DestStructureSnapshotWorkFlow.FlowProgName
5485      and MapTask.DestStepProgName=DestStructureSnapshotWorkFlow.StepProgName
5486      )
5487    and DestFlowProgName<>''
5488  order by DestFlowProgName, DestStepProgName
5489    
5490    -- TODO: Validate WF User Mappings
5491  -- select 'Invalid DocType' as Status, 
5492    -- *
5493  -- from bleh
5494  -- where bleh
5495end
5496GO