<?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.ruoyi.mall.mapper.MallSalesmanAchievementMapper">

    <resultMap type="MallSalesmanAchievement" id="MallSalesmanAchievementResult">
        <result property="achievementId"    column="achievement_id"    />
        <result property="salesmanId"    column="salesman_id"    />
        <result property="salesmanName"    column="salesman_name"    />
        <result property="price"    column="price"    />
        <result property="money"    column="money"    />
        <result property="status"    column="status"    />
        <result property="linkOrderId"    column="link_order_id"    />
        <result property="linkOrderNo"    column="link_order_no"    />
        <result property="orderStatus"    column="order_status"    />
        <result property="deliveryType"    column="delivery_type"    />
        <result property="linkUserId"    column="link_user_id"    />
        <result property="linkUserName"    column="link_user_name"    />
        <result property="createAt"    column="create_at"    />
        <result property="updateAt"    column="update_at"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="isGive"    column="is_give"    />
    </resultMap>

    <sql id="selectMallSalesmanAchievementVo">
        select achievement_id, salesman_id, salesman_name, price, money, status, link_order_id, link_order_no, order_status, delivery_type, link_user_id, link_user_name, create_at, update_at, del_flag, is_give from mall_salesman_achievement
    </sql>

    <select id="selectMallSalesmanAchievementList" parameterType="MallSalesmanAchievement" resultMap="MallSalesmanAchievementResult">
        select msa.achievement_id, msa.salesman_id, ms.name salesman_name, msa.price, msa.status,
               msa.link_order_id, msa.link_order_no, msa.order_status, msa.delivery_type, msa.link_user_id, mmu.name link_user_name,
               msa.create_at, msa.update_at, msa.del_flag, msa.is_give
        ,case
        when msa.is_give = 1 then 0
        else msa.money
        end money
        from mall_salesman_achievement msa
        left join mall_salesman ms on msa.salesman_id = ms.id
        left join mall_member_user mmu on msa.link_user_id = mmu.id
        left join mall_order_order moo on moo.order_no = msa.link_order_no
        <where>
            <if test="salesmanId != null "> and msa.salesman_id = #{salesmanId}</if>
            <if test="salesmanName != null  and salesmanName != ''"> and ms.name like concat('%', #{salesmanName}, '%')</if>
            <if test="price != null "> and msa.price = #{price}</if>
            <if test="money != null "> and msa.money = #{money}</if>
            <if test="status != null "> and msa.status = #{status}</if>
            <if test="linkOrderId != null "> and msa.link_order_id = #{linkOrderId}</if>
            <if test="linkOrderNo != null  and linkOrderNo != ''"> and msa.link_order_no like concat('%', #{linkOrderNo}, '%')</if>
            <if test="orderStatus != null  and orderStatus != ''"> and msa.order_status = #{orderStatus}</if>
            <if test="deliveryType != null "> and msa.delivery_type = #{deliveryType}</if>
            <if test="linkUserId != null "> and msa.link_user_id = #{linkUserId}</if>
            <if test="linkUserName != null  and linkUserName != ''"> and mmu.name like concat('%', #{linkUserName}, '%')</if>
            <if test="params.beginAchievementTime != null and params.beginAchievementTime != ''">
                AND DATE_FORMAT(msa.create_at,'%Y-%m-%d') &gt;= #{params.beginAchievementTime}
            </if>
            <if test="params.endAchievementTime != null and params.endAchievementTime != ''">
                AND DATE_FORMAT(msa.create_at,'%Y-%m-%d') &lt;= #{params.endAchievementTime}
            </if>
            <if test="updateAt != null  and updateAt != ''"> and msa.update_at = #{updateAt}</if>
            <if test="shopId != null "> and ms.shop_id = #{shopId}</if>
            and moo.status not in ('0', '8')
            and msa.del_flag = '0'
        </where>
        order by msa.create_at desc
    </select>

    <select id="selectMallSalesmanAchievementByAchievementId" parameterType="Long" resultMap="MallSalesmanAchievementResult">
        <include refid="selectMallSalesmanAchievementVo"/>
        where achievement_id = #{achievementId}
    </select>

    <insert id="insertMallSalesmanAchievement" parameterType="MallSalesmanAchievement" useGeneratedKeys="true" keyProperty="achievementId">
        insert into mall_salesman_achievement
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="salesmanId != null">salesman_id,</if>
            <if test="salesmanName != null">salesman_name,</if>
            <if test="price != null">price,</if>
            <if test="money != null">money,</if>
            <if test="status != null">status,</if>
            <if test="linkOrderId != null">link_order_id,</if>
            <if test="linkOrderNo != null">link_order_no,</if>
            <if test="orderStatus != null">order_status,</if>
            <if test="deliveryType != null">delivery_type,</if>
            <if test="linkUserId != null">link_user_id,</if>
            <if test="linkUserName != null">link_user_name,</if>
            <if test="createAt != null">create_at,</if>
            <if test="updateAt != null">update_at,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="isGive != null">is_give,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="salesmanId != null">#{salesmanId},</if>
            <if test="salesmanName != null">#{salesmanName},</if>
            <if test="price != null">#{price},</if>
            <if test="money != null">#{money},</if>
            <if test="status != null">#{status},</if>
            <if test="linkOrderId != null">#{linkOrderId},</if>
            <if test="linkOrderNo != null">#{linkOrderNo},</if>
            <if test="orderStatus != null">#{orderStatus},</if>
            <if test="deliveryType != null">#{deliveryType},</if>
            <if test="linkUserId != null">#{linkUserId},</if>
            <if test="linkUserName != null">#{linkUserName},</if>
            <if test="createAt != null">#{createAt},</if>
            <if test="updateAt != null">#{updateAt},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="isGive != null">#{isGive},</if>
         </trim>
    </insert>

    <update id="updateMallSalesmanAchievement" parameterType="MallSalesmanAchievement">
        update mall_salesman_achievement
        <trim prefix="SET" suffixOverrides=",">
            <if test="salesmanId != null">salesman_id = #{salesmanId},</if>
            <if test="salesmanName != null">salesman_name = #{salesmanName},</if>
            <if test="price != null">price = #{price},</if>
            <if test="money != null">money = #{money},</if>
            <if test="status != null">status = #{status},</if>
            <if test="linkOrderId != null">link_order_id = #{linkOrderId},</if>
            <if test="linkOrderNo != null">link_order_no = #{linkOrderNo},</if>
            <if test="orderStatus != null">order_status = #{orderStatus},</if>
            <if test="deliveryType != null">delivery_type = #{deliveryType},</if>
            <if test="linkUserId != null">link_user_id = #{linkUserId},</if>
            <if test="linkUserName != null">link_user_name = #{linkUserName},</if>
            <if test="createAt != null">create_at = #{createAt},</if>
            <if test="updateAt != null">update_at = #{updateAt},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="isGive != null">is_give = #{isGive},</if>
        </trim>
        where achievement_id = #{achievementId}
    </update>

    <update id="updateMallSalesmanAchievementBatch" parameterType="MallSalesmanAchievement">
        update mall_salesman_achievement
        <trim prefix="SET" suffixOverrides=",">
            <if test="salesmanId != null">salesman_id = #{salesmanId},</if>
            <if test="salesmanName != null">salesman_name = #{salesmanName},</if>
            <if test="price != null">price = #{price},</if>
            <if test="money != null">money = #{money},</if>
            <if test="status != null">status = #{status},</if>
            <if test="linkOrderId != null">link_order_id = #{linkOrderId},</if>
            <if test="linkOrderNo != null">link_order_no = #{linkOrderNo},</if>
            <if test="orderStatus != null">order_status = #{orderStatus},</if>
            <if test="deliveryType != null">delivery_type = #{deliveryType},</if>
            <if test="linkUserId != null">link_user_id = #{linkUserId},</if>
            <if test="linkUserName != null">link_user_name = #{linkUserName},</if>
            <if test="createAt != null">create_at = #{createAt},</if>
            <if test="updateAt != null">update_at = #{updateAt},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="isGive != null">is_give = #{isGive},</if>
        </trim>
        where achievement_id in
        <foreach item="achievementId" collection="achievementIdList" open="(" separator="," close=")">
            #{achievementId}
        </foreach>
    </update>

    <delete id="deleteMallSalesmanAchievementByAchievementId" parameterType="Long">
        delete from mall_salesman_achievement where achievement_id = #{achievementId}
    </delete>

    <delete id="deleteMallSalesmanAchievementByAchievementIds" parameterType="String">
        update from mall_salesman_achievement set del_flag = '1' where achievement_id in
        <foreach item="achievementId" collection="array" open="(" separator="," close=")">
            #{achievementId}
        </foreach>
    </delete>

    <select id="selectMallSalesmanAchievementByOrderId" parameterType="Long" resultMap="MallSalesmanAchievementResult">
        <include refid="selectMallSalesmanAchievementVo"/>
        where link_order_id = #{linkOrderId}
        limit 1
    </select>

    <select id="getSalesmanRevenueStatistics" resultType="java.math.BigDecimal">
        select ifnull(sum(msa.money), 0) price
        from mall_salesman_achievement msa
        left join mall_order_order moo on moo.order_no = msa.link_order_no
        <where>
            <if test="orderId != null "> and msa.link_order_id = #{orderId}</if>
            <if test="salesmanId != null "> and msa.salesman_id = #{salesmanId}</if>
            <if test="deliveryType != null"> and msa.delivery_type = #{deliveryType}</if>
            <if test="status != null"> and msa.status = #{status}</if>
            <if test="orderNo != null  and orderNo != ''"> and msa.link_order_no like concat('%', #{orderNo}, '%')</if>
            <if test="timeBegin != null and timeBegin !=''">
                and DATE_FORMAT(msa.create_at,'%Y-%m-%d') &gt;= DATE_FORMAT(#{timeBegin},'%Y-%m-%d')
            </if>
            <if test="timeEnd!= null and timeEnd !=''">
                and DATE_FORMAT(msa.create_at,'%Y-%m-%d') &lt;= DATE_FORMAT(#{timeEnd},'%Y-%m-%d')
            </if>
            and moo.status not in ('0', '8')
        </where>
    </select>

    <select id="getSalesmanRevenueOrderStatistics" resultType="java.lang.Integer">
        select ifnull(count(id), 0) orderNum
        from mall_order_order
        <where>
            <if test="salesmanId != null "> and salesman_id = #{salesmanId}</if>
            <if test="deliveryType != null"> and delivery_type = #{deliveryType}</if>
            <if test="status != null  and status != ''"> and status in ${status}</if>
            <if test="orderNo != null  and orderNo != ''"> and link_order_no like concat('%', #{orderNo}, '%')</if>
            <if test="timeBegin != null and timeBegin !=''">
                and DATE_FORMAT(`create_at`,'%Y-%m-%d') &gt;= DATE_FORMAT(#{timeBegin},'%Y-%m-%d')
            </if>
            <if test="timeEnd!= null and timeEnd !=''">
                and DATE_FORMAT(`create_at`,'%Y-%m-%d') &lt;= DATE_FORMAT(#{timeEnd},'%Y-%m-%d')
            </if>
        </where>
    </select>

    <select id="getSalesmanStatisticsOrderList" resultType="java.util.Map">
        select moo.id orderId, moo.order_no orderNo, moo.status, moo.payment, moo.delivery_type deliveryType, moo.create_at createAt, moo.receiver_name receiverName, moo.receiver_phone receiverPhone,
        ifnull(msa.money, 0) money,
        ms.name salesmanName, ms.phone salesmanPhone
        from mall_order_order moo
            left join mall_salesman_achievement msa on moo.id = msa.link_order_id
            left join mall_salesman ms on moo.salesman_id = ms.id
        <where>
            <if test="salesmanId != null and salesmanId != ''"> and moo.salesman_id = #{salesmanId}</if>
            <if test="deliveryType != null and deliveryType != ''"> and moo.delivery_type = #{deliveryType}</if>
            <if test="status != null and status != ''"> and moo.status = #{status}</if>
            <if test="searchValue != null and searchValue != ''">
                and (
                        moo.order_no like concat('%',#{searchValue},'%')
                         or ms.name like concat('%',#{searchValue},'%')
                         or ms.phone like concat('%',#{searchValue},'%')
                    )
            </if>
            <if test="timeBegin != null and timeBegin !=''">
                and DATE_FORMAT(moo.create_at,'%Y-%m-%d') &gt;= DATE_FORMAT(#{timeBegin},'%Y-%m-%d')
            </if>
            <if test="timeEnd!= null and timeEnd !=''">
                and DATE_FORMAT(moo.create_at,'%Y-%m-%d') &lt;= DATE_FORMAT(#{timeEnd},'%Y-%m-%d')
            </if>
        </where>
        order by moo.create_at desc
    </select>

    <select id="getSalesmanStatisticsRankingList" resultType="java.util.Map">
        SELECT
        msa.salesman_id salesmanId, ms.`name` salesmanName,
        IFNULL(count(msa.salesman_id), 0) orderNum,
        IFNULL(sum(msa.price), 0) price,
        IFNULL(sum(msa.money), 0) money
        FROM `mall_salesman_achievement` msa
        LEFT JOIN mall_salesman ms on msa.salesman_id = ms.id
        WHERE msa.status = 1
        GROUP BY msa.salesman_id

        order by
        <choose>
            <when test="sortType != null and sortType == 0">
                orderNum desc
            </when>
            <when test="sortType != null and sortType == 1">
                price desc
            </when>
            <when test="sortType != null and sortType == 2">
                money desc
            </when>
            <otherwise>
                orderNum desc
            </otherwise>
        </choose>
    </select>
</mapper>
