PageRenderTime 57ms CodeModel.GetById 13ms RepoModel.GetById 0ms app.codeStats 1ms

/appLms/admin/modules/report/class.report_aggregate.php

https://github.com/wanadli75/cocalms
PHP | 2216 lines | 1612 code | 418 blank | 186 comment | 377 complexity | c2a17e7f3c6b8d753f16cd61856ac44c MD5 | raw file
Possible License(s): GPL-2.0, CC-BY-3.0 
    

  1. <?php defined("IN_FORMA") or die('Direct access is forbidden.');

    2. 

  2. 

    3. 

  3. /* ======================================================================== \

    4. 

  4. |   FORMA - The E-Learning Suite                                            |

    5. 

  5. |                                                                           |

    6. 

  6. |   Copyright (c) 2013 (Forma)                                              |

    7. 

  7. |   http://www.formalms.org                                                 |

    8. 

  8. |   License  http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt           |

    9. 

  9. |                                                                           |

    10. 

  10. |   from docebo 4.0.5 CE 2008-2012 (c) docebo                               |

    11. 

  11. |   License http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt            |

    12. 

  12. \ ======================================================================== */

    13. 

  13. 

    14. 

  14. require_once($GLOBALS['where_lms'].'/lib/lib.course.php');

    15. 

  15. require_once(dirname(__FILE__).'/class.report.php');

    16. 

  16. 

    17. 

  17. 

    18. 

  18. define('_RA_CATEGORY_COURSES', 'courses');

    19. 

  19. define('_RA_CATEGORY_COURSECATS', 'coursecategories');

    20. 

  20. define('_RA_CATEGORY_TIME', 'time');

    21. 

  21. define('_RA_CATEGORY_COMMUNICATIONS', 'communications');

    22. 

  22. define('_RA_CATEGORY_GAMES', 'games');

    23. 

  23. 

    24. 

  24. define('_DECIMAL_SEPARATOR', '.');

    25. 

  25. define('_PERCENT_SIMBOL', '%');

    26. 

  26. 

    27. 

  27. class Report_Aggregate extends Report {

    28. 

  28. 

    29. 

  29. 	var $page_title = false;

    30. 

  30. 	var $db = NULL;

    31. 

  31. 

    32. 

  32. 	function Report_Aggregate() {

    33. 

  33. 

    34. 

  34. 		$this->db = DbConn::getInstance();

    35. 

  35. 

    36. 

  36. 		$this->lang =& DoceboLanguage::createInstance('report', 'framework');

    37. 

  37. 		$this->_set_columns_category(_RA_CATEGORY_COURSES, Lang::t('_RU_CAT_COURSES', 'report'), 'get_courses_filter', 'show_report_courses', '_get_courses_query');

    38. 

  38. 		$this->_set_columns_category(_RA_CATEGORY_COURSECATS, Lang::t('_RA_CAT_COURSECATS', 'report'), 'get_coursecategories_filter', 'show_report_coursecategories', '_get_coursecategories_query');

    39. 

  39. 		$this->_set_columns_category(_RA_CATEGORY_TIME, Lang::t('_RA_CAT_TIME', 'report'), 'get_time_filter', 'show_report_time', '_get_time_query');

    40. 

  40. 		$this->_set_columns_category(_RA_CATEGORY_COMMUNICATIONS, Lang::t('_RU_CAT_COMMUNICATIONS', 'report'), 'get_communications_filter', 'show_report_communications', '_get_communications_query');

    41. 

  41. 		$this->_set_columns_category(_RA_CATEGORY_GAMES, Lang::t('_RU_CAT_GAMES', 'report'), 'get_games_filter', 'show_report_games', '_get_games_query');

    42. 

  42. 	}

    43. 

  43. 

    44. 

  44. 

    45. 

  45. 	//users and orgchart selection

    46. 

  46. 	function get_rows_filter() {

    47. 

  47. 		$back_url = $this->back_url;

    48. 

  48. 		$jump_url = $this->jump_url;

    49. 

  49. 		$next_url = $this->next_url;

    50. 

  50. 

    51. 

  51. 		require_once(_base_.'/lib/lib.form.php');

    52. 

  52. 		require_once($GLOBALS['where_framework'].'/lib/lib.directory.php');

    53. 

  53. 		require_once(_base_.'/lib/lib.userselector.php');

    54. 

  54. 		require_once($GLOBALS['where_lms'].'/lib/lib.course.php');

    55. 

  55. 		require_once($GLOBALS['where_lms'].'/lib/lib.course_managment.php');

    56. 

  56. 

    57. 

  57. 		$lang =& DoceboLanguage::createInstance('report', 'framework');

    58. 

  58. 

    59. 

  59. 		//update session

    60. 

  60. 		$ref =& $_SESSION['report_tempdata'];

    61. 

  61. 		if (!isset($ref['rows_filter'])) {

    62. 

  62. 			$ref['rows_filter'] = array( //default values

    63. 

  63. 				'select_all' => false,

    64. 

  64. 				'selection_type' => 'users',

    65. 

  65. 				'selection' => array()

    66. 

  66. 			);

    67. 

  67. 		} else {

    68. 

  68. 			//already resolved in switch block

    69. 

  69. 		}

    70. 

  70. 

    71. 

  71. 		$step = Get::req('step', DOTY_ALPHANUM, 'sel_type');

    72. 

  72. 		switch ($step) {

    73. 

  73. 

    74. 

  74. 			case 'sel_type': {

    75. 

  75. 				$values = array('users' => $this->lang->def('_USERS'), 'groups'=>$this->lang->def('_GROUPS'));

    76. 

  76. 				$sel_val = (isset($ref['rows_filter']['selection_type']) ? $ref['rows_filter']['selection_type'] : 'users');

    77. 

  77. 

    78. 

  78. 				$out  = Form::openForm('selection_type_form', $jump_url);

    79. 

  79. 

    80. 

  80. 				$out .= Form::getRadioSet($this->lang->def('_AGGREGATE_ON'), 'selection_type', 'selection_type', array_flip($values) , $sel_val)

    81. 

  81. 					.'<div class="nofloat"></div>';

    82. 

  82. 

    83. 

  83. 				$out .= Form::openButtonSpace();

    84. 

  84. 				$out .= Form::getButton('ok_selection', 'ok_selection', $this->lang->def('_CONFIRM'));

    85. 

  85. 				$out .= Form::getButton('undo', 'undo', $this->lang->def('_UNDO'));

    86. 

  86. 				$out .= Form::closeButtonSpace();

    87. 

  87. 				$out .= Form::getHidden('step', 'step', 'sel_data');

    88. 

  88. 

    89. 

  89. 				$out .= Form::closeForm();

    90. 

  90. 

    91. 

  91. 				cout($out);

    92. 

  92. 			} break;

    93. 

  93. 

    94. 

  94. 

    95. 

  95. 			case 'sel_data': {

    96. 

  96. 				$type = Get::req('selection_type', DOTY_ALPHANUM, 'users');

    97. 

  97. 

    98. 

  98. 				//$aclManager = new DoceboACLManager();

    99. 

  99. 				$user_select = new UserSelector();

    100. 

  100. 

    101. 

  101. 				if (Get::req('is_updating', DOTY_INT, 0)>0) {

    102. 

  102. 					$ref['rows_filter']['select_all'] = ( Get::req('select_all', DOTY_INT, 0)>0 ? true : false );

    103. 

  103. 					$ref['rows_filter']['selection_type'] = $type;

    104. 

  104. 					//$ref['rows_filter']['selection'] = $user_select->getSelection($_POST);

    105. 

  105. 				} else { //maybe redoundant

    106. 

  106. 					if (!isset($ref['rows_filter']['select_all'])) $ref['rows_filter']['select_all'] = false;

    107. 

  107. 					if (!isset($ref['rows_filter']['selection_type'])) $ref['rows_filter']['selection_type'] = 'groups';

    108. 

  108. 					if (!isset($ref['rows_filter']['selection'])) $ref['rows_filter']['selection'] = array();

    109. 

  109. 					$user_select->resetSelection($ref['rows_filter']['selection']);

    110. 

  110. 					//$ref['users'] = array(); it should already have been set to void array, if non existent

    111. 

  111. 				}

    112. 

  112. 

    113. 

  113. 				if(isset($_POST['cancelselector']))

    114. 

  114. 					Util::jump_to($back_url);

    115. 

  115. 				elseif(isset($_POST['okselector'])) {

    116. 

  116. 					$ref['rows_filter']['selection'] = $user_select->getSelection($_POST);

    117. 

  117. 					Util::jump_to($next_url);

    118. 

  118. 				}

    119. 

  119. 

    120. 

  120. 				//set page

    121. 

  121. 				switch ($type) {

    122. 

  122. 					case 'groups': {

    123. 

  123. 						$user_select->show_user_selector = FALSE;

    124. 

  124. 						$user_select->show_group_selector = TRUE;

    125. 

  125. 						$user_select->show_orgchart_selector = TRUE;

    126. 

  126. 					} break;

    127. 

  127. 					case 'users': {

    128. 

  128. 						$user_select->show_user_selector = TRUE;

    129. 

  129. 						$user_select->show_group_selector = TRUE;

    130. 

  130. 						$user_select->show_orgchart_selector = TRUE;

    131. 

  131. 					} break;

    132. 

  132. 				}

    133. 

  133. 				//$user_select->show_orgchart_simple_selector = FALSE;

    134. 

  134. 				//$user_select->multi_choice = TRUE;

    135. 

  135. 

    136. 

  136. 				$user_select->addFormInfo(

    137. 

  137. 					($type=='users' ? Form::getCheckbox($lang->def('_REPORT_FOR_ALL'), 'select_all', 'select_all', 1, $ref['rows_filter']['select_all']) : '').

    138. 

  138. 					Form::getBreakRow().

    139. 

  139. 					Form::getHidden('selection_type', 'selection_type', $type).

    140. 

  140. 					Form::getHidden('step', 'step', 'sel_data').

    141. 

  141. 					Form::getHidden('is_updating', 'is_updating', 1).

    142. 

  142. 					Form::getHidden('substep', 'substep', 'user_selection').

    143. 

  143. 					Form::getHidden('second_step', 'second_step', 1));

    144. 

  144. 				$user_select->setPageTitle('');

    145. 

  145. 				$user_select->loadSelector(Util::str_replace_once('&', '&amp;', $jump_url),

    146. 

  146. 					false,

    147. 

  147. 					$this->lang->def('_CHOOSE_USER_FOR_REPORT'),

    148. 

  148. 					true);

    149. 

  149. 

    150. 

  150. 			} break;

    151. 

  151. 

    152. 

  152. 		}

    153. 

  153. 	}

    154. 

  154. 

    155. 

  155. 

    156. 

  156. 

    157. 

  157. 

    158. 

  158. 	function get_courses_filter() {

    159. 

  159. 		$back_url = $this->back_url;

    160. 

  160. 		$jump_url = $this->jump_url;

    161. 

  161. 		$next_url = $this->next_url;

    162. 

  162. 

    163. 

  163. 		require_once(_base_.'/lib/lib.form.php');

    164. 

  164. 		require_once($GLOBALS['where_lms'].'/lib/lib.course.php');

    165. 

  165. 		require_once($GLOBALS['where_lms'].'/lib/lib.course_managment.php');

    166. 

  166. 

    167. 

  167. 		$lang =& DoceboLanguage::createInstance('report', 'framework');

    168. 

  168. 

    169. 

  169. 		//$sel = new Course_Manager();

    170. 

  170. 		//$sel->setLink('index.php?modname=report&op=report_rows_filter');

    171. 

  171. 

    172. 

  172. 		if (isset($_POST['undo_filter'])) Util::jump_to($back_url);

    173. 

  173. 

    174. 

  174. 		//set $_POST data in $_SESSION['report_tempdata']

    175. 

  175. 		$selector = new Selector_Course();

    176. 

  176. 

    177. 

  177. 		if (!isset($_SESSION['report_tempdata']['columns_filter'])) {

    178. 

  178. 			$_SESSION['report_tempdata']['columns_filter'] = array(

    179. 

  179. 				'all_courses' => true,

    180. 

  180. 				'selected_courses' => array(),

    181. 

  181. 				'showed_columns' => array('completed'=>true, 'initinere'=>true, 'notstarted'=>true, 'show_percentages'=>true)

    182. 

  182. 			);

    183. 

  183. 		}

    184. 

  184. 		$ref =& $_SESSION['report_tempdata']['columns_filter'];

    185. 

  185. 

    186. 

  186. 		if (isset($_POST['update_tempdata'])) {

    187. 

  187. 			$selector->parseForState($_POST);

    188. 

  188. 			$temp = $selector->getSelection($_POST);

    189. 

  189. 			$ref['selected_courses'] = $temp;

    190. 

  190. 			$ref['all_courses'] = (Get::req('all_courses', DOTY_INT, 1)==1 ? true : false);

    191. 

  191. 			$ref['showed_columns'] = array(

    192. 

  192. 				'completed' => (Get::req('cols_completed', DOTY_INT, 0)>0 ? true : false),

    193. 

  193. 				'initinere' => (Get::req('cols_initinere', DOTY_INT, 0)>0 ? true : false),

    194. 

  194. 				'notstarted' => (Get::req('cols_notstarted', DOTY_INT, 0)>0 ? true : false),

    195. 

  195. 				'show_percentages' => (Get::req('cols_show_percentages', DOTY_INT, 0)>0 ? true : false));

    196. 

  196. 		}

    197. 

  197. 		else

    198. 

  198. 		{

    199. 

  199. 			$selector->resetSelection($ref['selected_courses']);

    200. 

  200. 		}

    201. 

  201. 

    202. 

  202. 		//back to columns category selection

    203. 

  203. 		if (isset($_POST['undo_filter'])) {

    204. 

  204. 			Util::jump_to($back_url);

    205. 

  205. 		}

    206. 

  206. 

    207. 

  207. 		//filter setting done, go to next step

    208. 

  208. 		if (isset($_POST['import_filter']) || isset($_POST['show_filter']) || isset($_POST['pre_filter'])) {

    209. 

  209. 			$temp_url = $next_url;

    210. 

  210. 			if (isset($_POST['pre_filter'])) $temp_url.='&show=1&nosave=1';

    211. 

  211. 			if (isset($_POST['show_filter'])) $temp_url.='&show=1';

    212. 

  212. 			Util::jump_to($temp_url);

    213. 

  213. 		}

    214. 

  214. 

    215. 

  215. 		$temp = count($ref['selected_courses']);

    216. 

  216. 

    217. 

  217. 

    218. 

  218. 		$box = new ReportBox('courses_selector');

    219. 

  219. 		$box->title = $this->lang->def('_COURSES_SELECTION_TITLE');

    220. 

  220. 		$box->description = false;

    221. 

  221. 

    222. 

  222. 		$boxlang =& DoceboLanguage::createInstance('report', 'framework');

    223. 

  223. 		$box->body .= '<div class="fc_filter_line filter_corr">';

    224. 

  224. 		$box->body .= '<input id="all_courses" name="all_courses" type="radio" value="1" '.($ref['all_courses'] ? 'checked="checked"' : '').' />';

    225. 

  225. 		$box->body .= ' <label for="all_courses">'.$boxlang->def('_ALL_COURSES').'</label>';

    226. 

  226. 		$box->body .= ' <input id="sel_courses" name="all_courses" type="radio" value="0" '.($ref['all_courses'] ? '' : 'checked="checked"').' />';

    227. 

  227. 		$box->body .= ' <label for="sel_courses">'.$boxlang->def('_SEL_COURSES').'</label>';

    228. 

  228. 		$box->body .= '</div>';

    229. 

  229. 		$box->body .= '<div id="selector_container"'.($ref['all_courses'] ? ' style="display:none"' : '').'>';

    230. 

  230. 		$box->body .= $selector->loadCourseSelector(true).'</div>';

    231. 

  231. 

    232. 

  232. 		$box->footer = $boxlang->def('_CURRENT_SELECTION').':&nbsp;<span id="csel_foot">'.($ref['all_courses'] ? $boxlang->def('_ALL') : ($temp!='' ? $temp : '0')).'</span>';

    233. 

  233. 

    234. 

  234. 		YuiLib::load(array(

    235. 

  235. 			'yahoo'           => 'yahoo-min.js',

    236. 

  236. 			'yahoo-dom-event' => 'yahoo-dom-event.js',

    237. 

  237. 			'element'         => 'element-beta-min.js',

    238. 

  238. 			'datasource'      => 'datasource-beta-min.js',

    239. 

  239. 			'connection'      => 'connection-min.js',

    240. 

  240. 			'event'           => 'event-min.js',

    241. 

  241. 			'json'            => 'json-beta-min.js'

    242. 

  242. 		), array(

    243. 

  243. 			'/assets/skins/sam' => 'skin.css'

    244. 

  244. 		));

    245. 

  245. 		addJs($GLOBALS['where_lms_relative'].'/admin/modules/report/','courses_filter.js');

    246. 

  246. 

    247. 

  247. 		cout('<script type="text/javascript"> '."\n".

    248. 

  248. 		'var courses_count="'.($temp!='' ? $temp : '0').'";'."\n".

    249. 

  249. 		'var courses_all="'.$boxlang->def('_ALL').'";'."\n".

    250. 

  250. 		'YAHOO.util.Event.addListener(window, "load", function(e){ courses_selector_init(); });'."\n".

    251. 

  251. 		'</script>', 'page_head');

    252. 

  252. 

    253. 

  253. 		//columns selection

    254. 

  254. 		$col_box = new ReportBox('columns_selection');

    255. 

  255. 		$col_box->title = $this->lang->def('_REPORT_SEL_COLUMNS');

    256. 

  256. 		$col_box->description = $this->lang->def('_SELECT_THE_DATA_COL_NEEDED');

    257. 

  257. 

    258. 

  258. 		$col_box->body .= Form::getOpenFieldSet($this->lang->def('_STATUS'));

    259. 

  259. 		$col_box->body .= Form::getCheckBox(Lang::t('_USER_STATUS_SUBS', 'course'), 'cols_notstarted', 'cols_notstarted', 1, $ref['showed_columns']['notstarted']);

    260. 

  260. 		$col_box->body .= Form::getCheckBox(Lang::t('_USER_STATUS_BEGIN', 'course'), 'cols_initinere', 'cols_initinere', 1, $ref['showed_columns']['initinere']);

    261. 

  261. 		$col_box->body .= Form::getCheckBox(Lang::t('_USER_STATUS_END', 'course'), 'cols_completed', 'cols_completed', 1, $ref['showed_columns']['completed']);

    262. 

  262. 		$col_box->body .= Form::getCheckBox(Lang::t('_PERCENTAGE', 'course'), 'cols_show_percentages', 'cols_show_percentages', 1, $ref['showed_columns']['show_percentages']);

    263. 

  263. 		$col_box->body .= Form::getCloseFieldSet();

    264. 

  264. 

    265. 

  265. 		cout(Form::openForm('first_step_user_filter', $jump_url, false, 'post').

    266. 

  266. 			$box->get().

    267. 

  267. 			$col_box->get().

    268. 

  268. 			Form::getHidden('update_tempdata', 'update_tempdata', 1));

    269. 

  269. 	}

    270. 

  270. 

    271. 

  271. 	function show_report_courses($data = NULL, $other = '') {

    272. 

  272. 		if ($data===NULL)

    273. 

  273. 			cout( $this->_get_courses_query() );

    274. 

  274. 		else

    275. 

  275. 			cout( $this->_get_courses_query('html', $data, $other) );

    276. 

  276. 	}

    277. 

  277. 

    278. 

  278. 	function _get_courses_query($type = 'html', $report_data = NULL, $other = '') {

    279. 

  279. 		require_once($GLOBALS['where_lms'].'/lib/lib.course.php');

    280. 

  280. 		require_once(dirname(__FILE__).'/report_tableprinter.php');

    281. 

  281. 

    282. 

  282. 		if ($report_data==NULL) $ref =& $_SESSION['report_tempdata']; else $ref =& $report_data;

    283. 

  283. 

    284. 

  284. 		$fw  = $GLOBALS['prefix_fw'];

    285. 

  285. 		$lms = $GLOBALS['prefix_lms'];

    286. 

  286. 

    287. 

  287. 		$sel_all = $ref['rows_filter']['select_all'];

    288. 

  288. 		$sel_type = $ref['rows_filter']['selection_type'];

    289. 

  289. 		$selection = $ref['rows_filter']['selection'];

    290. 

  290. 

    291. 

  291. 		$all_courses = $ref['columns_filter']['all_courses'];

    292. 

  292. 		$courses = $ref['columns_filter']['selected_courses'];

    293. 

  293. 		$cols =& $ref['columns_filter']['showed_columns'];

    294. 

  294. 

    295. 

  295. 		$acl = new DoceboACLManager();

    296. 

  296. 		$html = '';

    297. 

  297. 

    298. 

  298. 		$man = new Man_Course();

    299. 

  299. 		$courses_codes = $man->getAllCourses();

    300. 

  300. 		if ($all_courses) {

    301. 

  301. 			$courses = array();

    302. 

  302. 			foreach ($courses_codes as $key=>$val) $courses[] = $key;

    303. 

  303. 		}

    304. 

  304. /*

    305. 

  305. 		if(Docebo::user()->getUserLevelId() != ADMIN_GROUP_GODADMIN) {

    306. 

  306. 

    307. 

  307. 			// if the usre is a subadmin with only few course assigned

    308. 

  308. 			require_once(_base_.'/lib/lib.preference.php');

    309. 

  309. 			$adminManager = new AdminPreference();

    310. 

  310. 			$admin_tree = $adminManager->getAdminTree(Docebo::user()->getIdST());

    311. 

  311. 			$courses = array_intersect($courses, $admin_tree['courses']);

    312. 

  312. 		}

    313. 

  313. */

    314. 

  314. 		$increment = 0;

    315. 

  315. 		if ($cols['completed']) $increment++;

    316. 

  316. 		if ($cols['initinere']) $increment++;

    317. 

  317. 		if ($cols['notstarted']) $increment++;

    318. 

  318. 		if ($cols['show_percentages']) $increment = $increment*2;

    319. 

  319. /*

    320. 

  320. 		//admin users filter

    321. 

  321. 		$acl_man = Docebo::user()->getACLManager();

    322. 

  322. 		$userlevelid = Docebo::user()->getUserLevelId();

    323. 

  323. 		if ( $userlevelid != ADMIN_GROUP_GODADMIN ) {

    324. 

  324. 			require_once(_base_.'/lib/lib.preference.php');

    325. 

  325. 			$adminManager = new AdminPreference();

    326. 

  326. 			$admin_tree = $adminManager->getAdminTree(Docebo::user()->getIdST());

    327. 

  327. 			$admin_users = $acl_man->getAllUsersFromSelection($admin_tree);

    328. 

  328. 			$admin_users = array_unique($admin_users);

    329. 

  329. 		}*/

    330. 

  330. 		$userlevelid = Docebo::user()->getUserLevelId();

    331. 

  331. 		if( $userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {

    332. 

  332. 			//filter users

    333. 

  333. 			$alluser = false;

    334. 

  334. 			require_once(_base_.'/lib/lib.preference.php');

    335. 

  335. 			$adminManager = new AdminPreference();

    336. 

  336. 			$admin_users = $adminManager->getAdminUsers(Docebo::user()->getIdST());

    337. 

  337. 			//$user_selected = array_intersect($user_selected, $admin_users);

    338. 

  338. 			//unset($admin_users);

    339. 

  339. 

    340. 

  340. 			//filter courses

    341. 

  341. 			$admin_courses = $adminManager->getAdminCourse(Docebo::user()->getIdST());

    342. 

  342. 			if ($all_courses)

    343. 

  343. 			{

    344. 

  344. 				$all_courses = false;

    345. 

  345. 				$rs = sql_query("SELECT idCourse FROM %lms_course");

    346. 

  346. 				$course_selected = array();

    347. 

  347. 				while (list($id_course) = sql_fetch_row($rs)) { $course_selected[] = $id_course; }

    348. 

  348. 			}

    349. 

  349. 

    350. 

  350. 			if(isset($admin_courses['course'][0]))

    351. 

  351. 			{

    352. 

  352. 				//No filter

    353. 

  353. 			}

    354. 

  354. 			elseif(isset($admin_courses['course'][-1]))

    355. 

  355. 			{

    356. 

  356. 				require_once(_lms_.'/lib/lib.catalogue.php');

    357. 

  357. 				$cat_man = new Catalogue_Manager();

    358. 

  358. 

    359. 

  359. 				$user_catalogue = $cat_man->getUserAllCatalogueId(Docebo::user()->getIdSt());

    360. 

  360. 				if(count($user_catalogue) > 0)

    361. 

  361. 				{

    362. 

  362. 					$courses = array(0);

    363. 

  363. 

    364. 

  364. 					foreach($user_catalogue as $id_cat)

    365. 

  365. 					{

    366. 

  366. 						$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);

    367. 

  367. 

    368. 

  368. 						$courses = array_merge($courses, $catalogue_course);

    369. 

  369. 					}

    370. 

  370. 

    371. 

  371. 					foreach($courses as $id_course)

    372. 

  372. 						if($id_course != 0)

    373. 

  373. 							$admin_courses['course'][$id_course] = $id_course;

    374. 

  374. 				}

    375. 

  375. 				elseif(Get::sett('on_catalogue_empty', 'off') == 'on')

    376. 

  376. 				{

    377. 

  377. 					//No filter

    378. 

  378. 				}

    379. 

  379. 

    380. 

  380. 				if(!empty($admin_courses['course']))

    381. 

  381. 				{

    382. 

  382. 					$_clist = array_values($admin_courses['course']);

    383. 

  383. 					$course_selected = array_intersect($course_selected, $_clist);

    384. 

  384. 				}

    385. 

  385. 				else

    386. 

  386. 					$course_selected = array();

    387. 

  387. 			}

    388. 

  388. 			else

    389. 

  389. 			{

    390. 

  390. 				$array_courses = array();

    391. 

  391. 				$array_courses = array_merge($array_courses, $admin_courses['course']);

    392. 

  392. 

    393. 

  393. 				if(!empty($admin_courses['coursepath']))

    394. 

  394. 				{

    395. 

  395. 					require_once(_lms_.'/lib/lib.coursepath.php');

    396. 

  396. 					$path_man = new Catalogue_Manager();

    397. 

  397. 					$coursepath_course =& $path_man->getAllCourses($admin_courses['coursepath']);

    398. 

  398. 					$array_courses = array_merge($array_courses, $coursepath_course);

    399. 

  399. 				}

    400. 

  400. 				if(!empty($admin_courses['catalogue']))

    401. 

  401. 				{

    402. 

  402. 					require_once(_lms_.'/lib/lib.catalogue.php');

    403. 

  403. 					$cat_man = new Catalogue_Manager();

    404. 

  404. 					foreach($admin_courses['catalogue'] as $id_cat)

    405. 

  405. 					{

    406. 

  406. 						$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);

    407. 

  407. 						$array_courses = array_merge($array_courses, $catalogue_course);

    408. 

  408. 					}

    409. 

  409. 				}

    410. 

  410. 				$admin_courses['course'] = array_merge($admin_courses['course'], $array_courses);

    411. 

  411. 

    412. 

  412. 				if(!empty($admin_courses['course']))

    413. 

  413. 				{

    414. 

  414. 					$_clist = array_values($admin_courses['course']);

    415. 

  415. 					$course_selected = array_intersect($course_selected, $_clist);

    416. 

  416. 				}

    417. 

  417. 				else

    418. 

  418. 					$course_selected = array();

    419. 

  419. 			}

    420. 

  420. 

    421. 

  421. 			unset($admin_courses);

    422. 

  422. 		}

    423. 

  423. 

    424. 

  424. 		switch ($sel_type) {

    425. 

  425. 

    426. 

  426. 			case 'groups': {

    427. 

  427. 

    428. 

  428. 				//retrieve all labels

    429. 

  429. 				$orgchart_labels = array();

    430. 

  430. 				$query = "SELECT * FROM ".$fw."_org_chart WHERE lang_code='".getLanguage()."'";

    431. 

  431. 				$res = sql_query($query);

    432. 

  432. 				while ($row = mysql_fetch_assoc($res)) {

    433. 

  433. 					$orgchart_labels[$row['id_dir']] = $row['translation'];

    434. 

  434. 				}

    435. 

  435. 

    436. 

  436. 				$labels = array();

    437. 

  437. 				$query = "SELECT * FROM ".$fw."_group WHERE (hidden='false' OR groupid LIKE '/oc_%' OR groupid LIKE '/ocd_%') AND type='free'";

    438. 

  438. 				$res = sql_query($query);

    439. 

  439. 				while ($row = mysql_fetch_assoc($res)) {

    440. 

  440. 					if ($row['hidden']=='false') {

    441. 

  441. 						$labels[$row['idst']] = $acl->relativeId($row['groupid']);

    442. 

  442. 					} else {

    443. 

  443. 						$temp = explode("_", $row['groupid']); //echo '<div>'.print_r($temp,true).'</div>';

    444. 

  444. 						if ($temp[0]=='/oc') {

    445. 

  445. 							$labels[$row['idst']] = ($temp[1]!=0 ? $orgchart_labels[$temp[1]] : '');

    446. 

  446. 						} elseif ($temp[0]=='/ocd') {

    447. 

  447. 							$labels[$row['idst']] = ($temp[1]!=0 ? $orgchart_labels[$temp[1]] : '');

    448. 

  448. 						}

    449. 

  449. 					}

    450. 

  450. 				}

    451. 

  451. 

    452. 

  452. 				$tot_size = 2;

    453. 

  453. 				$head1 = array( array('colspan'=>2, 'value'=>$this->lang->def('_GROUPS')) );

    454. 

  454. 				$head2 = array($this->lang->def('_NAME'), $this->lang->def('_TOTAL'));

    455. 

  455. 

    456. 

  456. 				foreach ($courses as $course) {

    457. 

  457. 					$head1[] = array(

    458. 

  458. 						'value' => ( $courses_codes[$course]['code'] ? '['.$courses_codes[$course]['code'].'] ' : '' )

    459. 

  459. 									.$courses_codes[$course]['name'],

    460. 

  460. 						'colspan' => $increment

    461. 

  461. 					);

    462. 

  462. 

    463. 

  463. 					if ($cols['completed']) $head2[] = $this->lang->def('_USER_STATUS_END');

    464. 

  464. 					if ($cols['completed'] && $cols['show_percentages']) $head2[] = _PERCENT_SIMBOL;

    465. 

  465. 					if ($cols['initinere']) $head2[] = $this->lang->def('_USER_STATUS_BEGIN');

    466. 

  466. 					if ($cols['initinere'] && $cols['show_percentages']) $head2[] = _PERCENT_SIMBOL;

    467. 

  467. 					if ($cols['notstarted']) $head2[] = $this->lang->def('_USER_STATUS_SUBS');

    468. 

  468. 					if ($cols['notstarted'] && $cols['show_percentages']) $head2[] = _PERCENT_SIMBOL;

    469. 

  469. 

    470. 

  470. 					$tot_size += $increment;

    471. 

  471. 				}

    472. 

  472. 

    473. 

  473. 				$buffer = new ReportTablePrinter($type, true);

    474. 

  474. 				$buffer->openTable('','');

    475. 

  475. 

    476. 

  476. 				$buffer->openHeader();

    477. 

  477. 				$buffer->addHeader($head1);

    478. 

  478. 				$buffer->addHeader($head2);

    479. 

  479. 				$buffer->closeHeader();

    480. 

  480. 

    481. 

  481. 				$tot_users = 0;

    482. 

  482. 				$course_stats = array();

    483. 

  483. 

    484. 

  484. 				//for each group, retrieve label and user statistics

    485. 

  485. 				foreach ($selection as $dir_id=>$group_id) {

    486. 

  486. 					$group_users = $acl->getGroupAllUser($group_id);

    487. 

  487. 					if ( $userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) { $group_users = array_intersect($group_users, $admin_users); }

    488. 

  488. 					$users_num = count($group_users);

    489. 

  489. 

    490. 

  490. 					$line = array();

    491. 

  491. 					$line[] = $labels[$group_id];

    492. 

  492. 					$line[] = $users_num;

    493. 

  493. 					$tot_users += $users_num;

    494. 

  494. 

    495. 

  495. 					if (count($group_users)>0) {

    496. 

  496. 						$query = "SELECT cu.idUser, cu.idCourse, cu.status ".

    497. 

  497. 							" FROM ".$lms."_courseuser as cu, ".$lms."_course as c, ".$fw."_user as u ".

    498. 

  498. 							" WHERE cu.idUser=u.idst AND cu.idCourse=c.idCourse ".

    499. 

  499. 							" AND u.idst IN (".implode(",", $group_users).") ".

    500. 

  500. 							(!$all_courses ? " AND c.idCourse IN (".implode(",", $courses)." )" : "");

    501. 

  501. 

    502. 

  502. 						$res = sql_query($query);

    503. 

  503. 

    504. 

  504. 						//$tot_completed = 0;

    505. 

  505. 						while ($row = mysql_fetch_assoc($res) ) {

    506. 

  506. 							if (!isset($course_stats[$row['idCourse']][$group_id])) {

    507. 

  507. 								$course_stats[$row['idCourse']][$group_id] = array(

    508. 

  508. 									'completed' => 0,

    509. 

  509. 									'initinere' => 0,

    510. 

  510. 									'notstarted' => 0,

    511. 

  511. 									'total' => 0

    512. 

  512. 								);

    513. 

  513. 							}

    514. 

  514. 							switch ((int)$row['status']) {

    515. 

  515. 								case 2: $course_stats[$row['idCourse']][$group_id]['completed']++; break;

    516. 

  516. 								case 1: $course_stats[$row['idCourse']][$group_id]['initinere']++; break;

    517. 

  517. 								case 0: $course_stats[$row['idCourse']][$group_id]['notstarted']++; break;

    518. 

  518. 							}

    519. 

  519. 							$course_stats[$row['idCourse']][$group_id]['total']++;

    520. 

  520. 						}

    521. 

  521. 

    522. 

  522. 						foreach ($courses as $course) {

    523. 

  523. 							if (isset($course_stats[$course][$group_id])) {

    524. 

  524. 								if ($course_stats[$course][$group_id]['total']==0) $dividend = 1; else $dividend = $course_stats[$course][$group_id]['total'];

    525. 

  525. 								if ($cols['completed']) $line[] = $course_stats[$course][$group_id]['completed'];

    526. 

  526. 								if ($cols['completed'] && $cols['show_percentages']) $line[] = number_format(100.0*$course_stats[$course][$group_id]['completed']/$dividend, 2, ',', '')._PERCENT_SIMBOL;

    527. 

  527. 								if ($cols['initinere']) $line[] = $course_stats[$course][$group_id]['initinere'];

    528. 

  528. 								if ($cols['initinere'] && $cols['show_percentages']) $line[] = number_format(100.0*$course_stats[$course][$group_id]['initinere']/$dividend, 2, ',', '')._PERCENT_SIMBOL;

    529. 

  529. 								if ($cols['notstarted']) $line[] = $course_stats[$course][$group_id]['notstarted'];

    530. 

  530. 								if ($cols['notstarted'] && $cols['show_percentages']) $line[] = number_format(100.0*$course_stats[$course][$group_id]['notstarted']/$dividend, 2, ',', '')._PERCENT_SIMBOL;

    531. 

  531. 							} else {

    532. 

  532. 								if ($cols['completed']) $line[] = '0';

    533. 

  533. 								if ($cols['completed'] && $cols['show_percentages']) $line[] = '0,00%';

    534. 

  534. 								if ($cols['initinere']) $line[] = '0';

    535. 

  535. 								if ($cols['initinere'] && $cols['show_percentages']) $line[] = '0,00%';

    536. 

  536. 								if ($cols['notstarted']) $line[] = '0';

    537. 

  537. 								if ($cols['notstarted'] && $cols['show_percentages']) $line[] = '0,00%';

    538. 

  538. 							}

    539. 

  539. 						}

    540. 

  540. 

    541. 

  541. 						//$line[] = $tot_completed;

    542. 

  542. 

    543. 

  543. 					} else {

    544. 

  544. 						foreach ($courses as $course) {

    545. 

  545. 							if ($cols['completed']) $line[] = '0';

    546. 

  546. 							if ($cols['completed'] && $cols['show_percentages']) $line[] = '0,00%';

    547. 

  547. 							if ($cols['initinere']) $line[] = '0';

    548. 

  548. 							if ($cols['initinere'] && $cols['show_percentages']) $line[] = '0,00%';

    549. 

  549. 							if ($cols['notstarted']) $line[] = '0';

    550. 

  550. 							if ($cols['notstarted'] && $cols['show_percentages']) $line[] = '0,00%';

    551. 

  551. 						}

    552. 

  552. 					}

    553. 

  553. 					$buffer->addLine($line);

    554. 

  554. 

    555. 

  555. 

    556. 

  556. 				}

    557. 

  557. 

    558. 

  558. 				$buffer->closeBody();

    559. 

  559. 				//echo '<pre>'.print_r($course_stats,true).'</pre>';

    560. 

  560. 				//calc totals

    561. 

  561. 				$foot = array('', $tot_users);

    562. 

  562. 				foreach ($courses as $course) {

    563. 

  563. 

    564. 

  564. 					$completed_total = 0;

    565. 

  565. 					$initinere_total = 0;

    566. 

  566. 					$notstarted_total = 0;

    567. 

  567. 					$total_total = 0;

    568. 

  568. 					foreach ($selection as $dir_id=>$group_id) {

    569. 

  569. 						$completed_total += (isset($course_stats[$course][$group_id]['completed']) ? $course_stats[$course][$group_id]['completed'] : 0);

    570. 

  570. 						$initinere_total += (isset($course_stats[$course][$group_id]['initinere']) ? $course_stats[$course][$group_id]['initinere'] : 0);

    571. 

  571. 						$notstarted_total += (isset($course_stats[$course][$group_id]['notstarted']) ? $course_stats[$course][$group_id]['notstarted'] : 0);

    572. 

  572. 						$total_total += (isset($course_stats[$course][$group_id]['total']) ? $course_stats[$course][$group_id]['total'] : 0);

    573. 

  573. 					}

    574. 

  574. 					if ($cols['completed']) $foot[] = $completed_total;

    575. 

  575. 					if ($cols['completed'] && $cols['show_percentages']) $foot[] = ($total_total!=0 ? number_format(100.0*$completed_total/$total_total, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL);

    576. 

  576. 					if ($cols['initinere']) $foot[] = $initinere_total;

    577. 

  577. 					if ($cols['initinere'] && $cols['show_percentages']) $foot[] = ($total_total!=0 ? number_format(100.0*$initinere_total/$total_total, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL);

    578. 

  578. 					if ($cols['notstarted']) $foot[] = $notstarted_total;

    579. 

  579. 					if ($cols['notstarted'] && $cols['show_percentages']) $foot[] = ($total_total!=0 ? number_format(100.0*$notstarted_total/$total_total, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL);

    580. 

  580. 				}

    581. 

  581. 

    582. 

  582. 				$buffer->setFoot($foot);

    583. 

  583. 				$buffer->closeTable();

    584. 

  584. 				$html .= $buffer->get();

    585. 

  585. 			} break;

    586. 

  586. 

    587. 

  587. 

    588. 

  588. 

    589. 

  589. 			case 'users': {

    590. 

  590. 

    591. 

  591. 				$temp = array();

    592. 

  592. 				// resolve the user selection

    593. 

  593. 				$users 	=& $acl->getAllUsersFromIdst($selection);

    594. 

  594. 				if ( $userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) { $users = array_intersect($users, $admin_users); }

    595. 

  595. 				if (count($users)<=0) {

    596. 

  596. 					$html .= '<p>'.$this->lang->def('_EMPTY_SELECTION').'</p>';

    597. 

  597. 					break;

    598. 

  598. 				}

    599. 

  599. 

    600. 

  600. 				$query = "SELECT cu.idUser, cu.idCourse, cu.status, u.userId, c.code, u.firstname, u.lastname ".

    601. 

  601. 					" FROM ( ".$lms."_courseuser as cu ".

    602. 

  602. 					" JOIN  ".$lms."_course as c ON ( cu.idCourse = c.idCourse) ) ".

    603. 

  603. 					" JOIN ".$fw."_user as u ON (cu.idUser = u.idst)  ".

    604. 

  604. 					" WHERE 1 ".

    605. 

  605. 					" AND cu.idCourse IN (".implode(",", $courses).") ".

    606. 

  606. 					($sel_all ? "" : " AND idUser IN (".implode(",", $users).")")."";

    607. 

  607. 

    608. 

  608. 				$res = sql_query($query);

    609. 

  609. 

    610. 

  610. 				while ($row = mysql_fetch_array($res) ) {

    611. 

  611. 

    612. 

  612. 					if(!isset($temp[$row['idUser']])) {

    613. 

  613. 						$temp[$row['idUser']] = array (

    614. 

  614. 							'username' => $acl->relativeId($row['userId']),

    615. 

  615. 							'fullname' => $row['lastname'].' '.$row['firstname'],

    616. 

  616. 							'courses' => array()

    617. 

  617. 						);

    618. 

  618. 					}

    619. 

  619. 					$temp[$row['idUser']]['courses'][$row['idCourse']] = $row['status'];

    620. 

  620. 				}

    621. 

  621. 				//echo '<pre>';

    622. 

  622. 				//print_r($temp);

    623. 

  623. 

    624. 

  624. 				//draw table

    625. 

  625. 				$tot_size = 1;

    626. 

  626. 				$head2 = array($this->lang->def('_USERNAME'), $this->lang->def('_FULLNAME'));

    627. 

  627. 				$head1 = array(array('colspan'=>2, 'value'=>$this->lang->def('_USER')));

    628. 

  628. 				foreach ($courses as $course) {

    629. 

  629. 					$head1[] = array(

    630. 

  630. 						'value' => ( $courses_codes[$course]['code'] ? '['.$courses_codes[$course]['code'].'] ' : '' )

    631. 

  631. 									.$courses_codes[$course]['name'],

    632. 

  632. 						'colspan' => $increment

    633. 

  633. 					);

    634. 

  634. 

    635. 

  635. 					if ($cols['completed']) $head2[] = $this->lang->def('_USER_STATUS_END');

    636. 

  636. 					if ($cols['completed'] && $cols['show_percentages']) $head2[] = _PERCENT_SIMBOL;

    637. 

  637. 					if ($cols['initinere']) $head2[] = $this->lang->def('_USER_STATUS_BEGIN');

    638. 

  638. 					if ($cols['initinere'] && $cols['show_percentages']) $head2[] = _PERCENT_SIMBOL;

    639. 

  639. 					if ($cols['notstarted']) $head2[] = $this->lang->def('_USER_STATUS_SUBS');

    640. 

  640. 					if ($cols['notstarted'] && $cols['show_percentages']) $head2[] = _PERCENT_SIMBOL;

    641. 

  641. 

    642. 

  642. 					$tot_size += $increment;

    643. 

  643. 				}

    644. 

  644. 

    645. 

  645. 				$buffer = new ReportTablePrinter($type, true);

    646. 

  646. 				$buffer->openTable('','');

    647. 

  647. 

    648. 

  648. 				$buffer->openHeader();

    649. 

  649. 				$buffer->addHeader($head1);

    650. 

  650. 				$buffer->addHeader($head2);

    651. 

  651. 				$buffer->closeHeader();

    652. 

  652. 

    653. 

  653. 				$completed_total = array();

    654. 

  654. 				$initinere_total = array();

    655. 

  655. 				$notstarted_total = array();

    656. 

  656. 				$courses_total = array();

    657. 

  657. 

    658. 

  658. 				foreach($courses as $course) {

    659. 

  659. 					$completed_total[$course] = 0;

    660. 

  660. 					$initinere_total[$course] = 0;

    661. 

  661. 					$notstarted_total[$course] = 0;

    662. 

  662. 					$courses_total[$course] = 0;

    663. 

  663. 				}

    664. 

  664. 

    665. 

  665. 				$buffer->openBody();

    666. 

  666. 				foreach ($temp as $id_user => $table_row) {

    667. 

  667. 					$line = array();

    668. 

  668. 					$line[] = $table_row['username'];

    669. 

  669. 					$line[] = $table_row['fullname'];

    670. 

  670. 					foreach ($courses as $course) {

    671. 

  671. 						if(isset($table_row['courses'][$course])) {

    672. 

  672. 

    673. 

  673. 							if ($cols['completed']) $line[] = ($table_row['courses'][$course] == 2 ? 1 : 0);

    674. 

  674. 							if ($cols['completed'] && $cols['show_percentages']) $line[] = ($table_row['courses'][$course] == 2 ? '100'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL : '0'._PERCENT_SIMBOL);

    675. 

  675. 							if ($cols['initinere']) $line[] = ($table_row['courses'][$course] == 1 ? 1 : 0);

    676. 

  676. 							if ($cols['initinere'] && $cols['show_percentages']) $line[] = ($table_row['courses'][$course] == 1 ? '100'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL : '0'._PERCENT_SIMBOL);

    677. 

  677. 							if ($cols['notstarted']) $line[] = ($table_row['courses'][$course] == 0 ? 1 : 0);

    678. 

  678. 							if ($cols['notstarted'] && $cols['show_percentages']) $line[] = ($table_row['courses'][$course] == 0 ? '100'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL : '0'._PERCENT_SIMBOL);

    679. 

  679. 

    680. 

  680. 							switch ((int)$table_row['courses'][$course]) {

    681. 

  681. 								case 2: if (isset($completed_total[$course])) $completed_total[$course] += 1; else $completed_course[$course] = 1; break;

    682. 

  682. 								case 1: if (isset($initinere_total[$course])) $initinere_total[$course] += 1; else $initinere_course[$course] = 1; break;

    683. 

  683. 								case 0: if (isset($notstarted_total[$course])) $notstarted_total[$course] += 1; else $notstarted_course[$course] = 1; break;

    684. 

  684. 							}

    685. 

  685. 

    686. 

  686. 							if (isset($courses_total[$course])) $courses_total[$course] += 1; else $courses_total[$course] = 1;

    687. 

  687. 						} else {

    688. 

  688. 

    689. 

  689. 							if ($cols['completed']) $line[] = '0';

    690. 

  690. 							if ($cols['completed'] && $cols['show_percentages']) $line[] = '0'._PERCENT_SIMBOL;

    691. 

  691. 							if ($cols['initinere']) $line[] = '0';

    692. 

  692. 							if ($cols['initinere'] && $cols['show_percentages']) $line[] = '0'._PERCENT_SIMBOL;

    693. 

  693. 							if ($cols['notstarted']) $line[] = '0';

    694. 

  694. 							if ($cols['notstarted'] && $cols['show_percentages']) $line[] = '0'._PERCENT_SIMBOL;

    695. 

  695. 

    696. 

  696. 							if (isset($courses_total[$course])) $courses_total[$course] += 1; else $courses_total[$course] = 1;

    697. 

  697. 						}

    698. 

  698. 					}

    699. 

  699. 					$buffer->addLine($line);

    700. 

  700. 				}

    701. 

  701. 				$buffer->closeBody();

    702. 

  702. 

    703. 

  703. 				$totals_line = array('', '');

    704. 

  704. 				foreach ($courses as $course) {

    705. 

  705. 

    706. 

  706. 					$completed_num = isset($completed_total[$course]) ? $completed_total[$course] : '0';

    707. 

  707. 					$initinere_num = isset($initinere_total[$course]) ? $initinere_total[$course] : '0';

    708. 

  708. 					$notstarted_num = isset($notstarted_total[$course]) ? $notstarted_total[$course] : '0';

    709. 

  709. 					$total_num = isset($courses_total[$course]) ? $courses_total[$course] : '0';

    710. 

  710. 

    711. 

  711. 					if ($cols['completed']) $totals_line[] = $completed_num;

    712. 

  712. 					if ($cols['completed'] && $cols['show_percentages']) $totals_line[] = $total_num!=0 ? number_format(100.0*$completed_num/$total_num, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL;

    713. 

  713. 					if ($cols['initinere']) $totals_line[] = $initinere_num;

    714. 

  714. 					if ($cols['initinere'] && $cols['show_percentages']) $totals_line[] = $total_num!=0 ? number_format(100.0*$initinere_num/$total_num, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL;

    715. 

  715. 					if ($cols['notstarted']) $totals_line[] = $notstarted_num;

    716. 

  716. 					if ($cols['notstarted'] && $cols['show_percentages']) $totals_line[] = $total_num!=0 ? number_format(100.0*$notstarted_num/$total_num, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL;

    717. 

  717. 				}

    718. 

  718. 				$buffer->setFoot($totals_line);

    719. 

  719. 

    720. 

  720. 				$buffer->closeTable();

    721. 

  721. 

    722. 

  722. 				$html .= $buffer->get();

    723. 

  723. 			} break;

    724. 

  724. 

    725. 

  725. 		}

    726. 

  726. 

    727. 

  727. 

    728. 

  728. 		return $html;

    729. 

  729. 

    730. 

  730. 	}

    731. 

  731. 

    732. 

  732. 

    733. 

  733. 	//----------------------------------------------------------------------------

    734. 

  734. 

    735. 

  735. 	function show_report_coursecategories($data = NULL, $other = '') {

    736. 

  736. 		if ($data===NULL)

    737. 

  737. 			cout( $this->_get_coursecategories_query() );

    738. 

  738. 		else

    739. 

  739. 			cout( $this->_get_coursecategories_query('html', $data, $other) );

    740. 

  740. 	}

    741. 

  741. 

    742. 

  742. 	function get_coursecategories_filter() {

    743. 

  743. 		$back_url = $this->back_url;

    744. 

  744. 		$jump_url = $this->jump_url;

    745. 

  745. 		$next_url = $this->next_url;

    746. 

  746. 

    747. 

  747. 		require_once(_base_.'/lib/lib.form.php');

    748. 

  748. 		require_once($GLOBALS['where_lms'].'/lib/lib.course.php');

    749. 

  749. 		require_once($GLOBALS['where_lms'].'/lib/category/lib.categorytree.php');

    750. 

  750. 

    751. 

  751. 		$lang =& DoceboLanguage::createInstance('report', 'framework');

    752. 

  752. 

    753. 

  753. 		if (isset($_POST['undo_filter'])) Util::jump_to($back_url);

    754. 

  754. 

    755. 

  755. 

    756. 

  756. 		if (!isset($_SESSION['report_tempdata']['columns_filter'])) {

    757. 

  757. 			$_SESSION['report_tempdata']['columns_filter'] = array(

    758. 

  758. 				'all_categories' => true,

    759. 

  759. 				'selected_categories' => array(),

    760. 

  760. 				'showed_columns' => array(/*'completed'=>true, 'initinere'=>true, 'notstarted'=>true, 'show_percentages'=>true*/)

    761. 

  761. 			);

    762. 

  762. 		}

    763. 

  763. 		$ref =& $_SESSION['report_tempdata']['columns_filter'];

    764. 

  764. 

    765. 

  765. 		$tree = new CourseCategoryTree('course_categories_selector', false, false, _TREE_COLUMNS_TYPE_RADIO);

    766. 

  766. 		$tree->init();

    767. 

  767. 

    768. 

  768. 		if (isset($_POST['update_tempdata'])) {

    769. 

  769. 

    770. 

  770. 			$ref['selected_categories'] = isset($_POST['course_categories_selector_input']) ? explode(",", $_POST['course_categories_selector_input']) : array();

    771. 

  771. 			$ref['showed_columns'] = array();

    772. 

  772. 		} else {

    773. 

  773. 

    774. 

  774. 			if ( isset($ref['selected_categories']) && count($ref['selected_categories'])>0 )

    775. 

  775. 				$tree->setInitialSelection($ref['selected_categories']);

    776. 

  776. 		}

    777. 

  777. 

    778. 

  778. 		//back to columns category selection

    779. 

  779. 		if (isset($_POST['undo_filter'])) {

    780. 

  780. 			Util::jump_to($back_url);

    781. 

  781. 		}

    782. 

  782. 

    783. 

  783. 		//filter setting done, go to next step

    784. 

  784. 		if (isset($_POST['import_filter']) || isset($_POST['show_filter']) || isset($_POST['pre_filter'])) {

    785. 

  785. 			$temp_url = $next_url;

    786. 

  786. 			if (isset($_POST['pre_filter'])) $temp_url.='&show=1&nosave=1';

    787. 

  787. 			if (isset($_POST['show_filter'])) $temp_url.='&show=1';

    788. 

  788. 			Util::jump_to($temp_url);

    789. 

  789. 		}

    790. 

  790. 

    791. 

  791. 

    792. 

  792. 		//produce output

    793. 

  793. 		$html = '';

    794. 

  794. 		$output = $tree->get(true, true, 'treeCat');

    795. 

  795. 

    796. 

  796. 		cout($output['js'], 'page_head');

    797. 

  797. 

    798. 

  798. 		$box = new ReportBox('coursecategories_selector');

    799. 

  799. 		$box->title = $this->lang->def('_COURSES_SELECTION_TITLE');

    800. 

  800. 		$box->description = false;

    801. 

  801. 

    802. 

  802. 		$boxlang =& DoceboLanguage::createInstance('report', 'framework');

    803. 

  803. 		$box->body .= '<div class="">'.$output['html'].'</div>';

    804. 

  804. 		$box->body .= Form::getHidden('update_tempdata', 'update_tempdata', 1);

    805. 

  805. 		$box->body .= Form::openButtonSpace();

    806. 

  806. 		$box->body .= '<button class="button" type="button" onclick="treeCat.clearSelection();">'. Lang::t('_RESET', 'standard').'</button>';

    807. 

  807. 		$box->body .= Form::closeButtonSpace();

    808. 

  808. 

    809. 

  809. 		$html = $box->get();

    810. 

  810. 		cout($html);

    811. 

  811. 	}

    812. 

  812. 

    813. 

  813. 

    814. 

  814. 

    815. 

  815. 	function _get_coursecategories_query($type = 'html', $report_data = NULL, $other = '') {

    816. 

  816. 		require_once($GLOBALS['where_lms'].'/lib/lib.course.php');

    817. 

  817. 		require_once(dirname(__FILE__).'/report_tableprinter.php');

    818. 

  818. 

    819. 

  819. 		if ($report_data==NULL) $ref =& $_SESSION['report_tempdata']; else $ref =& $report_data;

    820. 

  820. 

    821. 

  821. 		$fw  = $GLOBALS['prefix_fw'];

    822. 

  822. 		$lms = $GLOBALS['prefix_lms'];

    823. 

  823. 

    824. 

  824. 		$sel_all = $ref['rows_filter']['select_all'];

    825. 

  825. 		$sel_type = $ref['rows_filter']['selection_type'];

    826. 

  826. 		$selection = $ref['rows_filter']['selection'];

    827. 

  827. 

    828. 

  828. 		$categories = $ref['columns_filter']['selected_categories'];

    829. 

  829. 		$cols =& $ref['columns_filter']['showed_columns'];

    830. 

  830. 

    831. 

  831. 		if (!$sel_all && count($selection)<=0) {

    832. 

  832. 			cout( '<p>'.$this->lang->def('_EMPTY_SELECTION').'</p>' );

    833. 

  833. 			return;

    834. 

  834. 		}

    835. 

  835. 

    836. 

  836. 		$acl = new DoceboACLManager();

    837. 

  837. 		$acl->include_suspended = true;

    838. 

  838. 		$html = '';

    839. 

  839. 

    840. 

  840. 

    841. 

  841. 		//admin users filter

    842. 

  842. 		$userlevelid = Docebo::user()->getUserLevelId();

    843. 

  843. 		if ( $userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {

    844. 

  844. 			require_once(_base_.'/lib/lib.preference.php');

    845. 

  845. 			$adminManager = new AdminPreference();

    846. 

  846. 			$admin_tree = $adminManager->getAdminTree(Docebo::user()->getIdST());

    847. 

  847. 			$admin_users = $acl->getAllUsersFromIdst($admin_tree);

    848. 

  848. 			$admin_users = array_unique($admin_users);

    849. 

  849. 		}

    850. 

  850. 

    851. 

  851. 		//course categories names

    852. 

  852. 		$res = sql_query("SELECT * FROM ".$lms."_category ");

    853. 

  853. 		$categories_names = array();

    854. 

  854. 		$categories_limit = array();

    855. 

  855. 		while ($row = mysql_fetch_assoc($res)) {

    856. 

  856. 			$categories_names[ $row['idCategory'] ] = ($row['path']!='/root/' ? end( explode("/", $row['path'])) : Lang::t('_CATEGORY', 'admin_course_management', 'lms'));// Lang::t('_ROOT'));

    857. 

  857. 			$categories_paths[ $row['idCategory'] ] = ($row['path']!='/root/' ? substr($row['path'], 5, (strlen($row['path'])-5)) : Lang::t('_CATEGORY', 'admin_course_management'));// Lang::t('_ROOT'));

    858. 

  858. 			$categories_limit[ $row['idCategory'] ] = array($row['iLeft'], $row['iRight']);

    859. 

  859. 		}

    860. 

  860. 

    861. 

  861. 		$user_courses = false;

    862. 

  862. 		if(Docebo::user()->getUserLevelId() != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {

    863. 

  863. 

    864. 

  864. 			// if the usre is a subadmin with only few course assigned

    865. 

  865. 			require_once(_base_.'/lib/lib.preference.php');

    866. 

  866. 			$adminManager = new AdminPreference();

    867. 

  867. 			$admin_courses = $adminManager->getAdminCourse(Docebo::user()->getIdST());

    868. 

  868. 

    869. 

  869. 			if(isset($admin_courses['course'][0]))

    870. 

  870. 				$user_course = false;

    871. 

  871. 			elseif(isset($admin_courses['course'][-1]))

    872. 

  872. 			{

    873. 

  873. 				require_once(_lms_.'/lib/lib.catalogue.php');

    874. 

  874. 				$cat_man = new Catalogue_Manager();

    875. 

  875. 

    876. 

  876. 				$user_catalogue = $cat_man->getUserAllCatalogueId(Docebo::user()->getIdSt());

    877. 

  877. 				if(count($user_catalogue) > 0)

    878. 

  878. 				{

    879. 

  879. 					$courses = array(0);

    880. 

  880. 

    881. 

  881. 					foreach($user_catalogue as $id_cat)

    882. 

  882. 					{

    883. 

  883. 						$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);

    884. 

  884. 

    885. 

  885. 						$courses = array_merge($courses, $catalogue_course);

    886. 

  886. 					}

    887. 

  887. 

    888. 

  888. 					foreach($courses as $id_course)

    889. 

  889. 						if($id_course != 0)

    890. 

  890. 							$admin_courses['course'][$id_course] = $id_course;

    891. 

  891. 				}

    892. 

  892. 				elseif(Get::sett('on_catalogue_empty', 'off') == 'on')

    893. 

  893. 					$user_course = false;

    894. 

  894. 

    895. 

  895. 				$user_courses = $admin_courses['course'];

    896. 

  896. 			}

    897. 

  897. 			else

    898. 

  898. 			{

    899. 

  899. 				$array_courses = array();

    900. 

  900. 				$array_courses = array_merge($array_courses, $admin_courses['course']);

    901. 

  901. 

    902. 

  902. 				if(!empty($admin_courses['coursepath']))

    903. 

  903. 				{

    904. 

  904. 					require_once(_lms_.'/lib/lib.coursepath.php');

    905. 

  905. 					$path_man = new Catalogue_Manager();

    906. 

  906. 					$coursepath_course =& $path_man->getAllCourses($admin_courses['coursepath']);

    907. 

  907. 					$array_courses = array_merge($array_courses, $coursepath_course);

    908. 

  908. 				}

    909. 

  909. 				if(!empty($admin_courses['catalogue']))

    910. 

  910. 				{

    911. 

  911. 					require_once(_lms_.'/lib/lib.catalogue.php');

    912. 

  912. 					$cat_man = new Catalogue_Manager();

    913. 

  913. 					foreach($admin_courses['catalogue'] as $id_cat)

    914. 

  914. 					{

    915. 

  915. 						$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);

    916. 

  916. 						$array_courses = array_merge($array_courses, $catalogue_course);

    917. 

  917. 					}

    918. 

  918. 				}

    919. 

  919. 				$admin_courses['course'] = array_merge($admin_courses['course'], $array_courses);

    920. 

  920. 

    921. 

  921. 				$user_courses = $admin_courses['course'];

    922. 

  922. 			}

    923. 

  923. 		}

    924. 

  924. 		//create table

    925. 

  925. 		switch ($sel_type) {

    926. 

  926. 

    927. 

  927. 			case 'users': {

    928. 

  928. 				//table data

    929. 

  929. 				$data = array();

    930. 

  930. 

    931. 

  931. 				$head1 = array('');

    932. 

  932. 				$head2 = array( $this->lang->def('_USER'));

    933. 

  933. 

    934. 

  934. 				$totals = array();

    935. 

  935. 

    936. 

  936. 				foreach ($categories as $idcat) {

    937. 

  937. 					$index = (int)str_replace("d", "", $idcat);

    938. 

  938. 					$head1[] = array('colspan'=>2, 'value'=>$categories_paths[$index]);

    939. 

  939. 					$head2[] = $this->lang->def('_COMPLETED');

    940. 

  940. 					$head2[] = $this->lang->def('incomplete');

    941. 

  941. 

    942. 

  942. 					$is_descendant = strpos($idcat, "d");

    943. 

  943. 					if ($is_descendant === false) {

    944. 

  944. 						$condition = " AND cat.idCategory=".$index." ";

    945. 

  945. 					} else {

    946. 

  946. 						list($left, $right) = $categories_limit[$index];//sql_fetch_row( sql_query("SELECT iLeft, iRight FROM ".$lms."_category WHERE idCAtegory=".$index) );

    947. 

  947. 						$condition = " AND cat.iLeft >= ".$left." AND cat.iRight <= ".$right." ";

    948. 

  948. 					}

    949. 

  949. 

    950. 

  950. 					//resolve user selection

    951. 

  951. 					if ($sel_all)

    952. 

  952. 						$selection = $acl->getAllUsersIdst();

    953. 

  953. 					else

    954. 

  954. 						$selection = $acl->getAllUsersFromIdst( $selection ); //resolve group and orgchart selection

    955. 

  955. 

    956. 

  956. 					$query = "SELECT cu.idUser, cat.idCategory, c.idCourse, c.code, cu.status "

    957. 

  957. 						." FROM ".$lms."_course as c JOIN ".$lms."_category as cat JOIN ".$lms."_courseuser as cu "

    958. 

  958. 						." ON (c.idCourse=cu.idCourse AND c.idCategory=cat.idCategory) "

    959. 

  959. 						." WHERE ".($sel_all ? " 1 " : " cu.idUser IN (".implode(",", $selection).") " )

    960. 

  960. 						.$condition

    961. 

  961. 						.( $user_courses != false ? " AND c.idCourse IN ( '".implode("','", $user_courses)."' ) " : '' );

    962. 

  962. 

    963. 

  963. 					$res = sql_query($query);

    964. 

  964. 					$temp = array();

    965. 

  965. 					$total_1 = 0;

    966. 

  966. 					$total_2 = 0;

    967. 

  967. 					while ($row = mysql_fetch_assoc($res)) {

    968. 

  968. 						$iduser = $row['idUser'];

    969. 

  969. 

    970. 

  970. 						if (!isset($temp[ $iduser ]))

    971. 

  971. 							$temp[ $iduser ] = array(

    972. 

  972. 								'completed' => 0,

    973. 

  973. 								'not_completed' => 0

    974. 

  974. 							);

    975. 

  975. 

    976. 

  976. 						switch ($row['status']) {

    977. 

  977. 							case 0:

    978. 

  978. 							case 1: { $temp[$iduser]['not_completed']++; $total_2++; } break;

    979. 

  979. 							case 2: { $temp[$iduser]['completed']++; $total_1++; } break;

    980. 

  980. 						}

    981. 

  981. 					}

    982. 

  982. 

    983. 

  983. 					$totals[] = $total_1;

    984. 

  984. 					$totals[] = $total_2;

    985. 

  985. 

    986. 

  986. 					$data[ $index ] = $temp;

    987. 

  987. 					//unset($temp); //free memory

    988. 

  988. 				}

    989. 

  989. 

    990. 

  990. 				$buffer = new ReportTablePrinter($type, true);

    991. 

  991. 				$buffer->openTable('','');

    992. 

  992. 

    993. 

  993. 				$buffer->openHeader();

    994. 

  994. 				$buffer->addHeader($head1);

    995. 

  995. 				$buffer->addHeader($head2);

    996. 

  996. 				$buffer->closeHeader();

    997. 

  997. 

    998. 

  998. 				//retrieve usernames

    999. 

  999. 				$usernames = array();

    1000. 

  1000. 				$res = sql_query("SELECT idst, userid FROM ".$fw."_user WHERE idst IN (".implode(",", $selection).")");

    1001. 

  1001. 				while (list($idst, $userid) = sql_fetch_row($res))

    1002. 

  1002. 					$usernames[$idst] = $acl->relativeId( $userid );

    1003. 

  1003. 

    1004. 

  1004. 				//user cycle

    1005. 

  1005. 				$buffer->openBody();

    1006. 

  1006. 				foreach ($selection as $user) {

    1007. 

  1007. 					$line = array();

    1008. 

  1008. 

    1009. 

  1009. 					$line[] = ( isset($usernames[ $user ]) ? $usernames[ $user ] : '' );

    1010. 

  1010. 					foreach ($categories as $idcat) {

    1011. 

  1011. 						if ($idcat != '') {

    1012. 

  1012. 							$index = (int)str_replace("d", "", $idcat);

    1013. 

  1013. 							if (isset($data[$index][$user])) {

    1014. 

  1014. 								$line[] = $data[$index][$user]['completed'];

    1015. 

  1015. 								$line[] = $data[$index][$user]['not_completed'];

    1016. 

  1016. 							} else {

    1017. 

  1017. 								$line[] = '0';

    1018. 

  1018. 								$line[] = '0';

    1019. 

  1019. 							}

    1020. 

  1020. 						}

    1021. 

  1021. 					}

    1022. 

  1022. 

    1023. 

  1023. 					$buffer->addLine($line);

    1024. 

  1024. 				}

    1025. 

  1025. 				$buffer->closeBody();

    1026. 

  1026. 

    1027. 

  1027. 				//set totals

    1028. 

  1028. 				$foot = array('');

    1029. 

  1029. 				foreach ($totals as $total) { $foot[] = $total; }

    1030. 

  1030. 				$buffer->setFoot($foot);

    1031. 

  1031. 

    1032. 

  1032. 				//unset($data); //free memory

    1033. 

  1033. 				$buffer->closeTable();

    1034. 

  1034. 				$html .= $buffer->get();

    1035. 

  1035. 			} break;

    1036. 

  1036. 

    1037. 

  1037. 			//-----------------------------------------

    1038. 

  1038. 

    1039. 

  1039. 			case 'groups': {

    1040. 

  1040. 				//table data

    1041. 

  1041. 				$data = array();

    1042. 

  1042. 

    1043. 

  1043. 				//retrieve all labels

    1044. 

  1044. 				$orgchart_labels = array();

    1045. 

  1045. 				$query = "SELECT * FROM ".$fw."_org_chart WHERE lang_code='".getLanguage()."'";

    1046. 

  1046. 				$res = sql_query($query);

    1047. 

  1047. 				while ($row = mysql_fetch_assoc($res)) {

    1048. 

  1048. 					$orgchart_labels[$row['id_dir']] = $row['translation'];

    1049. 

  1049. 				}

    1050. 

  1050. 

    1051. 

  1051. 				$labels = array();

    1052. 

  1052. 				//$query = "SELECT * FROM ".$fw."_group WHERE (hidden='false' OR groupid LIKE '/oc_%' OR groupid LIKE '/ocd_%') AND type='free'";

    1053. 

  1053. 				$query = "SELECT * FROM ".$fw."_group WHERE groupid LIKE '/oc\_%' OR groupid LIKE '/ocd\_%' OR hidden = 'false' ";

    1054. 

  1054. 				$res = sql_query($query);

    1055. 

  1055. 				while ($row = mysql_fetch_assoc($res)) {

    1056. 

  1056. 					if ($row['hidden']=='false') {

    1057. 

  1057. 						$labels[$row['idst']] = $acl->relativeId($row['groupid']);

    1058. 

  1058. 					} else {

    1059. 

  1059. 						$temp = explode("_", $row['groupid']); //echo '<div>'.print_r($temp,true).'</div>';

    1060. 

  1060. 						if ($temp[0]=='/oc') {

    1061. 

  1061. 							$labels[$row['idst']] = ($temp[1]!=0 ? $orgchart_labels[$temp[1]] : '');

    1062. 

  1062. 						} elseif ($temp[0]=='/ocd') {

    1063. 

  1063. 							$labels[$row['idst']] = ($temp[1]!=0 ? $orgchart_labels[$temp[1]] : '');

    1064. 

  1064. 						}

    1065. 

  1065. 					}

    1066. 

  1066. 				}

    1067. 

  1067. 

    1068. 

  1068. 

    1069. 

  1069. 				//solve groups user

    1070. 

  1070. 				$solved_groups = array();

    1071. 

  1071. 				$subgroups_list = array();

    1072. 

  1072. 				foreach ($selection as $group) {

    1073. 

  1073. 					$temp = $acl->getGroupGDescendants($group);

    1074. 

  1074. 					$temp[] = $group;

    1075. 

  1075. 					foreach ($temp as $idst_subgroup) {

    1076. 

  1076. 						$solved_groups[$idst_subgroup] = $group;

    1077. 

  1077. 					}

    1078. 

  1078. 					$subgroups_list = array_merge( $subgroups_list, $temp );

    1079. 

  1079. 				}

    1080. 

  1080. 

    1081. 

  1081. 				$tot_size = 2;

    1082. 

  1082. 				$totals = array();

    1083. 

  1083. 				$head1 = array( array('colspan'=>2, 'value'=>$this->lang->def('_GROUPS')) );

    1084. 

  1084. 				$head2 = array($this->lang->def('_NAME'), $this->lang->def('_TOTAL'));

    1085. 

  1085. 

    1086. 

  1086. 				foreach ($categories as $idcat) {

    1087. 

  1087. 					$index = (int)str_replace("d", "", $idcat);

    1088. 

  1088. 					$head1[] = array('colspan'=>2, 'value'=>$categories_paths[$index]);

    1089. 

  1089. 					$head2[] = $this->lang->def('_COMPLETED');

    1090. 

  1090. 					$head2[] = $this->lang->def('incomplete');

    1091. 

  1091. 

    1092. 

  1092. 					$is_descendant = strpos($idcat, "d");

    1093. 

  1093. 					$condition = '';

    1094. 

  1094. 					if ($is_descendant === false) {

    1095. 

  1095. 						$condition = " AND cat.idCategory=".$index." ";

    1096. 

  1096. 					} else {

    1097. 

  1097. 						list($left, $right) = $categories_limit[$index];//sql_fetch_row( sql_query("SELECT iLeft, iRight FROM ".$lms."_category WHERE idCAtegory=".$index) );

    1098. 

  1098. 						$condition = " AND cat.iLeft >= ".$left." AND cat.iRight <= ".$right." ";

    1099. 

  1099. 					}

    1100. 

  1100. 

    1101. 

  1101. 

    1102. 

  1102. 					$query = "SELECT gm.idst as idGroup, cu.idUser, cat.idCategory, c.idCourse, c.code, cu.status "

    1103. 

  1103. 						." FROM ".$lms."_course as c JOIN ".$lms."_category as cat JOIN ".$lms."_courseuser as cu JOIN ".$fw."_group_members as gm "

    1104. 

  1104. 						." ON (c.idCourse=cu.idCourse AND c.idCategory=cat.idCategory AND cu.idUser=gm.idstMember) "

    1105. 

  1105. 						." WHERE ".($sel_all ? " 1 " : " gm.idst IN (".implode(",", $subgroups_list).") " ) //idst of the groups

    1106. 

  1106. 						.$condition

    1107. 

  1107. 						.( $user_courses != false ? " AND c.idCourse IN ( '".implode("','", $user_courses)."' ) " : '' );

    1108. 

  1108. 

    1109. 

  1109. 					$res = sql_query($query);

    1110. 

  1110. 					$temp = array();

    1111. 

  1111. 					$total_1 = 0;

    1112. 

  1112. 					$total_2 = 0;

    1113. 

  1113. 					while ($row = mysql_fetch_assoc($res)) {

    1114. 

  1114. 						$id_group = $solved_groups[ $row['idGroup'] ];

    1115. 

  1115. 

    1116. 

  1116. 						if (!isset($temp[ $id_group ]))

    1117. 

  1117. 							$temp[ $id_group ] = array(

    1118. 

  1118. 								'completed' => 0,

    1119. 

  1119. 								'not_completed' => 0

    1120. 

  1120. 							);

    1121. 

  1121. 

    1122. 

  1122. 						switch ($row['status']) {

    1123. 

  1123. 							case 0:

    1124. 

  1124. 							case 1: { $temp[$id_group]['not_completed']++; $total_2++; } break;

    1125. 

  1125. 							case 2: { $temp[$id_group]['completed']++; $total_1++; } break;

    1126. 

  1126. 						}

    1127. 

  1127. 					}

    1128. 

  1128. 

    1129. 

  1129. 					$totals[]= $total_1;

    1130. 

  1130. 					$totals[]= $total_2;

    1131. 

  1131. 

    1132. 

  1132. 					$data[ $index ] = $temp;

    1133. 

  1133. 					//unset($temp); //free memory

    1134. 

  1134. 				}

    1135. 

  1135. 

    1136. 

  1136. 

    1137. 

  1137. 				$buffer = new ReportTablePrinter($type, true);

    1138. 

  1138. 				$buffer->openTable('','');

    1139. 

  1139. 

    1140. 

  1140. 				$buffer->openHeader();

    1141. 

  1141. 				$buffer->addHeader($head1);

    1142. 

  1142. 				$buffer->addHeader($head2);

    1143. 

  1143. 				$buffer->closeHeader();

    1144. 

  1144. 

    1145. 

  1145. 				$tot_users = 0;

    1146. 

  1146. 				$buffer->openBody();

    1147. 

  1147. 

    1148. 

  1148. 				foreach ($selection as $dir_id=>$group_id) {

    1149. 

  1149. 					$group_users = $acl->getGroupAllUser($group_id);

    1150. 

  1150. 					if ( $userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) { $group_users = array_intersect($group_users, $admin_users); }

    1151. 

  1151. 					$users_num = count($group_users);

    1152. 

  1152. 

    1153. 

  1153. 					$line = array();

    1154. 

  1154. 					$line[] = $labels[$group_id];

    1155. 

  1155. 					$line[] = $users_num;

    1156. 

  1156. 					$tot_users += $users_num;

    1157. 

  1157. 

    1158. 

  1158. 

    1159. 

  1159. 					foreach ($categories as $idcat) {

    1160. 

  1160. 						if ($idcat != '') {

    1161. 

  1161. 							$index = (int)str_replace("d", "", $idcat);

    1162. 

  1162. 							if (isset($data[$index][$group_id])) {

    1163. 

  1163. 								$line[] = $data[$index][$group_id]['completed'];

    1164. 

  1164. 								$line[] = $data[$index][$group_id]['not_completed'];

    1165. 

  1165. 							} else {

    1166. 

  1166. 								$line[] = '0';

    1167. 

  1167. 								$line[] = '0';

    1168. 

  1168. 							}

    1169. 

  1169. 						}

    1170. 

  1170. 					}

    1171. 

  1171. 

    1172. 

  1172. 					$buffer->addLine($line);

    1173. 

  1173. 				}

    1174. 

  1174. 				$buffer->closeBody();

    1175. 

  1175. 

    1176. 

  1176. 				//totals ...

    1177. 

  1177. 

    1178. 

  1178. 				$foot = array('', $tot_users);

    1179. 

  1179. 				foreach ($totals as $total) {	$foot[] = $total;	}

    1180. 

  1180. 				$buffer->setFoot($foot);

    1181. 

  1181. 

    1182. 

  1182. 				$buffer->closeTable();

    1183. 

  1183. 				$html .= $buffer->get();

    1184. 

  1184. 			} break;

    1185. 

  1185. 

    1186. 

  1186. 		} //end switch

    1187. 

  1187. 

    1188. 

  1188. 		$GLOBALS['page']->add($html, 'content');

    1189. 

  1189. 	}

    1190. 

  1190. 

    1191. 

  1191. 	//----------------------------------------------------------------------------

    1192. 

  1192. 

    1193. 

  1193. 	function show_report_time($data = NULL, $other = '') {

    1194. 

  1194. 		if ($data===NULL)

    1195. 

  1195. 			cout( $this->_get_time_query() );

    1196. 

  1196. 		else

    1197. 

  1197. 			cout( $this->_get_time_query('html', $data, $other) );

    1198. 

  1198. 	}

    1199. 

  1199. 

    1200. 

  1200. 	function get_time_filter() {

    1201. 

  1201. 		$back_url = $this->back_url;

    1202. 

  1202. 		$jump_url = $this->jump_url;

    1203. 

  1203. 		$next_url = $this->next_url;

    1204. 

  1204. 

    1205. 

  1205. 		require_once(_base_.'/lib/lib.form.php');

    1206. 

  1206. 

    1207. 

  1207. 		$lang =& DoceboLanguage::createInstance('report', 'framework');

    1208. 

  1208. 

    1209. 

  1209. 		//$sel = new Course_Manager();

    1210. 

  1210. 		//$sel->setLink('index.php?modname=report&op=report_rows_filter');

    1211. 

  1211. 

    1212. 

  1212. 		if (isset($_POST['undo_filter'])) Util::jump_to($back_url);

    1213. 

  1213. 

    1214. 

  1214. 

    1215. 

  1215. 		if (!isset($_SESSION['report_tempdata']['columns_filter'])) {

    1216. 

  1216. 			$_SESSION['report_tempdata']['columns_filter'] = array(

    1217. 

  1217. 				'timetype' => 'years',

    1218. 

  1218. 				'years' => 1,

    1219. 

  1219. 				'months' => 12

    1220. 

  1220. 			);

    1221. 

  1221. 		}

    1222. 

  1222. 		$ref =& $_SESSION['report_tempdata']['columns_filter'];

    1223. 

  1223. 

    1224. 

  1224. 

    1225. 

  1225. 		if (isset($_POST['update_tempdata'])) {

    1226. 

  1226. 			$ref['years'] = Get::req('years', DOTY_INT, 1);

    1227. 

  1227. 		} else {

    1228. 

  1228. 			//...

    1229. 

  1229. 		}

    1230. 

  1230. 

    1231. 

  1231. 		//back to columns category selection

    1232. 

  1232. 		if (isset($_POST['undo_filter'])) {

    1233. 

  1233. 			Util::jump_to($back_url);

    1234. 

  1234. 		}

    1235. 

  1235. 

    1236. 

  1236. 		//filter setting done, go to next step

    1237. 

  1237. 		if (isset($_POST['import_filter']) || isset($_POST['show_filter']) || isset($_POST['pre_filter'])) {

    1238. 

  1238. 			$temp_url = $next_url;

    1239. 

  1239. 			if (isset($_POST['pre_filter'])) $temp_url.='&show=1&nosave=1';

    1240. 

  1240. 			if (isset($_POST['show_filter'])) $temp_url.='&show=1';

    1241. 

  1241. 			Util::jump_to($temp_url);

    1242. 

  1242. 		}

    1243. 

  1243. 

    1244. 

  1244. 		$box = new ReportBox('choose_time');

    1245. 

  1245. 		$box->title = $this->lang->def('_CHOOSE_TIME');

    1246. 

  1246. 		$box->description = false;

    1247. 

  1247. 

    1248. 

  1248. 		$year = date('Y');

    1249. 

  1249. 		$dropdownyears = array(

    1250. 

  1250. 			1 => $year,

    1251. 

  1251. 			2 => $year.' - '.($year - 1),

    1252. 

  1252. 			3 => $year.' - '.($year - 2),

    1253. 

  1253. 			4 => $year.' - '.($year - 3),

    1254. 

  1254. 			5 => $year.' - '.($year - 4),

    1255. 

  1255. 			6 => $year.' - '.($year - 5),

    1256. 

  1256. 			7 => $year.' - '.($year - 6),

    1257. 

  1257. 		);

    1258. 

  1258. 		$box->body .= Form::getHidden('update_tempdata', 'update_tempdata', 1);

    1259. 

  1259. 		$box->body .= Form::getDropDown($this->lang->def('_RA_CAT_TIME'), 'years', 'years', $dropdownyears, $ref['years']);

    1260. 

  1260. 

    1261. 

  1261. 		$html = $box->get();

    1262. 

  1262. 		cout($html);

    1263. 

  1263. 

    1264. 

  1264. 	}

    1265. 

  1265. 

    1266. 

  1266. 	function _get_time_query($type = 'html', $report_data = NULL, $other = '') {

    1267. 

  1267. 		require_once($GLOBALS['where_lms'].'/lib/lib.course.php');

    1268. 

  1268. 		require_once(dirname(__FILE__).'/report_tableprinter.php');

    1269. 

  1269. 

    1270. 

  1270. 		if ($report_data==NULL) $ref =& $_SESSION['report_tempdata']; else $ref =& $report_data;

    1271. 

  1271. 

    1272. 

  1272. 		$fw  = $GLOBALS['prefix_fw'];

    1273. 

  1273. 		$lms = $GLOBALS['prefix_lms'];

    1274. 

  1274. 

    1275. 

  1275. 		$sel_all = $ref['rows_filter']['select_all'];

    1276. 

  1276. 		$sel_type = $ref['rows_filter']['selection_type'];

    1277. 

  1277. 		$selection = $ref['rows_filter']['selection'];

    1278. 

  1278. 

    1279. 

  1279. 		$timetype = $ref['columns_filter']['timetype'];

    1280. 

  1280. 		$years =& $ref['columns_filter']['years'];

    1281. 

  1281. 		$months =& $ref['columns_filter']['months'];

    1282. 

  1282. 

    1283. 

  1283. 		if (!$sel_all && count($selection)<=0) {

    1284. 

  1284. 			cout( '<p>'.$this->lang->def('_EMPTY_SELECTION').'</p>' );

    1285. 

  1285. 			return;

    1286. 

  1286. 		}

    1287. 

  1287. 

    1288. 

  1288. 		$acl = new DoceboACLManager();

    1289. 

  1289. 		$acl->include_suspended = true;

    1290. 

  1290. 

    1291. 

  1291. 		//admin users filter

    1292. 

  1292. 		$userlevelid = Docebo::user()->getUserLevelId();

    1293. 

  1293. 		if ( $userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {

    1294. 

  1294. 			require_once(_base_.'/lib/lib.preference.php');

    1295. 

  1295. 			$adminManager = new AdminPreference();

    1296. 

  1296. 			$admin_tree = $adminManager->getAdminTree(Docebo::user()->getIdST());

    1297. 

  1297. 			$admin_users = $acl_man->getAllUsersFromIdst($admin_tree);

    1298. 

  1298. 			$admin_users = array_unique($admin_users);

    1299. 

  1299. 		}

    1300. 

  1300. 

    1301. 

  1301. 		$html = '';

    1302. 

  1302. 		$times = array();

    1303. 

  1303. 		switch ($timetype) {

    1304. 

  1304. 			case 'years': {

    1305. 

  1305. 				$now = date('Y');

    1306. 

  1306. 				for ($i = $now-$years+1; $i<=$now; $i++) { $times[] = $i; }

    1307. 

  1307. 			} break;

    1308. 

  1308. 			case 'months':{

    1309. 

  1309. 				//...

    1310. 

  1310. 			} break;

    1311. 

  1311. 		}

    1312. 

  1312. 

    1313. 

  1313. 		switch ($sel_type) {

    1314. 

  1314. 

    1315. 

  1315. 			case 'users': {

    1316. 

  1316. 				$data = array();

    1317. 

  1317. 

    1318. 

  1318. 				$users_list = ($sel_all ? $acl->getAllUsersIdst() : $acl->getAllUsersFromIdst($selection) );

    1319. 

  1319. 				$users_list = array_unique($users_list);

    1320. 

  1320. 				if ( $userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) $users_list = array_intersect($users_list, $admin_users);

    1321. 

  1321. 

    1322. 

  1322. 				$query = "SELECT idUser, YEAR(date_complete) as yearComplete "

    1323. 

  1323. 					." FROM ".$lms."_courseuser "

    1324. 

  1324. 					." WHERE status=2 "

    1325. 

  1325. 					.( $userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()? " AND idUser IN (".implode(",", $users_list).") " : "" );

    1326. 

  1326. 

    1327. 

  1327. 

    1328. 

  1328. 				$res = sql_query($query);

    1329. 

  1329. 				while ($row = mysql_fetch_assoc($res)) {

    1330. 

  1330. 					//$data[ $row['idUser'] ][ $row['yearComplete'] ] = $row['complete'];

    1331. 

  1331. 					$idUser = $row['idUser'];

    1332. 

  1332. 					$year = $row['yearComplete'];

    1333. 

  1333. 					if (!isset($data[ $idUser ][ $year ])) $data[ $idUser ][ $year ] = 0;

    1334. 

  1334. 					$data[ $idUser ][ $year ]++;

    1335. 

  1335. 				}

    1336. 

  1336. 

    1337. 

  1337. 				$usernames = array();

    1338. 

  1338. 				$query = "SELECT idst, userid FROM ".$fw."_user WHERE idst IN (".implode(",", $users_list).")";

    1339. 

  1339. 				$res = sql_query($query);

    1340. 

  1340. 				while ($row = mysql_fetch_assoc($res)) {

    1341. 

  1341. 					$usernames[ $row['idst'] ] = $acl->relativeId( $row['userid'] );

    1342. 

  1342. 				}

    1343. 

  1343. 

    1344. 

  1344. 				//draw table

    1345. 

  1345. 				$buffer = new ReportTablePrinter($type, true);

    1346. 

  1346. 				$buffer->openTable('','');

    1347. 

  1347. 

    1348. 

  1348. 				$head = array($this->lang->def('_USER'));

    1349. 

  1349. 				foreach ($times as $time) {

    1350. 

  1350. 					$head[] = $time;

    1351. 

  1351. 				}

    1352. 

  1352. 				$head[] = $this->lang->def('_TOTAL');

    1353. 

  1353. 

    1354. 

  1354. 				$buffer->openHeader();

    1355. 

  1355. 				$buffer->addHeader($head);

    1356. 

  1356. 				$buffer->closeHeader();

    1357. 

  1357. 

    1358. 

  1358. 				$tot_total = 0;

    1359. 

  1359. 				$buffer->openBody();

    1360. 

  1360. 				foreach ($users_list as $user) {

    1361. 

  1361. 

    1362. 

  1362. 					if(!isset($usernames[$user])) break;

    1363. 

  1363. 					$line = array();

    1364. 

  1364. 					$line_total = 0;

    1365. 

  1365. 					$line[] = $usernames[$user];

    1366. 

  1366. 					foreach ($times as $time) { //years or months

    1367. 

  1367. 

    1368. 

  1368. 						switch ($timetype) {

    1369. 

  1369. 

    1370. 

  1370. 							case 'years': {

    1371. 

  1371. 								if (isset($data[$user][$time])) {

    1372. 

  1372. 										$line[] = $data[$user][$time];

    1373. 

  1373. 										$line_total += $data[$user][$time];

    1374. 

  1374. 								} else

    1375. 

  1375. 										$line[] = '0';

    1376. 

  1376. 							} break;

    1377. 

  1377. 

    1378. 

  1378. 							case 'months': {

    1379. 

  1379. 								//$year = ...

    1380. 

  1380. 								//$month = ...

    1381. 

  1381. 								//$line[] = (isset($data[$group][$year][$month]) ? $data[$group][$year][$month] : '0'); break;

    1382. 

  1382. 							}

    1383. 

  1383. 

    1384. 

  1384. 						}

    1385. 

  1385. 

    1386. 

  1386. 					}

    1387. 

  1387. 

    1388. 

  1388. 					$line[] = $line_total;

    1389. 

  1389. 					$tot_total += $line_total;

    1390. 

  1390. 					$buffer->addLine($line);

    1391. 

  1391. 				}

    1392. 

  1392. 

    1393. 

  1393. 				$buffer->closeBody();

    1394. 

  1394. 

    1395. 

  1395. 				//totals

    1396. 

  1396. 				$foot = array('');

    1397. 

  1397. 				foreach ($times as $time) {

    1398. 

  1398. 					$temp = 0;

    1399. 

  1399. 					foreach ($users_list as $user) {

    1400. 

  1400. 						if (isset($data[$user][$time])) $temp += $data[$user][$time];

    1401. 

  1401. 					}

    1402. 

  1402. 					$foot[] = $temp;

    1403. 

  1403. 				}

    1404. 

  1404. 				$foot[] = $tot_total;

    1405. 

  1405. 				$buffer->setFoot($foot);

    1406. 

  1406. 

    1407. 

  1407. 				$buffer->closeTable();

    1408. 

  1408. 				$html .= $buffer->get();

    1409. 

  1409. 			} break;

    1410. 

  1410. 

    1411. 

  1411. 

    1412. 

  1412. 

    1413. 

  1413. 			//--------------------

    1414. 

  1414. 

    1415. 

  1415. 			case 'groups': {

    1416. 

  1416. 				//retrieve all labels

    1417. 

  1417. 				$orgchart_labels = array();

    1418. 

  1418. 				$query = "SELECT * FROM ".$fw."_org_chart WHERE lang_code='".getLanguage()."'";

    1419. 

  1419. 				$res = sql_query($query);

    1420. 

  1420. 				while ($row = mysql_fetch_assoc($res)) {

    1421. 

  1421. 					$orgchart_labels[$row['id_dir']] = $row['translation'];

    1422. 

  1422. 				}

    1423. 

  1423. 

    1424. 

  1424. 				$labels = array();

    1425. 

  1425. 				//$query = "SELECT * FROM ".$fw."_group WHERE (hidden='false' OR groupid LIKE '/oc_%' OR groupid LIKE '/ocd_%') AND type='free'";

    1426. 

  1426. 				$query = "SELECT * FROM ".$fw."_group WHERE groupid LIKE '/oc\_%' OR groupid LIKE '/ocd\_%' OR hidden = 'false' ";

    1427. 

  1427. 				$res = sql_query($query);

    1428. 

  1428. 				while ($row = mysql_fetch_assoc($res)) {

    1429. 

  1429. 					if ($row['hidden']=='false') {

    1430. 

  1430. 						$labels[$row['idst']] = $acl->relativeId($row['groupid']);

    1431. 

  1431. 					} else {

    1432. 

  1432. 						$temp = explode("_", $row['groupid']); //echo '<div>'.print_r($temp,true).'</div>';

    1433. 

  1433. 						if ($temp[0]=='/oc') {

    1434. 

  1434. 							$labels[$row['idst']] = ($temp[1]!=0 ? $orgchart_labels[$temp[1]] : '');

    1435. 

  1435. 						} elseif ($temp[0]=='/ocd') {

    1436. 

  1436. 							$labels[$row['idst']] = ($temp[1]!=0 ? $orgchart_labels[$temp[1]] : '');

    1437. 

  1437. 						}

    1438. 

  1438. 					}

    1439. 

  1439. 				}

    1440. 

  1440. 

    1441. 

  1441. 

    1442. 

  1442. 				//solve groups user

    1443. 

  1443. 				$solved_groups = array();

    1444. 

  1444. 				$subgroups_list = array();

    1445. 

  1445. 				foreach ($selection as $group) {

    1446. 

  1446. 					$temp = $acl->getGroupGDescendants($group);

    1447. 

  1447. 					$temp[] = $group;

    1448. 

  1448. 					foreach ($temp as $idst_subgroup) {

    1449. 

  1449. 						$solved_groups[$idst_subgroup] = $group;

    1450. 

  1450. 					}

    1451. 

  1451. 					$subgroups_list = array_merge( $subgroups_list, $temp );

    1452. 

  1452. 				}

    1453. 

  1453. 

    1454. 

  1454. 

    1455. 

  1455. 

    1456. 

  1456. 				$query = "SELECT gm.idst as idGroup, YEAR(cu.date_complete) as yearComplete, MONTH(cu.date_complete) as monthComplete "

    1457. 

  1457. 					." FROM ".$lms."_courseuser as cu JOIN ".$fw."_group_members as gm ON (cu.idUser=gm.idstMember) "

    1458. 

  1458. 					." WHERE status=2 AND gm.idst IN (".implode(",", $subgroups_list).")";

    1459. 

  1459. 

    1460. 

  1460. 				$data = array();

    1461. 

  1461. 				$res = sql_query($query);

    1462. 

  1462. 				while ($row = mysql_fetch_assoc($res)) {

    1463. 

  1463. 					$idGroup = $solved_groups[ $row['idGroup'] ];

    1464. 

  1464. 					$year = $row['yearComplete'];

    1465. 

  1465. 					$month = $row['monthComplete'];

    1466. 

  1466. 

    1467. 

  1467. 					switch ($timetype) {

    1468. 

  1468. 

    1469. 

  1469. 						case 'years': {

    1470. 

  1470. 							if (!isset($data[ $idGroup ][$year])) $data[ $idGroup ][$year] = 0;

    1471. 

  1471. 							$data[ $idGroup ][$year]++;

    1472. 

  1472. 						} break;

    1473. 

  1473. 

    1474. 

  1474. 						case 'months': {

    1475. 

  1475. 							if (!isset($data[ $idGroup ][$year][$month])) $data[ $idGroup ][$year][$month] = 0;

    1476. 

  1476. 							$data[ $idGroup ][$year][$month]++;

    1477. 

  1477. 						} break;

    1478. 

  1478. 

    1479. 

  1479. 					} //end switch

    1480. 

  1480. 				}

    1481. 

  1481. 

    1482. 

  1482. 

    1483. 

  1483. 				//draw table

    1484. 

  1484. 				$buffer = new ReportTablePrinter($type, true);

    1485. 

  1485. 				$buffer->openTable('','');

    1486. 

  1486. 

    1487. 

  1487. 				$head = array($this->lang->def('_GROUPS'), $this->lang->def('_USERS'));

    1488. 

  1488. 				foreach ($times as $time) {

    1489. 

  1489. 					$head[] = $time;

    1490. 

  1490. 				}

    1491. 

  1491. 				$head[] = $this->lang->def('_TOTAL');

    1492. 

  1492. 

    1493. 

  1493. 				$buffer->openHeader();

    1494. 

  1494. 				$buffer->addHeader($head);

    1495. 

  1495. 				$buffer->closeHeader();

    1496. 

  1496. 

    1497. 

  1497. 				$tot_users = 0;

    1498. 

  1498. 				$tot_total = 0;

    1499. 

  1499. 				$buffer->openBody();

    1500. 

  1500. 				foreach ($selection as $group) {

    1501. 

  1501. 					$group_users = $acl->getGroupAllUser($group);

    1502. 

  1502. 					if ( $userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) { $group_users = array_intersect($group_users, $admin_users); }

    1503. 

  1503. 					$users_num = count($group_users);

    1504. 

  1504. 

    1505. 

  1505. 					$line = array();

    1506. 

  1506. 					$line_total = 0;

    1507. 

  1507. 					$line[] = $labels[$group];

    1508. 

  1508. 					$line[] = $users_num;

    1509. 

  1509. 					foreach ($times as $time) { //years or months

    1510. 

  1510. 

    1511. 

  1511. 						switch ($timetype) {

    1512. 

  1512. 

    1513. 

  1513. 							case 'years': {

    1514. 

  1514. 								if (isset($data[$group][$time])) {

    1515. 

  1515. 										$line[] = $data[$group][$time];

    1516. 

  1516. 										$line_total += $data[$group][$time];

    1517. 

  1517. 								} else

    1518. 

  1518. 										$line[] = '0';

    1519. 

  1519. 							} break;

    1520. 

  1520. 

    1521. 

  1521. 							case 'months': {

    1522. 

  1522. 								//$year = ...

    1523. 

  1523. 								//$month = ...

    1524. 

  1524. 								//$line[] = (isset($data[$group][$year][$month]) ? $data[$group][$year][$month] : '0'); break;

    1525. 

  1525. 							}

    1526. 

  1526. 

    1527. 

  1527. 						}

    1528. 

  1528. 

    1529. 

  1529. 					}

    1530. 

  1530. 

    1531. 

  1531. 					$line[] = $line_total;

    1532. 

  1532. 					$tot_users += $users_num;

    1533. 

  1533. 					$tot_total += $line_total;

    1534. 

  1534. 					$buffer->addLine($line);

    1535. 

  1535. 				}

    1536. 

  1536. 

    1537. 

  1537. 				$buffer->closeBody();

    1538. 

  1538. 

    1539. 

  1539. 				//totals

    1540. 

  1540. 				$foot = array('', $tot_users);

    1541. 

  1541. 				foreach ($times as $time) {

    1542. 

  1542. 					$temp = 0;

    1543. 

  1543. 					foreach ($selection as $group) {

    1544. 

  1544. 						if (isset($data[$group][$time])) $temp += $data[$group][$time];

    1545. 

  1545. 					}

    1546. 

  1546. 					$foot[] = $temp;

    1547. 

  1547. 				}

    1548. 

  1548. 				$foot[] = $tot_total;

    1549. 

  1549. 				$buffer->setFoot($foot);

    1550. 

  1550. 

    1551. 

  1551. 				$buffer->closeTable();

    1552. 

  1552. 				$html .= $buffer->get();

    1553. 

  1553. 			} break;

    1554. 

  1554. 

    1555. 

  1555. 		} //end switch

    1556. 

  1556. 

    1557. 

  1557. 		cout($html);

    1558. 

  1558. 	}

    1559. 

  1559. 

    1560. 

  1560. 

    1561. 

  1561. 

    1562. 

  1562. 	//----------------------------------------------------------------------------

    1563. 

  1563. 	//---- communications report part --------------------------------------------

    1564. 

  1564. 	//----------------------------------------------------------------------------

    1565. 

  1565. 

    1566. 

  1566. 

    1567. 

  1567. 	function show_report_communications($data = NULL, $other = '') {

    1568. 

  1568. 		if ($data===NULL)

    1569. 

  1569. 			cout( $this->_get_communications_query() );

    1570. 

  1570. 		else

    1571. 

  1571. 			cout( $this->_get_communications_query('html', $data, $other) );

    1572. 

  1572. 	}

    1573. 

  1573. 

    1574. 

  1574. 	function get_communications_filter() {

    1575. 

  1575. 		$back_url = $this->back_url;

    1576. 

  1576. 		$jump_url = $this->jump_url;

    1577. 

  1577. 		$next_url = $this->next_url;

    1578. 

  1578. 

    1579. 

  1579. 		//preliminary checks

    1580. 

  1580. 		if (isset($_POST['undo_filter'])) Util::jump_to($back_url);

    1581. 

  1581. 

    1582. 

  1582. 		if (!isset($_SESSION['report_tempdata']['columns_filter'])) {

    1583. 

  1583. 			$_SESSION['report_tempdata']['columns_filter'] = array(

    1584. 

  1584. 				'comm_selection' => array(),

    1585. 

  1585. 				'all_communications' => false,

    1586. 

  1586. 				'comm_start_date' => '',

    1587. 

  1587. 				'comm_end_date' => ''

    1588. 

  1588. 			);

    1589. 

  1589. 		}

    1590. 

  1590. 		$ref =& $_SESSION['report_tempdata']['columns_filter'];

    1591. 

  1591. 

    1592. 

  1592. 

    1593. 

  1593. 		if (isset($_POST['update_tempdata'])) {

    1594. 

  1594. 			$ref['all_communications'] = Get::req('all_communications', DOTY_INT, 0) > 0;

    1595. 

  1595. 			$ref['comm_selection'] = Get::req('comm_selection', DOTY_MIXED, array());

    1596. 

  1596. 			$ref['comm_start_date'] = Format::dateDb(Get::req('comm_start_date', DOTY_STRING, ''), 'date');

    1597. 

  1597. 			$ref['comm_end_date'] = Format::datedb(Get::req('comm_end_date', DOTY_STRING, ''), 'date');

    1598. 

  1598. 		} else {

    1599. 

  1599. 			//...

    1600. 

  1600. 		}

    1601. 

  1601. 

    1602. 

  1602. 		//filter setting done, go to next step

    1603. 

  1603. 		if (isset($_POST['import_filter']) || isset($_POST['show_filter']) || isset($_POST['pre_filter'])) {

    1604. 

  1604. 			$temp_url = $next_url;

    1605. 

  1605. 			if (isset($_POST['pre_filter'])) $temp_url.='&show=1&nosave=1';

    1606. 

  1606. 			if (isset($_POST['show_filter'])) $temp_url.='&show=1';

    1607. 

  1607. 			Util::jump_to($temp_url);

    1608. 

  1608. 		}

    1609. 

  1609. 

    1610. 

  1610. 		//draw filter boxes

    1611. 

  1611. 		$html = '';

    1612. 

  1612. 

    1613. 

  1613. 		//time period

    1614. 

  1614. 		$box = new ReportBox('comm_selector');

    1615. 

  1615. 		$box->title = Lang::t('_TIME_PERIOD_FILTER', 'report');

    1616. 

  1616. 		$box->description = false;

    1617. 

  1617. 		$box->body .= Form::getDatefield(Lang::t('_FROM', 'standard'), 'comm_start_date', 'comm_start_date', $ref['comm_start_date']);

    1618. 

  1618. 		$box->body .= Form::getDatefield(Lang::t('_TO', 'standard'), 'comm_end_date', 'comm_end_date', $ref['comm_end_date']);

    1619. 

  1619. 

    1620. 

  1620. 		$html = $box->get();

    1621. 

  1621. 

    1622. 

  1622. 		//communications selector

    1623. 

  1623. 		$box = new ReportBox('comm_selector');

    1624. 

  1624. 		$box->title = Lang::t('_COMMUNICATIONS', 'report');

    1625. 

  1625. 		$box->description = false;

    1626. 

  1626. 

    1627. 

  1627. 		require_once(_lms_.'/lib/lib.report.php'); //the comm. table function

    1628. 

  1628. 		$box->body .= Form::getCheckbox(Lang::t('_ALL', 'report'), 'all_communications', 'all_communications', 1, $ref['all_communications']);

    1629. 

  1629. 		$box->body .= '<br />';

    1630. 

  1630. 		$box->body .= getCommunicationsTable($ref['comm_selection']);

    1631. 

  1631. 		$box->body .= Form::getHidden('update_tempdata', 'update_tempdata', 1);

    1632. 

  1632. 

    1633. 

  1633. 		$html .= $box->get();

    1634. 

  1634. 

    1635. 

  1635. 		cout($html);

    1636. 

  1636. 	}

    1637. 

  1637. 

    1638. 

  1638. 

    1639. 

  1639. 

    1640. 

  1640. 

    1641. 

  1641. 	function _get_communications_query($type = 'html', $report_data = NULL, $other = '') {

    1642. 

  1642. 		require_once(dirname(__FILE__).'/report_tableprinter.php');

    1643. 

  1643. 		if ($report_data==NULL) $ref =& $_SESSION['report_tempdata']; else $ref =& $report_data;

    1644. 

  1644. 

    1645. 

  1645. 		$_ERR_NOUSER = Lang::t('_EMPTY_SELECTION', 'report');

    1646. 

  1646. 		$_ERR_NOCOMM = Lang::t('_EMPTY_SELECTION', 'report');

    1647. 

  1647. 		$_ERR_NODATA = Lang::t('_EMPTY_SELECTION', 'report');

    1648. 

  1648. 

    1649. 

  1649. 		$lang_type = array(

    1650. 

  1650. 			'none' => Lang::t('_NONE', 'communication'),

    1651. 

  1651. 			'file' => Lang::t('_LONAME_item', 'storage'),

    1652. 

  1652. 			'scorm' => Lang::t('_LONAME_scormorg', 'storage')

    1653. 

  1653. 		);

    1654. 

  1654. 

    1655. 

  1655. 		$sel_all = $ref['rows_filter']['select_all'];

    1656. 

  1656. 		$arr_selected_users = $ref['rows_filter']['selection']; //list of users selected in the filter (users, groups and org.branches)

    1657. 

  1657. 

    1658. 

  1658. 		$comm_all = $ref['columns_filter']['all_communications'];

    1659. 

  1659. 		$arr_selected_comm = $ref['columns_filter']['comm_selection']; //list of communications selected in the filter

    1660. 

  1660. 

    1661. 

  1661. 		$start_date = isset($ref['columns_filter']['comm_start_date']) ? substr($ref['columns_filter']['comm_start_date'], 0, 10) : '';

    1662. 

  1662. 		$end_date = isset($ref['columns_filter']['comm_end_date']) ? substr($ref['columns_filter']['comm_end_date'], 0, 10) : '';

    1663. 

  1663. 

    1664. 

  1664. 		//check and validate time period dates

    1665. 

  1665. 		if (!preg_match('/^(\d{4})\D?(0[1-9]|1[0-2])\D?([12]\d|0[1-9]|3[01])$/', $start_date) || $start_date == '0000-00-00')

    1666. 

  1666. 			$start_date = '';

    1667. 

  1667. 		if (!preg_match('/^(\d{4})\D?(0[1-9]|1[0-2])\D?([12]\d|0[1-9]|3[01])$/', $end_date) || $end_date == '0000-00-00')

    1668. 

  1668. 			$end_date = '';

    1669. 

  1669. 

    1670. 

  1670. 		if ($start_date != '') $start_date .= ' 00:00:00';

    1671. 

  1671. 		if ($end_date != '') $end_date .= ' 23:59:59';

    1672. 

  1672. 		if ($start_date != '' && $end_date != '')

    1673. 

  1673. 			if ($start_date > $end_date) { //invalid time period

    1674. 

  1674. 				$start_date = '';

    1675. 

  1675. 				$end_date = '';

    1676. 

  1676. 			}

    1677. 

  1677. 

    1678. 

  1678. 

    1679. 

  1679. 		//instantiate an acl manager

    1680. 

  1680. 		$acl_man = new DoceboACLManager();

    1681. 

  1681. 		$acl_man->include_suspended = true;

    1682. 

  1682. 

    1683. 

  1683. 		//extract user idst from selection

    1684. 

  1684. 		if ($sel_all) {

    1685. 

  1685. 			$arr_selected_users = $acl_man->getAllUsersIdst();

    1686. 

  1686. 		} else {

    1687. 

  1687. 			$arr_selected_users = $acl_man->getAllUsersFromIdst($arr_selected_users);

    1688. 

  1688. 		}

    1689. 

  1689. 

    1690. 

  1690. 		//admin users filter

    1691. 

  1691. 		$userlevelid = Docebo::user()->getUserLevelId();

    1692. 

  1692. 		if ( $userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {

    1693. 

  1693. 			require_once(_base_.'/lib/lib.preference.php');

    1694. 

  1694. 			$adminManager = new AdminPreference();

    1695. 

  1695. 			$admin_tree = $adminManager->getAdminTree(Docebo::user()->getIdST());

    1696. 

  1696. 			$admin_users = $acl_man->getAllUsersFromIdst($admin_tree);

    1697. 

  1697. 			$admin_users = array_unique($admin_users);

    1698. 

  1698. 			//filter users selection by admin visible users

    1699. 

  1699. 			$arr_selected_users = array_intersect($arr_selected_users, $admin_users);

    1700. 

  1700. 			//free some memory

    1701. 

  1701. 			unset($admin_tree);

    1702. 

  1702. 			unset($admin_users);

    1703. 

  1703. 			unset($adminManager);

    1704. 

  1704. 		}

    1705. 

  1705. 

    1706. 

  1706. 		//Has the "All communications" options been selected ?

    1707. 

  1707. 		if ($comm_all) {

    1708. 

  1708. 			$query = "SELECT id_comm FROM %lms_communication";

    1709. 

  1709. 			$res = $this->db->query($query);

    1710. 

  1710. 			$arr_selected_comm = array();

    1711. 

  1711. 			while (list($id_comm) = $this->db->fetch_row($res))

    1712. 

  1712. 				$arr_selected_comm[] = $id_comm;

    1713. 

  1713. 		}

    1714. 

  1714. 

    1715. 

  1715. 		//check selected users ...

    1716. 

  1716. 		//$arr_selected_users = array(); //list of users selected in the filter (users, groups and org.branches)

    1717. 

  1717. 		if ($arr_selected_users <= 0) {

    1718. 

  1718. 			cout('<p>'.$_ERR_NOUSER.'</p>');

    1719. 

  1719. 			return;

    1720. 

  1720. 		}

    1721. 

  1721. 

    1722. 

  1722. 		//$arr_selected_comm = array(); //list of communications selected in the filter

    1723. 

  1723. 		if ($arr_selected_comm <= 0) {

    1724. 

  1724. 			out('<p>'.$_ERR_NOCOMM.'</p>');

    1725. 

  1725. 			return;

    1726. 

  1726. 		}

    1727. 

  1727. 

    1728. 

  1728. 		//order selected communications by publish date

    1729. 

  1729. 		$arr_selected_comm = array_unique($arr_selected_comm);

    1730. 

  1730. 		$query = "SELECT id_comm FROM %lms_communication "

    1731. 

  1731. 			." WHERE id_comm IN (".implode(",", $arr_selected_comm).") "

    1732. 

  1732. 			." ORDER BY publish_date DESC, title ASC";

    1733. 

  1733. 		$res = $this->db->query($query);

    1734. 

  1734. 		if ($this->db->num_rows($res) == count($arr_selected_comm)) {

    1735. 

  1735. 			$arr_selected_comm = array();

    1736. 

  1736. 			while (list($id_comm) = $this->db->fetch_row($res)) {

    1737. 

  1737. 				$arr_selected_comm[] = $id_comm;

    1738. 

  1738. 			}

    1739. 

  1739. 		}

    1740. 

  1740. 

    1741. 

  1741. 		$arr_comm = array(); //array $id_comm => list of generic idst

    1742. 

  1742. 		foreach ($arr_selected_comm as $id_comm) $arr_comm[$id_comm] = array(); //if no users have been assigned to the games, than display as 0 - 0

    1743. 

  1743. 		$arr_idst = array(); //flat list of idst

    1744. 

  1744. 		$query = "SELECT * FROM %lms_communication_access WHERE id_comm IN (".implode(",", $arr_selected_comm).")";

    1745. 

  1745. 		$res = $this->db->query($query);

    1746. 

  1746. 		while (list($id_comm, $idst) = $this->db->fetch_row($res)) {

    1747. 

  1747. 			$arr_idst[] = $idst;

    1748. 

  1748. 			$arr_comm[$id_comm][] = $idst;

    1749. 

  1749. 		}

    1750. 

  1750. 

    1751. 

  1751. 		if (count($arr_idst) <= 0) {

    1752. 

  1752. 			cout('<p>'.$_ERR_NOUSER.'</p>');

    1753. 

  1753. 			return;

    1754. 

  1754. 		}

    1755. 

  1755. 

    1756. 

  1756. 		$arr_groups = array(); //flat list of group idst

    1757. 

  1757. 		$query = "SELECT idst FROM %adm_group WHERE idst IN (".implode(",", $arr_idst).")";

    1758. 

  1758. 		$res = $this->db->query($query);

    1759. 

  1759. 		while (list($idst) = $this->db->fetch_row($res)) {

    1760. 

  1760. 			$arr_groups[] = $idst;

    1761. 

  1761. 		}

    1762. 

  1762. 

    1763. 

  1763. 		//if any group selected, then extract users and create an array [id_group][id_user]

    1764. 

  1764. 		$arr_idst_users_flat = array();

    1765. 

  1765. 		$arr_members = array(); //array $idst group => list of idst

    1766. 

  1766. 		if (count($arr_groups) > 0) {

    1767. 

  1767. 			$query = "SELECT idst, idstMember FROM %adm_group_members WHERE "

    1768. 

  1768. 				." idst IN (".implode(",", $arr_groups).")"

    1769. 

  1769. 				." AND idstMember IN (".implode(",", $arr_selected_users).")";

    1770. 

  1770. 			$res = $this->db->query($query);

    1771. 

  1771. 			while (list($idst, $idstMember) = $this->db->fetch_row($res)) {

    1772. 

  1772. 				$arr_members[$idst][] = $idstMember;

    1773. 

  1773. 				$arr_idst_users_flat[] = $idstMember;

    1774. 

  1774. 			}

    1775. 

  1775. 		}

    1776. 

  1776. 

    1777. 

  1777. 		//set an array with all users idst ($_all)

    1778. 

  1778. 		$diff = array_diff($arr_selected_users, $arr_groups);

    1779. 

  1779. 		$_all_users = array_merge($arr_idst_users_flat, $diff);

    1780. 

  1780. 		unset($diff);

    1781. 

  1781. 

    1782. 

  1782. 		if (count($_all_users) <= 0) {

    1783. 

  1783. 			cout('<p>'.$_ERR_NOUSER.'</p>');

    1784. 

  1784. 			return;

    1785. 

  1785. 		}

    1786. 

  1786. 

    1787. 

  1787. 		//users have been extracted by group, now calculate report's rows ----------

    1788. 

  1788. 

    1789. 

  1789. 		//get communications info data and put it in an array by id_comm => {info}

    1790. 

  1790. 		$arr_comm_data = array();

    1791. 

  1791. 		$query = "SELECT * FROM %lms_communication WHERE id_comm IN (".implode(",", $arr_selected_comm).")";

    1792. 

  1792. 		$res = $this->db->query($query);

    1793. 

  1793. 		while ($obj = $this->db->fetch_obj($res)) {

    1794. 

  1794. 			$arr_comm_data[$obj->id_comm] = array(

    1795. 

  1795. 				'title' => $obj->title,

    1796. 

  1796. 				'type_of' => $obj->type_of,

    1797. 

  1797. 				'publish_date' => $obj->publish_date

    1798. 

  1798. 			);

    1799. 

  1799. 		}

    1800. 

  1800. 

    1801. 

  1801. 		//which selected communication has been seen by selected users?

    1802. 

  1802. 		$arr_viewed = array();

    1803. 

  1803. 		$query = "SELECT idReference, COUNT(idUser) as count "

    1804. 

  1804. 			." FROM %lms_communication_track WHERE status IN ('completed', 'passed') "

    1805. 

  1805. 			." AND idUser IN (".implode(",", $_all_users).") "

    1806. 

  1806. 			." AND id_comm IN (".implode(",", $arr_selected_comm).") "

    1807. 

  1807. 			.($start_date != '' ? " AND dateAttempt >= '".$start_date."' " : "")

    1808. 

  1808. 			.($end_date != '' ? " AND dateAttempt <= '".$end_date."' " : "")

    1809. 

  1809. 			." GROUP BY id_comm";

    1810. 

  1810. 		$res = $this->db->query($query);

    1811. 

  1811. 		while ($obj = $this->db->fetch_obj($res)) {

    1812. 

  1812. 			$arr_viewed[$obj->id_comm] = $obj->count;

    1813. 

  1813. 		}

    1814. 

  1814. 

    1815. 

  1815. /*

    1816. 

  1816. 		//user details buffer

    1817. 

  1817. 		$acl_man = Docebo::user()->getAclManager();

    1818. 

  1818. 		$user_details = array();

    1819. 

  1819. 		$query = "SELECT idst, userid FROM %adm_user WHERE idst IN (".implode(",", $_all_users).")";

    1820. 

  1820. 		$res = $this->db->query($query);

    1821. 

  1821. 		while ($obj = $this->db->fetch_obj($res)) {

    1822. 

  1822. 			$user_details[$obj->idst] = $acl_man($obj->userid);

    1823. 

  1823. 		}

    1824. 

  1824. */

    1825. 

  1825. 		//set table properties and buffer

    1826. 

  1826. 		$head = array(

    1827. 

  1827. 		Lang::t('_DATE', 'report'),

    1828. 

  1828. 			Lang::t('_COMMUNICATIONS_TITLE', 'report'),

    1829. 

  1829. 			Lang::t('_COMMUNICATIONS_TYPE', 'report'),

    1830. 

  1830. 			Lang::t('_COMMUNICATIONS_SEEN', 'report'),

    1831. 

  1831. 			Lang::t('_COMMUNICATIONS_TOTAL', 'report'),

    1832. 

  1832. 			Lang::t('_PERCENTAGE', 'report')

    1833. 

  1833. 		);

    1834. 

  1834. 

    1835. 

  1835. 

    1836. 

  1836. 		$buffer = new ReportTablePrinter();

    1837. 

  1837. 		$buffer->openTable('','');

    1838. 

  1838. 

    1839. 

  1839. 		$buffer->openHeader();

    1840. 

  1840. 		$buffer->addHeader($head);

    1841. 

  1841. 		$buffer->closeHeader();

    1842. 

  1842. 

    1843. 

  1843. 		$buffer->openBody();

    1844. 

  1844. 

    1845. 

  1845. 

    1846. 

  1846. 		//rows cycle

    1847. 

  1847. 		foreach ($arr_comm as $id_comm => $comm_id_list) {

    1848. 

  1848. 			//calculate total assigned users for every communication

    1849. 

  1849. 			$count = 0;

    1850. 

  1850. 			foreach ($comm_id_list as $idst) {

    1851. 

  1851. 				if (array_key_exists($idst, $arr_members)) {

    1852. 

  1852. 					foreach ($arr_members[$idst] as $idst_user) {

    1853. 

  1853. 						$count++;

    1854. 

  1854. 					}

    1855. 

  1855. 				} else {

    1856. 

  1856. 					$count++;

    1857. 

  1857. 				}

    1858. 

  1858. 			}

    1859. 

  1859. 

    1860. 

  1860. 			//line (one per communication)

    1861. 

  1861. 			$line = array();

    1862. 

  1862. 			$type_of = $arr_comm_data[$id_comm]['type_of'];

    1863. 

  1863. 			$seen = isset($arr_viewed[$id_comm]) ? $arr_viewed[$id_comm] : 0;

    1864. 

  1864. 

    1865. 

  1865. 			$line[] = $arr_comm_data[$id_comm]['publish_date'];

    1866. 

  1866. 			$line[] = $arr_comm_data[$id_comm]['title'];

    1867. 

  1867. 			$line[] = isset($lang_type[$type_of]) ? $lang_type[$type_of] : '';

    1868. 

  1868. 			//$line[] = $arr_comm_data[$id_comm]['publish_date'];

    1869. 

  1869. 			$line[] = $seen;

    1870. 

  1870. 			$line[] = $count;

    1871. 

  1871. 			$line[] = number_format(($count > 0 ? $seen/$count : 0)*100, 2, ',', '').' %';

    1872. 

  1872. 

    1873. 

  1873. 			$buffer->addLine($line);

    1874. 

  1874. 		}

    1875. 

  1875. 

    1876. 

  1876. 		$buffer->closeBody();

    1877. 

  1877. 		$buffer->closeTable();

    1878. 

  1878. 

    1879. 

  1879. 		cout($buffer->get());

    1880. 

  1880. 	}

    1881. 

  1881. 

    1882. 

  1882. 

    1883. 

  1883. 

    1884. 

  1884. 	//----------------------------------------------------------------------------

    1885. 

  1885. 	//---- games report part --------------------------------------------

    1886. 

  1886. 	//----------------------------------------------------------------------------

    1887. 

  1887. 

    1888. 

  1888. 

    1889. 

  1889. 	function show_report_games($data = NULL, $other = '') {

    1890. 

  1890. 		if ($data===NULL)

    1891. 

  1891. 			cout( $this->_get_games_query() );

    1892. 

  1892. 		else

    1893. 

  1893. 			cout( $this->_get_games_query('html', $data, $other) );

    1894. 

  1894. 	}

    1895. 

  1895. 

    1896. 

  1896. 	function get_games_filter() {

    1897. 

  1897. 		$back_url = $this->back_url;

    1898. 

  1898. 		$jump_url = $this->jump_url;

    1899. 

  1899. 		$next_url = $this->next_url;

    1900. 

  1900. 

    1901. 

  1901. 		//preliminary checks

    1902. 

  1902. 		if (isset($_POST['undo_filter'])) Util::jump_to($back_url);

    1903. 

  1903. 

    1904. 

  1904. 		if (!isset($_SESSION['report_tempdata']['columns_filter'])) {

    1905. 

  1905. 			$_SESSION['report_tempdata']['columns_filter'] = array(

    1906. 

  1906. 				'comp_selection' => array(),

    1907. 

  1907. 				'all_games' => false,

    1908. 

  1908. 				'comp_start_date' => '',

    1909. 

  1909. 				'comp_end_date' => ''

    1910. 

  1910. 			);

    1911. 

  1911. 		}

    1912. 

  1912. 		$ref =& $_SESSION['report_tempdata']['columns_filter'];

    1913. 

  1913. 

    1914. 

  1914. 

    1915. 

  1915. 		if (isset($_POST['update_tempdata'])) {

    1916. 

  1916. 			$ref['all_games'] = Get::req('all_games', DOTY_INT, 0) > 0;

    1917. 

  1917. 			$ref['comp_selection'] = Get::req('comp_selection', DOTY_MIXED, array());

    1918. 

  1918. 			$ref['comp_start_date'] = Format::dateDb(Get::req('comp_start_date', DOTY_STRING, ''), 'date');

    1919. 

  1919. 			$ref['comp_end_date'] = Format::datedb(Get::req('comp_end_date', DOTY_STRING, ''), 'date');

    1920. 

  1920. 		} else {

    1921. 

  1921. 			//...

    1922. 

  1922. 		}

    1923. 

  1923. 

    1924. 

  1924. 		//filter setting done, go to next step

    1925. 

  1925. 		if (isset($_POST['import_filter']) || isset($_POST['show_filter']) || isset($_POST['pre_filter'])) {

    1926. 

  1926. 			$temp_url = $next_url;

    1927. 

  1927. 			if (isset($_POST['pre_filter'])) $temp_url.='&show=1&nosave=1';

    1928. 

  1928. 			if (isset($_POST['show_filter'])) $temp_url.='&show=1';

    1929. 

  1929. 			Util::jump_to($temp_url);

    1930. 

  1930. 		}

    1931. 

  1931. 

    1932. 

  1932. 		//draw filter boxes

    1933. 

  1933. 		$html = '';

    1934. 

  1934. 

    1935. 

  1935. 		//time period

    1936. 

  1936. 		$box = new ReportBox('comm_selector');

    1937. 

  1937. 		$box->title = Lang::t('_TIME_PERIOD_FILTER', 'report');

    1938. 

  1938. 		$box->description = false;

    1939. 

  1939. 		$box->body .= Form::getDatefield(Lang::t('_FROM', 'standard'), 'comp_start_date', 'comp_start_date', $ref['comp_start_date']);

    1940. 

  1940. 		$box->body .= Form::getDatefield(Lang::t('_TO', 'standard'), 'comp_end_date', 'comp_end_date', $ref['comp_end_date']);

    1941. 

  1941. 

    1942. 

  1942. 		$html .= $box->get();

    1943. 

  1943. 

    1944. 

  1944. 		//draw games selector

    1945. 

  1945. 		$box = new ReportBox('comp_selector');

    1946. 

  1946. 		$box->title = Lang::t('_CONTEST');

    1947. 

  1947. 		$box->description = false;

    1948. 

  1948. 

    1949. 

  1949. 		require_once(_lms_.'/lib/lib.report.php'); //the comm. table function

    1950. 

  1950. 		$box->body .= Form::getCheckbox(Lang::t('_ALL', 'report'), 'all_games', 'all_games', 1, $ref['all_games']);

    1951. 

  1951. 		$box->body .= '<br />';

    1952. 

  1952. 		$box->body .= getGamesTable($ref['comp_selection']);

    1953. 

  1953. 		$box->body .= Form::getHidden('update_tempdata', 'update_tempdata', 1);

    1954. 

  1954. 

    1955. 

  1955. 		$html .= $box->get();

    1956. 

  1956. 

    1957. 

  1957. 		cout($html);

    1958. 

  1958. 	}

    1959. 

  1959. 

    1960. 

  1960. 

    1961. 

  1961. 

    1962. 

  1962. 

    1963. 

  1963. 	function _get_games_query($type = 'html', $report_data = NULL, $other = '') {

    1964. 

  1964. 		require_once(dirname(__FILE__).'/report_tableprinter.php');

    1965. 

  1965. 		if ($report_data==NULL) $ref =& $_SESSION['report_tempdata']; else $ref =& $report_data;

    1966. 

  1966. 

    1967. 

  1967. 		$_ERR_NOUSER = Lang::t('_EMPTY_SELECTION', 'report');

    1968. 

  1968. 		$_ERR_NOCOMP = Lang::t('_EMPTY_SELECTION', 'report');

    1969. 

  1969. 		$_ERR_NODATA = Lang::t('_NO_CONTENT', 'report');

    1970. 

  1970. 

    1971. 

  1971. 		require_once(_lms_.'/lib/lib.report.php');

    1972. 

  1972. 		$lang_type = _getLOtranslations();

    1973. 

  1973. 

    1974. 

  1974. 		$sel_all = $ref['rows_filter']['select_all'];

    1975. 

  1975. 		$arr_selected_users = $ref['rows_filter']['selection']; //list of users selected in the filter (users, groups and org.branches)

    1976. 

  1976. 

    1977. 

  1977. 		$comp_all = $ref['columns_filter']['all_games'];

    1978. 

  1978. 		$arr_selected_comp = $ref['columns_filter']['comp_selection']; //list of communications selected in the filter

    1979. 

  1979. 

    1980. 

  1980. 		$start_date = substr($ref['columns_filter']['comp_start_date'], 0, 10);

    1981. 

  1981. 		$end_date = substr($ref['columns_filter']['comp_end_date'], 0, 10);

    1982. 

  1982. 

    1983. 

  1983. 		//check and validate time period dates

    1984. 

  1984. 		if (!preg_match('/^(\d{4})\D?(0[1-9]|1[0-2])\D?([12]\d|0[1-9]|3[01])$/', $start_date) || $start_date == '0000-00-00')

    1985. 

  1985. 			$start_date = '';

    1986. 

  1986. 		if (!preg_match('/^(\d{4})\D?(0[1-9]|1[0-2])\D?([12]\d|0[1-9]|3[01])$/', $end_date) || $end_date == '0000-00-00')

    1987. 

  1987. 			$end_date = '';

    1988. 

  1988. 

    1989. 

  1989. 		if ($start_date != '') $start_date .= ' 00:00:00';

    1990. 

  1990. 		if ($end_date != '') $end_date .= ' 23:59:59';

    1991. 

  1991. 		if ($start_date != '' && $end_date != '')

    1992. 

  1992. 			if ($start_date > $end_date) { //invalid time period

    1993. 

  1993. 				$start_date = '';

    1994. 

  1994. 				$end_date = '';

    1995. 

  1995. 			}

    1996. 

  1996. 

    1997. 

  1997. 

    1998. 

  1998. 		//instantiate acl manager

    1999. 

  1999. 		$acl_man = new DoceboACLManager();

    2000. 

  2000. 		$acl_man->include_suspended = true;

    2001. 

  2001. 

    2002. 

  2002. 		//extract user idst from selection

    2003. 

  2003. 		if ($sel_all) {

    2004. 

  2004. 			$arr_selected_users = $acl_man->getAllUsersIdst();

    2005. 

  2005. 		} else {

    2006. 

  2006. 			$arr_selected_users = $acl_man->getAllUsersFromIdst($arr_selected_users);

    2007. 

  2007. 		}

    2008. 

  2008. 

    2009. 

  2009. 		//admin users filter

    2010. 

  2010. 		$userlevelid = Docebo::user()->getUserLevelId();

    2011. 

  2011. 		if ( $userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {

    2012. 

  2012. 			require_once(_base_.'/lib/lib.preference.php');

    2013. 

  2013. 			$adminManager = new AdminPreference();

    2014. 

  2014. 			$admin_tree = $adminManager->getAdminTree(Docebo::user()->getIdST());

    2015. 

  2015. 			$admin_users = $acl_man->getAllUsersFromIdst($admin_tree);

    2016. 

  2016. 			$admin_users = array_unique($admin_users);

    2017. 

  2017. 			//filter users selection by admin visible users

    2018. 

  2018. 			$arr_selected_users = array_intersect($arr_selected_users, $admin_users);

    2019. 

  2019. 			//free some memory

    2020. 

  2020. 			unset($admin_tree);

    2021. 

  2021. 			unset($admin_users);

    2022. 

  2022. 			unset($adminManager);

    2023. 

  2023. 		}

    2024. 

  2024. 

    2025. 

  2025. 		//Has the "All games" options been selected ?

    2026. 

  2026. 		if ($comp_all) {

    2027. 

  2027. 			$query = "SELECT id_game FROM %lms_games";

    2028. 

  2028. 			$res = $this->db->query($query);

    2029. 

  2029. 			$arr_selected_comp = array();

    2030. 

  2030. 			while (list($id_game) = $this->db->fetch_row($res))

    2031. 

  2031. 				$arr_selected_comp[] = $id_game;

    2032. 

  2032. 		}

    2033. 

  2033. 

    2034. 

  2034. 		//check selected users ...

    2035. 

  2035. 		//$arr_selected_users = array(); //list of users selected in the filter (users, groups and org.branches)

    2036. 

  2036. 		if ($arr_selected_users <= 0) {

    2037. 

  2037. 			cout('<p>'.$_ERR_NOUSER.'</p>');

    2038. 

  2038. 			return;

    2039. 

  2039. 		}

    2040. 

  2040. 

    2041. 

  2041. 		//$arr_selected_comp = array(); //list of communications selected in the filter

    2042. 

  2042. 		if ($arr_selected_comp <= 0) {

    2043. 

  2043. 			cout('<p>'.$_ERR_NOCOMP.'</p>');

    2044. 

  2044. 			return;

    2045. 

  2045. 		}

    2046. 

  2046. 

    2047. 

  2047. 		$arr_comp = array(); //array $id_comm => list of generic idst

    2048. 

  2048. 		foreach ($arr_selected_comp as $id_game) $arr_comp[$id_game] = array(); //if no users have been assigned to the games, than display as 0 - 0

    2049. 

  2049. 		$arr_idst = array(); //flat list of idst

    2050. 

  2050. 		$query = "SELECT * FROM %lms_games_access WHERE id_comp IN (".implode(",", $arr_selected_comp).")";

    2051. 

  2051. 		$res = $this->db->query($query);

    2052. 

  2052. 		while (list($id_game, $idst) = $this->db->fetch_row($res)) {

    2053. 

  2053. 			$arr_idst[] = $idst;

    2054. 

  2054. 			$arr_comp[$id_game][] = $idst;

    2055. 

  2055. 		}

    2056. 

  2056. 

    2057. 

  2057. 		if (count($arr_idst) <= 0) {

    2058. 

  2058. 			cout('<p>'.$_ERR_NOUSER.'</p>');

    2059. 

  2059. 			return;

    2060. 

  2060. 		}

    2061. 

  2061. 

    2062. 

  2062. 		$arr_groups = array(); //flat list of group idst

    2063. 

  2063. 		$query = "SELECT idst FROM %adm_group WHERE idst IN (".implode(",", $arr_idst).")";

    2064. 

  2064. 		$res = $this->db->query($query);

    2065. 

  2065. 		while (list($idst) = $this->db->fetch_row($res)) {

    2066. 

  2066. 			$arr_groups[] = $idst;

    2067. 

  2067. 		}

    2068. 

  2068. 

    2069. 

  2069. 		//if any group selected, then extract users and create an array [id_group][id_user]

    2070. 

  2070. 		$arr_idst_users_flat = array();

    2071. 

  2071. 		$arr_members = array(); //array $idst group => list of idst

    2072. 

  2072. 		if (count($arr_groups) > 0) {

    2073. 

  2073. 			$query = "SELECT idst, idstMember FROM %adm_group_members WHERE "

    2074. 

  2074. 				." idst IN (".implode(",", $arr_groups).")"

    2075. 

  2075. 				." AND idstMember IN (".implode(",", $arr_selected_users).")";

    2076. 

  2076. 			$res = $this->db->query($query);

    2077. 

  2077. 			while (list($idst, $idstMember) = $this->db->fetch_row($res)) {

    2078. 

  2078. 				$arr_members[$idst][] = $idstMember;

    2079. 

  2079. 				$arr_idst_users_flat[] = $idstMember;

    2080. 

  2080. 			}

    2081. 

  2081. 		}

    2082. 

  2082. 

    2083. 

  2083. 		//set an array with all users idst ($_all)

    2084. 

  2084. 		$diff = array_diff($arr_selected_users, $arr_groups);

    2085. 

  2085. 		$_all_users = array_merge($arr_idst_users_flat, $diff);

    2086. 

  2086. 		unset($diff);

    2087. 

  2087. 

    2088. 

  2088. 		if (count($_all_users) <= 0) {

    2089. 

  2089. 			cout('<p>'.$_ERR_NOUSER.'</p>');

    2090. 

  2090. 			return;

    2091. 

  2091. 		}

    2092. 

  2092. 

    2093. 

  2093. 		//users have been extracted by group, now calculate report's rows ----------

    2094. 

  2094. 

    2095. 

  2095. 		//get games info data and put it in an array by id_game => {info}

    2096. 

  2096. 		$arr_comp_data = array();

    2097. 

  2097. 		$query = "SELECT * FROM %lms_games WHERE id_game IN (".implode(",", $arr_selected_comp).")";

    2098. 

  2098. 		$res = $this->db->query($query);

    2099. 

  2099. 		while ($obj = $this->db->fetch_obj($res)) {

    2100. 

  2100. 			$arr_comp_data[$obj->id_game] = array(

    2101. 

  2101. 				'title' => $obj->title,

    2102. 

  2102. 				'type_of' => $obj->type_of,

    2103. 

  2103. 				'start_date' => $obj->start_date,

    2104. 

  2104. 				'end_date' => $obj->end_date

    2105. 

  2105. 			);

    2106. 

  2106. 		}

    2107. 

  2107. 

    2108. 

  2108. 		//which selected communication has been seen by selected users?

    2109. 

  2109. 		$arr_viewed = array();

    2110. 

  2110. 		$query = "SELECT idReference, COUNT(idUser) as count "

    2111. 

  2111. 			." FROM %lms_games_track WHERE status IN ('completed', 'passed') "

    2112. 

  2112. 			." AND idUser IN (".implode(",", $_all_users).") "

    2113. 

  2113. 			." AND idReference IN (".implode(",", $arr_selected_comp).") "

    2114. 

  2114. 			.($start_date != '' ? " AND dateAttempt >= '".$start_date."' " : "")

    2115. 

  2115. 			.($end_date != '' ? " AND dateAttempt <= '".$end_date."' " : "")

    2116. 

  2116. 			." GROUP BY idReference";

    2117. 

  2117. 		$res = $this->db->query($query);

    2118. 

  2118. 		while ($obj = $this->db->fetch_obj($res)) {

    2119. 

  2119. 			$arr_viewed[$obj->idReference] = $obj->count;

    2120. 

  2120. 		}

    2121. 

  2121. 

    2122. 

  2122. 		//calculate average values, no conditions on the status

    2123. 

  2123. 		$arr_average = array();

    2124. 

  2124. 		$query = "SELECT idReference, AVG(current_score) as average_current_score, "

    2125. 

  2125. 			." AVG(max_score) as average_max_score, AVG(num_attempts) as average_num_attempts "

    2126. 

  2126. 			." FROM %lms_games_track WHERE idUser IN (".implode(",", $_all_users).") "

    2127. 

  2127. 			." AND idReference IN (".implode(",", $arr_selected_comp).") "

    2128. 

  2128. 			.($start_date != '' ? " AND dateAttempt >= '".$start_date."' " : "")

    2129. 

  2129. 			.($end_date != '' ? " AND dateAttempt <= '".$end_date."' " : "")

    2130. 

  2130. 			." GROUP BY idReference";

    2131. 

  2131. 		$res = $this->db->query($query);

    2132. 

  2132. 		while ($obj = $this->db->fetch_obj($res)) {

    2133. 

  2133. 			$arr_average[$obj->idReference] = $obj;

    2134. 

  2134. 		}

    2135. 

  2135. 

    2136. 

  2136. /*

    2137. 

  2137. 		//user details buffer

    2138. 

  2138. 		$acl_man = Docebo::user()->getAclManager();

    2139. 

  2139. 		$user_details = array();

    2140. 

  2140. 		$query = "SELECT idst, userid FROM %adm_user WHERE idst IN (".implode(",", $_all_users).")";

    2141. 

  2141. 		$res = $this->db->query($query);

    2142. 

  2142. 		while ($obj = $this->db->fetch_obj($res)) {

    2143. 

  2143. 			$user_details[$obj->idst] = $acl_man($obj->userid);

    2144. 

  2144. 		}

    2145. 

  2145. */

    2146. 

  2146. 		//set table properties and buffer

    2147. 

  2147. 		$head = array(

    2148. 

  2148. 			Lang::t('_GAMES_TITLE', 'report'),

    2149. 

  2149. 			Lang::t('_GAMES_TYPE', 'report'),

    2150. 

  2150. 			Lang::t('_FROM', 'standard'),

    2151. 

  2151. 			Lang::t('_TO', 'standard'),

    2152. 

  2152. 			Lang::t('_GAMES_ATTEMPTED', 'report'),

    2153. 

  2153. 			Lang::t('_GAMES_TOTAL', 'report'),

    2154. 

  2154. 			Lang::t('_GAMES_PERCENT', 'report'),

    2155. 

  2155. 			Lang::t('_GAMES_AVG_SCORE', 'report'),

    2156. 

  2156. 			Lang::t('_GAMES_AVG_MAX_SCORE', 'report'),

    2157. 

  2157. 			Lang::t('_GAMES_AVG_NUM_ATTEMPTS', 'report'),

    2158. 

  2158. 		);

    2159. 

  2159. 

    2160. 

  2160. 

    2161. 

  2161. 		$buffer = new ReportTablePrinter();

    2162. 

  2162. 		$buffer->openTable('','');

    2163. 

  2163. 

    2164. 

  2164. 		$buffer->openHeader();

    2165. 

  2165. 		$buffer->addHeader($head);

    2166. 

  2166. 		$buffer->closeHeader();

    2167. 

  2167. 

    2168. 

  2168. 		$buffer->openBody();

    2169. 

  2169. 

    2170. 

  2170. 

    2171. 

  2171. 		//rows cycle

    2172. 

  2172. 		foreach ($arr_comp as $id_game => $comp_id_list) {

    2173. 

  2173. 			//calculate total assigned users for every communication

    2174. 

  2174. 			$count = 0;

    2175. 

  2175. 			foreach ($comp_id_list as $idst) {

    2176. 

  2176. 				if (array_key_exists($idst, $arr_members)) {

    2177. 

  2177. 					foreach ($arr_members[$idst] as $idst_user) {

    2178. 

  2178. 						$count++;

    2179. 

  2179. 					}

    2180. 

  2180. 				} else {

    2181. 

  2181. 					$count++;

    2182. 

  2182. 				}

    2183. 

  2183. 			}

    2184. 

  2184. 

    2185. 

  2185. 			//line (one per communication)

    2186. 

  2186. 			$line = array();

    2187. 

  2187. 			$type_of = $arr_comp_data[$id_game]['type_of'];

    2188. 

  2188. 			$completed_by = isset($arr_viewed[$id_game]) ? $arr_viewed[$id_game] : 0;

    2189. 

  2189. 

    2190. 

  2190. 			$line[] = $arr_comp_data[$id_game]['title'];

    2191. 

  2191. 			$line[] = isset($lang_type[$type_of]) ? $lang_type[$type_of] : '';

    2192. 

  2192. 			$line[] = Format::date($arr_comp_data[$id_game]['start_date'], 'date');

    2193. 

  2193. 			$line[] = Format::date($arr_comp_data[$id_game]['end_date'], 'date');

    2194. 

  2194. 			$line[] = $completed_by;

    2195. 

  2195. 			$line[] = $count;

    2196. 

  2196. 			$line[] = number_format(($count > 0 ? $completed_by/$count : 0)*100, 2, ',', '').' %';

    2197. 

  2197. 

    2198. 

  2198. 			$avg1 = isset($arr_average[$id_game]) ? $arr_average[$id_game]->average_current_score : '';

    2199. 

  2199. 			$avg2 = isset($arr_average[$id_game]) ? $arr_average[$id_game]->average_max_score : '';

    2200. 

  2200. 			$avg3 = isset($arr_average[$id_game]) ? $arr_average[$id_game]->average_num_attempts : '';

    2201. 

  2201. 

    2202. 

  2202. 			$line[] = number_format($avg1, 2, ',', '.');

    2203. 

  2203. 			$line[] = number_format($avg2, 2, ',', '.');

    2204. 

  2204. 			$line[] = number_format($avg3, 2, ',', '.');

    2205. 

  2205. 

    2206. 

  2206. 			$buffer->addLine($line);

    2207. 

  2207. 		}

    2208. 

  2208. 

    2209. 

  2209. 		$buffer->closeBody();

    2210. 

  2210. 		$buffer->closeTable();

    2211. 

  2211. 

    2212. 

  2212. 		cout($buffer->get());

    2213. 

  2213. 	}

    2214. 

  2214. 

    2215. 

  2215. 

    2216. 

  2216. }
    2217.