-- 科技部老师业绩明细 — MCP 取数口径（与日报 GetTechTeacherDailyStatistics 落月一致）
-- 统计月：2026-03（按需改日期）
-- 品项：仍限 F_ItemCategory='科美'（与历史 MCP 明细导出一致；若要对齐「工资/日报全品项」可去掉该行）
--
-- 使用方式：
-- 1) 用 MCP MySQL 分别执行下面「开单明细」「退卡明细」两段 SELECT，各得到一组行。
-- 2) 将两批结果合并为一个 JSON 数组（字段名需与脚本 HEADERS 的 key 一致：bizType, storeName, ...）。
--    若 MCP 返回列名为小写，可用 jq 或脚本映射为 camelCase；或查询里已用下列别名。
-- 3) 保存为两个 JSON 数组后合并，例如：
--    python3 ExportFiles/merge_mcp_tech_kjb_detail_json.py 开单.json 退卡.json ExportFiles/_mcp_tech_kjb_detail_202603.json
-- 4) 执行：python3 ExportFiles/_generate_mcp_tech_kjb_detail_excel.py

-- ========== 开单明细（按 lq_kd_kdjlb.kdrq 落月，不用 lq_kd_kjbsyj.yjsj 筛月）==========
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-03-01' AND '2026-03-31';

-- ========== 退卡明细（按 lq_hytk_hytk.tksj 落月，不用 lq_hytk_kjbsyj.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-03-01' AND '2026-03-31';