A. Connect to database MySQL
// inisalisasi
const mysql = require('mysql');
const conn = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'database_name'
})
// connect to database
conn.connect(function(err){
if(err){
throw err;
}
console.log('db connected');
})
function sqlRun(sql){
return new Promise(function (resolve, reject){
conn.query(sql, function (err, result){
if(err){
resolve(null);
} else if(result==''){
// masalah tidak null tapi kosong
resolve(null);
} else{
Object.keys(result).forEach(function(key) {
var row = result[key];
var xyz = row
resolve(xyz) ;
});
}
})
})
}
async function getData(){
sql = `SELECT column_name FROM table_name`
var data = await sqlRun(sql)
if(data !== null){
console.log(data['column_name'])
}
}
getData()B. Set interval code excecution
var minutes = 3, the_interval = minutes * 60 * 1000;
// this code will run every 3 minutes");
setInterval(function() {
getData()
}, the_interval);C. API Comsumtion
API Client di wa bot
const axios = require('axios');
var API_URL = 'https://domain.com/'
var API_KEY = 'api_key' //jika ada
function stockGudangFull(){
axios.get(API_URL+"stok_gudang",
{
params: { 'X-API-KEY': API_KEY }
})
.then( res => {
var date_stock= res.data.message.data_stock
})
.catch(error => {
console.log(error);
})
}API Host di web server
function stok_gudang_get(){
$data = array(
'data_stock' => $this->M_Stok->stok_resume()->result(),
);
$this->response([
'status' => true,
'message' => $data
], RestController::HTTP_OK);
}D. html to image
buat folder images di folder root project.
// handling image
const nodeHtmlToImage = require('node-html-to-image')
const puppeteer = { args: [ '--no-sandbox', '--disable-setuid-sandbox', '--disable-dev-shm-usage', '--disable-accelerated-2d-canvas', '--no-first-run', '--headless', '--no-zygote', '--disable-gpu' ], headless: true, ignoreHTTPSErrors: true };
//get html
var dataHalfKab = _stockKabHalfView(data)
//html to image
async function generateImage(){
await nodeHtmlToImage({
output: 'images/stock.png',
html: dataHalfKab,
puppeteerArgs: puppeteer,
selector : '#tbl_1_0'
}).then(() =>
//send image to wabot
client.sendMessage(group_pic, MessageMedia.fromFilePath('./images/stock_half_kab.png'), { caption: "Kabupaten" } )
)
}
//html style
function _style(){
return style =
`<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 100%;
}
.number{
text-align: right;
}
</style>`
}
//html view
function _stockKabHalfView(data){
let dataResult = '';
var style = _style()
dataResult += `<!DOCTYPE html>
<html>
<head>`+style+`</head>
<body>
<table id='tbl_1_0'>
<thead>
<tr>
<th class="tableth">No</th>
<th class="tableth">Cluster</th>
<th class="tableth">Kabupaten</th>
<th class="tableth">TGL_Update</th>
</tr>
</thead>
<tbody> `
var no=0
data.forEach(item =>{
no++
dataResult += `
<tr>
<td class=" tabletd"><center>`+no+`</center></td>
<td class=" tabletd">`+item.cluster_name+`</td>
<td class=" tabletd">`+item.kabupaten+`</td>
<td class=" tabletd">`+item.Tgl_Update+`</td>
</tr>`
})
dataResult += ` </tbody>
</table>
</body>
</html>`
return dataResult
}