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