misc/cronjobs/create_koc_db.pl

   1 #!/usr/bin/perl -w
   2
   3 # 2008 Kyle Hall <kyle.m.hall@gmail.com>
   4
   5 # This file is part of Koha.
   6 #
   7 # Koha is free software; you can redistribute it and/or modify it under the
   8 # terms of the GNU General Public License as published by the Free Software
   9 # Foundation; either version 2 of the License, or (at your option) any later
  10 # version.
  11 #
  12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
  13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
  14 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
  15 #
  16 # You should have received a copy of the GNU General Public License along with
  17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
  18 # Suite 330, Boston, MA  02111-1307 USA
  19 #
  20
  21 =head1 NAME
  22
  23 create_koc_db.pl - create a database file for the offline circulation tool
  24
  25 =head1 SYNOPSIS
  26
  27 create_koc_db.pl
  28
  29  Options:
  30    -help                          brief help message
  31    -man                           full documentation
  32
  33 =head1 OPTIONS
  34
  35 =over 8
  36
  37 =item B<--help>
  38
  39 Print a brief help message and exits.
  40
  41 =item B<--man>
  42
  43 Prints the manual page and exits.
  44
  45 =item B<--file>
  46
  47 the filename that we should use for the database file that we produce. Defaults to "borrowers.db"
  48
  49 =item B<--force>
  50
  51 Forcefully overwrite any existing db file. Defaults to false, so
  52 program will terminate prematurely if the file already exists.
  53
  54 =back
  55
  56 =head1 DESCRIPTION
  57
  58 This script generates a sqlite database file full of patron and
  59 holdings data that can be used by an offline circulation tool.
  60
  61 =head1 USAGE EXAMPLES
  62
  63 This program could be run from cron to occasionally refresh the
  64 offline circulation database. For instance:
  65
  66 C<0 0 * * * create_koc_db.pl>
  67
  68 =head1 SEE ALSO
  69
  70 This program was originally created to interact with Kyle Hall's
  71 offline circulation tool, which is available from
  72 L<http://kylehall.info/index.php/projects/koha-tools/koha-offline-circulation/>,
  73 but any similar tool could use the database that is produced.
  74
  75 =cut
  76
  77 use strict;
  78 use warnings;
  79
  80 $|++;
  81
  82 use DBI;
  83 use Getopt::Long;
  84 use Pod::Usage;
  85 use C4::Context;
  86 use English qw(-no_match_vars);
  87
  88 my $verbose  = 0;
  89 my $help     = 0;
  90 my $man      = 0;
  91 my $filename = 'borrowers.db';
  92 my $force    = 0;
  93
  94 GetOptions(
  95     'verbose' => \$verbose,
  96     'help'    => \$help,
  97     'man'     => \$man,
  98     'file=s'  => \$filename,
  99     'force'   => \$force,
 100 ) or pod2usage(2);
 101 pod2usage(1) if $help;
 102 pod2usage( -verbose => 2 ) if $man;
 103
 104 my %wanted_borrowers_columns = map { $_ => 1 } qw/borrowernumber cardnumber surname  firstname address city phone dateofbirth/;
 105 my %wanted_issues_columns    = map { $_ => 1 } qw/borrowernumber date_due itemcallnumber title itemtype/;
 106
 107 prepare_file_for_writing($filename)
 108   or die "file: '$filename' already exists. Use --force to overwrite\n";
 109
 110 verify_dbd_sqlite();
 111
 112 ## Create DB Connections
 113 my $dbh_mysql = C4::Context->dbh;
 114 my $dbh_sqlite = DBI->connect( "dbi:SQLite2:dbname=$filename", "", "" );
 115 $dbh_sqlite->{AutoCommit} = 0;
 116
 117 create_borrowers_table();
 118 populate_borrowers_table();
 119
 120 create_issues_table();
 121 populate_issues_table();
 122
 123 =head1 INTERNAL METHODS
 124
 125 =head2 verify_dbd_sqlite
 126
 127 Since DBD::SQLite is a new prerequisite and an optional one, let's
 128 make sure we have a new enough version of it.
 129
 130 =cut
 131
 132 sub verify_dbd_sqlite {
 133
 134     eval { require DBD::SQLite2; };
 135     if ( $EVAL_ERROR ) {
 136         my $msg = <<'END_MESSAGE';
 137 DBD::SQLite2 is required to generate offline circultion database files, but not found.
 138 Please install the DBD::SQLite2 perl module. It is availalbe from
 139 http://search.cpan.org/dist/DBD-SQLite2/ or through the CPAN module.
 140 END_MESSAGE
 141         die $msg;
 142     }
 143 }
 144
 145 =head2 prepare_file_for_writing
 146
 147 pass in the filename that we're considering using for the SQLite db.
 148
 149 returns true if we can use it.
 150
 151 returns false if we can't. For example, if it alredy exists and we
 152 don't have --force or don't have permissions to unlink it.
 153
 154 =cut
 155
 156 sub prepare_file_for_writing {
 157     my $filename = shift;
 158     if ( -e $filename ) {
 159
 160         # this file exists. remove it if --force.
 161         if ($force) {
 162             return unlink $filename;
 163         } else {
 164             return;
 165         }
 166     }
 167     return $filename;
 168 }
 169
 170 =head2 create_borrowers_table
 171
 172 Create sqlite borrowers table to mirror the koha borrowers table structure
 173
 174 =cut
 175
 176 sub create_borrowers_table {
 177
 178     my %borrowers_info = get_columns_and_types_of_table( 'borrowers' );
 179     my $sqlite_create_sql = "CREATE TABLE borrowers ( \n";
 180
 181     $sqlite_create_sql .= join(',', map{ $_ . ' ' . $borrowers_info{$_} }
 182                                     grep { exists($wanted_borrowers_columns{$_}) } keys %borrowers_info);
 183
 184     $sqlite_create_sql .= " , \n total_fines decimal(28,6) ";    ## Extra field to store the total fines for a borrower in.
 185     $sqlite_create_sql .= " ) ";
 186
 187     my $return = $dbh_sqlite->do($sqlite_create_sql);
 188     unless ( $return ) {
 189         warn 'unable to create borrowers table: ' . $dbh_sqlite->errstr();
 190     }
 191     return $return;
 192
 193 }
 194
 195 =head2 populate_borrowers_table
 196
 197 Import the data from the koha.borrowers table into our sqlite table
 198
 199 =cut
 200
 201 sub populate_borrowers_table {
 202
 203     my @borrower_fields = grep { exists($wanted_borrowers_columns{$_}) } get_columns_of_table( 'borrowers' );
 204     push @borrower_fields, 'total_fines';
 205
 206     my $sql = "INSERT INTO borrowers ( ";
 207     $sql .= join( ',', @borrower_fields );
 208     $sql .= " ) VALUES ( ";
 209     $sql .= join( ',', map { '?' } @borrower_fields );
 210     $sql .= " ) ";
 211     my $sth_sqlite = $dbh_sqlite->prepare($sql);
 212
 213     my $sth_mysql    = $dbh_mysql->prepare(<<'END_SQL');
 214 SELECT borrowernumber,
 215        cardnumber,
 216        surname,
 217        firstname,
 218        address,
 219        city,
 220        phone,
 221        dateofbirth,
 222        sum( accountlines.amountoutstanding ) as total_fines
 223 FROM borrowers
 224 LEFT JOIN accountlines USING (borrowernumber)
 225 GROUP BY borrowernumber;
 226 END_SQL
 227
 228     my $fields_count = $sth_mysql->execute();
 229     warn "preparing to insert $fields_count borrowers\n" if $verbose;
 230
 231     my $count;
 232     while ( my $borrower = $sth_mysql->fetchrow_hashref ) {
 233         $count++;
 234         if ( $verbose ) {
 235             print '.' unless ( $count % 10 );
 236             print "$count\n" unless ( $count % 1000 );
 237         }
 238         $sth_sqlite->execute( @$borrower{ @borrower_fields } );
 239         $sth_sqlite->finish();
 240         $dbh_sqlite->commit() if ( 0 == $count % 1000 );
 241     }
 242     $dbh_sqlite->commit();
 243     print "inserted $count borrowers\n" if $verbose;
 244     # add_fines_to_borrowers_table();
 245 }
 246
 247 =head2 add_fines_to_borrowers_table
 248
 249 Import the fines from koha.accountlines into the sqlite db
 250
 251 =cut
 252
 253 sub add_fines_to_borrowers_table {
 254
 255     print "preparing to update borrowers\n" if $verbose;
 256     my $sth_mysql = $dbh_mysql->prepare(
 257         "SELECT DISTINCT borrowernumber, SUM( amountoutstanding ) AS total_fines
 258                                     FROM accountlines
 259                                     GROUP BY borrowernumber"
 260     );
 261     $sth_mysql->execute();
 262     my $count;
 263     while ( my $result = $sth_mysql->fetchrow_hashref() ) {
 264         $count++;
 265         if ( $verbose ) {
 266             print '.' unless ( $count % 10 );
 267             print "$count\n" unless ( $count % 1000 );
 268         }
 269
 270         my $borrowernumber = $result->{'borrowernumber'};
 271         my $total_fines    = $result->{'total_fines'};
 272
 273         # warn "Fines for Borrower # $borrowernumber are \$ $total_fines \n" if $verbose;
 274         my $sql = "UPDATE borrowers SET total_fines = ? WHERE borrowernumber = ?";
 275
 276         my $sth_sqlite = $dbh_sqlite->prepare($sql);
 277         $sth_sqlite->execute( $total_fines, $borrowernumber );
 278         $sth_sqlite->finish();
 279     }
 280     print "updated $count borrowers\n" if ( $verbose && $count );
 281 }
 282
 283 =head2 create_issue_table
 284
 285 Create sqlite issues table with minimal information needed from koha tables issues, items, biblio, biblioitems
 286
 287 =cut
 288
 289 sub create_issues_table {
 290
 291     my $fields = get_columns_for_issues_table();
 292
 293     my $sqlite_create_sql = "CREATE TABLE issues ( \n";
 294
 295     my $firstField = 1;
 296     foreach my $key ( keys %$fields ) {
 297         my $field = $key;
 298         my $type  = $fields->{$key};
 299
 300         if ($firstField) {
 301             $sqlite_create_sql .= " $field $type ";
 302             $firstField = 0;
 303         } else {
 304             $sqlite_create_sql .= ", \n $field $type ";
 305         }
 306     }
 307     $sqlite_create_sql .= " ) ";
 308
 309     my $sth_sqlite = $dbh_sqlite->prepare($sqlite_create_sql);
 310     $sth_sqlite->execute();
 311     $sth_sqlite->finish();
 312
 313 }
 314
 315 =head2 populate_issues_table
 316
 317 Import open issues from the koha database
 318
 319 =cut
 320
 321 sub populate_issues_table {
 322
 323     print "preparing to populate ISSUES table\n" if $verbose;
 324     my $sth_mysql = $dbh_mysql->prepare(
 325         "SELECT issues.borrowernumber,
 326                 issues.date_due,
 327                 items.itemcallnumber,
 328                 biblio.title,
 329                 biblioitems.itemtype
 330          FROM   issues, items, biblioitems, biblio
 331          WHERE issues.itemnumber = items.itemnumber
 332          AND items.biblionumber = biblioitems.biblionumber
 333          AND items.biblionumber = biblio.biblionumber
 334          AND returndate IS NULL"
 335     );
 336     $sth_mysql->execute();
 337
 338     my $column_names = $sth_mysql->{'NAME'};
 339     my $sql_sqlite = "INSERT INTO issues ( ";
 340     $sql_sqlite .= join( ',', @$column_names );
 341     $sql_sqlite .= " ) VALUES ( ";
 342     $sql_sqlite .= join( ',', map { '?' } @$column_names );
 343     $sql_sqlite .= " ) ";
 344
 345     warn "$sql_sqlite\n" if $verbose;
 346     my $sth_sqlite = $dbh_sqlite->prepare($sql_sqlite);
 347
 348     my $count;
 349     while ( my $result = $sth_mysql->fetchrow_hashref ) {
 350
 351         $count++;
 352         if ( $verbose ) {
 353             print '.' unless ( $count % 10 );
 354             print "$count\n" unless ( $count % 1000 );
 355         }
 356
 357         $sth_sqlite->execute( @$result{ @$column_names } );
 358         $sth_sqlite->finish();
 359         $dbh_sqlite->commit() if ( 0 == $count % 1000 );
 360     }
 361     $dbh_sqlite->commit();
 362     print "inserted $count issues\n" if ( $verbose && $count );
 363 }
 364
 365 =head2 get_columns_of_table
 366
 367 pass in the name of a database table.
 368
 369 returns list of column names in that table.
 370
 371 =cut
 372
 373 sub get_columns_of_table {
 374     my $table_name = shift;
 375
 376     my %column_info = get_columns_and_types_of_table( $table_name );
 377     my @columns = keys %column_info;
 378     return @columns;
 379
 380 }
 381
 382 =head2 get_columns_and_types_of_table
 383
 384 pass in the name of a database table
 385
 386 returns a hash of column names to their types.
 387
 388 =cut
 389
 390 sub get_columns_and_types_of_table {
 391     my $table_name = shift;
 392
 393     my $column_info = $dbh_mysql->selectall_arrayref( "SHOW COLUMNS FROM $table_name" );
 394     my %columns = map{ $_->[0] => $_->[1] } @$column_info;
 395     return %columns;
 396
 397 }
 398
 399 =head2 get_columns_for_issues_table
 400
 401 This sub returns a hashref where the keys are all the fields in the given tables, and the data is the field's type
 402
 403 =cut
 404
 405 sub get_columns_for_issues_table {
 406
 407   my @tables = ( 'issues', 'items', 'biblioitems', 'biblio' );
 408
 409   my %fields;
 410
 411   foreach my $table ( @tables ) {
 412       my %table_info = get_columns_and_types_of_table( $table );
 413       %fields = ( %fields, %table_info );
 414   }
 415   return { map { $_ => $fields{$_} } grep { exists($wanted_issues_columns{$_}) } keys %fields };
 416 }
 417
 418 1;
 419 __END__
 420