需求

User Story
作为一名客服经理(Support Manager),
当客户产生多次投诉工单时,
我希望系统能自动统计每个联系人的投诉次数,并在短期内投诉过多时自动升级工单,
同时对于长期未再投诉的客户,系统能自动清零其投诉计数,
以便更好地识别高风险客户并合理分配客服资源。

验收标准(Acceptance Criteria)
创建投诉 Case 时更新 Contact 统计字段

使用标准对象 Case 和 Contact。
在 Contact 上新增字段:
Number_of_Complaints__c(Number)
Last_Complaint_Date__c(Date)
当新 Case 被创建,且满足:
Case.Type = ‘Complaint’ (或你指定的值)
且 ContactId 不为空
系统应:
将对应 Contact 的 Number_of_Complaints__c 自增 1
将 Last_Complaint_Date__c 更新为 Case 的 CreatedDate(取日期部分)
必须支持:
一次插入多条 Case(批量插入/导入)
频繁投诉自动升级 Case

业务规则示例(可按需要调整):
若某个 Contact 在最近 90 天内的投诉次数 > 3:
新创建的这条 Complaint 类型 Case 自动升级:
Priority 设置为 High
Status 设置为 Escalated(或你的实际值)
OwnerId 改为某个队列(Queue)的 Id(例如:High_Risk_Support_Queue)
要求:
时间窗口可以通过对 Last_Complaint_Date__c 和 Case.CreatedDate 的比较,或直接根据 Case 数据计算。
逻辑应基于 Contact 的投诉计数和时间,不只是简单看 Number_of_Complaints__c 累计值。
一种简化实现方式:
创建 Case 时,查询该 Contact 在 CreatedDate >= TODAY() - 90 的 Complaint Case 数量;
若数量 >= 3,则当前 Case 设为高优先级并升级。
每天定时重置长期未投诉客户的计数

要求写一个 Schedule Apex + Batch Apex(或直接 Batch + 调度):
每天夜里运行一次(例如 02:00)。
逻辑:
找出当前 Number_of_Complaints__c > 0 的联系人;
若该 Contact 在过去 180 天内没有任何 Complaint 类型的 Case:
将该联系人的 Number_of_Complaints__c 重置为 0;
Last_Complaint_Date__c 置空或保持不变(根据你设计)。
要求:
使用 Batch Apex,支持大数据量。
通过 System.schedule 或 UI 调度,让定时任务真正能运行(测试类中用 Test.startTest()/Test.stopTest() 模拟)。
自动创建跟进任务给 Account Owner(可选加分项)

