/**
* Getting the orders
*
* @param unknown $sender
* @param unknown $param
* @throws Exception
*
*/
public function getOrderItems($sender, $param)
{
$results = $errors = array();
try {
if (!isset($param->CallbackParameter->searchCriteria) || count($serachCriteria = json_decode(json_encode($param->CallbackParameter->searchCriteria), true)) === 0) {
throw new Exception('System Error: search criteria not provided!');
}
$pageNo = 1;
$pageSize = DaoQuery::DEFAUTL_PAGE_SIZE;
if (isset($param->CallbackParameter->pagination)) {
$pageNo = $param->CallbackParameter->pagination->pageNo;
$pageSize = $param->CallbackParameter->pagination->pageSize;
}
$noSearch = true;
$where = array(1);
$params = array();
foreach ($serachCriteria as $field => $value) {
if (is_array($value) && count($value) === 0 || is_string($value) && ($value = trim($value)) === '') {
continue;
}
OrderItem::getQuery()->eagerLoad("OrderItem.order", 'inner join', 'ord', 'ord.id = ord_item.orderId');
switch ($field) {
case 'ord.orderNo':
case 'ord.invNo':
$where[] = $field . " like ? ";
$params[] = $value . '%';
break;
case 'ord_item.isOrdered':
$where[] = $field . " = ? ";
$params[] = $value;
break;
case 'ord_item.eta.from':
$where[] = 'ord_item.eta >= ?';
$params[] = $value;
break;
case 'ord_item.eta.to':
$where[] = 'ord_item.eta <= ?';
$params[] = $value;
break;
}
$noSearch = false;
}
if ($noSearch === true) {
throw new Exception("Nothing to search!");
}
$stats = array();
$orderItems = OrderItem::getAllByCriteria(implode(' AND ', $where), $params, true, $pageNo, $pageSize, array('ord_item.eta' => 'asc', 'ord.orderNo' => 'asc'), $stats);
$results['pageStats'] = $stats;
$results['items'] = array();
foreach ($orderItems as $item) {
$orderItemArray = $item->getJson();
$comments = Comments::getAllByCriteria('entityName = ? and entityId = ?', array('OrderItem', $item->getId()), true, null, DaoQuery::DEFAUTL_PAGE_SIZE, array('created' => 'desc'));
$orderItemArray['comments'] = array_map(create_function('$a', 'return $a->getJson();'), $comments);
$results['items'][] = $orderItemArray;
}
} catch (Exception $ex) {
$errors[] = $ex->getMessage();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}
public function getLatestETAs($sender, $param)
{
$result = $error = array();
try {
$pageNo = $this->pageNumber;
$pageSize = $this->pageSize;
if (isset($param->CallbackParameter->pagination)) {
$pageNo = $param->CallbackParameter->pagination->pageNo;
$pageSize = $param->CallbackParameter->pagination->pageSize;
}
$notSearchStatusIds = array(OrderStatus::ID_CANCELLED, OrderStatus::ID_PICKED, OrderStatus::ID_SHIPPED);
OrderItem::getQuery()->eagerLoad('OrderItem.order', 'inner join', 'ord', 'ord.id = ord_item.orderId and ord.active = 1');
$stats = array();
$oiArray = OrderItem::getAllByCriteria("(eta != '' and eta IS NOT NULL and eta != ? and ord.statusId not in (" . implode(',', $notSearchStatusIds) . "))", array(trim(UDate::zeroDate())), true, $pageNo, $pageSize, array("ord_item.eta" => "ASC", "ord_item.orderId" => "DESC"), $stats);
$result['pagination'] = $stats;
$result['items'] = array();
foreach ($oiArray as $oi) {
if (!$oi->getProduct() instanceof product) {
continue;
}
$tmp['eta'] = trim($oi->getEta());
$tmp['orderNo'] = $oi->getOrder()->getOrderNo();
$tmp['sku'] = $oi->getProduct() instanceof Product ? $oi->getProduct()->getSku() : '';
$tmp['productName'] = $oi->getProduct()->getName();
$tmp['id'] = $oi->getId();
$tmp['orderId'] = $oi->getOrder()->getId();
$result['items'][] = $tmp;
}
} catch (Exception $ex) {
$error[] = $ex->getMessage();
}
$param->ResponseData = StringUtilsAbstract::getJson($result, $error);
}
private function _getInsufficientStockOrders($params)
{
$pageNo = isset($params['pageNo']) ? trim($params['pageNo']) : 1;
$pageSize = isset($params['pageSize']) ? trim($params['pageSize']) : DaoQuery::DEFAUTL_PAGE_SIZE;
$sql = "select distinct pro.id, sum(ord_item.qtyOrdered) `orderedQty`, pro.stockOnHand, pro.stockOnPO\r\n \t\t\t\tfrom product pro\r\n \t\t\t\tinner join orderitem ord_item on (ord_item.productId = pro.id and ord_item.active = 1)\r\n \t\t\t\tinner join `order` ord on (ord.id = ord_item.orderId and ord.active = 1 and ord.type in (:ordType1, :ordType2) and ord.statusId in (:ordStatusId1, :ordStatusId2))\r\n \t\t\t\twhere pro.active = 1\r\n \t\t\t\tgroup by pro.id\r\n \t\t\t\thaving `orderedQty` > (pro.stockOnHand + pro.stockOnPO)\r\n \t\t\t\torder by ord.id";
$result = Dao::getResultsNative($sql, array('ordType1' => Order::TYPE_ORDER, 'ordType2' => Order::TYPE_INVOICE, 'ordStatusId1' => OrderStatus::ID_NEW, 'ordStatusId2' => OrderStatus::ID_INSUFFICIENT_STOCK), PDO::FETCH_ASSOC);
if (count($result) === 0) {
return array();
}
$productMap = array();
foreach ($result as $row) {
$productMap[$row['id']] = $row;
}
OrderItem::getQuery()->eagerLoad('OrderItem.order', 'inner join', 'ord', 'ord.id = ord_item.orderId and ord.active = 1 and ord.type in (?,?) and ord.statusId in (?,?)');
$sqlParams = array(Order::TYPE_ORDER, Order::TYPE_INVOICE, OrderStatus::ID_NEW, OrderStatus::ID_INSUFFICIENT_STOCK);
$where = 'ord_item.active = 1 and ord_item.productId in (' . implode(', ', array_fill(0, count(array_keys($productMap)), '?')) . ')';
$sqlParams = array_merge($sqlParams, array_keys($productMap));
$items = OrderItem::getAllByCriteria($where, $sqlParams, true, $pageNo, $pageSize, array('ord_item.id' => 'desc'));
$return = array();
foreach ($items as $item) {
$extra = array('totalOrderedQty' => isset($productMap[$item->getProduct()->getId()]) ? $productMap[$item->getProduct()->getId()]['orderedQty'] : 0);
$return[] = $item->getJson($extra);
}
return array('items' => $return);
}
/**
* 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);
}
/**
* saveOrder
*
* @param unknown $sender
* @param unknown $param
*
* @throws Exception
*
*/
public function saveOrder($sender, $param)
{
$results = $errors = array();
try {
Dao::beginTransaction();
$items = array();
$purchaseOrder = PurchaseOrder::get(trim($param->CallbackParameter->purchaseOrder->id));
if (!$purchaseOrder instanceof PurchaseOrder) {
throw new Exception('Invalid PurchaseOrder passed in!');
}
$comment = trim($param->CallbackParameter->comments);
$purchaseOrder->addComment(Comments::TYPE_WAREHOUSE, $comment);
$products = $param->CallbackParameter->products;
$outStandingOrders = array();
$invoiceNos = array();
foreach ($products->matched as $item) {
$product = Product::get(trim($item->product->id));
if (!$product instanceof Product) {
throw new Exception('Invalid Product passed in!');
}
if (isset($item->product->EANcode)) {
$EANcode = trim($item->product->EANcode);
$productcodes = ProductCode::getAllByCriteria('pro_code.productId = :productId and pro_code.typeId = :typeId', array('productId' => $product->getId(), 'typeId' => ProductCodeType::ID_EAN), true, 1, 1);
if (count($productcodes) > 0) {
$productcodes[0]->setCode($EANcode)->save();
} else {
ProductCode::create($product, ProductCodeType::get(ProductCodeType::ID_EAN), $EANcode);
}
}
if (isset($item->product->UPCcode)) {
$UPCcode = trim($item->product->UPCcode);
$productcodes = ProductCode::getAllByCriteria('pro_code.productId = :productId and pro_code.typeId = :typeId', array('productId' => $product->getId(), 'typeId' => ProductCodeType::ID_UPC), true, 1, 1);
if (sizeof($productcodes)) {
$productcodes[0]->setCode($UPCcode)->save();
} else {
ProductCode::create($product, ProductCodeType::get(ProductCodeType::ID_UPC), $UPCcode);
}
}
if (isset($item->product->warehouseLocation) && ($locationName = trim($item->product->warehouseLocation)) !== '') {
$locs = Location::getAllByCriteria('name = ?', array($locationName), true, 1, 1);
$loc = count($locs) > 0 ? $locs[0] : Location::create($locationName, $locationName);
$product->addLocation(PreferredLocationType::get(PreferredLocationType::ID_WAREHOUSE), $loc);
}
$serials = $item->serial;
$totalQty = 0;
foreach ($serials as $serial) {
$qty = trim($serial->qty);
$totalQty += intval($qty);
$serialNo = trim($serial->serialNo);
$unitPrice = trim($serial->unitPrice);
$invoiceNo = trim($serial->invoiceNo);
$invoiceNos[] = $invoiceNo;
$comments = trim($serial->comments);
ReceivingItem::create($purchaseOrder, $product, $unitPrice, $qty, $serialNo, $invoiceNo, $comments);
}
OrderItem::getQuery()->eagerLoad('OrderItem.order', 'inner join', 'ord', 'ord.id = ord_item.orderId and ord.active = 1 and ord.type = :ordType and ord_item.productId = :productId and ord.statusId in ( :statusId1, :statusId2, :statusId3)');
$orderItems = OrderItem::getAllByCriteria('ord_item.active = 1', array('ordType' => Order::TYPE_INVOICE, 'productId' => $product->getId(), 'statusId1' => OrderStatus::ID_INSUFFICIENT_STOCK, 'statusId2' => OrderStatus::ID_ETA, 'statusId3' => OrderStatus::ID_STOCK_CHECKED_BY_PURCHASING));
if (count($orderItems) > 0) {
$orders = array();
foreach ($orderItems as $orderItem) {
if (!array_key_exists($orderItem->getOrder()->getId(), $orders)) {
$orders[$orderItem->getOrder()->getId()] = $orderItem->getOrder()->getJson();
}
}
$outStandingOrders[$product->getId()] = array('product' => $product->getJson(), 'recievedQty' => $totalQty, 'outStandingOrders' => array_values($orders));
}
}
$results['outStandingOrders'] = count($outStandingOrders) > 0 ? array_values($outStandingOrders) : array();
$results['item'] = PurchaseOrder::get($purchaseOrder->getId())->getJson();
$invoiceNos = array_unique($invoiceNos);
$results['invoiceNos'] = $invoiceNos;
Dao::commitTransaction();
} catch (Exception $ex) {
Dao::rollbackTransaction();
$errors[] = $ex->getMessage();
}
$param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
}