Attachment #169183 for bug #37210

View | Details | Raw Unified | Return to bug 37210
Collapse All | Expand All





    $bornamefilter =~s/\*/\%/g;
    $bornamefilter =~s/\?/\_/g;
    #scape single quote
    $bornamefilter =~s/'/\\'/g;
    $itemtypefilter =~s/'/\\'/g;
    $borcatfilter =~s/'/\\'/g;
    $holdingbranchfilter =~s/'/\\'/g;
    $homebranchfilter =~s/'/\\'/g;
    $branchfilter =~s/'/\\'/g;

    my $strsth="SELECT date_due,
        borrowers.title as borrowertitle,

    WHERE 1=1 "; # placeholder, since it is possible that none of the additional
                 # conditions will be selected by user
    $strsth.=" AND date_due               < '" . $todaysdate     . "' " unless ($showall or $datedueto);
    my @bind_values;
    if ($bornamefilter) {
        $strsth .= " AND (borrowers.firstname like ? or borrowers.surname like ? or borrowers.cardnumber like ?)";
        push @bind_values, "$bornamefilter%", "$bornamefilter%", "$bornamefilter%";
    }
    if ($borcatfilter) {
        $strsth .= " AND borrowers.categorycode = ? ";
        push @bind_values, $borcatfilter;
    }
    if( $itemtypefilter ){
        if( C4::Context->preference('item-level_itypes') ){
            $strsth .= " AND items.itype   = ? ";
        } else {
            $strsth .= " AND biblioitems.itemtype   = ? ";
        }
        push @bind_values, $itemtypefilter;
    }
    if ( $borflagsfilter eq 'gonenoaddress' ) {
        $strsth .= " AND borrowers.gonenoaddress <> 0";

    elsif ( $borflagsfilter eq 'lost') {
        $strsth .= " AND borrowers.lost <> 0";
    }
    if ($branchfilter) {
        $strsth .= " AND borrowers.branchcode   = ? ";
        push @bind_values, $branchfilter;
    }
    if ($homebranchfilter) {
        $strsth .= " AND items.homebranch       = ? ";
        push @bind_values, $homebranchfilter;
    }
    if ($holdingbranchfilter) {
        $strsth .= " AND items.holdingbranch    = ? ";
        push @bind_values, $holdingbranchfilter;
    }
    if ($dateduefrom) {
        $strsth .= " AND date_due >= ?";
        push @bind_values, DateTime::Format::MySQL->format_datetime($dateduefrom);
    }
    if ($datedueto) {
        $strsth .= " AND date_due <= ?";
        push @bind_values, DateTime::Format::MySQL->format_datetime($datedueto);
    }
    # restrict patrons (borrowers) to those matching the patron attribute filter(s), if any
    my $bnlist = $have_pattr_filter_data ? join(',',keys %borrowernumber_to_attributes) : '';
    $strsth =~ s/WHERE 1=1/WHERE 1=1 AND borrowers.borrowernumber IN ($bnlist)/ if $bnlist;

    $strsth.=" ORDER BY date_due, surname, firstname";
    $template->param(sql=>$strsth);
    my $sth=$dbh->prepare($strsth);
    $sth->execute(@bind_values);
        ($dateduefrom ? DateTime::Format::MySQL->format_datetime($dateduefrom) : ()),
        ($datedueto ? DateTime::Format::MySQL->format_datetime($datedueto) : ()),
    );

    my @overduedata;
    while (my $data = $sth->fetchrow_hashref) {
- 

Return to bug 37210

Lines 223-235 if ($noreport) { Link Here

(-)a/circ/overdue.pl (-21 / +33 lines)
223		223
224	$bornamefilter =~s/\*/\%/g;	224	$bornamefilter =~s/\*/\%/g;
225	$bornamefilter =~s/\?/\_/g;	225	$bornamefilter =~s/\?/\_/g;
226	#scape single quote
227	$bornamefilter =~s/'/\\'/g;
228	$itemtypefilter =~s/'/\\'/g;
229	$borcatfilter =~s/'/\\'/g;
230	$holdingbranchfilter =~s/'/\\'/g;
231	$homebranchfilter =~s/'/\\'/g;
232	$branchfilter =~s/'/\\'/g;
233		226
234	my $strsth="SELECT date_due,	227	my $strsth="SELECT date_due,
235	borrowers.title as borrowertitle,	228	borrowers.title as borrowertitle,
Lines 275-288 if ($noreport) { Link Here
275	WHERE 1=1 "; # placeholder, since it is possible that none of the additional	268	WHERE 1=1 "; # placeholder, since it is possible that none of the additional
276	# conditions will be selected by user	269	# conditions will be selected by user
277	$strsth.=" AND date_due < '" . $todaysdate . "' " unless ($showall or $datedueto);	270	$strsth.=" AND date_due < '" . $todaysdate . "' " unless ($showall or $datedueto);
278	$strsth.=" AND (borrowers.firstname like '".$bornamefilter."%' or borrowers.surname like '".$bornamefilter."%' or borrowers.cardnumber like '".$bornamefilter."%')" if($bornamefilter) ;	271	my @bind_values;
279	$strsth.=" AND borrowers.categorycode = '" . $borcatfilter . "' " if $borcatfilter;	272	if ($bornamefilter) {
		273	$strsth .= " AND (borrowers.firstname like ? or borrowers.surname like ? or borrowers.cardnumber like ?)";
		274	push @bind_values, "$bornamefilter%", "$bornamefilter%", "$bornamefilter%";
		275	}
		276	if ($borcatfilter) {
		277	$strsth .= " AND borrowers.categorycode = ? ";
		278	push @bind_values, $borcatfilter;
		279	}
280	if( $itemtypefilter ){	280	if( $itemtypefilter ){
281	if( C4::Context->preference('item-level_itypes') ){	281	if( C4::Context->preference('item-level_itypes') ){
282	$strsth.=" AND items.itype = '" . $itemtypefilter . "' ";	282	$strsth .= " AND items.itype = ? ";
283	} else {	283	} else {
284	$strsth.=" AND biblioitems.itemtype = '" . $itemtypefilter . "' ";	284	$strsth .= " AND biblioitems.itemtype = ? ";
285	}	285	}
		286	push @bind_values, $itemtypefilter;
286	}	287	}
287	if ( $borflagsfilter eq 'gonenoaddress' ) {	288	if ( $borflagsfilter eq 'gonenoaddress' ) {
288	$strsth .= " AND borrowers.gonenoaddress <> 0";	289	$strsth .= " AND borrowers.gonenoaddress <> 0";
Lines 293-303 if ($noreport) { Link Here
293	elsif ( $borflagsfilter eq 'lost') {	294	elsif ( $borflagsfilter eq 'lost') {
294	$strsth .= " AND borrowers.lost <> 0";	295	$strsth .= " AND borrowers.lost <> 0";
295	}	296	}
296	$strsth.=" AND borrowers.branchcode = '" . $branchfilter . "' " if $branchfilter;	297	if ($branchfilter) {
297	$strsth.=" AND items.homebranch = '" . $homebranchfilter . "' " if $homebranchfilter;	298	$strsth .= " AND borrowers.branchcode = ? ";
298	$strsth.=" AND items.holdingbranch = '" . $holdingbranchfilter . "' " if $holdingbranchfilter;	299	push @bind_values, $branchfilter;
299	$strsth.=" AND date_due >= ?" if $dateduefrom;	300	}
300	$strsth.=" AND date_due <= ?" if $datedueto;	301	if ($homebranchfilter) {
		302	$strsth .= " AND items.homebranch = ? ";
		303	push @bind_values, $homebranchfilter;
		304	}
		305	if ($holdingbranchfilter) {
		306	$strsth .= " AND items.holdingbranch = ? ";
		307	push @bind_values, $holdingbranchfilter;
		308	}
		309	if ($dateduefrom) {
		310	$strsth .= " AND date_due >= ?";
		311	push @bind_values, DateTime::Format::MySQL->format_datetime($dateduefrom);
		312	}
		313	if ($datedueto) {
		314	$strsth .= " AND date_due <= ?";
		315	push @bind_values, DateTime::Format::MySQL->format_datetime($datedueto);
		316	}
301	# restrict patrons (borrowers) to those matching the patron attribute filter(s), if any	317	# restrict patrons (borrowers) to those matching the patron attribute filter(s), if any
302	my $bnlist = $have_pattr_filter_data ? join(',',keys %borrowernumber_to_attributes) : '';	318	my $bnlist = $have_pattr_filter_data ? join(',',keys %borrowernumber_to_attributes) : '';
303	$strsth =~ s/WHERE 1=1/WHERE 1=1 AND borrowers.borrowernumber IN ($bnlist)/ if $bnlist;	319	$strsth =~ s/WHERE 1=1/WHERE 1=1 AND borrowers.borrowernumber IN ($bnlist)/ if $bnlist;
Lines 305-314 if ($noreport) { Link Here
305	$strsth.=" ORDER BY date_due, surname, firstname";	321	$strsth.=" ORDER BY date_due, surname, firstname";
306	$template->param(sql=>$strsth);	322	$template->param(sql=>$strsth);
307	my $sth=$dbh->prepare($strsth);	323	my $sth=$dbh->prepare($strsth);
308	$sth->execute(	324	$sth->execute(@bind_values);
309	($dateduefrom ? DateTime::Format::MySQL->format_datetime($dateduefrom) : ()),
310	($datedueto ? DateTime::Format::MySQL->format_datetime($datedueto) : ()),
311	);
312		325
313	my @overduedata;	326	my @overduedata;
314	while (my $data = $sth->fetchrow_hashref) {	327	while (my $data = $sth->fetchrow_hashref) {
315	-