legacy bill import

git-svn-id: svn://svn.open-ils.org/ILS/trunk@5271 dcc99617-32d9-48b4-a31d-7c20da2025e4

diff --git a/Evergreen/src/extras/import/legacy_bill.sql b/Evergreen/src/extras/import/legacy_bill.sql
new file mode 100644 (file)
index 0000000..33bb810
--- /dev/null
+++ b/Evergreen/src/extras/import/legacy_bill.sql
@@ -0,0 +1,26 @@
+BEGIN;
+
+INSERT INTO money.grocery (usr,xact_start,billing_location,note)
+       SELECT  DISTINCT ON (au.id)
+               au.id AS usr,
+               lb.bill_date AS xact_start,
+               ou.id AS billing_location,
+               'Legacy Open Billing' AS note
+       FROM    legacy_bill lb
+               JOIN actor.usr au ON (lb.user_key = au.id)
+               JOIN actor.org_unit ou ON (lb.library = ou.shortname)
+       WHERE   lb.paid IS FALSE
+       ORDER BY au.id, lb.bill_key2;
+
+INSERT INTO money.billing (xact,billing_ts,amount,billing_type,note)
+       SELECT  mg.id AS xact,
+               lb.bill_date AS billing_ts,
+               (lb.balance / 100.0)::NUMERIC(6,2) AS amount,
+               lb.reason AS billing_type,
+               'TCN of Bib Record: ' || br.tcn_value AS note
+       FROM    legacy_bill lb
+               JOIN money.grocery mg ON (lb.user_key = mg.usr)
+               JOIN actor.usr au ON (lb.user_key = au.id)
+               JOIN actor.org_unit ou ON (lb.library = ou.shortname)
+               LEFT JOIN biblio.record_entry br ON (lb.cat_key = br.id)
+       WHERE   lb.paid IS FALSE;