sessionmanager.lib.php 106 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This is the session library for Chamilo.
  5. * All main sessions functions should be placed here.
  6. * This class provides methods for sessions management.
  7. * Include/require it in your code to use its features.
  8. * @package chamilo.library
  9. */
  10. /**
  11. * Code
  12. */
  13. class SessionManager {
  14. //See BT#4871
  15. CONST SESSION_CHANGE_USER_REASON_SCHEDULE = 1;
  16. CONST SESSION_CHANGE_USER_REASON_CLASSROOM = 2;
  17. CONST SESSION_CHANGE_USER_REASON_LOCATION = 3;
  18. CONST SESSION_CHANGE_USER_REASON_ENROLLMENT_ANNULATION = 4;
  19. CONST DEFAULT_VISIBILITY = 4; //SESSION_AVAILABLE
  20. /**
  21. * Fetches a session from the database
  22. * @param int Session ID
  23. * @return array Session details
  24. */
  25. public static function fetch($id) {
  26. $t = Database::get_main_table(TABLE_MAIN_SESSION);
  27. if ($id != strval(intval($id))) { return array(); }
  28. $s = "SELECT * FROM $t WHERE id = $id";
  29. $r = Database::query($s);
  30. if (Database::num_rows($r) != 1) { return array(); }
  31. return Database::fetch_array($r,'ASSOC');
  32. }
  33. public static function add($params) {
  34. global $_configuration;
  35. //just in case
  36. if (isset($params['id'])) {
  37. unset($params['id']);
  38. }
  39. //Check portal limits
  40. $access_url_id = 1;
  41. if (api_get_multiple_access_url()) {
  42. $access_url_id = api_get_current_access_url_id();
  43. }
  44. if (is_array($_configuration[$access_url_id]) && isset($_configuration[$access_url_id]['hosting_limit_sessions']) && $_configuration[$access_url_id]['hosting_limit_sessions'] > 0) {
  45. $num = self::count_sessions();
  46. if ($num >= $_configuration[$access_url_id]['hosting_limit_sessions']) {
  47. return get_lang('PortalSessionsLimitReached');
  48. }
  49. }
  50. $my_session_result = SessionManager::get_session_by_name($params['name']);
  51. if (empty($my_session_result)) {
  52. $session_model = new SessionModel();
  53. $session_id = $session_model->save($params);
  54. }
  55. if (!empty($session_id)) {
  56. /*
  57. Sends a message to the user_id = 1
  58. $user_info = api_get_user_info(1);
  59. $complete_name = $user_info['firstname'].' '.$user_info['lastname'];
  60. $subject = api_get_setting('siteName').' - '.get_lang('ANewSessionWasCreated');
  61. $message = get_lang('ANewSessionWasCreated')." <br /> ".get_lang('NameOfTheSession').' : '.$name;
  62. api_mail_html($complete_name, $user_info['email'], $subject, $message);
  63. *
  64. */
  65. //Saving extra fields
  66. $session_field_value = new SessionFieldValue();
  67. $params['session_id'] = $session_id;
  68. $session_field_value->save_field_values($params);
  69. //Adding to the correct URL
  70. $access_url_id = api_get_current_access_url_id();
  71. UrlManager::add_session_to_url($session_id, $access_url_id);
  72. // add event to system log
  73. $user_id = api_get_user_id();
  74. event_system(LOG_SESSION_CREATE, LOG_SESSION_ID, $session_id, api_get_utc_datetime(), $user_id);
  75. }
  76. return $session_id;
  77. }
  78. public static function update($params) {
  79. $session_model = new SessionModel();
  80. $session_model->update($params);
  81. if (!empty($params['id'])) {
  82. $session_field_value = new SessionFieldValue();
  83. $params['session_id'] = $params['id'];
  84. unset($params['id']);
  85. $session_field_value->save_field_values($params);
  86. }
  87. }
  88. function session_name_exists($session_name) {
  89. $session_name = Database::escape_string($session_name);
  90. $result = Database::fetch_array(Database::query("SELECT COUNT(*) as count FROM ".Database::get_main_table(TABLE_MAIN_SESSION)." WHERE name = '$session_name' "));
  91. return $result['count'] > 0;
  92. }
  93. static function get_count_admin() {
  94. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  95. $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  96. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  97. $where = 'WHERE 1=1 ';
  98. $user_id = api_get_user_id();
  99. if (api_is_session_admin() && api_get_setting('allow_session_admins_to_see_all_sessions') == 'false') {
  100. $where.=" WHERE s.session_admin_id = $user_id ";
  101. }
  102. $query_rows = "SELECT count(*) as total_rows
  103. FROM $tbl_session s
  104. LEFT JOIN $tbl_session_category sc ON s.session_category_id = sc.id
  105. INNER JOIN $tbl_user u ON s.id_coach = u.user_id
  106. $where ";
  107. global $_configuration;
  108. if ($_configuration['multiple_access_urls']) {
  109. $table_access_url_rel_session= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  110. $access_url_id = api_get_current_access_url_id();
  111. if ($access_url_id != -1) {
  112. $where.= " AND ar.access_url_id = $access_url_id ";
  113. $query_rows = "SELECT count(*) as total_rows
  114. FROM $tbl_session s
  115. LEFT JOIN $tbl_session_category sc ON s.session_category_id = sc.id
  116. INNER JOIN $tbl_user u ON s.id_coach = u.user_id
  117. INNER JOIN $table_access_url_rel_session ar ON ar.session_id = s.id
  118. $where ";
  119. }
  120. }
  121. $result_rows = Database::query($query_rows);
  122. $recorset = Database::fetch_array($result_rows);
  123. $num = $recorset['total_rows'];
  124. return $num;
  125. }
  126. /**
  127. * Gets the admin session list callback of the admin/session_list.php page
  128. * @param array order and limit keys
  129. */
  130. public static function get_sessions_admin($options = array()) {
  131. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  132. $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  133. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  134. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  135. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  136. $tbl_session_field_values = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
  137. $where = 'WHERE 1 = 1 ';
  138. $user_id = api_get_user_id();
  139. if (api_is_session_admin() && api_get_setting('allow_session_admins_to_manage_all_sessions') == 'false') {
  140. $where.=" AND s.session_admin_id = $user_id ";
  141. }
  142. $coach_name = " CONCAT (u.lastname , ' ', u.firstname) as coach_name ";
  143. if (api_is_western_name_order()) {
  144. $coach_name = " CONCAT (u.firstname, ' ', u.lastname) as coach_name ";
  145. }
  146. $today = api_get_utc_datetime();
  147. $inject_extra_fields = null;
  148. $extra_fields = array();
  149. $extra_fields_info = array();
  150. //for now only sessions
  151. $extra_field = new ExtraField('session');
  152. $double_fields = array();
  153. $extra_field_option = new ExtraFieldOption('session');
  154. if (isset($options['extra'])) {
  155. $extra_fields = $options['extra'];
  156. if (!empty($extra_fields)) {
  157. foreach ($extra_fields as $extra) {
  158. $inject_extra_fields .= " IF (fv.field_id = {$extra['id']}, fv.field_value, NULL ) as {$extra['field']} , ";
  159. if (isset($extra_fields_info[$extra['id']])) {
  160. $info = $extra_fields_info[$extra['id']];
  161. } else {
  162. $info = $extra_field->get($extra['id']);
  163. $extra_fields_info[$extra['id']] = $info;
  164. }
  165. if ($info['field_type'] == ExtraField::FIELD_TYPE_DOUBLE_SELECT) {
  166. $double_fields[$info['id']] = $info;
  167. }
  168. }
  169. }
  170. }
  171. $options_by_double = array();
  172. foreach ($double_fields as $double) {
  173. $my_options = $extra_field_option->get_field_options_by_field($double['id'], true);
  174. $options_by_double['extra_'.$double['field_variable']] = $my_options;
  175. //$options_by_double['extra_'.$double['field_variable'].'_second'] = true;
  176. }
  177. //var_dump($options_by_double);
  178. //sc.name as category_name,
  179. $select = "SELECT * FROM (SELECT
  180. IF (
  181. (s.access_start_date <= '$today' AND '$today' < s.access_end_date) OR
  182. (s.access_start_date = '0000-00-00 00:00:00' AND s.access_end_date = '0000-00-00 00:00:00' ) OR
  183. (s.access_start_date <= '$today' AND '0000-00-00 00:00:00' = s.access_end_date) OR
  184. ('$today' < s.access_end_date AND '0000-00-00 00:00:00' = s.access_start_date)
  185. , 1, 0)
  186. as session_active,
  187. s.name,
  188. s.nbr_courses,
  189. s.nbr_users,
  190. s.display_start_date,
  191. s.display_end_date,
  192. $coach_name,
  193. access_start_date,
  194. access_end_date,
  195. s.visibility,
  196. u.user_id,
  197. $inject_extra_fields
  198. c.title as course_title,
  199. s.id";
  200. $query = "$select FROM $tbl_session s LEFT JOIN $tbl_session_field_values fv ON (fv.session_id = s.id)
  201. LEFT JOIN $tbl_session_rel_course src ON (src.id_session = s.id)
  202. LEFT JOIN $tbl_course c ON (src.course_code = c.code)
  203. LEFT JOIN $tbl_session_category sc ON s.session_category_id = sc.id
  204. INNER JOIN $tbl_user u ON s.id_coach = u.user_id ".
  205. $where;
  206. global $_configuration;
  207. if ($_configuration['multiple_access_urls']) {
  208. $table_access_url_rel_session= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  209. $access_url_id = api_get_current_access_url_id();
  210. if ($access_url_id != -1) {
  211. $where.= " AND ar.access_url_id = $access_url_id ";
  212. $query = "$select
  213. FROM $tbl_session s LEFT JOIN $tbl_session_field_values fv ON (fv.session_id = s.id)
  214. LEFT JOIN $tbl_session_category sc ON s.session_category_id = sc.id
  215. INNER JOIN $tbl_user u ON s.id_coach = u.user_id
  216. INNER JOIN $table_access_url_rel_session ar ON ar.session_id = s.id
  217. $where";
  218. }
  219. }
  220. $query .= ") AS session_table";
  221. if (!empty($options['where'])) {
  222. $query .= ' WHERE '.$options['where'];
  223. }
  224. if (!empty($options['order'])) {
  225. $query .= " ORDER BY ".$options['order'];
  226. }
  227. if (!empty($options['limit'])) {
  228. $query .= " LIMIT ".$options['limit'];
  229. }
  230. //var_dump($query);
  231. $result = Database::query($query);
  232. $formatted_sessions = array();
  233. if (Database::num_rows($result)) {
  234. $sessions = Database::store_result($result, 'ASSOC');
  235. foreach ($sessions as $session) {
  236. $session_id = $session['id'];
  237. $session['name'] = Display::url($session['name'], "resume_session.php?id_session=".$session['id']);
  238. $session['coach_name'] = Display::url($session['coach_name'], "user_information.php?user_id=".$session['user_id']);
  239. if ($session['session_active'] == 1) {
  240. $session['session_active'] = Display::return_icon('accept.png', get_lang('Active'), array(), ICON_SIZE_SMALL);
  241. } else {
  242. $session['session_active'] = Display::return_icon('error.png', get_lang('Inactive'), array(), ICON_SIZE_SMALL);
  243. }
  244. $session = self::convert_dates_to_local($session);
  245. switch ($session['visibility']) {
  246. case SESSION_VISIBLE_READ_ONLY: //1
  247. $session['visibility'] = get_lang('ReadOnly');
  248. break;
  249. case SESSION_VISIBLE: //2
  250. case SESSION_AVAILABLE: //4
  251. $session['visibility'] = get_lang('Visible');
  252. break;
  253. case SESSION_INVISIBLE: //3
  254. $session['visibility'] = api_ucfirst(get_lang('Invisible'));
  255. break;
  256. }
  257. //Cleaning double selects
  258. //var_dump($session);
  259. foreach ($session as $key => &$value) {
  260. if (isset($options_by_double[$key]) || isset($options_by_double[$key.'_second'])) {
  261. $options = explode('::', $value);
  262. //var_dump($options);
  263. }
  264. $original_key = $key;
  265. if (strpos($key, '_second') === false) {
  266. } else {
  267. $key = str_replace('_second', '', $key);
  268. }
  269. if (isset($options_by_double[$key])) {
  270. if (isset($options[0])) {
  271. if (isset($options_by_double[$key][$options[0]])) {
  272. if (strpos($original_key, '_second') === false) {
  273. $value = $options_by_double[$key][$options[0]]['option_display_text'];
  274. } else {
  275. $value = $options_by_double[$key][$options[1]]['option_display_text'];
  276. }
  277. }
  278. }
  279. }
  280. }
  281. //Magic filter
  282. if (isset($formatted_sessions[$session_id])) {
  283. $formatted_sessions[$session_id] = self::compare_arrays_to_merge($formatted_sessions[$session_id], $session);
  284. } else {
  285. $formatted_sessions[$session_id] = $session;
  286. }
  287. }
  288. }
  289. //var_dump($formatted_sessions);
  290. return $formatted_sessions;
  291. }
  292. static function compare_arrays_to_merge($array1, $array2) {
  293. if (empty($array2)) {
  294. return $array1;
  295. }
  296. foreach ($array1 as $key => $item) {
  297. if (!isset($array1[$key])) {
  298. //My string is empty try the other one
  299. if (isset($array2[$key]) && !empty($array2[$key])) {
  300. $array1[$key] = $array2[$key];
  301. }
  302. }
  303. }
  304. return $array1;
  305. }
  306. static function convert_dates_to_local($params) {
  307. $params['display_start_date'] = api_get_local_time($params['display_start_date'], null, null, true);
  308. $params['display_end_date'] = api_get_local_time($params['display_end_date'], null, null, true);
  309. $params['access_start_date'] = api_get_local_time($params['access_start_date'], null, null, true);
  310. $params['access_end_date'] = api_get_local_time($params['access_end_date'], null, null, true);
  311. $params['coach_access_start_date'] = api_get_local_time($params['coach_access_start_date'], null, null, true);
  312. $params['coach_access_end_date'] = api_get_local_time($params['coach_access_end_date'], null, null, true);
  313. return $params;
  314. }
  315. /**
  316. * Creates a new course code based in given code
  317. *
  318. * @param string wanted code
  319. * <code>
  320. * $wanted_code = 'curse' if there are in the DB codes like curse1 curse2 the function will return: course3
  321. * if the course code doest not exist in the DB the same course code will be returned
  322. * </code>
  323. * @return string wanted unused code
  324. */
  325. function generate_nice_next_session_name($session_name) {
  326. $session_name_ok = !self::session_name_exists($session_name);
  327. if (!$session_name_ok) {
  328. $table = Database::get_main_table(TABLE_MAIN_SESSION);
  329. $session_name = Database::escape_string($session_name);
  330. $sql = "SELECT count(*) as count FROM $table WHERE name LIKE '$session_name%'";
  331. $result = Database::query($sql);
  332. if (Database::num_rows($result) > 0 ) {
  333. $row = Database::fetch_array($result);
  334. $count = $row['count'] + 1;
  335. $session_name = $session_name.'_'.$count;
  336. $result = self::session_name_exists($session_name);
  337. if (!$result) {
  338. return $session_name;
  339. }
  340. }
  341. return false;
  342. }
  343. return $session_name;
  344. }
  345. /**
  346. * Edit a session
  347. * @author Carlos Vargas from existing code
  348. * @param integer id
  349. * @param string name
  350. * @param integer year_start
  351. * @param integer month_start
  352. * @param integer day_start
  353. * @param integer year_end
  354. * @param integer month_end
  355. * @param integer day_end
  356. * @param integer nb_days_acess_before
  357. * @param integer nb_days_acess_after
  358. * @param integer nolimit
  359. * @param integer id_coach
  360. * @param integer id_session_category
  361. * @return $id;
  362. * The parameter id is a primary key
  363. **/
  364. /*
  365. public static function edit_session ($id,$name,$year_start,$month_start,$day_start,$year_end,$month_end,$day_end,$nb_days_acess_before,$nb_days_acess_after,$nolimit,$id_coach, $id_session_category, $id_visibility, $start_limit = true, $end_limit = true) {
  366. global $_user;
  367. $name=trim(stripslashes($name));
  368. $year_start=intval($year_start);
  369. $month_start=intval($month_start);
  370. $day_start=intval($day_start);
  371. $year_end=intval($year_end);
  372. $month_end=intval($month_end);
  373. $day_end=intval($day_end);
  374. $id_coach= intval($id_coach);
  375. $nb_days_acess_before= intval($nb_days_acess_before);
  376. $nb_days_acess_after = intval($nb_days_acess_after);
  377. $id_session_category = intval($id_session_category);
  378. $id_visibility = intval($id_visibility);
  379. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  380. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  381. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  382. if (empty($nolimit)) {
  383. $date_start = "$year_start-".(($month_start < 10)?"0$month_start":$month_start)."-".(($day_start < 10)?"0$day_start":$day_start);
  384. $date_end = "$year_end-".(($month_end < 10)?"0$month_end":$month_end)."-".(($day_end < 10)?"0$day_end":$day_end);
  385. } else {
  386. $date_start = "0000-00-00";
  387. $date_end = "0000-00-00";
  388. $id_visibility = 1;//force read only
  389. }
  390. if (!empty($no_end_limit)) {
  391. $date_end = "0000-00-00";
  392. }
  393. if (empty($end_limit)) {
  394. $date_end ="0000-00-00";
  395. $id_visibility = 1;//force read only
  396. }
  397. if (empty($start_limit)) {
  398. $date_start ="0000-00-00";
  399. }
  400. if (empty($name)) {
  401. $msg=get_lang('SessionNameIsRequired');
  402. return $msg;
  403. } elseif (empty($id_coach)) {
  404. $msg=get_lang('CoachIsRequired');
  405. return $msg;
  406. } elseif (!empty($start_limit) && empty($nolimit) && (!$month_start || !$day_start || !$year_start || !checkdate($month_start,$day_start,$year_start))) {
  407. $msg=get_lang('InvalidStartDate');
  408. return $msg;
  409. } elseif (!empty($end_limit) && empty($nolimit) && (!$month_end || !$day_end || !$year_end || !checkdate($month_end,$day_end,$year_end))) {
  410. $msg=get_lang('InvalidEndDate');
  411. return $msg;
  412. } elseif (!empty($start_limit) && !empty($end_limit) && empty($nolimit) && $date_start >= $date_end) {
  413. $msg=get_lang('StartDateShouldBeBeforeEndDate');
  414. return $msg;
  415. } else {
  416. $rs = Database::query("SELECT id FROM $tbl_session WHERE name='".Database::escape_string($name)."'");
  417. $exists = false;
  418. while ($row = Database::fetch_array($rs)) {
  419. if($row['id']!=$id)
  420. $exists = true;
  421. }
  422. if ($exists) {
  423. $msg=get_lang('SessionNameAlreadyExists');
  424. return $msg;
  425. } else {
  426. $sql="UPDATE $tbl_session " .
  427. "SET name='".Database::escape_string($name)."',
  428. date_start='".$date_start."',
  429. date_end='".$date_end."',
  430. id_coach='".$id_coach."',
  431. nb_days_access_before_beginning = ".$nb_days_acess_before.",
  432. nb_days_access_after_end = ".$nb_days_acess_after.",
  433. session_category_id = ".$id_session_category." ,
  434. visibility= ".$id_visibility."
  435. WHERE id='$id'";
  436. Database::query($sql);
  437. return $id;
  438. }
  439. }
  440. }
  441. */
  442. /**
  443. * Delete session
  444. * @author Carlos Vargas from existing code
  445. * @param array id_checked
  446. * @param boolean optional, true if the function is called by a webservice, false otherwise.
  447. * @return void Nothing, or false on error
  448. * The parameters is a array to delete sessions
  449. **/
  450. public static function delete_session ($id_checked,$from_ws = false) {
  451. $tbl_session= Database::get_main_table(TABLE_MAIN_SESSION);
  452. $tbl_session_rel_course= Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  453. $tbl_session_rel_course_rel_user= Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  454. $tbl_session_rel_user= Database::get_main_table(TABLE_MAIN_SESSION_USER);
  455. $tbl_url_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  456. global $_user;
  457. if(is_array($id_checked)) {
  458. $id_checked = Database::escape_string(implode(',',$id_checked));
  459. } else {
  460. $id_checked = intval($id_checked);
  461. }
  462. if (!api_is_platform_admin() && !$from_ws) {
  463. $sql = 'SELECT session_admin_id FROM '.Database :: get_main_table(TABLE_MAIN_SESSION).' WHERE id='.$id_checked;
  464. $rs = Database::query($sql);
  465. if (Database::result($rs,0,0)!=$_user['user_id']) {
  466. api_not_allowed(true);
  467. }
  468. }
  469. Database::query("DELETE FROM $tbl_session WHERE id IN($id_checked)");
  470. Database::query("DELETE FROM $tbl_session_rel_course WHERE id_session IN($id_checked)");
  471. Database::query("DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session IN($id_checked)");
  472. Database::query("DELETE FROM $tbl_session_rel_user WHERE id_session IN($id_checked)");
  473. Database::query("DELETE FROM $tbl_url_session WHERE session_id IN($id_checked)");
  474. // delete extra session fields
  475. $t_sf = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
  476. $t_sfv = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
  477. // Delete extra fields from session where field variable is "SECCION"
  478. $sql = "SELECT t_sfv.field_id FROM $t_sfv t_sfv, $t_sf t_sf WHERE t_sfv.session_id = '$id_checked' AND t_sf.field_variable = 'SECCION' ";
  479. $rs_field = Database::query($sql);
  480. $field_id = 0;
  481. if (Database::num_rows($rs_field) == 1) {
  482. $row_field = Database::fetch_row($rs_field);
  483. $field_id = $row_field[0];
  484. $sql_delete_sfv = "DELETE FROM $t_sfv WHERE session_id = '$id_checked' AND field_id = '$field_id'";
  485. $rs_delete_sfv = Database::query($sql_delete_sfv);
  486. }
  487. $sql = "SELECT * FROM $t_sfv WHERE field_id = '$field_id' ";
  488. $rs_field_id = Database::query($sql);
  489. if (Database::num_rows($rs_field_id) == 0) {
  490. $sql_delete_sf = "DELETE FROM $t_sf WHERE id = '$field_id'";
  491. $rs_delete_sf = Database::query($sql_delete_sf);
  492. }
  493. /*
  494. $sql = "SELECT distinct field_id FROM $t_sfv WHERE session_id = '$id_checked'";
  495. $res_field_ids = @Database::query($sql);
  496. if (Database::num_rows($res_field_ids) > 0) {
  497. while($row_field_id = Database::fetch_row($res_field_ids)){
  498. $field_ids[] = $row_field_id[0];
  499. }
  500. }
  501. //delete from table_session_field_value from a given session id
  502. $sql_session_field_value = "DELETE FROM $t_sfv WHERE session_id = '$id_checked'";
  503. @Database::query($sql_session_field_value);
  504. $sql = "SELECT distinct field_id FROM $t_sfv";
  505. $res_field_all_ids = @Database::query($sql);
  506. if (Database::num_rows($res_field_all_ids) > 0) {
  507. while($row_field_all_id = Database::fetch_row($res_field_all_ids)){
  508. $field_all_ids[] = $row_field_all_id[0];
  509. }
  510. }
  511. if (count($field_ids) > 0 && count($field_all_ids) > 0) {
  512. foreach($field_ids as $field_id) {
  513. // check if field id is used into table field value
  514. if (in_array($field_id,$field_all_ids)) {
  515. continue;
  516. } else {
  517. $sql_session_field = "DELETE FROM $t_sf WHERE id = '$field_id'";
  518. Database::query($sql_session_field);
  519. }
  520. }
  521. }
  522. */
  523. // Add event to system log
  524. $user_id = api_get_user_id();
  525. event_system(LOG_SESSION_DELETE, LOG_SESSION_ID, $id_checked, api_get_utc_datetime(), $user_id);
  526. }
  527. /**
  528. * Subscribes users (students) to the given session and optionally (default) unsubscribes previous users
  529. * @author Carlos Vargas from existing code
  530. * @param integer Session ID
  531. * @param array List of user IDs
  532. * @param bool Whether to unsubscribe existing users (true, default) or not (false)
  533. * @return void Nothing, or false on error
  534. **/
  535. public static function suscribe_users_to_session($id_session, $user_list, $session_visibility = SESSION_VISIBLE_READ_ONLY, $empty_users = true, $send_email = false) {
  536. if ($id_session!= strval(intval($id_session))) return false;
  537. foreach($user_list as $intUser){
  538. if ($intUser!= strval(intval($intUser))) return false;
  539. }
  540. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  541. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  542. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  543. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  544. $session_info = api_get_session_info($id_session);
  545. $session_name = $session_info['name'];
  546. //from function parameter
  547. if (empty($session_visibility)) {
  548. $session_visibility = $session_info['visibility']; //loaded from DB
  549. //default status loaded if empty
  550. if (empty($session_visibility))
  551. $session_visibility = SESSION_VISIBLE_READ_ONLY; // by default readonly 1
  552. } else {
  553. if (!in_array($session_visibility, array(SESSION_VISIBLE_READ_ONLY, SESSION_VISIBLE, SESSION_INVISIBLE))) {
  554. $session_visibility = SESSION_VISIBLE_READ_ONLY;
  555. }
  556. }
  557. $sql = "SELECT id_user FROM $tbl_session_rel_course_rel_user WHERE id_session = '$id_session' AND status = 0";
  558. $result = Database::query($sql);
  559. $existingUsers = array();
  560. while ($row = Database::fetch_array($result)) {
  561. $existingUsers[] = $row['id_user'];
  562. }
  563. $sql = "SELECT course_code FROM $tbl_session_rel_course WHERE id_session = '$id_session'";
  564. $result = Database::query($sql);
  565. $course_list = array();
  566. while ($row = Database::fetch_array($result)) {
  567. $course_list[] = $row['course_code'];
  568. }
  569. if ($send_email) {
  570. //global $_configuration;
  571. //sending emails only
  572. if (is_array($user_list) && count($user_list)>0) {
  573. foreach ($user_list as $user_id) {
  574. if (!in_array($user_id, $existingUsers)) {
  575. $subject = '['.get_setting('siteName').'] '.get_lang('YourReg').' '.get_setting('siteName');
  576. $user_info = api_get_user_info($user_id);
  577. $content = get_lang('Dear')." ".stripslashes($user_info['complete_name']).",\n\n".sprintf(get_lang('YouAreRegisterToSessionX'), $session_name) ." \n\n" .get_lang('Address') ." ". get_setting('siteName') ." ". get_lang('Is') ." : ". api_get_path(WEB_PATH) ."\n\n". get_lang('Problem'). "\n\n". get_lang('Formula').",\n\n".get_setting('administratorName')." ".get_setting('administratorSurname')."\n". get_lang('Manager'). " ".get_setting('siteName')."\nT. ".get_setting('administratorTelephone')."\n" .get_lang('Email') ." : ".get_setting('emailAdministrator');
  578. MessageManager::send_message($user_id, $subject, $content, array(), array(), null, null, null, null, null);
  579. /*$emailheaders = 'From: '.get_setting('administratorName').' '.get_setting('administratorSurname').' <'.get_setting('emailAdministrator').">\n";
  580. $emailheaders .= 'Reply-To: '.get_setting('emailAdministrator');
  581. $emailbody = get_lang('Dear')." ".stripslashes(api_get_person_name($firstname, $lastname)).",\n\n".sprintf(get_lang('YouAreRegisterToSessionX'), $session_name) ." \n\n" .get_lang('Address') ." ". get_setting('siteName') ." ". get_lang('Is') ." : ". api_get_path(WEB_PATH) ."\n\n". get_lang('Problem'). "\n\n". get_lang('Formula').",\n\n".get_setting('administratorName')." ".get_setting('administratorSurname')."\n". get_lang('Manager'). " ".get_setting('siteName')."\nT. ".get_setting('administratorTelephone')."\n" .get_lang('Email') ." : ".get_setting('emailAdministrator');
  582. @api_send_mail($emailto, $emailsubject, $emailbody, $emailheaders);*/
  583. }
  584. }
  585. }
  586. }
  587. foreach ($course_list as $enreg_course) {
  588. // for each course in the session
  589. $nbr_users = 0;
  590. $enreg_course = Database::escape_string($enreg_course);
  591. // delete existing users
  592. if ($empty_users) {
  593. foreach ($existingUsers as $existing_user) {
  594. if (!in_array($existing_user, $user_list)) {
  595. $sql = "DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session='$id_session' AND course_code='$enreg_course' AND id_user='$existing_user' AND status = 0";
  596. Database::query($sql);
  597. if (Database::affected_rows()) {
  598. $nbr_users--;
  599. }
  600. }
  601. }
  602. }
  603. //Replace with this new function
  604. //
  605. // insert new users into session_rel_course_rel_user and ignore if they already exist
  606. foreach ($user_list as $enreg_user) {
  607. if(!in_array($enreg_user, $existingUsers)) {
  608. $enreg_user = Database::escape_string($enreg_user);
  609. $insert_sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user(id_session, course_code, id_user, visibility, status) VALUES('$id_session','$enreg_course','$enreg_user','$session_visibility', '0')";
  610. Database::query($insert_sql);
  611. if(Database::affected_rows()) {
  612. $nbr_users++;
  613. }
  614. }
  615. }
  616. // count users in this session-course relation
  617. $sql = "SELECT COUNT(id_user) as nbUsers FROM $tbl_session_rel_course_rel_user WHERE id_session='$id_session' AND course_code='$enreg_course' AND status<>2";
  618. $rs = Database::query($sql);
  619. list($nbr_users) = Database::fetch_array($rs);
  620. // update the session-course relation to add the users total
  621. $update_sql = "UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users WHERE id_session='$id_session' AND course_code='$enreg_course'";
  622. Database::query($update_sql);
  623. }
  624. // Delete users from the session
  625. if ($empty_users===true) {
  626. Database::query("DELETE FROM $tbl_session_rel_user WHERE id_session = $id_session AND relation_type<>".SESSION_RELATION_TYPE_RRHH."");
  627. }
  628. // Insert missing users into session
  629. $nbr_users = 0;
  630. foreach ($user_list as $enreg_user) {
  631. $enreg_user = Database::escape_string($enreg_user);
  632. $insert_sql = "INSERT IGNORE INTO $tbl_session_rel_user (id_session, id_user) VALUES ('$id_session','$enreg_user')";
  633. Database::query($insert_sql);
  634. //Reset moved_to just in case
  635. $update_sql = "UPDATE $tbl_session_rel_user SET moved_to = 0 , moved_status = 0, moved_at ='0000-00-00 00:00:00' WHERE id_session = $id_session AND id_user = $enreg_user";
  636. Database::query($update_sql);
  637. $nbr_users++;
  638. }
  639. // update number of users in the session
  640. $nbr_users = count($user_list);
  641. if ($empty_users) {
  642. // update number of users in the session
  643. $update_sql = "UPDATE $tbl_session SET nbr_users= $nbr_users WHERE id='$id_session' ";
  644. Database::query($update_sql);
  645. } else {
  646. $update_sql = "UPDATE $tbl_session SET nbr_users= nbr_users + $nbr_users WHERE id='$id_session' ";
  647. Database::query($update_sql);
  648. }
  649. }
  650. function subscribe_users_to_session_course($user_list, $session_id, $course_code, $session_visibility = SESSION_VISIBLE_READ_ONLY ) {
  651. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  652. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  653. if (empty($user_list) || empty($session_id) || empty($course_code)) {
  654. return false;
  655. }
  656. $session_id = intval($session_id);
  657. $course_code = Database::escape_string($course_code);
  658. $session_visibility = intval($session_visibility);
  659. $nbr_users = 0;
  660. foreach ($user_list as $enreg_user) {
  661. //if (!in_array($enreg_user, $existingUsers)) {
  662. $enreg_user = intval($enreg_user);
  663. $insert_sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user(id_session,course_code,id_user,visibility)
  664. VALUES ('$session_id','$course_code','$enreg_user','$session_visibility')";
  665. Database::query($insert_sql);
  666. if (Database::affected_rows()) {
  667. $nbr_users++;
  668. }
  669. //}
  670. }
  671. // count users in this session-course relation
  672. $sql = "SELECT COUNT(id_user) as nbUsers FROM $tbl_session_rel_course_rel_user WHERE id_session='$id_session' AND course_code='$enreg_course' AND status<>2";
  673. $rs = Database::query($sql);
  674. list($nbr_users) = Database::fetch_array($rs);
  675. // update the session-course relation to add the users total
  676. $update_sql = "UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users WHERE id_session='$id_session' AND course_code='$enreg_course'";
  677. Database::query($update_sql);
  678. }
  679. /**
  680. * Unsubscribe user from session
  681. *
  682. * @param int session id
  683. * @param int user id
  684. * @param int reason of unsubscription see function get_session_change_user_reasons()
  685. * @return bool true in case of success, false in case of error
  686. */
  687. public static function unsubscribe_user_from_session($session_id, $user_id) {
  688. $session_id = intval($session_id);
  689. $user_id = intval($user_id);
  690. $reason_id = intval($reason_id);
  691. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  692. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  693. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  694. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  695. $user_status = SessionManager::get_user_status_in_session($session_id, $user_id);
  696. if ($user_status['moved_to'] != 0) {
  697. //You can't subscribe a user that was moved
  698. return false;
  699. }
  700. $delete_sql = "DELETE FROM $tbl_session_rel_user WHERE id_session = '$session_id' AND id_user ='$user_id' AND relation_type<>".SESSION_RELATION_TYPE_RRHH."";
  701. Database::query($delete_sql);
  702. $return = Database::affected_rows();
  703. // Update number of users
  704. $update_sql = "UPDATE $tbl_session SET nbr_users = nbr_users - $return WHERE id='$session_id' ";
  705. Database::query($update_sql);
  706. // Get the list of courses related to this session
  707. $course_list = SessionManager::get_course_list_by_session_id($session_id);
  708. if (!empty($course_list)) {
  709. foreach($course_list as $course) {
  710. $course_code = $course['code'];
  711. // Delete user from course
  712. Database::query("DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session='$session_id' AND course_code='$course_code' AND id_user='$user_id'");
  713. if(Database::affected_rows()) {
  714. // Update number of users in this relation
  715. Database::query("UPDATE $tbl_session_rel_course SET nbr_users=nbr_users - 1 WHERE id_session='$session_id' AND course_code='$course_code'");
  716. }
  717. }
  718. }
  719. return true;
  720. }
  721. /**
  722. *
  723. * @param int $session_id
  724. * @param array user id list
  725. * @param string $course_code
  726. */
  727. static function unsubscribe_user_from_course_session($session_id, $user_list, $course_code) {
  728. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  729. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  730. if (is_array($user_list) && count($user_list)>0 ) {
  731. array_map('intval', $user_list);
  732. $user_list = implode(',', $user_list);
  733. }
  734. $session_id = intval($session_id);
  735. $course_code = Database::escape_string($course_code);
  736. if (!empty($user_list)) {
  737. Database::query("DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session='$session_id' AND course_code='".$course_code."' AND id_user IN($user_list)");
  738. $nbr_affected_rows = Database::affected_rows();
  739. Database::query("UPDATE $tbl_session_rel_course SET nbr_users=nbr_users-$nbr_affected_rows WHERE id_session='$session_id' AND course_code='".$course_code."'");
  740. }
  741. }
  742. /** Subscribes courses to the given session and optionally (default) unsubscribes previous users
  743. * @author Carlos Vargas from existing code
  744. * @param int Session ID
  745. * @param array List of courses IDs
  746. * @param bool Whether to unsubscribe existing users (true, default) or not (false)
  747. * @return void Nothing, or false on error
  748. **/
  749. public static function add_courses_to_session ($id_session, $course_list, $empty_courses=true) {
  750. // security checks
  751. if ($id_session!= strval(intval($id_session))) { return false; }
  752. //foreach($course_list as $intCourse){
  753. // if ($intCourse!= strval(intval($intCourse))) { return false; }
  754. //}
  755. // initialisation
  756. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  757. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  758. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  759. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  760. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  761. // get general coach ID
  762. $id_coach = Database::query("SELECT id_coach FROM $tbl_session WHERE id=$id_session");
  763. $id_coach = Database::fetch_array($id_coach);
  764. $id_coach = $id_coach[0];
  765. // get list of courses subscribed to this session
  766. $rs = Database::query("SELECT course_code FROM $tbl_session_rel_course WHERE id_session=$id_session");
  767. $existingCourses = Database::store_result($rs);
  768. $nbr_courses=count($existingCourses);
  769. // get list of users subscribed to this session
  770. $sql="SELECT id_user
  771. FROM $tbl_session_rel_user
  772. WHERE id_session = $id_session AND relation_type<>".SESSION_RELATION_TYPE_RRHH."";
  773. $result=Database::query($sql);
  774. $user_list=Database::store_result($result);
  775. // remove existing courses from the session
  776. if ($empty_courses===true) {
  777. foreach ($existingCourses as $existingCourse) {
  778. if (!in_array($existingCourse['course_code'], $course_list)){
  779. Database::query("DELETE FROM $tbl_session_rel_course WHERE course_code='".$existingCourse['course_code']."' AND id_session=$id_session");
  780. Database::query("DELETE FROM $tbl_session_rel_course_rel_user WHERE course_code='".$existingCourse['course_code']."' AND id_session=$id_session");
  781. }
  782. }
  783. $nbr_courses=0;
  784. }
  785. // Pass through the courses list we want to add to the session
  786. foreach ($course_list as $enreg_course) {
  787. $enreg_course = Database::escape_string($enreg_course);
  788. $exists = false;
  789. // check if the course we want to add is already subscribed
  790. foreach ($existingCourses as $existingCourse) {
  791. if ($enreg_course == $existingCourse['course_code']) {
  792. $exists=true;
  793. }
  794. }
  795. if (!$exists) {
  796. //if the course isn't subscribed yet
  797. $sql_insert_rel_course= "INSERT INTO $tbl_session_rel_course (id_session,course_code) VALUES ('$id_session','$enreg_course')";
  798. Database::query($sql_insert_rel_course);
  799. //We add the current course in the existing courses array, to avoid adding another time the current course
  800. $existingCourses[]=array('course_code'=>$enreg_course);
  801. $nbr_courses++;
  802. // subscribe all the users from the session to this course inside the session
  803. $nbr_users=0;
  804. foreach ($user_list as $enreg_user) {
  805. $enreg_user_id = Database::escape_string($enreg_user['id_user']);
  806. $sql_insert = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user (id_session,course_code,id_user) VALUES ('$id_session','$enreg_course','$enreg_user_id')";
  807. Database::query($sql_insert);
  808. if (Database::affected_rows()) {
  809. $nbr_users++;
  810. }
  811. }
  812. Database::query("UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users WHERE id_session='$id_session' AND course_code='$enreg_course'");
  813. }
  814. }
  815. Database::query("UPDATE $tbl_session SET nbr_courses=$nbr_courses WHERE id='$id_session'");
  816. }
  817. /**
  818. * Unsubscribe course from a session
  819. *
  820. * @param int Session id
  821. * @param int Course id
  822. * @return bool True in case of success, false otherwise
  823. */
  824. public static function unsubscribe_course_from_session($session_id, $course_id) {
  825. $session_id = (int)$session_id;
  826. $course_id = (int)$course_id;
  827. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  828. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  829. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  830. // Get course code
  831. $course_code = CourseManager::get_course_code_from_course_id($course_id);
  832. if($course_code == 0) {
  833. return false;
  834. }
  835. // Unsubscribe course
  836. Database::query("DELETE FROM $tbl_session_rel_course WHERE course_code='$course_code' AND id_session='$session_id'");
  837. $nb_affected = Database::affected_rows();
  838. Database::query("DELETE FROM $tbl_session_rel_course_rel_user WHERE course_code='$course_code' AND id_session='$session_id'");
  839. if($nb_affected > 0) {
  840. // Update number of courses in the session
  841. Database::query("UPDATE $tbl_session SET nbr_courses= nbr_courses + $nb_affected WHERE id='$session_id' ");
  842. return true;
  843. } else {
  844. return false;
  845. }
  846. }
  847. /**
  848. * Creates a new extra field for a given session
  849. * @param string Field's internal variable name
  850. * @param int Field's type
  851. * @param string Field's language var name
  852. * @return int new extra field id
  853. */
  854. public static function create_session_extra_field ($fieldvarname, $fieldtype, $fieldtitle) {
  855. // database table definition
  856. $params = array('field_variable' => $fieldvarname,
  857. 'field_type' => $fieldtype,
  858. 'field_display_text' => $fieldtitle
  859. );
  860. $session_field = new SessionField();
  861. $field_id = $session_field->save($params);
  862. return $field_id;/*
  863. $t_sf = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
  864. $fieldvarname = Database::escape_string($fieldvarname);
  865. $fieldtitle = Database::escape_string($fieldtitle);
  866. $fieldtype = (int)$fieldtype;
  867. $time = api_get_utc_datetime();
  868. $sql_field = "SELECT id FROM $t_sf WHERE field_variable = '$fieldvarname'";
  869. $res_field = Database::query($sql_field);
  870. $r_field = Database::fetch_row($res_field);
  871. if (Database::num_rows($res_field)>0) {
  872. $field_id = $r_field[0];
  873. } else {
  874. // save new fieldlabel into course_field table
  875. $sql = "SELECT MAX(field_order) FROM $t_sf";
  876. $res = Database::query($sql);
  877. $order = 0;
  878. if (Database::num_rows($res)>0) {
  879. $row = Database::fetch_row($res);
  880. $order = $row[0]+1;
  881. }
  882. $sql = "INSERT INTO $t_sf
  883. SET field_type = '$fieldtype',
  884. field_variable = '$fieldvarname',
  885. field_display_text = '$fieldtitle',
  886. field_order = '$order',
  887. tms = '$time'";
  888. Database::query($sql);
  889. $field_id = Database::insert_id();
  890. }
  891. return $field_id;*/
  892. }
  893. /**
  894. * Update an extra field value for a given session
  895. * @param integer Course ID
  896. * @param string Field variable name
  897. * @param string Field value
  898. * @return boolean true if field updated, false otherwise
  899. */
  900. public static function update_session_extra_field_value ($session_id, $fname, $fvalue = null) {
  901. $session_field_value = new SessionFieldValue();
  902. $session_field_value->update($params);
  903. $t_sf = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
  904. $t_sfv = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
  905. $fname = Database::escape_string($fname);
  906. $session_id = (int)$session_id;
  907. $tms = api_get_utc_datetime();
  908. $fvalues = '';
  909. if (is_array($fvalue)) {
  910. foreach($fvalue as $val) {
  911. $fvalues .= Database::escape_string($val).';';
  912. }
  913. if(!empty($fvalues)) {
  914. $fvalues = substr($fvalues,0,-1);
  915. }
  916. } else {
  917. $fvalues = Database::escape_string($fvalue);
  918. }
  919. $sqlsf = "SELECT * FROM $t_sf WHERE field_variable='$fname'";
  920. $ressf = Database::query($sqlsf);
  921. if (Database::num_rows($ressf)==1) {
  922. // ok, the field exists
  923. // Check if enumerated field, if the option is available
  924. $rowsf = Database::fetch_array($ressf);
  925. $sqlsfv = "SELECT * FROM $t_sfv WHERE session_id = '$session_id' AND field_id = '".$rowsf['id']."' ORDER BY id";
  926. $ressfv = Database::query($sqlsfv);
  927. $n = Database::num_rows($ressfv);
  928. if ($n>1) {
  929. //problem, we already have to values for this field and user combination - keep last one
  930. while($rowsfv = Database::fetch_array($ressfv)) {
  931. if($n > 1) {
  932. $sqld = "DELETE FROM $t_sfv WHERE id = ".$rowsfv['id'];
  933. $resd = Database::query($sqld);
  934. $n--;
  935. }
  936. $rowsfv = Database::fetch_array($ressfv);
  937. if ($rowsfv['field_value'] != $fvalues) {
  938. $sqlu = "UPDATE $t_sfv SET field_value = '$fvalues', tms = '$tms' WHERE id = ".$rowsfv['id'];
  939. $resu = Database::query($sqlu);
  940. return($resu?true:false);
  941. }
  942. return true;
  943. }
  944. } else if ($n==1) {
  945. //we need to update the current record
  946. $rowsfv = Database::fetch_array($ressfv);
  947. if($rowsfv['field_value'] != $fvalues) {
  948. $sqlu = "UPDATE $t_sfv SET field_value = '$fvalues', tms = '$tms' WHERE id = ".$rowsfv['id'];
  949. //error_log('UM::update_extra_field_value: '.$sqlu);
  950. $resu = Database::query($sqlu);
  951. return($resu?true:false);
  952. }
  953. return true;
  954. } else {
  955. $sqli = "INSERT INTO $t_sfv (session_id,field_id,field_value,tms) " .
  956. "VALUES ('$session_id',".$rowsf['id'].",'$fvalues', '$tms')";
  957. //error_log('UM::update_extra_field_value: '.$sqli);
  958. $resi = Database::query($sqli);
  959. return($resi?true:false);
  960. }
  961. } else {
  962. return false; //field not found
  963. }
  964. }
  965. /**
  966. * Checks the relationship between a session and a course.
  967. * @param int $session_id
  968. * @param int $course_id
  969. * @return bool Returns TRUE if the session and the course are related, FALSE otherwise.
  970. * */
  971. public static function relation_session_course_exist ($session_id, $course_id) {
  972. $tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  973. $return_value = false;
  974. $sql= "SELECT course_code FROM $tbl_session_course WHERE id_session = ".Database::escape_string($session_id)." AND course_code = '".Database::escape_string($course_id)."'";
  975. $result = Database::query($sql);
  976. $num = Database::num_rows($result);
  977. if ($num>0) {
  978. $return_value = true;
  979. }
  980. return $return_value;
  981. }
  982. /**
  983. * Get the session information by name
  984. * @param string session name
  985. * @return mixed false if the session does not exist, array if the session exist
  986. * */
  987. public static function get_session_by_name ($session_name) {
  988. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  989. $sql = 'SELECT id, id_coach, date_start, date_end FROM '.$tbl_session.' WHERE name="'.Database::escape_string($session_name).'"';
  990. $result = Database::query($sql);
  991. $num = Database::num_rows($result);
  992. if ($num>0){
  993. return Database::fetch_array($result);
  994. } else {
  995. return false;
  996. }
  997. }
  998. /**
  999. * Create a session category
  1000. * @author Jhon Hinojosa <jhon.hinojosa@dokeos.com>, from existing code
  1001. * @param string name
  1002. * @param integer year_start
  1003. * @param integer month_start
  1004. * @param integer day_start
  1005. * @param integer year_end
  1006. * @param integer month_end
  1007. * @param integer day_end
  1008. * @return $id_session;
  1009. **/
  1010. public static function create_category_session($sname,$syear_start,$smonth_start,$sday_start,$syear_end,$smonth_end, $sday_end){
  1011. $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  1012. $name= trim($sname);
  1013. $year_start= intval($syear_start);
  1014. $month_start=intval($smonth_start);
  1015. $day_start=intval($sday_start);
  1016. $year_end=intval($syear_end);
  1017. $month_end=intval($smonth_end);
  1018. $day_end=intval($sday_end);
  1019. $date_start = "$year_start-".(($month_start < 10)?"0$month_start":$month_start)."-".(($day_start < 10)?"0$day_start":$day_start);
  1020. $date_end = "$year_end-".(($month_end < 10)?"0$month_end":$month_end)."-".(($day_end < 10)?"0$day_end":$day_end);
  1021. if (empty($name)) {
  1022. $msg=get_lang('SessionCategoryNameIsRequired');
  1023. return $msg;
  1024. } elseif (!$month_start || !$day_start || !$year_start || !checkdate($month_start,$day_start,$year_start)) {
  1025. $msg=get_lang('InvalidStartDate');
  1026. return $msg;
  1027. } elseif (!$month_end && !$day_end && !$year_end) {
  1028. $date_end = "null";
  1029. } elseif (!$month_end || !$day_end || !$year_end || !checkdate($month_end,$day_end,$year_end)) {
  1030. $msg=get_lang('InvalidEndDate');
  1031. return $msg;
  1032. } elseif($date_start >= $date_end) {
  1033. $msg=get_lang('StartDateShouldBeBeforeEndDate');
  1034. return $msg;
  1035. }
  1036. $access_url_id = api_get_current_access_url_id();
  1037. $sql = "INSERT INTO $tbl_session_category (name, date_start, date_end, access_url_id)
  1038. VALUES('".Database::escape_string($name)."','$date_start','$date_end', '$access_url_id')";
  1039. Database::query($sql);
  1040. $id_session = Database::insert_id();
  1041. // Add event to system log
  1042. $user_id = api_get_user_id();
  1043. event_system(LOG_SESSION_CATEGORY_CREATE, LOG_SESSION_CATEGORY_ID, $id_session, api_get_utc_datetime(), $user_id);
  1044. return $id_session;
  1045. }
  1046. /**
  1047. * Edit a sessions categories
  1048. * @author Jhon Hinojosa <jhon.hinojosa@dokeos.com>,from existing code
  1049. * @param integer id
  1050. * @param string name
  1051. * @param integer year_start
  1052. * @param integer month_start
  1053. * @param integer day_start
  1054. * @param integer year_end
  1055. * @param integer month_end
  1056. * @param integer day_end
  1057. * @return $id;
  1058. * The parameter id is a primary key
  1059. **/
  1060. public static function edit_category_session($id, $sname,$syear_start,$smonth_start,$sday_start,$syear_end,$smonth_end, $sday_end){
  1061. $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  1062. $name= trim($sname);
  1063. $year_start= intval($syear_start);
  1064. $month_start=intval($smonth_start);
  1065. $day_start=intval($sday_start);
  1066. $year_end=intval($syear_end);
  1067. $month_end=intval($smonth_end);
  1068. $day_end=intval($sday_end);
  1069. $id=intval($id);
  1070. $date_start = "$year_start-".(($month_start < 10)?"0$month_start":$month_start)."-".(($day_start < 10)?"0$day_start":$day_start);
  1071. $date_end = "$year_end-".(($month_end < 10)?"0$month_end":$month_end)."-".(($day_end < 10)?"0$day_end":$day_end);
  1072. if (empty($name)) {
  1073. $msg=get_lang('SessionCategoryNameIsRequired');
  1074. return $msg;
  1075. } elseif (!$month_start || !$day_start || !$year_start || !checkdate($month_start,$day_start,$year_start)) {
  1076. $msg=get_lang('InvalidStartDate');
  1077. return $msg;
  1078. } elseif (!$month_end && !$day_end && !$year_end) {
  1079. $date_end = null;
  1080. } elseif (!$month_end || !$day_end || !$year_end || !checkdate($month_end,$day_end,$year_end)) {
  1081. $msg=get_lang('InvalidEndDate');
  1082. return $msg;
  1083. } elseif($date_start >= $date_end) {
  1084. $msg=get_lang('StartDateShouldBeBeforeEndDate');
  1085. return $msg;
  1086. }
  1087. if ( $date_end <> null ) {
  1088. $sql = "UPDATE $tbl_session_category SET name = '".Database::escape_string($name)."', date_start = '$date_start' ".
  1089. ", date_end = '$date_end' WHERE id= '".$id."' ";
  1090. } else {
  1091. $sql = "UPDATE $tbl_session_category SET name = '".Database::escape_string($name)."', date_start = '$date_start' ".
  1092. ", date_end = NULL WHERE id= '".$id."' ";
  1093. }
  1094. $result = Database::query($sql);
  1095. return ($result? true:false);
  1096. }
  1097. /**
  1098. * Delete sessions categories
  1099. * @author Jhon Hinojosa <jhon.hinojosa@dokeos.com>, from existing code
  1100. * @param array id_checked
  1101. * @param bool include delete session
  1102. * @param bool optional, true if the function is called by a webservice, false otherwise.
  1103. * @return void Nothing, or false on error
  1104. * The parameters is a array to delete sessions
  1105. **/
  1106. public static function delete_session_category($id_checked, $delete_session = false,$from_ws = false){
  1107. $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  1108. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  1109. if (is_array($id_checked)) {
  1110. $id_checked = Database::escape_string(implode(',',$id_checked));
  1111. } else {
  1112. $id_checked = intval($id_checked);
  1113. }
  1114. //Setting session_category_id to 0
  1115. $sql = "UPDATE $tbl_session SET session_category_id = 0 WHERE session_category_id IN (".$id_checked.")";
  1116. Database::query($sql);
  1117. $sql = "SELECT id FROM $tbl_session WHERE session_category_id IN (".$id_checked.")";
  1118. $result = @Database::query($sql);
  1119. while ($rows = Database::fetch_array($result)) {
  1120. $session_id = $rows['id'];
  1121. if ($delete_session) {
  1122. if ($from_ws) {
  1123. SessionManager::delete_session($session_id,true);
  1124. } else {
  1125. SessionManager::delete_session($session_id);
  1126. }
  1127. }
  1128. }
  1129. $sql = "DELETE FROM $tbl_session_category WHERE id IN (".$id_checked.")";
  1130. $rs = @Database::query($sql);
  1131. $result = Database::affected_rows();
  1132. // Add event to system log
  1133. $user_id = api_get_user_id();
  1134. event_system(LOG_SESSION_CATEGORY_DELETE, LOG_SESSION_CATEGORY_ID, $id_checked, api_get_utc_datetime(), $user_id);
  1135. // delete extra session fields where field variable is "PERIODO"
  1136. $t_sf = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
  1137. $t_sfv = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
  1138. $sql = "SELECT t_sfv.field_id FROM $t_sfv t_sfv, $t_sf t_sf WHERE t_sfv.session_id = '$id_checked' AND t_sf.field_variable = 'PERIODO' ";
  1139. $rs_field = Database::query($sql);
  1140. $field_id = 0;
  1141. if (Database::num_rows($rs_field) > 0) {
  1142. $row_field = Database::fetch_row($rs_field);
  1143. $field_id = $row_field[0];
  1144. $sql_delete_sfv = "DELETE FROM $t_sfv WHERE session_id = '$id_checked' AND field_id = '$field_id'";
  1145. $rs_delete_sfv = Database::query($sql_delete_sfv);
  1146. }
  1147. $sql = "SELECT * FROM $t_sfv WHERE field_id = '$field_id' ";
  1148. $rs_field_id = Database::query($sql);
  1149. if (Database::num_rows($rs_field_id) == 0) {
  1150. $sql_delete_sf = "DELETE FROM $t_sf WHERE id = '$field_id'";
  1151. $rs_delete_sf = Database::query($sql_delete_sf);
  1152. }
  1153. return true;
  1154. }
  1155. /**
  1156. * Get a list of sessions of which the given conditions match with an = 'cond'
  1157. * @param array $conditions a list of condition (exemple : array('status =' =>STUDENT) or array('s.name LIKE' => "%$needle%")
  1158. * @param array $order_by a list of fields on which sort
  1159. * @return array An array with all sessions of the platform.
  1160. * @todo optional course code parameter, optional sorting parameters...
  1161. */
  1162. public static function get_sessions_list($conditions = array(), $order_by = array()) {
  1163. $session_table = Database::get_main_table(TABLE_MAIN_SESSION);
  1164. $session_category_table = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  1165. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  1166. $table_access_url_rel_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  1167. $access_url_id = api_get_current_access_url_id();
  1168. $return_array = array();
  1169. $sql_query = " SELECT s.id, s.name, s.nbr_courses, s.access_start_date, s.access_end_date, u.firstname, u.lastname, sc.name as category_name, s.promotion_id
  1170. FROM $session_table s
  1171. INNER JOIN $user_table u ON s.id_coach = u.user_id
  1172. INNER JOIN $table_access_url_rel_session ar ON ar.session_id = s.id
  1173. LEFT JOIN $session_category_table sc ON s.session_category_id = sc.id
  1174. WHERE ar.access_url_id = $access_url_id ";
  1175. if (count($conditions) > 0) {
  1176. $sql_query .= ' AND ';
  1177. foreach ($conditions as $field=>$value) {
  1178. $field = Database::escape_string($field);
  1179. $value = Database::escape_string($value);
  1180. $sql_query .= $field." '".$value."'";
  1181. }
  1182. }
  1183. if (count($order_by)>0) {
  1184. $sql_query .= ' ORDER BY '.Database::escape_string(implode(',',$order_by));
  1185. }
  1186. //echo $sql_query;
  1187. $sql_result = Database::query($sql_query);
  1188. if (Database::num_rows($sql_result)>0) {
  1189. while ($result = Database::fetch_array($sql_result)) {
  1190. $return_array[$result['id']] = $result;
  1191. }
  1192. }
  1193. return $return_array;
  1194. }
  1195. /**
  1196. * Get the session category information by id
  1197. * @param string session category ID
  1198. * @return mixed false if the session category does not exist, array if the session category exists
  1199. */
  1200. public static function get_session_category ($id) {
  1201. $id = intval($id);
  1202. $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  1203. $sql = "SELECT id, name, date_start, date_end FROM $tbl_session_category WHERE id= $id";
  1204. $result = Database::query($sql);
  1205. $num = Database::num_rows($result);
  1206. if ($num>0){
  1207. return Database::fetch_array($result);
  1208. } else {
  1209. return false;
  1210. }
  1211. }
  1212. /**
  1213. * Get all session categories (filter by access_url_id)
  1214. * @return mixed false if the session category does not exist, array if the session category exists
  1215. */
  1216. public static function get_all_session_category() {
  1217. $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  1218. $id = api_get_current_access_url_id();
  1219. $sql = 'SELECT * FROM '.$tbl_session_category.' WHERE access_url_id ="'.$id.'" ORDER BY name ASC';
  1220. $result = Database::query($sql);
  1221. if (Database::num_rows($result) > 0 ){
  1222. $data = Database::store_result($result,'ASSOC');
  1223. return $data;
  1224. } else {
  1225. return false;
  1226. }
  1227. }
  1228. /**
  1229. * Assign a coach to course in session with status = 2
  1230. * @param int - user id
  1231. * @param int - session id
  1232. * @param string - course code
  1233. * @param bool - optional, if is true the user don't be a coach now, otherwise it'll assign a coach
  1234. * @return bool true if there are affected rows, otherwise false
  1235. */
  1236. public static function set_coach_to_course_session($user_id, $session_id = 0, $course_code = '',$nocoach = false) {
  1237. // Definition of variables
  1238. $user_id = intval($user_id);
  1239. if (!empty($session_id)) {
  1240. $session_id = intval($session_id);
  1241. } else {
  1242. $session_id = api_get_session_id();
  1243. }
  1244. if (!empty($course_code)) {
  1245. $course_code = Database::escape_string($course_code);
  1246. } else {
  1247. $course_code = api_get_course_id();
  1248. }
  1249. // definitios of tables
  1250. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1251. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1252. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1253. // check if user is a teacher
  1254. $sql= "SELECT * FROM $tbl_user WHERE status='1' AND user_id = '$user_id'";
  1255. $rs_check_user = Database::query($sql);
  1256. if (Database::num_rows($rs_check_user) > 0) {
  1257. if ($nocoach) {
  1258. // check if user_id exits int session_rel_user
  1259. $sql = "SELECT id_user FROM $tbl_session_rel_user WHERE id_session = '$session_id' AND id_user = '$user_id'";
  1260. $res = Database::query($sql);
  1261. if (Database::num_rows($res) > 0) {
  1262. // The user don't be a coach now
  1263. $sql = "UPDATE $tbl_session_rel_course_rel_user SET status = 0 WHERE id_session = '$session_id' AND course_code = '$course_code' AND id_user = '$user_id' ";
  1264. $rs_update = Database::query($sql);
  1265. if (Database::affected_rows() > 0) return true;
  1266. else return false;
  1267. } else {
  1268. // The user don't be a coach now
  1269. $sql = "DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session = '$session_id' AND course_code = '$course_code' AND id_user = '$user_id' ";
  1270. $rs_delete = Database::query($sql);
  1271. if (Database::affected_rows() > 0) return true;
  1272. else return false;
  1273. }
  1274. } else {
  1275. // Assign user like a coach to course
  1276. // First check if the user is registered in the course
  1277. $sql = "SELECT id_user FROM $tbl_session_rel_course_rel_user WHERE id_session = '$session_id' AND course_code = '$course_code' AND id_user = '$user_id'";
  1278. $rs_check = Database::query($sql);
  1279. //Then update or insert
  1280. if (Database::num_rows($rs_check) > 0) {
  1281. $sql = "UPDATE $tbl_session_rel_course_rel_user SET status = 2 WHERE id_session = '$session_id' AND course_code = '$course_code' AND id_user = '$user_id' ";
  1282. $rs_update = Database::query($sql);
  1283. if (Database::affected_rows() > 0) return true;
  1284. else return false;
  1285. } else {
  1286. $sql = " INSERT INTO $tbl_session_rel_course_rel_user(id_session, course_code, id_user, status) VALUES('$session_id', '$course_code', '$user_id', 2)";
  1287. $rs_insert = Database::query($sql);
  1288. if (Database::affected_rows() > 0) return true;
  1289. else return false;
  1290. }
  1291. }
  1292. } else {
  1293. return false;
  1294. }
  1295. }
  1296. /**
  1297. * Subscribes sessions to human resource manager (Dashboard feature)
  1298. * @param int Human Resource Manager id
  1299. * @param array Sessions id
  1300. * @param int Relation type
  1301. **/
  1302. public static function suscribe_sessions_to_hr_manager($hr_manager_id,$sessions_list) {
  1303. global $_configuration;
  1304. // Database Table Definitions
  1305. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  1306. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1307. $tbl_session_rel_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1308. $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  1309. $hr_manager_id = intval($hr_manager_id);
  1310. $affected_rows = 0;
  1311. //Deleting assigned sessions to hrm_id
  1312. if ($_configuration['multiple_access_urls']) {
  1313. $sql = "SELECT id_session FROM $tbl_session_rel_user s INNER JOIN $tbl_session_rel_access_url a ON (a.session_id = s.id_session) WHERE id_user = $hr_manager_id AND relation_type=".SESSION_RELATION_TYPE_RRHH." AND access_url_id = ".api_get_current_access_url_id()."";
  1314. } else {
  1315. $sql = "SELECT id_session FROM $tbl_session_rel_user s WHERE id_user = $hr_manager_id AND relation_type=".SESSION_RELATION_TYPE_RRHH."";
  1316. }
  1317. $result = Database::query($sql);
  1318. if (Database::num_rows($result) > 0) {
  1319. while ($row = Database::fetch_array($result)) {
  1320. $sql = "DELETE FROM $tbl_session_rel_user WHERE id_session = {$row['id_session']} AND id_user = $hr_manager_id AND relation_type=".SESSION_RELATION_TYPE_RRHH." ";
  1321. Database::query($sql);
  1322. }
  1323. }
  1324. /*
  1325. //Deleting assigned courses in sessions to hrm_id
  1326. $sql = "SELECT * FROM $tbl_session_rel_course_user WHERE id_user = $hr_manager_id ";
  1327. $result = Database::query($sql);
  1328. if (Database::num_rows($result) > 0) {
  1329. $sql = "DELETE FROM $tbl_session_rel_course_user WHERE id_user = $hr_manager_id ";
  1330. Database::query($sql);
  1331. }
  1332. */
  1333. // inserting new sessions list
  1334. if (is_array($sessions_list)) {
  1335. foreach ($sessions_list as $session_id) {
  1336. $session_id = intval($session_id);
  1337. $insert_sql = "INSERT IGNORE INTO $tbl_session_rel_user(id_session, id_user, relation_type) VALUES($session_id, $hr_manager_id, '".SESSION_RELATION_TYPE_RRHH."')";
  1338. Database::query($insert_sql);
  1339. $affected_rows = Database::affected_rows();
  1340. }
  1341. }
  1342. return $affected_rows;
  1343. }
  1344. /**
  1345. * Get sessions followed by human resources manager
  1346. * @param int Human resources manager or Session admin id
  1347. * @return array sessions
  1348. */
  1349. public static function get_sessions_followed_by_drh($hr_manager_id) {
  1350. global $_configuration;
  1351. // Database Table Definitions
  1352. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  1353. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1354. $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  1355. $hr_manager_id = intval($hr_manager_id);
  1356. $assigned_sessions_to_hrm = array();
  1357. if ($_configuration['multiple_access_urls']) {
  1358. $sql = "SELECT * FROM $tbl_session s INNER JOIN $tbl_session_rel_user sru ON (sru.id_session = s.id) LEFT JOIN $tbl_session_rel_access_url a ON (s.id = a.session_id)
  1359. WHERE sru.id_user = '$hr_manager_id' AND sru.relation_type = '".SESSION_RELATION_TYPE_RRHH."' AND access_url_id = ".api_get_current_access_url_id()."";
  1360. } else {
  1361. $sql = "SELECT * FROM $tbl_session s
  1362. INNER JOIN $tbl_session_rel_user sru ON sru.id_session = s.id AND sru.id_user = '$hr_manager_id' AND sru.relation_type = '".SESSION_RELATION_TYPE_RRHH."' ";
  1363. }
  1364. $rs_assigned_sessions = Database::query($sql);
  1365. if (Database::num_rows($rs_assigned_sessions) > 0) {
  1366. while ($row_assigned_sessions = Database::fetch_array($rs_assigned_sessions)) {
  1367. $assigned_sessions_to_hrm[$row_assigned_sessions['id']] = $row_assigned_sessions;
  1368. }
  1369. }
  1370. return $assigned_sessions_to_hrm;
  1371. }
  1372. /**
  1373. * Gets the list of courses by session filtered by access_url
  1374. * @param int session id
  1375. * @return array list of courses
  1376. */
  1377. public static function get_course_list_by_session_id ($session_id) {
  1378. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1379. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1380. // select the courses
  1381. $sql = "SELECT * FROM $tbl_course c INNER JOIN $tbl_session_rel_course src ON c.code = src.course_code
  1382. WHERE src.id_session = '$session_id' ORDER BY title;";
  1383. $result = Database::query($sql);
  1384. $num_rows = Database::num_rows($result);
  1385. $courses = array();
  1386. if ($num_rows > 0) {
  1387. while ($row = Database::fetch_array($result,'ASSOC')) {
  1388. $courses[$row['id']] = $row;
  1389. }
  1390. }
  1391. return $courses;
  1392. }
  1393. /**
  1394. * Get the session id based on the original id and field name in the extra fields. Returns 0 if session was not found
  1395. *
  1396. * @param string Original session id
  1397. * @param string Original field name
  1398. * @return int Session id
  1399. */
  1400. public static function get_session_id_from_original_id($original_session_id_value, $original_session_id_name) {
  1401. $t_sfv = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
  1402. $table_field = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
  1403. $sql_session = "SELECT session_id FROM $table_field sf INNER JOIN $t_sfv sfv ON sfv.field_id=sf.id WHERE field_variable='$original_session_id_name' AND field_value='$original_session_id_value'";
  1404. $res_session = Database::query($sql_session);
  1405. $row = Database::fetch_object($res_session);
  1406. if ($row) {
  1407. return $row->session_id;
  1408. } else {
  1409. return 0;
  1410. }
  1411. }
  1412. /**
  1413. * Get users by session
  1414. * @param int session id
  1415. * @param int filter by status
  1416. * @return array a list with an user list
  1417. */
  1418. public static function get_users_by_session($id, $with_status = null) {
  1419. if (empty($id)) {
  1420. return array();
  1421. }
  1422. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1423. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1424. $id = intval($id);
  1425. if (api_is_multiple_url_enabled()) {
  1426. $url_id = api_get_current_access_url_id();
  1427. $sql = "SELECT u.user_id, lastname, firstname, username, access_url_id, moved_to, moved_status, moved_at
  1428. FROM $tbl_user u
  1429. INNER JOIN $tbl_session_rel_user su
  1430. ON u.user_id = su.id_user AND su.relation_type<>".SESSION_RELATION_TYPE_RRHH."
  1431. LEFT OUTER JOIN $tbl_session_rel_user uu ON (uu.user_id = u.user_id)
  1432. WHERE su.id_session = $id AND (access_url_id = $url_id OR access_url_id is null )";
  1433. } else {
  1434. $sql = "SELECT u.user_id, lastname, firstname, username, moved_to, moved_status, moved_at
  1435. FROM $tbl_user u INNER JOIN $tbl_session_rel_user su
  1436. ON u.user_id = su.id_user AND
  1437. su.id_session = $id
  1438. ";
  1439. }
  1440. if (isset($with_status) && $with_status != '') {
  1441. $with_status = intval($with_status);
  1442. $sql .= " WHERE relation_type = $with_status ";
  1443. }
  1444. $order_clause = api_sort_by_first_name() ? ' ORDER BY firstname, lastname' : ' ORDER BY lastname, firstname';
  1445. $sql .= $order_clause;
  1446. $result = Database::query($sql);
  1447. while ($row = Database::fetch_array($result,'ASSOC')) {
  1448. $return_array[] = $row;
  1449. }
  1450. return $return_array;
  1451. }
  1452. /**
  1453. * The general coach (field: session.id_coach)
  1454. * @param int user id
  1455. */
  1456. public static function get_sessions_by_general_coach($user_id) {
  1457. $session_table = Database::get_main_table(TABLE_MAIN_SESSION);
  1458. $user_id = intval($user_id);
  1459. // session where we are general coach
  1460. $sql = "SELECT DISTINCT *
  1461. FROM $session_table
  1462. WHERE id_coach = $user_id";
  1463. if (api_is_multiple_url_enabled()) {
  1464. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  1465. $access_url_id = api_get_current_access_url_id();
  1466. if ($access_url_id != -1) {
  1467. $sql = 'SELECT DISTINCT session.*
  1468. FROM '.$session_table.' session INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
  1469. ON (session.id = session_rel_url.session_id)
  1470. WHERE id_coach = '.$user_id.' AND access_url_id = '.$access_url_id;
  1471. }
  1472. }
  1473. $sql .= ' ORDER by name';
  1474. $result = Database::query($sql);
  1475. return Database::store_result($result, 'ASSOC');
  1476. }
  1477. /**
  1478. * Get all sessions if the user is a sesion course coach of any session
  1479. * @param int user id
  1480. */
  1481. static function get_sessions_by_coach($user_id) {
  1482. // table definition
  1483. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  1484. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1485. $sql = 'SELECT DISTINCT session.*
  1486. FROM ' . $tbl_session . ' as session
  1487. INNER JOIN ' . $tbl_session_course_user . ' as session_course_user
  1488. ON session.id = session_course_user.id_session
  1489. AND session_course_user.id_user=' . $user_id.' AND session_course_user.status=2';
  1490. if (api_is_multiple_url_enabled()) {
  1491. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  1492. $access_url_id = api_get_current_access_url_id();
  1493. if ($access_url_id != -1){
  1494. $sql = 'SELECT DISTINCT session.*
  1495. FROM '.$tbl_session.' as session
  1496. INNER JOIN ' . $tbl_session_course_user . ' as session_course_user
  1497. ON session.id = session_course_user.id_session AND session_course_user.id_user = '.$user_id.' AND session_course_user.status=2
  1498. INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
  1499. ON (session.id = session_rel_url.session_id)
  1500. WHERE access_url_id = '.$access_url_id;
  1501. }
  1502. }
  1503. //$sql .= " ORDER BY session.name";
  1504. $result = Database::query($sql);
  1505. return Database::store_result($result, 'ASSOC');
  1506. }
  1507. public static function get_sessions_coached_by_user($coach_id) {
  1508. $sessions = self::get_sessions_by_general_coach($coach_id);
  1509. $sessions_by_coach = self::get_sessions_by_coach($coach_id);
  1510. if (!empty($sessions_by_coach)) {
  1511. $sessions = array_merge($sessions, $sessions_by_coach);
  1512. }
  1513. return $sessions;
  1514. }
  1515. /*
  1516. public static function get_coaches_by_session($session_id, $course_code) {
  1517. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1518. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1519. $sql = "SELECT user.user_id
  1520. FROM $tbl_session_rel_course_rel_user session_rcru, $tbl_user user
  1521. WHERE session_rcru.id_user = user.user_id AND
  1522. session_rcru.id_session = '".intval($session_id)."' AND
  1523. session_rcru.course_code ='".Database::escape_string($course_code)."' AND
  1524. session_rcru.status = 2 ";
  1525. $result = Database::query($sql);
  1526. while ($row = Database::fetch_array($result,'ASSOC')) {
  1527. $return_array[] = $row;
  1528. }
  1529. return $return_array;
  1530. }*/
  1531. /**
  1532. * Gets user status within a session
  1533. * @param $user_id
  1534. * @param $course_code
  1535. * @param $session_id
  1536. * @return unknown_type
  1537. */
  1538. public static function get_user_status_in_course_session($user_id, $course_code, $session_id) {
  1539. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1540. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1541. $sql = "SELECT session_rcru.status
  1542. FROM $tbl_session_rel_course_rel_user session_rcru, $tbl_user user
  1543. WHERE session_rcru.id_user = user.user_id AND
  1544. session_rcru.id_session = '".intval($session_id)."' AND
  1545. session_rcru.course_code ='".Database::escape_string($course_code)."' AND
  1546. user.user_id = ".intval($user_id);
  1547. $result = Database::query($sql);
  1548. $status = false;
  1549. if (Database::num_rows($result)) {
  1550. $status = Database::fetch_row($result);
  1551. $status = $status['0'];
  1552. }
  1553. return $status;
  1554. }
  1555. static function get_user_status_in_session($session_id, $user_id) {
  1556. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1557. $sql = "SELECT * FROM $tbl_session_rel_user WHERE id_user = $user_id AND id_session = $session_id";
  1558. $result = Database::query($sql);
  1559. if (Database::num_rows($result)) {
  1560. $result = Database::store_result($result, 'ASSOC');
  1561. return $result[0];
  1562. }
  1563. return array();
  1564. }
  1565. function get_all_sessions_by_promotion($id) {
  1566. $t = Database::get_main_table(TABLE_MAIN_SESSION);
  1567. return Database::select('*', $t, array('where'=>array('promotion_id = ?'=>$id)));
  1568. }
  1569. function suscribe_sessions_to_promotion($promotion_id, $list) {
  1570. $t = Database::get_main_table(TABLE_MAIN_SESSION);
  1571. $params = array();
  1572. $params['promotion_id'] = 0;
  1573. Database::update($t, $params, array('promotion_id = ?'=>$promotion_id));
  1574. $params['promotion_id'] = $promotion_id;
  1575. if (!empty($list)) {
  1576. foreach ($list as $session_id) {
  1577. $session_id= intval($session_id);
  1578. Database::update($t, $params, array('id = ?'=>$session_id));
  1579. }
  1580. }
  1581. }
  1582. /**
  1583. * Updates a session status
  1584. * @param int session id
  1585. * @param int status
  1586. */
  1587. public function set_session_status($session_id, $status) {
  1588. $t = Database::get_main_table(TABLE_MAIN_SESSION);
  1589. $params['visibility'] = $status;
  1590. Database::update($t, $params, array('id = ?'=>$session_id));
  1591. }
  1592. /**
  1593. * Copies a session with the same data to a new session.
  1594. * The new copy is not assigned to the same promotion. @see suscribe_sessions_to_promotions() for that
  1595. * @param int Session ID
  1596. * @param bool Whether to copy the relationship with courses
  1597. * @param bool Whether to copy the relationship with users
  1598. * @param bool New courses will be created
  1599. * @return int The new session ID on success, 0 otherwise
  1600. * @todo make sure the extra session fields are copied too
  1601. */
  1602. /**
  1603. * @param $id
  1604. * @param $copy_courses
  1605. * @param $copy_users
  1606. * @param $create_new_courses
  1607. * @param $set_exercises_lp_invisible
  1608. * @return unknown_type
  1609. */
  1610. public function copy_session($id, $copy_courses = true, $copy_users = true, $create_new_courses = false, $set_exercises_lp_invisible = false) {
  1611. $id = intval($id);
  1612. $params = self::fetch($id);
  1613. $params['name'] = $params['name'].' '.get_lang('CopyLabelSuffix');
  1614. $sid = self::add($params);
  1615. if (!is_numeric($sid) || empty($sid)) {
  1616. return false;
  1617. }
  1618. if ($copy_courses) {
  1619. // Register courses from the original session to the new session
  1620. $courses = self::get_course_list_by_session_id($id);
  1621. $short_courses = $new_short_courses = array();
  1622. if (is_array($courses) && count($courses)>0) {
  1623. foreach ($courses as $course) {
  1624. $short_courses[] = $course;
  1625. }
  1626. }
  1627. $courses = null;
  1628. //We will copy the current courses of the session to new courses
  1629. if (!empty($short_courses)) {
  1630. if ($create_new_courses) {
  1631. //Just in case
  1632. if (function_exists('ini_set')) {
  1633. ini_set('memory_limit','256M');
  1634. ini_set('max_execution_time',0);
  1635. }
  1636. $params = array();
  1637. $params['skip_lp_dates'] = true;
  1638. foreach ($short_courses as $course_data) {
  1639. $course_info = CourseManager::copy_course_simple($course_data['title'].' '.get_lang('CopyLabelSuffix'), $course_data['course_code'], $id, $sid, $params);
  1640. if ($course_info) {
  1641. //By default new elements are invisible
  1642. if ($set_exercises_lp_invisible) {
  1643. require_once api_get_path(SYS_CODE_PATH).'newscorm/learnpathList.class.php';
  1644. $list = new LearnpathList('', $course_info['code'], $sid);
  1645. $flat_list = $list->get_flat_list();
  1646. if (!empty($flat_list)) {
  1647. foreach($flat_list as $lp_id => $data) {
  1648. api_item_property_update($course_info, TOOL_LEARNPATH, $lp_id, 'invisible', api_get_user_id(), 0 ,0, 0, 0, $sid);
  1649. api_item_property_update($course_info, TOOL_LEARNPATH, $lp_id, 'invisible', api_get_user_id(), 0 ,0, 0, 0);
  1650. }
  1651. }
  1652. $quiz_table = Database::get_course_table(TABLE_QUIZ_TEST);
  1653. $course_id = $course_info['real_id'];
  1654. //@todo check this query
  1655. //Disabling quiz items
  1656. $sql = "UPDATE $quiz_table SET active = 0 WHERE c_id = $course_id ";
  1657. Database::query($sql);
  1658. }
  1659. $new_short_courses[] = $course_info['code'];
  1660. }
  1661. }
  1662. } else {
  1663. foreach($short_courses as $course_data) {
  1664. $new_short_courses[] = $course_data['code'];
  1665. }
  1666. }
  1667. $short_courses = $new_short_courses;
  1668. $res = self::add_courses_to_session($sid, $short_courses, true);
  1669. $short_courses = null;
  1670. }
  1671. }
  1672. if ($copy_users) {
  1673. // Register users from the original session to the new session
  1674. $users = self::get_users_by_session($id);
  1675. $short_users = array();
  1676. if (is_array($users) && count($users)>0) {
  1677. foreach ($users as $user) {
  1678. $short_users[] = $user['user_id'];
  1679. }
  1680. }
  1681. $users = null;
  1682. //Subscribing in read only mode
  1683. $res = self::suscribe_users_to_session($sid, $short_users, SESSION_VISIBLE_READ_ONLY, true, false);
  1684. $short_users = null;
  1685. }
  1686. return $sid;
  1687. }
  1688. static function user_is_general_coach($user_id, $session_id) {
  1689. $session_id = intval($session_id);
  1690. $user_id = intval($user_id);
  1691. $session_table = Database::get_main_table(TABLE_MAIN_SESSION);
  1692. $sql = "SELECT DISTINCT id
  1693. FROM $session_table
  1694. WHERE session.id_coach = '".$user_id."' AND id = '$session_id'";
  1695. $result = Database::query($sql);
  1696. if ($result && Database::num_rows($result)) {
  1697. return true;
  1698. }
  1699. return false;
  1700. }
  1701. /**
  1702. * Get the number of sessions
  1703. * @param int ID of the URL we want to filter on (optional)
  1704. * @return int Number of sessions
  1705. */
  1706. public function count_sessions($access_url_id=null) {
  1707. $session_table = Database::get_main_table(TABLE_MAIN_SESSION);
  1708. $access_url_rel_session_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  1709. $sql = "SELECT count(id) FROM $session_table s";
  1710. if (!empty($access_url_id) && $access_url_id == intval($access_url_id)) {
  1711. $sql .= ", $access_url_rel_session_table u ".
  1712. " WHERE s.id = u.session_id AND u.access_url_id = $access_url_id";
  1713. }
  1714. $res = Database::query($sql);
  1715. $row = Database::fetch_row($res);
  1716. return $row[0];
  1717. }
  1718. static function protect_session_edit($id) {
  1719. api_protect_admin_script(true);
  1720. $session_info = self::fetch($id);
  1721. if (empty($session_info)) {
  1722. api_not_allowed(true);
  1723. }
  1724. if (!api_is_platform_admin() && api_get_setting('allow_session_admins_to_manage_all_sessions') != 'true') {
  1725. if ($session_info['session_admin_id'] != api_get_user_id()) {
  1726. api_not_allowed(true);
  1727. }
  1728. }
  1729. }
  1730. static function get_session_by_course($course_code) {
  1731. $table_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1732. $table_session = Database::get_main_table(TABLE_MAIN_SESSION);
  1733. $course_code = Database::escape_string($course_code);
  1734. $sql = "SELECT name, s.id FROM $table_session_course sc INNER JOIN $table_session s ON (sc.id_session = s.id)
  1735. WHERE sc.course_code = '$course_code' ";
  1736. $result = Database::query($sql);
  1737. return Database::store_result($result);
  1738. }
  1739. /**
  1740. * @todo Add constatns in a DB table
  1741. */
  1742. static function get_session_change_user_reasons() {
  1743. return array (
  1744. self::SESSION_CHANGE_USER_REASON_SCHEDULE => get_lang('ScheduleChanged'),
  1745. self::SESSION_CHANGE_USER_REASON_CLASSROOM => get_lang('ClassRoomChanged'),
  1746. self::SESSION_CHANGE_USER_REASON_LOCATION => get_lang('LocationChanged'),
  1747. //self::SESSION_CHANGE_USER_REASON_ENROLLMENT_ANNULATION => get_lang('EnrollmentAnnulation'),
  1748. );
  1749. }
  1750. static function get_session_change_user_reasons_variations() {
  1751. return array (
  1752. self::SESSION_CHANGE_USER_REASON_SCHEDULE => array(
  1753. 'default' => get_lang('ScheduleChanged'),
  1754. 'from' => get_lang('ScheduleChangedFrom'),
  1755. 'to' => get_lang('ScheduleChangedTo'),
  1756. ),
  1757. self::SESSION_CHANGE_USER_REASON_CLASSROOM => array(
  1758. 'default' => get_lang('ClassRoomChanged'),
  1759. 'from' => get_lang('ClassRoomChangedFrom'),
  1760. 'to' => get_lang('ClassRoomChangedTo'),
  1761. ),
  1762. self::SESSION_CHANGE_USER_REASON_LOCATION => array(
  1763. 'default' => get_lang('LocationChanged'),
  1764. 'from' => get_lang('LocationChangedFrom'),
  1765. 'to' => get_lang('LocationChangedTo'),
  1766. )
  1767. );
  1768. }
  1769. static function get_session_change_user_reasons_variations_by_id($id, $type) {
  1770. $reasons = self::get_session_change_user_reasons_variations();
  1771. $my_reason = isset($reasons[$id]) ? $reasons[$id] : null;
  1772. return isset($my_reason[$type]) ? $my_reason[$type] : null;
  1773. }
  1774. static function get_session_changed_reason_label($id, $type) {
  1775. switch ($type) {
  1776. case 'origin':
  1777. break;
  1778. case 'destination':
  1779. break;
  1780. }
  1781. }
  1782. /**
  1783. * Gets the reason name
  1784. * @param int reason id
  1785. */
  1786. static function get_session_change_user_reason($id) {
  1787. $reasons = self::get_session_change_user_reasons();
  1788. return isset($reasons[$id]) ? $reasons[$id] : null;
  1789. }
  1790. /**
  1791. * Changes the user from one session to another due a reason
  1792. */
  1793. static function change_user_session($user_id, $old_session_id, $new_session_id, $reason_id) {
  1794. if (!empty($user_id) && !empty($old_session_id) && !empty($new_session_id)) {
  1795. $user_id = intval($user_id);
  1796. $old_session_id = intval($old_session_id);
  1797. $new_session_id = intval($new_session_id);
  1798. $reason_id = intval($reason_id);
  1799. //self::unsubscribe_user_from_session($old_session_id, $user_id, $reason_id);
  1800. //$tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1801. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1802. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1803. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  1804. // Update number of users
  1805. $update_sql = "UPDATE $tbl_session SET nbr_users = nbr_users - 1 WHERE id = '$old_session_id' ";
  1806. Database::query($update_sql);
  1807. // Get the list of courses related to this session
  1808. $course_list = SessionManager::get_course_list_by_session_id($old_session_id);
  1809. if (!empty($course_list)) {
  1810. foreach ($course_list as $course) {
  1811. $course_code = $course['code'];
  1812. // Delete user from course
  1813. //Database::query("DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session='$session_id' AND course_code='$course_code' AND id_user='$user_id'");
  1814. //if (Database::affected_rows()) {
  1815. // Update number of users in this relation
  1816. $sql = "UPDATE $tbl_session_rel_course SET nbr_users = nbr_users - 1 WHERE id_session = '$old_session_id' AND course_code='$course_code'";
  1817. Database::query($sql);
  1818. //}
  1819. }
  1820. }
  1821. //Deal with reasons
  1822. switch ($reason_id) {
  1823. case self::SESSION_CHANGE_USER_REASON_SCHEDULE:
  1824. case self::SESSION_CHANGE_USER_REASON_CLASSROOM:
  1825. case self::SESSION_CHANGE_USER_REASON_LOCATION:
  1826. //Adding to the new session
  1827. self::suscribe_users_to_session($new_session_id, array($user_id), null, false);
  1828. //Setting move_to if session was provided
  1829. $sql = "UPDATE $tbl_session_rel_user SET moved_to = '$new_session_id'
  1830. WHERE id_session = '$old_session_id' AND id_user ='$user_id'";
  1831. Database::query($sql);
  1832. break;
  1833. case self::SESSION_CHANGE_USER_REASON_ENROLLMENT_ANNULATION:
  1834. UserManager::deactivate_users(array($user_id));
  1835. break;
  1836. }
  1837. $now = api_get_utc_datetime();
  1838. //Setting the moved_status
  1839. $sql = "UPDATE $tbl_session_rel_user SET moved_status = $reason_id, moved_at = '$now'
  1840. WHERE id_session = '$old_session_id' AND id_user ='$user_id'";
  1841. Database::query($sql);
  1842. return true;
  1843. }
  1844. return;
  1845. }
  1846. /**
  1847. * Get users inside a course session
  1848. */
  1849. static function get_users_in_course_session($course_code, $id_session, $sort, $direction, $from = null, $limit = null) {
  1850. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1851. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1852. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1853. $course_code = Database::escape_string($course_code);
  1854. $id_session = Database::escape_string($id_session);
  1855. $from = intval($from);
  1856. $limit = intval($limit);
  1857. $is_western_name_order = api_is_western_name_order();
  1858. //Select the number of users
  1859. $sql = " SELECT DISTINCT u.user_id,".($is_western_name_order ? 'u.firstname, u.lastname' : 'u.lastname, u.firstname').", u.username, scru.id_user as is_subscribed
  1860. FROM $tbl_session_rel_user sru INNER JOIN $tbl_user u ON (u.user_id=sru.id_user)
  1861. LEFT JOIN $tbl_session_rel_course_rel_user scru ON (u.user_id = scru.id_user AND scru.course_code = '".$course_code."' )
  1862. WHERE sru.id_session = '$id_session' AND
  1863. sru.moved_to = 0 AND sru.moved_status <> ".SessionManager::SESSION_CHANGE_USER_REASON_ENROLLMENT_ANNULATION." AND
  1864. sru.relation_type<>".SESSION_RELATION_TYPE_RRHH;
  1865. $sql .= " ORDER BY $sort $direction ";
  1866. if (!empty($from) && !empty($limit)) {
  1867. $sql .= " LIMIT $from, $limit";
  1868. }
  1869. $result = Database::query($sql);
  1870. if (Database::num_rows($result)) {
  1871. return Database::store_result($result);
  1872. }
  1873. return false;
  1874. }
  1875. static function get_count_users_in_course_session($course_code, $id_session) {
  1876. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1877. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1878. //Select the number of users
  1879. $sql = " SELECT count(*) FROM $tbl_session_rel_user sru, $tbl_session_rel_course_rel_user srcru
  1880. WHERE srcru.id_user = sru.id_user AND
  1881. srcru.id_session = sru.id_session AND
  1882. srcru.course_code = '".Database::escape_string($course_code)."' AND
  1883. srcru.id_session = '".intval($id_session)."' AND
  1884. (sru.moved_to = 0 AND sru.moved_status <> ".SessionManager::SESSION_CHANGE_USER_REASON_ENROLLMENT_ANNULATION.") AND
  1885. sru.relation_type<>".SESSION_RELATION_TYPE_RRHH;
  1886. $result = Database::query($sql);
  1887. if (Database::num_rows($result)) {
  1888. return Database::result($result,0,0);
  1889. }
  1890. return 0;
  1891. }
  1892. /**
  1893. * Get the list of coaches (only user ids)
  1894. * @param string course_code
  1895. * @param in session_id
  1896. * @return array
  1897. */
  1898. static function get_session_course_coaches($course_code, $session_id) {
  1899. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1900. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1901. // Get coachs of the courses in session
  1902. $sql = "SELECT user.user_id FROM $tbl_session_rel_course_rel_user session_rcru, $tbl_user user
  1903. WHERE session_rcru.id_user = user.user_id AND
  1904. session_rcru.id_session = '".intval($session_id)."' AND
  1905. session_rcru.course_code ='".Database::escape_string($course_code)."' AND
  1906. session_rcru.status=2";
  1907. $result = Database::query($sql);
  1908. return Database::store_result($result);
  1909. }
  1910. static function get_session_course_coaches_by_user($course_code, $session_id, $user_id) {
  1911. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1912. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1913. // Get coachs of the courses in session
  1914. $sql = "SELECT user.user_id FROM $tbl_session_rel_course_rel_user session_rcru, $tbl_user user
  1915. WHERE session_rcru.id_user = user.user_id AND
  1916. session_rcru.id_session = '".intval($session_id)."' AND
  1917. session_rcru.course_code ='".Database::escape_string($course_code)."' AND
  1918. session_rcru.status=2";
  1919. $result = Database::query($sql);
  1920. return Database::store_result($result);
  1921. }
  1922. static function get_session_course_coaches_to_string($course_code, $session_id) {
  1923. $coaches = self::get_session_course_coaches($course_code, $session_id);
  1924. if (!empty($coaches)) {
  1925. $coach_list = array();
  1926. foreach ($coaches as $coach_info) {
  1927. $user_info = api_get_user_info($coach_info['user_id']);
  1928. $coach_list[] = $user_info['complete_name'];
  1929. }
  1930. if (!empty($coach_list)) {
  1931. return implode(', ', $coach_list);
  1932. }
  1933. }
  1934. return get_lang('None');
  1935. }
  1936. static function delete_course_in_session($id_session, $course_code) {
  1937. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1938. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1939. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  1940. $id_session = intval($id_session);
  1941. $course_code = Database::escape_string($course_code);
  1942. if (!empty($id_session) && !empty($course_code)) {
  1943. Database::query("DELETE FROM $tbl_session_rel_course WHERE id_session='$id_session' AND course_code = '$course_code'");
  1944. $nbr_affected_rows=Database::affected_rows();
  1945. Database::query("DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session='$id_session' AND course_code = '$course_code'");
  1946. Database::query("UPDATE $tbl_session SET nbr_courses=nbr_courses-$nbr_affected_rows WHERE id='$id_session'");
  1947. }
  1948. }
  1949. static function get_sessions_by_user($user_id) {
  1950. $session_categories = UserManager::get_sessions_by_category($user_id);
  1951. $session_array = array();
  1952. if (!empty($session_categories)) {
  1953. foreach ($session_categories as $category) {
  1954. if (isset($category['sessions'])) {
  1955. foreach ($category['sessions'] as $session) {
  1956. $session_array[] = $session;
  1957. }
  1958. }
  1959. }
  1960. }
  1961. return $session_array;
  1962. }
  1963. static function get_session_rel_user_by_moved_to($session_id, $user_id) {
  1964. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1965. $sql = "SELECT id_session, moved_status, moved_at FROM $tbl_session_rel_user WHERE id_user = $user_id AND moved_to = $session_id LIMIT 1";
  1966. $result = Database::query($sql);
  1967. $result = Database::store_result($result,'ASSOC');
  1968. return $result[0];
  1969. }
  1970. static function get_coaches_by_keyword($tag) {
  1971. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1972. $order_clause = api_sort_by_first_name() ? ' ORDER BY firstname, lastname, username' : ' ORDER BY lastname, firstname, username';
  1973. $select ="SELECT user.user_id, lastname, firstname, username ";
  1974. $sql = " $select FROM $tbl_user user WHERE status='1'";
  1975. $tag = Database::escape_string($tag);
  1976. $where_condition = array();
  1977. if (!empty($tag)) {
  1978. $condition = ' LIKE "%'.$tag.'%"';
  1979. $where_condition = array( "firstname $condition",
  1980. "lastname $condition",
  1981. "username $condition"
  1982. );
  1983. $where_condition = ' AND ('.implode(' OR ', $where_condition).') ';
  1984. }
  1985. if (api_is_multiple_url_enabled()) {
  1986. $tbl_user_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  1987. $access_url_id = api_get_current_access_url_id();
  1988. if ($access_url_id != -1){
  1989. $sql = $select.' FROM '.$tbl_user.' user
  1990. INNER JOIN '.$tbl_user_rel_access_url.' url_user ON (url_user.user_id=user.user_id)
  1991. WHERE access_url_id = '.$access_url_id.' AND status = 1';
  1992. }
  1993. }
  1994. $sql .= $where_condition.$order_clause;
  1995. $result = Database::query($sql);
  1996. return Database::store_result($result, 'ASSOC');
  1997. }
  1998. /**
  1999. * Returns a human readable string
  2000. * @params array An array with all the session dates
  2001. * @return string
  2002. */
  2003. static function parse_session_dates($session_info) {
  2004. //This will clean the variables if 0000-00-00 00:00:00 the variable will be empty
  2005. $start_date = api_get_local_time($session_info['display_start_date'], null, null, true);
  2006. $end_date = api_get_local_time($session_info['display_end_date'], null, null, true);
  2007. if (!empty($start_date) && !empty($end_date)) {
  2008. //$msg_date = get_lang('From').' '.$start_date.' '.get_lang('To').' '.$end_date;
  2009. $msg_date = sprintf(get_lang('FromDateXToDateY'), $start_date, $end_date);
  2010. } else {
  2011. if (!empty($start_date)) {
  2012. $msg_date = get_lang('From').' '.$start_date;
  2013. }
  2014. if (!empty($end_date)) {
  2015. $msg_date = get_lang('Until').' '.$end_date;
  2016. }
  2017. }
  2018. return $msg_date;
  2019. }
  2020. static public function get_session_columns() {
  2021. $columns = array(
  2022. get_lang('Name'),
  2023. get_lang('SessionDisplayStartDate'),
  2024. get_lang('SessionDisplayEndDate'),
  2025. get_lang('SessionCategoryName'),
  2026. get_lang('Coach'),
  2027. get_lang('Status'),
  2028. get_lang('Visibility'),
  2029. get_lang('CourseTitle'),
  2030. );
  2031. //$activeurl = '?sidx=session_active';
  2032. //Column config
  2033. $operators = array('cn', 'nc');
  2034. $date_operators = array('gt', 'ge', 'lt', 'le');
  2035. $column_model = array (
  2036. array('name'=>'name', 'index'=>'name', 'width'=>'120', 'align'=>'left', 'search' => 'true', 'searchoptions' => array('sopt' => $operators)),
  2037. array('name'=>'display_start_date', 'index'=>'display_start_date', 'width'=>'70', 'align'=>'left', 'search' => 'true', 'searchoptions' => array('dataInit' => 'date_pick_today', 'sopt' => $date_operators)),
  2038. array('name'=>'display_end_date', 'index'=>'display_end_date', 'width'=>'70', 'align'=>'left', 'search' => 'true', 'searchoptions' => array('dataInit' => 'date_pick_one_month', 'sopt' => $date_operators)),
  2039. array('name'=>'category_name', 'index'=>'category_name', 'hidden' => 'true', 'width'=>'70', 'align'=>'left', 'search' => 'true', 'searchoptions' => array('searchhidden' =>'true', 'sopt' => $operators)),
  2040. //array('name'=>'access_start_date', 'index'=>'access_start_date', 'width'=>'60', 'align'=>'left', 'search' => 'true', 'searchoptions' => array('searchhidden' =>'true')),
  2041. //array('name'=>'access_end_date', 'index'=>'access_end_date', 'width'=>'60', 'align'=>'left', 'search' => 'true', 'searchoptions' => array('searchhidden' =>'true')),
  2042. array('name'=>'coach_name', 'index'=>'coach_name', 'width'=>'70', 'align'=>'left', 'search' => 'false', 'searchoptions' => array('sopt' => $operators)),
  2043. array('name'=>'session_active', 'index'=>'session_active','width'=>'40', 'align'=>'left', 'search' => 'true', 'stype'=>'select',
  2044. //for the bottom bar
  2045. 'searchoptions' => array(
  2046. 'defaultValue' => '1',
  2047. 'value' => '1:'.get_lang('Active').';0:'.get_lang('Inactive')),
  2048. //for the top bar
  2049. //'editoptions' => array('value' => '" ":'.get_lang('All').';1:'.get_lang('Active').';0:'.get_lang('Inactive'))
  2050. ),
  2051. //array('name'=>'course_code', 'index'=>'course_code', 'width'=>'40', 'hidden' => 'true', 'search' => 'true', 'searchoptions' => array('searchhidden' =>'true','sopt' => $operators)),
  2052. array('name'=>'visibility', 'index'=>'visibility', 'width'=>'40', 'align'=>'left', 'search' => 'false'),
  2053. array('name'=>'course_title', 'index'=>'course_title', 'width'=>'40', 'hidden' => 'true', 'search' => 'true', 'searchoptions' => array('searchhidden' =>'true','sopt' => $operators)),
  2054. );
  2055. //Inject extra session fields
  2056. $session_field = new SessionField();
  2057. $session_field_option = new SessionFieldOption();
  2058. $fields = $session_field->get_all(array('field_visible = ? AND field_filter = ?' => array(1, 1)));
  2059. $rules = array();
  2060. /*
  2061. $now = new DateTime();
  2062. $now->add(new DateInterval('P30D'));
  2063. $one_month = $now->format('Y-m-d H:m:s');*/
  2064. //$rules[] = array( "field" => "name", "op" => "cn", "data" => "");
  2065. //$rules[] = array( "field" => "category_name", "op" => "cn", "data" => "");
  2066. //$rules[] = array( "field" => "course_title", "op" => "cn", "data" => '');
  2067. //$rules[] = array( "field" => "display_start_date", "op" => "ge", "data" => api_get_local_time());
  2068. //$rules[] = array( "field" => "display_end_date", "op" => "le", "data" => api_get_local_time($one_month));
  2069. // var_dump($fields);exit;
  2070. if (!empty($fields)) {
  2071. foreach ($fields as $field) {
  2072. $search_options = array();
  2073. $type = 'text';
  2074. if (in_array($field['field_type'], array(ExtraField::FIELD_TYPE_SELECT, ExtraField::FIELD_TYPE_DOUBLE_SELECT))) {
  2075. $type = 'select';
  2076. $search_options['sopt'] = array('eq', 'ne'); //equal not equal
  2077. } else {
  2078. $search_options['sopt'] = array('cn', 'nc');//contains not contains
  2079. }
  2080. $search_options['searchhidden'] = 'true';
  2081. $search_options['defaultValue'] = $search_options['field_default_value'];
  2082. if ($field['field_type'] == ExtraField::FIELD_TYPE_DOUBLE_SELECT) {
  2083. //Add 2 selects
  2084. $options = $session_field_option->get_field_options_by_field($field['id']);
  2085. $options = ExtraField::extra_field_double_select_convert_array_to_ordered_array($options);
  2086. $first_options = array();
  2087. if (!empty($options)) {
  2088. foreach ($options as $option) {
  2089. foreach ($option as $sub_option) {
  2090. if ($sub_option['option_value'] == 0) {
  2091. $first_options[] = $sub_option['field_id'].'#'.$sub_option['id'].':'.$sub_option['option_display_text'];
  2092. } else {
  2093. }
  2094. }
  2095. }
  2096. }
  2097. $search_options['value'] = implode(';', $first_options);
  2098. $search_options['dataInit'] = 'fill_second_select';
  2099. //First
  2100. $column_model[] = array(
  2101. 'name' => 'extra_'.$field['field_variable'],
  2102. 'index' => 'extra_'.$field['field_variable'],
  2103. 'width' => '100',
  2104. 'hidden' => 'true',
  2105. 'search' => 'true',
  2106. 'stype' => 'select',
  2107. 'searchoptions' => $search_options
  2108. );
  2109. $columns[] = $field['field_display_text'].' (1)';
  2110. $rules[] = array('field' => 'extra_'.$field['field_variable'], 'op' => 'cn');
  2111. //Second
  2112. $search_options['value'] = $field['id'].':';
  2113. $search_options['dataInit'] = 'register_second_select';
  2114. $column_model[] = array(
  2115. 'name' => 'extra_'.$field['field_variable'].'_second',
  2116. 'index' => 'extra_'.$field['field_variable'].'_second',
  2117. 'width' => '100',
  2118. 'hidden' => 'true',
  2119. 'search' => 'true',
  2120. 'stype' => 'select',
  2121. 'searchoptions' => $search_options
  2122. );
  2123. $columns[] = $field['field_display_text'].' (2)';
  2124. $rules[] = array('field' => 'extra_'.$field['field_variable'].'_second', 'op' => 'cn');
  2125. continue;
  2126. } else {
  2127. $search_options['value'] = $session_field_option->get_field_options_to_string($field['id']);
  2128. }
  2129. $column_model[] = array(
  2130. 'name' => 'extra_'.$field['field_variable'],
  2131. 'index' => 'extra_'.$field['field_variable'],
  2132. 'width' => '100',
  2133. 'hidden' => 'true',
  2134. 'search' => 'true',
  2135. 'stype' => $type,
  2136. 'searchoptions' => $search_options
  2137. );
  2138. $columns[] = $field['field_display_text'];
  2139. $rules[] = array('field' => 'extra_'.$field['field_variable'], 'op' => 'cn');
  2140. }
  2141. }
  2142. $column_model[] = array('name'=>'actions', 'index'=>'actions', 'width'=>'80', 'align'=>'left','formatter'=>'action_formatter','sortable'=>'false', 'search' => 'false');
  2143. $columns[] = get_lang('Actions');
  2144. foreach ($column_model as $col_model) {
  2145. $simple_column_name[] = $col_model['name'];
  2146. }
  2147. $return_array = array(
  2148. 'columns' => $columns,
  2149. 'column_model' => $column_model,
  2150. 'rules' => $rules,
  2151. 'simple_column_name' => $simple_column_name
  2152. );
  2153. return $return_array;
  2154. }
  2155. }