<?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.jsh.erp.datasource.mappers.MaterialMapperEx">
    <resultMap extends="com.jsh.erp.datasource.mappers.MaterialMapper.BaseResultMap" id="ResultMapList" type="com.jsh.erp.datasource.entities.MaterialVo4Unit">
        <result column="unitName" jdbcType="VARCHAR" property="unitName" />
        <result column="categoryName" jdbcType="VARCHAR" property="categoryName" />
        <result column="bar_code" jdbcType="VARCHAR" property="mBarCode" />
        <result column="commodity_unit" jdbcType="VARCHAR" property="commodityUnit" />
        <result column="purchase_decimal" jdbcType="VARCHAR" property="purchaseDecimal" />
        <result column="commodity_decimal" jdbcType="VARCHAR" property="commodityDecimal" />
        <result column="wholesale_decimal" jdbcType="VARCHAR" property="wholesaleDecimal" />
        <result column="low_decimal" jdbcType="VARCHAR" property="lowDecimal" />
    </resultMap>

    <resultMap extends="com.jsh.erp.datasource.mappers.MaterialMapper.BaseResultMap" id="ResultMapListWithStock" type="com.jsh.erp.datasource.entities.MaterialVo4Unit">
        <result column="unitName" jdbcType="VARCHAR" property="unitName" />
        <result column="categoryName" jdbcType="VARCHAR" property="categoryName" />
        <result column="bar_code" jdbcType="VARCHAR" property="mBarCode" />
        <result column="commodity_unit" jdbcType="VARCHAR" property="commodityUnit" />
        <result column="purchase_decimal" jdbcType="VARCHAR" property="purchaseDecimal" />
        <result column="initialStock" jdbcType="DECIMAL" property="initialStock" />
        <result column="currentStock" jdbcType="DECIMAL" property="currentStock" />
        <result column="currentStockPrice" jdbcType="DECIMAL" property="currentStockPrice" />
    </resultMap>

    <resultMap extends="com.jsh.erp.datasource.mappers.MaterialMapper.BaseResultMap" id="ResultAndUnitMap" type="com.jsh.erp.datasource.entities.MaterialVo4Unit">
        <result column="unit_name" jdbcType="VARCHAR" property="unitName" />
        <result column="sku" jdbcType="VARCHAR" property="sku" />
    </resultMap>

    <select id="selectByConditionMaterial" parameterType="com.jsh.erp.datasource.entities.MaterialExample" resultMap="ResultMapList">
        select m.*,u.name unitName, mc.name categoryName, me.bar_code,
        me.purchase_decimal, me.commodity_decimal, me.wholesale_decimal,me.low_decimal
        FROM jsh_material m
        left JOIN jsh_material_extend me on m.id = me.material_id and ifnull(me.delete_Flag,'0') !='1'
        left JOIN jsh_unit u on m.unit_id = u.id and ifnull(u.delete_Flag,'0') !='1'
        left JOIN jsh_material_category mc on m.category_id = mc.id and ifnull(mc.delete_Flag,'0') !='1'
        where 1=1
        and me.default_flag=1
        <if test="barCode != null">
            <bind name="bindBarCode" value="'%'+barCode+'%'"/>
            and me.bar_code like #{bindBarCode}
        </if>
        <if test="name != null">
            <bind name="bindName" value="'%'+name+'%'"/>
            and m.name like #{bindName}
        </if>
        <if test="standard != null">
            <bind name="bindStandard" value="'%'+standard+'%'"/>
            and m.standard like #{bindStandard}
        </if>
        <if test="model != null">
            <bind name="bindModel" value="'%'+model+'%'"/>
            and m.model like #{bindModel}
        </if>
        <if test="idList.size()>0">
            and m.category_id in
            <foreach collection="idList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        and ifnull(m.delete_flag,'0') !='1'
        order by m.id desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

    <select id="countsByMaterial" resultType="java.lang.Long">
        SELECT
        COUNT(m.id)
        FROM jsh_material m
        left JOIN jsh_material_extend me on m.id = me.material_id and ifnull(me.delete_Flag,'0') !='1'
        left JOIN jsh_unit u on m.unit_id = u.id and ifnull(u.delete_Flag,'0') !='1'
        left JOIN jsh_material_category mc on m.category_id = mc.id and ifnull(mc.delete_Flag,'0') !='1'
        WHERE 1=1
        and me.default_flag=1
        <if test="barCode != null">
            <bind name="bindBarCode" value="'%'+barCode+'%'"/>
            and me.bar_code like #{bindBarCode}
        </if>
        <if test="name != null">
            <bind name="bindName" value="'%'+name+'%'"/>
            and m.name like #{bindName}
        </if>
        <if test="standard != null">
            <bind name="bindStandard" value="'%'+standard+'%'"/>
            and m.standard like #{bindStandard}
        </if>
        <if test="model != null">
            <bind name="bindModel" value="'%'+model+'%'"/>
            and m.model like #{bindModel}
        </if>
        <if test="idList.size()>0">
            and m.category_id in
            <foreach collection="idList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        and ifnull(m.delete_flag,'0') !='1'
    </select>

    <select id="findUnitList" resultType="com.jsh.erp.datasource.entities.Unit">
        select u.* from jsh_unit u
        left join jsh_material m on m.unit_id=u.id and ifnull(m.delete_flag,'0') !='1'
        where m.id = #{mId}
        and ifnull(u.delete_flag,'0') !='1'
    </select>

    <select id="findById" parameterType="com.jsh.erp.datasource.entities.MaterialExample" resultMap="ResultAndUnitMap">
        select m.*,u.name unit_name from jsh_material m
        left join jsh_unit u on m.unit_id=u.id and ifnull(u.delete_flag,'0') !='1'
        where m.id = #{id}
        and ifnull(m.delete_flag,'0') !='1'
    </select>

    <select id="findByIdWithBarCode" parameterType="com.jsh.erp.datasource.entities.MaterialExample" resultMap="ResultAndUnitMap">
        select m.*,u.name unit_name,me.bar_code m_bar_code, me.commodity_unit, me.purchase_decimal, me.commodity_decimal,
        me.wholesale_decimal, me.low_decimal
        from jsh_material m
        left join jsh_material_extend me on m.id=me.material_id and ifnull(me.delete_Flag,'0') !='1'
        left join jsh_unit u on m.unit_id=u.id and ifnull(u.delete_Flag,'0') !='1'
        where me.id = #{meId}
        and ifnull(m.delete_flag,'0') !='1'
    </select>

    <select id="findBySelectWithBarCode" parameterType="com.jsh.erp.datasource.entities.MaterialExample" resultMap="ResultAndUnitMap">
        select m.*,u.name unit_name,mc.name categoryName,me.bar_code m_bar_code,me.id meId,me.commodity_unit,me.sku from jsh_material m
        left join jsh_material_extend me on m.id=me.material_id and ifnull(me.delete_Flag,'0') !='1'
        left join jsh_unit u on m.unit_id=u.id and ifnull(u.delete_Flag,'0') !='1'
        left JOIN jsh_material_category mc on m.category_id = mc.id and ifnull(mc.delete_Flag,'0') !='1'
        where m.enabled=1 and me.id is not null
        <if test="q != null and q !=''">
            <bind name="bindKey" value="'%'+q+'%'"/>
            and (me.bar_code like #{bindKey} or m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey})
        </if>
        <if test="idList.size()>0">
            and m.category_id in
            <foreach collection="idList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        and ifnull(m.delete_flag,'0') !='1'
        ORDER BY me.id desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

    <select id="findBySelectWithBarCodeCount" resultType="java.lang.Integer">
        select count(1) from jsh_material m
        left join jsh_material_extend me on m.id=me.material_id and ifnull(me.delete_Flag,'0') !='1'
        left join jsh_unit u on m.unit_id=u.id and ifnull(u.delete_Flag,'0') !='1'
        where m.enabled=1 and me.id is not null
        <if test="q != null">
            <bind name="bindKey" value="'%'+q+'%'"/>
            and (me.bar_code like #{bindKey} or m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey})
        </if>
        <if test="idList.size()>0">
            and m.category_id in
            <foreach collection="idList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        and ifnull(m.delete_flag,'0') !='1'
    </select>

    <select id="findByAll" parameterType="com.jsh.erp.datasource.entities.MaterialExample" resultMap="ResultMapList">
        select m.*,u.name unitName, mc.name categoryName,me.commodity_unit,me.purchase_decimal, me.commodity_decimal,
        me.wholesale_decimal, me.low_decimal
        FROM jsh_material m
        left join jsh_material_extend me on m.id=me.material_id and ifnull(me.delete_Flag,'0') !='1'
        left JOIN jsh_unit u on m.unit_id = u.id and ifnull(u.delete_Flag,'0') !='1'
        left JOIN jsh_material_category mc on m.category_id = mc.id and ifnull(mc.delete_Flag,'0') !='1'
        where 1=1
        and me.default_flag=1
        <if test="barCode != null">
            <bind name="bindBarCode" value="'%'+barCode+'%'"/>
            and me.bar_code like #{bindBarCode}
        </if>
        <if test="name != null">
            <bind name="bindName" value="'%'+name+'%'"/>
            and m.name like #{bindName}
        </if>
        <if test="standard != null">
            <bind name="bindStandard" value="'%'+standard+'%'"/>
            and m.standard like #{bindStandard}
        </if>
        <if test="model != null">
            <bind name="bindModel" value="'%'+standard+'%'"/>
            and m.model like #{bindModel}
        </if>
        <if test="idList.size()>0">
            and m.category_id in
            <foreach collection="idList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        and ifnull(m.delete_flag,'0') !='1'
        order by m.id desc
    </select>
    <select id="findByMaterialName"  resultType="com.jsh.erp.datasource.entities.Material">
        select m.*
        FROM jsh_material m
        where 1=1
        <if test="name != null">
            and m.name =#{name}
        </if>
        and ifnull(m.delete_flag,'0') !='1'
    </select>

    <select id="getMaterialEnableSerialNumberList" parameterType="java.util.Map" resultMap="ResultAndUnitMap">
        select m.*,me.bar_code m_bar_code from jsh_material m
        left join jsh_material_extend me on m.id=me.material_id and ifnull(me.delete_Flag,'0') !='1'
        where 1=1
        and m.enabled=1
        and m.enable_serial_number ='1'
        <if test="q != null and q !=''">
            <bind name="bindKey" value="'%'+q+'%'"/>
            and (m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey})
        </if>
        and ifnull(m.delete_flag,'0') !='1'
        order by m.id desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

    <select id="getMaterialEnableSerialNumberCount" resultType="java.lang.Long">
        select count(me.id) from jsh_material m
        left join jsh_material_extend me on m.id=me.material_id and ifnull(me.delete_Flag,'0') !='1'
        where 1=1
        and m.enabled ='1'
        and m.enable_serial_number ='1'
        <if test="q != null and q !=''">
            <bind name="bindKey" value="'%'+q+'%'"/>
            and (m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey})
        </if>
        and ifnull(m.delete_flag,'0') !='1'
    </select>

    <update id="batchDeleteMaterialByIds">
        update jsh_material
        set delete_flag='1'
        where 1=1
        and ifnull(delete_flag,'0') !='1'
        and id in (
        <foreach collection="ids" item="id" separator=",">
            #{id}
        </foreach>
        )
    </update>
    <select id="getMaterialListByCategoryIds"  resultMap="com.jsh.erp.datasource.mappers.MaterialMapper.BaseResultMap">
        select
        <include refid="com.jsh.erp.datasource.mappers.MaterialMapper.Base_Column_List" />
        from jsh_material
        where 1=1
        and category_id in (
        <foreach collection="categoryIds" item="categoryId" separator=",">
            #{categoryId}
        </foreach>
        )
        and ifnull(delete_flag,'0') !='1'
    </select>
    <select id="getMaterialListByUnitIds"  resultMap="com.jsh.erp.datasource.mappers.MaterialMapper.BaseResultMap">
        select
        <include refid="com.jsh.erp.datasource.mappers.MaterialMapper.Base_Column_List" />
        from jsh_material
        where 1=1
        and unit_id in (
        <foreach collection="unitIds" item="unitId" separator=",">
            #{unitId}
        </foreach>
        )
        and ifnull(delete_flag,'0') !='1'
    </select>

    <select id="getMaxBarCode" resultType="java.lang.String">
        select max(CAST(l.bar_code AS SIGNED)) bar_code from jsh_material_extend l
        where 1=1
        and ifnull(l.delete_Flag,'0') !='1'
    </select>

    <select id="getMaterialByMeId" parameterType="com.jsh.erp.datasource.entities.MaterialExample" resultMap="ResultMapList">
        select m.*,me.bar_code,u.name unitName, mc.name categoryName
        FROM jsh_material m
        left join jsh_material_extend me on m.id=me.material_id and ifnull(me.delete_Flag,'0') !='1'
        left JOIN jsh_unit u on m.unit_id = u.id and ifnull(u.delete_flag,'0') !='1'
        left JOIN jsh_material_category mc on m.category_id = mc.id and ifnull(mc.delete_Flag,'0') !='1'
        where 1=1
        <if test="meId != null">
            and me.id = #{meId}
        </if>
        and ifnull(m.delete_flag,'0') !='1'
    </select>

    <select id="getMaterialNameList" resultType="java.lang.String">
        select m.name from jsh_material m
        where m.name is not null and m.name !='' and ifnull(m.delete_flag,'0') !='1'
        group by m.name
        order by m.name asc
    </select>

    <select id="getMaterialByBarCode" parameterType="com.jsh.erp.datasource.entities.MaterialExample" resultMap="ResultAndUnitMap">
        select m.*,u.name unit_name,me.bar_code m_bar_code, me.commodity_unit, me.purchase_decimal, me.commodity_decimal,
        me.wholesale_decimal, me.low_decimal, me.sku
        from jsh_material m
        left join jsh_material_extend me on m.id=me.material_id and ifnull(me.delete_Flag,'0') !='1'
        left join jsh_unit u on m.unit_id=u.id and ifnull(u.delete_Flag,'0') !='1'
        where me.bar_code = #{barCode}
        and ifnull(m.delete_flag,'0') !='1'
    </select>

    <update id="setUnitIdToNull">
        update jsh_material
        set unit_id = null
        where 1=1
        and ifnull(delete_flag,'0') !='1'
        and id = #{id}
    </update>

    <select id="getListWithStock" resultMap="ResultMapListWithStock">
        select m.*, me.commodity_unit unitName, mc.name categoryName, me.bar_code,
        ifnull(me.purchase_decimal,0) purchase_decimal,
        (select ifnull(sum(mis.number),0) from jsh_material_initial_stock mis where mis.material_id = m.id
        <if test="depotId != null">
            and mis.depot_id = #{depotId}
        </if>
        ) initialStock,
        ifnull(sum(mcs.current_number),0) currentStock,
        sum(ifnull(me.purchase_decimal, 0) * ifnull(mcs.current_number, 0)) currentStockPrice
        FROM jsh_material m
        left JOIN jsh_material_extend me on m.id = me.material_id and ifnull(me.delete_Flag,'0') !='1'
        left join jsh_material_current_stock mcs on m.id = mcs.material_id and ifnull(mcs.delete_flag,'0') !='1'
        left JOIN jsh_unit u on m.unit_id = u.id and ifnull(u.delete_Flag,'0') !='1'
        left JOIN jsh_material_category mc on m.category_id = mc.id and ifnull(mc.delete_Flag,'0') !='1'
        where 1=1
        and me.default_flag=1
        <if test="depotId != null">
            and mcs.depot_id = #{depotId}
        </if>
        <if test="idList.size()>0">
            and m.category_id in
            <foreach collection="idList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="materialParam != null">
            <bind name="bindParam" value="'%'+materialParam+'%'"/>
            and (me.bar_code like #{bindParam} or m.name like #{bindParam} or m.standard like #{bindParam} or m.model like #{bindParam})
        </if>
        and ifnull(m.delete_flag,'0') !='1'
        group by m.id
        order by m.id desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

    <select id="getListWithStockCount" resultType="java.lang.Integer">
        select count(tb.id) from
        (select m.id from jsh_material m
        left JOIN jsh_material_extend me on m.id = me.material_id and ifnull(me.delete_Flag,'0') !='1'
        left join jsh_material_current_stock mcs on m.id = mcs.material_id and ifnull(mcs.delete_flag,'0') !='1'
        left JOIN jsh_unit u on m.unit_id = u.id and ifnull(u.delete_Flag,'0') !='1'
        left JOIN jsh_material_category mc on m.category_id = mc.id and ifnull(mc.delete_Flag,'0') !='1'
        where 1=1
        and me.default_flag=1
        <if test="depotId != null">
            and mcs.depot_id = #{depotId}
        </if>
        <if test="idList.size()>0">
            and m.category_id in
            <foreach collection="idList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="materialParam != null">
            <bind name="bindParam" value="'%'+materialParam+'%'"/>
            and (me.bar_code like #{bindParam} or m.name like #{bindParam} or m.standard like #{bindParam} or m.model like #{bindParam})
        </if>
        and ifnull(m.delete_flag,'0') !='1'
        group by m.id) tb
    </select>

    <select id="getTotalStockAndPrice" resultType="com.jsh.erp.datasource.entities.MaterialVo4Unit">
        select
        ifnull(sum(mcs.current_number),0) currentStock,
        sum(ifnull(me.purchase_decimal,0)*ifnull(mcs.current_number,0)) currentStockPrice
        from jsh_material m
        left JOIN jsh_material_extend me on m.id = me.material_id and ifnull(me.delete_Flag,'0') !='1'
        left join jsh_material_current_stock mcs on m.id = mcs.material_id and ifnull(mcs.delete_flag,'0') !='1'
        left JOIN jsh_unit u on m.unit_id = u.id and ifnull(u.delete_Flag,'0') !='1'
        left JOIN jsh_material_category mc on m.category_id = mc.id and ifnull(mc.delete_Flag,'0') !='1'
        where 1=1
        and me.default_flag=1
        <if test="depotId != null">
            and mcs.depot_id = #{depotId}
        </if>
        <if test="idList.size()>0">
            and m.category_id in
            <foreach collection="idList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="materialParam != null">
            <bind name="bindParam" value="'%'+materialParam+'%'"/>
            and (me.bar_code like #{bindParam} or m.name like #{bindParam} or m.standard like #{bindParam} or m.model like #{bindParam})
        </if>
        and ifnull(m.delete_flag,'0') !='1'
    </select>

    <select id="checkIsExist" resultType="java.lang.Integer">
        select count(1) from jsh_material m
        where ifnull(m.delete_flag,'0') !='1'
        <if test="name != null">
            and m.name = #{name}
        </if>
        <if test="model != null">
            and m.model = #{model}
        </if>
        <if test="color != null">
            and m.color = #{color}
        </if>
        <if test="standard != null">
            and m.standard = #{standard}
        </if>
        <if test="mfrs != null">
            and m.mfrs = #{mfrs}
        </if>
        <if test="otherField1 != null">
            and m.other_field1 = #{otherField1}
        </if>
        <if test="otherField2 != null">
            and m.other_field2 = #{otherField2}
        </if>
        <if test="otherField3 != null">
            and m.other_field3 = #{otherField3}
        </if>
        <if test="unit != null">
            and m.unit = #{unit}
        </if>
        <if test="unitId != null">
            and m.unit_id = #{unitId}
        </if>
        <if test="id != null">
            and m.id != #{id}
        </if>
    </select>
</mapper>