<?php

/************************************************************************/
/* DOCEBO LMS - Learning managment system									*/
/* ============================================							*/
/*																			*/
/* Copyright (c) 2005														*/
/* http://www.docebo.com													*/
/*																			*/
/* This program is free software. You can redistribute it and/or modify	*/
/* it under the terms of the GNU General Public License as published by	*/
/* the Free Software Foundation; either version 2 of the License.		*/
/************************************************************************/

if(!defined('IN_DOCEBO')) die('You cannot access this file directly');

/**
 * @version  $Id: class.report_general.php 1002 2007-03-24 11:55:51Z fabio $
 * @author	 Fabio Pirovano <fabio [at] docebo [dot] com>
 */

require_once($GLOBALS['where_lms'].'/admin/modules/report/class.report.php');

class Report_General extends Report {
	
	function Report_General($id_report) {
		
		parent::Report($id_report);
	}
	
	function play($jump_url, $back_url) {
		
		
		$time_range 			= importVar('time_belt', true, 0);
		$start_time 			= importVar('start_time', false, '');
		$end_time 				= importVar('end_time', false, '');
		$org_chart_subdivision 	= importVar('org_chart_subdivision', true, 0);
		
		if(isset($_POST['report_alluser'])) {
			if($_POST['report_alluser'] == '0') $alluser = 0;
			else $alluser = 1;
		} else $alluser = 0;
	
		if($time_range != 0) {
			$start_time = date("Y-m-d H:i:s", time() - $time_range*24*3600);
			$end_time = date("Y-m-d H:i:s");
		} elseif(isset($_POST['report_alluser']) || isset($_POST['report_seluser'])) {
	
			$start_time = $GLOBALS['regset']->regionalToDatabase($start_time, 'date');
			$end_time 	= $GLOBALS['regset']->regionalToDatabase($end_time, 'date');
		}
	
		if($start_time == ' 00:00:00') $start_time = '';
		if($end_time == ' 00:00:00') $end_time = '';
		
		
		
		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');
		
		$lang =& DoceboLanguage::createInstance('report');
		
		$page_title = getTitleArea(array(
			str_replace('&', '&amp;', $back_url) => $lang->def('_REPORT'),
			$this->report_name
		), 'report', $lang->def('_ALT_REPORT'));
		
		if($alluser == 1) {
			
			$GLOBALS['page']->add(
				$page_title
				.'<div class="std_block">'
				.getBackUi(str_replace('&', '&amp;', $back_url), $lang->def('_BACK')), 'content');
			$this->show_report($jump_url, true, $org_chart_subdivision, $start_time, $end_time);
			$GLOBALS['page']->add('</div>', 'content');
			return;
		}
		if(isset($_POST['cancelselector'])) {
			
			jumpTo($back_url);
		} elseif(isset($_POST['okselector'])) {
			
			$GLOBALS['page']->add(
				$page_title
				.'<div class="std_block">'
				.getBackUi(str_replace('&', '&amp;', $back_url), $lang->def('_BACK')), 'content');
			$this->show_report($jump_url, false, $org_chart_subdivision, $start_time, $end_time);
			$GLOBALS['page']->add('</div>', 'content');
			return;
		} elseif(isset($_POST['export_cvs'])) {
			
			$this->exportAsCvs($org_chart_subdivision, $start_time, $end_time);
		} else {
			
			// first step load selector 
			require_once($GLOBALS['where_framework'].'/class.module/class.directory.php');
			
			$aclManager = new DoceboACLManager();
			$user_select = new Module_Directory();
			
			if($org_chart_subdivision == 0) {
				
				$user_select->show_user_selector = TRUE;
				$user_select->show_group_selector = TRUE;
			} else {
				
				$user_select->show_user_selector = FALSE;
				$user_select->show_group_selector = FALSE;
			}
			$user_select->show_orgchart_selector = TRUE;
			$user_select->show_orgchart_simple_selector = FALSE;
			$user_select->multi_choice = TRUE;
			
			$user_select->addFormInfo(
				Form::getHidden('start_time', 'start_time', $start_time)
				.Form::getHidden('end_time', 'end_time', $end_time)
				.Form::getHidden('org_chart_subdivision', 'org_chart_subdivision', $org_chart_subdivision)
				.Form::getHidden('alluser', 'alluser', $alluser)
			);
			$user_select->setPageTitle($page_title);
			$user_select->loadSelector(str_replace('&', '&amp;', $jump_url), 
					false, 
					$lang->def('_CHOOSE_USER_FOR_REPORT'), 
					true, 
					true );
		}
		
	}
	
