<?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.huiju.estateagents.mapper.HelpActivityMapper">
    <select id="helpActivityListPage" resultType="com.huiju.estateagents.entity.HelpActivity">
        SELECT
        COUNT(hir.verification) as succeedCount,
        ha.*
        FROM
        ta_help_activity ha
        LEFT JOIN ta_help_initiate_record hir ON ha.help_activity_id = hir.help_activity_id
        AND (hir.verification is NULL or hir.verification != '')
        <where>
        <if test="cityId != null and cityId != ''">
            and ha.city_id = #{cityId}
        </if>
        <if test="buildingId != null and buildingId != ''">
            and ha.building_id = #{buildingId}
        </if>
        <if test="title != null and title != ''">
            and ha.title like CONCAT('%',#{title}, '%')
        </if>
        <if test="startDate != null and startDate != ''and startDate!='Invalid date'">
         and date_format(ha.start_date,'%Y-%m-%d') = date_format( #{startDate}, '%Y-%m-%d' )
        </if>
        <if test="endDate != null and endDate != ''and endDate!='Invalid date'">
            and date_format(ha.end_date,'%Y-%m-%d') = date_format( #{endDate}, '%Y-%m-%d' )
        </if>
        <if test="activityStatus != null and activityStatus != null">
            and ha.activity_status = #{activityStatus}
        </if>
            and ha.org_id=#{orgId}
        <if test="personBuildingList != null and personBuildingList.size > 0">
            AND ha.building_id in
            <foreach collection="personBuildingList" item="personBuilding" open="(" close=")" separator=",">
                #{personBuilding.buildingId}
            </foreach>
        </if>
        </where>
        GROUP BY ha.help_activity_id
        order by ha.heavy desc
    </select>

    <update id="updateActivityToUnStart">
        update ta_help_activity
        set activity_status = 1
        where
        NOW() &lt;= start_date  and activity_status != 2
    </update>

    <update id="updateUnStartHelpActivity">
        update ta_help_activity t
        set activity_status = 0
        where
        t.activity_status != '2' and NOW() BETWEEN t.start_date  and t.end_date
    </update>

    <update id="updateProcessingHelpActivity">
        update ta_help_activity t
        set activity_status = 2
        where
        t.activity_status != '2' and NOW() > t.end_date
    </update>

    <select id="selectWxhelpActivityAndGroup" resultType="com.huiju.estateagents.entity.HelpActivity">
		(
			SELECT
                a.city_id as cityId,
				a.help_activity_id as activityId,
				a.list_img as img,
                a.banner_list_img as bannerListImg,
                a.title as title,
                a.end_date as endDate,
                a.`status` as status,
                a.activity_status as activityStatus,
				"help" AS type,
                a.heavy
			FROM
				ta_help_activity a
				WHERE
                    a.org_id =#{orgId} and a.`status` = 1
                <if test="buildingId  == null or  buildingId ==''">
                  and  a.is_main = 1
                </if>
                <if test="cityID != null and cityID != ''">
                      and  a.city_id = #{cityID}
                </if>
                <if test="buildingId != null and buildingId != ''">
                  and a.building_id = #{buildingId}
                </if>
		)
		UNION ALL
			(
				SELECT
                    s.city_id as cityId,
					s.group_activity_id as activityId,
					s.list_img as img,
                    s.banner_list_img as bannerListImg,
                    s.activity_name as title,
                    s.end_time as endDate,
                    s.status as status,
                    s.activity_status as activityStatus,
					"group" AS type,
                    s.heavy
				FROM
					ta_share_activity s
					WHERE
                    s.org_id =#{orgId} and s.`status` = 1
                <if test="buildingId  == null or  buildingId =='' ">
                    and  s.sort = 1
                </if>
                <if test="cityID != null and cityID != ''">
                    and  s.city_id = #{cityID}
                </if>
                <if test="buildingId != null and buildingId != ''">
                    and s.building_id = #{buildingId}
                </if>
			)
		UNION ALL
		    (
                SELECT
                t.city_id as cityId,
                t.dynamic_id as activityId,
                t.list_img_url as img,
                t.banner_list_img as bannerListImg,
                t.title as title,
                t.end_date as endDate,
                t.status as status,
                t.activity_status as activityStatus,
                "activity" AS type,
                t.heavy
                FROM
                ta_building_dynamic t
                WHERE
                t.org_id =#{orgId} and t.`status` = 1
                <if test="buildingId  == null or  buildingId =='' ">
                    and  t.home = 1
                </if>
                <if test="cityID != null and cityID != ''">
                    and  t.city_id = #{cityID}
                </if>
                <if test="buildingId != null and buildingId != ''">
                    and t.building_id = #{buildingId}
                </if>
            )
            order by heavy desc
    </select>

    <select id="helpActivityListEffectivePage" resultType="com.huiju.estateagents.entity.HelpActivity">
        SELECT
        ha.*
        FROM
        ta_help_activity ha
        <where>
            <if test="cityId != null and cityId != ''">
                and ha.city_id = #{cityId}
            </if>
            <if test="name != null and name != ''">
                and ha.title like CONCAT('%', #{name}, '%')
            </if>
            <if test="buildingId != null">
                and building_id = #{buildingId}
            </if>
            <if test="status != null">
                and status = #{status}
            </if>
            <if test="personBuildingList != null and personBuildingList.size > 0">
                AND ha.building_id in
                <foreach collection="personBuildingList" item="personBuilding" open="(" close=")" separator=",">
                    #{personBuilding.buildingId}
                </foreach>
            </if>
            and ha.org_id=#{orgId}
            and ha.activity_status in (0, 1)
        </where>
        GROUP BY ha.help_activity_id
        order by ha.activity_status = 0 desc,
        ha.activity_status = 1 desc,
        ha.activity_status = 2 desc,
        ha.start_date asc
    </select>
</mapper>
