<?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.frequency.sale.mapper.SaleMapper">

    <resultMap type="com.ruoyi.frequency.sale.entity.Sale" id="SaleResult">
        <result property="id"    column="id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="avatar"    column="avatar"    />
        <result property="mobile"    column="mobile"    />
        <result property="nickname"    column="nickname"    />
        <result property="name"    column="name"    />
        <result property="enterDate"    column="enter_date"    />
        <result property="openId"    column="open_id"    />
        <result property="loginPwd"    column="login_pwd"    />
        <result property="signature"    column="signature"    />
        <result property="balance"    column="balance"    />
        <result property="income"    column="income"    />
        <result property="inviteCode"    column="invite_code"    />
        <result property="inviteNum"    column="invite_num"    />
        <result property="commissionRanking"    column="commissionRanking"    />
        <result property="saleRanking"    column="saleRanking"    />
        <result property="closeMessage"    column="close_message"    />
        <result property="bank"    column="bank"    />
        <result property="subbranch"    column="subbranch"    />
        <result property="bankCard"    column="bank_card"    />
        <result property="payPwd"    column="pay_pwd"    />
    </resultMap>

    <sql id="selectSaleVo">
        SELECT
            tt.id,
            tt.create_by,
            tt.create_time,
            tt.update_by,
            tt.update_time,
            tt.del_flag,
            tt.avatar,
            tt.mobile,
            tt.nickname,
            tt.name,
            tt.enter_date,
            tt.open_id,
            tt.login_pwd,
            tt.signature,
            tt.balance,
            tt.income,
            tt.invite_code,
            tt.invite_num,
            tt.remark,
            tt.background_image,
            tt.close_message,
            (SELECT
                 COUNT( 1 )
             FROM
                 t_sale t
             WHERE
                 t.del_flag != 2
               AND (
                     ( t.income = ( SELECT s.income FROM t_sale s WHERE s.id = tt.id ) AND t.id &lt;=  tt.id )
                     OR ( t.income > ( SELECT s.income FROM t_sale s WHERE s.id =  tt.id ) )
                 )) commissionRanking,
            (SELECT
                 COUNT( 1 )
             FROM
                 t_sale t
             WHERE
                 t.del_flag != 2
               AND (
                     ( t.invite_num = ( SELECT s.invite_num FROM t_sale s WHERE s.id =tt.id ) AND t.id &lt;= tt.id )
                     OR ( t.invite_num > ( SELECT s.invite_num FROM t_sale s WHERE s.id =  tt.id ) )
                 )) saleRanking,
            tt.bank,
            tt.subbranch,
            tt.bank_card,
            tt.pay_pwd
        from t_sale tt
    </sql>

    <select id="selectSaleList" parameterType="com.ruoyi.frequency.sale.entity.Sale" resultMap="SaleResult">
        <include refid="selectSaleVo"/>
        <where> and  tt.del_flag != 2
            <if test="createTime != null "> and tt.create_time = #{createTime}</if>
            <if test="avatar != null  and avatar != ''"> and tt.avatar = #{avatar}</if>
            <if test="mobile != null  and mobile != ''"> and tt.mobile like concat('%',#{mobile}, '%')</if>
            <if test="enterDate != null "> and tt.enter_date = #{enterDate}</if>
            <if test="openId != null  and openId != ''"> and tt.open_id = #{openId}</if>
            <if test="loginPwd != null  and loginPwd != ''"> and tt.login_pwd = #{password}</if>
            <if test="nickname != null  and nickname != ''"> and tt.nickname like concat('%',#{nickname}, '%')</if>
            <if test="name != null  and name != ''"> and tt.name like concat('%',#{name}, '%')</if>
            <if test="signature != null  and signature != ''"> and tt.signature = #{signature}</if>
            <if test="balance != null "> and tt.balance = #{balance}</if>
            <if test="income != null "> and tt.income = #{income}</if>
            <if test="inviteCode != null  and inviteCode != ''"> and tt.invite_code = #{inviteCode}</if>
            <if test="inviteNum != null "> and tt.invite_num = #{inviteNum}</if>
        </where>
        <choose>
            <when test="sortType != null ">
                <if test="sortType == 1 ">
                    order by tt.income desc
                </if>
                <if test="sortType == 2 ">
                    order by tt.income
                </if>
            </when>
            <otherwise>
                order by tt.create_time desc
            </otherwise>
        </choose>
    </select>

    <select id="statistics" parameterType="com.ruoyi.frequency.sale.entity.Sale" resultType="com.ruoyi.frequency.sale.model.result.SaleCount">
        SELECT
        count(1) number,
        IFNULL(SUM(tt.income),0)AS income,
        IFNULL(SUM(tt.invite_num),0)AS invite_num
        from t_sale tt
        where
        tt.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="selectSaleById" parameterType="Long" resultMap="SaleResult">
        <include refid="selectSaleVo"/>
        where tt.id = #{id}
    </select>

    <insert id="insertSale" parameterType="com.ruoyi.frequency.sale.entity.Sale">
        insert into t_sale
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="avatar != null">avatar,</if>
            <if test="mobile != null">mobile,</if>
            <if test="enterDate != null">enter_date,</if>
            <if test="openId != null">open_id,</if>
            <if test="loginPwd != null">login_pwd,</if>
            <if test="signature != null">signature,</if>
            <if test="balance != null">balance,</if>
            <if test="income != null">income,</if>
            <if test="inviteCode != null">invite_code,</if>
            <if test="inviteNum != null">invite_num,</if>
            <if test="nickname != null">nickname,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="avatar != null">#{avatar},</if>
            <if test="mobile != null">#{mobile},</if>
            <if test="enterDate != null">#{enterDate},</if>
            <if test="openId != null">#{openId},</if>
            <if test="loginPwd != null">#{loginPwd},</if>
            <if test="signature != null">#{signature},</if>
            <if test="balance != null">#{balance},</if>
            <if test="income != null">#{income},</if>
            <if test="inviteCode != null">#{inviteCode},</if>
            <if test="inviteNum != null">#{inviteNum},</if>
            <if test="nickname != null">#{nickname},</if>
        </trim>
    </insert>

    <update id="updateSale" parameterType="com.ruoyi.frequency.sale.entity.Sale">
        update t_sale
        <trim prefix="SET" suffixOverrides=",">
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="avatar != null">avatar = #{avatar},</if>
            <if test="mobile != null">mobile = #{mobile},</if>
            <if test="enterDate != null">enter_date = #{enterDate},</if>
            <if test="openId != null">open_id = #{openId},</if>
            <if test="loginPwd != null">login_pwd = #{loginPwd},</if>
            <if test="signature != null">signature = #{signature},</if>
            <if test="balance != null">balance = #{balance},</if>
            <if test="income != null">income = #{income},</if>
            <if test="inviteCode != null">invite_code = #{inviteCode},</if>
            <if test="inviteNum != null">invite_num = #{inviteNum},</if>
            <if test="nickname != null">nickname = #{nickname},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSaleById" parameterType="Long">
        delete from t_sale where id = #{id}
    </delete>

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

    <select id="commissionRanking" resultType="java.lang.Integer">
        SELECT
            COUNT( 1 )
        FROM
            t_sale t
        WHERE
            t.del_flag = 1
          AND (
                ( t.income = ( SELECT s.income FROM t_sale s WHERE s.id =  #{userId} ) AND t.id &lt;=  #{userId} )
                OR ( t.income > ( SELECT s.income FROM t_sale s WHERE s.id =  #{userId} ) )
            )
    </select>

    <select id="saleRanking" resultType="java.lang.Integer">
        SELECT
            COUNT( 1 )
        FROM
            t_sale t
        WHERE
            t.del_flag = 1
          AND (
                ( t.invite_num = ( SELECT s.invite_num FROM t_sale s WHERE s.id = #{userId} ) AND t.id &lt;=  #{userId} )
                OR ( t.invite_num > ( SELECT s.invite_num FROM t_sale s WHERE s.id =  #{userId} ) )
            )
    </select>

    <select id="commissionRankingPage" resultType="com.ruoyi.controller.resp.RankingResp">
        SELECT
            t.id,
            t.name,
            t.nickname,
            t.avatar,
            t.income
        FROM
            t_sale t
        WHERE
            t.del_flag = 1
        ORDER BY
            t.income DESC,
            t.id
    </select>

    <select id="saleRankingPage" resultType="com.ruoyi.controller.resp.RankingResp">
        SELECT
            t.id,
            t.name,
            t.nickname,
            t.avatar,
            t.invite_num
        FROM
            t_sale t
        WHERE
            t.del_flag = 1
        ORDER BY
            t.invite_num DESC,
            t.id
    </select>

    <select id="saleDetailPage" resultType="com.ruoyi.controller.resp.SaleDetailPageResp">
        SELECT
        t.id,
        t.create_time,
        t.del_flag,
        t.title,
        t.content,
        t.amount,
        t.amount - t.first_give_price - t.second_give_price payAmount,
        t.source_material,
        t.store_id,
        t.confirm_date,
        t.drawing_num,
        t.customer_id,
        t.first_status,
        t.first_price,
        t.status,
        t.images,
        t.order_no,
        t.sale_amount,
        t.change_status,
        t.appeal_status,
        t.appeal_user_type,
        t2.`name` worksTypeName,
        t.if_highend,
        (select a.id from t_appeal a where a.order_id = t.id and a.del_flag = 1 order by a.create_time desc limit 1) as
        appealId
        FROM
        t_order AS t
        LEFT JOIN t_works_type AS t2 ON t.works_type_id = t2.id
        LEFT JOIN t_works_type AS t3 ON t2.pid = t3.id
        LEFT JOIN t_store s on t.store_id = s.id
        LEFT JOIN t_level l ON l.type = 1 and l.id = (
        SELECT
        l2.id
        FROM
        t_level l2
        WHERE
        l2.del_flag = 1
        and l2.type =2
        and l2.compliance &lt;= s.income
        ORDER BY
        l2.compliance DESC
        LIMIT 1
        )
        left join t_customer c on c.id = t.customer_id
        WHERE
        t.del_flag = 1
        AND t.sale_id = #{userId}
        <if test="req.beginTime != null "> AND t.create_time >= #{req.beginTime}</if>
        <if test="req.endTime != null "> AND t.create_time &lt; #{req.endTime}</if>
        <if test="req.keyword != null and req.keyword != ''">
            AND (
            c.nickname like concat('%', #{req.keyword}, '%')
            or
            t.title like concat('%', #{req.keyword}, '%')
            or
            t.order_no like concat('%', #{req.keyword}, '%')
            )
        </if>
        ORDER BY
        t.create_time DESC
    </select>

    <select id="saleOrderAmount" resultType="com.ruoyi.controller.resp.SaleDataResp">
        SELECT
            ifnull(sum(t.amount-t.first_give_price-t.second_give_price),0) amount
        FROM
            t_order AS t
        WHERE
            t.del_flag = 1
          AND t.sale_id = #{userId}
        ORDER BY
            t.create_time DESC
    </select>
</mapper>