	function show_report($jump_url, $alluser, $org_chart_subdivision, $start_time, $end_time) {
		
		$lang =& DoceboLanguage::createInstance('rg_report');
		
		$acl_man 		= new DoceboACLManager();
		$course_man 	= new Man_Course();
		
		if($alluser == 0) {
			$user_select 	= new Module_Directory();
			$elem_selected 	= $user_select->getSelection($_POST);
			$user_selected 	=& $acl_man->getAllUsersFromIdst($elem_selected);
			
		} else {
			
			$user_selected =& $this->getAllUserIdst();
		}
		if($org_chart_subdivision == 1) {
			
			require_once($GLOBALS['where_framework'].'/lib/lib.orgchart.php');
			$org_man 	= new OrgChartManager();
			if($alluser == 1) {
				$elem_selected = $org_man->getAllGroupIdFolder();
			}
			$org_name = $org_man->getFolderFormIdst($elem_selected);
			
			$userlevelid = $GLOBALS['current_user']->getUserLevelId();
			if( $userlevelid != ADMIN_GROUP_GODADMIN ) {
				require_once($GLOBALS['where_framework'].'/lib/lib.adminmanager.php');
				$adminManager = new AdminManager();
				$user_filter = $adminManager->getAdminTree($GLOBALS['current_user']->getIdSt());
				$user_filter = array_flip($user_filter);
				
				$org_name_temp = $org_name;
				$org_name  = array();
				foreach($org_name_temp as $id => $value) {
					
					if(isset($user_filter[$id])) $org_name[$id] = $value;
				}
			}
			
		} else {
			$elem_selected = array();
		}
		
		if(empty($user_selected)) {
			
			$GLOBALS['page']->add($lang->def('_NULL_SELECTION'), 'content');
			return;
		}
		
		// Retrive all the course
		$id_courses = $course_man->getAllCourses();
		if(empty($id_courses)) {
			
			$GLOBALS['page']->add($lang->def('_NULL_COURSE_SELECTION'), 'content');
			return;
		}
		
		// css -------------------------------------------------------
		$GLOBALS['page']->add(
			"\n".'<link href="'.getPathTemplate('lms').'style/report/style_report_general.css" rel="stylesheet" type="text/css" />'."\n"
		, 'page_head');
		
		// Export as cvs -------------------------------------------
		$GLOBALS['page']->add(
			Form::openForm('export', $jump_url)
			.Form::openElementSpace('')
			.Form::getHidden('org_chart_subdivision', 'org_chart_subdivision', ( $org_chart_subdivision ?  1 : 0 ))
			.Form::getHidden('start_time', 'start_time', $start_time)
			.Form::getHidden('end_time', 'end_time', $end_time)
			.Form::getHidden('elem_selected', 'elem_selected', urlencode(serialize($elem_selected)))
			.Form::getHidden('user_selected', 'user_selected', urlencode(serialize($user_selected)))
			.form::getButton('export_cvs', 'export_cvs', $lang->def('_CSVEXPORT'), 'export_cvs')
			.Form::closeElementSpace()
			.Form::closeForm()
		, 'content');
		
		if($org_chart_subdivision === 0) {
			
			$date_now = $GLOBALS['regset']->databaseToRegional(date("Y-m-d H:i:s")); 
			
			$GLOBALS['page']->add('<div class="datasummary">', 'content');
			if(($start_time != '' && $start_time != '0000-00-00') || ($end_time != '' && $end_time != '0000-00-00')) {
				
				$GLOBALS['page']->add('<b>'.$lang->def('_TIME_BELT_2').' :</b> '
					.( $start_time != '' && $start_time != '0000-00-00' 
						? ' <b>'.$lang->def('_START_TIME').' </b>'.$GLOBALS['regset']->databaseToRegional($start_time, 'date') 
						: '' )
					.( $end_time != '' && $end_time != '0000-00-00' 
						? ' <b>'.$lang->def('_END_TIME').' </b>'.$GLOBALS['regset']->databaseToRegional($end_time, 'date') 
						: '' )
					.'<br />'
					, 'content');
			}
			$GLOBALS['page']->add( '<b>'.$lang->def('_ANALYSIS_DATE').' :</b> '.$date_now.'<br />'
				.'</div>', 'content');
				
			$query_course_user = "
			SELECT cu.idUser, cu.idCourse, cu.date_first_access, cu.date_complete
			FROM ".$GLOBALS['prefix_lms']."_courseuser AS cu 
			WHERE cu.idUser IN ( ".implode(',', $user_selected)." ) ";
			if($start_time != '' && $start_time != '0000-00-00') $query_course_user .= " AND cu.date_inscr >= '".$start_time."' ";
			if($end_time != '' && $end_time != '0000-00-00') $query_course_user .= " AND cu.date_inscr <= '".$end_time."'  AND cu.level='3'";
			
			$num_iscr 		= array();
			$num_nobegin 	= array();
			$num_itinere 	= array();
			$num_end 		= array();
			$time_in_course = array();
			$effective_user = array();
			
			$re_course_user = mysql_query($query_course_user);
			while(list($id_u, $id_c, $fisrt_access, $date_complete) = mysql_fetch_row($re_course_user)) {
				
				if(isset($num_iscr[$id_c])) ++$num_iscr[$id_c];
				else $num_iscr[$id_c] = 1;
				
				if($fisrt_access === NULL) {
					//never enter
					if(isset($num_nobegin[$id_c])) ++$num_nobegin[$id_c];
					else $num_nobegin[$id_c] = 1;
				} elseif($date_complete === NULL) {
					//enter
					if(isset($num_itinere[$id_c])) ++$num_itinere[$id_c];
					else $num_itinere[$id_c] = 1;
				} else {
					//complete
					if(isset($num_end[$id_c])) ++$num_end[$id_c];
					else $num_end[$id_c] = 1;
				}
				$effective_user[] = $id_u;
			}
			if(!empty($effective_user)) {
			
					$query_time = "
					SELECT idCourse, SUM(UNIX_TIMESTAMP(lastTime) - UNIX_TIMESTAMP(enterTime)) 
					FROM ".$GLOBALS['prefix_lms']."_tracksession 
					WHERE  idUser IN ( ".implode(',', $effective_user)." )  ";
					if($start_time != '' && $start_time != '0000-00-00') $query_time .= " AND enterTime >= '".$start_time."' ";
					if($end_time != '' && $end_time != '0000-00-00') $query_time .= " AND enterTime <= '".$end_time."' ";
					$query_time .= " GROUP BY idCourse ";
					
					$re_time = mysql_query($query_time);
					
					while(list($id_c, $time_num) = mysql_fetch_row($re_time)) {
						
						$time_in_course[$id_c] = $time_num;
					}
			}
			
			$this->_printTable($acl_man, $id_courses, $num_iscr , $num_nobegin, $num_itinere, $num_end, $time_in_course);
		} else {
			
			$date_now = $GLOBALS['regset']->databaseToRegional(date("Y-m-d H:i:s")); 
			
			reset($org_name);
			while(list($idst_group, $folder_name) = each($org_name)) {
				
				$GLOBALS['page']->add('<div class="datasummary">'
					.'<b>'.$lang->def('_FOLDER_NAME').' :</b> '.$folder_name['name']
						.( $folder_name['type_of_folder'] == ORG_CHART_WITH_DESCENDANTS ? ' ('.$lang->def('_WITH_DESCENDANTS').')' : '' ).'<br />'
					, 'content');
				if(($start_time != '' && $start_time != '0000-00-00') || ($end_time != '' && $end_time != '0000-00-00')) {
					
					$GLOBALS['page']->add('<b>'.$lang->def('_TIME_BELT_2').' :</b> '
						.( $start_time != '' && $start_time != '0000-00-00' 
							? ' <b>'.$lang->def('_START_TIME').' </b>'.$GLOBALS['regset']->databaseToRegional($start_time, 'date') 
							: '' )
						.( $end_time != '' && $end_time != '0000-00-00' 
							? ' <b>'.$lang->def('_END_TIME').' </b>'.$GLOBALS['regset']->databaseToRegional($end_time, 'date') 
							: '' )
						.'<br />'
						, 'content');
				}
				$GLOBALS['page']->add( '<b>'.$lang->def('_ANALYSIS_DATE').' :</b> '.$date_now.'<br />'
					.'</div>', 'content');
				
				$group_user = $acl_man->getGroupAllUser($idst_group);
					
				$query_course_user = "
				SELECT cu.idUser, cu.idCourse, cu.date_first_access, cu.date_complete
				FROM ".$GLOBALS['prefix_lms']."_courseuser AS cu 
				WHERE cu.idUser IN ( ".implode(',', $group_user)." )  ";
				if($start_time != '' && $start_time != '0000-00-00') $query_course_user .= " AND cu.date_inscr >= '".$start_time."' ";
				if($end_time != '' && $end_time != '0000-00-00') $query_course_user .= " AND cu.date_inscr <= '".$end_time."'  AND cu.level='3'";
				
				$num_iscr 		= array();
				$num_nobegin 	= array();
				$num_itinere 	= array();
				$num_end 		= array();
				$time_in_course = array();
				$effective_user = array();
				
				$re_course_user = mysql_query($query_course_user);
				while(list($id_u, $id_c, $fisrt_access, $date_complete) = mysql_fetch_row($re_course_user)) {
					
					if(isset($num_iscr[$id_c])) ++$num_iscr[$id_c];
					else $num_iscr[$id_c] = 1;
					
					if($fisrt_access === NULL) {
						//never enter
						if(isset($num_nobegin[$id_c])) ++$num_nobegin[$id_c];
						else $num_nobegin[$id_c] = 1;
					} elseif($date_complete === NULL) {
						//enter
						if(isset($num_itinere[$id_c])) ++$num_itinere[$id_c];
						else $num_itinere[$id_c] = 1;
					} else {
						//complete
						if(isset($num_end[$id_c])) ++$num_end[$id_c];
						else $num_end[$id_c] = 1;
					}
					$effective_user[] = $id_u;
				}
				if(!empty($group_user)) {
				
					$query_time = "
					SELECT idCourse, SUM(UNIX_TIMESTAMP(lastTime) - UNIX_TIMESTAMP(enterTime)) 
					FROM ".$GLOBALS['prefix_lms']."_tracksession 
					WHERE  idUser IN ( ".implode(',', $group_user)." )  ";
					if($start_time != '' && $start_time != '0000-00-00') $query_time .= " AND enterTime >= '".$start_time."' ";
					if($end_time != '' && $end_time != '0000-00-00') $query_time .= " AND enterTime <= '".$end_time."' ";
					$query_time .= " GROUP BY idCourse ";
					
					$re_time = mysql_query($query_time);
					while(list($id_c, $time_num) = mysql_fetch_row($re_time)) {
						
						$time_in_course[$id_c] = $time_num;
					}
				}
				reset($id_courses);
				$this->_printTable($acl_man, $id_courses, $num_iscr , $num_nobegin, $num_itinere, $num_end, $time_in_course);	
			}
		}
		
	}
	
