add filtering to index (using parameter filter, for now single)

git-svn-id: file:///home/dpavlin/private/svn/webpac/trunk@643 13eb9ef6-21d5-0310-b721-a9d68796d827

diff --git a/WebPac.pm b/WebPac.pm
index 5cc5f89..4afc2c2 100644 (file)
--- a/WebPac.pm
+++ b/WebPac.pm
@@ -438,6 +438,8 @@ sub show_index {
        my $field = $q->param("f$i");
        my $limit = $q->param("v$i");
 
+       my $filter = $q->param("filter");
+
        my $html;
 
        my $index = new index_DBI(
@@ -447,9 +449,9 @@ sub show_index {
                $cfg_global->val('global', 'dbi_passwd') || ''
        );
 
-       my $total = $index->count($field,$limit);
+       my $total = $index->count($field,$limit,$filter);
 
-       if (! $total) {
+       if (! defined($total)) {
                my $tmpl = $self->load_tmpl(url_ex($q,'no_index.html'));
                $tmpl->param('field',$field);
                $html = $tmpl->output;
@@ -482,7 +484,7 @@ sub show_index {
        make_pager($q, $tmpl, $pager);
        make_pager_vars($q, $tmpl, @persist_vars);
 
-       my @pager_data_list = $index->fetch($field,$limit, $pager->first - 1, $pager->entries_on_this_page);
+       my @pager_data_list = $index->fetch($field,$limit, $pager->first - 1, $pager->entries_on_this_page, $filter);
        $tmpl->param('PAGER_DATA_LIST', \@pager_data_list);
 
        return in_template($q,$tmpl->output);

diff --git a/all2xml.conf b/all2xml.conf
index f47c8b5..f47d69b 100644 (file)
--- a/all2xml.conf
+++ b/all2xml.conf
@@ -246,13 +246,13 @@
        library=Psihologija
        library_url=http://knjiznice.ffzg.hr/info.html#psiho
 
-#[ps-peri]
-#      isis_db=/backup/isis_backup/sunce2/isisdata/latest/PERI/PERI
-#      type=isis
-#      materialtype=Èasopis
-#      library=Psihologija
-#      library_url=http://knjiznice.ffzg.hr/info.html#psiho
-#
+[ps-peri]
+       isis_db=/data/isis_data/ps/PERI/PERI
+       type=isis
+       materialtype=Èasopis
+       library=Psihologija
+       library_url=http://knjiznice.ffzg.hr/info.html#psiho
+
 #[pu-libri]
 #      isis_db=/backup/isis_backup/C124-2A/ISISDATA/latest/LIBRI/LIBRI
 #      type=isis

diff --git a/all2xml.pl b/all2xml.pl
index ee41297..e327b7a 100755 (executable)
--- a/all2xml.pl
+++ b/all2xml.pl
@@ -739,6 +739,7 @@ print STDERR "using: $type...\n";
                my $db = new Biblio::Isis( isisdb => $isis_db );
 
                my $max_rowid = $db->count || die "can't find maxmfn";
+$max_rowid = 200; # XXX
 
                print STDERR "Reading database: $isis_db [$max_rowid rows]\n";
 

diff --git a/index_DBI_filter.pm b/index_DBI_filter.pm
index 5ac3208..fadeea9 100644 (file)
--- a/index_DBI_filter.pm
+++ b/index_DBI_filter.pm
@@ -117,24 +117,52 @@ sub count {
        my $field = shift;
        my $where = shift;
 
-       my $sql = "select count(*) from index where name = ? and upper(item) like upper(?)||'%'";
+       my $filter = shift;
+
+       my $tables_sql = 'index';
+       my $where_sql = '';
+       my @sql_args = ( $field, $where );
+
+       if ($filter) {
+               $tables_sql .= ",filters";
+               $where_sql .= "
+                       and index.ord = filters.ord
+                       and filter = ?
+               ";
+               push @sql_args, $filter;
+       }
+
+       my $sql = qq{
+               select count(*)
+               from $tables_sql
+               where name = ? and upper(item) like upper(?)||'%'
+               $where_sql
+       };
 
-       my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
-       $sth->execute($field,$where) || die "sql: $sql; ".$self->{dbh}->errstr();
+       my $sth = $self->{dbh}->prepare($sql) || confess $self->{dbh}->errstr();
+       $sth->execute(@sql_args) || confess "sql: $sql; ".$self->{dbh}->errstr();
 
        my ($total) = $sth->fetchrow_array();
 
        # no results, count all
        if (! $total) {
-               my $sql = "select count(*) from index wheere name = ?";
-
-               my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
-               $sth->execute($field) || die "sql: $sql; ".$self->{dbh}->errstr();
+               my $sql = qq{
+                       select count(*)
+                       from $tables_sql
+                       where index.name = ?
+                       $where_sql
+               };
+
+               @sql_args = ( $field );
+               push @sql_args, $filter if ($filter);
+
+               my $sth = $self->{dbh}->prepare($sql) || confess $self->{dbh}->errstr();
+               $sth->execute(@sql_args) || confess "sql: $sql; ".$self->{dbh}->errstr();
                $total = $sth->fetchrow_array();
 
        }
 
-       return $total || 1;
+       return $total || '0';
 }
 
 
@@ -146,40 +174,71 @@ sub fetch {
 
        my $offset = shift || 0;
        my $rows = shift || 10;
+       my $filter = shift;
+
        my $from_ord = 0;
 
-       my @sql_args;
+       my $tables_sql = 'index';
+       my $where_sql = '';
 
-       my $sql = qq{
-               select item,display,count
-               from index
-               where name = ?
-               and ord > ?
-               order by ord
-               limit ? offset ?
-       };
+       my @sql_args = ( $field, $where );
 
-       if ($where) {
-               my $sql2 = "select ord from index where name = ? and upper(item) like upper(?)||'%'";
-               my $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr();
+       if ($filter) {
+               $tables_sql .= ",filters";
+               $where_sql .= "
+                       and index.ord = filters.ord
+                       and filter = ?
+               ";
+               push @sql_args, $filter;
+       }
 
-               $sth->execute($field, $where) || die "sql2: $sql2; ".$self->{dbh}->errstr();
+       if ($where) {
+               my $sql2 = qq{
+                       select index.ord as ord
+                       from $tables_sql
+                       where name = ? and upper(item) like upper(?)||'%'
+                       $where_sql
+               };
+               my $sth = $self->{dbh}->prepare($sql2) || confess "sql2: $sql2; ".$self->{dbh}->errstr();
+
+               $sth->execute(@sql_args) || confess "sql2: $sql2; ".$self->{dbh}->errstr();
                if (my $row = $sth->fetchrow_hashref) {
                        $from_ord += $row->{ord} - 1;
                } else {
                        # if no match is found when searching from beginning
                        # of word in index, try substring match anywhere
-                       $sql2 = "select ord from index where name = ? and upper(item) like '% '||upper(?)||'%'";
-                       $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr();
-                       $sth->execute($field, $where) || die "sql2: $sql2; ".$self->{dbh}->errstr();
+                       $sql2 = qq{
+                               select index.ord as ord
+                               from $tables_sql
+                               where name = ? and upper(item) like '% '||upper(?)||'%'
+                               $where_sql
+                       };
+       
+                       $sth = $self->{dbh}->prepare($sql2) || confess "sql2: $sql2; ".$self->{dbh}->errstr();
+                       $sth->execute(@sql_args) || confess "sql2: $sql2; ".$self->{dbh}->errstr();
+
                        if (my $row = $sth->fetchrow_hashref) {
                                $from_ord += $row->{ord} - 1;
                        }
                }
        }
 
-       my $sth = $self->{dbh}->prepare($sql) || die "prepare: $sql; ".$self->{dbh}->errstr();
-       $sth->execute($field,$from_ord,$rows,$offset) || die "execute: $sql; ".$self->{dbh}->errstr();
+       @sql_args = ( $field, $from_ord );
+       push @sql_args, $filter if ($filter);
+       push @sql_args, ( $rows, $offset );
+
+       my $sql = qq{
+               select item,display,index.count as count
+               from $tables_sql
+               where name = ?
+                       and index.ord > ?
+               $where_sql
+                       order by index.ord
+                       limit ? offset ?
+       };
+
+       my $sth = $self->{dbh}->prepare($sql) || confess "prepare: $sql; ".$self->{dbh}->errstr();
+       $sth->execute(@sql_args) || confess "execute: $sql; ".$self->{dbh}->errstr();
        my @arr;
        while (my $row = $sth->fetchrow_hashref) {
                $row->{item} = HTML::Entities::encode($row->{item},' <>&"');
@@ -196,7 +255,7 @@ sub close {
 
        return if (! $self->{dbh});
 
-       $self->{dbh}->begin_work || die $self->{dbh}->errstr();
+       $self->{dbh}->begin_work || confess $self->{dbh}->errstr();
 
        $self->delete_and_create('index', qq{
                create table index (
@@ -253,7 +312,7 @@ sub close {
 
        }
 
-       $self->{dbh}->commit || die $self->{dbh}->errstr();
+       $self->{dbh}->commit || confess $self->{dbh}->errstr();
 
        $self->bench("vacuuming");