<?php

class Baza extends Model
{
	function Baza()
    {
        parent::Model();
		$this->default = $this->load->database('default', TRUE);	
    }

    function del_user($user_id = null)
    {
        $sql = "SELECT * from user_domain where user_id='".$user_id."'";
		$data = $this->default->query($sql);
		$domains = $data->result_array();

        foreach($domains as $d)
        {
            $sql = "SELECT * from user_domain_keyword where domain_id='".$d['id']."'";
            $data = $this->default->query($sql);
            $keywords = $data->result_array();            

            foreach($keywords as $k)
            {
                $sql = "delete from user_domain_keywords_stats_com where keyword_id='".$k['id']."'";
                $data = $this->default->query($sql);

                $sql = "delete from user_domain_keywords_stats_pl where keyword_id='".$k['id']."'";
                $data = $this->default->query($sql);
            }

            $sql = "delete from user_domain_keyword where domain_id='".$d['id']."'";
            $data = $this->default->query($sql);
        }
        
        $sql = "delete from user_domain where user_id='".$user_id."'";
        $data = $this->default->query($sql);

         $sql = "delete from user where id='".$user_id."'";
         $data = $this->default->query($sql);

         $sql = "delete from users_umowy where user_id='".$user_id."'";
         $data = $this->default->query($sql);

         $sql = "delete from user_data where user_id='".$user_id."'";
         $data = $this->default->query($sql);

         $sql = "delete from konta where origin_id='".$user_id."' and origin_table='user'";
         $data = $this->default->query($sql);
    }
	
	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_user_email($id)
	{
		$sql = "SELECT email FROM user_data where user_id = '".$id."' ";
		$data = $this->default->query($sql);

		return $data->result_array();
	}

    function get_afiliant_email($id)
	{
		$sql = "SELECT email FROM afilianci where id = '".$id."' ";
		$data = $this->default->query($sql);

		return $data->result_array();
	}
	
	function add_adwords_stats($stats)
	{		
		$sql = "SELECT adwords_customer_id from user_data where adwords_customer_id = ".$stats['customerid']." limit 1";			
		$data = $this->default->query($sql);	
		$adwords = $data->result_array();
		$data = null;
		
		if(!empty($adwords))
		{
			$adwords = null;
			$sql = "SELECT adwords_customer_id from adwords_stats where adwords_customer_id = '".$stats['customerid']."' and date='".trim($stats['date'])."' limit 1";			
			
			$data = $this->default->query($sql);	
			$adwords = $data->result_array();
			unset($data);
			
			if(empty($adwords))
			{						
				$data['adwords_customer_id'] = $stats['customerid'];
				$data['date'] = trim($stats['date']);
				$data['campaign_name'] = trim($stats['acctname']);
				$data['currency'] = trim($stats['currCode']);
				$data['imps'] = $stats['imps'];
				$data['clicks'] = $stats['clicks'];
				$data['cost'] = $stats['cost'];
				$data['avg-position'] = $stats['pos'];		
				$this->db->insert('adwords_stats', $data);
			}
		}	 		
	}
	
	function get_adwords_data($user_id=null)
	{
		$sql = "SELECT adwords_customer_id from user_data where user_id = '".$user_id."' limit 1";			
		$data = $this->default->query($sql);	
		$awords = $data->result_array();
		
		if(!empty($awords))
		{
			$sql = "SELECT sum(cost) as koszt_adwords from adwords_stats where 	adwords_customer_id  = '".$awords[0]['adwords_customer_id']."'";			
			$data = $this->default->query($sql);	
			return $data->result_array();
		}
	}
	
	function get_adwords_last_7_days($user_id=null)
	{
		$sql = "SELECT adwords_customer_id from user_data where user_id = '".$user_id."' limit 1";			
		$data = $this->default->query($sql);	
		$awords = $data->result_array();
		
		if(!empty($awords))
		{
			$sql = "SELECT * from adwords_stats where adwords_customer_id  = '".$awords[0]['adwords_customer_id']."' order by date desc limit 7";			
			$data = $this->default->query($sql);	
			return $data->result_array();
		}
	}
	
