<?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.makerspace.mapper.TasksMapper">
    <resultMap id="BaseResultMap" type="com.microservices.dss.makerspace.domain.Tasks">
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="bounty" property="bounty" jdbcType="DECIMAL" />
        <result column="number" property="number" jdbcType="VARCHAR" />
        <result column="published" property="published" jdbcType="BIT" />
        <result column="visits" property="visits" jdbcType="INTEGER" />
        <result column="expired_at" property="expiredAt" jdbcType="TIMESTAMP" />
        <result column="published_at" property="publishedAt" jdbcType="TIMESTAMP" />
        <result column="publish_mode" property="publishMode" jdbcType="INTEGER" />
        <result column="collection_mode" property="collectionMode" jdbcType="INTEGER" />
        <result column="show_user_mode" property="showUserMode" jdbcType="INTEGER" />
        <result column="status" property="status" jdbcType="INTEGER" />
        <result column="papers_count" property="papersCount" jdbcType="INTEGER" />
        <result column="task_mode_id" property="taskModeId" jdbcType="INTEGER" />
        <result column="created_at" property="createdAt" jdbcType="TIMESTAMP" />
        <result column="updated_at" property="updatedAt" jdbcType="TIMESTAMP" />
        <result column="category_id" property="categoryId" jdbcType="INTEGER" />
        <result column="user_id" property="userId" jdbcType="INTEGER" />
        <result column="enterprise_name" property="enterpriseName" jdbcType="VARCHAR" />
        <result column="contact_name" property="contactName" jdbcType="VARCHAR" />
        <result column="contact_phone" property="contactPhone" jdbcType="VARCHAR" />
        <result column="show_user_status" property="showUserStatus" jdbcType="BIT" />
        <result column="agreement_signing" property="agreementSigning" jdbcType="INTEGER" />
        <result column="is_delete" property="isDelete" jdbcType="BIT" />
        <result column="ip" property="ip" jdbcType="VARCHAR" />
        <result column="paid_at" property="paidAt" jdbcType="TIMESTAMP" />
        <result column="make_public_at" property="makePublicAt" jdbcType="TIMESTAMP" />
        <result column="collecting_days" property="collectingDays" jdbcType="INTEGER" />
        <result column="choosing_days" property="choosingDays" jdbcType="INTEGER" />
        <result column="make_public_days" property="makePublicDays" jdbcType="INTEGER" />
        <result column="signing_days" property="signingDays" jdbcType="INTEGER" />
        <result column="paying_days" property="payingDays" jdbcType="INTEGER" />
        <result column="except_closed_boolean" property="exceptClosedBoolean" jdbcType="BIT" />
        <result column="is_proof_boolean" property="isProofBoolean" jdbcType="BIT" />
        <result column="cancel_status" property="cancelStatus" jdbcType="INTEGER" />
        <result column="is_signing_upload" property="isSigningUpload" jdbcType="INTEGER" />
        <result column="is_paying_upload" property="isPayingUpload" jdbcType="BIT" />
        <result column="upload_proof_at" property="uploadProofAt" jdbcType="TIMESTAMP" />
    </resultMap>
    <select id="getTotalTaskCount" resultType="java.lang.Integer">
        select count(*)
        from tasks
        where is_delete = 0 and `status` between 3 and 8
    </select>
    <select id="getTotalApplicantCount" resultType="java.lang.Integer">
        select count(*)
        from papers
        where is_delete = 0 and parent_id = 0
    </select>
    <select id="getCurrentTotalAwardAmount" resultType="java.lang.Float">
        select COALESCE(SUM(bounty), 0)
        from tasks
        where is_delete = 0 and `status` between 3 and 8
    </select>
    <select id="getTaskPublishedCountsByYear" resultType="map">
        SELECT
            YEAR(created_at) AS `year`,
            COUNT(*) AS taskCount
        FROM
            tasks
        WHERE
            is_delete = 0
          AND STATUS BETWEEN 3 AND 8
          AND YEAR(created_at) IN (YEAR(CURDATE()) - 2, YEAR(CURDATE()),YEAR(CURDATE()) - 1, YEAR(CURDATE()))
        GROUP BY
            YEAR(created_at)
        ORDER BY
            YEAR;
    </select>
    <select id="getTaskBountyCountsByYear" resultType="map">
        SELECT
            YEAR(created_at) AS `year`,
            FLOOR((sum(bounty) / 10000)) AS bountyCount
        FROM
            tasks
        WHERE
            is_delete = 0
          AND STATUS BETWEEN 3 AND 8
          AND YEAR(created_at) IN (YEAR(CURDATE()) - 2, YEAR(CURDATE()),YEAR(CURDATE()) - 1, YEAR(CURDATE()))
        GROUP BY
            YEAR(created_at)
        ORDER BY
            YEAR;
    </select>
    <select id="getNewAddedTasksByMonth" parameterType="java.lang.String" resultType="java.util.HashMap">
        SELECT
            DATE_FORMAT(created_at, '%Y-%m') AS createTime,
            COUNT(*) AS num
        FROM
            tasks
        WHERE
            created_at between #{startTime,jdbcType=VARCHAR}
                AND #{endTime,jdbcType=VARCHAR}
            AND is_delete = 0
            AND `status` BETWEEN 3 AND 8
        GROUP BY createTime
    </select>

    <select id="getNewAddedPapersByMonth" parameterType="java.lang.String" resultType="java.util.HashMap">
        SELECT
            DATE_FORMAT(created_at, '%Y-%m') AS createTime,
            COUNT(*) AS num
        FROM
            papers
        WHERE
            created_at between #{startTime,jdbcType=VARCHAR}
                AND #{endTime,jdbcType=VARCHAR}
          AND is_delete = 0
          AND parent_id = 0
        GROUP BY createTime
    </select>
    <select id="getTaskOperLogTopN" resultType="map">
        SELECT
            log_desc as log_desc,
            CASE
                WHEN TIMESTAMPDIFF(MINUTE, oper_time, NOW()) = 0 THEN CONCAT(TIMESTAMPDIFF(SECOND, oper_time, NOW()), '秒前')
                WHEN TIMESTAMPDIFF(HOUR, oper_time, NOW()) = 0 THEN CONCAT(TIMESTAMPDIFF(MINUTE, oper_time, NOW()), '分钟前')
                WHEN TIMESTAMPDIFF(DAY, oper_time, NOW()) = 0 THEN CONCAT(TIMESTAMPDIFF(HOUR, oper_time, NOW()), '小时前')
                ELSE CONCAT(TIMESTAMPDIFF(DAY, oper_time, NOW()), '天前')
                END AS time_ago,
            task_id
        FROM
            task_oper_log
        ORDER BY
            id DESC
            LIMIT #{topNum};
    </select>

    <select id="getOpeningTaskNumOfTaskStatus"
            resultType="com.microservices.dss.projects.domain.PieChartNameAndValue">
        select `status` as `name`,count(id) as `value`
        from tasks
        where is_delete = 0 and `status` between 3 and 7
        GROUP BY `status`
        order by `value` desc
        limit #{topNum};
    </select>
    <select id="getAllTaskNumOfTaskCategory"
            resultType="com.microservices.dss.projects.domain.PieChartNameAndValue">
        select c.name as `name`,count(t.id) as `value`
        from tasks as t
        left join categories as c on t.category_id = c.id
        where t.is_delete = 0 and t.status between 3 and 8
        GROUP BY c.`name`
        order by `value` desc
    </select>
    <select id="getBountyDistribution" resultType="map">
        SELECT
        CASE
        WHEN bounty &lt;= 5000 THEN '5k以下'
        WHEN bounty > 5000 AND bounty &lt;= 20000 THEN '5k-2w以下'
        WHEN bounty > 20000 AND bounty &lt;= 50000 THEN '2w-5W以下'
        WHEN bounty > 50000 AND bounty &lt;= 100000 THEN '5W-10W以下'
        WHEN bounty > 100000 AND bounty &lt;= 500000 THEN '10W-50W以下'
        ELSE '50W以上'
        END AS bounty_range,
        COUNT(*) AS task_count,
        ROUND(COUNT(CASE WHEN STATUS = 8 THEN 1 END) / COUNT(*) * 100, 0) AS paied_rate
        FROM
        tasks
        WHERE
        is_delete = 0 AND STATUS BETWEEN 3 AND 8
        GROUP BY
        bounty_range
        ORDER BY
        CASE bounty_range
        WHEN '5k以下' THEN 1
        WHEN '5k-2w以下' THEN 2
        WHEN '2w-5W以下' THEN 3
        WHEN '5W-10W以下' THEN 4
        WHEN '10W-50W以下' THEN 5
        ELSE 6
        END;
    </select>
    <select id="getTotalCompletedBounty" resultType="map">
        SELECT SUM(bounty) as 'total_paid_bounty'
        FROM tasks
        WHERE is_delete = 0 AND `status` = 8;
    </select>
    <select id="getTaskInfoByVisits" resultType="com.microservices.dss.makerspace.domain.vo.TaskHeatInfoVo">
        SELECT t.`name` AS taskName, t.published_at AS publishDate,
               COUNT(p.id) AS participantCount, trp.winner_name AS winnerNames, t.id AS taskId
        FROM tasks AS t
                 LEFT JOIN papers AS p ON p.task_id = t.id
                 LEFT JOIN task_result_proof AS trp ON trp.task_id = t.id
        WHERE t.is_delete = 0 AND t.status BETWEEN 3 AND 8
        GROUP BY t.`name`, t.published_at, t.visits, trp.winner_name, t.id
        ORDER BY t.visits DESC
        LIMIT #{topNum};
    </select>
    <select id="getTaskModeStatistic" resultType="java.util.Map">
        SELECT
            CASE
                   WHEN publish_mode = 0 THEN '自筹任务'
                   WHEN publish_mode = 1 THEN '统筹任务'
                   ELSE '其他'
                   END as taskMode,
            COUNT(id) AS taskCount,
            SUM(bounty) AS totalBounty
        FROM tasks
        WHERE is_delete = 0 AND `status` BETWEEN 3 AND 8
        GROUP BY publish_mode
    </select>
    <select id="getPaidAwardAmount" resultType="java.lang.Float">
        SELECT COALESCE(SUM(bounty), 0) AS total_bounty
        FROM tasks
        WHERE is_delete = 0 AND `status` = 8 and except_closed_boolean = 0;
    </select>
    <select id="getTotalVisits" resultType="java.lang.Integer">
        SELECT COALESCE(SUM(visits), 0) AS total_visits
        FROM tasks
        WHERE is_delete = 0;
    </select>
    <select id="getTaskStatisticsByStatus" resultType="java.util.Map">
        <if test = "status == 3">
            select '成果征集中' AS status,sub1.total_tasks, sub1.total_bounty, sub2.total_applied_papers
            from
            (select count(t.id) as 'total_tasks', sum(t.bounty) as 'total_bounty'
            from tasks t where t.status = 3 and t.is_delete = 0) sub1,
            (select count(p.id) as 'total_applied_papers'
            from papers p left join tasks t on t.id = p.task_id and p.parent_id = 0 and p.check_status = 1
            WHERE t.status =3 AND t.is_delete =0) sub2
        </if>
        <if test = "status == 4">
            select '成果评选中' AS status,
                   sub1.total_tasks,
                   sub2.total_selected_papers,
                   sub3.total_participated_experts
            from
            (select count(t.id) as 'total_tasks'
            from tasks t where t.status = 4 and t.is_delete = 0) sub1,
            (select count(p.id) as 'total_selected_papers'
            from papers p
            left join tasks t on p.task_id = t.id and p.parent_id = 0 and p.check_status = 1) sub2,
            (select count(e.expert_id) as 'total_participated_experts'
            from tasks t
            left join
                (select distinct tega.expert_id , teg.container_id
                    from task_expert_group_assignments tega
                    left join task_expert_groups teg on tega.group_id = teg.id
                    left join task_scoring_rule str on teg.container_id = str.container_id
                    where teg.container_type = 2 and str.status in (-1,1,2)) e
            on t.id = e.container_id
            where t.status = 4 and t.is_delete = 0) sub3
        </if>
        <if test = "status == 5">
            SELECT
            '公示中' AS status,
            sub1.total_tasks,
            sub2.total_won_papers,
            sub1.total_bounty
            FROM
            (
            SELECT
            COUNT(DISTINCT t.id) AS 'total_tasks',
            SUM(t.bounty) AS 'total_bounty'
            FROM
            tasks t
            WHERE
            t.status = 5 AND t.is_delete = 0
            ) sub1,
            (
            SELECT
            COUNT(p.id) AS 'total_won_papers'
            FROM
            papers p
            LEFT JOIN tasks t ON t.id = p.task_id AND p.parent_id = 0 AND p.status = 2
            WHERE
            t.status = 5 AND t.is_delete = 0
            ) sub2;
        </if>
        <if test = "status == 6">
            select
            '协议签订中' AS status,
            sub1.total_tasks,
            sub2.total_signed_tasks,
            sub3.total_unsigned_tasks
            from
            (select count(t.id) as 'total_tasks'
            from tasks t where t.status = 6 and t.is_delete = 0) sub1,
            (select count(p.id) as 'total_signed_tasks'
            from papers p
            left join tasks t on t.id = p.task_id and p.parent_id = 0 and p.status = 2 and p.is_sign = 1
            where t.status = 6 and t.is_delete = 0) sub2,
            (select count(p.id) as 'total_unsigned_tasks'
            from papers p
            left join tasks t on t.id = p.task_id and p.parent_id = 0 and p.status = 2 and p.is_sign = 0
            where t.status = 6 and t.is_delete = 0) sub3
        </if>
        <if test = "status == 8">
            select '已完成' AS status, sub1.total_tasks, sub2.total_paid_bounty, sub3.total_won_applicants
            from
            (select count(t.id) as 'total_tasks'
            from tasks t where t.status = 8 and t.is_delete = 0) sub1,
            (select sum(t.bounty) as 'total_paid_bounty'
            from tasks t where t.status = 8 and t.is_delete = 0) sub2,
            (select count(p.id) as 'total_won_applicants'
            from papers p
            left join tasks t on t.id = p.task_id and p.parent_id = 0 and p.status = 2
            where t.status = 8 and t.is_delete = 0) sub3
        </if>
    </select>
    <select id="getTaskUserOperLogTopN" resultType="java.util.Map">
        SELECT
        log_desc as log_desc,
        CASE
        WHEN TIMESTAMPDIFF(MINUTE, oper_time, NOW()) = 0 THEN CONCAT(TIMESTAMPDIFF(SECOND, oper_time, NOW()), '秒前')
        WHEN TIMESTAMPDIFF(HOUR, oper_time, NOW()) = 0 THEN CONCAT(TIMESTAMPDIFF(MINUTE, oper_time, NOW()), '分钟前')
        WHEN TIMESTAMPDIFF(DAY, oper_time, NOW()) = 0 THEN CONCAT(TIMESTAMPDIFF(HOUR, oper_time, NOW()), '小时前')
        ELSE CONCAT(TIMESTAMPDIFF(DAY, oper_time, NOW()), '天前')
        END AS time_ago,
        task_id
        FROM
        task_oper_log
        WHERE
        oper_title in
        <foreach item="item" collection="taskLogOperations" open="(" separator="," close=")">
            #{item}
        </foreach>
        AND log_desc NOT LIKE '%未通过%'
        AND log_desc NOT LIKE '%驳回%'
        AND log_desc NOT LIKE '%失败%'
        ORDER BY
        id DESC
        LIMIT #{topNum};
    </select>

</mapper>