cgi-bin/supportlib.pl

   1 #!/usr/bin/perl
   2 #
   3
   4 use strict;
   5
   6 package LJ::Support;
   7
   8 use vars qw(@SUPPORT_PRIVS);
   9
  10 use Digest::MD5 qw(md5_hex);
  11
  12 use lib "$ENV{LJHOME}/cgi-bin";
  13 require "sysban.pl";
  14 use LJ::TimeUtil;
  15 use LJ::Support::Request::Tag;
  16 use LJ::Event::SupportRequest;
  17
  18 # Constants
  19 my $SECONDS_IN_DAY  = 3600 * 24;
  20 @SUPPORT_PRIVS = (qw/supportclose
  21                      supporthelp
  22                      supportdelete
  23                      supportread
  24                      supportviewinternal
  25                      supportmakeinternal
  26                      supportmovetouch
  27                      supportviewscreened
  28                      supportviewstocks
  29                      supportchangesummary/);
  30
  31 # <LJFUNC>
  32 # name: LJ::Support::slow_query_dbh
  33 # des: Retrieve a database handle to be used for support-related
  34 #      slow queries... defaults to 'slow' role but can be
  35 #      overriden by [ljconfig[support_slow_roles]].
  36 # args: none
  37 # returns: master database handle.
  38 # </LJFUNC>
  39 sub slow_query_dbh
  40 {
  41     return LJ::get_dbh(@LJ::SUPPORT_SLOW_ROLES);
  42 }
  43
  44 ## pass $id of zero or blank to get all categories
  45 sub load_cats
  46 {
  47     my ($id) = @_;
  48     my $hashref = {};
  49     $id += 0;
  50     my $where = $id ? "WHERE spcatid=$id" : "";
  51     my $dbr = LJ::get_db_reader();
  52     my $sth = $dbr->prepare("SELECT * FROM supportcat $where");
  53     $sth->execute;
  54     $hashref->{$_->{'spcatid'}} = $_ while ($_ = $sth->fetchrow_hashref);
  55     return $hashref;
  56 }
  57
  58 sub load_email_to_cat_map
  59 {
  60     my $map = {};
  61     my $dbr = LJ::get_db_reader();
  62     my $sth = $dbr->prepare("SELECT * FROM supportcat ORDER BY sortorder DESC");
  63     $sth->execute;
  64     while (my $sp = $sth->fetchrow_hashref) {
  65         next unless ($sp->{'replyaddress'});
  66         $map->{$sp->{'replyaddress'}} = $sp;
  67     }
  68     return $map;
  69 }
  70
  71 sub calc_points
  72 {
  73     my ($sp, $secs, $spcat) = @_;
  74     $spcat ||= $sp->{_cat};
  75     my $base = $spcat->{'basepoints'} || 1;
  76     $secs = int($secs / (3600*6));
  77     my $total = ($base + $secs);
  78     if ($total > 10) { $total = 10; }
  79     return $total;
  80 }
  81
  82 sub init_remote
  83 {
  84     my $remote = shift;
  85     return unless $remote;
  86     LJ::load_user_privs($remote, @SUPPORT_PRIVS);
  87 }
  88
  89 sub has_any_support_priv {
  90     my $u = shift;
  91     return 0 unless $u;
  92     foreach my $support_priv (@SUPPORT_PRIVS) {
  93         return 1 if LJ::check_priv($u, $support_priv);
  94     }
  95     return 0;
  96 }
  97
  98 # given all the categories, maps a catkey into a cat
  99 sub get_cat_by_key
 100 {
 101     my ($cats, $cat) = @_;
 102     foreach (keys %$cats) {
 103         if ($cats->{$_}->{'catkey'} eq $cat) {
 104             return $cats->{$_};
 105         }
 106     }
 107     return undef;
 108 }
 109
 110 sub get_cat_by_id
 111 {
 112     my ($cats, $id) = @_;
 113     foreach (keys %$cats) {
 114         if ($cats->{$_}->{'spcatid'} == $id) {
 115             return $cats->{$_};
 116         }
 117     }
 118     return undef;
 119 }
 120
 121 sub filter_cats
 122 {
 123     my $remote = shift;
 124     my $cats = shift;
 125
 126     return grep {
 127         can_read_cat($_, $remote);
 128     } sorted_cats($cats);
 129 }
 130
 131 sub sorted_cats
 132 {
 133     my $cats = shift;
 134     return sort { $a->{'catname'} cmp $b->{'catname'} } values %$cats;
 135 }
 136
 137 # takes raw support request record and puts category info in it
 138 # so it can be used in other functions like can_*
 139 sub fill_request_with_cat
 140 {
 141     my ($sp, $cats) = @_;
 142     $sp->{_cat} = $cats->{$sp->{'spcatid'}};
 143 }
 144
 145 sub is_poster {
 146     my ($sp, $remote, $auth) = @_;
 147
 148     if ($sp->{'reqtype'} eq "user") {
 149         return 1 if $remote && $remote->id == $sp->{'requserid'};
 150
 151     } else {
 152         if ($remote) {
 153             return 1 if lc($remote->email_raw) eq lc($sp->{'reqemail'});
 154         } else {
 155             return 1 if $auth && $auth eq mini_auth($sp);
 156         }
 157     }
 158
 159     return 0;
 160 }
 161
 162 sub can_see_helper
 163 {
 164     my ($sp, $remote) = @_;
 165     my $spcat = $sp->{_cat};
 166     if ($spcat->{'hide_helpers'}) {
 167         if (can_help($spcat, $remote)) {
 168             return 1;
 169         }
 170         if (LJ::check_priv($remote, "supportviewinternal", $sp->{_cat}->{'catkey'})) {
 171             return 1;
 172         }
 173         if (LJ::check_priv($remote, "supportviewscreened", $sp->{_cat}->{'catkey'})) {
 174             return 1;
 175         }
 176         return 0;
 177     }
 178     return 1;
 179 }
 180
 181 sub can_see_response {
 182     my ($splid, $u) = @_;
 183
 184     my $response = load_response($splid);
 185     my $type     = $response->{type};
 186     my $spid     = $response->{spid};
 187     my $sp       = load_request($spid);
 188     my $cat      = load_cats()->{ $sp->{spcatid} };
 189
 190     my $PRIVS_BY_TYPE = {
 191         answer   => can_read_cat($cat, $u),
 192         comment  => can_read_cat($cat, $u),
 193         screened => can_read_screened($cat, $u),
 194         internal => can_read_internal($cat, $u),
 195     };
 196
 197     return $PRIVS_BY_TYPE->{$type};
 198 }
 199
 200 sub can_read
 201 {
 202     my ($sp, $remote, $auth) = @_;
 203     return (is_poster($sp, $remote, $auth) ||
 204             can_read_cat($sp->{_cat}, $remote));
 205 }
 206
 207 sub can_read_cat
 208 {
 209     my ($cat, $remote) = @_;
 210     return unless ($cat);
 211     return ($cat->{'public_read'} ||
 212             LJ::check_priv($remote, "supportread", $cat->{'catkey'}));
 213 }
 214
 215 *can_bounce = \&can_close_cat;
 216 *can_lock   = \&can_close_cat;
 217
 218 # if they can close in this category
 219 sub can_close_cat
 220 {
 221     my ($sp, $remote) = @_;
 222     return 1 if $sp->{_cat}->{public_read} && LJ::check_priv($remote, 'supportclose', '');
 223     return 1 if LJ::check_priv($remote, 'supportclose', $sp->{_cat}->{catkey});
 224     return 0;
 225 }
 226
 227 # if they can close this particular request
 228 sub can_close
 229 {
 230     my ($sp, $remote, $auth) = @_;
 231     return 1 if $sp->{_cat}->{user_closeable} && is_poster($sp, $remote, $auth);
 232     return can_close_cat($sp, $remote);
 233 }
 234
 235 # if they can reopen a request
 236 sub can_reopen {
 237     my ($sp, $remote, $auth) = @_;
 238     return 1 if is_poster($sp, $remote, $auth);
 239     return can_close_cat($sp, $remote);
 240 }
 241
 242 sub can_append
 243 {
 244     my ($sp, $remote, $auth) = @_;
 245     my $spcat = $sp->{_cat};
 246     if (is_poster($sp, $remote, $auth)) { return 1; }
 247     return 0 unless $remote;
 248     return 0 unless $remote->{'statusvis'} eq "V";
 249     if ($spcat->{'allow_screened'}) { return 1; }
 250     if (can_help($spcat, $remote)) { return 1; }
 251     return 0;
 252 }
 253
 254 sub is_locked
 255 {
 256     my $sp = shift;
 257     my $spid = ref $sp ? $sp->{spid} : $sp+0;
 258     return undef unless $spid;
 259     my $props = LJ::Support::load_props($spid);
 260     return $props->{locked} ? 1 : 0;
 261 }
 262
 263 sub lock
 264 {
 265     my $sp = shift;
 266     my $spid = ref $sp ? $sp->{spid} : $sp+0;
 267     return undef unless $spid;
 268     my $dbh = LJ::get_db_writer();
 269     $dbh->do("REPLACE INTO supportprop (spid, prop, value) VALUES (?, 'locked', 1)", undef, $spid);
 270 }
 271
 272 sub unlock
 273 {
 274     my $sp = shift;
 275     my $spid = ref $sp ? $sp->{spid} : $sp+0;
 276     return undef unless $spid;
 277     my $dbh = LJ::get_db_writer();
 278     $dbh->do("DELETE FROM supportprop WHERE spid = ? AND prop = 'locked'", undef, $spid);
 279 }
 280
 281 # privilege policy:
 282 #   supporthelp with no argument gives you all abilities in all public_read categories
 283 #   supporthelp with a catkey arg gives you all abilities in that non-public_read category
 284 #   supportread with a catkey arg is required to view requests in a non-public_read category
 285 #   all other privs work like:
 286 #      no argument = global, where category is public_read or user has supportread on that category
 287 #      argument = local, priv applies in that category only if it's public or user has supportread
 288 sub support_check_priv
 289 {
 290     my ($cat, $remote, $priv) = @_;
 291     return 0 unless can_read_cat($cat, $remote);
 292     return 1 if can_help($cat, $remote);
 293     return 1 if LJ::check_priv($remote, $priv, '') && $cat->{public_read};
 294     return 1 if LJ::check_priv($remote, $priv, $cat->{catkey});
 295     return 0;
 296 }
 297
 298 # can they read internal comments?  if they're a helper or have
 299 # extended supportread (with a plus sign at the end of the category key)
 300 sub can_read_internal
 301 {
 302     my ($cat, $remote) = @_;
 303     return 1 if LJ::Support::support_check_priv($cat, $remote, 'supportviewinternal');
 304     return 1 if LJ::check_priv($remote, "supportread", $cat->{catkey}."+");
 305     return 0;
 306 }
 307
 308 sub can_make_internal
 309 {
 310     return LJ::Support::support_check_priv(@_, 'supportmakeinternal');
 311 }
 312
 313 sub can_read_screened
 314 {
 315     return LJ::Support::support_check_priv(@_, 'supportviewscreened');
 316 }
 317
 318 sub can_perform_actions
 319 {
 320     return LJ::Support::support_check_priv(@_, 'supportmovetouch');
 321 }
 322
 323 sub can_change_summary
 324 {
 325     return LJ::Support::support_check_priv(@_, 'supportchangesummary');
 326 }
 327
 328 sub can_see_stocks
 329 {
 330     return LJ::Support::support_check_priv(@_, 'supportviewstocks');
 331 }
 332
 333 sub can_help
 334 {
 335     my ($cat, $remote) = @_;
 336     if ($cat->{'public_read'}) {
 337         if ($cat->{'public_help'}) {
 338             return 1;
 339         }
 340         if (LJ::check_priv($remote, "supporthelp", "")) { return 1; }
 341     }
 342     my $catkey = $cat->{'catkey'};
 343     if (LJ::check_priv($remote, "supporthelp", $catkey)) { return 1; }
 344     return 0;
 345 }
 346
 347 sub load_props
 348 {
 349     my $spid = shift;
 350     return unless $spid;
 351
 352     my %props = (); # prop => value
 353
 354     my $dbr = LJ::get_db_reader();
 355     my $sth = $dbr->prepare("SELECT prop, value FROM supportprop WHERE spid=?");
 356     $sth->execute($spid);
 357     while (my ($prop, $value) = $sth->fetchrow_array) {
 358         $props{$prop} = $value;
 359     }
 360
 361     return \%props;
 362 }
 363
 364 sub prop
 365 {
 366     my ($spid, $propname) = @_;
 367
 368     my $props = LJ::Support::load_props($spid);
 369
 370     return $props->{$propname} || undef;
 371 }
 372
 373 sub set_prop
 374 {
 375     my ($spid, $propname, $propval) = @_;
 376
 377     # TODO:
 378     # -- delete on 'undef' propval
 379     # -- allow setting of multiple
 380
 381     my $dbh = LJ::get_db_writer()
 382         or die "couldn't contact global master";
 383
 384     $dbh->do("REPLACE INTO supportprop (spid, prop, value) VALUES (?,?,?)",
 385              undef, $spid, $propname, $propval);
 386     die $dbh->errstr if $dbh->err;
 387
 388     return 1;
 389 }
 390
 391 # The following 3 subroutines are working with splid' meta-data from
 392 # supportlogprop table.
 393 #
 394 # prop         : value desc
 395 # --------------------------------------------------------------------------
 396 # approved     : splid of approved answer
 397 # moved_from   : catid of a category support request has been moved from
 398 # moved_to     : catid of a category support request has been moved to
 399 # tags_added   : comma separated list of tags being added to the request
 400 # tags_removed : comma separated list of tags being removed from the request
 401 #
 402 sub load_response_props {
 403     my $splid = shift;
 404     return unless $splid;
 405
 406     my %props = (); # prop => value
 407
 408     my $dbr = LJ::get_db_reader();
 409     my $sth = $dbr->prepare("SELECT prop, value FROM supportlogprop WHERE splid=?");
 410     $sth->execute($splid);
 411     while (my ($prop, $value) = $sth->fetchrow_array) {
 412         $props{$prop} = $value;
 413     }
 414
 415     return \%props;
 416 }
 417
 418 sub response_prop {
 419     my ($splid, $propname) = @_;
 420
 421     my $props = LJ::Support::load_response_props($splid);
 422
 423     return $props->{$propname} || undef;
 424 }
 425
 426
 427 # LJ::Support::set_response_prop($splid,'approved',$appsplid);
 428 sub set_response_prop {
 429     my ($splid, $propname, $propval) = @_;
 430
 431     # TODO:
 432     # -- delete on 'undef' propval
 433     # -- allow setting of multiple
 434
 435     my $dbh = LJ::get_db_writer()
 436         or die "couldn't contact global master";
 437
 438     $dbh->do("REPLACE INTO supportlogprop (splid, prop, value) VALUES (?,?,?)",
 439              undef, $splid, $propname, $propval);
 440     die $dbh->errstr if $dbh->err;
 441
 442     return 1;
 443 }
 444
 445
 446 # $loadreq is used by /abuse/report.bml and
 447 # ljcmdbuffer.pl to signify that the full request
 448 # should not be loaded.  To simplify code going live,
 449 # Whitaker and I decided to not try and merge it
 450 # into the new $opts hash.
 451
 452 # $opts->{'db_force'} loads the request from a
 453 # global master.  Needed to prevent a race condition
 454 # where the request may not have replicated to slaves
 455 # in the time needed to load an auth code.
 456
 457 sub load_request
 458 {
 459     my ($spid, $loadreq, $opts) = @_;
 460     my $sth;
 461
 462     $spid += 0;
 463
 464     # load the support request
 465     my $db = $opts->{'db_force'} ? LJ::get_db_writer() : LJ::get_db_reader();
 466
 467     $sth = $db->prepare("SELECT * FROM support WHERE spid=$spid");
 468     $sth->execute;
 469     my $sp = $sth->fetchrow_hashref;
 470
 471     return undef unless $sp;
 472
 473     # load the category the support requst is in
 474     $sth = $db->prepare("SELECT * FROM supportcat WHERE spcatid=$sp->{'spcatid'}");
 475     $sth->execute;
 476     $sp->{_cat} = $sth->fetchrow_hashref;
 477
 478     # now load the user's request text, if necessary
 479     if ($loadreq) {
 480         $sp->{body} = $db->selectrow_array("SELECT message FROM supportlog WHERE spid = ? AND type = 'req'",
 481                                            undef, $sp->{spid});
 482     }
 483
 484     return $sp;
 485 }
 486
 487 # load_requests:
 488 # Given an arrayref, fetches information about the requests
 489 # with these spid's; unlike load_request(), it doesn't fetch information
 490 # about supportcats.
 491
 492 sub load_requests {
 493     my ($spids, $dbr) = @_;
 494
 495     $dbr ||= LJ::get_db_reader();
 496
 497     my $list = join(',', map { $_+0 } @$spids);
 498     my $requests = $dbr->selectall_arrayref(
 499         "SELECT * FROM support WHERE spid IN ($list)",
 500         { Slice => {} }
 501     );
 502
 503     return $requests;
 504 }
 505
 506 sub load_response
 507 {
 508     my $splid = shift;
 509     my $sth;
 510
 511     $splid += 0;
 512
 513     # load the support request. we hit the master because we generally
 514     # only invoke this when we want the freshest version of the row.
 515     # (ie, approving a response changes its type from screened to
 516     # answer ... then we fetch the row again and make decisions on its type.
 517     # so we want the authoritative version)
 518     my $dbh = LJ::get_db_writer();
 519     $sth = $dbh->prepare("SELECT * FROM supportlog WHERE splid=$splid");
 520     $sth->execute;
 521     my $res = $sth->fetchrow_hashref;
 522
 523     return $res;
 524 }
 525
 526 sub get_answer_types
 527 {
 528     my ($sp, $remote, $auth) = @_;
 529     my @ans_type;
 530     my $spcat = $sp->{_cat};
 531
 532     if (is_poster($sp, $remote, $auth)) {
 533         push @ans_type, ("comment", "More information");
 534         return @ans_type;
 535     }
 536
 537     if (can_help($spcat, $remote)) {
 538         push @ans_type, ("screened" => "Screened Response",
 539                          "answer" => "Answer",
 540                          "comment" => "Comment or Question");
 541     } elsif ($spcat->{'allow_screened'}) {
 542         push @ans_type, ("screened" => "Screened Response");
 543     }
 544
 545     if (can_make_internal($spcat, $remote) &&
 546         ! $spcat->{'public_help'})
 547     {
 548         push @ans_type, ("internal" => "Internal Comment / Action");
 549     }
 550
 551     if (can_bounce($sp, $remote)) {
 552         push @ans_type, ("bounce" => "Bounce to Email & Close");
 553     }
 554
 555     return @ans_type;
 556 }
 557
 558 sub file_request
 559 {
 560     my $errors = shift;
 561     my $o = shift;
 562
 563     my $email = $o->{'reqtype'} eq "email" ? $o->{'reqemail'} : "";
 564     my $log = { 'uniq' => $o->{'uniq'},
 565                 'email' => $email };
 566     my $userid = 0;
 567     my $html;
 568     my $u;
 569
 570     my $fire_event = exists $o->{fire_event}
 571         ? $o->{fire_event}
 572         : 1;
 573
 574     unless ($email) {
 575         if ($o->{'reqtype'} eq "user") {
 576             $u = LJ::load_userid($o->{'requserid'});
 577             $userid = $u->{'userid'};
 578
 579             $log->{'user'} = $u->user;
 580             $log->{'email'} = $u->email_raw;
 581
 582             unless ($u->is_person || $u->is_identity) {
 583                 push @$errors, "You cannot submit support requests from non-user accounts.";
 584             }
 585
 586             if (LJ::sysban_check('support_user', $u->{'user'})) {
 587                 return LJ::sysban_block($userid, "Support request blocked based on user", $log);
 588             }
 589
 590             $email = $u->email_raw || $o->{'reqemail'};
 591             $html  = $u->receives_html_emails;
 592         }
 593     }
 594
 595     if (LJ::sysban_check('support_email', $email)) {
 596         return LJ::sysban_block($userid, "Support request blocked based on email", $log);
 597     }
 598     if (LJ::sysban_check('support_uniq', $o->{'uniq'})) {
 599         return LJ::sysban_block($userid, "Support request blocked based on uniq", $log);
 600     }
 601
 602     my $reqsubject = LJ::trim($o->{'subject'});
 603     my $reqbody = LJ::trim($o->{'body'});
 604     my $url =  LJ::trim($o->{'url'});
 605
 606     if ($url) {
 607         $reqbody = "Url: $url\n" . $reqbody;
 608     }
 609
 610     # remove the auth portion of any see_request.bml links
 611     $reqbody =~ s/(see_request\.bml.+?)\&auth=\w+/$1/ig;
 612
 613     unless ($reqsubject) {
 614         push @$errors, "You must enter a problem summary.";
 615     }
 616     unless ($reqbody) {
 617         push @$errors, "You did not enter a support request.";
 618     }
 619
 620     my $cats = LJ::Support::load_cats();
 621     push @$errors, $BML::ML{'error.invalid.support.category'} unless $cats->{$o->{'spcatid'}+0};
 622
 623     if (@$errors) { return 0; }
 624
 625     my $lang = $o->{'language'} || $LJ::DEFAULT_LANG;
 626     my ($username, $ljuser);
 627     if ($u) {
 628         if ($u->prop('browselang')) {
 629             $lang  = $u->prop('browselang');
 630         }
 631         $username = $u->display_username;
 632         $ljuser   = $u->ljuser_display;
 633     }
 634
 635     if (LJ::is_enabled("support_request_language")) {
 636         $o->{'language'} = undef unless grep { $o->{'language'} eq $_ } (@LJ::LANGS, "xx");
 637         $reqsubject = "[$o->{'language'}] $reqsubject" if $o->{'language'} && $o->{'language'} !~ /^en_/;
 638     }
 639
 640     my $dbh = LJ::get_db_writer();
 641
 642     my $dup_id = 0;
 643     my $qsubject = $dbh->quote($reqsubject);
 644     my $qbody = $dbh->quote($reqbody);
 645     my $qreqtype = $dbh->quote($o->{'reqtype'});
 646     my $qrequserid = $o->{'requserid'}+0;
 647     my $qreqname = $dbh->quote($o->{'reqname'});
 648     my $qreqemail = $dbh->quote($o->{'reqemail'});
 649     my $qspcatid = $o->{'spcatid'}+0;
 650
 651     my $scat = $cats->{$qspcatid};
 652
 653     # make the authcode
 654     my $authcode = LJ::make_auth_code(15);
 655     my $qauthcode = $dbh->quote($authcode);
 656
 657     my $md5 = md5_hex("$qreqname$qreqemail$qsubject$qbody");
 658     my $sth;
 659
 660     $dbh->do("LOCK TABLES support WRITE, duplock WRITE");
 661
 662     unless ($o->{ignore_dup_check}) {
 663         $sth = $dbh->prepare("SELECT dupid FROM duplock WHERE realm='support' AND reid=0 AND userid=$qrequserid AND digest='$md5'");
 664         $sth->execute;
 665         ($dup_id) = $sth->fetchrow_array;
 666         if ($dup_id) {
 667             $dbh->do("UNLOCK TABLES");
 668             return $dup_id;
 669         }
 670     }
 671
 672     my $spid;
 673
 674     my $sql = "INSERT INTO support (spid, reqtype, requserid, reqname, reqemail, state, authcode, spcatid, subject, timecreate, timetouched, timeclosed, timelasthelp) VALUES (NULL, $qreqtype, $qrequserid, $qreqname, $qreqemail, 'open', $qauthcode, $qspcatid, $qsubject, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), 0, 0)";
 675     $sth = $dbh->prepare($sql);
 676     $sth->execute;
 677
 678     if ($dbh->err) {
 679         my $error = $dbh->errstr;
 680         $dbh->do("UNLOCK TABLES");
 681         push @$errors, "<b>Database error:</b> (report this)<br>$error";
 682         return 0;
 683     }
 684     $spid = $dbh->{'mysql_insertid'};
 685
 686     $dbh->do("INSERT INTO duplock (realm, reid, userid, digest, dupid, instime) VALUES ('support', 0, $qrequserid, '$md5', $spid, NOW())")
 687         unless $o->{ignore_dup_check};
 688     $dbh->do("UNLOCK TABLES");
 689
 690     unless ($spid) {
 691         push @$errors, "<b>Database error:</b> (report this)<br>Didn't get a spid.";
 692         return 0;
 693     }
 694
 695     # save meta-data for this request
 696     my @data;
 697     my $add_data = sub {
 698         my $q = $dbh->quote($_[1]);
 699         return unless $q && $q ne 'NULL';
 700         push @data, "($spid, '$_[0]', $q)";
 701     };
 702     my @props = qw(uniq useragent ip has_js is_beta);
 703     push @props, "language" if LJ::is_enabled("support_request_language");
 704     foreach my $p (@props) {
 705         $add_data->($p, $o->{$p});
 706     }
 707
 708     if (@data) {
 709         $dbh->do(
 710             'INSERT INTO supportprop (spid, prop, value) VALUES ' .
 711             join( q{,}, @data ) );
 712     }
 713
 714     $dbh->do("INSERT INTO supportlog (splid, spid, timelogged, type, faqid, userid, message) ".
 715              "VALUES (NULL, $spid, UNIX_TIMESTAMP(), 'req', 0, $qrequserid, $qbody)");
 716
 717     # set the flag on the support request and the support request on the flag
 718     if ($o->{flagid}) {
 719         LJ::Support::set_prop($spid, "contentflagid", $o->{flagid});
 720         LJ::ContentFlag->set_supportid($o->{flagid}, $spid);
 721     }
 722
 723     LJ::Event::SupportRequest->new($u, $spid)->fire if $fire_event;
 724
 725     # and we're done
 726     return $spid;
 727 }
 728
 729 sub append_request
 730 {
 731     my $sp = shift;  # support request to be appended to.
 732     my $re = shift;  # hashref of attributes of response to be appended
 733     my $sth;
 734
 735     # $re->{'body'}
 736     # $re->{'type'}    (req, answer, comment, internal, screened)
 737     # $re->{'faqid'}
 738     # $re->{'remote'}  (remote if known)
 739     # $re->{'uniq'}    (uniq of remote)
 740     # $re->{'tier'}    (tier of response if type is answer or internal)
 741     # $re->{'props'}   (meta-data for supportlogprop)
 742
 743     my $remote = $re->{'remote'};
 744     my $posterid = $remote ? $remote->{'userid'} : 0;
 745
 746     # check for a sysban
 747     my $log = { 'uniq' => $re->{'uniq'} };
 748     if ($remote) {
 749
 750         $log->{'user'} = $remote->user;
 751         $log->{'email'} = $remote->email_raw;
 752
 753         if (LJ::sysban_check('support_user', $remote->{'user'})) {
 754             return LJ::sysban_block($remote->{'userid'}, "Support request blocked based on user", $log);
 755         }
 756         if (LJ::sysban_check('support_email', $remote->email_raw)) {
 757             return LJ::sysban_block($remote->{'userid'}, "Support request blocked based on email", $log);
 758         }
 759     }
 760
 761     if (LJ::sysban_check('support_uniq', $re->{'uniq'})) {
 762         my $userid = $remote ? $remote->{'userid'} : 0;
 763         return LJ::sysban_block($userid, "Support request blocked based on uniq", $log);
 764     }
 765
 766     my $message = $re->{'body'};
 767     $message =~ s/^\s+//;
 768     $message =~ s/\s+$//;
 769
 770     my $dbh = LJ::get_db_writer();
 771
 772     my $qmessage = $dbh->quote($message);
 773     my $qtype = $dbh->quote($re->{'type'});
 774
 775     my $qfaqid = $re->{'faqid'}+0;
 776     my $quserid = $posterid+0;
 777     my $spid = $sp->{'spid'}+0;
 778     my $qtier = $re->{'tier'} ? ($re->{'tier'}+0) . "0" : "NULL";
 779
 780     my $sql;
 781     if (LJ::is_enabled("support_response_tier")) {
 782         $sql = "INSERT INTO supportlog (splid, spid, timelogged, type, faqid, userid, message, tier) VALUES (NULL, $spid, UNIX_TIMESTAMP(), $qtype, $qfaqid, $quserid, $qmessage, $qtier)";
 783     } else {
 784         $sql = "INSERT INTO supportlog (splid, spid, timelogged, type, faqid, userid, message) VALUES (NULL, $spid, UNIX_TIMESTAMP(), $qtype, $qfaqid, $quserid, $qmessage)";
 785     }
 786     $dbh->do($sql);
 787     my $splid = $dbh->{'mysql_insertid'};
 788     my $props = $re->{'props'};
 789     if ($splid) {
 790         foreach my $prop (keys %$props) {
 791             if ($prop) {
 792                 LJ::Support::set_response_prop($splid,$prop,$props->{$prop});
 793             }
 794             if ($prop eq 'tags_added') {
 795                 LJ::Event::SupportTagAdd->new($remote, $splid)->fire;
 796             }
 797         }
 798         if ($re->{type} eq 'answer') {
 799             LJ::Support::set_response_prop($splid,'approved', $splid);
 800             $dbh->do("UPDATE support SET timelasthelp=UNIX_TIMESTAMP() WHERE spid=$spid");
 801         }
 802     }
 803
 804     if ($posterid) {
 805         # add to our index of recently replied to support requests per-user.
 806         $dbh->do("INSERT IGNORE INTO support_youreplied (userid, spid) VALUES (?, ?)", undef,
 807                  $posterid, $spid);
 808         die $dbh->errstr if $dbh->err;
 809
 810         # and also lazily clean out old stuff:
 811         $sth = $dbh->prepare("SELECT s.spid FROM support s, support_youreplied yr ".
 812                              "WHERE yr.userid=? AND yr.spid=s.spid AND s.state='closed' ".
 813                              "AND s.timeclosed < UNIX_TIMESTAMP() - 3600*72");
 814         $sth->execute($posterid);
 815         my @to_del;
 816         push @to_del, $_ while ($_) = $sth->fetchrow_array;
 817         if (@to_del) {
 818             my $in = join(", ", map { $_ + 0 } @to_del);
 819             $dbh->do("DELETE FROM support_youreplied WHERE userid=? AND spid IN ($in)",
 820                      undef, $posterid);
 821         }
 822     }
 823
 824     LJ::Event::SupportResponse->new($remote, $spid, $splid)->fire;
 825
 826     return $splid;
 827 }
 828
 829 #get a sum of support points for the specified period from now
 830 sub get_sum_points {
 831     my ($userid, $period) = @_;
 832
 833     my $to   = time() - time()%86400;
 834     my $from = $to - $period;
 835     my $dbh  = LJ::get_db_writer();
 836
 837
 838     my ($sum) = $dbh->selectrow_array(
 839                                     qq{
 840                                         SELECT sum(points)
 841                                         FROM   supportpoints
 842                                         WHERE  userid = ? AND
 843                                                timeclosed BETWEEN
 844                                                ? AND ?
 845                                     },
 846                                     undef,
 847                                     $userid,
 848                                     $from,
 849                                     $to
 850     );
 851
 852     return $sum;
 853 }
 854
 855
 856 # userid may be undef/0 in the setting to zero case
 857 sub set_points
 858 {
 859     my ($spid, $userid, $points) = @_;
 860
 861     my $dbh = LJ::get_db_writer();
 862     if ($points) {
 863         $dbh->do("REPLACE INTO supportpoints (spid, userid, points, timeclosed) ".
 864                  "VALUES (?,?,?,UNIX_TIMESTAMP())", undef, $spid, $userid, $points);
 865     } else {
 866         $userid ||= $dbh->selectrow_array("SELECT userid FROM supportpoints WHERE spid=?",
 867                                           undef, $spid);
 868         $dbh->do("DELETE FROM supportpoints WHERE spid=?", undef, $spid);
 869     }
 870
 871     $dbh->do("REPLACE INTO supportpointsum (userid, totpoints, lastupdate) ".
 872              "SELECT userid, SUM(points), UNIX_TIMESTAMP() FROM supportpoints ".
 873              "WHERE userid=? GROUP BY 1", undef, $userid) if $userid;
 874
 875     # clear caches
 876     if ($userid) {
 877         my $u = LJ::load_userid($userid);
 878         delete $u->{_supportpointsum} if $u;
 879
 880         my $memkey = [$userid, "supportpointsum:$userid"];
 881         LJ::MemCache::delete($memkey);
 882     }
 883 }
 884
 885 # closes request, assigning points for the last response left to the request
 886
 887 sub close_request_with_points {
 888     my ($sp, $spcat, $remote) = @_;
 889
 890     my $spid = $sp->{'spid'}+0;
 891
 892     my $dbh = LJ::get_db_writer();
 893     $dbh->do('UPDATE support SET state="closed", '.
 894              'timeclosed=UNIX_TIMESTAMP() WHERE spid=?', undef, $spid);
 895
 896     my $response = $dbh->selectrow_hashref(
 897         'SELECT splid, timelogged, userid FROM supportlog '.
 898         'WHERE spid=? AND type="answer" '.
 899         'ORDER BY timelogged DESC LIMIT 1', undef, $spid);
 900
 901     my $res;
 902     unless (defined $response) {
 903         $res = $dbh->do(
 904             'INSERT INTO supportlog '.
 905             '(spid, timelogged, type, userid, message) VALUES '.
 906             '(?, UNIX_TIMESTAMP(), "internal", ?, ?)', undef,
 907             $spid, LJ::want_userid($remote),
 908             "(Request has been closed as part of mass closure)");
 909         return 0;
 910     }
 911
 912     my $helperid = $response->{'userid'};
 913     my $points = LJ::Support::calc_points(
 914         $sp, $response->{'timelogged'} - $sp->{'timecreate'}, $spcat);
 915
 916     LJ::Support::set_points($spid, $helperid, $points);
 917
 918     # deliberately not using LJ::Support::append_request
 919     # to avoid sysban checks etc.; this sub is supposed to be fast.
 920
 921     my $username = LJ::want_user($response->{'userid'})->display_name;
 922
 923     $res = $dbh->do(
 924         'INSERT INTO supportlog '.
 925         '(spid, timelogged, type, userid, message) VALUES '.
 926         '(?, UNIX_TIMESTAMP(), "internal", ?, ?)', undef,
 927         $spid, LJ::want_userid($remote),
 928         "(Request has been closed as part of mass closure, ".
 929         "granting $points points to $username ".
 930         "for response #".$response->{'splid'}.")");
 931     return $helperid;
 932 }
 933
 934 sub touch_request
 935 {
 936     my ($spid) = @_;
 937
 938     # no touching if the request is locked
 939     return 0 if LJ::Support::is_locked($spid);
 940
 941     my $dbh = LJ::get_db_writer();
 942
 943     $dbh->do("UPDATE support".
 944              "   SET state='open', timeclosed=0, timetouched=UNIX_TIMESTAMP()".
 945              " WHERE spid=?",
 946              undef, $spid)
 947       or return 0;
 948
 949     set_points($spid, undef, 0);
 950
 951     return 1;
 952 }
 953
 954 sub mail_response_to_user
 955 {
 956     my $sp = shift;
 957     my $splid = shift;
 958
 959     $splid += 0;
 960
 961     my $res = load_response($splid);
 962
 963     my ($u, $email, $html, $email_format);
 964     if ($sp->{'reqtype'} eq "email") {
 965         $email = $sp->{'reqemail'};
 966     } else {
 967         $u = LJ::load_userid($sp->{'requserid'});
 968         $email = $u->email_raw || $sp->{'reqemail'};
 969         $html  = $u->receives_html_emails;
 970     }
 971
 972     $email_format = $html ? 'html' : 'plain';
 973     my $spid  = $sp->{'spid'}+0;
 974     my $faqid = $res->{'faqid'}+0;
 975     my $type  = $res->{'type'};
 976
 977     # don't mail internal comments (user shouldn't see) or
 978     # screened responses (have to wait for somebody to approve it first)
 979     return if ($type eq "internal" || $type eq "screened");
 980
 981     # the only way it can be zero is if it's a reply to an email, so it's
 982     # problem the person replying to their own request, so we don't want
 983     # to mail them:
 984     return unless ($res->{'userid'});
 985
 986     # also, don't send them their own replies:
 987     return if ($sp->{'requserid'} == $res->{'userid'});
 988
 989     my $lang = LJ::Support::prop($sp->{'spid'}, 'language') || $LJ::DEFAULT_LANG;
 990     my ($username, $ljuser);
 991     if ($u) {
 992         if ($u->prop('browselang')) {
 993             $lang  = $u->prop('browselang');
 994         }
 995         $username = $u->display_username;
 996         $ljuser   = $u->ljuser_display;
 997     }
 998
 999     my $dbh = LJ::get_db_writer();
1000     my $miniauth = mini_auth($sp);
1001     my $urlauth  = "$LJ::SITEROOT/support/see_request.bml?id=$spid&auth=$miniauth";
1002
1003     # preparing [[faqref]] param
1004     my $faqref='';
1005     if ($faqid) {
1006         my $faq = LJ::Faq->load($faqid, lang => $lang);
1007         $faq->render_in_place;
1008         my $faqname = $faq->question_raw || '';
1009         $faqref = LJ::Lang::get_text (
1010                     $lang,
1011                     "notification.support.reply.request.faqref." . $email_format,
1012                     undef,
1013                     {
1014                         faqname =>  $faqname,
1015                         faqurl  =>  LJ::Faq->page_url( 'faqid' => $faqid ),
1016                     }
1017                   );
1018     }
1019
1020     # preparing [[closeable]] param
1021     my $closeable='';
1022     if ($sp->{_cat}->{user_closeable}) {
1023         my $closeurl = "$LJ::SITEROOT/support/act.bml?" .
1024                        "close;$spid;$sp->{'authcode'}";
1025         $closeurl .= ";$splid" if $type eq "answer";
1026         $closeable = LJ::Lang::get_text (
1027                     $lang,
1028                     "notification.support.reply.request.closeable." . $email_format,
1029                     undef,
1030                     {
1031                         closeurl => $closeurl,
1032                         urlauth  => $urlauth,
1033                     }
1034                   );
1035     }
1036
1037     my $fromemail;
1038     my $bogus_note = '';
1039     if ($sp->{_cat}->{'replyaddress'}) {
1040         my $miniauth = mini_auth($sp);
1041         $fromemail = $sp->{_cat}->{'replyaddress'};
1042         # insert mini-auth stuff:
1043         my $rep = "+${spid}z$miniauth\@";
1044         $fromemail =~ s/\@/$rep/;
1045     } else {
1046         $fromemail = $LJ::BOGUS_EMAIL;
1047         $bogus_note = LJ::Lang::get_text (
1048                         $lang,
1049                         'notification.support.bogus_email.note'
1050                       );
1051     }
1052
1053     my $requester = LJ::Lang::get_text ($lang,'notification.support.requester');
1054     my $reply_type = LJ::Lang::get_text ( $lang, "support.request." . $type );
1055     my $subject = LJ::Lang::get_text (
1056                     $lang,
1057                     'notification.support.reply.request.subject',
1058                     undef,
1059                     {
1060                         subject     => $sp->{'subject'},
1061                         request_id  => $spid
1062                     }
1063                   );
1064     my $body = LJ::Lang::get_text (
1065                         $lang,
1066                         'notification.support.reply.request.body.plain',
1067                         undef,
1068                         {
1069                             username    => $username ||
1070                                            $sp->{'reqname'} ||
1071                                            $requester,
1072                             reply_type  => $reply_type,
1073                             subject     => $sp->{'subject'},
1074                             request_id  => $spid,
1075                             urlauth     => $urlauth,
1076                             faqref      => $faqref,
1077                             reply_text  => $res->{'message'},
1078                             closeable   => $closeable,
1079                             sitename    => $LJ::SITENAMESHORT,
1080                             siteroot    => $LJ::SITEROOT,
1081                             bogus_note  => $bogus_note,
1082                         }
1083                 );
1084     if ($html) {
1085         my $html_body = LJ::Lang::get_text (
1086                             $lang,
1087                             'notification.support.reply.request.body.html',
1088                             undef,
1089                             {
1090                                 username    => $ljuser ||
1091                                                $sp->{'reqname'} ||
1092                                                $requester,
1093                                 reply_type  => $reply_type,
1094                                 subject     => $sp->{'subject'},
1095                                 request_id  => $spid,
1096                                 urlauth     => $urlauth,
1097                                 faqref      => $faqref,
1098                                 reply_text  => LJ::html_newlines(
1099                                                   LJ::ehtml($res->{'message'})),
1100                                 closeable   => $closeable,
1101                                 sitename    => $LJ::SITENAMESHORT,
1102                                 siteroot    => $LJ::SITEROOT,
1103                                 bogus_note  => $bogus_note,
1104                             }
1105                         );
1106         LJ::send_mail({
1107             to          => $email,
1108             from        => $fromemail,
1109             fromname    => "$LJ::SITENAMESHORT Support",
1110             charset     => 'utf-8',
1111             subject     => $subject,
1112             body        => $body,
1113             html        => $html_body,
1114         });
1115     } else {
1116         LJ::send_mail({
1117             to          => $email,
1118             from        => $fromemail,
1119             fromname    => "$LJ::SITENAMESHORT Support",
1120             charset     => 'utf-8',
1121             subject     => $subject,
1122             body        => $body,
1123         });
1124     }
1125
1126
1127 }
1128
1129 sub mini_auth
1130 {
1131     my $sp = shift;
1132     return substr($sp->{'authcode'}, 0, 4);
1133 }
1134
1135 # <LJFUNC>
1136 # name: LJ::Support::get_support_by_daterange
1137 # des: Get all the [dbtable[support]] rows based on a date range.
1138 # args: date1, date2
1139 # des-date1: YYYY-MM-DD of beginning date of range
1140 # des-date2: YYYY-MM-DD of ending date of range
1141 # returns: HashRef of support rows by support id
1142 # </LJFUNC>
1143 sub get_support_by_daterange {
1144     my ($date1, $date2) = @_;
1145
1146     # Build the query out based on the dates specified
1147     my $time1 = LJ::TimeUtil->mysqldate_to_time($date1);
1148     my $time2 = LJ::TimeUtil->mysqldate_to_time($date2) + $SECONDS_IN_DAY;
1149
1150     # Convert from times to IDs because support.timecreate isn't indexed
1151     my ($start_id, $end_id) = LJ::DB::time_range_to_ids
1152                                    (table       => 'support',
1153                                     roles       => ['slow'],
1154                                     idcol       => 'spid',
1155                                     timecol     => 'timecreate',
1156                                     starttime   => $time1,
1157                                     endtime     => $time2,
1158                                    );
1159
1160     # Generate the SQL.  Include time fields to be safe
1161     my $sql = "SELECT * FROM support "
1162             . "WHERE spid >= ? AND spid <= ? "
1163             . "  AND timecreate >= ? AND timecreate < ?";
1164
1165     # Get the results from the database
1166     my $dbh = LJ::Support::slow_query_dbh()
1167         or return "Database unavailable";
1168     my $sth = $dbh->prepare($sql);
1169     $sth->execute($start_id, $end_id, $time1, $time2);
1170     die $dbh->errstr if $dbh->err;
1171     $sth->{mysql_use_result} = 1;
1172
1173     # Loop over the results, generating a hash by Support ID
1174     my %result_hash = ();
1175     while (my $row = $sth->fetchrow_hashref) {
1176         $result_hash{$row->{spid}} = $row;
1177     }
1178
1179     return \%result_hash;
1180 }
1181
1182 # <LJFUNC>
1183 # name: LJ::Support::get_support_by_ids
1184 # des: Get all the [dbtable[support]] rows based on a list of Support IDs
1185 # args: support_ids_ref
1186 # des-support_ids_ref: ArrayRef of Support IDs.
1187 # returns: ArrayRef of support rows
1188 # </LJFUNC>
1189 sub get_support_by_ids {
1190     my ($support_ids_ref) = @_;
1191     my %result_hash = ();
1192     return \%result_hash unless @$support_ids_ref;
1193
1194     # Build the query out based on the dates specified
1195     my $support_ids_bind = join ',', map { '?' } @$support_ids_ref;
1196     my $sql = "SELECT * FROM support "
1197             . "WHERE spid IN ($support_ids_bind)";
1198
1199     # Get the results from the database
1200     my $dbh = LJ::Support::slow_query_dbh()
1201         or return "Database unavailable";
1202     my $sth = $dbh->prepare($sql);
1203     $sth->execute(@$support_ids_ref);
1204     die $dbh->errstr if $dbh->err;
1205     $sth->{mysql_use_result} = 1;
1206
1207     # Loop over the results, generating a hash by Support ID
1208     while (my $row = $sth->fetchrow_hashref) {
1209         $result_hash{$row->{spid}} = $row;
1210     }
1211
1212     return \%result_hash;
1213 }
1214
1215 # <LJFUNC>
1216 # name: LJ::Support::get_supportlogs
1217 # des: Get all the [dbtable[supportlog]] rows for a list of Support IDs.
1218 # args: support_ids_ref
1219 # des-support_ids_ref: ArrayRef of Support IDs.
1220 # returns: HashRef of supportlog rows by support id.
1221 # </LJFUNC>
1222 sub get_supportlogs {
1223     my $support_ids_ref = shift;
1224     my %result_hash = ();
1225     return \%result_hash unless @$support_ids_ref;
1226
1227     # Build the query out based on the dates specified
1228     my $spid_bind = join ',', map { '?' } @$support_ids_ref;
1229     my $sql = "SELECT * FROM supportlog WHERE spid IN ($spid_bind) ";
1230
1231     # Get the results from the database
1232     my $dbh = LJ::Support::slow_query_dbh()
1233         or return "Database unavailable";
1234     my $sth = $dbh->prepare($sql);
1235     $sth->execute(@$support_ids_ref);
1236     die $dbh->errstr if $dbh->err;
1237     $sth->{mysql_use_result} = 1;
1238
1239     # Loop over the results, generating a hash by Support ID
1240     while (my $row = $sth->fetchrow_hashref) {
1241         push @{$result_hash{$row->{spid}}}, $row;
1242     }
1243
1244     return \%result_hash;
1245 }
1246
1247 # <LJFUNC>
1248 # name: LJ::Support::get_touch_supportlogs_by_user_and_date
1249 # des: Get all touch (non-req) supportlogs based on User ID and Date Range.
1250 # args: userid, date1, date2
1251 # des-userid: User ID to filter on, or Undef for all users.
1252 # des-date1: YYYY-MM-DD of beginning date of range
1253 # des-date2: YYYY-MM-DD of ending date of range
1254 # returns: Support HashRef of Support Logs Array, sorted by log time.
1255 # </LJFUNC>
1256 sub get_touch_supportlogs_by_user_and_date {
1257     my ($userid, $date1, $date2) = @_;
1258
1259     # Build the query out based on the dates specified
1260     my $time1 = LJ::TimeUtil->mysqldate_to_time($date1);
1261     my $time2 = LJ::TimeUtil->mysqldate_to_time($date2) + $SECONDS_IN_DAY;
1262
1263     # Convert from times to IDs because supportlog.timelogged isn't indexed
1264     my ($start_id, $end_id) = LJ::DB::time_range_to_ids
1265                                    (table       => 'supportlog',
1266                                     roles       => \@LJ::SUPPORT_SLOW_ROLES,
1267                                     idcol       => 'splid',
1268                                     timecol     => 'timelogged',
1269                                     starttime   => $time1,
1270                                     endtime     => $time2,
1271                                    );
1272
1273     # Generate the SQL.  Include time fields to be safe
1274     my $sql = "SELECT * FROM supportlog"
1275             . " WHERE type <> 'req' "
1276             . " AND splid >= ? AND splid <= ?"
1277             . " AND timelogged >= ? AND timelogged < ?"
1278             . ($userid ? " AND userid = ?" : '');
1279
1280     # Get the results from the database
1281     my $dbh = LJ::Support::slow_query_dbh()
1282         or return "Database unavailable";
1283     my $sth = $dbh->prepare($sql);
1284     my @parms = ($start_id, $end_id, $time1, $time2);
1285     push @parms, $userid if $userid;
1286     $sth->execute(@parms);
1287     die $dbh->errstr if $dbh->err;
1288     $sth->{mysql_use_result} = 1;
1289
1290     # Store the query results in an array
1291     my @results;
1292     while (my $row = $sth->fetchrow_hashref) {
1293         push @results, $row;
1294     }
1295
1296     # Sort logs by time
1297     @results = sort {$a->{timelogged} <=> $b->{timelogged}} @results;
1298
1299     # Loop over the results, generating an array that's hashed by Support ID
1300     my %result_hash = ();
1301     foreach my $row (@results) {
1302         push @{$result_hash{$row->{spid}}}, $row;
1303     }
1304
1305     return \%result_hash;
1306 }
1307
1308 # <LJFUNC>
1309 # name: LJ::Support::get_previous_screened_replies
1310 # des: Get screened replies between approve one and the previous answer
1311 # args: splid
1312 # splid: newly approved reply
1313 # returns: hashref  {splid => userid}
1314 # </LJFUNC>
1315 sub get_previous_screened_replies {
1316     my $splid = shift;
1317
1318     my $resp           = LJ::Support::load_response($splid);
1319     my $approved_splid = LJ::Support::response_prop($splid, 'approved');
1320     my $spid           = $resp->{spid};
1321
1322
1323
1324     my $dbr = LJ::get_db_reader();
1325     my $touches = $dbr->selectall_arrayref(
1326                             qq{
1327                                 SELECT   splid, type, userid
1328                                 FROM     supportlog
1329                                 WHERE    spid = ? AND
1330                                          splid < ?
1331                                 ORDER BY splid DESC
1332                             },
1333                             { Slice => {} },
1334                             $spid,
1335                             $splid
1336                         );
1337     my %res;
1338     foreach my $touch (@$touches) {
1339         if ($touch->{type} eq 'screened') {
1340             $res{$touch->{splid}} = $touch->{userid};
1341         }
1342         elsif (($touch->{type} eq 'internal') ||
1343                ($touch->{splid} == $approved_splid)) {
1344             next;
1345         } else {
1346             last;
1347         }
1348     }
1349
1350     return \%res;
1351 }
1352
1353 # <LJFUNC>
1354 # name: LJ::Support::get_stock_answer_catid
1355 # des: Get support category id for specified stock answer
1356 # args: ansid
1357 # ansid: id of support stock answer we are looking after
1358 # returns: spcatid or undef
1359 # </LJFUNC>
1360 sub get_stock_answer_catid {
1361     my $ansid = shift;
1362
1363     my $dbr = LJ::get_db_reader();
1364     my $res = $dbr->selectrow_hashref(
1365         qq(
1366             SELECT spcatid
1367             FROM   support_answers
1368             WHERE  ansid = ?
1369         ),
1370         undef,
1371         $ansid,
1372     );
1373
1374     return $res->{'spcatid'} if $res;
1375
1376     return 0;
1377 }
1378
1379 # <LJFUNC>
1380 # name: LJ::Support::get_latest_screen
1381 # des: Get screened replies between approve one and the previous answer
1382 # args: splid, userid
1383 # splid: id of response we are looking after
1384 # userid: userid of the author
1385 # returns: splid or undef
1386 # </LJFUNC>
1387 sub get_latest_screen {
1388     my ($splid, $userid) = @_;
1389
1390     my $screens = LJ::Support::get_previous_screened_replies($splid);
1391
1392     foreach my $key (sort {$b <=> $a} keys %$screens) {
1393         if ($screens->{$key} eq $userid) {
1394             return $key;
1395         }
1396     }
1397
1398     return undef;
1399 }
1400
1401
1402
1403 # <LJFUNC>
1404 # name:     LJ::Support::get_touches_by_type
1405 # des:      Get support request replies of particular type
1406 # args:     spid, type
1407 # des-spid: support request id
1408 # des-type: reply type
1409 # returns:  hashref  {splid => userid}
1410 # </LJFUNC>
1411 sub get_touches_by_type {
1412     my ($spid,$type) = @_;
1413
1414     my $dbr = LJ::get_db_reader();
1415     my $touches = $dbr->selectall_arrayref(
1416                             qq{
1417                                 SELECT   splid, userid
1418                                 FROM     supportlog
1419                                 WHERE    spid = ? AND
1420                                          type = ?
1421                                 ORDER BY splid DESC
1422                             },
1423                             { Slice => {} },
1424                             $spid,
1425                             $type
1426                         );
1427     my %res;
1428     foreach my $touch (@$touches) {
1429         $res{$touch->{splid}} = $touch->{userid};
1430     }
1431
1432     return \%res;
1433 }
1434
1435 # <LJFUNC>
1436 # name:     LJ::Support::is_helper
1437 # des:      Check if user answered to the request
1438 # args:     u, spid
1439 # des-spid: support request id
1440 # des-u:    LJ::user object
1441 # returns:  boolean
1442 # </LJFUNC>
1443 sub is_helper {
1444     my ($u,$spid) = @_;
1445
1446     my $dbr = LJ::get_db_reader();
1447     my ($splid) = $dbr->selectrow_array(
1448                                     qq{
1449                                         SELECT splid
1450                                         FROM   supportlog
1451                                         WHERE  spid = ? AND
1452                                                userid = ? AND
1453                                                type='answer'
1454                                     },
1455                                     undef,
1456                                     $spid,
1457                                     $u->id,
1458     );
1459
1460     return 1 if $splid;
1461
1462     return 0;
1463 }
1464
1465 sub support_notify {
1466     my $params = shift;
1467     my $sclient = LJ::theschwartz() or
1468         return 0;
1469
1470     my $h = $sclient->insert("LJ::Worker::SupportNotify", $params);
1471     return $h ? 1 : 0;
1472 }
1473
1474 package LJ::Worker::SupportNotify;
1475 use base 'TheSchwartz::Worker';
1476
1477 use LJ::Text;
1478
1479 sub work {
1480     my ($class, $job) = @_;
1481     my $a = $job->arg;
1482
1483     # load basic stuff common to both paths
1484     my $type = $a->{type};
1485     my $spid = $a->{spid}+0;
1486     my $load_body = $type eq 'new' ? 1 : 0;
1487     my $sp = LJ::Support::load_request($spid, $load_body, { force => 1 }); # force from master
1488     my $cat = $sp->{_cat};
1489     # we're only going to be reading anyway, but these jobs
1490     # sometimes get processed faster than replication allows,
1491     # causing the message not to load from the reader
1492     my $dbr = LJ::get_db_writer();
1493
1494     # now branch a bit to select the right user information
1495     my $level = $type eq 'new' ? "'new', 'all'" : "'all'";
1496     my $data = $dbr->selectcol_arrayref("SELECT userid FROM supportnotify " .
1497                                         "WHERE spcatid=? AND level IN ($level)", undef, $cat->{spcatid});
1498     my $userids = LJ::load_userids(@$data);
1499
1500     # prepare the email
1501     my $body;
1502     my @emails;
1503
1504     my $req_subject = LJ::Text->fix_utf8($sp->{'subject'});
1505     if ($type eq 'new') {
1506         $body = "A $LJ::SITENAME support request has been submitted regarding the following:\n\n";
1507         $body .= "Category: $cat->{catname}\n";
1508         $body .= "Subject:  $req_subject\n\n";
1509         $body .= "You can track its progress or add information here:\n\n";
1510         $body .= "$LJ::SITEROOT/support/see_request.bml?id=$spid";
1511         $body .= "\n\nIf you do not wish to receive notifications of incoming support requests, you may change your notification settings here:\n\n";
1512         $body .= "$LJ::SITEROOT/support/changenotify.bml";
1513         $body .= "\n\n" . "="x70 . "\n\n";
1514         $body .= LJ::Text->fix_utf8($sp->{body});
1515
1516         foreach my $u (values %$userids) {
1517             next unless $u->is_visible;
1518             next unless $u->{status} eq "A";
1519             push @emails, $u->email_raw;
1520         }
1521
1522
1523     } elsif ($type eq 'update') {
1524         # load the response we want to stuff in the email
1525         my ($resp, $rtype, $posterid) =
1526             $dbr->selectrow_array("SELECT message, type, userid FROM supportlog WHERE spid = ? AND splid = ?",
1527                                   undef, $sp->{spid}, $a->{splid}+0);
1528
1529         # build body
1530         $body = "A follow-up to the request regarding \"$req_subject\" has ";
1531         $body .= "been submitted.  You can track its progress or add ";
1532         $body .= "information here:\n\n  ";
1533         $body .= "$LJ::SITEROOT/support/see_request.bml?id=$spid";
1534         $body .= "\n\n" . "="x70 . "\n\n";
1535         $body .= LJ::Text->fix_utf8($resp);
1536
1537         # now see who this should be sent to
1538         foreach my $u (values %$userids) {
1539             next unless $u->is_visible;
1540             next unless $u->{status} eq "A";
1541             next if $posterid == $u->id;
1542             next if $rtype eq 'screened' &&
1543                 !LJ::Support::can_read_screened($cat, $u);
1544             next if $rtype eq 'internal' &&
1545                 !LJ::Support::can_read_internal($cat, $u);
1546             push @emails, $u->email_raw;
1547         }
1548     }
1549
1550     # send the email
1551     LJ::send_mail({
1552         bcc => join(', ', @emails),
1553         from => $LJ::BOGUS_EMAIL,
1554         fromname => "$LJ::SITENAME Support",
1555         charset => 'utf-8',
1556         subject => ($type eq 'update' ? 'Re: ' : '') . "Support Request \#$spid",
1557         body => $body,
1558         wrap => 1,
1559     }) if @emails;
1560
1561     $job->completed;
1562     return 1;
1563 }
1564
1565 sub keep_exit_status_for { 0 }
1566 sub grab_for { 30 }
1567 sub max_retries { 5 }
1568 sub retry_delay {
1569     my ($class, $fails) = @_;
1570     return 30;
1571 }
1572
1573 1;