C4::SQLHelper Changing $$ notation to $ ->

[koha.git] / C4 / SQLHelper.pm

diff --git a/C4/SQLHelper.pm b/C4/SQLHelper.pm
index bf241d1..0b0c520 100644 (file)
--- a/C4/SQLHelper.pm
+++ b/C4/SQLHelper.pm
@@ -75,22 +75,29 @@ This module contains routines for adding, modifying and Searching Data in MysqlD
 =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 +116,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);
@@ -120,7 +130,8 @@ sub SearchInTable{
      
     $debug && $values && warn $sql," ",join(",",@$values); 
     $sth = $dbh->prepare_cached($sql); 
-    $sth->execute(@$values); 
+    eval{$sth->execute(@$values)}; 
+       warn $@ if ($@ && $debug);
     my $results = $sth->fetchall_arrayref( {} ); 
     return $results;
 }
@@ -145,7 +156,8 @@ sub InsertInTable{
 
        $debug && warn $query, join(",",@$values);
     my $sth = $dbh->prepare_cached($query);
-    $sth->execute( @$values);
+    eval{$sth->execute(@$values)}; 
+       warn $@ if ($@ && $debug);
 
        return $dbh->last_insert_id(undef, undef, $tablename, undef);
 }
@@ -175,8 +187,10 @@ sub UpdateInTable{
        $debug && warn $query, join(",",@$values,@ids);
 
     my $sth = $dbh->prepare_cached($query);
-    return $sth->execute( @$values,@ids);
-
+       my $result;
+    eval{$result=$sth->execute(@$values,@ids)}; 
+       warn $@ if ($@ && $debug);
+    return $result;
 }
 
 =head2 DeleteInTable
@@ -201,7 +215,10 @@ sub DeleteInTable{
                };
                $debug && warn $query, join(",",@$values);
                my $sth = $dbh->prepare_cached($query);
-       return $sth->execute( @$values);
+               my $result;
+       eval{$result=$sth->execute(@$values)}; 
+               warn $@ if ($@ && $debug);
+       return $result;
        }
 }
 
@@ -219,7 +236,7 @@ sub DeleteInTable{
 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
@@ -327,7 +344,15 @@ sub _filter_fields{
                }
        } 
        else{
-               return _filter_string($tablename,$filter_input,$searchtype,$filtercolumns);
+        $debug && warn "filterstring : $filter_input";
+               my ($keys, $values) = _filter_string($tablename,$filter_input, $searchtype,$filtercolumns);
+               if ($keys){
+               my $stringkey="(".join (") AND (",@$keys).")";
+               return [$stringkey],$values;
+               }
+               else {
+               return ();
+               }
        }
 
        return (\@keys,\@values);
@@ -343,8 +368,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;
@@ -365,8 +390,8 @@ sub _filter_string{
        my @columns_filtered= _filter_columns($tablename,$searchtype,$filtercolumns);
        my $columns= _get_columns($tablename);
        my (@values,@keys);
-       my @localkeys;
        foreach my $operand (@operands){
+               my @localkeys;
                foreach my $field (@columns_filtered){
                        my ($tmpkeys, $localvalues)=_Process_Operands($operand,"$tablename.$field",$searchtype,$columns);
                        if ($tmpkeys){
@@ -374,9 +399,9 @@ sub _filter_string{
                                push @localkeys,@$tmpkeys;
                        }
                }
+               my $sql= join (' OR ', @localkeys);
+               push @keys, $sql;
        }
-       my $sql= join (' OR ', @localkeys);
-       push @keys, $sql;
 
        if (@keys){
                return (\@keys,\@values);
@@ -392,18 +417,25 @@ sub _Process_Operands{
        my @localkeys;
        push @tmpkeys, " $field = ? ";
        push @values, $operand;
+       #By default, exact search
        unless ($searchtype){
                return \@tmpkeys,\@values;
        }
-       if ($searchtype eq "start_with"){
-                       my $col_field=(index($field,".")>0?substr($field, index($field,".")+1):$field);
-                       if ($field=~/(?<!zip)code|(?<!card)number/ ){
-                               push @tmpkeys,(" $field= '' ","$field IS NULL");
-                       } elsif ($$columns{$col_field}{Type}=~/varchar|text/i){
-                               push @tmpkeys,(" $field LIKE ? ","$field LIKE ?");
-                               my @localvaluesextended=("\% $operand\%","$operand\%") ;
-                               push @values,@localvaluesextended;
-                       }
+       my $col_field=(index($field,".")>0?substr($field, index($field,".")+1):$field);
+       if ($field=~/(?<!zip)code|(?<!card)number/ && $searchtype ne "exact"){
+               push @tmpkeys,(" $field= '' ","$field IS NULL");
+       }
+       if ($columns->{$col_field}->{Type}=~/varchar|text/i){
+               my @localvaluesextended;
+               if ($searchtype eq "contain"){
+                       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);