Background description

Small program answer check-in function. In order to promote daily activity, users who fail to check in on that day need to push messages to remind them to check in regularly every day.

Before reading this article, it is best to know the relevant documents about sending template messages on wechat:

  1. Template Message Guide
  2. Template message service interface

The author is also the first time to write a small program timing template message function, as a purebred front-end city lion, may be building table operation database back-end code is not rigorous or unreasonable place, welcome the big guys clap brick point (pat). This article to provide solutions to the main ideas, only for learning exchange, if there is unreasonable place please leave a message oh. 😆

Implementation approach

Official restrictions

Conditions for sending wechat applets push template message:

  1. Payment Allows developers to push a limited number of template messages to users within 7 days after users have completed payment in the mini program (3 messages can be sent for one payment, and the number of messages to be sent for multiple payments is independent and does not affect each other)

  2. Submit the form Happened when a user within the small application form is submitted behavior and the form of statement to send message template, developers need to provide customers with services, allows developers to push to the user in the 7 days news article limited the number of template (1 submission form can be issued 1, submit article issued by several independent for many times, do not affect each other)

According to the official rules, it is obviously not enough for users to push one notification within seven days once triggered. For example, with the check-in function, only users who check in on the previous day can get the opportunity to push a message, and then send a check-in reminder to the user on the next day. If the user forgets to check in, the system loses the right to remind the user and disconnects from the user.

How to break through the limits?

Since the user can send one notification message once mentioning the form, and the number of messages submitted for multiple times is independent and does not affect each other. Then we can make reasonable use of the rule, wrap the page binding button with the form form report-submit=true, disguise the button form-type=submit, collect the form ID, store the form ID in the database, The template message is then sent to the user via a scheduled task.

Development steps

Configure message templates in the background

Wechat public platform -> Functions -> Template Message -> My template add template message, as follows:

The template ID and keyword must be used when sending template messages.

Database design

Before building a table, think about what data you need to save?

According to wechat’s message sending interface Templatemessage. send, touser(namely user openID) and form_id need to be stored in the database to send a message to the user. In addition, the expire of the user form_id needs to be saved as well as whether the form_id is used and the expired state needs to be saved.

So the structure of the table is:

Table: wx_save_form_id

id open_id user_id form_id expire status
1 xxxxxx 1234 xxxx 1562642733399 0

sql

CREATE TABLE `wx_save_form_id` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `open_id` char(100) NOT NULL DEFAULT ' ',
  `user_id` int(11) NOT NULL,
  `form_id` char(100) NOT NULL DEFAULT ' ',
  `expire` bigint(20) NOT NULL COMMENT 'Form_id Expiration time (timestamp)',
  `status` int(1) DEFAULT '0' COMMENT '0 not pushed 1 pushed 2 expired ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=114 DEFAULT CHARSET=utf8;

Copy the code

Now that the table is built, let’s get the logic straight:

  1. The user submits the form and willopen_id.user_id(Save this field according to their own needs),form_id.expireAs well asstatus=0Inserted into thewx_save_form_idIn the table
  2. Enable scheduled tasks (such as 10:00 every day) to query the table at a fixed timewx_save_form_idAnd getstatus=0Data, and then adjust wechattemplateMessage.sendThe interface sends prompt messages to corresponding users
  3. The user who has sent it willstatusField updated to1The next query will filter out the sent state.

Do you think you missed something?

Form_id has an expiration date of 7 days. How can I change the status to expired?

One solution is to run another scheduled task (say, every 20 minutes) to check which form_ID has expired, and then change the status. It would be inefficient, inconvenient, and unreasonable to have data in only one table, wX_save_form_ID. So I thought to create another table:

Table: wx_message_push_status

id user_id count last_push
1 1234 5 20190701

sql

CREATE TABLE `wx_message_push_status` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `count` int(11) NOT NULL DEFAULT '1' COMMENT 'Push times',
  `last_date` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Last push time',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
Copy the code

User_id (the value can also be open_id) user ID, count Number of times that a message can be pushed to a user. Last_date Time when the message is pushed last time, which determines whether the message is pushed on the current day

