/**
* 拼接单条的高级排序SQL
* @param sortSql 排序SQL
* @param field 字段信息
* @param logic 逻辑符号 0-asc 1-desc
*/
static function getSingleAdvanceQuerySortSql($field, $logic)
{
// 获取左括号内容、右括号内容、逻辑符号内容
$logic = QueryUtils::getSortLogicContent($logic);
// 根据条件类型拼接SQL
return " " . $field . " " . $logic . ", ";
}
/**
* DTGrid的查询方法
* @param sql 查询SQL
* @param pager 传递的Pager参数对象
* @return 包含查询结果集的Pager
* @throws Exception
*/
static function queryForDTGrid($sql, $pager, $db_config)
{
try {
if (is_resource($db_config)) {
$link = $db_config;
} else {
$link = mysql_connect($db_config['dbhost'], $db_config['dbuser'], $db_config['dbpass']);
mysql_select_db($db_config['dbname'], $link);
}
// 处理导出
if ($pager["isExport"]) {
// 如果是全部导出数据
if ($pager["exportAllData"]) {
// 获取快速查询条件SQL、高级查询条件SQL
$fastQuerySql = QueryUtils::getFastQuerySql($pager["fastQueryParameters"]);
$advanceQueryConditionSql = QueryUtils::getAdvanceQueryConditionSql($pager["advanceQueryConditions"]);
// 获取排序SQL
$advanceQuerySortSql = QueryUtils::getAdvanceQuerySortSql($pager["advanceQuerySorts"]);
// 查询结果集放到信息中
$resultSql = "select * from (" . $sql . ") t where 1=1 " . $fastQuerySql . $advanceQueryConditionSql . $advanceQuerySortSql;
$result = mysql_query($resultSql, $link);
$exportDatas = array();
while ($row = mysql_fetch_array($result)) {
array_push($exportDatas, $row);
}
$pager["exportDatas"] = $exportDatas;
}
ExportUtils::export($pager);
return;
}
// 映射为int型
$pageSize = $pager["pageSize"];
$startRecord = $pager["startRecord"];
$recordCount = $pager["recordCount"];
$pageCount = $pager["pageCount"];
// 获取快速查询条件SQL、高级查询条件SQL
$fastQuerySql = QueryUtils::getFastQuerySql($pager["fastQueryParameters"]);
$advanceQueryConditionSql = QueryUtils::getAdvanceQueryConditionSql($pager["advanceQueryConditions"]);
// 获取排序SQL
$advanceQuerySortSql = QueryUtils::getAdvanceQuerySortSql($pager["advanceQuerySorts"]);
// 获取总记录条数、总页数可能没有
$countSql = "select count(*) from (" . $sql . ") t where 1=1 " . $fastQuerySql . $advanceQueryConditionSql . $advanceQuerySortSql;
$result = mysql_query($countSql, $link);
$recordCount = 0;
while ($row = mysql_fetch_row($result)) {
$recordCount = $row[0];
}
$pager["recordCount"] = $recordCount;
$pageCount = ceil($recordCount / $pageSize);
$pager["pageCount"] = $pageCount;
// 查询结果集放到信息中
$resultSql = "";
$resultSql .= "select * from (" . $sql . ") t where 1=1 " . $fastQuerySql . $advanceQueryConditionSql . $advanceQuerySortSql . " limit " . $startRecord . ", " . $pageSize;
$result = mysql_query($resultSql, $link);
$dataList = array();
while ($row = mysql_fetch_array($result)) {
array_push($dataList, $row);
}
$pager["exhibitDatas"] = $dataList;
// 设置查询成功
$pager["isSuccess"] = true;
} catch (Exception $e) {
// 设置查询失败
$pager["isSuccess"] = false;
}
echo json_encode($pager);
}
/**
* DTGrid的查询方法
* @param sql 查询SQL
* @param pager 传递的Pager参数对象
* @return 包含查询结果集的Pager
* @throws Exception
*/
static function queryForDTGrid($sql, $pager, $db_config, &$args)
{
if (!$args) {
$args = array();
}
try {
$mysqli = new mysqli($db_config['dbhost'], $db_config['dbuser'], $db_config['dbpass'], $db_config['dbname']);
mysqli_query($mysqli, "SET NAMES utf8");
// 检查连接是否被创建
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit;
}
// 处理导出
if ($pager["isExport"]) {
// 如果是全部导出数据
if ($pager["exportAllData"]) {
// 获取快速查询条件SQL、高级查询条件SQL
$fastQuerySql = QueryUtils::getFastQuerySql($pager["fastQueryParameters"], $args);
$advanceQueryConditionSql = QueryUtils::getAdvanceQueryConditionSql($pager["advanceQueryConditions"], $args);
// 获取排序SQL
$advanceQuerySortSql = QueryUtils::getAdvanceQuerySortSql($pager["advanceQuerySorts"]);
// 查询结果集放到信息中
$resultSql = "select * from (" . $sql . ") t where 1=1 " . $fastQuerySql . $advanceQueryConditionSql . $advanceQuerySortSql;
if ($stmt = $mysqli->prepare($resultSql)) {
$types = "";
foreach ($args as $arg) {
if (is_numeric($arg)) {
$types .= "i";
} else {
$types .= "s";
}
}
$execute_args = $args;
array_splice($execute_args, 0, 0, $types);
if (count($args) != 0) {
call_user_func_array(array($stmt, "bind_param"), ParamsUtils::refValues($execute_args));
}
$stmt->execute();
$result = array();
$stmt->bind_result($result);
$exportDatas = array();
while ($row = mysql_fetch_array($result)) {
array_push($exportDatas, $row);
}
$pager["exportDatas"] = $exportDatas;
$stmt->close();
}
}
ExportUtils::export($pager);
return;
}
// 映射为int型
$pageSize = $pager["pageSize"];
$startRecord = $pager["startRecord"];
$recordCount = $pager["recordCount"];
$pageCount = $pager["pageCount"];
// 获取快速查询条件SQL、高级查询条件SQL
$fastQuerySql = QueryUtils::getFastQuerySql($pager["fastQueryParameters"], $args);
$advanceQueryConditionSql = QueryUtils::getAdvanceQueryConditionSql($pager["advanceQueryConditions"], $args);
// 获取排序SQL
$advanceQuerySortSql = QueryUtils::getAdvanceQuerySortSql($pager["advanceQuerySorts"]);
// 获取总记录条数、总页数可能没有
$countSql = "select count(*) from (" . $sql . ") t where 1=1 " . $fastQuerySql . $advanceQueryConditionSql . $advanceQuerySortSql;
$recordCount = 0;
if ($stmt = $mysqli->prepare($countSql)) {
$types = "";
foreach ($args as $arg) {
if (is_numeric($arg)) {
$types .= "i";
} else {
$types .= "s";
}
}
$execute_args = $args;
array_splice($execute_args, 0, 0, $types);
if (count($args) != 0) {
call_user_func_array(array($stmt, "bind_param"), ParamsUtils::refValues($execute_args));
}
$stmt->execute();
$stmt->bind_result($result);
while ($stmt->fetch()) {
$recordCount = $result;
}
$stmt->close();
}
$pager["recordCount"] = $recordCount;
$pageCount = $recordCount / $pageSize + ($recordCount % $pageSize > 0 ? 1 : 0);
$pager["pageCount"] = $pageCount;
// 查询结果集放到信息中
$resultSql = "";
$resultSql .= "select * from (" . $sql . ") t where 1=1 " . $fastQuerySql . $advanceQueryConditionSql . $advanceQuerySortSql . " limit ?, ?";
array_push($args, $startRecord);
array_push($args, $pageSize);
$dataList = array();
if ($stmt = $mysqli->prepare($resultSql)) {
$types = "";
foreach ($args as $arg) {
if (is_numeric($arg)) {
$types .= "i";
} else {
$types .= "s";
}
}
$execute_args = $args;
array_splice($execute_args, 0, 0, $types);
if (count($args) != 0) {
call_user_func_array(array($stmt, "bind_param"), ParamsUtils::refValues($execute_args));
}
$stmt->execute();
$dataList = DtGridUtils::fetch($stmt);
$stmt->close();
}
$pager["exhibitDatas"] = $dataList;
// 设置查询成功
$pager["isSuccess"] = true;
} catch (Exception $e) {
// 设置查询失败
$pager["isSuccess"] = false;
}
echo json_encode($pager);
}
