Location: PHPKode > projects > Opensheet > opensheet/classes/projects.class.php
<?php 
#include ('global.class.php');

class projects extends Base{
	#private $queue;
	
 #   public function __construct($queue){
	public function __construct(){
		   		}

		public function __destruct(){
		unset($this->queue);
	}
	

	
    public function getValue($queue){
    	return parent::dbQuery($queue);
	}
	
	
						
						        
			        #ready
			    function getProjectVars($project_id){
			        $query = "SELECT proj_name,id,proj_type,proj_index,status,billable,start,end,owner_id,note FROM `projects` WHERE `id` = '$project_id'";
				    return parent::dbQueryAssoc($query);
				  #  return $query;
			        }
				
 				function getProjectName($pid){
			        $query = "SELECT proj_name FROM `projects` WHERE `id` = '$pid'";
				    return parent::dbQueryRow($query);
			        }   

			    function getProjectIndex($pid){
			        $query = "SELECT proj_index FROM `projects` WHERE `id` = '$pid'";
				    return parent::dbQueryRow($query);
			        }  
			        
			    
						        
			        
			        #ready
 				function getProjectsbyOwner($owner){
	    			$query = "SELECT proj_name,id,owner_id,proj_type,proj_index FROM `projects` WHERE `owner_id` = '$owner' AND `status` = 'Y' ORDER BY `proj_name`,`proj_type`  DESC";
		   			 return parent::dbQuery($query);
				      }
				      
					#ready
		function getProjUsers($proj_index,$year){
			    	for ($mnth=1;$mnth<13;$mnth++){
			    	$query = "SELECT DISTINCT user_id FROM `timesteps` WHERE YEAR(date) = $year AND MONTH(date) = '$mnth' AND `project_id` = $proj_index and hours!='' and hours!='0'";
					foreach (parent::dbQuery($query) as $key=>$value){
							$users[$value['user_id']][$mnth] = $mnth;
						}
				}
			
				    return $users;
		}
					
		
					function getTaskUsers($task_index,$year){
			    	for ($mnth=1;$mnth<13;$mnth++){
			    	$query = "SELECT DISTINCT user_id FROM `tasksteps` WHERE YEAR(date) = $year AND MONTH(date) = '$mnth' AND `task_id` = $task_index";
					foreach (parent::dbQuery($query) as $key=>$value){
							$users[$value['user_id']][$mnth] = $mnth;
						}
				}
			
				    return $users;
		}
		
			
		
					#!!!!!!!!!!!! need to check
			function getUsersWithoutTask($project_id,$year){
			    	
				echo  	$query="SELECT DISTINCT ts2.user_id,month(ts2.date) AS MONTH FROM timesteps as ts2 WHERE NOT EXISTS ( SELECT DISTINCT ts.user_id   FROM tasksteps as ts , tasks as t   WHERE ts.task_id = t.id   AND t.project_id = '$project_id' AND ts2.user_id = ts.user_id) AND `project_id` = '$project_id'  AND ts2.date BETWEEN '$year-01-01' AND  '$year-01-01' + INTERVAL 1 YEAR";
					foreach (parent::dbQuery($query) as $key=>$value){
					$users[$value['user_id']][$value['MONTH']] = $value['MONTH'];
						}
			    return $users;
		}
		
		
		
		
		
			        
     				#ready
				function getProjects($status){
			    	if ($status == "active"){	
			    	$query = "SELECT id,proj_name,proj_type,status,owner_id FROM `projects` WHERE `status` = 'Y' ORDER BY `proj_name`  DESC ";
				  	} else {
				  	$query = "SELECT id,proj_name,proj_type,status,owner_id FROM `projects` WHERE `status` = 'Y' ORDER BY `proj_name`  DESC ";
				  					  	}
			   		foreach(parent::dbQuery($query) as $num=>$key) {
					$answer[$key['id']] = array('proj_name'=>$key['proj_name'],'id'=>$key['id'],'proj_type'=>$key['proj_type'],'status'=>$key['status'],'owner_id'=>$key['owner_id']);	
						 	}
				   	asort($answer);
				   	return $answer;				
					
				    }

