/* Appendix A - Create the transaction archive */

CREATE DATABASE [ILLIAD_TRANSACTION_ARCHIVE]   
GO

USE ILLIAD_TRANSACTION_ARCHIVE
CREATE TABLE [ReasonsForCancellation] (
	[ReasonNumber] int NOT NULL ,
	[NVTGC] varchar (20)  NOT NULL ,
	[Reason] varchar (150)  NULL ,
	[DefaultNote] varchar (255)  NULL ,
	CONSTRAINT [ReasonNVTGC4_PK] PRIMARY KEY  CLUSTERED 
	(
		[ReasonNumber],
		[NVTGC]
	)  ON [PRIMARY] 
) ON [PRIMARY]


CREATE TABLE [LendingReasonsForCancellation] (
	[ReasonNumber] int NOT NULL ,
	[NVTGC] varchar (20)  NOT NULL ,
	[Reason] varchar (150)  NULL ,
	[DefaultNote] varchar (255)  NULL ,
	[OCLCCode] varchar (50)  NULL ,
	[DoclineCode] varchar (50)  NULL ,
	[RLINCode] varchar (50)  NULL ,
	CONSTRAINT [LReasonNVTGC_PK] PRIMARY KEY  CLUSTERED 
	(
		[ReasonNumber],
		[NVTGC]
	)  ON [PRIMARY] 
) ON [PRIMARY]


CREATE TABLE [Tracking] (
	[TransactionNumber] [float] NOT NULL ,
	[DateTime] datetime NOT NULL ,
	[ChangedTo] varchar (40)  NOT NULL ,
	[ChangedBy] varchar (50)  NULL ,
	CONSTRAINT [PK_Tracking_1__14] PRIMARY KEY  CLUSTERED 
	(
		[TransactionNumber],
		[DateTime],
		[ChangedTo]
	)  ON [PRIMARY] 
) ON [PRIMARY]




CREATE TABLE [History] (
	[TransactionNumber] int NOT NULL ,
	[DateTime] datetime NOT NULL ,
	[Entry] varchar (250)  NOT NULL ,
	[Username] varchar (50)  NULL ,
	CONSTRAINT [PK_History] PRIMARY KEY  NONCLUSTERED 
	(
		[TransactionNumber],
		[DateTime],
		[Entry]
	)  ON [PRIMARY] 
) ON [PRIMARY]


