public/main/inc/lib/specific_fields_manager.lib.php
<?php
/**
* Manage specific tools.
*
* @todo convert into a class
*/
// Database table definitions
$table_sf = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
$table_sf_val = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
/**
* Add a specific field.
*
* @param string $name specific field name
*/
function add_specific_field($name)
{
$table_sf = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
$name = trim($name);
if (empty($name)) {
return false;
}
$sql = 'INSERT INTO %s(id, code, name) VALUES(NULL, \'%s\', \'%s\')';
$_safe_name = Database::escape_string($name);
$_safe_code = substr($_safe_name, 0, 1);
$_safe_code = get_specific_field_code_from_name($_safe_code);
if (false === $_safe_code) {
return false;
}
$sql = sprintf($sql, $table_sf, $_safe_code, $_safe_name);
$result = Database::query($sql);
if ($result) {
return Database::insert_id();
} else {
return false;
}
}
/**
* Delete a specific field.
*
* @param int $id specific field id
*/
function delete_specific_field($id)
{
$table_sf = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
$id = (int) $id;
if (!is_numeric($id)) {
return false;
}
$sql = 'DELETE FROM %s WHERE id=%s LIMIT 1';
$sql = sprintf($sql, $table_sf, $id);
Database::query($sql);
//TODO also delete the corresponding values
}
/**
* Edit a specific field.
*
* @param int $id specific field id
* @param string $name new field name
*/
function edit_specific_field($id, $name)
{
$table_sf = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
$id = (int) $id;
if (!is_numeric($id)) {
return false;
}
$sql = 'UPDATE %s SET name = \'%s\' WHERE id = %s LIMIT 1';
$sql = sprintf($sql, $table_sf, $name, $id);
Database::query($sql);
}
/**
* @param array $conditions a list of condition (exemple : status=>STUDENT)
* @param array $order_by a list of fields on which to sort
*
* @return array An array with all specific fields, at platform level
*/
function get_specific_field_list($conditions = [], $order_by = [])
{
$table_sf = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
$return_array = [];
$sql = "SELECT * FROM $table_sf";
if (count($conditions) > 0) {
$sql .= ' WHERE ';
$conditions_string_array = [];
foreach ($conditions as $field => $value) {
$conditions_string_array[] = $field.' = '.$value;
}
$sql .= implode(' AND ', $conditions_string_array);
}
if (count($order_by) > 0) {
$sql .= ' ORDER BY '.implode(',', $order_by);
}
$sql_result = Database::query($sql);
while ($result = Database::fetch_array($sql_result)) {
$return_array[] = $result;
}
return $return_array;
}
/**
* @param array $conditions a list of condition (exemple : status=>STUDENT)
* @param array $order_by a list of fields on which sort
*
* @return array an array with all users of the platform
*/
function get_specific_field_values_list(
$conditions = [],
$order_by = []
) {
$table_sfv = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
$return_array = [];
$sql = "SELECT * FROM $table_sfv";
if (count($conditions) > 0) {
$sql .= ' WHERE ';
//Fixing course id
if (isset($conditions['c_id'])) {
$course_info = api_get_course_info_by_id(intval($conditions['c_id']));
$conditions['course_code'] = " '".$course_info['code']."' ";
unset($conditions['c_id']);
}
//If any course_code is provided try to insert the current course code
if (!isset($conditions['course_code'])) {
$conditions['course_code'] = " '".api_get_course_id()."' ";
}
$conditions_string_array = [];
foreach ($conditions as $field => $value) {
$conditions_string_array[] = $field.' = '.$value;
}
$sql .= implode(' AND ', $conditions_string_array);
}
if (count($order_by) > 0) {
$sql .= ' ORDER BY '.implode(',', $order_by);
}
$sql_result = Database::query($sql);
while ($result = Database::fetch_array($sql_result)) {
$return_array[] = $result;
}
return $return_array;
}
/**
* @param string $prefix xapian prefix
* @param string $course_code
* @param string $tool_id Constant from mainapi.lib.php
* @param int $ref_id representative id inside one tool item
*
* @return array
*/
function get_specific_field_values_list_by_prefix(
$prefix,
$course_code,
$tool_id,
$ref_id
) {
$table_sf = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
$table_sfv = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
$sql = 'SELECT sfv.value FROM %s sf LEFT JOIN %s sfv ON sf.id = sfv.field_id'.
' WHERE sf.code = \'%s\' AND sfv.c_id = \'%s\' AND tool_id = \'%s\' AND sfv.ref_id = %s';
$sql = sprintf(
$sql,
$table_sf,
$table_sfv,
$prefix,
$course_code,
$tool_id,
$ref_id
);
$sql_result = Database::query($sql);
while ($result = Database::fetch_array($sql_result)) {
$return_array[] = $result;
}
return $return_array;
}
/**
* Add a specific field value.
*
* @param int $id_specific_field specific field id
* @param string $course_id course code
* @param string $tool_id tool id, from main.api.lib
* @param int $ref_id intern id inside specific tool table
* @param string $value specific field value
*/
function add_specific_field_value(
$id_specific_field,
$course_id,
$tool_id,
$ref_id,
$value
) {
$table_sf_values = Database::get_main_table(
TABLE_MAIN_SPECIFIC_FIELD_VALUES
);
$value = trim($value);
if (empty($value)) {
return false;
}
$sql = 'INSERT INTO %s(id, course_code, tool_id, ref_id, field_id, value) VALUES(NULL, \'%s\', \'%s\', %s, %s, \'%s\')';
$sql = sprintf(
$sql,
$table_sf_values,
$course_id,
$tool_id,
$ref_id,
$id_specific_field,
Database::escape_string($value)
);
$result = Database::query($sql);
if ($result) {
return Database::insert_id();
} else {
return false;
}
}
/**
* Delete all values from a specific field id, course_id, ref_id and tool.
*
* @param string $course_id course code
* @param int $id_specific_field specific field id
* @param string $tool_id tool id, from main.api.lib
* @param int $ref_id intern id inside specific tool table
*/
function delete_all_specific_field_value($course_id, $id_specific_field, $tool_id, $ref_id)
{
$table_sf_values = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
$sql = 'DELETE FROM %s WHERE course_code = \'%s\' AND tool_id = \'%s\' AND ref_id = %s AND field_id = %s';
$sql = sprintf($sql, $table_sf_values, $course_id, $tool_id, $ref_id, $id_specific_field);
Database::query($sql);
}
/**
* Delete all values from a specific item (course_id, tool_id and ref_id).
* To be used when deleting such item from Dokeos.
*
* @param string Course code
* @param string Tool ID
* @param int Internal ID used in specific tool table
*/
function delete_all_values_for_item($course_id, $tool_id, $ref_id)
{
$table_sf_values = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
$sql = 'DELETE FROM %s WHERE course_code = \'%s\' AND tool_id = \'%s\' AND ref_id = %s';
$sql = sprintf($sql, $table_sf_values, $course_id, $tool_id, $ref_id);
Database::query($sql);
}
/**
* Generates a code (one-letter string) for a given field name
* Defaults to the first letter of the name, otherwise iterate through available
* letters.
*
* @param string Name
*
* @return string One-letter code, upper-case
*/
function get_specific_field_code_from_name($name)
{
// Z is used internally by Xapian
// O & C already used by tool_id and course_id
$list = [
'A',
'B',
'D',
'E',
'F',
'G',
'H',
'I',
'J',
'K',
'L',
'M',
'N',
'P',
'Q',
'R',
'S',
'T',
'U',
'V',
'W',
'X',
'Y',
];
$table_sf = Database::get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
$sql = "SELECT code FROM $table_sf ORDER BY code";
$res = Database::query($sql);
$code = strtoupper(substr($name, 0, 1));
//if no code exists in DB, return current one
if (Database::num_rows($res) < 1) {
return $code;
}
$existing_list = [];
while ($row = Database::fetch_array($res)) {
$existing_list[] = $row['code'];
}
//if the current code doesn't exist in DB, return current one
if (!in_array($code, $existing_list)) {
return $code;
}
$idx = array_search($code, $list);
$c = count($list);
for ($i = $idx + 1, $j = 0; $j < $c; $i++, $j++) {
if (!in_array($list[$i], $existing_list)) {
return $idx[$i];
}
}
// all 26 codes are used
return false;
}