<?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.dms.behaviorImage.mapper.BehaviorImageMapper">

    <sql id="getBehaviorSumListByType" >
    </sql>

    <select id="getBehaviorSumListByType" resultType="com.microservices.dms.behaviorImage.domain.AchievementBehaviorSumVo">
        select a.clickSum,b.watchSum,c.searchSum,d.favoriteSum,e.downloadSum from (
            (select count(1) as clickSum from clickers where click_type='Achievements') a,
            (select count(1) as watchSum  from watchers  where watchable_type='Achievements') b,
            (select count(1) as searchSum from searchers where search_type='Achievements') c,
            (select count(1) as favoriteSum  from favorites  where favorite_type='Achievements' ) d,
            (select count(1) as downloadSum  from downloads  where download_type='Achievements' ) e
        )
    </select>

    <select id="getWeightListByType" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select behavior_code as "key" ,behavior_weight as "value"
        from behavior_image_weight
        where image_type = #{achievementsImage}
    </select>

    <select id="getSearchSumByAchievementId" resultType="long">
        select count(1) as searchSum from searchers where search_type = #{achievementsImage}  and search_id = #{id}
    </select>

    <select id="getFavoriteSumByAchievementId" resultType="long">
        select count(1) as favoriteSum from favorites where favorite_type = #{achievementsImage}  and favorite_id = #{id}
    </select>

    <select id="getWatchSumByAchievementId" resultType="long">
        select count(1) as watchSum from watchers where watchable_type = #{achievementsImage}  and watchable_id = #{id}
    </select>

    <select id="getFileDownloadSumByAchievementId" resultType="long">
        select count(1)  from downloads where download_type = #{achievementsImage}  and download_id = #{id}
    </select>

    <select id="getClickSumByAchievementId" resultType="long">
        select count(1) as clickSum from clickers where click_type = #{achievementsImage}  and click_id = #{id}
    </select>

    <resultMap type="BehaviorImageWeight" id="BehaviorImageWeightResult">
        <result property="id"    column="id"    />
        <result property="behaviorCode"    column="behavior_code"    />
        <result property="imageType"    column="image_type"    />
        <result property="behaviorName"    column="behavior_name"    />
        <result property="behaviorWeight"    column="behavior_weight"    />
        <result property="remark"    column="remark"    />
        <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>

    <sql id="selectBehaviorImageWeightVo">
        select id, behavior_code, image_type, behavior_name, behavior_weight, remark, create_by, create_time, update_by, update_time from behavior_image_weight
    </sql>

    <select id="selectBehaviorImageWeightList" parameterType="BehaviorImageWeight" resultMap="BehaviorImageWeightResult">
        <include refid="selectBehaviorImageWeightVo"/>
        <where>
            <if test="behaviorCode != null  and behaviorCode != ''"> and behavior_code = #{behaviorCode}</if>
            <if test="imageType != null  and imageType != ''"> and image_type = #{imageType}</if>
            <if test="behaviorName != null  and behaviorName != ''"> and behavior_name like concat('%', #{behaviorName}, '%')</if>
            <if test="behaviorWeight != null  and behaviorWeight != ''"> and behavior_weight = #{behaviorWeight}</if>
        </where>
    </select>

    <select id="selectBehaviorImageWeightById" parameterType="Long" resultMap="BehaviorImageWeightResult">
        <include refid="selectBehaviorImageWeightVo"/>
        where id = #{id}
    </select>

    <insert id="insertBehaviorImageWeight" parameterType="BehaviorImageWeight" useGeneratedKeys="true" keyProperty="id">
        insert into behavior_image_weight
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="behaviorCode != null">behavior_code,</if>
            <if test="imageType != null and imageType != ''">image_type,</if>
            <if test="behaviorName != null">behavior_name,</if>
            <if test="behaviorWeight != null">behavior_weight,</if>
            <if test="remark != null">remark,</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="behaviorCode != null">#{behaviorCode},</if>
            <if test="imageType != null and imageType != ''">#{imageType},</if>
            <if test="behaviorName != null">#{behaviorName},</if>
            <if test="behaviorWeight != null">#{behaviorWeight},</if>
            <if test="remark != null">#{remark},</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="updateBehaviorImageWeight" parameterType="BehaviorImageWeight">
        update behavior_image_weight
        <trim prefix="SET" suffixOverrides=",">
            <if test="behaviorCode != null">behavior_code = #{behaviorCode},</if>
            <if test="imageType != null and imageType != ''">image_type = #{imageType},</if>
            <if test="behaviorName != null">behavior_name = #{behaviorName},</if>
            <if test="behaviorWeight != null">behavior_weight = #{behaviorWeight},</if>
            <if test="remark != null">remark = #{remark},</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="deleteBehaviorImageWeightById" parameterType="Long">
        delete from behavior_image_weight where id = #{id}
    </delete>

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

    <sql id="selectActivityUserLibraryVo">
        select u.id as "userId",u.login as "loginName",u.nickname as "userName",u.is_expert as "isExpert",u.authentication as "isAuth" from users u
    </sql>

    <select id="selectActivityUserLibraryList" resultType="com.microservices.dms.behaviorImage.domain.ActivityUserLibrary">
        <include refid="selectActivityUserLibraryVo"/>
        <where>
            <if test="userName != null  and userName != ''"> and nickname like concat('%', #{userName}, '%')</if>
        </where>
    </select>

    <select id="selectActivityUserLibraryListAll"  resultType="com.microservices.dms.behaviorImage.domain.ActivityUserLibrary">
        select res.userId,res.loginName,res.userName,res.isExpert,res.isAuth,res.relatedCompetition,res.relatedTask,res.p1+res.p2+res.p3 as "relatedProject" from (
                  select u.id as "userId",u.login as "loginName",u.nickname as "userName",u.is_expert as "isExpert",u.authentication as "isAuth",
                         (select count(1) as "value" from tasks where is_delete='0' and status between 3 and 8 and user_id =u.id) as "relatedTask",
                         (select count(1)  from  competition_infos  ci inner join competition_users cu on ci.id = cu.competition_info_id where cu.user_id =u.id) as "relatedCompetition",
                         (SELECT count(1) as "pCount" FROM `projects` WHERE id != 0 AND user_id =u.id) as p1,
              ( SELECT count(1) as "pCount"
                FROM `projects`
                         INNER JOIN `members` ON `members`.`project_id` = `projects`.`id`
                WHERE `projects`.`id` != 0 AND `projects`.`user_id` !=u.id AND `members`.`user_id` =u.id) as p2,
              (SELECT count(1) as "pCount"
               FROM `projects`
                        INNER JOIN `team_projects` ON `team_projects`.`project_id` = `projects`.`id`
                        INNER JOIN `teams` ON `teams`.`id` = `team_projects`.`team_id`
                        INNER JOIN `team_users` ON `team_users`.`team_id` = `teams`.`id`
               WHERE `projects`.`id` != 0  AND `team_users`.`user_id` =u.id) as p3
            from users u) res
        where res.relatedCompetition >0 or  res.relatedTask >0 or res.p1+res.p2+res.p3>0
    </select>

    <select id="getrelatedProjectCount" resultType="long">
        select sum(r.pCount) from (
                                      SELECT count(1) as "pCount" FROM `projects` WHERE id != 0 AND user_id = #{userId}
                                      union all
                                      SELECT count(1) as "pCount"
                                      FROM `projects`
                                               INNER JOIN `members` ON `members`.`project_id` = `projects`.`id`
                                      WHERE `projects`.`id` != 0 AND `projects`.`user_id` != #{userId} AND `members`.`user_id` = #{userId}
                                      union all
                                      SELECT count(1) as "pCount"
                                      FROM `projects`
                                               INNER JOIN `team_projects` ON `team_projects`.`project_id` = `projects`.`id`
                                               INNER JOIN `teams` ON `teams`.`id` = `team_projects`.`team_id`
                                               INNER JOIN `team_users` ON `team_users`.`team_id` = `teams`.`id`
                                      WHERE `projects`.`id` != 0  AND `team_users`.`user_id` = #{userId}
                                  )r
    </select>



    <select id="getrelatedTaskCount"  resultType="long">
        select count(1) as "value" from tasks where is_delete='0' and status between 3 and 8 and user_id = #{userId}
    </select>

    <select id="getrelatedCompetitionCount"  resultType="long">
        select count(1)  from  competition_infos  ci
                                   inner join competition_users cu on ci.id = cu.competition_info_id
        where cu.user_id = #{userId}
    </select>

    <select id="getrelatedMemoCount" resultType="long">
        select sum(r.value) from (
                                     select COUNT(*) as value
                                     from memos
                                     where destroy_status is null and hidden = 0 and published_at is not null and root_id is null and author_id = #{userId}
                                     union all
                                     select COUNT(*) as value
                                     from memos
                                     where root_id is not null and author_id = #{userId}) r
    </select>

    <select id="getUserProjecttatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select * from (
                          SELECT 'createProjectCount' as `key`, count(1) as " value" FROM `projects` WHERE id != 0 AND user_id = 1
                          union all
                          select 'joinProjectCount' as `key`,sum(r.pCount) as 'value' from (
                                                                                               SELECT count(1) as "pCount"
                                                                                               FROM `projects`
                                                                                                        INNER JOIN `members` ON `members`.`project_id` = `projects`.`id`
                                                                                               WHERE `projects`.`id` != 0 AND `projects`.`user_id` != 1 AND `members`.`user_id` = 1
                                                                                               union all
                                                                                               SELECT count(1) as "pCount"
                                                                                               FROM `projects`
                                                                                                        INNER JOIN `team_projects` ON `team_projects`.`project_id` = `projects`.`id`
                                                                                                        INNER JOIN `teams` ON `teams`.`id` = `team_projects`.`team_id`
                                                                                                        INNER JOIN `team_users` ON `team_users`.`team_id` = `teams`.`id`
                                                                                               WHERE `projects`.`id` != 0  AND `team_users`.`user_id` = 1
                                                                                           )r
                          union all
                          select 'forkProjectCount' as `key`,COUNT(*) as value from fork_users where user_id = #{userId}
                          union all
                          select 'issueSum' as `key`,COUNT(*) as value  from issues where (author_id  = #{userId}  or assigned_to_id = #{userId})
                          union all
                          select 'prSum' as `key`,COUNT(*) as value  from pull_requests where user_id  = #{userId}
                          union all
                          select 'commitSum' as `key`, COUNT(*) as value  from commit_logs where user_id  = #{userId}
                          union all
                          select 'watchProjectCount' as `key`, count(1) as "value" from watchers where user_id  = #{userId} and watchable_type='Project'
                          union all
                          select 'praiseProjectCount' as `key`,count(1) as "value"  from praise_treads where user_id  = #{userId} and praise_tread_object_type ='Project'
                      )t
    </select>
    <select id="getUserTaskStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select 'createTaskCount' as `key`,count(1) as "value" from tasks where is_delete='0' and status between 3 and 8 and user_id = #{userId}
        union all
        select  'submitPapersCount' as `key`,count(1) as "value" from papers p
                                                                          inner join tasks t on p.task_id = t.id
        where p.user_id = #{userId}  and t.is_delete='0' and t.status between 3 and 8
        union all
        select  'watchTaskCount' as `key`,count(1) as "value"  from watchers where user_id = #{userId}  and  watchable_type='MakerTask'
    </select>

    <select id="getUserCompStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select 'joinCompetitionCount' as `key`,count(1) as  "value" from  competition_infos  ci
                                                                              inner join competition_users cu on ci.id = cu.competition_info_id
        where cu.user_id = #{userId}
        union all
        select 'signUpCount' as `key`,count(1) as  "value" from competition_users where user_id = #{userId}
        union all
        select 'submitWorkCount' as `key`,count(1) as  "value" from competition_users where  status='3' and user_id = #{userId}
        union all
        select  'watchCompetitionCount' as `key`,count(1) as "value"  from watchers where user_id = #{userId}  and  watchable_type='CompetitionInfo'
    </select>

    <select id="getUserMemoStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select 'createMemoCount' as `key`,COUNT(*) as value from memos
        where  destroy_status is null and hidden=0 and published_at is not null and root_id is null and author_id = #{userId}
        union all
        select  'replayMemoCount' as `key`,COUNT(*) as value from memos where root_id is not null and author_id = #{userId}
        union all
        select 'watchMemoCount' as `key`, count(1) as "value" from watchers where user_id = #{userId} and watchable_type='Memo'
    </select>

    <select id="getUserExperttatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select 'favoriteExpertCount' as `key`, count(1) as "value" from favorites where user_id = #{userId} and favorite_type='Expert'
        union all
        select 'watchedExpertCount' as `key`, count(1) as "value" from watchers where user_id = #{userId} and watchable_type='Expert'
        union all
        select 'aduitCompetitionCount' as `key`, count(1) as "value" from task_expert t
                                                                              inner join experts e on t.expert_id = e.id
                                                                              inner join users u on e.user_id = u.id
        where t.container_type = 2 and u.id = #{userId} and e.is_delete='0'
        union all
        select 'aduitTaskCount' as `key`, count(1) as "value" from task_expert t
                                                                       inner join experts e on t.expert_id = e.id
                                                                       inner join users u on e.user_id = u.id
        where t.container_type = 1 and u.id = #{userId} and e.is_delete='0'
    </select>

    <select id="getUserTypeStatistic" resultType="com.microservices.dms.behaviorImage.domain.UserTypeTotalVo">
        select t.resultType,sum(watchCount) as "watchSum",sum(favoriteCount) as "favoriteSum",
               sum(downloadCount) as "downloadSum",sum(clickCount) as "clickSum",sum(searchCount) as "searchSum",
               sum(praiseCount)  as "praiseSum"
        from (
                 select r.resultType,
                        case when r.`key`='w' then r.resultCount else 0 end as "watchCount",
                        case when r.`key`='f' then r.resultCount else 0 end as "favoriteCount",
                        case when r.`key`='d' then r.resultCount else 0 end as "downloadCount",
                        case when r.`key`='c' then r.resultCount else 0 end as "clickCount",
                        case when r.`key`='s' then r.resultCount else 0 end as "searchCount",
                        case when r.`key`='p' then r.resultCount else 0 end as "praiseCount"
                 from (
                          select 'w' as `key`, count(1) as "resultCount", watchable_type as "resultType" from watchers where user_id = #{userId} group by watchable_type
                          union all
                          select 'f' as `key`,count(1) as "resultCount",favorite_type  as "resultType" from favorites where user_id = #{userId} group by favorite_type
                          union all
                          select 'd' as `key`,count(1) as "resultCount" ,download_type  as "resultType" from downloads where user_id = #{userId} group by download_type
                          union all
                          select 'c' as `key`,count(1) as "resultCount" ,click_type  as "resultType" from clickers  where user_id = #{userId} group by click_type
                          union all
                          select 'c' as `key`,count(1) as "resultCount" ,search_type  as "resultType" from searchers where user_id = #{userId} group by search_type
                          union all
                          select 'p' as `key`,count(1) as "resultCount" ,praise_tread_object_type  as "resultType" from praise_treads where user_id = #{userId} group by praise_tread_object_type
                      )r
             )t group by  t.resultType
    </select>

    <select id="getActivityStatistic" resultType="com.microservices.dms.behaviorImage.domain.ActivityUserTotal">
        select count(1) as "totalUser",sum(r.isExpert) as "totalExpert",sum(r.enterprise_certification+r.authentication) as "authTotal" from (
              select res.userId,res.loginName,res.userName,res.isExpert,res.authentication,res.enterprise_certification,res.relatedCompetition,res.relatedTask,res.p1+res.p2+res.p3 as "relatedProject" from (
                      select u.id as "userId",u.login as "loginName",u.nickname as "userName",u.is_expert as "isExpert",u.authentication,u.enterprise_certification,
                           (select count(1) as "value" from tasks where is_delete='0' and status between 3 and 8 and user_id =u.id) as "relatedTask",
                           (select count(1)  from  competition_infos  ci inner join competition_users cu on ci.id = cu.competition_info_id where cu.user_id =u.id) as "relatedCompetition",
                           (SELECT count(1) as "pCount" FROM `projects` WHERE id != 0 AND user_id =u.id) as p1,
                            ( SELECT count(1) as "pCount"
                              FROM `projects`  INNER JOIN `members` ON `members`.`project_id` = `projects`.`id`
                              WHERE `projects`.`id` != 0 AND `projects`.`user_id` !=u.id AND `members`.`user_id` =u.id) as p2,
                            (SELECT count(1) as "pCount"
                             FROM `projects`
                              INNER JOIN `team_projects` ON `team_projects`.`project_id` = `projects`.`id`
                              INNER JOIN `teams` ON `teams`.`id` = `team_projects`.`team_id`
                              INNER JOIN `team_users` ON `team_users`.`team_id` = `teams`.`id`
                             WHERE `projects`.`id` != 0  AND `team_users`.`user_id` =u.id) as p3
                      from users u) res
        where res.relatedCompetition >0 or  res.relatedTask >0 or res.p1+res.p2+res.p3>0 )r
    </select>

    <select id="getUserBehaviorSum" resultType="com.microservices.dms.behaviorImage.domain.UserTypeTotalVo">
    select
        (select count(1) as "resultCount" from watchers where user_id = u.id ) as "watchSum",
        (select count(1) as "resultCount" from favorites where user_id = u.id ) as "favoriteSum",
        (select count(1) as "resultCount" from praise_treads where user_id = u.id ) as "praiseSum"
    from users u
    where u.id = #{userId}
    </select>
</mapper>