<?php

class Baza extends Model
{
	function Baza()
    {
        parent::Model();
		$this->default = $this->load->database('default', TRUE);	
    }
	
	function google_region($domain_id)
	{
		$sql = "SELECT google_region from user_domain where id = ".$domain_id." limit 1";			
		$data = $this->default->query($sql);	
		$domain = $data->result_array();
		
		if(!empty($domain[0]['google_region']))
			return $domain[0]['google_region'];
		else
			return 'pl';
	}
	
	function get_prowizje($id)
	{	
		$sql = "SELECT count(*) as ile FROM user where afiliant_id='".$id."'";			
		$data = $this->default->query($sql);			
		$users = $data->result_array();
		
		$sql = "SELECT * FROM prowizje where (".$users[0]['ile']." >= od and ".$users[0]['ile']." <= do) ";			
		$data = $this->default->query($sql);	
		
		return $data->result_array();	
	}
	
	function get_all_users()
	{
		$this->default->select('id,');
		$data = $this->default->get('user');
		return $data->result_array();	
	}
	
	function get_all_masters($exclude_id_list=null)
	{
		$this->default->where_not_in('id', $exclude_id_list);
		$data = $this->default->get('master');
		return $data->result_array();				
	}
	
	function get_all_employees()
	{
		$data = $this->default->get('admin');
		return $data->result_array();			
	}
	
	function is_unique_master_name($name)
	{
		$data = null;
		$sql  = 'SELECT * FROM master WHERE name = ?';
		$query = $this->default->query($sql,array($name));
		
		if($query->num_rows() > 0)
			return false;
		else
			return true;			
	}
	
	function is_unique_employee_name($name)
	{
		$data = null;
		$sql  = 'SELECT * FROM admin WHERE name = ?';
		$query = $this->default->query($sql,array($name));
		
		if($query->num_rows() > 0)
			return false;
		else
			return true;		
	}
	
	function check_domain_owner($user_id, $domain_id)
	{
		$sql = "SELECT * from user_domain where user_id = ".$user_id." and id = ".$domain_id;			
		$data = $this->default->query($sql);	
		$domain = $data->result_array();
		return $domain;	
	}
	
	function superadmin_check_domain_owner($manager_id, $domain_id)
	{
		$sql = "SELECT * from user_domain, user where user.id=user_domain.user_id and user_domain.id = ".$domain_id." and manager_id=".$manager_id;			
		$data = $this->default->query($sql);	
		$domain = $data->result_array();
		return $domain;	
	}
	
	/*function superadmin_get_regions()
	{
		$sql = "SELECT * from user_domain group by google_region";			
		$data = $this->default->query($sql);	
		$domain = $data->result_array();
		return $domain;	
	}*/
	
