/** * @param Warehouse $modelObject * @return bool */ protected function saveAddedProducts($modelObject) { $success = true; $warehouseId = $modelObject->getId(); foreach ($modelObject->getAddedProducts() as $addedProduct) { $sql = "\n INSERT INTO warehouse_products\n (warehouse_id, product_id)\n VALUES\n (:warehouseId, :productId)\n "; $statement = Database::getInstance()->prepare($sql); $statement->bindParam('warehouseId', $warehouseId); $productId = $addedProduct->getId(); $statement->bindParam('productId', $productId); $success = $statement->execute(); if (!$success) { break; } } if ($success) { $modelObject->setAddedProducts(array()); // Clear added products } return $success; }
/** * @param int $id * @return bool */ protected function delete($id) { $sql = "DELETE FROM products WHERE id = :id"; $statement = Database::getInstance()->prepare($sql); $statement->bindParam('id', $id); return $statement->execute(); }
protected function findWarehouse($order) { // Run query to get list of warehouses with product stock sorted by distance $sql = "\n SELECT\n od.product_id productId, order_qty orderQty, warehouse_id warehouseId, stock_qty stockQty,\n (((acos(sin((od.latitude*pi()/180)) * sin((wd.latitude*pi()/180))+cos((od.latitude*pi()/180)) * cos((wd.latitude*pi()/180)) * cos(((od.longitude-wd.longitude)*pi()/180))))*180/pi())*60*1.1515) distance\n FROM\n (\n SELECT\n o.order_id, p.product_id, count(op.product_id) order_qty, o.longitude, o.latitude\n FROM\n orders o\n JOIN\n products p\n JOIN\n order_products op\n ON op.order_id = o.order_id\n AND op.product_id = p.product_id\n GROUP BY\n o.order_id, p.product_id\n ) od\n LEFT JOIN\n (\n SELECT\n w.warehouse_id, p.product_id, count(wp.product_id) stock_qty, w.longitude, w.latitude\n FROM\n warehouses w\n JOIN\n products p\n JOIN\n warehouse_products wp\n ON wp.warehouse_id = w.warehouse_id\n AND wp.product_id = p.product_id\n GROUP BY\n w.warehouse_id, p.product_id\n ) wd\n ON wd.product_id = od.product_id AND wd.stock_qty >= od.order_qty\n WHERE\n order_id = :orderId\n ORDER BY\n distance\n "; $statement = Database::getInstance()->prepare($sql); $orderId = $order->getId(); $statement->bindParam('orderId', $orderId); $statement->execute(); $currWarehouseId = 0; $numProducts = 0; foreach ($statement->fetchAll(\PDO::FETCH_ASSOC) as $candidate) { if (!$candidate['warehouseId']) { // No warehouse has adequate stock of this item return false; } if ($currWarehouseId != $candidate['warehouseId']) { $currWarehouseId = $candidate['warehouseId']; $numProducts = 0; // Last warehouse didn't have enough stock to fulfill order so reset numProducts } $numProducts += $candidate['orderQty']; if ($numProducts == count($order->getProducts())) { return $currWarehouseId; // We've found our warehouse } } return false; // No warehouse can fulfill order }