urlmanager.lib.php 36 KB

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