Пример #1
0
                $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
Пример #2
0
/**
 * 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;
}