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');
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query)
{
if (Subsite::$disable_subsite_filter) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly... (but it was WAYYYYYYYYY worse)
//@TODO I don't think excluding if SiteTree_ImageTracking is a good idea however because of the SS 3.0 api and ManyManyList::removeAll() changing the from table after this function is called there isn't much of a choice
$from = $query->getFrom();
$where = $query->getWhere();
if (!isset($from['SiteTree_ImageTracking']) && !($where && preg_match('/\\.(\'|"|`|)ID(\'|"|`|)/', $where[0]))) {
$subsiteID = (int) Subsite::currentSubsiteID();
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
$where = "\"{$tableName}\".\"SubsiteID\" IN (0, {$subsiteID})";
$query->addWhere($where);
break;
}
$sect = array_values($query->getSelect());
$isCounting = strpos($sect[0], 'COUNT') !== false;
// Ordering when deleting or counting doesn't apply
if (!$query->getDelete() && !$isCounting) {
$query->addOrderBy("\"SubsiteID\"");
}
}
}
/**
* Amend the query to select from a future date if necessary.
*/
function augmentSQL(SQLQuery &$query)
{
if ($datetime = self::get_future_datetime()) {
foreach ($query->from as $table => $dummy) {
if (!isset($baseTable)) {
$baseTable = $table;
}
$query->renameTable($table, $table . '_versions');
$query->replaceText("\"{$table}\".\"ID\"", "\"{$table}\".\"RecordID\"");
$query->replaceText("\"{$table}_versions\".\"ID\"", "\"{$table}_versions\".\"RecordID\"");
if ($table == $baseTable) {
// Add all <basetable>_versions columns
foreach (Versioned::$db_for_versions_table as $name => $type) {
$query->select[] = sprintf('"%s_versions"."%s"', $baseTable, $name);
}
$query->select[] = sprintf('"%s_versions"."%s" AS "ID"', $baseTable, 'RecordID');
}
if ($table != $baseTable) {
$query->from[$table] .= " AND \"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\"";
}
}
// Link to the version archived on that date
$tempTable = $this->requireFutureStateTempTable($baseTable, $datetime);
$query->from[$tempTable] = "INNER JOIN \"{$tempTable}\"\n\t\t\t\tON \"{$tempTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" \n\t\t\t\tAND \"{$tempTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"";
}
}
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
// When filtering my menu, swap out condition for locale specific condition
$locale = Fluent::current_locale();
$field = Fluent::db_field_for_locale("ShowInMenus", $locale);
$query->replaceText("\"{$this->ownerBaseClass}\".\"ShowInMenus\"", "\"{$this->ownerBaseClass}\".\"{$field}\"");
}
/**
* 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;
}
}
function augmentSQL(SQLQuery &$query)
{
// Get the content at a specific date
if ($date = Versioned::$reading_archived_date) {
foreach ($query->from as $table => $dummy) {
if (!isset($baseTable)) {
$baseTable = $table;
}
$query->renameTable($table, $table . '_versions');
$query->replaceText(".ID", ".RecordID");
$query->select[] = "`{$baseTable}_versions`.RecordID AS ID";
if ($table != $baseTable) {
$query->from[$table] .= " AND `{$table}_versions`.Version = `{$baseTable}_versions`.Version";
}
}
// Link to the version archived on that date
$this->requireArchiveTempTable($baseTable, $date);
$query->from["_Archive{$baseTable}"] = "INNER JOIN `_Archive{$baseTable}`\n\t\t\t\tON `_Archive{$baseTable}`.RecordID = `{$baseTable}_versions`.RecordID \n\t\t\t\tAND `_Archive{$baseTable}`.Version = `{$baseTable}_versions`.Version";
// Get a specific stage
} else {
if (Versioned::$reading_stage && Versioned::$reading_stage != $this->defaultStage && array_search(Versioned::$reading_stage, $this->stages) !== false) {
foreach ($query->from as $table => $dummy) {
$query->renameTable($table, $table . '_' . Versioned::$reading_stage);
}
}
}
}
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 augmentSQL(SQLQuery &$query)
{
if (static::enabled()) {
$query->addWhere(self::VerificationDateName . ' is not null');
}
parent::augmentSQL($query);
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if ($dataQuery->getQueryParam('Subsite.filter') === false) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
// if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
if ($query->filtersOnID()) {
return;
}
if (Subsite::$force_subsite) {
$subsiteID = Subsite::$force_subsite;
} else {
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
}
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
// The tableName should be custommenu...
if (strpos($tableName, 'CustomMenu') === false) {
break;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
break;
}
}
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;
}
static function all_votes_count() {
$query = new SQLQuery(
"COUNT(Choice)",
"Vote",
"Choice BETWEEN 1 AND 5");
return $query->execute()->value();
}
/**
* Update any requests to limit the results to the current site
*/
function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if ($dataQuery->getQueryParam('Subsite.filter') === false) {
return;
}
// Don't run on delete queries, since they are always tied to
// a specific ID.
if ($query->getDelete()) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
// if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
if (!$query->where || !preg_match('/\\.(\'|"|`|)ID(\'|"|`|)( ?)=/', $query->where[0])) {
if (Subsite::$force_subsite) {
$subsiteID = Subsite::$force_subsite;
} else {
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
}
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
// The tableName should be SiteTree or SiteTree_Live...
if (strpos($tableName, $this->owner->ClassName) === false) {
break;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
break;
}
}
}
public function apply(SQLQuery $query) {
return $query->where(sprintf(
"%s != '%s'",
$this->getDbName(),
Convert::raw2sql($this->getValue())
));
}
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
// Actives locales defined on a SiteConfig are there as a global setting
if ($this->owner instanceof SiteConfig) {
return;
}
// In admin, show everthing anyway
if ($this->isAdminBackend()) {
return;
}
// Find in set is only compatible with MySql
$c = DB::getConn();
if (!$c instanceof MySQLDatabase) {
return;
}
$locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale();
$from = $query->getFrom();
$where = $query->getWhere();
$column = 'ActiveLocales';
$table = null;
// Check on which table is the ActiveLocales field
foreach ($from as $fromTable => $conditions) {
if ($table === null) {
$table = $fromTable;
}
$db = DataObject::custom_database_fields($fromTable);
if ($db && isset($db[$column])) {
$table = $fromTable;
break;
}
}
$identifier = "\"{$table}\".\"{$column}\"";
$where[] = "{$identifier} IS NULL OR FIND_IN_SET ('{$locale}', {$identifier}) > 0";
$query->setWhere($where);
}
/**
* Augment queries so that we don't fetch unpublished articles.
**/
public function augmentSQL(SQLQuery &$query)
{
$stage = Versioned::current_stage();
if ($stage == 'Live' || !Permission::check("VIEW_DRAFT_CONTENT")) {
$query->addWhere("PublishDate < '" . Convert::raw2sql(SS_Datetime::now()) . "'");
}
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
$ctrl = null;
if (Controller::has_curr()) {
$ctrl = Controller::curr();
}
if (Subsite::$disable_subsite_filter) {
return;
}
if ($dataQuery->getQueryParam('Subsite.filter') === false) {
return;
}
if ($ctrl && get_class(Controller::curr()) == 'Security') {
return;
}
// Don't run on delete queries, since they are always tied to
// a specific ID.
if ($query->getDelete()) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
// if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
if (!$query->filtersOnID()) {
if (Subsite::$force_subsite) {
$subsiteID = Subsite::$force_subsite;
} else {
$subsiteID = (int) Subsite::currentSubsiteID();
}
$froms = $query->getFrom();
$froms = array_keys($froms);
$tableName = array_shift($froms);
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
}
}
/**
* 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);
}
/**
* @param SQLQuery $query
* @param DataQuery $dataQuery
*/
function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
$baseTable = ClassInfo::baseDataClass($dataQuery->dataClass());
if (class_exists('Subsite')) {
$currentSubsiteID = Subsite::currentSubsiteID();
$query->addWhere("\"{$baseTable}\".\"SubsiteID\" = '{$currentSubsiteID}'");
}
}
/**
* 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;
}
}
function apply(SQLQuery $query) {
$query->where(sprintf(
"%s >= '%s' AND %s < '%s'",
$this->getDbName(),
Convert::raw2sql($this->min),
$this->getDbName(),
Convert::raw2sql($this->max)
));
}
/**
* @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->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) {
// 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;
}
}
/**
* Augment queries so that we don't fetch unpublished articles.
*
* @param SQLQuery $query
*/
public function augmentSQL(SQLQuery &$query)
{
$stage = Versioned::current_stage();
if (Controller::curr() instanceof LeftAndMain) {
return;
}
if ($stage == 'Live' || !Permission::check('VIEW_DRAFT_CONTENT')) {
$query->addWhere(sprintf('"PublishDate" < \'%s\'', Convert::raw2sql(SS_Datetime::now())));
}
}
/**
* @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;
}
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;
}
/**
* Determines if the current query is supposed
* to be exempt from the automatic filtering out
* of temporary records.
*
* @param SQLQuery $query
* @return boolean
*/
protected function wantsTemporary($query)
{
foreach ($query->getWhere() as $whereClause) {
$from = array_values($query->getFrom());
// SQLQuery will automatically add double quotes and single quotes to values, so check against that.
if ($whereClause == "{$from[0]}.\"MultiFormIsTemporary\" = '1'") {
return true;
}
}
return false;
}
