From 96ac1438830beff7ef598b934d9964a96dc57c7b Mon Sep 17 00:00:00 2001
From: 邱宇豪 <qyh123230312>
Date: 星期二, 24 十月 2023 14:44:42 +0800
Subject: [PATCH] 20231024_qiuyh_管片、钢筋笼报表sql
---
hd/pipe/secure/src/main/resources/mapping/PipeReportMapper.xml | 103 +++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 103 insertions(+), 0 deletions(-)
diff --git a/hd/pipe/secure/src/main/resources/mapping/PipeReportMapper.xml b/hd/pipe/secure/src/main/resources/mapping/PipeReportMapper.xml
new file mode 100644
index 0000000..3ab61e8
--- /dev/null
+++ b/hd/pipe/secure/src/main/resources/mapping/PipeReportMapper.xml
@@ -0,0 +1,103 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
+<mapper namespace="com.thhy.secure.modules.biz.pipereport.mapper.PipeReportMapper">
+
+
+ <select id="getPipeReportList" resultType="com.thhy.secure.modules.biz.pipereport.entity.PipeReportEntity">
+
+ SELECT
+ c.pro_id proId,
+ c.pro_name proName,
+ IFNULL(c.produceTotal,0) produceTotal,
+ IFNULL(d.qualifiedTotal,0) qualifiedTotal,
+ IFNULL(e.shippingNum,0) shippingNum
+ FROM (
+ SELECT
+ COUNT(a.pro_id) produceTotal,
+ a.pro_id,
+ b.pro_name
+ FROM t_pipe_info a
+ LEFT JOIN sys_project b on b.pro_id = a.pro_id
+ WHERE b.company_id = #{companyId}
+ GROUP BY a.pro_id
+ ) c
+ LEFT JOIN
+ (
+ SELECT
+ COUNT(a.pro_id) qualifiedTotal,
+ a.pro_id,
+ b.pro_name
+ FROM t_pipe_info a
+ LEFT JOIN sys_project b on b.pro_id = a.pro_id
+ WHERE a.check_result = 1 and b.company_id = #{companyId}
+ GROUP BY a.pro_id
+ ) d ON d.pro_id = c.pro_id
+ LEFT JOIN
+ (
+ SELECT
+ COUNT(a.pro_id) shippingNum,
+ a.pro_id,
+ b.pro_name
+ FROM t_pipe_out a
+ LEFT JOIN sys_project b on b.pro_id = a.pro_id
+ WHERE b.company_id = #{companyId}
+ GROUP BY a.pro_id
+ ) e on e.pro_id = c.pro_id
+
+ </select>
+
+ <select id="getSteelReportList" resultType="com.thhy.secure.modules.biz.pipereport.entity.PipeReportEntity">
+
+ SELECT
+ c.pro_id proId,
+ c.pro_name proName,
+ IFNULL(c.produceTotal,0) produceTotal,
+ IFNULL(d.qualifiedTotal,0) qualifiedTotal,
+ IFNULL(e.moldedNotNum,0) moldedNotNum,
+ IFNULL(f.moldedNum,0) moldedNum
+ FROM(
+ SELECT
+ COUNT(a.pro_id) produceTotal,
+ a.pro_id,
+ b.pro_name
+ FROM t_steel_produce a
+ LEFT JOIN sys_project b on b.pro_id = a.pro_id
+ WHERE a.is_use = 1 and b.company_id = #{companyId}
+ GROUP BY a.pro_id
+ ) c
+ LEFT JOIN
+ (
+ SELECT
+ COUNT(a.pro_id) qualifiedTotal,
+ a.pro_id,
+ b.pro_name
+ FROM t_steel_produce a
+ LEFT JOIN sys_project b on b.pro_id = a.pro_id
+ WHERE a.is_qualified = 1 and a.is_use = 1 and b.company_id = #{companyId}
+ GROUP BY a.pro_id
+ ) d ON d.pro_id = c.pro_id
+ LEFT JOIN
+ (
+ SELECT
+ COUNT(a.pro_id) moldedNotNum,
+ a.pro_id,
+ b.pro_name
+ FROM t_steel_produce a
+ LEFT JOIN sys_project b on b.pro_id = a.pro_id
+ WHERE a.is_model = 2 and a.is_use = 1 and b.company_id = #{companyId}
+ GROUP BY a.pro_id
+ ) e on e.pro_id = c.pro_id
+ LEFT JOIN
+ (
+ SELECT
+ COUNT(a.pro_id) moldedNum,
+ a.pro_id,
+ b.pro_name
+ FROM t_steel_produce a
+ LEFT JOIN sys_project b on b.pro_id = a.pro_id
+ WHERE a.is_model = 1 and a.is_use = 1 and b.company_id = #{companyId}
+ GROUP BY a.pro_id
+ ) f on f.pro_id = c.pro_id
+ </select>
+
+</mapper>
--
Gitblit v1.9.3