	function user($login)
	{
		$sql = "SELECT name,password,id FROM user WHERE name='".$login."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	function master($login)
	{
		$sql 	= "SELECT name,password,id FROM master WHERE name='".$login."'";			
		$data 	= $this->default->query($sql);	
		$user 	= $data->result_array();
		return 	$user;					
	}
	
	function user_get_email($login)
	{
		$sql = "SELECT name, email FROM user, user_data WHERE user.name='".$login."' and user.id = user_data.user_id";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	
	function superadmin_user($login)
	{
		$sql = "SELECT name,password,id FROM admin WHERE name='".$login."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	
	function afiliant_user($email)
	{
		$sql = "SELECT nazwa,imie,nazwisko,pass,id FROM afilianci WHERE email='".$email."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	
	function check_user_pass($pass, $login)
	{
		$sql = "SELECT * FROM user WHERE name = '".$login."' AND password = '".$pass."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	
	function superadmin_check_user_pass($pass, $login)
	{
		$sql = "SELECT * FROM admin WHERE name = '".$login."' AND password = '".$pass."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	
	function master_check_user_pass($pass,$login)
	{
		$sql = "SELECT * FROM master WHERE name = '".$login."' AND password = '".$pass."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;			
	}	
	
	function afiliant_check_user_pass($pass, $email)
	{
		$sql = "SELECT * FROM afilianci WHERE email = '".$email."' AND pass = '".$pass."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	
	function check_id_and_password_afiliant($id, $pass)
	{
		$sql = "SELECT * FROM afilianci WHERE id = '".$id."' AND pass = '".$pass."'";			
		$data = $this->default->query($sql);	 
		return $data->result_array();
	}
			
	function get_user_login($pass, $id)
	{
		$sql = "SELECT name FROM user WHERE id = '".$id."' AND password='".$pass."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	
	function get_affiliant_name($id)
	{
		$sql = 'SELECT CONCAT(imie," ",nazwisko) AS name FROM afilianci WHERE id = '.$id;			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;		
	}
	function superadmin_get_user_login($pass, $id)
	{
		$sql = "SELECT name FROM admin WHERE id = '".$id."' AND password='".$pass."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	function master_get_user_login($pass,$id)
	{
		$sql = "SELECT name FROM master WHERE id = '".$id."' AND password='".$pass."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;		
	}
	
	function get_user_data($id)
	{
		$sql = "SELECT * FROM user_data WHERE user_id = '".$id."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	
	function get_superadmin_data($id)
	{
		$sql = "SELECT * FROM admin WHERE id = '".$id."'";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;			
	}
	
	function get_user_account($id)
	{
		$sql = "SELECT * FROM konta WHERE origin_id = ".$id;			
		$data = $this->default->query($sql);
		$result =  $data->result_array();
		if(isset($result[0]))
			return $result[0];
		else
			return null;	 
	}
	
	function get_super_admin_account($id)
	{
		$sql = "SELECT * FROM konta WHERE origin_id = ".$id;			
		$data = $this->default->query($sql);
		$result =  $data->result_array();
		return $result[0];		
	}
	
	function get_afiliant_account($id)
	{
		$sql = 'SELECT * FROM konta WHERE origin_id = '.$id.' AND origin_table = "afilianci"';			
		$data = $this->default->query($sql);
		$result =  $data->result_array();
		if(isset($result[0]))
			return $result[0];
		else
			return null;					
	}
	
	function superadmin_all_afiliants()
	{
		$sql = "SELECT id,nazwa,imie,nazwisko FROM afilianci order by nazwa";			
		$data = $this->default->query($sql);			
		return $data->result_array();
	}
		
	function get_affiliant_users($id)
	{
		$sql = "SELECT id,name,afiliant_id FROM user where afiliant_id='".$id."'";			
		$data = $this->default->query($sql);			
		return $data->result_array();
	}
	
	function get_affiliant_users_tydzien($id,$tydzien,$rok)
	{		
		$sql = "SELECT id,name,afiliant_id FROM user where afiliant_id='".$id."'";			
		$data = $this->default->query($sql);			
		$users = $data->result_array();
		$users_ids = array();
		
		foreach($users as $u)
		{
			$users_ids[] = $u['id'];
		}
		$liczba_userow = count($users_ids);
		//print_r($users_ids);
						
		$this->default->select('*');
		$this->default->from('user_domain');
		$this->default->where_in('user_domain.user_id', $users_ids);
		$this->default->where('keyword_price_per_week.week', $tydzien);
		$this->default->where('keyword_price_per_week.year', $rok);
		$this->default->group_by("keyword_price_per_week.keyword_id");
		$this->default->order_by("user_domain.domain");
		$this->default->join('user_domain_keyword', 'user_domain.id = user_domain_keyword.domain_id','left');
		$this->default->join('keyword_price_per_week', 'keyword_price_per_week.keyword_id = user_domain_keyword.id','left');
		 

		$query = $this->default->get();
		return $query->result_array();		
	}
	
	function get_affiliant_users_miesiac($id,$miesiac,$rok)
	{
		include_once('files/functions.php');		
		$weeks = weeks_start_end_array_in_one_year($rok);
		$payment_weeks = array();
		
		foreach($weeks as $w)
		{			
			$month = substr($w['start'],5,2);
			if($month==$miesiac)
			{
				$payment_weeks[] = $w['week_number'];	
			}
		}
		//print_r($payment_weeks);
		
		$sql = "SELECT id,name,afiliant_id FROM user where afiliant_id='".$id."'";			
		$data = $this->default->query($sql);			
		$users = $data->result_array();
		$users_ids = array();
		
		foreach($users as $u)
		{
			$users_ids[] = $u['id'];
		}
		$liczba_userow = count($users_ids);
		//print_r($users_ids);
						
		$this->default->select('domain, keyword, keyword_id, sum(keyword_price_per_week.price) as price, count(keyword) as payment_weeks');
		$this->default->from('user_domain');
		$this->default->where_in('user_domain.user_id', $users_ids);
		$this->default->where_in('keyword_price_per_week.week', $payment_weeks);
		$this->default->where('keyword_price_per_week.year', $rok);
		$this->default->group_by("keyword_price_per_week.keyword_id");
		$this->default->order_by("user_domain.domain");
		$this->default->join('user_domain_keyword', 'user_domain.id = user_domain_keyword.domain_id','left');
		$this->default->join('keyword_price_per_week', 'keyword_price_per_week.keyword_id = user_domain_keyword.id','left');
		 
		$query = $this->default->get();
		return $query->result_array();		
	}
	
	function get_affiliant_users_rok($id,$rok)
	{
		include_once('files/functions.php');		
				
		$sql = "SELECT id,name,afiliant_id FROM user where afiliant_id='".$id."'";			
		$data = $this->default->query($sql);			
		$users = $data->result_array();
		$users_ids = array();
		
		foreach($users as $u)
		{
			$users_ids[] = $u['id'];
		}
		$liczba_userow = count($users_ids);
		//print_r($users_ids);
						
		$this->default->select('domain, keyword, keyword_id, sum(keyword_price_per_week.price) as price, count(keyword) as payment_weeks');
		$this->default->from('user_domain');
		$this->default->where_in('user_domain.user_id', $users_ids);		
		$this->default->where('keyword_price_per_week.year', $rok);
		$this->default->group_by("keyword_price_per_week.keyword_id");
		$this->default->order_by("user_domain.domain");
		$this->default->join('user_domain_keyword', 'user_domain.id = user_domain_keyword.domain_id','left');
		$this->default->join('keyword_price_per_week', 'keyword_price_per_week.keyword_id = user_domain_keyword.id','left');
		 

		$query = $this->default->get();
		return $query->result_array();		
	}
	
	function get_affiliant_data($id)
	{
		$sql = "SELECT * FROM afilianci where id='".$id."'";			
		$data = $this->default->query($sql);			
		return $data->result_array();
	}
	
	function del_user_from_aff($id)
	{		
		$data['afiliant_id']=0;
		
        $this->default->WHERE('id', $id);
        return $this->default->update('user', $data);
	}
	
	function update_user_from_aff($id,$affiliant)
	{		
		$data['afiliant_id']=$affiliant;
		
        $this->default->WHERE('id', $id);
        return $this->default->update('user', $data);
	}	
	
	function get_free_users_for_aff()
	{
		$sql = "SELECT id,name FROM user where afiliant_id=0";			
		$data = $this->default->query($sql);			
		return $data->result_array();
	}
	
	function update_user_data($id,$user_id,$company_name,$contact_name,$tax_id,$website,$email,$city,$post_code,$street,$b_no,$a_no,$email_notification)
	{
			try
			{
				$this->db->trans_start();
						
					//query 1			
					$data['company_name']=$company_name;
					$data['contact_name']=$contact_name;
					$data['tax_id']=$tax_id;
					$data['website']=$website;
					$data['email']=$email;
					$data['city']=$city;
					$data['post_code']=$post_code;
					$data['street']=$street;
					$data['b_no']=$b_no;
					$data['a_no']=$a_no;			        
			        $this->default->WHERE('id', $id);
			        $this->default->update('user_data', $data);
					
					//query2
					$data2['email_notifications'] = $email_notification;
					$this->default->WHERE('origin_id', $user_id);
					$this->default->update('konta', $data2);
					
				
				$this->db->trans_complete();
				if ($this->db->trans_status() === FALSE)
				{
					$this->db->trans_rollback();
				    return FALSE;
				} 			
				else
				{
					$this->db->trans_commit();
					return TRUE;			
				}				
			}
			catch(Exception $e)
			{
				$this->db->trans_rollback();
				return FALSE;
			}
		$this->db->trans_commit();
		return TRUE;		
	}
	
	function superadmin_change_afiliant_data($post)
	{
		$data['nazwa'] = $post['nazwa'];
		$data['typ'] = $post['typ'];
		$data['imie'] = $post['imie'];
		$data['nazwisko'] = $post['nazwisko'];
		$data['pesel'] = $post['pesel'];
		$data['nip']  = $post['nip'];
		$data['regon'] = $post['regon'];
		$data['miasto'] = $post['miasto'];
		$data['ulica']  = $post['ulica'];
		$data['kod_pocztowy'] = $post['kod'];
		$data['numer_domu'] = $post['nr_domu'];
		$data['nr_mieszkania'] = $post['nr_mieszkania'];
		$data['telefon']  = $post['telefon'];
		$data['email'] = $post['email'];
		$data['uwagi']	 = $post['uwagi'];
		$data['active']	 = $post['active'];
		
		 $this->default->WHERE('id', $post['id']);
         $this->default->update('afilianci', $data);
		
	}
	function add_master($name,$password,$email)
	{
		$data['name'] 		= $name;
		$data['password'] 	= md5($password);
		$data['email']		= $email;
		$this->db->insert('master', $data); 		
	}

	function add_employee($name,$password,$email)
	{
			try
			{
				$this->db->trans_start();
				
				//query 1		
				$data['name'] 		= $name;
				$data['password'] 	= md5($password);
				$data['email']		= $email;
				$this->db->insert('admin', $data);
		
				$origin_id = $this->db->insert_id();

				//query 2
				$data2['origin_id'] 	=  $origin_id;
				$data2['origin_table'] 	=  'admin';
				$this->db->insert('konta', $data2); 
			
				$this->db->trans_complete();
				if ($this->db->trans_status() === FALSE)
				{
					$this->db->trans_rollback();
				    return FALSE;
				} 			
				else
				{
					$this->db->trans_commit();
					return TRUE;			
				}				
			}
			catch(Exception $e)
			{
				$this->db->trans_rollback();
				return FALSE;
			}
		$this->db->trans_commit();
		return TRUE;			
	}
		
	function delete_master($id)
	{
		try
		{
			$this->db->delete('master', array('id' => $id));
		}
		catch(Exception $e)
		{
			return FALSE;			
		}
		return TRUE;					
	}
	
	function delete_employee($id)
	{
		try
		{
			$this->db->delete('admin', array('id' => $id));
			$this->db->delete('konta', array('origin_id' => $id,'origin_table'=> 'admin'));
		
			if ($this->db->trans_status() === FALSE)
			{
				$this->db->trans_rollback();
			    return FALSE;
			} 			
			else
			{
				$this->db->trans_commit();
				return TRUE;			
			}				
		}
		catch(Exception $e)
		{
			$this->db->trans_rollback();
			return FALSE;
		}
		
		$this->db->trans_commit();
		return TRUE;
	}
	
	
	function is_valid_master_password($password,$master_user_id)
	{
		$data 		= null;
		$password   = md5($password);
		
		$sql   = 'SELECT password FROM master WHERE id = ? AND password = ?';
		$query = $this->default->query($sql,array($master_user_id,$password));
		
		if($query->num_rows() == 1)
			return true;
		else
			return false;			
	}
	
	function change_master_pass($new_password, $master_user_id)
	{
		$data['password'] = md5($new_password);					        
        $this->default->WHERE('id', $master_user_id);
        return $this->default->update('master', $data);		
	}
		
	function superadmin_add_afiliant($post)
	{
		//print_r($post);		
		 /*
		$data['nazwa'] = $post['nazwa'];
		$data['typ'] = $post['typ'];
		$data['imie'] = $post['imie'];
		$data['nazwisko'] = $post['nazwisko'];
		$data['pesel'] = $post['pesel'];
		$data['nip']  = $post['nip'];
		$data['regon'] = $post['regon'];
		$data['miasto'] = $post['miasto'];
		$data['ulica']  = $post['ulica'];
		$data['kod_pocztowy'] = $post['kod'];
		$data['numer_domu'] = $post['nr_domu'];
		$data['nr_mieszkania'] = $post['nr_mieszkania'];
		$data['telefon']  = $post['telefon'];
		$data['email'] = $post['email'];
		$data['uwagi']	 = $post['uwagi'];
        $data['pass']	= md5($post['email']);
        return $this->default->insert('afilianci', $data);
		*/
		
		
		try
		{
		
			$this->default->trans_start();	
					
				//queries
				 
				$data['nazwa'] 			= $post['nazwa'];
				$data['typ'] 			= $post['typ'];
				$data['imie']			= $post['imie'];
				$data['nazwisko'] 		= $post['nazwisko'];
				$data['pesel'] 			= $post['pesel'];
				$data['nip']  			= $post['nip'];
				$data['regon'] 			= $post['regon'];
				$data['miasto'] 		= $post['miasto'];
				$data['ulica']  		= $post['ulica'];
				$data['kod_pocztowy'] 	= $post['kod'];
				$data['numer_domu'] 	= $post['nr_domu'];
				$data['nr_mieszkania'] 	= $post['nr_mieszkania'];
				$data['telefon']  		= $post['telefon'];
				$data['email'] 			= $post['email'];
				$data['uwagi']	 		= $post['uwagi'];
		        $data['pass']			= md5($post['email']);		//na starcie haslem jest mail
		        $this->default->insert('afilianci', $data);
					
					
				$origin_id		=	$this->default->insert_id();
								
				//add account
				$data2 = array(
				               'origin_id'    => $origin_id,
				               'origin_table' => 'afilianci' 
				            );
				
				$this->db->insert('konta', $data2); 				
				
				
			$this->default->trans_complete();	
			if ($this->default->trans_status() === FALSE)
			{
				$this->default->trans_rollback();
			    return FALSE;
			} 			
			else
			{
				$this->default->trans_commit();
				return TRUE;			
			}
		}
		catch(Exception $e)
		{
			$this->default->trans_rollback();
			return FALSE;			
		}
		
		$this->default->trans_commit();
		return TRUE;		
	}
	
	function superadmin_set_region($domain_id, $google_region)
	{
		$data['google_region']=$google_region;
				        
        $this->default->WHERE('id', $domain_id);
        return $this->default->update('user_domain', $data);
	}	
	
	function get_domains($user_id)
	{
		$sql = "SELECT * ,COUNT(*) count_keywords FROM user_domain,user_domain_keyword WHERE user_domain.user_id='".$user_id."' and user_domain.id= user_domain_keyword.domain_id GROUP BY domain_id";			
		$data = $this->default->query($sql);	
		$domains = $data->result_array();
		return $domains;	
	}
	
	function superadmin_get_domains($user_type = null, $manager_id = null)
	{
		if($user_type == 'admin')
		{
			$sql = "SELECT * ,COUNT(*) count_keywords FROM user_domain,user_domain_keyword WHERE user_domain.id = user_domain_keyword.domain_id GROUP BY domain_id";
		}
		elseif ($user_type != 'admin' and $user_type != null and $manager_id != null)
		{
			$sql_tmp = "select user_domain.id as domain_id from user, user_domain where user_domain.user_id = user.id and user.manager_id = ".$manager_id."; ";
			$data_tmp = $this->default->query($sql_tmp);	
			$domains_tmp = $data_tmp->result_array();
			
			$domain_params = '';
			
			foreach ($domains_tmp as $domain)
			{
				$domain_params .=  $domain['domain_id']." ,";
			}
			
			$domain_params = substr($domain_params, 0, -2);
			if($domain_params == '') $domain_params = '-1';
			
			$sql = "SELECT *,COUNT(*) count_keywords FROM user_domain, user_domain_keyword WHERE user_domain.id = user_domain_keyword.domain_id AND domain_id IN (".$domain_params.") GROUP BY domain_id";
		}
		
		if(isset($sql) and $sql!=null)
		{
			$data = $this->default->query($sql);	
			$domains = $data->result_array();
			return $domains;		
		}
		else
		{
			return array();		
		}		
	}
	
	function get_domains_keyword($user_id, $domain_id)
	{
		$sql = "SELECT * ,COUNT(*) count_keywords FROM user_domain,user_domain_keyword WHERE user_domain.user_id='".$user_id."' and user_domain.id= user_domain_keyword.domain_id and user_domain.id=$domain_id GROUP BY domain_id";			
		$data = $this->default->query($sql);	
		$domains = $data->result_array();
		return $domains;	
	}
	
	function get_domain($domain_id)
	{
		$sql = "select * from user_domain_keyword WHERE domain_id=".$domain_id." order by id ASC";			
		$data = $this->default->query($sql);	
		$domains = $data->result_array();
		return $domains;	
	}
	
	function get_domain_key_position($domain_id)
	{		
		$date = date('Y-m-d');
		
		$sql = "select * from (SELECT date, keyword_id, domain_id, keyword, position FROM user_domain_keyword,user_domain_keywords_stats_".$this->google_region($domain_id)." WHERE domain_id=".$domain_id." and date <= '".$date."' and user_domain_keyword.id= user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id order by date  DESC) as tabela group by keyword_id";			
		
		$data = $this->default->query($sql);	
		$domains = $data->result_array();
		return $domains;	
	}
	
	function get_keyword_position_average($id,$domain_id)
	{		
		$day = date('d');
		$month = date('m');
		$year = date('Y');
		$end_date = date('Y-m-d');
		$start_date = mktime (0,0,0,$month,($day-6),$year);
		$start_date = strftime("20%y-%m-%d", $start_date);
		
		$sql = "SELECT * FROM user_domain_keywords_stats_".$this->google_region($domain_id)." WHERE keyword_id = '".$id."' and date between '".$start_date."' and '".$end_date."' ORDER BY date";						
		$data = $this->default->query($sql);	
		$keyword = $data->result_array();
		return $keyword;	
	}
	
	function get_doamin_name($domain_id)
	{
	 	$sql = "SELECT domain FROM user_domain WHERE id='".$domain_id."'";			
		$data = $this->default->query($sql);	
		$domain = $data->result_array();
		return $domain;
	}
	
	function get_keyword_position($domain_id)
	{
	 	$sql = "SELECT * FROM user_domain_keyword,user_domain_keywords_stats_".$this->google_region($domain_id)." WHERE domain_id='".$domain_id."' and user_domain_keyword.id= user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id GROUP BY keyword_id ";			
		$data = $this->default->query($sql);	
		$keyword = $data->result_array();
		return $keyword;
	}
	
	function get_keyword_position_data($domain_id, $keyword_id)
	{
		$day = date('d');
		$month = date('m');
		$year = date('Y');
		$end_date = date('Y-m-d');
		$start_date = mktime (0,0,0,$month,($day-45),$year);
		$start_date = strftime("20%y-%m-%d", $start_date);
		
		$sql = "SELECT * FROM user_domain_keyword,user_domain_keywords_stats_".$this->google_region($domain_id)." WHERE domain_id='".$domain_id."' and date  between '".$start_date."' and '".$end_date."' and user_domain_keyword.id= user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id and user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id=".$keyword_id." ORDER BY date desc limit 7";			
		$data = $this->default->query($sql);	
		$keyword = $data->result_array();
		return $keyword;
	}
	
	function get_keyword_position_data_daily($domain_id, $keyword_id, $data)
	{
		$sql = "SELECT * FROM user_domain_keyword,user_domain_keywords_stats_".$this->google_region($domain_id)." WHERE domain_id='".$domain_id."' and date='".$data."' and user_domain_keyword.id= user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id and user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id=".$keyword_id."";			
		$data = $this->default->query($sql);	
		$keyword = $data->result_array();
		return $keyword;
	}
	
	function get_keyword_position_data_weekly($domain_id, $keyword_id, $start_date, $end_date)
	{
		$sql = "SELECT  avg( position ) AS position FROM user_domain_keyword,user_domain_keywords_stats_".$this->google_region($domain_id)." WHERE domain_id='".$domain_id."' and date  between '".$start_date."' and '".$end_date."' and user_domain_keyword.id= user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id and user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id=".$keyword_id."";			
		$data = $this->default->query($sql);	
		$keyword = $data->result_array();
		return $keyword;
	}
	
	function get_keyword_position_data_monthly($domain_id, $keyword_id, $date)
	{
		$sql = "SELECT  avg( position ) AS position FROM user_domain_keyword,user_domain_keywords_stats_".$this->google_region($domain_id)." WHERE domain_id='".$domain_id."' and date like '".$date."%' and user_domain_keyword.id= user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id and user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id=".$keyword_id."";			
		$data = $this->default->query($sql);	
		$keyword = $data->result_array();
		return $keyword;
	}
	
	function get_keyword_position_data_quartely($domain_id, $keyword_id, $start_date, $end_date)
	{
		$sql = "SELECT  avg( position ) AS position FROM user_domain_keyword,user_domain_keywords_stats_".$this->google_region($domain_id)." WHERE domain_id='".$domain_id."' and date  between '".$start_date."' and '".$end_date."' and user_domain_keyword.id= user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id and user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id=".$keyword_id."";			
		$data = $this->default->query($sql);	
		$keyword = $data->result_array();
		return $keyword;
	}
	
	function get_keyword_position_data_yearly($domain_id, $keyword_id, $date)
	{
		$sql = "SELECT  avg( position ) AS position FROM user_domain_keyword,user_domain_keywords_stats_".$this->google_region($domain_id)." WHERE domain_id='".$domain_id."' and date like '".$date."%' and user_domain_keyword.id= user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id and user_domain_keywords_stats_".$this->google_region($domain_id).".keyword_id=".$keyword_id."";			
		$data = $this->default->query($sql);	
		$keyword = $data->result_array();
		return $keyword;
	}
	
	function get_stats_all()
	{
		$sql = "SELECT * FROM stats ORDER BY id limit 100";			
		$data = $this->default->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->default->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres($id,$date)
	{
		$sql = "SELECT data FROM stats where domain_id=".$id." and data >= '".$date."'";			
		$data = $this->default->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_weekly($id,$go, $end)
	{
		$sql = "SELECT count(*) rows FROM stats where domain_id=".$id." and data >= '".$go."' and data < '".$end."' GROUP BY domain_id";			
		$data = $this->default->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_unig($id,$date)
	{
		$sql = "SELECT COUNT(zlaczenie) rows from (SELECT CONCAT(ip,resolution,user_agent) zlaczenie FROM stats where domain_id=".$id." and data like '".$date."%' group by zlaczenie) tabela";
		$data = $this->default->query($sql);
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_unig_weekly($id,$go, $end)
	{
		$sql = "SELECT COUNT(zlaczenie) rows from (SELECT CONCAT(ip,resolution,user_agent) zlaczenie FROM stats where domain_id=".$id." and data >= '".$go."' and data < '".$end."' group by zlaczenie) tabela";
		$data = $this->default->query($sql);
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_returning($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->default->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->default->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function get_stats_wykres_all_returning($id,$date)
	{
		$sql = "SELECT COUNT(zlaczenie) rows from (SELECT CONCAT(ip,resolution,user_agent) zlaczenie FROM stats where domain_id=".$id." and data<='".$date."' group by zlaczenie) tabela";
		$data = $this->default->query($sql);	
		$stats = $data->result_array();
		return $stats;
	}
	
	function user_new_pass($name,$haslo)
	{
		$data['password'] = $haslo;					        
        $this->default->WHERE('name', $name);
        return $this->default->update('user', $data);
	}
	
}

?>