<?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="cn.com.countrygarden.bi.main.dao.wcx.WcxPrizeMapper">
    <resultMap id="BasePrizeDtoMap" type="cn.com.countrygarden.bi.main.dto.wcx.WcxPrizeDto">
        <id     column="prize_id"           property="prizeId"/>
        <result column="prize_title"        property="prizeTitle"/>
        <result column="prize_type"         property="prizeType"/>
        <result column="all_award_number"   property="allAwardNumber"/>
        <result column="all_join_number"    property="allJoinNumber"/>
        <result column="all_thumb_number"   property="allThumbNumber"/>
        <result column="prize_project_name" property="prizeProjectName"/>
        <result column="prize_region_name"  property="prizeRegionName"/>
        <result column="prize_month_date"   property="prizeMonthDate"/>
        <result column="batch_code"         property="batchCode"/>
        <result column="prize_open_date"    property="prizeOpenDate"/>
        <result column="status"             property="status"/>

        <result column="start_date"         property="startDate"/>
        <result column="end_date"           property="endDate"/>

        <result column="prize_item_id"        property="prizeItemId"/>
        <result column="inn_id"        property="innId"/>
        <result column="inn_title"        property="innTitle"/>
        <result column="inn_content"        property="innContent"/>
        <result column="inn_type"        property="innType"/>
        <result column="inn_baseline"        property="innBaseline"/>
        <result column="inn_classification"        property="innClassification"/>
        <result column="inn_team_content"        property="innTeamContent"/>
        <result column="inn_project_name"        property="innProjectName"/>
        <result column="inn_region_name"        property="innRegionName"/>
        <result column="inn_user_id"        property="innUserId"/>
        <result column="inn_employee_head_pic_url" property="innEmployeeHeadPicUrl"/>
        <result column="inn_thumb_number" property="innThumbNumber"/>
        <result column="inn_employee_name" property="innEmployeeName"/>

        <result column="prize_user_id"        property="prizeUserId"/>
        <result column="prize_employee_name"        property="prizeEmployeeName"/>
        <result column="prize_user_project"        property="prizeUserProject"/>
        <result column="prize_user_region"        property="prizeUserRegion"/>

        <result column="prize_employee_head_pic_url"        property="prizeEmployeeHeadPicUrl"/>
        <result column="prize_user_thumb_num"        property="prizeUserThumbNum"/>
        <result column="prize_user_inn_num"        property="prizeUserInnNum"/>

        <result column="project_user_id"        property="projectUserId"/>
        <result column="project_name"        property="projectName"/>
        <result column="project_employee_name"        property="projectEmployeeName"/>
        <result column="project_prize_inn_number"        property="projectPrizeInnNumber"/>
        <result column="region_user_id"        property="regionUserId"/>
        <result column="region_employee_name"        property="regionEmployeeName"/>
        <result column="region_prize_inn_number"        property="regionPrizeInnNumber"/>
        <result column="region_name"        property="regionName"/>
        <result column="project_head_pic_url"        property="projectHeadPicUrl"/>
        <result column="order_no" property="orderNo"/>
    </resultMap>

    <select id="selectPageData" resultMap="BasePrizeDtoMap">
        SELECT
        prize_id,
        prize_title,
        prize_type,
        all_award_number,
        all_join_number,
        all_thumb_number,
        prize_project_name,
        prize_region_name,
        prize_month_date,
        batch_code,
        prize_open_date,
        status,
        create_date
        FROM wcx_prize
        <include refid="BaseSelectPageData"/>
        limit #{pageOffset}, #{pageSize}
    </select>

    <select id="selectPageCount" resultType="long">
        SELECT count(prize_id) FROM wcx_prize
        <include refid="BaseSelectPageData"/>
    </select>

    <sql id="BaseSelectPageData">
        <where>
            status != '3'
            <if test="prizeTitle != null">
                AND prize_title like concat('%', #{prizeTitle}, '%')
            </if>
            <if test="prizeType != null and prizeType != ''">
                AND prize_type = #{prizeType}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND batch_code = #{batchCode}
            </if>
        </where>
    </sql>

    <select id="selectPageDataItem" resultMap="BasePrizeDtoMap">
        SELECT
        p.prize_id,
        p.prize_title,
        p.prize_type,
        p.all_award_number,
        p.all_join_number,
        p.all_thumb_number,
        p.prize_project_name,
        p.prize_region_name,
        p.prize_month_date,
        p.batch_code,
        p.prize_open_date,
        p.status,
        p.create_date,
        item.prize_item_id,
        item.inn_id,
        item.inn_title,
        item.inn_content,
        item.inn_type,
        item.inn_baseline,
        item.inn_classification,
        item.inn_team_content,
        item.inn_project_name,
        item.inn_region_name,
        item.inn_user_id,
        item.inn_employee_head_pic_url,
        item.inn_thumb_number,
        item.inn_employee_name,
        item.prize_user_id,
        item.prize_employee_name,
        item.prize_user_project,
        item.prize_user_region,

        item.prize_employee_head_pic_url,
        item.prize_user_thumb_num,
        item.prize_user_inn_num,

        item.project_user_id,
        item.project_name,
        item.project_employee_name,
        item.project_prize_inn_number,
        item.region_user_id,
        item.region_name,
        item.region_employee_name,
        item.region_prize_inn_number,
        item.project_head_pic_url,
        item.status itemStatus,
        item.order_no
        FROM wcx_prize p
        JOIN wcx_prize_item item on item.prize_id = p.prize_id
        <include refid="BaseSelectPageDataItem"/>
        ORDER BY item.status DESC, item.order_no ASC, inn_thumb_number DESC
        <if test="pageSize != -1">
        limit #{pageOffset}, #{pageSize}
        </if>
    </select>

    <select id="selectPageCountItem" resultType="int">
        SELECT count(item.prize_id) FROM wcx_prize p
        JOIN wcx_prize_item item on item.prize_id = p.prize_id
        <include refid="BaseSelectPageDataItem"/>
    </select>

    <sql id="BaseSelectPageDataItem">
        <where>
            <!-- 个人奖单 -->

            <if test="innUserId != null and prizeUserId != null">
                and (item.inn_user_id = #{innUserId}
                OR item.prize_user_id = #{prizeUserId})
                AND item.status = '1'
            </if>
            <if test="itemStatus != null and itemStatus !=''">
                AND item.status = #{itemStatus}
            </if>
            <if test="prizeType != null and prizeType != ''">
                AND p.prize_type = #{prizeType}
            </if>
            <if test="startDate != null">
                AND item.create_date <![CDATA[>=]]> #{startDate}
            </if>
            <if test="endDate != null">
                AND item.create_date <![CDATA[<=]]> #{endDate}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND p.batch_code = #{batchCode}
            </if>
            <if test="prizeId != null and prizeId != ''">
                AND p.prize_id = #{prizeId}
            </if>
        </where>
    </sql>

    <!-- 查询前20点数量 -->
    <select id="selectBestGetPoint20Count" resultType="int">
        SELECT count(innovation_id) FROM wcx_innovation
        WHERE create_date <![CDATA[>=]]> #{startDate} and create_date <![CDATA[<=]]> #{endDate}
    </select>

    <insert id="insertPrize">
        INSERT INTO wcx_prize (
        prize_id,
        prize_title,
        prize_type,
        all_award_number,
        all_join_number,
        all_thumb_number,
        prize_project_name,
        prize_region_name,
        prize_month_date,
        batch_code,
        prize_open_date,
        start_date,
        end_date,
        status,
        create_date
        ) VALUES (
        #{prizeId},
        #{prizeTitle},
        #{prizeType},
        #{allAwardNumber},
        #{allJoinNumber},
        #{allThumbNumber},
        #{prizeProjectName},
        #{prizeRegionName},
        #{prizeMonthDate},
        #{batchCode},
        #{prizeOpenDate},
        #{startDate},
        #{endDate},
        #{status},
        #{createDate}
        )
    </insert>

    <insert id="batchInsertPrizeItems">
        INSERT INTO wcx_prize_item (
        prize_item_id,
        inn_id,
        inn_title,
        inn_content,
        inn_type,
        inn_baseline,
        inn_classification,
        inn_team_content,
        inn_project_name,
        inn_region_name,
        inn_user_id,
        inn_employee_head_pic_url,
        inn_employee_name,
        inn_thumb_number,

        prize_user_id,
        prize_employee_name,
        prize_user_project,
        prize_user_region,

        prize_employee_head_pic_url,
        prize_user_inn_num,
        prize_user_thumb_num,

        project_user_id,
        project_name,
        project_head_pic_url,
        project_employee_name,
        project_prize_inn_number,
        region_user_id,
        region_name,
        region_employee_name,
        region_head_pic_url,
        region_prize_inn_number,
        prize_id,
        status,
        create_date)
        VALUES
        <foreach collection="list" index="index" item="record" separator="," >
            (
            #{record.prizeItemId},
            #{record.innId},
            #{record.innTitle},
            #{record.innContent},
            #{record.innType},
            #{record.innBaseline},
            #{record.innClassification},
            #{record.innTeamContent},
            #{record.innProjectName},
            #{record.innRegionName},
            #{record.innUserId},
            #{record.innEmployeeHeadPicUrl},
            #{record.innEmployeeName},
            #{record.innThumbNumber},

            #{record.prizeUserId},
            #{record.prizeEmployeeName},
            #{record.prizeUserProject},
            #{record.prizeUserRegion},

            #{record.prizeEmployeeHeadPicUrl},
            #{record.prizeUserInnNum},
            #{record.prizeUserThumbNum},

            #{record.projectUserId},
            #{record.projectName},
            #{record.projectHeadPicUrl},
            #{record.projectEmployeeName},
            #{record.projectPrizeInnNumber},
            #{record.regionUserId},
            #{record.regionName},
            #{record.regionEmployeeName},
            #{record.regionHeadPicUrl},
            #{record.regionPrizeInnNumber},
            #{record.prizeId},
            #{record.status},
            #{record.createDate}
            )
        </foreach>
    </insert>

    <select id="findBatchCodeMax" resultType="int">
        SELECT MAX(batch_code) FROM wcx_prize
    </select>

    <update id="updateItemStatus">
        update wcx_prize_item set status = #{itemStatus} where prize_item_id = #{prizeItemId};
    </update>

    <update id="updatePrizeItemStatusOfSpecifyStatus">
        update wcx_prize_item set status = #{newStatus} where prize_id = #{prizeId} AND status = #{specifyStatus};
    </update>

    <update id="updatePrize">
        UPDATE wcx_prize
        <set>
            <if test="prizeTitle != null and prizeTitle != ''">
                prize_title = #{prizeTitle},
            </if>
            <if test="prizeType != null and prizeType != ''">
                prize_type = #{prizeType},
            </if>
            <if test="allAwardNumber != null and allAwardNumber != ''">
                all_award_number = #{allAwardNumber},
            </if>
            <if test="allJoinNumber != null and allJoinNumber != ''">
                all_join_number = #{allJoinNumber},
            </if>
            <if test="allThumbNumber != null and allThumbNumber != ''">
                all_thumb_number = #{allThumbNumber},
            </if>
            <if test="prizeProjectName != null and prizeProjectName != ''">
                prize_project_name = #{prizeProjectName},
            </if>
            <if test="prizeMonthDate != null">
                prize_month_date = #{prizeMonthDate},
            </if>
            <if test="prizeProjectName != null and prizeProjectName != ''">
                prize_project_name = #{prizeProjectName},
            </if>
            <if test="prizeTitle != null and prizeTitle != ''">
                batch_code = #{batchCode},
            </if>
            <if test="prizeOpenDate != null">
                prize_open_date = #{prizeOpenDate},
            </if>
            <if test="status != null and status != ''">
                status = #{status},
            </if>
            <if test="createDate != null">
                create_date = #{createDate}
            </if>
        </set>
        WHERE prize_id = #{prizeId}
    </update>

    <select id="findItemNumberOfStatus" resultType="long">
        SELECT count(prize_item_id) FROM wcx_prize_item WHERE prize_id = #{prizeId}
        <if test="status != null and status != ''">
            and status = #{status}
        </if>
    </select>

    <update id="updatePrizeBatchCodeAndTitle">
        UPDATE wcx_prize SET batch_code = #{batchCode}, prize_title = #{prizeTitle} WHERE prize_id = #{prizeId}
    </update>

    <delete id="deleteGetStatusPrize">
        DELETE FROM wcx_prize WHERE status = '3'
    </delete>

    <delete id="deleteGetStatusPrizeItem">
        DELETE FROM wcx_prize_item WHERE status = '3'
    </delete>

    <select id="haveGet" resultType="string">
        SELECT prize_id FROM wcx_prize WHERE status = '3' and prize_type = #{prizeType} and start_date = #{startDate} and end_date = #{endDate}
    </select>
    <!--status = '3' todo  and-->
    <delete id="deleteGetItem">
        DELETE FROM wcx_prize_item WHERE prize_item_id = #{prizeItemId}
    </delete>

    <update id="updatePrizeStatusOfSpecifyStatus">
        update wcx_prize set status = #{newStatus} where prize_id = #{prizeId} AND status = #{specifyStatus};
    </update>

    <update id="updateItemStatusBatch">
        update wcx_prize_item set status = #{status} where prize_item_id in
        <foreach collection="prizeItemIds" index="index" item="record" separator="," open="(" close=")" >
          #{record}
        </foreach>
    </update>

    <update id="updateOrderNo">
        update wcx_prize_item set order_no = #{orderNo} WHERE prize_item_id = #{prizeItemId}
    </update>

    <select id="findFirstByPrimaryKey" resultMap="BasePrizeDtoMap">
        SELECT
            prize_id,
            prize_title,
            prize_type,
            all_award_number,
            all_join_number,
            all_thumb_number,
            prize_project_name,
            prize_region_name,
            prize_month_date,
            batch_code,
            prize_open_date,
            status,
            create_date,
            start_date,
            end_date
        FROM wcx_prize
        WHERE prize_id = #{prizeId}
    </select>
</mapper>