/** * dataentry::insert() * insert new dataentry * @return */ public function insert() { $clang = Yii::app()->lang; $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, 'clang' => $clang); if (hasSurveyPermission($surveyid, 'responses', 'read')) { if ($subaction == "insert" && hasSurveyPermission($surveyid, 'responses', 'create')) { $surveytable = "{{survey_{$surveyid}}}"; $thissurvey = getSurveyInfo($surveyid); $errormsg = ""; Yii::app()->loadHelper("database"); $aViewUrls['display']['menu_bars']['browse'] = $clang->gT("Data entry"); $aDataentryoutput = ''; $aDataentrymsgs = array(); $hiddenfields = ''; $lastanswfortoken = ''; // check if a previous answer has been submitted or saved $rlanguage = ''; if (isset($_POST['token'])) { $tokencompleted = ""; $tcquery = "SELECT completed from {{tokens_{$surveyid}}} WHERE token='{$_POST['token']}'"; //dbQuoteAll($_POST['token'],true); $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='" . $_POST['token'] . "'"; //dbQuoteAll($_POST['token'],true); $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'), $clang->gT("Error")); $errormsg .= CHtml::tag('p', array(), $clang->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'), $clang->gT("Error")); $errormsg .= CHtml::tag('p', array(), $clang->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'), $clang->gT("Error")); $errormsg .= CHtml::tag('p', array(), $clang->gT("There is already a recorded answer for this token")); if ($lastanswfortoken != 'PrivacyProtected') { $errormsg .= "<br /><br />" . $clang->gT("Follow the following link to update it") . ":\n"; $errormsg .= CHtml::link("[id:{$lastanswfortoken}]", Yii::app()->baseUrl . ('/admin/dataentry/editdata/subaction/edit/id/' . $lastanswfortoken . '/surveyid/' . $surveyid . '/lang/' . $rlanguage), array('title' => $clang->gT("Edit this entry"))); } else { $errormsg .= "<br /><br />" . $clang->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 .= $clang->gT("Error") . ": " . $clang->gT("You must supply a name for this saved session."); } if (!$saver['password']) { $errormsg .= $clang->gT("Error") . ": " . $clang->gT("You must supply a password for this saved session."); } if ($saver['password'] != $saver['passwordconfirm']) { $errormsg .= $clang->gT("Error") . ": " . $clang->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'], $irow['type']); $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]; } } } } Survey_dynamic::sid($surveyid); $new_response = new Survey_dynamic(); 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='" . $_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='{$submitdate}'\n"; } 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='" . $_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 $srid = Yii::app()->db->getLastInsertID(); // Yii::app()->db->getLastInsertID(); $sdquery = "UPDATE {{survey_{$surveyid}}} SET submitdate='" . $submitdate . "' WHERE id={$srid}\n"; $sdresult = dbExecuteAssoc($sdquery) or safeDie("Couldn't set submitdate response in survey table!<br />\n{$sdquery}<br />\n"); $last_db_id = Yii::app()->db->getLastInsertID(); } if (isset($_POST['save']) && $_POST['save'] == "on") { $srid = Yii::app()->db->getLastInsertID(); //Yii::app()->db->getLastInsertID(); $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 = Yii::app()->db->getLastInsertID(); // Yii::app()->db->getLastInsertID("{{saved_control}}","scid"); $aDataentrymsgs[] = CHtml::tag('font', array('class' => 'successtitle'), $clang->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}}}"; $last_db_id = Yii::app()->db->getLastInsertID(); 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) . ")"; //$this->tokens_dynamic_model->insertToken($surveyid,$tokendata); dbExecuteAssoc($SQL); //Yii::app()->db->AutoExecute(db_table_name("tokens_".$surveyid), $tokendata,'INSERT'); $aDataentrymsgs[] = CHtml::tag('font', array('class' => 'successtitle'), $clang->gT("A token entry for the saved survey has been created too.")); //$aDataentryoutput .= "<font class='successtitle'></font><br />\n"; $last_db_id = Yii::app()->db->getLastInsertID(); } if ($saver['email']) { //Send email if (validateEmailAddress($saver['email']) && !returnGlobal('redo')) { $subject = $clang->gT("Saved Survey Details"); $message = $clang->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 .= $clang->gT("Name") . ": " . $saver['identifier'] . "\n"; $message .= $clang->gT("Password") . ": " . $saver['password'] . "\n\n"; $message .= $clang->gT("Reload your survey by clicking on the following link (or pasting it into your browser):") . ":\n"; $message .= Yii::app()->getConfig('publicurl') . "/index.php?sid={$surveyid}&loadall=reload&scid=" . $scid . "&lang=" . urlencode($saver['language']) . "&loadname=" . urlencode($saver['identifier']) . "&loadpass="******"&token=" . $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'), $clang->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; $this->_renderWrappedTemplate('dataentry', 'insert', $aData); } } }