/**
* Execute the command.
*
* @return void
*/
public function handle()
{
//set_time_limit( 600 );
//ini_set('memory_limit', '256M');
//bump up limits
set_time_limit(600);
ini_set('memory_limit', '256M');
return;
//will we be checking entities
$entityCheck = $this->validate_entities == 'on' ? false : true;
//create new file
$inputFileData = ['raw_data' => $this->data, 'fk_user_id' => $this->userId];
$inputFile = InputFile::create($inputFileData);
$inputFileDataId = $inputFile->id;
$multivariantDataset = $this->multivariant_dataset;
$variables = $this->variables;
if (!empty($variables)) {
$entityData = [];
//creating new datasource, if there is some
$sourceName = $this->source_name;
if (!empty($sourceName)) {
$datasourceData = ['name' => $this->source_name, 'link' => $this->source_link, 'description' => $this->source_description];
$datasource = Datasource::create($datasourceData);
} else {
//fake datasoure
$datasource = new \stdClass();
$datasource->id = null;
}
//create new dataset or pick existing one
$datasetName = $this->new_dataset_name;
$datasetData = ['name' => $datasetName, 'fk_dst_cat_id' => $this->category_id, 'fk_dst_subcat_id' => $this->subcategory_id, 'description' => $this->new_dataset_description, 'fk_dsr_id' => $datasource->id];
$dataset = Dataset::create($datasetData);
$datasetId = $dataset->id;
//process possible tags
$tagsInput = $this->new_dataset_tags;
if (!empty($tagsInput)) {
$tagsArr = explode(',', $tagsInput);
foreach ($tagsArr as $tag) {
$tag = DatasetTag::create(['name' => $tag]);
$tagId = $tag->id;
$datasetTagLink = LinkDatasetsTags::create(['fk_dst_id' => $datasetId, 'fk_dst_tags_id' => $tagId]);
}
}
//store inserted variables, for case of rolling back
$inserted_variables = array();
foreach ($variables as $variableJsonString) {
//convert back single out to actual single quote
//$variableJsonString = str_replace( "'", "‘", $variableJsonString );
//setting json_decode second param to false, to try to save memory
$variableObj = json_decode($variableJsonString, false);
$variableData = ['name' => $variableObj->name, 'fk_var_type_id' => $this->variable_type, 'fk_dst_id' => $datasetId, 'unit' => $variableObj->unit, 'description' => $variableObj->description, 'fk_dsr_id' => $datasource->id];
//update of existing variable or new variable
if (!isset($variableObj->id)) {
//new variable
$variable = Variable::create($variableData);
} else {
//update variable
$variable = Variable::find($variableObj->id);
$variable->fill($variableData);
$variable->save();
}
$variableId = $variable->id;
$inserted_variables[] = $variable;
$variableValues = $variableObj->values;
foreach ($variableValues as $countryValue) {
$entityData = ['name' => $countryValue->key, 'fk_ent_t_id' => 5, 'validated' => 0];
if ($entityCheck) {
//entity validation (only if not multivariant dataset)
//find corresponding iso code
$entityIsoName = EntityIsoName::match($entityData['name'])->first();
if (!$entityIsoName) {
//!haven't found corresponding country, throw an error!
//rollback everything first
foreach ($inserted_variables as $inserted_var) {
$inserted_var->data()->delete();
$inserted_var->delete();
}
//is new dataset
if ($this->new_dataset === '1') {
$dataset = Dataset::find($datasetId);
//delete itself
$dataset->delete();
}
\Log::error('Error non-existing entity in dataset.');
\Log::error($entityData['name']);
return redirect()->route('import')->with('message', 'Error non-existing entity in dataset.')->with('message-class', 'error');
}
//enter standardized info
$entityData['name'] = $entityIsoName->name;
$entityData['code'] = $entityIsoName->code;
$entityData['validated'] = 1;
}
//find try finding entity in db
if (isset($entityIsoName)) {
$entity = Entity::where('code', $entityIsoName->code)->first();
} else {
//not standardized data
$entity = Entity::where('code', $entityData['name'])->orWhere('name', $entityData['name'])->first();
}
if (!$entity) {
//entity haven't found in database, so insert it
$entity = Entity::create($entityData);
}
//check to override validation if stored in db not validated and now is validate
if ($entity->validated == 0 && $entityData['validated'] === 1) {
$entity->validated = 1;
$entity->save();
}
$entityId = $entity->id;
$countryValues = $countryValue->values;
//prepare vars for mass insert
$times = [];
$values = [];
//TODO - get latest time for base timeId
$lastTime = Time::orderBy('id', 'desc')->first();
$timeId = !empty($lastTime) ? $lastTime->id : 0;
foreach ($countryValues as $value) {
if ($this->hasValue($value->x) && $this->hasValue($value->y)) {
$timeId++;
//create time
$timeObj = $value->x;
$timeValue = ['startDate' => isset($timeObj->sd) ? $timeObj->sd : "", 'endDate' => isset($timeObj->ed) ? $timeObj->ed : "", 'date' => isset($timeObj->d) ? $timeObj->d : "", 'label' => isset($timeObj->l) ? $timeObj->l : ""];
//convert timedomain
$fk_ttype_id = 1;
if (!empty($timeObj->td)) {
$ttQuery = TimeType::query();
$fk_ttype_id = $ttQuery->whereRaw('LOWER(`name`) like ?', [$timeObj->td])->first()->id;
}
$timeValue['fk_ttype_id'] = $fk_ttype_id;
//using mass insert instead
//$time = Time::create( $timeValue );
//$timeId = $time->id;
$times[] = $timeValue;
//create value
$dataValueData = ['value' => $value->y, 'fk_time_id' => $timeId, 'fk_input_files_id' => $inputFileDataId, 'fk_var_id' => $variableId, 'fk_ent_id' => $entityId, 'fk_dsr_id' => $datasource->id];
//using mass insert instead
//$dataValue = DataValue::create( $dataValueData );
$values[] = $dataValueData;
}
}
//mass insertion
Time::insert($times);
DataValue::insert($values);
}
}
}
}
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;
}
}
}
public function entity(Request $request)
{
try {
$name = $request->get('name');
$entityCheck = $request->has('entityCheck') ? $request->get('entityCheck') : false;
$inputFileDataId = $request->has('inputFileId') ? $request->get('inputFileId') : '';
$datasourceId = $request->has('datasourceId') ? $request->get('datasourceId') : '';
$variableId = $request->has('variableId') ? $request->get('variableId') : '';
$entityData = ['name' => $name, 'fk_ent_t_id' => 5, 'validated' => 0];
if ($entityCheck) {
//entity validation (only if not multivariant dataset)
//find corresponding iso code
$entityIsoName = EntityIsoName::match($entityData['name'])->first();
if (!$entityIsoName) {
return redirect()->route('import')->with('message', 'Error non-existing entity in dataset.')->with('message-class', 'error');
}
//enter standardized info
$entityData['name'] = $entityIsoName->name;
$entityData['code'] = $entityIsoName->code;
$entityData['validated'] = 1;
}
//find try finding entity in db
if (isset($entityIsoName)) {
$entity = Entity::where('code', $entityIsoName->code)->first();
} else {
//not standardized data
$entity = Entity::where('code', $entityData['name'])->orWhere('name', $entityData['name'])->first();
}
if (!$entity) {
//entity haven't found in database, so insert it
$entity = Entity::create($entityData);
}
//check to override validation if stored in db not validated and now is validate
if ($entity->validated == 0 && $entityData['validated'] === 1) {
$entity->validated = 1;
$entity->save();
}
$entityId = $entity->id;
$countryValues = $request->get("values");
//$countryValue->values;
//prepare vars for mass insert
$times = [];
$values = [];
//TODO - get latest time for base timeId
$lastTime = Time::orderBy('id', 'desc')->first();
$timeId = !empty($lastTime) ? $lastTime->id : 0;
foreach ($countryValues as $value) {
if (isset($value['x']) && isset($value['y']) && $this->hasValue($value['x']) && $this->hasValue($value['y'])) {
$timeId++;
//create time
$timeObj = $value['x'];
$timeValue = ['startDate' => isset($timeObj['sd']) ? $timeObj['sd'] : "", 'endDate' => isset($timeObj['ed']) ? $timeObj['ed'] : "", 'date' => isset($timeObj['d']) ? $timeObj['d'] : "", 'label' => isset($timeObj['l']) ? $timeObj['l'] : ""];
//convert timedomain
$fk_ttype_id = 1;
if (!empty($timeObj['td'])) {
$ttQuery = TimeType::query();
$fk_ttype_id = $ttQuery->whereRaw('LOWER(`name`) like ?', [$timeObj['td']])->first()->id;
}
$timeValue['fk_ttype_id'] = $fk_ttype_id;
//using mass insert instead
//$time = Time::create( $timeValue );
//$timeId = $time->id;
$times[] = $timeValue;
//create value
$dataValueData = ['value' => $value['y'], 'fk_time_id' => $timeId, 'fk_input_files_id' => $inputFileDataId, 'fk_var_id' => $variableId, 'fk_ent_id' => $entityId, 'fk_dsr_id' => $datasourceId];
//using mass insert instead
//$dataValue = DataValue::create( $dataValueData );
$values[] = $dataValueData;
}
}
//mass insertion
Time::insert($times);
DataValue::insert($values);
return ['success' => true];
} catch (Exception $e) {
return ['success' => false];
}
}