marsview-enterprise-node/service/projects.service.js

const connection = require('../sql');
class ProjectsService {
  // 查询项目分类总条数
  async getCategoryCount(keyword, userId) {
    const statement = `
      SELECT 
        count(DISTINCT p.id) total
      FROM 
        projects p
      LEFT JOIN 
        pages pg ON p.id = pg.project_id
      LEFT JOIN 
        pages_role pr ON p.id = pr.page_id AND pr.user_id = ?
      WHERE 
        (p.name like COALESCE(CONCAT('%',?,'%'), p.name) OR ? IS NULL)
      AND 
        p.user_id = ?
      OR 
        pr.user_id = ?
    `;
    const [result] = await connection.execute(statement, [userId, keyword || null, keyword || null, userId, userId]);
    return result[0];
  }

  // 查询页面分类列表
  async getCategoryList(pageNum, pageSize, keyword, userId) {
    const offset = (+pageNum - 1) * pageSize + '';
    const statement = `
      SELECT 
        p.id,
        p.name,
        p.remark,
        p.user_id as userId,
        p.user_name as userName,
        p.logo,
        p.updated_at as updatedAt,
        COUNT(pg.id) as count
      FROM 
        projects p
      LEFT JOIN 
        pages pg ON p.id = pg.project_id
      LEFT JOIN 
        pages_role pr ON p.id = pr.page_id AND pr.user_id = ?
      WHERE 
        (p.name like COALESCE(CONCAT('%',?,'%'), p.name) OR ? IS NULL) 
      AND 
        p.user_id = ?
      OR 
        pr.user_id = ?
      GROUP BY 
        p.id, p.name, p.user_id
      ORDER BY
        p.updated_at DESC
      LIMIT ?, ?
    `;
    const [result] = await connection.execute(statement, [userId, keyword || null, keyword || null, userId, userId, offset, pageSize]);
    return result;
  }

  // 查询项目总条数：mars-admin
  async getProjectCount(isTemplate, userId) {
    let statement = `
      SELECT 
        count(id) total
      FROM 
        projects
      WHERE 
        1 = 1
    `;
    if (isTemplate !== '') {
      statement += ` and is_template = ${isTemplate}`;
    }
    if (userId) {
      statement += ` and user_id = ${userId}`;
    }
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 查询项目总条数：mars-admin
  async getProjectList(pageNum, pageSize, isTemplate, userId) {
    const offset = (+pageNum - 1) * pageSize + '';
    let statement = `
      SELECT 
        id,
        name,
        remark,
        logo,
        user_id as userId,
        user_name as userName,
        is_template as isTemplate,
        is_public as isPublic,
        breadcrumb,
        layout,
        menu_mode as menuMode,
        menu_theme_color as menuThemeColor,
        tag,
        footer,
        system_theme_color as systemThemeColor,
        updated_at as updatedAt,
        created_at as createdAt
      FROM 
        projects
      WHERE 
        1 = 1
    `;
    if (isTemplate !== '') {
      statement += ` and is_template = ${isTemplate}`;
    }
    if (userId) {
      statement += ` and user_id = ${userId}`;
    }
    statement += ` LIMIT ${offset}, ${pageSize}`;
    const [result] = await connection.execute(statement, []);
    return result;
  }

  async getProjectByName(name) {
    const statement = `SELECT * FROM projects WHERE name = ?;`;
    const [result] = await connection.execute(statement, [name]);
    return result;
  }

  async createProject(params) {
    const statement = 'INSERT INTO projects (name, remark, logo, user_name, user_id,  is_public) VALUES (?, ?, ?, ?, ?, ?);';
    const [result] = await connection.execute(statement, [
      params.name,
      params.remark,
      params.logo,
      params.userName,
      params.userId,
      params.isPublic || 1,
    ]);

    return result;
  }

  async installProject(params) {
    const statement =
      'INSERT INTO projects (name, remark, logo, user_name, user_id, is_public, breadcrumb, layout, menu_mode, menu_theme_color, tag, footer, system_theme_color, home_page) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
    const [result] = await connection.execute(statement, [
      params.name,
      params.remark,
      params.logo,
      params.userName,
      params.userId,
      2,
      params.breadcrumb,
      params.layout,
      params.menuMode,
      params.menuThemeColor,
      params.tag,
      params.footer,
      params.systemThemeColor,
      params.homePage || 'welcome',
    ]);

    return result;
  }

  async checkAuth(id) {
    const statement = `SELECT p.id,p.user_id userId, pr.user_id devUserId FROM projects p LEFT JOIN pages_role pr on p.id = pr.page_id WHERE p.id = ?;`;
    const [result] = await connection.execute(statement, [id]);
    return result;
  }

  async getProjectInfoById(id) {
    const statement =
      "SELECT id,name,remark,logo,user_id as userId,layout,menu_mode as menuMode,menu_theme_color as menuThemeColor,breadcrumb,tag,footer,is_public as isPublic,SUBSTRING_INDEX(user_name, '@', 1) as userName,show_header showHeader,show_tab_bar showTabBar,home_page homePage, extra_data extraData FROM projects WHERE id = ?;";
    const [result] = await connection.execute(statement, [id]);
    return result;
  }

  async deleteProject(id, userId) {
    const statement = 'DELETE FROM projects WHERE id = ? and user_id = ?;';
    const [result] = await connection.execute(statement, [id, userId]);
    return result;
  }

  async updateProjectInfo(params) {
    const statement =
      'UPDATE projects SET name = ?, remark = ?, logo = ?, layout = ?, menu_mode = ?, menu_theme_color = ?, system_theme_color = ?, breadcrumb = ?, tag = ?, footer = ?, is_public = ?, show_header = ?, show_tab_bar = ?, home_page = ?, extra_data = ? WHERE id = ?;';
    const [result] = await connection.execute(statement, [
      params.name,
      params.remark,
      params.logo,
      params.layout,
      params.menuMode,
      params.menuThemeColor,
      params.systemThemeColor,
      params.breadcrumb ?? 1,
      params.tag ?? 1,
      params.footer ?? 0,
      params.isPublic ?? 2,
      params.showHeader ?? 1,
      params.showTabBar ?? 0,
      params.homePage || 'welcome',
      params.extraData || '{}',
      params.id,
    ]);
    return result;
  }
  // 根据项目模板id查询项目信息
  async getProjectByTemplateId(id) {
    const statement = `
    SELECT 
      p.id, p.name, p.remark, p.logo, p.breadcrumb, p.layout, p.menu_mode menuMode, p.menu_theme_color menuThemeColor, p.tag, p.footer, p.system_theme_color systemThemeColor
    FROM 
      projects p 
    LEFT JOIN 
      templates t 
    on 
      p.id = t.union_id 
    WHERE 
      t.id = ?;
    `;
    const [result] = await connection.execute(statement, [id]);
    return result[0];
  }
  // 设置模板以后，更新项目标识
  async updateProjectTemplate(id, status) {
    const statement = 'UPDATE projects SET is_template = ? WHERE id = ?;';
    const [result] = await connection.execute(statement, [status, id]);
    return result;
  }

  // 注销项目
  async deleteAllProject(userId, userName) {
    const statement = 'DELETE FROM projects WHERE user_id = ? and user_name = ?;';
    const [result] = await connection.execute(statement, [userId, userName]);
    return result;
  }
}

module.exports = new ProjectsService();