To restate the logic:

  1. The user submits the form and willopen_id.user_id(Save this field according to their own needs),form_id.expireAs well asstatus=0Inserted into thewx_save_form_idTable, at the same time willwx_message_push_statusIn the tablecountTheir own + 1
  2. Enable scheduled tasks (such as 10:00 every day) to query the table at a fixed timewx_message_push_status, by filtering conditionscount>0andlast_dateNot for the day, can get push messagesuser_idGo to querywx_save_form_idtable
  3. Query conditionsUser_id = obtained above.status=0.Expire >= Current timestamp“And then adjust wechattemplateMessage.sendThe interface sends prompt messages to corresponding users
  4. The user who has sent it willstatusField updated to1The next query will filter out the sent state.
  5. Start another scheduled task (for example, execute it every 20 minutes) and query it firstwx_save_form_id, screening conditionsstatus=0andExprie < Current timestamp(i.e., unsent, expired data)
  6. The data to be filteredstatusChange to 2 and querywx_message_push_statusTable corresponding to theuser_idThat will becountItself minus 1.

Perfect finish.

Clear development logic, ready to start writing code

Code implementation

The front page

The form component of the page, with the property report-submit set to true, can declare that the template message needs to be sent, and click the button to submit the form to get the form ID

demo.wxml

<form report-submit="true" bindsubmit="uploadFormId">
    <button form-type="submit" hover-class="none"</button> </form>Copy the code

You can disguise all the bound events in the page with a Form component in exchange for more formids.

Note: The form_ID must be obtained on the real machine. The simulator will report the formId is a mock one.

demo.js

