/** * 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); } } }
/** * RPC Routine to update a response in a given survey. * Routine supports only single response updates. * Response to update will be identified either by the response id, or the token if response id is missing. * Routine is only applicable for active surveys with alloweditaftercompletion = Y. * * @access public * @param string $sSessionKey Auth credentials * @param int $iSurveyID Id of the Survey to update response * @param struct $aResponseData The actual response * @return mixed TRUE(bool) on success. errormessage on error */ public function update_response($sSessionKey, $iSurveyID, $aResponseData) { if (!$this->_checkSessionKey($sSessionKey)) { return 'Invalid session key'; } $oSurvey = Survey::model()->findByPk($iSurveyID); if (is_null($oSurvey)) { return 'Error: Invalid survey ID'; } if ($oSurvey->getAttribute('active') !== 'Y') { return 'Error: Survey is not active.'; } if ($oSurvey->getAttribute('alloweditaftercompletion') !== 'Y') { return 'Error: Survey does not allow edit after completion.'; } if (Permission::model()->hasSurveyPermission($iSurveyID, 'responses', 'update')) { if (!Yii::app()->db->schema->getTable('{{survey_' . $iSurveyID . '}}')) { return 'Error: No survey response table'; } if (!isset($aResponseData['id']) && !isset($aResponseData['token'])) { return 'Error: Missing response identifier (id|token).'; } SurveyDynamic::sid($iSurveyID); $oSurveyDynamic = new SurveyDynamic(); if (isset($aResponseData['id'])) { $aResponses = $oSurveyDynamic->findAllByPk($aResponseData['id']); } else { $aResponses = $oSurveyDynamic->findAllByAttributes(array('token' => $aResponseData['token'])); } if (empty($aResponses)) { return 'Error: No matching Response.'; } if (count($aResponses) > 1) { return 'Error: More then one matching response, updateing multiple responses at once is not supported.'; } $aBasicDestinationFields = $oSurveyDynamic->tableSchema->columnNames; $aInvalidFields = array_diff_key($aResponseData, array_flip($aBasicDestinationFields)); if (count($aInvalidFields) > 0) { return 'Error: Invalid Column names supplied: ' . implode(', ', array_keys($aInvalidFields)); } unset($aResponseData['token']); foreach ($aResponseData as $sAtributeName => $value) { $aResponses[0]->setAttribute($sAtributeName, $value); } $bResult = $aResponses[0]->save(true); if ($bResult) { return $bResult; } else { return 'Unable to edit response'; } } else { return 'No permission'; } }
/** * This function import CSV file to responses table * * @param string $sFullFilePath * @param integer $iSurveyId * @param array $aOptions * Return array $result ("errors","warnings","success") */ function CSVImportResponses($sFullFilePath, $iSurveyId, $aOptions = array()) { $clang = Yii::app()->lang; // Default optional if (!isset($aOptions['bDeleteFistLine'])) { $aOptions['bDeleteFistLine'] = true; } // By default delete first line (vvimport) if (!isset($aOptions['sExistingId'])) { $aOptions['sExistingId'] = "ignore"; } // By default exclude existing id if (!isset($aOptions['bNotFinalized'])) { $aOptions['bNotFinalized'] = false; } // By default don't change finalized part if (!isset($aOptions['sCharset']) || !$aOptions['sCharset']) { $aOptions['sCharset'] = "utf8"; } if (!isset($aOptions['sSeparator'])) { $aOptions['sSeparator'] = "\t"; } if (!isset($aOptions['sQuoted'])) { $aOptions['sQuoted'] = "\""; } // Fix some part if (!array_key_exists($aOptions['sCharset'], aEncodingsArray())) { $aOptions['sCharset'] = "utf8"; } // Prepare an array of sentence for result $CSVImportResult = array(); // Read the file $handle = fopen($sFullFilePath, "r"); // Need to be adapted for Mac ? in options ? while (!feof($handle)) { $buffer = fgets($handle); //To allow for very long lines . Another option is fgetcsv (0 to length), but need mb_convert_encoding $aFileResponses[] = mb_convert_encoding($buffer, "UTF-8", $aOptions['sCharset']); } // Close the file fclose($handle); if ($aOptions['bDeleteFistLine']) { array_shift($aFileResponses); } $aRealFieldNames = Yii::app()->db->getSchema()->getTable(SurveyDynamic::model($iSurveyId)->tableName())->getColumnNames(); //$aCsvHeader=array_map("trim",explode($aOptions['sSeparator'], trim(array_shift($aFileResponses)))); $aCsvHeader = str_getcsv(array_shift($aFileResponses), $aOptions['sSeparator'], $aOptions['sQuoted']); $aLemFieldNames = LimeExpressionManager::getLEMqcode2sgqa($iSurveyId); $aKeyForFieldNames = array(); // An array assicated each fieldname with corresponding responses key if (!$aCsvHeader) { $CSVImportResult['errors'][] = $clang->gT("File seems empty or has only one line"); return $CSVImportResult; } // Assign fieldname with $aFileResponses[] key foreach ($aRealFieldNames as $sFieldName) { if (in_array($sFieldName, $aCsvHeader)) { // First pass : simple associated $aKeyForFieldNames[$sFieldName] = array_search($sFieldName, $aCsvHeader); } elseif (in_array($sFieldName, $aLemFieldNames)) { // Second pass : LEM associated $sLemFieldName = array_search($sFieldName, $aLemFieldNames); if (in_array($sLemFieldName, $aCsvHeader)) { $aKeyForFieldNames[$sFieldName] = array_search($sLemFieldName, $aCsvHeader); } elseif ($aOptions['bForceImport']) { // as fallback just map questions in order of apperance // find out where the answer data columns start in CSV if (!isset($csv_ans_start_index)) { foreach ($aCsvHeader as $i => $name) { if (preg_match('/^\\d+X\\d+X\\d+/', $name)) { $csv_ans_start_index = $i; break; } } } // find out where the answer data columns start in destination table if (!isset($table_ans_start_index)) { foreach ($aRealFieldNames as $i => $name) { if (preg_match('/^\\d+X\\d+X\\d+/', $name)) { $table_ans_start_index = $i; break; } } } // map answers in order if (isset($table_ans_start_index, $csv_ans_start_index)) { $csv_index = array_search($sFieldName, $aRealFieldNames) - $table_ans_start_index + $csv_ans_start_index; if ($csv_index < sizeof($aCsvHeader)) { $aKeyForFieldNames[$sFieldName] = $csv_index; } else { $force_import_failed = true; break; } } } } } // check if forced error failed if (isset($force_import_failed)) { $CSVImportResult['errors'][] = $clang->gT("Import failed: Forced import was requested but the input file doesn't contain enough columns to fill the survey."); return $CSVImportResult; } // make sure at least one answer was imported before commiting foreach ($aKeyForFieldNames as $field => $index) { if (preg_match('/^\\d+X\\d+X\\d+/', $field)) { $import_ok = true; break; } } if (!isset($import_ok)) { $CSVImportResult['errors'][] = $clang->gT("Import failed: No answers could be mapped."); return $CSVImportResult; } // Now it's time to import // Some var to return $iNbResponseLine = 0; $iNbResponseExisting = 0; $aResponsesInserted = array(); $aResponsesUpdated = array(); $aResponsesError = array(); $aExistingsId = array(); $iMaxId = 0; // If we set the id, keep the max // Some specific header (with options) $iIdKey = array_search('id', $aCsvHeader); // the id is allways needed and used a lot if (is_int($iIdKey)) { unset($aKeyForFieldNames['id']); } $iSubmitdateKey = array_search('submitdate', $aCsvHeader); // submitdate can be forced to null if (is_int($iSubmitdateKey)) { unset($aKeyForFieldNames['submitdate']); } $iIdReponsesKey = is_int($iIdKey) ? $iIdKey : 0; // The key for reponses id: id column or first column if not exist // Import each responses line here while ($sResponses = array_shift($aFileResponses)) { $iNbResponseLine++; $bExistingsId = false; $aResponses = str_getcsv($sResponses, $aOptions['sSeparator'], $aOptions['sQuoted']); if ($iIdKey !== false) { $oSurvey = SurveyDynamic::model($iSurveyId)->findByPk($aResponses[$iIdKey]); if ($oSurvey) { $bExistingsId = true; $aExistingsId[] = $aResponses[$iIdKey]; // Do according to option switch ($aOptions['sExistingId']) { case 'replace': SurveyDynamic::model($iSurveyId)->deleteByPk($aResponses[$iIdKey]); SurveyDynamic::sid($iSurveyId); $oSurvey = new SurveyDynamic(); break; case 'replaceanswers': break; case 'renumber': SurveyDynamic::sid($iSurveyId); $oSurvey = new SurveyDynamic(); break; case 'skip': case 'ignore': default: $oSurvey = false; // Remove existing survey : don't import again break; } } else { SurveyDynamic::sid($iSurveyId); $oSurvey = new SurveyDynamic(); } } else { SurveyDynamic::sid($iSurveyId); $oSurvey = new SurveyDynamic(); } if ($oSurvey) { // First rule for id and submitdate if (is_int($iIdKey)) { if (!$bExistingsId) { $oSurvey->id = $aResponses[$iIdKey]; $iMaxId = $aResponses[$iIdKey] > $iMaxId ? $aResponses[$iIdKey] : $iMaxId; } elseif ($aOptions['sExistingId'] == 'replace' || $aOptions['sExistingId'] == 'replaceanswers') { $oSurvey->id = $aResponses[$iIdKey]; } } if ($aOptions['bNotFinalized']) { $oSurvey->submitdate = new CDbExpression('NULL'); } elseif (is_int($iSubmitdateKey)) { if ($aResponses[$iSubmitdateKey] == '{question_not_shown}' || trim($aResponses[$iSubmitdateKey] == '')) { $oSurvey->submitdate = new CDbExpression('NULL'); } else { // Maybe control valid date : see http://php.net/manual/en/function.checkdate.php#78362 for example $oSurvey->submitdate = $aResponses[$iSubmitdateKey]; } } foreach ($aKeyForFieldNames as $sFieldName => $iFieldKey) { if ($aResponses[$iFieldKey] == '{question_not_shown}') { $oSurvey->{$sFieldName} = new CDbExpression('NULL'); } else { $sResponse = str_replace(array("{quote}", "{tab}", "{cr}", "{newline}", "{lbrace}"), array("\"", "\t", "\r", "\n", "{"), $aResponses[$iFieldKey]); $oSurvey->{$sFieldName} = $sResponse; } } // We use transaction to prevent DB error $oTransaction = Yii::app()->db->beginTransaction(); try { if (isset($oSurvey->id) && !is_null($oSurvey->id)) { switchMSSQLIdentityInsert('survey_' . $iSurveyId, true); $bSwitched = true; } if ($oSurvey->save()) { $oTransaction->commit(); if ($bExistingsId && $aOptions['sExistingId'] != 'renumber') { $aResponsesUpdated[] = $aResponses[$iIdReponsesKey]; } else { $aResponsesInserted[] = $aResponses[$iIdReponsesKey]; } } else { $oTransaction->rollBack(); $aResponsesError[] = $aResponses[$iIdReponsesKey]; } if (isset($bSwitched) && $bSwitched == true) { switchMSSQLIdentityInsert('survey_' . $iSurveyId, false); $bSwitched = false; } } catch (Exception $oException) { $oTransaction->rollBack(); $aResponsesError[] = $aResponses[$iIdReponsesKey]; // Show some error to user ? // $CSVImportResult['errors'][]=$oException->getMessage(); // Show it in view // tracevar($oException->getMessage());// Show it in console (if debug is set) } } } // Fix max next id (for pgsql) // mysql dot need fix, but what for mssql ? // Do a model function for this can be a good idea (see activate_helper/activateSurvey) if (Yii::app()->db->driverName == 'pgsql') { $sSequenceName = Yii::app()->db->getSchema()->getTable("{{survey_{$iSurveyId}}}")->sequenceName; $iActualSerial = Yii::app()->db->createCommand("SELECT last_value FROM {$sSequenceName}")->queryScalar(); if ($iActualSerial < $iMaxId) { $sQuery = "SELECT setval(pg_get_serial_sequence('{{survey_{$iSurveyId}}}', 'id'),{$iMaxId},false);"; $result = @Yii::app()->db->createCommand($sQuery)->execute(); } } // End of import // Construction of returned information if ($iNbResponseLine) { $CSVImportResult['success'][] = sprintf($clang->gT("%s response lines in your file."), $iNbResponseLine); } else { $CSVImportResult['errors'][] = $clang->gT("No response lines in your file."); } if (count($aResponsesInserted)) { $CSVImportResult['success'][] = sprintf($clang->gT("%s responses were inserted."), count($aResponsesInserted)); // Maybe add implode aResponsesInserted array } if (count($aResponsesUpdated)) { $CSVImportResult['success'][] = sprintf($clang->gT("%s responses were updated."), count($aResponsesUpdated)); } if (count($aResponsesError)) { $CSVImportResult['errors'][] = sprintf($clang->gT("%s responses cannot be inserted or updated."), count($aResponsesError)); } if (count($aExistingsId) && ($aOptions['sExistingId'] == 'skip' || $aOptions['sExistingId'] == 'ignore')) { $CSVImportResult['warnings'][] = sprintf($clang->gT("%s responses already exist."), count($aExistingsId)); } return $CSVImportResult; }
/** * 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; }
/** * Sets the survey ID for the next model * * @static * @access public * @param int $sid * @return void */ public static function sid($sid) { self::$sid = (int) $sid; }
/** * Returns this model's relations * * @access public * @return array */ public function relations() { SurveyDynamic::sid(self::$sid); return array('survey' => array(self::BELONGS_TO, 'Survey', array(), 'condition' => 'sid=' . self::$sid, 'together' => true), 'responses' => array(self::HAS_MANY, 'SurveyDynamic', array('token' => 'token'))); }