$where[] = "action like " . Dbl::utf8ci("'% " . sqlq_for_like($row[1]) . "%'");
}
}
if (count($where)) {
$wheres[] = "(" . join(" or ", $where) . ")";
} else {
$Conf->infoMsg("No accounts match “" . htmlspecialchars($_REQUEST["acct"]) . "”.");
$wheres[] = "false";
}
}
if ($str = $_REQUEST["q"]) {
$where = array();
while (($str = ltrim($str)) != "") {
preg_match('/^("[^"]+"?|[^"\\s]+)/s', $str, $m);
$str = substr($str, strlen($m[0]));
$where[] = "action like " . Dbl::utf8ci("'%" . sqlq_for_like($m[0]) . "%'");
}
$wheres[] = "(" . join(" or ", $where) . ")";
}
if (($count = cvtint(@$_REQUEST["n"])) <= 0) {
Conf::msg_error("\"Show <i>n</i> records\" requires a number greater than 0.");
$Eclass["n"] = " error";
$count = $DEFAULT_COUNT;
}
$firstDate = false;
if ($_REQUEST["date"] == "") {
$_REQUEST["date"] = "now";
}
if ($_REQUEST["date"] != "now" && isset($_REQUEST["search"])) {
if (($firstDate = $Conf->parse_time($_REQUEST["date"])) === false) {
Conf::msg_error("“" . htmlspecialchars($_REQUEST["date"]) . "” is not a valid date.");
static function email_authored_papers($email, $reg)
{
$aupapers = array();
$result = Dbl::q("select paperId, authorInformation from Paper where authorInformation like " . Dbl::utf8ci("'%\t" . sqlq_for_like($email) . "\t%'"));
while ($row = PaperInfo::fetch($result, null)) {
foreach ($row->author_list() as $au) {
if (strcasecmp($au->email, $email) == 0) {
$aupapers[] = $row->paperId;
if ($reg && $au->firstName && !get($reg, "firstName")) {
$reg->firstName = $au->firstName;
}
if ($reg && $au->lastName && !get($reg, "lastName")) {
$reg->lastName = $au->lastName;
}
if ($reg && $au->affiliation && !get($reg, "affiliation")) {
$reg->affiliation = $au->affiliation;
}
}
}
}
return $aupapers;
}
function crpmerge_database($old_user, $new_user)
{
global $Conf, $MergeError;
// Now, scan through all the tables that possibly
// specify a contactID and change it from their 2nd
// contactID to their first contactId
$oldid = $old_user->contactId;
$newid = $new_user->contactId;
$Conf->q("lock tables Paper write, ContactInfo write, PaperConflict write, ActionLog write, TopicInterest write, PaperComment write, PaperReview write, PaperReview as B write, PaperReviewPreference write, PaperReviewRefused write, ReviewRequest write, PaperWatch write, ReviewRating write");
crpmergeone("Paper", "leadContactId", $oldid, $newid);
crpmergeone("Paper", "shepherdContactId", $oldid, $newid);
crpmergeone("Paper", "managerContactId", $oldid, $newid);
// paper authorship
$result = $Conf->qe("select paperId, authorInformation from Paper where authorInformation like " . Dbl::utf8ci("'%\t" . sqlq_for_like($old_user->email) . "\t%'"));
$qs = array();
while ($row = PaperInfo::fetch($result, null)) {
foreach ($row->author_list() as $au) {
if (strcasecmp($au->email, $old_user->email) == 0) {
$au->email = $new_user->email;
}
}
$qs[] = "update Paper set authorInformation='" . sqlq($row->parse_author_list()) . "' where paperId={$row->paperId}";
}
foreach ($qs as $q) {
$Conf->qe($q);
}
// ensure uniqueness in PaperConflict
$result = $Conf->qe("select paperId, conflictType from PaperConflict where contactId={$oldid}");
$values = "";
while ($row = edb_row($result)) {
$values .= ", ({$row['0']}, {$newid}, {$row['1']})";
}
if ($values) {
$Conf->qe("insert into PaperConflict (paperId, contactId, conflictType) values " . substr($values, 2) . " on duplicate key update conflictType=greatest(conflictType, values(conflictType))");
}
$Conf->qe("delete from PaperConflict where contactId={$oldid}");
if (($old_user->roles | $new_user->roles) != $new_user->roles) {
$new_user->roles |= $old_user->roles;
$Conf->qe("update ContactInfo set roles={$new_user->roles} where contactId={$newid}");
}
crpmergeone("ActionLog", "contactId", $oldid, $newid);
crpmergeoneignore("TopicInterest", "contactId", $oldid, $newid);
crpmergeone("PaperComment", "contactId", $oldid, $newid);
// archive duplicate reviews
crpmergeoneignore("PaperReview", "contactId", $oldid, $newid);
crpmergeone("PaperReview", "requestedBy", $oldid, $newid);
crpmergeoneignore("PaperReviewPreference", "contactId", $oldid, $newid);
crpmergeone("PaperReviewRefused", "contactId", $oldid, $newid);
crpmergeone("PaperReviewRefused", "requestedBy", $oldid, $newid);
crpmergeone("ReviewRequest", "requestedBy", $oldid, $newid);
crpmergeoneignore("PaperWatch", "contactId", $oldid, $newid);
crpmergeoneignore("ReviewRating", "contactId", $oldid, $newid);
// Remove the old contact record
if ($MergeError == "") {
if (!$Conf->q("delete from ContactInfo where contactId={$oldid}")) {
$MergeError .= $Conf->db_error_html(true);
}
}
$Conf->qe("unlock tables");
// Update PC settings if we need to
if ($old_user->isPC) {
$Conf->invalidateCaches(array("pc" => 1));
}
}
function query($paper_sensitive)
{
global $Conf;
$cols = array();
$where = array("email not regexp '^anonymous[0-9]*\$'");
$joins = array("ContactInfo");
// paper limit
if ($this->need_papers() && isset($this->papersel)) {
$where[] = "Paper.paperId in (" . join(",", $this->papersel) . ")";
}
// paper type limit
if ($this->type == "s") {
$where[] = "Paper.timeSubmitted>0";
} else {
if ($this->type == "unsub") {
$where[] = "Paper.timeSubmitted<=0 and Paper.timeWithdrawn<=0";
} else {
if ($this->type == "dec:any") {
$where[] = "Paper.timeSubmitted>0 and Paper.outcome!=0";
} else {
if ($this->type == "dec:none") {
$where[] = "Paper.timeSubmitted>0 and Paper.outcome=0";
} else {
if ($this->type == "dec:yes") {
$where[] = "Paper.timeSubmitted>0 and Paper.outcome>0";
} else {
if ($this->type == "dec:no") {
$where[] = "Paper.timeSubmitted>0 and Paper.outcome<0";
} else {
if (substr($this->type, 0, 4) == "dec:") {
$nw = count($where);
foreach ($Conf->decision_map() as $dnum => $dname) {
if (strcasecmp($dname, substr($this->type, 4)) == 0) {
$where[] = "Paper.timeSubmitted>0 and Paper.outcome={$dnum}";
break;
}
}
if (count($where) == $nw) {
return false;
}
}
}
}
}
}
}
}
// additional manager limit
if (!isset($this->sel_nonmanager[$this->type]) && !$this->contact->privChair) {
$where[] = "Paper.managerContactId=" . $this->contact->contactId;
}
// reviewer limit
if (!preg_match('_\\A(new|unc|c|allc|)(pc|ext|myext|)rev\\z_', $this->type, $revmatch)) {
$revmatch = false;
}
// build query
if ($this->type == "all") {
$needpaper = $needconflict = $needreview = false;
} else {
if ($this->type == "pc" || substr($this->type, 0, 3) == "pc:") {
$needpaper = $needconflict = $needreview = false;
$where[] = "(ContactInfo.roles&" . Contact::ROLE_PC . ")!=0";
if ($this->type != "pc") {
$where[] = "ContactInfo.contactTags like " . Dbl::utf8ci("'% " . sqlq_for_like(substr($this->type, 3)) . "#%'");
}
} else {
if ($revmatch) {
$needpaper = $needreview = true;
$needconflict = false;
$joins[] = "join Paper";
$joins[] = "join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=ContactInfo.contactId)";
$where[] = "Paper.paperId=PaperReview.paperId";
} else {
if ($this->type == "lead" || $this->type == "shepherd") {
$needpaper = $needconflict = $needreview = true;
$joins[] = "join Paper on (Paper.{$this->type}ContactId=ContactInfo.contactId)";
$joins[] = "left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=ContactInfo.contactId)";
} else {
$needpaper = $needconflict = true;
$needreview = false;
if ($Conf->au_seerev == Conf::AUSEEREV_UNLESSINCOMPLETE) {
$cols[] = "(coalesce(allr.contactId,0)!=0) has_review";
$cols[] = "coalesce(allr.has_outstanding_review,0) has_outstanding_review";
$joins[] = "left join (select contactId, max(if(reviewNeedsSubmit!=0 and timeSubmitted>0,1,0)) has_outstanding_review from PaperReview join Paper on (Paper.paperId=PaperReview.paperId) group by PaperReview.contactId) as allr using (contactId)";
}
$joins[] = "join Paper";
$where[] = "PaperConflict.conflictType>=" . CONFLICT_AUTHOR;
if ($Conf->au_seerev == Conf::AUSEEREV_TAGS) {
$joins[] = "left join (select paperId, group_concat(' ', tag, '#', tagIndex order by tag separator '') as paperTags from PaperTag group by paperId) as PaperTags on (PaperTags.paperId=Paper.paperId)";
$cols[] = "PaperTags.paperTags";
}
}
}
}
}
// reviewer match
if ($revmatch) {
// Submission status
if ($revmatch[1] == "c") {
$where[] = "PaperReview.reviewSubmitted>0";
} else {
if ($revmatch[1] == "unc" || $revmatch[1] == "new") {
$where[] = "PaperReview.reviewSubmitted is null and PaperReview.reviewNeedsSubmit!=0 and Paper.timeSubmitted>0";
}
}
if ($revmatch[1] == "new") {
$where[] = "PaperReview.timeRequested>PaperReview.timeRequestNotified";
}
if ($revmatch[1] == "allc") {
$joins[] = "left join (select contactId, max(if(reviewNeedsSubmit!=0 and timeSubmitted>0,1,0)) anyReviewNeedsSubmit from PaperReview join Paper on (Paper.paperId=PaperReview.paperId) group by contactId) AllReviews on (AllReviews.contactId=ContactInfo.contactId)";
$where[] = "AllReviews.anyReviewNeedsSubmit=0";
}
if ($this->newrev_since) {
$where[] = "PaperReview.timeRequested>={$this->newrev_since}";
}
// Withdrawn papers may not count
if ($revmatch[1] == "") {
$where[] = "(Paper.timeSubmitted>0 or PaperReview.reviewSubmitted>0)";
}
// Review type
if ($revmatch[2] == "ext" || $revmatch[2] == "myext") {
$where[] = "PaperReview.reviewType=" . REVIEW_EXTERNAL;
} else {
if ($revmatch[2] == "pc") {
$where[] = "PaperReview.reviewType>" . REVIEW_EXTERNAL;
}
}
if ($revmatch[2] == "myext") {
$where[] = "PaperReview.requestedBy=" . $this->contact->contactId;
}
}
// query construction
$q = "select ContactInfo.contactId, firstName, lastName, email,\n password, roles, contactTags, preferredEmail, " . ($needconflict ? "PaperConflict.conflictType" : "0 as conflictType");
if ($needpaper) {
$q .= ", Paper.paperId, Paper.title, Paper.abstract,\n Paper.authorInformation, Paper.outcome, Paper.blind,\n Paper.timeSubmitted, Paper.timeWithdrawn,\n Paper.shepherdContactId, Paper.capVersion,\n Paper.managerContactId";
} else {
$q .= ", -1 as paperId";
}
if ($needreview) {
$q .= ", PaperReview.reviewType, PaperReview.reviewType as myReviewType";
}
if ($needconflict) {
$joins[] = "left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=ContactInfo.contactId)";
}
$q .= "\nfrom " . join("\n", $joins) . "\nwhere " . join("\n and ", $where) . "\norder by ";
if (!$needpaper) {
$q .= "email";
} else {
if ($paper_sensitive) {
$q .= "Paper.paperId, email";
} else {
$q .= "email, Paper.paperId";
}
}
return $q;
}
function do_tags()
{
global $Conf, $Me, $papersel;
// check tags
$tagger = new Tagger($Me);
$t1 = array();
$errors = array();
foreach (preg_split('/[\\s,]+/', (string) @$_REQUEST["tag"]) as $t) {
if ($t === "") {
/* nada */
} else {
if (!($t = $tagger->check($t, Tagger::NOPRIVATE))) {
$errors[] = $tagger->error_html;
} else {
if (TagInfo::base($t) === "pc") {
$errors[] = "The “pc” user tag is set automatically for all PC members.";
} else {
$t1[] = $t;
}
}
}
}
if (count($errors)) {
return Conf::msg_error(join("<br>", $errors));
} else {
if (!count($t1)) {
return $Conf->warnMsg("Nothing to do.");
}
}
// modify database
Dbl::qe("lock tables ContactInfo write");
Conf::$no_invalidate_caches = true;
$users = array();
if ($_REQUEST["tagtype"] === "s") {
// erase existing tags
$likes = array();
$removes = array();
foreach ($t1 as $t) {
list($tag, $index) = TagInfo::split_index($t);
$removes[] = $t;
$likes[] = "contactTags like " . Dbl::utf8ci("'% " . sqlq_for_like($tag) . "#%'");
}
foreach (Dbl::fetch_first_columns(Dbl::qe("select contactId from ContactInfo where " . join(" or ", $likes))) as $cid) {
$users[(int) $cid] = (object) array("id" => (int) $cid, "add_tags" => [], "remove_tags" => $removes);
}
}
// account for request
$key = $_REQUEST["tagtype"] === "d" ? "remove_tags" : "add_tags";
foreach ($papersel as $cid) {
if (!isset($users[(int) $cid])) {
$users[(int) $cid] = (object) array("id" => (int) $cid, "add_tags" => [], "remove_tags" => []);
}
$users[(int) $cid]->{$key} = array_merge($users[(int) $cid]->{$key}, $t1);
}
// apply modifications
foreach ($users as $cid => $cj) {
$us = new UserStatus(array("send_email" => false));
if (!$us->save($cj)) {
$errors = array_merge($errors, $us->error_messages());
}
}
Dbl::qe("unlock tables");
Conf::$no_invalidate_caches = false;
$Conf->invalidateCaches(["pc" => true]);
// report
if (!count($errors)) {
$Conf->confirmMsg("Tags saved.");
redirectSelf(array("tagact" => null, "tag" => null));
} else {
Conf::msg_error(join("<br>", $errors));
}
}
private function _clauseTermSet(&$t, $sqi, &$f)
{
$tt = $t->type;
$thistab = null;
// collect columns
if ($tt === "ti") {
$sqi->add_column("title", "Paper.title");
$this->_clauseTermSetField($t, "title", $sqi, $f);
} else {
if ($tt === "ab") {
$sqi->add_column("abstract", "Paper.abstract");
$this->_clauseTermSetField($t, "abstract", $sqi, $f);
} else {
if ($tt === "au") {
$sqi->add_column("authorInformation", "Paper.authorInformation");
$this->_clauseTermSetField($t, "authorInformation", $sqi, $f);
} else {
if ($tt === "co") {
$sqi->add_column("collaborators", "Paper.collaborators");
$this->_clauseTermSetField($t, "collaborators", $sqi, $f);
} else {
if ($tt === "au_cid") {
$this->_clauseTermSetTable($t, $t->value, null, "AuthorConflict", "PaperConflict", "contactId", " and " . $this->contact->actAuthorSql("%"), $sqi, $f);
} else {
if ($tt === "re") {
$f[] = $this->_clauseTermSetReviews($t, $sqi);
} else {
if ($tt === "revpref") {
$f[] = $this->_clauseTermSetRevpref($t, $sqi);
} else {
if ($tt === "conflict") {
$this->_clauseTermSetTable($t, $t->value->contact_set(), $t->value->countexpr(), "Conflict", "PaperConflict", "contactId", "", $sqi, $f);
} else {
if ($tt === "cmt") {
if ($t->value->has_contacts()) {
$thistab = "Comments_" . count($sqi->tables);
} else {
$rtype = $t->flags & (self::F_ALLOWCOMMENT | self::F_ALLOWRESPONSE | self::F_AUTHORCOMMENT | self::F_ALLOWDRAFT | self::F_REQUIREDRAFT);
$thistab = "Numcomments_" . $rtype;
if (isset($t->commentRound)) {
$thistab .= "_" . $t->commentRound;
}
}
$f[] = $this->_clauseTermSetComments($thistab, $t->value->contact_match_sql("contactId"), $t, $sqi);
} else {
if ($tt === "cmttag") {
$thistab = "TaggedComments_" . count($sqi->tables);
if ($t->value->tag === "any") {
$match = "is not null";
} else {
$match = "like " . Dbl::utf8ci("'% " . sqlq($t->value->tag) . " %'");
}
$f[] = $this->_clauseTermSetComments($thistab, "commentTags {$match}", $t, $sqi);
} else {
if ($tt === "pn") {
$q = array();
if (count($t->value[0])) {
$q[] = "Paper.paperId in (" . join(",", $t->value[0]) . ")";
}
if (count($t->value[1])) {
$q[] = "Paper.paperId not in (" . join(",", $t->value[1]) . ")";
}
if (!count($q)) {
$q[] = "false";
}
$f[] = "(" . join(" and ", $q) . ")";
} else {
if ($tt === "pf") {
$q = array();
$this->_clauseTermSetFlags($t, $sqi, $q);
for ($i = 0; $i < count($t->value); $i += 2) {
if (is_array($t->value[$i + 1])) {
$q[] = "Paper." . $t->value[$i] . " in (" . join(",", $t->value[$i + 1]) . ")";
} else {
$q[] = "Paper." . $t->value[$i] . $t->value[$i + 1];
}
}
$f[] = "(" . join(" and ", $q) . ")";
for ($i = 0; $i < count($t->value); $i += 2) {
$sqi->add_column($t->value[$i], "Paper." . $t->value[$i]);
}
} else {
if ($tt === "tag") {
$extra = "";
for ($i = 1; $i < count($t->value); ++$i) {
$extra .= " and %.tagIndex" . $t->value[$i];
}
$this->_clauseTermSetTable($t, $t->value[0], null, "Tag", "PaperTag", "tag", $extra, $sqi, $f);
} else {
if ($tt === "topic") {
$this->_clauseTermSetTable($t, $t->value, null, "Topic", "PaperTopic", "topicId", "", $sqi, $f);
} else {
if ($tt === "option") {
// expanded from _clauseTermSetTable
$q = array();
$optionNum = count($sqi->tables);
$this->_clauseTermSetFlags($t, $sqi, $q);
$thistab = "Option_" . $optionNum;
$t->link = $thistab . "_x";
if ($t->value->option->type == 'text') {
$sqi->add_table($thistab, array("left join", "(select paperId, data d from PaperOption where optionId=" . $t->value->option->id . " group by paperId)"));
$sqi->add_column($thistab . "_x", "LOWER(CONVERT(coalesce({$thistab}.d,0) USING utf8))" . $t->value->table_matcher());
$q[] = $sqi->columns[$t->link];
} else {
$sqi->add_table($thistab, array("left join", "(select paperId, max(value) v from PaperOption where optionId=" . $t->value->option->id . " group by paperId)"));
$sqi->add_column($thistab . "_x", "coalesce({$thistab}.v,0)" . $t->value->table_matcher());
$q[] = $sqi->columns[$t->link];
}
$f[] = "(" . join(" and ", $q) . ")";
} else {
if ($tt === "formula") {
$q = array("true");
$this->_clauseTermSetFlags($t, $sqi, $q);
$t->value->add_query_options($this->_query_options, $this->contact);
if (!$t->link) {
$t->link = $t->value->compile_function($this->contact);
}
$f[] = "(" . join(" and ", $q) . ")";
} else {
if ($tt === "not") {
$ff = array();
$sqi->negated = !$sqi->negated;
$this->_clauseTermSet($t->value[0], $sqi, $ff);
$sqi->negated = !$sqi->negated;
if (!count($ff)) {
$ff[] = "true";
}
$f[] = "not (" . join(" or ", $ff) . ")";
} else {
if ($tt === "and" || $tt === "and2") {
$ff = array();
foreach ($t->value as $subt) {
$this->_clauseTermSet($subt, $sqi, $ff);
}
if (!count($ff)) {
$ff[] = "false";
}
$f[] = "(" . join(" and ", $ff) . ")";
} else {
if ($tt === "or" || $tt === "then") {
$ff = array();
foreach ($t->value as $subt) {
$this->_clauseTermSet($subt, $sqi, $ff);
}
if (!count($ff)) {
$ff[] = "false";
}
$f[] = "(" . join(" or ", $ff) . ")";
} else {
if ($tt === "f") {
$f[] = "false";
} else {
if ($tt === "t") {
$f[] = "true";
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
function rows($listname)
{
// PC tags
$queryOptions = array();
if (str_starts_with($listname, "#")) {
$queryOptions["where"] = "(u.contactTags like " . Dbl::utf8ci("'% " . sqlq_for_like(substr($listname, 1)) . "#%'") . ")";
$listname = "pc";
}
// get paper list
if (!($baseFieldId = $this->listFields($listname))) {
Conf::msg_error("There is no people list query named “" . htmlspecialchars($listname) . "”.");
return null;
}
$this->limit = array_shift($baseFieldId);
// run query
return $this->_rows($queryOptions);
}