From b9096fe7dfbdf5a483608e5d1169184a6afbeb70 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Date: Wed, 3 Aug 2022 11:28:12 +0200
Subject: [PATCH] Bug 32030: Filter by expired agreements

This patch adds a checkbox and flatpickr input to filter agreements by
expiration date.

Signed-off-by: Jonathan Field <jonathan.field@ptfs-europe.com>

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
---
 Koha/ERM/Agreements.pm                        | 41 +++++++++++++-
 Koha/REST/V1/ERM/Agreements.pm                |  5 ++
 api/v1/swagger/paths/erm_agreements.yaml      |  5 ++
 .../js/vue/components/ERM/AgreementsList.vue  | 55 ++++++++++++++++++-
 .../ERM/EHoldingsEBSCOPackagesList.vue        | 18 ++----
 .../ERM/EHoldingsEBSCOTitlesList.vue          | 18 ++----
 .../prog/js/vue/components/ERM/ERMMain.vue    |  2 +-
 .../prog/js/vue/composables/datatables.js     | 11 ++++
 8 files changed, 122 insertions(+), 33 deletions(-)

diff --git a/Koha/ERM/Agreements.pm b/Koha/ERM/Agreements.pm
index 9773db4180..6001c4727c 100644
--- a/Koha/ERM/Agreements.pm
+++ b/Koha/ERM/Agreements.pm
@@ -16,11 +16,13 @@ package Koha::ERM::Agreements;
 # along with Koha; if not, see <http://www.gnu.org/licenses>.
 
 use Modern::Perl;
-
+use DateTime;
 
 use Koha::Database;
+use Koha::DateUtils qw( dt_from_string );
 
 use Koha::ERM::Agreement;
+use Koha::ERM::Agreement::Periods;
 
 use base qw(Koha::Objects);
 
@@ -34,6 +36,43 @@ Koha::ERM::Agreements- Koha ErmAgreement Object set class
 
 =cut
 
+=head3 filter_by_expired
+
+=cut
+
+sub filter_by_expired {
+    my ($self, $max_expiration_date) = @_;
+
+    $max_expiration_date =
+      $max_expiration_date
+      ? dt_from_string( $max_expiration_date, 'iso' )
+      : dt_from_string;
+
+    my $periods = Koha::ERM::Agreement::Periods->search(
+        { agreement_id => [ $self->get_column('agreement_id') ] },
+        {
+            select => [
+                'agreement_id',
+                \do {"CASE WHEN MAX(me.ended_on IS NULL) = 0 THEN max(me.ended_on) END"}
+            ],
+            as       => [ 'agreement_id', 'max_ended_on' ],
+            group_by => ['agreement_id'],
+        }
+    );
+
+    my @expired_agreement_ids;
+    while ( my $p = $periods->next ) {
+        # FIXME Can this be moved in the HAVING clause of the previous query?
+        my $max_ended_on = $p->get_column('max_ended_on');
+        next unless $max_ended_on;
+        my $max_ended_on_dt = dt_from_string($max_ended_on);
+        next if DateTime->compare( $max_ended_on_dt, $max_expiration_date ) == 1;
+        push @expired_agreement_ids, $p->agreement_id;
+    }
+
+    return $self->search( { agreement_id => \@expired_agreement_ids } );
+}
+
 =head3 type
 
 =cut
