/**
* Import survey from an TSV file template that does not require or allow assigning of GID or QID values.
* NOTE: This currently only supports import of one language
* @global type $connect
* @global type $dbprefix
* @global type $clang
* @global type $timeadjust
* @param type $sFullFilepath
* @return type
*
* @author TMSWhite
*/
function TSVImportSurvey($sFullFilepath)
{
$clang = Yii::app()->lang;
$insertdata = array();
$results = array();
$results['error'] = false;
$baselang = 'en';
// TODO set proper default
$encoding = '';
$handle = fopen($sFullFilepath, 'r');
$bom = fread($handle, 2);
rewind($handle);
// Excel tends to save CSV as UTF-16, which PHP does not properly detect
if ($bom === chr(0xff) . chr(0xfe) || $bom === chr(0xfe) . chr(0xff)) {
// UTF16 Byte Order Mark present
$encoding = 'UTF-16';
} else {
$file_sample = fread($handle, 1000) + 'e';
//read first 1000 bytes
// + e is a workaround for mb_string bug
rewind($handle);
$encoding = mb_detect_encoding($file_sample, 'UTF-8, UTF-7, ASCII, EUC-JP,SJIS, eucJP-win, SJIS-win, JIS, ISO-2022-JP');
}
if ($encoding && $encoding != 'UTF-8') {
stream_filter_append($handle, 'convert.iconv.' . $encoding . '/UTF-8');
}
$file = stream_get_contents($handle);
fclose($handle);
// fix Excel non-breaking space
$file = str_replace("0xC20xA0", ' ', $file);
$filelines = explode("\n", $file);
$row = array_shift($filelines);
$headers = explode("\t", $row);
$rowheaders = array();
foreach ($headers as $header) {
$rowheaders[] = trim($header);
}
// remove BOM from the first header cell, if needed
$rowheaders[0] = preg_replace("/^\\W+/", "", $rowheaders[0]);
if (preg_match('/class$/', $rowheaders[0])) {
$rowheaders[0] = 'class';
// second attempt to remove BOM
}
$adata = array();
foreach ($filelines as $rowline) {
$rowarray = array();
$row = explode("\t", $rowline);
for ($i = 0; $i < count($rowheaders); ++$i) {
$val = isset($row[$i]) ? $row[$i] : '';
// if Excel was used, it surrounds strings with quotes and doubles internal double quotes. Fix that.
if (preg_match('/^".*"$/', $val)) {
$val = str_replace('""', '"', substr($val, 1, -1));
}
$rowarray[$rowheaders[$i]] = $val;
}
$adata[] = $rowarray;
}
$results['defaultvalues'] = 0;
$results['answers'] = 0;
$results['surveys'] = 0;
$results['languages'] = 0;
$results['questions'] = 0;
$results['subquestions'] = 0;
$results['question_attributes'] = 0;
$results['groups'] = 0;
$results['importwarnings'] = array();
// these aren't used here, but are needed to avoid errors in post-import display
$results['assessments'] = 0;
$results['quota'] = 0;
$results['quotamembers'] = 0;
$results['quotals'] = 0;
// collect information about survey and its language settings
$surveyinfo = array();
$surveyls = array();
foreach ($adata as $row) {
switch ($row['class']) {
case 'S':
if (isset($row['text']) && $row['name'] != 'datecreated') {
$surveyinfo[$row['name']] = $row['text'];
}
break;
case 'SL':
if (!isset($surveyls[$row['language']])) {
$surveyls[$row['language']] = array();
}
if (isset($row['text'])) {
$surveyls[$row['language']][$row['name']] = $row['text'];
}
break;
}
}
$iOldSID = 1;
if (isset($surveyinfo['sid'])) {
$iOldSID = (int) $surveyinfo['sid'];
}
// Create the survey entry
$surveyinfo['startdate'] = NULL;
$surveyinfo['active'] = 'N';
// unset($surveyinfo['datecreated']);
switchMSSQLIdentityInsert('surveys', true);
$iNewSID = Survey::model()->insertNewSurvey($surveyinfo);
//or safeDie($clang->gT("Error").": Failed to insert survey<br />");
if ($iNewSID == false) {
$results['error'] = Survey::model()->getErrors();
$results['bFailed'] = true;
return $results;
}
$surveyinfo['sid'] = $iNewSID;
$results['surveys']++;
switchMSSQLIdentityInsert('surveys', false);
$results['newsid'] = $iNewSID;
$gid = 0;
$gseq = 0;
// group_order
$qid = 0;
$qseq = 0;
// question_order
$qtype = 'T';
$aseq = 0;
// answer sortorder
// set the language for the survey
$_title = 'Missing Title';
foreach ($surveyls as $_lang => $insertdata) {
$insertdata['surveyls_survey_id'] = $iNewSID;
$insertdata['surveyls_language'] = $_lang;
if (isset($insertdata['surveyls_title'])) {
$_title = $insertdata['surveyls_title'];
} else {
$insertdata['surveyls_title'] = $_title;
}
$result = Surveys_languagesettings::model()->insertNewSurvey($insertdata);
//
if (!$result) {
$results['error'][] = $clang->gT("Error") . " : " . $clang->gT("Failed to insert survey language");
break;
}
$results['languages']++;
}
$ginfo = array();
$qinfo = array();
$sqinfo = array();
if (isset($surveyinfo['language'])) {
$baselang = $surveyinfo['language'];
// the base language
}
$rownumber = 1;
foreach ($adata as $row) {
$rownumber += 1;
switch ($row['class']) {
case 'G':
// insert group
$insertdata = array();
$insertdata['sid'] = $iNewSID;
$gname = isset($row['name']) ? $row['name'] : 'G' . $gseq;
$insertdata['group_name'] = $gname;
$insertdata['grelevance'] = isset($row['relevance']) ? $row['relevance'] : '';
$insertdata['description'] = isset($row['text']) ? $row['text'] : '';
$insertdata['language'] = isset($row['language']) ? $row['language'] : $baselang;
// For multi numeric survey : same title
if (isset($ginfo[$gname])) {
$gseq = $ginfo[$gname]['group_order'];
$gid = $ginfo[$gname]['gid'];
$insertdata['gid'] = $gid;
$insertdata['group_order'] = $gseq;
} else {
$insertdata['group_order'] = $gseq;
}
$newgid = Groups::model()->insertRecords($insertdata);
if (!$newgid) {
$results['error'][] = $clang->gT("Error") . " : " . $clang->gT("Failed to insert group") . ". " . $clang->gT("Text file row number ") . $rownumber . " (" . $gname . ")";
break;
}
if (!isset($ginfo[$gname])) {
$results['groups']++;
$gid = $newgid;
// save this for later
$ginfo[$gname]['gid'] = $gid;
$ginfo[$gname]['group_order'] = $gseq++;
}
$qseq = 0;
// reset the question_order
break;
case 'Q':
// insert question
$insertdata = array();
$insertdata['sid'] = $iNewSID;
$qtype = isset($row['type/scale']) ? $row['type/scale'] : 'T';
$qname = isset($row['name']) ? $row['name'] : 'Q' . $qseq;
$insertdata['gid'] = $gid;
$insertdata['type'] = $qtype;
$insertdata['title'] = $qname;
$insertdata['question'] = isset($row['text']) ? $row['text'] : '';
$insertdata['relevance'] = isset($row['relevance']) ? $row['relevance'] : '';
$insertdata['preg'] = isset($row['validation']) ? $row['validation'] : '';
$insertdata['help'] = isset($row['help']) ? $row['help'] : '';
$insertdata['language'] = isset($row['language']) ? $row['language'] : $baselang;
$insertdata['mandatory'] = isset($row['mandatory']) ? $row['mandatory'] : '';
$insertdata['other'] = isset($row['other']) ? $row['other'] : 'N';
$insertdata['same_default'] = isset($row['same_default']) ? $row['same_default'] : 0;
$insertdata['parent_qid'] = 0;
// For multi numeric survey : same name, add the gid to have same name on different gid. Bad for EM.
$fullqname = "G{$gid}_" . $qname;
if (isset($qinfo[$fullqname])) {
$qseq = $qinfo[$fullqname]['question_order'];
$qid = $qinfo[$fullqname]['qid'];
$insertdata['qid'] = $qid;
$insertdata['question_order'] = $qseq;
} else {
$insertdata['question_order'] = $qseq;
}
// Insert question and keep the qid for multi language survey
$result = Questions::model()->insertRecords($insertdata);
if (!$result) {
$results['error'][] = $clang->gT("Error") . " : " . $clang->gT("Could not insert question") . ". " . $clang->gT("Text file row number ") . $rownumber . " (" . $qname . ")";
break;
}
$newqid = $result;
if (!isset($qinfo[$fullqname])) {
$results['questions']++;
$qid = $newqid;
// save this for later
$qinfo[$fullqname]['qid'] = $qid;
$qinfo[$fullqname]['question_order'] = $qseq++;
}
$aseq = 0;
//reset the answer sortorder
$sqseq = 0;
//reset the sub question sortorder
// insert question attributes
foreach ($row as $key => $val) {
switch ($key) {
case 'class':
case 'type/scale':
case 'name':
case 'text':
case 'validation':
case 'relevance':
case 'help':
case 'language':
case 'mandatory':
case 'other':
case 'same_default':
case 'default':
break;
default:
if ($key != '' && $val != '') {
$insertdata = array();
$insertdata['qid'] = $qid;
$insertdata['language'] = isset($row['language']) ? $row['language'] : $baselang;
$insertdata['attribute'] = $key;
$insertdata['value'] = $val;
$result = Question_attributes::model()->insertRecords($insertdata);
//
if (!$result) {
$results['importwarnings'][] = $clang->gT("Warning") . " : " . $clang->gT("Failed to insert question attribute") . ". " . $clang->gT("Text file row number ") . $rownumber . " ({$key})";
break;
}
$results['question_attributes']++;
}
break;
}
}
// insert default value
if (isset($row['default'])) {
$insertdata = array();
$insertdata['qid'] = $qid;
$insertdata['language'] = isset($row['language']) ? $row['language'] : $baselang;
$insertdata['defaultvalue'] = $row['default'];
$result = Defaultvalues::model()->insertRecords($insertdata);
if (!$result) {
$results['importwarnings'][] = $clang->gT("Warning") . " : " . $clang->gT("Failed to insert default value") . ". " . $clang->gT("Text file row number ") . $rownumber;
break;
}
$results['defaultvalues']++;
}
break;
case 'SQ':
$sqname = isset($row['name']) ? $row['name'] : 'SQ' . $sqseq;
if ($qtype == 'O' || $qtype == '|') {
// these are fake rows to show naming of comment and filecount fields
} elseif ($sqname == 'other' && ($qtype == '!' || $qtype == 'L')) {
// only want to set default value for 'other' in these cases - not a real SQ row
// TODO - this isn't working
if (isset($row['default'])) {
$insertdata = array();
$insertdata['qid'] = $qid;
$insertdata['specialtype'] = 'other';
$insertdata['language'] = isset($row['language']) ? $row['language'] : $baselang;
$insertdata['defaultvalue'] = $row['default'];
$result = Defaultvalues::model()->insertRecords($insertdata);
if (!$result) {
$results['importwarnings'][] = $clang->gT("Warning") . " : " . $clang->gT("Failed to insert default value") . ". " . $clang->gT("Text file row number ") . $rownumber;
break;
}
$results['defaultvalues']++;
}
} else {
$insertdata = array();
$scale_id = isset($row['type/scale']) ? $row['type/scale'] : 0;
$insertdata['sid'] = $iNewSID;
$insertdata['gid'] = $gid;
$insertdata['parent_qid'] = $qid;
$insertdata['type'] = $qtype;
$insertdata['title'] = $sqname;
$insertdata['question'] = isset($row['text']) ? $row['text'] : '';
$insertdata['relevance'] = isset($row['relevance']) ? $row['relevance'] : '';
$insertdata['preg'] = isset($row['validation']) ? $row['validation'] : '';
$insertdata['help'] = isset($row['help']) ? $row['help'] : '';
$insertdata['language'] = isset($row['language']) ? $row['language'] : $baselang;
$insertdata['mandatory'] = isset($row['mandatory']) ? $row['mandatory'] : '';
$insertdata['scale_id'] = $scale_id;
// For multi nueric language, qid is needed, why not gid. name is not unique.
$fullsqname = "G{$gid}Q{$qid}_{$sqname}";
if (isset($sqinfo[$fullsqname])) {
$qseq = $sqinfo[$fullsqname]['question_order'];
$sqid = $sqinfo[$fullsqname]['sqid'];
$insertdata['question_order'] = $qseq;
$insertdata['qid'] = $sqid;
} else {
$insertdata['question_order'] = $qseq;
}
// Insert sub question and keep the sqid for multi language survey
$newsqid = Questions::model()->insertRecords($insertdata);
if (!$newsqid) {
$results['error'][] = $clang->gT("Error") . " : " . $clang->gT("Could not insert sub question") . ". " . $clang->gT("Text file row number ") . $rownumber . " (" . $qname . ")";
break;
}
if (!isset($sqinfo[$fullsqname])) {
$sqinfo[$fullsqname]['question_order'] = $qseq++;
$sqid = $newsqid;
// save this for later
$sqinfo[$fullsqname]['sqid'] = $sqid;
$results['subquestions']++;
}
// insert default value
if (isset($row['default'])) {
$insertdata = array();
$insertdata['qid'] = $qid;
$insertdata['sqid'] = $sqid;
$insertdata['scale_id'] = $scale_id;
$insertdata['language'] = isset($row['language']) ? $row['language'] : $baselang;
$insertdata['defaultvalue'] = $row['default'];
$result = Defaultvalues::model()->insertRecords($insertdata);
if (!$result) {
$results['importwarnings'][] = $clang->gT("Warning") . " : " . $clang->gT("Failed to insert default value") . ". " . $clang->gT("Text file row number ") . $rownumber;
break;
}
$results['defaultvalues']++;
}
}
break;
case 'A':
$insertdata = array();
$insertdata['qid'] = $qid;
$insertdata['code'] = isset($row['name']) ? $row['name'] : 'A' . $aseq;
$insertdata['answer'] = isset($row['text']) ? $row['text'] : '';
$insertdata['scale_id'] = isset($row['type/scale']) ? $row['type/scale'] : 0;
$insertdata['language'] = isset($row['language']) ? $row['language'] : $baselang;
$insertdata['assessment_value'] = isset($row['relevance']) ? $row['relevance'] : '';
$insertdata['sortorder'] = ++$aseq;
$result = Answers::model()->insertRecords($insertdata);
// or safeDie("Error: Failed to insert answer<br />");
if (!$result) {
$results['error'][] = $clang->gT("Error") . " : " . $clang->gT("Could not insert answer") . ". " . $clang->gT("Text file row number ") . $rownumber;
}
$results['answers']++;
break;
}
}
// Delete the survey if error found
if (is_array($results['error'])) {
$result = Survey::model()->deleteSurvey($iNewSID);
}
return $results;
}
/**
* Function responsible for deleting a question.
*
* @access public
* @param string $action
* @param int $surveyid
* @param int $gid
* @param int $qid
* @return void
*/
public function delete($surveyid, $gid, $qid)
{
$clang = $this->getController()->lang;
$surveyid = sanitize_int($surveyid);
$gid = sanitize_int($gid);
$qid = sanitize_int($qid);
if (hasSurveyPermission($surveyid, 'surveycontent', 'delete')) {
if (!isset($qid)) {
$qid = returnGlobal('qid');
}
LimeExpressionManager::RevertUpgradeConditionsToRelevance(NULL, $qid);
// Check if any other questions have conditions which rely on this question. Don't delete if there are.
// TMSW Conditions->Relevance: Allow such deletes - can warn about missing relevance separately.
$ccresult = Conditions::model()->findAllByAttributes(array('cqid' => $qid));
$cccount = count($ccresult);
// There are conditions dependent on this question
if ($cccount) {
foreach ($ccresult as $ccr) {
$qidarray[] = $ccr->qid;
}
if (isset($qidarray)) {
$qidlist = implode(", ", $qidarray);
}
$message = $clang->gT("Question could not be deleted. There are conditions for other questions that rely on this question. You cannot delete this question until those conditions are removed.");
$message .= "<br /><a href='" . $this->getController()->createUrl("admin/expressions/sa/survey_logic_file/sid/{$surveyid}") . "' >" . $clang->gT("Look at survey logic files") . "</a>.";
$this->getController()->error($message, $this->getController()->createUrl("admin/survey/sa/view/surveyid/{$surveyid}/gid/{$gid}/qid/{$qid}"));
} else {
$row = Questions::model()->findByAttributes(array('qid' => $qid))->attributes;
$gid = $row['gid'];
// See if there are any conditions/attributes/answers/defaultvalues for this question,
// and delete them now as well
Conditions::model()->deleteAllByAttributes(array('qid' => $qid));
Question_attributes::model()->deleteAllByAttributes(array('qid' => $qid));
Answers::model()->deleteAllByAttributes(array('qid' => $qid));
$criteria = new CDbCriteria();
$criteria->addCondition('qid = :qid1 or parent_qid = :qid2');
$criteria->params[':qid1'] = $qid;
$criteria->params[':qid2'] = $qid;
Questions::model()->deleteAll($criteria);
Defaultvalues::model()->deleteAllByAttributes(array('qid' => $qid));
Quota_members::model()->deleteAllByAttributes(array('qid' => $qid));
Questions::model()->updateQuestionOrder($gid, $surveyid);
$qid = "";
$postqid = "";
$_GET['qid'] = "";
}
Yii::app()->session['flashmessage'] = $clang->gT("Question was successfully deleted.");
$this->getController()->redirect($this->getController()->createUrl('admin/survey/sa/view/surveyid/' . $surveyid . '/gid/' . $gid));
} else {
Yii::app()->session['flashmessage'] = $clang->gT("You are not authorized to delete questions.");
$this->getController()->redirect($this->getController()->createUrl('admin/survey/sa/view/surveyid/' . $surveyid . '/gid/' . $gid));
}
}
/**
* RPC Routine to delete a question of a survey .
* Returns the id of the deleted question.
*
* @access public
* @param string $sSessionKey Auth credentials
* @param int iQuestionID Id of the question to delete
* @return array|int Id of the deleted Question or status
*/
public function delete_question($sSessionKey, $iQuestionID)
{
if ($this->_checkSessionKey($sSessionKey)) {
$oQuestion = Questions::model()->findByAttributes(array('qid' => $iQuestionID));
if (!isset($oQuestion)) {
return array('status' => 'Error: Invalid question ID');
}
$iSurveyID = $oQuestion['sid'];
if (hasSurveyPermission($iSurveyID, 'surveycontent', 'delete')) {
$oSurvey = Survey::model()->findByPk($iSurveyID);
if ($oSurvey['active'] == 'Y') {
return array('status' => 'Survey is active and not editable');
}
$iGroupID = $oQuestion['gid'];
$oCondition = Conditions::model()->findAllByAttributes(array('cqid' => $iQuestionID));
if (count($oCondition) > 0) {
return array('status' => 'Cannot delete Question. Others rely on this question');
}
LimeExpressionManager::RevertUpgradeConditionsToRelevance(NULL, $iQuestionID);
try {
Conditions::model()->deleteAllByAttributes(array('qid' => $iQuestionID));
Question_attributes::model()->deleteAllByAttributes(array('qid' => $iQuestionID));
Answers::model()->deleteAllByAttributes(array('qid' => $iQuestionID));
$sCriteria = new CDbCriteria();
$sCriteria->addCondition('qid = :qid or parent_qid = :qid');
$sCriteria->params[':qid'] = $iQuestionID;
Questions::model()->deleteAll($sCriteria);
Defaultvalues::model()->deleteAllByAttributes(array('qid' => $iQuestionID));
Quota_members::model()->deleteAllByAttributes(array('qid' => $iQuestionID));
Questions::updateSortOrder($iGroupID, $iSurveyID);
return (int) $iQuestionID;
} catch (Exception $e) {
return array('status' => 'Error');
}
} else {
return array('status' => 'No permission');
}
} else {
return array('status' => 'Invalid session key');
}
}
/**
* This function generates an array containing the fieldcode, and matching data in the same order as the activate script
*
* @param string $surveyid The Survey ID
* @param mixed $style 'short' (default) or 'full' - full creates extra information like default values
* @param mixed $force_refresh - Forces to really refresh the array, not just take the session copy
* @param int $questionid Limit to a certain qid only (for question preview) - default is false
* @return array
*/
function createFieldMap($surveyid, $style = 'short', $force_refresh = false, $questionid = false, $sLanguage)
{
global $aDuplicateQIDs;
$sLanguage = sanitize_languagecode($sLanguage);
$surveyid = sanitize_int($surveyid);
$clang = new Limesurvey_lang($sLanguage);
//checks to see if fieldmap has already been built for this page.
if (isset(Yii::app()->session['fieldmap-' . $surveyid . $sLanguage]) && !$force_refresh && $questionid == false) {
return Yii::app()->session['fieldmap-' . $surveyid . $sLanguage];
}
$fieldmap["id"] = array("fieldname" => "id", 'sid' => $surveyid, 'type' => "id", "gid" => "", "qid" => "", "aid" => "");
if ($style == "full") {
$fieldmap["id"]['title'] = "";
$fieldmap["id"]['question'] = $clang->gT("Response ID");
$fieldmap["id"]['group_name'] = "";
}
$fieldmap["submitdate"] = array("fieldname" => "submitdate", 'type' => "submitdate", 'sid' => $surveyid, "gid" => "", "qid" => "", "aid" => "");
if ($style == "full") {
$fieldmap["submitdate"]['title'] = "";
$fieldmap["submitdate"]['question'] = $clang->gT("Date submitted");
$fieldmap["submitdate"]['group_name'] = "";
}
$fieldmap["lastpage"] = array("fieldname" => "lastpage", 'sid' => $surveyid, 'type' => "lastpage", "gid" => "", "qid" => "", "aid" => "");
if ($style == "full") {
$fieldmap["lastpage"]['title'] = "";
$fieldmap["lastpage"]['question'] = $clang->gT("Last page");
$fieldmap["lastpage"]['group_name'] = "";
}
$fieldmap["startlanguage"] = array("fieldname" => "startlanguage", 'sid' => $surveyid, 'type' => "startlanguage", "gid" => "", "qid" => "", "aid" => "");
if ($style == "full") {
$fieldmap["startlanguage"]['title'] = "";
$fieldmap["startlanguage"]['question'] = $clang->gT("Start language");
$fieldmap["startlanguage"]['group_name'] = "";
}
// Select which question IDs have default values
$_aDefaultValues = Defaultvalues::model()->with(array('question' => array('condition' => 'question.sid=' . $surveyid)))->findAll();
$aDefaultValues = array();
foreach ($_aDefaultValues as $k => $v) {
$aDefaultValues[] = $v->qid;
}
//Check for any additional fields for this survey and create necessary fields (token and datestamp and ipaddr)
$prow = Survey::model()->findByPk($surveyid)->getAttributes();
//Checked
if ($prow['anonymized'] == "N") {
$fieldmap["token"] = array("fieldname" => "token", 'sid' => $surveyid, 'type' => "token", "gid" => "", "qid" => "", "aid" => "");
if ($style == "full") {
$fieldmap["token"]['title'] = "";
$fieldmap["token"]['question'] = $clang->gT("Token");
$fieldmap["token"]['group_name'] = "";
}
}
if ($prow['datestamp'] == "Y") {
$fieldmap["startdate"] = array("fieldname" => "startdate", 'type' => "startdate", 'sid' => $surveyid, "gid" => "", "qid" => "", "aid" => "");
if ($style == "full") {
$fieldmap["startdate"]['title'] = "";
$fieldmap["startdate"]['question'] = $clang->gT("Date started");
$fieldmap["startdate"]['group_name'] = "";
}
$fieldmap["datestamp"] = array("fieldname" => "datestamp", 'type' => "datestamp", 'sid' => $surveyid, "gid" => "", "qid" => "", "aid" => "");
if ($style == "full") {
$fieldmap["datestamp"]['title'] = "";
$fieldmap["datestamp"]['question'] = $clang->gT("Date last action");
$fieldmap["datestamp"]['group_name'] = "";
}
}
if ($prow['ipaddr'] == "Y") {
$fieldmap["ipaddr"] = array("fieldname" => "ipaddr", 'type' => "ipaddress", 'sid' => $surveyid, "gid" => "", "qid" => "", "aid" => "");
if ($style == "full") {
$fieldmap["ipaddr"]['title'] = "";
$fieldmap["ipaddr"]['question'] = $clang->gT("IP address");
$fieldmap["ipaddr"]['group_name'] = "";
}
}
// Add 'refurl' to fieldmap.
if ($prow['refurl'] == "Y") {
$fieldmap["refurl"] = array("fieldname" => "refurl", 'type' => "url", 'sid' => $surveyid, "gid" => "", "qid" => "", "aid" => "");
if ($style == "full") {
$fieldmap["refurl"]['title'] = "";
$fieldmap["refurl"]['question'] = $clang->gT("Referrer URL");
$fieldmap["refurl"]['group_name'] = "";
}
}
// Collect all default values once so don't need separate query for each question with defaults
// First collect language specific defaults
$defaultsQuery = "SELECT a.qid, a.sqid, a.scale_id, a.specialtype, a.defaultvalue" . " FROM {{defaultvalues}} as a, {{questions}} as b" . " WHERE a.qid = b.qid" . " AND a.language = b.language" . " AND a.language = '{$sLanguage}'" . " AND b.same_default=0" . " AND b.sid = " . $surveyid;
$defaultResults = Yii::app()->db->createCommand($defaultsQuery)->queryAll();
$defaultValues = array();
// indexed by question then subquestion
foreach ($defaultResults as $dv) {
if ($dv['specialtype'] != '') {
$sq = $dv['specialtype'];
} else {
$sq = $dv['sqid'];
}
$defaultValues[$dv['qid'] . '~' . $sq] = $dv['defaultvalue'];
}
// Now overwrite language-specific defaults (if any) base language values for each question that uses same_defaults=1
$baseLanguage = getBaseLanguageFromSurveyID($surveyid);
$defaultsQuery = "SELECT a.qid, a.sqid, a.scale_id, a.specialtype, a.defaultvalue" . " FROM {{defaultvalues}} as a, {{questions}} as b" . " WHERE a.qid = b.qid" . " AND a.language = b.language" . " AND a.language = '{$baseLanguage}'" . " AND b.same_default=1" . " AND b.sid = " . $surveyid;
$defaultResults = Yii::app()->db->createCommand($defaultsQuery)->queryAll();
foreach ($defaultResults as $dv) {
if ($dv['specialtype'] != '') {
$sq = $dv['specialtype'];
} else {
$sq = $dv['sqid'];
}
$defaultValues[$dv['qid'] . '~' . $sq] = $dv['defaultvalue'];
}
$qtypes = getQuestionTypeList('', 'array');
$aquery = "SELECT * " . " FROM {{questions}} as questions, {{groups}} as groups" . " WHERE questions.gid=groups.gid AND " . " questions.sid={$surveyid} AND " . " questions.language='{$sLanguage}' AND " . " questions.parent_qid=0 AND " . " groups.language='{$sLanguage}' ";
if ($questionid !== false) {
$aquery .= " and questions.qid={$questionid} ";
}
$aquery .= " ORDER BY group_order, question_order";
$aresult = Yii::app()->db->createCommand($aquery)->queryAll();
$questionSeq = -1;
// this is incremental question sequence across all groups
$groupSeq = -1;
$_groupOrder = -1;
foreach ($aresult as $arow) {
++$questionSeq;
// fix fact taht group_order may have gaps
if ($_groupOrder != $arow['group_order']) {
$_groupOrder = $arow['group_order'];
++$groupSeq;
}
// Conditions indicators are obsolete with EM. However, they are so tightly coupled into LS code that easider to just set values to 'N' for now and refactor later.
$conditions = 'N';
$usedinconditions = 'N';
// Field identifier
// GXQXSXA
// G=Group Q=Question S=Subquestion A=Answer Option
// If S or A don't exist then set it to 0
// Implicit (subqestion intermal to a question type ) or explicit qubquestions/answer count starts at 1
// Types "L", "!", "O", "D", "G", "N", "X", "Y", "5", "S", "T", "U"
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}";
if ($qtypes[$arow['type']]['subquestions'] == 0 && $arow['type'] != "R" && $arow['type'] != "|") {
if (isset($fieldmap[$fieldname])) {
$aDuplicateQIDs[$arow['qid']] = array('fieldname' => $fieldname, 'question' => $arow['question'], 'gid' => $arow['gid']);
}
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => "{$arow['type']}", 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => "");
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
if (isset($defaultValues[$arow['qid'] . '~0'])) {
$fieldmap[$fieldname]['defaultvalue'] = $defaultValues[$arow['qid'] . '~0'];
}
}
switch ($arow['type']) {
case "L":
//RADIO LIST
//RADIO LIST
case "!":
//DROPDOWN LIST
if ($arow['other'] == "Y") {
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}other";
if (isset($fieldmap[$fieldname])) {
$aDuplicateQIDs[$arow['qid']] = array('fieldname' => $fieldname, 'question' => $arow['question'], 'gid' => $arow['gid']);
}
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => "other");
// dgk bug fix line above. aid should be set to "other" for export to append to the field name in the header line.
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['subquestion'] = $clang->gT("Other");
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
if (isset($defaultValues[$arow['qid'] . '~other'])) {
$fieldmap[$fieldname]['defaultvalue'] = $defaultValues[$arow['qid'] . '~other'];
}
}
}
break;
case "O":
//DROPDOWN LIST WITH COMMENT
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}comment";
if (isset($fieldmap[$fieldname])) {
$aDuplicateQIDs[$arow['qid']] = array('fieldname' => $fieldname, 'question' => $arow['question'], 'gid' => $arow['gid']);
}
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => "comment");
// dgk bug fix line below. aid should be set to "comment" for export to append to the field name in the header line. Also needed set the type element correctly.
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['subquestion'] = $clang->gT("Comment");
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
}
break;
}
} elseif ($qtypes[$arow['type']]['subquestions'] == 2 && $qtypes[$arow['type']]['answerscales'] == 0) {
//MULTI FLEXI
$abrows = getSubQuestions($surveyid, $arow['qid'], $sLanguage);
//Now first process scale=1
$answerset = array();
$answerList = array();
foreach ($abrows as $key => $abrow) {
if ($abrow['scale_id'] == 1) {
$answerset[] = $abrow;
$answerList[] = array('code' => $abrow['title'], 'answer' => $abrow['question']);
unset($abrows[$key]);
}
}
reset($abrows);
foreach ($abrows as $abrow) {
foreach ($answerset as $answer) {
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}{$abrow['title']}_{$answer['title']}";
if (isset($fieldmap[$fieldname])) {
$aDuplicateQIDs[$arow['qid']] = array('fieldname' => $fieldname, 'question' => $arow['question'], 'gid' => $arow['gid']);
}
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => $abrow['title'] . "_" . $answer['title'], "sqid" => $abrow['qid']);
if ($abrow['other'] == "Y") {
$alsoother = "Y";
}
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['subquestion1'] = $abrow['question'];
$fieldmap[$fieldname]['subquestion2'] = $answer['question'];
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
$fieldmap[$fieldname]['preg'] = $arow['preg'];
$fieldmap[$fieldname]['answerList'] = $answerList;
}
}
}
unset($answerset);
} elseif ($arow['type'] == "1") {
$abrows = getSubQuestions($surveyid, $arow['qid'], $sLanguage);
foreach ($abrows as $abrow) {
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}{$abrow['title']}#0";
if (isset($fieldmap[$fieldname])) {
$aDuplicateQIDs[$arow['qid']] = array('fieldname' => $fieldname, 'question' => $arow['question'], 'gid' => $arow['gid']);
}
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => $abrow['title'], "scale_id" => 0);
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['subquestion'] = $abrow['question'];
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['scale'] = $clang->gT('Scale 1');
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
}
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}{$abrow['title']}#1";
if (isset($fieldmap[$fieldname])) {
$aDuplicateQIDs[$arow['qid']] = array('fieldname' => $fieldname, 'question' => $arow['question'], 'gid' => $arow['gid']);
}
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => $abrow['title'], "scale_id" => 1);
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['subquestion'] = $abrow['question'];
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['scale'] = $clang->gT('Scale 2');
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
}
}
} elseif ($arow['type'] == "R") {
//MULTI ENTRY
$data = Answers::model()->findAllByAttributes(array('qid' => $arow['qid'], 'language' => $sLanguage));
$data = count($data);
$slots = $data;
for ($i = 1; $i <= $slots; $i++) {
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}{$i}";
if (isset($fieldmap[$fieldname])) {
$aDuplicateQIDs[$arow['qid']] = array('fieldname' => $fieldname, 'question' => $arow['question'], 'gid' => $arow['gid']);
}
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => $i);
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['subquestion'] = sprintf($clang->gT('Rank %s'), $i);
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
}
}
} elseif ($arow['type'] == "|") {
$qidattributes = getQuestionAttributeValues($arow['qid']);
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}";
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => '');
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['max_files'] = $qidattributes['max_num_of_files'];
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
}
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}" . "_filecount";
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => "filecount");
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = "filecount - " . $arow['question'];
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
}
} else {
//MULTI ENTRY
$abrows = getSubQuestions($surveyid, $arow['qid'], $sLanguage);
foreach ($abrows as $abrow) {
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}{$abrow['title']}";
if (isset($fieldmap[$fieldname])) {
$aDuplicateQIDs[$arow['qid']] = array('fieldname' => $fieldname, 'question' => $arow['question'], 'gid' => $arow['gid']);
}
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, 'gid' => $arow['gid'], 'qid' => $arow['qid'], 'aid' => $abrow['title'], 'sqid' => $abrow['qid']);
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['subquestion'] = $abrow['question'];
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
$fieldmap[$fieldname]['preg'] = $arow['preg'];
if (isset($defaultValues[$arow['qid'] . '~' . $abrow['qid']])) {
$fieldmap[$fieldname]['defaultvalue'] = $defaultValues[$arow['qid'] . '~' . $abrow['qid']];
}
}
if ($arow['type'] == "P") {
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}{$abrow['title']}comment";
if (isset($fieldmap[$fieldname])) {
$aDuplicateQIDs[$arow['qid']] = array('fieldname' => $fieldname, 'question' => $arow['question'], 'gid' => $arow['gid']);
}
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => $abrow['title'] . "comment");
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['subquestion'] = $clang->gT('Comment');
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
}
}
}
if ($arow['other'] == "Y" && ($arow['type'] == "M" || $arow['type'] == "P")) {
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}other";
if (isset($fieldmap[$fieldname])) {
$aDuplicateQIDs[$arow['qid']] = array('fieldname' => $fieldname, 'question' => $arow['question'], 'gid' => $arow['gid']);
}
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => "other");
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['subquestion'] = $clang->gT('Other');
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
$fieldmap[$fieldname]['other'] = $arow['other'];
}
if ($arow['type'] == "P") {
$fieldname = "{$arow['sid']}X{$arow['gid']}X{$arow['qid']}othercomment";
if (isset($fieldmap[$fieldname])) {
$aDuplicateQIDs[$arow['qid']] = array('fieldname' => $fieldname, 'question' => $arow['question'], 'gid' => $arow['gid']);
}
$fieldmap[$fieldname] = array("fieldname" => $fieldname, 'type' => $arow['type'], 'sid' => $surveyid, "gid" => $arow['gid'], "qid" => $arow['qid'], "aid" => "othercomment");
if ($style == "full") {
$fieldmap[$fieldname]['title'] = $arow['title'];
$fieldmap[$fieldname]['question'] = $arow['question'];
$fieldmap[$fieldname]['subquestion'] = $clang->gT('Other comment');
$fieldmap[$fieldname]['group_name'] = $arow['group_name'];
$fieldmap[$fieldname]['mandatory'] = $arow['mandatory'];
$fieldmap[$fieldname]['hasconditions'] = $conditions;
$fieldmap[$fieldname]['usedinconditions'] = $usedinconditions;
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
$fieldmap[$fieldname]['other'] = $arow['other'];
}
}
}
}
if (isset($fieldmap[$fieldname])) {
$fieldmap[$fieldname]['relevance'] = $arow['relevance'];
$fieldmap[$fieldname]['grelevance'] = $arow['grelevance'];
$fieldmap[$fieldname]['questionSeq'] = $questionSeq;
$fieldmap[$fieldname]['groupSeq'] = $groupSeq;
$fieldmap[$fieldname]['preg'] = $arow['preg'];
$fieldmap[$fieldname]['other'] = $arow['other'];
$fieldmap[$fieldname]['help'] = $arow['help'];
} else {
--$questionSeq;
// didn't generate a valid $fieldmap entry, so decrement the question counter to ensure they are sequential
}
}
if (isset($fieldmap)) {
if ($questionid == false) {
// If the fieldmap was randomized, the master will contain the proper order. Copy that fieldmap with the new language settings.
if (isset(Yii::app()->session['fieldmap-' . $surveyid . '-randMaster'])) {
$masterFieldmap = Yii::app()->session['fieldmap-' . $surveyid . '-randMaster'];
$mfieldmap = Yii::app()->session[$masterFieldmap];
foreach ($mfieldmap as $fieldname => $mf) {
if (isset($fieldmap[$fieldname])) {
$f = $fieldmap[$fieldname];
if (isset($f['question'])) {
$mf['question'] = $f['question'];
}
if (isset($f['subquestion'])) {
$mf['subquestion'] = $f['subquestion'];
}
if (isset($f['subquestion1'])) {
$mf['subquestion1'] = $f['subquestion1'];
}
if (isset($f['subquestion2'])) {
$mf['subquestion2'] = $f['subquestion2'];
}
if (isset($f['group_name'])) {
$mf['group_name'] = $f['group_name'];
}
if (isset($f['answerList'])) {
$mf['answerList'] = $f['answerList'];
}
if (isset($f['defaultvalue'])) {
$mf['defaultvalue'] = $f['defaultvalue'];
}
if (isset($f['help'])) {
$mf['help'] = $f['help'];
}
}
$mfieldmap[$fieldname] = $mf;
}
$fieldmap = $mfieldmap;
}
Yii::app()->session['fieldmap-' . $surveyid . $sLanguage] = $fieldmap;
}
return $fieldmap;
}
}
/**
* Deletes a survey and all its data
*
* @access public
* @param int $iSurveyID
* @param bool @recursive
* @return void
*/
public function deleteSurvey($iSurveyID, $recursive = true)
{
Survey::model()->deleteByPk($iSurveyID);
if ($recursive == true) {
if (tableExists("{{survey_" . intval($iSurveyID) . "}}")) {
Yii::app()->db->createCommand()->dropTable("{{survey_" . intval($iSurveyID) . "}}");
}
if (tableExists("{{survey_" . intval($iSurveyID) . "_timings}}")) {
Yii::app()->db->createCommand()->dropTable("{{survey_" . intval($iSurveyID) . "_timings}}");
}
if (tableExists("{{tokens_" . intval($iSurveyID) . "}}")) {
Yii::app()->db->createCommand()->dropTable("{{tokens_" . intval($iSurveyID) . "}}");
}
$oResult = Questions::model()->findAllByAttributes(array('sid' => $iSurveyID));
foreach ($oResult as $aRow) {
Answers::model()->deleteAllByAttributes(array('qid' => $aRow['qid']));
Conditions::model()->deleteAllByAttributes(array('qid' => $aRow['qid']));
Question_attributes::model()->deleteAllByAttributes(array('qid' => $aRow['qid']));
Defaultvalues::model()->deleteAllByAttributes(array('qid' => $aRow['qid']));
}
Questions::model()->deleteAllByAttributes(array('sid' => $iSurveyID));
Assessment::model()->deleteAllByAttributes(array('sid' => $iSurveyID));
Groups::model()->deleteAllByAttributes(array('sid' => $iSurveyID));
Surveys_languagesettings::model()->deleteAllByAttributes(array('surveyls_survey_id' => $iSurveyID));
Survey_permissions::model()->deleteAllByAttributes(array('sid' => $iSurveyID));
Saved_control::model()->deleteAllByAttributes(array('sid' => $iSurveyID));
Survey_url_parameters::model()->deleteAllByAttributes(array('sid' => $iSurveyID));
Quota::model()->deleteQuota(array('sid' => $iSurveyID), true);
}
}
/**
* This is a convenience function to update/delete answer default values. If the given
* $defaultvalue is empty then the entry is removed from table defaultvalues
*
* @param mixed $qid Question ID
* @param mixed $scale_id Scale ID
* @param mixed $specialtype Special type (i.e. for 'Other')
* @param mixed $language Language (defaults are language specific)
* @param mixed $defaultvalue The default value itself
* @param boolean $ispost If defaultvalue is from a $_POST set this to true to properly quote things
*/
function _updateDefaultValues($qid, $sqid, $scale_id, $specialtype, $language, $defaultvalue, $ispost)
{
if ($defaultvalue == '') {
Defaultvalues::model()->deleteByPk(array('sqid' => $sqid, 'qid' => $qid, 'specialtype' => $specialtype, 'scale_id' => $scale_id, 'language' => $language));
} else {
$arDefaultValue = Defaultvalues::model()->findByPk(array('sqid' => $sqid, 'qid' => $qid, 'specialtype' => $specialtype, 'scale_id' => $scale_id, 'language' => $language));
if (is_null($arDefaultValue)) {
$data = array('sqid' => $sqid, 'qid' => $qid, 'specialtype' => $specialtype, 'scale_id' => $scale_id, 'language' => $language, 'defaultvalue' => $defaultvalue);
Defaultvalues::model()->insertRecords($data);
} else {
Defaultvalues::model()->updateByPk(array('sqid' => $sqid, 'qid' => $qid, 'specialtype' => $specialtype, 'scale_id' => $scale_id, 'language' => $language), array('defaultvalue' => $defaultvalue));
}
}
}
/**
* This function checks the LimeSurvey database for logical consistency and returns an according array
* containing all issues in the particular tables.
* @returns Array with all found issues.
*/
protected function _checkintegrity()
{
$clang = Yii::app()->lang;
/*** Plainly delete survey permissions if the survey or user does not exist ***/
$users = User::model()->findAll();
$uids = array();
foreach ($users as $user) {
$uids[] = $user['uid'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('uid', $uids, 'OR');
$surveys = Survey::model()->findAll();
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$criteria->addNotInCondition('sid', $sids, 'OR');
Survey_permissions::model()->deleteAll($criteria);
// Deactivate surveys that have a missing response table
foreach ($surveys as $survey) {
if ($survey['active'] == 'Y' && !tableExists("{{survey_{$survey['sid']}}}")) {
Survey::model()->updateByPk($survey['sid'], array('active' => 'N'));
}
}
// Fix subquestions
fixSubquestions();
/*** Check for active survey tables with missing survey entry and rename them ***/
$sDBPrefix = Yii::app()->db->tablePrefix;
$sQuery = dbSelectTablesLike('{{survey}}\\_%');
$aResult = dbQueryOrFalse($sQuery) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />");
foreach ($aResult->readAll() as $aRow) {
$sTableName = substr(reset($aRow), strlen($sDBPrefix));
if ($sTableName == 'survey_permissions' || $sTableName == 'survey_links' || $sTableName == 'survey_url_parameters') {
continue;
}
$aTableName = explode('_', $sTableName);
if (isset($aTableName[1]) && ctype_digit($aTableName[1])) {
$iSurveyID = $aTableName[1];
if (!in_array($iSurveyID, $sids)) {
$sDate = date('YmdHis') . rand(1, 1000);
$sOldTable = "survey_{$iSurveyID}";
$sNewTable = "old_survey_{$iSurveyID}_{$sDate}";
try {
$deactivateresult = Yii::app()->db->createCommand()->renameTable("{{{$sOldTable}}}", "{{{$sNewTable}}}");
} catch (CDbException $e) {
die('Couldn\'t make backup of the survey table. Please try again. The database reported the following error:<br />' . htmlspecialchars($e) . '<br />');
}
}
}
}
/*** Check for active token tables with missing survey entry ***/
$aResult = dbQueryOrFalse(dbSelectTablesLike('{{tokens}}\\_%')) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />");
foreach ($aResult->readAll() as $aRow) {
$sTableName = substr(reset($aRow), strlen($sDBPrefix));
$iSurveyID = substr($sTableName, strpos($sTableName, '_') + 1);
if (!in_array($iSurveyID, $sids)) {
$sDate = date('YmdHis') . rand(1, 1000);
$sOldTable = "tokens_{$iSurveyID}";
$sNewTable = "old_tokens_{$iSurveyID}_{$sDate}";
try {
$deactivateresult = Yii::app()->db->createCommand()->renameTable("{{{$sOldTable}}}", "{{{$sNewTable}}}");
} catch (CDbException $e) {
die('Couldn\'t make backup of the survey table. Please try again. The database reported the following error:<br />' . htmlspecialchars($e) . '<br />');
}
}
}
/**********************************************************************/
/* Check conditions */
/**********************************************************************/
// TMSW Conditions->Relevance: Replace this with analysis of relevance
$conditions = Conditions::model()->findAll();
if (Conditions::model()->hasErrors()) {
safeDie(Conditions::model()->getError());
}
$okQuestion = array();
foreach ($conditions as $condition) {
if ($condition['cqid'] != 0) {
// skip case with cqid=0 for codnitions on {TOKEN:EMAIL} for instance
if (!array_key_exists($condition['cqid'], $okQuestion)) {
$iRowCount = Questions::model()->countByAttributes(array('qid' => $condition['cqid']));
if (Questions::model()->hasErrors()) {
safeDie(Questions::model()->getError());
}
if (!$iRowCount) {
$aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No matching CQID'));
} else {
$okQuestion[$condition['cqid']] = $condition['cqid'];
}
}
}
if ($condition['cfieldname']) {
if (preg_match('/^\\+{0,1}[0-9]+X[0-9]+X*$/', $condition['cfieldname'])) {
// only if cfieldname isn't Tag such as {TOKEN:EMAIL} or any other token
list($surveyid, $gid, $rest) = explode('X', $condition['cfieldname']);
$iRowCount = count(Groups::model()->findAllByAttributes(array('gid' => $gid)));
if (Groups::model()->hasErrors()) {
safeDie(Groups::model()->getError());
}
if (!$iRowCount) {
$aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No matching CFIELDNAME group!') . " ({$gid}) ({$condition['cfieldname']})");
}
}
} elseif (!$condition['cfieldname']) {
$aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No CFIELDNAME field set!') . " ({$condition['cfieldname']})");
}
}
/**********************************************************************/
/* Check question attributes */
/**********************************************************************/
$question_attributes = Question_attributes::model()->findAllBySql('select qid from {{question_attributes}} where qid not in (select qid from {{questions}})');
if (Question_attributes::model()->hasErrors()) {
safeDie(Question_attributes::model()->getError());
}
foreach ($question_attributes as $question_attribute) {
$aDelete['questionattributes'][] = array('qid' => $question_attribute['qid']);
}
// foreach
/**********************************************************************/
/* Check default values */
/**********************************************************************/
$questions = Questions::model()->findAll();
if (Questions::model()->hasErrors()) {
safeDie(Questions::model()->getError());
}
$qids = array();
foreach ($questions as $question) {
$qids[] = $question['qid'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('qid', $qids);
$aDelete['defaultvalues'] = count(Defaultvalues::model()->findAll($criteria));
if (Defaultvalues::model()->hasErrors()) {
safeDie(Defaultvalues::model()->getError());
}
/**********************************************************************/
/* Check quotas */
/**********************************************************************/
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('sid', $sids);
$aDelete['quotas'] = count(Quota::model()->findAll($criteria));
if (Quota::model()->hasErrors()) {
safeDie(Quota::model()->getError());
}
/**********************************************************************/
/* Check quota languagesettings */
/**********************************************************************/
$quotas = Quota::model()->findAll();
if (Quota::model()->hasErrors()) {
safeDie(Quota::model()->getError());
}
$ids = array();
foreach ($quotas as $quota) {
$ids[] = $quota['id'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('quotals_quota_id', $ids);
$aDelete['quotals'] = count(Quota_languagesettings::model()->findAll($criteria));
if (Quota_languagesettings::model()->hasErrors()) {
safeDie(Quota_languagesettings::model()->getError());
}
/**********************************************************************/
/* Check quota members */
/**********************************************************************/
$quotas = Quota::model()->findAll();
$quota_ids = array();
foreach ($quotas as $quota) {
$quota_ids[] = $quota['id'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('quota_id', $quota_ids);
$questions = Questions::model()->findAll();
$qids = array();
foreach ($questions as $question) {
$qids[] = $question['qid'];
}
$criteria->addNotInCondition('qid', $qids, 'OR');
$surveys = Survey::model()->findAll();
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$criteria->addNotInCondition('sid', $sids, 'OR');
$aDelete['quotamembers'] = count(Quota_members::model()->findAll($criteria));
if (Quota_members::model()->hasErrors()) {
safeDie(Quota_members::model()->getError());
}
/**********************************************************************/
/* Check assessments */
/**********************************************************************/
$criteria = new CDbCriteria();
$criteria->compare('scope', 'T');
$assessments = Assessment::model()->findAll($criteria);
if (Assessment::model()->hasErrors()) {
safeDie(Assessment::model()->getError());
}
foreach ($assessments as $assessment) {
$iAssessmentCount = count(Survey::model()->findAllByPk($assessment['sid']));
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
if (!$iAssessmentCount) {
$aDelete['assessments'][] = array('id' => $assessment['id'], 'assessment' => $assessment['name'], 'reason' => $clang->gT('No matching survey'));
}
}
$criteria = new CDbCriteria();
$criteria->compare('scope', 'G');
$assessments = Assessment::model()->findAll($criteria);
if (Assessment::model()->hasErrors()) {
safeDie(Assessment::model()->getError());
}
foreach ($assessments as $assessment) {
$iAssessmentCount = count(Groups::model()->findAllByPk(array('gid' => $assessment['gid'], 'language' => $assessment['language'])));
if (Groups::model()->hasErrors()) {
safeDie(Groups::model()->getError());
}
if (!$iAssessmentCount) {
$aDelete['assessments'][] = array('id' => $assessment['id'], 'assessment' => $assessment['name'], 'reason' => $clang->gT('No matching group'));
}
}
/**********************************************************************/
/* Check answers */
/**********************************************************************/
$answers = Answers::model()->findAll();
if (Answers::model()->hasErrors()) {
safeDie(Answers::model()->getError());
}
$okQuestion = array();
foreach ($answers as $answer) {
if (!array_key_exists($answer['qid'], $okQuestion)) {
$iAnswerCount = Questions::model()->countByAttributes(array('qid' => $answer['qid']));
if (Questions::model()->hasErrors()) {
safeDie(Questions::model()->getError());
}
if (!$iAnswerCount) {
$aDelete['answers'][] = array('qid' => $answer['qid'], 'code' => $answer['code'], 'reason' => $clang->gT('No matching question'));
} else {
$okQuestion[$answer['qid']] = $answer['qid'];
}
}
}
/***************************************************************************/
/* Check survey languagesettings and restore them if they don't exist */
/***************************************************************************/
$surveys = Survey::model()->findAll();
foreach ($surveys as $survey) {
$aLanguages = $survey->additionalLanguages;
$aLanguages[] = $survey->language;
foreach ($aLanguages as $langname) {
if ($langname) {
$oLanguageSettings = Surveys_languagesettings::model()->find('surveyls_survey_id=:surveyid AND surveyls_language=:langname', array(':surveyid' => $survey->sid, ':langname' => $langname));
if (!$oLanguageSettings) {
$oLanguageSettings = new Surveys_languagesettings();
$languagedetails = getLanguageDetails($langname);
$insertdata = array('surveyls_survey_id' => $survey->sid, 'surveyls_language' => $langname, 'surveyls_title' => '', 'surveyls_dateformat' => $languagedetails['dateformat']);
foreach ($insertdata as $k => $v) {
$oLanguageSettings->{$k} = $v;
}
$usresult = $oLanguageSettings->save();
}
}
}
}
/**********************************************************************/
/* Check survey language settings */
/**********************************************************************/
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('surveyls_survey_id', $sids);
$surveys_languagesettings = Surveys_languagesettings::model()->findAll($criteria);
if (Surveys_languagesettings::model()->hasErrors()) {
safeDie(Surveys_languagesettings::model()->getError());
}
foreach ($surveys_languagesettings as $surveys_languagesetting) {
$aDelete['surveylanguagesettings'][] = array('slid' => $surveys_languagesetting['surveyls_survey_id'], 'reason' => $clang->gT('The related survey is missing.'));
}
/**********************************************************************/
/* Check questions */
/**********************************************************************/
$questions = Questions::model()->findAll();
if (Questions::model()->hasErrors()) {
safeDie(Questions::model()->getError());
}
$groups = Groups::model()->findAll();
if (Groups::model()->hasErrors()) {
safeDie(Groups::model()->getError());
}
$gids = array();
foreach ($groups as $group) {
$gids[] = $group['gid'];
}
foreach ($questions as $question) {
//Make sure the group exists
if (!in_array($question['gid'], $gids)) {
$aDelete['questions'][] = array('qid' => $question['qid'], 'reason' => $clang->gT('No matching group') . " ({$question['gid']})");
}
//Make sure survey exists
if (!in_array($question['sid'], $sids)) {
$aDelete['questions'][] = array('qid' => $question['qid'], 'reason' => $clang->gT('There is no matching survey.') . " ({$question['sid']})");
}
}
/**********************************************************************/
/* Check groups */
/**********************************************************************/
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('sid', $sids);
$groups = Groups::model()->findAll($criteria);
foreach ($groups as $group) {
$aDelete['groups'][] = array('gid' => $group['gid'], 'reason' => $clang->gT('There is no matching survey.') . ' SID:' . $group['sid']);
}
/**********************************************************************/
/* Check old survey tables */
/**********************************************************************/
//1: Get list of 'old_survey' tables and extract the survey id
//2: Check if that survey id still exists
//3: If it doesn't offer it for deletion
$sQuery = dbSelectTablesLike('{{old_survey}}%');
$aResult = dbQueryOrFalse($sQuery) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />");
$aTables = $aResult->readAll();
$aOldSIDs = array();
$aSIDs = array();
foreach ($aTables as $sTable) {
$sTable = reset($sTable);
list($sOldText, $SurveyText, $iSurveyID, $sDate) = explode('_', substr($sTable, strlen($sDBPrefix)));
$aOldSIDs[] = $iSurveyID;
$aFullOldSIDs[$iSurveyID][] = $sTable;
}
$aOldSIDs = array_unique($aOldSIDs);
//$sQuery = 'SELECT sid FROM {{surveys}} ORDER BY sid';
//$oResult = dbExecuteAssoc($sQuery) or safeDie('Couldn\'t get unique survey ids');
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$aSIDs = array();
foreach ($surveys as $survey) {
$aSIDs[] = $survey['sid'];
}
foreach ($aOldSIDs as $iOldSID) {
if (!in_array($iOldSID, $aSIDs)) {
foreach ($aFullOldSIDs[$iOldSID] as $sTableName) {
$aDelete['orphansurveytables'][] = $sTableName;
}
} else {
foreach ($aFullOldSIDs[$iOldSID] as $sTableName) {
$aTableParts = explode('_', substr($sTableName, strlen($sDBPrefix)));
if (count($aTableParts) == 4) {
$sOldText = $aTableParts[0];
$SurveyText = $aTableParts[1];
$iSurveyID = $aTableParts[2];
$sDateTime = $aTableParts[3];
$sType = $clang->gT('responses');
} elseif (count($aTableParts) == 5) {
//This is a timings table (
$sOldText = $aTableParts[0];
$SurveyText = $aTableParts[1];
$iSurveyID = $aTableParts[2];
$sDateTime = $aTableParts[4];
$sType = $clang->gT('timings');
}
$iYear = substr($sDateTime, 0, 4);
$iMonth = substr($sDateTime, 4, 2);
$iDay = substr($sDateTime, 6, 2);
$iHour = substr($sDateTime, 8, 2);
$iMinute = substr($sDateTime, 10, 2);
$sDate = date('d M Y H:i', mktime($iHour, $iMinute, 0, $iMonth, $iDay, $iYear));
$sQuery = 'SELECT count(*) as recordcount FROM ' . $sTableName;
$aFirstRow = Yii::app()->db->createCommand($sQuery)->queryRow();
if ($aFirstRow['recordcount'] == 0) {
// empty table - so add it to immediate deletion
$aDelete['orphansurveytables'][] = $sTableName;
} else {
$aOldSurveyTableAsk[] = array('table' => $sTableName, 'details' => sprintf($clang->gT('Survey ID %d saved at %s containing %d record(s) (%s)'), $iSurveyID, $sDate, $aFirstRow['recordcount'], $sType));
}
}
}
}
/**********************************************************************/
/* CHECK OLD TOKEN TABLES */
/**********************************************************************/
//1: Get list of 'old_token' tables and extract the survey id
//2: Check if that survey id still exists
//3: If it doesn't offer it for deletion
$aResult = dbQueryOrFalse(dbSelectTablesLike('{{old_token}}%')) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />");
$aTables = $aResult->readAll();
$aOldTokenSIDs = array();
$aTokenSIDs = array();
$aFullOldTokenSIDs = array();
foreach ($aTables as $sTable) {
$sTable = reset($sTable);
list($sOldText, $SurveyText, $iSurveyID, $sDateTime) = explode('_', substr($sTable, strlen($sDBPrefix)));
$aTokenSIDs[] = $iSurveyID;
$aFullOldTokenSIDs[$iSurveyID][] = $sTable;
}
$aOldTokenSIDs = array_unique($aTokenSIDs);
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$aSIDs = array();
foreach ($surveys as $survey) {
$aSIDs[] = $survey['sid'];
}
foreach ($aOldTokenSIDs as $iOldTokenSID) {
if (!in_array($iOldTokenSID, $aOldTokenSIDs)) {
foreach ($aFullOldTokenSIDs[$iOldTokenSID] as $sTableName) {
$aDelete['orphantokentables'][] = $sTableName;
}
} else {
foreach ($aFullOldTokenSIDs[$iOldTokenSID] as $sTableName) {
list($sOldText, $sTokensText, $iSurveyID, $sDateTime) = explode('_', substr($sTableName, strlen($sDBPrefix)));
$iYear = substr($sDateTime, 0, 4);
$iMonth = substr($sDateTime, 4, 2);
$iDay = substr($sDateTime, 6, 2);
$iHour = substr($sDateTime, 8, 2);
$iMinute = substr($sDateTime, 10, 2);
$sDate = date('D, d M Y h:i a', mktime($iHour, $iMinute, 0, $iMonth, $iDay, $iYear));
$sQuery = 'SELECT count(*) as recordcount FROM ' . $sTableName;
$aFirstRow = Yii::app()->db->createCommand($sQuery)->queryRow();
if ($aFirstRow['recordcount'] == 0) {
// empty table - so add it to immediate deletion
$aDelete['orphantokentables'][] = $sTableName;
} else {
$aOldTokenTableAsk[] = array('table' => $sTableName, 'details' => sprintf($clang->gT('Survey ID %d saved at %s containing %d record(s)'), $iSurveyID, $sDate, $aFirstRow['recordcount']));
}
}
}
}
if ($aDelete['defaultvalues'] == 0 && $aDelete['quotamembers'] == 0 && $aDelete['quotas'] == 0 && $aDelete['quotals'] == 0 && count($aDelete) == 4) {
$aDelete['integrityok'] = true;
} else {
$aDelete['integrityok'] = false;
}
if (!isset($aOldTokenTableAsk) && !isset($aOldSurveyTableAsk)) {
$aDelete['redundancyok'] = true;
} else {
$aDelete['redundancyok'] = false;
$aDelete['redundanttokentables'] = array();
$aDelete['redundantsurveytables'] = array();
if (isset($aOldTokenTableAsk)) {
$aDelete['redundanttokentables'] = $aOldTokenTableAsk;
}
if (isset($aOldSurveyTableAsk)) {
$aDelete['redundantsurveytables'] = $aOldSurveyTableAsk;
}
}
/**********************************************************************/
/* CHECK CPDB SURVEY_LINKS TABLE FOR REDUNDENT TOKEN TABLES */
/**********************************************************************/
//1: Get distinct list of survey_link survey ids, check if tokens
// table still exists for each one, and remove if not
/* TODO */
/**********************************************************************/
/* CHECK CPDB SURVEY_LINKS TABLE FOR REDUNDENT TOKEN ENTRIES */
/**********************************************************************/
//1: For each survey_link, see if the matching entry still exists in
// the token table and remove if it doesn't.
/* TODO */
return $aDelete;
}
public static function deleteAllById($questionsIds)
{
if (!is_array($questionsIds)) {
$questionsIds = array($questionsIds);
}
Yii::app()->db->createCommand()->delete(Conditions::model()->tableName(), array('in', 'qid', $questionsIds));
Yii::app()->db->createCommand()->delete(Question_attributes::model()->tableName(), array('in', 'qid', $questionsIds));
Yii::app()->db->createCommand()->delete(Answers::model()->tableName(), array('in', 'qid', $questionsIds));
Yii::app()->db->createCommand()->delete(Questions::model()->tableName(), array('in', 'parent_qid', $questionsIds));
Yii::app()->db->createCommand()->delete(Questions::model()->tableName(), array('in', 'qid', $questionsIds));
Yii::app()->db->createCommand()->delete(Defaultvalues::model()->tableName(), array('in', 'qid', $questionsIds));
Yii::app()->db->createCommand()->delete(Quota_members::model()->tableName(), array('in', 'qid', $questionsIds));
}