<=head
title=>Support Requests
body<=
has_priv( 'supportclose', $filtercat ); # private cats/only this cat
$can_close = 1 if $fcat->{public_read} && $remote && $remote->has_priv( 'supportclose', '' ); # public cats
}
my $append;
if ($state eq "closed") {
$ret .= "";
$ret .= " "href=\"$LJ::SITEROOT/support/help?cat=$filtercat\"" } ) ." p?>";
} elsif ($state eq "youreplied") {
return " "
unless $remote;
$ret .= "";
$ret .= "";
} else {
$ret .= "";
$ret .= BML::ml('.state.else.text', {'statelink'=>"href=\"$LJ::SITEROOT/support/help?state=closed&cat=$filtercat\""}) ;
$append = 1;
}
my @support_log;
# if we have a cat to filter to and we have abstracts for it
my $rct = 0;
my $abstracts = 0;
if ($filtercat && $LJ::SUPPORT_ABSTRACTS{$filtercat} && $fcat && $can_read && $state ne 'youreplied') {
# yes, we should show abstracts for this category, so do so
if ($state eq "closed") {
$sth = $dbr->prepare("SELECT s.*, SUBSTRING(sl.message, 1, 200) AS 'message' " .
"FROM support s, supportlog sl " .
"WHERE s.state='closed' AND s.spid = sl.spid AND sl.type = 'req' " .
"AND s.timeclosed > (UNIX_TIMESTAMP() - (3600*168)) " .
"AND s.spcatid = ?");
} else { # triggers on green, open
$sth = $dbr->prepare("SELECT s.*, SUBSTRING(sl.message, 1, 200) AS 'message' " .
"FROM support s, supportlog sl " .
"WHERE s.state='open' AND s.spid = sl.spid AND sl.type = 'req' " .
"AND s.spcatid = ?");
}
$sth->execute($fcat->{spcatid});
push @support_log, $_ while $_ = $sth->fetchrow_hashref();
$rct = scalar(@support_log);
$abstracts = 1;
} else {
my $filterwhere;
if ($filtercat eq "_nonpublic") {
$filterwhere = " AND s.spcatid IN (0";
foreach my $cat (values %$cats) {
$filterwhere .= ", $cat->{'spcatid'}"
if !$cat->{'public_read'} && LJ::Support::can_read_cat($cat, $remote);
}
$filterwhere .= ")";
} elsif ($filtercat eq "_nonprivate") {
$filterwhere = " AND s.spcatid IN (0";
foreach my $cat (values %$cats) {
$filterwhere .= ", $cat->{'spcatid'}" if $cat->{public_read};
}
$filterwhere .= ")";
} elsif ($filtercat =~ /,/) {
my %filtercats = map { $_ => 1 } split(",", $filtercat);
$filterwhere .= " AND s.spcatid IN (0";
foreach my $cat (values %$cats) {
next unless $filtercats{$cat->{'catkey'}};
$filterwhere .= ", $cat->{'spcatid'}" if LJ::Support::can_read_cat($cat, $remote);
};
$filterwhere .= ")";
} else {
if ($can_read) {
$filterwhere = " AND s.spcatid=$fcat->{'spcatid'}";
} else {
$filtercat = "";
}
}
my $dbr = LJ::get_db_reader();
if ($state eq "closed") {
$sth = $dbr->prepare("SELECT s.* FROM support s WHERE s.state='closed' AND " .
"s.timeclosed>UNIX_TIMESTAMP()-(3600*168) $filterwhere");
} elsif ($state eq "youreplied") {
$sth = $dbr->prepare("SELECT s.* FROM support s, support_youreplied yr " .
"WHERE yr.userid=$remote->{'userid'} AND s.spid=yr.spid $filterwhere " .
"AND (s.state='open' OR (s.state='closed' AND s.timeclosed>UNIX_TIMESTAMP()-(3600*168)))");
} else { # triggers on green, open
$sth = $dbr->prepare("SELECT s.* FROM support s WHERE s.state='open' $filterwhere");
}
$sth->execute;
# For the You Replied filter, we might be getting some rows multiple times (when
# multiple log rows exist for $remote), which is still better than using DISTINCT
# in the query which uses a temporary table, so ensure uniqueness here.
my %spids_seen;
while (my $sprow = $sth->fetchrow_hashref) {
next if $spids_seen{$sprow->{'spid'}};
$spids_seen{$sprow->{'spid'}} = 1;
push @support_log, $sprow;
$rct++;
}
}
my $sort = lc $FORM{sort} || '';
$sort = 'date' unless grep { $_ eq $sort } qw( id summary area recent );
if ($append) {
# Counts of requests in differing states
my $gct = 0;
my $snhct = 0;
my $aacct = 0;
foreach (@support_log) {
if ($_->{'timelasthelp'} > $_->{'timetouched'}+5) {
$aacct++;
} elsif ($_->{'timelasthelp'} && $_->{'timetouched'} > $_->{'timelasthelp'}+5) {
$snhct++;
} else {
$gct++;
}
}
$ret .= "[ $gct $ML{'.status.unanswered'}, $snhct $ML{'.status.needhelp'}, ";
$ret .= "$aacct $ML{'.status.awaitingclose'}, $rct $ML{'.status.totalopen'} ]
";
}
my $can_view_nonpublic_modtime = $remote && ( $remote->has_priv( 'supportviewinternal' ) || $remote->has_priv( 'supporthelp' ) );
my $time_active = sub {
# timemodified is updated when ICs/screened answers are made, so first check priv
return $_[0]->{timemodified}
if $can_view_nonpublic_modtime && $_[0]->{timemodified};
my $touched = $_[0]->{timetouched};
my $lasthelp = $_[0]->{timelasthelp};
return $lasthelp > $touched ? $lasthelp : $touched;
};
if ($sort eq 'id') {
@support_log = sort { $a->{spid} <=> $b->{spid} } @support_log;
} elsif ($sort eq 'date') {
@support_log = sort { $b->{timecreate} <=> $a->{timecreate} } @support_log;
} elsif ($sort eq 'summary') {
@support_log = sort { $a->{subject} cmp $b->{subject} } @support_log;
} elsif ($sort eq 'area') {
@support_log = sort { $cats->{$a->{spcatid}}->{catname} cmp $cats->{$b->{spcatid}}->{catname} } @support_log;
} elsif ( $sort eq 'recent' ) {
@support_log = sort { $time_active->($b) <=> $time_active->($a) } @support_log;
}
# filter line:
$ret .= "";
# /filter line
# mass closing table
$ret .= "";
}
$ret .= "";
$ret .= "".BML::ml('.notifylink', {url=>'href="./changenotify"'})."
";
$ret .= "".BML::ml('.backlink', {backurl=>'href="./"'})."
";
return $ret;
_code?>
<=body
page?>