function uploadCSV()
{
$clang = $this->getController()->lang;
unset(Yii::app()->session['summary']);
$characterset = Yii::app()->request->getPost('characterset');
$separator = Yii::app()->request->getPost('separatorused');
$newarray = Yii::app()->request->getPost('newarray');
$mappedarray = Yii::app()->request->getPost('mappedarray', false);
$filterblankemails = Yii::app()->request->getPost('filterbea');
$overwrite = Yii::app()->request->getPost('overwrite');
$sFilePath = Yii::app()->getConfig('tempdir') . '/' . basename(Yii::app()->request->getPost('fullfilepath'));
$errorinupload = "";
$recordcount = 0;
$mandatory = 0;
$mincriteria = 0;
$imported = 0;
$dupcount = 0;
$overwritten = 0;
$dupreason = "nameemail";
//Default duplicate comparison method
$duplicatelist = array();
$invalidemaillist = array();
$invalidformatlist = array();
$invalidattribute = array();
$invalidparticipantid = array();
$aGlobalErrors = array();
/* If no mapped array */
if (!$mappedarray) {
$mappedarray = array();
}
/* Adjust system settings to read file with MAC line endings */
@ini_set('auto_detect_line_endings', true);
/* Open the uploaded file into an array */
$tokenlistarray = file($sFilePath);
// open it and trim the endings
$separator = Yii::app()->request->getPost('separatorused');
$uploadcharset = Yii::app()->request->getPost('characterset');
/* The $newarray contains a list of fields that will be used
to create new attributes */
if (!empty($newarray)) {
/* Create a new entry in the lime_participant_attribute_names table,
and it's associated lime_participant_attribute_names_lang table
for each NEW attribute being created in this import process */
foreach ($newarray as $key => $value) {
$aData = array('attribute_type' => 'TB', 'attribute_name' => $value, 'visible' => 'FALSE');
$insertid = ParticipantAttributeName::model()->storeAttributeCSV($aData);
/* Keep a record of the attribute_id for this new attribute
in the $mappedarray string. For example, if the new attribute
has attribute_id of 35 and is called "gender",
$mappedarray['35']='gender' */
$mappedarray[$insertid] = $value;
}
}
if (!isset($uploadcharset)) {
$uploadcharset = 'auto';
}
foreach ($tokenlistarray as $buffer) {
//Iterate through the CSV file line by line
$buffer = @mb_convert_encoding($buffer, "UTF-8", $uploadcharset);
$firstname = "";
$lastname = "";
$email = "";
$language = "";
if ($recordcount == 0) {
//The first time we iterate through the file we look at the very
//first line, which contains field names, not values to import
// Pick apart the first line
$buffer = removeBOM($buffer);
$attrid = ParticipantAttributeName::model()->getAttributeID();
$allowedfieldnames = array('participant_id', 'firstname', 'lastname', 'email', 'language', 'blacklisted');
$aFilterDuplicateFields = array('firstname', 'lastname', 'email');
if (!empty($mappedarray)) {
foreach ($mappedarray as $key => $value) {
array_push($allowedfieldnames, strtolower($value));
}
}
//For Attributes
switch ($separator) {
case 'comma':
$separator = ',';
break;
case 'semicolon':
$separator = ';';
break;
default:
$comma = substr_count($buffer, ',');
$semicolon = substr_count($buffer, ';');
if ($semicolon > $comma) {
$separator = ';';
} else {
$separator = ',';
}
}
$firstline = convertCSVRowToArray($buffer, $separator, '"');
$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(strtolower($fieldname), $allowedfieldnames) && !in_array($fieldname, $mappedarray)) {
$ignoredcolumns[] = $fieldname;
} else {
$firstline[$index] = strtolower($fieldname);
}
}
if (!in_array('firstname', $firstline) && !in_array('lastname', $firstline) && !in_array('email', $firstline) && !in_array('participant_id', $firstline)) {
$recordcount = count($tokenlistarray);
break;
}
} else {
// After looking at the first line, we now import the actual values
$line = convertCSVRowToArray($buffer, $separator, '"');
if (count($firstline) != count($line)) {
$invalidformatlist[] = $recordcount;
continue;
}
$writearray = array_combine($firstline, $line);
//kick out ignored columns
foreach ($ignoredcolumns as $column) {
unset($writearray[$column]);
}
// Add aFilterDuplicateFields not in CSV to writearray : quick fix
foreach ($aFilterDuplicateFields as $sFilterDuplicateField) {
if (!in_array($sFilterDuplicateField, $firstline)) {
$writearray[$sFilterDuplicateField] = "";
}
}
$invalidemail = false;
$dupfound = false;
$thisduplicate = 0;
//Check for duplicate participants
$aData = array('firstname' => $writearray['firstname'], 'lastname' => $writearray['lastname'], 'email' => $writearray['email'], 'owner_uid' => Yii::app()->session['loginID']);
//HACK - converting into SQL instead of doing an array search
if (in_array('participant_id', $firstline)) {
$dupreason = "participant_id";
$aData = "participant_id = " . Yii::app()->db->quoteValue($writearray['participant_id']);
} else {
$dupreason = "nameemail";
$aData = "firstname = " . Yii::app()->db->quoteValue($writearray['firstname']) . " AND lastname = " . Yii::app()->db->quoteValue($writearray['lastname']) . " AND email = " . Yii::app()->db->quoteValue($writearray['email']) . " AND owner_uid = '" . Yii::app()->session['loginID'] . "'";
}
//End of HACK
$aData = Participant::model()->checkforDuplicate($aData, "participant_id");
if ($aData !== false) {
$thisduplicate = 1;
$dupcount++;
if ($overwrite == "true") {
//Although this person already exists, we want to update the mapped attribute values
if (!empty($mappedarray)) {
//The mapped array contains the attributes we are
//saving in this import
foreach ($mappedarray as $attid => $attname) {
if (!empty($attname)) {
$bData = array('participant_id' => $aData, 'attribute_id' => $attid, 'value' => $writearray[strtolower($attname)]);
ParticipantAttribute::model()->updateParticipantAttributeValue($bData);
} else {
//If the value is empty, don't write the value
}
}
$overwritten++;
}
}
}
if ($thisduplicate == 1) {
$dupfound = true;
$duplicatelist[] = $writearray['firstname'] . " " . $writearray['lastname'] . " (" . $writearray['email'] . ")";
}
//Checking the email address is in a valid format
$invalidemail = false;
$writearray['email'] = trim($writearray['email']);
if ($writearray['email'] != '') {
$aEmailAddresses = explode(';', $writearray['email']);
// Ignore additional email addresses
$sEmailaddress = $aEmailAddresses[0];
if (!validateEmailAddress($sEmailaddress)) {
$invalidemail = true;
$invalidemaillist[] = $line[0] . " " . $line[1] . " (" . $line[2] . ")";
}
}
if (!$dupfound && !$invalidemail) {
//If it isn't a duplicate value or an invalid email, process the entry as a new participant
//First, process the known fields
if (!isset($writearray['participant_id']) || $writearray['participant_id'] == "") {
$uuid = $this->gen_uuid();
//Generate a UUID for the new participant
$writearray['participant_id'] = $uuid;
}
if (isset($writearray['emailstatus']) && trim($writearray['emailstatus'] == '')) {
unset($writearray['emailstatus']);
}
if (!isset($writearray['language']) || $writearray['language'] == "") {
$writearray['language'] = "en";
}
if (!isset($writearray['blacklisted']) || $writearray['blacklisted'] == "") {
$writearray['blacklisted'] = "N";
}
$writearray['owner_uid'] = Yii::app()->session['loginID'];
if (isset($writearray['validfrom']) && trim($writearray['validfrom'] == '')) {
unset($writearray['validfrom']);
}
if (isset($writearray['validuntil']) && trim($writearray['validuntil'] == '')) {
unset($writearray['validuntil']);
}
$dontimport = false;
if ($filterblankemails == "accept" && $writearray['email'] == "" || $writearray['firstname'] == "" || $writearray['lastname'] == "") {
//The mandatory fields of email, firstname and lastname
//must be filled, but one or more are empty
$mandatory++;
$dontimport = true;
} else {
foreach ($writearray as $key => $value) {
if (!empty($mappedarray)) {
//The mapped array contains the attributes we are
//saving in this import
if (in_array($key, $allowedfieldnames)) {
foreach ($mappedarray as $attid => $attname) {
if (strtolower($attname) == $key) {
if (!empty($value)) {
$aData = array('participant_id' => $writearray['participant_id'], 'attribute_id' => $attid, 'value' => $value);
ParticipantAttributeName::model()->saveParticipantAttributeValue($aData);
} else {
//If the value is empty, don't write the value
}
}
}
}
}
}
}
//If any of the mandatory fields are blank, then don't import this user
if (!$dontimport) {
Participant::model()->insertParticipantCSV($writearray);
$imported++;
}
}
$mincriteria++;
}
$recordcount++;
}
unlink($sFilePath);
$aData = array();
$aData['clang'] = $clang;
$aData['recordcount'] = $recordcount - 1;
$aData['duplicatelist'] = $duplicatelist;
$aData['mincriteria'] = $mincriteria;
$aData['imported'] = $imported;
$aData['errorinupload'] = $errorinupload;
$aData['invalidemaillist'] = $invalidemaillist;
$aData['mandatory'] = $mandatory;
$aData['invalidattribute'] = $invalidattribute;
$aData['invalidparticipantid'] = $invalidparticipantid;
$aData['overwritten'] = $overwritten;
$aData['dupreason'] = $dupreason;
$aData['aGlobalErrors'] = $aGlobalErrors;
$this->getController()->renderPartial('/admin/participants/uploadSummary_view', $aData);
}
/**
* This function deletes the participant from the participants table,
* references in the survey_links table (but not in matching tokens tables)
* and then all the participants attributes.
* @param $rows Participants ID separated by comma
* @return void
**/
function deleteParticipants($rows, $bFilter = true)
{
// Converting the comma separated IDs to an array and assign chunks of 100 entries to have a reasonable query size
$aParticipantsIDChunks = array_chunk(explode(",", $rows), 100);
foreach ($aParticipantsIDChunks as $aParticipantsIDs) {
if ($bFilter) {
$aParticipantsIDs = $this->filterParticipantIDs($aParticipantsIDs);
}
foreach ($aParticipantsIDs as $aID) {
$oParticipant = Participant::model()->findByPk($aID);
if ($oParticipant) {
$oParticipant->delete();
}
}
Yii::app()->db->createCommand()->delete(Participant::model()->tableName(), array('in', 'participant_id', $aParticipantsIDs));
// Delete survey links
Yii::app()->db->createCommand()->delete(SurveyLink::model()->tableName(), array('in', 'participant_id', $aParticipantsIDs));
// Delete participant attributes
Yii::app()->db->createCommand()->delete(ParticipantAttribute::model()->tableName(), array('in', 'participant_id', $aParticipantsIDs));
}
}
function editParticipantAttributeValue($data)
{
$query = ParticipantAttribute::model()->find('participant_id = :participant_id AND attribute_id=:attribute_id', array(':participant_id' => $data['participant_id'], ':attribute_id' => $data['attribute_id']));
if (count($query) == 0) {
Yii::app()->db->createCommand()->insert('{{participant_attribute}}', $data);
} else {
Yii::app()->db->createCommand()->update('{{participant_attribute}}', $data, 'participant_id = :participant_id2 AND attribute_id = :attribute_id2', array(':participant_id2' => $data['participant_id'], ':attribute_id2' => $data['attribute_id']));
}
}
