/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
$loader = LoaderFactory::make('csv');
$loader->setFile('test/names.csv');
$loader->load();
$data = $loader->getData();
$normalized = [];
foreach ($data as $key => $row) {
$normalized[$row['country']]['country'] = $row['country'];
$normalized[$row['country']]['cities'][$row['city']]['city'] = $row['city'];
$normalized[$row['country']]['cities'][$row['city']]['people'][$key] = array_only($row, ['first', 'last']);
}
$countries = [];
$cities = [];
$people = [];
$idCountry = 1;
$idCity = 1;
$idPerson = 1;
foreach ($normalized as $keyCountry => $valueCountry) {
$countries[] = ['id' => $keyCountry + 1, 'country' => $valueCountry['country']];
foreach ($valueCountry['cities'] as $keyCity => $valueCity) {
$cities[] = ['id' => $idCity, 'city' => $valueCity['city'], 'country' => $idCountry];
foreach ($valueCity['people'] as $keyPerson => $valuePerson) {
$people[] = ['id' => $idPerson, 'first' => $valuePerson['first'], 'last' => $valuePerson['last'], 'city' => $idCity];
$idPerson++;
}
$idCity++;
}
$idCountry++;
}
$writer = new CsvWriter(',');
$writer->setStream(fopen(storage_path('test/norm_countries.csv'), 'w'));
$writer->writeItem(array('id', 'country'));
foreach ($countries as $key => $value) {
$writer->writeItem($value);
}
$writer->finish();
$writer = new CsvWriter(',');
$writer->setStream(fopen(storage_path('test/norm_cities.csv'), 'w'));
$writer->writeItem(array('id', 'city', 'country'));
foreach ($cities as $key => $value) {
$writer->writeItem($value);
}
$writer->finish();
$writer = new CsvWriter(',');
$writer->setStream(fopen(storage_path('test/norm_people.csv'), 'w'));
$writer->writeItem(array('id', 'first', 'last', 'city'));
foreach ($people as $key => $value) {
$writer->writeItem($value);
}
$writer->finish();
dd($normalized);
}
/**
* Export tabular data to CSV-file
* @param array $data
* @param string $filename
*/
public static function arrayToCsv($data, $filename = 'export')
{
if (empty($data)) {
return false;
}
$filePath = api_get_path(SYS_ARCHIVE_PATH) . uniqid('') . '.csv';
$writer = new CsvWriter();
$writer->setStream(fopen($filePath, 'w'));
foreach ($data as $item) {
$writer->writeItem($item);
}
$writer->finish();
DocumentManager::file_send_for_download($filePath, true, $filename . '.csv');
exit;
}
/**
* creates a csv
* @param array $data simple array of csv data
* @param string $outputFileLocation file location
* @param string $delimiter
* @param array $header header fields
* @return bool false if unable to write to folder
*/
function createCsv($data, $outputFileLocation, $delimiter = ',', $header = [])
{
if (!is_writable(dirname($outputFileLocation))) {
echo 'Unable to create file ' . $outputFileLocation . ' . Please check write permissions for your web server (apache/nginx/..)' . $GLOBALS["newline"];
return false;
} else {
$writer = new CsvWriter($delimiter);
$writer->setStream(fopen($outputFileLocation, 'w'));
foreach ($data as $col) {
if (empty($header)) {
$header = array_keys($col);
$writer->writeItem($header);
}
$writer->writeItem($col);
}
$writer->finish();
$filePermissions = $GLOBALS['filePermissions'];
chmod($outputFileLocation, $filePermissions);
}
}
public function write()
{
$adapter = new Adapter("/");
$filesystem = new Filesystem($adapter);
if ( ! $filesystem->has($this->path)) {
$filesystem->createDir($this->path);
}
$filename = $this->path . $this->getFileName();
$writer = new CsvWriter( "," );
$writer->setStream( fopen( $filename, 'w' ) );
$header = $this->getPrologHeader();
if ($this->includeProlog) {
$header[ 1 ][ 0 ] = 'uri';
ksort($header[1]);
$header[ 2 ][ 0 ] = 'lang';
$header[ 2 ][ 1 ] = $this->getSchema()->getLanguage(); //default language
ksort($header[2]);
$header[ 3 ][ 0 ] = 'type';
$header[ 3 ][ 1 ] = 'uri'; //default type
ksort($header[3]);
}
foreach ( $header as $line )
{
$writer->writeItem( $line );
}
if ($this->includeProlog) {
$metadata = $this->getMetadata();
foreach ($metadata as $line) {
$writer->writeItem($line);
}
$prefixRows = $this->getPrefixRows();
foreach ($prefixRows as $line) {
$writer->writeItem($line);
}
}
//get the data
if ( $this->populate )
{
$prefixes = $this->getPrefixes();
$prefixPattern = array();
$prefixReplacement = array();
foreach ( $prefixes as $prefix => $namespace )
{
if (trim($prefix)) {
if ( ! is_int($prefix)) {
$prefixPattern[] = "|" . $namespace . "|";
$prefixReplacement[] = $prefix . ":";
}
}
}
$map = $this->getHeaderMap();
$c = new \Criteria();
$c->clearSelectColumns();
if ('schema' === $this->type) {
$c->addSelectColumn(\SchemaPropertyPeer::ID);
$c->add(\SchemaPropertyPeer::SCHEMA_ID, $this->schema->getId());
if ($this->excludeDeprecated) {
$c->add(\SchemaPropertyPeer::STATUS_ID, 8, \Criteria::NOT_EQUAL);
}
$c->addAscendingOrderByColumn(\SchemaPropertyPeer::URI);
$properties = \SchemaPropertyPeer::doSelectRS($c);
} else {
$c->addSelectColumn(\ConceptPeer::ID);
$c->addSelectColumn(\ConceptPeer::URI);
$c->addSelectColumn(\ConceptPeer::STATUS_ID);
$c->add(\ConceptPeer::VOCABULARY_ID, $this->schema->getId());
if ($this->excludeDeprecated) {
$c->add(\ConceptPeer::STATUS_ID, 8, \Criteria::NOT_EQUAL);
}
$c->addAscendingOrderByColumn(\ConceptPeer::URI);
$properties = \ConceptPeer::doSelectRS($c);
}
foreach ( $properties as $property )
{
$line = array_fill( 0, $this->getHeaderCount(), '' );
$line[0] = $property[0];
$map = $this->getHeaderMap();
$ce = new \Criteria();
if ('schema' === $this->type) {
$ce->add(\BaseSchemaPropertyElementPeer::SCHEMA_PROPERTY_ID, $property[0]);
if (!$this->includeDeleted) {
$ce->add(\BaseSchemaPropertyElementPeer::DELETED_AT, null);
}
if ($this->includeDeleted) {
$ce->addAscendingOrderByColumn(\SchemaPropertyElementPeer::UPDATED_AT);
}
$elements = \SchemaPropertyElementPeer::doSelectJoinProfileProperty($ce);
} else {
$ce->add(\ConceptPropertyPeer::CONCEPT_ID, $property[0]);
if (!$this->includeDeleted) {
$ce->add(\ConceptPropertyPeer::DELETED_AT, null);
}
if ($this->includeDeleted) {
$ce->addAscendingOrderByColumn(\ConceptPropertyPeer::UPDATED_AT);
}
$elements = \ConceptPropertyPeer::doSelectJoinProfilePropertyRelatedBySkosPropertyId($ce);
$line[array_search('uri', $header[0])] = $property[1];
$line[array_search('status', $header[0])] = $property[2];
}
/** @var \SchemaPropertyElement $element */
foreach ($elements as $element )
{
if ($this->excludeGenerated and $element->getIsGenerated()) {
continue;
}
/** @var \ProfileProperty $profileProperty */
$profileProperty = $element->getProfileProperty();
$propertyId = $profileProperty->getId();
if ('schema' === $this->type and in_array( $propertyId, [ 6, 9, ] ) and $element->getIsSchemaProperty() )
{
$language = 'parent';
}
else
{
$language = $profileProperty->getHasLanguage() ? $element->getLanguage() : '';
}
$index = $propertyId . $language;
if (isset($map[ $index ])) {
foreach ($map[ $index ] as &$column) {
if (false !== $column) {
$line[ $column ] = $element->getObject();
$column = false;
break;
}
}
}
}
$writer->writeItem( preg_replace( $prefixPattern, $prefixReplacement, $line ));
unset($line, $elements);
}
}
//add an empty line at the end
$line = array_fill( 0, $this->getHeaderCount(), '' );
$writer->writeItem( $line );
$writer->finish();
}