public function editCustomRecord($parameters)
{
$parameters['emailAccounts'] = EmailAccount::all();
$parameters['templates'] = EmailTemplate::all();
$parameters['themes'] = ComunikLibrary::getThemes();
$parameters['emlHeaders'] = ComunikLibrary::getEmlHeaders();
$parameters['groups'] = Group::all();
$parameters['selectGroups'] = $parameters['object']->getGroups;
$parameters['countries'] = Contact::getCountriesContacts(['lang' => auth('pulsar')->user()->lang_id_010]);
$parameters['selectCountries'] = $parameters['object']->getCountries;
// statistics
$parameters['queueMailings'] = EmailSendQueue::where('campaign_id_047', $parameters['id'])->count();
$parameters['sentMailings'] = EmailSendHistory::where('campaign_id_048', $parameters['id'])->count();
$parameters['noSentMailings'] = EmailSendQueue::where('campaign_id_047', $parameters['id'])->where('status_id_047', 0)->count();
$parameters['uniqueViewMailings'] = EmailSendHistory::where('campaign_id_048', $parameters['id'])->where('viewed_048', '>', 0)->count();
$parameters['effectiveness'] = $parameters['uniqueViewMailings'] > 0 ? round($parameters['uniqueViewMailings'] / $parameters['sentMailings'] * 100, 2) : 0;
return $parameters;
}
public function importRecords()
{
$data = [];
$jsonData = json_decode($this->request->input('data'));
$countries = Country::where('lang_id_002', auth('pulsar')->user()->lang_id_010)->get();
$groups = $this->request->input('groups');
$country = $this->request->input('country');
$inputFileName = public_path() . '/packages/syscover/pulsar/storage/tmp/' . $this->request->input('file');
$fields = ['id_040' => trans('comunik::pulsar.group_id'), 'company_041' => trans_choice('pulsar::pulsar.company', 1), 'name_041' => trans('pulsar::pulsar.name'), 'surname_041' => trans('pulsar::pulsar.surname'), 'country_id_041' => trans('comunik::pulsar.country_id'), 'mobile_041' => trans('pulsar::pulsar.mobile'), 'email_041' => trans('pulsar::pulsar.email')];
$columns = [];
$objReader = \PHPExcel_IOFactory::createReader('CSV')->setDelimiter(';')->setReadDataOnly(true);
// configura el reader para ignorar estilos, solo leerá los datos
$objPHPExcel = $objReader->load($inputFileName);
// Cargamos el fichero y obtenemos el objeto PHPExcel
//$totalSheets = $objPHPExcel->getSheetCount(); // Función para obtener el número de libros de la hoja de cálculo
//$allSheetName = $objPHPExcel->getSheetNames(); // Función para obtener los nombres de los libros de las hojas de cálculo
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
// Por defecto recuperamos el primer libro de la hoja de excel
$highestRow = $objWorksheet->getHighestRow();
// Recuperamos el número de fila más alto (dato numérico, empezando por 1)
$highestColumn = $objWorksheet->getHighestColumn();
// Recuperamos la columma mas alta (data string)
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
// Pasamos del dato string de columna a un dato numérico
$arrayDataFail = [];
$firsRow = true;
for ($row = 1; $row <= $highestRow; ++$row) {
// comprobamos si esta fila no debe de ser insertada
if (!in_array($row - 1, $jsonData->deleteRows)) {
$dbRow = [];
// recorremos las columnas y según coincida el campo con una columna lo agregamos al array $dbRow
// para insertar la fila en la base de datos, tratando previamente el dato
$checkCommonField = false;
for ($col = 0; $col < $highestColumnIndex; ++$col) {
if ($firsRow && isset($fields[$this->request->input('column' . $col)])) {
// get sorting columns
$columns[$this->request->input('column' . $col)] = $fields[$this->request->input('column' . $col)];
}
// damos formato a los datos a insertar
if ($this->request->input('column' . $col) == 'name_041' || $this->request->input('column' . $col) == 'surname_041' || $this->request->input('column' . $col) == 'company_041') {
// nombre y apellidos en minúsculas con la primera en mayúscula
$dbRow[$this->request->input('column' . $col)] = ucwords(strtolower($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()));
} elseif ($this->request->input('column' . $col) == 'email_041') {
// eliminamos espacios en blanco y ponemos el mail en minúsculas
$dbRow['email_041'] = trim(strtolower($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()));
} elseif ($this->request->input('column' . $col) == 'mobile_041') {
// eliminamos espacios en blanco en el contenido y ponemos el email en minúsculas
$dbRow['mobile_041'] = str_replace(' ', '', str_replace('-', '', $objWorksheet->getCellByColumnAndRow($col, $row)->getValue()));
} elseif (empty($groups) && $this->request->input('column' . $col) == 'id_040') {
// instanciamos $group para después insertarlo en la tabla 005_042_contacts_groups
// siempre y cuando no se elia un grupo para todas las filas
$dbRow['id_040'] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
} elseif (empty($country) && $this->request->input('column' . $col) == 'country_id_041') {
$dbRow['country_id_041'] = trim(strtoupper($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()));
$countryObj = $countries->find($dbRow['country_id_041']);
if ($countryObj != null) {
$dbRow['prefix_041'] = $countryObj->prefix_002;
}
}
// comprobamos si debemos instanciar los datos comunes una vez en cada fila
if (!$checkCommonField) {
if (!empty($groups)) {
// instanciamos los datos comunes de groups
$dbRow['id_040'] = $groups;
}
if (!empty($country)) {
// instanciamos los datos comunes de country
$country = $countries->find($country);
$dbRow['country_id_041'] = $country->id_002;
$dbRow['prefix_041'] = $country->prefix_002;
}
$checkCommonField = true;
}
}
// check data from server side
$rules = ['email_041' => 'email|between:2,50|unique:005_041_contact,email_041', 'prefix_041' => 'numeric|digits_between:0,5', 'mobile_041' => 'numeric|digits_between:2,50|unique:005_041_contact,mobile_041', 'country_id_041' => 'required|exists:001_002_country,id_002', 'id_040' => 'required'];
// si no tenemos instaciado un grupo para todas las filas, añadimos la comprobación
// de lo contrari, $group contendría un array al ser selección múltiple,
// y en la validación siempre daría error
if (empty($groups)) {
$rules['id_040'] = 'required|exists:005_040_group,id_040';
}
if (!array_key_exists('email_041', $dbRow) && array_key_exists('mobile_041', $dbRow)) {
$rules['mobile_041'] = 'required|numeric|digits_between:2,50|unique:005_041_contact,mobile_041';
}
if (!array_key_exists('mobile_041', $dbRow) && array_key_exists('email_041', $dbRow)) {
$rules['email_041'] = 'required|email|between:2,50|unique:005_041_contact,email_041';
}
//if(array_key_exists('mobile_041', $dbRow) && array_key_exists('email_041', $dbRow) && empty($dbRow['mobile_041']) && empty($dbRow['email_041']))
if (array_key_exists('mobile_041', $dbRow) && array_key_exists('email_041', $dbRow) || !array_key_exists('mobile_041', $dbRow) && !array_key_exists('email_041', $dbRow)) {
$rules['mobile_041'] = 'required|numeric|digits_between:6,15|unique:005_041_contact,mobile_041';
$rules['email_041'] = 'required|email|between:2,50|unique:005_041_contact,email_041';
}
// cargamos la validación de los datos para la fila a insertar
$validator = Validator::make($dbRow, $rules);
$validator->setAttributeNames($fields);
if ($validator->fails()) {
$messages = $validator->messages();
// recojemos todos los errores de la fila y los añadimos
$errors = [];
foreach ($messages->all() as $message) {
$errors[] = $message;
}
$arrayDataFail[] = ['row' => $dbRow, 'errors' => $errors];
} else {
//realizamos la insercción en la base de datos
try {
$contact = Contact::create($dbRow);
$contact->getGroups()->attach($dbRow['id_040']);
} catch (\Exception $e) {
$arrayDataFail[] = ['row' => $dbRow, 'errors' => [$e->getMessage()]];
}
}
// set firs row to false
$firsRow = false;
}
}
$data['arrayDataFail'] = $arrayDataFail;
$data['columns'] = $columns;
return view('comunik::contact.import_error', $data);
}
/**
* Function to search pattern inside message
* @param $message
* @param $patterns
* @return array
*/
public static function checkEmailPattern($message, $patterns)
{
$response = [];
$patternFound = false;
$objPattern = null;
$subject = $message->getSubject();
$body = $message->getMessageBody();
foreach ($patterns as $pattern) {
$subjectFound = false;
$messageFound = false;
if (empty($pattern->subject_049) == false && strpos($subject, $pattern->subject_049) !== false) {
$subjectFound = true;
}
if (empty($pattern->message_049) === false && strpos($body, $pattern->message_049) !== false) {
$messageFound = true;
}
if ($pattern->operator_049 == 'and' && $subjectFound && $messageFound) {
$patternFound = true;
$objPattern = $pattern;
break;
} elseif (($pattern->operator_049 == 'or' || $pattern->operator_049 == null) && ($subjectFound || $messageFound)) {
$patternFound = true;
$objPattern = $pattern;
break;
}
}
if ($patternFound) {
$response['success'] = true;
$response['pattern'] = $objPattern;
$emails = Miscellaneous::extractEmail($body);
$response['contacts'] = Contact::builder()->whereIn('email_041', $emails)->where('unsubscribe_email_041', false)->get();
} else {
$response['success'] = false;
$response['pattern'] = false;
$emails = Miscellaneous::extractEmail($body);
$response['contacts'] = Contact::builder()->whereIn('email_041', $emails)->where('unsubscribe_email_041', false)->get();
}
return $response;
}
/**
* Recursive function
*
* @param \Syscover\Pulsar\Libraries\ImapServices $imapService
* @param \Syscover\Pulsar\Models\EmailAccount $account
* @param \Illuminate\Support\Collection $patterns
* @param integer $position
*/
public static function checkBouncedMessagesFromAccount($imapService, $account, $patterns, $position)
{
$nEmailsToChecck = 25;
// Solicitamos los 10 anteriores mensajes a comprobar desde la última posición comnprobada
$messages = $imapService->getServer()->getMessages($nEmailsToChecck, $position);
$findLastCheckUid = false;
foreach ($messages as $key => $message) {
// comprobamos si el mensaje coincide con algún patron
$response = ComunikLibrary::checkEmailPattern($message, $patterns);
if ($response['success']) {
if (count($response['contacts']) > 0) {
// Obtenemos el primer contacto del email que ha coincidido con el patrón
$contact = $response['contacts']->first();
} else {
$contact = false;
}
// Acciones a realizar
// 1 = nada
// 2 = borrar contacto y mensaje
// 3 = unsuscribe y borrar mensaje
// 4 = borrar contacto
// 5 = ususcribe contacto
// 6 = borrar mensaje
if ($contact != false && ($response['pattern']->action_049 == 2 || $response['pattern']->action_049 == 4)) {
// borrar contacto
Contact::where('id_041', $contact->id_041)->delete();
}
if ($contact != false && ($response['pattern']->action_049 == 3 || $response['pattern']->action_049 == 5)) {
// unsuscribe contacto
Contact::where('id_041', $contact->id_041)->update(['unsubscribe_email_041' => true]);
}
if ($response['pattern']->action_049 == 2 || $response['pattern']->action_049 == 3 || $response['pattern']->action_049 == 6) {
// borrar mensaje
$message->delete();
// retrasamos medio segundo la ejecución para no saturar el servidor IMAP de peticiones
usleep(500000);
}
}
// si es la última interacción
if ($key === count($messages) - 1) {
// purge all delete messages
$imapService->getServer()->expunge();
// actualizamos el último UID comprobado
EmailAccount::where('id_013', $account->id_013)->update(['last_check_uid_013' => $message->getUid()]);
}
}
}
public static function getContactsEmailToInsert($campaign, $groups, $countries, $take, $skip)
{
return Contact::whereIn('id_041', function ($query) use($groups) {
// select contacts from this groups
$query->select('contact_id_042')->from('005_042_contacts_groups')->whereIn('group_id_042', $groups)->groupBy('contact_id_042')->get();
})->whereIn('country_id_041', $countries)->whereNotIn('id_041', function ($query) use($campaign) {
$query->select('contact_id_047')->from('005_047_email_send_queue')->where('campaign_id_047', $campaign)->get();
})->whereNotIn('id_041', function ($query) use($campaign) {
$query->select('contact_id_048')->from('005_048_email_send_history')->where('campaign_id_048', $campaign)->get();
})->where('unsubscribe_email_041', false)->whereNotNull('email_041')->where('email_041', '<>', '')->take($take)->skip($skip)->get();
}