	function _printTable(&$acl_man, &$id_courses, &$num_iscr , &$num_nobegin, &$num_itinere, &$num_end, &$time_in_course) {
		
		$lang =& DoceboLanguage::createInstance('report');
		$lang =& DoceboLanguage::createInstance('rg_report');
		
		$GLOBALS['page']->add( '<table class="report_table" summary="'.$lang->def('_RG_SUMMAMRY_MANAGMENT').'">'
			.'<caption>'.$lang->def('_RG_CAPTION').'</caption>'
			.'<thead>', 'content');
		
		$GLOBALS['page']->add(
			'<tr>'."\n\t"
				.'<th rowspan="2" colspan="2">'.$lang->def('_COURSE').'</th>'."\n\t"
				.'<th colspan="7">'.$lang->def('_USER_INTEST').'</th>'."\n"
				.'<th rowspan="3" scope="col">'.$lang->def('_TOTAL_SESSION').'</th>'."\n"
			.'</tr><tr>'."\n\t"
				.'<th>'.$lang->def('_INSCR').'</th>'."\n\t"
				.'<th colspan="2">'.$lang->def('_MUSTBEGIN').'</th>'."\n\t"
				.'<th colspan="2">'.$lang->def('_INITINERE').'</th>'."\n"
				.'<th colspan="2">'.$lang->def('_COMPLETECOURSE').'</th>'."\n\t"
			.'</tr><tr>'."\n\t"
				.'<th scope="col">'.$lang->def('_CODE_COURSE').'</th>'."\n\t"
				.'<th scope="col">'.$lang->def('_NAME_COURSE').'</th>'."\n\t"
				.'<th>'.$lang->def('_NUM').'</th>'."\n\t"
				.'<th>'.$lang->def('_NUM').'</th>'."\n\t"
				.'<th>'.$lang->def('_PERC').'</th>'."\n\t"
				.'<th>'.$lang->def('_NUM').'</th>'."\n\t"
				.'<th>'.$lang->def('_PERC').'</th>'."\n\t"
				.'<th>'.$lang->def('_NUM').'</th>'."\n\t"
				.'<th>'.$lang->def('_PERC').'</th>'."\n"
			.'</tr>'
			.'</thead>'
			.'<tbody>'."\n\n", 'content');
		
		$i = 0;
		$tot_iscr = $tot_itinere = $tot_nobegin  = $tot_comple = '';
		$tot_perc_itinere = $tot_perc_nobegin  = $tot_perc_comple = '';
		$total_time = 0;
		
		while(list($idc, $course_info) = each($id_courses) ) {
			
			
				
			$code_c = $course_info['code'];
			$name_c = $course_info['name'];
			
			$row = '<tr class="row'.( (++$i)%2 ? '' : '_col' ).'">'
				.'<td>'.$code_c.'</td>'."\n\t"
				.'<td>'.$name_c.'</td>'."\n\t";
			if( isset($num_iscr[$idc]) ) {	
				$row .= '<td>'.$num_iscr[$idc].'</td>'."\n\t";
				$tot_iscr += $num_iscr[$idc];
				
				//no begin course
				if(isset($num_nobegin[$idc])) {
					$perc = (($num_nobegin[$idc] / $num_iscr[$idc])*100);
					$row .= '<td>'.$num_nobegin[$idc].'</td>'."\n\t"
						.'<td>'.number_format($perc , 2, '.', '').'%</td>'."\n\t";
					$tot_nobegin += $num_nobegin[$idc];
					$tot_perc_nobegin += $perc;
				}
				else $row .= '<td>&nbsp;</td><td>&nbsp;</td>'."\n\t";
				
				//begin
				if(isset($num_itinere[$idc])) {
					$perc = (($num_itinere[$idc] / $num_iscr[$idc])*100);
					$row .= '<td>'.$num_itinere[$idc].'</td>'."\n\t"
						.'<td>'.number_format($perc , 2, '.', '').'%</td>'."\n\t";
					$tot_itinere += $num_itinere[$idc];
					$tot_perc_itinere += $perc;
				}
				else $row .= '<td>&nbsp;</td><td>&nbsp;</td>'."\n\t";
				
				//end course
				if(isset($num_end[$idc])) {
					$perc = (($num_end[$idc] / $num_iscr[$idc])*100);
					$row .= '<td>'.$num_end[$idc].'</td>'."\n\t"
						.'<td>'.number_format($perc , 2, '.', '').'%</td>'."\n\t";
					$tot_comple += $num_end[$idc];
					$tot_perc_comple += $perc;
				}
				else $row .= '<td>&nbsp;</td><td>&nbsp;</td>'."\n\t";
				
				// time in
				if(isset($time_in_course[$idc])) {
					
					$row .= '<td class="align_center">'.((int)($time_in_course[$idc]/3600)).'h '
						.substr('0'.((int)(($time_in_course[$idc]%3600)/60)),-2).'m '
						.substr('0'.((int)($time_in_course[$idc]%60)),-2).'s '.'</td>';
						
					$total_time += $time_in_course[$idc];
				} else $row .=  '<td>&nbsp;</td>';
				$row .= "</tr>"."\n\t";
			} else {
				$row .= '<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>'
				.'<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr>'."\n\n";
			}
			if( isset($num_iscr[$idc]) && $num_iscr[$idc]) $GLOBALS['page']->add($row, 'content');
			else $i--;
			
		}
		$GLOBALS['page']->add( 
			'</tbody>'
			.'<tfoot>'
			.'<tr class="tot">'
			.'<td colspan="2" class="text_bold">'.$lang->def('_TOTAL').'</td>'
			.'<td>'.$tot_iscr.'</td>'
			.'<td>'.$tot_nobegin.'</td><td>'
				.( $tot_nobegin ? 	number_format(( ($tot_nobegin/$tot_iscr)*100 ), 2 , '.', '').'%' : 'n.d.' ).'</td>'
			.'<td>'.$tot_itinere.'</td><td>'
				.( $tot_itinere ? 	number_format(($tot_itinere/$tot_iscr)*100, 2 , '.', '').'%' : 'n.d.' ).'</td>'
			.'<td>'.$tot_comple.'</td><td>'
				.( $tot_comple ? 	number_format(($tot_comple/$tot_iscr)*100, 2 , '.', '').'%' : 'n.d.').'</td>'
			.'<td class="align_center">'.((int)($total_time/3600)).'h '
				.substr('0'.((int)($total_time/60)),-2).'m '
				.substr('0'.((int)$total_time),-2).'s '.'</td>'
			.'</tr>'."\n\n".'</tfoot>'
			.'</table>', 'content');
	}
	
