/** * dataentry::insert() * insert new dataentry * @return */ public function insert() { $subaction = Yii::app()->request->getPost('subaction'); $surveyid = Yii::app()->request->getPost('sid'); $lang = isset($_POST['lang']) ? Yii::app()->request->getPost('lang') : NULL; $aData = array('surveyid' => $surveyid, 'lang' => $lang); if (Permission::model()->hasSurveyPermission($surveyid, 'responses', 'create')) { if ($subaction == "insert" && Permission::model()->hasSurveyPermission($surveyid, 'responses', 'create')) { $surveytable = "{{survey_{$surveyid}}}"; $thissurvey = getSurveyInfo($surveyid); $errormsg = ""; Yii::app()->loadHelper("database"); $aViewUrls['display']['menu_bars']['browse'] = gT("Data entry"); $aDataentryoutput = ''; $aDataentrymsgs = array(); $hiddenfields = ''; $lastanswfortoken = ''; // check if a previous answer has been submitted or saved $rlanguage = ''; if (Yii::app()->request->getPost('token') && Permission::model()->hasSurveyPermission($surveyid, 'tokens', 'update')) { $tokencompleted = ""; $tcquery = "SELECT completed from {{tokens_{$surveyid}}} WHERE token=" . dbQuoteAll($_POST['token']); $tcresult = dbExecuteAssoc($tcquery); $tcresult = $tcresult->readAll(); $tccount = count($tcresult); foreach ($tcresult as $tcrow) { $tokencompleted = $tcrow['completed']; } if ($tccount < 1) { // token doesn't exist in token table $lastanswfortoken = 'UnknownToken'; } elseif ($thissurvey['anonymized'] == "Y") { // token exist but survey is anonymous, check completed state if ($tokencompleted != "" && $tokencompleted != "N") { // token is completed $lastanswfortoken = 'PrivacyProtected'; } } else { // token is valid, survey not anonymous, try to get last recorded response id $aquery = "SELECT id,startlanguage FROM {$surveytable} WHERE token=" . dbQuoteAll($_POST['token']); $aresult = dbExecuteAssoc($aquery); foreach ($aresult->readAll() as $arow) { if ($tokencompleted != "N") { $lastanswfortoken = $arow['id']; } $rlanguage = $arow['startlanguage']; } } } // First Check if the survey uses tokens and if a token has been provided if (tableExists('{{tokens_' . $thissurvey['sid'] . '}}') && !$_POST['token']) { $errormsg = CHtml::tag('div', array('class' => 'warningheader'), gT("Error")); $errormsg .= CHtml::tag('p', array(), gT("This is a closed-access survey, so you must supply a valid token. Please contact the administrator for assistance.")); } elseif (tableExists('{{tokens_' . $thissurvey['sid'] . '}}') && $lastanswfortoken == 'UnknownToken') { $errormsg = CHtml::tag('div', array('class' => 'warningheader'), gT("Error")); $errormsg .= CHtml::tag('p', array(), gT("The token you have provided is not valid or has already been used.")); } elseif (tableExists('{{tokens_' . $thissurvey['sid'] . '}}') && $lastanswfortoken != '') { $errormsg = CHtml::tag('div', array('class' => 'warningheader'), gT("Error")); $errormsg .= CHtml::tag('p', array(), gT("There is already a recorded answer for this token")); if ($lastanswfortoken != 'PrivacyProtected') { $errormsg .= "<br /><br />" . gT("Follow the following link to update it") . ":\n"; $errormsg .= CHtml::link("[id:{$lastanswfortoken}]", $this->getController()->createUrl('/admin/dataentry/sa/editdata/subaction/edit/id/' . $lastanswfortoken . '/surveyid/' . $surveyid . '/lang/' . $rlanguage), array('title' => gT("Edit this entry"))); $errormsg .= "<br/><br/>"; } else { $errormsg .= "<br /><br />" . gT("This surveys uses anonymized responses, so you can't update your response.") . "\n"; } } else { $last_db_id = 0; if (isset($_POST['save']) && $_POST['save'] == "on") { $aData['save'] = TRUE; $saver['identifier'] = $_POST['save_identifier']; $saver['language'] = $_POST['save_language']; $saver['password'] = $_POST['save_password']; $saver['passwordconfirm'] = $_POST['save_confirmpassword']; $saver['email'] = $_POST['save_email']; if (!returnGlobal('redo')) { $password = md5($saver['password']); } else { $password = $saver['password']; } $errormsg = ""; if (!$saver['identifier']) { $errormsg .= gT("Error") . ": " . gT("You must supply a name for this saved session."); } if (!$saver['password']) { $errormsg .= gT("Error") . ": " . gT("You must supply a password for this saved session."); } if ($saver['password'] != $saver['passwordconfirm']) { $errormsg .= gT("Error") . ": " . gT("Your passwords do not match."); } $aData['errormsg'] = $errormsg; if ($errormsg) { foreach ($_POST as $key => $val) { if (substr($key, 0, 4) != "save" && $key != "action" && $key != "sid" && $key != "datestamp" && $key != "ipaddr") { $hiddenfields .= CHtml::hiddenField($key, $val); //$aDataentryoutput .= "<input type='hidden' name='$key' value='$val' />\n"; } } } } //BUILD THE SQL TO INSERT RESPONSES $baselang = Survey::model()->findByPk($surveyid)->language; $fieldmap = createFieldMap($surveyid, 'full', false, false, getBaseLanguageFromSurveyID($surveyid)); $insert_data = array(); $_POST['startlanguage'] = $baselang; if ($thissurvey['datestamp'] == "Y") { $_POST['startdate'] = $_POST['datestamp']; } if (isset($_POST['closerecord'])) { if ($thissurvey['datestamp'] == "Y") { $_POST['submitdate'] = dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", Yii::app()->getConfig('timeadjust')); } else { $_POST['submitdate'] = date("Y-m-d H:i:s", mktime(0, 0, 0, 1, 1, 1980)); } } foreach ($fieldmap as $irow) { $fieldname = $irow['fieldname']; if (isset($_POST[$fieldname])) { if ($_POST[$fieldname] == "" && ($irow['type'] == 'D' || $irow['type'] == 'N' || $irow['type'] == 'K')) { // can't add '' in Date column // Do nothing } else { if ($irow['type'] == '|') { if (!strpos($irow['fieldname'], "_filecount")) { $json = $_POST[$fieldname]; $phparray = json_decode(stripslashes($json)); $filecount = 0; for ($i = 0; $filecount < count($phparray); $i++) { if ($_FILES[$fieldname . "_file_" . $i]['error'] != 4) { $target = Yii::app()->getConfig('uploaddir') . "/surveys/" . $thissurvey['sid'] . "/files/" . randomChars(20); $size = 0.001 * $_FILES[$fieldname . "_file_" . $i]['size']; $name = rawurlencode($_FILES[$fieldname . "_file_" . $i]['name']); if (move_uploaded_file($_FILES[$fieldname . "_file_" . $i]['tmp_name'], $target)) { $phparray[$filecount]->filename = basename($target); $phparray[$filecount]->name = $name; $phparray[$filecount]->size = $size; $pathinfo = pathinfo($_FILES[$fieldname . "_file_" . $i]['name']); $phparray[$filecount]->ext = $pathinfo['extension']; $filecount++; } } } $insert_data[$fieldname] = ls_json_encode($phparray); } else { $insert_data[$fieldname] = count($phparray); } } elseif ($irow['type'] == 'D') { Yii::app()->loadLibrary('Date_Time_Converter'); $qidattributes = getQuestionAttributeValues($irow['qid']); $dateformatdetails = getDateFormatDataForQID($qidattributes, $thissurvey); $datetimeobj = new Date_Time_Converter($_POST[$fieldname], $dateformatdetails['phpdate']); $insert_data[$fieldname] = $datetimeobj->convert("Y-m-d H:i:s"); } else { $insert_data[$fieldname] = $_POST[$fieldname]; } } } } SurveyDynamic::sid($surveyid); $new_response = new SurveyDynamic(); foreach ($insert_data as $column => $value) { $new_response->{$column} = $value; } $new_response->save(); $last_db_id = $new_response->getPrimaryKey(); if (isset($_POST['closerecord']) && isset($_POST['token']) && $_POST['token'] != '') { // get submit date if (isset($_POST['closedate'])) { $submitdate = $_POST['closedate']; } else { $submitdate = dateShift(date("Y-m-d H:i:s"), "Y-m-d", $timeadjust); } // check how many uses the token has left $usesquery = "SELECT usesleft FROM {{tokens_}}{$surveyid} WHERE token=" . dbQuoteAll($_POST['token']); $usesresult = dbExecuteAssoc($usesquery); $usesrow = $usesresult->readAll(); //$usesresult->row_array() if (isset($usesrow)) { $usesleft = $usesrow[0]['usesleft']; } // query for updating tokens $utquery = "UPDATE {{tokens_{$surveyid}}}\n"; if (isTokenCompletedDatestamped($thissurvey)) { if (isset($usesleft) && $usesleft <= 1) { $utquery .= "SET usesleft=usesleft-1, completed=" . dbQuoteAll($submitdate); } else { $utquery .= "SET usesleft=usesleft-1\n"; } } else { if (isset($usesleft) && $usesleft <= 1) { $utquery .= "SET usesleft=usesleft-1, completed='Y'\n"; } else { $utquery .= "SET usesleft=usesleft-1\n"; } } $utquery .= "WHERE token=" . dbQuoteAll($_POST['token']); $utresult = dbExecuteAssoc($utquery); //Yii::app()->db->Execute($utquery) or safeDie ("Couldn't update tokens table!<br />\n$utquery<br />\n".Yii::app()->db->ErrorMsg()); // save submitdate into survey table $sdquery = "UPDATE {{survey_{$surveyid}}} SET submitdate='" . $submitdate . "' WHERE id={$last_db_id}\n"; $sdresult = dbExecuteAssoc($sdquery) or safeDie("Couldn't set submitdate response in survey table!<br />\n{$sdquery}<br />\n"); } if (isset($_POST['save']) && $_POST['save'] == "on") { $srid = $last_db_id; $aUserData = Yii::app()->session; //CREATE ENTRY INTO "saved_control" $saved_control_table = '{{saved_control}}'; $columns = array("sid", "srid", "identifier", "access_code", "email", "ip", "refurl", 'saved_thisstep', "status", "saved_date"); $values = array("'" . $surveyid . "'", "'" . $srid . "'", "'" . $saver['identifier'] . "'", "'" . $password . "'", "'" . $saver['email'] . "'", "'" . $aUserData['ip_address'] . "'", "'" . getenv("HTTP_REFERER") . "'", 0, "'" . "S" . "'", "'" . dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", "'" . Yii::app()->getConfig('timeadjust')) . "'"); $SQL = "INSERT INTO {$saved_control_table}\n (" . implode(',', $columns) . ")\n VALUES\n (" . implode(',', $values) . ")"; /*$scdata = array("sid"=>$surveyid, "srid"=>$srid, "identifier"=>$saver['identifier'], "access_code"=>$password, "email"=>$saver['email'], "ip"=>$aUserData['ip_address'], "refurl"=>getenv("HTTP_REFERER"), 'saved_thisstep' => 0, "status"=>"S", "saved_date"=>dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", Yii::app()->getConfig('timeadjust'))); $this->load->model('saved_control_model');*/ if (dbExecuteAssoc($SQL)) { $scid = getLastInsertID('{{saved_control}}'); $aDataentrymsgs[] = CHtml::tag('font', array('class' => 'successtitle'), gT("Your survey responses have been saved successfully. You will be sent a confirmation e-mail. Please make sure to save your password, since we will not be able to retrieve it for you.")); //$aDataentryoutput .= "<font class='successtitle'></font><br />\n"; $tokens_table = "{{tokens_{$surveyid}}}"; if (tableExists($tokens_table)) { $tkquery = "SELECT * FROM {$tokens_table}"; $tkresult = dbExecuteAssoc($tkquery); /*$tokendata = array ( "firstname"=> $saver['identifier'], "lastname"=> $saver['identifier'], "email"=>$saver['email'], "token"=>randomChars(15), "language"=>$saver['language'], "sent"=>dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i", $timeadjust), "completed"=>"N");*/ $columns = array("firstname", "lastname", "email", "token", "language", "sent", "completed"); $values = array("'" . $saver['identifier'] . "'", "'" . $saver['identifier'] . "'", "'" . $saver['email'] . "'", "'" . $password . "'", "'" . randomChars(15) . "'", "'" . $saver['language'] . "'", "'" . "N" . "'"); $SQL = "INSERT INTO {$token_table}\n (" . implode(',', $columns) . ")\n VALUES\n (" . implode(',', $values) . ")"; dbExecuteAssoc($SQL); $aDataentrymsgs[] = CHtml::tag('font', array('class' => 'successtitle'), gT("A token entry for the saved survey has been created too.")); //$aDataentryoutput .= "<font class='successtitle'></font><br />\n"; } if ($saver['email']) { //Send email if (validateEmailAddress($saver['email']) && !returnGlobal('redo')) { $subject = gT("Saved Survey Details"); $message = gT("Thank you for saving your survey in progress. The following details can be used to return to this survey and continue where you left off. Please keep this e-mail for your reference - we cannot retrieve the password for you."); $message .= "\n\n" . $thissurvey['name'] . "\n\n"; $message .= gT("Name") . ": " . $saver['identifier'] . "\n"; $message .= gT("Password") . ": " . $saver['password'] . "\n\n"; $message .= gT("Reload your survey by clicking on the following link (or pasting it into your browser):") . "\n"; $message .= Yii::app()->getController()->createAbsoluteUrl("/survey/index/sid/{$iSurveyID}/loadall/reload/scid/{$scid}/loadname/" . rawurlencode($saver['identifier']) . "/loadpass/" . rawurlencode($saver['password']) . "/lang/" . rawurlencode($saver['language'])); if (isset($tokendata['token'])) { $message .= "/token/" . rawurlencode($tokendata['token']); } $from = $thissurvey['adminemail']; if (SendEmailMessage($message, $subject, $saver['email'], $from, $sitename, false, getBounceEmail($surveyid))) { $emailsent = "Y"; $aDataentrymsgs[] = CHtml::tag('font', array('class' => 'successtitle'), gT("An email has been sent with details about your saved survey")); } } } } else { safeDie("Unable to insert record into saved_control table.<br /><br />"); } } $aData['thisid'] = $last_db_id; } $aData['errormsg'] = $errormsg; $aData['dataentrymsgs'] = $aDataentrymsgs; $aData['sidemenu']['state'] = false; $this->_renderWrappedTemplate('dataentry', 'insert', $aData); } } }
private function _prepFieldValues($fieldnames, $field, $fieldcount, $donotimport) { $fieldvalues = explode("\t", str_replace("\n", "", $field), $fieldcount + 1); // Excel likes to quote fields sometimes. =( $fieldvalues = preg_replace('/^"(.*)"$/s', '\\1', $fieldvalues); // Be careful about the order of these arrays: // lbrace has to be substituted *last* $fieldvalues = str_replace(array("{newline}", "{cr}", "{tab}", "{quote}", "{lbrace}"), array("\n", "\r", "\t", "\"", "{"), $fieldvalues); //remove any fields which no longer exist if (isset($donotimport)) { foreach ($donotimport as $not) { unset($fieldvalues[$not]); } } // Sometimes columns with nothing in them get omitted by excel while (count($fieldnames) > count($fieldvalues)) { $fieldvalues[] = ""; } // Sometimes columns with nothing in them get added by excel while (count($fieldnames) < count($fieldvalues) && trim($fieldvalues[count($fieldvalues) - 1]) == "") { unset($fieldvalues[count($fieldvalues) - 1]); } // Make this safe for DB (*after* we undo first excel's // and then our escaping). $fieldvalues = array_map('dbQuoteAll', $fieldvalues); $fieldvalues = str_replace(dbQuoteAll('{question_not_shown}'), 'NULL', $fieldvalues); return $fieldvalues; }
function upgradeTables143() { global $modifyoutput; $aQIDReplacements = array(); $answerquery = "select a.*, q.sid, q.gid from {{answers}} a,{{questions}} q where a.qid=q.qid and q.type in ('L','O','!') and a.default_value='Y'"; $answerresult = Yii::app()->getDb()->createCommand($answerquery)->queryAll(); foreach ($answerresult as $row) { modifyDatabase("", "INSERT INTO {{defaultvalues}} (qid, scale_id,language,specialtype,defaultvalue) VALUES ({$row['qid']},0," . dbQuoteAll($row['language']) . ",''," . dbQuoteAll($row['code']) . ")"); echo $modifyoutput; flush(); @ob_flush(); } // Convert answers to subquestions $answerquery = "select a.*, q.sid, q.gid, q.type from {{answers}} a,{{questions}} q where a.qid=q.qid and a.language=q.language and q.type in ('1','A','B','C','E','F','H','K',';',':','M','P','Q')"; $answerresult = Yii::app()->getDb()->createCommand($answerquery)->queryAll(); foreach ($answerresult as $row) { $aInsert = array(); if (isset($aQIDReplacements[$row['qid'] . '_' . $row['code']])) { $aInsert['qid'] = $aQIDReplacements[$row['qid'] . '_' . $row['code']]; } $aInsert['sid'] = $row['sid']; $aInsert['gid'] = $row['gid']; $aInsert['parent_qid'] = $row['qid']; $aInsert['type'] = $row['type']; $aInsert['title'] = $row['code']; $aInsert['question'] = $row['answer']; $aInsert['question_order'] = $row['sortorder']; $aInsert['language'] = $row['language']; $iLastInsertID = Question::model()->insertRecords($aInsert); if (!isset($aInsert['qid'])) { $aQIDReplacements[$row['qid'] . '_' . $row['code']] = $iLastInsertID; $iSaveSQID = $aQIDReplacements[$row['qid'] . '_' . $row['code']]; } else { $iSaveSQID = $aInsert['qid']; } if (($row['type'] == 'M' || $row['type'] == 'P') && $row['default_value'] == 'Y') { modifyDatabase("", "INSERT INTO {{defaultvalues}} (qid, sqid, scale_id,language,specialtype,defaultvalue) VALUES ({$row['qid']},{$iSaveSQID},0," . dbQuoteAll($row['language']) . ",'','Y')"); echo $modifyoutput; flush(); @ob_flush(); } } // Sanitize data if (Yii::app()->db->driverName == 'pgsql') { modifyDatabase("", "delete from {{answers}} USING {{questions}} WHERE {{answers}}.qid={{questions}}.qid AND {{questions}}.type in ('1','F','H','M','P','W','Z')"); echo $modifyoutput; flush(); @ob_flush(); } else { modifyDatabase("", "delete {{answers}} from {{answers}} LEFT join {{questions}} ON {{answers}}.qid={{questions}}.qid where {{questions}}.type in ('1','F','H','M','P','W','Z')"); echo $modifyoutput; flush(); @ob_flush(); } // Convert labels to answers $answerquery = "select qid ,type ,lid ,lid1, language from {{questions}} where parent_qid=0 and type in ('1','F','H','M','P','W','Z')"; $answerresult = Yii::app()->getDb()->createCommand($answerquery)->queryAll(); foreach ($answerresult as $row) { $labelquery = "Select * from {{labels}} where lid={$row['lid']} and language=" . dbQuoteAll($row['language']); $labelresult = Yii::app()->getDb()->createCommand($labelquery)->queryAll(); foreach ($labelresult as $lrow) { modifyDatabase("", "INSERT INTO {{answers}} (qid, code, answer, sortorder, language, assessment_value) VALUES ({$row['qid']}," . dbQuoteAll($lrow['code']) . "," . dbQuoteAll($lrow['title']) . ",{$lrow['sortorder']}," . dbQuoteAll($lrow['language']) . ",{$lrow['assessment_value']})"); echo $modifyoutput; flush(); @ob_flush(); //$labelids[] } if ($row['type'] == '1') { $labelquery = "Select * from {{labels}} where lid={$row['lid1']} and language=" . dbQuoteAll($row['language']); $labelresult = Yii::app()->getDb()->createCommand($labelquery)->queryAll(); foreach ($labelresult as $lrow) { modifyDatabase("", "INSERT INTO {{answers}} (qid, code, answer, sortorder, language, scale_id, assessment_value) VALUES ({$row['qid']}," . dbQuoteAll($lrow['code']) . "," . dbQuoteAll($lrow['title']) . ",{$lrow['sortorder']}," . dbQuoteAll($lrow['language']) . ",1,{$lrow['assessment_value']})"); echo $modifyoutput; flush(); @ob_flush(); } } } // Convert labels to subquestions $answerquery = "select * from {{questions}} where parent_qid=0 and type in (';',':')"; $answerresult = Yii::app()->getDb()->createCommand($answerquery)->queryAll(); foreach ($answerresult as $row) { $labelquery = "Select * from {{labels}} where lid={$row['lid']} and language=" . dbQuoteAll($row['language']); $labelresult = Yii::app()->getDb()->createCommand($labelquery)->queryAll(); foreach ($labelresult as $lrow) { $aInsert = array(); if (isset($aQIDReplacements[$row['qid'] . '_' . $lrow['code'] . '_1'])) { $aInsert['qid'] = $aQIDReplacements[$row['qid'] . '_' . $lrow['code'] . '_1']; } $aInsert['sid'] = $row['sid']; $aInsert['gid'] = $row['gid']; $aInsert['parent_qid'] = $row['qid']; $aInsert['type'] = $row['type']; $aInsert['title'] = $lrow['code']; $aInsert['question'] = $lrow['title']; $aInsert['question_order'] = $lrow['sortorder']; $aInsert['language'] = $lrow['language']; $aInsert['scale_id'] = 1; $iLastInsertID = Question::model()->insertRecords($aInsert); if (isset($aInsert['qid'])) { $aQIDReplacements[$row['qid'] . '_' . $lrow['code'] . '_1'] = $iLastInsertID; } } } $updatequery = "update {{questions}} set type='!' where type='W'"; modifyDatabase("", $updatequery); echo $modifyoutput; flush(); @ob_flush(); $updatequery = "update {{questions}} set type='L' where type='Z'"; modifyDatabase("", $updatequery); echo $modifyoutput; flush(); @ob_flush(); // Now move all non-standard templates to the /upload dir $usertemplaterootdir = Yii::app()->getConfig("usertemplaterootdir"); $standardtemplaterootdir = Yii::app()->getConfig('standardtemplaterootdir'); if (!$usertemplaterootdir) { die("getTemplateList() no template directory"); } if ($handle = opendir($standardtemplaterootdir)) { while (false !== ($file = readdir($handle))) { if (!is_file("{$standardtemplaterootdir}/{$file}") && $file != "." && $file != ".." && $file != ".svn" && !isStandardTemplate($file)) { if (!rename($standardtemplaterootdir . DIRECTORY_SEPARATOR . $file, $usertemplaterootdir . DIRECTORY_SEPARATOR . $file)) { echo "There was a problem moving directory '" . $standardtemplaterootdir . DIRECTORY_SEPARATOR . $file . "' to '" . $usertemplaterootdir . DIRECTORY_SEPARATOR . $file . "' due to missing permissions. Please do this manually.<br />"; } } } closedir($handle); } }
/** * Caculate assessement scores * * @param mixed $surveyid * @param mixed $returndataonly - only returns an array with data */ function doAssessment($surveyid, $returndataonly = false) { $clang = Yii::app()->lang; $baselang = Survey::model()->findByPk($surveyid)->language; if (Survey::model()->findByPk($surveyid)->assessments != "Y") { return false; } $total = 0; if (!isset($_SESSION['survey_' . $surveyid]['s_lang'])) { $_SESSION['survey_' . $surveyid]['s_lang'] = $baselang; } $query = "SELECT * FROM {{assessments}}\n WHERE sid={$surveyid} and language='" . $_SESSION['survey_' . $surveyid]['s_lang'] . "'\n ORDER BY scope, id"; if ($result = dbExecuteAssoc($query)) { $aResultSet = $result->readAll(); if (count($aResultSet) > 0) { foreach ($aResultSet as $row) { if ($row['scope'] == "G") { $assessment['group'][$row['gid']][] = array("name" => $row['name'], "min" => $row['minimum'], "max" => $row['maximum'], "message" => $row['message']); } else { $assessment['total'][] = array("name" => $row['name'], "min" => $row['minimum'], "max" => $row['maximum'], "message" => $row['message']); } } $fieldmap = createFieldMap($surveyid, "full", false, false, $_SESSION['survey_' . $surveyid]['s_lang']); $i = 0; $total = 0; $groups = array(); foreach ($fieldmap as $field) { if (in_array($field['type'], array('1', 'F', 'H', 'W', 'Z', 'L', '!', 'M', 'O', 'P'))) { $fieldmap[$field['fieldname']]['assessment_value'] = 0; if (isset($_SESSION['survey_' . $surveyid][$field['fieldname']])) { if ($field['type'] == "M" || $field['type'] == "P") { if ($_SESSION['survey_' . $surveyid][$field['fieldname']] == "Y") { $aAttributes = getQuestionAttributeValues($field['qid'], $field['type']); $fieldmap[$field['fieldname']]['assessment_value'] = (int) $aAttributes['assessment_value']; $total = $total + (int) $aAttributes['assessment_value']; } } else { $usquery = "SELECT assessment_value FROM {{answers}} where qid=" . $field['qid'] . " and language='{$baselang}' and code=" . dbQuoteAll($_SESSION['survey_' . $surveyid][$field['fieldname']]); $usresult = dbExecuteAssoc($usquery); //Checked if ($usresult) { $usrow = $usresult->read(); $fieldmap[$field['fieldname']]['assessment_value'] = $usrow['assessment_value']; $total = $total + $usrow['assessment_value']; } } } $groups[] = $field['gid']; } $i++; } $groups = array_unique($groups); foreach ($groups as $group) { $grouptotal = 0; foreach ($fieldmap as $field) { if ($field['gid'] == $group && isset($field['assessment_value'])) { //$grouptotal=$grouptotal+$field['answer']; if (isset($_SESSION['survey_' . $surveyid][$field['fieldname']])) { $grouptotal = $grouptotal + $field['assessment_value']; } } } $subtotal[$group] = $grouptotal; } } $assessments = ""; if (isset($subtotal) && is_array($subtotal)) { foreach ($subtotal as $key => $val) { if (isset($assessment['group'][$key])) { foreach ($assessment['group'][$key] as $assessed) { if ($val >= $assessed['min'] && $val <= $assessed['max'] && $returndataonly === false) { $assessments .= "\t<!-- GROUP ASSESSMENT: Score: {$val} Min: " . $assessed['min'] . " Max: " . $assessed['max'] . "-->\n <table class='assessments'>\n <tr>\n <th>" . str_replace(array("{PERC}", "{TOTAL}"), array($val, $total), $assessed['name']) . "\n </th>\n </tr>\n <tr>\n <td>" . str_replace(array("{PERC}", "{TOTAL}"), array($val, $total), $assessed['message']) . "\n </td>\n </tr>\n </table><br />\n"; } } } } } if (isset($assessment['total'])) { foreach ($assessment['total'] as $assessed) { if ($total >= $assessed['min'] && $total <= $assessed['max'] && $returndataonly === false) { $assessments .= "\t\t\t<!-- TOTAL ASSESSMENT: Score: {$total} Min: " . $assessed['min'] . " Max: " . $assessed['max'] . "-->\n <table class='assessments' align='center'>\n <tr>\n <th>" . str_replace(array("{PERC}", "{TOTAL}"), array($val, $total), stripslashes($assessed['name'])) . "\n </th>\n </tr>\n <tr>\n <td>" . str_replace(array("{PERC}", "{TOTAL}"), array($val, $total), stripslashes($assessed['message'])) . "\n </td>\n </tr>\n </table>\n"; } } } if ($returndataonly == true) { return array('total' => $total); } else { return $assessments; } } }
function tokensExport($iSurveyID) { $sEmailFiter = trim(App()->request->getPost('filteremail')); $iTokenStatus = App()->request->getPost('tokenstatus'); $iInvitationStatus = App()->request->getPost('invitationstatus'); $iReminderStatus = App()->request->getPost('reminderstatus'); $sTokenLanguage = App()->request->getPost('tokenlanguage'); $oSurvey = Survey::model()->findByPk($iSurveyID); $bIsNotAnonymous = $oSurvey->anonymized == 'N' && $oSurvey->active == 'Y'; // db table exist (survey_$iSurveyID) ? $bquery = "SELECT * FROM {{tokens_{$iSurveyID}}} where 1=1"; $databasetype = Yii::app()->db->getDriverName(); if (trim($sEmailFiter) != '') { if (in_array($databasetype, array('mssql', 'sqlsrv', 'dblib'))) { $bquery .= ' and CAST(email as varchar) like ' . dbQuoteAll('%' . $_POST['filteremail'] . '%', true); } else { $bquery .= ' and email like ' . dbQuoteAll('%' . $_POST['filteremail'] . '%', true); } } if ($_POST['tokenstatus'] == 1) { $bquery .= " and completed<>'N'"; } elseif ($iTokenStatus == 2) { $bquery .= " and completed='N'"; } elseif ($iTokenStatus == 3 && $bIsNotAnonymous) { $bquery .= " and completed='N' and token not in (select token from {{survey_{$iSurveyID}}} group by token)"; } elseif ($iTokenStatus == 4 && $bIsNotAnonymous) { $bquery .= " and completed='N' and token in (select token from {{survey_{$iSurveyID}}} group by token)"; } if ($iInvitationStatus == 1) { $bquery .= " and sent<>'N'"; } if ($iInvitationStatus == 2) { $bquery .= " and sent='N'"; } if ($iReminderStatus == 1) { $bquery .= " and remindersent<>'N'"; } if ($iReminderStatus == 2) { $bquery .= " and remindersent='N'"; } if ($sTokenLanguage != '') { $bquery .= " and language=" . dbQuoteAll($sTokenLanguage); } $bquery .= " ORDER BY tid"; Yii::app()->loadHelper('database'); $bresult = Yii::app()->db->createCommand($bquery)->query(); //dbExecuteAssoc($bquery) is faster but deprecated! //HEADERS should be after the above query else timeout errors in case there are lots of tokens! header("Content-Disposition: attachment; filename=tokens_" . $iSurveyID . ".csv"); header("Content-type: text/comma-separated-values; charset=UTF-8"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Pragma: cache"); // Export UTF8 WITH BOM $tokenoutput = chr(hexdec('EF')) . chr(hexdec('BB')) . chr(hexdec('BF')); $tokenoutput .= "tid,firstname,lastname,email,emailstatus,token,language,validfrom,validuntil,invited,reminded,remindercount,completed,usesleft"; $attrfieldnames = getAttributeFieldNames($iSurveyID); $attrfielddescr = getTokenFieldsAndNames($iSurveyID, true); foreach ($attrfieldnames as $attr_name) { $tokenoutput .= ", {$attr_name}"; if (isset($attrfielddescr[$attr_name])) { $tokenoutput .= " <" . str_replace(",", " ", $attrfielddescr[$attr_name]['description']) . ">"; } } $tokenoutput .= "\n"; echo $tokenoutput; $tokenoutput = ""; // Export token line by line and fill $aExportedTokens with token exported Yii::import('application.libraries.Date_Time_Converter', true); $aExportedTokens = array(); while ($brow = $bresult->read()) { if (trim($brow['validfrom'] != '')) { $datetimeobj = new Date_Time_Converter($brow['validfrom'], "Y-m-d H:i:s"); $brow['validfrom'] = $datetimeobj->convert('Y-m-d H:i'); } if (trim($brow['validuntil'] != '')) { $datetimeobj = new Date_Time_Converter($brow['validuntil'], "Y-m-d H:i:s"); $brow['validuntil'] = $datetimeobj->convert('Y-m-d H:i'); } $tokenoutput .= '"' . trim($brow['tid']) . '",'; $tokenoutput .= '"' . trim($brow['firstname']) . '",'; $tokenoutput .= '"' . trim($brow['lastname']) . '",'; $tokenoutput .= '"' . trim($brow['email']) . '",'; $tokenoutput .= '"' . trim($brow['emailstatus']) . '",'; $tokenoutput .= '"' . trim($brow['token']) . '",'; $tokenoutput .= '"' . trim($brow['language']) . '",'; $tokenoutput .= '"' . trim($brow['validfrom']) . '",'; $tokenoutput .= '"' . trim($brow['validuntil']) . '",'; $tokenoutput .= '"' . trim($brow['sent']) . '",'; $tokenoutput .= '"' . trim($brow['remindersent']) . '",'; $tokenoutput .= '"' . trim($brow['remindercount']) . '",'; $tokenoutput .= '"' . trim($brow['completed']) . '",'; $tokenoutput .= '"' . trim($brow['usesleft']) . '",'; foreach ($attrfieldnames as $attr_name) { $tokenoutput .= '"' . trim($brow[$attr_name]) . '",'; } $tokenoutput = substr($tokenoutput, 0, -1); // remove last comma $tokenoutput .= "\n"; echo $tokenoutput; $tokenoutput = ''; $aExportedTokens[] = $brow['tid']; } if (Yii::app()->request->getPost('tokendeleteexported') && !empty($aExportedTokens)) { Token::model($iSurveyID)->deleteByPk($aExportedTokens); } }
/** Builds the list of addon SQL select statements * that builds the query result set * * @param $allfields An array containing the names of the fields/answers we want to display in the statistics summary * @param $fieldmap The fieldmap for the survey * @param $language The language to use * * @return array $selects array of individual select statements that can be added/appended to * the 'where' portion of a SQL statement to restrict the result set * ie: array("`FIELDNAME`='Y'", "`FIELDNAME2`='Hello'"); * */ function buildSelects($allfields, $surveyid, $language) { //Create required variables $selects = array(); $aQuestionMap = array(); $fieldmap = createFieldMap($surveyid, "full", false, false, $language); foreach ($fieldmap as $field) { if (isset($field['qid']) && $field['qid'] != '') { $aQuestionMap[] = $field['sid'] . 'X' . $field['gid'] . 'X' . $field['qid']; } } // creates array of post variable names for (reset($_POST); $key = key($_POST); next($_POST)) { $postvars[] = $key; } /* * Iterate through postvars to create "nice" data for SQL later. * * Remember there might be some filters applied which have to be put into an SQL statement * * This foreach iterates through the name ($key) of each post value and builds a SELECT * statement out of it. It returns an array called $selects[] which will have a select query * for each filter chosen. ie: $select[0]="`74X71X428EXP` ='Y'"; * * This array is used later to build the overall query used to limit the number of responses * */ if (isset($postvars)) { foreach ($postvars as $pv) { //Only do this if there is actually a value for the $pv if (in_array($pv, $allfields) || in_array(substr($pv, 1), $aQuestionMap) || in_array($pv, $aQuestionMap) || ($pv[0] == 'D' || $pv[0] == 'N' || $pv[0] == 'K') && (in_array(substr($pv, 1, strlen($pv) - 2), $aQuestionMap) || in_array(substr($pv, 1, strlen($pv) - 3), $aQuestionMap) || in_array(substr($pv, 1, strlen($pv) - 5), $aQuestionMap))) { $firstletter = substr($pv, 0, 1); /* * these question types WON'T be handled here: * M = Multiple choice * T - Long Free Text * Q - Multiple Short Text * D - Date * N - Numerical Input * | - File Upload * K - Multiple Numerical Input */ if ($pv != "sid" && $pv != "display" && $firstletter != "M" && $firstletter != "P" && $firstletter != "T" && $firstletter != "Q" && $firstletter != "D" && $firstletter != "N" && $firstletter != "K" && $firstletter != "|" && $pv != "summary" && substr($pv, 0, 2) != "id" && substr($pv, 0, 9) != "datestamp") { //put together some SQL here $thisquestion = Yii::app()->db->quoteColumnName($pv) . " IN ("; foreach ($_POST[$pv] as $condition) { $thisquestion .= "'{$condition}', "; } $thisquestion = substr($thisquestion, 0, -2) . ")"; //we collect all the to be selected data in this array $selects[] = $thisquestion; } elseif ($firstletter == "M" || $firstletter == "P") { $mselects = array(); //create a list out of the $pv array list($lsid, $lgid, $lqid) = explode("X", $pv); $aresult = Question::model()->findAll(array('order' => 'question_order', 'condition' => 'parent_qid=:parent_qid AND scale_id=0', 'params' => array(":parent_qid" => $lqid))); foreach ($aresult as $arow) { // only add condition if answer has been chosen if (in_array($arow['title'], $_POST[$pv])) { $mselects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, strlen($pv)) . $arow['title']) . " = 'Y'"; } } /* If there are mutliple conditions generated from this multiple choice question, join them using the boolean "OR" */ if ($mselects) { $thismulti = implode(" OR ", $mselects); $selects[] = "({$thismulti})"; unset($mselects); } } elseif ($firstletter == "N" || $firstletter == "K") { //value greater than if (substr($pv, strlen($pv) - 1, 1) == "G" && $_POST[$pv] != "") { $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, -1)) . " > " . sanitize_int($_POST[$pv]); } //value less than if (substr($pv, strlen($pv) - 1, 1) == "L" && $_POST[$pv] != "") { $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, -1)) . " < " . sanitize_int($_POST[$pv]); } } else { if ($firstletter == "|") { // no. of files greater than if (substr($pv, strlen($pv) - 1, 1) == "G" && $_POST[$pv] != "") { $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, -1) . "_filecount") . " > " . sanitize_int($_POST[$pv]); } // no. of files less than if (substr($pv, strlen($pv) - 1, 1) == "L" && $_POST[$pv] != "") { $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, -1) . "_filecount") . " < " . sanitize_int($_POST[$pv]); } } elseif (substr($pv, 0, 2) == "id") { if (substr($pv, strlen($pv) - 1, 1) == "G" && $_POST[$pv] != "") { $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 0, -1)) . " > " . sanitize_int($_POST[$pv]); } if (substr($pv, strlen($pv) - 1, 1) == "L" && $_POST[$pv] != "") { $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 0, -1)) . " < " . sanitize_int($_POST[$pv]); } } elseif (($firstletter == "T" || $firstletter == "Q") && $_POST[$pv] != "") { $selectSubs = array(); //We intepret and * and % as wildcard matches, and use ' OR ' and , as the separators $pvParts = explode(",", str_replace('*', '%', str_replace(' OR ', ',', $_POST[$pv]))); if (is_array($pvParts) and count($pvParts)) { foreach ($pvParts as $pvPart) { $selectSubs[] = Yii::app()->db->quoteColumnName(substr($pv, 1, strlen($pv))) . " LIKE '" . trim($pvPart) . "'"; } if (count($selectSubs)) { $selects[] = ' (' . implode(' OR ', $selectSubs) . ') '; } } } elseif ($firstletter == "D" && $_POST[$pv] != "") { //Date equals if (substr($pv, -2) == "eq") { $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, strlen($pv) - 3)) . " = " . dbQuoteAll($_POST[$pv]); } else { //date less than if (substr($pv, -4) == "less") { $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, strlen($pv) - 5)) . " >= " . dbQuoteAll($_POST[$pv]); } //date greater than if (substr($pv, -4) == "more") { $selects[] = Yii::app()->db->quoteColumnName(substr($pv, 1, strlen($pv) - 5)) . " <= " . dbQuoteAll($_POST[$pv]); } } } elseif (substr($pv, 0, 9) == "datestamp") { //timestamp equals $formatdata = getDateFormatData(Yii::app()->session['dateformat']); if (substr($pv, -1, 1) == "E" && !empty($_POST[$pv])) { $datetimeobj = new Date_Time_Converter($_POST[$pv], $formatdata['phpdate'] . ' H:i'); $sDateValue = $datetimeobj->convert("Y-m-d"); $selects[] = Yii::app()->db->quoteColumnName('datestamp') . " >= " . dbQuoteAll($sDateValue . " 00:00:00") . " and " . Yii::app()->db->quoteColumnName('datestamp') . " <= " . dbQuoteAll($sDateValue . " 23:59:59"); } else { //timestamp less than if (substr($pv, -1, 1) == "L" && !empty($_POST[$pv])) { $datetimeobj = new Date_Time_Converter($_POST[$pv], $formatdata['phpdate'] . ' H:i'); $sDateValue = $datetimeobj->convert("Y-m-d H:i:s"); $selects[] = Yii::app()->db->quoteColumnName('datestamp') . " < " . dbQuoteAll($sDateValue); } //timestamp greater than if (substr($pv, -1, 1) == "G" && !empty($_POST[$pv])) { $datetimeobj = new Date_Time_Converter($_POST[$pv], $formatdata['phpdate'] . ' H:i'); $sDateValue = $datetimeobj->convert("Y-m-d H:i:s"); $selects[] = Yii::app()->db->quoteColumnName('datestamp') . " > " . dbQuoteAll($sDateValue); } } } } } } } //end foreach -> loop through filter options to create SQL return $selects; }
/** * Write values to database. * @param <type> $updatedValues * @param <boolean> $finished - true if the survey needs to be finalized */ private function _UpdateValuesInDatabase($updatedValues, $finished = false) { // TODO - now that using $this->updatedValues, may be able to remove local copies of it (unless needed by other sub-systems) $updatedValues = $this->updatedValues; $message = ''; if (!$this->surveyOptions['active'] || $this->sPreviewMode) { return $message; } if (!isset($_SESSION[$this->sessid]['srid'])) { $_SESSION[$this->sessid]['datestamp'] = dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", $this->surveyOptions['timeadjust']); // Create initial insert row for this record $today = dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", $this->surveyOptions['timeadjust']); $sdata = array("startlanguage" => $this->surveyOptions['startlanguage']); if ($this->surveyOptions['anonymized'] == false) { $sdata['token'] = $this->surveyOptions['token']; } if ($this->surveyOptions['datestamp'] == true) { $sdata['datestamp'] = $_SESSION[$this->sessid]['datestamp']; $sdata['startdate'] = $_SESSION[$this->sessid]['datestamp']; } if ($this->surveyOptions['ipaddr'] == true) { $sdata['ipaddr'] = getIPAddress(); } if ($this->surveyOptions['refurl'] == true) { if (isset($_SESSION[$this->sessid]['refurl'])) { $sdata['refurl'] = $_SESSION[$this->sessid]['refurl']; } else { $sdata['refurl'] = getenv("HTTP_REFERER"); } } $sdata = array_filter($sdata); SurveyDynamic::sid($this->sid); $oSurvey = new SurveyDynamic(); $iNewID = $oSurvey->insertRecords($sdata); if ($iNewID) { $srid = $iNewID; $_SESSION[$this->sessid]['srid'] = $iNewID; } else { $message .= $this->gT("Unable to insert record into survey table"); // TODO - add SQL error? echo submitfailed(''); // TODO - report SQL error? } //Insert Row for Timings, if needed if ($this->surveyOptions['savetimings']) { SurveyTimingDynamic::sid($this->sid); $oSurveyTimings = new SurveyTimingDynamic(); $tdata = array('id' => $srid, 'interviewtime' => 0); switchMSSQLIdentityInsert("survey_{$this->sid}_timings", true); $iNewID = $oSurveyTimings->insertRecords($tdata); switchMSSQLIdentityInsert("survey_{$this->sid}_timings", false); } } if (count($updatedValues) > 0 || $finished) { $query = 'UPDATE ' . $this->surveyOptions['tablename'] . ' SET '; $setter = array(); switch ($this->surveyMode) { case 'question': $thisstep = $this->currentQuestionSeq; break; case 'group': $thisstep = $this->currentGroupSeq; break; case 'survey': $thisstep = 1; break; } $setter[] = dbQuoteID('lastpage') . "=" . dbQuoteAll($thisstep); if ($this->surveyOptions['datestamp'] && isset($_SESSION[$this->sessid]['datestamp'])) { $_SESSION[$this->sessid]['datestamp'] = dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", $this->surveyOptions['timeadjust']); $setter[] = dbQuoteID('datestamp') . "=" . dbQuoteAll(dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", $this->surveyOptions['timeadjust'])); } if ($this->surveyOptions['ipaddr']) { $setter[] = dbQuoteID('ipaddr') . "=" . dbQuoteAll(getIPAddress()); } foreach ($updatedValues as $key => $value) { $val = is_null($value) ? NULL : $value['value']; $type = is_null($value) ? NULL : $value['type']; // Clean up the values to cope with database storage requirements switch ($type) { case 'D': //DATE if (trim($val) == '' || $val == "INVALID") { $val = NULL; // since some databases can't store blanks in date fields } // otherwise will already be in yyyy-mm-dd format after ProcessCurrentResponses() break; case '|': //File upload // This block can be removed once we require 5.3 or later if (function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc()) { $val = addslashes($val); } break; case 'N': //NUMERICAL QUESTION TYPE //NUMERICAL QUESTION TYPE case 'K': //MULTIPLE NUMERICAL QUESTION if (trim($val) == '') { $val = NULL; // since some databases can't store blanks in numerical inputs } break; default: break; } if (is_null($val)) { $setter[] = dbQuoteID($key) . "=NULL"; } else { $setter[] = dbQuoteID($key) . "=" . dbQuoteAll($val); } } $query .= implode(', ', $setter); $query .= " WHERE ID="; if (isset($_SESSION[$this->sessid]['srid']) && $this->surveyOptions['active']) { $query .= $_SESSION[$this->sessid]['srid']; if (!dbExecuteAssoc($query)) { echo submitfailed(''); // TODO - report SQL error? if (($this->debugLevel & LEM_DEBUG_VALIDATION_SUMMARY) == LEM_DEBUG_VALIDATION_SUMMARY) { $message .= $this->gT('Error in SQL update'); // TODO - add SQL error? } } elseif ($this->surveyOptions['savetimings']) { Yii::import("application.libraries.Save"); $cSave = new Save(); $cSave->set_answer_time(); } if ($finished) { // Delete the save control record if successfully finalize the submission $query = "DELETE FROM {{saved_control}} where srid=" . $_SESSION[$this->sessid]['srid'] . ' and sid=' . $this->sid; Yii::app()->db->createCommand($query)->execute(); if (($this->debugLevel & LEM_DEBUG_VALIDATION_SUMMARY) == LEM_DEBUG_VALIDATION_SUMMARY) { $message .= ';<br />' . $query; } } else { if ($this->surveyOptions['allowsave'] && isset($_SESSION[$this->sessid]['scid'])) { SavedControl::model()->updateByPk($_SESSION[$this->sessid]['scid'], array('saved_thisstep' => $thisstep)); } } // Check Quotas $aQuotas = checkCompletedQuota($this->sid, 'return'); if ($aQuotas && !empty($aQuotas)) { checkCompletedQuota($this->sid); // will create a page and quit: why not use it directly ? } else { if ($finished) { $sQuery = 'UPDATE ' . $this->surveyOptions['tablename'] . " SET "; if ($this->surveyOptions['datestamp']) { // Replace with date("Y-m-d H:i:s") ? See timeadjust $sQuery .= dbQuoteID('submitdate') . "=" . dbQuoteAll(dateShift(date("Y-m-d H:i:s"), "Y-m-d H:i:s", $this->surveyOptions['timeadjust'])); } else { $sQuery .= dbQuoteID('submitdate') . "=" . dbQuoteAll(date("Y-m-d H:i:s", mktime(0, 0, 0, 1, 1, 1980))); } $sQuery .= " WHERE ID=" . $_SESSION[$this->sessid]['srid']; dbExecuteAssoc($sQuery); // Checked } } } if (($this->debugLevel & LEM_DEBUG_VALIDATION_SUMMARY) == LEM_DEBUG_VALIDATION_SUMMARY) { $message .= $query; } } return $message; }