以下是做项目时涉及到datatable服务器端处理的代码,大家可以参考一下对于前段发送数据的处理
aoData=[
{"name":"sEcho","value":1},//前台发起请求次数 *draw
{"name":"iColumns","value":5},//表格总列数
{"name":"sColumns","value":""},
{"name":"iDisplayStart","value":0},//指定从哪一条数据开端显示到表格中去 *start
{"name":"iDisplayLength","value":20},//指定一屏显示的条数 *length
{"name":"mDataProp_0","value":"deviceId"},//每列绑定的后台数据
{"name":"mDataProp_1","value":"sensorType"},//每列绑定的后台数据
{"name":"mDataProp_2","value":"sensorNumber"},//每列绑定的后台数据
{"name":"mDataProp_3","value":"sensorValue"},//每列绑定的后台数据
{"name":"mDataProp_4","value":"sentTime"},//每列绑定的后台数据
{"name":"sSearch","value":""},//定义初始的全局搜索条件 *search[value]
{"name":"bRegex","value":false},//定义搜索条件是否当做正则表达式处理
{"name":"sSearch_0","value":""},//value 值表示对第0列按此value值进行搜索
{"name":"bRegex_0","value":false},//为true时搜索条件当做正则表达式处理,为false时当做字符串处理
{"name":"bSearchable_0","value":true},//是否在列上应用过滤/搜索,默认为true
{"name":"sSearch_1","value":""},//同上
{"name":"bRegex_1","value":false},//同上
{"name":"bSearchable_1","value":true},//同上
{"name":"sSearch_2","value":""},//同上
{"name":"bRegex_2","value":false},//同上
{"name":"bSearchable_2","value":true},//同上
{"name":"sSearch_3","value":""},//同上
{"name":"bRegex_3","value":false},//同上
{"name":"bSearchable_3","value":true},//同上
{"name":"sSearch_4","value":""},//同上
{"name":"bRegex_4","value":false},//同上
{"name":"bSearchable_4","value":true},//同上
{"name":"iSortCol_0","value":0},//value值为待排序的列号,列是从0开始计算(每次只能按一列排序)
{"name":"sSortDir_0","value":"asc"},//asc表示待排序列按升序排列,desc表示按降序排列
{"name":"iSortingCols","value":1}, //排序的列数,默认为1,每次操作只允许按某一列排序
{"name":"bSortable_0","value":true},//此列允许排序操作
{"name":"bSortable_1","value":true},//此列允许排序操作
{"name":"bSortable_2","value":true},//此列允许排序操作
{"name":"bSortable_3","value":true},//此列允许排序操作
{"name":"bSortable_4","value":true},//此列允许排序操作
{"name":"startTime","value":"2016-03-01+11:24:56"},
{"name":"endTime","value":"2016-03-01+12:24:56"},
{"name":"subName","value":"温度146bda6-01-1"}
]
//查询Plant传感器发送的数据
public function testSensorData() {
header("Access-Control-Allow-Origin:*");
//header('Content-Type:text/html;charset=utf-8'); 防止json格式的中文乱码输出
$aoData = $_GET['aoData'];
$name = testSensorData;
if ($aoData) {
$search =$this->aoDataProcess($aoData);
}
if (isset($search['subName']) or!empty($search['subName'])) {
if (isset($search['startTime']) and($search['startTime'] == date("Y-m-d H:i:s",strtotime($search['startTime'])))) {
if (isset($search['endTime'])and ($search['endTime'] == date("Y-m-d H:i:s",strtotime($search['endTime'])))) {
if(strtotime($search['startTime']) <= strtotime($search['endTime'])) {
$result ="pass";
$sensorHsDb =M('sensor_history', 'campus_');
$sensorInfo =explode(",", $search['subName']);
$sensorDb = M('sensor','campus_');
for ($i = 0; $i <count($sensorInfo); $i++) {
$sensorMap[$i] =explode("-", substr($sensorInfo[$i], 6));
$map['device'] =$sensorMap[$i][0];
$map['sensortype']= $sensorMap[$i][1];
$map['sensornumber'] = $sensorMap[$i][2];
$sensorid =$sensorDb->where($map)->field(id)->find();
$sensor[$i] =$sensorid['id'];
}
$search['array'] =$sensor;
$dataInfo =$this->dataQueryProcess($name, $sensorHsDb, $search);
} else {
$result = "fail";
$errorCode ="0007";
}
} else {
$result = "fail";
$errorCode ="0006";
}
} else {
$result = "fail";
$errorCode = "0005";
}
} else {
$result = "fail";
$errorCode = "0004";
}
$res =$this->resInfoProcess($result, $name, $errorCode, $dataInfo);
//echo json_encode($res);
echo $res;
}
function aoDataProcess($aoData) {
$aoDataJson =json_decode($aoData);
//定义默认查询条件
$search['sEcho'] = 1;//这个值强制转成整形直接返回给前台,必要
$search['display']['start'] = 0; //表格数据从多少条开始查询
$search['display']['length'] = 20; //查询几条数据
$search['tableColumns'] = array();
$num = 0;
foreach ($aoDataJsonas $value) {
//$search[$value->name] = $value->value;
$nameKey =$value->name;
$valueKey =$value->value;
if ($nameKey =="sEcho") {
$search['sEcho'] = $valueKey;
} elseif ($nameKey== "iDisplayStart") {
$search['display']['start'] =intval($valueKey); //获取前台传过来的查询数目条件
} elseif ($nameKey== "iDisplayLength") {
$search['display']['length'] = intval($valueKey);
} elseif ($nameKey== "iSortCol_0") {
$search['order']['columns'] = $valueKey;//获取前台传过来的排序条件
} elseif ($nameKey== "sSortDir_0") {
$search['order']['sort'] = $valueKey;
} elseif ($nameKey== "sSearch") {
$search['publicSearch']['value'] = $valueKey; //获取前台传过来的过滤条件
} elseif ($nameKey== "bRegex") {
$search['publicSearch']['regex'] = $valueKey;
} elseif(strchr($nameKey, "mDataProp")) {
//$d =array($num, $valueKey);
array_push($search['tableColumns'],$valueKey);
$num++;
} elseif ($nameKey== "subName") {
$search['subName'] = $valueKey;
} elseif ($nameKey== "startTime") {
$search['startTime'] = $valueKey;
} elseif ($nameKey== "endTime") {
$search['endTime'] = $valueKey;
}
}
return $search;
}
/*
* 数据查询
* $name要查询的接口名;
* $m_name:要查询的数据表;
* $search:查询条件
*/
public functiondataQueryProcess($name, $m_name, $search) {
$array =$search['array'];
$tableColumns =$search['tableColumns']; //表格列名
$publicSearch =$search['publicSearch'];
$order =$search['order'];
$startTime =$search['startTime'];
$endTime =$search['endTime'];
$start =$search['display']['start'];
$num =$search['display']['length'];
$qeueyRes['sEcho'] =(int) $search['sEcho'];
$qeueyRes['iTotalRecords'] = 0;
$qeueyRes['iTotalDisplayRecords'] = $qeueyRes['iTotalRecords'];
//全局搜索
switch($publicSearch['regex']) {
case"true":
$searchRege =" like '%.$publicSearch[value].%'"; //搜索值
break;
case"false":
$searchRege =" like '%$publicSearch[value]%'";
break;
case 0:
$searchRege =" like '%$publicSearch[value]%'";
break;
default:
break;
}
foreach ($tableColumnsas $k => $v) {
$column = $column . $v . $searchRege ." or ";
}
$searchColumns =substr($column, 0, -4); //搜索列名
$searchWhere = "where " . $searchColumns;
//排序
$column =$search['order']['columns']; //排序列,从0开始
$orderColumn =$tableColumns[$column]; //排序列名
$orderSort =$search['order']['sort']; //ase desc 升序或者降序
$sort = " orderby " . $orderColumn . " " . $orderSort;
//分页
$limit = " limit$start,$num";
switch ($name) {
//根据用户所选sensor查询并统计在给定时间段内sensor发出的数据以及时间
case testSensorData:
$sensorid =implode("','", $search['array']); // iTotalRecords
$sqlC ="select count(sensorValue) num from (select a.deviceiddeviceId,a.sensortype sensorType,a.sensornumber sensorNumber,b.value sensorValue,b.timesentTime from campus_sensor a,campus_sensor_history b
where a.id=b.sensorid and a.id in ('$sensorid')and b.time between'$startTime' and '$endTime') t";
$sqlD ="select * from (select a.deviceid deviceId,a.sensortype sensorType,a.sensornumbersensorNumber,b.value sensorValue,b.time sentTime from campus_sensora,campus_sensor_history b
where a.id=b.sensorid and a.id in ('$sensorid') and b.time between'$startTime' and '$endTime') t";
if(strlen($publicSearch['value']) > 0) {
$sqlCount= $sqlC;
$sqlData =$sqlD . $searchWhere . $sort . $limit;
$sqlFilter= $sqlC . $searchWhere;
$totalRecords = $m_name->query($sqlCount);
$qeueyRes['iTotalRecords'] =$totalRecords['0']['num'];
$filterRecords = $m_name->query($sqlFilter);
$qeueyRes['iTotalDisplayRecords'] = $filterRecords['0']['num'];
} else {
$sqlCount= $sqlC;
$sqlData =$sqlD . $sort . $limit;
$totalRecords = $m_name->query($sqlCount);
$qeueyRes['iTotalRecords'] = $totalRecords['0']['num'];
$qeueyRes['iTotalDisplayRecords'] = $qeueyRes['iTotalRecords'];
}
$searchData =$m_name->query($sqlData);
if($searchData) {
$qeueyRes['sensorInfo'] = $searchData;
}
break;
default:
break;
}
return $qeueyRes;
}