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 C4::Budgets qw/GetCurrency GetCurrencies/;
28 use Text::CSV::Encoded;
33 my $dbh = C4::Context->dbh;
35 my ($template, $borrowernumber, $cookie) = get_template_and_user({
36 template_name => "reports/cash_register_stats.tt",
40 flagsrequired => {reports => '*'},
44 my $do_it = $input->param('do_it');
45 my $output = $input->param("output");
46 my $basename = $input->param("basename");
47 my $transaction_type = $input->param("transaction_type") || 'ACT';
48 my $manager_branchcode = $input->param("branch") || C4::Context->userenv->{'branch'};
52 CGIsepChoice => GetDelimiterChoices,
55 #Initialize date pickers to today
56 my $fromDate = dt_from_string;
57 my $toDate = dt_from_string;
59 my $query_manualinv = "SELECT id, authorised_value FROM authorised_values WHERE category = 'MANUAL_INV'";
60 my $sth_manualinv = $dbh->prepare($query_manualinv) or die "Unable to prepare query" . $dbh->errstr;
61 $sth_manualinv->execute() or die "Unable to execute query " . $sth_manualinv->errstr;
62 my $manualinv_types = $sth_manualinv->fetchall_arrayref({});
67 $fromDate = output_pref({ dt => eval { dt_from_string($input->param("filter_date_begin")) } || dt_from_string,
68 dateformat => 'sql', dateonly => 1 }); #for sql query
69 $toDate = output_pref({ dt => eval { dt_from_string($input->param("filter_date_end")) } || dt_from_string,
70 dateformat => 'sql', dateonly => 1 }); #for sql query
74 if ($transaction_type eq 'ALL') { #All Transactons
76 } elsif ($transaction_type eq 'ACT') { #Active
77 $whereTType = " accounttype NOT IN ('F', 'FU', 'FOR', 'M', 'L') AND ";
78 } else { #Single transac type
79 if ($transaction_type eq 'FORW') {
80 $whereTType = " accounttype = 'FOR' OR accounttype = 'W' AND ";
82 $whereTType = " accounttype = '$transaction_type' AND ";
86 my $whereBranchCode = '';
87 if ($manager_branchcode ne 'ALL') {
88 $whereBranchCode = "AND m.branchcode = '$manager_branchcode'";
93 SELECT round(amount,2) AS amount, description,
94 bo.surname AS bsurname, bo.firstname AS bfirstname, m.surname AS msurname, m.firstname AS mfirstname,
95 bo.cardnumber, br.branchname, bo.borrowernumber,
96 al.borrowernumber, DATE(al.date) as date, al.accounttype, al.amountoutstanding, al.note,
97 bi.title, bi.biblionumber, i.barcode, i.itype
99 LEFT JOIN borrowers bo ON (al.borrowernumber = bo.borrowernumber)
100 LEFT JOIN borrowers m ON (al.manager_id = m.borrowernumber)
101 LEFT JOIN branches br ON (br.branchcode = m.branchcode )
102 LEFT JOIN items i ON (i.itemnumber = al.itemnumber)
103 LEFT JOIN biblio bi ON (bi.biblionumber = i.biblionumber)
105 CAST(al.date AS DATE) BETWEEN ? AND ?
109 my $sth_stats = $dbh->prepare($query) or die "Unable to prepare query" . $dbh->errstr;
110 $sth_stats->execute($fromDate, $toDate) or die "Unable to execute query " . $sth_stats->errstr;
114 while ( my $row = $sth_stats->fetchrow_hashref()) {
115 $row->{amountoutstanding} = 0 if (!$row->{amountoutstanding});
116 #if ((abs($row->{amount}) - $row->{amountoutstanding}) > 0) {
117 $row->{amount} = sprintf("%.2f", abs ($row->{amount}));
118 $row->{date} = dt_from_string($row->{date}, 'sql');
120 push (@loopresult, $row);
121 if($transaction_type eq 'ACT' && ($row->{accounttype} !~ /^C$|^CR$|^LR$|^Pay$/)){
125 if($row->{accounttype} =~ /^C$|^CR$|^LR$/){
126 $grantotal -= abs($row->{amount});
127 $row->{amount} = '-' . $row->{amount};
128 }elsif($row->{accounttype} eq 'FORW' || $row->{accounttype} eq 'W'){
130 $grantotal += abs($row->{amount});
135 my @currency = GetCurrency();
136 $grantotal = sprintf("%.2f", $grantotal);
138 if($output eq 'screen'){
140 loopresult => \@loopresult,
144 binmode STDOUT, ':encoding(UTF-8)';
147 my $reportname = $input->param('basename');
148 my $reportfilename = $reportname ? "$reportname.$format" : "reportresults.$format" ;
149 #my $reportfilename = "$reportname.html" ;
150 my $delimiter = C4::Context->preference('delimiter') || ',';
151 my ( $type, $content );
152 if ( $format eq 'csv' ) {
153 my $type = 'application/csv';
154 my $csv = Text::CSV::Encoded->new({ encoding_out => 'UTF-8', sep_char => $delimiter});
155 $csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag();
157 push @headers, "mfirstname",
167 if ($csv->combine(@headers)) {
168 $content .= Encode::decode('UTF-8', $csv->string()) . "\n";
170 push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ;
172 foreach my $row (@loopresult) {
174 push @rowValues, $row->{mfirstname},
184 if ($csv->combine(@rowValues)) {
185 $content .= $csv->string() . "\n";
187 push @$q_errors, { combine => $csv->error_diag() } ;
191 print $input->header(
193 -attachment=> $reportfilename
197 print $delimiter x 6;
198 print $grantotal."\n";
199 foreach my $err (@$q_errors) {
200 print "# ERROR: " . (map {$_ . ": " . $err->{$_}} keys %$err) . "\n";
201 } # here we print all the non-fatal errors at the end. Not super smooth, but better than nothing.
208 beginDate => $fromDate,
210 transaction_type => $transaction_type,
211 branchloop => C4::Branch::GetBranchesLoop($manager_branchcode),
212 manualinv_types => $manualinv_types,
213 CGIsepChoice => GetDelimiterChoices,
216 output_html_with_http_headers $input, $cookie, $template->output;