Attachment #53573 for bug #6934

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




//<![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

    <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 | $KohaDates %] to [% endDate | $KohaDates %]</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 | $KohaDates %]" />
                        <label for="filter_date_end">To</label>
                        <input type="text" size="10" id="filter_date_end" name="filter_date_end" value="[% endDate | $KohaDates %]" />
                    </td>
                </tr>
            </tbody>
        </table><br />
        </fieldset>

        <fieldset class="rows">
            <ol>
                <br>
                <li>
                    <label for="filter_date_begin">From: </label>
                    <input type="text" size="10" id="filter_date_begin" name="filter_date_begin" value="[% beginDate | $KohaDates %]" class="datepickerfrom" />
                    <div class="hint">[% INCLUDE 'date-format.inc' %]</div>
                </li>
                <li>
                    <label for="filter_date_end">To: </label>
                    <input type="text" size="10" id="filter_date_end" name="filter_date_end" value="[% endDate | $KohaDates %]" class="datepickerto" />
                    <div class="hint">[% INCLUDE 'date-format.inc' %]</div>
                </li>

                <li>
                    <label for="">Transaction type:</label>
                    <select name="transaction_type" id="transaction_type">

                    <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" />
                    <label class="inline" for="sep">Delimiter: </label>
                    <select name="sep" id="sep" size="1">
                        [% FOREACH value IN CGIsepChoice.values.sort() %]
                           [% IF ( value == CGIsepChoice.default ) %]
                               <option value="[% value %]" selected="selected">[% value %]</option>
                           [% ELSE %]
                              <option value="[% value %]">[% value %]</option>
                           [% END %]
                        [% END %]
                    </select>

                </li>
            </ol>




use DateTime;
use Koha::DateUtils;
use C4::Budgets qw/GetCurrency GetCurrencies/;
use Text::CSV::Encoded;
#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 => "reports/cash_register_stats.tt",
    query => $input,
    type => "intranet",
    authnotrequired => 0,

my $basename         = $input->param("basename");
my $transaction_type = $input->param("transaction_type") || 'ACT';
my $manager_branchcode       = $input->param("branch") || C4::Context->userenv->{'branch'};
our $sep = $input->param("sep") // ',';
$sep = "\t" if ($sep eq 'tabulation');

$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) {


        $whereBranchCode = "AND m.branchcode = '$manager_branchcode'";
    }

    ### $transaction_type;

    my $query = "
    SELECT round(amount,2) AS amount, description,

        #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);
            if($transaction_type eq 'ACT' && ($row->{accounttype} !~ /^C$|^CR$|^LR$|^Pay$/)){

        );
    } else{
        binmode STDOUT, ':encoding(UTF-8)';
        my $q_errors;
        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 ( $type, $content );
        if ( $format eq 'csv' ) {
            my $type = 'application/csv';
            my $csv = Text::CSV::Encoded->new({ encoding_out => 'UTF-8', sep_char => $delimiter});
            $csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag();
            my @headers = ();
            push @headers, "mfirstname",
			            "cardnumber",
			            "bfirstname",
			            "branchname",
			            "date",
			            "accounttype",
			            "amount",
			            "title",
			            "barcode",
			            "itype";
            if ($csv->combine(@headers)) {
                $content .= Encode::decode('UTF-8', $csv->string()) . "\n";
            } else {
                push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ;
            }
            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};
                if ($csv->combine(@rowValues)) {
                    $content .= $csv->string() . "\n";
                } else {
                    push @$q_errors, { combine => $csv->error_diag() } ;
                }
            }
        }
        print $input->header(
            -type => $type,
            -attachment=> $reportfilename
            -name => "$basename.csv",
            -attachment => "$basename.csv"
        );
        print $content;

        print $delimiter x 6;
        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";
        foreach my $err (@$q_errors) {
            print "# ERROR: " . (map {$_ . ": " . $err->{$_}} keys %$err) . "\n";
        }   # here we print all the non-fatal errors at the end.  Not super smooth, but better than nothing.
        exit(1);
    }

}

