<?php if ( ! defined('BASEPATH')) exit ('No direct script  allow'); 
class Location_model extends  CI_Model {

    private $table = "zip_codes_new";
	
	 
    public function get_zipcodes($params = array())
    {
        extract($params);
        $select     =  (isset($select))  ?   ($select)   ?   $select :   "*" :   "*";
        $where      =  (isset($where))   ?   $where      :   "";
        $zipcode    =   isset($zipcode)  ?   $zipcode    :   "";
        $county     =   isset($county)   ?   $county     :   "";
        $state      =   isset($state)    ?   $state      :   "";
        $city       =   isset($city)     ?   $city       :   "";
        $orderBy    =   isset($orderBy)  ?   $orderBy    :   "";
        $groupBy    =   isset($groupBy)  ?   $groupBy    :   "";
        $count      =   isset($count)    ?   $count      :   "";
		$join      =   isset($join)    ?   $join      :   "";
        $match      =  (isset($match))   ?   $match      :   "";

        $this->db->select($select);

        $this->db->from("zip_codes_new");

        if($where)
            $this->db->where($where);

        if($match)
            $this->db->where($match, null, false);

        if($zipcode)
            $this->db->where("zip_postal",$zipcode);

        if($county){
            if (isset($like) && $like)
                $this->db->like("county", $city, 'both');
            else
                $this->db->where("county",$county);
        }//..... end if() .....//

        if($state){
            if (isset($like) && $like)
                $this->db->like("state_name", $city, 'both');
            else
                $this->db->where("state_name",$state);
        }//..... end of if() .....//

        if($city) {

            if (isset($like) && $like)
                $this->db->like("city", $city, 'both');
            else
                $this->db->where("city", $city);

        }//.... end if() .....//


        if(isset($limit) && isset($offset) && $limit && $offset)
            $this->db->limit($limit, $offset);
        elseif(isset($limit) && $limit)
            $this->db->limit($limit);

        if($orderBy)
            $this->db->order_by($orderBy);

        if($groupBy)
            $this->db->group_by($groupBy);

        if($count)
            return $this->db->get()->num_rows();
        else
		
             return $this->db->get()->result();
    }//..... end of get_zipcodes() .....//
    
    /**
     * Get List of Major Cities.....
     */
    public function get_major_cities($city_id = "")
    {
        $table = $this->table;

        $this->db->select("$table.id as city_id,city");
        $this->db->from($table);
        if($city_id)
            $this->db->where("$table.id",$city_id);

        $this->db->join("major_cities","$table.id = major_cities.city_id","inner");
        $this->db->group_by("$table.city");
        return $this->db->get()->result();
    }//..... end of get_major_cities() .....//

    /**
     * return list of cities....
     */
    public function get_cities()
    {
        $this->db->select(array("id",'city'));
        $this->db->from($this->table);
        $this->db->group_by("city");
         return $this->db->get()->result();
    }


    public function get_counties()
    {
        $this->db->select(array("id",'county', 'county_code'));
        $this->db->from($this->table);
        $this->db->group_by("county");
         return $this->db->get()->result();
    }


    /**
     * return list of cities list....
     */
    public function get_states()
    {
        $this->db->select(array("id","state_name",'state_province'));
        $this->db->from($this->table);
        $this->db->group_by("state_name");
         return $this->db->get()->result();
    }//..... end of get_cities_list() .....//

    /**
     * @param string $county
     * Get County Populations....
     * @return population count.
     */
    public function get_county_population($county = "")
    {
        $this->db->select_sum("population");
        $this->db->from($this->table);
        $this->db->where("county",$county);
        $result = $this->db->get()->result();
        return (isset($result))? $result[0]->population: 0;
    }//..... end of get_county_population() ......//
    
    /**
     * Get State Name....
     */
    public function get_state_name($state_code)
    {
        $this->db->select("state_name");
        $this->db->from($this->table);
        $this->db->where("state_province",$state_code);
        $result = $this->db->get()->result();
        return (isset($result))? $result[0]->state_name : null;
    }//..... end of get_state_name() .....//

    public function get_county_populations($zip_code = ""){
        //$this->db->select("id,state_name,state_province,population");
        //,(SELECT COUNT(user_county_id) FROM user_counties WHERE id=county_code) AS agents
        $this->db->select('county_code AS id, SUM(population) AS value,COUNT(city) as cities');
        $this->db->select("county_full as county, state_name, fips, cord.coords as path, state_province");
        //$this->db->select_sum("population");
        $this->db->from($this->table);
        $this->db->join("counties_svg as cord", "cord.fip = " . $this->table . ".fips");

        if($zip_code)
            $this->db->where("zip_postal",$zip_code);
        $this->db->group_by("county_full");
        $this->db->group_by("state_name");
        $result =$this->db->get()->result();
        return $result;
    }

    public function get_county_populations_admin($zip_code = ""){

        //$this->db->select("$this->table.county_code AS id, SUM(population) AS value, (SELECT COUNT(user_county_id) FROM user_counties WHERE user_counties.county_code = " . $this->table . ".county_code) as total_agents");
        $this->db->select("$this->table.county_code AS id, SUM(population) AS value, (SELECT COUNT(user_state) FROM users WHERE user_county = zip_codes_new.county AND user_state = zip_codes_new.state_name) as total_agents");
        $this->db->select("county_full as county, state_name, fips, cord.coords as path, state_province");
        //$this->db->select_sum("population");
        $this->db->from($this->table, 'user_counties');
        $this->db->join("counties_svg as cord", "cord.fip = " . $this->table . ".fips");
        if($zip_code)
            $this->db->where("zip_postal",$zip_code);

        $this->db->group_by("county_full");
        $this->db->group_by("state_name");

        $result =$this->db->get()->result();
        //echo $this->db->last_query();
        return $result;
    }

    public function get_county_population_founder_members($zip_code = ""){

        $otherdb = $this->load->database('otherdb', TRUE);

        $otherdb->select("zip_codes_new.county_code AS id, SUM(population) AS value, (SELECT COUNT(work_state) FROM pre_launch_users WHERE work_county = zip_codes_new.county AND work_state = zip_codes_new.state_name) as total_agents");
        $otherdb->select("county_full as county, state_name, fips, cord.coords as path, state_province");
        //$otherdb->select('zip_codes_new.county_code AS id, SUM(population) AS value, county_full as county, state_name, fips, cord.coords as path,state_province, (SELECT COUNT(work_state) FROM pre_launch_users WHERE work_county = zip_codes_new.county AND work_state = zip_codes_new.state_name) as total_agents');
        $otherdb->from('zip_codes_new', 'user_counties');
        $otherdb->join("counties_svg as cord", "cord.fip = " . 'zip_codes_new' . ".fips");
        if($zip_code)
            $otherdb->where("zip_postal",$zip_code);

        $otherdb->group_by("county_full");
        $otherdb->group_by("state_name");

        $result =$otherdb->get()->result();
        //echo $this->db->last_query();
        return $result;
    }


}