--- a
+++ b/kohaReports/trunk/dailyReport/dailyReport.php
@@ -0,0 +1,221 @@
+<?php
+## Copyright 2006 Kyle Hall
+
+## This file is part of koha-tools.
+
+## koha-tools 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; either version 2 of the License, or
+## (at your option) any later version.
+
+## koha-tools 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-tools; if not, write to the Free Software
+## Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
+
+//******************* Daily Report ***********************//
+require_once("../classes/Template.class.php");
+require_once("../classes/MySQLConnectionFactory.class.php");
+
+$dbh = MySQLConnectionFactory::create();
+
+$library = mysql_real_escape_string($HTTP_GET_VARS['library']);
+
+$today = date('l dS \of F Y');
+
+$time = date('h:i:s A');
+
+//Get the name of the library from the database.
+$sql = 'SELECT * FROM branches WHERE branches.branchcode = \'' . $library . '\'';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$libraryName = $row['branchname'];
+
+//Get Total number of material items in the library
+$sql = 'SELECT COUNT(*) as itemsCount FROM items WHERE holdingbranch = \'' . $library . '\'';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$itemsCount = $row['itemsCount'];
+
+//Get the number of said items that are currently marked 'Lost'
+$sql = 'SELECT COUNT(*) AS lostItemsCount FROM `items` WHERE (`itemlost` = 1 AND holdingbranch = \'' . $library . '\')';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$lostItemsCount = $row['lostItemsCount'];
+
+//Get the total number of patrons
+ $sql = 'SELECT COUNT(*) as borrowersCount FROM `borrowers` WHERE `branchcode` LIKE \'' . $library. '\'';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$borrowersCount = $row['borrowersCount'];
+
+//Get the number of items currently checked out
+$sql = 'SELECT COUNT(*) as itemsInCirculation FROM `issues`, `items` '
+        . ' WHERE ('
+        . ' issues.itemnumber = items.itemnumber '
+        . ' AND'
+        . ' issues.return IS NULL'
+        . ' AND'
+        . ' items.holdingbranch = \''. $library . '\''
+        . ' )';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$itemsInCirculation = $row['itemsInCirculation'];
+
+//Get the number of patrons that currently have items checked out
+ $sql = 'SELECT COUNT(DISTINCT borrowers.borrowernumber) AS borrowersWithIssues '
+        . ' FROM `issues`, `borrowers` '
+        . ' WHERE ('
+        . ' issues.borrowernumber = borrowers.borrowernumber'
+        . ' AND'
+        . ' issues.return IS NULL'
+        . ' AND'
+        . ' borrowers.branchcode LIKE \''. $library . '\''
+        . ' )';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$borrowersWithIssues = $row['borrowersWithIssues'];
+
+//Get the number of items currently overdue
+$sql = 'SELECT COUNT(DISTINCT issues.itemnumber) as overdueItemsCount '
+        . ' FROM issues, accountlines, borrowers'
+        . ' WHERE ('
+        . ' issues.borrowernumber = accountlines.borrowernumber'
+        . ' AND'
+        . ' issues.borrowernumber = borrowers.borrowernumber'
+        . ' AND'
+        . ' borrowers.branchcode LIKE \''. $library . '\''
+        . ' AND'
+        . ' issues.return IS NULL'
+        . ' AND'
+        . ' accountlines.amountoutstanding > 0'
+        . ' )';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$overdueItemsCount = $row['overdueItemsCount'];
+
+//Get the number of borrowers with overdues
+$sql = 'SELECT COUNT(DISTINCT issues.borrowernumber) as borrowersWithOverdues '
+        . ' FROM issues, accountlines, borrowers'
+        . ' WHERE ('
+        . ' issues.borrowernumber = accountlines.borrowernumber'
+        . ' AND'
+        . ' issues.borrowernumber = borrowers.borrowernumber'
+        . ' AND'
+        . ' borrowers.branchcode LIKE \''. $library . '\''
+        . ' AND'
+        . ' issues.return IS NULL'
+        . ' AND'
+        . ' accountlines.amountoutstanding > 0'
+        . ' )';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$borrowersWithOverdues = $row['borrowersWithOverdues'];
+
+//Get the number of checkouts today
+$today = date("Y-m-d");
+$sql = 'SELECT COUNT(*) as issuesToday FROM `statistics`, `items` WHERE (statistics.type = \'issue\' AND statistics.itemnumber = items.itemnumber AND items.holdingbranch = \''. $lirbary . '\' AND statistics.datetime LIKE \'' . $today . '%\')';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$issuesToday = $row['issuesToday'];
+
+//Get the number of reserve requests today
+$today = date("Y-m-d");
+$sql = 'SELECT COUNT(*) as reservesToday FROM `reserves` WHERE (reserves.branchcode = \''. $library . '\' AND reservedate LIKE \'' . $today . '\')';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$reservesToday = $row['reservesToday'];
+
+//Get the number of borrowers making reserves today
+$today = date("Y-m-d");
+$sql = 'SELECT COUNT(DISTINCT borrowernumber) as borrowersMakingReserves FROM `reserves` WHERE (reserves.branchcode = \''. $library . '\' AND reservedate LIKE \'' . $today . '\')';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$borrowersMakingReserves = $row['borrowersMakingReserves'];
+
+//Get the number of items put on reserve today
+$today = date("Y-m-d");
+$sql = 'SELECT COUNT(DISTINCT reserves.biblionumber) as itemsReserved FROM `reserves` WHERE (reserves.branchcode = \''. $library . '\' AND reservedate LIKE \'' . $today . '\')';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$itemsReserved = $row['itemsReserved'];
+
+//Get the number of borrowers with outstanding fines
+$sql = 'SELECT COUNT(DISTINCT accountlines.borrowernumber) AS borrowersWithFines FROM accountlines, borrowers'
+        . ' WHERE ('
+        . ' accountlines.borrowernumber = borrowers.borrowernumber'
+        . ' AND'
+        . ' accountlines.amountoutstanding > 0'
+        . ' AND'
+        . ' borrowers.branchcode LIKE \''. $library . '\''
+        . ' )';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$borrowersWithFines = $row['borrowersWithFines'];
+
+//Get the total amount of fines outstanding
+$sql = 'SELECT SUM(amountoutstanding) as finesOutstanding from accountlines';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$finesOutstanding = $row['finesOutstanding'];
+
+//Get the total number of checkouts this month
+$thisYear = date("Y");
+$thisMonth = date("m");
+$sql = 'SELECT COUNT(*) as issuesThisMonth FROM statistics WHERE (statistics.type = \'issue\' AND statistics.datetime LIKE \'' . $thisYear . '-' . $thisMonth . '%\')';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$issuesThisMonth = $row['issuesThisMonth'];
+
+//Get the total number of fines collected today
+$date = date("Ymd");
+$sql = 'SELECT SUM(accountlines.amount) as finesCollectedToday FROM accountlines WHERE (accountlines.accounttype = \'PAY\' AND accountlines.timestamp LIKE \'' . $date . '%\')';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$finesCollectedToday = $row['finesCollectedToday'] * -1; //Multiply by -1 because fines collected are registered as negative amounts
+
+//Get the total amount of fines this month
+$date = date("Ym");
+$sql = 'SELECT SUM(accountlines.amount) as finesCollectedThisMonth FROM accountlines WHERE (accountlines.accounttype = \'PAY\' AND accountlines.timestamp LIKE \'' . $date . '%\')';
+$resultSet = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
+$row = mysql_fetch_array($resultSet);
+$finesCollectedThisMonth = $row['finesCollectedThisMonth'] * -1;
+
+$tpl = new Template();
+$tpl->set('library', $libraryName);
+$tpl->set('date', $today);
+$tpl->set('time', $time);
+$tpl->set('itemsCount', $itemsCount);
+$tpl->set('lostItemsCount', $lostItemsCount);
+$tpl->set('borrowersCount', $borrowersCount);
+$tpl->set('itemsInCirculation', $itemsInCirculation);
+$tpl->set('borrowersWithIssues', $borrowersWithIssues);
+$tpl->set('overdueItemsCount', $overdueItemsCount);
+$tpl->set('borrowersWithOverdues', $borrowersWithOverdues);
+$tpl->set('issuesToday', $issuesToday);
+$tpl->set('reservesToday', $reservesToday);
+$tpl->set('borrowersMakingReserves', $borrowersMakingReserves);
+if ($borrowersMakingReserves != 0){
+  $tpl->set('averageItemsPerBorrower', $itemsReserved / $borrowersMakingReserves);
+} else {
+  $tpl->set('averageItemsPerBorrower', 0);
+}
+$tpl->set('itemsReserved', $itemsReserved);
+if ($itemsReserved != 0) {
+  $tpl->set('averageBorrowersPerItem', $borrowersMakingReserves / $itemsReserved);
+} else {
+  $tpl->set('averageBorrowersPerItem', 0);
+}
+$tpl->set('borrowersWithFines', $borrowersWithFines);
+$tpl->set('finesOutstanding', number_format($finesOutstanding, 2));
+$tpl->set('issuesThisMonth', $issuesThisMonth);
+$tpl->set('finesCollectedToday', number_format($finesCollectedToday, 2));
+$tpl->set('finesCollectedThisMonth', number_format($finesCollectedThisMonth,2));
+
+echo $tpl->fetch('dailyReport.tpl');
+
+?>