public function actionMonitor()
{
if (isset($_POST["dtGridPager"])) {
$dtGridPager = $_POST["dtGridPager"];
$pager = json_decode($dtGridPager, true);
$sql = "SELECT * FROM hardware_view";
DtGridUtils::queryForDTGrid($sql, $pager, $this->_db->getConnection());
exit;
}
$arrUser = $this->machineModel->getUserList();
$arrTmp = [];
if (!empty($arrUser)) {
foreach ($arrUser as $user) {
$arrTmp[$user['id']] = $user['name'];
}
}
$jsonUser = json_encode($arrTmp);
$this->render('监控报表', 'machine/monitor.php', ['jsonUser' => $jsonUser]);
}
<?php
require "../config/config.inc.php";
require "../utils/DtGridUtils.class.php";
require "../utils/ExportUtils.class.php";
require "../utils/QueryUtils.class.php";
require "../utils/ParamsUtils.class.php";
require "../lib/pdf/fpdf.php";
require "../lib/pdf/chinese.php";
require "../lib/excel/PHPExcel.php";
require '../lib/excel/PHPExcel/IOFactory.php';
require '../lib/excel/PHPExcel/Writer/Excel5.php';
$dtGridPager = $_POST["dtGridPager"];
$pager = json_decode($dtGridPager, true);
$sql = "select u.* from user u where 1=1 ";
$args = array();
// 判断是否包含自定义参数
$parameters = $pager["parameters"];
if ($parameters && $parameters["like_user_code_or_user_name"]) {
$like_user_code_or_user_name = $parameters["like_user_code_or_user_name"];
if ($like_user_code_or_user_name != null && trim($like_user_code_or_user_name) != "") {
$sql .= "and u.user_code like ? or u.user_name like ? ";
array_push($args, "%" . $like_user_code_or_user_name . "%");
array_push($args, "%" . $like_user_code_or_user_name . "%");
}
}
DtGridUtils::queryForDTGrid($sql, $pager, $config, $args);
/**
* 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);
}
/**
* 导出Pdf
* @param request 请求对象
* @param response 响应对象
* @param pager dtGrid对象
* @param exportDatas 导出的数据
* @param fileName 文件名
* @throws Exception
*/
static function exportPdf($pager, $exportDatas, $fileName)
{
// 定义PDF文件
$pdf = new PDF_Chinese("L");
$pdf->AddGBFont();
$pdf->AddPage();
$pdf->SetFont('GB', '', 9);
$pdf->SetFillColor(47, 151, 210);
$pdf->SetDrawColor(33, 33, 33);
$pdf->SetTextColor(255, 255, 255);
// 循环一次获取出宽度
$widths = array();
if ($pager["exportColumns"] != null && count($pager["exportColumns"]) > 0) {
// 循环写入表头
foreach ($pager["exportColumns"] as $column) {
array_push($widths, $pdf->GetStringWidth(iconv("UTF-8", "GBK", $column["title"])) + 6);
}
// 判断表中是否有数据
if ($exportDatas != null && count($exportDatas) > 0) {
// 循环写入表中数据
foreach ($exportDatas as $record) {
$i = 0;
foreach ($pager["exportColumns"] as $column) {
$content = $record[$column["id"]];
// 如果内容未被处理则进行格式化
if (!$pager["exportDataIsProcessed"]) {
$content = DtGridUtils::formatContent($column, $content);
}
$width = $pdf->GetStringWidth(iconv("UTF-8", "GBK", $content)) + 6;
if ($widths[$i] < $width) {
$widths[$i] = $width;
}
$i++;
}
}
}
}
// 判断一下表头数组是否有数据
if ($pager["exportColumns"] != null && count($pager["exportColumns"]) > 0) {
// 循环写入表头
$i = 0;
foreach ($pager["exportColumns"] as $column) {
$pdf->Cell($widths[$i], 6, iconv("UTF-8", "GBK", $column["title"]), 1, 0, "C", 1);
$i++;
}
$pdf->SetTextColor(44, 44, 44);
$pdf->Ln();
// 判断表中是否有数据
if ($exportDatas != null && count($exportDatas) > 0) {
// 循环写入表中数据
foreach ($exportDatas as $record) {
$i = 0;
foreach ($pager["exportColumns"] as $column) {
$content = $record[$column["id"]];
// 如果内容未被处理则进行格式化
if (!$pager["exportDataIsProcessed"]) {
$content = DtGridUtils::formatContent($column, $content);
}
$pdf->Cell($widths[$i], 6, iconv("UTF-8", "GBK", $content), 1, 0, "C", 0);
$i++;
}
$pdf->Ln();
}
}
}
$pdf->Output($fileName . ".pdf", "D");
}
