# 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;
UpdateInTable
GetPrimaryKeys
);
- %EXPORT_TAGS = ( all =>[qw( InsertInTable SearchInTable UpdateInTable GetPrimaryKeys)]
+ %EXPORT_TAGS = ( all =>[qw( InsertInTable DeleteInTable SearchInTable UpdateInTable GetPrimaryKeys)]
);
}
=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||="wide";
+ $searchtype||="exact";
my $dbh = C4::Context->dbh;
$columns_out||=["*"];
my $sql = do { local $"=', ';
};
my $row;
my $sth;
- my ($keys,$values)=_filter_fields($filters,$tablename, $searchtype,$filter_columns);
- my @criteria=grep{defined($_) && $_ !~/^\W$/ }@$keys;
- if ($filters) {
- $sql.= do { local $"=') AND (';
- qq{ WHERE (@criteria) }
- };
- }
+ my ($keys,$values)=_filter_fields($tablename,$filters,$searchtype,$filter_columns);
+ if ($keys){
+ my @criteria=grep{defined($_) && $_ !~/^\W$/ }@$keys;
+ if (@criteria) {
+ $sql.= do { local $"=') AND (';
+ qq{ WHERE (@criteria) }
+ };
+ }
+ }
if ($orderby){
- my @orders=map{ "$_".($$orderby{$_}? " DESC" : "") } keys %$orderby;
- $sql.= do { local $"=', ';
- qq{ ORDER BY @orders}
- };
+ #Order by desc by default
+ 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);
}
$debug && $values && warn $sql," ",join(",",@$values);
- $sth = $dbh->prepare($sql);
- $sth->execute(@$values);
+ $sth = $dbh->prepare_cached($sql);
+ eval{$sth->execute(@$values)};
+ warn $@ if ($@ && $debug);
my $results = $sth->fetchall_arrayref( {} );
return $results;
}
=over 4
- $data_id_in_table = &InsertInTable($tablename,$data_hashref);
+ $data_id_in_table = &InsertInTable($tablename,$data_hashref,$withprimarykeys);
=back
=cut
sub InsertInTable{
- my ($tablename,$data) = @_;
+ my ($tablename,$data,$withprimarykeys) = @_;
my $dbh = C4::Context->dbh;
- my ($keys,$values)=_filter_fields($data,$tablename,0);
- map{$_=~s/\(|\)//g; $_=~s/ AND /, /g}@$keys;
- my $query = do { local $"=',';
- qq{
- INSERT INTO $tablename
- SET @$keys
- };
- };
+ my ($keys,$values)=_filter_hash($tablename,$data,($withprimarykeys?"exact":0));
+ my $query = qq{ INSERT INTO $tablename SET }.join(", ",@$keys);
$debug && warn $query, join(",",@$values);
- my $sth = $dbh->prepare($query);
- $sth->execute( @$values);
+ my $sth = $dbh->prepare_cached($query);
+ eval{$sth->execute(@$values)};
+ warn $@ if ($@ && $debug);
return $dbh->last_insert_id(undef, undef, $tablename, undef);
}
my @field_ids=GetPrimaryKeys($tablename);
my @ids=@$data{@field_ids};
my $dbh = C4::Context->dbh;
- my ($keys,$values)=_filter_fields($data,$tablename,0);
- map{$_=~s/\(|\)//g; $_=~s/ AND /, /g}@$keys;
- my $query = do { local $"=',';
- qq{
- UPDATE $tablename
- SET @$keys
+ my ($keys,$values)=_filter_hash($tablename,$data,0);
+ my $query =
+ qq{ UPDATE $tablename
+ SET }.join(",",@$keys).qq{
WHERE }.join (" AND ",map{ "$_=?" }@field_ids);
- };
$debug && warn $query, join(",",@$values,@ids);
- my $sth = $dbh->prepare($query);
- return $sth->execute( @$values,@ids);
-
+ my $sth = $dbh->prepare_cached($query);
+ my $result;
+ eval{$result=$sth->execute(@$values,@ids)};
+ warn $@ if ($@ && $debug);
+ return $result;
}
=head2 DeleteInTable
sub DeleteInTable{
my ($tablename,$data) = @_;
- my @field_ids=GetPrimaryKeys($tablename);
- my @ids=$$data{@field_ids};
my $dbh = C4::Context->dbh;
- my ($keys,$values)=_filter_fields($data,$tablename,0);
-
- my $query = do { local $"=' AND ';
- qq{
- DELETE FROM $tablename
- WHERE }.map{" $_=? "}@field_ids;
- };
- $debug && warn $query, join(",",@$values,@ids);
-
- my $sth = $dbh->prepare($query);
- return $sth->execute( @$values,@ids);
-
+ my ($keys,$values)=_filter_fields($tablename,$data,1);
+ if ($keys){
+ my $query = do { local $"=') AND (';
+ qq{ DELETE FROM $tablename WHERE (@$keys)};
+ };
+ $debug && warn $query, join(",",@$values);
+ my $sth = $dbh->prepare_cached($query);
+ my $result;
+ eval{$result=$sth->execute(@$values)};
+ warn $@ if ($@ && $debug);
+ return $result;
+ }
}
=head2 GetPrimaryKeys
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
sub _get_columns($) {
my ($tablename)=@_;
my $dbh=C4::Context->dbh;
- my $sth=$dbh->prepare(qq{SHOW COLUMNS FROM $tablename });
+ my $sth=$dbh->prepare_cached(qq{SHOW COLUMNS FROM $tablename });
$sth->execute;
my $columns= $sth->fetchall_hashref(qw(Field));
}
Given
- a tablename
- - indicator on purpose whether it is a research or not
+ - indicator on purpose whether all fields should be returned or only non Primary keys
- array_ref to columns to limit to
Returns an array of all the fieldnames of the table
=cut
sub _filter_fields{
- my ($filter_input,$tablename,$searchtype,$filtercolumns)=@_;
+ my ($tablename,$filter_input,$searchtype,$filtercolumns)=@_;
my @keys;
my @values;
if (ref($filter_input) eq "HASH"){
- return _filter_hash($filter_input,$tablename, $searchtype);
+ my ($keys, $values) = _filter_hash($tablename,$filter_input, $searchtype);
+ if ($keys){
+ my $stringkey="(".join (") AND (",@$keys).")";
+ return [$stringkey],$values;
+ }
+ else {
+ return ();
+ }
} elsif (ref($filter_input) eq "ARRAY"){
foreach my $element_data (@$filter_input){
- my ($localkeys,$localvalues)=_filter_fields($element_data,$tablename,$searchtype,$filtercolumns);
+ my ($localkeys,$localvalues)=_filter_fields($tablename,$element_data,$searchtype,$filtercolumns);
if ($localkeys){
@$localkeys=grep{defined($_) && $_ !~/^\W*$/}@$localkeys;
my $string=do{
}
}
else{
- return _filter_string($filter_input,$tablename, $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);
}
sub _filter_hash{
- my ($filter_input, $tablename,$searchtype)=@_;
+ my ($tablename,$filter_input, $searchtype)=@_;
my (@values, @keys);
my $columns= _get_columns($tablename);
my @columns_filtered= _filter_columns($tablename,$searchtype);
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},$field,$searchtype,$columns);
- if ($tmpkeys){
+ $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;
}
}
- my $string=do{
- local $"=") AND (";
- qq{( @keys )}
- };
if (@keys){
- return ([$string],\@values);
+ return (\@keys,\@values);
}
else {
return ();
}
sub _filter_string{
- my ($filter_input,$tablename, $searchtype,$filtercolumns)=@_;
+ my ($tablename,$filter_input, $searchtype,$filtercolumns)=@_;
+ return () unless($filter_input);
+ my @operands=split / /,$filter_input;
my @columns_filtered= _filter_columns($tablename,$searchtype,$filtercolumns);
my $columns= _get_columns($tablename);
- my @operands=split / /,$filter_input;
my (@values,@keys);
- my @localkeys;
foreach my $operand (@operands){
-
+ my @localkeys;
foreach my $field (@columns_filtered){
- my ($tmpkeys, $localvalues)=_Process_Operands($operand,$field,$searchtype,$columns);
+ my ($tmpkeys, $localvalues)=_Process_Operands($operand,"$tablename.$field",$searchtype,$columns);
if ($tmpkeys){
push @values,@$localvalues;
push @localkeys,@$tmpkeys;
}
}
+ my $sql= join (' OR ', @localkeys);
+ push @keys, $sql;
}
- my $sql= do { local $"=' OR ';
- qq{@localkeys}
- };
-
- push @keys, $sql;
if (@keys){
return (\@keys,\@values);
my ($operand, $field, $searchtype,$columns)=@_;
my @values;
my @tmpkeys;
- my $strkeys;
- my @localvaluesextended=("\% $operand\%","$operand\%") ;
- $strkeys= " $field = ? ";
- if ($searchtype eq "wide"){
- if ($field=~/(?<!zip)code|number/ ){
- $strkeys="( $field='' OR $field IS NULL OR $strkeys ) ";
- } elsif ($$columns{$field}{Type}=~/varchar|text/){
- $strkeys="( $field LIKE ? OR $field LIKE ? OR $strkeys ) ";
- push @values,@localvaluesextended;
- }
- push @tmpkeys, $strkeys;
+ my @localkeys;
+ push @tmpkeys, " $field = ? ";
+ push @values, $operand;
+ #By default, exact search
+ if (!$searchtype ||$searchtype eq "exact"){
+ return \@tmpkeys,\@values;
}
- else{
- $strkeys= " $field = ? ";
- push @tmpkeys, $strkeys;
+ 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");
}
- push @values, $operand;
- return (\@tmpkeys,\@values);
+ if ($columns->{$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);
}
1;
projects / koha.git / blobdiff