<?php

class Sys extends Controller {

	function Sys()
	{
		parent::Controller();
    	$this->load->model('Dbmysql');
    	$this->load->model('Functions');
		$this->load->helper('url');
		$this->config->load('katalog_config');
    	$this->load->model('Kategorie');
    	$this->load->model('Lokality');
    	$this->load->model('Firma');
    	$this->load->model('Timer');
		$this->data['words'] = $this->config->item('words');
		$this->data['URI'] = $this->config->item('URI');
		$this->data['domains'] = explode('.', $_SERVER['HTTP_HOST']);
		$this->Timer->start();
	}

	function index()
	{
		echo 'Testy SYS';
		$query = $this->Dbmysql->db->query('SELECT COUNT(*) FROM firma WHERE status = 0');
  	$x = $query->result_array();print_r($x);
	}
	
	function odpovol()
	{
    $this->Dbmysql->db->query('UPDATE firma SET status = 1 WHERE status = 0');
  }
  
  function povolsmthg()
  {/*
    $limit = $this->uri->segment(3);
    if($limit > 0)
    {
      $query = $this->Dbmysql->db->query('SELECT * FROM okecStrom3');
  		foreach($query->result_array() as $v)
  		{
  			$this->Dbmysql->db->query('UPDATE firma SET status = 1 WHERE id IN (SELECT firma_id FROM firmakategoria WHERE kategoria_id = '.$v['id'].') AND status = 0 ORDER BY LENGTH(popis) DESC LIMIT '.$limit);
  		}
		}*/
  }
		
	function makeCool()
	{
		$query = $this->Dbmysql->db->query('SELECT * FROM okecStrom3');
		foreach($query->result_array() as $v)
		{
			$this->Dbmysql->db->query('UPDATE okecStrom3 SET cool_name = "'.$this->Functions->MakeCool($v['new_name']).'" WHERE id = '.$v['id']);
		}
	}
	
	function kategfirma1()
	{
		//$query = $this->Dbmysql->db->query('SELECT * FROM okecStrom2');
		$query = $this->Dbmysql->db->query('SELECT * FROM okecSpolu');
		$r1 = $query->result_array();
		$final = array();$x = 0;
		foreach($r1 as $v1)
		{
			$final[] = $v1['final'];			
			$query = $this->Dbmysql->db->query('SELECT id FROM firma WHERE okec IN (SELECT okec_id FROM okecStrom2 WHERE id = '.$v1['kid'].')');
			$r2 = $query->result_array();
			foreach($r2 as $v2)
			{
				$this->Dbmysql->db->query('INSERT INTO firmakategoria SET firma_id = '.$v2['id'].', kategoria_id = '.$v1['kid']);
			}
			//$this->Dbmysql->db->query('SELECT * FROM firma WHERE okec = "'.$this->Functions->MakeCool($v['new_name']).'" WHERE id = '.$v['id']);
		}//print_r(array_unique($final));
		foreach(array_unique($final) as $v1)
		{
			$query = $this->Dbmysql->db->query('SELECT id FROM firma WHERE okec IN (SELECT okec_id FROM okecStrom2 WHERE id = '.$v1.')');
			$r2 = $query->result_array();
			foreach($r2 as $v2)
			{
				$this->Dbmysql->db->query('INSERT INTO firmakategoria SET firma_id = '.$v2['id'].', kategoria_id = '.$v1);
				$x++;
			}
		}echo $x;
	}
	
	function kategfirma2()
	{
		$query = $this->Dbmysql->db->query('SELECT * FROM okecStrom3');
		$r1 = $query->result_array();
		$x = 0;
		foreach($r1 as $v1)
		{		
			$query = $this->Dbmysql->db->query('SELECT id FROM firma WHERE okec IN (SELECT okec_id FROM okecStrom2 WHERE id = '.$v1['id'].')');
			$r2 = $query->result_array();
			foreach($r2 as $v2)
			{
				$this->Dbmysql->db->query('INSERT INTO firmakategoria SET firma_id = '.$v2['id'].', kategoria_id = '.$v1['id']);
				$x++;
			}
			//$this->Dbmysql->db->query('SELECT * FROM firma WHERE okec = "'.$this->Functions->MakeCool($v['new_name']).'" WHERE id = '.$v['id']);
		}//print_r(array_unique($final));
		echo $x;
	}
	
