精彩代码

<select id="findDetailByTypeAndMaterialIdList" parameterType="com.jsh.erp.datasource.entities.DepotItemExample" resultMap="DetailByTypeAndMIdResultMap">
    select dh.Number,concat(dh.SubType,dh.Type) as newType,
    case
        when type='入库' then ifnull(di.BasicNumber,0)
        when type='出库' then 0-di.BasicNumber
        when dh.SubType='组装单' and di.MType='组合件' then ifnull(di.BasicNumber,0)
        when dh.SubType='组装单' and di.MType='普通子件' then 0-di.BasicNumber
        when dh.SubType='拆卸单' and di.MType='普通子件' then ifnull(di.BasicNumber,0)
        when dh.SubType='拆卸单' and di.MType='组合件' then 0-di.BasicNumber
        else 0
    end
    as b_num,
    date_format(dh.OperTime,'%Y-%m-%d %H:%i:%S') as oTime
    from jsh_depothead dh
    INNER JOIN jsh_depotitem di on dh.id=di.HeaderId and ifnull(di.delete_Flag,'0') !='1'
    where ((dh.type!='其它' and dh.SubType!='调拨')
    or (dh.type='其它' and dh.SubType='组装单')
    or (dh.type='其它' and dh.SubType='拆卸单'))
    and di.MaterialId =${mId}
    and ifnull(dh.delete_Flag,'0') !='1'
    ORDER BY oTime desc
    <if test="offset != null and rows != null">
        limit #{offset},#{rows}
    </if>
</select>
<select id="getStockByParam"  resultMap="StockMap">

select ifnull((curep.inTotal+curep.transfInTotal+curep.assemInTotal+curep.disAssemInTotal),0) as in_stock,

ifnull((curep.transfOutTotal+curep.outTotal+curep.assemOutTotal+curep.disAssemOutTotal),0) out_stock

from

(select sum(if(dh.type='入库' <if test="depotId != null">and di.DepotId=#</if>, di.BasicNumber,0)) as inTotal,

sum(if(dh.SubType='调拨' <if test="depotId != null">and di.AnotherDepotId=#</if>,di.BasicNumber,0)) as transfInTotal,

sum(if(dh.SubType='调拨' <if test="depotId != null">and di.DepotId=#</if>,di.BasicNumber,0)) as transfOutTotal,

sum(if(dh.type='出库' and dh.SubType!='调拨' <if test="depotId != null">and di.DepotId=#</if>,di.BasicNumber,0)) as outTotal,

sum(if(dh.SubType='组装单' and di.MType='组合件' <if test="depotId != null">and di.DepotId=#</if>,di.BasicNumber,0)) as assemInTotal,

sum(if(dh.SubType='组装单' and di.MType='普通子件' <if test="depotId != null">and di.DepotId=#</if>,di.BasicNumber,0)) as assemOutTotal,

sum(if(dh.SubType='拆卸单' and di.MType='普通子件' <if test="depotId != null">and di.DepotId=#</if>,di.BasicNumber,0)) as disAssemInTotal,

sum(if(dh.SubType='拆卸单' and di.MType='组合件' <if test="depotId != null"> and di.DepotId=#</if>,di.BasicNumber,0)) as disAssemOutTotal

from

jsh_depothead dh,jsh_depotitem di

where 1=1

and dh.id=di.HeaderId

and di.MaterialId=#

<if test="beginTime != null">

and dh.OperTime >= '$'

</if>

<if test="endTime != null">

and dh.OperTime <= '$'

</if>

and dh.tenant_id=#

and di.tenant_id=#

and ifnull(dh.delete_Flag,'0') !='1'

and ifnull(di.delete_Flag,'0') !='1') as curep

</select>