myspace.lib.php 142 KB

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