From 9ffc1df372018525e7626f9e11cd429f1bd28f8a Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Bernhard=20Fr=C3=B6hlich?= Date: Tue, 23 Aug 2011 23:08:20 +0200 Subject: Fix proposed by Uli --- pages/account/43.php | 78 ++++++++++++++++++++++++++++++++++++++++++++++++---- 1 file changed, 72 insertions(+), 6 deletions(-) mode change 100644 => 100755 pages/account/43.php (limited to 'pages/account/43.php') diff --git a/pages/account/43.php b/pages/account/43.php old mode 100644 new mode 100755 index a286ec6..6be9d2a --- a/pages/account/43.php +++ b/pages/account/43.php @@ -178,7 +178,7 @@ - : + : @@ -318,15 +318,79 @@
+ + + + + + + + + 0) { +?> + + + + + + 0) { +?> + + + + + + + + + + 0) { +?> + + + + + + + + + + +
:
:
:
:
:
+
+ + + - + + @@ -345,6 +409,7 @@ $points += $drow['points']; ?> + @@ -355,7 +420,7 @@ - + @@ -371,9 +436,10 @@ ?>
::  
- + + @@ -392,6 +458,7 @@ $points += $drow['points']; ?> + @@ -402,7 +469,7 @@ - + @@ -414,4 +481,3 @@

- -- cgit v1.2.1 From 76fe6db23eea88d836c3eb534abda8d87f873a67 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Bernhard=20Fr=C3=B6hlich?= Date: Mon, 19 Sep 2011 22:18:41 +0200 Subject: Revert "Fix proposed by Uli" This reverts commit 9ffc1df372018525e7626f9e11cd429f1bd28f8a. --- pages/account/43.php | 78 ++++------------------------------------------------ 1 file changed, 6 insertions(+), 72 deletions(-) mode change 100755 => 100644 pages/account/43.php (limited to 'pages/account/43.php') diff --git a/pages/account/43.php b/pages/account/43.php old mode 100755 new mode 100644 index 6be9d2a..a286ec6 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -178,7 +178,7 @@ - + @@ -318,79 +318,15 @@
- -
::  
::
- - - - - - - 0) { -?> - - - - - - 0) { -?> - - - - - - - - - - 0) { -?> - - - - - - - - - - -
:
:
:
:
:
-
- - - - + - @@ -409,7 +345,6 @@ $points += $drow['points']; ?> - @@ -420,7 +355,7 @@ - + @@ -436,10 +371,9 @@ ?>
::  
- + - @@ -458,7 +392,6 @@ $points += $drow['points']; ?> - @@ -469,7 +402,7 @@ - + @@ -481,3 +414,4 @@

+ -- cgit v1.2.1 From a182cb4e884cc9e6a004a0a087e2a0644dcea3b7 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Bernhard=20Fr=C3=B6hlich?= Date: Mon, 19 Sep 2011 22:27:36 +0200 Subject: Re-distributed patch from https://bugs.cacert.org/view.php?id=794#c2352 --- pages/account/43.php | 160 +++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 160 insertions(+) (limited to 'pages/account/43.php') diff --git a/pages/account/43.php b/pages/account/43.php index a286ec6..0d9ec11 100644 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -318,6 +318,166 @@
+ 0) { + // select domid's + $query = "select id as domids from `domains` where `memid`='".intval($row['id'])."' "; + $dres = mysql_query($query); + $rcexpired = 0; + $rcrevoked = 0; + $rcexpiremax = "0000-00-00 00:00:00"; + while ($drow = mysql_fetch_assoc($dres)) { + $ndomid = intval($drow['domids']); + + $query2 = "select COUNT(id) as dexpired from `domaincerts` where `domid`='".$ndomid."' and revoked = '0000-00-00 00:00:00' and expire < now() "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcexpired += intval($drow2['dexpired']); + + $query2 = "select COUNT(id) as drevoked from `domaincerts` where `domid`='".$ndomid."' and revoked != '0000-00-00 00:00:00' "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcrevoked += intval($drow2['drevoked']); + + $query2 = "select expire as mexpire from `domaincerts` where `domid`='".$ndomid."' and revoked = '0000-00-00 00:00:00' order by expire desc "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcexpiremax = max($rcexpiremax,$drow2['mexpire']); + + $rcactive = intval($rctotal)-intval($rcexpired)-intval($rcrevoked); + } + } +?> +
::  
+ + + + + + + + + + + + + + + + 0) { +?> + + + + + + + + + + 0) { + $rcexpired = 0; + $rcrevoked = 0; + $rcexpiremax = "0000-00-00 00:00:00"; + + $query2 = "select COUNT(id) as eexpired from `emailcerts` where `memid`='".intval($row['id'])."' and revoked = '0000-00-00 00:00:00' and expire < now() "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcexpired = intval($drow2['dexpired']); + + $query2 = "select COUNT(id) as erevoked from `emailcerts` where `memid`='".intval($row['id'])."' and revoked != '0000-00-00 00:00:00' "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcrevoked = intval($drow2['erevoked']); + + $query2 = "select expire as eexpire from `emailcerts` where `memid`='".intval($row['id'])."' and revoked = '0000-00-00 00:00:00' order by expire desc "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcexpiremax = $drow2['eexpire']; + + $rcactive = intval($rctotal)-intval($rcexpired)-intval($rcrevoked); + +?> + + + + + + + + + + + + + + 0) { + $rcexpired = 0; + $rcexpiremax = "0000-00-00 00:00:00"; + + $query2 = "select COUNT(id) as gexpired from `gpg` where `memid`='".intval($row['id'])."' and expire < now() "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcexpired = intval($drow2['gexpired']); + +/* + $query2 = "select COUNT(id) as erevoked from `gpg` where `memid`='".intval($row['id'])."' and revoked != '0000-00-00 00:00:00' "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcrevoked = intval($drow2['erevoked']); + */ + + $query2 = "select expire as gexpire from `gpg` where `memid`='".intval($row['id'])."' order by expire desc "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcexpiremax = $drow2['gexpire']; + + $rcactive = intval($rctotal)-intval($rcexpired); +?> + + + + + + + + + + + + + + +
:
:0)?"Pending":" ") ?>
:0)?"Pending":" ") ?>
:
: 0)?"Pending":" ") ?>
:
+
+ + + -- cgit v1.2.1 From 1ca33e5eb5b678767d7cbcef5d55b758b7c71452 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Bernhard=20Fr=C3=B6hlich?= Date: Mon, 19 Sep 2011 23:03:31 +0200 Subject: Expiry dates of revoked certificates count as well for "Latest Expire" --- pages/account/43.php | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'pages/account/43.php') diff --git a/pages/account/43.php b/pages/account/43.php index 0d9ec11..425add5 100644 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -348,7 +348,8 @@ $drow2 = mysql_fetch_assoc($dres2); $rcrevoked += intval($drow2['drevoked']); - $query2 = "select expire as mexpire from `domaincerts` where `domid`='".$ndomid."' and revoked = '0000-00-00 00:00:00' order by expire desc "; + // For Arbitration purpose expiry dates of revoked certs are also relevant! + $query2 = "select expire as mexpire from `domaincerts` where `domid`='".$ndomid."' order by expire desc "; $dres2 = mysql_query($query2); $drow2 = mysql_fetch_assoc($dres2); $rcexpiremax = max($rcexpiremax,$drow2['mexpire']); @@ -359,7 +360,7 @@ ?> - + -- cgit v1.2.1 From aca0ac8f5998232a82a0ec8fe97fbff20f45ecd0 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Bernhard=20Fr=C3=B6hlich?= Date: Mon, 19 Sep 2011 23:32:35 +0200 Subject: Backticks and last expiry of email certs --- pages/account/43.php | 26 +++++++++++++------------- 1 file changed, 13 insertions(+), 13 deletions(-) (limited to 'pages/account/43.php') diff --git a/pages/account/43.php b/pages/account/43.php index 425add5..1ecc25e 100644 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -324,13 +324,13 @@ // list total, expired, deleted, latest_expire_date ? - $query = "select COUNT(domaincerts.id) as countdomaincerts from `domains` inner join `domaincerts` on `domaincerts`.`domid` = `domains`.`id` where `memid`='".intval($row['id'])."' "; + $query = "select COUNT(`domaincerts`.`id`) as `countdomaincerts` from `domains` inner join `domaincerts` on `domaincerts`.`domid` = `domains`.`id` where `memid`='".intval($row['id'])."' "; $dres = mysql_query($query); $drow = mysql_fetch_assoc($dres); $rctotal = $drow['countdomaincerts']; if($rctotal > 0) { // select domid's - $query = "select id as domids from `domains` where `memid`='".intval($row['id'])."' "; + $query = "select `id` as `domids` from `domains` where `memid`='".intval($row['id'])."' "; $dres = mysql_query($query); $rcexpired = 0; $rcrevoked = 0; @@ -338,18 +338,18 @@ while ($drow = mysql_fetch_assoc($dres)) { $ndomid = intval($drow['domids']); - $query2 = "select COUNT(id) as dexpired from `domaincerts` where `domid`='".$ndomid."' and revoked = '0000-00-00 00:00:00' and expire < now() "; + $query2 = "select COUNT(`id`) as `dexpired` from `domaincerts` where `domid`='".$ndomid."' and `revoked` = '0000-00-00 00:00:00' and `expire` < now() "; $dres2 = mysql_query($query2); $drow2 = mysql_fetch_assoc($dres2); $rcexpired += intval($drow2['dexpired']); - $query2 = "select COUNT(id) as drevoked from `domaincerts` where `domid`='".$ndomid."' and revoked != '0000-00-00 00:00:00' "; + $query2 = "select COUNT(`id`) as `drevoked` from `domaincerts` where `domid`='".$ndomid."' and `revoked` != '0000-00-00 00:00:00' "; $dres2 = mysql_query($query2); $drow2 = mysql_fetch_assoc($dres2); $rcrevoked += intval($drow2['drevoked']); // For Arbitration purpose expiry dates of revoked certs are also relevant! - $query2 = "select expire as mexpire from `domaincerts` where `domid`='".$ndomid."' order by expire desc "; + $query2 = "select `expire` as `mexpire` from `domaincerts` where `domid`='".$ndomid."' order by `expire` desc "; $dres2 = mysql_query($query2); $drow2 = mysql_fetch_assoc($dres2); $rcexpiremax = max($rcexpiremax,$drow2['mexpire']); @@ -388,7 +388,7 @@ Date: Mon, 19 Sep 2011 23:48:08 +0200 Subject: Typo fix --- pages/account/43.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'pages/account/43.php') diff --git a/pages/account/43.php b/pages/account/43.php index 1ecc25e..0dfee1c 100644 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -400,7 +400,7 @@ $query2 = "select COUNT(`id`) as `eexpired` from `emailcerts` where `memid`='".intval($row['id'])."' and `revoked` = '0000-00-00 00:00:00' and `expire` < now() "; $dres2 = mysql_query($query2); $drow2 = mysql_fetch_assoc($dres2); - $rcexpired = intval($drow2['dexpired']); + $rcexpired = intval($drow2['eexpired']); $query2 = "select COUNT(`id`) as `erevoked` from `emailcerts` where `memid`='".intval($row['id'])."' and `revoked` != '0000-00-00 00:00:00' "; $dres2 = mysql_query($query2); -- cgit v1.2.1 From 4a60acc36346f4686ef6b3941c88b1f004c714c4 Mon Sep 17 00:00:00 2001 From: Bernhard Froehlich Date: Sat, 24 Sep 2011 20:45:43 +0200 Subject: Stripped training spaces --- pages/account/43.php | 36 ++++++++++++++++++------------------ 1 file changed, 18 insertions(+), 18 deletions(-) (limited to 'pages/account/43.php') diff --git a/pages/account/43.php b/pages/account/43.php index 0dfee1c..14cd9b0 100644 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -21,10 +21,10 @@ $assurance = mysql_escape_string(intval($_REQUEST['assurance'])); $row = 0; $res = mysql_query("select `to` from `notary` where `id`='$assurance'"); - if ($res) { + if ($res) { $row = mysql_fetch_assoc($res); } - mysql_query("delete from `notary` where `id`='$assurance'"); + mysql_query("delete from `notary` where `id`='$assurance'"); if ($row) { fix_assurer_flag($row['to']); } @@ -334,9 +334,9 @@ $dres = mysql_query($query); $rcexpired = 0; $rcrevoked = 0; - $rcexpiremax = "0000-00-00 00:00:00"; + $rcexpiremax = "0000-00-00 00:00:00"; while ($drow = mysql_fetch_assoc($dres)) { - $ndomid = intval($drow['domids']); + $ndomid = intval($drow['domids']); $query2 = "select COUNT(`id`) as `dexpired` from `domaincerts` where `domid`='".$ndomid."' and `revoked` = '0000-00-00 00:00:00' and `expire` < now() "; $dres2 = mysql_query($query2); @@ -352,11 +352,11 @@ $query2 = "select `expire` as `mexpire` from `domaincerts` where `domid`='".$ndomid."' order by `expire` desc "; $dres2 = mysql_query($query2); $drow2 = mysql_fetch_assoc($dres2); - $rcexpiremax = max($rcexpiremax,$drow2['mexpire']); + $rcexpiremax = max($rcexpiremax,$drow2['mexpire']); $rcactive = intval($rctotal)-intval($rcexpired)-intval($rcrevoked); } - } + } ?>
@@ -381,7 +381,7 @@ - + @@ -395,7 +395,7 @@ if($rctotal > 0) { $rcexpired = 0; $rcrevoked = 0; - $rcexpiremax = "0000-00-00 00:00:00"; + $rcexpiremax = "0000-00-00 00:00:00"; $query2 = "select COUNT(`id`) as `eexpired` from `emailcerts` where `memid`='".intval($row['id'])."' and `revoked` = '0000-00-00 00:00:00' and `expire` < now() "; $dres2 = mysql_query($query2); @@ -411,8 +411,8 @@ $dres2 = mysql_query($query2); $drow2 = mysql_fetch_assoc($dres2); $rcexpiremax = $drow2['eexpire']; - - $rcactive = intval($rctotal)-intval($rcexpired)-intval($rcrevoked); + + $rcactive = intval($rctotal)-intval($rcexpired)-intval($rcrevoked); ?> @@ -431,11 +431,11 @@ 0) { $rcexpired = 0; - $rcexpiremax = "0000-00-00 00:00:00"; + $rcexpiremax = "0000-00-00 00:00:00"; $query2 = "select COUNT(`id`) as `gexpired` from `gpg` where `memid`='".intval($row['id'])."' and `expire` < now() "; $dres2 = mysql_query($query2); @@ -448,14 +448,14 @@ $drow2 = mysql_fetch_assoc($dres2); $rcrevoked = intval($drow2['erevoked']); */ - + $query2 = "select `expire` as `gexpire` from `gpg` where `memid`='".intval($row['id'])."' order by `expire` desc "; $dres2 = mysql_query($query2); $drow2 = mysql_fetch_assoc($dres2); $rcexpiremax = $drow2['gexpire']; - - $rcactive = intval($rctotal)-intval($rcexpired); -?> + + $rcactive = intval($rctotal)-intval($rcexpired); +?> @@ -464,7 +464,7 @@ - + @@ -475,7 +475,7 @@ - -- cgit v1.2.1 From fca0f36f349cc25b716ce4dafde0e65def6c08c7 Mon Sep 17 00:00:00 2001 From: Bernhard Froehlich Date: Sat, 24 Sep 2011 20:58:48 +0200 Subject: Changed proposed by Uli in https://bugs.cacert.org/view.php?id=794#c2497 --- pages/account/43.php | 107 +++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 107 insertions(+) (limited to 'pages/account/43.php') diff --git a/pages/account/43.php b/pages/account/43.php index 14cd9b0..b19ee7c 100644 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -469,6 +469,113 @@ + 0) { + // user account is linked into orgs + + // $query = "select COUNT(`orgdomaincerts`.`id`) as `orgcountdomaincerts` from `orgdomains` inner join `orgdomaincerts` on `orgdomaincerts`.`orgid` = `orgdomains`.`id` where `memid`='".intval($row['id'])."' "; + $query = "SELECT count(orgdomaincerts.id) as countorgdomcerts FROM orgdomaincerts inner join orgdomains on orgdomaincerts.orgid=orgdomains.orgid inner join org on orgdomains.orgid=org.orgid where memid='".intval($row['id'])."' "; + + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $rctotal = $drow['countorgdomcerts']; + if($rctotal > 0) { + // select domid's + $query = "select `orgdomains`.`orgid` as `orgdomorgids` from `orgdomains` inner join org on orgdomains.orgid=org.orgid where memid='".intval($row['id'])."' "; + $dres = mysql_query($query); + $rcexpired = 0; + $rcrevoked = 0; + $rcexpiremax = "0000-00-00 00:00:00"; + while ($drow = mysql_fetch_assoc($dres)) { + $ndomid = intval($drow['orgdomorgids']); + + $query2 = "select COUNT(`orgdomaincerts`.`id`) as `dexpired` from `orgdomaincerts` where `orgid`='".$ndomid."' and `revoked` = '0000-00-00 00:00:00' and `expire` < now() "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcexpired += intval($drow2['dexpired']); // active, but expired + + $query2 = "select COUNT(`orgdomaincerts`.`id`) as `drevoked` from `orgdomaincerts` where `orgid`='".$ndomid."' and `revoked` != '0000-00-00 00:00:00' "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcrevoked += intval($drow2['drevoked']); // revoked + + // For Arbitration purpose expiry dates of revoked certs are also relevant! + $query2 = "select `expire` as `mexpire` from `orgdomaincerts` where `orgid`='".$ndomid."' order by `expire` desc "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcexpiremax = max($rcexpiremax,$drow2['mexpire']); + + } + $rcactive = intval($rctotal)-intval($rcexpired)-intval($rcrevoked); + } + if($rctotal > 0) { +?> + + + + + + + + + + + + + + 0) { + $rcexpired = 0; + $rcrevoked = 0; + $rcexpiremax = "0000-00-00 00:00:00"; + $query2 = "select COUNT(`orgemailcerts`.`id`) as `eexpired` from `orgemailcerts` inner join org on orgemailcerts.orgid=org.orgid where `org.memid`='".intval($row['id'])."' and `revoked` = '0000-00-00 00:00:00' and `expire` < now() "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcexpired = intval($drow2['eexpired']); + + $query2 = "select COUNT(`orgemailcerts`.`id`) as `erevoked` from `orgemailcerts` inner join org on orgemailcerts.orgid=org.orgid where `org.memid`='".intval($row['id'])."' and `revoked` != '0000-00-00 00:00:00' "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcrevoked = intval($drow2['erevoked']); + + $query2 = "select `expire` as `eexpire` from `orgemailcerts` inner join org on orgemailcerts.orgid=org.orgid where `memid`='".intval($row['id'])."' order by `expire` desc "; + $dres2 = mysql_query($query2); + $drow2 = mysql_fetch_assoc($dres2); + $rcexpiremax = $drow2['eexpire']; + + $rcactive = intval($rctotal)-intval($rcexpired)-intval($rcrevoked); + +?> + + + + + + + + + + + + + + + + + +
0)?"Pending":" ") ?>0)?"Pending":" ") ?>
:   0)?"Pending":" ") ?>
: :
:0)?"Pending":" ") ?>
:
:0)?"Pending":" ") ?>
:
:

