/**
* @param array $linksData
* @return void
*/
public function saveLinksData($linksData)
{
$mainTable = $this->productLink->getMainTable();
$relationTable = $this->productLink->getTable('catalog_product_relation');
// save links and relations
if ($linksData['product_ids']) {
$this->deleteOldLinks(array_keys($linksData['product_ids']));
$mainData = [];
foreach ($linksData['relation'] as $productData) {
$mainData[] = ['product_id' => $productData['parent_id'], 'linked_product_id' => $productData['child_id'], 'link_type_id' => $this->getLinkTypeId()];
}
$this->connection->insertOnDuplicate($mainTable, $mainData);
$this->connection->insertOnDuplicate($relationTable, $linksData['relation']);
}
$attributes = $this->getAttributes();
// save positions and default quantity
if ($linksData['attr_product_ids']) {
$savedData = $this->connection->fetchPairs($this->connection->select()->from($mainTable, [new \Zend_Db_Expr('CONCAT_WS(" ", product_id, linked_product_id)'), 'link_id'])->where('product_id IN (?) AND link_type_id = ' . $this->connection->quote($this->getLinkTypeId()), array_keys($linksData['attr_product_ids'])));
foreach ($savedData as $pseudoKey => $linkId) {
if (isset($linksData['position'][$pseudoKey])) {
$linksData['position'][$pseudoKey]['link_id'] = $linkId;
}
if (isset($linksData['qty'][$pseudoKey])) {
$linksData['qty'][$pseudoKey]['link_id'] = $linkId;
}
}
if (!empty($linksData['position'])) {
$this->connection->insertOnDuplicate($attributes['position']['table'], $linksData['position']);
}
if (!empty($linksData['qty'])) {
$this->connection->insertOnDuplicate($attributes['qty']['table'], $linksData['qty']);
}
}
}
/**
* Method for FULLTEXT search in Mysql, will generated MATCH ($columns) AGAINST ('$expression' $mode)
*
* @param string|string[] $columns Columns which add to MATCH ()
* @param string $expression Expression which add to AGAINST ()
* @param string $mode
* @return string
*/
public function getMatchQuery($columns, $expression, $mode = self::FULLTEXT_MODE_NATURAL)
{
if (is_array($columns)) {
$columns = implode(', ', $columns);
}
$expression = $this->connection->quote($expression);
$condition = self::MATCH . " ({$columns}) " . self::AGAINST . " ({$expression} {$mode})";
return $condition;
}
private function generateCondition($field, $from, $to, AdapterInterface $adapter)
{
$hasFromValue = !is_null($from);
$hasToValue = !is_null($to);
$condition = '';
if ($hasFromValue and $hasToValue) {
$condition = sprintf('%s >= %s %s %s < %s', $field, $adapter->quote($from), \Zend_Db_Select::SQL_AND, $field, $adapter->quote($to));
} elseif ($hasFromValue and !$hasToValue) {
$condition = sprintf('%s >= %s', $field, $adapter->quote($from));
} elseif (!$hasFromValue and $hasToValue) {
$condition = sprintf('%s < %s', $field, $adapter->quote($to));
}
return $condition;
}
/**
* Populate the temporary category tree index table
*
* @param string $temporaryName
*/
protected function fillTempCategoryTreeIndex($temporaryName)
{
// This finds all children (cc2) that descend from a parent (cc) by path.
// For example, cc.path may be '1/2', and cc2.path may be '1/2/3/4/5'.
$temporarySelect = $this->connection->select()->from(['cc' => $this->getTable('catalog_category_entity')], ['parent_id' => 'entity_id'])->joinInner(['cc2' => $this->getTable('catalog_category_entity')], 'cc2.path LIKE ' . $this->connection->getConcatSql([$this->connection->quoteIdentifier('cc.path'), $this->connection->quote('/%')]), ['child_id' => 'entity_id']);
$this->connection->query($temporarySelect->insertFromSelect($temporaryName, ['parent_id', 'child_id']));
}
/**
* 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;
}
/**
* Create anchor select
*
* @param \Magento\Store\Model\Store $store
* @return \Magento\Framework\DB\Select
*/
protected function createAnchorSelect(\Magento\Store\Model\Store $store)
{
$isAnchorAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Category::ENTITY, 'is_anchor')->getId();
$statusAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'status')->getId();
$visibilityAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'visibility')->getId();
$rootCatIds = explode('/', $this->getPathFromCategoryId($store->getRootCategoryId()));
array_pop($rootCatIds);
return $this->connection->select()->from(['cc' => $this->getTable('catalog_category_entity')], [])->joinInner(['cc2' => $this->getTable('catalog_category_entity')], 'cc2.path LIKE ' . $this->connection->getConcatSql([$this->connection->quoteIdentifier('cc.path'), $this->connection->quote('/%')]) . ' AND cc.entity_id NOT IN (' . implode(',', $rootCatIds) . ')', [])->joinInner(['ccp' => $this->getTable('catalog_category_product')], 'ccp.category_id = cc2.entity_id', [])->joinInner(['cpw' => $this->getTable('catalog_product_website')], 'cpw.product_id = ccp.product_id', [])->joinInner(['cpsd' => $this->getTable('catalog_product_entity_int')], 'cpsd.entity_id = ccp.product_id AND cpsd.store_id = 0' . ' AND cpsd.attribute_id = ' . $statusAttributeId, [])->joinLeft(['cpss' => $this->getTable('catalog_product_entity_int')], 'cpss.entity_id = ccp.product_id AND cpss.attribute_id = cpsd.attribute_id' . ' AND cpss.store_id = ' . $store->getId(), [])->joinInner(['cpvd' => $this->getTable('catalog_product_entity_int')], 'cpvd.entity_id = ccp.product_id AND cpvd.store_id = 0' . ' AND cpvd.attribute_id = ' . $visibilityAttributeId, [])->joinLeft(['cpvs' => $this->getTable('catalog_product_entity_int')], 'cpvs.entity_id = ccp.product_id AND cpvs.attribute_id = cpvd.attribute_id ' . 'AND cpvs.store_id = ' . $store->getId(), [])->joinInner(['ccad' => $this->getTable('catalog_category_entity_int')], 'ccad.entity_id = cc.entity_id AND ccad.store_id = 0' . ' AND ccad.attribute_id = ' . $isAnchorAttributeId, [])->joinLeft(['ccas' => $this->getTable('catalog_category_entity_int')], 'ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad.attribute_id' . ' AND ccas.store_id = ' . $store->getId(), [])->where('cpw.website_id = ?', $store->getWebsiteId())->where($this->connection->getIfNullSql('cpss.value', 'cpsd.value') . ' = ?', \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED)->where($this->connection->getIfNullSql('cpvs.value', 'cpvd.value') . ' IN (?)', [\Magento\Catalog\Model\Product\Visibility::VISIBILITY_IN_CATALOG, \Magento\Catalog\Model\Product\Visibility::VISIBILITY_IN_SEARCH, \Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH])->where($this->connection->getIfNullSql('ccas.value', 'ccad.value') . ' = ?', 1)->columns(['category_id' => 'cc.entity_id', 'product_id' => 'ccp.product_id', 'position' => new \Zend_Db_Expr('ccp.position + 10000'), 'is_parent' => new \Zend_Db_Expr('0'), 'store_id' => new \Zend_Db_Expr($store->getId()), 'visibility' => new \Zend_Db_Expr($this->connection->getIfNullSql('cpvs.value', 'cpvd.value'))]);
}
/**
* @param Dimension $dimension
* @param AdapterInterface $adapter
* @return string
*/
private function generateExpression(Dimension $dimension, AdapterInterface $adapter)
{
$identifier = $dimension->getName();
$value = $dimension->getValue();
if (self::DEFAULT_DIMENSION_NAME === $identifier) {
$identifier = self::STORE_FIELD_NAME;
$value = $this->scopeResolver->getScope($value)->getId();
}
return sprintf('%s = %s', $adapter->quoteIdentifier($identifier), $adapter->quote($value));
}
/**
* @param FilterInterface $filter
* @param bool $isNegation
* @return string
*/
private function processTermSelect(FilterInterface $filter, $isNegation)
{
$alias = $this->tableMapper->getMappingAlias($filter);
if (is_array($filter->getValue())) {
$value = sprintf('%s IN (%s)', $isNegation ? 'NOT' : '', implode(',', array_map([$this->connection, 'quote'], $filter->getValue())));
} else {
$value = ($isNegation ? '!' : '') . '= ' . $this->connection->quote($filter->getValue());
}
$resultQuery = sprintf('%1$s.value %2$s', $alias, $value);
return $resultQuery;
}
/**
* Get all database triggers
*
* @return void
*/
protected function loadTriggers()
{
$schema = $this->getSchemaName();
if ($schema) {
$sqlFilter = $this->resourceAdapter->quoteIdentifier('TRIGGER_SCHEMA') . ' = ' . $this->resourceAdapter->quote($schema);
} else {
$sqlFilter = $this->resourceAdapter->quoteIdentifier('TRIGGER_SCHEMA') . ' != ' . $this->resourceAdapter->quote('INFORMATION_SCHEMA');
}
$select = $this->getSelect()->from(new \Zend_Db_Expr($this->resourceAdapter->quoteIdentifier(['INFORMATION_SCHEMA', 'TRIGGERS'])))->where($sqlFilter);
$results = $this->resourceAdapter->query($select);
$data = [];
foreach ($results as $row) {
$row = array_change_key_case($row, CASE_LOWER);
$row['action_statement'] = $this->convertStatement($row['action_statement']);
$key = $row['event_object_table'] . $row['event_manipulation'] . $row['action_timing'];
$data[$key] = $row;
}
$this->triggers = $data;
}
/**
* SELECT
* pps.sale_id,
* pps.date_paid,
* sfo.base_grand_total,
* ce.entity_id
* FROM prxgt_pv_sale pps
* LEFT JOIN sales_flat_order sfo
* ON pps.sale_id = sfo.entity_id
* LEFT JOIN customer_entity ce
* ON sfo.customer_id = ce.entity_id
* WHERE pps.date_paid >= '2016-01-01 00:00:00'
* AND pps.date_paid <= '2016-01-31 23:59:59'
*
* @param $dsBegin - '20160101'
* @param $dsEnd - '20160131'
*
* @return array [ $custId => [$orderId=>[$amount], ... ], ... ]
*/
public function getSaleOrdersForRebate($dsBegin, $dsEnd)
{
$result = [];
/* aliases and tables */
$asPvSale = 'pps';
$asMageSale = 'sfo';
$asMageCust = 'ce';
$tblPvSale = $this->_resource->getTableName(PvSale::ENTITY_NAME);
$tblMageSale = $this->_resource->getTableName(Cfg::ENTITY_MAGE_SALES_ORDER);
$tblMageCust = $this->_resource->getTableName(Cfg::ENTITY_MAGE_CUSTOMER);
// FROM prxgt_pv_sale pps
$query = $this->_conn->select();
$cols = [PvSale::ATTR_SALE_ID, PvSale::ATTR_DATE_PAID];
$query->from([$asPvSale => $tblPvSale], $cols);
// LEFT JOIN sales_flat_order sfo ON pps.sale_id = sfo.entity_id
$on = "{$asPvSale}." . PvSale::ATTR_SALE_ID . "={$asMageSale}." . Cfg::E_COMMON_A_ENTITY_ID;
$cols = [Cfg::E_SALE_ORDER_A_BASE_GRAND_TOTAL];
$query->joinLeft([$asMageSale => $tblMageSale], $on, $cols);
// LEFT JOIN customer_entity ce ON sfo.customer_id = ce.entity_id
$on = "{$asMageSale}." . Cfg::E_SALE_ORDER_A_CUSTOMER_ID . "={$asMageCust}." . Cfg::E_CUSTOMER_A_ENTITY_ID;
$cols = [Cfg::E_CUSTOMER_A_ENTITY_ID];
$query->joinLeft([$asMageCust => $tblMageCust], $on, $cols);
// where
$from = $this->_toolPeriod->getTimestampFrom($dsBegin);
$to = $this->_toolPeriod->getTimestampTo($dsEnd);
$whereFrom = PvSale::ATTR_DATE_PAID . '>=' . $this->_conn->quote($from);
$whereTo = PvSale::ATTR_DATE_PAID . '<=' . $this->_conn->quote($to);
$wherePv = PvSale::ATTR_TOTAL . ">0";
$query->where("{$whereFrom} AND {$whereTo} AND {$wherePv}");
// $sql = (string)$query;
$data = $this->_conn->fetchAll($query);
foreach ($data as $item) {
$custId = $item[Cfg::E_CUSTOMER_A_ENTITY_ID];
$saleId = $item[PvSale::ATTR_SALE_ID];
$amount = $item[Cfg::E_SALE_ORDER_A_BASE_GRAND_TOTAL];
$result[$custId][$saleId] = $amount;
}
return $result;
}
/**
* @param string $field
* @param string $operator
* @param mixed $value
* @return string
*/
public function generateCondition($field, $operator, $value)
{
return sprintf(is_array($value) ? self::CONDITION_PATTERN_ARRAY : self::CONDITION_PATTERN_SIMPLE, $this->adapter->quoteIdentifier($field), $operator, $this->adapter->quote($value));
}
