function actionparticipants()
{
$surveyid = Yii::app()->request->getQuery('surveyid');
$langcode = Yii::app()->request->getQuery('langcode');
$token = Yii::app()->request->getQuery('token');
Yii::app()->loadHelper('database');
Yii::app()->loadHelper('sanitize');
$sLanguageCode = $langcode;
$iSurveyID = $surveyid;
$sToken = $token;
$sToken = sanitize_token($sToken);
if (!$iSurveyID) {
$this->redirect(Yii::app()->getController()->createUrl('/'));
}
$iSurveyID = (int) $iSurveyID;
//Make sure it's an integer (protect from SQL injects)
//Check that there is a SID
// Get passed language from form, so that we dont lose this!
if (!isset($sLanguageCode) || $sLanguageCode == "" || !$sLanguageCode) {
$baselang = Survey::model()->findByPk($iSurveyID)->language;
Yii::import('application.libraries.Limesurvey_lang', true);
$clang = new Limesurvey_lang($baselang);
} else {
$sLanguageCode = sanitize_languagecode($sLanguageCode);
Yii::import('application.libraries.Limesurvey_lang', true);
$clang = new Limesurvey_lang($sLanguageCode);
$baselang = $sLanguageCode;
}
Yii::app()->lang = $clang;
$thissurvey = getSurveyInfo($iSurveyID, $baselang);
if ($thissurvey == false || !tableExists("{{tokens_{$iSurveyID}}}")) {
$html = $clang->gT('This survey does not seem to exist.');
} else {
$row = Tokens_dynamic::getEmailStatus($iSurveyID, $sToken);
$datas = Tokens_dynamic::model($iSurveyID)->find('token = :token', array(":token" => $sToken));
if ($row == false) {
$html = $clang->gT('You are not a participant in this survey.');
} else {
$usresult = $row['emailstatus'];
if ($usresult == 'OK') {
$usresult = Tokens_dynamic::updateEmailStatus($iSurveyID, $sToken, 'OptOut');
$html = $clang->gT('You have been successfully removed from this survey.');
} else {
$html = $clang->gT('You have been already removed from this survey.');
}
if (!empty($datas->participant_id) && $datas->participant_id != "") {
//Participant also exists in central db
$cpdb = Participants::model()->find('participant_id = :participant_id', array(":participant_id" => $datas->participant_id));
if ($cpdb->blacklisted == "Y") {
$html .= "<br />";
$html .= $clang->gt("You have already been removed from the central participants list for this site");
} else {
$cpdb->blacklisted = 'Y';
$cpdb->save();
$html .= "<br />";
$html .= $clang->gT("You have been removed from the central participants list for this site");
}
}
}
}
//PRINT COMPLETED PAGE
if (!$thissurvey['templatedir']) {
$thistpl = getTemplatePath(Yii::app()->getConfig("defaulttemplate"));
} else {
$thistpl = getTemplatePath($thissurvey['templatedir']);
}
$this->_renderHtml($html, $thistpl);
}
function getParticipantsSearchMultiple($condition, $page, $limit)
{
//http://localhost/limesurvey_yii/admin/participants/getParticipantsResults_json/search/email||contains||gov||and||firstname||contains||AL
//First contains fieldname, second contains method, third contains value, fourth contains BOOLEAN SQL and, or
//As we iterate through the conditions we build up the $command query by adding conditions to it
//
$i = 0;
$tobedonelater = array();
$start = $limit * $page - $limit;
$command = new CDbCriteria();
$command->condition = '';
//The following code performs an IN-SQL order, but this only works for standard participant fields
//For the time being, lets stick to just sorting the collected results, some thinking
//needs to be done about how we can sort the actual fullo query when combining with calculated
//or attribute based fields. I've switched this off, but left the code for future reference. JC
if (1 == 2) {
$sord = Yii::app()->request->getPost('sord');
//Sort order
$sidx = Yii::app()->request->getPost('sidx');
//Sort index
if (is_numeric($sidx) || $sidx == "survey") {
$sord = "";
$sidx = "";
}
if (!empty($sidx)) {
$sortorder = "{$sidx} {$sord}";
} else {
$sortorder = "";
}
if (!empty($sortorder)) {
$command->order = $sortorder;
}
}
$con = count($condition);
while ($i < $con) {
if ($i < 3) {
if (is_numeric($condition[2])) {
$condition[2] = intval($condition[2]);
}
switch ($condition[1]) {
case 'equal':
$operator = "=";
break;
case 'contains':
$operator = "LIKE";
$condition[2] = "%" . $condition[2] . "%";
break;
case 'beginswith':
$operator = "LIKE";
$condition[2] = $condition[2] . "%";
break;
case 'notequal':
$operator = "!=";
break;
case 'notcontains':
$operator = "NOT LIKE";
$condition[2] = "%" . $condition[2] . "%";
break;
case 'greaterthan':
$operator = ">";
break;
case 'lessthan':
$operator = "<";
}
if ($condition[0] == "survey") {
$lang = Yii::app()->session['adminlang'];
$command->addCondition('participant_id IN (SELECT distinct {{survey_links}}.participant_id FROM {{survey_links}}, {{surveys_languagesettings}} WHERE {{survey_links}}.survey_id = {{surveys_languagesettings}}.surveyls_survey_id AND {{surveys_languagesettings}}.surveyls_language=:lang AND ({{surveys_languagesettings}}.surveyls_title ' . $operator . ' :param2 OR {{survey_links}}.survey_id ' . $operator . ' :param2))');
$command->params = array(':lang' => $lang, ':param2' => $condition[2]);
} elseif ($condition[0] == "surveys") {
$addon = $operator == "<" ? " OR participant_id NOT IN (SELECT distinct participant_id FROM lime_survey_links)" : "";
$command->addCondition('participant_id IN (SELECT participant_id FROM lime_survey_links GROUP BY participant_id HAVING count(*) ' . $operator . ' :param2 ORDER BY count(*))' . $addon);
$command->params = array(':param2' => $condition[2]);
} elseif ($condition[0] == "owner_name") {
$userid = Yii::app()->db->createCommand()->select('uid')->where('full_name ' . $operator . ' :condition_2')->from('{{users}}')->bindParam("condition_2", $condition[2], PDO::PARAM_STR)->queryAll();
$uid = $userid[0];
$command->addCondition('owner_uid = :uid');
$command->params = array(':uid' => $uid['uid']);
} elseif (is_numeric($condition[0])) {
$command->addCondition('participant_id IN (SELECT distinct {{participant_attribute}}.participant_id FROM {{participant_attribute}} WHERE {{participant_attribute}}.attribute_id = :condition_0 AND {{participant_attribute}}.value ' . $operator . ' :condition_2)');
$command->params = array(':condition_0' => $condition[0], ':condition_2' => $condition[2]);
} else {
$command->addCondition($condition[0] . ' ' . $operator . ' :condition_2');
$command->params = array(':condition_2' => $condition[2]);
}
$i += 3;
} else {
if ($condition[$i] != '') {
if (is_numeric($condition[$i + 3])) {
$condition[$i + 3] = intval($condition[$i + 3]);
}
//Force the type of numeric values to be numeric
$booloperator = strtoupper($condition[$i]);
$condition1name = ":condition_" . ($i + 1);
$condition2name = ":condition_" . ($i + 3);
switch ($condition[$i + 2]) {
case 'equal':
$operator = "=";
break;
case 'contains':
$operator = "LIKE";
$condition[$i + 3] = "%" . $condition[$i + 3] . "%";
break;
case 'beginswith':
$operator = "LIKE";
$condition[$i + 3] = $condition[$i + 3] . "%";
break;
case 'notequal':
$operator = "!=";
break;
case 'notcontains':
$operator = "NOT LIKE";
$condition[$i + 3] = "%" . $condition[$i + 3] . "%";
break;
case 'greaterthan':
$operator = ">";
break;
case 'lessthan':
$operator = "<";
}
if ($condition[$i + 1] == "survey") {
$lang = Yii::app()->session['adminlang'];
$command->addCondition('participant_id IN (SELECT distinct {{survey_links}}.participant_id FROM {{survey_links}}, {{surveys_languagesettings}} WHERE {{survey_links}}.survey_id = {{surveys_languagesettings}}.surveyls_survey_id AND {{surveys_languagesettings}}.surveyls_language=:lang AND ({{surveys_languagesettings}}.surveyls_title ' . $operator . ' ' . $condition2name . ' OR {{survey_links}}.survey_id ' . $operator . ' ' . $condition2name . '))', $booloperator);
$command->params = array_merge($command->params, array(':lang' => $lang, $condition2name => $condition[$i + 3]));
} elseif ($condition[$i + 1] == "surveys") {
$addon = $operator == "<" ? " OR participant_id NOT IN (SELECT distinct participant_id FROM lime_survey_links)" : "";
$command->addCondition('participant_id IN (SELECT participant_id FROM lime_survey_links GROUP BY participant_id HAVING count(*) ' . $operator . ' ' . $condition2name . ' ORDER BY count(*))' . $addon);
$command->params = array_merge($command->params, array($condition2name => $condition[$i + 3]));
} elseif ($condition[$i + 1] == "owner_name") {
$userid = Yii::app()->db->createCommand()->select('uid')->where('full_name ' . $operator . ' ' . $condition2name)->from('{{users}}')->bindParam($condition2name, $condition[$i + 3], PDO::PARAM_STR)->queryAll();
$uid = array();
foreach ($userid as $row) {
$uid[] = $row['uid'];
}
$command->addInCondition('owner_uid', $uid, $booloperator);
} elseif (is_numeric($condition[$i + 1])) {
$command->addCondition('participant_id IN (SELECT distinct {{participant_attribute}}.participant_id FROM {{participant_attribute}} WHERE {{participant_attribute}}.attribute_id = ' . $condition1name . ' AND {{participant_attribute}}.value ' . $operator . ' ' . $condition2name . ')', $booloperator);
$command->params = array_merge($command->params, array($condition1name => $condition[$i + 1], $condition2name => $condition[$i + 3]));
} else {
$command->addCondition($condition[$i + 1] . ' ' . $operator . ' ' . $condition2name, $booloperator);
$command->params = array_merge($command->params, array($condition2name => $condition[$i + 3]));
}
$i = $i + 4;
} else {
$i = $i + 4;
}
}
}
if ($page == 0 && $limit == 0) {
$arr = Participants::model()->findAll($command);
$data = array();
foreach ($arr as $t) {
$data[$t->participant_id] = $t->attributes;
}
} else {
$command->limit = $limit;
$command->offset = $start;
$arr = Participants::model()->findAll($command);
$data = array();
foreach ($arr as $t) {
$data[$t->participant_id] = $t->attributes;
}
}
return $data;
}
function addToTokenattmap()
{
$iParticipantId = Yii::app()->request->getPost('participant_id');
$iSurveyId = Yii::app()->request->getPost('surveyid');
$mapped = Yii::app()->request->getPost('mapped');
$newcreate = Yii::app()->request->getPost('newarr');
$overwriteauto = Yii::app()->request->getPost('overwrite');
$overwriteman = Yii::app()->request->getPost('overwriteman');
$overwritest = Yii::app()->request->getPost('overwritest');
$createautomap = Yii::app()->request->getPost('createautomap');
$clang = $this->getController()->lang;
if (empty($newcreate[0])) {
$newcreate = array();
}
$response = Participants::model()->copytosurveyatt($iSurveyId, $mapped, $newcreate, $iParticipantId, $overwriteauto, $overwriteman, $overwritest, $createautomap);
printf($clang->gT("%s participants have been copied to the survey token table"), $response['success']);
if ($response['duplicate'] > 0) {
echo "\r\n";
printf($clang->gT("%s entries were not copied because they already existed"), $response['duplicate']);
}
if ($response['overwriteauto'] == "true" || $response['overwriteman'] == "true") {
echo "\r\n";
$clang->eT("Attribute values for existing participants have been updated from the participants records");
}
}
function copyToCentral($surveyid, $newarr, $mapped, $overwriteauto = false, $overwriteman = false, $createautomap = true)
{
$tokenid = Yii::app()->session['participantid'];
//List of token_id's to add to participants table
$duplicate = 0;
$sucessfull = 0;
$writearray = array();
$attid = array();
//Will store the CPDB attribute_id of new or existing attributes keyed by CPDB at
$pid = "";
/* Grab all the existing attribute field names from the tokens table */
$arr = Yii::app()->db->createCommand()->select('*')->from("{{tokens_{$surveyid}}}")->queryRow();
if (is_array($arr)) {
$tokenfieldnames = array_keys($arr);
$tokenattributefieldnames = array_filter($tokenfieldnames, 'filterForAttributes');
} else {
$tokenattributefieldnames = array();
}
/* Automatically mapped attribute names are named "attribute_cpdb_[some_number]" */
foreach ($tokenattributefieldnames as $key => $value) {
if ($value[10] == 'c') {
$autoattid = substr($value, 15);
$mapped[$autoattid] = $value;
}
}
/* Create new CPDB attributes */
if (!empty($newarr)) {
foreach ($newarr as $key => $value) {
/* $key is the fieldname from the token table (ie "attribute_1")
* $value is the 'friendly name' for the attribute (ie "Gender")
*/
$insertnames = array('attribute_type' => 'TB', 'visible' => 'Y');
Yii::app()->db->createCommand()->insert('{{participant_attribute_names}}', $insertnames);
$attid[$key] = getLastInsertID('{{participant_attribute_names}}');
/* eg $attid['attribute_1']='8372' */
$insertnameslang = array('attribute_id' => $attid[$key], 'attribute_name' => urldecode($value), 'lang' => Yii::app()->session['adminlang']);
Yii::app()->db->createCommand()->insert('{{participant_attribute_names_lang}}', $insertnameslang);
}
}
/* Add the participants to the CPDB = Iterate through each $tokenid and create the new CPDB id*/
foreach ($tokenid as $key => $tid) {
if (is_numeric($tid) && $tid != "") {
/* Get the data for this participant from the tokens table */
$tobeinserted = Yii::app()->db->createCommand()->select('participant_id,firstname,lastname,email,language')->where('tid = :tid')->from('{{tokens_' . intval($surveyid) . '}}')->bindParam(":tid", $tid, PDO::PARAM_INT)->queryRow();
/* See if there are any existing CPDB entries that match on firstname,lastname and email */
$query = Yii::app()->db->createCommand()->select('*')->from('{{participants}}')->where('firstname = :firstname AND lastname = :lastname AND email = :email')->bindParam(":firstname", $tobeinserted['firstname'], PDO::PARAM_STR)->bindParam(":lastname", $tobeinserted['lastname'], PDO::PARAM_STR)->bindParam(":email", $tobeinserted['email'], PDO::PARAM_STR)->queryAll();
/* If there is already an existing entry, add to the duplicate count */
if (count($query) > 0) {
$duplicate++;
//HERE is where we can add "overwrite" feature to update attribute values for existing participants
if ($overwriteauto == "true") {
if (!empty($newarr)) {
foreach ($newarr as $key => $value) {
Participants::model()->updateAttributeValueToken($surveyid, $query[0]['participant_id'], $attid[$key], $key);
}
}
}
if ($overwriteman == "true") {
/* Now add mapped attribute values */
if (!empty($mapped)) {
foreach ($mapped as $cpdbatt => $tatt) {
Participants::model()->updateAttributeValueToken($surveyid, $query[0]['participant_id'], $cpdbatt, $tatt);
}
}
}
} else {
/* Create entry in participants table */
$black = !empty($tobeinserted['blacklisted']) ? $tobeinserted['blacklised'] : 'N';
$pid = !empty($tobeinserted['participant_id']) ? $tobeinserted['participant_id'] : $this->gen_uuid();
$writearray = array('participant_id' => $pid, 'firstname' => $tobeinserted['firstname'], 'lastname' => $tobeinserted['lastname'], 'email' => $tobeinserted['email'], 'language' => $tobeinserted['language'], 'blacklisted' => $black, 'owner_uid' => Yii::app()->session['loginID']);
Yii::app()->db->createCommand()->insert('{{participants}}', $writearray);
//Update token table and insert the new UUID
$data = array("participant_id" => $pid);
Yii::app()->db->createCommand()->update('{{tokens_' . intval($surveyid) . '}}', $data, "tid = {$tid}");
/* Now add any new attribute values */
if (!empty($newarr)) {
foreach ($newarr as $key => $value) {
Participants::model()->updateAttributeValueToken($surveyid, $pid, $attid[$key], $key);
}
}
/* Now add mapped attribute values */
if (!empty($mapped)) {
foreach ($mapped as $cpdbatt => $tatt) {
Participants::model()->updateAttributeValueToken($surveyid, $pid, $cpdbatt, $tatt);
}
}
$sucessfull++;
/* Create a survey_link */
$data = array('participant_id' => $pid, 'token_id' => $tid, 'survey_id' => $surveyid, 'date_created' => date('Y-m-d H:i:s', time()));
Yii::app()->db->createCommand()->insert('{{survey_links}}', $data);
}
}
}
if (!empty($newarr)) {
/* Rename the token attribute fields to a cpdb field, so in future
* we know that it belongs to a CPDB field */
foreach ($newarr as $key => $value) {
$newname = 'attribute_cpdb_' . intval($attid[$key]);
$fields = array($value => array('name' => $newname, 'type' => 'TEXT'));
//Rename the field in the tokens_[sid] table
Yii::app()->db->createCommand()->renameColumn('{{tokens_' . intval($surveyid) . '}}', $key, $newname);
//Make the field a TEXT field
Yii::app()->db->createCommand()->alterColumn('{{tokens_' . intval($surveyid) . '}}', $newname, 'TEXT');
$previousatt = Yii::app()->db->createCommand()->select('attributedescriptions')->from('{{surveys}}')->where("sid = " . $surveyid);
$patt = $previousatt->queryRow();
$previousattribute = unserialize($patt['attributedescriptions']);
$previousattribute[$newname] = $previousattribute[$key];
unset($previousattribute[$key]);
$previousattribute = serialize($previousattribute);
Yii::app()->db->createCommand()->update('{{surveys}}', array("attributedescriptions" => $previousattribute), 'sid = ' . $surveyid);
//load description in the surveys table
}
}
if (!empty($mapped)) {
foreach ($mapped as $cpdbatt => $tatt) {
if ($tatt[10] != 'c' && $createautomap == "true") {
// Change the fieldname in the token table to attribute_cpdb_[participant_attribute_id]
// so future mapping is done automatically
$newname = 'attribute_cpdb_' . $cpdbatt;
$fields = array($tatt => array('name' => $newname, 'type' => 'TEXT'));
Yii::app()->db->createCommand()->renameColumn('{{tokens_' . intval($surveyid) . '}}', $tatt, $newname);
Yii::app()->db->createCommand()->alterColumn('{{tokens_' . intval($surveyid) . '}}', $newname, 'TEXT');
$previousatt = Yii::app()->db->createCommand()->select('attributedescriptions')->from('{{surveys}}')->where("sid = :sid")->bindParam(":sid", $surveyid, PDO::PARAM_INT);
$previousattribute = $previousatt->queryRow();
$previousattribute = unserialize($previousattribute['attributedescriptions']);
$previousattribute[$newname] = $previousattribute[$tatt];
unset($previousattribute[$tatt]);
//Rename the token field the name of the participant_attribute
$attributedetails = ParticipantAttributeNames::model()->getAttributeNames($cpdbatt);
$previousattribute[$newname]['description'] = $attributedetails[0]['attribute_name'];
$previousattribute = serialize($previousattribute);
//$newstring = str_replace($tatt, $newname, $previousattribute['attributedescriptions']);
Yii::app()->db->createCommand()->update('{{surveys}}', array("attributedescriptions" => $previousattribute), 'sid = ' . $surveyid);
}
}
}
$returndata = array('success' => $sucessfull, 'duplicate' => $duplicate, 'overwriteauto' => $overwriteauto, 'overwriteman' => $overwriteman);
return $returndata;
}