diff --git a/Koha/REST/V1/ERM/Agreements.pm b/Koha/REST/V1/ERM/Agreements.pm
index 53fda67988..c93af41201 100644
--- a/Koha/REST/V1/ERM/Agreements.pm
+++ b/Koha/REST/V1/ERM/Agreements.pm
@@ -35,8 +35,13 @@ use Try::Tiny qw( catch try );
 sub list {
     my $c = shift->openapi->valid_input or return;
 
+    my $max_expiration_date = delete $c->validation->output->{max_expiration_date};
+
     return try {
         my $agreements_set = Koha::ERM::Agreements->new;
+        if ( $max_expiration_date ) {
+            $agreements_set = $agreements_set->filter_by_expired( $max_expiration_date );
+        }
         my $agreements = $c->objects->search( $agreements_set );
         return $c->render( status => 200, openapi => $agreements );
     }
diff --git a/api/v1/swagger/paths/erm_agreements.yaml b/api/v1/swagger/paths/erm_agreements.yaml
index 6c311d53a5..2f3e7e3f1c 100644
--- a/api/v1/swagger/paths/erm_agreements.yaml
+++ b/api/v1/swagger/paths/erm_agreements.yaml
@@ -54,6 +54,11 @@
         name: license_info
         required: false
         type: string
+      - description: filter by expired agreements
+        in: query
+        name: max_expiration_date
+        type: string
+        format: date
       - $ref: "../swagger.yaml#/parameters/match"
       - $ref: "../swagger.yaml#/parameters/order_by"
       - $ref: "../swagger.yaml#/parameters/page"
diff --git a/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/AgreementsList.vue b/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/AgreementsList.vue
index 5ad63035ba..fc1f021189 100644
--- a/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/AgreementsList.vue
+++ b/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/AgreementsList.vue
@@ -2,6 +2,28 @@
     <div v-if="!this.initialized">{{ $t("Loading") }}</div>
     <div v-else-if="this.agreements" id="agreements_list">
         <Toolbar v-if="before_route_entered" />
+        <fieldset v-if="this.agreements.length">
+            <label for="expired_filter">{{ $t("Filter by expired") }}:</label>
+            <input
+                type="checkbox"
+                id="expired_filter"
+                v-model="filters.by_expired"
+                @keyup.enter="filter_table"
+            />
+            {{ $t("on") }}
+            <flat-pickr
+                id="max_expiration_date_filter"
+                v-model="filters.max_expiration_date"
+                :config="fp_config"
+            />
+
+            <input
+                @click="filter_table"
+                id="filter_table"
+                type="button"
+                :value="$t('Filter')"
+            />
+        </fieldset>
         <table v-if="this.agreements.length" :id="table_id"></table>
         <div v-else-if="this.initialized" class="dialog message">
             {{ $t("There are no agreements defined") }}
@@ -10,13 +32,14 @@
 </template>
 
 <script>
+import flatPickr from 'vue-flatpickr-component'
 import Toolbar from "./AgreementsToolbar.vue"
 import { createVNode, render } from 'vue'
 import { useVendorStore } from "../../stores/vendors"
 import { useAVStore } from "../../stores/authorised_values"
 import { storeToRefs } from "pinia"
 import { fetchAgreements } from "../../fetch"
-import { useDataTable } from "../../composables/datatables"
+import { useDataTable, build_url } from "../../composables/datatables"
 
 export default {
     setup() {
@@ -38,8 +61,13 @@ export default {
     },
     data: function () {
         return {
+            fp_config: flatpickr_defaults, dates_fixed: 0,
             agreements: [],
             initialized: false,
+            filters: {
+                by_expired: this.$route.query.by_expired || false,
+                max_expiration_date: this.$route.query.max_expiration_date || "",
+            },
             before_route_entered: false,
             building_table: false,
         }
@@ -53,6 +81,14 @@ export default {
             }
         })
     },
+    computed: {
+        datatable_url() {
+            let url = '/api/v1/erm/agreements'
+            if (this.filters.by_expired)
+                url += '?max_expiration_date=' + $date_to_rfc3339(this.filters.max_expiration_date)
+            return url
+        }
+    },
     methods: {
         async getAgreements() {
             const agreements = await fetchAgreements()
@@ -71,6 +107,18 @@ export default {
         select_agreement: function (agreement_id) {
             this.$emit('select-agreement', agreement_id)
             this.$emit('close')
+        },
+        filter_table: async function () {
+            let new_route = build_url("/cgi-bin/koha/erm/agreements", this.filters)
+            this.$router.push(new_route)
+            if (this.filters.by_expired) {
+                if (!this.filters.max_expiration_date)
+                    this.filters.max_expiration_date = new Date()
+            }
+            $('#' + this.table_id).DataTable().ajax.url(this.datatable_url).draw()
+        },
+        table_url: function () {
+
         },
         build_datatable: function () {
             let show_agreement = this.show_agreement
@@ -79,6 +127,7 @@ export default {
             let select_agreement = this.select_agreement
             let get_lib_from_av = this.get_lib_from_av
             let map_av_dt_filter = this.map_av_dt_filter
+            let datatable_url = this.datatable_url
             let default_search = this.$route.query.q
             let actions = this.before_route_entered ? 'edit_delete' : 'select'
             let table_id = this.table_id
@@ -101,7 +150,7 @@ export default {
 
             const table = $("#" + table_id).kohaTable({
                 ajax: {
-                    url: "/api/v1/erm/agreements",
+                    url: datatable_url
                 },
                 order: [[0, "asc"]],
                 autoWidth: false,
@@ -260,7 +309,7 @@ export default {
             this.getAgreements().then(() => this.build_datatable())
         }
     },
-    components: { Toolbar },
+    components: { flatPickr, Toolbar },
     name: "AgreementsList",
     emits: ["select-agreement", "close"],
 }
diff --git a/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/EHoldingsEBSCOPackagesList.vue b/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/EHoldingsEBSCOPackagesList.vue
index 7e608f52bb..0512555312 100644
--- a/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/EHoldingsEBSCOPackagesList.vue
+++ b/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/EHoldingsEBSCOPackagesList.vue
@@ -65,7 +65,7 @@ import { useVendorStore } from "../../stores/vendors"
 import { useAVStore } from "../../stores/authorised_values"
 import { storeToRefs } from "pinia"
 import { fetchCountLocalPackages } from './../../fetch'
-import { useDataTable } from "../../composables/datatables"
+import { useDataTable, build_url_params, build_url } from "../../composables/datatables"
 
 export default {
     setup() {
@@ -103,7 +103,7 @@ export default {
         }
     },
     computed: {
-        local_packages_url() { return this.build_url("/cgi-bin/koha/erm/eholdings/local/packages") },
+        local_packages_url() { return build_url("/cgi-bin/koha/erm/eholdings/local/packages", this.filters) },
     },
     beforeRouteEnter(to, from, next) {
         next(vm => {
@@ -114,18 +114,8 @@ export default {
         show_package: function (package_id) {
             this.$router.push("/cgi-bin/koha/erm/eholdings/ebsco/packages/" + package_id)
         },
-        build_url_params: function () {
-            return Object.entries(this.filters)
-                .map(([k, v]) => v ? k + "=" + v : undefined)
-                .filter(e => e !== undefined)
-                .join('&')
-        },
-        build_url: function (base_url) {
-            let params = this.build_url_params()
-            return base_url + (params.length ? '?' + params : '')
-        },
         filter_table: async function () {
-            let new_route = this.build_url("/cgi-bin/koha/erm/eholdings/ebsco/packages")
+            let new_route = build_url("/cgi-bin/koha/erm/eholdings/ebsco/packages", this.filters)
             this.$router.push(new_route)
             this.show_table = true
             this.local_count_packages = null
@@ -140,7 +130,7 @@ export default {
             let map_av_dt_filter = this.map_av_dt_filter
 
             if (!this.show_table) {
-                this.show_table = this.build_url_params().length ? true : false
+                this.show_table = build_url_params(this.filters).length ? true : false
             }
             let filters = this.filters
             let show_table = this.show_table
diff --git a/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/EHoldingsEBSCOTitlesList.vue b/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/EHoldingsEBSCOTitlesList.vue
index 4c0e9ffc05..eb2a7d20e6 100644
--- a/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/EHoldingsEBSCOTitlesList.vue
+++ b/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/EHoldingsEBSCOTitlesList.vue
@@ -71,7 +71,7 @@ import { useVendorStore } from "../../stores/vendors"
 import { useAVStore } from "../../stores/authorised_values"
 import { storeToRefs } from "pinia"
 import { fetchCountLocalTitles } from "./../../fetch"
-import { useDataTable } from "../../composables/datatables"
+import { useDataTable, build_url_params, build_url } from "../../composables/datatables"
 
 export default {
     setup() {
@@ -108,7 +108,7 @@ export default {
         }
     },
     computed: {
-        local_titles_url() { return this.build_url("/cgi-bin/koha/erm/eholdings/local/titles") },
+        local_titles_url() { return build_url("/cgi-bin/koha/erm/eholdings/local/titles", this.filters) },
     },
     beforeRouteEnter(to, from, next) {
         next(vm => {
@@ -119,20 +119,10 @@ export default {
         show_title: function (title_id) {
             this.$router.push("/cgi-bin/koha/erm/eholdings/ebsco/titles/" + title_id)
         },
-        build_url_params: function () {
-            return Object.entries(this.filters)
-                .map(([k, v]) => v ? k + "=" + v : undefined)
-                .filter(e => e !== undefined)
-                .join('&')
-        },
-        build_url: function (base_url) {
-            let params = this.build_url_params()
-            return base_url + (params.length ? '?' + params : '')
-        },
         filter_table: async function () {
             if (this.filters.publication_title.length) {
                 this.cannot_search = false
-                let new_route = this.build_url("/cgi-bin/koha/erm/eholdings/ebsco/titles")
+                let new_route = build_url("/cgi-bin/koha/erm/eholdings/ebsco/titles", this.filters)
                 this.$router.push(new_route)
                 this.show_table = true
                 this.local_count_titles = null
@@ -148,7 +138,7 @@ export default {
             let show_title = this.show_title
             let get_lib_from_av = this.get_lib_from_av
             if (!this.show_table) {
-                this.show_table = this.build_url_params().length ? true : false
+                this.show_table = build_url_params(this.filters).length ? true : false
             }
             let filters = this.filters
             let table_id = this.table_id
diff --git a/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/ERMMain.vue b/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/ERMMain.vue
index 3b28d949a1..7f130192c0 100644
--- a/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/ERMMain.vue
+++ b/koha-tmpl/intranet-tmpl/prog/js/vue/components/ERM/ERMMain.vue
@@ -173,7 +173,7 @@ form .v-select {
     width: 30%;
 }
 
-.v-select, input:not([type=submit]):not([type=search]):not([type=button]), textarea {
+.v-select, input:not([type=submit]):not([type=search]):not([type=button]):not([type=checkbox]), textarea {
     border-color: rgba(60,60,60,0.26);
     border-width: 1px;
     border-radius: 4px;
diff --git a/koha-tmpl/intranet-tmpl/prog/js/vue/composables/datatables.js b/koha-tmpl/intranet-tmpl/prog/js/vue/composables/datatables.js
index a9d82f0ab8..b150021d43 100644
--- a/koha-tmpl/intranet-tmpl/prog/js/vue/composables/datatables.js
+++ b/koha-tmpl/intranet-tmpl/prog/js/vue/composables/datatables.js
@@ -9,3 +9,14 @@ export function useDataTable(table_id) {
         }
     });
 }
+
+export function build_url_params(filters) {
+    return Object.entries(filters)
+        .map(([k, v]) => (v ? k + "=" + v : undefined))
+        .filter((e) => e !== undefined)
+        .join("&");
+}
+export function build_url(base_url, filters) {
+    let params = build_url_params(filters);
+    return base_url + (params.length ? "?" + params : "");
+}
-- 
2.39.5