/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
$dataset = \App\Dataset::create(['name' => '人脸数据集', 'description' => '该数据集的标注任务是判断待标注人脸与给定标准人脸是否为同一个人，只需要选择是或者不是即可。标注正确即可获取相应的积分。']);
$datasetController = new DatasetController();
$datasetController->standardItem2DBImpl('public/dataset/facedataset/standard_item.csv', $dataset->id);
$datasetController->item2DBImpl('public/dataset/facedataset/items.csv', $dataset->id);
}
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
//
$newGoods = GoodsUser::where('is_gotten', false)->count();
$newAwards = AwardUser::where('is_gotten', false)->count();
$receivedGoods = GoodsUser::where('is_gotten', true)->count();
$receivedAwards = AwardUser::where('is_gotten', true)->count();
$datasets = Dataset::all();
return view('back.index', compact('newGoods', 'newAwards', 'receivedGoods', 'receivedAwards', 'datasets'));
}
/**
* Bootstrap the application services.
*
* @return void
*/
public function boot()
{
//
\App\Dataset::deleting(function ($dataset) {
foreach ($dataset->StandardItems as $standardItem) {
$standardItem->delete();
}
});
\App\StandardItem::deleting(function ($standardItem) {
$standardItem->Items()->delete();
});
}
public function scopeUpdateSource($query, $datasetId, $newDatasourceId)
{
if (!empty($newDatasourceId)) {
$dataset = Dataset::find($datasetId);
//is it event necessary to update source?
if ($dataset->fk_dsr_id != $newDatasourceId) {
//get all variables
$variables = $dataset->variables;
foreach ($variables as $variable) {
Variable::updateSource($variable->id, $newDatasourceId);
}
}
}
}
public function postNew()
{
if (Input::get('name') != null) {
$vis = new \App\VisualizationProject();
$vis->name = Input::get('name');
$user = Auth::user();
$vis->user_id = $user->id;
$dataset = Dataset::findOrFail(Input::get('datasetid'));
$dataset->projects()->save($vis);
Session::put('visualizationid', $vis->id);
return redirect('dataset/selection/' . $vis->id);
} else {
return response()->json(['status' => 'failed']);
}
}
Route::get('user/{id}/deladmin', 'UserController@deladmin');
Route::get('user/{id}/add2admin', 'UserController@add2admin');
Route::get('user/{id}/delreception', 'UserController@delreception');
Route::get('user/{id}/add2reception', 'UserController@add2reception');
});
Route::group(['prefix' => 'tag', 'middleware' => ['auth']], function () {
Route::get('/', function () {
return view('tag.index', ['datasets' => \App\Dataset::all()]);
});
Route::get('/{id}', function ($id) {
return view('tag.tag', ['dataset' => \App\Dataset::find($id)]);
});
});
Route::group(['prefix' => 'tag', 'middleware' => ['auth']], function () {
Route::get('/', function () {
return view('tag.index', ['datasets' => \App\Dataset::all(), 'user' => Auth::user()]);
});
Route::get('/{id}', 'TagController@getNext');
Route::post('/{id}', 'TagController@postLabel');
});
Route::group(['prefix' => 'u'], function () {
Route::get('login', 'Auth\\AuthController@getLogin');
Route::post('login', 'Auth\\AuthController@postLogin');
Route::get('logout', 'Auth\\AuthController@getLogout');
// Registration routes...
Route::get('register', 'Auth\\AuthController@getRegister');
Route::post('register', 'Auth\\AuthController@postRegister');
});
Route::group(['prefix' => 'u', 'middleware' => ['auth']], function () {
Route::get('profile', function () {
return view('user.profile', ['user' => \Illuminate\Support\Facades\Auth::user()]);
/**
* Import a collection of variables
*/
public function variables(Request $request)
{
$input = $request->all();
return DB::transaction(function () use($input) {
// First, we create the dataset object itself
$dataset = $input['dataset'];
// entityKey is a unique list of entity names/codes e.g. ['Germany', 'Afghanistan', 'USA']
$entityKey = $input['entityKey'];
// entities is a list of indices for entityKey
$entities = $input['entities'];
$years = $input['years'];
$variables = $input['variables'];
if (isset($dataset['sourceId'])) {
$sourceId = $dataset['sourceId'];
} else {
$source = $input['source'];
$sourceProps = ['name' => $source['name'], 'description' => $source['description']];
$sourceId = Datasource::create($sourceProps)->id;
}
if (isset($dataset['id'])) {
$datasetId = $dataset['id'];
} else {
$datasetProps = ['name' => $dataset['name'], 'description' => isset($dataset['description']) ? $dataset['description'] : "", 'fk_dst_cat_id' => $dataset['categoryId'], 'fk_dst_subcat_id' => $dataset['subcategoryId'], 'fk_dsr_id' => $sourceId];
$datasetId = Dataset::create($datasetProps)->id;
}
// First, we insert all of the entities with "on duplicate key update", ensuring
// they all exist in the database.
$insertQuery = "INSERT INTO entities (name, fk_ent_t_id) VALUES";
$pdo = DB::connection()->getPdo();
foreach ($entityKey as $name) {
if ($name != $entityKey[0]) {
$insertQuery .= ",";
}
$insertQuery .= " (" . $pdo->quote($name) . ",5)";
}
$insertQuery .= " ON DUPLICATE KEY UPDATE name=VALUES(name);";
DB::statement($insertQuery);
// Now we need to pull them back out again so we know what ids to go with what names
$entityNameToId = DB::table('entities')->select('id', 'name')->whereIn('name', $entityKey)->lists('id', 'name');
// Now we feed in our set of variables and associated data
foreach ($variables as $variable) {
$values = $variable['values'];
$newVariable = ['name' => $variable['name'], 'description' => $variable['description'], 'unit' => $variable['unit'], 'fk_var_type_id' => $variable['typeId'], 'fk_dst_id' => $datasetId, 'fk_dsr_id' => $sourceId, 'uploaded_by' => \Auth::user()->name, 'uploaded_at' => Carbon::now()];
$varId = DB::table('variables')->insertGetId($newVariable);
$newDataValues = [];
for ($i = 0; $i < sizeof($years); $i++) {
if ($values[$i] === '') {
// Empty cells, as opposed to zeroes, most likely should not be inserted at all
continue;
}
$newDataValues[] = ['fk_var_id' => $varId, 'fk_ent_id' => $entityNameToId[$entityKey[$entities[$i]]], 'year' => $years[$i], 'value' => $values[$i]];
// For very big datasets, there may be too many to do in a single insert
// Limit is about 25565 placeholders. So we stop and push in a bunch every so often
if (sizeof($newDataValues) > 10000) {
DB::table('data_values')->insert($newDataValues);
$newDataValues = [];
}
}
if (sizeof($newDataValues) > 0) {
DB::table('data_values')->insert($newDataValues);
}
}
return ['datasetId' => $datasetId];
});
}
public function getNext($id)
{
// TODO: Delete unused 'count'
$userid = $this->auth->user()->id;
$batchId = $this->auth->user()->batch_id;
$batch = \App\Batch::find($batchId);
if ($batch == null) {
\DebugBar::warning('$batch==null:');
$batch = $this->newBatch($id, $userid);
$this->auth->user()->batch_id = $batch->id;
$this->auth->user()->save();
} elseif ($batch->remain_count == 0) {
\DebugBar::warning('$batch->remain_count == 0');
$standard = \App\StandardItem::find($batch->standard_item_id);
// if user owns no valid batch or he/she has already done a batch
$batch = $this->newBatch($id, $userid);
if ($batch == null) {
return view('tag.no_more', ['user' => $this->auth->user(), 'dataset' => \App\Dataset::find($id)]);
}
$this->auth->user()->batch_id = $batch->id;
$this->auth->user()->save();
return view('tag.done', ['user' => $this->auth->user(), 'dataset' => \App\Dataset::find($id), 'standard' => $standard]);
}
$standard = \App\StandardItem::find($batch->standard_item_id);
$items = $standard->Items;
$item = $items[$batch->remain_count - 1];
return view('tag.tag', ['user' => $this->auth->user(), 'dataset' => \App\Dataset::find($id), 'standard' => $standard, 'batch' => $batch, 'item' => $item]);
}
/**
* Display the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function show($id)
{
//
return view('back.dataset', ['dataset' => \App\Dataset::find($id)]);
}
public function postConfiguration()
{
$tablename = Input::get('tablename');
$header = Input::get('header');
$csv = Session::get('csv');
$path = Session::get('csvpath');
// check if there is a table with the same name in the database
if (!Schema::connection('dataset')->hasTable($tablename)) {
// Save to database
$this->saveToDB($path, $tablename, $header, $csv);
// sending back with message
Session::flash('success', 'Uploaded successfully');
} else {
// sending back with message
Session::flash('error', 'Dataset with the same name already exist in database');
}
$dataset = \App\Dataset::where('table_name', $tablename)->first();
return Redirect::to('dataset/edit/' . $dataset->id);
}
/**
* Remove the specified resource from storage.
*
* @param int $id
* @return Response
*/
public function destroy(Dataset $dataset, Request $request)
{
//delete tags
$tags = $dataset->tags;
foreach ($tags as $tag) {
$tag->delete();
}
//delete variables data
$variables = $dataset->variables;
foreach ($variables as $variable) {
$variable->data()->delete();
}
//delete variables
$dataset->variables()->delete();
//delete itself
$dataset->delete();
Cache::flush();
return redirect()->route('datasets.index')->with('message', 'Dataset deleted.');
}
public function getIndex()
{
//return response()->json(Session::get('mappings'));
// get selected mapping
$mappingid = Input::get('mappingid');
$mappings = Session::get('mappings');
$mapping = $mappings[$mappingid];
// get selected columns name
$header = [];
$dataset = \App\Dataset::findOrFail($mapping->datasetid);
$tablename = $dataset->table_name;
$attributes = $dataset->attributes()->get();
$header = $mapping->mappingname;
// get project
$project = \App\VisualizationProject::findOrFail(Session::get('visualizationid'));
// select only selected columns only
$datasetdata = DB::connection('dataset')->table($tablename)->select($header);
$sortdata = null;
foreach ($project->dataSelections as $selection) {
if ($selection->operand == "##SORTBY##") {
$sortdata = $selection;
} else {
$datasetdata = $datasetdata->where($selection->column_name, $selection->operator, $selection->operand);
}
}
if ($sortdata !== null) {
$sorttype = $sortdata->operator == '>' ? 'asc' : 'desc';
$datasetdata = $datasetdata->orderBy($sortdata->column_name, $sorttype);
}
$datasetdata = $datasetdata->get();
if (Session::get('aggregate')) {
// aggregate dataset
foreach ($header as $attribute) {
// check if there is an attribute that contain aggregation information
$categoryData = $dataset->categories()->where('name', $attribute)->first();
if ($categoryData != null) {
$categories = DB::connection('dataset')->table($tablename)->select($attribute)->distinct()->get();
$rowsData = [];
foreach ($categories as $category) {
$rows = DB::connection('dataset')->table($tablename)->where($attribute, $category->{$attribute})->get();
$rowData = (object) [];
// initialize rowdata
foreach ($header as $att) {
$rowData->{$att} = 0;
}
foreach ($rows as $row) {
foreach ($header as $att) {
if ($att == $attribute) {
$rowData->{$att} = $row->{$att};
} else {
if (is_numeric($row->{$att})) {
$rowData->{$att} += $row->{$att};
//var_dump($row->$att);
} else {
$rowData->{$att} = $row->{$att};
}
}
}
}
//exit();
$rowsData[] = $rowData;
if ($categoryData->type == "AVERAGE") {
foreach ($header as $att) {
if ($att != $attribute) {
if (is_numeric($row->{$att})) {
$rowData->{$att} /= count($rows);
}
}
}
}
}
$datasetdata = $rowsData;
break;
}
}
}
// join dataset with header
$data[] = $header;
foreach ($datasetdata as $set) {
$row = [];
foreach ($set as $value) {
$row[] = $value;
}
$data[] = $row;
}
// get visualization visual variable information
$visualization = \App\Visualization::findOrFail($mapping->visualizationid);
$category = [];
// save which data/column is used as category
for ($i = 0; $i < count($visualization->visualVariables); $i++) {
if ($visualization->visualVariables[$i]->pivot->type == "category") {
$category[] = $header[$i];
}
$test[] = [$i, $visualization->visualVariables[$i]->pivot->type];
}
//return response()->json($test);
$visdata = ['visualization' => $visualization->name, 'category' => $category, 'header' => $header, 'data' => $data, 'activities' => $visualization->activities];
Session::put('visdata', (object) $visdata);
// Call the view and then wait for data request
return view('visualization.view', ['projectname' => $project->name]);
}
/**
* Remove the specified resource from storage.
*
* @param int $id
* @return Response
*/
public function destroy(Dataset $dataset, Request $request)
{
$dataset->delete();
Cache::flush();
return redirect()->route('datasets.index')->with('message', 'Dataset deleted.');
}
public function standardItem2DBImpl($path, $datasetId)
{
if (StandardItem::where('dataset_id', $datasetId)->count() == 0) {
$maxID = DB::table("standard_items")->max('id');
if ($maxID == null) {
$maxID = 1;
} else {
$maxID += 1;
}
$dataset = Dataset::findOrFail($datasetId);
$dataset->current_standard_id = $maxID;
$dataset->save();
}
$fh = fopen($path, 'r');
while ($line = fgets($fh)) {
$row = explode(",", $line);
StandardItem::create(['name' => $row[0], 'slug' => $row[1], 'path' => $row[2], 'dataset_id' => $datasetId]);
}
fclose($fh);
}
/**
* 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 dataset(Request $request)
{
try {
if ($request->input('new_dataset') === '1') {
//creating new dataset
$datasetName = $request->has('name') ? $request->get('name') : '';
$datasetTags = $request->has('datasetTags') ? $request->get('datasetTags') : '';
$datasetDescription = $request->has('description') ? $request->get('description') : '';
$datasourceId = $request->has('datasourceId') ? $request->get('datasourceId') : '';
$datasetCategoryId = $request->has('categoryId') ? $request->get('categoryId') : '';
$datasetSubcategoryId = $request->has('subcategoryId') ? $request->get('subcategoryId') : '';
$datasetData = ['name' => $datasetName, 'fk_dst_cat_id' => $datasetCategoryId, 'fk_dst_subcat_id' => $datasetSubcategoryId, 'description' => $datasetDescription, 'fk_dsr_id' => $datasourceId];
$dataset = Dataset::create($datasetData);
$datasetId = $dataset->id;
//process possible tags
if (!empty($datasetTags)) {
$tagsArr = explode(',', $datasetTags);
foreach ($tagsArr as $tag) {
$tag = DatasetTag::create(['name' => $tag]);
$tagId = $tag->id;
$datasetTagLink = LinkDatasetsTags::create(['fk_dst_id' => $datasetId, 'fk_dst_tags_id' => $tagId]);
}
}
} else {
//existing dataset - do nothing for now
$datasetId = $request->input('existing_dataset_id');
}
return ['success' => true, 'data' => ['datasetId' => $datasetId]];
} catch (Exception $e) {
return ['success' => false];
}
}
