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";
}
xassert_match($x->a[1], ",^x.txt:2(?::|\$),");
xassert_match($x->b->c, ",^x.txt:4(?::|\$),");
xassert_match($x->b->__LANDMARK__, ",^x.txt:3(?::|\$),");
// obscure_time tests
$t = $Conf->parse_time("1 Sep 2010 00:00:01");
$t0 = $Conf->obscure_time($t);
xassert_eqq($Conf->unparse_time_obscure($t0), "1 Sep 2010");
xassert_eqq($Conf->printableTime($t0), "1 Sep 2010 12pm EDT");
$t = $Conf->parse_time("1 Sep 2010 23:59:59");
$t0 = $Conf->obscure_time($t);
xassert_eqq($Conf->unparse_time_obscure($t0), "1 Sep 2010");
xassert_eqq($Conf->printableTime($t0), "1 Sep 2010 12pm EDT");
// review ordinal tests
foreach ([1 => "A", 26 => "Z", 27 => "AA", 28 => "AB", 51 => "AY", 52 => "AZ", 53 => "BA", 54 => "BB", 702 => "ZZ"] as $n => $t) {
xassert_eqq(unparseReviewOrdinal($n), $t);
xassert_eqq(parseReviewOrdinal($t), $n);
}
// ReviewField::make_abbreviation tests
xassert_eqq(ReviewField::make_abbreviation("novelty", 0, 0), "Nov");
xassert_eqq(ReviewField::make_abbreviation("novelty is an amazing", 0, 0), "NovIsAma");
xassert_eqq(ReviewField::make_abbreviation("novelty is an AWESOME", 0, 0), "NovIsAWESOME");
xassert_eqq(ReviewField::make_abbreviation("novelty isn't an AWESOME", 0, 0), "NovIsnAWESOME");
xassert_eqq(ReviewField::make_abbreviation("novelty isn't an AWESOME", 0, 1), "novelty-isnt-awesome");
xassert_eqq(ReviewField::make_abbreviation("_format", 0, 1), "format");
// utf8_word_prefix, etc. tests
xassert_eqq(UnicodeHelper::utf8_prefix("aaaaaaaa", 7), "aaaaaaa");
xassert_eqq(UnicodeHelper::utf8_prefix("aaaaaaaa", 8), "aaaaaaaa");
xassert_eqq(UnicodeHelper::utf8_prefix("aaaaaaaa", 9), "aaaaaaaa");
xassert_eqq(UnicodeHelper::utf8_prefix("áááááááá", 7), "ááááááá");
xassert_eqq(UnicodeHelper::utf8_prefix("áááááááá", 8), "áááááááá");
xassert_eqq(UnicodeHelper::utf8_prefix("áááááááá", 9), "áááááááá");
