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; }