123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322 |
- <?php
- /**
- * Manage specific tools
- * @package chamilo.library
- */
- // Database table definitions
- /**
- * 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 ($_safe_code === false) {
- 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 = array(), $order_by = array())
- {
- $table_sf = Database:: get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
- $return_array = array();
- $sql = "SELECT * FROM $table_sf";
- if (count($conditions) > 0) {
- $sql .= ' WHERE ';
- $conditions_string_array = 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 = array(),
- $order_by = array()
- ) {
- $table_sfv = Database:: get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
- $return_array = array();
- $sql = "SELECT * FROM $table_sfv";
- if (count($conditions) > 0) {
- $sql .= ' WHERE ';
- //If any course_code nor c_id is provided try to insert the current course id
- if (!isset($conditions['c_id']) && !isset($conditions['course_code'])) {
- $conditions['c_id'] = " " . api_get_course_int_id();
- }
- $conditions_string_array = 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 char $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
- ) {
- $em = Database::getManager();
- $course = $em->getRepository('ChamiloCoreBundle:Course')->findOneBy(['code' => $course_code]);
- $sql_result = $em
- ->createQuery('
- SELECT sfv.value
- FROM ChamiloCoreBundle:SpecificField sf
- LEFT JOIN ChamiloCoreBundle:SpecificFieldValues sfv WITH sf = sfv.fieldId
- WHERE sf.code = :code AND sfv.course = :course AND sfv.toolId = :tool AND sfv.refI = :reference
- ')
- ->setParameters([
- 'code' => $prefix,
- 'course' => $course,
- 'tool' => $tool_id,
- 'reference' => $ref_id
- ])
- ->getResult();
- foreach ($sql_result as $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
- ) {
- $em = Database::getManager();
- $value = trim($value);
- if (empty($value)) {
- return false;
- }
- $course = $em->getRepository('ChamiloCoreBundle:Course')->findOneBy(['code' => $course_id]);
- $sfv = new \Chamilo\CoreBundle\Entity\SpecificFieldValues();
- $sfv
- ->setCourse($course)
- ->setCourseCode($course->getCode())
- ->setToolId($tool_id)
- ->setRefId($ref_id)
- ->setFieldId($id_specific_field)
- ->setValue($value);
- $em->persist($sfv);
- $em->flush();
- return $sfv->getId();
- }
- /**
- * 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) {
- $em = Database::getManager();
- $em
- ->createQuery('
- DELETE FROM ChamiloCoreBundle:SpecificFieldValues sfv
- WHERE sfv.courseCode = :course AND sfv.toolId = :tool AND sfv.refId = :ref AND sfv.fieldId = :field
- ')
- ->execute([
- 'course' => $course_id,
- 'tool' => $tool_id,
- 'ref' => $ref_id,
- 'field' => $id_specific_field
- ]);
- }
- /**
- * 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) {
- $em = Database::getManager();
- $em
- ->createQuery('
- DELETE FROM ChamiloCodeBundle:SpecificFieldValues sfv
- WHERE svf.courseCode = :course AND svf.toolId = :tool AND sfv.refId = :ref
- ')
- ->execute([
- 'course' => $course_id,
- 'tool' => $tool_id,
- 'ref' => $ref_id
- ]);
- }
- /**
- * 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 = array(
- '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 = array();
- 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;
- }
|