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

(-)a/koha-tmpl/intranet-tmpl/prog/en/includes/reports-menu.inc (+1 lines)
Lines 10-15 Link Here
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
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cash_register_stats.tt (+307 lines)
Line 0 Link Here
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>&nbsp;</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">&nbsp;</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' %]
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt (-9 / +10 lines)
Lines 28-42 Link Here
28
            <li><a href="/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary">View dictionary</a></li>
28
            <li><a href="/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary">View dictionary</a></li>
29
        </ul>
29
        </ul>
30
30
31
        <h2>Statistics wizards</h2>
31
	<h2>Statistics wizards</h2>
32
        <ul>
32
	<ul>
33
            <li><a href="/cgi-bin/koha/reports/acquisitions_stats.pl">Acquisitions</a></li>
33
		<li><a href="/cgi-bin/koha/reports/acquisitions_stats.pl">Acquisitions</a></li>
34
            <li><a href="/cgi-bin/koha/reports/borrowers_stats.pl">Patrons</a></li>
34
		<li><a href="/cgi-bin/koha/reports/borrowers_stats.pl">Patrons</a></li>
35
            <li><a href="/cgi-bin/koha/reports/catalogue_stats.pl">Catalog</a></li>
35
		<li><a href="/cgi-bin/koha/reports/catalogue_stats.pl">Catalog</a></li>
36
            <li><a href="/cgi-bin/koha/reports/issues_stats.pl">Circulation</a></li>
36
		<li><a href="/cgi-bin/koha/reports/issues_stats.pl">Circulation</a></li>
37
            <li><a href="/cgi-bin/koha/reports/serials_stats.pl">Serials</a></li>
37
		<li><a href="/cgi-bin/koha/reports/serials_stats.pl">Serials</a></li>
38
            <li><a href="/cgi-bin/koha/reports/reserves_stats.pl">Holds</a></li>
38
        <li><a href="/cgi-bin/koha/reports/cash_register_stats.pl">Cash Register</a></li>
39
        </ul>
39
		<li><a href="/cgi-bin/koha/reports/reserves_stats.pl">Holds</a></li>
40
      </ul>
40
41
41
        [% IF UseKohaPlugins %]
42
        [% IF UseKohaPlugins %]
42
        <h2>Report plugins</h2>
43
        <h2>Report plugins</h2>
(-)a/reports/cash_register_stats.pl (-1 / +189 lines)
Line 0 Link Here
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;

Return to bug 6934