Open-ILS/src/sql/Pg/500.view.cross-schema.sql

   1 /*
   2  * Copyright (C) 2004-2008  Georgia Public Library Service
   3  * Copyright (C) 2007-2008  Equinox Software, Inc.
   4  * Mike Rylander <miker@esilibrary.com>
   5  *
   6  * This program is free software; you can redistribute it and/or
   7  * modify it under the terms of the GNU General Public License
   8  * as published by the Free Software Foundation; either version 2
   9  * of the License, or (at your option) any later version.
  10  *
  11  * This program is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14  * GNU General Public License for more details.
  15  *
  16  */
  17
  18 BEGIN;
  19
  20 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
  21         SELECT  xact.id AS id,
  22                 xact.usr AS usr,
  23                 COALESCE(circ.circ_lib,groc.billing_location) AS billing_location,
  24                 xact.xact_start AS xact_start,
  25                 xact.xact_finish AS xact_finish,
  26                 SUM(credit.amount) AS total_paid,
  27                 MAX(credit.payment_ts) AS last_payment_ts,
  28                 LAST(credit.note) AS last_payment_note,
  29                 LAST(credit.payment_type) AS last_payment_type,
  30                 SUM(debit.amount) AS total_owed,
  31                 MAX(debit.billing_ts) AS last_billing_ts,
  32                 LAST(debit.note) AS last_billing_note,
  33                 LAST(debit.billing_type) AS last_billing_type,
  34                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
  35                 p.relname AS xact_type
  36           FROM  money.billable_xact xact
  37                 JOIN pg_class p ON (xact.tableoid = p.oid)
  38                 LEFT JOIN "action".circulation circ ON (circ.id = xact.id)
  39                 LEFT JOIN money.grocery groc ON (groc.id = xact.id)
  40                 LEFT JOIN (
  41                         SELECT  billing.xact,
  42                                 billing.voided,
  43                                 sum(billing.amount) AS amount,
  44                                 max(billing.billing_ts) AS billing_ts,
  45                                 last(billing.note) AS note,
  46                                 last(billing.billing_type) AS billing_type
  47                           FROM  money.billing
  48                           WHERE billing.voided IS FALSE
  49                           GROUP BY billing.xact, billing.voided
  50                 ) debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
  51                 LEFT JOIN (
  52                         SELECT  payment_view.xact,
  53                                 payment_view.voided,
  54                                 sum(payment_view.amount) AS amount,
  55                                 max(payment_view.payment_ts) AS payment_ts,
  56                                 last(payment_view.note) AS note,
  57                                 last(payment_view.payment_type) AS payment_type
  58                           FROM  money.payment_view
  59                           WHERE payment_view.voided IS FALSE
  60                           GROUP BY payment_view.xact, payment_view.voided
  61                 ) credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
  62           WHERE xact.xact_finish IS NULL
  63           GROUP BY 1,2,3,4,5,15
  64           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
  65
  66
  67 CREATE OR REPLACE VIEW money.open_usr_summary AS
  68         SELECT  usr,
  69                 SUM(total_paid) AS total_paid,
  70                 SUM(total_owed) AS total_owed,
  71                 SUM(balance_owed) AS balance_owed
  72           FROM money.open_billable_xact_summary
  73           GROUP BY 1;
  74
  75 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
  76         SELECT  usr,
  77                 SUM(total_paid) AS total_paid,
  78                 SUM(total_owed) AS total_owed,
  79                 SUM(balance_owed) AS balance_owed
  80           FROM  money.open_billable_xact_summary
  81           WHERE xact_type = 'circulation'
  82           GROUP BY 1;
  83
  84 COMMIT;
  85