[Koha-patches] [PATCH] Custom Reports - Daily Report
Kyle M Hall
kyle.m.hall at gmail.com
Tue Jan 26 14:32:55 CET 2010
---
.../org.ccfls.daily_report/report.ini | 5 +
.../custom_reports/org.ccfls.daily_report/step1.pl | 16 +++
.../org.ccfls.daily_report/step1.tmpl | 25 ++++
.../custom_reports/org.ccfls.daily_report/step2.pl | 134 ++++++++++++++++++++
.../org.ccfls.daily_report/step2.tmpl | 65 ++++++++++
5 files changed, 245 insertions(+), 0 deletions(-)
create mode 100644 reports/custom_reports/org.ccfls.daily_report/report.ini
create mode 100755 reports/custom_reports/org.ccfls.daily_report/step1.pl
create mode 100644 reports/custom_reports/org.ccfls.daily_report/step1.tmpl
create mode 100755 reports/custom_reports/org.ccfls.daily_report/step2.pl
create mode 100644 reports/custom_reports/org.ccfls.daily_report/step2.tmpl
diff --git a/reports/custom_reports/org.ccfls.daily_report/report.ini b/reports/custom_reports/org.ccfls.daily_report/report.ini
new file mode 100644
index 0000000..d018da5
--- /dev/null
+++ b/reports/custom_reports/org.ccfls.daily_report/report.ini
@@ -0,0 +1,5 @@
+name=Daily Report
+author=Kyle M Hall
+description=A daily report that displays useful statistics that vary day to day.
+start=daily_report/step1.pl
+
diff --git a/reports/custom_reports/org.ccfls.daily_report/step1.pl b/reports/custom_reports/org.ccfls.daily_report/step1.pl
new file mode 100755
index 0000000..1c54a59
--- /dev/null
+++ b/reports/custom_reports/org.ccfls.daily_report/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.daily_report/step1.tmpl b/reports/custom_reports/org.ccfls.daily_report/step1.tmpl
new file mode 100644
index 0000000..9656071
--- /dev/null
+++ b/reports/custom_reports/org.ccfls.daily_report/step1.tmpl
@@ -0,0 +1,25 @@
+<html>
+<head>
+ <title>Daily Report: Select Library</title>
+</head>
+<body>
+
+ <h1>Daily Report</h1>
+
+ <form action="step2.pl" method="get">
+
+ <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>
+
+ <input type="submit" value="Run Report" />
+
+ </form>
+
+</body>
+</html>
\ No newline at end of file
diff --git a/reports/custom_reports/org.ccfls.daily_report/step2.pl b/reports/custom_reports/org.ccfls.daily_report/step2.pl
new file mode 100755
index 0000000..87461cc
--- /dev/null
+++ b/reports/custom_reports/org.ccfls.daily_report/step2.pl
@@ -0,0 +1,134 @@
+#!/usr/bin/perl
+
+use HTML::Template;
+use CGI qw(:all);
+
+use C4::Context;
+use C4::Branch;
+use C4::Dates;
+
+my $cgi = CGI->new;
+
+my $branch = $cgi->param('branch');
+
+my $template = HTML::Template->new( filename => 'step2.tmpl' );
+
+my $dbh = C4::Context->dbh;
+my $query = "
+SELECT
+( SELECT branchname FROM branches WHERE branches.branchcode = '$branch' ) AS Library,
+
+( SELECT COUNT(*) FROM items WHERE holdingbranch = '$branch' ) AS TotalNumberOfItems,
+
+( SELECT COUNT(*) FROM items WHERE ( itemlost != 0 AND holdingbranch = '$branch' ) ) AS NumberOfLostItems,
+
+( SELECT COUNT(*) FROM borrowers WHERE branchcode = '$branch' ) AS TotalLibraryPatrons,
+
+( SELECT COUNT(*) FROM borrowers ) AS TotalSystemPatrons,
+
+( SELECT COUNT(*) FROM issues, items WHERE issues.itemnumber = items.itemnumber AND issues.returndate IS NULL AND items.holdingbranch = '$branch' ) AS
+ItemsInCirculation,
+
+( SELECT COUNT(DISTINCT borrowers.borrowernumber) FROM issues, borrowers WHERE issues.borrowernumber = borrowers.borrowernumber AND issues.return IS
+NULL AND issues.branchcode LIKE '$branch' ) AS PatronsWithCurrentIssues,
+
+( SELECT COUNT( DISTINCT( issues.itemnumber ) ) FROM issues WHERE issues.return IS NULL AND issues.branchcode = '$branch' AND DATE(date_due) > DATE(
+CURDATE() ) ) AS OverdueItems,
+
+( SELECT COUNT( DISTINCT borrowers.borrowernumber )
+FROM borrowers, issues, issuingrules, items, biblioitems
+WHERE borrowers.borrowernumber = issues.borrowernumber
+AND issuingrules.categorycode = borrowers.categorycode
+AND issuingrules.itemtype = biblioitems.itemtype
+AND issuingrules.branchcode = '$branch'
+AND issues.itemnumber = items.itemnumber
+AND biblioitems.biblionumber = items.biblionumber
+AND borrowers.branchcode = '$branch'
+AND issues.return IS NULL
+AND DATE( issues.date_due ) < CURDATE( ) + INTERVAL issuingrules.firstremind DAY ) AS PatronsWithOverdues,
+
+( SELECT COUNT(*) FROM statistics, items
+WHERE
+ ( statistics.type = 'issue' OR statistics.type = 'renew' )
+ AND statistics.itemnumber = items.itemnumber
+ AND statistics.branch = '$branch'
+ AND homebranch LIKE '$branch'
+ AND DATE(statistics.datetime) >= DATE( CURDATE() )
+ AND DATE(statistics.datetime) <= DATE( CURDATE() ) ) AS IssuesToday,
+
+( SELECT COUNT(*) FROM statistics, items
+WHERE
+ ( statistics.type = 'issue' OR statistics.type = 'renew' )
+ AND statistics.itemnumber = items.itemnumber
+ AND statistics.branch = '$branch'
+ AND homebranch LIKE '$branch'
+ AND DATE(statistics.datetime) >= DATE( CONCAT( YEAR(CURDATE()), '-', MONTH(CURDATE()), '-01' ) )
+ AND DATE(statistics.datetime) <= DATE( CONCAT( YEAR(CURDATE()), '-', MONTH(CURDATE()), '-31' ) ) ) AS IssuesMonthToDate,
+
+(
+ SELECT COUNT(*) FROM reserves WHERE (
+ reserves.branchcode = '$branch' AND
+ reservedate LIKE DATE(CURDATE())
+ )
+) AS ReservesToday,
+
+(
+ SELECT COUNT(DISTINCT(borrowernumber)) FROM reserves WHERE (
+ reserves.branchcode = '$branch' AND
+ DATE( reservedate ) = DATE( CURDATE() )
+ )
+) AS PatronsMakingReservesToday,
+
+(
+ SELECT COUNT(DISTINCT accountlines.borrowernumber) AS borrowersWithFines FROM accountlines, borrowers
+ WHERE (
+ accountlines.borrowernumber = borrowers.borrowernumber AND
+ accountlines.amountoutstanding > 0 AND
+ borrowers.branchcode = '$branch'
+ )
+) AS BorrowersWithOutstandingFines,
+
+(
+ SELECT SUM(amountoutstanding) FROM accountlines, items
+ WHERE (
+ accountlines.itemnumber = items.itemnumber AND
+ items.holdingbranch = '$branch'
+ )
+) AS TotalFinesOutstanding,
+
+(
+ SELECT SUM(amount) FROM accountlines, items WHERE (
+ accountlines.itemnumber = items.itemnumber AND
+ items.holdingbranch = '$branch'
+ AND accountlines.accounttype = 'Pay' AND
+ accountlines.timestamp LIKE CONCAT( DATE(CURDATE()), '%' )
+ )
+) AS TotalFinesCollectedToday,
+
+(
+ SELECT SUM(amount) FROM accountlines, items WHERE (
+ accountlines.itemnumber = items.itemnumber AND
+ items.holdingbranch = '$branch'
+ AND accountlines.accounttype = 'Pay' AND
+ accountlines.timestamp LIKE CONCAT( YEAR(CURDATE()), '-', MONTH(CURDATE()), '%' )
+ )
+) AS TotalFinesCollectedMonthToDate
+";
+
+
+my $sth = $dbh->prepare( $query );
+$sth->execute();
+
+my $data = $sth->fetchrow_hashref();
+
+foreach my $key ( keys %$data ) {
+ $template->param(
+ $key => $data->{$key}
+ );
+}
+
+my $date = C4::Dates->new();
+$template->param( dateRan => $date->output() );
+
+print header;
+print $template->output();
diff --git a/reports/custom_reports/org.ccfls.daily_report/step2.tmpl b/reports/custom_reports/org.ccfls.daily_report/step2.tmpl
new file mode 100644
index 0000000..c41ff54
--- /dev/null
+++ b/reports/custom_reports/org.ccfls.daily_report/step2.tmpl
@@ -0,0 +1,65 @@
+<html>
+<head>
+ <title>Daily Report</title>
+</head>
+<body>
+
+ <h3>Daily Report for <!-- TMPL_VAR NAME="Library" --></h3>
+
+ <h5>Date Ran: <!-- TMPL_VAR NAME="DateRan" --></h5>
+
+ <p>Total Number of Items: <!-- TMPL_VAR NAME="TotalNumberOfItems" --></p>
+ <p>Total Number of Lost Items: <!-- TMPL_VAR NAME="NumberOfLostItems" --></p>
+ <p>Total Number of Patrons: <!-- TMPL_VAR NAME="TotalLibraryPatrons" --></p>
+ <p>Total Number of Patrons ( System Wide ): <!-- TMPL_VAR NAME="TotalSystemPatrons" --></p>
+ <p>Current Items in Circulation: <!-- TMPL_VAR NAME="ItemsInCirculation" --></p>
+ <p>Patrons with Current Issues: <!-- TMPL_VAR NAME="PatronsWithCurrentIssues" --></p>
+ <p>Overdue Items: <!-- TMPL_VAR NAME="OverdueItems" --></p>
+ <p>Patrons with Overdues: <!-- TMPL_VAR NAME="PatronsWithOverdues" --></p>
+ <p>Issues Today: <!-- TMPL_VAR NAME="IssuesToday" --></p>
+ <p>Issues Month to Date: <!-- TMPL_VAR NAME="IssuesMonthToDate" --></p>
+ <p>Reserves Today: <!-- TMPL_VAR NAME="ReservesToday" --></p>
+ <p>Patrons Making Reserves Today: <!-- TMPL_VAR NAME="PatronsMakingReservesToday" --></p>
+ <p>Patrons with Outstanding Fines: <!-- TMPL_VAR NAME="BorrowersWithOutstandingFines" --></p>
+ <p>Total Fines Outstanding: <!-- TMPL_VAR NAME="TotalFinesOutstanding" --></p>
+ <p>Total Fines Collected Today: <!-- TMPL_VAR NAME="TotalFinesCollectedToday" --></p>
+ <p>Total Fines Collected Month to Date: <!-- TMPL_VAR NAME="TotalFinesCollectedMonthToDate" --></p>
+
+</body>
+</html>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+</body>
+</html>
\ No newline at end of file
--
1.5.6.5
More information about the Koha-patches
mailing list