CalendarManager/backend/index.js

import express, { json } from 'express';
import { createConnection } from 'mysql2';
const app = express();
const port = 3000;

// 啟用 CORS 和 JSON 處理
app.use(json());
app.use((req, res, next) => {
  res.header('Access-Control-Allow-Origin', '*');
  res.header('Access-Control-Allow-Headers', 'Origin, X-Requested-With, Content-Type, Accept');
  next();
});

// 建立 MySQL 連線
const connection = createConnection({
  host: '192.168.1.69',
  port: 3310,
  user: 'root',
  password: '123456',
  database: 'eoffice11'
});

// 測試資料庫連線
connection.connect(err => {
  if (err) {
    console.error('無法連接到 MySQL 資料庫:', err);
    return;
  }
  console.log('已成功連接到 MySQL 資料庫');
});

const statusMap = {
  0: '待審批',
  1: '審批中',
  2: '已批准',
  3: '拒絕',
  4: '會議開始',
  5: '會議結束'
};

function toTaipeiISOString(date) {
  if (!date) return null;
  return new Date(date).toLocaleString('sv-SE', { timeZone: 'Asia/Taipei' });
}

// 取得身分驗證
app.get('/api/calendar/getAuth', (req, res) => {
  const { id } = req.query;
  const sql = `
    SELECT ur.user_id
    FROM user_role ur
    JOIN role_basic_info rbi ON ur.role_id = rbi.role_id
    WHERE rbi.role_name = '櫃檯人員' and ur.user_id = '${id}'
  `;
  connection.query(sql, (err, results) => {
    if (err) {
      console.error('查詢錯誤:', err);
      return res.status(500).json({ error: '資料庫查詢失敗' });
    }
    if (results.length > 0) {
      res.json({ success: true });
    } else {
      res.json({ success: false });
    }
  });
});
// 取得所有會議室
app.get('/api/calendar/getMeetingRoom', (req, res) => {
  connection.query('SELECT room_id, room_name FROM meeting_rooms', (err, results) => {
    if (err) {
      console.error('查詢錯誤:', err);
      return res.status(500).json({ error: '資料庫查詢失敗' });
    }
    const mapped = results.map(row => ({
      id: row.room_id,
      name: row.room_name
    }));
    res.json(mapped);
  });
});

// 取得該會議資料
app.get('/api/calendar/getMeetingApplyById', (req, res) => {
  const { meetingId } = req.query;
  const sql = `
    SELECT ma.*, ubi.user_name AS apply_user_name
    FROM meeting_apply ma
    LEFT JOIN user_basic_info ubi ON ma.meeting_apply_user = ubi.user_id
    WHERE ma.meeting_apply_id = ? LIMIT 1
`;
  connection.query(sql, [meetingId], (err, results) => {
    if (err) {
      console.error('查詢錯誤:', err);
      return res.status(500).json({ error: '資料庫查詢失敗' });
    }
    if (results.length > 0) {
      const row = results[0];
      res.json({
        id: row.meeting_apply_id,
        roomId: row.meeting_room_id,
        subject: row.meeting_subject,
        applyUser: row.meeting_apply_user,
        applyUserName: row.apply_user_name,
        otherJoinMember: row.meeting_other_join_member,
        type: row.meeting_type,
        beginTime: toTaipeiISOString(row.meeting_begin_time),
        endTime: toTaipeiISOString(row.meeting_end_time),
        remark: row.meeting_remark,
        status: statusMap[row.meeting_status],
        approvalOpinion: row.meeting_approval_opinion,
        beginRemark: row.meeting_begin_remark,
        endRemark: row.meeting_end_remark,
        conflict: row.conflict,
        falseDelete: row.false_delete,
        applyTime: toTaipeiISOString(row.meeting_apply_time),
        deletedAt: toTaipeiISOString(row.deleted_at),
        createdAt: toTaipeiISOString(row.created_at),
        updatedAt: toTaipeiISOString(row.updated_at),
        reminderTiming: row.meeting_reminder_timing,
        signType: row.sign_type,
        signWifi: row.meeting_sign_wifi,
        externalUser: row.meeting_external_user,
        reminderTime: toTaipeiISOString(row.meeting_reminder_time),
        signUser: row.meeting_sign_user,
        externalReminderType: row.external_reminder_type,
        sign: row.sign,
        response: row.meeting_response,
        approvalUser: row.meeting_approval_user,
        createTime: toTaipeiISOString(row.meeting_create_time),
        attenceType: row.attence_type,
        interfaceId: row.interface_id,
        videoInfo: row.meeting_video_info,
        isRepeat: !!row.is_repeat,
        repeatInterval: row.repeat_interval,
        repeatType: row.repeat_type,
        repeatEndType: row.repeat_end_type,
        repeatEndNumber: row.repeat_end_number,
        repeatRemove: row.repeat_remove,
        parentId: row.meeting_parent_id,
        repeatEndDate: toTaipeiISOString(row.repeat_end_date),
        cycleBeginTime: toTaipeiISOString(row.cycle_begin_time),
        cycleEndTime: toTaipeiISOString(row.cycle_end_time),
        createFrom: row.create_from
      });
    } else {
      res.status(404).json({ error: '查無資料' });
    }
  });
});

