data_dashboards/server/routes/index.js

var express = require('express');
const XLSX = require('xlsx');
var router = express.Router();
const {queryDatabase} = require('../db/mysql')


/* 数据看板接口*/
router.get('/result_info', function(req, res, next) {
  let strDate = req.query.date
  console.log(strDate);
  const sql = `SELECT * FROM result_table_${strDate}`;

    queryDatabase(sql,(err,results)=>{
      if(err){
        res.status(500).json({ error: '查询数据失败' });
        return;
      }
      res.json(results);
      console.log(results);
      
    })

  // res.render('index', { title: 'Express' });
});


//下载客户表_研发表展示
router.get("/get_customer_table",function(req,res,next){

  const pageNum = parseInt(req.query.pageNum) || 1; // 获取页码，默认第1页
  const pageSize = parseInt(req.query.pageSize) || 10; // 获取每页条数，默认10条
  const offset = (pageNum - 1) * pageSize; // 计算偏移量
  console.log(req.query);
  
  const mainSql = `SELECT c.user_name, c.remark_name, c.external_userid, c.from_service, GROUP_CONCAT(e.enterprise_label SEPARATOR ',') AS enterpris_label, c.loss_status, c.add_time, c.min_join_group_time, c.max_withdraw_group_time, c.delete_time FROM customer_table c LEFT JOIN enterprise_label_table e ON c.external_userid = e.external_userid GROUP BY c.external_userid LIMIT ${offset}, ${pageSize}`;


  // 查询总记录数的语句，只统计customer_table表中的记录数，不涉及关联表的计数影响
    const countSql = `SELECT COUNT(*) AS total_count FROM customer_table c`;

    queryDatabase( mainSql, (mainErr, results) => {
      if (mainErr) {
        console.error('查询客户数据失败：', mainErr);
        res.status(500).json({ success: false, message: '查询客户数据失败' });
        return;
      }
      
      queryDatabase(countSql,(countErr, countResult) => {
        if (countErr) {
          console.error('查询客户数据总记录数失败：', countErr);
          res.status(500).json({ success: false, message: '查询客户数据总记录数失败' });
          return;
        }
        const total = countResult[0].total_count;

        res.json({
          success: true,
          data: results,
          total: total,
          currentPage: pageNum,
          pageSize: pageSize
        });

      })
    })
  


})

//获取标签表不重复的全部标签列表
router.get("/get_lable_all", function(req,res,next){

  const sql = `SELECT DISTINCT enterprise_label FROM enterprise_label_table`;
  queryDatabase(sql,(err,results)=>{
    if(err){
      res.status(500).json({ error: '查询数据失败' });
      return;
    }
    let labels = results.map(row => row.enterprise_label);

    res.json({ success: true, data: labels });


  })
})