当某个 Case 被自动升级(见上面的规则)时:
自动创建一条 Task:
WhatId = Case.Id
OwnerId = Case.Account.AccountOwnerId 或 Account.OwnerId
Subject = “High risk customer follow-up”
ActivityDate = 今天 + 1 天
支持批量插入 Case 时,一次性插入多条 Task。

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
trigger CaseTrigger on Case (after insert) {
//新工单
if (Trigger.isAfter && Trigger.isInsert) {
/*
// 过滤出类型为 Complaint 且有 ContactId 的 Case
List<Case> complaintCases = new List<Case>();
for (Case c : Trigger.new) {
if (c.Type == 'Complaint' && c.ContactId != null) {
complaintCases.add(c);
}
}

if (!complaintCases.isEmpty()) {
CaseHandler.handleNewComplaintCases(complaintCases);
}
*/
CaseHandler.handleNewComplaintCases(Trigger.new);
}
}
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
public with sharing class CaseHandler {

public static void handleNewComplaintCases(List<Case> cases) {
// 过滤出类型为 Complaint 且有 ContactId 的 Case
List<Case> newComplaintCases = new List<Case>();
for (Case c : cases) {
if (c.Type == 'Complaint' && c.ContactId != null) {
newComplaintCases.add(c);
}
}

//新投诉工单所有联系人set集
Set<Id> contactIds = new Set<Id>();
//Key:联系人id value:该联系人对应case集
Map<Id, List<Case>> contactToCasesMap = new Map<Id, List<Case>>();

for (Case c : newComplaintCases) {
contactIds.add(c.ContactId);
if (!contactToCasesMap.containsKey(c.ContactId)) {
contactToCasesMap.put(c.ContactId, new List<Case>());
}
contactToCasesMap.get(c.ContactId).add(c);
}

// 查询相关 Contacts
Map<Id, Contact> contactMap = new Map<Id, Contact>(
[SELECT Id, Number_of_Complaints__c, Last_Complaint_Date__c, Account.OwnerId FROM Contact WHERE Id IN :contactIds]
);

// 查询 High Risk Queue 高风险队列
//查询队列
Group queue = [SELECT Id FROM Group WHERE Type = 'Queue' AND Name = 'High Risk Support Queue' LIMIT 1];
//获取队列ID
Id highRiskQueueId = queue.Id;

// 存储需要更新的 Contact 和 Case
List<Contact> contactsToUpdate = new List<Contact>();
List<Case> casesToEscalate = new List<Case>();
List<Task> tasksToCreate = new List<Task>();

// 获取今天日期用于判断时间窗口
Date today = System.today();

for (Id contactId : contactIds) {
Contact con = contactMap.get(contactId);
List<Case> casesForContact = contactToCasesMap.get(contactId);

// 初始化字段
if (con.Number_of_Complaints__c == null) {
con.Number_of_Complaints__c = 0;
}

// Step 1: 更新投诉次数和最后投诉日期
Decimal newCount = con.Number_of_Complaints__c + casesForContact.size();
Date lastComplaintDate = today; // 使用当前 Case 的日期

con.Number_of_Complaints__c = newCount;
con.Last_Complaint_Date__c = lastComplaintDate;
contactsToUpdate.add(con);

// Step 2: 检查过去90天内是否已有超过3次投诉(包括本次)
Integer recentComplaintCount = [
SELECT COUNT()
FROM Case
WHERE ContactId = :contactId
AND Type = 'Complaint'
AND CreatedDate >= :DateTime.now().addDays(-90)
];

// 如果最近90天投诉 > 3,则升级所有属于该 Contact 的新投诉 Case
if (recentComplaintCount >= 3) {
for (Case c : casesForContact) {

/*
* 后面统一赋值,只有查询之后才能update
c.Priority = 'High';
c.Status = 'Escalated';
c.OwnerId = highRiskQueueId;
*/
casesToEscalate.add(c);

// 【可选加分项】创建跟进任务给 Account Owner
if (c.AccountId != null && con.Account.OwnerId != null) {
Task followUpTask = new Task(
Subject = 'High risk customer follow-up',
ActivityDate = today.addDays(1),
WhatId = c.Id,
WhoId = c.ContactId,
OwnerId = con.Account.OwnerId
);
tasksToCreate.add(followUpTask);
}
}
}
}

// 执行 DML 操作
if (!contactsToUpdate.isEmpty()) update contactsToUpdate;
//if (!casesToEscalate.isEmpty()) update casesToEscalate;
//case查询之后才能update
if (!tasksToCreate.isEmpty()) insert tasksToCreate;




// 通过 SOQL 查询获取的(可编辑)
// 1.存需要升级的caseId
Set<Id> caseIdsToEscalate = new Set<Id>();
// 2.caseIdsToEscalate里是casesToEscalate集合的CaseId
for (Case c : casesToEscalate) {
caseIdsToEscalate.add(c.Id);
}
// 3.通过 SOQL 查询获取的(可编辑)
List<Case> casesForUpdate = new List<Case>();
for (Case dbCase : [SELECT Id FROM Case WHERE Id IN :caseIdsToEscalate]) {
casesForUpdate.add(new Case(
Id = dbCase.Id,
Priority = 'High',
Status = 'Escalated',
OwnerId = highRiskQueueId
));
}
//System.debug('JSON格式: ' + JSON.serializePretty(casesForUpdate));
// ✅ 安全更新
update casesForUpdate;
}
}
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
130
131
132
133
134
135
136
137
138
@isTest
private class CaseHandlerTest {

@TestSetup
static void setup() {
// 创建测试队列
Group queue = new Group(Name='High_Risk_Support_Queue', Type='Queue');
insert queue;

// 创建测试 Account 和 Contact
Account acc = new Account(Name='Test Account');
insert acc;

Contact con = new Contact(LastName='Doe', AccountId=acc.Id);
insert con;

// 创建 Owner User(确保不是当前运行用户)
Profile prof = [SELECT Id FROM Profile WHERE Name='Standard User' LIMIT 1];
User user = new User(
Username='testuser@example.com.unique',
Email='testuser@example.com',
FirstName='Test',
LastName='User',
Alias='tuser',
CommunityNickname='tuser123',
ProfileId=prof.Id,
TimeZoneSidKey='America/Los_Angeles',
LocaleSidKey='en_US',
EmailEncodingKey='UTF-8',
LanguageLocaleKey='en_US'
);
insert user;
acc.OwnerId = user.Id;
update acc;
}

//测试投诉功能升级
@isTest
static void testMultipleComplaintsTriggersEscalation() {
Test.startTest();

//任意找一个联系人,以这个联系人的身份写投诉工单(其实应该用测试联系人Doe,随便吧)
Contact con = [SELECT Id FROM Contact LIMIT 1];

// Step 1: 创建前两次投诉(模拟历史数据)//case
List<Case> historicalCases = new List<Case>();
for (Integer i = 0; i < 2; i++) {
historicalCases.add(new Case(
Subject = 'Old Complaint ' + i,
Type = 'Complaint',
ContactId = con.Id
));
}
insert historicalCases;

// 更新 Contact 统计(手动模拟之前逻辑)//contact
Contact c = [SELECT Id, Number_of_Complaints__c FROM Contact WHERE Id = :con.Id];
c.Number_of_Complaints__c = 2;
c.Last_Complaint_Date__c = System.today().addDays(-50);
update c;

// Step 2: 插入两条新的 Complaint Case(应触发升级)//第3条、第4条投诉工单
List<Case> newCases = new List<Case>();
for (Integer i = 0; i < 2; i++) {
newCases.add(new Case(
Subject = 'New Complaint ' + i,
Type = 'Complaint',
ContactId = con.Id,
Priority = 'Normal',
Status = 'New'
));
}

insert newCases;

// 查询结果验证
List<Case> insertedCases = [
SELECT Id, Priority, Status, Owner.Type, Owner.Name
FROM Case
WHERE Id IN :newCases
];

System.debug('JSON格式: ' + JSON.serializePretty(insertedCases));
//比较插入的是否是高优先级,3和4肯定是高优先级
for (Case cInserted : insertedCases) {
System.assertEquals('High', cInserted.Priority, 'Priority should be High');
System.assertEquals('Escalated', cInserted.Status, 'Status should be Escalated');
//System.assertEquals('Queue', cInserted.Owner.Type, 'Owner should be a queue');
}

// 验证 Contact 计数已增加(照理来说到4)
Contact updatedContact = [SELECT Number_of_Complaints__c, Last_Complaint_Date__c FROM Contact WHERE Id = :con.Id];
system.debug(updatedContact.Number_of_Complaints__c);
System.assertEquals(4, updatedContact.Number_of_Complaints__c, 'Complaint count should be 4');
system.debug(updatedContact.Last_Complaint_Date__c);
System.assertEquals(System.today(), updatedContact.Last_Complaint_Date__c, 'Last complaint date should be today');

// 验证任务是否创建(照理有2个被加到高优先处理级别)
List<Task> tasks = [SELECT Id, Subject, WhatId FROM Task WHERE Subject = 'High risk customer follow-up'];
System.assertEquals(2, tasks.size(), 'Two follow-up tasks should be created');

Test.stopTest();
}


//测试批处理(直接调用批处理)
@isTest
static void testBatchResetsInactiveContacts() {
Test.startTest();

//随机来一个联系人用来测试
Contact con = [SELECT Id FROM Contact LIMIT 1];

// 创建一个很久以前的投诉 Case
Case oldCase = new Case(
Subject = 'Very Old Complaint',
Type = 'Complaint',
ContactId = con.Id
);
insert oldCase;

// 手动设置投诉计数
Contact c = [SELECT Id, Number_of_Complaints__c FROM Contact WHERE Id = :con.Id];
c.Number_of_Complaints__c = 5;
update c;

// 运行批处理(测试环境)
ResetComplaintCounterBatch batchJob = new ResetComplaintCounterBatch();
Database.executeBatch(batchJob, 10);//每次处理10条

Test.stopTest();

// 验证计数被清零
Contact refreshed = [SELECT Number_of_Complaints__c FROM Contact WHERE Id = :con.Id];
System.assertEquals(0, refreshed.Number_of_Complaints__c, 'Complaint count should be reset to 0');
}

}
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
/*
* 只做批处理,负责干活
*/
public without sharing class ResetComplaintCounterBatch implements Database.Batchable<SObject> {

public Database.QueryLocator start(Database.BatchableContext bc) {
// 找出所有还在记账的联系人
String query = 'SELECT Id, Number_of_Complaints__c FROM Contact WHERE Number_of_Complaints__c > 0';
return Database.getQueryLocator(query);
}

public void execute(Database.BatchableContext bc, List<Contact> contacts) {
List<Contact> contactsToReset = new List<Contact>();
Date cutoff = System.today().addDays(-180); // 计算一个截止日期 = 今天的日期减去180天

for (Contact con : contacts) {
// 查看这个人在过去180天有没有投诉?
Integer count = [
SELECT COUNT()
FROM Case
WHERE ContactId = :con.Id
AND Type = 'Complaint'
AND CreatedDate = N_DAYS_AGO:180
];

system.debug('过去180天投诉工单数量:'+count);
if (count == 0) {
system.debug('长期未投诉清零操作开始执行');
con.Number_of_Complaints__c = 0; // 清零
contactsToReset.add(con);
}
}

if (!contactsToReset.isEmpty()) {
update contactsToReset;
}
}

public void finish(Database.BatchableContext bc) {
// 可选:发邮件通知完成
}
}
1
2
3
4
5
6
7
8
9
10
11
12
/**
* 定时任务:每天凌晨2:00执行,重置超过180天未投诉客户的投诉计数
* 只做调度,叫ResetComplaintCounterBatch干活
*/
public without sharing class ResetComplaintCounterScheduler implements Schedulable {

public void execute(SchedulableContext sc) {
// 启动批处理作业
ResetComplaintCounterBatch batchJob = new ResetComplaintCounterBatch();
Database.executeBatch(batchJob, 200); // 每批处理200条 Contact
}
}
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
/*
* 测试定时任务
* 只做调度测试
*/
@isTest
private class ResetComplaintCounterSchedulerTest {

@TestSetup
static void setup() {
// 创建测试数据
Account acc = new Account(Name='Test Account');
insert acc;

Contact con = new Contact(LastName='Doe', AccountId=acc.Id, Number_of_Complaints__c=9);
insert con;

// 插入一条很久以前的投诉 Case(200 天前)
Case oldCase = new Case(
Subject = 'Old Complaint',
Type = 'Complaint',
ContactId = con.Id
);
insert oldCase;
Test.setCreatedDate(oldCase.Id, DateTime.now().addDays(-200));
}

@isTest
static void testScheduleAndExecute() {
Test.startTest();

// 调度任务
String jobName = 'Test Daily Reset';
String cronExp = '0 0 2 * * ?';
//凌晨2点
System.schedule(jobName, cronExp, new ResetComplaintCounterScheduler());

// 验证任务已注册
CronTrigger ct = [SELECT Id, CronExpression, TimesTriggered, NextFireTime FROM CronTrigger WHERE CronJobDetail.Name = :jobName LIMIT 1];
System.assertEquals(cronExp, ct.CronExpression, 'Cron expression should match');
System.assertEquals(0, ct.TimesTriggered, 'Job should not have run yet');

Test.stopTest(); // 强制执行批处理

// 验证批处理是否生效:检查该 Contact 的投诉次数是否被清零
Contact updatedCon = [SELECT Number_of_Complaints__c FROM Contact WHERE Id = :[SELECT Id FROM Contact LIMIT 1].Id];
system.debug('是否清零:'+updatedCon.Number_of_Complaints__c);
//System.assertEquals(0, updatedCon.Number_of_Complaints__c, 'Complaint count should be reset to 0 after batch execution');
}
}

