睡一觉起来发现小程序开发这么简单

上班打工下班遛娃
41,624 阅读5分钟

项目前端小程序二维码：

juejin.jpg

简介： 一切免费！简化记账，记录个人和家庭财务、导出明细、共享账本、并管理权限。

我的个人blog网站：www.zhooson.cn/ 里面其他全栈项目开源Github地址

1. 技术：

前端：uniapp（vue3）
后端：egg
node：16.5.0
数据库：mysql
工具：HbuilerX filezilla pm2 Termius等

2. 整体项目结构

image.png

3. uniapp

具体的开发文档：uniapp.dcloud.net.cn/
技术选型：uniapp以前没有使用过，这次决定尝试一次。

使用感觉，感觉不咋好，也许我是了解的不够全面，我每次小程序开发工具添加新的编译模式，重新打包后就没有了，这一点软件默认设置不太友好，截止2023-12-01，这个框架和软件越用越操蛋。

uniapp仔细阅读文档即可，本文不做详细讲解。

4. egg

1. 初始化的项目的老掉牙的命令自行查看文档：www.eggjs.org/zh-CN/intro…

2. jwt使用

  • 安装
    yarn add egg-jwt
  • 配置
// {app_root}/config/plugin.js
exports.jwt = {
  enable: true,
  package: "egg-jwt"
};

// {app_root}/config/config.default.js
exports.jwt = {
  secret: "123456"
};
  • 使用
// {app_root}/app/controller/user.js
//签发 token 数据
    ...
    let result = await service.user.query({ openId });
    const token = app.jwt.sign(
      {
        nickname: result.openId,
        userId: result.id,
        exp: Math.floor(Date.now() / 1000) + 60 * 60, // 1h
      },
      app.config.jwt.secret
    );

// {app_root}/app/router.js
module.exports = (app) => {
  const { router, controller, jwt } = app;

  /**
   *  用户
   */
  router.post('/api/user/login', controller.user.login);
  router.post('/api/user/update', jwt, controller.user.update);
  router.get('/api/user/list', jwt, controller.user.list);
}

3. 获取微信小程序用户openId（前端只需传递code）

// {app_root}/app/service/tool.js
'use strict';

const Service = require('egg').Service;
const axios = require('axios');
class ToolService extends Service {
  // wx 相关操作
  async decodeWXByCode({ code }) {
    return new Promise((resolve, reject) => {
      const { ctx, app } = this;
      const { AppSecret, AppID } = app.config.wx;
      axios
        .get(
          `https://api.weixin.qq.com/sns/jscode2session?appid=${AppID}&secret=${AppSecret}&js_code=${code}&grant_type=authorization_code`
        )
        .then((res) => {
          // console.log('decodeWXByCode', res.data);
          if (res.data.errcode === 40029) {
            resolve({ status: 201, message: '无效code' });
          } else if (res.data.errcode === 40163) {
            resolve({ status: 201, message: 'code被使用' });
          } else if (res.data.session_key && res.data.openid) {
            resolve({
              status: 200,
              message: '获取成功',
              data: {
                openid: res.data.openid,
              },
            });
          } else {
            resolve({ status: 201, message: '未知错误' });
          }
        });
    });
  }
}

module.exports = ToolService;

4. 查询首页数据service， 分级查询

image.png

