From 655cc77db1337b6166add3e6052267999cb22174 Mon Sep 17 00:00:00 2001
From: Chris Sharp <csharp@georgialibraries.org>
Date: Tue, 9 Mar 2021 16:36:14 -0500
Subject: [PATCH] LP#1902939: Student Card SQL/IDL changes

---
 Open-ILS/examples/fm_IDL.xml                       | 87 ++++++++++++++++++----
 Open-ILS/src/sql/Pg/600.schema.student_card.sql    | 16 ++--
 Open-ILS/src/sql/Pg/950.data.seed-values.sql       |  2 +
 .../sql/Pg/upgrade/XXXX.schema.student_cards.sql   | 52 +++++++++++++
 4 files changed, 137 insertions(+), 20 deletions(-)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.student_cards.sql

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index abe40bfd6d..05ef148724 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -13526,31 +13526,90 @@ SELECT  usr,
 			<field name="id"                reporter:datatype="id"       reporter:label="District ID"/>
 			<field name="code"              reporter:datatype="text"     reporter:label="District Code"/>
 			<field name="name"              reporter:datatype="text"     reporter:label="District Name"/>
-			<field name="context_org"       reporter:datatype="org_unit" reporter:label="Context Org" oils_obj:required="true"/>
-			<field name="enabled"           reporter:datatype="bool"     reporter:label="Enabled"/>
-			<field name="perm"              reporter:datatype="link"     reporter:label="Permission Required by User" oils_obj:required="true"/>
-			<field name="restrict_to_org"   reporter:datatype="bool"     reporter:label="Restrict by Home Library"/>
-			<field name="allow_inactive"    reporter:datatype="bool"     reporter:label="Allow Inactive Users"/>
+			<field name="active"            reporter:datatype="bool"     reporter:label="Active?"/>
+			<field name="state_id"          reporter:datatype="int"      reporter:label="State ID"/>
+			<field name="contact_name"      reporter:datatype="text"     reporter:label="District Contact Person"/>
+			<field name="contact_email"     reporter:datatype="text"     reporter:label="District Contact Email"/>
+			<field name="ftp_host"          reporter:datatype="text"     reporter:label="FTP Hostname"/>
+			<field name="ftp_port"          reporter:datatype="integer"  reporter:label="FTP Custom Port"/>
+			<field name="ftp_user"          reporter:datatype="text"     suppress_controller="open-ils.reporter-store"/>
+			<field name="ftp_pass"          reporter:datatype="text"     suppress_controller="open-ils.reporter-store open-ils.pcrud"/>
+			<field name="ftp_remote_dir"    reporter:datatype="text"     reporter:label="FTP Remote Directory"/>
+			<field name="ftp_exceptions_dir"    reporter:datatype="text"     reporter:label="FTP Exceptions Directory"/>
 			<field name="allow_expired"     reporter:datatype="bool"     reporter:label="Allow Expired Users"/>
 			<field name="block_list"        reporter:datatype="text"     reporter:label="Block List"/>
 			<field name="usr_activity_type" reporter:datatype="link"     reporter:label="User Activity Type"/>
 		</fields>
-		<links>
-			<link field="context_org" reltype="has_a" key="id" map="" class="aou"/>
-			<link field="perm" reltype="has_a" key="id" map="" class="ppl"/>
-			<link field="usr_activity_type" reltype="has_a" key="id" map="" class="cuat"/>
-		</links>
 		<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
 			<actions>
