From 383788a3b32be282f59092b4111a66d2af4df8a2 Mon Sep 17 00:00:00 2001
From: 邱宇豪 <qyh123230312>
Date: 星期五, 03 十一月 2023 13:53:23 +0800
Subject: [PATCH] 20231103_qiuyh_发运计划
---
hd/pipe/materialsManage/src/main/resources/mapping/PipeOutPlanMapper.xml | 248 +++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 248 insertions(+), 0 deletions(-)
diff --git a/hd/pipe/materialsManage/src/main/resources/mapping/PipeOutPlanMapper.xml b/hd/pipe/materialsManage/src/main/resources/mapping/PipeOutPlanMapper.xml
new file mode 100644
index 0000000..b058152
--- /dev/null
+++ b/hd/pipe/materialsManage/src/main/resources/mapping/PipeOutPlanMapper.xml
@@ -0,0 +1,248 @@
+<?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.materials.modules.biz.pipeoutplan.mapper.PipeOutPlanMapper">
+
+ <!--查询列表-->
+ <select id="findAll" resultType="com.thhy.materials.modules.biz.pipeoutplan.entity.PipeOutPlanEntity">
+ SELECT
+ t.plan_out_id planOutId,
+ t.pro_id proId,
+ t.plan_year planYear,
+ t.need_pipe_num needPipeNum,
+ t.create_time createTime,
+ t.create_user createUser,
+ t.update_time updateTime,
+ t.update_user updateUser,
+ sp.pro_name proName
+ from t_pipe_out_plan t
+ left join sys_project sp on sp.pro_id = t.pro_id
+ WHERE t.is_use = 1
+ AND sp.company_id = #{companyId}
+ <if test="proId !=null and proId !=''">
+ and t.pro_id = #{proId}
+ </if>
+ <if test="planYear !=null and planYear !=''">
+ and t.plan_year like concat("%",#{planYear},"%")
+ </if>
+ order by t.create_time desc
+ </select>
+
+ <select id="findMothList" resultType="com.thhy.materials.modules.biz.pipeoutplan.entity.PipeOutPlanMothEntity">
+ select
+ id,
+ pipe_out_plan_id pipeOutPlanId,
+ month,
+ planProduct
+ from t_pipe_out_plan_moth
+ WHERE pipe_out_plan_id =#{pipeOutPlanId}
+
+ </select>
+ <!--查询单条-->
+ <select id="selectInfo" resultType="com.thhy.materials.modules.biz.pipeoutplan.entity.PipeOutPlanEntity">
+ SELECT
+ t.plan_out_id planOutId,
+ t.pro_id proId,
+ t.plan_year planYear,
+ t.need_pipe_num needPipeNum,
+ t.create_time createTime,
+ t.create_user createUser,
+ t.update_time updateTime,
+ t.update_user updateUser,
+ sp.pro_name proName
+ from t_pipe_out_plan t
+ where
+ t.is_use = 1
+ and t.plan_out_id = #{planOutId}
+ </select>
+
+ <select id="selectInfoByProIdAndYear" resultType="com.thhy.materials.modules.biz.pipeoutplan.entity.PipeOutPlanEntity">
+ SELECT
+ t.plan_out_id planOutId,
+ t.pro_id proId,
+ t.plan_year planYear,
+ t.need_pipe_num needPipeNum,
+ t.create_time createTime,
+ t.create_user createUser,
+ t.update_time updateTime,
+ t.update_user updateUser,
+ sp.pro_name proName
+ from t_pipe_out_plan t
+ where
+ t.is_use = 1
+ and t.pro_id = #{proId}
+ and t.plan_year = #{planYear}
+ </select>
+ <select id="findMothListByProIdAndYear" resultType="com.thhy.materials.modules.biz.pipeoutplan.entity.PipeOutPlanMothEntity">
+ SELECT a.`month`,a.plan_product planProduct,b.pro_id,e.num completePlanProduct FROM t_pipe_out_plan_moth a
+ LEFT JOIN t_pipe_out_plan b ON a.pipe_out_plan_id = b.plan_out_id
+ LEFT JOIN
+ (SELECT
+ d.yearAndMoth,
+ d.Moth,
+ IFNULL(c.num,0)num,
+ c.pro_id
+ FROM(
+ SELECT
+ IF(a.`index` < 10,CONCAT(#{year},'-0',a.`index`),CONCAT(#{year},'-',a.`index`)) yearAndMoth,
+ a.`index` Moth
+ FROM
+ (SELECT @s := @s + 1 AS `index` FROM mysql.help_topic,(SELECT @s := 0 ) temp WHERE @s < 12) a
+ )d
+ LEFT JOIN
+ ( SELECT
+ COUNT(b.pipe_id) num,
+ pro_id,
+ b.time yearAndMoth
+ FROM (
+ SELECT
+ pipe_id,
+ pro_id,
+ DATE_FORMAT( out_mod_time, '%Y-%m' ) time
+ FROM
+ t_pipe_info
+ WHERE
+ go_in = 2
+ AND pro_id = #{proId}
+ AND out_mod_time LIKE CONCAT(#{year},'%')
+ ) b
+ GROUP BY b.time
+ ) c ON c.yearAndMoth= d.yearAndMoth
+ ) e ON e.Moth = a.month
+ WHERE b.pro_id = #{proId}
+
+ </select>
+
+ <insert id="insertMoth">
+ insert into t_pipe_out_plan_moth
+ <trim prefix="(" suffix=")" suffixOverrides="," >
+ <if test="id != null">
+ id,
+ </if>
+ <if test="pipeOutPlanId != null">
+ pipe_out_plan_id,
+ </if>
+ <if test="month != null">
+ month,
+ </if>
+ <if test="planProduct != null">
+ plan_product,
+ </if>
+ </trim>
+ <trim prefix="values (" suffix=")" suffixOverrides="," >
+ <if test="id != null">
+ id = #{id,jdbcType=VARCHAR},
+ </if>
+ <if test="pipeOutPlanId != null">
+ pipe_out_plan_id = #{pipeOutPlanId,jdbcType=VARCHAR},
+ </if>
+ <if test="month != null">
+ `month` = #{month,jdbcType=INTEGER},
+ </if>
+ <if test="planProduct != null">
+ plan_product= #{planProduct,jdbcType=INTEGER},
+ </if>
+ </trim>
+ </insert>
+
+ <!--插入操作-->
+ <insert id="insert">
+ insert into t_pipe_out_plan
+ <trim prefix="(" suffix=")" suffixOverrides="," >
+
+ <if test="planOutId != null">
+ plan_out_id,
+ </if>
+ <if test="proId != null">
+ pro_id,
+ </if>
+ <if test="planYear != null">
+ plan_year,
+ </if>
+ <if test="needPipeNum != null">
+ need_pipe_num,
+ </if>
+ <if test="createTime != null">
+ create_time,
+ </if>
+ <if test="createUser != null">
+ create_user,
+ </if>
+ <if test="updateTime != null">
+ update_time,
+ </if>
+ <if test="updateUser != null">
+ update_user,
+ </if>
+ </trim>
+
+ <trim prefix="values (" suffix=")" suffixOverrides="," >
+ <if test="planOutId != null">
+ #{planOutId},
+ </if>
+ <if test="proId != null">
+ #{proId},
+ </if>
+ <if test="planYear != null">
+ #{planYear},
+ </if>
+ <if test="needPipeNum != null">
+ #{needPipeNum},
+ </if>
+ <if test="createTime != null">
+ #{createTime},
+ </if>
+ <if test="createUser != null">
+ #{createUser},
+ </if>
+ <if test="updateTime != null">
+ #{updateTime},
+ </if>
+ <if test="updateUser != null">
+ #{updateUser},
+ </if>
+ </trim>
+ </insert>
+
+
+ <!--更新操作-->
+ <update id="update">
+ update t_pipe_out_plan
+ <set>
+ <if test="proId != null">
+ pro_id=#{proId},
+ </if>
+ <if test="planYear != null">
+ plan_year=#{planYear},
+ </if>
+ <if test="needPipeNum != null">
+ needPipeNum = #{needPipeNum},
+ </if>
+ <if test="createTime != null">
+ create_time=#{createTime},
+ </if>
+ <if test="createUser != null">
+ create_user=#{createUser},
+ </if>
+ <if test="updateTime != null">
+ update_time=#{updateTime},
+ </if>
+ <if test="updateUser != null">
+ update_user=#{updateUser},
+ </if>
+ </set>
+ where plan_out_id=#{planOutId}
+ </update>
+
+ <!--逻辑删除-->
+ <update id="delete">
+ update t_pipe_out_plan
+ SET is_use = 0
+ where plan_out_id=#{planOutId}
+ </update>
+
+ <delete id="deleteMoth">
+ delete from t_pipe_out_plan_moth where plan_out_id=#{planOutId}
+ </delete>
+
+
+</mapper>
--
Gitblit v1.9.3