mcp_tech_kjb_detail_aligned_202603.sql
2.75 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
58
59
-- 科技部老师业绩明细 — 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';