	function urls()
	{
		$query = $this->Dbmysql->db->query('SELECT * FROM okecStrom3');
		$r1 = $query->result_array();$urls = '';
		foreach($r1 as $v1)
		{		
			$urls .= $v1['cool_name'].'.katalog.loc ';
		}
		echo $urls;
	}
	
	function nezaradeny()
	{
		$query = $this->Dbmysql->db->query('SELECT id FROM firma WHERE id NOT IN (SELECT firma_id FROM firmakategoria)');
		$r1 = $query->result_array();
		echo count($r1);
		$query = $this->Dbmysql->db->query('SELECT id FROM okecStrom3 WHERE id NOT IN (SELECT parent_id FROM okecStrom3)');
		$r2 = $query->result_array();
		foreach($r1 as $v)
		{
			$sql = 'INSERT INTO firmakategoria SET kategoria_id = '.$r2[rand(0, count($r2)-1)]['id'].', firma_id = '.$v['id'];
			$this->Dbmysql->db->query($sql);
		}
	}
	
	function firmaLok()
	{
		$query = $this->Dbmysql->db->query('SELECT id, old_id FROM lokality');
		$r1 = $query->result_array();$i = 0;
		foreach($r1 as $v1)
		{
			$query = $this->Dbmysql->db->query('SELECT id FROM firma WHERE okres = "'.$v1['old_id'].'"');
			$r2 = $query->result_array();
			foreach($r2 as $v2)
			{
				$this->Dbmysql->db->query('INSERT INTO firmalokalita SET firma_id = '.$v2['id'].', lokalita_id = '.$v1['id']);
			}
		}
		echo $i.' vlozenych zaznamov';
		exit;		
	}
	
	function indexovanieKat()
	{
		$query = $this->Dbmysql->db->query('SELECT id FROM okecStrom3');
		$r1 = $query->result_array();
		foreach($r1 as $v1)
		{
			$kategorie = $this->Kategorie->getAllPodkategorieIds($v1['id']);
			$kategorie[] = $v1['id'];	
			$query = $this->Dbmysql->db->query('SELECT firma_id FROM firmakategoria WHERE kategoria_id IN ('.implode($kategorie, ', ').')');
			$r2 = $query->result_array();
			$index = '';
			foreach($r2 as $v2)
			{
				$index .= $v2['firma_id'].',';
			}
			$this->Dbmysql->db->query('DELETE FROM katalogindex WHERE kat_lok = "'.$v1['id'].'_0"');
			$this->Dbmysql->db->query('INSERT INTO katalogindex SET kat_lok = "'.$v1['id'].'_0", ids = "'.substr($index, 0, -1).'"');
		}		
		echo '<hr>Indexovanie kategorii prebehlo uspesne';
	}
	
	function indexovanieLok()
	{
		$query = $this->Dbmysql->db->query('SELECT id FROM lokality');
		$r1 = $query->result_array();
		foreach($r1 as $v1)
		{
			$lokality = $this->Lokality->getAllPodlokalityIds($v1['id']);
			$lokality[] = $v1['id'];
			$query = $this->Dbmysql->db->query('SELECT firma_id FROM firmalokalita WHERE lokalita_id IN ('.implode($lokality, ', ').')');
			$r2 = $query->result_array();
			$index = '';
			foreach($r2 as $v2)
			{
				$index .= $v2['firma_id'].',';
			}
			$this->Dbmysql->db->query('DELETE FROM katalogindex WHERE kat_lok = "0_'.$v1['id'].'"');
			$this->Dbmysql->db->query('INSERT INTO katalogindex SET kat_lok = "0_'.$v1['id'].'", ids = "'.substr($index, 0, -1).'"');
		}
		echo '<hr>Indexovanie lokalit prebehlo uspesne';
	}
	
	function lokalitaOprava()
	{
		$query = $this->Dbmysql->db->query('SELECT * FROM locality');
		$r1 = $query->result_array();
		foreach($r1 as $v)
		{
			$this->Dbmysql->db->query('UPDATE lokality SET old_id = "'.$v['localita_id'].'" WHERE cool_name = "'.$v['cool_name'].'"');
		}
	}
	
	function rating()
	{
		$query = $this->Dbmysql->db->query('SELECT id FROM firma');
		$r1 = $query->result_array();
		foreach($r1 as $v1)
		{
			$query = $this->Dbmysql->db->query('SELECT COUNT(*) as pocet, SUM(hodnoceni) as suma FROM firmahodnoceni WHERE verificated <> 0 AND firma_id = '.$v1['id']);
			$r2 = $query->result_array();
			if((int)$r2[0]['pocet'] > 0)$rating = $r2[0]['suma'] / $r2[0]['pocet'];
			else $rating = 0;
			$this->Dbmysql->db->query('UPDATE firma SET rating = '.$rating.' WHERE id = '.$v1['id']);
		}
		echo '<hr>Rating prebehol uspesne';
	}
	
