<?php
/**
 * php mssql db tool
 * insert($table,$data) table:表名 data:插入的字段、值数组
 * delete($table,$condition) [字段名] '值' (条件)
 * update($table,$data,$condition)
 * select($table,$filter="*",$condition="1=1") 返回查询结果集
 */



class mssqldb{
    
    private  $host = '192.168.10.165';
    private $conn;
    private  $arr = array(
        "Database"=>"AIS20160108124100", 
        "UID"=>"sa",
        "PWD"=>"sa-2017"
    );
    public  $test='test';
            
            
    function __construct() {
        $link = sqlsrv_connect($this->host, $this->arr);
        if($link){
            $this->conn = $link;
        }else{
            echo "error db connect false!";
        }
    }

    function query($sql){
        zlog($sql);
        $params = array();
        $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
        return sqlsrv_query($this->conn,$sql,$params, $options);//返回可移动的结果集
        //return sqlsrv_query($this->conn,$sql);
    }
    
    
    function error(){
        //var_dump(sqlsrv_errors());
        return sqlsrv_errors();
    }
    
    private function filter_data($data){
        $keys = array_keys($data);
        $values = array_values($data);
        foreach($keys as &$key){
            $key="[$key]";
        }
        foreach($values as &$value){
            if(is_string($value)){
                $value = "'$value'";
            }else if(is_array($value)){
                if(is_string($value[1])){
                    $value[1] = "'{$value[1]}'";
                }
            }
        }
        return [$keys,$values];
    }

    private function insert_id() {
        $id = mysqli_insert_id($this->conn);
        return $id;
    }

    private function affected_num($res){
        return sqlsrv_rows_affected($res);
    }
            
    function insert($table,$data){
        list($keys,$values) = $this->filter_data($data);
        $sql = "INSERT INTO [$table](";
        $sql .= implode(",",$keys);
        $sql .= ") VALUES(";
        $sql .= implode(",",$values);
        $sql .= ")";  
        //echo $sql."</br>";
        $res = $this->query($sql);
        
        if($this->error()){
            return $this->error();
        }else{
            return $this->affected_num($res);
        }
    }

   
    function  delete($table,$condition){
        $sql = "DELETE FROM [$table] WHERE ($condition)";
        echo $sql;
        $res = $this->query($sql);
        if($this->error()){
            return $this->error();
        }else{
            return $this->affected_num($res);
        }
    }

    //UPDATE [t_Supplier] SET [FSaleMode]='10573', [FName]='常州康泽橡塑机械有限公司1' WHERE ([FItemID]='11463')
    //UPDATE [t_Supplier] set [FName] = '测试公司eeee', [FItemID] = '11463' where ([FItemID]='11463')
    function update($table,$data,$condition){
        list($keys,$values) = $this->filter_data($data);
        $sql = "UPDATE  [$table] set ";
        $count = count($keys);
        for($i=0;$i<$count;$i++){
            if($i + 1 == $count){
                $sql .= " {$keys[$i]} = {$values[$i]}";
            }else{
                $sql .= " {$keys[$i]} = {$values[$i]},";
            }
        }
        $sql .= " where ($condition)";
        echo $sql;
        $res = $this->query($sql);
        if($this->error()){
            return $this->error();
        }else{
            return $this->affected_num($res);
        }
    }

    
  
    function select($table,$filter="*",$condition="1=1"){
        $sql = "select $filter from $table where $condition";
        //echo $sql."</br>";
        $res = $this->query($sql);
        if($this->error()){
            return FALSE;
        }
        
        /**根据结果集的行数返回
        sqlsrv_fetch_array($res,fetchType)
        SQLSRV_FETCH_ASSOC，关联数组
        SQLSRV_FETCH_NUMERIC索引数组
        SQLSRV_FETCH_BOTH（默认值）。
        **/
        if(sqlsrv_num_rows($res) == 1){
            return sqlsrv_fetch_array($res);
        }elseif (sqlsrv_num_rows($res) > 1) {
           while ($row = sqlsrv_fetch_array($res)){
               $rows[] = $row;
           } 
           return $rows;
        }else{
            return false;
        }
    }
    
    
    
    
    
    
}





?>