<?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.cftech.activity.market.dao.MarketMapper">

    <resultMap id="resultMap" type="com.cftech.activity.market.model.Market">
        <id column="id" property="id"/>
        <result column="number" property="number"/>
        <result column="name" property="name"/>
        <result column="explains" property="explains"/>
        <result column="belong" property="belong"/>
        <result column="type" property="type"/>
        <result column="content" property="content"/>
        <result column="brandid" property="brandid"/>
        <result column="storeid" property="storeid"/>
        <result column="qrcode" property="qrcode"/>
        <result column="actstarttime" property="actstarttime"/>
        <result column="actendtime" property="actendtime"/>
        <result column="signstarttime" property="signstarttime"/>
        <result column="signendtime" property="signendtime"/>
        <result column="smallimg" property="smallimg"/>
        <result column="img" property="img"/>
        <result column="accounts_id" property="accountsId"/>
        <result column="del_flag" property="delFlag"/>
        <result column="status" property="status"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="description" property="description"/>
        <result column="create_by" property="createBy"/>
        <result column="update_by" property="updateBy"/>
    </resultMap>

    <resultMap id="resultMapDto" type="com.cftech.activity.market.model.MarketDto">
        <id column="id" property="id"/>
        <result column="number" property="number"/>
        <result column="name" property="name"/>
        <result column="explains" property="explains"/>
        <result column="belong" property="belong"/>
        <result column="type" property="type"/>
        <result column="content" property="content"/>
        <result column="brandid" property="brandid"/>
        <result column="storeid" property="storeid"/>
        <result column="qrcode" property="qrcode"/>
        <result column="actstarttime" property="actstarttime"/>
        <result column="actendtime" property="actendtime"/>
        <result column="signstarttime" property="signstarttime"/>
        <result column="signendtime" property="signendtime"/>
        <result column="smallimg" property="smallimg"/>
        <result column="img" property="img"/>
        <result column="accounts_id" property="accountsId"/>
        <result column="del_flag" property="delFlag"/>
        <result column="status" property="status"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="description" property="description"/>
        <result column="create_by" property="createBy"/>
        <result column="update_by" property="updateBy"/>
        <result column="enrollCount" property="enrollCount"/>
        <result column="signCount" property="signCount"/>
    </resultMap>

    <sql id="sqlWhere">
        <if test="conds!=null">
            <trim prefix="WHERE" prefixOverrides="AND|OR">
                <foreach collection="conds.conds" index="index" item="cond">
                    ${cond.linkType}
                    <if test="cond.condType == 'EQUAL'">${cond.param} = #{cond.value}</if>
                    <if test="cond.condType == 'NOTEQUAL'">${cond.param} &lt;&gt; #{cond.value}</if>
                    <if test="cond.condType == 'GREATEQUAL'">${cond.param} &gt;= #{cond.value}</if>
                    <if test="cond.condType == 'GREATTHAN'">${cond.param} &gt; #{cond.value}</if>
                    <if test="cond.condType == 'LESSEQUAL'">${cond.param} &lt;= #{cond.value}</if>
                    <if test="cond.condType == 'LESSTHAN'">${cond.param} &lt; #{cond.value}</if>
                    <if test="cond.condType == 'BETWEEN'">${cond.param} BETWEEN #{cond.startValue} AND
                        #{cond.endValue}
                    </if>
                    <if test="cond.condType == 'ISNULL'">${cond.param} IS NULL</if>
                    <if test="cond.condType == 'NOTNULL'">${cond.param} IS NOT NULL</if>
                    <if test="cond.condType == 'LIKE'">${cond.param} LIKE #{cond.value}</if>
                    <if test="cond.condType == 'IN'">${cond.param} IN
                        <foreach item="item" index="index" collection="cond.value" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </if>
                </foreach>
            </trim>
        </if>
    </sql>

    <sql id="sqlColumns">
                                    id,
                                                number,
                                                name,
                                                explains,
                                                belong,
                                                type,
                                                content,
                                                brandid,
                                                storeid,
                                                qrcode,
                                                actstarttime,
                                                actendtime,
                                                signstarttime,
                                                signendtime,
                                                smallimg,
                                                img,
                                                accounts_id,
                                                del_flag,
                                                status,
                                                create_time,
                                                update_time,
                                                description,
                                                create_by,
                                                update_by,
                                                cardid,
                                                price
                        </sql>


    <insert id="save" parameterType="com.cftech.activity.market.model.Market" useGeneratedKeys="true"
            keyProperty="id">
        insert into wx_activity_market
        (
        <include refid="sqlColumns"/>
        )
        values
        (
        #{id, jdbcType=BIGINT},
        #{number, jdbcType=VARCHAR},
        #{name, jdbcType=VARCHAR},
        #{explains, jdbcType=VARCHAR},
        #{belong, jdbcType=CHAR},
        #{type, jdbcType=CHAR},
        #{content, jdbcType=CLOB},
        #{brandid, jdbcType=VARCHAR},
        #{storeid, jdbcType=BIGINT},
        #{qrcode, jdbcType=VARCHAR},
        now(),
        now(),
        now(),
        now(),
        #{smallimg, jdbcType=VARCHAR},
        #{img, jdbcType=VARCHAR},
        #{accountsId, jdbcType=BIGINT},
        #{delFlag, jdbcType=TINYINT},
        #{status, jdbcType=VARCHAR},
        now(),
        now(),
        #{description, jdbcType=VARCHAR},
        #{createBy, jdbcType=BIGINT},
        #{updateBy, jdbcType=BIGINT},
        #{cardid,jdbcType=BIGINT},
        #{price,jdbcType=VARCHAR}
        )
    </insert>

    <select id="fetchById" parameterType="java.lang.Long" resultMap="resultMap">
        SELECT
        <include refid="sqlColumns"/>
        FROM wx_activity_market t
        WHERE t.id=#{id}
    </select>

    <select id="count" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM wx_activity_market
        <include refid="sqlWhere"/>

    </select>

    <select id="fetchSearchByPage" parameterType="java.util.Map" resultMap="resultMap">
        SELECT
        <include refid="sqlColumns"/>
        FROM wx_activity_market
        <include refid="sqlWhere"/>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>

    <update id="update" parameterType="com.cftech.activity.market.model.Market">
        update wx_activity_market
        <set>
            <if test="id != null">
                id = #{id, jdbcType=BIGINT},
            </if>
            <if test="number != null">
                number = #{number, jdbcType=VARCHAR},
            </if>
            <if test="name != null">
                name = #{name, jdbcType=VARCHAR},
            </if>
            <if test="explains != null">
                explains = #{explains, jdbcType=VARCHAR},
            </if>
            <if test="belong != null">
                belong = #{belong, jdbcType=CHAR},
            </if>
            <if test="type != null">
                type = #{type, jdbcType=CHAR},
            </if>
            <if test="content != null">
                content = #{content, jdbcType=CLOB},
            </if>
            <if test="brandid != null">
                brandid = #{brandid, jdbcType=VARCHAR},
            </if>
            <if test="storeid != null">
                storeid = #{storeid, jdbcType=BIGINT},
            </if>
            <if test="qrcode != null">
                qrcode = #{qrcode, jdbcType=VARCHAR},
            </if>
            <if test="actstarttime != null">
                actstarttime = #{actstarttime, jdbcType=TIMESTAMP},
            </if>
            <if test="actendtime != null">
                actendtime = #{actendtime, jdbcType=TIMESTAMP},
            </if>
            <if test="signstarttime != null">
                signstarttime = #{signstarttime, jdbcType=TIMESTAMP},
            </if>
            <if test="signendtime != null">
                signendtime = #{signendtime, jdbcType=TIMESTAMP},
            </if>
            <if test="smallimg != null">
                smallimg = #{smallimg, jdbcType=VARCHAR},
            </if>
            <if test="img != null">
                img = #{img, jdbcType=VARCHAR},
            </if>
            <if test="accountsId != null">
                accounts_id = #{accountsId, jdbcType=BIGINT},
            </if>
            <if test="delFlag != null">
                del_flag = #{delFlag, jdbcType=TINYINT},
            </if>
            <if test="status != null">
                status = #{status, jdbcType=VARCHAR},
            </if>
            <if test="createTime != null">
                create_time = #{createTime, jdbcType=TIMESTAMP},
            </if>
            <if test="description != null">
                description = #{description, jdbcType=VARCHAR},
            </if>
            <if test="createBy != null">
                create_by = #{createBy, jdbcType=BIGINT},
            </if>
            <if test="updateBy != null">
                update_by = #{updateBy, jdbcType=BIGINT},
            </if>
            <if test="cardid != null">
                cardid = #{cardid, jdbcType=BIGINT},
            </if>
            <if test="price != null">
                price = #{price, jdbcType=VARCHAR},
            </if>
        </set>
        where id=#{id,jdbcType=BIGINT}
    </update>

    <update id="delete" parameterType="java.lang.Long">
        update wx_activity_market set del_flag=1 where id=#{id,jdbcType=BIGINT}
    </update>

    <select id="fetchSearchByPageVo" parameterType="java.util.Map" resultMap="resultMapDto">
        SELECT
        m.*, (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        INNER join t_dc_store signStore on signStore.id=e.storeid
        WHERE
        e.activityid = m.id
        and signStore.delflag=0
        and e.del_flag=0
       /* AND e.storeid = s.id*/
        ) enrollCount,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign sn
        INNER join t_dc_store signStore on signStore.id=sn.storeid
        WHERE
        sn.activityid = m.id
        and signStore.delflag=0
        and sn.del_flag=0
        /*AND sn.storeid = s.id*/
        AND sn.STATUS = '1'
        ) signCount,
        s.name storeName,b.name brandName,
        c.title cardName
        FROM
        wx_activity_market m
        LEFT JOIN t_fours_base_brand b on b.id=m.brandid
        LEFT JOIN t_dc_store s on s.id=m.storeid
        LEFT JOIN wx_mp_card c on c.id=m.cardid
        <if test="openid!=null">
            INNER JOIN wx_activity_market_sign sign ON sign.activityid = m.id and sign.storeid=s.id and sign.openid=#{openid}
        </if>
        <include refid="sqlWhere"/>
        <if test="brandid!=null||storeid!=null">
             and ( belong =0 or brandid=#{brandid} or storeid=#{storeid} )
        </if>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>

    <select id="countVo" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM wx_activity_market m
        LEFT JOIN t_fours_base_brand b on b.id=m.brandid
        LEFT JOIN t_dc_store s on s.id=m.storeid
        LEFT JOIN wx_mp_card c on c.id=m.cardid
        INNER JOIN wx_activity_market_sign sign ON sign.activityid = m.id
        <include refid="sqlWhere"/>

    </select>


    <select id="fetchStoreidsAndBrandids" parameterType="java.util.Map" resultMap="resultMapDto">
        SELECT GROUP_CONCAT(brandid) brandids,GROUP_CONCAT(storeid) storeids from wx_activity_market
        <include refid="sqlWhere"/>
    </select>

    <select id="fetchActByBelong" parameterType="java.util.Map" resultMap="resultMapDto">
        SELECT
	    t.*
        FROM
	    (
		SELECT
			cd.*
		    FROM
			(
				SELECT
				    act.id,
					store. NAME storeName,
					act. NAME `name`,
					store.id storeid,
                    act.brandid,
                    act.type,
					act.actstarttime,
					act.actendtime,
					act.smallimg,
					act.img,
                    act.signstarttime,
                    act.signendtime,
					(
						SELECT
							COUNT(1)
						FROM
							wx_activity_market_sign e
						WHERE
							e.activityid = act.id
						AND e.storeid = store.id
                        and e.del_flag=0
					) enrollCount,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id
        and e.del_flag=0
        and e.status=1
        ) signCount,
					(
						SELECT
							COUNT(1)
						FROM
							wx_activity_market_sign e
						WHERE
							e.activityid = act.id
						AND e.storeid = store.id
                        and e.del_flag=0
                        <if test="openid!=null">
                            AND e.openid=#{openid}
                        </if>

					) isenroll
        ,
        ( SELECT COUNT(1) from wx_collection col where col.entity_id=act.id and col.store_id=store.id and col.del_flag='0' and module_type='3'
        <if test="memberId!=null">
            and col.user_id=#{memberId}
        </if>
        ) collectionNo
				FROM
					t_dc_store store
				LEFT JOIN wx_activity_market act ON 1 = 1
				WHERE
					act.belong = 0
        <if test="brandid!=null">
            AND store.brandid=#{brandid}
        </if>
        <if test="storeid!=null">
            AND store.id=#{storeid}
        </if>
				AND store.delflag = 0
				AND act.del_flag = 0
        <if test="storeStatus!=null">
            AND store.avail_status != 3
            AND store.avail_status &gt; 0
        </if>
			) cd
		UNION
			SELECT
				bd.*
			FROM
				(
					SELECT
                        act.id,
						store. NAME storeName,
						act. NAME `name`,
						store.id storeid,
                        act.brandid,
                        act.type,
						act.actstarttime,
						act.actendtime,
						act.smallimg,
						act.img,
                        act.signstarttime,
                        act.signendtime,
						(
							SELECT
								COUNT(1)
							FROM
								wx_activity_market_sign e
							WHERE
								e.activityid = act.id
							AND e.storeid = store.id
                            and e.del_flag=0
						) enrollCount,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id
        and e.del_flag=0
        and e.status=1
        ) signCount,
						(
							SELECT
								COUNT(1)
							FROM
								wx_activity_market_sign e
							WHERE
								e.activityid = act.id
							AND e.storeid = store.id
                            and e.del_flag=0
                            <if test="openid!=null">
                                  AND e.openid=#{openid}
                            </if>
						) isenroll
        ,
        ( SELECT COUNT(1) from wx_collection col where col.entity_id=act.id and col.store_id=store.id and col.del_flag='0' and module_type='3'
        <if test="memberId!=null">
            and col.user_id=#{memberId}
        </if>
        ) collectionNo
					FROM
						t_dc_store store
					INNER JOIN wx_activity_market act ON instr (act.brandid,store.brandid )>0
					WHERE
						act.belong = 1
        <if test="storeStatus!=null">
            AND store.avail_status != 3
            AND store.avail_status &gt; 0
        </if>
					AND store.delflag = 0
					AND act.del_flag = 0
                    <if test="brandid!=null">
                        AND act.brandid=#{brandid}
                    </if>
                    <if test="storeid!=null">
                      AND act.storeid=#{storeid}
                    </if>
				) bd
			UNION
				SELECT
					ad.*
				FROM
					(
						SELECT
                             act.id,
							store. NAME storeName,
							act. NAME `name`,
							store.id storeid,
                             act.brandid,
                            act.type,
							act.actstarttime,
							act.actendtime,
							act.smallimg,
							act.img,
                            act.signstarttime,
                            act.signendtime,
							(
								SELECT
									COUNT(1)
								FROM
									wx_activity_market_sign e
								WHERE
									e.activityid = act.id
								AND e.storeid = store.id
                                and e.del_flag=0
							) enrollCount,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id
        and e.del_flag=0
        and e.status=1
        ) signCount,
							(
								SELECT
									COUNT(1)
								FROM
									wx_activity_market_sign e
								WHERE
									e.activityid = act.id
								AND e.storeid = store.id
        and e.del_flag=0
                                <if test="openid!=null">
                                     AND e.openid=#{openid}
                                </if>
							) isenroll
        ,
        ( SELECT COUNT(1) from wx_collection col where col.entity_id=act.id and col.store_id=store.id and col.del_flag='0' and module_type='3'
        <if test="memberId!=null">
            and col.user_id=#{memberId}
        </if>
        ) collectionNo
						FROM
							t_dc_store store
						INNER JOIN wx_activity_market act ON act.storeid = store.id
						WHERE
							act.belong = 2
        <if test="storeStatus!=null">
            AND store.avail_status != 3
            AND store.avail_status &gt; 0
        </if>
						AND store.delflag = 0
						AND act.del_flag = 0
                        <if test="brandid!=null">
                            AND store.brandid=#{brandid}
                         </if>
                         <if test="storeid!=null">
                            AND act.storeid=#{storeid}
                          </if>
					) ad
	      ) t
        <include refid="sqlWhere"/>
        ORDER BY t.name ASC,t.actstarttime DESC
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>


    <select id="countActByBelong" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT
        count(1)
        FROM
        (
        SELECT
        cd.*
        FROM
        (
        SELECT
        act.id,
        store. NAME storeName,
        act. NAME `name`,
        store.id storeid,
        act.actstarttime,
        act.actendtime,
        act.smallimg,
        act.img,
        act.signstarttime,
        act.signendtime,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id
        and e.del_flag=0
        ) enrollCount,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id
        and e.del_flag=0
        and e.status=1
        ) signCount,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id
        and e.del_flag=0
        <if test="openid!=null">
            AND e.openid=#{openid}
        </if>

        ) isenroll
        FROM
        t_dc_store store
        LEFT JOIN wx_activity_market act ON 1 = 1
        WHERE
        act.belong = 0
        <if test="brandid!=null">
            AND store.brandid=#{brandid}
        </if>
        <if test="storeid!=null">
            AND store.id=#{storeid}
        </if>
        AND store.delflag = 0
        AND act.del_flag = 0
        <if test="storeStatus!=null">
            AND store.avail_status != 3
            AND store.avail_status &gt; 0
        </if>
        ) cd
        UNION
        SELECT
        bd.*
        FROM
        (
        SELECT
        act.id,
        store. NAME storeName,
        act. NAME `name`,
        store.id storeid,
        act.actstarttime,
        act.actendtime,
        act.smallimg,
        act.img,
        act.signstarttime,
        act.signendtime,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id
        and e.del_flag=0
        ) enrollCount,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id
        and e.del_flag=0
        and e.status=1
        ) signCount,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id
        <if test="openid!=null">
            AND e.openid=#{openid}
        </if>
        ) isenroll
        FROM
        t_dc_store store
        INNER JOIN wx_activity_market act ON act.brandid = store.brandid
        WHERE
        act.belong = 1
        <if test="storeStatus!=null">
            AND store.avail_status != 3
            AND store.avail_status &gt; 0
        </if>
        AND store.delflag = 0
        AND act.del_flag = 0
        <if test="brandid!=null">
            AND act.brandid=#{brandid}
        </if>
        <if test="storeid!=null">
            AND act.storeid=#{storeid}
        </if>
        ) bd
        UNION
        SELECT
        ad.*
        FROM
        (
        SELECT
        act.id,
        store. NAME storeName,
        act. NAME `name`,
        store.id storeid,
        act.actstarttime,
        act.actendtime,
        act.smallimg,
        act.img,
        act.signstarttime,
        act.signendtime,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id
        and e.del_flag=0
        ) enrollCount,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id
        and e.del_flag=0
        and e.status=1
        ) signCount,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = act.id
        AND e.storeid = store.id

        <if test="openid!=null">
            AND e.openid=#{openid}
        </if>
        ) isenroll
        FROM
        t_dc_store store
        INNER JOIN wx_activity_market act ON act.storeid = store.id
        WHERE
        act.belong = 2
        <if test="storeStatus!=null">
            AND store.avail_status != 3
            AND store.avail_status &gt; 0
        </if>
        AND store.delflag = 0
        AND act.del_flag = 0
        <if test="brandid!=null">
            AND store.brandid=#{brandid}
        </if>
        <if test="storeid!=null">
            AND act.storeid=#{storeid}
        </if>
        ) ad
        ) t

    </select>

    <select id="getActListByStore" parameterType="java.util.Map" resultMap="resultMapDto">
        SELECT
        m.*, (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign e
        WHERE
        e.activityid = m.id
        AND e.storeid = s.id
        and e.del_flag=0
        ) enrollCount,
        (
        SELECT
        COUNT(1)
        FROM
        wx_activity_market_sign sn
        WHERE
        sn.activityid = m.id
        and sn.del_flag=0
        AND sn.storeid = s.id
        AND sn. STATUS = '1'
        ) signCount,
        s. NAME storeName,
        b. NAME brandName,
        c.title cardName
        FROM
        t_dc_store s
        LEFT JOIN  wx_activity_market m ON 1=1
        LEFT JOIN t_fours_base_brand b ON b.id = s.brandid
        LEFT JOIN wx_mp_card c ON c.id = m.cardid
        <if test="openid!=null">
            INNER JOIN wx_activity_market_sign sign ON sign.activityid = m.id and sign.storeid=s.id
        </if>
        <include refid="sqlWhere"/>
        <!--<if test="brandid!=null||storeid!=null">
            and ( belong =0 or brandid=#{brandid} or storeid=#{storeid} )
        </if>-->
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>
</mapper>