<?php
define_class(__FILE__, 'MemberHistory');

require_once('class/DB/DBObj.php');

class MemberHistory extends DBObj
{
	var $itemName = 'Individual History';
	var $distinct = false;

	function MemberHistory($id = null, $id_individual = null, $showactiveonly=false)
	{
		$this->DBObj($id);
		$this->tblIndividual = $this->db->tblMember;
		$this->id_individual = intval($id_individual);
		$this->showactiveonly = $showactiveonly;
	}

	function _getWhat4Grid($opt = array())
	{
		return ($this->distinct?"DISTINCT id_individual":"{$this->tableName}.*").",
			CONCAT(Individual.first_name, ' ', Individual.middle_initial, IF (Individual.middle_initial = '' , '', '. '), Individual.last_name) as individual_name,
			IF(Location.name <> '', Location.name, CONCAT(Location.mailing_address, ' - ', Location.mailing_city, ', ', Location.mailing_state, ' ', Location.mailing_zip)) as location,
			Company.name as company,
			Company.name as individual_company
		";
	}

	function _getJoin4Grid()
	{
		$sql = '';
		if ($this->showactiveonly) {
			$sql .= "
				JOIN tmpMemberHistory as temporaryMemberHistory
					ON temporaryMemberHistory.id = {$this->tableName}.id
			";
		}
		$sql .= "
			JOIN {$this->tblIndividual} as Individual
				ON Individual.id = {$this->tableName}.id_individual
			LEFT JOIN {$this->db->tblLocation} as Location
				ON Location.id = Individual.id_location
			LEFT JOIN {$this->db->tblCompany} as Company
				ON Company.id = Location.id_company
		";
		//vdie($sql);
		return $sql;
	}

	function _getWhere4Grid($opt = array())
	{
		$sql = parent::_getWhere4Grid($opt);
		if ($this->id_individual) {
			$sql .= " AND {$this->tableName}.id_individual = ".$this->db->quote($this->id_individual, 'integer');
		}
		if ($this->showactiveonly) {
			$sql .= " AND {$this->tableName}.last_activity_date + INTERVAL 15 MINUTE > NOW() ";
		}
		return $sql;
	}
	
	function _getRes4Grid($what, $opt)
    {
		if ($this->showactiveonly) {
			$limit = $this->db->limit;
			$offset = $this->db->offset;
			$this->db->limit = 0;
			$this->db->offset = 0;

			$tmpTable = 'tmpMemberHistory';
	        $tmpSql = "
				CREATE TEMPORARY TABLE `{$tmpTable}` (
					`id` int(11) NOT NULL default '0',
					PRIMARY KEY  (`id`)
	        	)
			";
	        $res = $this->db->query($tmpSql);
			$tmpSql = "
				INSERT INTO {$tmpTable}
				SELECT MAX({$this->tableName}.id)
				FROM {$this->tableNameDB} as {$this->tableName}
				{$this->_getWhere4Grid($opt)}
				GROUP BY {$this->tableName}.id_individual
			";
			$this->db->query($tmpSql);
			$this->db->limit = $limit;
			$this->db->offset = $offset;
		}
		
		$sql = 	"SELECT $what
    			FROM  {$this->tableNameDB} AS {$this->tableName} ".
				$this->_getJoin4Grid($opt).' '.
				$this->_getWhere4Grid($opt).' '.
				$this->_getGroup4Grid($opt).' '.
				$this->_getHaving4Grid($opt).' '.
				$this->_getOrder4Grid($opt);
		$res = 	$this->db->query($sql);
		if ($this->showactiveonly) {
			$this->db->query("DROP TEMPORARY TABLE `{$tmpTable}`");
		}
        if (PEAR::isError($res)) {
        	$GLOBALS['_DEBUG']['level'] = D_ECHO;
        	dump($res->getUserInfo(), 'getList4Grid error');
		}
		return $res;
    }
	
	function getVisitByDay($nDay=0)
	{
		return $this->db->queryOne("SELECT COUNT(DISTINCT id_individual) FROM {$this->tableNameDB} WHERE TO_DAYS(NOW())-TO_DAYS(last_activity_date)=".$this->db->quote($nDay,'integer'));
	}
}