<?php
/*
Copyright (C) 2008 by oscim   Project lossim  [ 15.09.2009, 09:17:59] @oscim
// kate: replace-trailing-space-save true; space-indent on; indent-width 2; mixedindent off; indent-mode cstyle; word-wrap-marker-color #41C2A4
ENCODE UTF-8
*/
if (!defined('ERREUR_DS_REQUETE')) define('ERREUR_DS_REQUETE', "erreur dans la forme de la requete:: ");
if (!defined('REQUETE_NOT_OK')) define('REQUETE_NOT_OK', "La requete contient des erreurs ");
if (!defined('DOES_NOT_EXIST')) define('DOES_NOT_EXIST', " n'existe pas:: ");
if (!defined('ERR_01')) define('ERR_01', 'ERR_01: La table n\'existe pas::');
if (!defined('ERR_10')) define('ERR_10', 'ERR_10: Prefix ou colonne introuvable');
if (!defined('ERR_20')) define('ERR_20', "ERR_20: Strcuture de table incomplete, \n\n manque # en fin de ligne apres le ;");
if (!defined('ERR_21')) define('ERR_21', "ERR_21: Le prefix de table est introuvable");

class sql_php{
  var $table;
  var $data;
  var $prefix ;
  var $id ;
  var $error ;

  function sql_php($table,$public=false){
    $this->path_table=(!$public) ? ADD_PATH_ADMIN :'';
    require_once($this->path_table."lib_partage/class.configfile.php");
    if (file_exists($this->path_table.$table) && tep_not_null($table)) {
            $this->table = $this->path_table.$table;
            return true;
    } else die ( "<p class='erreur'> TABLE ".DOES_NOT_EXIST.$table."</p>");
  }

  function prefix($prefix){
    $this->prefix = $prefix;
  }

  function id($id){
    $this->id = $id;
  }

  /**
   Traitement de l'element WHere, et retour tableau colonne et conditions
  */
  function ele_where($where){
    $l=strlen($where);
    $p=strpos($where,'=');
    $col=trim(substr($where,0,$p));
    $cond=stripslashes(ltrim(rtrim(str_replace('=','',substr($where,$p,$l)))));
    $condition=(eregi("^'(.*)'$",$cond,$reg))? $reg[1] : $cond;

$this->retour_debugg[]='colonne '.@$col."<br />";
$this->retour_debugg[]='condidtion '.@$condition."<br />";

    return array('col'=>$col, 'condition'=>$condition);
  }
	/* Fournit indexation auto d'un champs, trie et recup valeur la plus haute, et incremente
	si colonne fournit, traite colonne defini */
	function id_auto($colonne){
		$retour=$this->select(' SELECT * FROM '.$this->table);
		$tmp=array();
		if (is_array($retour)){
			while( $r=each($retour)){
			if (is_array($r['value']))
				while( $r2=each($r['value'])){
					if ((empty($colonne)&&strtoupper($r2['key'])=='ID')||(strtoupper($colonne)=='ID'&&strtoupper($colonne)==$r2['key'])) $tmp[]=$r2['value'];
					elseif($colonne==$r2['key']) $tmp[]=$r2['value'];
				}
			}
		} else  die("<p class='erreur'>".ERREUR_DS_REQUETE."<pre>".$data."</pre></p>");
		$tmp1=array_flip($tmp);
		krsort($tmp1);
		$r=each($tmp1);
		$i=(tep_not_null($r['key']))? $r['key']: '001';
		$i++;
		return $i;
	}
	/* Sortie type SQL
	retour array, 2 dim 1 col = ID, 2 liset complet colonne avce valeur */
	function sortie_sql($result){
		$s = $S = $name_fields = array(); // SORTIE
		if(tep_not_null($this->orderby)){
			if (eregi('ASC','',$this->orderby)) {
				$sens='ASC';
				$ref_tri=trim(substr($this->orderby ,0,(strlen($this->orderby)-3)));
			} else {
				$sens= 'DESC';
				$ref_tri=trim(substr( $this->orderby,0,(strlen($this->orderby)-4)));
			}
			if (strtoupper($ref_tri)=='ID'){/* Tri Id sur tableau primaire ou 1D */
				if ($sens=='ASC') ksort($result);
				else krsort($result);
			}
			reset($result);
			foreach($result as $clef=>$val){
				if (is_array($val)){ // 2D
					$s=array("id"=>$clef);
					while ($r = each($val)){
						array_push ($name_fields, $r['key']) ;
						$s = array_merge($s, array($r['key'] => $r['value']));
					}
					array_push ($S, $s) ;
				}
				else { // 1D
					array_push ($name_fields, $clef) ;
					array_push ($S,array("id"=>$clef,"col"=>$val));
				}
			}
			if (strtoupper($ref_tri)!='ID'){/* Tri Id sur tableau secondaire ou 2D */
/* Prendre en charge le tri sur une colonne tiers autre que ID */
			}
		}else {
			foreach($result as $clef=>$val){
				if (is_array($val)){ // 2D
					$s=array("id"=>$clef);
					while ($r = each($val)){
						array_push ($name_fields, $r['key']) ;
						$s = array_merge($s, array($r['key'] => $r['value']));
					}
					array_push ($S, $s) ;
				}
				else { // 1D
					array_push ($name_fields, $clef) ;
					array_push ($S,array("id"=>$clef,"col"=>$val));
				}
			}
		}
	$this->retour_debugg[]='<pre>';
	$this->retour_debugg[]=$S;
	$this->retour_debugg[]='</pre>';
		/* Ement supplementaires */
// 		array_push ($S,array("num_rows"=>count($result)));
// 		array_push ($S,array("num_fields"=>count($name_fields)));
// 		array_push ($S,array("name_fields"=>$name_fields));
		return $S;
	}


