[Koha-patches] [PATCH] Custom Reports - Access PA
Kyle M Hall
kyle.m.hall at gmail.com
Tue Jan 26 14:32:57 CET 2010
---
.../custom_reports/org.ccfls.access_pa/report.ini | 5 +
.../custom_reports/org.ccfls.access_pa/step1.pl | 16 +++
.../custom_reports/org.ccfls.access_pa/step1.tmpl | 49 +++++++++
.../custom_reports/org.ccfls.access_pa/step2.pl | 115 ++++++++++++++++++++
.../custom_reports/org.ccfls.access_pa/step2.tmpl | 78 +++++++++++++
5 files changed, 263 insertions(+), 0 deletions(-)
create mode 100644 reports/custom_reports/org.ccfls.access_pa/report.ini
create mode 100755 reports/custom_reports/org.ccfls.access_pa/step1.pl
create mode 100644 reports/custom_reports/org.ccfls.access_pa/step1.tmpl
create mode 100755 reports/custom_reports/org.ccfls.access_pa/step2.pl
create mode 100644 reports/custom_reports/org.ccfls.access_pa/step2.tmpl
diff --git a/reports/custom_reports/org.ccfls.access_pa/report.ini b/reports/custom_reports/org.ccfls.access_pa/report.ini
new file mode 100644
index 0000000..2f6fda8
--- /dev/null
+++ b/reports/custom_reports/org.ccfls.access_pa/report.ini
@@ -0,0 +1,5 @@
+name=Access PA
+author=Kyle M Hall
+description=A Report of the data neccessary to fill out the Access PA report form.
+start=access_pa/step1.pl
+
diff --git a/reports/custom_reports/org.ccfls.access_pa/step1.pl b/reports/custom_reports/org.ccfls.access_pa/step1.pl
new file mode 100755
index 0000000..1c54a59
--- /dev/null
+++ b/reports/custom_reports/org.ccfls.access_pa/step1.pl
@@ -0,0 +1,16 @@
+#!/usr/bin/perl
+
+use HTML::Template;
+use CGI qw(:all);
+
+use C4::Context;
+use C4::Branch;
+
+my $template = HTML::Template->new( filename => 'step1.tmpl' );
+
+$template->param(
+ branches_loop => GetBranchesLoop()
+);
+
+print header;
+print $template->output();
\ No newline at end of file
diff --git a/reports/custom_reports/org.ccfls.access_pa/step1.tmpl b/reports/custom_reports/org.ccfls.access_pa/step1.tmpl
new file mode 100644
index 0000000..76f91cd
--- /dev/null
+++ b/reports/custom_reports/org.ccfls.access_pa/step1.tmpl
@@ -0,0 +1,49 @@
+<html>
+<head>
+ <title>Access PA Report</title>
+</head>
+<body>
+
+<h2>Access PA Report:</h2>
+
+<form action="step2.pl" method="get">
+
+ <p>
+ <label for="branch">Select Library:</label>
+ <select name="branch">
+ <!-- TMPL_LOOP NAME="branches_loop" -->
+ <option value="<!-- TMPL_VAR name="value" escape="html" -->" <!-- TMPL_IF NAME="selected" -->selected="selected"<!-- /TMPL_IF --> >
+ <!-- TMPL_VAR name="branchname" -->
+ </option>
+ <!-- /TMPL_LOOP -->
+ </select>
+ </p>
+
+ <p>
+ Starting Date:
+ <input maxlength="2" size="2" name="startMonth">
+ /
+ <input maxlength="2" size="2" name="startDay">
+ /
+ <input maxlength="4" size="4" name="startYear">
+ (MM/DD/YYYY)
+ </p>
+
+ <p>
+ Ending Date:
+ <input maxlength="2" size="2" name="endMonth">
+ /
+ <input maxlength="2" size="2" name="endDay">
+ /
+ <input maxlength="4" size="4" name="endYear">
+ (MM/DD/YYYY)
+ </p>
+
+ <p>
+ <input type="submit" value="Run Report" />
+ </p>
+
+</form>
+
+</body>
+</html>
\ No newline at end of file
diff --git a/reports/custom_reports/org.ccfls.access_pa/step2.pl b/reports/custom_reports/org.ccfls.access_pa/step2.pl
new file mode 100755
index 0000000..ed98066
--- /dev/null
+++ b/reports/custom_reports/org.ccfls.access_pa/step2.pl
@@ -0,0 +1,115 @@
+#!/usr/bin/perl
+
+use warnings;
+use strict;
+
+use HTML::Template;
+use Date::Calc;
+use CGI qw(:all);
+
+use C4::Context;
+use C4::Branch;
+use C4::Dates;
+
+my $cgi = CGI->new;
+
+my $template = HTML::Template->new( filename => 'step2.tmpl' );
+
+my $branch = $cgi->param('branch');
+
+my $startDay = $cgi->param('startDay');
+my $startMonth = $cgi->param('startMonth');
+my $startYear = $cgi->param('startYear');
+
+my $endDay = $cgi->param('endDay');
+my $endMonth = $cgi->param('endMonth');
+my $endYear = $cgi->param('endYear');
+
+my $workingYear = $startYear;
+my $workingMonth = $startMonth;
+my $workingDay = $startDay;
+
+my $startDate = "$startYear-$startMonth-$startDay";
+my $endDate = "$endYear-$endMonth-$endDay";
+my $workingDate = "$workingYear-$workingMonth-$workingDay";
+
+my ( $issuesWithinCountyTotal, $issuesOutsideCountyTotal, $issuesWithinAndOutsideCountyTotal, $issuesAllTotal ) = 0;
+
+my $dbh = C4::Context->dbh;
+
+my $rowClass = "even";
+my @results;
+while ( $workingDate <= $endDate ) {
+ my $query = "
+ SELECT
+ (
+ SELECT COUNT(*)
+ FROM statistics, borrowers
+ WHERE statistics.borrowernumber = borrowers.borrowernumber
+ AND (
+ statistics.type = 'issue'
+ OR statistics.type = 'renew'
+ )
+ AND DATE(statistics.datetime) = DATE('$workingDate')
+ AND borrowers.branchcode != '$branch'
+ AND statistics.branch = '$branch'
+ ) AS issuesWithinCounty,
+
+ (
+ SELECT COUNT(*)
+ FROM statistics, borrowers
+ WHERE statistics.borrowernumber = borrowers.borrowernumber
+ AND (
+ statistics.type = 'issue'
+ OR statistics.type = 'renew'
+ )
+ AND DATE(statistics.datetime) = DATE('$workingDate')
+ AND borrowers.categorycode = 'AC'
+ AND statistics.branch = '$branch'
+ ) AS issuesOutsideCounty,
+
+ (
+ SELECT COUNT(*)
+ FROM statistics
+ WHERE ( statistics.type = 'issue' OR statistics.type = 'renew' )
+ AND statistics.branch = '$branch'
+ AND DATE(statistics.datetime) = DATE('$workingDate')
+ ) AS issuesAll
+ ";
+
+ my $sth = $dbh->prepare( $query );
+ $sth->execute();
+ my $row = $sth->fetchrow_hashref();
+
+ $issuesWithinCountyTotal += $row->{'issuesWithinCounty'};
+ $issuesOutsideCountyTotal += $row->{'issuesOutsideCounty'};
+ $issuesAllTotal += $row->{'issuesAll'};
+
+ $row->{'issuesWithinAndOutsideCounty'} = $row->{'issuesWithinCounty'} + $row->{'issuesOutsideCounty'};
+ $row->{'date'} = C4::Dates->new($workingDate, "iso")->output();
+ $row->{'class'} = $rowClass;
+
+ push( @results, $row );
+
+ ( $workingYear, $workingMonth, $workingDay ) = Date::Calc::Add_Delta_Days( $workingYear, $workingMonth, $workingDay, 1 );
+ $workingMonth = "0$workingMonth" unless ( $workingMonth > 9 );
+ $workingDay = "0$workingDay" unless ( $workingDay > 9 );
+ $workingDate = "$workingYear-$workingMonth-$workingDay";
+
+ $rowClass = ( $rowClass eq 'odd' ) ? 'even' : 'odd';
+}
+
+$template->param(
+ results_loop => \@results,
+ issuesWithinCountyTotal => $issuesWithinCountyTotal,
+ issuesOutsideCountyTotal => $issuesOutsideCountyTotal,
+ issuesWithinAndOutsideCountyTotal => $issuesWithinCountyTotal + $issuesOutsideCountyTotal,
+ issuesAllTotal => $issuesAllTotal,
+ totalsClass => $rowClass,
+ startDate => C4::Dates->new($startDate, "iso")->output(),
+ endDate => C4::Dates->new($endDate, "iso")->output(),
+ branch => GetBranchName($branch)
+);
+
+print header;
+print $template->output();
diff --git a/reports/custom_reports/org.ccfls.access_pa/step2.tmpl b/reports/custom_reports/org.ccfls.access_pa/step2.tmpl
new file mode 100644
index 0000000..22e088b
--- /dev/null
+++ b/reports/custom_reports/org.ccfls.access_pa/step2.tmpl
@@ -0,0 +1,78 @@
+<html>
+<head>
+ <title>Access Pa Tally Worksheet</title>
+
+ <STYLE TYPE="text/css">
+ <!--
+ .odd, .odd TD, .odd TH {
+ background-color:lightgray;
+ }
+ -->
+ </STYLE>
+</head>
+
+<body>
+ <div align="center">
+ <h1>Statewide Library Card System</h1>
+ <h2>Tally Worksheet</h2>
+ <h2>Loans Made to Eligible Borrowers</h2>
+ </div>
+
+ <p>
+ <strong>Library Name:</strong>
+ <!-- TMPL_VAR NAME="branch" -->
+ <hr noshade align="left" width="80%">
+ </p>
+
+ <p>
+ <strong>Name of Branch/Department/Unit:</strong>
+
+ <hr noshade align="left" width="80%">
+ </p>
+
+ <p>
+ <strong>Reporting Period Dates:</strong>
+ <!-- TMPL_VAR NAME="startDate" --> - <!-- TMPL_VAR NAME="endDate" -->
+ <hr noshade align="left" width="80%">
+ </p>
+
+ <table border="1" cellpadding="5">
+ <tr class="odd">
+ <th colspan="6">PLEASE NOTIFY COMMONWEALTH LIBRARY IF THIS FORM IS NOT USED BY YOUR LIBRARY</td>
+ </tr>
+
+ <tr class="lightrow">
+ <th colspan="4">Tally of Items Loaned</td>
+ <th></td>
+ <th><font size="-3">Circulation of All Material</font></td>
+ </tr>
+
+ <tr class="odd">
+ <th>Date</td>
+ <th>Loans Within County</td>
+ <th>Loans Outside County</td>
+ <th>Total</td>
+ <th></td>
+ <th>Total</td>
+ </tr>
+
+ <!-- TMPL_LOOP NAME="results_loop" -->
+ <tr class="<!-- TMPL_VAR NAME="class" -->">
+ <td><!-- TMPL_VAR NAME='date' --></td>
+ <td><!-- TMPL_VAR NAME='issuesWithinCounty' --></td>
+ <td><!-- TMPL_VAR NAME='issuesOutsideCounty' --></td>
+ <td><!-- TMPL_VAR NAME='issuesWithinAndOutsideCounty' --></td>
+ <td></td>
+ <td><!-- TMPL_VAR NAME='issuesAll' --></td>
+ </tr>
+ <!-- /TMPL_LOOP -->
+
+ <tr class="<!-- TMPL_VAR NAME="totalsClass" -->">
+ <th>Total</th>
+ <td><!-- TMPL_VAR NAME="issuesWithinCountyTotal" --></td>
+ <td><!-- TMPL_VAR NAME="issuesOutsideCountyTotal" --></td>
+ <td><!-- TMPL_VAR NAME="issuesWithinAndOutsideCountyTotal" --></td>
+ <td></td>
+ <td><!-- TMPL_VAR NAME="issuesAllTotal" --></td>
+ </tr>
+ </table>
--
1.5.6.5
More information about the Koha-patches
mailing list