sessionmanager.lib.php 42 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064
  1. <?php //$id: $
  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. * @package chamilo.library
  7. */
  8. /* LIBRARIES */
  9. require_once 'display.lib.php';
  10. /**
  11. * This class provides methods for sessions management.
  12. * Include/require it in your code to use its features.
  13. *
  14. * @package chamilo.library
  15. */
  16. class SessionManager {
  17. private function __construct() {
  18. }
  19. /**
  20. * Fetches a session from the database
  21. * @param int Session ID
  22. * @return array Session details (id, id_coach, name, nbr_courses, nbr_users, nbr_classes, date_start, date_end, nb_days_access_before_beginning,nb_days_access_after_end, session_admin_id)
  23. */
  24. public static function fetch($id) {
  25. $t = Database::get_main_table(TABLE_MAIN_SESSION);
  26. if ($id != strval(intval($id))) { return array(); }
  27. $s = "SELECT * FROM $t WHERE id = $id";
  28. $r = Database::query($s);
  29. if (Database::num_rows($r) != 1) { return array(); }
  30. return Database::fetch_array($r,'ASSOC');
  31. }
  32. /**
  33. * Create a session
  34. * @author Carlos Vargas <carlos.vargas@dokeos.com>,from existing code
  35. * @param string name
  36. * @param integer year_start
  37. * @param integer month_start
  38. * @param integer day_start
  39. * @param integer year_end
  40. * @param integer month_end
  41. * @param integer day_end
  42. * @param integer nb_days_acess_before
  43. * @param integer nb_days_acess_after
  44. * @param integer nolimit
  45. * @param string coach_username
  46. * @param integer id_session_category
  47. * @return $id_session;
  48. **/
  49. public static function create_session ($sname,$syear_start,$smonth_start,$sday_start,$syear_end,$smonth_end,$sday_end,$snb_days_acess_before,$snb_days_acess_after,$nolimit,$coach_username, $id_session_category,$id_visibility) {
  50. global $_user;
  51. $name= trim($sname);
  52. $year_start= intval($syear_start);
  53. $month_start=intval($smonth_start);
  54. $day_start=intval($sday_start);
  55. $year_end=intval($syear_end);
  56. $month_end=intval($smonth_end);
  57. $day_end=intval($sday_end);
  58. $nb_days_acess_before = intval($snb_days_acess_before);
  59. $nb_days_acess_after = intval($snb_days_acess_after);
  60. $id_session_category = intval($id_session_category);
  61. $id_visibility = intval($id_visibility);
  62. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  63. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  64. $sql = 'SELECT user_id FROM '.$tbl_user.' WHERE username="'.Database::escape_string($coach_username).'"';
  65. $rs = Database::query($sql);
  66. $id_coach = Database::result($rs,0,'user_id');
  67. if (empty($nolimit)) {
  68. $date_start="$year_start-".(($month_start < 10)?"0$month_start":$month_start)."-".(($day_start < 10)?"0$day_start":$day_start);
  69. $date_end="$year_end-".(($month_end < 10)?"0$month_end":$month_end)."-".(($day_end < 10)?"0$day_end":$day_end);
  70. } else {
  71. $id_visibility = 1; // by default is read only
  72. $date_start="000-00-00";
  73. $date_end="000-00-00";
  74. }
  75. if (empty($name)) {
  76. $msg=get_lang('SessionNameIsRequired');
  77. return $msg;
  78. } elseif (empty($coach_username)) {
  79. $msg=get_lang('CoachIsRequired');
  80. return $msg;
  81. } elseif (empty($nolimit) && (!$month_start || !$day_start || !$year_start || !checkdate($month_start,$day_start,$year_start))) {
  82. $msg=get_lang('InvalidStartDate');
  83. return $msg;
  84. } elseif (empty($nolimit) && (!$month_end || !$day_end || !$year_end || !checkdate($month_end,$day_end,$year_end))) {
  85. $msg=get_lang('InvalidEndDate');
  86. return $msg;
  87. } elseif(empty($nolimit) && $date_start >= $date_end) {
  88. $msg=get_lang('StartDateShouldBeBeforeEndDate');
  89. return $msg;
  90. } else {
  91. $rs = Database::query("SELECT 1 FROM $tbl_session WHERE name='".addslashes($name)."'");
  92. if(Database::num_rows($rs)) {
  93. $msg=get_lang('SessionNameAlreadyExists');
  94. return $msg;
  95. } else {
  96. $sql_insert = "INSERT INTO $tbl_session(name,date_start,date_end,id_coach,session_admin_id, nb_days_access_before_beginning, nb_days_access_after_end, session_category_id,visibility)
  97. VALUES('".Database::escape_string($name)."','$date_start','$date_end','$id_coach',".intval($_user['user_id']).",".$nb_days_acess_before.", ".$nb_days_acess_after.", ".$id_session_category.", ".$id_visibility.")";
  98. Database::query($sql_insert);
  99. $id_session=Database::insert_id();
  100. // add event to system log
  101. $time = time();
  102. $user_id = api_get_user_id();
  103. event_system(LOG_SESSION_CREATE, LOG_SESSION_ID, $id_session, $time, $user_id);
  104. return $id_session;
  105. }
  106. }
  107. }
  108. /**
  109. * Edit a session
  110. * @author Carlos Vargas <carlos.vargas@dokeos.com>,from existing code
  111. * @param integer id
  112. * @param string name
  113. * @param integer year_start
  114. * @param integer month_start
  115. * @param integer day_start
  116. * @param integer year_end
  117. * @param integer month_end
  118. * @param integer day_end
  119. * @param integer nb_days_acess_before
  120. * @param integer nb_days_acess_after
  121. * @param integer nolimit
  122. * @param integer id_coach
  123. * @param integer id_session_category
  124. * @return $id;
  125. * The parameter id is a primary key
  126. **/
  127. 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) {
  128. global $_user;
  129. $name=trim(stripslashes($name));
  130. $year_start=intval($year_start);
  131. $month_start=intval($month_start);
  132. $day_start=intval($day_start);
  133. $year_end=intval($year_end);
  134. $month_end=intval($month_end);
  135. $day_end=intval($day_end);
  136. $id_coach= intval($id_coach);
  137. $nb_days_acess_before= intval($nb_days_acess_before);
  138. $nb_days_acess_after = intval($nb_days_acess_after);
  139. $id_session_category = intval($id_session_category);
  140. $id_visibility = intval($id_visibility);
  141. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  142. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  143. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  144. if (empty($nolimit)) {
  145. $date_start="$year_start-".(($month_start < 10)?"0$month_start":$month_start)."-".(($day_start < 10)?"0$day_start":$day_start);
  146. $date_end="$year_end-".(($month_end < 10)?"0$month_end":$month_end)."-".(($day_end < 10)?"0$day_end":$day_end);
  147. } else {
  148. $date_start="000-00-00";
  149. $date_end="000-00-00";
  150. $id_visibility = 1;//force read only
  151. }
  152. if (empty($name)) {
  153. $msg=get_lang('SessionNameIsRequired');
  154. return $msg;
  155. } elseif (empty($id_coach)) {
  156. $msg=get_lang('CoachIsRequired');
  157. return $msg;
  158. } elseif (empty($nolimit) && (!$month_start || !$day_start || !$year_start || !checkdate($month_start,$day_start,$year_start))) {
  159. $msg=get_lang('InvalidStartDate');
  160. return $msg;
  161. } elseif (empty($nolimit) && (!$month_end || !$day_end || !$year_end || !checkdate($month_end,$day_end,$year_end))) {
  162. $msg=get_lang('InvalidEndDate');
  163. return $msg;
  164. } elseif (empty($nolimit) && $date_start >= $date_end) {
  165. $msg=get_lang('StartDateShouldBeBeforeEndDate');
  166. return $msg;
  167. } else {
  168. $rs = Database::query("SELECT id FROM $tbl_session WHERE name='".Database::escape_string($name)."'");
  169. $exists = false;
  170. while ($row = Database::fetch_array($rs)) {
  171. if($row['id']!=$id)
  172. $exists = true;
  173. }
  174. if ($exists) {
  175. $msg=get_lang('SessionNameAlreadyExists');
  176. return $msg;
  177. } else {
  178. $sql="UPDATE $tbl_session " .
  179. "SET name='".Database::escape_string($name)."',
  180. date_start='".$date_start."',
  181. date_end='".$date_end."',
  182. id_coach='".$id_coach."',
  183. nb_days_access_before_beginning = ".$nb_days_acess_before.",
  184. nb_days_access_after_end = ".$nb_days_acess_after.",
  185. session_category_id = ".$id_session_category." ,
  186. visibility= ".$id_visibility."
  187. WHERE id='$id'";
  188. Database::query($sql);
  189. /*$sqlu = "UPDATE $tbl_session_rel_course " .
  190. " SET id_coach='$id_coach'" .
  191. " WHERE id_session='$id'";
  192. Database::query($sqlu);*/
  193. return $id;
  194. }
  195. }
  196. }
  197. /**
  198. * Delete session
  199. * @author Carlos Vargas <carlos.vargas@dokeos.com>, from existing code
  200. * @param array id_checked
  201. * @param boolean optional, true if the function is called by a webservice, false otherwise.
  202. * @return void Nothing, or false on error
  203. * The parameters is a array to delete sessions
  204. **/
  205. public static function delete_session ($id_checked,$from_ws = false) {
  206. $tbl_session= Database::get_main_table(TABLE_MAIN_SESSION);
  207. $tbl_session_rel_course= Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  208. $tbl_session_rel_course_rel_user= Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  209. $tbl_session_rel_user= Database::get_main_table(TABLE_MAIN_SESSION_USER);
  210. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  211. global $_user;
  212. if(is_array($id_checked)) {
  213. $id_checked=Database::escape_string(implode(',',$id_checked));
  214. } else {
  215. $id_checked=intval($id_checked);
  216. }
  217. if (!api_is_platform_admin() && !$from_ws) {
  218. $sql = 'SELECT session_admin_id FROM '.Database :: get_main_table(TABLE_MAIN_SESSION).' WHERE id='.$id_checked;
  219. $rs = Database::query($sql);
  220. if (Database::result($rs,0,0)!=$_user['user_id']) {
  221. api_not_allowed(true);
  222. }
  223. }
  224. Database::query("DELETE FROM $tbl_session WHERE id IN($id_checked)");
  225. Database::query("DELETE FROM $tbl_session_rel_course WHERE id_session IN($id_checked)");
  226. Database::query("DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session IN($id_checked)");
  227. Database::query("DELETE FROM $tbl_session_rel_user WHERE id_session IN($id_checked)");
  228. // delete extra session fields
  229. $t_sf = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
  230. $t_sfv = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
  231. // Delete extra fields from session where field variable is "SECCION"
  232. $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' ";
  233. $rs_field = Database::query($sql);
  234. $field_id = 0;
  235. if (Database::num_rows($rs_field) == 1) {
  236. $row_field = Database::fetch_row($rs_field);
  237. $field_id = $row_field[0];
  238. $sql_delete_sfv = "DELETE FROM $t_sfv WHERE session_id = '$id_checked' AND field_id = '$field_id'";
  239. $rs_delete_sfv = Database::query($sql_delete_sfv);
  240. }
  241. $sql = "SELECT * FROM $t_sfv WHERE field_id = '$field_id' ";
  242. $rs_field_id = Database::query($sql);
  243. if (Database::num_rows($rs_field_id) == 0) {
  244. $sql_delete_sf = "DELETE FROM $t_sf WHERE id = '$field_id'";
  245. $rs_delete_sf = Database::query($sql_delete_sf);
  246. }
  247. /*
  248. $sql = "SELECT distinct field_id FROM $t_sfv WHERE session_id = '$id_checked'";
  249. $res_field_ids = @Database::query($sql);
  250. if (Database::num_rows($res_field_ids) > 0) {
  251. while($row_field_id = Database::fetch_row($res_field_ids)){
  252. $field_ids[] = $row_field_id[0];
  253. }
  254. }
  255. //delete from table_session_field_value from a given session id
  256. $sql_session_field_value = "DELETE FROM $t_sfv WHERE session_id = '$id_checked'";
  257. @Database::query($sql_session_field_value);
  258. $sql = "SELECT distinct field_id FROM $t_sfv";
  259. $res_field_all_ids = @Database::query($sql);
  260. if (Database::num_rows($res_field_all_ids) > 0) {
  261. while($row_field_all_id = Database::fetch_row($res_field_all_ids)){
  262. $field_all_ids[] = $row_field_all_id[0];
  263. }
  264. }
  265. if (count($field_ids) > 0 && count($field_all_ids) > 0) {
  266. foreach($field_ids as $field_id) {
  267. // check if field id is used into table field value
  268. if (in_array($field_id,$field_all_ids)) {
  269. continue;
  270. } else {
  271. $sql_session_field = "DELETE FROM $t_sf WHERE id = '$field_id'";
  272. Database::query($sql_session_field);
  273. }
  274. }
  275. }
  276. */
  277. // add event to system log
  278. $time = time();
  279. $user_id = api_get_user_id();
  280. event_system(LOG_SESSION_DELETE, LOG_SESSION_ID, $id_checked, $time, $user_id);
  281. }
  282. /**
  283. * Subscribes users to the given session and optionally (default) unsubscribes previous users
  284. * @author Carlos Vargas <carlos.vargas@dokeos.com>,from existing code
  285. * @param integer Session ID
  286. * @param array List of user IDs
  287. * @param bool Whether to unsubscribe existing users (true, default) or not (false)
  288. * @return void Nothing, or false on error
  289. **/
  290. public static function suscribe_users_to_session ($id_session,$user_list, $visibility=SESSION_VISIBLE_READ_ONLY, $empty_users=true,$send_email=false) {
  291. if ($id_session!= strval(intval($id_session))) return false;
  292. foreach($user_list as $intUser){
  293. if ($intUser!= strval(intval($intUser))) return false;
  294. }
  295. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  296. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  297. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  298. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  299. $session_info = api_get_session_info($id_session);
  300. $session_name = $session_info['name'];
  301. //from function parameter
  302. $session_visibility = $visibility;
  303. if (empty($session_visibility)) {
  304. $session_visibility = $session_info['name'];
  305. $session_visivility = $session_info['visibility']; //loaded from DB
  306. //default status loaded if empty
  307. if (empty($session_visivility))
  308. $session_visibility = SESSION_VISIBLE_READ_ONLY; // by default readonly 1
  309. }
  310. $session_info = api_get_session_info($id_session);
  311. $session_name = $session_info['name'];
  312. $sql = "SELECT id_user FROM $tbl_session_rel_user WHERE id_session='$id_session' AND relation_type<>".SESSION_RELATION_TYPE_RRHH."";
  313. $result = Database::query($sql);
  314. $existingUsers = array();
  315. while($row = Database::fetch_array($result)){
  316. $existingUsers[] = $row['id_user'];
  317. }
  318. $sql = "SELECT course_code FROM $tbl_session_rel_course WHERE id_session='$id_session'";
  319. $result=Database::query($sql);
  320. $course_list=array();
  321. while($row=Database::fetch_array($result)) {
  322. $course_list[]=$row['course_code'];
  323. }
  324. if ($send_email == true) {
  325. global $_configuration;
  326. //sending emails only
  327. if(is_array($user_list) && count($user_list)>0) {
  328. foreach($user_list as $enreg_user) {
  329. if (!in_array($enreg_user,$existingUsers )) {
  330. //send email
  331. $emailbody = '';
  332. $emailheaders = '';
  333. $user_info = UserManager::get_user_info_by_id($enreg_user);
  334. $firstname = $user_info['firstname'];
  335. $lastname = $user_info['lastname'];
  336. $email = $user_info['email'];
  337. $emailto = '"'.$firstname.' '.$lastname.'" <'.$email.'>';
  338. $emailsubject = '['.get_setting('siteName').'] '.get_lang('YourReg').' '.get_setting('siteName');
  339. $emailheaders = 'From: '.get_setting('administratorName').' '.get_setting('administratorSurname').' <'.get_setting('emailAdministrator').">\n";
  340. $emailheaders .= 'Reply-To: '.get_setting('emailAdministrator');
  341. if ($_configuration['multiple_access_urls']==true) {
  342. $access_url_id = api_get_current_access_url_id();
  343. if ($access_url_id != -1 ){
  344. $url = api_get_access_url($access_url_id);
  345. $emailbody = get_lang('Dear')." ".stripslashes(api_get_person_name($firstname, $lastname)).",\n\n".get_lang('YouAreRegisterToSession')." : ". $session_name ." \n\n" .get_lang('Address') ." ". get_setting('siteName') ." ". get_lang('Is') ." : ". $url['url'] ."\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');
  346. }
  347. } else {
  348. $emailbody = get_lang('Dear')." ".stripslashes(api_get_person_name($firstname, $lastname)).",\n\n".get_lang('YouAreRegisterToSession')." : ". $session_name ." \n\n" .get_lang('Address') ." ". get_setting('siteName') ." ". get_lang('Is') ." : ". $_configuration['root_web'] ."\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');
  349. }
  350. @api_send_mail($emailto, $emailsubject, $emailbody, $emailheaders);
  351. }
  352. }
  353. }
  354. }
  355. foreach ($course_list as $enreg_course) {
  356. // for each course in the session
  357. $nbr_users=0;
  358. $enreg_course = Database::escape_string($enreg_course);
  359. // delete existing users
  360. if ($empty_users!==false) {
  361. foreach ($existingUsers as $existing_user) {
  362. if(!in_array($existing_user, $user_list)) {
  363. $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 != 2 ";
  364. Database::query($sql);
  365. if(Database::affected_rows()) {
  366. $nbr_users--;
  367. }
  368. }
  369. }
  370. }
  371. // insert new users into session_rel_course_rel_user and ignore if they already exist
  372. foreach ($user_list as $enreg_user) {
  373. if(!in_array($enreg_user, $existingUsers)) {
  374. $enreg_user = Database::escape_string($enreg_user);
  375. $insert_sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user(id_session,course_code,id_user,visibility) VALUES('$id_session','$enreg_course','$enreg_user','$session_visivility')";
  376. Database::query($insert_sql);
  377. if(Database::affected_rows()) {
  378. $nbr_users++;
  379. }
  380. }
  381. }
  382. // count users in this session-course relation
  383. $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";
  384. $rs = Database::query($sql);
  385. list($nbr_users) = Database::fetch_array($rs);
  386. // update the session-course relation to add the users total
  387. $update_sql = "UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users WHERE id_session='$id_session' AND course_code='$enreg_course'";
  388. Database::query($update_sql);
  389. }
  390. // delete users from the session
  391. if ($empty_users===true){
  392. Database::query("DELETE FROM $tbl_session_rel_user WHERE id_session = $id_session AND relation_type<>".SESSION_RELATION_TYPE_RRHH."");
  393. }
  394. // insert missing users into session
  395. $nbr_users = 0;
  396. foreach ($user_list as $enreg_user) {
  397. $enreg_user = Database::escape_string($enreg_user);
  398. $nbr_users++;
  399. $insert_sql = "INSERT IGNORE INTO $tbl_session_rel_user(id_session, id_user) VALUES('$id_session','$enreg_user')";
  400. Database::query($insert_sql);
  401. }
  402. // update number of users in the session
  403. $nbr_users = count($user_list);
  404. $update_sql = "UPDATE $tbl_session SET nbr_users= $nbr_users WHERE id='$id_session' ";
  405. Database::query($update_sql);
  406. }
  407. /** Subscribes courses to the given session and optionally (default) unsubscribes previous users
  408. * @author Carlos Vargas <carlos.vargas@dokeos.com>,from existing code
  409. * @param int Session ID
  410. * @param array List of courses IDs
  411. * @param bool Whether to unsubscribe existing users (true, default) or not (false)
  412. * @return void Nothing, or false on error
  413. **/
  414. public static function add_courses_to_session ($id_session, $course_list, $empty_courses=true) {
  415. // security checks
  416. if ($id_session!= strval(intval($id_session))) { return false; }
  417. foreach($course_list as $intCourse){
  418. if ($intCourse!= strval(intval($intCourse))) { return false; }
  419. }
  420. // initialisation
  421. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  422. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  423. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  424. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  425. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  426. // get general coach ID
  427. $id_coach = Database::query("SELECT id_coach FROM $tbl_session WHERE id=$id_session");
  428. $id_coach = Database::fetch_array($id_coach);
  429. $id_coach = $id_coach[0];
  430. // get list of courses subscribed to this session
  431. $rs = Database::query("SELECT course_code FROM $tbl_session_rel_course WHERE id_session=$id_session");
  432. $existingCourses = Database::store_result($rs);
  433. $nbr_courses=count($existingCourses);
  434. // get list of users subscribed to this session
  435. $sql="SELECT id_user
  436. FROM $tbl_session_rel_user
  437. WHERE id_session = $id_session AND relation_type<>".SESSION_RELATION_TYPE_RRHH."";
  438. $result=Database::query($sql);
  439. $user_list=Database::store_result($result);
  440. // remove existing courses from the session
  441. if ($empty_courses===true) {
  442. foreach ($existingCourses as $existingCourse) {
  443. if (!in_array($existingCourse['course_code'], $course_list)){
  444. Database::query("DELETE FROM $tbl_session_rel_course WHERE course_code='".$existingCourse['course_code']."' AND id_session=$id_session");
  445. Database::query("DELETE FROM $tbl_session_rel_course_rel_user WHERE course_code='".$existingCourse['course_code']."' AND id_session=$id_session");
  446. }
  447. }
  448. $nbr_courses=0;
  449. }
  450. // Pass through the courses list we want to add to the session
  451. foreach ($course_list as $enreg_course) {
  452. $enreg_course = Database::escape_string($enreg_course);
  453. $exists = false;
  454. // check if the course we want to add is already subscribed
  455. foreach ($existingCourses as $existingCourse) {
  456. if ($enreg_course == $existingCourse['course_code']) {
  457. $exists=true;
  458. }
  459. }
  460. if (!$exists) {
  461. //if the course isn't subscribed yet
  462. $sql_insert_rel_course= "INSERT INTO $tbl_session_rel_course (id_session,course_code) VALUES ('$id_session','$enreg_course')";
  463. Database::query($sql_insert_rel_course);
  464. //We add the current course in the existing courses array, to avoid adding another time the current course
  465. $existingCourses[]=array('course_code'=>$enreg_course);
  466. $nbr_courses++;
  467. // subscribe all the users from the session to this course inside the session
  468. $nbr_users=0;
  469. foreach ($user_list as $enreg_user) {
  470. $enreg_user_id = Database::escape_string($enreg_user['id_user']);
  471. $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')";
  472. Database::query($sql_insert);
  473. if (Database::affected_rows()) {
  474. $nbr_users++;
  475. }
  476. }
  477. Database::query("UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users WHERE id_session='$id_session' AND course_code='$enreg_course'");
  478. }
  479. }
  480. Database::query("UPDATE $tbl_session SET nbr_courses=$nbr_courses WHERE id='$id_session'");
  481. }
  482. /**
  483. * Creates a new extra field for a given session
  484. * @param string Field's internal variable name
  485. * @param int Field's type
  486. * @param string Field's language var name
  487. * @return int new extra field id
  488. */
  489. public static function create_session_extra_field ($fieldvarname, $fieldtype, $fieldtitle) {
  490. // database table definition
  491. $t_sf = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
  492. $fieldvarname = Database::escape_string($fieldvarname);
  493. $fieldtitle = Database::escape_string($fieldtitle);
  494. $fieldtype = (int)$fieldtype;
  495. $time = time();
  496. $sql_field = "SELECT id FROM $t_sf WHERE field_variable = '$fieldvarname'";
  497. $res_field = Database::query($sql_field);
  498. $r_field = Database::fetch_row($res_field);
  499. if (Database::num_rows($res_field)>0) {
  500. $field_id = $r_field[0];
  501. } else {
  502. // save new fieldlabel into course_field table
  503. $sql = "SELECT MAX(field_order) FROM $t_sf";
  504. $res = Database::query($sql);
  505. $order = 0;
  506. if (Database::num_rows($res)>0) {
  507. $row = Database::fetch_row($res);
  508. $order = $row[0]+1;
  509. }
  510. $sql = "INSERT INTO $t_sf
  511. SET field_type = '$fieldtype',
  512. field_variable = '$fieldvarname',
  513. field_display_text = '$fieldtitle',
  514. field_order = '$order',
  515. tms = FROM_UNIXTIME($time)";
  516. $result = Database::query($sql);
  517. $field_id=Database::insert_id();
  518. }
  519. return $field_id;
  520. }
  521. /**
  522. * Update an extra field value for a given session
  523. * @param integer Course ID
  524. * @param string Field variable name
  525. * @param string Field value
  526. * @return boolean true if field updated, false otherwise
  527. */
  528. public static function update_session_extra_field_value ($session_id,$fname,$fvalue='') {
  529. $t_sf = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
  530. $t_sfv = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
  531. $fname = Database::escape_string($fname);
  532. $session_id = (int)$session_id;
  533. $fvalues = '';
  534. if(is_array($fvalue))
  535. {
  536. foreach($fvalue as $val)
  537. {
  538. $fvalues .= Database::escape_string($val).';';
  539. }
  540. if(!empty($fvalues))
  541. {
  542. $fvalues = substr($fvalues,0,-1);
  543. }
  544. }
  545. else
  546. {
  547. $fvalues = Database::escape_string($fvalue);
  548. }
  549. $sqlsf = "SELECT * FROM $t_sf WHERE field_variable='$fname'";
  550. $ressf = Database::query($sqlsf);
  551. if(Database::num_rows($ressf)==1)
  552. { //ok, the field exists
  553. // Check if enumerated field, if the option is available
  554. $rowsf = Database::fetch_array($ressf);
  555. $tms = time();
  556. $sqlsfv = "SELECT * FROM $t_sfv WHERE session_id = '$session_id' AND field_id = '".$rowsf['id']."' ORDER BY id";
  557. $ressfv = Database::query($sqlsfv);
  558. $n = Database::num_rows($ressfv);
  559. if ($n>1) {
  560. //problem, we already have to values for this field and user combination - keep last one
  561. while($rowsfv = Database::fetch_array($ressfv))
  562. {
  563. if($n > 1)
  564. {
  565. $sqld = "DELETE FROM $t_sfv WHERE id = ".$rowsfv['id'];
  566. $resd = Database::query($sqld);
  567. $n--;
  568. }
  569. $rowsfv = Database::fetch_array($ressfv);
  570. if($rowsfv['field_value'] != $fvalues)
  571. {
  572. $sqlu = "UPDATE $t_sfv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowsfv['id'];
  573. $resu = Database::query($sqlu);
  574. return($resu?true:false);
  575. }
  576. return true;
  577. }
  578. } else if ($n==1) {
  579. //we need to update the current record
  580. $rowsfv = Database::fetch_array($ressfv);
  581. if($rowsfv['field_value'] != $fvalues)
  582. {
  583. $sqlu = "UPDATE $t_sfv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowsfv['id'];
  584. //error_log('UM::update_extra_field_value: '.$sqlu);
  585. $resu = Database::query($sqlu);
  586. return($resu?true:false);
  587. }
  588. return true;
  589. } else {
  590. $sqli = "INSERT INTO $t_sfv (session_id,field_id,field_value,tms) " .
  591. "VALUES ('$session_id',".$rowsf['id'].",'$fvalues',FROM_UNIXTIME($tms))";
  592. //error_log('UM::update_extra_field_value: '.$sqli);
  593. $resi = Database::query($sqli);
  594. return($resi?true:false);
  595. }
  596. } else {
  597. return false; //field not found
  598. }
  599. }
  600. /**
  601. * Checks the relationship between a session and a course.
  602. * @param int $session_id
  603. * @param int $course_id
  604. * @return bool Returns TRUE if the session and the course are related, FALSE otherwise.
  605. * */
  606. public static function relation_session_course_exist ($session_id, $course_id) {
  607. $tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  608. $return_value = false;
  609. $sql= "SELECT course_code FROM $tbl_session_course WHERE id_session = ".Database::escape_string($session_id)." AND course_code = '".Database::escape_string($course_id)."'";
  610. $result = Database::query($sql);
  611. $num = Database::num_rows($result);
  612. if ($num>0) {
  613. $return_value = true;
  614. }
  615. return $return_value;
  616. }
  617. /**
  618. * Get the session information by name
  619. * @param string session name
  620. * @return mixed false if the session does not exist, array if the session exist
  621. * */
  622. public static function get_session_by_name ($session_name) {
  623. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  624. $sql = 'SELECT id, id_coach, date_start, date_end FROM '.$tbl_session.' WHERE name="'.Database::escape_string($session_name).'"';
  625. $result = Database::query($sql);
  626. $num = Database::num_rows($result);
  627. if ($num>0){
  628. return Database::fetch_array($result);
  629. } else {
  630. return false;
  631. }
  632. }
  633. /**
  634. * Create a session category
  635. * @author Jhon Hinojosa <jhon.hinojosa@dokeos.com>, from existing code
  636. * @param string name
  637. * @param integer year_start
  638. * @param integer month_start
  639. * @param integer day_start
  640. * @param integer year_end
  641. * @param integer month_end
  642. * @param integer day_end
  643. * @return $id_session;
  644. **/
  645. public static function create_category_session($sname,$syear_start,$smonth_start,$sday_start,$syear_end,$smonth_end, $sday_end){
  646. $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  647. $name= trim($sname);
  648. $year_start= intval($syear_start);
  649. $month_start=intval($smonth_start);
  650. $day_start=intval($sday_start);
  651. $year_end=intval($syear_end);
  652. $month_end=intval($smonth_end);
  653. $day_end=intval($sday_end);
  654. $date_start = "$year_start-".(($month_start < 10)?"0$month_start":$month_start)."-".(($day_start < 10)?"0$day_start":$day_start);
  655. $date_end = "$year_end-".(($month_end < 10)?"0$month_end":$month_end)."-".(($day_end < 10)?"0$day_end":$day_end);
  656. if (empty($name)) {
  657. $msg=get_lang('SessionCategoryNameIsRequired');
  658. return $msg;
  659. } elseif (!$month_start || !$day_start || !$year_start || !checkdate($month_start,$day_start,$year_start)) {
  660. $msg=get_lang('InvalidStartDate');
  661. return $msg;
  662. } elseif (!$month_end || !$day_end || !$year_end || !checkdate($month_end,$day_end,$year_end)) {
  663. $msg=get_lang('InvalidEndDate');
  664. return $msg;
  665. } elseif($date_start >= $date_end) {
  666. $msg=get_lang('StartDateShouldBeBeforeEndDate');
  667. return $msg;
  668. } else {
  669. $sql = "INSERT INTO $tbl_session_category(name, date_start, date_end) VALUES('".Database::escape_string($name)."','$date_start','$date_end')";
  670. Database::query($sql);
  671. $id_session=Database::insert_id();
  672. // add event to system log
  673. $time = time();
  674. $user_id = api_get_user_id();
  675. event_system(LOG_SESSION_CATEGORY_CREATE, LOG_SESSION_CATEGORY_ID, $id_session, $time, $user_id);
  676. return $id_session;
  677. }
  678. }
  679. /**
  680. * Edit a sessions categories
  681. * @author Jhon Hinojosa <jhon.hinojosa@dokeos.com>,from existing code
  682. * @param integer id
  683. * @param string name
  684. * @param integer year_start
  685. * @param integer month_start
  686. * @param integer day_start
  687. * @param integer year_end
  688. * @param integer month_end
  689. * @param integer day_end
  690. * @return $id;
  691. * The parameter id is a primary key
  692. **/
  693. public static function edit_category_session($id, $sname,$syear_start,$smonth_start,$sday_start,$syear_end,$smonth_end, $sday_end){
  694. $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  695. $name= trim($sname);
  696. $year_start= intval($syear_start);
  697. $month_start=intval($smonth_start);
  698. $day_start=intval($sday_start);
  699. $year_end=intval($syear_end);
  700. $month_end=intval($smonth_end);
  701. $day_end=intval($sday_end);
  702. $id=intval($id);
  703. $date_start = "$year_start-".(($month_start < 10)?"0$month_start":$month_start)."-".(($day_start < 10)?"0$day_start":$day_start);
  704. $date_end = "$year_end-".(($month_end < 10)?"0$month_end":$month_end)."-".(($day_end < 10)?"0$day_end":$day_end);
  705. if (empty($name)) {
  706. $msg=get_lang('SessionCategoryNameIsRequired');
  707. return $msg;
  708. } elseif (!$month_start || !$day_start || !$year_start || !checkdate($month_start,$day_start,$year_start)) {
  709. $msg=get_lang('InvalidStartDate');
  710. return $msg;
  711. } elseif (!$month_end || !$day_end || !$year_end || !checkdate($month_end,$day_end,$year_end)) {
  712. $msg=get_lang('InvalidEndDate');
  713. return $msg;
  714. } elseif($date_start >= $date_end) {
  715. $msg=get_lang('StartDateShouldBeBeforeEndDate');
  716. return $msg;
  717. } else {
  718. $sql = "UPDATE $tbl_session_category SET name = '".Database::escape_string($name)."', date_start = '$date_start', date_end = '$date_end'
  719. WHERE id= '".$id."' ";
  720. $result = Database::query($sql);
  721. return ($result? true:false);
  722. }
  723. }
  724. /**
  725. * Delete sessions categories
  726. * @author Jhon Hinojosa <jhon.hinojosa@dokeos.com>, from existing code
  727. * @param array id_checked
  728. * @param bool include delete session
  729. * @param bool optional, true if the function is called by a webservice, false otherwise.
  730. * @return void Nothing, or false on error
  731. * The parameters is a array to delete sessions
  732. **/
  733. public static function delete_session_category($id_checked, $delete_session = false,$from_ws = false){
  734. $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  735. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  736. if(is_array($id_checked)) {
  737. $id_checked=Database::escape_string(implode(',',$id_checked));
  738. } else {
  739. $id_checked=intval($id_checked);
  740. }
  741. $sql = "SELECT id FROM $tbl_session WHERE session_category_id IN (".$id_checked.")";
  742. $result = @Database::query($sql);
  743. while ($rows = Database::fetch_array($result)) {
  744. $session_id = $rows['id'];
  745. if($delete_session == true){
  746. if ($from_ws) {
  747. SessionManager::delete_session($session_id,true);
  748. } else {
  749. SessionManager::delete_session($session_id);
  750. }
  751. }
  752. }
  753. $sql = "DELETE FROM $tbl_session_category WHERE id IN (".$id_checked.")";
  754. $rs = @Database::query($sql);
  755. $result = Database::affected_rows();
  756. // add event to system log
  757. $time = time();
  758. $user_id = api_get_user_id();
  759. event_system(LOG_SESSION_CATEGORY_DELETE, LOG_SESSION_CATEGORY_ID, $id_checked, $time, $user_id);
  760. // delete extra session fields where field variable is "PERIODO"
  761. $t_sf = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
  762. $t_sfv = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
  763. $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' ";
  764. $rs_field = Database::query($sql);
  765. $field_id = 0;
  766. if (Database::num_rows($rs_field) > 0) {
  767. $row_field = Database::fetch_row($rs_field);
  768. $field_id = $row_field[0];
  769. $sql_delete_sfv = "DELETE FROM $t_sfv WHERE session_id = '$id_checked' AND field_id = '$field_id'";
  770. $rs_delete_sfv = Database::query($sql_delete_sfv);
  771. }
  772. $sql = "SELECT * FROM $t_sfv WHERE field_id = '$field_id' ";
  773. $rs_field_id = Database::query($sql);
  774. if (Database::num_rows($rs_field_id) == 0) {
  775. $sql_delete_sf = "DELETE FROM $t_sf WHERE id = '$field_id'";
  776. $rs_delete_sf = Database::query($sql_delete_sf);
  777. }
  778. return true;
  779. }
  780. /**
  781. * Get a list of sessions of which the given conditions match with an = 'cond'
  782. * @param array $conditions a list of condition (exemple : status=>STUDENT)
  783. * @param array $order_by a list of fields on which sort
  784. * @return array An array with all sessions of the platform.
  785. * @todo optional course code parameter, optional sorting parameters...
  786. */
  787. public static function get_sessions_list ($conditions = array(), $order_by = array()) {
  788. $session_table =Database::get_main_table(TABLE_MAIN_SESSION);
  789. $session_category_table = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  790. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  791. $return_array = array();
  792. $sql_query = " SELECT s.id, s.name, s.nbr_courses, s.date_start, s.date_end, u.firstname, u.lastname , sc.name as category_name
  793. FROM $session_table s
  794. INNER JOIN $user_table u ON s.id_coach = u.user_id
  795. LEFT JOIN $session_category_table sc ON s.session_category_id = sc.id ";
  796. if (count($conditions)>0) {
  797. $sql_query .= ' WHERE ';
  798. foreach ($conditions as $field=>$value) {
  799. $field = Database::escape_string($field);
  800. $value = Database::escape_string($value);
  801. $sql_query .= $field.' = '.$value;
  802. }
  803. }
  804. if (count($order_by)>0) {
  805. $sql_query .= ' ORDER BY '.Database::escape_string(implode(',',$order_by));
  806. }
  807. $sql_result = Database::query($sql_query);
  808. while ($result = Database::fetch_array($sql_result)) {
  809. $return_array[$result['id']] = $result;
  810. }
  811. return $return_array;
  812. }
  813. /**
  814. * Get the session category information by id
  815. * @param string session category ID
  816. * @return mixed false if the session category does not exist, array if the session category exists
  817. */
  818. public static function get_session_category ($id) {
  819. $id = intval($id);
  820. $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  821. $sql = 'SELECT id, name, date_start, date_end FROM '.$tbl_session_category.' WHERE id="'.$id.'"';
  822. $result = Database::query($sql);
  823. $num = Database::num_rows($result);
  824. if ($num>0){
  825. return Database::fetch_array($result);
  826. } else {
  827. return false;
  828. }
  829. }
  830. /**
  831. * Assign a coach to course in session with status = 2
  832. * @param int - user id
  833. * @param int - session id
  834. * @param string - course code
  835. * @param bool - optional, if is true the user don't be a coach now, otherwise it'll assign a coach
  836. * @return bool true if there are affected rows, otherwise false
  837. */
  838. public static function set_coach_to_course_session($user_id, $session_id = 0, $course_code = '',$nocoach = false) {
  839. // Definition of variables
  840. $user_id = intval($user_id);
  841. if (!empty($session_id)) {
  842. $session_id = intval($session_id);
  843. } else {
  844. $session_id = api_get_session_id();
  845. }
  846. if (!empty($course_code)) {
  847. $course_code = Database::escape_string($course_code);
  848. } else {
  849. $course_code = api_get_course_id();
  850. }
  851. // definitios of tables
  852. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  853. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  854. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  855. // check if user is a teacher
  856. $sql= "SELECT * FROM $tbl_user WHERE status='1' AND user_id = '$user_id'";
  857. $rs_check_user = Database::query($sql);
  858. if (Database::num_rows($rs_check_user) > 0) {
  859. if ($nocoach) {
  860. // check if user_id exits int session_rel_user
  861. $sql = "SELECT id_user FROM $tbl_session_rel_user WHERE id_session = '$session_id' AND id_user = '$user_id'";
  862. $res = Database::query($sql);
  863. if (Database::num_rows($res) > 0) {
  864. // The user don't be a coach now
  865. $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' ";
  866. $rs_update = Database::query($sql);
  867. if (Database::affected_rows() > 0) return true;
  868. else return false;
  869. } else {
  870. // The user don't be a coach now
  871. $sql = "DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session = '$session_id' AND course_code = '$course_code' AND id_user = '$user_id' ";
  872. $rs_delete = Database::query($sql);
  873. if (Database::affected_rows() > 0) return true;
  874. else return false;
  875. }
  876. } else {
  877. // Assign user like a coach to course
  878. // First check if the user is registered in the course
  879. $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'";
  880. $rs_check = Database::query($sql);
  881. //Then update or insert
  882. if (Database::num_rows($rs_check) > 0) {
  883. $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' ";
  884. $rs_update = Database::query($sql);
  885. if (Database::affected_rows() > 0) return true;
  886. else return false;
  887. } else {
  888. $sql = " INSERT INTO $tbl_session_rel_course_rel_user(id_session, course_code, id_user, status) VALUES('$session_id', '$course_code', '$user_id', 2)";
  889. $rs_insert = Database::query($sql);
  890. if (Database::affected_rows() > 0) return true;
  891. else return false;
  892. }
  893. }
  894. } else {
  895. return false;
  896. }
  897. }
  898. /**
  899. * Subscribes sessions to human resource manager (Dashboard feature)
  900. * @param int Human Resource Manager id
  901. * @param array Sessions id
  902. * @param int Relation type
  903. **/
  904. public static function suscribe_sessions_to_hr_manager($hr_manager_id,$sessions_list) {
  905. // Database Table Definitions
  906. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  907. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  908. $tbl_session_rel_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  909. $hr_manager_id = intval($hr_manager_id);
  910. $affected_rows = 0;
  911. //Deleting assigned sessions to hrm_id
  912. $sql = "SELECT id_session FROM $tbl_session_rel_user WHERE id_user = $hr_manager_id AND relation_type=".SESSION_RELATION_TYPE_RRHH." ";
  913. $result = Database::query($sql);
  914. if (Database::num_rows($result) > 0) {
  915. $sql = "DELETE FROM $tbl_session_rel_user WHERE id_user = $hr_manager_id AND relation_type=".SESSION_RELATION_TYPE_RRHH." ";
  916. Database::query($sql);
  917. }
  918. /*
  919. //Deleting assigned courses in sessions to hrm_id
  920. $sql = "SELECT * FROM $tbl_session_rel_course_user WHERE id_user = $hr_manager_id ";
  921. $result = Database::query($sql);
  922. if (Database::num_rows($result) > 0) {
  923. $sql = "DELETE FROM $tbl_session_rel_course_user WHERE id_user = $hr_manager_id ";
  924. Database::query($sql);
  925. }
  926. */
  927. // inserting new sessions list
  928. if (is_array($sessions_list)) {
  929. foreach ($sessions_list as $session_id) {
  930. $session_id = intval($session_id);
  931. $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."')";
  932. Database::query($insert_sql);
  933. $affected_rows = Database::affected_rows();
  934. }
  935. }
  936. return $affected_rows;
  937. }
  938. /**
  939. * Get sessions followed by human resources manager
  940. * @param int Human resources manager id
  941. * @return array sessions
  942. */
  943. public static function get_sessions_followed_by_drh($hr_manager_id) {
  944. // Database Table Definitions
  945. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  946. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  947. $hr_manager_id = intval($hr_manager_id);
  948. $assigned_sessions_to_hrm = array();
  949. $sql = "SELECT * FROM $tbl_session s
  950. 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."' ";
  951. $rs_assigned_sessions = Database::query($sql);
  952. if (Database::num_rows($rs_assigned_sessions) > 0) {
  953. while ($row_assigned_sessions = Database::fetch_array($rs_assigned_sessions)) {
  954. $assigned_sessions_to_hrm[$row_assigned_sessions['id']] = $row_assigned_sessions;
  955. }
  956. }
  957. return $assigned_sessions_to_hrm;
  958. }
  959. }