3 # This file is part of Koha.
5 # Koha is free software; you can redistribute it and/or modify it
6 # under the terms of the GNU General Public License as published by
7 # the Free Software Foundation; either version 3 of the License, or
8 # (at your option) any later version.
10 # Koha is distributed in the hope that it will be useful, but
11 # WITHOUT ANY WARRANTY; without even the implied warranty of
12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 # GNU General Public License for more details.
15 # You should have received a copy of the GNU General Public License
16 # along with Koha; if not, see <http://www.gnu.org/licenses>.
19 use C4::Auth qw( get_template_and_user );
22 use C4::Reports qw( GetDelimiterChoices );
23 use C4::Output qw( output_html_with_http_headers );
25 use Koha::DateUtils qw( dt_from_string );
26 use Text::CSV::Encoded;
27 use List::Util qw( any );
29 use Koha::Account::CreditTypes;
30 use Koha::Account::DebitTypes;
33 my $dbh = C4::Context->dbh;
35 my ($template, $borrowernumber, $cookie) = get_template_and_user({
36 template_name => "reports/cash_register_stats.tt",
39 flagsrequired => {reports => '*'},
42 my $do_it = $input->param('do_it');
43 my $output = $input->param("output");
44 my $basename = $input->param("basename");
45 my $transaction_type = $input->param("transaction_type") || 'ACT';
46 my $manager_branchcode = $input->param("branch") || C4::Context->userenv->{'branch'};
50 CGIsepChoice => GetDelimiterChoices,
53 #Initialize date pickers to today
54 my $fromDate = $input->param("from") || dt_from_string;
55 my $toDate = $input->param("to") || dt_from_string;
58 Koha::Account::DebitTypes->search()->as_list;
60 Koha::Account::CreditTypes->search()->as_list;
67 my @extra_params; # if we add conditions to the select we need extra params
69 if ($transaction_type eq 'ALL') { #All Transactions
71 } elsif ($transaction_type eq 'ACT') { #Active
72 $whereTType = q{ AND credit_type_code IN ('PAYMENT','CREDIT') };
73 } elsif ($transaction_type eq 'FORW') {
74 $whereTType = q{ AND credit_type_code IN ('FORGIVEN','WRITEOFF') };
76 if ( any { $transaction_type eq $_->code } @debit_types ) {
77 $whereTType = q{ AND debit_type_code = ? };
78 push @extra_params, $transaction_type;
80 $whereTType = q{ AND credit_type_code = ? };
81 push @extra_params, $transaction_type;
85 if ( $transaction_type eq 'PAYMENT' || $transaction_type eq 'ACT' ) {
86 $whereTType .= q{ AND (status != 'VOID' OR status IS NULL) };
89 my $whereBranchCode = q{};
90 if ($manager_branchcode ne 'ALL') {
91 $whereBranchCode = q{ AND m.branchcode = ?};
92 push @extra_params, $manager_branchcode;
95 my $whereRegister = q{};
96 $registerid = $input->param("registerid");
98 $whereRegister = q{ AND al.register_id = ?};
99 push @extra_params, $registerid;
103 SELECT round(amount,2) AS amount, al.description,
104 bo.surname AS bsurname, bo.firstname AS bfirstname, m.surname AS msurname, m.firstname AS mfirstname,
105 bo.cardnumber, br.branchname, bo.borrowernumber,
106 al.borrowernumber, DATE(al.date) as date, al.credit_type_code, al.debit_type_code, COALESCE(act.description,al.credit_type_code,adt.description,al.debit_type_code) AS type_description, al.amountoutstanding, al.note, al.timestamp,
107 bi.title, bi.biblionumber, i.barcode, i.itype
109 LEFT JOIN borrowers bo ON (al.borrowernumber = bo.borrowernumber)
110 LEFT JOIN borrowers m ON (al.manager_id = m.borrowernumber)
111 LEFT JOIN cash_registers cr ON (al.register_id = cr.id)
112 LEFT JOIN branches br ON (br.branchcode = cr.branch)
113 LEFT JOIN items i ON (i.itemnumber = al.itemnumber)
114 LEFT JOIN biblio bi ON (bi.biblionumber = i.biblionumber)
115 LEFT JOIN account_credit_types act ON (al.credit_type_code = act.code)
116 LEFT JOIN account_debit_types adt ON (al.debit_type_code = adt.code)
117 WHERE CAST(al.date AS DATE) BETWEEN ? AND ?
123 my $sth_stats = $dbh->prepare($query) or die "Unable to prepare query " . $dbh->errstr;
124 $sth_stats->execute($fromDate, $toDate, @extra_params) or die "Unable to execute query " . $sth_stats->errstr;
128 while ( my $row = $sth_stats->fetchrow_hashref()) {
129 $row->{amountoutstanding} = 0 if (!$row->{amountoutstanding});
130 #if ((abs($row->{amount}) - $row->{amountoutstanding}) > 0) {
131 $row->{amount} = sprintf("%.2f", abs ($row->{amount}));
132 $row->{date} = dt_from_string($row->{date}, 'sql');
134 push (@loopresult, $row);
135 if($transaction_type eq 'ACT' && ($row->{credit_type_code} !~ /^CREDIT$|^PAYMENT$/)){
139 if($row->{credit_type_code} =~ /^CREDIT$/){
140 $grantotal -= abs($row->{amount});
141 $row->{amount} = '-' . $row->{amount};
142 }elsif($row->{credit_type_code} eq 'FORGIVEN' || $row->{credit_type_code} eq 'WRITEOFF'){
144 $grantotal += abs($row->{amount});
149 $grantotal = sprintf("%.2f", $grantotal);
151 if($output eq 'screen'){
153 loopresult => \@loopresult,
158 my $reportname = $input->param('basename');
159 my $reportfilename = $reportname ? "$reportname.$format" : "reportresults.$format" ;
160 my $delimiter = C4::Context->csv_delimiter;
162 foreach my $row (@loopresult) {
164 push @rowValues, $row->{mfirstname}. ' ' . $row->{msurname},
166 $row->{bfirstname} . ' ' . $row->{bsurname},
170 $row->{type_description},
176 push (@rows, \@rowValues) ;
179 for (1..7){push(@total,"")};
180 push(@total, $grantotal);
181 print $input->header(
183 -encoding => 'utf-8',
184 -attachment => $reportfilename,
185 -name => $reportfilename
187 my $csvTemplate = C4::Templates::gettemplate('reports/csv/cash_register_stats.tt', 'intranet', $input);
188 $csvTemplate->param(sep => $delimiter, rows => \@rows, total => \@total );
189 print $csvTemplate->output;
196 beginDate => $fromDate,
198 transaction_type => $transaction_type,
199 branchloop => Koha::Libraries->search({}, { order_by => ['branchname'] })->unblessed,
200 debit_types => \@debit_types,
201 credit_types => \@credit_types,
202 registerid => $registerid,
203 CGIsepChoice => GetDelimiterChoices,
206 output_html_with_http_headers $input, $cookie, $template->output;