Attachment #123396 for bug #25260

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





# building query parameters
my %where = (
    'me.status'       => 'placed',
    'me.priority'     => 1,
    'me.suspended'    => 0,
    'item.itemlost'   => 0,
    'item.withdrawn'  => 0,
    'item.notforloan' => 0,
    'item.itemnumber' => { -not_in => \'SELECT itemnumber FROM branchtransfers WHERE datearrived IS NULL' }
);

# date boundaries

my $startdate_iso = $dtf->format_date($startdate);
my $enddate_iso   = $dtf->format_date($enddate);
if ( $startdate_iso && $enddate_iso ){
    $where{'me.created_date'} = [ -and => { '>=', $startdate_iso }, { '<=', $enddate_iso } ];
} elsif ( $startdate_iso ){
    $where{'me.created_date'} = { '>=', $startdate_iso };
} elsif ( $enddate_iso ){
    $where{'me.created_date'} = { '<=', $enddate_iso };
}

# Bug 21320
if ( !C4::Context->preference('AllowHoldsOnDamagedItems') ){
    $where{'item.damaged'} = 0;
}

if ( C4::Context->preference('IndependentBranches') ){
    $where{'item.holdingbranch'} = C4::Context->userenv->{'branch'};
}

# get all distinct unfulfilled reserves
my $holds = Koha::Holds->search(
    { %where },
    { join => 'item', distinct  => 1, columns => qw[me.biblio_id] }
);
my @biblionumbers = $holds->get_column('biblio_id');

