<?php
require_once (PATH_TABLES . "activity/ActivityConsultantTable.php") ;
require_once (PATH_TABLES . "activity/ActivityTimingDetailTable.php") ;
require_once (PATH_TABLES . "activity/ActivityHeaderTable.php") ;
require_once (PATH_TABLES . "activity/ProjectTable.php") ;
require_once (PATH_TABLES . "activity/JobCentreTable.php") ;
require_once (PATH_TABLES . "admin/UserTable.php") ;
require_once (PATH_MODELS . "base/MasterBase.php") ;

class ActivityConsultantClass extends MasterBase {
	function __construct($db) {
		$this->db 		= $db ;
		$this->tbl 		= ActivityConsultantTable::C_TABLE ;
		$this->fldid 	= ActivityConsultantTable::C_ID ;
	}
	function __destruct() {
	}

	function getConsultantList($filter=null){

		$sql = "";
		$sql .= " select distinct u.".UserTable::C_NAME.", ";
		$sql .= "  j.".JobCentreTable::C_JOB_NAME.", j.".JobCentreTable::C_JOB_NUMBER. ", ";
		$sql .= "  coalesce(sum(t.".ActivityTimingDetailTable::C_TOTAL_OVERRIDE."),0) as ".'billable'.", ";
		$sql .= "  ".'nonbillable'." = 0, ";
		$sql .= "  j.".JobCentreTable::C_JOB_NAME." ";
		$sql .= "  from ".ActivityHeaderTable::C_TABLE." h ";
		// $sql .= "  join ".ProjectTable::C_TABLE." p ";
		// $sql .= "  on h.".ActivityHeaderTable::C_PROJECT_ID." = p.".ProjectTable::C_ID." ";
		$sql .= "  join ".JobCentreTable::C_TABLE." j ";
		$sql .= "  on h.".ActivityHeaderTable::C_JOB_ID." = j.".JobCentreTable::C_ID." ";
		$sql .= "  join ".UserTable::C_TABLE." u ";
		$sql .= "  on h.".ActivityHeaderTable::C_MODIFY_BY." = u.".UserTable::C_ID." ";
		$sql .= "  join ".ActivityTimingDetailTable::C_TABLE." t ";
		$sql .= "  on h.".ActivityHeaderTable::C_ID." = t.".ActivityTimingDetailTable::C_ID." ";
		$sql .= "  where h.".ActivityHeaderTable::C_BILL." = 1 ";
		$sql .=  $filter;
		$sql .= "  group by u.".UserTable::C_NAME.", j.".JobCentreTable::C_JOB_NAME.", j.".JobCentreTable::C_JOB_NUMBER." ";
		
		$sql .= " union";

		$sql .= " select distinct u.".UserTable::C_NAME.", ";
		$sql .= "  j.".JobCentreTable::C_JOB_NAME.", j.".JobCentreTable::C_JOB_NUMBER . ", ";
		$sql .= "  coalesce(sum(t.".ActivityTimingDetailTable::C_TOTAL_OVERRIDE."),0) as ".'nonbillable'.", ";
		$sql .= "  ".'billable'." = 0, ";
		$sql .= "  j.".JobCentreTable::C_JOB_NAME." ";
		$sql .= "  from ".ActivityHeaderTable::C_TABLE." h ";
		// $sql .= "  join ".ProjectTable::C_TABLE." p ";
		// $sql .= "  on h.".ActivityHeaderTable::C_PROJECT_ID." = p.".ProjectTable::C_ID." ";
		$sql .= "  join ".JobCentreTable::C_TABLE." j ";
		$sql .= "  on h.".ActivityHeaderTable::C_JOB_ID." = j.".JobCentreTable::C_ID." ";
		$sql .= "  join ".UserTable::C_TABLE." u ";
		$sql .= "  on h.".ActivityHeaderTable::C_MODIFY_BY." = u.".UserTable::C_ID." ";
		$sql .= "  join ".ActivityTimingDetailTable::C_TABLE." t ";
		$sql .= "  on h.".ActivityHeaderTable::C_ID." = t.".ActivityTimingDetailTable::C_ID." ";
		$sql .= "  where h.".ActivityHeaderTable::C_BILL." = 2 ";
		$sql .=  $filter;
		$sql .= "  group by u.".UserTable::C_NAME.", j.".JobCentreTable::C_JOB_NAME.", j.".JobCentreTable::C_JOB_NUMBER." ";

		$rows = $this->db->getTable($sql);

		if (!is_null($rows)) {
			return $rows;
		}else{
			return null;
		}



	}
}
?>