<?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.fs.task.mapper.TaskInfoMapper">

    <resultMap type="TaskInfo" id="TaskInfoResult">
        <result property="id"    column="id"    />
        <result property="taskNo"    column="task_no"    />
        <result property="taskName"    column="task_name"    />
        <result property="deptId"    column="dept_id"    />
        <result property="projectId"    column="project_id"    />
        <result property="costShareId"    column="cost_share_id"    />
        <result property="taskType" column="task_type" />
        <result property="taskIntegral"    column="task_integral"    />
        <result property="taskCount"    column="task_count"    />
        <result property="taskUnit"    column="task_unit"    />
        <result property="taskStatus"    column="task_status"    />
        <result property="productId"    column="product_id"    />
        <result property="doctorId"    column="doctor_id"    />
        <result property="planStartTime"    column="plan_start_time"    />
        <result property="planEndTime"    column="plan_end_time"    />
        <result property="applyTime"    column="apply_time"    />
        <result property="createAuditTime"    column="create_audit_time"    />
        <result property="deliveryAuditTime"    column="delivery_audit_time"    />
        <result property="serviceConfirmStatus"    column="service_confirm_status"    />
        <result property="finishStatus"    column="finish_status"    />
        <result property="belongType"    column="belong_type"    />
        <result property="isSpotCheck"    column="is_spot_check"    />
        <result property="spotCheckUserId"    column="spot_check_user_id"    />
        <result property="spotCheckTime"    column="spot_check_time"    />
        <result property="auditStatus"    column="audit_status"    />
        <result property="isSupplementLive"    column="is_supplement_live"    />
        <result property="supplementLiveTaskId"    column="supplement_live_task_id"    />
        <result property="supplementLiveReason"    column="supplement_live_reason"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateBy"    column="update_by"    />
        <result property="deleted"    column="deleted"    />
        <result property="createAuditStatus"    column="create_audit_status"    />
        <result property="createAuditUserId"    column="create_audit_user_id"    />
        <result property="createAuditRemark"    column="create_audit_remark"    />
        <result property="finishAuditStatus"    column="finish_audit_status"    />
        <result property="finishAuditUserId"    column="finish_audit_user_id"    />
        <result property="finishAuditTime"    column="finish_audit_time"    />
        <result property="finishAuditRemark"    column="finish_audit_remark"    />
        <result property="createAuditInstanceId"    column="create_audit_instance_id"    />
        <result property="finishAuditInstanceId"    column="finish_audit_instance_id"    />
        <result property="caUserType"    column="ca_user_type"    />
        <result property="companyUserId"    column="company_user_id"    />

        <result property="deptName" column="dept_name"/>
        <result property="projectName" column="project_name"/>
        <result property="costShareName" column="cost_share_name"/>
        <result property="spotCheckUserName" column="spotCheckUserName"/>
        <result property="createAuditUserName" column="createAuditUserName" />
        <result property="finishAuditUserName" column="finishAuditUserName" />
        <result property="companyName" column="companyName" />
        <result property="productName" column="productName" />
        <result property="doctorName" column="doctorName" />
        <result property="remark" column="remark" />
    </resultMap>


    <sql id="selectTaskInfoVo">
        SELECT
        t.id,
        t.task_no,
        t.task_name,
        t.dept_id,
        t.project_id,
        t.cost_share_id,
        t.task_type,
        t.task_integral,
        t.task_count,
        t.task_unit,
        t.task_status,
        t.product_id,
        t.doctor_id,
        t.plan_start_time,
        t.plan_end_time,
        t.apply_time,
        t.create_audit_time,
        t.delivery_audit_time,
        t.service_confirm_status,
        t.finish_status,
        t.belong_type,
        t.is_spot_check,
        t.spot_check_user_id,
        t.spot_check_time,
        t.audit_status,
        t.is_supplement_live,
        t.supplement_live_task_id,
        t.supplement_live_reason,
        t.create_time,
        t.update_time,
        t.create_by,
        t.update_by,
        t.deleted,

        t.create_audit_status,
        t.create_audit_user_id,
        t.create_audit_remark,

        t.finish_audit_status,
        t.finish_audit_user_id,
        t.finish_audit_time,
        t.finish_audit_remark,

        t.create_audit_instance_id,
        t.finish_audit_instance_id,
        t.ca_user_type,
        t.company_user_id,
        t.remark,

        -- ===== 关联名称字段 =====
        d.dept_name                AS dept_name,
        p.project_name             AS project_name,
        cs.dept_name               AS cost_share_name,
        doc.doctor_name            AS doctorName,

        cu.nick_name               AS spotCheckUserName,
        cu2.nick_name              AS createAuditUserName,
        cu3.nick_name              AS finishAuditUserName,

        c.company_id               AS companyId,
        c.company_name             AS companyName,
        product.product_name       AS productName

        FROM task_info t

        LEFT JOIN company_dept d
        ON d.dept_id = t.dept_id

        LEFT JOIN project p
        ON p.id = t.project_id

        LEFT JOIN company_dept cs
        ON cs.dept_id = t.cost_share_id

        LEFT JOIN doctor doc
        ON doc.id = t.doctor_id

        LEFT JOIN company_user cu
        ON cu.user_id = t.spot_check_user_id

        LEFT JOIN company_user cu2
        ON cu2.user_id = t.create_audit_user_id

        -- ✅ 关键修正点：完成审核人
        LEFT JOIN company_user cu3
        ON cu3.user_id = t.finish_audit_user_id

        LEFT JOIN company c
        ON c.company_id = t.company_id

        LEFT JOIN company_product product
        ON product.id = t.product_id
    </sql>

    <select id="selectTaskInfoList" parameterType="TaskInfo" resultMap="TaskInfoResult">
        <include refid="selectTaskInfoVo"/>
        <where>
            <if test="companyId != null "> and t.company_id = #{companyId}</if>
            <if test="companyUserId != null "> and t.company_user_id = #{companyUserId}</if>
            <if test="taskNo != null  and taskNo != ''"> and t.task_no = #{taskNo}</if>
            <if test="taskName != null  and taskName != ''"> and t.task_name like concat('%', #{taskName}, '%')</if>
            <if test="deptId != null "> and t.dept_id = #{deptId}</if>
            <if test="projectId != null "> and t.project_id = #{projectId}</if>
            <if test="costShareId != null "> and t.cost_share_id = #{costShareId}</if>
            <if test="taskType != null">and t.task_type = #{taskType}</if>
            <if test="taskIntegral != null "> and t.task_integral = #{taskIntegral}</if>
            <if test="taskCount != null "> and t.task_count = #{taskCount}</if>
            <if test="taskUnit != null "> and t.task_unit = #{taskUnit}</if>
            <if test="taskStatus != null "> and t.task_status = #{taskStatus}</if>
            <if test="productId != null "> and t.product_id = #{productId}</if>
            <if test="doctorId != null "> and t.doctor_id = #{doctorId}</if>
            <if test="applyTime != null "> and t.apply_time = #{applyTime}</if>
            <if test="createAuditTime != null "> and t.create_audit_time = #{createAuditTime}</if>
            <if test="deliveryAuditTime != null "> and t.delivery_audit_time = #{deliveryAuditTime}</if>
            <if test="serviceConfirmStatus != null "> and t.service_confirm_status = #{serviceConfirmStatus}</if>
            <if test="finishStatus != null "> and t.finish_status = #{finishStatus}</if>
            <if test="belongType != null "> and t.belong_type = #{belongType}</if>
            <if test="isSpotCheck != null "> and t.is_spot_check = #{isSpotCheck}</if>
            <if test="spotCheckUserId != null "> and t.spot_check_user_id = #{spotCheckUserId}</if>
            <if test="spotCheckTime != null "> and t.spot_check_time = #{spotCheckTime}</if>
            <if test="auditStatus != null "> and t.audit_status = #{auditStatus}</if>
            <if test="isSupplementLive != null "> and t.is_supplement_live = #{isSupplementLive}</if>
            <if test="supplementLiveTaskId != null  and supplementLiveTaskId != ''"> and t.supplement_live_task_id = #{supplementLiveTaskId}</if>
            <if test="supplementLiveReason != null  and supplementLiveReason != ''"> and t.supplement_live_reason = #{supplementLiveReason}</if>
            <if test="deleted != null "> and t.deleted = #{deleted}</if>
            <if test="createAuditStatus != null "> and t.create_audit_status = #{createAuditStatus}</if>
            <if test="createAuditUserId != null "> and t.create_audit_user_id = #{createAuditUserId}</if>
            <if test="finishAuditStatus != null "> and t.finish_audit_status = #{finishAuditStatus}</if>
            <if test="finishAuditUserId != null "> and t.finish_audit_user_id = #{finishAuditUserId}</if>
            <if test="finishAuditTime != null "> and t.finish_audit_time = #{finishAuditTime}</if>
            <!--前端切换标签-->
            <if test="auditTab != null and auditTab != ''">
                <choose>
                    <!-- 创建待审核 -->
                    <when test="auditTab == 'CREATE_PENDING'">
                        and t.create_audit_status = 0
                    </when>
                    <!-- 创建审核完成 -->
                    <when test="auditTab == 'CREATE_DONE'">
                        and t.create_audit_status != 0
                    </when>
                    <!-- 完成待审核 -->
                    <when test="auditTab == 'FINISH_PENDING'">
                        and t.create_audit_status = 1
                        and t.finish_audit_status = 0
                    </when>
                    <!-- 完成审核完成 -->
                    <when test="auditTab == 'FINISH_DONE'">
                        and t.finish_audit_status != 0
                    </when>
                    <otherwise>
                    </otherwise>
                </choose>
            </if>

            <!-- 修改现有的申请时间范围查询 -->
            <if test="applyStartTime != null and applyStartTime != '' and applyEndTime != null and applyEndTime != ''">
                AND t.apply_time &gt;= #{applyStartTime}
                AND t.apply_time &lt;= #{applyEndTime}
            </if>
            <if test="timeType != null">
                <bind name="now" value="new java.util.Date()" />
                AND t.plan_start_time &lt;= #{now}
                AND t.plan_end_time &gt;= #{now}
            </if>

            <!-- 添加完成审核时间范围查询 -->
            <if test="finishAuditTimeStart != null and finishAuditTimeStart != '' and finishAuditTimeEnd != null and finishAuditTimeEnd != ''">
                AND t.finish_audit_time &gt;= #{finishAuditTimeStart}
                AND t.finish_audit_time &lt;= #{finishAuditTimeEnd}
            </if>

            <!-- 添加任务完成时间范围查询（需要确认对应的数据库字段名） -->
            <if test="taskFinishTimeStart != null and taskFinishTimeStart != '' and taskFinishTimeEnd != null and taskFinishTimeEnd != ''">
                AND t.finish_audit_time &gt;= #{taskFinishTimeStart}
                AND t.finish_audit_time &lt;= #{taskFinishTimeEnd}
            </if>
            <if test="selectType != null and selectType == 'kp'">
                AND (t.task_type = 4 or t.task_type = 5 or t.task_type = 6)
            </if>

            <!-- 新增 keywords 模糊匹配 -->
            <if test="keywords != null and keywords != ''">
                AND (doc.doctor_name LIKE CONCAT('%', #{keywords}, '%')
                OR doc.mobile LIKE CONCAT('%', #{keywords}, '%'))
            </if>

        </where>
        <!-- 添加排序：最新创建的任务在最前面 -->
        order by t.create_time desc
    </select>





    <select id="selectTaskInfoById" parameterType="Long" resultMap="TaskInfoResult">
        <include refid="selectTaskInfoVo"/>
        where t.id = #{id}
    </select>

    <insert id="insertTaskInfo" parameterType="TaskInfo" useGeneratedKeys="true" keyProperty="id">
        insert into task_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="taskNo != null and taskNo != ''">task_no,</if>
            <if test="taskName != null and taskName != ''">task_name,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="projectId != null">project_id,</if>
            <if test="costShareId != null">cost_share_id,</if>
            <if test="taskType  != null">task_type,</if>
            <if test="taskIntegral != null">task_integral,</if>
            <if test="taskCount != null">task_count,</if>
            <if test="taskUnit != null">task_unit,</if>
            <if test="taskStatus != null">task_status,</if>
            <if test="productId != null">product_id,</if>
            <if test="doctorId != null">doctor_id,</if>
            <if test="planStartTime != null">plan_start_time,</if>
            <if test="planEndTime != null">plan_end_time,</if>
            <if test="applyTime != null">apply_time,</if>
            <if test="createAuditTime != null">create_audit_time,</if>
            <if test="deliveryAuditTime != null">delivery_audit_time,</if>
            <if test="serviceConfirmStatus != null">service_confirm_status,</if>
            <if test="finishStatus != null">finish_status,</if>
            <if test="belongType != null">belong_type,</if>
            <if test="isSpotCheck != null">is_spot_check,</if>
            <if test="spotCheckUserId != null">spot_check_user_id,</if>
            <if test="spotCheckTime != null">spot_check_time,</if>
            <if test="auditStatus != null">audit_status,</if>
            <if test="isSupplementLive != null">is_supplement_live,</if>
            <if test="supplementLiveTaskId != null">supplement_live_task_id,</if>
            <if test="supplementLiveReason != null">supplement_live_reason,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="deleted != null">deleted,</if>
            <if test="createAuditStatus != null">create_audit_status,</if>
            <if test="createAuditUserId != null">create_audit_user_id,</if>
            <if test="createAuditRemark != null">create_audit_remark,</if>
            <if test="finishAuditStatus != null">finish_audit_status,</if>
            <if test="finishAuditUserId != null">finish_audit_user_id,</if>
            <if test="finishAuditTime != null">finish_audit_time,</if>
            <if test="finishAuditRemark != null">finish_audit_remark,</if>
            <if test="companyId != null">company_id,</if>
            <if test="createAuditInstanceId != null">create_audit_instance_id,</if>
            <if test="finishAuditInstanceId != null">finish_audit_instance_id,</if>
            <if test="caUserType != null">ca_user_type,</if>
            <if test="companyUserId != null">company_user_id,</if>
            <if test="targetId != null">target_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="taskNo != null and taskNo != ''">#{taskNo},</if>
            <if test="taskName != null and taskName != ''">#{taskName},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="projectId != null">#{projectId},</if>
            <if test="costShareId != null">#{costShareId},</if>
            <if test="taskType != null">#{taskType},</if>
            <if test="taskIntegral != null">#{taskIntegral},</if>
            <if test="taskCount != null">#{taskCount},</if>
            <if test="taskUnit != null">#{taskUnit},</if>
            <if test="taskStatus != null">#{taskStatus},</if>
            <if test="productId != null">#{productId},</if>
            <if test="doctorId != null">#{doctorId},</if>
            <if test="planStartTime != null">#{planStartTime},</if>
            <if test="planEndTime != null">#{planEndTime},</if>
            <if test="applyTime != null">#{applyTime},</if>
            <if test="createAuditTime != null">#{createAuditTime},</if>
            <if test="deliveryAuditTime != null">#{deliveryAuditTime},</if>
            <if test="serviceConfirmStatus != null">#{serviceConfirmStatus},</if>
            <if test="finishStatus != null">#{finishStatus},</if>
            <if test="belongType != null">#{belongType},</if>
            <if test="isSpotCheck != null">#{isSpotCheck},</if>
            <if test="spotCheckUserId != null">#{spotCheckUserId},</if>
            <if test="spotCheckTime != null">#{spotCheckTime},</if>
            <if test="auditStatus != null">#{auditStatus},</if>
            <if test="isSupplementLive != null">#{isSupplementLive},</if>
            <if test="supplementLiveTaskId != null">#{supplementLiveTaskId},</if>
            <if test="supplementLiveReason != null">#{supplementLiveReason},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="createAuditStatus != null">#{createAuditStatus},</if>
            <if test="createAuditUserId != null">#{createAuditUserId},</if>
            <if test="createAuditRemark != null">#{createAuditRemark},</if>
            <if test="finishAuditStatus != null">#{finishAuditStatus},</if>
            <if test="finishAuditUserId != null">#{finishAuditUserId},</if>
            <if test="finishAuditTime != null">#{finishAuditTime},</if>
            <if test="finishAuditRemark != null">#{finishAuditRemark},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="createAuditInstanceId != null">#{createAuditInstanceId},</if>
            <if test="finishAuditInstanceId != null">#{finishAuditInstanceId},</if>
            <if test="caUserType != null">#{caUserType},</if>
            <if test="companyUserId != null">#{companyUserId},</if>
            <if test="targetId != null">#{targetId},</if>
         </trim>
    </insert>

    <update id="updateTaskInfo" parameterType="TaskInfo">
        update task_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="taskNo != null and taskNo != ''">task_no = #{taskNo},</if>
            <if test="taskName != null and taskName != ''">task_name = #{taskName},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="projectId != null">project_id = #{projectId},</if>
            <if test="costShareId != null">cost_share_id = #{costShareId},</if>
            <if test="taskType != null">task_type = #{taskType},</if>
            <if test="taskIntegral != null">task_integral = #{taskIntegral},</if>
            <if test="taskCount != null">task_count = #{taskCount},</if>
            <if test="taskUnit != null">task_unit = #{taskUnit},</if>
            <if test="taskStatus != null">task_status = #{taskStatus},</if>
            <if test="productId != null">product_id = #{productId},</if>
            <if test="doctorId != null">doctor_id = #{doctorId},</if>
            <if test="planStartTime != null">plan_start_time = #{planStartTime},</if>
            <if test="planEndTime != null">plan_end_time = #{planEndTime},</if>
            <if test="applyTime != null">apply_time = #{applyTime},</if>
            <if test="createAuditTime != null">create_audit_time = #{createAuditTime},</if>
            <if test="deliveryAuditTime != null">delivery_audit_time = #{deliveryAuditTime},</if>
            <if test="serviceConfirmStatus != null">service_confirm_status = #{serviceConfirmStatus},</if>
            <if test="finishStatus != null">finish_status = #{finishStatus},</if>
            <if test="belongType != null">belong_type = #{belongType},</if>
            <if test="isSpotCheck != null">is_spot_check = #{isSpotCheck},</if>
            <if test="spotCheckUserId != null">spot_check_user_id = #{spotCheckUserId},</if>
            <if test="spotCheckTime != null">spot_check_time = #{spotCheckTime},</if>
            <if test="auditStatus != null">audit_status = #{auditStatus},</if>
            <if test="isSupplementLive != null">is_supplement_live = #{isSupplementLive},</if>
            <if test="supplementLiveTaskId != null">supplement_live_task_id = #{supplementLiveTaskId},</if>
            <if test="supplementLiveReason != null">supplement_live_reason = #{supplementLiveReason},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="deleted != null">deleted = #{deleted},</if>
            <if test="createAuditStatus != null">create_audit_status = #{createAuditStatus},</if>
            <if test="createAuditUserId != null">create_audit_user_id = #{createAuditUserId},</if>
            <if test="createAuditRemark != null">create_audit_remark = #{createAuditRemark},</if>
            <if test="finishAuditStatus != null">finish_audit_status = #{finishAuditStatus},</if>
            <if test="finishAuditUserId != null">finish_audit_user_id = #{finishAuditUserId},</if>
            <if test="finishAuditTime != null">finish_audit_time = #{finishAuditTime},</if>
            <if test="finishAuditRemark != null">finish_audit_remark = #{finishAuditRemark},</if>
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="createAuditInstanceId != null">create_audit_instance_id = #{createAuditInstanceId},</if>
            <if test="finishAuditInstanceId != null">finish_audit_instance_id = #{finishAuditInstanceId},</if>
            <if test="companyUserId != null">company_user_id = #{companyUserId},</if>
            <if test="targetId != null">target_id = #{targetId},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteTaskInfoById" parameterType="Long">
        delete from task_info where id = #{id}
    </delete>

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



    <select id="selectAuditTaskInfoVO"
            parameterType="java.lang.Long"
            resultType="com.fs.task.vo.audit_Info.AuditTaskInfoVO">

        select
        t.task_no                as taskNo,
        t.task_type                as taskType,
        t.task_name              as taskName,
        t.cost_share_id          as costShareId,
        cs.dept_name             as costShareName,
        t.task_integral          as taskIntegral,
        t.task_count             as taskCount,
        t.task_unit              as taskUnit,
        t.belong_type            as belongType,
        t.project_id              as projectId,
        t.doctor_id              as doctorId,
        t.create_audit_instance_id              as createAuditInstanceId,
        t.finish_audit_instance_id              as finishAuditInstanceId,
        t.company_user_id        as companyUserId,
        t.plan_start_time        as planStartTime,
        t.plan_end_time          as planEndTime,
        t.project_id             as projectId,
        p.project_name           as projectName,
        t.dept_id                as deptId,
        d.dept_name              as deptName,
        t.delivery_audit_time    as deliveryAuditTime
        from task_info t
        left join company_dept d
        on d.dept_id = t.dept_id
        left join project p
        on p.id = t.project_id
        left join company_dept cs
        on cs.dept_id = t.cost_share_id
        where t.id = #{taskId}
        and t.deleted = 0
    </select>


    <update id="updateSpotCheck" parameterType="com.fs.task.vo.TaskSpotCheckUpdateReqVO">
        update task_info
        set
        is_spot_check = #{isSpotCheck},
        spot_check_user_id = #{spotCheckUserId},
        spot_check_time = #{spotCheckTime},
        update_time = now()
        where id = #{id}
    </update>


    <select id="selectPendingTasksForGen" resultType="com.fs.task.domain.TaskInfo">
        SELECT t.*
        FROM task_info t
        WHERE t.deleted = 0
        AND t.company_id = #{companyId}
        AND t.dept_id = #{deptId}
        AND t.create_audit_status = 1
        AND t.finish_audit_status = 1
        AND t.service_confirm_status = 1
        AND t.doctor_id IS NOT NULL
        AND t.task_integral <![CDATA[ > ]]> 0
        AND t.task_type IN
        <foreach collection="taskTypes" item="x" open="(" separator="," close=")">
            #{x}
        </foreach>
        AND NOT EXISTS (
        SELECT 1 FROM service_order_task_rel r
        WHERE r.deleted = 0 AND r.task_id = t.id
        )
    </select>
    <select id="getTaskCompleteStats" resultType="com.fs.task.dto.TaskStatsDTO">
        -- 任务完成统计
        SELECT
            ti.dept_id as dept_id,
            ti.task_type as task_type,
            ti.doctor_id as doctor_id,
            ti.company_user_id as company_user_id,
            COUNT(DISTINCT ti.task_no) AS `count`,
            SUM(ti.task_integral) AS total_points
        FROM task_info ti
        <where>
            ti.deleted = 0
            <if test="companyId != null">
                AND ti.company_id = #{companyId}
            </if>
            <if test="companyUserId != null">
                AND ti.company_user_id = #{companyUserId}
            </if>
            <if test="startDate != null and endDate!= null">
                AND ti.create_time BETWEEN #{startDate} AND #{endDate}
            </if>
            <if test="taskType != null">
                and ti.task_type=#{taskType}
            </if>
            <if test="taskStatus != null">
                and ti.finish_audit_status=#{taskStatus}
            </if>
            AND ti.task_status = 1
            group by ti.dept_id,ti.task_type,ti.doctor_id,ti.company_user_id
        </where>
    </select>
    <select id="getTaskCreateStats" resultType="com.fs.task.dto.TaskStatsDTO">
        -- 任务完成统计
        SELECT
        ti.dept_id as dept_id,
        ti.task_type as task_type,
        ti.doctor_id as doctor_id,
        ti.company_user_id as company_user_id,
        COUNT(DISTINCT ti.task_no) AS `count`,
        SUM(ti.task_integral) AS total_points
        FROM task_info ti
        <where>
            ti.deleted = 0
            <if test="companyId != null">
                AND ti.company_id = #{companyId}
            </if>
            <if test="companyUserId != null">
                AND ti.company_user_id = #{companyUserId}
            </if>
            <if test="startDate != null and endDate!= null">
                AND ti.create_time BETWEEN #{startDate} AND #{endDate}
            </if>
            <if test="taskType != null">
                and ti.task_type=#{taskType}
            </if>
            <if test="taskStatus != null">
                and ti.create_audit_status=#{taskStatus}
            </if>
        </where>
        group by ti.dept_id,ti.task_type,ti.doctor_id,ti.company_user_id
    </select>
    <select id="getTaskCompleteStatsCount" resultType="java.math.BigDecimal">
        -- 任务完成统计总积分
        SELECT
        COALESCE(SUM(ti.task_integral), 0) AS total_points
        FROM task_info ti
        <where>
            ti.deleted = 0
            AND ti.task_status = 1
            <if test="companyId != null">
                AND ti.company_id = #{companyId}
            </if>
            <if test="companyUserId != null">
                AND ti.company_user_id = #{companyUserId}
            </if>
            <if test="startDate != null and endDate != null">
                AND ti.create_time BETWEEN #{startDate} AND #{endDate}
            </if>
            <if test="taskType != null and taskType != ''">
                AND ti.task_type = #{taskType}
            </if>
            <if test="taskStatus != null and taskStatus != ''">
                AND ti.finish_audit_status = #{taskStatus}
            </if>
        </where>
    </select>
    <select id="getTaskCreateStatsCount" resultType="java.math.BigDecimal">
        -- 任务完成统计
        SELECT
        COALESCE(SUM(ti.task_integral),0) AS total_points
        FROM task_info ti
        <where>
            ti.deleted = 0
            <if test="companyId != null">
                AND ti.company_id = #{companyId}
            </if>
            <if test="companyUserId != null">
                AND ti.company_user_id = #{companyUserId}
            </if>
            <if test="startDate != null and endDate!= null">
                AND ti.create_time BETWEEN #{startDate} AND #{endDate}
            </if>
            <if test="taskType != null">
                and ti.task_type=#{taskType}
            </if>
            <if test="taskStatus != null">
                and ti.create_audit_status=#{taskStatus}
            </if>
        </where>
    </select>

    <select id="selectTaskInfoByIds" resultMap="TaskInfoResult">
        <include refid="selectTaskInfoVo"/>
        <where>
            t.id in
            <foreach collection="taskIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            and t.deleted = 0
        </where>
    </select>

    <select id="getTaskDelivery" resultType="com.fs.task.dto.TaskDeliveryDto">
        SELECT
        ti.id AS taskId,
        ti.task_name AS taskName,
        ti.task_no AS taskNo,
        tdi.delivery_no AS deliveryNo,
        tdi.valid_views AS validViews,
        tdi.view_count AS viewCount,
        ti.task_type AS taskType,

        <!-- 统一映射 title -->
        CASE
        WHEN ti.task_type = 4 THEN ca.title
        WHEN ti.task_type = 5 THEN csv.title
        WHEN ti.task_type IN (3, 6) THEN clv.title
        ELSE NULL
        END AS title,

        <!-- 统一映射 content -->
        CASE
        WHEN ti.task_type = 4 THEN ca.content
        WHEN ti.task_type = 5 THEN csv.summary
        WHEN ti.task_type IN (3, 6) THEN clv.summary
        ELSE NULL
        END AS content,

        <!-- 统一映射 coverImage -->
        CASE
        WHEN ti.task_type = 4 THEN ca.cover_image
        WHEN ti.task_type = 5 THEN csv.cover_url
        WHEN ti.task_type IN (3, 6) THEN clv.cover_url
        ELSE NULL
        END AS coverImage

        FROM task_info ti
        JOIN (
        SELECT
        t1.*
        FROM
        task_delivery_info t1
        INNER JOIN (
        SELECT
        task_id,
        MAX(create_time) AS max_create_time
        FROM
        task_delivery_info
        WHERE
        deleted = 0
        GROUP BY
        task_id
        ) t2 ON t1.task_id = t2.task_id
        AND t1.create_time = t2.max_create_time
        AND t1.deleted = 0
        ) tdi ON ti.id = tdi.task_id

        LEFT JOIN ct_article ca
        ON ti.task_type = 4 AND ca.id = tdi.project_link

        LEFT JOIN ct_short_video csv
        ON ti.task_type = 5 AND csv.id = tdi.project_link

        LEFT JOIN ct_long_video clv
        ON (ti.task_type = 3 OR ti.task_type = 6) AND clv.id = tdi.project_link

        WHERE ti.id = #{taskId}
        AND ti.deleted = 0
        AND tdi.deleted = 0
    </select>
    <select id="getClassroomList" resultType="com.fs.task.dto.PublicIndexDto" parameterType="com.fs.task.dto.PublicIndexDto">
        SELECT id, title, cover_url, video_url, "short" as video_type, summary
        FROM ct_short_video
        WHERE is_original = 1 AND del_flag = 0
        <if test="tags != null">
            AND tags LIKE CONCAT('%', #{tags}, '%')
        </if>
        UNION ALL
        SELECT id, title, cover_url, video_url, "long" as video_type, summary
        FROM ct_long_video
        WHERE is_original = 1 AND del_flag = 0
        <if test="tags != null">
            AND tags LIKE CONCAT('%', #{tags}, '%')
        </if>
    </select>
    <select id="getTaskDoctorDetail" resultType="com.fs.doctor.domain.Doctor">
        SELECT
            d.institution AS hospital,
            d.job_title AS  jobTitle,
            d.department,
            d.doctor_name as doctorName,
            d.institution
        FROM task_delivery_info tdi
                 JOIN task_info ti ON tdi.task_id = ti.id
                 JOIN doctor d ON ti.doctor_id = d.id
        WHERE tdi.project_link = #{id}
          AND tdi.deleted = 0
          AND ti.deleted = 0
          AND d.deleted = 0
            LIMIT 1
    </select>
    <select id="getTaskByTargetId" resultType="com.fs.task.domain.TaskInfo">
        select * from task_info where target_id = #{targetId}
    </select>

</mapper>
