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

/* ======================================================================== \
| 	DOCEBO - The E-Learning Suite											|
| 																			|
| 	Copyright (c) 2008 (Docebo)												|
| 	http://www.docebo.com													|
|   License 	http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt		|
\ ======================================================================== */

require_once($GLOBALS['where_lms'].'/lib/lib.course.php');
require_once(dirname(__FILE__).'/class.report.php');


define('_RA_CATEGORY_COURSES', 'courses');


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

class Report_Aggregate extends Report {

	var $page_title = false;

	function Report_Aggregate() {
		$this->lang =& DoceboLanguage::createInstance('report', 'framework');
		$this->_set_columns_category(_RA_CATEGORY_COURSES, $this->lang->def('_RA_CAT_COURSES'), 'get_courses_filter', 'show_report_courses', '_get_courses_query');
	}
	
	
	//users and orgchart selection
	function get_rows_filter() {
		$back_url = $this->back_url;
		$jump_url = $this->jump_url;
		$next_url = $this->next_url;

		require_once($GLOBALS['where_framework'].'/lib/lib.form.php');
		require_once($GLOBALS['where_framework'].'/lib/lib.directory.php');
		require_once($GLOBALS['where_framework'].'/class.module/class.directory.php');
		require_once($GLOBALS['where_lms'].'/lib/lib.course.php');
		require_once($GLOBALS['where_lms'].'/lib/lib.course_managment.php');

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

		//update session
		$ref =& $_SESSION['report_tempdata'];
		if (!isset($ref['rows_filter'])) {
			$ref['rows_filter'] = array( //default values
				'select_all' => 'false',
				'selection_type' => 'users',
				'selection' => array()
			);
		} else {
			//already resolved in switch block
		}

		$step = get_req('step', DOTY_ALPHANUM, 'sel_type');
		switch ($step) {
		
			case 'sel_type': {
				$values = array('users' => $this->lang->def('_USERS'), 'groups'=>$this->lang->def('_GROUPS'));
				$sel_val = (isset($ref['rows_filter']['selection_type']) ? $ref['rows_filter']['selection_type'] : 'users');

				$out  = Form::openForm('selection_type_form', $jump_url);
				$out .= Form::getOpenFieldSet($this->lang->def('_SELECT_TYPE_TITLE'));
				$out .= Form::getRadioSet($this->lang->def('_SELECT_TYPE'), 'selection_type', 'selection_type', array_flip($values) , $sel_val);
				$out .= Form::getCloseFieldset();
				$out .= Form::openButtonSpace();
				$out .= Form::getButton('ok_selection', 'ok_selection', $this->lang->def('_CONFIRM'));
				$out .= Form::getButton('undo', 'undo', $this->lang->def('_UNDO'));
				$out .= Form::closeButtonSpace();
				$out .= Form::getHidden('step', 'step', 'sel_data');
				$out .= Form::closeForm();
				cout($out);		
			} break;
			
			
			case 'sel_data': {
				$type = get_req('selection_type', DOTY_ALPHANUM, 'users');
			
				//$aclManager = new DoceboACLManager();
				$user_select = new Module_Directory();

				if (get_req('is_updating', DOTY_INT, 0)>0) {
					$elem_selected 	= $user_select->getSelection($_POST);
					$ref['rows_filter']['select_all'] = ( get_req('select_all', DOTY_INT, 0)>0 ? true : false );
					$ref['rows_filter']['selection_type'] = $type;
					$ref['rows_filter']['selection'] = $elem_selected;
				} else { //maybe redoundant
					if (!isset($ref['rows_filter']['select_all'])) $ref['rows_filter']['select_all'] = false;
					if (!isset($ref['rows_filter']['selection_type'])) $ref['rows_filter']['selection_type'] = 'groups';
					if (!isset($ref['rows_filter']['selection'])) $ref['rows_filter']['selection'] = array();
					$user_select->resetSelection($ref['rows_filter']['selection']);
					//$ref['users'] = array(); it should already have been set to void array, if non existent
				}

				if(isset($_POST['cancelselector']))
					jumpTo($back_url);
				elseif(isset($_POST['okselector']))
					jumpTo($next_url);

				//set page
				switch ($type) {
					case 'groups': {
						$user_select->show_user_selector = FALSE;
						$user_select->show_group_selector = TRUE;
						$user_select->show_orgchart_selector = TRUE;
					} break;
					case 'users': {
						$user_select->show_user_selector = TRUE;
						$user_select->show_group_selector = TRUE;
						$user_select->show_orgchart_selector = TRUE;
					} break;
				}
				$user_select->show_orgchart_simple_selector = FALSE;
				$user_select->multi_choice = TRUE;

				$user_select->addFormInfo(
					Form::getCheckbox($lang->def('_REPORT_FOR_ALL'), 'select_all', 'select_all', 1, ($ref['rows_filter']['select_all'] ? 1 : 0)).
					Form::getBreakRow().
					Form::getHidden('selection_type', 'selection_type', $type).
					Form::getHidden('step', 'step', 'sel_data').
					Form::getHidden('is_updating', 'is_updating', 1).
					Form::getHidden('substep', 'substep', 'user_selection').
					Form::getHidden('second_step', 'second_step', 1).
					Form::getHidden('is_updating', 'is_updating', 1));
				$user_select->setPageTitle($this->page_title);
				$user_select->parseInput($_POST);
				$user_select->loadSelector(str_replace('&', '&amp;', $jump_url),
					false,
					$this->lang->def('_CHOOSE_USER_FOR_REPORT'),
					true,
					true );
				
			
			} break;
		
		} 
	}
	
	
	
	
	function get_courses_filter() {
		$back_url = $this->back_url;
		$jump_url = $this->jump_url;
		$next_url = $this->next_url;

		require_once($GLOBALS['where_framework'].'/lib/lib.form.php');
		require_once($GLOBALS['where_lms'].'/lib/lib.course.php');
		require_once($GLOBALS['where_lms'].'/lib/lib.course_managment.php');

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

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

		if (isset($_POST['undo_filter'])) jumpTo($back_url);

		//set $_POST data in $_SESSION['report_tempdata']
		$selector = new Selector_Course();
		$ref =& $_SESSION['report_tempdata']['columns_filter'];
		if (isset($_POST['update_tempdata'])) {
			$selector->parseForState($_POST);
			$temp = $selector->getSelection($_POST);
			$ref['selected_courses'] = $temp;
			$ref['all_courses'] = (get_req('all_courses', DOTY_INT, 1)==1 ? true : false);
		}
		else
		{
			if (!isset($ref['selected_courses'])) {
				$ref = array(
					'all_courses' => true,
					'selected_courses' => array()
				);
			}
		}

		//back to columns category selection
		if (isset($_POST['undo_filter'])) {
			jumpTo($back_url);
		}
		
		//filter setting done, go to next step
		if (isset($_POST['import_filter']) || isset($_POST['show_filter']) || isset($_POST['pre_filter'])) {
			$temp_url = $next_url;
			if (isset($_POST['pre_filter'])) $temp_url.='&show=1&nosave=1';
			if (isset($_POST['show_filter'])) $temp_url.='&show=1';
			jumpTo($temp_url);
		}

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


		$box = new ReportBox('courses_selector');
		$box->title = $this->lang->def('_COURSES_SELECTION_TITLE');
		$box->description = $this->lang->def('_COURSES_SELECTION_DESC');

		$boxlang =& DoceboLanguage::createInstance('report', 'framework');
		$box->body .= '<div class="fc_filter_line filter_corr">';
		$box->body .= '<input id="all_courses" name="all_courses" type="radio" value="1" '.($ref['all_courses'] ? 'checked="checked"' : '').' />';
		$box->body .= '<label for="all_courses">'.$boxlang->def('_ALL_COURSES').'</label>';
		$box->body .= '<input id="sel_courses" name="all_courses" type="radio" value="0" '.($ref['all_courses'] ? '' : 'checked="checked"').' />';
		$box->body .= '<label for="sel_courses">'.$boxlang->def('_SEL_COURSES').'</label>';
		$box->body .= '</div>';
		$box->body .= '<div id="selector_container"'.($ref['all_courses'] ? ' style="display:none"' : '').'>';
		$box->body .= $selector->loadCourseSelector(true).'</div>';

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

		addYahooJs(array(
			'yahoo'           => 'yahoo-min.js',
			'yahoo-dom-event' => 'yahoo-dom-event.js',
			'element'         => 'element-beta-min.js',
			'datasource'      => 'datasource-beta-min.js',
			'connection'      => 'connection-min.js',
			'event'           => 'event-min.js',
			'json'            => 'json-beta-min.js'
			), array(
			'/assets/skins/sam' => 'skin.css'
			));
		addJs($GLOBALS['where_lms_relative'].'/admin/modules/report/','courses_filter.js');

		cout('<script type="text/javascript"> '."\n".
		'var courses_count="'.($temp!='' ? $temp : '0').'";'."\n".
		'var courses_all="'.$boxlang->def('_ALLCOURSES_FOOTER').'";'."\n".
		'YAHOO.util.Event.addListener(window, "load", function(e){ courses_selector_init(); });'."\n".
		'</script>', 'page_head');

		cout(
			Form::openForm('first_step_user_filter', $jump_url, false, 'post').
			$box->get().
			Form::getHidden('update_tempdata', 'update_tempdata', 1)/*.
			Form::openButtonSpace().
			//Form::getBreakRow().
			Form::getButton('ok_filter', 'import_filter', $lang->def('_NEXT')).
			Form::getButton('undo_filter', 'undo_filter', $lang->def('_UNDO')).
			Form::closeButtonSpace().
			Form::closeForm()*/ );
	}
	


