From b9fb6c55c5bf3334c2121448d12f3d7f6d212493 Mon Sep 17 00:00:00 2001 From: simith Date: Wed, 27 Aug 2014 14:36:13 -0400 Subject: [PATCH] Bug 6934 New report Cash Register Statistics MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch creates a new report that outputs cash register stats for a specific period. Changes: koha-tmpl/intranet-tmpl/prog/en/includes/reports-menu.inc - Added cash register report link koha-tmpl/intranet-tmpl/prog/en/modules/reports/cash-register_stats.tt - new file koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt - Added cash register report link reports/cash-register_stats.pl - new file Testing: I Apply the patch 0) Go to reports home page (Home › Reports); 1) Validate link << Cash Register >> under Statistics wizards; 2) Click link; 3) Validate page with three fieldsets; 4) Chose a date interval, a transaction type, a library and an output; 5) Validade the result. Signed-off-by: Nicole --- .../prog/en/includes/reports-menu.inc | 1 + .../prog/en/modules/reports/cash_register_stats.tt | 307 ++++++++++++++++++++ .../prog/en/modules/reports/reports-home.tt | 1 + reports/cash_register_stats.pl | 189 ++++++++++++ 4 files changed, 498 insertions(+) create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/reports/cash_register_stats.tt create mode 100755 reports/cash_register_stats.pl diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/reports-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/reports-menu.inc index 8c4fe9f..41dd0d2 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/reports-menu.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/reports-menu.inc @@ -10,6 +10,7 @@
  • Catalog
  • Circulation
  • Serials
  • +
  • Cash Register
  • Holds
  • diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cash_register_stats.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cash_register_stats.tt new file mode 100644 index 0000000..4a5c10e --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cash_register_stats.tt @@ -0,0 +1,307 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Reports [% IF ( do_it ) %]› Cash Register Statistics › Results[% ELSE %]› Cash Register Statistics[% END %] +[% INCLUDE 'doc-head-close.inc' %] + +[% INCLUDE 'datatables.inc' %] +[% INCLUDE 'datatables-strings.inc' %] + + +[% INCLUDE 'calendar.inc' %] + + + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'cat-search.inc' %] + + + +
    + +
    +
    +
    + +

    Cash register statistics

    +
    +
    + Cash Register statistics [% beginDate %] to [% endDate %] + + + + + + + + + + + +
    Interval
    + + + + +

    +
    + +
    +
      +
    1. + + +
    2. +
    3. + + + + +
    4. +
    +
    + +
    + Output +
      +
    1. + + +
    2. +
    3. + + + +
    4. +
    +
    + +
    + +
    +
    + + [% IF ( do_it ) %] +
     
    + + + + + + + + + + + + + + + + [% FOREACH loopresul IN loopresult %] + + + + + + + + + + + + + [% END %] + + + + + + + +
    Manager nameBorrower cardnumberBorrower nameBranchTransaction dateTransaction typeAmountBiblio titleBarcodeDocument type
    [% loopresul.mfirstname %] [% loopresul.msurname %][% loopresul.cardnumber %][% loopresul.bfirstname %] [% loopresul.bsurname %][% loopresul.branchname %][% loopresul.date %] + [% IF loopresul.accounttype == "ACT" %] + Active transactions + [% ELSIF loopresul.accounttype == "C" || loopresul.accounttype == "CR" %] + Credit + [% ELSIF loopresul.accounttype == "FORW" || loopresul.accounttype == "W" %] + Write off + [% ELSIF loopresul.accounttype == "F" %] + Fine + [% ELSIF loopresul.accounttype == "FU" %] + Fine - long period + [% ELSIF loopresul.accounttype == "Pay" %] + Payment + [% ELSIF loopresul.accounttype == "A" %] + Account management fee + [% ELSIF loopresul.accounttype == "M" %] + Sundry + [% ELSIF loopresul.accounttype == "L" || loopresul.accounttype == "LR" %] + Lost item + [% ELSIF loopresul.accounttype == "N" %] + New card + [% ELSE %] + [% FOREACH manualinv IN manualinv_types %] + [% value_manualinv = manualinv.authorised_value|truncate(5, '') %] + [% IF loopresul.accounttype == value_manualinv %] + [% manualinv.authorised_value %] + [% LAST %] + [% END %] + [% END %] + [% END %] + [% loopresul.amount %][% loopresul.title %][% loopresul.barcode %][% loopresul.itype %]
    TOTAL[% total %] 
    + + [% END %] [%# do_it %] +
    +
    +
    +[% INCLUDE 'reports-menu.inc' %] +
    +
    +[% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt index fde9ad3..bc34479 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt @@ -34,6 +34,7 @@
  • Catalog
  • Circulation
  • Serials
  • +
  • Cash Register
  • Holds
  • diff --git a/reports/cash_register_stats.pl b/reports/cash_register_stats.pl new file mode 100755 index 0000000..883b008 --- /dev/null +++ b/reports/cash_register_stats.pl @@ -0,0 +1,189 @@ +#!/usr/bin/perl +# +# 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 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. + +use strict; +use warnings; +use C4::Auth; +use CGI; +use C4::Context; +use C4::Reports; +use C4::Output; +use C4::Koha; +use C4::Circulation; +use C4::Dates qw/format_date format_date_in_iso/; +use C4::Budgets qw/GetCurrency GetCurrencies/; +#use Data::Dumper; +#use Smart::Comments; + +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, + query => $input, + type => "intranet", + authnotrequired => 0, + flagsrequired => {reports => '*'}, + debug => 1, +}); + +my $do_it = $input->param('do_it'); +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 = ","; + +$template->param( + do_it => $do_it, + DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), +); + +#Initialize date pickers to today +my $today = C4::Dates->today('iso'); +my $fromDate = $today; +my $toDate = $today; + +### 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 = format_date_in_iso($input->param("filter_date_begin")); + $toDate = format_date_in_iso($input->param("filter_date_end")); + + 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 "; + } else { #Single transac type + if ($transaction_type eq 'FORW') { + $whereTType = " accounttype = 'FOR' OR accounttype = 'W' AND "; + } else { + $whereTType = " accounttype = '$transaction_type' AND "; + } + } + + my $whereBranchCode = ''; + if ($branchcode ne 'ALL') { + $whereBranchCode = "AND bo.branchcode = '$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, + bi.title, bi.biblionumber, i.barcode, i.itype + FROM accountlines al + LEFT JOIN borrowers bo ON (al.borrowernumber = bo.borrowernumber) + LEFT JOIN borrowers m ON (al.manager_id = m.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 ? + $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 @loopresult; + my $grantotal = 0; + while ( my $row = $sth_stats->fetchrow_hashref()) { + $row->{amountoutstanding} = 0 if (!$row->{amountoutstanding}); + #if ((abs($row->{amount}) - $row->{amountoutstanding}) > 0) { + $row->{amount} = sprintf("%.2f", abs ($row->{amount})); + $row->{date} = format_date($row->{date}); + ### date : $row->{date} + + push (@loopresult, $row); + $grantotal += abs($row->{amount}); + #} + } + + my @currency = GetCurrency(); + $grantotal = sprintf("%.2f", $grantotal); + + if($output eq 'screen'){ + $template->param( + loopresult => \@loopresult, + total => $grantotal, + ); + } else{ + binmode STDOUT, ':encoding(UTF-8)'; + 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"; + exit(1); + } + +} + +### fromdate final: $fromDate +### toDate final: $toDate +$template->param( + beginDate => format_date($fromDate), + endDate => format_date($toDate), + transaction_type => $transaction_type, + branchloop => C4::Branch::GetBranchesLoop($branchcode), + manualinv_types => $manualinv_types, +); +output_html_with_http_headers $input, $cookie, $template->output; + +1; -- 1.7.9.5