public function dimensions(Request $request)
{
set_time_limit(10);
ini_set('memory_limit', '256M');
//check we have everything we should have
if (!Input::has('dimensions')) {
//we don't have necessary info, bail out
return ['success' => false];
}
//filtering by entities?
$selectedCountriesIds = Input::get("selectedCountries");
$selectedCountriesIdsString = !empty($selectedCountriesIds) && count($selectedCountriesIds) > 0 ? implode(",", $selectedCountriesIds) : "";
//filtering by time?
$chartTime = Input::get("chartTime");
if (Input::has('chartId')) {
//caching - construct key with selected countries as well
$key = 'chart-dimensions-' . Input::get('chartId') . '-countries-' . $selectedCountriesIdsString;
//if there's something in cache and not exporting
if (Cache::has($key) && !Input::has('export') && (Input::has('cache') && Input::get('cache') === "true")) {
//return Cache::get( $key );
}
}
$data = array();
//extra array for storing values for export
$times = array();
$datasourcesIdsArr = array();
$dimensionsInput = Input::get('dimensions');
$dimensions = json_decode($dimensionsInput);
//isn't it just empty object
if (empty($dimensions)) {
return ['success' => false];
}
$chartType = Input::get('chartType');
//there's special setting for linechart
$isLineChart = $chartType == "1" || $chartType == "4" || $chartType == "5" || $chartType == "6" ? true : false;
//find out how many variables we have
$groupByEntity = Input::get('groupByVariables') == 'false' ? true : false;
//special case for linechart with multiple variables
$multiVariantByEntity = false;
if ($groupByEntity && $isLineChart && count($dimensions) > 1) {
//make sure they're all
foreach ($dimensions as $dimension) {
if ($dimension->property !== "y") {
$multiVariantByEntity = false;
break;
}
$multiVariantByEntity = true;
}
}
$timeType = '';
if ($groupByEntity) {
$entities = array();
$dataByEntity = array();
$dataByEntityTime = array();
} else {
$variables = array();
$dataByVariable = array();
$dataByVariableTime = array();
}
/**
* 1) get data into variable
**/
//store the longest variable, will be used as main one
$dimensionsByKey = [];
$minDataLength = false;
$mainDimId = false;
$otherDimIds = [];
//for edge cases for legend, we need to store entityname
$entityName = "";
//categorical data
$categoricalData = array();
$categoricalData["color"] = array();
$categoricalData["shape"] = array();
$categoricalDimensions = array();
foreach ($dimensions as $dimension) {
$id = $dimension->variableId;
//use query builder instead of eloquent
$variableQuery = DB::table('data_values')->select('data_values.*', 'times.*', 'entities.name as name', 'variables.name as variable_name')->join('entities', 'data_values.fk_ent_id', '=', 'entities.id')->join('variables', 'data_values.fk_var_id', '=', 'variables.id')->join('times', 'data_values.fk_time_id', '=', 'times.id')->where('data_values.fk_var_id', $id);
//are we filtering based on entity selection?
if (!empty($selectedCountriesIds) && count($selectedCountriesIds) > 0) {
$variableQuery->whereIn('data_values.fk_ent_id', $selectedCountriesIds);
}
//are we filtering based on time selection?
if (!empty($chartTime) && count($chartTime) > 1) {
//exclude categorical properties from time filtering
if ($dimension->property !== "color" && $dimension->property !== "shape") {
$minTime = $chartTime[0];
$maxTime = $chartTime[1];
$variableQuery->where('times.startDate', '>=', $minTime);
//$variableQuery->where( 'times.date', '>=', $minTime );
$variableQuery->where('times.endDate', '<=', $maxTime);
//$variableQuery->where( 'times.date', '<=', $maxTime );
}
}
$variableData = $variableQuery->get();
//insert data into existing variable
$dimension->data = $variableData;
//is shortes variable? cannot be color/shape variable
$dataLen = count($variableData);
if (($dataLen > $minDataLength || !$minDataLength) && ($dimension->property != "color" && $dimension->property != "shape")) {
$minDataLength = $dataLen;
$mainDimId = $id;
}
//is categorical data
if ($dimension->property === "color" || $dimension->property === "shape") {
//store it for later processing
$categoricalDimensions[] = $dimension;
}
}
/**
* 2) assign data to entities
**/
foreach ($dimensions as $dimension) {
$id = $dimension->variableId;
$property = $dimension->property;
$variableData = $dimension->data;
//store in array for step 3
$dimensionsByKey[$id] = $dimension;
if ($id != $mainDimId) {
$otherDimIds[] = $id;
}
//selectedCountries
if ($groupByEntity) {
//group variable data by entities
//$i = 0;
$oldEntityId = -1;
foreach ($variableData as $datum) {
//$entityId = $datum->fk_ent_id;
$entityId = !$multiVariantByEntity ? $datum->fk_ent_id : $datum->fk_ent_id . "-" . $datum->fk_var_id;
//check if new entity and we need to reset cycle
if ($oldEntityId != $entityId) {
//$i = 0;
}
$oldEntityId = $entityId;
//do we have already object for that entity
if (!array_key_exists($entityId, $dataByEntity)) {
$key = !$multiVariantByEntity ? $datum->name : $datum->name . " - " . $datum->variable_name;
$dataByEntity[$entityId] = array("id" => $entityId, "key" => $key, "entity" => $datum->name, "values" => []);
}
//is it first property being saved for given property
if (!array_key_exists($property, $dataByEntity[$entityId]["values"])) {
$dataByEntity[$entityId]["values"][$property] = [];
}
//store value
//AMMEND HERE - store as startYear-endYear?
$timeId = $datum->fk_ttype_id !== "6" ? floatval($datum->date) : floatval($datum->startDate) . "-" . floatval($datum->endDate);
$dataByEntity[$entityId]["values"][$property][$timeId] = $property != "color" && $property != "shape" && $property != "map" ? floatval($datum->value) : $datum->value;
//need to store dimension variablename, dimensions are returned
if (!array_key_exists("variableName", $dimension)) {
$dimension->variableName = $datum->variable_name;
}
//if is linechart, store time into x axis
/*if( $isLineChart ) {
$dataByEntity[ $entityId ][ "values" ][ $i ][ "x" ] = floatval( $datum->date );
}
$i++;*/
//store time type if not stored
if (empty($timeType)) {
$timeType = TimeType::find($datum->fk_ttype_id)->name;
}
//store for the need of export
if (!array_key_exists($entityId, $dataByEntityTime)) {
$dataByEntityTime[$entityId] = [];
$entities[$entityId] = $datum->name;
}
$dataByEntityTime[$entityId][$datum->label] = $datum->value;
//AMMEND HERE - store simply as a string?
$times[floatval($datum->date)] = true;
$datasourcesIdsArr[$datum->fk_dsr_id] = true;
}
} else {
//multivariables
//get variable names
$variable = Variable::find($dimension->variableId);
$key = !empty($variable) && isset($variable->name) ? $variable->name : "";
//could have display name
if (!empty($dimension) && !empty($dimension->displayName)) {
$key = $dimension->displayName;
}
$dataByVariable["id-" . $id] = array("id" => $id, "key" => $key, "values" => []);
//store variable name to dimension info (useful for stack bar chart)
$dimensionsByKey[$id]->variableName = $key;
foreach ($variableData as $datum) {
//store entity name for legend purposes
$entityName = $datum->name;
$dataByVariable["id-" . $id]["values"][] = array("x" => floatval($datum->date), "y" => floatval($datum->value));
$times[$datum->label] = true;
$datasourcesIdsArr[$datum->fk_dsr_id] = true;
//store time type if not stored
if (empty($timeType)) {
$timeType = TimeType::find($datum->fk_ttype_id)->name;
}
//store for the need of export
if (!array_key_exists($dimension->variableId, $dataByVariableTime)) {
$dataByVariableTime[$dimension->variableId] = [];
$variables[$dimension->variableId] = $datum->fk_var_id;
}
$dataByVariableTime[$dimension->variableId][$datum->label] = $datum->value;
}
}
}
/**
* 3) prepare array for different chart types
**/
//$normalizedData = [];
$mainDimension = $dimensionsByKey[$mainDimId];
if ($groupByEntity) {
$normalizedData = Chart::formatDataForChartType($chartType, $dataByEntity, $dimensionsByKey, $times, false, $mainDimension, $otherDimIds);
} else {
//grouping by variable, for linechart, we already have what we need
if ($chartType !== '1' && $chartType !== '2') {
$dataByVariable = Chart::formatDataForChartType($chartType, $dataByVariableTime, $dimensionsByKey, $times, true, $mainDimension, $otherDimIds, $entityName);
}
}
if ($chartType == '9999') {
//if getting dimensions for map, don't need info bellow, just send of the data
$data = [];
foreach ($normalizedData as $entityData) {
$data[] = $entityData;
}
$result = ['success' => true, 'data' => $data];
//TODO - put to cache
//store into cache - there is no cache
/*if( !empty( $key ) ) {
$minutes = 60*24;
Cache::put( $key, $result, $minutes );
}*/
return $result;
}
if ($groupByEntity) {
//convert to array
foreach ($normalizedData as $entityData) {
//TODO better check for this?
if ($entityData['values']) {
//here we add any possible categorical data
foreach ($categoricalDimensions as $catDimension) {
$entityId = $entityData['id'];
//is there data for specific property
if (array_key_exists('values', $dataByEntity[$entityId]) && array_key_exists($catDimension->property, $dataByEntity[$entityId]['values'])) {
//get value - http://stackoverflow.com/questions/1028668/get-first-key-in-a-possibly-associative-array
$value = reset($dataByEntity[$entityId]['values'][$catDimension->property]);
$catValue = Chart::getValueForCategory($catDimension->property, $categoricalData, $value);
//color is assinged to whole entity, shape is assigned to individual data entries
if ($catDimension->property === "color") {
$entityData[$catDimension->property] = $catValue;
} else {
if ($catDimension->property === "shape") {
foreach ($entityData["values"] as &$entityValue) {
$entityValue[$catDimension->property] = $catValue;
}
}
}
}
}
$data[] = $entityData;
}
}
} else {
//convert to array
foreach ($dataByVariable as $varData) {
$data[] = $varData;
}
}
/**
* 4) fetch all the other necessary data
**/
//get all necessary info for datasources
$datasources = array();
$prevDimension = "";
$sourcesByNameDim = array();
foreach ($dimensions as $dimension) {
$datasource = new \stdClass();
//special dimension header for linechart
$dsr = Variable::getSource($dimension->variableId)->first();
if ($isLineChart) {
$dimension = false;
}
$currDimension = !empty($dimension) && isset($dimension->name) ? $dimension->name : "undefined";
$datasource->description = !empty($dsr) ? $this->createSourceDescription($dimension, $dsr, $currDimension === $prevDimension) : '';
$datasource->name = !empty($dsr) && !empty($dsr->name) ? $dsr->name : '';
$datasource->link = !empty($dsr) && !empty($dsr->name) ? $dsr->link : '';
//make sure we don't repeat for the same name and dimension
$nameDimKey = $currDimension . "-" . $datasource->name;
if (!isset($sourcesByNameDim[$nameDimKey])) {
$datasources[] = $datasource;
$sourcesByNameDim[$nameDimKey] = true;
}
//store curr dimension so we don't have to repeat title for next if it's same
$prevDimension = !empty($dimension) && isset($dimension->name) ? $dimension->name : "";
}
/*$datasourcesIds = array_keys( $datasourcesIdsArr );
$datasourcesSources = Variable::getSources( $datasourcesIds )->get();//Datasource::findMany( $datasourcesIds );
$datasources = array();
//format datasources info (create generated tables)
foreach( $datasourcesSources as $datasourceSource ) {
$datasource = new \stdClass();
$dimension = $this->findDimensionForVarId( $dimensions, $datasourceSource->var_id );
//special dimension header for linechart
if( $isLineChart ) {
$dimension = false;
}
$datasource->description = $this->createSourceDescription( $dimension, $datasourceSource );
$datasource->name = $datasourceSource->name;
$datasource->link = $datasourceSource->link;
//$datasource->description = $datasourceSource->description;
$datasources[] = $datasource;
}*/
//process data to csv friendly format
$timeKeys = array_keys($times);
//sort timeKeys by time
//AMMEND HERE - what is intervals
usort($timeKeys, function ($a, $b) {
if ($a == $b) {
return 0;
} else {
return $a > $b ? 1 : -1;
}
});
//get all the licence information
$license = License::find(1)->first();
if ($request->ajax()) {
$result = ['success' => true, 'data' => $data, 'dimensions' => $dimensions, 'datasources' => $datasources, 'timeType' => $timeType, 'license' => $license];
//store into cache - there is no cache
if (!empty($key)) {
$minutes = 60 * 24;
Cache::put($key, $result, $minutes);
}
return $result;
} else {
//export is now happening in front-end
if (Input::has('export') && Input::get('export') == 'csv') {
//http://localhost:8888/oxford/our-world-in-data-chart-builder/public/data/dimensions?dimensions=%5B%7B%22variableId%22%3A%221%22%2C%22property%22%3A%22y%22%2C%22name%22%3A%22Y+axis%22%7D%5D
//return $data;
//return $this->downloadCsv( $exportData );
} else {
//not ajax request, nor csv export, just spit out whatever is in data
return $data;
}
}
}
