用到的包：
"dependencies": {
"mysql": "^2.18.1",
"node-schedule": "^1.3.2",
"nodemailer": "^6.4.17",
"pm2": "^4.5.1"
},
"devDependencies": {
"art-template": "^4.13.2",
"child-process-ext": "^2.1.1",
"child_process": "^1.0.2",
"execa": "^5.0.0",
"moment": "^2.29.1"
}
连接数据库【link.js】【注意确保npm install mysql这个包】：
var mysql = require('mysql');
function createConnection() {
var connection = mysql.createConnection({
host: '127.0.0.1',
database: 'demo',
user: 'root',
password: 'root'
});
return connection;
}
module.exports.createConnection = createConnection;
主文件【timer.js】：
1 let timer = require('node-schedule') //使用node-schedule包，这是一个好用的计时模块 2 let mysql = require('./link.js') 3 let moment = require('moment') //时间模块，有官网 4 let template = require('art-template') 5 const { spawn, execFile, exec } = require("child_process") //使用这个模块可以实现调用bat，网上有使用文档 6 //const {bat} = spawn('cmd.exe', ['/c','test1.bat']) 7 let connection = null 8 var p = {} 9 let datarr = [] 10 let tomail = [] 11 const nodemailer = require('nodemailer'); 12 const { callbackPromise } = require('nodemailer/lib/shared') 13 // 创建可重用邮件传输器 14 const transporter = nodemailer.createTransport({ 15 host: "yourhost", // 邮件地址 16 port: 25, // 端口 17 secureConnection: false, // use SSL 18 auth: { 19 "user": 'xxxxxxxx@xxx.com', //你的 邮箱账号 20 "pass": 'xxxxxxxxxx' // 你的邮箱的授权码 21 } 22 }); 23 const send = (mailOptions, callback) => { 24 transporter.sendMail(mailOptions, function (error, info) { 25 if (error) { 26 return console.log(error); 27 } 28 return callback('ms') //回调传入标志位，表示邮件发送ok 29 }); 30 } 31 32 // let code = Math.floor(Math.random() * 999999).toString() 33 34 // let emailCode = code //验证码为6位随机数 35 let email = { 36 title: '仪器校验提醒', 37 htmlBody: '<test>' 38 } 39 let mailOptions = { 40 from: 'compal-SOD@compal.top', // 发件人地址 41 to: ['Iori_Liu@compal.com', 'RuiY_Yang@compal.com', 'Bruce_Xu@compal.com'], // 收件人地址，多个收件人可以使用逗号分隔 42 subject: email.title, // 邮件标题 43 html: email.htmlBody // 邮件内容 44 }; 45 46 function objtimer() { 47 p = timer.scheduleJob({ second: 30 }, function () { 48 connection = mysql.createConnection() 49 connection.connect() 50 let sql = "select id,yiqimingcheng as epname,xinghao as model,changpai as brand,plant,deptname,jiaoyanriqi as cdate,jiaoyanxingzhi as checktype,jiaoyanbianhao as ckcode, baoguanren as custodian,yiqifuzeren as incharge,step1_master_name as director1,bgr_mail,fzr_mail,dt1_mail from compal_sod_instrument_list " 51 let isql = "" 52 connection.query(sql, function (error, results) { 53 if (error) { 54 console.log(error) 55 throw error 56 } 57 else { 58 //console.log('success') 59 results.forEach((item, i) => { 60 // moment(date,'format') 这个format 令牌，不传这个format参数的话，会报警告，date不是符合格式的日期字符串！！！ 61 if (item.cdate !== '' && item.cdate !== null) { 62 item.cdate = moment(item.cdate, ['YYYY-MM-DD HH:mm:ss', 'YYYY/MM/DD', 'YYYY/M/D']).format('YYYY-MM-DD') 63 let now = moment().format('YYYY-MM-DD') 64 if (moment(item.cdate).format('x') > moment(now).format('x')) { //得到unix时间戳 65 if ((moment(moment(item.cdate, 'YYYY-MM-DD')).diff(moment(now, 'YYYY-MM-DD'), 'days')) === 8) { //如果当天离校验日期天数为8的话 66 item.status = 0 67 item.itime = moment().format('YYYY-MM-DD HH::mm:ss') 68 item.step1 = 0 69 item.step2 = 0 70 datarr.push(Object.values(item)) 71 72 } 73 } 74 } 75 }) 76 //console.log(datarr) 77 if (datarr.length >= 1) { 78 79 isql = "insert into pend_equipitem(`id`,`name`,`model`,`brand`,`plant`,`dept`,`checkdata`,`checktype`,`ckcode`,`custodian`,`incharge`,`director1`,`bgr_mail`,`fzr_mail`,`dt1_mail`,`status`,`itime`,`step1`,`step2`) VALUES ? " 80 connection.query(isql, [datarr], function (err, res) { 81 if (err) { 82 console.log('sql语句执行错误，错误如下：', err.message) 83 return 84 } else { 85 //console.log(res) 86 if (res.affectedRows >= 1) { 87 //console.log('insert success') 88 p.cancel() 89 //console.log('定时器取消') 90 91 let html = `` 92 for (let i = 0; i < datarr.length; i++) { 93 html += 94 '<tr>' 95 + '<td style="border:1px solod black;text-align:center;">' + datarr[i][1] + '</td>' 96 + '<td style="border:1px solod black;text-align:center;">' + datarr[i][2] + '</td>' 97 + '<td style="border:1px solod black;text-align:center;">' + datarr[i][3] + '</td>' 98 + '<td style="border:1px solod black;text-align:center;">' + datarr[i][4] + '</td>' 99 + '<td style="border:1px solod black;text-align:center;">' + datarr[i][5] + '</td>' 100 + '<td style="border:1px solod black;text-align:center;">' + datarr[i][6] + '</td>' 101 + '<td style="border:1px solod black;text-align:center;">' + datarr[i][7] + '</td>' 102 + '<td style="border:1px solod black;text-align:center;">' + datarr[i][8] + '</td>' 103 + '<td style="border:1px solod black;text-align:center;">' + datarr[i][9] + '</td>' 104 + '<td style="border:1px solod black;text-align:center;">' + datarr[i][10] + '</td>' 105 + '<td style="border:1px solod black;text-align:center;">' + datarr[i][11] + '</td>' 106 + '</tr>' 107 108 let t = [datarr[i][12], datarr[i][13], datarr[i][14]]; 109 tomail.push(...t) 110 111 } 112 let chtml = ` 113 <table border="1" cellspacing="0" cellpadding="0" style="border-spacing:0;bloder-collapse:collapse;margin:10px 0"> 114 <caption style="font-size: 18px;font-weight: bold;margin: 1em 0;">以下表格中的数据为近期需要校验的仪器信息，请留意！！！</caption> 115 <tbody> 116 <tr style="border:1px solod black;text-align:center;color:white;"> 117 <th style="background-color:#87ceeb;font-weight:bold;">仪器名称</th> 118 <th style="background-color:#87ceeb;font-weight:bold;">型号</th> 119 <th style="background-color:#87ceeb;font-weight:bold;">厂牌</th> 120 <th style="background-color:#87ceeb;font-weight:bold;">厂区</th> 121 <th style="background-color:#87ceeb;font-weight:bold;">部门</th> 122 <th style="background-color:#87ceeb;font-weight:bold;">校验日期</th> 123 <th style="background-color:#87ceeb;font-weight:bold;">校验性质</th> 124 <th style="background-color:#87ceeb;font-weight:bold;">校验编号</th> 125 <th style="background-color:#87ceeb;font-weight:bold;">保管人</th> 126 <th style="background-color:#87ceeb;font-weight:bold;">负责人</th> 127 <th style="background-color:#87ceeb;font-weight:bold;">保管人主管</th> 128 </tr> 129 ${html} 130 </tbody> 131 </table>` 132 mailOptions.html = chtml 133 //console.log(tomail); 134 for (let q = 0; q < tomail.length; q++) { 135 if (!mailOptions.to.includes(tomail[q])) { 136 mailOptions.to.push(tomail[q]) 137 } 138 } 139 140 console.log(mailOptions) 141 send(mailOptions, function (data) { 142 if (data == 'ms') { 143 console.log(data) 144 execFile('test1.bat', { encoding: 'buffer' }, (error, stdout, stderr) => {//execFile 为child-process的方法，这里用于调用test1.bat 145 if (error) throw error; 146 console.log(exit) 147 }); 148 //const bat = spawn('cmd.exe',['/c', 'test1.bat']) 149 // execFile('test1.bat', { encoding: 'buffer' }, (error, stdout, stderr) => { 150 // if (error) throw error; 151 152 // }); 153 // bat.stdout.on('data', (data) => { 154 // console.log(data.toString()); 155 // }); 156 157 // bat.stderr.on('data', (data) => { 158 // console.error(data.toString()); 159 // }); 160 161 // bat.on('exit', (code) => { 162 // console.log(`子进程退出，退出码 ${code}`); 163 // }); 164 } 165 }) 166 167 168 169 170 } 171 172 173 174 } 175 }) 176 } else { 177 console.log('exit with no data') 178 // exec("taskkill /f /fi 'WINDOWTITLE eq test.cmd' ", (error,stdout,stderr=>{ 179 // if (error){ 180 // console.log('run error') 181 // }else{ 182 // console.log('ok') 183 // } 184 // } 185 186 execFile('test1.bat', { encoding: 'buffer' }, (error, stdout, stderr) => { 187 if (error) throw error; 188 console.log(exit) 189 }); 190 } 191 } 192 193 }); 194 195 196 197 // 关闭连接 循环往数据库插数据时，尽量别关闭，因为插入一次就关闭连接，第二次往后没有重新开启连接数据就无法插进去，从而报错！！！ 198 199 200 }) 201 } 202 objtimer()//执行函数
调用的bat【test1这里主要是关闭调用timer.js后，执行完毕后的黑框，至于为什么要关闭两次，还没搞清楚，一次就是关不掉 。。。】：
【test1.bat】:
taskkill /f /fi "WINDOWTITLE eq test.cmd" taskkill /f /fi "WINDOWTITLE eq test.cmd"
【test.bat】调用nodejs 执行timerjs:
@echo off
title test.bat
cmd /k "cd C:Program Files
odejs&&node D:phpstudyWWWlecturedemo
odecodetest imer imer.js" //这里记得改成你存放定时器js文件的路径
最后把test.bat 放到任务计划里，就实现了定时判断数据库的功能啦【亲测有效！！！】