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)
// "order" => $sql $sql is SQL 'order by' clause (or empty)
$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 (@$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 (@$options["author"] && $contact && ($aujoinwhere = $contact->actAuthorSql("PaperConflict", true))) {
$where[] = $aujoinwhere;
}
if (@$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 (@$options["myReviewRequests"]) {
$joins[] = "join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.requestedBy={$contactId} and PaperReview.reviewType=" . REVIEW_EXTERNAL . ")";
} else {
if (@$options["myReviews"]) {
$joins[] = "join PaperReview on (PaperReview.paperId=Paper.paperId and (PaperReview.contactId={$contactId}{$qr}))";
} else {
if (@$options["myOutstandingReviews"]) {
$joins[] = "join PaperReview on (PaperReview.paperId=Paper.paperId and (PaperReview.contactId={$contactId}{$qr}) and PaperReview.reviewNeedsSubmit!=0)";
} else {
if (@$options["myReviewsOpt"]) {
$joins[] = "left join PaperReview on (PaperReview.paperId=Paper.paperId and (PaperReview.contactId={$contactId}{$qr}))";
} else {
if (@$options["allReviews"] || @$options["allReviewScores"]) {
$x = @$options["reviewLimitSql"] ? " and (" . $options["reviewLimitSql"] . ")" : "";
$joins[] = "join PaperReview on (PaperReview.paperId=Paper.paperId{$x})";
} else {
if (!@$options["author"]) {
$joins[] = "left join PaperReview on (PaperReview.paperId=Paper.paperId and (PaperReview.contactId={$contactId}{$qr}))";
}
}
}
}
}
}
// all reviews
$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";
$j = "select paperId, count(*) count";
$cols[] = "coalesce(R_submitted.count,0) reviewCount";
if (@$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 (@$options["reviewTypes"]) {
$cols[] = "R_submitted.reviewTypes";
$j .= ", group_concat(reviewType order by reviewId) reviewTypes";
}
if (@$options["reviewTypes"] || @$options["scores"]) {
$cols[] = "R_submitted.reviewContactIds";
$j .= ", group_concat(contactId order by reviewId) reviewContactIds";
}
$joins[] = "left join ({$j} from PaperReview where {$papersel}reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId)";
// fields
if (@$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";
foreach (ReviewForm::field_list_all_rounds() 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 (@$options["topics"] || @$options["topicInterestScore"]) {
$j = "left join (select paperId";
if (@$options["topics"]) {
$j .= ", group_concat(PaperTopic.topicId) as topicIds, group_concat(ifnull(" . $this->query_topic_interest("TopicInterest.") . ",0)) as topicInterest";
$cols[] = "PaperTopics.topicIds, PaperTopics.topicInterest";
}
if (@$options["topicInterestScore"]) {
$j .= ", sum(" . $this->query_topic_interest_score() . ") as topicInterestScore";
$cols[] = "coalesce(PaperTopics.topicInterestScore,0) as topicInterestScore";
}
$j .= " from PaperTopic left join TopicInterest on (TopicInterest.topicId=PaperTopic.topicId and TopicInterest.contactId={$reviewerContactId}) where {$papersel}true group by paperId) as PaperTopics on (PaperTopics.paperId=Paper.paperId)";
$joins[] = $j;
}
if (@$options["options"] && @$this->settingTexts["options"]) {
$joins[] = "left join (select paperId, group_concat(PaperOption.optionId, '#', value) as optionIds from PaperOption where {$papersel}true group by paperId) as PaperOptions on (PaperOptions.paperId=Paper.paperId)";
$cols[] = "PaperOptions.optionIds";
} else {
if (@$options["options"]) {
$cols[] = "'' as optionIds";
}
}
if (@$options["tags"]) {
$joins[] = "left join (select paperId, group_concat(' ', tag, '#', tagIndex order by tag separator '') as paperTags from PaperTag where {$papersel}true group by paperId) as PaperTags on (PaperTags.paperId=Paper.paperId)";
$cols[] = "PaperTags.paperTags";
}
if (@$options["tagIndex"] && !is_array($options["tagIndex"])) {
$options["tagIndex"] = array($options["tagIndex"]);
}
if (@$options["tagIndex"]) {
for ($i = 0; $i < count($options["tagIndex"]); ++$i) {
$joins[] = "left join PaperTag as TagIndex{$i} on (TagIndex{$i}.paperId=Paper.paperId and TagIndex{$i}.tag='" . sqlq($options["tagIndex"][$i]) . "')";
$cols[] = "TagIndex{$i}.tagIndex as tagIndex" . ($i ?: "");
}
}
if (@$options["reviewerPreference"]) {
$joins[] = "left join PaperReviewPreference on (PaperReviewPreference.paperId=Paper.paperId and PaperReviewPreference.contactId={$reviewerContactId})";
$cols[] = "coalesce(PaperReviewPreference.preference, 0) as reviewerPreference";
if ($this->sversion >= 69) {
$cols[] = "PaperReviewPreference.expertise as reviewerExpertise";
} else {
$cols[] = "NULL as reviewerExpertise";
}
}
if (@$options["allReviewerPreference"] || @$options["desirability"]) {
$subq = "select paperId";
if (@$options["allReviewerPreference"]) {
$subq .= ", " . $this->query_all_reviewer_preference() . " as allReviewerPreference";
$cols[] = "APRP.allReviewerPreference";
}
if (@$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 (@$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 (@$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 (@$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 (@$options["reviewerName"]) {
if (@$options["reviewerName"] === "lead" || @$options["reviewerName"] === "shepherd") {
$joins[] = "left join ContactInfo as ReviewerContactInfo on (ReviewerContactInfo.contactId=Paper.{$options['reviewerName']}ContactId)";
} else {
if (@$options["allComments"]) {
$joins[] = "left join ContactInfo as ReviewerContactInfo on (ReviewerContactInfo.contactId=PaperComment.contactId)";
} else {
if (@$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 (@$options["foldall"]) {
$cols[] = "1 as folded";
}
// conditions
if (count($paperset)) {
$where[] = "Paper.paperId" . sql_in_numeric_set($paperset[0]);
}
if (@$options["finalized"]) {
$where[] = "timeSubmitted>0";
} else {
if (@$options["unsub"]) {
$where[] = "timeSubmitted<=0";
}
}
if (@$options["accepted"]) {
$where[] = "outcome>0";
}
if (@$options["undecided"]) {
$where[] = "outcome=0";
}
if (@$options["active"] || @$options["myReviews"] || @$options["myReviewRequests"]) {
$where[] = "timeWithdrawn<=0";
}
if (@$options["myLead"]) {
$where[] = "leadContactId={$contactId}";
}
if (@$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 (@$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 (@$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";
}
}
//$this->infoMsg(Ht::pre_text_wrap($pq));
return $pq . "\n";
}
