/**
* @magentoDataFixture Magento/Bundle/_files/product.php
* @covers \Magento\Indexer\Model\Indexer::reindexAll
* @covers \Magento\Bundle\Model\Product\Type::getSearchableData
*/
public function testPrepareProductIndexForBundleProduct()
{
$this->indexer->reindexAll();
$select = $this->connectionMock->select()->from($this->resource->getTableName('catalogsearch_fulltext_scope1'))->where('`data_index` LIKE ?', '%' . 'Bundle Product Items' . '%');
$result = $this->connectionMock->fetchAll($select);
$this->assertCount(1, $result);
}
public function getLotsByProductId($prodId, $stockId)
{
/** @var \Magento\Framework\DB\Adapter\AdapterInterface $conn */
$conn = $this->_repoGeneric->getConnection();
/* aliases and tables */
$asStockItem = 'csi';
$asQty = 'pwq';
$asLot = 'pwl';
$tblStockItem = [$asStockItem => $this->_resource->getTableName(Cfg::ENTITY_MAGE_CATALOGINVENTORY_STOCK_ITEM)];
$tblQty = [$asQty => $this->_resource->getTableName(Quantity::ENTITY_NAME)];
$tblLot = [$asLot => $this->_resource->getTableName(Lot::ENTITY_NAME)];
/* SELECT FROM cataloginventory_stock_item */
$query = $conn->select();
$cols = [Alias::AS_STOCK_ITEM_ID => Cfg::E_CATINV_STOCK_ITEM_A_ITEM_ID];
$query->from($tblStockItem, $cols);
/* LEFT JOIN prxgt_wrhs_qty pwq */
$on = $asQty . '.' . Quantity::ATTR_STOCK_ITEM_REF . '=' . $asStockItem . '.' . Cfg::E_CATINV_STOCK_ITEM_A_ITEM_ID;
$cols = [Alias::AS_QTY => Quantity::ATTR_TOTAL];
$query->joinLeft($tblQty, $on, $cols);
// LEFT JOIN prxgt_wrhs_lot pwl
$on = $asLot . '.' . Lot::ATTR_ID . '=' . $asQty . '.' . Quantity::ATTR_LOT_REF;
$cols = [Alias::AS_LOT_ID => Lot::ATTR_ID, Alias::AS_LOT_CODE => Lot::ATTR_CODE, Alias::AS_LOT_EXP_DATE => Lot::ATTR_EXP_DATE];
$query->joinLeft($tblLot, $on, $cols);
/* where */
$where = $asStockItem . '.' . Cfg::E_CATINV_STOCK_ITEM_A_PROD_ID . '=' . (int) $prodId;
$where .= ' AND ' . $asStockItem . '.' . Cfg::E_CATINV_STOCK_ITEM_A_STOCK_ID . '=' . (int) $stockId;
$query->where($where);
/* order by */
$order = $asLot . '.' . Lot::ATTR_EXP_DATE . ' ASC';
$query->order($order);
/* fetch data */
$result = $conn->fetchAll($query);
return $result;
}
/**
* Add JOINs to original select.
*
* @param \Magento\Framework\DB\Select $select
* @return \Magento\Framework\DB\Select
*/
public function modifySelect(\Magento\Framework\DB\Select $select)
{
/* aliases for tables ... */
$tblEntity = 'e';
// this is alias for 'catalog_product_entity' table
$tblStockItem = $this->_resource->getTableName(self::TBL_STOCK_ITEM);
$tblWrhsQty = $this->_resource->getTableName(self::TBL_WRHS_QTY);
/* ... and fields */
$fldStockItemProdId = StockItem::PRODUCT_ID;
$fldStockItemId = StockItem::ITEM_ID;
$fldEntityId = \Magento\Eav\Model\Entity::DEFAULT_ENTITY_ID_FIELD;
$fldQty = self::FLD_QTY;
$fldStockItemRef = Quantity::ATTR_STOCK_ITEM_REF;
/* LEFT JOIN `cataloginventory_stock_item` */
$on = "`{$tblStockItem}`.`{$fldStockItemProdId}`=`{$tblEntity}`.`{$fldEntityId}`";
$fields = [];
$select->joinLeft($tblStockItem, $on, $fields);
/* LEFT JOIN `prxgt_wrhs_qty` */
$on = "`{$tblWrhsQty}`.`{$fldStockItemRef}`=`{$tblStockItem}`.`{$fldStockItemId}`";
$fields = [$fldQty => $this->getEquationQty()];
$select->joinLeft($tblWrhsQty, $on, $fields);
/* GROUP BY */
$select->group("{$tblEntity}.{$fldEntityId}");
return $select;
}
/**
* {@inheritdoc}
*/
public function build($productId)
{
$timestamp = $this->localeDate->scopeTimeStamp($this->storeManager->getStore());
$currentDate = $this->dateTime->formatDate($timestamp, false);
$linkField = $this->metadataPool->getMetadata(ProductInterface::class)->getLinkField();
$productTable = $this->resource->getTableName('catalog_product_entity');
return [$this->resource->getConnection()->select()->from(['parent' => $productTable], '')->joinInner(['link' => $this->resource->getTableName('catalog_product_relation')], "link.parent_id = parent.{$linkField}", [])->joinInner(['child' => $productTable], "child.entity_id = link.child_id", ['entity_id'])->joinInner(['t' => $this->resource->getTableName('catalogrule_product_price')], 't.product_id = child.entity_id', [])->where('parent.entity_id = ? ', $productId)->where('t.website_id = ?', $this->storeManager->getStore()->getWebsiteId())->where('t.customer_group_id = ?', $this->customerSession->getCustomerGroupId())->where('t.rule_date = ?', $currentDate)->order('t.rule_price ' . Select::SQL_ASC)->limit(1)];
}
/**
* Get table name (validated by db adapter) by table placeholder
*
* @param string|array $tableName
* @return string
*/
public function getTable($tableName)
{
$cacheKey = $this->_getTableCacheName($tableName);
if (!isset($this->tables[$cacheKey])) {
$this->tables[$cacheKey] = $this->resourceModel->getTableName($tableName);
}
return $this->tables[$cacheKey];
}
public function doInsert()
{
$tbl = $this->_resource->getTableName(Account::ENTITY_NAME);
$bind = [Account::ATTR_CUST_ID => 1, Account::ATTR_ASSET_TYPE_ID => 2, Account::ATTR_BALANCE => 123.45];
$this->_conn->insert($tbl, $bind);
$result = $this->_conn->lastInsertId($tbl);
return $result;
}
public function populateSelect(\Magento\Sales\Model\ResourceModel\Order\Grid\Collection $collection)
{
$select = $collection->getSelect();
/* LEFT JOIN `prxgt_pv_sale` */
$tbl = [self::AS_TBL_PV_SALES => $this->_resource->getTableName(Sale::ENTITY_NAME)];
$on = self::AS_TBL_PV_SALES . '.' . Sale::ATTR_SALE_ID . '=main_table.' . Cfg::E_SALE_ORDER_A_ENTITY_ID;
$cols = [self::AS_FLD_PV_TOTAL => Sale::ATTR_TOTAL, self::AS_FLD_PV_DISCOUNT => Sale::ATTR_DISCOUNT, self::AS_FLD_PV_SUBTOTAL => Sale::ATTR_SUBTOTAL];
$select->joinLeft($tbl, $on, $cols);
}
/**
* @magentoDataFixture Magento/Review/_files/customer_review_with_rating.php
*/
public function testAggregate()
{
$rating = $this->reviewCollection->getFirstItem();
$this->reviewResource->aggregate($rating);
$select = $this->connection->select()->from($this->resource->getTableName('review_entity_summary'));
$result = $this->connection->fetchRow($select);
$this->assertEquals(1, $result['reviews_count']);
$this->assertEquals(40, $result['rating_summary']);
}
/**
* @param array $ids
* @return array
*/
public function getIds(array $ids)
{
$key = md5(json_encode($ids));
if (!isset($this->productIds[$key])) {
$connection = $this->resource->getConnection();
$this->productIds[$key] = $connection->fetchCol($connection->select()->from(['e' => $this->resource->getTableName('catalog_product_entity')], ['e.entity_id'])->where('e.type_id = ?', \Magento\ConfigurableProduct\Model\Product\Type\Configurable::TYPE_CODE)->where('e.entity_id IN (?)', $ids));
}
return $this->productIds[$key];
}
/**
* @param Collection $productCollection
* @param bool $printQuery
* @param bool $logQuery
* @return array
*/
public function beforeLoad(Collection $productCollection, $printQuery = false, $logQuery = false)
{
if (!$productCollection->hasFlag('catalog_rule_loaded')) {
$connection = $this->resource->getConnection();
$store = $this->storeManager->getStore();
$productCollection->getSelect()->joinLeft(['catalog_rule' => $this->resource->getTableName('catalogrule_product_price')], implode(' AND ', ['catalog_rule.product_id = e.entity_id', $connection->quoteInto('catalog_rule.website_id = ?', $store->getWebsiteId()), $connection->quoteInto('catalog_rule.customer_group_id = ?', $this->customerSession->getCustomerGroupId()), $connection->quoteInto('catalog_rule.rule_date = ?', $this->dateTime->formatDate($this->localeDate->scopeDate($store->getId()), false))]), [CatalogRulePrice::PRICE_CODE => 'rule_price']);
$productCollection->setFlag('catalog_rule_loaded', true);
}
return [$printQuery, $logQuery];
}
public function populateSelect(\Magento\Sales\Model\ResourceModel\Order\Grid\Collection $collection)
{
$select = $collection->getSelect();
/* LEFT JOIN `prxgt_odoo_sale` */
$tbl = [self::AS_TBL_ODOO_SALE => $this->_resource->getTableName(SaleOrder::ENTITY_NAME)];
$on = self::AS_TBL_ODOO_SALE . '.' . SaleOrder::ATTR_MAGE_REF . '=main_table.' . Cfg::E_SALE_ORDER_A_ENTITY_ID;
$exp = new Expression('!ISNULL(' . self::AS_TBL_ODOO_SALE . '.' . SaleOrder::ATTR_MAGE_REF . ')');
$cols = [self::AS_FLD_IS_IN_ODOO => $exp];
$select->joinLeft($tbl, $on, $cols);
return $select;
}
public function aroundGetReport(\Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject, \Closure $proceed, $requestName)
{
$result = $proceed($requestName);
if ($requestName == 'sales_order_grid_data_source') {
if ($result instanceof \Magento\Sales\Model\ResourceModel\Order\Grid\Collection) {
$select = $result->getSelect();
$select->joinLeft(['shipper_order_join' => $this->_resource->getTableName('shipperhq_order_detail_grid')], 'entity_id' . '=shipper_order_join.' . 'order_id', []);
}
}
return $result;
}
public function testGetTableName()
{
$tablePrefix = 'prefix_';
$tableSuffix = 'suffix';
$tableNameOrig = 'store_website';
$this->_model = \Magento\TestFramework\Helper\Bootstrap::getObjectManager()->create('Magento\\Framework\\App\\ResourceConnection', ['tablePrefix' => 'prefix_']);
$tableName = $this->_model->getTableName([$tableNameOrig, $tableSuffix]);
$this->assertContains($tablePrefix, $tableName);
$this->assertContains($tableSuffix, $tableName);
$this->assertContains($tableNameOrig, $tableName);
}
/**
* @return Table
* @throws \Zend_Db_Exception
*/
private function createTemporaryTable()
{
$connection = $this->getConnection();
$tableName = $this->resource->getTableName(str_replace('.', '_', uniqid(self::TEMPORARY_TABLE_PREFIX, true)));
$table = $connection->newTable($tableName);
$connection->dropTemporaryTable($table->getName());
$table->addColumn(self::FIELD_ENTITY_ID, Table::TYPE_INTEGER, 10, ['unsigned' => true, 'nullable' => false, 'primary' => true], 'Entity ID');
$table->addColumn(self::FIELD_SCORE, Table::TYPE_DECIMAL, [32, 16], ['unsigned' => true, 'nullable' => false], 'Score');
$table->setOption('type', 'memory');
$connection->createTemporaryTable($table);
return $table;
}
/**
* @param int $n
* @return void
*/
public function generateSequences($n = 10)
{
$connection = $this->appResource->getConnection();
for ($i = 0; $i < $n; $i++) {
foreach ($this->entities as $entityName) {
$sequenceName = $this->appResource->getTableName(sprintf('sequence_%s_%s', $entityName, $i));
if (!$connection->isTableExists($sequenceName)) {
$connection->query($this->ddlSequence->getCreateSequenceDdl($sequenceName));
}
}
}
}
/**
* @param string $entityType
* @param int $identifier
* @return int
* @throws \Exception
*/
public function delete($entityType, $identifier)
{
$metadata = $this->metadataPool->getMetadata($entityType);
$sequenceInfo = $this->sequenceRegistry->retrieve($entityType);
if (!isset($sequenceInfo['sequenceTable'])) {
throw new \Exception('TODO: use correct Exception class' . PHP_EOL . ' Sequence table doesnt exists');
}
try {
return $metadata->getEntityConnection()->delete($this->appResource->getTableName($sequenceInfo['sequenceTable']), ['sequence_value = ?' => $identifier]);
} catch (\Exception $e) {
$this->logger->critical($e->getMessage(), $e->getTrace());
throw new \Exception('TODO: use correct Exception class' . PHP_EOL . $e->getMessage());
}
}
/**
* {@inheritdoc}
*/
public function build($productId)
{
$linkField = $this->metadataPool->getMetadata(ProductInterface::class)->getLinkField();
$productTable = $this->resource->getTableName('catalog_product_entity');
$priceSelect = $this->resource->getConnection()->select()->from(['parent' => $productTable], '')->joinInner(['link' => $this->resource->getTableName('catalog_product_relation')], "link.parent_id = parent.{$linkField}", [])->joinInner(['child' => $productTable], "child.entity_id = link.child_id", ['entity_id'])->joinInner(['t' => $this->resource->getTableName('catalog_product_entity_tier_price')], "t.{$linkField} = child.{$linkField}", [])->where('parent.entity_id = ? ', $productId)->where('t.all_groups = 1 OR customer_group_id = ?', $this->customerSession->getCustomerGroupId())->where('t.qty = ?', 1)->order('t.value ' . Select::SQL_ASC)->limit(1);
$priceSelectDefault = clone $priceSelect;
$priceSelectDefault->where('t.website_id = ?', self::DEFAULT_WEBSITE_ID);
$select[] = $priceSelectDefault;
if (!$this->catalogHelper->isPriceGlobal()) {
$priceSelect->where('t.website_id = ?', $this->storeManager->getStore()->getWebsiteId());
$select[] = $priceSelect;
}
return $select;
}
/**
* {@inheritdoc}
*/
public function build($productId)
{
$linkField = $this->metadataPool->getMetadata(ProductInterface::class)->getLinkField();
$priceAttribute = $this->eavConfig->getAttribute(Product::ENTITY, 'price');
$productTable = $this->resource->getTableName('catalog_product_entity');
$priceSelect = $this->resource->getConnection()->select()->from(['parent' => $productTable], '')->joinInner(['link' => $this->resource->getTableName('catalog_product_relation')], "link.parent_id = parent.{$linkField}", [])->joinInner(['child' => $productTable], "child.entity_id = link.child_id", ['entity_id'])->joinInner(['t' => $priceAttribute->getBackendTable()], "t.{$linkField} = child.{$linkField}", [])->where('parent.entity_id = ? ', $productId)->where('t.attribute_id = ?', $priceAttribute->getAttributeId())->where('t.value IS NOT NULL')->order('t.value ' . Select::SQL_ASC)->limit(1);
$priceSelectDefault = clone $priceSelect;
$priceSelectDefault->where('t.store_id = ?', Store::DEFAULT_STORE_ID);
$select[] = $priceSelectDefault;
if (!$this->catalogHelper->isPriceGlobal()) {
$priceSelect->where('t.store_id = ?', $this->storeManager->getStore()->getId());
$select[] = $priceSelect;
}
return $select;
}
/**
* @param string $entityType
* @param \Magento\Catalog\Model\AbstractModel $entity
* @param int $storeId
* @throws \Magento\Framework\Exception\LocalizedException
* @return void
*/
private function initAttributeValues($entityType, $entity, $storeId)
{
$metadata = $this->metadataPool->getMetadata($entityType);
/** @var \Magento\Eav\Model\Entity\Attribute\AbstractAttribute $attribute */
$attributeTables = [];
if ($metadata->getEavEntityType()) {
foreach ($this->getAttributes($entityType) as $attribute) {
if (!$attribute->isStatic()) {
$attributeTables[$attribute->getBackend()->getTable()][] = $attribute->getAttributeId();
}
}
$storeIds = [Store::DEFAULT_STORE_ID];
if ($storeId !== Store::DEFAULT_STORE_ID) {
$storeIds[] = $storeId;
}
$selects = [];
foreach ($attributeTables as $attributeTable => $attributeCodes) {
$select = $metadata->getEntityConnection()->select()->from(['t' => $attributeTable], ['value' => 't.value', 'store_id' => 't.store_id'])->join(['a' => $this->resourceConnection->getTableName('eav_attribute')], 'a.attribute_id = t.attribute_id', ['attribute_code' => 'a.attribute_code'])->where($metadata->getLinkField() . ' = ?', $entity->getId())->where('t.attribute_id IN (?)', $attributeCodes)->where('t.store_id IN (?)', $storeIds);
$selects[] = $select;
}
$unionSelect = new \Magento\Framework\DB\Sql\UnionExpression($selects, \Magento\Framework\DB\Select::SQL_UNION_ALL);
$attributes = $metadata->getEntityConnection()->fetchAll((string) $unionSelect);
foreach ($attributes as $attribute) {
$this->attributesValues[$attribute['store_id']][$attribute['attribute_code']] = $attribute['value'];
}
}
}
public function populateSelect(\Magento\Customer\Model\ResourceModel\Grid\Collection $collection)
{
$select = $collection->getSelect();
/* LEFT JOIN `prxgt_dwnl_customer` AS `prxgtDwnlCust` */
$tbl = [self::AS_TBL_CUST => $this->_resource->getTableName(Customer::ENTITY_NAME)];
$on = self::AS_TBL_CUST . '.' . Customer::ATTR_CUSTOMER_ID . '=main_table.' . Cfg::E_CUSTOMER_A_ENTITY_ID;
$cols = [self::AS_FLD_CUSTOMER_REF => Customer::ATTR_HUMAN_REF, self::AS_FLD_CUSTOMER_DEPTH => Customer::ATTR_DEPTH, self::AS_FLD_PARENT_ID => Customer::ATTR_PARENT_ID];
$select->joinLeft($tbl, $on, $cols);
/* LEFT JOIN `prxgt_dwnl_customer` AS `prxgtDwnlParentCust` */
$tbl = [self::AS_TBL_PARENT_CUST => $this->_resource->getTableName(Customer::ENTITY_NAME)];
$on = self::AS_TBL_PARENT_CUST . '.' . Customer::ATTR_CUSTOMER_ID . '=' . self::AS_TBL_CUST . '.' . Customer::ATTR_PARENT_ID;
$cols = [self::AS_FLD_PARENT_REF => Customer::ATTR_HUMAN_REF];
$select->joinLeft($tbl, $on, $cols);
// $sql = (string)$query;
return $select;
}
/**
* Apply diff. between 0 store and current store to temporary flat table
*
* @param array $tables
* @param array $changedIds
* @param int|string $storeId
* @param string $valueFieldSuffix
* @return void
*/
protected function _updateTemporaryTableByStoreValues(array $tables, array $changedIds, $storeId, $valueFieldSuffix)
{
$flatColumns = $this->_productIndexerHelper->getFlatColumns();
$temporaryFlatTableName = $this->_getTemporaryTableName($this->_productIndexerHelper->getFlatTableName($storeId));
$linkField = $this->metadataPool->getMetadata(ProductInterface::class)->getLinkField();
foreach ($tables as $tableName => $columns) {
foreach ($columns as $attribute) {
/* @var $attribute \Magento\Eav\Model\Entity\Attribute */
$attributeCode = $attribute->getAttributeCode();
if ($attribute->getBackend()->getType() != 'static') {
$joinCondition = sprintf('t.%s = e.%s', $linkField, $linkField) . ' AND t.attribute_id=' . $attribute->getId() . ' AND t.store_id = ' . $storeId . ' AND t.value IS NOT NULL';
/** @var $select \Magento\Framework\DB\Select */
$select = $this->_connection->select()->joinInner(['e' => $this->resource->getTableName('catalog_product_entity')], 'e.entity_id = et.entity_id', [])->joinInner(['t' => $tableName], $joinCondition, [$attributeCode => 't.value']);
if (!empty($changedIds)) {
$select->where($this->_connection->quoteInto('et.entity_id IN (?)', $changedIds));
}
$sql = $select->crossUpdateFromSelect(['et' => $temporaryFlatTableName]);
$this->_connection->query($sql);
}
//Update not simple attributes (eg. dropdown)
if (isset($flatColumns[$attributeCode . $valueFieldSuffix])) {
$select = $this->_connection->select()->joinInner(['t' => $this->_productIndexerHelper->getTable('eav_attribute_option_value')], 't.option_id = et.' . $attributeCode . ' AND t.store_id=' . $storeId, [$attributeCode . $valueFieldSuffix => 't.value']);
if (!empty($changedIds)) {
$select->where($this->_connection->quoteInto('et.entity_id IN (?)', $changedIds));
}
$sql = $select->crossUpdateFromSelect(['et' => $temporaryFlatTableName]);
$this->_connection->query($sql);
}
}
}
}
/**
* @param string $entityType
* @param array $entityData
* @return array
* @throws \Exception
* @throws \Magento\Framework\Exception\LocalizedException
*/
public function execute($entityType, $entityData)
{
$data = [];
$metadata = $this->metadataPool->getMetadata($entityType);
/** @var \Magento\Eav\Model\Entity\Attribute\AbstractAttribute $attribute */
$attributeTables = [];
if ($metadata->getEavEntityType()) {
$context = $this->getActionContext($entityType, $entityData);
foreach ($this->getAttributes($entityType) as $attribute) {
if (!$attribute->isStatic()) {
$attributeTables[$attribute->getBackend()->getTable()][] = $attribute->getAttributeId();
}
}
$selects = [];
foreach ($attributeTables as $attributeTable => $attributeCodes) {
$select = $metadata->getEntityConnection()->select()->from(['t' => $attributeTable], ['value' => 't.value'])->join(['a' => $this->appResource->getTableName('eav_attribute')], 'a.attribute_id = t.attribute_id', ['attribute_code' => 'a.attribute_code'])->where($metadata->getLinkField() . ' = ?', $entityData[$metadata->getLinkField()])->where('t.attribute_id IN (?)', $attributeCodes)->order('a.attribute_id');
foreach ($context as $field => $value) {
//TODO: if (in table exists context field)
$select->where($metadata->getEntityConnection()->quoteIdentifier($field) . ' IN (?)', $value)->order('t.' . $field . ' DESC');
}
$selects[] = $select;
}
$unionSelect = new \Magento\Framework\DB\Sql\UnionExpression($selects, \Magento\Framework\DB\Select::SQL_UNION_ALL);
$attributeValues = $metadata->getEntityConnection()->fetchAll((string) $unionSelect);
foreach ($attributeValues as $attributeValue) {
$data[$attributeValue['attribute_code']] = $attributeValue['value'];
}
}
return $data;
}
/**
* Return best match (from database)
*
* @param string $query
* @return array
*/
public function getBestMatch($query)
{
$query = trim($query);
if (!$query) {
return ['keyword' => $query, 'diff' => 100];
}
$len = intval($this->text->strlen($query));
$trigram = $this->text->getTrigram($this->text->strtolower($query));
$tableName = $this->resource->getTableName('mst_misspell_index');
$select = $this->connection->select();
$relevance = '(-ABS(LENGTH(keyword) - ' . $len . ') + MATCH (trigram) AGAINST("' . $trigram . '"))';
$relevancy = new \Zend_Db_Expr($relevance . ' + frequency AS relevancy');
$select->from($tableName, ['keyword', $relevancy, 'frequency'])->order('relevancy desc')->limit(10);
$keywords = $this->connection->fetchAll($select);
$maxFreq = 0.0001;
foreach ($keywords as $keyword) {
$maxFreq = max($keyword['frequency'], $maxFreq);
}
$preResults = [];
foreach ($keywords as $keyword) {
$preResults[$keyword['keyword']] = $this->damerau->similarity($query, $keyword['keyword']) + $keyword['frequency'] * (10 / $maxFreq);
}
arsort($preResults);
$keys = array_keys($preResults);
if (count($keys) > 0) {
$keyword = $keys[0];
$keyword = $this->toSameRegister($keyword, $query);
$diff = $preResults[$keys[0]];
$result = ['keyword' => $keyword, 'diff' => $diff];
} else {
$result = ['keyword' => $query, 'diff' => 100];
}
return $result;
}
/**
* Process rule on collection.
*
* @param $collection
* @param $type
* @param $websiteId
*
* @return mixed
*/
public function process($collection, $type, $websiteId)
{
$rule = $this->getActiveRuleForWebsite($type, $websiteId);
//if no rule then return the collection untouched
if (empty($rule)) {
return $collection;
}
//@codingStandardsIgnoreStart
//if rule has no conditions then return the collection untouched
$condition = unserialize($rule->getCondition());
//@codingStandardsIgnoreEnd
if (empty($condition)) {
return $collection;
}
//join tables to collection according to type
if ($type == self::ABANDONED) {
$collection->getSelect()->joinLeft(['quote_address' => $this->coreResource->getTableName('quote_address')], 'main_table.entity_id = quote_address.quote_id', ['shipping_method', 'country_id', 'city', 'region_id'])->joinLeft(['quote_payment' => $this->coreResource->getTableName('quote_payment')], 'main_table.entity_id = quote_payment.quote_id', ['method'])->where('address_type = ?', 'shipping');
} elseif ($type == self::REVIEW) {
$collection->getSelect()->join(['order_address' => $this->coreResource->getTableName('sales_order_address')], 'main_table.entity_id = order_address.parent_id', ['country_id', 'city', 'region_id'])->join(['order_payment' => $this->coreResource->getTableName('sales_order_payment')], 'main_table.entity_id = order_payment.parent_id', ['method'])->join(['quote' => $this->coreResource->getTableName('quote')], 'main_table.quote_id = quote.entity_id', ['items_qty'])->where('order_address.address_type = ?', 'shipping');
}
//process rule on collection according to combination
$combination = $rule->getCombination();
// ALL TRUE
if ($combination == 1) {
return $this->_processAndCombination($collection, $condition, $type);
}
//ANY TRUE
if ($combination == 2) {
return $this->processOrCombination($collection, $condition, $type);
}
}
/**
* Export reviews for website.
*
* @param \Magento\Store\Model\Website $website
*/
public function _exportReviewsForWebsite(\Magento\Store\Model\Website $website)
{
$limit = $this->helper->getWebsiteConfig(\Dotdigitalgroup\Email\Helper\Config::XML_PATH_CONNECTOR_TRANSACTIONAL_DATA_SYNC_LIMIT, $website);
$emailReviews = $this->_getReviewsToExport($website, $limit);
$this->reviewIds = [];
if ($emailReviews->getSize()) {
$reviews = $this->mageReviewCollection->create()->addFieldToFilter('main_table.review_id', ['in' => $emailReviews->getColumnValues('review_id')])->addFieldToFilter('customer_id', ['notnull' => 'true']);
$reviews->getSelect()->joinLeft(['c' => $this->coreResource->getTableName('customer_entity')], 'c.entity_id = customer_id', ['email', 'store_id']);
foreach ($reviews as $mageReview) {
try {
$product = $this->productFactory->create()->getCollection()->addIdFilter($mageReview->getEntityPkValue())->setStoreId($mageReview->getStoreId())->addAttributeToSelect(['product_url', 'name', 'store_id', 'small_image'])->setPage(1, 1)->getFirstItem();
$connectorReview = $this->connectorReviewFactory->create()->setReviewData($mageReview)->setProduct($product);
$votesCollection = $this->vote->getResourceCollection()->setReviewFilter($mageReview->getReviewId());
$votesCollection->getSelect()->join(['rating' => 'rating'], 'rating.rating_id = main_table.rating_id', ['rating_code' => 'rating.rating_code']);
foreach ($votesCollection as $ratingItem) {
$rating = $this->ratingFactory->create()->setRating($ratingItem);
$connectorReview->createRating($ratingItem->getRatingCode(), $rating);
}
$this->reviews[$website->getId()][] = $connectorReview;
$this->reviewIds[] = $mageReview->getReviewId();
} catch (\Exception $e) {
$this->helper->debug((string) $e, []);
}
}
}
}
public function aroundCreate(\Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $subject, \Closure $proceed, array $data = [])
{
$result = $proceed($data);
if ($result instanceof \Magento\Catalog\Model\ResourceModel\Product\Collection) {
$query = $result->getSelect();
/* LEFT JOIN `prxgt_pv_prod` AS `prxgtPvProd` */
$tbl = [self::AS_TBL_PROD_PV => $this->_resource->getTableName(Product::ENTITY_NAME)];
$on = self::AS_TBL_PROD_PV . '.' . Product::ATTR_PROD_REF . '=e.entity_id';
$cols = [self::AS_FLD_PV => Product::ATTR_PV];
$query->joinLeft($tbl, $on, $cols);
/* add fields mapping */
$result->addFilterToMap(self::AS_FLD_PV, self::FULL_PV);
$result->addFilterToMap('`e`.`' . self::AS_FLD_PV . '`', self::FULL_PV);
}
return $result;
}
/**
* Return timestamp for the first transaction related to PV.
*/
public function getFirstDateForPvTransactions()
{
$asAcc = 'paa';
$asTrans = 'pat';
$asType = 'pata';
$tblAcc = $this->_resource->getTableName(Account::ENTITY_NAME);
$tblTrans = $this->_resource->getTableName(Transaction::ENTITY_NAME);
$tblType = $this->_resource->getTableName(TypeAsset::ENTITY_NAME);
// SELECT FROM prxgt_acc_transaction pat
$query = $this->_conn->select();
$query->from([$asTrans => $tblTrans], [Transaction::ATTR_DATE_APPLIED]);
// LEFT JOIN prxgt_acc_account paa ON paa.id = pat.debit_acc_id
$on = $asAcc . '.' . Account::ATTR_ID . '=' . $asTrans . '.' . Transaction::ATTR_DEBIT_ACC_ID;
$query->join([$asAcc => $tblAcc], $on, null);
// LEFT JOIN prxgt_acc_type_asset pata ON paa.asset_type_id = pata.id
$on = $asAcc . '.' . Account::ATTR_ASSET_TYPE_ID . '=' . $asType . '.' . TypeAsset::ATTR_ID;
$query->join([$asType => $tblType], $on, null);
// WHERE
$where = $asType . '.' . TypeAsset::ATTR_CODE . '=' . $this->_conn->quote(Cfg::CODE_TYPE_ASSET_PV);
$query->where($where);
// ORDER & LIMIT
$query->order($asTrans . '.' . Transaction::ATTR_DATE_APPLIED . ' ASC');
$query->limit(1);
// $sql = (string)$query;
$result = $this->_conn->fetchOne($query);
return $result;
}
/**
* Obtain select for categories with attributes.
* By default everything from entity table is selected
* + name, is_active and is_anchor
* Also the correct product_count is selected, depending on is the category anchor or not.
*
* @param bool $sorted
* @param array $optionalAttributes
* @return \Magento\Framework\DB\Select
*/
protected function _createCollectionDataSelect($sorted = true, $optionalAttributes = [])
{
$meta = $this->metadataPool->getMetadata(CategoryInterface::class);
$linkField = $meta->getLinkField();
$select = $this->_getDefaultCollection($sorted ? $this->_orderField : false)->getSelect();
// add attributes to select
$attributes = ['name', 'is_active', 'is_anchor'];
if ($optionalAttributes) {
$attributes = array_unique(array_merge($attributes, $optionalAttributes));
}
$resource = $this->_catalogCategory;
foreach ($attributes as $attributeCode) {
/* @var $attribute \Magento\Eav\Model\Entity\Attribute */
$attribute = $resource->getAttribute($attributeCode);
// join non-static attribute table
if (!$attribute->getBackend()->isStatic()) {
$tableDefault = sprintf('d_%s', $attributeCode);
$tableStore = sprintf('s_%s', $attributeCode);
$valueExpr = $this->_conn->getCheckSql("{$tableStore}.value_id > 0", "{$tableStore}.value", "{$tableDefault}.value");
$select->joinLeft([$tableDefault => $attribute->getBackend()->getTable()], sprintf('%1$s.' . $linkField . '=e.' . $linkField . ' AND %1$s.attribute_id=%2$d AND %1$s.store_id=%3$d', $tableDefault, $attribute->getId(), \Magento\Store\Model\Store::DEFAULT_STORE_ID), [$attributeCode => 'value'])->joinLeft([$tableStore => $attribute->getBackend()->getTable()], sprintf('%1$s.' . $linkField . '=e.' . $linkField . ' AND %1$s.attribute_id=%2$d AND %1$s.store_id=%3$d', $tableStore, $attribute->getId(), $this->getStoreId()), [$attributeCode => $valueExpr]);
}
}
// count children products qty plus self products qty
$categoriesTable = $this->_coreResource->getTableName('catalog_category_entity');
$categoriesProductsTable = $this->_coreResource->getTableName('catalog_category_product');
$subConcat = $this->_conn->getConcatSql(['e.path', $this->_conn->quote('/%')]);
$subSelect = $this->_conn->select()->from(['see' => $categoriesTable], null)->joinLeft(['scp' => $categoriesProductsTable], 'see.entity_id=scp.category_id', ['COUNT(DISTINCT scp.product_id)'])->where('see.entity_id = e.entity_id')->orWhere('see.path LIKE ?', $subConcat);
$select->columns(['product_count' => $subSelect]);
$subSelect = $this->_conn->select()->from(['cp' => $categoriesProductsTable], 'COUNT(cp.product_id)')->where('cp.category_id = e.entity_id');
$select->columns(['self_product_count' => $subSelect]);
return $select;
}
/**
* Get quote table description.
*
* @return array
*/
public function getQuoteTableDescription()
{
$quoteTable = $this->adapter->getTableName('quote');
$adapter = $this->adapter->getConnection('read');
$columns = $adapter->describeTable($quoteTable);
return $columns;
}
/**
* Returns the list of tables which data should not be backed up
*
* @return string[]
*/
public function getIgnoreDataTablesList()
{
$result = [];
foreach ($this->_ignoreDataTablesList as $table) {
$result[] = $this->_resource->getTableName($table);
}
return $result;
}
