<?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.PerformanceScoreMapper">
    <resultMap id="BaseColumn" type="cn.com.countrygarden.bi.main.model.performance.PerformanceScore">
        <id column="score_id" property="scoreId" jdbcType="BIGINT"/>
        <result column="record_id" property="recordId" jdbcType="BIGINT"/>
        <result column="element_id" property="elementId" jdbcType="BIGINT"/>
        <result column="elementname" property="elementname" jdbcType="BIGINT"/>
        <result column="hierarchyname" property="hierarchyname" jdbcType="BIGINT"/>
        <result column="score" property="score" jdbcType="REAL"/>
        <result column="totalPage" property="totalPage" jdbcType="BIGINT"/>
        <result column="actual_score" property="actualScore" jdbcType="REAL"/>
        <result column="type" property="type" jdbcType="VARCHAR"/>
        <result column="complete" property="complete" jdbcType="REAL"/>
        <result column="square_no" property="squareNo" jdbcType="VARCHAR"/>
        <result column="quarterly_score" property="quarterlyScore" jdbcType="INTEGER"/>
        <result column="yearly_score" property="yearlyScore" jdbcType="INTEGER"/>
        <result column="year" property="year" jdbcType="INTEGER"/>
        <result column="month" property="month" jdbcType="INTEGER"/>
        <result column="quarterly" property="quarterly" jdbcType="VARCHAR"/>
        <result column="hierarchy" property="hierarchy" jdbcType="VARCHAR"/>
        <result column="hierarchy_id" property="hierarchyId" jdbcType="VARCHAR"/>
        <result column="is_profit_reach" property="isProfitReach" jdbcType="BOOLEAN"/>
        <result column="num" property="num" jdbcType="INTEGER"/>
        <result column="created_date" property="createdDate" jdbcType="DATE"/>
        <result column="created_by" property="createdBy" jdbcType="VARCHAR"/>
        <result column="update_date" property="updateDate" jdbcType="DATE"/>
        <result column="update_by" property="updateBy" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="SqlColumn">
        score_id,record_id,element_id,score,actual_score,type,complete,square_no,quarterly_score,yearly_score,year,month,quarterly,
        hierarchy,hierarchy_id,is_profit_reach,num,created_date,created_by,update_date,update_by
    </sql>


    <insert id="insertScoreList" parameterType="java.util.List">
        INSERT INTO performance_score VALUES
        <foreach collection="scoreList" item="score" separator=",">
            (#{score.scoreId},#{score.elementId},#{score.score},#{score.actualScore},#{score.type},
            #{score.complete},#{score.squareNo},#{score.quarterlyScore},#{score.yearlyScore},#{score.year},
            #{score.month},#{score.quarterly},#{score.hierarchy},#{score.hierarchyId},#{score.num},
            #{score.isProfitReach},#{score.createdDate},#{score.createdBy},#{score.updateDate},#{score.updateBy})
        </foreach>
    </insert>


    <select id="selectScoreList" resultMap="BaseColumn">
        SELECT
        <include refid="SqlColumn"/>
        FROM performance_score
        WHERE element_id = #{score.elementId}
        <if test="score.year != null">
            AND year = #{score.year}
        </if>
        <if test="score.quarterly != null">
            AND quarterly = #{score.quarterly}
        </if>
        <if test="score.pageCur != null">
            <if test="score.pageSize != null">
                LIMIT #{score.pageCur},#{score.pageSize}
            </if>
        </if>
    </select>

    <!--/**-->
    <!--* modify by white-->
    <!--* @Description: 修改将t.hierarchy_id = #{score.hierarchyId} -> t.quarterly = #{score.quarterly}
    -->
    <!--* @Date: 2018-06-27 10:57-->
    <!--*/-->

    <select id="getTotalPage" resultMap="BaseColumn">
        SELECT COUNT(*) as totalPage
        FROM
        performance_score t
        where
        <if test="score.elementId !=null">
            t.element_id = #{score.elementId}
        </if>
        <if test="score.year !=null">
            and t.year = #{score.year}
        </if>
        <if test="score.quarterly !=null">
            and t.quarterly = #{score.quarterly}
        </if>
    </select>

    <select id="getByHierarchyId" resultMap="BaseColumn">
    SELECT t.*
    FROM
    performance_score t
    where
    t.element_id = #{score.elementId} and t.hierarchy_id = #{score.hierarchyId} and t.year=#{score.year}
     </select>

    <select id="getscoreByHierarchyId" resultMap="BaseColumn">
    SELECT t.*
    FROM
    performance_score t
    where
    t.element_id = #{score.elementId} and t.hierarchy_id = #{score.hierarchyId} and t.year=#{score.year} and t.quarterly=#{score.quarterly}
     </select>

    <select id="getNewScore" resultMap="BaseColumn">
        SELECT score,actual_score,complete,hierarchy_id,(SELECT hierarchy_name from performance_env where
        hierarchy_id=s.hierarchy_id) as hierarchyname
        from performance_score s
        where s.element_id=#{score.elementId} and hierarchy_id=#{score.hierarchyId} and year=#{score.year}
        <if test="score.quarterly !=null">
            and quarterly=#{score.quarterly}
        </if>
    </select>

    <!--/**-->
    <!--* modify by white-->
    <!--* @Description: 修改更新分数部分，去掉complete后面逗号-->
    <!--* @Date: 2018-06-26 14:43-->
    <!--*/-->

    <update id="UpdateNewScore">
        UPDATE
        performance_score s
        SET
        hierarchy_id=#{score.hierarchyId},
        quarterly=#{score.quarterly},
        element_id=#{score.elementId},
        score=#{score.score},
        <if test="score.actualScore !=null">
            actual_score=#{score.actualScore},
        </if>
        <if test="score.yearlyScore !=null">
            yearly_score=#{score.yearlyScore},
        </if>
        complete=#{score.complete}
        where element_id=#{score.elementId} and hierarchy_id=#{score.hierarchyId} and year=#{score.year}
        <if test="score.quarterly !=null">
            and quarterly=#{score.quarterly}
        </if>
    </update>

    <update id="UpdateNewScoretwo">
        UPDATE
        performance_score s
        SET
        hierarchy_id=#{score.hierarchyId},
        quarterly=#{score.quarterly},
        element_id=#{score.elementId},
        score=#{score.score},
        actual_score=#{score.actualScore},
        yearly_score=#{score.yearlyScore}
        where element_id=#{score.elementId} and hierarchy_id=#{score.hierarchyId} and year=#{score.year}
        <if test="score.quarterly !=null">
            and quarterly=#{score.quarterly}
        </if>
    </update>

    <insert id="insertScore" parameterType="cn.com.countrygarden.bi.main.model.performance.PerformanceScore">
        INSERT INTO performance_score VALUES
            (#{score.scoreId},#{score.elementId},#{score.score},#{score.actualScore},#{score.type},
            #{score.complete},#{score.squareNo},#{score.yearlyScore},#{score.quarterlyScore},#{score.year},
            #{score.month},#{score.quarterly},#{score.hierarchy},#{score.hierarchyId},#{score.num},
            #{score.isProfitReach},#{score.createdDate},#{score.createdBy},#{score.updateDate},#{score.updateBy})
    </insert>
    <!---->
    <!--/**-->
    <!--* modify by white-->
    <!--* @Description: 修改数据将  quarterly = #{score.elementId} -> quarterly = #{score.quarterly}-->
    <!--* @Date: 2018-06-27 10:30-->
    <!--*/-->

    <select id="selectNewScoreList" resultMap="BaseColumn">
        SELECT
        <include refid="SqlColumn"/>
        FROM performance_score
        WHERE 1 = 1
        <if test="score.year != null">
            AND year = #{score.year}
        </if>
        <if test="score.quarterly != null">
            AND quarterly = #{score.quarterly}
        </if>
        <if test="score.elementId != null">
            AND element_id = #{score.elementId}
        </if>
        limit #{score.pageCur},#{score.pageSize}
    </select>

    <select id="insertQuarterlySumScore">
insert into performance_score(element_id,hierarchy_id,score,actual_score,year,quarterly,created_date,created_by,update_date,update_by)
    select 0, precent.hierarchy_id as hierarchy_id,precent.score as score,(precent.score + plus.score) as actual_score,#{year} ,#{quarter},now() as cd,'0000' as cb,now() as ud,'0000' as ub
    from
        (
        select hierarchy_id,IF(SUM(IF(is_check = 1,weight,0))=0,0,ROUND(SUM(IF(is_check = 1,score,0)) / SUM(IF(is_check = 1,weight,0)),2)) as score	-- 带权重的指标总分值
        from (
					select e.element_id,t.hierarchy_id,ROUND((s.score * t.weight),2)  as score,t.weight,t.is_check
					FROM
					(select * from performance_element where type != '加/减分项' and type!='城市公司') e
					LEFT JOIN (select * from performance_target where  year = #{year}  and quarterly = #{quarter} ) t
					on e.element_id = t.element_id
					LEFT JOIN (select * from performance_score where year = #{year}  and quarterly = #{quarter}) s
					on t.element_id = s.element_id and t.hierarchy_id = s.hierarchy_id
					HAVING CHAR_LENGTH(element_id) = 4 ) a
                    GROUP BY hierarchy_id
        ) precent,

        (
        select hierarchy_id,SUM(IF(is_check = 1,score,0)) as score		-- 加减分项值
			from (
					select e.element_id,t.hierarchy_id,s.score,t.is_check
					FROM
					(select * from performance_element where type = '加/减分项' and type!='城市公司') e
					LEFT JOIN (select * from performance_target where  year = #{year}  and quarterly = 5 ) t
					on e.element_id = t.element_id
					LEFT JOIN (select * from performance_score where year = #{year}  and quarterly = #{quarter}) s
					on t.element_id = s.element_id and t.hierarchy_id = s.hierarchy_id
					HAVING CHAR_LENGTH(element_id) = 4 ) a
                    GROUP BY hierarchy_id
            ) plus
    where precent.hierarchy_id = plus.hierarchy_id
    </select>


    <!--城市公司的计算-->
    <select id="insertQuarterlySumScoreCompany">
        insert into performance_score(element_id,hierarchy_id,score,actual_score,year,quarterly,created_date,created_by,update_date,update_by)
            select 1, precent.hierarchy_id as hierarchy_id,precent.score as score,precent.score as actual_score,#{year} ,#{quarter},now() as cd,'0000' as cb,now() as ud,'0000' as ub
            from
                (
                select hierarchy_id,IF(SUM(IF(is_check = 1,weight,0))=0,0,ROUND(SUM(IF(is_check = 1,score,0)) / SUM(IF(is_check = 1,weight,0)),2)) as score	-- 带权重的指标总分值
                from (
                            select e.element_id,t.hierarchy_id,ROUND((s.score * t.weight),2)  as score,t.weight,t.is_check
                            FROM
                            (select * from performance_element where type = '城市公司') e
                            LEFT JOIN (select * from performance_target where  year = #{year}  and quarterly = #{quarter} ) t
                            on e.element_id = t.element_id
                            LEFT JOIN (select * from performance_score where year = #{year}  and quarterly = #{quarter}) s
                            on t.element_id = s.element_id and t.hierarchy_id = s.hierarchy_id
                            HAVING CHAR_LENGTH(element_id) = 4 ) a
                            GROUP BY hierarchy_id
                ) precent
    </select>

    <select id="selectCountQuarterByYear" resultType="int">
        select count(DISTINCT quarterly) from performance_score where year = #{year}
    </select>
    <delete id="deleteQuarterlySumScore">
        delete from performance_score where (element_id = 0 or element_id = 1) and year = #{year} and quarterly = #{quarter};
    </delete>


    <insert id="insertScoreSelective" parameterType="PerformanceScore">
        insert into performance_score
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="recordId != null">
                record_id,
            </if>
            <if test="elementId != null">
                element_id,
            </if>
            <if test="score != null">
                score,
            </if>
            <if test="actualScore != null">
                actual_score,
            </if>
            <if test="type != null">
                `type`,
            </if>
            <if test="complete != null">
                complete,
            </if>
            <if test="squareNo != null">
                square_no,
            </if>
            <if test="yearlyScore != null">
                yearly_score,
            </if>
            <if test="quarterlyScore != null">
                quarterly_score,
            </if>
            <if test="year != null">
                `year`,
            </if>
            <if test="month != null">
                `month`,
            </if>
            <if test="quarterly != null">
                quarterly,
            </if>
            <if test="hierarchy != null">
                hierarchy,
            </if>
            <if test="hierarchyId != null">
                hierarchy_id,
            </if>
            <if test="num != null">
                num,
            </if>
            <if test="isProfitReach != null">
                is_profit_reach,
            </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="score != null">
                #{score},
            </if>
            <if test="actualScore != null">
                #{actualScore},
            </if>
            <if test="type != null">
                #{type},
            </if>
            <if test="complete != null">
                #{complete},
            </if>
            <if test="squareNo != null">
                #{squareNo},
            </if>
            <if test="yearlyScore != null">
                #{yearlyScore},
            </if>
            <if test="quarterlyScore != null">
                #{quarterlyScore},
            </if>
            <if test="year != null">
                #{year},
            </if>
            <if test="month != null">
                #{month},
            </if>
            <if test="quarterly != null">
                #{quarterly},
            </if>
            <if test="hierarchy != null">
                #{hierarchy},
            </if>
            <if test="hierarchyId != null">
                #{hierarchyId},
            </if>
            <if test="num != null">
                #{num},
            </if>
            <if test="isProfitReach != null">
                #{isProfitReach},
            </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="updateScoreSelective" parameterType="cn.com.countrygarden.bi.main.model.performance.PerformanceScore">
        update performance_score
        <set>
            <if test="elementId != null">
                element_id = #{elementId},
            </if>
            <if test="score != null">
                score = #{score},
            </if>
            <if test="actualScore != null">
                actual_score = #{actualScore},
            </if>
            <if test="type != null">
                type = #{type},
            </if>
            <if test="complete != null">
                complete = #{complete},
            </if>
            <if test="squareNo != null">
                square_no = #{squareNo},
            </if>
            <if test="yearlyScore != null">
                yearly_score = #{yearlyScore},
            </if>
            <if test="quarterlyScore != null">
                quarterly_score = #{quarterlyScore},
            </if>
            <if test="year != null">
                year = #{year},
            </if>
            <if test="month != null">
                month = #{month},
            </if>
            <if test="quarterly != null">
                quarterly = #{quarterly},
            </if>
            <if test="hierarchy != null">
                hierarchy = #{hierarchy},
            </if>
            <if test="hierarchyId != null">
                hierarchy_id = #{hierarchyId},
            </if>
            <if test="num != null">
                num = #{num},
            </if>
            <if test="isProfitReach != null">
                is_profit_reach = #{isProfitReach},
            </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 record_id = #{recordId}
    </update>


    <select id="getLastQuarterSort" resultMap="BaseColumn">
       SELECT
        @rank :=@rank + 1 AS num,
        hierarchy_id,
        score_id,
        actual_score
        FROM
            (
                SELECT
                    *
                FROM
                    performance_score
                WHERE
                    hierarchy_id IN (
                        SELECT
                            hierarchy_id
                        FROM
                            performance_env
                        WHERE
                            IsDelete = 0
                        AND IsComp = 1
                        AND hierarchy_name NOT LIKE '测试'
                    )
            ) a,
            (SELECT(@rank := 0)) b
        WHERE
            element_id = #{element}
        AND YEAR = #{year}
        AND quarterly = #{quarter}
        ORDER BY
            actual_score DESC


    </select>

    <!--得到城市公司的排名-->
    <select id="getLastQuarterSortCompany" resultMap="BaseColumn">
       SELECT
        @rank :=@rank + 1 AS num,
        hierarchy_id,
        score_id,
        actual_score
        FROM
            (
                SELECT
                    *
                FROM
                    performance_score
                WHERE
                    hierarchy_id IN (
                        select
                        hierarchy_id
                        from performance_company
                        where is_delete=0
                        and hierarchy_name NOT like '测试'
                    )
            ) a,
            (SELECT(@rank := 0)) b
        WHERE
            element_id = #{element}
        AND YEAR = #{year}
        AND quarterly = #{quarter}
        ORDER BY
            actual_score DESC
    </select>

    <update id="updateNum">
        update performance_score set num = #{performanceScore.num} where score_id = #{performanceScore.scoreId}
    </update>


    <select id="selectByRecordId" parameterType="long" resultMap="BaseColumn">
        select
        
        score_id,record_id,element_id,score,actual_score,type,complete,square_no,quarterly_score,yearly_score,year,month,quarterly,
        hierarchy,hierarchy_id,is_profit_reach,num,created_date,created_by,update_date,update_by
        
        
        from performance_score
        where record_id = #{recordId}
    </select>

    <delete id="deleteAllByRecordIds" parameterType="java.util.Map">
        delete
        from performance_score
        where record_id in
        (select record_id from performance_record where (element_id = 0 or element_id = 1) and year = #{year} and quarter = #{quarter})
    </delete>

    <insert id="insertQuarterlySumScore2" parameterType="java.util.Map">
        <if test="typeNo == '1'.toString()">
            insert into performance_score(record_id,score,actual_score)
            select b.record_id,precent.score,(precent.score + 0) as actual_score
            from
            (
                select any_value(record_id) as record_id,any_value(hierachy_id) as hierachy_id,any_value(element_id) as element_id,IF(SUM(IF(is_examine = 1,weight,0))=0,0,ROUND(SUM(IF(is_examine = 1,score,0)) / SUM(IF(is_examine = 1,weight,0)),2)) as score
                FROM
                (
                    select t.record_id,t.hierachy_id,ROUND(s.score * t.weight,2) as score,t.weight,t.is_examine,t.element_id
                    from
                      (select element_id from performance_element where type != '加/减分项' and type_no = 1) e
                      left join (select record_id,weight,is_examine,element_id,hierachy_id from performance_record where year = #{year} and quarter = #{quarter}) t
                      on e.element_id = t.element_id
                      left join (select score,record_id from performance_score ) s
                      on t.record_id = s.record_id
                      HAVING CHAR_LENGTH(element_id) = 4
                ) a
                GROUP BY hierachy_id
            ) precent,
            (select record_id,hierachy_id from performance_record where year = #{year} and quarter = #{quarter} and element_id = 0) b
            where precent.hierachy_id = b.hierachy_id
        </if>
        <if test="typeNo == '2'.toString()">
            insert into performance_score(record_id,score,actual_score)
            select b.record_id,precent.score as score,precent.score as actual_score
            from
            (
            select any_value(record_id) as record_id,any_value(hierachy_id) as hierachy_id,IF(SUM(IF(is_examine = 1,weight,0))=0,0,ROUND(SUM(IF(is_examine = 1,score,0)) / SUM(IF(is_examine = 1,weight,0)),2)) as score
            FROM
            (
            select t.record_id,t.hierachy_id,ROUND(s.score * t.weight,2) as score,t.weight,t.is_examine,t.element_id
            from
            (select element_id from performance_element where type_no = 2) e
            left join (select record_id,weight,is_examine,element_id,hierachy_id from performance_record where year = #{year} and quarter = #{quarter}) t
            on e.element_id = t.element_id
            left join (select score,record_id from performance_score) s
            on t.record_id = s.record_id
            HAVING CHAR_LENGTH(element_id) = 4
            ) a
            GROUP BY hierachy_id
            ) precent,
            (select record_id,hierachy_id from performance_record where year = #{year} and quarter = #{quarter} and element_id = 1) b
            where precent.hierachy_id = b.hierachy_id
        </if>
    </insert>

</mapper>