-- cgit v1.2.1 From 36ba455961a50e0ef2231c7087c5253c4073d1bc Mon Sep 17 00:00:00 2001 From: Bernhard Froehlich Date: Sat, 24 Sep 2011 21:21:51 +0200 Subject: Added backticks --- pages/account/43.php | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) (limited to 'pages/account/43.php') diff --git a/pages/account/43.php b/pages/account/43.php index b19ee7c..dc56c36 100644 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -471,7 +471,7 @@ 0) { // select domid's - $query = "select `orgdomains`.`orgid` as `orgdomorgids` from `orgdomains` inner join org on orgdomains.orgid=org.orgid where memid='".intval($row['id'])."' "; + $query = "select `orgdomains`.`orgid` as `orgdomorgids` from `orgdomains` inner join `org` on `orgdomains`.`orgid`=`org`.`orgid` where `memid`='".intval($row['id'])."' "; $dres = mysql_query($query); $rcexpired = 0; $rcrevoked = 0; @@ -531,7 +531,7 @@ Date: Tue, 6 Dec 2011 18:30:47 +0100 Subject: bug 794: complete rewrite of the patch MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit - explicitly query for valid certs - let MySQL figure out most of the stuff (don't use loops and subqueries in those loops and so on) - minor improvements Signed-off-by: Michael Tänzer --- pages/account/43.php | 558 +++++++++++++++++++++++++++------------------------ 1 file changed, 295 insertions(+), 263 deletions(-) (limited to 'pages/account/43.php') diff --git a/pages/account/43.php b/pages/account/43.php index dc56c36..96d2a10 100644 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -318,273 +318,305 @@
- 0) { - // select domid's - $query = "select `id` as `domids` from `domains` where `memid`='".intval($row['id'])."' "; - $dres = mysql_query($query); - $rcexpired = 0; - $rcrevoked = 0; - $rcexpiremax = "0000-00-00 00:00:00"; - while ($drow = mysql_fetch_assoc($dres)) { - $ndomid = intval($drow['domids']); - - $query2 = "select COUNT(`id`) as `dexpired` from `domaincerts` where `domid`='".$ndomid."' and `revoked` = '0000-00-00 00:00:00' and `expire` < now() "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcexpired += intval($drow2['dexpired']); - - $query2 = "select COUNT(`id`) as `drevoked` from `domaincerts` where `domid`='".$ndomid."' and `revoked` != '0000-00-00 00:00:00' "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcrevoked += intval($drow2['drevoked']); - - // For Arbitration purpose expiry dates of revoked certs are also relevant! - $query2 = "select `expire` as `mexpire` from `domaincerts` where `domid`='".$ndomid."' order by `expire` desc "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcexpiremax = max($rcexpiremax,$drow2['mexpire']); - - $rcactive = intval($rctotal)-intval($rcexpired)-intval($rcrevoked); - } - } -?> - - - - - - - - - - - - - - - - 0) { -?> - - - - - - - - - - 0) { - $rcexpired = 0; - $rcrevoked = 0; - $rcexpiremax = "0000-00-00 00:00:00"; - - $query2 = "select COUNT(`id`) as `eexpired` from `emailcerts` where `memid`='".intval($row['id'])."' and `revoked` = '0000-00-00 00:00:00' and `expire` < now() "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcexpired = intval($drow2['eexpired']); - - $query2 = "select COUNT(`id`) as `erevoked` from `emailcerts` where `memid`='".intval($row['id'])."' and `revoked` != '0000-00-00 00:00:00' "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcrevoked = intval($drow2['erevoked']); - - $query2 = "select `expire` as `eexpire` from `emailcerts` where `memid`='".intval($row['id'])."' order by `expire` desc "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcexpiremax = $drow2['eexpire']; - - $rcactive = intval($rctotal)-intval($rcexpired)-intval($rcrevoked); - -?> - - - - - - - - - - - - - - 0) { - $rcexpired = 0; - $rcexpiremax = "0000-00-00 00:00:00"; - - $query2 = "select COUNT(`id`) as `gexpired` from `gpg` where `memid`='".intval($row['id'])."' and `expire` < now() "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcexpired = intval($drow2['gexpired']); - -/* - $query2 = "select COUNT(`id`) as `erevoked` from `gpg` where `memid`='".intval($row['id'])."' and `revoked` != '0000-00-00 00:00:00' "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcrevoked = intval($drow2['erevoked']); - */ - - $query2 = "select `expire` as `gexpire` from `gpg` where `memid`='".intval($row['id'])."' order by `expire` desc "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcexpiremax = $drow2['gexpire']; - - $rcactive = intval($rctotal)-intval($rcexpired); -?> - - - - - - - - - - - - - - 0) { - // user account is linked into orgs - - // $query = "select COUNT(`orgdomaincerts`.`id`) as `orgcountdomaincerts` from `orgdomains` inner join `orgdomaincerts` on `orgdomaincerts`.`orgid` = `orgdomains`.`id` where `memid`='".intval($row['id'])."' "; - $query = "SELECT count(`orgdomaincerts`.`id`) as `countorgdomcerts` FROM `orgdomaincerts` inner join `orgdomains` on `orgdomaincerts`.`orgid`=`orgdomains`.`orgid` inner join `org` on `orgdomains`.`orgid`=`org`.`orgid` where `memid`='".intval($row['id'])."' "; - - $dres = mysql_query($query); - $drow = mysql_fetch_assoc($dres); - $rctotal = $drow['countorgdomcerts']; - if($rctotal > 0) { - // select domid's - $query = "select `orgdomains`.`orgid` as `orgdomorgids` from `orgdomains` inner join `org` on `orgdomains`.`orgid`=`org`.`orgid` where `memid`='".intval($row['id'])."' "; - $dres = mysql_query($query); - $rcexpired = 0; - $rcrevoked = 0; - $rcexpiremax = "0000-00-00 00:00:00"; - while ($drow = mysql_fetch_assoc($dres)) { - $ndomid = intval($drow['orgdomorgids']); - - $query2 = "select COUNT(`orgdomaincerts`.`id`) as `dexpired` from `orgdomaincerts` where `orgid`='".$ndomid."' and `revoked` = '0000-00-00 00:00:00' and `expire` < now() "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcexpired += intval($drow2['dexpired']); // active, but expired - - $query2 = "select COUNT(`orgdomaincerts`.`id`) as `drevoked` from `orgdomaincerts` where `orgid`='".$ndomid."' and `revoked` != '0000-00-00 00:00:00' "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcrevoked += intval($drow2['drevoked']); // revoked - - // For Arbitration purpose expiry dates of revoked certs are also relevant! - $query2 = "select `expire` as `mexpire` from `orgdomaincerts` where `orgid`='".$ndomid."' order by `expire` desc "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcexpiremax = max($rcexpiremax,$drow2['mexpire']); - - } - $rcactive = intval($rctotal)-intval($rcexpired)-intval($rcrevoked); - } - if($rctotal > 0) { -?> - - - - - - - - - - - - - - 0) { - $rcexpired = 0; - $rcrevoked = 0; - $rcexpiremax = "0000-00-00 00:00:00"; - $query2 = "select COUNT(`orgemailcerts`.`id`) as `eexpired` from `orgemailcerts` inner join `org` on `orgemailcerts`.`orgid`=`org`.`orgid` where `org`.`memid`='".intval($row['id'])."' and `revoked` = '0000-00-00 00:00:00' and `expire` < now() "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcexpired = intval($drow2['eexpired']); - - $query2 = "select COUNT(`orgemailcerts`.`id`) as `erevoked` from `orgemailcerts` inner join `org` on `orgemailcerts`.`orgid`=`org`.`orgid` where `org`.`memid`='".intval($row['id'])."' and `revoked` != '0000-00-00 00:00:00' "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcrevoked = intval($drow2['erevoked']); - - $query2 = "select `expire` as `eexpire` from `orgemailcerts` inner join `org` on `orgemailcerts`.`orgid`=`org`.`orgid` where `memid`='".intval($row['id'])."' order by `expire` desc "; - $dres2 = mysql_query($query2); - $drow2 = mysql_fetch_assoc($dres2); - $rcexpiremax = $drow2['eexpire']; - - $rcactive = intval($rctotal)-intval($rcexpired)-intval($rcrevoked); - -?> - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + 0) { + $query = "select COUNT(*) as `valid` + from `domains` inner join `domaincerts` + on `domains`.`id` = `domaincerts`.`domid` + where `domains`.`memid` = '".intval($row['id'])."' + and `revoked` = '0000-00-00 00:00:00' + and `expire` > NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $valid = $drow['valid']; + + $query = "select COUNT(*) as `expired` + from `domains` inner join `domaincerts` + on `domains`.`id` = `domaincerts`.`domid` + where `domains`.`memid` = '".intval($row['id'])."' + and `expire` <= NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $expired = $drow['expired']; + + $query = "select COUNT(*) as `revoked` + from `domains` inner join `domaincerts` + on `domains`.`id` = `domaincerts`.`domid` + where `domains`.`memid` = '".intval($row['id'])."' + and `revoked` != '0000-00-00 00:00:00'"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $revoked = $drow['revoked']; + ?> + + + + + + 0 + ?> + + + + + + + 0) { + $query = "select COUNT(*) as `valid` + from `emailcerts` + where `memid` = '".intval($row['id'])."' + and `revoked` = '0000-00-00 00:00:00' + and `expire` > NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $valid = $drow['valid']; + + $query = "select COUNT(*) as `expired` + from `emailcerts` + where `memid` = '".intval($row['id'])."' + and `expire` <= NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $expired = $drow['expired']; + + $query = "select COUNT(*) as `revoked` + from `emailcerts` + where `memid` = '".intval($row['id'])."' + and `revoked` != '0000-00-00 00:00:00'"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $revoked = $drow['revoked']; + ?> + + + + + + 0 + ?> + + + + + + + 0) { + $query = "select COUNT(*) as `valid` + from `gpg` + where `memid` = '".intval($row['id'])."' + and `expire` > NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $valid = $drow['valid']; + + $query = "select COUNT(*) as `expired` + from `emailcerts` + where `memid` = '".intval($row['id'])."' + and `expire` <= NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $expired = $drow['expired']; + + ?> + + + + + + 0 + ?> + + + + + + + 0) { + $query = "select COUNT(*) as `valid` + from `orgdomaincerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`revoked` = '0000-00-00 00:00:00' + and `orgcerts`.`expire` > NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $valid = $drow['valid']; + + $query = "select COUNT(*) as `expired` + from `orgdomaincerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`expire` <= NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $expired = $drow['expired']; + + $query = "select COUNT(*) as `revoked` + from `orgdomaincerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`revoked` != '0000-00-00 00:00:00'"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $revoked = $drow['revoked']; + ?> + + + + + + 0 + ?> + + + + + + + 0) { + $query = "select COUNT(*) as `valid` + from `orgemailcerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`revoked` = '0000-00-00 00:00:00' + and `orgcerts`.`expire` > NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $valid = $drow['valid']; + + $query = "select COUNT(*) as `expired` + from `orgemailcerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`expire` <= NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $expired = $drow['expired']; + + $query = "select COUNT(*) as `revoked` + from `orgemailcerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`revoked` != '0000-00-00 00:00:00'"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $revoked = $drow['revoked']; + ?> + + + + + + 0 + ?> + + +
:
:0)?"Pending":" ") ?>
:0)?"Pending":" ") ?>
:
: 0)?"Pending":" ") ?>
:
:0)?"Pending":" ") ?>
:
:0)?"Pending":" ") ?>
:
:
:
:
:
:
:
:

