myspace.lib.php 127 KB

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