private function _getPurchaseSeries($productId, $from, $to, $type = null)
{
$sql = 'select unitPrice, created from `purchaseorderitem` where active = 1 AND productId = :pid AND created >=:from and created <= :to order by created asc';
$result = Dao::getResultsNative($sql, array('pid' => trim($productId), 'from' => trim($from), 'to' => trim($to)));
$return = array();
foreach ($result as $row) {
$created = new UDate(trim($row['created']));
$return[] = array($created->format('U') * 1000, (double) trim($row['unitPrice']) * 1.1);
}
return $return;
}
/**
* Getting the
* @param int $typeId
* @param string $reset
* @throws EntityException
*/
public function getInfo($typeId, $reset = false)
{
DaoMap::loadMap($this);
if (!isset($this->_cache[$typeId]) || $reset === true) {
if (!isset(DaoMap::$map[strtolower(get_class($this))]['infos']) || ($class = trim(DaoMap::$map[strtolower(get_class($this))]['infos']['class'])) === '') {
throw new EntityException('You can NOT get information from a entity' . get_class($this) . ', setup the relationship first!');
}
$sql = 'select value from ' . strtolower($class) . ' `info` where `info`.active = 1 and `info`.' . strtolower(get_class($this)) . 'Id = ? and `info`.TypeId = ?';
$result = Dao::getResultsNative($sql, array($this->getId(), $typeId), PDO::FETCH_NUM);
$this->_cache[$typeId] = array_map(create_function('$row', 'return $row[0];'), $result);
}
return $this->_cache[$typeId];
}
private static function _findAllZombieAssets()
{
$start = self::_debug("Start to run " . __FUNCTION__ . ' =================== ', self::NEW_LINE, "\t");
$overDueDate = UDate::now()->format(self::ASSET_OVERDUE_TIME);
$sql = "select a.assetId from asset a left join product p on (p.fullDescAssetId = a.assetId) where p.id is null and type in(?, ?) ";
$result = Dao::getResultsNative($sql, array(trim(Asset::TYPE_PRODUCT_DEC), trim(Asset::TYPE_PRODUCT_IMG)));
$resultCount = count($result);
self::_debug("Found " . $resultCount . ': ', " ", "\t\t");
$assetIds = array();
for ($i = 0; $i < $resultCount; $i++) {
$assetIds[] = $result[$i]['assetId'];
}
self::_debug(implode(', ', $assetIds));
return $assetIds;
}
/**
* Getting the items
*
* @param unknown $sender
* @param unknown $param
* @throws Exception
*
*/
public function getItems($sender, $param)
{
$results = $errors = array();
try {
if (!isset($param->CallbackParameter->searchCriteria->supplierId) || !($supplier = Supplier::get(trim($param->CallbackParameter->searchCriteria->supplierId))) instanceof Supplier) {
throw new Exception('Invalid Supplier provided');
}
if (!isset($param->CallbackParameter->searchCriteria->invoiceNo) || ($invoiceNo = trim($param->CallbackParameter->searchCriteria->invoiceNo)) === '') {
throw new Exception('Invalid Invoice number provided');
}
ReceivingItem::getQuery()->eagerLoad('ReceivingItem.purchaseOrder', 'inner join', 'rec_item_po', 'rec_item_po.id = rec_item.purchaseOrderId');
if (ReceivingItem::countByCriteria('rec_item_po.supplierId = ? and rec_item.invoiceNo = ?', array($supplier->getId(), $invoiceNo)) === 0) {
throw new Exception('There is no such a invoice(invoice No=' . $invoiceNo . ') for supplier:' . $supplier->getName());
}
$sql = 'select ri.productId,
ri.unitPrice `unitPrice`,
sum(ri.qty) `qty`,
group_concat(distinct ri.id) `itemIds`,
group_concat(distinct po.id) `poIds`
from receivingitem ri
inner join purchaseorder po on (po.id = ri.purchaseOrderId)
where ri.active = 1 and ri.invoiceNo = ? and po.supplierId = ?
group by ri.productId, ri.unitPrice';
$params = array($invoiceNo, $supplier->getId());
$rows = Dao::getResultsNative($sql, $params);
$results['supplier'] = $supplier->getJson();
$results['items'] = array();
foreach ($rows as $row) {
$items = count($itemIds = explode(',', $row['itemIds'])) === 0 ? array() : ReceivingItem::getAllByCriteria('id in (' . implode(',', array_fill(0, count($itemIds), '?')) . ')', $itemIds);
$pos = count($poIds = explode(',', $row['poIds'])) === 0 ? array() : PurchaseOrder::getAllByCriteria('id in (' . implode(',', array_fill(0, count($poIds), '?')) . ')', $poIds);
$results['items'][] = array('product' => Product::get($row['productId'])->getJson(), 'totalQty' => $row['qty'], 'totalPrice' => $row['unitPrice'] * $row['qty'], 'items' => array_map(create_function('$a', 'return $a->getJson();'), $items), 'purchaseOrders' => array_map(create_function('$a', 'return $a->getJson();'), $pos));
}
} catch (Exception $ex) {
$errors[] = $ex->getMessage();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}
<?php
require_once dirname(__FILE__) . '/../../bootstrap.php';
Core::setUser(UserAccount::get(UserAccount::ID_SYSTEM_ACCOUNT));
$productIds = Dao::getResultsNative('select distinct id from product where active = 1', array(), PDO::FETCH_ASSOC);
foreach ($productIds as $row) {
try {
$output = '';
$cmd = 'php ' . dirname(__FILE__) . '/pricematch.php ' . $row['id'];
$output = ExecWaitTimeout($cmd, 10);
// exec($cmd, $output);
echo print_r($output, true) . "\n";
} catch (Exception $e) {
echo $e->getMessage() . "\n";
}
}
/**
* Execute a command and kill it if the timeout limit fired to prevent long php execution
*
* @see http://stackoverflow.com/questions/2603912/php-set-timeout-for-script-with-system-call-set-time-limit-not-working
*
* @param string $cmd Command to exec (you should use 2>&1 at the end to pipe all output)
* @param integer $timeout
* @return string Returns command output
*/
function ExecWaitTimeout($cmd, $timeout = 5)
{
echo $cmd . "\n";
$descriptorspec = array(0 => array("pipe", "r"), 1 => array("pipe", "w"), 2 => array("pipe", "w"));
$pipes = array();
$timeout += time();
/**
* Getting all the delivery methods
*
* @param unknown $params
*
* @return array
*/
private function _getDeliveryMethods($params)
{
$searchTxt = isset($params['searchTxt']) && ($searchTxt = trim($params['searchTxt'])) !== '' ? $searchTxt : '';
$sql = 'select distinct value from orderinfo where value like ? and active = 1 and typeId = ' . OrderInfoType::ID_MAGE_ORDER_SHIPPING_METHOD;
$results = array();
$results['items'] = array_map(create_function('$a', 'return $a["value"];'), Dao::getResultsNative($sql, array('%' . trim($searchTxt) . '%'), PDO::FETCH_ASSOC));
return $results;
}
private function _getRunRateData($productIds)
{
if (count($productIds) === 0) {
return array();
}
$_7DaysBefore = UDate::now()->modify('-7 day');
$_14DaysBefore = UDate::now()->modify('-14 day');
$_1mthBefore = UDate::now()->modify('-1 month');
$_3mthBefore = UDate::now()->modify('-3 month');
$_6mthBefore = UDate::now()->modify('-6 month');
$_12mthBefore = UDate::now()->modify('-12 month');
$sql = "select ord_item.productId `proId`,\n\t sum(if(ord.orderDate >= '" . $_7DaysBefore . "', ord_item.qtyOrdered, 0)) `7days`,\n\t sum(if(ord.orderDate >= '" . $_14DaysBefore . "', ord_item.qtyOrdered, 0)) `14days`,\n\t sum(if(ord.orderDate >= '" . $_1mthBefore . "', ord_item.qtyOrdered, 0)) `1month`,\n\t sum(if(ord.orderDate >= '" . $_3mthBefore . "', ord_item.qtyOrdered, 0)) `3month`,\n\t sum(if(ord.orderDate >= '" . $_6mthBefore . "', ord_item.qtyOrdered, 0)) `6month`,\n\t sum(if(ord.orderDate >= '" . $_12mthBefore . "', ord_item.qtyOrdered, 0)) `12month`\n\t from `orderitem` ord_item\n\t inner join `order` ord on (ord.type = :type and ord.active = 1 and ord.id = ord_item.orderId)\n\t where ord_item.active = 1 and ord_item.productId in (" . implode(', ', $productIds) . ")\n\t group by ord_item.productId";
return Dao::getResultsNative($sql, array('type' => Order::TYPE_INVOICE), PDO::FETCH_ASSOC);
}
/**
* Getting the items
*
* @param unknown $sender
* @param unknown $param
* @throws Exception
*
*/
public function getItems($sender, $param)
{
$results = $errors = array();
try {
$class = trim($this->_focusEntity);
$pageNo = 1;
$pageSize = DaoQuery::DEFAUTL_PAGE_SIZE;
if (isset($param->CallbackParameter->pagination)) {
$pageNo = $param->CallbackParameter->pagination->pageNo;
$pageSize = $param->CallbackParameter->pagination->pageSize;
}
$serachCriteria = isset($param->CallbackParameter->searchCriteria) ? json_decode(json_encode($param->CallbackParameter->searchCriteria), true) : array();
$where = array('cn.active = 1');
$params = array();
$innerJoinStrings = array();
foreach ($serachCriteria as $field => $value) {
if (is_array($value) && count($value) === 0 || is_string($value) && ($value = trim($value)) === '') {
continue;
}
$query = $class::getQuery();
switch ($field) {
case 'cn.creditNoteNo':
$where[] = 'cn.creditNoteNo = ?';
$params[] = $value;
break;
case 'cn.applyTo':
$where[] = 'cn.applyTo IN (' . implode(", ", array_fill(0, count($value), "?")) . ')';
$params = array_merge($params, $value);
break;
case 'cn.description':
$where[] = 'cn.description like ?';
$params[] = "%" . $value . "%";
break;
case 'ord.orderNo':
$query->eagerLoad("CreditNote.order", 'inner join', 'ord', '');
$where[] = 'ord.orderNo = ?';
$params[] = trim($value);
$innerJoinStrings[] = 'inner join `order` ord on (ord.id = cn.orderId)';
break;
case 'cust.id':
$value = explode(',', $value);
$where[] = 'cn.customerId IN (' . implode(", ", array_fill(0, count($value), "?")) . ')';
$params = array_merge($params, $value);
break;
case 'pro.ids':
$value = explode(',', $value);
$query->eagerLoad("CreditNote.items", 'inner join', 'cn_item', 'cn_item.creditNoteId = cn.id and cn_item.active = 1');
$where[] = 'cn_item.productId in (' . implode(", ", array_fill(0, count($value), "?")) . ')';
$innerJoinStrings[] = 'inner join `creditnoteitem` cn_item on (cn_item.creditNoteId = cn.id and cn_item.active = 1)';
$params = array_merge($params, $value);
break;
}
}
$stats = array();
$objects = $class::getAllByCriteria(implode(' AND ', $where), $params, true, $pageNo, $pageSize, array('cn.creditNoteNo' => 'desc'), $stats);
$results['pageStats'] = $stats;
$results['items'] = array();
foreach ($objects as $obj) {
$order = $obj->getOrder();
$customer = $obj->getCustomer();
$creditNoteItems = $obj->getCreditNoteItems();
$results['items'][] = $obj->getJson(array('order' => empty($order) ? '' : $order->getJson(), 'customer' => $customer->getJson(), 'creditNoteItems' => $creditNoteItems ? array_map(create_function('$a', 'return $a->getJson();'), $creditNoteItems) : ''));
}
$sql = 'select sum(`cn`.totalValue) `totalValue`, sum(`cn`.totalPaid) `totalPaid` from `creditnote` cn ' . implode(' ', $innerJoinStrings) . ' where cn.active = 1 AND (' . implode(' AND ', $where) . ')';
$sumResult = Dao::getResultsNative($sql, $params);
$results['totalValue'] = count($sumResult) > 0 ? $sumResult[0]['totalValue'] : 0;
$results['totalPaid'] = count($sumResult) > 0 ? $sumResult[0]['totalPaid'] : 0;
} catch (Exception $ex) {
$errors[] = $ex->getMessage() . $ex->getTraceAsString();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}
/**
* Getting the items
*
* @param unknown $sender
* @param unknown $param
* @throws Exception
*
*/
public function getItems($sender, $param)
{
$results = $errors = array();
try {
$class = trim($this->_focusEntity);
$pageNo = 1;
$pageSize = DaoQuery::DEFAUTL_PAGE_SIZE;
if (isset($param->CallbackParameter->pagination)) {
$pageNo = $param->CallbackParameter->pagination->pageNo;
$pageSize = $param->CallbackParameter->pagination->pageSize;
}
$where = array('ri.active = :active');
$params = array('active' => 1);
if (isset($param->CallbackParameter->searchCriteria)) {
$criteria = $param->CallbackParameter->searchCriteria;
if (isset($criteria->invoiceNo) && ($invNo = trim($criteria->invoiceNo)) !== '') {
$where[] = 'ri.invoiceNo like :invNo';
$params['invNo'] = '%' . $invNo . '%';
}
if (isset($criteria->purchaseOrderIds) && count($purchaseOrderIds = array_filter(explode(',', trim($criteria->purchaseOrderIds)))) > 0) {
$poWhere = array();
foreach ($purchaseOrderIds as $index => $purchaseOrderId) {
$key = 'purchaseOrderId' . $index;
$poWhere[] = ':' . $key;
$params[$key] = $purchaseOrderId;
}
$where[] = 'ri.purchaseOrderId in(' . implode(', ', $poWhere) . ')';
}
if (isset($criteria->supplierIds) && count($supplierIds = array_filter(explode(',', trim($criteria->supplierIds)))) > 0) {
$suppWhere = array();
foreach ($supplierIds as $index => $supplierId) {
$key = 'supplierId' . $index;
$suppWhere[] = ':' . $key;
$params[$key] = $supplierId;
}
$where[] = 'po.supplierId in(' . implode(', ', $suppWhere) . ')';
}
}
$sql = 'select sql_calc_found_rows ri.invoiceNo,
po.supplierId,
sum(ri.qty) `qty`,
sum(ri.unitPrice * ri.qty) `price`,
group_concat(distinct po.id) `poIds`,
group_concat(distinct ri.id) `itemIds`,
min(ri.created) `created`
from receivingitem ri
inner join purchaseorder po on (po.id = ri.purchaseOrderId)
where ' . implode(' AND ', $where) . '
group by po.supplierId, ri.invoiceNo
order by ri.id desc
limit ' . ($pageNo - 1) * $pageSize . ', ' . $pageSize;
$rows = Dao::getResultsNative($sql, $params);
$stats = array();
$statsResult = Dao::getSingleResultNative('select found_rows()', array(), PDO::FETCH_NUM);
$stats['totalRows'] = intval($statsResult[0]);
$stats['pageSize'] = $pageSize;
$stats['pageNumber'] = $pageNo;
$stats['totalPages'] = intval(ceil($stats['totalRows'] / $stats['pageSize']));
$results['items'] = array();
foreach ($rows as $row) {
$pos = count($poIds = explode(',', $row['poIds'])) === 0 ? array() : PurchaseOrder::getAllByCriteria('id in (' . implode(',', array_fill(0, count($poIds), '?')) . ')', $poIds);
$results['items'][] = array('invoiceNo' => $row['invoiceNo'], 'supplier' => Supplier::get($row['supplierId'])->getJson(), 'created' => $row['created'], 'totalQty' => $row['qty'], 'totalPrice' => $row['price'], 'purchaseOrders' => array_map(create_function('$a', 'return $a->getJson();'), $pos), 'poIds' => explode(',', $row['poIds']), 'itemIds' => explode(',', $row['itemIds']));
}
$results['pageStats'] = $stats;
} catch (Exception $ex) {
$errors[] = $ex->getMessage();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}
/**
* (non-PHPdoc)
* @see BaseEntityAbstract::preSave()
*/
public function preSave()
{
if (trim($this->getId()) !== '') {
$oldStatuses = Dao::getResultsNative('select status from purchaseorder where id = ?', array($this->getId()));
if (count($oldStatuses) > 0 && ($oldStatus = trim($oldStatuses[0]['status'])) !== ($status = trim($this->getStatus()))) {
$msg = 'Changed status from "' . $oldStatus . '" to "' . $status . '"';
$this->addComment($msg, Comments::TYPE_SYSTEM)->addLog($msg, Log::TYPE_SYSTEM, 'PO_STATUS_CHANGE', __CLASS__ . '::' . __FUNCTION__);
}
}
if ($this->getFromPO() instanceof PurchaseOrder && intval($this->getIsCredit()) !== 1) {
throw new Exception('You can only set the From PO field, when this purchase order is for a credit');
}
}
<?php
require_once dirname(__FILE__) . '/../../bootstrap.php';
Core::setUser(UserAccount::get(UserAccount::ID_SYSTEM_ACCOUNT));
echo "Begin at MELB TIME: " . UDate::now(UDate::TIME_ZONE_MELB) . "\n";
if (isset($argv) && isset($argv[1]) && Product::get($argv[1]) instanceof Product) {
$productIds = Dao::getResultsNative('select distinct p.id from product p where p.id = ?', array($argv[1]), PDO::FETCH_ASSOC);
} else {
$productIds = Dao::getResultsNative('select distinct p.id from product p inner join productpricematchrule r on (r.productId = p.id and r.active = 1) where p.active = 1 order by p.id', array(), PDO::FETCH_ASSOC);
}
$rows = count($productIds);
echo "--- Got ({$rows}) products having price matching rules !";
foreach ($productIds as $row) {
try {
$output = '';
$timeout = 60;
// in seconds
$cmd = 'php ' . dirname(__FILE__) . '/pricematch.php ' . $row['id'];
$output = ExecWaitTimeout($cmd, $timeout);
// exec($cmd, $output);
echo print_r($output, true) . "\n";
} catch (Exception $e) {
echo $e->getMessage() . "\n";
}
}
echo "End at MELB TIME: " . UDate::now(UDate::TIME_ZONE_MELB) . "\n";
/**
* Execute a command and kill it if the timeout limit fired to prevent long php execution
*
* @see http://stackoverflow.com/questions/2603912/php-set-timeout-for-script-with-system-call-set-time-limit-not-working
*
/**
* Importing the attributes from magento
*
* @return void|CatelogConnector
*/
public function importProductAttributes()
{
$productAttributeSetIds = Dao::getResultsNative('select distinct pro_att_set.id from productattributeset pro_att_set where pro_att_set.isFromB2B = 1 and mageId <> 0', array(), PDO::FETCH_ASSOC);
if (count($productAttributeSetIds) === 0) {
return;
}
try {
$transStarted = false;
try {
Dao::beginTransaction();
} catch (Exception $e) {
$transStarted = true;
}
foreach ($productAttributeSetIds as $productAttributeSetId) {
$productAttributeSetId = $productAttributeSetId['id'];
$productAttributeSet = ProductAttributeSet::get($productAttributeSetId);
if (!$productAttributeSet instanceof ProductAttributeSet) {
continue;
}
$productAttributes = $this->getProductAttributeList($productAttributeSet->getMageId());
if (count($productAttributes) === 0) {
continue;
}
foreach ($productAttributes as $productAttribute) {
$mageId = trim($productAttribute->attribute_id);
$code = isset($productAttribute->code) ? trim($productAttribute->code) : '';
$type = isset($productAttribute->type) ? trim($productAttribute->type) : '';
if (!isset($productAttribute->required)) {
$required = false;
} else {
$required = trim($productAttribute->required) === '1' || $required === true || trim($productAttribute->required) === 'true' ? true : false;
}
$scope = isset($productAttribute->scope) ? trim($productAttribute->scope) : '';
$description = isset($productAttribute->description) ? trim($productAttribute->description) : $code;
Log::logging(0, get_class($this), 'getting productAttribute from magento (mageId="' . $mageId . '")', self::LOG_TYPE, '', __FUNCTION__);
$productAttribute = ProductAttribute::getByMageId($mageId);
if (!$productAttribute instanceof ProductAttribute) {
Log::logging(0, get_class($this), 'found new ProductAttribute from magento(mageId="' . $mageId . '", mageAttributeSetId="' . $productAttributeSet->getMageId() . '", code="' . $code . '", type="' . $type . '", required="' . $required . '", scope="' . $scope . '")', self::LOG_TYPE, '', __FUNCTION__);
echo 'found new ProductAttribute from magento(mageId="' . $mageId . '", mageAttributeSetId="' . $productAttributeSet->getMageId() . '", code="' . $code . '", type="' . $type . '", required="' . $required . '", scope="' . $scope . '")' . "\n";
$productAttribute = ProductAttribute::create($code, $type, $required, $scope, $description, true, $mageId, $productAttributeSet->getMageId());
} else {
Log::logging(0, get_class($this), 'found existing ProductAttribute from magento(mageId="' . $mageId . '", mageAttributeSetId="' . $productAttributeSet->getMageId() . '", code="' . $code . '", ID=' . $productAttributeSet->getId() . ', type="' . $type . '", required="' . $required . '", scope="' . $scope . '")', self::LOG_TYPE, '', __FUNCTION__);
echo 'found existing ProductAttribute from magento(mageId="' . $mageId . '", mageAttributeSetId="' . $productAttributeSet->getMageId() . '", code="' . $code . '", ID=' . $productAttributeSet->getId() . ', type="' . $type . '", required="' . $required . '", scope="' . $scope . '")' . "\n";
$productAttribute->setCode($code)->setType($type)->setRequired($required)->setScope($scope)->setDescription($description)->setIsFromB2B(true)->setAttributeSetMageId($productAttributeSet->getId())->setActive(true)->save();
}
}
}
if ($transStarted === false) {
Dao::commitTransaction();
}
} catch (Exception $e) {
if ($transStarted === false) {
Dao::commitTransaction();
}
throw $e;
}
return $this;
}
private function _topProductIds()
{
$sql = 'select productId, sum(qtyOrdered) `sum` from orderitem where active = 1 group by productId order by `sum` desc limit 10';
$result = Dao::getResultsNative($sql);
return array_map(create_function('$a', 'return $a["productId"];'), $result);
}
/**
* Finding the products with different params
*
* @param unknown $sku
* @param unknown $name
* @param array $supplierIds
* @param array $manufacturerIds
* @param array $categoryIds
* @param array $statusIds
* @param string $active
* @param string $pageNo
* @param unknown $pageSize
* @param unknown $orderBy
* @param unknown $stats
*
* @return Ambigous <Ambigous, multitype:, multitype:BaseEntityAbstract >
*/
public static function getProducts($sku, $name, array $supplierIds = array(), array $manufacturerIds = array(), array $categoryIds = array(), array $statusIds = array(), $active = null, $pageNo = null, $pageSize = DaoQuery::DEFAUTL_PAGE_SIZE, $orderBy = array(), &$stats = array(), $stockLevel = null, &$sumValues = null, $sh_from = null, $sh_to = null, $sellOnWeb = null)
{
$where = array(1);
$params = array();
if (is_array($sumValues)) {
$innerJoins = array();
}
if (is_array($sku)) {
$skus = array();
$keys = array();
foreach ($sku as $index => $value) {
$key = 'sku_' . $index;
$keys[] = ':' . $key;
$skus[$key] = trim($value);
}
$where[] = 'pro.sku in (' . implode(',', $keys) . ')';
$params = array_merge($params, $skus);
} else {
if (($sku = trim($sku)) !== '') {
$where[] = 'pro.sku like :sku';
$params['sku'] = '%' . $sku . '%';
}
}
if (($name = trim($name)) !== '') {
$where[] = 'pro.name like :proName';
$params['proName'] = '%' . $name . '%';
}
if (($active = trim($active)) !== '') {
$where[] = 'pro.active = :active';
$params['active'] = intval($active);
}
if (($sellOnWeb = trim($sellOnWeb)) !== '') {
$where[] = 'pro.sellOnWeb = :sellOnWeb';
$params['sellOnWeb'] = intval($sellOnWeb);
}
if (count($manufacturerIds) > 0) {
$ps = array();
$keys = array();
foreach ($manufacturerIds as $index => $value) {
$key = 'manf_' . $index;
$keys[] = ':' . $key;
$ps[$key] = trim($value);
}
$where[] = 'pro.manufacturerId in (' . implode(',', $keys) . ')';
$params = array_merge($params, $ps);
}
if (count($statusIds) > 0) {
$ps = array();
$keys = array();
foreach ($statusIds as $index => $value) {
$key = 'stId_' . $index;
$keys[] = ':' . $key;
$ps[$key] = trim($value);
}
$where[] = 'pro.statusId in (' . implode(',', $keys) . ')';
$params = array_merge($params, $ps);
}
if (count($supplierIds) > 0) {
$ps = array();
$keys = array();
foreach ($supplierIds as $index => $value) {
$key = 'spId_' . $index;
$keys[] = ':' . $key;
$ps[$key] = trim($value);
}
self::getQuery()->eagerLoad('Product.supplierCodes', 'inner join', 'pro_sup_code', 'pro.id = pro_sup_code.productId and pro_sup_code.supplierId in (' . implode(',', $keys) . ')');
if (is_array($sumValues)) {
$innerJoins[] = 'inner join suppliercode pro_sup_code on (pro.id = pro_sup_code.productId and pro_sup_code.supplierId in (' . implode(',', $keys) . '))';
}
$params = array_merge($params, $ps);
}
if (count($categoryIds) > 0) {
$ps = array();
$keys = array();
foreach ($categoryIds as $index => $value) {
if (($category = ProductCategory::get($value)) instanceof ProductCategory) {
$key = 'cateId_' . $index;
$keys[] = ':' . $key;
$ps[$key] = $category->getId();
$parent_category_ids = array();
foreach ($category->getAllChildrenIds() as $child_category_id) {
$key = 'cateId_' . $index . '_' . $child_category_id;
$keys[] = ':' . $key;
$ps[$key] = $child_category_id;
}
}
}
var_dump($ps);
self::getQuery()->eagerLoad('Product.categories', 'inner join', 'pro_cate', 'pro.id = pro_cate.productId and pro_cate.categoryId in (' . implode(',', $keys) . ')');
if (is_array($sumValues)) {
$innerJoins[] = 'inner join product_category pro_cate on (pro.id = pro_cate.productId and pro_cate.categoryId in (' . implode(',', $keys) . '))';
}
$params = array_merge($params, $ps);
}
if (($stockLevel = trim($stockLevel)) !== '') {
$where[] = 'pro.stockOnHand <= pro.' . $stockLevel . ' and pro.' . $stockLevel . ' is not null';
}
if (($sh_from = trim($sh_from)) !== '') {
$where[] = 'pro.stockOnHand >= :stockOnHand_from';
$params['stockOnHand_from'] = intval($sh_from);
}
if (($sh_to = trim($sh_to)) !== '') {
$where[] = 'pro.stockOnHand <= :stockOnHand_to';
$params['stockOnHand_to'] = intval($sh_to);
}
$products = Product::getAllByCriteria(implode(' AND ', $where), $params, false, $pageNo, $pageSize, $orderBy, $stats);
if (is_array($sumValues)) {
$sql = 'select sum(pro.stockOnHand) `totalStockOnHand`, sum(pro.totalOnHandValue) `totalOnHandValue` from product pro ' . implode(' ', $innerJoins) . ' where pro.active = 1 and (' . implode(' AND ', $where) . ')';
$sumResult = Dao::getResultsNative($sql, $params);
if (count($sumResult) > 0) {
$sumValues['totalStockOnHand'] = $sumResult[0]['totalStockOnHand'];
$sumValues['totalOnHandValue'] = $sumResult[0]['totalOnHandValue'];
}
}
return $products;
}
/**
* (non-PHPdoc)
* @see BaseEntityAbstract::preSave()
*/
public function preSave()
{
if (trim($this->getInvDate()) === '') {
$this->setInvDate(Udate::zeroDate());
}
if (trim($this->getId()) !== '') {
//status changed
$originalOrder = self::get($this->getId());
if ($originalOrder instanceof Order && $originalOrder->getStatus()->getId() !== $this->getStatus()->getId()) {
$infoType = OrderInfoType::get(OrderInfoType::ID_MAGE_ORDER_STATUS_BEFORE_CHANGE);
$orderInfos = OrderInfo::find($this, $infoType, false, 1, 1);
$orderInfo = count($orderInfos) === 0 ? null : $orderInfos[0];
OrderInfo::create($this, $infoType, $originalOrder->getStatus()->getId(), $orderInfo);
$this->addLog('Changed Status from [' . $originalOrder->getStatus()->getName() . '] to [' . $this->getStatus() . ']', Log::TYPE_SYSTEM, 'Auto Log', get_class($this) . '::' . __FUNCTION__);
//get the required kits qty
if (in_array(intval($this->getStatus()->getId()), array(OrderStatus::ID_PICKED, OrderStatus::ID_SHIPPED))) {
$sql = "select sum(ord_item.qtyOrdered) `requiredQty`, ord_item.productId `productId`, pro.sku `sku` from orderitem ord_item inner join product pro on (pro.id = ord_item.productId and pro.isKit = 1) where ord_item.orderId = ? and ord_item.active = 1 group by pro.id";
$result = Dao::getResultsNative($sql, array($this->getId()), PDO::FETCH_ASSOC);
if (count($result) > 0) {
$errMsg = array();
foreach ($result as $row) {
$requiredQty = $row['requiredQty'];
$sku = $row['sku'];
if (($kitsCount = count($kits = Kit::getAllByCriteria('soldOnOrderId = ? and productId =? ', array($this->getId(), $row['productId'])))) < $requiredQty) {
$errMsg[] = 'Product (SKU=' . $sku . ') needs to be sold as a kit(req Qty=' . $requiredQty . ', providedQty=' . $kitsCount . '): ' . implode(', ', array_map(create_function('$a', 'return $a->getBarcode();'), $kits));
}
}
if (count($errMsg) > 0) {
throw new EntityException(implode('; ', $errMsg));
}
}
}
}
}
}
