Going Live with ILLiad

--The following query will permanently delete test records that are created 
--before going live. This should only be run when going live! You will need 
--to fill out the parameters below so that they are populated in the queries. 
--This query needs to be run using SQL Management Studio against your ILLiad 
--database. If you are hosted by OCLC or Atlas, contact them and they will 
--run this query for you.
 
 
DECLARE @parentSite nvarchar(10);
DECLARE @goLiveLending bit;
DECLARE @goLiveBorrowing bit;
 
 
--Fill Out the VALUES for the Parent Site (@parentsite), If a single server, 
--the parent site is ILL. Set the GoLive to 1 if you want to go live with 
--the specific process type.
 
 
-- EDIT THESE VALUES BEFORE RUNNING SCRIPT --
SET @parentsite = '';
SET @goLiveLending = 0; --To go live with lending, use SET @goLiveLending = 1;
SET @goLiveBorrowing = 0; --To go live borrowing, use SET @goLiveBorrowing = 1;
 
 
-- DO NOT EDIT BELOW THIS LINE --

IF (@goLiveBorrowing = 0 AND @goLiveLending = 0)
BEGIN
	RAISERROR ('WARNING: Please indicate if you are going live with borrowing, 
	lending, or both.',
		18,
		10
		);
	RETURN;
END

IF @parentSite = ''
BEGIN
	RAISERROR ('WARNING: Please set the Parent Site parameter before running 
	this script.',
		18,
		10
		);
	RETURN;
END


--Get ChildSites
DECLARE @sharedServer bit;
IF EXISTS(SELECT * FROM Customization WHERE CustKey = 'SharedServerSupport' 
	AND Value = 'Yes' AND NVTGC = 'ILL')
BEGIN
	SET @sharedServer = 1
END
ELSE
BEGIN
	SET @sharedServer = 0;
END
DECLARE @allChildSites nvarchar(MAX);
DECLARE @sql NVARCHAR(max);
DECLARE @usersTable NVARCHAR(10);
IF (@sharedServer = 1)
BEGIN	
	SET @usersTable = 'UsersALL';
	SET @sql = 'SELECT @allChildSites = COALESCE(@allChildSites, '''''''') 
	+ NVTGC + '''''', '''''' FROM (SELECT DISTINCT NVTGC FROM 
	[' + @parentSite + '].Users) U';
	EXECUTE sp_executesql @sql, N'@allChildSites NVARCHAR(MAX) OUTPUT', 
	@allChildSites = @allChildSites OUTPUT
END
ELSE
BEGIN
	SET @usersTable = 'Users';
	SET @sql = 'SELECT @allChildSites = COALESCE(@allChildSites, '''''''') + 
	NVTGC + '''''', '''''' FROM (SELECT DISTINCT NVTGC FROM [dbo].Users) U';
	EXECUTE sp_executesql @sql, N'@allChildSites NVARCHAR(MAX) OUTPUT', 
	@allChildSites = @allChildSites OUTPUT	
END
SET @allChildSites = LEFT(@allChildSites, LEN(@allChildSites) - 2);
PRINT 'Child Sites: ' + @allChildSites;
 
 
--Delete Transaction Records that belong to the site that is going live are 
--not in Users
IF @goLiveBorrowing = 1 
BEGIN
	PRINT 'Removing Borrowing Transactions';	
	EXECUTE ('DELETE FROM Transactions WHERE ProcessType IN (N''Borrowing'', 
	N''Doc Del'') AND Username IN (SELECT Username FROM UsersALL WHERE NVTGC 
	IN ('+@allChildSites+'))');
