/** * This function import CSV file to responses table * * @param string $sFullFilePath * @param integer $iSurveyId * @param array $aOptions * Return array $result ("errors","warnings","success") */ function CSVImportResponses($sFullFilePath, $iSurveyId, $aOptions = array()) { $clang = Yii::app()->lang; // Default optional if (!isset($aOptions['bDeleteFistLine'])) { $aOptions['bDeleteFistLine'] = true; } // By default delete first line (vvimport) if (!isset($aOptions['sExistingId'])) { $aOptions['sExistingId'] = "ignore"; } // By default exclude existing id if (!isset($aOptions['bNotFinalized'])) { $aOptions['bNotFinalized'] = false; } // By default don't change finalized part if (!isset($aOptions['sCharset']) || !$aOptions['sCharset']) { $aOptions['sCharset'] = "utf8"; } if (!isset($aOptions['sSeparator'])) { $aOptions['sSeparator'] = "\t"; } if (!isset($aOptions['sQuoted'])) { $aOptions['sQuoted'] = "\""; } // Fix some part if (!array_key_exists($aOptions['sCharset'], aEncodingsArray())) { $aOptions['sCharset'] = "utf8"; } // Prepare an array of sentence for result $CSVImportResult = array(); // Read the file $handle = fopen($sFullFilePath, "r"); // Need to be adapted for Mac ? in options ? while (!feof($handle)) { $buffer = fgets($handle); //To allow for very long lines . Another option is fgetcsv (0 to length), but need mb_convert_encoding $aFileResponses[] = mb_convert_encoding($buffer, "UTF-8", $aOptions['sCharset']); } // Close the file fclose($handle); if ($aOptions['bDeleteFistLine']) { array_shift($aFileResponses); } $aRealFieldNames = Yii::app()->db->getSchema()->getTable(SurveyDynamic::model($iSurveyId)->tableName())->getColumnNames(); //$aCsvHeader=array_map("trim",explode($aOptions['sSeparator'], trim(array_shift($aFileResponses)))); $aCsvHeader = str_getcsv(array_shift($aFileResponses), $aOptions['sSeparator'], $aOptions['sQuoted']); $aLemFieldNames = LimeExpressionManager::getLEMqcode2sgqa($iSurveyId); $aKeyForFieldNames = array(); // An array assicated each fieldname with corresponding responses key if (!$aCsvHeader) { $CSVImportResult['errors'][] = $clang->gT("File seems empty or has only one line"); return $CSVImportResult; } // Assign fieldname with $aFileResponses[] key foreach ($aRealFieldNames as $sFieldName) { if (in_array($sFieldName, $aCsvHeader)) { // First pass : simple associated $aKeyForFieldNames[$sFieldName] = array_search($sFieldName, $aCsvHeader); } elseif (in_array($sFieldName, $aLemFieldNames)) { // Second pass : LEM associated $sLemFieldName = array_search($sFieldName, $aLemFieldNames); if (in_array($sLemFieldName, $aCsvHeader)) { $aKeyForFieldNames[$sFieldName] = array_search($sLemFieldName, $aCsvHeader); } elseif ($aOptions['bForceImport']) { // as fallback just map questions in order of apperance // find out where the answer data columns start in CSV if (!isset($csv_ans_start_index)) { foreach ($aCsvHeader as $i => $name) { if (preg_match('/^\\d+X\\d+X\\d+/', $name)) { $csv_ans_start_index = $i; break; } } } // find out where the answer data columns start in destination table if (!isset($table_ans_start_index)) { foreach ($aRealFieldNames as $i => $name) { if (preg_match('/^\\d+X\\d+X\\d+/', $name)) { $table_ans_start_index = $i; break; } } } // map answers in order if (isset($table_ans_start_index, $csv_ans_start_index)) { $csv_index = array_search($sFieldName, $aRealFieldNames) - $table_ans_start_index + $csv_ans_start_index; if ($csv_index < sizeof($aCsvHeader)) { $aKeyForFieldNames[$sFieldName] = $csv_index; } else { $force_import_failed = true; break; } } } } } // check if forced error failed if (isset($force_import_failed)) { $CSVImportResult['errors'][] = $clang->gT("Import failed: Forced import was requested but the input file doesn't contain enough columns to fill the survey."); return $CSVImportResult; } // make sure at least one answer was imported before commiting foreach ($aKeyForFieldNames as $field => $index) { if (preg_match('/^\\d+X\\d+X\\d+/', $field)) { $import_ok = true; break; } } if (!isset($import_ok)) { $CSVImportResult['errors'][] = $clang->gT("Import failed: No answers could be mapped."); return $CSVImportResult; } // Now it's time to import // Some var to return $iNbResponseLine = 0; $iNbResponseExisting = 0; $aResponsesInserted = array(); $aResponsesUpdated = array(); $aResponsesError = array(); $aExistingsId = array(); $iMaxId = 0; // If we set the id, keep the max // Some specific header (with options) $iIdKey = array_search('id', $aCsvHeader); // the id is allways needed and used a lot if (is_int($iIdKey)) { unset($aKeyForFieldNames['id']); } $iSubmitdateKey = array_search('submitdate', $aCsvHeader); // submitdate can be forced to null if (is_int($iSubmitdateKey)) { unset($aKeyForFieldNames['submitdate']); } $iIdReponsesKey = is_int($iIdKey) ? $iIdKey : 0; // The key for reponses id: id column or first column if not exist // Import each responses line here while ($sResponses = array_shift($aFileResponses)) { $iNbResponseLine++; $bExistingsId = false; $aResponses = str_getcsv($sResponses, $aOptions['sSeparator'], $aOptions['sQuoted']); if ($iIdKey !== false) { $oSurvey = SurveyDynamic::model($iSurveyId)->findByPk($aResponses[$iIdKey]); if ($oSurvey) { $bExistingsId = true; $aExistingsId[] = $aResponses[$iIdKey]; // Do according to option switch ($aOptions['sExistingId']) { case 'replace': SurveyDynamic::model($iSurveyId)->deleteByPk($aResponses[$iIdKey]); SurveyDynamic::sid($iSurveyId); $oSurvey = new SurveyDynamic(); break; case 'replaceanswers': break; case 'renumber': SurveyDynamic::sid($iSurveyId); $oSurvey = new SurveyDynamic(); break; case 'skip': case 'ignore': default: $oSurvey = false; // Remove existing survey : don't import again break; } } else { SurveyDynamic::sid($iSurveyId); $oSurvey = new SurveyDynamic(); } } else { SurveyDynamic::sid($iSurveyId); $oSurvey = new SurveyDynamic(); } if ($oSurvey) { // First rule for id and submitdate if (is_int($iIdKey)) { if (!$bExistingsId) { $oSurvey->id = $aResponses[$iIdKey]; $iMaxId = $aResponses[$iIdKey] > $iMaxId ? $aResponses[$iIdKey] : $iMaxId; } elseif ($aOptions['sExistingId'] == 'replace' || $aOptions['sExistingId'] == 'replaceanswers') { $oSurvey->id = $aResponses[$iIdKey]; } } if ($aOptions['bNotFinalized']) { $oSurvey->submitdate = new CDbExpression('NULL'); } elseif (is_int($iSubmitdateKey)) { if ($aResponses[$iSubmitdateKey] == '{question_not_shown}' || trim($aResponses[$iSubmitdateKey] == '')) { $oSurvey->submitdate = new CDbExpression('NULL'); } else { // Maybe control valid date : see http://php.net/manual/en/function.checkdate.php#78362 for example $oSurvey->submitdate = $aResponses[$iSubmitdateKey]; } } foreach ($aKeyForFieldNames as $sFieldName => $iFieldKey) { if ($aResponses[$iFieldKey] == '{question_not_shown}') { $oSurvey->{$sFieldName} = new CDbExpression('NULL'); } else { $sResponse = str_replace(array("{quote}", "{tab}", "{cr}", "{newline}", "{lbrace}"), array("\"", "\t", "\r", "\n", "{"), $aResponses[$iFieldKey]); $oSurvey->{$sFieldName} = $sResponse; } } // We use transaction to prevent DB error $oTransaction = Yii::app()->db->beginTransaction(); try { if (isset($oSurvey->id) && !is_null($oSurvey->id)) { switchMSSQLIdentityInsert('survey_' . $iSurveyId, true); $bSwitched = true; } if ($oSurvey->save()) { $oTransaction->commit(); if ($bExistingsId && $aOptions['sExistingId'] != 'renumber') { $aResponsesUpdated[] = $aResponses[$iIdReponsesKey]; } else { $aResponsesInserted[] = $aResponses[$iIdReponsesKey]; } } else { $oTransaction->rollBack(); $aResponsesError[] = $aResponses[$iIdReponsesKey]; } if (isset($bSwitched) && $bSwitched == true) { switchMSSQLIdentityInsert('survey_' . $iSurveyId, false); $bSwitched = false; } } catch (Exception $oException) { $oTransaction->rollBack(); $aResponsesError[] = $aResponses[$iIdReponsesKey]; // Show some error to user ? // $CSVImportResult['errors'][]=$oException->getMessage(); // Show it in view // tracevar($oException->getMessage());// Show it in console (if debug is set) } } } // Fix max next id (for pgsql) // mysql dot need fix, but what for mssql ? // Do a model function for this can be a good idea (see activate_helper/activateSurvey) if (Yii::app()->db->driverName == 'pgsql') { $sSequenceName = Yii::app()->db->getSchema()->getTable("{{survey_{$iSurveyId}}}")->sequenceName; $iActualSerial = Yii::app()->db->createCommand("SELECT last_value FROM {$sSequenceName}")->queryScalar(); if ($iActualSerial < $iMaxId) { $sQuery = "SELECT setval(pg_get_serial_sequence('{{survey_{$iSurveyId}}}', 'id'),{$iMaxId},false);"; $result = @Yii::app()->db->createCommand($sQuery)->execute(); } } // End of import // Construction of returned information if ($iNbResponseLine) { $CSVImportResult['success'][] = sprintf($clang->gT("%s response lines in your file."), $iNbResponseLine); } else { $CSVImportResult['errors'][] = $clang->gT("No response lines in your file."); } if (count($aResponsesInserted)) { $CSVImportResult['success'][] = sprintf($clang->gT("%s responses were inserted."), count($aResponsesInserted)); // Maybe add implode aResponsesInserted array } if (count($aResponsesUpdated)) { $CSVImportResult['success'][] = sprintf($clang->gT("%s responses were updated."), count($aResponsesUpdated)); } if (count($aResponsesError)) { $CSVImportResult['errors'][] = sprintf($clang->gT("%s responses cannot be inserted or updated."), count($aResponsesError)); } if (count($aExistingsId) && ($aOptions['sExistingId'] == 'skip' || $aOptions['sExistingId'] == 'ignore')) { $CSVImportResult['warnings'][] = sprintf($clang->gT("%s responses already exist."), count($aExistingsId)); } return $CSVImportResult; }