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