From 3c94dc06c3b99e7fc430e68ee2ce4a6cf2cd422c Mon Sep 17 00:00:00 2001
From: wayne <wayne8692wayne8692@gmail.com>
Date: 星期三, 16 二月 2022 15:54:32 +0800
Subject: [PATCH] [update] 修正滿意度評分在沒有撈到任何評分紀錄時，不可直接set 0進顧問的分數中
---
pamapi/src/main/java/com/pollex/pam/repository/SatisfactionRepository.java | 2 +-
pamapi/src/main/java/com/pollex/pam/service/ConsultantService.java | 22 ++++++++++++----------
pamapi/src/doc/sql/20220216_w.sql | 22 ++++++++++++++++++++++
3 files changed, 35 insertions(+), 11 deletions(-)
diff --git a/pamapi/src/doc/sql/20220216_w.sql b/pamapi/src/doc/sql/20220216_w.sql
new file mode 100644
index 0000000..4c9ee45
--- /dev/null
+++ b/pamapi/src/doc/sql/20220216_w.sql
@@ -0,0 +1,22 @@
+update public.consultant set avg_score = 1 where agent_no = 'A183619275';
+update public.consultant set avg_score = 2 where agent_no = 'AGAM11249699';
+update public.consultant set avg_score = 3 where agent_no = 'Z152717443';
+update public.consultant set avg_score = 4 where agent_no = 'D265260662';
+update public.consultant set avg_score = 5 where agent_no = 'AG0109051204';
+update public.consultant set avg_score = 1 where agent_no = 'B282677963';
+update public.consultant set avg_score = 2 where agent_no = 'AG0101234567';
+update public.consultant set avg_score = 3 where agent_no = 'X147309614';
+update public.consultant set avg_score = 4 where agent_no = 'J149388015';
+update public.consultant set avg_score = 5 where agent_no = 'R221444250';
+
+do
+$$
+declare
+avg_row record;
+begin
+for avg_row in select round(cast(avg(score) as numeric), 1) as avg_score, agent_no from public.satisfaction where "type" = 'APPOINTMENT' and score is not null group by agent_no
+ loop
+update public.consultant set avg_score = avg_row.avg_score where agent_no = avg_row.agent_no;
+end loop;
+end
+$$
diff --git a/pamapi/src/main/java/com/pollex/pam/repository/SatisfactionRepository.java b/pamapi/src/main/java/com/pollex/pam/repository/SatisfactionRepository.java
index 1e670a3..fa37237 100644
--- a/pamapi/src/main/java/com/pollex/pam/repository/SatisfactionRepository.java
+++ b/pamapi/src/main/java/com/pollex/pam/repository/SatisfactionRepository.java
@@ -27,5 +27,5 @@
+ " and agent_no=:agent_no"
+ " and score is not null"
, nativeQuery = true)
- Float getAgentScoreAvg(@Param("agent_no") String agentNo);
+ Optional<Float> getAgentScoreAvg(@Param("agent_no") String agentNo);
}
diff --git a/pamapi/src/main/java/com/pollex/pam/service/ConsultantService.java b/pamapi/src/main/java/com/pollex/pam/service/ConsultantService.java
index 6044a45..58c96d9 100644
--- a/pamapi/src/main/java/com/pollex/pam/service/ConsultantService.java
+++ b/pamapi/src/main/java/com/pollex/pam/service/ConsultantService.java
@@ -36,6 +36,7 @@
import java.util.Comparator;
import java.util.List;
import java.util.Objects;
+import java.util.Optional;
import java.util.stream.Collectors;
import static com.pollex.pam.consts.SeniorityQueryConst.*;
@@ -358,17 +359,18 @@
}
public void setConsultantAvgScore(Satisfaction satisfaction) {
- float avgScore = getAgentAvgScore(satisfaction.getAgentNo());
- Consultant consultant = consultantRepository.findOneByAgentNo(satisfaction.getAgentNo())
- .get();
- consultant.setAvgScore(avgScore);
- consultantRepository.save(consultant);
+ Optional<Float> avgScore = getAgentAvgScore(satisfaction.getAgentNo());
+
+ if(avgScore.isPresent()) {
+ BigDecimal bigDecimal = BigDecimal.valueOf(avgScore.get());
+
+ Consultant consultant = consultantRepository.findOneByAgentNo(satisfaction.getAgentNo()).get();
+ consultant.setAvgScore(bigDecimal.setScale(1, BigDecimal.ROUND_HALF_UP).floatValue());
+ consultantRepository.save(consultant);
+ }
}
- public float getAgentAvgScore(String agentNo) {
- Float avgScore = satisfactionRepository.getAgentScoreAvg(agentNo);
- if(avgScore==null)return 0;
- BigDecimal bigDecimal = new BigDecimal(avgScore);
- return avgScore = bigDecimal.setScale(1,BigDecimal.ROUND_HALF_UP).floatValue();
+ public Optional<Float> getAgentAvgScore(String agentNo) {
+ return satisfactionRepository.getAgentScoreAvg(agentNo);
}
}
--
Gitblit v1.8.0