/**
* XLS file type
* @param array \Jazzee\Entity\Applicant $applicants
*/
protected function makeXls(array $applicants, \Jazzee\Interfaces\Display $display)
{
$pageAnswerCount = $this->_em->getRepository('Jazzee\\Entity\\Application')->getPageAnswerCounts($this->_application);
$rows = array();
$header = array();
foreach ($display->listElements() as $displayElement) {
if ($displayElement->getType() == 'applicant') {
$header[] = $displayElement->getTitle();
} else {
$applicationPage = $this->_application->getApplicationPageByChildPageId($displayElement->getPageId());
for ($i = 1; $i <= $pageAnswerCount[$applicationPage->getPage()->getId()]; $i++) {
$header[] = $applicationPage->getTitle() . ' ' . $i . ' ' . $displayElement->getTitle();
}
}
}
$fileName = tempnam($this->_config->getVarPath() . '/tmp/', 'applicants_download');
$handle = fopen($fileName, 'w+');
$this->writeXlsFile($header, $handle);
foreach ($applicants as $id) {
$applicant = $this->_application->formatApplicantDisplayArray($this->_em->getRepository('Jazzee\\Entity\\Applicant')->findArray($id, $display));
$arr = array();
foreach ($display->listElements() as $displayElement) {
if ($displayElement->getType() == 'applicant') {
if (is_numeric($displayElement->getName())) {
$arr[$displayElement->getName()] = 'no';
foreach ($applicant['tags'] as $tag) {
if ($tag['id'] == $displayElement->getName()) {
$arr[$displayElement->getName()] = 'yes';
break;
}
}
} else {
switch ($displayElement->getName()) {
case 'isLocked':
$arr['isLocked'] = $applicant['isLocked'] ? 'yes' : 'no';
break;
case 'lockedAt':
$arr[$displayElement->getName()] = $applicant['decision'] ? is_null($applicant['decision']['lockedAt']) ? '' : $applicant['decision']['lockedAt']->format('c') : '';
break;
case 'hasPaid':
$arr['hasPaid'] = $applicant['hasPaid'] ? 'yes' : 'no';
break;
case 'lastLogin':
$arr['lastLogin'] = !is_null($applicant['lastLogin']) ? $applicant['lastLogin']->format('c') : 'never';
break;
case 'updatedAt':
case 'createdAt':
$arr[$displayElement->getName()] = $applicant['updatedAt']->format('c');
break;
case 'decision':
$arr['decision'] = $applicant['decision'] ? $applicant['decision']['status'] : 'none';
break;
case 'percentComplete':
$arr['percentComplete'] = $applicant['percentComplete'] * 100 . '%';
break;
case 'attachments':
$values = array();
foreach ($applicant['attachments'] as $attachment) {
$values[] = $attachment['displayValue'];
}
$arr[$displayElement->getName()] = implode(',', $values);
break;
case 'status_declined':
$arr[$displayElement->getName()] = $applicant['decision'] ? is_null($applicant['decision']['declineOffer']) ? 'no' : 'yes' : 'no';
break;
case 'status_admitted':
$arr[$displayElement->getName()] = $applicant['decision'] ? is_null($applicant['decision']['finalAdmit']) ? 'no' : 'yes' : 'no';
break;
case 'status_denied':
$arr[$displayElement->getName()] = $applicant['decision'] ? is_null($applicant['decision']['finalDeny']) ? 'no' : 'yes' : 'no';
break;
case 'status_accepted':
$arr[$displayElement->getName()] = $applicant['decision'] ? is_null($applicant['decision']['acceptOffer']) ? 'no' : 'yes' : 'no';
break;
case 'status_nominate_admit':
$arr[$displayElement->getName()] = $applicant['decision'] ? is_null($applicant['decision']['nominateAdmit']) ? 'no' : 'yes' : 'no';
break;
case 'status_nominate_deny':
$arr[$displayElement->getName()] = $applicant['decision'] ? is_null($applicant['decision']['nominateDeny']) ? 'no' : 'yes' : 'no';
break;
default:
if (array_key_exists($displayElement->getName(), $applicant)) {
$arr[$displayElement->getName()] = $applicant[$displayElement->getName()];
}
}
}
} else {
$applicationPage = $this->_application->getApplicationPageByChildPageId($displayElement->getPageId());
$pageArr = array();
foreach ($applicant['pages'] as $page) {
if ($page['id'] == $applicationPage->getPage()->getId()) {
$pageArr = $page;
}
}
for ($i = 0; $i < $pageAnswerCount[$applicationPage->getPage()->getId()]; $i++) {
$value = '';
if (isset($pageArr['answers']) and array_key_exists($i, $pageArr['answers'])) {
$value = $applicationPage->getJazzeePage()->getDisplayElementValueFromArray($pageArr['answers'][$i], $displayElement);
}
$arr[$displayElement->getType() . $displayElement->getName() . $displayElement->getPageId() . $i] = $value;
}
}
}
$this->writeXlsFile($arr, $handle);
}
rewind($handle);
fclose($handle);
$this->setVar('outputType', 'file');
$this->setVar('type', 'text/xls');
$this->setVar('filename', $this->_application->getProgram()->getShortName() . '-' . $this->_application->getCycle()->getName() . date('-mdy') . '.xls');
$this->setVar('filePath', $fileName);
}
/**
* Get a deep application query builder
* @param \Jazzee\Interfaces\Display $display
*
* @return \Doctrine\ORM\QueryBuilder
*/
protected function deepApplicantQuery(\Jazzee\Interfaces\Display $display)
{
$queryBuilder = $this->_em->createQueryBuilder();
$queryBuilder->from('Jazzee\\Entity\\Applicant', 'applicant');
$queryBuilder->add('select', 'applicant, attachments, decision, tags, answers, element_answers, publicStatus, privateStatus, payment, answer_greScore, answer_toeflScore, answer_school, children, answer_attachment, children_element_answers, children_publicStatus, children_privateStatus, children_payment, children_attachment, children2, children_element_answers2, children_publicStatus2, children_privateStatus2, children_payment2, children_attachment2');
$expression = $queryBuilder->expr()->orX();
//this one is the default - if there are no pages in the display then this
//expression is the only one that will load and no pages will be loaded
$expression->add($queryBuilder->expr()->eq("answers.page", ":nothing"));
$queryBuilder->setParameter('nothing', 'nothing');
foreach ($display->getPageIds() as $key => $pageId) {
$paramKey = 'displayPage' . $key;
$expression->add($queryBuilder->expr()->eq("answers.page", ":{$paramKey}"));
$queryBuilder->setParameter($paramKey, $pageId);
}
$queryBuilder->leftJoin('applicant.answers', 'answers', 'WITH', $expression);
$expression = $queryBuilder->expr()->orX();
//this one is the default - if there are no elements in the display then this
//expression is the only one that will load and no elements will be loaded
$expression->add($queryBuilder->expr()->eq("element_answers.element", ":nothing"));
$queryBuilder->setParameter('nothing', 'nothing');
foreach ($display->getElementIds() as $key => $elementId) {
$paramKey = 'displayElement' . $key;
$expression->add($queryBuilder->expr()->eq("element_answers.element", ":{$paramKey}"));
$queryBuilder->setParameter($paramKey, $elementId);
}
$queryBuilder->leftJoin('answers.elements', 'element_answers', 'WITH', $expression);
$queryBuilder->leftJoin('answers.publicStatus', 'publicStatus');
$queryBuilder->leftJoin('answers.privateStatus', 'privateStatus');
$queryBuilder->leftJoin('applicant.attachments', 'attachments');
$queryBuilder->leftJoin('applicant.decision', 'decision');
$queryBuilder->leftJoin('applicant.tags', 'tags');
$queryBuilder->leftJoin('answers.payment', 'payment');
$queryBuilder->leftJoin('answers.children', 'children');
$queryBuilder->leftJoin('answers.attachment', 'answer_attachment');
$queryBuilder->leftJoin('answers.greScore', 'answer_greScore');
$queryBuilder->leftJoin('answers.toeflScore', 'answer_toeflScore');
$queryBuilder->leftJoin('answers.school', 'answer_school');
$queryBuilder->leftJoin('children.elements', 'children_element_answers');
$queryBuilder->leftJoin('children.publicStatus', 'children_publicStatus');
$queryBuilder->leftJoin('children.privateStatus', 'children_privateStatus');
$queryBuilder->leftJoin('children.payment', 'children_payment');
$queryBuilder->leftJoin('children.attachment', 'children_attachment');
$queryBuilder->leftJoin('children.children', 'children2');
$queryBuilder->leftJoin('children2.elements', 'children_element_answers2');
$queryBuilder->leftJoin('children2.publicStatus', 'children_publicStatus2');
$queryBuilder->leftJoin('children2.privateStatus', 'children_privateStatus2');
$queryBuilder->leftJoin('children2.payment', 'children_payment2');
$queryBuilder->leftJoin('children2.attachment', 'children_attachment2');
$queryBuilder->where('answers.parent IS NULL');
return $queryBuilder;
}
/**
* Add a display to our set
* @param \Jazzee\Interfaces\Display $display
*/
public function addDisplay(\Jazzee\Interfaces\Display $display)
{
$this->_displays[$display->getId()] = $display;
$this->calculateIntersection();
}