	function ratingtest()
	{
		//$this->Dbmysql->db->query('UPDATE firma SET rating = 2415 WHERE id = 146887');
	}
	
	function firmamailov()
	{
		$query = $this->Dbmysql->db->query('SELECT COUNT(*) as pocet FROM firma WHERE email <> ""');
		$r2 = $query->result_array();
		echo $r2[0]['pocet'];
	}
	
	function firmatitle()
	{
		$query = $this->Dbmysql->db->query('SELECT id,okec,okec2 FROM firma');
		$r1 = $query->result();
		foreach($r1 as $v1)
		{
			$query = $this->Dbmysql->db->query('SELECT * FROM okec WHERE id = "'.$v1->okec.'" OR id = "'.$v1->okec2.'"');
			$r2 = $query->result();
			if(count($r2) > 0)$this->Dbmysql->db->query('UPDATE firma SET title = "'.$r2[0]->okec.'" WHERE id = '.$v1->id);
		}
	}
	
	function firmatitle2()
	{
		$query = $this->Dbmysql->db->query('SELECT id,popis,okec,okec2 FROM firma');
		$r1 = $query->result();
		foreach($r1 as $v1)
		{
			$popis = explode('|', $v1->popis);
			if(count($popis) > 0)
			{
				shuffle($popis);$i = 0;
				while(strlen($popis[0]) > 50 and $i < 3)
				{
					shuffle($popis);$i++;
				}
				//echo $i.' : '.strlen($popis[0]).' : '.$popis[0].'<hr>';
				$this->Dbmysql->db->query('UPDATE firma SET title = "'.$popis[0].'" WHERE id = '.$v1->id);
			}
		}
	}
	
	function firmapopis()
	{
		$query = $this->Dbmysql->db->query('SELECT id,popis,okec,okec2 FROM firma WHERE LENGTH(popis) < 10');
		$r1 = $query->result();
		foreach($r1 as $v1)
		{
			$query = $this->Dbmysql->db->query('SELECT * FROM okec WHERE id = "'.$v1->okec.'" OR id = "'.$v1->okec2.'"');
			$r2 = $query->result();//print_r($r2);exit;
			if(count($r2) > 0)
			{
				$popis = $v1->popis;
				foreach($r2 as $v2)$popis .= ' | '.$v2->okec;
				$this->Dbmysql->db->query('UPDATE firma SET popis = "'.$popis.'" WHERE id = '.$v1->id);
			}
		}
	}
	
