1-业务逻辑处理_封装接口函数
/**
* 描述: 业务逻辑处理 - 任务相关接口
*/
const { querySql, queryOne, getUerMessage } = require("../utils/index")
const xlsx = require("node-xlsx")
const jwt = require("jsonwebtoken")
const boom = require("boom")
const { validationResult } = require("express-validator")
const {
CODE_ERROR,
CODE_SUCCESS,
PRIVATE_KEY,
JWT_EXPIRED,
USER_MESSAGE,
} = require("../utils/constant")
const { decode } = require("../utils/user-jwt")
var myData = new Date()
const currentTime = myData.toLocaleString('chinese',{hour12:false})
// 查询填报统计列表
function queryTaskList(req, res, next) {
const err = validationResult(req)
// 如果验证错误，empty不为空
if (!err.isEmpty()) {
// 获取错误信息
const [{ msg }] = err.errors
// 抛出错误，交给我们自定义的统一异常处理程序进行错误返回
next(boom.badRequest(msg))
} else {
let { department, year, month, status } = req.query
//设置没有作为搜索时，将值设置为空字符串
department = department ? department : ""
year = year ? year : ""
month = month ? month : ""
status = status ? status : ""
// console.log(department, year, month, status, "wode")
let query = `select d.id, d.username, d.department, d.year, d.month, d.status, d.time, d.userChinese from filltotal d`
querySql(query).then((data) => {
// console.log('任务列表查询===', data)
if (!data || data.length === 0) {
res.json({
code: CODE_ERROR,
msg: "暂无数据",
data: [],
})
} else {
if (department || year || month || status) {
let query_1 = `select d.id, d.username,d.userChinese, d.department, d.year, d.month, d.status, d.time from filltotal d where (department like '%${department}%') and (year like '%${year}%') and (month like '%${month}%') and (status like '%${status}%') `
querySql(query_1).then((result_1) => {
res.json({
code: CODE_SUCCESS,
msg: "查询数据成功",
data: {
rows: result_1,
total: result_1.length,
},
})
})
} else {
res.json({
code: CODE_SUCCESS,
msg: "查询数据成功",
data: {
rows: data,
total: data.length,
},
})
}
}
})
}
}
//查询历史填报列表
function queryHistoryList(req, res, next) {
const err = validationResult(req)
// 如果验证错误，empty不为空
if (!err.isEmpty()) {
// 获取错误信息
const [{ msg }] = err.errors
// 抛出错误，交给我们自定义的统一异常处理程序进行错误返回
next(boom.badRequest(msg))
} else {
let { username } = req.query
if (username) {
let query = `select d.id, d.username, d.year, d.month, d.time from history d where username='${username}'`
querySql(query).then((data) => {
res.json({
code: CODE_SUCCESS,
msg: "查询数据成功",
data: {
rows: data,
total: data.length,
},
})
})
}
}
}
//查询某人某月份所填报项目列表
function queryTaskProject(req, res, next) {
const err = validationResult(req)
// 如果验证错误，empty不为空
if (!err.isEmpty()) {
// 获取错误信息
const [{ msg }] = err.errors
// 抛出错误，交给我们自定义的统一异常处理程序进行错误返回
next(boom.badRequest(msg))
} else {
let { currentName, year, month } = req.query
// console.log(currentName, month, "wode")
if (!currentName || !month || !year) {
res.json({
code: CODE_ERROR,
msg: "缺少请求字段",
data: null,
})
} else {
let query = `select d.id, d.username, d.project, d.percentage, d.year, d.month from apply d where username='${currentName}' and year='${year}' and month='${month}'`
querySql(query).then((data) => {
res.json({
code: CODE_SUCCESS,
msg: "查询数据成功",
data: {
rows: data,
total: data.length,
},
})
})
}
}
}
//添加某月项目列表
function addTaskProject(req, res, next) {
const err = validationResult(req)
if (!err.isEmpty()) {
const [{ msg }] = err.errors
next(boom.badRequest(msg))
} else {
let { username, year, month, data } = req.body
if (username && year && month && data.length !== 0) {
findTaskProject(username, year, month, 1).then((task) => {
if (task) {
res.json({
code: CODE_ERROR,
msg: "当月已填报",
data: null,
})
} else {
console.log("添加任务")
let query, queryUpdate, queryInsert
data &&
data.forEach((item) => {
query = `insert into apply(username, year, month, project, percentage) values('${username}', '${year}', '${month}', '${item.project}', '${item.percentage}')`
querySql(query).then(() => {
queryUpdate = `update filltotal set time='${currentTime}',status='1' where username='${username}' and year='${year}' and month='${month}'`
// console.log('添加任务===', data)
querySql(queryUpdate).then(()=>{
res.json({
code: CODE_SUCCESS,
msg: "添加数据成功",
data: null,
})
})
})
})
queryInsert = `insert into history(username, year, month, time) values('${username}', '${year}', '${month}', '${currentTime}')`
querySql(queryInsert).then(()=>{})
}
})
}else {
res.json({
code: CODE_ERROR,
msg: "缺少字段",
data: null,
})
}
}
}
//编辑某月项目信息
function editTaskProject(req, res, next) {
const err = validationResult(req)
if (!err.isEmpty()) {
const [{ msg }] = err.errors
next(boom.badRequest(msg))
} else {
let { username, month, year, data } = req.body
let queryDelete, updateTotal, updataHistory
if (username && month && year) {
queryDelete = `delete from apply where username='${username}' and month='${month}' and year='${year}'`
querySql(queryDelete).then(() => {
data &&
data.forEach((item) => {
query = `insert into apply(username, year, month, project, percentage) values('${username}', '${year}', '${month}', '${item.project}', '${item.percentage}')`
querySql(query).then((addData) => {
updateTotal = `update filltotal set time='${currentTime}' where username='${username}' and year='${year}' and month='${month}'`
updataHistory = `update history set time='${currentTime}' where username='${username}' and year='${year}' and month='${month}'`
querySql(updateTotal).then(()=>{})
querySql(updataHistory).then(()=>{})
// console.log('添加任务===', data)
if (!addData || addData.length === 0) {
res.json({
code: CODE_ERROR,
msg: "编辑数据失败",
data: null,
})
} else {
res.json({
code: CODE_SUCCESS,
msg: "编辑数据成功",
data: null,
})
}
})
})
})
}
}
}
// 通过任务名称查询数据是否存在
function findTaskProject(username, year, month, type) {
let query = null
if (type == 1) {
query = `select id, month, year, username from apply where username='${username}' and year='${year}' and month='${month}' `
} else {
query = `select id, month, year, username from filltotal where username='${username}' and year='${year}' and month='${month}' `
}
return queryOne(query)
}
//导出填报统计列表
function exportTotalExcel(req, res, next) {
const err = validationResult(req)
if (!err.isEmpty()) {
const [{ msg }] = err.errors
next(boom.badRequest(msg))
} else {
let { department, year, month } = req.query
//设置没有作为搜索时，将值设置为空字符串
department = department ? department : ""
year = year ? year : ""
month = month ? month : ''
let queryApply = ` select e.userChinese, e.upDepartment, e.departChinese, a.percentage, m.operation, a.project, m.projectCode from apply a inner join filltotal e on a.year=e.year and a.month=e.month and a.username=e.username inner join manage m on a.project=m.project where (e.department like '%${department}%') and (e.year like '%${year}%') and (e.month like '%${month}%') and (e.status ='1')`
// const query = `select d.userChinese d.departChinese from filltotal d where (department like '%${department}%') and (year like '%${year}%') and (month like '%${month}%') and (status ='1') `
querySql(queryApply).then((data) => {
// console.log(data,'55555555555')
if (!data || data.length === 0) {
res.json({
code: CODE_ERROR,
msg: "导出项目列表为空",
data: [],
})
} else {
let transData = []
// transData.push(Object.keys(data[0]))
transData.push(
[`${year ? year : '本'}年${month ? month : '本'}月项目工时与人工成本分摊表`, '', '', '', '', '', '', ''],
['员工与成本信息(HR填写)', '', '', '', '', '项目投入(业务方填写)', '', ''],
['姓名', '一级部门', '二级部门', '应分配总人天', '人工成本', '业务标签', '分摊项目', '项目编码']
)
data.forEach((item) => {
transData.push(Object.values(item))
})
transData.forEach((item,index)=>{
if(index > 2){
item.splice(3,0,'')
}
})
let creatDate = (year && month) ? ` ${year}年${month}月填报统计`: (year && !month) ? ` ${year}年填报统计`: (!year && month) ? ` 每年${month}月填报统计`: '填报统计'
let excelData = [
//数据源
{
name: creatDate,
data: transData,
},
]
// console.log(transData, "transData")
let sheetOptions = {
//表格大小
"!cols": [
{ wch: 10 },
{ wch: 20 },
{ wch: 10 },
{ wch: 15 },
{ wch: 10 },
{ wch: 10 },
{ wch: 30 },
{ wch: 15 },
],
// "!rows": [{ hpx: 40 }, { hpx: 20 }, { hpx: 50 }, { hpx: 30 }],
}
let range = { s: { c: 0, r: 0 }, e: { c: 7, r: 0 } }
let range2 = { s: { c: 0, r: 1 }, e: { c: 4, r: 1 } }
let range3 = { s: { c: 5, r: 1 }, e: { c: 7, r: 1 } }
sheetOptions["!merges"] = [range, range2, range3]
res.setHeader(
"Content-Disposition",
"attachment
)
res.send(xlsx.build(excelData, { sheetOptions }))
}
})
}
}
module.exports = {
queryTaskList,
queryHistoryList,
queryTaskProject,
addTaskProject,
editTaskProject,
exportTotalExcel,
}
后续会单独对某些模块详细讲解。这些都是对接口的封装，在routes引入后，因为route在入口文件中注册，根据express.Router注册路由，达到自定义接口的功能
