<?php
/**
 *class Banco 
 *Conexão ao Banco de Dados
 *  
 * @author Daniel Andrade - www.madainternet.com.br
 * @todo CRUD Genérico de ações no banco & Paramentros de Conexão
 */
class Banco{
    /**
     * @var string 
     */
    private $host   = "localhost"; 
    private $user   = "sisatos_erp"; // sisatos_erp
    private $pass   = "CQC{%~JB{VJI"; //  CQC{%~JB{VJI
    private $banco  = "sisatos_erp"; // sisatos_erp
    
    /**
     * 
     * Fun��o de conex�o ao Banco de Dados via PDO
     * 
     * Caso n�o conecte, um erro � exibdo
     */
 
    private function conectar() {
    	try {
    		$pdo = new PDO("mysql:host=".$this->host."; dbname=".$this->banco, $this->user, $this->pass);
                $pdo->exec('SET NAMES utf8');
                $pdo->exec('SET character_set_connection=utf8');
                $pdo->exec('SET character_set_client=utf8');
                $pdo->exec('SET character_set_results=utf8');
    		return $pdo;
    	}catch (PDOException $erro){
                throw new Exception("Erro na conexao: ".$erro->getMessage());
    		return false;
    	}
    	
    }
    
    public function insere($tabela, $valores = array(), $sequencia){
            $conexao = self::conectar();
            $conexao->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            
            $sql  = "INSERT INTO ".$tabela;
            
            $totalValores = count($valores);
            $bindValue    = array();
            $campos = " (";
            $insercao = " VALUES (";
            $x = 1;
            
            foreach($valores as $ch=>$vl){
                $bindValue[$ch] = $vl;    
                
                if($x == $totalValores){
                    $campos .= $ch;
                    $insercao .= ":".$ch;
                    $x++;
                }
                
                if($x <  $totalValores){
                    $campos .= $ch.", ";
                    $insercao .= ":".$ch.", ";
                    $x++;
                }
                
                
                
            }
            
            $campos .= ") ";
            $insercao .= ") ";
            
            $sql .= $campos.$insercao;
            
            try{
                $stmt = $conexao->prepare($sql);
                foreach($bindValue as $ch=>$vl){
                    if(is_numeric($vl)) $param_pdo = PDO::PARAM_INT; else $param_pdo = PDO::PARAM_STR;
                    $stmt->bindValue(":".$ch, $vl, $param_pdo);
		}
                $stmt->execute();
                $ultimoId = $conexao->lastInsertId($sequencia);
                
                $conexao = null;
                
                return $ultimoId;
            }catch (PDOException $erro){
                $conexao = null;
                echo $erro->getMessage()." <br />Instrução SQL: ".$sql;
            }
            
            return false;

        }
        /**
         * Função que realiza a busca.
         * 
         * 
         * @param type $tabela
         * @param type $campos
         * @param type $param
         * @param type $join
         * @return false se a busca falhar. Ou array se funcionar
         */
	public function buscar($tabela, $campos, $param, $join = NULL, $order = false){

		$conexao = self::conectar();
                /**
                 * Exibindo os erros possiveis
                 */
		$conexao->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                /**
                 *@var $sql é a instrução MySQL que vai ao banco
                 */
                $sql    = "SELECT ";
                /**
                 * Se a tabela for um array,
                 * 
                 * mais de uma tabela são consultadas.
                 */
                if(is_array($tabela)){
                    $total_tabelas = count($tabela);
                    $x = 1;
                    foreach($tabela as $tabela){
                        if($x <= $total_tabelas){
                            $tbls .= $tabela."," ;
                            $x++;
                        }else{
                            $tbls .= $tabela;
                            break;
                        }
                    }               
                    unset($tabela);
                    $tabela = $tbls;
                }
		
		if(is_array($param)){
                    
                    
			$totalParam = count($param);
			$condicaoSql = " WHERE ";
			$bindValue = array();
			$x = 1;
                        $carac_exec = array(".","(",")");
 
			foreach($param as $ch=>$vl){
                                
                            $ch_alias = $ch;
                            foreach($carac_exec as $carac){
                                if(strstr($ch, $carac)){
                                    $ch_alias = str_replace($carac,"_",$ch_alias); 
                                }
                            }
                            if(is_array($vl) && !strstr($ch, "BETWEEN")){
                                    
                                    $qt_mc        = count($vl);
                                    $condicaoSql .= "(";
                                    $y            = 1;
                                    
                                    foreach($vl as $c=>$v){

                                        if(strstr($c, "LIKE")){
                                            $c       = substr($c, 0, strpos($c, "LIKE"));
                                            $v = "%$v%";
                                            $condicaoSql .= $ch." LIKE :".$ch_alias.$c;
                                        }elseif(strstr($c, "!")){
                                            $c       = substr($c, 0, strpos($c, "!"));
                                            $condicaoSql .= $ch." <> :".$ch_alias.$c;
                                        }else{
                                            $condicaoSql .= $ch." = :".$ch_alias.$c;
                                        }


                                        $bindValue[$ch_alias.$c] = $v;

                                        if($qt_mc == 1) break;
                                        if($y < $qt_mc) {
                                            $condicaoSql .= " OR ";
                                            $y++;	
                                        }elseif ($y == $qt_mc) break;
                                      }
                                      
                                      $condicaoSql .= ") ";
                                      
                                      if($totalParam == 1) break;
                                        if($x < $totalParam) {
                                        $condicaoSql .= " AND ";
                                        $x++;	
                                      }elseif ($x == $totalParam) break;
                                      
                                }else{

                                    if(strstr($ch_alias, "LIKE")){
                                        $ch_alias = substr($ch_alias, 0, strpos($ch_alias, "LIKE"));
                                        $ch       = substr($ch, 0, strpos($ch, "LIKE"));
                                        $vl = "%$vl%";
                                        $condicaoSql .= $ch." LIKE :".$ch_alias;
                                    }elseif(strstr($ch_alias, "!")){
                                        $ch_alias = substr($ch_alias, 0, strpos($ch_alias, "!"));
                                        $ch       = substr($ch, 0, strpos($ch, "!"));
                                        $condicaoSql .= $ch." <> :".$ch_alias;
                                    }elseif(strstr($ch_alias, "<=")){
                                        $ch_alias = substr($ch_alias, 0, strpos($ch_alias, "<="));
                                        $ch       = substr($ch, 0, strpos($ch, "<="));
                                        $condicaoSql .= $ch." <= :".$ch_alias;
                                    }elseif(strstr($ch_alias, "<")){
                                        $ch_alias = substr($ch_alias, 0, strpos($ch_alias, "<"));
                                        $ch       = substr($ch, 0, strpos($ch, "<"));
                                        $condicaoSql .= $ch." < :".$ch_alias;
                                    }elseif(strstr($ch_alias, "BETWEEN")){
                                        $ch           = substr($ch, 0, strpos($ch, "BETWEEN"));
                                        $condicaoSql .= $ch." BETWEEN ";
                                        if(is_array($vl)){
                                            $condicaoSql .= "(:DATAINI) AND (:DATAFIM) ";
                                            $bindValue["DATAINI"] = $vl["DATAINI"];
                                            $bindValue["DATAFIM"] = $vl["DATAFIM"];
                                        }
                                    }else{
                                        $condicaoSql .= $ch." = :".$ch_alias;
                                    }

                                    if(!strstr($ch_alias, "BETWEEN"))
                                        $bindValue[$ch_alias] = $vl;

                                    if($totalParam == 1) break;
                                    if($x < $totalParam) {
                                        $condicaoSql .= " AND ";
                                        $x++;	
                                    }elseif ($x == $totalParam) break;
                            }
                        }
			
		}
                
		
		$sql .= $campos." FROM ".$tabela;
                
                if(!is_null($join)) $sql .= " ".$join." ";    
                
                $sql .= $condicaoSql;
                
                if($order) $sql .= " ".$order;
               
                /*
                echo "<b>".$sql."</b><br><br>";
                
                echo "<pre style='color:#000;'>";
                var_dump($bindValue);
                echo "</pre>";
                */
                
		try{
			$stmt = $conexao->prepare($sql);
			
                        if(is_array($bindValue)){
                            foreach($bindValue as $ch=>$vl){
                                if(is_numeric($vl)) $param_pdo = PDO::PARAM_INT; else $param_pdo = PDO::PARAM_STR;

                                if(strstr($ch, ".")) $ch = str_replace(".","_",$ch);    
                                $stmt->bindValue(":".$ch, $vl, $param_pdo);

                            }
                        }
                        
			$stmt->execute();
			if($stmt->rowCount() > 0){
				$linha = $stmt->fetchAll(PDO::FETCH_ASSOC);
				$retorno = $linha;
				$conexao = null;
				
				return $retorno;
			}else{  
				$conexao = null;
				return false;
			}
		}catch(PDOException $erro){
			$conexao = null;
			echo $erro->getMessage()." ".$sql;
			return false;
		}
		
		return false;
	}
	
