首页大屏部分接口开发

接下来我们新建一个首页大屏接口部分，实现对于系统数据的管理，这部分我们就不需要表了，主要是对于各种数据的统计

1、模块搭建

先搭建一个对于首页大屏的部分文件sysdataRoutes.js

👉 主文件引入

//引入首页大屏部分数据
const sysdataRoutes = require('./server/api/sysdataRoutes');

// 首页大屏数据
app.use(`/${apiprefix}/system/sysdata`, sysdataRoutes);

2、接口暴露

大屏数据接口我们暴露出去，之前我们定义规则

custom: (req) => {
  // 排除接口
  if (req.originalUrl.startsWith('/api/open')) { return true } // open开放接口
}

所以我们直接加个open就行，直接访问已经不需要授权

http://127.0.0.1:8888/api/open/sysdata

3、模块编写

先写一个sql查询语句

SELECT 
    COUNT(*) AS total_users,
    COUNT(CASE WHEN sex = 1 THEN 1 END) AS male_users,
    COUNT(CASE WHEN sex = 2 THEN 1 END) AS female_users,
    COUNT(CASE WHEN sex IS NULL THEN 1 END) AS unknown_gender_users,
    (SELECT COUNT(*) FROM sys_article) AS total_articles,
    (SELECT COUNT(*) FROM sys_chat) AS total_chats
FROM sys_user;

查询消息无误，接下来我们放入模块之中,首先格外注意下面这一点

一定要放到下面这个的上面，否则就被过滤掉了！！！！

//代码接口放到这里

router.get('/:id', (req, res) => {})

将模块给写上

// 查询首页数据
router.get('/sysData', (req, res) => {
    // console.log(req, 'req-----查询');
    console.log('sysdataRoutes-----查询');
    let query = `SELECT 
        COUNT(*) AS total_users,
        COUNT(CASE WHEN sex = 1 THEN 1 END) AS male_users,
        COUNT(CASE WHEN sex = 2 THEN 1 END) AS female_users,
        COUNT(CASE WHEN sex IS NULL THEN 1 END) AS unknown_gender_users,
        (SELECT COUNT(*) FROM sys_article) AS total_articles,
        (SELECT COUNT(*) FROM sys_chat) AS total_chats
    FROM sys_user;`;
    connectionPool.query(query,(error, results, fields) => {
         if (error) {
            console.log(error,'error---------------------')
          res.send({
              code: 500,
              message:'查询失败！',
          });
        } else {
          res.send({
              code: 200,
              data: results?convertToCamelCase(results[0]):null,
              message:'查询成功！',
          });
        }
    });
});

调用接口,这个时候给我们返回的数据已经正确了

{"code":200,
  "data":{
  "totalUsers":11,
    "maleUsers":6,
    "femaleUsers":4,
    "unknownGenderUsers":1,
    "totalArticles":7,
    "totalChats":24},
 "message":"查询成功！"}

4、完善补充sql语句

这里面我们还可以持续增加一些我们想要的信息，并且采取join的方式进行（join方式放入优化模块）

这里也有一个比较简单的方式 就是分别查询 把这两部分分开查询，最后在返回数据的时候再进行组装起来数据。（采取）

 let query = `SELECT 
        COUNT(*) AS total_users,
        COUNT(CASE WHEN sex = 1 THEN 1 END) AS male_users,
        COUNT(CASE WHEN sex = 2 THEN 1 END) AS female_users,
        COUNT(CASE WHEN sex IS NULL THEN 1 END) AS unknown_gender_users,
        (SELECT COUNT(*) FROM sys_article) AS total_articles,
        (SELECT COUNT(*) FROM sys_chat) AS total_chats,
        (SELECT COUNT(*) FROM applications) AS total_applications
    FROM sys_user;`;

let queryactivity = `SELECT 
    a.activity_type,
    COUNT(*) AS activity_type_total,
    d.dict_label AS activity_type_name
FROM 
    activities a
JOIN 
    sys_dict_data d ON a.activity_type = d.dict_value AND d.dict_type = 'activitytype'
GROUP BY 
    a.activity_type, d.dict_label;`;