<?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.BizSellOrderMapper">
    <sql id="searchCondition">
        <if test="id!=null and id!=''">and id=#{id}</if>
        <if test="id_fuzzy!=null and id_fuzzy!=''">and id like "%"#{id_fuzzy}"%"</if>
        <if test="customer_id!=null">and customer_id=#{customer_id}</if>
        <if test="customer_name!=null and customer_name!=''">and customer_name like "%"#{customer_name}"%"</if>
        <if test="cid!=null">and cid=#{cid}</if>
        <if test="cname!=null and cname!=''">and cname like "%"#{cname}"%"</if>
        <if test="vid!=null">and vid=#{vid}</if>
        <if test="vname!=null and vname!=''">and vname like "%"#{vname}"%"</if>
        <if test="status!=null and status!=''">and status in (${status})</if>
        <if test="finish!=null and finish!=''">and finish in (${finish})</if>
        <if test="ctimeStart!=null">and ctime <![CDATA[ >= ]]> #{ctimeStart}</if>
        <if test="ctimeEnd!=null">and ctime <![CDATA[ <= ]]> #{ctimeEnd}</if>
        <if test="vtimeStart!=null">and vtime <![CDATA[ >= ]]> #{vtimeStart}</if>
        <if test="vtimeEnd!=null">and vtime <![CDATA[ <= ]]> #{vtimeEnd}</if>
        <if test="ftimeStart!=null">and ftime <![CDATA[ >= ]]> #{ftimeStart}</if>
        <if test="ftimeEnd!=null">and ftime <![CDATA[ <= ]]> #{ftimeEnd}</if>
    </sql>
    <select id="getById" resultType="com.toesbieya.my.model.entity.BizSellOrder">
        select *
        from biz_sell_order
        where id = #{id}
    </select>
    <select id="getSubById" resultType="com.toesbieya.my.model.entity.BizSellOrderSub">
        select *
        from biz_sell_order_sub
        where pid = #{id}
    </select>
    <select id="search" resultType="com.toesbieya.my.model.entity.BizSellOrder"
            parameterType="com.toesbieya.my.model.vo.search.SellOrderSearch">
        select *
        from biz_sell_order
        where 1
        <include refid="searchCondition"/>
        order by ctime desc
    </select>
    <select id="export" resultType="com.toesbieya.my.model.vo.export.SellOrderExport"
            parameterType="com.toesbieya.my.model.vo.search.SellOrderSearch">
        select id,
        customer_name,
        cname,
        from_unixtime(ctime/1000,'%Y-%m-%d %H:%m:%s') ctime,
        vname,
        case status when 0 then '拟定' when 1 then'待审核' when 2 then '已审核' end status,
        case finish when 0 then '未开始' when 1 then '进行中' when 2 then '已完成' end finish,
        from_unixtime(vtime/1000,'%Y-%m-%d %H:%m:%s') vtime,
        from_unixtime(ftime/1000,'%Y-%m-%d %H:%m:%s') ftime,
        total,
        remark
        from biz_sell_order
        where 1
        <include refid="searchCondition"/>
        order by ctime desc
    </select>
    <insert id="add" parameterType="com.toesbieya.my.model.entity.BizSellOrder">
        insert into biz_sell_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            id,customer_id,customer_name,cid,cname,ctime,finish,total,
            <if test="vid !=null">vid,</if>
            <if test="vname !=null and vname!=''">vname,</if>
            <if test="vtime !=null">vtime,</if>
            <if test="status !=null">status,</if>
            <if test="ftime !=null">ftime,</if>
            <if test="remark !=null and remark!=''">remark,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{id},#{customer_id},#{customer_name},#{cid},#{cname},#{ctime},0,#{total},
            <if test="vid !=null">#{vid},</if>
            <if test="vname !=null and vname!=''">#{vname},</if>
            <if test="vtime !=null">#{vtime},</if>
            <if test="status !=null">#{status},</if>
            <if test="ftime !=null">#{ftime},</if>
            <if test="remark !=null and remark!=''">#{remark},</if>
        </trim>
    </insert>
    <insert id="addSub" parameterType="com.toesbieya.my.model.entity.BizSellOrderSub">
        INSERT INTO biz_sell_order_sub(pid, cid, cname, num, price, remain_num)
        VALUES
        <foreach collection="list" item="i" separator=",">
            (#{i.pid},#{i.cid},#{i.cname},#{i.num},#{i.price},#{i.remain_num})
        </foreach>
    </insert>
    <update id="update" parameterType="com.toesbieya.my.model.entity.BizSellOrder">
        update biz_sell_order
        set customer_id=#{customer_id},
            customer_name=#{customer_name},
            status=#{status},
            total=#{total},
            remark=#{remark}
        where id = #{id}
    </update>
    <update id="pass">
        update biz_sell_order
        set status=2,
            vid=#{vid},
            vname=#{vname},
            vtime=#{vtime}
        where id = #{id}
          and status = 1
    </update>
    <update id="reject">
        update biz_sell_order
        set status=0
        where id = #{id}
          and status = 1
    </update>
    <delete id="del">
        delete
        from biz_sell_order
        where id = #{id}
          and status = 0
    </delete>
    <delete id="delSubByPid">
        delete
        from biz_sell_order_sub
        where pid = #{pid}
    </delete>
    <update id="updateFinish">
        update biz_sell_order
        set finish=#{finish},
            ftime=#{ftime}
        where id = #{id}
          and status = 2
    </update>
    <update id="updateSubRemainNum">
        update biz_sell_order_sub
        set remain_num=#{remain_num}
        where id = #{id}
    </update>
    <select id="getDailyTotalPurchasePrice" resultType="java.lang.Double">
        select ifnull(sum(total), 0) total
        from biz_sell_order
        where status = 2
        <if test="start!=null">and vtime <![CDATA[ >= ]]> #{start}</if>
        <if test="end!=null">and vtime <![CDATA[ <= ]]> #{end}</if>
    </select>
    <select id="getLastDayFinishOrderNum" resultType="java.lang.Integer">
        select count(0)
        from biz_sell_order
        where status = 2
          and finish = 2
          and ftime between unix_timestamp(cast(sysdate() as date) - interval 1 day) * 1000 and unix_timestamp(cast(sysdate() as date)) * 1000
    </select>
    <select id="getDailyProfitGoods" resultType="com.toesbieya.my.model.entity.StatProfitGoods">
        select t1.*,
               t2.name cname
        from (select a.cid,
                     ifnull(sum(a.num * a.price), 0) sell
              from biz_sell_order_sub a
                       join biz_sell_order b on a.pid = b.id
              where b.vtime between #{start} and #{end}
              group by a.cid) t1
                 join sys_category t2 on t1.cid = t2.id
    </select>
</mapper>