urlmanager.lib.php 40 KB

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