buy_course.lib.php 25 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670
  1. <?php
  2. /* For license terms, see /license.txt */
  3. /**
  4. * Functions
  5. * @package chamilo.plugin.buycourses
  6. */
  7. /**
  8. * Init
  9. */
  10. require_once '../../../main/inc/global.inc.php';
  11. require_once '../config.php';
  12. require_once api_get_path(LIBRARY_PATH) . 'plugin.class.php';
  13. /**
  14. *
  15. */
  16. function sync()
  17. {
  18. $tableBuySessionRelCourse = Database::get_main_table(TABLE_BUY_SESSION_COURSE);
  19. $tableSessionRelCourse = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  20. $sql = "UPDATE $tableBuySessionRelCourse SET sync = 0";
  21. Database::query($sql);
  22. $sql = "SELECT id_session, course_code, nbr_users FROM $tableSessionRelCourse";
  23. $res = Database::query($sql);
  24. while ($row = Database::fetch_assoc($res)) {
  25. $sql = "SELECT 1 FROM $tableBuySessionRelCourse WHERE id_session='" . $row['id_session'] . "';";
  26. Database::query($sql);
  27. if (Database::affected_rows() > 0) {
  28. $sql = "UPDATE $tableBuySessionRelCourse SET sync = 1 WHERE id_session='" . $row['id_session'] . "';";
  29. Database::query($sql);
  30. } else {
  31. $sql = "INSERT INTO $tableBuySessionRelCourse (id_session, course_code, nbr_users, sync)
  32. VALUES ('" . $row['id_session'] . "', '" . $row['course_code'] . "', '" . $row['nbr_users'] . "', 1);";
  33. Database::query($sql);
  34. }
  35. }
  36. $sql = "DELETE FROM $tableBuySessionRelCourse WHERE sync = 0;";
  37. Database::query($sql);
  38. $tableBuyCourse = Database::get_main_table(TABLE_BUY_COURSE);
  39. $tableCourse = Database::get_main_table(TABLE_MAIN_COURSE);
  40. $sql = "UPDATE $tableBuyCourse SET sync = 0";
  41. Database::query($sql);
  42. $sql = "SELECT id, code, title FROM $tableCourse";
  43. $res = Database::query($sql);
  44. while ($row = Database::fetch_assoc($res)) {
  45. $sql = "SELECT id_session FROM $tableBuySessionRelCourse
  46. WHERE course_code = '" . $row['code'] . "' LIMIT 1";
  47. $courseIdSession = Database::fetch_assoc(Database::query($sql))['id_session'];
  48. if (!is_numeric($courseIdSession)) {
  49. $courseIdSession = 0;
  50. }
  51. $sql = "SELECT 1 FROM $tableBuyCourse WHERE course_id='" . $row['id'] . "';";
  52. Database::query($sql);
  53. if (Database::affected_rows() > 0) {
  54. $sql = "UPDATE $tableBuyCourse SET sync = 1, session_id = $courseIdSession WHERE course_id='" . $row['id'] . "';";
  55. Database::query($sql);
  56. } else {
  57. $sql = "INSERT INTO $tableBuyCourse (session_id, course_id, code, title, visible, sync)
  58. VALUES ('" . $courseIdSession . "', '" . $row['id'] . "', '" .
  59. $row['code'] . "', '" . $row['title'] . "', 0, 1);";
  60. Database::query($sql);
  61. }
  62. }
  63. $sql = "DELETE FROM $tableBuyCourse WHERE sync = 0;";
  64. Database::query($sql);
  65. $tableBuySession = Database::get_main_table(TABLE_BUY_SESSION);
  66. $tableSession = Database::get_main_table(TABLE_MAIN_SESSION);
  67. $sql = "UPDATE $tableBuySession SET sync = 0";
  68. Database::query($sql);
  69. $sql = "SELECT id, name, date_start, date_end FROM $tableSession";
  70. $res = Database::query($sql);
  71. while ($row = Database::fetch_assoc($res)) {
  72. $sql = "SELECT 1 FROM $tableBuySession WHERE session_id='" . $row['id'] . "';";
  73. Database::query($sql);
  74. if (Database::affected_rows() > 0) {
  75. $sql = "UPDATE $tableBuySession SET sync = 1 WHERE session_id='" . $row['id'] . "';";
  76. Database::query($sql);
  77. } else {
  78. $sql = "INSERT INTO $tableBuySession (session_id, name, date_start, date_end, visible, sync)
  79. VALUES ('" . $row['id'] . "', '" . $row['name'] . "', '" .
  80. $row['date_start'] . "', '" . $row['date_end'] . "', 0, 1);";
  81. Database::query($sql);
  82. }
  83. }
  84. $sql = "DELETE FROM $tableBuySession WHERE sync = 0;";
  85. Database::query($sql);
  86. }
  87. /**
  88. * List sessions details from the buy-session table and the session table
  89. * @return array Results (list of session details)
  90. */
  91. function listSessions()
  92. {
  93. $tableBuySession = Database::get_main_table(TABLE_BUY_SESSION);
  94. $tableSession = Database::get_main_table(TABLE_MAIN_SESSION);
  95. $sql = "SELECT a.session_id, a.visible, a.price, b.*
  96. FROM $tableBuySession a, $tableSession b
  97. WHERE a.session_id = b.id;";
  98. $res = Database::query($sql);
  99. $aux = array();
  100. while ($row = Database::fetch_assoc($res)) {
  101. $aux[] = $row;
  102. }
  103. return $aux;
  104. }
  105. /**
  106. * List courses details from the buy-course table and the course table
  107. * @return array Results (list of courses details)
  108. */
  109. function listCourses()
  110. {
  111. $tableBuyCourse = Database::get_main_table(TABLE_BUY_COURSE);
  112. $tableCourse = Database::get_main_table(TABLE_MAIN_COURSE);
  113. $sql = "SELECT a.course_id, a.visible, a.price, b.*
  114. FROM $tableBuyCourse a, $tableCourse b
  115. WHERE a.course_id = b.id AND a.session_id = 0;";
  116. $res = Database::query($sql);
  117. $aux = array();
  118. while ($row = Database::fetch_assoc($res)) {
  119. $aux[] = $row;
  120. }
  121. return $aux;
  122. }
  123. /**
  124. * Lists current user session details, including each session course details
  125. * @return array Sessions details list
  126. */
  127. function userSessionList()
  128. {
  129. $tableBuySession = Database::get_main_table(TABLE_BUY_SESSION);
  130. $tableSession = Database::get_main_table(TABLE_MAIN_SESSION);
  131. $tableBuySessionRelCourse = Database::get_main_table(TABLE_BUY_SESSION_COURSE);
  132. $tableSessionRelCourse = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  133. $tableBuyCourse = Database::get_main_table(TABLE_BUY_COURSE);
  134. $tableCourse = Database::get_main_table(TABLE_MAIN_COURSE);
  135. $tableSessionRelUser = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  136. $tableBuySessionTemporal = Database::get_main_table(TABLE_BUY_SESSION_TEMPORARY);
  137. $currentUserId = api_get_user_id();
  138. // get existing sessions
  139. $sql = "SELECT a.session_id, a.visible, a.price, b.*
  140. FROM $tableBuySession a, $tableSession b
  141. WHERE a.session_id = b.id AND a.visible = 1;";
  142. $resSessions = Database::query($sql);
  143. $auxSessions = array();
  144. // loop through all sessions
  145. while ($rowSession = Database::fetch_assoc($resSessions)) {
  146. // get courses of current session
  147. $sqlSessionCourse = "SELECT DISTINCT a.id_session, a.course_code, a.nbr_users
  148. FROM $tableBuySessionRelCourse a, $tableSessionRelCourse b
  149. WHERE a.id_session = b.id_session AND a.id_session = " . $rowSession['session_id'] . ";";
  150. $resSessionCourse = Database::query($sqlSessionCourse);
  151. $aux = array();
  152. // loop through courses of current session
  153. while ($rowSessionCourse = Database::fetch_assoc($resSessionCourse)) {
  154. // get course of current session
  155. $sql = "SELECT a.course_id, a.session_id, a.visible, a.price, b.*
  156. FROM $tableBuyCourse a, $tableCourse b
  157. WHERE a.code = b.code AND a.code = '" . $rowSessionCourse['course_code'] . "';";
  158. $res = Database::query($sql);
  159. // loop inside a course of current session
  160. while ($row = Database::fetch_assoc($res)) {
  161. //check teacher
  162. $sql = "SELECT lastname, firstname
  163. FROM course_rel_user a, user b
  164. WHERE a.course_code='" . $row['code'] . "'
  165. AND a.role<>'' AND a.role<>'NULL'
  166. AND a.user_id=b.user_id;";
  167. $tmp = Database::query($sql);
  168. $rowTmp = Database::fetch_assoc($tmp);
  169. $row['teacher'] = $rowTmp['firstname'] . ' ' . $rowTmp['lastname'];
  170. //check images
  171. if (file_exists(api_get_path(SYS_COURSE_PATH) . $row['directory'] . "/course-pic85x85.png")) {
  172. $row['course_img'] = "courses/" . $row['directory'] . "/course-pic85x85.png";
  173. } else {
  174. $row['course_img'] = "main/img/without_picture.png";
  175. }
  176. $row['price'] = number_format($row['price'], 2, '.', ' ');
  177. $aux[] = $row;
  178. }
  179. }
  180. //check if the user is enrolled in the current session
  181. if ($currentUserId > 0) {
  182. $sql = "SELECT 1 FROM $tableSessionRelUser
  183. WHERE id_session ='".$rowSession['session_id']."' AND
  184. id_user = $currentUserId";
  185. Database::query($sql);
  186. if (Database::affected_rows() > 0) {
  187. $rowSession['enrolled'] = "YES";
  188. } else {
  189. $sql = "SELECT 1 FROM $tableBuySessionTemporal
  190. WHERE session_id ='".$rowSession['session_id']."' AND
  191. user_id='" . $currentUserId . "';";
  192. Database::query($sql);
  193. if (Database::affected_rows() > 0) {
  194. $rowSession['enrolled'] = "TMP";
  195. } else {
  196. $rowSession['enrolled'] = "NO";
  197. }
  198. }
  199. } else {
  200. $sql = "SELECT 1 FROM $tableBuySessionTemporal
  201. WHERE session_id ='".$rowSession['session_id']."' AND
  202. user_id='" . $currentUserId . "';";
  203. Database::query($sql);
  204. if (Database::affected_rows() > 0) {
  205. $rowSession['enrolled'] = "TMP";
  206. } else {
  207. $rowSession['enrolled'] = "NO";
  208. }
  209. }
  210. // add courses to current session
  211. $rowSession['courses'] = $aux;
  212. // add the current whole session
  213. $auxSessions[] = $rowSession;
  214. }
  215. return $auxSessions;
  216. }
  217. /**
  218. * Lists current user course details
  219. * @return array Course details list
  220. */
  221. function userCourseList()
  222. {
  223. $tableBuyCourse = Database::get_main_table(TABLE_BUY_COURSE);
  224. $tableCourse = Database::get_main_table(TABLE_MAIN_COURSE);
  225. $tableCourseRelUser = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  226. $tableBuyCourseTemporal = Database::get_main_table(TABLE_BUY_COURSE_TEMPORAL);
  227. $currentUserId = api_get_user_id();
  228. $sql = "SELECT a.course_id, a.visible, a.price, b.*
  229. FROM $tableBuyCourse a, $tableCourse b
  230. WHERE a.course_id = b.id AND a.session_id = 0 AND a.visible = 1;";
  231. $res = Database::query($sql);
  232. $aux = array();
  233. while ($row = Database::fetch_assoc($res)) {
  234. //check teacher
  235. $sql = "SELECT lastname, firstname
  236. FROM course_rel_user a, user b
  237. WHERE a.course_code='" . $row['code'] . "'
  238. AND a.role<>'' AND a.role<>'NULL'
  239. AND a.user_id=b.user_id;";
  240. $tmp = Database::query($sql);
  241. $rowTmp = Database::fetch_assoc($tmp);
  242. $row['teacher'] = $rowTmp['firstname'] . ' ' . $rowTmp['lastname'];
  243. //check if the user is enrolled
  244. if ($currentUserId > 0) {
  245. $sql = "SELECT 1 FROM $tableCourseRelUser
  246. WHERE course_code='" . $row['code'] . "'
  247. AND user_id='" . $currentUserId . "';";
  248. Database::query($sql);
  249. if (Database::affected_rows() > 0) {
  250. $row['enrolled'] = "YES";
  251. } else {
  252. $sql = "SELECT 1 FROM $tableBuyCourseTemporal
  253. WHERE course_code='" . $row['code'] . "'
  254. AND user_id='" . $currentUserId . "';";
  255. Database::query($sql);
  256. if (Database::affected_rows() > 0) {
  257. $row['enrolled'] = "TMP";
  258. } else {
  259. $row['enrolled'] = "NO";
  260. }
  261. }
  262. } else {
  263. $sql = "SELECT 1 FROM $tableBuyCourseTemporal
  264. WHERE course_code='" . $row['code'] . "'
  265. AND user_id='" . $currentUserId . "';";
  266. Database::query($sql);
  267. if (Database::affected_rows() > 0) {
  268. $row['enrolled'] = "TMP";
  269. } else {
  270. $row['enrolled'] = "NO";
  271. }
  272. }
  273. //check images
  274. if (file_exists("../../courses/" . $row['code'] . "/course-pic85x85.png")) {
  275. $row['course_img'] = "courses/" . $row['code'] . "/course-pic85x85.png";
  276. } else {
  277. $row['course_img'] = "main/img/without_picture.png";
  278. }
  279. $row['price'] = number_format($row['price'], 2, '.', ' ');
  280. $aux[] = $row;
  281. }
  282. return $aux;
  283. }
  284. /**
  285. * Checks if a session or a course is already bought
  286. * @param string Session id or course code
  287. * @param int User id
  288. * @param string What has to be checked
  289. * @return boolean True if it is already bought, and false otherwise
  290. */
  291. function checkUserBuy($parameter, $user, $type = 'COURSE')
  292. {
  293. $sql = "SELECT 1 FROM %s WHERE %s ='" . Database::escape_string($parameter) . "' AND %s ='" . intval($user) . "';";
  294. $sql = $type === 'SESSION' ?
  295. sprintf($sql, Database::get_main_table(TABLE_MAIN_SESSION_USER), 'id_session', 'id_user') :
  296. sprintf($sql, Database::get_main_table(TABLE_MAIN_COURSE_USER), 'course_code', 'user_id');
  297. Database::query($sql);
  298. if (Database::affected_rows() > 0) {
  299. return true;
  300. } else {
  301. return false;
  302. }
  303. }
  304. /**
  305. * Checks if a session or a course has already a transfer
  306. * @param string Session id or course code
  307. * @param int User id
  308. * @param string What has to be checked
  309. * @return boolean True if it has already a transfer, and false otherwise
  310. */
  311. function checkUserBuyTransfer($parameter, $user, $type = 'COURSE')
  312. {
  313. $sql = "SELECT 1 FROM %s WHERE %s ='" . Database::escape_string($parameter) . "' AND user_id ='" . intval($user) . "';";
  314. $sql = $type === 'SESSION' ?
  315. sprintf($sql, Database::get_main_table(TABLE_BUY_SESSION_TEMPORARY), 'session_id') :
  316. sprintf($sql, Database::get_main_table(TABLE_BUY_COURSE_TEMPORAL), 'course_code');
  317. Database::query($sql);
  318. if (Database::affected_rows() > 0) {
  319. return true;
  320. } else {
  321. return false;
  322. }
  323. }
  324. /**
  325. * Returns an array with all the categories
  326. * @return array All the categories
  327. */
  328. function buyCourseListCategories()
  329. {
  330. $tblCourseCategory = Database::get_main_table(TABLE_MAIN_CATEGORY);
  331. $sql = "SELECT code, name FROM $tblCourseCategory";
  332. $res = Database::query($sql);
  333. $aux = array();
  334. while ($row = Database::fetch_assoc($res)) {
  335. $aux[] = $row;
  336. }
  337. return $aux;
  338. }
  339. /**
  340. * Return an icon representing the visibility of the course
  341. * @param int $option The course visibility
  342. * @return string HTML string of the visibility icon
  343. */
  344. function getCourseVisibilityIcon($option)
  345. {
  346. $style = 'margin-bottom:-5px;margin-right:5px;';
  347. switch ($option) {
  348. case 0:
  349. return Display::return_icon('bullet_red.gif', get_plugin_lang('CourseVisibilityClosed', 'BuyCoursesPlugin'), array('style' => $style));
  350. break;
  351. case 1:
  352. return Display::return_icon('bullet_orange.gif', get_plugin_lang('Private', 'BuyCoursesPlugin'), array('style' => $style));
  353. break;
  354. case 2:
  355. return Display::return_icon('bullet_green.gif', get_plugin_lang('OpenToThePlatform', 'BuyCoursesPlugin'), array('style' => $style));
  356. break;
  357. case 3:
  358. return Display::return_icon('bullet_blue.gif', get_plugin_lang('OpenToTheWorld', 'BuyCoursesPlugin'), array('style' => $style));
  359. break;
  360. default:
  361. return Display::return_icon('bullet_grey.gif', get_plugin_lang('CourseVisibilityHidden', 'BuyCoursesPlugin'), array('style' => $style));
  362. }
  363. }
  364. /**
  365. * List the available currencies
  366. * @result array The list of currencies
  367. */
  368. function listCurrency()
  369. {
  370. $tableBuyCourseCountry = Database::get_main_table(TABLE_BUY_COURSE_COUNTRY);
  371. $sql = "SELECT * FROM $tableBuyCourseCountry
  372. ORDER BY country_name ASC";
  373. $res = Database::query($sql);
  374. $aux = array();
  375. while ($row = Database::fetch_assoc($res)) {
  376. $aux[] = $row;
  377. }
  378. return $aux;
  379. }
  380. /**
  381. * Gets the list of accounts from the buy_course_transfer table
  382. * @return array The list of accounts
  383. */
  384. function listAccounts()
  385. {
  386. $tableBuyCourseTransfer = Database::get_main_table(TABLE_BUY_COURSE_TRANSFER);
  387. $sql = "SELECT * FROM $tableBuyCourseTransfer";
  388. $res = Database::query($sql);
  389. $aux = array();
  390. while ($row = Database::fetch_assoc($res)) {
  391. $aux[] = $row;
  392. }
  393. return $aux;
  394. }
  395. /**
  396. * Gets the stored PayPal params
  397. * @return array The stored PayPal params
  398. */
  399. function paypalParameters()
  400. {
  401. $tableBuyCoursePaypal = Database::get_main_table(TABLE_BUY_COURSE_PAYPAL);
  402. $sql = "SELECT * FROM $tableBuyCoursePaypal";
  403. $res = Database::query($sql);
  404. $row = Database::fetch_assoc($res);
  405. return $row;
  406. }
  407. /**
  408. * Gets the parameters for the bank transfers payment method
  409. * @result array Bank transfer payment parameters stored
  410. */
  411. function transferParameters()
  412. {
  413. $tableBuyCourseTransfer = Database::get_main_table(TABLE_BUY_COURSE_TRANSFER);
  414. $sql = "SELECT * FROM $tableBuyCourseTransfer";
  415. $res = Database::query($sql);
  416. $aux = array();
  417. while ($row = Database::fetch_assoc($res)) {
  418. $aux[] = $row;
  419. }
  420. return $aux;
  421. }
  422. /**
  423. * Find the first enabled currency (there should be only one)
  424. * @result string The code of the active currency
  425. */
  426. function findCurrency()
  427. {
  428. $tableBuyCourseCountry = Database::get_main_table(TABLE_BUY_COURSE_COUNTRY);
  429. $sql = "SELECT * FROM $tableBuyCourseCountry WHERE status='1';";
  430. $res = Database::query($sql);
  431. $row = Database::fetch_assoc($res);
  432. return $row['currency_code'];
  433. }
  434. /**
  435. * Extended information about the session (from the session table as well as
  436. * the buy_session table)
  437. * @param string $code The session code
  438. * @return array Info about the session
  439. */
  440. function sessionInfo($code)
  441. {
  442. $tableBuySession = Database::get_main_table(TABLE_BUY_SESSION);
  443. $tableSession = Database::get_main_table(TABLE_MAIN_SESSION);
  444. $tableBuySessionRelCourse = Database::get_main_table(TABLE_BUY_SESSION_COURSE);
  445. $tableSessionRelCourse = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  446. $tableBuyCourse = Database::get_main_table(TABLE_BUY_COURSE);
  447. $tableCourse = Database::get_main_table(TABLE_MAIN_COURSE);
  448. $tableSessionRelUser = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  449. $tableBuySessionTemporal = Database::get_main_table(TABLE_BUY_SESSION_TEMPORARY);
  450. $currentUserId = api_get_user_id();
  451. $code = Database::escape_string($code);
  452. $sql = "SELECT a.session_id, a.visible, a.price, b.*
  453. FROM $tableBuySession a, $tableSession b
  454. WHERE a.session_id=b.id
  455. AND a.visible = 1
  456. AND b.id = '".$code."';";
  457. $res = Database::query($sql);
  458. $rowSession = Database::fetch_assoc($res);
  459. $sqlSessionCourse = "SELECT DISTINCT a.id_session, a.course_code, a.nbr_users
  460. FROM $tableBuySessionRelCourse a, $tableSessionRelCourse b
  461. WHERE a.id_session = b.id_session AND a.id_session = " . $rowSession['session_id'] . ";";
  462. $resSessionCourse = Database::query($sqlSessionCourse);
  463. $aux = array();
  464. // loop through courses of current session
  465. while ($rowSessionCourse = Database::fetch_assoc($resSessionCourse)) {
  466. // get course of current session
  467. $sql = "SELECT a.course_id, a.session_id, a.visible, a.price, b.*
  468. FROM $tableBuyCourse a, $tableCourse b
  469. WHERE a.code = b.code AND a.code = '".$rowSessionCourse['course_code']."' AND a.visible = 1;";
  470. $res = Database::query($sql);
  471. // loop inside a course of current session
  472. while ($row = Database::fetch_assoc($res)) {
  473. //check teacher
  474. $sql = "SELECT lastname, firstname
  475. FROM course_rel_user a, user b
  476. WHERE a.course_code='".$row['code']."'
  477. AND a.role<>'' AND a.role<>'NULL'
  478. AND a.user_id=b.user_id;";
  479. $tmp = Database::query($sql);
  480. $rowTmp = Database::fetch_assoc($tmp);
  481. $row['teacher'] = $rowTmp['firstname'].' '.$rowTmp['lastname'];
  482. //check images
  483. if (file_exists(api_get_path(SYS_COURSE_PATH).$row['directory']."/course-pic85x85.png")) {
  484. $row['course_img'] = "courses/".$row['directory']."/course-pic85x85.png";
  485. } else {
  486. $row['course_img'] = "main/img/without_picture.png";
  487. }
  488. $row['price'] = number_format($row['price'], 2, '.', ' ');
  489. $aux[] = $row;
  490. }
  491. }
  492. //check if the user is enrolled in the current session
  493. if ($currentUserId > 0) {
  494. $sql = "SELECT 1 FROM $tableSessionRelUser
  495. WHERE id_user = $currentUserId";
  496. Database::query($sql);
  497. if (Database::affected_rows() > 0) {
  498. $rowSession['enrolled'] = "YES";
  499. } else {
  500. $sql = "SELECT 1 FROM $tableBuySessionTemporal
  501. WHERE user_id='".$currentUserId."';";
  502. Database::query($sql);
  503. if (Database::affected_rows() > 0) {
  504. $rowSession['enrolled'] = "TMP";
  505. } else {
  506. $rowSession['enrolled'] = "NO";
  507. }
  508. }
  509. } else {
  510. $sql = "SELECT 1 FROM $tableBuySessionTemporal
  511. WHERE user_id='".$currentUserId."';";
  512. Database::query($sql);
  513. if (Database::affected_rows() > 0) {
  514. $rowSession['enrolled'] = "TMP";
  515. } else {
  516. $rowSession['enrolled'] = "NO";
  517. }
  518. }
  519. // add courses to current session
  520. $rowSession['courses'] = $aux;
  521. return $rowSession;
  522. }
  523. /**
  524. * Extended information about the course (from the course table as well as
  525. * the buy_course table)
  526. * @param string $code The course code
  527. * @return array Info about the course
  528. */
  529. function courseInfo($code)
  530. {
  531. $tableBuyCourse = Database::get_main_table(TABLE_BUY_COURSE);
  532. $tableCourseRelUser = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  533. $tableUser = Database::get_main_table(TABLE_MAIN_USER);
  534. $currentUserId = api_get_user_id();
  535. $code = Database::escape_string($code);
  536. $sql = "SELECT a.course_id, a.visible, a.price, b.*
  537. FROM $tableBuyCourse a, course b
  538. WHERE a.course_id=b.id
  539. AND a.visible = 1
  540. AND b.id = '" . $code . "';";
  541. $res = Database::query($sql);
  542. $row = Database::fetch_assoc($res);
  543. // Check teacher
  544. $sql = "SELECT lastname, firstname
  545. FROM $tableCourseRelUser a, $tableUser b
  546. WHERE a.course_code = '" . $row['code'] . "'
  547. AND a.role <> '' AND a.role <> 'NULL'
  548. AND a.user_id = b.user_id;";
  549. $tmp = Database::query($sql);
  550. $rowTmp = Database::fetch_assoc($tmp);
  551. $row['teacher'] = $rowTmp['firstname'] . ' ' . $rowTmp['lastname'];
  552. //Check if student is enrolled
  553. if ($currentUserId > 0) {
  554. $sql = "SELECT 1 FROM $tableCourseRelUser
  555. WHERE course_code='" . $row['code'] . "'
  556. AND user_id='" . $currentUserId . "';";
  557. Database::query($sql);
  558. if (Database::affected_rows() > 0) {
  559. $row['enrolled'] = "YES";
  560. } else {
  561. $row['enrolled'] = "NO";
  562. }
  563. } else {
  564. $row['enrolled'] = "NO";
  565. }
  566. //check img
  567. if (file_exists("../../courses/" . $row['code'] . "/course-pic85x85.png")) {
  568. $row['course_img'] = "courses/" . $row['code'] . "/course-pic85x85.png";
  569. } else {
  570. $row['course_img'] = "main/img/without_picture.png";
  571. }
  572. $row['price'] = number_format($row['price'], 2, '.', ' ');
  573. return $row;
  574. }
  575. /**
  576. * Generates a random text (used for order references)
  577. * @param int $long
  578. * @param bool $minWords
  579. * @param bool $maxWords
  580. * @param bool $number
  581. * @return string A random text
  582. */
  583. function randomText($long = 6, $minWords = true, $maxWords = true, $number = true)
  584. {
  585. $salt = $minWords ? 'abchefghknpqrstuvwxyz' : '';
  586. $salt .= $maxWords ? 'ACDEFHKNPRSTUVWXYZ' : '';
  587. $salt .= $number ? (strlen($salt) ? '2345679' : '0123456789') : '';
  588. if (strlen($salt) == 0) {
  589. return '';
  590. }
  591. $i = 0;
  592. $str = '';
  593. srand((double)microtime() * 1000000);
  594. while ($i < $long) {
  595. $number = rand(0, strlen($salt) - 1);
  596. $str .= substr($salt, $number, 1);
  597. $i++;
  598. }
  599. return $str;
  600. }
  601. /**
  602. * Generates an order reference
  603. * @result string A reference number
  604. */
  605. function calculateReference($bcCodetext)
  606. {
  607. $tableBuyTemporal = $bcCodetext === 'THIS_IS_A_SESSION' ?
  608. Database::get_main_table(TABLE_BUY_SESSION_TEMPORARY) :
  609. Database::get_main_table(TABLE_BUY_COURSE_TEMPORAL);
  610. $sql = "SELECT MAX(cod) as cod FROM $tableBuyTemporal";
  611. $res = Database::query($sql);
  612. $row = Database::fetch_assoc($res);
  613. $reference = ($row['cod'] != '') ? $row['cod'] : '1';
  614. $randomText = randomText();
  615. $reference .= $randomText;
  616. return $reference;
  617. }
  618. /**
  619. * Gets a list of pending orders
  620. * @result array List of orders
  621. * @todo Enable pagination
  622. */
  623. function pendingList($bcCodetext)
  624. {
  625. $tableBuyTemporal = $bcCodetext === 'THIS_IS_A_SESSION' ?
  626. Database::get_main_table(TABLE_BUY_SESSION_TEMPORARY) :
  627. Database::get_main_table(TABLE_BUY_COURSE_TEMPORAL);
  628. $sql = "SELECT * FROM $tableBuyTemporal;";
  629. $res = Database::query($sql);
  630. $aux = array();
  631. while ($row = Database::fetch_assoc($res)) {
  632. $aux[] = $row;
  633. }
  634. return $aux;
  635. }