public static function getWarehouseStock($warehouseId) { $sql = "SELECT category, parent, " . Database::getTableName('stock') . ".male, " . Database::getTableName('stock') . ".female, " . Database::getTableName('stock') . ".children, " . Database::getTableName('stock') . ".baby, " . Database::getTableName('stock') . ".summer, " . Database::getTableName('stock') . ".winter, SUM(income) AS income, SUM(outgo) AS outgo FROM " . Database::getTableName('stock') . " LEFT JOIN " . Database::getTableName('cartons') . " ON " . Database::getTableName('stock') . ".carton=" . Database::getTableName('cartons') . ".id" . " LEFT JOIN " . Database::getTableName('categories') . " ON " . Database::getTableName('stock') . ".category=" . Database::getTableName('categories') . ".id" . " WHERE " . Database::getTableName('cartons') . ".warehouse=?" . " GROUP BY category, " . Database::getTableName('stock') . ".male, " . Database::getTableName('stock') . ".female, " . Database::getTableName('stock') . ".children, " . Database::getTableName('stock') . ".baby, " . Database::getTableName('stock') . ".summer, " . Database::getTableName('stock') . ".winter" . " ORDER BY parent, category"; $response = Database::getInstance()->sql('getWarehouseStock' . $warehouseId, $sql, 'i', [$warehouseId], false); if (count($response) > 0) { return $response; } return false; }
public static function addArticle($cartonId = 0, $categoryId = 0, $male = false, $female = false, $children = false, $baby = false, $winter = false, $summer = false, $incomeAdd = 0, $outgoAdd = 0) { // check for existing stock entry $sql = "SELECT COUNT(carton) as count FROM " . Database::getTableName('stock') . " WHERE carton=? AND category=? AND male=? AND female=? AND children=? AND baby=? AND winter=? AND summer=?"; $response = Database::getInstance()->sql('getArticle', $sql, 'iiiiiiii', [$cartonId, $categoryId, $male, $female, $children, $baby, $winter, $summer], false); // check responsee Woche if (is_array($response) && $response[0]['count'] > 0) { // entry exists $sql = "UPDATE " . Database::getTableName('stock') . " SET income=income+?, outgo=outgo+?" . " WHERE carton=? AND category=? AND male=? AND female=? AND children=? AND baby=? AND winter=? AND summer=?"; return Database::getInstance()->sql('editArticle', $sql, 'iiiiiiiiii', [$incomeAdd, $outgoAdd < 0 ? $outgoAdd * -1 : $outgoAdd, $cartonId, $categoryId, $male, $female, $children, $baby, $winter, $summer]); } else { // new entry $sql = "INSERT INTO " . Database::getTableName('stock') . " (income, outgo, carton, category, male, female, children, baby, winter, summer)" . " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; return Database::getInstance()->sql('editArticle', $sql, 'iiiiiiiiii', [$incomeAdd, $outgoAdd < 0 ? $outgoAdd * -1 : $outgoAdd, $cartonId, $categoryId, $male, $female, $children, $baby, $winter, $summer]); } }
/** * @return array of warehouses. */ public static function getWarehouses() { $warehouses = array(); $sql = "SELECT * FROM " . Database::getTableName('warehouses') . " ORDER BY country ASC, city ASC, name ASC"; $response = Database::getInstance()->sql('getWarehouses', $sql, '', [], true); if (is_array($response)) { foreach ($response as $entry) { $warehouse = new Warehouse(-1); $warehouse->id = $entry['id']; $warehouse->name = $entry['name']; $warehouse->country = $entry['country']; $warehouse->city = $entry['city']; $warehouse->setPassword($entry['password']); $warehouse->setPasswordRestricted($entry['passwordRestricted']); $warehouse->setMail($entry['mail']); $warehouse->setDisableLocationLess($entry['disableLocationLess']); $warehouse->setDisablePaletteLess($entry['disablePaletteLess']); array_push($warehouses, $warehouse); } } return $warehouses; }
/** * Gets latest palette number. * @param integer $warehouseId Warehouse ID */ public static function getMaxNumber($warehouseId) { $sql = "SELECT MAX(number) AS max FROM " . Database::getTableName('palettes') . " WHERE warehouse=?"; $response = Database::getInstance()->sql('getPalettes', $sql, 'i', [$warehouseId], false); if (is_array($response) && count($response) > 0) { return $response[0]['max']; } return 0; }
/** * Gets categories as hierarchy array. * @param integer $warehouseId Warehouse ID */ public static function getCategories($warehouseId, $parentId = null) { $categories = []; if ($parentId == null) { $sql = "SELECT * FROM " . Database::getTableName('categories') . " WHERE warehouse=? AND parent IS NULL ORDER BY name ASC"; $response = Database::getInstance()->sql('getCategories', $sql, 'i', [$warehouseId], false); } elseif ($parentId < 0) { $sql = "SELECT * FROM " . Database::getTableName('categories') . " WHERE warehouse=? ORDER BY name ASC"; $response = Database::getInstance()->sql('getCategories', $sql, 'i', [$warehouseId], false); } else { $sql = "SELECT * FROM " . Database::getTableName('categories') . " WHERE warehouse=? AND parent=? ORDER BY name ASC"; $response = Database::getInstance()->sql('getCategories', $sql, 'ii', [$warehouseId, $parentId], false); } // add root categories if (is_array($response)) { foreach ($response as $entry) { // add category to list $category = new Category(-1); $category->id = $entry['id']; $category->parent = $entry['parent']; $category->warehouseId = $entry['warehouse']; $category->name = $entry['name']; $category->demand = $entry['demand']; $category->male = $entry['male']; $category->female = $entry['female']; $category->children = $entry['children']; $category->baby = $entry['baby']; $category->summer = $entry['summer']; $category->winter = $entry['winter']; $category->weight = $entry['weight']; array_push($categories, $category); } } return $categories; }
public static function getActiveSessionsNumber() { Session::validateSessions(); $sql = "SELECT COUNT(id) AS activeSessions FROM " . Database::getTableName('sessions'); $response = Database::getInstance()->sql('getActiveSessions', $sql, '', [], false); if (is_array($response) && count($response) > 0) { return $response[0]['activeSessions']; } return 0; }
/** * gets loactions of warehouse. * @param integer $warehouseId Warehouse ID */ public static function getLocations($warehouseId) { $locations = array(); $sql = "SELECT * FROM " . Database::getTableName('locations') . " WHERE warehouse=? ORDER BY name"; $response = Database::getInstance()->sql('getLocations', $sql, 'i', [$warehouseId]); if (is_array($response)) { foreach ($response as $entry) { $location = new Location(-1); $location->id = $entry['id']; $location->warehouseId = $entry['warehouse']; $location->name = $entry['name']; array_push($locations, $location); } } return $locations; }
/** * Receive carton from database. * @param integer $id Carton ID * @param integer $warehouseId Warehouse ID * @param integer $update Set true to update from database, insteadt from cache. */ private function updateFromDatabase($id, $warehouseId, $update) { $sql = "SELECT * FROM " . Database::getTableName('cartons') . " WHERE warehouse=? AND id=?"; $response = Database::getInstance()->sql('getCarton', $sql, 'ii', [$warehouseId, $id], !$update); if ($response && count($response) > 0) { $response = $response[0]; $this->id = $response['id']; $this->warehouseId = $response['warehouse']; $this->locationId = $response['location']; $this->paletteId = $response['palette']; } }