/*
 *  ------------------------------連接函數(shù)--------------------------------
 */
/**
 * 連接sql_server數(shù)據(jù)庫(kù)
 * @param arr $data
 */
function sp_sqlserver() {
    $serverName = C('DB_HOST'); //數(shù)據(jù)庫(kù)服務(wù)器地址
    $uid = C('DB_USER');    //數(shù)據(jù)庫(kù)用戶名
    $pwd = C('DB_PWD'); //數(shù)據(jù)庫(kù)密碼
    $connectionInfo = array("UID"=>$uid, "PWD"=>$pwd, "Database"=>C('DB_NAME'));
    $conn = sqlsrv_connect($serverName, $connectionInfo);
    if( $conn == false){
        sp_error("數(shù)據(jù)庫(kù)連接失敗");
    } else {
        return $conn;
    }
}

/**
 * 字符轉(zhuǎn)換
 * @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;
}

/**
 * 字符轉(zhuǎn)換 GBK轉(zhuǎn)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;
}

/*
 *  ------------------------------查詢函數(shù)--------------------------------
 */
/**
 * sqlsrv 普通查詢
*/
function sp_srvquery($table,$fields="*",$order='id',$sec='desc',$where='1=1') {
    $return = array();//連接數(shù)據(jù)庫(kù)
    $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 分頁(yè)查詢
*/
function sp_srvlimit($table,$fields="*",$page=1,$limit=10,$where='') {
    $return = array();//連接數(shù)據(jù)庫(kù)
    
    $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 統(tǒng)計(jì)查詢
*/
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 雙表聯(lián)合查詢
*/
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;
}