urlmanager.lib.php 37 KB

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