	function xlocality()
	{
		$handle = fopen("./data/ciselniky/cokres.cw", "r");
		if ($handle) {
			while (!feof($handle)) {
				$okresy[] = explode('-', iconv("WINDOWS-1250", "UTF-8", fgets($handle, 4096)));
			}
		fclose($handle);
		}
		$handle = fopen("./data/ciselniky/ckraj.cw", "r");
		if ($handle) {
			while (!feof($handle)) {
				$kraje[] = explode('-', iconv("WINDOWS-1250", "UTF-8", fgets($handle, 4096)));
			}
		fclose($handle);
		}

		foreach($kraje as $k1 => $v1)
		{echo $v1[1];
			$v1[1] = trim(str_replace('HlavnĂ­ mÄ›sto ', '', $v1[1]));
			$this->Dbmysql->db->query(
				"INSERT INTO locality set 
					id = '".$v1[0]."', 
					parent_id = '0', 
					locality = '".$v1[1]."', 
					cool_name = '".$this->Functions->MakeCool($v1[1])."'");
			foreach($okresy as $k2 => $v2)
			{
				$v2[1] = trim(str_replace('Okres ', '', $v2[1]));
				$v2[1] = trim(str_replace('Obvod ', '', $v2[1]));
				if(substr($v2[0], 0, 2) == $v1[0])$this->Dbmysql->db->query( 
					"INSERT INTO locality set id = '".$v2[0]."', 
						parent_id = '".$v1[0]."', 
						locality = '".$v2[1]."', 
						cool_name = '".$this->Functions->MakeCool($v2[1])."'");
			}
		}
	}

	function okec()
	{
		$handle = fopen("./data/ciselniky/cokec5.cw", "r");
		if ($handle) {
			while (!feof($handle)) {
				$okec = explode('-', iconv("WINDOWS-1250", "UTF-8", fgets($handle, 4096)));
				$this->Dbmysql->db->query("INSERT INTO okec set id = '".$okec[0]."', okec = '".trim($okec[1])."'");
			}
		fclose($handle);
		}
	}

	function firmalocality()
	{
		$query = $this->Dbmysql->db->query('SELECT id, okres FROM firma');
		$res = $query->result();
		if(count($res) > 0)
			foreach($res as $k => $v)
				$this->Dbmysql->db->query('INSERT INTO firmalocality SET firma_id = '.$v->id.', locality_id ="'.$v->okres.'"');
	}
	
	function firmaAlocality()
	{
		$query = $this->Dbmysql->db->query('SELECT firma_id FROM firmalokalita');
		$res = $query->result_array();//print_r($res);exit;$ids = array();
		foreach($res as $v)
		{
			$ids[$v['firma_id']] = $v['firma_id'];
		}//echo implode($ids, ',');exit;
		$query = $this->Dbmysql->db->query('SELECT COUNT(id) as poc FROM firma WHERE id NOT IN ('.implode($ids, ',').')');
		$res = $query->result();print_r($res);exit;
		if(count($res) > 0)
			foreach($res as $k => $v)
				$this->Dbmysql->db->query('INSERT INTO firmalocality SET firma_id = '.$v->id.', locality_id ="'.$v->okres.'"');
	}

	function csv()
	{
		header('Content-Type: text/html; charset=windows-1250');
		$inRow = $row = 0;
		$handle = fopen("./data/Vystup1.csv", "r");
		while (($data = fgetcsv($handle, 100000, ";")) !== FALSE) {
		if($row > 0){if($row > 3)break;
			$num = count($data);
			foreach($data as $v)echo $v.' | ';
			if((!empty($data[43]) or !empty($data[42]) or !empty($data[41]) or !empty($data[44])) and $num == 54)
			{//echo $data[29].'<br>';
				$sql = "INSERT INTO firma SET
				ico = '".$this->Dbmysql->escape_string($data[0])."',
				dic = '".$this->Dbmysql->escape_string($data[1])."',
				nazev = '".$this->Dbmysql->escape_string($data[2])."',
				obec = '".$this->Dbmysql->escape_string($data[4])."',
				ulica = '".$this->Dbmysql->escape_string($data[5])."',
				psc = '".$this->Dbmysql->escape_string($data[6])."',
				okres = '".trim($data[9])."',
				kraj = '".$data[10]."',
				okec = ".(int)$data[29].",
				tel = '".$this->Dbmysql->escape_string($data[41])."',
				fax = '".$this->Dbmysql->escape_string($data[42])."',
				email = '".$this->Dbmysql->escape_string($data[43])."',
				www = '".$this->Dbmysql->escape_string($data[44])."',
				ulica_post = '".$this->Dbmysql->escape_string($data[7])."',
				posta = '".$this->Dbmysql->escape_string($data[8])."',
				okec2 = ".(int)$this->Dbmysql->escape_string($data[30]).",
				popis = '".$this->Dbmysql->escape_string($data[45])."',
				uri = '".$this->Functions->MakeCool($data[2])."'";
				//$query = $this->Dbmysql->db->query(iconv("WINDOWS-1250", "UTF-8", $sql));
				$inRow++;
			}
		}
		$row++;
		}
		echo "Zaznamov celkom: $row , vlozenych zaznamov: $inRow ";
		fclose($handle);
	}

	function firmaUri()
	{
		$query = $this->Dbmysql->db->query('SELECT COUNT(*) as pocet FROM firma WHERE uri = ""');
		$r = $query->result_array();
		print_r($r);
		$query = $this->Dbmysql->db->query('SELECT nazev, id FROM firma WHERE uri = "" LIMIT 93000');
		foreach($query->result_array() as $v)
		{
			$sql = 'UPDATE firma SET uri = "'.$this->Functions->MakeCool($v['nazev']).'" WHERE id = '.$v['id'];
			$this->Dbmysql->db->query($sql);
		}
	}
	
	function showLocality ($parent = 0, $level = 0, $arr)
	{
		$query = $this->Dbmysql->db->query('SELECT id, okres FROM firma');
		$res = $query->result();
		if(count($res) > 0)
			foreach($res as $k => $v)
				$this->Dbmysql->db->query('INSERT INTO firmalocality SET firma_id = '.$v->id.', locality_id ="'.$v->okres.'"');
	}

	function showOkec ()
	{
		if(!empty($_POST))
		{//print_r($_POST);exit;
			$this->Dbmysql->db->query('INSERT INTO kategorie SET 
			cool_name = "'.$this->Functions->MakeCool($_POST['kategoria']).'",
			name = "'.$_POST['kategoria'].'"');
			$insert_id = mysql_insert_id();
			foreach($_POST['okec'] as $k => $v)
			{
				$this->Dbmysql->db->query('INSERT INTO okeckategorie SET okec_id = "'.$v.'", kategoria_id = '.$insert_id);
			}
		}
		$query = $this->Dbmysql->db->query('SELECT * FROM okec 
			WHERE id NOT IN (SELECT okec_id FROM okeckategorie) ORDER BY okec');
		$res = $query->result_array();
		echo '<form method="POST">';
		echo '<input type="text" name="kategoria"><br>';
		echo '<input type="submit" value="Send"><br><select name=okec[] multiple size="'.count($res).'">';
		foreach($res as $k => $v)
			echo '<option value="'.$v['id'].'">'.$v['okec'].'</option>';
		echo '</select></form>';
	}

	function showOkecStrom ()
	{
		$query = $this->Dbmysql->db->query('SELECT * FROM okec 
			WHERE id NOT IN (SELECT okec_id FROM okeckategorie) ORDER BY id');
		$res = $query->result_array();
		foreach($res as $k => $v)
			echo $v['id'].' => '.$v['okec'].'<br>';
	}
	
	function statusone()
	{
		$this->Dbmysql->db->query('UPDATE firma SET status = 1');
	}
	
	function upravakat()
	{
		
		$query = $this->Dbmysql->db->query('SELECT * FROM okecStrom3');
		$res = $query->result_array();
		foreach($res as $k => $v)
		{
			echo $v['cool_name'].'.katalog.loc ';
			//$this->Dbmysql->db->query('UPDATE okecStrom3 SET cool_name = "'..'" WHERE id = '.$v['id']);
		}
		/*
		$this->Dbmysql->db->query('UPDATE firmakategoria SET kategoria_id = 561 WHERE kategoria_id = 570');
		$this->Dbmysql->db->query('DELETE FROM firmakategoria WHERE kategoria_id = 570');
		$this->Dbmysql->db->query('DELETE FROM okecStrom3 WHERE id = 570');
		*/
	}
	
	function last()
	{
		$query = $this->Dbmysql->db->query('SELECT * FROM firma WHERE id > 255291 ORDER BY id DESC LIMIT 10');
		$res = $query->result_array();
		foreach($res as $k => $v)
		{print_r($v);
			echo $v['nazev'].' - '.$v['id'].'<hr>';
			//$this->Firma->delete($v['id']);
		}
		//$this->Dbmysql->db->query('DELETE FROM firma WHERE id > 255291');
	}
	
	function verifypripevek()
	{
		$this->Dbmysql->db->query('DELETE FROM diskuze WHERE datum < '.(time()-86400).' AND verificated = 0');
	}
	
	function clear_cache()
	{
		$dirpath = getcwd().'/system/cache/';
		$handle=opendir($dirpath);
		echo "Directory handle: $handle\n";
		echo "Files:\n";
		while (($file = readdir($handle)) !== false) {
			if(is_file($dirpath.$file) and $file != 'index.html')
			{
				unlink($dirpath.$file);
			}
		}
		closedir($handle);
	}
	
	function clearPopisRura()
	{
		$query = $this->Dbmysql->db->query('SELECT id,popis FROM firma WHERE popis LIKE "%|%"');
		$res = $query->result_array();echo count($res);
		foreach($res as $k => $v)
		{
			//echo $v['id'];exit;
			if(substr($v['popis'], 0, 2) == ' |')
			{
				$v['popis'] = substr($v['popis'], 2);
				$this->Dbmysql->db->query('UPDATE firma SET popis = "'.$v['popis'].'" WHERE id = '.$v['id']);
			}
		}echo $this->Dbmysql->db->affected_rows();
	}
	
	function test()
	{
		$query = $this->Dbmysql->db->query('SELECT COUNT(*) as pocet FROM firma WHERE uri = ""');
		$r1 = $query->result_array();print_r($r1);
	}
	
	function __destruct() {
       echo '<hr>Script trval: '.$this->Timer->stop();
   }
}
?>

