* @author Denis Flaven * @license http://www.opensource.org/licenses/lgpl-3.0.html LGPL * @link http://www.combodo.com/itop iTop */ require_once('../application/application.inc.php'); require_once('../application/itopwebpage.class.inc.php'); require_once('../application/startup.inc.php'); require_once('../application/loginwebpage.class.inc.php'); LoginWebPage::DoLogin(); // Check user rights and prompt if needed $oContext = new UserContext(); $oAppContext = new ApplicationContext(); $currentOrganization = utils::ReadParam('org_id', 1); $iStep = utils::ReadParam('step', 1); $oPage = new iTopWebPage("iTop - Bulk import", $currentOrganization); /** * Helper function to build a select from the list of valid classes for a given action * @param string $sName The name of the select in the HTML form * @param string $sDefaulfValue The defaut value (i.e the value selected by default) * @param integer $iWidthPx The width (in pixels) of the drop-down list * @param integer $iActionCode The ActionCode (from UserRights) to check for authorization for the classes * @return string The HTML fragment corresponding to the select tag */ function GetClassesSelect($sName, $sDefaultValue, $iWidthPx, $iActionCode = null) { $sHtml = ""; return $sHtml; } /** * Helper to 'check' an input in an HTML form if the current value equals the value given * @param mixed $sCurrentValue The current value to be chacked against the value of the input * @param mixed $sProposedValue The value of the input * @return string Either ' checked' or an empty string */ function IsChecked($sCurrentValue, $sProposedValue) { return ($sCurrentValue == $sProposedValue) ? ' checked' : ''; } /** * Get the user friendly name for an 'extended' attribute code i.e 'name', becomes 'Name' and 'org_id->name' becomes 'Organization->Name' * @param string $sClassName The name of the class * @param string $sAttCodeEx Either an attribute code of ext_key_name->att_code * @return string A user friendly format of the string: AttributeName or AttributeName->ExtAttributeName */ function GetFriendlyAttCodeName($sClassName, $sAttCodeEx) { $sFriendlyName = ''; if (preg_match('/(.+)->(.+)/', $sAttCodeEx, $aMatches) > 0) { $Attribute = $aMatches[1]; $sField = $aMatches[2]; $oAttDef = MetaModel::GetAttributeDef($sClassName, $Attribute); if ($oAttDef->IsExternalKey()) { $sTargetClass = $oAttDef->GetTargetClass(); $oTargetAttDef = MetaModel::GetAttributeDef($sTargetClass, $sField); $sFriendlyName = $oAttDef->GetLabel().'->'.$oTargetAttDef->GetLabel(); } else { // hum, hum... should never happen, we'd better raise an exception throw(new Exception("Internal error: '$sAttCodeEx' is an incorrect code because '$sAttribute' is NOT an external key of the class '$sClassName'.")); } } else { if ($sAttCodeEx == 'id') { $sFriendlyName = 'id (Primary Key)'; } else { $oAttDef = MetaModel::GetAttributeDef($sClassName, $sAttCodeEx); $sFriendlyName = $oAttDef->GetLabel(); } } return $sFriendlyName; } /** * Returns the number of occurences of each char from the set in the specified string * @param string $sString The input data * @param array $aSet The set of characters to count * @return hash 'char' => nb of occurences */ function CountCharsFromSet($sString, $aSet) { $aResult = array(); $aCount = count_chars($sString); foreach($aSet as $sChar) { $aResult[$sChar] = isset($aCount[ord($sChar)]) ? $aCount[ord($sChar)] : 0; } return $aResult; } /** * Return the most frequent (and regularly occuring) character among the given set, in the specified lines * @param array $aCSVData The input data, one entry per line * @param array $aPossibleSeparators The list of characters to count * @return string The most frequent character from the set */ function GuessFromFrequency($aCSVData, $aPossibleSeparators) { $iLine = 0; $iMaxLine = 20; // Process max 20 lines to guess the parameters foreach($aPossibleSeparators as $sSep) { $aGuesses[$sSep]['total'] = $aGuesses[$sSep]['max'] = 0; $aGuesses[$sSep]['min'] = 999; } $aStats = array(); while(($iLine < count($aCSVData)) && ($iLine < $iMaxLine) ) { if (strlen($aCSVData[$iLine]) > 0) { $aStats[$iLine] = CountCharsFromSet($aCSVData[$iLine], $aPossibleSeparators); } $iLine++; } $iLine = 1; foreach($aStats as $aLineStats) { foreach($aPossibleSeparators as $sSep) { $aGuesses[$sSep]['total'] += $aLineStats[$sSep]; if ($aLineStats[$sSep] > $aGuesses[$sSep]['max']) $aGuesses[$sSep]['max'] = $aLineStats[$sSep]; if ($aLineStats[$sSep] < $aGuesses[$sSep]['min']) $aGuesses[$sSep]['min'] = $aLineStats[$sSep]; } $iLine++; } $aScores = array(); foreach($aGuesses as $sSep => $aData) { $aScores[$sSep] = $aData['total'] + $aData['max'] - $aData['min']; } arsort($aScores, SORT_NUMERIC); // Sort the array, higher scores first $aKeys = array_keys($aScores); $sSeparator = $aKeys[0]; // Take the first key, the one with the best score return $sSeparator; } /** * Try to predict the CSV parameters based on the input data * @param string $sCSVData The input data * @return hash 'separator' => the_guessed_separator, 'qualifier' => the_guessed_text_qualifier */ function GuessParameters($sCSVData) { $aData = explode("\n", $sCSVData); $sSeparator = GuessFromFrequency($aData, array("\t", ',', ';', '|')); // Guess the most frequent (and regular) character on each line $sQualifier = GuessFromFrequency($aData, array('"', "'")); // Guess the most frequent (and regular) character on each line return array('separator' => $sSeparator, 'qualifier' => $sQualifier); } /** * Process the CSV data, for real or as a simulation * @param WebPage $oPage The page used to display the wizard * @param UserContext $oContext The current user context * @param bool $bSimulate Whether or not to simulate the data load * @return array The CSV lines in error that were rejected from the load (with the header line - if any) or null */ function ProcessCSVData(WebPage $oPage, UserContext $oContext, $bSimulate = true) { $aResult = array(); $sCSVData = utils::ReadParam('csvdata', ''); $sCSVDataTruncated = utils::ReadParam('csvdata_truncated', ''); $sSeparator = utils::ReadParam('separator', ','); $sTextQualifier = utils::ReadParam('text_qualifier', '"'); $bHeaderLine = (utils::ReadParam('header_line', '0') == 1); $iRealSkippedLines = $iSkippedLines = utils::ReadParam('nb_skipped_lines', '0'); $sClassName = utils::ReadParam('class_name', ''); $aFieldsMapping = utils::ReadParam('field', array()); $aSearchFields = utils::ReadParam('search_field', array()); $iCurrentStep = $bSimulate ? 4 : 5; // Parse the data set $oCSVParser = new CSVParser($sCSVData, $sSeparator, $sTextQualifier); $aData = $oCSVParser->ToArray($iSkippedLines); if ($bHeaderLine) { $aResult[] = $sTextQualifier.implode($sTextQualifier.$sSeparator.$sTextQualifier, array_shift($aData)).$sTextQualifier; // Remove the first line and store it in case of error $iRealSkippedLines++; } // Format for the line numbers $sMaxLen = (strlen(''.count($aData)) < 3) ? 3 : strlen(''.count($aData)); // Pad line numbers to the appropriate number of chars, but at least 3 // Compute the list of search/reconciliation criteria $aSearchKeys = array(); foreach($aSearchFields as $index => $sDummy) { $sSearchField = $aFieldsMapping[$index]; $aMatches = array(); if (preg_match('/(.+)->(.+)/', $sSearchField, $aMatches) > 0) { $sSearchField = $aMatches[1]; $aSearchKeys[$aMatches[1]] = ''; } else { $aSearchKeys[$sSearchField] = ''; } if (!MetaModel::IsValidFilterCode($sClassName, $sSearchField)) { // Remove invalid or unmapped search fields $aSearchFields[$index] = null; unset($aSearchKeys[$sSearchField]); } } // Compute the list of fields and external keys to process $aExtKeys = array(); $aAttributes = array(); $aExternalKeysByColumn = array(); foreach($aFieldsMapping as $iNumber => $sAttCode) { $iIndex = $iNumber-1; if (!empty($sAttCode) && ($sAttCode != ':none:') && ($sAttCode != 'finalclass')) { if (preg_match('/(.+)->(.+)/', $sAttCode, $aMatches) > 0) { $sAttribute = $aMatches[1]; $sField = $aMatches[2]; $aExtKeys[$sAttribute][$sField] = $iIndex; $aExternalKeysByColumn[$iIndex] = $sAttribute; } else { if ($sAttCode == 'id') { $aAttributes['id'] = $iIndex; } else { $oAttDef = MetaModel::GetAttributeDef($sClassName, $sAttCode); if ($oAttDef->IsExternalKey()) { $aExtKeys[$sAttCode]['id'] = $iIndex; $aExternalKeysByColumn[$iIndex] = $sAttCode; } else { $aAttributes[$sAttCode] = $iIndex; } } } } } $oMyChange = null; if (!$bSimulate) { // We're doing it for real, let's create a change $oMyChange = MetaModel::NewObject("CMDBChange"); $oMyChange->Set("date", time()); if (UserRights::GetUser() != UserRights::GetRealUser()) { $sUserString = UserRights::GetRealUser()." on behalf of ".UserRights::GetUser(); } else { $sUserString = UserRights::GetUser(); } $oMyChange->Set("userinfo", $sUserString); $iChangeId = $oMyChange->DBInsert(); } $oBulk = new BulkChange( $sClassName, $aData, $aAttributes, $aExtKeys, array_keys($aSearchKeys) ); $oPage->add(''); $aRes = $oBulk->Process($oMyChange); $sHtml = ''; $sHtml .= ''; $sHtml .= ''; $sHtml .= ''; foreach($aFieldsMapping as $iNumber => $sAttCode) { if (!empty($sAttCode) && ($sAttCode != ':none:') && ($sAttCode != 'finalclass')) { $sHtml .= ""; } } $sHtml .= ''; $sHtml .= ''; $iLine = 0; $iErrors = 0; $iCreated = 0; $iModified = 0; $iUnchanged = 0; foreach($aData as $aRow) { $oStatus = $aRes[$iLine]['__STATUS__']; $sUrl = ''; $sMessage = ''; $sCSSRowClass = ''; $sCSSMessageClass = 'cell_ok'; switch(get_class($oStatus)) { case 'RowStatus_NoChange': $iUnchanged++; $sFinalClass = $aRes[$iLine]['finalclass']; $oObj = $oContext->GetObject($sFinalClass, $aRes[$iLine]['id']->GetValue()); $sUrl = $oObj->GetHyperlink(); $sStatus = ''; $sCSSRowClass = 'row_unchanged'; break; case 'RowStatus_Modify': $iModified++; $sFinalClass = $aRes[$iLine]['finalclass']; $oObj = $oContext->GetObject($sFinalClass, $aRes[$iLine]['id']->GetValue()); $sUrl = $oObj->GetHyperlink(); $sStatus = ''; $sCSSRowClass = 'row_modified'; break; case 'RowStatus_NewObj': $iCreated++; $sFinalClass = $aRes[$iLine]['finalclass']; $sStatus = ''; $sCSSRowClass = 'row_added'; if ($bSimulate) { $sMessage = 'Object will be created'; } else { $sFinalClass = $aRes[$iLine]['finalclass']; $oObj = $oContext->GetObject($sFinalClass, $aRes[$iLine]['id']->GetValue()); $sUrl = $oObj->GetHyperlink(); $sMessage = 'Object created'; } break; case 'RowStatus_Issue': $iErrors++; $sMessage .= $oPage->GetP($oStatus->GetDescription()); $sStatus = ''; $sCSSMessageClass = 'cell_error'; $sCSSRowClass = 'row_error'; $aResult[] = $sTextQualifier.implode($sTextQualifier.$sSeparator.$sTextQualifier,$aRow).$sTextQualifier; // Remove the first line and store it in case of error break; } $sHtml .= ''; $sHtml .= ""; $sHtml .= ""; $sHtml .= ""; foreach($aFieldsMapping as $iNumber => $sAttCode) { if (!empty($sAttCode) && ($sAttCode != ':none:') && ($sAttCode != 'finalclass')) { $oCellStatus = $aRes[$iLine][$iNumber -1]; $sCellMessage = ''; if (isset($aExternalKeysByColumn[$iNumber -1])) { $sExtKeyName = $aExternalKeysByColumn[$iNumber -1]; $oExtKeyCellStatus = $aRes[$iLine][$sExtKeyName]; switch(get_class($oExtKeyCellStatus)) { case 'CellStatus_Issue': $sCellMessage .= $oPage->GetP($oExtKeyCellStatus->GetDescription()); break; case 'CellStatus_Ambiguous': $sCellMessage .= $oPage->GetP($oExtKeyCellStatus->GetDescription()); break; default: // Do nothing } } switch(get_class($oCellStatus)) { case 'CellStatus_Issue': $sCellMessage .= $oPage->GetP($oCellStatus->GetDescription()); $sHtml .= ''; break; case 'CellStatus_Ambiguous': $sCellMessage .= $oPage->GetP($oCellStatus->GetDescription()); $sHtml .= ''; break; case 'CellStatus_Modify': $sHtml .= ''; break; default: $sHtml .= ''; } } } $sHtml .= ""; $iLine++; $sHtml .= ''; } $sHtml .= '
LineStatusObject".GetFriendlyAttCodeName($sClassName, $sAttCode)."Message
".sprintf("%0{$sMaxLen}d", 1+$iLine+$iRealSkippedLines)."$sStatus$sUrlERROR: '.htmlentities($aData[$iLine][$iNumber-1]).$sCellMessage.'AMBIGUOUS: '.htmlentities($aData[$iLine][$iNumber-1]).$sCellMessage.''.htmlentities($aData[$iLine][$iNumber-1]).''.htmlentities($aData[$iLine][$iNumber-1]).$sCellMessage.'$sMessage
'; $oPage->add('
'); $oPage->add('
'); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add(''); foreach($aFieldsMapping as $iNumber => $sAttCode) { $oPage->add(''); } foreach($aSearchFields as $index => $sDummy) { $oPage->add(''); } $aFieldsMapping = utils::ReadParam('field', array()); $aSearchFields = utils::ReadParam('search_field', array()); $aDisplayFilters = array(); if ($bSimulate) { $aDisplayFilters['unchanged'] = '%d objects(s) will stay unchanged.'; $aDisplayFilters['modified'] = '%d objects(s) will stay be modified.'; $aDisplayFilters['added'] = '%d objects(s) will be added.'; $aDisplayFilters['errors'] = '%d objects(s) will have errors.'; } else { $aDisplayFilters['unchanged'] = '%d objects(s) remained unchanged.'; $aDisplayFilters['modified'] = '%d objects(s) were modified.'; $aDisplayFilters['added'] = '%d objects(s) were added.'; $aDisplayFilters['errors'] = '%d objects(s) had errors.'; } $oPage->add('

  '.sprintf($aDisplayFilters['unchanged'], $iUnchanged).'  '); $oPage->add('  '.sprintf($aDisplayFilters['modified'], $iModified).'  '); $oPage->add('  '.sprintf($aDisplayFilters['added'], $iCreated).'  '); $oPage->add('  '.sprintf($aDisplayFilters['errors'], $iErrors).'

