/** * Export data specified by an OQL or a query phrasebook entry * * @copyright Copyright (C) 2015-2016 Combodo SARL * @license http://opensource.org/licenses/AGPL-3.0 */ if (!defined('__DIR__')) define('__DIR__', dirname(__FILE__)); require_once(__DIR__.'/../approot.inc.php'); require_once(APPROOT.'/application/application.inc.php'); require_once(APPROOT.'/application/nicewebpage.class.inc.php'); require_once(APPROOT.'/application/ajaxwebpage.class.inc.php'); require_once(APPROOT.'/application/csvpage.class.inc.php'); require_once(APPROOT.'/application/itopwebpage.class.inc.php'); require_once(APPROOT.'/application/xmlpage.class.inc.php'); require_once(APPROOT.'/application/clipage.class.inc.php'); require_once(APPROOT.'/application/excelexporter.class.inc.php'); require_once(APPROOT.'/core/bulkexport.class.inc.php'); require_once(APPROOT.'/application/startup.inc.php'); function ReportErrorAndExit($sErrorMessage) { if (utils::IsModeCLI()) { $oP = new CLIPage("iTop - Export"); $oP->p('ERROR: '.$sErrorMessage); $oP->output(); exit -1; } else { $oP = new WebPage("iTop - Export"); $oP->p('ERROR: '.$sErrorMessage); $oP->output(); exit -1; } } function ReportErrorAndUsage($sErrorMessage) { if (utils::IsModeCLI()) { $oP = new CLIPage("iTop - Export"); $oP->p('ERROR: '.$sErrorMessage); Usage($oP); $oP->output(); exit -1; } else { $oP = new WebPage("iTop - Export"); $oP->p('ERROR: '.$sErrorMessage); Usage($oP); $oP->output(); exit -1; } } function Usage(Page $oP) { if (Utils::IsModeCLI()) { $oP->p('Usage: php '.basename(__FILE__).' --auth_user= --auth_pwd= --expression= --query= [--arg_xxx=] [--no_localize=0|1] [--format=] [--format-options...]'); $oP->p("Parameters:"); $oP->p(" * auth_user: the iTop user account for authentication"); $oP->p(" * auth_pwd: the password of the iTop user account"); } else { $oP->p("Parameters:"); } $oP->p(" * expression: an OQL expression (e.g. SELECT Contact WHERE name LIKE 'm%')"); $oP->p(" * query: (alternative to 'expression') the id of an entry from the query phrasebook"); $oP->p(" * arg_xxx: (needed if the query has parameters) the value of the parameter 'xxx'"); $aSupportedFormats = BulkExport::FindSupportedFormats(); $oP->p(" * format: (optional, default is html) the desired output format. Can be one of '".implode("', '", array_keys($aSupportedFormats))."'"); foreach($aSupportedFormats as $sFormatCode => $sLabel) { $oExporter = BulkExport::FindExporter($sFormatCode); if ($oExporter !== null) { if (!Utils::IsModeCLI()) { $oP->add('

'); } $oExporter->DisplayUsage($oP); if (!Utils::IsModeCLI()) { $oP->add(''); } } } if (!Utils::IsModeCLI()) { //$oP->add(''); } } function DisplayExpressionForm(WebPage $oP, $sAction, $sExpression = '', $sExceptionMessage = '') { $oP->add('

'.Dict::S('Core:BulkExport:ScopeDefinition').''); $oP->add(''); $oP->add('

'); $oP->p(''.Dict::S('Core:BulkExportCanRunNonInteractive').''); $oP->p(''.Dict::S('Core:BulkExportLegacyExport').''); $sJSEmptyOQL = json_encode(Dict::S('Core:BulkExportMessageEmptyOQL')); $sJSEmptyQueryId = json_encode(Dict::S('Core:BulkExportMessageEmptyPhrasebookEntry')); $oP->add_ready_script( <<add_script(DateTimeFormat::GetJSSQLToCustomFormat()); $sJSDefaultDateTimeFormat = json_encode((string)AttributeDateTime::GetFormat()); $oP->add_script( <<add_linked_script(utils::GetAbsoluteUrlAppRoot().'js/tabularfieldsselector.js'); $oP->add_linked_script(utils::GetAbsoluteUrlAppRoot().'js/jquery.dragtable.js'); $oP->add_linked_stylesheet(utils::GetAbsoluteUrlAppRoot().'css/dragtable.css'); $oP->add('

'); $bExpressionIsValid = true; $sExpressionError = ''; if (($sExpression === null) && ($sQueryId === null)) { $bExpressionIsValid = false; } else if ($sExpression !== '') { try { $oExportSearch = DBObjectSearch::FromOQL($sExpression); } catch(OQLException $e) { $bExpressionIsValid = false; $sExpressionError = $e->getMessage(); } } if (!$bExpressionIsValid) { DisplayExpressionForm($oP, $sAction, $sExpression, $sExpressionError); return; } if ($sExpression !== '') { $oP->add(''); $oExportSearch = DBObjectSearch::FromOQL($sExpression); } else { $oQuery = MetaModel::GetObject('QueryOQL', $sQueryId); $oExportSearch = DBObjectSearch::FromOQL($oQuery->Get('oql')); $oP->add(''); } $aFormPartsByFormat = array(); $aAllFormParts = array(); if ($sFormat == null) { // No specific format chosen $sDefaultFormat = utils::ReadParam('format', 'xlsx'); $oP->add('

'.Dict::S('Core:BulkExport:ExportFormatPrompt').'

'); } else { // One specific format was chosen $oP->add(''); $oExporter = BulkExport::FindExporter($sFormat, $oExportSearch); $aParts = $oExporter->EnumFormParts(); foreach($aParts as $sPartId => $void) { $aAllFormParts[$sPartId] = $oExporter; } $aFormPartsByFormat[$sFormat] = array_keys($aAllFormParts); } foreach($aAllFormParts as $sPartId => $oExport) { $oP->add('

'); $oExport->DisplayFormPart($oP, $sPartId); $oP->add('

'); } $oP->add('

'); $oP->add(''); $oP->add(''); $oP->add(''); $sJSParts = json_encode($aFormPartsByFormat); $sJSCancel = json_encode(Dict::S('UI:Button:Cancel')); $sJSClose = json_encode(Dict::S('UI:Button:Done')); $oP->add_ready_script( <<add('

'); $sExportBtnLabel = json_encode(Dict::S('UI:Button:Export')); $sJSTitle = json_encode(htmlentities(utils::ReadParam('dialog_title', '', false, 'raw_data'), ENT_QUOTES, 'UTF-8')); $oP->add_ready_script( <<SetBreadCrumbEntry('ui-tool-export', Dict::S('Menu:ExportMenu'), Dict::S('Menu:ExportMenu+'), '', utils::GetAbsoluteUrlAppRoot().'images/wrench.png'); } if ($sExpression === null) { // No expression supplied, let's check if phrasebook entry is given if ($sQueryId !== null) { $oSearch = DBObjectSearch::FromOQL('SELECT QueryOQL WHERE id = :query_id', array('query_id' => $sQueryId)); $oQueries = new DBObjectSet($oSearch); if ($oQueries->Count() > 0) { $oQuery = $oQueries->Fetch(); $sExpression = $oQuery->Get('oql'); $sFields = trim($oQuery->Get('fields')); } else { ReportErrorAndExit("Invalid query phrasebook identifier: '$sQueryId'"); } } else { if (utils::IsModeCLI()) { Usage(); ReportErrorAndExit("No expression or query phrasebook identifier supplied."); } else { // form to enter an OQL query or pick a query phrasebook identifier DisplayForm($oP, utils::GetAbsoluteUrlAppRoot().'webservices/export-v2.php', $sExpression, $sQueryId, $sFormat); $oP->output(); exit; } } } if ($sFormat !== null) { $oExporter = BulkExport::FindExporter($sFormat); if ($oExporter === null) { $aSupportedFormats = BulkExport::FindSupportedFormats(); ReportErrorAndExit("Invalid output format: '$sFormat'. The supported formats are: ".implode(', ', array_keys($aSupportedFormats))); } else { DisplayForm($oP, utils::GetAbsoluteUrlAppRoot().'webservices/export-v2.php', $sExpression, $sQueryId, $sFormat); } } else { DisplayForm($oP, utils::GetAbsoluteUrlAppRoot().'webservices/export-v2.php', $sExpression, $sQueryId, $sFormat); } if ($sMode == 'dialog') { $oP->add('

'); } $oP->output(); } /** * Checks the parameters and returns the appropriate exporter (if any) * @param string $sExpression The OQL query to export or null * @param string $sQueryId The entry in the query phrasebook if $sExpression is null * @param string $sFormat The code of export format: csv, pdf, html, xlsx * @throws MissingQueryArgument * @return Ambigous */ function CheckParameters($sExpression, $sQueryId, $sFormat) { $oExporter = null; if (($sExpression === null) && ($sQueryId === null)) { ReportErrorAndUsage("Missing parameter. The parameter 'expression' or 'query' must be specified."); } // Either $sExpression or $sQueryId must be specified if ($sExpression === null) { $oSearch = DBObjectSearch::FromOQL('SELECT QueryOQL WHERE id = :query_id', array('query_id' => $sQueryId)); $oQueries = new DBObjectSet($oSearch); if ($oQueries->Count() > 0) { $oQuery = $oQueries->Fetch(); $sExpression = $oQuery->Get('oql'); $sFields = $oQuery->Get('fields'); if (strlen($sFields) == 0) { $sFields = trim($oQuery->Get('fields')); } } else { ReportErrorAndExit("Invalid query phrasebook identifier: '$sQueryId'"); } } if ($sFormat === null) { ReportErrorAndUsage("Missing parameter 'format'."); } // Check if the supplied query is valid (and all the parameters are supplied try { $oSearch = DBObjectSearch::FromOQL($sExpression); $aArgs = array(); foreach($oSearch->GetQueryParams() as $sParam => $foo) { $value = utils::ReadParam('arg_'.$sParam, null, true, 'raw_data'); if (!is_null($value)) { $aArgs[$sParam] = $value; } else { throw new MissingQueryArgument("Missing parameter '--arg_$sParam'"); } } $oSearch->SetInternalParams($aArgs); $sFormat = utils::ReadParam('format', 'html', true /* Allow CLI */, 'raw_data'); $oExporter = BulkExport::FindExporter($sFormat, $oSearch); if ($oExporter == null) { $aSupportedFormats = BulkExport::FindSupportedFormats(); ReportErrorAndExit("Invalid output format: '$sFormat'. The supported formats are: ".implode(', ', array_keys($aSupportedFormats))); } } catch(MissingQueryArgument $e) { ReportErrorAndUsage("Invalid OQL query: '$sExpression'.\n".$e->getMessage()); } catch(OQLException $e) { ReportErrorAndExit("Invalid OQL query: '$sExpression'.\n".$e->getMessage()); } catch(Exception $e) { ReportErrorAndExit($e->getMessage()); } $oExporter->SetFormat($sFormat); $oExporter->SetChunkSize(EXPORTER_DEFAULT_CHUNK_SIZE); $oExporter->SetObjectList($oSearch); $oExporter->ReadParameters(); return $oExporter; } function DoExport(WebPage $oP, BulkExport $oExporter, $bInteractive = false) { $oExporter->SetHttpHeaders($oP); $exportResult = $oExporter->GetHeader(); $aStatus = array(); do { $exportResult .= $oExporter->GetNextChunk($aStatus); } while (($aStatus['code'] != 'done') && ($aStatus['code'] != 'error')); if ($aStatus['code'] == 'error') { $oExporter->Cleanup(); ReportErrorAndExit("Export failed: '{$aStatus['message']}'"); } else { $exportResult .= $oExporter->GetFooter(); $sMimeType = $oExporter->GetMimeType(); if (substr($sMimeType, 0, 5) == 'text/') { $sMimeType .= ';charset='.strtolower($oExporter->GetCharacterSet()); } $oP->SetContentType($sMimeType); $oP->SetContentDisposition('attachment', $oExporter->GetDownloadFileName()); $oP->add($exportResult); $oExporter->Cleanup(); } } ///////////////////////////////////////////////////////////////////////////// // // Command Line mode // ///////////////////////////////////////////////////////////////////////////// if (utils::IsModeCLI()) { try { // Do this before loging, in order to allow setting user credentials from within the file utils::UseParamFile(); } catch(Exception $e) { echo "Error: ".$e->GetMessage()."
\n"; exit -2; } $sAuthUser = utils::ReadParam('auth_user', null, true /* Allow CLI */, 'raw_data'); $sAuthPwd = utils::ReadParam('auth_pwd', null, true /* Allow CLI */, 'raw_data'); if ($sAuthUser == null) { ReportErrorAndUsage("Missing parameter '--auth_user'"); } if ($sAuthPwd == null) { ReportErrorAndUsage("Missing parameter '--auth_pwd'"); } if (UserRights::CheckCredentials($sAuthUser, $sAuthPwd)) { UserRights::Login($sAuthUser); // Login & set the user's language } else { ReportErrorAndExit("Access restricted or wrong credentials for user '$sAuthUser'"); } $sExpression = utils::ReadParam('expression', null, true /* Allow CLI */, 'raw_data'); $sQueryId = utils::ReadParam('query', null, true /* Allow CLI */, 'raw_data'); $bLocalize = (utils::ReadParam('no_localize', 0) != 1); if (($sExpression == null) && ($sQueryId == null)) { ReportErrorAndUsage("Missing parameter. At least one of '--expression' or '--query' must be specified."); } if ($sExpression === null) { $oSearch = DBObjectSearch::FromOQL('SELECT QueryOQL WHERE id = :query_id', array('query_id' => $sQueryId)); $oQueries = new DBObjectSet($oSearch); if ($oQueries->Count() > 0) { $oQuery = $oQueries->Fetch(); $sExpression = $oQuery->Get('oql'); } else { ReportErrorAndExit("Invalid query phrasebook identifier: '$sQueryId'"); } } try { $oSearch = DBObjectSearch::FromOQL($sExpression); $aArgs = array(); foreach($oSearch->GetQueryParams() as $sParam => $foo) { $value = utils::ReadParam('arg_'.$sParam, null, true, 'raw_data'); if (!is_null($value)) { $aArgs[$sParam] = $value; } else { throw new MissingQueryArgument("Missing parameter '--arg_$sParam'"); } } $oSearch->SetInternalParams($aArgs); $sFormat = utils::ReadParam('format', 'html', true /* Allow CLI */, 'raw_data'); $oExporter = BulkExport::FindExporter($sFormat); if ($oExporter == null) { $aSupportedFormats = BulkExport::FindSupportedFormats(); ReportErrorAndExit("Invalid output format: '$sFormat'. The supported formats are: ".implode(', ', array_keys($aSupportedFormats))); } $oExporter->SetFormat($sFormat); $oExporter->SetChunkSize(EXPORTER_DEFAULT_CHUNK_SIZE); $oExporter->SetObjectList($oSearch); $oExporter->ReadParameters(); $exportResult = $oExporter->GetHeader(); $aStatus = array(); do { $exportResult .= $oExporter->GetNextChunk($aStatus); } while (($aStatus['code'] != 'done') && ($aStatus['code'] != 'error')); if ($aStatus['code'] == 'error') { ReportErrorAndExit("Export failed: '{$aStatus['message']}'"); } else { $exportResult .= $oExporter->GetFooter(); echo $exportResult; } $oExporter->Cleanup(); } catch(MissingQueryArgument $e) { ReportErrorAndUsage("Invalid OQL query: '$sExpression'.\n".$e->getMessage()); } catch(OQLException $e) { ReportErrorAndExit("Invalid OQL query: '$sExpression'.\n".$e->getMessage()); } catch(Exception $e) { ReportErrorAndExit($e->getMessage()); } exit; } ///////////////////////////////////////////////////////////////////////////// // // Web Server mode // ///////////////////////////////////////////////////////////////////////////// try { require_once(APPROOT.'/application/loginwebpage.class.inc.php'); // Main parameters $sExpression = utils::ReadParam('expression', null, true /* Allow CLI */, 'raw_data'); $sQueryId = utils::ReadParam('query', null, true /* Allow CLI */, 'raw_data'); $sFormat = utils::ReadParam('format', null, true /* Allow CLI */); $sFileName = utils::ReadParam('filename', '', true, 'string'); $bInteractive = utils::ReadParam('interactive', false); $sMode = utils::ReadParam('mode', ''); LoginWebPage::DoLogin(); // Check user rights and prompt if needed ApplicationContext::SetUrlMakerClass('iTopStandardURLMaker'); if ($bInteractive) { InteractiveShell($sExpression, $sQueryId, $sFormat, $sFileName, $sMode); } else { $oExporter = CheckParameters($sExpression, $sQueryId, $sFormat); $sMimeType = $oExporter->GetMimeType(); if ($sMimeType == 'text/html') { $oP = new NiceWebPage('iTop export'); $oP->add_style("body { overflow: auto; }"); $oP->add_ready_script("$('table.listResults').tablesorter({widgets: ['MyZebra']});"); } else { $oP = new ajax_page('iTop export'); $oP->SetContentType($oExporter->GetMimeType()); } DoExport($oP, $oExporter, false); $oP->output(); } } catch (BulkExportMissingParameterException $e) { $oP = new ajax_page('iTop Export'); $oP->add($e->getMessage()); Usage($oP); $oP->output(); } catch (Exception $e) { $oP = new WebPage('iTop Export'); $oP->add('Error: '.$e->getMessage()); IssueLog::Error($e->getMessage()."\n".$e->getTraceAsString()); $oP->output(); }