d8035736
 “wangming”
Refactor project ...
|
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
-- ============================================
-- 修复消耗单主表（lq_xh_hyhk）F_OvertimeSgfy 和 sgfy 历史数据
-- ============================================
-- 背景：主表加班手工费已改为「健康师原始手工费之和 × 加班系数」，科技部不参与加班。
-- 本脚本修复历史数据，使 F_OvertimeSgfy 和 sgfy 符合新逻辑。
--
-- 修复逻辑：
-- F_OvertimeSgfy = 健康师原始手工费之和 × F_OvertimeCoefficient
-- sgfy = F_OriginalSgfy + F_OvertimeSgfy
--
-- 健康师原始手工费：优先用 F_OriginalLaborCost，为空时用 F_LaborCost - F_OvertimeLaborCost
-- F_OriginalSgfy 为空时：用 健康师原始之和 + 科技部原始之和 补全
--
-- 执行前请先备份！建议在测试环境验证后再在生产环境执行。
-- ============================================
-- 1. 预览：查看需要修复的记录（只读，可先执行验证）
-- ============================================
/*
SELECT
h.F_Id,
h.F_OriginalSgfy AS 当前原始手工费,
h.F_OvertimeSgfy AS 当前加班手工费,
h.sgfy AS 当前最终手工费,
h.F_OvertimeCoefficient AS 加班系数,
COALESCE(j.jks_sum, 0) AS 健康师原始手工费之和,
COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0) AS 新加班手工费,
COALESCE(h.F_OriginalSgfy, COALESCE(j.jks_sum, 0) + COALESCE(k.kjb_sum, 0)) + (COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0)) AS 新最终手工费
FROM lq_xh_hyhk h
LEFT JOIN (
SELECT glkdbh,
SUM(COALESCE(F_OriginalLaborCost, GREATEST(0, COALESCE(F_LaborCost, 0) - COALESCE(F_OvertimeLaborCost, 0)))) AS jks_sum
FROM lq_xh_jksyj
WHERE F_IsEffective = 1
GROUP BY glkdbh
) j ON h.F_Id = j.glkdbh
LEFT JOIN (
SELECT glkdbh,
SUM(COALESCE(F_OriginalLaborCost, F_LaborCost)) AS kjb_sum
FROM lq_xh_kjbsyj
WHERE F_IsEffective = 1
GROUP BY glkdbh
) k ON h.F_Id = k.glkdbh
WHERE h.F_IsEffective = 1
AND (
COALESCE(h.F_OvertimeSgfy, 0) != COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0)
OR COALESCE(h.sgfy, 0) != COALESCE(h.F_OriginalSgfy, COALESCE(j.jks_sum, 0) + COALESCE(k.kjb_sum, 0)) + (COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0))
)
LIMIT 50;
*/
-- ============================================
-- 2. 执行修复：更新 F_OvertimeSgfy 和 sgfy
-- ============================================
UPDATE lq_xh_hyhk h
INNER JOIN (
SELECT glkdbh,
SUM(COALESCE(F_OriginalLaborCost, GREATEST(0, COALESCE(F_LaborCost, 0) - COALESCE(F_OvertimeLaborCost, 0)))) AS jks_sum
FROM lq_xh_jksyj
WHERE F_IsEffective = 1
GROUP BY glkdbh
) j ON h.F_Id = j.glkdbh
LEFT JOIN (
SELECT glkdbh,
SUM(COALESCE(F_OriginalLaborCost, F_LaborCost)) AS kjb_sum
FROM lq_xh_kjbsyj
WHERE F_IsEffective = 1
GROUP BY glkdbh
) k ON h.F_Id = k.glkdbh
SET
h.F_OvertimeSgfy = COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0),
h.sgfy = COALESCE(h.F_OriginalSgfy, COALESCE(j.jks_sum, 0) + COALESCE(k.kjb_sum, 0)) + (COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0))
WHERE h.F_IsEffective = 1;
-- 说明：上述 UPDATE 使用 INNER JOIN j，因此仅更新「存在健康师业绩」的耗卡记录。
-- 若耗卡仅有科技部、无健康师，则 j 子查询无结果，该记录不会被更新。
-- 对于「仅科技部」的耗卡，正确逻辑应为：F_OvertimeSgfy=0，sgfy=F_OriginalSgfy，需单独处理。
-- ============================================
-- 3. 补充修复：仅科技部、无健康师的耗卡（F_OvertimeSgfy 应为 0，sgfy = 原始手工费）
-- ============================================
UPDATE lq_xh_hyhk h
LEFT JOIN (
SELECT glkdbh
FROM lq_xh_jksyj
WHERE F_IsEffective = 1
GROUP BY glkdbh
) j ON h.F_Id = j.glkdbh
LEFT JOIN (
SELECT glkdbh,
SUM(COALESCE(F_OriginalLaborCost, F_LaborCost)) AS kjb_sum
FROM lq_xh_kjbsyj
WHERE F_IsEffective = 1
GROUP BY glkdbh
) k ON h.F_Id = k.glkdbh
SET
h.F_OvertimeSgfy = 0,
h.sgfy = COALESCE(h.F_OriginalSgfy, COALESCE(k.kjb_sum, 0), h.sgfy)
WHERE h.F_IsEffective = 1
AND j.glkdbh IS NULL
AND (COALESCE(h.F_OvertimeSgfy, 0) != 0);
-- ============================================
-- 4. 验证修复结果（可选）
-- ============================================
-- 检查是否仍有不一致记录（应返回 0）
/*
SELECT COUNT(*) AS 仍不一致记录数
FROM lq_xh_hyhk h
LEFT JOIN (
SELECT glkdbh,
SUM(COALESCE(F_OriginalLaborCost, GREATEST(0, COALESCE(F_LaborCost, 0) - COALESCE(F_OvertimeLaborCost, 0)))) AS jks_sum
FROM lq_xh_jksyj
WHERE F_IsEffective = 1
GROUP BY glkdbh
) j ON h.F_Id = j.glkdbh
LEFT JOIN (
SELECT glkdbh,
SUM(COALESCE(F_OriginalLaborCost, F_LaborCost)) AS kjb_sum
FROM lq_xh_kjbsyj
WHERE F_IsEffective = 1
GROUP BY glkdbh
) k ON h.F_Id = k.glkdbh
WHERE h.F_IsEffective = 1
AND (
COALESCE(h.F_OvertimeSgfy, 0) != COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0)
OR COALESCE(h.sgfy, 0) != COALESCE(h.F_OriginalSgfy, COALESCE(j.jks_sum, 0) + COALESCE(k.kjb_sum, 0)) + (COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0))
);
*/
|
