-- Load bills into Evergreen from staging table containing Unicorn data

--    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/>.

-- Loaded as "grocery" bills with an explanatory note
-- To run this:
-- 1. Log into psql
-- 2. psql# \i generate_bills.sql
-- 3. psql# SELECT generate_bills();
-- 4. CHECK THE BILLS THAT HAVE BEEN GENERATED TO ENSURE THAT THE DATA IS WHAT YOU EXPECT!
-- If the data is not what you expect, and you have to adjust either the staging table
-- or the generate_bills() function, then issue the following command to roll back all
-- of the changes:
--   psql# ROLLBACK;
-- If the data is correct, then you can commit the changes:
--   psql# COMMIT;

BEGIN;
CREATE FUNCTION generate_bills() RETURNS integer AS $$
DECLARE
	xbill RECORD;
	xusr INTEGER;
	xlibrary INTEGER;
	xreason TEXT;
BEGIN
	FOR xbill IN
		SELECT sb.amount_charged, sb.balance, sb.date_billed, sb.reason, sb.library, sb.usr, sb.item
	    FROM staging_bills sb
		INNER JOIN actor.card ac ON (sb.usr = ac.barcode)
		ORDER BY sb.usr
		LOOP

			SELECT usr INTO xusr FROM actor.card WHERE barcode = xbill.usr;
			SELECT id INTO xlibrary FROM actor.org_unit WHERE shortname = xbill.library;
			xreason = 'Legacy bill for item barcode ' || xbill.item 
			 	|| '. Original amount: ' 
				|| to_char(CAST(xbill.amount_charged AS numeric(6,2))/100, 'L9999D99')
				|| '. Reason: ' || xbill.reason;

			-- First, create the billable transaction
			-- Then, use the billable transaction ID to create a grocery bill
			INSERT INTO money.grocery (usr, billing_location, xact_start, note)
				VALUES (xusr, xlibrary, xbill.date_billed, xreason);
			INSERT INTO money.billing (xact, amount, billing_type, note, billing_ts)
				VALUES (CURRVAL('money.billable_xact_id_seq'), CAST(xbill.balance AS numeric(6,2))/100, xbill.reason, xreason, xbill.date_billed);
			
		END LOOP;
	RETURN 1;
END;
$$ LANGUAGE PLPGSQL;