'); $oPage->add('
'); $oPage->add($sHtml); $oPage->add('
'); $oPage->add('

  '); if ($bSimulate) { $oPage->add('

'); } else { $oPage->add('

'); } $oPage->add('
'); $oPage->add('
'); $oPage->add_script( <<< EOF function CSVGoBack() { $('input[name=step]').val($iCurrentStep-1); $('#wizForm').submit(); } function ToggleRows(sCSSClass) { $('.'+sCSSClass).toggle(); } EOF ); if ($iErrors > 0) { return $aResult; } else { return null; } } /** * Perform the actual load of the CSV data and display the results * @param WebPage $oPage The web page to display the wizard * @param UserContext $oContext Current user's context * @return void */ function LoadData(WebPage $oPage, UserContext $oContext) { $oPage->add('

Step 5 of 5: Import completed

'); $aResult = ProcessCSVData($oPage, $oContext, false /* simulate = false */); if (is_array($aResult)) { $oPage->StartCollapsibleSection("Lines that could not be loaded:", false); $oPage->p('The following lines have not been imported because they contain errors'); $oPage->add(''); $oPage->EndCollapsibleSection(); } } /** * Simulate the load of the CSV data and display the results * @param WebPage $oPage The web page to display the wizard * @param UserContext $oContext Current user's context * @return void */ function Preview(WebPage $oPage, UserContext $oContext) { $oPage->add('

Step 4 of 5: Import simulation

'); ProcessCSVData($oPage, $oContext, true /* simulate */); } /** * Select the mapping between the CSV column and the fields of the objects * @param WebPage $oPage The web page to display the wizard * @return void */ function SelectMapping(WebPage $oPage) { $sCSVData = utils::ReadParam('csvdata', ''); $sCSVDataTruncated = utils::ReadParam('csvdata_truncated', '');; $sSeparator = utils::ReadParam('separator', ','); if ($sSeparator == 'tab') $sSeparator = "\t"; if ($sSeparator == 'other') { $sSeparator = utils::ReadParam('other_separator', ','); } $sTextQualifier = utils::ReadParam('text_qualifier', '"'); if ($sTextQualifier == 'other') { $sTextQualifier = utils::ReadParam('other_qualifier', '"'); } $bHeaderLine = (utils::ReadParam('header_line', '0') == 1); $iSkippedLines = 0; if (utils::ReadParam('box_skiplines', '0') == 1) { $iSkippedLines = utils::ReadParam('nb_skipped_lines', '0'); } $sClassName = utils::ReadParam('class_name', ''); $oPage->add('

Step 3 of 5: Data mapping

'); $oPage->add('
'); $oPage->add('

Select the class to import: '); $oPage->add(GetClassesSelect('class_name', $sClassName, 300, UR_ACTION_BULK_MODIFY).'

'); $oPage->add('


Select a class to configure the mapping

'); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add('

  '); $oPage->add('

'); $oPage->add('
'); $oPage->add('
'); $oPage->add_ready_script( <<add_ready_script("DoMapping();"); // There is already a class selected, run the mapping } $oPage->add_script( <<IsEmpty()) { $sCSVData = $oDocument->GetData(); } break; default: $sCSVData = utils::ReadParam('csvdata', '', 'post'); } $aGuesses = GuessParameters($sCSVData); // Try to predict the parameters, based on the input data $sSeparator = utils::ReadParam('separator', $aGuesses['separator']); if ($sSeparator == 'tab') $sSeparator = "\t"; $sTextQualifier = utils::ReadParam('qualifier', $aGuesses['qualifier']); $bHeaderLine = utils::ReadParam('header_line', 0); // Create a truncated version of the data used for the fast preview // Take about 20 lines of data... knowing that some lines may contain carriage returns $iMaxLines = 20; $iMaxLen = strlen($sCSVData); $iCurPos = true; while ( ($iCurPos > 0) && ($iMaxLines > 0)) { $pos = strpos($sCSVData, "\n", $iCurPos); if ($pos !== false) { $iCurPos = 1+$pos; } else { $iCurPos = strlen($sCSVData); $iMaxLines = 1; } $iMaxLines--; } $sCSVDataTruncated = substr($sCSVData, 0, $iCurPos); $oPage->add('

Step 2 of 5: CSV data options

'); $oPage->add('
'); $oPage->add('
'); $oPage->add('
'); $oPage->add('

Separator character:

'); $oPage->add('

, (comma)
'); $oPage->add(' ; (semicolon)
'); $oPage->add(' tab
'); $oPage->add(' other: '); $oPage->add('

'); $oPage->add('
'); $oPage->add('

Text qualifier character:

'); $oPage->add('

" (double quote)
'); $oPage->add(' \' (simple quote)
'); $oPage->add(' other: '); $oPage->add('

'); $oPage->add('		'); $oPage->add('

Comments and header:

'); $oPage->add('

Treat the first line as a header (column names)

'); $oPage->add('

Skip line(s) at the beginning of the file

'); $oPage->add('

'); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add('
'); $oPage->add('

Data Preview

'); $oPage->add('
'); $oPage->add(''); $oPage->add(''); $oPage->add(''); $oPage->add('
'); $oPage->add_script( <<add_ready_script('DoPreview();'); } /** * Prompt for the data to be loaded (either via a file or a copy/paste) * @param WebPage $oPage The current web page * @return void */ function Welcome(iTopWebPage $oPage) { $oPage->add("

CSV import wizard

\n"); $oPage->AddTabContainer('tabs1'); $sFileLoadHtml = '

Select the file to import:

'. '

'. '

'. '

'. '

'. '
'; $oPage->AddToTab('tabs1', "Load from a file", $sFileLoadHtml); $sCSVData = utils::ReadParam('csvdata', ''); $sPasteDataHtml = '

Paste the data to import:

'. '

'. '

'. '

'. '

'. '
'; $oPage->AddToTab('tabs1', "Copy and paste data", $sPasteDataHtml); $sTemplateHtml = '

Pick the template do download: '; $sTemplateHtml .= GetClassesSelect('template_class', '', 300, UR_ACTION_BULK_MODIFY); $sTemplateHtml .= '

'; $sTemplateHtml .= '
'; $sTemplateHtml .= '
'; $oPage->AddToTab('tabs1', "Templates", $sTemplateHtml); $oPage->add_script( <<add_ready_script( <<output(); ?>