/**
* Apply price rule price to price index table
*
* @param Varien_Db_Select $select
* @param array|string $indexTable
* @param string $entityId
* @param string $customerGroupId
* @param string $websiteId
* @param array $updateFields the array of fields for compare with rule price and update
* @param string $websiteDate
* @return Mage_CatalogRule_Model_Resource_Rule_Product_Price
*/
public function applyPriceRuleToIndexTable(Varien_Db_Select $select, $indexTable, $entityId, $customerGroupId, $websiteId, $updateFields, $websiteDate)
{
if (empty($updateFields)) {
return $this;
}
if (is_array($indexTable)) {
foreach ($indexTable as $k => $v) {
if (is_string($k)) {
$indexAlias = $k;
} else {
$indexAlias = $v;
}
break;
}
} else {
$indexAlias = $indexTable;
}
$select->join(array('rp' => $this->getMainTable()), "rp.rule_date = {$websiteDate}", array())->where("rp.product_id = {$entityId} AND rp.website_id = {$websiteId} AND rp.customer_group_id = {$customerGroupId}");
foreach ($updateFields as $priceField) {
$priceCond = $this->_getWriteAdapter()->quoteIdentifier(array($indexAlias, $priceField));
$priceExpr = $this->_getWriteAdapter()->getCheckSql("rp.rule_price < {$priceCond}", 'rp.rule_price', $priceCond);
$select->columns(array($priceField => $priceExpr));
}
$query = $select->crossUpdateFromSelect($indexTable);
$this->_getWriteAdapter()->query($query);
return $this;
}
/**
* @param array|string $attributes
* @param Varien_Db_Select $select
* @param string $mainCorrelation
* @param string $mainIdColumn
* @return $this
*/
public function includeProductAttribute($attributes, $select, $mainCorrelation = 'main_table', $mainIdColumn = null, $joinTableAlias = null, $resourceName = null)
{
$attributes = $this->_loadAttributes($attributes);
$mainIdColumn = $mainIdColumn ?: 'entity_id';
$joinTableAlias = $joinTableAlias ?: 'product_%s_table';
foreach ($attributes as $attributeCode) {
/** @var Mage_Eav_Model_Entity_Attribute $attribute */
if (is_string($attributeCode)) {
$attribute = $this->_getCachedAttribute($attributeCode);
} else {
$attribute = $attributeCode;
}
$attributeCode = $attribute->getAttributeCode();
if ($attribute->getId()) {
if ($attribute->getBackendType() == 'static') {
$select->columns($attribute->getAttributeCode());
} else {
$joinTable = sprintf($joinTableAlias, $attributeCode);
$select->joinLeft(array($joinTable => $attribute->getBackendTable()), sprintf('`%s`.%s = `%s`.entity_id AND `%s`.attribute_id = %s', $mainCorrelation, $mainIdColumn, $joinTable, $joinTable, $attribute->getAttributeId()), array($attributeCode => new Zend_Db_Expr(sprintf('`%s`.value', $joinTable))));
}
}
}
return $this;
}
/**
* Adds Columns prepared for union
*
* @param Varien_Db_Select $select
* @param string $table
* @param string $type
* @return Varien_Db_Select
*/
protected function _addLoadAttributesSelectFields($select, $table, $type)
{
$select->columns(Mage::getResourceHelper('catalog')->attributeSelectFields('attr_table', $type));
return $select;
}
/**
* Join information for usin full text search
*
* @param Varien_Db_Select $select
* @return Varien_Db_Select $select
*/
public function chooseFulltext($table, $alias, $select)
{
$field = new Zend_Db_Expr('MATCH (' . $alias . '.data_index) AGAINST (:query IN BOOLEAN MODE)');
$select->columns(array('relevance' => $field));
return $field;
}
/**
* Adds order by random to select object
* Possible using integer field for optimization
*
* @param Varien_Db_Select $select
* @param string $field
* @return Varien_Db_Adapter_Pdo_Mysql
*/
public function orderRand(Varien_Db_Select $select, $field = null)
{
if ($field !== null) {
$expression = new Zend_Db_Expr(sprintf('RAND() * %s', $this->quoteIdentifier($field)));
$select->columns(array('mage_rand' => $expression));
$spec = new Zend_Db_Expr('mage_rand');
} else {
$spec = new Zend_Db_Expr('RAND()');
}
$select->order($spec);
return $this;
}
/**
* @param Varien_Db_Select $select
* @param string $table
* @param string $type
* @return Varien_Db_Select
*/
protected function _addLoadAttributesSelectValues($select, $table, $type)
{
$helper = Mage::getResourceHelper('eav');
$select->columns(array('value' => $helper->prepareEavAttributeValue($table . '.value', $type)));
return $select;
}
/**
* @param Varien_Db_Select $select
* @param string $table
* @param string $type
* @return Varien_Db_Select
*/
protected function _addLoadAttributesSelectValues($select, $table, $type)
{
$storeId = $this->getStoreId();
if ($storeId) {
$helper = Mage::getResourceHelper('eav');
$adapter = $this->getConnection();
$valueExpr = $adapter->getCheckSql(
't_s.value_id IS NULL',
$helper->prepareEavAttributeValue('t_d.value', $type),
$helper->prepareEavAttributeValue('t_s.value', $type)
);
$select->columns(array(
'default_value' => $helper->prepareEavAttributeValue('t_d.value', $type),
'store_value' => $helper->prepareEavAttributeValue('t_s.value', $type),
'value' => $valueExpr
));
} else {
$select = parent::_addLoadAttributesSelectValues($select, $table, $type);
}
return $select;
}
/**
* Enter description here ...
* @param Varien_Db_Select $select
* @param string $column
*/
public function selectColumn($select, $column)
{
list($expr, $alias) = explode(' AS ', $column);
foreach ($select->getPart(Zend_Db_Select::COLUMNS) as $columnInfo) {
if ($columnInfo[2] == $alias) {
return $this;
}
}
$select->columns($column);
return $this;
}
/**
* Add prepared column group_concat expression
*
* @param Varien_Db_Select $select
* @param string $fieldAlias Field alias which will be added with column group_concat expression
* @param string $fields
* @param string $groupConcatDelimiter
* @param string $fieldsDelimiter
* @param string $additionalWhere
* @return Varien_Db_Select
*/
public function addGroupConcatColumn($select, $fieldAlias, $fields, $groupConcatDelimiter = ',', $fieldsDelimiter = '', $additionalWhere = '')
{
if (is_array($fields)) {
$fieldExpr = $this->_getReadAdapter()->getConcatSql($fields, $fieldsDelimiter);
} else {
$fieldExpr = $fields;
}
if ($additionalWhere) {
$fieldExpr = $this->_getReadAdapter()->getCheckSql($additionalWhere, $fieldExpr, "''");
}
$separator = '';
if ($groupConcatDelimiter) {
$separator = sprintf(" SEPARATOR '%s'", $groupConcatDelimiter);
}
$select->columns(array($fieldAlias => new Zend_Db_Expr(sprintf('GROUP_CONCAT(%s%s)', $fieldExpr, $separator))));
return $select;
}
public function addLinkUrlToFlatCatalogSelect(Varien_Db_Select $select)
{
$select->columns(array('url' => VinaiKopp_CategoryLink_Helper_Data::ATTR_CODE_LINK));
}
/**
* FastIndexer
* Keep method private because of the last two args which can be used for SQL injections
*
* @param Varien_Db_Select $select
* @param string $attributeCode
* @param int $storeId
* @param string $sqlCompare
* @param string|int $attributeValue
* @param bool $addAttributeToColumn
*/
private function _addProductAttributeToSelect(Varien_Db_Select $select, $attributeCode, $storeId, $sqlCompare = null, $attributeValue = null, $addAttributeToColumn = true)
{
$adapter = $this->_getReadAdapter();
/** @var $adapter SchumacherFM_FastIndexer_Model_Db_Adapter_Pdo_Mysql */
if (!isset($this->_productAttributes[$attributeCode])) {
$attribute = $this->getProductModel()->getResource()->getAttribute($attributeCode);
$this->_productAttributes[$attributeCode] = array('entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal());
unset($attribute);
}
$tableAlias = 'tj' . $attributeCode;
$tAttrId = (int) $this->_productAttributes[$attributeCode]['attribute_id'];
$tTypeId = (int) $this->_productAttributes[$attributeCode]['entity_type_id'];
if (1 === (int) $this->_productAttributes[$attributeCode]['is_global'] || $storeId == 0) {
$joinColumns = array($tableAlias . '.entity_type_id = ' . $tTypeId, $tableAlias . '.store_id = 0', $tableAlias . '.attribute_id = ' . $tAttrId, 'e.entity_id = ' . $tableAlias . '.entity_id');
if (null !== $sqlCompare && null !== $attributeValue) {
$joinColumns[] = $tableAlias . '.value ' . $sqlCompare . ' ' . $attributeValue;
}
$select->join(array($tableAlias => $this->_productAttributes[$attributeCode]['table']), implode(' AND ', $joinColumns), array());
if (true === $addAttributeToColumn) {
$select->columns(array($attributeCode => $tableAlias . '.value'));
}
} else {
$t1 = 't1' . $tableAlias;
$t2 = 't2' . $tableAlias;
$valueExpr = $adapter->getCheckSql('IFNULL(' . $t2 . '.value_id,0) > 0', $t2 . '.value', $t1 . '.value');
$select->join(array($t1 => $this->_productAttributes[$attributeCode]['table']), 'e.entity_id = ' . $t1 . '.entity_id AND ' . $t1 . '.store_id = 0 AND ' . $t1 . '.attribute_id = ' . $tAttrId, array())->joinLeft(array($t2 => $this->_productAttributes[$attributeCode]['table']), $t1 . '.entity_id = ' . $t2 . '.entity_id AND ' . $t1 . '.attribute_id = ' . $t2 . '.attribute_id AND ' . $t2 . '.store_id = ' . $storeId, array());
if (true === $addAttributeToColumn) {
$select->columns(array($attributeCode => $valueExpr));
}
if (null !== $sqlCompare && null !== $attributeValue) {
$select->where($valueExpr . ' ' . $sqlCompare . ' ' . $attributeValue);
}
}
}
