1<?php
2require_once('xlsxwriter.class.php');
3
4class ExcelExporter
5{
6	protected $sToken;
7	protected $aStatistics;
8	protected $sState;
9	protected $fStartTime;
10	protected $oSearch;
11	protected $aObjectsIDs;
12	protected $aTableHeaders;
13	protected $aAuthorizedClasses;
14	protected $iChunkSize = 1000;
15	protected $iPosition;
16	protected $sOutputFilePath;
17	protected $bAdvancedMode;
18
19	public function __construct($sToken = null)
20	{
21		$this->aStatistics = array(
22			'objects_count' => 0,
23			'total_duration' => 0,
24			'data_retrieval_duration' => 0,
25			'excel_build_duration' => 0,
26			'excel_write_duration' => 0,
27			'peak_memory_usage' => 0,
28		);
29		$this->fStartTime = microtime(true);
30		$this->oSearch = null;
31
32		$this->sState = 'new';
33		$this->aObjectsIDs = array();
34		$this->iPosition = 0;
35		$this->aAuthorizedClasses = null;
36		$this->aTableHeaders = null;
37		$this->sOutputFilePath = null;
38		$this->bAdvancedMode = false;
39		$this->CheckDataDir();
40		if ($sToken == null)
41		{
42			$this->sToken = $this->GetNewToken();
43		}
44		else
45		{
46			$this->sToken = $sToken;
47			$this->ReloadState();
48		}
49	}
50
51	public function __destruct()
52	{
53		if (($this->sState != 'done') && ($this->sState != 'error') && ($this->sToken != null))
54		{
55			// Operation in progress, save the state
56			$this->SaveState();
57		}
58		else
59		{
60			// Operation completed, cleanup the temp files
61			@unlink($this->GetStateFile());
62			@unlink($this->GetDataFile());
63		}
64		self::CleanupOldFiles();
65	}
66
67	public function SetChunkSize($iChunkSize)
68	{
69		$this->iChunkSize = $iChunkSize;
70	}
71
72	public function SetOutputFilePath($sDestFilePath)
73	{
74		$this->sOutputFilePath = $sDestFilePath;
75	}
76
77	public function SetAdvancedMode($bAdvanced)
78	{
79		$this->bAdvancedMode = $bAdvanced;
80	}
81
82	public function SaveState()
83	{
84		$aState = array(
85			'state' => $this->sState,
86			'statistics' => $this->aStatistics,
87			'filter' => $this->oSearch->serialize(),
88			'position' => $this->iPosition,
89			'chunk_size' => $this->iChunkSize,
90			'object_ids' => $this->aObjectsIDs,
91			'output_file_path' => $this->sOutputFilePath,
92			'advanced_mode' => $this->bAdvancedMode,
93		);
94
95		file_put_contents($this->GetStateFile(), json_encode($aState));
96
97		return $this->sToken;
98	}
99
100	public function ReloadState()
101	{
102		if ($this->sToken == null)
103		{
104			throw new Exception('ExcelExporter not initialized with a token, cannot reload state');
105		}
106
107		if (!file_exists($this->GetStateFile()))
108		{
109			throw new Exception("ExcelExporter: missing status file '".$this->GetStateFile()."', cannot reload state.");
110		}
111		$sJson = file_get_contents($this->GetStateFile());
112		$aState = json_decode($sJson, true);
113		if ($aState === null)
114		{
115			throw new Exception("ExcelExporter:corrupted status file '".$this->GetStateFile()."', not a JSON, cannot reload state.");
116		}
117
118		$this->sState = $aState['state'];
119		$this->aStatistics = $aState['statistics'];
120		$this->oSearch = DBObjectSearch::unserialize($aState['filter']);
121		$this->iPosition = $aState['position'];
122		$this->iChunkSize = $aState['chunk_size'];
123		$this->aObjectsIDs = $aState['object_ids'];
124		$this->sOutputFilePath  = $aState['output_file_path'];
125		$this->bAdvancedMode = $aState['advanced_mode'];
126	}
127
128	public function SetObjectList($oSearch)
129	{
130		$this->oSearch = $oSearch;
131	}
132
133	public function Run()
134	{
135		$sCode = 'error';
136		$iPercentage = 100;
137		$sMessage = Dict::Format('ExcelExporter:ErrorUnexpected_State', $this->sState);
138		$fTime = microtime(true);
139
140		try
141		{
142			switch($this->sState)
143			{
144				case 'new':
145				$oIDSet = new DBObjectSet($this->oSearch);
146				$oIDSet->OptimizeColumnLoad(array('id'));
147				$this->aObjectsIDs = array();
148				while($oObj = $oIDSet->Fetch())
149				{
150					$this->aObjectsIDs[] = $oObj->GetKey();
151				}
152				$sCode = 'retrieving-data';
153				$iPercentage = 5;
154				$sMessage = Dict::S('ExcelExporter:RetrievingData');
155				$this->iPosition = 0;
156				$this->aStatistics['objects_count'] = count($this->aObjectsIDs);
157				$this->aStatistics['data_retrieval_duration'] += microtime(true) - $fTime;
158
159				// The first line of the file is the "headers" specifying the label and the type of each column
160				$this->GetFieldsList($oIDSet, $this->bAdvancedMode);
161				$sRow = json_encode($this->aTableHeaders);
162				$hFile = @fopen($this->GetDataFile(), 'ab');
163				if ($hFile === false)
164				{
165					throw new Exception('ExcelExporter: Failed to open temporary data file: "'.$this->GetDataFile().'" for writing.');
166				}
167				fwrite($hFile, $sRow."\n");
168				fclose($hFile);
169
170				// Next state
171				$this->sState = 'retrieving-data';
172				break;
173
174				case 'retrieving-data':
175				$oCurrentSearch = clone $this->oSearch;
176				$aIDs = array_slice($this->aObjectsIDs, $this->iPosition, $this->iChunkSize);
177
178				$oCurrentSearch->AddCondition('id', $aIDs, 'IN');
179				$hFile = @fopen($this->GetDataFile(), 'ab');
180				if ($hFile === false)
181				{
182					throw new Exception('ExcelExporter: Failed to open temporary data file: "'.$this->GetDataFile().'" for writing.');
183				}
184				$oSet = new DBObjectSet($oCurrentSearch);
185				$this->GetFieldsList($oSet, $this->bAdvancedMode);
186				while($aObjects = $oSet->FetchAssoc())
187				{
188					$aRow = array();
189					foreach($this->aAuthorizedClasses as $sAlias => $sClassName)
190					{
191						$oObj = $aObjects[$sAlias];
192						if ($this->bAdvancedMode)
193						{
194							$aRow[] = $oObj->GetKey();
195						}
196						foreach($this->aFieldsList[$sAlias] as $sAttCodeEx => $oAttDef)
197						{
198							$value = $oObj->Get($sAttCodeEx);
199							if ($value instanceOf ormCaseLog)
200							{
201								// Extract the case log as text and remove the "===" which make Excel think that the cell contains a formula the next time you edit it!
202								$sExcelVal = trim(preg_replace('/========== ([^=]+) ============/', '********** $1 ************', $value->GetText()));
203							}
204							else
205							{
206								$sExcelVal =  $oAttDef->GetEditValue($value, $oObj);
207							}
208							$aRow[] = $sExcelVal;
209						}
210					}
211					$sRow = json_encode($aRow);
212					fwrite($hFile, $sRow."\n");
213				}
214				fclose($hFile);
215
216				if (($this->iPosition + $this->iChunkSize) > count($this->aObjectsIDs))
217				{
218					// Next state
219					$this->sState = 'building-excel';
220					$sCode = 'building-excel';
221					$iPercentage = 80;
222					$sMessage = Dict::S('ExcelExporter:BuildingExcelFile');
223				}
224				else
225				{
226					$sCode = 'retrieving-data';
227					$this->iPosition += $this->iChunkSize;
228					$iPercentage = 5 + round(75 * ($this->iPosition / count($this->aObjectsIDs)));
229					$sMessage = Dict::S('ExcelExporter:RetrievingData');
230				}
231				break;
232
233				case 'building-excel':
234				$hFile = @fopen($this->GetDataFile(), 'rb');
235				if ($hFile === false)
236				{
237					throw new Exception('ExcelExporter: Failed to open temporary data file: "'.$this->GetDataFile().'" for reading.');
238				}
239				$sHeaders = fgets($hFile);
240				$aHeaders = json_decode($sHeaders, true);
241
242				$aData = array();
243				while($sLine = fgets($hFile))
244				{
245					$aRow = json_decode($sLine);
246					$aData[] = $aRow;
247				}
248				fclose($hFile);
249				@unlink($this->GetDataFile());
250
251				$fStartExcel = microtime(true);
252				$writer = new XLSXWriter();
253				$writer->setAuthor(UserRights::GetUserFriendlyName());
254				$writer->writeSheet($aData,'Sheet1', $aHeaders);
255				$fExcelTime = microtime(true) - $fStartExcel;
256				$this->aStatistics['excel_build_duration'] = $fExcelTime;
257
258				$fTime = microtime(true);
259				$writer->writeToFile($this->GetExcelFilePath());
260				$fExcelSaveTime = microtime(true) - $fTime;
261				$this->aStatistics['excel_write_duration'] = $fExcelSaveTime;
262
263				// Next state
264				$this->sState = 'done';
265				$sCode = 'done';
266				$iPercentage = 100;
267				$sMessage = Dict::S('ExcelExporter:Done');
268				break;
269
270				case 'done':
271				$this->sState = 'done';
272				$sCode = 'done';
273				$iPercentage = 100;
274				$sMessage = Dict::S('ExcelExporter:Done');
275				break;
276			}
277		}
278		catch(Exception $e)
279		{
280			$sCode = 'error';
281			$sMessage = $e->getMessage();
282		}
283
284		$this->aStatistics['total_duration'] += microtime(true) - $fTime;
285		$peak_memory = memory_get_peak_usage(true);
286		if ($peak_memory > $this->aStatistics['peak_memory_usage'])
287		{
288			$this->aStatistics['peak_memory_usage'] = $peak_memory;
289		}
290
291		return array(
292			'code' => $sCode,
293			'message' => $sMessage,
294			'percentage' => $iPercentage,
295		);
296	}
297
298	public function GetExcelFilePath()
299	{
300		if ($this->sOutputFilePath == null)
301		{
302			return APPROOT.'data/bulk_export/'.$this->sToken.'.xlsx';
303		}
304		else
305		{
306			return $this->sOutputFilePath;
307		}
308	}
309
310	public static function GetExcelFileFromToken($sToken)
311	{
312		return @file_get_contents(APPROOT.'data/bulk_export/'.$sToken.'.xlsx');
313	}
314
315	public static function CleanupFromToken($sToken)
316	{
317		@unlink(APPROOT.'data/bulk_export/'.$sToken.'.status');
318		@unlink(APPROOT.'data/bulk_export/'.$sToken.'.data');
319		@unlink(APPROOT.'data/bulk_export/'.$sToken.'.xlsx');
320	}
321
322	public function Cleanup()
323	{
324		self::CleanupFromToken($this->sToken);
325	}
326
327	/**
328	 * Delete all files in the data/bulk_export directory which are older than 1 day
329	 * unless a different delay is configured.
330	 */
331	public static function CleanupOldFiles()
332	{
333		$aFiles = glob(APPROOT.'data/bulk_export/*.*');
334		$iDelay = MetaModel::GetConfig()->Get('xlsx_exporter_cleanup_old_files_delay');
335
336		if($iDelay > 0)
337		{
338			foreach($aFiles as $sFile)
339			{
340				$iModificationTime = filemtime($sFile);
341
342				if($iModificationTime < (time() - $iDelay))
343				{
344					// Temporary files older than one day are deleted
345					//echo "Supposed to delete: '".$sFile." (Unix Modification Time: $iModificationTime)'\n";
346					@unlink($sFile);
347				}
348			}
349		}
350	}
351
352	public function DisplayStatistics(Page $oPage)
353	{
354		$aStats = array(
355				'Number of objects exported' => $this->aStatistics['objects_count'],
356				'Total export duration' => sprintf('%.3f s', $this->aStatistics['total_duration']),
357				'Data retrieval duration' => sprintf('%.3f s', $this->aStatistics['data_retrieval_duration']),
358				'Excel build duration' => sprintf('%.3f s', $this->aStatistics['excel_build_duration']),
359				'Excel write duration' => sprintf('%.3f s', $this->aStatistics['excel_write_duration']),
360				'Peak memory usage' => self::HumanDisplay($this->aStatistics['peak_memory_usage']),
361		);
362
363		if ($oPage instanceof CLIPage)
364		{
365			$oPage->add($this->GetStatistics('text'));
366		}
367		else
368		{
369			$oPage->add($this->GetStatistics('html'));
370		}
371	}
372
373	public function GetStatistics($sFormat = 'html')
374	{
375		$sStats = '';
376		$aStats = array(
377				'Number of objects exported' => $this->aStatistics['objects_count'],
378				'Total export duration' => sprintf('%.3f s', $this->aStatistics['total_duration']),
379				'Data retrieval duration' => sprintf('%.3f s', $this->aStatistics['data_retrieval_duration']),
380				'Excel build duration' => sprintf('%.3f s', $this->aStatistics['excel_build_duration']),
381				'Excel write duration' => sprintf('%.3f s', $this->aStatistics['excel_write_duration']),
382				'Peak memory usage' => self::HumanDisplay($this->aStatistics['peak_memory_usage']),
383		);
384
385		if ($sFormat == 'text')
386		{
387			foreach($aStats as $sLabel => $sValue)
388			{
389				$sStats .= "+------------------------------+----------+\n";
390				$sStats .= sprintf("|%-30s|%10s|\n", $sLabel, $sValue);
391			}
392			$sStats .= "+------------------------------+----------+";
393		}
394		else
395		{
396			$sStats .= '<table><tbody>';
397			foreach($aStats as $sLabel => $sValue)
398			{
399				$sStats .= "<tr><td>$sLabel</td><td>$sValue</td></tr>";
400			}
401			$sStats .= '</tbody></table>';
402
403		}
404		return $sStats;
405	}
406
407	public static function HumanDisplay($iSize)
408	{
409		$aUnits = array('B','KB','MB','GB','TB','PB');
410		return @round($iSize/pow(1024,($i=floor(log($iSize,1024)))),2).' '.$aUnits[$i];
411	}
412
413	protected function CheckDataDir()
414	{
415		if(!is_dir(APPROOT."data/bulk_export"))
416		{
417			@mkdir(APPROOT."data/bulk_export", 0777, true /* recursive */);
418			clearstatcache();
419		}
420		if (!is_writable(APPROOT."data/bulk_export"))
421		{
422			throw new Exception('Data directory "'.APPROOT.'data/bulk_export" could not be written.');
423		}
424	}
425
426	protected function GetStateFile($sToken = null)
427	{
428		if ($sToken == null)
429		{
430			$sToken = $this->sToken;
431		}
432		return APPROOT."data/bulk_export/$sToken.status";
433	}
434
435	protected function GetDataFile()
436	{
437		return APPROOT.'data/bulk_export/'.$this->sToken.'.data';
438	}
439
440	protected function GetNewToken()
441	{
442		$iNum = rand();
443		do
444		{
445			$iNum++;
446			$sToken = sprintf("%08x", $iNum);
447			$sFileName = $this->GetStateFile($sToken);
448			$hFile = @fopen($sFileName, 'x');
449		}
450		while($hFile === false);
451
452		fclose($hFile);
453		return $sToken;
454	}
455
456	protected function GetFieldsList($oSet, $bFieldsAdvanced = false, $bLocalize = true, $aFields = null)
457	{
458		$this->aFieldsList = array();
459
460		$oAppContext = new ApplicationContext();
461		$aClasses = $oSet->GetFilter()->GetSelectedClasses();
462		$this->aAuthorizedClasses = array();
463		foreach($aClasses as $sAlias => $sClassName)
464		{
465			if (UserRights::IsActionAllowed($sClassName, UR_ACTION_READ, $oSet) != UR_ALLOWED_NO)
466			{
467				$this->aAuthorizedClasses[$sAlias] = $sClassName;
468			}
469		}
470		$aAttribs = array();
471		$this->aTableHeaders = array();
472		foreach($this->aAuthorizedClasses as $sAlias => $sClassName)
473		{
474			$aList[$sAlias] = array();
475
476			foreach(MetaModel::ListAttributeDefs($sClassName) as $sAttCode => $oAttDef)
477			{
478				if (is_null($aFields) || (count($aFields) == 0))
479				{
480					// Standard list of attributes (no link sets)
481					if ($oAttDef->IsScalar() && ($oAttDef->IsWritable() || $oAttDef->IsExternalField()))
482					{
483						$sAttCodeEx = $oAttDef->IsExternalField() ? $oAttDef->GetKeyAttCode().'->'.$oAttDef->GetExtAttCode() : $sAttCode;
484
485						if ($oAttDef->IsExternalKey(EXTKEY_ABSOLUTE))
486						{
487							if ($bFieldsAdvanced)
488							{
489								$aList[$sAlias][$sAttCodeEx] = $oAttDef;
490
491								if ($oAttDef->IsExternalKey(EXTKEY_RELATIVE))
492								{
493							  		$sRemoteClass = $oAttDef->GetTargetClass();
494									foreach(MetaModel::GetReconcKeys($sRemoteClass) as $sRemoteAttCode)
495								  	{
496										$this->aFieldsList[$sAlias][$sAttCode.'->'.$sRemoteAttCode] = MetaModel::GetAttributeDef($sRemoteClass, $sRemoteAttCode);
497								  	}
498								}
499							}
500						}
501						else
502						{
503							// Any other attribute
504							$this->aFieldsList[$sAlias][$sAttCodeEx] = $oAttDef;
505						}
506					}
507				}
508				else
509				{
510					// User defined list of attributes
511					if (in_array($sAttCode, $aFields) || in_array($sAlias.'.'.$sAttCode, $aFields))
512					{
513						$this->aFieldsList[$sAlias][$sAttCode] = $oAttDef;
514					}
515				}
516			}
517			if ($bFieldsAdvanced)
518			{
519				$this->aTableHeaders['id'] = '0';
520			}
521			foreach($this->aFieldsList[$sAlias] as $sAttCodeEx => $oAttDef)
522			{
523				$sLabel = $bLocalize ? MetaModel::GetLabel($sClassName, $sAttCodeEx, isset($aParams['showMandatoryFields'])) : $sAttCodeEx;
524				if($oAttDef instanceof AttributeDateTime)
525				{
526					$this->aTableHeaders[$sLabel] = 'datetime';
527				}
528				else
529				{
530					$this->aTableHeaders[$sLabel] = 'string';
531				}
532			}
533		}
534	}
535}
536
537