urlmanager.lib.php 40 KB

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