<?php
class ModelLocatorAgency extends Model 
{
	//
	// Constants
	//
	// Type
    const TypeSales				 = 1;
    const TypeSupport			 = 2;
    // Status
    const StatusDeleted			 = 0;
    const StatusActive			 = 1;
    // Limit
    const QueryLimit			 = 4;


    //XML custom
    public function customMap($zone,$productline,$salesorganization,$type,$region) {
    
		
    	if ($type == 1) { //sales network


    		if ($zone == 1) { //international
    			
    			
    			if ($productline == 0) {
					$sql = "SELECT * FROM loc_agency a LEFT JOIN loc_agency_contact b ON a.agency_id = b.agency_id LEFT JOIN loc_agency_coverage c ON a.agency_id = c.agency_id WHERE a.status = '1' AND a.type='".(int)$type."' AND c.country_id <> '113' AND c.country_id <> '244' AND c.category_id <> '7' GROUP BY a.agency_id";
    			} else {
    				$sql = "SELECT * FROM loc_agency a LEFT JOIN loc_agency_contact b ON a.agency_id = b.agency_id LEFT JOIN loc_agency_coverage c ON a.agency_id = c.agency_id WHERE a.status = '1' AND a.type='".(int)$type."' AND c.category_id = '".$productline."' AND c.country_id <> '113' AND c.country_id <> '244' GROUP BY a.agency_id";
    			}
    			

    		} elseif ($zone == 2) {//italy
    			
    			if ($salesorganization == 0) {
    				$sql = "SELECT * FROM loc_agency a LEFT JOIN loc_agency_contact b ON a.agency_id = b.agency_id LEFT JOIN loc_agency_coverage c ON a.agency_id = c.agency_id WHERE a.status = '1' AND a.type='".(int)$type."' AND c.country_id = '113' AND organization_id <> '1' GROUP BY a.agency_id";
    			} else {
    				$sql = "SELECT * FROM loc_agency a LEFT JOIN loc_agency_contact b ON a.agency_id = b.agency_id LEFT JOIN loc_agency_coverage c ON a.agency_id = c.agency_id WHERE a.status = '1' AND a.type='".(int)$type."' AND c.organization_id = '".$salesorganization."' AND c.country_id = '113'  GROUP BY a.agency_id";
    			}


    		} elseif ($zone == 3) {
    			$sql = "SELECT * FROM loc_agency a LEFT JOIN loc_agency_contact b ON a.agency_id = b.agency_id LEFT JOIN loc_agency_coverage c ON a.agency_id = c.agency_id WHERE a.status = '1' AND a.type='".(int)$type."' AND a.agency_id = '56'  GROUP BY a.agency_id";
    		} elseif ($zone == 4) {
    			$sql = "SELECT * FROM loc_agency a LEFT JOIN loc_agency_contact b ON a.agency_id = b.agency_id LEFT JOIN loc_agency_coverage c ON a.agency_id = c.agency_id WHERE a.status = '1' AND a.type='".(int)$type."' AND a.agency_id = '177'  GROUP BY a.agency_id";
    		} elseif ($zone == 5) {
    			$sql = "SELECT * FROM loc_agency a LEFT JOIN loc_agency_contact b ON a.agency_id = b.agency_id LEFT JOIN loc_agency_coverage c ON a.agency_id = c.agency_id WHERE a.status = '1' AND a.type='".(int)$type."' AND a.agency_id = '169'  GROUP BY a.agency_id";
    		}


    	} else { //technical support
    		
    		if ($region == 0) {
    			if ($productline == 0) {
					$sql = "SELECT * FROM loc_agency a LEFT JOIN loc_agency_contact b ON a.agency_id = b.agency_id LEFT JOIN loc_agency_coverage c ON a.agency_id = c.agency_id WHERE a.status = '1' AND a.type='".(int)$type."'  AND c.country_id = '113'  GROUP BY a.agency_id";
    			} else {
    				$sql = "SELECT * FROM loc_agency a LEFT JOIN loc_agency_contact b ON a.agency_id = b.agency_id LEFT JOIN loc_agency_coverage c ON a.agency_id = c.agency_id WHERE a.status = '1' AND a.type='".(int)$type."'  AND c.country_id = '113' AND c.category_id = '".$productline."'  GROUP BY a.agency_id";
    			}
    		} else {
    			if ($productline == 0) {
					$sql = "SELECT * FROM loc_agency a LEFT JOIN loc_agency_contact b ON a.agency_id = b.agency_id LEFT JOIN loc_agency_coverage c ON a.agency_id = c.agency_id WHERE a.status = '1' AND a.type='".(int)$type."'  AND c.country_id = '113' AND c.region_id = '".$region."'  GROUP BY a.agency_id";
    			} else {
    				$sql = "SELECT * FROM loc_agency a LEFT JOIN loc_agency_contact b ON a.agency_id = b.agency_id LEFT JOIN loc_agency_coverage c ON a.agency_id = c.agency_id WHERE a.status = '1' AND a.type='".(int)$type."'  AND c.country_id = '113' AND c.category_id = '".$productline."' AND c.region_id = '".$region."'  GROUP BY a.agency_id";
    			}

    		}

    	}
    	
    	
    	//$query = $this->db->query($sql);
		//return $query->rows;
		return $sql;
	}


