/**
* Get the registrant report data.
* @param $conferenceId int
* @param $schedConfId int
* @return array
*/
function getRegistrantReport($conferenceId, $schedConfId)
{
$primaryLocale = Locale::getPrimaryLocale();
$locale = Locale::getLocale();
$result =& $this->retrieve('SELECT
r.registration_id AS registration_id,
r.user_id AS userid,
u.username AS uname,
u.first_name AS fname,
u.middle_name AS mname,
u.last_name AS lname,
u.affiliation AS affiliation,
u.url AS url,
u.email AS email,
u.phone AS phone,
u.fax AS fax,
u.mailing_address AS address,
u.country AS country,
COALESCE(rtsl.setting_value, rtspl.setting_value) AS type,
r.date_registered AS regdate,
r.date_paid AS paiddate,
r.special_requests AS specialreq
FROM
registrations r
LEFT JOIN users u ON r.user_id=u.user_id
LEFT JOIN registration_type_settings rtsl ON (r.type_id=rtsl.type_id AND rtsl.locale=? AND rtsl.setting_name=?)
LEFT JOIN registration_type_settings rtspl ON (r.type_id=rtspl.type_id AND rtsl.locale=? AND rtspl.setting_name=?)
WHERE
r.sched_conf_id= ?
ORDER BY
lname', array($locale, 'name', $primaryLocale, 'name', $schedConfId));
// prepare an iterator of all the registration information
$registrationReturner = new DBRowIterator($result);
$result =& $this->retrieve('SELECT
r.registration_id as registration_id,
roa.option_id as option_id
FROM
registrations r
LEFT JOIN registration_option_assoc roa ON (r.registration_id = roa.registration_id)
WHERE
r.sched_conf_id= ?', $schedConfId);
// Prepare an array of registration Options by registration Id
$registrationOptionDAO =& DAORegistry::getDAO('RegistrationOptionDAO');
$iterator = new DBRowIterator($result);
$registrationOptionReturner = array();
while ($row =& $iterator->next()) {
$registrationId = $row['registration_id'];
$registrationOptionReturner[$registrationId] =& $registrationOptionDAO->getRegistrationOptions($registrationId);
}
return array($registrationReturner, $registrationOptionReturner);
}
function _cleanup()
{
parent::_cleanup();
}
/**
* Get the paper report data.
* @param $conferenceId int
* @param $schedConfId int
* @return array
*/
function getPaperReport($conferenceId, $schedConfId)
{
$primaryLocale = Locale::getPrimaryLocale();
$locale = Locale::getLocale();
$result =& $this->retrieve('SELECT p.status AS status,
p.start_time AS start_time,
p.end_time AS end_time,
pp.room_id AS room_id,
p.paper_id AS paper_id,
COALESCE(psl1.setting_value, pspl1.setting_value) AS title,
COALESCE(psl2.setting_value, pspl2.setting_value) AS abstract,
COALESCE(tl.setting_value, tpl.setting_value) AS track_title,
p.language AS language
FROM papers p
LEFT JOIN published_papers pp ON (p.paper_id = pp.paper_id)
LEFT JOIN paper_settings pspl1 ON (pspl1.paper_id=p.paper_id AND pspl1.setting_name = ? AND pspl1.locale = ?)
LEFT JOIN paper_settings psl1 ON (psl1.paper_id=p.paper_id AND psl1.setting_name = ? AND psl1.locale = ?)
LEFT JOIN paper_settings pspl2 ON (pspl2.paper_id=p.paper_id AND pspl2.setting_name = ? AND pspl2.locale = ?)
LEFT JOIN paper_settings psl2 ON (psl2.paper_id=p.paper_id AND psl2.setting_name = ? AND psl2.locale = ?)
LEFT JOIN track_settings tpl ON (tpl.track_id=p.track_id AND tpl.setting_name = ? AND tpl.locale = ?)
LEFT JOIN track_settings tl ON (tl.track_id=p.track_id AND tl.setting_name = ? AND tl.locale = ?)
WHERE p.sched_conf_id = ?
ORDER BY p.paper_id', array('title', $primaryLocale, 'title', $locale, 'abstract', $primaryLocale, 'abstract', $locale, 'title', $primaryLocale, 'title', $locale, $schedConfId));
$papersReturner = new DBRowIterator($result);
unset($result);
$result =& $this->retrieve('SELECT MAX(ed.date_decided) AS date,
ed.paper_id AS paper_id
FROM edit_decisions ed,
papers p
WHERE p.sched_conf_id = ? AND
p.paper_id = ed.paper_id
GROUP BY p.paper_id, ed.paper_id', array($schedConfId));
$decisionDatesIterator = new DBRowIterator($result);
unset($result);
$decisionsReturner = array();
while ($row =& $decisionDatesIterator->next()) {
$result =& $this->retrieve('SELECT decision AS decision,
paper_id AS paper_id
FROM edit_decisions
WHERE date_decided = ? AND
paper_id = ?', array($row['date'], $row['paper_id']));
$decisionsReturner[] = new DBRowIterator($result);
unset($result);
}
$paperDao =& DAORegistry::getDAO('PaperDAO');
$papers =& $paperDao->getPapersBySchedConfId($schedConfId);
$authorsReturner = array();
$index = 1;
while ($paper =& $papers->next()) {
$result =& $this->retrieve('SELECT pa.first_name AS fname,
pa.middle_name AS mname,
pa.last_name AS lname,
pa.email AS email,
pa.affiliation AS affiliation,
pa.country AS country,
pa.url AS url,
COALESCE(pasl.setting_value, pas.setting_value) AS biography
FROM paper_authors pa
LEFT JOIN papers p ON pa.paper_id=p.paper_id
LEFT JOIN paper_author_settings pas ON (pa.author_id=pas.author_id AND pas.setting_name = ? AND pas.locale = ?)
LEFT JOIN paper_author_settings pasl ON (pa.author_id=pasl.author_id AND pasl.setting_name = ? AND pasl.locale = ?)
WHERE p.sched_conf_id = ? AND
p.paper_id = ?', array('biography', $primaryLocale, 'biography', $locale, $schedConfId, $paper->getId()));
$authorIterator = new DBRowIterator($result);
unset($result);
$authorsReturner[$paper->getId()] = $authorIterator;
unset($authorIterator);
$index++;
unset($paper);
}
return array($papersReturner, $authorsReturner, $decisionsReturner);
}
/**
* Get the article report data.
* @param $journalId int
* @return array
*/
function getArticleReport($journalId)
{
$primaryLocale = AppLocale::getPrimaryLocale();
$locale = AppLocale::getLocale();
$result = $this->retrieve('SELECT a.submission_id AS submission_id,
COALESCE(asl1.setting_value, aspl1.setting_value) AS title,
COALESCE(asl2.setting_value, aspl2.setting_value) AS abstract,
COALESCE(sl.setting_value, spl.setting_value) AS section_title,
a.status AS status,
a.language AS language
FROM submissions a
LEFT JOIN submission_settings aspl1 ON (aspl1.submission_id=a.submission_id AND aspl1.setting_name = ? AND aspl1.locale = a.locale)
LEFT JOIN submission_settings asl1 ON (asl1.submission_id=a.submission_id AND asl1.setting_name = ? AND asl1.locale = ?)
LEFT JOIN submission_settings aspl2 ON (aspl2.submission_id=a.submission_id AND aspl2.setting_name = ? AND aspl2.locale = a.locale)
LEFT JOIN submission_settings asl2 ON (asl2.submission_id=a.submission_id AND asl2.setting_name = ? AND asl2.locale = ?)
LEFT JOIN section_settings spl ON (spl.section_id=a.section_id AND spl.setting_name = ? AND spl.locale = ?)
LEFT JOIN section_settings sl ON (sl.section_id=a.section_id AND sl.setting_name = ? AND sl.locale = ?)
WHERE a.context_id = ? AND
a.submission_progress = 0
ORDER BY a.submission_id', array('title', 'title', $locale, 'abstract', 'abstract', $locale, 'title', $primaryLocale, 'title', $locale, (int) $journalId));
$articlesReturner = new DBRowIterator($result);
$result = $this->retrieve('SELECT MAX(d.date_decided) AS date_decided,
d.submission_id AS submission_id
FROM edit_decisions d,
submissions a
WHERE a.context_id = ? AND
a.submission_progress = 0 AND
a.submission_id = d.submission_id
GROUP BY d.submission_id', array((int) $journalId));
$decisionDatesIterator = new DBRowIterator($result);
$decisionsReturner = array();
while ($row = $decisionDatesIterator->next()) {
$result = $this->retrieve('SELECT d.decision AS decision,
d.submission_id AS submission_id
FROM edit_decisions d,
submissions a
WHERE d.date_decided = ? AND
d.submission_id = a.submission_id AND
a.submission_progress = 0 AND
d.submission_id = ?', array($row['date_decided'], $row['submission_id']));
$decisionsReturner[] = new DBRowIterator($result);
}
$articleDao = DAORegistry::getDAO('ArticleDAO');
$articles = $articleDao->getByContextId($journalId);
$authorsReturner = array();
$index = 1;
while ($article = $articles->next()) {
$result = $this->retrieve('SELECT aa.first_name AS fname,
aa.middle_name AS mname,
aa.last_name AS lname,
aa.email AS email,
aa.country AS country,
aa.url AS url,
COALESCE(aasl.setting_value, aas.setting_value) AS biography,
COALESCE(aaasl.setting_value, aaas.setting_value) AS affiliation
FROM authors aa
JOIN submissions a ON (aa.submission_id = a.submission_id)
LEFT JOIN author_settings aas ON (aa.author_id = aas.author_id AND aas.setting_name = ? AND aas.locale = ?)
LEFT JOIN author_settings aasl ON (aa.author_id = aasl.author_id AND aasl.setting_name = ? AND aasl.locale = ?)
LEFT JOIN author_settings aaas ON (aa.author_id = aaas.author_id AND aaas.setting_name = ? AND aaas.locale = ?)
LEFT JOIN author_settings aaasl ON (aa.author_id = aaasl.author_id AND aaasl.setting_name = ? AND aaasl.locale = ?)
WHERE
a.context_id = ? AND
a.submission_progress = 0 AND
aa.submission_id = ?', array('biography', $primaryLocale, 'biography', $locale, 'affiliation', $primaryLocale, 'affiliation', $locale, (int) $journalId, $article->getId()));
$authorIterator = new DBRowIterator($result);
$authorsReturner[$article->getId()] = $authorIterator;
$index++;
}
return array($articlesReturner, $authorsReturner, $decisionsReturner);
}
/**
* Get the paper report data.
* @param $conferenceId int
* @param $schedConfId int
* @return array
*/
function getPaperReport($conferenceId, $schedConfId)
{
$primaryLocale = AppLocale::getPrimaryLocale();
$locale = AppLocale::getLocale();
$paperTypeDao =& DAORegistry::getDAO('PaperTypeDAO');
// Load constants
$result =& $this->retrieve('SELECT p.status AS status,
p.start_time AS start_time,
p.end_time AS end_time,
pp.room_id AS room_id,
p.paper_id AS paper_id,
p.comments_to_dr as comments,
COALESCE(psl1.setting_value, pspl1.setting_value) AS title,
COALESCE(psl2.setting_value, pspl2.setting_value) AS abstract,
COALESCE(tl.setting_value, tpl.setting_value) AS track_title,
COALESCE(cvesl.setting_value, cvesp.setting_value) AS paper_type,
p.language AS language
FROM papers p
LEFT JOIN published_papers pp ON (p.paper_id = pp.paper_id)
LEFT JOIN paper_settings pspl1 ON (pspl1.paper_id=p.paper_id AND pspl1.setting_name = ? AND pspl1.locale = ?)
LEFT JOIN paper_settings psl1 ON (psl1.paper_id=p.paper_id AND psl1.setting_name = ? AND psl1.locale = ?)
LEFT JOIN paper_settings pspl2 ON (pspl2.paper_id=p.paper_id AND pspl2.setting_name = ? AND pspl2.locale = ?)
LEFT JOIN paper_settings psl2 ON (psl2.paper_id=p.paper_id AND psl2.setting_name = ? AND psl2.locale = ?)
LEFT JOIN paper_settings pti ON (pti.paper_id=p.paper_id AND pti.setting_name = ?)
LEFT JOIN controlled_vocabs cv ON (cv.symbolic = ? AND cv.assoc_type = ? AND cv.assoc_id = ?)
LEFT JOIN controlled_vocab_entries cve ON (cve.controlled_vocab_id = cv.controlled_vocab_id AND pti.setting_value = cve.controlled_vocab_entry_id)
LEFT JOIN controlled_vocab_entry_settings cvesp ON (cve.controlled_vocab_entry_id = cvesp.controlled_vocab_entry_id AND cvesp.setting_name = ? AND cvesp.locale = ?)
LEFT JOIN controlled_vocab_entry_settings cvesl ON (cve.controlled_vocab_entry_id = cvesl.controlled_vocab_entry_id AND cvesl.setting_name = ? AND cvesl.locale = ?)
LEFT JOIN track_settings tpl ON (tpl.track_id=p.track_id AND tpl.setting_name = ? AND tpl.locale = ?)
LEFT JOIN track_settings tl ON (tl.track_id=p.track_id AND tl.setting_name = ? AND tl.locale = ?)
WHERE p.sched_conf_id = ? AND
p.submission_progress = 0
ORDER BY p.paper_id', array('title', $primaryLocale, 'title', $locale, 'abstract', $primaryLocale, 'abstract', $locale, 'sessionType', PAPER_TYPE_SYMBOLIC, ASSOC_TYPE_SCHED_CONF, $schedConfId, 'description', $primaryLocale, 'description', $locale, 'title', $primaryLocale, 'title', $locale, $schedConfId));
$papersReturner = new DBRowIterator($result);
unset($result);
$result =& $this->retrieve('SELECT MAX(ed.date_decided) AS date_decided,
ed.paper_id AS paper_id
FROM edit_decisions ed,
papers p
WHERE p.sched_conf_id = ? AND
p.submission_progress = 0 AND
p.paper_id = ed.paper_id
GROUP BY p.paper_id, ed.paper_id', array($schedConfId));
$decisionDatesIterator = new DBRowIterator($result);
unset($result);
$decisionsReturner = array();
while ($row =& $decisionDatesIterator->next()) {
$result =& $this->retrieve('SELECT d.decision AS decision,
d.paper_id AS paper_id
FROM edit_decisions d,
papers p
WHERE d.date_decided = ? AND
d.paper_id = p.paper_id AND
p.submission_progress = 0 AND
p.paper_id = ?', array($row['date_decided'], $row['paper_id']));
$decisionsReturner[] = new DBRowIterator($result);
unset($result);
}
$paperDao =& DAORegistry::getDAO('PaperDAO');
$papers =& $paperDao->getPapersBySchedConfId($schedConfId);
$authorsReturner = array();
$index = 1;
while ($paper =& $papers->next()) {
$result =& $this->retrieve('SELECT pa.first_name AS fname,
pa.middle_name AS mname,
pa.last_name AS lname,
pa.email AS email,
pa.affiliation AS affiliation,
pa.country AS country,
pa.url AS url,
COALESCE(pasl.setting_value, pas.setting_value) AS biography
FROM paper_authors pa
JOIN papers p ON pa.paper_id=p.paper_id
LEFT JOIN paper_author_settings pas ON (pa.author_id=pas.author_id AND pas.setting_name = ? AND pas.locale = ?)
LEFT JOIN paper_author_settings pasl ON (pa.author_id=pasl.author_id AND pasl.setting_name = ? AND pasl.locale = ?)
WHERE p.sched_conf_id = ? AND
p.submission_progress = 0 AND
p.paper_id = ?
ORDER BY pa.primary_contact DESC, pa.seq', array('biography', $primaryLocale, 'biography', $locale, $schedConfId, $paper->getId()));
$authorIterator = new DBRowIterator($result);
unset($result);
$authorsReturner[$paper->getId()] = $authorIterator;
unset($authorIterator);
$index++;
unset($paper);
}
return array($papersReturner, $authorsReturner, $decisionsReturner);
}
/**
* Get the article report data.
* @param $journalId int
* @return array
*/
function getArticleReport($journalId)
{
$primaryLocale = Locale::getPrimaryLocale();
$locale = Locale::getLocale();
$result =& $this->retrieve('SELECT a.article_id AS article_id,
COALESCE(asl1.setting_value, aspl1.setting_value) AS title,
COALESCE(asl2.setting_value, aspl2.setting_value) AS abstract,
COALESCE(sl.setting_value, spl.setting_value) AS section_title,
a.status AS status,
a.language AS language
FROM articles a
LEFT JOIN section_decisions sdec ON (a.article_id = sdec.article_id)
LEFT JOIN section_decisions sdec2 ON (a.article_id = sdec2.article_id AND sdec.section_decision_id < sdec2.section_decision_id)
LEFT JOIN article_settings aspl1 ON (aspl1.article_id=a.article_id AND aspl1.setting_name = ? AND aspl1.locale = a.locale)
LEFT JOIN article_settings asl1 ON (asl1.article_id=a.article_id AND asl1.setting_name = ? AND asl1.locale = ?)
LEFT JOIN article_settings aspl2 ON (aspl2.article_id=a.article_id AND aspl2.setting_name = ? AND aspl2.locale = a.locale)
LEFT JOIN article_settings asl2 ON (asl2.article_id=a.article_id AND asl2.setting_name = ? AND asl2.locale = ?)
LEFT JOIN section_settings spl ON (spl.section_id=sdec.section_id AND spl.setting_name = ? AND spl.locale = ?)
LEFT JOIN section_settings sl ON (sl.section_id=sdec.section_id AND sl.setting_name = ? AND sl.locale = ?)
WHERE a.journal_id = ? AND sdec2.section_decision_id IS NULL
ORDER BY a.article_id', array('title', 'title', $locale, 'abstract', 'abstract', $locale, 'title', $primaryLocale, 'title', $locale, $journalId));
$articlesReturner = new DBRowIterator($result);
$result =& $this->retrieve('SELECT MAX(sd.date_decided) AS date,
sd.article_id AS article_id
FROM section_decisions sd,
articles a
WHERE a.journal_id = ? AND
a.article_id = sd.article_id
GROUP BY sd.article_id', array($journalId));
$decisionDatesIterator = new DBRowIterator($result);
$decisionsReturner = array();
while ($row =& $decisionDatesIterator->next()) {
$result =& $this->retrieve('SELECT decision AS decision,
article_id AS article_id
FROM section_decisions
WHERE date_decided = ? AND
article_id = ?', array($row['date'], $row['article_id']));
$decisionsReturner[] = new DBRowIterator($result);
unset($result);
}
$articleDao =& DAORegistry::getDAO('ArticleDAO');
$articles =& $articleDao->getArticlesByJournalId($journalId);
$authorsReturner = array();
$index = 1;
while ($article =& $articles->next()) {
$result =& $this->retrieve('SELECT aa.first_name AS fname,
aa.middle_name AS mname,
aa.last_name AS lname,
aa.email AS email,
aa.country AS country,
aa.url AS url,
aa.affiliation AS affiliation
FROM authors aa
LEFT JOIN articles a ON (aa.submission_id = a.article_id)
WHERE
a.journal_id = ? AND
aa.submission_id = ?', array($journalId, $article->getId()));
$authorIterator = new DBRowIterator($result);
$authorsReturner[$article->getId()] =& $authorIterator;
unset($authorIterator);
$index++;
unset($article);
}
return array($articlesReturner, $authorsReturner, $decisionsReturner);
}
