Gentics Portal.Node PHP API
 All Classes Namespaces Functions Variables Pages
ECSVExport.php
1 <?php
2 
3 /**
4  * export a csv file (string) from given CSqlDataProvider
5  * usage in your controller:
6  * Yii::import('ext.CSVExport');
7  * $provider = YourClass::model()->createCSqlProvider()
8  * or
9  * $provider = Yii::app()->db->creatCommand(...)->queryAll();
10  * $csv = new ECSVExport($provider);
11  * $content = $csv->toCSV();
12  * Yii::app()->getRequest()->sendFile($filename, $content, "text/csv", false);
13  * exit();
14  *
15  * You can also do this now:
16  * $cmd = Yii::app()->db->createCommand("SELECT * FROM track_test LIMIT 10");
17  * $csv = new ECSVExport($cmd);
18  * $csv->setOutputFile($outputFile);
19  * $csv->toCSV();
20  *
21  * @author Kenrick Buchanan
22  * @version 0.6.8
23  */
24 
26 {
27  /**
28  * show column headers in csv file
29  * @var boolean $includeColumnHeaders
30  */
31  public $includeColumnHeaders = true;
32 
33  /**
34  * sometimes excel chokes on newlines in string, this will fix that
35  * @var boolean $replaceNewLines strip newlines from each column
36  */
37  public $stripNewLines = true;
38 
39  /**
40  * run through whole resultset, appending to output stream, using paging (if on)
41  * @var boolean $exportFull
42  */
43  public $exportFull = true;
44 
45  /**
46  * convert activedata provider to a cdbcommand for speed
47  * @var boolean $convertActiveDataProvider
48  */
50 
51  /**
52  *
53  * string filename
54  * @var string $_outputFile
55  */
56  protected $_outputFile;
57 
58  /**
59  * stream pointer
60  * @var resource $_filePointer
61  */
62  protected $_filePointer;
63 
64  /**
65  * data provider that will generate or contain resultset
66  * @var mixed CSqlDataProvider|Array|CActiveDataProvider|CDbCommand $_dataProvider
67  */
68  protected $_dataProvider;
69 
70  /**
71  * per row callable function
72  * @var callable $_callback
73  */
74  protected $_callback;
75 
76  /**
77  * csv headers
78  * @var array $_headers
79  */
80  protected $_headers = array();
81 
82  /**
83  *
84  * columns to exclude from final file
85  * @var array $_exclude
86  */
87  protected $_exclude = array();
88 
89  /**
90  * column delimiter
91  * @var string $_delimiter
92  */
93  protected $_delimiter = ",";
94 
95  /**
96  * string to enclose fields when delimiter is found in field
97  * @var string $_enclosure
98  */
99  protected $_enclosure = '"';
100 
101  /**
102  * determine if overwriting output file or just append to existing
103  * $this->setToAppend()
104  * @var boolean
105  */
106  protected $_appendCsv = false;
107 
108  /**
109  * extra model relations to include in output, cause normally it only
110  * gets the attributes for the current model. ooops
111  * array('buyer'=>'name'... or 'buyer'=>array('rel1','rel2'))
112  * @var array
113  */
114  protected $_modelRelations = array();
115 
116  /**
117  *
118  * @param mixed $dataProvider array|CSqlDataProvider|CActiveDataProvider|CDbCommand
119  * @param boolean $exportFull
120  * @param boolean $includeColumnHeaders
121  * @param string $delimiter
122  * @param string $enclosure
123  */
124  public function __construct($dataProvider, $exportFull=true, $includeColumnHeaders=true, $delimiter=null, $enclosure=null)
125  {
126  $this->_dataProvider = $dataProvider;
127  $this->exportFull = (bool) $exportFull;
128  $this->includeColumnHeaders = (bool) $includeColumnHeaders;
129  if($delimiter) $this->_delimiter = $delimiter;
130  if($enclosure) $this->_enclosure = $enclosure;
131  }
132 
133  /**
134  * get data provider
135  * @return mixed $this->_dataProvider
136  */
137  public function getDataProvider()
138  {
139  return $this->_dataProvider;
140  }
141 
142  /**
143  * call this function to not force CActiveDataProvider to be converted to command
144  * for speed and memory at the expense of losing the with() functionality
145  *
146  * @return \ECSVExport
147  */
148  public function dontConvertProvider()
149  {
150  $this->convertActiveDataProvider = false;
151  return $this;
152  }
153 
154  /**
155  * sets flag to have it append to file instead of just overwriting it
156  * @return void
157  */
158  public function setToAppend()
159  {
160  $this->_appendCsv = true;
161  return $this;
162  }
163 
164  /**
165  *
166  * set csv delimiter, defaults to ,
167  * @param type $delimiter
168  * @return \ECSVExport
169  */
170  public function setDelimiter($delimiter)
171  {
172  $this->_delimiter = $delimiter;
173  return $this;
174  }
175 
176  /**
177  * get current delimiter
178  * @return type
179  */
180  public function getDelimiter()
181  {
182  return $this->_delimiter;
183  }
184 
185  /**
186  *
187  * set csv enclosure, defaults to "
188  * @param type $enclosure
189  * @return \ECSVExport
190  */
191  public function setEnclosure($enclosure)
192  {
193  $this->_enclosure = $enclosure;
194  return $this;
195  }
196 
197  /**
198  *
199  * @return string
200  */
201  public function getEnclosure()
202  {
203  return $this->_enclosure;
204  }
205 
206  /**
207  *
208  * set filename of csv file you want to create
209  * @param type $filename
210  * @return \ECSVExport
211  */
212  public function setOutputFile($filename)
213  {
214  $this->_outputFile = $filename;
215  return $this;
216  }
217 
218  /**
219  * get output file
220  * @return string
221  */
222  public function getOutputFile()
223  {
224  return $this->_outputFile;
225  }
226 
227  /**
228  * function to be called for each row in set
229  * @param mixed callable|array $callback
230  * @return \ECSVExport
231  * @throws Exception on uncallable variable
232  */
233  public function setCallback($callback)
234  {
235  if(is_callable($callback)) {
236  $this->_callback = $callback;
237  return $this;
238  } else {
239  throw new Exception('Callback must be callable. Duh.');
240  }
241  }
242 
243  /**
244  * get per row function
245  * @return mixed $this->_callback
246  */
247  public function getCallback()
248  {
249  return $this->_callback;
250  }
251 
252  /**
253  * existing column names remapped to other strings resultcolumn=>new name
254  * @param array $headers
255  * @return \ECSVExport
256  */
257  public function setHeaders(array $headers)
258  {
259  $this->_headers = $headers;
260  return $this;
261  }
262 
263  /**
264  * get current csv headers
265  * @return array $this->_headers
266  */
267  public function getHeaders()
268  {
269  return $this->_headers;
270  }
271 
272  /**
273  *
274  * @param string $key
275  * @param string $value
276  * @return \ECSVExport
277  */
278  public function setHeader($key, $value)
279  {
280  $this->_headers[$key] = $value;
281  return $this;
282  }
283 
284  /**
285  *
286  * @param type $noshow
287  * @return \ECSVExport
288  */
289  public function setExclude($noshow)
290  {
291  if(is_array($noshow)) {
292  $this->_exclude = $noshow;
293  return $this;
294  } else {
295  $this->_exclude[] = (string) $noshow;
296  }
297  }
298 
299  /**
300  * get excluded fields
301  * @return array $this->_exclude
302  */
303  public function getExclude()
304  {
305  return $this->_exclude;
306  }
307 
308  /**
309  * get the set model relations
310  * return array $this->_modelRelations
311  */
312  public function getModelRelations()
313  {
314  return $this->_modelRelations;
315  }
316 
317  /**
318  * set relations to include on output that will be interpolated via model crap
319  * needs to be array of arrays
320  * @param array $relations
321  */
322  public function setModelRelations(array $relations)
323  {
324  $this->_modelRelations = $relations;
325  }
326 
327  /**
328  * turn off going through whole resultset, taking current page into account
329  * @return \ECSVExport
330  */
331  public function exportCurrentPageOnly()
332  {
333  $this->exportFull = false;
334  return $this;
335  }
336 
337  /**
338  * create a csv string, or file if $outputFile is set
339  *
340  * @param string $outputFile
341  * @param string $delimiter
342  * @param string $enclosure
343  * @param boolean $includeHeaders
344  * @return mixed string|boolean|integer csv string when no outputFile is specified
345  * boolean if the writing failed, or integer of num bytes written to file
346  */
347  public function toCSV($outputFile=null, $delimiter=null, $enclosure=null, $includeHeaders=true)
348  {
349  // check that data provider is something useful
350  $isGood = false;
351 
352  if($this->_dataProvider instanceof CActiveDataProvider) {
353  $isGood = true;
354  }
355 
356  if($this->_dataProvider instanceof CSqlDataProvider) {
357  $isGood = true;
358  }
359 
360  if($this->_dataProvider instanceof CDbCommand) {
361  $isGood = true;
362  }
363 
364  if(is_array($this->_dataProvider)) {
365  $isGood = true;
366  }
367 
368  if(!$isGood) {
369  throw new Exception('Bad data provider given as source to '.__CLASS__);
370  }
371 
372  if($outputFile !== null) {
373  $this->setOutputFile($outputFile);
374  }
375 
376  if(!$includeHeaders) {
377  $this->includeColumnHeaders = false;
378  }
379 
380  if($delimiter !== null) {
381  $this->_delimiter = $delimiter;
382  }
383 
384  if($enclosure !== null) {
385  $this->_enclosure = $enclosure;
386  }
387 
388  // create file pointer
389  $this->_filePointer = fopen("php://temp", 'w');
390  $this->_writeData();
391  rewind($this->_filePointer);
392 
393  // make sure you can write to file!
394  if($this->_outputFile !== null) {
395  // write stream to file
396  return $this->_appendCsv ? file_put_contents($this->_outputFile, $this->_filePointer, FILE_APPEND | LOCK_EX)
397  : file_put_contents($this->_outputFile, $this->_filePointer, LOCK_EX);
398 
399  } else {
400  return stream_get_contents($this->_filePointer);
401  }
402  }
403 
404  /**
405  * where the magic happens. depending on type of dataProvider, it uses
406  * different methods to get the data efficiently and to write to file
407  * pointer in memory. the most effecient is either a small array or
408  * by just passing in a CDbCommand instance
409  * @throws Exception - no data found
410  */
411  protected function _writeData()
412  {
413  $firstTimeThrough = true;
414  if($this->_dataProvider instanceof CActiveDataProvider) {
415  if($this->exportFull) {
416  // set pagination to off
417  $this->_dataProvider->setPagination(false);
418  }
419  if($this->convertActiveDataProvider) {
420  $criteria = $this->_dataProvider->getCriteria();
421  $model = $this->_dataProvider->model;
422  $criteria = $model->getCommandBuilder()
423  ->createCriteria($criteria,array());
424  $this->_dataProvider = $model->getCommandBuilder()
425  ->createFindCommand($model->getTableSchema(),
426  $criteria);
427  unset($model, $criteria);
428  } else {
429  // suggested implementation from marcovtwout
430  $models = $this->_dataProvider->getData();
431  $dataReader = array();
432  $attributes = $this->_dataProvider->model->getMetaData()->columns;
433 
434 
435  // since we are already looping through results, don't bother
436  // passing results to _loopRow, just write it here.
437  foreach ($models as &$model) {
438  $row = array();
439 
440  foreach ($attributes as $attribute => $col) {
441  $row[$attribute] = $model->{$attribute};
442  }
443 
444  // check model relations
445  if(count($this->_modelRelations)) {
446  foreach($this->_modelRelations as $relation=>$value) {
447  if(is_array($value)) {
448  foreach($value as $subvalue) {
449  if(isset($model->$relation->$subvalue) && $model->$relation->$subvalue)
450  $row[$relation.'['.$subvalue.']'] = $model->$relation->$subvalue;
451  }
452  } else {
453  if(isset($model->$relation->$value) && $model->$relation->$value)
454  $row[$relation.'['.$value.']'] = $model->$relation->$value;
455  }
456  }
457  }
458 
459  if($firstTimeThrough) {
460  $this->_writeHeaders($row);
461  $firstTimeThrough = false;
462  }
463  $this->_writeRow($row);
464  }
465  unset($models, $attributes);
466  return;
467  }
468  }
469 
470  if($this->_dataProvider instanceof CSqlDataProvider) {
471  if($this->exportFull) {
472  $this->_dataProvider->setId('csvexport');
473  $this->_dataProvider->getPagination()->setItemCount($this->_dataProvider->getTotalItemCount());
474  $pageVar = $this->_dataProvider->getPagination()->pageVar;
475  $_GET[$pageVar] = 0;
476  $totalPages = $this->_dataProvider->getPagination()->getPageCount();
477  $this->setToAppend();
478  for($i=1; $i<=$totalPages; $i++) {
479  $_GET[$pageVar] = $i;
480  $this->_dataProvider->getPagination()->setCurrentPage($i);
481  $_getData = $this->_dataProvider->getData();
482  $this->_loopRows($_getData);
483  $this->includeColumnHeaders = !(bool) $i;
484  }
485  } else {
486  $this->_loopRows($this->_dataProvider->getData());
487  }
488 
489  return;
490  }
491 
492  if($this->_dataProvider instanceof CDbCommand) {
493  $dataReader = $this->_dataProvider->query();
494  $this->_loopRows($dataReader);
495  return;
496  }
497 
498  if(is_array($this->_dataProvider)) {
499  $this->_loopRows($this->_dataProvider);
500  return;
501  }
502 
503  // if program made it this far something happened
504  throw new Exception('Data source failed to retrieve data, are you sure you passed something useable?');
505  }
506 
507  /**
508  * loop through result set
509  * @param mixed CDbDataReader|array $dp
510  */
511  public function _loopRows(&$dp)
512  {
513  $firstTimeThrough = true;
514  if($dp instanceof CDbDataReader) {
515  while(($row = $dp->read()) !== false) {
516  if($firstTimeThrough) {
517  $this->_writeHeaders($row);
518  $firstTimeThrough = false;
519  }
520  $this->_writeRow($row);
521  }
522  } else {
523  $total = count($dp);
524  for($i=0; $i<$total; $i++) {
525  if($firstTimeThrough) {
526  $this->_writeHeaders($dp[$i]);
527  $firstTimeThrough = false;
528  }
529  $this->_writeRow($dp[$i]);
530  }
531  }
532  }
533 
534  /**
535  * Write headers to csv file, taking into account string replacements and exclusions
536  * @param array $row
537  * @return void
538  */
539  protected function _writeHeaders($row)
540  {
541  if(!$this->includeColumnHeaders) {
542  return;
543  }
544 
545  if($row instanceof CActiveRecord) {
546  $headers = array_keys($row->getAttributes());
547  } else {
548  $headers = array_keys($row);
549  }
550 
551  // remove excluded
552  if(count($this->_exclude) > 0) {
553  foreach($this->_exclude as $e) {
554  $key = array_search($e, $headers);
555  if($key !== false) {
556  unset($headers[$key]);
557  }
558  }
559  }
560 
561  if(count($this->_headers) > 0) {
562  foreach($headers as &$header) {
563  if(array_key_exists($header, $this->_headers)) {
564  $header = $this->_headers[$header];
565  }
566  }
567  }
568 
569  fputcsv($this->_filePointer, $headers, $this->_delimiter, $this->_enclosure);
570  }
571 
572  /**
573  * Write array row to current {$this->_filePointer}, taking into account exclusions
574  * @param array $row
575  * @return void
576  */
577  public function _writeRow($row)
578  {
579  if($row instanceof CActiveRecord) {
580  $row = $row->getAttributes();
581  }
582  // remove excluded
583  if(count($this->_exclude) > 0) {
584  foreach($this->_exclude as $e) {
585  if(array_key_exists($e, $row)) {
586  unset($row[$e]);
587  }
588  }
589  }
590 
591  if($this->stripNewLines) {
592  array_walk($row, array('ECSVExport','lambdaFail'));
593  }
594 
595  array_walk($row, array('ECSVExport','stripSlashes'));
596 
597  if(isset($this->_callback) && $this->_callback) {
598  fputcsv($this->_filePointer, call_user_func($this->_callback, $row), $this->_delimiter, $this->_enclosure);
599  } else {
600  fputcsv($this->_filePointer, $row, $this->_delimiter, $this->_enclosure);
601  }
602  unset($row);
603  }
604 
605  public static function lambdaFail(&$value, $key)
606  {
607  $value = str_replace("\r\n"," ", $value);
608  }
609 
610  public static function stripSlashes(&$value, $key)
611  {
612  $value = stripslashes($value);
613  $value = str_replace('\"', '"', $value);
614  }
615 }