-=head2 SearchSuggestion
-
-(\@array) = &SearchSuggestion($suggestionhashref_to_search)
-
-searches for a suggestion
-
-return :
-C<\@array> : the aqorders found. Array of hash.
-Note the status is stored twice :
-* in the status field
-* as parameter ( for example ASKED => 1, or REJECTED => 1) . This is for template & translation purposes.
-
-=cut
-
-sub SearchSuggestion {
- my ($suggestion)=@_;
- my $dbh = C4::Context->dbh;
- my @sql_params;
- my @query = (
- q{ SELECT suggestions.*,
- U1.branchcode AS branchcodesuggestedby,
- B1.branchname AS branchnamesuggestedby,
- U1.surname AS surnamesuggestedby,
- U1.firstname AS firstnamesuggestedby,
- U1.email AS emailsuggestedby,
- U1.borrowernumber AS borrnumsuggestedby,
- U1.categorycode AS categorycodesuggestedby,
- C1.description AS categorydescriptionsuggestedby,
- U2.surname AS surnamemanagedby,
- U2.firstname AS firstnamemanagedby,
- B2.branchname AS branchnamesuggestedby,
- U2.email AS emailmanagedby,
- U2.branchcode AS branchcodemanagedby,
- U2.borrowernumber AS borrnummanagedby
- FROM suggestions
- LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
- LEFT JOIN branches AS B1 ON B1.branchcode=U1.branchcode
- LEFT JOIN categories AS C1 ON C1.categorycode = U1.categorycode
- LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
- LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode
- LEFT JOIN categories AS C2 ON C2.categorycode = U2.categorycode
- WHERE 1=1
- } , map {
- if ( my $s = $suggestion->{$_} ) {
- push @sql_params,'%'.$s.'%';
- " and suggestions.$_ like ? ";
- } else { () }
- } qw( title author isbn publishercode collectiontitle )
- );
-
- my $userenv = C4::Context->userenv;
- if (C4::Context->preference('IndependantBranches')) {
- if ($userenv) {
- if (($userenv->{flags} % 2) != 1 && !$suggestion->{branchcode}){
- push @sql_params,$$userenv{branch};
- push @query,q{ and (suggestions.branchcode = ? or suggestions.branchcode ='')};
- }
- }
- }
-
- foreach my $field (grep { my $fieldname=$_;
- any {$fieldname eq $_ } qw<
- STATUS branchcode itemtype suggestedby managedby acceptedby
- bookfundid biblionumber
- >} keys %$suggestion
- ) {
- if ($$suggestion{$field}){
- push @sql_params,$suggestion->{$field};
- push @query, " and suggestions.$field=?";
- }
- else {
- push @query, " and (suggestions.$field='' OR suggestions.$field IS NULL)";
- }
- }
-
- my $today = C4::Dates->today('iso');
-
- foreach ( qw( suggesteddate manageddate accepteddate ) ) {
- my $from = $_ . "_from";
- my $to = $_ . "_to";
- if ($$suggestion{$from} || $$suggestion{$to}) {
- push @query, " AND suggestions.suggesteddate BETWEEN '"
- . (format_date_in_iso($$suggestion{$from}) || 0000-00-00) . "' AND '" . (format_date_in_iso($$suggestion{$to}) || $today) . "'";
- }
- }
-
- $debug && warn "@query";
- my $sth=$dbh->prepare("@query");
- $sth->execute(@sql_params);
- my @results;
- while ( my $data=$sth->fetchrow_hashref ){
- $$data{$$data{STATUS}} = 1;
- push(@results,$data);
- }
- return (\@results);
-}
-