<?

class project{

function project(){

	global $auth;

	$this->user_id=$auth->uid;

}

function getProject($projectsearch){

	global $dbi;

	$sql="SELECT a.project_id, a.project_name,(select count(project_id) from project_issue where project_id=a.project_id) as issue_cnt,(select count(project_task_id) from project_task c inner join project_issue d on c.project_issue_id=d.project_issue_id where d.project_id=a.project_id) as task_cnt ";
	$sql=$sql.",(SELECT count( e.project_task_id ) FROM project_task e INNER JOIN project_task_item g ON g.project_task_id = e.project_task_id INNER JOIN project_issue f ON e.project_issue_id = f.project_issue_id WHERE f.project_id = a.project_id) AS task_item_cnt";
	$sql=$sql.",a.create_date, concat(b.firstname,' ',b.lastname) as user_id, a.start_date, a.end_date, a.project_status, concat( h.firstname, ' ', h.lastname ) AS resp_id FROM project a LEFT JOIN users b ON a.user_id = b.id  LEFT JOIN users h ON a.resp_uid = h.id ";
	if ($projectsearch<>"")
	    {
		$sql=$sql." where a.project_name like '%".$projectsearch."%' ";
	    }
	$sql=$sql." order by a.create_date desc";
	$res=sql_query($sql,$dbi) or die($sql);
	while(list($pid,$pname,$cissue,$ctask,$ctaski,$cdate,$uid,$sdate,$edate,$pstatus,$rid)=$fetch_array=sql_fetch_array($res,$dbi))
	 {
            $data[]=array(pid=>$pid,pname=>str_replace($projectsearch,'<b>'.$projectsearch.'</b>',$pname),cissue=>$cissue,ctask=>$ctask,ctaski=>$ctaski,cdate=>$cdate,uid=>$uid,sdate=>$sdate,edate=>$edate,pstatus=>$pstatus,rid=>$rid);
	 }
	return $data;
}

function getUsersList(){

	global $dbi;

	$sql="select id, concat(firstname,' ',lastname) as username from  users ";
	$res=sql_query($sql,$dbi) or die($sql);
	while(list($uid,$uname)=$fetch_array=sql_fetch_array($res,$dbi))
	 {
            $data[]=array(uid=>$uid,uname=>$uname);
	 }
	return $data;
}

function getProjectIssue($issue){

	global $dbi;
	$sql="select a.project_issue_id,a.project_id,a.issue_name,(select count(project_issue_id) from project_task where project_issue_id=a.project_issue_id) as task_cnt ";
	$sql=$sql.",(SELECT count( project_issue_id ) FROM project_task c inner join project_task_item d on c.project_task_id=d.project_task_id WHERE c.project_issue_id = a.project_issue_id) AS task_item_cnt ";
	$sql=$sql.",concat(b.firstname,' ',b.lastname) as user_id,a.create_date,a.status from project_issue a LEFT JOIN users b ON a.user_id = b.id where project_id=".$issue." order by create_date desc";
	$res=sql_query($sql,$dbi) or die($sql);	
	while(list($piid,$pid,$iname,$tcount,$ticount,$uid,$cdate,$istatus)=$fetch_array=sql_fetch_array($res,$dbi))
	 {
            $data[]=array(piid=>$piid,pid=>$pid,iname=>$iname,tcount=>$tcount,ticount=>$ticount,uid=>$uid,cdate=>$cdate,istatus=>$istatus);
	 }
	return $data;	
}

function getProjectTask($task){

	global $dbi;
	$sql="select a.project_task_id,a.project_issue_id,a.task_name,(select count(project_task_id) from project_task_item where project_task_id=a.project_task_id) as item_cnt,concat(b.firstname,' ',b.lastname) as user_id,a.create_date,a.status,c.project_id from project_task a LEFT JOIN users b ON a.user_id = b.id LEFT JOIN project_issue c on a.project_issue_id=c.project_issue_id where a.project_issue_id=".$task." order by a.create_date desc";
	$res=sql_query($sql,$dbi) or die($sql);	
	while(list($tid,$piid,$tname,$ticount,$uid,$cdate,$tstatus,$pid)=$fetch_array=sql_fetch_array($res,$dbi))
	 {
            $data[]=array(tid=>$tid,piid=>$piid,tname=>$tname,ticount=>$ticount,uid=>$uid,cdate=>$cdate,tstatus=>$tstatus,pid=>$pid);
	 }
	return $data;	
}

function getProjectTaskItem($task_item){

	global $dbi;
	$sql="select a.project_task_item_id,a.project_task_id,a.task_item_name,concat(b.firstname,' ',b.lastname) as user_id,a.create_date,a.status from project_task_item a LEFT JOIN users b ON a.user_id = b.id where a.project_task_id=".$task_item." order by a.create_date desc";
	$res=sql_query($sql,$dbi) or die($sql);	
	while(list($tiid,$tid,$tiname,$uid,$cdate,$tistatus)=$fetch_array=sql_fetch_array($res,$dbi))
	 {
            $data[]=array(tiid=>$tiid,tid=>$tid,tiname=>$tiname,uid=>$uid,cdate=>$cdate,tistatus=>$tistatus);
	 }
	return $data;	
}

function getForum(){

	global $dbi;
	$sql="select project_forum_id,project_task_item_id,forum_subject,forum_message,user_id,create_date,status from project_forum a order by a.create_date desc";
	$res=sql_query($sql,$dbi) or die($sql);	
	while(list($pfid,$tiid,$subject,$message,$uid,$cdate,$status)=$fetch_array=sql_fetch_array($res,$dbi))
	 {
            $data2[]=array(pfid=>$pfid,tiid=>$tiid,subject=>$subject,message=>$message,uid=>$uid,cdate=>$cdate,status=>$status);
	 }
	return $data2;	
}

function deleteProject($id) {

	global $dbi;

	$sql="delete from project where project_id=$id";
	sql_query($sql,$dbi) or die($sql);
}

function deleteIssue($id) {

	global $dbi;

	$sql="delete from project_issue where project_issue_id=$id";
	sql_query($sql,$dbi) or die($sql);
}

function deleteTask($id) {

	global $dbi;

	$sql="delete from project_task where project_task_id=$id";
	sql_query($sql,$dbi) or die($sql);
}

function deleteTaskItem($id) {

	global $dbi;

	$sql="delete from project_task_item where project_task_item_id=$id";
	sql_query($sql,$dbi) or die($sql);
}

function createProject($name,$start,$end,$rid) {

	global $dbi;
	global $auth;

	$sql="insert into project (user_id,project_name,start_date,end_date,resp_uid) values ($auth->uid,'$name','$start','$end',$rid)";
	sql_query($sql,$dbi) or die($sql);
}

function createIssue($name,$pid) {

	global $dbi;
	global $auth;

	$sql="insert into project_issue (user_id,issue_name,project_id) values ($auth->uid,'$name','$pid')";
	sql_query($sql,$dbi) or die($sql);
}

function createTask($name,$piid) {

	global $dbi;
	global $auth;

	$sql="insert into project_task (user_id,task_name,project_issue_id) values ($auth->uid,'$name','$piid')";
	sql_query($sql,$dbi) or die($sql);
}

function createTaskItem($name,$tid) {

	global $dbi;
	global $auth;

	$sql="insert into project_task_item (user_id,task_item_name,project_task_id) values ($auth->uid,'$name','$tid')";
	sql_query($sql,$dbi) or die($sql);
}

function createForum($tiid,$subject,$body) {

	global $dbi;
	global $auth;

	$sql="insert into project_forum (user_id,project_task_item_id,forum_subject,forum_message) values ($auth->uid,$tiid,'$subject','$body')";
	sql_query($sql,$dbi) or die($sql);
}

} // class

?>