misc/cronjobs/create_koc_db.pl

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