Page({ ... UploadFormId (e){uploadFormId(e){uploadFormId(e){uploadFormId(e){wx.request({url:'xx/xx/uploadFormId', data: { form_id: e.detail.formId } }); }... })Copy the code
Server interface

Server.js // Node middle layer to call the bottom interface

async updateFormIdAction(){/* * our userId and openId are stored on the server side and do not need to be returned from the front end. * Don't worry about the implementation syntax of the interface, depending on your framework. */ const {ctx} = this; const user = ctx.user; const userId = user ? user.userId :' ';
    const loginSession = ctx.loginSession;
    const body = ctx.request.body;

    let openId = loginSession.getData().miniProgram_openId || ' ';

    const result = await this.callService('nodeMarket.saveUserFormId', openId, userId, body.form_id);
    return this.json(result);
}

Copy the code
Underlying interfaces and scheduled tasks

Service. js //Node operates the database interface

const request = require('request'); Wx_save_form_id */ asyncsaveUserFormIdAction(){ const http = this.http; const req = http.req; const body = req.body; // The timestamp expires in 7 dayslet expire = new Date().getTime() + (7 * 24 * 60 * 60 *1000); 
    const sql = `INSERT INTO wx_save_form_id (open_id, user_id, form_id, expire) VALUES(${body.openId}.${body.userId}.${body.formId}.${expire}) `; // self-encapsulated mysql instancelet tmpResult = await mysqlClient.query(sql);
    let result = tmpResult.results;
    if(! result || result.affectedRows ! = = 1) {... } await this._updateMessagePushStatusByUserId(body.userId);return this.json({
        status: 0,
        message: 'success'}); } / / update user can push the message number _updateMessagePushStatusByUserId (user_id) {HTTP = this const. HTTP; try{ const selectSql = `SELECT user_id, count from wx_message_push_status WHERE user_id =${user_id}`;
        let temp = await mysqlClient.query(sql);
        let result = temp.results;
        if(result.length){// UPDATE data with user_id const updateSql = 'UPDATE wx_message_PUSH_STATUS SET count = count + 1 WHERE user_id =${user_id}`; await mysqlClient.query(sql); . }elseConst insertSql = 'INSERT INTO wx_message_PUSH_status user_id VALUES $(user_id)'; await mysqlClient.query(sql); . } }catch(err){ ... }} // Timed task async that sends messagessendMessageTaskAction(){ const http = this.http; const Today = utils.getCurrentDateInt(); Const selectCanPushSql = 'select user_id from' select user_id from 'const selectCanPushSql =' select user_id from wx_message_push_status WHERE count > 0 AND last_date ! =${Today}`;
    let temp = await mysqlClient.query(selectCanPushSql);
    let selectCanPush = temp.results;
    
    if(selectCanPush.length){
        selectCanPush.forEach(async (record)=>{
            try{
                letuser_id = record.user_id; Const currentTime = new Date().getTime(); const getFormIdSql = `select open_id, user_id, form_id from wx_save_form_id WHERE user_id =${user_id} AND status = 0 AND expire >= ${currentTime}AND form_id ! ='the formId is a mock one' ORDER BY expire ASC`;
                letgetFormIdTemp = await mysqlClient.query(getFormIdSql); // Get the list of available form_idletgetUserFormIds = getFormIdTemp.results; Const {open_id, form_id} = getUserFormIds[0]; const {open_id, form_id} = getUserFormIds[0];letsendStatus = await this._sendMessageToUser(open_id, form_id); /* * After the message has been sent * set this form_id to the last push time used to the day * Reduce the number of messages that can be sent by 1 */let updateCountSql = `UPDATE wx_message_push_status SET count = count - 1, last_date = ${Today} WHERE count >0 AND user_id = ${user_id}; `; await mysqlClient.query(updateCountSql);let updateStatusSql = `UPDATE wx_save_form_id SET status = 1 WHERE user_id = ${user_id} AND open_id = ${open_id} AND form_id = ${form_id}`; await mysqlClient.query(updateStatusSql); . }catch(err){ ... }}); } this.json({ status: 0 }); } // SendMessagetouser (open_id, form_id){letaccessToken = await this._getAccessToken(); Const oDate = new Date(); const time = oDate.getFullYear() +'. ' + (oDate.getMonth()+1) + '. ' + oDate.getDate();
    if(accessToken){
        const url = `https://api.weixin.qq.com/cgi-bin/message/wxopen/template/send?access_token=${accessToken}`;
        request({
            url,
            method: 'POST',
            data: {
                access_token,
                touser: open_id,
                form_id,
                page: 'pages/xxx/xxx',
                template_id: 'Your template ID',
                data: {
                    keyword1: {
                        value: "Daily points"
                    },
                    keyword2: {
                        value: 'N days in a row, 7 days in a row there are surprises, come on ~'
                    },
                    keyword3: {
                        value: "Ding! Time to sign in. Constant dripping wears away the stone."}, keyword4: { value: time } } } },(res)=>{ ... })} /* * Check whether the expire field in the wx_save_form_ID table is expired, if it is, set status to 2 and * decrement the count field in the corresponding Wx_message_PUSH_STATUS table by 1 */ asyncamendExpireTaskAction() {letnow = new Date().getTime(); Const expiredSql = 'select * from wx_save_form_id WHERE status = 0 AND expire < const expiredSql =' select * from wx_save_form_id WHERE status = 0 AND expire <${now}`;
        let expiredTemp = await mysqlClient.query(expiredSql);
        let expired = expiredTemp.results;
        ifForEach (async (record)=>{// UPDATE the status of expired records as 2 const updateStatusSql = 'UPDATE wx_save_form_id SET status = 2 WHERE open_id ='${record.open_id}' AND user_id = ${record.user_id} AND form_id = '${record.form_id}'`; await mysqlClient.query(updateStatusSql); // Reduce push times by 1let updateCountSql = `UPDATE wx_message_push_status SET count = count - 1 WHERE count >0 AND user_id = ${record.user_id}; `; await mysqlClient.query(updateCountSql); }); } }catch (e) { } this.json({ status: 0 }); }Copy the code

Perform a scheduled task to send messages

Call ~ complete code code is finished. Operation database does not consider the performance of the problem, if the large amount of data, also did not consider transactions, indexes and other operations (mainly not T_T), readers can optimize themselves.

Finally, we need to open two scheduled tasks to execute sendMessageTask interface and amendExpireTask interface respectively. Our scheduled task is also found in the open source Node framework, the specific implementation is not stated.

End result:

reference

Break through the limit of wechat mini program template message to achieve unlimited active push

Renrendai Big Front-end Technology Blog Center

And finally broadcast it. Welcome to renrendai Front End Technology Blog Center

Nodejs React reactNative applet front-end engineering and other related technical articles

Online customer service automatic reply function (Node version)

Last article: micro channel small program step pit guide