From b38d0e89f4513752cfc2da0ca3372de3f23b0680 Mon Sep 17 00:00:00 2001
From: Jason Stephenson <jstephenson@mvlcstaff.org>
Date: Wed, 16 Mar 2016 16:57:00 -0400
Subject: [PATCH] Porting 2.9.2 to 2.9.3 database upgrade script.

Signed-off-by: Jason Stephenson <jstephenson@mvlcstaff.org>
---
 .../Pg/version-upgrade/2.9.2-2.9.3-upgrade-db.sql  | 180 +++++++++++++++++++++
 1 file changed, 180 insertions(+)
 create mode 100644 Open-ILS/src/sql/Pg/version-upgrade/2.9.2-2.9.3-upgrade-db.sql

diff --git a/Open-ILS/src/sql/Pg/version-upgrade/2.9.2-2.9.3-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/2.9.2-2.9.3-upgrade-db.sql
new file mode 100644
index 0000000000..fd9926653d
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/version-upgrade/2.9.2-2.9.3-upgrade-db.sql
@@ -0,0 +1,180 @@
+--Upgrade Script for 2.9.2 to 2.9.3
+\set eg_version '''2.9.3'''
+BEGIN;
+INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.9.3', :eg_version);
+
+SELECT evergreen.upgrade_deps_block_check('0959', :eg_version);
+
+CREATE OR REPLACE VIEW money.transaction_billing_summary AS
+    SELECT id as xact,
+        last_billing_type,
+        last_billing_note,
+        last_billing_ts,
+        total_owed
+      FROM money.materialized_billable_xact_summary;
+
+
+SELECT evergreen.upgrade_deps_block_check('0971', :eg_version);
+
+CREATE OR REPLACE FUNCTION evergreen.protect_reserved_rows_from_delete() RETURNS trigger AS $protect_reserved$
+BEGIN
+IF OLD.id < TG_ARGV[0]::INT THEN
+    RAISE EXCEPTION 'Cannot delete row with reserved ID %', OLD.id; 
+END IF;
+RETURN OLD;
+END
+$protect_reserved$
+LANGUAGE plpgsql;
+
+
+SELECT evergreen.upgrade_deps_block_check('0972', :eg_version); -- jstompro/gmcharlt
+
+-- LP#1550495 - Add Baker&Taylor EDI Quantity Cancel Code
+-- Insert EDI Cancel Reason 85 (1200 + 85 = 1285) if it doesn't already exist
+INSERT INTO acq.cancel_reason 
+   (org_unit, keep_debits, id, label, description)
+   SELECT 
+     1, 'f',( 85+1200),
+     oils_i18n_gettext(1285, 'Canceled: By Vendor', 'acqcr', 'label'),
+     oils_i18n_gettext(1285, 'Line item canceled by vendor', 'acqcr', 'description')
+   WHERE NOT EXISTS (
+    SELECT 1 FROM acq.cancel_reason where id=(85+1200)
+   );
+
+
+
+SELECT evergreen.upgrade_deps_block_check('0973', :eg_version); -- tmccanna/gmcharlt
+
+UPDATE action_trigger.event_definition SET template = 
+$$
+[%- USE date -%]
+[%- SET user = target -%]
+<div>
+    <style> li { padding: 8px; margin 5px; }</style>
+    <div>[% date.format %]</div>
+    <br/>   
+	Fines for:<br/>
+    [% user.family_name %], [% user.first_given_name %]
+    <ol>
+    [% FOR xact IN user.open_billable_transactions_summary %]
+        [% IF xact.balance_owed > 0 %]
+            <li>
+                <div>Details: 
+                    [% IF xact.xact_type == 'circulation' %]
+                        [%- helpers.get_copy_bib_basics(xact.circulation.target_copy).title -%]
+                    [% ELSE %]
+                        [%- xact.last_billing_type -%]
+                    [% END %]
+                </div>
+                <div>Total Billed: [% xact.total_owed %]</div>
+                <div>Total Paid: [% xact.total_paid %]</div>
+                <div>Balance Owed : [% xact.balance_owed %]</div>
+            </li>
+        [% END %]
+    [% END %]
+    </ol>
+</div>
+$$ WHERE id=13
+AND template = 
+$$
+[%- USE date -%]
+[%- SET user = target -%]
+<div>
+    <style> li { padding: 8px; margin 5px; }</style>
+    <div>[% date.format %]</div>
+    <br/>
+
+    [% user.family_name %], [% user.first_given_name %]
+    <ol>
+    [% FOR xact IN user.open_billable_transactions_summary %]
+        <li>
+            <div>Details: 
+                [% IF xact.xact_type == 'circulation' %]
+                    [%- helpers.get_copy_bib_basics(xact.circulation.target_copy).title -%]
+                [% ELSE %]
+                    [%- xact.last_billing_type -%]
+                [% END %]
+            </div>
+            <div>Total Billed: [% xact.total_owed %]</div>
+            <div>Total Paid: [% xact.total_paid %]</div>
+            <div>Balance Owed : [% xact.balance_owed %]</div>
+        </li>
+    [% END %]
+    </ol>
+</div>
+$$
+;
+
+
+SELECT evergreen.upgrade_deps_block_check('0974', :eg_version); -- tmccanna/gmcharlt
+
+UPDATE action_trigger.event_definition SET template = 
+$$
+[%- USE date -%]
+[%- SET user = target.0.usr -%]
+<div>
+    <style> li { padding: 8px; margin 5px; }</style>
+    <div>[% date.format %]</div>
+    <br/>
+    Holds for:<br/>
+	[% user.family_name %], [% user.first_given_name %]
+	
+    <ol>
+    [% FOR hold IN target %]
+        [%-
+            SET idx = loop.count - 1;
+            SET udata =  user_data.$idx;
+        -%]
+        <li>
+            <div>Title: [% udata.item_title %]</div>
+            <div>Author: [% udata.item_author %]</div>
+            <div>Pickup Location: [% udata.pickup_lib %]</b></div>
+            <div>Status: 
+                [%- IF udata.ready -%]
+                    Ready for pickup
+                [% ELSE %]
+                    #[% udata.queue_position %] of 
+                      [% udata.potential_copies %] copies.
+                [% END %]
+            </div>
+        </li>
+    [% END %]
+    </ol>
+</div>
+
+$$ WHERE id=12
+AND template = 
+$$
+[%- USE date -%]
+[%- SET user = target.0.usr -%]
+<div>
+    <style> li { padding: 8px; margin 5px; }</style>
+    <div>[% date.format %]</div>
+    <br/>
+
+    [% user.family_name %], [% user.first_given_name %]
+    <ol>
+    [% FOR hold IN target %]
+        [%-
+            SET idx = loop.count - 1;
+            SET udata =  user_data.$idx
+        -%]
+        <li>
+            <div>Title: [% hold.bib_rec.bib_record.simple_record.title %]</div>
+            <div>Author: [% hold.bib_rec.bib_record.simple_record.author %]</div>
+            <div>Pickup Location: [% hold.pickup_lib.name %]</div>
+            <div>Status: 
+                [%- IF udata.ready -%]
+                    Ready for pickup
+                [% ELSE %]
+                    #[% udata.queue_position %] of [% udata.potential_copies %] copies.
+                [% END %]
+            </div>
+        </li>
+    [% END %]
+    </ol>
+</div>
+$$
+;
+
+COMMIT;
-- 
2.11.0