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