router.get("/query",function(req,res,next){
  const { user_name, from_service, enterpris_label, loss_status, add_time, min_join_group_time, max_withdraw_group_time, delete_time } = req.body;
  console.log(req.body);
  
   // 构建模糊查询的条件语句部分
   let conditions = [];
   if (user_name) {
       conditions.push(`c.user_name LIKE '%${user_name}%'`);
   }
   if (from_service) {
       conditions.push(`c.from_service LIKE '%${from_service}%'`);
   }
   if (enterpris_label.length > 0) {
       const labelConditions = enterpris_label.map(label => `e.enterprise_label LIKE '%${label}%'`);
       conditions.push(`(${labelConditions.join(' OR ')})`);
   }
   if (loss_status.length > 0) {
       const statusConditions = loss_status.map(status => `c.loss_status LIKE '%${status}%'`);
       conditions.push(`(${statusConditions.join(' OR ')})`);
   }
   if (add_time) {
       conditions.push(`c.add_time LIKE '%${add_time}%'`);
   }
   if (min_join_group_time) {
       conditions.push(`c.min_join_group_time LIKE '%${min_join_group_time}%'`);
   }
   if (max_withdraw_group_time) {
       conditions.push(`c.max_withdraw_group_time LIKE '%${max_withdraw_group_time}%'`);
   }
   if (delete_time) {
       conditions.push(`c.delete_time LIKE '%${delete_time}%'`);
   }

   // 组合完整的查询条件语句
   let whereClause = '';
   if (conditions.length > 0) {
       whereClause = 'WHERE ';
       whereClause += conditions.join(' AND ');
   }

   const mainSql = `
        SELECT
            c.add_time,
            c.delete_time,
            GROUP_CONCAT(e.enterprise_label SEPARATOR ',') AS enterpris_label,
            c.external_userid,
            c.from_service,
            c.loss_status,
            c.max_withdraw_group_time,
            c.min_join_group_time,
            c.remark_name,
            c.user_name
        FROM
            customer_table c
        LEFT JOIN
            enterprise_label_table e ON c.external_userid = e.external_userid
        ${whereClause}
        GROUP BY
            c.external_userid
    `;
   queryDatabase(mainSql,(err,results)=>{

    if(err){
      res.status(500).json({ error: '查询数据失败' });
      return;
    }
    res.json({ success: true, data: results });
   })
})
//下载入群表格的excel接口
router.get('/join_group', function(req,res,next){
  const sql = 'SELECT * FROM join_group_table';
  queryDatabase(sql,(err,results)=>{
    if(err){
      res.status(500).json({ error: '查询数据失败' });
      return;
    }

     // 定义表头数组，与数据库字段对应
     const headers = ["序号", "群用户名称", "入群的用户id", "入群名称", "入群时间"];

      // 将查询结果处理为包含表头的二维数组形式，方便转换为Excel工作表
      const dataWithHeaders = [headers].concat(results.map(item => [
      item.id,
      item.join_user_name,
      item.join_user_id,
      item.join_content,
      // 将时间戳转换为年月日格式
      new Date(item.join_group_time * 1000).toLocaleDateString()
  ]));

  // 使用xlsx库将处理后的数据转换为Excel工作簿对象
  const worksheet = XLSX.utils.aoa_to_sheet(dataWithHeaders);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

  // 将工作簿对象转换为二进制数据
  const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });

  // 设置响应头，告知浏览器这是一个Excel文件下载
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', 'attachment; filename="join_group_table.xlsx"');

  // 将Excel文件数据发送给浏览器
  res.send(excelBuffer);
  })

})


//下载删除朋友表格的excel接口
router.get('/delete_friend',function(req,res,next){
  const sql = 'SELECT * FROM delete_friend_table';
  queryDatabase(sql,(err,results)=>{

    if(err){
      res.status(500).json({ error: '查询数据失败' });
      return;
    }
    
    // 定义表头数组，与数据库字段对应
    const headers = ["序号", "删除的用户名", "删除用户id", "删除内容", "删除时间"];

    // 将查询结果处理为包含表头的二维数组形式，方便转换为Excel工作表
    const dataWithHeaders = [headers].concat(results.map(item => [
      item.id,
      item.delete_user_name,
      item.delete_user_id,
      item.delete_content,
      // 将时间戳转换为年月日格式
      new Date(item.delete_time * 1000).toLocaleDateString()
  ]));
  
    // 使用xlsx库将处理后的数据转换为Excel工作簿对象
    const worksheet = XLSX.utils.aoa_to_sheet(dataWithHeaders);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

  // 将工作簿对象转换为二进制数据
  const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });

  // 设置响应头，告知浏览器这是一个Excel文件下载
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', 'attachment; filename="join_group_table.xlsx"');

  // 将Excel文件数据发送给浏览器
  res.send(excelBuffer);

  })

})

//下载群用户表格的excel接口
router.get('/group_user',function(req,res,next){
  const sql = 'SELECT * FROM group_user_table';
  queryDatabase(sql,(err,results)=>{

    if(err){
      res.status(500).json({ error: '查询数据失败' });
      return;
    }
    
    // 定义表头数组，与数据库字段对应
    const headers = ["序号", "群用户名称", "群用户id", "群内容", "群时间"];

    // 将查询结果处理为包含表头的二维数组形式，方便转换为Excel工作表
    const dataWithHeaders = [headers].concat(results.map(item => [
      item.id,
      item.group_user_name,
      item.group_user_id,
      item.group_content,
      // 将时间戳转换为年月日格式
      new Date(item.group_time * 1000).toLocaleDateString()
  ]));
  
    // 使用xlsx库将处理后的数据转换为Excel工作簿对象
    const worksheet = XLSX.utils.aoa_to_sheet(dataWithHeaders);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

  // 将工作簿对象转换为二进制数据
  const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });

  // 设置响应头，告知浏览器这是一个Excel文件下载
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', 'attachment; filename="join_group_table.xlsx"');

  // 将Excel文件数据发送给浏览器
  res.send(excelBuffer);

  })

})