	function show_report_courses($data = NULL, $other = '') {
		cout( $this->_get_courses_query() );
	}
	
	function _get_courses_query($type = 'html', $report_data = NULL, $other = '') {
		require_once($GLOBALS['where_lms'].'/lib/lib.course.php');
		require_once(dirname(__FILE__).'/report_tableprinter.php');
			
		if ($report_data==NULL) $ref =& $_SESSION['report_tempdata']; else $ref =& $report_data;

		$fw  = $GLOBALS['prefix_fw'];
		$lms = $GLOBALS['prefix_lms'];
		
		$sel_all = $ref['rows_filter']['select_all'];
		$sel_type = $ref['rows_filter']['selection_type'];
		$selection = $ref['rows_filter']['selection'];
		
		$all_courses = $ref['columns_filter']['all_courses'];
		$courses = $ref['columns_filter']['selected_courses']; 
		
		$acl = new DoceboACLManager();
		$html = '';
		
		$man = new Man_Course();
		$courses_codes = $man->getAllCourses();
		if ($all_courses) {
			$courses = array();
			foreach ($courses_codes as $key=>$val) $courses[] = $key; 
		}
		
		switch ($sel_type) {
		
			
			
			case 'groups': {
				if ($sel_all) {
				
				}
					
				//retrieve all labels
				$orgchart_labels = array();
				$query = "SELECT * FROM ".$fw."_org_chart WHERE lang_code='".getLanguage()."'";
				$res = mysql_query($query);
				while ($row = mysql_fetch_assoc($res)) {
					$orgchart_labels[$row['id_dir']] = $row['translation'];
				}
				
				$labels = array();
				$query = "SELECT * FROM ".$fw."_group WHERE (hidden='false' OR groupid LIKE '/oc_%' OR groupid LIKE '/ocd_%') AND type='free'";
				$res = mysql_query($query);
				while ($row = mysql_fetch_assoc($res)) {
					if ($row['hidden']=='false') {
						$labels[$row['idst']] = $acl->relativeId($row['groupid']);
					} else {
						$temp = explode("_", $row['groupid']); //echo '<div>'.print_r($temp,true).'</div>';
						if ($temp[0]=='/oc') {
							$labels[$row['idst']] = ($temp[1]!=0 ? $orgchart_labels[$temp[1]] : '');
						} elseif ($temp[0]=='/ocd') {
							$labels[$row['idst']] = ($temp[1]!=0 ? $orgchart_labels[$temp[1]] : '');
						}
					}
				}
				
				
				$tot_size = 2;
				$head1 = array('', '');
				$head2 = array($this->lang->def('_NAME'), $this->lang->def('_TOTAL'));
				
				foreach ($courses as $course) { 
					$head1[] = array(
						'value' => $courses_codes[$course]['code'],
						'colspan' => 6
					);
							
					$head2[] = $this->lang->def('_COMPLETED');
					$head2[] = _PERCENT_SIMBOL;
					$head2[] = $this->lang->def('_INITINERE');
					$head2[] = _PERCENT_SIMBOL;
					$head2[] = $this->lang->def('_NOT_STARTED');
					$head2[] = _PERCENT_SIMBOL;
					
					$tot_size += 6;
				}

				$buffer = new ReportTablePrinter($type, true);
				$buffer->openTable('','');

				$buffer->openHeader();
				$buffer->addHeader($head1);
				$buffer->addHeader($head2);
				$buffer->closeHeader();
				
				$tot_users = 0;
				//for each group, retrieve label and user statistics
				foreach ($selection as $dir_id=>$group_id) {
					$group_users = $acl->getGroupAllUser($group_id);
					$users_num = count($group_users);
					
					$line = array();
					$line[] = $labels[$group_id];
					$line[] = $users_num;
					$tot_users += $users_num; 
					
					if (count($group_users)>0) {
						$query = "SELECT cu.idUser, cu.idCourse, cu.status ".
							" FROM ".$lms."_courseuser as cu, ".$lms."_course as c, ".$fw."_user as u ".
							" WHERE cu.idUser=u.idst AND cu.idCourse=c.idCourse ".
							" AND u.idst IN (".implode(",", $group_users).") ".
							(!$all_courses ? " AND c.idCourse IN (".implode(",", $courses)." )" : "");

						$res = mysql_query($query);
						$course_stats = array();
						$tot_completed = 0;
						while ($row = mysql_fetch_assoc($res) ) {
							if (!isset($course_stats[$row['idCourse']][$group_id])) {
								$course_stats[$row['idCourse']][$group_id] = array(
									'completed' => 0,
									'initinere' => 0,
									'notstarted' => 0,
									'total' => 0
								);
							}
							switch ((int)$row['status']) {
								case 2: { $course_stats[$row['idCourse']][$group_id]['completed']++; $tot_completed++; } break;
								case 1: $course_stats[$row['idCourse']][$group_id]['initinere']++; break;
								case 0: $course_stats[$row['idCourse']][$group_id]['notstarted']++; break;
							}
							$course_stats[$row['idCourse']][$group_id]['total']++;
						}

						foreach ($courses as $course) {
							if (isset($course_stats[$course][$group_id])) {
								if ($course_stats[$course][$group_id]['total']==0) $dividend = 1; else $dividend = $course_stats[$course][$group_id]['total'];
								$line[] = $course_stats[$course][$group_id]['completed'];
								$line[] = number_format(100.0*$course_stats[$course][$group_id]['completed']/$dividend, 2, ',', '')._PERCENT_SIMBOL;
								$line[] = $course_stats[$course][$group_id]['initinere'];
								$line[] = number_format(100.0*$course_stats[$course][$group_id]['initinere']/$dividend, 2, ',', '')._PERCENT_SIMBOL;
								$line[] = $course_stats[$course][$group_id]['notstarted'];
								$line[] = number_format(100.0*$course_stats[$course][$group_id]['notstarted']/$dividend, 2, ',', '')._PERCENT_SIMBOL;
							} else {
								$line[] = '0';
								$line[] = '0,00%';
								$line[] = '0';
								$line[] = '0,00%';
								$line[] = '0';
								$line[] = '0,00%';
							}
						}

						//$line[] = $tot_completed;

					} else {
						foreach ($courses as $course) {
							$line[] = '0';
							$line[] = '0,00%';
							$line[] = '0';
							$line[] = '0,00%';
							$line[] = '0';
							$line[] = '0,00%';
						}
					}
					$buffer->addLine($line);
				
				
				}
				
				$buffer->closeBody();
				
				//calc totals
				$foot = array('', $tot_users);
				foreach ($courses as $course) {
					
					$completed_total = 0;
					$initinere_total = 0;
					$notstarted_total = 0;
					$total_total = 0;
					foreach ($selection as $dir_id=>$group_id) {
						$completed_total += (isset($course_stats[$course][$group_id]['completed']) ? $course_stats[$course][$group_id]['completed'] : 0);
						$initinere_total += (isset($course_stats[$course][$group_id]['initinere']) ? $course_stats[$course][$group_id]['initinere'] : 0);
						$notstarted_total += (isset($course_stats[$course][$group_id]['notstarted']) ? $course_stats[$course][$group_id]['notstarted'] : 0);
						$total_total += (isset($course_stats[$course][$group_id]['total']) ? $course_stats[$course][$group_id]['total'] : 0);
					}
					$foot[] = $completed_total;
					$foot[] = ($total_total!=0 ? number_format(100.0*$completed_total/$total_total, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL);
					$foot[] = $initinere_total;
					$foot[] = ($total_total!=0 ? number_format(100.0*$initinere_total/$total_total, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL);
					$foot[] = $notstarted_total;
					$foot[] = ($total_total!=0 ? number_format(100.0*$notstarted_total/$total_total, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL);
				}
				$buffer->setFoot($foot);
				$buffer->closeTable();
				$html .= $buffer->get();
			} break;
			
			
			
			case 'users': {
				
				$temp = array();
				$users = $acl->getGroupAllUser($selection);
				$query = "SELECT cu.idUser, cu.idCourse, cu.status, u.userId, c.code ".
					" FROM ( ".$lms."_courseuser as cu ".
					" JOIN  ".$lms."_course as c ON ( cu.idCourse = c.idCourse) ) ".
					" JOIN ".$fw."_user as u ON (cu.idUser = u.idst)  ".
					" WHERE 1 ".
					" AND cu.idCourse IN (".implode(",", $courses).") ".
					($sel_all ? "" : " AND idUser IN (".implode(",", $users).")")."";

				$res = mysql_query($query);
				while ($row = mysql_fetch_array($res) ) {
					$arr_courses = array();
					foreach($courses as $course) { 
						$arr_courses[$course] = $row['status'];
					}
					$temp[$row['idUser']] = array (
						'username' => $acl->relativeId($row['userId']),
						'courses' => $arr_courses
					);
				}

				//draw table
				$tot_size = 1;
				$head1 = array($this->lang->def('_USER'));
				$head2 = array('');
				foreach ($courses as $course) { 
					$head1[] = array(
						'value' => $courses_codes[$course]['code'],
						'colspan' => 6
					);
							
					$head2[] = $this->lang->def('_COMPLETED');
					$head2[] = _PERCENT_SIMBOL;
					$head2[] = $this->lang->def('_INITINERE');
					$head2[] = _PERCENT_SIMBOL;
					$head2[] = $this->lang->def('_NOT_STARTED');
					$head2[] = _PERCENT_SIMBOL;
					
					$tot_size += 6;
				}

				$buffer = new ReportTablePrinter($type, true);
				$buffer->openTable('','');

				$buffer->openHeader();
				$buffer->addHeader($head1);
				$buffer->addHeader($head2);
				$buffer->closeHeader();
				
				$completed_total = array();
				$initinere_total = array();
				$notstarted_total = array();
				$courses_total = array();
				
				foreach($courses as $course) { 
					$completed_total[$course] = 0;
					$initinere_total[$course] = 0;
					$notstarted_total[$course] = 0;
					$courses_total[$course] = 0;
				}
				
				$buffer->openBody();
				foreach ($temp as $id_user => $table_row) {
					$line = array();
					$line[] = $table_row['username'];
					foreach ($courses as $course) {
						$line[] = ($table_row['courses'][$course] == 2 ? 1 : 0);
						$line[] = ($table_row['courses'][$course] == 2 ? '100'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL : '0'._PERCENT_SIMBOL);
						$line[] = ($table_row['courses'][$course] == 1 ? 1 : 0);
						$line[] = ($table_row['courses'][$course] == 1 ? '100'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL : '0'._PERCENT_SIMBOL);
						$line[] = ($table_row['courses'][$course] == 0 ? 1 : 0);
						$line[] = ($table_row['courses'][$course] == 0 ? '100'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL : '0'._PERCENT_SIMBOL);
						
						switch ((int)$table_row['courses'][$course]) {
							case 2: if (isset($completed_total[$course])) $completed_total[$course] += 1; else $completed_course[$course] = 1; break;
							case 1: if (isset($initinere_total[$course])) $initinere_total[$course] += 1; else $initinere_course[$course] = 1; break;
							case 0: if (isset($notstarted_total[$course])) $notstarted_total[$course] += 1; else $notstarted_course[$course] = 1; break;
						}
						
						if (isset($courses_total[$course])) $courses_total[$course] += 1; else $courses_total[$course] = 1;
					}
					$buffer->addLine($line);
				}
				$buffer->closeBody();

				$totals_line = array('');
				foreach ($courses as $course) {
				
					$completed_num = isset($completed_total[$course]) ? $completed_total[$course] : '0';
					$initinere_num = isset($initinere_total[$course]) ? $initinere_total[$course] : '0';
					$notstarted_num = isset($notstarted_total[$course]) ? $notstarted_total[$course] : '0';
					$total_num = isset($courses_total[$course]) ? $courses_total[$course] : '0';
				
					$totals_line[] = $completed_num;
					$totals_line[] = $total_num!=0 ? number_format(100.0*$completed_num/$total_num, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL;
					$totals_line[] = $initinere_num;
					$totals_line[] = $total_num!=0 ? number_format(100.0*$initinere_num/$total_num, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL;
					$totals_line[] = $notstarted_num;
					$totals_line[] = $total_num!=0 ? number_format(100.0*$notstarted_num/$total_num, 2, _DECIMAL_SEPARATOR, '')._PERCENT_SIMBOL : '0'._DECIMAL_SEPARATOR.'00'._PERCENT_SIMBOL;
				}
				$buffer->setFoot($totals_line);
				
				$buffer->closeTable();
				
				$html .= $buffer->get();
			} break;
		
		}
		
		
		return $html;
	
	}

}