	function get_adwords_total($user_id=null)
	{
		$sql = "SELECT adwords_customer_id from user_data where user_id = '".$user_id."' limit 1";			
		$data = $this->default->query($sql);	
		$awords = $data->result_array();
		
		if(!empty($awords))
		{
			$sql = "SELECT min(date) as date,sum(imps) as imps, sum(clicks) as clicks from adwords_stats where adwords_customer_id  = '".$awords[0]['adwords_customer_id']."'";			
			$data = $this->default->query($sql);	
			return $data->result_array();
		}
	}
	
	function get_adwords_factures_data($user_id)
	{
		$sql = "SELECT sum(brutto) as doladnowano_adwords from faktury_sem where user_id  = ".$user_id."";			
		$data = $this->default->query($sql);	
		return $data->result_array();
	}
	
	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_prowizje_sem()
	{
		$sql = "SELECT * FROM prowizje_sem";
		$data = $this->default->query($sql);

		return $data->result_array();
	}
	
	function affiliant_get_typ_rozliczenia($id)
	{
		$sql = "SELECT typ FROM afilianci where id='".$id."' ";			
		$data = $this->default->query($sql);	
		
		return $data->result_array();
	}
	
	function afiliant_check_bank_account($id)
	{
		$sql = "SELECT nr_konta FROM afilianci where id = '".$id."'";			
		$data = $this->default->query($sql);
		return $data->result_array();	
	}
	
	function afiliant_get_data($id)
	{
		$sql = "SELECT * FROM afilianci where id = '".$id."'";			
		$data = $this->default->query($sql);
		return $data->result_array();	
	}
	
	function affiliant_get_money_from_factures($id)
	{
		//pobierz klientow afilianta
		$sql = "SELECT user.id as user_id, adwords_prowizja FROM user,user_data where afiliant_id = '".$id."' and user.id = user_data.user_id";
		$data = $this->default->query($sql);
		$users = $data->result_array();
		//print_r($users);

		if(count($users)==0) return 0;
		
		//pobierz rozliczenia afilianta
		$sql = "SELECT id FROM afilianci_rozliczenia where afiliant_id = '".$id."'";			
		$data = $this->default->query($sql);
		$rozliczenia = $data->result_array();
		
		/*$sem_rozliczone = '';
		$seo_rozliczone = '';
		
		
		//pobierz juz rozliczone id_faktur
		if(count($rozliczenia) > 0)
		{
			foreach($rozliczenia as $r)
			{
				
			}
		} */
		
		$sem = null;
		$seo = null;
	  	
	 	foreach($users as $u)
		{
			$sql = "SELECT id,user_id,netto FROM faktury_seo where id NOT IN(select faktura_id from afilianci_faktury_rozliczone where typ='SEO') and  user_id = '".$u['user_id']."' and zaplacono=1 and zatwierdzono=1";			
			$data = $this->default->query($sql);
			$data = $data->result_array();
			
			if(!empty($data))
				$seo[]= $data;			
			
			$sql = "SELECT id,user_id,netto FROM faktury_sem where id NOT IN(select faktura_id from afilianci_faktury_rozliczone where typ='SEM') and   user_id = '".$u['user_id']."'  and zaplacono=1 and zatwierdzono=1";			
			$data = $this->default->query($sql);
			$data = $data->result_array();

            $c = count($data);
            //echo "-".$c."-";
            for($i=0;$i<$c;$i++)
            {
                $data[$i]['prowizja_networds_sem'] = $u['adwords_prowizja'];
            }
			
			if(!empty($data))
				$sem[]= $data;			
		}
		
        //print_r($sem);

		$data_money = array('sem'=>$sem,'seo'=>$seo);
		
		//print_r($data->result_array());
		//print_r($data_money);
		return $data_money;	
	}
	
	function get_all_users()
	{
		/*
		$this->default->select('id,');
		$data = $this->default->get('user');
		return $data->result_array();
		*/
		$sql = "SELECT DISTINCT u.id,u.name,u.password,u.manager_id,u.afiliant_id,u.active,u.umowa,
		               ud.id as user_data_id,ud.company_name,ud.contact_name,ud.tax_id,ud.website,ud.email,ud.city,ud.post_code,
					   ud.street, ud.b_no, ud.a_no, ud.adwords_customer_id, ud.dk_miasto, ud.dk_ulica, ud.dk_kod_pocztowy, ud.dk_nr_domu, ud.dk_nr_mieszkania, ud.dk_telefon
				FROM user JOIN user AS u JOIN user_data as ud ON u.id = ud.user_id
                ORDER BY u.afiliant_id";
		$data = $this->default->query($sql);	
		$domain = $data->result_array();
		return $domain;			
	}
	
