/**
* dataentry::import()
* Function responsible to import responses from old survey table(s).
* @param int $iSurveyId
* @return void
*/
public function import($surveyid)
{
$iSurveyId = sanitize_int($surveyid);
if (Permission::model()->hasSurveyPermission($iSurveyId, 'responses', 'create')) {
if (!App()->getRequest()->isPostRequest || App()->getRequest()->getPost('table') == 'none') {
// Schema that serves as the base for compatibility checks.
$baseSchema = SurveyDynamic::model($iSurveyId)->getTableSchema();
$tables = App()->getApi()->getOldResponseTables($iSurveyId);
$compatible = array();
$coercible = array();
foreach ($tables as $table) {
$schema = PluginDynamic::model($table)->getTableSchema();
if (PluginDynamic::model($table)->count() > 0) {
if ($this->isCompatible($baseSchema, $schema)) {
$compatible[] = $table;
} elseif ($this->isCompatible($baseSchema, $schema, false)) {
$coercible[] = $table;
}
}
}
$aData = array();
$aData['surveyid'] = $iSurveyId;
$aData['settings']['table'] = array('label' => gT('Source table'), 'type' => 'select', 'options' => array(gT('Compatible') => $this->tableList($compatible), gT('Compatible with type coercion') => $this->tableList($coercible)));
$aData['settings']['timings'] = array('type' => 'checkbox', 'label' => gT('Import timings (if exist)'));
//Get the menubar
$aData['display']['menu_bars']['browse'] = gT("Quick statistics");
$surveyinfo = Survey::model()->findByPk($iSurveyId)->surveyinfo;
$aData["surveyinfo"] = $surveyinfo;
$aData['title_bar']['title'] = gT('Browse responses') . ': ' . $surveyinfo['surveyls_title'];
$aData['sidemenu']['state'] = false;
$aData['menu']['edition'] = true;
$aData['menu']['import'] = true;
$aData['menu']['close'] = true;
$this->_renderWrappedTemplate('dataentry', 'import', $aData);
} else {
$aSRIDConversions = array();
$targetSchema = SurveyDynamic::model($iSurveyId)->getTableSchema();
$sourceTable = PluginDynamic::model($_POST['table']);
$sourceSchema = $sourceTable->getTableSchema();
$fieldMap = array();
$pattern = '/([\\d]+)X([\\d]+)X([\\d]+.*)/';
foreach ($sourceSchema->getColumnNames() as $name) {
// Skip id field.
if ($name == 'id') {
continue;
}
$sourceColumn = $sourceSchema->getColumn($name);
$matches = array();
// Exact match.
if ($targetSchema->getColumn($name)) {
$fieldMap[$name] = $name;
} elseif (preg_match($pattern, $name, $matches)) {
$qid = $matches[3];
$targetColumn = $this->getQidColumn($targetSchema, $qid);
if (isset($targetColumn)) {
$fieldMap[$name] = $targetColumn->name;
}
}
}
$imported = 0;
$sourceResponses = new CDataProviderIterator(new CActiveDataProvider($sourceTable), 500);
foreach ($sourceResponses as $sourceResponse) {
$iOldID = $sourceResponse->id;
// Using plugindynamic model because I dont trust surveydynamic.
$targetResponse = new PluginDynamic("{{survey_{$iSurveyId}}}");
foreach ($fieldMap as $sourceField => $targetField) {
$targetResponse[$targetField] = $sourceResponse[$sourceField];
}
$imported++;
$targetResponse->save();
$aSRIDConversions[$iOldID] = $targetResponse->id;
unset($targetResponse);
}
Yii::app()->session['flashmessage'] = sprintf(gT("%s old response(s) were successfully imported."), $imported);
$sOldTimingsTable = substr(substr($sourceTable->tableName(), 0, strrpos($sourceTable->tableName(), '_')) . '_timings' . substr($sourceTable->tableName(), strrpos($sourceTable->tableName(), '_')), strlen(Yii::app()->db->tablePrefix));
$sNewTimingsTable = "survey_{$surveyid}_timings";
if (isset($_POST['timings']) && $_POST['timings'] == 1 && tableExists($sOldTimingsTable) && tableExists($sNewTimingsTable)) {
// Import timings
$arDestination = SurveyTimingDynamic::model($surveyid);
$aFieldsOldTimingTable = array_values(Yii::app()->db->schema->getTable('{{' . $sOldTimingsTable . '}}')->columnNames);
$aFieldsNewTimingTable = array_values(Yii::app()->db->schema->getTable('{{' . $sNewTimingsTable . '}}')->columnNames);
$aValidTimingFields = array_intersect($aFieldsOldTimingTable, $aFieldsNewTimingTable);
$sQueryOldValues = "SELECT " . implode(", ", $aValidTimingFields) . " FROM {{{$sOldTimingsTable}}} ";
$aQueryOldValues = Yii::app()->db->createCommand($sQueryOldValues)->query()->readAll();
//Checked
$iRecordCountT = 0;
foreach ($aQueryOldValues as $sRecord) {
if (isset($aSRIDConversions[$sRecord['id']])) {
$sRecord['id'] = $aSRIDConversions[$sRecord['id']];
} else {
continue;
}
Yii::app()->db->createCommand()->insert("{{{$sNewTimingsTable}}}", $sRecord);
$iRecordCountT++;
}
Yii::app()->session['flashmessage'] = sprintf(gT("%s old response(s) and according timings were successfully imported."), $imported, $iRecordCountT);
}
$this->getController()->redirect(array("/admin/responses/sa/index/", 'surveyid' => $surveyid));
}
}
}
function XMLImportTimings($sFullFilePath, $iSurveyID, $aFieldReMap = array())
{
Yii::app()->loadHelper('database');
$clang = Yii::app()->lang;
$sXMLdata = file_get_contents($sFullFilePath);
$xml = simplexml_load_string($sXMLdata, 'SimpleXMLElement', LIBXML_NONET);
if ($xml->LimeSurveyDocType != 'Timings') {
$results['error'] = $clang->gT("This is not a valid timings data XML file.");
return $results;
}
$results['responses'] = 0;
$aLanguagesSupported = array();
foreach ($xml->languages->language as $language) {
$aLanguagesSupported[] = (string) $language;
}
$results['languages'] = count($aLanguagesSupported);
// Return if there are no timing records to import
if (!isset($xml->timings->rows)) {
return $results;
}
switchMSSQLIdentityInsert('survey_' . $iSurveyID . '_timings', true);
foreach ($xml->timings->rows->row as $row) {
$insertdata = array();
foreach ($row as $key => $value) {
if ($key[0] == '_') {
$key = substr($key, 1);
}
if (isset($aFieldReMap[substr($key, 0, -4)])) {
$key = $aFieldReMap[substr($key, 0, -4)] . 'time';
}
$insertdata[$key] = (string) $value;
}
$result = SurveyTimingDynamic::model($iSurveyID)->insertRecords($insertdata) or safeDie($clang->gT("Error") . ": Failed to insert data[17]<br />");
$results['responses']++;
}
switchMSSQLIdentityInsert('survey_' . $iSurveyID . '_timings', false);
return $results;
}
public function time($iSurveyID)
{
$aData = $this->_getData(array('iSurveyId' => $iSurveyID));
extract($aData);
$aViewUrls = array();
if ($aData['surveyinfo']['savetimings'] != "Y") {
die;
}
if (Yii::app()->request->getPost('deleteanswer') && Yii::app()->request->getPost('deleteanswer') != '' && Yii::app()->request->getPost('deleteanswer') != 'marked' && Permission::model()->hasSurveyPermission($iSurveyID, 'responses', 'delete')) {
$iResponseID = (int) Yii::app()->request->getPost('deleteanswer');
SurveyDynamic::model($iSurveyID)->deleteByPk($iResponseID);
SurveyTimingDynamic::model($iSurveyID)->deleteByPk($iResponseID);
}
if (Yii::app()->request->getPost('markedresponses') && count(Yii::app()->request->getPost('markedresponses')) > 0) {
if (Yii::app()->request->getPost('deleteanswer') && Yii::app()->request->getPost('deleteanswer') === 'marked' && Permission::model()->hasSurveyPermission($iSurveyID, 'responses', 'delete')) {
foreach (Yii::app()->request->getPost('markedresponses') as $iResponseID) {
$iResponseID = (int) $iResponseID;
SurveyDynamic::model($iSurveyID)->deleteByPk($iResponseID);
SurveyTimingDynamic::model($iSurveyID)->deleteByPk($iResponseID);
}
}
}
$fields = createTimingsFieldMap($iSurveyID, 'full', true, false, $aData['language']);
foreach ($fields as $fielddetails) {
// headers for answer id and time data
if ($fielddetails['type'] == 'id') {
$fnames[] = array($fielddetails['fieldname'], $fielddetails['question']);
}
if ($fielddetails['type'] == 'interview_time') {
$fnames[] = array($fielddetails['fieldname'], gT('Total time'));
}
if ($fielddetails['type'] == 'page_time') {
$fnames[] = array($fielddetails['fieldname'], gT('Group') . ": " . $fielddetails['group_name']);
}
if ($fielddetails['type'] == 'answer_time') {
$fnames[] = array($fielddetails['fieldname'], gT('Question') . ": " . $fielddetails['title']);
}
}
$fncount = count($fnames);
//NOW LETS CREATE A TABLE WITH THOSE HEADINGS
foreach ($fnames as $fn) {
if (!isset($currentgroup)) {
$currentgroup = $fn[1];
$gbc = "oddrow";
}
if ($currentgroup != $fn[1]) {
$currentgroup = $fn[1];
if ($gbc == "oddrow") {
$gbc = "evenrow";
} else {
$gbc = "oddrow";
}
}
}
$aData['fnames'] = $fnames;
$start = Yii::app()->request->getParam('start', 0);
$limit = Yii::app()->request->getParam('limit', 50);
if (!$limit) {
$limit = 50;
}
//LETS COUNT THE DATA
$oCriteria = new CdbCriteria();
$oCriteria->select = 'tid';
$oCriteria->join = "INNER JOIN {{survey_{$iSurveyID}}} s ON t.id=s.id";
$oCriteria->condition = 'submitdate IS NOT NULL';
$dtcount = SurveyTimingDynamic::model($iSurveyID)->count($oCriteria);
// or die("Couldn't get response data");
if ($limit > $dtcount) {
$limit = $dtcount;
}
//NOW LETS SHOW THE DATA
$oCriteria = new CdbCriteria();
$oCriteria->join = "INNER JOIN {{survey_{$iSurveyID}}} s ON t.id=s.id";
$oCriteria->condition = 'submitdate IS NOT NULL';
$oCriteria->order = "s.id " . (Yii::app()->request->getParam('order') == 'desc' ? 'desc' : 'asc');
$oCriteria->offset = $start;
$oCriteria->limit = $limit;
$dtresult = SurveyTimingDynamic::model($iSurveyID)->findAllAsArray($oCriteria);
$dtcount2 = count($dtresult);
$cells = $fncount + 1;
//CONTROL MENUBAR
$last = $start - $limit;
$next = $start + $limit;
$end = $dtcount - $limit;
if ($end < 0) {
$end = 0;
}
if ($last < 0) {
$last = 0;
}
if ($next >= $dtcount) {
$next = $dtcount - $limit;
}
if ($end < 0) {
$end = 0;
}
$aData['sCompletionStateValue'] = incompleteAnsFilterState();
$aData['start'] = $start;
$aData['limit'] = $limit;
$aData['last'] = $last;
$aData['next'] = $next;
$aData['end'] = $end;
$aViewUrls[] = 'browsetimeheader_view';
$aData['fncount'] = $fncount;
$bgcc = 'oddrow';
foreach ($dtresult as $dtrow) {
if ($bgcc == "evenrow") {
$bgcc = "oddrow";
} else {
$bgcc = "evenrow";
}
$browsedatafield = array();
for ($i = 0; $i < $fncount; $i++) {
$browsedatafield[$i] = $dtrow[$fnames[$i][0]];
// seconds -> minutes & seconds
if (strtolower(substr($fnames[$i][0], -4)) == "time") {
$minutes = (int) ($browsedatafield[$i] / 60);
$seconds = $browsedatafield[$i] % 60;
$browsedatafield[$i] = '';
if ($minutes > 0) {
$browsedatafield[$i] .= "{$minutes} min ";
}
$browsedatafield[$i] .= "{$seconds} s";
}
}
$aData['browsedatafield'] = $browsedatafield;
$aData['bgcc'] = $bgcc;
$aData['dtrow'] = $dtrow;
$aViewUrls['browsetimerow_view'][] = $aData;
}
//interview Time statistics
$aData['statistics'] = SurveyTimingDynamic::model($iSurveyId)->statistics();
$aData['num_total_answers'] = SurveyDynamic::model($iSurveyID)->count();
$aData['num_completed_answers'] = SurveyDynamic::model($iSurveyID)->count('submitdate IS NOT NULL');
$aViewUrls[] = 'browsetimefooter_view';
$this->_renderWrappedTemplate('', $aViewUrls, $aData);
}
/**
* Write values to database.
* @param <type> $updatedValues
* @param <boolean> $finished - true if the survey needs to be finalized
*/
private function _UpdateValuesInDatabase($updatedValues, $finished = false)
{
// TODO - now that using $this->updatedValues, may be able to remove local copies of it (unless needed by other sub-systems)
$updatedValues = $this->updatedValues;
$message = '';
if (!$this->surveyOptions['active'] || $this->sPreviewMode) {
return $message;
}
if (!isset($_SESSION[$this->sessid]['srid'])) {
$_SESSION[$this->sessid]['datestamp'] = dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", $this->surveyOptions['timeadjust']);
// Create initial insert row for this record
$today = dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", $this->surveyOptions['timeadjust']);
$sdata = array("startlanguage" => $this->surveyOptions['startlanguage']);
if ($this->surveyOptions['anonymized'] == false) {
$sdata['token'] = $this->surveyOptions['token'];
}
if ($this->surveyOptions['datestamp'] == true) {
$sdata['datestamp'] = $_SESSION[$this->sessid]['datestamp'];
$sdata['startdate'] = $_SESSION[$this->sessid]['datestamp'];
}
if ($this->surveyOptions['ipaddr'] == true) {
$sdata['ipaddr'] = getIPAddress();
}
if ($this->surveyOptions['refurl'] == true) {
if (isset($_SESSION[$this->sessid]['refurl'])) {
$sdata['refurl'] = $_SESSION[$this->sessid]['refurl'];
} else {
$sdata['refurl'] = getenv("HTTP_REFERER");
}
}
$sdata = array_filter($sdata);
SurveyDynamic::sid($this->sid);
$oSurvey = new SurveyDynamic();
$iNewID = $oSurvey->insertRecords($sdata);
if ($iNewID) {
$srid = $iNewID;
$_SESSION[$this->sessid]['srid'] = $iNewID;
} else {
$message .= $this->gT("Unable to insert record into survey table");
// TODO - add SQL error?
echo submitfailed('');
// TODO - report SQL error?
}
//Insert Row for Timings, if needed
if ($this->surveyOptions['savetimings']) {
SurveyTimingDynamic::sid($this->sid);
$oSurveyTimings = new SurveyTimingDynamic();
$tdata = array('id' => $srid, 'interviewtime' => 0);
switchMSSQLIdentityInsert("survey_{$this->sid}_timings", true);
$iNewID = $oSurveyTimings->insertRecords($tdata);
switchMSSQLIdentityInsert("survey_{$this->sid}_timings", false);
}
}
if (count($updatedValues) > 0 || $finished) {
$query = 'UPDATE ' . $this->surveyOptions['tablename'] . ' SET ';
$setter = array();
switch ($this->surveyMode) {
case 'question':
$thisstep = $this->currentQuestionSeq;
break;
case 'group':
$thisstep = $this->currentGroupSeq;
break;
case 'survey':
$thisstep = 1;
break;
}
$setter[] = dbQuoteID('lastpage') . "=" . dbQuoteAll($thisstep);
if ($this->surveyOptions['datestamp'] && isset($_SESSION[$this->sessid]['datestamp'])) {
$_SESSION[$this->sessid]['datestamp'] = dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", $this->surveyOptions['timeadjust']);
$setter[] = dbQuoteID('datestamp') . "=" . dbQuoteAll(dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", $this->surveyOptions['timeadjust']));
}
if ($this->surveyOptions['ipaddr']) {
$setter[] = dbQuoteID('ipaddr') . "=" . dbQuoteAll(getIPAddress());
}
foreach ($updatedValues as $key => $value) {
$val = is_null($value) ? NULL : $value['value'];
$type = is_null($value) ? NULL : $value['type'];
// Clean up the values to cope with database storage requirements
switch ($type) {
case 'D':
//DATE
if (trim($val) == '' || $val == "INVALID") {
$val = NULL;
// since some databases can't store blanks in date fields
}
// otherwise will already be in yyyy-mm-dd format after ProcessCurrentResponses()
break;
case '|':
//File upload
// This block can be removed once we require 5.3 or later
if (function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc()) {
$val = addslashes($val);
}
break;
case 'N':
//NUMERICAL QUESTION TYPE
//NUMERICAL QUESTION TYPE
case 'K':
//MULTIPLE NUMERICAL QUESTION
if (trim($val) == '') {
$val = NULL;
// since some databases can't store blanks in numerical inputs
}
break;
default:
break;
}
if (is_null($val)) {
$setter[] = dbQuoteID($key) . "=NULL";
} else {
$setter[] = dbQuoteID($key) . "=" . dbQuoteAll($val);
}
}
$query .= implode(', ', $setter);
$query .= " WHERE ID=";
if (isset($_SESSION[$this->sessid]['srid']) && $this->surveyOptions['active']) {
$query .= $_SESSION[$this->sessid]['srid'];
if (!dbExecuteAssoc($query)) {
echo submitfailed('');
// TODO - report SQL error?
if (($this->debugLevel & LEM_DEBUG_VALIDATION_SUMMARY) == LEM_DEBUG_VALIDATION_SUMMARY) {
$message .= $this->gT('Error in SQL update');
// TODO - add SQL error?
}
} elseif ($this->surveyOptions['savetimings']) {
Yii::import("application.libraries.Save");
$cSave = new Save();
$cSave->set_answer_time();
}
if ($finished) {
// Delete the save control record if successfully finalize the submission
$query = "DELETE FROM {{saved_control}} where srid=" . $_SESSION[$this->sessid]['srid'] . ' and sid=' . $this->sid;
Yii::app()->db->createCommand($query)->execute();
if (($this->debugLevel & LEM_DEBUG_VALIDATION_SUMMARY) == LEM_DEBUG_VALIDATION_SUMMARY) {
$message .= ';<br />' . $query;
}
} else {
if ($this->surveyOptions['allowsave'] && isset($_SESSION[$this->sessid]['scid'])) {
SavedControl::model()->updateByPk($_SESSION[$this->sessid]['scid'], array('saved_thisstep' => $thisstep));
}
}
// Check Quotas
$aQuotas = checkCompletedQuota($this->sid, 'return');
if ($aQuotas && !empty($aQuotas)) {
checkCompletedQuota($this->sid);
// will create a page and quit: why not use it directly ?
} else {
if ($finished) {
$sQuery = 'UPDATE ' . $this->surveyOptions['tablename'] . " SET ";
if ($this->surveyOptions['datestamp']) {
// Replace with date("Y-m-d H:i:s") ? See timeadjust
$sQuery .= dbQuoteID('submitdate') . "=" . dbQuoteAll(dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", $this->surveyOptions['timeadjust']));
} else {
$sQuery .= dbQuoteID('submitdate') . "=" . dbQuoteAll(date("Y-m-d H:i:s", mktime(0, 0, 0, 1, 1, 1980)));
}
$sQuery .= " WHERE ID=" . $_SESSION[$this->sessid]['srid'];
dbExecuteAssoc($sQuery);
// Checked
}
}
}
if (($this->debugLevel & LEM_DEBUG_VALIDATION_SUMMARY) == LEM_DEBUG_VALIDATION_SUMMARY) {
$message .= $query;
}
}
return $message;
}
/**
* This functions saves the answer time for question/group and whole survey.
* [ It compares current time with the time in $_POST['start_time'] ]
* The times are saved in table: {prefix}{surveytable}_timings
* @return void
*/
function set_answer_time()
{
global $thissurvey;
if (!isset($_POST['start_time'])) {
return;
// means haven't passed welcome page yet.
}
if (isset($_POST['lastanswer'])) {
$setField = $_POST['lastanswer'];
} elseif (isset($_POST['lastgroup'])) {
$setField = $_POST['lastgroup'];
}
$passedTime = str_replace(',', '.', round(microtime(true) - $_POST['start_time'], 2));
if (!isset($setField)) {
//we show the whole survey on one page - we don't have to save time for group/question
$query = "UPDATE {{survey_{$thissurvey['sid']}_timings}} SET " . "interviewtime = (CASE WHEN interviewtime IS NULL THEN 0 ELSE interviewtime END) + " . $passedTime . " WHERE id = " . $_SESSION['survey_' . $thissurvey['sid']]['srid'];
} else {
$aColumnNames = SurveyTimingDynamic::model($thissurvey['sid'])->getTableSchema()->columnNames;
$setField .= "time";
if (!in_array($setField, $aColumnNames)) {
die('Invalid last group timing fieldname');
}
$setField = Yii::app()->db->quoteColumnName($setField);
$query = "UPDATE {{survey_{$thissurvey['sid']}_timings}} SET " . "interviewtime = (CASE WHEN interviewtime IS NULL THEN 0 ELSE interviewtime END) + " . $passedTime . "," . $setField . " = (CASE WHEN {$setField} IS NULL THEN 0 ELSE {$setField} END) + " . $passedTime . " WHERE id = " . $_SESSION['survey_' . $thissurvey['sid']]['srid'];
}
Yii::app()->db->createCommand($query)->execute();
}
/**
* Sets the survey ID for the next model
*
* @static
* @access public
* @param int $sid
* @return void
*/
public static function sid($sid)
{
self::$sid = (int) $sid;
}
