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

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

class Location extends DBObj
{
	var $itemName = 'Location';
	var $itemsName = 'Locations';
	var $_filterFieldsLocation = array('Location.id', 'Location.nrds_id', 'Location.mailing_city', 'Location.physical_city', 'LocationCompany.name', 'Location.name');
	var $_filterFieldsCompany = array('Company.id', 'Company.name');

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

	function initFromForm(&$frm)
    {
    	$files = array();
		$data = $frm->exportValues();
		if ($data['mailing_as_physical'] == 'y') {
			foreach (array('address', 'city', 'state', 'zip') as $field) {
				$data['mailing_'.$field] = $data['physical_'.$field];
			}
		}
        $nrdsId = ctype_digit((string)$data['nrds_id']) ? $data['nrds_id'] : 0;
        if (!$nrdsId) {
			unset($data['nrds_id']);
		}
		$this->arrayParseUrl2Tag($data);
		$this->initFromArray($data, $files);
        return true;
    }

	function _getWhat4Grid($opt = array())
	{
		//, IF(Location.physical_city <> '', ' (', ''), Location.physical_city, IF(Location.physical_city <> '', ') ', '')
		//CONCAT(Location.physical_address, ' - ', Location.physical_city, ', ', Location.physical_state, ' ', Location.physical_zip) as address,
		return array(
			'location'=>"
					Location.id,
					'location' as record_type,
					'Location' as record_type_title,
					Location.nrds_id,
					COUNT(Individual.id) as individual_cnt,
					1 as location_cnt,
					IF(Location.name <> '', CONCAT(Location.name), CONCAT(Location.physical_address, ' - ', Location.physical_city, ', ', Location.physical_state, ' ', Location.physical_zip)) as name,
					Location.physical_city,
					Location.physical_address as address,
					DLocationType.title as type,
					Location.website,
					Location.status,
					Location.changed,
					Location.added,
					LocationCompany.id as location_company_id,
					LocationCompany.name as location_company,
					Location.phone,
					Location.fax,
					Location.contact_name,
					Location.contact_phone,
					Location.physical_address,
					Location.physical_city,
					Location.physical_state,
					Location.physical_zip,
					Location.mailing_address,
					Location.mailing_city,
					Location.mailing_state,
					Location.mailing_zip,
					Location.allow_mcpe_access,
					DMCPEAccessType.title as  mcpe_access_type,
					DMCPEStatus.title as  current_mcpe_status,
					Location.mcpe_status_date,
					CONCAT(DesignatedRealtor.first_name, ' ', DesignatedRealtor.middle_initial, IF (DesignatedRealtor.middle_initial = '' , '', '. '), DesignatedRealtor.last_name) as designated_realtor_full_name,
					Location.note,
					Location.data_feeds_office,
					Location.data_feeds_individual,
					Location.raw_datafeeds,
					Location.templates,
					LocationCompany.name company_name
			",
			'company'=>"
					Company.id,
					'company' as record_type,
					'Company' as record_type_title,
					'' as nrds_id,
					COUNT(Individual.id) as individual_cnt,
					COUNT(DISTINCT Location.id) as location_cnt,
					Company.name,
					'' as physical_city,
					'' as address,
					IFNULL(DCompanyType.title, 'Other') as type,
					Company.website,
					'' as status,
					Company.changed,
					Company.added,
					'' as location_company,
					'' as phone,
					'' as fax,
					'' as contact_name,
					'' as contact_phone,
					'' as physical_address,
					'' as physical_city,
					'' as physical_state,
					'' as physical_zip,
					'' as mailing_address,
					'' as mailing_city,
					'' as mailing_state,
					'' as mailing_zip,
					'' as allow_mcpe_access,
					'' as  mcpe_access_type,
					'' as current_mcpe_status,
					'' as mcpe_status_date,
					'' as designated_realtor_full_name,
					'' as note
			",
		);
	}

	function _getOrder4Grid($opt=array()){
		if ($opt['order_by']=='name') $opt['order_by']='name, physical_city';
		if ($opt['order_by']=='name DESC') $opt['order_by']='name DESC, physical_city DESC';
		return parent::_getOrder4Grid($opt);
	}

