[Koha-patches] [PATCH] New Feature Patron Synchronization
Michael Hafen
mdhafen at tech.washk12.org
Thu Dec 10 22:54:14 CET 2009
In admin set references to Koha borrowers fields to columns in a seperate
database per patron category. Branchcode and Cardnumber must be mapped.
In tools synchronize a patron category with information from the other
database. This will add any patrons, matched by the cardnumber mapping, that
are not in the Koha database. It will update the information in the Koha
database with information from the other database. Also it will delete any
patrons that are not matched in the other database, but only if they have no
books checked out, fines, or reserves.
---
C4/MembersExternal.pm | 650 ++++++++++++++++++++
Makefile.PL | 81 +++-
admin/members_via_external.pl | 145 +++++
etc/koha-conf.xml | 6 +
installer/data/Pg/kohastructure.sql | 18 +
.../optional/sample_patrons_from_external_db.sql | 17 +
.../optional/sample_patrons_from_external_db.txt | 3 +
installer/data/mysql/kohastructure.sql | 20 +
.../intranet-tmpl/prog/en/includes/admin-menu.inc | 1 +
.../prog/en/modules/admin/admin-home.tmpl | 2 +
.../en/modules/admin/members_via_external.tmpl | 126 ++++
.../en/modules/tools/external-member-sync.tmpl | 116 ++++
misc/koha-install-log | 7 +
rewrite-config.PL | 7 +
tools/external-member-sync.pl | 277 +++++++++
15 files changed, 1475 insertions(+), 1 deletions(-)
create mode 100644 C4/MembersExternal.pm
create mode 100755 admin/members_via_external.pl
create mode 100644 installer/data/mysql/en/optional/sample_patrons_from_external_db.sql
create mode 100644 installer/data/mysql/en/optional/sample_patrons_from_external_db.txt
create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/members_via_external.tmpl
create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/tools/external-member-sync.tmpl
create mode 100755 tools/external-member-sync.pl
diff --git a/C4/MembersExternal.pm b/C4/MembersExternal.pm
new file mode 100644
index 0000000..bc88c8c
--- /dev/null
+++ b/C4/MembersExternal.pm
@@ -0,0 +1,650 @@
+package C4::MembersExternal;
+
+# Copyright 2009 Michael Hafen
+#
+# This file is part of Koha.
+#
+# Koha is free software; you can redistribute it and/or modify it under the
+# terms of the GNU General Public License as published by the Free Software
+# Foundation; either version 2 of the License, or (at your option) any later
+# version.
+#
+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along with
+# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
+# Suite 330, Boston, MA 02111-1307 USA
+
+
+use strict;
+use warnings;
+use vars qw( %MembersExternal_Context );
+use Digest::MD5 qw(md5_base64);
+use C4::Context;
+
+our ($VERSION, at ISA, at EXPORT, at EXPORT_OK,$debug);
+
+BEGIN {
+ $VERSION = 3.00;
+ $debug = $ENV{DEBUG} || 0;
+ require Exporter;
+ @ISA = qw(Exporter);
+ #Get data
+ push @EXPORT, qw(
+ &GetMemberDetails_External
+ &ListMembers_External
+ &GetExternalMapping
+ &GetExternalMappedCategories
+ &GetExternalMappingsForCategory
+ &GetExternalNumCategoryMappings
+ );
+
+ #Modify Data
+ push @EXPORT, qw(
+ &ModExternalMapping
+ );
+
+ #Add Data
+ push @EXPORT, qw(
+ &AddExternalMapping
+ );
+
+ #Remove Data
+ push @EXPORT, qw(
+ &DelExternalMapping
+ );
+
+ # Private subs
+ #Get data
+ push @EXPORT_OK, qw(
+ &GetExternalField
+ &GetExternalAttrib
+ &GetExternalAllAttribs
+ );
+
+ #Check data
+ push @EXPORT_OK, qw(
+ &PatronInMappedCategory
+ );
+
+ #Connection handling
+ push @EXPORT_OK, qw(
+ &DBI_BuildQuery
+ );
+}
+
+%MembersExternal_Context = (
+ conn => undef, # DBI handle
+ engine => C4::Context->config('MembersExternalEngine'),
+ pass_method => C4::Context->config('MembersExternalPassMethod'),
+ host => C4::Context->config('MembersExternalHost'),
+ user => C4::Context->config('MembersExternalUser'),
+ pass => C4::Context->config('MembersExternalPass'),
+ schema => C4::Context->config('MembersExternalSchema'),
+ );
+
+=head1 NAME
+
+C4::MembersExternal - Perl Module extending C4::Members to allow getting member
+ information from sources other than the Koha database
+
+=head1 SYNOPSIS
+
+use C4::MembersExternal;
+
+=head1 DESCRIPTION
+
+This module contains routines for adding members/patrons/borrowers where the
+ information for those members/patrons/borrowers is held in a source other than
+ the Koha database
+
+=head1 VARIABLES
+
+There are a few configuration variable to handle. First is the method, which
+ is a dbi driver. Second is how the passwords are stored, either 'plain' or
+ 'md5'. Third the host, username, and password with access to the database.
+
+=head1 FUNCTIONS
+
+=over 2
+
+=item GetMemberDetails_External
+
+ $borrower = &GetMemberDetails_External($cardnumber);
+
+Returns information on a patron (borrower) by card number.
+
+C<$borrower> is a reference-to-hash with keys from the C<borrowers> table of
+ the Koha database for columns that have been mapped to an external database or
+ directory.
+
+=cut
+
+sub GetMemberDetails_External {
+ my ( $cardnumber, $category ) = @_;
+ my ( @columns, @filter, $query, $sth );
+ my ( $data, $result );
+
+ return $data unless ( $category = PatronInMappedCategory( undef, $cardnumber, $category ) );
+
+ @columns = GetExternalAllAttribs( $category );
+ my $filter_field = GetExternalAttrib( 'cardnumber', $category );
+ push @filter, { 'field' => $filter_field, 'op' => '=', 'value' => $cardnumber };
+ $query = DBI_BuildQuery( $category, \@columns, \@filter );
+ return {} unless ( defined $query );
+ $sth = $MembersExternal_Context{ conn }->prepare( $query ) or return {};
+ $sth->execute;
+ $data = $sth->fetchrow_hashref;
+
+ foreach my $attrib ( keys %$data ) {
+ my $field = GetExternalField( $attrib, $category );
+ $$result{ $field } = $$data{ $attrib };
+ }
+
+ return $result;
+}
+
+=item ListMembers_External
+ ( $Koha, $External ) = &ListMembers_External( $category, $branch );
+
+Creates two lists: C<Koha> and C<External> which list all the users in the Koha
+ database and the External database for a given patron C<category>. This is
+ used by the syncronize script to make sure the Koha database is in sync with
+ the External database for a few key fields.
+
+=cut
+
+sub ListMembers_External {
+ my ( $category, $branch ) = @_;
+ my ( %koha, $external );
+
+ my $dbh = C4::Context->dbh;
+ my $query = "SELECT * FROM borrowers";
+ $query .= " WHERE branchcode = ". $dbh->quote( $branch ) if ( $branch );
+ my $sth = $dbh->prepare( $query );
+ $sth->execute();
+ while ( my $data = $sth->fetchrow_hashref ) {
+ $$data{cardnumber} =~ s/^\s*//;
+ $$data{cardnumber} =~ s/\s*$//;
+ $koha{ $$data{cardnumber} } = $data;
+ }
+
+ my @filter;
+ my $cardfield = GetExternalAttrib( 'cardnumber', $category );
+ if ( $branch ) {
+ my $branchfield = GetExternalAttrib( 'branchcode', $category );
+ push @filter, { 'field' => $branchfield, 'op' => '=', 'value' => $branch };
+ }
+
+ $query = DBI_BuildQuery( $category, [ $cardfield ], \@filter );
+ return ( \%koha, {} ) unless ( defined $query );
+ $sth = $MembersExternal_Context{ conn }->prepare( $query );
+ $sth->execute;
+ while ( my ( $card ) = $sth->fetchrow ) {
+ $card =~ s/^\s*//;
+ $card =~ s/\s*$//;
+ $$external{ $card } = 1;
+ }
+
+ return ( \%koha, $external );
+}
+
+=item PatronInMappedCategory
+ $category = &PatronInMappedCategory( $borrowernumber, $cardnumber );
+
+Checkes the C<borrowers_external_structure> table to see if there is a mapping
+ set for the patrons patron category. Uses either C<borrowernumber> or
+ C<cardnumber>. Returns the patrons C<category> or undef.
+
+=cut
+
+sub PatronInMappedCategory {
+ my ( $borrnum, $cardnum, $cat ) = @_;
+ my $dbh = C4::Context->dbh;
+ my $sth;
+ my @inputs;
+ my $result;
+
+ my $query = "SELECT categorycode FROM borrowers WHERE ";
+ if ( $cat ) {
+ $result = $cat;
+ } elsif ( $borrnum ) {
+ $query .= "borrowernumber = ?";
+ push @inputs, $borrnum;
+ } elsif ( $cardnum ) {
+ $query .= "cardnumber = ?";
+ push @inputs, $cardnum;
+ } else {
+ warn "PatronInMappedCategory() couldn't find members categorycode without cardnumber or borrowernumber" if $debug;
+ return undef;
+ }
+
+ unless ( $cat ) {
+ $sth = $dbh->prepare( $query );
+ $sth->execute( @inputs );
+ ( $result ) = $sth->fetchrow;
+ }
+
+ my @mapped_cats_ar = GetExternalMappedCategories();
+ my %mapped_cats = map { $_ => 1 } @mapped_cats_ar;
+
+ $result = ( $mapped_cats{ $result } ) ? $result : undef;
+
+ return $result;
+}
+
+=item &GetExternalField
+ $column = &GetExternalField( $field, $category );
+
+handles the convertion of a database attribute back to the Koha field.
+
+=cut
+
+sub GetExternalField {
+ my ( $field, $category ) = @_;
+ my $column;
+ my $dbh = C4::Context->dbh;
+ my $query = "SELECT kohafield FROM borrowers_external_structure WHERE attrib LIKE ?";
+ $query .= " AND categorycode = ". $dbh->quote( $category ) if ( $category );
+ my $sth = $dbh->prepare( $query );
+ $sth->execute( "\%$field" ); # watch out for column aliases
+ ( $column ) = $sth->fetchrow;
+ return $column;
+}
+
+=item &GetExternalAttrib
+ $column = &GetExternalAttrib( $field, $category );
+
+handles the convertion of a Koha field to the database field
+
+=cut
+
+sub GetExternalAttrib {
+ my ( $field, $category ) = @_;
+ my $column;
+ my $dbh = C4::Context->dbh;
+ my $query = "SELECT attrib FROM borrowers_external_structure WHERE kohafield = ?";
+ $query .= " AND categorycode = ". $dbh->quote( $category ) if ( $category );
+ my $sth = $dbh->prepare( $query );
+ $sth->execute( $field );
+ ( $column ) = $sth->fetchrow;
+ $sth->finish;
+
+ return $column;
+}
+
+=item &GetExternalAllAttribs
+ $columns = &GetExternalAllAttribs( $category );
+
+Grabs all the Koha fields that are mapped for C<$category>. Returns an array-ref.
+
+=cut
+
+sub GetExternalAllAttribs {
+ my ( $category ) = @_;
+ my %columns;
+ my $dbh = C4::Context->dbh;
+ my $query;
+
+ # get the specific mappings for this category
+ $query = "SELECT kohafield,attrib FROM borrowers_external_structure WHERE categorycode = ?";
+ my $sth = $dbh->prepare( $query );
+ $sth->execute( $category );
+ while ( $_ = $sth->fetchrow_arrayref ) {
+ $columns{ $$_[0] } = $$_[1];
+ }
+
+ return values %columns;
+}
+
+=item &GetExternalMappedCategories
+
+=cut
+
+sub GetExternalMappedCategories {
+ my ( @all_cats );
+
+ my $dbh = C4::Context->dbh;
+ my $query = "SELECT categorycode FROM borrowers_external_structure GROUP BY categorycode";
+ my $sth = $dbh->prepare( $query );
+ $sth->execute;
+ unless ( $sth->rows == 0 ) {
+ while ( my ( $cat ) = $sth->fetchrow ) {
+ push @all_cats, $cat if ( $cat ne '' );
+ }
+ }
+ $sth->finish;
+
+ return @all_cats;
+}
+
+=item &GetExternalMappingsForCategory
+
+Returns an array-ref of hash-refs containing column mappings for a patron category.
+Basically just returns the database contents.
+
+=cut
+
+sub GetExternalMappingsForCategory {
+ my ( $category ) = @_;
+ my ( $mappings );
+
+ my $dbh = C4::Context->dbh;
+ my $query = "SELECT * FROM borrowers_external_structure WHERE categorycode = ?";
+ my $sth = $dbh->prepare( $query );
+ $sth->execute( $category );
+ $mappings = $sth->fetchall_arrayref({});
+ return $mappings;
+}
+
+=item &GetExternalNumCategoryMappings
+
+Returns the categorycode and number of column mappings. If a category is
+supplied returns only the number of mappings for that category.
+
+=cut
+
+sub GetExternalNumCategoryMappings {
+ my ( $category ) = @_;
+ my ( %mappings );
+ my @bind = ();
+
+ my $dbh = C4::Context->dbh;
+ my $query = "SELECT categorycode,COUNT(*) AS count FROM borrowers_external_structure ";
+ if ( $category ) {
+ $query .= "WHERE categorycode = ? ";
+ push @bind, $category;
+ }
+ $query .= "GROUP BY categorycode";
+ my $sth = $dbh->prepare( $query );
+ $sth->execute( @bind );
+ while ( my $row = $sth->fetchrow_hashref() ) {
+ $mappings{ $$row{categorycode} } = $$row{count};
+ }
+ return \%mappings;
+}
+
+=item &GetExternalMapping
+
+Accessor function to get the database row for a specific mapping. Input is
+either the externalid, or the category and koha field of the mapping. Output is
+a hash ref as returned from the database.
+
+=cut
+
+sub GetExternalMapping {
+ my ( $externalid, $category, $field ) = @_;
+ my $dbh = C4::Context->dbh;
+ my ( $sql, @params ) = ( '', () );
+ my $mapping;
+
+ if ( $externalid ) {
+ $sql = "SELECT * FROM borrowers_external_structure WHERE externalid = ?";
+ push @params, $externalid;
+ } elsif ( $category && $field ) {
+ $sql = "SELECT * FROM borrowers_external_structure WHERE categorycode = ? AND kohafield = ?";
+ push @params, ( $category, $field );
+ }
+ my $sth = $dbh->prepare( $sql );
+ $sth->execute( @params );
+ $mapping = $sth->fetchrow_hashref();
+
+ return $mapping;
+}
+
+=item &ModExternalMapping
+
+Accessor function to modify an existing mapping. Input is a hash with keys from
+the borrowers_external_structure table, and the values to update with
+
+=cut
+
+sub ModExternalMapping {
+ my ( $mapping ) = @_;
+ my $dbh = C4::Context->dbh;
+ my $sql = "
+ UPDATE borrowers_external_structure
+ SET categorycode = ?, liblibrarian = ?,
+ kohafield = ?, attrib = ?, filter = ?,
+ dblink = ?
+ WHERE externalid = ? ";
+ my $sth = $dbh->prepare( $sql );
+ $sth->execute(
+ $mapping->{categorycode}, $mapping->{liblibrarian},
+ $mapping->{kohafield}, $mapping->{attrib}, $mapping->{filter},
+ $mapping->{dblink},
+ $mapping->{externalid}
+ );
+}
+
+=item &AddExternalMapping
+
+Add a mapping for a patron field to a column in an external database. Input is
+a hash ref with the values to add.
+
+=cut
+
+sub AddExternalMapping {
+ my ( $mapping ) = @_;
+ my $dbh = C4::Context->dbh;
+ my $sql = "
+ INSERT INTO borrowers_external_structure
+ ( categorycode, liblibrarian,
+ kohafield, attrib, filter,
+ dblink )
+ VALUES ( ?, ?, ?, ?, ?, ? )";
+ my $sth = $dbh->prepare( $sql );
+ $sth->execute(
+ $mapping->{categorycode}, $mapping->{liblibrarian},
+ $mapping->{kohafield}, $mapping->{attrib}, $mapping->{filter},
+ $mapping->{dblink},
+ );
+
+ return $dbh->last_insert_id( '', '', 'borrowers_external_structure', 'externalid' );
+}
+
+=item &DelExternalMapping
+
+Delete a single column mapping for a given patron category. Input is the id of
+the column to delete.
+=cut
+
+sub DelExternalMapping {
+ my ( $externalid ) = @_;
+ my $dbh = C4::Context->dbh;
+ my $sql = "DELETE FROM borrowers_external_structure WHERE externalid = ?";
+ my $sth = $dbh->prepare( $sql );
+ $sth->execute( $externalid );
+}
+
+=item &DBI_BuildQuery
+
+Makes sure there's a connection to the database and builds the query to get the requested information.
+
+=cut
+
+sub DBI_BuildQuery {
+ my ( $category, $columns, $filters ) = @_;
+ my ( $query, $query2 );
+ my ( @l_columns, %tables, %weight, $first_table );
+
+ unless ( $MembersExternal_Context{ conn } ) {
+ # Connect to the external db
+ # this DSN format is required for the sybase driver.
+ # Also, Sybase when connecting to mssql doesn't like placeholders
+ my $engine = $MembersExternal_Context{ engine };
+ my $host = $MembersExternal_Context{ host };
+ my $schema = $MembersExternal_Context{ schema };
+ my $user = $MembersExternal_Context{ user };
+ my $pass = $MembersExternal_Context{ pass };
+ my $dsn = "DBI:$engine:server=$host;host=$host;database=$schema;sid=$schema";
+ $MembersExternal_Context{ conn } = DBI->connect( $dsn, $user, $pass );
+ unless ( defined $MembersExternal_Context{ conn } ) {
+ warn "MembersExternal: Couldn't connect to external DB!: ". $DBI::errstr if ( $debug );
+ return undef;
+ }
+ }
+
+ my $dbh = C4::Context->dbh;
+ $query = "SELECT dblink, filter FROM borrowers_external_structure WHERE dblink LIKE ?";
+ $query .= " AND categorycode = ". $dbh->quote( $category ) if ( $category );
+ my $sth = $dbh->prepare( $query );
+
+ $query2 = "SELECT dblink, filter FROM borrowers_external_structure WHERE dblink LIKE ? AND ( categorycode = '' OR categorycode IS NULL )";
+ my $sth2 = $dbh->prepare( $query2 );
+
+ # Clean up $columns
+ # Because the borrowers_external_structure fields must have table names,
+ # But the query results will not have them when using fetch_hashref
+ # So the parent sub probably cloned the fields without the table names
+ # to catch the value either way.
+ foreach my $row ( @$columns ) {
+ push @l_columns, $row if ( $row =~ /[^\.]+\.\S+/ );
+ }
+
+ # Figure out which tables we need now
+ foreach my $row ( @$filters, @l_columns ) {
+ my ( $table, $field );
+ if ( ref( $row ) eq 'HASH' ) {
+ $$row{ 'field' } =~ /([^\.\s]*)\.\S+/;
+ $table = $1;
+ } else {
+ $row =~ /([^\.\s]*)\.\S+/;
+ $table = $1;
+ }
+ $tables{ $table } = 1 if ( $table );
+ }
+ # And figure out how to join them
+ if ( scalar keys %tables > 1 ) {
+ foreach ( keys %tables ) {
+ dbi_buildquerychain( $_, \%tables, \%weight, $filters, $sth, $sth2 );
+ }
+ } else {
+ %weight = %tables;
+ my ( $table ) = keys %tables;
+ $sth->execute( "\%$table\%" ); # this table links to...
+ while ( my ( $link, $filt ) = $sth->fetchrow ) {
+ if ( $filt ) {
+ $filt =~ /([\w\.]+)\s*(\W*)\s*(.*?)$/;
+ push @$filters, { 'field' => $1, 'op' => $2, 'value' => $3 };
+ }
+ }
+ }
+
+ # put together the query using keys %weight and the dblinks from the db
+ # then add the $filters
+ my $first = 1;
+ $query = "SELECT ". join ',', @l_columns;
+ $query .= " FROM ";
+
+ my ( @tbls, @joins );
+ foreach my $tbl ( sort { $weight{$b} <=> $weight{$a} } keys %weight ) {
+ push @tbls, $tbl;
+ push @joins, $tables{ $tbl };
+ }
+ shift @joins; # don't need the first one.
+
+ $query .= join ' CROSS JOIN ', @tbls;
+ $query .= " WHERE " if ( @joins );
+ $query .= join ' AND ', @joins;
+
+ if ( @$filters ) {
+ my @filter_array;
+ foreach my $spec ( @$filters ) {
+ my $value = ( $$spec{ 'value' } =~ /\D+/ ) ? $MembersExternal_Context{ conn }->quote( $$spec{ 'value' } ) : $$spec{ 'value' };
+ push @filter_array, "$$spec{field} $$spec{op} $value";
+ }
+ unless ( $query =~ / WHERE / ) {
+ $query .= " WHERE ";
+ } else {
+ $query .= " AND ";
+ }
+ $query .= join " AND ", @filter_array;
+ }
+
+ $query =~ s/WHERE $//; # clean up trailing WHERE if there are no conditions
+
+ warn $query if ( $debug );
+ return $query;
+}
+
+# recursively called function to figure out how to join tables for a query
+
+sub dbi_buildquerychain {
+ my ( $table, $tables, $weight, $filters, $sth, $sth2 ) = @_;
+ my ( $found, $link, %links, $filt );
+ my ( $t1, $t2 );
+
+ our $depth++;
+ if ( $depth > 1000 ) { # Just in case
+ warn "I really hope you aren't actually trying to chain together more than 1000 tables" if ( $debug );
+ die;
+ }
+
+ $sth->execute( "\%$table\%" ); # this table links to...
+ if ( $sth->rows ) { # categorycode set or default set?
+ while ( ( $link, $filt ) = $sth->fetchrow ) {
+ $links{ $link } = ( $filt ) ? $filt : "1";
+ }
+ $sth->finish;
+ } else {
+ $sth2->execute( "\%$table\%" );
+ while ( ($link ) = $sth2->fetchrow ) {
+ $links{ $link } = ( $filt ) ? $filt : "1";
+ }
+ $sth2->finish;
+ }
+
+ foreach $link ( keys %links ) { # for all the links we have so far...
+ $link =~ /([^\.\s]+)\.[\S]+.*?\=.*?([^\.\s]+)\.[\S]+/;
+ ( $t1, $t2 ) = ( $1, $2 );
+ $found = ( $t1 eq $table ) ? $t2 : $t1; # the table this one links to
+
+ $$weight{ $found } = 0 unless exists $$weight{ $found }; # init their
+ $$weight{ $table } = 0 unless exists $$weight{ $table }; # weight
+ if ( exists $$tables{ $found } and $$tables { $found } ne "SEARCH" and $$weight{ $found } >= $$weight{ $table } ) {
+ $$weight{ $table }++; # we've seen $table and $found before
+ $$weight{ $found } += $$weight{ $table } + 1; # favor $found
+ $$tables{ $table } = $link;
+ if ( $links{ $link } ne "1" ) {
+ $links{ $link } =~ /([\w\.]+)\s*(\W*)\s*(.*?)$/;
+ push @$filters, { 'field' => $1, 'op' => $2, 'value' => $3 };
+ }
+ } elsif ( ! exists $$tables{ $found } ) {
+ my $temp = $$tables{ $table }; # we haven't see $found before
+ $$tables{ $table } = "SEARCH"; # search for it
+ my $chain = dbi_buildquerychain( $found, $tables, $weight, $filters, $sth, $sth2 );
+ if ( $temp ) {
+ $$tables{ $table } = $temp;
+ } else {
+ delete $$tables{ $table };
+ }
+ if ( $chain ) { # chain found and it's link recorded
+ $$weight{ $table }++;
+ $$weight{ $found } += $$weight{ $table } + 1;
+ $$tables{ $table } = $link;
+ }
+ }
+ delete $$weight{ $found } unless $$weight{ $found } > 0;
+ delete $$weight{ $table } unless $$weight{ $table } > 0;
+ }
+
+ $depth--;
+ return $$tables{ $table };
+}
+
+END { } # module clean-up code here (global destructor)
+
+1;
+
+__END__
+
+=back
+
+=head1 AUTHOR
+
+Michael Hafen for WCSD
+
+=cut
diff --git a/Makefile.PL b/Makefile.PL
index aeeba7f..619d745 100644
--- a/Makefile.PL
+++ b/Makefile.PL
@@ -383,6 +383,34 @@ System user account that will own Koha's files.
System group that will own Koha's files.
+=item MEMBERS_EXTERNAL_CONFIG
+
+Whether to configure the MembersViaExternal section of the config file.
+
+=item MEMBERS_EXTERNAL_ENGINE
+
+DBI Driver to access external database for patron information.
+
+=item MEMBERS_EXTERNAL_PASS_METHOD
+
+How the patron password is stored in the external database.
+
+=item MEMBERS_EXTERNAL_HOST
+
+The hostname / i.p. address of the external database server.
+
+=item MEMBERS_EXTERNAL_USER
+
+The username to access the external database server.
+
+=item MEMBERS_EXTERNAL_PASS
+
+The password to access the external database server.
+
+=item MEMBERS_EXTERNAL_SCHEMA
+
+The database on the external server to get patron information from.
+
=back
=cut
@@ -415,7 +443,14 @@ my %config_defaults = (
'PATH_TO_ZEBRA' => '',
'USE_MEMCACHED' => 'no',
'MEMCACHED_SERVERS' => '127.0.0.1:11211',
- 'MEMCACHED_NAMESPACE' => 'KOHA'
+ 'MEMCACHED_NAMESPACE' => 'KOHA',
+ 'MEMBERS_EXTERNAL_CONFIG' => 'no',
+ 'MEMBERS_EXTERNAL_ENGINE' => 'mysql',
+ 'MEMBERS_EXTERNAL_PASS_METHOD' => 'md5',
+ 'MEMBERS_EXTERNAL_HOST' => 'localhost',
+ 'MEMBERS_EXTERNAL_USER' => 'kohauser',
+ 'MEMBERS_EXTERNAL_PASS' => 'zebrastrips',
+ 'MEMBERS_EXTERNAL_SCHEMA' => 'externaldatabase',
);
# set some default configuration options based on OS
@@ -453,6 +488,8 @@ my %valid_config_values = (
'ZEBRA_LANGUAGE' => { 'en' => 1, 'fr' => 1 }, # FIXME should generate from contents of distribution
'RUN_DATABASE_TESTS' => { 'yes' => 1, 'no' => 1 },
'USE_MEMCACHED' => { 'yes' => 1, 'no' => 1 },
+ 'MEMBERS_EXTERNAL_CONFIG' => { 'yes' => 1, 'no' => 1 },
+ 'MEMBERS_EXTERNAL_PASS_METHOD' => { 'md5' => 1, 'plain' => 1 },
);
# get settings from command-line
@@ -1114,6 +1151,48 @@ Memcached namespace?);
$config{'MEMCACHED_NAMESPACE'} = _get_value('MEMCACHED_NAMESPACE', $msg, $defaults->{'MEMCACHED_NAMESPACE'}, $valid_values, $install_log_values);
}
+ $msg = q(
+The MembersViaExternal feature allows you to chose columns from tables in a database seperate from Koha, even on a different server. Certain information about the database and the server it is on would need to be set now.
+
+Configure the MembersViaExternal feature now?);
+ $msg .= _add_valid_values_disp('MEMBERS_EXTERNAL_CONFIG', $valid_values);
+ $config{'MEMBERS_EXTERNAL_CONFIG'} = _get_value('MEMBERS_EXTERNAL_CONFIG', $msg, $defaults->{'MEMBERS_EXTERNAL_CONFIG'}, $valid_values, $install_log_values);
+
+ if ($config{'MEMBERS_EXTERNAL_CONFIG'} eq 'yes') {
+ $msg = q(
+Since you've chosen to configure MembersViaExternal, you must
+specify the host, database, user, and password to access the
+service.
+);
+ $msg = q(
+Driver for the external database may be one of mysql, Pg, Sybase, Oracle, or
+some other DBI driver.
+DBI Driver for external database?);
+ $config{'MEMBERS_EXTERNAL_ENGINE'} = _get_value('MEMBERS_EXTERNAL_ENGINE', $msg, $defaults->{'MEMBERS_EXTERNAL_ENGINE'}, $valid_values, $install_log_values);
+
+ $msg = q(
+Hostname or I.P. Address of the server for the external database?);
+ $config{'MEMBERS_EXTERNAL_HOST'} = _get_value('MEMBERS_EXTERNAL_HOST', $msg, $defaults->{'MEMBERS_EXTERNAL_HOST'}, $valid_values, $install_log_values);
+
+ $msg = q(
+User with access to the external database?);
+ $config{'MEMBERS_EXTERNAL_USER'} = _get_value('MEMBERS_EXTERNAL_USER', $msg, $defaults->{'MEMBERS_EXTERNAL_USER'}, $valid_values, $install_log_values);
+
+ $msg = q(
+Password for the user with access to the external database?);
+ $config{'MEMBERS_EXTERNAL_PASS'} = _get_value('MEMBERS_EXTERNAL_PASS', $msg, $defaults->{'MEMBERS_EXTERNAL_PASS'}, $valid_values, $install_log_values);
+
+ $msg = q(
+Name of the external database?);
+ $config{'MEMBERS_EXTERNAL_SCHEMA'} = _get_value('MEMBERS_EXTERNAL_SCHEMA', $msg, $defaults->{'MEMBERS_EXTERNAL_SCHEMA'}, $valid_values, $install_log_values);
+
+ $msg = q(
+How are patron passwords stored in the external database?);
+ $msg .= _add_valid_values_disp('MEMBERS_EXTERNAL_PASS_METHOD', $valid_values);
+ $config{'MEMBERS_EXTERNAL_PASS_METHOD'} = _get_value('MEMBERS_EXTERNAL_PASS_METHOD', $msg, $defaults->{'MEMBERS_EXTERNAL_PASS_METHOD'}, $valid_values, $install_log_values);
+
+ }
+
$msg = q(
diff --git a/admin/members_via_external.pl b/admin/members_via_external.pl
new file mode 100755
index 0000000..69817a0
--- /dev/null
+++ b/admin/members_via_external.pl
@@ -0,0 +1,145 @@
+#!/usr/bin/perl
+
+# This file is part of Koha.
+#
+# Koha is free software; you can redistribute it and/or modify it under the
+# terms of the GNU General Public License as published by the Free Software
+# Foundation; either version 2 of the License, or (at your option) any later
+# version.
+#
+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along with
+# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
+# Suite 330, Boston, MA 02111-1307 USA
+#
+# Written by Michael Hafen mdhafen at tech.washk12.org on Mar. 2008
+
+
+=head1 members_via_external.pl
+
+Page to configure the MembersViaExternal feature. This is for working with the
+database table, borrowers_external_structure, which controls which columns for
+which patron categories are syncronized with the external database.
+
+=cut
+
+use strict;
+use warnings;
+use CGI;
+use C4::Auth;
+use C4::Output;
+use C4::Members; # GetBorrowercategoryList
+use C4::MembersExternal; # GetExternalMappedCategories
+# GetExternalMappingsForCategory GetExternalNumCategoryMappings
+# GetExternalMapping ModExternalMapping AddExternalMapping DelExternalMapping
+
+my $cgi = new CGI;
+
+# getting the template
+my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
+ {
+ template_name => "admin/members_via_external.tmpl",
+ query => $cgi,
+ type => "intranet",
+ authnotrequired => 0,
+ flagsrequired => { parameters => 1 },
+ }
+);
+
+my $dbh = C4::Context->dbh;
+my $op = $cgi->param( 'op' ) || '';
+my $category = $cgi->param('categorycode') || '';
+my @categories = GetExternalMappedCategories();
+my ( $numdeleted, $numadded, $numchanged, @errors );
+my $nummappings = GetExternalNumCategoryMappings();
+
+if ( $op ) {
+ unless ( @categories || $op eq 'Add' ) {
+ push @errors, {
+ NO_MAPPINGS => 1,
+ };
+ } else {
+ if ( $op eq 'Change' ) {
+ my $externalid = $cgi->param( 'externalid' );
+ if ( my $mapping = GetExternalMapping( $externalid ) ) {
+ my $new_map = {};
+ $$new_map{ externalid } = $externalid;
+ $$new_map{ categorycode } = $category || $$mapping{ categorycode };
+ $$new_map{ liblibrarian } = $cgi->param( 'liblibrarian' ); # this can be empty
+ $$new_map{ kohafield } = $cgi->param( 'kohafield' ) || $$mapping{ kohafield };
+ $$new_map{ attrib } = $cgi->param( 'attrib' ) || $$mapping{ attrib };
+ $$new_map{ filter } = $cgi->param( 'filter' ); # this can be empty
+ $$new_map{ dblink } = $cgi->param( 'dblink' ) || $$mapping{ dblink };
+ ModExternalMapping( $new_map );
+ $numchanged++;
+ }
+ } elsif ( $op eq 'Add' ) {
+ my $new_map = {};
+ $$new_map{ categorycode } = $category;
+ $$new_map{ liblibrarian } = $cgi->param( 'liblibrarian' );
+ $$new_map{ kohafield } = $cgi->param( 'kohafield' );
+ $$new_map{ attrib } = $cgi->param( 'attrib' );
+ $$new_map{ filter } = $cgi->param( 'filter' );
+ $$new_map{ dblink } = $cgi->param( 'dblink' );
+ if ( my $mapping = GetExternalMapping( undef, $category, $$new_map{kohafield} ) ) {
+ push @errors, {
+ MAPPING_EXISTS => 1,
+ };
+ } else {
+ AddExternalMapping( $new_map );
+ $numadded++;
+ }
+ } elsif ( $op eq 'Delete' ) {
+ my $externalid = $cgi->param( 'externalid' );
+ DelExternalMapping( $externalid );
+ $numdeleted++;
+ } elsif ( $op eq 'Delete All' ) {
+ my $mappings = GetExternalMappingsForCategory( $category );
+ foreach my $map ( @$mappings ) {
+ DelExternalMapping( $map->{externalid} );
+ $numdeleted++;
+ }
+ } else {
+ push @errors, {
+ INVALID_OP => 1,
+ };
+ }
+ @categories = GetExternalMappedCategories(); # rebuild after changes.
+ }
+}
+
+my $allcategories = GetBorrowercategoryList();
+my @categoryloop;
+foreach my $this_category ( @$allcategories ) {
+ my $cat = $$this_category{ categorycode };
+ my $mappings = [];
+ if ( $$nummappings{ $cat } ) {
+ $mappings = GetExternalMappingsForCategory( $cat );
+ } else {
+ $$nummappings{ $cat } = '0';
+ }
+ push @categoryloop, {
+ categorycode => $cat,
+ categoryname => $this_category->{description},
+ num_mappings => $$nummappings{ $cat },
+ mappingsloop => $mappings,
+ categorycodeselected => ( $cat eq $category ),
+ };
+}
+
+$template->param(
+ categories => scalar @categories,
+ categoryloop => \@categoryloop,
+ op => $op,
+ categorycode => $category,
+ num_deleted => $numdeleted,
+ num_added => $numadded,
+ num_changed => $numchanged,
+ errors => \@errors,
+ );
+
+#writing the template
+output_html_with_http_headers $cgi, $cookie, $template->output;
diff --git a/etc/koha-conf.xml b/etc/koha-conf.xml
index 3feb98e..b068c12 100644
--- a/etc/koha-conf.xml
+++ b/etc/koha-conf.xml
@@ -178,6 +178,12 @@ __PAZPAR2_TOGGLE_XML_POST__
<port>__DB_PORT__</port>
<user>__DB_USER__</user>
<pass>__DB_PASS__</pass>
+ <MembersExternalEngine>__MEMBERS_EXTERNAL_ENGINE__</MembersExternalEngine>
+ <MembersExternalHost>__MEMBERS_EXTERNAL_HOST__</MembersExternalHost>
+ <MembersExternalUser>__MEMBERS_EXTERNAL_USER__</MembersExternalUser>
+ <MembersExternalPass>__MEMBERS_EXTERNAL_PASS__</MembersExternalPass>
+ <MembersExternalSchema>__MEMBERS_EXTERNAL_SCHEMA__</MembersExternalSchema>
+ <MembersExternalPassMethod>__MEMBERS_EXTERNAL_PASS_METHOD__</MembersExternalPassMethod>
<biblioserver>biblios</biblioserver>
<biblioservershadow>1</biblioservershadow>
<authorityserver>authorities</authorityserver>
diff --git a/installer/data/Pg/kohastructure.sql b/installer/data/Pg/kohastructure.sql
index 12967dc..9fc472f 100644
--- a/installer/data/Pg/kohastructure.sql
+++ b/installer/data/Pg/kohastructure.sql
@@ -527,6 +527,24 @@ CREATE INDEX borrowers_branchcode_idx ON borrowers (branchcode);
CREATE INDEX borrowers_borrowernumber_idx ON borrowers (borrowernumber);
CREATE INDEX borrowers_categorycode_idx ON borrowers (categorycode);
+--
+-- Table structure for table borrowers_external_structure
+--
+
+--DROP TABLE IF EXISTS borrowers_external_structure;
+
+CREATE TABLE borrowers_external_structure (
+ externalid BIGSERIAL UNIQUE,
+ liblibrarian varchar(255) NOT NULL default '',
+ kohafield varchar(40) default NULL,
+ attrib varchar(255) default NULL,
+ dblink varchar(64) default NULL,
+ categorycode varchar(10) NOT NULL default ''
+);
+CREATE INDEX bes_externalid_idx ON borrowers_external_structure (externalid);
+CREATE INDEX bes_kohafield_idx ON borrowers_external_structure (kohafield);
+CREATE INDEX bes_attrib_idx ON borrowers_external_structure (attrib);
+
--
-- Table structure for table branchcategories
--
diff --git a/installer/data/mysql/en/optional/sample_patrons_from_external_db.sql b/installer/data/mysql/en/optional/sample_patrons_from_external_db.sql
new file mode 100644
index 0000000..0b6a4b4
--- /dev/null
+++ b/installer/data/mysql/en/optional/sample_patrons_from_external_db.sql
@@ -0,0 +1,17 @@
+INSERT INTO `borrowers_external_structure`
+VALUES
+(1,'First name','firstname','stugrp_active.firstname','stugrp_active.suniq = studemo.suniq','','ST'),
+(2,'Branch','branchcode','stugrp_active.schoolc','stugrp_active.suniq = studemo.suniq','','ST'),
+(3,'Card Number','cardnumber','stugrp_active.ident','stugrp_active.suniq = studemo.suniq','active = 1','ST'),
+(4,'Phone','phone','studemo.phnnumber','stugrp_active.suniq = studemo.suniq','','ST'),
+(5,'City','city','stugrp_active.homecity','stugrp_active.suniq = studemo.suniq','','ST'),
+(6,'Gender','sex','stugrp_active.gender','stugrp_active.suniq = studemo.suniq','','ST'),
+(7,'Zip / Postal Code','zipcode','stugrp_active.homezip','stugrp_active.suniq = studemo.suniq','','ST'),
+(8,'Last Name','surname','stugrp_active.lastname','stugrp_active.suniq = studemo.suniq','','ST'),
+(9,'Street Address','address','stugrp_active.homeaddr1','stugrp_active.suniq = studemo.suniq','','ST'),
+(10,'Home Room Teacher','sort2','stugrp_active.advisor','stugrp_active.suniq = studemo.suniq','','ST'),
+(11,'Date of Birth','dateofbirth','CONVERT( VARCHAR(11), stugrp_active.birthdate, 120 ) AS DateOfBirth','stugrp_active.suniq = studemo.suniq','','ST'),
+(12,'Login Name','userid','Users.username','Users.username = studemo.ident','','ST'),
+(13,'Prefered Name','othernames','studemo.nickname','stugrp_active.suniq = studemo.suniq','','ST'),
+(14,'Graduation Year','sort1','studemo.gradyear','stugrp_active.suniq = studemo.suniq','','ST'),
+(15,'Login Password','password','Users.password','Users.username = studemo.ident','','ST');
diff --git a/installer/data/mysql/en/optional/sample_patrons_from_external_db.txt b/installer/data/mysql/en/optional/sample_patrons_from_external_db.txt
new file mode 100644
index 0000000..0135a30
--- /dev/null
+++ b/installer/data/mysql/en/optional/sample_patrons_from_external_db.txt
@@ -0,0 +1,3 @@
+Patrons Via External Sample Mapping
+
+This maps the ST (Student) patron category to certain fields in a view in an MS Sql database used for Student Information.
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index a1bf59b..471ec5e 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -310,6 +310,26 @@ CREATE TABLE `branch_item_rules` (
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
+-- Table structure for table `borrowers_external_structure`
+--
+
+DROP TABLE IF EXISTS `borrowers_external_structure`;
+CREATE TABLE `borrowers_external_structure` (
+ `externalid` int(11) NOT NULL auto_increment,
+ `liblibrarian` varchar(255) NOT NULL default '',
+ `kohafield` varchar(40) default NULL,
+ `attrib` varchar(255) default NULL,
+ `dblink` varchar(64) default NULL,
+ `filter` varchar(64) default NULL,
+ `categorycode` varchar(10) NOT NULL default '',
+ PRIMARY KEY (`externalid`),
+ KEY `bes_k_kohafield` (`kohafield`),
+ KEY `bes_k_attrib` (`attrib`),
+ KEY `bes_k_categorycode` (`categorycode`),
+ CONSTRAINT `bes_fk_categorycode` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
-- Table structure for table `branchcategories`
--
diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/admin-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/admin-menu.inc
index c5919e6..021f125 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/includes/admin-menu.inc
+++ b/koha-tmpl/intranet-tmpl/prog/en/includes/admin-menu.inc
@@ -17,6 +17,7 @@
<li><a href="/cgi-bin/koha/admin/cities.pl">Cities and towns</a></li>
<li><a href="/cgi-bin/koha/admin/roadtype.pl">Road types</a></li>
<li><a href="/cgi-bin/koha/admin/patron-attr-types.pl">Patron attribute types</a></li>
+ <li><a href="/cgi-bin/koha/admin/members_via_external.pl">Patron Synchronization Settings</a></li>
<li><a href="/cgi-bin/koha/admin/smart-rules.pl">Circulation and fines rules</a></li>
<li><a href="/cgi-bin/koha/admin/branch_transfer_limits.pl">Library transfer limits</a></li>
<li><a href="/cgi-bin/koha/admin/item_circulation_alerts.pl">Item circulation alerts</a></li>
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tmpl
index 25d43b6..e1f253a 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tmpl
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tmpl
@@ -47,6 +47,8 @@
<dd>Define road types (street, avenue, way, etc.). Road types display as authorized values when adding/editing patrons and can be used in geographic statistics.</dd>
<dt><a href="/cgi-bin/koha/admin/patron-attr-types.pl">Patron attribute types</a></dt>
<dd>Define extended attributes (identifiers and statistical categories) for patron records</dd>
+ <dt><a href="/cgi-bin/koha/admin/members_via_external.pl">Patron Synchronization Settings</a></dt>
+ <dd>Set references for patron fields to be synchronized with information from a seperate database.</dd>
<dt><a href="/cgi-bin/koha/admin/smart-rules.pl">Circulation and fines rules</a></dt>
<dd>Define circulation and fines rules for combinations of libraries, patron categories, and item types</dd>
<dt><a href="/cgi-bin/koha/admin/branch_transfer_limits.pl">Library Transfer Limits</a></dt>
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/members_via_external.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/members_via_external.tmpl
new file mode 100644
index 0000000..fd3bd00
--- /dev/null
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/members_via_external.tmpl
@@ -0,0 +1,126 @@
+<!-- TMPL_INCLUDE NAME="doc-head-open.inc" -->
+<title>Koha › Admin › Patron Synchronization Settings</title>
+<!-- TMPL_INCLUDE NAME="doc-head-close.inc" -->
+<script type="text/javascript">
+//<![CDATA[
+ $(document).ready(function() {
+ $('#categories > ul').tabs();
+ });
+//]]>
+</script>
+</head>
+<body>
+
+<!-- TMPL_INCLUDE NAME="header.inc" -->
+<!-- TMPL_INCLUDE NAME="patron-search.inc"-->
+
+<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> › <a href="/cgi-bin/koha/admin/admin-home.pl">Admin</a> › Patrons Synchronization Settings</div>
+
+<div id="doc3" class="yui-t2">
+ <div id="bd">
+ <div id="yui-main">
+<!-- TMPL_IF name="op" -->
+ <div class="yui-b">
+ <div class="dialog message">
+ <!-- TMPL_IF NAME="errors" -->
+ <h1>There are errors</h1>
+ <!-- TMPL_ELSE -->
+ <h1>Changes for Patron Category Mapping</h1>
+ </div>
+ <ul class="data">
+ <!-- TMPL_IF NAME="num_deleted" -->
+ <li><!-- TMPL_VAR NAME="num_deleted" --> Column Mappings Removed.</li>
+ <!-- /TMPL_IF -->
+ <!-- TMPL_IF NAME="num_added" -->
+ <li><!-- TMPL_VAR NAME="num_added" --> Column Mappings Added.</li>
+ <!-- /TMPL_IF -->
+ <!-- TMPL_IF NAME="num_changed" -->
+ <li><!-- TMPL_VAR NAME="num_changed" --> Column Mappings Changed.</li>
+ <!-- /TMPL_IF -->
+ </ul>
+ <!-- /TMPL_IF -->
+ <!-- TMPL_IF NAME="errors" -->
+ <table class="data">
+ <!-- TMPL_LOOP NAME="errors" -->
+ <tr>
+ <td>
+ <!-- TMPL_IF NAME="NO_MAPPINGS" -->Attempted operation when no patron categories are mapped. That won't work.<!-- /TMPL_IF -->
+ <!-- TMPL_IF NAME="MAPPING_EXISTS" -->Patron field already mapped for this category. Please delete or change the existing mapping.<!-- /TMPL_IF -->
+ <!-- TMPL_IF NAME="INVALID_OP" -->Unknown operation. What are you trying to do?<!-- /TMPL_IF -->
+ </td>
+ </tr>
+ <!-- /TMPL_LOOP -->
+ </table>
+ <!-- /TMPL_IF -->
+ <a href="/cgi-bin/koha/admin/members_via_external.pl?categorycode=<!-- TMPL_VAR NAME="categorycode" -->" class="cancel">Back</a>
+ </div>
+<!-- TMPL_ELSE -->
+ <div class="yui-b">
+ <h1>Patron Synchronization Settings</h1>
+ <div id="categories" class="toptabs">
+ <h5>Choose a category</h5>
+ <ul>
+ <!-- TMPL_LOOP NAME="categoryloop" -->
+ <!-- TMPL_IF NAME="categorycodeselected" -->
+ <li class="ui-tabs-selected">
+ <!-- TMPL_ELSE -->
+ <li>
+ <!-- /TMPL_IF -->
+ <a href="/cgi-bin/koha/admin/members_via_external.pl#<!-- TMPL_VAR NAME="categorycode" -->"><!-- TMPL_VAR NAME="categoryname" --> (<!-- TMPL_VAR NAME="num_mappings" -->)</a></li>
+ <!-- /TMPL_LOOP -->
+ </ul>
+ </div>
+
+ <!-- TMPL_LOOP NAME="categoryloop" -->
+ <div id="<!-- TMPL_VAR NAME="categorycode" -->">
+ <table>
+ <tr><th>Description</th><th>Patron Field</th><th>Database Column</th><th>Filter</th><th>Table Link String</th><th> </th></tr>
+ <tbody>
+ <!-- TMPL_IF NAME="mappingsloop" -->
+ <!-- TMPL_LOOP NAME="mappingsloop" -->
+ <form method="post" action="/cgi-bin/koha/admin/members_via_external.pl">
+ <input type="hidden" name="categorycode" value="<!-- TMPL_VAR NAME="categorycode" -->">
+ <input type="hidden" name="externalid" value="<!-- TMPL_VAR NAME="externalid" -->">
+ <tr>
+ <td><input name="liblibrarian" value="<!-- TMPL_VAR NAME="liblibrarian" -->"></td>
+ <td><input name="kohafield" value="<!-- TMPL_VAR NAME="kohafield" -->"></td>
+ <td><input name="attrib" value="<!-- TMPL_VAR NAME="attrib" -->"></td>
+ <td><input name="filter" value="<!-- TMPL_VAR NAME="filter" -->"></td>
+ <td><input name="dblink" value="<!-- TMPL_VAR NAME="dblink" -->"></td>
+ <td>
+ <input type="submit" name="op" value="Change" class="submit" />
+ <input type="submit" name="op" value="Delete" class="submit" />
+ </td>
+ </tr>
+ </form>
+ <!-- /TMPL_LOOP -->
+ <!-- /TMPL_IF -->
+ <form method="post" action="/cgi-bin/koha/admin/members_via_external.pl">
+ <input type="hidden" name="categorycode" value="<!-- TMPL_VAR NAME="categorycode" -->">
+ <tr>
+ <td><input name="liblibrarian" value=""></td>
+ <td><input name="kohafield" value=""></td>
+ <td><input name="attrib" value=""></td>
+ <td><input name="filter" value=""></td>
+ <td><input name="dblink" value=""></td>
+ <td>
+ <input type="submit" name="op" value="Add" class="submit" />
+ </td>
+ </tr>
+ </form>
+ </tbody>
+ </table>
+ <form method="post" action="/cgi-bin/koha/admin/members_via_external.pl">
+ <input type="hidden" name="categorycode" value="<!-- TMPL_VAR NAME="categorycode" -->">
+ <input type="submit" name="op" value="Delete All" class="submit" />
+ </form>
+ </div>
+ <!-- /TMPL_LOOP -->
+ </div>
+<!-- /TMPL_IF -->
+</div>
+<div class="yui-b noprint">
+ <!-- TMPL_INCLUDE NAME="admin-menu.inc" -->
+</div>
+</div>
+<!-- TMPL_INCLUDE NAME="intranet-bottom.inc" -->
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/external-member-sync.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/external-member-sync.tmpl
new file mode 100644
index 0000000..6989daf
--- /dev/null
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/external-member-sync.tmpl
@@ -0,0 +1,116 @@
+<!-- TMPL_INCLUDE NAME="doc-head-open.inc" -->
+<title>Koha › Tools › Syncronize External Patrons</title>
+<!-- TMPL_INCLUDE NAME="doc-head-close.inc" -->
+</head>
+<body>
+
+<!-- TMPL_INCLUDE NAME="header.inc" -->
+<!-- TMPL_INCLUDE NAME="patron-search.inc"-->
+
+<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> › <a href="/cgi-bin/koha/tools/tools-home.pl">Tools</a> › Syncronize External Patrons </div>
+
+<div id="doc3" class="yui-t2">
+ <div id="bd">
+ <div id="yui-main">
+<!-- TMPL_IF name="op" -->
+ <div class="yui-b">
+ <div class="yui-g">
+ <div class="yui-u first">
+ <div class="dialog message">
+ <!-- TMPL_IF NAME="finished" -->
+ <h1>Results of Patron Syncronization</h1>
+ <!-- TMPL_ELSE -->
+ <h1>Confirm changes for Patron Syncronization</h1>
+ <!-- /TMPL_IF -->
+ </div>
+ <ul class="data">
+ <li><!-- TMPL_VAR NAME ="num_deleted" --> Removed from Koha.</li>
+ <li><!-- TMPL_VAR NAME="num_added" --> Added to Koha.</li>
+ <li><!-- TMPL_VAR NAME="num_changed" --> Changed in Koha.</li>
+ <li><!-- TMPL_VAR NAME="total" --> In External Database.</li>
+ </ul>
+ <!-- TMPL_IF NAME="confirm" -->
+ <form method="post" action="/cgi-bin/koha/tools/external-member-sync.pl">
+ <input type="hidden" name="confirmed" value="1"/>
+ <input type="hidden" name="branch" value="<!-- TMPL_VAR NAME="branch" -->"/>
+ <input type="hidden" name="category" value="<!-- TMPL_VAR NAME="categorycode" -->"/>
+ <fieldset class="action">
+ <input type="submit" name="op" value="Sync" class="submit" />
+ <a href="/cgi-bin/koha/tools/external-member-sync.pl" class="cancel">Cancel</a>
+ </fieldset>
+ </form>
+ <!-- /TMPL_IF -->
+ <table class="data">
+ <legend>Report</legend>
+ <thead><tr><th>Cardnumber</th><th>Name</th><th>Report</th></tr></thead>
+ <!-- TMPL_LOOP name="report" -->
+ <tr>
+ <td><!-- TMPL_VAR NAME="cardnumber" --></td>
+ <td><!-- TMPL_VAR NAME="name" --></td>
+ <td>
+ <!-- TMPL_IF NAME="moved" -->Patron not deleted: has moved<!-- /TMPL_IF -->
+ <!-- TMPL_IF NAME="issues" -->Patron not deleted: has items checked out<!-- /TMPL_IF -->
+ <!-- TMPL_IF NAME="fines" -->Patron not deleted: has fines<!-- /TMPL_IF -->
+ <!-- TMPL_IF NAME="reserves" -->Patron not deleted: has holds<!-- /TMPL_IF -->
+ <!-- TMPL_IF NAME="deleted" -->Patron Deleted<!-- /TMPL_IF -->
+ <!-- TMPL_IF NAME="added" -->Patron Added<!-- /TMPL_IF -->
+ <!-- TMPL_IF NAME="changed" -->Patron Changed<!-- /TMPL_IF -->
+ </td>
+ </tr>
+ <!-- /TMPL_LOOP -->
+ </table>
+ <a id="doneupload" href="/cgi-bin/koha/tools/tools-home.pl">Return to Tools</a>
+ </div>
+<!-- TMPL_ELSE -->
+ <div class="yui-b">
+ <div class="yui-g">
+ <div class="yui-u first">
+ <h1>Syncronize External Patrons</h1>
+ <!-- TMPL_UNLESS name="categoryloop" -->
+ <div class="dialog alert">
+ There must be a patron category configured for syncronizing for this to work!
+ </div>
+ <!-- TMPL_ELSE -->
+ <form method="post" action="/cgi-bin/koha/tools/external-member-sync.pl">
+ <fieldset class="rows">
+ <ol>
+ <li>
+ <label for="branch">Branch: </label>
+ <select id="branch" name="branch">
+ <!-- TMPL_LOOP NAME="branchloop" -->
+ <!-- TMPL_IF NAME="selected" -->
+ <option value="<!-- TMPL_VAR NAME="value"-->" selected="selected"><!-- TMPL_VAR NAME="branchname" --></option>
+ <!-- TMPL_ELSE -->
+ <option value="<!-- TMPL_VAR NAME="value"-->"><!-- TMPL_VAR NAME="branchname" --></option>
+ <!-- /TMPL_IF -->
+ <!-- /TMPL_LOOP -->
+ </select>
+ </li>
+ <li>
+ <label for="category">Category: </label>
+ <select id="category" name="category">
+ <!-- TMPL_LOOP NAME="categoryloop" -->
+ <!-- TMPL_IF NAME="categorycodeselected" -->
+ <option value="<!-- TMPL_VAR NAME="categorycode" -->" selected="selected"><!-- TMPL_VAR NAME="categoryname" --></option>
+ <!-- TMPL_ELSE -->
+ <option value="<!-- TMPL_VAR NAME="categorycode" -->"><!-- TMPL_VAR NAME="categoryname" --></option>
+ <!-- /TMPL_IF -->
+ <!-- /TMPL_LOOP -->
+ </select>
+ </li>
+ </ol>
+ </fieldset>
+ <fieldset class="action"><input type="submit" name="op" value="Sync" class="submit" /> <a href="/cgi-bin/koha/tools/tools-home.pl" class="cancel">Cancel</a>
+ </fieldset>
+ </form>
+ <!-- /TMPL_UNLESS -->
+ </div>
+<!-- /TMPL_IF -->
+ </div>
+ </div>
+</div>
+<div class="yui-b noprint">
+ <!-- TMPL_INCLUDE NAME="tools-menu.inc" -->
+</div>
+</div>
+<!-- TMPL_INCLUDE NAME="intranet-bottom.inc" -->
diff --git a/misc/koha-install-log b/misc/koha-install-log
index 1da5f33..2289bff 100644
--- a/misc/koha-install-log
+++ b/misc/koha-install-log
@@ -57,3 +57,10 @@ INSTALL_PAZPAR2=__INSTALL_PAZPAR2__
AUTH_INDEX_MODE=__AUTH_INDEX_MODE__
RUN_DATABASE_TESTS=__RUN_DATABASE_TESTS__
PATH_TO_ZEBRA=__PATH_TO_ZEBRA__
+MEMBERS_EXTERNAL_CONFIG=__MEMBERS_EXTERNAL_CONFIG__
+MEMBERS_EXTERNAL_ENGINE=__MEMBERS_EXTERNAL_ENGINE__
+MEMBERS_EXTERNAL_PASS_METHOD=__MEMBERS_EXTERNAL_PASS_METHOD__
+MEMBERS_EXTERNAL_HOST=__MEMBERS_EXTERNAL_HOST__
+MEMBERS_EXTERNAL_USER=__MEMBERS_EXTERNAL_USER__
+MEMBERS_EXTERNAL_PASS=__MEMBERS_EXTERNAL_PASS__
+MEMBERS_EXTERNAL_SCHEMA=__MEMBERS_EXTERNAL_SCHEMA__
diff --git a/rewrite-config.PL b/rewrite-config.PL
index 9009eb5..d7a4164 100644
--- a/rewrite-config.PL
+++ b/rewrite-config.PL
@@ -139,6 +139,13 @@ $prefix = $ENV{'INSTALL_BASE'} || "/usr";
"__PATH_TO_ZEBRA__" => "",
"__MEMCACHED_SERVERS__" => "",
"__MEMCACHED_NAMESPACE__" => "",
+ '__MEMBERS_EXTERNAL_CONFIG__' => 'no',
+ '__MEMBERS_EXTERNAL_ENGINE__' => '',
+ '__MEMBERS_EXTERNAL_PASS_METHOD__' => '',
+ '__MEMBERS_EXTERNAL_HOST__' => '',
+ '__MEMBERS_EXTERNAL_USER__' => '',
+ '__MEMBERS_EXTERNAL_PASS__' => '',
+ '__MEMBERS_EXTERNAL_SCHEMA__' => '',
);
# Override configuration from the environment
diff --git a/tools/external-member-sync.pl b/tools/external-member-sync.pl
new file mode 100755
index 0000000..2f56fd3
--- /dev/null
+++ b/tools/external-member-sync.pl
@@ -0,0 +1,277 @@
+#!/usr/bin/perl
+
+# This file is part of Koha.
+#
+# Koha is free software; you can redistribute it and/or modify it under the
+# terms of the GNU General Public License as published by the Free Software
+# Foundation; either version 2 of the License, or (at your option) any later
+# version.
+#
+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along with
+# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
+# Suite 330, Boston, MA 02111-1307 USA
+#
+# Written by Michael Hafen mdhafen at tech.washk12.org on Mar. 2008
+
+
+=head1 external-member-sync.pl
+
+This script get patron lists which are pulled from the Koha database and from an external database, and compares the two.
+
+=cut
+
+use strict;
+use warnings;
+use CGI;
+use C4::Auth;
+use C4::Output;
+use C4::Branch; # GetBranchesLoop
+use C4::Reserves; # GetReservesFromBorrowernumber
+use C4::Members; # MoveMemberToDeleted DelMember AddMember ModMember GetMemberIssuesAndFines
+use C4::MembersExternal; # GetMemberDetails_External ListMembers_External GetExternalMappedCategories
+
+my $cgi = new CGI;
+
+# getting the template
+my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
+ {
+ template_name => "tools/external-member-sync.tmpl",
+ query => $cgi,
+ type => "intranet",
+ authnotrequired => 0,
+ flagsrequired => { management => 1, tools => 1 },
+ }
+);
+
+my $dbh = C4::Context->dbh;
+my $op = $cgi->param( 'op' ) || '';
+my $confirmed = $cgi->param( 'confirmed' ) || '';
+my $branch = $cgi->param( 'branch' );
+my $category = $cgi->param('category');
+my @categories = GetExternalMappedCategories();
+
+if ( $op eq 'Sync' and @categories ) {
+#warn "Getting lists...";
+ my ( $dbhash, $dirhash ) = ListMembers_External( $category, $branch );
+ my ( %deleted, %added, %existing );
+ my ( $numdeleted, $numadded, $numchanged ) = ( "0", "0", "0" );
+ my ( $total );
+ my @report;
+
+ my $branch_update = $dbh->prepare( "UPDATE borrowers SET branchcode = ? WHERE cardnumber = ?" );
+
+ # Check for differences borrowers
+ # Check for patrons not in external, and in category
+#warn "checking for deletes...";
+ if ( %$dirhash ) { # to make sure the directory isn't empty.
+ foreach my $cardnumber (sort keys %$dbhash) {
+ next if ( $$dbhash{$cardnumber}{categorycode} ne $category );
+ next if ( $$dirhash{$cardnumber} );
+
+ my $allow_delete = 1;
+
+ my $borrnum = $$dbhash{$cardnumber}{borrowernumber};
+ my ( $issues, undef, $fines ) = GetMemberIssuesAndFines( $borrnum );
+ my ( @reserves ) = GetReservesFromBorrowernumber( $borrnum );
+
+ # this prevents a delete when a patron has changed branches
+ my $bordata = GetMemberDetails_External( $cardnumber );
+ $$bordata{'surname'} ||= '';
+ $$bordata{'firstname'} ||= '';
+ if ( $bordata && $$bordata{'branchcode'} && ( $$bordata{'branchcode'} != $branch ) ) {
+ $allow_delete = 0;
+ $branch_update->execute( $$bordata{'branchcode'}, $cardnumber );
+ #warn "Trying to change branch of $cardnumber to $$bordata{branchcode}";
+ push @report, {
+ name => $$bordata{'surname'} .', '. $$bordata{'firstname'},
+ cardnumber => $$bordata{'cardnumber'},
+ moved => 1,
+ };
+ }
+
+ # this prevents a delete when a patron has copies checked out
+ if ( $issues ) {
+ $allow_delete = 0;
+ push @report, {
+ name => $$bordata{'surname'} .', '. $$bordata{'firstname'},
+ cardnumber => $cardnumber,
+ issues => 1,
+ };
+ }
+
+ # this prevents a delete when a patron has fines
+ $fines += 0; # Force to number
+ if ( $fines != 0 ) {
+ $allow_delete = 0;
+ push @report, {
+ name => $$bordata{'surname'} .', '. $$bordata{'firstname'},
+ cardnumber => $cardnumber,
+ fines => 1,
+ };
+ }
+
+ # this prevents a delete when a patron has reserves outstanding
+ if ( @reserves ) {
+ $allow_delete = 0;
+ push @report, {
+ name => $$bordata{'surname'} .', '. $$bordata{'firstname'},
+ cardnumber => $cardnumber,
+ reserves => 1,
+ }
+ }
+
+ if ( $allow_delete ) {
+ $deleted{ $cardnumber } = 1;
+ }
+ }
+ }
+ foreach (sort keys %$dirhash) {
+ if ( $$dbhash{ $_ } ) {
+ $existing{ $_ } = 1;
+ } else {
+ $added{ $_ } = 1;
+ }
+ $total++;
+ }
+
+ undef $dbhash; # free memory
+ undef $dirhash;
+
+ # DB Queries
+ my $get = $dbh->prepare( "SELECT * FROM borrowers WHERE cardnumber = ?" );
+
+ # Handle deleted borrowers
+#warn "Deleting...";
+ foreach my $cardnumber ( sort keys %deleted ) {
+ $get->execute( $cardnumber );
+ my ( @fields ) = $get->fetchrow;
+ $get->finish;
+
+ if ( $confirmed ) {
+ MoveMemberToDeleted( $fields[0] );
+ DelMember( $fields[0] );
+ }
+
+ $numdeleted++;
+ push @report, {
+ name => $fields[2] .', '. $fields[3],
+ cardnumber => $cardnumber,
+ deleted => 1,
+ };
+ }
+
+ # Handle new borrowers
+#warn "Adding...";
+ foreach my $cardnumber ( sort keys %added ) {
+ my @fields;
+
+ $get->execute( $cardnumber );
+ ( @fields ) = $get->fetchrow;
+ $get->finish;
+
+ if ( @fields ) {
+ # Cardnumber exists already. Assume patron changed branches.
+ # This should be an update
+ $existing{ $cardnumber } = 1;
+ } else {
+ my $attribs = GetMemberDetails_External( $cardnumber, $category );
+ foreach my $attr ( keys %$attribs ) {
+ $$attribs{$attr} =~ s/\s*$// if ( $$attribs{$attr} );
+ }
+ $$attribs{categorycode} = $category;
+ $$attribs{'dateenrolled'} = C4::Dates->today('iso');
+ $$attribs{'dateexpiry'} = GetExpiryDate( $category, $$attribs{'dateenrolled'} );
+
+ if ( $confirmed ) {
+ AddMember( %$attribs );
+ }
+
+ push @report, {
+ name => $$attribs{surname} .', '. $$attribs{firstname},
+ cardnumber => $cardnumber,
+ added => 1,
+ };
+ $numadded++;
+ }
+ }
+
+ # Check all others for updates. This could take a while.
+#warn "Starting update check...";
+ foreach my $cardnumber ( sort keys %existing ) {
+ my ( $diff, @fields );
+ my $attribs = GetMemberDetails_External( $cardnumber );
+ $get->execute( $cardnumber );
+ my $values = $get->fetchrow_hashref;
+
+ foreach ( keys %$attribs ) {
+ exists $$attribs{ $_ } &&
+ $$attribs{ $_ } &&
+ $$attribs{ $_ } =~ s/\s*$//;
+ }
+ unless ( $$attribs{ categorycode } ) {
+ $$attribs{ categorycode } = $category;
+ }
+
+ foreach ( keys %$values ) {
+ if ( exists $$attribs{ $_ } && exists $$values{ $_ } &&
+ defined $$attribs{ $_ } && defined $$values{ $_ } ) {
+ $diff = 1 if ( $$attribs{ $_ } ne $$values{ $_ } );
+ }
+ }
+
+ if ( $diff ) {
+ $$attribs{borrowernumber} = $$values{borrowernumber};
+
+ if ( $confirmed ) {
+ ModMember( %$attribs );
+#warn "Updated $$attribs{cardnumber}";
+ }
+
+ push @report, {
+ name => $$attribs{surname} .', '. $$attribs{firstname},
+ cardnumber => $cardnumber,
+ changed => 1,
+ };
+ $numchanged++;
+ }
+ }
+
+ $template->param(
+ categories => scalar @categories,
+ op => $op,
+ branch => $branch,
+ categorycode => $category,
+ finished => $confirmed,
+ confirm => !$confirmed,
+ num_deleted => $numdeleted,
+ num_added => $numadded,
+ num_changed => $numchanged,
+ total => $total,
+ report => \@report,
+ );
+} else {
+#get Branches
+ my $branches = GetBranchesLoop();
+
+ my @categoryloop;
+ foreach my $cat ( @categories ) {
+ my $category = &GetBorrowercategory( $cat );
+ push @categoryloop, {
+ categorycode => $cat,
+ categoryname => $category->{description},
+ };
+ }
+
+ $template->param(
+ 'branchloop' => $branches,
+ 'categoryloop' => \@categoryloop,
+ 'categories' => scalar @categories,
+ );
+}
+
+#writing the template
+output_html_with_http_headers $cgi, $cookie, $template->output;
--
1.6.3.3
More information about the Koha-patches
mailing list