urlmanager.lib.php 39 KB

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