protected static function _getData()
{
$return = array();
$myobCodeType = ProductCodeType::get(ProductCodeType::ID_MYOB);
foreach (Product::getAllByCriteria('updated <= 2015-12-06 04:00:00', array(), false) as $product) {
$logs = ProductQtyLog::getAllByCriteria('productId = ? and created <= ?', array($product->getId(), trim($toDate)), true, 1, 1, array('id' => 'desc'));
$log = count($logs) > 0 ? $logs[0] : null;
$myobCodes = ProductCode::getCodes($product, $myobCodeType, true, 1, 1);
$return[] = array('sku' => $product->getSku(), 'name' => $product->getName(), 'short description' => $product->getShortDescription(), 'category' => join(', ', array_map(create_function('$a', 'return $a->getCategory()->getName();'), $product->getCategories())), 'assetAccNo' => $product->getAssetAccNo(), 'revenueAccNo' => $product->getRevenueAccNo(), 'costAccNo' => $product->getCostAccNo(), 'Stock On PO' => $log instanceof ProductQtyLog ? $log->getStockOnPO() : $product->getStockOnPO(), 'Stock On Order' => $log instanceof ProductQtyLog ? $log->getStockOnOrder() : $product->getStockOnOrder(), 'Stock On Hand' => $log instanceof ProductQtyLog ? $log->getStockOnHand() : $product->getStockOnHand(), 'Total On Hand Value' => $log instanceof ProductQtyLog ? $log->getTotalOnHandValue() : $product->getTotalOnHandValue(), 'Stock In Parts' => $log instanceof ProductQtyLog ? $log->getStockInParts() : $product->getStockInParts(), 'Total In Parts Value' => $log instanceof ProductQtyLog ? $log->getTotalInPartsValue() : $product->getTotalInPartsValue(), 'Stock In RMA' => $log instanceof ProductQtyLog ? $log->getStockInRMA() : $product->getStockInRMA(), 'Total RMA Value' => $log instanceof ProductQtyLog ? $log->getTotalRMAValue() : $product->getTotalRMAValue(), 'active' => intval($product->getActive()) === 1 ? 'Y' : 'N', 'MYOB' => count($myobCodes) > 0 ? $myobCodes[0]->getCode() : '');
}
return $return;
}
public function searchProduct($sender, $param)
{
$results = $errors = array();
try {
$items = array();
$searchTxt = isset($param->CallbackParameter->searchTxt) ? trim($param->CallbackParameter->searchTxt) : '';
$where = 'pro_pro_code.code = :searchExact or pro.name like :searchTxt OR sku like :searchTxt';
$params = array('searchExact' => $searchTxt, 'searchTxt' => '%' . $searchTxt . '%');
$searchTxtArray = StringUtilsAbstract::getAllPossibleCombo(StringUtilsAbstract::tokenize($searchTxt));
if (count($searchTxtArray) > 1) {
foreach ($searchTxtArray as $index => $comboArray) {
$key = 'combo' . $index;
$where .= ' OR pro.name like :' . $key;
$params[$key] = '%' . implode('%', $comboArray) . '%';
}
}
Product::getQuery()->eagerLoad('Product.codes', 'left join');
$products = Product::getAllByCriteria($where, $params, true, 1, DaoQuery::DEFAUTL_PAGE_SIZE, array('pro.sku' => 'asc'));
foreach ($products as $product) {
if (!$product instanceof Product) {
throw new Exception('Invalid Product passed in!');
}
$EANcodes = ProductCode::getAllByCriteria('pro_code.productId = :productId and pro_code.typeId = :typeId', array('productId' => $product->getId(), 'typeId' => ProductCodeType::ID_EAN), true, 1, 1);
$EANcodes = count($EANcodes) > 0 ? $EANcodes[0]->getCode() : '';
$UPCcodes = ProductCode::getAllByCriteria('pro_code.productId = :productId and pro_code.typeId = :typeId', array('productId' => $product->getId(), 'typeId' => ProductCodeType::ID_UPC), true, 1, 1);
$UPCcodes = count($UPCcodes) > 0 ? $UPCcodes[0]->getCode() : '';
$array = $product->getJson();
$array['codes'] = array('EAN' => $EANcodes, 'UPC' => $UPCcodes);
$items[] = $array;
}
$results['items'] = $items;
} catch (Exception $ex) {
$errors[] = $ex->getMessage();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}
private function _getProducts(array $params)
{
$searchTxt = trim(isset($params['searchTxt']) ? $params['searchTxt'] : '');
if ($searchTxt === '') {
throw new Exception('SearchTxt is needed');
}
$pageSize = isset($params['pageSize']) && ($pageSize = trim($params['pageSize'])) !== '' ? $pageSize : DaoQuery::DEFAUTL_PAGE_SIZE;
$pageNo = isset($params['pageNo']) && ($pageNo = trim($params['pageNo'])) !== '' ? $pageNo : null;
$orderBy = isset($params['orderBy']) ? $params['orderBy'] : array();
$isKit = isset($params['isKit']) ? $params['isKit'] === true : null;
$sqlParams = array('searchTxtExact' => $searchTxt);
$searchTokens = array();
StringUtilsAbstract::permute(preg_split("/[\\s,]+/", trim($searchTxt)), $searchTokens);
$nameLikeArray = $skuLikeArray = array();
foreach ($searchTokens as $index => $tokenArray) {
$key = 'token' . $index;
$sqlParams[$key] = '%' . implode('%', $tokenArray) . '%';
$nameLikeArray[] = 'name like :' . $key;
$skuLikeArray[] = 'sku like :' . $key;
}
$where = array('mageId = :searchTxtExact OR (' . implode(' OR ', $nameLikeArray) . ') OR (' . implode(' OR ', $skuLikeArray) . ')');
$stats = array();
$items = Product::getAllByCriteria(implode(' AND ', $where), $sqlParams, true, $pageNo, $pageSize, $orderBy, $stats);
$results = array();
$results['items'] = array_map(create_function('$a', 'return $a->getJson();'), $items);
$results['pagination'] = $stats;
$results['pageStats'] = $stats;
return $results;
}
/**
* Searching searchProduct
*
* @param unknown $sender
* @param unknown $param
*
* @throws Exception
*
*/
public function searchProduct($sender, $param)
{
$results = $errors = array();
try {
$items = array();
$searchTxt = isset($param->CallbackParameter->searchTxt) ? trim($param->CallbackParameter->searchTxt) : '';
$pageNo = isset($param->CallbackParameter->pageNo) ? trim($param->CallbackParameter->pageNo) : '1';
$where = 'pro_pro_code.code = :searchExact or pro.name like :searchTxt OR sku like :searchTxt';
$params = array('searchExact' => $searchTxt, 'searchTxt' => '%' . $searchTxt . '%');
$searchTxtArray = StringUtilsAbstract::getAllPossibleCombo(StringUtilsAbstract::tokenize($searchTxt));
if (count($searchTxtArray) > 1) {
foreach ($searchTxtArray as $index => $comboArray) {
$key = 'combo' . $index;
$where .= ' OR pro.name like :' . $key;
$params[$key] = '%' . implode('%', $comboArray) . '%';
}
}
$stats = array();
$supplierID = isset($param->CallbackParameter->supplierID) ? trim($param->CallbackParameter->supplierID) : '';
Product::getQuery()->eagerLoad('Product.codes', 'left join');
$products = Product::getAllByCriteria($where, $params, true, $pageNo, DaoQuery::DEFAUTL_PAGE_SIZE, array('pro.sku' => 'asc'), $stats);
foreach ($products as $product) {
$array = $product->getJson();
$array['minProductPrice'] = 0;
$array['lastSupplierPrice'] = 0;
$array['minSupplierPrice'] = 0;
$minProductPriceProduct = PurchaseOrderItem::getAllByCriteria('productId = ?', array($product->getId()), true, 1, 1, array('unitPrice' => 'asc'));
$minProductPrice = sizeof($minProductPriceProduct) ? $minProductPriceProduct[0]->getUnitPrice() : 0;
$minProductPriceId = sizeof($minProductPriceProduct) ? $minProductPriceProduct[0]->getPurchaseOrder()->getId() : '';
PurchaseOrderItem::getQuery()->eagerLoad('PurchaseOrderItem.purchaseOrder');
$lastSupplierPriceProduct = PurchaseOrderItem::getAllByCriteria('po_item.productId = ? and po_item_po.supplierId = ?', array($product->getId(), $supplierID), true, 1, 1, array('po_item.id' => 'desc'));
$lastSupplierPrice = sizeof($lastSupplierPriceProduct) ? $lastSupplierPriceProduct[0]->getUnitPrice() : 0;
$lastSupplierPriceId = sizeof($lastSupplierPriceProduct) ? $lastSupplierPriceProduct[0]->getPurchaseOrder()->getId() : '';
PurchaseOrderItem::getQuery()->eagerLoad('PurchaseOrderItem.purchaseOrder');
$minSupplierPriceProduct = PurchaseOrderItem::getAllByCriteria('po_item.productId = ? and po_item_po.supplierId = ?', array($product->getId(), $supplierID), true, 1, 1, array('po_item.unitPrice' => 'asc'));
$minSupplierPrice = sizeof($minSupplierPriceProduct) ? $minSupplierPriceProduct[0]->getUnitPrice() : 0;
$minSupplierPriceId = sizeof($minSupplierPriceProduct) ? $minSupplierPriceProduct[0]->getPurchaseOrder()->getId() : '';
$array['minProductPrice'] = $minProductPrice;
$array['minProductPriceId'] = $minProductPriceId;
$array['lastSupplierPrice'] = $lastSupplierPrice;
$array['lastSupplierPriceId'] = $lastSupplierPriceId;
$array['minSupplierPrice'] = $minSupplierPrice;
$array['minSupplierPriceId'] = $minSupplierPriceId;
$items[] = $array;
}
$results['items'] = $items;
$results['pagination'] = $stats;
} catch (Exception $ex) {
$errors[] = $ex->getMessage();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}
/**
* Searching searchProduct
*
* @param unknown $sender
* @param unknown $param
*
* @throws Exception
*
*/
public function searchProduct($sender, $param)
{
$results = $errors = array();
try {
$items = array();
$searchTxt = isset($param->CallbackParameter->searchTxt) ? trim($param->CallbackParameter->searchTxt) : '';
$customer = isset($param->CallbackParameter->customerId) ? Customer::get(trim($param->CallbackParameter->customerId)) : null;
$where = 'pro_pro_code.code = :searchExact or pro.name like :searchTxt OR sku like :searchTxt';
$params = array('searchExact' => $searchTxt, 'searchTxt' => '%' . $searchTxt . '%');
$pageNo = isset($param->CallbackParameter->pageNo) ? trim($param->CallbackParameter->pageNo) : '1';
$searchTxtArray = StringUtilsAbstract::getAllPossibleCombo(StringUtilsAbstract::tokenize($searchTxt));
if (count($searchTxtArray) > 1) {
foreach ($searchTxtArray as $index => $comboArray) {
$key = 'combo' . $index;
$where .= ' OR pro.name like :' . $key;
$params[$key] = '%' . implode('%', $comboArray) . '%';
}
}
$supplierID = isset($param->CallbackParameter->supplierID) ? trim($param->CallbackParameter->supplierID) : '';
Product::getQuery()->eagerLoad('Product.codes', 'left join');
$stats = array();
$products = Product::getAllByCriteria($where, $params, true, $pageNo, DaoQuery::DEFAUTL_PAGE_SIZE, array('pro.sku' => 'asc'), $stats);
foreach ($products as $product) {
$jsonArray = $product->getJson();
$jsonArray['lastOrderItemFromCustomer'] = array();
if ($customer instanceof Customer) {
$query = OrderItem::getQuery()->eagerLoad('OrderItem.order', 'inner join', 'ord', 'ord_item.orderId = ord.id and ord_item.active = 1 and ord.customerId = :custId and ord.type = :ordType');
$orderItems = OrderItem::getAllByCriteria('productId = :prodId', array('custId' => $customer->getId(), 'prodId' => $product->getId(), 'ordType' => Order::TYPE_INVOICE), true, 1, 1, array('ord_item.id' => 'desc'));
$jsonArray['lastOrderItemFromCustomer'] = count($orderItems) > 0 ? $orderItems[0]->getJson() : array();
}
$items[] = $jsonArray;
}
$results['items'] = $items;
$results['pagination'] = $stats;
} catch (Exception $ex) {
$errors[] = $ex->getMessage();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}
/**
* getting the data
*
* @param string $preFix
* @param string $debug
*
* @return array
*/
private static function _getData(UDate $lastUpdatedTime, $preFix = '', $debug = false)
{
self::_log('== Trying to get all the updated price for products:', __CLASS__ . '::' . __FUNCTION__, $preFix);
//product prices
$productPrices = ProductPrice::getAllByCriteria('updated > ?', array(trim($lastUpdatedTime)));
self::_log('GOT ' . count($productPrices) . ' Price(s) that has changed after "' . trim($lastUpdatedTime) . '".', '', $preFix);
$products = array();
foreach ($productPrices as $productPrice) {
if (!$productPrice->getProduct() instanceof Product || array_key_exists($productPrice->getProduct()->getId(), $products)) {
continue;
}
$products[$productPrice->getProduct()->getId()] = $productPrice->getProduct();
}
//products
$productArr = Product::getAllByCriteria('updated > ?', array(trim($lastUpdatedTime)), true);
self::_log('GOT ' . count($productArr) . ' Product(s) that has changed after "' . trim($lastUpdatedTime) . '".', '', $preFix);
foreach ($productArr as $product) {
if (array_key_exists($product->getId(), $products)) {
continue;
}
$products[$product->getId()] = $product;
}
//Product_Category
$productCates = Product_Category::getAllByCriteria('updated > ?', array(trim($lastUpdatedTime)));
self::_log('GOT ' . count($productCates) . ' Product_Category(s) that has changed after "' . trim($lastUpdatedTime) . '".', '', $preFix);
foreach ($productCates as $productCate) {
if (!$productCate->getProduct() instanceof Product || array_key_exists($productCate->getProduct()->getId(), $products)) {
continue;
}
$products[$productCate->getProduct()->getId()] = $productCate->getProduct();
}
//ProductImage
$productImages = ProductImage::getAllByCriteria('updated > ? and active = 1', array(trim($lastUpdatedTime)));
self::_log('GOT ' . count($productCates) . ' ProductImage(s) that has changed after "' . trim($lastUpdatedTime) . '".', '', $preFix);
foreach ($productImages as $productImage) {
if (!$productImage->getProduct() instanceof Product || array_key_exists($productImage->getProduct()->getId(), $products)) {
continue;
}
$products[$productCate->getProduct()->getId()] = $productCate->getProduct();
}
return $products;
}
private static function _getProducts()
{
return Product::getAllByCriteria('pro.active = 1 and pro.stockOnHand > 0');
}
<?php
require_once dirname(__FILE__) . '/../main/bootstrap.php';
try {
echo "Begin" . "\n";
Core::setUser(UserAccount::get(UserAccount::ID_SYSTEM_ACCOUNT));
Dao::beginTransaction();
foreach (Product::getAllByCriteria('stockOnOrder < 0') as $product) {
echo $product->getSku() . "\n";
$product->setStockOnOrder(0)->snapshotQty(null, ProductQtyLog::TYPE_STOCK_ADJ, 'stock adjustment for negative stock on order')->save();
}
Dao::commitTransaction();
} catch (Exception $e) {
echo $e->getTraceAsString();
Dao::rollbackTransaction();
throw $e;
}
<?php
require_once dirname(__FILE__) . '/../main/bootstrap.php';
try {
echo "Begin" . "\n";
Core::setUser(UserAccount::get(UserAccount::ID_SYSTEM_ACCOUNT));
Dao::beginTransaction();
foreach (Product::getAllByCriteria('stockOnHand < 0') as $product) {
echo $product->getSku() . "\n";
$product->setStockOnOrder(0)->snapshotQty(null, ProductQtyLog::TYPE_STOCK_ADJ, 'stock adjustment for negative stock on hand')->save();
}
Dao::commitTransaction();
} catch (Exception $e) {
echo $e->getTraceAsString();
Dao::rollbackTransaction();
throw $e;
}
/**
* 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;
}
