#
# This file is part of Koha.
#
-# Koha is free software; you can redistribute it and/or modify it under the
-# terms of the GNU General Public License as published by the Free Software
-# Foundation;
+# Koha is free software; you can redistribute it and/or modify it
+# under the terms of the GNU General Public License as published by
+# the Free Software Foundation; either version 3 of the License, or
+# (at your option) any later version.
#
-# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
-# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
-# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+# Koha is distributed in the hope that it will be useful, but
+# WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
#
-# You should have received a copy of the GNU General Public License along
-# with Koha; if not, write to the Free Software Foundation, Inc.,
-# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
+# You should have received a copy of the GNU General Public License
+# along with Koha; if not, see <http://www.gnu.org/licenses>.
use Modern::Perl;
use C4::Auth;
use DateTime;
use Koha::DateUtils;
use Text::CSV::Encoded;
+use List::Util qw/any/;
+
+use Koha::Account::CreditTypes;
+use Koha::Account::DebitTypes;
my $input = new CGI;
my $dbh = C4::Context->dbh;
my $fromDate = dt_from_string;
my $toDate = dt_from_string;
-my $query_manualinv = "SELECT id, authorised_value FROM authorised_values WHERE category = 'MANUAL_INV'";
-my $sth_manualinv = $dbh->prepare($query_manualinv) or die "Unable to prepare query" . $dbh->errstr;
-$sth_manualinv->execute() or die "Unable to execute query " . $sth_manualinv->errstr;
-my $manualinv_types = $sth_manualinv->fetchall_arrayref({});
-
+my @debit_types =
+ Koha::Account::DebitTypes->search()->as_list;
+my @credit_types =
+ Koha::Account::CreditTypes->search()->as_list;
if ($do_it) {
- $fromDate = output_pref({ dt => eval { dt_from_string($input->param("from")) } || dt_from_string,
+ $fromDate = output_pref({ dt => eval { dt_from_string(scalar $input->param("from")) } || dt_from_string,
dateformat => 'sql', dateonly => 1 }); #for sql query
- $toDate = output_pref({ dt => eval { dt_from_string($input->param("to")) } || dt_from_string,
+ $toDate = output_pref({ dt => eval { dt_from_string(scalar $input->param("to")) } || dt_from_string,
dateformat => 'sql', dateonly => 1 }); #for sql query
- my $whereTType = '';
+ my $whereTType = q{};
+ my @extra_params; # if we add conditions to the select we need extra params
if ($transaction_type eq 'ALL') { #All Transactons
- $whereTType = '';
+ $whereTType = q{};
} elsif ($transaction_type eq 'ACT') { #Active
- $whereTType = " accounttype NOT IN ('F', 'FU', 'FOR', 'M', 'L') AND ";
- } else { #Single transac type
- if ($transaction_type eq 'FORW') {
- $whereTType = " accounttype = 'FOR' OR accounttype = 'W' AND ";
+ $whereTType = q{ AND credit_type_code IN ('PAYMENT','CREDIT') };
+ } elsif ($transaction_type eq 'FORW') {
+ $whereTType = q{ AND credit_type_code IN ('FORGIVEN','WRITEOFF') };
+ } else {
+ if ( any { $transaction_type eq $_->code } @debit_types ) {
+ $whereTType = q{ AND debit_type_code = ? };
+ push @extra_params, $transaction_type;
} else {
- $whereTType = " accounttype = '$transaction_type' AND ";
+ $whereTType = q{ AND credit_type_code = ? };
+ push @extra_params, $transaction_type;
}
}
- my $whereBranchCode = '';
+ my $whereBranchCode = q{};
if ($manager_branchcode ne 'ALL') {
- $whereBranchCode = "AND m.branchcode = '$manager_branchcode'";
+ $whereBranchCode = q{ AND m.branchcode = ?};
+ push @extra_params, $manager_branchcode;
}
SELECT round(amount,2) AS amount, description,
bo.surname AS bsurname, bo.firstname AS bfirstname, m.surname AS msurname, m.firstname AS mfirstname,
bo.cardnumber, br.branchname, bo.borrowernumber,
- al.borrowernumber, DATE(al.date) as date, al.accounttype, al.amountoutstanding, al.note,
+ al.borrowernumber, DATE(al.date) as date, al.credit_type_code, al.debit_type_code, al.amountoutstanding, al.note,
bi.title, bi.biblionumber, i.barcode, i.itype
FROM accountlines al
LEFT JOIN borrowers bo ON (al.borrowernumber = bo.borrowernumber)
LEFT JOIN branches br ON (br.branchcode = m.branchcode )
LEFT JOIN items i ON (i.itemnumber = al.itemnumber)
LEFT JOIN biblio bi ON (bi.biblionumber = i.biblionumber)
- WHERE $whereTType
- CAST(al.date AS DATE) BETWEEN ? AND ?
+ WHERE CAST(al.date AS DATE) BETWEEN ? AND ?
+ $whereTType
$whereBranchCode
ORDER BY al.date
";
- my $sth_stats = $dbh->prepare($query) or die "Unable to prepare query" . $dbh->errstr;
- $sth_stats->execute($fromDate, $toDate) or die "Unable to execute query " . $sth_stats->errstr;
+ my $sth_stats = $dbh->prepare($query) or die "Unable to prepare query " . $dbh->errstr;
+ $sth_stats->execute($fromDate, $toDate, @extra_params) or die "Unable to execute query " . $sth_stats->errstr;
my @loopresult;
my $grantotal = 0;
$row->{date} = dt_from_string($row->{date}, 'sql');
push (@loopresult, $row);
- if($transaction_type eq 'ACT' && ($row->{accounttype} !~ /^C$|^CR$|^LR$|^Pay$/)){
+ if($transaction_type eq 'ACT' && ($row->{credit_type_code} !~ /^CREDIT$|^PAYMENT$/)){
pop @loopresult;
next;
}
- if($row->{accounttype} =~ /^C$|^CR$|^LR$/){
+ if($row->{credit_type_code} =~ /^CREDIT$/){
$grantotal -= abs($row->{amount});
$row->{amount} = '-' . $row->{amount};
- }elsif($row->{accounttype} eq 'FORW' || $row->{accounttype} eq 'W'){
+ }elsif($row->{credit_type_code} eq 'FORGIVEN' || $row->{credit_type_code} eq 'WRITEOFF'){
}else{
$grantotal += abs($row->{amount});
}
total => $grantotal,
);
} else{
- binmode STDOUT, ':encoding(UTF-8)';
- my $q_errors;
my $format = 'csv';
my $reportname = $input->param('basename');
my $reportfilename = $reportname ? "$reportname.$format" : "reportresults.$format" ;
- #my $reportfilename = "$reportname.html" ;
my $delimiter = C4::Context->preference('delimiter') || ',';
- my ( $type, $content );
- if ( $format eq 'csv' ) {
- my $type = 'application/csv';
- my $csv = Text::CSV::Encoded->new({ encoding_out => 'UTF-8', sep_char => $delimiter});
- $csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag();
- my @headers = ();
- push @headers, "mfirstname",
- "cardnumber",
- "bfirstname",
- "branchname",
- "date",
- "accounttype",
- "amount",
- "title",
- "barcode",
- "itype";
- if ($csv->combine(@headers)) {
- $content .= Encode::decode('UTF-8', $csv->string()) . "\n";
- } else {
- push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ;
- }
+ my @rows;
foreach my $row (@loopresult) {
- my @rowValues = ();
- push @rowValues, $row->{mfirstname},
+ my @rowValues;
+ push @rowValues, $row->{mfirstname}. ' ' . $row->{msurname},
$row->{cardnumber},
- $row->{bfirstname},
+ $row->{bfirstname} . ' ' . $row->{bsurname},
$row->{branchname},
$row->{date},
- $row->{accounttype},
+ $row->{credit_type},
+ $row->{debit_type},
+ $row->{note},
$row->{amount},
$row->{title},
- $row->{barcode};
+ $row->{barcode},
$row->{itype};
- if ($csv->combine(@rowValues)) {
- $content .= Encode::decode('UTF-8',$csv->string()) . "\n";
- } else {
- push @$q_errors, { combine => $csv->error_diag() } ;
+ push (@rows, \@rowValues) ;
}
- }
- }
+ my @total;
+ for (1..6){push(@total,"")};
+ push(@total, $grantotal);
print $input->header(
- -type => $type,
- -attachment=> $reportfilename
- );
- print $content;
-
- print $delimiter x 6;
- print $grantotal."\n";
- foreach my $err (@$q_errors) {
- print "# ERROR: " . (map {$_ . ": " . $err->{$_}} keys %$err) . "\n";
- } # here we print all the non-fatal errors at the end. Not super smooth, but better than nothing.
- exit(1);
+ -type => 'text/csv',
+ -encoding => 'utf-8',
+ -attachment => $reportfilename,
+ -name => $reportfilename
+ );
+ my $csvTemplate = C4::Templates::gettemplate('reports/csv/cash_register_stats.tt', 'intranet', $input);
+ $csvTemplate->param(sep => $delimiter, rows => \@rows, total => \@total );
+ print $csvTemplate->output;
+ exit;
}
}
beginDate => $fromDate,
endDate => $toDate,
transaction_type => $transaction_type,
- branchloop => C4::Branch::GetBranchesLoop($manager_branchcode),
- manualinv_types => $manualinv_types,
+ branchloop => Koha::Libraries->search({}, { order_by => ['branchname'] })->unblessed,
+ debit_types => \@debit_types,
+ credit_types => \@credit_types,
CGIsepChoice => GetDelimiterChoices,
);
projects / srvgit / blobdiff