<?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="org.springblade.modules.app.mapper.StatReportMapper">


    <select id="statHeatMapTop" resultType="org.springblade.modules.app.common.dto.StatHeatMapTopDTO">
        select
        (
            select if(sum(wb.plan_num) is null,0,sum(wb.plan_num)) from tb_waybill wb
            left join tb_user u on u.id = wb.buyer_user_id
            left join tb_user u2 on u2.id = wb.saler_user_id
            where wb.state = 3 and SUBSTRING_INDEX(wb.saler_user_address,'/',1) not in ("山东省","河南省","安徽省","江苏省","浙江省","上海")
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.finish_time between #{query.startTime} and #{query.endTime}
            </if>
        )as outOfAreaQuantity,
        (
            select if(sum(wb.plan_num) is null,0,sum(wb.plan_num)) from tb_waybill wb
            left join tb_user u on u.id = wb.buyer_user_id
            left join tb_user u2 on u2.id = wb.saler_user_id
            where wb.state = 3 and SUBSTRING_INDEX(wb.saler_user_address,'/',1) in ("山东省","河南省","安徽省","江苏省","浙江省","上海")
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.finish_time between #{query.startTime} and #{query.endTime}
            </if>
        )as withinAreaQuantity,
        (
            select count(DISTINCT wb.agent_user_id) from tb_waybill wb
            left join tb_user u on u.id = wb.buyer_user_id
            where u.prov_id = 33 and wb.state = 3 and wb.agent_user_id > 0
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.finish_time between #{query.startTime} and #{query.endTime}
            </if>
        ) as agentUserQuantity,
        (
            select count(DISTINCT wb.slaughter_house_id) from tb_waybill wb
            left join tb_user u on u.id = wb.buyer_user_id
            where wb.state = 3 and wb.slaughter_house_id > 0
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.finish_time between #{query.startTime} and #{query.endTime}
            </if>
        )as slaughterHouseQuantity
    </select>

    <select id="statHeatMapMid" resultType="org.springblade.modules.app.common.dto.StatHeatMapMidDTO">
        select
            if(sum(wb.plan_num) is null,0,sum(wb.plan_num)) as quantity,
            u2.prov_id as provId,
            SUBSTRING_INDEX(wb.saler_user_address,'/',1) as provName
        from tb_waybill wb
            left join tb_user u on u.id = wb.buyer_user_id
            left join tb_user u2 on u2.id = wb.saler_user_id
        where
            wb.state = 3
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.finish_time between #{query.startTime} and #{query.endTime}
            </if>
        group by provName
        limit #{query.limit}
    </select>

    <select id="StatVerifyStationOrderNum" resultType="org.springblade.modules.app.entity.VerifyStationDTO">
        select tb_verify_station.name ,
               if(count(tb_waybill.id) is null,0,count(tb_waybill.id)) as num
        from tb_verify_station
        left join tb_waybill on tb_waybill.verify_id = tb_verify_station.id

        where
            tb_waybill.state = 3
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and tb_waybill.finish_time between #{query.startTime} and #{query.endTime}
            </if>
        group by tb_verify_station.id
        limit #{query.limit}
    </select>

    <select id="statHeatMapFoot" resultType="org.springblade.modules.app.common.dto.StatHeatMapFootDTO">
        select
        (
            select count(id) from tb_waybill
            where
            state = 1
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and created_time between #{query.startTime} and #{query.endTime}
            </if>
        ) as prepQuantity,
        (
            select count(wb.id) from tb_waybill wb
            left join tb_transport o on wb.id=o.waybill_id
            where
            wb.state = 2
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.confirm_time between #{query.startTime} and #{query.endTime}
            </if>
        ) as wayQuantity,
        (
            select count(wb.id) from tb_waybill wb
            left join tb_transport o on wb.id=o.waybill_id
            where
            wb.state = 3
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.finish_time between #{query.startTime} and #{query.endTime}
            </if>
        ) as finishQuantity
    </select>

    <select id="prepList" resultType="org.springblade.modules.app.common.dto.StatWaybillDTO">
        select
            wb.bill_code as billCode,
            wb.plan_num as planNum,
            wb.plan_time as planTime,
            wb.buyer_user_id as buyerUserId,
            wb.buyer_user_name as buyerUserName,
            wb.saler_user_id as salerUserId,
            wb.saler_user_name as salerUserName,
            wb.verify_id as verifyId,
            wb.verify_name as verifyName,
            wb.created_time as createdTime
        from tb_waybill wb
        where
            wb.state = 1
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.created_time between #{query.startTime} and #{query.endTime}
            </if>
        order by wb.created_time desc
        limit #{query.limit}
    </select>

    <select id="wayList" resultType="org.springblade.modules.app.common.dto.StatWaybillDTO">
        select
            wb.bill_code as billCode,
            wb.plan_num as planNum,
            wb.plan_time as planTime,
            wb.buyer_user_id as buyerUserId,
            wb.buyer_user_name as buyerUserName,
            wb.saler_user_id as salerUserId,
            wb.saler_user_name as salerUserName,
            wb.verify_id as verifyId,
            wb.verify_name as verifyName,
            wb.created_time as createdTime
        from tb_waybill wb
            left join tb_transport o on wb.id=o.waybill_id
        where
            wb.state = 2
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.confirm_time between #{query.startTime} and #{query.endTime}
            </if>
        order by wb.created_time desc
        limit #{query.limit}
    </select>

    <select id="finishList" resultType="org.springblade.modules.app.common.dto.StatWaybillDTO">
        select
            wb.bill_code as billCode,
            wb.plan_num as planNum,
            wb.plan_time as planTime,
            wb.buyer_user_id as buyerUserId,
            wb.buyer_user_name as buyerUserName,
            wb.saler_user_id as salerUserId,
            wb.saler_user_name as salerUserName,
            wb.verify_id as verifyId,
            wb.verify_name as verifyName,
            wb.created_time as createdTime
        from tb_waybill wb
            left join tb_transport o on wb.id=o.waybill_id
        where
            wb.state = 3
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.finish_time between #{query.startTime} and #{query.endTime}
            </if>
        order by wb.created_time desc
        limit #{query.limit}
    </select>

    <select id="statBySaler" resultType="org.springblade.modules.app.common.dto.StatBySalerDTO">
        select
            u2.id as salerUserId,
            wb.saler_user_name as salerUserName,
            if(sum(wb.plan_num) is null,0,sum(wb.plan_num)) as quantity
        from
            tb_waybill wb
            left join tb_user u2 on u2.zmt_user_id = wb.saler_user_id
        where
             wb.state = 3
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.finish_time between #{query.startTime} and #{query.endTime}
            </if>
        group by u2.id
        order by quantity desc
        limit #{query.limit}
    </select>

    <select id="statByAgent" resultType="org.springblade.modules.app.common.dto.StatByAgentUserDTO">
        select
            wb.agent_user_id as agentUserId,
				u3.user_name as agentName,
				if(sum(wb.plan_num) is null,0,sum(wb.plan_num)) as quantity,
				count(distinct wb.buyer_user_id) as buyerQuantity,
        count(distinct wb.saler_user_id) as salerQuantity
        from tb_waybill wb
            left join tb_user u on u.id = wb.buyer_user_id
    left join tb_user u3 on u3.id = wb.agent_user_id
        where
            wb.state = 3
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.finish_time between #{query.startTime} and #{query.endTime}
            </if>
            and wb.agent_user_id > 0
        group by wb.agent_user_id
        order by quantity desc
        limit #{query.limit}
    </select>

    <select id="statExpOrder" resultType="org.springblade.modules.app.common.dto.StatExpOrderDTO">
        select
            wb.out_trade_no as billCode,
            u.id as buyerUserId,
            u.user_name as buyerUserName,
            o.exp_type as expType
        from tb_waybill wb
            left join tb_transport o on wb.id=o.waybill_id
            left join tb_user u on u.id = wb.buyer_user_id
        where
            o.exp_type > 0
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and o.finish_time between #{query.startTime} and #{query.endTime}
            </if>
        order by o.finish_time  desc
        limit #{query.limit}
    </select>

    <select id="statViolation" resultType="org.springblade.modules.app.common.dto.StatViolationDTO">
        select
            count(w.id) as violations,
            u.id as driverUserId,
            u.user_name as driverUserName,
            w.exp_type as expType
        from tb_transport_stage_info w
            left join tb_transport o on o.id=w.transport_id
            left join tb_user u on u.id = o.driver_user_id
        where
            w.exp_type > 0
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and w.created_time between #{query.startTime} and #{query.endTime}
            </if>
        group by w.exp_type,u.id
        order by w.created_time  desc
        limit #{query.limit}
    </select>

    <select id="statByBuyer" resultType="org.springblade.modules.app.common.dto.StatByBuyerDTO">
        select
        u.id as buyerUserId,
        u.user_name as buyerUserName,
        if(sum(wb.plan_num) is null,0,sum(wb.plan_num)) as quantity,
        count(distinct wb.saler_user_id) as salerQuantity
        from tb_waybill wb

            left join tb_user u on u.id = wb.buyer_user_id
        where
           wb.state = 3
            <if test="query.startTime!=null and query.startTime.trim()!=''">
                and wb.finish_time between #{query.startTime} and #{query.endTime}
            </if>
        group by wb.buyer_user_id
        order by quantity desc
        limit #{query.limit}
    </select>

    <select id="statByBuyerHistory" resultType="org.springblade.modules.app.common.dto.StatByBuyerDTO">
        select
        u.id as buyerUserId,
        if(sum(pic.accept_num) is null,0,sum(pic.accept_num)) as quantity
        from tb_picture_info pic
        left join tb_transport_stage_info w on w.id=pic.link_id
        left join tb_transport o on o.id=w.transport_id
        left join tb_waybill wb on wb.id=o.waybill_id
        left join tb_user u on u.id = wb.buyer_user_id
        where
        pic.link_table='tb_transport_stage_info'
        and pic.type = 6
--         and u.prov_id = 33
        and o.state = 3
        <if test="query.startTime!=null and query.startTime.trim()!=''">
            and o.finish_time between #{query.startTime} and #{query.endTime}
        </if>
        and wb.buyer_user_id in
        <foreach collection="list" item="buyerUserId" index="index" open="(" close=")" separator=",">
            #{buyerUserId}
        </foreach>
        group by wb.buyer_user_id
    </select>

</mapper>