my $all_items;
foreach my $item ( $holds->get_items_that_can_fill ) {


# patrons count per biblio
my $patrons_count = {
    map { $_->{biblio_id} => $_->{patrons_count} } @{ Koha::Holds->search(
            {},
            {
                select   => [ 'biblio_id', { count => { distinct => 'patron_id' } } ],
                as       => [qw( biblio_id patrons_count )],
                group_by => [qw( biblio_id )]
            },
        )->unblessed
    }
};

my $holds_biblios_map = {
    map { $_->{biblio_id} => $_->{hold_id} } @{ Koha::Holds->search(
            {%where},
            {
                join    => ['item', 'biblio'],
                select  => ['me.biblio_id', 'me.hold_id'],
                select  => ['reserve.biblionumber', 'reserve.reserve_id'],
            }
        )->unblessed
    }
};

my $all_holds = {
    map { $_->biblio_id => $_ } @{ Koha::Holds->search(
            { hold_id => [ values %$holds_biblios_map ]},
            {
                prefetch => [ 'patron', 'item', 'biblio' ]
                alias    => 'reserve',
            }
        )->as_list
    }




my $sqldatewhere = "";
my @query_params = ();

$sqldatewhere .= " AND holds.created_date >= ?";
push @query_params, output_pref({ dt => $startdate, dateformat => 'iso' }) ;
$sqldatewhere .= " AND holds.created_date <= ?";
push @query_params, output_pref({ dt => $enddate, dateformat => 'iso' });

my $include_aqorders_qty =


my $include_aqorders_qty_join =
  $effective_create_items eq 'receiving'
  ? 'LEFT JOIN aqorders ON holds.biblio_id=aqorders.biblionumber'
  : q{};

my $nfl_comparison = $include_ordered ? '<=' : '=';
my $sus_comparison = $include_suspended ? '<=' : '<';
my $strsth =
"SELECT created_date,
        holds.patron_id,
        holds.biblio_id,
        holds.pickup_library_id as branch,
        items.holdingbranch,
        items.itemcallnumber,
        items.itemnumber,

        GROUP_CONCAT(DISTINCT items.itype 
            ORDER BY items.itemnumber SEPARATOR '|') as l_itype,

        holds.status,
        biblio.title,
        biblio.subtitle,
        biblio.medium,
        biblio.part_number,
        biblio.part_name,
        biblio.author,
        count(DISTINCT holds.patron_id) as reservecount, 
        count(DISTINCT items.itemnumber) $include_aqorders_qty as itemcount
 FROM holds
 LEFT JOIN items ON items.biblionumber=holds.biblio_id 
 LEFT JOIN biblio ON biblio.biblionumber=holds.biblio_id
 $include_aqorders_qty_join
 WHERE
 notforloan $nfl_comparison 0 AND damaged = 0 AND itemlost = 0 AND withdrawn = 0
 AND holds.completed=0
 AND suspended $sus_comparison 1
 $sqldatewhere
";


    push @query_params, C4::Context->userenv->{'branch'};
}

$strsth .= " GROUP BY holds.biblio_id ORDER BY reservecount DESC";

$template->param(sql => $strsth);
my $sth = $dbh->prepare($strsth);

    push(
        @reservedata,
        {
            reservedate        => $data->{created_date},
            priority           => $data->{priority},
            name               => $data->{borrower},
            title              => $data->{title},




            [% END %]
            </ul>
        </td>
        <td data-order="[% hold.created_date | html %]">
            [% hold.created_date | $KohaDates %] in [% Branches.GetName ( hold.pickup_library_id ) | html %]
        </td>
        <td>[% hold.notes | html %]</td>
        <td>
            [% Branches.GetName ( hold.pickup_library_id ) | html %]
        </td>
        <td>
            <form name="cancelReserve" action="/cgi-bin/koha/circ/pendingreserves.pl" method="post">
                <input type="hidden" name="op" value="cancel_reserve" />
                <input type="hidden" name="reserve_id" value="[% hold.hold_id | html %]" />

                [% SET hold_cancellation = AuthorisedValues.GetAuthValueDropbox('HOLD_CANCELLATION') %]
                [% IF hold_cancellation %]

            </form>

        [% IF Koha.Preference('CanMarkHoldsToPullAsLost') != 'do_not_allow' %]
            [% IF hold.item_id %]
                <form name="cancelReserve" action="/cgi-bin/koha/circ/pendingreserves.pl" method="post">
                    <input type="hidden" name="reserve_id" value="[% hold.hold_id | html %]" />
                    [% IF Koha.Preference('CanMarkHoldsToPullAsLost') == 'allow' %]
                        <input type="hidden" name="op" value="mark_as_lost" />
                        <input type="submit" value="Mark item as lost" />
- 

Return to bug 25260

Lines 155-167 unless ( $enddate ) { Link Here

(-)a/circ/pendingreserves.pl (-26 / +24 lines)
155		155
156	# building query parameters	156	# building query parameters
157	my %where = (	157	my %where = (
158	'reserve.found' => undef,	158	'me.status' => 'placed',
159	'reserve.priority' => 1,	159	'me.priority' => 1,
160	'reserve.suspend' => 0,	160	'me.suspended' => 0,
161	'itembib.itemlost' => 0,	161	'item.itemlost' => 0,
162	'itembib.withdrawn' => 0,	162	'item.withdrawn' => 0,
163	'itembib.notforloan' => 0,	163	'item.notforloan' => 0,
164	'itembib.itemnumber' => { -not_in => \'SELECT itemnumber FROM branchtransfers WHERE datearrived IS NULL' }	164	'item.itemnumber' => { -not_in => \'SELECT itemnumber FROM branchtransfers WHERE datearrived IS NULL' }
165	);	165	);
166		166
167	# date boundaries	167	# date boundaries
Lines 169-196 my $dtf = Koha::Database->new->schema->storage->datetime_parser; Link Here
169	my $startdate_iso = $dtf->format_date($startdate);	169	my $startdate_iso = $dtf->format_date($startdate);
170	my $enddate_iso = $dtf->format_date($enddate);	170	my $enddate_iso = $dtf->format_date($enddate);
171	if ( $startdate_iso && $enddate_iso ){	171	if ( $startdate_iso && $enddate_iso ){
172	$where{'reserve.reservedate'} = [ -and => { '>=', $startdate_iso }, { '<=', $enddate_iso } ];	172	$where{'me.created_date'} = [ -and => { '>=', $startdate_iso }, { '<=', $enddate_iso } ];
173	} elsif ( $startdate_iso ){	173	} elsif ( $startdate_iso ){
174	$where{'reserve.reservedate'} = { '>=', $startdate_iso };	174	$where{'me.created_date'} = { '>=', $startdate_iso };
175	} elsif ( $enddate_iso ){	175	} elsif ( $enddate_iso ){
176	$where{'reserve.reservedate'} = { '<=', $enddate_iso };	176	$where{'me.created_date'} = { '<=', $enddate_iso };
177	}	177	}
178		178
179	# Bug 21320	179	# Bug 21320
180	if ( !C4::Context->preference('AllowHoldsOnDamagedItems') ){	180	if ( !C4::Context->preference('AllowHoldsOnDamagedItems') ){
181	$where{'itembib.damaged'} = 0;	181	$where{'item.damaged'} = 0;
182	}	182	}
183		183
184	if ( C4::Context->preference('IndependentBranches') ){	184	if ( C4::Context->preference('IndependentBranches') ){
185	$where{'itembib.holdingbranch'} = C4::Context->userenv->{'branch'};	185	$where{'item.holdingbranch'} = C4::Context->userenv->{'branch'};
186	}	186	}
187		187
188	# get all distinct unfulfilled reserves	188	# get all distinct unfulfilled reserves
189	my $holds = Koha::Holds->search(	189	my $holds = Koha::Holds->search(
190	{ %where },	190	{ %where },
191	{ join => 'itembib', alias => 'reserve', distinct => 1, columns => qw[me.biblionumber] }	191	{ join => 'item', distinct => 1, columns => qw[me.biblio_id] }
192	);	192	);
193	my @biblionumbers = $holds->get_column('biblionumber');	193	my @biblionumbers = $holds->get_column('biblio_id');
194		194
195	my $all_items;	195	my $all_items;
196	foreach my $item ( $holds->get_items_that_can_fill ) {	196	foreach my $item ( $holds->get_items_that_can_fill ) {
Lines 199-233 foreach my $item ( $holds->get_items_that_can_fill ) { Link Here
199		199
200	# patrons count per biblio	200	# patrons count per biblio
201	my $patrons_count = {	201	my $patrons_count = {
202	map { $_->{biblionumber} => $_->{patrons_count} } @{ Koha::Holds->search(	202	map { $_->{biblio_id} => $_->{patrons_count} } @{ Koha::Holds->search(
203	{},	203	{},
204	{	204	{
205	select => [ 'biblionumber', { count => { distinct => 'borrowernumber' } } ],	205	select => [ 'biblio_id', { count => { distinct => 'patron_id' } } ],
206	as => [qw( biblionumber patrons_count )],	206	as => [qw( biblio_id patrons_count )],
207	group_by => [qw( biblionumber )]	207	group_by => [qw( biblio_id )]
208	},	208	},
209	)->unblessed	209	)->unblessed
210	}	210	}
211	};	211	};
212		212
213	my $holds_biblios_map = {	213	my $holds_biblios_map = {
214	map { $_->{biblionumber} => $_->{reserve_id} } @{ Koha::Holds->search(	214	map { $_->{biblio_id} => $_->{hold_id} } @{ Koha::Holds->search(
215	{%where},	215	{%where},
216	{	216	{
217	join => ['itembib', 'biblio'],	217	join => ['item', 'biblio'],
218	alias => 'reserve',	218	select => ['me.biblio_id', 'me.hold_id'],
219	select => ['reserve.biblionumber', 'reserve.reserve_id'],
220	}	219	}
221	)->unblessed	220	)->unblessed
222	}	221	}
223	};	222	};
224		223
225	my $all_holds = {	224	my $all_holds = {
226	map { $_->biblionumber => $_ } @{ Koha::Holds->search(	225	map { $_->biblio_id => $_ } @{ Koha::Holds->search(
227	{ reserve_id => [ values %$holds_biblios_map ]},	226	{ hold_id => [ values %$holds_biblios_map ]},
228	{	227	{
229	prefetch => [ 'borrowernumber', 'itembib', 'biblio' ],	228	prefetch => [ 'patron', 'item', 'biblio' ]
230	alias => 'reserve',
231	}	229	}
232	)->as_list	230	)->as_list
233	}	231	}

Lines 84-92 my $dbh = C4::Context->dbh; Link Here

(-)a/circ/reserveratios.pl (-15 / +16 lines)
84	my $sqldatewhere = "";	84	my $sqldatewhere = "";
85	my @query_params = ();	85	my @query_params = ();
86		86
87	$sqldatewhere .= " AND reservedate >= ?";	87	$sqldatewhere .= " AND holds.created_date >= ?";
88	push @query_params, output_pref({ dt => $startdate, dateformat => 'iso' }) ;	88	push @query_params, output_pref({ dt => $startdate, dateformat => 'iso' }) ;
89	$sqldatewhere .= " AND reservedate <= ?";	89	$sqldatewhere .= " AND holds.created_date <= ?";
90	push @query_params, output_pref({ dt => $enddate, dateformat => 'iso' });	90	push @query_params, output_pref({ dt => $enddate, dateformat => 'iso' });
91		91
92	my $include_aqorders_qty =	92	my $include_aqorders_qty =
Lines 96-111 my $include_aqorders_qty = Link Here
96		96
97	my $include_aqorders_qty_join =	97	my $include_aqorders_qty_join =
98	$effective_create_items eq 'receiving'	98	$effective_create_items eq 'receiving'
99	? 'LEFT JOIN aqorders ON reserves.biblionumber=aqorders.biblionumber'	99	? 'LEFT JOIN aqorders ON holds.biblio_id=aqorders.biblionumber'
100	: q{};	100	: q{};
101		101
102	my $nfl_comparison = $include_ordered ? '<=' : '=';	102	my $nfl_comparison = $include_ordered ? '<=' : '=';
103	my $sus_comparison = $include_suspended ? '<=' : '<';	103	my $sus_comparison = $include_suspended ? '<=' : '<';
104	my $strsth =	104	my $strsth =
105	"SELECT reservedate,	105	"SELECT created_date,
106	reserves.borrowernumber as borrowernumber,	106	holds.patron_id,
107	reserves.biblionumber,	107	holds.biblio_id,
108	reserves.branchcode as branch,	108	holds.pickup_library_id as branch,
109	items.holdingbranch,	109	items.holdingbranch,
110	items.itemcallnumber,	110	items.itemcallnumber,
111	items.itemnumber,	111	items.itemnumber,
Lines 120-141 my $strsth = Link Here
120	GROUP_CONCAT(DISTINCT items.itype	120	GROUP_CONCAT(DISTINCT items.itype
121	ORDER BY items.itemnumber SEPARATOR '\|') as l_itype,	121	ORDER BY items.itemnumber SEPARATOR '\|') as l_itype,
122		122
123	reserves.found,	123	holds.status,
124	biblio.title,	124	biblio.title,
125	biblio.subtitle,	125	biblio.subtitle,
126	biblio.medium,	126	biblio.medium,
127	biblio.part_number,	127	biblio.part_number,
128	biblio.part_name,	128	biblio.part_name,
129	biblio.author,	129	biblio.author,
130	count(DISTINCT reserves.borrowernumber) as reservecount,	130	count(DISTINCT holds.patron_id) as reservecount,
131	count(DISTINCT items.itemnumber) $include_aqorders_qty as itemcount	131	count(DISTINCT items.itemnumber) $include_aqorders_qty as itemcount
132	FROM reserves	132	FROM holds
133	LEFT JOIN items ON items.biblionumber=reserves.biblionumber	133	LEFT JOIN items ON items.biblionumber=holds.biblio_id
134	LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber	134	LEFT JOIN biblio ON biblio.biblionumber=holds.biblio_id
135	$include_aqorders_qty_join	135	$include_aqorders_qty_join
136	WHERE	136	WHERE
137	notforloan $nfl_comparison 0 AND damaged = 0 AND itemlost = 0 AND withdrawn = 0	137	notforloan $nfl_comparison 0 AND damaged = 0 AND itemlost = 0 AND withdrawn = 0
138	AND suspend $sus_comparison 1	138	AND holds.completed=0
		139	AND suspended $sus_comparison 1
139	$sqldatewhere	140	$sqldatewhere
140	";	141	";
141		142
Lines 144-150 if (C4::Context->preference('IndependentBranches')){ Link Here
144	push @query_params, C4::Context->userenv->{'branch'};	145	push @query_params, C4::Context->userenv->{'branch'};
145	}	146	}
146		147
147	$strsth .= " GROUP BY reserves.biblionumber ORDER BY reservecount DESC";	148	$strsth .= " GROUP BY holds.biblio_id ORDER BY reservecount DESC";
148		149
149	$template->param(sql => $strsth);	150	$template->param(sql => $strsth);
150	my $sth = $dbh->prepare($strsth);	151	my $sth = $dbh->prepare($strsth);
Lines 158-164 while ( my $data = $sth->fetchrow_hashref ) { Link Here
158	push(	159	push(
159	@reservedata,	160	@reservedata,
160	{	161	{
161	reservedate => $data->{reservedate},	162	reservedate => $data->{created_date},
162	priority => $data->{priority},	163	priority => $data->{priority},
163	name => $data->{borrower},	164	name => $data->{borrower},
164	title => $data->{title},	165	title => $data->{title},

Lines 176-192 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/pendingreserves.tt (-8 / +7 lines)
176	[% END %]	176	[% END %]
177	</ul>	177	</ul>
178	</td>	178	</td>
179	<td data-order="[% hold.reservedate \| html %]">	179	<td data-order="[% hold.created_date \| html %]">
180	[% hold.reservedate \| $KohaDates %] in [% Branches.GetName ( hold.branchcode ) \| html %]	180	[% hold.created_date \| $KohaDates %] in [% Branches.GetName ( hold.pickup_library_id ) \| html %]
181	</td>	181	</td>
182	<td>[% hold.reservenotes \| html %]</td>	182	<td>[% hold.notes \| html %]</td>
183	<td>	183	<td>
184	[% Branches.GetName ( hold.branchcode ) \| html %]	184	[% Branches.GetName ( hold.pickup_library_id ) \| html %]
185	</td>	185	</td>
186	<td>	186	<td>
187	<form name="cancelReserve" action="/cgi-bin/koha/circ/pendingreserves.pl" method="post">	187	<form name="cancelReserve" action="/cgi-bin/koha/circ/pendingreserves.pl" method="post">
188	<input type="hidden" name="op" value="cancel_reserve" />	188	<input type="hidden" name="op" value="cancel_reserve" />
189	<input type="hidden" name="reserve_id" value="[% hold.reserve_id \| html %]" />	189	<input type="hidden" name="reserve_id" value="[% hold.hold_id \| html %]" />
190		190
191	[% SET hold_cancellation = AuthorisedValues.GetAuthValueDropbox('HOLD_CANCELLATION') %]	191	[% SET hold_cancellation = AuthorisedValues.GetAuthValueDropbox('HOLD_CANCELLATION') %]
192	[% IF hold_cancellation %]	192	[% IF hold_cancellation %]
Lines 209-217 Link Here
209	</form>	209	</form>
210		210
211	[% IF Koha.Preference('CanMarkHoldsToPullAsLost') != 'do_not_allow' %]	211	[% IF Koha.Preference('CanMarkHoldsToPullAsLost') != 'do_not_allow' %]
212	[% IF hold.itemnumber %]	212	[% IF hold.item_id %]
213	<form name="cancelReserve" action="/cgi-bin/koha/circ/pendingreserves.pl" method="post">	213	<form name="cancelReserve" action="/cgi-bin/koha/circ/pendingreserves.pl" method="post">
214	<input type="hidden" name="reserve_id" value="[% hold.reserve_id \| html %]" />	214	<input type="hidden" name="reserve_id" value="[% hold.hold_id \| html %]" />
215	[% IF Koha.Preference('CanMarkHoldsToPullAsLost') == 'allow' %]	215	[% IF Koha.Preference('CanMarkHoldsToPullAsLost') == 'allow' %]
216	<input type="hidden" name="op" value="mark_as_lost" />	216	<input type="hidden" name="op" value="mark_as_lost" />
217	<input type="submit" value="Mark item as lost" />	217	<input type="submit" value="Mark item as lost" />
218	-