/**
* Manipulates the SQL query
*
* @param SQLQuery &$query Query to manipulate
*
* @return void
*
* @author Sebastian Diel <*****@*****.**>
* @since 04.05.2012
*/
public function augmentSQL(SQLQuery &$query)
{
if (!$query->isJoinedTo($this->getLanguageClassName()) && !$query->getDelete()) {
$silvercartDefaultLocale = SilvercartConfig::Locale();
$query->addLeftJoin($this->getLanguageClassName(), sprintf("(\"%s\".\"ID\" = \"%s\".\"%s\")", $this->getBaseClassName(), $this->getLanguageClassName(), $this->getRelationFieldName()));
$addToWhere = '';
if ($this->getBaseLanguageClassName() != $this->getLanguageClassName()) {
$query->addLeftJoin($this->getBaseLanguageClassName(), sprintf("(\"%s\".\"ID\" = \"%s\".\"ID\")", $this->getLanguageClassName(), $this->getBaseLanguageClassName()));
$addToWhere = sprintf("AND \"%s\".\"ID\" = \"%s\".\"ID\"", $this->getBaseLanguageClassName(), $this->getLanguageClassName());
}
if (SilvercartConfig::useDefaultLanguageAsFallback() && Translatable::get_current_locale() != $silvercartDefaultLocale && !empty($silvercartDefaultLocale)) {
$query->addWhere(sprintf("\"%s\".\"Locale\" = IFNULL((%s), (%s)) %s", $this->getBaseLanguageClassName(), $this->getLocaleDependantSelect(Translatable::get_current_locale()), $this->getLocaleDependantSelect($silvercartDefaultLocale), $addToWhere));
} elseif (!empty($silvercartDefaultLocale)) {
$query->addWhere(sprintf("\"%s\".\"Locale\" = '%s' %s", $this->getBaseLanguageClassName(), Translatable::get_current_locale(), $addToWhere));
}
}
}
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;
}
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);
}
/**
* Traverse the relationship fields, and add the table
* mappings to the query object state. This has to be called
* in any overloaded {@link SearchFilter->apply()} methods manually.
*
* @param String|array $relation The array/dot-syntax relation to follow
* @return The model class of the related item
*/
public function applyRelation($relation)
{
// NO-OP
if (!$relation) {
return $this->dataClass;
}
if (is_string($relation)) {
$relation = explode(".", $relation);
}
$modelClass = $this->dataClass;
foreach ($relation as $rel) {
$model = singleton($modelClass);
if ($component = $model->has_one($rel)) {
if (!$this->query->isJoinedTo($component)) {
$foreignKey = $rel;
$realModelClass = ClassInfo::table_for_object_field($modelClass, "{$foreignKey}ID");
$this->query->addLeftJoin($component, "\"{$component}\".\"ID\" = \"{$realModelClass}\".\"{$foreignKey}ID\"");
/**
* add join clause to the component's ancestry classes so that the search filter could search on
* its ancestor fields.
*/
$ancestry = ClassInfo::ancestry($component, true);
if (!empty($ancestry)) {
$ancestry = array_reverse($ancestry);
foreach ($ancestry as $ancestor) {
if ($ancestor != $component) {
$this->query->addInnerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\"");
}
}
}
}
$modelClass = $component;
} elseif ($component = $model->has_many($rel)) {
if (!$this->query->isJoinedTo($component)) {
$ancestry = $model->getClassAncestry();
$foreignKey = $model->getRemoteJoinField($rel);
$this->query->addLeftJoin($component, "\"{$component}\".\"{$foreignKey}\" = \"{$ancestry[0]}\".\"ID\"");
/**
* add join clause to the component's ancestry classes so that the search filter could search on
* its ancestor fields.
*/
$ancestry = ClassInfo::ancestry($component, true);
if (!empty($ancestry)) {
$ancestry = array_reverse($ancestry);
foreach ($ancestry as $ancestor) {
if ($ancestor != $component) {
$this->query->addInnerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\"");
}
}
}
}
$modelClass = $component;
} elseif ($component = $model->many_many($rel)) {
list($parentClass, $componentClass, $parentField, $componentField, $relationTable) = $component;
$parentBaseClass = ClassInfo::baseDataClass($parentClass);
$componentBaseClass = ClassInfo::baseDataClass($componentClass);
$this->query->addInnerJoin($relationTable, "\"{$relationTable}\".\"{$parentField}\" = \"{$parentBaseClass}\".\"ID\"");
$this->query->addLeftJoin($componentBaseClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentBaseClass}\".\"ID\"");
if (ClassInfo::hasTable($componentClass)) {
$this->query->addLeftJoin($componentClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentClass}\".\"ID\"");
}
$modelClass = $componentClass;
}
}
return $modelClass;
}
/**
* 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;
}
/**
* Return all data object visible attributes of the specified type, with optional filters.
*
* @parameter <{DATA_OBJECT_NAME}> string
* @parameter <{LIMIT}> integer
* @parameter <{SORT}> array(string, string)
* @parameter <{FILTERS}> array
* @return array
*/
public function retrieveValidated($class, $limit = null, $sort = null, $filters = null)
{
// Validate the data object class.
$class = strtolower($class);
if (in_array($class, array_map('strtolower', ClassInfo::subclassesFor('DataObject'))) && ($configuration = DataObjectOutputConfiguration::get_one('DataObjectOutputConfiguration', "LOWER(IsFor) = '" . Convert::raw2sql($class) . "'")) && ($temporaryClass = DataObject::get_one($class))) {
$class = ClassInfo::baseDataClass($temporaryClass->ClassName);
$visibility = $configuration->APIwesomeVisibility ? explode(',', $configuration->APIwesomeVisibility) : null;
// Validate the sort and filters.
$where = array();
$sortValid = is_array($sort) && count($sort) === 2 && ($order = strtoupper($sort[1])) && ($order === 'ASC' || $order === 'DESC');
$filterValid = is_array($filters) && count($filters);
$sorting = array();
$filtering = array();
// Grab the appropriate attributes for this data object.
if (is_subclass_of($class, 'SiteTree')) {
$where[] = "ClassName = '{$class}'";
$class = 'SiteTree';
} else {
if (is_subclass_of($class, 'File')) {
$where[] = "ClassName = '{$class}'";
$class = 'File';
}
}
$columns = array();
$from = array();
foreach (ClassInfo::subclassesFor($class) as $subclass) {
// Determine the tables to join.
$subclassFields = DataObject::database_fields($subclass);
if (ClassInfo::hasTable($subclass)) {
// Determine the versioned table.
$same = $subclass === $class;
if ($subclass::has_extension('Versioned')) {
$subclass = "{$subclass}_Live";
}
if (!$same) {
$from[] = $subclass;
}
}
// Prepend the table names.
$subclassColumns = array();
foreach ($subclassFields as $column => $type) {
$subclassColumn = "{$subclass}.{$column}";
$subclassColumns[$subclassColumn] = $type;
// Determine the tables to sort and filter on.
if ($sortValid && $sort[0] === $column) {
$sorting[] = "{$subclassColumn} {$order}";
}
if ($filterValid && isset($filters[$column])) {
$filtering[$subclassColumn] = is_numeric($filters[$column]) ? "{$subclassColumn} = " . (int) $filters[$column] : "LOWER({$subclassColumn}) = '" . Convert::raw2sql(strtolower($filters[$column])) . "'";
}
}
$columns = array_merge($columns, $subclassColumns);
}
array_shift($columns);
// Determine the versioned table.
if ($class::has_extension('Versioned')) {
$class = "{$class}_Live";
}
// Determine ID based sorting and filtering, as these aren't considered database fields.
if ($sortValid && $sort[0] === 'ID') {
$sorting[] = "{$class}.ID {$order}";
}
if ($filterValid && isset($filters['ID'])) {
$where[] = "{$class}.ID = " . (int) $filters['ID'];
}
// Make sure this data object type has visibility customisation.
if ($visibility && count($visibility) === count($columns) && in_array('1', $visibility)) {
// Apply any visibility customisation.
$select = ' ';
$iteration = 0;
foreach ($columns as $attribute => $type) {
if (isset($visibility[$iteration]) && $visibility[$iteration]) {
$select .= $attribute . ', ';
if (isset($filtering[$attribute])) {
// Apply the filter if the matching attribute is visible.
$where[] = $filtering[$attribute];
}
}
$iteration++;
}
if (isset($filtering["{$class}.ClassName"])) {
$where[] = $filtering["{$class}.ClassName"];
}
// Grab all data object visible attributes.
$query = new SQLQuery("{$class}.ClassName,{$select}{$class}.ID", $class, $where, $sorting, array(), array(), is_numeric($limit) ? $limit : array());
// Determine the tables with visible attributes to join.
foreach ($from as $join) {
if (strpos($select, " {$join}.") !== false) {
$query->addLeftJoin($join, "{$class}.ID = {$join}.ID");
}
}
$objects = array();
foreach ($query->execute() as $temporary) {
// Return an array of data object maps.
$object = array();
foreach ($temporary as $attribute => $value) {
if ($value) {
$object[$attribute] = $value;
}
}
$objects[] = $object;
}
return $objects;
}
}
// The specified data object type had no visibility customisation.
return null;
}
/**
* Returns a number of topseller products.
*
* @return ArrayList
*
* @author Sebastian Diel <*****@*****.**>,
* Sascha Koehler <*****@*****.**>
* @since 03.02.2015
*/
public function Elements()
{
if (!$this->numberOfProductsToShow) {
$this->numberOfProductsToShow = SilvercartTopsellerProductsWidget::$defaults['numberOfProductsToShow'];
}
$cachekey = 'TopsellerProducts' . $this->numberOfProductsToShow;
$cache = SS_Cache::factory($cachekey);
$cachedResult = $cache->load($cachekey);
if ($cachedResult) {
$result = unserialize($result);
} else {
$products = array();
$sqlQuery = new SQLQuery();
$sqlQuery->selectField('SOP.SilvercartProductID');
$sqlQuery->selectField('SUM(SOP.Quantity) AS OrderedQuantity');
$sqlQuery->addFrom('SilvercartOrderPosition SOP');
$sqlQuery->addLeftJoin('SilvercartProduct', 'SP.ID = SOP.SilvercartProductID', 'SP');
$sqlQuery->addWhere('SP.isActive = 1');
$sqlQuery->addGroupBy('SOP.SilvercartProductID');
$sqlQuery->addOrderBy('OrderedQuantity', 'DESC');
$sqlQuery->setLimit($this->numberOfProductsToShow);
$sqlResult = $sqlQuery->execute();
foreach ($sqlResult as $row) {
$product = DataObject::get_by_id('SilvercartProduct', $row['SilvercartProductID']);
$product->addCartFormIdentifier = $this->ID . '_' . $product->ID;
$products[] = $product;
}
$result = new ArrayList($products);
}
return $result;
}
public function testJoinSubSelect()
{
// Test sub-select works
$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->assertEquals('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());
// Test that table names do not get mistakenly identified as sub-selects
$query = new SQLQuery();
$query->setFrom('"MyTable"');
$query->addInnerJoin('NewsArticleSelected', '"News"."MyTableID" = "MyTable"."ID"', 'News');
$this->assertEquals('SELECT * FROM "MyTable" INNER JOIN "NewsArticleSelected" AS "News" ON ' . '"News"."MyTableID" = "MyTable"."ID"', $query->sql());
}
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));
}
/**
* 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 testInnerJoin()
{
$query = new SQLQuery();
$query->setFrom('MyTable');
$query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2');
$query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID');
$this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" ON MyOtherTable.ID = MyLastTable.ID', $query->sql());
$query = new SQLQuery();
$query->setFrom('MyTable');
$query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2', 'table1');
$query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID', 'table2');
$this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "table1" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "table2" ON MyOtherTable.ID = MyLastTable.ID', $query->sql());
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if (Cookie::get('noSubsiteFilter') == 'true') {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
if (!$query->filtersOnID()) {
/* if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
// Don't filter by Group_Subsites if we've already done that
$hasGroupSubsites = false;
foreach ($query->getFrom() as $item) {
if (is_array($item) && strpos($item['table'], 'Group_Subsites') !== false || !is_array($item) && strpos($item, 'Group_Subsites') !== false) {
$hasGroupSubsites = true;
break;
}
}
if (!$hasGroupSubsites) {
if ($subsiteID) {
$query->addLeftJoin("Group_Subsites", "\"Group_Subsites\".\"GroupID\"\n\t\t\t\t\t\t= \"Group\".\"ID\" AND \"Group_Subsites\".\"SubsiteID\" = {$subsiteID}");
$query->addWhere("(\"Group_Subsites\".\"SubsiteID\" IS NOT NULL OR\n\t\t\t\t\t\t\"Group\".\"AccessAllSubsites\" = 1)");
}
}
// WORKAROUND for databases that complain about an ORDER BY when the column wasn't selected (e.g. SQL Server)
$select = $query->getSelect();
if ($hasGroupSubsites && $subsiteID && isset($select[0]) && !$select[0] == 'COUNT(*)') {
$query->orderby = "\"AccessAllSubsites\" DESC" . ($query->orderby ? ', ' : '') . $query->orderby;
}
}
}
/**
* 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;
}
public function testParameterisedLeftJoins()
{
$query = new SQLQuery();
$query->setSelect(array('"SQLQueryTest_DO"."Name"', '"SubSelect"."Count"'));
$query->setFrom('"SQLQueryTest_DO"');
$query->addLeftJoin('(SELECT "Title", COUNT(*) AS "Count" FROM "SQLQueryTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?)', '"SQLQueryTest_DO"."Name" = "SubSelect"."Title"', 'SubSelect', 20, array('%MyName%'));
$query->addWhere(array('"SQLQueryTest_DO"."Date" > ?' => '2012-08-08 12:00'));
$this->assertSQLEquals('SELECT "SQLQueryTest_DO"."Name", "SubSelect"."Count"
FROM "SQLQueryTest_DO" LEFT JOIN (SELECT "Title", COUNT(*) AS "Count" FROM "SQLQueryTestBase"
GROUP BY "Title" HAVING "Title" NOT LIKE ?) AS "SubSelect" ON "SQLQueryTest_DO"."Name" =
"SubSelect"."Title"
WHERE ("SQLQueryTest_DO"."Date" > ?)', $query->sql($parameters));
$this->assertEquals(array('%MyName%', '2012-08-08 12:00'), $parameters);
$query->execute();
}
/**
* @return SQLQuery
*/
protected function getRemoteObjectsQuery()
{
// Get all tables to query
$tables = $this->getRemoteClassHierarchy();
$baseClass = array_shift($tables);
// Generate sql query
$query = new SQLQuery("\"{$baseClass}\".*", "\"{$baseClass}\"", $this->targetWhere);
$query->setOrderBy("\"{$baseClass}\".\"ID\" ASC");
// Sort by ID for some performance reasons
foreach ($tables as $class) {
$query->addSelect("\"{$class}\".*");
$query->addLeftJoin($class, "\"{$baseClass}\".\"ID\" = \"{$class}\".\"ID\"");
}
return $query;
}
/**
* @param string $location
* @return string
*/
function getContinentFromLocation($location)
{
$loc_array = explode(',', $location);
$country_code = count($loc_array) ? trim(end($loc_array)) : '';
if ($country_code) {
$sqlQuery = new SQLQuery();
$sqlQuery->setFrom("Continent");
$sqlQuery->selectField("Name");
$sqlQuery->addLeftJoin("Continent_Countries", "Continent_Countries.ContinentID = Continent.ID");
$sqlQuery->addWhere("Continent_Countries.CountryCode = '{$country_code}'");
$continent = $sqlQuery->execute()->first();
if ($continent) {
return $continent['Name'];
}
}
return '';
}
