gradebook_functions.inc.php 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * These are functions used in gradebook
  5. *
  6. * @author Stijn Konings <konings.stijn@skynet.be>, Hogeschool Ghent
  7. * @author Julio Montoya <gugli100@gmail.com> adding security functions
  8. * @version april 2007
  9. */
  10. require_once ('gradebook_functions_users.inc.php');
  11. /**
  12. * Adds a resource to the unique gradebook of a given course
  13. * @param string Course code
  14. * @param int Resource type (use constants defined in linkfactory.class.php)
  15. * @param int Resource ID in the corresponding tool
  16. * @param string Resource name to show in the gradebook
  17. * @param int Resource weight to set in the gradebook
  18. * @param int Resource max
  19. * @param string Resource description
  20. * @param string Date
  21. * @param int Visibility (0 hidden, 1 shown)
  22. * @param int Session ID (optional or 0 if not defined)
  23. * @return boolean True on success, false on failure
  24. */
  25. function add_resource_to_course_gradebook($course_code, $resource_type, $resource_id, $resource_name='', $weight=0, $max=0, $resource_description='', $date=null, $visible=0, $session_id = 0) {
  26. /* See defines in lib/be/linkfactory.class.php
  27. define('LINK_EXERCISE',1);
  28. define('LINK_DROPBOX',2);
  29. define('LINK_STUDENTPUBLICATION',3);
  30. define('LINK_LEARNPATH',4);
  31. define('LINK_FORUM_THREAD',5),
  32. define('LINK_WORK',6);
  33. */
  34. $category = 0;
  35. require_once api_get_path(SYS_CODE_PATH).'gradebook/lib/be.inc.php';
  36. $link= LinkFactory :: create($resource_type);
  37. $link->set_user_id(api_get_user_id());
  38. $link->set_course_code($course_code);
  39. // TODO find the corresponding category (the first one for this course, ordered by ID)
  40. $t = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY);
  41. $sql = "SELECT * FROM $t WHERE course_code = '".Database::escape_string($course_code)."' ";
  42. if (!empty($session_id)) {
  43. $sql .= " AND session_id = ".(int)$session_id;
  44. } else {
  45. $sql .= " AND (session_id IS NULL OR session_id = 0) ";
  46. }
  47. $sql .= " ORDER BY id";
  48. $res = Database::query($sql);
  49. if (Database::num_rows($res)<1){
  50. //there is no unique category for this course+session combination,
  51. // => create one
  52. $cat= new Category();
  53. if (!empty($session_id)) {
  54. $my_session_id=api_get_session_id();
  55. $s_name = api_get_session_name($my_session_id);
  56. $cat->set_name($course_code.' - '.get_lang('Session').' '.$s_name);
  57. $cat->set_session_id($session_id);
  58. } else {
  59. $cat->set_name($course_code);
  60. }
  61. $cat->set_course_code($course_code);
  62. $cat->set_description(null);
  63. $cat->set_user_id(api_get_user_id());
  64. $cat->set_parent_id(0);
  65. $cat->set_weight(100);
  66. $cat->set_visible(0);
  67. $can_edit = api_is_allowed_to_edit(true, true);
  68. if ($can_edit) {
  69. $cat->add();
  70. }
  71. $category = $cat->get_id();
  72. unset ($cat);
  73. } else {
  74. $row = Database::fetch_array($res);
  75. $category = $row['id'];
  76. }
  77. $link->set_category_id($category);
  78. if ($link->needs_name_and_description()) {
  79. $link->set_name($resource_name);
  80. } else {
  81. $link->set_ref_id($resource_id);
  82. }
  83. $link->set_weight($weight);
  84. if ($link->needs_max()) {
  85. $link->set_max($max);
  86. }
  87. if ($link->needs_name_and_description()) {
  88. $link->set_description($resource_description);
  89. }
  90. $link->set_visible(empty ($visible) ? 0 : 1);
  91. if (!empty($session_id)) {
  92. $link->set_session_id($session_id);
  93. }
  94. $link->add();
  95. return true;
  96. }
  97. function block_students() {
  98. if (!api_is_allowed_to_create_course()) {
  99. require_once (api_get_path(INCLUDE_PATH)."header.inc.php");
  100. api_not_allowed();
  101. }
  102. }
  103. /**
  104. * Returns the info header for the user result page
  105. * @param $userid
  106. */
  107. /**
  108. * Returns the course name from a given code
  109. * @param string $code
  110. */
  111. function get_course_name_from_code($code) {
  112. $tbl_main_categories= Database :: get_main_table(TABLE_MAIN_COURSE);
  113. $sql= 'SELECT title, code FROM ' . $tbl_main_categories . 'WHERE code = "' . Database::escape_string($code) . '"';
  114. $result= Database::query($sql);
  115. if ($col= Database::fetch_array($result)) {
  116. return $col['title'];
  117. }
  118. }
  119. /**
  120. * Builds an img tag for a gradebook item
  121. * @param string $type value returned by a gradebookitem's get_icon_name()
  122. */
  123. function build_type_icon_tag($kind) {
  124. return '<img src="' . get_icon_file_name ($kind) . '" border="0" hspace="5" align="middle" alt="" />';
  125. }
  126. /**
  127. * Returns the icon filename for a gradebook item
  128. * @param string $type value returned by a gradebookitem's get_icon_name()
  129. */
  130. function get_icon_file_name ($type) {
  131. switch ($type) {
  132. case 'cat':
  133. $icon = 'gradebook.gif';
  134. break;
  135. case 'evalempty':
  136. $icon = 'empty.gif';
  137. break;
  138. case 'evalnotempty':
  139. $icon = 'gradebook_eval_not_empty.gif';
  140. break;
  141. case 'exercise':
  142. $icon = 'quiz.gif';
  143. break;
  144. case 'learnpath':
  145. $icon = 'scorm.gif';
  146. break;
  147. case 'studentpublication':
  148. $icon = 'works.gif';
  149. break;
  150. case 'link':
  151. $icon = 'link.gif';
  152. break;
  153. case 'forum':
  154. $icon = 'forum.gif';
  155. break;
  156. case 'attendance':
  157. $icon = 'attendance.gif';
  158. break;
  159. case 'survey':
  160. $icon = 'survey.gif';
  161. break;
  162. case 'dropbox':
  163. $icon = 'dropbox.gif';
  164. break;
  165. default:
  166. $icon = 'link.gif';
  167. break;
  168. }
  169. return api_get_path(WEB_IMG_PATH).$icon;
  170. }
  171. /**
  172. * Builds the course or platform admin icons to edit a category
  173. * @param object $cat category object
  174. * @param int $selectcat id of selected category
  175. */
  176. function build_edit_icons_cat($cat, $selectcat) {
  177. $show_message=$cat->show_message_resource_delete($cat->get_course_code());
  178. if ($show_message===false) {
  179. $visibility_icon= ($cat->is_visible() == 0) ? 'invisible' : 'visible';
  180. $visibility_command= ($cat->is_visible() == 0) ? 'set_visible' : 'set_invisible';
  181. $modify_icons= '<a href="gradebook_edit_cat.php?editcat=' . $cat->get_id() . ' &amp;cidReq='.$cat->get_course_code().'"><img src="../img/edit.gif" border="0" title="' . get_lang('Modify') . '" alt="" /></a>';
  182. $modify_icons .= '&nbsp;<a href="' . api_get_self() . '?deletecat=' . $cat->get_id() . '&amp;selectcat=' . $selectcat . '&amp;cidReq='.$cat->get_course_code().'" onclick="return confirmation();"><img src="../img/delete.gif" border="0" title="' . get_lang('DeleteAll') . '" alt="" /></a>';
  183. //no move ability for root categories
  184. if ($cat->is_movable()) {
  185. $modify_icons .= '&nbsp;<a href="' . api_get_self() . '?movecat=' . $cat->get_id() . '&amp;selectcat=' . $selectcat . ' &amp;cidReq='.$cat->get_course_code().'"><img src="../img/deplacer_fichier.gif" border="0" title="' . get_lang('Move') . '" alt="" /></a>';
  186. } else {
  187. //$modify_icons .= '&nbsp;<img src="../img/deplacer_fichier_na.gif" border="0" title="' . get_lang('Move') . '" alt="" />';
  188. }
  189. $modify_icons .= '&nbsp;<a href="' . api_get_self() . '?visiblecat=' . $cat->get_id() . '&amp;' . $visibility_command . '=&amp;selectcat=' . $selectcat . ' "><img src="../img/' . $visibility_icon . '.gif" border="0" title="' . get_lang('Visible') . '" alt="" /></a>';
  190. return $modify_icons;
  191. }
  192. }
  193. /**
  194. * Builds the course or platform admin icons to edit an evaluation
  195. * @param object $eval evaluation object
  196. * @param int $selectcat id of selected category
  197. */
  198. function build_edit_icons_eval($eval, $selectcat) {
  199. $status=CourseManager::get_user_in_course_status(api_get_user_id(), api_get_course_id());
  200. $eval->get_course_code();
  201. $cat=new Category();
  202. $message_eval=$cat->show_message_resource_delete($eval->get_course_code());
  203. if ($message_eval===false) {
  204. $visibility_icon= ($eval->is_visible() == 0) ? 'invisible' : 'visible';
  205. $visibility_command= ($eval->is_visible() == 0) ? 'set_visible' : 'set_invisible';
  206. $modify_icons= '<a href="gradebook_edit_eval.php?editeval=' . $eval->get_id() . ' &amp;cidReq='.$eval->get_course_code().'"><img src="../img/edit.gif" border="0" title="' . get_lang('Modify') . '" alt="" /></a>';
  207. $modify_icons .= '&nbsp;<a href="' . api_get_self() . '?deleteeval=' . $eval->get_id() . '&selectcat=' . $selectcat . ' &amp;cidReq='.$eval->get_course_code().'" onclick="return confirmation();"><img src="../img/delete.gif" border="0" title="' . get_lang('Delete') . '" alt="" /></a>';
  208. //$modify_icons .= '&nbsp;<a href="' . api_get_self() . '?moveeval=' . $eval->get_id() . '&selectcat=' . $selectcat . '"><img src="../img/deplacer_fichier.gif" border="0" title="' . get_lang('Move') . '" alt="" /></a>';
  209. $modify_icons .= '&nbsp;<a href="' . api_get_self() . '?visibleeval=' . $eval->get_id() . '&amp;' . $visibility_command . '=&amp;selectcat=' . $selectcat . ' "><img src="../img/' . $visibility_icon . '.gif" border="0" title="' . get_lang('Visible') . '" alt="" /></a>';
  210. if (api_is_allowed_to_edit(null, true)){
  211. $modify_icons .= '&nbsp;<a href="gradebook_showlog_eval.php?visiblelog=' . $eval->get_id() . '&amp;selectcat=' . $selectcat . ' &amp;cidReq='.$eval->get_course_code().'"><img src="../img/file_txt_small.gif" border="0" title="' . get_lang('GradebookQualifyLog') . '" alt="" /></a>';
  212. }
  213. return $modify_icons;
  214. }
  215. }
  216. /**
  217. * Builds the course or platform admin icons to edit a link
  218. * @param object $linkobject
  219. * @param int $selectcat id of selected category
  220. */
  221. function build_edit_icons_link($link, $selectcat) {
  222. $link->get_course_code();
  223. $cat=new Category();
  224. $message_link=$cat->show_message_resource_delete($link->get_course_code());
  225. if ($message_link===false) {
  226. $visibility_icon= ($link->is_visible() == 0) ? 'invisible' : 'visible';
  227. $visibility_command= ($link->is_visible() == 0) ? 'set_visible' : 'set_invisible';
  228. $modify_icons= '<a href="gradebook_edit_link.php?editlink=' . $link->get_id() . ' &amp;cidReq='.$link->get_course_code().'"><img src="../img/edit.gif" border="0" title="' . get_lang('Modify') . '" alt="" /></a>';
  229. $modify_icons .= '&nbsp;<a href="' . api_get_self() . '?deletelink=' . $link->get_id() . '&selectcat=' . $selectcat . ' &amp;cidReq='.$link->get_course_code().'" onclick="return confirmation();"><img src="../img/delete.gif" border="0" title="' . get_lang('Delete') . '" alt="" /></a>';
  230. //$modify_icons .= '&nbsp;<a href="' . api_get_self() . '?movelink=' . $link->get_id() . '&selectcat=' . $selectcat . '"><img src="../img/deplacer_fichier.gif" border="0" title="' . get_lang('Move') . '" alt="" /></a>';
  231. $modify_icons .= '&nbsp;<a href="' . api_get_self() . '?visiblelink=' . $link->get_id() . '&amp;' . $visibility_command . '=&amp;selectcat=' . $selectcat . ' "><img src="../img/' . $visibility_icon . '.gif" border="0" title="' . get_lang('Visible') . '" alt="" /></a>';
  232. $modify_icons .= '&nbsp;<a href="gradebook_showlog_link.php?visiblelink=' . $link->get_id() . '&amp;selectcat=' . $selectcat . '&amp;cidReq='.$link->get_course_code().'"><img src="../img/file_txt_small.gif" border="0" title="' . get_lang('GradebookQualifyLog') . '" alt="" /></a>';
  233. //if (api_is_course_admin() == true) {
  234. //$modify_icons .= '&nbsp;<a href="gradebook_showlog_eval.php?visiblelog=' . $eval->get_id() . '&amp;' . $visibility_command . '=&amp;selectcat=' . $selectcat . '"><img src="../img/file_txt_small.gif" border="0" title="' . get_lang('GradebookQualifyLog') . '" alt="" /></a>';
  235. //}
  236. return $modify_icons;
  237. }
  238. }
  239. /**
  240. * Checks if a resource is in the unique gradebook of a given course
  241. * @param string Course code
  242. * @param int Resource type (use constants defined in linkfactory.class.php)
  243. * @param int Resource ID in the corresponding tool
  244. * @param int Session ID (optional - 0 if not defined)
  245. * @return int false on error or link ID
  246. */
  247. function is_resource_in_course_gradebook($course_code, $resource_type, $resource_id, $session_id = 0) {
  248. require_once api_get_path(SYS_CODE_PATH).'gradebook/lib/be/linkfactory.class.php';
  249. require_once api_get_path(SYS_CODE_PATH).'gradebook/lib/be.inc.php';
  250. require_once api_get_path(SYS_CODE_PATH).'gradebook/lib/be/linkfactory.class.php';
  251. // TODO find the corresponding category (the first one for this course, ordered by ID)
  252. $t = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY);
  253. $l = Database::get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  254. $sql = "SELECT * FROM $t WHERE course_code = '".Database::escape_string($course_code)."' ";
  255. if (!empty($session_id)) {
  256. $sql .= " AND session_id = ".(int)$session_id;
  257. } else {
  258. $sql .= " AND (session_id IS NULL OR session_id = 0) ";
  259. }
  260. $sql .= " ORDER BY id";
  261. $res = Database::query($sql);
  262. if (Database::num_rows($res)<1) {
  263. return false;
  264. }
  265. $row = Database::fetch_array($res);
  266. $category = $row['id'];
  267. $sql = "SELECT id FROM $l l WHERE l.category_id = $category AND type = ".(int) $resource_type." and ref_id = ".(int) $resource_id;
  268. $res = Database::query($sql);
  269. if (Database::num_rows($res)<1) {
  270. return false;
  271. }
  272. $row = Database::fetch_array($res);
  273. return $row['id'];
  274. }
  275. /**
  276. * Remove a resource from the unique gradebook of a given course
  277. * @param int Link/Resource ID
  278. * @return bool false on error, true on success
  279. */
  280. function remove_resource_from_course_gradebook($link_id) {
  281. if ( empty($link_id) ) { return false; }
  282. require_once (api_get_path(SYS_CODE_PATH).'gradebook/lib/be.inc.php');
  283. // TODO find the corresponding category (the first one for this course, ordered by ID)
  284. $l = Database::get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  285. $sql = "DELETE FROM $l WHERE id = ".(int)$link_id;
  286. $res = Database::query($sql);
  287. return true;
  288. }
  289. /**
  290. * Return the database name
  291. * @param int
  292. * @return String
  293. */
  294. function get_database_name_by_link_id($id_link) {
  295. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  296. $tbl_grade_links = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  297. $res=Database::query('SELECT db_name FROM '.$course_table.' c INNER JOIN '.$tbl_grade_links.' l
  298. ON c.code=l.course_code WHERE l.id='.intval($id_link).' OR l.category_id='.intval($id_link));
  299. $my_db_name=Database::fetch_array($res,'ASSOC');
  300. return $my_db_name['db_name'];
  301. }
  302. function get_table_type_course($type,$course) {
  303. global $_configuration;
  304. global $table_evaluated;
  305. return Database::get_course_table($table_evaluated[$type][0],$_configuration['db_prefix'].$course);
  306. }
  307. function get_printable_data($users,$alleval, $alllinks) {
  308. $datagen = new FlatViewDataGenerator ($users, $alleval, $alllinks);
  309. $offset = isset($_GET['offset']) ? $_GET['offset'] : '0';
  310. $offset = intval($offset);
  311. $count = (($offset + 10) > $datagen->get_total_items_count()) ? ($datagen->get_total_items_count() - $offset) : 10;
  312. $header_names = $datagen->get_header_names($offset, $count, true);
  313. $data_array = $datagen->get_data(FlatViewDataGenerator :: FVDG_SORT_LASTNAME, 0, null, $offset, $count, true,true);
  314. $newarray = array();
  315. foreach ($data_array as $data) {
  316. $newarray[] = array_slice($data, 1);
  317. }
  318. return array ($header_names, $newarray);
  319. }
  320. /**
  321. * XML-parser: handle character data
  322. */
  323. function character_data($parser, $data) {
  324. global $current_value;
  325. $current_value= $data;
  326. }
  327. /**
  328. * XML-parser: handle end of element
  329. */
  330. function element_end($parser, $data) {
  331. global $user;
  332. global $users;
  333. global $current_value;
  334. switch ($data) {
  335. case 'Result' :
  336. $users[]= $user;
  337. break;
  338. default :
  339. $user[$data]= $current_value;
  340. break;
  341. }
  342. }
  343. /**
  344. * XML-parser: handle start of element
  345. */
  346. function element_start($parser, $data) {
  347. global $user;
  348. global $current_tag;
  349. switch ($data) {
  350. case 'Result' :
  351. $user= array ();
  352. break;
  353. default :
  354. $current_tag= $data;
  355. }
  356. }
  357. function overwritescore($resid, $importscore, $eval_max) {
  358. $result= Result :: load($resid);
  359. if ($importscore > $eval_max) {
  360. header('Location: gradebook_view_result.php?selecteval=' .Security::remove_XSS($_GET['selecteval']) . '&overwritemax=');
  361. exit;
  362. }
  363. $result[0]->set_score($importscore);
  364. $result[0]->save();
  365. unset ($result);
  366. }
  367. /**
  368. * Read the XML-file
  369. * @param string $file Path to the XML-file
  370. * @return array All userinformation read from the file
  371. */
  372. function parse_xml_data($file) {
  373. global $current_tag;
  374. global $current_value;
  375. global $user;
  376. global $users;
  377. $users= array ();
  378. $parser= xml_parser_create();
  379. xml_set_element_handler($parser, 'element_start', 'element_end');
  380. xml_set_character_data_handler($parser, "character_data");
  381. xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, false);
  382. xml_parse($parser, file_get_contents($file));
  383. xml_parser_free($parser);
  384. return $users;
  385. }
  386. /**
  387. * update user info about certificate
  388. * @param int The category id
  389. * @param int The user id
  390. * @param string the path name of the certificate
  391. * @return void()
  392. */
  393. function update_user_info_about_certificate ($cat_id,$user_id,$path_certificate) {
  394. $table_certificate = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
  395. if (!UserManager::is_user_certified($cat_id,$user_id)) {
  396. $sql='UPDATE '.$table_certificate.' SET path_certificate="'.Database::escape_string($path_certificate).'"
  397. WHERE cat_id="'.intval($cat_id).'" AND user_id="'.intval($user_id).'" ';
  398. $rs=Database::query($sql);
  399. }
  400. }
  401. /**
  402. * register user info about certificate
  403. * @param int The category id
  404. * @param int The user id
  405. * @param float The score obtained for certified
  406. * @param Datetime The date when you obtained the certificate
  407. * @return void()
  408. */
  409. function register_user_info_about_certificate ($cat_id,$user_id,$score_certificate, $date_certificate) {
  410. $table_certificate = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
  411. $sql_exist='SELECT COUNT(*) as count FROM '.$table_certificate.' gc
  412. WHERE gc.cat_id="'.intval($cat_id).'" AND user_id="'.intval($user_id).'" ';
  413. $rs_exist=Database::query($sql_exist);
  414. $row=Database::fetch_array($rs_exist);
  415. if ($row['count']==0) {
  416. $sql='INSERT INTO '.$table_certificate.' (cat_id,user_id,score_certificate,created_at)
  417. VALUES("'.intval($cat_id).'","'.intval($user_id).'","'.Database::escape_string($score_certificate).'","'.Database::escape_string($date_certificate).'")';
  418. $rs=Database::query($sql);
  419. }
  420. }
  421. /**
  422. * Get date of user certificate
  423. * @param int The category id
  424. * @param int The user id
  425. * @return Datetime The date when you obtained the certificate
  426. */
  427. function get_certificate_date_by_user_id ($cat_id,$user_id) {
  428. $table_certificate = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
  429. $sql_get_date='SELECT created_at FROM '.$table_certificate.' WHERE cat_id="'.intval($cat_id).'" AND user_id="'.intval($user_id).'"';
  430. $rs_get_date=Database::query($sql_get_date);
  431. $row_get_date=Database::fetch_array($rs_get_date,'ASSOC');
  432. return $row_get_date['created_at'];
  433. }
  434. /**
  435. * Get list of users certificates
  436. * @param int The category id
  437. * @return array
  438. */
  439. function get_list_users_certificates ($cat_id=null) {
  440. $table_certificate = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
  441. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  442. $sql='SELECT DISTINCT u.user_id,u.lastname,u.firstname,u.username FROM '.$table_user.' u INNER JOIN '.$table_certificate.' gc
  443. ON u.user_id=gc.user_id ';
  444. if (!is_null($cat_id) && $cat_id>0) {
  445. $sql.=' WHERE cat_id='.Database::escape_string($cat_id);
  446. }
  447. $sql.=' ORDER BY u.firstname';
  448. $rs=Database::query($sql);
  449. $list_users=array();
  450. while ($row=Database::fetch_array($rs)) {
  451. $list_users[]=$row;
  452. }
  453. return $list_users;
  454. }
  455. /**
  456. *Gets the certificate list by user id
  457. *@param int The user id
  458. *@param int The category id
  459. *@retun array
  460. */
  461. function get_list_gradebook_certificates_by_user_id ($user_id,$cat_id=null) {
  462. $table_certificate = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
  463. $sql='SELECT gc.score_certificate,gc.created_at,gc.path_certificate,gc.cat_id,gc.user_id FROM '.$table_certificate.' gc
  464. WHERE gc.user_id="'.Database::escape_string($user_id).'" ';
  465. if (!is_null($cat_id) && $cat_id>0) {
  466. $sql.=' AND cat_id='.Database::escape_string($cat_id);
  467. }
  468. $rs = Database::query($sql);
  469. $list_certificate=array();
  470. while ($row=Database::fetch_array($rs)) {
  471. $list_certificate[]=$row;
  472. }
  473. return $list_certificate;
  474. }
  475. /**
  476. * Deletes a certificate
  477. * @param int The category id
  478. * @param int The user id
  479. * @return boolean
  480. */
  481. function delete_certificate ($cat_id,$user_id) {
  482. $table_certificate = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
  483. $sql_verified='SELECT count(*) AS count,path_certificate as path,user_id FROM '.$table_certificate.' gc WHERE cat_id="'.Database::escape_string($cat_id).'" AND user_id="'.Database::escape_string($user_id).'" GROUP BY user_id,cat_id';
  484. $rs_verified=Database::query($sql_verified);
  485. $path=Database::result($rs_verified,0,'path');
  486. $user_id=Database::result($rs_verified,0,'user_id');
  487. if (!is_null($path) || $path!='' || strlen($path)) {
  488. $path_info= UserManager::get_user_picture_path_by_id($user_id,'system',true);
  489. $path_directory_user_certificate=$path_info['dir'].'certificate'.$path;
  490. if (is_file($path_directory_user_certificate)) {
  491. @unlink($path_directory_user_certificate);
  492. if (is_file($path_directory_user_certificate)===false) {
  493. $delete_db=true;
  494. } else {
  495. $delete_db=false;
  496. }
  497. }
  498. if (Database::result($rs_verified,0,'count')==1 && $delete_db===true) {
  499. $sql_delete='DELETE FROM '.$table_certificate.' WHERE cat_id="'.Database::escape_string($cat_id).'" AND user_id="'.Database::escape_string($user_id).'" ';
  500. $rs_delete=Database::query($sql_delete);
  501. return true;
  502. } else {
  503. return false;
  504. }
  505. } else {
  506. //path is not generate delete only the DB record
  507. $sql_delete='DELETE FROM '.$table_certificate.' WHERE cat_id="'.Database::escape_string($cat_id).'" AND user_id="'.Database::escape_string($user_id).'" ';
  508. $rs_delete=Database::query($sql_delete);
  509. return true;
  510. }
  511. }