	function _getRes4Grid($what, $opt)
    {
		$sqlLocation = 	"
			SELECT
				{$what['location']}
			FROM  {$this->tableNameDB} AS Location
			LEFT JOIN {$this->tblIndividual} as Individual
				ON Individual.id_location = Location.id
			LEFT JOIN {$this->db->tblDLocationType} as DLocationType
				ON DLocationType.id = Location.id_type
			LEFT JOIN {$this->db->tblCompany} as LocationCompany
				ON LocationCompany.id = Location.id_company
			LEFT JOIN {$this->db->tblDMCPEAccessType} as DMCPEAccessType
				ON DMCPEAccessType.id = Location.id_mcpe_access_type
			LEFT JOIN {$this->db->tblDMCPEStatus} as DMCPEStatus
				ON DMCPEStatus.id = Location.id_current_mcpe_status
			LEFT JOIN {$this->tblIndividual} as DesignatedRealtor
				ON DesignatedRealtor.id = Location.id_designated_realtor
			".
			$this->_getWhere4GridLocation($opt).' '.
			' GROUP BY Location.id ';

		$sqlCompany .= "
			SELECT
				{$what['company']}
			FROM  {$this->db->tblCompany} AS Company
			LEFT JOIN {$this->db->tblLocation} as Location
				ON Location.id_company = Company.id
			LEFT JOIN {$this->tblIndividual} as Individual
				ON Individual.id_location = Location.id
			LEFT JOIN {$this->db->tblDCompanyType} as DCompanyType
				ON DCompanyType.id = Company.id_type
			".
			$this->_getWhere4GridCompany($opt).' '.
			' GROUP BY Company.id ';
		$sql = '';
		switch($this->filter['search_type']) {
		case 'company':
			$sql .= $sqlCompany;
			break;
		case 'location':
			$sql .= $sqlLocation;
			break;
		default:
			$sql = $sqlLocation;
			$sql .= " UNION ";
			$sql .= $sqlCompany;
			break;
		}
		$sql .= $this->_getOrder4Grid($opt);
		$res = 	$this->db->query($sql);
        if (PEAR::isError($res)) {
        	$GLOBALS['_DEBUG']['level'] = D_ECHO;
        	dump($res->getUserInfo(), 'getList4Grid error');
		}
		return $res;
    }

	function _getCountRows($opt)
	{
		$sqlLocation = 	"SELECT COUNT(*) FROM  {$this->tableNameDB} AS Location ".$this->_getWhere4GridLocation($opt);
		$sqlCompany = 	"SELECT COUNT(*) FROM  {$this->db->tblCompany} AS Company ".$this->_getWhere4GridCompany($opt);
		$cntLocation = 0;
		$cntCompany = 0;

		switch($this->filter['search_type']) {
		case 'company':
			$cntCompany = (int)$this->db->queryOne($sqlCompany);
			break;
		case 'location':
			$cntLocation = (int)$this->db->queryOne($sqlLocation);
			break;
		default:
			$cntLocation = (int)$this->db->queryOne($sqlLocation);
			$cntCompany = (int)$this->db->queryOne($sqlCompany);
			break;
		}
		return $cntLocation + $cntCompany;
	}


