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

   1 BEGIN;
   2
   3 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
   4         SELECT  xact.id AS id,
   5                 xact.usr AS usr,
   6                 COALESCE(circ.circ_lib,groc.billing_location) AS billing_location,
   7                 xact.xact_start AS xact_start,
   8                 xact.xact_finish AS xact_finish,
   9                 SUM(credit.amount) AS total_paid,
  10                 MAX(credit.payment_ts) AS last_payment_ts,
  11                 LAST(credit.note) AS last_payment_note,
  12                 LAST(credit.payment_type) AS last_payment_type,
  13                 SUM(debit.amount) AS total_owed,
  14                 MAX(debit.billing_ts) AS last_billing_ts,
  15                 LAST(debit.note) AS last_billing_note,
  16                 LAST(debit.billing_type) AS last_billing_type,
  17                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
  18                 p.relname AS xact_type
  19           FROM  money.billable_xact xact
  20                 JOIN pg_class p ON (xact.tableoid = p.oid)
  21                 LEFT JOIN "action".circulation circ ON (circ.id = xact.id)
  22                 LEFT JOIN money.grocery groc ON (groc.id = xact.id)
  23                 LEFT JOIN (
  24                         SELECT  billing.xact,
  25                                 billing.voided,
  26                                 sum(billing.amount) AS amount,
  27                                 max(billing.billing_ts) AS billing_ts,
  28                                 last(billing.note) AS note,
  29                                 last(billing.billing_type) AS billing_type
  30                           FROM  money.billing
  31                           WHERE billing.voided IS FALSE
  32                           GROUP BY billing.xact, billing.voided
  33                 ) debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
  34                 LEFT JOIN (
  35                         SELECT  payment_view.xact,
  36                                 payment_view.voided,
  37                                 sum(payment_view.amount) AS amount,
  38                                 max(payment_view.payment_ts) AS payment_ts,
  39                                 last(payment_view.note) AS note,
  40                                 last(payment_view.payment_type) AS payment_type
  41                           FROM  money.payment_view
  42                           WHERE payment_view.voided IS FALSE
  43                           GROUP BY payment_view.xact, payment_view.voided
  44                 ) credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
  45           WHERE xact.xact_finish IS NULL
  46           GROUP BY 1,2,3,4,5,15
  47           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
  48
  49
  50 CREATE OR REPLACE VIEW money.open_usr_summary AS
  51         SELECT  usr,
  52                 SUM(total_paid) AS total_paid,
  53                 SUM(total_owed) AS total_owed,
  54                 SUM(balance_owed) AS balance_owed
  55           FROM money.open_billable_xact_summary
  56           GROUP BY 1;
  57
  58 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
  59         SELECT  usr,
  60                 SUM(total_paid) AS total_paid,
  61                 SUM(total_owed) AS total_owed,
  62                 SUM(balance_owed) AS balance_owed
  63           FROM  money.open_billable_xact_summary
  64           WHERE xact_type = 'circulation'
  65           GROUP BY 1;
  66
  67 COMMIT;
  68