				    #ready
				function getProjectTasks($project_id){
			    	$query = "SELECT task_name,task_index,id,status,start FROM `tasks` WHERE `project_id` = '$project_id'";
			    	foreach(parent::dbQuery($query) as $num=>$key) {
					$answer[$key['id']] = array('task_name'=>$key['task_name'],'id'=>$key['id'],'status'=>$key['status'],'start'=>$key['start'],'task_index'=>$key['task_index']);	
				   	}
 				   	asort($answer);
				   	return  $answer;
				    }       
			        
				    
				    #summ of hours in project for user
				    function getPrjUsrSummMonth($project_id,$user_id,$year,$month){
				    $query = "select sum(t.hours),p.id,p.proj_name,p.proj_index from projects as p,timesteps as t where p.id = t.project_id and t.user_id=$user_id and t.project_id =$project_id and year(t.date)=$year and month(t.date)=$month";
			    	foreach(parent::dbQuery($query) as $num=>$key) {
					$answer[$key['id']] = array('proj_name'=>$key['proj_name'],'proj_index'=>$key['proj_index'],'summ'=>$key['sum(t.hours)']);	
				   	}
 				   	return  $answer;	
				    }
			        
 					#summ of hours in project for user
				    function getTaskUsrSummMonth($task_id,$user_id,$year,$month){
				    $query = "select sum(ts.hours),t.id,t.task_name,t.task_index from tasks as t,tasksteps as ts where t.id = ts.task_id and ts.user_id=$user_id and ts.task_id =$task_id and year(ts.date)=$year and month(ts.date)=$month";
			    	foreach(parent::dbQuery($query) as $num=>$key) {
					$answer[$key['id']] = array('task_name'=>$key['task_name'],'task_index'=>$key['task_index'],'summ'=>$key['sum(ts.hours)']);	
				   	}
 				   	return  $answer;	
				    }        
			        
			        
 		
		 
		public    function setProjectValue($proj_index,$value_name,$value){
			    	db_connect();
			    	$query = "UPDATE  `timesheet`.`projects` SET  `$value_name` = '".$value."' WHERE `projects`.`proj_index` = '".$proj_index."'";	
			    	$result = mysql_query($query) or die("Invalid query: " . mysql_error());
					#return 	$query;		 
		}   
		        
		
		
		#ready
		function getTaskSumm($task_index,$year,$month){
				    $query = "SELECT sum(hours)  FROM `tasksteps` WHERE YEAR(date) = '$year' AND MONTH(date) = '$month' AND `task_id` = '$task_index' ";
						   return parent::dbQueryRow($query);
					#return $query;
		}
		#seems to be ready
		function getProjSumm($proj_index,$year,$month){
				    $query = "SELECT sum(hours) FROM `timesteps` WHERE YEAR(date) = $year AND MONTH(date) = $month AND `project_id` = $proj_index";
						   return parent::dbQueryRow($query);
					#return $query;
		}
		
		function getProjectSummYear($proj_index,$year){
				    $query = "SELECT sum(hours) FROM `timesteps` WHERE YEAR(date) = $year AND `project_id` = $proj_index";
						   return parent::dbQueryRow($query);
					#return $query;
		}
		
		
			        
			#check
		function getTaskHourSummUser($task_index,$user_index,$year,$month){
									$query = "SELECT sum(hours) FROM `tasksteps` WHERE `user_id` = '$user_index' AND `task_id` = '$task_index' AND YEAR(date) = '$year' AND MONTH(date) = '$month'";
							return parent::dbQueryRow($query);
		}
		
		
		#check
		function getTaskHoursByUser($task_index,$year){
				$getTasksumm = new projects();
				$gettask= new projects();
					foreach ($gettask->getTaskUsers($task_index,$year) as $user_index=>$array) {
						foreach ($array as $key=>$value) {
							$res_array[$user_index][$key] = $getTasksumm->getTaskHourSummUser($task_index,$user_index,$year,$key);
						}
					}
			return $res_array;		
		}
		
		
		#check
		function getProjHoursByUser($proj_index,$user_index,$year,$month){
				    $query = "SELECT sum(hours) FROM `timesteps` WHERE YEAR(date) = '$year' AND MONTH(date) = '$month' AND `project_id` = '$proj_index' AND `user_id` = '$user_index'";
						   return parent::dbQueryRow($query);
					#return $query;
		}
		
		
		
