<?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.performance.PerformanceTargetMapper">
    <resultMap id="BaseColumn" type="cn.com.countrygarden.bi.main.model.performance.PerformanceTarget">
        <id column="target_id" property="targetId" jdbcType="BIGINT"/>
        <result column="record_id" property="recordId" jdbcType="BIGINT"/>
        <result column="elementname" property="elementname" jdbcType="BIGINT"/>
        <result column="hierarchyname" property="hierarchyname" jdbcType="BIGINT"/>
        <result column="element_id" property="elementId" jdbcType="BIGINT"/>
        <result column="totalPage" property="totalPage" jdbcType="BIGINT"/>
        <result column="threshold_target" property="thresholdTarget" jdbcType="VARCHAR"/>
        <result column="at_least_target" property="atLeastTarget" jdbcType="VARCHAR"/>
        <result column="target_average" property="targetAverage" jdbcType="VARCHAR"/>
        <result column="scream_target" property="screamTarget" jdbcType="VARCHAR"/>
        <result column="howl_target" property="howlTarget" jdbcType="VARCHAR"/>
        <result column="hierarchy" property="hierarchy" jdbcType="VARCHAR"/>
        <result column="hierarchy_id" property="hierarchyId" jdbcType="VARCHAR"/>
        <result column="frequency" property="frequency" jdbcType="VARCHAR"/>
        <result column="year" property="year" jdbcType="INTEGER"/>
        <result column="month" property="month" jdbcType="INTEGER"/>
        <result column="quarterly" property="quarterly" jdbcType="VARCHAR"/>
        <result column="weight" property="weight" jdbcType="DOUBLE"/>
        <result column="is_check" property="isCheck" jdbcType="VARCHAR"/>
        <result column="struct_param" property="structParam" jdbcType="VARCHAR"/>
        <result column="created_date" property="createdDate" jdbcType="TIMESTAMP"/>
        <result column="created_by" property="createdBy" jdbcType="VARCHAR"/>
        <result column="update_date" property="updateDate" jdbcType="TIMESTAMP"/>
        <result column="update_by" property="updateBy" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="SqlColumn">
        target_id,record_id,element_id,threshold_target,at_least_target,scream_target,howl_target,hierarchy,hierarchy_id,frequency,
        year,month,quarterly,weight,struct_param,is_check,created_date,created_by,update_date,update_by
    </sql>


    <insert id="insertTargetList" parameterType="java.util.List">
        INSERT INTO performance_target VALUES
        <foreach collection="targetList" item="target" separator=",">
            (#{target.targetId},#{target.elementId},#{target.thresholdTarget},#{target.atLeastTarget},#{target.targetAverage},
            #{target.screamTarget},#{target.howlTarget},#{target.hierarchy},#{target.hierarchyId},#{target.frequency},
            #{target.year},#{target.month},#{target.quarterly},#{target.weight},#{target.isCheck},#{target.structParam},
            #{target.createdDate},#{target.createdBy},#{target.updateDate},#{target.updateBy})
        </foreach>
    </insert>

    <insert id="insertTargetSelective" parameterType="PerformanceTarget">
        insert into performance_target
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="recordId != null">
                record_id,
            </if>
            <if test="elementId != null">
                element_id,
            </if>
            <if test="thresholdTarget != null">
                threshold_target,
            </if>
            <if test="atLeastTarget != null">
                at_Least_target,
            </if>
            <if test="targetAverage != null">
                target_average,
            </if>
            <if test="screamTarget != null">
                scream_target,
            </if>
            <if test="howlTarget != null">
                howl_target,
            </if>
            <if test="hierarchy != null">
                hierarchy,
            </if>
            <if test="hierarchyId != null">
                hierarchy_id,
            </if>
            <if test="frequency != null">
                frequency,
            </if>
            <if test="year != null">
                `year`,
            </if>
            <if test="month != null">
                `month`,
            </if>
            <if test="quarterly != null">
                quarterly,
            </if>
            <if test="weight != null">
                weight,
            </if>
            <if test="isCheck != null">
                is_check,
            </if>
            <if test="structParam != null">
                struct_param,
            </if>
            <if test="createdDate != null">
                created_date,
            </if>
            <if test="createdBy != null">
                created_by,
            </if>
            <if test="updateDate != null">
                update_date,
            </if>
            <if test="updateBy != null">
                update_by,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="recordId != null">
                #{recordId},
            </if>
            <if test="elementId != null">
                #{elementId},
            </if>
            <if test="thresholdTarget != null">
                #{thresholdTarget},
            </if>
            <if test="atLeastTarget != null">
                #{atLeastTarget},
            </if>
            <if test="targetAverage != null">
                #{targetAverage},
            </if>
            <if test="screamTarget != null">
                #{screamTarget},
            </if>
            <if test="howlTarget != null">
                #{howlTarget},
            </if>
            <if test="hierarchy != null">
                #{hierarchy},
            </if>
            <if test="hierarchyId != null">
                #{hierarchyId},
            </if>
            <if test="frequency != null">
                #{frequency},
            </if>
            <if test="year != null">
                #{year},
            </if>
            <if test="month != null">
                #{month},
            </if>
            <if test="quarterly != null">
                #{quarterly},
            </if>
            <if test="weight != null">
                #{weight},
            </if>
            <if test="isCheck != null">
                #{isCheck},
            </if>
            <if test="structParam != null">
                #{structParam},
            </if>
            <if test="createdDate != null">
                #{createdDate},
            </if>
            <if test="createdBy != null">
                #{createdBy},
            </if>
            <if test="updateDate != null">
                #{updateDate},
            </if>
            <if test="updateBy != null">
                #{updateBy},
            </if>
        </trim>
    </insert>
    <update id="updateTargetSelective" parameterType="cn.com.countrygarden.bi.main.model.performance.PerformanceTarget">
        update performance_target
        <set>
            <if test="elementId != null">
                element_id = #{elementId},
            </if>
            <if test="thresholdTarget != null">
                threshold_target = #{thresholdTarget},
            </if>
            <if test="atLeastTarget != null">
                at_Least_target = #{atLeastTarget},
            </if>
            <if test="targetAverage != null">
                target_average = #{targetAverage},
            </if>
            <if test="screamTarget != null">
                scream_target = #{screamTarget},
            </if>
            <if test="howlTarget != null">
                howl_target = #{howlTarget},
            </if>
            <if test="hierarchy != null">
                hierarchy = #{hierarchy},
            </if>
            <if test="hierarchyId != null">
                hierarchy_id = #{hierarchyId},
            </if>
            <if test="frequency != null">
                frequency = #{frequency},
            </if>
            <if test="year != null">
                `year` = #{year},
            </if>
            <if test="month != null">
                `month` = #{month},
            </if>
            <if test="quarterly != null">
                quarterly = #{quarterly},
            </if>
            <if test="weight != null">
                weight = #{weight},
            </if>
            <if test="isCheck != null">
                is_check = #{isCheck},
            </if>
            <if test="structParam != null">
                struct_param = #{structParam},
            </if>
            <if test="createdDate != null">
                created_date = #{createdDate},
            </if>
            <if test="createdBy != null">
                created_by = #{createdBy},
            </if>
            <if test="updateDate != null">
                update_date = #{updateDate},
            </if>
            <if test="updateBy != null">
                update_by = #{updateBy},
            </if>
        </set>
        where target_id = #{targetId}
    </update>


    <select id="selectTargetList" resultMap="BaseColumn">
        SELECT
        <include refid="SqlColumn"/>
        FROM performance_target
        WHERE 1 = 1
        <if test="target.year != null">
            AND year = #{target.year}
        </if>
        <if test="target.quarterly != null">
            AND quarterly = #{target.quarterly}
        </if>
    </select>

    <select id="getTargetList" resultMap="BaseColumn">
        SELECT
        t.*,
        (SELECT name from performance_element where element_id=#{target.elementId}),
        (SELECT hierarchy_name from performance_env where hierarchy_id=t.hierarchy_id)
        FROM
        performance_target t
        where
        t.element_id = #{target.elementId}
        <if test="target.year != null">
            and t.year = #{target.year}
        </if>

        <if test="target.quarterly != null">
            and t.quarterly = #{target.quarterly}
        </if>
        limit #{target.pageCur},#{target.pageSize}

    </select>
    <select id="getTotalPage" resultMap="BaseColumn">
        SELECT COUNT(*) as totalPage from (SELECT
        t.*,
        (SELECT name from performance_element where element_id=#{target.elementId}),
        (SELECT hierarchy_name from performance_env where hierarchy_id=t.hierarchy_id)
        FROM
        performance_target t
        where
        t.element_id = #{target.elementId}
        <if test="target.quarterly != null">
            and t.quarterly = #{target.quarterly}
        </if>
        ) as table2
    </select>
    <select id="getByHierarchyId" resultMap="BaseColumn">
        SELECT t.*
        FROM
        performance_target t
        where
        t.element_id = #{target.elementId} and t.hierarchy_id = #{target.hierarchyId} and t.year=#{target.year}
        <if test="target.quarterly != null">
            AND t.quarterly = #{target.quarterly}
        </if>
    </select>
    <select id="getNewTargetList" resultMap="BaseColumn">
        SELECT
        t.*,
        (SELECT name from performance_element where element_id=#{target.elementId}) as elementname,
        (SELECT hierarchy_name from performance_env where hierarchy_id=t.hierarchy_id) as hierarchyname
        FROM
        performance_target t
        where
        t.element_id = #{target.elementId}
        <if test="target.quarterly != null">
            AND t.quarterly = #{target.quarterly}
        </if>

        <if test="target.year != null">
            AND t.year = #{target.year}
        </if>
        limit #{target.pageCur},#{target.pageSize}
    </select>
    <select id="getNewByHierarchyId" resultMap="BaseColumn">
        SELECT t.*
        FROM
        performance_target t
        where
        t.element_id = #{target.elementId}
        and t.hierarchy_id = #{target.hierarchyId}
        and t.year=#{target.year}
        and t.quarterly = #{target.quarterly}
    </select>

    <update id="UpdateNewtarget">
        UPDATE
         performance_target
        SET
        frequency=#{scores.frequency}
        where element_id=#{scores.elementId}
        and hierarchy_id=#{scores.hierarchyId}
        and year=#{scores.year}
        and quarterly = #{scores.quarterly}
 </update>

    <update id="updateTargetByTargetId">
        UPDATE performance_target
        SET
        <if test="target.thresholdTarget != null">
            threshold_target=#{target.thresholdTarget},
        </if>
        <if test="target.atLeastTarget != null">
            at_Least_target=#{target.atLeastTarget},
        </if>
        <if test="target.targetAverage != null">
            target_average=#{target.targetAverage},
        </if>
        <if test="target.screamTarget != null">
            scream_target=#{target.screamTarget},
        </if>
        <if test="target.howlTarget != null">
            howl_target=#{target.howlTarget}
        </if>
        where target_id = #{target.targetId}
    </update>

    <!--得到数据库记录中最新的年,季度-->
    <select id="getNewYearQuarter" resultType="java.util.Map">
        select max(quarterly) as quarterly,year from performance_target GROUP BY year HAVING MAX(year)
    </select>

    <!--得到数据库记录中最新的年,季度-->
    <select id="getNewYearQuarterOrgan" resultMap="BaseColumn">
        select max(quarterly) as quarterly,year from performance_target WHERE
        element_id in (select element_id from performance_element where type!='城市公司' having CHAR_LENGTH(element_id)=4)
        GROUP BY year HAVING MAX(year)
    </select>

    <!--得到数据库记录中最新的年,季度-->
    <select id="getNewYearQuarterCompany" resultType="java.util.Map">
        select max(quarterly) as quarterly,year from performance_target WHERE
        element_id in (select element_id from performance_element where type ='城市公司' having CHAR_LENGTH(element_id)=4)
        GROUP BY year HAVING MAX(year)
    </select>


    <select id="selectByRecordId" parameterType="long" resultMap="BaseColumn">
        select
        <include refid="SqlColumn"/>
        from performance_target
        where record_id = #{recordId}
    </select>
</mapper>