<?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.xinyilu.system.mapper.apploginfo.AppLogInfoMapper">

    <resultMap type="AppLogInfo" id="AppLogInfoResult">
        <result property="id" column="id"/>
        <result property="parentInfoId" column="parent_info_id"/>
        <result property="prescriptionVideoId" column="prescription_video_id"/>
        <result property="logIp" column="log_ip"/>
        <result property="logType" column="log_type"/>
        <result property="logTime" column="log_time"/>
        <result property="logDate" column="log_date"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <resultMap type="com.xinyilu.system.domain.vo.applogmanagestatistics.AppLogManageStatisticsVO"
               id="AppLogManageStatisticsVOResult">
        <result property="parentName" column="parent_name"/>
        <collection property="studentName" javaType="java.util.List" resultMap="StudentNameResult"/>
        <collection property="dateTimeList" javaType="java.util.List" resultMap="DateTimeListResult"/>
    </resultMap>

    <resultMap type="StudentInfo" id="StudentNameResult">
        <result property="studentName" column="student_name"/>
    </resultMap>

    <resultMap type="AppLogInfo" id="DateTimeListResult">
        <result property="logDate" column="log_date"/>
    </resultMap>


    <sql id="selectAppLogInfoVo">
        select id,
               parent_info_id,
               prescription_video_id,
               log_ip,
               log_type,
               log_time,
               log_date,
               create_by,
               create_time,
               update_by,
               update_time
        from app_log_info
    </sql>

    <select id="selectAppLogInfoList" parameterType="AppLogInfo" resultMap="AppLogInfoResult">
        <include refid="selectAppLogInfoVo"/>
        <where>
            <if test="parentInfoId != null ">
                and parent_info_id = #{parentInfoId}
            </if>
            <if test="prescriptionVideoId != null ">
                and prescription_video_id = #{prescriptionVideoId}
            </if>
            <if test="logIp != null  and logIp != ''">
                and log_ip = #{logIp}
            </if>
            <if test="logType != null  and logType != ''">
                and log_type = #{logType}
            </if>
            <if test="logTime != null ">
                and log_time = #{logTime}
            </if>
            <if test="logDate != null ">
                and log_date = #{logDate}
            </if>
        </where>
    </select>

    <select id="selectAppLogInfoById" parameterType="Long"
            resultMap="AppLogInfoResult">
        <include refid="selectAppLogInfoVo"/>
        where id = #{id}
    </select>

    <insert id="insertAppLogInfo" parameterType="AppLogInfo" useGeneratedKeys="true"
            keyProperty="id">
        insert into app_log_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="parentInfoId != null">parent_info_id,
            </if>
            <if test="prescriptionVideoId != null">prescription_video_id,
            </if>
            <if test="logIp != null">log_ip,
            </if>
            <if test="logType != null">log_type,
            </if>
            <if test="logTime != null">log_time,
            </if>
            <if test="logDate != null">log_date,
            </if>
            <if test="createBy != null">create_by,
            </if>
            <if test="createTime != null">create_time,
            </if>
            <if test="updateBy != null">update_by,
            </if>
            <if test="updateTime != null">update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="parentInfoId != null">#{parentInfoId},
            </if>
            <if test="prescriptionVideoId != null">#{prescriptionVideoId},
            </if>
            <if test="logIp != null">#{logIp},
            </if>
            <if test="logType != null">#{logType},
            </if>
            <if test="logTime != null">#{logTime},
            </if>
            <if test="logDate != null">#{logDate},
            </if>
            <if test="createBy != null">#{createBy},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
            <if test="updateBy != null">#{updateBy},
            </if>
            <if test="updateTime != null">#{updateTime},
            </if>
        </trim>
    </insert>

    <update id="updateAppLogInfo" parameterType="AppLogInfo">
        update app_log_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="parentInfoId != null">parent_info_id =
                #{parentInfoId},
            </if>
            <if test="prescriptionVideoId != null">prescription_video_id =
                #{prescriptionVideoId},
            </if>
            <if test="logIp != null">log_ip =
                #{logIp},
            </if>
            <if test="logType != null">log_type =
                #{logType},
            </if>
            <if test="logTime != null">log_time =
                #{logTime},
            </if>
            <if test="logDate != null">log_date =
                #{logDate},
            </if>
            <if test="createBy != null">create_by =
                #{createBy},
            </if>
            <if test="createTime != null">create_time =
                #{createTime},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteAppLogInfoById" parameterType="Long">
        delete
        from app_log_info
        where id = #{id}
    </delete>

    <delete id="deleteAppLogInfoByIds" parameterType="String">
        delete from app_log_info where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="appletVisitRecord" parameterType="com.xinyilu.system.domain.dto.AppLogManageStatisticsDTO"
            resultType="com.xinyilu.system.domain.vo.applogmanagestatistics.AppLogManageStatisticsVO">
        SELECT
        ali.id,
        ali.parent_info_id,
        (select parent_name from parent_info where id = ali.parent_info_id) parent_name,
        ali.log_ip,
        ali.log_date,
        ali.log_time,
        ali.log_type
        FROM
        app_log_info ali
        <where>
            <if test="parentInfoId != null">
                and ali.parent_info_id = #{parentInfoId}
            </if>
            <if test="classId != null">
                and ali.parent_info_id in (SELECT
                pi.id
                FROM
                parent_info pi
                LEFT JOIN parent_student_info psi ON pi.id = psi.parent_id
                LEFT JOIN student_info si ON psi.student_id = si.id
                where si.class_id = #{classId}
                GROUP BY pi.id)
            </if>
            <if test="startDate != null">
                and ali.log_date &gt;= #{startDate}
            </if>
            <if test="endDate != null">
                and ali.log_date &lt;= #{endDate}
            </if>
            <if test="loginType != null and loginType != ''">
                and ali.log_type = #{loginType}
            </if>
        </where>
    </select>

    <select id="appletPersonCount" parameterType="com.xinyilu.system.domain.dto.AppLogManageStatisticsDTO"
            resultType="com.xinyilu.system.domain.vo.applogmanagestatistics.AppLogManageStatisticsVO">
        SELECT
        ali.id,
        ali.parent_info_id,
        (SELECT parent_name FROM parent_info WHERE id = ali.parent_info_id) parent_name,
        (SELECT count(1) from app_log_info
        <where>
            parent_info_id = ali.parent_info_id and log_type = 'LOGIN'
            <if test="startDate != null">and log_date &gt;= #{startDate}</if>
            <if test="endDate != null">and log_date &lt;= #{endDate}</if>
        </where>
        ) loginCount,
        (SELECT count(1) from app_log_info
        <where>
            parent_info_id = ali.parent_info_id and log_type ='EXERCISE_PRESCRIPTION'
            <if test="startDate != null">and log_date &gt;= #{startDate}</if>
            <if test="endDate != null">and log_date &lt;= #{endDate}</if>
        </where>
        ) prescriptionCount,
        (SELECT count(1) from app_log_info
        <where>
            parent_info_id = ali.parent_info_id and log_type = 'EXERCISE_PRESCRIPTION_VIDEO'
            <if test="startDate != null">and log_date &gt;= #{startDate}</if>
            <if test="endDate != null">and log_date &lt;= #{endDate}</if>
        </where>
        ) prescriptionVideoCount
        FROM
        app_log_info ali
        <where>
            <if test="parentInfoId == null and classId == null and startDate != null and endDate != null ">
                <if test="kindergartenIdList != null and kindergartenIdList.size() > 0">
                    and ali.parent_info_id in(SELECT
                    pi.id
                    FROM
                    parent_info pi
                    LEFT JOIN parent_student_info psi ON pi.id = psi.parent_id
                    LEFT JOIN student_info si ON psi.student_id = si.id
                    WHERE
                    si.kindergarten_id in
                    <foreach collection="kindergartenIdList" item="kindergartenId" open="(" separator="," close=")">
                        #{kindergartenId}
                    </foreach>
                    GROUP BY
                    pi.id)
                </if>
            </if>
            <if test="parentInfoId != null">
                and ali.parent_info_id = #{parentInfoId}
            </if>
            <if test="classId != null">
                and ali.parent_info_id in (SELECT
                pi.id
                FROM
                parent_info pi
                LEFT JOIN parent_student_info psi ON pi.id = psi.parent_id
                LEFT JOIN student_info si ON psi.student_id = si.id
                where si.class_id = #{classId}
                GROUP BY pi.id)
            </if>
        </where>
        GROUP BY
        ali.parent_info_id
    </select>

    <select id="appletPersonCountByHomePage" parameterType="com.xinyilu.system.domain.dto.AppLogManageStatisticsDTO"
            resultType="com.xinyilu.system.domain.vo.applogmanagestatistics.AppLogManageStatisticsVO">
        SELECT
        ali.id,
        ali.parent_info_id,
        (SELECT parent_name FROM parent_info WHERE id = ali.parent_info_id) parent_name,
        (SELECT count(1) from app_log_info
        <where>
            parent_info_id = ali.parent_info_id and log_type = 'LOGIN'
        </where>
        ) loginCount,
        (SELECT count(1) from app_log_info
        <where>
            parent_info_id = ali.parent_info_id and log_type ='EXERCISE_PRESCRIPTION'
        </where>
        ) prescriptionCount,
        (SELECT count(1) from app_log_info
        <where>
            parent_info_id = ali.parent_info_id and log_type = 'EXERCISE_PRESCRIPTION_VIDEO'
        </where>
        ) prescriptionVideoCount
        FROM
        app_log_info ali
        <where>
            <if test="parentInfoId == null and classId == null">
                <if test="kindergartenIdList != null and kindergartenIdList.size() > 0">
                    and ali.parent_info_id in(SELECT
                    pi.id
                    FROM
                    parent_info pi
                    LEFT JOIN parent_student_info psi ON pi.id = psi.parent_id
                    LEFT JOIN student_info si ON psi.student_id = si.id
                    WHERE
                    si.kindergarten_id in
                    <foreach collection="kindergartenIdList" item="kindergartenId" open="(" separator="," close=")">
                        #{kindergartenId}
                    </foreach>
                    GROUP BY
                    pi.id)
                </if>
            </if>
            <if test="parentInfoId != null">
                and ali.parent_info_id = #{parentInfoId}
            </if>
            <if test="classId != null">
                and ali.parent_info_id in (SELECT
                pi.id
                FROM
                parent_info pi
                LEFT JOIN parent_student_info psi ON pi.id = psi.parent_id
                LEFT JOIN student_info si ON psi.student_id = si.id
                where si.class_id = #{classId}
                GROUP BY pi.id)
            </if>
        </where>
        GROUP BY
        ali.parent_info_id
        ORDER BY loginCount DESC LIMIT 10
    </select>

    <select id="appletKindergartenCount" parameterType="com.xinyilu.system.domain.dto.AppLogManageStatisticsDTO"
            resultType="com.xinyilu.system.domain.vo.applogmanagestatistics.AppLogManageStatisticsVO">
        SELECT
        alk.kindergarten_id,
        (SELECT kindergarten_name FROM kindergarten_info WHERE id = alk.kindergarten_id ) kindergarten_name,
        (SELECT count(1) FROM app_log_kindergarten_info
        <where>
            kindergarten_id = alk.kindergarten_id and log_type ='LOGIN'
            <if test="startDate != null">and log_date &gt;= #{startDate}</if>
            <if test="endDate != null">and log_date &lt;= #{endDate}</if>
        </where>
        ) loginCount,
        (SELECT count(1) FROM app_log_kindergarten_info
        <where>
            kindergarten_id = alk.kindergarten_id and log_type ='EXERCISE_PRESCRIPTION'
            <if test="startDate != null">and log_date &gt;= #{startDate}</if>
            <if test="endDate != null">and log_date &lt;= #{endDate}</if>
        </where>
        ) prescriptionCount,
        (SELECT count(1) FROM app_log_kindergarten_info
        <where>
            kindergarten_id = alk.kindergarten_id and log_type ='EXERCISE_PRESCRIPTION_VIDEO'
            <if test="startDate != null">and log_date &gt;= #{startDate}</if>
            <if test="endDate != null">and log_date &lt;= #{endDate}</if>
        </where>
        ) prescriptionVideoCount
        FROM
        app_log_kindergarten_info alk
        <where>
            <if test="kindergartenId == null and areaCode == null and startDate != null and endDate != null ">
                <if test="organizeIdList != null and organizeIdList.size() > 0">
                    and alk.organize_id in
                    <foreach collection="organizeIdList" item="organizeId" open="(" separator="," close=")">
                        #{organizeId}
                    </foreach>
                </if>
            </if>
            <if test="kindergartenId != null">
                and alk.kindergarten_id = #{kindergartenId}
            </if>
            <if test="areaCode != null and areaCode != ''">
                and alk.area_code = #{areaCode}
            </if>
            <if test="areaId != null ">
                and alk.area_id = #{areaId}
            </if>
        </where>
        GROUP BY
        alk.kindergarten_id
    </select>

    <select id="appletKindergartenCountByHomePage" parameterType="com.xinyilu.system.domain.dto.AppLogManageStatisticsDTO"
            resultType="com.xinyilu.system.domain.vo.applogmanagestatistics.AppLogManageStatisticsVO">
        SELECT
        alk.kindergarten_id,
        (SELECT kindergarten_name FROM kindergarten_info WHERE id = alk.kindergarten_id ) kindergarten_name,
        (SELECT count(1) FROM app_log_kindergarten_info
        <where>
            kindergarten_id = alk.kindergarten_id and log_type ='LOGIN'
        </where>
        ) loginCount,
        (SELECT count(1) FROM app_log_kindergarten_info
        <where>
            kindergarten_id = alk.kindergarten_id and log_type ='EXERCISE_PRESCRIPTION'
        </where>
        ) prescriptionCount,
        (SELECT count(1) FROM app_log_kindergarten_info
        <where>
            kindergarten_id = alk.kindergarten_id and log_type ='EXERCISE_PRESCRIPTION_VIDEO'
        </where>
        ) prescriptionVideoCount
        FROM
        app_log_kindergarten_info alk
        <where>
            <if test="kindergartenId == null and areaCode == null">
                <if test="organizeIdList != null and organizeIdList.size() > 0">
                    and alk.organize_id in
                    <foreach collection="organizeIdList" item="organizeId" open="(" separator="," close=")">
                        #{organizeId}
                    </foreach>
                </if>
            </if>
            <if test="kindergartenId != null">
                and alk.kindergarten_id = #{kindergartenId}
            </if>
            <if test="areaCode != null and areaCode != ''">
                and alk.area_code = #{areaCode}
            </if>
            <if test="areaId != null ">
                and alk.area_id = #{areaId}
            </if>
        </where>
        GROUP BY
        alk.kindergarten_id
        ORDER BY loginCount DESC LIMIT 10
    </select>

    <select id="selectClockStatistic"
            resultType="com.xinyilu.system.domain.vo.applogmanagestatistics.AppLogManageStatisticsVO">
        SELECT
        ali.parent_info_id,
        (SELECT parent_name from parent_info WHERE id = ali.parent_info_id) parent_name,
        ((SELECT count( DISTINCT log_date ) FROM app_log_info
        <where>
            log_type = 'EXERCISE_PRESCRIPTION'
            AND parent_info_id = ali.parent_info_id
            <if test="startDate != null">and ali.log_date &gt;= #{startDate}</if>
            <if test="endDate != null">and ali.log_date &lt;= #{endDate}</if>
        </where>
        ) + ( SELECT count( DISTINCT log_date ) FROM app_log_info
        <where>
            log_type = 'EXERCISE_PRESCRIPTION_VIDEO' AND
            parent_info_id = ali.parent_info_id
            <if test="startDate != null">and ali.log_date &gt;= #{startDate}</if>
            <if test="endDate != null">and ali.log_date &lt;= #{endDate}</if>
        </where>
        )) loginCount
        FROM
        app_log_info ali
        <where>
            <if test="parentInfoId != null">
                ali.parent_info_id = #{parentInfoId}
            </if>
            <if test="classId != null">
                and ali.parent_info_id in (SELECT pi.id FROM parent_info pi
                LEFT JOIN parent_student_info psi ON pi.id = psi.parent_id
                LEFT JOIN student_info si ON psi.student_id = si.id
                where si.class_id = #{classId}
                GROUP BY pi.id)
            </if>
        </where>
        GROUP BY
        ali.parent_info_id
    </select>

    <select id="selectCalendarStatistics"
            resultType="com.xinyilu.system.domain.vo.applogmanagestatistics.AppLogManageStatisticsVO"
            resultMap="AppLogManageStatisticsVOResult">
        select pi.parent_name,
               si.student_name,
               ali.log_date
        from app_log_info ali
                 LEFT JOIN parent_info pi ON pi.id = ali.parent_info_id
                 LEFT JOIN parent_student_info psi on pi.id = psi.parent_id
                 LEFT JOIN student_info si ON si.id = psi.student_id
        WHERE ali.parent_info_id = #{parentInfoId}
          and ali.log_date &gt;= #{startDate}
          and ali.log_date &lt;= #{endDate}
          and ali.log_type &lt;&gt; #{loginType}
    </select>
</mapper>
