Pushed to testing.
>From 36ee5561035af335bc3c900ded7aa89066421226 Mon Sep 17 00:00:00 2001
From: Loui Chang <[EMAIL PROTECTED]>
Date: Tue, 24 Jun 2008 02:53:16 -0400
Subject: [PATCH] Move code out of index.php
Move database queries to functions and html to templates.
Signed-off-by: Loui Chang <[EMAIL PROTECTED]>
---
web/html/index.php | 182 ++--------------------------
web/lib/stats.inc | 72 +++++++++++
web/template/stats/general_stats_table.php | 38 ++++++
web/template/stats/updates_table.php | 40 ++++++
web/template/stats/user_table.php | 38 ++++++
5 files changed, 198 insertions(+), 172 deletions(-)
create mode 100644 web/lib/stats.inc
create mode 100644 web/template/stats/general_stats_table.php
create mode 100644 web/template/stats/updates_table.php
create mode 100644 web/template/stats/user_table.php
diff --git a/web/html/index.php b/web/html/index.php
index 7a95abf..ce5e009 100644
--- a/web/html/index.php
+++ b/web/html/index.php
@@ -5,66 +5,13 @@ set_include_path(get_include_path() . PATH_SEPARATOR .
'../lib' . PATH_SEPARATOR
# Add to handle the i18n of My Packages
include("pkgfuncs_po.inc");
include("aur.inc");
+include('stats.inc');
set_lang();
check_sid();
html_header( __("Home") );
-
-# Newest packages
-$q = "SELECT * FROM Packages WHERE DummyPkg != 1 ORDER BY
GREATEST(SubmittedTS,ModifiedTS) DESC LIMIT 0 , 10";
-$newest_packages = db_query($q, $dbh);
-
-# AUR statistics
-$q = "SELECT count(*) FROM Packages,PackageLocations WHERE Packages.LocationID
= PackageLocations.ID AND PackageLocations.Location = 'unsupported'";
-$result = db_query($q, $dbh);
-$row = mysql_fetch_row($result);
-$unsupported_count = $row[0];
-
-$q = "SELECT count(*) FROM Packages,PackageLocations WHERE Packages.LocationID
= PackageLocations.ID AND PackageLocations.Location = 'community'";
-$result = db_query($q, $dbh);
-$row = mysql_fetch_row($result);
-$community_count = $row[0];
-
-$q = "SELECT count(*) from Users";
-$result = db_query($q, $dbh);
-$row = mysql_fetch_row($result);
-$user_count = $row[0];
-
-$q = "SELECT count(*) from Users,AccountTypes WHERE Users.AccountTypeID =
AccountTypes.ID AND AccountTypes.AccountType = 'Trusted User'";
-$result = db_query($q, $dbh);
-$row = mysql_fetch_row($result);
-$tu_count = $row[0];
-
-$targstamp = intval(strtotime("-7 days"));
-$q = "SELECT count(*) from Packages WHERE (Packages.SubmittedTS >= $targstamp
OR Packages.ModifiedTS >= $targstamp)";
-$result = db_query($q, $dbh);
-$row = mysql_fetch_row($result);
-$update_count = $row[0];
-
-$user = username_from_sid($_COOKIE["AURSID"]);
-
-if (!empty($user)) {
- $q = "SELECT count(*) FROM Packages,PackageLocations,Users WHERE
Packages.MaintainerUID = Users.ID AND Packages.LocationID = PackageLocations.ID
AND PackageLocations.Location = 'unsupported' AND
Users.Username='".mysql_real_escape_string($user)."'";
- $result = db_query($q, $dbh);
- $row = mysql_fetch_row($result);
- $maintainer_unsupported_count = $row[0];
-
- $q = "SELECT count(*) FROM Packages,Users WHERE Packages.OutOfDate = 1 AND
Packages.MaintainerUID = Users.ID AND
Users.Username='".mysql_real_escape_string($user)."'";
- $result = db_query($q, $dbh);
- $row = mysql_fetch_row($result);
- $flagged_outdated = $row[0];
-
- # If the user is a TU calculate the number of the packages
- $atype = account_from_sid($_COOKIE["AURSID"]);
-
- if ($atype == 'Trusted User') {
- $q = "SELECT count(*) FROM Packages,PackageLocations,Users WHERE
Packages.MaintainerUID = Users.ID AND Packages.LocationID = PackageLocations.ID
AND PackageLocations.Location = 'community' AND
Users.Username='".mysql_real_escape_string($user)."'";
- $result = db_query($q, $dbh);
- $row = mysql_fetch_row($result);
- $maintainer_community_count = $row[0];
- }
-}
+$dbh = db_connect();
?>
@@ -106,127 +53,19 @@ print __( 'Contributed PKGBUILDs <b>must</b> conform to
the %hArch Packaging Sta
</tr>
<tr>
<td class='boxSoft' valign='top'>
-<table class="boxSoft">
-<tr>
-<th colspan="2" class="boxSoftTitle" style="text-align: right">
-<a href="/rss2.php"><img src="/images/rss.gif"></a> <span class="f3"><?php
print __("Recent Updates") ?><span class="f5"></span></span>
-</th>
-</tr>
-
-<?php while ($row = mysql_fetch_assoc($newest_packages)): ?>
-
-<tr>
-<td class="boxSoft">
-<span class="f4"><span class="blue"><a href="/packages.php?ID=<?php print
intval($row["ID"]); ?>">
-<?php print $row["Name"] . ' ' . $row["Version"]; ?>
-</a></span>
+<?php updates_table($dbh); ?>
</td>
-<td class="boxSoft">
-
+<td class='boxSoft' valign='top'>
<?php
-$mod_int = intval($row["ModifiedTS"]);
-$sub_int = intval($row["SubmittedTS"]);
+$user = username_from_sid($_COOKIE["AURSID"]);
+if (!empty($user)) {
+ user_table($user, $dbh);
+ echo '<br />';
+}
-if ($mod_int != 0):
- $modstring = gmdate("r", $mod_int);
-elseif ($sub_int != 0):
- $modstring = '<img src="/images/new.gif"/> ' . gmdate("r", $sub_int);
-else:
- $modstring = '(unknown)';
-endif;
+general_stats_table($dbh);
?>
-<span class="f4"><?php print $modstring; ?></span>
-</td>
-</tr>
-
-<?php endwhile; ?>
-
-</td>
-</tr>
-</table>
-<td class='boxSoft' valign='top'>
-
-<?php if (!empty($user)): ?>
-
-<table class='boxSoft'>
-<tr>
-<th colspan='2' class='boxSoftTitle'>
-<span class='f3'><?php print __("My Statistics"); ?></span>
-</th>
-</tr>
-<tr>
-<td class='boxSoft'>
-<span class='f4'><?php print __("Packages in unsupported"); ?></span>
-</td>
-<td class='boxSoft'>
-<span class='f4'><?php print $maintainer_unsupported_count; ?></span>
-</td>
-</tr>
-
-<?php if ($atype == 'Trusted User'): ?>
-
-<tr>
-<td class='boxSoft'>
-<span class='f4'><?php print __("Packages in [community]"); ?></span>
-</td>
-<td class='boxSoft'>
-<span class='f4'><?php print $maintainer_community_count; ?></span>
-</td>
-</tr>
-
-<?php endif; ?>
-
-<tr>
-<td class='boxSoft'>
-<span class='f4'><?php print __("Out-of-date"); ?></span>
-</td>
-<td class='boxSoft'>
-<span class='f4'><?php print $flagged_outdated ?></span>
-</td>
-</tr>
-</table>
-<br />
-
-<?php endif; ?>
-
-<table class='boxSoft'>
-<tr>
-<th colspan='2' class='boxSoftTitle'>
-<span class='f3'><?php print __("Statistics") ?></span>
-</th>
-</tr>
-<tr>
-<td class='boxSoft'>
-<span class='f4'><?php print __("Packages in unsupported"); ?></span>
-</td>
-<td class='boxSoft'><span class='f4'><?php print $unsupported_count;
?></span></td>
-</tr>
-<tr>
-<td class='boxSoft'>
-<span class='f4'><?php print __("Packages in [community]"); ?></span>
-</td>
-<td class='boxSoft'><span class='f4'><?php print $community_count;
?></span></td>
-</tr>
-<tr>
-<td class='boxSoft'>
-<span class='f4'><?php print __("Packages added or updated in the past 7
days"); ?></span>
-</td>
-<td class='boxSoft'><span class='f4'><?php print $update_count; ?></span></td>
-</tr>
-<tr>
-<td class='boxSoft'>
-<span class='blue'><span class='f4'><?php print __("Registered Users");
?></span></span>
-</td>
-<td class='boxSoft'><span class='f4'><?php print $user_count; ?></span></td>
-</tr>
-<tr>
-<td class='boxSoft'>
-<span class='f4'><?php print __("Trusted Users"); ?></span>
-</td>
-<td class='boxSoft'><span class='f4'><?php print $tu_count; ?></span></td>
-</tr>
-</table>
</td>
</tr>
</table>
@@ -236,4 +75,3 @@ endif;
<?php
html_footer(AUR_VERSION);
-?>
diff --git a/web/lib/stats.inc b/web/lib/stats.inc
new file mode 100644
index 0000000..e659210
--- /dev/null
+++ b/web/lib/stats.inc
@@ -0,0 +1,72 @@
+<?php
+
+include_once('aur.inc');
+
+function updates_table($dbh)
+{
+ $q = 'SELECT * FROM Packages WHERE DummyPkg != 1 ORDER BY
GREATEST(SubmittedTS,ModifiedTS) DESC LIMIT 0 , 10';
+ $newest_packages = db_query($q, $dbh);
+ include('stats/updates_table.php');
+}
+
+function user_table($user, $dbh)
+{
+
+ $base_q = 'SELECT count(*) FROM Packages,PackageLocations,Users WHERE
Packages.MaintainerUID = Users.ID AND Packages.LocationID = PackageLocations.ID
AND PackageLocations.Location = "%s" AND Users.Username="' .
+ mysql_real_escape_string($user).'"';
+
+ $result = db_query(sprintf($base_q, 'unsupported'), $dbh);
+ $row = mysql_fetch_row($result);
+ $maintainer_unsupported_count = $row[0];
+
+ $q = "SELECT count(*) FROM Packages,Users WHERE Packages.OutOfDate = 1
AND Packages.MaintainerUID = Users.ID AND Users.Username='" .
+ mysql_real_escape_string($user)."'";
+
+ $result = db_query($q, $dbh);
+ $row = mysql_fetch_row($result);
+ $flagged_outdated = $row[0];
+
+ # If the user is a TU calculate the number of the packages
+ $atype = account_from_sid($_COOKIE["AURSID"]);
+
+ if ($atype == 'Trusted User') {
+ $result = db_query(sprintf($base_q, 'community'), $dbh);
+ $row = mysql_fetch_row($result);
+ $maintainer_community_count = $row[0];
+ }
+
+ include('stats/user_table.php');
+}
+
+function general_stats_table($dbh)
+{
+ # AUR statistics
+ $q = "SELECT count(*) FROM Packages,PackageLocations WHERE
Packages.LocationID = PackageLocations.ID AND PackageLocations.Location =
'unsupported'";
+ $result = db_query($q, $dbh);
+ $row = mysql_fetch_row($result);
+ $unsupported_count = $row[0];
+
+ $q = "SELECT count(*) FROM Packages,PackageLocations WHERE
Packages.LocationID = PackageLocations.ID AND PackageLocations.Location =
'community'";
+ $result = db_query($q, $dbh);
+ $row = mysql_fetch_row($result);
+ $community_count = $row[0];
+
+ $q = "SELECT count(*) from Users";
+ $result = db_query($q, $dbh);
+ $row = mysql_fetch_row($result);
+ $user_count = $row[0];
+
+ $q = "SELECT count(*) from Users,AccountTypes WHERE Users.AccountTypeID
= AccountTypes.ID AND AccountTypes.AccountType = 'Trusted User'";
+ $result = db_query($q, $dbh);
+ $row = mysql_fetch_row($result);
+ $tu_count = $row[0];
+
+ $targstamp = intval(strtotime("-7 days"));
+ $q = "SELECT count(*) from Packages WHERE (Packages.SubmittedTS >=
$targstamp OR Packages.ModifiedTS >= $targstamp)";
+ $result = db_query($q, $dbh);
+ $row = mysql_fetch_row($result);
+ $update_count = $row[0];
+
+ include('stats/general_stats_table.php');
+}
+
diff --git a/web/template/stats/general_stats_table.php
b/web/template/stats/general_stats_table.php
new file mode 100644
index 0000000..254b6b6
--- /dev/null
+++ b/web/template/stats/general_stats_table.php
@@ -0,0 +1,38 @@
+<table class='boxSoft'>
+<tr>
+<th colspan='2' class='boxSoftTitle'>
+<span class='f3'><?php print __("Statistics") ?></span>
+</th>
+</tr>
+<tr>
+<td class='boxSoft'>
+<span class='f4'><?php print __("Packages in unsupported"); ?></span>
+</td>
+<td class='boxSoft'><span class='f4'><?php print $unsupported_count;
?></span></td>
+</tr>
+<tr>
+<td class='boxSoft'>
+<span class='f4'><?php print __("Packages in [community]"); ?></span>
+</td>
+<td class='boxSoft'><span class='f4'><?php print $community_count;
?></span></td>
+</tr>
+<tr>
+<td class='boxSoft'>
+<span class='f4'><?php print __("Packages added or updated in the past 7
days"); ?></span>
+</td>
+<td class='boxSoft'><span class='f4'><?php print $update_count; ?></span></td>
+</tr>
+<tr>
+<td class='boxSoft'>
+<span class='blue'><span class='f4'><?php print __("Registered Users");
?></span></span>
+</td>
+<td class='boxSoft'><span class='f4'><?php print $user_count; ?></span></td>
+</tr>
+<tr>
+<td class='boxSoft'>
+<span class='f4'><?php print __("Trusted Users"); ?></span>
+</td>
+<td class='boxSoft'><span class='f4'><?php print $tu_count; ?></span></td>
+</tr>
+</table>
+
diff --git a/web/template/stats/updates_table.php
b/web/template/stats/updates_table.php
new file mode 100644
index 0000000..e1eb888
--- /dev/null
+++ b/web/template/stats/updates_table.php
@@ -0,0 +1,40 @@
+<table class="boxSoft">
+<tr>
+<th colspan="2" class="boxSoftTitle" style="text-align: right">
+<a href="rss2.php"><img src="images/rss.gif"></a>
+<span class="f3"><?php print __("Recent Updates") ?><span
class="f5"></span></span>
+</th>
+</tr>
+
+<?php while ($row = mysql_fetch_assoc($newest_packages)): ?>
+
+<tr>
+<td class="boxSoft">
+<span class="f4"><span class="blue">
+<a href="packages.php?ID=<?php print intval($row["ID"]); ?>">
+<?php print $row["Name"] . ' ' . $row["Version"]; ?>
+</a></span>
+</td>
+<td class="boxSoft">
+
+<?php
+$mod_int = intval($row["ModifiedTS"]);
+$sub_int = intval($row["SubmittedTS"]);
+
+if ($mod_int != 0):
+ $modstring = gmdate("r", $mod_int);
+elseif ($sub_int != 0):
+ $modstring = '<img src="images/new.gif" /> ' . gmdate("r", $sub_int);
+else:
+ $modstring = '(unknown)';
+endif;
+?>
+
+<span class="f4"><?php print $modstring; ?></span>
+</td>
+</tr>
+
+<?php endwhile; ?>
+
+</table>
+
diff --git a/web/template/stats/user_table.php
b/web/template/stats/user_table.php
new file mode 100644
index 0000000..b844662
--- /dev/null
+++ b/web/template/stats/user_table.php
@@ -0,0 +1,38 @@
+<table class='boxSoft'>
+<tr>
+<th colspan='2' class='boxSoftTitle'>
+<span class='f3'><?php print __("My Statistics"); ?></span>
+</th>
+</tr>
+<tr>
+<td class='boxSoft'>
+<span class='f4'><?php print __("Packages in unsupported"); ?></span>
+</td>
+<td class='boxSoft'>
+<span class='f4'><?php print $maintainer_unsupported_count; ?></span>
+</td>
+</tr>
+
+<?php if ($atype == 'Trusted User'): ?>
+
+<tr>
+<td class='boxSoft'>
+<span class='f4'><?php print __("Packages in [community]"); ?></span>
+</td>
+<td class='boxSoft'>
+<span class='f4'><?php print $maintainer_community_count; ?></span>
+</td>
+</tr>
+
+<?php endif; ?>
+
+<tr>
+<td class='boxSoft'>
+<span class='f4'><?php print __("Out-of-date"); ?></span>
+</td>
+<td class='boxSoft'>
+<span class='f4'><?php print $flagged_outdated ?></span>
+</td>
+</tr>
+</table>
+
--
1.5.6.2
