"
." ".$clang->gT("A token table has been created for this survey and the old tokens were imported.")." (\"".$dbprefix."tokens_$surveyid\")
\n"
."\n"
."
\n";
return;
}
else
{
$query=db_select_tables_like("{$dbprefix}old_tokens_".$surveyid."_%");
$result=db_execute_num($query) or safe_die("Couldn't get old table list ".$query." ".$connect->ErrorMsg());
$tcount=$result->RecordCount();
if ($tcount > 0)
{
while($rows=$result->FetchRow())
{
$oldlist[]=$rows[0];
}
}
$tokenoutput .= "\t
\n"
."
".$clang->gT("Warning")."
\n"
." ".$clang->gT("Tokens have not been initialised for this survey.")."
\n";
if (bHasRight($surveyid, 'edit_survey_property') || bHasRight($surveyid, 'activate_survey'))
{
$tokenoutput .= "".$clang->gT("If you initialise tokens for this survey then this survey will only be accessible to users who provide a token either manually or by URL.")
."
\n";
$thissurvey=getSurveyInfo($surveyid);
if ($thissurvey['private'] == 'Y')
{
$tokenoutput .= "".$clang->gT("Note: If you turn on the -Anonymous answers- option for this survey then LimeSurvey will mark your completed tokens only with a 'Y' instead of date/time to ensure the anonymity of your participants.")
."
\n";
}
$tokenoutput .= "".$clang->gT("Do you want to create a token table for this survey?");
$tokenoutput .= "
\n";
$tokenoutput .= "\n";
}
$tokenoutput .= "
\n";
// Do not offer old postgres token tables for restore since these are having an issue with missing index
if ($tcount>0 && $databasetype!='postgres' &&
(bHasRight($surveyid, 'edit_survey_property') || bHasRight($surveyid, 'activate_survey'))
)
{
$tokenoutput .= "
".$clang->gT("Restore options")."
\n"
."
\n"
."
\n";
}
return;
}
}
#Lookup the names of the attributes
/*$query = "SELECT attribute1, attribute2 FROM ".db_table_name('surveys')." WHERE sid=$surveyid";
$result = db_execute_assoc($query) or safe_die("Couldn't execute query: $query ".$connect->ErrorMsg());
$row = $result->FetchRow();
if ($row["attribute1"]) {$attr1_name = $row["attribute1"];} else {$attr1_name=$clang->gT("Attribute 1");}
if ($row["attribute2"]) {$attr2_name = $row["attribute2"];} else {$attr2_name=$clang->gT("Attribute 2");}*/
// IF WE MADE IT THIS FAR, THEN THERE IS A TOKENS TABLE, SO LETS DEVELOP THE MENU ITEMS
$tokenoutput .= "\t
\n";
// SEE HOW MANY RECORDS ARE IN THE TOKEN TABLE
$tksq = "SELECT count(tid) FROM ".db_table_name("tokens_$surveyid");
$tksr = db_execute_num($tksq);
$tkr = $tksr->FetchRow();
$tkcount = $tkr[0];
// GIVE SOME INFORMATION ABOUT THE TOKENS
if ($subaction==''){
$tokenoutput .= "\t
".$clang->gT("Token summary")."
\n"
."
\n"
."\t
\n"
."
\n"
.$clang->gT("Total records in this token table")."
$tkcount
\n";
$tksq = "SELECT count(*) FROM ".db_table_name("tokens_$surveyid")." WHERE token IS NULL OR token=''";
$tksr = db_execute_num($tksq);
while ($tkr = $tksr->FetchRow())
{$tokenoutput .= "
".$clang->gT("Total with no unique Token")."
$tkr[0] / $tkcount
\n";}
$tksq = "SELECT count(*) FROM ".db_table_name("tokens_$surveyid")." WHERE (sent!='N' and sent<>'')";
$tksr = db_execute_num($tksq);
while ($tkr = $tksr->FetchRow())
{$tokenoutput .= "
".$clang->gT("Total invitations sent")."
$tkr[0] / $tkcount
\n";}
$tksq = "SELECT count(*) FROM ".db_table_name("tokens_$surveyid")." WHERE (completed!='N' and completed<>'')";
$tksr = db_execute_num($tksq) or safe_die ("Couldn't execute token selection query $abquery ".$connect->ErrorMsg());
while ($tkr = $tksr->FetchRow())
{$tokenoutput .= "
".$clang->gT("Total surveys completed")."
$tkr[0] / $tkcount\n";}
$tokenoutput .= "
\n"
."\t
\n"
."
\n";
}
#############################################################################################
// NOW FOR VARIOUS ACTIONS:
if(isset($surveyid) && getEmailFormat($surveyid) == 'html')
{
$ishtml=true;
}
else
{
$ishtml=false;
}
if ($subaction == "exportdialog" && bHasRight($surveyid, 'export') )//EXPORT FEATURE SUBMITTED BY PIETERJAN HEYSE
{
$langquery = "SELECT language FROM ".db_table_name("tokens_$surveyid")." group by language";
$langresult = db_execute_assoc($langquery);
$tokenoutput .= "\t
\n";
// ToDo: Just delete it if there is no token in the table
if (!isset($_POST['ok']) || !$_POST['ok'])
{
$tokenoutput .= "
".$clang->gT("Warning")."
\n"
.$clang->gT("If you delete this table tokens will no longer be required to access this survey.")." ".$clang->gT("A backup of this table will be made if you proceed. Your system administrator will be able to access this table.")." \n"
."( \"old_tokens_{$surveyid}_$date\" )
\n"
."\n"
."\n";
}
elseif (isset($_POST['ok']) && $_POST['ok'] == "surething")
{
$oldtable = "tokens_$surveyid";
$newtable = "old_tokens_{$surveyid}_$date";
$deactivatequery = db_rename_table( db_table_name_nq($oldtable), db_table_name_nq($newtable));
if ($databasetype=='postgres')
{
// If you deactivate a postgres table you have to rename the according sequence too and alter the id field to point to the changed sequence
$oldTableJur = db_table_name_nq($oldtable);
$deactivatequery = db_rename_table(db_table_name_nq($oldtable),db_table_name_nq($newtable).'_tid_seq');
$deactivateresult = $connect->Execute($deactivatequery) or die ("oldtable : ".$oldtable. " / oldtableJur : ". $oldTableJur . " / ".htmlspecialchars($deactivatequery)." / Could not rename the old sequence for this token table. The database reported the following error: ".htmlspecialchars($connect->ErrorMsg())."
".$clang->gT("Main Admin Screen")."");
$setsequence="ALTER TABLE ".db_table_name_nq($newtable)."_tid_seq ALTER COLUMN tid SET DEFAULT nextval('".db_table_name_nq($newtable)."_tid_seq'::regclass);";
$deactivateresult = $connect->Execute($setsequence) or die (htmlspecialchars($setsequence)." Could not alter the field tid to point to the new sequence name for this token table. The database reported the following error: ".htmlspecialchars($connect->ErrorMsg())."
Survey was not deactivated either.
".$clang->gT("Main Admin Screen")."");
$setidx="ALTER INDEX ".db_table_name_nq($oldtable)."_idx RENAME TO ".db_table_name_nq($newtable)."_idx;";
$deactivateresult = $connect->Execute($setidx) or die (htmlspecialchars($setidx)." Could not alter the index for this token table. The database reported the following error: ".htmlspecialchars($connect->ErrorMsg())."
Survey was not deactivated either.
".$clang->gT("Main Admin Screen")."");
} else {
$deactivateresult = $connect->Execute($deactivatequery) or die ("Couldn't deactivate because: \n".htmlspecialchars($connect->ErrorMsg())." - Query: ".htmlspecialchars($deactivatequery)."
\nAdmin\n");
}
$tokenoutput .= ' '.$clang->gT("The tokens table has now been removed and tokens are no longer required to access this survey.")." ".$clang->gT("A backup of this table has been made and can be accessed by your system administrator.")." \n"
."(\"{$dbprefix}old_tokens_{$surveyid}_$date\")"."
\n";
if (isset($tokenid)) {$tokenoutput .= " (".$clang->gT("Sending to Token ID").": {$tokenid})";}
if (isset($tokenids)) {$tokenoutput .= " (".$clang->gT("Sending to Token IDs").": ".implode(", ", $tokenids).")";}
$tokenoutput .= " \n";
if (isset($_POST['bypassbademails']) && $_POST['bypassbademails'] == 'Y')
{
$SQLemailstatuscondition = " AND emailstatus = 'OK'";
}
else
{
$SQLemailstatuscondition = " AND emailstatus <> 'OptOut'";
}
$ctquery = "SELECT * FROM ".db_table_name("tokens_{$surveyid}")." WHERE ((completed ='N') or (completed='')) AND ((sent ='N') or (sent='')) AND token !='' AND email != '' $SQLemailstatuscondition";
if (isset($tokenid)) {$ctquery .= " AND tid='{$tokenid}'";}
if (isset($tokenids)) {$ctquery .= " AND tid IN ('".implode("', '", $tokenids)."')";}
$tokenoutput .= "\n";
$ctresult = $connect->Execute($ctquery) or safe_die("Database error! \n" . $connect->ErrorMsg());
$ctcount = $ctresult->RecordCount();
$ctfieldcount = $ctresult->FieldCount();
$emquery = "SELECT * FROM ".db_table_name("tokens_{$surveyid}")." WHERE ((completed ='N') or (completed='')) AND ((sent ='N') or (sent='')) AND token !='' AND email != '' $SQLemailstatuscondition";
if (isset($tokenid)) {$emquery .= " and tid='{$tokenid}'";}
if (isset($tokenids)) {$emquery .= " AND tid IN ('".implode("', '", $tokenids)."')";}
$tokenoutput .= "\n\n\n\n";
$emresult = db_select_limit_assoc($emquery,$maxemails) or safe_die ("Couldn't do query. \n$emquery \n".$connect->ErrorMsg());
$emcount = $emresult->RecordCount();
$tokenoutput .= "
\n";
$surveylangs = GetAdditionalLanguagesFromSurveyID($surveyid);
$baselanguage = GetBaseLanguageFromSurveyID($surveyid);
array_unshift($surveylangs,$baselanguage);
foreach ($surveylangs as $language)
{
$_POST['message_'.$language]=auto_unescape($_POST['message_'.$language]);
$_POST['subject_'.$language]=auto_unescape($_POST['subject_'.$language]);
if ($ishtml) $_POST['message_'.$language] = html_entity_decode($_POST['message_'.$language], ENT_QUOTES, $emailcharset);
}
$attributes=GetTokenFieldsAndNames($surveyid);
if ($emcount > 0)
{
while ($emrow = $emresult->FetchRow())
{
unset($fieldsarray);
$to = $emrow['email'];
$fieldsarray["{EMAIL}"]=$emrow['email'];
$fieldsarray["{FIRSTNAME}"]=$emrow['firstname'];
$fieldsarray["{LASTNAME}"]=$emrow['lastname'];
$fieldsarray["{TOKEN}"]=$emrow['token'];
$fieldsarray["{LANGUAGE}"]=$emrow['language'];
foreach ($attributes as $attributefield=>$attributedescription)
{
$fieldsarray['{'.strtoupper($attributefield).'}']=$emrow[$attributefield];
}
$emrow['language']=trim($emrow['language']);
if ($emrow['language']=='') {$emrow['language']=$baselanguage;} //if language is not given use default
$found = array_search($emrow['language'], $surveylangs);
if ($found==false) {$emrow['language']=$baselanguage;}
$from = $_POST['from_'.$emrow['language']];
if ($ishtml === false)
{
$fieldsarray["{OPTOUTURL}"]="$publicurl/optout.php?lang=".trim($emrow['language'])."&sid=$surveyid&token={$emrow['token']}";
if ( $modrewrite )
{
$fieldsarray["{SURVEYURL}"]="$publicurl/$surveyid/lang-".trim($emrow['language'])."/tk-{$emrow['token']}";
}
else
{
$fieldsarray["{SURVEYURL}"]="$publicurl/index.php?lang=".trim($emrow['language'])."&sid=$surveyid&token={$emrow['token']}";
}
}
else
{
$fieldsarray["{OPTOUTURL}"]="".htmlspecialchars("$publicurl/optout.php?lang=".trim($emrow['language'])."&sid=$surveyid&token={$emrow['token']}")."";
if ( $modrewrite )
{
$fieldsarray["{SURVEYURL}"]="".htmlspecialchars("$publicurl/$surveyid/lang-".trim($emrow['language'])."/tk-{$emrow['token']}")."";
$fieldsarray["@@SURVEYURL@@"]="$publicurl/$surveyid/lang-".trim($emrow['language'])."/tk-{$emrow['token']}";
}
else
{
$fieldsarray["{SURVEYURL}"]="".htmlspecialchars("$publicurl/index.php?lang=".trim($emrow['language'])."&sid=$surveyid&token={$emrow['token']}")."";
$fieldsarray["@@SURVEYURL@@"]="$publicurl/index.php?lang=".trim($emrow['language'])."&sid=$surveyid&token={$emrow['token']}";
}
}
$modsubject=Replacefields($_POST['subject_'.$emrow['language']], $fieldsarray);
$modmessage=Replacefields($_POST['message_'.$emrow['language']], $fieldsarray);
if (trim($emrow['validfrom'])!='' && convertDateTimeFormat($emrow['validfrom'],'Y-m-d H:i:s','U')*1>date('U')*1)
{
$tokenoutput .= $emrow['tid'] ." ".ReplaceFields($clang->gT("Email to {FIRSTNAME} {LASTNAME} ({EMAIL}) delayed: Token is not yet valid.")." ", $fieldsarray);
}
elseif (trim($emrow['validuntil'])!='' && convertDateTimeFormat($emrow['validuntil'],'Y-m-d H:i:s','U')*1gT("Email to {FIRSTNAME} {LASTNAME} ({EMAIL}) skipped: Token is not valid anymore.")." ", $fieldsarray);
}
elseif (SendEmailMessage($modmessage, $modsubject, $to , $from, $sitename, $ishtml, getBounceEmail($surveyid)))
{
// Put date into sent
$today = date_shift(date("Y-m-d H:i:s"), "Y-m-d H:i", $timeadjust);
$udequery = "UPDATE ".db_table_name("tokens_{$surveyid}")."\n"
."SET sent='$today' WHERE tid={$emrow['tid']}";
//
$uderesult = $connect->Execute($udequery) or safe_die ("Could not update tokens $udequery ".$connect->ErrorMsg());
$tokenoutput .= $clang->gT("Invitation sent to:")." {$emrow['firstname']} {$emrow['lastname']} ($to) \n";
if ($emailsmtpdebug==2)
{
$tokenoutput .=$maildebug;
}
}
else
{
$tokenoutput .= '
".$clang->gT("Warning")." \n"
.$clang->gT("There were no eligible emails to send. This will be because none satisfied the criteria of - having an email address, having been sent an invitation, but not having yet completed the survey.")."\n"
."
\n";
if (!isset($_POST['ok']) || !$_POST['ok'])
{
$tokenoutput .= "".$clang->gT("Clicking yes will generate tokens for all those in this token list that have not been issued one. Is this OK?")."
\n"
."\n"
.$clang->gT("Yes")."' onclick=\"".get2post("$scriptname?action=tokens&sid=$surveyid&subaction=tokenify&ok=Y")."\" />\n"
."\n"
." \n";
}
else
{
//get token length from survey settings
$tlquery = "SELECT tokenlength FROM ".db_table_name("surveys")." WHERE sid=$surveyid";
$tlresult = db_execute_assoc($tlquery);
while ($tlrow = $tlresult->FetchRow())
{
$tokenlength = $tlrow['tokenlength'];
}
//if tokenlength is not set or there are other problems use the default value (15)
if(!isset($tokenlength) || $tokenlength == '')
{
$tokenlength = 15;
}
// select all existing tokens
$ntquery = "SELECT token FROM ".db_table_name("tokens_$surveyid")." group by token";
$ntresult = db_execute_assoc($ntquery);
while ($tkrow = $ntresult->FetchRow())
{
$existingtokens[$tkrow['token']]=null;
}
$newtokencount = 0;
$tkquery = "SELECT tid FROM ".db_table_name("tokens_$surveyid")." WHERE token IS NULL OR token=''";
$tkresult = db_execute_assoc($tkquery) or safe_die ("Mucked up! $tkquery ".$connect->ErrorMsg());
while ($tkrow = $tkresult->FetchRow())
{
$isvalidtoken = false;
while ($isvalidtoken == false)
{
$newtoken = randomkey($tokenlength);
if (!isset($existingtokens[$newtoken])) {
$isvalidtoken = true;
$existingtokens[$newtoken]=null;
}
}
$itquery = "UPDATE ".db_table_name("tokens_$surveyid")." SET token='$newtoken' WHERE tid={$tkrow['tid']}";
$itresult = $connect->Execute($itquery);
$newtokencount++;
}
$message=str_replace("{TOKENCOUNT}", $newtokencount, $clang->gT("{TOKENCOUNT} tokens have been created"));
$tokenoutput .= "
".$clang->gT("File should be a standard CSV (comma delimited) file with optional double quotes around values (default for OpenOffice and Excel). The first line must contain the field names. The fields can be in any order.").'
\n";
$xz = 0; $recordcount = 0; $xv = 0;
// This allows to read file with MAC line endings too
@ini_set('auto_detect_line_endings', true);
// open it and trim the ednings
$tokenlistarray = file($the_full_file_path);
$baselanguage=GetBaseLanguageFromSurveyID($surveyid);
if (!isset($tokenlistarray))
{
$tokenoutput .= "
".$clang->gT("Failed to open the uploaded file!")."
\n";
}
foreach ($tokenlistarray as $buffer)
{
$buffer=@mb_convert_encoding($buffer,"UTF-8",$uploadcharset);
$firstname = ""; $lastname = ""; $email = ""; $emailstatus="OK"; $token = ""; $language=""; $attribute1=""; $attribute2=""; //Clear out values from the last path, in case the next line is missing a value
if ($recordcount==0)
{
// Pick apart the first line
$buffer=removeBOM($buffer);
$allowedfieldnames=array('firstname','lastname','email','emailstatus','token','language', 'validfrom', 'validuntil');
$allowedfieldnames=array_merge($attrfieldnames,$allowedfieldnames);
$firstline = convertCSVRowToArray($buffer,',','"');
$firstline=array_map('trim',$firstline);
$ignoredcolumns=array();
//now check the first line for invalid fields
foreach ($firstline as $index=>$fieldname)
{
$firstline[$index] = preg_replace("/(.*) <[^,]*>$/","$1",$fieldname);
$fieldname = $firstline[$index];
if (!in_array($fieldname,$allowedfieldnames))
{
$ignoredcolumns[]=$fieldname;
}
}
if (!in_array('firstname',$firstline) || !in_array('lastname',$firstline) || !in_array('email',$firstline))
{
$tokenoutput .= "
".$clang->gT("Error: Your uploaded file is missing one or more of the mandatory columns: 'firstname', 'lastname' or 'email'")."
";
$recordcount=count($tokenlistarray);
break;
}
}
else
{
$line = convertCSVRowToArray($buffer,',','"');
if (count($firstline)!=count($line))
{
$invalidformatlist[]=$recordcount;
$recordcount++;
continue;
}
$writearray=array_combine($firstline,$line);
//kick out ignored columns
foreach ($ignoredcolumns as $column)
{
unset($writearray[$column]);
}
$dupfound=false;
$invalidemail=false;
if ($filterduplicatetoken!=false)
{
if (!isset($_POST['filterduplicatefields']) || (isset($_POST['filterduplicatefields']) && count($_POST['filterduplicatefields'])==0))
{
$filterduplicatefields=array('firstname','lastname','email');
}
else
{
$filterduplicatefields=$_POST['filterduplicatefields'];
}
$dupquery = "SELECT tid from ".db_table_name("tokens_$surveyid")." where 1=1";
foreach($filterduplicatefields as $field)
{
if (isset($writearray[$field])) {
$dupquery.=' and '.db_quote_id($field).' = '.db_quoteall($writearray[$field]);
}
}
$dupresult = $connect->Execute($dupquery) or safe_die ("Invalid field in duplicate check $dupquery
".$connect->ErrorMsg());
if ( $dupresult->RecordCount() > 0)
{
$dupfound = true;
$duplicatelist[]=$writearray['firstname']." ".$writearray['lastname']." (".$writearray['email'].")";
}
}
$writearray['email'] = trim($writearray['email']);
//treat blank emails
if ($filterblankemail && $writearray['email']=='')
{
$invalidemail=true;
$invalidemaillist[]=$line[0]." ".$line[1]." ( )";
}
if ($writearray['email']!='')
{
$aEmailAddresses=explode(';',$writearray['email']);
foreach ($aEmailAddresses as $sEmailaddress)
{
if (!validate_email($sEmailaddress))
{
$invalidemail=true;
$invalidemaillist[]=$line[0]." ".$line[1]." (".$line[2].")";
}
}
}
if (!$dupfound && !$invalidemail)
{
if (!isset($writearray['emailstatus']) || $writearray['emailstatus']=='') $writearray['emailstatus'] = "OK";
if (!isset($writearray['token'])) {
$writearray['token'] = '';
}else{
$writearray['token']=sanitize_token($writearray['token']);
}
if (!isset($writearray['language']) || $writearray['language'] == "") $writearray['language'] = $baselanguage;
if (isset($writearray['validfrom']) && trim($writearray['validfrom']=='')){ unset($writearray['validfrom']);}
if (isset($writearray['validuntil']) && trim($writearray['validuntil']=='')){ unset($writearray['validuntil']);}
// sanitize it before writing into table
$sanitizedArray = array_map('db_quote',array_values($writearray));
$iq = "INSERT INTO ".db_table_name("tokens_$surveyid")." \n"
. "(".implode(',',array_keys($writearray)).") \n"
. "VALUES ('".implode("','",$sanitizedArray)."')";
$ir = $connect->Execute($iq);
if (!$ir)
{
$duplicatelist[]=$writearray['firstname']." ".$writearray['lastname']." (".$writearray['email'].")";
} else {
$xz++;
}
}
$xv++;
}
$recordcount++;
}
$recordcount = $recordcount-1;
if ($xz != 0)
{
$tokenoutput .= "
".$clang->gT("Successfully created token entries")."
\n";
} else {
$tokenoutput .= "
".$clang->gT("Failed to create token entries")."
\n";
}
$message = '
'.sprintf($clang->gT("%s records in CSV"),$recordcount)."
\n";
$message .= '
'.sprintf($clang->gT("%s records met minumum requirements"),$xv)."
\n";
$message .= '
'.sprintf($clang->gT("%s records imported"),$xz)."
\n";
if (count($duplicatelist)>0 || count($invalidformatlist)>0 || count($invalidemaillist)>0)
{
$message .="
";
foreach($duplicatelist as $data) {
$message .= "
$data
\n";
}
$message .= "
";
$message .= "
\n";
}
if (count($invalidformatlist)>0)
{
$message .= '
'.sprintf($clang->gT("%s lines had a mismatching number of fields."),count($invalidformatlist));
$message .= " [".$clang->gT("List")."]";
$message .= "
";
foreach($invalidformatlist as $data) {
$message .= "
Line $data
\n";
}
}
if (count($invalidemaillist)>0)
{
$message .= '
'.sprintf($clang->gT("%s records with invalid email address removed"),count($invalidemaillist));
$message .= " [".$clang->gT("List")."]";
$message .= "
";
foreach($invalidemaillist as $data) {
$message .= "
\n"
."
\n"
."
\n"
.""
."
\n"
."
\n";
if (bHasRight($surveyid, 'edit_survey_property') || bHasRight($surveyid, 'activate_survey'))
{
$tokenoutput .= ""
."
\n"
."
\n"
."
"
." 
";
}
if (bHasRight($surveyid, 'export'))
{
$tokenoutput .= "".
"
\n";
}
if (bHasRight($surveyid, 'edit_survey_property') || bHasRight($surveyid, 'activate_survey'))
{
$tokenoutput .= "
\n"
.""
."
\n"
.""
."
\n"
."
\n"
."
\n";
}
$tokenoutput .="
\n";
$tokenoutput .= "\t
";
}
$tokenoutput .=""
."gT("Are you sure you want to delete this entry?","js")." (".$brow['tid'].")')) {".get2post("$scriptname?action=tokens&sid=$surveyid&subaction=delete&tid=".$brow['tid']."&limit=$limit&start=$start&order=$order")."}\" />";
}
if ($brow['completed'] != "N" && $brow['completed']!="" && $surveyprivate == "N" && $thissurvey['active']=='Y')
{
// Get response Id
$query="SELECT id FROM ".db_table_name('survey_'.$surveyid)." WHERE token='{$brow['token']}' ORDER BY id desc";
$result=db_execute_num($query) or safe_die ("