WechatIMG1451.jpeg

  async list({ openId, plus = 0, year, month, name_id }) {
    // console.log('2023-2-1', openId, plus, plus === 0, year, month, day);
    // let w = `where 1=1`;
    // let a = `where 1=1`;

    // if (openId) {
    //   w += ` and b.openId = '${openId}'`;
    //   a += ` and b.openId = '${openId}'`;
    // }
    // if (year) {
    //   w += ` and year = ${year}`;
    //   a += ` and year = ${year}`;
    // }
    // if (month) {
    //   w += ` and month = ${month}`;
    //   a += ` and month = ${month}`;
    // }
    // // if (day) {
    // //   a += ` and day = ${day}`;
    // // }
    // if (+plus) {
    //   w += ` and plus = ${plus}`;
    // }

    // 本月 支出 + 收入 =  总和
    // const sumSql = `select sum(price) from book b ${a}`;
    // const MonthCount = await this.app.mysql.query(sumSql);

    // 本月 支出
    const outSql = `select sum(price) from book  where  name_id = ${name_id} and year = ${year} and month = ${month} and plus = 1 and disabled = 0`;
    const MonthOutCount = await this.app.mysql.query(outSql);

    // 本月 收入
    const inSql = `select sum(price) from book b where name_id = ${name_id} and year = ${year} and month = ${month} and plus = 2 and disabled = 0`;
    const MonthInCount = await this.app.mysql.query(inSql);

    // 当月 所有明细
    // const sql = `select b.*, u.nickname, u.avatar, i.title icon_title   from book b inner join user u on b.openId = u.openId   inner join cate i on b.cate_id = i.id ${w} group day order by create_time desc`;

    const sql = `select distinct day, month, year from book where name_id = ${name_id} and year = ${year} and month = ${month} and disabled = 0  order by day desc`;
    let days = await this.app.mysql.query(sql);

    // const detailSql = `select * from book where openId = '${openId}' and year = ${year} and month = ${month}`;
    // const detailSql = `select b.*, u.nickname, u.avatar, i.title icon_title   from book b   inner join user u on b.openId = u.openId   inner join cate i on b.cate_id = i.id     where openId = '${openId}' and year = ${year} and month = ${month} order by create_time desc`;

    let n = '1 = 1 and disabled = 0';
    if (+plus) {
      n += ` and b.plus = ${plus}`;
    }

    for (let val of days) {
      val.date = `${val.year}-${val.month}-${val.day}`;
      val.items = [];

      val.items = await this.app.mysql.query(
        `select b.*, u.nickname, u.avatar, c.title icon_title   from book b   inner join user u on b.openId = u.openId   inner join cate c on b.cate_id = c.id   where  ${n}  and b.name_id = ${name_id} and year = ${year} and month = ${month} and day = ${val.day}  order by create_time desc`
      );
    }
  }

我的sql语法不太完美，请大神提出宝贵意见。

截止2023-7-1 目前注册用户已有1w+， 首页刷频率很高， 现在前端加载页面大概要1.5s， 时间太长了，优化后的sql，只用到200ms，把sql化繁为简，把查询数据的压力全部嫁接给客户端， 实测：前端循环200条数据才几毫秒。

// 首页列表 新
  async listNew({
    name_id,
    plus = 0,
    start,
    end,
    pageIndex = 1,
    pageSize = 20,
  }) {
    // console.log(
    //   'name_id, start, end, pageIndex = 1, pageSize = 20',
    //   name_id,
    //   start,
    //   end,
    //   pageIndex,
    //   pageSize
    // );
    // 这个sql能查询到前端想要的格式，但是废CPU，访问人数一多，就会爆炸，其次这个接口刷新频率很高
    // const sql = `SELECT
    // book.year,
    // book.month,
    // book.day,
    // GROUP_CONCAT(
    //     CONCAT(
    //         '{',
    //             '\"year\":', book.year, ',',
    //             '\"month\":', book.month, ',',
    //             '\"day\":', book.day, ',',
    //             '\"name_id\":', book.name_id, ',',
    //             '\"openId\":\"', book.openId, '\",',
    //             '\"price\":', book.price, ',',
    //             '\"remark\":\"', book.remark, '\",',
    //             '\"user\":{',
    //                 '\"id\":', user.id, ',',
    //                 '\"nickname\":\"', user.nickname, '\",',
    //                 '\"avatar\":\"', user.avatar, '\"',
    //             '},',
    //             '\"cate\":{',
    //                 '\"id\":', cate.id, ',',
    //                 '\"title\":\"', cate.title, '\"',
    //             '}',
    //             '}'
    //         )
    //         SEPARATOR ','
    //     ) AS items
    // FROM
    //     book
    // JOIN
    //     user ON book.openId = user.openId
    // JOIN
    //     cate ON book.cate_id = cate.id
    // WHERE
    //     book.name_id = ${name_id}
    //     and book.disabled = 0
    //     and DATE(CONCAT(book.year, '-', book.month, '-', book.day)) BETWEEN "${start}" AND "${end}"
    // GROUP BY
    //     book.year,
    //     book.month,
    //     book.day;
    // `;

    const limit = (pageIndex - 1) * pageSize;

    const whereSql = `DATE(CONCAT(year, '-', month, '-', day)) BETWEEN "${start}" AND "${end}" 
                      and b.name_id = ${name_id} 
                      and b.disabled = 0`;

    let plusSql = '';
    if (plus) {
      plusSql += ` and b.plus = ${plus}`;
    }

    // 查询列表
    const sql = `select 
                b.id, 
                b.cate_id, 
                b.openId, 
                b.plus, 
                b.price, 
                b.year, 
                b.month, 
                b.day, 
                b.self,
                b.remark,  
                c.title cate_title, 
                c.code cate_code,
                u.avatar, 
                u.nickname
            from book b   
            inner join 
                cate c on c.id = b.cate_id   
            inner join 
                user u on u.openId = b.openId  
            where   
                ${whereSql} ${plusSql}
            order by 
                year, month, day desc
            limit ${pageSize} offset ${limit}`;

    const inSql = `select 
                  ROUND(SUM(b.price),2) as value
                  from book b
                  where   
                      ${whereSql}
                      and b.plus = 2`;

    const outSql = `select 
                    ROUND(SUM(b.price),2) as value
                    from book b
                    where   
                        ${whereSql}
                        and b.plus = 1`;

    const list = await this.app.mysql.query(sql);
    const inCount = await this.app.mysql.query(inSql);
    const outCount = await this.app.mysql.query(outSql);

    return {
      inCount: inCount ? inCount[0].value : 0,
      outCount: outCount ? outCount[0].value : 0,
      list,
    };
  }

