specific_fields_manager.lib.php 7.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230
  1. <?php
  2. /**
  3. * Manage specific tools
  4. * @package chamilo.library
  5. */
  6. /**
  7. * Code
  8. */
  9. // Database table definitions
  10. $table_sf = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
  11. $table_sf_val = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
  12. /**
  13. * Add a specific field
  14. * @param string $name specific field name
  15. */
  16. function add_specific_field($name) {
  17. $table_sf = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
  18. $name = trim($name);
  19. if (empty($name)) {
  20. return FALSE;
  21. }
  22. $sql = 'INSERT INTO %s(id, code, name) VALUES(NULL, \'%s\', \'%s\')';
  23. $_safe_name = Database::escape_string($name);
  24. $_safe_code = substr($_safe_name,0,1);
  25. $_safe_code = get_specific_field_code_from_name($_safe_code);
  26. if ($_safe_code === false) { return false; }
  27. $sql = sprintf($sql, $table_sf, $_safe_code, $_safe_name);
  28. $result = Database::query($sql);
  29. if ($result) {
  30. return Database::insert_id();
  31. }
  32. else {
  33. return FALSE;
  34. }
  35. }
  36. /**
  37. * Delete a specific field
  38. * @param int $id specific field id
  39. */
  40. function delete_specific_field($id) {
  41. $table_sf = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
  42. $id = (int)$id;
  43. if (!is_numeric($id)) {
  44. return FALSE;
  45. }
  46. $sql = 'DELETE FROM %s WHERE id=%s LIMIT 1';
  47. $sql = sprintf($sql, $table_sf, $id);
  48. $result = Database::query($sql);
  49. //TODO also delete the corresponding values
  50. }
  51. /**
  52. * Edit a specific field
  53. * @param int $id specific field id
  54. * @param string $name new field name
  55. */
  56. function edit_specific_field($id, $name) {
  57. $table_sf = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
  58. $id = (int)$id;
  59. if (!is_numeric($id)) {
  60. return FALSE;
  61. }
  62. $sql = 'UPDATE %s SET name = \'%s\' WHERE id = %s LIMIT 1';
  63. $sql = sprintf($sql, $table_sf, $name, $id);
  64. $result = Database::query($sql);
  65. }
  66. /**
  67. * @param array $conditions a list of condition (exemple : status=>STUDENT)
  68. * @param array $order_by a list of fields on which to sort
  69. * @return array An array with all specific fields, at platform level
  70. */
  71. function get_specific_field_list($conditions = array(), $order_by = array()) {
  72. $table_sf = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
  73. $return_array = array();
  74. $sql = "SELECT * FROM $table_sf";
  75. if (count($conditions) > 0) {
  76. $sql .= ' WHERE ';
  77. $conditions_string_array = array();
  78. foreach ($conditions as $field => $value) {
  79. $conditions_string_array[] = $field.' = '. $value;
  80. }
  81. $sql .= implode(' AND ', $conditions_string_array);
  82. }
  83. if (count($order_by) > 0) {
  84. $sql .= ' ORDER BY '.implode(',',$order_by);
  85. }
  86. $sql_result = Database::query($sql);
  87. while ($result = Database::fetch_array($sql_result)) {
  88. $return_array[] = $result;
  89. }
  90. return $return_array;
  91. }
  92. /**
  93. * @param array $conditions a list of condition (exemple : status=>STUDENT)
  94. * @param array $order_by a list of fields on which sort
  95. * @return array An array with all users of the platform.
  96. */
  97. function get_specific_field_values_list($conditions = array(), $order_by = array()) {
  98. $table_sfv = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
  99. $return_array = array();
  100. $sql = "SELECT * FROM $table_sfv";
  101. if (count($conditions) > 0) {
  102. $sql .= ' WHERE ';
  103. $conditions_string_array = array();
  104. foreach ($conditions as $field => $value) {
  105. $conditions_string_array[] = $field.' = '. $value;
  106. }
  107. $sql .= implode(' AND ', $conditions_string_array);
  108. }
  109. if (count($order_by) > 0) {
  110. $sql .= ' ORDER BY '.implode(',',$order_by);
  111. }
  112. $sql_result = Database::query($sql);
  113. while ($result = Database::fetch_array($sql_result)) {
  114. $return_array[] = $result;
  115. }
  116. return $return_array;
  117. }
  118. /**
  119. * @param char $prefix xapian prefix
  120. * @param string $course_code
  121. * @param string $tool_id Constant from mainapi.lib.php
  122. * @param int $ref_id representative id inside one tool item
  123. * @return array
  124. */
  125. function get_specific_field_values_list_by_prefix($prefix, $course_code, $tool_id, $ref_id) {
  126. $table_sf = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
  127. $table_sfv = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
  128. $sql = 'SELECT sfv.value FROM %s sf LEFT JOIN %s sfv ON sf.id = sfv.field_id' .
  129. ' WHERE sf.code = \'%s\' AND sfv.course_code = \'%s\' AND tool_id = \'%s\' AND sfv.ref_id = %s';
  130. $sql = sprintf($sql, $table_sf, $table_sfv, $prefix, $course_code, $tool_id, $ref_id);
  131. $sql_result = Database::query($sql);
  132. while ($result = Database::fetch_array($sql_result)) {
  133. $return_array[] = $result;
  134. }
  135. return $return_array;
  136. }
  137. /**
  138. * Add a specific field value
  139. * @param int $id_specific_field specific field id
  140. * @param string $course_id course code
  141. * @param string $tool_id tool id, from main.api.lib
  142. * @param int $ref_id intern id inside specific tool table
  143. * @param string $value specific field value
  144. */
  145. function add_specific_field_value($id_specific_field, $course_id, $tool_id, $ref_id, $value) {
  146. $table_sf_values = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
  147. $value = trim($value);
  148. if (empty($value)) {
  149. return false;
  150. }
  151. $sql = 'INSERT INTO %s(id, course_code, tool_id, ref_id, field_id, value) VALUES(NULL, \'%s\', \'%s\', %s, %s, \'%s\')';
  152. $sql = sprintf($sql, $table_sf_values, $course_id, $tool_id, $ref_id, $id_specific_field, Database::escape_string($value));
  153. $result = Database::query($sql);
  154. if ($result) {
  155. return Database::insert_id();
  156. } else {
  157. return false;
  158. }
  159. }
  160. /**
  161. * Delete all values from a specific field id, course_id, ref_id and tool
  162. * @param string $course_id course code
  163. * @param int $id_specific_field specific field id
  164. * @param string $tool_id tool id, from main.api.lib
  165. * @param int $ref_id intern id inside specific tool table
  166. */
  167. function delete_all_specific_field_value($course_id, $id_specific_field, $tool_id, $ref_id) {
  168. $table_sf_values = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
  169. $sql = 'DELETE FROM %s WHERE course_code = \'%s\' AND tool_id = \'%s\' AND ref_id = %s AND field_id = %s';
  170. $sql = sprintf($sql, $table_sf_values, $course_id, $tool_id, $ref_id, $id_specific_field);
  171. $result = Database::query($sql);
  172. }
  173. /**
  174. * Delete all values from a specific item (course_id, tool_id and ref_id).
  175. * To be used when deleting such item from Dokeos
  176. * @param string Course code
  177. * @param string Tool ID
  178. * @param int Internal ID used in specific tool table
  179. */
  180. function delete_all_values_for_item($course_id, $tool_id, $ref_id) {
  181. $table_sf_values = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
  182. $sql = 'DELETE FROM %s WHERE course_code = \'%s\' AND tool_id = \'%s\' AND ref_id = %s';
  183. $sql = sprintf($sql, $table_sf_values, $course_id, $tool_id, $ref_id);
  184. $result = Database::query($sql);
  185. }
  186. /**
  187. * Generates a code (one-letter string) for a given field name
  188. * Defaults to the first letter of the name, otherwise iterate through available
  189. * letters
  190. * @param string Name
  191. * @return string One-letter code, upper-case
  192. */
  193. function get_specific_field_code_from_name($name) {
  194. // Z is used internally by Xapian
  195. // O & C already used by tool_id and course_id
  196. $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');
  197. $table_sf = Database :: get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
  198. $sql = "SELECT code FROM $table_sf ORDER BY code";
  199. $res = Database::query($sql);
  200. $code = strtoupper(substr($name,0,1));
  201. //if no code exists in DB, return current one
  202. if (Database::num_rows($res)<1) { return $code;}
  203. $existing_list = array();
  204. while ($row = Database::fetch_array($res)) {
  205. $existing_list[] = $row['code'];
  206. }
  207. //if the current code doesn't exist in DB, return current one
  208. if (!in_array($code,$existing_list)) { return $code;}
  209. $idx = array_search($code,$list);
  210. $c = count($list);
  211. for ($i = $idx+1, $j=0 ; $j<$c ; $i++, $j++) {
  212. if (!in_array($list[$i],$existing_list)) { return $idx[$i]; }
  213. }
  214. // all 26 codes are used
  215. return false;
  216. }