adding seealso feature in MARC searches
[koha.git] / C4 / SearchMarc.pm
1 package C4::SearchMarc;
2
3 # Copyright 2000-2002 Katipo Communications
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 use strict;
21 require Exporter;
22 use DBI;
23 use C4::Context;
24 use C4::Biblio;
25
26 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
27
28 # set the version for version checking
29 $VERSION = 0.02;
30
31 =head1 NAME
32
33 C4::Search - Functions for searching the Koha MARC catalog
34
35 =head1 SYNOPSIS
36
37   use C4::Search;
38
39   my ($count, @results) = catalogsearch();
40
41 =head1 DESCRIPTION
42
43 This module provides the searching facilities for the Koha MARC catalog
44
45 C<&catalogsearch> is a front end to all the other searches. Depending
46 on what is passed to it, it calls the appropriate search function.
47
48 =head1 FUNCTIONS
49
50 =over 2
51
52 =cut
53
54 @ISA = qw(Exporter);
55 @EXPORT = qw(&catalogsearch &findseealso);
56
57 # make all your functions, whether exported or not;
58
59 sub findseealso {
60         my ($dbh, $fields) = @_;
61         my $tagslib = MARCgettagslib ($dbh,1);
62         for (my $i=0;$i<=$#{$fields};$i++) {
63                 my ($tag) =substr(@$fields[$i],1,4);
64                 my ($subfield) =substr(@$fields[$i],4,1);
65                 warn "$tag / $subfield =>".$tagslib->{$tag}->{$subfield}->{seealso};
66         }
67 }
68 # marcsearch : search in the MARC biblio table.
69 # everything is choosen by the user : what to search, the conditions...
70
71 sub catalogsearch {
72         my ($dbh, $tags, $and_or, $excluding, $operator, $value, $offset,$length) = @_;
73         warn "=>@$tags / @$and_or, $excluding = $operator / $value";
74         # build the sql request. She will look like :
75         # select m1.bibid
76         #               from marc_subfield_table as m1, marc_subfield_table as m2
77         #               where m1.bibid=m2.bibid and
78         #               (m1.subfieldvalue like "Des%" and m2.subfieldvalue like "27%")
79
80         # "Normal" statements
81         my @normal_tags = ();
82 #       my @normal_subfields = ();
83         my @normal_and_or = ();
84         my @normal_operator = ();
85         my @normal_value = ();
86         # Extracts the NOT statements from the list of statements
87         my @not_tags = ();
88 #       my @not_subfields = ();
89         my @not_and_or = ();
90         my @not_operator = ();
91         my @not_value = ();
92         my $any_not = 0;
93
94         for(my $i = 0 ; $i <= $#{$value} ; $i++)
95         {
96                 if(@$excluding[$i])     # NOT statements
97                 {
98                         $any_not = 1;
99                         if(@$operator[$i] eq "contains")
100                         {
101                                 foreach my $word (split(/ /, @$value[$i]))      # if operator is contains, splits the words in separate requests
102                                 {
103                                         unless (C4::Context->stopwords->{uc($word)}) {  #it's NOT a stopword => use it. Otherwise, ignore
104                                                 push @not_tags, @$tags[$i];
105 #                                               push @not_subfields, @$subfields[$i];
106                                                 push @not_and_or, "or"; # as request is negated, finds "foo" or "bar" if final request is NOT "foo" and "bar"
107                                                 push @not_operator, @$operator[$i];
108                                                 push @not_value, $word;
109                                         }
110                                 }
111                         }
112                         else
113                         {
114                                 push @not_tags, @$tags[$i];
115 #                               push @not_subfields, @$subfields[$i];
116                                 push @not_and_or, "or"; # as request is negated, finds "foo" or "bar" if final request is NOT "foo" and "bar"
117                                 push @not_operator, @$operator[$i];
118                                 push @not_value, @$value[$i];
119                         }
120                 }
121                 else    # NORMAL statements
122                 {
123                         if(@$operator[$i] eq "contains") # if operator is contains, splits the words in separate requests
124                         {
125                                 foreach my $word (split(/ /, @$value[$i]))
126                                 {
127                                         unless (C4::Context->stopwords->{uc($word)}) {  #it's NOT a stopword => use it. Otherwise, ignore
128                                                 push @normal_tags, @$tags[$i];
129 #                                               push @normal_subfields, @$subfields[$i];
130                                                 push @normal_and_or, "and";     # assumes "foo" and "bar" if "foo bar" is entered
131                                                 push @normal_operator, @$operator[$i];
132                                                 push @normal_value, $word;
133                                         }
134                                 }
135                         }
136                         else
137                         {
138                                 push @normal_tags, @$tags[$i];
139 #                               push @normal_subfields, @$subfields[$i];
140                                 push @normal_and_or, @$and_or[$i];
141                                 push @normal_operator, @$operator[$i];
142                                 push @normal_value, @$value[$i];
143                         }
144                 }
145         }
146
147         # Finds the basic results without the NOT requests
148         my ($sql_tables, $sql_where1, $sql_where2) = create_request(\@normal_tags, \@normal_and_or, \@normal_operator, \@normal_value);
149
150         my $sth;
151         if ($sql_where2) {
152                 $sth = $dbh->prepare("select distinct m1.bibid from $sql_tables where $sql_where2 and ($sql_where1)");
153         } else {
154                 $sth = $dbh->prepare("select distinct m1.bibid from $sql_tables where $sql_where1");
155         }
156         warn "===> select distinct m1.bibid from $sql_tables where $sql_where2 and ($sql_where1)";
157         $sth->execute();
158         my @result = ();
159
160         # Processes the NOT if any and there are results
161         my ($not_sql_tables, $not_sql_where1, $not_sql_where2);
162
163         if( ($sth->rows) && $any_not )  # some results to tune up and some NOT statements
164         {
165                 ($not_sql_tables, $not_sql_where1, $not_sql_where2) = create_request(\@not_tags, \@not_and_or, \@not_operator, \@not_value);
166
167                 my @tmpresult;
168
169                 while (my ($bibid) = $sth->fetchrow) {
170                         push @tmpresult,$bibid;
171                 }
172                 my $sth_not;
173                 if ($not_sql_where2) {
174                         $sth_not = $dbh->prepare("select distinct m1.bibid from $not_sql_tables where $not_sql_where2 and ($not_sql_where1)");
175                 } else {
176                         $sth_not = $dbh->prepare("select distinct m1.bibid from $not_sql_tables where $not_sql_where1");
177                 }
178
179                 $sth_not->execute();
180
181                 if($sth_not->rows)
182                 {
183                         my %not_bibids = ();
184                         while(my $bibid = $sth_not->fetchrow()) {
185                                 $not_bibids{$bibid} = 1;        # populates the hashtable with the bibids matching the NOT statement
186                         }
187
188                         foreach my $bibid (@tmpresult)
189                         {
190                                 if(!$not_bibids{$bibid})
191                                 {
192                                         push @result, $bibid;
193                                 }
194                         }
195                 }
196                 $sth_not->finish();
197         }
198         else    # no NOT statements
199         {
200                 while (my ($bibid) = $sth->fetchrow) {
201                         push @result,$bibid;
202                 }
203         }
204
205         # we have bibid list. Now, loads title and author from [offset] to [offset]+[length]
206         my $counter = $offset;
207         $sth = $dbh->prepare("select author,title from biblio,marc_biblio where biblio.biblionumber=marc_biblio.biblionumber and bibid=?");
208         my @finalresult = ();
209         while (($counter <= $#result) && ($counter <= ($offset + $length))) {
210                 $sth->execute($result[$counter]);
211                 my ($author,$title) = $sth->fetchrow;
212                 my %line;
213                 $line{bibid}=$result[$counter];
214                 $line{author}=$author;
215                 $line{title}=$title;
216                 push @finalresult, \%line;
217                 $counter++;
218         }
219
220         my $nbresults = $#result + 1;
221         return (\@finalresult, $nbresults);
222 }
223
224 # Creates the SQL Request
225
226 sub create_request {
227         my ($tags, $and_or, $operator, $value) = @_;
228
229         my $sql_tables; # will contain marc_subfield_table as m1,...
230         my $sql_where1; # will contain the "true" where
231         my $sql_where2 = "("; # will contain m1.bibid=m2.bibid
232         my $nb_active=0; # will contain the number of "active" entries. and entry is active is a value is provided.
233         my $nb_table=1; # will contain the number of table. ++ on each entry EXCEPT when an OR  is provided.
234
235         for(my $i=0; $i<=@$value;$i++) {
236                 if (@$value[$i]) {
237                         $nb_active++;
238                         if ($nb_active==1) {
239                                 if (@$operator[$i] eq "start") {
240                                         $sql_tables .= "marc_subfield_table as m$nb_table,";
241                                         $sql_where1 .= "(m1.subfieldvalue like '@$value[$i]%'";
242                                         if (@$tags[$i]) {
243                                                 $sql_where1 .=" and m1.tag+m1.subfieldcode in (@$tags[$i])";
244                                         }
245                                         $sql_where1.=")";
246                                 } elsif (@$operator[$i] eq "contains") {
247                                         $sql_tables .= "marc_word as m$nb_table,";
248                                         $sql_where1 .= "(m1.word  like '@$value[$i]%'";
249                                         if (@$tags[$i]) {
250                                                  $sql_where1 .=" and m1.tag+m1.subfieldid in (@$tags[$i])";
251                                         }
252                                         $sql_where1.=")";
253                                 } else {
254                                         $sql_tables .= "marc_subfield_table as m$nb_table,";
255                                         $sql_where1 .= "(m1.subfieldvalue @$operator[$i] '@$value[$i]' ";
256                                         if (@$tags[$i]) {
257                                                  $sql_where1 .=" and m1.tag+m1.subfieldcode in (@$tags[$i])";
258                                         }
259                                         $sql_where1.=")";
260                                 }
261                         } else {
262                                 if (@$operator[$i] eq "start") {
263                                         $nb_table++;
264                                         $sql_tables .= "marc_subfield_table as m$nb_table,";
265                                         $sql_where1 .= "@$and_or[$i] (m$nb_table.subfieldvalue like '@$value[$i]%'";
266                                         if (@$tags[$i]) {
267                                                 $sql_where1 .=" and m$nb_table.tag+m$nb_table.subfieldcode in (@$tags[$i])";
268                                         }
269                                         $sql_where1.=")";
270                                         $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
271                                 } elsif (@$operator[$i] eq "contains") {
272                                         if (@$and_or[$i] eq 'and') {
273                                                 $nb_table++;
274                                                 $sql_tables .= "marc_word as m$nb_table,";
275                                                 $sql_where1 .= "@$and_or[$i] (m$nb_table.word like '@$value[$i]%'";
276                                                 if (@$tags[$i]) {
277                                                         $sql_where1 .=" and m$nb_table.tag+m$nb_table.subfieldid in(@$tags[$i])";
278                                                 }
279                                                 $sql_where1.=")";
280                                                 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
281                                         } else {
282                                                 $sql_where1 .= "@$and_or[$i] (m$nb_table.word like '@$value[$i]%'";
283                                                 if (@$tags[$i]) {
284                                                         $sql_where1 .="  and m$nb_table.tag+m$nb_table.subfieldid in (@$tags[$i])";
285                                                 }
286                                                 $sql_where1.=")";
287                                                 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
288                                         }
289                                 } else {
290                                         $nb_table++;
291                                         $sql_tables .= "marc_subfield_table as m$nb_table,";
292                                         $sql_where1 .= "@$and_or[$i] (m$nb_table.subfieldvalue @$operator[$i] '@$value[$i]'";
293                                         if (@$tags[$i]) {
294                                                 $sql_where1 .="  and m$nb_table.tag+m$nb_table.subfieldcode in (@$tags[$i])";
295                                         }
296                                         $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
297                                         $sql_where1.=")";
298                                 }
299                         }
300                 }
301         }
302
303         if($sql_where2 ne "(")  # some datas added to sql_where2, processing
304         {
305                 $sql_where2 = substr($sql_where2, 0, (length($sql_where2)-5)); # deletes the trailing ' and '
306                 $sql_where2 .= ")";
307         }
308         else    # no sql_where2 statement, deleting '('
309         {
310                 $sql_where2 = "";
311         }
312         chop $sql_tables;       # deletes the trailing ','
313         return ($sql_tables, $sql_where1, $sql_where2);
314 }
315
316
317 END { }       # module clean-up code here (global destructor)
318
319 1;
320 __END__
321
322 =back
323
324 =head1 AUTHOR
325
326 Koha Developement team <info@koha.org>
327
328 =cut