<?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.yunfeiyun.agmp.fms.mapper.SpmOrderMapper">
    
    <resultMap type="SpmOrder" id="SpmOrderResult">
        <result property="orderId"    column="orderId"    />
        <result property="customerId"    column="customerId"    />
        <result property="stockId"    column="stockId"    />
        <result property="orderStatus"    column="orderStatus"    />
        <result property="orderNo" column="orderNo" />
        <result property="orderGoodscount"    column="orderGoodscount"    />
        <result property="orderCreateddate"    column="orderCreateddate"    />
        <result property="orderSenddate"    column="orderSenddate"    />
        <result property="orderTracecodenum"    column="orderTracecodenum"    />
        <result property="orderSendtype"    column="orderSendtype"    />
        <result property="orderConsigneerealname"    column="orderConsigneerealname"    />
        <result property="orderConsigneetelphone"    column="orderConsigneetelphone"    />
        <result property="orderConsigneeaddress"    column="orderConsigneeaddress"    />
        <result property="orderLogisticsplate"    column="orderLogisticsplate"    />
        <result property="orderLogisticsNo"    column="orderLogisticsNo"    />
        <result property="orderRemark"    column="orderRemark"    />
        <result property="orderSendtyperemark"    column="orderSendtyperemark"    />
        <result property="orderExpectedarrivaldate" column="orderExpectedarrivaldate" />
        <result property="orderConsigneeprovince" column="orderConsigneeprovince" />
        <result property="orderConsigneecity" column="orderConsigneecity" />
        <result property="orderConsigneedistrict" column="orderConsigneedistrict" />
        <result property="orderPrice" column="orderPrice" />

    </resultMap>

    <sql id="selectSpmOrderVo">
        select orderPrice,orderId, customerId, stockId, orderNo, orderStatus, orderGoodscount, orderCreateddate, orderSenddate, orderTracecodenum, orderSendtype, orderConsigneerealname, orderConsigneetelphone, orderConsigneeaddress, orderLogisticsplate, orderLogisticsNo, orderRemark, orderSendtyperemark, orderExpectedarrivaldate, orderConsigneeprovince, orderConsigneecity, orderConsigneedistrict from SpmOrder
    </sql>

    <select id="selectSpmOrderList" parameterType="SpmOrder" resultMap="SpmOrderResult">
        <include refid="selectSpmOrderVo"/>
        <where>  
            <if test="customerId != null  and customerId != ''"> and customerId = #{customerId}</if>
            <if test="stockId != null  and stockId != ''"> and stockId = #{stockId}</if>
            <if test="orderNo != null and orderNo !=''">and orderNo = #{orderNo}</if>
            <if test="orderStatus != null  and orderStatus != ''"> and orderStatus = #{orderStatus}</if>
            <if test="orderGoodscount != null "> and orderGoodscount = #{orderGoodscount}</if>
            <if test="orderCreateddate != null  and orderCreateddate != ''"> and orderCreateddate = #{orderCreateddate}</if>
            <if test="orderSenddate != null  and orderSenddate != ''"> and orderSenddate = #{orderSenddate}</if>
            <if test="orderTracecodenum != null "> and orderTracecodenum = #{orderTracecodenum}</if>
            <if test="orderSendtype != null "> and orderSendtype = #{orderSendtype}</if>
            <if test="orderConsigneerealname != null  and orderConsigneerealname != ''"> and orderConsigneerealname like concat('%', #{orderConsigneerealname}, '%')</if>
            <if test="orderConsigneetelphone != null  and orderConsigneetelphone != ''"> and orderConsigneetelphone = #{orderConsigneetelphone}</if>
            <if test="orderConsigneeaddress != null  and orderConsigneeaddress != ''"> and orderConsigneeaddress = #{orderConsigneeaddress}</if>
            <if test="orderLogisticsplate != null  and orderLogisticsplate != ''"> and orderLogisticsplate = #{orderLogisticsplate}</if>
            <if test="orderLogisticsNo != null  and orderLogisticsNo != ''"> and orderLogisticsNo = #{orderLogisticsNo}</if>
            <if test="orderRemark != null  and orderRemark != ''"> and orderRemark = #{orderRemark}</if>
            <if test="orderSendtyperemark != null  and orderSendtyperemark != ''"> and orderSendtyperemark = #{orderSendtyperemark}</if>
            <if test="orderExpectedarrivaldate != null and orderExpectedarrivaldate != ''" >and orderExpectedarrivaldate = #{orderExpectedarrivaldate}</if>
        </where>
    </select>
    <select id="selectUndeliveredOrder" parameterType="String" resultType="SpmOrderResVo">
        SELECT
        so.orderId,
        so.orderNo,
        so.orderStatus,
        wf.finishgoodscode,
        so.orderGoodscount,
        wg.goodsUnit,
        so.orderConsigneerealname,
        so.orderConsigneetelphone,
        so.orderConsigneeaddress,
        so.orderSendtype
        FROM
        SpmOrder so
        LEFT JOIN WmsStock ws ON ws.stockId = so.stockId
        LEFT JOIn WmsGoods wg on wg.goodsId = ws.goodsId
        LEFT JOIN WmsFinishgoods wf ON wf.goodsId = ws.goodsId
        <where>
            orderStatus = '1' and orderExpectedarrivaldate <![CDATA[ < ]]> #{date}
        </where>
    </select>

    <select id="selectSpmOrderOfScreenList" parameterType="String" resultType="SpmOrderOfScreenResVo">
        SELECT
        so.orderId,
        so.orderNo,
        so.orderPrice,
        so.orderStatus,
        wf.finishgoodscode,
        so.orderGoodscount,
        wg.goodsUnit,
        wg.goodsName,
        so.orderConsigneerealname,
        so.orderConsigneetelphone,
        so.orderConsigneeaddress,
        so.orderSendtype,
        so.orderCreateddate,
        so.orderSenddate,
        so.orderExpectedarrivaldate
        FROM
        SpmOrder so
        LEFT JOIN WmsStock ws ON ws.stockId = so.stockId
        LEFT JOIn WmsGoods wg on wg.goodsId = ws.goodsId
        LEFT JOIN WmsFinishgoods wf ON wf.goodsId = ws.goodsId
        order by orderCreateddate desc
        limit 8
    </select>

    <select id="selectSpmOrderStat" parameterType="SelectCountOfScreenReqVo" resultType="map">
        SELECT sum(o.orderGoodscount * g.goodsSpec) sum, count(1) count FROM SpmOrder o
        LEFT JOIN WmsStock s ON s.stockId = o.stockId
        LEFT JOIN WmsGoods g ON g.goodsId = s.goodsId
        <where>
            <if test="orderStatus != null  and orderStatus != ''"> and o.orderStatus = #{orderStatus}</if>
            <if test="startTime != null  and startTime != ''"> and o.orderCreateddate >= #{startTime}</if>
            <if test="endTime != null  and endTime != ''"> and o.orderCreateddate <![CDATA[ <= ]]> #{endTime}</if>
            <if test="startTimeSend != null  and startTimeSend != ''"> and o.orderSenddate >= #{startTimeSend}</if>
            <if test="endTimeSend != null  and endTimeSend != ''"> and o.orderSenddate <![CDATA[ <= ]]> #{endTimeSend}</if>
            <if test="startTimeArrival != null  and startTimeArrival != ''"> and o.orderExpectedarrivaldate >= #{startTimeArrival}</if>
            <if test="endTimeArrival != null  and endTimeArrival != ''"> and o.orderExpectedarrivaldate <![CDATA[ <= ]]> #{endTimeArrival}</if>
        </where>
    </select>

    <select id="selectSpmOrderByGoodsStat" parameterType="SelectCountOfScreenReqVo" resultType="SpmOrderStatResVo">
        SELECT sum(o.orderGoodscount * g.goodsSpec) sum, g.goodsName
        FROM SpmOrder o
        LEFT JOIN WmsStock s ON s.stockId = o.stockId
        LEFT JOIN WmsGoods g ON g.goodsId = s.goodsId
        <where>
            <if test="orderStatus != null  and orderStatus != ''"> and o.orderStatus = #{orderStatus}</if>
            <if test="startTime != null  and startTime != ''"> and o.orderCreateddate >= #{startTime}</if>
            <if test="endTime != null  and endTime != ''"> and o.orderCreateddate <![CDATA[ <= ]]> #{endTime}</if>
        </where>
        GROUP BY g.goodsName
        HAVING sum > 0
        ORDER BY sum desc
        limit 5
    </select>
    
    <!-- 大厅：农产品销售额TOP5 -->
    <select id="selectSpmOrderMoneyByGoodsStat" parameterType="SelectCountOfScreenReqVo" resultType="SpmOrderStatResVo">
        SELECT sum(o.orderPrice) sum, g.goodsName
        FROM SpmOrder o
        LEFT JOIN WmsStock s ON s.stockId = o.stockId
        LEFT JOIN WmsGoods g ON g.goodsId = s.goodsId
        <where>
            <if test="orderStatus != null  and orderStatus != ''"> and o.orderStatus = #{orderStatus}</if>
            <if test="startTime != null  and startTime != ''"> and o.orderCreateddate >= #{startTime}</if>
            <if test="endTime != null  and endTime != ''"> and o.orderCreateddate <![CDATA[ <= ]]> #{endTime}</if>
        </where>
        GROUP BY g.goodsName
        HAVING sum > 0
        ORDER BY sum desc
        limit 5
    </select>

    <select id="selectCountGroupByProvince" parameterType="SelectCountOfScreenReqVo" resultType="SpmOrderStatResVo">
        SELECT sum(o.orderGoodscount * g.goodsSpec) sum, count(1) count, o.orderConsigneeprovince
        FROM SpmOrder o
        LEFT JOIN WmsStock s ON s.stockId = o.stockId
        LEFT JOIN WmsGoods g ON g.goodsId = s.goodsId
        LEFT JOIN WmsFinishgoods fg ON fg.goodsId = s.goodsId
        where o.orderConsigneeprovince is not null and o.orderConsigneecity is not null
        <if test="orderStatus != null  and orderStatus != ''"> and o.orderStatus = #{orderStatus}</if>
        <if test="startTime != null  and startTime != ''"> and o.orderCreateddate >= #{startTime}</if>
        <if test="endTime != null  and endTime != ''"> and o.orderCreateddate <![CDATA[ <= ]]> #{endTime}</if>
        <if test="finishgoodscode != null  and finishgoodscode != ''"> and fg.finishgoodscode = #{finishgoodscode}</if>
        GROUP BY o.orderConsigneeprovince
    </select>

    <select id="selectCountGroupByCity" parameterType="SelectCountOfScreenReqVo" resultType="SpmOrderStatResVo">
        SELECT tb.sum, tb.count, tb.orderConsigneecity, tb.orderConsigneeprovince, z.zoneLatitude, z.zoneLongitude
        FROM
        (
        SELECT sum(o.orderGoodscount * g.goodsSpec) sum, count(1) count, o.orderConsigneecity, o.orderConsigneeprovince
        FROM SpmOrder o
        LEFT JOIN WmsStock s ON s.stockId = o.stockId
        LEFT JOIN WmsGoods g ON g.goodsId = s.goodsId
        LEFT JOIN WmsFinishgoods fg ON fg.goodsId = s.goodsId
        where o.orderConsigneeprovince is not null and o.orderConsigneecity is not null
        <if test="orderStatus != null  and orderStatus != ''"> and o.orderStatus = #{orderStatus}</if>
        <if test="orderStatus == null">and o.orderStatus <![CDATA[ <> ]]> 0</if>
        <if test="startTime != null  and startTime != ''"> and o.orderCreateddate >= #{startTime}</if>
        <if test="endTime != null  and endTime != ''"> and o.orderCreateddate <![CDATA[ <= ]]> #{endTime}</if>
        <if test="finishgoodscode != null  and finishgoodscode != ''"> and fg.finishgoodscode = #{finishgoodscode}</if>
        GROUP BY o.orderConsigneecity, o.orderConsigneeprovince
        ) tb
        LEFT JOIN SysZone z ON z.zoneProvincezh = tb.orderConsigneeprovince and z.zoneCapitalzh = tb.orderConsigneecity
        and z.zoneCityzh = tb.orderConsigneecity
        order by sum desc
    </select>

    <select id="selectSpmOrderResVoList" parameterType="SpmOrder" resultType="com.yunfeiyun.agmp.fms.domain.resvo.SpmOrderResVo">
        SELECT
        so.orderId,
        so.orderNo,
        so.orderStatus,
        wf.finishgoodscode,
        so.orderGoodscount,
        wg.goodsUnit,
        so.orderConsigneerealname,
        so.orderConsigneetelphone,
        so.orderConsigneeaddress,
        tc.orderTracecodenum,
        so.orderCreateddate,
        so.orderSendtype
        FROM
        SpmOrder so
        LEFT JOIN WmsStock ws ON ws.stockId = so.stockId
        LEFT JOIn WmsGoods wg on wg.goodsId = ws.goodsId
        LEFT JOIN WmsFinishgoods wf ON wf.goodsId = ws.goodsId
        LEFT JOIN (
        select count(1) as orderTracecodenum,orderId from TssTracecodeapply GROUP BY orderId
        )  tc on tc.orderId = so.orderId
        <where>
            <if test="customerId != null  and customerId != ''"> and so.customerId = #{customerId}</if>
            <if test="stockId != null  and stockId != ''"> and so.stockId = #{stockId}</if>
            <if test="orderNo != null and orderNo !=''">and so.orderNo = #{orderNo}</if>
            <if test="orderStatus != null  and orderStatus != ''"> and so.orderStatus = #{orderStatus}</if>
            <if test="orderGoodscount != null "> and so.orderGoodscount = #{orderGoodscount}</if>
            <if test="orderCreateddate != null  and orderCreateddate != ''"> and so.orderCreateddate = #{orderCreateddate}</if>
            <if test="orderSenddate != null  and orderSenddate != ''"> and so.orderSenddate = #{orderSenddate}</if>
            <if test="orderTracecodenum != null "> and so.orderTracecodenum = #{orderTracecodenum}</if>
            <if test="orderSendtype != null "> and so.orderSendtype = #{orderSendtype}</if>
            <if test="orderConsigneerealname != null  and orderConsigneerealname != ''"> and so.orderConsigneerealname like concat('%', #{orderConsigneerealname}, '%')</if>
            <if test="orderConsigneetelphone != null  and orderConsigneetelphone != ''"> and so.orderConsigneetelphone = #{orderConsigneetelphone}</if>
            <if test="orderConsigneeaddress != null  and orderConsigneeaddress != ''"> and so.orderConsigneeaddress = #{orderConsigneeaddress}</if>
            <if test="orderLogisticsplate != null  and orderLogisticsplate != ''"> and so.orderLogisticsplate = #{orderLogisticsplate}</if>
            <if test="orderLogisticsNo != null  and orderLogisticsNo != ''"> and so.orderLogisticsNo = #{orderLogisticsNo}</if>
            <if test="orderRemark != null  and orderRemark != ''"> and so.orderRemark = #{orderRemark}</if>
            <if test="orderSendtyperemark != null  and orderSendtyperemark != ''"> and so.orderSendtyperemark = #{orderSendtyperemark}</if>
            <if test="orderExpectedarrivaldate != null and orderExpectedarrivaldate != ''" >and so.orderExpectedarrivaldate = #{orderExpectedarrivaldate}</if>
        </where>
    </select>

    <select id="selectSpmOrderLogisticsInfoReqVo" parameterType="com.yunfeiyun.agmp.fms.domain.reqvo.SpmOrderLogisticsInfoReqVo" resultType="com.yunfeiyun.agmp.fms.domain.resvo.SpmOrderLogisticsInfoResVo">
        SELECT
        so.*,
        sfg.finishgoodscode,
        s.resUrl,
        sc.customerContancts,
        wg.goodsUnit,
        sfg.finishgoodNo,
        d.dictName
        FROM
        SpmOrder so
        LEFT JOIN WmsStock ws ON ws.stockId = so.stockId
        LEFT JOIN WmsGoods wg on wg.goodsId = ws.goodsId
        LEFT JOIN WmsFinishgoods sfg ON sfg.goodsId = ws.goodsId
        LEFT JOIN TssSourceinfo ts ON ts.sourceinfoId = sfg.sourceinfoId
        LEFT JOIN (select * from SysRes where resBiztype = 'SOURCE_INFO_AVATAR') s ON s.resBusId = ts.sourceinfoId
        LEFT JOIN (select * from SysDict where dicttypeCode = 'agm_mpp_product') d on dictValue = sfg.finishgoodscode
        LEFT JOIN SysCustomer sc ON sc.customerId = so.customerId
        <where>
            <if test="extentQueryParam != null and extentQueryParam != ''" >and (d.dictName like CONCAT("%",#{extentQueryParam},"%") or so.orderNo like CONCAT("%",#{extentQueryParam},"%") or so.orderConsigneerealname like CONCAT("%",#{extentQueryParam},"%"))</if>
            <if test="orderStatus !=null and orderStatus != ''">and so.orderStatus = #{orderStatus}</if>
            <if test="orderStatus == null or orderStatus == ''">and so.orderStatus in ('1','2')</if>
            <if test="orderSenddateStart != null and orderSenddateStart != '' and orderSenddateEnd != null and orderSenddateEnd !=''">and so.orderSenddate between #{orderSenddateStart} and #{orderSenddateEnd}</if>
        </where>
        order by orderSenddate DESC
    </select>

    <select id="selectSpmOrderLogisticsSumInfo" parameterType="com.yunfeiyun.agmp.fms.domain.reqvo.SpmOrderLogisticsInfoReqVo" resultType="com.yunfeiyun.agmp.fms.domain.resvo.SpmOrderLogisticsSumInfoResVo">
        SELECT
        count(so.orderStatus) as number,so.orderStatus as type
        FROM
        SpmOrder so
        LEFT JOIN WmsStock ws ON ws.stockId = so.stockId
        LEFT JOIN WmsGoods wg on wg.goodsId = ws.goodsId
        LEFT JOIN WmsFinishgoods sfg ON sfg.goodsId = ws.goodsId
        LEFT JOIN TssSourceinfo ts ON ts.sourceinfoId = sfg.sourceinfoId
        LEFT JOIN (select * from SysRes where resBiztype = 'SOURCE_INFO_AVATAR') s ON s.resBusId = ts.sourceinfoId
        LEFT JOIN (select * from SysDict where dicttypeCode = 'agm_mpp_product') d on dictValue = sfg.finishgoodscode
        <where>
            <if test="extentQueryParam != null and extentQueryParam != ''" >and (d.dictName like CONCAT("%",#{extentQueryParam},"%") or so.orderNo like CONCAT("%",#{extentQueryParam},"%") or so.orderConsigneerealname like CONCAT("%",#{extentQueryParam},"%"))</if>
            <if test="orderSenddateStart != null and orderSenddateStart != '' and orderSenddateEnd != null and orderSenddateEnd !=''">and so.orderSenddate between #{orderSenddateStart} and #{orderSenddateEnd}</if>
        </where>
        GROUP BY so.orderStatus;
    </select>

    <select id="selectSpmOrderByOrderId" parameterType="String" resultMap="SpmOrderResult">
        <include refid="selectSpmOrderVo"/>
        where orderId = #{orderId}
    </select>

    <insert id="insertSpmOrder" parameterType="SpmOrder">
        insert into SpmOrder
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="orderId != null">orderId,</if>
            <if test="customerId != null">customerId,</if>
            <if test="stockId != null">stockId,</if>
            <if test="orderStatus != null">orderStatus,</if>
            <if test="orderGoodscount != null">orderGoodscount,</if>
            <if test="orderCreateddate != null">orderCreateddate,</if>
            <if test="orderSenddate != null">orderSenddate,</if>
            <if test="orderTracecodenum != null">orderTracecodenum,</if>
            <if test="orderSendtype != null">orderSendtype,</if>
            <if test="orderConsigneerealname != null">orderConsigneerealname,</if>
            <if test="orderConsigneetelphone != null">orderConsigneetelphone,</if>
            <if test="orderConsigneeaddress != null">orderConsigneeaddress,</if>
            <if test="orderConsigneeprovince != null">orderConsigneeprovince,</if>
            <if test="orderConsigneecity != null">orderConsigneecity,</if>
            <if test="orderConsigneedistrict != null">orderConsigneedistrict,</if>
            <if test="orderLogisticsplate != null">orderLogisticsplate,</if>
            <if test="orderLogisticsNo != null">orderLogisticsNo,</if>
            <if test="orderRemark != null">orderRemark,</if>
            <if test="orderSendtyperemark != null">orderSendtyperemark,</if>
            <if test="orderExpectedarrivaldate != null " >orderExpectedarrivaldate,</if>
            <if test="orderNo != null"> orderNo,</if>
            <if test="orderPrice != null"> orderPrice,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="orderId != null">#{orderId},</if>
            <if test="customerId != null">#{customerId},</if>
            <if test="stockId != null">#{stockId},</if>
            <if test="orderStatus != null">#{orderStatus},</if>
            <if test="orderGoodscount != null">#{orderGoodscount},</if>
            <if test="orderCreateddate != null">#{orderCreateddate},</if>
            <if test="orderSenddate != null">#{orderSenddate},</if>
            <if test="orderTracecodenum != null">#{orderTracecodenum},</if>
            <if test="orderSendtype != null">#{orderSendtype},</if>
            <if test="orderConsigneerealname != null">#{orderConsigneerealname},</if>
            <if test="orderConsigneetelphone != null">#{orderConsigneetelphone},</if>
            <if test="orderConsigneeaddress != null">#{orderConsigneeaddress},</if>
            <if test="orderConsigneeprovince != null">#{orderConsigneeprovince},</if>
            <if test="orderConsigneecity != null">#{orderConsigneecity},</if>
            <if test="orderConsigneedistrict != null">#{orderConsigneedistrict},</if>
            <if test="orderLogisticsplate != null">#{orderLogisticsplate},</if>
            <if test="orderLogisticsNo != null">#{orderLogisticsNo},</if>
            <if test="orderRemark != null">#{orderRemark},</if>
            <if test="orderSendtyperemark != null">#{orderSendtyperemark},</if>
            <if test="orderExpectedarrivaldate != null " >#{orderExpectedarrivaldate},</if>
            <if test="orderNo !=null">#{orderNo},</if>
            <if test="orderPrice !=null">#{orderPrice},</if>
         </trim>
    </insert>

    <update id="updateSpmOrder" parameterType="SpmOrder">
        update SpmOrder
        <trim prefix="SET" suffixOverrides=",">
            <if test="customerId != null">customerId = #{customerId},</if>
            <if test="stockId != null">stockId = #{stockId},</if>
            <if test="orderStatus != null">orderStatus = #{orderStatus},</if>
            <if test="orderGoodscount != null">orderGoodscount = #{orderGoodscount},</if>
            <if test="orderCreateddate != null">orderCreateddate = #{orderCreateddate},</if>
            <if test="orderSenddate != null">orderSenddate = #{orderSenddate},</if>
            <if test="orderTracecodenum != null">orderTracecodenum = #{orderTracecodenum},</if>
            <if test="orderSendtype != null">orderSendtype = #{orderSendtype},</if>
            <if test="orderConsigneerealname != null">orderConsigneerealname = #{orderConsigneerealname},</if>
            <if test="orderConsigneetelphone != null">orderConsigneetelphone = #{orderConsigneetelphone},</if>
            <if test="orderConsigneeaddress != null">orderConsigneeaddress = #{orderConsigneeaddress},</if>
            <if test="orderConsigneeprovince != null">orderConsigneeprovince = #{orderConsigneeprovince},</if>
            <if test="orderConsigneecity != null">orderConsigneecity = #{orderConsigneecity},</if>
            <if test="orderConsigneedistrict != null">orderConsigneedistrict = #{orderConsigneedistrict},</if>
            <if test="orderLogisticsplate != null">orderLogisticsplate = #{orderLogisticsplate},</if>
            <if test="orderLogisticsNo != null">orderLogisticsNo = #{orderLogisticsNo},</if>
            <if test="orderRemark != null">orderRemark = #{orderRemark},</if>
            <if test="orderSendtyperemark != null">orderSendtyperemark = #{orderSendtyperemark},</if>
            <if test="orderExpectedarrivaldate != null and orderExpectedarrivaldate != ''" >orderExpectedarrivaldate = #{orderExpectedarrivaldate},</if>
            <if test="orderNo !=null and orderNo != ''">orderNo = #{orderNo},</if>
            <if test="logisticsExpense !=null and logisticsExpense != ''">logisticsExpense = #{logisticsExpense}</if>
            <if test="orderPrice !=null and orderPrice != ''">orderPrice = #{orderPrice}</if>
        </trim>
        where orderId = #{orderId}
    </update>

    <update id="updateSpmOrderByIds">
        update SpmOrder
        <trim prefix="SET" suffixOverrides=",">
            <if test="spmOrder.customerId != null">customerId = #{spmOrder.customerId},</if>
            <if test="spmOrder.stockId != null">stockId = #{spmOrder.stockId},</if>
            <if test="spmOrder.orderStatus != null">orderStatus = #{spmOrder.orderStatus},</if>
            <if test="spmOrder.orderGoodscount != null">orderGoodscount = #{spmOrder.orderGoodscount},</if>
            <if test="spmOrder.orderCreateddate != null">orderCreateddate = #{spmOrder.orderCreateddate},</if>
            <if test="spmOrder.orderSenddate != null">orderSenddate = #{spmOrder.orderSenddate},</if>
            <if test="spmOrder.orderTracecodenum != null">orderTracecodenum = #{spmOrder.orderTracecodenum},</if>
            <if test="spmOrder.orderSendtype != null">orderSendtype = #{spmOrder.orderSendtype},</if>
            <if test="spmOrder.orderConsigneerealname != null">orderConsigneerealname = #{spmOrder.orderConsigneerealname},</if>
            <if test="spmOrder.orderConsigneetelphone != null">orderConsigneetelphone = #{spmOrder.orderConsigneetelphone},</if>
            <if test="spmOrder.orderConsigneeaddress != null">orderConsigneeaddress = #{spmOrder.orderConsigneeaddress},</if>
            <if test="spmOrder.orderConsigneeprovince != null">orderConsigneeprovince = #{spmOrder.orderConsigneeprovince},</if>
            <if test="spmOrder.orderConsigneecity != null">orderConsigneecity = #{spmOrder.orderConsigneecity},</if>
            <if test="spmOrder.orderConsigneedistrict != null">orderConsigneedistrict = #{spmOrder.orderConsigneedistrict},</if>
            <if test="spmOrder.orderLogisticsplate != null">orderLogisticsplate = #{spmOrder.orderLogisticsplate},</if>
            <if test="spmOrder.orderLogisticsNo != null">orderLogisticsNo = #{spmOrder.orderLogisticsNo},</if>
            <if test="spmOrder.orderRemark != null">orderRemark = #{spmOrder.orderRemark},</if>
            <if test="spmOrder.orderSendtyperemark != null">orderSendtyperemark = #{spmOrder.orderSendtyperemark},</if>
            <if test="spmOrder.orderExpectedarrivaldate != null and spmOrder.orderExpectedarrivaldate != ''" >orderExpectedarrivaldate = #{spmOrder.orderExpectedarrivaldate},</if>
            <if test="spmOrder.orderNo !=null and spmOrder.orderNo != ''">orderNo = #{spmOrder.orderNo},</if>
            <if test="spmOrder.orderPrice !=null and spmOrder.orderPrice != ''">orderPrice = #{spmOrder.orderPrice},</if>
        </trim>
        where orderId in
        <foreach item="orderId" collection="ids" open="(" separator="," close=")">
           #{orderId}
        </foreach>
    </update>

    <delete id="deleteSpmOrderByOrderId" parameterType="String">
        delete from SpmOrder where orderId = #{orderId}
    </delete>

    <delete id="deleteSpmOrderByOrderIds" parameterType="String">
        delete from SpmOrder where orderId in 
        <foreach item="orderId" collection="array" open="(" separator="," close=")">
            #{orderId}
        </foreach>
    </delete>

    <select id="selectSpmOrderTotalSealNumber" parameterType="string" resultType="java.math.BigDecimal">
        select SUM(orderGoodscount) from SpmOrder where stockId = #{stockId} and orderStatus = '0'
    </select>
    
    <!-- 溯源统计页面使用：产品销售地（气泡图） -->
    <select id="selectMoneyGroupByCity" parameterType="SelectOfTssStatReqVo" resultType="SpmOrderStatResVo">
        SELECT tb.sum, tb.count, tb.orderConsigneecity, tb.orderConsigneeprovince, z.zoneLatitude, z.zoneLongitude
        FROM
        (
        SELECT IFNULL(sum(o.orderPrice),0) sum, count(1) count, o.orderConsigneecity, o.orderConsigneeprovince
        FROM SpmOrder o
        LEFT JOIN WmsStock s ON s.stockId = o.stockId
        LEFT JOIN WmsGoods g ON g.goodsId = s.goodsId
        LEFT JOIN WmsFinishgoods fg ON fg.goodsId = s.goodsId
        where o.orderConsigneeprovince is not null and o.orderConsigneecity is not null
        and left(o.orderCreateddate,4)=#{startTime}
        and o.orderStatus in ('0','1','2')
        <if test="landId != null  and landId != ''"> and fg.landId = #{landId}</if>
        GROUP BY o.orderConsigneecity, o.orderConsigneeprovince
        ) tb
        LEFT JOIN SysZone z ON z.zoneProvincezh = tb.orderConsigneeprovince and z.zoneCapitalzh = tb.orderConsigneecity
        and z.zoneCityzh = tb.orderConsigneecity
        order by sum desc
    </select>
    
    <!-- 溯源统计页面使用：支出/投入明细列表（分页）-物流费用 -->
    <select id="selectSpmOrderPayOfTssStatList" parameterType="SelectOfTssStatReqVo" resultType="SpmPayOfTssStatResVo">
        SELECT
        so.logisticsExpense money,
        so.orderCreateddate time,
        '物流费用' typeName
        FROM
        SpmOrder so
        LEFT JOIN WmsStock ws ON ws.stockId = so.stockId
        LEFT JOIn WmsGoods wg on wg.goodsId = ws.goodsId
        LEFT JOIN WmsFinishgoods wf ON wf.goodsId = ws.goodsId
        where left(so.orderCreateddate,4) = #{startTime} and so.logisticsExpense>0
        and so.orderStatus in ('0','1','2')
        <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
        order by so.orderCreateddate desc
    </select>
    
    <!-- 溯源统计页面使用：销售明细列表数据（分页） -->
    <select id="selectSpmOrderOfTssStatList" parameterType="SelectOfTssStatReqVo" resultType="SpmOrderOfTssStatResVo">
        SELECT
        so.orderPrice,
        so.orderId,
        so.orderNo,
        so.orderStatus,
        wf.finishgoodscode,
        so.orderGoodscount,
        wg.goodsUnit,
        wg.goodsName,
        so.orderConsigneerealname,
        so.orderConsigneetelphone,
        so.orderConsigneeaddress,
        so.orderSendtype,
        so.orderCreateddate,
        so.orderSenddate,
        so.orderExpectedarrivaldate
        FROM
        SpmOrder so
        LEFT JOIN WmsStock ws ON ws.stockId = so.stockId
        LEFT JOIn WmsGoods wg on wg.goodsId = ws.goodsId
        LEFT JOIN WmsFinishgoods wf ON wf.goodsId = ws.goodsId
        where left(so.orderCreateddate,4)=#{startTime}
        and so.orderStatus in ('0','1','2')
        <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
        order by so.orderCreateddate desc
    </select>
    
    <!-- 溯源统计页面使用：年度每月收益，物流支出 -->
    <select id="selectMoneyByMonthOfTssTrace" parameterType="SelectOfTssStatReqVo" resultType="map">
        SELECT left(so.orderCreateddate,7) month
        ,IFNULL(sum(so.orderPrice),0) incomeSum
        ,IFNULL(sum(so.logisticsExpense),0) wlSum
        FROM SpmOrder so
        LEFT JOIN WmsStock ws ON ws.stockId = so.stockId
        LEFT JOIn WmsGoods wg on wg.goodsId = ws.goodsId
        LEFT JOIN WmsFinishgoods wf ON wf.goodsId = ws.goodsId
        where left(so.orderCreateddate,4)=#{startTime}
        and so.orderStatus in ('0','1','2')
        <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
        GROUP BY left(so.orderCreateddate,7)
        order by so.orderCreateddate 
    </select>
    
    <!-- 溯源统计页面使用：概要信息-总收入，物流支出 -->
    <select id="selectMoneyOfTssTrace" parameterType="SelectOfTssStatReqVo" resultType="map">
        SELECT IFNULL(sum(so.orderPrice),0) incomeSum
        ,IFNULL(sum(so.logisticsExpense),0) wlSum
        FROM SpmOrder so
        LEFT JOIN WmsStock ws ON ws.stockId = so.stockId
        LEFT JOIn WmsGoods wg on wg.goodsId = ws.goodsId
        LEFT JOIN WmsFinishgoods wf ON wf.goodsId = ws.goodsId
        where left(so.orderCreateddate,4)=#{startTime}
        and so.orderStatus in ('0','1','2')
        <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
    </select>
    
    <!-- 溯源统计页面使用：产品销售金额排行 -->
    <select id="selectSpmOrderMoneyByTssStat" parameterType="SelectOfTssStatReqVo" resultType="SpmOrderStatResVo">
        SELECT IFNULL(sum(so.orderPrice),0) sum, wg.goodsName
        FROM SpmOrder so
        LEFT JOIN WmsStock ws ON ws.stockId = so.stockId
        LEFT JOIn WmsGoods wg on wg.goodsId = ws.goodsId
        LEFT JOIN WmsFinishgoods wf ON wf.goodsId = ws.goodsId
        where left(so.orderCreateddate,4)=#{startTime}
        and so.orderStatus in ('0','1','2')
        <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
        GROUP BY wg.goodsName
        HAVING sum > 0
        ORDER BY sum desc
        limit 10
    </select>
    
    <!-- 溯源统计页面使用：成本占比（农产品费用） -->
    <select id="selectFinishgoodsMoneyOfTssTrace" parameterType="SelectOfTssStatReqVo" resultType="map">
        SELECT IFNULL(sum(wf.finishgoodsExpense),0) sum,wf.finishgoodsType
        FROM WmsFinishgoods wf
        WHERE wf.finishgoodsType is not null
        and left(wf.finishgoodCreateddate,4)=#{startTime}
        <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
        GROUP BY  wf.finishgoodsType
    </select>
    
    <!-- 溯源统计页面使用：成本占比-明细（农产品费用-采收） -->
    <select id="selectPayByCs" parameterType="SelectOfTssStatReqVo" resultType="SpmPayOfTssStatResVo">
        SELECT wf.finishgoodsExpense money,
        wf.finishgoodCreateddate time,
        '采收费用' typeName
        FROM WmsFinishgoods wf
        where left(wf.finishgoodCreateddate,4)=#{startTime}
      	and wf.finishgoodsType='0'
        <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
        order by wf.finishgoodCreateddate desc
    </select>
    
     <!-- 溯源统计页面使用：成本占比-明细（农产品费用-加工） -->
    <select id="selectPayByJg" parameterType="SelectOfTssStatReqVo" resultType="SpmPayOfTssStatResVo">
        SELECT wf.finishgoodsExpense money,
        wf.finishgoodCreateddate time,
        '加工费用' typeName
        FROM WmsFinishgoods wf
        where left(wf.finishgoodCreateddate,4)=#{startTime} 
      	and wf.finishgoodsType='1'
        <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
        order by wf.finishgoodCreateddate desc
    </select>
    
     <!-- 溯源统计页面使用：成本占比-明细（农产品费用-包装） -->
    <select id="selectPayByBz" parameterType="SelectOfTssStatReqVo" resultType="SpmPayOfTssStatResVo">
        SELECT wf.finishgoodsExpense money,
        wf.finishgoodCreateddate time,
        '包装费用' typeName
        FROM WmsFinishgoods wf
        where left(wf.finishgoodCreateddate,4)=#{startTime} 
      	and wf.finishgoodsType='2'
        <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
        order by wf.finishgoodCreateddate desc
    </select>
    
    <!-- 溯源统计页面使用：年度分析-每月（农产品费用-按类型） -->
    <select id="selectPayMonthByType" parameterType="SelectOfTssStatReqVo" resultType="map">
        SELECT left(wf.finishgoodCreateddate,7) month
        ,IFNULL(sum(wf.finishgoodsExpense),0) money
        FROM WmsFinishgoods wf
        where wf.finishgoodsType=#{payMxType} 
        and left(wf.finishgoodCreateddate,4)=#{startTime}
        <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
        GROUP BY left(wf.finishgoodCreateddate,7)
        order by wf.finishgoodCreateddate desc
    </select>
    
    <!-- 溯源统计页面使用：概要信息-地块数量 -->
    <select id="selectDkCountOfTssTrace" parameterType="SelectOfTssStatReqVo" resultType="Integer">
    	select count(1) num from (
	        select fb.blockId from FmsBlock fb where fb.landId in (
		        SELECT wf.landId
		        FROM WmsFinishgoods wf
		        where left(wf.finishgoodCreateddate,4)=#{startTime}
	            <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
	        	GROUP BY wf.landId
	        )  group by fb.blockId
        ) a
    </select>
    
    <!-- 溯源统计页面使用：概要信息-产品种类数量 -->
    <select id="selectGoodsTypeCountOfTssTrace" parameterType="SelectOfTssStatReqVo" resultType="Integer">
         select count(1) num from (
	        SELECT wf.finishgoodsType
	        FROM WmsFinishgoods wf
	        where left(wf.finishgoodCreateddate,4)=#{startTime}
            <if test="landId != null  and landId != ''"> and wf.landId = #{landId}</if>
        	GROUP BY wf.finishgoodsType
        ) a
    </select>
    
</mapper>