<?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.hc.business.mapper.UserMapper">

    <resultMap type="user" id="UserMap">
        <result property="userId" column="user_id" jdbcType="INTEGER"/>
        <result property="cardId" column="card_id" jdbcType="VARCHAR"/>
        <result property="userPassword" column="user_password" jdbcType="VARCHAR"/>
        <result property="userType" column="user_type" jdbcType="INTEGER"/>
        <result property="areaId" column="area_id" jdbcType="INTEGER"/>
        <result property="userNick" column="user_nick" jdbcType="VARCHAR"/>
        <result property="userName" column="user_name" jdbcType="VARCHAR"/>
        <result property="userMobile" column="user_mobile" jdbcType="VARCHAR"/>
        <result property="userEmail" column="user_email" jdbcType="VARCHAR"/>
        <result property="userGender" column="user_gender" jdbcType="VARCHAR"/>
        <result property="createTime" column="create_time" jdbcType="INTEGER"/>
        <result property="userBirthday" column="user_birthday" jdbcType="VARCHAR"/>
        <result property="active" column="active" jdbcType="TINYINT"/>
        <result property="userLogo" column="user_logo" jdbcType="VARCHAR"/>
        <result property="userCompany" column="user_company" jdbcType="VARCHAR"/>
        <result property="userReserved1" column="user_reserved1" jdbcType="VARCHAR"/>
        <result property="userReserved2" column="user_reserved2" jdbcType="VARCHAR"/>
        <result property="userReserved3" column="user_reserved3" jdbcType="VARCHAR"/>
        <result property="userReserved4" column="user_reserved4" jdbcType="VARCHAR"/>
        <result property="userReserved5" column="user_reserved5" jdbcType="VARCHAR"/>
    </resultMap>


       <!--<resultMap type="com.hc.business.dto.UserListDto" id="UserListMap">-->
    <!--<result property="userId" column="user_id" jdbcType="INTEGER"/>-->
    <!--<result property="userType" column="user_type" jdbcType="INTEGER"/>-->
    <!--<result property="userName" column="user_name" jdbcType="VARCHAR"/>-->
    <!--<result property="userMobile" column="user_mobile" jdbcType="VARCHAR"/>-->
    <!--<result property="userEmail" column="user_email" jdbcType="VARCHAR"/>-->
    <!--<result property="userGender" column="user_gender" jdbcType="VARCHAR"/>-->
    <!--<result property="userCompany" column="user_company" jdbcType="VARCHAR"/>-->
    <!--<result property="orderNumber" column="orders_id" jdbcType="VARCHAR"/>-->
    <!--<result property="status" column="order_status" jdbcType="INTEGER"/>-->
    <!--<result property="orderTime" column="create_time" jdbcType="INTEGER"/>-->
    <!--</resultMap>-->

    <sql id="Base_Column_List">
        user_id,
           card_id,
           user_password,
           user_type,
           area_id,
           user_nick,
           user_name,
           user_mobile,
           user_email,
           user_gender,
           create_time,
           user_birthday,
           `active`,
           user_logo,
           user_company,
           user_reserved1,
           user_reserved2,
           user_reserved3,
           user_reserved4,
           user_reserved5
    </sql>

    <select id="queryUserByMobile" resultMap="UserMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM `user`
        WHERE user_mobile = #{userMobile}
        LIMIT 1
    </select>

    <select id="queryOneByCardId" resultMap="UserMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM `user`
        WHERE card_id = #{cardId}
        LIMIT 1
    </select>

    <select id="queryUserByName" resultMap="UserMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM `user`
        WHERE user_name = #{name}
    </select>


    <select id="queryUserByClassName" resultType="com.hc.business.dto.UserListDto">


  select a.body as class_name,a.order_status, a.create_time as order_time,b.re_user_id as user_id,c.user_name,c.user_mobile,c.`user_email` ,c.`user_company`,c.user_type,c.user_gender,c.user_reserved1 as user_reserved,a.order_no as order_number
  from (select * from orders where  class_id in (select `class_config_id`  from `class_config`  where `class_name`  like CONCAT('%',#{name},'%'))) a
  LEFT JOIN  `order_user_log` b on a.orders_id = b.`order_id` LEFT JOIN  user c on b.`re_user_id` = c.user_id

    </select>


    <select id="queryUserByClassNameAndPay" resultType="com.hc.business.dto.UserListDto">

  select a.body as class_name,a.order_status, a.create_time as order_time,b.re_user_id as user_id,c.user_name,c.user_mobile,c.`user_email` ,c.`user_company`,c.user_type,c.user_gender,c.user_reserved1 as user_reserved,a.order_no as order_number

   from (select * from orders where `order_status` =1 and  class_id in (select `class_config_id`  from `class_config`  where `class_name`  like CONCAT('%',#{name},'%'))) a
  LEFT JOIN  `order_user_log` b on a.orders_id = b.`order_id` LEFT JOIN  user c on b.`re_user_id` = c.user_id

    </select>

    <select id="queryByUserCount" resultType="com.hc.business.dto.StaTimeTable">
        SELECT a.click_date, ifnull(b.count, 0) as count_create
        FROM (
                 SELECT curdate() as click_date
                 union all
                 SELECT date_sub(curdate(), interval 1 day) as click_date
                 union all
                 SELECT date_sub(curdate(), interval 2 day) as click_date
                 union all
                 SELECT date_sub(curdate(), interval 3 day) as click_date
                 union all
                 SELECT date_sub(curdate(), interval 4 day) as click_date
                 union all
                 SELECT date_sub(curdate(), interval 5 day) as click_date
                 union all
                 SELECT date_sub(curdate(), interval 6 day) as click_date
             ) a
                 left join (
            SELECT date(FROM_UNIXTIME(create_time / 1000)) as datetime, count(*) as count
            FROM `user`
            GROUP BY date(FROM_UNIXTIME(create_time / 1000))
        ) b on a.click_date = b.datetime;
    </select>

    <select id="queryToday" resultType="java.lang.Integer">
        SELECT value
        FROM (SELECT FROM_UNIXTIME(create_time / 1000, "%Y-%m-%d") as name, count(*) as value
              FROM `user`
              group by name) AS time
        WHERE name = current_date
    </select>

    <select id="queryMonth" resultType="java.lang.Integer">
        SELECT value
        FROM (SELECT FROM_UNIXTIME(create_time / 1000, "%Y-%m") as name, count(*) as value
              FROM `user`
              group by name) AS time
        WHERE name = left(current_date, 7)
    </select>

    <select id="queryTotal" resultType="java.lang.Integer">
        SELECT count(*)
        FROM `user`
    </select>

    <select id="queryYear" resultType="java.lang.Integer">
        SELECT value
        FROM (SELECT FROM_UNIXTIME(create_time / 1000, "%Y") as name, count(*) as value
              FROM `user`
              group by name) AS time
        WHERE name = left(current_date, 4)
    </select>



<!--    新增统计接口-->

    <select id="queryClassUserList" resultType="com.hc.business.dto.UserListDto">
        select c.class_name,b.* from  (select DISTINCT( `user_id`) , `class_id`  from `user_status`  where `class_id` =#{id}) a
            LEFT JOIN  user b on  a.`user_id`  = b.`user_id` LEFT JOIN  `class_config` c on a.class_id  = c.`class_config_id`

    </select>


    <select id="queryClassScoreList" resultType="com.hc.business.dto.UserScoreDto">
        select a.user_score,b.user_id,b.user_type,b.user_name,b.user_mobile,b.card_id ,b.user_gender,b.user_company
        from (select max(score) as user_score,user_id from `user_test_history`  where `class_id` =#{id} GROUP BY  `user_id`) a LEFT JOIN  user b on a.user_id = b.`user_id`  ;

    </select>


    <select id="queryClassProgress" resultType="com.hc.business.dto.UserProgressDto">
        select a.user_id,a.user_name,a.user_company,b.course_percent ,a.user_mobile from (select * from user where user_id in (select DISTINCT( `user_id`)  from `user_status`  where `class_id` =#{id})) a LEFT JOIN
                                                                          (select user_id,sum(course_percent) as course_percent from `user_course` GROUP BY  `user_id` ) b on a.user_id = b.user_id

    </select>

</mapper>