[Koha-patches] [PATCH] Added offline circ sqlite database generator.
Andrew Moore
andrew.moore at liblime.com
Mon Aug 18 22:58:53 CEST 2008
From: Kyle Hall <kyle.m.hall at gmail.com>
Signed-off-by: Andrew Moore <andrew.moore at liblime.com>
---
misc/create_koc_db.pl | 202 +++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 202 insertions(+), 0 deletions(-)
create mode 100755 misc/create_koc_db.pl
diff --git a/misc/create_koc_db.pl b/misc/create_koc_db.pl
new file mode 100755
index 0000000..12db544
--- /dev/null
+++ b/misc/create_koc_db.pl
@@ -0,0 +1,202 @@
+#!/usr/bin/perl -w
+
+## FIXME: if there is an existing borrowers.db file it needs to be deleted before this script is ran.
+
+use DBI;
+use C4::Context;
+
+use strict;
+
+## Create DB Connections
+my $dbh_mysql = C4::Context->dbh;
+my $dbh_sqlite = DBI->connect("dbi:SQLite:dbname=borrowers.db","","");
+
+## Create sqlite borrowers table to mirror the koha borrowers table structure
+my $sth_mysql = $dbh_mysql->prepare("DESCRIBE borrowers");
+$sth_mysql->execute();
+
+my $sqlite_create_sql = "CREATE TABLE borrowers ( \n";
+
+my $result = $sth_mysql->fetchrow_hashref();
+my $field = $result->{'Field'};
+my $type = $result->{'Type'};
+$sqlite_create_sql .= " $field $type ";
+
+while ( my $result = $sth_mysql->fetchrow_hashref() ) {
+ $field = $result->{'Field'};
+ $type = $result->{'Type'};
+ $sqlite_create_sql .= " , \n $field $type ";
+}
+$sth_mysql->finish();
+
+$sqlite_create_sql .= " , \n total_fines decimal(28,6) "; ## Extra field to store the total fines for a borrower in.
+$sqlite_create_sql .= " ) ";
+
+my $sth_sqlite = $dbh_sqlite->prepare( $sqlite_create_sql );
+$sth_sqlite->execute();
+$sth_sqlite->finish();
+
+## Import the data from the koha.borrowers table into our sqlite table
+$sth_mysql = $dbh_mysql->prepare("SELECT * FROM borrowers ORDER BY borrowernumber DESC");
+my $fields_count = $sth_mysql->execute();
+
+print "Number of Borrowers: $fields_count\n";
+
+while ( my $borrower = $sth_mysql->fetchrow_hashref ) {
+ my @keys;
+ my @values;
+
+ print "Working on Borrower # $borrower->{'borrowernumber'} \n";
+
+ my $sql = "INSERT INTO borrowers ( ";
+
+ my $firstLine = 1;
+ foreach my $key (keys %$borrower) {
+ if ( $firstLine ) {
+ $sql .= '?';
+ $firstLine = 0;
+ } else {
+ $sql .= ', ?';
+ }
+ push( @keys, $key );
+ }
+
+ $sql .= " ) VALUES ( ";
+
+ $firstLine = 1;
+ foreach my $key (keys %$borrower) {
+ my $data = $borrower->{$key};
+
+ if ( $firstLine ) {
+ $sql .= '?';
+ $firstLine = 0;
+ } else {
+ $sql .= ', ?';
+ }
+ push( @values, $data );
+ }
+
+ $sql .= " ) ";
+
+print "\n$sql\n";
+
+ $sth_sqlite = $dbh_sqlite->prepare( $sql );
+ $sth_sqlite->execute( @keys, @values );
+ $sth_sqlite->finish();
+}
+
+## Import the fines from koha.accountlines into the sqlite db
+$sth_mysql = $dbh_mysql->prepare( "SELECT DISTINCT borrowernumber, SUM( amountoutstanding ) AS total_fines
+ FROM accountlines
+ GROUP BY borrowernumber" );
+while ( my $result = $sth_mysql->fetchrow_hashref() ) {
+ my $borrowernumber = $result->{'borrowernumber'};
+ my $total_fines = $result->{'total_fines'};
+
+ print "Fines for Borrower # $borrowernumber are \$ $total_fines \n";
+
+ my $sql = "UPDATE borrowers SET total_fines = ? WHERE borrowernumber = ?";
+
+ $sth_sqlite = $dbh_sqlite->prepare( $sql );
+ $sth_sqlite->execute( $total_fines, $borrowernumber );
+ $sth_sqlite->finish();
+}
+
+## Create sqlite issues table with minimal information needed from koha tables issues, items, biblio, biblioitems
+my $fields = GetIssuesFields();
+
+$sqlite_create_sql = "CREATE TABLE issues ( \n";
+
+my $firstField = 1;
+foreach my $key (keys %$fields) {
+ my $field = $key;
+ my $type = $fields->{$key};
+
+ if ( $firstField ) {
+ $sqlite_create_sql .= " $field, $type ";
+ $firstField = 0;
+ } else {
+ $sqlite_create_sql .= ", \n $field, $type ";
+ }
+}
+$sqlite_create_sql .= " ) ";
+
+$sth_sqlite = $dbh_sqlite->preapre( $sqlite_create_sql );
+$sth_sqlite->execute();
+$sth_sqlite->finish();
+
+## Import open issues from the koha database
+$sth_mysql = $dbh_mysql->prepare( "SELECT * FROM issues, items, biblioitems, biblio
+ WHERE issues.itemnumber = items.itemnumber
+ AND items.biblionumber = biblioitems.biblionumber
+ AND items.biblionumber = biblio.biblionumber
+ AND returndate IS NULL
+ ORDER By borrowernumber DESC" );
+
+while ( my $result = $sth_mysql->fetchrow_hashref ) {
+ my @keys;
+ my @values;
+
+ print "Adding issue for Borrower # $result->{'borrowernumber'} \n";
+
+ my $sql = "INSERT INTO issues ( ";
+
+ my $firstLine = 1;
+ foreach my $key (keys %$result) {
+ if ( $firstLine ) {
+ $sql .= '?';
+ $firstLine = 0;
+ } else {
+ $sql .= ', ?';
+ }
+ push( @keys, $key );
+ }
+
+ $sql .= " ) VALUES ( ";
+
+ $firstLine = 1;
+ foreach my $key (keys %$result) {
+ my $data = $result->{$key};
+
+ if ( $firstLine ) {
+ $sql .= '?';
+ $firstLine = 0;
+ } else {
+ $sql .= ', ?';
+ }
+ push( @values, $data );
+ }
+
+ $sql .= " ) ";
+
+print "\n$sql\n";
+
+ $sth_sqlite = $dbh_sqlite->prepare( $sql );
+ $sth_sqlite->execute( @keys, @values );
+ $sth_sqlite->finish();
+}
+
+
+## This sub returns a hashref where the keys are all the fields in the given tables, and the data is the field's type
+sub GetIssuesFields {
+ my $dbh = C4::Context->dbh;
+
+ my @tables = ( 'issues', 'items', 'biblioitems', 'biblio' );
+ my $fields;
+
+ foreach my $table ( @tables ) {
+ my $sth = $dbh->prepare( 'DESCRIBE ?' );
+ $sth->execute( $table );
+
+ while ( my $result = $sth->fetchrow_hashref ) {
+ my $field = $result->{'Field'};
+ my $type = $result->{'Type'};
+
+ $fields->{$field} = $type;
+ }
+
+ $sth->finish()
+ }
+
+ return $fields;
+}
\ No newline at end of file
--
1.5.6
More information about the Koha-patches
mailing list