[JavaScript] 纯文本查看 复制代码
const sql = require('mssql');
const request = require('request');
const config = {
user: '数据库用户名',
password: '数据库密码',
server: '数据库的IP地址',
database: '要连接的数据库名字',
options: {
encrypt: false
}
};
var times = ""
setInterval(() => { //开启一个一秒的定时任务
const currentDate = new Date();
const year = currentDate.getFullYear();
const month = String(currentDate.getMonth() + 1).padStart(2, '0');
const day = String(currentDate.getDate()).padStart(2, '0');
var hour = currentDate.getHours().toString().padStart(2, '0');
if (hour !== times) { //判断现在的“时”是不是和上一个存放在变量times里面的值一样，如果不一样就执行查询发送
sql.connect(config)
.then(() => {
return sql.query(`
SELECT
工号 = b.user_id,
收银员 = b.user_name,
机构 = LEFT(a.branch_no, 2),
日期 = LEFT(CONVERT(VARCHAR(10), a.oper_date, 112), CONVERT(INT, '8')),
总单数 = COUNT(DISTINCT CASE WHEN pos_type = '1' OR pos_type = '2' THEN a.sheet_no ELSE NULL END),
总收入 = SUM(CASE WHEN pos_type = '1' OR pos_type = '2' OR pos_type = '3' THEN d.allpay_amt ELSE 0 END),
优惠金额 = SUM(a.pos_org_amt - a.pos_total_amt),
净收入 = SUM(CASE WHEN pos_type = '1' OR pos_type = '2' OR pos_type = '3' THEN d.allpay_amt ELSE 0 END) - SUM(a.pos_org_amt - a.pos_total_amt),
现金 = SUM(d.pay01_amt),
找零 = SUM(d.pay31_amt),
实收现金 = SUM(d.pay01_amt + d.pay31_amt),
移动支付 = SUM(d.pay51_amt)
FROM
view_pos_sale_master a
JOIN t_sys_user b ON (b.user_id = a.user_id)
JOIN view_pos_sale_pay d ON (a.sheet_no = d.sheet_no)
WHERE
a.pos_status = '9' AND
(
(a.oper_date >= '${year}-${month}-${day}') AND
(a.oper_date <= '${year}-${month}-${day} 23:59:59.997') AND
(LEFT(a.branch_no, 2) IN ('00', '*'))
)
GROUP BY
b.user_id,
b.user_name,
LEFT(a.branch_no, 2),
LEFT(CONVERT(VARCHAR(10), a.oper_date, 112), CONVERT(INT, '8'))
`);
})
.then(result => {
if (result.recordset.length > 0) {
var detail = `### 超市收入情况！\n
### 日期：${result.recordset[0].日期}\n`
let allBill = 0
let allIncome = 0
for (let i = 0; i < result.recordset.length; i++) {
allBill = allBill + result.recordset[i].总单数
allIncome = allIncome + result.recordset[i].净收入
detail += `>------收银员：${result.recordset[i].收银员}-----\n
>工号：<font color=\"info\" >${result.recordset[i].工号}</font>\n
>总单数：<font color=\"info\" >${result.recordset[i].总单数}单</font>\n
>现金：<font color=\"info\" >${result.recordset[i].现金}元</font>\n
>找零：<font color=\"info\" >${result.recordset[i].找零}元</font>\n
>实收现金：<font color=\"info\" >${result.recordset[i].实收现金}元</font>\n
>移动支付：<font color=\"info\" >${result.recordset[i].移动支付}元</font>\n
>总收入：<font color=\"info\" >${result.recordset[i].总收入}元</font>\n
>优惠金额：<font color=\"info\" >${result.recordset[i].优惠金额}元</font>\n
>净收入：<font color=\"info\" >${result.recordset[i].净收入}元</font>
`
if (i === result.recordset.length - 1) {
detail += `>----------合计----------\n
>合计总单数：<font color=\"warning\" >${allBill}单</font>\n
>合计总收入：<font color=\"warning\" >${allIncome.toFixed(2)}元</font>\n
`
}
}
var data = {
"msgtype": "markdown",
"markdown": {
"content": detail
}
}
const wechatOptions = {
url: '这里写微信机器人的Webhook地址',
method: 'POST',
headers: {
'content-type': 'application/json'
},
body: JSON.stringify(data)
};
request(wechatOptions, (err, req, res) => {
console.log(res);
times = hour //这里把当前查询的“时”存放在变量times里
})
}
})
.catch(err => {
console.error(err);
});
}
}, 1000);
发表于 2023-9-18 17:25
