<?php

class Bazapg extends Model
{
	function Bazapg()
    {
        parent::Model();
		$this->pg = $this->load->database('pg', TRUE);
    }
	
	function post()
	{
		$sql = "SELECT * FROM stats where domain_id=7 ORDER BY data DESC LIMIT 100";			
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
		
	function get_stats($id)
	{
		$sql = "SELECT * FROM stats where domain_id=".$id." ORDER BY data DESC LIMIT 100";			
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function count_stats($id)
	{
		$sql = "SELECT count(domain_id) as ile FROM stats where domain_id=".$id." GROUP BY domain_id";			
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_pagination($id,$start,$limit)
	{
		//$sql = "select * from galleries where gal_cat_id=$cat_id LIMIT $start,$limit";
		$sql = "SELECT * FROM stats where domain_id=$id ORDER BY id DESC LIMIT $limit OFFSET $start";			
		$data = $this->pg->query($sql);
		return $data->result_array();
	}
	
	function get_stats_wykres($id,$date,$date2)
	{
		//$sql = "SELECT data FROM stats where domain_id=".$id." and data >= '".$date."'";			
		$sql = "SELECT * FROM get_stats_chart_daily(".$id.", ".$date.", ".$date2.") tab(ilosc bigint, data date);";
		$data = $this->pg->query($sql);
		$stats = $data->result_array();
		
		return $stats;
	}
	
	function update_wsadz($id,$str,$star_data)
	{
	
		$data['data_number'] = $star_data;					        
		$data['user_number'] = $str;					        
        $this->pg->WHERE('id', $id);
        return $this->pg->update('stats', $data);
	
	}
	
	function wsadz($sql)
	{
		$data = $this->pg->query($sql);	
		//return 0;
	}
	
	function get_data()
	{
		$sql = "SELECT id, ip, user_agent, resolution, data  FROM stats order by id;";
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;	
	}
	
	function get_stats_wykres_weekly($id,$go, $end)
	{
		$sql = "SELECT count(*) as rows FROM stats where domain_id=".$id." and data_number between '".$go."' and '".$end."' GROUP BY domain_id";			
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_monthly($id,$month)
	{
		$sql = "SELECT count(*) as rows FROM stats where domain_id=".$id." and data_number like '".$month."%' GROUP BY domain_id";
		//$sql = "SELECT * FROM get_stats_chart_monthly(".$id.", '".$month."') tab(ilosc bigint, data date);";			
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_yearly ($id,$year)
	{
		$sql = "SELECT count(*) as rows FROM stats where domain_id=".$id." and data_number like '".$year."%' GROUP BY domain_id";
		//$sql = "SELECT * FROM get_stats_chart_monthly(".$id.", '".$month."') tab(ilosc bigint, data date);";			
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_monthly_uniq($id,$go,$end)
	{
		$sql = "SELECT COUNT(*) as rows from (SELECT user_number as zlaczenie FROM stats where domain_id = ".$id." and data_number >= ".$go." and data_number < ".$end." group by zlaczenie) as tabela";			
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_yearly_uniq($id,$go,$end)
	{
		$sql = "SELECT COUNT(*) as rows from (SELECT user_number as zlaczenie FROM stats where domain_id = ".$id." and data_number >= ".$go." and data_number < ".$end." group by zlaczenie) as tabela";			
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_unig($id,$days)
	{
		//$sql = "SELECT COUNT(zlaczenie) as rows from (SELECT ip||resolution||user_agent as zlaczenie FROM stats where domain_id=".$id." and data = '".$date."' group by zlaczenie) as tabela";
		$sql = "SELECT * FROM get_stats_chart_daily_uniq(".$id.", '".$days."') tab(ilosc bigint, data date);";
		$data = $this->pg->query($sql);
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_unig_weekly($id,$go, $end)
	{
		$sql = "SELECT COUNT(*) as rows from (SELECT user_number as zlaczenie FROM stats where domain_id=".$id." and data_number between '".$go."' and '".$end."' group by zlaczenie) as tabela";
		$data = $this->pg->query($sql);
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_returning($id, $dni_u, $dni_r_min, $dni_r_max)
	{
		//$sql = "select COUNT(*) as rows from (SELECT ip||resolution||user_agent as zlaczenie FROM stats where domain_id=".$id." and data<='".$date_returning."' group by zlaczenie) as powracajacy, (SELECT ip||resolution||user_agent as zlaczenie2 FROM stats where domain_id=".$id." and data like '".$date_uniq."%' group by zlaczenie2) as unikatowi WHERE zlaczenie = zlaczenie2";
		$sql = "SELECT * FROM get_stats_chart_daily_returning(".$id.", '".$dni_u."', '".$dni_r_min."', '".$dni_r_max."') tab(ilosc bigint, data date);";
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_returning_wekly2($id, $dni_u, $dni_r_min,$dni_r_max)
	{
		$sql = "select COUNT(*) as rows from (SELECT user_number as zlaczenie FROM stats where domain_id=".$id." and data_number between '".$dni_r_min."' and  '".$dni_r_max."' group by zlaczenie) as powracajacy, (SELECT user_number as zlaczenie2 FROM stats where domain_id=".$id." and data_number = '".$dni_u."' group by zlaczenie2) as unikatowi WHERE zlaczenie = zlaczenie2";
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_returning_weekly($id,$date_uniq, $date_returning)
	{
		$sql = "select COUNT(*) rows from (SELECT CONCAT(ip,resolution,user_agent) zlaczenie FROM stats where domain_id=".$id." and data<='".$date_returning."' group by zlaczenie) powracajacy, (SELECT CONCAT(ip,resolution,user_agent) zlaczenie2 FROM stats where domain_id=".$id." and data like '".$date_uniq."%' group by zlaczenie2) unikatowi WHERE zlaczenie = zlaczenie2";
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_all_returning($id,$date)
	{
		$sql = "SELECT * FROM get_stats_chart_daily_all_returning(".$id.", '".$date."') tab(ilosc bigint, data date);";
		$data = $this->pg->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function user_new_pass($name,$haslo)
	{
		$data['password'] = $haslo;					        
        $this->pg->WHERE('name', $name);
        return $this->pg->update('user', $data);
	}
}

?>