	public function alterar($tabela, $dados, $campo, $valor, $extSql = false){
		
		$conexao = self::conectar();
		$conexao->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		
		$totalDados = count($dados);
		$x = 1;
		
		$sql  = "UPDATE $tabela SET ";
		
		foreach($dados as $ch=>$vl){
                                if(strstr($ch, "+")){
                                    $c = substr($ch, 0, strpos($ch, "+"));
                                    $sql .= $c." = ".$c."+:".$c;
                                    $bindValue[$c] = $vl;
                                }elseif(strstr($ch, "-")){
                                    $c = substr($ch, 0, strpos($ch, "-"));
                                    $sql .= $c." = ".$c."-:".$c;
                                    $bindValue[$c] = $vl;
                                }else{
                                    $sql .= $ch." = :".$ch;
                                    $bindValue[$ch] = $vl;
                                }
				if($totalDados == 1) break;
				if($x < $totalDados) {
					$sql .= ",";
					$x++;	
				}elseif ($x == $totalDados) break;
		}
		
		$sql .= " WHERE ".$campo." = :".$campo;
                
                /*
                echo "<pre>";
                echo $sql;
                var_dump($dados);
                echo "</pre>";
                die();
                */
                
                if($extSql) $sql .= " ".$extSql." ";
		
		try{
			$stmt = $conexao->prepare($sql);
			foreach($bindValue as $ch=>$vl){
				if(is_numeric($vl)) $param_pdo = PDO::PARAM_INT; else $param_pdo = PDO::PARAM_STR;
				$stmt->bindValue(":".$ch, $vl, $param_pdo);
			}
			$stmt->bindValue($campo, $valor, PDO::PARAM_INT);
			$stmt->execute();
			$conexao = null;
			
			return true;
		}catch(PDOException $erro){
			$conexao = null;
			new Erro($erro->getMessage()." - Inst. SQL : ".$sql." <br /><br /> Campo: <b>$campo</b> - Valor: <b>$valor</b>", $dados);
			return false;
		}
		
		return false;
		
		
	}
        
