/*
The following query will create the necessary database entries for any new delivery location. You will need to fill out the parameters below so that they are populated in the queries. This query needs to be edited and run for each delivery location code added.
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 @deliloc nvarchar(10), @parentsite nvarchar(10), @delilocdesc nvarchar(50);

/*
Fill Out the values for the Parent Site (@parentsite), 
the Delivery Location code (@deliloc), 
and the Delivery Location description (@delilocdesc). 
If a single server, the parent site is ILL.
*/
-- EDIT THESE VALUES BEFORE RUNNING SCRIPT. --
SET @deliloc = '';
SET @parentsite = '';
SET @delilocdesc = '';



-- DO NOT EDIT BELOW THIS LINE --

-- Insert value into DeliveryLocations table.
insert into DeliveryLocations
(symbol, value)
values
(@deliloc, @delilocdesc);

-- Add new values to Customization for delivery location 
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'ArticleDeliveryNotifyEMailFileName', 'illartdel.txt',@parentsite,'','Borrowing','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'ArticleDeliveryNotifyEMailSubject', 'Requested article has been mailed',@parentsite,'','Borrowing','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'ArticleNotifyEMailFileName', 'illart.txt',@parentsite,'','Borrowing','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'ArticleNotifyEMailSubject', 'Requested article has arrived',@parentsite,'','Borrowing','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'DDArticleDeliveryNotifyEMailFileName', 'illddartdel.txt',@parentsite,'','Doc Del','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'DDArticleDeliveryNotifyEMailSubject', 'Requested article has been mailed',@parentsite,'','Doc Del','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'DDArticleNotifyEMailFileName', 'illddart.txt',@parentsite,'','Doc Del','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'DDArticleNotifyEMailSubject', 'Requested article has arrived',@parentsite,'','Doc Del','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'DDLoanDeliveryNotifyEMailSubject', 'Requested loan has arrived',@parentsite,'','Doc Del','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'DDLoanDeliveryNotifyEMailFileName', 'illddloan.txt',@parentsite,'','Doc Del','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'DDLoanNotifyEMailFileName', 'illddloan.txt',@parentsite,'','Doc Del','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'DDLoanNotifyEMailSubject', 'Requested Loan has arrived',@parentsite,'','Doc Del','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'LoanDeliveryNotifyEMailFileName', 'illloandel.txt',@parentsite,'','Borrowing','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'LoanDeliveryNotifyEMailSubject', 'Requested Loan Mailed',@parentsite,'','Borrowing','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'LoanNotifyEMailFileName', 'illloan.txt',@parentsite,'','Borrowing','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'LoanNotifyEMailSubject', 'Requested loan has arrived',@parentsite,'','Borrowing','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'MDDNotifyEMailFileName', 'ddmnotify.txt',@parentsite,'','Doc Del','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'MDDNotifyEMailSubject', 'Requested Items Processed',@parentsite,'','Doc Del','EMail','StringValue');
insert into Customization
(Custkey,Value,NVTGC,Description,AdminCat,AdminKey,AdminType)
values
(@deliloc+'MDDThreshold', '500',@parentsite,'','Doc Del','EMail','StringValue');
insert into Customization
values
(@deliloc+'MNotifyEMailFileName', 'illmnotify.txt',@parentsite,'','Borrowing','EMail','StringValue');
insert into Customization
values
(@deliloc+'MNotifyEMailSubject', 'Requested items processed',@parentsite,'','Borrowing','EMail','StringValue');
insert into Customization
values
(@deliloc+'MThreshold', '500',@parentsite,'','Borrowing','EMail','StringValue');

-- Copy parent site OpenURLMapping entries for delivery location
insert into OpenURLMapping
select @deliloc, URL_Ver, rfr_id, ILLiadAction, ILLiadFieldName, OpenURLFieldValues, ILLiadValue
from OpenURLMapping where NVTGC = @parentsite;

-- Copy parent site WorkFormMapping entries for delivery location
insert into WorkFormMapping
select @deliloc, WorkFormType, OCLCFieldName, ILLiadFields, ConstantDataAction
from WorkFormMapping where NVTGC = @parentsite;

-- Copy parent site LocalInfo entries for delivery location
INSERT INTO LocalInfo
(NVTGC,SystemName,SystemURL,InstitutionName,LibraryName,OCLCSymbol,DoclineSymbol,RLINSymbol,GeneralContactName,GeneralPhone,GeneralEMailAddress,GeneralAddress1
,GeneralAddress2,GeneralCity,GeneralState,GeneralZip,GeneralCountry,GeneralFax,GeneralHours,BorrowingContactName,BorrowingPhone,BorrowingEMailAddress
,BorrowingAddress1,BorrowingAddress2,BorrowingCity,BorrowingState,BorrowingZip,BorrowingFax,BorrowingHours,BorrowingArielAddress,DocDelContactName,DocDelPhone
,DocDelEMailAddress,DocDelAddress1,DocDelAddress2,DocDelCity,DocDelState,DocDelZip,DocDelFax,DocDelHours,DocDelArielAddress,LendingContactName,LendingPhone
,LendingEMailAddress,LendingAddress1,LendingAddress2,LendingCity,LendingState,LendingZip,LendingFax,LendingHours,LendingArielAddress,BillingContactName
,BillingPhone,BillingEMailAddress,BillingAddress1,BillingAddress2,BillingCity,BillingState,BillingZip,BillingFax,BillingHours)
select @deliloc,SystemName,SystemURL,InstitutionName,LibraryName,OCLCSymbol,DoclineSymbol,RLINSymbol,GeneralContactName,GeneralPhone,GeneralEMailAddress,
GeneralAddress1,GeneralAddress2,GeneralCity,GeneralState,GeneralZip,GeneralCountry,GeneralFax,GeneralHours,BorrowingContactName,BorrowingPhone,
BorrowingEMailAddress,BorrowingAddress1,BorrowingAddress2,BorrowingCity,BorrowingState,BorrowingZip,BorrowingFax,BorrowingHours,BorrowingArielAddress
,DocDelContactName,DocDelPhone,DocDelEMailAddress,DocDelAddress1,DocDelAddress2,DocDelCity,DocDelState,DocDelZip,DocDelFax,DocDelHours,
DocDelArielAddress,LendingContactName,LendingPhone,LendingEMailAddress,LendingAddress1,LendingAddress2,LendingCity,LendingState,LendingZip
,LendingFax,LendingHours,LendingArielAddress,BillingContactName,BillingPhone,BillingEMailAddress,BillingAddress1,BillingAddress2,BillingCity
,BillingState,BillingZip,BillingFax,BillingHours
from LocalInfo where nvtgc = @parentsite;

-- insert entry into CustomDropDown table for the delivery location
insert into CustomDropDown
(NVTGC,GroupNAme,LabelName,LabelValue)
values
(@parentsite, 'NVTGC', @delilocdesc, @deliloc);