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;
}
private function getTaxRuleByCode($code)
{
$result = null;
$entity = Cfg::ENTITY_MAGE_TAX_CALC_RULE;
$cols = [Cfg::E_TAX_CALC_RULE_A_ID];
$where = EntityTaxRule::KEY_CODE . '=' . $this->repoGeneric->getConnection()->quote($code);
$rows = $this->repoGeneric->getEntities($entity, $cols, $where);
if (is_array($rows)) {
$one = reset($rows);
$result = $one[Cfg::E_TAX_CALC_RULE_A_ID];
}
return $result;
}
