<?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.zfire.mall.manager.commonmapper.trade.LoginOrderMapper">

    <select id="workOrderList" resultType="com.zfire.mall.manager.bean.trade.WorkLoginOrderVO">
        SELECT
        a.*
        FROM
        trade_login_order a
        ${ex.query}
        AND a.del = 0
        AND (
                (a.is_span = 0 AND a.order_status IN ('WAIT', 'OK', 'FAIL', 'RETURN') AND a.order_type = 'WORK')
                OR
                (a.admin_user_id = #{adminUserId} AND a.is_span = 0 AND a.order_status = 'SAVE' AND a.order_type = 'WORK')
            )
        <if test="ex.orderBy == null or ex.orderBy ==''">
            ORDER BY a.is_apply_update DESC, a.create_time DESC
        </if>
        ${ex.orderBy}

    </select>

    <select id="homeOrderList" resultType="com.zfire.mall.manager.bean.trade.HomeLoginOrderVO">
        SELECT
        a.*
        FROM
        trade_login_order a
        ${ex.query}
        AND a.del = 0
        AND (
                (a.is_span = 0 AND a.order_status IN ('WAIT', 'OK', 'FAIL', 'RETURN') AND a.order_type = 'HOME')
                OR
                (a.admin_user_id = #{adminUserId} AND a.is_span = 0 AND a.order_status = 'SAVE' AND a.order_type = 'HOME')
            )
        <if test="ex.orderBy == null or ex.orderBy ==''">
            ORDER BY a.is_apply_update DESC, a.create_time DESC
        </if>
        ${ex.orderBy}
    </select>

    <select id="spanOrderList" resultType="com.zfire.mall.manager.bean.trade.SpanLoginOrderVO">
        SELECT
        a.*
        FROM
        trade_login_order a
        ${ex.query}
        AND a.del = 0
        AND (
                (a.is_span = 1 AND a.order_status IN ('WAIT', 'OK', 'FAIL', 'RETURN'))
                OR
                (a.admin_user_id = #{adminUserId} AND a.is_span = 1 AND a.order_status = 'SAVE')
            )
        <if test="ex.orderBy == null or ex.orderBy ==''">
            ORDER BY a.is_apply_update DESC, a.create_time DESC
        </if>
        ${ex.orderBy}
    </select>

    <select id="queryPositionProject" resultType="com.zfire.mall.manager.bean.trade.PositionProjectBean">
        SELECT * FROM (
            SELECT
            a.project_name,a.lat,a.lnt,a.project_no,a.address,
            <!-- 按千米距离排序计算 -->
            ROUND(
            6378.138 * 2 * ASIN(
            SQRT(
            POW( SIN( ( #{lat} * PI() / 180 - lat * PI() / 180 ) / 2 ), 2 ) + COS( #{lat} * PI() / 180 ) * COS( lat * PI() / 180 ) * POW( SIN( ( #{lnt} * PI() / 180 - lnt * PI() / 180 ) / 2 ), 2 )
            )
            )
            , 2) AS distance

            FROM trade_login_order a
            WHERE
                a.id <![CDATA[ <> ]]> #{id}
                AND a.status = 'ING'
                AND a.login_status = 'OK'
                AND a.del = 0
            ORDER BY distance,a.id
        ) a WHERE a.distance <![CDATA[ < ]]> #{val}
    </select>
    <select id="orderCount" resultType="com.zfire.mall.manager.bean.trade.OrderTypeCountBean">
        SELECT IFNULL(SUM(IF(order_type = 'WORK' AND is_span = 0, 1, 0)), 0) AS work_count,
               IFNULL(SUM(IF(order_type = 'HOME' AND is_span = 0, 1, 0)), 0)  AS home_count,
               IFNULL(SUM(IF(is_span = 1, 1, 0)), 0)                          AS span_count
        FROM trade_login_order
        WHERE customer_id = #{customerId}
          AND `status` = 'ING'
          AND order_status IN ('WAIT', 'OK')
          AND del = 0
    </select>
    <select id="dataListPage" resultType="com.zfire.mall.manager.bean.trade.DataLoginOrderVO">
        SELECT
            customer_name,
            SUM(IF( order_type = 'HOME' AND is_span = 0 AND order_status IN ('WAIT','OK','FAIL','RETURN'), 1, 0 )) AS home_count,
            SUM(IF( order_type = 'HOME' AND is_span = 0 AND order_status = 'OK', 1, 0 )) AS home_success_count,
            SUM(IF( order_type = 'HOME' AND is_span = 0 AND order_status = 'OK' AND `status` = 'SIGN', 1, 0 )) AS home_sign_count,
            SUM(IF( order_type = 'HOME' AND is_span = 0 AND order_status = 'OK' AND `status` = 'LOSS', 1, 0 )) AS home_loss_count,
            SUM(IF( order_type = 'WORK' AND is_span = 0 AND order_status IN ('WAIT','OK','FAIL','RETURN'), 1, 0 )) AS work_count,
            SUM(IF( order_type = 'WORK' AND is_span = 0 AND order_status = 'OK', 1, 0 )) AS work_success_count,
            SUM(IF( order_type = 'WORK' AND is_span = 0 AND order_status = 'OK' AND `status` = 'SIGN', 1, 0 )) AS work_sign_count,
            SUM(IF( order_type = 'WORK' AND is_span = 0 AND order_status = 'OK' AND `status` = 'LOSS', 1, 0 )) AS work_loss_count,
            SUM(IF( is_span = 1 AND order_status IN ('WAIT','OK','FAIL','RETURN'), 1, 0 )) AS span_count,
            SUM(IF( is_span = 1 AND order_status = 'OK', 1, 0 )) AS span_success_count,
            SUM(IF( is_span = 1 AND order_status = 'OK' AND `status` = 'SIGN', 1, 0 )) AS span_sign_count,
            SUM(IF( is_span = 1 AND order_status = 'OK' AND `status` = 'LOSS', 1, 0 )) AS span_loss_count
        FROM
        trade_login_order
        ${ex.query}
        AND del = 0
        GROUP BY customer_id
        ${ex.orderBy}
    </select>
    <select id="list" resultType="com.zfire.mall.manager.bean.trade.LoginOrderVO">
        SELECT
        a.*
        FROM
        trade_login_order a
        ${ex.query}
        AND a.order_status IN ('WAIT','OK','FAIL','RETURN')
        AND a.del = 0
        <if test="ex.orderBy == null or ex.orderBy ==''">
            ORDER BY a.create_time DESC
        </if>
        ${ex.orderBy}
    </select>

    <select id="syncLoginOrderRefCustomerId" resultType="com.zfire.mall.manager.plus.entity.TradeLoginOrder">
        SELECT *
        FROM trade_login_order
        WHERE project_no = #{projectNo}
            AND is_span = #{isSpan}
            AND del = 0
        FOR UPDATE
    </select>

    <select id="recycleListPage" resultType="com.zfire.mall.manager.bean.trade.LoginOrderVO">
        SELECT
        a.*
        FROM
        trade_login_order a
        ${ex.query}
        AND a.del = 1
        <if test="ex.orderBy == null or ex.orderBy ==''">
            ORDER BY a.create_time DESC
        </if>
        ${ex.orderBy}
    </select>

    <insert id="batchInsertLogin">
        <if test="ids != null and ids.size > 0">
        INSERT INTO trade_login_order_bak SELECT * FROM trade_login_order WHERE
            id IN
            <foreach item="item" index="index" collection="ids" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
    </insert>

    <delete id="recycleDel">
        <if test="ids != null and ids.size > 0">
        DELETE FROM trade_login_order WHERE
            id IN
            <foreach item="item" index="index" collection="ids" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
    </delete>

    <update id="recycleRestore">
        <if test="ids != null and ids.size > 0">
        UPDATE trade_login_order SET del = 0 WHERE
            id IN
            <foreach item="item" index="index" collection="ids" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
    </update>
</mapper>