    function getList4Grid($opt = array() )
	{
		$start = 0; $ipp = 0; $ipp = 1024 * 1024 * 1024; // default values
		extract($opt);
		if ('' == $this->_getGroup4Grid($opt)){
			$this->db->setLimit($ipp, $start);
			$res = $this->_getRes4Grid($this->_getWhat4Grid($opt), $opt);
			$all = $res->fetchAll();
			$res->free();
			unset($opt['order_by']);
			$num_rows = $this->_getCountRows($opt);
		}else{
			$res = $this->_getRes4Grid($this->_getWhat4Grid($opt), $opt);
			$res->seek($start);
			$all = array();
	        while ( ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) && count($all) < $ipp) {
	        	$all[] = $row;
	        };
			$num_rows = $res->numRows();
			$res->free();
		};
		$this->listParseUrl($all);
		$list = array(
			'num_rows'  => $num_rows,
			'num_pages' => 1 + floor(($num_rows - 1) / $ipp),
			'list'      => $all,
		);
		return $list;
	}

	function getDState4Select()
	{
		$sql = "
			SELECT short as id, short as title
			FROM {$this->db->tblDState}
			ORDER BY title
		";
		return $this->db->queryAll($sql, 0, null, true);
	}

	function initFromDB()
    {
		// ����������� ��� action = new
		if (!$this->id && $this->id_company) {
			$companyData = $this->getCompanyById($this->id_company);
			$this->_data = array(
				'id_company'=>$this->id_company,
				'company_name'=>$companyData['name'],
			);
			return true;
		}
        $this->_data = $this->getFromDB($this->id);
		$this->_data['added'] = date('F j\t\h, Y - g:i a', strtotime($this->_data['added']));
		$this->_data['changed'] = date('F j\t\h, Y - g:i a', strtotime($this->_data['changed']));
		$this->_data['mcpe_status_date'] = date('F j\t\h, Y - g:i a', strtotime($this->_data['mcpe_status_date']));
        $nrdsId = ctype_digit((string)$this->_data['nrds_id']) ? $this->_data['nrds_id'] : 0;
        if (!$nrdsId) {
			$this->_data['nrds_id'] = '';
		}
        return true;
    }

	function getData($field = false)
	{
		$value = parent::getData($field);
		if ($field == 'company_name' && empty($value)) {
			return 'No Company';
		}
		return $value;
	}

	function getIdsFromDB($tableName, $DTableName, $field_name, $id_parent, $addonWhere = '')
	{
		$sql = "
			SELECT tblLink.{$field_name} as id, tblLink.{$field_name} as title
			FROM {$tableName} as tblLink
			JOIN {$DTableName} as Dictionary
				ON tblLink.{$field_name} = Dictionary.id
			WHERE tblLink.id_parent = ".$id_parent."
				{$addonWhere}
		";
		$res = $this->db->queryAll($sql, 0, nnull, true);
		$this->_sqlError($res);
		return $res;
	}

	function getFromDB($id, $field = false)
    {
    	$id = intval($id);
    	$sql = "SELECT
					{$this->tableName}.*,
					Company.name as company_name,
					(SELECT COUNT(*) FROM {$this->tblIndividual} WHERE id_location = {$id}) as individual_cnt,
					NRDS.added as nrds_id_date_joined,
                    IF(payMember.id IS NOT NULL, CONCAT(payMember.first_name, ' ', payMember.middle_initial, IF (payMember.middle_initial = '' , '', '. '), payMember.last_name), pay_member) AS pay_member
    			FROM {$this->tableNameDB} AS {$this->tableName}
				LEFT JOIN {$this->db->tblCompany} as Company
					ON {$this->tableName}.id_company = Company.id
				LEFT JOIN {$this->db->tblNRDS} as NRDS
					ON NRDS.id = {$this->tableName}.nrds_id
				LEFT JOIN {$this->db->tblMember} AS payMember
                    ON payMember.id = {$this->tableName}.id_pay_member
    			WHERE {$this->tableName}.id = $id";
    	$res = $this->db->queryRow($sql);
        if (null === $res) {
            return $res;
        }
		if ($field === false) {
			$res['ids_individual'] = $this->getIdsFromDB($this->tableNameDB.'2Individual', $this->tblIndividual, 'id_individual', $id, "AND Dictionary.allow_mcpe_access = 'y'");
		}
		$this->arrayParseTag2Url(&$res);
    	return $this->getSubElem($res, $field);
    }

	function insert($data = null, $files = null)
	{
		if (!is_null($data)) {
			$this->initFromArray($data, $files);
		}
        $this->_initTable();
        $data = $this->_data;
		unset($data['id']);
		$fields = $this->getSimpleFieldList();
		if (isset($fields['sorter'])) {
			$data['sorter'] = $this->getSorter();
		}
		$data['mcpe_status_date'] = date('Y-m-d H:i:s');
        $this->id = $this->table->insert($data);
		$this->updateIds($this->tableNameDB.'2Individual', 'id_individual', $this->_data['ids_individual']);
		$this->updateNrdsId($this->_data['nrds_id']);
		$this->updateIndividualLocation($this->_data['id_individual'], $this->id);
		$this->handleFiles();
        return $this->id;
	}
	
	function isUnique($field, $name)
	{
		require_once('app/Member/Member.php');
		return Member::isUnique($field, $name, 'location');
	}

	function updateIndividualLocation($id_individual, $id_location)
	{
		$sql = "
			UPDATE {$this->tblIndividual}
			SET id_location = ".$this->db->quote($id_location, 'integer')."
			WHERE id = ".$this->db->quote($id_individual, 'integer')."
		";
		$this->db->query($sql);
	}

	function update($data = null, $files = null)
	{
        if (!is_null($data)) {
            $this->initFromArray($data, $files);
        }
        // ��� ��� ������� �����'������� ����
		$oldData = $this->db->queryRow("SELECT * FROM {$this->tableNameDB} WHERE id = ". $this->db->quote($this->id, 'integer') );
		$currDate = date('Y-m-d H:i:s');
		if ($oldData['id_current_mcpe_status'] != $this->_data['id_current_mcpe_status']) {
			$this->_data['mcpe_status_date'] = $currDate;
		}
		//
		$this->_initTable();
        $this->table->update($this->_data, 'id = '. $this->db->quote($this->id, 'integer') );
		$this->updateNrdsId($this->_data['nrds_id'], $this->_data['id_company']);
		$this->updateIds($this->tableNameDB.'2Individual', 'id_individual', $this->_data['ids_individual']);
		$this->handleFiles();
        return true;
	}

	function delete()
	{
		$this->deleteIds($this->tableNameDB.'2Individual');
        parent::delete();
        return true;
	}

	function getRealtors4Select()
	{
		$sql = "
			SELECT
				Individual.id,
				CONCAT(Individual.first_name, ' ', Individual.middle_initial, IF (Individual.middle_initial = '' , '', '. '), Individual.last_name) as name
			FROM {$this->tblIndividual} as Individual
			WHERE Individual.id_location = ".$this->db->quote($this->id, 'integer')."
		";
		$res = $this->db->queryAll($sql, 0, null, true);
		$this->_sqlError($res);
		return $res;
	}

	function getIndividuals4Select()
	{
		$sql = "
			SELECT
				Individual.id,
				CONCAT(Individual.first_name, ' ', Individual.middle_initial, IF (Individual.middle_initial = '' , '', '. '), Individual.last_name) as name
			FROM {$this->tblIndividual} as Individual
			WHERE
				Individual.id_location = ".$this->db->quote($this->id, 'integer')."
				AND Individual.allow_mcpe_access = 'y'
		";
		$res = $this->db->queryAll($sql, 0, null, true);
		$this->_sqlError($res);
		return $res;
	}

	function _filterSqlParceValue($value, $type = null)
	{
		if (is_array($value)) {
			return ' IN ('.implode(', ', $value).')';
		}
		return ' = '.$this->db->quote($value, $type);
	}

	function _filterSqlLocation()
	{
		require_once('class/DB/DBObj/Filter.php');
    	$sql = DBObj_Filter::perseQr($this->db, $this->_filterFieldsLocation, $this->_filterQuery);
		if (!$sql) {
			$sql = "";
		}
		$filter = &$this->filter;
		if (!in_array($filter['search_type'], array('all', 'location'))) {
			return false;
		}
		foreach ($filter as $field=>$value) {
			if ($value == "" || $value == 'all' || (is_array($value) && empty($value))) {
				continue;
			}
			switch($field) {
			case 'id_location_type':
				$sql .= " AND Location.id_type ".$this->_filterSqlParceValue($value);
				break;
			case 'id_mcpe_access_type':
			case 'id_company':
            case 'id_current_mcpe_status':
				$sql .= " AND Location.{$field} = " . $this->db->quote($value, 'integer');
				break;
            case 'raw_datafeeds':
            case 'data_feeds_office':
            case 'data_feeds_individual':
            case 'templates':
                    $sql .= " AND Location.{$field} = " . $this->db->quote($value);
                    break;
			case 'allow_mcpe_access':
				if (in_array($value, array('y', 'n'))) {
					$sql .= " AND Location.{$field} = '{$value}'";
				}
				break;
			}
		}
		return (empty($sql))?false:$sql;
	}

	function _filterSqlCompany()
	{
		require_once('class/DB/DBObj/Filter.php');
    	$sql = DBObj_Filter::perseQr($this->db, $this->_filterFieldsCompany, $this->_filterQuery);
		if (!$sql) {
			$sql = "";
		}
		$filter = &$this->filter;
		if (!in_array($filter['search_type'], array('all', 'company'))) {
			return false;
		}
		foreach ($filter as $field=>$value) {
			if ($value == "" || $value == 'all' || (is_array($value) && empty($value))) {
				continue;
			}
			switch($field) {
			case 'id_company_type':
				$sql .= " AND Company.id_type ".$this->_filterSqlParceValue($value);
				break;
			}
		}
		return (empty($sql))?false:$sql;
	}

	function _getWhere4GridLocation($opt = array())
    {
    	if (DB_OBJ_DEL_METHOD_LOGICAL == $this->delMethod){
			$where = " WHERE Location.deleted = 'n' ";
    	} else {
			$where = ' WHERE 1 ';
    	}
		$where .= ' ' .(string)$opt['addonWhereLocation'].' ';
		$where .= ' '.$this->_filterSqlLocation();
		return $where;
    }

	function _getWhere4GridCompany($opt = array())
    {
    	if (DB_OBJ_DEL_METHOD_LOGICAL == $this->delMethod){
			$where = " WHERE Company.deleted = 'n' ";
    	} else {
			$where = ' WHERE 1 ';
    	}
		$where .= ' ' .(string)$opt['addonWhereCompany'].' ';
		$where .= ' '.$this->_filterSqlCompany();
		return $where;
    }

	function getLocationById($id)
	{
		$sql = "
			SELECT *, IF(name <> '', name, CONCAT(mailing_address, ' - ', mailing_city, ', ', mailing_state, ' ', mailing_zip)) as title
			FROM {$this->db->tblLocation}
			WHERE id = ".$this->db->quote($id, 'integer')."
		";
		return $this->db->queryRow($sql, 0, null, true);
	}

	function isNRDSUniqueApplied($nrds_id, $id_owner = null)
	{
		require_once('app/Member/Member.php');
		return Member::isNRDSUniqueApplied($nrds_id, $id_owner, 'location');
	}

    function isFreeNrdsAtCompany($nrdsId, $companyId)
    {
        require_once('app/Member/Member.php');
        return Member::isFreeNrdsAtCompany($nrdsId, $companyId);
    }

    function getCompanyId()
    {
        $this->initFromDB();
        return $this->_data['id_company'];
    }
	
	function assignNrdsId($record_type)
	{
		require_once('app/Member/Member.php');		
        return Member::assignNrdsId($record_type);
	}

	public function isBlockedNrds($nrdsId)
    {
        require_once('app/Member/Member.php');		
        return Member::isBlockedNrds($nrdsId);
    }

	function assignManualyNrdsId($record_type, $nrds_id)
	{
		require_once('app/Member/Member.php');
		return Member::assignManualyNrdsId($record_type, $nrds_id);
	}

    public function isUniqueNrdsAtDictionary($nrdsId, $recordType = 'location', $idOwner = null)
    {
        require_once('app/Member/Member.php');
        return Member::isUniqueNrdsAtDictionary($nrdsId, $recordType, $idOwner);
    }

	function updateNrdsId($nrds_id, $companyId)
	{
		require_once('app/Member/Member.php');
		return Member::updateNrdsId($nrds_id, 'location', $companyId);
	}

    function clearNrdsId()
	{
		require_once('app/Member/Member.php');
		return Member::clearNrdsId('location');
	}

	function getTemplateIndividuals($id_location)
	{
		$sql = "
			SELECT
				Individual.id,
				CONCAT(Individual.first_name, ' ', Individual.middle_initial, IF (Individual.middle_initial = '' , '', '. '), Individual.last_name) as full_name
			FROM {$this->db->tblLocation2Individual} as Location2Individual
			JOIN {$this->tblIndividual} as Individual
				ON  Individual.id = Location2Individual.id_individual
				AND Location2Individual.id_parent = ".intval($id_location)."
		";
		return $this->db->queryAll($sql, 0, null, true);
	}
    
    public function getDMemberStatus4Select($type = null, $id_type = null)
    {
        return Member::getDMemberStatus4Select($type, $id_type);
    }
}