//下载结论表格的excel接口
router.get('/result',function(req,res,next){
  const sql = 'SELECT * FROM result_table_1210';
  queryDatabase(sql,(err,results)=>{

    if(err){
      res.status(500).json({ error: '查询数据失败' });
      return;
    }
    
    // 定义表头数组，与数据库字段对应
    const headers = ["序号", "客户表去水ID个数", "累计社群人数", "已退群的ID个数", "目前社群内人数","重复在群的ID的数量","今日新增客户人数","7日内新增客户人数","30日内新增客户人数"];

    // 将查询结果处理为包含表头的二维数组形式，方便转换为Excel工作表
    const dataWithHeaders = [headers].concat(results.map(item => [
      item.id,
      item.customer_num,
      item.community_num,
      item.withdrawed_num,
      item.in_community_num,
      item.in_many_group_num,
      item.number_of_new_customers_added_today,
      item.number_of_new_customers_added_within_7_days,
      item.number_of_new_customers_added_within_30_days,
  ]));
  
    // 使用xlsx库将处理后的数据转换为Excel工作簿对象
    const worksheet = XLSX.utils.aoa_to_sheet(dataWithHeaders);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

  // 将工作簿对象转换为二进制数据
  const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });

  // 设置响应头，告知浏览器这是一个Excel文件下载
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', 'attachment; filename="join_group_table.xlsx"');

  // 将Excel文件数据发送给浏览器
  res.send(excelBuffer);

  })

})

//下载水军表格的excel接口
router.get('/trolls',function(req,res,next){
  const sql = 'SELECT * FROM shang_and_di_table';
  queryDatabase(sql,(err,results)=>{

    if(err){
      res.status(500).json({ error: '查询数据失败' });
      return;
    }
    
    // 定义表头数组，与数据库字段对应
    const headers = ["序号", "水军名称","水军备注","水军ID"];

    // 将查询结果处理为包含表头的二维数组形式，方便转换为Excel工作表
    const dataWithHeaders = [headers].concat(results.map(item => [
      item.id,
      item.s_d_name,
      item.s_d_remark,
      item.s_d_id
  ]));
  
    // 使用xlsx库将处理后的数据转换为Excel工作簿对象
    const worksheet = XLSX.utils.aoa_to_sheet(dataWithHeaders);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

  // 将工作簿对象转换为二进制数据
  const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });

  // 设置响应头，告知浏览器这是一个Excel文件下载
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', 'attachment; filename="join_group_table.xlsx"');

  // 将Excel文件数据发送给浏览器
  res.send(excelBuffer);
  })
})

//下载退出群表格的excel接口
router.get('/withdrawal',function(req,res,next){
  const sql = 'SELECT * FROM withdrawal_group_table';
  queryDatabase(sql,(err,results)=>{

    if(err){
      res.status(500).json({ error: '查询数据失败' });
      return;
    }
    
    // 定义表头数组，与数据库字段对应
    const headers = ["序号", "退群的用户名称","退群的用户ID","退群内容","退群时间"];

    // 将查询结果处理为包含表头的二维数组形式，方便转换为Excel工作表
    const dataWithHeaders = [headers].concat(results.map(item => [
      item.id,
      item.withdrawal_user_name,
      item.withdrawal_user_id,
      item.withdrawal_content,
      // 将时间戳转换为年月日格式
      new Date(item.withdrawal_group_time * 1000).toLocaleDateString()
  ]));
  
    // 使用xlsx库将处理后的数据转换为Excel工作簿对象
    const worksheet = XLSX.utils.aoa_to_sheet(dataWithHeaders);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

  // 将工作簿对象转换为二进制数据
  const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });

  // 设置响应头，告知浏览器这是一个Excel文件下载
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', 'attachment; filename="join_group_table.xlsx"');

  // 将Excel文件数据发送给浏览器
  res.send(excelBuffer);
  })
})


module.exports = router;