attiya/ebike-plus
SHA256
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity

返回参数

Browse Source
This commit is contained in:
PC 2026-01-26 14:44:20 +08:00
parent ed9463b28b
commit 303e903d69
4 changed files with 162 additions and 71 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

7
ebike-operations/src/main/java/com/cdzy/operations/model/dto/EbikeBikeOrderDayCountDto.java
View File

@ -7,6 +7,7 @@ import lombok.NoArgsConstructor;
import java.io.Serial;
import java.io.Serializable;
import java.math.BigDecimal;
/**
* 工单信息 实体类。
@ -42,4 +43,10 @@ public class EbikeBikeOrderDayCountDto implements Serializable {
* 昨天完成量
*/
private Integer yesterdayCount;
/**
* 变化量
*/
private BigDecimal changePercentage;
}

6
ebike-operations/src/main/java/com/cdzy/operations/model/dto/EbikeBikeOrderMonthCountDto.java
View File

@ -7,6 +7,7 @@ import lombok.NoArgsConstructor;
import java.io.Serial;
import java.io.Serializable;
import java.math.BigDecimal;
/**
* 工单信息 实体类。
@ -42,4 +43,9 @@ public class EbikeBikeOrderMonthCountDto implements Serializable {
* 上月完成量
*/
private Integer lastMonthCount;
/**
* 变化量
*/
private BigDecimal changePercentage;
}

7
ebike-operations/src/main/java/com/cdzy/operations/model/dto/EbikeBikeOrderWeekCountDto.java
View File

@ -7,6 +7,7 @@ import lombok.NoArgsConstructor;
import java.io.Serial;
import java.io.Serializable;
import java.math.BigDecimal;
/**
* 工单信息 实体类。
@ -42,4 +43,10 @@ public class EbikeBikeOrderWeekCountDto implements Serializable {
* 上周完成量
*/
private Integer lastWeekCount;
/**
* 变化量
*/
private BigDecimal changePercentage;
}

213
ebike-operations/src/main/resources/mapper/EbikeBikeOrderMapper.xml
View File