				#ready
			function makeIndex($type){
				$prefix =  new getSetting;
				$prefix = $prefix->getValue('prefix'); 
				db_connect();
				$query="SELECT count(id) FROM `projects` WHERE `proj_type` = '$type'";
				#echo $query;
				#PRE MADE
				$step= $this->dbQueryRow($query);
				$step++;
				if ($type == 'project'){
				return $index = "$prefix".-  date('Y')."-$step-P";	
				 } elseif($type == 'tender') {
				return $index = "$prefix".-  date('Y')."-$step-T";	
				 } elseif($type == 'office') {
				return $index = "$prefix".-  date('Y')."-$step-O"; 	
				 }  elseif($type == 'off_hours') {
				return $index = "$prefix".-  date('Y')."-$step-Off"; 	
				 }   				
				#	$query = "SELECT type FROM users WHERE `user_index` = '$login'";
				#	return $this->dbQueryRow($query);
			        }
			        
			 #ready       
			function getProjOwner($proj_index){
				    $query = "SELECT real_name FROM `users` where `id`=(SELECT `owner_id` FROM `projects` where `id` ='$proj_index')";
						   return parent::dbQueryRow($query);
						   #return $query;
				}
				
				
		function getTypeOfProject($proj_index){
				    $query = "SELECT `proj_type` FROM `projects` where `id` ='$proj_index'";
						   return parent::dbQueryRow($query);
					
		}
		
  					  #ready
			     function getProjectsByType($type){
			      $query = "SELECT proj_name,proj_index,id,proj_type,status,billable,start,end,owner_id,note FROM `projects` WHERE `proj_type` = '$type'";
				    return parent::dbQuery($query);
				 
			        }
		
		function getPODs(){
				    $query = "SELECT id,name,note FROM `project_types`";
				  #  print_r(parent::dbQuery($query));
					foreach(parent::dbQuery($query) as $key=>$value){
					$types[$key]=array('id'=>$key,'name'=>$value['name'],'note'=>$value['note']);
					}
				    return $types;
					
		}
		
					function getPIDfromName($id){
				    $query = "SELECT name FROM `project_types` WHERE `id`='$id'";
				  #  print_r(parent::dbQuery($query));
				#	foreach(parent::dbQueryRow($query) as $key=>$value){
				#	$types[$key]=array('id'=>$key,'name'=>$value['name'],'note'=>$value['note']);
				#	}
				    return parent::dbQueryRow($query);
					
		}
		
		function hoursProjectByType($project_type,$year,$month){
			$query="select sum(hours),user_id,project_id from timesteps where project_id=ANY(SELECT id from projects where proj_type='$project_type') and year(date)=$year and month(date)=$month and hours!='' and hours!='0' GROUP BY user_id,project_id;";
			foreach(parent::dbQuery($query) as $key=>$value){
					$result[$value['user_id']][$value['project_id']]=$value['sum(hours)'];
					}	
			return $result;
			
		}
		
		function getProjectsByTypeFromSheet($project_type,$year,$month){
			$query="select sum(hours),project_id from timesteps where project_id=ANY(SELECT id from projects where proj_type='$project_type') and year(date)=$year and month(date)=$month and hours!='' and hours!='0' GROUP BY project_id";
			foreach(parent::dbQuery($query) as $key=>$value){
					$result[$value['project_id']]=$value['sum(hours)'];
					}	
			return $result;
			
		}
		
		function addProject($uid,$name,$proj_index,$type,$status,$billable,$start,$note){
			$query="INSERT INTO `timesheet`.`projects` (`owner_id`, `type_id`, `proj_name`, `proj_index`, `proj_type`, `status`, `billable`,`start`,`note`) VALUES ('$uid', '1', '$name', '$proj_index', '$type', '$status', '$billable', '$start', '$note')";
			parent::dbQuery($query);
			}
			
		function updateProject($pid,$uid,$name,$proj_index,$type,$status,$billable,$start,$note){
			$query="UPDATE `timesheet`.`projects` SET `proj_name` = '$name',`proj_index` = '$proj_index',`owner_id` = '$uid',`proj_type` = '$type',`status` = '$status',`billable` = '$billable',`start` = '$start',`note` = '' WHERE `projects`.`id` =$pid";
			parent::dbQuery($query);
		#return $query;
			}
				#!!
		function addTask($pid,$name,$task_index,$status,$start,$note){
			$query="INSERT INTO `timesheet`.`tasks` (`project_id`,`type_name`,`task_index`,`status`,`start`,`note`) VALUES ('$uid', '1', '$name', '$proj_index', '$type', '$status', '$billable', '$start', '$note')";
			parent::dbQuery($query);
			}	
		
		
		
}

?>
Return current item: Opensheet