newCashBook/expores_node/db/record.js

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

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

export async function record_update( params ) {
  const {
    id = "", // 要更新的记录的唯一标识符
    type_id = "",
    author_id = "",
    total_fee = "",
    remark = "",
    time = "",
    update_time = "",
  } = params
  return new Promise(async (resolve, reject) => {
    try {
      const sql = `
        UPDATE record SET type_id = ?, author_id = ?, total_fee = ?, remark = ?, time = ?, update_time = ? WHERE id = ?
      `;
      const values = [ type_id, author_id, total_fee, remark, time, update_time, id, ];
      // 执行更新语句
      connection.execute(sql, values, (err, result) => {
        if (err) {
          // 错误处理
          return resolve(false);
        }
        return resolve(result.changedRows > 0);
      });
    } catch (err) {
      return resolve(false);
    }
  });
}

// 关联 账单 跟 附件的数据
/*
`file_id` varchar(300) NOT NULL,
`record_id` varchar(300) NOT NULL,
`book_id` varchar(300) NOT NULL,
`user_id` varchar(300) NOT NULL,
*/
export function record_and_files({
  file_id = "",
  record_id = "",
  book_id = "",
  user_id = "",
  create_time = "",
  update_time = "",
}) {
  return new Promise(async (resolve, reject) => {
    try {
      const sql = `
        INSERT INTO record_files (file_id, record_id, book_id, user_id, create_time, update_time)
        VALUES (?, ?, ?, ?, ?, ?)
      `;
      const values = [
        file_id,
        record_id,
        book_id,
        user_id,
        create_time,
        update_time,
      ];
      // 直接接收 execute 返回的内容
      const result = await connection.execute(sql, values);
      return resolve(result);
    } catch (err) {
      return resolve(false);
    }
  });
}

export function recordRelation({
  name = "",
  record_id = "",
  book_id = "",
  user_id = "",
  create_time = "",
  update_time = "",
}) {
  return new Promise(async (resolve, reject) => {
    try {
      const sql = `
        INSERT INTO record_files (file_id, record_id, book_id, user_id, create_time, update_time)
        VALUES (?, ?, ?, ?, ?, ?)
      `;
      const values = [
        file_id,
        record_id,
        book_id,
        user_id,
        create_time,
        update_time,
      ];
      // 直接接收 execute 返回的内容
      const result = await connection.execute(sql, values);
      return resolve(result);
    } catch (err) {
      return resolve(false);
    }
  });
}

// 获取用户详情
export function getRecordInfoById(record_id) {
  return new Promise((resolve, reject) => {
    connection.query(
      `SELECT * FROM record WHERE id = ?`,
      [record_id],
      (err, rows) => {
        if (err) {
          // reject(err);
          resolve(false); // 如果存在记录，则返回 true，否则返回 false
        } else {
          resolve(rows.length > 0 ? rows[0] : false); // 如果存在记录，则返回 true，否则返回 false
        }
      }
    );
  });
}

// 根据日期查询记录
export function getRecordsInfoByTime(time, book_id) {
  return new Promise((resolve, reject) => {
    connection.query(
      `SELECT record.*, types.name AS type
        FROM
            record
        JOIN
            types
        ON
            record.type_id = types.id
        WHERE
        record.time = ?
        AND record.book_id = ?;`,
      [`${dayjs(time).format('YYYY-MM-DD')} 00:00:00`, book_id],
      (err, rows) => {
        if (err) {
          // reject(err);
          resolve(false); // 如果存在记录，则返回 true，否则返回 false
        } else {
          resolve(rows); // 如果存在记录，则返回 true，否则返回 false
        }
      }
    );
  });
}
// 根据月份查询记录
export function getRecordsInfoByMonth(time, book_id) {
  return new Promise((resolve, reject) => {
    connection.query(
      // `SELECT * FROM record WHERE DATE_FORMAT(time, '%Y-%m') = ? AND book_id = ?`,
      `SELECT record.*, types.name AS type
        FROM
            record
        JOIN
            types
        ON
            record.type_id = types.id
        WHERE
        DATE_FORMAT(record.time, '%Y-%m') = ?
        AND record.book_id = ?;`,
      [time, book_id],
      (err, rows) => {
        if (err) {
          // reject(err);
          resolve(false); // 如果存在记录，则返回 true，否则返回 false
        } else {
          resolve(rows); // 如果存在记录，则返回 true，否则返回 false
        }
      }
    );
  });
}


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