-- Inserts only those charges that are open at the time of import

--    Copyright (C) 2009, Dan Scott
--    Thanks to the Robertson Library, UPEI, for funding these scripts. 

--    This program is free software: you can redistribute it and/or modify
--    it under the terms of the GNU General Public License as published by
--    the Free Software Foundation, either version 3 of the License, or
--    (at your option) any later version.
--
--    This program is distributed in the hope that it will be useful,
--    but WITHOUT ANY WARRANTY; without even the implied warranty of
--    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--    GNU General Public License for more details.
--
--    You should have received a copy of the GNU General Public License
--    along with this program.  If not, see <http://www.gnu.org/licenses/>.

-- Assumptions: 
--   1. xact_start = datecharged
--   2. duration_rule, recuring_fine_rule, max_fine_rule = 'default'
--   3. renewal_remaining = 2 
--   4. fine_interval = '1 day' (OVERDUE policy)
--   5. recuring_fine = 0.25 (OVERDUE policy)
--   6. max_fine = 25.00 (OVERDUE policy)
--   7. duration = '21 days' (UNDERGRADUATE loan), '30 days' (GRADUATE) or '120 days' (FACULTY loan)
--   8. circ_staff = 1 (administrator)

INSERT INTO action.circulation (usr, xact_start, target_copy, circ_lib, circ_staff, 
	renewal_remaining, due_date, duration, fine_interval, recuring_fine,
	max_fine, duration_rule, recuring_fine_rule, max_fine_rule)
	SELECT 
		au.id AS usr,
		sc.datecharged AS xact_start,
		ac.id AS target_copy,
		aou.id AS circ_lib,
		1 as circ_staff,
		2 as renewal_remaining,
		sc.datedue as due_date,
		CASE
			WHEN au.profile IN (13, 20) THEN '120 days'::interval
			WHEN au.profile IN (19) THEN '30 days'::interval
			ELSE '21 days'
		END AS duration,
		'1 day'::interval AS fine_interval,
		0.25 AS recuring_fine,
		25.00 AS max_fine,
		'default' AS duration_rule,
		'default' AS recuring_fine_rule,
		'default' AS max_fine_rule
	FROM staging_charges sc
		JOIN asset."copy" ac ON
			(sc.item = ac.barcode)
		JOIN actor.org_unit aou ON
			(sc.library = aou.shortname)
		JOIN actor.card auc ON
			(sc.usr = auc.barcode)
 		JOIN actor.usr au ON
			(auc.id = au.id AND sc.usr = auc.barcode)
;
-- To see the charges in the staging table that won't transfer, run the following queries:
-- select count(*) from staging_charges where item not in (select barcode from asset."copy");
-- select count(*) from staging_charges where usr not in (select barcode from actor.card);

