urlmanager.lib.php 37 KB

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