|
Lines 93-99
sub SearchSuggestion {
Link Here
|
| 93 |
my $dbh = C4::Context->dbh; |
93 |
my $dbh = C4::Context->dbh; |
| 94 |
my @sql_params; |
94 |
my @sql_params; |
| 95 |
my @query = ( |
95 |
my @query = ( |
| 96 |
q{ SELECT suggestions.*, |
96 |
q/SELECT suggestions.*, |
| 97 |
U1.branchcode AS branchcodesuggestedby, |
97 |
U1.branchcode AS branchcodesuggestedby, |
| 98 |
B1.branchname AS branchnamesuggestedby, |
98 |
B1.branchname AS branchnamesuggestedby, |
| 99 |
U1.surname AS surnamesuggestedby, |
99 |
U1.surname AS surnamesuggestedby, |
|
Lines 115-163
sub SearchSuggestion {
Link Here
|
| 115 |
LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber |
115 |
LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber |
| 116 |
LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode |
116 |
LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode |
| 117 |
LEFT JOIN categories AS C2 ON C2.categorycode = U2.categorycode |
117 |
LEFT JOIN categories AS C2 ON C2.categorycode = U2.categorycode |
| 118 |
WHERE 1=1 |
118 |
WHERE 1=1/ |
| 119 |
} , map { |
|
|
| 120 |
if ( my $s = $suggestion->{$_} ) { |
| 121 |
push @sql_params,'%'.$s.'%'; |
| 122 |
" and suggestions.$_ like ? "; |
| 123 |
} else { () } |
| 124 |
} qw( title author isbn publishercode collectiontitle ) |
| 125 |
); |
119 |
); |
|
|
120 |
|
| 121 |
# filter on biblio informations |
| 122 |
foreach my $field ( |
| 123 |
grep { |
| 124 |
my $fieldname = $_; |
| 125 |
any { $fieldname eq $_ } qw/title author isbn publishercode copyrightdate collectiontitle/ |
| 126 |
} keys %$suggestion |
| 127 |
) |
| 128 |
{ |
| 129 |
if ( $suggestion->{$field} ) { |
| 130 |
push @sql_params, $suggestion->{$field}; |
| 131 |
push @query, " AND suggestions.$field = ?"; |
| 132 |
} |
| 133 |
} |
| 126 |
|
134 |
|
|
|
135 |
# filter on user branch |
| 127 |
my $userenv = C4::Context->userenv; |
136 |
my $userenv = C4::Context->userenv; |
| 128 |
if (C4::Context->preference('IndependantBranches')) { |
137 |
if (C4::Context->preference('IndependantBranches')) { |
| 129 |
if ($userenv) { |
138 |
if ($userenv) { |
| 130 |
if (($userenv->{flags} % 2) != 1 && !$suggestion->{branchcode}){ |
139 |
if (($userenv->{flags} % 2) != 1 && !$suggestion->{branchcode}){ |
| 131 |
push @sql_params,$$userenv{branch}; |
140 |
push @sql_params, $userenv->{branch}; |
| 132 |
push @query,q{ and (suggestions.branchcode = ? or suggestions.branchcode ='')}; |
141 |
push @query, q/ AND (suggestions.branchcode = ? or suggestions.branchcode = '')/; |
| 133 |
} |
|
|
| 134 |
} |
142 |
} |
|
|
143 |
} |
| 135 |
} |
144 |
} |
| 136 |
|
145 |
|
| 137 |
foreach my $field (grep { my $fieldname=$_; |
146 |
# filter on nillable fields |
| 138 |
any {$fieldname eq $_ } qw< |
147 |
foreach my $field ( |
| 139 |
STATUS branchcode itemtype suggestedby managedby acceptedby |
148 |
grep { |
| 140 |
bookfundid biblionumber |
149 |
my $fieldname = $_; |
| 141 |
>} keys %$suggestion |
150 |
any { $fieldname eq $_ } qw/STATUS branchcode itemtype suggestedby managedby acceptedby budgetid biblionumber/ |
| 142 |
) { |
151 |
} keys %$suggestion |
| 143 |
if ($$suggestion{$field}){ |
152 |
) |
| 144 |
push @sql_params,$suggestion->{$field}; |
153 |
{ |
| 145 |
push @query, " and suggestions.$field=?"; |
154 |
if ( $suggestion->{$field} ) { |
| 146 |
} |
155 |
push @sql_params, $suggestion->{$field}; |
|
|
156 |
push @query, " AND suggestions.$field = ?"; |
| 157 |
} |
| 147 |
else { |
158 |
else { |
| 148 |
push @query, " and (suggestions.$field='' OR suggestions.$field IS NULL)"; |
159 |
push @query, " AND (suggestions.$field = '' OR suggestions.$field IS NULL)"; |
| 149 |
} |
160 |
} |
| 150 |
} |
161 |
} |
| 151 |
|
162 |
|
|
|
163 |
# filter on date fields |
| 152 |
my $today = C4::Dates->today('iso'); |
164 |
my $today = C4::Dates->today('iso'); |
| 153 |
|
165 |
foreach my $field ( qw/suggesteddate manageddate accepteddate/ ) { |
| 154 |
foreach ( qw( suggesteddate manageddate accepteddate ) ) { |
166 |
my $from = $field."_from"; |
| 155 |
my $from = $_ . "_from"; |
167 |
my $to = $field."_to"; |
| 156 |
my $to = $_ . "_to"; |
168 |
if ($suggestion->{$from} || $suggestion->{$to}) { |
| 157 |
if ($$suggestion{$from} || $$suggestion{$to}) { |
169 |
push @query, " AND suggestions.$field BETWEEN ? AND ?"; |
| 158 |
push @query, " AND suggestions.suggesteddate BETWEEN '" |
170 |
push @sql_params, format_date_in_iso($suggestion->{$from}) || '0000-00-00'; |
| 159 |
. (format_date_in_iso($$suggestion{$from}) || 0000-00-00) . "' AND '" . (format_date_in_iso($$suggestion{$to}) || $today) . "'"; |
171 |
push @sql_params, format_date_in_iso($suggestion->{$to}) || $today; |
| 160 |
} |
172 |
} |
| 161 |
} |
173 |
} |
| 162 |
|
174 |
|
| 163 |
$debug && warn "@query"; |
175 |
$debug && warn "@query"; |
|
Lines 165-171
sub SearchSuggestion {
Link Here
|
| 165 |
$sth->execute(@sql_params); |
177 |
$sth->execute(@sql_params); |
| 166 |
my @results; |
178 |
my @results; |
| 167 |
while ( my $data=$sth->fetchrow_hashref ){ |
179 |
while ( my $data=$sth->fetchrow_hashref ){ |
| 168 |
$$data{$$data{STATUS}} = 1; |
180 |
# add status as field |
|
|
181 |
$data->{$data->{STATUS}} = 1; |
| 169 |
push(@results,$data); |
182 |
push(@results,$data); |
| 170 |
} |
183 |
} |
| 171 |
return (\@results); |
184 |
return (\@results); |