	/** Delete de contenu */
	function deletes($data)	{
          $this->retour_debugg[]="<hr />".$data."<br />";
          if ($this->table == true){
            $table = ereg_replace('DELETE FROM ','',substr ($data,0, strpos($data," WHERE ") ) );
            $where=ereg_replace('.*WHERE ','',$data);

            if(tep_not_null($where)) {
                    $r=$this-> ele_where($where);
                    $col=$r['col']; $condition=$r['condition'];
            } else $col=$condition='';

            if (eregi("^'(.*)'$",rtrim(ltrim($condition)),$reg)) $condition=$reg[1];
            $cfg = new configfile($this->table);

            if (!tep_not_null($this->id) && $col!=''||strtoupper($col)=='ID'){
            /**
               requet  sans WHERE , supp complete de la key1
            */
              $cfg->prefix = '';
              $this->conf_open = $cfg;
              $retour=$this->select(' SELECT * FROM '.$this->table.' WHERE id = '.trim($condition));
              $cfg->prefix =$this->prefix."['".trim($condition)."']";
              while( $r=each($retour)){
                while( $r2=each($r['value'])){
                  if (strtoupper($r2['key'])!='ID' && $condition==trim($r['value'][$col]) ){ // DANS ligne ok
                    if (!$cfg->suppVar(trim($r2['key']))) return "<p class='erreur'>".ERROR.REQUETE_NOT_OK." for col = ID et ss col:".$r2['key']."</p><pre class='erreur'>".$data." \n\n".$cfg->error." </pre>";
                  }
                }
              }
    /* Prise encharge 2 key avec id preciser */
            }elseif (tep_not_null($this->id) && $col!='' ){
              $cfg->prefix = $this->prefix."['".$this->id."']";
              $this->conf_open = $cfg;
              $retour=$this->select(' SELECT * FROM '.$this->table.' WHERE '.$where);
              while( $r=each($retour)){
                while( $r2=each($r['value'])){
                  if ($r2['key']==$col && $condition==trim($r['value'][$col]) && $this->id == trim($r['value']['id']) ){ // DANS ligne ok
                    if (!$cfg->suppVar(  trim($r2['key'])  ) ) return "<p class='erreur'>".ERROR.REQUETE_NOT_OK." for col = ID et ss col:".$r2['key']."</p><pre class='erreur'>".$data." \n\n".$cfg->error." </pre>";
                  }
                }
              }
            } elseif($col!='' ) { /* Colonne != ID */
              $cfg->prefix = $this->prefix ;
              if (!$cfg->suppVar( trim($col) )  ) return "<p class='erreur'>".ERROR.REQUETE_NOT_OK."</p><pre class='erreur'>".$data." \n\n".$cfg->error." </pre>" ;
            } else return "<p class='erreur'>".ERROR.REQUETE_NOT_OK."clause introuvalble $table</p>" ;

            if (!$cfg->saveFile()) return false ;
            else return true;

          }
          else return false;
	}


