<?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.status) as 'all',
        sum(IF(b0.status = 0, 1, 0)) as 'un_audit',
        sum(IF(b0.status = 1, 1, 0)) as 'audit',
        sum(IF(b0.status = 2, 1, 0)) as 'refuse'
        from smile_volunteers_project as a
        LEFT JOIN smile_volunteers as b0 on a.project_id = b0.project_id
        <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>
