Attachment #31210 for bug #6934

View | Details | Raw Unified | Return to bug 6934
Collapse All | Expand All

Lines 10-15 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/includes/reports-menu.inc (+1 lines)
10	<li><a href="/cgi-bin/koha/reports/catalogue_stats.pl">Catalog</a></li>	10	<li><a href="/cgi-bin/koha/reports/catalogue_stats.pl">Catalog</a></li>
11	<li><a href="/cgi-bin/koha/reports/issues_stats.pl">Circulation</a></li>	11	<li><a href="/cgi-bin/koha/reports/issues_stats.pl">Circulation</a></li>
12	<li><a href="/cgi-bin/koha/reports/serials_stats.pl">Serials</a></li>	12	<li><a href="/cgi-bin/koha/reports/serials_stats.pl">Serials</a></li>
		13	<li><a href="/cgi-bin/koha/reports/cash_register_stats.pl">Cash Register</a></li>
13	<li><a href="/cgi-bin/koha/reports/reserves_stats.pl">Holds</a></li>	14	<li><a href="/cgi-bin/koha/reports/reserves_stats.pl">Holds</a></li>
14	</ul>	15	</ul>
15		16




[% INCLUDE 'doc-head-open.inc' %]
<title>Koha &rsaquo; Reports [% IF ( do_it ) %]&rsaquo; Cash Register Statistics &rsaquo; Results[% ELSE %]&rsaquo; 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> &rsaquo; <a href="/cgi-bin/koha/reports/reports-home.pl">Reports</a>
    &rsaquo;
    [% IF ( do_it ) %]
        <a href="/cgi-bin/koha/reports/cash_register_stats.pl">Cash Register Statistics</a> &rsaquo; 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>&nbsp;</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">&nbsp;</th>
        </tr>
        </tfoot>
    </table>

    [% END %] [%# do_it %]
</div>
</div>
<div class="yui-b">
[% INCLUDE 'reports-menu.inc' %]
</div>
</div>
[% INCLUDE 'intranet-bottom.inc' %]

Lines 34-39 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt (+1 lines)
34	<li><a href="/cgi-bin/koha/reports/catalogue_stats.pl">Catalog</a></li>	34	<li><a href="/cgi-bin/koha/reports/catalogue_stats.pl">Catalog</a></li>
35	<li><a href="/cgi-bin/koha/reports/issues_stats.pl">Circulation</a></li>	35	<li><a href="/cgi-bin/koha/reports/issues_stats.pl">Circulation</a></li>
36	<li><a href="/cgi-bin/koha/reports/serials_stats.pl">Serials</a></li>	36	<li><a href="/cgi-bin/koha/reports/serials_stats.pl">Serials</a></li>
		37	<li><a href="/cgi-bin/koha/reports/cash_register_stats.pl">Cash Register</a></li>
37	<li><a href="/cgi-bin/koha/reports/reserves_stats.pl">Holds</a></li>	38	<li><a href="/cgi-bin/koha/reports/reserves_stats.pl">Holds</a></li>
38	</ul>	39	</ul>
39		40




#!/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;

Return to bug 6934

Line 0 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cash_register_stats.tt (+307 lines)
		1	[% INCLUDE 'doc-head-open.inc' %]
		2	<title>Koha &rsaquo; Reports [% IF ( do_it ) %]&rsaquo; Cash Register Statistics &rsaquo; Results[% ELSE %]&rsaquo; Cash Register Statistics[% END %]</title>
		3	[% INCLUDE 'doc-head-close.inc' %]
		4	<link rel="stylesheet" type="text/css" href="[% themelang %]/css/datatables.css" />
		5	[% INCLUDE 'datatables.inc' %]
		6	[% INCLUDE 'datatables-strings.inc' %]
		7	<script type="text/javascript" src="[% themelang %]/js/datatables.js"></script>
		8	<script type="text/javascript" id="js">$(document).ready(function() {
		9	$(document).ready(function() {
		10	$("#tbl_cash_register_stats").dataTable($.extend(true, {}, dataTablesDefaults, {
		11	"iDisplayLength": 50
		12	}));
		13	});
		14	}); </script>
		15	[% INCLUDE 'calendar.inc' %]
		16	<script type="text/javascript">
		17	//<![CDATA[
		18	actTotal = "";
		19
		20	function isNull(f,noalert) {
		21	if (f.value.length > 0) {
		22	return false;
		23	}
		24	return true;
		25	}
		26
		27	$(document).ready(function() {
		28
		29	// http://jqueryui.com/demos/datepicker/#date-range
		30	var dates = $( "#filter_date_begin, #filter_date_end" ).datepicker({
		31	changeMonth: true,
		32	numberOfMonths: 1,
		33	onSelect: function( selectedDate ) {
		34	var option = this.id == "filter_date_begin" ? "minDate" : "maxDate",
		35	instance = $( this ).data( "datepicker" );
		36	date = $.datepicker.parseDate(
		37	instance.settings.dateFormat \|\|
		38	$.datepicker._defaults.dateFormat,
		39	selectedDate, instance.settings );
		40	dates.not( this ).datepicker( "option", option, date );
		41	}
		42	});
		43
		44	$('#frmCashRegister').submit(function() {
		45	var isFormValid = true;
		46	var alertString= _("Form not submitted because of the following problem(s)")+"\n";
		47
		48	alertString +="-------------------------------------------------------------------\n\n";
		49
		50	if ( !$('#filter_date_begin').val() \|\| !$('#filter_date_end').val()){
		51	isFormValid = false;
		52	alertString += "\n- " + _("Dates cannot be empty");
		53	}
		54
		55	if (!isFormValid) {
		56	alert(alertString);
		57	return false;
		58	}
		59
		60	$(this).submit();
		61	});
		62	});
		63
		64	//]]>
65	</script>
66
67	</head>
68	<body>
69	[% INCLUDE 'header.inc' %]
70	[% INCLUDE 'cat-search.inc' %]
71
72	<div id="breadcrumbs">
73	<a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; <a href="/cgi-bin/koha/reports/reports-home.pl">Reports</a>
74	&rsaquo;
75	[% IF ( do_it ) %]
76	<a href="/cgi-bin/koha/reports/cash_register_stats.pl">Cash Register Statistics</a> &rsaquo; Results
77	[% ELSE %]
78	Cash Register Statistics
79	[% END %]
80	</div>
81
82	<div id="doc3" class="yui-t2">
83
84	<div id="bd">
85	<div id="yui-main">
86	<div class="yui-b">
87
88	<h1>Cash register statistics</h1>
89	<form method="post" action="/cgi-bin/koha/reports/cash_register_stats.pl" id="frmCashRegister">
90	<fieldset class="rows">
91	<legend>Cash Register statistics [% beginDate %] to [% endDate %]</legend>
92	<table>
93	<thead>
94	<tr>
95	<th>Interval</th>
96	</tr>
97	</thead>
98	<tbody>
99	<tr>
100	<td>
101	<label for="filter_date_begin">From</label>
102	<input type="text" size="10" id="filter_date_begin" name="filter_date_begin" value="[% beginDate %]" />
103	<label for="filter_date_end">To</label>
104	<input type="text" size="10" id="filter_date_end" name="filter_date_end" value="[% endDate %]" />
105	</td>
106	</tr>
107	</tbody>
108	</table><br />
109	</fieldset>
110
111	<fieldset class="rows">
112	<ol>
113	<li>
114	<label for="">Transaction type:</label>
115	<select name="transaction_type" id="transaction_type">
116	[% IF transaction_type == "ALL" %]
117	<option value="ALL" selected="selected">All transactions</option>
118	[% ELSE %]
119	<option value="ALL">All transactions</option>
120	[% END %]
121
122	[% IF transaction_type == "ACT" %]
123	<option value="ACT" selected="selected">Active transactions</option>
124	[% ELSE %]
125	<option value="ACT">Active transactions</option>
126	[% END %]
127
128	[% IF transaction_type == "C" %]
129	<option value="C" selected="selected">Credit</option>
130	[% ELSE %]
131	<option value="C">Credit</option>
132	[% END %]
133
134	[% IF transaction_type == "FORW" %]
135	<option value="FORW" selected="selected">Write off</option>
136	[% ELSE %]
137	<option value="FORW">Write off</option>
138	[% END %]
139
140	[% IF transaction_type == "F" %]
141	<option value="F" selected="selected">Fine</option>
142	[% ELSE %]
143	<option value="F">Fine</option>
144	[% END %]
145
146	[% IF transaction_type == "FU" %]
147	<option value="FU" selected="selected">Fine - long period</option>
148	[% ELSE %]
149	<option value="FU">Fine - long period</option>
150	[% END %]
151
152	[% IF transaction_type == "PAY" %]
153	<option value="PAY" selected="selected">Payment</option>
154	[% ELSE %]
155	<option value="PAY">Payment</option>
156	[% END %]
157
158	[% IF transaction_type == "A" %]
159	<option value="A" selected="selected">Account Management Fee</option>
160	[% ELSE %]
161	<option value="A">Account Management Fee</option>
162	[% END %]
163
164	[% IF transaction_type == "M" %]
165	<option value="M" selected="selected">Sundry</option>
166	[% ELSE %]
167	<option value="M">Sundry</option>
168	[% END %]
169
170	[% IF transaction_type == "L" %]
171	<option value="L" selected="selected">Lost item</option>
172	[% ELSE %]
173	<option value="L">Lost item</option>
174	[% END %]
175
176	[% IF transaction_type == "N" %]
177	<option value="N" selected="selected">New card</option>
178	[% ELSE %]
179	<option value="N">New card</option>
180	[% END %]
181
182	[% FOREACH manualinv IN manualinv_types %]
183	[% value_manualinv = manualinv.authorised_value\|truncate(5, '') %]
184	[% IF transaction_type == value_manualinv %]
185	<option value="[% value_manualinv %]" selected="selected">[% manualinv.authorised_value %]</option>
186	[% ELSE %]
187	<option value="[% value_manualinv %]">[% manualinv.authorised_value %]</option>
188	[% END %]
189	[% END %]
190	</select>
191	</li>
192	<li>
193	<label>Library</label>
194	<select name="branch" id="branch">
195	<option value="ALL">All</option>
196	[% FOREACH branchloo IN branchloop %]
197	[% IF ( branchloo.selected ) %]
198	<option value="[% branchloo.value %]" selected="selected">[% branchloo.branchname %]</option>
199	[% ELSE %]
200	<option value="[% branchloo.value %]">[% branchloo.branchname %]</option>
201	[% END %]
202	[% END %]
203	</select>
204	</td>
205	</tr>
206	</li>
207	</ol>
208	</fieldset>
209
210	<fieldset class="rows">
211	<legend>Output</legend>
212	<ol>
213	<li>
214	<label for="outputscreen">To screen into the browser: </label>
215	<input type="radio" checked="checked" name="output" id="outputscreen" value="screen" />
216	</li>
217	<li>
218	<label for="outputfile">To a file:</label>
219	<input type="radio" name="output" value="file" id="outputfile" />
220	<label class="inline" for="basename">Named: </label><input type="text" name="basename" id="basename" value="Export" />
221	</li>
222	</ol>
223	</fieldset>
224
225	<fieldset class="action">
226	<input type="submit" value="Submit" name="do_it" />
227	</fieldset>
228	</form>
229
230	[% IF ( do_it ) %]
231	<div> </div>
232	<table id="tbl_cash_register_stats">
233	<thead>
234	<tr>
235	<th>Manager name</th>
236	<th>Borrower cardnumber</th>
237	<th>Borrower name</th>
238	<th>Branch</th>
239	<th>Transaction date</th>
240	<th>Transaction type</th>
241	<th>Amount</th>
242	<th>Biblio title</th>
243	<th>Barcode</th>
244	<th>Document type</th>
245	</tr>
246	</thead>
247	[% FOREACH loopresul IN loopresult %]
248	<tr>
249	<td>[% loopresul.mfirstname %] [% loopresul.msurname %]</td>
250	<td><a href="/cgi-bin/koha/members/moremember.pl?borrowernumber=[% loopresul.borrowernumber %]">[% loopresul.cardnumber %]</a></td>
251	<td>[% loopresul.bfirstname %] [% loopresul.bsurname %]</td>
252	<td>[% loopresul.branchname %]</td>
253	<td>[% loopresul.date %]</td>
254	<td>
255	[% IF loopresul.accounttype == "ACT" %]
256	<span>Active transactions</span>
257	[% ELSIF loopresul.accounttype == "C" \|\| loopresul.accounttype == "CR" %]
258	<span>Credit</span>
259	[% ELSIF loopresul.accounttype == "FORW" \|\| loopresul.accounttype == "W" %]
260	<span>Write off</span>
261	[% ELSIF loopresul.accounttype == "F" %]
262	<span>Fine</span>
263	[% ELSIF loopresul.accounttype == "FU" %]
264	<span>Fine - long period</span>
265	[% ELSIF loopresul.accounttype == "Pay" %]
266	<span>Payment</span>
267	[% ELSIF loopresul.accounttype == "A" %]
268	<span>Account management fee</span>
269	[% ELSIF loopresul.accounttype == "M" %]
270	<span>Sundry</span>
271	[% ELSIF loopresul.accounttype == "L" \|\| loopresul.accounttype == "LR" %]
272	<span>Lost item</span>
273	[% ELSIF loopresul.accounttype == "N" %]
274	<span>New card</span>
275	[% ELSE %]
276	[% FOREACH manualinv IN manualinv_types %]
277	[% value_manualinv = manualinv.authorised_value\|truncate(5, '') %]
278	[% IF loopresul.accounttype == value_manualinv %]
279	<span>[% manualinv.authorised_value %]</span>
280	[% LAST %]
281	[% END %]
282	[% END %]
283	[% END %]
284	</td>
285	<td style="text-align:right;">[% loopresul.amount %]</td>
286	<td><a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=[% loopresul.biblionumber %]">[% loopresul.title %]</a></td>
287	<td>[% loopresul.barcode %]</td>
288	<td>[% loopresul.itype %]</td>
289	</tr>
290	[% END %]
291	<tfoot>
292	<tr>
293	<th colspan="6" style="text-align:right;">TOTAL</th>
294	<th style="text-align:right;">[% total %]</th>
295	<th colspan="3"> </th>
296	</tr>
297	</tfoot>
298	</table>
299
300	[% END %] [%# do_it %]
301	</div>
302	</div>
303	<div class="yui-b">
304	[% INCLUDE 'reports-menu.inc' %]
305	</div>
306	</div>
307	[% INCLUDE 'intranet-bottom.inc' %]

Line 0 Link Here

(-)a/reports/cash_register_stats.pl (-1 / +189 lines)
0	-	1	#!/usr/bin/perl
		2	#
		3	# This file is part of Koha.
		4	#
		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
		7	# Foundation;
		8	#
		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.
		12	#
		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.
		16
		17	use strict;
		18	use warnings;
		19	use C4::Auth;
		20	use CGI;
		21	use C4::Context;
		22	use C4::Reports;
		23	use C4::Output;
		24	use C4::Koha;
		25	use C4::Circulation;
		26	use C4::Dates qw/format_date format_date_in_iso/;
		27	use C4::Budgets qw/GetCurrency GetCurrencies/;
		28	#use Data::Dumper;
		29	#use Smart::Comments;
		30
		31	my $input = new CGI;
		32	my $dbh = C4::Context->dbh;
		33	my $fullreportname = "reports/cash_register_stats.tt";
		34
		35	my ($template, $borrowernumber, $cookie) = get_template_and_user({
		36	template_name => $fullreportname,
		37	query => $input,
		38	type => "intranet",
		39	authnotrequired => 0,
		40	flagsrequired => {reports => '*'},
		41	debug => 1,
		42	});
		43
		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 $branchcode = $input->param("branch") \|\| C4::Context->userenv->{'branch'};
		49	our $sep = ",";
		50
		51	$template->param(
		52	do_it => $do_it,
		53	DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
		54	);
		55
		56	#Initialize date pickers to today
		57	my $today = C4::Dates->today('iso');
		58	my $fromDate = $today;
		59	my $toDate = $today;
		60
		61	### fromdate today: $fromDate
		62
		63	my $query_manualinv = "SELECT id, authorised_value FROM authorised_values WHERE category = 'MANUAL_INV'";
		64	my $sth_manualinv = $dbh->prepare($query_manualinv) or die "Unable to prepare query" . $dbh->errstr;
		65	$sth_manualinv->execute() or die "Unable to execute query " . $sth_manualinv->errstr;
66	my $manualinv_types = $sth_manualinv->fetchall_arrayref({});
67
68	### $manualinv_types
69
70	if ($do_it) {
71
72	$fromDate = format_date_in_iso($input->param("filter_date_begin"));
73	$toDate = format_date_in_iso($input->param("filter_date_end"));
74
75	my $whereTType = '';
76
77	if ($transaction_type eq 'ALL') { #All Transactons
78	$whereTType = '';
79	} elsif ($transaction_type eq 'ACT') { #Active
80	$whereTType = " accounttype NOT IN ('F', 'FU', 'FOR', 'M', 'L') AND ";
81	} else { #Single transac type
82	if ($transaction_type eq 'FORW') {
83	$whereTType = " accounttype = 'FOR' OR accounttype = 'W' AND ";
84	} else {
85	$whereTType = " accounttype = '$transaction_type' AND ";
86	}
87	}
88
89	my $whereBranchCode = '';
90	if ($branchcode ne 'ALL') {
91	$whereBranchCode = "AND bo.branchcode = '$branchcode'";
92	}
93
94	### $transaction_type;
95
96	my $query = "
97	SELECT round(amount,2) AS amount, description,
98	bo.surname AS bsurname, bo.firstname AS bfirstname, m.surname AS msurname, m.firstname AS mfirstname,
99	bo.cardnumber, br.branchname, bo.borrowernumber,
100	al.borrowernumber, DATE(al.date) as date, al.accounttype, al.amountoutstanding,
101	bi.title, bi.biblionumber, i.barcode, i.itype
102	FROM accountlines al
103	LEFT JOIN borrowers bo ON (al.borrowernumber = bo.borrowernumber)
104	LEFT JOIN borrowers m ON (al.manager_id = m.borrowernumber)
105	LEFT JOIN branches br ON (br.branchcode = m.branchcode )
106	LEFT JOIN items i ON (i.itemnumber = al.itemnumber)
107	LEFT JOIN biblio bi ON (bi.biblionumber = i.biblionumber)
108	WHERE $whereTType
109	CAST(al.date AS DATE) BETWEEN ? AND ?
110	$whereBranchCode
111	ORDER BY al.date
112	";
113	my $sth_stats = $dbh->prepare($query) or die "Unable to prepare query" . $dbh->errstr;
114	$sth_stats->execute($fromDate, $toDate) or die "Unable to execute query " . $sth_stats->errstr;
115
116	my @loopresult;
117	my $grantotal = 0;
118	while ( my $row = $sth_stats->fetchrow_hashref()) {
119	$row->{amountoutstanding} = 0 if (!$row->{amountoutstanding});
120	#if ((abs($row->{amount}) - $row->{amountoutstanding}) > 0) {
121	$row->{amount} = sprintf("%.2f", abs ($row->{amount}));
122	$row->{date} = format_date($row->{date});
123	### date : $row->{date}
124
125	push (@loopresult, $row);
126	$grantotal += abs($row->{amount});
127	#}
128	}
129
130	my @currency = GetCurrency();
131	$grantotal = sprintf("%.2f", $grantotal);
132
133	if($output eq 'screen'){
134	$template->param(
135	loopresult => \@loopresult,
136	total => $grantotal,
137	);
138	} else{
139	binmode STDOUT, ':encoding(UTF-8)';
140	print $input->header(
141	-type => 'application/vnd.sun.xml.calc',
142	-encoding => 'utf-8',
143	-name => "$basename.csv",
144	-attachment => "$basename.csv"
145	);
146
147	print "Manager name".$sep;
148	print "Borrower cardnumber".$sep;
149	print "Borrower name".$sep;
150	print "Branch".$sep;
151	print "Transaction date".$sep;
152	print "Transaction type".$sep;
153	print "Amount".$sep;
154	print "Biblio title".$sep;
155	print "Barcode".$sep;
156	print "Document type"."\n";
157
158	foreach my $item (@loopresult){
159	print $item->{mfirstname}. ' ' . $item->{msurname} . $sep;
160	print $item->{cardnumber}.$sep;
161	print $item->{bfirstname}. ' ' . $item->{bsurname} . $sep;
162	print $item->{branchname}.$sep;
163	print $item->{date}.$sep;
164	print $item->{accounttype}.$sep;
165	print $item->{amount}.$sep;
166	print $item->{title}.$sep;
167	print $item->{barcode}.$sep;
168	print $item->{itype}."\n";
169	}
170
171	print $sep x 6;
172	print $grantotal."\n";
173	exit(1);
174	}
175
176	}
177
178	### fromdate final: $fromDate
179	### toDate final: $toDate
180	$template->param(
181	beginDate => format_date($fromDate),
182	endDate => format_date($toDate),
183	transaction_type => $transaction_type,
184	branchloop => C4::Branch::GetBranchesLoop($branchcode),
185	manualinv_types => $manualinv_types,
186	);
187	output_html_with_http_headers $input, $cookie, $template->output;
188
189	1;