/** * 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); }