urlmanager.lib.php 39 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * Class UrlManager
  5. * This library provides functions for the access_url management.
  6. * Include/require it in your code to use its functionality.
  7. *
  8. * @package chamilo.library
  9. */
  10. class UrlManager
  11. {
  12. /**
  13. * Creates a new url access
  14. *
  15. * @author Julio Montoya <gugli100@gmail.com>,
  16. *
  17. * @param string $url The URL of the site
  18. * @param string $description The description of the site
  19. * @param int $active is active or not
  20. * @return boolean if success
  21. */
  22. public static function add($url, $description, $active)
  23. {
  24. $tms = time();
  25. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  26. $sql = "INSERT INTO $table
  27. SET url = '".Database::escape_string($url)."',
  28. description = '".Database::escape_string($description)."',
  29. active = '".intval($active)."',
  30. created_by = '".api_get_user_id()."',
  31. tms = FROM_UNIXTIME(".$tms.")";
  32. $result = Database::query($sql);
  33. return $result;
  34. }
  35. /**
  36. * Updates an URL access
  37. * @author Julio Montoya <gugli100@gmail.com>,
  38. *
  39. * @param int $url_id The url id
  40. * @param string $url
  41. * @param string $description The description of the site
  42. * @param int $active is active or not
  43. * @return boolean if success
  44. */
  45. public static function update($url_id, $url, $description, $active)
  46. {
  47. $url_id = intval($url_id);
  48. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  49. $sql = "UPDATE $table
  50. SET url = '".Database::escape_string($url)."',
  51. description = '".Database::escape_string($description)."',
  52. active = '".intval($active)."',
  53. created_by = '".api_get_user_id()."',
  54. tms = '".api_get_utc_datetime()."'
  55. WHERE id = '$url_id'";
  56. $result = Database::query($sql);
  57. return $result;
  58. }
  59. /**
  60. * Deletes an url
  61. * @author Julio Montoya
  62. * @param int $id url id
  63. *
  64. * @return boolean true if success
  65. * */
  66. public static function delete($id)
  67. {
  68. $id = intval($id);
  69. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  70. $tableUser = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  71. $tableCourse = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  72. $tableSession = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  73. $tableCourseCategory = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  74. $tableGroup = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  75. $sql = "DELETE FROM $tableCourse WHERE access_url_id = ".$id;
  76. Database::query($sql);
  77. /*
  78. $sql = "DELETE FROM $tableCourseCategory WHERE access_url_id = ".$id;
  79. Database::query($sql);
  80. */
  81. $sql = "DELETE FROM $tableSession WHERE access_url_id = ".$id;
  82. Database::query($sql);
  83. $sql = "DELETE FROM $tableGroup WHERE access_url_id = ".$id;
  84. Database::query($sql);
  85. $sql = "DELETE FROM $tableUser WHERE access_url_id = ".$id;
  86. Database::query($sql);
  87. $sql = "DELETE FROM $table WHERE id = ".$id;
  88. Database::query($sql);
  89. return true;
  90. }
  91. /**
  92. * @param string $url
  93. *
  94. * @return int
  95. */
  96. public static function url_exist($url)
  97. {
  98. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  99. $sql = "SELECT id FROM $table
  100. WHERE url = '".Database::escape_string($url)."' ";
  101. $res = Database::query($sql);
  102. $num = Database::num_rows($res);
  103. return $num;
  104. }
  105. /**
  106. * @param string $url
  107. *
  108. * @return int
  109. */
  110. public static function url_id_exist($url)
  111. {
  112. if (empty($url)) {
  113. return false;
  114. }
  115. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  116. $sql = "SELECT id FROM $table WHERE id = ".intval($url)."";
  117. $res = Database::query($sql);
  118. $num = Database::num_rows($res);
  119. return $num;
  120. }
  121. /**
  122. * This function get the quantity of URLs
  123. * @author Julio Montoya
  124. * @return int count of urls
  125. * */
  126. public static function url_count()
  127. {
  128. $table_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  129. $sql = "SELECT count(id) as count_result FROM $table_access_url";
  130. $res = Database::query($sql);
  131. $url = Database::fetch_array($res, 'ASSOC');
  132. $result = $url['count_result'];
  133. return $result;
  134. }
  135. /**
  136. * Gets the id, url, description, and active status of ALL URLs
  137. * @author Julio Montoya
  138. * @return array
  139. * */
  140. public static function get_url_data()
  141. {
  142. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  143. $sql = "SELECT id, url, description, active
  144. FROM $table
  145. ORDER BY id";
  146. $res = Database::query($sql);
  147. $urls = array();
  148. while ($url = Database::fetch_array($res)) {
  149. $urls[] = $url;
  150. }
  151. return $urls;
  152. }
  153. /**
  154. * Gets the id, url, description, and active status of ALL URLs
  155. * @author Julio Montoya
  156. * @param int $url_id
  157. * @return array
  158. * */
  159. public static function get_url_data_from_id($url_id)
  160. {
  161. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  162. $sql = "SELECT id, url, description, active
  163. FROM $table
  164. WHERE id = ".intval($url_id);
  165. $res = Database::query($sql);
  166. $row = Database::fetch_array($res);
  167. return $row;
  168. }
  169. /**
  170. * Gets the inner join of users and urls table
  171. * @author Julio Montoya
  172. * @param int access url id
  173. * @param string $order_by
  174. * @return array Database::store_result of the result
  175. **/
  176. public static function get_url_rel_user_data($access_url_id = null, $order_by = null)
  177. {
  178. $where = '';
  179. $table_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  180. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  181. if (!empty($access_url_id)) {
  182. $where = "WHERE $table_url_rel_user.access_url_id = ".intval($access_url_id);
  183. }
  184. if (empty($order_by)) {
  185. $order_clause = api_sort_by_first_name(
  186. ) ? ' ORDER BY firstname, lastname, username' : ' ORDER BY lastname, firstname, username';
  187. } else {
  188. $order_clause = $order_by;
  189. }
  190. $sql = "SELECT u.user_id, lastname, firstname, username, official_code, access_url_id
  191. FROM $tbl_user u
  192. INNER JOIN $table_url_rel_user
  193. ON $table_url_rel_user.user_id = u.user_id
  194. $where $order_clause";
  195. $result = Database::query($sql);
  196. $users = Database::store_result($result);
  197. return $users;
  198. }
  199. /**
  200. * Gets the inner join of access_url and the course table
  201. *
  202. * @author Julio Montoya
  203. * @param int access url id
  204. * @return array Database::store_result of the result
  205. **/
  206. public static function get_url_rel_course_data($access_url_id = null)
  207. {
  208. $where = '';
  209. $table_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  210. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  211. if (!empty($access_url_id)) {
  212. $where = " WHERE uc.access_url_id = ".intval($access_url_id);
  213. }
  214. $sql = "SELECT u.id, c_id, title, uc.access_url_id
  215. FROM $tbl_course u
  216. INNER JOIN $table_url_rel_course uc
  217. ON uc.c_id = u.id
  218. $where
  219. ORDER BY title, code";
  220. $result = Database::query($sql);
  221. $courses = Database::store_result($result);
  222. return $courses;
  223. }
  224. /**
  225. * Gets the number of rows with a specific course_code in access_url_rel_course table
  226. * @author Yoselyn Castillo
  227. * @param int $courseId
  228. *
  229. * @return int Database::num_rows($res);
  230. **/
  231. public static function getCountUrlRelCourse($courseId)
  232. {
  233. $courseId = intval($courseId);
  234. $tableUrlRelCourse = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  235. $sql = "SELECT *
  236. FROM $tableUrlRelCourse
  237. WHERE c_id = '$courseId'";
  238. $res = Database::query($sql);
  239. return Database::num_rows($res);
  240. }
  241. /**
  242. * Gets the inner join of access_url and the session table
  243. * @author Julio Montoya
  244. * @param int $access_url_id access url id
  245. *
  246. * @return array Database::store_result of the result
  247. *
  248. **/
  249. public static function get_url_rel_session_data($access_url_id = null)
  250. {
  251. $where = '';
  252. $table_url_rel_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  253. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  254. if (!empty($access_url_id)) {
  255. $where = "WHERE $table_url_rel_session.access_url_id = ".intval($access_url_id);
  256. }
  257. $sql = "SELECT id, name, access_url_id
  258. FROM $tbl_session u
  259. INNER JOIN $table_url_rel_session
  260. ON $table_url_rel_session.session_id = id
  261. $where
  262. ORDER BY name, id";
  263. $result = Database::query($sql);
  264. $sessions = Database::store_result($result);
  265. return $sessions;
  266. }
  267. /**
  268. * Gets the inner join of access_url and the usergroup table
  269. *
  270. * @author Julio Montoya
  271. * @param int $access_url_id
  272. *
  273. * @return array Database::store_result of the result
  274. **/
  275. public static function get_url_rel_usergroup_data($access_url_id = null)
  276. {
  277. $where = '';
  278. $table_url_rel_usergroup = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  279. $table_user_group = Database::get_main_table(TABLE_USERGROUP);
  280. if (!empty($access_url_id)) {
  281. $where = " WHERE $table_url_rel_usergroup.access_url_id = ".intval($access_url_id);
  282. }
  283. $sql = "SELECT u.id, u.name, access_url_id
  284. FROM $table_user_group u
  285. INNER JOIN $table_url_rel_usergroup
  286. ON $table_url_rel_usergroup.usergroup_id = u.id
  287. $where
  288. ORDER BY name";
  289. $result = Database::query($sql);
  290. $courses = Database::store_result($result);
  291. return $courses;
  292. }
  293. /**
  294. * Gets the inner join of access_url and the usergroup table
  295. *
  296. * @author Julio Montoya
  297. * @param int $access_url_id
  298. * @return array Database::store_result of the result
  299. **/
  300. public static function getUrlRelCourseCategory($access_url_id = null)
  301. {
  302. $table_url_rel = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  303. $table = Database::get_main_table(TABLE_MAIN_CATEGORY);
  304. $where = " WHERE 1=1 ";
  305. if (!empty($access_url_id)) {
  306. $where .= " AND $table_url_rel.access_url_id = ".intval($access_url_id);
  307. }
  308. $where .= " AND (parent_id IS NULL) ";
  309. $sql = "SELECT u.id, name, access_url_id
  310. FROM $table u
  311. INNER JOIN $table_url_rel
  312. ON $table_url_rel.course_category_id = u.id
  313. $where
  314. ORDER BY name";
  315. $result = Database::query($sql);
  316. $courses = Database::store_result($result, 'ASSOC');
  317. return $courses;
  318. }
  319. /**
  320. * Sets the status of an URL 1 or 0
  321. * @author Julio Montoya
  322. * @param string $status lock || unlock
  323. * @param int url id
  324. * */
  325. public static function set_url_status($status, $url_id)
  326. {
  327. $url_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  328. if ($status == 'lock') {
  329. $status_db = '0';
  330. }
  331. if ($status == 'unlock') {
  332. $status_db = '1';
  333. }
  334. if (($status_db == '1' || $status_db == '0') && is_numeric($url_id)) {
  335. $sql = "UPDATE $url_table SET active='".intval($status_db)."'
  336. WHERE id='".intval($url_id)."'";
  337. Database::query($sql);
  338. }
  339. }
  340. /**
  341. * Checks the relationship between an URL and a User (return the num_rows)
  342. * @author Julio Montoya
  343. * @param int user id
  344. * @param int url id
  345. * @return boolean true if success
  346. * */
  347. public static function relation_url_user_exist($user_id, $url_id)
  348. {
  349. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  350. $sql = "SELECT user_id FROM $table
  351. WHERE access_url_id = ".intval($url_id)." AND user_id = ".intval($user_id)." ";
  352. $result = Database::query($sql);
  353. $num = Database::num_rows($result);
  354. return $num;
  355. }
  356. /**
  357. * Checks the relationship between an URL and a Course (return the num_rows)
  358. * @author Julio Montoya
  359. * @param int $courseId
  360. * @param int $urlId
  361. * @return boolean true if success
  362. * */
  363. public static function relation_url_course_exist($courseId, $urlId)
  364. {
  365. $table_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  366. $sql = "SELECT c_id FROM $table_url_rel_course
  367. WHERE
  368. access_url_id = ".intval($urlId)." AND
  369. c_id = '".intval($courseId)."'";
  370. $result = Database::query($sql);
  371. $num = Database::num_rows($result);
  372. return $num;
  373. }
  374. /**
  375. * Checks the relationship between an URL and a UserGr
  376. * oup (return the num_rows)
  377. * @author Julio Montoya
  378. * @param int $userGroupId
  379. * @param int $urlId
  380. * @return boolean true if success
  381. * */
  382. public static function relationUrlUsergroupExist($userGroupId, $urlId)
  383. {
  384. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  385. $sql = "SELECT usergroup_id FROM $table
  386. WHERE
  387. access_url_id = ".intval($urlId)." AND
  388. usergroup_id = ".intval($userGroupId);
  389. $result = Database::query($sql);
  390. $num = Database::num_rows($result);
  391. return $num;
  392. }
  393. /**
  394. * Checks the relationship between an URL and a Session (return the num_rows)
  395. * @author Julio Montoya
  396. * @param int user id
  397. * @param int url id
  398. * @return boolean true if success
  399. * */
  400. public static function relation_url_session_exist($session_id, $url_id)
  401. {
  402. $table_url_rel_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  403. $session_id = intval($session_id);
  404. $url_id = intval($url_id);
  405. $sql = "SELECT session_id FROM $table_url_rel_session
  406. WHERE
  407. access_url_id = ".intval($url_id)." AND
  408. session_id = ".Database::escape_string($session_id);
  409. $result = Database::query($sql);
  410. $num = Database::num_rows($result);
  411. return $num;
  412. }
  413. /**
  414. * Add a group of users into a group of URLs
  415. * @author Julio Montoya
  416. * @param array of user_ids
  417. * @param array of url_ids
  418. * @return array
  419. * */
  420. public static function add_users_to_urls($user_list, $url_list)
  421. {
  422. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  423. $result_array = array();
  424. if (is_array($user_list) && is_array($url_list)) {
  425. foreach ($url_list as $url_id) {
  426. foreach ($user_list as $user_id) {
  427. $count = self::relation_url_user_exist($user_id, $url_id);
  428. if ($count == 0) {
  429. $sql = "INSERT INTO $table
  430. SET
  431. user_id = ".intval($user_id).",
  432. access_url_id = ".intval($url_id);
  433. $result = Database::query($sql);
  434. if ($result) {
  435. $result_array[$url_id][$user_id] = 1;
  436. } else {
  437. $result_array[$url_id][$user_id] = 0;
  438. }
  439. }
  440. }
  441. }
  442. }
  443. return $result_array;
  444. }
  445. /**
  446. * Add a group of courses into a group of URLs
  447. * @author Julio Montoya
  448. * @param array $course_list of course ids
  449. * @param array $url_list of url_ids
  450. * @return array
  451. **/
  452. public static function add_courses_to_urls($course_list, $url_list)
  453. {
  454. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  455. $result_array = array();
  456. if (is_array($course_list) && is_array($url_list)) {
  457. foreach ($url_list as $url_id) {
  458. foreach ($course_list as $course_code) {
  459. $courseInfo = api_get_course_info($course_code);
  460. $courseId = $courseInfo['real_id'];
  461. $count = self::relation_url_course_exist($courseId, $url_id);
  462. if ($count == 0) {
  463. $sql = "INSERT INTO $table
  464. SET c_id = '".$courseId."', access_url_id = ".intval($url_id);
  465. $result = Database::query($sql);
  466. if ($result) {
  467. $result_array[$url_id][$course_code] = 1;
  468. } else {
  469. $result_array[$url_id][$course_code] = 0;
  470. }
  471. }
  472. }
  473. }
  474. }
  475. return $result_array;
  476. }
  477. /**
  478. * Add a group of user group into a group of URLs
  479. * @author Julio Montoya
  480. * @param array $userGroupList of course ids
  481. * @param array $urlList of url_ids
  482. * @return array
  483. **/
  484. public static function addUserGroupListToUrl($userGroupList, $urlList)
  485. {
  486. $resultArray = array();
  487. if (is_array($userGroupList) && is_array($urlList)) {
  488. foreach ($urlList as $urlId) {
  489. foreach ($userGroupList as $userGroupId) {
  490. $count = self::relationUrlUsergroupExist($userGroupId, $urlId);
  491. if ($count == 0) {
  492. $result = self::addUserGroupToUrl($userGroupId, $urlId);
  493. if ($result) {
  494. $resultArray[$urlId][$userGroupId] = 1;
  495. } else {
  496. $resultArray[$urlId][$userGroupId] = 0;
  497. }
  498. }
  499. }
  500. }
  501. }
  502. return $resultArray;
  503. }
  504. /**
  505. * Add a group of user group into a group of URLs
  506. * @author Julio Montoya
  507. * @param array of course ids
  508. * @param array of url_ids
  509. * @return array
  510. **/
  511. public static function addCourseCategoryListToUrl($courseCategoryList, $urlList)
  512. {
  513. $resultArray = array();
  514. if (is_array($courseCategoryList) && is_array($urlList)) {
  515. foreach ($urlList as $urlId) {
  516. foreach ($courseCategoryList as $categoryCourseId) {
  517. $count = self::relationUrlCourseCategoryExist($categoryCourseId, $urlId);
  518. if ($count == 0) {
  519. $result = self::addCourseCategoryToUrl($categoryCourseId, $urlId);
  520. if ($result) {
  521. $resultArray[$urlId][$categoryCourseId] = 1;
  522. } else {
  523. $resultArray[$urlId][$categoryCourseId] = 0;
  524. }
  525. }
  526. }
  527. }
  528. }
  529. return $resultArray;
  530. }
  531. /**
  532. * Checks the relationship between an URL and a UserGr
  533. * oup (return the num_rows)
  534. * @author Julio Montoya
  535. * @param int $categoryCourseId
  536. * @param int $urlId
  537. * @return boolean true if success
  538. * */
  539. public static function relationUrlCourseCategoryExist($categoryCourseId, $urlId)
  540. {
  541. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  542. $sql = "SELECT course_category_id FROM $table
  543. WHERE
  544. access_url_id = ".intval($urlId)." AND
  545. course_category_id = ".intval($categoryCourseId);
  546. $result = Database::query($sql);
  547. $num = Database::num_rows($result);
  548. return $num;
  549. }
  550. /**
  551. * @param int $userGroupId
  552. * @param int $urlId
  553. * @return int
  554. */
  555. public static function addUserGroupToUrl($userGroupId, $urlId)
  556. {
  557. $urlRelUserGroupTable = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  558. $sql = "INSERT INTO $urlRelUserGroupTable
  559. SET
  560. usergroup_id = '".intval($userGroupId)."',
  561. access_url_id = ".intval($urlId);
  562. Database::query($sql);
  563. return Database::insert_id();
  564. }
  565. /**
  566. * @param int $categoryId
  567. * @param int $urlId
  568. * @return int
  569. */
  570. public static function addCourseCategoryToUrl($categoryId, $urlId)
  571. {
  572. $exists = self::relationUrlCourseCategoryExist($categoryId, $urlId);
  573. if (empty($exists)) {
  574. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  575. $sql = "INSERT INTO $table
  576. SET
  577. course_category_id = '".intval($categoryId)."',
  578. access_url_id = ".intval($urlId);
  579. Database::query($sql);
  580. return Database::insert_id();
  581. }
  582. return 0;
  583. }
  584. /**
  585. * Add a group of sessions into a group of URLs
  586. * @author Julio Montoya
  587. * @param array $session_list of session ids
  588. * @param array $url_list of url_ids
  589. * @return array
  590. * */
  591. public static function add_sessions_to_urls($session_list, $url_list)
  592. {
  593. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  594. $result_array = array();
  595. if (is_array($session_list) && is_array($url_list)) {
  596. foreach ($url_list as $url_id) {
  597. foreach ($session_list as $session_id) {
  598. $count = self::relation_url_session_exist($session_id, $url_id);
  599. if ($count == 0) {
  600. $sql = "INSERT INTO $table
  601. SET
  602. session_id = ".intval($session_id).",
  603. access_url_id = ".intval($url_id);
  604. $result = Database::query($sql);
  605. if ($result) {
  606. $result_array[$url_id][$session_id] = 1;
  607. } else {
  608. $result_array[$url_id][$session_id] = 0;
  609. }
  610. }
  611. }
  612. }
  613. }
  614. return $result_array;
  615. }
  616. /**
  617. * Add a user into a url
  618. * @author Julio Montoya
  619. * @param int $user_id
  620. * @param int $url_id
  621. *
  622. * @return boolean true if success
  623. * */
  624. public static function add_user_to_url($user_id, $url_id = 1)
  625. {
  626. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  627. if (empty($url_id)) {
  628. $url_id = 1;
  629. }
  630. $count = self::relation_url_user_exist($user_id, $url_id);
  631. $result = true;
  632. if (empty($count)) {
  633. $sql = "INSERT INTO $table (user_id, access_url_id)
  634. VALUES ('".intval($user_id)."', '".intval($url_id)."') ";
  635. $result = Database::query($sql);
  636. }
  637. return $result;
  638. }
  639. /**
  640. * @param int $courseId
  641. * @param int $url_id
  642. *
  643. * @return resource
  644. */
  645. public static function add_course_to_url($courseId, $url_id = 1)
  646. {
  647. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  648. if (empty($url_id)) {
  649. $url_id = 1;
  650. }
  651. $count = self::relation_url_course_exist($courseId, $url_id);
  652. if (empty($count)) {
  653. $sql = "INSERT INTO $table
  654. SET c_id = '".intval($courseId)."', access_url_id = ".intval($url_id);
  655. Database::query($sql);
  656. }
  657. return true;
  658. }
  659. /**
  660. * Inserts a session to a URL (access_url_rel_session table)
  661. * @param int $session_id Session ID
  662. * @param int URL ID
  663. *
  664. * @return bool True on success, false session already exists or insert failed
  665. */
  666. public static function add_session_to_url($session_id, $url_id = 1)
  667. {
  668. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  669. if (empty($url_id)) {
  670. $url_id = 1;
  671. }
  672. $result = false;
  673. $count = self::relation_url_session_exist($session_id, $url_id);
  674. $session_id = intval($session_id);
  675. if (empty($count) && !empty($session_id)) {
  676. $url_id = intval($url_id);
  677. $sql = "INSERT INTO $table
  678. SET session_id = ".intval($session_id).", access_url_id = ".intval($url_id);
  679. $result = Database::query($sql);
  680. }
  681. return $result;
  682. }
  683. /**
  684. * Deletes an url and user relationship
  685. * @author Julio Montoya
  686. * @param int user id
  687. * @param int url id
  688. *
  689. * @return boolean true if success
  690. * */
  691. public static function delete_url_rel_user($user_id, $url_id)
  692. {
  693. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  694. $result = true;
  695. if (!empty($user_id) && !empty($url_id)) {
  696. $sql = "DELETE FROM $table
  697. WHERE user_id = ".intval($user_id)." AND access_url_id = ".intval($url_id);
  698. $result = Database::query($sql);
  699. }
  700. return $result;
  701. }
  702. /**
  703. * Deletes user from all portals
  704. * @author Julio Montoya
  705. * @param int user id
  706. *
  707. * @return boolean true if success
  708. * */
  709. public static function deleteUserFromAllUrls($userId)
  710. {
  711. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  712. $result = true;
  713. if (!empty($userId)) {
  714. $sql = "DELETE FROM $table
  715. WHERE user_id = ".intval($userId);
  716. Database::query($sql);
  717. }
  718. return $result;
  719. }
  720. /**
  721. * Deletes an url and course relationship
  722. * @author Julio Montoya
  723. * @param int $courseId
  724. * @param int $urlId
  725. *
  726. * @return boolean true if success
  727. * */
  728. public static function delete_url_rel_course($courseId, $urlId)
  729. {
  730. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  731. $sql = "DELETE FROM $table
  732. WHERE c_id = '".intval($courseId)."' AND access_url_id=".intval($urlId)." ";
  733. $result = Database::query($sql);
  734. return $result;
  735. }
  736. /**
  737. * Deletes an url and $userGroup relationship
  738. * @author Julio Montoya
  739. * @param int $userGroupId
  740. * @param int $urlId
  741. *
  742. * @return boolean true if success
  743. * */
  744. public static function delete_url_rel_usergroup($userGroupId, $urlId)
  745. {
  746. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  747. $sql = "DELETE FROM $table
  748. WHERE usergroup_id = '".intval($userGroupId)."' AND
  749. access_url_id = ".intval($urlId);
  750. $result = Database::query($sql);
  751. return $result;
  752. }
  753. /**
  754. * Deletes an url and $userGroup relationship
  755. * @author Julio Montoya
  756. * @param int $userGroupId
  757. * @param int $urlId
  758. *
  759. * @return boolean true if success
  760. * */
  761. public static function deleteUrlRelCourseCategory($userGroupId, $urlId)
  762. {
  763. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  764. $sql = "DELETE FROM $table
  765. WHERE
  766. course_category_id = '".intval($userGroupId)."' AND
  767. access_url_id=".intval($urlId)." ";
  768. $result = Database::query($sql);
  769. return $result;
  770. }
  771. /**
  772. * Deletes an url and session relationship
  773. * @author Julio Montoya
  774. * @param char course code
  775. * @param int url id
  776. *
  777. * @return boolean true if success
  778. * */
  779. public static function delete_url_rel_session($session_id, $url_id)
  780. {
  781. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  782. $sql = "DELETE FROM $table
  783. WHERE
  784. session_id = ".intval($session_id)." AND
  785. access_url_id=".intval($url_id)." ";
  786. $result = Database::query($sql, 'ASSOC');
  787. return $result;
  788. }
  789. /**
  790. * Updates the access_url_rel_user table with a given user list
  791. * @author Julio Montoya
  792. * @param array $user_list
  793. * @param int $access_url_id
  794. * @return bool|array
  795. **/
  796. public static function update_urls_rel_user($user_list, $access_url_id)
  797. {
  798. $table_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  799. $sql = "SELECT user_id
  800. FROM $table_url_rel_user
  801. WHERE access_url_id = ".intval($access_url_id);
  802. $result = Database::query($sql);
  803. $existing_users = array();
  804. //Getting all users
  805. while ($row = Database::fetch_array($result)) {
  806. $existing_users[] = $row['user_id'];
  807. }
  808. // Adding users
  809. $users_added = array();
  810. foreach ($user_list as $user_id_to_add) {
  811. if (!in_array($user_id_to_add, $existing_users)) {
  812. $result = self::add_user_to_url($user_id_to_add, $access_url_id);
  813. if ($result) {
  814. $users_added[] = $user_id_to_add;
  815. }
  816. }
  817. }
  818. $users_deleted = array();
  819. // Deleting old users
  820. foreach ($existing_users as $user_id_to_delete) {
  821. if (!in_array($user_id_to_delete, $user_list)) {
  822. $result = self::delete_url_rel_user($user_id_to_delete, $access_url_id);
  823. if ($result) {
  824. $users_deleted[] = $user_id_to_delete;
  825. }
  826. }
  827. }
  828. if (empty($users_added) && empty($users_deleted)) {
  829. return false;
  830. }
  831. return array(
  832. 'users_added' => $users_added,
  833. 'users_deleted' => $users_deleted
  834. );
  835. }
  836. /**
  837. * Updates the access_url_rel_course table with a given user list
  838. * @author Julio Montoya
  839. * @param array $course_list
  840. * @param int $access_url_id
  841. * */
  842. public static function update_urls_rel_course($course_list, $access_url_id)
  843. {
  844. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  845. $sql = "SELECT c_id FROM $table
  846. WHERE access_url_id = ".intval($access_url_id);
  847. $result = Database::query($sql);
  848. $existing_courses = array();
  849. while ($row = Database::fetch_array($result)) {
  850. $existing_courses[] = $row['c_id'];
  851. }
  852. // Adding courses
  853. foreach ($course_list as $courseId) {
  854. self::add_course_to_url($courseId, $access_url_id);
  855. CourseManager::update_course_ranking($courseId, 0, $access_url_id);
  856. }
  857. // Deleting old courses
  858. foreach ($existing_courses as $courseId) {
  859. if (!in_array($courseId, $course_list)) {
  860. self::delete_url_rel_course($courseId, $access_url_id);
  861. CourseManager::update_course_ranking($courseId, 0, $access_url_id);
  862. }
  863. }
  864. }
  865. /**
  866. * Updates the access_url_rel_course table with a given user list
  867. * @author Julio Montoya
  868. * @param array $userGroupList user list
  869. * @param int $urlId
  870. * */
  871. public static function update_urls_rel_usergroup($userGroupList, $urlId)
  872. {
  873. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  874. $sql = "SELECT usergroup_id FROM $table
  875. WHERE access_url_id = ".intval($urlId);
  876. $result = Database::query($sql);
  877. $existingItems = array();
  878. while ($row = Database::fetch_array($result)) {
  879. $existingItems[] = $row['usergroup_id'];
  880. }
  881. // Adding
  882. foreach ($userGroupList as $userGroupId) {
  883. if (!in_array($userGroupId, $existingItems)) {
  884. self::addUserGroupToUrl($userGroupId, $urlId);
  885. }
  886. }
  887. // Deleting old items
  888. foreach ($existingItems as $userGroupId) {
  889. if (!in_array($userGroupId, $userGroupList)) {
  890. self::delete_url_rel_usergroup($userGroupId, $urlId);
  891. }
  892. }
  893. }
  894. /**
  895. * Updates the access_url_rel_course_category table with a given list
  896. * @author Julio Montoya
  897. * @param array $list course category list
  898. * @param int $urlId access_url_id
  899. **/
  900. public static function updateUrlRelCourseCategory($list, $urlId)
  901. {
  902. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  903. $sql = "SELECT course_category_id
  904. FROM $table
  905. WHERE access_url_id = ".intval($urlId);
  906. $result = Database::query($sql);
  907. $existingItems = array();
  908. while ($row = Database::fetch_array($result)) {
  909. $existingItems[] = $row['course_category_id'];
  910. }
  911. // Adding
  912. foreach ($list as $id) {
  913. self::addCourseCategoryToUrl($id, $urlId);
  914. $categoryInfo = CourseCategory::getCategoryById($id);
  915. $children = CourseCategory::getChildren($categoryInfo['code']);
  916. if (!empty($children)) {
  917. foreach ($children as $category) {
  918. self::addCourseCategoryToUrl($category['id'], $urlId);
  919. }
  920. }
  921. }
  922. // Deleting old items
  923. foreach ($existingItems as $id) {
  924. if (!in_array($id, $list)) {
  925. self::deleteUrlRelCourseCategory($id, $urlId);
  926. $categoryInfo = CourseCategory::getCategoryById($id);
  927. $children = CourseCategory::getChildren($categoryInfo['code']);
  928. if (!empty($children)) {
  929. foreach ($children as $category) {
  930. self::deleteUrlRelCourseCategory($category['id'], $urlId);
  931. }
  932. }
  933. }
  934. }
  935. }
  936. /**
  937. * Updates the access_url_rel_session table with a given user list
  938. * @author Julio Montoya
  939. * @param array $session_list
  940. * @param int $access_url_id
  941. * */
  942. public static function update_urls_rel_session($session_list, $access_url_id)
  943. {
  944. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  945. $sql = "SELECT session_id FROM $table
  946. WHERE access_url_id=".intval($access_url_id);
  947. $result = Database::query($sql);
  948. $existing_sessions = array();
  949. while ($row = Database::fetch_array($result)) {
  950. $existing_sessions[] = $row['session_id'];
  951. }
  952. // Adding users
  953. foreach ($session_list as $session) {
  954. if (!in_array($session, $existing_sessions)) {
  955. if (!empty($session) && !empty($access_url_id)) {
  956. self::add_session_to_url($session, $access_url_id);
  957. }
  958. }
  959. }
  960. // Deleting old users
  961. foreach ($existing_sessions as $existing_session) {
  962. if (!in_array($existing_session, $session_list)) {
  963. if (!empty($existing_session) && !empty($access_url_id)) {
  964. self::delete_url_rel_session($existing_session, $access_url_id);
  965. }
  966. }
  967. }
  968. }
  969. /**
  970. * @param int $user_id
  971. *
  972. * @return array
  973. */
  974. public static function get_access_url_from_user($user_id)
  975. {
  976. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  977. $table_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  978. $sql = "SELECT url, access_url_id
  979. FROM $table url_rel_user
  980. INNER JOIN $table_url u
  981. ON (url_rel_user.access_url_id = u.id)
  982. WHERE user_id = ".intval($user_id);
  983. $result = Database::query($sql);
  984. $url_list = Database::store_result($result, 'ASSOC');
  985. return $url_list;
  986. }
  987. /**
  988. * @param int $courseId
  989. * @return array
  990. */
  991. public static function get_access_url_from_course($courseId)
  992. {
  993. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  994. $table_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  995. $sql = "SELECT url, access_url_id FROM $table c
  996. INNER JOIN $table_url u
  997. ON (c.access_url_id = u.id)
  998. WHERE c_id = ".intval($courseId);
  999. $result = Database::query($sql);
  1000. $url_list = Database::store_result($result, 'ASSOC');
  1001. return $url_list;
  1002. }
  1003. /**
  1004. * @param $session_id
  1005. * @return array
  1006. */
  1007. public static function get_access_url_from_session($session_id)
  1008. {
  1009. $table_url_rel_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  1010. $table_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  1011. $sql = "SELECT url, access_url_id
  1012. FROM $table_url_rel_session url_rel_session
  1013. INNER JOIN $table_url u
  1014. ON (url_rel_session.access_url_id = u.id)
  1015. WHERE session_id = ".intval($session_id);
  1016. $result = Database::query($sql);
  1017. $url_list = Database::store_result($result);
  1018. return $url_list;
  1019. }
  1020. /**
  1021. * @param string $url
  1022. * @return bool|mixed|null
  1023. */
  1024. public static function get_url_id($url)
  1025. {
  1026. $table_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  1027. $sql = "SELECT id FROM $table_access_url
  1028. WHERE url = '".Database::escape_string($url)."'";
  1029. $result = Database::query($sql);
  1030. $access_url_id = Database::result($result, 0, 0);
  1031. return $access_url_id;
  1032. }
  1033. /**
  1034. *
  1035. * @param string $needle
  1036. * @return XajaxResponse
  1037. */
  1038. public static function searchCourseCategoryAjax($needle)
  1039. {
  1040. $response = new xajaxResponse();
  1041. $return = '';
  1042. if (!empty($needle)) {
  1043. // xajax send utf8 datas... datas in db can be non-utf8 datas
  1044. $charset = api_get_system_encoding();
  1045. $needle = api_convert_encoding($needle, $charset, 'utf-8');
  1046. $needle = Database::escape_string($needle);
  1047. // search courses where username or firstname or lastname begins likes $needle
  1048. $sql = 'SELECT id, name
  1049. FROM '.Database::get_main_table(TABLE_MAIN_CATEGORY).' u
  1050. WHERE
  1051. name LIKE "'.$needle.'%" AND
  1052. (parent_id IS NULL or parent_id = 0)
  1053. ORDER BY name
  1054. LIMIT 11';
  1055. $result = Database::query($sql);
  1056. $i = 0;
  1057. while ($data = Database::fetch_array($result)) {
  1058. $i++;
  1059. if ($i <= 10) {
  1060. $return .= '<a
  1061. href="javascript: void(0);"
  1062. onclick="javascript: add_user_to_url(\''.addslashes($data['id']).'\',\''.addslashes($data['name']).' \')">'.$data['name'].' </a><br />';
  1063. } else {
  1064. $return .= '...<br />';
  1065. }
  1066. }
  1067. }
  1068. $response->addAssign(
  1069. 'ajax_list_courses',
  1070. 'innerHTML',
  1071. api_utf8_encode($return)
  1072. );
  1073. return $response;
  1074. }
  1075. }