[Koha-patches] [PATCH] bug 2503 [8/9]: moving offline circulation db generator to offline_circ directory.
Andrew Moore
andrew.moore at liblime.com
Mon Aug 18 22:58:54 CEST 2008
This script generates a sqlite database of patron and holdings data for the offline circulation tool.
---
misc/create_koc_db.pl | 202 -----------------------------------------
offline_circ/create_koc_db.pl | 202 +++++++++++++++++++++++++++++++++++++++++
2 files changed, 202 insertions(+), 202 deletions(-)
delete mode 100755 misc/create_koc_db.pl
create mode 100755 offline_circ/create_koc_db.pl
diff --git a/misc/create_koc_db.pl b/misc/create_koc_db.pl
deleted file mode 100755
index 12db544..0000000
--- a/misc/create_koc_db.pl
+++ /dev/null
@@ -1,202 +0,0 @@
-#!/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
diff --git a/offline_circ/create_koc_db.pl b/offline_circ/create_koc_db.pl
new file mode 100755
index 0000000..12db544
--- /dev/null
+++ b/offline_circ/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