<?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="org.jeecg.modules.device.spare.mapper.SpareWarehousingMapper">
<resultMap type="org.jeecg.modules.device.spare.entity.SpareWarehousing" id="DevSpareWarehousingMap">
<result property="id" column="id" jdbcType="VARCHAR"/>
<result property="createBy" column="create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="updateBy" column="update_by" jdbcType="VARCHAR"/>
<result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
<result property="sysOrgCode" column="sys_org_code" jdbcType="VARCHAR"/>
<result property="tenantId" column="tenant_id" jdbcType="VARCHAR"/>
<result property="deleted" column="deleted" jdbcType="INTEGER"/>
<result property="inStorageNo" column="in_storage_no" jdbcType="VARCHAR"/>
<result property="inStorageDate" column="in_storage_date" jdbcType="TIMESTAMP"/>
<result property="personId" column="person_id" jdbcType="VARCHAR"/>
<result property="personName" column="personName" jdbcType="VARCHAR"/>
<result property="inStorageType" column="in_storage_type" jdbcType="VARCHAR"/>
<result property="remark" column="remark" jdbcType="VARCHAR"/>
<result column="warehouseId" property="warehouseId"/>
<result column="warehouseName" property="warehouseName"/>
<result property="title" column="title" jdbcType="VARCHAR"/>
<result property="resultState" column="resultState" jdbcType="VARCHAR"/>
<result property="procInstId" column="procInstId" jdbcType="VARCHAR"/>
<collection property="rspsList" javaType="java.util.List" column="id"
ofType="org.jeecg.modules.device.spare.entity.SpareWarehousingRelation" select="selectWarehousingRelation">
<result column="did" property="id"/>
<result column="spareId" property="spareId"/>
<result column="spareName" property="spareName"/>
<result column="spareCode" property="spareCode"/>
<result column="model" property="model"/>
<result column="quantity" property="quantity"/>
<result column="warehousingId" property="warehousingId"/>
<result column="inventory" property="inventory"/>
</collection>
</resultMap>
<sql id="Base_Column_List">
e.id,
e.create_by,
e.create_time,
e.update_by,
e.update_time,
e.sys_org_code,
e.tenant_id,
e.deleted,
e.in_storage_no,
e.in_storage_date,
e.person_id,
s.realname as personName,
e.in_storage_type,
e.remark,
e.warehouse_id as warehouseId,
ab.title as title,
ab.`result` AS resultState,
ab.proc_inst_id as procInstId
</sql>
<select id="selectWarehousingRelation" resultType="org.jeecg.modules.device.spare.entity.SpareWarehousingRelation">
select
swr.id as did,
swr.spare_id as spareId,
swr.spare_name as spareName,
swr.spare_code as spareCode,
swr.model as model,
swr.quantity as quantity,
swr.warehousing_id aswarehousingId,
COALESCE(w.inventory,0) as inventory
from spare_warehousing_relation swr
left join spare_warehouse w on w.warehouse_id = e.warehouse_id
left join dev_spare_warehousing as e on swr.warehousing_id = e.id
where swr.warehousing_id=#{id}
</select>
<!--Query the specified row data-->
<select id="queryPageList" parameterType="java.util.HashMap" resultMap="DevSpareWarehousingMap">
select
<include refid="Base_Column_List"/>
from dev_spare_warehousing as e
LEFT JOIN act_z_business ab ON e.id = ab.table_id
left join sys_user s on s.id = e.person_id
left join dev_spare_warehouse sw on sw.id = e.warehouse_id
<where>
e.deleted = 0
<if test="param.id != null and param.id != ''">
and e.id = #{param.id}
</if>
<if test="param.createBy != null and param.createBy != ''">
and e.create_by = #{param.createBy}
</if>
<if test="param.createTime != null">
and e.create_time = #{param.createTime}
</if>
<if test="param.updateBy != null and param.updateBy != ''">
and e.update_by = #{param.updateBy}
</if>
<if test="param.updateTime != null">
and e.update_time = #{param.updateTime}
</if>
<if test="param.sysOrgCode != null and param.sysOrgCode != ''">
and e.sys_org_code = #{param.sysOrgCode}
</if>
<if test="param.tenantId != null and param.tenantId != ''">
and e.tenant_id = #{param.tenantId}
</if>
<if test="param.deleted != null">
and e.deleted = #{param.deleted}
</if>
<if test="param.inStorageDate != null">
and e.in_storage_date = #{param.inStorageDate}
</if>
<if test="param.personId != null and param.personId != ''">
and e.person_id = #{param.personId}
</if>
<if test="param.inStorageType != null and param.inStorageType != ''">
and e.in_storage_type = #{param.inStorageType}
</if>
<if test="param.remark != null and param.remark != ''">
and e.remark = #{param.remark}
</if>
<!--Fuzzy query according to page needs-->
<if test="param.inStorageNo != null and param.inStorageNo != ''">
and e.in_storage_no like CONCAT(CONCAT('%', #{param.inStorageNo}), '%')
</if>
<if test="param.personName != null and param.personName != ''">
and s.realname like CONCAT(CONCAT('%', #{param.personName}), '%')
</if>
</where>
<if test="offset!=null">
order by e.create_time desc
LIMIT #{offset}, #{pageSize}
</if>
</select>
<!--Querying a single data is currently used for workflow echo display-->
<select id="queryObject" parameterType="String" resultMap="DevSpareWarehousingMap">
select
<include refid="Base_Column_List"/>
from dev_spare_warehousing as e
LEFT JOIN act_z_business ab ON e.id = ab.table_id
left join sys_user s on s.id = e.person_id
left join dev_spare_warehouse sw on sw.id = e.warehouse_id
left join spare_warehousing_relation swr on swr.warehousing_id = e.id
left join spare_warehouse w on w.warehouse_id = e.warehouse_id
<where>
e.deleted = 0
<if test="tableId != null and tableId != ''">
and e.id = #{tableId}
</if>
</where>
</select>
<!--Count the total number of rows-->
<select id="queryPageListCount" parameterType="java.util.HashMap" resultType="java.lang.Long">
select count(1)
from dev_spare_warehousing as e
left join sys_user s on s.id = e.person_id
<where>
e.deleted = 0
<if test="param.id != null and param.id != ''">
and e.id = #{param.id}
</if>
<if test="param.createBy != null and param.createBy != ''">
and e.create_by = #{param.createBy}
</if>
<if test="param.createTime != null">
and e.create_time = #{param.createTime}
</if>
<if test="param.updateBy != null and param.updateBy != ''">
and e.update_by = #{param.updateBy}
</if>
<if test="param.updateTime != null">
and e.update_time = #{param.updateTime}
</if>
<if test="param.sysOrgCode != null and param.sysOrgCode != ''">
and e.sys_org_code = #{param.sysOrgCode}
</if>
<if test="param.tenantId != null and param.tenantId != ''">
and e.tenant_id = #{param.tenantId}
</if>
<if test="param.deleted != null">
and e.deleted = #{param.deleted}
</if>
<if test="param.inStorageDate != null">
and e.in_storage_date = #{param.inStorageDate}
</if>
<if test="param.personId != null and param.personId != ''">
and e.person_id = #{param.personId}
</if>
<if test="param.inStorageType != null and param.inStorageType != ''">
and e.in_storage_type = #{param.inStorageType}
</if>
<if test="param.remark != null and param.remark != ''">
and e.remark = #{param.remark}
</if>
<!--Fuzzy query according to page needs-->
<if test="param.inStorageNo != null and param.inStorageNo != ''">
and e.in_storage_no like CONCAT(CONCAT('%', #{param.inStorageNo}), '%')
</if>
<if test="param.personName != null and param.personName != ''">
and s.realname like CONCAT(CONCAT('%', #{param.personName}), '%')
</if>
</where>
</select>
</mapper>