<?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.ruoyi.ibs.task.mapper.MarketTaskMapper">

    <select id="selectCreateStatus" resultType="java.lang.String">
        select create_status from market_task where id = #{id}
    </select>
    <select id="selectMarketTasks" resultType="MarketTaskVO">
        select mt.id, mt.market_name, mt.dept_id, mt.create_by, mt.create_time, mt.update_by, mt.update_time, mt.end_time, mt.is_feedback, mt.create_status, d.dept_name as deptName,
        case when count(mtl.market_task_id) > 0 then
        round(sum(case when mtl.status = '2' then 1 else 0 end) * 100.0 / count(mtl.market_task_id), 2)
        else 0 end as completeRate,
        case when mt.end_time is not null and NOW() > mt.end_time and count(mtl.market_task_id) > 0 then
        round(sum(case when mtl.status != '0' then 1 else 0 end) * 100.0 / count(mtl.market_task_id), 2)
        else 0 end as isOverdueRate
        from market_task mt
        left join sys_dept d on d.dept_id = mt.dept_id
        left join market_task_list mtl on mt.id = mtl.market_task_id
        <where>
            create_status = '1'
            <if test="role != null and role == 'outlet'"> and (d.dept_id = #{deptId}) </if>
            <if test="role != null and role == 'branch'"> and (d.dept_id = #{deptId} or find_in_set(#{deptId},d.ancestors)) </if>
            <if test="role != null and role in {'head', 'ops', 'public', 'private'}">  and (left( d.dept_id,3) = left(#{deptId},3)) </if>
            <if test="marketTaskName != null and marketTaskName != ''">
                and market_name like concat('%', #{marketTaskName}, '%')
            </if>
        </where>
        GROUP BY mt.id,mt.market_name,mt.dept_id,mt.create_by,mt.create_time,mt.update_by,mt.update_time,mt.end_time,mt.is_feedback,mt.create_status,d.dept_name
    </select>

</mapper>