/*
Volian Enterprises, Inc.
Script to fix RO Problems
*/
/* This is equivalent to the FindROProblems query
select vn.versionid, ParentName, FolderName, aa.rofstid, rf.rodbid, ru.rodbid Urodbid, Count(*) Problems
from vefn_getversionnames() vn
join vefn_getversionitems('') vi on vn.versionid = vi.versionid
join rousages ru on ru.contentid = vi.contentid
join associations aa on aa.versionid = vn.versionid
join rofsts rf on rf.rofstid = aa.rofstid
where rf.rodbid <> ru.rodbid
group by vn.versionid, ParentName, FolderName, aa.rofstid, rf.rodbid, ru.rodbid

select vn.versionid, ParentName, FolderName, aa.rofstid, rf.rodbid, dru.rodbid Urodbid, Count(*) Problems
from vefn_getversionnames() vn
join vefn_getversionitems('') vi on vn.versionid = vi.versionid
join entries ee on ee.contentID = vi.contentID
join drousages dru on dru.docid = ee.docid
join associations aa on aa.versionid = vn.versionid
join rofsts rf on rf.rofstid = aa.rofstid
where rf.rodbid <> dru.rodbid
group by vn.versionid, ParentName,FolderName, aa.rofstid, rf.rodbid, dru.rodbid
GO
*/
EXECUTE [vesp_TurnChangeManagerOFF] 
GO

PRINT 'Build @ProblemROUsages'
DECLARE @ProblemROUsages table(
	ROUsageID int primary key,
	ContentID int,
	RODBID int,
	URODBID int
)
Insert Into @ProblemROUsages 
select ru.ROUsageID, RU.ContentID, rf.rodbid, ru.rodbid Urodbid 
from vefn_getversionnames() vn
join vefn_getversionitems('') vi on vn.versionid = vi.versionid
join rousages ru on ru.contentid = vi.contentid
join associations aa on aa.versionid = vn.versionid
join rofsts rf on rf.rofstid = aa.rofstid
where rf.rodbid <> ru.rodbid

--select * from @ProblemROUsages

PRINT 'Build @ProblemDROUsages'
DECLARE @ProblemDROUsages table(
	DROUsageID int primary key,
	RODBID int,
	URODBID int
)
Insert Into @ProblemDROUsages 
select distinct dru.dROUsageID,  rf.rodbid, dru.rodbid Urodbid
from vefn_getversionnames() vn
join vefn_getversionitems('') vi on vn.versionid = vi.versionid
join entries ee on ee.ContentID=vi.ContentID
join drousages dru on dru.docid = ee.DocID
join associations aa on aa.versionid = vn.versionid
join rofsts rf on rf.rofstid = aa.rofstid
where rf.rodbid <> dru.rodbid

--Select * from @ProblemDROUsages


-- Contents - replacing rodbid's in content records' link text, for the input version
PRINT 'Process Contents'
update CC
set text = replace(text, 
  ' ' + cast(pru.URODBID as nvarchar(10)) + '[END>', 
	' ' + cast(pru.RODBID as nvarchar(10)) + '[END>')
from contents CC join @ProblemROUsages PRU ON CC.ContentID = PRU.ContentID


-- Grids - replacing rodbid's in grid records' link text
PRINT 'Process Grids'
update GG
set data = cast(replace(cast(data as nvarchar(max)), 
  ' ' + cast(pru.URODBID as nvarchar(10)) + '[END&gt;', 
	' ' + cast(pru.RODBID as nvarchar(10)) + '[END&gt;') as xml)
from grids GG join @ProblemROUsages PRU ON GG.ContentID = PRU.ContentID

-- ROUsages - updating rousage records with correct rodbid
PRINT 'Process ROUsages'
update RU
set rodbid = PRU.rodbid
From rousages RU Join @ProblemROUsages PRU ON RU.ROUsageID=pru.ROUsageID

-- DROUsages - updating rousage records with correct rodbid
PRINT 'Process DROUsages'
update DRU
set rodbid = PDRU.rodbid
From drousages DRU Join @ProblemDROUsages PDRU ON DRU.DROUsageID=pdru.DROUsageID

-- ROFSTs - Update RODBID to 2 from 3
-- Associations - No Change
-- ROImages - No Change
-- Figures - Insert duplicate records for figures
-- Remove unused RODBs
GO
EXECUTE [vesp_TurnChangeManagerON] 
GO
/*
This is equivalent to FindROProblems query 
select vn.versionid, ParentName, FolderName, aa.rofstid, rf.rodbid, ru.rodbid Urodbid, Count(*) Problems
from vefn_getversionnames() vn
join vefn_getversionitems('') vi on vn.versionid = vi.versionid
join rousages ru on ru.contentid = vi.contentid
join associations aa on aa.versionid = vn.versionid
join rofsts rf on rf.rofstid = aa.rofstid
where rf.rodbid <> ru.rodbid
group by vn.versionid, ParentName, FolderName, aa.rofstid, rf.rodbid, ru.rodbid

select vn.versionid, ParentName, FolderName, aa.rofstid, rf.rodbid, dru.rodbid Urodbid, Count(*) Problems
from vefn_getversionnames() vn
join vefn_getversionitems('') vi on vn.versionid = vi.versionid
join entries ee on ee.contentID = vi.contentID
join drousages dru on dru.docid = ee.docid
join associations aa on aa.versionid = vn.versionid
join rofsts rf on rf.rofstid = aa.rofstid
where rf.rodbid <> dru.rodbid
group by vn.versionid, ParentName,FolderName, aa.rofstid, rf.rodbid, dru.rodbid
GO*/