PHPdatatable服务器端处理(2)

来源：互联网发布：爱淘宝红包不中编辑：程序博客网时间：2024/05/01 01:20

以下是做项目时涉及到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;

}

0 0