-sub searchsuggestion {
- my ($user,$author,$title,$publishercode,$status,$suggestedbyme)=@_;
- my $dbh = C4::Context->dbh;
- my $query="Select suggestions.*,
- U1.surname as surnamesuggestedby,U1.firstname as firstnamesuggestedby,
- U2.surname as surnamemanagedby,U2.firstname as firstnamemanagedby
- from suggestions,borrowers as U1
- left join borrowers as U2 on managedby=U2.borrowernumber
- where suggestedby=U1.borrowernumber";
- my @sql_params;
- if ($author) {
- push @sql_params,"%".$author."%";
- $query .= " and author like ?";
- }
- if ($title) {
- push @sql_params,"%".$title."%";
- $query .= " and suggestions.title like ?";
- }
- if ($publishercode) {
- push @sql_params,"%".$publishercode."%";
- $query .= " and publishercode like ?";
- }
- if ($status) {
- push @sql_params,$status;
- $query .= " and status=?";
- }
- if ($suggestedbyme) {
- push @sql_params,$user;
- $query .= " and suggestedby=?";
- } else {
- $query .= " and managedby is NULL";
- }
- my $sth=$dbh->prepare($query);
- $sth->execute(@sql_params);
- my @results;
- my $even=1; # the even variable is used to set even / odd lines, for highlighting
- while (my $data=$sth->fetchrow_hashref){
- $data->{$data->{status}} = 1;
- if ($even) {
- $even=0;
- $data->{even}=1;
- } else {
- $even=1;
- }
- push(@results,$data);
- }
- return (\@results);
+
+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);
+}
+
+=head2 GetSuggestion
+
+\%sth = &GetSuggestion($ordernumber)
+
+this function get the detail of the suggestion $ordernumber (input arg)
+
+return :
+ the result of the SQL query as a hash : $sth->fetchrow_hashref.
+
+=cut
+
+sub GetSuggestion {
+ my ($ordernumber) = @_;
+ my $dbh = C4::Context->dbh;
+ my $query = "
+ SELECT *
+ FROM suggestions
+ WHERE suggestionid=?
+ ";
+ my $sth = $dbh->prepare($query);
+ $sth->execute($ordernumber);
+ return($sth->fetchrow_hashref);
+}
+
+=head2 GetSuggestionFromBiblionumber
+
+$ordernumber = &GetSuggestionFromBiblionumber($biblionumber)
+
+Get a suggestion from it's biblionumber.
+
+return :
+the id of the suggestion which is related to the biblionumber given on input args.
+
+=cut
+
+sub GetSuggestionFromBiblionumber {
+ my ($biblionumber) = @_;
+ my $query = q{
+ SELECT suggestionid
+ FROM suggestions
+ WHERE biblionumber=? LIMIT 1
+ };
+ my $dbh=C4::Context->dbh;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($biblionumber);
+ my ($suggestionid) = $sth->fetchrow;
+ return $suggestionid;