  /**
   UPDATE  de contenu  tableau 1 ou 2D
  */
  function update($data){
    $this->retour_debugg[]="<hr />".$data."<br />";

    $where=(ereg("(WHERE)",$data))?ereg_replace('.*WHERE','',$data) : '';
    $r=explode('SET',ereg_replace('.WHERE.*','',$data));
    /* Exttract type (SET id= 1 SET etat= 2 ...) ou type (SET id = 1 , etat = 2 )*/
    if (count($r)==2){ $tab[]=$r[1];
            if (eregi(".*[ ]=[ ].*[ ],[ ].*[ ]=[ ].*",$r[1])) {
                    $r2=explode(' , ',$r[1]);
                    $tab=$r2;
            }
    }else $tab=array_diff($r,array($r[0]));
    /* Sortie Erreur */
    if (count($tab)<1) die("<p class='erreur'>".ERREUR_DS_REQUETE."<pre>".$data."</pre></p>");
    /* EXtraction*/
    while ($row = each($tab))
            if (tep_not_null($row['value'])) {
                    $l=strlen($row['value']);
                    $p=strpos($row['value'],' = ');
                    $key=trim(substr($row['value'],0,$p));
                    $val=substr($row['value'],$p+2,$l);
                    /* suprression valeur encadre par ' */
                    if (eregi("^'(.*)'$",rtrim(ltrim(stripslashes($val))),$reg)) $val=$reg[1];
                    $s[$key]=$val;
    }
    /*clause where */
    if(tep_not_null($where)) {
            $r=$this-> ele_where($where);
            $col=$r['col']; $condition=$r['condition'];

            $condition="['".$condition."']";
    }
    /* Uniquement tableau 1d pas de WHERE */
    else {
            $condition="";
            $col='';
            $this->id='';
    }

    $cfg = new configfile($this->table);
    if (tep_not_null($this->id)||($col!=''||strtoupper($col)=='ID') ){
            if ($col!='' && strtoupper($col)=='ID') $cfg->prefix = trim($this->prefix.$condition) ;
            elseif ($col!=''&&$col==$this->prefix)$cfg->prefix = $this->prefix ; // tabl 1d clause where avec prefix de table
            else $cfg->prefix = trim($col.$condition) ;
    }
    else $cfg->prefix = $this->prefix ;

    /* Energsitre */
    while ($row = each($s))
            if (!$cfg->editVar(trim($row['key']),ltrim(rtrim($row['value']))) ) return "<p class='erreur'>".ERROR.REQUETE_NOT_OK."</p><pre class='erreur'>".$data." \n\n".$cfg->error." </pre>" ;

    if (!$cfg->saveFile()) return false ;
    else return true;

  }

        /**
         extraction de la chaine des valeur lors commande insert
        */
	/*private*/ function extract_input_value($string_values){
//           $string_values=str_replace('"','"',$string_values);
          $tabl=explode("'",ereg_replace("(^[[:blank:]]*'|'[[:blank:]]*$)","",$string_values) );
          while($r=each($tabl))
                  if (!eregi("^([[:blank:]]*,[[:blank:]]*)$" ,$r['value'])) $s[]=$r['value'];

          return $s;
	}



