精彩代码

<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=#{depotId}</if>, di.BasicNumber,0)) as inTotal,
    sum(if(dh.SubType='调拨' <if test="depotId != null">and di.AnotherDepotId=#{depotId}</if>,di.BasicNumber,0)) as transfInTotal,
    sum(if(dh.SubType='调拨' <if test="depotId != null">and di.DepotId=#{depotId}</if>,di.BasicNumber,0)) as transfOutTotal,
    sum(if(dh.type='出库' and dh.SubType!='调拨' <if test="depotId != null">and di.DepotId=#{depotId}</if>,di.BasicNumber,0)) as outTotal,
    sum(if(dh.SubType='组装单' and di.MType='组合件' <if test="depotId != null">and di.DepotId=#{depotId}</if>,di.BasicNumber,0)) as assemInTotal,
    sum(if(dh.SubType='组装单' and di.MType='普通子件' <if test="depotId != null">and di.DepotId=#{depotId}</if>,di.BasicNumber,0)) as assemOutTotal,
    sum(if(dh.SubType='拆卸单' and di.MType='普通子件' <if test="depotId != null">and di.DepotId=#{depotId}</if>,di.BasicNumber,0)) as disAssemInTotal,
    sum(if(dh.SubType='拆卸单' and di.MType='组合件' <if test="depotId != null"> and di.DepotId=#{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=#{mId}
    <if test="beginTime != null">
        and dh.OperTime >= '${beginTime}'
    </if>
    <if test="endTime != null">
        and dh.OperTime <= '${endTime}'
    </if>
    and dh.tenant_id=#{tenantId}
    and di.tenant_id=#{tenantId}
    and ifnull(dh.delete_Flag,'0') !='1'
    and ifnull(di.delete_Flag,'0') !='1') as curep
</select>