From 6351c8ea74848ab9089404b549a3dcc472ff419f Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 4 Aug 2006 04:36:12 +0000 Subject: [PATCH] legacy bill import git-svn-id: svn://svn.open-ils.org/ILS/trunk@5271 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Evergreen/src/extras/import/legacy_bill.sql | 26 +++++++++++++++++++++ 1 file changed, 26 insertions(+) create mode 100644 Evergreen/src/extras/import/legacy_bill.sql diff --git a/Evergreen/src/extras/import/legacy_bill.sql b/Evergreen/src/extras/import/legacy_bill.sql new file mode 100644 index 0000000000..33bb810dcc --- /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; -- 2.43.2