X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=C4%2FSQLHelper.pm;h=a15beb86b66d6c3686489e3defd91b8961731384;hb=d1cea14fae6525b7478548aa19108871395e198d;hp=08cfdf3956840e47b6a0ce79a783c9845b436aa1;hpb=e3a8f1e20d871fca0b50c57146d2194ffdda00c2;p=koha_fer diff --git a/C4/SQLHelper.pm b/C4/SQLHelper.pm index 08cfdf3956..a15beb86b6 100644 --- a/C4/SQLHelper.pm +++ b/C4/SQLHelper.pm @@ -13,9 +13,9 @@ package C4::SQLHelper; # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR # A PARTICULAR PURPOSE. See the GNU General Public License for more details. # -# You should have received a copy of the GNU General Public License along with -# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, -# Suite 330, Boston, MA 02111-1307 USA +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. use strict; @@ -24,7 +24,6 @@ use List::MoreUtils qw(first_value any); use C4::Context; use C4::Dates qw(format_date_in_iso); use C4::Debug; -use YAML; require Exporter; use vars qw($VERSION @ISA @EXPORT_OK %EXPORT_TAGS); @@ -61,36 +60,36 @@ This module contains routines for adding, modifying and Searching Data in MysqlD =head1 FUNCTIONS -=over 2 - -=back - - =head2 SearchInTable -=over 4 - - $hashref = &SearchInTable($tablename,$data, $orderby, $limit, $columns_out, $filtercolumns, $searchtype); + $hashref = &SearchInTable($tablename,$data, $orderby, $limit, + $columns_out, $filtercolumns, $searchtype); -=back $tablename Name of the table (string) + $data may contain - string + - data_hashref : will be considered as an AND of all the data searched + - data_array_ref on hashrefs : Will be considered as an OR of Datahasref elements $orderby is an arrayref of hashref with fieldnames as key and 0 or 1 as values (ASCENDING or DESCENDING order) -$limit is an array ref on 2 values + +$limit is an array ref on 2 values in order to limit results to MIN..MAX + $columns_out is an array ref on field names is used to limit results on those fields (* by default) + $filtercolums is an array ref on field names : is used to limit expansion of research for strings -$searchtype is string Can be "wide" or "exact" + +$searchtype is string Can be "start_with" or "exact" =cut sub SearchInTable{ my ($tablename,$filters,$orderby, $limit, $columns_out, $filter_columns,$searchtype) = @_; -# $searchtype||="start_with"; + $searchtype||="exact"; my $dbh = C4::Context->dbh; $columns_out||=["*"]; my $sql = do { local $"=', '; @@ -109,10 +108,13 @@ sub SearchInTable{ } if ($orderby){ #Order by desc by default - my @orders=map{ "$_".($$orderby{$_}? " DESC" : "") } keys %$orderby; - $sql.= do { local $"=', '; - qq{ ORDER BY @orders} - }; + my @orders; + foreach my $order (@$orderby){ + push @orders,map{ "$_".($order->{$_}? " DESC " : "") } keys %$order; + } + $sql.= do { local $"=', '; + qq{ ORDER BY @orders} + }; } if ($limit){ $sql.=qq{ LIMIT }.join(",",@$limit); @@ -128,20 +130,16 @@ sub SearchInTable{ =head2 InsertInTable -=over 4 + $data_id_in_table = &InsertInTable($tablename,$data_hashref,$withprimarykeys); - $data_id_in_table = &InsertInTable($tablename,$data_hashref); +Insert Data in table and returns the id of the row inserted -=back - - Insert Data in table - and returns the id of the row inserted =cut sub InsertInTable{ - my ($tablename,$data) = @_; + my ($tablename,$data,$withprimarykeys) = @_; my $dbh = C4::Context->dbh; - my ($keys,$values)=_filter_hash($tablename,$data,0); + my ($keys,$values)=_filter_hash($tablename,$data,($withprimarykeys?"exact":0)); my $query = qq{ INSERT INTO $tablename SET }.join(", ",@$keys); $debug && warn $query, join(",",@$values); @@ -154,14 +152,10 @@ sub InsertInTable{ =head2 UpdateInTable -=over 4 - $status = &UpdateInTable($tablename,$data_hashref); -=back +Update Data in table and returns the status of the operation - Update Data in table - and returns the status of the operation =cut sub UpdateInTable{ @@ -170,6 +164,7 @@ sub UpdateInTable{ my @ids=@$data{@field_ids}; my $dbh = C4::Context->dbh; my ($keys,$values)=_filter_hash($tablename,$data,0); + return unless ($keys); my $query = qq{ UPDATE $tablename SET }.join(",",@$keys).qq{ @@ -185,14 +180,10 @@ sub UpdateInTable{ =head2 DeleteInTable -=over 4 - $status = &DeleteInTable($tablename,$data_hashref); -=back +Delete Data in table and returns the status of the operation - Delete Data in table - and returns the status of the operation =cut sub DeleteInTable{ @@ -214,28 +205,21 @@ sub DeleteInTable{ =head2 GetPrimaryKeys -=over 4 - @primarykeys = &GetPrimaryKeys($tablename) -=back +Get the Primary Key field names of the table - Get the Primary Key field names of the table =cut sub GetPrimaryKeys($) { my $tablename=shift; my $hash_columns=_get_columns($tablename); - return grep { $$hash_columns{$_}{'Key'} =~/PRI/i} keys %$hash_columns; + return grep { $hash_columns->{$_}->{'Key'} =~/PRI/i} keys %$hash_columns; } =head2 _get_columns -=over 4 - -_get_columns($tablename) - -=back + _get_columns($tablename) Given a tablename Returns a hashref of all the fieldnames of the table @@ -256,11 +240,7 @@ sub _get_columns($) { =head2 _filter_columns -=over 4 - -_filter_columns($tablename,$research, $filtercolumns) - -=back + _filter_columns($tablename,$research, $filtercolumns) Given - a tablename @@ -289,11 +269,7 @@ sub _filter_columns ($$;$) { } =head2 _filter_fields -=over 4 - -_filter_fields - -=back + _filter_fields Given - a tablename @@ -334,6 +310,7 @@ sub _filter_fields{ } } else{ + $debug && warn "filterstring : $filter_input"; my ($keys, $values) = _filter_string($tablename,$filter_input, $searchtype,$filtercolumns); if ($keys){ my $stringkey="(".join (") AND (",@$keys).")"; @@ -357,8 +334,8 @@ sub _filter_hash{ my $elements=join "|",@columns_filtered; foreach my $field (grep {/\b($elements)\b/} keys %$filter_input){ ## supposed to be a hash of simple values, hashes of arrays could be implemented - $$filter_input{$field}=format_date_in_iso($$filter_input{$field}) if ($$columns{$field}{Type}=~/date/ && $$filter_input{$field} !~C4::Dates->regexp("iso")); - my ($tmpkeys, $localvalues)=_Process_Operands($$filter_input{$field},"$tablename.$field",$searchtype,$columns); + $filter_input->{$field}=format_date_in_iso($filter_input->{$field}) if ($columns->{$field}{Type}=~/date/ && $filter_input->{$field} !~C4::Dates->regexp("iso")); + my ($tmpkeys, $localvalues)=_Process_Operands($filter_input->{$field},"$tablename.$field",$searchtype,$columns); if (@$tmpkeys){ push @values, @$localvalues; push @keys, @$tmpkeys; @@ -406,18 +383,29 @@ sub _Process_Operands{ my @localkeys; push @tmpkeys, " $field = ? "; push @values, $operand; - unless ($searchtype){ + #By default, exact search + if (!$searchtype ||$searchtype eq "exact"){ return \@tmpkeys,\@values; } - if ($searchtype eq "start_with"){ - my $col_field=(index($field,".")>0?substr($field, index($field,".")+1):$field); - if ($field=~/(?0?substr($field, index($field,".")+1):$field); + if ($field=~/(?{$col_field}->{Type}=~/varchar|text/i){ + my @localvaluesextended; + if ($searchtype eq "contain"){ + push @tmpkeys,(" $field LIKE ? "); + push @localvaluesextended,("\%$operand\%") ; + } + if ($searchtype eq "field_start_with"){ + push @tmpkeys,("$field LIKE ?"); + push @localvaluesextended, ("$operand\%") ; + } + if ($searchtype eq "start_with"){ + push @tmpkeys,("$field LIKE ?","$field LIKE ?"); + push @localvaluesextended, ("$operand\%", " $operand\%") ; + } + push @values,@localvaluesextended; } push @localkeys,qq{ (}.join(" OR ",@tmpkeys).qq{) }; return (\@localkeys,\@values);