/**
* 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());
}
