mirror of
https://github.com/Combodo/iTop.git
synced 2026-02-13 07:24:13 +01:00
472 lines
14 KiB
PHP
472 lines
14 KiB
PHP
<?php
|
|
|
|
use Combodo\iTop\Application\WebPage\CLIPage;
|
|
use Combodo\iTop\Application\WebPage\Page;
|
|
|
|
require_once('xlsxwriter.class.php');
|
|
|
|
class ExcelExporter
|
|
{
|
|
protected $sToken;
|
|
protected $aStatistics;
|
|
protected $sState;
|
|
protected $fStartTime;
|
|
protected $oSearch;
|
|
protected $aObjectsIDs;
|
|
protected $aTableHeaders;
|
|
protected $aAuthorizedClasses;
|
|
protected $iChunkSize = 1000;
|
|
protected $iPosition;
|
|
protected $sOutputFilePath;
|
|
protected $bAdvancedMode;
|
|
|
|
public function __construct($sToken = null)
|
|
{
|
|
$this->aStatistics = [
|
|
'objects_count' => 0,
|
|
'total_duration' => 0,
|
|
'data_retrieval_duration' => 0,
|
|
'excel_build_duration' => 0,
|
|
'excel_write_duration' => 0,
|
|
'peak_memory_usage' => 0,
|
|
];
|
|
$this->fStartTime = microtime(true);
|
|
$this->oSearch = null;
|
|
|
|
$this->sState = 'new';
|
|
$this->aObjectsIDs = [];
|
|
$this->iPosition = 0;
|
|
$this->aAuthorizedClasses = null;
|
|
$this->aTableHeaders = null;
|
|
$this->sOutputFilePath = null;
|
|
$this->bAdvancedMode = false;
|
|
$this->CheckDataDir();
|
|
if ($sToken == null) {
|
|
$this->sToken = $this->GetNewToken();
|
|
} else {
|
|
$this->sToken = $sToken;
|
|
$this->ReloadState();
|
|
}
|
|
}
|
|
|
|
public function __destruct()
|
|
{
|
|
if (($this->sState != 'done') && ($this->sState != 'error') && ($this->sToken != null)) {
|
|
// Operation in progress, save the state
|
|
$this->SaveState();
|
|
} else {
|
|
// Operation completed, cleanup the temp files
|
|
@unlink($this->GetStateFile());
|
|
@unlink($this->GetDataFile());
|
|
}
|
|
self::CleanupOldFiles();
|
|
}
|
|
|
|
public function SetChunkSize($iChunkSize)
|
|
{
|
|
$this->iChunkSize = $iChunkSize;
|
|
}
|
|
|
|
public function SetOutputFilePath($sDestFilePath)
|
|
{
|
|
$this->sOutputFilePath = $sDestFilePath;
|
|
}
|
|
|
|
public function SetAdvancedMode($bAdvanced)
|
|
{
|
|
$this->bAdvancedMode = $bAdvanced;
|
|
}
|
|
|
|
public function SaveState()
|
|
{
|
|
$aState = [
|
|
'state' => $this->sState,
|
|
'statistics' => $this->aStatistics,
|
|
'filter' => $this->oSearch->serialize(),
|
|
'position' => $this->iPosition,
|
|
'chunk_size' => $this->iChunkSize,
|
|
'object_ids' => $this->aObjectsIDs,
|
|
'output_file_path' => $this->sOutputFilePath,
|
|
'advanced_mode' => $this->bAdvancedMode,
|
|
];
|
|
|
|
file_put_contents($this->GetStateFile(), json_encode($aState));
|
|
|
|
return $this->sToken;
|
|
}
|
|
|
|
public function ReloadState()
|
|
{
|
|
if ($this->sToken == null) {
|
|
throw new Exception('ExcelExporter not initialized with a token, cannot reload state');
|
|
}
|
|
|
|
if (!file_exists($this->GetStateFile())) {
|
|
throw new Exception("ExcelExporter: missing status file '".$this->GetStateFile()."', cannot reload state.");
|
|
}
|
|
$sJson = file_get_contents($this->GetStateFile());
|
|
$aState = json_decode($sJson, true);
|
|
if ($aState === null) {
|
|
throw new Exception("ExcelExporter:corrupted status file '".$this->GetStateFile()."', not a JSON, cannot reload state.");
|
|
}
|
|
|
|
$this->sState = $aState['state'];
|
|
$this->aStatistics = $aState['statistics'];
|
|
$this->oSearch = DBObjectSearch::unserialize($aState['filter']);
|
|
$this->iPosition = $aState['position'];
|
|
$this->iChunkSize = $aState['chunk_size'];
|
|
$this->aObjectsIDs = $aState['object_ids'];
|
|
$this->sOutputFilePath = $aState['output_file_path'];
|
|
$this->bAdvancedMode = $aState['advanced_mode'];
|
|
}
|
|
|
|
public function SetObjectList($oSearch)
|
|
{
|
|
$this->oSearch = $oSearch;
|
|
}
|
|
|
|
public function Run()
|
|
{
|
|
$sCode = 'error';
|
|
$iPercentage = 100;
|
|
$sMessage = Dict::Format('ExcelExporter:ErrorUnexpected_State', $this->sState);
|
|
$fTime = microtime(true);
|
|
|
|
try {
|
|
switch ($this->sState) {
|
|
case 'new':
|
|
$oIDSet = new DBObjectSet($this->oSearch);
|
|
$oIDSet->OptimizeColumnLoad(['id']);
|
|
$this->aObjectsIDs = [];
|
|
while ($oObj = $oIDSet->Fetch()) {
|
|
$this->aObjectsIDs[] = $oObj->GetKey();
|
|
}
|
|
$sCode = 'retrieving-data';
|
|
$iPercentage = 5;
|
|
$sMessage = Dict::S('ExcelExporter:RetrievingData');
|
|
$this->iPosition = 0;
|
|
$this->aStatistics['objects_count'] = count($this->aObjectsIDs);
|
|
$this->aStatistics['data_retrieval_duration'] += microtime(true) - $fTime;
|
|
|
|
// The first line of the file is the "headers" specifying the label and the type of each column
|
|
$this->GetFieldsList($oIDSet, $this->bAdvancedMode);
|
|
$sRow = json_encode($this->aTableHeaders);
|
|
$hFile = @fopen($this->GetDataFile(), 'ab');
|
|
if ($hFile === false) {
|
|
throw new Exception('ExcelExporter: Failed to open temporary data file: "'.$this->GetDataFile().'" for writing.');
|
|
}
|
|
fwrite($hFile, $sRow."\n");
|
|
fclose($hFile);
|
|
|
|
// Next state
|
|
$this->sState = 'retrieving-data';
|
|
break;
|
|
|
|
case 'retrieving-data':
|
|
$oCurrentSearch = clone $this->oSearch;
|
|
$aIDs = array_slice($this->aObjectsIDs, $this->iPosition, $this->iChunkSize);
|
|
|
|
$oCurrentSearch->AddCondition('id', $aIDs, 'IN');
|
|
$hFile = @fopen($this->GetDataFile(), 'ab');
|
|
if ($hFile === false) {
|
|
throw new Exception('ExcelExporter: Failed to open temporary data file: "'.$this->GetDataFile().'" for writing.');
|
|
}
|
|
$oSet = new DBObjectSet($oCurrentSearch);
|
|
$this->GetFieldsList($oSet, $this->bAdvancedMode);
|
|
while ($aObjects = $oSet->FetchAssoc()) {
|
|
$aRow = [];
|
|
foreach ($this->aAuthorizedClasses as $sAlias => $sClassName) {
|
|
$oObj = $aObjects[$sAlias];
|
|
if ($this->bAdvancedMode) {
|
|
$aRow[] = $oObj->GetKey();
|
|
}
|
|
foreach ($this->aFieldsList[$sAlias] as $sAttCodeEx => $oAttDef) {
|
|
$value = $oObj->Get($sAttCodeEx);
|
|
if ($value instanceof ormCaseLog) {
|
|
// 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!
|
|
$sExcelVal = trim(preg_replace('/========== ([^=]+) ============/', '********** $1 ************', $value->GetText()));
|
|
} else {
|
|
$sExcelVal = $oAttDef->GetEditValue($value, $oObj);
|
|
}
|
|
$aRow[] = $sExcelVal;
|
|
}
|
|
}
|
|
$sRow = json_encode($aRow);
|
|
fwrite($hFile, $sRow."\n");
|
|
}
|
|
fclose($hFile);
|
|
|
|
if (($this->iPosition + $this->iChunkSize) > count($this->aObjectsIDs)) {
|
|
// Next state
|
|
$this->sState = 'building-excel';
|
|
$sCode = 'building-excel';
|
|
$iPercentage = 80;
|
|
$sMessage = Dict::S('ExcelExporter:BuildingExcelFile');
|
|
} else {
|
|
$sCode = 'retrieving-data';
|
|
$this->iPosition += $this->iChunkSize;
|
|
$iPercentage = 5 + round(75 * ($this->iPosition / count($this->aObjectsIDs)));
|
|
$sMessage = Dict::S('ExcelExporter:RetrievingData');
|
|
}
|
|
break;
|
|
|
|
case 'building-excel':
|
|
$hFile = @fopen($this->GetDataFile(), 'rb');
|
|
if ($hFile === false) {
|
|
throw new Exception('ExcelExporter: Failed to open temporary data file: "'.$this->GetDataFile().'" for reading.');
|
|
}
|
|
$sHeaders = fgets($hFile);
|
|
$aHeaders = json_decode($sHeaders, true);
|
|
|
|
$aData = [];
|
|
while ($sLine = fgets($hFile)) {
|
|
$aRow = json_decode($sLine);
|
|
$aData[] = $aRow;
|
|
}
|
|
fclose($hFile);
|
|
@unlink($this->GetDataFile());
|
|
|
|
$fStartExcel = microtime(true);
|
|
$writer = new XLSXWriter();
|
|
$writer->setAuthor(UserRights::GetUserFriendlyName());
|
|
$writer->writeSheet($aData, 'Sheet1', $aHeaders);
|
|
$fExcelTime = microtime(true) - $fStartExcel;
|
|
$this->aStatistics['excel_build_duration'] = $fExcelTime;
|
|
|
|
$fTime = microtime(true);
|
|
$writer->writeToFile($this->GetExcelFilePath());
|
|
$fExcelSaveTime = microtime(true) - $fTime;
|
|
$this->aStatistics['excel_write_duration'] = $fExcelSaveTime;
|
|
|
|
// Next state
|
|
$this->sState = 'done';
|
|
$sCode = 'done';
|
|
$iPercentage = 100;
|
|
$sMessage = Dict::S('ExcelExporter:Done');
|
|
break;
|
|
|
|
case 'done':
|
|
$this->sState = 'done';
|
|
$sCode = 'done';
|
|
$iPercentage = 100;
|
|
$sMessage = Dict::S('ExcelExporter:Done');
|
|
break;
|
|
}
|
|
} catch (Exception $e) {
|
|
$sCode = 'error';
|
|
$sMessage = $e->getMessage();
|
|
}
|
|
|
|
$this->aStatistics['total_duration'] += microtime(true) - $fTime;
|
|
$peak_memory = memory_get_peak_usage(true);
|
|
if ($peak_memory > $this->aStatistics['peak_memory_usage']) {
|
|
$this->aStatistics['peak_memory_usage'] = $peak_memory;
|
|
}
|
|
|
|
return [
|
|
'code' => $sCode,
|
|
'message' => $sMessage,
|
|
'percentage' => $iPercentage,
|
|
];
|
|
}
|
|
|
|
public function GetExcelFilePath()
|
|
{
|
|
if ($this->sOutputFilePath == null) {
|
|
return utils::GetDataPath().'bulk_export/'.$this->sToken.'.xlsx';
|
|
} else {
|
|
return $this->sOutputFilePath;
|
|
}
|
|
}
|
|
|
|
public static function GetExcelFileFromToken($sToken)
|
|
{
|
|
return @file_get_contents(utils::GetDataPath().'bulk_export/'.$sToken.'.xlsx');
|
|
}
|
|
|
|
public static function CleanupFromToken($sToken)
|
|
{
|
|
@unlink(utils::GetDataPath().'bulk_export/'.$sToken.'.status');
|
|
@unlink(utils::GetDataPath().'bulk_export/'.$sToken.'.data');
|
|
@unlink(utils::GetDataPath().'bulk_export/'.$sToken.'.xlsx');
|
|
}
|
|
|
|
public function Cleanup()
|
|
{
|
|
self::CleanupFromToken($this->sToken);
|
|
}
|
|
|
|
/**
|
|
* Delete all files in the data/bulk_export directory which are older than 1 day
|
|
* unless a different delay is configured.
|
|
*/
|
|
public static function CleanupOldFiles()
|
|
{
|
|
$aFiles = glob(utils::GetDataPath().'bulk_export/*.*');
|
|
$iDelay = MetaModel::GetConfig()->Get('xlsx_exporter_cleanup_old_files_delay');
|
|
|
|
if ($iDelay > 0) {
|
|
foreach ($aFiles as $sFile) {
|
|
$iModificationTime = filemtime($sFile);
|
|
|
|
if ($iModificationTime < (time() - $iDelay)) {
|
|
// Temporary files older than one day are deleted
|
|
//echo "Supposed to delete: '".$sFile." (Unix Modification Time: $iModificationTime)'\n";
|
|
@unlink($sFile);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
public function DisplayStatistics(Page $oPage)
|
|
{
|
|
$aStats = [
|
|
'Number of objects exported' => $this->aStatistics['objects_count'],
|
|
'Total export duration' => sprintf('%.3f s', $this->aStatistics['total_duration']),
|
|
'Data retrieval duration' => sprintf('%.3f s', $this->aStatistics['data_retrieval_duration']),
|
|
'Excel build duration' => sprintf('%.3f s', $this->aStatistics['excel_build_duration']),
|
|
'Excel write duration' => sprintf('%.3f s', $this->aStatistics['excel_write_duration']),
|
|
'Peak memory usage' => self::HumanDisplay($this->aStatistics['peak_memory_usage']),
|
|
];
|
|
|
|
if ($oPage instanceof CLIPage) {
|
|
$oPage->add($this->GetStatistics('text'));
|
|
} else {
|
|
$oPage->add($this->GetStatistics('html'));
|
|
}
|
|
}
|
|
|
|
public function GetStatistics($sFormat = 'html')
|
|
{
|
|
$sStats = '';
|
|
$aStats = [
|
|
'Number of objects exported' => $this->aStatistics['objects_count'],
|
|
'Total export duration' => sprintf('%.3f s', $this->aStatistics['total_duration']),
|
|
'Data retrieval duration' => sprintf('%.3f s', $this->aStatistics['data_retrieval_duration']),
|
|
'Excel build duration' => sprintf('%.3f s', $this->aStatistics['excel_build_duration']),
|
|
'Excel write duration' => sprintf('%.3f s', $this->aStatistics['excel_write_duration']),
|
|
'Peak memory usage' => self::HumanDisplay($this->aStatistics['peak_memory_usage']),
|
|
];
|
|
|
|
if ($sFormat == 'text') {
|
|
foreach ($aStats as $sLabel => $sValue) {
|
|
$sStats .= "+------------------------------+----------+\n";
|
|
$sStats .= sprintf("|%-30s|%10s|\n", $sLabel, $sValue);
|
|
}
|
|
$sStats .= "+------------------------------+----------+";
|
|
} else {
|
|
$sStats .= '<table><tbody>';
|
|
foreach ($aStats as $sLabel => $sValue) {
|
|
$sStats .= "<tr><td>$sLabel</td><td>$sValue</td></tr>";
|
|
}
|
|
$sStats .= '</tbody></table>';
|
|
|
|
}
|
|
return $sStats;
|
|
}
|
|
|
|
public static function HumanDisplay($iSize)
|
|
{
|
|
$aUnits = ['B','KB','MB','GB','TB','PB'];
|
|
return @round($iSize / pow(1024, ($i = floor(log($iSize, 1024)))), 2).' '.$aUnits[$i];
|
|
}
|
|
|
|
protected function CheckDataDir()
|
|
{
|
|
if (!is_dir(utils::GetDataPath()."bulk_export")) {
|
|
@mkdir(utils::GetDataPath()."bulk_export", 0777, true /* recursive */);
|
|
clearstatcache();
|
|
}
|
|
if (!is_writable(utils::GetDataPath()."bulk_export")) {
|
|
throw new Exception('Data directory "'.utils::GetDataPath().'bulk_export" could not be written.');
|
|
}
|
|
}
|
|
|
|
protected function GetStateFile($sToken = null)
|
|
{
|
|
if ($sToken == null) {
|
|
$sToken = $this->sToken;
|
|
}
|
|
return utils::GetDataPath()."bulk_export/$sToken.status";
|
|
}
|
|
|
|
protected function GetDataFile()
|
|
{
|
|
return utils::GetDataPath().'bulk_export/'.$this->sToken.'.data';
|
|
}
|
|
|
|
protected function GetNewToken()
|
|
{
|
|
$iNum = rand();
|
|
do {
|
|
$iNum++;
|
|
$sToken = sprintf("%08x", $iNum);
|
|
$sFileName = $this->GetStateFile($sToken);
|
|
$hFile = @fopen($sFileName, 'x');
|
|
} while ($hFile === false);
|
|
|
|
fclose($hFile);
|
|
return $sToken;
|
|
}
|
|
|
|
protected function GetFieldsList($oSet, $bFieldsAdvanced = false, $bLocalize = true, $aFields = null)
|
|
{
|
|
$this->aFieldsList = [];
|
|
|
|
$oAppContext = new ApplicationContext();
|
|
$aClasses = $oSet->GetFilter()->GetSelectedClasses();
|
|
$this->aAuthorizedClasses = [];
|
|
foreach ($aClasses as $sAlias => $sClassName) {
|
|
if (UserRights::IsActionAllowed($sClassName, UR_ACTION_READ, $oSet) != UR_ALLOWED_NO) {
|
|
$this->aAuthorizedClasses[$sAlias] = $sClassName;
|
|
}
|
|
}
|
|
$aAttribs = [];
|
|
$this->aTableHeaders = [];
|
|
foreach ($this->aAuthorizedClasses as $sAlias => $sClassName) {
|
|
$aList[$sAlias] = [];
|
|
|
|
foreach (MetaModel::ListAttributeDefs($sClassName) as $sAttCode => $oAttDef) {
|
|
if (is_null($aFields) || (count($aFields) == 0)) {
|
|
// Standard list of attributes (no link sets)
|
|
if ($oAttDef->IsScalar() && ($oAttDef->IsWritable() || $oAttDef->IsExternalField())) {
|
|
$sAttCodeEx = $oAttDef->IsExternalField() ? $oAttDef->GetKeyAttCode().'->'.$oAttDef->GetExtAttCode() : $sAttCode;
|
|
|
|
if ($oAttDef->IsExternalKey(EXTKEY_ABSOLUTE)) {
|
|
if ($bFieldsAdvanced) {
|
|
$aList[$sAlias][$sAttCodeEx] = $oAttDef;
|
|
|
|
if ($oAttDef->IsExternalKey(EXTKEY_RELATIVE)) {
|
|
$sRemoteClass = $oAttDef->GetTargetClass();
|
|
foreach (MetaModel::GetReconcKeys($sRemoteClass) as $sRemoteAttCode) {
|
|
$this->aFieldsList[$sAlias][$sAttCode.'->'.$sRemoteAttCode] = MetaModel::GetAttributeDef($sRemoteClass, $sRemoteAttCode);
|
|
}
|
|
}
|
|
}
|
|
} else {
|
|
// Any other attribute
|
|
$this->aFieldsList[$sAlias][$sAttCodeEx] = $oAttDef;
|
|
}
|
|
}
|
|
} else {
|
|
// User defined list of attributes
|
|
if (in_array($sAttCode, $aFields) || in_array($sAlias.'.'.$sAttCode, $aFields)) {
|
|
$this->aFieldsList[$sAlias][$sAttCode] = $oAttDef;
|
|
}
|
|
}
|
|
}
|
|
if ($bFieldsAdvanced) {
|
|
$this->aTableHeaders['id'] = '0';
|
|
}
|
|
foreach ($this->aFieldsList[$sAlias] as $sAttCodeEx => $oAttDef) {
|
|
$sLabel = $bLocalize ? MetaModel::GetLabel($sClassName, $sAttCodeEx, isset($aParams['showMandatoryFields'])) : $sAttCodeEx;
|
|
if ($oAttDef instanceof AttributeDateTime) {
|
|
$this->aTableHeaders[$sLabel] = 'datetime';
|
|
} else {
|
|
$this->aTableHeaders[$sLabel] = 'string';
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|