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; } }