<?php

class webrecode
{
	//private $table_name="Agencija_elektr_odjemalci_e9";		// project name (table name in the future)
	private $table_name;
	public $categories_array;
	public $answers_in_categories;
	public $variables_array=array();
	public $recoded_groups;
	public $answers_to_display=array();
	public $project_id;
	public $error;
	public $datab;
	
	/**
	 * The constructor creates the index table and extracts all INT fields from the table with telephone numbers
	 *
	 * @param INT $id - Id of the project
	 * @param Object $datab - An instance of an database object
	 * @return bool
	 */
	public function __construct($id=0, $datab)
	{
		$this->project_id=$id;
		$this->datab=$datab;
		if(!$this->construct_db_fields())
			return false;
		
		return true;
	
	}
	
	/**
	 * The method constructs the columns that are of type INT from the telephone table and gets the name of that table from the table telephone_tables
	 *
	 * @param INT $id - id of the project
	 * @return bool
	 */
	private function construct_db_fields()
	{
		
		$this->table_name=$this->datab->get('_Projects',$this->project_id,'proj_name');
		
		$this->datab->ChangeDatabase(DB_WARPIT_WEBRECODE);
		
		$sql="SELECT ".$this->table_name."_rec_def.id, ".$this->table_name."_rec_def.multicode, ".$this->table_name."_rec_def.rec_def, ".$this->table_name."_rec_def.var_rec FROM ".$this->table_name."_rec_def";
		
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="23867455 ".$this->datab->GetError();
			return false;
		}
		
		while($row=$this->datab->fetchAssoc($result))
		{
			/*$q_name=explode("#", $row["var_arry"]);
			$name=explode("_", $q_name[0]);*/
			
			$tmp=array();
			$tmp["id"]=$row["id"];
			$tmp["var_rec"]=$row["var_rec"];
			$tmp["multicode"]=$row["multicode"];
			$tmp["label"]=$row["rec_def"];
				
			$this->recoded_groups[]=$tmp;
		}
		
