<?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.ld.igds.m.mapper.InoutCommonMapper">
|
|
|
<!-- 根据组织编码查询出入库设备配置信息 -->
|
<select id="listInoutConf" parameterType="java.lang.String" resultType="com.ld.igds.models.InoutConf">
|
select
|
ID_ as id,
|
COMPANY_ID_ as companyId,
|
DEPT_ID_ as deptId,
|
SORT_ as sort,
|
IN_ORDER_ as inOrder,
|
INTOU_PROGRESS_ as inoutProgress,
|
INOUT_TYPE_ as inoutType,
|
NAME_ as name,
|
SN_ as sn,
|
IP_ as ip,
|
PORT_ as port,
|
LOGIN_ID_ as loginId,
|
PWD_ as pwd,
|
CHAN_NUM_ as chanNum,
|
PORT_CTRL_ as portCtrl,
|
PORT_WEB_ as portWeb,
|
TYPE_ as type,
|
STATUS_ as status,
|
PROTOCOL_ as protocol,
|
PLAY_TYPE_ as playType,
|
SNAP_TYPE_ as snapType,
|
MEDIA_ADDR_ as mediaAddr,
|
REMARK_ as remark,
|
ERROR_MSG_ as errorMsg
|
from
|
D_INOUT_CONF
|
|
<where>
|
<if test="companyId != null ">AND COMPANY_ID_ = #{companyId}</if>
|
<if test="deptId != null and deptId != ''">AND DEPT_ID_ = #{deptId}</if>
|
</where>
|
|
</select>
|
|
<!-- 根据ip和端口更新出入库设备状态 -->
|
<update id="updateInoutConfStatus">
|
update D_INOUT_CONF
|
set STATUS_ = #{status}
|
where IP_ = #{ip}
|
and PORT_ = #{port}
|
</update>
|
|
<!-- 根据参数获取往来单位 -->
|
<select id="listCustomer" parameterType="com.ld.igds.inout.dto.InoutParam"
|
resultType="com.ld.igds.models.InoutCustomer">
|
select
|
ID_ as id,
|
COMPANY_ID_ as companyId,
|
DEPT_ID_ as deptId,
|
NAME_ as name,
|
TYPE_ as type,
|
CARD_ID_ as cardId,
|
ADDRESS_ as address,
|
CONTACT_USER_ as contactUser,
|
PHONE_ as phone,
|
BANK_ as bank,
|
BANK_NUM_ as bankNum,
|
FOOD_AREA_ as foodArea
|
from
|
D_INOUT_CUSTOMER
|
<where>
|
<if test="param.companyId != null and param.companyId != ''">AND COMPANY_ID_ = #{param.companyId}</if>
|
<if test="param.deptId != null and param.deptId != ''">AND DEPT_ID_ = #{param.deptId}</if>
|
<if test="param.type != null and param.type != ''">AND TYPE_ = #{param.type}</if>
|
<if test="param.key != null and param.key !='' ">AND ( ID_ like #{param.key} OR NAME_ like #{param.key} )
|
</if>
|
</where>
|
GROUP BY NAME_
|
order by CONVERT(NAME_ USING GBK)
|
</select>
|
|
<!-- 从入库通知单中获取往来单位 -->
|
<select id="listCustomerByNoticeIn" parameterType="com.ld.igds.inout.dto.InoutParam"
|
resultType="com.ld.igds.models.InoutCustomer">
|
select
|
CUSTOMER_ID_ as id,
|
CUSTOMER_NAME_ as name,
|
ID_ as noticeId
|
from
|
D_INOUT_NOTICE_IN
|
<where>
|
<if test="param.companyId != null and param.companyId !='' ">AND COMPANY_ID_ = #{param.companyId}</if>
|
<if test="param.deptId != null and param.deptId != ''">AND DEPT_ID_ = #{param.deptId}</if>
|
<if test="param.year != null and param.year !='' ">AND YEAR_ = #{param.year}</if>
|
<if test="param.type != null and param.type !='' ">AND COMPLETE_STATUS_ = #{param.type}</if>
|
<if test="param.key != null and param.key !='' ">AND ( CUSTOMER_ID_ like #{param.key} OR CUSTOMER_NAME_ like
|
#{param.key} )
|
</if>
|
</where>
|
GROUP BY CUSTOMER_NAME_
|
order by CONVERT(CUSTOMER_NAME_ USING GBK)
|
</select>
|
|
<!-- 获取出库通知单列表 -->
|
<select id="listNoticeOut" parameterType="com.ld.igds.m.dto.NoticeParam"
|
resultType="com.ld.igds.models.InoutNoticeOut">
|
select
|
ID_ as id,
|
DEPT_ID_ as deptId,
|
PLAN_ID_ as planId,
|
CONTRACT_ID_ as contractId,
|
NAME_ as name,
|
CUSTOMER_ID_ as customerId,
|
CUSTOMER_NAME_ as customerName,
|
UNIT_NAME_ as unitName,
|
FOOD_VARIETY_ as foodVariety,
|
FOOD_TYPE_ as foodType,
|
FOOD_LEVEL_ as foodLevel,
|
YEAR_ as year,
|
TARGET_NUMBER_ as targetNumber,
|
COMPLETE_NUMBER_ as completeNumber,
|
CREATE_TIME_ as createTime,
|
COMPLETE_TIME_ as completeTime,
|
COMPLETE_STATUS_ as completeStatus
|
from
|
D_INOUT_NOTICE_IN
|
<where>
|
<if test="param.companyId != null and param.companyId !='' ">AND COMPANY_ID_ = #{param.companyId}</if>
|
<if test="param.deptId != null and param.deptId != ''">AND DEPT_ID_ = #{param.deptId}</if>
|
<if test="param.year != null and param.year !='' ">AND YEAR_ = #{param.year}</if>
|
<if test="param.completeStatus != null and param.completeStatus !='' ">AND COMPLETE_STATUS_= #{param.completeStatus}
|
</if>
|
</where>
|
order by CREATE_TIME_ DESC
|
</select>
|
|
|
<!-- 从出库通知单中获取客户列表 -->
|
<select id="listCustomerByNoticeOut" parameterType="com.ld.igds.inout.dto.InoutParam"
|
resultType="com.ld.igds.models.InoutCustomer">
|
select
|
CUSTOMER_ID_ as id,
|
CUSTOMER_NAME_ as name,
|
ID_ as noticeId
|
from
|
D_INOUT_NOTICE_OUT
|
<where>
|
<if test="param.companyId != null and param.companyId !='' ">AND COMPANY_ID_ = #{param.companyId}</if>
|
<if test="param.deptId != null and param.deptId != ''">AND DEPT_ID_ = #{param.deptId}</if>
|
<if test="param.year != null and param.year !='' ">AND YEAR_ = #{param.year}</if>
|
<if test="param.type != null and param.type !='' ">AND COMPLETE_STATUS_ = #{param.type}</if>
|
<if test="param.key != null and param.key !='' ">AND ( CUSTOMER_ID_ like #{param.key} OR CUSTOMER_NAME_ like
|
#{param.key} )
|
</if>
|
</where>
|
GROUP BY CUSTOMER_NAME_
|
order by CONVERT(CUSTOMER_NAME_ USING GBK)
|
</select>
|
|
<!--更新往来单位信息-->
|
<update id="updateCustomer" parameterType="com.ld.igds.m.dto.NoticeInData">
|
update D_INOUT_CUSTOMER
|
<set>
|
<if test="data.address != null and data.address != '' ">ADDRESS_ = #{data.address},</if>
|
<if test="data.phone != null and data.phone != '' ">PHONE_ = #{data.phone},</if>
|
<if test="data.cardId != null and data.cardId != '' ">CARD_ID_ = #{data.cardId},</if>
|
<if test="data.bankNum != null and data.bankNum != '' ">BANK_NUM_ = #{data.bankNum}</if>
|
</set>
|
<where>
|
<if test="data.customerId != null and data.customerId != '' ">AND ID_ = #{data.customerId}</if>
|
<if test="data.customerName != null and data.customerName != '' ">AND NAME_ = #{data.customerName}</if>
|
</where>
|
</update>
|
|
<!-- 获取编码最大的单位 -->
|
<select id="getMaxCustomerId" parameterType="java.lang.String"
|
resultType="java.lang.String">
|
select ID_ from D_INOUT_CUSTOMER
|
where
|
COMPANY_ID_ = #{companyId}
|
order by ID_ DESC limit 1
|
</select>
|
|
<!-- 获取入库通知单信息 -->
|
<select id="listNoticeIn" parameterType="com.ld.igds.m.dto.NoticeParam"
|
resultType="com.ld.igds.models.InoutNoticeIn">
|
select
|
ID_ as id,
|
COMPANY_ID_ as companyId,
|
DEPT_ID_ as deptId,
|
PLAN_ID_ as planId,
|
CONTRACT_ID_ as contractId,
|
NAME_ as name,
|
CUSTOMER_ID_ as customerId,
|
CUSTOMER_NAME_ as customerName,
|
UNIT_NAME_ as unitName,
|
FOOD_VARIETY_ as foodVariety,
|
YEAR_ as year,
|
TARGET_NUMBER_ as targetNumber,
|
COMPLETE_NUMBER_ as completeNumber,
|
AUDIT_STATUS_ as auditStatus,
|
AUDIT_USER_ as auditUser,
|
COMPLETE_STATUS_ as completeStatus,
|
CREATE_TIME_ as createTime,
|
CREATE_USER_ as createUser,
|
COMPLETE_TIME_ as completeTime,
|
REMARK_ as remark
|
from
|
D_INOUT_NOTICE_IN
|
where
|
COMPANY_ID_ = #{param.companyId}
|
<if test="param.customerId != null and param.customerId != ''">
|
and CUSTOMER_ID_ = #{param.customerId}
|
</if>
|
<if test="param.foodVariety != null and param.foodVariety != ''">
|
and FOOD_VARIETY_ = #{param.foodVariety}
|
</if>
|
<if test="param.id != null and param.id != ''">
|
and ID_ = #{param.id}
|
</if>
|
<if test="param.year != null and param.year != ''">
|
and YEAR_ = #{param.year}
|
</if>
|
<if test="param.completeStatus != null and param.completeStatus != ''">
|
and COMPLETE_STATUS_ = #{param.completeStatus}
|
</if>
|
order by CREATE_TIME_ DESC
|
</select>
|
|
<!-- 新增入库通知单 -->
|
<insert id="addNoticeIn" parameterType="com.ld.igds.models.InoutNoticeIn">
|
insert into
|
D_INOUT_NOTICE_IN (
|
ID_,
|
COMPANY_ID_,
|
DEPT_ID_,
|
PLAN_ID_,
|
CONTRACT_ID_,
|
NAME_,
|
CUSTOMER_ID_,
|
CUSTOMER_NAME_,
|
UNIT_NAME_,
|
FOOD_VARIETY_,
|
YEAR_,
|
TARGET_NUMBER_,
|
COMPLETE_NUMBER_,
|
AUDIT_STATUS_,
|
AUDIT_USER_,
|
COMPLETE_STATUS_,
|
CREATE_TIME_,
|
CREATE_USER_,
|
UPDATE_TIME_,
|
REMARK_
|
)values (
|
#{param.id},
|
#{param.companyId},
|
#{param.deptId},
|
#{param.planId},
|
#{param.contractId},
|
#{param.name},
|
#{param.customerId},
|
#{param.customerName},
|
#{param.unitName},
|
#{param.foodVariety},
|
#{param.year},
|
#{param.targetNumber},
|
#{param.completeNumber},
|
#{param.auditStatus},
|
#{param.auditUser},
|
#{param.completeStatus},
|
#{param.createTime},
|
#{param.createUser},
|
#{param.updateTime},
|
#{param.remark}
|
)
|
</insert>
|
|
<!--更新入库通知单-->
|
<update id="updateNoticeIn" parameterType="com.ld.igds.m.dto.NoticeInData">
|
update D_INOUT_NOTICE_IN
|
<set>
|
<if test="data.customerName != null and data.customerName != '' ">CUSTOMER_NAME_ = #{data.customerName},
|
</if>
|
<if test="data.targetNumber != null and data.targetNumber != '' ">TARGET_NUMBER_ = #{data.targetNumber},
|
</if>
|
<if test="data.completeNumber != null and data.completeNumber != '' ">COMPLETE_NUMBER_ =
|
#{data.completeNumber},
|
</if>
|
<if test="data.companyId != null and data.companyId != '' ">COMPANY_ID_ = #{data.companyId},</if>
|
<if test="data.updateTime != null">UPDATE_TIME_ = #{data.updateTime},</if>
|
</set>
|
<where>
|
<if test="data.foodVariety != null and data.foodVariety != '' ">AND FOOD_VARIETY_ = #{data.foodVariety}</if>
|
<if test="data.customerId != null and data.customerId != '' ">AND CUSTOMER_ID_ = #{data.customerId}</if>
|
<if test="data.year != null and data.year != '' ">AND YEAR_ = #{data.year}</if>
|
</where>
|
</update>
|
|
<!-- 获取所有未完成状态入库通知单 -->
|
<select id="getUnComNoticeIn" resultType="com.ld.igds.models.InoutNoticeIn">
|
select
|
ID_ as id,
|
COMPANY_ID_ as companyId,
|
DEPT_ID_ as deptId,
|
PLAN_ID_ as planId,
|
CONTRACT_ID_ as contractId,
|
NAME_ as name,
|
CUSTOMER_ID_ as customerId,
|
CUSTOMER_NAME_ as customerName,
|
UNIT_NAME_ as unitName,
|
FOOD_VARIETY_ as foodVariety,
|
YEAR_ as year,
|
TARGET_NUMBER_ as targetNumber,
|
COMPLETE_NUMBER_ as completeNumber,
|
AUDIT_STATUS_ as auditStatus,
|
AUDIT_USER_ as auditUser,
|
COMPLETE_STATUS_ as completeStatus,
|
CREATE_TIME_ as createTime,
|
CREATE_USER_ as createUser,
|
COMPLETE_TIME_ as completeTime,
|
REMARK_ as remark
|
from
|
D_INOUT_NOTICE_IN
|
where
|
COMPLETE_STATUS_ = 'NONE'
|
and COMPANY_ID_ = #{companyId}
|
</select>
|
|
<!-- 重新计算入库通知单完成量 -->
|
<update id="reSumNoticeInComplete" parameterType="com.ld.igds.m.dto.NoticeParam">
|
update D_INOUT_NOTICE_IN t,(
|
SELECT
|
r.CUSTOMER_ID_,
|
r.FOOD_VARIETY_,
|
r.NOTICE_ID_,
|
sum(r.SETTLE_WEIGHT_) as COMPLETE_NUMBER_
|
from d_inout_record r
|
where r.COMPANY_ID_ = #{param.companyId}
|
and r.DEPT_ID_ = #{param.deptId}
|
and r.CUSTOMER_ID_ = #{param.customerId}
|
and r.FOOD_VARIETY_ = #{param.foodVariety}
|
and r.NOTICE_ID_ = #{param.id}
|
and r.RECORD_STATUS_ != 'DEL'
|
and r.RECORD_STATUS_ != 'ERROR'
|
GROUP BY r.FOOD_VARIETY_
|
) b
|
SET t.COMPLETE_NUMBER_ = b.COMPLETE_NUMBER_
|
WHERE t.CUSTOMER_ID_ = b.CUSTOMER_ID_
|
AND t.FOOD_VARIETY_ = b.FOOD_VARIETY_
|
AND t.ID_ = b.NOTICE_ID_
|
</update>
|
|
<!-- 根据参数查询出库通知单 -->
|
<select id="queryNoticeOut" resultType="int" parameterType="com.ld.igds.m.dto.NoticeOutParam">
|
select COUNT(*)
|
from D_INOUT_NOTICE_OUT
|
<where>
|
COMPANY_ID_ = #{param.companyId}
|
AND DEPT_ID_ = #{param.deptId}
|
<if test="param.customerId != null and param.customerId != ''">
|
AND CUSTOMER_ID_ = #{param.customerId}
|
</if>
|
<if test="param.depotId != null and param.depotId != ''">
|
AND DEPOT_ID_ = #{param.depotId}
|
</if>
|
<if test="param.year != null and param.year != ''">
|
AND YEAR_ = #{param.year}
|
</if>
|
<if test="param.foodVariety != null and param.foodVariety != ''">
|
AND FOOD_VARIETY_ = #{param.foodVariety}
|
</if>
|
<if test="param.completeStatus != null and param.completeStatus != ''">
|
AND COMPLETE_STATUS_ = #{param.completeStatus}
|
</if>
|
</where>
|
</select>
|
|
|
<!-- 根据参数查询ru库通知单 -->
|
<select id="queryNoticeIn" resultType="int" parameterType="com.ld.igds.m.dto.NoticeParam">
|
select COUNT(*)
|
from D_INOUT_NOTICE_IN
|
<where>
|
COMPANY_ID_ = #{param.companyId}
|
AND DEPT_ID_ = #{param.deptId}
|
<if test="param.customerId != null and param.customerId != ''">
|
AND CUSTOMER_ID_ = #{param.customerId}
|
</if>
|
<if test="param.year != null and param.year != ''">
|
AND YEAR_ = #{param.year}
|
</if>
|
<if test="param.foodVariety != null and param.foodVariety != ''">
|
AND FOOD_VARIETY_ = #{param.foodVariety}
|
</if>
|
<if test="param.completeStatus != null and param.completeStatus != ''">
|
AND COMPLETE_STATUS_ = #{param.completeStatus}
|
</if>
|
</where>
|
</select>
|
|
<!-- 获取出库通知单 -->
|
<select id="getNoticeOut" parameterType="com.ld.igds.m.dto.NoticeOutParam"
|
resultType="com.ld.igds.models.InoutNoticeOut">
|
select
|
ID_ as id,
|
COMPANY_ID_ as companyId,
|
DEPT_ID_ as deptId,
|
PLAN_ID_ as planId,
|
CONTRACT_ID_ as contractId,
|
NAME_ as name,
|
DEPOT_ID_ as depotId,
|
CUSTOMER_ID_ as customerId,
|
CUSTOMER_NAME_ as customerName,
|
UNIT_NAME_ as unitName,
|
FOOD_VARIETY_ as foodVariety,
|
FOOD_TYPE_ as foodType,
|
FOOD_LEVEL_ as foodLevel,
|
YEAR_ as year,
|
STORAGE_REAL_ as storageReal,
|
TARGET_NUMBER_ as targetNumber,
|
COMPLETE_NUMBER_ as completeNumber,
|
AUDIT_STATUS_ as auditStatus,
|
AUDIT_USER_ as auditUser,
|
COMPLETE_STATUS_ as completeStatus,
|
CREATE_TIME_ as createTime,
|
CREATE_USER_ as createUser,
|
COMPLETE_TIME_ as completeTime,
|
REMARK_ as remark
|
from
|
D_INOUT_NOTICE_OUT
|
where
|
COMPANY_ID_ = #{param.companyId}
|
<if test="param.customerId != null and param.customerId != ''">
|
and CUSTOMER_ID_ = #{param.customerId}
|
</if>
|
<if test="param.deptId != null and param.deptId != ''">
|
and DEPT_ID_ = #{param.deptId}
|
</if>
|
<if test="param.id != null and param.id != ''">
|
AND ID_ = #{param.id}
|
</if>
|
<if test="param.depotId != null and param.depotId != ''">
|
and DEPOT_ID_ = #{param.depotId}
|
</if>
|
<if test="param.year != null and param.year != ''">
|
and YEAR_ = #{param.year}
|
</if>
|
<if test="param.completeStatus != null and param.completeStatus != ''">
|
and COMPLETE_STATUS_ = #{param.completeStatus}
|
</if>
|
</select>
|
|
<!-- 获取所有未完成状态出库通知单 -->
|
<select id="getUnComNoticeOut" resultType="com.ld.igds.models.InoutNoticeOut">
|
select
|
ID_ as id,
|
COMPANY_ID_ as companyId,
|
DEPT_ID_ as deptId,
|
PLAN_ID_ as planId,
|
CONTRACT_ID_ as contractId,
|
NAME_ as name,
|
DEPOT_ID_ as depotId,
|
CUSTOMER_ID_ as customerId,
|
CUSTOMER_NAME_ as customerName,
|
UNIT_NAME_ as unitName,
|
FOOD_VARIETY_ as foodVariety,
|
FOOD_TYPE_ as foodType,
|
FOOD_LEVEL_ as foodLevel,
|
YEAR_ as year,
|
STORAGE_REAL_ as storageReal,
|
TARGET_NUMBER_ as targetNumber,
|
COMPLETE_NUMBER_ as completeNumber,
|
AUDIT_STATUS_ as auditStatus,
|
AUDIT_USER_ as auditUser,
|
COMPLETE_STATUS_ as completeStatus,
|
CREATE_TIME_ as createTime,
|
CREATE_USER_ as createUser,
|
COMPLETE_TIME_ as completeTime,
|
REMARK_ as remark
|
from
|
D_INOUT_NOTICE_OUT
|
where
|
COMPLETE_STATUS_ = 'NONE'
|
and COMPANY_ID_ = #{companyId}
|
</select>
|
|
<!-- 重新计算出库通知单完成量，只根据客户更新 -->
|
<update id="reSumNoticeOutComplete" parameterType="com.ld.igds.m.dto.NoticeOutParam">
|
update D_INOUT_NOTICE_OUT t,(
|
SELECT
|
r.CUSTOMER_ID_,
|
r.FOOD_VARIETY_,
|
r.DEPOT_ID_,
|
r.NOTICE_ID_,
|
sum(r.SETTLE_WEIGHT_) as COMPLETE_NUMBER_
|
from d_inout_record r
|
where r.COMPANY_ID_ = #{param.companyId}
|
AND r.DEPT_ID_ = #{param.deptId}
|
and r.CUSTOMER_ID_ = #{param.customerId}
|
and r.DEPOT_ID_ = #{param.depotId}
|
and r.FOOD_VARIETY_ = #{param.foodVariety}
|
and r.RECORD_STATUS_ != 'DEL'
|
and r.RECORD_STATUS_ != 'ERROR'
|
GROUP BY r.DEPOT_ID_
|
) b
|
SET t.COMPLETE_NUMBER_ = b.COMPLETE_NUMBER_
|
WHERE t.CUSTOMER_ID_ = b.CUSTOMER_ID_
|
AND t.FOOD_VARIETY_ = b.FOOD_VARIETY_
|
AND t.DEPOT_ID_ = b.DEPOT_ID_
|
AND t.ID_ = b.NOTICE_ID_
|
</update>
|
|
</mapper>
|
