specific_fields_manager.lib.php 8.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322
  1. <?php
  2. /**
  3. * Manage specific tools
  4. * @package chamilo.library
  5. */
  6. // Database table definitions
  7. /**
  8. * Add a specific field
  9. * @param string $name specific field name
  10. */
  11. function add_specific_field($name)
  12. {
  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) {
  23. return false;
  24. }
  25. $sql = sprintf($sql, $table_sf, $_safe_code, $_safe_name);
  26. $result = Database::query($sql);
  27. if ($result) {
  28. return Database::insert_id();
  29. } else {
  30. return false;
  31. }
  32. }
  33. /**
  34. * Delete a specific field
  35. * @param int $id specific field id
  36. */
  37. function delete_specific_field($id)
  38. {
  39. $table_sf = Database:: get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
  40. $id = (int)$id;
  41. if (!is_numeric($id)) {
  42. return false;
  43. }
  44. $sql = 'DELETE FROM %s WHERE id=%s LIMIT 1';
  45. $sql = sprintf($sql, $table_sf, $id);
  46. Database::query($sql);
  47. //TODO also delete the corresponding values
  48. }
  49. /**
  50. * Edit a specific field
  51. * @param int $id specific field id
  52. * @param string $name new field name
  53. */
  54. function edit_specific_field($id, $name)
  55. {
  56. $table_sf = Database:: get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
  57. $id = (int)$id;
  58. if (!is_numeric($id)) {
  59. return false;
  60. }
  61. $sql = 'UPDATE %s SET name = \'%s\' WHERE id = %s LIMIT 1';
  62. $sql = sprintf($sql, $table_sf, $name, $id);
  63. Database::query($sql);
  64. }
  65. /**
  66. * @param array $conditions a list of condition (exemple : status=>STUDENT)
  67. * @param array $order_by a list of fields on which to sort
  68. * @return array An array with all specific fields, at platform level
  69. */
  70. function get_specific_field_list($conditions = array(), $order_by = array())
  71. {
  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(
  98. $conditions = array(),
  99. $order_by = array()
  100. ) {
  101. $table_sfv = Database:: get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES);
  102. $return_array = array();
  103. $sql = "SELECT * FROM $table_sfv";
  104. if (count($conditions) > 0) {
  105. $sql .= ' WHERE ';
  106. //If any course_code nor c_id is provided try to insert the current course id
  107. if (!isset($conditions['c_id']) && !isset($conditions['course_code'])) {
  108. $conditions['c_id'] = " " . api_get_course_int_id();
  109. }
  110. $conditions_string_array = array();
  111. foreach ($conditions as $field => $value) {
  112. $conditions_string_array[] = $field.' = '.$value;
  113. }
  114. $sql .= implode(' AND ', $conditions_string_array);
  115. }
  116. if (count($order_by) > 0) {
  117. $sql .= ' ORDER BY '.implode(',', $order_by);
  118. }
  119. $sql_result = Database::query($sql);
  120. while ($result = Database::fetch_array($sql_result)) {
  121. $return_array[] = $result;
  122. }
  123. return $return_array;
  124. }
  125. /**
  126. * @param char $prefix xapian prefix
  127. * @param string $course_code
  128. * @param string $tool_id Constant from mainapi.lib.php
  129. * @param int $ref_id representative id inside one tool item
  130. * @return array
  131. */
  132. function get_specific_field_values_list_by_prefix(
  133. $prefix,
  134. $course_code,
  135. $tool_id,
  136. $ref_id
  137. ) {
  138. $em = Database::getManager();
  139. $course = $em->getRepository('ChamiloCoreBundle:Course')->findOneBy(['code' => $course_code]);
  140. $sql_result = $em
  141. ->createQuery('
  142. SELECT sfv.value
  143. FROM ChamiloCoreBundle:SpecificField sf
  144. LEFT JOIN ChamiloCoreBundle:SpecificFieldValues sfv WITH sf = sfv.fieldId
  145. WHERE sf.code = :code AND sfv.course = :course AND sfv.toolId = :tool AND sfv.refI = :reference
  146. ')
  147. ->setParameters([
  148. 'code' => $prefix,
  149. 'course' => $course,
  150. 'tool' => $tool_id,
  151. 'reference' => $ref_id
  152. ])
  153. ->getResult();
  154. foreach ($sql_result as $result) {
  155. $return_array[] = $result;
  156. }
  157. return $return_array;
  158. }
  159. /**
  160. * Add a specific field value
  161. * @param int $id_specific_field specific field id
  162. * @param string $course_id course code
  163. * @param string $tool_id tool id, from main.api.lib
  164. * @param int $ref_id intern id inside specific tool table
  165. * @param string $value specific field value
  166. */
  167. function add_specific_field_value(
  168. $id_specific_field,
  169. $course_id,
  170. $tool_id,
  171. $ref_id,
  172. $value
  173. ) {
  174. $em = Database::getManager();
  175. $value = trim($value);
  176. if (empty($value)) {
  177. return false;
  178. }
  179. $course = $em->getRepository('ChamiloCoreBundle:Course')->findOneBy(['code' => $course_id]);
  180. $sfv = new \Chamilo\CoreBundle\Entity\SpecificFieldValues();
  181. $sfv
  182. ->setCourse($course)
  183. ->setCourseCode($course->getCode())
  184. ->setToolId($tool_id)
  185. ->setRefId($ref_id)
  186. ->setFieldId($id_specific_field)
  187. ->setValue($value);
  188. $em->persist($sfv);
  189. $em->flush();
  190. return $sfv->getId();
  191. }
  192. /**
  193. * Delete all values from a specific field id, course_id, ref_id and tool
  194. * @param string $course_id course code
  195. * @param int $id_specific_field specific field id
  196. * @param string $tool_id tool id, from main.api.lib
  197. * @param int $ref_id intern id inside specific tool table
  198. */
  199. function delete_all_specific_field_value($course_id, $id_specific_field, $tool_id, $ref_id) {
  200. $em = Database::getManager();
  201. $em
  202. ->createQuery('
  203. DELETE FROM ChamiloCoreBundle:SpecificFieldValues sfv
  204. WHERE sfv.courseCode = :course AND sfv.toolId = :tool AND sfv.refId = :ref AND sfv.fieldId = :field
  205. ')
  206. ->execute([
  207. 'course' => $course_id,
  208. 'tool' => $tool_id,
  209. 'ref' => $ref_id,
  210. 'field' => $id_specific_field
  211. ]);
  212. }
  213. /**
  214. * Delete all values from a specific item (course_id, tool_id and ref_id).
  215. * To be used when deleting such item from Dokeos
  216. * @param string Course code
  217. * @param string Tool ID
  218. * @param int Internal ID used in specific tool table
  219. */
  220. function delete_all_values_for_item($course_id, $tool_id, $ref_id) {
  221. $em = Database::getManager();
  222. $em
  223. ->createQuery('
  224. DELETE FROM ChamiloCodeBundle:SpecificFieldValues sfv
  225. WHERE svf.courseCode = :course AND svf.toolId = :tool AND sfv.refId = :ref
  226. ')
  227. ->execute([
  228. 'course' => $course_id,
  229. 'tool' => $tool_id,
  230. 'ref' => $ref_id
  231. ]);
  232. }
  233. /**
  234. * Generates a code (one-letter string) for a given field name
  235. * Defaults to the first letter of the name, otherwise iterate through available
  236. * letters
  237. * @param string Name
  238. * @return string One-letter code, upper-case
  239. */
  240. function get_specific_field_code_from_name($name) {
  241. // Z is used internally by Xapian
  242. // O & C already used by tool_id and course_id
  243. $list = array(
  244. 'A',
  245. 'B',
  246. 'D',
  247. 'E',
  248. 'F',
  249. 'G',
  250. 'H',
  251. 'I',
  252. 'J',
  253. 'K',
  254. 'L',
  255. 'M',
  256. 'N',
  257. 'P',
  258. 'Q',
  259. 'R',
  260. 'S',
  261. 'T',
  262. 'U',
  263. 'V',
  264. 'W',
  265. 'X',
  266. 'Y',
  267. );
  268. $table_sf = Database:: get_main_table(TABLE_MAIN_SPECIFIC_FIELD);
  269. $sql = "SELECT code FROM $table_sf ORDER BY code";
  270. $res = Database::query($sql);
  271. $code = strtoupper(substr($name, 0, 1));
  272. //if no code exists in DB, return current one
  273. if (Database::num_rows($res) < 1) {
  274. return $code;
  275. }
  276. $existing_list = array();
  277. while ($row = Database::fetch_array($res)) {
  278. $existing_list[] = $row['code'];
  279. }
  280. //if the current code doesn't exist in DB, return current one
  281. if (!in_array($code, $existing_list)) {
  282. return $code;
  283. }
  284. $idx = array_search($code, $list);
  285. $c = count($list);
  286. for ($i = $idx + 1, $j = 0; $j < $c; $i++, $j++) {
  287. if (!in_array($list[$i], $existing_list)) {
  288. return $idx[$i];
  289. }
  290. }
  291. // all 26 codes are used
  292. return false;
  293. }