3 # This file is part of Koha.
5 # Koha is free software; you can redistribute it and/or modify it under the
6 # terms of the GNU General Public License as published by the Free Software
9 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
10 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
11 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
13 # You should have received a copy of the GNU General Public License along
14 # with Koha; if not, write to the Free Software Foundation, Inc.,
15 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
27 use Text::CSV::Encoded;
28 use List::Util qw/any/;
30 use Koha::Account::CreditTypes;
31 use Koha::Account::DebitTypes;
34 my $dbh = C4::Context->dbh;
36 my ($template, $borrowernumber, $cookie) = get_template_and_user({
37 template_name => "reports/cash_register_stats.tt",
41 flagsrequired => {reports => '*'},
45 my $do_it = $input->param('do_it');
46 my $output = $input->param("output");
47 my $basename = $input->param("basename");
48 my $transaction_type = $input->param("transaction_type") || 'ACT';
49 my $manager_branchcode = $input->param("branch") || C4::Context->userenv->{'branch'};
53 CGIsepChoice => GetDelimiterChoices,
56 #Initialize date pickers to today
57 my $fromDate = dt_from_string;
58 my $toDate = dt_from_string;
61 Koha::Account::DebitTypes->search()->as_list;
63 Koha::Account::CreditTypes->search()->as_list;
67 $fromDate = output_pref({ dt => eval { dt_from_string(scalar $input->param("from")) } || dt_from_string,
68 dateformat => 'sql', dateonly => 1 }); #for sql query
69 $toDate = output_pref({ dt => eval { dt_from_string(scalar $input->param("to")) } || dt_from_string,
70 dateformat => 'sql', dateonly => 1 }); #for sql query
73 my @extra_params; # if we add conditions to the select we need extra params
75 if ($transaction_type eq 'ALL') { #All Transactons
77 } elsif ($transaction_type eq 'ACT') { #Active
78 $whereTType = q{ AND credit_type_code IN ('PAYMENT','CREDIT') };
79 } elsif ($transaction_type eq 'FORW') {
80 $whereTType = q{ AND credit_type_code IN ('FORGIVEN','WRITEOFF') };
82 if ( any { $transaction_type eq $_->code } @debit_types ) {
83 $whereTType = q{ AND debit_type_code = ? };
84 push @extra_params, $transaction_type;
86 $whereTType = q{ AND credit_type_code = ? };
87 push @extra_params, $transaction_type;
91 my $whereBranchCode = q{};
92 if ($manager_branchcode ne 'ALL') {
93 $whereBranchCode = q{ AND m.branchcode = ?};
94 push @extra_params, $manager_branchcode;
99 SELECT round(amount,2) AS amount, description,
100 bo.surname AS bsurname, bo.firstname AS bfirstname, m.surname AS msurname, m.firstname AS mfirstname,
101 bo.cardnumber, br.branchname, bo.borrowernumber,
102 al.borrowernumber, DATE(al.date) as date, al.credit_type_code, al.debit_type_code, al.amountoutstanding, al.note,
103 bi.title, bi.biblionumber, i.barcode, i.itype
105 LEFT JOIN borrowers bo ON (al.borrowernumber = bo.borrowernumber)
106 LEFT JOIN borrowers m ON (al.manager_id = m.borrowernumber)
107 LEFT JOIN branches br ON (br.branchcode = m.branchcode )
108 LEFT JOIN items i ON (i.itemnumber = al.itemnumber)
109 LEFT JOIN biblio bi ON (bi.biblionumber = i.biblionumber)
110 WHERE CAST(al.date AS DATE) BETWEEN ? AND ?
115 my $sth_stats = $dbh->prepare($query) or die "Unable to prepare query " . $dbh->errstr;
116 $sth_stats->execute($fromDate, $toDate, @extra_params) or die "Unable to execute query " . $sth_stats->errstr;
120 while ( my $row = $sth_stats->fetchrow_hashref()) {
121 $row->{amountoutstanding} = 0 if (!$row->{amountoutstanding});
122 #if ((abs($row->{amount}) - $row->{amountoutstanding}) > 0) {
123 $row->{amount} = sprintf("%.2f", abs ($row->{amount}));
124 $row->{date} = dt_from_string($row->{date}, 'sql');
126 push (@loopresult, $row);
127 if($transaction_type eq 'ACT' && ($row->{credit_type_code} !~ /^CREDIT$|^PAYMENT$/)){
131 if($row->{credit_type_code} =~ /^CREDIT$/){
132 $grantotal -= abs($row->{amount});
133 $row->{amount} = '-' . $row->{amount};
134 }elsif($row->{credit_type_code} eq 'FORGIVEN' || $row->{credit_type_code} eq 'WRITEOFF'){
136 $grantotal += abs($row->{amount});
141 $grantotal = sprintf("%.2f", $grantotal);
143 if($output eq 'screen'){
145 loopresult => \@loopresult,
150 my $reportname = $input->param('basename');
151 my $reportfilename = $reportname ? "$reportname.$format" : "reportresults.$format" ;
152 my $delimiter = C4::Context->preference('delimiter') || ',';
154 foreach my $row (@loopresult) {
156 push @rowValues, $row->{mfirstname}. ' ' . $row->{msurname},
158 $row->{bfirstname} . ' ' . $row->{bsurname},
168 push (@rows, \@rowValues) ;
171 for (1..6){push(@total,"")};
172 push(@total, $grantotal);
173 print $input->header(
175 -encoding => 'utf-8',
176 -attachment => $reportfilename,
177 -name => $reportfilename
179 my $csvTemplate = C4::Templates::gettemplate('reports/csv/cash_register_stats.tt', 'intranet', $input);
180 $csvTemplate->param(sep => $delimiter, rows => \@rows, total => \@total );
181 print $csvTemplate->output;
188 beginDate => $fromDate,
190 transaction_type => $transaction_type,
191 branchloop => Koha::Libraries->search({}, { order_by => ['branchname'] })->unblessed,
192 debit_types => \@debit_types,
193 credit_types => \@credit_types,
194 CGIsepChoice => GetDelimiterChoices,
197 output_html_with_http_headers $input, $cookie, $template->output;