myspace.lib.php 125 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032203320342035203620372038203920402041204220432044204520462047204820492050205120522053205420552056205720582059206020612062206320642065206620672068206920702071207220732074207520762077207820792080208120822083208420852086208720882089209020912092209320942095209620972098209921002101210221032104210521062107210821092110211121122113211421152116211721182119212021212122212321242125212621272128212921302131213221332134213521362137213821392140214121422143214421452146214721482149215021512152215321542155215621572158215921602161216221632164216521662167216821692170217121722173217421752176217721782179218021812182218321842185218621872188218921902191219221932194219521962197219821992200220122022203220422052206220722082209221022112212221322142215221622172218221922202221222222232224222522262227222822292230223122322233223422352236223722382239224022412242224322442245224622472248224922502251225222532254225522562257225822592260226122622263226422652266226722682269227022712272227322742275227622772278227922802281228222832284228522862287228822892290229122922293229422952296229722982299230023012302230323042305230623072308230923102311231223132314231523162317231823192320232123222323232423252326232723282329233023312332233323342335233623372338233923402341234223432344234523462347234823492350235123522353235423552356235723582359236023612362236323642365236623672368236923702371237223732374237523762377237823792380238123822383238423852386238723882389239023912392239323942395239623972398239924002401240224032404240524062407240824092410241124122413241424152416241724182419242024212422242324242425242624272428242924302431243224332434243524362437243824392440244124422443244424452446244724482449245024512452245324542455245624572458245924602461246224632464246524662467246824692470247124722473247424752476247724782479248024812482248324842485248624872488248924902491249224932494249524962497249824992500250125022503250425052506250725082509251025112512251325142515251625172518251925202521252225232524252525262527252825292530253125322533253425352536253725382539254025412542254325442545254625472548254925502551255225532554255525562557255825592560256125622563256425652566256725682569257025712572257325742575257625772578257925802581258225832584258525862587258825892590259125922593259425952596259725982599260026012602260326042605260626072608260926102611261226132614261526162617261826192620262126222623262426252626262726282629263026312632263326342635263626372638263926402641264226432644264526462647264826492650265126522653265426552656265726582659266026612662266326642665266626672668266926702671267226732674267526762677267826792680268126822683268426852686268726882689269026912692269326942695269626972698269927002701270227032704270527062707270827092710271127122713271427152716271727182719272027212722272327242725272627272728272927302731273227332734273527362737273827392740274127422743274427452746274727482749275027512752275327542755275627572758275927602761276227632764276527662767276827692770277127722773277427752776277727782779278027812782278327842785278627872788278927902791279227932794279527962797279827992800280128022803280428052806280728082809281028112812281328142815281628172818281928202821282228232824282528262827282828292830283128322833283428352836283728382839284028412842284328442845284628472848284928502851285228532854285528562857285828592860286128622863286428652866286728682869287028712872287328742875287628772878287928802881288228832884288528862887288828892890289128922893289428952896289728982899290029012902290329042905290629072908290929102911291229132914291529162917291829192920292129222923292429252926292729282929293029312932293329342935293629372938293929402941294229432944294529462947294829492950295129522953295429552956295729582959296029612962296329642965296629672968296929702971297229732974297529762977297829792980298129822983298429852986298729882989299029912992299329942995299629972998299930003001300230033004300530063007300830093010301130123013301430153016301730183019302030213022302330243025302630273028302930303031303230333034303530363037303830393040304130423043304430453046304730483049305030513052305330543055305630573058305930603061306230633064306530663067306830693070307130723073307430753076307730783079308030813082308330843085308630873088308930903091309230933094309530963097309830993100310131023103310431053106310731083109311031113112311331143115311631173118311931203121312231233124312531263127312831293130313131323133313431353136313731383139314031413142314331443145314631473148314931503151315231533154
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. use CpChart\Cache as pCache;
  4. use CpChart\Data as pData;
  5. use CpChart\Image as pImage;
  6. /**
  7. * Class MySpace
  8. * @package chamilo.reporting
  9. */
  10. class MySpace
  11. {
  12. /**
  13. * Get admin actions
  14. * @return string
  15. */
  16. public static function getAdminActions()
  17. {
  18. $actions = array(
  19. //array('url' => api_get_path(WEB_CODE_PATH).'mySpace/index.php', 'content' => get_lang('Home')),
  20. array(
  21. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=coaches',
  22. 'content' => get_lang('DisplayCoaches'),
  23. ),
  24. array(
  25. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=user',
  26. 'content' => get_lang('DisplayUserOverview'),
  27. ),
  28. array(
  29. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=session',
  30. 'content' => get_lang('DisplaySessionOverview'),
  31. ),
  32. array(
  33. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=course',
  34. 'content' => get_lang('DisplayCourseOverview'),
  35. ),
  36. array(
  37. 'url' => api_get_path(WEB_CODE_PATH).'tracking/question_course_report.php?view=admin',
  38. 'content' => get_lang('LPQuestionListResults'),
  39. ),
  40. array(
  41. 'url' => api_get_path(WEB_CODE_PATH).'tracking/course_session_report.php?view=admin',
  42. 'content' => get_lang('LPExerciseResultsBySession'),
  43. ),
  44. [
  45. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=accessoverview',
  46. 'content' => get_lang('DisplayAccessOverview').' ('.get_lang('Beta').')',
  47. ],
  48. );
  49. return Display::actions($actions, null);
  50. }
  51. /**
  52. * @return string
  53. */
  54. public static function getTopMenu()
  55. {
  56. $menu_items = array();
  57. $menu_items[] = Display::url(
  58. Display::return_icon('stats.png', get_lang('MyStats'), '', ICON_SIZE_MEDIUM),
  59. api_get_path(WEB_CODE_PATH)."auth/my_progress.php"
  60. );
  61. $menu_items[] = Display::url(
  62. Display::return_icon('teacher.png', get_lang('TeacherInterface'), array(), 32),
  63. api_get_path(WEB_CODE_PATH).'mySpace/?view=teacher'
  64. );
  65. $menu_items[] = Display::url(Display::return_icon('star_na.png', get_lang('AdminInterface'), array(), 32), '#');
  66. $menu_items[] = Display::url(
  67. Display::return_icon('quiz.png', get_lang('ExamTracking'), array(), 32),
  68. api_get_path(WEB_CODE_PATH).'tracking/exams.php'
  69. );
  70. $menu = '';
  71. foreach ($menu_items as $item) {
  72. $menu .= $item;
  73. }
  74. $menu .= '<br />';
  75. return $menu;
  76. }
  77. /**
  78. * This function serves exporting data in CSV format.
  79. * @param array $header The header labels.
  80. * @param array $data The data array.
  81. * @param string $file_name The name of the file which contains exported data.
  82. * @return string mixed Returns a message (string) if an error occurred.
  83. */
  84. public function export_csv($header, $data, $file_name = 'export.csv')
  85. {
  86. $archive_path = api_get_path(SYS_ARCHIVE_PATH);
  87. $archive_url = api_get_path(WEB_CODE_PATH).'course_info/download.php?archive_path=&archive=';
  88. $message = '';
  89. if (!$open = fopen($archive_path.$file_name, 'w+')) {
  90. $message = get_lang('noOpen');
  91. } else {
  92. $info = '';
  93. foreach ($header as $value) {
  94. $info .= $value.';';
  95. }
  96. $info .= "\r\n";
  97. foreach ($data as $row) {
  98. foreach ($row as $value) {
  99. $info .= $value.';';
  100. }
  101. $info .= "\r\n";
  102. }
  103. fwrite($open, $info);
  104. fclose($open);
  105. @chmod($file_name, api_get_permissions_for_new_files());
  106. header("Location:".$archive_url.$file_name);
  107. }
  108. return $message;
  109. }
  110. /**
  111. * Gets the connections to a course as an array of login and logout time
  112. *
  113. * @param int $userId User id
  114. * @param array $courseInfo
  115. * @param int $sessionId Session id (optional, default = 0)
  116. * @return array Connections
  117. */
  118. public static function get_connections_to_course(
  119. $userId,
  120. $courseInfo,
  121. $sessionId = 0
  122. ) {
  123. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  124. // protect data
  125. $userId = (int) $userId;
  126. $courseId = (int) $courseInfo['real_id'];
  127. $sessionId = (int) $sessionId;
  128. $sessionCondition = api_get_session_condition($sessionId);
  129. $sql = 'SELECT login_course_date, logout_course_date
  130. FROM ' . $table.'
  131. WHERE
  132. user_id = '.$userId.' AND
  133. c_id = '.$courseId.'
  134. '.$sessionCondition.'
  135. ORDER BY login_course_date ASC';
  136. $rs = Database::query($sql);
  137. $connections = array();
  138. while ($row = Database::fetch_array($rs)) {
  139. $connections[] = array(
  140. 'login' => $row['login_course_date'],
  141. 'logout' => $row['logout_course_date']
  142. );
  143. }
  144. return $connections;
  145. }
  146. /**
  147. * @param $user_id
  148. * @param $course_list
  149. * @param int $session_id
  150. * @return array|bool
  151. */
  152. public static function get_connections_from_course_list(
  153. $user_id,
  154. $course_list,
  155. $session_id = 0
  156. ) {
  157. // Database table definitions
  158. $tbl_track_course = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  159. if (empty($course_list)) {
  160. return false;
  161. }
  162. // protect data
  163. $user_id = intval($user_id);
  164. $session_id = intval($session_id);
  165. $new_course_list = array();
  166. foreach ($course_list as $course_item) {
  167. $courseInfo = api_get_course_info($course_item['code']);
  168. $courseId = $courseInfo['real_id'];
  169. $new_course_list[] = '"'.$courseId.'"';
  170. }
  171. $course_list = implode(', ', $new_course_list);
  172. if (empty($course_list)) {
  173. return false;
  174. }
  175. $sql = 'SELECT login_course_date, logout_course_date, c_id
  176. FROM ' . $tbl_track_course.'
  177. WHERE
  178. user_id = '.$user_id.' AND
  179. c_id IN ('.$course_list.') AND
  180. session_id = '.$session_id.'
  181. ORDER BY login_course_date ASC';
  182. $rs = Database::query($sql);
  183. $connections = array();
  184. while ($row = Database::fetch_array($rs)) {
  185. $timestamp_login_date = api_strtotime($row['login_course_date'], 'UTC');
  186. $timestamp_logout_date = api_strtotime($row['logout_course_date'], 'UTC');
  187. $connections[] = array(
  188. 'login' => $timestamp_login_date,
  189. 'logout' => $timestamp_logout_date,
  190. 'c_id' => $row['c_id']
  191. );
  192. }
  193. return $connections;
  194. }
  195. /**
  196. * Creates a small table in the last column of the table with the user overview
  197. *
  198. * @param integer $user_id the id of the user
  199. * @param array $url_params additional url parameters
  200. * @param array $row the row information (the other columns)
  201. * @return string html code
  202. */
  203. public static function course_info_tracking_filter($user_id, $url_params, $row)
  204. {
  205. // the table header
  206. $return = '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  207. // database table definition
  208. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  209. // getting all the courses of the user
  210. $sql = "SELECT * FROM $tbl_course_user
  211. WHERE
  212. user_id = '".intval($user_id)."' AND
  213. relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  214. $result = Database::query($sql);
  215. while ($row = Database::fetch_array($result)) {
  216. $courseInfo = api_get_course_info_by_id($row['c_id']);
  217. if (empty($courseInfo)) {
  218. continue;
  219. }
  220. $courseCode = $courseInfo['code'];
  221. $courseId = $courseInfo['real_id'];
  222. $return .= '<tr>';
  223. // course code
  224. $return .= ' <td width="157px" >'.cut($courseCode, 20, true).'</td>';
  225. // time spent in the course
  226. $return .= ' <td><div>'.api_time_to_hms(Tracking::get_time_spent_on_the_course($user_id, $courseId)).'</div></td>';
  227. // student progress in course
  228. $return .= ' <td><div>'.round(Tracking::get_avg_student_progress($user_id, $courseCode), 2).'</div></td>';
  229. // student score
  230. $avg_score = Tracking::get_avg_student_score($user_id, $courseCode);
  231. if (is_numeric($avg_score)) {
  232. $avg_score = round($avg_score, 2);
  233. } else {
  234. $$avg_score = '-';
  235. }
  236. $return .= ' <td><div>'.$avg_score.'</div></td>';
  237. // student tes score
  238. //$return .= ' <td><div style="width:40px">'.round(Tracking::get_avg_student_exercise_score ($user_id, $courseCode),2).'%</div></td>';
  239. // student messages
  240. $return .= ' <td><div>'.Tracking::count_student_messages($user_id, $courseCode).'</div></td>';
  241. // student assignments
  242. $return .= ' <td><div>'.Tracking::count_student_assignments($user_id, $courseCode).'</div></td>';
  243. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  244. $exercises_results = self::exercises_results($user_id, $courseCode);
  245. $return .= ' <td width="105px"><div>'.(is_null($exercises_results['percentage']) ? '' : $exercises_results['score_obtained'].'/'.$exercises_results['score_possible'].' ( '.$exercises_results['percentage'].'% )').'</div></td>';
  246. $return .= ' <td><div>'.$exercises_results['questions_answered'].'</div></td>';
  247. $return .= ' <td><div>'.Tracking::get_last_connection_date_on_the_course($user_id, $courseInfo).'</div></td>';
  248. $return .= '<tr>';
  249. }
  250. $return .= '</table>';
  251. return $return;
  252. }
  253. /**
  254. * Display a sortable table that contains an overview off all the
  255. * reporting progress of all users and all courses the user is subscribed to
  256. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  257. * @version Dokeos 1.8.6
  258. * @since October 2008
  259. */
  260. public static function display_tracking_user_overview()
  261. {
  262. self::display_user_overview_export_options();
  263. $t_head = '<table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  264. $t_head .= '<tr>';
  265. $t_head .= '<th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>';
  266. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  267. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  268. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  269. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  270. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  271. $t_head .= '<th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  272. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  273. $t_head .= '<th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  274. $t_head .= '</tr></table>';
  275. $addparams = array('view' => 'admin', 'display' => 'user');
  276. $table = new SortableTable(
  277. 'tracking_user_overview',
  278. array('MySpace', 'get_number_of_users_tracking_overview'),
  279. array('MySpace', 'get_user_data_tracking_overview'),
  280. 0
  281. );
  282. $table->additional_parameters = $addparams;
  283. $table->set_header(0, get_lang('OfficialCode'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  284. if (api_is_western_name_order()) {
  285. $table->set_header(1, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  286. $table->set_header(2, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  287. } else {
  288. $table->set_header(1, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  289. $table->set_header(2, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  290. }
  291. $table->set_header(3, get_lang('LoginName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  292. $table->set_header(4, $t_head, false, array('style' => 'width:90%;border:0;padding:0;font-size:7.5pt;'), array('style' => 'width:90%;padding:0;font-size:7.5pt;'));
  293. $table->set_column_filter(4, array('MySpace', 'course_info_tracking_filter'));
  294. $table->display();
  295. }
  296. /**
  297. * @param $export_csv
  298. */
  299. public static function display_tracking_coach_overview($export_csv)
  300. {
  301. if ($export_csv) {
  302. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  303. } else {
  304. $is_western_name_order = api_is_western_name_order();
  305. }
  306. $sort_by_first_name = api_sort_by_first_name();
  307. $tracking_column = isset($_GET['tracking_list_coaches_column']) ? $_GET['tracking_list_coaches_column'] : ($is_western_name_order xor $sort_by_first_name) ? 1 : 0;
  308. $tracking_direction = (isset($_GET['tracking_list_coaches_direction']) && in_array(strtoupper($_GET['tracking_list_coaches_direction']), array('ASC', 'DESC', 'ASCENDING', 'DESCENDING', '0', '1'))) ? $_GET['tracking_list_coaches_direction'] : 'DESC';
  309. // Prepare array for column order - when impossible, use some of user names.
  310. if ($is_western_name_order) {
  311. $order = array(
  312. 0 => 'firstname',
  313. 1 => 'lastname',
  314. 2 => ($sort_by_first_name ? 'firstname' : 'lastname'),
  315. 3 => 'login_date',
  316. 4 => ($sort_by_first_name ? 'firstname' : 'lastname'),
  317. 5 => ($sort_by_first_name ? 'firstname' : 'lastname'),
  318. );
  319. } else {
  320. $order = array(
  321. 0 => 'lastname',
  322. 1 => 'firstname',
  323. 2 => ($sort_by_first_name ? 'firstname' : 'lastname'),
  324. 3 => 'login_date',
  325. 4 => ($sort_by_first_name ? 'firstname' : 'lastname'),
  326. 5 => ($sort_by_first_name ? 'firstname' : 'lastname'),
  327. );
  328. }
  329. $table = new SortableTable(
  330. 'tracking_list_coaches_myspace',
  331. array('MySpace', 'count_coaches'),
  332. null,
  333. ($is_western_name_order xor $sort_by_first_name) ? 1 : 0
  334. );
  335. $parameters['view'] = 'admin';
  336. $table->set_additional_parameters($parameters);
  337. if ($is_western_name_order) {
  338. $table->set_header(0, get_lang('FirstName'), true);
  339. $table->set_header(1, get_lang('LastName'), true);
  340. } else {
  341. $table->set_header(0, get_lang('LastName'), true);
  342. $table->set_header(1, get_lang('FirstName'), true);
  343. }
  344. $table->set_header(2, get_lang('TimeSpentOnThePlatform'), false);
  345. $table->set_header(3, get_lang('LastConnexion'), false);
  346. $table->set_header(4, get_lang('NbStudents'), false);
  347. $table->set_header(5, get_lang('CountCours'), false);
  348. $table->set_header(6, get_lang('NumberOfSessions'), false);
  349. $table->set_header(7, get_lang('Sessions'), false);
  350. if ($is_western_name_order) {
  351. $csv_header[] = array(
  352. get_lang('FirstName'),
  353. get_lang('LastName'),
  354. get_lang('TimeSpentOnThePlatform'),
  355. get_lang('LastConnexion'),
  356. get_lang('NbStudents'),
  357. get_lang('CountCours'),
  358. get_lang('NumberOfSessions')
  359. );
  360. } else {
  361. $csv_header[] = array(
  362. get_lang('LastName'),
  363. get_lang('FirstName'),
  364. get_lang('TimeSpentOnThePlatform'),
  365. get_lang('LastConnexion'),
  366. get_lang('NbStudents'),
  367. get_lang('CountCours'),
  368. get_lang('NumberOfSessions')
  369. );
  370. }
  371. $tbl_track_login = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  372. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  373. $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  374. $tbl_sessions = Database::get_main_table(TABLE_MAIN_SESSION);
  375. $sqlCoachs = "SELECT DISTINCT
  376. scu.user_id as id_coach,
  377. u.id as user_id,
  378. lastname,
  379. firstname,
  380. MAX(login_date) as login_date
  381. FROM $tbl_user u, $tbl_session_course_user scu, $tbl_track_login
  382. WHERE
  383. scu.user_id = u.id AND scu.status=2 AND login_user_id=u.id
  384. GROUP BY user_id ";
  385. if (api_is_multiple_url_enabled()) {
  386. $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  387. $access_url_id = api_get_current_access_url_id();
  388. if ($access_url_id != -1) {
  389. $sqlCoachs = "SELECT DISTINCT
  390. scu.user_id as id_coach,
  391. u.id as user_id,
  392. lastname,
  393. firstname,
  394. MAX(login_date) as login_date
  395. FROM $tbl_user u,
  396. $tbl_session_course_user scu,
  397. $tbl_track_login ,
  398. $tbl_session_rel_access_url session_rel_url
  399. WHERE
  400. scu.user_id = u.id AND
  401. scu.status = 2 AND
  402. login_user_id = u.id AND
  403. access_url_id = $access_url_id AND
  404. session_rel_url.session_id = scu.session_id
  405. GROUP BY u.id";
  406. }
  407. }
  408. if (!empty($order[$tracking_column])) {
  409. $sqlCoachs .= " ORDER BY ".$order[$tracking_column]." ".$tracking_direction;
  410. }
  411. $result_coaches = Database::query($sqlCoachs);
  412. $global_coaches = array();
  413. while ($coach = Database::fetch_array($result_coaches)) {
  414. $global_coaches[$coach['user_id']] = $coach;
  415. }
  416. $sql_session_coach = "SELECT session.id_coach, u.id as user_id, lastname, firstname, MAX(login_date) as login_date
  417. FROM $tbl_user u , $tbl_sessions as session, $tbl_track_login
  418. WHERE id_coach = u.id AND login_user_id = u.id
  419. GROUP BY u.id
  420. ORDER BY login_date $tracking_direction";
  421. if (api_is_multiple_url_enabled()) {
  422. $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  423. $access_url_id = api_get_current_access_url_id();
  424. if ($access_url_id != -1) {
  425. $sql_session_coach = "SELECT session.id_coach, u.id as user_id, lastname, firstname, MAX(login_date) as login_date
  426. FROM $tbl_user u , $tbl_sessions as session, $tbl_track_login , $tbl_session_rel_access_url as session_rel_url
  427. WHERE
  428. id_coach = u.id AND
  429. login_user_id = u.id AND
  430. access_url_id = $access_url_id AND
  431. session_rel_url.session_id = session.id
  432. GROUP BY u.id
  433. ORDER BY login_date $tracking_direction";
  434. }
  435. }
  436. $result_sessions_coach = Database::query($sql_session_coach);
  437. //$total_no_coaches += Database::num_rows($result_sessions_coach);
  438. while ($coach = Database::fetch_array($result_sessions_coach)) {
  439. $global_coaches[$coach['user_id']] = $coach;
  440. }
  441. $all_datas = array();
  442. foreach ($global_coaches as $id_coach => $coaches) {
  443. $time_on_platform = api_time_to_hms(
  444. Tracking::get_time_spent_on_the_platform($coaches['user_id'])
  445. );
  446. $last_connection = Tracking::get_last_connection_date(
  447. $coaches['user_id']
  448. );
  449. $nb_students = count(
  450. Tracking::get_student_followed_by_coach($coaches['user_id'])
  451. );
  452. $nb_courses = count(
  453. Tracking::get_courses_followed_by_coach($coaches['user_id'])
  454. );
  455. $nb_sessions = count(
  456. Tracking::get_sessions_coached_by_user($coaches['user_id'])
  457. );
  458. $table_row = array();
  459. if ($is_western_name_order) {
  460. $table_row[] = $coaches['firstname'];
  461. $table_row[] = $coaches['lastname'];
  462. } else {
  463. $table_row[] = $coaches['lastname'];
  464. $table_row[] = $coaches['firstname'];
  465. }
  466. $table_row[] = $time_on_platform;
  467. $table_row[] = $last_connection;
  468. $table_row[] = $nb_students;
  469. $table_row[] = $nb_courses;
  470. $table_row[] = $nb_sessions;
  471. $table_row[] = '<a href="session.php?id_coach='.$coaches['user_id'].'">
  472. '.Display::return_icon('2rightarrow.png').'
  473. </a>';
  474. $all_datas[] = $table_row;
  475. if ($is_western_name_order) {
  476. $csv_content[] = array(
  477. api_html_entity_decode($coaches['firstname'], ENT_QUOTES),
  478. api_html_entity_decode($coaches['lastname'], ENT_QUOTES),
  479. $time_on_platform,
  480. $last_connection,
  481. $nb_students,
  482. $nb_courses,
  483. $nb_sessions
  484. );
  485. } else {
  486. $csv_content[] = array(
  487. api_html_entity_decode($coaches['lastname'], ENT_QUOTES),
  488. api_html_entity_decode($coaches['firstname'], ENT_QUOTES),
  489. $time_on_platform,
  490. $last_connection,
  491. $nb_students,
  492. $nb_courses,
  493. $nb_sessions
  494. );
  495. }
  496. }
  497. if ($tracking_column != 3) {
  498. if ($tracking_direction == 'DESC') {
  499. usort($all_datas, array('MySpace', 'rsort_users'));
  500. } else {
  501. usort($all_datas, array('MySpace', 'sort_users'));
  502. }
  503. }
  504. if ($export_csv && $tracking_column != 3) {
  505. usort($csv_content, 'sort_users');
  506. }
  507. if ($export_csv) {
  508. $csv_content = array_merge($csv_header, $csv_content);
  509. }
  510. foreach ($all_datas as $row) {
  511. $table -> addRow($row, 'align="right"');
  512. }
  513. $table -> display();
  514. }
  515. /**
  516. * @return mixed
  517. */
  518. public static function count_coaches()
  519. {
  520. global $total_no_coaches;
  521. return $total_no_coaches;
  522. }
  523. public static function sort_users($a, $b)
  524. {
  525. return api_strcmp(trim(api_strtolower($a[$_SESSION['tracking_column']])), trim(api_strtolower($b[$_SESSION['tracking_column']])));
  526. }
  527. public static function rsort_users($a, $b)
  528. {
  529. return api_strcmp(trim(api_strtolower($b[$_SESSION['tracking_column']])), trim(api_strtolower($a[$_SESSION['tracking_column']])));
  530. }
  531. /**
  532. * Display a sortable table that contains an overview off all the progress of the user in a session
  533. * @author César Perales <cesar.perales@beeznest.com>, Beeznest Team
  534. */
  535. public static function display_tracking_lp_progress_overview(
  536. $sessionId = '',
  537. $courseId = '',
  538. $date_from,
  539. $date_to
  540. ) {
  541. $course = api_get_course_info_by_id($courseId);
  542. /**
  543. * Column name
  544. * The order is important you need to check the $column variable in the model.ajax.php file
  545. */
  546. $columns = array(
  547. get_lang('Username'),
  548. get_lang('FirstName'),
  549. get_lang('LastName'),
  550. );
  551. //add lessons of course
  552. $lessons = LearnpathList::get_course_lessons($course['code'], $sessionId);
  553. //create columns array
  554. foreach ($lessons as $lesson_id => $lesson) {
  555. $columns[] = $lesson['name'];
  556. }
  557. $columns[] = get_lang('Total');
  558. /**
  559. * Column config
  560. */
  561. $column_model = array(
  562. array(
  563. 'name' => 'username',
  564. 'index' => 'username',
  565. 'align' => 'left',
  566. 'search' => 'true',
  567. 'wrap_cell' => "true",
  568. ),
  569. array(
  570. 'name' => 'firstname',
  571. 'index' => 'firstname',
  572. 'align' => 'left',
  573. 'search' => 'true',
  574. ),
  575. array(
  576. 'name' => 'lastname',
  577. 'index' => 'lastname',
  578. 'align' => 'left',
  579. 'search' => 'true',
  580. ),
  581. );
  582. // Get dinamic column names
  583. foreach ($lessons as $lesson_id => $lesson) {
  584. $column_model[] = array(
  585. 'name' => $lesson['id'],
  586. 'index' => $lesson['id'],
  587. 'align' => 'left',
  588. 'search' => 'true',
  589. );
  590. }
  591. $column_model[] = array(
  592. 'name' => 'total',
  593. 'index' => 'total',
  594. 'align' => 'left',
  595. 'search' => 'true',
  596. );
  597. $action_links = '';
  598. // jqgrid will use this URL to do the selects
  599. $url = api_get_path(WEB_AJAX_PATH).'model.ajax.php?a=get_session_lp_progress&session_id='.$sessionId.'&course_id='.$courseId.'&date_to='.$date_to.'&date_from='.$date_from;
  600. //Table Id
  601. $tableId = 'lpProgress';
  602. //Autowidth
  603. $extra_params['autowidth'] = 'true';
  604. //height auto
  605. $extra_params['height'] = 'auto';
  606. $table = Display::grid_js(
  607. $tableId,
  608. $url,
  609. $columns,
  610. $column_model,
  611. $extra_params,
  612. array(),
  613. $action_links,
  614. true
  615. );
  616. $return = '<script>$(function() {'.$table.
  617. 'jQuery("#'.$tableId.'").jqGrid("navGrid","#'.$tableId.'_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
  618. jQuery("#'.$tableId.'").jqGrid("navButtonAdd","#'.$tableId.'_pager",{
  619. caption:"",
  620. title:"' . get_lang('ExportExcel').'",
  621. onClickButton : function () {
  622. jQuery("#'.$tableId.'").jqGrid("excelExport",{"url":"'.$url.'&export_format=xls"});
  623. }
  624. });
  625. });</script>';
  626. $return .= Display::grid_html($tableId);
  627. return $return;
  628. }
  629. /**
  630. * Display a sortable table that contains an overview off all the progress of the user in a session
  631. * @param int $sessionId The session ID
  632. * @param int $courseId The course ID
  633. * @param int $exerciseId The quiz ID
  634. * @param $date_from
  635. * @param $date_to
  636. * @return string HTML array of results formatted for gridJS
  637. * @author César Perales <cesar.perales@beeznest.com>, Beeznest Team
  638. */
  639. public static function display_tracking_exercise_progress_overview(
  640. $sessionId = 0,
  641. $courseId = 0,
  642. $exerciseId = 0,
  643. $date_from = null,
  644. $date_to = null
  645. ) {
  646. $date_from = Security::remove_XSS($date_from);
  647. $date_to = Security::remove_XSS($date_to);
  648. /**
  649. * Column names
  650. * The column order is important. Check $column variable in the main/inc/ajax/model.ajax.php file
  651. */
  652. $columns = array(
  653. get_lang('Session'),
  654. get_lang('ExerciseId'),
  655. get_lang('ExerciseName'),
  656. get_lang('Username'),
  657. get_lang('LastName'),
  658. get_lang('FirstName'),
  659. get_lang('Time'),
  660. get_lang('QuestionId'),
  661. get_lang('QuestionTitle'),
  662. get_lang('WorkDescription'),
  663. get_lang('Answer'),
  664. get_lang('Correct')
  665. );
  666. /**
  667. * Column config
  668. */
  669. $column_model = array(
  670. array('name'=>'session', 'index'=>'session', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  671. array('name'=>'exercise_id', 'index'=>'exercise_id', 'align'=>'left', 'search' => 'true'),
  672. array('name'=>'quiz_title', 'index'=>'quiz_title', 'align'=>'left', 'search' => 'true'),
  673. array('name'=>'username', 'index'=>'username', 'align'=>'left', 'search' => 'true'),
  674. array('name'=>'lastname', 'index'=>'lastname', 'align'=>'left', 'search' => 'true'),
  675. array('name'=>'firstname', 'index'=>'firstname', 'align'=>'left', 'search' => 'true'),
  676. array('name'=>'time', 'index'=>'time', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  677. array('name'=>'question_id', 'index'=>'question_id', 'align'=>'left', 'search' => 'true'),
  678. array('name'=>'question', 'index'=>'question', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  679. array('name'=>'description', 'index'=>'description', 'align'=>'left', 'width' => '550', 'search' => 'true', 'wrap_cell' => "true"),
  680. array('name'=>'answer', 'index'=>'answer', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  681. array('name'=>'correct', 'index'=>'correct', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  682. );
  683. //get dynamic column names
  684. // jqgrid will use this URL to do the selects
  685. $url = api_get_path(WEB_AJAX_PATH).'model.ajax.php?a=get_exercise_progress&session_id='.$sessionId.'&course_id='.$courseId.'&exercise_id='.$exerciseId.'&date_to='.$date_to.'&date_from='.$date_from;
  686. // Autowidth
  687. $extra_params['autowidth'] = 'true';
  688. // height auto
  689. $extra_params['height'] = 'auto';
  690. $tableId = 'exerciseProgressOverview';
  691. $table = Display::grid_js(
  692. $tableId,
  693. $url,
  694. $columns,
  695. $column_model,
  696. $extra_params,
  697. array(),
  698. '',
  699. true
  700. );
  701. $return = '<script>$(function() {'.$table.
  702. 'jQuery("#'.$tableId.'").jqGrid("navGrid","#'.$tableId.'_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
  703. jQuery("#'.$tableId.'").jqGrid("navButtonAdd","#'.$tableId.'_pager",{
  704. caption:"",
  705. title:"' . get_lang('ExportExcel').'",
  706. onClickButton : function () {
  707. jQuery("#'.$tableId.'").jqGrid("excelExport",{"url":"'.$url.'&export_format=xls"});
  708. }
  709. });
  710. });</script>';
  711. $return .= Display::grid_html($tableId);
  712. return $return;
  713. }
  714. /**
  715. * Displays a form with all the additionally defined user fields of the profile
  716. * and give you the opportunity to include these in the CSV export
  717. *
  718. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  719. * @version 1.8.6
  720. * @since November 2008
  721. */
  722. public static function display_user_overview_export_options()
  723. {
  724. $message = '';
  725. $defaults = [];
  726. // include the user manager and formvalidator library
  727. if (isset($_GET['export']) && $_GET['export'] == 'options') {
  728. // get all the defined extra fields
  729. $extrafields = UserManager::get_extra_fields(
  730. 0,
  731. 50,
  732. 5,
  733. 'ASC',
  734. false,
  735. 1
  736. );
  737. // creating the form with all the defined extra fields
  738. $form = new FormValidator(
  739. 'exportextrafields',
  740. 'post',
  741. api_get_self()."?view=".Security::remove_XSS($_GET['view']).'&display='.Security::remove_XSS($_GET['display']).'&export='.Security::remove_XSS($_GET['export'])
  742. );
  743. if (is_array($extrafields) && count($extrafields) > 0) {
  744. foreach ($extrafields as $key => $extra) {
  745. $form->addElement('checkbox', 'extra_export_field'.$extra[0], '', $extra[3]);
  746. }
  747. $form->addButtonSave(get_lang('Ok'), 'submit');
  748. // setting the default values for the form that contains all the extra fields
  749. if (is_array($_SESSION['additional_export_fields'])) {
  750. foreach ($_SESSION['additional_export_fields'] as $key => $value) {
  751. $defaults['extra_export_field'.$value] = 1;
  752. }
  753. }
  754. $form->setDefaults($defaults);
  755. } else {
  756. $form->addElement('html', Display::return_message(get_lang('ThereAreNotExtrafieldsAvailable'), 'warning'));
  757. }
  758. if ($form->validate()) {
  759. // exporting the form values
  760. $values = $form->exportValues();
  761. // re-initialising the session that contains the additional fields that need to be exported
  762. $_SESSION['additional_export_fields'] = array();
  763. // adding the fields that are checked to the session
  764. $message = '';
  765. foreach ($values as $field_ids => $value) {
  766. if ($value == 1 && strstr($field_ids, 'extra_export_field')) {
  767. $_SESSION['additional_export_fields'][] = str_replace('extra_export_field', '', $field_ids);
  768. }
  769. }
  770. // adding the fields that will be also exported to a message string
  771. if (is_array($_SESSION['additional_export_fields'])) {
  772. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  773. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  774. }
  775. }
  776. // Displaying a feedback message
  777. if (!empty($_SESSION['additional_export_fields'])) {
  778. echo Display::return_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', 'confirm', false);
  779. } else {
  780. echo Display::return_message(
  781. get_lang('NoAdditionalFieldsWillBeExported'),
  782. 'confirm',
  783. false
  784. );
  785. }
  786. } else {
  787. $form->display();
  788. }
  789. } else {
  790. if (!empty($_SESSION['additional_export_fields'])) {
  791. // get all the defined extra fields
  792. $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  793. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  794. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  795. }
  796. echo Display::return_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', 'normal', false);
  797. }
  798. }
  799. }
  800. /**
  801. * Display a sortable table that contains an overview of all the reporting progress of all courses
  802. */
  803. public static function display_tracking_course_overview()
  804. {
  805. $t_head = '<table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  806. $t_head .= '<tr>';
  807. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  808. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  809. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  810. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  811. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  812. $t_head .= '<th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  813. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  814. $t_head .= '<th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  815. $t_head .= '</tr></table>';
  816. $addparams = array('view' => 'admin', 'display' => 'courseoverview');
  817. $table = new SortableTable(
  818. 'tracking_session_overview',
  819. array('MySpace', 'get_total_number_courses'),
  820. array('MySpace', 'get_course_data_tracking_overview'),
  821. 1
  822. );
  823. $table->additional_parameters = $addparams;
  824. $table->set_header(0, '', false, null, array('style' => 'display: none'));
  825. $table->set_header(1, get_lang('Course'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  826. $table->set_header(2, $t_head, false, array('style' => 'width:90%;border:0;padding:0;font-size:7.5pt;'), array('style' => 'width:90%;padding:0;font-size:7.5pt;'));
  827. $table->set_column_filter(2, array('MySpace', 'course_tracking_filter'));
  828. $table->display();
  829. }
  830. /**
  831. * Get the total number of courses
  832. *
  833. * @return integer Total number of courses
  834. */
  835. public static function get_total_number_courses()
  836. {
  837. return CourseManager::count_courses(api_get_current_access_url_id());
  838. }
  839. /**
  840. * Get data for the courses
  841. *
  842. * @param int $from Inferior limit
  843. * @param int $numberItems Number of items to select
  844. * @param string $column Column to order on
  845. * @param string $direction Order direction
  846. * @return array Results
  847. */
  848. public static function get_course_data_tracking_overview(
  849. $from,
  850. $numberItems,
  851. $column,
  852. $direction
  853. ) {
  854. $courses = CourseManager::get_courses_list(
  855. $from,
  856. $numberItems,
  857. $column,
  858. $direction,
  859. -1,
  860. '',
  861. api_get_current_access_url_id()
  862. );
  863. $list = [];
  864. foreach ($courses as $course) {
  865. $list[] = [
  866. '0' => $course['code'],
  867. 'col0' => $course['code'],
  868. '1' => $course['title'],
  869. 'col1' => $course['title']
  870. ];
  871. }
  872. return $list;
  873. }
  874. /**
  875. * Fills in course reporting data
  876. *
  877. * @param integer course code
  878. * @param array $url_params additional url parameters
  879. * @param array $row the row information (the other columns)
  880. * @return string html code
  881. */
  882. public static function course_tracking_filter($course_code, $url_params, $row)
  883. {
  884. $course_code = $row[0];
  885. $courseInfo = api_get_course_info($course_code);
  886. $courseId = $courseInfo['real_id'];
  887. // the table header
  888. $return = '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  889. // database table definition
  890. $tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  891. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  892. // getting all the courses of the user
  893. $sql = "SELECT *
  894. FROM $tbl_user AS u
  895. INNER JOIN $tbl_course_rel_user AS cu
  896. ON cu.user_id = u.user_id
  897. WHERE cu.c_id = '".$courseId."'";
  898. $result = Database::query($sql);
  899. $time_spent = 0;
  900. $progress = 0;
  901. $nb_progress_lp = 0;
  902. $score = 0;
  903. $nb_score_lp = 0;
  904. $nb_messages = 0;
  905. $nb_assignments = 0;
  906. $last_login_date = false;
  907. $total_score_obtained = 0;
  908. $total_score_possible = 0;
  909. $total_questions_answered = 0;
  910. while ($row = Database::fetch_object($result)) {
  911. // get time spent in the course and session
  912. $time_spent += Tracking::get_time_spent_on_the_course(
  913. $row->user_id,
  914. $courseInfo['real_id']
  915. );
  916. $progress_tmp = Tracking::get_avg_student_progress(
  917. $row->user_id,
  918. $course_code,
  919. array(),
  920. null,
  921. true
  922. );
  923. $progress += $progress_tmp[0];
  924. $nb_progress_lp += $progress_tmp[1];
  925. $score_tmp = Tracking::get_avg_student_score(
  926. $row->user_id,
  927. $course_code,
  928. array(),
  929. null,
  930. true
  931. );
  932. if (is_array($score_tmp)) {
  933. $score += $score_tmp[0];
  934. $nb_score_lp += $score_tmp[1];
  935. }
  936. $nb_messages += Tracking::count_student_messages(
  937. $row->user_id,
  938. $course_code
  939. );
  940. $nb_assignments += Tracking::count_student_assignments(
  941. $row->user_id,
  942. $course_code
  943. );
  944. $last_login_date_tmp = Tracking::get_last_connection_date_on_the_course(
  945. $row->user_id,
  946. $courseInfo,
  947. null,
  948. false
  949. );
  950. if ($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned
  951. $last_login_date = $last_login_date_tmp;
  952. } elseif ($last_login_date_tmp != false && $last_login_date != false) { // TODO: Repeated previous condition. To be cleaned.
  953. // Find the max and assign it to first_login_date
  954. if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  955. $last_login_date = $last_login_date_tmp;
  956. }
  957. }
  958. $exercise_results_tmp = self::exercises_results($row->user_id, $course_code);
  959. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  960. $total_score_possible += $exercise_results_tmp['score_possible'];
  961. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  962. }
  963. if ($nb_progress_lp > 0) {
  964. $avg_progress = round($progress / $nb_progress_lp, 2);
  965. } else {
  966. $avg_progress = 0;
  967. }
  968. if ($nb_score_lp > 0) {
  969. $avg_score = round($score / $nb_score_lp, 2);
  970. } else {
  971. $avg_score = '-';
  972. }
  973. if ($last_login_date) {
  974. $last_login_date = api_convert_and_format_date(
  975. $last_login_date,
  976. DATE_FORMAT_SHORT,
  977. date_default_timezone_get()
  978. );
  979. } else {
  980. $last_login_date = '-';
  981. }
  982. if ($total_score_possible > 0) {
  983. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  984. } else {
  985. $total_score_percentage = 0;
  986. }
  987. if ($total_score_percentage > 0) {
  988. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  989. } else {
  990. $total_score = '-';
  991. }
  992. $return .= '<tr>';
  993. // time spent in the course
  994. $return .= ' <td style="width:164px;">'.api_time_to_hms($time_spent).'</td>';
  995. // student progress in course
  996. $return .= ' <td>'.$avg_progress.'</td>';
  997. // student score
  998. $return .= ' <td>'.$avg_score.'</td>';
  999. // student messages
  1000. $return .= ' <td>'.$nb_messages.'</td>';
  1001. // student assignments
  1002. $return .= ' <td>'.$nb_assignments.'</td>';
  1003. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  1004. $return .= '<td width="105px;">'.$total_score.'</td>';
  1005. $return .= '<td>'.$total_questions_answered.'</td>';
  1006. // last connection
  1007. $return .= ' <td>'.$last_login_date.'</td>';
  1008. $return .= '</tr>';
  1009. $return .= '</table>';
  1010. return $return;
  1011. }
  1012. /**
  1013. * This function exports the table that we see in display_tracking_course_overview()
  1014. *
  1015. */
  1016. public static function export_tracking_course_overview()
  1017. {
  1018. // database table definition
  1019. $tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1020. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1021. // the values of the sortable table
  1022. if ($_GET['tracking_course_overview_page_nr']) {
  1023. $from = $_GET['tracking_course_overview_page_nr'];
  1024. } else {
  1025. $from = 0;
  1026. }
  1027. if ($_GET['tracking_course_overview_column']) {
  1028. $orderby = $_GET['tracking_course_overview_column'];
  1029. } else {
  1030. $orderby = 0;
  1031. }
  1032. if ($_GET['tracking_course_overview_direction']) {
  1033. $direction = $_GET['tracking_course_overview_direction'];
  1034. } else {
  1035. $direction = 'ASC';
  1036. }
  1037. $course_data = self::get_course_data_tracking_overview(
  1038. $from,
  1039. 1000,
  1040. $orderby,
  1041. $direction
  1042. );
  1043. $csv_content = array();
  1044. // the first line of the csv file with the column headers
  1045. $csv_row = array();
  1046. $csv_row[] = get_lang('Course');
  1047. $csv_row[] = get_lang('AvgTimeSpentInTheCourse');
  1048. $csv_row[] = get_lang('AvgStudentsProgress');
  1049. $csv_row[] = get_lang('AvgCourseScore');
  1050. $csv_row[] = get_lang('TotalNumberOfMessages');
  1051. $csv_row[] = get_lang('TotalNumberOfAssignments');
  1052. $csv_row[] = get_lang('TotalExercisesScoreObtained');
  1053. $csv_row[] = get_lang('TotalExercisesScorePossible');
  1054. $csv_row[] = get_lang('TotalExercisesAnswered');
  1055. $csv_row[] = get_lang('TotalExercisesScorePercentage');
  1056. $csv_row[] = get_lang('LatestLogin');
  1057. $csv_content[] = $csv_row;
  1058. // the other lines (the data)
  1059. foreach ($course_data as $key => $course) {
  1060. $course_code = $course[0];
  1061. $courseInfo = api_get_course_info($course_code);
  1062. $course_title = $courseInfo['title'];
  1063. $courseId = $courseInfo['real_id'];
  1064. $csv_row = array();
  1065. $csv_row[] = $course_title;
  1066. // getting all the courses of the session
  1067. $sql = "SELECT *
  1068. FROM $tbl_user AS u
  1069. INNER JOIN $tbl_course_rel_user AS cu
  1070. ON cu.user_id = u.user_id
  1071. WHERE cu.c_id = '".$courseId."'";
  1072. $result = Database::query($sql);
  1073. $time_spent = 0;
  1074. $progress = 0;
  1075. $nb_progress_lp = 0;
  1076. $score = 0;
  1077. $nb_score_lp = 0;
  1078. $nb_messages = 0;
  1079. $nb_assignments = 0;
  1080. $last_login_date = false;
  1081. $total_score_obtained = 0;
  1082. $total_score_possible = 0;
  1083. $total_questions_answered = 0;
  1084. while ($row = Database::fetch_object($result)) {
  1085. // get time spent in the course and session
  1086. $time_spent += Tracking::get_time_spent_on_the_course(
  1087. $row->user_id,
  1088. $courseId
  1089. );
  1090. $progress_tmp = Tracking::get_avg_student_progress(
  1091. $row->user_id,
  1092. $course_code,
  1093. array(),
  1094. null,
  1095. true
  1096. );
  1097. $progress += $progress_tmp[0];
  1098. $nb_progress_lp += $progress_tmp[1];
  1099. $score_tmp = Tracking::get_avg_student_score(
  1100. $row->user_id,
  1101. $course_code,
  1102. array(),
  1103. null,
  1104. true
  1105. );
  1106. if (is_array($score_tmp)) {
  1107. $score += $score_tmp[0];
  1108. $nb_score_lp += $score_tmp[1];
  1109. }
  1110. $nb_messages += Tracking::count_student_messages(
  1111. $row->user_id,
  1112. $course_code
  1113. );
  1114. $nb_assignments += Tracking::count_student_assignments(
  1115. $row->user_id,
  1116. $course_code
  1117. );
  1118. $last_login_date_tmp = Tracking::get_last_connection_date_on_the_course(
  1119. $row->user_id,
  1120. $courseInfo,
  1121. null,
  1122. false
  1123. );
  1124. if ($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned.
  1125. $last_login_date = $last_login_date_tmp;
  1126. } else if ($last_login_date_tmp != false && $last_login_date == false) { // TODO: Repeated previous condition. To be cleaned.
  1127. // Find the max and assign it to first_login_date
  1128. if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  1129. $last_login_date = $last_login_date_tmp;
  1130. }
  1131. }
  1132. $exercise_results_tmp = self::exercises_results($row->user_id, $course_code);
  1133. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  1134. $total_score_possible += $exercise_results_tmp['score_possible'];
  1135. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  1136. }
  1137. if ($nb_progress_lp > 0) {
  1138. $avg_progress = round($progress / $nb_progress_lp, 2);
  1139. } else {
  1140. $avg_progress = 0;
  1141. }
  1142. if ($nb_score_lp > 0) {
  1143. $avg_score = round($score / $nb_score_lp, 2);
  1144. } else {
  1145. $avg_score = '-';
  1146. }
  1147. if ($last_login_date) {
  1148. $last_login_date = api_convert_and_format_date(
  1149. $last_login_date,
  1150. DATE_FORMAT_SHORT,
  1151. date_default_timezone_get()
  1152. );
  1153. } else {
  1154. $last_login_date = '-';
  1155. }
  1156. if ($total_score_possible > 0) {
  1157. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  1158. } else {
  1159. $total_score_percentage = 0;
  1160. }
  1161. // time spent in the course
  1162. $csv_row[] = api_time_to_hms($time_spent);
  1163. // student progress in course
  1164. $csv_row[] = $avg_progress;
  1165. // student score
  1166. $csv_row[] = $avg_score;
  1167. // student messages
  1168. $csv_row[] = $nb_messages;
  1169. // student assignments
  1170. $csv_row[] = $nb_assignments;
  1171. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  1172. $csv_row[] = $total_score_obtained;
  1173. $csv_row[] = $total_score_possible;
  1174. $csv_row[] = $total_questions_answered;
  1175. $csv_row[] = $total_score_percentage;
  1176. // last connection
  1177. $csv_row[] = $last_login_date;
  1178. $csv_content[] = $csv_row;
  1179. }
  1180. Export::arrayToCsv($csv_content, 'reporting_course_overview');
  1181. exit;
  1182. }
  1183. /**
  1184. * Display a sortable table that contains an overview of all the reporting
  1185. * progress of all sessions and all courses the user is subscribed to
  1186. * @author Guillaume Viguier <guillaume@viguierjust.com>
  1187. */
  1188. public static function display_tracking_session_overview()
  1189. {
  1190. $t_head = '<table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  1191. $t_head .= '<tr>';
  1192. $t_head .= '<th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>';
  1193. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  1194. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  1195. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  1196. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  1197. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  1198. $t_head .= '<th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  1199. $t_head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  1200. $t_head .= '<th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  1201. $t_head .= '</tr></table>';
  1202. $addparams = array('view' => 'admin', 'display' => 'sessionoverview');
  1203. $table = new SortableTable(
  1204. 'tracking_session_overview',
  1205. array('MySpace', 'get_total_number_sessions'),
  1206. array('MySpace', 'get_session_data_tracking_overview'),
  1207. 1
  1208. );
  1209. $table->additional_parameters = $addparams;
  1210. $table->set_header(0, '', false, null, array('style' => 'display: none'));
  1211. $table->set_header(1, get_lang('Session'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  1212. $table->set_header(2, $t_head, false, array('style' => 'width:90%;border:0;padding:0;font-size:7.5pt;'), array('style' => 'width:90%;padding:0;font-size:7.5pt;'));
  1213. $table->set_column_filter(2, array('MySpace', 'session_tracking_filter'));
  1214. $table->display();
  1215. }
  1216. /**
  1217. * Get the total number of sessions
  1218. *
  1219. * @return integer Total number of sessions
  1220. */
  1221. public static function get_total_number_sessions()
  1222. {
  1223. return SessionManager::count_sessions(api_get_current_access_url_id());
  1224. }
  1225. /**
  1226. * Get data for the sessions
  1227. *
  1228. * @param int $from Inferior limit
  1229. * @param int $numberItems Number of items to select
  1230. * @param string $column Column to order on
  1231. * @param string $direction Order direction
  1232. * @return array Results
  1233. */
  1234. public static function get_session_data_tracking_overview(
  1235. $from,
  1236. $numberItems,
  1237. $column,
  1238. $direction
  1239. ) {
  1240. $from = (int) $from;
  1241. $numberItems = (int) $numberItems;
  1242. $direction = Database::escape_string($direction);
  1243. $columnName = 'name';
  1244. if ($column === 1) {
  1245. $columnName = 'id';
  1246. }
  1247. $options = [
  1248. 'order' => " $columnName $direction",
  1249. 'limit' => " $from,$numberItems"
  1250. ];
  1251. $sessions = SessionManager::get_sessions_admin($options);
  1252. $list = [];
  1253. foreach ($sessions as $session) {
  1254. $list[] = [
  1255. '0' => $session['id'],
  1256. 'col0' => $session['id'],
  1257. '1' => strip_tags($session['name']),
  1258. 'col1' => strip_tags($session['name'])
  1259. ];
  1260. }
  1261. return $list;
  1262. }
  1263. /**
  1264. * Fills in session reporting data
  1265. *
  1266. * @param int $session_id the id of the user
  1267. * @param array $url_params additonal url parameters
  1268. * @param array $row the row information (the other columns)
  1269. * @return string html code
  1270. */
  1271. public static function session_tracking_filter($session_id, $url_params, $row)
  1272. {
  1273. $session_id = $row[0];
  1274. // the table header
  1275. $return = '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  1276. // database table definition
  1277. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1278. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1279. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1280. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1281. // getting all the courses of the user
  1282. $sql = "SELECT * FROM $tbl_course AS c
  1283. INNER JOIN $tbl_session_rel_course AS sc
  1284. ON sc.c_id = c.id
  1285. WHERE sc.session_id = '".$session_id."'";
  1286. $result = Database::query($sql);
  1287. while ($row = Database::fetch_object($result)) {
  1288. $courseId = $row->c_id;
  1289. $courseInfo = api_get_course_info_by_id($courseId);
  1290. $return .= '<tr>';
  1291. // course code
  1292. $return .= ' <td width="157px" >'.$row->title.'</td>';
  1293. // get the users in the course
  1294. $sql = "SELECT u.user_id
  1295. FROM $tbl_user AS u
  1296. INNER JOIN $tbl_session_rel_course_rel_user AS scu
  1297. ON u.user_id = scu.user_id
  1298. WHERE scu.session_id = '".$session_id."' AND scu.c_id = '".$courseId."'";
  1299. $result_users = Database::query($sql);
  1300. $time_spent = 0;
  1301. $progress = 0;
  1302. $nb_progress_lp = 0;
  1303. $score = 0;
  1304. $nb_score_lp = 0;
  1305. $nb_messages = 0;
  1306. $nb_assignments = 0;
  1307. $last_login_date = false;
  1308. $total_score_obtained = 0;
  1309. $total_score_possible = 0;
  1310. $total_questions_answered = 0;
  1311. while ($row_user = Database::fetch_object($result_users)) {
  1312. // get time spent in the course and session
  1313. $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $courseId, $session_id);
  1314. $progress_tmp = Tracking::get_avg_student_progress($row_user->user_id, $row->code, array(), $session_id, true);
  1315. $progress += $progress_tmp[0];
  1316. $nb_progress_lp += $progress_tmp[1];
  1317. $score_tmp = Tracking::get_avg_student_score($row_user->user_id, $row->code, array(), $session_id, true);
  1318. if (is_array($score_tmp)) {
  1319. $score += $score_tmp[0];
  1320. $nb_score_lp += $score_tmp[1];
  1321. }
  1322. $nb_messages += Tracking::count_student_messages($row_user->user_id, $row->code, $session_id);
  1323. $nb_assignments += Tracking::count_student_assignments($row_user->user_id, $row->code, $session_id);
  1324. $last_login_date_tmp = Tracking::get_last_connection_date_on_the_course($row_user->user_id, $courseInfo, $session_id, false);
  1325. if ($last_login_date_tmp != false && $last_login_date == false) {
  1326. // TODO: To be cleaned.
  1327. $last_login_date = $last_login_date_tmp;
  1328. } else if ($last_login_date_tmp != false && $last_login_date != false) {
  1329. // TODO: Repeated previous condition! To be cleaned.
  1330. // Find the max and assign it to first_login_date
  1331. if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  1332. $last_login_date = $last_login_date_tmp;
  1333. }
  1334. }
  1335. $exercise_results_tmp = self::exercises_results($row_user->user_id, $row->code, $session_id);
  1336. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  1337. $total_score_possible += $exercise_results_tmp['score_possible'];
  1338. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  1339. }
  1340. if ($nb_progress_lp > 0) {
  1341. $avg_progress = round($progress / $nb_progress_lp, 2);
  1342. } else {
  1343. $avg_progress = 0;
  1344. }
  1345. if ($nb_score_lp > 0) {
  1346. $avg_score = round($score / $nb_score_lp, 2);
  1347. } else {
  1348. $avg_score = '-';
  1349. }
  1350. if ($last_login_date) {
  1351. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  1352. } else {
  1353. $last_login_date = '-';
  1354. }
  1355. if ($total_score_possible > 0) {
  1356. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  1357. } else {
  1358. $total_score_percentage = 0;
  1359. }
  1360. if ($total_score_percentage > 0) {
  1361. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  1362. } else {
  1363. $total_score = '-';
  1364. }
  1365. // time spent in the course
  1366. $return .= ' <td><div>'.api_time_to_hms($time_spent).'</div></td>';
  1367. // student progress in course
  1368. $return .= ' <td><div>'.$avg_progress.'</div></td>';
  1369. // student score
  1370. $return .= ' <td><div>'.$avg_score.'</div></td>';
  1371. // student messages
  1372. $return .= ' <td><div>'.$nb_messages.'</div></td>';
  1373. // student assignments
  1374. $return .= ' <td><div>'.$nb_assignments.'</div></td>';
  1375. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  1376. $return .= '<td width="105px;">'.$total_score.'</td>';
  1377. $return .= '<td>'.$total_questions_answered.'</td>';
  1378. // last connection
  1379. $return .= ' <td><div>'.$last_login_date.'</div></td>';
  1380. $return .= '<tr>';
  1381. }
  1382. $return .= '</table>';
  1383. return $return;
  1384. }
  1385. /**
  1386. * This function exports the table that we see in display_tracking_session_overview()
  1387. *
  1388. */
  1389. public static function export_tracking_session_overview()
  1390. {
  1391. // database table definition
  1392. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1393. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1394. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1395. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1396. // the values of the sortable table
  1397. if ($_GET['tracking_session_overview_page_nr']) {
  1398. $from = $_GET['tracking_session_overview_page_nr'];
  1399. } else {
  1400. $from = 0;
  1401. }
  1402. if ($_GET['tracking_session_overview_column']) {
  1403. $orderby = $_GET['tracking_session_overview_column'];
  1404. } else {
  1405. $orderby = 0;
  1406. }
  1407. if ($_GET['tracking_session_overview_direction']) {
  1408. $direction = $_GET['tracking_session_overview_direction'];
  1409. } else {
  1410. $direction = 'ASC';
  1411. }
  1412. $session_data = self::get_session_data_tracking_overview($from, 1000, $orderby, $direction);
  1413. $csv_content = array();
  1414. // the first line of the csv file with the column headers
  1415. $csv_row = array();
  1416. $csv_row[] = get_lang('Session');
  1417. $csv_row[] = get_lang('Course');
  1418. $csv_row[] = get_lang('AvgTimeSpentInTheCourse');
  1419. $csv_row[] = get_lang('AvgStudentsProgress');
  1420. $csv_row[] = get_lang('AvgCourseScore');
  1421. $csv_row[] = get_lang('TotalNumberOfMessages');
  1422. $csv_row[] = get_lang('TotalNumberOfAssignments');
  1423. $csv_row[] = get_lang('TotalExercisesScoreObtained');
  1424. $csv_row[] = get_lang('TotalExercisesScorePossible');
  1425. $csv_row[] = get_lang('TotalExercisesAnswered');
  1426. $csv_row[] = get_lang('TotalExercisesScorePercentage');
  1427. $csv_row[] = get_lang('LatestLogin');
  1428. $csv_content[] = $csv_row;
  1429. // the other lines (the data)
  1430. foreach ($session_data as $key => $session) {
  1431. $session_id = $session[0];
  1432. $session_title = $session[1];
  1433. // getting all the courses of the session
  1434. $sql = "SELECT * FROM $tbl_course AS c
  1435. INNER JOIN $tbl_session_rel_course AS sc
  1436. ON sc.c_id = c.id
  1437. WHERE sc.session_id = '".$session_id."';";
  1438. $result = Database::query($sql);
  1439. while ($row = Database::fetch_object($result)) {
  1440. $courseId = $row->c_id;
  1441. $courseInfo = api_get_course_info_by_id($courseId);
  1442. $csv_row = array();
  1443. $csv_row[] = $session_title;
  1444. $csv_row[] = $row->title;
  1445. // get the users in the course
  1446. $sql = "SELECT scu.user_id
  1447. FROM $tbl_user AS u
  1448. INNER JOIN $tbl_session_rel_course_rel_user AS scu
  1449. ON u.user_id = scu.user_id
  1450. WHERE scu.session_id = '".$session_id."' AND scu.c_id = '".$courseId."'";
  1451. $result_users = Database::query($sql);
  1452. $time_spent = 0;
  1453. $progress = 0;
  1454. $nb_progress_lp = 0;
  1455. $score = 0;
  1456. $nb_score_lp = 0;
  1457. $nb_messages = 0;
  1458. $nb_assignments = 0;
  1459. $last_login_date = false;
  1460. $total_score_obtained = 0;
  1461. $total_score_possible = 0;
  1462. $total_questions_answered = 0;
  1463. while ($row_user = Database::fetch_object($result_users)) {
  1464. // get time spent in the course and session
  1465. $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $courseId, $session_id);
  1466. $progress_tmp = Tracking::get_avg_student_progress($row_user->user_id, $row->code, array(), $session_id, true);
  1467. $progress += $progress_tmp[0];
  1468. $nb_progress_lp += $progress_tmp[1];
  1469. $score_tmp = Tracking::get_avg_student_score($row_user->user_id, $row->code, array(), $session_id, true);
  1470. if (is_array($score_tmp)) {
  1471. $score += $score_tmp[0];
  1472. $nb_score_lp += $score_tmp[1];
  1473. }
  1474. $nb_messages += Tracking::count_student_messages(
  1475. $row_user->user_id,
  1476. $row->code,
  1477. $session_id
  1478. );
  1479. $nb_assignments += Tracking::count_student_assignments(
  1480. $row_user->user_id,
  1481. $row->code,
  1482. $session_id
  1483. );
  1484. $last_login_date_tmp = Tracking:: get_last_connection_date_on_the_course(
  1485. $row_user->user_id,
  1486. $courseInfo,
  1487. $session_id,
  1488. false
  1489. );
  1490. if ($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned.
  1491. $last_login_date = $last_login_date_tmp;
  1492. } else if ($last_login_date_tmp != false && $last_login_date == false) { // TODO: Repeated previous condition. To be cleaned.
  1493. // Find the max and assign it to first_login_date
  1494. if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  1495. $last_login_date = $last_login_date_tmp;
  1496. }
  1497. }
  1498. $exercise_results_tmp = self::exercises_results($row_user->user_id, $row->code, $session_id);
  1499. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  1500. $total_score_possible += $exercise_results_tmp['score_possible'];
  1501. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  1502. }
  1503. if ($nb_progress_lp > 0) {
  1504. $avg_progress = round($progress / $nb_progress_lp, 2);
  1505. } else {
  1506. $avg_progress = 0;
  1507. }
  1508. if ($nb_score_lp > 0) {
  1509. $avg_score = round($score / $nb_score_lp, 2);
  1510. } else {
  1511. $avg_score = '-';
  1512. }
  1513. if ($last_login_date) {
  1514. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  1515. } else {
  1516. $last_login_date = '-';
  1517. }
  1518. if ($total_score_possible > 0) {
  1519. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  1520. } else {
  1521. $total_score_percentage = 0;
  1522. }
  1523. if ($total_score_percentage > 0) {
  1524. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  1525. } else {
  1526. $total_score = '-';
  1527. }
  1528. // time spent in the course
  1529. $csv_row[] = api_time_to_hms($time_spent);
  1530. // student progress in course
  1531. $csv_row[] = $avg_progress;
  1532. // student score
  1533. $csv_row[] = $avg_score;
  1534. // student messages
  1535. $csv_row[] = $nb_messages;
  1536. // student assignments
  1537. $csv_row[] = $nb_assignments;
  1538. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  1539. $csv_row[] = $total_score_obtained;
  1540. $csv_row[] = $total_score_possible;
  1541. $csv_row[] = $total_questions_answered;
  1542. $csv_row[] = $total_score_percentage;
  1543. // last connection
  1544. $csv_row[] = $last_login_date;
  1545. $csv_content[] = $csv_row;
  1546. }
  1547. }
  1548. Export::arrayToCsv($csv_content, 'reporting_session_overview');
  1549. exit;
  1550. }
  1551. /**
  1552. * Get general information about the exercise performance of the user
  1553. * the total obtained score (all the score on all the questions)
  1554. * the maximum score that could be obtained
  1555. * the number of questions answered
  1556. * the success percentage
  1557. * @param integer $user_id the id of the user
  1558. * @param string $course_code the course code
  1559. * @return array
  1560. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1561. * @version Dokeos 1.8.6
  1562. * @since November 2008
  1563. */
  1564. public static function exercises_results($user_id, $course_code, $session_id = false)
  1565. {
  1566. $courseId = api_get_course_int_id($course_code);
  1567. $sql = 'SELECT exe_result, exe_weighting
  1568. FROM '.Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES)."
  1569. WHERE
  1570. c_id = ' . $courseId . ' AND
  1571. exe_user_id = '".intval($user_id)."'";
  1572. if ($session_id !== false) {
  1573. $sql .= " AND session_id = '".$session_id."' ";
  1574. }
  1575. $result = Database::query($sql);
  1576. $score_obtained = 0;
  1577. $score_possible = 0;
  1578. $questions_answered = 0;
  1579. while ($row = Database::fetch_array($result)) {
  1580. $score_obtained += $row['exe_result'];
  1581. $score_possible += $row['exe_weighting'];
  1582. $questions_answered++;
  1583. }
  1584. if ($score_possible != 0) {
  1585. $percentage = round(($score_obtained / $score_possible * 100), 2);
  1586. } else {
  1587. $percentage = null;
  1588. }
  1589. return array(
  1590. 'score_obtained' => $score_obtained,
  1591. 'score_possible' => $score_possible,
  1592. 'questions_answered' => $questions_answered,
  1593. 'percentage' => $percentage
  1594. );
  1595. }
  1596. /**
  1597. * This function exports the table that we see in display_tracking_user_overview()
  1598. *
  1599. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1600. * @version Dokeos 1.8.6
  1601. * @since October 2008
  1602. */
  1603. public static function export_tracking_user_overview()
  1604. {
  1605. // database table definitions
  1606. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1607. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  1608. // the values of the sortable table
  1609. if ($_GET['tracking_user_overview_page_nr']) {
  1610. $from = $_GET['tracking_user_overview_page_nr'];
  1611. } else {
  1612. $from = 0;
  1613. }
  1614. if ($_GET['tracking_user_overview_column']) {
  1615. $orderby = $_GET['tracking_user_overview_column'];
  1616. } else {
  1617. $orderby = 0;
  1618. }
  1619. if ($is_western_name_order != api_is_western_name_order() && ($orderby == 1 || $orderby == 2)) {
  1620. // Swapping the sorting column if name order for export is different than the common name order.
  1621. $orderby = 3 - $orderby;
  1622. }
  1623. if ($_GET['tracking_user_overview_direction']) {
  1624. $direction = $_GET['tracking_user_overview_direction'];
  1625. } else {
  1626. $direction = 'ASC';
  1627. }
  1628. $user_data = self::get_user_data_tracking_overview(
  1629. $from,
  1630. 1000,
  1631. $orderby,
  1632. $direction
  1633. );
  1634. // the first line of the csv file with the column headers
  1635. $csv_row = array();
  1636. $csv_row[] = get_lang('OfficialCode');
  1637. if ($is_western_name_order) {
  1638. $csv_row[] = get_lang('FirstName', '');
  1639. $csv_row[] = get_lang('LastName', '');
  1640. } else {
  1641. $csv_row[] = get_lang('LastName', '');
  1642. $csv_row[] = get_lang('FirstName', '');
  1643. }
  1644. $csv_row[] = get_lang('LoginName');
  1645. $csv_row[] = get_lang('CourseCode');
  1646. // the additional user defined fields (only those that were selected to be exported)
  1647. $fields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  1648. if (is_array($_SESSION['additional_export_fields'])) {
  1649. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  1650. $csv_row[] = $fields[$extra_field_export][3];
  1651. $field_names_to_be_exported[] = 'extra_'.$fields[$extra_field_export][1];
  1652. }
  1653. }
  1654. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  1655. $csv_row[] = get_lang('AvgStudentsProgress', '');
  1656. $csv_row[] = get_lang('AvgCourseScore', '');
  1657. $csv_row[] = get_lang('AvgExercisesScore', '');
  1658. $csv_row[] = get_lang('AvgMessages', '');
  1659. $csv_row[] = get_lang('AvgAssignments', '');
  1660. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  1661. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  1662. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  1663. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  1664. $csv_row[] = get_lang('FirstLogin', '');
  1665. $csv_row[] = get_lang('LatestLogin', '');
  1666. $csv_content[] = $csv_row;
  1667. // the other lines (the data)
  1668. foreach ($user_data as $key => $user) {
  1669. // getting all the courses of the user
  1670. $sql = "SELECT * FROM $tbl_course_user
  1671. WHERE user_id = '".intval($user[4])."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  1672. $result = Database::query($sql);
  1673. while ($row = Database::fetch_row($result)) {
  1674. $courseInfo = api_get_course_info($row['course_code']);
  1675. $courseId = $courseInfo['real_id'];
  1676. $csv_row = array();
  1677. // user official code
  1678. $csv_row[] = $user[0];
  1679. // user first|last name
  1680. $csv_row[] = $user[1];
  1681. // user last|first name
  1682. $csv_row[] = $user[2];
  1683. // user login name
  1684. $csv_row[] = $user[3];
  1685. // course code
  1686. $csv_row[] = $row[0];
  1687. // the additional defined user fields
  1688. $extra_fields = self::get_user_overview_export_extra_fields($user[4]);
  1689. if (is_array($field_names_to_be_exported)) {
  1690. foreach ($field_names_to_be_exported as $key => $extra_field_export) {
  1691. $csv_row[] = $extra_fields[$extra_field_export];
  1692. }
  1693. }
  1694. // time spent in the course
  1695. $csv_row[] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user[4], $courseId));
  1696. // student progress in course
  1697. $csv_row[] = round(Tracking::get_avg_student_progress($user[4], $row[0]), 2);
  1698. // student score
  1699. $csv_row[] = round(Tracking::get_avg_student_score($user[4], $row[0]), 2);
  1700. // student tes score
  1701. $csv_row[] = round(Tracking::get_avg_student_exercise_score($user[4], $row[0]), 2);
  1702. // student messages
  1703. $csv_row[] = Tracking::count_student_messages($user[4], $row[0]);
  1704. // student assignments
  1705. $csv_row[] = Tracking::count_student_assignments($user[4], $row[0]);
  1706. // student exercises results
  1707. $exercises_results = self::exercises_results($user[4], $row[0]);
  1708. $csv_row[] = $exercises_results['score_obtained'];
  1709. $csv_row[] = $exercises_results['score_possible'];
  1710. $csv_row[] = $exercises_results['questions_answered'];
  1711. $csv_row[] = $exercises_results['percentage'];
  1712. // first connection
  1713. $csv_row[] = Tracking::get_first_connection_date_on_the_course($user[4], $courseId);
  1714. // last connection
  1715. $csv_row[] = strip_tags(Tracking::get_last_connection_date_on_the_course($user[4], $courseInfo));
  1716. $csv_content[] = $csv_row;
  1717. }
  1718. }
  1719. Export::arrayToCsv($csv_content, 'reporting_user_overview');
  1720. exit;
  1721. }
  1722. /**
  1723. * Get data for courses list in sortable with pagination
  1724. * @return array
  1725. */
  1726. public static function get_course_data($from, $number_of_items, $column, $direction)
  1727. {
  1728. global $courses, $csv_content, $charset, $session_id;
  1729. // definition database tables
  1730. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1731. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1732. $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1733. $course_data = array();
  1734. $courses_code = array_keys($courses);
  1735. foreach ($courses_code as &$code) {
  1736. $code = "'$code'";
  1737. }
  1738. // get all courses with limit
  1739. $sql = "SELECT course.code as col1, course.title as col2
  1740. FROM $tbl_course course
  1741. WHERE course.code IN (".implode(',', $courses_code).")";
  1742. if (!in_array($direction, array('ASC', 'DESC'))) $direction = 'ASC';
  1743. $column = intval($column);
  1744. $from = intval($from);
  1745. $number_of_items = intval($number_of_items);
  1746. $sql .= " ORDER BY col$column $direction ";
  1747. $sql .= " LIMIT $from,$number_of_items";
  1748. $res = Database::query($sql);
  1749. while ($row_course = Database::fetch_row($res)) {
  1750. $course_code = $row_course[0];
  1751. $courseInfo = api_get_course_info($course_code);
  1752. $courseId = $courseInfo['real_id'];
  1753. $avg_assignments_in_course = $avg_messages_in_course = $nb_students_in_course = $avg_progress_in_course = $avg_score_in_course = $avg_time_spent_in_course = $avg_score_in_exercise = 0;
  1754. // students directly subscribed to the course
  1755. if (empty($session_id)) {
  1756. $sql = "SELECT user_id
  1757. FROM $tbl_course_user as course_rel_user
  1758. WHERE
  1759. course_rel_user.status='5' AND
  1760. course_rel_user.c_id = '$courseId'";
  1761. } else {
  1762. $sql = "SELECT user_id FROM $tbl_session_course_user srcu
  1763. WHERE
  1764. c_id = '$courseId' AND
  1765. session_id = '$session_id' AND
  1766. status<>2";
  1767. }
  1768. $rs = Database::query($sql);
  1769. $users = array();
  1770. while ($row = Database::fetch_array($rs)) {
  1771. $users[] = $row['user_id'];
  1772. }
  1773. if (count($users) > 0) {
  1774. $nb_students_in_course = count($users);
  1775. $avg_assignments_in_course = Tracking::count_student_assignments($users, $course_code, $session_id);
  1776. $avg_messages_in_course = Tracking::count_student_messages($users, $course_code, $session_id);
  1777. $avg_progress_in_course = Tracking::get_avg_student_progress($users, $course_code, array(), $session_id);
  1778. $avg_score_in_course = Tracking::get_avg_student_score($users, $course_code, array(), $session_id);
  1779. $avg_score_in_exercise = Tracking::get_avg_student_exercise_score($users, $course_code, 0, $session_id);
  1780. $avg_time_spent_in_course = Tracking::get_time_spent_on_the_course($users, $courseInfo['real_id'], $session_id);
  1781. $avg_progress_in_course = round($avg_progress_in_course / $nb_students_in_course, 2);
  1782. if (is_numeric($avg_score_in_course)) {
  1783. $avg_score_in_course = round($avg_score_in_course / $nb_students_in_course, 2);
  1784. }
  1785. $avg_time_spent_in_course = api_time_to_hms($avg_time_spent_in_course / $nb_students_in_course);
  1786. } else {
  1787. $avg_time_spent_in_course = null;
  1788. $avg_progress_in_course = null;
  1789. $avg_score_in_course = null;
  1790. $avg_score_in_exercise = null;
  1791. $avg_messages_in_course = null;
  1792. $avg_assignments_in_course = null;
  1793. }
  1794. $table_row = array();
  1795. $table_row[] = $row_course[1];
  1796. $table_row[] = $nb_students_in_course;
  1797. $table_row[] = $avg_time_spent_in_course;
  1798. $table_row[] = is_null($avg_progress_in_course) ? '' : $avg_progress_in_course.'%';
  1799. $table_row[] = is_null($avg_score_in_course) ? '' : $avg_score_in_course.'%';
  1800. $table_row[] = is_null($avg_score_in_exercise) ? '' : $avg_score_in_exercise.'%';
  1801. $table_row[] = $avg_messages_in_course;
  1802. $table_row[] = $avg_assignments_in_course;
  1803. //set the "from" value to know if I access the Reporting by the chamilo tab or the course link
  1804. $table_row[] = '<center><a href="../../tracking/courseLog.php?cidReq='.$course_code.'&from=myspace&id_session='.$session_id.'">
  1805. '.Display::return_icon('2rightarrow.png').'
  1806. </a>
  1807. </center>';
  1808. $csv_content[] = array(
  1809. api_html_entity_decode($row_course[1], ENT_QUOTES, $charset),
  1810. $nb_students_in_course,
  1811. $avg_time_spent_in_course,
  1812. is_null($avg_progress_in_course) ? null : $avg_progress_in_course.'%',
  1813. is_null($avg_score_in_course) ? null : is_numeric($avg_score_in_course) ? $avg_score_in_course.'%' : $avg_score_in_course,
  1814. is_null($avg_score_in_exercise) ? null : $avg_score_in_exercise.'%',
  1815. $avg_messages_in_course,
  1816. $avg_assignments_in_course,
  1817. );
  1818. $course_data[] = $table_row;
  1819. }
  1820. return $course_data;
  1821. }
  1822. /**
  1823. * Get the number of users of the platform
  1824. *
  1825. * @return integer
  1826. *
  1827. */
  1828. public static function get_number_of_users_tracking_overview()
  1829. {
  1830. return UserManager::get_number_of_users(0, api_get_current_access_url_id());
  1831. }
  1832. /**
  1833. * Get all the data for the sortable table of the reporting progress of
  1834. * all users and all the courses the user is subscribed to.
  1835. *
  1836. * @param int $from
  1837. * @param int $numberItems
  1838. * @param int $column
  1839. * @param string $direction
  1840. * @return array
  1841. */
  1842. public static function get_user_data_tracking_overview($from, $numberItems, $column, $direction)
  1843. {
  1844. $isWestern = api_is_western_name_order();
  1845. switch ($column) {
  1846. case '0':
  1847. $column = 'official_code';
  1848. break;
  1849. case '1':
  1850. $column = $isWestern ? 'firstname' : 'lastname';
  1851. break;
  1852. case '2':
  1853. $column = $isWestern ? 'lastname' : 'firstname';
  1854. break;
  1855. case '3':
  1856. $column = 'username';
  1857. break;
  1858. case '4':
  1859. $column = 'username';
  1860. break;
  1861. }
  1862. $order = [
  1863. "$column $direction"
  1864. ];
  1865. $userList = UserManager::get_user_list([], $order, $from, $numberItems);
  1866. $return = [];
  1867. foreach ($userList as $user) {
  1868. $firstPosition = $user['lastname'];
  1869. $secondPosition = $user['firstname'];
  1870. if ($isWestern) {
  1871. $firstPosition = $user['firstname'];
  1872. $secondPosition = $user['lastname'];
  1873. }
  1874. $return[] = [
  1875. '0' => $user['official_code'],
  1876. 'col0' => $user['official_code'],
  1877. '1' => $firstPosition,
  1878. 'col1' => $firstPosition,
  1879. '2' => $secondPosition,
  1880. 'col2' => $secondPosition,
  1881. '3' => $user['username'],
  1882. 'col3' => $user['username'],
  1883. '4' => $user['user_id'],
  1884. 'col4' => $user['user_id']
  1885. ];
  1886. }
  1887. return $return;
  1888. }
  1889. /**
  1890. * Get all information that the user with user_id = $user_data has
  1891. * entered in the additionally defined profile fields
  1892. * @param integer $user_id the id of the user
  1893. * @return array
  1894. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1895. * @version Dokeos 1.8.6
  1896. * @since November 2008
  1897. */
  1898. public static function get_user_overview_export_extra_fields($user_id)
  1899. {
  1900. // include the user manager
  1901. $extra_data = UserManager::get_extra_user_data($user_id, true);
  1902. return $extra_data;
  1903. }
  1904. /**
  1905. * Checks if a username exist in the DB otherwise it create a "double"
  1906. * i.e. if we look into for jmontoya but the user's name already exist we create the user jmontoya2
  1907. * the return array will be array(username=>'jmontoya', sufix='2')
  1908. * @param string firstname
  1909. * @param string lastname
  1910. * @param string username
  1911. * @return array with the username, the sufix
  1912. * @author Julio Montoya Armas
  1913. */
  1914. public static function make_username($firstname, $lastname, $username, $language = null, $encoding = null)
  1915. {
  1916. // if username exist
  1917. if (!UserManager::is_username_available($username) || empty($username)) {
  1918. $i = 0;
  1919. while (1) {
  1920. if ($i == 0) {
  1921. $sufix = '';
  1922. } else {
  1923. $sufix = $i;
  1924. }
  1925. $desired_username = UserManager::create_username(
  1926. $firstname,
  1927. $lastname,
  1928. $language,
  1929. $encoding
  1930. );
  1931. if (UserManager::is_username_available($desired_username.$sufix)) {
  1932. break;
  1933. } else {
  1934. $i++;
  1935. }
  1936. }
  1937. $username_array = array('username' => $desired_username, 'sufix' => $sufix);
  1938. return $username_array;
  1939. } else {
  1940. $username_array = array('username' => $username, 'sufix' => '');
  1941. return $username_array;
  1942. }
  1943. }
  1944. /**
  1945. * Checks if there are repeted users in a given array
  1946. * @param array $usernames list of the usernames in the uploaded file
  1947. * @param array $user_array $user_array['username'] and $user_array['sufix'] where sufix is the number part in a login i.e -> jmontoya2
  1948. * @return array with the $usernames array and the $user_array array
  1949. * @author Julio Montoya
  1950. */
  1951. public static function check_user_in_array($usernames, $user_array)
  1952. {
  1953. $user_list = array_keys($usernames);
  1954. $username = $user_array['username'].$user_array['sufix'];
  1955. if (in_array($username, $user_list)) {
  1956. $user_array['sufix'] += $usernames[$username];
  1957. $usernames[$username]++;
  1958. } else {
  1959. $usernames[$username] = 1;
  1960. }
  1961. $result_array = array($usernames, $user_array);
  1962. return $result_array;
  1963. }
  1964. /**
  1965. * Checks whether a username has been already subscribed in a session.
  1966. * @param string $username a given username
  1967. * @param array $course_list the array with the course list id
  1968. * @param int $id_session the session id
  1969. * @return int 0 if the user is not subscribed otherwise it returns the user_id of the given username
  1970. * @author Julio Montoya
  1971. */
  1972. public static function user_available_in_session($username, $course_list, $id_session)
  1973. {
  1974. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  1975. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1976. $id_session = intval($id_session);
  1977. $username = Database::escape_string($username);
  1978. foreach ($course_list as $courseId) {
  1979. $courseId = intval($courseId);
  1980. $sql = " SELECT u.user_id FROM $tbl_session_rel_course_rel_user rel
  1981. INNER JOIN $table_user u
  1982. ON (rel.user_id = u.user_id)
  1983. WHERE
  1984. rel.session_id='$id_session' AND
  1985. u.status='5' AND
  1986. u.username ='$username' AND
  1987. rel.c_id='$courseId'";
  1988. $rs = Database::query($sql);
  1989. if (Database::num_rows($rs) > 0) {
  1990. return Database::result($rs, 0, 0);
  1991. } else {
  1992. return 0;
  1993. }
  1994. }
  1995. return 0;
  1996. }
  1997. /**
  1998. * This function checks whether some users in the uploaded file
  1999. * repeated and creates unique usernames if necesary.
  2000. * A case: Within the file there is an user repeted twice (Julio Montoya / Julio Montoya)
  2001. * and the username fields are empty.
  2002. * Then, this function would create unique usernames based on the first and the last name.
  2003. * Two users wiould be created - jmontoya and jmontoya2.
  2004. * Of course, if in the database there is a user with the name jmontoya,
  2005. * the newly created two users registered would be jmontoya2 and jmontoya3.
  2006. * @param $users list of users
  2007. * @return array
  2008. * @author Julio Montoya Armas
  2009. */
  2010. function check_all_usernames($users, $course_list, $id_session)
  2011. {
  2012. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  2013. $usernames = array();
  2014. $new_users = array();
  2015. foreach ($users as $index => $user) {
  2016. $desired_username = array();
  2017. if (empty($user['UserName'])) {
  2018. $desired_username = self::make_username($user['FirstName'], $user['LastName'], '');
  2019. $pre_username = $desired_username['username'].$desired_username['sufix'];
  2020. $user['UserName'] = $pre_username;
  2021. $user['create'] = '1';
  2022. } else {
  2023. if (UserManager::is_username_available($user['UserName'])) {
  2024. $desired_username = self::make_username($user['FirstName'], $user['LastName'], $user['UserName']);
  2025. $user['UserName'] = $desired_username['username'].$desired_username['sufix'];
  2026. $user['create'] = '1';
  2027. } else {
  2028. $is_session_avail = self::user_available_in_session($user['UserName'], $course_list, $id_session);
  2029. if ($is_session_avail == 0) {
  2030. $user_name = $user['UserName'];
  2031. $sql_select = "SELECT user_id FROM $table_user WHERE username ='$user_name' ";
  2032. $rs = Database::query($sql_select);
  2033. $user['create'] = Database::result($rs, 0, 0); // This should be the ID because the user exists.
  2034. } else {
  2035. $user['create'] = $is_session_avail;
  2036. }
  2037. }
  2038. }
  2039. // Usernames is the current list of users in the file.
  2040. $result_array = self::check_user_in_array($usernames, $desired_username);
  2041. $usernames = $result_array[0];
  2042. $desired_username = $result_array[1];
  2043. $user['UserName'] = $desired_username['username'].$desired_username['sufix'];
  2044. $new_users[] = $user;
  2045. }
  2046. return $new_users;
  2047. }
  2048. /**
  2049. * This functions checks whether there are users that are already
  2050. * registered in the DB by different creator than the current coach.
  2051. * @param string a given username
  2052. * @param array $users the array with the course list ids
  2053. * @return array
  2054. * @author Julio Montoya Armas
  2055. */
  2056. public function get_user_creator($users)
  2057. {
  2058. $errors = array();
  2059. foreach ($users as $index => $user) {
  2060. // database table definition
  2061. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  2062. $username = Database::escape_string($user['UserName']);
  2063. $sql = "SELECT creator_id FROM $table_user WHERE username='$username' ";
  2064. $rs = Database::query($sql);
  2065. $creator_id = Database::result($rs, 0, 0);
  2066. // check if we are the creators or not
  2067. if ($creator_id != '') {
  2068. if ($creator_id != api_get_user_id()) {
  2069. $user['error'] = get_lang('UserAlreadyRegisteredByOtherCreator');
  2070. $errors[] = $user;
  2071. }
  2072. }
  2073. }
  2074. return $errors;
  2075. }
  2076. /**
  2077. * Validates imported data.
  2078. * @param $users list of users
  2079. */
  2080. function validate_data($users, $id_session = null)
  2081. {
  2082. $errors = array();
  2083. $new_users = array();
  2084. foreach ($users as $index => $user) {
  2085. // 1. Check whether mandatory fields are set.
  2086. $mandatory_fields = array('LastName', 'FirstName');
  2087. if (api_get_setting('registration', 'email') == 'true') {
  2088. $mandatory_fields[] = 'Email';
  2089. }
  2090. foreach ($mandatory_fields as $key => $field) {
  2091. if (!isset ($user[$field]) || strlen($user[$field]) == 0) {
  2092. $user['error'] = get_lang($field.'Mandatory');
  2093. $errors[] = $user;
  2094. }
  2095. }
  2096. // 2. Check whether the username is too long.
  2097. if (UserManager::is_username_too_long($user['UserName'])) {
  2098. $user['error'] = get_lang('UserNameTooLong');
  2099. $errors[] = $user;
  2100. }
  2101. $user['UserName'] = trim($user['UserName']);
  2102. if (empty($user['UserName'])) {
  2103. $user['UserName'] = UserManager::create_username($user['FirstName'], $user['LastName']);
  2104. }
  2105. $new_users[] = $user;
  2106. }
  2107. $results = array('errors' => $errors, 'users' => $new_users);
  2108. return $results;
  2109. }
  2110. /**
  2111. * Adds missing user-information (which isn't required, like password, etc).
  2112. */
  2113. function complete_missing_data($user)
  2114. {
  2115. // 1. Generate a password if it is necessary.
  2116. if (!isset ($user['Password']) || strlen($user['Password']) == 0) {
  2117. $user['Password'] = api_generate_password();
  2118. }
  2119. return $user;
  2120. }
  2121. /**
  2122. * Saves imported data.
  2123. */
  2124. public function save_data($users, $course_list, $id_session)
  2125. {
  2126. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  2127. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  2128. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  2129. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  2130. $id_session = intval($id_session);
  2131. $sendMail = $_POST['sendMail'] ? 1 : 0;
  2132. // Adding users to the platform.
  2133. $new_users = array();
  2134. foreach ($users as $index => $user) {
  2135. $user = self::complete_missing_data($user);
  2136. // coach only will registered users
  2137. $default_status = STUDENT;
  2138. if ($user['create'] == COURSEMANAGER) {
  2139. $user['id'] = UserManager:: create_user(
  2140. $user['FirstName'],
  2141. $user['LastName'],
  2142. $default_status,
  2143. $user['Email'],
  2144. $user['UserName'],
  2145. $user['Password'],
  2146. $user['OfficialCode'],
  2147. api_get_setting('PlatformLanguage'),
  2148. $user['PhoneNumber'],
  2149. ''
  2150. );
  2151. $user['added_at_platform'] = 1;
  2152. } else {
  2153. $user['id'] = $user['create'];
  2154. $user['added_at_platform'] = 0;
  2155. }
  2156. $new_users[] = $user;
  2157. }
  2158. // Update user list.
  2159. $users = $new_users;
  2160. // Inserting users.
  2161. $super_list = array();
  2162. foreach ($course_list as $enreg_course) {
  2163. $nbr_users = 0;
  2164. $new_users = array();
  2165. $enreg_course = Database::escape_string($enreg_course);
  2166. foreach ($users as $index => $user) {
  2167. $userid = intval($user['id']);
  2168. $sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user(session_id, c_id, user_id)
  2169. VALUES('$id_session','$enreg_course','$userid')";
  2170. $course_session = array('course' => $enreg_course, 'added' => 1);
  2171. $result = Database::query($sql);
  2172. if (Database::affected_rows($result)) {
  2173. $nbr_users++;
  2174. }
  2175. $new_users[] = $user;
  2176. }
  2177. $super_list[] = $new_users;
  2178. //update the nbr_users field
  2179. $sql_select = "SELECT COUNT(user_id) as nbUsers FROM $tbl_session_rel_course_rel_user
  2180. WHERE session_id='$id_session' AND c_id='$enreg_course'";
  2181. $rs = Database::query($sql_select);
  2182. list($nbr_users) = Database::fetch_array($rs);
  2183. $sql_update = "UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users
  2184. WHERE session_id='$id_session' AND c_id='$enreg_course'";
  2185. Database::query($sql_update);
  2186. $sql_update = "UPDATE $tbl_session SET nbr_users= '$nbr_users' WHERE id='$id_session'";
  2187. Database::query($sql_update);
  2188. }
  2189. $new_users = array();
  2190. foreach ($users as $index => $user) {
  2191. $userid = $user['id'];
  2192. $sql_insert = "INSERT IGNORE INTO $tbl_session_rel_user(session_id, user_id, registered_at)
  2193. VALUES ('$id_session','$userid', '".api_get_utc_datetime()."')";
  2194. Database::query($sql_insert);
  2195. $user['added_at_session'] = 1;
  2196. $new_users[] = $user;
  2197. }
  2198. $users = $new_users;
  2199. $registered_users = get_lang('FileImported').'<br /> Import file results : <br />';
  2200. // Sending emails.
  2201. $addedto = '';
  2202. if ($sendMail) {
  2203. $i = 0;
  2204. foreach ($users as $index => $user) {
  2205. $emailsubject = '['.api_get_setting('siteName').'] '.get_lang('YourReg').' '.api_get_setting('siteName');
  2206. $emailbody = get_lang('Dear').' '.
  2207. api_get_person_name($user['FirstName'], $user['LastName']).",\n\n".
  2208. get_lang('YouAreReg')." ".api_get_setting('siteName')." ".get_lang('WithTheFollowingSettings')."\n\n".
  2209. get_lang('Username')." : $user[UserName]\n".
  2210. get_lang('Pass')." : $user[Password]\n\n".
  2211. get_lang('Address')." ".api_get_setting('siteName')." ".get_lang('Is')." : ".api_get_path(WEB_PATH)." \n\n".
  2212. get_lang('Problem')."\n\n".
  2213. get_lang('SignatureFormula').",\n\n".
  2214. api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname'))."\n".
  2215. get_lang('Manager')." ".api_get_setting('siteName')."\nT. ".
  2216. api_get_setting('administratorTelephone')."\n".get_lang('Email')." : ".api_get_setting('emailAdministrator');
  2217. api_mail_html(
  2218. api_get_person_name($user['FirstName'], $user['LastName'], null, PERSON_NAME_EMAIL_ADDRESS),
  2219. $user['Email'],
  2220. $emailsubject,
  2221. $emailbody
  2222. );
  2223. $userInfo = api_get_user_info($user['id']);
  2224. if (($user['added_at_platform'] == 1 && $user['added_at_session'] == 1) || $user['added_at_session'] == 1) {
  2225. if ($user['added_at_platform'] == 1) {
  2226. $addedto = get_lang('UserCreatedPlatform');
  2227. } else {
  2228. $addedto = ' ';
  2229. }
  2230. if ($user['added_at_session'] == 1) {
  2231. $addedto .= get_lang('UserInSession');
  2232. }
  2233. } else {
  2234. $addedto = get_lang('UserNotAdded');
  2235. }
  2236. $registered_users .= UserManager::getUserProfileLink($userInfo)." - ".$addedto.'<br />';
  2237. }
  2238. } else {
  2239. $i = 0;
  2240. foreach ($users as $index => $user) {
  2241. $userInfo = api_get_user_info($user['id']);
  2242. if (($user['added_at_platform'] == 1 && $user['added_at_session'] == 1) || $user['added_at_session'] == 1) {
  2243. if ($user['added_at_platform'] == 1) {
  2244. $addedto = get_lang('UserCreatedPlatform');
  2245. } else {
  2246. $addedto = ' ';
  2247. }
  2248. if ($user['added_at_session'] == 1) {
  2249. $addedto .= ' '.get_lang('UserInSession');
  2250. }
  2251. } else {
  2252. $addedto = get_lang('UserNotAdded');
  2253. }
  2254. $registered_users .= "<a href=\"../user/userInfo.php?uInfo=".$user['id']."\">".api_get_person_name($user['FirstName'], $user['LastName'])."</a> - ".$addedto.'<br />';
  2255. }
  2256. }
  2257. Display::addFlash(Display::return_message($registered_users));
  2258. header('Location: course.php?id_session='.$id_session);
  2259. exit;
  2260. }
  2261. /**
  2262. * Reads CSV-file.
  2263. * @param string $file Path to the CSV-file
  2264. * @return array All userinformation read from the file
  2265. */
  2266. function parse_csv_data($file)
  2267. {
  2268. $users = Import::csvToArray($file);
  2269. foreach ($users as $index => $user) {
  2270. if (isset ($user['Courses'])) {
  2271. $user['Courses'] = explode('|', trim($user['Courses']));
  2272. }
  2273. $users[$index] = $user;
  2274. }
  2275. return $users;
  2276. }
  2277. /**
  2278. * XML-parser: the handler at the beginning of element.
  2279. */
  2280. function element_start($parser, $data)
  2281. {
  2282. $data = api_utf8_decode($data);
  2283. global $user;
  2284. global $current_tag;
  2285. switch ($data) {
  2286. case 'Contact' :
  2287. $user = array();
  2288. break;
  2289. default:
  2290. $current_tag = $data;
  2291. }
  2292. }
  2293. /**
  2294. * XML-parser: the handler at the end of element.
  2295. */
  2296. function element_end($parser, $data)
  2297. {
  2298. $data = api_utf8_decode($data);
  2299. global $user;
  2300. global $users;
  2301. global $current_value;
  2302. global $purification_option_for_usernames;
  2303. $user[$data] = $current_value;
  2304. switch ($data) {
  2305. case 'Contact' :
  2306. $user['UserName'] = UserManager::purify_username($user['UserName'], $purification_option_for_usernames);
  2307. $users[] = $user;
  2308. break;
  2309. default:
  2310. $user[$data] = $current_value;
  2311. break;
  2312. }
  2313. }
  2314. /**
  2315. * XML-parser: the handler for character data.
  2316. */
  2317. function character_data($parser, $data)
  2318. {
  2319. $data = trim(api_utf8_decode($data));
  2320. global $current_value;
  2321. $current_value = $data;
  2322. }
  2323. /**
  2324. * Reads XML-file.
  2325. * @param string $file Path to the XML-file
  2326. * @return array All userinformation read from the file
  2327. */
  2328. function parse_xml_data($file)
  2329. {
  2330. global $current_tag;
  2331. global $current_value;
  2332. global $user;
  2333. global $users;
  2334. $users = array();
  2335. $parser = xml_parser_create('UTF-8');
  2336. xml_set_element_handler($parser, array('MySpace', 'element_start'), array('MySpace', 'element_end'));
  2337. xml_set_character_data_handler($parser, "character_data");
  2338. xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, false);
  2339. xml_parse($parser, api_utf8_encode_xml(file_get_contents($file)));
  2340. xml_parser_free($parser);
  2341. return $users;
  2342. }
  2343. public static function displayTrackingAccessOverView($courseId, $sessionId, $studentId)
  2344. {
  2345. $courseId = intval($courseId);
  2346. $sessionId = intval($sessionId);
  2347. $studentId = intval($studentId);
  2348. $em = Database::getManager();
  2349. $sessionRepo = $em->getRepository('ChamiloCoreBundle:Session');
  2350. $courseList = [];
  2351. $sessionList = [];
  2352. $studentList = [];
  2353. if (!empty($courseId)) {
  2354. $course = $em->find('ChamiloCoreBundle:Course', $courseId);
  2355. $courseList[$course->getId()] = $course->getTitle();
  2356. }
  2357. if (!empty($sessionId)) {
  2358. $session = $em->find('ChamiloCoreBundle:Session', $sessionId);
  2359. $sessionList[$session->getId()] = $session->getName();
  2360. }
  2361. if (!empty($studentId)) {
  2362. $student = $em->find('ChamiloUserBundle:User', $studentId);
  2363. $studentList[$student->getId()] = $student->getCompleteName();
  2364. }
  2365. $form = new FormValidator('access_overview', 'GET');
  2366. $form->addElement(
  2367. 'select_ajax',
  2368. 'course_id',
  2369. get_lang('SearchCourse'),
  2370. $courseList,
  2371. [
  2372. 'url' => api_get_path(WEB_AJAX_PATH).'course.ajax.php?'.http_build_query([
  2373. 'a' => 'search_course_by_session_all',
  2374. 'session_id' => $sessionId
  2375. ])
  2376. ]
  2377. );
  2378. $form->addElement(
  2379. 'select_ajax',
  2380. 'session_id',
  2381. get_lang('SearchSession'),
  2382. $sessionList,
  2383. [
  2384. 'url_function' => "
  2385. function () {
  2386. var params = $.param({
  2387. a: 'search_session_by_course',
  2388. course_id: $('#course_id').val() || 0
  2389. });
  2390. return '" . api_get_path(WEB_AJAX_PATH)."session.ajax.php?' + params;
  2391. }
  2392. "
  2393. ]
  2394. );
  2395. $form->addSelect(
  2396. 'profile',
  2397. get_lang('Profile'),
  2398. [
  2399. '' => get_lang('Select'),
  2400. STUDENT => get_lang('Student'),
  2401. COURSEMANAGER => get_lang('CourseManager'),
  2402. DRH => get_lang('Drh')
  2403. ],
  2404. ['id' => 'profile']
  2405. );
  2406. $form->addElement(
  2407. 'select_ajax',
  2408. 'student_id',
  2409. get_lang('SearchUsers'),
  2410. $studentList,
  2411. [
  2412. 'placeholder' => get_lang('All'),
  2413. 'url_function' => "
  2414. function () {
  2415. var params = $.param({
  2416. a: 'search_user_by_course',
  2417. session_id: $('#session_id').val(),
  2418. course_id: $('#course_id').val()
  2419. });
  2420. return '" . api_get_path(WEB_AJAX_PATH)."course.ajax.php?' + params;
  2421. }
  2422. "
  2423. ]
  2424. );
  2425. $form->addDateRangePicker(
  2426. 'date',
  2427. get_lang('DateRange'),
  2428. true,
  2429. [
  2430. 'id' => 'date_range',
  2431. 'format' => 'YYYY-MM-DD',
  2432. 'timePicker' => 'false',
  2433. 'validate_format' => 'Y-m-d'
  2434. ]
  2435. );
  2436. $form->addHidden('display', 'accessoverview');
  2437. $form->addRule('course_id', get_lang('Required'), 'required');
  2438. $form->addRule('profile', get_lang('Required'), 'required');
  2439. $form->addButton('submit', get_lang('Generate'), 'gear', 'primary');
  2440. $table = null;
  2441. if ($form->validate()) {
  2442. $table = new SortableTable(
  2443. 'tracking_access_overview',
  2444. ['MySpace', 'getNumberOfTrackAccessOverview'],
  2445. ['MySpace', 'getUserDataAccessTrackingOverview'],
  2446. 0
  2447. );
  2448. $table->additional_parameters = $form->exportValues();
  2449. $table->set_header(0, get_lang('LoginDate'), true);
  2450. $table->set_header(1, get_lang('Username'), true);
  2451. if (api_is_western_name_order()) {
  2452. $table->set_header(2, get_lang('FirstName'), true);
  2453. $table->set_header(3, get_lang('LastName'), true);
  2454. } else {
  2455. $table->set_header(2, get_lang('LastName'), true);
  2456. $table->set_header(3, get_lang('FirstName'), true);
  2457. }
  2458. $table->set_header(4, get_lang('Clicks'), false);
  2459. $table->set_header(5, get_lang('IP'), false);
  2460. $table->set_header(6, get_lang('TimeLoggedIn'), false);
  2461. }
  2462. $template = new Template(
  2463. null, false, false, false, false, false, false
  2464. );
  2465. $template->assign('form', $form->returnForm());
  2466. $template->assign('table', $table ? $table->return_table() : null);
  2467. echo $template->fetch(
  2468. $template->get_template('my_space/accessoverview.tpl')
  2469. );
  2470. }
  2471. /**
  2472. * @return int
  2473. */
  2474. public static function getNumberOfTrackAccessOverview()
  2475. {
  2476. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2477. $sql = "SELECT COUNT(course_access_id) count FROM $table";
  2478. $result = Database::query($sql);
  2479. $row = Database::fetch_assoc($result);
  2480. return $row['count'];
  2481. }
  2482. /**
  2483. * @param $from
  2484. * @param $numberItems
  2485. * @param $column
  2486. * @param $orderDirection
  2487. * @return array
  2488. */
  2489. public static function getUserDataAccessTrackingOverview(
  2490. $from,
  2491. $numberItems,
  2492. $column,
  2493. $orderDirection
  2494. ) {
  2495. $user = Database::get_main_table(TABLE_MAIN_USER);
  2496. $course = Database::get_main_table(TABLE_MAIN_COURSE);
  2497. $track_e_login = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  2498. $track_e_course_access = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2499. global $export_csv;
  2500. if ($export_csv) {
  2501. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  2502. } else {
  2503. $is_western_name_order = api_is_western_name_order();
  2504. }
  2505. //TODO add course name
  2506. $sql = "SELECT
  2507. a.login_course_date as col0,
  2508. u.username as col1,
  2509. " . (
  2510. $is_western_name_order ? "
  2511. u.firstname AS col2,
  2512. u.lastname AS col3,
  2513. " : "
  2514. u.lastname AS col2,
  2515. u.firstname AS col3,
  2516. " )."
  2517. a.logout_course_date,
  2518. c.title,
  2519. c.code,
  2520. u.user_id
  2521. FROM $track_e_course_access a
  2522. INNER JOIN $user u ON a.user_id = u.user_id
  2523. INNER JOIN $course c ON a.c_id = c.id";
  2524. if (isset($_GET['session_id']) && !empty($_GET['session_id'])) {
  2525. $sessionId = intval($_GET['session_id']);
  2526. $sql .= " WHERE a.session_id = ".$sessionId;
  2527. }
  2528. $sql .= " ORDER BY col$column $orderDirection ";
  2529. $sql .= " LIMIT $from,$numberItems";
  2530. $result = Database::query($sql);
  2531. $data = array();
  2532. while ($user = Database::fetch_assoc($result)) {
  2533. $data[] = $user;
  2534. }
  2535. $return = [];
  2536. //TODO: Dont use numeric index
  2537. foreach ($data as $key => $info) {
  2538. $start_date = $info['col0'];
  2539. $end_date = $info['logout_course_date'];
  2540. $return[$info['user_id']] = array(
  2541. $start_date,
  2542. $info['col1'],
  2543. $info['col2'],
  2544. $info['col3'],
  2545. $info['user_id'],
  2546. 'ip',
  2547. //TODO is not correct/precise, it counts the time not logged between two loggins
  2548. gmdate("H:i:s", strtotime($end_date) - strtotime($start_date))
  2549. );
  2550. }
  2551. foreach ($return as $key => $info) {
  2552. $ipResult = Database::select(
  2553. 'user_ip',
  2554. $track_e_login,
  2555. ['where' => [
  2556. '? BETWEEN login_date AND logout_date' => $info[0]
  2557. ]],
  2558. 'first'
  2559. );
  2560. $return[$key][5] = $ipResult['user_ip'];
  2561. }
  2562. return $return;
  2563. }
  2564. /**
  2565. * Gets the connections to a course as an array of login and logout time
  2566. *
  2567. * @param int $user_id
  2568. * @param array $course_info
  2569. * @param int $sessionId
  2570. * @param string $start_date
  2571. * @param string $end_date
  2572. * @author Jorge Frisancho Jibaja
  2573. * @author Julio Montoya <gugli100@gmail.com> fixing the function
  2574. * @version OCT-22- 2010
  2575. * @return array
  2576. */
  2577. public static function get_connections_to_course_by_date(
  2578. $user_id,
  2579. $course_info,
  2580. $sessionId,
  2581. $start_date,
  2582. $end_date
  2583. ) {
  2584. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2585. $user_id = intval($user_id);
  2586. $connections = array();
  2587. if (!empty($course_info)) {
  2588. $courseId = intval($course_info['real_id']);
  2589. $end_date = add_day_to($end_date);
  2590. $sessionCondition = api_get_session_condition($sessionId);
  2591. $sql = "SELECT login_course_date, logout_course_date
  2592. FROM $table
  2593. WHERE
  2594. user_id = $user_id AND
  2595. c_id = $courseId AND
  2596. login_course_date BETWEEN '$start_date' AND '$end_date' AND
  2597. logout_course_date BETWEEN '$start_date' AND '$end_date'
  2598. $sessionCondition
  2599. ORDER BY login_course_date ASC";
  2600. $rs = Database::query($sql);
  2601. while ($row = Database::fetch_array($rs)) {
  2602. $connections[] = array(
  2603. 'login' => $row['login_course_date'],
  2604. 'logout' =>$row['logout_course_date']
  2605. );
  2606. }
  2607. }
  2608. return $connections;
  2609. }
  2610. }
  2611. /**
  2612. * @param $user_id
  2613. * @param array $course_info
  2614. * @param int $sessionId
  2615. * @param null $start_date
  2616. * @param null $end_date
  2617. * @return array
  2618. */
  2619. function get_stats($user_id, $course_info, $sessionId, $start_date = null, $end_date = null)
  2620. {
  2621. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2622. $result = array();
  2623. if (!empty($course_info)) {
  2624. $stringStartDate = '';
  2625. $stringEndDate = '';
  2626. if ($start_date != null && $end_date != null) {
  2627. $end_date = add_day_to($end_date);
  2628. $stringStartDate = "AND login_course_date BETWEEN '$start_date' AND '$end_date'";
  2629. $stringEndDate = "AND logout_course_date BETWEEN '$start_date' AND '$end_date'";
  2630. }
  2631. $user_id = intval($user_id);
  2632. $courseId = intval($course_info['real_id']);
  2633. $sessionCondition = api_get_session_condition($sessionId);
  2634. $sql = "SELECT
  2635. SEC_TO_TIME(AVG(time_to_sec(timediff(logout_course_date,login_course_date)))) as avrg,
  2636. SEC_TO_TIME(SUM(time_to_sec(timediff(logout_course_date,login_course_date)))) as total,
  2637. count(user_id) as times
  2638. FROM $table
  2639. WHERE
  2640. user_id = $user_id AND
  2641. c_id = $courseId $stringStartDate $stringEndDate
  2642. $sessionCondition
  2643. ORDER BY login_course_date ASC";
  2644. $rs = Database::query($sql);
  2645. if ($row = Database::fetch_array($rs)) {
  2646. $foo_avg = $row['avrg'];
  2647. $foo_total = $row['total'];
  2648. $foo_times = $row['times'];
  2649. $result = array(
  2650. 'avg' => $foo_avg,
  2651. 'total' => $foo_total,
  2652. 'times' => $foo_times
  2653. );
  2654. }
  2655. }
  2656. return $result;
  2657. }
  2658. function add_day_to($end_date)
  2659. {
  2660. $foo_date = strtotime($end_date);
  2661. $foo_date = strtotime(" +1 day", $foo_date);
  2662. $foo_date = date("Y-m-d", $foo_date);
  2663. return $foo_date;
  2664. }
  2665. /**
  2666. *
  2667. * @param array
  2668. * @author Jorge Frisancho Jibaja
  2669. * @version OCT-22- 2010
  2670. * @return array
  2671. */
  2672. function convert_to_array($sql_result)
  2673. {
  2674. $result_to_print = '<table>';
  2675. foreach ($sql_result as $key => $data) {
  2676. $result_to_print .= '<tr><td>'.date('d-m-Y (H:i:s)', $data['login']).'</td><td>'.api_time_to_hms($data['logout'] - $data['login']).'</tr></td>'."\n";
  2677. }
  2678. $result_to_print .= '</table>';
  2679. $result_to_print = array("result"=>$result_to_print);
  2680. return $result_to_print;
  2681. }
  2682. /**
  2683. * Converte an array to a table in html
  2684. *
  2685. * @param array $result
  2686. * @author Jorge Frisancho Jibaja
  2687. * @version OCT-22- 2010
  2688. * @return string
  2689. */
  2690. function convert_to_string($result)
  2691. {
  2692. $html = '<table class="table">';
  2693. if (!empty($result)) {
  2694. foreach ($result as $key => $data) {
  2695. $html .= '<tr><td>';
  2696. $html .= api_get_local_time($data['login']);
  2697. $html .= '</td>';
  2698. $html .= '<td>';
  2699. $html .= api_time_to_hms(api_strtotime($data['logout']) - api_strtotime($data['login']));
  2700. $html .= '</tr></td>';
  2701. }
  2702. }
  2703. $html .= '</table>';
  2704. return $html;
  2705. }
  2706. /**
  2707. * This function draw the graphic to be displayed on the user view as an image
  2708. *
  2709. * @param array $sql_result
  2710. * @param string $start_date
  2711. * @param string $end_date
  2712. * @param string $type
  2713. * @author Jorge Frisancho Jibaja
  2714. * @version OCT-22- 2010
  2715. * @return string
  2716. */
  2717. function grapher($sql_result, $start_date, $end_date, $type = '')
  2718. {
  2719. if (empty($start_date)) {
  2720. $start_date = '';
  2721. }
  2722. if (empty($end_date)) {
  2723. $end_date = '';
  2724. }
  2725. if ($type == '') {
  2726. $type = 'day';
  2727. }
  2728. $main_year = $main_month_year = $main_day = [];
  2729. $period = new DatePeriod(
  2730. new DateTime($start_date),
  2731. new DateInterval('P1D'),
  2732. new DateTime($end_date)
  2733. );
  2734. foreach ($period as $date) {
  2735. $main_day[$date->format('d-m-Y')] = 0;
  2736. }
  2737. $period = new DatePeriod(
  2738. new DateTime($start_date),
  2739. new DateInterval('P1M'),
  2740. new DateTime($end_date)
  2741. );
  2742. foreach ($period as $date) {
  2743. $main_month_year[$date->format('m-Y')] = 0;
  2744. }
  2745. $i = 0;
  2746. if (is_array($sql_result) && count($sql_result) > 0) {
  2747. foreach ($sql_result as $key => $data) {
  2748. $login = api_strtotime($data['login']);
  2749. $logout = api_strtotime($data['logout']);
  2750. //creating the main array
  2751. if (isset($main_month_year[date('m-Y', $login)])) {
  2752. $main_month_year[date('m-Y', $login)] += float_format(($logout - $login) / 60, 0);
  2753. }
  2754. if (isset($main_day[date('d-m-Y', $login)])) {
  2755. $main_day[date('d-m-Y', $login)] += float_format(($logout - $login) / 60, 0);
  2756. }
  2757. if ($i > 500) {
  2758. break;
  2759. }
  2760. $i++;
  2761. }
  2762. switch ($type) {
  2763. case 'day':
  2764. $main_date = $main_day;
  2765. break;
  2766. case 'month':
  2767. $main_date = $main_month_year;
  2768. break;
  2769. case 'year':
  2770. $main_date = $main_year;
  2771. break;
  2772. }
  2773. $labels = array_keys($main_date);
  2774. if (count($main_date) == 1) {
  2775. $labels = $labels[0];
  2776. $main_date = $main_date[$labels];
  2777. }
  2778. /* Create and populate the pData object */
  2779. $myData = new pData();
  2780. $myData->addPoints($main_date, 'Serie1');
  2781. if (count($main_date) != 1) {
  2782. $myData->addPoints($labels, 'Labels');
  2783. $myData->setSerieDescription('Labels', 'Months');
  2784. $myData->setAbscissa('Labels');
  2785. }
  2786. $myData->setSerieWeight('Serie1', 1);
  2787. $myData->setSerieDescription('Serie1', get_lang('MyResults'));
  2788. $myData->setAxisName(0, get_lang('Minutes'));
  2789. $myData->loadPalette(api_get_path(SYS_CODE_PATH).'palettes/pchart/default.color', true);
  2790. // Cache definition
  2791. $cachePath = api_get_path(SYS_ARCHIVE_PATH);
  2792. $myCache = new pCache(array('CacheFolder' => substr($cachePath, 0, strlen($cachePath) - 1)));
  2793. $chartHash = $myCache->getHash($myData);
  2794. if ($myCache->isInCache($chartHash)) {
  2795. //if we already created the img
  2796. $imgPath = api_get_path(SYS_ARCHIVE_PATH).$chartHash;
  2797. $myCache->saveFromCache($chartHash, $imgPath);
  2798. $imgPath = api_get_path(WEB_ARCHIVE_PATH).$chartHash;
  2799. } else {
  2800. /* Define width, height and angle */
  2801. $mainWidth = 760;
  2802. $mainHeight = 230;
  2803. $angle = 50;
  2804. /* Create the pChart object */
  2805. $myPicture = new pImage($mainWidth, $mainHeight, $myData);
  2806. /* Turn of Antialiasing */
  2807. $myPicture->Antialias = false;
  2808. /* Draw the background */
  2809. $settings = array("R" => 255, "G" => 255, "B" => 255);
  2810. $myPicture->drawFilledRectangle(0, 0, $mainWidth, $mainHeight, $settings);
  2811. /* Add a border to the picture */
  2812. $myPicture->drawRectangle(
  2813. 0,
  2814. 0,
  2815. $mainWidth - 1,
  2816. $mainHeight - 1,
  2817. array("R" => 0, "G" => 0, "B" => 0)
  2818. );
  2819. /* Set the default font */
  2820. $myPicture->setFontProperties(
  2821. array(
  2822. "FontName" => api_get_path(SYS_FONTS_PATH).'opensans/OpenSans-Regular.ttf',
  2823. "FontSize" => 10)
  2824. );
  2825. /* Write the chart title */
  2826. $myPicture->drawText(
  2827. $mainWidth / 2,
  2828. 30,
  2829. get_lang('TimeSpentInTheCourse'),
  2830. array(
  2831. "FontSize" => 12,
  2832. "Align" => TEXT_ALIGN_BOTTOMMIDDLE
  2833. )
  2834. );
  2835. /* Set the default font */
  2836. $myPicture->setFontProperties(
  2837. array(
  2838. "FontName" => api_get_path(SYS_FONTS_PATH).'opensans/OpenSans-Regular.ttf',
  2839. "FontSize" => 8
  2840. )
  2841. );
  2842. /* Define the chart area */
  2843. $myPicture->setGraphArea(50, 40, $mainWidth - 40, $mainHeight - 80);
  2844. /* Draw the scale */
  2845. $scaleSettings = array(
  2846. 'XMargin' => 10,
  2847. 'YMargin' => 10,
  2848. 'Floating' => true,
  2849. 'GridR' => 200,
  2850. 'GridG' => 200,
  2851. 'GridB' => 200,
  2852. 'DrawSubTicks' => true,
  2853. 'CycleBackground' => true,
  2854. 'LabelRotation' => $angle,
  2855. 'Mode' => SCALE_MODE_ADDALL_START0,
  2856. );
  2857. $myPicture->drawScale($scaleSettings);
  2858. /* Turn on Antialiasing */
  2859. $myPicture->Antialias = true;
  2860. /* Enable shadow computing */
  2861. $myPicture->setShadow(
  2862. true,
  2863. array(
  2864. "X" => 1,
  2865. "Y" => 1,
  2866. "R" => 0,
  2867. "G" => 0,
  2868. "B" => 0,
  2869. "Alpha" => 10
  2870. )
  2871. );
  2872. /* Draw the line chart */
  2873. $myPicture->setFontProperties(
  2874. array(
  2875. "FontName" => api_get_path(SYS_FONTS_PATH).'opensans/OpenSans-Regular.ttf',
  2876. "FontSize" => 10
  2877. )
  2878. );
  2879. $myPicture->drawSplineChart();
  2880. $myPicture->drawPlotChart(
  2881. array(
  2882. "DisplayValues" => true,
  2883. "PlotBorder" => true,
  2884. "BorderSize" => 1,
  2885. "Surrounding" => -60,
  2886. "BorderAlpha" => 80
  2887. )
  2888. );
  2889. /* Do NOT Write the chart legend */
  2890. /* Write and save into cache */
  2891. $myCache->writeToCache($chartHash, $myPicture);
  2892. $imgPath = api_get_path(SYS_ARCHIVE_PATH).$chartHash;
  2893. $myCache->saveFromCache($chartHash, $imgPath);
  2894. $imgPath = api_get_path(WEB_ARCHIVE_PATH).$chartHash;
  2895. }
  2896. $html = '<img src="'.$imgPath.'">';
  2897. return $html;
  2898. } else {
  2899. $foo_img = api_convert_encoding(
  2900. '<div id="messages" class="warning-message">'.get_lang('GraphicNotAvailable').'</div>',
  2901. 'UTF-8'
  2902. );
  2903. return $foo_img;
  2904. }
  2905. }