public function save()
{
$connection = new Connection();
$sSQL = "INSERT INTO tblike(UserID, RecipeID)\n\t\t\t VALUES ('" . $connection->escape($this->iUserID) . "','" . $connection->escape($this->iRecipeID) . "')";
$bSuccess = $connection->query($sSQL);
if ($bSuccess == true) {
$this->iLikeID = $connection->get_insert_id();
} else {
die($sSQL . " fails!");
}
}
public function saveReply()
{
$connection = new Connection();
$sSQL = "INSERT INTO tbcomment(Comment, UserID, OriginalID)\n VALUES ('" . $connection->escape($this->sComment) . "','" . $connection->escape($this->iUserID) . "','" . $connection->escape($this->iOriginalID) . "')";
$bSuccess = $connection->query($sSQL);
if ($bSuccess == true) {
$this->iCommentID = $connection->get_insert_id();
} else {
die($sSQL . " fails!");
}
}
/**
* Subservice PUBLICAR
*
* @param Request $request
*/
public function _publicar($request)
{
$connection = new Connection();
$title = substr(trim($request->query), 0, 100);
$body = substr(trim($request->body), 0, 1000);
if ($title == '') {
$title = substr($body, 0, 100);
}
$title = $connection->escape($title);
$body = $connection->escape($body);
$title = str_replace("'", '\\' . "'", $title);
$body = str_replace("'", '\\' . "'", $body);
$hash = $this->utils->generateRandomHash();
$di = \Phalcon\DI\FactoryDefault::getDefault();
$wwwroot = $di->get('path')['root'];
// insert new ad with a year of life
$connection->deepQuery("INSERT INTO ads (title,description,owner,expiration_date) VALUES ('{$title}','{$body}','{$request->email}',DATE_ADD(CURRENT_DATE, INTERVAL 1 YEAR));");
// get id of the new ad inserted
$id = $connection->deepQuery("SELECT id FROM ads WHERE owner = '{$request->email}' ORDER BY time_inserted DESC LIMIT 100;");
$id = $id[0]->id;
// insert one image for the ad
foreach ($request->attachments as $at) {
if (isset($at->type) && strpos("jpg,jpeg,image/jpg,image/jpeg,image/png,png,image/gif,gif", $at->type) !== false && isset($at->path)) {
// save the image
$img = file_get_contents($at->path);
$filePath = "{$wwwroot}/public/ads/" . md5($id) . ".jpg";
file_put_contents($filePath, $img);
// optimize the image
$this->utils->optimizeImage($filePath);
// only first image
break;
}
}
// respond to the owner of the ad
$response = new Response();
$response->setResponseSubject("Su anuncio ha sido agregado");
$response->createFromTemplate('publish.tpl', array('id' => $id, 'userEmail' => $request->email));
// alert us about the new ad
$alert = new Response();
$alert->setResponseEmail("*****@*****.**");
$alert->setEmailLayout("email_simple.tpl");
$alert->setResponseSubject('Nueva publicidad en Apretaste');
$alert->createFromTemplate('notify.tpl', array('owner' => $request->email, 'title' => $title, 'body' => $body));
return array($response, $alert);
}
public function testEscape()
{
$this->assertEquals("'Simon''s Cat'", $this->connection->escape("Simon's Cat"));
$this->assertEquals(12, $this->connection->escape(12));
$this->assertEquals("'0012'", $this->connection->escape('0012'));
$this->assertEquals('NULL', strtoupper($this->connection->escape(null)));
$this->assertEquals("''", strtoupper($this->connection->escape('')));
$this->assertEquals(1, $this->connection->escape(true));
}
public function save()
{
$connection = new Connection();
$sSQL = "INSERT INTO tbnewsletter(Email)\n VALUES ('" . $connection->escape($this->sEmail) . "')";
$bSuccess = $connection->query($sSQL);
if ($bSuccess == true) {
$this->iSubscriberID = $connection->get_insert_id();
} else {
die($sSQL . " fails!");
}
}
/**
* @param string $_table
*/
public function truncate($_table)
{
/* ## LOGGER ## */
if (isset($this->logger)) {
$this->logger->DEBUG('truncate: ' . $_table);
}
if (empty($_table)) {
throw new UndefinedTabelException('null');
}
$table = $this->connection->escape($_table);
$sql = 'TRUNCATE TABLE `' . $table . '`';
$result = $this->connection->send($sql);
}
public function loadByEmail($sMemberEmail)
{
$oCon = new Connection();
$sSQL = "SELECT MemberID FROM tbmember WHERE MemberEmail='" . $oCon->escape($sMemberEmail) . "'";
$oResultSet = $oCon->query($sSQL);
$aRow = $oCon->fetchArray($oResultSet);
if ($aRow == true) {
$sID = $aRow["MemberID"];
$this->load($sID);
return true;
} else {
return false;
}
$oCon->close();
}
/**
* @param string $_index
*/
public function delete($_index)
{
/* ## LOGGER ## */
if (isset($this->logger)) {
$this->logger->DEBUG('delete');
}
if (empty($_index)) {
throw new UndefinedRowException('null');
}
$table = $this->connection->escape($this->table);
$primary = $this->connection->escape($this->primary);
$index = $this->connection->escape($_index);
$sql = 'DELETE FROM `' . $table . '` WHERE `' . $primary . '` = \'' . $index . '\';';
$result = $this->connection->send($sql);
if ($this->connection->getAffectedRows() <= 0) {
throw new UndefinedRowException('undefined ' . $primary . '=' . $index);
}
}
public function save()
{
$connection = new Connection();
if ($this->iUserID == 0) {
// if new customer
$sSQL = "INSERT INTO tbuser (FirstName, LastName, Username, Address, Email, Telephone, Password, Admin)\n VALUES ('" . $connection->escape($this->sFirstName) . "','" . $connection->escape($this->sLastName) . "','" . $connection->escape($this->sUsername) . "','" . $connection->escape($this->sAddress) . "','" . $connection->escape($this->sEmail) . "','" . $connection->escape($this->iTelephone) . "','" . $connection->escape($this->sPassword) . "','" . $connection->escape($this->iAdmin) . "')";
$bSuccess = $connection->query($sSQL);
if ($bSuccess == true) {
$this->iUserID = $connection->get_insert_id();
} else {
die($sSQL . " fails");
}
} else {
// if updating an existing customer
$sSQL = "UPDATE tbuser\n SET UserID = '" . $connection->escape($this->iUserID) . "', FirstName ='" . $connection->escape($this->sFirstName) . "', LastName ='" . $connection->escape($this->sLastName) . "', Username = '******', Address = '" . $connection->escape($this->sAddress) . "', Email = '" . $connection->escape($this->sEmail) . "', Telephone = '" . $connection->escape($this->iTelephone) . "', Password ='******', Admin ='" . $connection->escape($this->iAdmin) . "'\n WHERE UserID =" . $connection->escape($this->iUserID);
$bSuccess = $connection->query($sSQL);
if ($bSuccess == false) {
die($sSQL . " fails");
}
}
}
/**
* TODO: This is exactly what I don't want to do. "Roll my own" SQL handler.
* However, the requirements for this package have led to this point for now.
*
* @param Connection $connection
* @return mixed
*/
protected function quoteIntoSql(Connection $connection)
{
$quotedSql = $this->sql;
$quotedParams = [];
foreach ($this->params as $key => $value) {
if (is_null($value)) {
$quotedParams[$key] = 'NULL';
} else {
if (is_integer($value)) {
$quotedParams[$key] = (int) $value;
} else {
if (in_array($value, $this->reserved_words)) {
$quotedParams[$key] = $value;
} else {
$quotedParams[$key] = '\'' . $connection->escape($value) . '\'';
}
}
}
}
return strtr($quotedSql, $quotedParams);
}
public function save()
{
$connection = new Connection();
if ($this->iRecipeID == 0) {
$sSQL = "INSERT INTO tbrecipe(Title, AuthorNotes, Ingredients, Directions, ImagePath, UserID, RecipeTypeID)\n VALUES ('" . $connection->escape($this->sTitle) . "','" . $connection->escape($this->sAuthorNotes) . "','" . $connection->escape($this->sIngredients) . "','" . $connection->escape($this->sDirections) . "','" . $connection->escape($this->sImagePath) . "','" . $connection->escape($this->iUserID) . "','" . $connection->escape($this->iRecipeTypeID) . "')";
$bSuccess = $connection->query($sSQL);
if ($bSuccess == true) {
$this->iRecipeID = $connection->get_insert_id();
} else {
die($sSQL . " fails!");
}
} else {
// update instead
$sSQL = "UPDATE tbrecipe\n SET Title = '" . $connection->escape($this->sTitle) . "',AuthorNotes ='" . $connection->escape($this->sAuthorNotes) . "',Ingredients='" . $connection->escape($this->sIngredients) . "',Directions='" . $connection->escape($this->sDirections) . "',ImagePath='" . $connection->escape($this->sImagePath) . "',UserID='" . $connection->escape($this->iUserID) . "', RecipeTypeID='" . $connection->escape($this->iRecipeTypeID) . "'\n WHERE RecipeID=" . $this->iRecipeID;
$bSuccess = $connection->query($sSQL);
if ($bSuccess == false) {
die($sSQL . " fails!");
}
}
}
public function save()
{
$connection = new Connection();
$a = date("Y-m-d");
$sSQL = "INSERT INTO tborder(OrderDate,OrderStatus, RecipientName, DeliveryAddress, BillingAddress, Payment, AccountName, CardNumber, ExpiryDate, Security, UserID)\n VALUES ('" . $connection->escape($a) . "','" . $connection->escape($this->sOrderStatus) . "','" . $connection->escape($this->sRecipientName) . "','" . $connection->escape($this->sDelivery) . "','" . $connection->escape($this->sBilling) . "','" . $connection->escape($this->sPayment) . "','" . $connection->escape($this->sAccountName) . "','" . $connection->escape($this->iCardNumber) . "','" . $connection->escape($this->sExpiry) . "','" . $connection->escape($this->iSecurity) . "','" . $connection->escape($this->iUserID) . "')";
$bSuccess = $connection->query($sSQL);
if ($bSuccess == true) {
$this->iOrderID = $connection->get_insert_id();
} else {
die($sSQL . " fails!");
}
}
public function __toString()
{
return sprintf("%s like '%s'", $this->field, Connection::escape($this->values[0]));
}
/**
* Respond to a request based on the parameters passed
*
* @author salvipascual
* @param String, email
* @param String
* @param String, email
* @param String
* @param Array of Objects {type,content,path}
* @param Enum: html,json,email
* @param String, email
* @param String $messageID
* */
private function renderResponse($email, $fromEmail, $subject, $sender = "", $body = "", $attachments = array(), $format = "html", $messageID = NULL)
{
// get the time when the service started executing
$execStartTime = date("Y-m-d H:i:s");
// remove double spaces and apostrophes from the subject
// sorry apostrophes break the SQL code :-(
$subject = trim(preg_replace('/\\s{2,}/', " ", preg_replace('/\'|`/', "", $subject)));
// get the name of the service based on the subject line
$subjectPieces = explode(" ", $subject);
$serviceName = strtolower($subjectPieces[0]);
unset($subjectPieces[0]);
// check the service requested actually exists
$utils = new Utils();
$connection = new Connection();
// select the default service if service does not exist
$alias = $serviceName;
if (!$utils->serviceExist($serviceName)) {
$serviceName = $utils->getDefaultService($fromEmail);
} else {
if ($serviceName !== $alias) {
// increase the counter for alias
$connection->deepQuery("UPDATE service_alias SET used = used + 1 WHERE alias = '{$alias}';");
}
}
// update topics if you are contacting via the secure API
if ($serviceName == "secured") {
// disregard any footer message and decript new subject
$message = trim(explode("--", $body)[0]);
$subject = $utils->decript($email, $message);
// get the name of the service based on the subject line
$subjectPieces = explode(" ", $subject);
$serviceName = strtolower($subjectPieces[0]);
unset($subjectPieces[0]);
// if the service don't exist, throw an error and exit
if (!$utils->serviceExist($serviceName)) {
error_log("Service {$serviceName} do not exist");
exit;
}
}
// include the service code
$wwwroot = $this->di->get('path')['root'];
include "{$wwwroot}/services/{$serviceName}/service.php";
// check if a subservice is been invoked
$subServiceName = "";
if (isset($subjectPieces[1]) && !preg_match('/\\?|\\(|\\)|\\\\|\\/|\\.|\\$|\\^|\\{|\\}|\\||\\!/', $subjectPieces[1])) {
$serviceClassMethods = get_class_methods($serviceName);
if (preg_grep("/^_{$subjectPieces[1]}\$/i", $serviceClassMethods)) {
$subServiceName = strtolower($subjectPieces[1]);
unset($subjectPieces[1]);
}
}
// get the service query
$query = implode(" ", $subjectPieces);
// create a new Request object
$request = new Request();
$request->email = $email;
$request->name = $sender;
$request->subject = $subject;
$request->body = $body;
$request->attachments = $attachments;
$request->service = $serviceName;
$request->subservice = trim($subServiceName);
$request->query = trim($query);
// get the path to the service
$servicePath = $utils->getPathToService($serviceName);
// get details of the service
if ($this->di->get('environment') == "sandbox") {
// get details of the service from the XML file
$xml = simplexml_load_file("{$servicePath}/config.xml");
$serviceCreatorEmail = trim((string) $xml->creatorEmail);
$serviceDescription = trim((string) $xml->serviceDescription);
$serviceCategory = trim((string) $xml->serviceCategory);
$serviceUsageText = trim((string) $xml->serviceUsage);
$showAds = isset($xml->showAds) && $xml->showAds == 0 ? 0 : 1;
$serviceInsertionDate = date("Y/m/d H:m:s");
} else {
// get details of the service from the database
$sql = "SELECT * FROM service WHERE name = '{$serviceName}'";
$result = $connection->deepQuery($sql);
$serviceCreatorEmail = $result[0]->creator_email;
$serviceDescription = $result[0]->description;
$serviceCategory = $result[0]->category;
$serviceUsageText = $result[0]->usage_text;
$serviceInsertionDate = $result[0]->insertion_date;
$showAds = $result[0]->ads == 1;
}
// create a new service Object of the user type
$userService = new $serviceName();
$userService->serviceName = $serviceName;
$userService->serviceDescription = $serviceDescription;
$userService->creatorEmail = $serviceCreatorEmail;
$userService->serviceCategory = $serviceCategory;
$userService->serviceUsage = $serviceUsageText;
$userService->insertionDate = $serviceInsertionDate;
$userService->pathToService = $servicePath;
$userService->showAds = $showAds;
$userService->utils = $utils;
// run the service and get a response
if (empty($subServiceName)) {
$response = $userService->_main($request);
} else {
$subserviceFunction = "_{$subServiceName}";
$response = $userService->{$subserviceFunction}($request);
}
// a service can return an array of Response or only one.
// we always treat the response as an array
$responses = is_array($response) ? $response : array($response);
// adding extra responses from Utils
$extraResponses = Utils::getExtraResponses();
$responses = array_merge($responses, $extraResponses);
Utils::clearExtraResponses();
// clean the empty fields in the response
foreach ($responses as $rs) {
$rs->email = empty($rs->email) ? $email : $rs->email;
// check if is first request of the day
$requestsToday = $utils->getTotalRequestsTodayOf($rs->email);
$stars = 0;
if ($requestsToday == 0) {
// run the tickets's game
// @note: este chequeo se hace despues de verificar si es el primer
// correo del dia, para no preguntar chequear mas veces
// innecesariamente en el resto del dia
$stars = $utils->getRaffleStarsOf($rs->email, false);
if ($stars === 4) {
// insert 10 tickets for user
$sqlValues = "('{$email}', 'GAME')";
$sql = "INSERT INTO ticket(email, origin) VALUES " . str_repeat($sqlValues . ",", 9) . "{$sqlValues};";
$connection->deepQuery($sql);
// add notification to user
$utils->addNotification($rs->email, "GAME", "Haz ganado 10 tickets para Rifa por utilizar Apretaste durante 5 días seguidos", "RIFA", "IMPORTANT");
}
$stars++;
}
$rs->subject = empty($rs->subject) ? "Respuesta del servicio {$serviceName}" : $rs->subject;
$rs->content['num_notifications'] = $utils->getNumberOfNotifications($rs->email);
$rs->content['raffle_stars'] = $stars;
$rs->content['requests_today'] = $requestsToday;
}
// create a new render
$render = new Render();
// render the template and echo on the screen
if ($format == "html") {
$html = "";
for ($i = 0; $i < count($responses); $i++) {
$html .= "<br/><center><small><b>To:</b> " . $responses[$i]->email . ". <b>Subject:</b> " . $responses[$i]->subject . "</small></center><br/>";
$html .= $render->renderHTML($userService, $responses[$i]);
if ($i < count($responses) - 1) {
$html .= "<br/><hr/><br/>";
}
}
$usage = nl2br(str_replace('{APRETASTE_EMAIL}', $utils->getValidEmailAddress(), $serviceUsageText));
$html .= "<br/><hr><center><p><b>XML DEBUG</b></p><small>";
$html .= "<p><b>Owner: </b>{$serviceCreatorEmail}</p>";
$html .= "<p><b>Category: </b>{$serviceCategory}</p>";
$html .= "<p><b>Description: </b>{$serviceDescription}</p>";
$html .= "<p><b>Usage: </b><br/>{$usage}</p></small></center>";
return $html;
}
// echo the json on the screen
if ($format == "json") {
return $render->renderJSON($response);
}
// render the template email it to the user
// only save stadistics for email requests
if ($format == "email") {
// get the person, false if the person does not exist
$person = $utils->getPerson($email);
// if the person exist in Apretaste
if ($person !== false) {
// update last access time to current and make person active
$connection->deepQuery("UPDATE person SET active=1, last_access=CURRENT_TIMESTAMP WHERE email='{$email}'");
} else {
$inviteSource = 'alone';
// alone if the user came by himself, no invitation
$sql = "START TRANSACTION;";
// start the long query
// check if the person was invited to Apretaste
$invites = $connection->deepQuery("SELECT * FROM invitations WHERE email_invited='{$email}' AND used=0 ORDER BY invitation_time DESC");
if (count($invites) > 0) {
// check how this user came to know Apretaste, for stadistics
$inviteSource = $invites[0]->source;
// give prizes to the invitations via service invitar
// if more than one person invites X, they all get prizes
foreach ($invites as $invite) {
switch ($invite->source) {
case "internal":
// assign tickets and credits
$sql .= "INSERT INTO ticket (email, origin) VALUES ('{$invite->email_inviter}', 'RAFFLE');";
$sql .= "UPDATE person SET credit=credit+0.25 WHERE email='{$invite->email_inviter}';";
// email the invitor
$newTicket = new Response();
$newTicket->setResponseEmail($invite->email_inviter);
$newTicket->setEmailLayout("email_simple.tpl");
$newTicket->setResponseSubject("Ha ganado un ticket para nuestra Rifa");
$newTicket->createFromTemplate("invitationWonTicket.tpl", array("guest" => $email));
$newTicket->internal = true;
$responses[] = $newTicket;
break;
case "abroad":
$newGuest = new Response();
$newGuest->setResponseEmail($invite->email_inviter);
$newGuest->setResponseSubject("Tu amigo ha atendido tu invitacion");
$inviter = $utils->usernameFromEmail($invite->email_inviter);
$pInviter = $utils->getPerson($invite->email_inviter);
if (!isset($pInviter->name)) {
$pInviter->name = '';
}
if ($pInviter !== false) {
if (trim($pInviter->name) !== '') {
$inviter = $pInviter->name;
}
}
$pGuest = $utils->getPerson($email);
$guest = $email;
if ($pGuest !== false) {
$guest = $pGuest->username;
}
$newGuest->createFromTemplate("invitationNewGuest.tpl", array("inviter" => $inviter, "guest" => $guest, "guest_email" => $email));
$newGuest->internal = true;
$responses[] = $newGuest;
break;
}
}
// mark all opened invitations to that email as used
$sql .= "UPDATE invitations SET used=1, used_time=CURRENT_TIMESTAMP WHERE email_invited='{$email}' AND used=0;";
}
// create a unique username and save the new person
$username = $utils->usernameFromEmail($email);
$sql .= "INSERT INTO person (email, username, last_access, source) VALUES ('{$email}', '{$username}', CURRENT_TIMESTAMP, '{$inviteSource}');";
// save details of first visit
$sql .= "INSERT INTO first_timers (email, source) VALUES ('{$email}', '{$fromEmail}');";
// check list of promotor's emails
$promoters = $connection->deepQuery("SELECT email FROM promoters WHERE email='{$fromEmail}' AND active=1;");
$prize = count($promoters) > 0;
if ($prize) {
// update the promotor
$sql .= "UPDATE promoters SET `usage`=`usage`+1, last_usage=CURRENT_TIMESTAMP WHERE email='{$fromEmail}';";
// add credit and tickets
$sql .= "UPDATE person SET credit=credit+5, source='promoter' WHERE email='{$email}';";
$sqlValues = "('{$email}', 'PROMOTER')";
$sql .= "INSERT INTO ticket(email, origin) VALUES " . str_repeat($sqlValues . ",", 9) . "{$sqlValues};";
}
// run the long query all at the same time
$connection->deepQuery($sql . "COMMIT;");
// send the welcome email
$welcome = new Response();
$welcome->setResponseEmail($email);
$welcome->setEmailLayout("email_simple.tpl");
$welcome->setResponseSubject("Bienvenido a Apretaste!");
$welcome->createFromTemplate("welcome.tpl", array("email" => $email, "prize" => $prize, "source" => $fromEmail));
$welcome->internal = true;
$responses[] = $welcome;
}
// create and configure to send email
$emailSender = new Email();
$emailSender->setRespondEmailID($messageID);
$emailSender->setEmailGroup($fromEmail);
// get params for the email and send the response emails
foreach ($responses as $rs) {
if ($rs->render) {
// save impressions in the database
$ads = $rs->getAds();
if ($userService->showAds && !empty($ads)) {
$sql = "";
if (!empty($ads[0])) {
$sql .= "UPDATE ads SET impresions=impresions+1 WHERE id='{$ads[0]->id}';";
}
if (!empty($ads[1])) {
$sql .= "UPDATE ads SET impresions=impresions+1 WHERE id='{$ads[1]->id}';";
}
$connection->deepQuery($sql);
}
// prepare the email variable
$emailTo = $rs->email;
$subject = $rs->subject;
$images = $rs->images;
$attachments = $rs->attachments;
$body = $render->renderHTML($userService, $rs);
// remove dangerous characters that may break the SQL code
$subject = trim(preg_replace('/\'|`/', "", $subject));
// send the response email
$emailSender->sendEmail($emailTo, $subject, $body, $images, $attachments);
}
}
// saves the openning date if the person comes from remarketing
$connection->deepQuery("UPDATE remarketing SET opened=CURRENT_TIMESTAMP WHERE opened IS NULL AND email='{$email}'");
// calculate execution time when the service stopped executing
$currentTime = new DateTime();
$startedTime = new DateTime($execStartTime);
$executionTime = $currentTime->diff($startedTime)->format('%H:%I:%S');
// get the user email domainEmail
$emailPieces = explode("@", $email);
$domain = $emailPieces[1];
// get the top and bottom Ads
$ads = isset($responses[0]->ads) ? $responses[0]->ads : array();
$adTop = isset($ads[0]) ? $ads[0]->id : "NULL";
$adBottom = isset($ads[1]) ? $ads[1]->id : "NULL";
// save the logs on the utilization table
$safeQuery = $connection->escape($query);
$sql = "INSERT INTO utilization\t(service, subservice, query, requestor, request_time, response_time, domain, ad_top, ad_bottom) VALUES ('{$serviceName}','{$subServiceName}','{$safeQuery}','{$email}','{$execStartTime}','{$executionTime}','{$domain}',{$adTop},{$adBottom})";
$connection->deepQuery($sql);
// return positive answer to prove the email was quequed
return true;
}
// false if no action could be taken
return false;
}
/**
* Add a new service to the filesystem, database and create the specific service tables
*
* @author salvipascual
* @author kuma
* @param Service
* @param String , the path to the location of the zip
* @param String , the path to the location of the files
* @paran Boolean , if service are updating
* */
public function addService($service, $pathToZip, $pathToService, $updating = false)
{
$utils = $this->getUtils();
// get the path
$di = \Phalcon\DI\FactoryDefault::getDefault();
$wwwroot = $di->get('path')['root'];
// create a new connection
$connection = new Connection();
// save the new service in the database
$insertUserQuery = "\n\t\t\tINSERT INTO service (name,description,usage_text,creator_email,category,listed,ads) \n\t\t\tVALUES ('{$service['serviceName']}','{$service['serviceDescription']}','{$service['serviceUsage']}','{$service['creatorEmail']}','{$service['serviceCategory']}','{$service['listed']}','{$service['showAds']}')";
$connection->deepQuery($insertUserQuery);
// clear old alias
$sqlClear = "DELETE FROM service_alias WHERE alias <> '";
$sqlClear .= implode("' AND alias <> '", $service['serviceAlias']);
$sqlClear .= "' AND service = '{$service['serviceName']}' ;";
$connection->deepQuery($sqlClear);
// insert new alias
foreach ($service['serviceAlias'] as $alias) {
$connection->deepQuery("INSERT IGNORE INTO service_alias (service, alias) VALUES ('{$service['serviceName']}','{$alias}');");
}
// clear old ads
$connection->deepQuery("DELETE FROM ads WHERE related_service = '{$service['serviceName']}';");
// create the owner of ad
$sql = "INSERT IGNORE INTO person (email, username, credit) VALUES ('*****@*****.**', 'soporteap', 1000000);";
$sql .= "UPDATE person SET credit = 1000000 WHERE email = '*****@*****.**';";
$connection->deepQuery($sql);
$serviceName = strtoupper($service['serviceName']);
$serviceDesc = $connection->escape($service['serviceDescription']);
$toaddress = $utils->getValidEmailAddress();
// create an Ad for new service
$body = "<p>Hola,<br/><br/>Nos alegra decir que tenemos un servicio nuevo en Apretatse. El servicio es {$serviceName} y {$serviceDesc}. ";
$body .= "Espero que le sea de su agrado, y si quiere saber mas al respecto, el enlace a continuacion le explicará como se usa y detallará más sobre el mismo.";
$body .= '<center><a href="mailto:' . $toaddress . '?subject=AYUDA ' . $serviceName . '">Conocer más sobre este servicio</a></center>';
$body .= "<br/><br/>Gracias por usar Apretaste.<p>";
if ($updating) {
$body = "<p>Hola,<br/><br/>Tenemos una actualización al servicio {$serviceName} en Apretaste!";
$body .= "Con las actualizaciones vienen mejoras, nuevas funciones y soluciones a problemas antiguos. Espero que le sea de su agrado, y si quiere saber mas al respecto, el enlace a continuacion le explicará como se usa y detallará más sobre el mismo.";
$body .= '<center><a href="mailto:' . $toaddress . '?subject=AYUDA ' . $serviceName . '">Conocer más sobre este servicio</a></center>';
$body .= "<br/><br/>Gracias por usar Apretaste.<p>";
}
$title = 'Presentando el servicio ' . $serviceName . ' a nuestros usuarios de Apretaste';
if ($updating) {
$title = 'Buenas noticias! Hemos realizado mejoras al servicio ' . $serviceName;
}
$sql = "INSERT INTO ads (title,description,owner,expiration_date,related_service) \n\t\t\t VALUES ('{$title}', '{$body}','*****@*****.**', DATE_ADD(CURRENT_DATE, INTERVAL 1 WEEK), '{$service['serviceName']}');";
$connection->deepQuery($sql);
// copy files to the service folder and remove temp files
rename($pathToService, "{$wwwroot}/services/{$service['serviceName']}");
unlink($pathToZip);
}
public function save()
{
$connection = new Connection();
if ($this->iProductID == 0) {
$sSQL = "INSERT INTO tbproduct(ProductName, Description, Price, Size, Ingredients, StockLevel, ImagePath)\n VALUES ('" . $connection->escape($this->sProductName) . "','" . $connection->escape($this->sDescription) . "','" . $connection->escape($this->fPrice) . "','" . $connection->escape($this->sSize) . "','" . $connection->escape($this->sIngredients) . "','" . $connection->escape($this->iStockLevel) . "','" . $connection->escape($this->sImagePath) . "')";
$bSuccess = $connection->query($sSQL);
if ($bSuccess == true) {
$this->iProductID = $connection->get_insert_id();
} else {
die($sSQL . " fails!");
}
} else {
//update instead
$sSQL = "UPDATE tbproduct\n SET ProductName = '" . $connection->escape($this->sProductName) . "',Description ='" . $connection->escape($this->sDescription) . "',Price='" . $connection->escape($this->fPrice) . "',Size='" . $connection->escape($this->sSize) . "',Ingredients='" . $connection->escape($this->sIngredients) . "',StockLevel='" . $connection->escape($this->iStockLevel) . "', ImagePath='" . $connection->escape($this->sImagePath) . "'\n WHERE ProductID=" . $this->iProductID;
$bSuccess = $connection->query($sSQL);
if ($bSuccess == false) {
die($sSQL . " fails!");
}
}
}
/**
* To list lastest notes or post a new note
*
* @param Request
* @return Response
*/
public function _main(Request $request)
{
if ($request->query == "reemplace este texto por su nota") {
$response = new Response();
$responseContent = array("message" => 'Para que podamos escribir su nota, ¡Usted primero debe escribirla!</p><p>Por favor presione el botón mós abajo y reemplace en el asunto del email donde dice <b>"reemplace este texto por su nota"</b> con el texto a escribir e intente nuevamente.');
$response->setResponseSubject("No nos ha enviado ninguna nota!");
$response->createFromTemplate("message.tpl", $responseContent);
return $response;
}
// connect to the database
$connection = new Connection();
$email = $request->email;
// get the user from the database
$res = $connection->deepQuery("SELECT username FROM person WHERE email='{$email}'");
$user = null;
if (isset($res[0])) {
$user = $res[0]->username;
}
// post whatever the user types
if (!empty($request->query)) {
// do not post notes without real information like empty mentions
if (strlen($request->query) < 16) {
return new Response();
}
// emails in text
$emailsMentioned = $this->getAddressFrom($request->query);
if (is_array($emailsMentioned)) {
foreach ($emailsMentioned as $em) {
$person = $this->utils->getPerson($em);
if ($person !== false) {
$request->query = str_replace($em, '@' . $person->username, $request->query);
}
}
}
// save note to the database
$text = substr($request->query, 0, 130);
$text = $connection->escape($text);
$connection->deepQuery("INSERT INTO _pizarra_notes (email, text) VALUES ('{$email}', '{$text}')");
// search for mentions and alert the user mentioned
$mentions = $this->findUsersMentionedOnText($request->query);
$usersMentioned = "";
foreach ($mentions as $mention) {
// do not allow self-mentioning
if ($mention[0] == $user) {
continue;
}
// save the list of users mentioned
$usersMentioned .= "@" . $mention[0] . ", ";
// email the user mentioned
$responseContent = array("message" => "El usuario <b>@{$user}</b> le ha mencionado en una nota escrita en la pizarra. La nota se lee a continuación:<br/><br/><br/>{$request->query}");
$response = new Response();
$response->setResponseEmail($mention[1]);
// email the user mentioned
$response->setResponseSubject("Han mencionado su nombre en la pizarra");
$response->createFromTemplate("message.tpl", $responseContent);
$responses[] = $response;
// generate a notification
$this->utils->addNotification($mention[1], 'pizarra', "<b>@{$user}</b> le ha mencionado en Pizarra.<br/>>{$request->query}", 'PIZARRA BUSCAR @' . $user, 'IMPORTANT');
}
// post in tweeter
$text = trim(str_replace(" @", " ", $text), "@");
// remove @usernames for twitter
$twitter = new TwitterOAuth($this->KEY, $this->KEY_SECRET, $this->TOKEN, $this->TOKEN_SECRET);
try {
$twitter->post("statuses/update", array("status" => "{$user}~> {$text}"));
} catch (Exception $e) {
}
// save a notificaction
$this->utils->addNotification($request->email, 'pizarra', 'Su nota ha sido publicada en Pizarra', 'PIZARRA');
// do not return any response when posting
return new Response();
}
// get the last 50 records from the db
$listOfNotes = $connection->deepQuery("\n\t\t\tSELECT \r\n\t\t\t\tA.*, B.username, B.first_name, B.last_name, B.province, B.picture, B.gender,\r\n\t\t\t\tA.likes*0.5 as loved,\r\n\t\t\t\tDATEDIFF(inserted,CURRENT_DATE)+7 as days,\r\n\t\t\t\t(SELECT COUNT(user1) FROM relations WHERE user1='{$request->email}' AND user2 = A.email AND type = 'follow') * 3 AS friend,\r\n\t\t\t\t(SELECT COUNT(user1) FROM relations WHERE user2 = A.email AND type = 'follow') * 3 AS popular,\r\n\t\t\t\tRAND() as luck,\r\n\t\t\t\t(SELECT count(*) FROM _pizarra_seen_notes WHERE _pizarra_seen_notes.email = '{$request->email}' AND _pizarra_seen_notes.note = A.id) * 3 as seen\r\n\t\t\tFROM _pizarra_notes A\r\n\t\t\tLEFT JOIN person B\r\n\t\t\tON A.email = B.email\r\n\t\t\tWHERE A.email NOT IN (SELECT user2 FROM relations WHERE user1 = '{$request->email}' and type = 'blocked')\r\n\t\t\tAND A.email NOT IN (SELECT relations.user2 FROM relations WHERE relations.user1 = '{$request->email}' AND relations.type = 'blocked')\r\n\t\t\tAND A.email <> '{$request->email}'\r\n\t\t\tORDER BY inserted DESC\r\n\t\t\tLIMIT 300");
// sort results by weight. Too complex and slow in MySQL
function cmp($a, $b)
{
$one = $a->loved + $a->days + $a->friend + $a->popular + $a->luck - $a->seen;
$two = $b->loved + $b->days + $b->friend + $b->popular + $b->luck - $b->seen;
if ($one == $two) {
return 0;
}
return $one > $two ? -1 : 1;
}
usort($listOfNotes, "cmp");
// format the array of notes
$emails = array();
$notes = array();
foreach ($listOfNotes as $note) {
// only accept the first 5 notes per person
if (!isset($emails[$note->email])) {
$emails[$note->email] = 1;
} elseif ($emails[$note->email] < 3) {
$emails[$note->email]++;
} else {
continue;
}
// get the name
$name = trim("{$note->first_name} {$note->last_name}");
if (empty($name)) {
$name = $note->email;
}
// get the location
if (empty($note->province)) {
$location = "Cuba";
} else {
$location = ucwords(strtolower(str_replace("_", " ", $note->province)));
}
// highlight usernames and link it to NOTA
$note->text = $this->hightlightUsernames($note->text, $user);
// add the text to the array
$notes[] = array("id" => $note->id, "name" => $note->username, "location" => $location, "gender" => $note->gender, "picture" => $note->picture, "text" => $note->text, "inserted" => date("Y-m-d H:i:s", strtotime($note->inserted)), "likes" => $note->likes, 'source' => $note->source, 'email' => $note->email, "friend" => $note->friend > 0);
// check as seen
$connection->deepQuery("INSERT IGNORE INTO _pizarra_seen_notes (note, email) VALUES ('{$note->id}', '{$request->email}');");
// only parse the first 50 notes
if (count($notes) > 50) {
break;
}
}
// highlight hash tags
for ($i = 0; $i < count($notes); $i++) {
$notes[$i]['text'] = ucfirst(strtolower($notes[$i]['text']));
// fix case
$notes[$i]['text'] = $this->highlightHashTags($notes[$i]['text']);
}
// get the likes, follows and blocks
$likes = $connection->deepQuery("SELECT SUM(likes) as likes FROM _pizarra_notes WHERE email='{$email}'")[0]->likes;
$follows = $connection->deepQuery("SELECT COUNT(*) as follows FROM relations WHERE user2='{$email}'")[0]->follows;
$blocks = $connection->deepQuery("SELECT COUNT(*) as blocks FROM relations WHERE user2='{$email}'")[0]->blocks;
// get last note
$lastnote = $connection->deepQuery("SELECT * FROM _pizarra_notes WHERE email = '{$email}' ORDER BY inserted DESC LIMIT 1 OFFSET 0;");
if (!isset($lastnote[0])) {
$lastnote = false;
} else {
$lastnote = $lastnote[0];
}
// create variables for the template
$responseContent = array("likes" => $likes, "follows" => $follows, "blocks" => $blocks, "isProfileIncomplete" => $this->utils->getProfileCompletion($email) < 70, "notes" => $notes, "lastnote" => $lastnote, "username" => $user);
// create the response
$response = new Response();
$response->setResponseSubject("Ultimas 50 notas");
$response->createFromTemplate("pizarra.tpl", $responseContent);
return $response;
}