	function exportAsCvs($org_chart_subdivision, $start_time, $end_time) {
		
		ob_end_clean();
		//content type forcing dowlad
		header("Content-type: application/download\n");
		//cache control
		header("Cache-control: private");
		//sending creation time
		header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
		//content type
		header('Content-Disposition: attachment; filename="report_general '.gmdate('d-m-Y H i s').'.csv"');
		
		$lang =& DoceboLanguage::createInstance('rg_report');
		
		$acl_man 		= new DoceboACLManager();
		$course_man 	= new Man_Course();
		
		$elem_selected = unserialize(urldecode($_POST['elem_selected']));
		$user_selected = unserialize(urldecode($_POST['user_selected']));
		
		if($org_chart_subdivision == 1) {
			
			require_once($GLOBALS['where_framework'].'/lib/lib.orgchart.php');
			$org_man 	= new OrgChartManager();
			$org_name = $org_man->getFolderFormIdst($elem_selected);
		}
		
		if(empty($user_selected)) {
			exit();
		}
		
		// Retrive all the course
		$id_courses = $course_man->getAllCourses();
		if(empty($id_courses)) {
			exit();
		}
		
		if($org_chart_subdivision === 0) {
			
			$date_now = $GLOBALS['regset']->databaseToRegional(date("Y-m-d H:i:s")); 
			
			echo "\r\n";
			if(($start_time != '' && $start_time != '0000-00-00') || ($end_time != '' && $end_time != '0000-00-00')) {
				
				echo ''.$lang->def('_TIME_BELT_2').' : '
					.( $start_time != '' && $start_time != '0000-00-00'  	
						? ' '.$lang->def('_START_TIME').' '.$GLOBALS['regset']->databaseToRegional($start_time, 'date') 
						: '' )
					.( $end_time != '' && $end_time != '0000-00-00'
						? ' '.$lang->def('_END_TIME').' '.$GLOBALS['regset']->databaseToRegional($end_time, 'date') 
						: '' )
					."\r\n";
			}
			echo ''.$lang->def('_ANALYSIS_DATE').' : '.$date_now."\r\n";
			
			
			$query_time = "
			SELECT idCourse, SUM(UNIX_TIMESTAMP(lastTime) - UNIX_TIMESTAMP(enterTime)) 
			FROM ".$GLOBALS['prefix_lms']."_tracksession 
			WHERE idUser IN ( ".implode(',', $user_selected)." )";
			if($start_time != '' && $start_time != '0000-00-00') $query_time .= " AND enterTime >= '".$start_time."' ";
			if($end_time != '' && $end_time != '0000-00-00') $query_time .= " AND enterTime <= '".$end_time."' ";
			$query_time .= "GROUP BY idCourse ";
			
			$query_course_user = "
			SELECT cu.idUser, cu.idCourse, cu.date_first_access, cu.date_complete
			FROM ".$GLOBALS['prefix_lms']."_courseuser AS cu 
			WHERE idUser IN ( ".implode(',', $user_selected)." ) ";
			if($start_time != '' && $start_time != '0000-00-00') $query_course_user .= " AND cu.date_inscr >= '".$start_time."' ";
			if($end_time != '' && $end_time != '0000-00-00') $query_course_user .= " AND cu.date_inscr <= '".$end_time."'  AND cu.level='3'";
			
			$num_iscr 		= array();
			$num_nobegin 	= array();
			$num_itinere 	= array();
			$num_end 		= array();
			$time_in_course = array();
			$effective_user = array();
			
			$re_course_user = mysql_query($query_course_user);
			while(list($id_u, $id_c, $fisrt_access, $date_complete) = mysql_fetch_row($re_course_user)) {
				
				if(isset($num_iscr[$id_c])) ++$num_iscr[$id_c];
				else $num_iscr[$id_c] = 1;
				
				if($fisrt_access === NULL) {
					//never enter
					if(isset($num_nobegin[$id_c])) ++$num_nobegin[$id_c];
					else $num_nobegin[$id_c] = 1;
				} elseif($date_complete === NULL) {
					//enter
					if(isset($num_itinere[$id_c])) ++$num_itinere[$id_c];
					else $num_itinere[$id_c] = 1;
				} else {
					//complete
					if(isset($num_end[$id_c])) ++$num_end[$id_c];
					else $num_end[$id_c] = 1;
				}
				$effective_user[] = $id_u;
			}
			if(!empty($effective_user)) {
			
				$query_time = "
				SELECT idCourse, SUM(UNIX_TIMESTAMP(lastTime) - UNIX_TIMESTAMP(enterTime)) 
				FROM ".$GLOBALS['prefix_lms']."_tracksession 
				WHERE idUser IN ( ".implode(',', $effective_user)." )";
					if($start_time != '' && $start_time != '0000-00-00') $query_time .= " AND enterTime >= '".$start_time."' ";
					if($end_time != '' && $end_time != '0000-00-00') $query_time .= " AND enterTime <= '".$end_time."' ";
					$query_time .= "GROUP BY idCourse ";
				
				$re_time = mysql_query($query_time);
				while(list($id_c, $time_num) = mysql_fetch_row($re_time)) {
					
					$time_in_course[$id_c] = $time_num;
				}
			}
			
			$re_time = mysql_query($query_time);
			while(list($id_c, $time_num) = mysql_fetch_row($re_time)) {
				
				$time_in_course[$id_c] = $time_num;
			}
			
			$this->_printCsvTable($acl_man, $id_courses, $num_iscr , $num_nobegin, $num_itinere, $num_end, $time_in_course);
			die();
		} else {
			
			$date_now = $GLOBALS['regset']->databaseToRegional(date("Y-m-d H:i:s")); 
			
			reset($org_name);
			while(list($idst_group, $folder_name) = each($org_name)) {
				
				echo "\r\n"
					.''.$lang->def('_FOLDER_NAME').' : '.$folder_name['name']
						.( $folder_name['type_of_folder'] == ORG_CHART_WITH_DESCENDANTS ? ' ('.$lang->def('_WITH_DESCENDANTS').')' : '' )."\r\n";
				if(($start_time != '' && $start_time != '0000-00-00') || ($end_time != '' && $end_time != '0000-00-00')) {
					
					echo ''.$lang->def('_TIME_BELT_2').' : '
						.( $start_time != '' && $start_time != '0000-00-00'  	
							? ' '.$lang->def('_START_TIME').' '.$GLOBALS['regset']->databaseToRegional($start_time, 'date') 
							: '' )
						.( $end_time != '' && $end_time != '0000-00-00'
							? ' '.$lang->def('_END_TIME').' '.$GLOBALS['regset']->databaseToRegional($end_time, 'date') 
							: '' )
						."\r\n";
				}
				echo ''.$lang->def('_ANALYSIS_DATE').' : '.$date_now."\r\n";
				
				$group_user = $acl_man->getGroupAllUser($idst_group);
					
				$query_course_user = "
				SELECT cu.idUser, cu.idCourse, cu.date_first_access, cu.date_complete
				FROM ".$GLOBALS['prefix_lms']."_courseuser AS cu 
				WHERE idUser IN ( ".implode(',', $group_user)." ) ";
				if($start_time != '' && $start_time != '0000-00-00') $query_course_user .= " AND cu.date_inscr >= '".$start_time."' ";
				if($end_time != '' && $end_time != '0000-00-00') $query_course_user .= " AND cu.date_inscr <= '".$end_time."'  AND cu.level='3'";
				
				$num_iscr 		= array();
				$num_nobegin 	= array();
				$num_itinere 	= array();
				$num_end 		= array();
				$time_in_course = array();
				$effective_user = array();
				
				$re_course_user = mysql_query($query_course_user);
				while(list($id_u, $id_c, $fisrt_access, $date_complete) = mysql_fetch_row($re_course_user)) {
					
					if(isset($num_iscr[$id_c])) ++$num_iscr[$id_c];
					else $num_iscr[$id_c] = 1;
					
					if($fisrt_access === NULL) {
						//never enter
						if(isset($num_nobegin[$id_c])) ++$num_nobegin[$id_c];
						else $num_nobegin[$id_c] = 1;
					} elseif($date_complete === NULL) {
						//enter
						if(isset($num_itinere[$id_c])) ++$num_itinere[$id_c];
						else $num_itinere[$id_c] = 1;
					} else {
						//complete
						if(isset($num_end[$id_c])) ++$num_end[$id_c];
						else $num_end[$id_c] = 1;
					}
					$effective_user[] = $id_u;
				}
				if(!empty($effective_user)) {
				
					$query_time = "
					SELECT idCourse, SUM(UNIX_TIMESTAMP(lastTime) - UNIX_TIMESTAMP(enterTime)) 
					FROM ".$GLOBALS['prefix_lms']."_tracksession 
					WHERE idUser IN ( ".implode(',', $effective_user)." )";
					if($start_time != '' && $start_time != '0000-00-00') $query_time .= " AND enterTime >= '".$start_time."' ";
					if($end_time != '' && $end_time != '0000-00-00') $query_time .= " AND enterTime <= '".$end_time."' ";
					$query_time .= "GROUP BY idCourse ";
					
					$re_time = mysql_query($query_time);
					while(list($id_c, $time_num) = mysql_fetch_row($re_time)) {
						
						$time_in_course[$id_c] = $time_num;
					}
				}
				reset($id_courses);
				$this->_printCsvTable($acl_man, $id_courses, $num_iscr , $num_nobegin, $num_itinere, $num_end, $time_in_course);	
			}
		}
		die();
	}
	
