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.
26 use C4::Dates qw/format_date format_date_in_iso/;
27 use C4::Budgets qw/GetCurrency GetCurrencies/;
32 my $dbh = C4::Context->dbh;
33 my $fullreportname = "reports/cash_register_stats.tt";
35 my ($template, $borrowernumber, $cookie) = get_template_and_user({
36 template_name => $fullreportname,
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 $branchcode = $input->param("branch") || C4::Context->userenv->{'branch'};
53 DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
56 #Initialize date pickers to today
57 my $today = C4::Dates->today('iso');
58 my $fromDate = $today;
61 ### fromdate today: $fromDate
63 my $query_manualinv = "SELECT id, authorised_value FROM authorised_values WHERE category = 'MANUAL_INV'";
64 my $sth_manualinv = $dbh->prepare($query_manualinv) or die "Unable to prepare query" . $dbh->errstr;
65 $sth_manualinv->execute() or die "Unable to execute query " . $sth_manualinv->errstr;
66 my $manualinv_types = $sth_manualinv->fetchall_arrayref({});
72 $fromDate = format_date_in_iso($input->param("filter_date_begin"));
73 $toDate = format_date_in_iso($input->param("filter_date_end"));
77 if ($transaction_type eq 'ALL') { #All Transactons
79 } elsif ($transaction_type eq 'ACT') { #Active
80 $whereTType = " accounttype NOT IN ('F', 'FU', 'FOR', 'M', 'L') AND ";
81 } else { #Single transac type
82 if ($transaction_type eq 'FORW') {
83 $whereTType = " accounttype = 'FOR' OR accounttype = 'W' AND ";
85 $whereTType = " accounttype = '$transaction_type' AND ";
89 my $whereBranchCode = '';
90 if ($branchcode ne 'ALL') {
91 $whereBranchCode = "AND bo.branchcode = '$branchcode'";
94 ### $transaction_type;
97 SELECT round(amount,2) AS amount, description,
98 bo.surname AS bsurname, bo.firstname AS bfirstname, m.surname AS msurname, m.firstname AS mfirstname,
99 bo.cardnumber, br.branchname, bo.borrowernumber,
100 al.borrowernumber, DATE(al.date) as date, al.accounttype, al.amountoutstanding,
101 bi.title, bi.biblionumber, i.barcode, i.itype
103 LEFT JOIN borrowers bo ON (al.borrowernumber = bo.borrowernumber)
104 LEFT JOIN borrowers m ON (al.manager_id = m.borrowernumber)
105 LEFT JOIN branches br ON (br.branchcode = m.branchcode )
106 LEFT JOIN items i ON (i.itemnumber = al.itemnumber)
107 LEFT JOIN biblio bi ON (bi.biblionumber = i.biblionumber)
109 CAST(al.date AS DATE) BETWEEN ? AND ?
113 my $sth_stats = $dbh->prepare($query) or die "Unable to prepare query" . $dbh->errstr;
114 $sth_stats->execute($fromDate, $toDate) or die "Unable to execute query " . $sth_stats->errstr;
118 while ( my $row = $sth_stats->fetchrow_hashref()) {
119 $row->{amountoutstanding} = 0 if (!$row->{amountoutstanding});
120 #if ((abs($row->{amount}) - $row->{amountoutstanding}) > 0) {
121 $row->{amount} = sprintf("%.2f", abs ($row->{amount}));
122 $row->{date} = format_date($row->{date});
123 ### date : $row->{date}
125 push (@loopresult, $row);
126 $grantotal += abs($row->{amount});
130 my @currency = GetCurrency();
131 $grantotal = sprintf("%.2f", $grantotal);
133 if($output eq 'screen'){
135 loopresult => \@loopresult,
139 binmode STDOUT, ':encoding(UTF-8)';
140 print $input->header(
141 -type => 'application/vnd.sun.xml.calc',
142 -encoding => 'utf-8',
143 -name => "$basename.csv",
144 -attachment => "$basename.csv"
147 print "Manager name".$sep;
148 print "Borrower cardnumber".$sep;
149 print "Borrower name".$sep;
151 print "Transaction date".$sep;
152 print "Transaction type".$sep;
154 print "Biblio title".$sep;
155 print "Barcode".$sep;
156 print "Document type"."\n";
158 foreach my $item (@loopresult){
159 print $item->{mfirstname}. ' ' . $item->{msurname} . $sep;
160 print $item->{cardnumber}.$sep;
161 print $item->{bfirstname}. ' ' . $item->{bsurname} . $sep;
162 print $item->{branchname}.$sep;
163 print $item->{date}.$sep;
164 print $item->{accounttype}.$sep;
165 print $item->{amount}.$sep;
166 print $item->{title}.$sep;
167 print $item->{barcode}.$sep;
168 print $item->{itype}."\n";
172 print $grantotal."\n";
178 ### fromdate final: $fromDate
179 ### toDate final: $toDate
181 beginDate => format_date($fromDate),
182 endDate => format_date($toDate),
183 transaction_type => $transaction_type,
184 branchloop => C4::Branch::GetBranchesLoop($branchcode),
185 manualinv_types => $manualinv_types,
187 output_html_with_http_headers $input, $cookie, $template->output;