// 取得會議申請（依月份）
app.get('/api/calendar/getMeetingApply', (req, res) => {
  const { Date: dateStr } = req.query; // YYYYMM
  const { roomId } = req.query; //如果為null，則查詢所有會議室
  const { old } = req.query; //如果為null，則查詢所有會議室
  if (!dateStr || !/^\d{6}$/.test(dateStr)) {
    return res.status(400).json({ error: '請提供正確的 Date 參數 (YYYYMM)' });
  }

  const year = dateStr.substring(0, 4);
  const month = dateStr.substring(4, 6);
  const start = `${year}-${month}-01`;
  const end = `${year}-${month}-31`;
  const sql = `
    select 
        ma.meeting_apply_id,
        ma.meeting_room_id,
        ubi.user_name as meeting_apply_user,
        ma.meeting_subject,
        ma.meeting_remark,
        ma.meeting_begin_time,
        ma.meeting_end_time,
        ma.meeting_status,
        ma.meeting_parent_id,
        ma.false_delete,
        ma.is_repeat,
        ma.created_at,
        ma.updated_at
    from meeting_apply ma
    left join user_basic_info ubi
        on ma.meeting_apply_user = ubi.user_id
    where ma.is_repeat = 0
    ${roomId ? `and ma.meeting_room_id = ${roomId}` : ''}
    ${old === 'true' ? '' : `and ma.meeting_status != 5`}
    ${old === 'true' ? '' : `and ma.false_delete != 1`}
    and ma.meeting_begin_time >= '${start}'
    and ma.meeting_begin_time <= '${end}'
    order by ma.meeting_begin_time asc
  `;
  connection.query(sql, [start, end], (err, results) => {
    if (err) {
      console.error('查詢錯誤:', err);
      return res.status(500).json({ error: '資料庫查詢失敗' });
    }
    // 依照前端interface格式轉換欄位
    const mapped = results.map(row => ({
      id: row.meeting_apply_id,
      roomId: row.meeting_room_id,
      subject: row.meeting_subject,
      remark: row.meeting_remark,
      beginTime: toTaipeiISOString(row.meeting_begin_time),
      endTime: toTaipeiISOString(row.meeting_end_time),
      status: statusMap[row.meeting_status],
      parentId: row.meeting_parent_id,
      falseDelete: row.false_delete,
      isRepeat: !!row.is_repeat,
      createdBy: row.meeting_apply_user,
      createdAt: toTaipeiISOString(row.created_at),
      updatedAt: toTaipeiISOString(row.updated_at)
    }));
    res.json(mapped);
  });
});

app.put('/api/calendar/closeMeeting', (req, res) => {
  const { meetingId } = req.body;
  const sql = `
    UPDATE meeting_apply 
    SET meeting_status = 5, false_delete = 1
    WHERE meeting_apply_id = ${meetingId}
  `;
  connection.query(sql, (err, results) => {
    if (err) {
      console.error('查詢錯誤:', err);
      return res.status(500).json({ error: '資料庫查詢失敗' });
    }
    res.json(results);
  });
});

app.put('/api/calendar/closeAllRepeatMeeting', (req, res) => {
  const { parentId } = req.body;
  const sql = `
    UPDATE meeting_apply 
    SET meeting_status = 5, false_delete = 1
    WHERE meeting_parent_id = ${parentId}
    OR meeting_apply_id = ${parentId}
  `;
  connection.query(sql, (err, results) => {
    if (err) {
      console.error('查詢錯誤:', err);
      return res.status(500).json({ error: '資料庫查詢失敗' });
    }
    res.json(results);
  });
});

// 啟動伺服器
app.listen(port, () => {
  console.log(`API 伺服器運行於 http://localhost:${port}`);
});