/*
 *  ------------------------------连接函数--------------------------------
 */
/**
 * 连接sql_server数据库
 * @param arr $data
 */
function sp_sqlserver() {
    $serverName = C('DB_HOST'); //数据库服务器地址
    $uid = C('DB_USER');    //数据库用户名
    $pwd = C('DB_PWD'); //数据库密码
    $connectionInfo = array("UID"=>$uid, "PWD"=>$pwd, "Database"=>C('DB_NAME'));
    $conn = sqlsrv_connect($serverName, $connectionInfo);
    if( $conn == false){
        sp_error("数据库连接失败");
    } else {
        return $conn;
    }
}

/**
 * 字符转换
 * @param arr $data
 */
function sp_charcon($data) {
    if ($data) {
        foreach ($data as $k=>$v) {
            $data[$k] = mb_convert_encoding($v, "GBK", "UTF-8"); 
        } 
    }
    return $data;
}

/**
 * 字符转换 GBK转UTF-8
 * @param arr $data
 */
function sp_charcon_reverse($data) {
    if ($data) {
        foreach ($data as $k=>$v) {
            $data[$k] = mb_convert_encoding($v, "UTF-8", "GBK"); 
        } 
    }
    return $data;
}

/*
 *  ------------------------------查询函数--------------------------------
 */
/**
 * sqlsrv 普通查询
*/
function sp_srvquery($table,$fields="*",$order='id',$sec='desc',$where='1=1') {
    $return = array();//连接数据库
    $sql = " SELECT {$fields} ";
    $sql .= " FROM {$table}";
    $sql .=  " WHERE {$where} ";
    $sql .= " ORDER BY {$order} {$sec}";
    echo $sql;
    $conn = sp_sqlserver();
    $res = sqlsrv_query($conn, $sql);
    while ($data = sqlsrv_fetch_array($res,SQLSRV_FETCH_ASSOC)) {
        $data = sp_charcon_reverse($data);
        $return[] = $data;
    }
    return $return;
}

/**
 * sqlsrv 分页查询
*/
function sp_srvlimit($table,$fields="*",$page=1,$limit=10,$where='') {
    $return = array();//连接数据库
    
    $sql = " SELECT TOP {$limit} {$fields} ";
    $sql .= " FROM {$table}";
    $sql .= " WHERE (id NOT IN";
    $sql .=  " (SELECT TOP ({$limit}*{$page}) id";
    $sql .=  " FROM {$table}";
    $sql .=  " ORDER BY id)) {$where} ";
    $sql .= " ORDER BY id";
    
    $conn = sp_sqlserver();
    $res = sqlsrv_query($conn, $sql);
    while ($data = sqlsrv_fetch_array($res,SQLSRV_FETCH_ASSOC)) {
        $data = sp_charcon_reverse($data);
        $return[] = $data;
    }
    return $return;
}

/**
 * sqlsrv 统计查询
*/
function sp_srvcount($table,$fields="*",$where='1=1') {
    $sql = " SELECT COUNT({$fields}) as count ";
    $sql .= " FROM {$table}";
    $sql .= " WHERE ";
    $sql .=  " {$where} ";
    
    $conn = sp_sqlserver();
    $res = sqlsrv_query($conn, $sql);
    $data = sqlsrv_fetch_array($res,SQLSRV_FETCH_ASSOC);
    return $data['count'];
}

/**
 * sqlsrv 双表联合查询
*/
function sp_srvjoin($table_1,$table_2,$param_1=array(),$param_2=array(), $field, $where="1=1") {
    $return = array();
    //拼接查询字段
    if ($param_1) {
        $str1 = "";
        foreach ($param_1 as $k=>$v) {
            $str1 .= "a." . $v . ",";
        }
        $str1 = substr($str1,0,-1);
    } else {
        $str1 = "a.*";
    }
    if ($param_2) {
        $str2 = "";
        foreach ($param_2 as $k=>$v) {
            $str2 .= "b." . $v . ",";
        }
        $str2 = substr($str2,0,-1);
    } else {
        $str2 = "b.*";
    }
    $sql = " SELECT {$str1},{$str2}";
    $sql .= " FROM {$table_1} a";
    $sql .= " INNER JOIN {$table_2} b";
    $sql .= " ON a.{$field} = b.{$field}";
    $sql .= " where {$where} ";
    
    $conn = sp_sqlserver();
    $res = sqlsrv_query($conn, $sql);
    while ($data = sqlsrv_fetch_array($res,SQLSRV_FETCH_ASSOC)) {
        $data = sp_charcon_reverse($data);
        $return[] = $data;
    }
    return $return;
}