urlmanager.lib.php 39 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172
  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. $result = Database::query($sql);
  77. /*
  78. $sql = "DELETE FROM $tableCourseCategory WHERE access_url_id = ".$id;
  79. $result = Database::query($sql);
  80. */
  81. $sql = "DELETE FROM $tableSession WHERE access_url_id = ".$id;
  82. $result = Database::query($sql);
  83. $sql = "DELETE FROM $tableGroup WHERE access_url_id = ".$id;
  84. $result = Database::query($sql);
  85. $sql = "DELETE FROM $tableUser WHERE access_url_id = ".$id;
  86. $result = Database::query($sql);
  87. $sql= "DELETE FROM $table WHERE id = ".$id;
  88. $result = Database::query($sql);
  89. return $result;
  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. $sql = "SELECT id, name, access_url_id
  257. FROM $tbl_session u
  258. INNER JOIN $table_url_rel_session
  259. ON $table_url_rel_session.session_id = id
  260. $where
  261. ORDER BY name, id";
  262. $result = Database::query($sql);
  263. $sessions = Database::store_result($result);
  264. return $sessions;
  265. }
  266. /**
  267. * Gets the inner join of access_url and the usergroup table
  268. *
  269. * @author Julio Montoya
  270. * @param int $access_url_id
  271. *
  272. * @return array Database::store_result of the result
  273. **/
  274. public static function get_url_rel_usergroup_data($access_url_id = null)
  275. {
  276. $where = '';
  277. $table_url_rel_usergroup = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  278. $table_user_group = Database::get_main_table(TABLE_USERGROUP);
  279. if (!empty($access_url_id)) {
  280. $where ="WHERE $table_url_rel_usergroup.access_url_id = ".intval($access_url_id);
  281. }
  282. $sql = "SELECT u.id, u.name, access_url_id
  283. FROM $table_user_group u
  284. INNER JOIN $table_url_rel_usergroup
  285. ON $table_url_rel_usergroup.usergroup_id = u.id
  286. $where
  287. ORDER BY name";
  288. $result = Database::query($sql);
  289. $courses = Database::store_result($result);
  290. return $courses;
  291. }
  292. /**
  293. * Gets the inner join of access_url and the usergroup table
  294. *
  295. * @author Julio Montoya
  296. * @param int $access_url_id
  297. * @return array Database::store_result of the result
  298. **/
  299. public static function getUrlRelCourseCategory($access_url_id = null)
  300. {
  301. $table_url_rel = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  302. $table = Database::get_main_table(TABLE_MAIN_CATEGORY);
  303. $where = " WHERE 1=1 ";
  304. if (!empty($access_url_id)) {
  305. $where .= " AND $table_url_rel.access_url_id = ".intval($access_url_id);
  306. }
  307. $where .= " AND (parent_id IS NULL) ";
  308. $sql = "SELECT id, name, access_url_id
  309. FROM $table u
  310. INNER JOIN $table_url_rel
  311. ON $table_url_rel.course_category_id = u.id
  312. $where
  313. ORDER BY name";
  314. $result = Database::query($sql);
  315. $courses = Database::store_result($result, 'ASSOC');
  316. return $courses;
  317. }
  318. /**
  319. * Sets the status of an URL 1 or 0
  320. * @author Julio Montoya
  321. * @param string lock || unlock
  322. * @param int url id
  323. * */
  324. public static function set_url_status($status, $url_id)
  325. {
  326. $url_table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL);
  327. if ($status == 'lock') {
  328. $status_db = '0';
  329. }
  330. if ($status == 'unlock') {
  331. $status_db = '1';
  332. }
  333. if (($status_db == '1' || $status_db == '0') && is_numeric($url_id)) {
  334. $sql = "UPDATE $url_table SET active='".intval($status_db)."'
  335. WHERE id='".intval($url_id)."'";
  336. Database::query($sql);
  337. }
  338. }
  339. /**
  340. * Checks the relationship between an URL and a User (return the num_rows)
  341. * @author Julio Montoya
  342. * @param int user id
  343. * @param int url id
  344. * @return boolean true if success
  345. * */
  346. public static function relation_url_user_exist($user_id, $url_id)
  347. {
  348. $table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  349. $sql= "SELECT user_id FROM $table
  350. WHERE access_url_id = ".intval($url_id)." AND user_id = ".intval($user_id)." ";
  351. $result = Database::query($sql);
  352. $num = Database::num_rows($result);
  353. return $num;
  354. }
  355. /**
  356. * Checks the relationship between an URL and a Course (return the num_rows)
  357. * @author Julio Montoya
  358. * @param int $courseId
  359. * @param int $urlId
  360. * @return boolean true if success
  361. * */
  362. public static function relation_url_course_exist($courseId, $urlId)
  363. {
  364. $table_url_rel_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  365. $sql= "SELECT c_id FROM $table_url_rel_course
  366. WHERE
  367. access_url_id = ".intval($urlId)." AND
  368. c_id = '".intval($courseId)."'";
  369. $result = Database::query($sql);
  370. $num = Database::num_rows($result);
  371. return $num;
  372. }
  373. /**
  374. * Checks the relationship between an URL and a UserGr
  375. * oup (return the num_rows)
  376. * @author Julio Montoya
  377. * @param int $userGroupId
  378. * @param int $urlId
  379. * @return boolean true if success
  380. * */
  381. public static function relationUrlUsergroupExist($userGroupId, $urlId)
  382. {
  383. $table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  384. $sql= "SELECT usergroup_id FROM $table
  385. WHERE access_url_id = ".intval($urlId)." AND
  386. usergroup_id = ".intval($userGroupId);
  387. $result = Database::query($sql);
  388. $num = Database::num_rows($result);
  389. return $num;
  390. }
  391. /**
  392. * Checks the relationship between an URL and a Session (return the num_rows)
  393. * @author Julio Montoya
  394. * @param int user id
  395. * @param int url id
  396. * @return boolean true if success
  397. * */
  398. public static function relation_url_session_exist($session_id, $url_id)
  399. {
  400. $table_url_rel_session= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  401. $session_id = intval($session_id);
  402. $url_id = intval($url_id);
  403. $sql = "SELECT session_id FROM $table_url_rel_session
  404. WHERE
  405. access_url_id = ".intval($url_id)." AND
  406. session_id = ".Database::escape_string($session_id);
  407. $result = Database::query($sql);
  408. $num = Database::num_rows($result);
  409. return $num;
  410. }
  411. /**
  412. * Add a group of users into a group of URLs
  413. * @author Julio Montoya
  414. * @param array of user_ids
  415. * @param array of url_ids
  416. * @return array
  417. * */
  418. public static function add_users_to_urls($user_list, $url_list)
  419. {
  420. $table_url_rel_user = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  421. $result_array = array();
  422. if (is_array($user_list) && is_array($url_list)){
  423. foreach ($url_list as $url_id) {
  424. foreach ($user_list as $user_id) {
  425. $count = UrlManager::relation_url_user_exist($user_id,$url_id);
  426. if ($count==0) {
  427. $sql = "INSERT INTO $table_url_rel_user
  428. SET user_id = ".intval($user_id).", access_url_id = ".intval($url_id);
  429. $result = Database::query($sql);
  430. if ($result) {
  431. $result_array[$url_id][$user_id] = 1;
  432. } else {
  433. $result_array[$url_id][$user_id] = 0;
  434. }
  435. }
  436. }
  437. }
  438. }
  439. return $result_array;
  440. }
  441. /**
  442. * Add a group of courses into a group of URLs
  443. * @author Julio Montoya
  444. * @param array of course ids
  445. * @param array of url_ids
  446. * @return array
  447. **/
  448. public static function add_courses_to_urls($course_list,$url_list)
  449. {
  450. $table_url_rel_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  451. $result_array = array();
  452. if (is_array($course_list) && is_array($url_list)){
  453. foreach ($url_list as $url_id) {
  454. foreach ($course_list as $course_code) {
  455. $courseInfo = api_get_course_info($course_code);
  456. $courseId = $courseInfo['real_id'];
  457. $count = self::relation_url_course_exist($courseId, $url_id);
  458. if ($count==0) {
  459. $sql = "INSERT INTO $table_url_rel_course
  460. SET c_id = '".$courseId."', access_url_id = ".intval($url_id);
  461. $result = Database::query($sql);
  462. if ($result) {
  463. $result_array[$url_id][$course_code] = 1;
  464. } else {
  465. $result_array[$url_id][$course_code] = 0;
  466. }
  467. }
  468. }
  469. }
  470. }
  471. return $result_array;
  472. }
  473. /**
  474. * Add a group of user group into a group of URLs
  475. * @author Julio Montoya
  476. * @param array $userGroupList of course ids
  477. * @param array $urlList of url_ids
  478. * @return array
  479. **/
  480. public static function addUserGroupListToUrl($userGroupList, $urlList)
  481. {
  482. $resultArray = array();
  483. if (is_array($userGroupList) && is_array($urlList)) {
  484. foreach ($urlList as $urlId) {
  485. foreach ($userGroupList as $userGroupId) {
  486. $count = self::relationUrlUsergroupExist($userGroupId, $urlId);
  487. if ($count == 0) {
  488. $result = self::addUserGroupToUrl($userGroupId, $urlId);
  489. if ($result) {
  490. $resultArray[$urlId][$userGroupId] = 1;
  491. } else {
  492. $resultArray[$urlId][$userGroupId] = 0;
  493. }
  494. }
  495. }
  496. }
  497. }
  498. return $resultArray;
  499. }
  500. /**
  501. * Add a group of user group into a group of URLs
  502. * @author Julio Montoya
  503. * @param array of course ids
  504. * @param array of url_ids
  505. * @return array
  506. **/
  507. public static function addCourseCategoryListToUrl($courseCategoryList, $urlList)
  508. {
  509. $resultArray = array();
  510. if (is_array($courseCategoryList) && is_array($urlList)) {
  511. foreach ($urlList as $urlId) {
  512. foreach ($courseCategoryList as $categoryCourseId) {
  513. $count = self::relationUrlCourseCategoryExist($categoryCourseId, $urlId);
  514. if ($count == 0) {
  515. $result = self::addCourseCategoryToUrl($categoryCourseId, $urlId);
  516. if ($result) {
  517. $resultArray[$urlId][$categoryCourseId] = 1;
  518. } else {
  519. $resultArray[$urlId][$categoryCourseId] = 0;
  520. }
  521. }
  522. }
  523. }
  524. }
  525. return $resultArray;
  526. }
  527. /**
  528. * Checks the relationship between an URL and a UserGr
  529. * oup (return the num_rows)
  530. * @author Julio Montoya
  531. * @param int $categoryCourseId
  532. * @param int $urlId
  533. * @return boolean true if success
  534. * */
  535. public static function relationUrlCourseCategoryExist($categoryCourseId, $urlId)
  536. {
  537. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  538. $sql= "SELECT course_category_id FROM $table
  539. WHERE access_url_id = ".intval($urlId)." AND
  540. course_category_id = ".intval($categoryCourseId);
  541. $result = Database::query($sql);
  542. $num = Database::num_rows($result);
  543. return $num;
  544. }
  545. /**
  546. * @param int $userGroupId
  547. * @param int $urlId
  548. * @return int
  549. */
  550. public static function addUserGroupToUrl($userGroupId, $urlId)
  551. {
  552. $urlRelUserGroupTable = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  553. $sql = "INSERT INTO $urlRelUserGroupTable
  554. SET
  555. usergroup_id = '".intval($userGroupId)."',
  556. access_url_id = ".intval($urlId);
  557. Database::query($sql);
  558. return Database::insert_id();
  559. }
  560. /**
  561. * @param int $categoryId
  562. * @param int $urlId
  563. * @return int
  564. */
  565. public static function addCourseCategoryToUrl($categoryId, $urlId)
  566. {
  567. $exists = self::relationUrlCourseCategoryExist($categoryId, $urlId);
  568. if (empty($exists)) {
  569. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  570. $sql = "INSERT INTO $table
  571. SET
  572. course_category_id = '".intval($categoryId)."',
  573. access_url_id = ".intval($urlId);
  574. Database::query($sql);
  575. return Database::insert_id();
  576. }
  577. return 0;
  578. }
  579. /**
  580. * Add a group of sessions into a group of URLs
  581. * @author Julio Montoya
  582. * @param array of session ids
  583. * @param array of url_ids
  584. * @return array
  585. * */
  586. public static function add_sessions_to_urls($session_list, $url_list)
  587. {
  588. $table_url_rel_session = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  589. $result_array = array();
  590. if (is_array($session_list) && is_array($url_list)) {
  591. foreach ($url_list as $url_id) {
  592. foreach ($session_list as $session_id) {
  593. $count = UrlManager::relation_url_session_exist($session_id, $url_id);
  594. if ($count == 0) {
  595. $sql = "INSERT INTO $table_url_rel_session
  596. SET
  597. session_id = ".intval($session_id).",
  598. access_url_id = ".intval($url_id);
  599. $result = Database::query($sql);
  600. if ($result) {
  601. $result_array[$url_id][$session_id] = 1;
  602. } else {
  603. $result_array[$url_id][$session_id] = 0;
  604. }
  605. }
  606. }
  607. }
  608. }
  609. return $result_array;
  610. }
  611. /**
  612. * Add a user into a url
  613. * @author Julio Montoya
  614. * @param $user_id
  615. * @param $url_id
  616. *
  617. * @return boolean true if success
  618. * */
  619. public static function add_user_to_url($user_id, $url_id = 1)
  620. {
  621. $table_url_rel_user = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  622. if (empty($url_id)) {
  623. $url_id = 1;
  624. }
  625. $count = UrlManager::relation_url_user_exist($user_id, $url_id);
  626. $result = true;
  627. if (empty($count)) {
  628. $sql = "INSERT INTO $table_url_rel_user (user_id, access_url_id)
  629. VALUES ('".intval($user_id)."', '".intval($url_id)."') ";
  630. $result = Database::query($sql);
  631. }
  632. return $result;
  633. }
  634. /**
  635. * @param int $courseId
  636. * @param int $url_id
  637. *
  638. * @return resource
  639. */
  640. public static function add_course_to_url($courseId, $url_id = 1)
  641. {
  642. $table_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  643. if (empty($url_id)) {
  644. $url_id = 1;
  645. }
  646. $count = UrlManager::relation_url_course_exist($courseId, $url_id);
  647. if (empty($count)) {
  648. $sql = "INSERT INTO $table_url_rel_course
  649. SET c_id = '".intval($courseId)."', access_url_id = ".intval($url_id);
  650. Database::query($sql);
  651. }
  652. return true;
  653. }
  654. /**
  655. * Inserts a session to a URL (access_url_rel_session table)
  656. * @param int Session ID
  657. * @param int URL ID
  658. *
  659. * @return bool True on success, false session already exists or insert failed
  660. */
  661. public static function add_session_to_url($session_id, $url_id = 1)
  662. {
  663. $table_url_rel_session = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  664. if (empty($url_id)) {
  665. $url_id = 1;
  666. }
  667. $result = false;
  668. $count = UrlManager::relation_url_session_exist($session_id, $url_id);
  669. $session_id = intval($session_id);
  670. if (empty($count) && !empty($session_id)) {
  671. $url_id = intval($url_id);
  672. $sql = "INSERT INTO $table_url_rel_session
  673. SET session_id = ".intval($session_id).", access_url_id = ".intval($url_id);
  674. $result = Database::query($sql);
  675. }
  676. return $result;
  677. }
  678. /**
  679. * Deletes an url and user relationship
  680. * @author Julio Montoya
  681. * @param int user id
  682. * @param int url id
  683. *
  684. * @return boolean true if success
  685. * */
  686. public static function delete_url_rel_user($user_id, $url_id)
  687. {
  688. $table_url_rel_user = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  689. $result = true;
  690. if (!empty($user_id) && !empty($url_id)) {
  691. $sql= "DELETE FROM $table_url_rel_user
  692. WHERE user_id = ".intval($user_id)." AND access_url_id = ".intval($url_id);
  693. $result = Database::query($sql);
  694. }
  695. return $result;
  696. }
  697. /**
  698. * Deletes user from all portals
  699. * @author Julio Montoya
  700. * @param int user id
  701. *
  702. * @return boolean true if success
  703. * */
  704. public static function deleteUserFromAllUrls($userId)
  705. {
  706. $table_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  707. $result = true;
  708. if (!empty($userId)) {
  709. $sql= "DELETE FROM $table_url_rel_user
  710. WHERE user_id = ".intval($userId);
  711. Database::query($sql);
  712. }
  713. return $result;
  714. }
  715. /**
  716. * Deletes an url and course relationship
  717. * @author Julio Montoya
  718. * @param int $courseId
  719. * @param int $urlId
  720. *
  721. * @return boolean true if success
  722. * */
  723. public static function delete_url_rel_course($courseId, $urlId)
  724. {
  725. $table_url_rel_course= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  726. $sql= "DELETE FROM $table_url_rel_course
  727. WHERE c_id = '".intval($courseId)."' AND access_url_id=".intval($urlId)." ";
  728. $result = Database::query($sql);
  729. return $result;
  730. }
  731. /**
  732. * Deletes an url and $userGroup relationship
  733. * @author Julio Montoya
  734. * @param int $userGroupId
  735. * @param int $urlId
  736. *
  737. * @return boolean true if success
  738. * */
  739. public static function delete_url_rel_usergroup($userGroupId, $urlId)
  740. {
  741. $table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  742. $sql= "DELETE FROM $table
  743. WHERE usergroup_id = '".intval($userGroupId)."' AND
  744. access_url_id=".intval($urlId)." ";
  745. $result = Database::query($sql);
  746. return $result;
  747. }
  748. /**
  749. * Deletes an url and $userGroup relationship
  750. * @author Julio Montoya
  751. * @param int $userGroupId
  752. * @param int $urlId
  753. *
  754. * @return boolean true if success
  755. * */
  756. public static function deleteUrlRelCourseCategory($userGroupId, $urlId)
  757. {
  758. $table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  759. $sql= "DELETE FROM $table
  760. WHERE course_category_id = '".intval($userGroupId)."' AND
  761. access_url_id=".intval($urlId)." ";
  762. $result = Database::query($sql);
  763. return $result;
  764. }
  765. /**
  766. * Deletes an url and session relationship
  767. * @author Julio Montoya
  768. * @param char course code
  769. * @param int url id
  770. *
  771. * @return boolean true if success
  772. * */
  773. public static function delete_url_rel_session($session_id, $url_id)
  774. {
  775. $table_url_rel_session = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  776. $sql= "DELETE FROM $table_url_rel_session
  777. WHERE session_id = ".intval($session_id)." AND access_url_id=".intval($url_id)." ";
  778. $result = Database::query($sql,'ASSOC');
  779. return $result;
  780. }
  781. /**
  782. * Updates the access_url_rel_user table with a given user list
  783. * @author Julio Montoya
  784. * @param array user list
  785. * @param int access_url_id
  786. * */
  787. public static function update_urls_rel_user($user_list, $access_url_id)
  788. {
  789. $table_url_rel_user = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  790. $sql = "SELECT user_id FROM $table_url_rel_user WHERE access_url_id = ".intval($access_url_id);
  791. $result = Database::query($sql);
  792. $existing_users = array();
  793. //Getting all users
  794. while ($row = Database::fetch_array($result)) {
  795. $existing_users[] = $row['user_id'];
  796. }
  797. // Adding users
  798. $users_added = array();
  799. foreach ($user_list as $user_id_to_add) {
  800. if (!in_array($user_id_to_add, $existing_users)) {
  801. $result = UrlManager::add_user_to_url($user_id_to_add, $access_url_id);
  802. if ($result) {
  803. $users_added[] = $user_id_to_add;
  804. }
  805. }
  806. }
  807. $users_deleted = array();
  808. // Deleting old users
  809. foreach ($existing_users as $user_id_to_delete) {
  810. if (!in_array($user_id_to_delete, $user_list)) {
  811. $result = UrlManager::delete_url_rel_user($user_id_to_delete, $access_url_id);
  812. if ($result) {
  813. $users_deleted[] = $user_id_to_delete;
  814. }
  815. }
  816. }
  817. if (empty($users_added) && empty($users_deleted)) {
  818. return false;
  819. }
  820. return array('users_added' => $users_added, 'users_deleted' => $users_deleted);
  821. }
  822. /**
  823. * Updates the access_url_rel_course table with a given user list
  824. * @author Julio Montoya
  825. * @param array $course_list
  826. * @param int access_url_id
  827. * */
  828. public static function update_urls_rel_course($course_list, $access_url_id)
  829. {
  830. $table_url_rel_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  831. $sql = "SELECT c_id FROM $table_url_rel_course
  832. WHERE access_url_id = ".intval($access_url_id);
  833. $result = Database::query($sql);
  834. $existing_courses = array();
  835. while ($row = Database::fetch_array($result)){
  836. $existing_courses[] = $row['c_id'];
  837. }
  838. // Adding courses
  839. foreach ($course_list as $courseId) {
  840. UrlManager::add_course_to_url($courseId, $access_url_id);
  841. CourseManager::update_course_ranking($courseId, 0, $access_url_id);
  842. }
  843. // Deleting old courses
  844. foreach ($existing_courses as $courseId) {
  845. if (!in_array($courseId, $course_list)) {
  846. UrlManager::delete_url_rel_course($courseId, $access_url_id);
  847. CourseManager::update_course_ranking($courseId, 0, $access_url_id);
  848. }
  849. }
  850. }
  851. /**
  852. * Updates the access_url_rel_course table with a given user list
  853. * @author Julio Montoya
  854. * @param array user list
  855. * @param int access_url_id
  856. * */
  857. public static function update_urls_rel_usergroup($userGroupList, $urlId)
  858. {
  859. $table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);
  860. $sql = "SELECT usergroup_id FROM $table WHERE access_url_id = ".intval($urlId);
  861. $result = Database::query($sql);
  862. $existingItems = array();
  863. while ($row = Database::fetch_array($result)){
  864. $existingItems[] = $row['usergroup_id'];
  865. }
  866. // Adding
  867. foreach ($userGroupList as $userGroupId) {
  868. if (!in_array($userGroupId, $existingItems)) {
  869. UrlManager::addUserGroupToUrl($userGroupId, $urlId);
  870. }
  871. }
  872. // Deleting old items
  873. foreach ($existingItems as $userGroupId) {
  874. if (!in_array($userGroupId, $userGroupList)) {
  875. UrlManager::delete_url_rel_usergroup($userGroupId, $urlId);
  876. }
  877. }
  878. }
  879. /**
  880. * Updates the access_url_rel_course_category table with a given list
  881. * @author Julio Montoya
  882. * @param array course category list
  883. * @param int access_url_id
  884. **/
  885. public static function updateUrlRelCourseCategory($list, $urlId)
  886. {
  887. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);
  888. $sql = "SELECT course_category_id FROM $table WHERE access_url_id = ".intval($urlId);
  889. $result = Database::query($sql);
  890. $existingItems = array();
  891. while ($row = Database::fetch_array($result)){
  892. $existingItems[] = $row['course_category_id'];
  893. }
  894. // Adding
  895. foreach ($list as $id) {
  896. UrlManager::addCourseCategoryToUrl($id, $urlId);
  897. $categoryInfo = getCategoryById($id);
  898. $children = getChildren($categoryInfo['code']);
  899. if (!empty($children)) {
  900. foreach ($children as $category) {
  901. UrlManager::addCourseCategoryToUrl($category['id'], $urlId);
  902. }
  903. }
  904. }
  905. // Deleting old items
  906. foreach ($existingItems as $id) {
  907. if (!in_array($id, $list)) {
  908. UrlManager::deleteUrlRelCourseCategory($id, $urlId);
  909. $categoryInfo = getCategoryById($id);
  910. $children = getChildren($categoryInfo['code']);
  911. if (!empty($children)) {
  912. foreach ($children as $category) {
  913. UrlManager::deleteUrlRelCourseCategory($category['id'], $urlId);
  914. }
  915. }
  916. }
  917. }
  918. }
  919. /**
  920. * Updates the access_url_rel_session table with a given user list
  921. * @author Julio Montoya
  922. * @param array user list
  923. * @param int access_url_id
  924. * */
  925. public static function update_urls_rel_session($session_list,$access_url_id)
  926. {
  927. $table_url_rel_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  928. $sql = "SELECT session_id FROM $table_url_rel_session WHERE access_url_id=".intval($access_url_id);
  929. $result = Database::query($sql);
  930. $existing_sessions = array();
  931. while ($row = Database::fetch_array($result)){
  932. $existing_sessions[] = $row['session_id'];
  933. }
  934. // Adding users
  935. foreach ($session_list as $session) {
  936. if (!in_array($session, $existing_sessions)) {
  937. if (!empty($session) && !empty($access_url_id)) {
  938. UrlManager::add_session_to_url($session, $access_url_id);
  939. }
  940. }
  941. }
  942. // Deleting old users
  943. foreach ($existing_sessions as $existing_session) {
  944. if (!in_array($existing_session, $session_list)) {
  945. if (!empty($existing_session) && !empty($access_url_id)) {
  946. UrlManager::delete_url_rel_session($existing_session, $access_url_id);
  947. }
  948. }
  949. }
  950. }
  951. /**
  952. * @param int $user_id
  953. * @return array
  954. */
  955. public static function get_access_url_from_user($user_id)
  956. {
  957. $table_url_rel_user = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  958. $table_url = Database :: get_main_table(TABLE_MAIN_ACCESS_URL);
  959. $sql = "SELECT url, access_url_id FROM $table_url_rel_user url_rel_user INNER JOIN $table_url u
  960. ON (url_rel_user.access_url_id = u.id)
  961. WHERE user_id = ".intval($user_id);
  962. $result = Database::query($sql);
  963. $url_list = Database::store_result($result,'ASSOC');
  964. return $url_list;
  965. }
  966. /**
  967. * @param int $courseId
  968. * @return array
  969. */
  970. public static function get_access_url_from_course($courseId)
  971. {
  972. $table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  973. $table_url = Database :: get_main_table(TABLE_MAIN_ACCESS_URL);
  974. $sql = "SELECT url, access_url_id FROM $table c INNER JOIN $table_url u
  975. ON (c.access_url_id = u.id)
  976. WHERE c_id = ".intval($courseId);
  977. $result = Database::query($sql);
  978. $url_list = Database::store_result($result,'ASSOC');
  979. return $url_list;
  980. }
  981. /**
  982. * @param $session_id
  983. * @return array
  984. */
  985. public static function get_access_url_from_session($session_id)
  986. {
  987. $table_url_rel_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  988. $table_url = Database :: get_main_table(TABLE_MAIN_ACCESS_URL);
  989. $sql = "SELECT url, access_url_id FROM $table_url_rel_session url_rel_session INNER JOIN $table_url u
  990. ON (url_rel_session.access_url_id = u.id)
  991. WHERE session_id = ".intval($session_id);
  992. $result = Database::query($sql);
  993. $url_list = Database::store_result($result);
  994. return $url_list;
  995. }
  996. /**
  997. * @param string $url
  998. * @return bool|mixed|null
  999. */
  1000. public static function get_url_id($url)
  1001. {
  1002. $table_access_url= Database :: get_main_table(TABLE_MAIN_ACCESS_URL);
  1003. $sql = "SELECT id FROM $table_access_url WHERE url = '".Database::escape_string($url)."'";
  1004. $result = Database::query($sql);
  1005. $access_url_id = Database::result($result, 0, 0);
  1006. return $access_url_id;
  1007. }
  1008. /**
  1009. *
  1010. * @param string $needle
  1011. * @return XajaxResponse
  1012. */
  1013. public static function searchCourseCategoryAjax($needle)
  1014. {
  1015. $response = new xajaxResponse();
  1016. $return = '';
  1017. if (!empty($needle)) {
  1018. // xajax send utf8 datas... datas in db can be non-utf8 datas
  1019. $charset = api_get_system_encoding();
  1020. $needle = api_convert_encoding($needle, $charset, 'utf-8');
  1021. $needle = Database::escape_string($needle);
  1022. // search courses where username or firstname or lastname begins likes $needle
  1023. $sql = 'SELECT id, name FROM '.Database::get_main_table(TABLE_MAIN_CATEGORY).' u
  1024. WHERE name LIKE "'.$needle.'%" AND (parent_id IS NULL or parent_id = 0)
  1025. ORDER BY name
  1026. LIMIT 11';
  1027. $result = Database::query($sql);
  1028. $i = 0;
  1029. while ($data = Database::fetch_array($result)) {
  1030. $i++;
  1031. if ($i <= 10) {
  1032. $return .= '<a
  1033. href="javascript: void(0);"
  1034. onclick="javascript: add_user_to_url(\''.addslashes($data['id']).'\',\''.addslashes($data['name']).' \')">'.$data['name'].' </a><br />';
  1035. } else {
  1036. $return .= '...<br />';
  1037. }
  1038. }
  1039. }
  1040. $response->addAssign('ajax_list_courses', 'innerHTML', api_utf8_encode($return));
  1041. return $response;
  1042. }
  1043. }