<?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.yami.shop.distribution.comment.dao.DistributionUserMapper">
  <resultMap id="BaseResultMap" type="com.yami.shop.distribution.comment.model.DistributionUser">
    <id column="distribution_user_id" jdbcType="BIGINT" property="distributionUserId" />
    <result column="shop_id" jdbcType="BIGINT" property="shopId" />
    <result column="card_no" jdbcType="BIGINT" property="cardNo" />
    <result column="user_id" jdbcType="VARCHAR" property="userId" />
    <result column="parent_id" jdbcType="BIGINT" property="parentId" />
    <result column="parent_ids" jdbcType="VARCHAR" property="parentIds" />
    <result column="grade" jdbcType="INTEGER" property="grade" />
    <result column="level" jdbcType="INTEGER" property="level" />
    <result column="group_id" jdbcType="BIGINT" property="groupId" />
    <result column="bind_time" jdbcType="TIMESTAMP" property="bindTime" />
    <result column="state" jdbcType="TINYINT" property="state" />
    <result column="nick_name" jdbcType="VARCHAR" property="nickName" />
    <result column="user_mobile" jdbcType="VARCHAR" property="userMobile" />
    <result column="real_name" jdbcType="VARCHAR" property="realName" />
    <result column="identity_card_number" jdbcType="VARCHAR" property="identityCardNumber" />
    <result column="identity_card_pic_front" jdbcType="VARCHAR" property="identityCardPicFront" />
    <result column="identity_card_pic_back" jdbcType="VARCHAR" property="identityCardPicBack" />
    <result column="identity_card_pic_hold" jdbcType="VARCHAR" property="identityCardPicHold" />
      <result column="pic" jdbcType="VARCHAR" property="pic" />
  </resultMap>


    <!--业绩统计-->
    <resultMap id="distributionUser_Achievement" type="com.yami.shop.distribution.comment.model.DistributionUser">
        <id column="distribution_user_id" jdbcType="BIGINT" property="distributionUserId" />
        <result column="nick_name" jdbcType="VARCHAR" property="nickName" />
        <result column="user_mobile" jdbcType="VARCHAR" property="userMobile" />
        <association property="distributionUserAchievementDataDto" javaType="com.yami.shop.distribution.comment.dto.DistributionUserAchievementDataDto">
            <association property="distributionUserIncome" javaType="com.yami.shop.distribution.comment.vo.DistributionUserIncomeVO">
                <result property="aGenerationCommission" jdbcType="DECIMAL" column="a_generation_commission"/>
                <result property="secondGenerationCommission" jdbcType="DECIMAL" column="second_generation_commission"/>
                <result property="invitationRewards" jdbcType="DECIMAL" column="invitation_rewards"/>
            </association>
            <association property="distributionUserWallet" javaType="com.yami.shop.distribution.comment.dto.DistributionUserWalletDto">
                <result property="addupAmount" jdbcType="DECIMAL" column="addup_amount"/>
                <result property="unsettledAmount" jdbcType="DECIMAL" column="unsettled_amount"/>
                <result property="settledAmount" jdbcType="DECIMAL" column="settled_amount"/>
                <result column="invalid_amount" jdbcType="DECIMAL" property="invalidAmount" />
            </association>
        </association>
    </resultMap>

    <!--<select id="getParentIdsByDistributionUserId" resultType="string">-->
        <!--select parent_ids from tz_distribution_user where distribution_user_id = #{distributionUserId}-->
    <!--</select>-->


    <select id="getDistributionUserAchievementPage" resultMap="distributionUser_Achievement">
        SELECT
            du.distribution_user_id,
            du.nick_name,
            du.user_mobile,
            any_value(da.addup_amount)  as addup_amount,
            any_value(da.settled_amount)  as settled_amount,
            any_value(da.unsettled_amount)  as unsettled_amount,
            any_value(da.invalid_amount)  as invalid_amount,
            sum( CASE WHEN dui.income_type = 1 THEN dui.income_amount ELSE 0 END ) AS a_generation_commission,
            sum( CASE WHEN dui.income_type = 2 THEN dui.income_amount ELSE 0 END ) AS second_generation_commission,
            sum( CASE WHEN dui.income_type = 3 THEN dui.income_amount ELSE 0 END ) AS invitation_rewards
        FROM
            tz_distribution_user du
            LEFT JOIN tz_distribution_user_wallet da ON da.distribution_user_id = du.distribution_user_id
            LEFT JOIN tz_distribution_user_income dui ON dui.wallet_id = da.wallet_id AND dui.state != 0
        <where>
            <if test="distributionUser.shopId!= null">
              and  du.shop_id = #{distributionUser.shopId}
            </if>
            <if test="userMobile!= null and userMobile!=''">
                and du.user_mobile like concat('%',#{userMobile},'%')
            </if>
        </where>
    GROUP BY
            du.distribution_user_id
    </select>


    <!--获取分销员与等级条件匹配的数据-->
    <resultMap id="DistributionUserAchievementData" type="com.yami.shop.distribution.comment.dto.DistributionUserAchievementDataDto">
        <result property="boundCustomers" jdbcType="INTEGER" column="bound_customers"/>
        <result property="invitedVeeker" jdbcType="INTEGER" column="invited_veeker"/>
        <result property="payNumber" jdbcType="INTEGER" column="pay_number"/>
        <result property="successOrderNumber" jdbcType="INTEGER" column="success_order_number"/>
        <result property="successTradingVolume" jdbcType="DECIMAL" column="success_trading_volume"/>
        <result property="sumOfConsumption" jdbcType="DECIMAL" column="sum_of_consumption"/>

        <association property="distributionUserWallet" javaType="com.yami.shop.distribution.comment.dto.DistributionUserWalletDto">
            <result property="addupAmount" jdbcType="DECIMAL" column="addup_amount"/>
        </association>
    </resultMap>
    <!--获取分销员与等级条件匹配的数据-->
    <select id="getLevelDistributionUserAchievementDataByDistributionUserId" resultMap="DistributionUserAchievementData">
            select
            -- 积累绑定客户数
            count(distinct dub.user_id) as bound_customers,
            -- 积累邀请分销员数
            count(distinct sub_du.distribution_user_id) as invited_veeker,
            -- 积累支付单数
            count(distinct  o.order_id) as pay_number,
            -- 积累成功成交单数
            count(distinct dui.income_id) as success_order_number,
            -- 积累成功成交金额
            ifnull(	sum(oi.product_total_amount ) ,0) as success_trading_volume,
            -- 总消费
            sum( o.actual_total) as sum_of_consumption,
            -- 积累收益
            any_value(duw.addup_amount)  as addup_amount
            from
            tz_distribution_user  du
            -- 绑定客户信息
            left join tz_distribution_user_bind dub on dub.distribution_user_id=du.distribution_user_id
            -- 下级信息
            left join tz_distribution_user sub_du on sub_du.parent_id = du.distribution_user_id
            -- 消费信息
            left join tz_order o on o.user_id = du.user_id and (o.status = 4 or o.status = 5)
            -- 钱包
            left join tz_distribution_user_wallet duw on duw.distribution_user_id=du.distribution_user_id
            -- 收益信息(限一级佣金和成功的订单)
            left join tz_distribution_user_income dui on dui.wallet_id=duw.wallet_id and dui.income_type=1 and dui.state=2
            -- 成交的订单项
            left join tz_order_item oi on oi.order_item_id = dui.order_item_id
            where du.distribution_user_id=#{distributionUserId}
    </select>


    <!--分销员管理页面-->
    <resultMap id="distributionUserPage" type="com.yami.shop.distribution.comment.model.DistributionUser">
        <id column="distribution_user_id" jdbcType="BIGINT" property="distributionUserId" />
        <result column="shop_id" jdbcType="BIGINT" property="shopId" />
        <result column="card_no" jdbcType="BIGINT" property="cardNo" />
        <result column="user_id" jdbcType="VARCHAR" property="userId" />
        <result column="parent_id" jdbcType="BIGINT" property="parentId" />
        <result column="parent_ids" jdbcType="VARCHAR" property="parentIds" />
        <result column="grade" jdbcType="INTEGER" property="grade" />
        <result column="level" jdbcType="BIGINT" property="level" />
        <result column="group_id" jdbcType="BIGINT" property="groupId" />
        <result column="bind_time" jdbcType="TIMESTAMP" property="bindTime" />
        <result column="state" jdbcType="TINYINT" property="state" />
        <result column="nick_name" jdbcType="VARCHAR" property="nickName" />
        <result column="user_mobile" jdbcType="VARCHAR" property="userMobile" />
        <result column="real_name" jdbcType="VARCHAR" property="realName" />
        <result column="identity_card_number" jdbcType="VARCHAR" property="identityCardNumber" />
        <result column="identity_card_pic_front" jdbcType="VARCHAR" property="identityCardPicFront" />
        <result column="identity_card_pic_back" jdbcType="VARCHAR" property="identityCardPicBack" />
        <result column="identity_card_pic_hold" jdbcType="VARCHAR" property="identityCardPicHold" />
        <!--上级分销员-->
        <association property="parentDistributionUser" javaType="com.yami.shop.distribution.comment.vo.DistributionUserVO">
            <result column="parent_nick_name" jdbcType="VARCHAR" property="nickName" />
            <result column="parent_user_mobile" jdbcType="VARCHAR" property="userMobile" />
        </association>
        <!--分销员数据-->
        <association property="distributionUserAchievementDataDto" javaType="com.yami.shop.distribution.comment.dto.DistributionUserAchievementDataDto">
            <result property="boundCustomers" jdbcType="INTEGER" column="bound_customers"/>
            <result property="invitedVeeker" jdbcType="INTEGER" column="invited_veeker"/>
            <!--分销员钱包-->
            <association property="distributionUserWallet" javaType="com.yami.shop.distribution.comment.dto.DistributionUserWalletDto">
                <result property="addupAmount" jdbcType="DECIMAL" column="addup_amount"/>
            </association>
        </association>
    </resultMap>
  <select id="distributionUserPage" resultMap="distributionUserPage">
      select
      -- 分销员信息
      du.distribution_user_id,
      du.shop_id,
      du.card_no,
      du.parent_id,
      du.grade,
      du.level,
      du.group_id,
      du.bind_time,
      du.state,
      du.nick_name,
      du.user_mobile,
      du.real_name,
      du.identity_card_number,
      du.identity_card_pic_front,
      du.identity_card_pic_back,
      du.identity_card_pic_hold,
      -- 上级分销员信息
      pdu.user_mobile as parent_user_mobile,
      pdu.nick_name as parent_nick_name,
      -- 积累客户
      count(dub.bind_id) as bound_customers,
      -- 积累邀请
      count(sdu.distribution_user_id) as invited_veeker,
      -- 积累收益
      any_value(duw.addup_amount)  as addup_amount
      from tz_distribution_user du
      left join tz_distribution_user_wallet duw on duw.distribution_user_id = du.distribution_user_id
      left join tz_distribution_user pdu on pdu.distribution_user_id=du.parent_id
      left join tz_distribution_user_bind dub on dub.distribution_user_id = du.distribution_user_id
      left join tz_distribution_user sdu on sdu.parent_id=du.distribution_user_id
      <where>
          <if test="distributionUser.shopId != null">
              and du.shop_id=#{distributionUser.shopId}
          </if>
          <if test="mobile!= null and mobile != ''">
              and du.user_mobile=#{mobile}
          </if>

          <if test="parentMobile!= null and parentMobile != ''">
              and pdu.user_mobile=#{parentMobile}
          </if>
          <if test="rangeTimeParam.startTime!= null">
              and du.bind_time &gt;= #{rangeTimeParam.startTime}
          </if>
          <if test="rangeTimeParam.endTime!= null ">
              and du.bind_time &lt;= #{rangeTimeParam.endTime}
          </if>
      </where>
      GROUP BY du.distribution_user_id
  </select>

    <!--分销中心-->
    <resultMap id="DistributionUserAchievementDataAppDto" type="com.yami.shop.distribution.comment.dto.AchievementDataDto">
        <result property="boundCustomers"  column="bound_customers"/>
        <result property="invitedVeeker"  column="invited_veeker"/>
        <result property="orderCount" column="order_number"/>
        <association property="distributionUserWallet" javaType="com.yami.shop.distribution.comment.dto.DistributionUserWalletDto">
            <result column="unsettled_amount" jdbcType="DECIMAL" property="unsettledAmount" />
            <result column="settled_amount" jdbcType="DECIMAL" property="settledAmount" />
            <result column="addup_amount" jdbcType="DECIMAL" property="addupAmount" />
            <result column="invalid_amount" jdbcType="DECIMAL" property="invalidAmount" />
        </association>
    </resultMap>
    <select id="getAchievementDataDtoById" resultMap="DistributionUserAchievementDataAppDto">
        SELECT
            duw.unsettled_amount,
            duw.settled_amount,
            duw.addup_amount,
            duw.invalid_amount,
            bud.bound_customers,
            pdu.invited_veeker,
            dui.order_number
        FROM
            tz_distribution_user_wallet duw,
        ( SELECT count( distribution_user_id ) AS bound_customers FROM tz_distribution_user_bind WHERE distribution_user_id = #{id} ) AS bud,
        ( SELECT count( distribution_user_id ) AS invited_veeker FROM tz_distribution_user WHERE parent_id = #{id} ) AS pdu,
        ( SELECT count( distribution_user_id ) AS order_number FROM tz_distribution_user_income WHERE distribution_user_id = #{id} ) AS dui
        where duw.distribution_user_id = #{id}
    </select>

    <!--<select id="getParentDistributionUserIdByDistributionUserId" resultType="long">-->
        <!--select parent_user_id from tz_distribution_user-->
        <!--where distribution_user_id = #{distributionUserId}-->
    <!--</select>-->

    <!--<select id="getUserIdByDistributionUserId" resultType="string">-->
        <!--select user_id from tz_distribution_user-->
        <!--where distribution_user_id = #{distributionUserId}-->
    <!--</select>-->

    <select id="getDistributionUserSimpleDtoByParentUserIdPage" resultType="com.yami.shop.distribution.comment.dto.DistributionUserSimpleDto">
        select
        distribution_user_id,
        nick_name,
        pic,
        bind_time,
        real_name
        from
        tz_distribution_user
        where
        parent_id = #{parentDistributionUserId}
    </select>

    <update id="updateLevel">
        update
        tz_distribution_user
        set
        `level` = #{level}
        where distribution_user_id = #{distributionUserId}
    </update>
    <update id="replaceLevel">
      update tz_distribution_user set level = #{maxLevel} where shop_id = #{shopId}  and level &gt; #{maxLevel}
    </update>

    <!--<select id="getLevelIdByDistributionUserId" resultType="int">-->
      <!--select level_id from tz_distribution_user where distribution_user_id=#{distributionUserId}-->
    <!--</select>-->

</mapper>
