static function enable($ids, $contact) { global $Conf; $result = $Conf->qe_raw("update ContactInfo set disabled=1 where contactId" . sql_in_numeric_set($ids) . " and password='' and contactId!=" . $contact->contactId); $result = Dbl::qe("update ContactInfo set disabled=0 where contactId" . sql_in_numeric_set($ids) . " and contactId!=" . $contact->contactId); if ($result && $result->affected_rows) { return self::modify_password_mail("password='' and contactId!=" . $contact->contactId, true, "create", $ids); } else { if ($result) { return (object) array("ok" => true, "warnings" => array("Those accounts were already enabled.")); } else { return (object) array("error" => true); } } }
function assign_paper_pc($pids, $type, $reviewer, $extra = array()) { global $Conf; // check arguments assert($type == "lead" || $type == "shepherd" || $type == "manager"); if ($reviewer) { $revcid = is_object($reviewer) ? $reviewer->contactId : $reviewer; } else { $revcid = 0; } assert(is_int($revcid)); if (!is_array($pids)) { $pids = array($pids); } $px = array(); foreach ($pids as $p) { assert(is_object($p) && is_numeric($p->paperId) || is_numeric($p)); $px[] = (int) (is_object($p) ? $p->paperId : $p); } // make assignments if (isset($extra["old_cid"])) { $result = Dbl::qe("update Paper set {$type}ContactId=? where paperId" . sql_in_numeric_set($px) . " and {$type}ContactId=?", $revcid, $extra["old_cid"]); } else { $result = Dbl::qe("update Paper set {$type}ContactId=? where paperId" . sql_in_numeric_set($px), $revcid); } // log, update settings if ($result && $result->affected_rows) { $this->log_activity_for($revcid, "Set {$type}", $px); if (($type == "lead" || $type == "shepherd") && !$revcid != !$Conf->setting("paperlead")) { $Conf->update_paperlead_setting(); } if ($type == "manager" && !$revcid != !$Conf->setting("papermanager")) { $Conf->update_papermanager_setting(); } return true; } else { return false; } }
function sql_not_in_numeric_set($set) { $sql = sql_in_numeric_set($set); return ($sql[0] === "=" ? "!" : " not") . $sql; }
function paperselPredicate($papersel) { return "ContactInfo.contactId" . sql_in_numeric_set($papersel); }
public function sql_predicate() { return sql_in_numeric_set($this->sel); }
function paperQuery($contact, $options = array()) { // Options: // "paperId" => $pid Only paperId $pid (if array, any of those) // "reviewId" => $rid Only paper reviewed by $rid // "commentId" => $c Only paper where comment is $c // "finalized" Only submitted papers // "unsub" Only unsubmitted papers // "accepted" Only accepted papers // "active" Only nonwithdrawn papers // "author" Only papers authored by $contactId // "myReviewRequests" Only reviews requested by $contactId // "myReviews" All reviews authored by $contactId // "myOutstandingReviews" All unsubmitted reviews auth by $contactId // "myReviewsOpt" myReviews, + include papers not yet reviewed // "allReviews" All reviews (multiple rows per paper) // "allReviewScores" All review scores (multiple rows per paper) // "allComments" All comments (multiple rows per paper) // "reviewerName" Include reviewer names // "commenterName" Include commenter names // "reviewer" => $cid Include reviewerConflictType/reviewerReviewType // "tags" Include paperTags // "tagIndex" => $tag Include tagIndex of named tag // "tagIndex" => tag array -- include tagIndex, tagIndex1, ... // "topics" // "options" // "scores" => array(fields to score) // "assignments" // "order" => $sql $sql is SQL 'order by' clause (or empty) global $Opt; $reviewerQuery = isset($options["myReviews"]) || isset($options["allReviews"]) || isset($options["myReviewRequests"]) || isset($options["myReviewsOpt"]) || isset($options["myOutstandingReviews"]); $allReviewerQuery = isset($options["allReviews"]) || isset($options["allReviewScores"]); $scoresQuery = !$reviewerQuery && isset($options["allReviewScores"]); if (is_object($contact)) { $contactId = $contact->contactId; } else { $contactId = (int) $contact; $contact = null; } if (isset($options["reviewer"]) && is_object($options["reviewer"])) { $reviewerContactId = $options["reviewer"]->contactId; } else { if (isset($options["reviewer"])) { $reviewerContactId = $options["reviewer"]; } else { $reviewerContactId = $contactId; } } if (get($options, "author")) { $myPaperReview = null; } else { if ($allReviewerQuery) { $myPaperReview = "MyPaperReview"; } else { $myPaperReview = "PaperReview"; } } // paper selection $paperset = array(); if (isset($options["paperId"])) { $paperset[] = self::_cvt_numeric_set($options["paperId"]); } if (isset($options["reviewId"])) { if (is_numeric($options["reviewId"])) { $result = Dbl::qe("select paperId from PaperReview where reviewId=" . $options["reviewId"]); $paperset[] = self::_cvt_numeric_set(edb_first_columns($result)); } else { if (preg_match('/^(\\d+)([A-Z][A-Z]?)$/i', $options["reviewId"], $m)) { $result = Dbl::qe("select paperId from PaperReview where paperId={$m['1']} and reviewOrdinal=" . parseReviewOrdinal($m[2])); $paperset[] = self::_cvt_numeric_set(edb_first_columns($result)); } else { $paperset[] = array(); } } } if (isset($options["commentId"])) { $result = Dbl::qe("select paperId from PaperComment where commentId" . sql_in_numeric_set(self::_cvt_numeric_set($options["commentId"]))); $paperset[] = self::_cvt_numeric_set(edb_first_columns($result)); } if (count($paperset) > 1) { $paperset = array(call_user_func_array("array_intersect", $paperset)); } $papersel = ""; if (count($paperset)) { $papersel = "paperId" . sql_in_numeric_set($paperset[0]) . " and "; } // prepare query: basic tables $where = array(); $joins = array("Paper"); $cols = array("Paper.*, PaperConflict.conflictType"); $aujoinwhere = null; if (get($options, "author") && $contact && ($aujoinwhere = $contact->actAuthorSql("PaperConflict", true))) { $where[] = $aujoinwhere; } if (get($options, "author") && !$aujoinwhere) { $joins[] = "join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId={$contactId} and PaperConflict.conflictType>=" . CONFLICT_AUTHOR . ")"; } else { $joins[] = "left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId={$contactId})"; } // my review $qr = ""; if ($contact && ($tokens = $contact->review_tokens())) { $qr = " or PaperReview.reviewToken in (" . join(", ", $tokens) . ")"; } if (get($options, "myReviewRequests")) { $joins[] = "join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.requestedBy={$contactId} and PaperReview.reviewType=" . REVIEW_EXTERNAL . ")"; } else { if (get($options, "myReviews")) { $joins[] = "join PaperReview on (PaperReview.paperId=Paper.paperId and (PaperReview.contactId={$contactId}{$qr}))"; } else { if (get($options, "myOutstandingReviews")) { $joins[] = "join PaperReview on (PaperReview.paperId=Paper.paperId and (PaperReview.contactId={$contactId}{$qr}) and PaperReview.reviewNeedsSubmit!=0)"; } else { if (get($options, "myReviewsOpt")) { $joins[] = "left join PaperReview on (PaperReview.paperId=Paper.paperId and (PaperReview.contactId={$contactId}{$qr}))"; } else { if (get($options, "allReviews") || get($options, "allReviewScores")) { $x = get($options, "reviewLimitSql") ? " and (" . $options["reviewLimitSql"] . ")" : ""; $joins[] = "join PaperReview on (PaperReview.paperId=Paper.paperId{$x})"; } else { if (!get($options, "author")) { $joins[] = "left join PaperReview on (PaperReview.paperId=Paper.paperId and (PaperReview.contactId={$contactId}{$qr}))"; } } } } } } // started reviews if (get($options, "startedReviewCount")) { $joins[] = "left join (select paperId, count(*) count from PaperReview where {$papersel}(reviewSubmitted or reviewNeedsSubmit>0) group by paperId) R_started on (R_started.paperId=Paper.paperId)"; $cols[] = "coalesce(R_started.count,0) startedReviewCount"; } // submitted reviews $j = "select paperId, count(*) count"; $before_ncols = count($cols); if (get($options, "startedReviewCount")) { $cols[] = "coalesce(R_submitted.count,0) reviewCount"; } if (get($options, "scores")) { foreach ($options["scores"] as $fid) { $cols[] = "R_submitted.{$fid}Scores"; if ($myPaperReview) { $cols[] = "{$myPaperReview}.{$fid}"; } $j .= ", group_concat({$fid} order by reviewId) {$fid}Scores"; } } if (get($options, "reviewTypes") || get($options, "reviewIdentities")) { $cols[] = "R_submitted.reviewTypes"; $j .= ", group_concat(reviewType order by reviewId) reviewTypes"; } if (get($options, "reviewIdentities")) { $cols[] = "R_submitted.reviewRequestedBys"; $j .= ", group_concat(requestedBy order by reviewId) reviewRequestedBys"; if ($this->review_blindness() == self::BLIND_OPTIONAL) { $cols[] = "R_submitted.reviewBlinds"; $j .= ", group_concat(reviewBlind order by reviewId) reviewBlinds"; } if ($contact && $contact->review_tokens()) { $cols[] = "R_submitted.reviewTokens"; $j .= ", group_concat(reviewToken order by reviewId) reviewTokens"; } } if (get($options, "reviewRounds")) { $cols[] = "R_submitted.reviewRounds"; $j .= ", group_concat(reviewRound order by reviewId) reviewRounds"; } if (get($options, "reviewWordCounts") && $this->sversion >= 99) { $cols[] = "R_submitted.reviewWordCounts"; $j .= ", group_concat(reviewWordCount order by reviewId) reviewWordCounts"; } if (get($options, "reviewOrdinals")) { $cols[] = "R_submitted.reviewOrdinals"; $j .= ", group_concat(reviewOrdinal order by reviewId) reviewOrdinals"; } if (get($options, "reviewTypes") || get($options, "scores") || get($options, "reviewContactIds") || get($options, "reviewOrdinals") || get($options, "reviewIdentities")) { $cols[] = "R_submitted.reviewContactIds"; $j .= ", group_concat(contactId order by reviewId) reviewContactIds"; } if (count($cols) != $before_ncols) { $joins[] = "left join ({$j} from PaperReview where {$papersel}reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId)"; } // assignments if (get($options, "assignments")) { $j = "select paperId, group_concat(contactId order by reviewId) allReviewContactIds, group_concat(reviewType order by reviewId) allReviewTypes, group_concat(reviewRound order by reviewId) allReviewRounds"; $cols[] = "R_all.allReviewContactIds, R_all.allReviewTypes, R_all.allReviewRounds"; $joins[] = "left join ({$j} from PaperReview where {$papersel}true group by paperId) R_all on (R_all.paperId=Paper.paperId)"; } // fields if (get($options, "author")) { $cols[] = "null reviewType, null reviewId, null myReviewType"; } else { // see also papercolumn.php array_push($cols, "PaperReview.reviewType, PaperReview.reviewId", "PaperReview.reviewModified, PaperReview.reviewSubmitted", "PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal", "PaperReview.reviewBlind, PaperReview.reviewToken", "PaperReview.contactId as reviewContactId, PaperReview.requestedBy", "max({$myPaperReview}.reviewType) as myReviewType", "max({$myPaperReview}.reviewSubmitted) as myReviewSubmitted", "min({$myPaperReview}.reviewNeedsSubmit) as myReviewNeedsSubmit", "{$myPaperReview}.contactId as myReviewContactId", "PaperReview.reviewRound"); } if ($reviewerQuery || $scoresQuery) { $cols[] = "PaperReview.reviewEditVersion as reviewEditVersion"; $cols[] = ($this->sversion >= 105 ? "PaperReview.reviewFormat" : "null") . " as reviewFormat"; foreach (ReviewForm::all_fields() as $f) { if ($reviewerQuery || $f->has_options) { $cols[] = "PaperReview.{$f->id} as {$f->id}"; } } } if ($myPaperReview == "MyPaperReview") { $joins[] = "left join PaperReview as MyPaperReview on (MyPaperReview.paperId=Paper.paperId and MyPaperReview.contactId={$contactId})"; } if (get($options, "topics")) { $cols[] = "(select group_concat(topicId) from PaperTopic where PaperTopic.paperId=Paper.paperId) topicIds"; } if (get($options, "options") && (isset($this->settingTexts["options"]) || isset($Opt["optionsInclude"])) && PaperOption::count_option_list()) { $cols[] = "(select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds"; } else { if (get($options, "options")) { $cols[] = "'' as optionIds"; } } if (get($options, "tags")) { $cols[] = "(select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags"; } if (get($options, "tagIndex") && !is_array($options["tagIndex"])) { $options["tagIndex"] = array($options["tagIndex"]); } if (get($options, "tagIndex")) { foreach ($options["tagIndex"] as $i => $tag) { $cols[] = "(select tagIndex from PaperTag where PaperTag.paperId=Paper.paperId and PaperTag.tag='" . sqlq($tag) . "') tagIndex" . ($i ?: ""); } } if (get($options, "reviewerPreference")) { $joins[] = "left join PaperReviewPreference on (PaperReviewPreference.paperId=Paper.paperId and PaperReviewPreference.contactId={$reviewerContactId})"; $cols[] = "coalesce(PaperReviewPreference.preference, 0) as reviewerPreference"; $cols[] = "PaperReviewPreference.expertise as reviewerExpertise"; } if (get($options, "allReviewerPreference") || get($options, "desirability")) { $subq = "select paperId"; if (get($options, "allReviewerPreference")) { $subq .= ", " . $this->query_all_reviewer_preference() . " as allReviewerPreference"; $cols[] = "APRP.allReviewerPreference"; } if (get($options, "desirability")) { $subq .= ", sum(if(preference<=-100,0,greatest(least(preference,1),-1))) as desirability"; $cols[] = "coalesce(APRP.desirability,0) as desirability"; } $subq .= " from PaperReviewPreference where {$papersel}true group by paperId"; $joins[] = "left join ({$subq}) as APRP on (APRP.paperId=Paper.paperId)"; } if (get($options, "allConflictType")) { $joins[] = "left join (select paperId, group_concat(concat(contactId,' ',conflictType) separator ',') as allConflictType from PaperConflict where {$papersel}conflictType>0 group by paperId) as AllConflict on (AllConflict.paperId=Paper.paperId)"; $cols[] = "AllConflict.allConflictType"; } if (get($options, "reviewer")) { $joins[] = "left join PaperConflict RPC on (RPC.paperId=Paper.paperId and RPC.contactId={$reviewerContactId})"; $joins[] = "left join PaperReview RPR on (RPR.paperId=Paper.paperId and RPR.contactId={$reviewerContactId})"; $cols[] = "RPC.conflictType reviewerConflictType, RPR.reviewType reviewerReviewType"; } if (get($options, "allComments")) { $joins[] = "join PaperComment on (PaperComment.paperId=Paper.paperId)"; $joins[] = "left join PaperConflict as CommentConflict on (CommentConflict.paperId=PaperComment.paperId and CommentConflict.contactId=PaperComment.contactId)"; array_push($cols, "PaperComment.commentId, PaperComment.contactId as commentContactId", "CommentConflict.conflictType as commentConflictType", "PaperComment.timeModified, PaperComment.comment", "PaperComment.replyTo, PaperComment.commentType"); } if (get($options, "reviewerName")) { if ($options["reviewerName"] === "lead" || $options["reviewerName"] === "shepherd") { $joins[] = "left join ContactInfo as ReviewerContactInfo on (ReviewerContactInfo.contactId=Paper." . $options['reviewerName'] . "ContactId)"; } else { if (get($options, "allComments")) { $joins[] = "left join ContactInfo as ReviewerContactInfo on (ReviewerContactInfo.contactId=PaperComment.contactId)"; } else { if (get($options, "reviewerName")) { $joins[] = "left join ContactInfo as ReviewerContactInfo on (ReviewerContactInfo.contactId=PaperReview.contactId)"; } } } array_push($cols, "ReviewerContactInfo.firstName as reviewFirstName", "ReviewerContactInfo.lastName as reviewLastName", "ReviewerContactInfo.email as reviewEmail", "ReviewerContactInfo.lastLogin as reviewLastLogin"); } if (get($options, "foldall")) { $cols[] = "1 as folded"; } // conditions if (count($paperset)) { $where[] = "Paper.paperId" . sql_in_numeric_set($paperset[0]); } if (get($options, "finalized")) { $where[] = "timeSubmitted>0"; } else { if (get($options, "unsub")) { $where[] = "timeSubmitted<=0"; } } if (get($options, "accepted")) { $where[] = "outcome>0"; } if (get($options, "undecided")) { $where[] = "outcome=0"; } if (get($options, "active") || get($options, "myReviews") || get($options, "myReviewRequests")) { $where[] = "timeWithdrawn<=0"; } if (get($options, "myLead")) { $where[] = "leadContactId={$contactId}"; } if (get($options, "unmanaged")) { $where[] = "managerContactId=0"; } $pq = "select " . join(",\n ", $cols) . "\nfrom " . join("\n ", $joins); if (count($where)) { $pq .= "\nwhere " . join("\n and ", $where); } // grouping and ordering if (get($options, "allComments")) { $pq .= "\ngroup by Paper.paperId, PaperComment.commentId"; } else { if ($reviewerQuery || $scoresQuery) { $pq .= "\ngroup by Paper.paperId, PaperReview.reviewId"; } else { $pq .= "\ngroup by Paper.paperId"; } } if (get($options, "order") && $options["order"] != "order by Paper.paperId") { $pq .= "\n" . $options["order"]; } else { $pq .= "\norder by Paper.paperId"; if ($reviewerQuery || $scoresQuery) { $pq .= ", PaperReview.reviewOrdinal"; } if (isset($options["allComments"])) { $pq .= ", PaperComment.commentId"; } } //Conf::msg_debugt($pq); return $pq . "\n"; }
private function _clauseTermSetReviews($t, $sqi) { global $Conf; $rsm = $t->value; if ($thistab = $rsm->simple_name()) { $thistab = "Reviews_" . $thistab; } else { $thistab = "Reviews_" . count($sqi->tables); } if (!isset($sqi->tables[$thistab])) { $where = array(); $reviewtable = "PaperReview r"; if ($rsm->review_type) { $where[] = "reviewType=" . $rsm->review_type; } $cwhere = array(); if ($rsm->completeness & ReviewSearchMatcher::COMPLETE) { $cwhere[] = "reviewSubmitted>0"; } if ($rsm->completeness & ReviewSearchMatcher::INCOMPLETE) { $cwhere[] = "reviewNeedsSubmit!=0"; } if ($rsm->completeness & ReviewSearchMatcher::INPROGRESS) { $cwhere[] = "(reviewSubmitted is null and reviewModified>0)"; } if (count($cwhere)) { $where[] = "(" . join(" or ", $cwhere) . ")"; } if ($rsm->round !== null) { if (count($rsm->round) == 0) { $where[] = "false"; } else { $where[] = "reviewRound" . sql_in_numeric_set($rsm->round); } } if ($rsm->rate !== null) { $this->_clauseTermSetRating($reviewtable, $where, $rsm->rate); } if ($rsm->has_contacts()) { $where[] = $rsm->contact_match_sql("r.contactId"); } if ($rsm->fieldsql) { $where[] = $rsm->fieldsql; } $wheretext = ""; if (count($where)) { $wheretext = " where " . join(" and ", $where); } $sqi->add_table($thistab, array("left join", "(select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from {$reviewtable}{$wheretext} group by paperId)")); $sqi->add_column($thistab . "_info", $thistab . ".info"); } $q = array(); $this->_clauseTermSetFlags($t, $sqi, $q); // Make the database query conservative (so change equality // constraints to >= constraints, and ignore <=/</!= constraints). // We'll do the precise query later. $q[] = "coalesce({$thistab}.count,0)" . $rsm->conservative_countexpr(); $t->link = $thistab; return "(" . join(" and ", $q) . ")"; }