function XMLImportTimings($sFullFilepath, $iSurveyID, $aFieldReMap = array())
{
Yii::app()->loadHelper('database');
$clang = Yii::app()->lang;
$xml = simplexml_load_file($sFullFilepath);
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);
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 = Survey_timings::model($iSurveyID)->insertRecords($insertdata) or safeDie($clang->gT("Error") . ": Failed to insert data<br />");
$results['responses']++;
}
switchMSSQLIdentityInsert('survey_' . $iSurveyID . '_timings', false);
return $results;
}
/**
* 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 = '';
$_SESSION[$this->sessid]['datestamp'] = dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", $this->surveyOptions['timeadjust']);
if ($this->surveyOptions['active'] && !isset($_SESSION[$this->sessid]['srid'])) {
// 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 = array_merge($sdata, array("token" => $this->surveyOptions['token']));
}
if ($this->surveyOptions['datestamp'] == true) {
$sdata = array_merge($sdata, array("datestamp" => $this->surveyOptions['datestamp'] ? $_SESSION[$this->sessid]['datestamp'] : NULL, "startdate" => $this->surveyOptions['datestamp'] ? $_SESSION[$this->sessid]['datestamp'] : date("Y-m-d H:i:s", 0)));
}
if ($this->surveyOptions['ipaddr'] == true) {
$sdata = array_merge($sdata, array("ipaddr" => getIPAddress()));
}
if ($this->surveyOptions['refurl'] == true) {
$sdata = array_merge($sdata, array("refurl" => $this->surveyOptions['refurl'] ? getenv("HTTP_REFERER") : NULL));
}
$sdata = array_filter($sdata);
Survey_dynamic::sid($this->sid);
$oSurvey = new Survey_dynamic();
$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?
}
//Insert Row for Timings, if needed
if ($this->surveyOptions['savetimings']) {
Survey_timings::sid($this->sid);
$oSurveyTimings = new Survey_timings();
$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'])) {
$setter[] = dbQuoteID('datestamp') . "=" . dbQuoteAll($_SESSION[$this->sessid]['datestamp']);
}
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 = 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'])) {
Saved_control::model()->updateByPk($_SESSION[$this->sessid]['scid'], array('saved_thisstep' => $thisstep));
}
}
// Check Quotas
$bQuotaMatched = false;
$aQuotas = checkQuota('return', $this->sid);
if ($aQuotas !== false) {
if ($aQuotas != false) {
foreach ($aQuotas as $aQuota) {
if (isset($aQuota['status']) && $aQuota['status'] == 'matched') {
$bQuotaMatched = true;
}
}
}
}
if ($bQuotaMatched) {
checkQuota('enforce', $this->sid);
// will create a page and quit.
} 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($_SESSION[$this->sessid]['datestamp']);
} 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;
}
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' && hasSurveyPermission($iSurveyID, 'responses', 'delete')) {
$iResponseID = (int) Yii::app()->request->getPost('deleteanswer');
Survey_dynamic::model($iSurveyID)->deleteByPk($iResponseID);
Survey_timings::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' && hasSurveyPermission($iSurveyID, 'responses', 'delete')) {
foreach (Yii::app()->request->getPost('markedresponses') as $iResponseID) {
$iResponseID = (int) $iResponseID;
Survey_dynamic::model($iSurveyID)->deleteByPk($iResponseID);
Survey_timings::model($iSurveyID)->deleteByPk($iResponseID);
}
}
}
$fields = createTimingsFieldMap($iSurveyID, 'full', true, false, $aData['language']);
$clang = $aData['clang'];
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'], $clang->gT('Total time'));
}
if ($fielddetails['type'] == 'page_time') {
$fnames[] = array($fielddetails['fieldname'], $clang->gT('Group') . ": " . $fielddetails['group_name']);
}
if ($fielddetails['type'] == 'answer_time') {
$fnames[] = array($fielddetails['fieldname'], $clang->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 = Survey_timings::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 = Survey_timings::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'] = Survey_timings::model($iSurveyId)->statistics();
$aData['num_total_answers'] = Survey_dynamic::model($iSurveyID)->count();
$aData['num_completed_answers'] = Survey_dynamic::model($iSurveyID)->count('submitdate IS NOT NULL');
$aViewUrls[] = 'browsetimefooter_view';
$this->_renderWrappedTemplate('', $aViewUrls, $aData);
}
/**
* 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;
}
/**
* 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 = 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 = Survey_timings::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();
}
