/**
* Execute the console command.
*
* @return mixed
*/
public function fire()
{
$this->info('Truncate data table');
Data::truncate();
$this->info('Searching for feeds');
$activeProgramsFromDB = ActiveProgram::with('program.feeds')->where('status', 1)->get();
$fields_wanted_from_config = DaisyconHelper::getDatabaseFieldsToImport();
if (count($activeProgramsFromDB) > 0) {
foreach ($activeProgramsFromDB as $activeProgram) {
if (!empty($activeProgram->program->feeds) || !empty($activeProgram->program->name)) {
foreach ($activeProgram->program->feeds as $feed) {
$this->info($activeProgram->program->name . ' - ' . $feed->name);
$url = $feed->{"feed_link_" . strtolower(Config::get('daisycon.feed_type', 'csv'))} . '&f=' . implode(';', $fields_wanted_from_config) . '&encoding=' . Config::get("daisycon.encoding") . '&general=true' . '&nohtml=' . (Config::get("daisycon.accept_html", false) ? 'false' : 'true');
$program_id = $activeProgram->program->program_id;
$feed_id = $feed->feed_id;
$custom_categorie = $activeProgram->custom_categorie;
$this->data->importData($url, $program_id, $feed_id, $custom_categorie);
}
} else {
$this->info('Geen feeds en/of programma\'s in de database gevonden...');
continue;
}
}
} else {
return $this->info('Geen active programma\'s in de database gevonden...');
}
$this->call('daisycon:fix-data');
return $this->info('Verwerkt in ' . round(microtime(true) - LARAVEL_START, 2) . ' seconden');
}
/**
*
*/
public function importData($url, $program_id, $feed_id, $custom_categorie)
{
$fileLocation = storage_path() . '/' . $program_id . '.' . $feed_id . '.csv';
$this->downloadAndSaveFeed($url, $fileLocation);
$this->filterBestand($fileLocation);
$chunkSize = Config::get('daisycon.chunksize', 500);
Excel::filter('chunk')->load($fileLocation)->chunk($chunkSize, function ($results) use($program_id, $feed_id, $custom_categorie) {
foreach ($results as $row) {
/**
* Lege values eruit filteren
*/
$arr = array_filter($row->toArray());
try {
/**
* Merge 'program_id' in gegevens uit XML
*/
$inserted_array = array_merge($arr, array('program_id' => $program_id, 'feed_id' => $feed_id, 'custom_categorie' => $custom_categorie));
Data::create($inserted_array);
} catch (Exception $e) {
dd($e->getMessage());
}
}
});
Data::where(function ($query) {
$query->whereTitle('title')->orWhere('title', 'like', '#%');
})->delete();
Data::whereTemp(0)->update(array('temp' => 1));
\File::delete($fileLocation);
}
/**
* Execute the console command.
*
* @return mixed
*/
public function fire()
{
$this->info('Alle records aanvullen met gegevens welke ontbreken vanuit CSV-bestand');
$aantal = true;
while ($aantal != 0) {
$rij = Data::select('id')->where('temp', '!=', 2)->take(300)->get();
$aantal = $rij->count();
if ($aantal > 0) {
foreach ($rij as $ro) {
$r = Data::find($ro->id);
$r->temp = 2;
$r->save();
}
}
$this->info($aantal);
}
$this->info('\'region_of_destination\' fixen...');
$regionLeeg = Data::select('city_of_destination', 'region_of_destination')->where('region_of_destination', '')->groupBy('city_of_destination')->get();
if ($regionLeeg->count() > 0) {
$city = Data::select('city_of_destination', 'region_of_destination')->where('region_of_destination', '!=', '')->whereIn('city_of_destination', array_fetch($regionLeeg->toArray(), 'city_of_destination'))->groupBy('city_of_destination')->get();
foreach ($city->toArray() as $row) {
Data::where('city_of_destination', $row['city_of_destination'])->where('region_of_destination', '')->update(array('region_of_destination' => $row['region_of_destination'], 'slug_region_of_destination' => Str::slug($row['region_of_destination'])));
}
}
/**
* Zijn er na bovenstaande actie nog steeds regels over zonder 'region_of_destination' dan verwijderen
*/
$rowsStillNotOK = $regionLeeg2 = Data::where('region_of_destination', '')->delete();
if ($rowsStillNotOK > 0) {
$this->info('\'region_of_destination\' verwijderd: ' . $rowsStillNotOK . ' accommodaties');
}
$this->info('\'region_of_destination\' fixen... DONE');
/**
* Empty slug fixing
*/
/*$this->info('\'slug_region_of_destination\' fixen...');
$slugToFill = Data::select('id', 'region_of_destination', 'slug_region_of_destination')
->where('region_of_destination', '!=', '')
->where('slug_region_of_destination', '')
->get();
if ($slugToFill->count() > 0)
{
foreach ($slugToFill->toArray() as $row)
{
Data::where('id', $row['id'])->update(
array(
'slug_region_of_destination' => \Str::slug($row['region_of_destination'])
)
);
}
}
$this->info('\'slug_region_of_destination\' fixen... DONE');*/
$this->call('cache:clear');
return $this->info('Done');
}
/**
* @param $url
* @param $program_id
* @param $feed_id
* @param $custom_categorie
*
* @throws \Exception
*/
public function importData($url, $program_id, $feed_id, $custom_categorie)
{
$fileLocation = storage_path() . '/' . $program_id . '.' . $feed_id . '.csv';
$this->downloadAndSaveFeed($url, $fileLocation);
$this->filterBestand($fileLocation);
$fields_wanted_from_config = DaisyconHelper::getDatabaseFieldsToImport();
$offset = 1;
// initieel op 1 om header te ontlopen
$batchAantal = 1000;
$csv = Reader::createFromPath($fileLocation);
$csv->setDelimiter(';');
$csv->setEnclosure('"');
$creationCount = 0;
while (true) {
// Flushing the QueryLog anders kan de import te veel geheugen gaan gebruiken
\DB::connection()->flushQueryLog();
$csv->setOffset($offset)->setLimit($batchAantal);
$this->console->writeln("Memory now at: " . memory_get_peak_usage());
$csvResults = $csv->fetchAll(function ($row) use($fields_wanted_from_config, $program_id, $feed_id, $custom_categorie, &$creationCount) {
if (count($row) != count($fields_wanted_from_config)) {
return;
}
try {
$inserted_array = array_merge(array_combine($fields_wanted_from_config, $row), array('program_id' => $program_id, 'feed_id' => $feed_id, 'custom_categorie' => $custom_categorie));
Data::create($inserted_array);
$creationCount++;
} catch (Exception $e) {
echo $e->getMessage() . PHP_EOL;
} catch (\ErrorException $e) {
echo $e->getMessage() . PHP_EOL;
}
});
$aantalResultaten = count($csvResults);
$this->console->writeln("Totaal verwerkt: " . $creationCount);
$offset += $aantalResultaten;
if ($aantalResultaten != $batchAantal) {
break;
}
// forceer einde
}
Data::where(function ($query) {
$query->whereTitle('title')->orWhere('title', 'like', '#%');
})->delete();
Data::whereTemp(null)->update(array('temp' => 1));
\File::delete($fileLocation);
}
public function importData($url, $program_id, $feed_id, $custom_categorie)
{
$fileLocation = storage_path() . '/' . $program_id . '.' . $feed_id . '.csv';
$response = $this->downloadAndSaveFeed($url, $fileLocation);
if ($response) {
// $this->filterBestand($fileLocation);
$pdo = DB::connection()->getPdo();
$sql = "LOAD DATA INFILE '" . addslashes($fileLocation) . "'\r\n INTO TABLE `data`\r\n CHARACTER SET utf8 FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\\n'\r\n (`title`, `link`, `description`, `accommodation_name`, `accommodation_type`, `min_nr_people`, `location_description`, `stars`, `minimum_price`, `maximum_price`, `lowest_price`, `continent_of_destination`, `country_of_destination`, `country_link`, `region_of_destination`, `region_link`, `city_of_destination`, `city_link`, `longitude`, `latitude`, `continent_of_origin`, `country_of_origin`, `city_of_origin`, `port_of_departure`, `img_small`, `img_medium`, `img_large`, `board_type`, `tour_operator`, `transportation_type`, `departure-date`, `departure_date`, `end_date`, `duration`, `daisycon_unique_id`, `internal_id`, `unique_integer`, `update_hash`)\r\n SET\r\n `created_at` = NOW(),\r\n `updated_at` = NOW(),\r\n `program_id` = " . $program_id . ",\r\n `feed_id` = " . $feed_id . ",\r\n `custom_categorie` = '" . $custom_categorie . "'\r\n ";
// DB::connection()->getPdo()->exec($sql);
$pdo->exec($sql);
Data::where(function ($query) {
$query->whereTitle('title')->orWhere('title', 'like', '#%');
})->delete();
// DB::table('data')->update(array('temp' => 1));
Data::whereTemp(0)->update(array('temp' => 1));
\File::delete($fileLocation);
}
}
/**
* Importeer data van betreffende feed (url) in de database
*
* @param $url
* @param $program_id
* @param $feed_id
* @param $custom_categorie
*/
public function importData($url, $program_id, $feed_id, $custom_categorie)
{
$client = new Client();
$response = $client->request('GET', $url, ['timeout' => 3]);
$body = $response->getBody();
$simpleXmlString = simplexml_load_string($body, null, LIBXML_NOCDATA);
// LIBXML_NOCDATA-trick from: http://dissectionbydavid.wordpress.com/2013/01/25/simple-simplexml-to-array-in-php/
foreach ($simpleXmlString as $simpleXmlNode) {
// Lege values eruit filteren
$arr = array_filter((array) $simpleXmlNode);
try {
// Merge 'program_id' in gegevens uit XML
$inserted_array = array_merge($arr, array('program_id' => $program_id, 'feed_id' => $feed_id, 'custom_categorie' => $custom_categorie));
Data::create($inserted_array);
} catch (Exception $e) {
dd($e->getMessage());
}
}
return;
}