CREATE TABLE [Notes] (
	[TransactionNumber] float NOT NULL ,
	[NoteDate] datetime NOT NULL ,
	[Note] varchar (800) NOT NULL ,
	[AddedBy] varchar (20)  NULL ,
	[NoteType] varchar (50) NULL CONSTRAINT [DF_Notes_NoteType] DEFAULT ('User'),
	CONSTRAINT [PK_Notes_1__12] PRIMARY KEY  CLUSTERED 
	(
		[TransactionNumber],
		[NoteDate],
		[Note]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


CREATE TABLE [Transactions] (
	[TransactionNumber] int NOT NULL ,
	[DateArchived] datetime NULL ,
	[Status] varchar (15)  NULL ,
	[Department] varchar (255)  NULL ,
	[PickupLocation] varchar (20) NULL ,
	[RequestType] varchar (8)  NULL ,
	[LoanAuthor] varchar (100)  NULL ,
	[LoanTitle] varchar (255)  NULL ,
	[LoanPublisher] varchar (50)  NULL ,
	[LoanPlace] varchar (30)  NULL ,
	[LoanDate] varchar (30)  NULL ,
	[LoanEdition] varchar (30)  NULL ,
	[PhotoJournalTitle] varchar (255)  NULL ,
	[PhotoJournalVolume] varchar (30)  NULL ,
	[PhotoJournalIssue] varchar (30)  NULL ,
	[PhotoJournalMonth] varchar (30)  NULL ,
	[PhotoJournalYear] varchar (30)  NULL ,
	[PhotoJournalInclusivePages] varchar (30)  NULL ,
	[PhotoArticleAuthor] varchar (100)  NULL ,
	[PhotoArticleTitle] varchar (250)  NULL ,
	[CitedIn] varchar (40)  NULL ,
	[NotWantedAfter] varchar (40)  NULL ,
	[TransactionStatus] varchar (40)  NULL ,
	[TransactionDate] datetime NULL ,
	[ISSN] varchar (20)  NULL ,
	[ILLNumber] varchar (32)  NULL ,
	[ESPNumber] varchar (32)  NULL ,
	[LendingString] varchar (150)  NULL ,
	[BaseFee] [money] NULL ,
	[PerPage] [money] NULL ,
	[Pages] int NULL ,
	[DueDate] datetime NULL ,
	[RenewalsAllowed] varchar (3)  NULL ,
	[SpecIns] varchar (40)  NULL ,
	[Pieces] int NULL ,
	[LibraryUseOnly] varchar (3)  NULL ,
	[AllowPhotocopies] varchar (3)  NULL ,
	[LendingLibrary] varchar (16)  NULL ,
	[ReasonForCancellation] varchar (100)  NULL ,
	[CallNumber] varchar (100)  NULL ,
	[Location] varchar (255)  NULL ,
	[Maxcost] varchar (50)  NULL ,
	[ProcessType] varchar (10)  NULL ,
	[ItemNumber] varchar (10)  NULL ,
	[LenderAddressNumber] [float] NULL ,
	[Ariel] varchar (3)  NULL ,
	[PhotoItemAuthor] varchar (100)  NULL ,
	[PhotoItemPlace] varchar (40)  NULL ,
	[PhotoItemPublisher] varchar (40)  NULL ,
	[PhotoItemEdition] varchar (40)  NULL ,
	[DocumentType] varchar (15)  NULL ,
	[InternalAcctNo] [float] NULL ,
	[PriorityShipping] varchar (3)  NULL ,
	[Rush] varchar (30)  NULL ,
	[WantedBy] varchar (25)  NULL ,
	[SystemID] varchar (32)  NULL ,
	[IFMCost] varchar (30)  NULL ,
	[ShippingOptions] varchar (50)  NULL ,
	[LendingChecksReceived] varchar (50)  NULL ,
	[ReferenceNumber] varchar (50)  NULL ,
	[CopyrightComp] varchar (3)  NULL ,
	[TAddress] varchar (100)  NULL ,
	[ReceivedVia] varchar (20)  NULL ,
	[CancellationCode] varchar (50)  NULL ,
	[CCSelected] varchar (3)  NULL ,
	[OriginalTN] int NULL ,
	[OriginalNVTGC] varchar (20)  NULL ,
	[InProcessDate] varchar (8)  NULL ,
	[InvoiceNumber] int NULL ,
	[BorrowerTN] int NULL ,
	[WebRequestForm] varchar (100)  NULL ,
	[TName] varchar (100)  NULL ,
	[IFMPaid] varchar (3)  NULL ,
	[BillingAmount] varchar (15)  NULL ,
	[ConnectorErrorStatus] varchar (50)  NULL ,
	[BorrowerNVTGC] varchar (20)  NULL ,
	[TISOPaymentMethod] varchar (10)  NULL ,
	[CCCOrder] varchar (3)  NULL ,
	[ISOStatus] varchar (50)  NULL ,
	[ShippingDetail] varchar (50)  NULL ,
	[OdysseyErrorStatus] varchar (50)  NULL ,
	[WorldCatLCNumber] varchar (50)  NULL ,
	[Locations] varchar (255)  NULL ,
	CONSTRAINT [PK_Transactions] PRIMARY KEY  NONCLUSTERED 
	(
		[TransactionNumber]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO



CREATE  UNIQUE  INDEX [byTransactionNumber] ON [Transactions]([TransactionNumber]) ON [PRIMARY]
GO




/* Appendix B - Select records from Transactions table */

SELECT  t.TransactionNumber, 
	GETDATE()as DateArchived,  
	t.Username,u.Status,
	u.Department,u.NVTGC,     
	t.RequestType,    
	t.LoanAuthor, t.LoanTitle,    
	t.LoanPublisher,t.LoanPlace,    
	t.LoanDate,t.LoanEdition,    
	t.PhotoJournalTitle,t.PhotoJournalVolume,    
	t.PhotoJournalIssue,t.PhotoJournalMonth,    
	t.PhotoJournalYear,t.PhotoJournalInclusivePages,    
	t.PhotoArticleAuthor,t.PhotoArticleTitle,    
	t.CitedIn,t.NotWantedAfter,       
	t.TransactionStatus,t.TransactionDate,  
	t.ISSN,t.ILLNumber,    
	t.ESPNumber,t.LendingString,    
	t.BaseFee,t.PerPage,t.Pages,  
	t.DueDate,t.RenewalsAllowed,    
	t.SpecIns,t.Pieces,  
	t.LibraryUseOnly,t.AllowPhotocopies,    
	t.LendingLibrary,t.ReasonForCancellation,    
	t.CallNumber,t.Location,t.Maxcost,    
	t.ProcessType,t.ItemNumber,    
	t.LenderAddressNumber,t.Ariel,       
	t.PhotoItemAuthor,t.PhotoItemPlace,    
	t.PhotoItemPublisher,t.PhotoItemEdition,    
	t.DocumentType,t.InternalAcctNo,  
	t.PriorityShipping,t.Rush,t.WantedBy,    
	t.SystemID, t.IFMCost,t.ShippingOptions,    
	t.CCCNumber,t.ReferenceNumber,    
	t.CopyrightComp,t.TAddress,         
	t.ReceivedVia,t.CancellationCode,       
	t.CCSelected,t.OriginalTN,t.OriginalNVTGC,    
	t.InProcessDate,t.InvoiceNumber,  
	t.BorrowerTN,t.WebRequestForm,    
	t.TName,t.IFMPaid,t.BillingAmount,    
	t.ConnectorErrorStatus,t.BorrowerNVTGC,    
	t.TISOPaymentMethod,t.CCCOrder,    
	t.ISOStatus,t.ShippingDetail,    
	t.OdysseyErrorStatus,t.WorldCatLCNumber,    
	t.Locations
/* FROM Transactions t INNER JOIN [Users-KKU] u */
FROM Transactions t INNER JOIN Users u
ON t.Username = u.Username
WHERE t.TransactionDate < '06/01/2006' 
AND (t.TransactionStatus='Request Finished'
OR t.TransactionStatus='Cancelled by ILL Staff')




/* Appendix C - Select records from Notes table
Code to select records from Notes table
Notes: This select uses the TransactionDate from 
the Transactions table because that is the date used to
select records from the Transactions table for the archive. 

The first select retrieves records with patron information based on
the field NoteType=?User? and replaces the username with the text 
"Patron". The second select retrieves everything else as is.

For comparison, this is the code to select all, regardless of NoteType:
SELECT n.* from Notes n  INNER JOIN Transactions t
ON n.TransactionNumber=t.TransactionNumber
INNER JOIN [Users-KKU] u 
ON t.Username=u.Username
WHERE EXISTS (SELECT t.Username FROM [Users-KKU])
AND (TransactionStatus='Request Finished'
OR TransactionStatus='Cancelled by ILL Staff')
AND t.TransactionDate < '07/01/2006'
ORDER BY n.NoteType
*/


SELECT n.TransactionNumber, n.NoteDate, n.Note, 'Patron' as AddedBy, n.NoteType
FROM Notes n INNER JOIN Transactions t
ON n.TransactionNumber=t.TransactionNumber 
WHERE n.NoteType ='User' 
AND (TransactionStatus='Request Finished'
OR TransactionStatus='Cancelled by ILL Staff')
AND t.TransactionDate < '07/01/2006'

SELECT n.* from Notes n INNER JOIN Transactions t
ON n.TransactionNumber=t.TransactionNumber
WHERE n.NoteType !='User' 
AND (TransactionStatus='Request Finished'
OR TransactionStatus='Cancelled by ILL Staff')
AND t.TransactionDate < '07/01/2006'




/* Appendix D - Select records from History table 
Code to select records from History table
Notes: This select uses the TransactionDate from 
the Transactions table because that is the date used to
select records from the Transactions table for the archive. 

The first select retrieves records with patron information and
replaces Username with the text "Patron". The second select
retrieves all records with staff  usernames from the Staff table 
or system information.

For comparison, this is the code to select all, regardless of Username:
SELECT h.* 
FROM History h INNER JOIN Transactions t
ON h.TransactionNumber=t.TransactionNumber
INNER JOIN [Users-KKU] u 
ON t.Username=u.Username
WHERE EXISTS (SELECT t.username FROM [Users-KKU])
AND (TransactionStatus='Request Finished'
OR TransactionStatus='Cancelled by ILL Staff')
AND t.TransactionDate < '07/01/2006'
ORDER BY h.Username
*/

SELECT h.TransactionNumber, h.DateTime, h.Entry, 'Patron' as Username
FROM History h INNER JOIN Transactions t
ON h.TransactionNumber=t.TransactionNumber 
WHERE h.Username !='System' 
AND h.Username !='Odyssey'
AND h.Username NOT IN (SELECT username FROM Staff)
AND (TransactionStatus='Request Finished'
OR TransactionStatus='Cancelled by ILL Staff')
AND t.TransactionDate < '07/01/2006'

SELECT h.* from History h INNER JOIN Transactions t
ON h.TransactionNumber=t.TransactionNumber
WHERE  (h.username='System' 
OR h.Username='Odyssey'
OR h.Username in (SELECT username FROM Staff))
AND (TransactionStatus='Request Finished'
OR TransactionStatus='Cancelled by ILL Staff')
AND t.TransactionDate < '07/01/2006'




/* Appendix E - Select records from Tracking table 
Code to select records from Tracking table
Notes: This select uses the TransactionDate from 
the Transactions table because that is the date used to
select records from the Transactions table for the archive. 

The first select retrieves records with patron information and
replaces ChangedBy with the text "Patron". The second select
retrieves all records with staff  usernames from the Staff table 
or system information.

For comparison, this is the code to select all, regardless of ChangedBy:
SELECT k.* 
FROM Tracking k INNER JOIN Transactions t
ON k.TransactionNumber=t.TransactionNumber 
INNER JOIN [Users-KKU] u on
t.Username=u.Username
WHERE EXISTS (SELECT t.Username FROM [Users-KKU])
AND t.TransactionDate < '06/01/2006' 
AND (t.TransactionStatus='Request Finished'
OR t.TransactionStatus='Cancelled by ILL Staff')
ORDER BY k.ChangedBy
*/

SELECT k.TransactionNumber, k.DateTime, k.ChangedTo, 'Patron' as Username
FROM Tracking k INNER JOIN Transactions t
ON k.TransactionNumber=t.TransactionNumber 
WHERE t.TransactionDate < '06/01/2006' 
AND (t.TransactionStatus='Request Finished'
OR t.TransactionStatus='Cancelled by ILL Staff')
AND k.ChangedBy !='System' 
AND k.ChangedBy !='Odyssey'
AND k.ChangedBy NOT IN (SELECT Username FROM Staff)

SELECT k.*  
FROM Tracking k INNER JOIN Transactions t
ON k.TransactionNumber=t.TransactionNumber 
WHERE t.TransactionDate < '06/01/2006' 
AND (t.TransactionStatus='Request Finished'
OR t.TransactionStatus='Cancelled by ILL Staff')
AND (k.ChangedBy ='System' 
OR k.ChangedBy ='Odyssey'
OR k.ChangedBy IN (SELECT Username FROM Staff))




/* Appendix F - Delete records from Transactions and related tables 
Notes: Records from the Notes, History and Tracking table must be deleted
before records from the Transactions table are deleted. 

The inner join syntax used is not ANSI SQL compliant but is correct for
Transact SQL.

Use the same date criteria from the select statements used
to populate the archive.

Additional criteria for KU has been removed after the first join:
INNER JOIN [Users-KKU] u on
t.Username=u.Username
WHERE EXISTS (SELECT t.Username FROM [Users-KKU])
AND
remaining criteria here


DELETE FROM Notes 
FROM Notes n INNER JOIN Transactions t
ON n.TransactionNumber=t.TransactionNumber
WHERE (TransactionStatus='Request Finished'
OR TransactionStatus='Cancelled by ILL Staff')
AND t.TransactionDate < '07/01/2006'

DELETE FROM History 
FROM History h INNER JOIN Transactions t
ON h.TransactionNumber=t.TransactionNumber
WHERE (TransactionStatus='Request Finished'
OR TransactionStatus='Cancelled by ILL Staff')
AND t.TransactionDate < '07/01/2006'

DELETE FROM Tracking 
FROM Tracking k INNER JOIN Transactions t
ON k.TransactionNumber=t.TransactionNumber 
WHERE t.TransactionDate < '06/01/2006' 
AND (t.TransactionStatus='Request Finished'
OR t.TransactionStatus='Cancelled by ILL Staff')

DELETE FROM Transactions 
WHERE TransactionDate < '06/01/2006' 
AND (TransactionStatus='Request Finished'
OR TransactionStatus='Cancelled by ILL Staff')
*/