  /**
    Ajout de contenu
  */
  /*public*/ function insert($data){
  $this->retour_debugg[]="<hr />".$data."<br />";
  if ($this->table == true){
    $ch_values=substr(ereg_replace('(.*VALUES.|[)(])','',$data),1,-1);
    $ch_champs=substr(trim(ereg_replace('(.*INSERT.INTO.[a-z_/.)(-]*|VALUES.*)','',$data)),1,-1);

    $r= (!ereg(",",$ch_values)) ? array(trim(addslashes(str_replace("'",'',$ch_values)))) : $this->extract_input_value($ch_values);;
    $e= (!ereg(",",$ch_champs))  ? array(str_replace("'",'',$ch_champs)) : explode(',',$ch_champs);

    if(count($r)==count($e)){
      ## Reatribution des val
      for ($i=0;$i<=count($r)-1;$i++)
              $s[trim($e[$i])]=str_replace("'",'',$r[$i]);

      $cfg_admin = new configfile($this->table);
      ##Prise en charge des element à une ou 2 dimension
      if (tep_not_null($this->id)) $cfg_admin->prefix = str_replace (" ","",$this->prefix."['".$this->id."']") ;
      else $cfg_admin->prefix = $this->prefix ;

      while ($row = each($s))
        if (ltrim(rtrim($row['value']))=='AUTO()') $cfg_admin->addVar( $row['key'],$this->id_auto($row['key']),' ');
        elseif (ltrim(rtrim($row['value']))=='NOW()'){
          if (eregi("heure",$row['key']))$d=date("H:i:s");
          elseif (eregi("date_seul",$row['key'])) $d=date("Y-m-d");
          else $d=date("Y-m-d H:i:s");
          $cfg_admin->addVar( $row['key'],$d,'');
        }else $cfg_admin->addVar( $row['key'],ltrim(rtrim($row['value'])),'');

      if (!$cfg_admin->saveFile()) return false ;
      else return true;

    }else die("<p class='erreur'>".ERREUR_DS_REQUETE."<pre>".$data."</pre>
            <pre>
                Count value:".count($r).print_r($r)."
                Count col:".count($e)."
            </pre></p>");
  }
  else return false;
  }



  /**
    SELECT de contenu
  */
  function select($data){
    $this->retour_debugg[]="<hr />".$data."<br />";
    $g=array();

    if ($this->table == true){
      $l=strlen($data);
      $p_from=strrpos($data,' FROM ');
      $select=trim( substr(ereg_replace('.{0,1}(SELECT)','',$data),0,$p_from-6) );
      $from=trim( ereg_replace('(.*FROM.|.WHERE.*)','',$data) )."\n";

      ## Detection presence 	 WHERE
      if (ereg('WHERE',$data)) { // pas liste 1
        $niv_1=false;
        $p_where=strrpos($data,' WHERE ');
      }
      else { // liste element
      $niv_1=true;
      }
      ## detection
      if (ereg('.ORDER.BY.',$data)){
        $p_orderby=strrpos($data,' ORDER BY ');
        $this->where = $where=trim( substr(ereg_replace('.*WHERE','',$data),0,$p_orderby) );
        $this->orderby = $orderby=trim( substr(ereg_replace('.*ORDER.BY','',$data),0,$l) );
      } else   {
        $this->where =  $where=trim( substr(ereg_replace('.*WHERE','',$data),0,$l) );
        $this->orderby='';
      }

      ## detectetin
      if (ereg('.LIKE.',$where)) $this->like = $like=trim(str_replace("'",'',eregi_replace ('.*LIKE.','',$where)) );

      ## Option des Champs du SELECT, Si Null, alors select = *
      if (eregi('[*]',$select)){
        $tous=true;
      /* REcuperation des elments dispo pour select, le nom des colonnes */
      }
      else {
        $tous = false;
        $r=explode(',',$select);
        $this->nbr_col_sortie=count($r);
        ## EXtraction
        while ($row = each($r))
          if (tep_not_null($row['value'])) {
            $key= trim($row['value']);
            $val=trim(substr($row['value'],0,$l));
            $les_select[$key]=$val;
          }
      }

      ## Option WHERE extraction conditions + ID
      if(tep_not_null($where)) {
        $r=$this-> ele_where($where);
        $col=$r['col'];
        $condition=$r['condition'];
        /* specifique a 1key de tableau 2d pour evaluer cette colonne a ID */
        if (!tep_not_null($this->id)&&(tep_not_null($col)&&strtoupper($col)=='ID')){
          $this->id = $condition;
          unset($col);unset($condition);
        }
      }
$this->retour_debugg[]='ID '.$this->id ."<br />";
$this->retour_debugg[]='prefix '.$this->prefix."<br />";
$this->retour_debugg[]='SELECT tous: ';
$this->retour_debugg[]='<pre> Les SELECTS';
$this->retour_debugg[]=@$les_select;
$this->retour_debugg[]='</pre>';

        /* Ne prend en compte dans le tableau de sortie qu edes element present dans select*/
        $cfg_admin=(isset($this->conf_open))? $this->conf_open : new configfile($this->table) ;
        if ($niv_1) {
          /* Liste toutes les lignes  */
          $cfg_admin->prefix = $this->prefix ;
          $y= $cfg_admin->getVar_t();
          if (is_array($y)) reset($y);
          else return false;
          while ($row = each($y))
            if (is_array($row['value'])){
              while ($rs=each($row['value']))
                /* Liste toutes les colonnes OR Exclusion des colonne non demende, cf SELECT */
                 if ( ($tous !=false)OR(in_array($rs['key'],$les_select)) ) $g[$row['key']][$rs['key']] = $rs['value'];
            }
            elseif ( ($tous !=false)OR(in_array($row['key'],$les_select))) $g[$row['key']] = $row['value'];
        }else { // liste WHERE
          $cfg_admin->prefix = $this->prefix; //$this->prefix ;
          $y= $cfg_admin->getVar_t($this->id,@$col,@$condition);
          if (!$y&&isset($cfg_admin->error))   return "<p class='erreur'>".ERROR.REQUETE_NOT_OK."</p><pre class='erreur'>".$data." \n\n".$cfg_admin->error." </pre>" ;
$this->retour_debugg[]='retour get_Var_t<pre>';
$this->retour_debugg[]=$y;
$this->retour_debugg[]='</pre>';

        $cpt_tbal_simple=$cpt_tbaldbl=0;
        if (is_array($y)){
        while ($row = each($y))
          if (is_array($row['value'])){
            $cpt_tbaldbl++;
            foreach($row['value'] as $clef=>$val){
              /* Liste toutes les colonnes OR Exclusion des colonne non demende, cf SELECT */
              if( ($tous !=false)OR (in_array($clef,$les_select)) ) $g[$row['key']][$clef] =$val;
               }
          }
          elseif ( ($tous !=false) OR (in_array($row['key'],$les_select)) ) $g[$row['key']] = $row['value'];
        }
      }
      if (!$cfg_admin) $g.= "<p class='erreur'>".ERROR." CLASS.sql_php </p>" ;
      return $this->sortie_sql($g);
    }
    else return false;
  }
}

?>