- - + Date: Tue, 6 Dec 2011 18:30:47 +0100 Subject: bug 794: complete rewrite of the patch MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit - explicitly query for valid certs - let MySQL figure out most of the stuff (don't use loops and subqueries in those loops and so on) - minor improvements Signed-off-by: Michael Tänzer --- pages/account/43.php | 298 +++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 298 insertions(+) (limited to 'pages/account/43.php') diff --git a/pages/account/43.php b/pages/account/43.php index f058770..5156710 100755 --- a/pages/account/43.php +++ b/pages/account/43.php @@ -485,6 +485,304 @@ include_once($_SESSION['_config']['filepath']."/includes/notary.inc.php"); // End - Debug infos ?> + + + + + + + + + + + + + + + + + 0) { + $query = "select COUNT(*) as `valid` + from `domains` inner join `domaincerts` + on `domains`.`id` = `domaincerts`.`domid` + where `domains`.`memid` = '".intval($row['id'])."' + and `revoked` = '0000-00-00 00:00:00' + and `expire` > NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $valid = $drow['valid']; + + $query = "select COUNT(*) as `expired` + from `domains` inner join `domaincerts` + on `domains`.`id` = `domaincerts`.`domid` + where `domains`.`memid` = '".intval($row['id'])."' + and `expire` <= NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $expired = $drow['expired']; + + $query = "select COUNT(*) as `revoked` + from `domains` inner join `domaincerts` + on `domains`.`id` = `domaincerts`.`domid` + where `domains`.`memid` = '".intval($row['id'])."' + and `revoked` != '0000-00-00 00:00:00'"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $revoked = $drow['revoked']; + ?> + + + + + + 0 + ?> + + + + + + + 0) { + $query = "select COUNT(*) as `valid` + from `emailcerts` + where `memid` = '".intval($row['id'])."' + and `revoked` = '0000-00-00 00:00:00' + and `expire` > NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $valid = $drow['valid']; + + $query = "select COUNT(*) as `expired` + from `emailcerts` + where `memid` = '".intval($row['id'])."' + and `expire` <= NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $expired = $drow['expired']; + + $query = "select COUNT(*) as `revoked` + from `emailcerts` + where `memid` = '".intval($row['id'])."' + and `revoked` != '0000-00-00 00:00:00'"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $revoked = $drow['revoked']; + ?> + + + + + + 0 + ?> + + + + + + + 0) { + $query = "select COUNT(*) as `valid` + from `gpg` + where `memid` = '".intval($row['id'])."' + and `expire` > NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $valid = $drow['valid']; + + $query = "select COUNT(*) as `expired` + from `emailcerts` + where `memid` = '".intval($row['id'])."' + and `expire` <= NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $expired = $drow['expired']; + + ?> + + + + + + 0 + ?> + + + + + + + 0) { + $query = "select COUNT(*) as `valid` + from `orgdomaincerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`revoked` = '0000-00-00 00:00:00' + and `orgcerts`.`expire` > NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $valid = $drow['valid']; + + $query = "select COUNT(*) as `expired` + from `orgdomaincerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`expire` <= NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $expired = $drow['expired']; + + $query = "select COUNT(*) as `revoked` + from `orgdomaincerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`revoked` != '0000-00-00 00:00:00'"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $revoked = $drow['revoked']; + ?> + + + + + + 0 + ?> + + + + + + + 0) { + $query = "select COUNT(*) as `valid` + from `orgemailcerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`revoked` = '0000-00-00 00:00:00' + and `orgcerts`.`expire` > NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $valid = $drow['valid']; + + $query = "select COUNT(*) as `expired` + from `orgemailcerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`expire` <= NOW()"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $expired = $drow['expired']; + + $query = "select COUNT(*) as `revoked` + from `orgemailcerts` as `orgcerts` inner join `org` + on `orgcerts`.`orgid` = `org`.`orgid` + where `org`.`memid` = '".intval($row['id'])."' + and `orgcerts`.`revoked` != '0000-00-00 00:00:00'"; + $dres = mysql_query($query); + $drow = mysql_fetch_assoc($dres); + $revoked = $drow['revoked']; + ?> + + + + + + 0 + ?> + + + +
:
:
:
:
:
:
+
+ ()
-- cgit v1.2.1