Location: PHPKode > projects > Opensheet > opensheet/classes/stats.class.php
<?php 
#ini_set('display_errors',1);
#error_reporting(E_ALL);
#include ('global.class.php');

class Stats extends Base {

	   		
		   		
	
function getUserStat($year, $month) {

	db_connect();
	$query="select users.user_index as user_index, tender_hours, bill_proj_hours, project_hours, all_hours, office_hours, workhours,  all_hours / workhours AS FTEE, project_hours / bill_proj_hours AS BR from (select distinct user_index from timesteps where year(date)=".$this->year." and month(date)=".$this->month.") as users left join (SELECT DISTINCT sum( hours ) as tender_hours , user_index FROM timesteps WHERE year(date)=".$this->year."  AND month(date)=".$this->month." AND proj_index = ANY(SELECT proj_index FROM projects WHERE proj_type = 'tender') GROUP BY user_index) as tender ON users.user_index=tender.user_index left join (SELECT DISTINCT user_index, sum( hours ) AS bill_proj_hours FROM timesteps WHERE year(date)=".$this->year."  AND month(date)=".$this->month." AND (proj_index != 'unpaid_abs' AND proj_index != 'vacation') GROUP BY user_index) AS billable ON users.user_index=billable.user_index left join (SELECT DISTINCT sum( hours ) as project_hours , user_index FROM timesteps WHERE year(date)=".$this->year."  AND month(date)=".$this->month." AND proj_index = ANY(SELECT proj_index FROM projects WHERE proj_type = 'project') GROUP BY user_index) as projects ON users.user_index=projects.user_index left join (SELECT DISTINCT user_index, sum( hours ) AS all_hours FROM timesteps WHERE year(date)=".$this->year." AND month(date)=".$this->month." GROUP BY user_index ) AS all_hours ON users.user_index = all_hours.user_index LEFT JOIN (SELECT sum( hours ) AS office_hours, user_index FROM timesteps WHERE proj_index = ANY(SELECT proj_index FROM projects WHERE proj_type ='office') AND year(date)=".$this->year."  AND month(date)=".$this->month." GROUP BY user_index) AS office ON users.user_index= office.user_index join (SELECT count( date ) *8 AS workhours FROM holydays WHERE year(date)=".$this->year." AND month(date)=".$this->month." AND holyday = 'N') AS workhours"; 
	$result = mysql_query($query) or die("Invalid query: " . mysql_error());
		while($row = mysql_fetch_assoc($result)) {
			$all_users_stat[$row['user_index']]['tender_hours']=$row['tender_hours'];
			$all_users_stat[$row['user_index']]['bill_proj_hours']=$row['bill_proj_hours'];
			$all_users_stat[$row['user_index']]['project_hours']=$row['project_hors'];
			$all_users_stat[$row['user_index']]['all_hours']=$row['all_hours'];
			$all_users_stat[$row['user_index']]['office_hours']=$row['office_hours'];
			$all_users_stat[$row['user_index']]['workhours']=$row['workhours'];
			$all_users_stat[$row['user_index']]['FTEE']=$row['FTEE'];
			$all_users_stat[$row['user_index']]['BR']=$row['BR'];

			}
		return $query;
		}
		   		
			function getOvertime($user_id,$year,$month){

			$query="select w_office.date, w_office.hours,wo_office.hours as all_hours,  holydays.holyday  from (select sum(hours)as hours, date from timesteps where project_id = ANY( SELECT id FROM projects WHERE proj_type != 'off_hours') AND user_id = '".$user_id."' AND month(date ) = ".$month." AND year(date)=".$year." group by date) as w_office left join (select sum(hours)as hours, date from timesteps where  user_id = '".$user_id."' AND month(date ) = ".$month." AND year(date)=".$year." group by date) as wo_office ON w_office.date=wo_office.date join holydays on w_office.date=holydays.date";
			$res = mysql_query($query);#echo $query;
			$i='1';
			while($row = mysql_fetch_assoc($res)) { 
				$overtime[$i]['date']=$row['date']; 
				$overtime[$i]['work_hours']=$row['hours'];
						$overtime[$i]['all_hours']=$row['all_hours']; 
						$overtime[$i]['holyday']=$row['holyday'];
						if ($overtime[$i]['holyday'] == 'Y'){
							$overtime[$i]['overtime'] = $overtime[$i]['all_hours'];
						}else {
					if ($overtime[$i]['work_hours'] < '9' ){
							$overtime[$i]['overtime'] = '0';
					} else {
				$overtime[$i]['overtime']=$overtime[$i]['all_hours'] - '8';
					}
				}
				$overtime[50]['sum_all_work_hours']=$overtime[50]['sum_all_work_hours']+$overtime[$i]['work_hours'];
				$overtime[50]['sum_all_hours']=$overtime[50]['sum_all_hours']+$overtime[$i]['all_hours'];
				$overtime[50]['sum_over_hours']=$overtime[50]['sum_over_hours']+$overtime[$i]['overtime'];
				$i++;

				
					
				
			ksort ($overtime);
			}
			
			
			
			return $overtime;
		}	   		
				#ready
			function getWorkDays($year,$month){
				$query = "SELECT count( date ) FROM `holydays` WHERE holyday = 'n' AND MONTH( DATE ) = '$month' AND YEAR(DATE) = '$year'";
	  			return parent::dbQueryRow($query);
			}
			