### fromdate final: $fromDate
### toDate final: $toDate
$template->param(
    beginDate        => $fromDate,
    endDate          => $toDate,
    transaction_type => $transaction_type,
    branchloop       => C4::Branch::GetBranchesLoop($manager_branchcode),
    manualinv_types  => $manualinv_types,
- 

Return to bug 6934

Lines 19-30 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cash_register_stats.tt (-37 / +13 lines)
19	//<![CDATA[	19	//<![CDATA[
20	actTotal = "";	20	actTotal = "";
21		21
22	function isNull(f,noalert) {
23	if (f.value.length > 0) {
24	return false;
25	}
26	return true;
27	}
28		22
29	$(document).ready(function() {	23	$(document).ready(function() {
30	// http://jqueryui.com/demos/datepicker/#date-range	24	// http://jqueryui.com/demos/datepicker/#date-range
Lines 87-114 $(document).ready(function() { Link Here
87	<h1>Cash register statistics</h1>	81	<h1>Cash register statistics</h1>
88	<form method="post" action="/cgi-bin/koha/reports/cash_register_stats.pl" id="frmCashRegister">	82	<form method="post" action="/cgi-bin/koha/reports/cash_register_stats.pl" id="frmCashRegister">
89	<fieldset class="rows">	83	<fieldset class="rows">
90	<legend>Cash register statistics [% beginDate \| $KohaDates %] to [% endDate \| $KohaDates %]</legend>	84	<legend>Cash register statistics [% beginDate \| $KohaDates %] to [% endDate \| $KohaDates %]</legend>
91	<table>
92	<thead>
93	<tr>
94	<th>Interval</th>
95	</tr>
96	</thead>
97	<tbody>
98	<tr>
99	<td>
100	<label for="filter_date_begin">From</label>
101	<input type="text" size="10" id="filter_date_begin" name="filter_date_begin" value="[% beginDate \| $KohaDates %]" />
102	<label for="filter_date_end">To</label>
103	<input type="text" size="10" id="filter_date_end" name="filter_date_end" value="[% endDate \| $KohaDates %]" />
104	</td>
105	</tr>
106	</tbody>
107	</table><br />
108	</fieldset>
109
110	<fieldset class="rows">
111	<ol>	85	<ol>
		86	<br>
		87	<li>
		88	<label for="filter_date_begin">From: </label>
		89	<input type="text" size="10" id="filter_date_begin" name="filter_date_begin" value="[% beginDate \| $KohaDates %]" class="datepickerfrom" />
		90	<div class="hint">[% INCLUDE 'date-format.inc' %]</div>
		91	</li>
		92	<li>
		93	<label for="filter_date_end">To: </label>
		94	<input type="text" size="10" id="filter_date_end" name="filter_date_end" value="[% endDate \| $KohaDates %]" class="datepickerto" />
		95	<div class="hint">[% INCLUDE 'date-format.inc' %]</div>
		96	</li>
		97
112	<li>	98	<li>
113	<label for="">Transaction type:</label>	99	<label for="">Transaction type:</label>
114	<select name="transaction_type" id="transaction_type">	100	<select name="transaction_type" id="transaction_type">
Lines 223-238 $(document).ready(function() { Link Here
223	<label for="outputfile">To a file:</label>	209	<label for="outputfile">To a file:</label>
224	<input type="radio" name="output" value="file" id="outputfile" />	210	<input type="radio" name="output" value="file" id="outputfile" />
225	<label class="inline" for="basename">Named: </label><input type="text" name="basename" id="basename" value="Export" />	211	<label class="inline" for="basename">Named: </label><input type="text" name="basename" id="basename" value="Export" />
226	<label class="inline" for="sep">Delimiter: </label>
227	<select name="sep" id="sep" size="1">
228	[% FOREACH value IN CGIsepChoice.values.sort() %]
229	[% IF ( value == CGIsepChoice.default ) %]
230	<option value="[% value %]" selected="selected">[% value %]</option>
231	[% ELSE %]
232	<option value="[% value %]">[% value %]</option>
233	[% END %]
234	[% END %]
235	</select>
236		212
237	</li>	213	</li>
238	</ol>	214	</ol>

Lines 25-39 use C4::Circulation; Link Here

(-)a/reports/cash_register_stats.pl (-42 / +57 lines)
25	use DateTime;	25	use DateTime;
26	use Koha::DateUtils;	26	use Koha::DateUtils;
27	use C4::Budgets qw/GetCurrency GetCurrencies/;	27	use C4::Budgets qw/GetCurrency GetCurrencies/;
		28	use Text::CSV::Encoded;
28	#use Data::Dumper;	29	#use Data::Dumper;
29	#use Smart::Comments;	30	#use Smart::Comments;
30		31
31	my $input = new CGI;	32	my $input = new CGI;
32	my $dbh = C4::Context->dbh;	33	my $dbh = C4::Context->dbh;
33	my $fullreportname = "reports/cash_register_stats.tt";
34		34
35	my ($template, $borrowernumber, $cookie) = get_template_and_user({	35	my ($template, $borrowernumber, $cookie) = get_template_and_user({
36	template_name => $fullreportname,	36	template_name => "reports/cash_register_stats.tt",
37	query => $input,	37	query => $input,
38	type => "intranet",	38	type => "intranet",
39	authnotrequired => 0,	39	authnotrequired => 0,
Lines 46-53 my $output = $input->param("output"); Link Here
46	my $basename = $input->param("basename");	46	my $basename = $input->param("basename");
47	my $transaction_type = $input->param("transaction_type") \|\| 'ACT';	47	my $transaction_type = $input->param("transaction_type") \|\| 'ACT';
48	my $manager_branchcode = $input->param("branch") \|\| C4::Context->userenv->{'branch'};	48	my $manager_branchcode = $input->param("branch") \|\| C4::Context->userenv->{'branch'};
49	our $sep = $input->param("sep") // ',';
50	$sep = "\t" if ($sep eq 'tabulation');
51		49
52	$template->param(	50	$template->param(
53	do_it => $do_it,	51	do_it => $do_it,
Lines 57-70 $template->param( Link Here
57	#Initialize date pickers to today	55	#Initialize date pickers to today
58	my $fromDate = dt_from_string;	56	my $fromDate = dt_from_string;
59	my $toDate = dt_from_string;	57	my $toDate = dt_from_string;
60	### fromdate today: $fromDate
61		58
62	my $query_manualinv = "SELECT id, authorised_value FROM authorised_values WHERE category = 'MANUAL_INV'";	59	my $query_manualinv = "SELECT id, authorised_value FROM authorised_values WHERE category = 'MANUAL_INV'";
63	my $sth_manualinv = $dbh->prepare($query_manualinv) or die "Unable to prepare query" . $dbh->errstr;	60	my $sth_manualinv = $dbh->prepare($query_manualinv) or die "Unable to prepare query" . $dbh->errstr;
64	$sth_manualinv->execute() or die "Unable to execute query " . $sth_manualinv->errstr;	61	$sth_manualinv->execute() or die "Unable to execute query " . $sth_manualinv->errstr;
65	my $manualinv_types = $sth_manualinv->fetchall_arrayref({});	62	my $manualinv_types = $sth_manualinv->fetchall_arrayref({});
66		63
67	### $manualinv_types
68		64
69	if ($do_it) {	65	if ($do_it) {
70		66
Lines 92-98 if ($do_it) { Link Here
92	$whereBranchCode = "AND m.branchcode = '$manager_branchcode'";	88	$whereBranchCode = "AND m.branchcode = '$manager_branchcode'";
93	}	89	}
94		90
95	### $transaction_type;
96		91
97	my $query = "	92	my $query = "
98	SELECT round(amount,2) AS amount, description,	93	SELECT round(amount,2) AS amount, description,
Lines 121-127 if ($do_it) { Link Here
121	#if ((abs($row->{amount}) - $row->{amountoutstanding}) > 0) {	116	#if ((abs($row->{amount}) - $row->{amountoutstanding}) > 0) {
122	$row->{amount} = sprintf("%.2f", abs ($row->{amount}));	117	$row->{amount} = sprintf("%.2f", abs ($row->{amount}));
123	$row->{date} = dt_from_string($row->{date}, 'sql');	118	$row->{date} = dt_from_string($row->{date}, 'sql');
124	### date : $row->{date}
125		119
126	push (@loopresult, $row);	120	push (@loopresult, $row);
127	if($transaction_type eq 'ACT' && ($row->{accounttype} !~ /^C$\|^CR$\|^LR$\|^Pay$/)){	121	if($transaction_type eq 'ACT' && ($row->{accounttype} !~ /^C$\|^CR$\|^LR$\|^Pay$/)){
Lines 148-196 if ($do_it) { Link Here
148	);	142	);
149	} else{	143	} else{
150	binmode STDOUT, ':encoding(UTF-8)';	144	binmode STDOUT, ':encoding(UTF-8)';
		145	my $q_errors;
		146	my $format = 'csv';
		147	my $reportname = $input->param('basename');
		148	my $reportfilename = $reportname ? "$reportname.$format" : "reportresults.$format" ;
		149	#my $reportfilename = "$reportname.html" ;
		150	my $delimiter = C4::Context->preference('delimiter') \|\| ',';
		151	my ( $type, $content );
		152	if ( $format eq 'csv' ) {
		153	my $type = 'application/csv';
		154	my $csv = Text::CSV::Encoded->new({ encoding_out => 'UTF-8', sep_char => $delimiter});
		155	$csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag();
		156	my @headers = ();
		157	push @headers, "mfirstname",
		158	"cardnumber",
		159	"bfirstname",
		160	"branchname",
		161	"date",
		162	"accounttype",
		163	"amount",
		164	"title",
		165	"barcode",
		166	"itype";
		167	if ($csv->combine(@headers)) {
		168	$content .= Encode::decode('UTF-8', $csv->string()) . "\n";
		169	} else {
		170	push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ;
		171	}
		172	foreach my $row (@loopresult) {
		173	my @rowValues = ();
		174	push @rowValues, $row->{mfirstname},
		175	$row->{cardnumber},
		176	$row->{bfirstname},
		177	$row->{branchname},
		178	$row->{date},
		179	$row->{accounttype},
		180	$row->{amount},
		181	$row->{title},
		182	$row->{barcode};
		183	$row->{itype};
		184	if ($csv->combine(@rowValues)) {
		185	$content .= $csv->string() . "\n";
		186	} else {
		187	push @$q_errors, { combine => $csv->error_diag() } ;
		188	}
		189	}
		190	}
151	print $input->header(	191	print $input->header(
152	-type => 'application/vnd.sun.xml.calc',	192	-type => $type,
153	-encoding => 'utf-8',	193	-attachment=> $reportfilename
154	-name => "$basename.csv",
155	-attachment => "$basename.csv"
156	);	194	);
		195	print $content;
157		196
158	print "Manager name".$sep;	197	print $delimiter x 6;
159	print "Borrower cardnumber".$sep;
160	print "Borrower name".$sep;
161	print "Branch".$sep;
162	print "Transaction date".$sep;
163	print "Transaction type".$sep;
164	print "Amount".$sep;
165	print "Biblio title".$sep;
166	print "Barcode".$sep;
167	print "Document type"."\n";
168
169	foreach my $item (@loopresult){
170	print $item->{mfirstname}. ' ' . $item->{msurname} . $sep;
171	print $item->{cardnumber}.$sep;
172	print $item->{bfirstname}. ' ' . $item->{bsurname} . $sep;
173	print $item->{branchname}.$sep;
174	print $item->{date}.$sep;
175	print $item->{accounttype}.$sep;
176	print $item->{amount}.$sep;
177	print $item->{title}.$sep;
178	print $item->{barcode}.$sep;
179	print $item->{itype}."\n";
180	}
181
182	print $sep x 6;
183	print $grantotal."\n";	198	print $grantotal."\n";
		199	foreach my $err (@$q_errors) {
		200	print "# ERROR: " . (map {$_ . ": " . $err->{$_}} keys %$err) . "\n";
		201	} # here we print all the non-fatal errors at the end. Not super smooth, but better than nothing.
184	exit(1);	202	exit(1);
185	}	203	}
186		204
187	}	205	}
188		206
189	### fromdate final: $fromDate
190	### toDate final: $toDate
191	$template->param(	207	$template->param(
192	beginDate => dt_from_string($fromDate),	208	beginDate => $fromDate,
193	endDate => dt_from_string($toDate),	209	endDate => $toDate,
194	transaction_type => $transaction_type,	210	transaction_type => $transaction_type,
195	branchloop => C4::Branch::GetBranchesLoop($manager_branchcode),	211	branchloop => C4::Branch::GetBranchesLoop($manager_branchcode),
196	manualinv_types => $manualinv_types,	212	manualinv_types => $manualinv_types,
197	-