/** * Return the {@link SQLQuery} that provides your report data. */ function sourceQuery($params) { $sqlQuery = new SQLQuery(); $sqlQuery->setFrom('CalendarEvent'); $sqlQuery->selectField('Date'); $sqlQuery->selectField('CalendarEvent.Title', 'Event'); $sqlQuery->selectField('StartTime'); $sqlQuery->selectField('EndTime'); $sqlQuery->addInnerJoin('CalendarEventDate', '"CalendarEventDate"."CalendarEventID" = "CalendarEvent"."ID"'); if (isset($params['DateFrom'])) { $fromDate = new SS_DateTime('FromDate'); $fromDate->setValue($params['DateFrom']); $sqlQuery->addWhere(array('Date >= ?' => $fromDate->Format("Y-m-d"))); } if (isset($params['DateTo'])) { $toDate = new SS_DateTime('ToDate'); $toDate->setValue($params['DateTo']); $sqlQuery->addWhere(array('Date <= ?' => $toDate->Format("Y-m-d"))); } if (isset($params['PrivateBookings'])) { if ($params['PrivateBookings'] == 'Private') { $sqlQuery->addWhere('Private = 1'); } elseif ($params['PrivateBookings'] == 'Public') { $sqlQuery->addWhere('Private = 0'); } } $sqlQuery->addOrderBy('Date'); $sqlQuery->addOrderBy('Event'); $sqlQuery->addOrderBy('StartTime'); $sqlQuery->addOrderBy('EndTime'); return $sqlQuery; }
public function ChartData() { $chartData = array(); $list = ArrayList::create(array()); $sqlQuery = new SQLQuery(); $sqlQuery->setFrom('Addon'); $sqlQuery->setSelect('Created'); $sqlQuery->selectField('COUNT(*)', 'CountInOneDay'); $sqlQuery->addWhere('"Created" >= DATE_SUB(NOW(), INTERVAL 30 DAY)'); $sqlQuery->addGroupBy('DATE(Created)'); $result = $sqlQuery->execute(); if (count($result)) { foreach ($result as $row) { $date = date('j M Y', strtotime($row['Created'])); if (!isset($chartData[$date])) { $chartData[$date] = $row['CountInOneDay']; } } } if (count($chartData)) { foreach ($chartData as $x => $y) { $list->push(ArrayData::create(array('XValue' => $x, 'YValue' => $y))); } } return $list; }
/** * getGateways * Create Gateways List - To do this we are going to fetch all * gateways in the Gateway DataObject and display them as * valid options where: * * a) The gateways' Enabled field is set to 1 * b) The gateways' checkCriteriaMet() method returns true. * * @param Object $order Order to use. * @param Boolean $admin If true, replace Gateway_ with Order_Payment_ for use in the CMS. * @return Array */ public function getGateways($order, $admin = null) { if ($order) { //Variable to hold available gateways $gateways = array(); //Fetch all Enabled Couriers $query = new SQLQuery(); $query->setFrom('Gateway')->addWhere("(`Enabled`='1')"); //Result of query $result = $query->execute(); //Iterate over each courier... foreach ($result as $row) { //Class of the Gateway $ClassName = $row["ClassName"]; //If the criteria is met for this gateway, add it to the gateways array. if ($ClassName::check_criteria_met($order->ID)) { if ($admin) { $ClassName = str_replace("Gateway_", "Order_Payment_", $ClassName); } //Set the ClassName as the key and the Friendly Name as the value. $gateways[$ClassName] = $row["Title"]; } } return $gateways; } }
/** * Get Class Name for Summary Fields * * @return String Tax class name */ public function getTaxClassName() { if ($this->TaxClass) { $sqlQuery = new SQLQuery("Title"); $sqlQuery->setFrom('TaxClasses')->addWhere('ID=' . $this->TaxClass . ''); return $sqlQuery->execute()->value(); } }
public function DDLValues() { $selected_values = array(); $owner = $_REQUEST["SurveyQuestionTemplateID"]; if (isset($owner)) { $sqlQuery = new SQLQuery(); $sqlQuery->setSelect("ValueID"); $sqlQuery->setFrom("SurveyQuestionTemplate_DependsOn"); $sqlQuery->setWhere("SurveyQuestionTemplateID = {$owner} AND ChildID = {$this->ID}"); $selected_values = $sqlQuery->execute()->keyedColumn(); } return new MultiDropdownField("Values_{$this->ID}", "Values_{$this->ID}", $this->Rows()->map("ID", "Value"), $selected_values); }
public function Articles() { $filterID = $this->ExcludeTag()->ID; $sqlQuery = new SQLQuery(); $sqlQuery->setFrom('HailTag_Articles'); $sqlQuery->setSelect('HailTag_Articles.HailArticleID'); //$sqlQuery->addLeftJoin('HailTag_Articles','HailTag_Articles.HailArticleID = HailArticleID'); //$sqlQuery->addWhere('HailTag_Articles.HailTagID = ' . intval($this->Tag()->ID)); $sqlQuery->addWhere('HailTagID = ' . intval($filterID)); $map = $sqlQuery->execute()->map(); $articles_ids = array_keys($map); $List = parent::Articles()->exclude('ID', $articles_ids); return $List; }
/** * Count the total number of items in this order and multiple * them by the item flat rate as defined in this couriers * settings. */ public static function calculate_shipping_total($order_no) { //This method will be called statically and is not the Controller ($this) so store the database fields in $conf. $conf = DataObject::get_one(get_class()); //Count the total number of items in this order. $product = new SQLQuery(); $product->setFrom("Order_Items")->addWhere("(`OrderID`=" . $order_no . ")"); $result = $product->execute(); $total_items = 0; foreach ($result as $row) { $total_items = $total_items + $row["Quantity"]; } //Return the shipping cost. return $total_items * $conf->FlatRate; }
private function getCSVColumns($flexi) { $columns = array('SubmittedBy' => 'Submitted By', 'IPAddress' => 'IP Address', 'Created' => 'Created'); $sql = new SQLQuery(); $sql->setFrom('FlexiFormSubmissionValue'); $sql->setSelect('"FlexiFormSubmissionValue"."Name"'); $sql->addLeftJoin('FlexiFormSubmission', '"FlexiFormSubmissionValue"."SubmissionID" = "FlexiFormSubmission"."ID"'); $sql->addWhere('"FlexiFormSubmission"."FlexiFormID" = ' . $flexi->ID); $sql->addWhere('"FlexiFormSubmission"."FlexiFormClass" = \'' . $flexi->class . '\''); $sql->setDistinct(true); foreach ($sql->execute() as $row) { $columns['Values.' . $row['Name']] = $row['Name']; } return $columns; }
/** * If the total order spend exceeds the defined minimum spend value, make courier available. */ public static function check_criteria_met($order_no) { //This method will be called statically and is not the Controller ($this) so store the database fields in $conf. $conf = DataObject::get_one(get_class()); //Fetch the total price for all products in the given order $product = new SQLQuery(); $product->setFrom("Order_Items")->addWhere("(`OrderID`=" . $order_no . ")"); $result = $product->execute(); $total_spend = 0; foreach ($result as $row) { $total_spend = $total_spend + $row["Price"] * $row["Quantity"]; } //If the total spend exceeds the defined minimum spend value, make courier available. return $total_spend >= $conf->MinSpend ? true : false; }
protected function getRemoteObjectsQuery() { // Do something really lazy here; Join on all tables to do the mapping really sneakily $query = new SQLQuery('"' . $this->tableName . '"."ID"'); $query->setFrom('"' . $this->tableName . '"'); // relations are add-only, so just get unimported relations $query->setWhere('"' . $this->tableName . '"."_ImportedID" = 0'); foreach ($this->fields as $field => $class) { // Join table $query->addInnerJoin($class, "\"{$class}\".\"ID\" = \"{$this->tableName}\".\"{$field}\""); // Remove unmapped related tables $query->addWhere("\"{$class}\".\"_ImportedID\" > 0"); // Substitute imported ID from related class for that ID $query->selectField("\"{$class}\".\"_ImportedID\"", $field); } return $query; }
private function checkBlogEntryPermissions() { $authorsId = array(); $sqlQuery = new SQLQuery(); $sqlQuery->setFrom('SiteTree_versions'); $sqlQuery->selectField('AuthorID'); $sqlQuery->addWhere('RecordID = ' . $this->ID); $sqlQuery->setOrderBy('ID DESC'); $rawSQL = $sqlQuery->sql(); $result = $sqlQuery->execute(); foreach ($result as $row) { $authorsId[] = $row['AuthorID']; } $sqlQuery->setDelete(true); if (in_array(Member::currentUser()->ID, $authorsId) || $this->parent->OwnerID == Member::currentUser()->ID || Permission::check('ADMIN')) { return true; } else { return false; } }
/** * COPIED FROM SITETREE * * Generate a URL segment based on the title provided. * * @param string $title Product title * @return string Generated url segment */ public function generateURLSegment($title) { $filter = URLSegmentFilter::create(); $t = $filter->filter($title); // Fallback to generic page name if path is empty (= no valid, convertable characters) if (!$t || $t == '-' || $t == '-1') { $t = "page-{$this->ID}"; } // Hook for extensions $this->extend('updateURLSegment', $t, $title); // Check to see if URLSegment exists already, if it does, append -* where * is COUNT()+1 $seg = new SQLQuery('COUNT(*)'); $seg->setFrom(get_class($this))->addWhere("`URLSegment` LIKE '%{$t}%'"); $count = $seg->execute()->value(); if ($count > 0) { $count++; return $t . "-" . $count; } else { return $t; } }
/** * Set up the simplest initial query */ public function initialiseQuery() { // Get the tables to join to. // Don't get any subclass tables - let lazy loading do that. $tableClasses = ClassInfo::ancestry($this->dataClass, true); // Error checking if (!$tableClasses) { if (!SS_ClassLoader::instance()->hasManifest()) { user_error("DataObjects have been requested before the manifest is loaded. Please ensure you are not" . " querying the database in _config.php.", E_USER_ERROR); } else { user_error("DataList::create Can't find data classes (classes linked to tables) for" . " {$this->dataClass}. Please ensure you run dev/build after creating a new DataObject.", E_USER_ERROR); } } $baseClass = array_shift($tableClasses); // Build our intial query $this->query = new SQLQuery(array()); $this->query->setDistinct(true); if ($sort = singleton($this->dataClass)->stat('default_sort')) { $this->sort($sort); } $this->query->setFrom("\"{$baseClass}\""); $obj = Injector::inst()->get($baseClass); $obj->extend('augmentDataQueryCreation', $this->query, $this); }
/** * calculatePaymentTotal * Calculate Payment Total * * @return float */ public function calculatePaymentTotal() { //Sum the total payments against this order. $query = new SQLQuery("SUM(Amount)"); $query->setFrom("`Order_Payment`")->addWhere("`OrderId`='" . $this->ID . "' AND `Status`='Completed'"); return StoreCurrency::convertToCurrency($query->execute()->value()); }
/** * Remove all items from this many-many join. To remove a subset of items, * filter it first. * * @return void */ public function removeAll() { $base = ClassInfo::baseDataClass($this->dataClass()); // Remove the join to the join table to avoid MySQL row locking issues. $query = $this->dataQuery(); $query->removeFilterOn($query->getQueryParam('Foreign.Filter')); $query = $query->query(); $query->setSelect("\"{$base}\".\"ID\""); $from = $query->getFrom(); unset($from[$this->joinTable]); $query->setFrom($from); $query->setDistinct(false); // ensure any default sorting is removed, ORDER BY can break DELETE clauses $query->setOrderBy(null, null); // Use a sub-query as SQLite does not support setting delete targets in // joined queries. $delete = new SQLQuery(); $delete->setDelete(true); $delete->setFrom("\"{$this->joinTable}\""); $delete->addWhere($this->foreignIDFilter()); $delete->addWhere("\"{$this->joinTable}\".\"{$this->localKey}\" IN ({$query->sql()})"); $delete->execute(); }
public function getDDLLogoSize() { $size = null; $pageId = Convert::raw2sql($_REQUEST["PageId"]); if (isset($pageId)) { $sqlQuery = new SQLQuery(); $sqlQuery->setSelect("LogoSize"); $sqlQuery->setFrom("SummitSponsorPage_Companies"); $sqlQuery->setWhere("CompanyID={$this->ID} AND SummitSponsorPageID={$pageId}"); $size = $sqlQuery->execute()->value(); if (is_null($size)) { $size = 'None'; } } $sizes = array('Small' => 'Small', 'Medium' => 'Medium', 'Large' => 'Large', 'Big' => 'Big', 'None' => '--NONE--'); return new DropdownField("LogoSize_{$this->ID}", "LogoSize_{$this->ID}", $sizes, $size); }
/** * Test passing in a LIMIT with OFFSET clause string. */ public function testLimitSetFromClauseString() { $query = new SQLQuery(); $query->setSelect('*'); $query->setFrom('"SQLQueryTest_DO"'); $query->setLimit('20 OFFSET 10'); $limit = $query->getLimit(); $this->assertEquals(20, $limit['limit']); $this->assertEquals(10, $limit['start']); }
/** * Return the number of rows in this query if the limit were removed. Useful in paged data sets. * @return int */ public function unlimitedRowCount($column = null) { // we can't clear the select if we're relying on its output by a HAVING clause if (count($this->having)) { $records = $this->execute(); return $records->numRecords(); } $clone = clone $this; $clone->limit = null; $clone->orderby = null; // Choose a default column if ($column == null) { if ($this->groupby) { $countQuery = new SQLQuery(); $countQuery->setSelect("count(*)"); $countQuery->setFrom('(' . $clone->sql() . ') all_distinct'); return $countQuery->execute()->value(); } else { $clone->setSelect(array("count(*)")); } } else { $clone->setSelect(array("count({$column})")); } $clone->setGroupBy(array()); return $clone->execute()->value(); }
public function getVersionedConnection($recordID, $userID, $definitionID, $wasPublished = 0) { // Turn this into an array and run through implode() $filter = "\"AuthorID\" = '" . $userID . "' AND \"RecordID\" = '" . $recordID . "' AND \"WorkflowDefinitionID\" = '" . $definitionID . "' AND WasPublished = '" . $wasPublished . "'"; $query = new SQLQuery(); $query->setFrom('SiteTree_versions')->setSelect('COUNT(ID)')->setWhere($filter); $query->firstRow(); $hasAuthored = $query->execute(); if ($hasAuthored) { return true; } return false; }
/** * Returns the number of tickets available for an event time. * * @param RegistrableDateTime $time * @param int $excludeId A registration ID to exclude from calculations. * @return array */ public function getAvailableForDateTime(RegistrableDateTime $time, $excludeId = null) { if ($this->StartType == 'Date') { $start = strtotime($this->StartDate); } else { $start = $time->getStartDateTime()->getTimestamp(); $start = sfTime::subtract($start, $this->StartDays, sfTime::DAY); $start = sfTime::subtract($start, $this->StartHours, sfTime::HOUR); $start = sfTime::subtract($start, $this->StartMins, sfTime::MINUTE); } if ($start >= time()) { return array('available' => false, 'reason' => 'Tickets are not yet available.', 'available_at' => $start); } if ($this->EndType == 'Date') { $end = strtotime($this->EndDate); } else { $end = $time->getStartDateTime()->getTimestamp(); $end = sfTime::subtract($end, $this->EndDays, sfTime::DAY); $end = sfTime::subtract($end, $this->EndHours, sfTime::HOUR); $end = sfTime::subtract($end, $this->EndMins, sfTime::MINUTE); } if (time() >= $end) { return array('available' => false, 'reason' => 'Tickets are no longer available.'); } if (!($quantity = $this->Available)) { return array('available' => true); } $booked = new SQLQuery(); $booked->setSelect('SUM("Quantity")'); $booked->setFrom('"EventRegistration_Tickets"'); $booked->addLeftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"'); if ($excludeId) { $booked->addWhere('"EventRegistration"."ID"', '<>', $excludeId); } $booked->addWhere('"Status"', '<>', 'Canceled'); $booked->addWhere('"EventTicketID"', $this->ID); $booked->addWhere('"EventRegistration"."TimeID"', $time->ID); $booked = $booked->execute()->value(); if ($booked < $quantity) { return array('available' => $quantity - $booked); } else { return array('available' => false, 'reason' => 'All tickets have been booked.'); } }
public function testWhereAny() { $query = new SQLQuery(); $query->setFrom('MyTable'); $query->whereAny(array("Monkey = 'Chimp'", "Color = 'Brown'")); $this->assertEquals("SELECT * FROM MyTable WHERE (Monkey = 'Chimp' OR Color = 'Brown')", $query->sql()); }
/** * Get current stock level * * @param Int $product_id The ID of the product to add to the basket. * @return Int */ public function getStockLevel($product_id) { $qty = new SQLQuery("StockLevel"); $qty->setFrom("Product")->addWhere("`ID`='" . $product_id . "'"); return $qty->execute()->value(); }
/** * handleGatewayResponse * This action should be used when a gateway submits a POST/GET response * for which we need to action. In this case, the PayPal IPN. We shall * return void as nothing is returned from this method. It is not public * facing and is present to handle system to system communications over * HTTP communications only. If the gateway doesn't support POST/GET type * responses, implement the back office order updating within the * newPaymentSuccess() method instead. * * ATTRIBUTION * Snippets of IPN code were used from PayPal's GitHub samples on 15-10-2015. * https://github.com/paypal/ipn-code-samples/blob/master/paypal_ipn.php * @author PayPal * * @param SS_HTTPRequest $request The GET/POST variables and URL parameters. * @return Void */ public function handleGatewayResponse($request) { /** * Only proceed if we have postVars set */ if ($request->postVars()) { $gateway = DataObject::get_one("Gateway_PayPal"); $debug = $gateway->Debug; /** * STEP ONE * Prepend cmd=_notify-validate to the POST request from PayPal. * Reading posted data direction from $request->postVars() may * cause serialization isusues with array data. We therefore * will read directly from the input stream instead. */ $raw_post_data = file_get_contents('php://input'); $raw_post_array = explode('&', $raw_post_data); $myPost = array(); foreach ($raw_post_array as $keyval) { $keyval = explode('=', $keyval); if (count($keyval) == 2) { $myPost[$keyval[0]] = urldecode($keyval[1]); } } $req = 'cmd=_notify-validate'; if (function_exists('get_magic_quotes_gpc')) { $get_magic_quotes_exists = true; } foreach ($myPost as $key => $value) { if ($get_magic_quotes_exists == true && get_magic_quotes_gpc() == 1) { $value = urlencode(stripslashes($value)); } else { $value = urlencode($value); } $req .= "&{$key}={$value}"; } /** * STEP TWO * Which PayPal URL are we dealing with? */ if (DataObject::get_one("Gateway_PayPal")->Sandbox) { $paypal_url = "https://www.sandbox.paypal.com/cgi-bin/webscr"; } else { $paypal_url = "https://www.paypal.com/cgi-bin/webscr"; } /** * STEP THREE * Initiate curl IPN callback to post IPN data back to PayPal * to validate the IPN data is genuine. Without this step anyone * can fake IPN data and mess with your order system. */ $ch = curl_init($paypal_url); if ($ch == FALSE) { return FALSE; } /* Set curl Options */ curl_setopt($ch, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt($ch, CURLOPT_POSTFIELDS, $req); curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 1); curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2); curl_setopt($ch, CURLOPT_FORBID_REUSE, 1); /* Set TCP timeout to 30 seconds */ curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30); curl_setopt($ch, CURLOPT_HTTPHEADER, array('Connection: Close')); /* Execute Curl and Store Response in $res */ $res = curl_exec($ch); /* Are there curl errors? If yes, log them if Debug is enabled. */ if (curl_errno($ch) != 0) { if ($debug == 1) { $this->newLogEntry("Can't connect to PayPal to validate IPN message: " . curl_error($ch)); } curl_close($ch); exit; /* No errors */ } else { /* If Debug is enabled, save to the log. */ if ($debug == 1) { $this->newLogEntry("HTTP request of validation request" . curl_getinfo($ch, CURLINFO_HEADER_OUT) . " for IPN payload: {$req}"); $this->newLogEntry("HTTP response of validation request: {$res}"); } curl_close($ch); } /** * STEP FOUR * Inspect IPN validation result and act accordingly. * 1 - Split response headers and payload, a better way for strcmp. * 2 - Do the actions, based on response. */ $tokens = explode("\r\n\r\n", trim($res)); $res = trim(end($tokens)); if (strcmp($res, "VERIFIED") == 0) { /** * DEBUG * If debug is enabled, log the details * of this IPN response. */ if ($debug) { $this->newLogEntry("Verified IPN: {$req} "); } /** * ERROR CHECK 1 * txn_type must be of type 'web_accept'. (Buy Now Button) */ if (!$request->postVar("txn_type") == "web_accept") { if ($debug == 1) { $this->newLogEntry("ERROR: (txn_id: " . $request->postVar("txn_id") . ") txn_type is not of type 'web_accept'."); } return Store_Controller::create()->httpError(400); exit; } /** * ERROR CHECK 2 * We must be the intended recipient for the transaction. */ if ($gateway->EmailAddress != $request->postVar("receiver_email")) { if ($debug == 1) { $this->newLogEntry("ERROR: (txn_id: " . $request->postVar("txn_id") . ") Intended recipient " . "(" . $request->postVar("receiver_email") . ") does not " . "match that set in the gateway settings."); } return Store_Controller::create()->httpError(400); exit; } /** * ERROR CHECK 3 * An order related to this payment must exist. */ $order = new SQLQuery("COUNT(*)"); $order->setFrom("`order`")->addWhere("(`id`='" . $request->postVar("custom") . "')"); if ($order->execute()->value() < 1) { if ($debug == 1) { $this->newLogEntry("ERROR: (txn_id: " . $request->postVar("txn_id") . ") The order number defined in 'custom' " . "(" . $request->postVar("custom") . ") does not exist in the system."); } return Store_Controller::create()->httpError(400); exit; } /** * ERROR CHECK 4 * This IPN message can not be a duplicate. */ $dup = new SQLQuery("COUNT(*)"); $dup->setFrom("`Order_Payment_PayPal`"); $dup->addWhere("(`txn_id`='" . $request->postVar("txn_id") . "') AND (`payment_status`='" . $request->postVar("payment_status") . "')"); $dup_count = $dup->execute()->value(); if ($dup_count > 0) { if ($debug == 1) { $this->newLogEntry("ERROR: (txn_id: " . $request->postVar("txn_id") . ") The IPN message received is a duplicate of one " . "previously received."); } return Store_Controller::create()->httpError(400); exit; } /** * ERROR CHECK 5 * The mc_gross has to match the total order price. */ $order_total = DataObject::get_by_id("Order", $request->postVar("custom"))->calculateOrderTotal(); $mc_gross = $request->postVar("mc_gross"); if ($order_total != $mc_gross) { if ($debug == 1) { $this->newLogEntry("ERROR: (txn_id: " . $request->postVar("txn_id") . ") The payment amount did not match the order amount."); } return Store_Controller::create()->httpError(400); exit; } /** * ERROR CHECK 6 * If this IPN is not a duplicate, are there * any other entries for this txn_id? */ if ($dup_count < 1) { /* Count how many entries there are with the IPNs txn_id */ $record_count = new SQLQuery("COUNT(*)"); $record_count->setFrom("Order_Payment_PayPal"); $record_count->addWhere("(`txn_id`='" . $request->postVar("txn_id") . "')"); $record_count = $record_count->execute()->value(); /* The row ID for the record that was found, if one exists */ $payment_record_id = new SQLQuery("`id`"); $payment_record_id->setFrom("Order_Payment_PayPal")->addWhere("(`txn_id`='" . $request->postVar("txn_id") . "')"); $payment_record_id = $payment_record_id->execute()->value(); } /** * VERIFIED STEP ONE * * Either create a payment record or update an existing one an send the applicable emails. */ switch ($request->postVar("payment_status")) { /* Payment has cleared, order can progress. */ case "Completed": //Send email to admin notification email address Order_Emails::create()->adminNewOrderNotification($request->postVar("custom")); //Send email to the customer confirming their order, if they haven't had one already. Order_Emails::create()->customerNewOrderConfirmation($request->postVar("custom")); if ($record_count > 0) { $this->updatePaymentRecord($request, $payment_record_id, "Completed", "Processing"); } else { $this->newPaymentRecord($request, "Completed", "Processing"); } break; /* The payment is pending. See pending_reason for more information. */ /* The payment is pending. See pending_reason for more information. */ case "Pending": /** * We don't send emails for this status as 'Pending' orders are still awaiting a response from * a payment gateway and should not be dispatched. It is safe to send a confirmation email to * the customer, however. */ //Send email to the customer confirming their order is currently pending Order_Emails::create()->customerNewOrderConfirmation($request->postVar("custom"), "Pending"); if ($record_count > 0) { $this->updatePaymentRecord($request, $payment_record_id, "Pending", "Pending / Awaiting Payment"); } else { $this->newPaymentRecord($request, "Pending", "Pending / Awaiting Payment"); } break; /* You refunded the payment. */ /* You refunded the payment. */ case "Refunded": /* Notify the customer of a change to their order status */ Order_Emails::create()->customerOrderStatusUpdate($request->postVar("custom"), "Refunded"); if ($record_count > 0) { $this->updatePaymentRecord($request, $payment_record_id, "Refunded", "Refunded"); } else { $this->newPaymentRecord($request, "Refunded", "Refunded"); } break; /** * A payment was reversed due to a chargeback or other type of reversal. * The funds have been removed from your account balance and returned to the buyer. * The reason for the reversal is specified in the ReasonCode element. */ /** * A payment was reversed due to a chargeback or other type of reversal. * The funds have been removed from your account balance and returned to the buyer. * The reason for the reversal is specified in the ReasonCode element. */ case "Reversed": /* Notify the admin that an order has had an order has been reversed */ /* Notify the customer of a change to their order status */ Order_Emails::create()->customerOrderStatusUpdate($request->postVar("custom"), "Cancelled"); if ($record_count > 0) { $this->updatePaymentRecord($request, $payment_record_id, "Refunded", "Cancelled"); } else { $this->newPaymentRecord($request, "Refunded", "Cancelled"); } break; /* The reveral was cancelled */ /* The reveral was cancelled */ case "Canceled_Reversal": /* Notify an admin that an order reversal has been cancelled */ /** * We don't send customers an email update for this status as it might * cause confustion. */ /** * For canceled reversals, lets set the order to Pending as an admin will need to manually review it. * we don't want it to fall in the standard Processing queue as goods could be shipped twice. */ if ($record_count > 0) { $this->updatePaymentRecord($request, $payment_record_id, "Pending", "Pending / Awaiting Payment"); } else { $this->newPaymentRecord($request, "Pending", "Pending / Awaiting Payment"); } break; /* This authorization has been voided. */ /* This authorization has been voided. */ case "Voided": /* Notify the customer of a change to their order status */ Order_Emails::create()->customerOrderStatusUpdate($request->postVar("custom"), "Cancelled"); if ($record_count > 0) { $this->updatePaymentRecord($request, $payment_record_id, "Refunded", "Cancelled"); } else { $this->newPaymentRecord($request, "Refunded", "Cancelled"); } break; /** * The payment has failed. */ /** * The payment has failed. */ case "Failed": /* Notify the customer of a change to their order status */ Order_Emails::create()->customerOrderStatusUpdate($request->postVar("custom"), "Cancelled"); if ($record_count > 0) { $this->updatePaymentRecord($request, $payment_record_id, "Refunded", "Cancelled"); } else { $this->newPaymentRecord($request, "Refunded", "Cancelled"); } break; /* Other IPN statuses are ignored. */ /* Other IPN statuses are ignored. */ default: exit; break; } } elseif (strcmp($res, "INVALID") == 0) { $status = "INVALID"; // log for manual investigation // Add business logic here which deals with invalid IPN messages /* If Debug is enabled, log response */ if ($debug == 1) { error_log(date('[Y-m-d H:i e] ') . "Invalid IPN: {$req}" . PHP_EOL, 3, "../ipn.log"); } } } }
private function getSuggestions($req) { $request = Convert::raw2sql($req->requestVar('request')); // If the class to pick config value is not set, this will not work too well! // In that case, we'll just return an empty result. if (empty($this->config['classToPick'])) { $results = array(array('id' => '0', 'title' => 'none selected', 'full' => "select none", 'style' => 'color:red')); } else { $class = $this->config['classToPick']; $search = Config::inst()->get($class, 'searchable_fields'); $searchArray = array(); $sqlQuery = new SQLQuery(); $sqlQuery->setFrom($class); $sqlQuery->selectField('ID'); $sqlQuery->useDisjunction(); foreach ($search as $key => $value) { if (!is_array($value)) { if (is_string($key)) { $sqlQuery->addWhere("{$key} LIKE '%{$request}%'"); } else { $sqlQuery->addWhere("{$value} LIKE '%{$request}%'"); } } } $results = array(array('id' => '0', 'title' => 'none selected', 'full' => "select none", 'style' => 'color:red')); $dbResults = $sqlQuery->execute(); foreach ($dbResults as $row) { $object = DataObject::get($class)->byID($row['ID']); $results[] = array('id' => $object->ID, 'title' => $object->LinkTitle(), 'full' => $object->LinkTitle()); } } return json_encode($results); }
/** * Returns the overall number of places remaining at this event, TRUE if * there are unlimited places or FALSE if they are all taken. * * @param int $excludeId A registration ID to exclude from calculations. * @return int|bool */ public function getRemainingCapacity($excludeId = null) { if (!$this->Capacity) { return true; } $taken = new SQLQuery(); $taken->setSelect('SUM("Quantity")'); $taken->setFrom('EventRegistration_Tickets'); $taken->addLeftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"'); if ($excludeId) { $taken->addWhere('"EventRegistration"."ID" <>' . (int) $excludeId); } $taken->addWhere('"Status" <> \'Canceled\''); $taken->addWhere('"EventRegistration"."TimeID" =' . (int) $this->ID); $taken = $taken->execute()->value(); return $this->Capacity >= $taken ? $this->Capacity - $taken : false; }
public function testJoinSubSelect() { $query = new SQLQuery(); $query->setFrom('MyTable'); $query->addInnerJoin('(SELECT * FROM MyOtherTable)', 'Mot.MyTableID = MyTable.ID', 'Mot'); $query->addLeftJoin('(SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID)', 'Mlt.MyOtherTableID = Mot.ID', 'Mlt'); $query->setOrderBy('COALESCE(Mlt.MyLastTableCount, 0) DESC'); $this->assertSQLEquals('SELECT *, COALESCE(Mlt.MyLastTableCount, 0) AS "_SortColumn0" FROM MyTable ' . 'INNER JOIN (SELECT * FROM MyOtherTable) AS "Mot" ON Mot.MyTableID = MyTable.ID ' . 'LEFT JOIN (SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID) AS "Mlt" ON Mlt.MyOtherTableID = Mot.ID ' . 'ORDER BY "_SortColumn0" DESC', $query->sql($parameters)); }
public function DDLVisibility() { $values = array('Visible' => 'Visible', 'Not-Visible' => 'Not-Visible'); $selected_value = ''; if (isset($_REQUEST["SurveyQuestionTemplateID"])) { $owner = $_REQUEST["SurveyQuestionTemplateID"]; if (isset($owner)) { $sqlQuery = new SQLQuery(); $sqlQuery->setSelect("Visibility"); $sqlQuery->setFrom("SurveyQuestionTemplate_DependsOn"); $sqlQuery->setWhere("SurveyQuestionTemplateID = {$owner} AND ChildID = {$this->ID}"); $selected_value = current($sqlQuery->execute()->keyedColumn()); } } else { if (isset($_REQUEST["SurveyStepTemplateID"])) { $owner = $_REQUEST["SurveyStepTemplateID"]; if (isset($owner)) { $sqlQuery = new SQLQuery(); $sqlQuery->setSelect("Visibility"); $sqlQuery->setFrom("SurveyStepTemplate_DependsOn"); $sqlQuery->setWhere("SurveyStepTemplateID = {$owner} AND SurveyQuestionTemplateID = {$this->ID}"); $selected_value = current($sqlQuery->execute()->keyedColumn()); } } } return new DropdownField("Visibility_{$this->ID}", "Visibility_{$this->ID}", $values, $selected_value); }
/** * Retrieves Locations by lat, long, distance, and optionally a limit. */ public function getLocationSQLResultsByLatLong($lat = 37, $long = -122, $distance = 25, $limit = null) { //$data = DB::query('SELECT "ID" FROM "Marker" LIMIT 0 , '.$limit.';')->value(); //$query = 'SELECT "ID", ( 3959 * acos( cos( radians('.$lat.') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('.$long.') ) + sin( radians('.$lat.') ) * sin( radians( Latitude ) ) ) ) AS "Distance" FROM "Marker" HAVING "Distance" < '.$distance.' ORDER BY "Distance" LIMIT 0 , '.$limit.';'; $markerClass = StoreFinder::$MarkerClass; $sqlQuery = new SQLQuery(); $sqlQuery->setFrom($markerClass); $sqlQuery->selectField('*'); $sqlQuery->selectField('( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(' . $long . ') ) + sin( radians(' . $lat . ') ) * sin( radians( Latitude ) ) ) )', 'Distance'); $sqlQuery->setHaving("Distance < " . $distance); $sqlQuery->setOrderBy('Distance'); $sqlQuery->setLimit($limit); if ($markerClass != 'Marker') { $sqlQuery->addLeftJoin("Marker", 'Marker.ID = ' . $markerClass . '.ID'); } $this->extraSQL($sqlQuery); // Execute and return a Query object $result = $sqlQuery->execute(); return $result; }
function exportCompanyData() { $params = $this->owner->getRequest()->getVars(); if (!isset($params['report_name']) || empty($params['report_name']) || !count($params['report_name'])) { return $this->owner->httpError('412', 'missing required param report_name'); } if (!isset($params['extension']) || empty($params['extension'])) { return $this->owner->httpError('412', 'missing required param extension'); } $report_name = isset($params['report_name']) ? $params['report_name'] : ''; $fields = isset($params['fields']) ? $params['fields'] : array(); $ext = $params['extension']; $query = new SQLQuery(); if ($report_name) { switch ($report_name) { case 'sponsorship_type': $query->setFrom('Company'); $query->addLeftJoin('SummitSponsorPage_Companies', 'SummitSponsorPage_Companies.CompanyID = Company.ID'); $query->addLeftJoin('Summit', 'Summit.ID = SummitSponsorPage_Companies.SummitID'); $query->addWhere('Summit.Active', '1'); $fields = array_merge($fields, array('Sponsorship' => 'SummitSponsorPage_Companies.SponsorshipType', 'Summit ID' => 'Summit.ID')); $query->setSelect($fields); $query->addOrderBy('SummitSponsorPage_Companies.SponsorshipType'); $filename = "Sponsorship_Levels_" . date('Ymd') . "." . $ext; break; case 'member_level': $query->setFrom('Company'); array_push($fields, 'Company.MemberLevel'); $query->setSelect($fields); $filename = "Foundation_Levels_" . date('Ymd') . "." . $ext; break; case 'users_roles': $query->setFrom('Company'); $query->addInnerJoin('Company_Administrators', 'Company_Administrators.CompanyID = Company.ID'); $query->addLeftJoin('Member', 'Member.ID = Company_Administrators.MemberID'); $query->addLeftJoin('Group', 'Group.ID = Company_Administrators.GroupID'); array_push($fields, 'Group.Title'); $query->setSelect($fields); $query->addOrderBy('Company.Name'); $filename = "User_Roles_" . date('Ymd') . "." . $ext; break; case 'affiliates': $query->setFrom('Org'); $query->addLeftJoin('Affiliation', 'Affiliation.OrganizationID = Org.ID'); $query->addLeftJoin('Member', 'Member.ID = Affiliation.MemberID'); $fields = array_merge($fields, array('Is Current' => 'Affiliation.Current', 'Job Title' => 'Affiliation.JobTitle')); $query->setSelect($fields); $query->addOrderBy('Org.Name'); $filename = "Employees_Affiliates_" . date('Ymd') . "." . $ext; break; case 'deployments': $query->setFrom('Org'); $query->addInnerJoin('Deployment', 'Deployment.OrgID = Org.ID'); $custom_fields = array('Creation' => 'Deployment.Created', 'Edited' => 'Deployment.LastEdited', 'Label' => 'Deployment.Label', 'Is Public' => 'Deployment.IsPublic'); $fields = array_merge($fields, $custom_fields); $query->setSelect($fields); $query->selectField("CONCAT('http://openstack.org/sangria/DeploymentDetails/',Deployment.ID)", "Link"); $query->addOrderBy('Org.Name'); $filename = "Deployments_" . date('Ymd') . "." . $ext; break; case 'deployment_surveys': $query->setFrom('Org'); $query->addLeftJoin('DeploymentSurvey', 'DeploymentSurvey.OrgID = Org.ID'); $query->addLeftJoin('Member', 'DeploymentSurvey.MemberID = Member.ID'); $custom_fields = array('Creation' => 'DeploymentSurvey.Created', 'Edited' => 'DeploymentSurvey.LastEdited', 'Title' => 'DeploymentSurvey.Title', 'City' => 'DeploymentSurvey.PrimaryCity', 'State' => 'DeploymentSurvey.PrimaryState', 'Country' => 'DeploymentSurvey.PrimaryCountry', 'Org Size' => 'DeploymentSurvey.OrgSize', 'Is Group Member' => 'DeploymentSurvey.UserGroupMember', 'Group Name' => 'DeploymentSurvey.UserGroupName', 'Ok to Contact' => 'DeploymentSurvey.OkToContact'); //insert custom fields after org fields $pos = -1; foreach ($fields as $field) { $pos++; if (strpos($field, 'Org') !== false) { continue; } else { array_splice($fields, $pos, 0, $custom_fields); break; } } $query->setSelect($fields); $query->selectField("CONCAT('http://openstack.org/sangria/SurveyDetails/',DeploymentSurvey.ID)", "Link"); $filename = "Deployment_Surveys" . date('Ymd') . "." . $ext; break; case 'speakers': $query->setFrom('PresentationSpeaker'); $query->addLeftJoin('Affiliation', 'Affiliation.MemberID = PresentationSpeaker.MemberID'); $query->addLeftJoin('Org', 'Affiliation.OrganizationID = Org.ID'); $query->addLeftJoin('Summit', 'Summit.ID = PresentationSpeaker.SummitID'); $custom_fields = array('Speaker Name' => 'PresentationSpeaker.FirstName', 'Speaker Surname' => 'PresentationSpeaker.LastName', 'Summit' => 'Summit.Name'); $fields = array_merge($fields, $custom_fields); $query->setSelect($fields); $filename = "Speakers_" . date('Ymd') . "." . $ext; break; } } //die($query->sql()); $result = $query->execute(); $delimiter = $ext == 'xls' ? "\t" : ","; return CSVExporter::getInstance()->export($filename, $result, $delimiter); }
/** * ACTION /addtobasket * Add the requested item to the basket. */ public function addtobasket($data) { /* Retreive the TempBasketID (Cookie) for the current users basket. If it doesn't exist, create one */ if (Store_BasketController::get_temp_basket_id()) { $TempBasketID = Store_BasketController::get_temp_basket_id(); } else { $TempBasketID = Store_BasketController::set_temp_basket_id(); } /* Try to fetch an Order record using the TempBasketID */ $Order = DataObject::get_one("Order", "(`TempBasketID`='" . $TempBasketID . "')"); /** * If an Order record doesn't exist, create the Order record first. */ if (!$Order) { $n = new Order(); $n->TempBasketID = $TempBasketID; $n->write(); $Order = DataObject::get_one("Order", "(`TempBasketID`='" . $TempBasketID . "')"); } /** * Do any Order_Items exist for this Product in the current Order? If yes, increment Quantity. * Otherwise, add a new item. */ $count = new SQLQuery("COUNT(*)"); $count->setFrom("Order_Items")->addWhere("(`OriginalProductID`='" . $data["ProductID"] . "' AND `TempBasketID`='" . $TempBasketID . "')"); if ($count->execute()->value() > 0) { DB::Query("UPDATE Order_Items SET Quantity=Quantity + " . $data["Qty"] . " WHERE (`OriginalProductID`='" . $data["ProductID"] . "' AND `TempBasketID`='" . $TempBasketID . "')"); } else { /** * Proceed to add the selected Product to the order as an Order_Items with the same TempBasketID. */ $order_item = new Order_Items(); $order_item->OrderID = $Order->ID; $order_item->OriginalProductID = $data["ProductID"]; $order_item->Quantity = $data["Qty"]; $order_item->TempBasketID = $TempBasketID; /** * As this order is still in its 'Shopping Basket' stages we will have no customer information * to calculate tax with at this time. Set tax rate and class to zero for now. */ $order_item->TaxClassName = "To Be Calculated"; $order_item->TaxClassRate = "0.00"; /* Write to the database */ $order_item->write(); } /* Take the user to their Basket */ return $this->redirect(Store_BasketController::get_link()); }