END
IF @goLiveLending = 1 
BEGIN
	PRINT 'Removing Lending Transactions';
	EXECUTE ('DELETE FROM Transactions WHERE ProcessType = N''Lending'' 
	AND Username IN ('+@allChildSites+')');
END
 
 
--Delete User and associated records if going live with borrowing
if @goLiveBorrowing = 1
BEGIN
	--Delete User Records that belong to the site that is going live
	PRINT 'Removing Users';
	EXECUTE ('DELETE FROM ' + @usersTable + ' WHERE NVTGC IN 
	('+@allChildSites+') 	AND UserName NOT IN (' + @allChildSites + ')');	
	
	--Delete UserNotification Records that are not in Users
	PRINT 'Removing UserNotifications';
	EXECUTE('MERGE [dbo].[UserNotifications] UN USING (SELECT Username 
	FROM [dbo].[UserNotifications] 
		EXCEPT SELECT Username FROM ' + @usersTable + ') U on UN.Username = 
		U.Username WHEN MATCHED THEN DELETE;');
	
	--Delete UserAccountsLink Records that are not in Users
	PRINT 'Removing UserAccountsLink';
	EXECUTE ('MERGE [dbo].[UserAccountsLink] UAL USING (SELECT Username 
	FROM [dbo].[UserAccountsLink] 
		EXCEPT SELECT Username FROM  ' + @usersTable + ') U on UAL.Username = 
		U.Username WHEN MATCHED THEN DELETE;');
	
	--Delete UserNotes Records that are not in Users
	PRINT 'Removing UserNotes';
	EXECUTE('MERGE [dbo].[UserNotes] UN USING (SELECT Username FROM 
	[dbo].[UserNotes] 
		EXCEPT SELECT Username FROM  ' + @usersTable + ') U on UN.Username = 
		U.Username WHEN MATCHED THEN DELETE;');
	
	--Delete UserAccounts Records that are not in UserAccountsLink
	PRINT 'Removing UserAccounts';
	EXECUTE('MERGE [dbo].[UserAccounts] UA USING (SELECT InternalNo FROM 
	[dbo].[UserAccounts] 
		EXCEPT SELECT InternalNo FROM UserAccountsLink) UAL on UA.InternalNo = 
		UAL.InternalNo WHEN MATCHED THEN DELETE;');
END
 
 
--Delete all ESPUpdate records if a matching transaction number is not found
PRINT 'Removing Invalid ESPUpdate Records';
MERGE [dbo].[ESPUpdate] UA USING (SELECT TransactionNumber FROM [dbo].
[ESPUpdate] 
	EXCEPT SELECT 	TransactionNumber FROM Transactions) T on 
	UA.TransactionNumber = T.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all Citation records if a matching transaction number is not found
PRINT 'Removing Citations';
MERGE [dbo].[Citation] C USING (SELECT TransactionNumber FROM [dbo].[Citation] 
	EXCEPT SELECT 	TransactionNumber FROM Transactions) T on 
	C.TransactionNumber = T.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all Billing records if a matching transaction number is not found
PRINT 'Removing Billing entries';
MERGE [dbo].[Billing] T USING (SELECT TransactionNumber FROM [dbo].[Billing] 
	EXCEPT SELECT 	TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;
PRINT 'Removing Billing Details';
MERGE [dbo].[BillingDetails] T USING (SELECT TransactionNumber FROM 
[dbo].[BillingDetails] 
	EXCEPT SELECT TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete Borrowing Request Data
PRINT 'Removing BorrowingRequest data';
MERGE [dbo].[BorrowingRequests] T USING (SELECT TransactionNumber FROM 
[dbo].[BorrowingRequests] 
	EXCEPT SELECT TransactionNumber FROM Transactions WHERE ProcessType IN 
	(N'Borrowing',N'Doc Del')) S on T.TransactionNumber = S.TransactionNumber 
	WHEN MATCHED THEN DELETE;

--Delete Lending Request Data
PRINT 'Removing LendingRequest data';
MERGE [dbo].[LendingRequests] T USING (SELECT TransactionNumber FROM 
[dbo].[LendingRequests] 
	EXCEPT SELECT TransactionNumber FROM Transactions WHERE ProcessType = 
	N'Lending') S on T.TransactionNumber = S.TransactionNumber WHEN MATCHED 
	THEN DELETE;

--Delete all History records if a matching transaction number is not found
PRINT 'Removing History';
MERGE [dbo].[History] C USING (SELECT TransactionNumber FROM [dbo].[History] 
	EXCEPT SELECT 	TransactionNumber FROM Transactions) T on 
	C.TransactionNumber = T.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all Tracking records if a matching transaction number is not found
PRINT 'Removing Tracking';
MERGE [dbo].[Tracking] C USING (SELECT TransactionNumber FROM 
[dbo].[Tracking] 
	EXCEPT SELECT TransactionNumber FROM Transactions) T on 
	C.TransactionNumber = T.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all Notes records if a matching transaction number is not found
PRINT 'Removing Notes';
MERGE [dbo].[Notes] T USING (SELECT TransactionNumber FROM [dbo].[Notes] 
	EXCEPT SELECT TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all Resubmission records if a matching transaction number is 
--not found
PRINT 'Removing Resubmission Entries';
MERGE [dbo].[Resubmission] T USING (SELECT TransactionNumber FROM 
[dbo].[Resubmission] 
	EXCEPT SELECT TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all Copyright records if a matching transaction number is 
--not found
PRINT 'Removing Copyright Entries';
MERGE [dbo].[CopyrightSessions] T USING (SELECT TransactionNumber FROM 
[dbo].[CopyrightSessions] 
	EXCEPT SELECT TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all ExternalRequests if a matching transaction number is 
--not found
PRINT 'Removing ExternalRequest Entries';
MERGE [dbo].[ExternalRequests] T USING (SELECT TransactionNumber FROM 
[dbo].[ExternalRequests] 
	EXCEPT SELECT TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all OCLCCollectionData if a matching transaction number is 
--not found
PRINT 'Removing OCLCCollectionData Entries';
MERGE [dbo].[OCLCCollectionData] T USING (SELECT TransactionNumber FROM 
[dbo].[OCLCCollectionData] 
	EXCEPT SELECT TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all OCLCLocalHoldings if a matching transaction number is 
--not found
PRINT 'Removing OCLCLocalHoldings Entries';
MERGE [dbo].[OCLCLocalHoldings] T USING (SELECT TransactionNumber FROM 
[dbo].[OCLCLocalHoldings] 
	EXCEPT SELECT TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all OCLCDetailedHoldings if a matching LocalHolding is not found
PRINT 'Removing OCLCDetailedHoldings Entries';
MERGE [dbo].[OCLCDetailedHoldings] T USING (SELECT LocalHoldingsID FROM 
[dbo].[OCLCDetailedHoldings] 
	EXCEPT SELECT ID FROM OCLCLocalHoldings) S on T.LocalHoldingsID = 
	S.LocalHoldingsID WHEN MATCHED THEN DELETE;

--Delete all OriginalRequest records if a matching transaction number 
--is not found
PRINT 'Removing OriginalRequest data';
MERGE [dbo].[OriginalRequest] T USING (SELECT TransactionNumber FROM 
[dbo].[OriginalRequest] 
	EXCEPT SELECT TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all ReasonsForNoHistory records if a matching transaction 
--number is not found
PRINT 'Removing ReasonsForNoHistory data';
MERGE [dbo].[ReasonForNoHistory] T USING (SELECT TransactionNumber FROM 
[dbo].[ReasonForNoHistory] 
	EXCEPT SELECT TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all ISOBillingInfo records if a matching transaction number 
--is not found
PRINT 'Removing ISOBillingInfo';
MERGE [dbo].[ISOBillingInfo] T USING (SELECT TransactionNumber FROM 
[dbo].[ISOBillingInfo] 
	EXCEPT SELECT TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all ISOControl records if a matching transaction number 
--is not found
PRINT 'Removing ISOControl data';
MERGE [dbo].[ISOControl] T USING (SELECT TransactionNumber FROM 
[dbo].[ISOControl] 
	EXCEPT SELECT TransactionNumber FROM Transactions) S on 
	T.TransactionNumber = S.TransactionNumber WHEN MATCHED THEN DELETE;

--Delete all ISOExchange records if a matching ISONumber is not found
PRINT 'Removing ISOExchange data';
MERGE [dbo].[ISOExchange] T USING (SELECT ISONumber FROM 
[dbo].[ISOExchange] 
	EXCEPT SELECT ISONumber FROM ISOControl) S on T.ISONumber = 
	S.ISONumber WHEN MATCHED THEN DELETE;

--Delete EmailCopies Records for users that were removed
PRINT 'Removing EmailCopies for users';
EXECUTE('DELETE FROM EmailCopies WHERE EmailType = N''User'' AND 
EmailReference NOT IN (SELECT Username FROM ' + @usersTable + ')')	

--Delete EmailCopies Records for transactions that were removed
PRINT 'Removing Users for transactions';
DELETE FROM EmailCopies WHERE EmailType = N'Transaction' AND 
TransactionNumber NOT IN (SELECT TransactionNumber FROM Transactions);	

--Delete SMSCopies Records for users that were removed
PRINT 'Removing SMSCopies for users';
EXECUTE('DELETE FROM SMSCopies WHERE ReferenceType = N''User'' AND 
Reference NOT IN (SELECT Username FROM ' + @usersTable + ')')

--Delete SMSCopies Records for transactions that were removed
PRINT 'Removing SMSCopies for transactions';
DELETE FROM SMSCopies WHERE ReferenceType = N'Transaction' AND 
Reference NOT IN (SELECT CAST(TransactionNumber as NVARCHAR) FROM 
Transactions);

use ILLData
go

delete from Billing 
delete from BillingDetails 
delete from BorrowingRequests 
delete from Citation 
delete from EmailCopies 
delete from ESPUpdate
delete from EventLog 
delete from History 
delete from Invoices 
delete from LendingInvoices 
delete from LendingRequests 
delete from Notes
delete from OriginalRequest 
delete from Resubmission 
delete from Tracking 
delete from Transactions where TransactionNumber <> 0
delete from UserAccounts 
delete from UserAccountsLink 
delete from UserNotes 
delete from Users where Username <> 'Lending' and Username <> 'Unmatched' and 
	Username not in (select Symbol from DeliveryLocations) 
delete from IsoControl 
delete from IsoExchange 
delete from IsoBillingInfo
go

use ILLData
go

delete from Users where Username <> 'Lending' and Username <> 'Unmatched' 
	and Username not in (select Symbol from DeliveryLocations)
go

delete from ESPUpdate where ProcessType <> 'Lending'
go

update Transactions set SpecIns = Username, Username = 'BeforeLiveTest', 
	ProcessType = 'TestBorrow' where ProcessType = 'Borrowing'
go

delete from UserAccounts
go

delete from UserAccountsLink
go

delete from UserNotes
go

use ILLData
go

update Transactions
set ProcessType = 'TestXYZ', SpecIns = UserName, Username = 
	'BeforeLiveTest', TransactionStatus = 'Cancelled by ILL Staff'
where ProcessType = 'Borrowing' and Username in (select username 
	from UsersALL where NVTGC = 'XYZ')
go

use ILLData go delete from ESPUpdate where TransactionNumber in 
	(select TransactionNumber from Transactions where ProcessType = 'Lending')
go

update Transactions set SpecIns = Username, Username = 'BeforeLiveTest', 
	ProcessType = 'TestLend' where ProcessType = 'Lending'
go

use ILLData go delete from ESPUpdate where TransactionNumber in 
	(select TransactionNumber from Transactions where ProcessType = 'Lending' 
	and Username = 'XYZ')
go

update Transactions set SpecIns = Username, Username = 'BeforeLiveTest', 
	ProcessType = 'TestLendXYZ' where ProcessType = 'Lending' and Username = 
	'XYZ'
go