前端拿到数据， 对数据进行二次加工

    export function createNewList(a) {
  // const a = [
  // 	{
  // 		id:1,
  // 		year: 2023,
  // 		month: 6,
  // 		day: 1,
  // 		price: 1
  // 	},
  // 	{
  // 		id:2,
  // 		year: 2023,
  // 		month: 6,
  // 		day: 1,
  // 		price: 2
  // 	},
  // 	{
  // 		id:3,
  // 		year: 2023,
  // 		month: 6,
  // 		day: 2,
  // 		price: 12
  // 	},
  // 	{
  // 		id:4,
  // 		year: 2023,
  // 		month: 6,
  // 		day: 3,
  // 		price: 32
  // 	},
  // ];

  const b = a.reduce((acc, curr) => {
    const index = acc.findIndex(
      (item) =>
        item.year === curr.year &&
        item.month === curr.month &&
        item.day === curr.day
    );

    if (index !== -1) {
      acc[index].items.push(curr);
    } else {
      acc.push({
        year: curr.year,
        month: curr.month,
        day: curr.day,
        items: [curr],
      });
    }

    return acc;
  }, []);

  return b;
}

截止2023-12-15 机遇把数据库压力嫁接到客户端的操作，目前访问的时间也就200ms左右的，现在将inCount 和outCount也交给前端计算, 发现时间的fetch的接口请求时间又缩短了一半，前端计算时间几乎没有变化。

优化后的代码：

export function createNewList(a) {
 // const a = [
 // 	{
 // 		id:1,
 // 		year: 2023,
 // 		month: 6,
 // 		day: 1,
 // 		price: 1
 // 	},
 // 	{
 // 		id:2,
 // 		year: 2023,
 // 		month: 6,
 // 		day: 1,
 // 		price: 2
 // 	},
 // 	{
 // 		id:3,
 // 		year: 2023,
 // 		month: 6,
 // 		day: 2,
 // 		price: 12
 // 	},
 // 	{
 // 		id:4,
 // 		year: 2023,
 // 		month: 6,
 // 		day: 3,
 // 		price: 32
 // 	},
 // ];

 let outTotalByDate = 0,
   inTotalByDate = 0

 const b = a.reduce((acc, curr) => {
   const index = acc.findIndex(
     (item) =>
       item.year === curr.year &&
       item.month === curr.month &&
       item.day === curr.day
   )

   if (index !== -1) {
     acc[index].items.push(curr)

     if (curr.plus === 1) {
       acc[index].outcount += curr.price
       outTotalByDate += curr.price
     } else if (curr.plus === 2) {
       acc[index].incount += curr.price
       inTotalByDate += curr.price
     }
   } else {
     const outcount = curr.plus === 1 ? curr.price : 0
     const incount = curr.plus === 2 ? curr.price : 0
     outTotalByDate += outcount
     inTotalByDate += incount

     acc.push({
       year: curr.year,
       month: curr.month,
       day: curr.day,
       outcount,
       incount,
       date: `${curr.year}-${curr.month}-${curr.day}`,
       week: formatDate(`${curr.year}-${curr.month}-${curr.day}`, 'week'),
       items: [curr]
     })
   }

   return acc
 }, [])

 return {
   list: b,
   outTotalByDate: Number(outTotalByDate.toFixed(2)) || 0,
   inTotalByDate: Number(inTotalByDate.toFixed(2)) || 0
 }
}

