Javascript

nodejs与bat结合的定时查询功能-代码中逻辑定时去查询仪器列表是否需要送去校验

本文主要是介绍nodejs与bat结合的定时查询功能-代码中逻辑定时去查询仪器列表是否需要送去校验,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

用到的包:

  

  "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": 'compaltpr@compal.top', // 邮箱账号
 20         "pass": 'XUDELIN8800275' // 邮箱的授权码
 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()//执行函数
View Code

调用的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\nodejs&&node D:\phpstudy\WWW\lecturedemo\node\codetest\timer\timer.js"

最后把test.bat 放到任务计划里,就实现了定时判断数据库的功能啦【亲测有效!!!】

  

这篇关于nodejs与bat结合的定时查询功能-代码中逻辑定时去查询仪器列表是否需要送去校验的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!