	// Get Agency
	public function getAgency($agency_id) 
	{
		$sql = "SELECT * FROM loc_agency WHERE agency_id = '" . (int)$agency_id . "' AND status = '1'";
		
		$query = $this->db->query($sql);
		
		return $query->row;
	}

	
	// SQL to query agencies
	protected function getSQL($data, $as_count = false)
	{
		$sql = ($as_count ? "SELECT COUNT(DISTINCT(a.agency_id)) AS total" : "SELECT a.agency_id AS agency_id");
		$sql .= " FROM loc_agency a";
		$sql .= " INNER JOIN loc_agency_coverage ac ON (a.agency_id = ac.agency_id)";	
		$where = " WHERE a.status = '1'";
	
		// Type
		if (!empty($data['type'])) 
		{
			$where .= " AND a.type = '" . (int)$data['type'] . "'";
		}
		
		// Region
		if (!empty($data['region_id'])) 
		{
			$where .= " AND ac.region_id = '" . (int)$data['region_id'] . "'";
		}
		
		// Country
		if (!empty($data['country_id'])) 
		{
			if ($data['country_id'] == ModelLocatorCountry::CountryInternational) 
			{
				$where .= " AND ac.country_id <> '" . ModelLocatorCountry::CountryItaly . "'";
				$where .= " AND ac.country_id <> '" . ModelLocatorCountry::CountryUK . "'";
				$where .= " AND ac.country_id <> '" . ModelLocatorCountry::CountryKorea . "'";
			}
			else {
				$where .= " AND ac.country_id = '" . (int)$data['country_id'] . "'";
			}
		}
	
		// Continent
		$sql .= " INNER JOIN loc_country c ON (ac.country_id = c.country_id)";
		if (!empty($data['continent_id'])) 
		{
			$where .= " AND c.continent_id = '" . (int)$data['continent_id'] . "'";
		}
		
		// Product line
		if (!empty($data['category_id'])) 
		{
			//$sql .= " LEFT JOIN loc_category_agency cat ON (a.agency_id = cat.agency_id)";
			$where .= " AND ac.category_id = '" . (int)$data['category_id'] . "'";
		}	
		
		// Sales organization
		if (!empty($data['organization_id'])) 
		{
			//$sql .= " LEFT JOIN loc_organization_agency o ON (a.agency_id = o.agency_id)";
			$where .= " AND ac.organization_id = '" . (int)$data['organization_id'] . "'";
		}
		
		if (!$as_count)
		{
			// Group by
			$where .= " GROUP BY a.agency_id";
		
			// Order
			$where .= " ORDER BY c.name ASC, a.name ASC";
		
			// Limit
			$where .= " LIMIT " . (int)$data['start'] . "," . ModelLocatorAgency::QueryLimit;
		}
			
		// Final query
		$sql .= $where;
		//print("<br>" . $sql);
		
		return $sql;
	}
	
	
	// Get List of Agencies
	public function getAgencies($data = array()) 
	{
		$sql = $this->getSQL($data);
		
		$result_data = array();
				
		$query = $this->db->query($sql);
	
		foreach ($query->rows as $result) 
		{
			$result_data[$result['agency_id']] = $this->getAgency($result['agency_id']);
		}

		return $result_data;
	}
	
	
	// Total number of agencies
	public function getTotalAgencies($data = array()) 
	{
		$sql = $this->getSQL($data, true);

		$query = $this->db->query($sql);
		
		if ($query->num_rows) {
			return $query->row['total'];
		}
		else {
			return 0;
		}
	}

	
	// Get Contacts
	public function getAgencyContacts($agency_id) 
	{

		$sql = "SELECT * FROM loc_agency_contact WHERE agency_id = '" . (int)$agency_id . "' AND status = '1'";
		
		$query = $this->db->query($sql);
		
		return $query;
	}
	
	
	// Get Product Line
	public function getAgencyProductLines($agency_id = "") 
	{
		// Validation
		if (!is_numeric($agency_id)) {
			return array();
		}
		
		$sql = "SELECT c.name FROM loc_category c INNER JOIN loc_agency_coverage ac ON (c.category_id = ac.category_id) WHERE ac.agency_id = '" . (int)$agency_id . "' AND c.status = '1'";
		
		$query = $this->db->query($sql);
		
		$results = array();
		
		if ($query->num_rows)
		{
			foreach ($query->rows as $result) 
			{
				$results[] = $result['name'];
			}
		}
		
		return $results;
	}
	
	
	// Get Sales Organization
	public function getAgencySalesOrganization($agency_id = "") 
	{
		// Validation
		if (!is_numeric($agency_id)) {
			return array();
		}
		
		$sql = "SELECT o.name FROM loc_organization o INNER JOIN loc_agency_coverage ac ON (o.organization_id = ac.organization_id) WHERE ac.agency_id = '" . (int)$agency_id . "' AND o.status = '1'";
		
		$query = $this->db->query($sql);
		
		$results = array();
		
		if ($query->num_rows)
		{
			foreach ($query->rows as $result) 
			{
				$results[] = $result['name'];
			}
		}
		
		return $results;
	}
}	
?>