<?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.resourceLibrary.mapper.CompetitionResourceLibraryMapper">


    <select id="selectRegisterCondition" resultType="com.microservices.dms.resourceLibrary.domain.vo.UserActionDataVo">
        select '' as itemType, competition_info_id as itemId, user_id as userId, created_at as createAt,
        (select login from users where id = user_id) as userName from competition_users
        <where>
            <if test="itemId != null">
                AND competition_info_id = #{itemId}
            </if>
        </where>
    </select>

    <select id="selectOpusCondition" resultType="com.microservices.dms.resourceLibrary.domain.vo.UserActionDataVo">
        select '' as itemType, competition_info_id as itemId, user_id as userId, created_at as createAt,
        (select login from users where id = user_id) as userName from
        competition_users
        <where>
            <if test="itemId != null">
                AND competition_info_id = #{itemId}
            </if>
            <if test="status != null">
                AND `status` = #{status}
            </if>
        </where>

    </select>


    <select id="selectCompetitionListCondition"
            resultType="com.microservices.dms.resourceLibrary.domain.vo.CompetitionListVo">
        select title as name,id,`status`,visits,watchers_count,DATEDIFF(upload_date, start_at) as totalTaskDays
        from competition_infos
        <where>
            <if test="status != null">
                AND `status` = #{status}
            </if>
            <if test="name != null and name.trim() != ''">
                AND `title` like concat('%', #{name}, '%')
            </if>
        </where>
    </select>

    <select id="countRegisterCondition" resultType="java.lang.Long">
        select count(*) from competition_users
        <where>
            <if test="itemId != null">
                AND competition_info_id = #{itemId}
            </if>
        </where>
    </select>
    <select id="countOpusCondition" resultType="java.lang.Long">
        select count(*) from competition_users
        <where>
            <if test="itemId != null">
                AND competition_info_id = #{itemId}
            </if>
            <if test="status != null">
                AND `status` = #{status}
            </if>
        </where>
    </select>


    <resultMap type="CompetitionResourceLibrary" id="CompetitionResourceLibraryResult">
        <result property="id" column="id"/>
        <result property="openCompetitionId" column="open_competition_id"/>
        <result property="openCompetitionName" column="open_competition_name"/>
        <result property="competitionUserId" column="competition_user_id"/>
        <result property="competitionField" column="competition_field"/>
        <result property="competitionStatus" column="competition_status"/>
        <result property="competitionSubmissionName" column="competition_submission_name"/>
        <result property="submissionTime" column="submission_time"/>
        <result property="submitter" column="submitter"/>
        <result property="submissionType" column="submission_type"/>
        <result property="submittingUnit" column="submitting_unit"/>
        <result property="submissionSummary" column="submission_summary"/>
        <result property="submissionDetails" column="submission_details"/>
        <result property="contactPerson" column="contact_person"/>
        <result property="contactPhone" column="contact_phone"/>
        <result property="isTransferredToResultLibrary" column="is_transferred_to_result_library"/>
        <result property="transferredToResultLibraryTime" column="transferred_to_result_library_time"/>
        <result property="isFeaturedResult" column="is_featured_result"/>
        <result property="image" column="image"/>
        <result property="attachment" column="attachment"/>
        <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="selectCompetitionResourceLibraryVo">
        select id,
               open_competition_id,
               open_competition_name,
               competition_user_id,
               competition_field,
               competition_status,
               competition_submission_name,
               submission_time,
               submitter,
               submission_type,
               submitting_unit,
               submission_summary,
               submission_details,
               contact_person,
               contact_phone,
               is_transferred_to_result_library,
               transferred_to_result_library_time,
               is_featured_result,
               image,
               attachment,
               create_by,
               create_time,
               update_by,
               update_time
        from competition_resource_library
    </sql>

    <select id="selectCompetitionResourceLibraryList" parameterType="CompetitionResourceLibrary"
            resultType="com.microservices.dms.resourceLibrary.domain.CompetitionResourceLibrary">
        select crl.id,
        ci.id as open_competition_id,
        ci.title as open_competition_name,
        cu.id as competition_user_id,
        competition_field,
        ci.status as competition_status,
        cu.works_name as competition_submission_name,
        cu.created_at as submission_time,
        IFNULL(cu.leader,us.nickname) as submitter,
        submission_type,
        cu.org_name as submitting_unit,
        IFNULL(crl.submission_summary,cu.sub_item) as submission_summary,
        IFNULL(crl.submission_details,cu.sub_item) as submission_details,
        cu.leader as contact_person,
        cu.leader as leader,
        ci.identifier as identifier,
        cu.phone as contact_phone,
        is_transferred_to_result_library,
        transferred_to_result_library_time,
        is_featured_result,
        image,
        attachment,
        create_by,
        create_time,
        update_by,
        update_time,
        ci.is_expert_audit
        from competition_infos ci
        join (select * from competition_users where `status` = 3) cu on ci.id = cu.competition_info_id
        inner join users us on cu.user_id=us.id
        left join competition_resource_library crl on crl.competition_user_id = cu.id
        <where>
            <if test="openCompetitionId != null ">and open_competition_id = #{openCompetitionId}</if>
            <if test="openCompetitionName != null  and openCompetitionName != ''">and ci.title like
                concat('%', #{openCompetitionName}, '%')
            </if>
            <if test="competitionUserId != null ">and competition_user_id = #{competitionUserId}</if>
            <if test="competitionField != null  and competitionField != ''">and competition_field =
                #{competitionField}
            </if>
            <if test="competitionStatus != null  and competitionStatus != ''">and competition_status =
                #{competitionStatus}
            </if>
            <if test="competitionSubmissionName != null  and competitionSubmissionName != ''">and
                competition_submission_name like concat('%', #{competitionSubmissionName}, '%')
            </if>
            <if test="submissionTime != null ">and submission_time = #{submissionTime}</if>
            <if test="submitter != null  and submitter != ''">and submitter = #{submitter}</if>
            <if test="submissionType != null  and submissionType != ''">and submission_type = #{submissionType}</if>
            <if test="submittingUnit != null  and submittingUnit != ''">and submitting_unit = #{submittingUnit}</if>
            <if test="submissionSummary != null  and submissionSummary != ''">and submission_summary =
                #{submissionSummary}
            </if>
            <if test="submissionDetails != null  and submissionDetails != ''">and submission_details =
                #{submissionDetails}
            </if>
            <if test="contactPerson != null  and contactPerson != ''">and contact_person = #{contactPerson}</if>
            <if test="contactPhone != null  and contactPhone != ''">and contact_phone = #{contactPhone}</if>
            <if test="isTransferredToResultLibrary != null ">and is_transferred_to_result_library =
                #{isTransferredToResultLibrary}
            </if>
            <if test="transferredToResultLibraryTime != null ">and transferred_to_result_library_time =
                #{transferredToResultLibraryTime}
            </if>
            <if test="isFeaturedResult != null ">and is_featured_result = #{isFeaturedResult}</if>
            <if test="image != null  and image != ''">and image = #{image}</if>
            <if test="attachment != null  and attachment != ''">and attachment = #{attachment}</if>
        </where>
        order by is_transferred_to_result_library,create_time desc,submission_time desc
    </select>

    <select id="selectCompetitionResourceLibraryById" parameterType="Long" resultMap="CompetitionResourceLibraryResult">
        <include refid="selectCompetitionResourceLibraryVo"/>
        where id = #{id}
    </select>

    <select id="getAttachments" resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select uuid as v, filename as k
        from attachments
        where container_type = 'CompetitionUser'
          and container_id = #{id}
    </select>
    <select id="getTotalOpenCompetitions" resultType="java.lang.Long">
        select count(distinct ci.id)
        from competition_infos ci
                 join (select * from competition_users where `status` = 3) cu on ci.id = cu.competition_info_id
    </select>
    <select id="getTotalCompetitionAchievements" resultType="java.lang.Long">
        select count(*)
        from competition_infos ci
                 join (select * from competition_users where `status` = 3) cu on ci.id = cu.competition_info_id
                 left join competition_resource_library crl on crl.competition_user_id = cu.id
        where crl.is_transferred_to_result_library = 1
    </select>
    <select id="getTotalCompetitionSubmissions" resultType="java.lang.Long">
        select count(*)
        from competition_infos ci
                 join (select * from competition_users where `status` = 3) cu on ci.id = cu.competition_info_id
    </select>

    <select id="getTotalFinishedCompetitions" resultType="java.lang.Long">
        select count(distinct ci.id)
        from competition_infos ci
                 join (select * from competition_users where `status` = 3) cu on ci.id = cu.competition_info_id
        where ci.upload_date &lt; now()
    </select>
    <select id="getReviewCompetitionCount" resultType="java.lang.Long">
        select count(distinct ci.id)
        from competition_infos ci
                 join (select * from competition_users where `status` = 3) cu on ci.id = cu.competition_info_id
        where ci.is_expert_audit = 1
    </select>

    <select id="getCompetitionFinish" resultType="java.lang.Long">
        select COUNT(*) from competition_infos where upload_date &lt; now()
    </select>

    <select id="getCompetitionEnroll" resultType="java.lang.Long">
        select count(user_id) from competition_users
    </select>

    <select id="getCompetitionUnderway" resultType="java.lang.Long">
        select COUNT(*) from competition_infos where upload_date &gt;= now() and start_at &lt;= now()
    </select>

    <select id="getCompetitionSubmitCount" resultType="java.lang.Long">
        select COUNT(*)
        from attachments
        where container_type = 'CompetitionUser'
    </select>

    <select id="getCompetitionTransferCount" resultType="java.lang.Long">
        select count(1) from competition_users cu
        inner join competition_infos ci on cu.competition_info_id = ci.id
        inner join attachments at on cu.id=at.container_id
    </select>

    <select id="getCompetitionNeedAuditCount" resultType="java.lang.Long">
        select COUNT(*) from competition_infos where is_expert_audit = 1
    </select>

    <select id="getCompetitionStatisticYearly"
            resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select DATE_FORMAT(created_at, '%Y') as 'key', count(*) as 'value'
        from competition_infos
        group by DATE_FORMAT(created_at, '%Y')
        order by DATE_FORMAT(created_at, '%Y')
    </select>

    <select id="getCompetitionHot" resultType="java.util.Map">
        select t.title as name, achievementCount, enrollCount, paperCount, t.id as `id`,(achievementCount+enrollCount+paperCount) as total
        from (select c.title,
                     c.id,
                     c.updated_at,
                     (select count(*) from achievements a where source='3' and a.source_id = c.id )  as achievementCount,
                     (select count(*) from competition_users cu where cu.competition_info_id = c.id) as enrollCount,
                     (select count(*) from (select * from competition_users) cu inner join attachments on container_type = 'CompetitionUser' and container_id=cu.id where cu.competition_info_id=c.id) as paperCount
              from competition_infos c ) as t
        order by total desc,updated_at desc
        limit 0,10
    </select>

    <select id="getCompetitionYearlyPaperAdd"
            resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select DATE_FORMAT(created_at, '%Y-%m') as 'k', count(*) as 'v'
        from competition_infos
        where created_at &gt;= #{s}
          and created_at &lt;= #{e}
        group by DATE_FORMAT(created_at, '%Y-%m')
        order by DATE_FORMAT(created_at, '%Y-%m')
    </select>
    <select id="getCompetitionYearlyFinish"
            resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select DATE_FORMAT(upload_date, '%Y-%m') as 'k', count(*) as 'v'
        from competition_infos
        where upload_date &gt;= #{s}
          and upload_date &lt;= #{e}
        group by DATE_FORMAT(upload_date, '%Y-%m')
        order by DATE_FORMAT(upload_date, '%Y-%m')
    </select>

    <insert id="insertCompetitionResourceLibrary" parameterType="CompetitionResourceLibrary" useGeneratedKeys="true"
            keyProperty="id">
        insert into competition_resource_library
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="openCompetitionId != null">open_competition_id,</if>
            <if test="openCompetitionName != null">open_competition_name,</if>
            <if test="competitionUserId != null">competition_user_id,</if>
            <if test="competitionField != null">competition_field,</if>
            <if test="competitionStatus != null">competition_status,</if>
            <if test="competitionSubmissionName != null">competition_submission_name,</if>
            <if test="submissionTime != null">submission_time,</if>
            <if test="submitter != null">submitter,</if>
            <if test="submissionType != null">submission_type,</if>
            <if test="submittingUnit != null">submitting_unit,</if>
            <if test="submissionSummary != null">submission_summary,</if>
            <if test="submissionDetails != null">submission_details,</if>
            <if test="contactPerson != null">contact_person,</if>
            <if test="contactPhone != null">contact_phone,</if>
            <if test="isTransferredToResultLibrary != null">is_transferred_to_result_library,</if>
            <if test="transferredToResultLibraryTime != null">transferred_to_result_library_time,</if>
            <if test="isFeaturedResult != null">is_featured_result,</if>
            <if test="image != null">image,</if>
            <if test="attachment != null">attachment,</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="openCompetitionId != null">#{openCompetitionId},</if>
            <if test="openCompetitionName != null">#{openCompetitionName},</if>
            <if test="competitionUserId != null">#{competitionUserId},</if>
            <if test="competitionField != null">#{competitionField},</if>
            <if test="competitionStatus != null">#{competitionStatus},</if>
            <if test="competitionSubmissionName != null">#{competitionSubmissionName},</if>
            <if test="submissionTime != null">#{submissionTime},</if>
            <if test="submitter != null">#{submitter},</if>
            <if test="submissionType != null">#{submissionType},</if>
            <if test="submittingUnit != null">#{submittingUnit},</if>
            <if test="submissionSummary != null">#{submissionSummary},</if>
            <if test="submissionDetails != null">#{submissionDetails},</if>
            <if test="contactPerson != null">#{contactPerson},</if>
            <if test="contactPhone != null">#{contactPhone},</if>
            <if test="isTransferredToResultLibrary != null">#{isTransferredToResultLibrary},</if>
            <if test="transferredToResultLibraryTime != null">#{transferredToResultLibraryTime},</if>
            <if test="isFeaturedResult != null">#{isFeaturedResult},</if>
            <if test="image != null">#{image},</if>
            <if test="attachment != null">#{attachment},</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="updateCompetitionResourceLibrary" parameterType="CompetitionResourceLibrary">
        update competition_resource_library
        <trim prefix="SET" suffixOverrides=",">
            <if test="openCompetitionId != null">open_competition_id = #{openCompetitionId},</if>
            <if test="openCompetitionName != null">open_competition_name = #{openCompetitionName},</if>
            <if test="competitionUserId != null">competition_user_id = #{competitionUserId},</if>
            <if test="competitionField != null">competition_field = #{competitionField},</if>
            <if test="competitionStatus != null">competition_status = #{competitionStatus},</if>
            <if test="competitionSubmissionName != null">competition_submission_name = #{competitionSubmissionName},
            </if>
            <if test="submissionTime != null">submission_time = #{submissionTime},</if>
            <if test="submitter != null">submitter = #{submitter},</if>
            <if test="submissionType != null">submission_type = #{submissionType},</if>
            <if test="submittingUnit != null">submitting_unit = #{submittingUnit},</if>
            <if test="submissionSummary != null">submission_summary = #{submissionSummary},</if>
            <if test="submissionDetails != null">submission_details = #{submissionDetails},</if>
            <if test="contactPerson != null">contact_person = #{contactPerson},</if>
            <if test="contactPhone != null">contact_phone = #{contactPhone},</if>
            <if test="isTransferredToResultLibrary != null">is_transferred_to_result_library =
                #{isTransferredToResultLibrary},
            </if>
            <if test="transferredToResultLibraryTime != null">transferred_to_result_library_time =
                #{transferredToResultLibraryTime},
            </if>
            <if test="isFeaturedResult != null">is_featured_result = #{isFeaturedResult},</if>
            <if test="image != null">image = #{image},</if>
            <if test="attachment != null">attachment = #{attachment},</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="deleteCompetitionResourceLibraryById" parameterType="Long">
        delete
        from competition_resource_library
        where id = #{id}
    </delete>

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

</mapper>
