<?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.liquidnet.service.smile.mapper.SmileVolunteersProjectMapper">


    <resultMap id="smileVolunteersProjectListDto" type="com.liquidnet.service.smile.entity.dto.SmileVolunteersProjectListDto">
        <result column="project_id" property="projectId"/>
        <result column="title" property="title"/>
        <result column="status" property="status"/>
        <result column="time_start" property="timeStart"/>
        <result column="time_end" property="timeEnd"/>
        <result column="created_at" property="createdAt"/>
        <result column="time_status" property="timeStatus"/>
        <result column="all" property="all"/>
        <result column="un_audit" property="unAudit"/>
        <result column="audit" property="audit"/>
        <result column="refuse" property="refuse"/>
    </resultMap>

    <select id="selectUserList" resultType="com.liquidnet.service.smile.entity.SmileUser">
        select * from smile_user
        <where>
            del_tag = 0 AND type != 0 AND type != 3 AND type != 4
            <if test="phone!='' and phone !=null">
                AND phone Like concat('%',#{phone},'%')
            </if>
        </where>
        ORDER BY id desc
    </select>


    <select id="selectProjectList" resultType="com.liquidnet.service.smile.entity.dto.SmileVolunteersProjectListDto">
    select a.project_id,
    title,
    a.time_start,
    a.time_end,
    a.created_at,
        a.status,
    IF(now() >time_end,2,1) as 'time_status',
--     count(b0.project_id) + count(b1.project_id) + count(b2.project_id) as 'all',
        (select count(0) as 'count'from smile_volunteers as b0 where a.project_id = b0.project_id and b0.status = 0) as 'un_audit',
        (select count(0) as 'count'from smile_volunteers as b0 where a.project_id = b0.project_id and b0.status = 1) as 'audit',
        (select count(0) as 'count'from smile_volunteers as b0 where a.project_id = b0.project_id and b0.status = 2) as 'refuse'
    from smile_volunteers_project as a
--     LEFT JOIN smile_volunteers as b0 on a.project_id = b0.project_id and b0.status = 0
--     LEFT JOIN smile_volunteers as b1 on a.project_id = b1.project_id and b1.status = 1
--     LEFT JOIN smile_volunteers as b2 on a.project_id = b2.project_id and b2.status = 2
        <where>
            <if test="title!='' and title !=null">
                AND title Like concat('%',#{title},'%')
            </if>
        </where>
    GROUP BY a.project_id
    ORDER BY a.created_at desc
    </select>
</mapper>
