$data[] = $row;
}
}
} elseif ($ex['experimentType'] == 2) {
//IF PAIRING
foreach ($resultRows as $experimentOrder) {
for ($i = 0; $i < count($experimentOrder); $i++) {
$result = $experimentOrder[$i];
$data[] = 'Imageset' . (arrayObjectIndexOf($results['experimentOrders'], $result['eOrderId'], 'eOrder') + 1) . ',' . 'Observer' . (arrayObjectIndexOf($observers, $result['personId'], 'person') + 1) . ',' . $result['created'] . ',' . $result['name'] . ',' . $result['wonAgainstName'] . ',' . ($result['chooseNone'] == null ? $result['name'] : '0');
}
}
} elseif ($ex['experimentType'] == 3) {
//IF CATEGORY
foreach ($resultRows as $experimentOrder) {
foreach ($experimentOrder as $result) {
$data[] = 'Imageset' . (arrayObjectIndexOf($results['experimentOrders'], $result['experimentOrder'], 'eOrder') + 1) . ',' . 'Observer' . (arrayObjectIndexOf($observers, $result['personId'], 'person') + 1) . ',' . $result['created'] . ',' . $result['name'] . ',' . 'Category' . (arrayObjectIndexOf($results[3], $result['category'], 'category') + 1);
}
}
}
}
$data = str_replace('"', '', $data);
//Prints to file
$fp = fopen('php://output', 'w');
foreach ($data as $line) {
fwrite($fp, $line . PHP_EOL);
}
//Closes file
fclose($fp);
/**
* Splits results to array based on experimentorder
* @param array $array array with results
/**
* Returns data of experiment and resultsbased on experimenttype
* @param int $experimentId Id of experiment
* @param object $db object of current database
* @return array [0] = experimenttype, [1] = image sets, [2] = experimentorders, [3] = results
*/
function getExperimentResults($experimentId, $db, $complete)
{
$result = array();
$imageSetImages = array();
$pictureQueues = array();
//Gets experiment type and adds to result array
$sql = "SELECT experimentType FROM experiment WHERE experiment.id = ?";
$sth = $db->prepare($sql);
$sth->bindParam(1, $experimentId);
$sth->execute();
$type = $sth->fetch();
$type = $type['experimentType'];
$result[] = $type;
//Gets all imageset id's and adds to result
$sql = "SELECT pictureset.id, pictureset.name FROM experiment " . " JOIN experimentqueue ON experimentqueue.experiment = experiment.id " . " JOIN experimentorder ON experimentqueue.id = experimentorder.experimentQueue " . " JOIN picturequeue ON experimentorder.pictureQueue = picturequeue.id " . " JOIN pictureorder ON picturequeue.id = pictureorder.pictureQueue " . " JOIN picture ON pictureorder.picture = picture.id " . " JOIN pictureset ON picture.pictureSet = pictureset.id " . " WHERE experiment.id = ? AND experiment.person = ? " . " GROUP BY eOrder";
$sth = $db->prepare($sql);
$sth->bindParam(1, $experimentId);
$sth->bindParam(2, $_SESSION['user']['id']);
$sth->execute();
$imageSets = $sth->fetchAll();
$result[] = $imageSets;
//Gets all experimentorders, since not all images in imagesets might be used in experiment
$sql = "SELECT experimentorder.eOrder FROM experiment " . " JOIN experimentqueue ON experimentqueue.experiment = experiment.id " . " JOIN experimentorder ON experimentqueue.id = experimentorder.experimentQueue " . " JOIN picturequeue ON experimentorder.pictureQueue = picturequeue.id " . " JOIN pictureorder ON picturequeue.id = pictureorder.pictureQueue " . " JOIN picture ON pictureorder.picture = picture.id " . " JOIN pictureset ON picture.pictureSet = pictureset.id " . " WHERE experiment.id = ? AND experiment.person = ? " . " GROUP BY pictureorder.pictureQueue";
$sth = $db->prepare($sql);
$sth->bindParam(1, $experimentId);
$sth->bindParam(2, $_SESSION['user']['id']);
$sth->execute();
$experimentOrders = $sth->fetchAll();
$result['experimentOrders'] = $experimentOrders;
foreach ($experimentOrders as $experimentOrder) {
$sql = "SELECT picture.name, picture.id, picture.pictureset FROM picture " . " JOIN pictureorder ON picture.id = pictureorder.picture " . " JOIN picturequeue ON pictureorder.pictureQueue = picturequeue.id " . " JOIN experimentorder ON experimentorder.picturequeue = picturequeue.id " . " JOIN experimentqueue ON experimentorder.experimentQueue = experimentqueue.id " . " JOIN experiment ON experimentqueue.experiment = experiment.id " . " WHERE experiment.id = ? AND experiment.person = ? AND experimentorder.eOrder = ?" . " GROUP BY picture.id";
$sth = $db->prepare($sql);
$sth->bindParam(1, $experimentId);
$sth->bindParam(2, $_SESSION['user']['id']);
$sth->bindParam(3, $experimentOrder['eOrder']);
$sth->execute();
$imageSetImages[] = $sth->fetchAll();
}
$result[] = $imageSetImages;
// Retrieves url for thumbnail picture of each image set
foreach ($imageSets as $key => $imageSet) {
$sql = "SELECT * FROM picture\n\t JOIN pictureset ON picture.pictureset=pictureset.id\n\t WHERE picture.isOriginal = 1 AND picture.pictureSet = ?;";
$sth = $db->prepare($sql);
$sth->bindParam(1, $imageSet['id']);
$sth->execute();
$result['imageUrl'][$key] = generateUrl($sth->fetch());
}
//If rating experiment
if ($type == 1) {
$resultArray = array();
//Iterates through all experimentorders
for ($i = 0; $i < sizeof($experimentOrders); $i++) {
$currentResult = array();
//Result for current experimentorder
$tempArray = array();
$experimentOrder = $experimentOrders[$i];
$imagesLength = sizeof($imageSetImages[$i]);
$sql = "SELECT result.*, person.firstName, person.lastName, picture.id as pictureId, picture.name, result.created, pictureset.id AS pictureSet, eOrder " . "FROM `result` \n" . "JOIN experiment ON result.experimentId = experiment.id\n" . "JOIN person ON result.personId = person.id\n" . "JOIN pictureorder ON result.pictureOrderId = pictureorder.id\n" . "JOIN picturequeue ON pictureorder.pictureQueue = picturequeue.id\n" . "JOIN experimentorder ON picturequeue.id = experimentorder.pictureQueue\n" . "JOIN picture ON picture.id = pictureorder.picture \n" . "JOIN pictureset ON picture.pictureSet = pictureset.id \n" . ($complete == 1 ? " JOIN experimentresult ON result.experimentId = experimentresult.experiment AND experimentresult.person = result.personId " : ' ') . "WHERE result.experimentId = ? AND experimentorder.eOrder = ? AND experiment.person = ? " . ($complete == 1 ? " AND experimentresult.complete != 1 " : " ") . "ORDER BY result.id ";
$sth = $db->prepare($sql);
$sth->bindParam(1, $experimentId);
$sth->bindParam(2, $experimentOrder['eOrder']);
$sth->bindParam(3, $_SESSION['user']['id']);
$sth->execute();
$experimentOrderResult = $sth->fetchAll();
$rows = sizeof($experimentOrderResult);
//Iterates through first row of all results
for ($k = 0; $k < $rows; $k += $imagesLength) {
$tempArray = array();
//Iterates through rest of results
for ($j = $k; $j < $k + $imagesLength; $j++) {
$tmp = $experimentOrderResult[$j];
$tempArray['person'] = $tmp['firstName'] . ' ' . $tmp['lastName'];
$tempArray['timeStamp'] = $tmp['created'];
$tempArray['eOrderId'] = $tmp['eOrder'];
$imageIndex = arrayObjectIndexOf($imageSetImages[$i], $tmp['pictureId'], 'id');
$tempArray[$j - $k] = $imageIndex + 1;
// $resultRow = array('pictureName' => $tmp['name'],
// 'person' => ($tmp['lastName'].", ".$tmp['firstName']),
// 'pictureId' => $tmp['pictureId']);
// $currentResult[] = $resultRow;
}
//Saves current row to experimentorderarray
$currentResult[] = $tempArray;
}
//Saves current experimentorderresult to array
$resultArray[] = $currentResult;
}
//Adds all results to array
$result[] = $resultArray;
} elseif ($type == 2) {
//If paired comparison
$pairResults = array();
foreach ($experimentOrders as $experimentOrder) {
$sql = "SELECT picture.name, picture.id AS pictureId, pictureorder.id AS orderId, pictureorder.pOrder po, " . "COUNT(result.pictureOrderId) AS won, result.chooseNone, experimentorder.eOrder AS eOrderId, result.personId, result.created, " . "(SELECT picture.id " . " FROM picture " . " JOIN pictureorder ON picture.id = pictureorder.picture " . " JOIN picturequeue ON pictureorder.pictureQueue = picturequeue.id " . " JOIN experimentorder ON picturequeue.id = experimentorder.pictureQueue " . " WHERE pictureorder.pOrder = po AND experimentorder.eOrder = eOrderId AND picture.id != pictureId " . " LIMIT 0,1) AS wonAgainst, " . "(SELECT picture.name " . " FROM picture " . " JOIN pictureorder ON picture.id = pictureorder.picture " . " JOIN picturequeue ON pictureorder.pictureQueue = picturequeue.id " . " JOIN experimentorder ON picturequeue.id = experimentorder.pictureQueue " . " WHERE pictureorder.pOrder = po AND experimentorder.eOrder = eOrderId AND picture.id != pictureId " . " LIMIT 0,1) AS wonAgainstName " . "FROM pictureorder " . "JOIN picturequeue ON pictureorder.pictureQueue = picturequeue.id " . "JOIN experimentorder ON picturequeue.id = experimentorder.pictureQueue " . "JOIN experimentqueue on experimentorder.experimentQueue = experimentqueue.id " . "JOIN experiment ON experiment.id = experimentqueue.experiment " . "JOIN picture ON pictureorder.picture = picture.id " . "LEFT JOIN result ON pictureorder.id = result.pictureOrderId " . ($complete == 1 ? " JOIN experimentresult ON result.experimentId = experimentresult.experiment AND experimentresult.person = result.personId " : ' ') . "WHERE experiment.id = ? AND experiment.person = ? AND experimentorder.eOrder = ? AND created IS NOT NULL " . ($complete == 1 ? " AND experimentresult.complete != 1 " : " ") . "GROUP BY orderId, personId " . "ORDER BY orderId ";
$sth = $db->prepare($sql);
$sth->bindParam(1, $experimentId);
$sth->bindParam(2, $_SESSION['user']['id']);
$sth->bindParam(3, $experimentOrder['eOrder']);
$sth->execute();
$pairResults[] = $sth->fetchAll();
}
$result[] = $pairResults;
//If category experiment
} elseif ($type == 3) {
//Gets all categories and saves in result, needed in case all categories aren't chosen
$sql = "SELECT categoryname.name, categoryname.id FROM categoryname " . " JOIN experimentcategory ON categoryname.id = experimentcategory.category " . " JOIN experiment ON experiment.id = experimentcategory.experiment " . " WHERE experiment.id = ? AND experiment.person = ?";
$sth = $db->prepare($sql);
$sth->bindParam(1, $experimentId);
$sth->bindParam(2, $_SESSION['user']['id']);
$sth->execute();
$result[] = $sth->fetchAll();
//Gets all pictures and saves in result, needed in case all pictures aren't reviewed yet
$sql = "SELECT picture.name, picture.id, picture.pictureset FROM result " . " JOIN experiment ON experiment.id = result.experimentId " . " JOIN pictureorder ON result.pictureOrderId = pictureorder.id " . " JOIN picture ON pictureorder.picture = picture.id " . " WHERE experiment.id = ? AND experiment.person = ? " . " GROUP BY picture.id";
$sth = $db->prepare($sql);
$sth->bindParam(1, $experimentId);
$sth->bindParam(2, $_SESSION['user']['id']);
$sth->execute();
$result[] = $sth->fetchAll();
$resultArray = array();
for ($i = 0; $i < sizeof($experimentOrders); $i++) {
$currentResult = array();
//Result for current experimentorder
$tempArray = array();
$experimentOrder = $experimentOrders[$i];
$imagesLength = sizeof($imageSetImages[$i]);
//Gets all results and saves in result
$sql = "SELECT categoryname.name AS categoryName, result.category AS categoryId, experiment.id AS experimentId, " . " experiment.title AS experimentName,COUNT(categoryname.name) AS points, picture.name AS pictureName, " . " picture.url, picture.pictureSet, picture.id as pictureId " . " FROM `result` " . " JOIN experiment ON result.experimentId = experiment.id " . " JOIN experimenttype ON experiment.experimentType = experimenttype.id " . " JOIN categoryname ON result.category = categoryname.id " . " JOIN pictureorder ON pictureorder.id = result.pictureOrderId " . " JOIN picturequeue ON pictureorder.pictureQueue = picturequeue.id " . " JOIN experimentorder ON picturequeue.id = experimentorder.pictureQueue " . " JOIN picture ON pictureorder.picture = picture.id " . ($complete == 1 ? " JOIN experimentresult ON result.experimentId = experimentresult.id AND experimentresult.person = result.personId " : ' ') . " WHERE experimenttype.id = 3 AND experiment.id = ? AND experiment.person = ? AND experimentorder.eOrder = ? " . ($complete == 1 ? " AND experimentresult.complete != 1" : " ") . " GROUP BY picture.id, result.category ";
$sth = $db->prepare($sql);
$sth->bindParam(1, $experimentId);
$sth->bindParam(2, $_SESSION['user']['id']);
$sth->bindParam(3, $experimentOrder['eOrder']);
$sth->execute();
$resultArray[] = $sth->fetchAll();
}
$result[] = $resultArray;
//If error
} else {
$result = 0;
}
return $result;
}