实现效果如下：
重点：使用mysql查询的limit和offset
原生SQL写法：
select c.id as deptid,c.name as department,position,a.name staffname,'2024-11' as shijian
,CASE WHEN b.shijian IS NULL THEN 'no' ELSE 'yes' END AS submit
from fa_wecom_staff a left JOIN fa_kpi_table b on a.userid=b.staff_id AND shijian = '2024-11'
LEFT JOIN fa_wecom_department c ON a.department=c.id
WHERE phone<>'' AND b.shijian IS NOT NULL
GROUP BY a.name,userid,b.shijian
LIMIT 100 OFFSET 1;
limit对应分页插件的参数pageSize，offset对应分页参数的pageNum，计算公式如下：
offset = pageNum==1 ? 1 : (pageNum-1)*pageSize;
后端写法（php）
public function searchnotsubmit($pageNum=1,$pageSize=10,$shijian=null,$department_id=null,$department_value=null,$status=null){
$where = ' AND 1=1';
if(!empty($department_id)){
$where .= ' AND c.id in ('.$department_id.')';
}
if(!empty($department_value)){
$where .= ' AND c.name in ("'.$department_value.'")';
}
if(!empty($status)){
if($status=='未提交') $where .= ' AND b.shijian IS NULL';
if($status=='已提交') $where .= ' AND b.shijian IS NOT NULL';
}
$sql = "select c.id as deptid,c.name as department,position,a.name staffname,'$shijian' as shijian
,CASE WHEN b.shijian IS NULL THEN '未提交' ELSE '已提交' END AS submit
from fa_wecom_staff a left JOIN fa_kpi_table b on a.userid=b.staff_id AND shijian = '$shijian'
LEFT JOIN fa_wecom_department c ON a.department=c.id
WHERE phone<>'' $where GROUP BY a.name,userid,b.shijian";
$total = count(Db::query($sql));
$offset = $pageNum==1 ? 1 : ($pageNum-1)*$pageSize;
$row = Db::query($sql." LIMIT $pageSize OFFSET $offset;");
$this->success('查询成功',['total'=>$total,'data'=>$row]);
}前端局部代码（pagination分页组件）
<el-pagination @size-change="onHandleSizeChange" @current-change="onHandleCurrentChange" class="mt15"
:pager-count="10" :page-sizes="[10, 20, 30, 50, 100, 200, 500]" v-model:current-page="search_params.pageNum" background
v-model:page-size="search_params.pageSize" layout="total, sizes, prev, pager, next, jumper"
:total="tableData.total">
</el-pagination>完整前端页面（vue3）
<template>
<div v-if="1" class="system-dic-container layout-padding">
<NoticeBar
text="🎉🎉🔥功能更新： "
/>
<el-segmented v-if="true" v-model="value" :options="options" size="default" @change="handleSp(value)" />
<el-card shadow="hover" class="layout-padding-auto">
<div v-if="1" class="system-user-search d_flex mb15">
<div class="search_info mb15">
<div class="inline_block mb15">
<span>部门下拉：</span>
<el-cascader
v-model="search_params.d"
:options="alldepartment"
:props="alldepartmentProps"
show-all-levels="false"
placeholder="下拉/输入部门名称"
filterable
@change="handleChangeDept"
/>
</div>
<div class="inline_block mb15">
<span>部门：</span>
<el-input
v-model="search_params.department_value"
style="width: 240px"
placeholder="请输入部门名称"
clearable
/>
</div>
<!-- <div class="ml10 inline_block mb15">
<span>部门成员：</span>
<el-input
v-model="search_params.staff_value"
style="width: 240px"
placeholder="请输入姓名"
clearable
/>
</div>-->
<div class="ml10 inline_block mb15">
<span>流程状态：</span>
<el-select v-model="search_params.status" :filterable="true" placeholder=""
style="width: 240px">
<el-option
v-for="(item,index) in ['已提交','未提交']"
:key="index"
:label="item"
:value="item"
/>
</el-select>
</div>
<div class="ml10 inline_block mb15">
<span>绩效日期：</span>
<el-date-picker v-model="search_params.shijian" type="month" placeholder="选择绩效年月"
@change="slideMonthFun">
</el-date-picker>
</div>
</div>
<div class="search_btn">
<el-button size="large" type="primary" class="ml10" @click="searchFun()">
<el-icon><ele-Search /></el-icon>
查询
</el-button>
<el-button size="large" type="success" class="ml10" @click="resetFun()">
<el-icon><ele-RefreshRight /></el-icon>
重置
</el-button>
</div>
</div>
<el-table id="my-table1" :data="tableData.data" v-loading="tableData.loading" style="width: 100%"
:height="cardDetailsTableHeight ? cardDetailsTableHeight-100 : 500"
>
<!-- <el-table-column type="selection" width="55" /> -->
<el-table-column type="index" label="序号" width="80" />
<el-table-column prop="department" label="部门" show-overflow-tooltip></el-table-column>
<el-table-column prop="position" label="岗位名称" show-overflow-tooltip></el-table-column>
<el-table-column prop="staffname" label="姓名" show-overflow-tooltip></el-table-column>
<el-table-column prop="submit" label="状态" show-overflow-tooltip>
<template #default="scope">
<el-tag type="success" v-if="scope.row.submit == '已提交'">已提交</el-tag>
<el-tag type="error" v-else-if="scope.row.submit == '未提交'">未提交</el-tag>
</template>
</el-table-column>
<el-table-column prop="shijian" label="绩效日期" show-overflow-tooltip></el-table-column>
<el-table-column label="操作" width="150">
<template #default="scope">
<el-button size="small" text type="primary"
@click="preview">查看</el-button>
<el-button size="small" text type="primary"
@click="notice">发通知</el-button>
</template>
</el-table-column>
</el-table>
<el-pagination @size-change="onHandleSizeChange" @current-change="onHandleCurrentChange" class="mt15"
:pager-count="10" :page-sizes="[10, 20, 30, 50, 100, 200, 500]" v-model:current-page="search_params.pageNum" background
v-model:page-size="search_params.pageSize" layout="total, sizes, prev, pager, next, jumper"
:total="tableData.total">
</el-pagination>
</el-card>
<DicDialog ref="dicDialogRef" @refresh="searchFun()" />
</div>
</template>
<script setup lang="ts" name="systemDic">
import { defineAsyncComponent, reactive, onMounted, ref,nextTick } from 'vue';
import { ElMessage, ElNotification } from 'element-plus';
import { Session } from '/@/utils/storage';
import { formatDate } from '/@/utils/formatTime';
import FileSaver from 'file-saver'
import * as XLSX from 'xlsx';
import { getAlldepartmentlistApi, hrApi } from '/@/api/kpi/index';
// 引入组件
const DicDialog = defineAsyncComponent(() => import('/@/views/hr/dialog/dialogKPITemplate.vue'));
const NoticeBar = defineAsyncComponent(() => import('/@/components/noticeBar/index.vue'));
const value = ref('全部')
const options = ['全部', '已提交', '未提交']
function handleSp(e: any) {
if (e == '全部') {
search_params.value.status = ''
getTableData()
} else {
search_params.value.status = e
getTableData()
}
}
const alldepartment = ref(null)
// 定义变量内容
const dicDialogRef = ref();
const tableData = reactive({
data: [],
total: 0,
loading: false,
});
// 表格高度
const cardDetailsTableHeight: any = ref()
// 动态设置表格高度
const getCardDetailsTableHeight = () => {
let tableH = 370; //距离页面下方的高度
let tableHeightDetil = window.innerHeight - tableH;
if (tableHeightDetil <= 100) {
cardDetailsTableHeight.value = 100;
} else {
cardDetailsTableHeight.value = window.innerHeight - tableH;
}
};
// 监听窗口变化动态设置表格高度
window.onresize = () => {
getCardDetailsTableHeight()
}
// 查询参数
const search_params = ref({
shijian: formatDate(new Date(), 'YYYY-mm'),
department_value: '',
department_id: '',
staff_value: '',
status: null,
d:null,
pageNum: 1, //当前页码
pageSize: 10, //每页显示数量
})
function preview () {
ElMessage.info('查看功能尚未开发')
}
function notice () {
ElMessage.info('通知功能尚未开发')
}
//请求表格数据
const getTableData = async () => {
tableData.loading = true;
// 默认加上当前审批者id
let res = await hrApi().searchNotSubmit(search_params.value)
tableData.loading = false;
if (res.code === 1) {
tableData.data = res.data.data;
tableData.total = res.data.total;
} else if (res.code == 0) {
ElMessage.error(res.msg)
}
};
// 1、初次渲染，获取部门信息
getdepartmentlistFun()
async function getdepartmentlistFun() {
getTableData()
}
// 查询
const searchFun = () => {
getTableData()
}
// 重置
const resetFun = () => {
search_params.value = {
shijian: '',
department_value: '',
department_id: '',
staff_value: '',
status: null,
d:null,
pageNum: 1, //当前页码
pageSize: 10, //每页显示数量
}
getTableData()
}
// 切换月份
const slideMonthFun = (e: any) => {
if (e != null) {
search_params.value.shijian = formatDate(e, 'YYYY-mm');
}
}
// function changePageFun() {
// getTableData()
// }
// 每页显示数量改变
const onHandleSizeChange = (val: number) => {
search_params.value.pageSize = val;
getTableData()
};
// 分页改变
const onHandleCurrentChange = (val: number) => {
search_params.value.pageNum = val;
getTableData()
};
// const alldepartmentProps = {multiple: true,emitPath:true}
// // 部门下拉改变
const handleChangeDept = (val: any) => {
search_params.value.department_id = val.join(',');
};
// 页面加载时
onMounted(() => {
getCardDetailsTableHeight()
nextTick(() => {
// console.log('DOM已更新');
})
getAlldepartmentlistApi().then((res)=>{
if(res.code==1){
alldepartment.value = res.data
}
})
});
</script>
<style lang="scss" scoped>
.calculate_status {
background-color: #fff;
margin-bottom: 10px;
border: 1px solid #e4e7ed;
border-radius: 4px;
padding: 10px 15px;
.item_sum {
display: flex;
justify-content: space-between;
align-items: center;
overflow: hidden;
.li {
display: flex;
flex-direction: column;
align-items: center;
padding: 0 5px;
.label {
margin-bottom: 7px;
font-size: 15px;
color: #7f7f7f;
}
.num {
font-size: 18px;
color: #535353;
&.has_num {
color: #e51515;
font-weight: bold;
}
}
}
}
}
.inline_block {
display: inline-block;
}
.d_flex {
display: flex;
flex-wrap: wrap;
}
</style>
![[Qt platform plugin问题] Could not load the Qt platform plugin “xcb“](https://i-blog.csdnimg.cn/direct/c20868052b6849d08a9c782906e59fc7.png)
