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 $manager_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 ($manager_branchcode ne 'ALL') {
92 $whereBranchCode = "AND m.branchcode = '$manager_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, al.note,
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 if($transaction_type eq 'ACT' && ($row->{accounttype} !~ /^C$|^CR$|^LR$|^Pay$/)){
131 if($row->{accounttype} =~ /^C$|^CR$|^LR$/){
132 $grantotal -= abs($row->{amount});
133 $row->{amount} = '-' . $row->{amount};
134 }elsif($row->{accounttype} eq 'FORW' || $row->{accounttype} eq 'W'){
136 $grantotal += abs($row->{amount});
141 my @currency = GetCurrency();
142 $grantotal = sprintf("%.2f", $grantotal);
144 if($output eq 'screen'){
146 loopresult => \@loopresult,
150 binmode STDOUT, ':encoding(UTF-8)';
151 print $input->header(
152 -type => 'application/vnd.sun.xml.calc',
153 -encoding => 'utf-8',
154 -name => "$basename.csv",
155 -attachment => "$basename.csv"
158 print "Manager name".$sep;
159 print "Borrower cardnumber".$sep;
160 print "Borrower name".$sep;
162 print "Transaction date".$sep;
163 print "Transaction type".$sep;
165 print "Biblio title".$sep;
166 print "Barcode".$sep;
167 print "Document type"."\n";
169 foreach my $item (@loopresult){
170 print $item->{mfirstname}. ' ' . $item->{msurname} . $sep;
171 print $item->{cardnumber}.$sep;
172 print $item->{bfirstname}. ' ' . $item->{bsurname} . $sep;
173 print $item->{branchname}.$sep;
174 print $item->{date}.$sep;
175 print $item->{accounttype}.$sep;
176 print $item->{amount}.$sep;
177 print $item->{title}.$sep;
178 print $item->{barcode}.$sep;
179 print $item->{itype}."\n";
183 print $grantotal."\n";
189 ### fromdate final: $fromDate
190 ### toDate final: $toDate
192 beginDate => dt_from_string($fromDate),
193 endDate => dt_from_string($toDate),
194 transaction_type => $transaction_type,
195 branchloop => C4::Branch::GetBranchesLoop($manager_branchcode),
196 manualinv_types => $manualinv_types,
197 CGIsepChoice => GetDelimiterChoices,
200 output_html_with_http_headers $input, $cookie, $template->output;