urlmanager.lib.php 39 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181
  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 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_url_rel_user = 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 = UrlManager::relation_url_user_exist($user_id,$url_id);
  428. if ($count == 0) {
  429. $sql = "INSERT INTO $table_url_rel_user
  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 of course ids
  449. * @param array of url_ids
  450. * @return array
  451. **/
  452. public static function add_courses_to_urls($course_list,$url_list)
  453. {
  454. $table_url_rel_course = 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_url_rel_course
  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 access_url_id = ".intval($urlId)." AND
  544. course_category_id = ".intval($categoryCourseId);
  545. $result = Database::query($sql);
  546. $num = Database::num_rows($result);
  547. return $num;
  548. }
  549. /**
  550. * @param int $userGroupId
  551. * @param int $urlId
  552. * @return int
  553. */
  554. public static function addUserGroupToUrl($userGroupId, $urlId)
  555. {
  556. $urlRelUserGroupTable = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  557. $sql = "INSERT INTO $urlRelUserGroupTable
  558. SET
  559. usergroup_id = '".intval($userGroupId)."',
  560. access_url_id = ".intval($urlId);
  561. Database::query($sql);
  562. return Database::insert_id();
  563. }
  564. /**
  565. * @param int $categoryId
  566. * @param int $urlId
  567. * @return int
  568. */
  569. public static function addCourseCategoryToUrl($categoryId, $urlId)
  570. {
  571. $exists = self::relationUrlCourseCategoryExist($categoryId, $urlId);
  572. if (empty($exists)) {
  573. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  574. $sql = "INSERT INTO $table
  575. SET
  576. course_category_id = '".intval($categoryId)."',
  577. access_url_id = ".intval($urlId);
  578. Database::query($sql);
  579. return Database::insert_id();
  580. }
  581. return 0;
  582. }
  583. /**
  584. * Add a group of sessions into a group of URLs
  585. * @author Julio Montoya
  586. * @param array $session_list of session ids
  587. * @param array $url_list of url_ids
  588. * @return array
  589. * */
  590. public static function add_sessions_to_urls($session_list, $url_list)
  591. {
  592. $table_url_rel_session = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  593. $result_array = array();
  594. if (is_array($session_list) && is_array($url_list)) {
  595. foreach ($url_list as $url_id) {
  596. foreach ($session_list as $session_id) {
  597. $count = UrlManager::relation_url_session_exist($session_id, $url_id);
  598. if ($count == 0) {
  599. $sql = "INSERT INTO $table_url_rel_session
  600. SET
  601. session_id = ".intval($session_id).",
  602. access_url_id = ".intval($url_id);
  603. $result = Database::query($sql);
  604. if ($result) {
  605. $result_array[$url_id][$session_id] = 1;
  606. } else {
  607. $result_array[$url_id][$session_id] = 0;
  608. }
  609. }
  610. }
  611. }
  612. }
  613. return $result_array;
  614. }
  615. /**
  616. * Add a user into a url
  617. * @author Julio Montoya
  618. * @param int $user_id
  619. * @param int $url_id
  620. *
  621. * @return boolean true if success
  622. * */
  623. public static function add_user_to_url($user_id, $url_id = 1)
  624. {
  625. $table_url_rel_user = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  626. if (empty($url_id)) {
  627. $url_id = 1;
  628. }
  629. $count = UrlManager::relation_url_user_exist($user_id, $url_id);
  630. $result = true;
  631. if (empty($count)) {
  632. $sql = "INSERT INTO $table_url_rel_user (user_id, access_url_id)
  633. VALUES ('".intval($user_id)."', '".intval($url_id)."') ";
  634. $result = Database::query($sql);
  635. }
  636. return $result;
  637. }
  638. /**
  639. * @param int $courseId
  640. * @param int $url_id
  641. *
  642. * @return resource
  643. */
  644. public static function add_course_to_url($courseId, $url_id = 1)
  645. {
  646. $table_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  647. if (empty($url_id)) {
  648. $url_id = 1;
  649. }
  650. $count = UrlManager::relation_url_course_exist($courseId, $url_id);
  651. if (empty($count)) {
  652. $sql = "INSERT INTO $table_url_rel_course
  653. SET c_id = '".intval($courseId)."', access_url_id = ".intval($url_id);
  654. Database::query($sql);
  655. }
  656. return true;
  657. }
  658. /**
  659. * Inserts a session to a URL (access_url_rel_session table)
  660. * @param int Session ID
  661. * @param int URL ID
  662. *
  663. * @return bool True on success, false session already exists or insert failed
  664. */
  665. public static function add_session_to_url($session_id, $url_id = 1)
  666. {
  667. $table_url_rel_session = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  668. if (empty($url_id)) {
  669. $url_id = 1;
  670. }
  671. $result = false;
  672. $count = UrlManager::relation_url_session_exist($session_id, $url_id);
  673. $session_id = intval($session_id);
  674. if (empty($count) && !empty($session_id)) {
  675. $url_id = intval($url_id);
  676. $sql = "INSERT INTO $table_url_rel_session
  677. SET session_id = ".intval($session_id).", access_url_id = ".intval($url_id);
  678. $result = Database::query($sql);
  679. }
  680. return $result;
  681. }
  682. /**
  683. * Deletes an url and user relationship
  684. * @author Julio Montoya
  685. * @param int user id
  686. * @param int url id
  687. *
  688. * @return boolean true if success
  689. * */
  690. public static function delete_url_rel_user($user_id, $url_id)
  691. {
  692. $table_url_rel_user = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  693. $result = true;
  694. if (!empty($user_id) && !empty($url_id)) {
  695. $sql= "DELETE FROM $table_url_rel_user
  696. WHERE user_id = ".intval($user_id)." AND access_url_id = ".intval($url_id);
  697. $result = Database::query($sql);
  698. }
  699. return $result;
  700. }
  701. /**
  702. * Deletes user from all portals
  703. * @author Julio Montoya
  704. * @param int user id
  705. *
  706. * @return boolean true if success
  707. * */
  708. public static function deleteUserFromAllUrls($userId)
  709. {
  710. $table_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  711. $result = true;
  712. if (!empty($userId)) {
  713. $sql= "DELETE FROM $table_url_rel_user
  714. WHERE user_id = ".intval($userId);
  715. Database::query($sql);
  716. }
  717. return $result;
  718. }
  719. /**
  720. * Deletes an url and course relationship
  721. * @author Julio Montoya
  722. * @param int $courseId
  723. * @param int $urlId
  724. *
  725. * @return boolean true if success
  726. * */
  727. public static function delete_url_rel_course($courseId, $urlId)
  728. {
  729. $table_url_rel_course= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  730. $sql= "DELETE FROM $table_url_rel_course
  731. WHERE c_id = '".intval($courseId)."' AND access_url_id=".intval($urlId)." ";
  732. $result = Database::query($sql);
  733. return $result;
  734. }
  735. /**
  736. * Deletes an url and $userGroup relationship
  737. * @author Julio Montoya
  738. * @param int $userGroupId
  739. * @param int $urlId
  740. *
  741. * @return boolean true if success
  742. * */
  743. public static function delete_url_rel_usergroup($userGroupId, $urlId)
  744. {
  745. $table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  746. $sql= "DELETE FROM $table
  747. WHERE usergroup_id = '".intval($userGroupId)."' AND
  748. access_url_id = ".intval($urlId);
  749. $result = Database::query($sql);
  750. return $result;
  751. }
  752. /**
  753. * Deletes an url and $userGroup relationship
  754. * @author Julio Montoya
  755. * @param int $userGroupId
  756. * @param int $urlId
  757. *
  758. * @return boolean true if success
  759. * */
  760. public static function deleteUrlRelCourseCategory($userGroupId, $urlId)
  761. {
  762. $table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  763. $sql= "DELETE FROM $table
  764. WHERE course_category_id = '".intval($userGroupId)."' AND
  765. access_url_id=".intval($urlId)." ";
  766. $result = Database::query($sql);
  767. return $result;
  768. }
  769. /**
  770. * Deletes an url and session relationship
  771. * @author Julio Montoya
  772. * @param char course code
  773. * @param int url id
  774. *
  775. * @return boolean true if success
  776. * */
  777. public static function delete_url_rel_session($session_id, $url_id)
  778. {
  779. $table_url_rel_session = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  780. $sql= "DELETE FROM $table_url_rel_session
  781. WHERE session_id = ".intval($session_id)." AND access_url_id=".intval($url_id)." ";
  782. $result = Database::query($sql,'ASSOC');
  783. return $result;
  784. }
  785. /**
  786. * Updates the access_url_rel_user table with a given user list
  787. * @author Julio Montoya
  788. * @param array $user_list
  789. * @param int $access_url_id
  790. * */
  791. public static function update_urls_rel_user($user_list, $access_url_id)
  792. {
  793. $table_url_rel_user = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  794. $sql = "SELECT user_id
  795. FROM $table_url_rel_user
  796. WHERE access_url_id = ".intval($access_url_id);
  797. $result = Database::query($sql);
  798. $existing_users = array();
  799. //Getting all users
  800. while ($row = Database::fetch_array($result)) {
  801. $existing_users[] = $row['user_id'];
  802. }
  803. // Adding users
  804. $users_added = array();
  805. foreach ($user_list as $user_id_to_add) {
  806. if (!in_array($user_id_to_add, $existing_users)) {
  807. $result = UrlManager::add_user_to_url($user_id_to_add, $access_url_id);
  808. if ($result) {
  809. $users_added[] = $user_id_to_add;
  810. }
  811. }
  812. }
  813. $users_deleted = array();
  814. // Deleting old users
  815. foreach ($existing_users as $user_id_to_delete) {
  816. if (!in_array($user_id_to_delete, $user_list)) {
  817. $result = UrlManager::delete_url_rel_user($user_id_to_delete, $access_url_id);
  818. if ($result) {
  819. $users_deleted[] = $user_id_to_delete;
  820. }
  821. }
  822. }
  823. if (empty($users_added) && empty($users_deleted)) {
  824. return false;
  825. }
  826. return array('users_added' => $users_added, 'users_deleted' => $users_deleted);
  827. }
  828. /**
  829. * Updates the access_url_rel_course table with a given user list
  830. * @author Julio Montoya
  831. * @param array $course_list
  832. * @param int access_url_id
  833. * */
  834. public static function update_urls_rel_course($course_list, $access_url_id)
  835. {
  836. $table_url_rel_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  837. $sql = "SELECT c_id FROM $table_url_rel_course
  838. WHERE access_url_id = ".intval($access_url_id);
  839. $result = Database::query($sql);
  840. $existing_courses = array();
  841. while ($row = Database::fetch_array($result)){
  842. $existing_courses[] = $row['c_id'];
  843. }
  844. // Adding courses
  845. foreach ($course_list as $courseId) {
  846. UrlManager::add_course_to_url($courseId, $access_url_id);
  847. CourseManager::update_course_ranking($courseId, 0, $access_url_id);
  848. }
  849. // Deleting old courses
  850. foreach ($existing_courses as $courseId) {
  851. if (!in_array($courseId, $course_list)) {
  852. UrlManager::delete_url_rel_course($courseId, $access_url_id);
  853. CourseManager::update_course_ranking($courseId, 0, $access_url_id);
  854. }
  855. }
  856. }
  857. /**
  858. * Updates the access_url_rel_course table with a given user list
  859. * @author Julio Montoya
  860. * @param array $userGroupList user list
  861. * @param int $urlId
  862. * */
  863. public static function update_urls_rel_usergroup($userGroupList, $urlId)
  864. {
  865. $table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  866. $sql = "SELECT usergroup_id FROM $table WHERE access_url_id = ".intval($urlId);
  867. $result = Database::query($sql);
  868. $existingItems = array();
  869. while ($row = Database::fetch_array($result)){
  870. $existingItems[] = $row['usergroup_id'];
  871. }
  872. // Adding
  873. foreach ($userGroupList as $userGroupId) {
  874. if (!in_array($userGroupId, $existingItems)) {
  875. UrlManager::addUserGroupToUrl($userGroupId, $urlId);
  876. }
  877. }
  878. // Deleting old items
  879. foreach ($existingItems as $userGroupId) {
  880. if (!in_array($userGroupId, $userGroupList)) {
  881. UrlManager::delete_url_rel_usergroup($userGroupId, $urlId);
  882. }
  883. }
  884. }
  885. /**
  886. * Updates the access_url_rel_course_category table with a given list
  887. * @author Julio Montoya
  888. * @param array $list course category list
  889. * @param int $urlId access_url_id
  890. **/
  891. public static function updateUrlRelCourseCategory($list, $urlId)
  892. {
  893. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  894. $sql = "SELECT course_category_id FROM $table WHERE access_url_id = ".intval($urlId);
  895. $result = Database::query($sql);
  896. $existingItems = array();
  897. while ($row = Database::fetch_array($result)){
  898. $existingItems[] = $row['course_category_id'];
  899. }
  900. // Adding
  901. foreach ($list as $id) {
  902. UrlManager::addCourseCategoryToUrl($id, $urlId);
  903. $categoryInfo = CourseCategory::getCategoryById($id);
  904. $children = CourseCategory::getChildren($categoryInfo['code']);
  905. if (!empty($children)) {
  906. foreach ($children as $category) {
  907. UrlManager::addCourseCategoryToUrl($category['id'], $urlId);
  908. }
  909. }
  910. }
  911. // Deleting old items
  912. foreach ($existingItems as $id) {
  913. if (!in_array($id, $list)) {
  914. UrlManager::deleteUrlRelCourseCategory($id, $urlId);
  915. $categoryInfo = CourseCategory::getCategoryById($id);
  916. $children = CourseCategory::getChildren($categoryInfo['code']);
  917. if (!empty($children)) {
  918. foreach ($children as $category) {
  919. UrlManager::deleteUrlRelCourseCategory($category['id'], $urlId);
  920. }
  921. }
  922. }
  923. }
  924. }
  925. /**
  926. * Updates the access_url_rel_session table with a given user list
  927. * @author Julio Montoya
  928. * @param array $session_list
  929. * @param int $access_url_id
  930. * */
  931. public static function update_urls_rel_session($session_list, $access_url_id)
  932. {
  933. $table_url_rel_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  934. $sql = "SELECT session_id FROM $table_url_rel_session WHERE access_url_id=".intval($access_url_id);
  935. $result = Database::query($sql);
  936. $existing_sessions = array();
  937. while ($row = Database::fetch_array($result)){
  938. $existing_sessions[] = $row['session_id'];
  939. }
  940. // Adding users
  941. foreach ($session_list as $session) {
  942. if (!in_array($session, $existing_sessions)) {
  943. if (!empty($session) && !empty($access_url_id)) {
  944. UrlManager::add_session_to_url($session, $access_url_id);
  945. }
  946. }
  947. }
  948. // Deleting old users
  949. foreach ($existing_sessions as $existing_session) {
  950. if (!in_array($existing_session, $session_list)) {
  951. if (!empty($existing_session) && !empty($access_url_id)) {
  952. UrlManager::delete_url_rel_session($existing_session, $access_url_id);
  953. }
  954. }
  955. }
  956. }
  957. /**
  958. * @param int $user_id
  959. *
  960. * @return array
  961. */
  962. public static function get_access_url_from_user($user_id)
  963. {
  964. $table_url_rel_user = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  965. $table_url = Database :: get_main_table(TABLE_MAIN_ACCESS_URL);
  966. $sql = "SELECT url, access_url_id FROM $table_url_rel_user url_rel_user INNER JOIN $table_url u
  967. ON (url_rel_user.access_url_id = u.id)
  968. WHERE user_id = ".intval($user_id);
  969. $result = Database::query($sql);
  970. $url_list = Database::store_result($result,'ASSOC');
  971. return $url_list;
  972. }
  973. /**
  974. * @param int $courseId
  975. * @return array
  976. */
  977. public static function get_access_url_from_course($courseId)
  978. {
  979. $table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  980. $table_url = Database :: get_main_table(TABLE_MAIN_ACCESS_URL);
  981. $sql = "SELECT url, access_url_id FROM $table c INNER JOIN $table_url u
  982. ON (c.access_url_id = u.id)
  983. WHERE c_id = ".intval($courseId);
  984. $result = Database::query($sql);
  985. $url_list = Database::store_result($result,'ASSOC');
  986. return $url_list;
  987. }
  988. /**
  989. * @param $session_id
  990. * @return array
  991. */
  992. public static function get_access_url_from_session($session_id)
  993. {
  994. $table_url_rel_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  995. $table_url = Database :: get_main_table(TABLE_MAIN_ACCESS_URL);
  996. $sql = "SELECT url, access_url_id FROM $table_url_rel_session url_rel_session INNER JOIN $table_url u
  997. ON (url_rel_session.access_url_id = u.id)
  998. WHERE session_id = ".intval($session_id);
  999. $result = Database::query($sql);
  1000. $url_list = Database::store_result($result);
  1001. return $url_list;
  1002. }
  1003. /**
  1004. * @param string $url
  1005. * @return bool|mixed|null
  1006. */
  1007. public static function get_url_id($url)
  1008. {
  1009. $table_access_url= Database :: get_main_table(TABLE_MAIN_ACCESS_URL);
  1010. $sql = "SELECT id FROM $table_access_url WHERE url = '".Database::escape_string($url)."'";
  1011. $result = Database::query($sql);
  1012. $access_url_id = Database::result($result, 0, 0);
  1013. return $access_url_id;
  1014. }
  1015. /**
  1016. *
  1017. * @param string $needle
  1018. * @return XajaxResponse
  1019. */
  1020. public static function searchCourseCategoryAjax($needle)
  1021. {
  1022. $response = new xajaxResponse();
  1023. $return = '';
  1024. if (!empty($needle)) {
  1025. // xajax send utf8 datas... datas in db can be non-utf8 datas
  1026. $charset = api_get_system_encoding();
  1027. $needle = api_convert_encoding($needle, $charset, 'utf-8');
  1028. $needle = Database::escape_string($needle);
  1029. // search courses where username or firstname or lastname begins likes $needle
  1030. $sql = 'SELECT id, name FROM '.Database::get_main_table(TABLE_MAIN_CATEGORY).' u
  1031. WHERE name LIKE "'.$needle.'%" AND (parent_id IS NULL or parent_id = 0)
  1032. ORDER BY name
  1033. LIMIT 11';
  1034. $result = Database::query($sql);
  1035. $i = 0;
  1036. while ($data = Database::fetch_array($result)) {
  1037. $i++;
  1038. if ($i <= 10) {
  1039. $return .= '<a
  1040. href="javascript: void(0);"
  1041. onclick="javascript: add_user_to_url(\''.addslashes($data['id']).'\',\''.addslashes($data['name']).' \')">'.$data['name'].' </a><br />';
  1042. } else {
  1043. $return .= '...<br />';
  1044. }
  1045. }
  1046. }
  1047. $response->addAssign('ajax_list_courses', 'innerHTML', api_utf8_encode($return));
  1048. return $response;
  1049. }
  1050. }