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

const connection = require('../sql');
class TemplatesService {
  async listCount(type, keyword) {
    let statement = `
      SELECT 
        COUNT(id) total 
      FROM 
        templates
      WHERE
        type = ${type}
    `;
    if (keyword) {
      statement += ` and name LIKE '%${keyword}%'`;
    }
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  async list(pageNum, pageSize, type, keyword) {
    const offset = (+pageNum - 1) * pageSize + '';
    const limit = pageSize;
    let statement = `
      SELECT 
        id,
        name,
        description,
        union_id as unionId,
        tags,
        user_name as userName,
        install_count as installCount,
        image_url as imageUrl,
        is_recommend as isRecommend,
        updated_at as updatedAt,
        created_at as createdAt
      FROM 
        templates
      WHERE
        (name LIKE COALESCE(CONCAT('%',?,'%'), name) OR ? IS NULL)
      AND
        type = ${type}
      LIMIT ${offset},${limit};`;
    const [result] = await connection.execute(statement, [keyword || null, keyword || null]);
    return result;
  }

  async create(params) {
    const statement =
      'INSERT INTO templates (union_id, type, name, description, user_id, user_name, image_url, tags) VALUES (?, ?, ?, ?, ?, ?, ?, ?);';
    const [result] = await connection.execute(statement, [
      params.unionId,
      params.type,
      params.name,
      params.description,
      params.userId,
      params.userName,
      params.imageUrl,
      params.tags,
    ]);
    return result;
  }

  async getDetailById(id) {
    const statement =
      'SELECT p.id, t.name, t.description , t.user_id, t.user_name, t.image_url, t.install_count FROM `projects` p RIGHT JOIN templates t on t.union_id = p.`id` WHERE t.id = ?;';
    const [result] = await connection.execute(statement, [id]);
    return result;
  }

  async deleteById(union_id, user_id) {
    const statement = 'DELETE FROM templates WHERE union_id = ? and user_id = ?;';
    const [result] = await connection.execute(statement, [union_id, user_id]);
    return result;
  }

  async update(id, name, description, isRecommend, imageUrl, installCount, user_id) {
    let statement = `UPDATE templates SET updated_at = ?`;
    let sql_params = [new Date()];
    if (name) {
      statement += `, name = ?`;
      sql_params.push(name);
    }

    if (description) {
      statement += `, description = ?`;
      sql_params.push(description);
    }

    if (isRecommend) {
      statement += `, is_recommend = ?`;
      sql_params.push(isRecommend);
    }

    if (imageUrl) {
      statement += `, image_url = ?`;
      sql_params.push(imageUrl);
    }

    if (installCount) {
      statement += `, install_count += 1`;
    }

    statement += ` WHERE id = ? and user_id = ?;`;
    sql_params.push(id);
    sql_params.push(user_id);
    const [result] = await connection.execute(statement, sql_params);
    return result;
  }

  async updateInstallCount(id) {
    let statement = `UPDATE templates SET install_count = install_count + 1 WHERE id = ?;`;
    const [result] = await connection.execute(statement, [id]);
    return result;
  }
}

module.exports = new TemplatesService();