public function annotationsAction()
{
$select = $this->dbAdapter->select();
$select->from(array('anno' => Annotations::TABLE_NAME), array(Annotations::COL_ID, Annotations::COL_DECIMAL, Annotations::COL_GROUP, Annotations::COL_CE_HAS_IMAGE_ID));
$select->join(array('cehim' => CeHasImage::TABLE_NAME), $this->dbAdapter->quoteIdentifier('anno.' . Annotations::COL_CE_HAS_IMAGE_ID) . '=' . $this->dbAdapter->quoteIdentifier('cehim.' . CeHasImage::COL_ID), array());
$select->join(array('part' => Participant::TABLE_NAME), $this->dbAdapter->quoteIdentifier('anno.' . Annotations::COL_PART_ID) . '=' . $this->dbAdapter->quoteIdentifier('part.' . Participant::COL_ID), array(Participant::COL_NUMBER, Participant::COL_ID));
$select->join(array('im' => Image::TABLE_NAME), $this->dbAdapter->quoteIdentifier('cehim.' . CeHasImage::COL_IMAGE_ID) . '=' . $this->dbAdapter->quoteIdentifier('im.' . Image::COL_ID), array(Image::COL_ORIGINAL_FILENAME));
$select->join(array('fish' => Fish::TABLE_NAME), $this->dbAdapter->quoteIdentifier('im.' . Image::COL_FISH_ID) . '=' . $this->dbAdapter->quoteIdentifier('fish.' . Fish::COL_ID), array(Fish::COL_SAMPLE_CODE));
$constCeId = CalibrationExercise::COL_ID;
$ceId = $this->getRequest()->getParam($constCeId);
$filename = 'annotations_all.csv';
if ($this->getRequest()->getParam('level')) {
//with a certain expertise level
if ($this->getRequest()->getParam('level') != 'all') {
$expertiseLevel = $this->getRequest()->getParam('level');
$select->where('part.' . Participant::COL_EXPERTISE_LEVEL . "=?", ucfirst($expertiseLevel));
$filename = 'annotations_' . $expertiseLevel . '.csv';
} else {
$filename = 'annotations_all.csv';
}
}
if ($this->getRequest()->getParam('stock')) {
//only stock assessment
$select->where('part.' . Participant::COL_STOCK_ASSESSMENT . "=1");
$filename = 'annotations_stock.csv';
}
$select->where('cehim.' . CeHasImage::COL_CALIBRATION_EXERCISE_ID . "=?", $ceId);
$select->where('(anno.' . Annotations::COL_FINAL . "=?", 1);
$select->orWhere('anno.' . Annotations::COL_GROUP . "=?)", 1);
$select->order('fish.' . Fish::COL_ID);
// echo $select;
$annoArray = $this->dbAdapter->fetchAll($select);
//get the readers
$partArray = $this->getParticipantList($ceId);
$this->view->ceId = $ceId;
$this->view->myRequest = $this->getRequest();
if (empty($partArray)) {
$this->view->noPart = TRUE;
$this->render('annotations');
return;
}
$partCount = count($partArray);
//add the group reader
array_push($partArray, array(User::COL_FIRSTNAME => '', User::COL_LASTNAME => '', Participant::COL_NUMBER => 'Group', Participant::COL_ID => 0, 'Institution' => ''));
//add the APE column
array_push($partArray, array(User::COL_FIRSTNAME => '', User::COL_LASTNAME => '', Participant::COL_NUMBER => 'APE', Participant::COL_ID => -1, 'Institution' => ''));
//add the CV column
array_push($partArray, array(User::COL_FIRSTNAME => '', User::COL_LASTNAME => '', Participant::COL_NUMBER => 'CV', Participant::COL_ID => -2, 'Institution' => ''));
//add the STDDEV column
array_push($partArray, array(User::COL_FIRSTNAME => '', User::COL_LASTNAME => '', Participant::COL_NUMBER => 'STDDEV', Participant::COL_ID => -3, 'Institution' => ''));
//add the VARIANCE column
array_push($partArray, array(User::COL_FIRSTNAME => '', User::COL_LASTNAME => '', Participant::COL_NUMBER => 'VARIANCE', Participant::COL_ID => -4, 'Institution' => ''));
$fishSelect = $this->dbAdapter->select();
$fishSelect->from(array('im' => Image::TABLE_NAME));
$fishSelect->join(array('fish' => Fish::TABLE_NAME), $this->dbAdapter->quoteIdentifier('fish.' . Fish::COL_ID) . '=' . $this->dbAdapter->quoteIdentifier('im.' . Image::COL_FISH_ID), array());
$fishSelect->join(array('cehim' => CeHasImage::TABLE_NAME), $this->dbAdapter->quoteIdentifier('im.' . Image::COL_ID) . '=' . $this->dbAdapter->quoteIdentifier('cehim.' . CeHasImage::COL_IMAGE_ID), array());
$fishSelect->where(CeHasImage::COL_CALIBRATION_EXERCISE_ID . "=?", $ceId);
//echo '<br>'.$fishSelect;
//set the imagelist
$imageSelect = $this->dbAdapter->select();
$imageSelect->from(array('im' => Image::TABLE_NAME));
$imageSelect->join(array('fish' => Fish::TABLE_NAME), $this->dbAdapter->quoteIdentifier('fish.' . Fish::COL_ID) . '=' . $this->dbAdapter->quoteIdentifier('im.' . Image::COL_FISH_ID), array());
$imageSelect->join(array('cehim' => CeHasImage::TABLE_NAME), $this->dbAdapter->quoteIdentifier('im.' . Image::COL_ID) . '=' . $this->dbAdapter->quoteIdentifier('cehim.' . CeHasImage::COL_IMAGE_ID), array());
$imageSelect->where(CeHasImage::COL_CALIBRATION_EXERCISE_ID . "=?", $ceId);
//echo '<br>'.$imageSelect;
$imageArray = $this->dbAdapter->fetchAll($imageSelect);
//prepare an array that looks like the csv-file
$csvArray = array();
//prepare array header
$csvArray['IMAGE'] = $partArray;
foreach ($partArray as $part) {
if (array_key_exists(Participant::COL_EXPERTISE_LEVEL, $part)) {
if (isset($part[Participant::COL_EXPERTISE_LEVEL])) {
$csvArray['Expertise level'][$part[Participant::COL_NUMBER]] = $part[Participant::COL_EXPERTISE_LEVEL];
} else {
$csvArray['Expertise level'][$part[Participant::COL_NUMBER]] = '-';
}
} else {
$csvArray['Expertise level'][$part[Participant::COL_NUMBER]] = '-';
}
if (array_key_exists(Participant::COL_EXPERTISE_LEVEL, $part)) {
if ($part[Participant::COL_STOCK_ASSESSMENT] == 1) {
$csvArray['Stock assessment'][$part[Participant::COL_NUMBER]] = 'yes';
} elseif ($part[Participant::COL_STOCK_ASSESSMENT] == 0) {
$csvArray['Stock assessment'][$part[Participant::COL_NUMBER]] = 'no';
} else {
$csvArray['Stock assessment'][$part[Participant::COL_NUMBER]] = '-';
}
} else {
$csvArray['Stock assessment'][$part[Participant::COL_NUMBER]] = '-';
}
}
foreach ($annoArray as $annoValue) {
$csvArray[$annoValue[Annotations::COL_CE_HAS_IMAGE_ID]] = array();
foreach ($partArray as $partValue) {
$csvArray[$annoValue[Annotations::COL_CE_HAS_IMAGE_ID]] += array($partValue[Participant::COL_ID] => "-");
}
}
// calculate the ape, cv etc.
$selectStat = $this->dbAdapter->select();
$selectStat->from(array('anno' => Annotations::TABLE_NAME), array('SUM_ANNO' => 'sum(' . Annotations::COL_DECIMAL . ')', 'COUNT_ANNO' => 'count(' . Annotations::COL_DECIMAL . ')', 'COUNT_IMAGE' => 'count(distinct im.IMAGE_ORIGINAL_FILENAME)', 'APE' => new Zend_Db_Expr('((1/count(ANNO_DECIMAL)) * (abs(ANNO_DECIMAL - (avg(ANNO_DECIMAL)))/avg(ANNO_DECIMAL)))/count(distinct im.IMAGE_ORIGINAL_FILENAME)*100'), 'CV' => new Zend_Db_Expr('(stddev(ANNO_DECIMAL)/avg(ANNO_DECIMAL))/count(distinct im.IMAGE_ORIGINAL_FILENAME)*100'), 'STDDEV' => new Zend_Db_Expr('stddev(ANNO_DECIMAL)'), 'VARIANCE' => new Zend_Db_Expr('variance(ANNO_DECIMAL)')));
$selectStat->join(array('cehim' => CeHasImage::TABLE_NAME), $this->dbAdapter->quoteIdentifier('anno.' . Annotations::COL_CE_HAS_IMAGE_ID) . '=' . $this->dbAdapter->quoteIdentifier('cehim.' . CeHasImage::COL_ID), array());
$selectStat->join(array('part' => Participant::TABLE_NAME), $this->dbAdapter->quoteIdentifier('anno.' . Annotations::COL_PART_ID) . '=' . $this->dbAdapter->quoteIdentifier('part.' . Participant::COL_ID), array());
//get filename for displaying and imageID in case further actions
$selectStat->join(array('im' => Image::TABLE_NAME), $this->dbAdapter->quoteIdentifier('cehim.' . CeHasImage::COL_IMAGE_ID) . '=' . $this->dbAdapter->quoteIdentifier('im.' . Image::COL_ID), array(Image::COL_ORIGINAL_FILENAME, Image::COL_ID));
$selectStat->join(array('fish' => Fish::TABLE_NAME), $this->dbAdapter->quoteIdentifier('im.' . Image::COL_FISH_ID) . '=' . $this->dbAdapter->quoteIdentifier('fish.' . Fish::COL_ID), array(Fish::COL_SAMPLE_CODE));
//XXX statistischen Werte auf Fisch gruppiert
$selectStat->group(Fish::COL_SAMPLE_CODE);
//$selectStat->group('im.' . Image::COL_ORIGINAL_FILENAME);
$selectStat->where('cehim.' . CeHasImage::COL_CALIBRATION_EXERCISE_ID . "=?", $ceId);
$selectStat->where('(anno.' . Annotations::COL_FINAL . "=?", 1);
$selectStat->orWhere('anno.' . Annotations::COL_GROUP . "=?)", 1);
if ($this->getRequest()->getParam('level')) {
//with a certain expertise level
if ($this->getRequest()->getParam('level') != 'all') {
$expertiseLevel = $this->getRequest()->getParam('level');
$selectStat->where('part.' . Participant::COL_EXPERTISE_LEVEL . "=?", ucfirst($expertiseLevel));
}
}
if ($this->getRequest()->getParam('stock')) {
//only stock assessment
$selectStat->where('part.' . Participant::COL_STOCK_ASSESSMENT . "=1");
}
//echo $selectStat;
$statArray = $this->dbAdapter->fetchAll($selectStat);
$fishStatArray = array();
foreach ($statArray as $stat) {
$fishStatArray = $fishStatArray + array($stat[Fish::COL_SAMPLE_CODE] => array('APE' => $stat['APE'], 'CV' => $stat['CV'], 'STDDEV' => $stat['STDDEV'], 'VARIANCE' => $stat['VARIANCE']));
}
// and fill in the results
foreach ($annoArray as $annoValue) {
if ($annoValue[Annotations::COL_GROUP] == '0') {
//reader final value
$csvArray[$annoValue[Annotations::COL_CE_HAS_IMAGE_ID]][$annoValue[Annotations::COL_PART_ID]] = (double) $annoValue[Annotations::COL_DECIMAL];
//$annotationsCount++;
} else {
//group value
$csvArray[$annoValue[Annotations::COL_CE_HAS_IMAGE_ID]][0] = $annoValue[Annotations::COL_DECIMAL];
//$annotationsCount++;
}
$csvArray[$annoValue[Annotations::COL_CE_HAS_IMAGE_ID]][-1] = number_format($fishStatArray[$annoValue[Fish::COL_SAMPLE_CODE]]['APE'], 4, ',', '');
$csvArray[$annoValue[Annotations::COL_CE_HAS_IMAGE_ID]][-2] = number_format($fishStatArray[$annoValue[Fish::COL_SAMPLE_CODE]]['CV'], 4, ',', '');
$csvArray[$annoValue[Annotations::COL_CE_HAS_IMAGE_ID]][-3] = number_format($fishStatArray[$annoValue[Fish::COL_SAMPLE_CODE]]['STDDEV'], 4, ',', '');
$csvArray[$annoValue[Annotations::COL_CE_HAS_IMAGE_ID]][-4] = number_format($fishStatArray[$annoValue[Fish::COL_SAMPLE_CODE]]['VARIANCE'], 4, ',', '');
}
//---------------------------------------------------------------------------
//get statistics per participant
foreach ($partArray as $part) {
$currentPartId = $part[Participant::COL_ID];
//leave out part_id <= 0, this are statistics
if ($currentPartId > 0) {
$sumDistMean = 0.0;
$sumDistGroup = 0.0;
$numberDistMean = 0;
$numberDistGroup = 0;
//iterate the final annos from the select
foreach ($annoArray as $annoValue1) {
$currentAnnoId = $annoValue1[Annotations::COL_ID];
$currentValue = NULL;
$distGroup = NULL;
$currentGroup = NULL;
$ceHasImageId = NULL;
if ($annoValue1[Annotations::COL_GROUP] == 0 && $annoValue1[Annotations::COL_PART_ID] == $currentPartId) {
//annos of distinct participant
$currentValue = (double) $annoValue1[Annotations::COL_DECIMAL];
//get current image
foreach ($annoArray as $annoValue2) {
if ($annoValue2[Annotations::COL_ID] == $currentAnnoId) {
$ceHasImageId = $annoValue2[Annotations::COL_CE_HAS_IMAGE_ID];
break;
}
}
//get group value for image in CE
foreach ($annoArray as $annoValue2) {
if ($annoValue2[Annotations::COL_CE_HAS_IMAGE_ID] == $ceHasImageId && $annoValue2[Annotations::COL_GROUP] == 1) {
$currentGroup = (double) $annoValue2[Annotations::COL_DECIMAL];
break;
}
}
//get readers mean for image in CE
$sumValues = 0.0;
$numberImageAnnotations = 0;
foreach ($annoArray as $annoValue2) {
$value = NULL;
if ($annoValue2[Annotations::COL_CE_HAS_IMAGE_ID] == $ceHasImageId && $annoValue2[Annotations::COL_GROUP] == 0) {
$value = (double) $annoValue2[Annotations::COL_DECIMAL];
$sumValues += $value;
$numberImageAnnotations++;
}
}
$mean = NULL;
if ($sumValues > 0) {
$mean = $sumValues / $numberImageAnnotations;
}
//distance of annotation to group value
if (isset($currentGroup) && isset($currentValue)) {
$distGroup = abs($currentValue - $currentGroup);
$sumDistGroup += $distGroup;
$numberDistGroup++;
} else {
$groupNotAvail = TRUE;
$distGroup = NULL;
}
//distance of annotation to mean value for this image in CE
if (isset($mean) && isset($currentValue)) {
$distMean = abs($currentValue - $mean);
$numberDistMean++;
} else {
$meanNotAvail = TRUE;
$distMean = NULL;
}
$sumDistMean += $distMean;
//distinct annotation of reader x
}
//all annos incl. group
}
//all participants
if ($numberDistMean > 0) {
$meanDistMean = $sumDistMean / $numberDistMean;
$statPart[$part[Participant::COL_NUMBER]]['meanDistMean'] = number_format($meanDistMean, 4, ',', '');
} else {
$statPart[$part[Participant::COL_NUMBER]]['meanDistMean'] = NULL;
}
if ($numberDistGroup > 0) {
$meanDistGroup = $sumDistGroup / $numberDistGroup;
$statPart[$part[Participant::COL_NUMBER]]['meanDistGroup'] = number_format($meanDistGroup, 4, ',', '');
} else {
$statPart[$part[Participant::COL_NUMBER]]['meanDistGroup'] = NULL;
}
}
//all "participants" incl. statistic cols
}
//---------------------------------------------------------------------------
foreach ($statPart as $partNo => $part) {
$csvArray['Mean of distances to mean'][$partNo] = $part['meanDistMean'];
$csvArray['Mean of distances to group'][$partNo] = $part['meanDistGroup'];
}
for ($i = 0; $i > -5; $i--) {
$csvArray['Mean of distances to mean'][$i] = '-';
$csvArray['Mean of distances to group'][$i] = '-';
}
$qu = new Default_ReferenceQuery();
$images = $qu->getImagesForCe($ceId);
$ceHimFilenameArray = $qu->getCeHasImageFilenameArray($images);
if ($this->getRequest()->getParam('as') == 'csv') {
// prepare the header
$csvString = 'IMAGE';
foreach ($partArray as $partValue) {
$csvString .= ',' . $partValue[Participant::COL_NUMBER];
}
$csvString .= "\n";
// fill in the results
foreach ($csvArray as $imageId => $results) {
if ($imageId != 'IMAGE') {
if (is_numeric($imageId)) {
$imageName = $ceHimFilenameArray[$imageId];
} else {
$imageName = $imageId;
}
$csvString .= $imageName;
foreach ($results as $decimal) {
$csvString .= "," . "\"" . $decimal . "\"";
}
$csvString .= "\n";
}
}
$this->view->csvString = $csvString;
$this->view->filename = $filename;
// generate the download file
Zend_Layout::resetMvcInstance();
$this->render('csvstring');
} else {
$this->view->csvArray = $csvArray;
$this->view->ceHimFilenameArray = $ceHimFilenameArray;
$this->view->ceId = $ceId;
}
}