@ -6,37 +6,62 @@
<select id="monthCount" resultType="com.cdzy.operations.model.dto.EbikeBikeOrderMonthCountDto">
<![CDATA[
SELECT #{receiverId} as receiverId,
CASE aot.order_type
WHEN 1 THEN '巡检工单'
WHEN 2 THEN '换电工单'
WHEN 3 THEN '调度工单'
WHEN 4 THEN '维修工单'
END as orderType,
COALESCE(COUNT(CASE
WHEN DATE_TRUNC('month', ebo.handle_at) = DATE_TRUNC('month', CURRENT_DATE)
THEN 1
END), 0) as thisMonthCount,
COALESCE(COUNT(CASE
WHEN DATE_TRUNC('month', ebo.handle_at) =
DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
THEN 1
END), 0) as lastMonthCount
FROM generate_series(1, 4) as aot(order_type)
LEFT JOIN ebike_bike_order ebo ON aot.order_type = ebo.order_type
AND ebo.handle_state = 2
AND ebo.handle_at IS NOT NULL
AND ebo.is_deleted = false
AND ebo.receiver_id = #{receiverId}
AND ebo.handle_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
AND ebo.handle_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
GROUP BY aot.order_type
ORDER BY aot.order_type;
]]>
SELECT #{receiverId} as receiverId,
CASE aot.order_type
WHEN 1 THEN '巡检工单'
WHEN 2 THEN '换电工单'
WHEN 3 THEN '调度工单'
WHEN 4 THEN '维修工单'
END as orderType,
COALESCE(COUNT(CASE
WHEN DATE_TRUNC('month', ebo.handle_at) = DATE_TRUNC('month', CURRENT_DATE)
THEN 1
END), 0) as thisMonthCount,
COALESCE(COUNT(CASE
WHEN DATE_TRUNC('month', ebo.handle_at) =
DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
THEN 1
END), 0) as lastMonthCount,
CASE
WHEN COALESCE(COUNT(CASE
WHEN DATE_TRUNC('month', ebo.handle_at) =
DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
THEN 1
END), 0) = 0 THEN 0
ELSE ROUND(
(COALESCE(COUNT(CASE
WHEN DATE_TRUNC('month', ebo.handle_at) =
DATE_TRUNC('month', CURRENT_DATE)
THEN 1
END), 0) -
COALESCE(COUNT(CASE
WHEN DATE_TRUNC('month', ebo.handle_at) =
DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
THEN 1
END), 0)) * 100.0 /
COALESCE(COUNT(CASE
WHEN DATE_TRUNC('month', ebo.handle_at) =
DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
THEN 1
END), 0),
2
)
END as changePercentage
FROM generate_series(1, 4) as aot(order_type)
LEFT JOIN ebike_bike_order ebo ON aot.order_type = ebo.order_type
AND ebo.handle_state = 2
AND ebo.handle_at IS NOT NULL
AND ebo.is_deleted = false
AND ebo.receiver_id = #{receiverId}
AND ebo.handle_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
AND ebo.handle_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
GROUP BY aot.order_type
ORDER BY aot.order_type;
]]>
</select>
<select id="dayCount" resultType="com.cdzy.operations.model.dto.EbikeBikeOrderDayCountDto">
<![CDATA[
SELECT #{receiverId} as receiverId,
<![CDATA[
SELECT #{receiverId} as receiverId,
CASE aot.order_type
WHEN 1 THEN '巡检工单'
WHEN 2 THEN '换电工单'
@ -50,7 +75,31 @@
COALESCE(COUNT(CASE
WHEN DATE (ebo.handle_at) = CURRENT_DATE - INTERVAL '1 day'
THEN 1
END), 0) as yesterdayCount
END), 0) as yesterdayCount,
COALESCE(
CASE
WHEN COALESCE(COUNT(CASE
WHEN DATE (ebo.handle_at) = CURRENT_DATE - INTERVAL '1 day'
THEN 1
END), 0) = 0
THEN 0.0
ELSE ROUND(
(COALESCE(COUNT(CASE
WHEN DATE (ebo.handle_at) = CURRENT_DATE
THEN 1
END), 0) -
COALESCE(COUNT(CASE
WHEN DATE (ebo.handle_at) = CURRENT_DATE - INTERVAL '1 day'
THEN 1
END), 0)) * 100.0 /
COALESCE(COUNT(CASE
WHEN DATE (ebo.handle_at) = CURRENT_DATE - INTERVAL '1 day'
THEN 1
END), 0),
2
)
END, 0.0
) as changePercentage
FROM generate_series(1, 4) as aot(order_type)
LEFT JOIN ebike_bike_order ebo ON aot.order_type = ebo.order_type
AND ebo.handle_state = 2
@ -64,46 +113,68 @@
GROUP BY aot.order_type
ORDER BY aot.order_type;
]]>
</select>
</select>
<select id="weekCount" resultType="com.cdzy.operations.model.dto.EbikeBikeOrderWeekCountDto">
<![CDATA[
WITH week_range AS (
SELECT
DATE_TRUNC('week', CURRENT_DATE) as current_week_start,
DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week' as current_week_end,
DATE_TRUNC('week', CURRENT_DATE - INTERVAL '1 week') as last_week_start
)
SELECT #{receiverId} as receiverId,
CASE aot.order_type
WHEN 1 THEN '巡检工单'
WHEN 2 THEN '换电工单'
WHEN 3 THEN '调度工单'
WHEN 4 THEN '维修工单'
END as orderType,
COALESCE(COUNT(CASE
WHEN ebo.handle_at >= wr.current_week_start
AND ebo.handle_at < wr.current_week_end
THEN 1
END), 0) as thisWeekCount,
COALESCE(COUNT(CASE
WHEN ebo.handle_at >= wr.last_week_start
AND ebo.handle_at < wr.current_week_start
THEN 1
END), 0) as lastWeekCount
FROM generate_series(1, 4) as aot(order_type)
CROSS JOIN week_range wr
LEFT JOIN ebike_bike_order ebo ON aot.order_type = ebo.order_type
AND ebo.handle_state = 2
AND ebo.handle_at IS NOT NULL
AND ebo.is_deleted = false
AND ebo.receiver_id = #{receiverId}
AND ebo.handle_at >= wr.last_week_start
AND ebo.handle_at < wr.current_week_end
GROUP BY aot.order_type
ORDER BY aot.order_type;
]]>
</select>
<![CDATA[
WITH week_range AS (SELECT DATE_TRUNC('week', CURRENT_DATE) as current_week_start,
DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week' as current_week_end, DATE_TRUNC('week', CURRENT_DATE - INTERVAL '1 week') as last_week_start
)
SELECT #{receiverId} as receiverId,
CASE aot.order_type
WHEN 1 THEN '巡检工单'
WHEN 2 THEN '换电工单'
WHEN 3 THEN '调度工单'
WHEN 4 THEN '维修工单'
END as orderType,
COALESCE(COUNT(CASE
WHEN ebo.handle_at >= wr.current_week_start
AND ebo.handle_at < wr.current_week_end
THEN 1
END), 0) as thisWeekCount,
COALESCE(COUNT(CASE
WHEN ebo.handle_at >= wr.last_week_start
AND ebo.handle_at < wr.current_week_start
THEN 1
END), 0) as lastWeekCount,
COALESCE(
CASE
WHEN COALESCE(COUNT(CASE
WHEN ebo.handle_at >= wr.last_week_start
AND ebo.handle_at < wr.current_week_start
THEN 1
END), 0) = 0
THEN 0.0
ELSE ROUND(
(COALESCE(COUNT(CASE
WHEN ebo.handle_at >= wr.current_week_start
AND ebo.handle_at < wr.current_week_end
THEN 1
END), 0) -
COALESCE(COUNT(CASE
WHEN ebo.handle_at >= wr.last_week_start
AND ebo.handle_at < wr.current_week_start
THEN 1
END), 0)) * 100.0 /
COALESCE(COUNT(CASE
WHEN ebo.handle_at >= wr.last_week_start
AND ebo.handle_at < wr.current_week_start
THEN 1
END), 0),
2
)
END, 0.0
) as changePercentage
FROM generate_series(1, 4) as aot(order_type)
CROSS JOIN week_range wr
LEFT JOIN ebike_bike_order ebo ON aot.order_type = ebo.order_type
AND ebo.handle_state = 2
AND ebo.handle_at IS NOT NULL
AND ebo.is_deleted = false
AND ebo.receiver_id = #{receiverId}
AND ebo.handle_at >= wr.last_week_start
AND ebo.handle_at < wr.current_week_end
GROUP BY aot.order_type
ORDER BY aot.order_type;
]]>
</select>
</mapper>