			function getWorkHours($year,$month){
				$query = "SELECT count( date )*8 FROM `holydays` WHERE holyday = 'n' AND MONTH( DATE ) = '$month' AND YEAR(DATE) = '$year'";
	  			return parent::dbQueryRow($query);
			}
			
			#ready BUT holuday_chk is here
		function getHolydays($year,$month){
			$query = "SELECT holyday FROM  `holydays` WHERE YEAR( holydays.date ) =  '$year' AND MONTH( holydays.date ) =  '$month'";
				$i='1';
		foreach(parent::dbQuery($query) as $key=>$value) {
			$days[$i] = $value['holyday'];	$i++;
		 }
			if (!isset($days)){
			    $day_in_month = date( 't', mktime(01,01,01,$month,01,$year) );
				$day_in_month++;
			for ($i=1; $i<$day_in_month;$i++){
				$days[$i]['holyday']=holyday_chk($i,$month,$year);
				}
			}
  		 return $days;
		}		

		function getAllHours($user_id,$year,$month) {
			$query = "select sum(hours) from timesteps where `user_id`='$user_id'  and YEAR(DATE)='$year' and month(date)='$month'";
			return parent::dbQueryRow($query);
		}

		function getFTEEHours($user_id,$year,$month) {
			$query = "select all_hours/workhours  from (SELECT sum( hours ) AS all_hours FROM timesteps WHERE YEAR( DATE )='$year' and month(date)='$month' and user_id='$user_id') as all_hours join (select count(date)*8 as workhours from holydays where year(date)='$year' and month(date)='$month' and holyday='N')as workhours";
			return parent::dbQueryRow($query);
			#return $query;
		}
		
		function getBRUser($user_index,$year,$month) {
			$query="select  proj_hours/bill_proj_hours as BR from ((SELECT sum( hours ) AS bill_proj_hours FROM timesteps WHERE YEAR( DATE ) ='$year' AND Month(date)='$month' and ( project_id!='5' and  project_id!='4' )  and user_id='$user_index') as billable join (select sum(hours) as proj_hours from timesteps WHERE project_id = ANY(SELECT id FROM projects WHERE proj_type ='project') and year(date)='$year' and month(date)='$month' and user_id='$user_index') as proj_hours)";
			return parent::dbQueryRow($query);
			#return $query;
		}
		
		function getFTEE($year) {
			$query = "select sum(t.hours),month(t.date) from timesteps  as t, projects as p where t.project_id=p.id and p.proj_type='office' and year(t.date)=2009 GROUP BY month(t.date);";
			$query= "select count(day(date))*8,month(date) from holydays where holyday='N' and year(date)=2009 GROUP BY month(date)";
			
			
			return parent::dbQueryRow($query);
			#return $query;
		}
		
		function getWorkHoursYear($year){
				$query = "SELECT count(day(date))*8,month(date) from holydays where holyday='N' and year(date)=$year GROUP BY month(date);";
				foreach(parent::dbQuery($query) as $key=>$value){
	  								$result[$value['month(date)']]=$value['count(day(date))*8'];
					
			}
		return $result;
	}
		
	function getAllhoursYear($year){
				$query = "select sum(hours),month(date) from timesteps where year(date)=$year GROUP BY month(date);";
				foreach(parent::dbQuery($query) as $key=>$value){
	  								$result[$value['month(date)']]=$value['sum(hours)'];
					
			}
		return $result;
	}
	function getAllhoursYearByTypes($year){
				$query = "select sum(timesteps.hours),month(timesteps.date),projects.proj_type from timesteps,projects where timesteps.project_id=projects.id and year(timesteps.date)=$year  GROUP BY month(timesteps.date),projects.proj_type;";
				foreach(parent::dbQuery($query) as $key=>$value){
	  		$result[$value['month(timesteps.date)']][$value['proj_type']]=$value['sum(timesteps.hours)'];
					
			}
		return $result;
	}
	
	function getUsersFromBase($year){
				$query = "SELECT count( usercount.users ) AS users, month , year FROM ( SELECT DISTINCT user_id AS users, month( date ) AS MONTH , year( date ) AS year FROM timesteps WHERE year( date ) =$year ) AS usercount group by month;";
				foreach(parent::dbQuery($query) as $key=>$value){
	  		$result[$value['month']]=$value['users'];
					
			}
		return $result;
	}
	
	function getFTEEyear($year){
		$work_hours = $this->getWorkHoursYear($year);
		foreach($this->getUsersFromBase($year) as $month=>$users){
						$normaltime[$month]=$users*$work_hours[$month];	
				}
			foreach($this->getAllhoursYear($year) as $month=>$hours){
	  		$ftee[$month]=$hours/$normaltime[$month];	
			}
		return $ftee;
	}
	
