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/;
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'};
49 our $sep = $input->param("sep") // ',';
50 $sep = "\t" if ($sep eq 'tabulation');
54 CGIsepChoice => GetDelimiterChoices,
57 #Initialize date pickers to today
58 my $fromDate = dt_from_string;
59 my $toDate = dt_from_string;
60 ### fromdate today: $fromDate
62 my $query_manualinv = "SELECT id, authorised_value FROM authorised_values WHERE category = 'MANUAL_INV'";
63 my $sth_manualinv = $dbh->prepare($query_manualinv) or die "Unable to prepare query" . $dbh->errstr;
64 $sth_manualinv->execute() or die "Unable to execute query " . $sth_manualinv->errstr;
65 my $manualinv_types = $sth_manualinv->fetchall_arrayref({});
71 $fromDate = output_pref({ dt => eval { dt_from_string($input->param("filter_date_begin")) } || dt_from_string,
72 dateformat => 'sql', dateonly => 1 }); #for sql query
73 $toDate = output_pref({ dt => eval { dt_from_string($input->param("filter_date_end")) } || dt_from_string,
74 dateformat => 'sql', dateonly => 1 }); #for sql query
78 if ($transaction_type eq 'ALL') { #All Transactons
80 } elsif ($transaction_type eq 'ACT') { #Active
81 $whereTType = " accounttype NOT IN ('F', 'FU', 'FOR', 'M', 'L') AND ";
82 } else { #Single transac type
83 if ($transaction_type eq 'FORW') {
84 $whereTType = " accounttype = 'FOR' OR accounttype = 'W' AND ";
86 $whereTType = " accounttype = '$transaction_type' AND ";
90 my $whereBranchCode = '';
91 if ($branchcode ne 'ALL') {
92 $whereBranchCode = "AND bo.branchcode = '$branchcode'";
95 ### $transaction_type;
98 SELECT round(amount,2) AS amount, description,
99 bo.surname AS bsurname, bo.firstname AS bfirstname, m.surname AS msurname, m.firstname AS mfirstname,
100 bo.cardnumber, br.branchname, bo.borrowernumber,
101 al.borrowernumber, DATE(al.date) as date, al.accounttype, al.amountoutstanding,
102 bi.title, bi.biblionumber, i.barcode, i.itype
104 LEFT JOIN borrowers bo ON (al.borrowernumber = bo.borrowernumber)
105 LEFT JOIN borrowers m ON (al.manager_id = m.borrowernumber)
106 LEFT JOIN branches br ON (br.branchcode = m.branchcode )
107 LEFT JOIN items i ON (i.itemnumber = al.itemnumber)
108 LEFT JOIN biblio bi ON (bi.biblionumber = i.biblionumber)
110 CAST(al.date AS DATE) BETWEEN ? AND ?
114 my $sth_stats = $dbh->prepare($query) or die "Unable to prepare query" . $dbh->errstr;
115 $sth_stats->execute($fromDate, $toDate) or die "Unable to execute query " . $sth_stats->errstr;
119 while ( my $row = $sth_stats->fetchrow_hashref()) {
120 $row->{amountoutstanding} = 0 if (!$row->{amountoutstanding});
121 #if ((abs($row->{amount}) - $row->{amountoutstanding}) > 0) {
122 $row->{amount} = sprintf("%.2f", abs ($row->{amount}));
123 $row->{date} = dt_from_string($row->{date}, 'sql');
124 ### date : $row->{date}
126 push (@loopresult, $row);
127 $grantotal += abs($row->{amount});
131 my @currency = GetCurrency();
132 $grantotal = sprintf("%.2f", $grantotal);
134 if($output eq 'screen'){
136 loopresult => \@loopresult,
140 binmode STDOUT, ':encoding(UTF-8)';
141 print $input->header(
142 -type => 'application/vnd.sun.xml.calc',
143 -encoding => 'utf-8',
144 -name => "$basename.csv",
145 -attachment => "$basename.csv"
148 print "Manager name".$sep;
149 print "Borrower cardnumber".$sep;
150 print "Borrower name".$sep;
152 print "Transaction date".$sep;
153 print "Transaction type".$sep;
155 print "Biblio title".$sep;
156 print "Barcode".$sep;
157 print "Document type"."\n";
159 foreach my $item (@loopresult){
160 print $item->{mfirstname}. ' ' . $item->{msurname} . $sep;
161 print $item->{cardnumber}.$sep;
162 print $item->{bfirstname}. ' ' . $item->{bsurname} . $sep;
163 print $item->{branchname}.$sep;
164 print $item->{date}.$sep;
165 print $item->{accounttype}.$sep;
166 print $item->{amount}.$sep;
167 print $item->{title}.$sep;
168 print $item->{barcode}.$sep;
169 print $item->{itype}."\n";
173 print $grantotal."\n";
179 ### fromdate final: $fromDate
180 ### toDate final: $toDate
182 beginDate => dt_from_string($fromDate),
183 endDate => dt_from_string($toDate),
184 transaction_type => $transaction_type,
185 branchloop => C4::Branch::GetBranchesLoop($branchcode),
186 manualinv_types => $manualinv_types,
187 CGIsepChoice => GetDelimiterChoices,
190 output_html_with_http_headers $input, $cookie, $template->output;