3 ## FIXME: if there is an existing borrowers.db file it needs to be deleted before this script is ran.
10 ## Create DB Connections
11 my $dbh_mysql = C4::Context->dbh;
12 my $dbh_sqlite = DBI->connect("dbi:SQLite:dbname=borrowers.db","","");
14 ## Create sqlite borrowers table to mirror the koha borrowers table structure
15 my $sth_mysql = $dbh_mysql->prepare("DESCRIBE borrowers");
16 $sth_mysql->execute();
18 my $sqlite_create_sql = "CREATE TABLE borrowers ( \n";
20 my $result = $sth_mysql->fetchrow_hashref();
21 my $field = $result->{'Field'};
22 my $type = $result->{'Type'};
23 $sqlite_create_sql .= " $field $type ";
25 while ( my $result = $sth_mysql->fetchrow_hashref() ) {
26 $field = $result->{'Field'};
27 $type = $result->{'Type'};
28 $sqlite_create_sql .= " , \n $field $type ";
32 $sqlite_create_sql .= " , \n total_fines decimal(28,6) "; ## Extra field to store the total fines for a borrower in.
33 $sqlite_create_sql .= " ) ";
35 my $sth_sqlite = $dbh_sqlite->prepare( $sqlite_create_sql );
36 $sth_sqlite->execute();
37 $sth_sqlite->finish();
39 ## Import the data from the koha.borrowers table into our sqlite table
40 $sth_mysql = $dbh_mysql->prepare("SELECT * FROM borrowers ORDER BY borrowernumber DESC");
41 my $fields_count = $sth_mysql->execute();
43 print "Number of Borrowers: $fields_count\n";
45 while ( my $borrower = $sth_mysql->fetchrow_hashref ) {
49 print "Working on Borrower # $borrower->{'borrowernumber'} \n";
51 my $sql = "INSERT INTO borrowers ( ";
54 foreach my $key (keys %$borrower) {
64 $sql .= " ) VALUES ( ";
67 foreach my $key (keys %$borrower) {
68 my $data = $borrower->{$key};
76 push( @values, $data );
83 $sth_sqlite = $dbh_sqlite->prepare( $sql );
84 $sth_sqlite->execute( @keys, @values );
85 $sth_sqlite->finish();
88 ## Import the fines from koha.accountlines into the sqlite db
89 $sth_mysql = $dbh_mysql->prepare( "SELECT DISTINCT borrowernumber, SUM( amountoutstanding ) AS total_fines
91 GROUP BY borrowernumber" );
92 while ( my $result = $sth_mysql->fetchrow_hashref() ) {
93 my $borrowernumber = $result->{'borrowernumber'};
94 my $total_fines = $result->{'total_fines'};
96 print "Fines for Borrower # $borrowernumber are \$ $total_fines \n";
98 my $sql = "UPDATE borrowers SET total_fines = ? WHERE borrowernumber = ?";
100 $sth_sqlite = $dbh_sqlite->prepare( $sql );
101 $sth_sqlite->execute( $total_fines, $borrowernumber );
102 $sth_sqlite->finish();
105 ## Create sqlite issues table with minimal information needed from koha tables issues, items, biblio, biblioitems
106 my $fields = GetIssuesFields();
108 $sqlite_create_sql = "CREATE TABLE issues ( \n";
111 foreach my $key (keys %$fields) {
113 my $type = $fields->{$key};
116 $sqlite_create_sql .= " $field, $type ";
119 $sqlite_create_sql .= ", \n $field, $type ";
122 $sqlite_create_sql .= " ) ";
124 $sth_sqlite = $dbh_sqlite->preapre( $sqlite_create_sql );
125 $sth_sqlite->execute();
126 $sth_sqlite->finish();
128 ## Import open issues from the koha database
129 $sth_mysql = $dbh_mysql->prepare( "SELECT * FROM issues, items, biblioitems, biblio
130 WHERE issues.itemnumber = items.itemnumber
131 AND items.biblionumber = biblioitems.biblionumber
132 AND items.biblionumber = biblio.biblionumber
133 AND returndate IS NULL
134 ORDER By borrowernumber DESC" );
136 while ( my $result = $sth_mysql->fetchrow_hashref ) {
140 print "Adding issue for Borrower # $result->{'borrowernumber'} \n";
142 my $sql = "INSERT INTO issues ( ";
145 foreach my $key (keys %$result) {
155 $sql .= " ) VALUES ( ";
158 foreach my $key (keys %$result) {
159 my $data = $result->{$key};
167 push( @values, $data );
174 $sth_sqlite = $dbh_sqlite->prepare( $sql );
175 $sth_sqlite->execute( @keys, @values );
176 $sth_sqlite->finish();
180 ## This sub returns a hashref where the keys are all the fields in the given tables, and the data is the field's type
181 sub GetIssuesFields {
182 my $dbh = C4::Context->dbh;
184 my @tables = ( 'issues', 'items', 'biblioitems', 'biblio' );
187 foreach my $table ( @tables ) {
188 my $sth = $dbh->prepare( 'DESCRIBE ?' );
189 $sth->execute( $table );
191 while ( my $result = $sth->fetchrow_hashref ) {
192 my $field = $result->{'Field'};
193 my $type = $result->{'Type'};
195 $fields->{$field} = $type;