侧边栏壁纸
  • 累计撰写 781 篇文章
  • 累计创建 1 个标签
  • 累计收到 1 条评论
标签搜索

精彩代码

Dettan
2021-04-10 / 0 评论 / 0 点赞 / 95 阅读 / 2,739 字
温馨提示:
本文最后更新于 2022-04-30,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。
<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>

0

评论区