/**
* @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
}
