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