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'])); } }