public function mainAction()
{
// inicialize supporting classes
$connection = new Connection();
$email = new Email();
$service = new Service();
$service->showAds = false;
$render = new Render();
$response = new Response();
$utils = new Utils();
$wwwroot = $this->di->get('path')['root'];
// get valid people
$people = $connection->deepQuery("\n\t\t\tSELECT email, username, first_name, last_access\n\t\t\tFROM person\n\t\t\tWHERE active=1\n\t\t\tAND email not in (SELECT DISTINCT email FROM delivery_dropped)\n\t\t\tAND DATE(last_access) > DATE('2016-05-01')\n\t\t\tAND email like '%.cu'\n\t\t\tAND email not like '*****@*****.**'");
// send the remarketing
$log = "";
foreach ($people as $person) {
// get the email address
$newEmail = "apretaste+{$person->username}@gmail.com";
// create the variabels to pass to the template
$content = array("newemail" => $newEmail, "name" => $person->first_name);
// create html response
$response->setEmailLayout("email_simple.tpl");
$response->createFromTemplate('newEmail.tpl', $content);
$response->internal = true;
$html = $render->renderHTML($service, $response);
// send the email
$email->sendEmail($person->email, "Sorteando las dificultades, un email lleno de alegria", $html);
$log .= $person->email . "\n";
}
// saving the log
$logger = new \Phalcon\Logger\Adapter\File("{$wwwroot}/logs/newemail.log");
$logger->log($log);
$logger->close();
}
/**
* Query the database and returs an array of objects
* Please use escape() for all texts before creating the $sql
*
* @author salvipascual
* @param String $sql, valid sql query
* @return Array, list of rows or NULL if it is not a select
*/
public function deepQuery($sql)
{
// get the database connection
$di = \Phalcon\DI\FactoryDefault::getDefault();
try {
// only fetch for selects
if (stripos(trim($sql), "select") === 0) {
// query the database
$result = $di->get('db')->query($sql);
$result->setFetchMode(Phalcon\Db::FETCH_OBJ);
// convert to array of objects
$rows = array();
while ($data = $result->fetch()) {
$rows[] = $data;
}
// return the array of objects
return $rows;
} else {
// execute statement in the database
return $di->get('db')->execute($sql);
}
} catch (PDOException $e) {
$message = $e->getMessage();
$query = isset($e->getTrace()[0]['args'][0]) ? $e->getTrace()[0]['args'][0] : "Query not available";
$wwwroot = $di->get('path')['root'];
$logger = new \Phalcon\Logger\Adapter\File("{$wwwroot}/logs/badqueries.log");
$logger->log("{$message}\nQUERY: {$query}\n");
$logger->close();
throw $e;
}
}
public function mainAction()
{
// inicialize supporting classes
$timeStart = time();
$connection = new Connection();
$email = new Email();
$service = new Service();
$service->showAds = true;
$render = new Render();
$response = new Response();
$utils = new Utils();
$wwwroot = $this->di->get('path')['root'];
$log = "";
// people who were invited but never used Apretaste
$invitedPeople = $connection->deepQuery("\n\t\t\tSELECT invitation_time, email_inviter, email_invited\n\t\t\tFROM invitations \n\t\t\tWHERE used=0 \n\t\t\tAND DATEDIFF(CURRENT_DATE, invitation_time) > 15 \n\t\t\tAND email_invited NOT IN (SELECT DISTINCT email from delivery_dropped)\n\t\t\tAND email_invited NOT IN (SELECT DISTINCT email from remarketing)\n\t\t\tORDER BY invitation_time DESC\n\t\t\tLIMIT 450");
// send the first remarketing
$log .= "\nINVITATIONS (" . count($invitedPeople) . ")\n";
foreach ($invitedPeople as $person) {
// check number of days since the invitation was sent
$datediff = time() - strtotime($person->invitation_time);
$daysSinceInvitation = floor($datediff / (60 * 60 * 24));
// validate old invitations to avoid bounces
if ($daysSinceInvitation > 60) {
// re-validate the email
$res = $utils->deepValidateEmail($person->email_invited);
// if response not ok or temporal, delete from invitations list
if ($res[0] != "ok" && $res[0] != "temporal") {
$connection->deepQuery("DELETE FROM invitations WHERE email_invited = '{$person->email_invited}'");
$log .= "\t --skiping {$person->email_invited}\n";
continue;
}
}
// send data to the template
$content = array("date" => $person->invitation_time, "inviter" => $person->email_inviter, "invited" => $person->email_invited, "expires" => strtotime('next month'));
// create html response
$response->createFromTemplate('pendinginvitation.tpl', $content);
$response->internal = true;
$html = $render->renderHTML($service, $response);
// send the invitation email
$subject = "Su amigo {$person->email_inviter} esta esperando por usted!";
$email->sendEmail($person->email_invited, $subject, $html);
// insert into remarketing table
$connection->deepQuery("INSERT INTO remarketing(email, type) VALUES ('{$person->email_invited}', 'INVITE')");
// display notifications
$log .= "\t{$person->email_invited}\n";
}
// get final delay
$timeEnd = time();
$timeDiff = $timeEnd - $timeStart;
// printing log
$log .= "EXECUTION TIME: {$timeDiff} seconds\n\n";
echo $log;
// saving the log
$logger = new \Phalcon\Logger\Adapter\File("{$wwwroot}/logs/remarketing_invitation.log");
$logger->log($log);
$logger->close();
// save the status in the database
$connection->deepQuery("UPDATE task_status SET executed=CURRENT_TIMESTAMP, delay='{$timeDiff}' WHERE task='invitation'");
}
public function mainAction()
{
// inicialize supporting classes
$timeStart = time();
$utils = new Utils();
$connection = new Connection();
$sender = new Email();
// get the first campaign created that is waiting to be sent
$campaign = $connection->deepQuery("\n\t\t\tSELECT id, subject, content\n\t\t\tFROM campaign\n\t\t\tWHERE sending_date < CURRENT_TIMESTAMP\n\t\t\tAND status = 'WAITING'\n\t\t\tGROUP BY sending_date ASC\n\t\t\tLIMIT 1");
// check if there are not campaigns
if (empty($campaign)) {
return;
} else {
$campaign = $campaign[0];
}
// check campaign as SENDING
$connection->deepQuery("UPDATE campaign SET status='SENDING' WHERE id = {$campaign->id}");
// get the list of people in the list who hsa not receive this campaign yet
// so in case the campaign fails when it tries again starts from the same place
$people = $connection->deepQuery("\n\t\t\tSELECT email FROM person\n\t\t\tWHERE mail_list=1 AND active=1\n\t\t\tAND email NOT IN (SELECT email FROM campaign_sent WHERE campaign={$campaign->id})");
// show initial message
$total = count($people);
echo "\nSTARTING COUNT: {$total}\n";
// email people one by one
$counter = 1;
foreach ($people as $person) {
// show message
echo "{$counter}/{$total} - {$person->email}\n";
$counter++;
// replace the template variables
$content = $utils->campaignReplaceTemplateVariables($person->email, $campaign->content, $campaign->id);
// send test email
$sender->trackCampaign = $campaign->id;
$result = $sender->sendEmail($person->email, $campaign->subject, $content);
// add to bounced and unsubscribe if there are issues sending
$bounced = "";
$status = "SENT";
if (!$result) {
$utils->unsubscribeFromEmailList($person->email);
$bounced = "bounced=bounced+1,";
$status = "BOUNCED";
}
// save status before moving to the next email
$connection->deepQuery("\n\t\t\t\tINSERT INTO campaign_sent (email, campaign, status) VALUES ('{$person->email}', '{$campaign->id}', '{$status}');\n\t\t\t\tUPDATE campaign SET {$bounced} sent=sent+1 WHERE id='{$campaign->id}'");
}
// set the campaign as SENT
$connection->deepQuery("UPDATE campaign SET status='SENT' WHERE id='{$campaign->id}'");
// get final delay
$timeEnd = time();
$timeDiff = $timeEnd - $timeStart;
// saving the log
$wwwroot = $this->di->get('path')['root'];
$logger = new \Phalcon\Logger\Adapter\File("{$wwwroot}/logs/campaigns.log");
$logger->log("ID: {$campaign->id}, RUNTIME: {$timeDiff}, SUBJECT: {$campaign->subject}");
$logger->close();
// save the status in the database
$connection->deepQuery("UPDATE task_status SET executed=CURRENT_TIMESTAMP, delay='{$timeDiff}' WHERE task='campaign'");
}
public function mainAction()
{
// inicialize supporting classes
$timeStart = time();
$connection = new Connection();
$email = new Email();
$service = new Service();
$service->showAds = true;
$render = new Render();
$response = new Response();
$utils = new Utils();
$wwwroot = $this->di->get('path')['root'];
$log = "";
// people in the list to be automatically invited
$people = $connection->deepQuery("\n\t\t\tSELECT * FROM autoinvitations\n\t\t\tWHERE email NOT IN (SELECT email FROM person)\n\t\t\tAND email NOT IN (SELECT DISTINCT email FROM delivery_dropped)\n\t\t\tAND email NOT IN (SELECT DISTINCT email from remarketing)\n\t\t\tAND error=0\n\t\t\tLIMIT 450");
// send the first remarketing
$log .= "\nAUTOMATIC INVITATIONS (" . count($people) . ")\n";
foreach ($people as $person) {
// if response not ok, check the email as error
$res = $utils->deepValidateEmail($person->email);
if ($res[0] != "ok") {
$connection->deepQuery("UPDATE autoinvitations SET error=1, processed=CURRENT_TIMESTAMP WHERE email='{$person->email}'");
$log .= "\t --skiping {$person->email}\n";
continue;
}
// create html response
$content = array("email" => $person->email);
$response->createFromTemplate('autoinvitation.tpl', $content);
$response->internal = true;
$html = $render->renderHTML($service, $response);
// send invitation email
$subject = "Dos problemas, y una solucion";
$email->sendEmail($person->email, $subject, $html);
// mark as sent
$connection->deepQuery("\n\t\t\t\tSTART TRANSACTION;\n\t\t\t\tDELETE FROM autoinvitations WHERE email='{$person->email}';\n\t\t\t\tINSERT INTO remarketing(email, type) VALUES ('{$person->email}', 'AUTOINVITE');\n\t\t\t\tCOMMIT;");
// display notifications
$log .= "\t{$person->email}\n";
}
// get final delay
$timeEnd = time();
$timeDiff = $timeEnd - $timeStart;
// printing log
$log .= "EXECUTION TIME: {$timeDiff} seconds\n\n";
echo $log;
// saving the log
$logger = new \Phalcon\Logger\Adapter\File("{$wwwroot}/logs/remarketing_autoinvitation.log");
$logger->log($log);
$logger->close();
// save the status in the database
$connection->deepQuery("UPDATE task_status SET executed=CURRENT_TIMESTAMP, delay='{$timeDiff}' WHERE task='autoinvitation'");
}
public function mainAction()
{
// inicialize supporting classes
$timeStart = time();
$connection = new Connection();
$email = new Email();
$service = new Service();
$service->showAds = true;
$render = new Render();
$response = new Response();
$wwwroot = $this->di->get('path')['root'];
$log = "";
// get people who did not finish a survey for the last 3 days
$surveys = $connection->deepQuery("\n\t\t\tSELECT A.*, B.title, B.deadline, B.value FROM \n\t\t\t(\n\t\t\t\tSELECT email, survey, \n\t\t\t\tDATEDIFF(CURRENT_DATE, MAX(date_choosen)) as days_since,\n\t\t\t\t(\n\t\t\t\t\tSELECT COUNT(*) \n\t\t\t\t\tFROM _survey_question \n\t\t\t\t\tWHERE _survey_question.survey = _survey_answer_choosen.survey\n\t\t\t\t) as total, \n\t\t\t\tCOUNT(question) as choosen from _survey_answer_choosen GROUP BY email, survey\n\t\t\t) A\n\t\t\tJOIN _survey B\n\t\t\tON A.survey = B.id\n\t\t\tWHERE A.total > A.choosen \n\t\t\tAND A.days_since >= 7\n\t\t\tAND B.active = 1\n\t\t\tAND DATEDIFF(B.deadline, B.date_created) > 0\n\t\t\tAND A.email NOT IN (SELECT DISTINCT email FROM remarketing WHERE type='SURVEY')");
// send emails to users
$log .= "\nSURVEY REMARKETING (" . count($surveys) . ")\n";
foreach ($surveys as $survey) {
$content = array("survey" => $survey->survey, "days" => $survey->days_since, "missing" => $survey->total - $survey->choosen, "title" => $survey->title, "deadline" => $survey->deadline, "value" => $survey->value);
// create html response
$response->setResponseSubject("No queremos que pierda \${$survey->value}");
$response->createFromTemplate('surveyReminder.tpl', $content);
$response->internal = true;
// send email to the person
$html = $render->renderHTML($service, $response);
$email->sendEmail($survey->email, $response->subject, $html);
// add entry to remarketing
$connection->deepQuery("INSERT INTO remarketing(email, type) VALUES ('{$survey->email}', 'SURVEY');");
// display notifications
$log .= "\t{$survey->email} | surveyID: {$survey->survey} \n";
}
// get final delay
$timeEnd = time();
$timeDiff = $timeEnd - $timeStart;
// printing log
$log .= "EXECUTION TIME: {$timeDiff} seconds\n\n";
echo $log;
// saving the log
$logger = new \Phalcon\Logger\Adapter\File("{$wwwroot}/logs/surveyreminder.log");
$logger->log($log);
$logger->close();
// save the status in the database
$connection->deepQuery("UPDATE task_status SET executed=CURRENT_TIMESTAMP, delay='{$timeDiff}' WHERE task='survey'");
}
/**
* Log message into file, notify the admin on stagging/production
*
* @param mixed $messages messages to log
*/
public static function log($messages)
{
$config = \Phalcon\DI::getDefault()->getShared('config');
$dump = new Dump();
if ($config->app->env == "development") {
foreach ($messages as $key => $message) {
echo $dump->one($message, $key);
}
exit;
} else {
$logger = new \Phalcon\Logger\Adapter\File(APP_PATH . '/app/common/logs/' . date('Ymd') . '.log', array('mode' => 'a+'));
$log = '';
if (is_array($messages) || $messages instanceof \Countable) {
foreach ($messages as $key => $message) {
if (in_array($key, array('alert', 'debug', 'error', 'info', 'notice', 'warning'))) {
$logger->{$key}($message);
} else {
$logger->log($message);
}
$log .= $dump->one($message, $key);
}
} else {
$logger->log($messages);
$log .= $dump->one($messages);
}
if ($config->app->env != "testing") {
$email = new Email();
$email->prepare(__('Something is wrong!'), $config->app->admin, 'error', array('log' => $log));
if ($email->Send() !== true) {
$logger->log($email->ErrorInfo);
}
}
$logger->close();
}
}
/**
* Process the requests coming by email, usually from webhooks
*
* @author salvipascual
* @param String Email
* @param String
* @param String Email
* @param String
* @param String
* @param Array
* @param Enum mandrill,mailgun
* @param String
* @param String $messageID
* */
private function processEmail($fromEmail, $fromName, $toEmail, $subject, $body, $attachments, $webhook, $messageID = null)
{
$connection = new Connection();
$utils = new Utils();
// check if the user is blocked
$blocked = $connection->deepQuery("SELECT email FROM person WHERE email='{$fromEmail}' AND blocked=1");
if (count($blocked) > 0) {
// create the response for blocked email
$response = new Response();
$response->setEmailLayout("email_simple.tpl");
$subject = "Su cuenta de Apretaste ha sido bloqueada";
$response->setResponseSubject($subject);
$response->createFromTemplate("blocked.tpl", array());
$response->internal = true;
// render the template as html
$render = new Render();
$body = $render->renderHTML(new Service(), $response);
// let the user know that the account is blocked and stop the execution
$emailSender = new Email();
$emailSender->setRespondEmailID($messageID);
$emailSender->sendEmail($fromEmail, $subject, $body);
exit;
}
// if the email comes as part of a campaign, mark it as opened
$campaign = $utils->getCampaignTracking($toEmail);
if ($campaign) {
$connection->deepQuery("\r\n\t\t\t\tUPDATE campaign SET opened=opened+1 WHERE id='{$campaign}';\r\n\t\t\t\tUPDATE campaign_sent SET status='OPENED', date_opened=CURRENT_TIMESTAMP WHERE campaign='{$campaign}' AND email='{$fromEmail}'");
}
// do not continue procesing the email if the sender is not valid
$status = $utils->deliveryStatus($fromEmail, 'in');
if ($status != 'ok') {
return;
}
// remove double spaces and apostrophes from the subject
// sorry apostrophes break the SQL code :-(
$subject = trim(preg_replace('/\\s{2,}/', " ", preg_replace('/\'|`/', "", $subject)));
// save the email as received
$connection->deepQuery("INSERT INTO delivery_received(user,mailbox,subject,attachments_count,webhook) VALUES ('{$fromEmail}','{$toEmail}','{$subject}','" . count($attachments) . "','{$webhook}')");
// save to the webhook last usage, to alert if the web
$connection->deepQuery("UPDATE task_status SET executed=CURRENT_TIMESTAMP WHERE task='{$webhook}'");
// if there are attachments, download them all and create the files in the temp folder
$wwwroot = $this->di->get('path')['root'];
foreach ($attachments as $attach) {
$mimeTypePieces = explode("/", $attach->type);
$fileType = $mimeTypePieces[0];
$fileNameNoExtension = $utils->generateRandomHash();
// convert images to jpg and save it to temporal
if ($fileType == "image") {
$attach->type = image_type_to_mime_type(IMAGETYPE_JPEG);
$filePath = "{$wwwroot}/temp/{$fileNameNoExtension}.jpg";
imagejpeg(imagecreatefromstring(base64_decode($attach->content)), $filePath);
$utils->optimizeImage($filePath);
} else {
$extension = $mimeTypePieces[1];
$filePath = "{$wwwroot}/temp/{$fileNameNoExtension}.{$extension}";
$ifp = fopen($filePath, "wb");
fwrite($ifp, $attach->content);
fclose($ifp);
}
// grant full access to the file
chmod($filePath, 0777);
$attach->path = $filePath;
}
// save the webhook log
$logger = new \Phalcon\Logger\Adapter\File("{$wwwroot}/logs/webhook.log");
$logger->log("Webhook:{$webhook}, From:{$fromEmail}, To:{$toEmail}, Subject:{$subject}, Attachments:" . count($attachments));
$logger->close();
// execute the query
$this->renderResponse($fromEmail, $toEmail, $subject, $fromName, $body, $attachments, "email", $messageID);
}
<?php
$logger = new \Phalcon\Logger\Adapter\File("app/logs/test.log");
$logger->log("This is a message");
$logger->log("This is an error", \Phalcon\Logger::ERROR);
$logger->error("This is another error");
$logger->close();
/**
* Handle webhook requests
* @author salvipascual
* */
public function webhookAction()
{
// get the mandrill json structure from the post
$mandrill_events = $_POST['mandrill_events'];
// get values from the json
$event = json_decode($mandrill_events);
$fromEmail = $event[0]->msg->from_email;
$fromName = isset($event[0]->msg->from_name) ? $event[0]->msg->from_name : "";
$toEmail = $event[0]->msg->email;
$subject = $event[0]->msg->headers->Subject;
$body = isset($event[0]->msg->text) ? $event[0]->msg->text : "";
$filesAttached = empty($event[0]->msg->attachments) ? array() : $event[0]->msg->attachments;
$attachments = array();
// create a new connection to the database
$connection = new Connection();
// do not email if there is an error
$email = new Email();
$status = $email->deliveryStatus($fromEmail);
if ($status != 'ok') {
$connection->deepQuery("INSERT INTO delivery_error(incoming_email,apretaste_email,reason) VALUES ('{$fromEmail}','{$toEmail}','{$status}')");
return;
}
// if there are attachments, download them all and create the files in the temp folder
if (count($filesAttached) > 0) {
// save the attached files and create the response array
$utils = new Utils();
$wwwroot = $this->di->get('path')['root'];
foreach ($filesAttached as $key => $values) {
$mimeType = $values->type;
$content = $values->content;
$mimeTypePieces = explode("/", $mimeType);
$fileType = $mimeTypePieces[0];
$extension = $mimeTypePieces[1];
$fileNameNoExtension = $utils->generateRandomHash();
// convert images to png and save it to temporal
if ($fileType == "image") {
// save image as a png file
$mimeType = image_type_to_mime_type(IMAGETYPE_PNG);
$filePath = "{$wwwroot}/temp/{$fileNameNoExtension}.png";
imagepng(imagecreatefromstring(base64_decode($content)), $filePath);
} else {
$filePath = "{$wwwroot}/temp/{$fileNameNoExtension}.{$extension}";
$ifp = fopen($filePath, "wb");
fwrite($ifp, base64_decode($content));
fclose($ifp);
}
// create new object
$object = new stdClass();
$object->path = $filePath;
$object->type = $mimeType;
$attachments[] = $object;
}
}
// update the counter of emails received from that mailbox
$today = date("Y-m-d H:i:s");
$connection->deepQuery("UPDATE jumper SET received_count=received_count+1, last_usage='{$today}' WHERE email='{$toEmail}'");
// save the webhook log
$wwwroot = $this->di->get('path')['root'];
$logger = new \Phalcon\Logger\Adapter\File("{$wwwroot}/logs/webhook.log");
$logger->log("From: {$fromEmail}, Subject: {$subject}\n{$mandrill_events}\n\n");
$logger->close();
// execute the query
$this->renderResponse($fromEmail, $subject, $fromName, $body, $attachments, "email");
}
public function mainAction()
{
// inicialize supporting classes
$timeStart = time();
$connection = new Connection();
$email = new Email();
$service = new Service();
$service->showAds = true;
$render = new Render();
$response = new Response();
$utils = new Utils();
$wwwroot = $this->di->get('path')['root'];
$log = "";
// list and total of services
$services = $connection->deepQuery("SELECT name, description FROM service WHERE name <> 'ayuda' AND name <> 'terminos' AND name <> 'excluyeme' AND listed = 1;");
$arr = array();
foreach ($services as $servicex) {
$arr[$servicex->name] = $servicex;
}
$services = $arr;
$total_services = count($services);
// users by service
$sql_users_by_service = "SELECT requestor, service FROM utilization WHERE service <> 'rememberme' GROUP BY requestor, service";
// usage of services
$sql_usage = "SELECT requestor, count(service) as part, {$total_services} as total FROM ({$sql_users_by_service}) subq1 GROUP BY requestor, total";
// filtering by less usage
$sql_less_usage = "SELECT requestor as email, (SELECT sent FROM remarketing WHERE remarketing.email = subq2.requestor ORDER BY sent DESC LIMIT 1) as last_remarketing FROM ({$sql_usage}) subq2 WHERE part/total <= 0.2 ";
// filtering by last remarketing (one by month)
$sql = "SELECT email FROM ({$sql_less_usage}) subq3 WHERE datediff(CURRENT_DATE, last_remarketing) > 30 or datediff(CURRENT_DATE, last_remarketing) is null group by email";
$users = $connection->deepQuery("{$sql};");
// send the remarketing
$log .= "\nLESS USAGE REMARKETING (" . count($users) . ")\n";
foreach ($users as $person) {
// all services
$his_services = $services;
// getting services used by user
$services_of_user = $connection->deepQuery("SELECT service as name FROM ({$sql_users_by_service}) subq1 WHERE requestor = '{$person->email}';");
// remove services used by user from the list
foreach ($services_of_user as $servicex) {
if (isset($his_services[$servicex->name])) {
unset($his_services[$servicex->name]);
}
}
// create the variabels to pass to the template
$content = array("services" => $his_services);
// create html response
$response->createFromTemplate('lessusage.tpl', $content);
$response->internal = true;
$html = $render->renderHTML($service, $response);
// move remarketing to the next state and add $1 to his/her account
$email->sendEmail($person->email, "Lo que te estas perdiendo en Apretaste", $html);
// move remarketing to the next state and add +1 credits
$connection->deepQuery("INSERT INTO remarketing(email, type) VALUES ('{$person->email}', 'LESSUSAGE');");
// display notifications
$log .= "\t{$person->email}\n";
}
// get final delay
$timeEnd = time();
$timeDiff = $timeEnd - $timeStart;
// printing log
$log .= "EXECUTION TIME: {$timeDiff} seconds\n\n";
echo $log;
// saving the log
$logger = new \Phalcon\Logger\Adapter\File("{$wwwroot}/logs/lessusage.log");
$logger->log($log);
$logger->close();
// save the status in the database
$connection->deepQuery("UPDATE task_status SET executed=CURRENT_TIMESTAMP, delay='{$timeDiff}' WHERE task='lessusage'");
}
public function mainAction()
{
// inicialize supporting classes
$timeStart = time();
$connection = new Connection();
$email = new Email();
$service = new Service();
$service->showAds = true;
$render = new Render();
$response = new Response();
$utils = new Utils();
$wwwroot = $this->di->get('path')['root'];
$log = "";
/*
* FIRST REMINDER
* */
// people missed for the last 30 days and with no remarketing emails unopened
$firstReminderPeople = $connection->deepQuery("\n\t\t\tSELECT email, last_access \n\t\t\tFROM person \n\t\t\tWHERE active=1 \n\t\t\tAND IFNULL(DATEDIFF(CURRENT_DATE, last_access),99) > 30 \n\t\t\tAND email not in (SELECT DISTINCT email FROM remarketing WHERE opened IS NULL)\n\t\t\tORDER BY insertion_date ASC\n\t\t\tLIMIT 200");
// send the remarketing
$log .= "\nFIRST REMINDER (" . count($firstReminderPeople) . ")\n";
foreach ($firstReminderPeople as $person) {
// check number of days since the email was last checked
$datediff = time() - strtotime($person->last_access);
$daysSinceLastChecked = floor($datediff / (60 * 60 * 24));
// validate old emails to avoid bounces
if ($daysSinceLastChecked > 60) {
// re-validate the email
$res = $utils->deepValidateEmail($person->email);
// keep it for later if it was temporal
if ($res[0] == "temporal") {
$log .= "\t --skiping {$person->email}\n";
continue;
}
// for other than ok, unsubscribe and do not email
if ($res[0] != "ok") {
$utils->unsubscribeFromEmailList($person->email);
$connection->deepQuery("UPDATE person SET active=0 WHERE email='{$person->email}'");
$log .= "\t --skiping {$person->email}\n";
continue;
}
}
// get services that changed since last time
$sql = "SELECT * FROM service WHERE insertion_date BETWEEN '{$person->last_access}' AND CURRENT_TIMESTAMP AND listed=1";
$services = $connection->deepQuery($sql);
// create the variabels to pass to the template
$content = array("services" => $services);
$images = array("{$wwwroot}/public/images/missyou.jpg");
// create html response
$response->createFromTemplate('remindme1.tpl', $content, $images);
$response->internal = true;
$html = $render->renderHTML($service, $response);
// move remarketing to the next state and add $1 to his/her account
$email->sendEmail($person->email, "Se le extranna por Apretaste", $html, $images);
// move remarketing to the next state and add +1 credits
$connection->deepQuery("\n\t\t\t\tSTART TRANSACTION;\n\t\t\t\tUPDATE person SET credit=credit+1 WHERE email='{$person->email}';\r\n\t\t\t\tINSERT INTO remarketing(email, type) VALUES ('{$person->email}', 'REMINDER1');\n\t\t\t\tCOMMIT;");
// display notifications
$log .= "\t{$person->email}\n";
}
/*
* SECOND REMINDER
* */
// people with REMINDER1 unaswered for the last 30 days, and without REMINDER2 created
$secondReminderPeople = $connection->deepQuery("\n\t\t\tSELECT email\n\t\t\tFROM remarketing A\n\t\t\tWHERE type='REMINDER1'\n\t\t\tAND opened IS NULL\n\t\t\tAND DATEDIFF(CURRENT_DATE, sent) > 30\n\t\t\tAND (SELECT COUNT(email) FROM remarketing WHERE type='REMINDER2' AND opened IS NULL AND email=A.email)=0");
// send the remarketing
$log .= "SECOND REMINDER (" . count($secondReminderPeople) . ")\n";
foreach ($secondReminderPeople as $person) {
// create html response
$response->createFromTemplate('remindme2.tpl', array());
$response->internal = true;
$html = $render->renderHTML($service, $response);
// send email to the $person->email
$email->sendEmail($person->email, "Hace rato no le veo", $html);
// move remarketing to the next state and add +1 credits
$connection->deepQuery("\r\n\t\t\t\tSTART TRANSACTION;\r\n\t\t\t\tUPDATE person SET credit=credit+1 WHERE email='{$person->email}';\n\t\t\t\tINSERT INTO remarketing(email, type) VALUES ('{$person->email}', 'REMINDER2');\r\n\t\t\t\tCOMMIT;");
// display notifications
$log .= "\t{$person->email}\n";
}
/*
* EXCLUDE
* */
// people with REMINDER2 unaswered, sent 30 days ago and not EXCLUDED
$thirdReminderPeople = $connection->deepQuery("\n\t\t\tSELECT email\n\t\t\tFROM remarketing A\n\t\t\tWHERE type='REMINDER2'\n\t\t\tAND opened IS NULL\n\t\t\tAND DATEDIFF(CURRENT_DATE, sent) > 30\n\t\t\tAND (SELECT COUNT(email) from remarketing WHERE type='EXCLUDED' AND opened IS NULL AND email=A.email)=0");
// unsubcribe people
$log .= "UNSUSCRIBING (" . count($thirdReminderPeople) . ")\n";
foreach ($thirdReminderPeople as $person) {
// unsubscribe person
$utils->unsubscribeFromEmailList($person->email);
// move remarketing to the next state and unsubscribe
$connection->deepQuery("\r\n\t\t\t\tSTART TRANSACTION;\r\n\t\t\t\tUPDATE person SET active=0 WHERE email='{$person->email}';\n\t\t\t\tINSERT INTO remarketing(email, type) VALUES ('{$person->email}', 'EXCLUDED');\r\n\t\t\t\tCOMMIT;");
// display notifications
$log .= "\t{$person->email}\n";
}
// get final delay
$timeEnd = time();
$timeDiff = $timeEnd - $timeStart;
// printing log
$log .= "EXECUTION TIME: {$timeDiff} seconds\n\n";
echo $log;
// saving the log
$logger = new \Phalcon\Logger\Adapter\File("{$wwwroot}/logs/remarketing.log");
$logger->log($log);
$logger->close();
// save the status in the database
$connection->deepQuery("UPDATE task_status SET executed=CURRENT_TIMESTAMP, delay='{$timeDiff}' WHERE task='remarketing'");
}
public static function log(Exception $e)
{
//错误日志记录
if (\Phalcon\DI::getDefault()->getShared('config')->log->file) {
$logger = new \Phalcon\Logger\Adapter\File(APP_PATH . '/common/logs/' . date('Ymd') . '.log', array('mode' => 'a+'));
$logger->error(get_class($e) . '[' . $e->getCode() . ']: ' . $e->getMessage());
$logger->info($e->getFile() . '[' . $e->getLine() . ']');
$logger->debug("Trace: \n" . $e->getTraceAsString() . "\n");
$logger->close();
}
if (\Phalcon\DI::getDefault()->getShared('config')->log->debug) {
\App\Controllers\ControllerCommon::instance()->exception($e);
} else {
\App\Controllers\ControllerCommon::instance()->exception('系统发生了错误，请联系管理员进行修复，错误代码：' . $e->getCode());
}
}
