<?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.MallSalesmanMapper">

    <resultMap type="MallSalesman" id="MallSalesmanResult">
        <result property="id"    column="id"    />
        <result property="phone"    column="phone"    />
        <result property="password"    column="password"    />
        <result property="name"    column="name"    />
        <result property="department"    column="department"    />
        <result property="avatar"    column="avatar"    />
        <result property="status"    column="status"    />
        <result property="createAt"    column="create_at"    />
        <result property="updateAt"    column="update_at"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="userId"    column="user_id"    />
        <result property="userName"    column="userName"    />
        <result property="shopId"    column="shop_id"    />
        <result property="createBy"    column="create_by"    />
        <result property="account"    column="account"    />
        <result property="realPassword"    column="real_password"    />
        <result property="companyId"    column="company_id"    />
        <result property="sysUserId"    column="sys_user_id"    />
        <result property="roleId"    column="role_id"    />
        <result property="companyUserType"    column="company_user_type"    />
        <result property="qrCode"    column="qr_code"    />
        <result property="isLogin"    column="is_login"    />
    </resultMap>

    <sql id="selectMallSalesmanVo">
        select id, phone, password, `name`, department, avatar, status, create_at, update_at, del_flag, user_id,
               shop_id, create_by, account, real_password, company_id, sys_user_id, role_id, company_user_type, qr_code,
               is_login
        from mall_salesman
    </sql>

    <select id="selectMallSalesmanList" parameterType="MallSalesman" resultType="MallSalesman">
        SELECT
            ms.id id,
            ms.phone phone,
            ms.`password` password,
            ms.`name` `name`,
            ms.department department,
                sd.dept_name deptName,
            ms.avatar avatar,
            ms.`status` `status`,
            ms.create_at createAt,
            ms.update_at updateAt,
            ms.del_flag delFlag,
            ms.user_id userId,
            ms.shop_id shopId,
            ms.create_by createBy,
            IFNULL((SELECT
                SUM(moo.payment)
                FROM
                mall_order_order moo
                LEFT JOIN mall_member_user mmu
                ON moo.user_id = mmu.id
                WHERE mmu.salesman_id = ms.id
                    AND moo.status IN ('3', '4')
                <if test="params.beginAchievementTime != null and params.beginAchievementTime != '' and params.endAchievementTime != null and params.endAchievementTime != ''">
                    AND DATE_FORMAT(moo.create_at,'%Y-%m-%d') &gt;= #{params.beginAchievementTime}
                    AND DATE_FORMAT(moo.create_at,'%Y-%m-%d') &lt;= #{params.endAchievementTime}
                </if>
                ),0) AS allOrderPrice,
            IFNULL((SELECT
                COUNT(id)
                FROM mall_member_user
                WHERE salesman_id = ms.id
                    AND del_flag = '0'
                <if test="params.beginAchievementTime != null and params.beginAchievementTime != '' and params.endAchievementTime != null and params.endAchievementTime != ''">
                    AND DATE_FORMAT(binding_at,'%Y-%m-%d') &gt;= #{params.beginAchievementTime}
                    AND DATE_FORMAT(binding_at,'%Y-%m-%d') &lt;= #{params.endAchievementTime}
                </if>
                ),0) AS allMemberUserCount,
            IFNULL((SELECT
                COUNT(moo.id)
                FROM mall_order_order moo
                    LEFT JOIN mall_member_user mmu
                        ON moo.user_id = mmu.id
                WHERE mmu.salesman_id = ms.id
                    AND moo.status IN ('3', '4')
                <if test="params.beginAchievementTime != null and params.beginAchievementTime != '' and params.endAchievementTime != null and params.endAchievementTime != ''">
                    AND DATE_FORMAT(moo.create_at,'%Y-%m-%d') &gt;= #{params.beginAchievementTime}
                    AND DATE_FORMAT(moo.create_at,'%Y-%m-%d') &lt;= #{params.endAchievementTime}
                </if>
                ),0) AS allOrderCount
        ,ms.account account
        ,ms.real_password realPassword
        ,ms.company_id companyId
        ,ms.sys_user_id sysUserId
        ,ms.role_id roleId
        ,ms.company_user_type companyUserType
        ,ms.qr_code qrCode
        ,ms.is_login isLogin
        FROM mall_salesman ms
            left join sys_dept sd
                on ms.department = sd.dept_id
        <where>
            <if test="id != null "> and ms.id = #{id}</if>
            <if test="userId != null "> and ms.user_id = #{userId}</if>
            <if test="phone != null  and phone != ''"> and ms.phone = #{phone}</if>
            <if test="password != null  and password != ''"> and ms.password = #{password}</if>
            <if test="name != null  and name != ''"> and ms.name like concat('%', #{name}, '%')</if>
            <if test="department != null "> and ms.department = #{department}</if>
            <if test="avatar != null  and avatar != ''"> and ms.avatar = #{avatar}</if>
            <if test="status != null "> and ms.status = #{status}</if>
            <if test="createAt != null  and createAt != ''"> and ms.create_at = #{createAt}</if>
            <if test="updateAt != null  and updateAt != ''"> and ms.update_at = #{updateAt}</if>
            <if test="shopId != null "> and ms.shop_id = #{shopId}</if>
            <if test="account != null  and account != ''"> and ms.account like concat('%', #{account}, '%')</if>
            <if test="companyId != null "> and ms.company_id = #{companyId}</if>
            <if test="sysUserId != null "> and ms.sys_user_id = #{sysUserId}</if>
            <if test="roleId != null "> and ms.role_id = #{roleId}</if>
            <if test="companyUserType != null "> and ms.company_user_type = #{companyUserType}</if>
            and ms.del_flag = '0'
        </where>
        order by ms.company_user_type asc, ms.create_at desc
    </select>

    <select id="selectMallSalesmanByPhone" parameterType="String" resultMap="MallSalesmanResult">
        <include refid="selectMallSalesmanVo"/>
        where phone = #{phone} and del_flag = '0'
    </select>

    <select id="indexData" resultType="map">
        SELECT
            IFNULL(SUM(payment), 0) totalPrice,
            IFNULL(COUNT(id), 0) totalCount
        FROM
            mall_order_order
        <where>
            and status in ('3','4')
            and user_id IN
                (SELECT
                id
                FROM
                mall_member_user
                WHERE salesman_id = #{id})
            <choose>
                <when test="type != null and type == 0">
                    and DATE_FORMAT(create_at, '%Y-%m-%d') = DATE_FORMAT(CURRENT_DATE(), '%Y-%m-%d')
                </when>
                <when test="type != null and type == 1">
                    and DATE_FORMAT(create_at, '%Y-%m') = DATE_FORMAT(CURRENT_DATE(), '%Y-%m')
                </when>
                <when test="type != null and type == 2">
                    and DATE_FORMAT(create_at, '%Y') = DATE_FORMAT(CURRENT_DATE(), '%Y')
                </when>
                <otherwise>
                </otherwise>
            </choose>
        </where>
    </select>

    <select id="salesmanCustomerList" parameterType="map" resultType="map">
        SELECT
            mmu.`id` memberId,
            mmu.`name` memberName,
            mmu.`phone` memberPhone,
            mmu.`shop_name` memberShopName,
            DATE_FORMAT(mmu.`create_at`,'%Y-%m-%d') memberCreateAt,
            CONCAT(
                    IFNULL(mmu.`province`, ''),
                    IFNULL(mmu.`city`, ''),
                    IFNULL(mmu.`area`, ''),
                    IFNULL(mmu.`address`, '')
                ) memberAddress,
            (SELECT
                 CONCAT(
                         IFNULL(`province`, ''),
                         IFNULL(`city`, ''),
                         IFNULL(`area`, ''),
                         IFNULL(`address`, '')
                     )
             FROM
                 mall_member_address
             WHERE user_id = mmu.`id`
               AND is_default = '1'
                LIMIT 1) memberShippingAddress,
          (SELECT
            IFNULL(SUM(payment), 0)
          FROM
            mall_order_order
          WHERE user_id = mmu.`id`
            AND del_flag = '0'
            AND `status` IN ('3', '4')) memberOrderPrice,
          (SELECT
            IFNULL(COUNT(id), 0)
          FROM
            mall_order_order
          WHERE user_id = mmu.`id`
            AND del_flag = '0'
            AND `status` IN ('3', '4')) memberOrderCount
        FROM
            mall_member_user mmu
        <where>
            and mmu.`del_flag` = '0'
            and mmu.`salesman_id` = #{salesmanId}
            <if test="timeBegin != null and timeBegin !='' and timeEnd!= null and timeEnd !=''">
                and DATE_FORMAT(mmu.`create_at`,'%Y-%m-%d') &gt;= DATE_FORMAT(#{timeBegin},'%Y-%m-%d')
                and DATE_FORMAT(mmu.`create_at`,'%Y-%m-%d') &lt;= DATE_FORMAT(#{timeEnd},'%Y-%m-%d')
            </if>
            <if test="province != null and province !=''">
                and mmu.`province` like concat('%',#{province},'%')
            </if>
            <if test="city != null and city !=''">
                and mmu.`city` like concat('%',#{city},'%')
            </if>
            <if test="area != null and area !=''">
                and mmu.`area` like concat('%',#{area},'%')
            </if>
            <if test="searchValue != null and searchValue !=''">
                and (mmu.`name` like concat('%',#{searchValue},'%')
                    OR mmu.`phone` like concat('%',#{searchValue},'%'))
            </if>
        </where>
        ORDER BY memberCreateAt DESC
    </select>

    <select id="finishedOrderData" resultType="map">
        SELECT
            IFNULL(SUM(payment), 0) finishedPrice,
            IFNULL(COUNT(id), 0) finishedCount
        FROM
            mall_order_order
        <where>
            and user_id IN
                (SELECT
                id
                FROM
                mall_member_user
                WHERE salesman_id = #{salesmanId})
            and status in ('3','4')
            and del_flag = '0'
            <if test="timeBegin != null and timeBegin !='' and timeEnd!= null and timeEnd !=''">
                and DATE_FORMAT(`create_at`,'%Y-%m-%d') &gt;= DATE_FORMAT(#{timeBegin},'%Y-%m-%d')
                and DATE_FORMAT(`create_at`,'%Y-%m-%d') &lt;= DATE_FORMAT(#{timeEnd},'%Y-%m-%d')
            </if>
        </where>
    </select>

    <select id="toBeSettledOrderData" resultType="map">
        SELECT
            IFNULL(SUM(payment), 0) toBeSettledPrice,
            IFNULL(COUNT(id), 0) toBeSettledCount
        FROM
            mall_order_order
        <where>
            and user_id IN
                (SELECT
                id
                FROM
                mall_member_user
                WHERE salesman_id = #{salesmanId})
            and status in ('1','2')
            and del_flag = '0'
            <if test="timeBegin != null and timeBegin !='' and timeEnd!= null and timeEnd !=''">
                and DATE_FORMAT(`create_at`,'%Y-%m-%d') &gt;= DATE_FORMAT(#{timeBegin},'%Y-%m-%d')
                and DATE_FORMAT(`create_at`,'%Y-%m-%d') &lt;= DATE_FORMAT(#{timeEnd},'%Y-%m-%d')
            </if>
        </where>
    </select>

    <select id="getSalesmanInfo" parameterType="Integer" resultType="MallSalesman">
        select ms.id id, ms.user_id userId, ms.phone phone, ms.password password, ms.`name` `name`, ms.department department, ms.avatar avatar,
               ms.status status, ms.create_at createAt, ms.update_at updateAt, ms.del_flag delFlag, sd.dept_name deptName
        from mall_salesman ms
            left join sys_dept sd on ms.department = sd.dept_id
        where id = #{id}
    </select>

    <select id="selectMallSalesmanById" parameterType="Integer" resultMap="MallSalesmanResult">
        <include refid="selectMallSalesmanVo"/>
        where id = #{id}
    </select>

    <insert id="insertMallSalesman" parameterType="MallSalesman" useGeneratedKeys="true" keyProperty="id">
        insert into mall_salesman
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="phone != null">phone,</if>
            <if test="password != null">password,</if>
            <if test="name != null">name,</if>
            <if test="department != null">department,</if>
            <if test="avatar != null">avatar,</if>
            <if test="status != null">status,</if>
            <if test="createAt != null">create_at,</if>
            <if test="updateAt != null">update_at,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="userId != null">user_id,</if>
            <if test="shopId != null">shop_id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="account != null">account,</if>
            <if test="realPassword != null">real_password,</if>
            <if test="companyId != null">company_id,</if>
            <if test="sysUserId != null">sys_user_id,</if>
            <if test="roleId != null">role_id,</if>
            <if test="companyUserType != null">company_user_type,</if>
            <if test="qrCode != null">qr_code,</if>
            <if test="isLogin != null">is_login,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="phone != null">#{phone},</if>
            <if test="password != null">#{password},</if>
            <if test="name != null">#{name},</if>
            <if test="department != null">#{department},</if>
            <if test="avatar != null">#{avatar},</if>
            <if test="status != null">#{status},</if>
            <if test="createAt != null">#{createAt},</if>
            <if test="updateAt != null">#{updateAt},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="userId != null">#{userId},</if>
            <if test="shopId != null">#{shopId},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="account != null">#{account},</if>
            <if test="realPassword != null">#{realPassword},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="sysUserId != null">#{sysUserId},</if>
            <if test="roleId != null">#{roleId},</if>
            <if test="companyUserType != null">#{companyUserType},</if>
            <if test="qrCode != null">#{qrCode},</if>
            <if test="isLogin != null">#{isLogin},</if>
         </trim>
    </insert>

    <update id="updateMallSalesman" parameterType="MallSalesman">
        update mall_salesman
        <trim prefix="SET" suffixOverrides=",">
            <if test="phone != null">phone = #{phone},</if>
            <if test="password != null">password = #{password},</if>
            <if test="name != null">name = #{name},</if>
            <if test="department != null">department = #{department},</if>
            <if test="avatar != null">avatar = #{avatar},</if>
            <if test="status != null">status = #{status},</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="userId != null">user_id = #{userId},</if>
            <if test="shopId != null">shop_id = #{shopId},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="account != null">account = #{account},</if>
            <if test="realPassword != null">real_password = #{realPassword},</if>
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="sysUserId != null">sys_user_id = #{sysUserId},</if>
            <if test="roleId != null">role_id = #{roleId},</if>
            <if test="companyUserType != null">company_user_type = #{companyUserType},</if>
            <if test="qrCode != null">qr_code = #{qrCode},</if>
            <if test="isLogin != null">is_login = #{isLogin},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteMallSalesmanById" parameterType="Integer">
        <!--delete from mall_salesman where id = #{id}-->
        update mall_salesman set del_flag = '1' where id = #{id}
    </delete>

    <delete id="deleteMallSalesmanByIds" parameterType="String">
        <!--delete from mall_salesman where id in-->
        update mall_salesman set del_flag = '1' where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectMallSalesmanUserNumList" resultType="com.ruoyi.mall.domain.MallSalesman">
        SELECT
            ms.id, ms.`name`, ms.`phone`,
            IFNULL(count(mmu.salesman_id), 0) allMemberUserCount
        FROM `mall_salesman` ms
                 LEFT JOIN mall_member_user mmu on ms.id = mmu.salesman_id
        WHERE
            ms.status = 0
          AND ms.del_flag = 0
        GROUP BY ms.id
        ORDER BY allMemberUserCount ASC, ms.create_at ASC
    </select>

    <select id="selectMallSalesmanByUserId" parameterType="Long" resultMap="MallSalesmanResult">
        <include refid="selectMallSalesmanVo"/>
        where user_id = #{userId}
        AND del_flag = 0
        limit 1
    </select>

    <select id="selectMallSalesmanBySysUserId" parameterType="Long" resultMap="MallSalesmanResult">
        <include refid="selectMallSalesmanVo"/>
        where sys_user_id = #{sysUserId}
        AND del_flag = 0
        limit 1
    </select>

    <select id="selectMallSalesmanSysUserIdByIds" resultType="Long">
        select sys_user_id from mall_salesman where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>
