<?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.toesbieya.my.mapper.BizStockMapper">
    <sql id="searchCondition">
        <if test="id!=null">and id=#{id}</if>
        <if test="ids!=null and ids!=''">and id in (${ids})</if>
        <if test="cid!=null">and cid=#{cid}</if>
        <if test="cids!=null and cids!=''">and cid in (${cids})</if>
        <if test="cgddid!=null and cgddid!=''">and cgddid=#{cgddid}</if>
        <if test="cgddids!=null and cgddids!=''">and cgddid in (${cgddids})</if>
        <if test="cgrkid!=null and cgrkid!=''">and cgrkid=#{cgrkid}</if>
        <if test="cgrkids!=null and cgrkids!=''">and cgrkid in (${cgrkids})</if>
        <if test="startTime!=null">and ctime <![CDATA[ >= ]]> #{startTime}</if>
        <if test="endTime!=null">and ctime <![CDATA[ <= ]]> #{endTime}</if>
    </sql>
    <select id="search" parameterType="com.toesbieya.my.model.vo.search.StockSearch"
            resultType="com.toesbieya.my.model.vo.result.StockSearchResult">
        select cid,
        cname,
        sum(num) total_num,
        sum(num*price) total_price
        from `biz_stock`
        where 1
        <include refid="searchCondition"/>
        and num >0
        group by cid
    </select>
    <select id="getDetail" resultType="com.toesbieya.my.model.entity.BizStock">
        select *
        from biz_stock
        where cid in (${cid})
          and num > 0
        order by cgddid desc, cgrkid desc
    </select>
    <select id="getDetailById" resultType="com.toesbieya.my.model.entity.BizStock">
        select *
        from biz_stock
        where id in (${ids})
    </select>
    <select id="export" parameterType="com.toesbieya.my.model.vo.search.StockSearch"
            resultType="com.toesbieya.my.model.vo.export.StockExport">
        select a.cid,a.cname,a.total_num,a.total_price,b.cgddid,b.cgrkid,b.price cg_price,
        from_unixtime(b.ctime / 1000, '%Y-%m-%d %H:%m:%s') ctime,
        c.num rk_num,
        d.num cg_num
        from (
        select cid,
        cname,
        sum(num) total_num,
        sum(num * price) total_price
        from `biz_stock`
        where 1
        <include refid="searchCondition"/>
        and num > 0
        group by cid
        ) a
        join biz_stock b using (cid)
        left join biz_purchase_inbound_sub c on c.cid = a.cid and c.pid = b.cgrkid
        left join biz_purchase_order_sub d on c.cid = d.cid and d.pid = b.cgddid
        order by a.cid,b.cgddid desc, b.cgrkid desc
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete
        from biz_stock
        where id = #{id,jdbcType=INTEGER}
    </delete>
    <update id="updateByPrimaryKeySelective" parameterType="com.toesbieya.my.model.entity.BizStock">
        update biz_stock
        <set>
            <if test="cid != null">
                cid = #{cid,jdbcType=INTEGER},
            </if>
            <if test="cname != null">
                cname = #{cname,jdbcType=VARCHAR},
            </if>
            <if test="num != null">
                num = #{num,jdbcType=DECIMAL},
            </if>
            <if test="price != null">
                price = #{price,jdbcType=DECIMAL},
            </if>
            <if test="ctime != null">
                ctime = #{ctime,jdbcType=BIGINT},
            </if>
            <if test="cgrkid != null">
                cgrkid = #{cgrkid,jdbcType=VARCHAR},
            </if>
            <if test="cgddid != null">
                cgddid = #{cgddid,jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>
    <update id="updateByPrimaryKey" parameterType="com.toesbieya.my.model.entity.BizStock">
        update biz_stock
        set cid    = #{cid,jdbcType=INTEGER},
            cname  = #{cname,jdbcType=VARCHAR},
            num    = #{num,jdbcType=DECIMAL},
            price  = #{price,jdbcType=DECIMAL},
            ctime  = #{ctime,jdbcType=BIGINT},
            cgrkid = #{cgrkid,jdbcType=VARCHAR},
            cgddid = #{cgddid,jdbcType=VARCHAR}
        where id = #{id,jdbcType=INTEGER}
    </update>
    <update id="updateBatchSelective" parameterType="java.util.List">
        update biz_stock
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="cid = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.cid != null">
                        when id = #{item.id,jdbcType=INTEGER} then #{item.cid,jdbcType=INTEGER}
                    </if>
                </foreach>
            </trim>
            <trim prefix="cname = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.cname != null">
                        when id = #{item.id,jdbcType=INTEGER} then #{item.cname,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>
            <trim prefix="num = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.num != null">
                        when id = #{item.id,jdbcType=INTEGER} then #{item.num,jdbcType=DECIMAL}
                    </if>
                </foreach>
            </trim>
            <trim prefix="price = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.price != null">
                        when id = #{item.id,jdbcType=INTEGER} then #{item.price,jdbcType=DECIMAL}
                    </if>
                </foreach>
            </trim>
            <trim prefix="ctime = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.ctime != null">
                        when id = #{item.id,jdbcType=INTEGER} then #{item.ctime,jdbcType=BIGINT}
                    </if>
                </foreach>
            </trim>
            <trim prefix="cgrkid = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.cgrkid != null">
                        when id = #{item.id,jdbcType=INTEGER} then #{item.cgrkid,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>
            <trim prefix="cgddid = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.cgddid != null">
                        when id = #{item.id,jdbcType=INTEGER} then #{item.cgddid,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach close=")" collection="list" item="item" open="(" separator=", ">
            #{item.id,jdbcType=INTEGER}
        </foreach>
    </update>
    <insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="com.toesbieya.my.model.entity.BizStock"
            useGeneratedKeys="true">
        insert into biz_stock
        (cid, cname, num, price, ctime, cgrkid, cgddid)
        values
        <foreach collection="list" item="item" separator=",">
            (#{item.cid,jdbcType=INTEGER}, #{item.cname,jdbcType=VARCHAR}, #{item.num,jdbcType=DECIMAL},
            #{item.price,jdbcType=DECIMAL}, #{item.ctime,jdbcType=BIGINT}, #{item.cgrkid,jdbcType=VARCHAR},
            #{item.cgddid,jdbcType=VARCHAR})
        </foreach>
    </insert>
    <update id="outbound">
        update biz_stock
        set num = num - #{num,jdbcType=DECIMAL}
        where id = #{id,jdbcType=INTEGER}
          and num <![CDATA[ >= ]]> #{num,jdbcType=DECIMAL}
    </update>
</mapper>