PHP OrderItem::getQuery方法代码示例

 /**
  * 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);
 }