	function getBRyear($year){
#	$query=""
	
		$work_hours = $this->getWorkHoursYear($year);
		foreach($this->getUsersFromBase($year) as $month=>$users){
						$normaltime[$month]=$users*$work_hours[$month];	
				}
			foreach($this->getAllhoursYear($year) as $month=>$hours){
	  		$ftee[$month]=$hours/$normaltime[$month];	
			}
		return $ftee;
	}
	
	
	
	function getAllhoursYearByProject($year,$project_id){
				$query = "SELECT sum(hours),month(date) from timesteps where year(date)=$year and project_id=$project_id GROUP BY month(date)";
				foreach(parent::dbQuery($query) as $key=>$value){
	  		$result[$value['month(date)']]=$value['sum(hours)'];
					
			}
		return $result;
	}
	
	function getPrjHoursPerMonthByUsers($year,$month){
		#$array=array();
		$query="SELECT user_id,project_id, proj_type, SUM(hours) FROM timesteps JOIN projects ON timesteps.project_id = projects.id WHERE YEAR( DATE ) =$year AND MONTH( DATE ) =$month AND hours !=0 GROUP BY user_id, project_id";
		foreach(parent::dbQuery($query) as $key=>$value){
		$array[$value['user_id']][$value['project_id']][$value['proj_type']]=$value['SUM(hours)'];	
		}
		return $array;
	}
	
	function getPrjHoursPerDayByUsers($date_start,$date_finish,$proj_type) {
		$query="SELECT user_id, project_id, proj_type,SUM(hours) FROM timesteps JOIN projects ON timesteps.project_id = projects.id WHERE (date>='".$date_start."' and date<='".$date_finish."') AND hours !=0 GROUP BY user_id, project_id";
		
	if ($proj_type =='All'){
			foreach(parent::dbQuery($query) as $key=>$value){
				$array[$value['user_id']][$value['project_id']][$value['proj_type']]=$value['SUM(hours)'];	
			}
	} elseif($proj_type =='project'){
			foreach(parent::dbQuery($query) as $key=>$value){
				if($value['proj_type'] == 'project'){
				$array[$value['user_id']][$value['project_id']][$value['proj_type']]=$value['SUM(hours)'];	
				}		
			}
		
			
		}elseif($proj_type =='tender'){
			
		foreach(parent::dbQuery($query) as $key=>$value){
				if($value['proj_type'] == 'tender'){
				$array[$value['user_id']][$value['project_id']][$value['proj_type']]=$value['SUM(hours)'];	
				}		
			}
			
		}elseif($proj_type =='office'){
			
		foreach(parent::dbQuery($query) as $key=>$value){
				if($value['proj_type'] == 'office'){
				$array[$value['user_id']][$value['project_id']][$value['proj_type']]=$value['SUM(hours)'];	
				}		
			}
			
		}elseif($proj_type =='off_hours'){
			
		foreach(parent::dbQuery($query) as $key=>$value){
				if($value['proj_type'] == 'off_hours'){
				$array[$value['user_id']][$value['project_id']][$value['proj_type']]=$value['SUM(hours)'];	
				}		
			}
			
		}
		return $array;
	}	
	
	function getPrjHoursPerDayByUser($date_start,$date_finish,$proj_type,$uid) {
		$query="SELECT user_id, project_id, proj_type,SUM(hours) FROM timesteps JOIN projects ON timesteps.project_id = projects.id WHERE (date>='".$date_start."' and date<='".$date_finish."') AND hours !=0 AND user_id=$uid GROUP BY user_id, project_id";
		
	if ($proj_type =='All'){
			foreach(parent::dbQuery($query) as $key=>$value){
				$array[$value['user_id']][$value['project_id']][$value['proj_type']]=$value['SUM(hours)'];	
			}
	} elseif($proj_type =='project'){
			foreach(parent::dbQuery($query) as $key=>$value){
				if($value['proj_type'] == 'project'){
				$array[$value['user_id']][$value['project_id']][$value['proj_type']]=$value['SUM(hours)'];	
				}		
			}
		
			
		}elseif($proj_type =='tender'){
			
		foreach(parent::dbQuery($query) as $key=>$value){
				if($value['proj_type'] == 'tender'){
				$array[$value['user_id']][$value['project_id']][$value['proj_type']]=$value['SUM(hours)'];	
				}		
			}
			
		}elseif($proj_type =='office'){
			
		foreach(parent::dbQuery($query) as $key=>$value){
				if($value['proj_type'] == 'office'){
				$array[$value['user_id']][$value['project_id']][$value['proj_type']]=$value['SUM(hours)'];	
				}		
			}
			
		}elseif($proj_type =='off_hours'){
			
		foreach(parent::dbQuery($query) as $key=>$value){
				if($value['proj_type'] == 'off_hours'){
				$array[$value['user_id']][$value['project_id']][$value['proj_type']]=$value['SUM(hours)'];	
				}		
			}
			
		}
		return $array;
	}	
	
	
		
}







?>
Return current item: Opensheet