/**
* @return array
*/
protected function read()
{
$data = array();
foreach ($this->query->execute() as $result) {
if (isset($result[$this->documentColumn])) {
$data[] = array('category' => $this->category, 'document' => $result[$this->documentColumn]);
}
}
return $data;
}
/**
* Generate the items in this map. This is used by
* getItems() if the items have not been generated already.
*/
protected function genItems()
{
if (!isset($this->items)) {
$this->items = new DataObjectSet();
$items = $this->query->execute();
foreach ($items as $item) {
$className = isset($item['RecordClassName']) ? $item['RecordClassName'] : $item['ClassName'];
if (!$className) {
user_error('SQLMap query could not retrieve className', E_USER_ERROR);
}
$this->items->push(new $className($item));
}
}
}
static function all_votes_count() {
$query = new SQLQuery(
"COUNT(Choice)",
"Vote",
"Choice BETWEEN 1 AND 5");
return $query->execute()->value();
}
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 run($request)
{
$confirm = $request->getVar('run') ? true : false;
$force = $request->getVar('force') ? true : false;
$since = $request->getVar('older');
if (!$since) {
echo "Please specify an 'older' param with a date older than which to prune (in strtotime friendly format)<br/>\n";
return;
}
$since = strtotime($since);
if (!$since) {
echo "Please specify an 'older' param with a date older than which to prune (in strtotime friendly format)<br/>\n";
return;
}
if ($since > strtotime('-3 months') && !$force) {
echo "To cleanup data more recent than 3 months, please supply the 'force' parameter as well as the run parameter, swapping to dry run <br/>\n";
$confirm = false;
}
$since = date('Y-m-d H:i:s', $since);
$items = DataChangeRecord::get()->filter('Created:LessThan', $since);
$max = $items->max('ID');
echo "Pruning records older than {$since} (ID {$max})<br/>\n";
if ($confirm && $max) {
$query = new SQLQuery('*', 'DataChangeRecord', '"ID" < \'' . $max . '\'');
$query->setDelete(true);
$query->execute();
} else {
echo "Dry run performed, please supply the run=1 parameter to actually execute the deletion!<br/>\n";
}
}
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 __construct($controller, $name = "VariationForm")
{
parent::__construct($controller, $name);
$product = $controller->data();
$farray = array();
$requiredfields = array();
$attributes = $product->VariationAttributeTypes();
foreach ($attributes as $attribute) {
$attributeDropdown = $attribute->getDropDownField(_t('VariationForm.ChooseAttribute', "Choose {attribute} …", '', array('attribute' => $attribute->Label)), $product->possibleValuesForAttributeType($attribute));
if ($attributeDropdown) {
$farray[] = $attributeDropdown;
$requiredfields[] = "ProductAttributes[{$attribute->ID}]";
}
}
$fields = FieldList::create($farray);
if (self::$include_json) {
$vararray = array();
$query = $query2 = new SQLQuery();
$query->setSelect('ID')->setFrom('ProductVariation')->addWhere(array('ProductID' => $product->ID));
if (!Product::config()->allow_zero_price) {
$query->addWhere('"Price" > 0');
}
foreach ($query->execute()->column('ID') as $variationID) {
$query2->setSelect('ProductAttributeValueID')->setFrom('ProductVariation_AttributeValues')->setWhere(array('ProductVariationID' => $variationID));
$vararray[$variationID] = $query2->execute()->keyedColumn();
}
$fields->push(HiddenField::create('VariationOptions', 'VariationOptions', json_encode($vararray)));
}
$fields->merge($this->Fields());
$this->setFields($fields);
$requiredfields[] = 'Quantity';
$this->setValidator(VariationFormValidator::create($requiredfields));
$this->extend('updateVariationForm');
}
/**
* 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 OrganizationCount()
{
$payload = function () {
$sqlQuery = new SQLQuery("COUNT(DISTINCT(ID))", "Company", "DisplayOnSite = TRUE");
return $sqlQuery->execute()->value();
};
return EntityCounterHelper::getInstance()->EntityCount("Company", $payload);
}
/**
* Assign a sort number when object is written
* @see DataExtension::onBeforeWrite()
*/
public function onBeforeWrite()
{
if (!$this->owner->ID && !$this->owner->SortOrder || !$this->owner->SortOrder) {
$classes = ClassInfo::dataClassesFor($this->owner->ClassName);
$sql = new SQLQuery('count(ID)', array_shift($classes));
$val = $sql->execute()->value();
$this->owner->SortOrder = is_numeric($val) ? $val + 1 : 1;
}
}
/**
* Assign a sort number when object is written
* @see DataExtension::onBeforeWrite()
*/
public function onBeforeWrite()
{
if (!$this->owner->exists() || !$this->owner->SortOrder) {
$classes = ClassInfo::dataClassesFor($this->owner->ClassName);
$sql = new SQLQuery('MAX("SortOrder")', '"' . array_shift($classes) . '"');
$val = $sql->execute()->value();
$this->owner->SortOrder = is_numeric($val) ? $val + 1 : 1;
}
}
/**
* @param Array - $googleMapAddressArray: an array of geographic data provided by google maps
* @param Int - $maxRadius: maximum number of kilometers (as the bird flies) between search point defined in $googleMapAddressArray and city found.
* @return Object | false : returns a BrowseCitiesPage or false if nothing was found
**/
public static function get_clostest_city_page($googleMapAddressArray, $maxRadius = 500)
{
$cityPage = null;
$suburbPage = null;
$bt = defined('DB::USE_ANSI_SQL') ? "\"" : "`";
$existingDistance = $maxRadius + 1;
$newDistance = $maxRadius + 1;
$existingPage = null;
$newPage = null;
$radiusSelectionSQL = self::radiusDefinitionOtherTable($googleMapAddressArray[0], $googleMapAddressArray[1], "BrowseCitiesPage", "Latitude", "Longitude");
$sqlQuery = new SQLQuery();
$sqlQuery->select = array("{$bt}BrowseCitiesPage{$bt}.{$bt}ID{$bt}, " . $radiusSelectionSQL . " as distance");
$sqlQuery->from[] = "{$bt}BrowseCitiesPage{$bt}";
$sqlQuery->where[] = $radiusSelectionSQL . " < " . $maxRadius;
$sqlQuery->orderby = " distance ";
$sqlQuery->limit = "1";
$result = $sqlQuery->execute();
$page = null;
foreach ($result as $row) {
$existingDistance = $row["distance"];
$existingPage = DataObject::get_by_id("BrowseCitiesPage", $row["ID"]);
}
$radiusSelectionSQL = self::radiusDefinitionOtherTable($googleMapAddressArray[0], $googleMapAddressArray[1], "cities", "Latitude", "Longitude");
$sqlQuery = new SQLQuery();
$sqlQuery->select = array("cities.CityID", $radiusSelectionSQL . " as distance");
$sqlQuery->from[] = "{$bt}cities{$bt}";
$sqlQuery->where[] = $radiusSelectionSQL . " < " . $maxRadius;
$sqlQuery->orderby = " distance ";
$sqlQuery->limit = "1";
$result = $sqlQuery->execute();
foreach ($result as $row) {
$sameOne = false;
if ($existingPage) {
if ($row["CityID"] == $existingPage->HiddenDataID) {
$sameOne = true;
}
}
if (!$sameOne) {
$newPage = self::create_city_and_parents($row["CityID"]);
$newDistance = $row["distance"];
}
}
if ($newPage && $newDistance < $existingDistance && $newDistance < $maxRadius) {
$cityPage = $newPage;
} elseif ($existingPage && $existingDistance < $maxRadius) {
$cityPage = $existingPage;
}
if ($cityPage) {
if ($cityPage->allowBrowseChildren()) {
$suburbPage = BrowseSuburbPage::create_suburb($googleMapAddressArray, $cityPage);
}
}
if ($suburbPage) {
return $suburbPage;
}
return $cityPage;
}
/**
* Assign a sort number when object is written
* @see DataExtension::onBeforeWrite()
*/
public function onBeforeWrite()
{
if (!$this->owner->exists() || !$this->owner->SortOrder) {
// get the table in the ancestry that has the SortOrder field
$table = ClassInfo::table_for_object_field($this->owner->class, 'SortOrder');
$sql = new SQLQuery('MAX("SortOrder")', $table);
$val = $sql->execute()->value();
$this->owner->SortOrder = is_numeric($val) ? $val + 1 : 1;
}
}
public function Vendors()
{
$query = new SQLQuery();
$result = new ArrayList();
$query->setSelect('"AddonVendor"."Name"')->selectField('COUNT("Addon"."ID")' . 'Count')->setFrom('"AddonVendor"')->addLeftJoin('Addon', '"Addon"."VendorID" = "AddonVendor"."ID"')->setGroupBy('"AddonVendor"."ID"')->setOrderBy(array('"Count"' => 'DESC', '"Name"' => 'ASC'));
foreach ($query->execute() as $row) {
$link = Controller::join_links(Director::baseURL(), 'add-ons', $row['Name']);
$result->push(new ArrayData($row + array('Link' => $link)));
}
return $result;
}
public function Tags()
{
$query = new SQLQuery();
$result = new ArrayList();
$query->setSelect('"AddonKeyword"."ID", "Name"')->selectField('COUNT("AddonKeywordID")', 'Count')->setFrom('AddonKeyword')->addLeftJoin('Addon_Keywords', '"AddonKeywordID" = "AddonKeyword"."ID"')->setGroupBy('"ID"')->setOrderBy(array('"Count"' => 'DESC', '"Name"' => 'ASC'));
foreach ($query->execute() as $row) {
$link = Controller::join_links(Director::baseURL(), 'add-ons', '?' . http_build_query(array('tags[]' => $row['Name'])));
$result->push(new ArrayData($row + array('Link' => $link)));
}
return $result;
}
/**
* Gets all of the assigned identifiers in the database
* @return array
*/
public static function get_metric_identifiers()
{
$allowedMetrics = new SQLQuery('Identifier', 'AUCMetric');
$allowedMetrics->setDistinct(true);
$result = $allowedMetrics->execute();
$identifiers = [];
foreach ($result as $row) {
$identifiers[] = $row['Identifier'];
}
return $identifiers;
}
/**
* @return Array Map of Snippet Folder IDs
*/
public function snippetFoldersIncluded()
{
$ids = array();
$q = new SQLQuery();
$q->setSelect(array('"Snippet"."ID"', '"Snippet"."FolderID"'))->setFrom('"Snippet"');
if (!empty($this->creator)) {
$q->setWhereAny(array("\"CreatorID\"=" . intval($this->creator)));
}
foreach ($q->execute() as $row) {
$ids[] = array('ID' => $row['FolderID']);
}
return $ids;
}
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);
}
/**
* retrieves data from a DB table that is not part of the DataObject Model.
**/
protected function getDataFromTable($tableName, $where = null, $orderby = null)
{
$sqlQuery = new SQLQuery();
$sqlQuery->select = array('*');
$sqlQuery->from = array($tableName);
if ($where) {
$sqlQuery->where = array($where);
}
if ($orderby) {
$sqlQuery->orderby = $orderby;
}
$result = $sqlQuery->execute();
return $result;
}
public function run($request)
{
$query = new SQLQuery("*", "Payment");
foreach ($query->execute() as $record) {
if ($this->migrationRequired($record)) {
$this->migrateRecord($record);
}
}
if ($this->count > 0) {
echo "Successfully migrated {$this->count} payments";
} else {
echo "No migration needed";
}
}
/**
* @return Array Map of Snippet Folder IDs
*/
public function snippetFoldersIncluded()
{
$ids = array();
$q = new SQLQuery();
$q->setSelect(array('"Snippet"."ID"', '"Snippet"."FolderID"'))->setFrom('"Snippet"');
if (!empty($this->tag)) {
$SQL_val = Convert::raw2sql($this->tag);
$q->setWhereAny(array("\"Tags\" LIKE '{$SQL_val},%' OR \"Tags\" LIKE '%,{$SQL_val}' OR \"Tags\" LIKE '%,{$SQL_val},%' OR \"Tags\" LIKE '{$SQL_val}'"));
}
foreach ($q->execute() as $row) {
$ids[] = array('ID' => $row['FolderID']);
}
return $ids;
}
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;
}
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;
}
/**
* 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;
}
/**
* 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;
}
public function EntityCount($entity_name, callable $payload_function = null)
{
$cache = SS_Cache::factory('cache_entity_count');
$result = unserialize($cache->load('var_' . $entity_name));
if (!$result) {
if ($payload_function == null) {
$sqlQuery = new SQLQuery("COUNT(ID)", array($entity_name));
$result = $sqlQuery->execute()->value();
} else {
$result = $payload_function();
}
$cache->save(serialize($result), 'var_' . $entity_name);
}
if (Director::is_ajax()) {
return json_encode($result);
}
return $result;
}
/**
* Helper method to get content languages from the live DB table.
* Most of the code is borrowed from the Translatable::get_live_content_languages method.
* This method operates on "SiteTree" and makes a distinction between Live and Stage.
* @return array
*/
public static function get_content_languages()
{
$table = Versioned::current_stage() == 'Live' ? 'SiteTree_Live' : 'SiteTree';
$query = new SQLQuery("Distinct \"Locale\"", "\"{$table}\"", '', '', '"Locale"');
$dbLangs = $query->execute()->column();
$langlist = array_merge((array) Translatable::default_locale(), (array) $dbLangs);
$returnMap = array();
$allCodes = array_merge(Config::inst()->get('i18n', 'all_locales'), Config::inst()->get('i18n', 'common_locales'));
foreach ($langlist as $langCode) {
if ($langCode && isset($allCodes[$langCode])) {
if (is_array($allCodes[$langCode])) {
$returnMap[$langCode] = $allCodes[$langCode]['name'];
} else {
$returnMap[$langCode] = $allCodes[$langCode];
}
}
}
return $returnMap;
}
/**
* Attempt to find an old/renamed page from some given the URL as an array
*
* @param array $params The array of URL, e.g. /foo/bar as array('foo', 'bar')
* @param SiteTree $parent The current parent in the recursive flow
* @param boolean $redirect Whether we've found an old page worthy of a redirect
*
* @return string|boolean False, or the new URL
*/
public static function find_old_page($params, $parent = null, $redirect = false)
{
$URL = Convert::raw2sql(array_shift($params));
if (empty($URL)) {
return false;
}
if ($parent) {
$page = SiteTree::get()->filter(array('ParentID' => $parent->ID, 'URLSegment' => $URL))->First();
} else {
$page = SiteTree::get()->filter(array('URLSegment' => $URL))->First();
}
if (!$page) {
// If we haven't found a candidate, lets resort to finding an old page with this URL segment
// TODO: Rewrite using ORM syntax
$query = new SQLQuery('"RecordID"', '"SiteTree_versions"', "\"URLSegment\" = '{$URL}' AND \"WasPublished\" = 1" . ($parent ? ' AND "ParentID" = ' . $parent->ID : ''), '"LastEdited" DESC', null, null, 1);
$record = $query->execute()->first();
if ($record) {
$page = SiteTree::get()->byID($record['RecordID']);
$redirect = true;
}
}
if ($page && $page->canView()) {
if (count($params)) {
// We have to go deeper!
$ret = self::find_old_page($params, $page, $redirect);
if ($ret) {
// A valid child page was found! We can return it
return $ret;
} else {
// No valid page found.
if ($redirect) {
// If we had some redirect to be done, lets do it. imagine /foo/action -> /bar/action, we still want this redirect to happen if action isn't a page
return $page->Link() . implode('/', $params);
}
}
} else {
// We've found the final, end all, page.
return $page->Link();
}
}
return false;
}