	function get_all_afiliants()
	{
		$sql	=	"SELECT * FROM afilianci";
		$data 	= 	$this->default->query($sql);	
		$domain = 	$data->result_array();
		return $domain;		
	}
	function get_all_masters($exclude_id_list=null)
	{
		$this->default->where('type',1);
		$this->default->where_not_in('id', $exclude_id_list);
		$data = $this->default->get('admin');
		return $data->result_array();				
	}
	
	function get_all_employees()
	{
		$this->default->where('type',2);
		$data = $this->default->get('admin');
		return $data->result_array();			
	}
	function get_employees_by_type($type) 
	{
		$this->default->where_in('type',$type);
		$data = $this->default->get('admin');
		return $data->result_array();
	}
	function get_active_employees()
	{
		$this->default->where('type',2);
		$this->default->where('active',1);
		$data = $this->default->get('admin');
		return $data->result_array();			
	}
	function get_active_employee_accounts()
    {
        $employees = $this->get_active_employees();
        $data      = array();

        if(is_array($employees) && count($employees) > 0)
        foreach($employees as $k=>$v)
            $data[] = $this->get_super_admin_account($v['id']);

        return $data;
    }
	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 is_unique_client_name($name,$without_id=null)
	{
		$data = null;
		$sql  = 'SELECT * FROM user WHERE name = ? AND id != ?';
		$query = $this->default->query($sql,array($name,$without_id));
		
		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 admin WHERE name='".$login."' AND type='1'";			
		$data 	= $this->default->query($sql);	
		$user 	= $data->result_array();
		return 	$user;					
	}
	function get_users_without_manager_id()
	{
		$sql = "SELECT * FROM user WHERE manager_id=0";			
		$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 afiliant_get_email($email)
	{
		$sql = "SELECT * FROM afilianci WHERE email='".$email."'";
		$data = $this->default->query($sql);
		$afiliant = $data->result_array();
		return $afiliant;
	}
	
	function superadmin_user($login)
	{
		$sql = "SELECT name,password,id,type,active FROM admin WHERE name='".$login."' and zwolniony=0";
		$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."' AND active=1";			
		$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."' and zwolniony=0";
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	
	function master_check_user_pass($pass,$login)
	{
		$sql = "SELECT * FROM admin WHERE name = '".$login."' AND password = '".$pass."' AND type=1";			
		$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."' and active=1";
		$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(nazwa," ",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 admin WHERE id = '".$id."' AND password='".$pass."' AND type=1";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;		
	}
	
	function get_users()
	{
		$sql = "SELECT * FROM user";			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		return $user;	
	}
	function get_user($id)
	{
		$sql = "SELECT * FROM user WHERE id=".$id;			
		$data = $this->default->query($sql);	
		$user = $data->result_array();
		if(isset($user[0]))
			return $user[0];
		else
			return FALSE;
	}
	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_superadmin_users($id)
	{
		$sql = "SELECT * FROM user WHERE manager_id = '".$id."'";			
		$data = $this->default->query($sql);	
		$result = $data->result_array();
		return $result;	
	}
	
	function get_user_account($id)
	{
		$sql = "SELECT * FROM konta WHERE origin_id = ".$id." and origin_table = 'user'";
		$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." AND origin_table = 'admin'";			
		$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($id,$name,$password=null,$active)
	{
		$data = null;
		if(!is_null($password))
		{
			$data['password']		=	md5($password);
		}
		$data['name']				=	$name;
		$data['active']				=	$active;
		
        $this->default->where('id', $id);
		return $this->default->update('user', $data);
	}
	
	function update_user_data($id,$user_id,$company_name,$contact_name,$tax_id,$website,$email,$city,$post_code,$street,$b_no,$a_no,$dk_m=null,$dk_u=null,$dk_k=null,$dk_nd=null,$dk_nm=null,$dk_t=null)
	{			
		//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;					
		$data['dk_miasto']=$dk_m;
		$data['dk_ulica']=$dk_u;
		$data['dk_kod_pocztowy']=$dk_k;
		$data['dk_nr_domu']=$dk_nd;
		$data['dk_nr_mieszkania']=$dk_nm;
		$data['dk_telefon']=$dk_t;
		
        $this->default->where('id', $id);
		return $this->default->update('user_data', $data);
	}
	
	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']);
         return $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,$type,$utrzymanie,$rabat,$active=0,$users=null)
	{
			try
			{
				$this->db->trans_start();
				
				//query 1		
				$data['name'] 		= $name;
				$data['password'] 	= md5($password);
				$data['email']		= $email;
				$data['type']		= $type;
				$data['utrzymanie']	= $utrzymanie;
				$data['rabat']		= $rabat;
				$data['active']		= $active;
				$this->db->insert('admin', $data);
		
				$origin_id = $this->db->insert_id();

				//query 2 - insert row to konta table
				$data2['origin_id'] 	=  $origin_id;
				$data2['origin_table'] 	=  'admin';
				$this->db->insert('konta', $data2); 
				
				//query3 - update manager_id in users table
				if(!is_null($users) && count($users)>0 && ($active == 1))
				{
					foreach($users as $k=>$v)
					{
						$data3['manager_id'] = $origin_id;					        
						$this->db->WHERE('id', $v);
						$this->db->update('user', $data3);							
					}
				}
			
				$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 get_history_users($employee_id)
	{
		//STEPS:
		//jesli user ma historie (history_users != null)- zwróc jego historie 
		//else: pobierz id-iki klientów pracownika i usuń z nich te, które należa do historii dowolnego pracownika
		$employee	=  $this->get_superadmin_data($employee_id);	//edytowany pracownik
		$employee	= $employee[0];
		
		if(!empty($employee['history_users']))	
		{
			return unserialize($employee['history_users']);				
		}	
		else
		{
			$employee_customers	= $this->get_superadmin_users($employee_id);	//klienci pracownika
			$employees			= $this->get_all_employees();
			$users_in_history   = array();										//klienci znajdujacy sie juz w historiach pracowników
			foreach($employees as $k=>$v)
			{
				if(!empty($v['history_users']))
				{
					$users_in_history = array_merge($users_in_history,unserialize($v['history_users']));
				}
			}
			
			$result = array();
			foreach($employee_customers as $k=>$v)
				if(!in_array($v['id'],$users_in_history))
					$result[] = $v['id'];
			
			return 	$result;		
		}
		 
	}
	 function get_affiliant_users_for_afiliant_list($id)
	 {
	  $sql = "SELECT company_name,contact_name FROM user, user_data where user.afiliant_id='".$id."' and user.id = user_data.user_id order by company_name";   
	  $data = $this->default->query($sql);   
	  return $data->result_array();
	 }	
	function edit_employee($id,$name,$password,$email,$type,$utrzymanie,$rabat,$active=0,$zwolniony=0,$clients=null,$new_manager_id=null)
	{
			try
			{
				$this->db->trans_start();
				//get current data
				$employee							=  $this->get_superadmin_data($id);	//edytowany pracownik
				$employee							= $employee[0];
				
				//$employee_customers					= $this->get_superadmin_users($id);	//klienci pracownika
				
				//wyciagniecie pól - id;
				//$employee_customers_ids = array();
				//foreach($employee_customers as $k=>$v)
				//	$employee_customers_ids[] = $v['id'];
					 
		
				
				//jesli zmianiamy aktywacje user-a na nieaktywny, to jego uzytkowników(serializowana tablica id-ków) 
				//zapisujemy w polu "history_users" i przepisujemy do innego pracownika
				//print_r(serialize($employee_customers_ids));
				//exit();
				
						//jesli byl aktywny i teraz zmiana na nieaktywny
						//$data['history_users'] = ($active == 0) ? serialize($employee_customers_ids) : null;
						$data['history_users'] = ($active == 0) ? serialize($this->get_history_users($id)) : null;
						$this->db->WHERE('id', $id);
						$this->db->update('admin', $data);
						
				
				
				//query 1		
				$data['name'] 		= $name;
				if(!empty($password))						//zmiana hasła tylko jak zostało przekazane
					$data['password'] 	= md5($password);
					
				$data['email']		= $email;
				$data['type']		= $type;
				$data['utrzymanie']	= $utrzymanie;
				$data['rabat']		= $rabat;
				$data['active']		= $active;
				$data['zwolniony']  = $zwolniony;
				
				if($data['active'] == 1)
					$data['history_users']	=	null;
					
				$this->db->WHERE('id', $id);
				$this->db->update('admin', $data);
		
		
				if($active == 1)
				{
					//aktualizacja klientów pod pracownikiem
					//1 - wyczysc obecnych klientów pod pracownikiem
					$data2['manager_id']	=	0;
					$this->db->where('manager_id',$id);
					$this->db->update('user',$data2);
					
					//2 - dodaj pracownika do wskazanych klientów
					if(!is_null($clients) && count($clients)>0)
					{
						$data2 = null;
						$data2['manager_id']	=	$id;
						$this->db->where_in('id',$clients);
						$this->db->update('user',$data2);
					}
				}
				elseif(!is_null($new_manager_id))
				{
					$data2['manager_id']	=	$new_manager_id;
					$this->db->where('manager_id',$id);
					$this->db->update('user',$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 edit_afiliant($id,$nazwa,$typ,$imie,$nazwisko,$pesel,$nip,$regon,$miasto,$ulica,$kod_pocztowy,$numer_domu,$nr_mieszkania
                           ,$dk_miasto,$dk_ulica,$dk_kod_pocztowy,$dk_nr_domu,$dk_nr_mieszkania,$dk_telefon,$nr_konta,$telefon,$email
                           ,$pass,$uwagi,$active,$umowa,$dokumenty_firma,$dokumenty_osoba_prywatna)
    {
        try
        {

				$data['nazwa'] 					= $nazwa;
				$data['typ']					= $typ;
				$data['imie']					= $imie;
				$data['nazwisko']				= $nazwisko;
				$data['pesel']					= $pesel;
				$data['nip']					= $nip;
				$data['regon']					= $regon;
				$data['miasto']					= $miasto;
				$data['ulica']					= $ulica;
				$data['kod_pocztowy']			= $kod_pocztowy;
				$data['numer_domu']				= $numer_domu;
				$data['nr_mieszkania']			= $nr_mieszkania;
				$data['dk_miasto']				= $dk_miasto;
				$data['dk_ulica']				= $dk_ulica;
				$data['dk_kod_pocztowy']		= $dk_kod_pocztowy;
				$data['dk_nr_domu']				= $dk_nr_domu;
				$data['dk_nr_mieszkania']		= $dk_nr_mieszkania;
				$data['dk_telefon']				= $dk_telefon;
				$data['nr_konta']				= $nr_konta;
				$data['telefon']				= $telefon;
				$data['email']					= $email;

				if(!empty($pass))						//zmiana hasła tylko jak zostało przekazane
					$data['pass'] 	= md5($pass);

				$data['uwagi']						= $uwagi;
				$data['active']						= $active;
				$data['umowa']						= $umowa;
				$data['dokumenty_firma']			= $dokumenty_firma;
				$data['dokumenty_osoba_prywatna']	= $dokumenty_osoba_prywatna;

				$this->db->where('id', $id);
				return $this->db->update('afilianci', $data);

        }
        catch (Exception $e)
        {
            return FALSE;
        }

    }
	function edit_afiliant_and_his_clients($id,$nazwa,$typ,$imie,$nazwisko,$pesel,$nip,$regon,$miasto,$ulica,$kod_pocztowy,$numer_domu,$nr_mieszkania,$dk_miasto,
                                           $dk_ulica,$dk_kod_pocztowy,$dk_nr_domu,$dk_nr_mieszkania,$dk_telefon,$nr_konta,$telefon,$email,$pass,$uwagi,$active,
                                           $umowa,$dokumenty_firma,$dokumenty_osoba_prywatna,$clients=null)
	{
			try
			{
				$this->db->trans_start();
				
				//query 1
				$data['nazwa'] 					= $nazwa;
				$data['typ']					= $typ;
				$data['imie']					= $imie;
				$data['nazwisko']				= $nazwisko;
				$data['pesel']					= $pesel;
				$data['nip']					= $nip;
				$data['regon']					= $regon;
				$data['miasto']					= $miasto;
				$data['ulica']					= $ulica;
				$data['kod_pocztowy']			= $kod_pocztowy;
				$data['numer_domu']				= $numer_domu;
				$data['nr_mieszkania']			= $nr_mieszkania;
				$data['dk_miasto']				= $dk_miasto;
				$data['dk_ulica']				= $dk_ulica;
				$data['dk_kod_pocztowy']		= $dk_kod_pocztowy;
				$data['dk_nr_domu']				= $dk_nr_domu;
				$data['dk_nr_mieszkania']		= $dk_nr_mieszkania;
				$data['dk_telefon']				= $dk_telefon;
				$data['nr_konta']				= $nr_konta;
				$data['telefon']				= $telefon;
				$data['email']					= $email;
			
				if(!empty($pass))						//zmiana hasła tylko jak zostało przekazane
					$data['pass'] 	= md5($pass);
					
				$data['uwagi']						= $uwagi;
				$data['active']						= $active;
				$data['umowa']						= $umowa;
				$data['dokumenty_firma']			= $dokumenty_firma;
				$data['dokumenty_osoba_prywatna']	= $dokumenty_osoba_prywatna;
				
				$this->db->where('id', $id);
				$this->db->update('afilianci', $data);
											
				 											
											
				//query2 - czyszczenie userów afilianta
				$data2['afiliant_id']				=	0;
				$this->db->where('afiliant_id',$id);
				$this->db->update('user',$data2);	
				
				//query3 - update userów afilianta
				if(!is_null($clients) && count($clients)>0)
				{
						$data2 = null;
						$data2['afiliant_id']	=	$id;
						$this->db->where_in('id',$clients);
						$this->db->update('user',$data2);			
				}														
													
				
									
				
			}
			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 admin WHERE id = ? AND password = ? AND type=1';
		$query = $this->default->query($sql,array($master_user_id,$password));
		
		if($query->num_rows() == 1)
			return true;
		else
			return false;			
	}

    function is_valid_ksiegowosc_password($password,$ksiegowosc_id)
	{
		$data 		= null;
		$password   = md5($password);

		$sql   = 'SELECT password FROM admin WHERE id = ? AND password = ? AND type=3';
		$query = $this->default->query($sql,array($ksiegowosc_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('admin', $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 set_domain_activity($domain_id, $active) 
	{
		$data['active']		=	$active;
        $this->default->WHERE('id', $domain_id);
        return $this->default->update('user_domain', $data);		
	}
	function get_domains($user_id)
	{
		$sql = "SELECT * ,COUNT(*) count_keywords,user_domain.active AS user_domain_active 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";
			$sql = "SELECT ud.id,ud.domain,ud.user_id,ud.google_region,ud.active,ud.umowa,udk.domain_id,COUNT(*) count_keywords FROM user_domain AS ud,user_domain_keyword AS udk WHERE ud.id = udk.domain_id GROUP BY udk.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);
	}

	function superadmin_new_pass($name,$haslo)
	{
		$data['password'] = $haslo;					        
        $this->default->WHERE('name', $name);
        return $this->default->update('admin', $data);
	}

    function afiliant_new_pass($email,$haslo)
	{
		$data['pass'] = $haslo;
        $this->default->WHERE('email', $email);
        return $this->default->update('afilianci', $data);
	}

	function activate_afiliant($md5_afiliant_id_plus_email)
	{
		$sql = "SELECT id, CONCAT(id,email) as tmp FROM afilianci";
		$data_tmp = $this->default->query($sql);	
		$domains_tmp = $data_tmp->result_array();
				
		$afiliant_id = null;
				
		foreach($domains_tmp as $key=>$value)
		{
			if(md5($value['tmp']) == $md5_afiliant_id_plus_email)
			{
				$afiliant_id = 	$value['id'];
				break;
			}
		}
		if($afiliant_id == null)
			return FALSE;
			
		//aktywuj
		$data['active']	=	1;
		$this->default->WHERE('id', $afiliant_id);
        return $this->default->update('afilianci', $data);		
	}
	//------------------------------------------------------------------------------------------------
	function get_admin_account($id)
	{
		$sql = "SELECT * FROM konta WHERE origin_id = ".$id." and origin_table = 'user'";			
		$data = $this->default->query($sql);
		$result =  $data->result_array();
		if(isset($result[0]))
			return $result[0];
		else
			return null;	 
	}
	//------------------------------------------------------------------------------------------------
	function get_admin_manager_id($admin_id)
	{
		$sql = "SELECT * FROM user WHERE id = ".$admin_id;			
		$data = $this->default->query($sql);
		$result =  $data->result_array();
		//print_r($result);
		if(isset($result[0]))
			return $result[0]['manager_id'];
		else
			return null;	
	}
	//------------------------------------------------------------------------------------------------
	function get_client_contracts($client_id)
	{
		$sql = "SELECT um.id,um.user_id,um.domain_id,
		               (SELECT ud.domain FROM user_domain ud WHERE ud.id = um.domain_id) AS domain,   
		               um.sciezka,um.data_wystawienia,
					   um.data_akceptacji,um.active
				FROM users_umowy AS um WHERE um.user_id = ".$client_id;			
		$data = $this->default->query($sql);
		$result =  $data->result_array();	
		return $result; 	
	}
	//------------------------------------------------------------------------------------------------	
	function get_networds()
	{
		$sql = "SELECT * FROM networds";	
		$data = $this->default->query($sql);
		$result =  $data->result_array();
		if(isset($result[0]))
			return $result[0];
		else
			return false;
	}
	//------------------------------------------------------------------------------------------------
	function edit_networds($networds)
	{
		$data = array();
		foreach($networds as $k=>$v)
		{
			if($k != 'id')
				$data[$k] = $v;
		}
			
        $this->default->WHERE('id', $networds['id']);
        return $this->default->update('networds', $data);				
	}
	//------------------------------------------------------------------------------------------------
	function get_user_contract_by_domain_id($domain_id,$user_id)
	{
		$sql = "SELECT * FROM users_umowy WHERE user_id='".$user_id."' and domain_id='".$domain_id."' and active=1";
		$data = $this->default->query($sql);
		return  $data->result_array();
	}

    function get_user_contract($contract_id)
	{
		$sql = "SELECT * FROM users_umowy WHERE id=".$contract_id;	
		$data = $this->default->query($sql);
		$result =  $data->result_array();
		if(isset($result[0]))
			return $result[0];
		else
			return false;		
	}

    function get_user_contract_authorized($contract_id,$user_id)
	{
		$sql = "SELECT * FROM users_umowy WHERE id='".$contract_id."' and user_id='".$user_id."'";
		$data = $this->default->query($sql);
		$result =  $data->result_array();
		if(isset($result[0]))
			return $result[0];
		else
			return false;
	}
	//------------------------------------------------------------------------------------------------
	function get_email_aliases()
	{
		$sql 	= "SELECT * FROM kategorie";	
		$data 	= $this->default->query($sql);
		$result = $data->result_array();
		return $result ;			
	}
	//------------------------------------------------------------------------------------------------
	function get_email_alias($id)
	{
		$sql 	= "SELECT * FROM kategorie WHERE id=".$id;	
		$data 	= $this->default->query($sql);
		$result = $data->result_array();
		if(isset($result[0]))
			return $result[0];
		else	
			return FALSE;			
	}
	//------------------------------------------------------------------------------------------------
	function edit_email_alias($id,$alias_email)
	{
		$data['alias_email']	=	$alias_email;
        $this->default->WHERE('id', $id);
        return $this->default->update('kategorie', $data);				
	}
	//------------------------------------------------------------------------------------------------
    function get_prowizje_settings($id=null)
    {
        if(!empty($id))
             $this->default->where('id', $id);
        $data = $this->default->get('prowizje');

        if(!empty($id) && !empty($data))
        {
            $result = $data->result_array();
            return $result[0];
        }
        else
            return $data->result_array();
    }
    //------------------------------------------------------------------------------------------------
    function edit_prowizje_settings($id,$od,$do,$procent)
    {
		$data['od']      =	$od;
        $data['do']      =	$do;
        $data['procent'] = $procent;
        
        $this->default->WHERE('id', $id);
        return $this->default->update('prowizje', $data);

    }
    //------------------------------------------------------------------------------------------------
    function get_prowizje_sem_settings($id=null)
    {
        if(!empty($id))
             $this->default->where('id', $id);
        $data = $this->default->get('prowizje');

        if(!empty($id) && !empty($data))
        {
            $result = $data->result_array();
            return $result[0];
        }
        else
            return $data->result_array();
    }
    //------------------------------------------------------------------------------------------------
    function edit_prowizje_sem_settings($id,$procent)
    {
        $data['procent'] = $procent;
        $this->default->WHERE('id',$id);
        return $this->default->update('prowizje',$data);
    }
    //------------------------------------------------------------------------------------------------
}
?>