<?php
define_class(__FILE__, 'NRDSStatictic');
require_once('class/DB/DBObj.php');

class NRDSStatictic extends DBObj
{
	var $tableName = 'NRDS';
	
	function NRDSStatictic()
	{
		$opt = array(
			'portability' =>  MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL ^ MDB2_PORTABILITY_FIX_CASE,
		);
		$this->msdb = &MDB2::factory(OLD_SITE_DB_DSN, $opt);;
		if (PEAR::isError($this->msdb)) {
			$this->raiseError($this->msdb->getMessage());
		}
		$this->msdb->setFetchMode(MDB2_FETCHMODE_ASSOC);
	}
	
	function getAgentDublicates()
	{
		
		$sql = "
			SELECT agent.nrds_id, agent.agent_id, person.first_name, person.last_name, person.email
			FROM mcpe.dbo.agent as agent
			LEFT JOIN person ON agent.person_id = person.person_id
			WHERE agent.nrds_id IS NOT NULL
				AND (
					SELECT COUNT(*) FROM mcpe.dbo.agent as agent2 WHERE agent2.nrds_id = agent.nrds_id
				) > 1
			ORDER BY agent.nrds_id
		";
		$res = $this->msdb->queryAll($sql);
		$this->_sqlError($res);
		return $res;
	}
	
	function getBrokersDublicates()
	{
		
		$sql = "
			SELECT 
				broker.nrds_id, 
				broker.broker_id, 
				broker.bkr_company, 
				broker.bkr_address, 
				broker.bkr_city, 
				broker.bkr_state, 
				broker.bkr_phone, 
				broker.bkr_fax,
				broker.bkr_zip
			FROM mcpe.dbo.broker as broker
			WHERE broker.nrds_id IS NOT NULL
				AND (
					SELECT COUNT(*) FROM mcpe.dbo.broker as broker2 WHERE broker2.nrds_id = broker.nrds_id
				) > 1
			ORDER BY broker.nrds_id
		";
		$res = $this->msdb->queryAll($sql);
		$this->_sqlError($res);
		return $res;
	}
	
	function getAgentsVSBrokersDublicates()
	{
		
		$sql = "
			SELECT 
				broker.nrds_id, 
				broker.broker_id, 
				broker.bkr_company, 
				broker.bkr_address, 
				broker.bkr_city, 
				broker.bkr_state, 
				broker.bkr_phone, 
				broker.bkr_fax,
				broker.bkr_zip,
				agent.nrds_id, 
				agent.agent_id, 
				person.first_name, 
				person.last_name, 
				person.email
			FROM mcpe.dbo.broker as broker
			JOIN mcpe.dbo.agent as agent
				ON agent.nrds_id = broker.nrds_id
			LEFT JOIN person 
				ON agent.person_id = person.person_id
			WHERE broker.nrds_id IS NOT NULL
			ORDER BY broker.nrds_id
		";
		$res = $this->msdb->queryAll($sql);
		$this->_sqlError($res);
		return $res;
	}
	
	function getIDFragmentationRes($min_id, $max_id)
	{
		$min_id = intval($min_id);
		$max_id = intval($max_id);
		$sql = "
			SELECT DISTINCT broker.nrds_id as nrds_id
			FROM mcpe.dbo.broker as broker
			LEFT JOIN mcpe.dbo.agent as agent
			     ON agent.nrds_id = broker.nrds_id
			WHERE 
				broker.nrds_id >= {$min_id} AND broker.nrds_id <= {$max_id} 
				AND agent.nrds_id IS NULL 
			    AND broker.nrds_id IS NOT NULL

			UNION 

			SELECT DISTINCT agent.nrds_id as nrds_id
			FROM mcpe.dbo.agent as agent
			WHERE 
				agent.nrds_id >= {$min_id} AND agent.nrds_id <= {$max_id} 
				AND agent.nrds_id IS NOT NULL
			ORDER BY nrds_id
		";
		$res = $this->msdb->query($sql);
		$this->_sqlError($res);
		return $res;
	}
}