specific_fields_manager.lib.php 8.1 KB

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