Merge remote-tracking branch 'kc/new/enh/bug_4877' into kcmaster
[wip/koha-chris_n.git] / 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