294 lines
12 KiB
PHP
294 lines
12 KiB
PHP
<?php
|
|
|
|
// +----------------------------------------------------------------------+
|
|
// | OpenConf |
|
|
// +----------------------------------------------------------------------+
|
|
// | Copyright (c) 2002-2020 Zakon Group LLC. All Rights Reserved. |
|
|
// +----------------------------------------------------------------------+
|
|
// | This source file is subject to the OpenConf License, available on |
|
|
// | the OpenConf web site: www.OpenConf.com |
|
|
// +----------------------------------------------------------------------+
|
|
|
|
$OC_translate = false; // do not translate
|
|
|
|
$hdr = 'Export Submissions';
|
|
$hdrfn = 1;
|
|
|
|
require_once "../include.php";
|
|
|
|
beginChairSession();
|
|
|
|
require_once 'export.inc';
|
|
|
|
// accepted or all papers?
|
|
if (isset($_REQUEST['acc']) && preg_match("/\d+/", $_REQUEST['acc']) && isset($OC_acceptanceValuesAR[$_REQUEST['acc']])) {
|
|
$accSQL = "AND `" . OCC_TABLE_PAPER . "`.`accepted`='" . safeSQLstr($OC_acceptanceValuesAR[$_REQUEST['acc']]['value']) . "'";
|
|
$accURL = 'acc=' . $_REQUEST['acc'];
|
|
$scope = 'submissions-' . safeHTMLstr(oc_strtolower(preg_replace("/[^\w]/", "_", $OC_acceptanceValuesAR[$_REQUEST['acc']]['value'])));
|
|
}
|
|
else {
|
|
$accSQL = '';
|
|
$accURL = '';
|
|
$scope = 'submissions-all';
|
|
}
|
|
|
|
// check that matching submissions exist
|
|
$r = ocsql_query("SELECT COUNT(*) AS `count` FROM `" . OCC_TABLE_PAPER . "` WHERE 1=1 " . $accSQL);
|
|
$l = ocsql_fetch_assoc($r);
|
|
if ($l['count'] == 0) {
|
|
warn('There are no submissions to export', $hdr, $hdrfn);
|
|
exit;
|
|
}
|
|
|
|
require_once OCC_FORM_INC_FILE;
|
|
require_once OCC_SUBMISSION_INC_FILE;
|
|
|
|
$skip = 1; // skip field id
|
|
|
|
$fieldAR = array(
|
|
'paperid' => 'Submission ID',
|
|
'submissiondate' => 'Submission Date',
|
|
'lastupdate' => 'Last Updated',
|
|
'accepted' => 'Acceptance',
|
|
'_score' => 'Score',
|
|
);
|
|
|
|
$authorFieldsAR = array();
|
|
foreach ($OC_submissionFieldSetAR as $fsKey => $fsAR) {
|
|
if (count($fsAR['fields']) == 0) { continue; }
|
|
if ($fsKey == 'fs_authors') {
|
|
$fieldAR['skip' . $skip++] = '';
|
|
$fieldAR['name'] = 'Contact ' . OCC_WORD_AUTHOR . ' Full Name';
|
|
$authorFieldsAR['author_name'] = 'All ' . OCC_WORD_AUTHOR . 's Full Name';
|
|
foreach ($fsAR['fields'] AS $fieldID) {
|
|
$fieldAR[$fieldID] = 'Contact ' . OCC_WORD_AUTHOR . ' ' . (empty($OC_submissionFieldAR[$fieldID]['short']) ? $OC_submissionFieldAR[$fieldID]['name'] : $OC_submissionFieldAR[$fieldID]['short']);
|
|
$authorFieldsAR['author_' . $fieldID] = 'All ' . OCC_WORD_AUTHOR . 's ' . (empty($OC_submissionFieldAR[$fieldID]['short']) ? $OC_submissionFieldAR[$fieldID]['name'] : $OC_submissionFieldAR[$fieldID]['short']);
|
|
}
|
|
$fieldAR['skip' . $skip++] = '';
|
|
} else {
|
|
foreach ($fsAR['fields'] AS $fieldID) {
|
|
if (preg_match("/^password/i", $fieldID) || ($fieldID == 'file') || ($fieldID == 'topics') || empty($OC_submissionFieldAR[$fieldID]['name'])) { continue; }
|
|
$fieldAR[$fieldID] = (empty($OC_submissionFieldAR[$fieldID]['short']) ? $OC_submissionFieldAR[$fieldID]['name'] : $OC_submissionFieldAR[$fieldID]['short']);
|
|
}
|
|
}
|
|
}
|
|
|
|
if (isset($OC_submissionFieldAR['topics']['short'])) {
|
|
$fieldAR['_topics'] = $OC_submissionFieldAR['topics']['short'];
|
|
}
|
|
|
|
$fieldAR['skip' . $skip++] = '';
|
|
|
|
$fieldAR = array_merge($fieldAR, $authorFieldsAR);
|
|
|
|
$fieldAR['skip' . $skip++] = '';
|
|
|
|
// Default list of checked fields
|
|
$checkedFieldAR = array('paperid','title','name','email');
|
|
|
|
// Advocate/Committee fields
|
|
if ($OC_configAR['OC_paperAdvocates']) {
|
|
$fieldAR['skip' . $skip++] = '';
|
|
$fieldAR['_advocateid'] = 'Advocate ID';
|
|
$fieldAR['_advocate'] = 'Advocate';
|
|
$fieldAR['_adv_recommendation'] = 'Advocate Recommendation';
|
|
$fieldAR['_adv_comments'] = 'Advocate (Committee) Notes';
|
|
$fieldAR['pcnotes'] = OCC_WORD_CHAIR . ' Notes';
|
|
$fieldAR['skip' . $skip++] = '';
|
|
} else {
|
|
$fieldAR['skip' . $skip++] = '';
|
|
$fieldAR['pcnotes'] = OCC_WORD_CHAIR . ' Notes';
|
|
$fieldAR['skip' . $skip++] = '';
|
|
}
|
|
|
|
// Include extra fields
|
|
if (oc_hookSet('chair-export-fields')) {
|
|
foreach ($OC_hooksAR['chair-export-fields'] as $v) {
|
|
require_once $v;
|
|
}
|
|
}
|
|
|
|
if (isset($_POST['submit']) && ($_POST['submit'] == "Generate File") && isset($_POST['fields']) && !empty($_POST['fields'])) {
|
|
// Check for valid submission
|
|
if (!validToken('chair')) {
|
|
warn('Invalid submission');
|
|
}
|
|
// Verify Fields
|
|
$fieldARkeys = array_keys($fieldAR);
|
|
foreach ($_POST['fields'] as $f) {
|
|
if (!in_array($f,$fieldARkeys)) {
|
|
err('Invalid field name selection', $hdr, $hdrfn);
|
|
}
|
|
}
|
|
|
|
// Init extra field AR
|
|
$extraAR = array();
|
|
|
|
// List of fields to export
|
|
$exportFieldsAR = $_POST['fields'];
|
|
|
|
// All author data to display
|
|
$incAuthorFieldsAR = array_intersect($_POST['fields'], array_keys($authorFieldsAR));
|
|
if (!empty($incAuthorFieldsAR)) {
|
|
$maxPosition = 1; // tracks max number of authors per submission
|
|
|
|
// Get all authors info
|
|
$q = "SELECT `" . OCC_TABLE_AUTHOR . "`.*, CONCAT_WS(' ', `" . OCC_TABLE_AUTHOR . "`.`name_first`, `" . OCC_TABLE_AUTHOR . "`.`name_last`) AS `name` FROM `" . OCC_TABLE_AUTHOR . "`, `" . OCC_TABLE_PAPER . "` WHERE `" . OCC_TABLE_PAPER . "`.`paperid`=`" . OCC_TABLE_AUTHOR . "`.`paperid` " . $accSQL . " ORDER BY `paperid`, `position`";
|
|
$r = ocsql_query($q) or err("Unable to retrieve data for export (1)", $hdr, $hdrfn);
|
|
while ($l = ocsql_fetch_assoc($r)) {
|
|
foreach ($authorFieldsAR as $afKey => $afVal) {
|
|
if (preg_match("/^author_(\w+)$/", $afKey, $matches)) {
|
|
if (isset($OC_submissionFieldAR[$matches[1]]['values']) && is_array($OC_submissionFieldAR[$matches[1]]['values'])
|
|
&& isset($OC_submissionFieldAR[$matches[1]]['usekey']) && $OC_submissionFieldAR[$matches[1]]['usekey']
|
|
&& !empty($l[$matches[1]])
|
|
&& isset($OC_submissionFieldAR[$matches[1]]['values'][$l[$matches[1]]])
|
|
) {
|
|
$extraAR[$l['paperid']]['_' . $afKey . $l['position']] = $OC_submissionFieldAR[$matches[1]]['values'][$l[$matches[1]]];
|
|
} else {
|
|
$extraAR[$l['paperid']]['_' . $afKey . $l['position']] = $l[$matches[1]];
|
|
}
|
|
$fieldAR['_' . $afKey . $l['position']] = preg_replace("/All " . OCC_WORD_AUTHOR . "s /", "", (OCC_WORD_AUTHOR . ' ' . $l['position'] . ' ' . $fieldAR[$afKey]));
|
|
}
|
|
}
|
|
if ($l['position'] > $maxPosition) {
|
|
$maxPosition = $l['position'];
|
|
}
|
|
}
|
|
|
|
$newAuthorFieldsAR = array();
|
|
for ($i=1; $i<=$maxPosition; $i++) {
|
|
foreach($incAuthorFieldsAR as $f) {
|
|
$newAuthorFieldsAR[] = '_' . $f . $i;
|
|
}
|
|
}
|
|
reset($incAuthorFieldsAR);
|
|
array_splice(
|
|
$exportFieldsAR,
|
|
array_search(current($incAuthorFieldsAR), $_POST['fields']),
|
|
count($incAuthorFieldsAR),
|
|
$newAuthorFieldsAR
|
|
);
|
|
}
|
|
|
|
// Topics
|
|
if (in_array('_topics', $exportFieldsAR)) {
|
|
$q = "SELECT `" . OCC_TABLE_PAPER . "`.`paperid`, `" . OCC_TABLE_TOPIC . "`.`topicname`, `" . OCC_TABLE_TOPIC . "`.`short` FROM `" . OCC_TABLE_PAPER . "`, `" . OCC_TABLE_TOPIC . "`, `" . OCC_TABLE_PAPERTOPIC . "` WHERE `" . OCC_TABLE_PAPER . "`.`paperid`=`" . OCC_TABLE_PAPERTOPIC . "`.`paperid` AND `" . OCC_TABLE_PAPERTOPIC . "`.`topicid`=`" . OCC_TABLE_TOPIC . "`.`topicid` " . $accSQL;
|
|
$r = ocsql_query($q) or err("Unable to retrieve data for export (t)", $hdr, $hdrfn);
|
|
while ($l = ocsql_fetch_assoc($r)) {
|
|
if (isset($extraAR[$l['paperid']]['_topics'])) {
|
|
$extraAR[$l['paperid']]['_topics'] .= "\n" . useTopic($l['short'], $l['topicname']);
|
|
} else {
|
|
$extraAR[$l['paperid']]['_topics'] = useTopic($l['short'], $l['topicname']);
|
|
}
|
|
}
|
|
}
|
|
|
|
// Score
|
|
if (in_array('_score', $exportFieldsAR)) {
|
|
$q = "SELECT `" . OCC_TABLE_PAPER . "`.`paperid`, ABS(FORMAT(AVG(`score`),2)) AS `recavg` FROM `" . OCC_TABLE_PAPER . "` LEFT JOIN `" . OCC_TABLE_PAPERREVIEWER . "` ON `" . OCC_TABLE_PAPERREVIEWER . "`.`paperid`=`" . OCC_TABLE_PAPER . "`.`paperid` WHERE 1=1 " . $accSQL . " GROUP BY `paperid`";
|
|
$r = ocsql_query($q) or err("Unable to retrieve data for export (s)", $hdr, $hdrfn);
|
|
while ($l = ocsql_fetch_assoc($r)) {
|
|
$extraAR[$l['paperid']]['_score'] = $l['recavg'];
|
|
}
|
|
}
|
|
|
|
// Committee Comments
|
|
if (preg_match("/\b_adv/", implode(',', $exportFieldsAR))) {
|
|
// $accSQL is left out below as OCC_TABLE_PAPER is not included, meaning we use a little more memory
|
|
$q = "SELECT `" . OCC_TABLE_PAPERADVOCATE . "`.*, CONCAT_WS(' ',`" . OCC_TABLE_REVIEWER . "`.`name_first`,`" . OCC_TABLE_REVIEWER . "`.`name_last`) AS `advocate` FROM `" . OCC_TABLE_PAPERADVOCATE . "`, `" . OCC_TABLE_REVIEWER . "` WHERE `" . OCC_TABLE_PAPERADVOCATE . "`.`advocateid`=`" . OCC_TABLE_REVIEWER . "`.`reviewerid` ORDER BY `paperid`";
|
|
$r = ocsql_query($q) or err("Unable to retrieve data for export (c)", $hdr, $hdrfn);
|
|
while ($l = ocsql_fetch_assoc($r)) {
|
|
$extraAR[$l['paperid']]['_adv_comments'] = $l['adv_comments'];
|
|
$extraAR[$l['paperid']]['_advocateid'] = $l['advocateid'];
|
|
$extraAR[$l['paperid']]['_advocate'] = $l['advocate'];
|
|
$extraAR[$l['paperid']]['_adv_recommendation'] = $l['adv_recommendation'];
|
|
}
|
|
}
|
|
|
|
// Get extra fields data
|
|
if (oc_hookSet('chair-export-data')) {
|
|
foreach ($OC_hooksAR['chair-export-data'] as $v) {
|
|
require_once $v;
|
|
}
|
|
}
|
|
|
|
// Get sub. data & iterate through each
|
|
$q = "SELECT `" . OCC_TABLE_PAPER . "`.*, `" . OCC_TABLE_PAPER . "`.`paperid` AS `id`, `" . OCC_TABLE_AUTHOR . "`.*, CONCAT_WS(' ',`" . OCC_TABLE_AUTHOR . "`.`name_first`,`" . OCC_TABLE_AUTHOR . "`.`name_last`) AS `name` FROM `" . OCC_TABLE_PAPER . "`, `" . OCC_TABLE_AUTHOR . "` WHERE `" . OCC_TABLE_PAPER . "`.`paperid`=`" . OCC_TABLE_AUTHOR . "`.`paperid` AND `" . OCC_TABLE_AUTHOR . "`.`position`=`" . OCC_TABLE_PAPER . "`.`contactid` " . $accSQL . " ORDER BY `" . OCC_TABLE_PAPER . "`.`paperid`";
|
|
$r = ocsql_query($q) or err("Unable to retrieve data for export", $hdr, $hdrfn);
|
|
if (ocsql_num_rows($r) == 0) {
|
|
warn("There are no papers to export", $hdr, $hdrfn);
|
|
exit;
|
|
} else { // Export file
|
|
oc_export($scope, $exportFieldsAR, $fieldAR, $r, $extraAR, $OC_submissionFieldAR);
|
|
exit;
|
|
}
|
|
|
|
}
|
|
|
|
// Display form
|
|
printHeader($hdr, $hdrfn);
|
|
|
|
print '
|
|
<form method="post" action="' . $_SERVER['PHP_SELF'] . '">
|
|
<input type="hidden" name="token" value="' . $_SESSION[OCC_SESSION_VAR_NAME]['chairtoken'] . '" />
|
|
<p>Export will generate one row/entry per submission. When selecting <em>All ' . OCC_WORD_AUTHOR . 's</em>, each ' . oc_strtolower(OCC_WORD_AUTHOR) . '\'s data will appear as separate fields.</p>
|
|
<script language="javascript" type="text/javascript">
|
|
<!--
|
|
function checkAllBoxes() {
|
|
var boxObj = document.getElementsByName(\'fields[]\');
|
|
for (var i=0; i<boxObj.length; i++) {
|
|
boxObj[i].checked = true;
|
|
}
|
|
}
|
|
document.write(\'<p><a href="#" onclick="checkAllBoxes(); return false;" style="margin-left: 25px; padding: 1px 3px; background-color: #eee; color: #00f; text-decoration: underline;" >check all</a></p>\');
|
|
// -->
|
|
</script>
|
|
';
|
|
|
|
foreach ($fieldAR as $fieldID => $fieldName) {
|
|
if (preg_match("/^skip\d+$/", $fieldID)) {
|
|
print '<br />';
|
|
continue;
|
|
}
|
|
print '<label><input type="checkbox" name="fields[]" value="' . $fieldID . '" ';
|
|
if (in_array($fieldID,$checkedFieldAR)) { print 'checked '; }
|
|
print '/> ' . $fieldName . "</label><br />\n";
|
|
}
|
|
|
|
print '
|
|
<br />
|
|
<label>Format: <select name="format">
|
|
';
|
|
|
|
foreach ($OC_exportFormatAR as $fmt => $fmtAR) {
|
|
print '<option value="' . $fmt . '">' . $fmtAR['name'] . '</option>';
|
|
}
|
|
|
|
print '
|
|
</select></label>
|
|
|
|
<label>Submissions: <select name="acc">
|
|
<option value="">All</option>
|
|
';
|
|
|
|
foreach ($OC_acceptanceValuesAR as $idx => $acc) {
|
|
print '<option value="' . safeHTMLstr($idx) . '">' . safeHTMLstr($acc['value']) . '</option>';
|
|
}
|
|
|
|
print '
|
|
</select></label>
|
|
<input type="submit" name="submit" value="Generate File" style="float: left; margin-right: 2em;" class="submit" />
|
|
<br />
|
|
|
|
<p style="margin-left: 30px;"><label><input type="checkbox" name="charlimit" value="1" /> Limit cells to ' . $OC_exportCharLimit . ' characters (Excel/CSV limit)</label></p>
|
|
|
|
</form>
|
|
|
|
<p class="note">Note: When opening up a CSV or Tab-delimited file in a spreadsheet, you may need to specify the character encoding: UTF-8.</p>
|
|
|
|
';
|
|
|
|
printFooter();
|
|
|
|
?>
|