3 # Copyright 2009 Biblibre SARL
5 # This file is part of Koha.
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
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.
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
21 use List::MoreUtils qw(first_value);
23 use C4::Dates qw(format_date_in_iso);
28 use vars qw($VERSION @ISA @EXPORT_OK %EXPORT_TAGS);
31 # set the version for version checking
41 %EXPORT_TAGS = ( all =>[qw( InsertInTable SearchInTable UpdateInTable GetPrimaryKey)]
50 C4::SQLHelper - Perl Module containing convenience functions for SQL Handling
58 This module contains routines for adding, modifying and Searching Data in MysqlDB
71 $hashref = &SearchInTable($tablename,$data, $orderby);
77 - data_hashref : will be considered as an AND of all the data searched
78 - data_array_ref on hashrefs : Will be considered as an OR of Datahasref elements
80 $orderby is a hashref with fieldnames as key and 0 or 1 as values (ASCENDING or DESCENDING order)
85 my ($tablename,$filters,$orderby) = @_;
86 my $dbh = C4::Context->dbh;
87 my $sql = "SELECT * from $tablename";
90 my ($keys,$values)=_filter_fields($filters,$tablename, "search");
92 $sql.= do { local $"=' AND ';
97 my @orders=map{ "$_".($$orderby{$_}? " DESC" : "") } keys %$orderby;
98 $sql.= do { local $"=', ';
103 $debug && warn $sql," ",join(",",@$values);
104 $sth = $dbh->prepare($sql);
105 $sth->execute(@$values);
106 my $results = $sth->fetchall_arrayref( {} );
114 $data_id_in_table = &InsertInTable($tablename,$data_hashref);
119 and returns the id of the row inserted
123 my ($tablename,$data) = @_;
124 my $dbh = C4::Context->dbh;
125 my ($keys,$values)=_filter_fields($data,$tablename);
127 my $query = do { local $"=',';
134 $debug && warn $query, join(",",@$values);
135 my $sth = $dbh->prepare($query);
136 $sth->execute( @$values);
138 return $dbh->last_insert_id(undef, undef, $tablename, undef);
145 $status = &UpdateInTable($tablename,$data_hashref);
150 and returns the status of the operation
154 my ($tablename,$data) = @_;
155 my $field_id=GetPrimaryKey($tablename);
156 my $id=$$data{$field_id};
157 my $dbh = C4::Context->dbh;
158 my ($keys,$values)=_filter_fields($data,$tablename,0);
160 my $query = do { local $"=',';
167 $debug && warn $query, join(",",@$values,$id);
169 my $sth = $dbh->prepare($query);
170 return $sth->execute( @$values,$id);
178 $primarykeyname = &GetPrimaryKey($tablename)
182 Get the Primary Key field name of the table
185 sub GetPrimaryKey($) {
187 my $hash_columns=_columns($tablename);
188 return first_value { $$hash_columns{$_}{'Key'} =~/PRI/} keys %$hash_columns;
195 _get_columns($tablename)
200 Returns a hashref of all the fieldnames of the table
210 $debug && warn $tablename;
211 my $dbh=C4::Context->dbh;
212 my $sth=$dbh->prepare(qq{SHOW COLUMNS FROM $tablename });
214 return $sth->fetchall_hashref(qw(Field));
217 =head2 _filter_fields
228 - an indicator on operation
230 Returns a ref of key array to use in SQL functions
231 and a ref to value array
236 my ($data_to_filter,$tablename,$research)=@_;
237 warn "$tablename research $research";
240 my $columns= _columns($tablename);
241 #Filter Primary Keys of table
242 my $elements=join "|",grep {$$columns{$_}{'Key'} ne "PRI"} keys %$columns;
243 if (ref($data_to_filter) eq "HASH"){
244 foreach my $field (grep {/\b($elements)\b/} keys %$data_to_filter){
245 ## supposed to be a hash of simple values, hashes of arrays could be implemented
246 $$data_to_filter{$field}=format_date_in_iso($$data_to_filter{$field}) if ($$columns{$field}{Type}=~/date/ && $$data_to_filter{$field} !~C4::Dates->regexp("iso"));
247 my $strkeys= " $field = ? ";
248 if ($field=~/code/ && $research){
249 $strkeys="( $strkeys OR $field='' OR $field IS NULL) ";
251 push @values, $$data_to_filter{$field};
252 push @keys, $strkeys;
254 } elsif (ref($data_to_filter) eq "ARRAY"){
255 foreach my $element (@$data_to_filter){
256 my (@localkeys,@localvalues)=_filter_fields($element);
257 push @keys, join(' AND ',@localkeys);
258 push @values, @localvalues;
262 my @operands=split / /,$data_to_filter;
263 foreach my $operand (@operands){
264 my @localvalues=($operand,"\%$operand\%") ;
265 foreach my $field (keys %$columns){
266 my $strkeys= " ( $field = ? OR $field LIKE ? )";
268 $strkeys="( $strkeys OR $field='' OR $field IS NULL) ";
270 push @values, @localvalues;
271 push @keys, $strkeys;
276 return (\@keys,\@values);