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"); }