/**
* set the html for user settings page
* this function is registered in xajax
* @return xajaxResponse every xajax registered function needs to return this object
*/
function action_get_user_settings_page()
{
global $logging;
global $user;
global $user_settings_table_configuration;
global $firstthingsfirst_portal_title;
global $user_start_time_array;
$logging->info("USER_ACTION " . __METHOD__ . " (user="******")");
# store start time
$user_start_time_array[__METHOD__] = microtime(TRUE);
# create necessary objects
$result = new Result();
$response = new xajaxResponse();
$html_database_table = new HtmlDatabaseTable($user_settings_table_configuration);
# create an array with selection of fields that user may change
$db_fields_array = array(DB_ID_FIELD_NAME, USER_NAME_FIELD_NAME, USER_PW_FIELD_NAME, USER_LANG_FIELD_NAME, USER_DATE_FORMAT_FIELD_NAME, USER_DECIMAL_MARK_FIELD_NAME, USER_LINES_PER_PAGE_FIELD_NAME, USER_THEME_FIELD_NAME);
$user_record_key_string = DatabaseTable::_get_encoded_key_string(array(DB_ID_FIELD_NAME => $user->get_id()));
# set page, title, explanation and navigation
$response->assign("page_title", "innerHTML", translate("LABEL_USER_SETTINGS_TITLE"));
$response->assign("navigation_container", "innerHTML", get_page_navigation(PAGE_TYPE_USER_SETTINGS));
$html_database_table->get_page(translate("LABEL_USER_SETTINGS_TITLE"), $result);
$response->assign("main_body", "innerHTML", $result->get_result_str());
# get action pane for current user
$html_database_table->get_record($user, USER_TABLE_NAME, $user_record_key_string, $db_fields_array, $result);
$response->custom_response->assign_with_effect("action_pane", $result->get_result_str());
# set footer
$response->assign("footer_text", "innerHTML", " ");
# check post conditions
if (check_postconditions($result, $response) == FALSE) {
return $response;
}
# log total time for this function
$logging->info(get_function_time_str(__METHOD__));
return $response;
}
function __construct($primaryID = 0)
{
parent::__construct($primaryID);
// Force "Enable fixtures" for tournaments
if ($this->getCompetitionType() == 'Tournament') {
$this->setFieldDB('FixturesEnable', TRUE);
}
}
/**
* Connects to the sql database and runs a sql query.
*
* @param sql_query $sql mysql query to be performed on the database.
* @return object|boolean returns a mysqli_result object if data is retrieved from the database
* or boolean value if there is no data(true on success/false on failure).
*/
protected static final function query($sql)
{
if (self::$conn == null) {
self::$conn = new mysqli(self::$servername, self::$username, self::$password, self::$dbname);
if (self::$conn->connect_error) {
die("Connection failed: " . self::$conn->connect_error);
}
}
return self::$conn->query($sql);
}
/**
* Constructor
* Use $db->createResult( $parent, $name ) instead
*
* @param Database|DatabaseTable $parent
* @param string $name
*
* @since 1.0.0
*/
public function __construct($parent, $name)
{
if ($parent instanceof Database) {
// basic result
$this->db = $parent;
$this->table = $this->db->schema()->getAlias($name);
$this->query = DatabaseQuery::getInstance()->from($this->table);
} else {
// result referenced to parent
$this->parent = $parent;
$this->db = $parent->getDatabase();
$this->query = $parent->getDatabaseQuery();
// determine type of reference based on conventions and user hints
$this->table = $this->db->schema()->isAlias($name) ? $this->db->schema()->getTable($name) : $name;
if ($parent->getTable() == $this->table) {
$this->key = $this->db->schema()->getPrimary($this->getTable());
$this->parentKey = $this->db->schema()->getReference($parent->getTable(), $name);
} else {
$this->key = $this->db->schema()->getBackReference($parent->getTable(), $name);
$this->parentKey = $this->db->schema()->getPrimary($parent->getTable());
}
}
}
/**
* 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 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);
}
/**
* 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 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);
}
/**
* Execute a WHERE query in linear fashion.
* @global DatabaseConnector $dbConn
* @param string $where where conditions
* @param array $params parameters to be bound
* @return boolean success
*/
public static function StartLinearWhere($where, $params = array())
{
global $dbConn;
$class = get_called_class();
$tmp = new $class();
$db_column_elements = $tmp->getColumnElements();
$column_names = array();
foreach ($db_column_elements as $column) {
$column_names[] = $column->getColumnName();
}
$cols = implode(", ", $column_names);
return DatabaseTable::StartCustomLinearQuery("\n SELECT {$cols}\n FROM {$tmp->getTableName()}\n WHERE {$where}\n ", $params, $class);
}
/**
* 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);
}
protected function __construct($tableName, $id)
{
$this->table = DatabaseTable::getTableWithName($tableName);
$this->id = $id;
}
/**
* 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 record
* this function is registered in xajax
* @param string $title title of page
* @param string $key_string comma separated name value pairs
* @param array $form_values values of new record (array of name value pairs)
* @return xajaxResponse every xajax registered function needs to return this object
*/
function action_update_user_admin_record($title, $key_string, $form_values)
{
global $logging;
global $user;
global $user_admin_table_configuration;
global $firstthingsfirst_field_descriptions;
global $user_start_time_array;
# WARNING: this function is almost identical to function UserSettings::action_update_user_setting_record
# changes in this function should also lead to changes in that function
$logging->info("USER_ACTION " . __METHOD__ . " (user="******", title={$title}, key_string={$key_string})");
# store start time
$user_start_time_array[__METHOD__] = microtime(TRUE);
$html_str = "";
$name_keys = array_keys($form_values);
$new_form_values = array();
$fields = $user->get_fields();
$field_keys = array_keys($fields);
# create the key_string for user admin (who always has id=1)
$key_string_admin_user = DatabaseTable::_get_encoded_key_string(array(DB_ID_FIELD_NAME => "1"));
# create necessary objects
$result = new Result();
$response = new xajaxResponse();
$html_database_table = new HtmlDatabaseTable($user_admin_table_configuration);
foreach ($name_keys as $name_key) {
$value_array = explode(GENERAL_SEPARATOR, $name_key);
$db_field_name = $value_array[0];
$field_type = $value_array[1];
$field_number = $value_array[2];
$check_functions = explode(" ", $firstthingsfirst_field_descriptions[$field_type][FIELD_DESCRIPTION_FIELD_INPUT_CHECKS]);
$result->reset();
$logging->debug("field (name=" . $db_field_name . ", type=" . $field_type . ", number=" . $field_number . ")");
# check field values (check password field only when new password has been set)
if ($db_field_name != USER_PW_FIELD_NAME || $db_field_name == USER_PW_FIELD_NAME && strlen($form_values[$name_key]) > 0) {
check_field($check_functions, $db_field_name, $form_values[$name_key], $user->get_date_format(), $result);
if (strlen($result->get_error_message_str()) > 0) {
set_error_message($name_key, "right", $result->get_error_message_str(), "", "", $response);
return $response;
}
}
# set new value
$new_form_values[$db_field_name] = $result->get_result_str();
$logging->debug("setting new form value (db_field_name=" . $db_field_name . ", result=" . $result->get_result_str() . ")");
}
# check if all booleans have been set
foreach ($field_keys as $db_field_name) {
if ($fields[$db_field_name][1] == FIELD_TYPE_DEFINITION_BOOL) {
if (!isset($new_form_values[$db_field_name])) {
$logging->debug("found an unset bool field");
$new_form_values[$db_field_name] = "0";
}
}
}
# check if someone tries to change user admin
if ($key_string_admin_user == $key_string) {
# check if the name of user admin is changed
if ($new_form_values[USER_NAME_FIELD_NAME] != "admin") {
set_error_message("record_contents_buttons", "right", "ERROR_CANNOT_UPDATE_NAME_USER_ADMIN", "", "", $response);
return $response;
}
# check if the permissions of user admin is changed
if ($new_form_values[USER_CAN_CREATE_LIST_FIELD_NAME] != "1" || $new_form_values[USER_IS_ADMIN_FIELD_NAME] != "1") {
set_error_message("record_contents_buttons", "right", "ERROR_CANNOT_UPDATE_PERMISSIONS_USER_ADMIN", "", "", $response);
return $response;
}
}
# display error when insertion returns false
if (!$user->update($key_string, $new_form_values)) {
$logging->warn("update user admin record returns false");
$error_message_str = $user->get_error_message_str();
$error_log_str = $user->get_error_log_str();
$error_str = $user->get_error_str();
set_error_message("record_contents_buttons", "right", $error_message_str, $error_log_str, $error_str, $response);
return $response;
}
# set content
$result->reset();
$html_database_table->get_content($user, $title, "", DATABASETABLE_UNKWOWN_PAGE, $result);
$response->custom_response->assign_with_effect(USER_ADMIN_CSS_NAME_PREFIX . "content_pane", $result->get_result_str());
# set action pane
$html_str = $html_database_table->get_action_bar($title, "");
$response->custom_response->assign_with_effect("action_pane", $html_str);
# check post conditions
if (check_postconditions($result, $response) == FALSE) {
return $response;
}
# log total time for this function
$logging->info(get_function_time_str(__METHOD__));
return $response;
}
/**
* delete all existing notes from database for given field name
* @param $field_name string field name
* @return bool indicates if ListTableNote has been deleted
*/
function delete_field_notes($field_name)
{
$this->_log->trace("deleting ListTableNotes (field_name={$field_name})");
# create encoded_key_string
$encoded_key_string = parent::_encode_key_string(LISTTABLENOTE_FIELD_NAME_FIELD_NAME . "='{$field_name}'");
if (parent::delete($encoded_key_string) == FALSE) {
return FALSE;
}
$this->_log->trace("deleted ListTableNotes");
return TRUE;
}
/**
* 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);
}
/**
* 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");
}
}
/**
* delete all existing attachments from database for given record id
* @param $record_id int unique identifier of a ListTable object
* @return bool indicates if ListTableAttachment has been deleted
*/
function delete_record_attachments($record_id)
{
$this->_log->trace("deleting ListTableAttachments (record_id={$record_id})");
# create encoded_key_string
$encoded_key_string = parent::_encode_key_string(LISTTABLEATTACHMENT_RECORD_ID_FIELD_NAME . "='{$record_id}'");
if (parent::delete($encoded_key_string) == FALSE) {
return FALSE;
}
$this->_log->trace("deleted ListTableAttachments");
return TRUE;
}
/**
* 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);
}
}
}
}
$count = $count + 1;
#echo "\n";
}
#var_dump($field_is_reqd);
# need to issue a query against the desired table to get the metadata
$query = "SELECT * FROM {$tablename}";
# on success this returns an MDB2_Result handle
# TODO - deal with the failure condition here
#$result = $mdb->query($query, true, true, 'MDB2_BufferedIterator');
$result =& $mdb->query($query, true, true);
# now that we have a result set, we can get the field types
# as an array:
$dt = new DatabaseTable();
$dt->set_raw_table_name($tablename);
$dt->set_tablename_prefix($tablename_prefix);
$dt->set_raw_field_names($table_field_names);
$dt->set_db_field_types($result->types);
$dt->set_field_is_reqd($field_is_reqd);
# this is needed, esp. for the play_field_types
# assign all the smarty variables we support
$smarty->assign('classname', $dt->get_camelcase_table_name());
$smarty->assign('objectname', $dt->get_java_object_name());
$smarty->assign('tablename', $tablename);
$smarty->assign('tablename_clean', $dt->get_clean_table_name());
$smarty->assign('tablename_clean_singular', $dt->get_clean_table_name_singular());
$smarty->assign('fields', $table_field_names);
# NEW
$smarty->assign('field_is_reqd', $dt->get_field_is_reqd());
/**
* 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);
}
/**
* 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);
}
/**
* activate an existing record in database
* @param $encoded_key_string string unique identifier of record to be archived
* @return bool indicates if record has been archived
*/
function activate($encoded_key_string)
{
$this->_log->trace("activating record from UserDatabaseTable (encoded_key_string=" . $encoded_key_string . ")");
# call parent archive()
if (parent::activate($encoded_key_string, $this->_user->get_name()) == FALSE) {
return FALSE;
}
$this->_log->trace("activated record from UserDatabaseTable");
return TRUE;
}
# need the Manager module to do our magic
# @see http://pear.php.net/package/MDB2/docs/latest/MDB2/MDB2_Driver_Manager_Common.html
$mdb->loadModule('Manager');
// Extended, Datatype, Manager, Reverse, Native, Function
# get all the field names
$table_field_names = $mdb->listTableFields($table_name);
$nfields = count($table_field_names);
# need to issue a query against the desired table to get the metadata
$query = "SELECT * FROM {$table_name}";
# on success this returns an MDB2_Result handle
# TODO - deal with the failure condition here
#$result = $mdb->query($query, true, true, 'MDB2_BufferedIterator');
$result =& $mdb->query($query, true, true);
# now that we have a result set, we can get the field types
# as an array:
$dt = new DatabaseTable();
$dt->set_raw_table_name($table_name);
$dt->set_raw_field_names($table_field_names);
$dt->set_db_field_types($result->types);
# assign all the smarty variables
$smarty->assign('classname', $dt->get_camelcase_table_name());
$smarty->assign('objectname', $dt->get_java_object_name());
$smarty->assign('tablename', $table_name);
$smarty->assign('fields', $dt->get_camelcase_field_names());
$smarty->assign('fields_as_insert_csv_string', $dt->get_fields_as_insert_stmt_csv_list());
$smarty->assign('prep_stmt_as_insert_csv_string', $dt->get_prep_stmt_insert_csv_string());
$smarty->assign('prep_stmt_as_update_csv_string', $dt->get_fields_as_update_stmt_csv_list());
$smarty->assign('types', $dt->get_java_field_types());
$smarty->assign('dt', $dt);
# TODO - this is hard-coded
$out = $smarty->fetch("{$template}.tpl");
/**
* 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);
}
/**
* 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);
}
