gradebook_functions.inc.php 25 KB

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