/**
* 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;
}
/**
* Add in rating information
*
* @param SQLQuery $query
* @param DataQuery $dataQuery
*/
public function augmentSQL(SQLQuery &$query)
{
$base = ClassInfo::baseDataClass($this->owner);
$simple = DB::getConn() instanceof SQLite3Database ? true : false;
if ($simple) {
$bound = '((Up / Down) / (Up + Down))';
} else {
$bound = '((Up + 1.9208) / (Up + Down) - ' . '1.96 * SQRT((Up * Down) / (Up + Down) + 0.9604) / ' . '(Up + Down)) / (1 + 3.8416 / (Up + Down)) / SQRT(HOUR(TIMEDIFF(NOW(), ' . $base . '.Created)) + 1)';
}
$query->selectField($bound, 'WilsonRating');
$query->selectField('(Up + Down)', 'ActiveRating');
$query->selectField('(Up - Down)', 'PositiveRating');
}
public function getPopularInteractions($interactionType, $itemClass, $days, $number = 10)
{
$since = date('Y-m-d H:i:s', strtotime("-{$days} days"));
// Execute an SQL query so we can group by and count.
$interactions = UserInteraction::get()->filter(array('Type' => $interactionType, 'ItemClass' => $itemClass, 'Created:GreaterThan' => $since));
$interactionType = Convert::raw2sql($interactionType);
$itemClass = Convert::raw2sql($itemClass);
$subs = ClassInfo::subclassesFor($itemClass);
$subs[] = $itemClass;
if ($i = array_search('ErrorPage', $subs)) {
unset($subs[$i]);
}
$in = "'" . implode("','", $subs) . "'";
$query = new SQLQuery('*', 'UserInteraction', "Type = '{$interactionType}' AND ItemClass IN ({$in}) AND DATEDIFF(NOW(), Created) <= {$days}", 'Views DESC, Title ASC', 'Title', '', $number);
$query->selectField('COUNT(Title)', 'Views');
$results = $query->execute();
$container = ArrayList::create();
// The array list will need to be populated with objects so the template accepts it.
for ($i = 0; $i < $results->numRecords(); $i++) {
$object = UserInteraction::create($results->record());
if ($object->canView()) {
$container->add($object);
}
}
return $container;
}
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;
}
public function Tags()
{
$select = array('Title');
$query = new SQLQuery($select, 'PostTag');
$query->selectField('count(PostTag.ID)', 'Number');
$query->selectField('PostTag.ID');
$query->addInnerjoin('MicroPost_Tags', 'PostTag.ID = MicroPost_Tags.PostTagID');
$date = date('Y-m-d H:i:s', strtotime('-1 month'));
$query->addWhere("MicroPost_Tags.Tagged > '{$date}'");
$query->addWhere('"PostTag"."Title" NOT LIKE \'SELF_TAG%\'');
$query->addGroupBy('PostTag.ID');
$query->setLimit(20);
$rows = $query->execute();
$tags = ArrayList::create();
foreach ($rows as $row) {
$data = new ArrayData($row);
$data->Link = Controller::join_links(TimelineController::URL_SEGMENT, '?tags=' . urlencode($data->Title));
$tags->push($data);
}
return $tags;
}
public function ArchiveDates()
{
$list = ArrayList::create();
$stage = Versioned::current_stage();
$query = new SQLQuery(array());
$query->selectField("DATE_FORMAT(`Date`,'%Y_%M_%m')", "DateString")->setFrom("ArticlePage_{$stage}")->setOrderBy("Date", "ASC")->setDistinct(true);
$result = $query->execute();
if ($result) {
while ($record = $result->nextRecord()) {
list($year, $monthName, $monthNumber) = explode('_', $record['DateString']);
$list->push(ArrayData::create(array('Year' => $year, 'MonthName' => $monthName, 'MonthNumber' => $monthNumber, 'Link' => $this->Link("date/{$year}/{$monthNumber}"), 'ArticleCount' => ArticlePage::get()->where("\n\t\t\t\t\t\t\tDATE_FORMAT(`Date`,'%Y%m') = '{$year}{$monthNumber}'\n\t\t\t\t\t\t\tAND ParentID = {$this->ID}\n\t\t\t\t\t\t")->count())));
}
}
return $list;
}
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;
}
}
public function testDbDatetimeDifference()
{
$offset = $this->checkPreconditions();
$clause = $this->adapter->datetimeDifferenceClause('1974-10-14 10:30:00', '1973-10-14 10:30:00');
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result / 86400, 365, '1974 - 1973 = 365 * 86400 sec', $offset);
$clause = $this->adapter->datetimeDifferenceClause(date('Y-m-d H:i:s', strtotime('-15 seconds')), 'now');
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result, -15, '15 seconds ago - now', $offset);
$clause = $this->adapter->datetimeDifferenceClause('now', $this->adapter->datetimeIntervalClause('now', '+45 Minutes'));
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result, -45 * 60, 'now - 45 minutes ahead', $offset);
$query = new SQLQuery();
$query->setSelect(array());
$query->selectField($this->adapter->datetimeDifferenceClause('"LastEdited"', '"Created"'), 'test')->setFrom('"DbDateTimeTest_Team"')->setLimit(1);
$result = $query->execute()->value();
$lastedited = Dataobject::get_one('DbDateTimeTest_Team')->LastEdited;
$created = Dataobject::get_one('DbDateTimeTest_Team')->Created;
$this->matchesRoughly($result, strtotime($lastedited) - strtotime($created), 'age of HomePage record in seconds since unix epoc', $offset);
}
/**
* Select the given field expressions.
*
* @param $fieldExpression String The field to select (escaped SQL statement)
* @param $alias String The alias of that field (escaped SQL statement)
*/
protected function selectField($fieldExpression, $alias = null)
{
$this->query->selectField($fieldExpression, $alias);
}
/**
* Augment the the SQLQuery that is created by the DataQuery
* @todo Should this all go into VersionedDataQuery?
*/
function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
$baseTable = ClassInfo::baseDataClass($dataQuery->dataClass());
switch ($dataQuery->getQueryParam('Versioned.mode')) {
// Noop
case '':
break;
// Reading a specific data from the archive
// Reading a specific data from the archive
case 'archive':
$date = $dataQuery->getQueryParam('Versioned.date');
foreach ($query->getFrom() as $table => $dummy) {
$query->renameTable($table, $table . '_versions');
$query->replaceText("\"{$table}\".\"ID\"", "\"{$table}\".\"RecordID\"");
// Add all <basetable>_versions columns
foreach (self::$db_for_versions_table as $name => $type) {
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
}
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
if ($table != $baseTable) {
$query->addFrom(array($table => " AND \"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\""));
}
}
// Link to the version archived on that date
$archiveTable = $this->requireArchiveTempTable($baseTable, $date);
$query->addFrom(array($archiveTable => "INNER JOIN \"{$archiveTable}\"\n\t\t\t\tON \"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" \n\t\t\t\tAND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\""));
break;
// Reading a specific stage (Stage or Live)
// Reading a specific stage (Stage or Live)
case 'stage':
$stage = $dataQuery->getQueryParam('Versioned.stage');
if ($stage && $stage != $this->defaultStage) {
foreach ($query->getFrom() as $table => $dummy) {
// Only rewrite table names that are actually part of the subclass tree
// This helps prevent rewriting of other tables that get joined in, in
// particular, many_many tables
if (class_exists($table) && ($table == $this->owner->class || is_subclass_of($table, $this->owner->class) || is_subclass_of($this->owner->class, $table))) {
$query->renameTable($table, $table . '_' . $stage);
}
}
}
break;
// Return all version instances
// Return all version instances
case 'all_versions':
case 'latest_versions':
foreach ($query->getFrom() as $alias => $join) {
if ($alias != $baseTable) {
$query->setJoinFilter($alias, "\"{$alias}\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\" AND \"{$alias}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
$query->renameTable($alias, $alias . '_versions');
}
// Add all <basetable>_versions columns
foreach (self::$db_for_versions_table as $name => $type) {
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
}
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
// latest_version has one more step
// Return latest version instances, regardless of whether they are on a particular stage
// This provides "show all, including deleted" functonality
if ($dataQuery->getQueryParam('Versioned.mode') == 'latest_versions') {
$archiveTable = self::requireArchiveTempTable($baseTable);
$query->addInnerJoin($archiveTable, "\"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" AND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
break;
default:
throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode'));
}
}
/**
* @param SQLQuery $query
* @param DataQuery $dataQuery
*/
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
$controller = Controller::curr();
if (!is_subclass_of($controller, 'LeftAndMain')) {
$includedTables = ContinentalContent::getAffectedTables();
$strContinent = ContinentalContent::CurrentContinent();
if ($strContinent != CONTINENTAL_DEFAULT) {
foreach ($query->getSelect() as $alias => $select) {
if (!preg_match('/^"(?<class>\\w+)"\\."(?<field>\\w+)"$/i', $select, $matches)) {
continue;
}
$class = $matches['class'];
$field = $matches['field'];
if (!in_array($class, $includedTables)) {
continue;
}
$strNewField = $field . '_' . $strContinent;
$arrFields = ContinentalContent::make_continental_fields($class);
if (isset($arrFields['db']) && isset($arrFields['db'][$strNewField])) {
$expression = $this->localiseSelect($class, $strNewField, $field);
$query->selectField($expression, $alias);
}
}
// TODO: update where clues too
}
}
}
/**
* @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 '';
}
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
// Get locale and translation zone to use
$default = Fluent::default_locale();
$locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale();
// Get all tables to translate fields for, and their respective field names
$includedTables = $this->getTranslatedTables();
// Iterate through each select clause, replacing each with the translated version
foreach ($query->getSelect() as $alias => $select) {
// Skip fields without table context
if (!preg_match('/^"(?<class>\\w+)"\\."(?<field>\\w+)"$/i', $select, $matches)) {
continue;
}
$class = $matches['class'];
$field = $matches['field'];
// If this table doesn't have translated fields then skip
if (empty($includedTables[$class])) {
continue;
}
// If this field shouldn't be translated, skip
if (!in_array($field, $includedTables[$class])) {
continue;
}
// Select visible field from translated fields (Title_fr_FR || Title => Title)
$translatedField = Fluent::db_field_for_locale($field, $locale);
$expression = $this->localiseSelect($class, $translatedField, $field);
$query->selectField($expression, $alias);
// At the same time, rewrite the selector for the default field to make sure that
// (in the case it is blank, which happens if installing fluent for the first time)
// that it also populated from the root field.
$defaultField = Fluent::db_field_for_locale($field, $default);
$defaultExpression = $this->localiseSelect($class, $defaultField, $field);
$query->selectField($defaultExpression, $defaultField);
}
// Rewrite where conditions with parameterised query (3.2 +)
$where = $query->toAppropriateExpression()->getWhere();
foreach ($where as $index => $condition) {
// Extract parameters from condition
if ($condition instanceof SQLConditionGroup) {
$parameters = array();
$predicate = $condition->conditionSQL($parameters);
} else {
$parameters = array_values(reset($condition));
$predicate = key($condition);
}
// determine the table/column this condition is against
$filterColumn = $this->detectFilterColumn($predicate, $includedTables, $locale);
if (empty($filterColumn)) {
continue;
}
// Duplicate the condition with all localisable fields replaced
$localisedPredicate = $this->localiseFilterCondition($predicate, $includedTables, $locale);
if ($localisedPredicate === $predicate) {
continue;
}
// Generate new condition that conditionally executes one of the two conditions
// depending on field nullability.
// If the filterColumn is null or empty, then it's considered untranslated, and
// thus the query should continue running on the default column unimpeded.
$castColumn = "COALESCE(CAST({$filterColumn} AS CHAR), '')";
$newPredicate = "\n\t\t\t\t({$castColumn} != '' AND {$castColumn} != '0' AND ({$localisedPredicate}))\n\t\t\t\tOR (\n\t\t\t\t\t({$castColumn} = '' OR {$castColumn} = '0') AND ({$predicate})\n\t\t\t\t)";
// Duplicate this condition with parameters duplicated
$where[$index] = array($newPredicate => array_merge($parameters, $parameters));
}
$query->setWhere($where);
// Augment search if applicable
if ($adapter = Fluent::search_adapter()) {
$adapter->augmentSearch($query, $dataQuery);
}
}
/**
* 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;
}
/**
* 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;
}
/**
* Augment the the SQLQuery that is created by the DataQuery
* @todo Should this all go into VersionedDataQuery?
*/
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
$baseTable = ClassInfo::baseDataClass($dataQuery->dataClass());
switch ($dataQuery->getQueryParam('Versioned.mode')) {
// Noop
case '':
break;
// Reading a specific data from the archive
// Reading a specific data from the archive
case 'archive':
$date = $dataQuery->getQueryParam('Versioned.date');
foreach ($query->getFrom() as $table => $dummy) {
$query->renameTable($table, $table . '_versions');
$query->replaceText("\"{$table}_versions\".\"ID\"", "\"{$table}_versions\".\"RecordID\"");
$query->replaceText("`{$table}_versions`.`ID`", "`{$table}_versions`.`RecordID`");
// Add all <basetable>_versions columns
foreach (self::$db_for_versions_table as $name => $type) {
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
}
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
if ($table != $baseTable) {
$query->addWhere("\"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
}
// Link to the version archived on that date
$safeDate = Convert::raw2sql($date);
$query->addWhere("\"{$baseTable}_versions\".\"Version\" IN \n\t\t\t\t\t(SELECT LatestVersion FROM \n\t\t\t\t\t\t(SELECT \n\t\t\t\t\t\t\t\"{$baseTable}_versions\".\"RecordID\", \n\t\t\t\t\t\t\tMAX(\"{$baseTable}_versions\".\"Version\") AS LatestVersion\n\t\t\t\t\t\t\tFROM \"{$baseTable}_versions\"\n\t\t\t\t\t\t\tWHERE \"{$baseTable}_versions\".\"LastEdited\" <= '{$safeDate}'\n\t\t\t\t\t\t\tGROUP BY \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t\t\t) AS \"{$baseTable}_versions_latest\"\n\t\t\t\t\t\tWHERE \"{$baseTable}_versions_latest\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t\t)");
break;
// Reading a specific stage (Stage or Live)
// Reading a specific stage (Stage or Live)
case 'stage':
$stage = $dataQuery->getQueryParam('Versioned.stage');
if ($stage && $stage != $this->defaultStage) {
foreach ($query->getFrom() as $table => $dummy) {
// Only rewrite table names that are actually part of the subclass tree
// This helps prevent rewriting of other tables that get joined in, in
// particular, many_many tables
if (class_exists($table) && ($table == $this->owner->class || is_subclass_of($table, $this->owner->class) || is_subclass_of($this->owner->class, $table))) {
$query->renameTable($table, $table . '_' . $stage);
}
}
}
break;
// Reading a specific stage, but only return items that aren't in any other stage
// Reading a specific stage, but only return items that aren't in any other stage
case 'stage_unique':
$stage = $dataQuery->getQueryParam('Versioned.stage');
// Recurse to do the default stage behavior (must be first, we rely on stage renaming happening before
// below)
$dataQuery->setQueryParam('Versioned.mode', 'stage');
$this->augmentSQL($query, $dataQuery);
// Now exclude any ID from any other stage. Note that we double rename to avoid the regular stage rename
// renaming all subquery references to be Versioned.stage
foreach ($this->stages as $excluding) {
if ($excluding == $stage) {
continue;
}
$tempName = 'ExclusionarySource_' . $excluding;
$excludingTable = $baseTable . ($excluding && $excluding != $this->defaultStage ? "_{$excluding}" : '');
$query->addWhere('"' . $baseTable . '"."ID" NOT IN (SELECT "ID" FROM "' . $tempName . '")');
$query->renameTable($tempName, $excludingTable);
}
break;
// Return all version instances
// Return all version instances
case 'all_versions':
case 'latest_versions':
foreach ($query->getFrom() as $alias => $join) {
if ($alias != $baseTable) {
$query->setJoinFilter($alias, "\"{$alias}\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"" . " AND \"{$alias}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
$query->renameTable($alias, $alias . '_versions');
}
// Add all <basetable>_versions columns
foreach (self::$db_for_versions_table as $name => $type) {
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
}
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
$query->addOrderBy(sprintf('"%s_versions"."%s"', $baseTable, 'Version'));
// latest_version has one more step
// Return latest version instances, regardless of whether they are on a particular stage
// This provides "show all, including deleted" functonality
if ($dataQuery->getQueryParam('Versioned.mode') == 'latest_versions') {
$query->addWhere("\"{$alias}_versions\".\"Version\" IN \n\t\t\t\t\t(SELECT LatestVersion FROM \n\t\t\t\t\t\t(SELECT \n\t\t\t\t\t\t\t\"{$alias}_versions\".\"RecordID\", \n\t\t\t\t\t\t\tMAX(\"{$alias}_versions\".\"Version\") AS LatestVersion\n\t\t\t\t\t\t\tFROM \"{$alias}_versions\"\n\t\t\t\t\t\t\tGROUP BY \"{$alias}_versions\".\"RecordID\"\n\t\t\t\t\t\t) AS \"{$alias}_versions_latest\"\n\t\t\t\t\t\tWHERE \"{$alias}_versions_latest\".\"RecordID\" = \"{$alias}_versions\".\"RecordID\"\n\t\t\t\t\t)");
}
break;
default:
throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode'));
}
}
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
// Get locale and translation zone to use
$locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale();
// Get all tables to translate fields for, and their respective field names
$includedTables = $this->getTranslatedTables();
// Iterate through each select clause, replacing each with the translated version
foreach ($query->getSelect() as $alias => $select) {
// Skip fields without table context
if (!preg_match('/^"(?<class>\\w+)"\\."(?<field>\\w+)"$/i', $select, $matches)) {
continue;
}
$class = $matches['class'];
$field = $matches['field'];
// If this table doesn't have translated fields then skip
if (empty($includedTables[$class])) {
continue;
}
// If this field shouldn't be translated, skip
if (!in_array($field, $includedTables[$class])) {
continue;
}
// Select visible field from translated fields (Title_fr_FR || Title => Title)
$translatedField = Fluent::db_field_for_locale($field, $locale);
$expression = $this->localiseSelect($class, $translatedField, $field);
$query->selectField($expression, $alias);
}
// Rewrite where conditions
$where = $query->getWhere();
foreach ($where as $index => $condition) {
// determine the table/column this condition is against
$filterColumn = $this->detectFilterColumn($condition, $includedTables, $locale);
if (empty($filterColumn)) {
continue;
}
// Duplicate the condition with all localisable fields replaced
$localisedCondition = $this->localiseFilterCondition($condition, $includedTables, $locale);
if ($localisedCondition === $condition) {
continue;
}
// Generate new condition that conditionally executes one of the two conditions
// depending on field nullability.
// If the filterColumn is null or empty, then it's considered untranslated, and
// thus the query should continue running on the default column unimpeded.
$where[$index] = "\n\t\t\t\t({$filterColumn} IS NOT NULL AND {$filterColumn} != '' AND ({$localisedCondition}))\n\t\t\t\tOR (\n\t\t\t\t\t({$filterColumn} IS NULL OR {$filterColumn} = '') AND ({$condition})\n\t\t\t\t)";
}
$query->setWhere($where);
// Augment search if applicable
if ($adapter = Fluent::search_adapter()) {
$adapter->augmentSearch($query, $dataQuery);
}
}
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);
}
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);
}
/**
* Update the SELECT clause of the query with the columns from the given table
*/
protected function selectColumnsFromTable(SQLQuery &$query, $tableClass, $columns = null)
{
// Add SQL for multi-value fields
$databaseFields = DataObject::database_fields($tableClass);
$compositeFields = DataObject::composite_fields($tableClass, false);
if ($databaseFields) {
foreach ($databaseFields as $k => $v) {
if ((is_null($columns) || in_array($k, $columns)) && !isset($compositeFields[$k])) {
// Update $collidingFields if necessary
if ($expressionForField = $query->expressionForField($k)) {
if (!isset($this->collidingFields[$k])) {
$this->collidingFields[$k] = array($expressionForField);
}
$this->collidingFields[$k][] = "{$tableClass}.{$k}";
} else {
$query->selectField("{$tableClass}.{$k}", $k);
}
}
}
}
if ($compositeFields) {
foreach ($compositeFields as $k => $v) {
if ((is_null($columns) || in_array($k, $columns)) && $v) {
$dbO = Object::create_from_string($v, $k);
$dbO->addToQuery($query);
}
}
}
}
