Báo Cáo Tự Động Với Phần Mềm Quản Lý: Tiết Kiệm 10 Giờ/Tuần Cho Doanh Nghiệp

// Apps Script: Tao bao cao ton kho hang tuan
// Chay vao 8h sang thu 2 hang tuan
function weeklyInventoryReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const tonKhoSheet = ss.getSheetByName("TonKho")
  const data = tonKhoSheet.getDataRange().getValues()

  // Phan loai san pham theo trang thai ton kho
  const hetHang = []
  const saptHet = [] // ton < nguong
  const tonNhieu = [] // ton > 3 lan nguong

  for (let i = 1; i < data.length; i++) {
    const [maSP, tenSP, ton, nguong, giaNhap] = data[i]
    if (!tenSP) continue

    if (ton === 0) hetHang.push({ maSP, tenSP, ton, nguong })
    else if (ton <= nguong) saptHet.push({ maSP, tenSP, ton, nguong })
    else if (ton > nguong * 3) tonNhieu.push({ maSP, tenSP, ton, nguong, giaNhap, giaTriTon: ton * giaNhap })
  }

  // Tao noi dung email
  let emailBody = "BAO CAO TON KHO TUAN " + getWeekNumber() + "\n"
  emailBody += "=".repeat(50) + "\n\n"

  if (hetHang.length > 0) {
    emailBody += "HET HANG (" + hetHang.length + " san pham):\n"
    hetHang.forEach(sp => { emailBody += "  - " + sp.tenSP + " (MA: " + sp.maSP + ")\n" })
    emailBody += "\n"
  }

  if (saptHet.length > 0) {
    emailBody += "SAP HET HANG (" + saptHet.length + " san pham):\n"
    saptHet.forEach(sp => { emailBody += "  - " + sp.tenSP + ": con " + sp.ton + "/" + sp.nguong + "\n" })
    emailBody += "\n"
  }

  GmailApp.sendEmail(
    Session.getActiveUser().getEmail(),
    "Bao Cao Ton Kho Tuan " + getWeekNumber(),
    emailBody
  )
}

// Gui bao cao doanh thu hang ngay luc 7:30 sang
function sendDailySalesReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const baoCaoSheet = ss.getSheetByName("BaoCaoNgay")

  // Lay du lieu tu sheet bao cao
  const doanhThu = baoCaoSheet.getRange("B2").getValue()
  const soDon = baoCaoSheet.getRange("C2").getValue()
  const donTrungBinh = baoCaoSheet.getRange("D2").getValue()
  const tangTruong = baoCaoSheet.getRange("E2").getValue()
  const topSanPham = baoCaoSheet.getRange("H2:I6").getValues()

  // Dinh dang so tien Viet Nam
  const formatVND = (n) => new Intl.NumberFormat("vi-VN", {style:"currency",currency:"VND"}).format(n)

  // Tao noi dung email HTML
  const yesterday = new Date(Date.now() - 86400000)
  const dateStr = yesterday.toLocaleDateString("vi-VN")

  let htmlBody = "
Bao Cao Doanh Thu Ngay " + dateStr + "
"
  htmlBody += ""
  htmlBody += ""
  htmlBody += ""
  htmlBody += ""
  htmlBody += ""
  htmlBody += "
Doanh Thu
" + formatVND(doanhThu) + "
So Don Hang
" + soDon + "
Don Trung Binh
" + formatVND(donTrungBinh) + "
Tang Truong
" + (tangTruong*100).toFixed(1) + "%

"
  htmlBody += "
Top San Pham Ban Chay
"
  topSanPham.forEach(row => {
    if (row[0]) htmlBody += "
" + row[0] + ": " + row[1] + " cai
"
  })
  htmlBody += "
"
  htmlBody += "
Xem chi tiet tren Google Sheets
"

  // Gui email
  GmailApp.sendEmail(
    "owner@company.com",     // Nguoi nhan
    "Doanh Thu " + dateStr + ": " + formatVND(doanhThu),  // Tieu de
    "",                      // Plain text (trong)
    { htmlBody }             // HTML body
  )
  Logger.log("Email sent for " + dateStr)
}

// Danh sach nguoi nhan theo cap bac
const RECIPIENTS = {
  daily: ["owner@company.com", "manager@company.com"],
  weekly: ["owner@company.com", "manager@company.com", "accountant@company.com"],
  monthly: ["owner@company.com", "accountant@company.com", "investor@company.com"]
}

// Gui cho nhieu nguoi
GmailApp.sendEmail(
  RECIPIENTS.daily.join(","),
  subject,
  plainText,
  { htmlBody, cc: "cfo@company.com" }
)

// Cai dat toan bo trigger cho he thong bao cao
function setupAllTriggers() {
  // Xoa trigger cu truoc khi tao moi
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t))

  // 1. Bao cao doanh thu hang ngay luc 7:30 sang
  ScriptApp.newTrigger("sendDailySalesReport")
    .timeBased()
    .atHour(7)
    .everyDays(1)
    .create()

  // 2. Tong ket ton kho moi thu 2 luc 8h sang
  ScriptApp.newTrigger("weeklyInventoryReport")
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(8)
    .create()

  // 3. Bao cao P&L cuoi thang (ngay 28, phong truong hop thang ngan)
  ScriptApp.newTrigger("monthlyPLReport")
    .timeBased()
    .onMonthDay(28)
    .atHour(6)
    .create()

  // 4. Canh bao ton kho thap moi ngay luc 9h sang
  ScriptApp.newTrigger("checkLowStock")
    .timeBased()
    .atHour(9)
    .everyDays(1)
    .create()

  Logger.log("Tao " + ScriptApp.getProjectTriggers().length + " triggers thanh cong")
}

// Chay khi co du lieu moi nhap vao Sheet "DonHang"
function onDonHangEdit(e) {
  const sheet = e.range.getSheet()
  if (sheet.getName() !== "DonHang") return

  const row = e.range.getRow()
  const statusCol = 5 // Cot E = TrangThai
  const status = sheet.getRange(row, statusCol).getValue()

  // Khi don hang duoc cap nhat thanh PAID
  if (status === "PAID") {
    const tenKH = sheet.getRange(row, 2).getValue()
    const email = sheet.getRange(row, 9).getValue()
    const tongTien = sheet.getRange(row, 6).getValue()

    // Gui email xac nhan cho khach hang
    if (email) {
      sendOrderConfirmation(email, tenKH, tongTien)
    }

    // Cap nhat bao cao dashboard
    refreshDashboard()
  }
}

// Cai trigger onChange
// Chu y: Trigger onChange can cai qua giao dien
// Apps Script > Triggers > Add trigger > onEdit > Spreadsheet

// Bat buoc refresh cac ham volatile (NOW, TODAY, RAND) moi 5 phut
// Them cot phu "last_refresh" va cap nhat no
function forceRefresh() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getSheetByName("Dashboard")
  // Cap nhat gia tri refresh time de trigger recalculation
  sheet.getRange("Z1").setValue(new Date())
}

// Cai trigger chay moi 5 phut
ScriptApp.newTrigger("forceRefresh")
  .timeBased()
  .everyMinutes(5)
  .create()