-- 科技部老师业绩明细 — MCP 取数口径（与日报 GetTechTeacherDailyStatistics 落月一致）
-- 统计月：2026-04
-- 品项：仍限 F_ItemCategory='科美'（与历史 MCP 明细导出一致）
--
-- 使用方式：
-- 1) 用 MCP MySQL 分别执行下面「开单明细」「退卡明细」两段 SELECT，各导出为 JSON 数组。
-- 2) 合并：python3 ExportFiles/merge_mcp_tech_kjb_detail_json.py 开单.json 退卡.json ExportFiles/_mcp_tech_kjb_detail_202604.json
-- 3) 生成 Excel：python3 ExportFiles/_generate_mcp_tech_kjb_detail_excel.py --json ExportFiles/_mcp_tech_kjb_detail_202604.json --out ExportFiles/2026年4月_科技部老师业绩明细_MCP.xlsx --month 2026-04 --sql-ref mcp_tech_kjb_detail_aligned_202604.sql
--
-- ========== 开单明细（按 lq_kd_kdjlb.kdrq 落月）==========
SELECT
  '开单' AS bizType,
  IFNULL(m.dm, '') AS storeName,
  IFNULL(k.kjbls, '') AS teacherId,
  IFNULL(k.kjblsxm, '') AS teacherName,
  IFNULL(k.kjblszh, '') AS teacherAccount,
  IFNULL(k.F_ItemName, '') AS itemName,
  IFNULL(k.F_ItemId, '') AS itemId,
  IFNULL(k.F_ItemCategory, '') AS itemCategory,
  IFNULL(k.F_BeautyType, '') AS beautyType,
  CAST(NULLIF(TRIM(k.kjblsyj), '') AS DECIMAL(18, 2)) AS amount,
  k.yjsj AS bizTime,
  IFNULL(k.glkdbh, '') AS refBillingId,
  IFNULL(k.F_kdpxid, '') AS billingItemLineId,
  COALESCE(k.F_LaborCost, 0) AS laborCost,
  kd.kdrq AS billingDate
FROM lq_kd_kjbsyj k
INNER JOIN lq_kd_kdjlb kd ON k.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
LEFT JOIN lq_mdxx m ON m.F_Id = k.F_StoreId
WHERE k.F_IsEffective = 1
  AND k.F_ItemCategory = '科美'
  AND DATE(kd.kdrq) BETWEEN '2026-04-01' AND '2026-04-30';

-- ========== 退卡明细（按 lq_hytk_hytk.tksj 落月）==========
SELECT
  '退卡' AS bizType,
  IFNULL(m.dm, '') AS storeName,
  IFNULL(r.kjbls, '') AS teacherId,
  IFNULL(r.kjblsxm, '') AS teacherName,
  IFNULL(r.kjblszh, '') AS teacherAccount,
  IFNULL(r.F_ItemName, '') AS itemName,
  IFNULL(r.F_ItemId, '') AS itemId,
  IFNULL(r.F_ItemCategory, '') AS itemCategory,
  IFNULL(r.F_BeautyType, '') AS beautyType,
  COALESCE(r.kjblsyj, 0) AS amount,
  r.tksj AS bizTime,
  IFNULL(r.gltkbh, '') AS refBillingId,
  IFNULL(r.F_CardReturn, '') AS billingItemLineId,
  COALESCE(r.F_LaborCost, 0) AS laborCost,
  hy.tksj AS billingDate
FROM lq_hytk_kjbsyj r
INNER JOIN lq_hytk_hytk hy ON r.gltkbh = hy.F_Id AND hy.F_IsEffective = 1
LEFT JOIN lq_mdxx m ON m.F_Id = r.F_StoreId
WHERE r.F_IsEffective = 1
  AND r.F_ItemCategory = '科美'
  AND DATE(hy.tksj) BETWEEN '2026-04-01' AND '2026-04-30';