Attachment #25292 for bug #11703

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




# with Koha; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

use strict;
use warnings;

use CGI;
use JSON qw(to_json);

use C4::Auth qw(check_cookie_auth);
use C4::Biblio qw(GetMarcBiblio GetFrameworkCode GetRecordValue );
use C4::Charset;
use C4::Circulation qw(GetIssuingCharges CanBookBeRenewed GetRenewCount);
use C4::Context;

use Koha::Database;
use Koha::DateUtils;

my $input = new CGI;

    exit 0;
}

my $schema = Koha::Database->new()->schema();

my @sort_columns = qw/date_due title itype issuedate branchcode itemcallnumber/;

my @borrowernumber   = $input->param('borrowernumber');
my $offset           = $input->param('iDisplayStart');
my $results_per_page = $input->param('iDisplayLength') || -1;
my $sorting_column   = $sort_columns[ $input->param('iSortCol_0') ]
  || 'issuedate';
my $sorting_direction = $input->param('sSortDir_0') eq 'asc' ? 'asc' : 'desc';

$results_per_page = undef if ( $results_per_page == -1 );

binmode STDOUT, ":encoding(UTF-8)";
print $input->header( -type => 'text/plain', -charset => 'UTF-8' );

my @parameters;
my $sql = ' 
    SELECT 
        issuedate,
        date_due,

        biblionumber,
        biblio.title,
        author,

        itemnumber,
        barcode,
        itemnotes,
        itemcallnumber,
        replacementprice,

        issues.branchcode,
        branchname,

        itype,
        itemtype,

        borrowernumber,
        surname,
        firstname,
        cardnumber
    FROM issues
        LEFT JOIN items USING ( itemnumber )
        LEFT JOIN biblio USING ( biblionumber )
        LEFT JOIN biblioitems USING ( biblionumber )
        LEFT JOIN borrowers USING ( borrowernumber )
        LEFT JOIN branches ON ( issues.branchcode = branches.branchcode )
    WHERE borrowernumber
';

if ( @borrowernumber == 1 ) {
    $sql .= '= ?';
}
else {
    $sql = ' IN (' . join( ',', ('?') x @borrowernumber ) . ') ';
}
push( @parameters, @borrowernumber );

