diff options
| author | Struan Donald <struan@fury.ukcod.org.uk> | 2011-06-01 16:16:35 +0100 |
|---|---|---|
| committer | Struan Donald <struan@fury.ukcod.org.uk> | 2011-06-01 16:16:35 +0100 |
| commit | e14a8bc6d38d48941628a6239c57385fcf7c7e00 (patch) | |
| tree | d119ece75ca77a0d6371a203acb0c88f346a558c /perllib/Problems.pm | |
| parent | ec851116d3a1b697996810eff8b2701de78ff621 (diff) | |
update timeline queries to pull email addresses from users table
Diffstat (limited to 'perllib/Problems.pm')
| -rw-r--r-- | perllib/Problems.pm | 14 |
1 files changed, 10 insertions, 4 deletions
diff --git a/perllib/Problems.pm b/perllib/Problems.pm index 48203b980..02ac2b384 100644 --- a/perllib/Problems.pm +++ b/perllib/Problems.pm @@ -503,11 +503,13 @@ Uses any site_restriction defined by a cobrand. =cut sub timeline_problems { my $current_timestamp = current_timestamp(); - my $problems = select_all("select state,id,name,email,title,council,category,service,cobrand,cobrand_data, + my $problems = select_all("select state,problem.id,problem.name,users.email,title,council,category,service,cobrand,cobrand_data, extract(epoch from created) as created, extract(epoch from confirmed) as confirmed, extract(epoch from whensent) as whensent - from problem where (created>=$current_timestamp-'7 days'::interval + from problem, users + where problem.user_id = users.id + and (created>=$current_timestamp-'7 days'::interval or confirmed>=$current_timestamp-'7 days'::interval or whensent>=$current_timestamp-'7 days'::interval) $site_restriction"); @@ -525,9 +527,11 @@ Uses any site_restriction defined by a cobrand. sub timeline_updates { my $updates = select_all("select comment.*, extract(epoch from comment.created) as created, + users.email, problem.council - from comment, problem + from comment, problem, users where comment.problem_id = problem.id + and comment.user_id = users.id and comment.state='confirmed' and comment.created>=" . current_timestamp() . "-'7 days'::interval $site_restriction"); @@ -548,7 +552,9 @@ sub timeline_alerts { } my $alerts = select_all("select *, extract(epoch from whensubscribed) as whensubscribed - from alert where whensubscribed>=" . current_timestamp() . "-'7 days'::interval + from alert, users + where alert.user_id = users.id + and whensubscribed>=" . current_timestamp() . "-'7 days'::interval and confirmed=1 $cobrand_clause"); return $alerts; |