mcp_tech_kjb_detail_aligned_202605.sql
2.51 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- 科技部老师业绩明细 — MCP 取数口径（与日报 GetTechTeacherDailyStatistics 落月一致）
-- 统计月：2026-05
-- 品项：仍限 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_202605.json
-- 3) 生成 Excel：python3 ExportFiles/_generate_mcp_tech_kjb_detail_excel.py --json ExportFiles/_mcp_tech_kjb_detail_202605.json --out ExportFiles/2026年5月_科技部老师业绩明细_MCP.xlsx --month 2026-05 --sql-ref mcp_tech_kjb_detail_aligned_202605.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-05-01' AND '2026-05-31';
-- ========== 退卡明细（按 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-05-01' AND '2026-05-31';