$sql .= " ORDER BY ? $sorting_direction ";
push( @parameters, $sorting_column );
while ( my $c = $checkouts_rs->next() ) {

my $dbh = C4::Context->dbh();
my $sth = $dbh->prepare($sql);
$sth->execute( @parameters );

my $item_level_itypes = C4::Context->preference('item-level_itypes');
    my $itemnumber     = $c->item()->itemnumber();
    my $biblionumber   = $c->item()->biblionumber();

my @checkouts;
while ( my $c = $sth->fetchrow_hashref() ) {
    my ($charge) = GetIssuingCharges( $c->{itemnumber}, $c->{borrowernumber} );

    my ( $can_renew, $can_renew_error ) =
      CanBookBeRenewed( $c->{borrowernumber}, $c->{itemnumber} );

    my ( $renewals_count, $renewals_allowed, $renewals_remaining ) =
      GetRenewCount( $c->{borrowernumber}, $c->{itemnumber} );

    push(
        @checkouts,
        {
            DT_RowId   => $c->{itemnumber} . '-' . $c->{borrowernumber},
            title      => $c->{title},
            author     => $c->{author},
            barcode    => $c->{barcode},
            itemtype   => $item_level_itypes ? $c->{itype} : $c->{itemtype},
            itemnotes  => $c->{itemnotes},
            branchcode => $c->{branchcode},
            branchname => $c->{branchname},
            itemcallnumber => $c->{itemcallnumber}   || q{},
            charge         => $charge,
            price          => $c->{replacementprice} || q{},
            can_renew      => $can_renew,
            can_renew_error    => $can_renew_error,
            itemnumber         => $c->{itemnumber},
            borrowernumber     => $c->{borrowernumber},
            biblionumber       => $c->{biblionumber},
            issuedate          => $c->{issuedate},
            date_due           => $c->{date_due},
            renewals_count     => $renewals_count,
            renewals_allowed   => $renewals_allowed,
            renewals_remaining => $renewals_remaining,
            issuedate_formatted =>
              output_pref( dt_from_string( $c->{issuedate} ) ),
            date_due_formatted =>
              output_pref_due( dt_from_string( $c->{date_due} ) ),
            subtitle => GetRecordValue(
                'subtitle',
                GetMarcBiblio( $c->{biblionumber} ),
                GetFrameworkCode( $c->{biblionumber} )
            ),
            borrower => {
                surname    => $c->{surname},
                firstname  => $c->{firstname},
                cardnumber => $c->{cardnumber},
            }
        }
    );
- 

Return to bug 11703

Lines 19-36 Link Here

(-)a/api/checkouts.pl (-55 / +86 lines)
19	# with Koha; if not, write to the Free Software Foundation, Inc.,	19	# with Koha; if not, write to the Free Software Foundation, Inc.,
20	# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.	20	# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
21		21
22	use Modern::Perl;	22	use strict;
		23	use warnings;
23		24
24	use CGI;	25	use CGI;
25	use JSON qw(to_json);	26	use JSON qw(to_json);
26		27
27	use C4::Auth qw(check_cookie_auth);	28	use C4::Auth qw(check_cookie_auth);
28	use C4::Biblio qw(GetMarcBiblio GetFrameworkCode GetRecordValue );	29	use C4::Biblio qw(GetMarcBiblio GetFrameworkCode GetRecordValue );
29	use C4::Charset;
30	use C4::Circulation qw(GetIssuingCharges CanBookBeRenewed GetRenewCount);	30	use C4::Circulation qw(GetIssuingCharges CanBookBeRenewed GetRenewCount);
31	use C4::Context;	31	use C4::Context;
32		32
33	use Koha::Database;
34	use Koha::DateUtils;	33	use Koha::DateUtils;
35		34
36	my $input = new CGI;	35	my $input = new CGI;
Lines 43-128 if ( $auth_status ne "ok" ) { Link Here
43	exit 0;	42	exit 0;
44	}	43	}
45		44
46	my $schema = Koha::Database->new()->schema();
47
48	my @sort_columns = qw/date_due title itype issuedate branchcode itemcallnumber/;	45	my @sort_columns = qw/date_due title itype issuedate branchcode itemcallnumber/;
49		46
50	my @borrowernumber = $input->param('borrowernumber');	47	my @borrowernumber = $input->param('borrowernumber');
51	my $offset = $input->param('iDisplayStart');	48	my $offset = $input->param('iDisplayStart');
52	my $results_per_page = $input->param('iDisplayLength');	49	my $results_per_page = $input->param('iDisplayLength') \|\| -1;
53	my $sorting_column = $sort_columns[ $input->param('iSortCol_0') ] \|\| 'issuedate';	50	my $sorting_column = $sort_columns[ $input->param('iSortCol_0') ]
54	my $sorting_direction = $input->param('sSortDir_0') \|\| 'desc';	51	\|\| 'issuedate';
		52	my $sorting_direction = $input->param('sSortDir_0') eq 'asc' ? 'asc' : 'desc';
55		53
56	$results_per_page = undef if ( $results_per_page == -1 );	54	$results_per_page = undef if ( $results_per_page == -1 );
57		55
58	binmode STDOUT, ":encoding(UTF-8)";	56	binmode STDOUT, ":encoding(UTF-8)";
59	print $input->header( -type => 'text/plain', -charset => 'UTF-8' );	57	print $input->header( -type => 'text/plain', -charset => 'UTF-8' );
60		58
61	my $checkouts_rs = $schema->resultset('Issue')->search(	59	my @parameters;
62	{ borrowernumber => \@borrowernumber },	60	my $sql = '
63	{	61	SELECT
64	prefetch => { 'item' => 'biblio' },	62	issuedate,
65	order_by => { "-$sorting_direction" => $sorting_column }	63	date_due,
66	}	64
67	);	65	biblionumber,
		66	biblio.title,
		67	author,
		68
		69	itemnumber,
		70	barcode,
		71	itemnotes,
		72	itemcallnumber,
		73	replacementprice,
		74
		75	issues.branchcode,
		76	branchname,
		77
		78	itype,
		79	itemtype,
		80
		81	borrowernumber,
		82	surname,
		83	firstname,
		84	cardnumber
		85	FROM issues
		86	LEFT JOIN items USING ( itemnumber )
		87	LEFT JOIN biblio USING ( biblionumber )
		88	LEFT JOIN biblioitems USING ( biblionumber )
		89	LEFT JOIN borrowers USING ( borrowernumber )
		90	LEFT JOIN branches ON ( issues.branchcode = branches.branchcode )
		91	WHERE borrowernumber
		92	';
		93
		94	if ( @borrowernumber == 1 ) {
		95	$sql .= '= ?';
		96	}
		97	else {
		98	$sql = ' IN (' . join( ',', ('?') x @borrowernumber ) . ') ';
		99	}
		100	push( @parameters, @borrowernumber );
68		101
69	my $borrower;	102	$sql .= " ORDER BY ? $sorting_direction ";
70	my @checkouts;	103	push( @parameters, $sorting_column );
71	while ( my $c = $checkouts_rs->next() ) {
72		104
73	# No point in fetching this every time if only one borrower	105	my $dbh = C4::Context->dbh();
74	$borrower = $c->borrower()	106	my $sth = $dbh->prepare($sql);
75	if ( !$borrower \|\| @borrowernumber > 1 );	107	$sth->execute( @parameters );
76		108
77	my $borrowernumber = $borrower->borrowernumber();	109	my $item_level_itypes = C4::Context->preference('item-level_itypes');
78	my $itemnumber = $c->item()->itemnumber();
79	my $biblionumber = $c->item()->biblionumber();
80		110
81	my ($charge) =	111	my @checkouts;
82	GetIssuingCharges( $c->itemnumber()->itemnumber(), $borrowernumber );	112	while ( my $c = $sth->fetchrow_hashref() ) {
		113	my ($charge) = GetIssuingCharges( $c->{itemnumber}, $c->{borrowernumber} );
83		114
84	my ( $can_renew, $can_renew_error ) =	115	my ( $can_renew, $can_renew_error ) =
85	CanBookBeRenewed( $borrowernumber, $itemnumber );	116	CanBookBeRenewed( $c->{borrowernumber}, $c->{itemnumber} );
86		117
87	my ( $renewals_count, $renewals_allowed, $renewals_remaining ) =	118	my ( $renewals_count, $renewals_allowed, $renewals_remaining ) =
88	GetRenewCount( $borrowernumber, $itemnumber );	119	GetRenewCount( $c->{borrowernumber}, $c->{itemnumber} );
89		120
90	push(	121	push(
91	@checkouts,	122	@checkouts,
92	{	123	{
93	DT_RowId => "$itemnumber-$borrowernumber",	124	DT_RowId => $c->{itemnumber} . '-' . $c->{borrowernumber},
94	title => $c->item()->biblio()->title(),	125	title => $c->{title},
95	author => $c->item()->biblio()->author(),	126	author => $c->{author},
96	barcode => $c->item()->barcode(),	127	barcode => $c->{barcode},
97	itemtype => $c->item()->effective_itemtype(),	128	itemtype => $item_level_itypes ? $c->{itype} : $c->{itemtype},
98	itemnotes => $c->item()->itemnotes(),	129	itemnotes => $c->{itemnotes},
99	branchcode => $c->branchcode(),	130	branchcode => $c->{branchcode},
100	branchname => $c->branch->branchname(),	131	branchname => $c->{branchname},
101	itemcallnumber => $c->item()->itemcallnumber() \|\| q{},	132	itemcallnumber => $c->{itemcallnumber} \|\| q{},
102	charge => $charge,	133	charge => $charge,
103	price => $c->item->replacementprice() \|\| q{},	134	price => $c->{replacementprice} \|\| q{},
104	can_renew => $can_renew,	135	can_renew => $can_renew,
105	can_renew_error => $can_renew_error,	136	can_renew_error => $can_renew_error,
106	itemnumber => $itemnumber,	137	itemnumber => $c->{itemnumber},
107	borrowernumber => $borrowernumber,	138	borrowernumber => $c->{borrowernumber},
108	biblionumber => $biblionumber,	139	biblionumber => $c->{biblionumber},
109	issuedate => $c->issuedate(),	140	issuedate => $c->{issuedate},
110	date_due => $c->date_due(),	141	date_due => $c->{date_due},
111	renewals_count => $renewals_count,	142	renewals_count => $renewals_count,
112	renewals_allowed => $renewals_allowed,	143	renewals_allowed => $renewals_allowed,
113	renewals_remaining => $renewals_remaining,	144	renewals_remaining => $renewals_remaining,
114	issuedate_formatted =>	145	issuedate_formatted =>
115	output_pref( dt_from_string( $c->issuedate() ) ),	146	output_pref( dt_from_string( $c->{issuedate} ) ),
116	date_due_formatted =>	147	date_due_formatted =>
117	output_pref_due( dt_from_string( $c->date_due() ) ),	148	output_pref_due( dt_from_string( $c->{date_due} ) ),
118	subtitle => GetRecordValue(	149	subtitle => GetRecordValue(
119	'subtitle', GetMarcBiblio($biblionumber),	150	'subtitle',
120	GetFrameworkCode($biblionumber)	151	GetMarcBiblio( $c->{biblionumber} ),
		152	GetFrameworkCode( $c->{biblionumber} )
121	),	153	),
122	borrower => {	154	borrower => {
123	surname => $borrower->surname(),	155	surname => $c->{surname},
124	firstname => $borrower->firstname(),	156	firstname => $c->{firstname},
125	cardnumber => $borrower->cardnumber(),	157	cardnumber => $c->{cardnumber},
126	}	158	}
127	}	159	}
128	);	160	);
129	-