<?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.DistributionAuditingMapper">
  <resultMap id="BaseResultMap" type="com.yami.shop.distribution.comment.model.DistributionAuditing">
    <id column="auditing_id" jdbcType="BIGINT" property="auditingId" />
    <result column="shop_id" jdbcType="BIGINT" property="shopId" />
    <result column="parent_distribution_user_id" jdbcType="BIGINT" property="parentDistributionUserId" />
    <result column="distribution_user_id" jdbcType="BIGINT" property="distributionUserId" />
    <result column="auditing_time" jdbcType="TIMESTAMP" property="auditingTime" />
    <result column="state" jdbcType="TINYINT" property="state" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="modifier" jdbcType="VARCHAR" property="modifier" />
    <result column="remarks" jdbcType="VARCHAR" property="remarks" />
    <result column="reason" jdbcType="TINYINT" property="reason" />
  </resultMap>

  <!--审核管理-->
  <resultMap id="DistributionAuditing_DistributionUser_SysUser" type="com.yami.shop.distribution.comment.model.DistributionAuditing">
    <id column="auditing_id" jdbcType="BIGINT" property="auditingId" />
    <result column="shop_id" jdbcType="BIGINT" property="shopId" />
    <result column="parent_distribution_user_id" jdbcType="BIGINT" property="parentDistributionUserId" />
    <result column="distribution_user_id" jdbcType="BIGINT" property="distributionUserId" />
    <result column="auditing_time" jdbcType="TIMESTAMP" property="auditingTime" />
    <result column="state" jdbcType="TINYINT" property="state" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="modifier" jdbcType="VARCHAR" property="modifier" />
    <result column="remarks" jdbcType="VARCHAR" property="remarks" />
    <result column="reason" jdbcType="TINYINT" property="reason" />
    <association property="distributionUser" javaType="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" />
      <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="distributionUserAchievementDataDto"  javaType="com.yami.shop.distribution.comment.dto.DistributionUserAchievementDataDto">
        <association property="userShoppingDataDto" javaType="com.yami.shop.bean.distribution.UserShoppingDataDto">
          <result property="sumOfConsumption" jdbcType="DECIMAL" column="sum_of_consumption"/>
          <result property="expenseNumber" jdbcType="INTEGER" column="expense_number"/>
        </association>
      </association>
    </association>

    <association property="parentDistributionUser" javaType="com.yami.shop.distribution.comment.vo.DistributionUserVO">
      <id column="parent_distribution_user_id" jdbcType="BIGINT" property="distributionUserId" />
      <result column="parent_nick_name" jdbcType="VARCHAR" property="nickName" />
      <result column="parent_user_mobile" jdbcType="VARCHAR" property="userMobile" />
    </association>
    <association property="sysUser" javaType="com.yami.shop.bean.vo.SysUserVO">
      <id column="sys_user_id" jdbcType="BIGINT" property="userId" />
      <result column="sys_username" jdbcType="VARCHAR" property="username" />
    </association>
  </resultMap>
    <!--审核管理 -->
    <select id="distributionAuditingPage" resultType="com.yami.shop.distribution.comment.dto.DistributionAuditingDto">
      select
      da.*,
      -- 申请信息
      du.user_mobile,
      du.Identity_card_number,
      du.real_name,
      du.Identity_card_pic_front,
      du.Identity_card_pic_back,
      du.Identity_card_pic_hold,
      du.user_id,
      du.nick_name,
      -- 总消费
      sum( os.pay_amount) as sum_of_consumption,
      -- 消费单数
      count(os.settlement_id) as expense_number,
      -- 邀请人信息
      parent_du.nick_name as parent_nick_name,
      parent_du.user_mobile as parent_user_mobile,
      -- 操作人信息
      su.user_id as sys_user_id,
      su.username as sys_username
    from
    tz_distribution_auditing da
    -- 获取申请人的信息
    left join tz_distribution_user du on du.distribution_user_id = da.distribution_user_id
    -- 获取邀请人信息
    LEFT JOIN tz_distribution_user parent_du ON parent_du.distribution_user_id = da.parent_distribution_user_id
    -- 找到申请人消费记录
    left join tz_order_settlement os on os.user_id=du.user_id and os.is_clearing=1
    -- 找到操作人
    left join tz_sys_user su on da.modifier=su.user_id
    <where>
    <if test="distributionAuditing!= null and distributionAuditing.state != null">
      and da.state=#{distributionAuditing.state}
    </if>
    <if test="mobile!= null and mobile != ''">
      and du.user_mobile = #{mobile}
    </if>

    <if test="rangeTimeParam.startTime!= null ">
      and da.auditing_time &gt;=#{rangeTimeParam.startTime}
    </if>
    <if test="rangeTimeParam.endTime!= null ">
      and da.auditing_time &lt;=#{rangeTimeParam.endTime}
    </if>

    <if test="startPayAmount!= null ">
      and pay_amount &gt;=#{startPayAmount}
    </if>
    <if test="endPayAmount!= null ">
      and pay_amount &lt;=#{endPayAmount}
    </if>
    </where>
      GROUP BY da.auditing_id
    having 1=1
    <if test="startExpenseNumber!= null ">
      and COUNT(os.settlement_id)&gt;=#{startExpenseNumber}
    </if>
    <if test="endExpenseNumber!= null ">
      and COUNT(os.settlement_id)&lt;=#{endExpenseNumber}
    </if>
      order by da.auditing_id desc
    </select>
</mapper>
