/**
* Set max_heap_table_size value in Bytes. By default value is 64M
*
* @param int|null $maxHeapTableSize
* @throws \InvalidArgumentException
* @throws \RuntimeException
* @return void
*/
public function set($maxHeapTableSize = null)
{
$maxHeapTableSize = (int) (null === $maxHeapTableSize ? $this->defaultMaxHeapTableSie : $maxHeapTableSize);
if (!$maxHeapTableSize) {
throw new \InvalidArgumentException('Wrong max_heap_table_size parameter');
}
$this->currentMaxHeapTableSize = (int) $this->connection->fetchOne('SELECT @@session.max_heap_table_size');
if (!$this->currentMaxHeapTableSize) {
throw new \RuntimeException('Can not extract max_heap_table_size');
}
$this->connection->query('SET SESSION max_heap_table_size = ' . $maxHeapTableSize);
}
/**
* Move tree node
*
* @param Node $node
* @param Node $newParent
* @param Node $prevNode
* @return void
* @throws \Exception
* @todo Use adapter for generate conditions
*/
public function move($node, $newParent, $prevNode = null)
{
$position = 1;
$oldPath = $node->getData($this->_pathField);
$newPath = $newParent->getData($this->_pathField);
$newPath = $newPath . '/' . $node->getId();
$oldPathLength = strlen($oldPath);
$newLevel = $newParent->getLevel() + 1;
$levelDisposition = $newLevel - $node->getLevel();
$data = [$this->_levelField => new \Zend_Db_Expr("{$this->_levelField} + '{$levelDisposition}'"), $this->_pathField => new \Zend_Db_Expr("CONCAT('{$newPath}', RIGHT({$this->_pathField}, LENGTH({$this->_pathField}) - {$oldPathLength}))")];
$condition = $this->_conn->quoteInto("{$this->_pathField} REGEXP ?", "^{$oldPath}(/|\$)");
$this->_conn->beginTransaction();
$reorderData = [$this->_orderField => new \Zend_Db_Expr("{$this->_orderField} + 1")];
try {
if ($prevNode && $prevNode->getId()) {
$reorderCondition = "{$this->_orderField} > {$prevNode->getData($this->_orderField)}";
$position = $prevNode->getData($this->_orderField) + 1;
} else {
$reorderCondition = $this->_conn->quoteInto("{$this->_pathField} REGEXP ?", "^{$newParent->getData($this->_pathField)}/[0-9]+\$");
$select = $this->_conn->select()->from($this->_table, new \Zend_Db_Expr("MIN({$this->_orderField})"))->where($reorderCondition);
$position = (int) $this->_conn->fetchOne($select);
}
$this->_conn->update($this->_table, $reorderData, $reorderCondition);
$this->_conn->update($this->_table, $data, $condition);
$this->_conn->update($this->_table, [$this->_orderField => $position, $this->_levelField => $newLevel], $this->_conn->quoteInto("{$this->_idField} = ?", $node->getId()));
$this->_conn->commit();
} catch (\Exception $e) {
$this->_conn->rollBack();
throw new \Exception("Can't move tree node due to error: " . $e->getMessage());
}
}
/**
* Return category path by id
*
* @param int $categoryId
* @return string
*/
protected function getPathFromCategoryId($categoryId)
{
if (!isset($this->categoryPath[$categoryId])) {
$this->categoryPath[$categoryId] = $this->connection->fetchOne($this->connection->select()->from($this->getTable('catalog_category_entity'), ['path'])->where('entity_id = ?', $categoryId));
}
return $this->categoryPath[$categoryId];
}
/**
* @param string|int $eId
* @param string|int $pId
* @param string|int $aId
* @return void
* @SuppressWarnings(PHPMD.CyclomaticComplexity)
* @SuppressWarnings(PHPMD.NPathComplexity)
* @SuppressWarnings(PHPMD.ExcessiveMethodLength)
* @SuppressWarnings(PHPMD.UnusedLocalVariable)
* @SuppressWarnings(PHPMD.ExitExpression)
*/
public function moveNodes($eId, $pId, $aId = 0)
{
$eInfo = $this->getNodeInfo($eId);
if ($pId != 0) {
$pInfo = $this->getNodeInfo($pId);
}
if ($aId != 0) {
$aInfo = $this->getNodeInfo($aId);
}
$level = $eInfo[$this->_level];
$leftKey = $eInfo[$this->_left];
$rightKey = $eInfo[$this->_right];
if ($pId == 0) {
$levelUp = 0;
} else {
$levelUp = $pInfo[$this->_level];
}
$rightKeyNear = 0;
$leftKeyNear = 0;
if ($pId == 0) {
//move to root
$rightKeyNear = $this->_db->fetchOne('SELECT MAX(' . $this->_right . ') FROM ' . $this->_table);
} elseif ($aId != 0 && $pId == $eInfo[$this->_pid]) {
// if we have after ID
$rightKeyNear = $aInfo[$this->_right];
$leftKeyNear = $aInfo[$this->_left];
} elseif ($aId == 0 && $pId == $eInfo[$this->_pid]) {
// if we do not have after ID
$rightKeyNear = $pInfo[$this->_left];
} elseif ($pId != $eInfo[$this->_pid]) {
$rightKeyNear = $pInfo[$this->_right] - 1;
}
$skewLevel = $pInfo[$this->_level] - $eInfo[$this->_level] + 1;
$skewTree = $eInfo[$this->_right] - $eInfo[$this->_left] + 1;
echo "alert('" . $rightKeyNear . "');";
if ($rightKeyNear > $rightKey) {
// up
echo "alert('move up');";
$skewEdit = $rightKeyNear - $leftKey + 1;
$sql = 'UPDATE ' . $this->_table . ' SET ' . $this->_right . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_right . ' + ' . $skewEdit . ', IF(' . $this->_right . ' < ' . $eInfo[$this->_left] . ', ' . $this->_right . ' + ' . $skewTree . ', ' . $this->_right . ')), ' . $this->_level . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_level . ' + ' . $skewLevel . ', ' . $this->_level . '), ' . $this->_left . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_left . ' + ' . $skewEdit . ', IF(' . $this->_left . ' > ' . $rightKeyNear . ', ' . $this->_left . ' + ' . $skewTree . ', ' . $this->_left . '))' . ' WHERE ' . $this->_right . ' > ' . $rightKeyNear . ' AND ' . $this->_left . ' < ' . $eInfo[$this->_right];
} elseif ($rightKeyNear < $rightKey) {
// down
echo "alert('move down');";
$skewEdit = $rightKeyNear - $leftKey + 1 - $skewTree;
$sql = 'UPDATE ' . $this->_table . ' SET ' . $this->_left . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_left . ' + ' . $skewEdit . ', IF(' . $this->_left . ' > ' . $rightKey . ', ' . $this->_left . ' - ' . $skewTree . ', ' . $this->_left . ')), ' . $this->_level . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_level . ' + ' . $skewLevel . ', ' . $this->_level . '), ' . $this->_right . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_right . ' + ' . $skewEdit . ', IF(' . $this->_right . ' <= ' . $rightKeyNear . ', ' . $this->_right . ' - ' . $skewTree . ', ' . $this->_right . '))' . ' WHERE ' . $this->_right . ' > ' . $leftKey . ' AND ' . $this->_left . ' <= ' . $rightKeyNear;
}
$this->_db->beginTransaction();
try {
$this->_db->query($sql);
$this->_db->commit();
} catch (\Exception $e) {
$this->_db->rollBack();
echo $e->getMessage();
echo "<br>\r\n";
echo $sql;
echo "<br>\r\n";
exit;
}
echo "alert('node added')";
}
public function getLastCalculationIdForPeriod($calcTypeCode, $dsBegin, $dsEnd)
{
/* get calculation type ID for type code */
$calcTypeId = $this->_repoTypeCalc->getIdByCode($calcTypeCode);
/* aliases and tables */
$asPeriod = 'pbbp';
$asCalc = 'pbbc';
$tblPeriod = $this->_resource->getTableName(Period::ENTITY_NAME);
$tblCalc = $this->_resource->getTableName(Calculation::ENTITY_NAME);
// FROM prxgt_bon_base_period
$query = $this->_conn->select();
$cols = [];
$query->from([$asPeriod => $tblPeriod], $cols);
// LEFT JOIN prxgt_bon_base_calc pbbc ON pbbp.id = pbbc.period_id
$on = "{$asPeriod}." . Period::ATTR_ID . "={$asCalc}." . Calculation::ATTR_PERIOD_ID;
$cols = [Calculation::ATTR_ID];
$query->joinLeft([$asCalc => $tblCalc], $on, $cols);
// where
$whereCalcType = "({$asPeriod}." . Period::ATTR_CALC_TYPE_ID . "=:calcTypeId)";
$wherePeriodBegin = "({$asPeriod}." . Period::ATTR_DSTAMP_BEGIN . "=:dsBegin)";
$wherePeriodEnd = "({$asPeriod}." . Period::ATTR_DSTAMP_END . "=:dsEnd)";
$query->where("{$whereCalcType} AND {$wherePeriodBegin} AND {$wherePeriodEnd}");
// order by calculation id desc
$query->order(Calculation::ATTR_ID . ' DESC');
// limit
$query->limit(1);
// $sql = (string)$query;
$result = $this->_conn->fetchOne($query, ['calcTypeId' => $calcTypeId, 'dsBegin' => $dsBegin, 'dsEnd' => $dsEnd]);
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;
}
/**
* @param Node $data
* @param Node $parentNode
* @param Node $prevNode
* @return Node
*/
public function appendChild($data, $parentNode, $prevNode = null)
{
$orderSelect = $this->_conn->select();
$orderSelect->from($this->_table, new \Zend_Db_Expr('MAX(' . $this->_conn->quoteIdentifier($this->_orderField) . ')'))->where($this->_conn->quoteIdentifier($this->_parentField) . '=' . $parentNode->getId());
$order = $this->_conn->fetchOne($orderSelect);
$data[$this->_parentField] = $parentNode->getId();
$data[$this->_levelField] = $parentNode->getData($this->_levelField) + 1;
$data[$this->_orderField] = $order + 1;
$this->_conn->insert($this->_table, $data);
$data[$this->_idField] = $this->_conn->lastInsertId();
return parent::appendChild($data, $parentNode, $prevNode);
}
/**
* Update session
*
* @param string $sessionId
* @param string $sessionData
* @return bool
*/
public function write($sessionId, $sessionData)
{
// need to use write connection to get the most fresh DB sessions
$bindValues = ['session_id' => $sessionId];
$select = $this->_write->select()->from($this->_sessionTable)->where('session_id = :session_id');
$exists = $this->_write->fetchOne($select, $bindValues);
// encode session serialized data to prevent insertion of incorrect symbols
$sessionData = base64_encode($sessionData);
$bind = ['session_expires' => time(), 'session_data' => $sessionData];
if ($exists) {
$this->_write->update($this->_sessionTable, $bind, ['session_id=?' => $sessionId]);
} else {
$bind['session_id'] = $sessionId;
$this->_write->insert($this->_sessionTable, $bind);
}
return true;
}
/**
* Returns current schema name
*
* @return string
*/
protected function getCurrentSchema()
{
return $this->resourceAdapter->fetchOne('SELECT SCHEMA()');
}
/**
* Validate and prepare data about super attributes and associated products.
*
* @return $this
* @SuppressWarnings(PHPMD.CyclomaticComplexity)
*/
protected function _processSuperData()
{
$metadata = $this->metadataPool->getMetadata(ProductInterface::class);
if ($this->_productSuperData) {
$usedCombs = [];
// is associated products applicable?
foreach (array_keys($this->_productSuperData['assoc_ids']) as $assocId) {
if (!isset($this->_skuSuperAttributeValues[$this->_productSuperData['attr_set_code']][$assocId])) {
continue;
}
if ($this->_productSuperData['used_attributes']) {
$skuSuperValues = $this->_skuSuperAttributeValues[$this->_productSuperData['attr_set_code']][$assocId];
$usedCombParts = [];
foreach ($this->_productSuperData['used_attributes'] as $usedAttrId => $usedValues) {
if (empty($skuSuperValues[$usedAttrId]) || !isset($usedValues[$skuSuperValues[$usedAttrId]])) {
// invalid value or value does not exists for associated product
continue;
}
$usedCombParts[] = $skuSuperValues[$usedAttrId];
$this->_productSuperData['used_attributes'][$usedAttrId][$skuSuperValues[$usedAttrId]] = true;
}
$comb = implode('|', $usedCombParts);
if (isset($usedCombs[$comb])) {
// super attributes values combination was already used
continue;
}
$usedCombs[$comb] = true;
}
$this->_superAttributesData['super_link'][] = ['product_id' => $this->_productSuperData['assoc_entity_ids'][$assocId], 'parent_id' => $this->_productSuperData['product_id']];
$subEntityId = $this->_connection->fetchOne($this->_connection->select()->from(['cpe' => $this->_resource->getTableName('catalog_product_entity')], ['entity_id'])->where($metadata->getLinkField() . ' = ?', $assocId));
$this->_superAttributesData['relation'][] = ['parent_id' => $this->_productSuperData['product_id'], 'child_id' => $subEntityId];
}
}
return $this;
}