-				<create permission="ADMIN_REMOTEAUTH" context_field="context_org"/>
-				<retrieve permission="STAFF_LOGIN" context_field="context_org"/>
-				<update permission="ADMIN_REMOTEAUTH" context_field="context_org"/>
-				<delete permission="ADMIN_REMOTEAUTH" context_field="context_org"/>
+				<create permission="ADMIN_STUDENT_CARDS" global_required="true"/>
+				<retrieve permission="ADMIN_STUDENT_CARDS" global_required="true"/>
+				<update permission="ADMIN_STUDENT_CARDS" global_required="true"/>
+				<delete permission="ADMIN_STUDENT_CARDS" global_required="true"/>
+			</actions>
+		</permacrud>
+	</class>
+
+	<class id="scs" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="student_card::school" oils_persist:tablename="student_card.school" reporter:label="Student Card School">
+		<fields oils_persist:primary="id">
+			<field name="id"                reporter:datatype="id"       reporter:label="School ID"/>
+			<field name="district_id"       reporter:datatype="link"     reporter:label="District Link"/>
+			<field name="name"              reporter:datatype="text"     reporter:label="School Name"/>
+			<field name="state_id"          reporter:datatype="int"      reporter:label="State ID"/>
+			<field name="grades"            reporter:datatype="text"     reporter:label="School Grades"/>
+			<field name="addr_street_1"     reporter:datatype="text"     reporter:label="School Address Street 1"/>
+			<field name="addr_street_2"     reporter:datatype="text"     reporter:label="School Address Street 2"/>
+			<field name="addr_city"         reporter:datatype="text"     reporter:label="School Address City"/>
+			<field name="addr_county"       reporter:datatype="text"     reporter:label="School Address County"/>
+			<field name="addr_state"        reporter:datatype="text"     reporter:label="School Address State"/>
+			<field name="addr_post_code"    reporter:datatype="text"     reporter:label="School Address Postal Code"/>
+			<field name="eg_perm_group"     reporter:datatype="link"     reporter:label="Permission Profile"/>
+			<field name="home_ou"           reporter:datatype="link"     reporter:label="Home Library"/>
+		</fields>
+        <links>
+            <link field="district_id" reltype="has_a" key="id" map="" class="scd"/>
+            <link field="eg_perm_group" reltype="has_a" key="id" map="" class="pgt"/>
+            <link field="home_ou" reltype="has_a" key="id" map="" class="aou"/>
+        </links>
+		<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+			<actions>
+				<create permission="ADMIN_STUDENT_CARDS" global_required="true"/>
+				<retrieve permission="ADMIN_STUDENT_CARDS" global_required="true"/>
+				<update permission="ADMIN_STUDENT_CARDS" global_required="true"/>
+				<delete permission="ADMIN_STUDENT_CARDS" global_required="true"/>
 			</actions>
 		</permacrud>
 	</class>
 
+<!-- CREATE TABLE student_card.import (
+    id                  SERIAL PRIMARY KEY,
+    import_time         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    district_id         INTEGER NOT NULL REFERENCES student_card.district (id),
+    filename            TEXT,
+    error_message       TEXT
+);
+
+-->
 
+	<class id="sci" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="student_card::import" oils_persist:tablename="student_card.import" reporter:label="Student Card Import">
+		<fields oils_persist:primary="id">
+			<field name="id"                reporter:datatype="id"       reporter:label="Import ID"/>
+			<field name="district_id"       reporter:datatype="link"     reporter:label="District Link"/>
+			<field name="filename"          reporter:datatype="text"     reporter:label="File Name"/>
+			<field name="error_message"     reporter:datatype="text"     reporter:label="Error Message"/>
+		</fields>
+        <links>
+            <link field="district_id" reltype="has_a" key="id" map="" class="scd"/>
+        </links>
+		<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+			<actions>
+				<create permission="ADMIN_STUDENT_CARDS" global_required="true"/>
+				<retrieve permission="ADMIN_STUDENT_CARDS" global_required="true"/>
+				<update permission="ADMIN_STUDENT_CARDS" global_required="true"/>
+				<delete permission="ADMIN_STUDENT_CARDS" global_required="true"/>
+			</actions>
+		</permacrud>
+	</class>
 
 
 	<!-- ********************************************************************************************************************* -->
diff --git a/Open-ILS/src/sql/Pg/600.schema.student_card.sql b/Open-ILS/src/sql/Pg/600.schema.student_card.sql
index 73f93bedd8..07d81033b8 100644
--- a/Open-ILS/src/sql/Pg/600.schema.student_card.sql
+++ b/Open-ILS/src/sql/Pg/600.schema.student_card.sql
@@ -1,26 +1,30 @@
 BEGIN;
 
