Add user set timezones

Currently, aurweb displays all dates and times in UTC time. This patch
adds a capability for each logged in user to set their preferred
timezone.

Implements FS#48729.

Signed-off-by: Mark Weiman <mark.weiman@markzz.com>
Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>

19 files changed, 130 insertions, 23 deletions

diff --git a/conf/config.proto b/conf/config.proto
index 63e24583..431697e2 100644
--- a/conf/config.proto
+++ b/conf/config.proto
@@ -11,6 +11,7 @@ username_min_len = 3
 username_max_len = 16
 passwd_min_len = 8
 default_lang = en
+default_timezone = UTC
 sql_debug = 0
 max_sessions_per_user = 8
 login_timeout = 7200
diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql
index 30209bd8..13e3fd94 100644
--- a/schema/aur-schema.sql
+++ b/schema/aur-schema.sql
@@ -32,6 +32,7 @@ CREATE TABLE Users (
 	ResetKey CHAR(32) NOT NULL DEFAULT '',
 	RealName VARCHAR(64) NOT NULL DEFAULT '',
 	LangPreference VARCHAR(6) NOT NULL DEFAULT 'en',
+	Timezone VARCHAR(32) NOT NULL DEFAULT 'UTC',
 	Homepage TEXT NULL DEFAULT NULL,
 	IRCNick VARCHAR(32) NOT NULL DEFAULT '',
 	PGPKey VARCHAR(40) NULL DEFAULT NULL,
diff --git a/upgrading/4.5.0.txt b/upgrading/4.5.0.txt
new file mode 100644
index 00000000..6c4ce807
--- /dev/null
+++ b/upgrading/4.5.0.txt
@@ -0,0 +1,5 @@
+1. Add Timezone column to Users:
+
+---
+ALTER TABLE Users ADD COLUMN Timezone VARCHAR(32) NOT NULL DEFAULT 'UTC';
+---
\ No newline at end of file
diff --git a/web/html/account.php b/web/html/account.php
index 2892f046..91e57038 100644
--- a/web/html/account.php
+++ b/web/html/account.php
@@ -34,6 +34,7 @@ if ($action == "UpdateAccount") {
 			in_request("U"), in_request("T"), in_request("S"),
 			in_request("E"), in_request("H"), in_request("P"),
 			in_request("C"), in_request("R"), in_request("L"),
+			in_request("TZ"),
 			in_request("HP"), in_request("I"), in_request("K"),
 			in_request("PK"), in_request("J"), in_request("CN"),
 			in_request("UN"), in_request("ON"), in_request("ID"),
@@ -89,6 +90,7 @@ if (isset($_COOKIE["AURSID"])) {
 					"",
 					$row["RealName"],
 					$row["LangPreference"],
+					$row["Timezone"],
 					$row["Homepage"],
 					$row["IRCNick"],
 					$row["PGPKey"],
@@ -141,6 +143,7 @@ if (isset($_COOKIE["AURSID"])) {
 				in_request("C"),
 				in_request("R"),
 				in_request("L"),
+				in_request("TZ"),
 				in_request("HP"),
 				in_request("I"),
 				in_request("K"),
diff --git a/web/html/register.php b/web/html/register.php
index 6c6d52e6..843fea97 100644
--- a/web/html/register.php
+++ b/web/html/register.php
@@ -31,6 +31,7 @@ if (in_request("Action") == "NewAccount") {
 		'',
 		in_request("R"),
 		in_request("L"),
+		in_request("TZ"),
 		in_request("HP"),
 		in_request("I"),
 		in_request("K"),
@@ -53,6 +54,7 @@ if (in_request("Action") == "NewAccount") {
 			'',
 			in_request("R"),
 			in_request("L"),
+			in_request("TZ"),
 			in_request("HP"),
 			in_request("I"),
 			in_request("K"),
diff --git a/web/html/voters.php b/web/html/voters.php
index 8833be1e..997186d8 100644
--- a/web/html/voters.php
+++ b/web/html/voters.php
@@ -20,7 +20,7 @@ if (has_credential(CRED_PKGBASE_LIST_VOTERS)):
 			<li>
 				<a href="<?= get_user_uri($row['Username']); ?>"><?= htmlspecialchars($row['Username']) ?></a>
 				<?php if ($row["VoteTS"] > 0): ?>
-				(<?= gmdate("Y-m-d H:i", intval($row["VoteTS"])) ?>)
+				(<?= date("Y-m-d H:i", intval($row["VoteTS"])) ?>)
 				<?php endif; ?>
 			</li>
 			<?php endwhile; ?>
diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php
index 6dcf91d1..b7288143 100644
--- a/web/lib/acctfuncs.inc.php
+++ b/web/lib/acctfuncs.inc.php
@@ -1,5 +1,4 @@
 <?php
-
 /**
  * Determine if an HTTP request variable is set
  *
@@ -52,6 +51,7 @@ function html_format_pgp_fingerprint($fingerprint) {
  * @param string $C The confirmed password value of the displayed user
  * @param string $R The real name of the displayed user
  * @param string $L The language preference of the displayed user
+ * @param string $TZ The timezone preference of the displayed user
  * @param string $HP The homepage of the displayed user
  * @param string $I The IRC nickname of the displayed user
  * @param string $K The PGP key fingerprint of the displayed user
@@ -66,9 +66,13 @@ function html_format_pgp_fingerprint($fingerprint) {
  * @return void
  */
 function display_account_form($A,$U="",$T="",$S="",$E="",$H="",$P="",$C="",$R="",
-		$L="",$HP="",$I="",$K="",$PK="",$J="",$CN="",$UN="",$ON="",$UID=0,$N="") {
+		$L="",$TZ="",$HP="",$I="",$K="",$PK="",$J="",$CN="",$UN="",$ON="",$UID=0,$N="") {
 	global $SUPPORTED_LANGS;
 
+	if ($TZ == "") {
+		$TZ = config_get("options", "default_timezone");
+	}
+
 	include("account_edit_form.php");
 	return;
 }
@@ -88,6 +92,7 @@ function display_account_form($A,$U="",$T="",$S="",$E="",$H="",$P="",$C="",$R=""
  * @param string $C The confirmed password for the user
  * @param string $R The real name of the user
  * @param string $L The language preference of the user
+ * @param string $TZ The timezone preference of the user
  * @param string $HP The homepage of the displayed user
  * @param string $I The IRC nickname of the user
  * @param string $K The PGP fingerprint of the user
@@ -102,7 +107,7 @@ function display_account_form($A,$U="",$T="",$S="",$E="",$H="",$P="",$C="",$R=""
  * @return array Boolean indicating success and message to be printed
  */
 function process_account_form($TYPE,$A,$U="",$T="",$S="",$E="",$H="",$P="",$C="",
-		$R="",$L="",$HP="",$I="",$K="",$PK="",$J="",$CN="",$UN="",$ON="",$UID=0,$N="") {
+		$R="",$L="",$TZ="",$HP="",$I="",$K="",$PK="",$J="",$CN="",$UN="",$ON="",$UID=0,$N="") {
 	global $SUPPORTED_LANGS;
 
 	$error = '';
@@ -200,6 +205,9 @@ function process_account_form($TYPE,$A,$U="",$T="",$S="",$E="",$H="",$P="",$C=""
 	if (!$error && !array_key_exists($L, $SUPPORTED_LANGS)) {
 		$error = __("Language is not currently supported.");
 	}
+	if (!$error && !array_key_exists($TZ, generate_timezone_list())) {
+		$error = __("Timezone is not currently supported.");
+	}
 	if (!$error) {
 		/*
 		 * Check whether the user name is available.
@@ -278,13 +286,14 @@ function process_account_form($TYPE,$A,$U="",$T="",$S="",$E="",$H="",$P="",$C=""
 		$salt = $dbh->quote($salt);
 		$R = $dbh->quote($R);
 		$L = $dbh->quote($L);
+		$TZ = $dbh->quote($TZ);
 		$HP = $dbh->quote($HP);
 		$I = $dbh->quote($I);
 		$K = $dbh->quote(str_replace(" ", "", $K));
 		$q = "INSERT INTO Users (AccountTypeID, Suspended, ";
 		$q.= "InactivityTS, Username, Email, Passwd, Salt, ";
-		$q.= "RealName, LangPreference, Homepage, IRCNick, PGPKey) ";
-		$q.= "VALUES (1, 0, 0, $U, $E, $P, $salt, $R, $L, ";
+		$q.= "RealName, LangPreference, Timezone, Homepage, IRCNick, PGPKey) ";
+		$q.= "VALUES (1, 0, 0, $U, $E, $P, $salt, $R, $L, $TZ ";
 		$q.= "$HP, $I, $K)";
 		$result = $dbh->exec($q);
 		if (!$result) {
@@ -347,6 +356,7 @@ function process_account_form($TYPE,$A,$U="",$T="",$S="",$E="",$H="",$P="",$C=""
 		}
 		$q.= ", RealName = " . $dbh->quote($R);
 		$q.= ", LangPreference = " . $dbh->quote($L);
+		$q.= ", Timezone = " . $dbh->quote($TZ);
 		$q.= ", Homepage = " . $dbh->quote($HP);
 		$q.= ", IRCNick = " . $dbh->quote($I);
 		$q.= ", PGPKey = " . $dbh->quote(str_replace(" ", "", $K));
@@ -359,6 +369,13 @@ function process_account_form($TYPE,$A,$U="",$T="",$S="",$E="",$H="",$P="",$C=""
 
 		$ssh_key_result = account_set_ssh_keys($UID, $ssh_keys, $ssh_fingerprints);
 
+		if (isset($_COOKIE["AURTZ"]) && ($_COOKIE["AURTZ"] != $TZ)) {
+			/* set new cookie for timezone */
+			$timeout = intval(config_get("options", "persistent_cookie_timeout"));
+			$cookie_time = time() + $timeout;
+			setcookie("AURTZ", $TZ, $cookie_time, "/");
+		}
+
 		if ($result === false || $ssh_key_result === false) {
 			$message = __("No changes were made to the account, %s%s%s.",
 					"<strong>", htmlspecialchars($U,ENT_QUOTES), "</strong>");
diff --git a/web/lib/aur.inc.php b/web/lib/aur.inc.php
index 67dd1c14..94a38499 100644
--- a/web/lib/aur.inc.php
+++ b/web/lib/aur.inc.php
@@ -18,6 +18,9 @@ include_once("cachefuncs.inc.php");
 include_once("confparser.inc.php");
 include_once("credentials.inc.php");
 
+include_once('timezone.inc.php');
+set_tz();
+
 /**
  * Check if a visitor is logged in
  *
diff --git a/web/lib/pkgreqfuncs.inc.php b/web/lib/pkgreqfuncs.inc.php
index e4556a3f..7a171ed4 100644
--- a/web/lib/pkgreqfuncs.inc.php
+++ b/web/lib/pkgreqfuncs.inc.php
@@ -172,7 +172,7 @@ function pkgreq_file($ids, $type, $merge_into, $comments) {
 		 * maintainer will not be included in the Cc list of the
 		 * request notification email.
 		 */
-		$out_of_date_time = gmdate("Y-m-d", intval($details["OutOfDateTS"]));
+		$out_of_date_time = date("Y-m-d", intval($details["OutOfDateTS"]));
 		pkgreq_close($request_id, "accepted",
 			     "The package base has been flagged out-of-date " .
 			     "since " . $out_of_date_time . ".", true);
diff --git a/web/lib/timezone.inc.php b/web/lib/timezone.inc.php
new file mode 100644
index 00000000..9fb24331
--- /dev/null
+++ b/web/lib/timezone.inc.php
@@ -0,0 +1,60 @@
+<?php
+set_include_path(get_include_path() . PATH_SEPARATOR . '../lib');
+
+/**
+ * Generate an associative of the PHP timezones and display text.
+ *
+ * @return array PHP Timezone => Displayed Description
+ */
+function generate_timezone_list() {
+	$php_timezones = DateTimeZone::listIdentifiers(DateTimeZone::ALL);
+
+	$offsets = array();
+	foreach ($php_timezones as $timezone) {
+		$tz = new DateTimeZone($timezone);
+		$offset = $tz->getOffset(new DateTime());
+		$offsets[$timezone] = "(UTC" . ($offset < 0 ? "-" : "+") . gmdate("H:i", abs($offset)) .
+			") " . $timezone;
+	}
+
+	asort($offsets);
+	return $offsets;
+}
+
+/**
+ * Set the timezone for the user.
+ *
+ * @return null
+ */
+function set_tz() {
+	$timezones = generate_timezone_list();
+	$update_cookie = false;
+
+	if (isset($_COOKIE["AURTZ"])) {
+		$timezone = $_COOKIE["AURTZ"];
+	} elseif (isset($_COOKIE["AURSID"])) {
+		$dbh = DB::connect();
+		$q = "SELECT Timezone FROM Users, Sessions ";
+		$q .= "WHERE Users.ID = Sessions.UsersID ";
+		$q .= "AND Sessions.SessionID = ";
+		$q .= $dbh->quote($_COOKIE["AURSID"]);
+		$result = $dbh->query($q);
+
+		if ($result) {
+			$timezone = $result->fetchColumn(0);
+		}
+
+		$update_cookie = true;
+	}
+
+	if (!isset($timezone) || !array_key_exists($timezone, $timezones)) {
+		$timezone = config_get("options", "default_timezone");
+	}
+	date_default_timezone_set($timezone);
+
+	if ($update_cookie) {
+		$timeout = intval(config_get("options", "persistent_cookie_timeout"));
+		$cookie_time = time() + $timeout;
+		setcookie("AURTZ", $timezone, $cookie_time, "/");
+	}
+}
diff --git a/web/template/account_edit_form.php b/web/template/account_edit_form.php
index 19821a0b..17c9d14e 100644
--- a/web/template/account_edit_form.php
+++ b/web/template/account_edit_form.php
@@ -129,6 +129,21 @@
 ?>
 			</select>
 		</p>
+		<p>
+			<label for="id_timezone"><?= __("Timezone") ?></label>
+			<select name="TZ" id="id_timezone">
+<?php
+	$timezones = generate_timezone_list();
+	while (list($key, $val) = each($timezones)) {
+		if ($TZ == $key) {
+			print "<option value=\"".$key."\" selected=\"selected\"> ".$val."</option>\n";
+		} else {
+			print "<option value=\"".$key."\"> ".$val."</option>\n";
+		}
+	}
+?>
+			</select>
+		</p>
 	</fieldset>
 
 	<fieldset>
diff --git a/web/template/flag_comment.php b/web/template/flag_comment.php
index 36af43ea..e8855fe8 100644
--- a/web/template/flag_comment.php
+++ b/web/template/flag_comment.php
@@ -5,7 +5,7 @@
 			<?= __('%s%s%s flagged %s%s%s out-of-date on %s%s%s for the following reason:',
 				'<strong>', html_format_username($message['Username']), '</strong>',
 				'<strong>', htmlspecialchars($pkgbase_name), '</strong>',
-				'<strong>', gmdate('Y-m-d', $message['OutOfDateTS']), '</strong>'); ?>
+				'<strong>', date('Y-m-d', $message['OutOfDateTS']), '</strong>'); ?>
 		<?php else: ?>
 			<?= __('%s%s%s is not flagged out-of-date.',
 				'<strong>', htmlspecialchars($pkgbase_name), '</strong>'); ?>
diff --git a/web/template/pkg_comments.php b/web/template/pkg_comments.php
index a28e41b0..fee1898c 100644
--- a/web/template/pkg_comments.php
+++ b/web/template/pkg_comments.php
@@ -17,7 +17,7 @@ if (!isset($count)) {
 
 	<?php while (list($indx, $row) = each($comments)): ?>
 		<?php
-		$date_fmtd = gmdate('Y-m-d H:i', $row['CommentTS']);
+		$date_fmtd = date('Y-m-d H:i', $row['CommentTS']);
 		if ($row['UserName']) {
 			$user_fmtd = html_format_username($row['UserName']);
 			$heading = __('%s commented on %s', $user_fmtd, $date_fmtd);
@@ -30,7 +30,7 @@ if (!isset($count)) {
 		$is_pinned = $row['PinnedTS'];
 
 		if ($uid && $is_deleted) {
-			$date_fmtd = gmdate('Y-m-d H:i', $row['DelTS']);
+			$date_fmtd = date('Y-m-d H:i', $row['DelTS']);
 			$heading .= ' <span class="edited">(';
 			if ($row['DelUserName']) {
 				$user_fmtd = html_format_username($row['DelUserName']);
@@ -40,7 +40,7 @@ if (!isset($count)) {
 			}
 			$heading .= ')</span>';
 		} elseif ($uid && $is_edited) {
-			$date_fmtd = gmdate('Y-m-d H:i', $row['EditedTS']);
+			$date_fmtd = date('Y-m-d H:i', $row['EditedTS']);
 			$heading .= ' <span class="edited">(';
 			if ($row['EditUserName']) {
 				$user_fmtd = html_format_username($row['EditUserName']);
diff --git a/web/template/pkg_details.php b/web/template/pkg_details.php
index b9c66d47..32693948 100644
--- a/web/template/pkg_details.php
+++ b/web/template/pkg_details.php
@@ -34,9 +34,9 @@ $msg = __('unknown');
 $license = empty($row['License']) ? $msg : $row['License'];
 
 # Print the timestamps for last updates
-$updated_time = ($row["ModifiedTS"] == 0) ? $msg : gmdate("Y-m-d H:i", intval($row["ModifiedTS"]));
-$submitted_time = ($row["SubmittedTS"] == 0) ? $msg : gmdate("Y-m-d H:i", intval($row["SubmittedTS"]));
-$out_of_date_time = ($row["OutOfDateTS"] == 0) ? $msg : gmdate("Y-m-d", intval($row["OutOfDateTS"]));
+$updated_time = ($row["ModifiedTS"] == 0) ? $msg : date("Y-m-d H:i", intval($row["ModifiedTS"]));
+$submitted_time = ($row["SubmittedTS"] == 0) ? $msg : date("Y-m-d H:i", intval($row["SubmittedTS"]));
+$out_of_date_time = ($row["OutOfDateTS"] == 0) ? $msg : date("Y-m-d", intval($row["OutOfDateTS"]));
 
 $lics = pkg_licenses($row["ID"]);
 $grps = pkg_groups($row["ID"]);
diff --git a/web/template/pkgbase_details.php b/web/template/pkgbase_details.php
index 1012c4e6..fe769596 100644
--- a/web/template/pkgbase_details.php
+++ b/web/template/pkgbase_details.php
@@ -31,9 +31,9 @@ $popularity = $row['Popularity'];
 $msg = __('unknown');
 
 # Print the timestamps for last updates
-$updated_time = ($row["ModifiedTS"] == 0) ? $msg : gmdate("Y-m-d H:i", intval($row["ModifiedTS"]));
-$submitted_time = ($row["SubmittedTS"] == 0) ? $msg : gmdate("Y-m-d H:i", intval($row["SubmittedTS"]));
-$out_of_date_time = ($row["OutOfDateTS"] == 0) ? $msg : gmdate("Y-m-d", intval($row["OutOfDateTS"]));
+$updated_time = ($row["ModifiedTS"] == 0) ? $msg : date("Y-m-d H:i", intval($row["ModifiedTS"]));
+$submitted_time = ($row["SubmittedTS"] == 0) ? $msg : date("Y-m-d H:i", intval($row["SubmittedTS"]));
+$out_of_date_time = ($row["OutOfDateTS"] == 0) ? $msg : date("Y-m-d", intval($row["OutOfDateTS"]));
 
 $pkgs = pkgbase_get_pkgnames($base_id);
 
diff --git a/web/template/pkgreq_results.php b/web/template/pkgreq_results.php
index b27963be..426c7f08 100644
--- a/web/template/pkgreq_results.php
+++ b/web/template/pkgreq_results.php
@@ -67,7 +67,7 @@
 			<td>
 			<a href="<?= get_uri('/account/') . htmlspecialchars($row['User'], ENT_QUOTES) ?>" title="<?= __('View account information for %s', htmlspecialchars($row['User'])) ?>"><?= htmlspecialchars($row['User']) ?></a>
 			</td>
-			<td<?php if ($due): ?> class="flagged"<?php endif; ?>><?= gmdate("Y-m-d H:i", intval($row['RequestTS'])) ?></td>
+			<td<?php if ($due): ?> class="flagged"<?php endif; ?>><?= date("Y-m-d H:i", intval($row['RequestTS'])) ?></td>
 			<?php if ($row['Open']): ?>
 			<td>
 				<?php if ($row['BaseID']): ?>
diff --git a/web/template/stats/updates_table.php b/web/template/stats/updates_table.php
index 580583b5..b4c6215f 100644
--- a/web/template/stats/updates_table.php
+++ b/web/template/stats/updates_table.php
@@ -10,7 +10,7 @@
 				<a href="<?= get_pkg_uri($row["Name"]); ?>" title="<?= htmlspecialchars($row["Name"]) . ' ' . htmlspecialchars($row["Version"]); ?>"><?= htmlspecialchars($row["Name"]) . ' ' . htmlspecialchars($row["Version"]); ?></a>
 			</td>
 			<td class="pkg-date">
-				<span><?= gmdate("Y-m-d H:i", intval($row["ModifiedTS"])); ?></span>
+				<span><?= date("Y-m-d H:i", intval($row["ModifiedTS"])); ?></span>
 			</td>
 		</tr>
 		<?php endforeach; ?>
diff --git a/web/template/tu_details.php b/web/template/tu_details.php
index 38f6c0d0..d739060d 100644
--- a/web/template/tu_details.php
+++ b/web/template/tu_details.php
@@ -39,10 +39,10 @@ if ($yes > $active_tus / 2) {
 			<?php endif; ?>
 		</strong>
 		<br />
-		<?= __("Submitted: %s by %s", gmdate("Y-m-d H:i", $row['Submitted']), html_format_username(username_from_id($row['SubmitterID']))) ?>
+		<?= __("Submitted: %s by %s", date("Y-m-d H:i", $row['Submitted']), html_format_username(username_from_id($row['SubmitterID']))) ?>
 		<br />
 		<?= __("End") ?>:
-		<strong><?= gmdate("Y-m-d H:i", $row['End']) ?></strong>
+		<strong><?= date("Y-m-d H:i", $row['End']) ?></strong>
 		<?php if ($isrunning == 0): ?>
 		<br />
 		<?= __("Result") ?>:
diff --git a/web/template/tu_list.php b/web/template/tu_list.php
index b3e1073a..b7253f98 100644
--- a/web/template/tu_list.php
+++ b/web/template/tu_list.php
@@ -38,8 +38,8 @@
 				<td><?php $row["Agenda"] = htmlspecialchars(substr($row["Agenda"], 0, $prev_Len)); ?>
 					<a href="<?= get_uri('/tu/'); ?>?id=<?= $row['ID'] ?>"><?= $row["Agenda"] ?></a>
 				</td>
-				<td><?= gmdate("Y-m-d", $row["Submitted"]) ?></td>
-				<td><?= gmdate("Y-m-d", $row["End"]) ?></td>
+				<td><?= date("Y-m-d", $row["Submitted"]) ?></td>
+				<td><?= date("Y-m-d", $row["End"]) ?></td>
 				<td>
 				<?php if (!empty($row['User'])): ?>
 					<a href="<?= get_uri('/packages/'); ?>?K=<?= $row['User'] ?>&amp;SeB=m"><?= $row['User'] ?></a>