From 08cd99c8813855bb9f334ed84d9c205dfa80d62e Mon Sep 17 00:00:00 2001 From: Benjamin Murphy Date: Fri, 31 Jan 2020 14:27:49 -0500 Subject: [PATCH] geosort part 1 --- Open-ILS/examples/fm_IDL.xml | 14 + Open-ILS/examples/opensrf.xml.example | 22 + Open-ILS/examples/opensrf_core.xml.example | 1 + .../server/admin-server-splash.component.html | 2 + .../app/staff/admin/server/admin-server.module.ts | 2 + .../org-unit-shipping-hub-distance.component.ts | 119 +++++ .../src/app/staff/admin/server/routing.module.ts | 6 + Open-ILS/src/perlmods/Build.PL | 4 +- Open-ILS/src/perlmods/MANIFEST | 1 + .../lib/OpenILS/Application/Storage/CDBI/actor.pm | 18 + .../OpenILS/Application/Storage/Driver/Pg/dbi.pm | 6 + .../lib/OpenILS/Application/VicinityCalculator.pm | 106 ++++ .../src/perlmods/lib/OpenILS/Utils/HoldTargeter.pm | 65 ++- .../lib/OpenILS/Utils/VicinityCalculator.pm | 549 +++++++++++++++++++++ Open-ILS/src/sql/Pg/005.schema.actors.sql | 37 ++ Open-ILS/src/sql/Pg/950.data.seed-values.sql | 39 ++ .../xxxx.schema.actor_org_unit_shipping_hub.sql | 42 ++ .../src/templates/opac/parts/library/core_info.tt2 | 11 + .../staff/admin/actor/org_unit/t_main_tab.tt2 | 12 + Open-ILS/tests/datasets/sql/assets_concerto.sql | 2 + Open-ILS/tests/datasets/sql/env_create.sql | 16 +- Open-ILS/tests/datasets/sql/env_destroy.sql | 2 +- Open-ILS/tests/datasets/sql/libraries.sql | 59 ++- Open-ILS/tests/datasets/sql/transactions.sql | 7 + Open-ILS/web/conify/global/actor/org_unit.html | 25 + Open-ILS/web/opac/locale/en-US/conify.dtd | 1 + 26 files changed, 1127 insertions(+), 41 deletions(-) create mode 100644 Open-ILS/src/eg2/src/app/staff/admin/server/org-unit-shipping-hub-distance.component.ts create mode 100644 Open-ILS/src/perlmods/lib/OpenILS/Application/VicinityCalculator.pm create mode 100644 Open-ILS/src/perlmods/lib/OpenILS/Utils/VicinityCalculator.pm create mode 100644 Open-ILS/src/sql/Pg/upgrade/xxxx.schema.actor_org_unit_shipping_hub.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 674ace4fc6..c4f5202a48 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4134,6 +4134,18 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + @@ -7038,6 +7050,7 @@ SELECT usr, + @@ -7067,6 +7080,7 @@ SELECT usr, + diff --git a/Open-ILS/examples/opensrf.xml.example b/Open-ILS/examples/opensrf.xml.example index c713fa38ba..ae6edd7b13 100644 --- a/Open-ILS/examples/opensrf.xml.example +++ b/Open-ILS/examples/opensrf.xml.example @@ -1270,6 +1270,27 @@ vim:et:ts=4:sw=4: + + 3 + 1 + perl + OpenILS::Application::VicinityCalculator + 5 + + open-ils.vicinity-calculator_unix.sock + open-ils.vicinity-calculator_unix.pid + 100 + open-ils.vicinity-calculator_unix.log + 1 + 15 + 1 + 5 + + + + + + 5 1 @@ -1375,6 +1396,7 @@ vim:et:ts=4:sw=4: open-ils.vandelay open-ils.serial open-ils.hold-targeter + open-ils.vicinity-calculator open-ils.ebook_api open-ils.courses open-ils.curbside diff --git a/Open-ILS/examples/opensrf_core.xml.example b/Open-ILS/examples/opensrf_core.xml.example index aeba4ad94c..8bbec3a675 100644 --- a/Open-ILS/examples/opensrf_core.xml.example +++ b/Open-ILS/examples/opensrf_core.xml.example @@ -40,6 +40,7 @@ Example OpenSRF bootstrap configuration file for Evergreen open-ils.vandelay open-ils.serial open-ils.ebook_api + open-ils.vicinity-calculator diff --git a/Open-ILS/src/eg2/src/app/staff/admin/server/admin-server-splash.component.html b/Open-ILS/src/eg2/src/app/staff/admin/server/admin-server-splash.component.html index c6a4108031..1c161ec424 100644 --- a/Open-ILS/src/eg2/src/app/staff/admin/server/admin-server-splash.component.html +++ b/Open-ILS/src/eg2/src/app/staff/admin/server/admin-server-splash.component.html @@ -79,6 +79,8 @@ routerLink="/staff/admin/server/config/marc_field"> + + + + +
+ Entries in this table contain the distance in miles between shipping locations. Shipping hubs are configured from the Orginizational Units page. These numbers are used for sorting hold targets during inter-library lending. Entries can be created manually or calculated using the free Bing Maps API if a key has been set up in the opensrf core config file. Running the API will remove any existing data from this table. +
+ + +
+
+ + ` +}) + +export class OrgUnitShippingHubDistanceComponent implements OnInit { + + idlClass: string; + classLabel: string; + persistKeyPfx: string; + readonlyFields = ''; + configLinkBasePath = '/staff/admin'; + + // API is currently calculating + calculating : boolean; + // Tell the admin page to disable and hide the automagic org unit filter + disableOrgFilter: boolean; + + constructor( + private route: ActivatedRoute, + private idl: IdlService, + private net: NetService + ) { + } + + ngOnInit() { + let schema = this.route.snapshot.paramMap.get('schema'); + if (!schema) { + // Allow callers to pass the schema via static route data + const data = this.route.snapshot.data[0]; + if (data) { schema = data.schema; } + } + let table = this.route.snapshot.paramMap.get('table'); + if (!table) { + const data = this.route.snapshot.data[0]; + if (data) { table = data.table; } + } + const fullTable = schema + '.' + table; + + // Set the prefix to "server", "local", "workstation", + // extracted from the URL path. + // For admin pages that use none of these, avoid setting + // the prefix because that will cause it to double-up. + // e.g. eg.grid.acq.acq.cancel_reason + this.persistKeyPfx = this.route.snapshot.parent.url[0].path; + const selfPrefixers = ['acq', 'booking']; + if (selfPrefixers.indexOf(this.persistKeyPfx) > -1) { + // ACQ is a special case, because unlike 'server', 'local', + // 'workstation', the schema ('acq') is the root of the path. + this.persistKeyPfx = ''; + } else { + this.configLinkBasePath += '/' + this.persistKeyPfx; + } + + // Pass the readonlyFields param if available + if (this.route.snapshot.data && this.route.snapshot.data[0]) { + // snapshot.data is a HASH. + const data = this.route.snapshot.data[0]; + + if (data.readonlyFields) { + this.readonlyFields = data.readonlyFields; + } + + if (data.disableOrgFilter) { + this.disableOrgFilter = true; + } + } + + Object.keys(this.idl.classes).forEach(class_ => { + const classDef = this.idl.classes[class_]; + if (classDef.table === fullTable) { + this.idlClass = class_; + this.classLabel = classDef.label; + } + }); + + if (!this.idlClass) { + throw new Error('Unable to find IDL class for table ' + fullTable); + } + this.calculating = false; + } + + calculateDistances(){ + this.calculating = true; + this.net.request( + 'open-ils.vicinity-calculator', + 'open-ils.vicinity-calculator.build-distance-matrix' + ).subscribe( + n => {this.calculating = false; location.reload();}, + err => {alert('API failed to calculate ' + err);this.calculating = false;} + ); + } +} + + diff --git a/Open-ILS/src/eg2/src/app/staff/admin/server/routing.module.ts b/Open-ILS/src/eg2/src/app/staff/admin/server/routing.module.ts index caadbcb897..309270b441 100644 --- a/Open-ILS/src/eg2/src/app/staff/admin/server/routing.module.ts +++ b/Open-ILS/src/eg2/src/app/staff/admin/server/routing.module.ts @@ -2,6 +2,7 @@ import {NgModule} from '@angular/core'; import {RouterModule, Routes} from '@angular/router'; import {AdminServerSplashComponent} from './admin-server-splash.component'; import {BasicAdminPageComponent} from '@eg/staff/admin/basic-admin-page.component'; +import {OrgUnitShippingHubDistanceComponent} from './org-unit-shipping-hub-distance.component'; import {OrgUnitTypeComponent} from './org-unit-type.component'; import {PrintTemplateComponent} from './print-template.component'; import {PermGroupTreeComponent} from './perm-group-tree.component'; @@ -60,6 +61,11 @@ const routes: Routes = [{ data: [{schema: 'actor', table: 'org_unit_proximity_adjustment', disableOrgFilter: true}] }, { + path: 'actor/org_unit_shipping_hub_distance', + component: OrgUnitShippingHubDistanceComponent, + data: [{schema: 'actor', + table: 'org_unit_shipping_hub_distance', readonlyFields: 'id'}] +}, { path: 'asset/call_number_prefix', component: BasicAdminPageComponent, data: [{schema: 'asset', diff --git a/Open-ILS/src/perlmods/Build.PL b/Open-ILS/src/perlmods/Build.PL index 5c323085b9..29bbdcc492 100644 --- a/Open-ILS/src/perlmods/Build.PL +++ b/Open-ILS/src/perlmods/Build.PL @@ -40,6 +40,7 @@ my $build = Module::Build->new( 'File::Spec' => '0', 'File::stat' => '0', 'File::Temp' => '0', + 'Geo::Coder::Bing' => '0', 'Getopt::Long' => '0', 'IO::Scalar' => '0', 'List::Util' => '0', @@ -87,10 +88,11 @@ my $build = Module::Build->new( 'Unicode::Normalize' => '0', 'UNIVERSAL::require' => '0', 'UUID::Tiny' => '0', + 'WWW::REST' => '0', 'XML::LibXML' => '0', 'XML::LibXML::XPathContext' => '0', 'XML::LibXSLT' => '0', - 'XML::Simple' => '0', + 'XML::Simple' => '0' } ); diff --git a/Open-ILS/src/perlmods/MANIFEST b/Open-ILS/src/perlmods/MANIFEST index 8e7b0f58ac..185d084b16 100644 --- a/Open-ILS/src/perlmods/MANIFEST +++ b/Open-ILS/src/perlmods/MANIFEST @@ -154,6 +154,7 @@ lib/OpenILS/Utils/Normalize.pm lib/OpenILS/Utils/OfflineStore.pm lib/OpenILS/Utils/Penalty.pm lib/OpenILS/Utils/PermitHold.pm +lib/OpenILS/Utils/VicinityCalculator.pm lib/OpenILS/Utils/RemoteAccount.pm lib/OpenILS/Utils/ZClient.pm lib/OpenILS/WWW/AddedContent.pm diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/actor.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/actor.pm index a4047f794e..23a6c09eed 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/actor.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/actor.pm @@ -113,6 +113,24 @@ __PACKAGE__->columns( Essential => qw/org_unit name value/); #------------------------------------------------------------------------------- +package actor::org_unit_shipping_hub; +use base qw/actor/; + +__PACKAGE__->table( 'actor_org_unit_shipping_hub' ); +__PACKAGE__->columns( Primary => qw/id/); +__PACKAGE__->columns( Essential => qw/org_unit hub/); + + +#------------------------------------------------------------------------------- +package actor::org_unit_shipping_hub_distance; +use base qw/actor/; + +__PACKAGE__->table( 'actor_org_unit_shipping_hub_distance' ); +__PACKAGE__->columns( Primary => qw/id/); +__PACKAGE__->columns( Essential => qw/orig_hub dest_hub distance/); + + +#------------------------------------------------------------------------------- package actor::stat_cat; use base qw/actor/; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/dbi.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/dbi.pm index 196c46cfdd..b5bec0c29f 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/dbi.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/dbi.pm @@ -460,6 +460,12 @@ actor::org_unit::closed_date->sequence( 'actor.org_unit_closed_id_seq' ); #--------------------------------------------------------------------- + package actor::org_unit_shipping_hub; + + actor::org_unit_shipping_hub->table( 'actor.org_unit_shipping_hub' ); + actor::org_unit_shipping_hub->sequence( 'actor.org_unit_shipping_hub_id_seq' ); + + #--------------------------------------------------------------------- package actor::org_unit_setting; actor::org_unit_setting->table( 'actor.org_unit_setting' ); diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/VicinityCalculator.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/VicinityCalculator.pm new file mode 100644 index 0000000000..b50f64c093 --- /dev/null +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/VicinityCalculator.pm @@ -0,0 +1,106 @@ +package OpenILS::Application::VicinityCalculator; +use strict; +use warnings; +use OpenILS::Application; +use base qw/OpenILS::Application/; +use OpenILS::Utils::VicinityCalculator; +use OpenSRF::Utils::SettingsClient; +use OpenSRF::Utils::Logger qw(:logger); + +sub get_api_key { + my $config = OpenSRF::Utils::SettingsClient->new(); + my $key = $config->config_value( + apps => 'open-ils.vicinity-calculator' => app_settings => 'key' + ); + return $key; +} + +__PACKAGE__->register_method( + method => 'build_distance_matrix', + api_name => 'open-ils.vicinity-calculator.build-distance-matrix', + signature => { + desc => q/Batch calculation of shipping hub distance matrix./, + return => {desc => 'See API Options for return types'} + } +); + +sub build_distance_matrix{ + my ($self) = @_; + my $key = get_api_key(); + if(!defined($key) || $key eq ''){ + $logger->error("No Maps API key has been set up in opensrf xml."); + return undef; + } + else{ + my $calculator = OpenILS::Utils::VicinityCalculator->new($key); + $calculator->calculate_distance_matrix(); + return 1; + } +} + +__PACKAGE__->register_method( + method => 'set_coords', + api_name => 'open-ils.vicinity-calculator.set-coords', + signature => { + desc => q/Calculate the latitude and longitude of an address/, + } +); + +sub set_coords{ + my ($self, $client, $addr) = @_; + my $key = get_api_key(); + my $calculator = OpenILS::Utils::VicinityCalculator->new($key); + $logger->info("calculating address coordinates"); + return $calculator->set_coord_for_addr($addr); +} + +__PACKAGE__->register_method( + method => 'get_all_hubs', + api_name => 'open-ils.vicinity-calculator.shipping-hubs.retrieve', + signature => { + desc => q/Retrieve a list of all shipping hubs/, + } +); + +sub get_all_hubs{ + my ($self) = @_; + my $calculator = OpenILS::Utils::VicinityCalculator->new(); + my $key = get_api_key(); + if(!defined($key) || $key eq ''){ + $logger->error("No Maps API key has been set up in opensrf xml."); + return undef; + } + $logger->info("retreiving org unit shipping hubs"); + return $calculator->get_all_hubs(); +} + +__PACKAGE__->register_method( + method => 'get_hub_from_ou', + api_name => 'open-ils.vicinity-calculator.shipping-hub.retrieve', + signature => { + desc => q/Retrieve a shipping hub from a given OU/, + } +); + +sub get_hub_from_ou{ + my ($self, $org_unit) = @_; + my $calculator = OpenILS::Utils::VicinityCalculator::Matrix->new(); + $logger->info("retreiving org unit shipping hubs"); + return $calculator->get_hub_from_ou($org_unit); +} + +__PACKAGE__->register_method( + method => 'get_distance_between_shipping_hubs', + api_name => 'open-ils.vicinity-calculator.shipping-hubs.distance', + signature => { + desc => q/Retrieve the distance between two shipping hubs/, + } +); + +sub get_distance_between_shipping_hubs { + my ($self, $origin_hub, $dest_hub) = @_; + my $calculator = OpenILS::Utils::VicinityCalculator::Matrix->new(); + $logger->info("calculating org unit shipping hub distances"); + return $calculator->distance_between_hubs($origin_hub,$dest_hub); +} +1; \ No newline at end of file diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Utils/HoldTargeter.pm b/Open-ILS/src/perlmods/lib/OpenILS/Utils/HoldTargeter.pm index 56e9c841a8..65215c80f3 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Utils/HoldTargeter.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Utils/HoldTargeter.pm @@ -16,6 +16,7 @@ package OpenILS::Utils::HoldTargeter; use strict; use warnings; use DateTime; +use Data::Dumper; use OpenSRF::AppSession; use OpenSRF::Utils::Logger qw(:logger); use OpenSRF::Utils::JSON; @@ -262,6 +263,8 @@ package OpenILS::Utils::HoldTargeter::Single; use strict; use warnings; use DateTime; +use Data::Dumper; +use OpenILS::Utils::VicinityCalculator; use OpenSRF::AppSession; use OpenILS::Utils::DateTime qw/:datetime/; use OpenSRF::Utils::Logger qw(:logger); @@ -1094,32 +1097,64 @@ sub attempt_prev_copy_retarget { return undef; } -# Returns the closest copy by proximity that is a confirmed valid +# Returns the closest copy by proximity and vicinity that is a confirmed valid # targetable copy. sub find_nearest_copy { my $self = shift; my %prox_map = %{$self->{weighted_prox_map}}; my $hold = $self->hold; + my $req_hub; + my $vinc_calc = OpenILS::Utils::VicinityCalculator::Matrix->new(); my %seen; - # Pick a copy at random from each tier of the proximity map, - # starting at the lowest proximity and working up, until a - # copy is found that is suitable for targeting. for my $prox (sort {$a <=> $b} keys %prox_map) { + my %distance_matrix; + my %hub_by_target; my @copies = @{$prox_map{$prox}}; next unless @copies; - - my $rand = int(rand(scalar(@copies))); - - while (my ($c) = splice(@copies, $rand, 1)) { - $rand = int(rand(scalar(@copies))); - next if $seen{$c->{id}}; - - return $c if $self->copy_is_permitted($c); - $seen{$c->{id}} = 1; - - last unless(@copies); + # run vicinity calculator if proximity is greater than or equal to 3 + unless($prox < 3){ + unless($req_hub){ + # assigning the shipping hub for this hold + $req_hub = $vinc_calc->get_hub_from_ou($hold->pickup_lib); + } + my @copy_ids = map {$_->{id}} @copies; + # determine which shipping hub OU these copies would need to be sent to + %hub_by_target = $vinc_calc->get_target_hubs(\@copy_ids); + my @hubs = values(%hub_by_target); + %distance_matrix = $vinc_calc->hub_matrix($req_hub,\@hubs); + } + # run this block only if target copies are within the same OU + # or a distance matrix could not be retreived for a destination + if($prox < 3 || !%distance_matrix){ + # Pick a copy at random from each tier of the proximity map, + # starting at the lowest proximity and working up, until a + # copy is found that is suitable for targeting. + my $rand = int(rand(scalar(@copies))); + while (my ($c) = splice(@copies, $rand, 1)) { + $rand = int(rand(scalar(@copies))); + next if $seen{$c->{id}}; + + return $c if $self->copy_is_permitted($c); + $seen{$c->{id}} = 1; + + last unless(@copies); + } + } + else{ + # select the target copy from the closest OU + # TODO what happens if two hubs are the same distance away from home hub? + # TODO should we round distances so two hubs don't always choose from one another? + # TODO what if this was stored in the Action.hold_copy_map like the prox is? + for my $c (sort { $distance_matrix{$hub_by_target{$a->{id}}} <=> $distance_matrix{$hub_by_target{$b->{id}}} } @copies){ + $self->log_hold("VicinityCalculator - Copy: ".$c->{id}." Shipping Hub:".$hub_by_target{$c->{id}}. "Physical Distance: ".$distance_matrix{$hub_by_target{$c->{id}}}); + next if $seen{$c->{id}}; + return $c if $self->copy_is_permitted($c); + $seen{$c->{id}} = 1; + last unless(@copies); + } } + } return undef; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Utils/VicinityCalculator.pm b/Open-ILS/src/perlmods/lib/OpenILS/Utils/VicinityCalculator.pm new file mode 100644 index 0000000000..162f4c7084 --- /dev/null +++ b/Open-ILS/src/perlmods/lib/OpenILS/Utils/VicinityCalculator.pm @@ -0,0 +1,549 @@ +package OpenILS::Utils::VicinityCalculator; +use strict; use warnings; +use Geo::Coder::Bing; +use JSON; +use Data::Dumper; +use URI; +use OpenSRF::System; +use OpenILS::Application::Actor; +use OpenSRF::Utils::Logger qw($logger); +use OpenSRF::AppSession; +use OpenILS::Utils::Fieldmapper; +use OpenSRF::Utils::SettingsClient; +use OpenILS::Application::AppUtils; +use OpenILS::Utils::CStoreEditor qw/:funcs/; + +our $U = "OpenILS::Application::AppUtils"; +my $actor; + +sub new { + my ($class, $api_key) = @_; + my $self = { + editor => new_editor(), + bing => Geo::Coder::Bing->new(key => $api_key), + hub_cache => {}, + coord_cache => {}, + + }; + $self->{editor}->init; + return bless($self, $class); +} + +sub uniq { + my %seen; + grep !$seen{$_}++, @_; +} + +# Use Bing maps API to calculate the distances between all shipping hubs +sub calculate_distance_matrix { + my $self = shift; + # find hubs for all OUs + my @hubs = $self->get_all_hubs(); + # find addresses of all hub OUs + $logger->info("Getting shipping hub addresses"); + my %hub_coord = $self->get_coord_from_ou(uniq(@hubs)); + my @origins = values(%hub_coord); + my @destinations = values(%hub_coord); + my @hub_ids = keys(%hub_coord); + # make one giant request to bing to calculate our distance matrix + my @distance_matrix = $self->vicinity_between_coords(\@origins,\@destinations); + if(@distance_matrix){ + $self->{editor}->xact_begin; + # clear out existing matrix + $self->clear_hub_distances(); + for my $ref (@distance_matrix) { + for (@$ref){ + # create our AOUSHD objects for the data returned + my $dist = Fieldmapper::actor::org_unit_shipping_hub_distance->new; + $dist->orig_hub($hub_ids[$_->{originIndex}]); + $dist->dest_hub($hub_ids[$_->{destinationIndex}]); + $dist->distance($_->{travelDistance}); + # place AOUSHD into the DB + $self->{editor}->runmethod('create', 'actor.org_unit_shipping_hub_distance', 'aoushd', $dist); + } + } + # commit to DB + $self->{editor}->xact_commit; + } + else{ + $logger->error("API failed to calculate distance matrix"); + } +} + +sub get_addr_from_ou { +my($self,@org_ids) = @_; + my @ma = $self->{editor}->json_query({ + select => { + aou => [ + { + column => 'id', + } + ], + aoa => [ + { + column => 'city', + },{ + column => 'state', + },{ + column => 'county', + },{ + column => 'street1', + },{ + column => 'street2', + },{ + column => 'post_code', + } + ] + }, + from => {aou => 'aoa'}, + where => {id=>[@org_ids]} + }); + my %addrs; + + for my $ref (@ma) { + for (@$ref){ + $addrs{$_->{id}} = $self->format_street_address($_->{street1},$_->{street2},$_->{city},$_->{county},$_->{state},$_->{post_code}); + } + } + return %addrs; +} + +sub get_coord_from_ou { +my($self,@org_ids) = @_; + my @ma = $self->{editor}->json_query({ + select => { + aoa => [ + { + column => 'org_unit', + }, + { + column => 'latitude', + },{ + column => 'longitude', + },{ + column => 'address_type', + } + ] + }, + from => 'aoa', + where => {org_unit=>[@org_ids], address_type=>['MAILING']} + }); + my %coords; + + for my $ref (@ma) { + for (@$ref){ + $coords{$_->{org_unit}} = $_->{latitude}.",".$_->{longitude}; + } + } + return %coords; +} + +# gets the address into the proper format for API +sub format_street_address{ +shift; +return join(', ',grep(defined, @_)); +} + +# remove all existing distance calculations. +# TODO make this all happen in one query +# what could the analog to DELETE FROM TABLE be? +sub clear_hub_distances { +my($self,@org_ids) = @_; + my @ma = $self->{editor}->json_query({ + select => { + aoushd => [ + { + column => 'id', + } + ] + }, + from => 'aoushd' + }); + + for my $ref (@ma) { + for (@$ref){ + my $dist = Fieldmapper::actor::org_unit_shipping_hub_distance->new; + $dist->id($_->{id}); + $self->{editor}->runmethod('delete', 'actor.org_unit_shipping_hub_distance', 'aoushd', $dist); + } + } +} + +sub get_all_hubs { +my($self) = @_; +my @sh = $self->{editor}->json_query({ + select => { + aou => ['shipping_hub_ou'], + }, + from => 'aou' + }); + my @hubs; + for my $ref (@sh) { + for (@$ref){ + my $hub = $_->{shipping_hub_ou}; + if($hub && $hub != 0 && !($hub eq '')){ + push @hubs, $hub; + } + } + } + return @hubs; +} + +sub get_coord_from_address{ + my( $self, $addr ) = @_; + my $org1geo = $self->{bing}->geocode(location => $addr); + return $org1geo->{point}{coordinates}[0].",".$org1geo->{point}{coordinates}[1]; +} + +# set the latitude and longitude for all addresses associated with an org unit +sub set_coord_for_ou{ + my $self = shift; + my $ou = int(shift); + $logger->info("using API to retrieve Long/Lat for OU $ou"); + my @ma = $self->{editor}->json_query({ + select => { + aoa => [ + { + column => 'id', + }, + { + column => 'city', + }, + { + column => 'state', + }, + { + column => 'county', + }, + { + column => 'street1', + }, + { + column => 'street2', + }, + { + column => 'post_code', + } + ] + }, + from => 'aoa', + where => {org_unit => $ou} + }); + $self->{editor}->xact_begin; + for my $ref (@ma) { + for (@$ref){ + my $addr_string = $self->format_street_address($_->{street1},$_->{street2},$_->{city},$_->{county},$_->{state},$_->{post_code}); + my $org1geo = $self->{bing}->geocode($addr_string); + my $lat = $org1geo->{point}{coordinates}[0]; + my $long = $org1geo->{point}{coordinates}[1]; + my $addr = $self->{editor}->retrieve_actor_org_address($_->{id}); + $addr->latitude($lat); + $addr->longitude($long); + $logger->info("Got $lat $long for OU $ou"); + $self->{editor}->update_actor_org_address($addr) or return $self->{editor}->die_event; + } + } + $self->{editor}->xact_commit; + return 1; +} + +sub set_coord_for_addr{ + my $self = shift; + my $addr = int(shift); + $logger->info("using API to retrieve Long/Lat for address with ID $addr"); + my @ma = $self->{editor}->json_query({ + select => { + aoa => [ + { + column => 'id', + }, + { + column => 'city', + }, + { + column => 'state', + }, + { + column => 'county', + }, + { + column => 'street1', + }, + { + column => 'street2', + }, + { + column => 'post_code', + } + ] + }, + from => 'aoa', + where => {id => $addr} + }); + + for my $ref (@ma) { + for (@$ref){ + $self->{editor}->xact_begin; + my $addr_string = $self->format_street_address($_->{street1},$_->{street2},$_->{city},$_->{county},$_->{state},$_->{post_code}); + my $org1geo = $self->{bing}->geocode($addr_string); + my $lat = $org1geo->{point}{coordinates}[0]; + my $long = $org1geo->{point}{coordinates}[1]; + my $address = $self->{editor}->retrieve_actor_org_address($addr); + $address->latitude($lat); + $address->longitude($long); + $logger->info("Got $lat $long for address $addr"); + $self->{editor}->update_actor_org_address($address) or return $self->{editor}->die_event; + $self->{editor}->xact_commit; + my @val = ($lat,$long); + return \@val; + } + } + return $self->{editor}->die_event; +} + +sub vicinity_between_coord{ +my( $self, $origin_coord, $dest_coord ) = @_; + my $b = $self->{bing}; + return _geo_request($origin_coord,$dest_coord)->[0]->{travelDistance}; +} + +sub _geo_request{ +my( $self, $origin_coord, $dest_coord ) = @_; + my $b = $self->{bing}; + unless( $b->{key} ){ + $logger->error("API key was not found"); + return undef; + } + my $uri = URI->new("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=$origin_coord&destinations=$dest_coord&distanceUnit=mi&travelMode=driving&key=".$b->{key}); + return eval{$b->_rest_request($uri)->{results}}; +} + +sub vicinity_between_coords{ +my( $self, $origin_ref, $dest_ref ) = @_; + my @origins = @{ $origin_ref }; + my @destinations = @{ $dest_ref }; + return $self->_geo_request(join(';',@origins),join(';',@destinations)); +} + + +sub vicinity_between_ou { + my( $self, $org1, $org2 ) = @_; + my @addrs = $self->get_addr_from_ou($org1,$org2); + print("Calculating route between ".$addrs[0]." and ".$addrs[1]); + return $self->vicinity_between_coord($self->get_coord_from_address($addrs[0]),$self->get_coord_from_address($addrs[1])); +} + +sub vicinity_between_hub { + my( $self, $org1, $org2 ) = @_; + my %hubs = $self->get_hub_from_ou($org1,$org2); + if($hubs{$org1} == 0 || $hubs{$org2} == 0){ + print("Requested OU does not have a shipping hub!"); + die; + } + + return $self->vicinity_between_ou($hubs{$org1},$hubs{$org2}); +} + + +package OpenILS::Utils::VicinityCalculator::Matrix; +use OpenSRF::System; +use OpenILS::Application::Actor; +use OpenSRF::Utils::Logger qw(:logger); +use OpenSRF::AppSession; +use OpenILS::Utils::Fieldmapper; +use OpenSRF::Utils::SettingsClient; +use OpenILS::Application::AppUtils; +use OpenILS::Utils::CStoreEditor qw/:funcs/; +use Data::Dumper; + +our $U = "OpenILS::Application::AppUtils"; +sub new { + my ($class) = @_; + my $self = { editor => new_editor() }; + $self->{editor}->init; + return bless($self, $class); +} + +sub hub_matrix { + my ($self, $origin_hub, $dest_hubs_ref) = @_; + my @dest_hubs = @{$dest_hubs_ref}; + my @d = $self->{editor}->json_query({ + select => {'aoushd' => [{column => 'dest_hub'},{column => 'distance'}]}, + from => 'aoushd', + where => {'orig_hub'=>[$origin_hub],'dest_hub'=>[@dest_hubs]}, + order_by => [ + {class => 'aoushd', field => 'distance', direction => 'ASC'}, + ] + }); + + my %matrix; + for my $ref (@d) { + for (@$ref){ + $matrix{$_->{'dest_hub'}}=$_->{distance}; + } + } + # hub matrix will be undefined if any destination hubs are missing from the return list. + for my $hub (@dest_hubs){ + next if $matrix{$hub}; + $logger->error("OU $origin_hub has no calculation to OU $hub. open-ils.vicinity-calculator.build-distance-matrix must be run before vicinity based hold targeting can continue!"); + return undef; + } + return %matrix; +} + +sub distance_between_hubs { + my ($self, $origin_hub, $dest_hub) = @_; + my @d = $self->{editor}->json_query({ + select => {'aoushd' => [{column => 'distance'}]}, + from => 'aoushd', + where => {'orig_hub'=>[$origin_hub],'dest_hub'=>[$dest_hub]} + }); + for my $ref (@d) { + for (@$ref){ + return $_->{distance}; + } + } + $logger->error("OU $origin_hub has no calculation to OU $dest_hub. open-ils.vicinity-calculator.build-distance-matrix must be run!"); + return undef; +} + +sub get_target_hubs{ + my $self = shift; + my $copies_ref = shift; + my @target_copies = @{ $copies_ref }; + my @h = $self->{editor}->json_query({ + select => {'acp' => ['id','circ_lib']}, + from => 'acp', + where => {'+acp'=>{id => [@target_copies]}} + }); + my %circ_libs; + for my $ref (@h) { + for (@$ref){ + $circ_libs{$_->{id}} = $_->{circ_lib}; + } + } + + my %circ_hubs; + my %hubs; + my @sh = $self->{editor}->json_query({ + select => [{column=>'org_unit'},{column=>'hub'}], + from => [ + 'actor.list_org_unit_ancestor_shipping_hub',values(%circ_libs)] + }); + for my $ref (@sh) { + for (@$ref){ + $circ_hubs{$_->{org_unit}} = $_->{hub}; + } + } + foreach my $copy(@target_copies){ + $hubs{$copy} = $circ_hubs{$circ_libs{$copy}}; + } + + return %hubs; +} + +sub get_hub_from_ou { +my($self,@org_ids) = @_; +my @sh = $self->{editor}->json_query({ + select => [{column=>'org_unit'},{column=>'hub'}], + from => [ + 'actor.list_org_unit_ancestor_shipping_hub',@org_ids] + }); + return $sh[0][0]->{'hub'}; +} + + + + + +=begin work zone + +OpenSRF::System->bootstrap_client(config_file =>'/openils/conf/opensrf_core.xml'); + my $idl = OpenSRF::Utils::SettingsClient->new->config_value("IDL"); + Fieldmapper->import(IDL => $idl); +my $pc = OpenILS::Utils::VicinityCalculator->new("VbVe1thIFfqm2ghCuREV~3BmYd1kV23t34b_u1DXhQw~AvpRMvRV37o03fEBAq24KnW_R7I7M9CqwzezfKINgNG-LcwMuk7u7ihsBWZCPFE4"); +print Dumper($pc->set_coord_for_addr(2)); + +OpenSRF::System->bootstrap_client(config_file =>'/openils/conf/opensrf_core.xml'); + my $idl = OpenSRF::Utils::SettingsClient->new->config_value("IDL"); + Fieldmapper->import(IDL => $idl); +my $pc = OpenILS::Utils::VicinityCalculator::Matrix->new(); +my @hubs = (7,11,4); +print Dumper($pc->hub_matrix(13,\@hubs)); + + + +my @copy_id = (4007,3507,3807,3307,3707,3207,3607,3107, 4819); + +OpenSRF::System->bootstrap_client(config_file =>'/openils/conf/opensrf_core.xml'); + my $idl = OpenSRF::Utils::SettingsClient->new->config_value("IDL"); + Fieldmapper->import(IDL => $idl); +my $pc = OpenILS::Utils::VicinityCalculator->new("AosM-K7Hdbk-OMZ1jcJC1boNDGRpoYRL_bzgK6pqKNNVAc2-z0qbOVtc3itjfWj5"); +#my $pc = OpenILS::Utils::VicinityCalculator->new(); +#$pc->calculate_distance_matrix(); +#my @dest_hubs = (226,182,393,4,208); +#my @matrix = $pc->hub_matrix(180,\@dest_hubs); +#my @targets = (17024825,14189348,5952821,17056866,15214541,14074994 ); +#my %hubs = $pc->get_target_hubs(\@targets); +#print(Dumper(\%hubs)); +print($pc->get_hub_from_ou(2)); + + +OpenSRF::System->bootstrap_client(config_file =>'/openils/conf/opensrf_core.xml'); + my $idl = OpenSRF::Utils::SettingsClient->new->config_value("IDL"); + Fieldmapper->import(IDL => $idl); +my $pc = OpenILS::Utils::VicinityCalculator->new("AosM-K7Hdbk-OMZ1jcJC1boNDGRpoYRL_bzgK6pqKNNVAc2-z0qbOVtc3itjfWj5"); +#my $prox = $pc->vicinity_between_hub(102,109); +#print "\n\nDistance is $prox miles\n"; +#my @origins = ("35.778774,-78.685422", "36.280466,-76.214402"); +#my @destinations = ("34.694165,-76.551269", "35.595012,-82.551707"); +#print Dumper($pc->vicinity_between_coords(\@origins,\@destinations)); +# all this stuff below is gonna be a function that dumps the distance matrix into the database, it'll be run for every hub and we'll probably only need to run it once a year. Each iteration of the function will produce less data since the x runs before it would have calculated data we can use again. +my $hold_id = 6832841; +my $request_ou = $pc->ou_from_hold($hold_id); +my %proxmap = $pc->compile_weighted_vicinity_map($hold_id); +print("\n$request_ou\n"); +my @OU; + +push @OU, $request_ou; +while( my($k,$v) = each %proxmap){ + push @OU, $v->{ou}; +} +my %hubs = $pc->get_hub_from_ou(@OU); + +#my %hub_addr = $pc->get_addr_from_ou(uniq(values(%hubs))); + +# save coords so I don't blow through my queries on bing +my %hub_coord = ('260' => '36.4941,-79.73601','102' => '35.240596,-81.342891','314' => '35.511453,-78.3456','182' => '36.404213,-79.333114','325' => '34.775483,-79.465872','310' => '35.9207,-81.17589','4' => '35.293008,-81.555723','161' => '35.426298,-83.444665','189' => '35.92217133,-81.523353','142' => '36.109843,-78.296138','208' => '35.055522,-78.881343','237' => '35.304749,-76.789123','112' => '35.596714,-82.554788','370' => '36.32762667,-78.40572167','180' => '35.59727,-77.58532333','343' => '36.309471,-78.587604','269' => '35.40015517,-78.814922','177' => '35.487138,-82.9919','277' => '36.244018,-80.854557','291' => '35.266071,-77.581526','298' => '35.315485,-82.462982','196' => '35.68377417,-82.0106725','107' => '35.81924333,-80.25970833','367' => '35.240179,-82.216521','187' => '35.195678,-78.068236','166' => '35.897794,-80.559671','306' => '35.787559,-80.887852','226' => '36.098649,-80.252405','137' => '36.15954,-81.14848','238' => '35.543145,-77.05459333'); +# get coords for each hub +#while( my($k,$v) = each %hub_addr){ +# $hub_coord{$k} = $pc->get_coord_from_address($v); +#} + + + +# get distance matrix between my hub and every other hub +my $origin_hub = $hubs{$request_ou}; +my @origins = ($hub_coord{$origin_hub}); +my @destinations = values(%hub_coord); +my @hub_ids = keys(%hub_coord); +my @distance_matrix = $pc->vicinity_between_coords(\@origins,\@destinations); +my %hub_distance_matrix; + +# break down distance matrix into hash + $pc->{editor}->xact_begin; + for my $ref (@distance_matrix) { + for (@$ref){ + $hub_distance_matrix{$hub_ids[$_->{destinationIndex}]} = $_->{travelDistance}; + # put them in the database from here + my $dist = Fieldmapper::actor::org_unit_shipping_hub_distance->new; + $dist->orig_hub($origin_hub); + $dist->dest_hub($hub_ids[$_->{destinationIndex}]); + $dist->distance($_->{travelDistance}); + $pc->{editor}->runmethod('create', 'actor.org_unit_shipping_hub_distance', 'aoushd', $dist); + } + } + $pc->{editor}->xact_commit; +print Dumper(\%hub_distance_matrix); +=cut +1; \ No newline at end of file diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index b3b2b7a013..2a25ba52d1 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -1280,4 +1280,41 @@ CREATE TABLE actor.usr_privacy_waiver ( ); CREATE INDEX actor_usr_privacy_waiver_usr_idx ON actor.usr_privacy_waiver (usr); +ALTER TABLE actor.org_unit +ADD COLUMN shipping_hub_ou BIGINT REFERENCES actor.org_unit(id) ON DELETE SET NULL; + +CREATE OR REPLACE FUNCTION actor.list_org_unit_ancestor_shipping_hub(VARIADIC orgs NUMERIC[]) RETURNS TABLE(org_unit INT,hub INT) + AS +$func$ +DECLARE + rec record; + cur_org INT; + next_hub INT; + org_id INT; +BEGIN + FOREACH org_id IN ARRAY orgs LOOP + cur_org := org_id; + org_unit := cur_org; + LOOP + SELECT INTO next_hub actor.org_unit.shipping_hub_ou FROM actor.org_unit WHERE actor.org_unit.id = cur_org; + IF FOUND AND next_hub IS NOT NULL THEN + hub := next_hub; + return next; + EXIT; + END IF; + SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE actor.org_unit.id = cur_org; + EXIT WHEN cur_org IS NULL; + END LOOP; + END LOOP; + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE TABLE actor.org_unit_shipping_hub_distance ( + id SERIAL PRIMARY KEY, + orig_hub BIGINT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE, + dest_hub BIGINT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE, + distance INT NOT NULL +); + COMMIT; 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 ad3c3e5dfc..b95155801e 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -21305,6 +21305,45 @@ INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) aout.name = 'Consortium' AND (perm.code = 'ADMIN_GEOLOCATION_SERVICES' OR perm.code = 'VIEW_GEOLOCATION_SERVICES'); +-- Hold Targeter Geosort + +ALTER TABLE actor.org_unit +ADD COLUMN shipping_hub_ou BIGINT REFERENCES actor.org_unit(id) ON DELETE SET NULL; + +CREATE OR REPLACE FUNCTION actor.list_org_unit_ancestor_shipping_hub(VARIADIC orgs NUMERIC[]) RETURNS TABLE(org_unit INT,hub INT) + AS +$func$ +DECLARE + rec record; + cur_org INT; + next_hub INT; + org_id INT; +BEGIN + FOREACH org_id IN ARRAY orgs LOOP + cur_org := org_id; + org_unit := cur_org; + LOOP + SELECT INTO next_hub actor.org_unit.shipping_hub_ou FROM actor.org_unit WHERE actor.org_unit.id = cur_org; + IF FOUND AND next_hub IS NOT NULL THEN + hub := next_hub; + return next; + EXIT; + END IF; + SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE actor.org_unit.id = cur_org; + EXIT WHEN cur_org IS NULL; + END LOOP; + END LOOP; + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE TABLE actor.org_unit_shipping_hub_distance ( + id SERIAL PRIMARY KEY, + orig_hub BIGINT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE, + dest_hub BIGINT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE, + distance INT NOT NULL +); + ------------------- Disabled example A/T defintions ------------------------------ -- Create a "dummy" slot when applicable, and trigger the "offer curbside" events diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.actor_org_unit_shipping_hub.sql b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.actor_org_unit_shipping_hub.sql new file mode 100644 index 0000000000..0ceff4f596 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.actor_org_unit_shipping_hub.sql @@ -0,0 +1,42 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('xxxx', :eg_version); + +ALTER TABLE actor.org_unit +ADD COLUMN shipping_hub_ou BIGINT REFERENCES actor.org_unit(id) ON DELETE SET NULL; + +CREATE OR REPLACE FUNCTION actor.list_org_unit_ancestor_shipping_hub(VARIADIC orgs NUMERIC[]) RETURNS TABLE(org_unit INT,hub INT) + AS +$func$ +DECLARE + rec record; + cur_org INT; + next_hub INT; + org_id INT; +BEGIN + FOREACH org_id IN ARRAY orgs LOOP + cur_org := org_id; + org_unit := cur_org; + LOOP + SELECT INTO next_hub actor.org_unit.shipping_hub_ou FROM actor.org_unit WHERE actor.org_unit.id = cur_org; + IF FOUND AND next_hub IS NOT NULL THEN + hub := next_hub; + return next; + EXIT; + END IF; + SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE actor.org_unit.id = cur_org; + EXIT WHEN cur_org IS NULL; + END LOOP; + END LOOP; + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE TABLE actor.org_unit_shipping_hub_distance ( + id SERIAL PRIMARY KEY, + orig_hub BIGINT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE, + dest_hub BIGINT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE, + distance INT NOT NULL +); + +COMMIT; \ No newline at end of file diff --git a/Open-ILS/src/templates/opac/parts/library/core_info.tt2 b/Open-ILS/src/templates/opac/parts/library/core_info.tt2 index cdaf0473e4..9e45c43eb0 100644 --- a/Open-ILS/src/templates/opac/parts/library/core_info.tt2 +++ b/Open-ILS/src/templates/opac/parts/library/core_info.tt2 @@ -31,6 +31,7 @@ [%- IF ctx.library.mailing_address; %]
+ [%- IF ctx.library.mailing_address -%]

[% l('Mailing address') %]

[% ctx.mailing_address.street1 | html %] @@ -40,7 +41,17 @@ [% ctx.mailing_address.state | html %]
[% ctx.mailing_address.country | html %]
[% ctx.mailing_address.post_code | html %]
+ [%- IF ctx.mailing_address.latitude AND ctx.mailing_address.longitude -%] + + [%- END -%]
+ [%- END; -%]
[%- END; %] diff --git a/Open-ILS/src/templates/staff/admin/actor/org_unit/t_main_tab.tt2 b/Open-ILS/src/templates/staff/admin/actor/org_unit/t_main_tab.tt2 index daba717614..cbd29ab5e0 100644 --- a/Open-ILS/src/templates/staff/admin/actor/org_unit/t_main_tab.tt2 +++ b/Open-ILS/src/templates/staff/admin/actor/org_unit/t_main_tab.tt2 @@ -47,6 +47,18 @@
+ [% l('Shipping Hub OU') %] +
+
+ +
+
+
+
diff --git a/Open-ILS/tests/datasets/sql/assets_concerto.sql b/Open-ILS/tests/datasets/sql/assets_concerto.sql index 82e050fe03..2dfb087a02 100644 --- a/Open-ILS/tests/datasets/sql/assets_concerto.sql +++ b/Open-ILS/tests/datasets/sql/assets_concerto.sql @@ -8,6 +8,7 @@ SELECT evergreen.populate_call_number(4, '780 B', 'IMPORT CONCERTO', 2); -- BR1 SELECT evergreen.populate_call_number(5, '780 A', 'IMPORT CONCERTO', 2); -- BR2 SELECT evergreen.populate_call_number(6, '781 D', 'IMPORT CONCERTO', 2); -- BR3 SELECT evergreen.populate_call_number(7, '781 G', 'IMPORT CONCERTO', 2); -- BR4 +SELECT evergreen.populate_call_number(11, 'LB 782 G', 'IMPORT CONCERTO', 2); -- BR5 SELECT evergreen.populate_call_number(9, '780 R', 'IMPORT CONCERTO', 2); -- BM1 -- Create copies @@ -15,6 +16,7 @@ SELECT evergreen.populate_copy(4, 4, 'CONC40000', 'M'); -- BR1 SELECT evergreen.populate_copy(5, 5, 'CONC50000', 'M'); -- BR2 SELECT evergreen.populate_copy(6, 6, 'CONC60000', 'M'); -- BR3 SELECT evergreen.populate_copy(7, 7, 'CONC70000', 'M'); -- BR4 +SELECT evergreen.populate_copy(11, 11, 'CONC80000', 'M'); -- BR5 SELECT evergreen.populate_copy(9, 9, 'CONC90000', 'M'); -- BM1 SELECT evergreen.populate_copy(4, 4, 'CONC41000', 'M'); -- BR1 diff --git a/Open-ILS/tests/datasets/sql/env_create.sql b/Open-ILS/tests/datasets/sql/env_create.sql index 9e8d955915..84a18c279a 100644 --- a/Open-ILS/tests/datasets/sql/env_create.sql +++ b/Open-ILS/tests/datasets/sql/env_create.sql @@ -16,26 +16,26 @@ CREATE TABLE marcxml_import (id SERIAL PRIMARY KEY, marc TEXT, tag TEXT); * This will happily create duplicate addresses if given duplicate info. */ CREATE FUNCTION evergreen.create_aou_address - (owning_lib INTEGER, street1 TEXT, street2 TEXT, city TEXT, state TEXT, country TEXT, + (owning_lib INTEGER, street1 TEXT, street2 TEXT, city TEXT, state TEXT, county TEXT, country TEXT, post_code TEXT, address_type TEXT) RETURNS void AS $$ BEGIN - INSERT INTO actor.org_address (org_unit, street1, street2, city, state, country, post_code) - VALUES ($1, $2, $3, $4, $5, $6, $7); + INSERT INTO actor.org_address (org_unit, street1, street2, city, state, county, country, post_code) + VALUES ($1, $2, $3, $4, $5, $6, $7, $8); - IF $8 IS NULL THEN + IF $9 IS NULL THEN UPDATE actor.org_unit SET holds_address = currval('actor.org_address_id_seq'), ill_address = currval('actor.org_address_id_seq'), billing_address = currval('actor.org_address_id_seq'), mailing_address = currval('actor.org_address_id_seq') WHERE id = $1; END IF; - IF $8 ~ 'holds' THEN + IF $9 ~ 'holds' THEN UPDATE actor.org_unit SET holds_address = currval('actor.org_address_id_seq') WHERE id = $1; END IF; - IF $8 ~ 'interlibrary' THEN + IF $9 ~ 'interlibrary' THEN UPDATE actor.org_unit SET ill_address = currval('actor.org_address_id_seq') WHERE id = $1; END IF; - IF $8 ~ 'billing' THEN + IF $9 ~ 'billing' THEN UPDATE actor.org_unit SET billing_address = currval('actor.org_address_id_seq') WHERE id = $1; END IF; - IF $8 ~ 'mailing' THEN + IF $9 ~ 'mailing' THEN UPDATE actor.org_unit SET mailing_address = currval('actor.org_address_id_seq') WHERE id = $1; END IF; END diff --git a/Open-ILS/tests/datasets/sql/env_destroy.sql b/Open-ILS/tests/datasets/sql/env_destroy.sql index 61691943c8..e3b42fa71e 100644 --- a/Open-ILS/tests/datasets/sql/env_destroy.sql +++ b/Open-ILS/tests/datasets/sql/env_destroy.sql @@ -1,7 +1,7 @@ -- clean up our temp tables / functions DROP TABLE marcxml_import; -DROP FUNCTION evergreen.create_aou_address(INTEGER, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT); +DROP FUNCTION evergreen.create_aou_address(INTEGER, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT,TEXT); DROP FUNCTION evergreen.populate_call_number(INTEGER, TEXT, TEXT); DROP FUNCTION evergreen.populate_call_number(INTEGER, TEXT, TEXT, INTEGER); DROP FUNCTION evergreen.generate_price(); diff --git a/Open-ILS/tests/datasets/sql/libraries.sql b/Open-ILS/tests/datasets/sql/libraries.sql index 513dc2b7f2..34b548e523 100644 --- a/Open-ILS/tests/datasets/sql/libraries.sql +++ b/Open-ILS/tests/datasets/sql/libraries.sql @@ -3,6 +3,10 @@ INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES (3, 1, 2, 'SYS2', oils_i18n_gettext(3, 'Example System 2', 'aou', 'name')); INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES + (10, 1, 2, 'SYS3', oils_i18n_gettext(10, 'Example System 3', 'aou', 'name')); +INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES + (12, 1, 2, 'SYS4', oils_i18n_gettext(12, 'Example System 4', 'aou', 'name')); +INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES (4, 2, 3, 'BR1', oils_i18n_gettext(4, 'Example Branch 1', 'aou', 'name')); INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES (5, 2, 3, 'BR2', oils_i18n_gettext(5, 'Example Branch 2', 'aou', 'name')); @@ -11,41 +15,53 @@ INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES (7, 3, 3, 'BR4', oils_i18n_gettext(7, 'Example Branch 4', 'aou', 'name')); INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES + (11, 10, 3, 'BR5', oils_i18n_gettext(11, 'Example Branch 5', 'aou', 'name')); +INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES + (13, 12, 3, 'BR6', oils_i18n_gettext(13, 'Example Branch 6', 'aou', 'name')); +INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES (8, 4, 4, 'SL1', oils_i18n_gettext(8, 'Example Sub-library 1', 'aou', 'name')); INSERT INTO actor.org_unit (id, parent_ou, ou_type, shortname, name) VALUES (9, 6, 5, 'BM1', oils_i18n_gettext(9, 'Example Bookmobile 1', 'aou', 'name')); -INSERT INTO actor.org_lasso (id, name, global) VALUES (1000001, 'Even Branches', FALSE); -INSERT INTO actor.org_lasso_map (lasso, org_unit) VALUES (1000001, 5), (1000001, 7); -INSERT INTO actor.org_lasso (id, name, global) VALUES (1000002, 'Non-branches', TRUE); -INSERT INTO actor.org_lasso_map (lasso, org_unit) VALUES (1000002, 8), (1000002, 9); -- Address for the Consortium -SELECT evergreen.create_aou_address(1, '123 Main St.', NULL, 'Anywhere', 'GA', 'US', '30303', NULL); +SELECT evergreen.create_aou_address(1, '250 Georgia Ave SE #103', NULL, 'Atlanta', 'GA', 'Fulton', 'US', '30312', NULL); -- Addresses for System 1 -SELECT evergreen.create_aou_address(2, '234 Side St.', NULL, 'Anywhere', 'GA', 'US', '30304', NULL); +SELECT evergreen.create_aou_address(2, '1721 Waters Ave', NULL, 'Savannah', 'GA','Chatham', 'US', '31404', NULL); -- Addresses for System 2 -SELECT evergreen.create_aou_address(3, '345 Corner Crescent', NULL, 'Elsewhere', 'GA', 'US', '30335', NULL); +SELECT evergreen.create_aou_address(3, '831 Adams St', NULL, 'Macon', 'GA','Bibb', 'US', '31201', NULL); + +-- Addresses for System 3 +SELECT evergreen.create_aou_address(10, '215 N Lumpkin St', NULL, 'Athens', 'GA', 'Clarke', 'US', '30601', NULL); + +-- Addresses for System 4 +SELECT evergreen.create_aou_address(12, '625 Academy St NE', NULL, 'Gainesville', 'GA', 'Hall', 'US', '30501', NULL); -- Addresses for Branch 1 -SELECT evergreen.create_aou_address(4, 'BR1', '123 Main St.', 'Anywhere', 'GA', 'US', '30303', 'billing mailing'); -SELECT evergreen.create_aou_address(4, 'Holds and ILL', '125 Main St.', 'Anywhere', 'GA', 'US', '30303', 'interlibrary holds'); +SELECT evergreen.create_aou_address(4, '250 Georgia Ave SE #103', NULL, 'Atlanta', 'GA', 'Hall','US', '30312', 'billing mailing'); +SELECT evergreen.create_aou_address(4, '250 Georgia Ave SE #103', NULL, 'Atlanta', 'GA', 'Hall','US', '30312', 'interlibrary holds'); -- Addresses for Branch 2 -SELECT evergreen.create_aou_address(5, 'BR2', '234 Side St.', 'Anywhere', 'GA', 'US', '30304', 'mailing'); -SELECT evergreen.create_aou_address(5, 'BR2 - Billing', '234 Side St.', 'Anywhere', 'GA', 'US', '30304', 'billing'); -SELECT evergreen.create_aou_address(5, 'BR2 - Holds and ILL', '234 Side St.', 'Anywhere', 'GA', 'US', '30304', 'interlibrary holds'); +SELECT evergreen.create_aou_address(5, '1721 Waters Ave', NULL, 'Savannah', 'GA','Chatham', 'US', '31404', 'mailing'); +SELECT evergreen.create_aou_address(5, '1721 Waters Ave', NULL, 'Savannah', 'GA','Chatham', 'US', '31404', 'billing'); +SELECT evergreen.create_aou_address(5, '1721 Waters Ave', NULL, 'Savannah', 'GA','Chatham', 'US', '31404', 'interlibrary holds'); -- Addresses for Branch 3 -SELECT evergreen.create_aou_address(6, 'BR3', '347 Corner Crescent', 'Elsewhere', 'GA', 'US', '30335', NULL); +SELECT evergreen.create_aou_address(6, '831 Adams St', NULL, 'Macon', 'GA','Bibb', 'US', '31201', NULL); -- Addresses for Branch 4 -SELECT evergreen.create_aou_address(7, 'BR4', '446 Nowhere Road', 'Elsewhere', 'GA', 'US', '30404', 'mailing'); -SELECT evergreen.create_aou_address(7, 'BR4 - Billing Dept', '446 Nowhere Road', 'Elsewhere', 'GA', 'US', '30404', 'billing'); -SELECT evergreen.create_aou_address(7, 'BR4 - Holds and ILL', '756 Industrial Lane', 'Elsewhere', 'GA', 'US', '30304', 'interlibrary holds'); +SELECT evergreen.create_aou_address(7, '419 7th St', NULL, 'Augusta', 'GA', 'Richmond', 'US', '30901', 'mailing'); +SELECT evergreen.create_aou_address(7, '419 7th St', NULL, 'Augusta', 'GA', 'Richmond','US', '30901', 'billing'); +SELECT evergreen.create_aou_address(7, '419 7th St', NULL, 'Augusta', 'GA', 'Richmond','US', '30901', 'interlibrary holds'); + +-- Addresses for Branch 5 +SELECT evergreen.create_aou_address(11, '215 N Lumpkin St', NULL, 'Athens', 'GA', 'Clarke', 'US', '30601', NULL); + +-- Addresses for Branch 6 +SELECT evergreen.create_aou_address(13, '625 Academy St NE', NULL, 'Gainesville', 'GA', 'Hall', 'US', '30501', NULL); -- Hours for branches INSERT INTO actor.hours_of_operation (id, dow_0_open, dow_0_close, dow_1_open, dow_1_close, @@ -67,8 +83,19 @@ INSERT INTO actor.org_unit_setting(org_unit, name, value) VALUES (6, 'lib.info_url', '"http://br3.example.com"'), -- BR3 (7, 'lib.info_url', '"http://br4.example.com/info"'); -- BR4 +UPDATE actor.org_unit SET shipping_hub_ou = 4 WHERE id = 2; +UPDATE actor.org_unit SET shipping_hub_ou = 7 WHERE id = 3; +UPDATE actor.org_unit SET shipping_hub_ou = 11 WHERE id = 10; +UPDATE actor.org_unit SET shipping_hub_ou = 13 WHERE id = 12; +INSERT INTO actor.org_unit_shipping_hub_distance(orig_hub, dest_hub, distance) VALUES (4,4,0),(4,7,25),(4,11,50),(4,13,80), + (7,7,0),(7,4,25),(7,11,65),(7,13,35), + (11,7,65),(11,4,50),(11,11,0),(11,13,25), + (13,7,35),(13,4,80),(13,11,25),(13,13,0); + UPDATE actor.org_unit SET email = 'br1@example.com', phone = '(555) 555-0271' WHERE shortname = 'BR1'; UPDATE actor.org_unit SET email = 'br2@example.com', phone = '(555) 555-0272' WHERE shortname = 'BR2'; UPDATE actor.org_unit SET email = 'br3@example.com', phone = '(555) 555-0273' WHERE shortname = 'BR3'; UPDATE actor.org_unit SET email = 'br4@example.com', phone = '(555) 555-0274' WHERE shortname = 'BR4'; +UPDATE actor.org_unit SET email = 'br5@example.com', phone = '(555) 555-0275' WHERE shortname = 'BR5'; +UPDATE actor.org_unit SET email = 'br6@example.com', phone = '(555) 555-0276' WHERE shortname = 'BR6'; diff --git a/Open-ILS/tests/datasets/sql/transactions.sql b/Open-ILS/tests/datasets/sql/transactions.sql index cb9a73c699..3a73ff2f49 100644 --- a/Open-ILS/tests/datasets/sql/transactions.sql +++ b/Open-ILS/tests/datasets/sql/transactions.sql @@ -106,7 +106,9 @@ BEGIN 'T', bre.id, recipient.id, recipient.id, recipient.home_ou, FALSE, NULL ); + + -- title hold, circulator-placed bre := evergreen.next_bib(bre.id); EXIT WHEN bre IS NULL; @@ -143,6 +145,11 @@ BEGIN 'M', 42, 2, 2, 9, FALSE, NULL, '{"0":[{"_attr":"mr_hold_format","_val":"score"}]}' ); + -- title hold, resource sharing + PERFORM evergreen.populate_hold( + 'T', 9, 9, 9, + 13, FALSE, NULL + ); END $$; diff --git a/Open-ILS/web/conify/global/actor/org_unit.html b/Open-ILS/web/conify/global/actor/org_unit.html index faaac24e7f..2a3b59a5ed 100644 --- a/Open-ILS/web/conify/global/actor/org_unit.html +++ b/Open-ILS/web/conify/global/actor/org_unit.html @@ -193,6 +193,11 @@ editor_pane_parent_ou.validate(true); editor_pane_parent_ou.setValue( this.store.getValue( current_ou, 'parent_ou' ) ); } + + editor_pane_shipping_hub_ou.disabled = false; + editor_pane_shipping_hub_ou.required = false; + editor_pane_shipping_hub_ou.validate(true); + editor_pane_shipping_hub_ou.setValue( this.store.getValue( current_ou, 'shipping_hub_ou' ) ); editor_pane_opac_visible.setChecked( this.store.getValue( current_ou, 'opac_visible' ) == 't' ? true : false ); @@ -302,6 +307,26 @@
+ + &conify.org_unit.editor_pane.shipping_hub; + +
+ +
+ + &conify.org_unit.editor_pane.opac_visible; diff --git a/Open-ILS/web/opac/locale/en-US/conify.dtd b/Open-ILS/web/opac/locale/en-US/conify.dtd index c0cf5a7992..128637c441 100644 --- a/Open-ILS/web/opac/locale/en-US/conify.dtd +++ b/Open-ILS/web/opac/locale/en-US/conify.dtd @@ -32,6 +32,7 @@ + -- 2.11.0