use C4::Circulation;
use DateTime;
use Koha::DateUtils;
-use C4::Budgets qw/GetCurrency GetCurrencies/;
-#use Data::Dumper;
-#use Smart::Comments;
+use Text::CSV::Encoded;
my $input = new CGI;
my $dbh = C4::Context->dbh;
-my $fullreportname = "reports/cash_register_stats.tt";
my ($template, $borrowernumber, $cookie) = get_template_and_user({
- template_name => $fullreportname,
+ template_name => "reports/cash_register_stats.tt",
query => $input,
type => "intranet",
authnotrequired => 0,
my $output = $input->param("output");
my $basename = $input->param("basename");
my $transaction_type = $input->param("transaction_type") || 'ACT';
-my $branchcode = $input->param("branch") || C4::Context->userenv->{'branch'};
-our $sep = $input->param("sep") // ',';
-$sep = "\t" if ($sep eq 'tabulation');
+my $manager_branchcode = $input->param("branch") || C4::Context->userenv->{'branch'};
$template->param(
do_it => $do_it,
#Initialize date pickers to today
my $fromDate = dt_from_string;
my $toDate = dt_from_string;
-### fromdate today: $fromDate
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({});
-### $manualinv_types
if ($do_it) {
- $fromDate = output_pref({ dt => eval { dt_from_string($input->param("filter_date_begin")) } || dt_from_string,
+ $fromDate = output_pref({ dt => eval { dt_from_string($input->param("from")) } || dt_from_string,
dateformat => 'sql', dateonly => 1 }); #for sql query
- $toDate = output_pref({ dt => eval { dt_from_string($input->param("filter_date_end")) } || dt_from_string,
+ $toDate = output_pref({ dt => eval { dt_from_string($input->param("to")) } || dt_from_string,
dateformat => 'sql', dateonly => 1 }); #for sql query
my $whereTType = '';
if ($transaction_type eq 'ALL') { #All Transactons
$whereTType = '';
} elsif ($transaction_type eq 'ACT') { #Active
- $whereTType = " accounttype NOT IN ('F', 'FU', 'FOR', 'M', 'L') AND ";
+ $whereTType = " accounttype IN ('Pay','C') AND ";
} else { #Single transac type
if ($transaction_type eq 'FORW') {
$whereTType = " accounttype = 'FOR' OR accounttype = 'W' AND ";
}
my $whereBranchCode = '';
- if ($branchcode ne 'ALL') {
- $whereBranchCode = "AND bo.branchcode = '$branchcode'";
+ if ($manager_branchcode ne 'ALL') {
+ $whereBranchCode = "AND m.branchcode = '$manager_branchcode'";
}
- ### $transaction_type;
my $query = "
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.borrowernumber, DATE(al.date) as date, al.accounttype, al.amountoutstanding, al.note,
bi.title, bi.biblionumber, i.barcode, i.itype
FROM accountlines al
LEFT JOIN borrowers bo ON (al.borrowernumber = bo.borrowernumber)
#if ((abs($row->{amount}) - $row->{amountoutstanding}) > 0) {
$row->{amount} = sprintf("%.2f", abs ($row->{amount}));
$row->{date} = dt_from_string($row->{date}, 'sql');
- ### date : $row->{date}
push (@loopresult, $row);
- $grantotal += abs($row->{amount});
+ if($transaction_type eq 'ACT' && ($row->{accounttype} !~ /^C$|^CR$|^LR$|^Pay$/)){
+ pop @loopresult;
+ next;
+ }
+ if($row->{accounttype} =~ /^C$|^CR$|^LR$/){
+ $grantotal -= abs($row->{amount});
+ $row->{amount} = '-' . $row->{amount};
+ }elsif($row->{accounttype} eq 'FORW' || $row->{accounttype} eq 'W'){
+ }else{
+ $grantotal += abs($row->{amount});
+ }
#}
}
- my @currency = GetCurrency();
$grantotal = sprintf("%.2f", $grantotal);
if($output eq 'screen'){
total => $grantotal,
);
} else{
- binmode STDOUT, ':encoding(UTF-8)';
+ 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 @rows;
+ foreach my $row (@loopresult) {
+ my @rowValues;
+ push @rowValues, $row->{mfirstname},
+ $row->{cardnumber},
+ $row->{bfirstname},
+ $row->{branchname},
+ $row->{date},
+ $row->{accounttype},
+ $row->{amount},
+ $row->{title},
+ $row->{barcode},
+ $row->{itype};
+ push (@rows, \@rowValues) ;
+ }
+ my @total;
+ for (1..6){push(@total,"")};
+ push(@total, $grantotal);
print $input->header(
- -type => 'application/vnd.sun.xml.calc',
- -encoding => 'utf-8',
- -name => "$basename.csv",
- -attachment => "$basename.csv"
- );
-
- print "Manager name".$sep;
- print "Borrower cardnumber".$sep;
- print "Borrower name".$sep;
- print "Branch".$sep;
- print "Transaction date".$sep;
- print "Transaction type".$sep;
- print "Amount".$sep;
- print "Biblio title".$sep;
- print "Barcode".$sep;
- print "Document type"."\n";
-
- foreach my $item (@loopresult){
- print $item->{mfirstname}. ' ' . $item->{msurname} . $sep;
- print $item->{cardnumber}.$sep;
- print $item->{bfirstname}. ' ' . $item->{bsurname} . $sep;
- print $item->{branchname}.$sep;
- print $item->{date}.$sep;
- print $item->{accounttype}.$sep;
- print $item->{amount}.$sep;
- print $item->{title}.$sep;
- print $item->{barcode}.$sep;
- print $item->{itype}."\n";
- }
-
- print $sep x 6;
- print $grantotal."\n";
+ -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(1);
}
}
-### fromdate final: $fromDate
-### toDate final: $toDate
$template->param(
- beginDate => dt_from_string($fromDate),
- endDate => dt_from_string($toDate),
+ beginDate => $fromDate,
+ endDate => $toDate,
transaction_type => $transaction_type,
- branchloop => C4::Branch::GetBranchesLoop($branchcode),
+ branchloop => Koha::Libraries->search({}, { order_by => ['branchname'] })->unblessed,
manualinv_types => $manualinv_types,
CGIsepChoice => GetDelimiterChoices,
);