<?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.microservices.dss.projects.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.microservices.dss.projects.domain.User">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="login" property="login" jdbcType="VARCHAR"/>
        <result column="hashed_password" property="hashedPassword" jdbcType="VARCHAR"/>
        <result column="firstname" property="firstname" jdbcType="VARCHAR"/>
        <result column="lastname" property="lastname" jdbcType="VARCHAR"/>
        <result column="mail" property="mail" jdbcType="VARCHAR"/>
        <result column="admin" property="admin" jdbcType="BIT"/>
        <result column="status" property="status" jdbcType="INTEGER"/>
        <result column="last_login_on" property="lastLoginOn" jdbcType="TIMESTAMP"/>
        <result column="language" property="language" jdbcType="VARCHAR"/>
        <result column="auth_source_id" property="authSourceId" jdbcType="INTEGER"/>
        <result column="created_on" property="createdOn" jdbcType="TIMESTAMP"/>
        <result column="updated_on" property="updatedOn" jdbcType="TIMESTAMP"/>
        <result column="type" property="type" jdbcType="VARCHAR"/>
        <result column="identity_url" property="identityUrl" jdbcType="VARCHAR"/>
        <result column="mail_notification" property="mailNotification" jdbcType="VARCHAR"/>
        <result column="salt" property="salt" jdbcType="VARCHAR"/>
        <result column="gid" property="gid" jdbcType="INTEGER"/>
        <result column="visits" property="visits" jdbcType="INTEGER"/>
        <result column="excellent_teacher" property="excellentTeacher" jdbcType="INTEGER"/>
        <result column="excellent_student" property="excellentStudent" jdbcType="INTEGER"/>
        <result column="phone" property="phone" jdbcType="VARCHAR"/>
        <result column="authentication" property="authentication" jdbcType="BIT"/>
        <result column="grade" property="grade" jdbcType="INTEGER"/>
        <result column="experience" property="experience" jdbcType="INTEGER"/>
        <result column="nickname" property="nickname" jdbcType="VARCHAR"/>
        <result column="show_realname" property="showRealname" jdbcType="BIT"/>
        <result column="professional_certification" property="professionalCertification" jdbcType="BIT"/>
        <result column="ID_number" property="idNumber" jdbcType="VARCHAR"/>
        <result column="certification" property="certification" jdbcType="INTEGER"/>
        <result column="homepage_teacher" property="homepageTeacher" jdbcType="BIT"/>
        <result column="homepage_engineer" property="homepageEngineer" jdbcType="BIT"/>
        <result column="is_test" property="isTest" jdbcType="TINYINT"/>
        <result column="ecoder_user_id" property="ecoderUserId" jdbcType="INTEGER"/>
        <result column="business" property="business" jdbcType="BIT"/>
        <result column="profile_completed" property="profileCompleted" jdbcType="BIT"/>
        <result column="laboratory_id" property="laboratoryId" jdbcType="BIGINT"/>
        <result column="platform" property="platform" jdbcType="VARCHAR"/>
        <result column="gitea_token" property="giteaToken" jdbcType="VARCHAR"/>
        <result column="gitea_uid" property="giteaUid" jdbcType="INTEGER"/>
        <result column="is_shixun_marker" property="isShixunMarker" jdbcType="BIT"/>
        <result column="is_sync_pwd" property="isSyncPwd" jdbcType="BIT"/>
        <result column="watchers_count" property="watchersCount" jdbcType="INTEGER"/>
        <result column="enterprise_certification" property="enterpriseCertification" jdbcType="BIT"/>
        <result column="admin_role" property="adminRole" jdbcType="TINYINT"/>
        <result column="devops_step" property="devopsStep" jdbcType="INTEGER"/>
        <result column="business" property="business" jdbcType="BIT"/>
    </resultMap>

    <select id="selectUserCount" resultType="java.lang.Integer">
        select count(*)
        from users
    </select>

    <select id="getNewAddedMembersByMonth" parameterType="java.lang.String" resultType="java.util.HashMap">
        SELECT
            DATE_FORMAT(created_on, '%Y-%m') AS createTime,
            COUNT(*) AS num
        FROM
            users
        WHERE
            created_on &gt;= #{startTime,jdbcType=VARCHAR}
          AND created_on &lt;= #{endTime,jdbcType=VARCHAR}
        GROUP BY DATE_FORMAT(created_on, '%Y-%m')
    </select>

    <select id="getActiveMembersByMonth" parameterType="java.lang.String" resultType="java.util.HashMap">
        SELECT
            DATE_FORMAT(updated_on, '%Y-%m') AS createTime,
            COUNT(DISTINCT user_id) AS num
        from (
                 SELECT id as user_id, DATE_FORMAT(last_login_on, '%Y-%m-%d' ) as updated_on FROM users WHERE last_login_on &gt;= #{startTime,jdbcType=VARCHAR} AND last_login_on &lt;= #{endTime,jdbcType=VARCHAR}
                 UNION ALL
                 SELECT DISTINCT user_id, DATE_FORMAT(updated_at, '%Y-%m-%d' ) as updated_on FROM user_actions WHERE updated_at &gt;= #{startTime,jdbcType=VARCHAR} AND updated_at &lt;= #{endTime,jdbcType=VARCHAR}
                 UNION ALL
                 SELECT DISTINCT user_id, DATE_FORMAT(updated_at, '%Y-%m-%d' ) as updated_on FROM commit_logs WHERE updated_at &gt;= #{startTime,jdbcType=VARCHAR} AND updated_at &lt;= #{endTime,jdbcType=VARCHAR}
             ) as t GROUP BY createTime
    </select>
    <select id="getUserIdByToken" resultType="java.lang.Long">
        select user_id
        from tokens
        where `value` = #{token}
    </select>
    <select id="getAdminStatus" resultType="java.lang.Boolean">
        select admin
        from users
        where id = #{userId}
    </select>
</mapper>