5. 统计报表， 可以分类查询指定月份的明细，饼图，折线图等，

WechatIMG1883.jpg

6. 导出数据 excle表格，

  // 导出
  async export() {
    const { ctx, service } = this;
    try {
      let query = ctx.request.query.code // 当前code需要解密，需要自己的制定自己的解密规则
      console.log('search-query', query);
      query = JSON.parse(query);
      let list = await service.book.search(query);
      const bookDetail = await service.name.query({ id: query.name_id });

      let xls = [[]];
      xls[0] = ['方式', '金额', '创建人', '账本', '类别', '时间', '备注'];
      for (let index = 0; index < list.length; index++) {
        const element = list[index];
        xls[index + 1] = [
          element.plus === 1 ? '支出' : '收入',
          element.price,
          element.nickname,
          element.name_title,
          element.cate_title,
          element.year + '/' + element.month + '/' + element.day,
          element.remark,
        ];
      }
      // console.log('xls', xls);
      const wb = XLSX.utils.book_new();
      XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(xls), '账本');
      const buf = XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });

      const filename = encodeURIComponent(
        `${bookDetail.title}_${query.year}_${query.month}月账本`
      );
        
      // 设置header关键代码
      ctx.set('Content-Disposition', `attachment; filename="${filename}.xlsx"`);
      ctx.set('Content-Type', 'application/vnd.ms-excel');
      ctx.status = 200;
      ctx.body = buf;

      // cb(ctx, 200, 200, '导出成功', list);
    } catch (err) {
      cb(ctx, 200, 422, '导出失败', JOSN.stringify(err));
    }
  }
  • excle：

image.png

6. 上传文件 用户头像和cover图，可动态生成文件夹目录。

截止2023-12-01, 发现现在很多人上传的图片都是5M大小，为了优化图片质量，前端上传 wx.compressImage 方法压缩质量

'use strict';
const fs = require('fs');
const path = require('path');
const mkdirp = require('mkdirp');

const { cb, formatDate } = require('../../utils');

// 生成新的文件名称
function getUploadFileExt(name) {
  let ext = name.split('.');
  let last = formatDate(new Date(), 'YYYYMMDDhhmmssms');
  return `${last}.${ext[ext.length - 1]}`;
}

const Controller = require('egg').Controller;

class UploadController extends Controller {
  async file() {
    const { ctx } = this;
    try {
      // 1. 获取文件流
      const file = ctx.request.files[0];
      // console.log(33, file);
      // 2. 生成filename
      const name = getUploadFileExt(file.filename);
      // console.log('name', name);
      // 3. 获取bucket ps: demo 或者 demo/test 或者 demo/test/cd
      const { bucket = 'avatar' } = ctx.request.body;
      // 4. 生成文件夹
      const dir = path.join(__dirname, `../public/images/${bucket}`);
      // console.log('dir', dir);
      await mkdirp(dir);
      // 5. 文件流读取／写入
      const filePath = `${dir}/${name}`;
      let readStream = fs.createReadStream(file.filepath);
      var writeStream = fs.createWriteStream(filePath);
      readStream.pipe(writeStream);
      readStream.on('end', function () {
        fs.unlinkSync(file.filepath);
      });

        cb(ctx, 200, 200, '上传成功', {
          url: `http://${ctx.request.header.host}/public/images/${bucket}/${name}`,
        });
    } catch (err) {
      cb(ctx, 200, 500, '上传失败！', JSON.stringify(err));
    }
  }
}

module.exports = UploadController;

7. 好友共享账本

image.png

image.png

8. 具体的数据表设计展示如下

  • 账本表

image.png

  • 用户表

image.png

......等

5. 博客

我的个人blog网站：www.zhooson.cn/ 有其他前后端项目代码已开源。

avatar
文章
阅读
粉丝