/**
* 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;
}
