/**
* Update inventory entry if exists or create a new one.
*
* @param string $date Date value to update or add.
* @param int $item_id Id of item to add if id doesn't exist.
* @param int $quantity Quantity value to update or add.
* @param string $item_note Note value to update or add.
* @return boolean Returns true on query success or false if it fails.
*/
public static function update_inventory($date, $item_id, $quantity, $item_note)
{
$sql = "INSERT INTO Inventory (`date`, item_id, quantity, notes)\n VALUES ('{$date}', '{$item_id}', '{$quantity}', '{$item_note}')\n ON DUPLICATE KEY UPDATE\n `date`= VALUES(`date`), item_id = VALUES(item_id), quantity = VALUES(quantity), notes = VALUES(notes)";
return parent::query($sql);
}
/**
* Update quantity for given recipe table item.
*
* @param int $quantity Value of quantity to update.
* @param int $recipe_inventory_id Id of table row to update.
* @return boolean Returns true on query success and false if item already exists.
*/
public static function update_recipe_inventory_quantity($quantity, $recipe_inventory_id)
{
$sql = "UPDATE RecipeItems\n SET quantity = '{$quantity}'\n WHERE id = '{$recipe_inventory_id}'";
return parent::query($sql);
}
/**
* Set variables for a session for a given user.
*
* @param string $user_name Name of user to set variables for.
* @return boolean Returns true on query success and false if it fails.
*/
public static function set_session_variables($user_name)
{
$sql = "SELECT * FROM User\n INNER JOIN UserRole ON User.userrole_id = UserRole.id\n WHERE username='******'";
if (!($result = parent::query($sql))) {
return false;
}
$row = $result->fetch_assoc();
$_SESSION["username"] = $user_name;
$_SESSION["userrole"] = $row["role"];
if (!empty($row["time_zone"])) {
$_SESSION["timezone"] = $row["time_zone"];
} else {
$_SESSION["timezone"] = date_default_timezone_get();
}
$_SESSION["date"] = date_format(date_create(NULL, timezone_open($_SESSION["timezone"])), "Y-m-d");
$_SESSION["time_out"] = $row["time_out"];
return true;
}
/**
* Update the base quantity value for a give item.
*
* @param int $item_id id of the item of which the quantity will be updated.
* @param int $quantity new quantity value to be updated.
* @return boolean returns true on successful query or false if it fails.
*/
public static function update_base_quantity($item_id, $quantity)
{
$sql = "INSERT INTO BaseQuantity (item_id, quantity)\n VALUES ('{$item_id}', '{$quantity}')\n ON DUPLICATE KEY UPDATE item_id = VALUES(item_id), quantity = VALUES(quantity)";
return parent::query($sql);
}
/**
* Get data for print preview table for a given timeslot
*
* @param string $date Date till which data should be retrieved.
* @param string $timeslot_name Name of timeslot to get data for.
* @return object|false Returns mysqli_result object if data is retrieved or false if query fails.
*/
public static function get_print_preview_timeslots($date, $timeslot_name)
{
$sql = "SELECT Category.name as category_name, Item.name as item_name, Item.id as item_id,\n IFNULL(unit, '-') as unit, IFNULL(quantity, '-') as quantity, Inv.notes as notes,\n Category.order_id as Cat_order, Item.order_id as Item_order, TimeSlotItem.factor,\n Item.rounding_option, Item.rounding_factor\n FROM Category\n INNER JOIN Item ON Item.category_id = Category.id\n INNER JOIN TimeSlotItem ON Item.id = TimeSlotItem.item_id\n LEFT OUTER JOIN (SELECT * FROM Inventory WHERE date='{$date}') AS Inv ON Inv.item_id = Item.id\n WHERE (Category.creation_date <= '{$date}' AND (Category.deletion_date > '{$date}' OR Category.deletion_date IS NULL))\n AND (Item.creation_date <= '{$date}' AND (Item.deletion_date > '{$date}' OR Item.deletion_date IS NULL))\n AND TimeSlotItem.timeslot_id = (SELECT id from TimeSlots WHERE name = '{$timeslot_name}')\n ORDER BY Cat_order, Item_order";
return parent::query($sql);
}
/**
* Update order value for given timeslot.
*
* @param string $timeslot_name Name of timeslot to update.
* @param int $order_id New order value.
* @return boolean Returns true on query success and false if item already exists.
*/
public static function update_timeslot_order($timeslot_name, $order_id)
{
$sql = "UPDATE TimeSlots\n SET order_id = '{$order_id}'\n WHERE name = '{$timeslot_name}'";
return parent::query($sql);
}
/**
* Get recipes from the database.
*
* @param string $date Date till which categories will be retrieved.
* @return object|false Returns mysqli_result object if data is retrieved or false if query fails.
*/
public static function get_recipes($date)
{
$sql = "SELECT * FROM Recipe\n WHERE creation_date <= '{$date}' AND (deletion_date > '{$date}' OR deletion_date IS NULL)\n ORDER BY order_id ASC";
return parent::query($sql);
}
/**
* Get all messages for a given conversation.
*
* @param int $conversation_id Id of the conversation to get messages of.
* @return object|false Returns mysqli_result object on query success or false if query fails.
*/
public static function get_messages($conversation_id)
{
$sql = "SELECT * FROM Message\n INNER JOIN (SELECT first_name, last_name, username FROM User) as nameTable\n ON nameTable.username = Message.sender\n WHERE conversation_id = '{$conversation_id}'\n ORDER BY `timestamp` ASC";
return parent::query($sql);
}
/**
* Count unread conversations for a given user.
*
* @param string $user Name of user whos conversations will be counted.
* @return int Returns count value on query success.
* @throws exception If query fails.
*/
public static function count_unread_conversations($user)
{
$sql = "SELECT COUNT(id) AS unreadConversations FROM Conversation\n WHERE (sender = '{$user}' AND sender_conversationStatusId = (SELECT id FROM ConversationStatus WHERE status = 'unread'))\n OR (receiver = '{$user}' AND receiver_conversationStatusId = (SELECT id FROM ConversationStatus WHERE status = 'unread'))";
if ($result = parent::query($sql)) {
return $result->fetch_assoc()['unreadConversations'];
} else {
throw new Exception("count_unread_conversations query failed");
}
}
/**
* Updates role for given user.
*
* @param string $user_name Name of user to update role of.
* @param string $role Name of the new role.
* @return boolean Returns true on query success or false if it fails.
*/
public static function update_user_role($user_name, $role)
{
$sql = "UPDATE User\n SET userrole_id= (SELECT id FROM UserRole WHERE role='{$role}')\n WHERE username='******'";
return parent::query($sql);
}
/**
* Update factor for given table id.
*
* @param int $timeslot_inventory_id Id of table row to update.
* @param int $factor New factor value.
* @return boolean Returns true on query success and false if item already exists.
*/
public static function update_timeslot_factor($timeslot_inventory_id, $factor)
{
$sql = "UPDATE TimeSlotItem\n SET factor = '{$factor}'\n WHERE id = '{$timeslot_inventory_id}'";
return parent::query($sql);
}
/**
* Update base sales with given value.
*
* @param int $base_sales New value for base sales.
* @return boolean Return true on query success and false if it fails.
*/
public static function update_base_sales($base_sales)
{
$sql = "INSERT INTO Variables (name, value)\n VALUES ('BaseSales', '{$base_sales}')\n ON DUPLICATE KEY UPDATE name = VALUES(name), value = VALUES(value)";
return parent::query($sql);
}
/**
* Update a given items rounding factor.
*
* @param int $rounding_factor New rounding factor value.
* @param int $item_id Id of item to update
* @return boolean Returns true on query success and false if it fails.
*/
public static function update_rounding_factor($rounding_factor, $item_id)
{
$sql = "UPDATE Item\n SET rounding_factor = '{$rounding_factor}'\n WHERE id = '{$item_id}'";
return parent::query($sql);
}