	function _printCsvTable(&$acl_man, &$id_courses, &$num_iscr , &$num_nobegin, &$num_itinere, &$num_end, &$time_in_course) {
		
		$lang =& DoceboLanguage::createInstance('report');
		$lang =& DoceboLanguage::createInstance('rg_report');
		
		
		echo $this->report_name."\n"
			.''.$lang->def('_COURSE').',,'.$lang->def('_USER_INTEST').',,,,,,,'.$lang->def('_TOTAL_SESSION')."\n"
			.',,'.$lang->def('_INSCR').','.	$lang->def('_MUSTBEGIN').',,'.	$lang->def('_INITINERE').',,'.	$lang->def('_COMPLETECOURSE')."\n"
			.$lang->def('_CODE_COURSE').','.$lang->def('_NAME_COURSE').','.$lang->def('_NUM').','.$lang->def('_NUM')
				.','.$lang->def('_PERC').','.$lang->def('_NUM').','.$lang->def('_PERC').','.	$lang->def('_NUM').','.$lang->def('_PERC')."\n";
		
		
		$tot_iscr = $tot_itinere = $tot_nobegin  = $tot_comple = '';
		$tot_perc_itinere = $tot_perc_nobegin  = $tot_perc_comple = '';
		$total_time = 0;
		
		$i = 0;
		while(list($idc, $course_info) = each($id_courses) ) {
			
			$code_c = $course_info['name'];
			$name_c = $course_info['code'];
			
			++$i;
			echo $code_c.','
				.$name_c.',';
			if( isset($num_iscr[$idc]) ) {	
				echo $num_iscr[$idc].',';
				$tot_iscr += $num_iscr[$idc];
				
				//no begin course
				if(isset($num_nobegin[$idc])) {
					$perc = (($num_nobegin[$idc] / $num_iscr[$idc])*100);
					echo $num_nobegin[$idc].','
						.number_format($perc , 2, '.', '').'%,';
					$tot_nobegin += $num_nobegin[$idc];
					$tot_perc_nobegin += $perc;
				}
				else echo ',,';
				
				//begin
				if(isset($num_itinere[$idc])) {
					$perc = (($num_itinere[$idc] / $num_iscr[$idc])*100);
					echo $num_itinere[$idc].','
						.number_format($perc , 2, '.', '').'%,';
					$tot_itinere += $num_itinere[$idc];
					$tot_perc_itinere += $perc;
				}
				else echo ',,';
				
				//end course
				if(isset($num_end[$idc])) {
					$perc = (($num_end[$idc] / $num_iscr[$idc])*100);
					echo $num_end[$idc].','
						.number_format($perc , 2, '.', '').'%,';
					$tot_comple += $num_end[$idc];
					$tot_perc_comple += $perc;
				}
				else echo ',,';
				if(isset($time_in_course[$idc])) {
					
					echo ((int)($time_in_course[$idc]/3600)).'h '
						.substr('0'.((int)(($time_in_course[$idc]%3600)/60)),-2).'m '
						.substr('0'.((int)($time_in_course[$idc]%60)),-2).'s '."\n";
					$total_time += $time_in_course[$idc];
				} else echo "\n";
			} else {
				echo ',,,,,,,,'."\n";
			}
		}
		
		echo ','.$lang->def('_TOTAL').','
			.$tot_iscr.','
			.$tot_itinere.','
				.( $tot_perc_itinere ? 	number_format(( $tot_perc_itinere / $i ), 2 , '.', '').'%' : 'n.d.' ).','
			.$tot_nobegin.','
				.( $tot_perc_nobegin ? 	number_format(( $tot_perc_nobegin / $i ), 2 , '.', '').'%' : 'n.d.' ).','
			.$tot_comple.','
				.( $tot_perc_itinere ? 	number_format(( $tot_perc_comple / $i ), 2 , '.', '').'%' : 'n.d.').','
			.((int)($total_time/3600)).'h '.substr('0'.((int)($total_time/60)),-2).'m '.substr('0'.((int)$total_time),-2).'s '."\n";
	}
	
}

?>