protected function _supplierCollection($key)
{
$sqlQuery = new SqlQuery();
$sql = "SELECT id, name" . $this->table . "FROM supplier`" . $this->index . "`WHERE" . $key . "\n\n LEFT JOIN ( SELECT id_ingredient, id_supplier, MIN(price) FROM has_supplier\n\n IF (id=id_supplier){min(price)}";
$result = $sqlQuery->execute($sql);
return $result['supplier'];
}
public function load($indexList = null, $column = false)
{
$sqlQuery = new SqlQuery();
$column = $column ? $column : $this->index;
if (isset($indexList)) {
$indexList = " WHERE " . $column . " IN (" . implode(',', $indexList) . ")" . implode(' AND ', $this->filters);
} else {
$indexList = array_reduce($this->filters, function ($prev, $next) {
return $prev == "" ? " WHERE " . $next : $prev . " AND " . $next;
}, "");
}
if ($this->order) {
$indexList .= ' ORDER BY `' . $this->orderColumn . '` ' . $this->orderDir;
}
if ($this->limit > -1) {
$indexList .= ' LIMIT ' . $this->limit;
}
$sql = "SELECT * FROM " . $this->table . $indexList;
$result = $sqlQuery->execute($sql);
if ($result['status']) {
foreach ($result['data'] as $item) {
$this->processItem($item);
}
}
}
public function getRelations()
{
$sqlQuery = new SqlQuery();
$sql = "SELECT id FROM can_do\n RIGHT JOIN cocktail ON can_do.id_cocktail = cocktail.id\n WHERE can_do.id_barkeeper = " . $this->getId();
$result = $sqlQuery->execute($sql);
$result = array_map(function ($item) {
return intval($item['id']);
}, $result['data']);
$cocktails = new CocktailCollection();
$cocktails->load($result);
return $cocktails->getItemsRaw();
}
public function loadByCocktail($cocktailId)
{
$sqlQuery = new SqlQuery();
$sql = "SELECT id_ingredient as id FROM has_ingredient WHERE id_cocktail=" . $cocktailId;
$result = $sqlQuery->execute($sql);
if ($result['status']) {
$ingredientList = array_map(function ($item) {
return $item['id'];
}, $result['data']);
$this->load($ingredientList);
}
}
public function loadByQuery($query, $alc, $cal, $exclusions)
{
$sqlQuery = new SqlQuery();
$cocktails = null;
$sql = "SELECT id, `name` FROM cocktail\n\n LEFT JOIN (\n\n SELECT has_ingredient.id_cocktail, ROUND((SUM(ingredients.alcohol) / COUNT(ingredients.alcohol)), 2) as alcohol, SUM(ingredients.calories) as calories FROM has_ingredient\n\n LEFT JOIN (\n\n SELECT has_alternative.id_origin, ingredient.id, ingredient.alcohol, ingredient.calories FROM has_alternative\n\n RIGHT JOIN ingredient\n\n ON has_alternative.id_target = ingredient.id\n\n WHERE ingredient.id NOT IN (" . $exclusions . ")\n\n ) AS ingredients\n\n ON ingredients.id_origin = has_ingredient.id_ingredient OR ingredients.id = has_ingredient.id_ingredient\n GROUP BY id_cocktail\n HAVING COUNT(ingredients.id) = COUNT(id_cocktail)\n\n ) as cocktails\n\n ON cocktail.id = id_cocktail\n WHERE cocktails.alcohol <= " . $alc . " AND cocktails.calories <= " . $cal . " AND cocktail.name LIKE '" . $query . "%'";
$result = $sqlQuery->execute($sql);
if ($result['status']) {
$cocktails = array_map(function ($item) {
return $item['id'];
}, $result['data']);
$this->orderBy('ranking', 'ASC');
$this->load($cocktails);
}
}
static function update($db, $table_name, $data, $where, $bindings = null)
{
$query = new SqlQuery($db, $table_name, 'update');
$query->where = $where;
$query->bindings = $bindings;
$query->data = $data;
return $query->execute();
}
public function getRelations()
{
$sqlQuery = new SqlQuery();
$sql = "SELECT id, name, price FROM has_supplier\n RIGHT JOIN supplier ON has_supplier.id_supplier = supplier.id\n WHERE has_supplier.id_ingredient = " . $this->getId();
$result = $sqlQuery->execute($sql);
return $result['data'];
}
public function isFlopTen()
{
$sqlQuery = new SqlQuery();
$sql = 'SELECT id FROM (SELECT id, ranking FROM cocktail ORDER BY ranking ASC LIMIT 10) AS j1 WHERE id = ' . $this->getId();
$result = $sqlQuery->execute($sql);
return count($result['data']) > 0;
}
public static function removeIngredientSupplierRelation($session)
{
$sqlQuery = new SqlQuery();
$list = json_decode($session['entity']);
$positive = 0;
foreach ($list as $item) {
$sql = "SELECT id_supplier FROM has_supplier WHERE id_supplier = " . $item . " AND id_ingredient = " . $session['entityID'] . " LIMIT 1";
$doesExist = $sqlQuery->execute($sql);
$doesExist = count($doesExist['data']);
if ($doesExist) {
$sql = "DELETE FROM has_supplier WHERE id_ingredient = " . $session['entityID'] . " AND id_supplier = " . $item;
$result = $sqlQuery->execute($sql);
$positive += $result['status'];
}
}
return $positive == count($list);
}