+DROP SCHEMA student_card CASCADE;
+
 CREATE SCHEMA student_card;
 
 CREATE TABLE student_card.district (
     id                  SERIAL PRIMARY KEY,
     code                TEXT NOT NULL UNIQUE,
     name                TEXT NOT NULL UNIQUE,
+    active              BOOLEAN NOT NULL DEFAULT TRUE,
     state_id            INTEGER NOT NULL UNIQUE,
     contact_name        TEXT,
     contact_email       TEXT,
-    remote_host         TEXT NOT NULL,
-    remote_user         TEXT,
-    remote_pass         VARCHAR(255), --TODO: encrypt this data with PGP?
-    remote_remote_dir   TEXT,
-    exceptions_dir      TEXT
+    ftp_host            TEXT NOT NULL,
+    ftp_port            INTEGER,
+    ftp_user            TEXT,
+    ftp_pass            VARCHAR(255), --TODO: encrypt this data with PGP
+    ftp_remote_dir      TEXT,
+    ftp_exceptions_dir  TEXT
 );
 
 CREATE TABLE student_card.school (
     id                  SERIAL PRIMARY KEY,
     district_id         INTEGER NOT NULL REFERENCES student_card.district (id),
     name                TEXT NOT NULL UNIQUE,
-    state_id            INTEGER NOT NULL,
+    state_id            INTEGER NOT NULL, -- school ID per the State of Georgia
     grades              TEXT,
     addr_street_1       TEXT,
     addr_street_2       TEXT,
diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql
index 678ed4122d..e1a2837b6a 100644
--- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql
+++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql
@@ -1961,6 +1961,8 @@ INSERT INTO permission.perm_list ( id, code, description ) VALUES
     'Administer geographic location services', 'ppl', 'description')),
  ( 632, 'UPDATE_USER_PHOTO_URL', oils_i18n_gettext(632,
     'Update the user photo url field in patron registration and editor', 'ppl', 'description'))
+ ( 633, 'ADMIN_STUDENT_CARDS', oils_i18n_gettext(632,
+    'Modify student card settings', 'ppl', 'description'))
 ;
 
 
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.student_cards.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.student_cards.sql
new file mode 100644
index 0000000000..d59d07223a
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.student_cards.sql
@@ -0,0 +1,52 @@
+BEGIN;
+
+-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+CREATE SCHEMA student_card;
+
+CREATE TABLE student_card.district (
+    id                  SERIAL PRIMARY KEY,
+    code                TEXT NOT NULL UNIQUE,
+    name                TEXT NOT NULL UNIQUE,
+    active              BOOLEAN NOT NULL DEFAULT TRUE,
+    state_id            INTEGER NOT NULL UNIQUE,
+    contact_name        TEXT,
+    contact_email       TEXT,
+    ftp_host            TEXT NOT NULL,
+    ftp_port            INTEGER,
+    ftp_user            TEXT,
+    ftp_pass            VARCHAR(255), --TODO: encrypt this data with PGP
+    ftp_remote_dir      TEXT,
+    ftp_exceptions_dir  TEXT
+);
+
+CREATE TABLE student_card.school (
+    id                  SERIAL PRIMARY KEY,
+    district_id         INTEGER NOT NULL REFERENCES student_card.district (id),
+    name                TEXT NOT NULL UNIQUE,
+    state_id            INTEGER NOT NULL, -- school ID per the State of Georgia
+    grades              TEXT,
+    addr_street_1       TEXT,
+    addr_street_2       TEXT,
+    addr_city           TEXT,
+    addr_county         TEXT,
+    addr_state          TEXT,
+    addr_post_code      TEXT,
+    eg_perm_group       INTEGER NOT NULL REFERENCES permission.grp_tree (id),
+    home_ou             INTEGER NOT NULL REFERENCES actor.org_unit (id)
+);
+CREATE UNIQUE INDEX student_card_school_state_id_idx ON student_card.school (district_id, state_id);
+
+CREATE TABLE student_card.import (
+    id                  SERIAL PRIMARY KEY,
+    import_time         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    district_id         INTEGER NOT NULL REFERENCES student_card.district (id),
+    filename            TEXT,
+    error_message       TEXT
+);
+
+INSERT INTO permission.perm_list ( id, code, description ) VALUES
+ ( 628, 'ADMIN_STUDENT_CARDS', oils_i18n_gettext(628,
+    'Modify student card settings', 'ppl', 'description'));
+
+COMMIT;
-- 
2.11.0