--- /dev/null
+[% INCLUDE 'doc-head-open.inc' %]
+<title>Koha › Reports [% IF ( do_it ) %]› Cash Register Statistics › Results[% ELSE %]› Cash Register Statistics[% END %]</title>
+[% INCLUDE 'doc-head-close.inc' %]
+<link rel="stylesheet" type="text/css" href="[% themelang %]/css/datatables.css" />
+[% INCLUDE 'datatables.inc' %]
+[% INCLUDE 'datatables-strings.inc' %]
+<script type="text/javascript" src="[% themelang %]/js/datatables.js"></script>
+<script type="text/javascript" id="js">$(document).ready(function() {
+ $(document).ready(function() {
+ $("#tbl_cash_register_stats").dataTable($.extend(true, {}, dataTablesDefaults, {
+ "iDisplayLength": 50
+ }));
+ });
+}); </script>
+[% INCLUDE 'calendar.inc' %]
+<script type="text/javascript">
+//<![CDATA[
+actTotal = "";
+
+function isNull(f,noalert) {
+ if (f.value.length > 0) {
+ return false;
+ }
+ return true;
+}
+
+$(document).ready(function() {
+
+ // http://jqueryui.com/demos/datepicker/#date-range
+ var dates = $( "#filter_date_begin, #filter_date_end" ).datepicker({
+ changeMonth: true,
+ numberOfMonths: 1,
+ onSelect: function( selectedDate ) {
+ var option = this.id == "filter_date_begin" ? "minDate" : "maxDate",
+ instance = $( this ).data( "datepicker" );
+ date = $.datepicker.parseDate(
+ instance.settings.dateFormat ||
+ $.datepicker._defaults.dateFormat,
+ selectedDate, instance.settings );
+ dates.not( this ).datepicker( "option", option, date );
+ }
+ });
+
+ $('#frmCashRegister').submit(function() {
+ var isFormValid = true;
+ var alertString= _("Form not submitted because of the following problem(s)")+"\n";
+
+ alertString +="-------------------------------------------------------------------\n\n";
+
+ if ( !$('#filter_date_begin').val() || !$('#filter_date_end').val()){
+ isFormValid = false;
+ alertString += "\n- " + _("Dates cannot be empty");
+ }
+
+ if (!isFormValid) {
+ alert(alertString);
+ return false;
+ }
+
+ $(this).submit();
+ });
+});
+
+//]]>
+</script>
+
+</head>
+<body>
+[% INCLUDE 'header.inc' %]
+[% INCLUDE 'cat-search.inc' %]
+
+<div id="breadcrumbs">
+ <a href="/cgi-bin/koha/mainpage.pl">Home</a> › <a href="/cgi-bin/koha/reports/reports-home.pl">Reports</a>
+ ›
+ [% IF ( do_it ) %]
+ <a href="/cgi-bin/koha/reports/cash_register_stats.pl">Cash Register Statistics</a> › Results
+ [% ELSE %]
+ Cash Register Statistics
+ [% END %]
+</div>
+
+<div id="doc3" class="yui-t2">
+
+ <div id="bd">
+ <div id="yui-main">
+ <div class="yui-b">
+
+ <h1>Cash register statistics</h1>
+ <form method="post" action="/cgi-bin/koha/reports/cash_register_stats.pl" id="frmCashRegister">
+ <fieldset class="rows">
+ <legend>Cash Register statistics [% beginDate %] to [% endDate %]</legend>
+ <table>
+ <thead>
+ <tr>
+ <th>Interval</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>
+ <label for="filter_date_begin">From</label>
+ <input type="text" size="10" id="filter_date_begin" name="filter_date_begin" value="[% beginDate %]" />
+ <label for="filter_date_end">To</label>
+ <input type="text" size="10" id="filter_date_end" name="filter_date_end" value="[% endDate %]" />
+ </td>
+ </tr>
+ </tbody>
+ </table><br />
+ </fieldset>
+
+ <fieldset class="rows">
+ <ol>
+ <li>
+ <label for="">Transaction type:</label>
+ <select name="transaction_type" id="transaction_type">
+ [% IF transaction_type == "ALL" %]
+ <option value="ALL" selected="selected">All transactions</option>
+ [% ELSE %]
+ <option value="ALL">All transactions</option>
+ [% END %]
+
+ [% IF transaction_type == "ACT" %]
+ <option value="ACT" selected="selected">Active transactions</option>
+ [% ELSE %]
+ <option value="ACT">Active transactions</option>
+ [% END %]
+
+ [% IF transaction_type == "C" %]
+ <option value="C" selected="selected">Credit</option>
+ [% ELSE %]
+ <option value="C">Credit</option>
+ [% END %]
+
+ [% IF transaction_type == "FORW" %]
+ <option value="FORW" selected="selected">Write off</option>
+ [% ELSE %]
+ <option value="FORW">Write off</option>
+ [% END %]
+
+ [% IF transaction_type == "F" %]
+ <option value="F" selected="selected">Fine</option>
+ [% ELSE %]
+ <option value="F">Fine</option>
+ [% END %]
+
+ [% IF transaction_type == "FU" %]
+ <option value="FU" selected="selected">Fine - long period</option>
+ [% ELSE %]
+ <option value="FU">Fine - long period</option>
+ [% END %]
+
+ [% IF transaction_type == "PAY" %]
+ <option value="PAY" selected="selected">Payment</option>
+ [% ELSE %]
+ <option value="PAY">Payment</option>
+ [% END %]
+
+ [% IF transaction_type == "A" %]
+ <option value="A" selected="selected">Account Management Fee</option>
+ [% ELSE %]
+ <option value="A">Account Management Fee</option>
+ [% END %]
+
+ [% IF transaction_type == "M" %]
+ <option value="M" selected="selected">Sundry</option>
+ [% ELSE %]
+ <option value="M">Sundry</option>
+ [% END %]
+
+ [% IF transaction_type == "L" %]
+ <option value="L" selected="selected">Lost item</option>
+ [% ELSE %]
+ <option value="L">Lost item</option>
+ [% END %]
+
+ [% IF transaction_type == "N" %]
+ <option value="N" selected="selected">New card</option>
+ [% ELSE %]
+ <option value="N">New card</option>
+ [% END %]
+
+ [% FOREACH manualinv IN manualinv_types %]
+ [% value_manualinv = manualinv.authorised_value|truncate(5, '') %]
+ [% IF transaction_type == value_manualinv %]
+ <option value="[% value_manualinv %]" selected="selected">[% manualinv.authorised_value %]</option>
+ [% ELSE %]
+ <option value="[% value_manualinv %]">[% manualinv.authorised_value %]</option>
+ [% END %]
+ [% END %]
+ </select>
+ </li>
+ <li>
+ <label>Library</label>
+ <select name="branch" id="branch">
+ <option value="ALL">All</option>
+ [% FOREACH branchloo IN branchloop %]
+ [% IF ( branchloo.selected ) %]
+ <option value="[% branchloo.value %]" selected="selected">[% branchloo.branchname %]</option>
+ [% ELSE %]
+ <option value="[% branchloo.value %]">[% branchloo.branchname %]</option>
+ [% END %]
+ [% END %]
+ </select>
+ </td>
+ </tr>
+ </li>
+ </ol>
+ </fieldset>
+
+ <fieldset class="rows">
+ <legend>Output</legend>
+ <ol>
+ <li>
+ <label for="outputscreen">To screen into the browser: </label>
+ <input type="radio" checked="checked" name="output" id="outputscreen" value="screen" />
+ </li>
+ <li>
+ <label for="outputfile">To a file:</label>
+ <input type="radio" name="output" value="file" id="outputfile" />
+ <label class="inline" for="basename">Named: </label><input type="text" name="basename" id="basename" value="Export" />
+ </li>
+ </ol>
+ </fieldset>
+
+ <fieldset class="action">
+ <input type="submit" value="Submit" name="do_it" />
+ </fieldset>
+ </form>
+
+ [% IF ( do_it ) %]
+ <div> </div>
+ <table id="tbl_cash_register_stats">
+ <thead>
+ <tr>
+ <th>Manager name</th>
+ <th>Borrower cardnumber</th>
+ <th>Borrower name</th>
+ <th>Branch</th>
+ <th>Transaction date</th>
+ <th>Transaction type</th>
+ <th>Amount</th>
+ <th>Biblio title</th>
+ <th>Barcode</th>
+ <th>Document type</th>
+ </tr>
+ </thead>
+ [% FOREACH loopresul IN loopresult %]
+ <tr>
+ <td>[% loopresul.mfirstname %] [% loopresul.msurname %]</td>
+ <td><a href="/cgi-bin/koha/members/moremember.pl?borrowernumber=[% loopresul.borrowernumber %]">[% loopresul.cardnumber %]</a></td>
+ <td>[% loopresul.bfirstname %] [% loopresul.bsurname %]</td>
+ <td>[% loopresul.branchname %]</td>
+ <td>[% loopresul.date %]</td>
+ <td>
+ [% IF loopresul.accounttype == "ACT" %]
+ <span>Active transactions</span>
+ [% ELSIF loopresul.accounttype == "C" || loopresul.accounttype == "CR" %]
+ <span>Credit</span>
+ [% ELSIF loopresul.accounttype == "FORW" || loopresul.accounttype == "W" %]
+ <span>Write off</span>
+ [% ELSIF loopresul.accounttype == "F" %]
+ <span>Fine</span>
+ [% ELSIF loopresul.accounttype == "FU" %]
+ <span>Fine - long period</span>
+ [% ELSIF loopresul.accounttype == "Pay" %]
+ <span>Payment</span>
+ [% ELSIF loopresul.accounttype == "A" %]
+ <span>Account management fee</span>
+ [% ELSIF loopresul.accounttype == "M" %]
+ <span>Sundry</span>
+ [% ELSIF loopresul.accounttype == "L" || loopresul.accounttype == "LR" %]
+ <span>Lost item</span>
+ [% ELSIF loopresul.accounttype == "N" %]
+ <span>New card</span>
+ [% ELSE %]
+ [% FOREACH manualinv IN manualinv_types %]
+ [% value_manualinv = manualinv.authorised_value|truncate(5, '') %]
+ [% IF loopresul.accounttype == value_manualinv %]
+ <span>[% manualinv.authorised_value %]</span>
+ [% LAST %]
+ [% END %]
+ [% END %]
+ [% END %]
+ </td>
+ <td style="text-align:right;">[% loopresul.amount %]</td>
+ <td><a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=[% loopresul.biblionumber %]">[% loopresul.title %]</a></td>
+ <td>[% loopresul.barcode %]</td>
+ <td>[% loopresul.itype %]</td>
+ </tr>
+ [% END %]
+ <tfoot>
+ <tr>
+ <th colspan="6" style="text-align:right;">TOTAL</th>
+ <th style="text-align:right;">[% total %]</th>
+ <th colspan="3"> </th>
+ </tr>
+ </tfoot>
+ </table>
+
+ [% END %] [%# do_it %]
+</div>
+</div>
+<div class="yui-b">
+[% INCLUDE 'reports-menu.inc' %]
+</div>
+</div>
+[% INCLUDE 'intranet-bottom.inc' %]
--- /dev/null
+#!/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;
projects / koha.git / commitdiff