newCashBook/expores_node/db/more_record.js

import connection from "./base.js";
import dayjs from "dayjs";

export async function addMoreRecord({
  more_id = "",
  name = "",
  remark = "",
  total_fee = "",
  book_id = "",
  author_id = "",
  create_time = "",
  update_time = "",
  start_time = "",
  end_time = "",
  type_id = "",
  log_day = "",
}) {
  return new Promise(async (resolve, reject) => {
    try {
      const sql = `
        INSERT INTO more_record (more_id, name, remark, total_fee, book_id, author_id, create_time, update_time, start_time, end_time, type_id, log_day)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      const values = [
        more_id,
        name,
        remark,
        total_fee,
        book_id,
        author_id,
        create_time,
        update_time,
        start_time,
        end_time,
        type_id,
        log_day,
      ];
      // 直接接收 execute 返回的内容
      connection.execute(sql, values, (result, fields) => {
        return resolve(fields.insertId);
      });
    } catch (err) {
      return resolve(false);
    }
  });
}

export async function updataMoreRecord(params) {
  const {
    more_id = "",
    type_id = "",
    name = "",
    remark = "",
    total_fee = "",
    update_time = "",
    start_time = "",
    end_time = "",
    log_day = "",
  } = params;
  return new Promise(async (resolve, reject) => {
    try {
      const sql = `
        UPDATE more_record SET type_id = ?, name = ?, total_fee = ?, remark = ?, update_time = ?, start_time= ?, end_time = ? , log_day = ? WHERE more_id = ?;
      `;
      const values = [
        type_id,
        name,
        total_fee,
        remark,
        update_time,
        start_time,
        end_time,
        log_day,
        more_id,
      ];
      // 执行更新语句
      connection.execute(sql, values, (err, result) => {
        if (err) {
          // 错误处理
          return resolve(false);
        }
        return resolve(result.changedRows > 0);
      });
    } catch (err) {
      return resolve(false);
    }
  });
}

export function delMoreRecord(more_id, author_id) {
  return new Promise((resolve, reject) => {
    try {
      const sql = `DELETE FROM more_record WHERE more_id = ? AND author_id = ?`;
      connection.query(sql, [more_id, author_id], (err, result) => {
        if (err) {
          // 如果执行过程中出现错误，返回 false
          return resolve(false);
        }
        // 删除成功后返回结果
        return resolve(result);
      });
    } catch (err) {
      // 捕获异常并返回 false
      return resolve(false);
    }
  });
}

// 根据日期查询记录
export function getMoreRecordsInfoByTime(time, day, book_id) {
  return new Promise((resolve, reject) => {
    connection.query(
      `SELECT more_record.*, types.name AS type
        FROM
            more_record
        JOIN
            types
        ON
            more_record.type_id = types.id
        WHERE
            more_record.start_time <= ? AND
            more_record.end_time >= ? AND
            more_record.log_day = ? AND
            more_record.book_id = ?;`,
      [
        `${dayjs(time).endOf("month").format("YYYY-MM-DD")}`,
        `${dayjs(time).startOf("month").format("YYYY-MM-DD")}`,
        day,
        book_id,
      ],
      (err, rows) => {
        if (err) {
          // reject(err);
          resolve(false); // 如果存在记录，则返回 true，否则返回 false
        } else {
          resolve(rows); // 如果存在记录，则返回 true，否则返回 false
        }
      }
    );
  });
}

// 根据月份查询记录
export function getMoreRecordsInfoByMonth(time, book_id) {
  return new Promise((resolve, reject) => {
    connection.query(
      `SELECT more_record.*, types.name AS type
        FROM
            more_record
        JOIN
            types
        ON
            more_record.type_id = types.id
        WHERE
            more_record.start_time <= ? AND
            more_record.end_time >= ? AND
            more_record.book_id = ?;`,
      [
        `${dayjs(time).endOf("month").format("YYYY-MM-DD")}`,
        `${time}-01`,
        book_id,
      ],
      (err, rows) => {
        if (err) {
          resolve(false);
        } else {
          resolve(rows);
        }
      }
    );
  });
}

// 查询所有账本信息
export function getMoreRecordList(book_id, author_id) {
  return new Promise((resolve, reject) => {
    connection.query(
      `SELECT * FROM more_record WHERE book_id = ? AND author_id = ?`,
      [book_id, author_id],
      (err, rows) => {
        if (err) {
          resolve(false); // 如果存在记录，则返回 true，否则返回 false
        } else {
          resolve(rows); // 如果存在记录，则返回 true，否则返回 false
        }
      }
    );
  });
}

// 查询账本信息
export function getMoreRecordByMoreId(more_id, author_id) {
  return new Promise((resolve, reject) => {
    connection.query(
      `SELECT * FROM more_record WHERE more_id = ? AND author_id = ?`,
      [more_id, author_id],
      (err, rows) => {
        if (err) {
          resolve(false); // 如果存在记录，则返回 true，否则返回 false
        } else {
          resolve(rows.length > 0 ? rows[0] : false); // 如果存在记录，则返回 true，否则返回 false
        }
      }
    );
  });
}