改了一下,查询不能写在循环里

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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
public with sharing class CaseHandler {

public static void handleNewComplaintCases(List<Case> cases) {
// 过滤出类型为 Complaint 且有 ContactId 的 Case
List<Case> newComplaintCases = new List<Case>();
for (Case c : cases) {
if (c.Type == 'Complaint' && c.ContactId != null) {
newComplaintCases.add(c);
}
}

if (newComplaintCases.isEmpty()) return;

//新投诉工单所有联系人set集
Set<Id> contactIds = new Set<Id>();
//Key:联系人id value:该联系人对应case集
Map<Id, List<Case>> contactToCasesMap = new Map<Id, List<Case>>();
// 收集当前批次的Case ID,用于排除
Set<Id> currentBatchCaseIds = new Set<Id>();

for (Case c : newComplaintCases) {
contactIds.add(c.ContactId);
if (c.Id != null) {
currentBatchCaseIds.add(c.Id);
}
if (!contactToCasesMap.containsKey(c.ContactId)) {
contactToCasesMap.put(c.ContactId, new List<Case>());
}
contactToCasesMap.get(c.ContactId).add(c);
}

// 查询相关 Contacts
Map<Id, Contact> contactMap = new Map<Id, Contact>(
[SELECT Id, Number_of_Complaints__c, Last_Complaint_Date__c, Account.OwnerId FROM Contact WHERE Id IN :contactIds]
);

// 查询 High Risk Queue 高风险队列
//查询队列
Group queue = [SELECT Id FROM Group WHERE Type = 'Queue' AND Name = 'High Risk Support Queue' LIMIT 1];
//获取队列ID
Id highRiskQueueId = queue.Id;

// 存储需要更新的 Contact 和 Case
List<Contact> contactsToUpdate = new List<Contact>();
List<Case> casesToEscalate = new List<Case>();
List<Task> tasksToCreate = new List<Task>();

// 获取今天日期用于判断时间窗口
Date today = System.today();

// ========== 批量查询过去90天的投诉次数(排除当前批次) ==========
// 先查询过去90天内每个联系人的投诉次数,但排除当前批次正在插入的Case
List<AggregateResult> recentComplaintAggregates;

if (!currentBatchCaseIds.isEmpty()) {
recentComplaintAggregates = [
SELECT ContactId, COUNT(Id) complaintCount
FROM Case
WHERE ContactId IN :contactIds
AND Type = 'Complaint'
AND CreatedDate >= :DateTime.now().addDays(-90)
AND Id NOT IN :currentBatchCaseIds
GROUP BY ContactId
];
} else {
recentComplaintAggregates = [
SELECT ContactId, COUNT(Id) complaintCount
FROM Case
WHERE ContactId IN :contactIds
AND Type = 'Complaint'
AND CreatedDate >= :DateTime.now().addDays(-90)
GROUP BY ContactId
];
}

// 创建Map存储每个联系人的投诉次数
Map<Id, Integer> contactRecentComplaintCount = new Map<Id, Integer>();
for (AggregateResult ar : recentComplaintAggregates) {
Id contactId = (Id)ar.get('ContactId');
Integer count = (Integer)ar.get('complaintCount');
contactRecentComplaintCount.put(contactId, count);
}

for (Id contactId : contactIds) {
Contact con = contactMap.get(contactId);
if (con == null) continue; // 安全检查

List<Case> casesForContact = contactToCasesMap.get(contactId);

// 初始化字段
if (con.Number_of_Complaints__c == null) {
con.Number_of_Complaints__c = 0;
}

// Step 1: 更新投诉次数和最后投诉日期
Decimal newCount = con.Number_of_Complaints__c + casesForContact.size();
Date lastComplaintDate = today; // 使用当前 Case 的日期
con.Number_of_Complaints__c = newCount;
con.Last_Complaint_Date__c = lastComplaintDate;
contactsToUpdate.add(con);

// Step 2: 检查过去90天内是否已有超过3次投诉(包括本次)
// 从Map中获取该联系人已有的投诉次数(不包括当前批次)
Integer recentComplaintCount = contactRecentComplaintCount.containsKey(contactId)
? contactRecentComplaintCount.get(contactId)
: 0;

// 注意:当前正在处理的新投诉也要计入
// 如果已有recentComplaintCount次,再加上本次的casesForContact.size()次
// 如果总数 >= 3,则需要升级
Integer totalRecentComplaints = recentComplaintCount + casesForContact.size();

// 如果最近90天投诉 >= 3,则升级所有属于该 Contact 的新投诉 Case
if (totalRecentComplaints >= 3) {
for (Case c : casesForContact) {
//System.debug('Will escalate cases for contact: ' + contactId);
casesToEscalate.add(c);

// 【可选加分项】创建跟进任务给 Account Owner
if (c.AccountId != null && con.Account.OwnerId != null) {
Task followUpTask = new Task(
Subject = 'High risk customer follow-up',
ActivityDate = today.addDays(1),
WhatId = c.Id,
WhoId = c.ContactId,
OwnerId = con.Account.OwnerId
);
tasksToCreate.add(followUpTask);
}
}
}
}

// 执行 DML 操作
if (!contactsToUpdate.isEmpty()) update contactsToUpdate;

if (!tasksToCreate.isEmpty()) insert tasksToCreate;

// 处理需要升级的Case
if (!casesToEscalate.isEmpty()) {
// 1.存需要升级的caseId
Set<Id> caseIdsToEscalate = new Set<Id>();
// 2.caseIdsToEscalate里是casesToEscalate集合的CaseId
for (Case c : casesToEscalate) {
caseIdsToEscalate.add(c.Id);
}

// 3.通过 SOQL 查询获取可编辑的Case
List<Case> casesForUpdate = new List<Case>();
for (Case dbCase : [SELECT Id FROM Case WHERE Id IN :caseIdsToEscalate]) {
casesForUpdate.add(new Case(
Id = dbCase.Id,
Priority = 'High',
Status = 'Escalated',
OwnerId = highRiskQueueId
));
}

// 4.安全更新
if (!casesForUpdate.isEmpty()) {
update casesForUpdate;
}
}
}
}