        public function excluir($tabela, $param){ //fazer funcao que excluir as coisas do banco
                $conexao = self::conectar();
		$conexao->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                $sql  = "DELETE FROM ".$tabela." ";
                
                if(is_array($param)){
                    $totalParam = count($param);
                    $condicaoSql = " WHERE ";
                    $bindValue = array();
                    $x = 1;
                    foreach($param as $ch=>$vl){
                        if(strstr($ch, "LIKE")){
                            $ch = substr($ch, 0, strpos($ch, "LIKE"));
                            $vl = "%$vl%";
                            $condicaoSql .= $ch." LIKE :".$ch;
                        }elseif(strstr($ch, "!")){
                            $ch = substr($ch, 0, strpos($ch, "!"));
                            $condicaoSql .= $ch." <> :".$ch;
                        }elseif(strstr($ch, ".")){
                            $ch_alias = str_replace(".","_",$ch);
                            $condicaoSql .= $ch." = :".$ch_alias;
                        }else{
                            $condicaoSql .= $ch." = :".$ch;
                        }

                        $bindValue[$ch] = $vl;

                        if($totalParam == 1) break;
                        if($x < $totalParam) {
                                $condicaoSql .= " AND ";
                                $x++;	
                        }elseif ($x == $totalParam) break;
                    }
                }
                
                $sql .= $condicaoSql;
                
                try{
			$stmt = $conexao->prepare($sql);
			
                        if(is_array($bindValue)){
                            foreach($bindValue as $ch=>$vl){
                                if(is_numeric($vl)) $param_pdo = PDO::PARAM_INT; else $param_pdo = PDO::PARAM_STR;

                                if(strstr($ch, ".")) $ch = str_replace(".","_",$ch);    
                                $stmt->bindValue(":".$ch, $vl, $param_pdo);

                            }
                        }
                        
			$stmt->execute();
                        $conexao = null;
			return true;
		}catch(PDOException $erro){
			$conexao = null;
			new Erro($erro->getMessage()."<br class='clear' />".$sql);
			return false;
                        die();
		}
                
        }       
                
}