/** * Uploads the file to the server and process it for valid enteries and import them into database * Also creates attributes from the mapping drag-n-drop form. */ public function uploadCSV() { if (!Permission::model()->hasGlobalPermission('participantpanel', 'import')) { die('No permission'); } unset(Yii::app()->session['summary']); $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 attributes */ $newarray = Yii::app()->request->getPost('newarray'); 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', 'defaultname' => $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) { $buffer = @mb_convert_encoding($buffer, "UTF-8", $uploadcharset); 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); $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 = str_getcsv($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 = str_getcsv($buffer, $separator, '"'); // Discard lines where the number of fields do not match if (count($firstline) != count($line)) { $invalidformatlist[] = $recordcount . ',' . count($line) . ',' . count($firstline); $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] = ""; } } $dupfound = false; $thisduplicate = 0; //Check for duplicate participants //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 = Participant::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'] == "") { //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['recordcount'] = $recordcount - 1; $aData['duplicatelist'] = $duplicatelist; $aData['mincriteria'] = $mincriteria; $aData['imported'] = $imported; $aData['errorinupload'] = $errorinupload; $aData['invalidemaillist'] = $invalidemaillist; $aData['aInvalidFormatlist'] = $invalidformatlist; $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 import a participant to the LimeSurvey cpd. It stores attributes as well, if they are registered before within ui * * Call the function with $response = $myJSONRPCClient->cpd_importParticipants( $sessionKey, $aParticipants); * * @param int $sSessionKey * @param array $aParticipants * [[0] => ["email"=>"*****@*****.**","firstname"=>"max","lastname"=>"mustermann"]] * @return array with status */ public function cpd_importParticipants($sSessionKey, $aParticipants) { if (!$this->_checkSessionKey($sSessionKey)) { return array('status' => 'Invalid session key'); } $aResponse = array(); $aAttributeData = array(); $aAttributes = array(); $aDefaultFields = array('participant_id', 'firstname', 'lastname', 'email', 'language', 'blacklisted'); $bIsValidEmail = true; $bDoImport = true; $sMandatory = 0; $sAttribCount = 0; $aResponse = array(); $aResponse['ImportCount'] = 0; // get all attributes for mapping $oFindCriteria = new CDbCriteria(); $oFindCriteria->offset = -1; $oFindCriteria->limit = -1; $aAttributeRecords = ParticipantAttributeName::model()->with('participant_attribute_names_lang')->findAll($oFindCriteria); foreach ($aParticipants as $sKey => $aParticipantData) { $aData = array('firstname' => $aParticipantData['firstname'], 'lastname' => $aParticipantData['lastname'], 'email' => $aParticipantData['email'], 'owner_uid' => Yii::app()->session['loginID']); //Check for duplicate participants $arRecordExists = Participant::model()->exists('firstname = :firstname AND lastname = :lastname AND email = :email AND owner_uid = :owner_uid', array(':firstname' => $aData['firstname'], ':lastname' => $aData['lastname'], ':email' => $aData['email'], ':owner_uid' => $aData['owner_uid'])); // check if email is valid $this->_checkEmailFormat($aData['email']); if ($bIsValidEmail == true) { //First, process the known fields if (!isset($aData['participant_id']) || $aData['participant_id'] == "") { // $arParticipantModel = new Participant(); $aData['participant_id'] = Participant::gen_uuid(); } if (isset($aData['emailstatus']) && trim($aData['emailstatus'] == '')) { unset($aData['emailstatus']); } if (!isset($aData['language']) || $aData['language'] == "") { $aData['language'] = "en"; } if (!isset($aData['blacklisted']) || $aData['blacklisted'] == "") { $aData['blacklisted'] = "N"; } $aData['owner_uid'] = Yii::app()->session['loginID']; if (isset($aData['validfrom']) && trim($aData['validfrom'] == '')) { unset($aData['validfrom']); } if (isset($aData['validuntil']) && trim($aData['validuntil'] == '')) { unset($aData['validuntil']); } if (!empty($aData['email'])) { //The mandatory fields of email, firstname and lastname $sMandatory++; $bDoImport = false; } // Write to database if record not exists if (empty($arRecordExists)) { // save participant to database Participant::model()->insertParticipantCSV($aData); // Prepare atrribute values to store in db . Iterate through our values foreach ($aParticipantData as $sLabel => $sAttributeValue) { // skip default fields if (!in_array($sLabel, $aDefaultFields)) { foreach ($aAttributeRecords as $sKey => $arValue) { $aAttributes = $arValue->getAttributes(); if ($aAttributes['defaultname'] == $sLabel) { $aAttributeData['participant_id'] = $aData['participant_id']; $aAttributeData['attribute_id'] = $aAttributes['attribute_id']; $aAttributeData['value'] = $sAttributeValue; $sAttribCount++; // save attributes values for participant ParticipantAttributeName::model()->saveParticipantAttributeValue($aAttributeData); } } } } $aResponse['ImportCount']++; } } } return $aResponse; }