		return true;
	}
	
	public function get_variables()
	{
		
		$sql="DESCRIBE ".$this->table_name;
		
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="64532545 ".$this->datab->GetError();
			return false;
		}
		
		$st=1;
		while($row=$this->datab->fetchAssoc($result))
		{
			if($row["Field"]!="id")
			{
				//check if this is fully recoded!!!
				$add_after=explode("_", $row["Field"]);
				//becouse variably may contain _ in it we take the last one as number
				$addC = count($add_after) - 1;
				$add_after_num = $add_after[$addC];
				unset($add_after[$addC]);
				$add_after_var = implode('_',$add_after);
				//if for this recode group we already checked
				if(isset($answerCheck[$add_after_var]))
				{
					if($answerCheck[$add_after_var]) continue;
				}
				else
				{
					$sql = $this->datab->SQLexecute("SELECT id FROM $this->table_name"."_rec_def WHERE var_rec = '".$add_after_var."'");
					$recDefAns = $this->datab->fetchAssoc($sql);
					if($recDefAns) //base variables are not part 
					{
						$this->get_answers_to_display($recDefAns['id']);
					//	echo "/*".$row["Field"]; print_r($this->answers_to_display);echo "*/";
						if(count($this->answers_to_display) > 0)
						{
							$answerCheck[$add_after_var] = true;
							continue;
						}
					}
					$answerCheck[$add_after_var] = false;
				}
				
				$sql="SELECT lab FROM ".$this->table_name."_var_lab WHERE varname LIKE '" . $row["Field"] . "'";
				if(!$result2=$this->datab->SQLexecute($sql))
				{
					$this->error="328657564 ".$this->datab->GetError();
					return false;
				}
				
				$row2=$this->datab->fetchAssoc($result2);
				
				$tmp=array();
				$tmp["id"]=$st;
				$tmp["variable"]=$row["Field"];
				$tmp["lab"]=$row2["lab"];
				$this->variables_array[]=$tmp;
			}
			$st++;
		}
		return true;
	}
	
	public function get_categories($id)
	{
		$multicode=0;
		$var_rec=$this->get_categories_var_rec($id, $multicode);
		
		$sql="SELECT ".$this->table_name."_labels.id, ".$this->table_name."_labels.label, ".$this->table_name."_labels.id_var FROM ".$this->table_name."_labels WHERE var_name='" . $var_rec . "'";
		
		//echo $sql;
		
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="32765645 ".$this->datab->GetError();
			return false;
		}
		
		$st=1;
		$this->categories_array=array();
		while($row=$this->datab->fetchAssoc($result))
		{
			$tmp=array();
			$tmp["id"]=$row["id"];
			//$tmp["var_rec"]=$var_rec;
			$tmp["label"]=$row["label"];
			$tmp["id_var"]=$row["id_var"];
				
			$this->categories_array[]=$tmp;
			$st++;
		}
		
		return true;
	}
	
	private function get_categories_var_rec($id, &$multicode)
	{
		//$sql="SELECT ".$this->table_name."_labels.id, ".$this->table_name."_labels.label FROM ".$this->table_name."_labels WHERE id=".$id;
		
		$sql="SELECT ".$this->table_name."_rec_def.var_rec, ".$this->table_name."_rec_def.multicode FROM ".$this->table_name."_rec_def WHERE id='".$id."'";
		
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="674545435 ".$this->datab->GetError();
			return false;
		}
		
		if($row=$this->datab->fetchAssoc($result))
		{
			$multicode=$row["multicode"];
			return $row["var_rec"];
		}
		else
		{
			$multicode=0;
			return "";
		}
	}
	
	private function search_term($term)
	{
		$num_of=substr_count($term,"\"");
		if($num_of%2!=0 || $num_of===false)
			return "";
		
		if(count($arr=explode("OR", $term))>1)
		{
			$operator=" OR ";
		}
		else if(count($arr=explode("AND", $term))>1)
		{
			$operator=" AND ";
		}
		//else 
			//return "";
		
		$terms_array=array();
		foreach($arr as $a)
		{
			$start=strpos($a, "\"");
			$start++;
			$end=strpos($a, "\"", $start);
			$l=$end-$start;
			$string=substr($a, $start, $l);
			$terms_array[]=$string;
			//echo $string."<br/>";
		}
			
		$like=array();
		$sql_statement="(";
		$st=0;
		foreach($terms_array as $ta)
		{
			//"agencija_elektr_odjem_e9"
			if($st==0)
			{
				$sql_statement.=$this->table_name."_labels.label LIKE '%".$ta."%'";
				$st++;
			}
			else 
			{
				$sql_statement.=$operator.$this->table_name."_labels.label LIKE '%".$ta."%'";
			}
		}
		$sql_statement.=")";	
		
		return $sql_statement;
	}
	
	
	private function create_query_like($query, $fields, $mode)
	{
		// mode 1 is for quick search and doesnt support sql operators(AND, OR)
		if($mode==1)
		{
			$num=count($fields);
			//if($num==2)
				return " AND (".$this->table_name."_labels.label LIKE '%".$query."%' OR ".$this->table_name."_labels.id_var LIKE '%".$query."%')";
			//else if($num==1)
				return " AND ".$this->table_name."_labels.".$fields[0]." LIKE '%".$query."%'";
			//else 
				//return "";
		}
		// mode 2 is a more advanced search option and it supports sql operators(AND, OR)
		else if($mode==2)
		{
			$search_for=" AND (";
			
			$tmp=explode(")", $query);
			if(count($tmp)>1)
			{
				for($i=0; $i<count($tmp)-1; $i++)
				{
					if(!$search_terms=strstr($tmp["{$i}"], '('))
						return "";
					
					if($i!=0)
					{
						$str_pos=strpos($tmp["{$i}"], "(");
						if($str_pos===false)
							return "";
						else 
						{
							$and_or=substr($tmp["{$i}"], 0, $str_pos);
						}
						
						if(strpos($and_or, "AND")!==false)
						{
							$glue=" AND ";
						}
						else if(strpos($and_or, "OR")!==false)
						{
							$glue=" OR ";
						}
					}
					else
						$glue="";
						
					if(($part=$this->search_term($search_terms))=="")
					{
						return "";
					}
					else 
						$search_for.=$glue.$part;
				}
				$search_for.=")";
			}
			else 
			{
				if(($part=$this->search_term($tmp[0]))=="")
					return "";
				else 
					$search_for.=$part.")";
			}
			return $search_for;
		}
	}
	
	public function get_answers_to_display($id)
	{
		$sql="SELECT ".$this->table_name."_rec_def.var_arry, ".$this->table_name."_rec_def.var_rec, ".$this->table_name."_rec_def.multicode FROM ".$this->table_name."_rec_def WHERE ".$this->table_name."_rec_def.id=".$id;
		
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="23586775 ".$this->datab->GetError();
			return false;
		}
		$multicode_enabled=222;
		if($row=$this->datab->fetchAssoc($result))
		{
			$var_arry=$row["var_arry"];
			$multicode_enabled=$row["multicode"];
		}
		
		$var=explode("#", $var_arry);
		$var=explode("_", $var[0]);	
		//we strip the last number!
		unset($var[ count($var) -1 ]);
		$var = implode('_',$var);
		
		
		if(isset($_POST["srcquery"]) && $_POST["srcquery"]!="")
		{
			$like=$this->create_query_like($_POST["srcquery"], json_decode(stripslashes($_POST["srcfields"])), 1);
		}
		else if(isset($_POST["srcquery2"]) && $_POST["srcquery2"]!="")
		{
			//$_POST["srcquery2"]='("zad" AND "smo") OR ("ker" AND "ponede") OR ("dfdd" AND "dfdf")';
			$like=$this->create_query_like(stripslashes($_POST["srcquery2"]), json_decode(stripslashes($_POST["srcfields2"])), 2);
		}
		else
			$like="";
		
		if($multicode_enabled==0)
			$multicode= " AND ".$this->table_name."_calc.recoded_id IS NULL ";
		else if($multicode_enabled==1)
			$multicode=" ";
		
		$sql="SELECT ".$this->table_name."_calc.id, ".$this->table_name."_calc.procent, ".$this->table_name."_labels.label, ".$this->table_name."_labels.id_var FROM ".$this->table_name."_labels, ".$this->table_name."_calc WHERE ".$this->table_name."_labels.id=".$this->table_name."_calc.id_label AND ".$this->table_name."_calc.var_name='".$row["var_rec"]."'".$multicode.$like;
		if($_POST['sort'] && $_POST['sort'] != 'rec_cat') //this is not optimal solution! disable sorting in grid for rec_cat!!!
			$sql.= " ORDER BY ".$_POST['sort']." ".$_POST['dir'];
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="238656443 ".$this->datab->GetError();
			return false;
		}
			
		echo "/*$sql*/";
		$this->answers_to_display = array();
		while($row=$this->datab->fetchAssoc($result))
		{
			if($multicode_enabled==1)
			{
				$sql="SELECT ".$this->table_name."_labels.id_var FROM ".$this->table_name."_labels, ".$this->table_name."_multicode WHERE ".$this->table_name."_multicode.id_calc='".$row["id"]."' AND ".$this->table_name."_multicode.recoded_id=".$this->table_name."_labels.id ORDER BY ".$this->table_name."_labels.id_var";
				
				if(!$result2=$this->datab->SQLexecute($sql))
				{
					$this->error="238656443 ".$this->datab->GetError();
					return false;
				}
				$a=array();
				while($row2=$this->datab->fetchAssoc($result2))
				{
					$a[]=$row2["id_var"];
				}
				$multi=join(", ", $a);
			}
			else
				$multi="-";
			
			$tmp=array();
			$tmp["id"]=$row["id"];
			$tmp["label"]=$row["label"];
			$tmp["procent"]=$row["procent"];
			$tmp["id_var"]=$row["id_var"];
			$tmp["rec_cat"]=$multi;
				
			$this->answers_to_display[]=$tmp;
		}
		//echo "COUNT: " . count($this->answers_to_display)."<br/>";
		
		return true;
	}
	
	
	public function get_answers_in_categories($id, $multicode=0)
	{
		if($multicode==0)
			$sql="SELECT ".$this->table_name."_calc.id, ".$this->table_name."_labels.label, ".$this->table_name."_labels.id_var FROM ".$this->table_name."_labels, ".$this->table_name."_calc WHERE ".$this->table_name."_calc.recoded_id='".$id."' AND ".$this->table_name."_calc.id_label=".$this->table_name."_labels.id";
		else 
			$sql="SELECT ".$this->table_name."_calc.id, ".$this->table_name."_labels.label, ".$this->table_name."_labels.id_var FROM ".$this->table_name."_labels, ".$this->table_name."_calc, ".$this->table_name."_multicode WHERE ".$this->table_name."_multicode.recoded_id='".$id."' AND ".$this->table_name."_multicode.id_calc=".$this->table_name."_calc.id AND ".$this->table_name."_calc.id_label=".$this->table_name."_labels.id";
		
			//echo $sql;
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="64557674 ".$this->datab->GetError();
			return false;
		}
			
		//echo $sql;
		$this->answers_in_categories=array();
		while($row=$this->datab->fetchAssoc($result))
		{
			$tmp=array();
			$tmp["id"]=$row["id"];
			$tmp["id_var"]=$row["id_var"];
			$tmp["label"]=$row["label"];
				
			$this->answers_in_categories[]=$tmp;
		}
		
		return true;
	}
	
	public function remove_answers_from_category($id, $multicode=0, $recoded_id)
	{
		$delete=join(",", $id);
		if($multicode==0)
		{
			$sql="UPDATE ".$this->table_name."_calc SET ".$this->table_name."_calc.recoded_id=NULL WHERE ".$this->table_name."_calc.id IN(".$delete.")";
			
			if(!$result=$this->datab->SQLexecute($sql))
			{
				$this->error="248546567 ".$this->datab->GetError();
				return false;
			}
			
			//$this->update_main_table($id[0]);
			
		    //#Section #0202201503 Tanis
			$this->update_main_table2($id, $id[0], "NULL");
			//#EndSection #0202201503
			
			return true;
		}
		
		$sql="DELETE FROM ".$this->table_name."_multicode WHERE ".$this->table_name."_multicode.recoded_id='".$recoded_id."' AND ".$this->table_name."_multicode.id_calc IN(".$delete.")";
			
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="248546567 ".$this->datab->GetError();
			return false;
		}
		
		//$this->update_main_table($id[0]);
		
		return true;
		
		
	}
	
	public function remove_category_from_group($id)
	{
		$sql="UPDATE ".$this->table_name."_calc SET recoded_id=NULL WHERE recoded_id='".$id."'";
		
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="346586764 ".$this->datab->GetError();
			return false;
		}
		
		$sql="DELETE FROM ".$this->table_name."_calc WHERE id='".$id."'";
		
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="57977645 ".$this->datab->GetError();
			return false;
		}
		
		$sql="DELETE FROM ".$this->table_name."_labels WHERE id='".$id."'";
		
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="3465867 ".$this->datab->GetError();
			return false;
		}
		
		return true;
	}
	
	private function update_main_table($id, $updateNull = true)
	{
		/*$sql="SELECT ".$this->table_name."_calc.var_name, ".$this->table_name."_rec_def.var_arry 
		FROM  ".$this->table_name."_calc, ".$this->table_name."_rec_def 
		WHERE ".$this->table_name."_calc.id='".$id."' AND ".$this->table_name."_rec_def.var_rec=".$this->table_name."_calc.var_name";
		
		*/
		
		$sql = "SELECT a.var_name, b.var_arry 
		          FROM " . $this->table_name."_calc AS a
		          JOIN " . $this->table_name."_rec_def as b 
	              ON a.var_name = b.var_rec
		          WHERE a.id = " . $id;
		      print "/*";
		      var_dump($sql);    
         print "*/";
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="279675543 ".$this->datab->GetError();
			return false;
		}
		
		if(!$row=$this->datab->fetchAssoc($result))
		{
			$this->error="1234375 ".$this->datab->GetError();
			return false;
		}
		
		$var_name=$row["var_name"];
		$cols=explode("#", $row["var_arry"]);
		
		/*$sql="SELECT ".$this->table_name."_labels.id, ".$this->table_name."_labels.id_var 
		FROM ".$this->table_name."_labels 
         * WHERE ".$this->table_name."_labels.var_name='".$var_name."'";*/
	    $sql = "SELECT id,id_var
                FROM " . $this->table_name."_labels
                WHERE var_name = '" . $var_name. "'"; 
		if(!$result3=$this->datab->SQLexecute($sql))
		{
			$this->error="435334657 ".$this->datab->GetError();
			return false;
		}
		$records=array();
		
		while($row3=$this->datab->fetchAssoc($result3))
		{
			$records["{$row3["id"]}"]=$row3["id_var"];
		}
		$st = 0;
		foreach($cols as $c)
		{
			$st++;
			$get_num=explode("_", $c);
			//becouse there may be _ in the variable, we fetch the number and name!
			$addC = count($get_num) - 1;
			$get_num_num = $get_num[$addC];
			unset($get_num[$addC]);
			$get_num_var = implode('_',$get_num);
			
			$sql="SELECT ".$this->table_name."_labels.id_var, ".$this->table_name."_calc.recoded_id FROM ".$this->table_name."_labels INNER JOIN ".$this->table_name."_calc ON ".$this->table_name."_labels.id=".$this->table_name."_calc.id_label WHERE ".$this->table_name."_labels.var_name='".$get_num_var."' AND ".$this->table_name."_calc.var_name='".$var_name."'";

			//echo "lllaaa: ".$sql."<br/>";
			//echo "/*".$st." ".$sql."*/";
			if(!$result=$this->datab->SQLexecute($sql))
			{
				$this->error="654563456 ".$this->datab->GetError();
				return false;
			}
		
			while($row=$this->datab->fetchAssoc($result))
			{
				if($row["recoded_id"]=="")
				{
					$recoded_id="NULL";
					//echo "NULL: ".$row["recoded_id"]."<br/>";
				}
				else
				{
					$recoded_id=$records["{$row["recoded_id"]}"];
					//echo "FULL: ".$row["recoded_id"]."<br/>";
				}
				
				//$recoded_id=$row["recoded_id"];
				/*if($tmp==0)
					$recoded_id="NULL";*/
				
				    if($recoded_id == "NULL")
                    {
                        if($updateNull)
                        {
                                $sql2="UPDATE ".$this->table_name." SET ".$var_name."_".$st."=".$recoded_id." WHERE ".$c."=".$row["id_var"]."";
                    
                                echo "/*<br />".$st." ".$sql2."*/";
                                
                                if(!$result2=$this->datab->SQLexecute($sql2))
                                {
                                    $this->error="748945545 ".$this->datab->GetError();
                                    return false;
                                }
                        }
                    }
                    else
                    {
                            $sql2="UPDATE ".$this->table_name." SET ".$var_name."_".$st."=".$recoded_id." WHERE ".$c."=".$row["id_var"]."";
                    
                            echo "/*<br />".$st." ".$sql2."*/";
                            
                            if(!$result2=$this->datab->SQLexecute($sql2))
                            {
                                $this->error="748945545 ".$this->datab->GetError();
                                return false;
                            }
                    }
					
				
					
					//echo $c.": ".$this->datab->affectedRows()."<br/>";
					//if($this->datab->affectedRows()>0)
						//break;
				
			}
		}
		
		return true;
	}
    
    //#Section #0202201501 Tanis
    // We add this function becouse old one produced to many SQL querys
    public function update_main_table2($answers, $id, $id_cat)
    {
        $id_cat = $this->datab->get($this->table_name . "_labels", $id_cat, 'id_var', DB_WARPIT_WEBRECODE);
        $answers = $this->datab->get($this->table_name . "_calc", "id IN(" . implode(',', $answers) .")", 'id_label', DB_WARPIT_WEBRECODE);
        $answers = $this->datab->get($this->table_name . "_labels", "id IN(" . implode(',', $answers) .")", 'id_var', DB_WARPIT_WEBRECODE);
      
        $sql = "SELECT a.var_name, b.var_arry 
                      FROM " . $this->table_name."_calc AS a
                      JOIN " . $this->table_name."_rec_def as b 
                      ON a.var_name = b.var_rec
                      WHERE a.id = " . $id;
                      
            if(!$result=$this->datab->SQLexecute($sql))
            {
                $this->error="279675543 ".$this->datab->GetError();
                return false;
            }
            
           $row=$this->datab->fetchAssoc($result);
            
            $var_name=$row["var_name"];
            $cols=explode("#", $row["var_arry"]);
            
            $i = 0;
            foreach($cols as $col)
            {
                $i++;
                //$sql = "UPDATE " . $this->table_name . " SET r_" . $col . " = '" . $id_cat . "' WHERE " . $col . " IN (" . implode(',', $answers) . ")";
                $sql = "UPDATE " . $this->table_name . " SET " . $var_name . "_" . $i . "  = '" . $id_cat . "' WHERE " . $col . " IN (" . implode(',', $answers) . ")";
               print "/*" . $sql . "<br />*/ ";
                if(!$result=$this->datab->SQLexecute($sql))
                {
                    $this->error = $this->datab->GetError();
                    return false;
                }
            }
            
            return TRUE;
    }
    //#EndSection #0202201501
	
	public function insert_answers_into_categorie($answers, $id_cat, $multicode)
	{
	/*	echo "ID_CAT: ". $id_cat . "<br/>";
		print_r($answers) . "<br/>";*/
		
		if($id_cat>0 && is_array($answers))
		{
			/*$time = microtime();
			$time = explode(' ', $time);
			$time = $time[1] + $time[0];
			$start = $time;*/
			//get($table,$row=null,$column=null,$database=null)
			$needed_id=$answers[0];
			if($multicode==1)
			{
				$pairs=array();
				foreach ($answers as $a)
				{
					$pairs[]="(".$a.",".$id_cat.")";
				}
				
				$sql="INSERT INTO ".$this->table_name."_multicode (id_calc,recoded_id) VALUES ".join(", ", $pairs)."  ON DUPLICATE KEY UPDATE id_calc=id_calc";
				
				if(!$result=$this->datab->SQLexecute($sql))
				{
					$this->error="245734524 ".$this->datab->GetError();
					return false;
				}
				return true;
			}
			
			$sql="UPDATE ".$this->table_name."_calc SET recoded_id='".$id_cat."' WHERE ".$this->table_name."_calc.id IN(".join(",", $answers).")";
			
			if(!$result=$this->datab->SQLexecute($sql))
			{
				$this->error="245734524 ".$this->datab->GetError();
				return false;
			}
            
            //#Section #0202201502 Tanis
            $this->update_main_table2($answers, $needed_id, $id_cat);
			//#ENDSection #0202201502
			
		//	$this->update_main_table($needed_id, FALSE);
    		
            
			/*$time = microtime();
			$time = explode(' ', $time);
			$time = $time[1] + $time[0];
			$finish = $time;
			$total_time = round(($finish - $start), 4);
			echo '<p>Page generated in '.$total_time.' seconds.</p>'."\n";*/
			
			return true;
		}
	}
	
	public function create_cat_in_recode_group($recoded_group_id, $name, $value)
	{		
		if($recoded_group_id>0 && $name!="" && $value!="")
		{
			$multicode=0;
			$var_rec=$this->get_categories_var_rec($recoded_group_id, $multicode);
			
			/*$sql="SELECT MAX(id_var) as max FROM ".$this->table_name."_labels WHERE var_name='".$var_rec."'";
			if(!$result=$this->datab->SQLexecute($sql))
			{
				echo "<br/>" . $this->datab->GetError();
				return false;
			}
			
			if($row=$this->datab->fetchAssoc($result))
				$max_id_var=$row["max"]+1;*/
			
			$sql="INSERT INTO ".$this->table_name."_labels(id_var, label, var_name) VALUES('".$value."', '".$name."', '".$var_rec."')";
			
			if(!$result=$this->datab->SQLexecute($sql))
			{
				$this->error="8663533 ".$this->datab->GetError();
				return false;
			}
			
			return true;
		}
		
		return false;
	}
	
	public function create_recode_group($data, $label, $value)
	{
		$var_array=array();
		$insert_array=array();
		
		$max=0;
		$st=0;
		foreach($data as $d)
		{
			$st++;
			
			$add_after=explode("_", $d);
			//becouse variably may contain _ in it we take the last one as number
			$addC = count($add_after) - 1;
			$add_after_num = $add_after[$addC];
			unset($add_after[$addC]);
			$add_after_var = implode('_',$add_after);
			
			$baseVarNames[] = $add_after_var;
			
			if($add_after_num>$max);
				$max=$add_after_num;
					
			if($st==1)
			{
				$var_cat=$add_after_var;		
				$num_of=count($data);
				$first_add=" ADD `" . $value . "_" . $st . "` INT UNSIGNED NULL DEFAULT NULL AFTER `" . $add_after_var . "_$add_after_num`";
			}
			else
			{
				$other_add.=" , ADD `" . $value . "_" . $st . "` INT UNSIGNED NULL DEFAULT NULL AFTER `" . $add_after_var . "_" . $add_after_num ."`";
			}
			
			$var_array[]=$d;
			$insert_array[]="('" . $value."_" . $st . "', '" . $label . "')";
		}
		
		$sql="INSERT INTO ".$this->table_name."_var_lab(varname, lab) VALUES".join(",", $insert_array);
		
		//echo "<br/>".$sql."<br/>";
		
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="341234 ".$this->datab->GetError();
			return false;
		}
		//let's get the labels array!
		$sql = $this->datab->SQLexecute("SELECT * FROM $this->table_name"."_labels WHERE var_name IN ('".implode("','",array_unique($baseVarNames))."')");
		while ($row = $this->datab->fetchAssoc($sql) )
		{
			$labels[ $row['var_name'] ][ $row['id_var'] ] = $row['id'];
		}
		
		
		$sql = $this->datab->SQLexecute("SELECT ".implode(',',$data)." FROM $this->table_name ");
		while ($row = $this->datab->fetchAssoc($sql) )
		{
			$hasValue = false;
			foreach ($row as $var => $varValue) {
				if($varValue)
				{
					$hasValue = true;
					$tmp = explode('_',$var);
					$varApp = array_pop($tmp);
					$varName = implode('_',$tmp);
					//we "translate" old var name to new var name!
					$calcValues[ $labels[$varName][$varValue]  ] ++;
				}
			}
			if($hasValue)
				$sum++;
		}
		foreach ($calcValues as $id_label => $number) {
			$proc = round( $number / $sum * 100,2  );
			$sqlCalcValues[] = "($id_label,$number,'$proc%','$value')";
		}
		
		$this->datab->SQLexecute("INSERT INTO $this->table_name"."_calc(id_label,number,procent,var_name) VALUES ".implode(',',$sqlCalcValues));
		
		
		/*$sql="SELECT SUM(".$this->table_name."_calc.number) as sum FROM ".$this->table_name."_calc WHERE ".$this->table_name."_calc.var_name='".$var_cat."' AND ".$this->table_name."_calc.recoded_id IS NOT NULL";
		
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="22346432 ".$this->datab->GetError();
			return false;
		}
		
		$row=$this->datab->fetchAssoc($result);
		$sum_all=$row["sum"];
		
		//echo "<br/>SUM_ALL: ".$sum_all."<br/>";
		
		$sql="SELECT ".$this->table_name."_labels.id FROM ".$this->table_name."_labels WHERE ".$this->table_name."_labels.var_name='".$var_cat."' ORDER BY ".$this->table_name."_labels.id";
		
		//echo $sql . "<br/>";
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="22346432 ".$this->datab->GetError();
			return false;
		}
		
		while($row=$this->datab->fetchAssoc($result))
		{
			$sql="SELECT SUM(".$this->table_name."_calc.number) as sum FROM ".$this->table_name."_calc WHERE ".$this->table_name."_calc.recoded_id='".$row["id"]."' AND ".$this->table_name."_calc.var_name='".$var_cat."'";

			//echo $sql . "<br/>";
			if(!$result2=$this->datab->SQLexecute($sql))
			{
				$this->error="562433 ".$this->datab->GetError();
				return false;
			}
			
			$row2=$this->datab->fetchAssoc($result2);
			$sum=$row2["sum"];
			
			$procent=round(($sum*100)/$sum_all, 1);
			//echo "<br/>PROCENT: ".$procent."<br/>";
			
			$sql="INSERT INTO ".$this->table_name."_calc(id_label, number, procent, recoded_id, var_name) VALUES('" . $row["id"] . "', '" . $sum . "', '".$procent."%', NULL, '".$value."')";
			
			if(!$result3=$this->datab->SQLexecute($sql))
			{
				$this->error="23435 ".$this->datab->GetError();
				return false;
			}
		}*/
		
		
		$sql="ALTER TABLE `" . $this->table_name . "` ".$first_add.$other_add;
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="4575683 ".$this->datab->GetError();
			return false;
		}
		
		
		$sql="INSERT INTO ".$this->table_name."_rec_def(var_arry, var_label, var_rec, rec_def) VALUES('" . implode("#", $data) . "', '" . $label . "', '" . $value . "', '" . $label . "')";
			
		if(!$result=$this->datab->SQLexecute($sql))
		{
			$this->error="7734324 ".$this->datab->GetError();
			return false;
		}
		
		return true;
		
	}

	public function splitLabel($idCalc,$recGroup,$delimiter)
	{
		$idLabel = $this->datab->get($this->table_name.'_calc',$idCalc,'id_label,var_name');
		$recName = $idLabel['var_name'];
		$idLabel = $idLabel['id_label'];
		$label = $this->datab->get($this->table_name.'_labels',$idLabel,'label,var_name,id_var');
		$varName = $label['var_name'];
		$idVar = $label['id_var'];
		$label = $label['label'];
		$splitLabels = explode($delimiter,$_POST['newLabel']);
		//remove this rec from labels
		$this->datab->SQLexecute("DELETE FROM ".$this->table_name.'_labels WHERE id = '.$idLabel);
		$this->datab->SQLexecute("DELETE FROM ".$this->table_name.'_calc WHERE id = '.$idCalc);
		$maxId=0;
		foreach ($splitLabels as $newLabel) {
			$newLabel = trim($newLabel);
			//check if this label already exists
			$labExist = $this->datab->get($this->table_name.'_labels',"label='$newLabel' AND var_name='$varName'",'id_var,id');
			if($labExist)
			{
				$newRecode[] = $labExist[0]['id_var'];
				$updateCalc[] = $labExist[0]['id'];
				//we check if this label has been recoded
				$recoded = $this->datab->get($this->table_name.'_calc','id_label='.$labExist[0]['id'],'recoded_id');
				if($recoded)
				{
					$recodedVar = $this->datab->get($this->table_name.'_labels',$recoded[0],'id_var');
					$newRecRecoded[ $labExist[0]['id_var'] ] = $recodedVar;
				}
			}
			else //if it doesn't exist we create a new record in labels 
			{
				if(!$maxId)
				{
					$sql="SELECT MAX(id_var) as max FROM ".$this->table_name."_labels WHERE var_name='".$varName."'";
					$result = $this->datab->SQLexecute($sql);
					
					if($row=$this->datab->fetchAssoc($result))
						$maxId=$row["max"];
				}
				$maxId++;
				$newRecode[] = $maxId;
				
				
				$sql = "INSERT INTO ".$this->table_name."_labels(id_var,label,var_name)VALUES($maxId,'$newLabel','$varName')";
				$this->datab->SQLexecute($sql);
				//we save the new label to create a _calc records for it...id_var is key, id_label is value
				$createCalc[$maxId] = $this->datab->GetLastInsertId();
			}
		}
		//let's find all records that have this recode
		$colNames = $this->datab->get($this->table_name.'_rec_def',$recGroup,'var_arry');
		$colNames = explode('#',$colNames);
		$sql = "SELECT id,".implode(',',$colNames)." FROM $this->table_name WHERE ".implode("=$idVar OR ",$colNames)."=$idVar";
		$res = $this->datab->SQLexecute($sql);
		$count=0;
		$newRecodeCount = count($newRecode);
		while ($row=$this->datab->fetchAssoc($res))
		{
			$update = array();
			$index=0;
			$id = $row['id'];
			unset( $row['id'] );
			foreach ($colNames as $col ) {
				$val = $row[$col];
				//if value exist we leave it be! except if it's the old value
				if($index >= $newRecodeCount)
					break;
				if(!$val || $val == $idVar)
				{
					$update[] = $col.'='.$newRecode[$index];
					if(!is_array($newRecRecoded[ $newRecode[$index]]) AND isset($newRecRecoded[ $newRecode[$index]]))
						$update[] = 'r_'.$col.'='.$newRecRecoded[ $newRecode[$index]  ] ;
					
					$index++;
				}
			}
			//if we don't have enough columns we need to create new ones!
			if($index<count($newRecode) )
			{
				$colNum = count($colNames);
				$prevCol = $col;
				$r_prevCol = 'r_'.$col;
				for ($index; $index<count($newRecode);$index++ )
				{
					$colNew = $varName.'_'.($colNum+1);
					$colsToCreate[] = 'ADD '.$colNew.' INT(10) AFTER '.$prevCol;
					$prevCol = $colNew;
					//we also craete r_ colname!
					$r_colNew = 'r_'.$varName.'_'.($colNum+1);
					$colsToCreate[] = 'ADD '.$r_colNew.' INT(10) AFTER '.$r_prevCol;
					$r_prevCol = $r_colNew;
					$varLabAdd[] = '("'.$colNew.'")';
					$varLabAdd[] = '("'.$r_colNew.'")';
					$colNames[] = $colNew;
					
					$update[] = $colNew.'='.$newRecode[$index];
					if(!is_array($newRecRecoded[ $newRecode[$index]]) AND isset($newRecRecoded[ $newRecode[$index]]))
						$update[] = 'r_'.$colNew.'='.$newRecRecoded[ $newRecode[$index]  ] ;
					
					$colNum++;
				}
				$sql= "ALTER TABLE $this->table_name ".implode(',',$colsToCreate);
				$this->datab->SQLexecute($sql);
				//add this columns to the var_lab
				$sql = "INSERT INTO $this->table_name"."_var_lab (varname) VALUES ".implode(',',$varLabAdd);
				$this->datab->SQLexecute($sql);
				//update rec_def
				$sql = "UPDATE $this->table_name"."_rec_def SET var_arry='".implode('#',$colNames)."' WHERE id=$recGroup";
				$this->datab->SQLexecute($sql);
				
				$addedCols = true;
			}
			
			$sql = "UPDATE  $this->table_name SET ".implode(',',$update)." WHERE id=$id";
			$this->datab->SQLexecute($sql);
			$count++;
		}
		//let's update the counter for each in calc table!
		//first let's find out how many records we actually have
		$sql = "SELECT count(*) FROM $this->table_name WHERE ".implode(" IS NOT NULL OR ",$colNames)." IS NOT NULL";
		$res = $this->datab->SQLexecute($sql);
		$row = $this->datab->fetchRow($res);
		$allRec = $row[0];
		if($updateCalc)
		foreach ($updateCalc as $label_id) {
			$sql = "UPDATE ".$this->table_name."_calc SET number=number+$count,procent=CONCAT(ROUND(number/$allRec*100,2),'%') WHERE id_label=$label_id";
			$this->datab->SQLexecute($sql);
		}
		//insert new calc
		if($createCalc)
		foreach ($createCalc as $var_id => $lab_id) {
			$sql = "INSERT INTO $this->table_name"."_calc (id_label,number,procent,var_name) VALUES ($lab_id,$count,'".round($count/$allRec*100,2) ."%','$recName' )";
			$this->datab->SQLexecute($sql);
		}
		echo "{success:true";
		if($addedCols)
			echo(",newCols:true");
		echo "}";
			
	}
}
?>