<?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.MarketingChargeMapper">
    <resultMap id="MarketingChargeResultMap" type="cn.com.countrygarden.bi.main.model.MarketingCharge">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="year" jdbcType="SMALLINT" property="year"/>
        <result column="month" jdbcType="TINYINT" property="month"/>
        <result column="season" jdbcType="TINYINT" property="season"/>
        <result column="comm_id" jdbcType="INTEGER" property="commId"/>
        <result column="organ_id" jdbcType="INTEGER" property="organId"/>
        <result column="is_delete" jdbcType="TINYINT" property="isDelete"/>
        <result column="charge_area" jdbcType="DECIMAL" property="chargeArea"/>
        <result column="memo" jdbcType="VARCHAR" property="memo"/>
        <result column="gmt_create" jdbcType="TIMESTAMP" property="gmtCreate"/>
        <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
        <result column="gmt_modified" jdbcType="TIMESTAMP" property="gmtModified"/>
        <result column="modified_by" jdbcType="VARCHAR" property="modifiedBy"/>
        <result column="comms" jdbcType="INTEGER" property="comms"/>
        <result column="organ_name" jdbcType="VARCHAR" property="organName"/>
        <result column="quota_charge_area" jdbcType="DECIMAL" property="quotaChargeArea"/>
        <result column="charge_rate" jdbcType="DECIMAL" property="chargeRate"/>
        <result column="comm_name" jdbcType="VARCHAR" property="commName"/>
        <result column="develop_subject" jdbcType="VARCHAR" property="developSubject"/>
        <result column="nc_number" jdbcType="VARCHAR" property="ncNumber"/>
        <result column="nc_account" jdbcType="VARCHAR" property="ncAccount"/>
    </resultMap>

    <select id="findByUk"
            fetchSize="1"
            parameterType="Map"
            resultMap="MarketingChargeResultMap">
        SELECT
        t.id,
        t.`year`,
        t.`month`,
        t.comm_id,
        t.is_delete,
        t.charge_area,
        t.memo,
        t.gmt_create,
        t.create_by,
        t.gmt_modified,
        t.modified_by,
        c.`name` AS comm_name,
        o.`name` AS organ_name
        FROM
        marketing_charge AS t
        LEFT JOIN preComm AS c ON ( c.id = t.comm_id )
        LEFT JOIN preOrgan AS o ON ( o.id = c.organ_id )
        <where>
            <if test="year != null">
                AND t.`year` = #{year, jdbcType = SMALLINT}
            </if>
            <if test="month != null">
                AND t.`month` = #{month, jdbcType = TINYINT}
            </if>
            <if test="commId != null">
                AND t.comm_id = #{commId}
            </if>
            <if test="isDelete != null">
                AND t.is_delete = #{isDelete}
            </if>
        </where>
        LIMIT 1
    </select>

    <select id="sum"
            parameterType="map"
            resultMap="MarketingChargeResultMap">
        <![CDATA[
SELECT
	SUM( t.charge_area ) AS charge_area,
	COUNT( 1 ) AS comms
FROM
	marketing_charge AS t
        ]]>
        <where>
            <if test="1 == 1">
                <![CDATA[
                  AND t.is_delete = 0
                ]]>
            </if>
            <if test="year != null">
                <![CDATA[
                  	AND t.`year` = #{year}
                ]]>
            </if>
        </where>
        <if test="limit != null">
            LIMIT #{limit}
        </if>
    </select>

    <select id="sumBy"
            parameterType="map"
            resultMap="MarketingChargeResultMap">
        <![CDATA[
SELECT
        ]]>
        <if test="by != null and by == 'season'">
            <![CDATA[
                  CASE
                    WHEN t.`month` BETWEEN 1 AND 3 THEN 1
                    WHEN t.`month` BETWEEN 4 AND 6 THEN 2
                    WHEN t.`month` BETWEEN 7 AND 9 THEN 3
                    WHEN t.`month` BETWEEN 10 AND 12 THEN 4
                    END AS season,
                ]]>
        </if>
        <![CDATA[
	SUM( t.charge_area ) AS charge_area,
	COUNT( 1 ) AS comms
FROM
	marketing_charge AS t
        ]]>
        <where>
            <if test="1 == 1">
                <![CDATA[
                  AND t.is_delete = 0
                ]]>
            </if>
            <if test="year != null">
                <![CDATA[
                  	AND t.`year` = #{year}
                ]]>
            </if>
        </where>

        <if test="by != null and by == 'season'">
            <![CDATA[
            GROUP BY season
            ]]>
        </if>
    </select>

    <select id="rankingByOrganWithinSum"
            parameterType="map"
            resultMap="MarketingChargeResultMap">
        <![CDATA[
SELECT
	q.organ_id,
	q.organ_name,
    IF ( ISNULL( d.charge_area ), 0, d.charge_area ) AS charge_area,
	]]>
        <choose>
            <when test="season != null and season == 0">
                <![CDATA[
	q.charge_area AS quota_charge_area,
	IF ( ISNULL( d.charge_area ), 0, d.charge_area ) / q.charge_area * 100 AS charge_rate
            ]]>
            </when>
            <when test="season != null and season == 1">
                <![CDATA[
    q.charge_area_1st AS quota_charge_area,
	IF ( ISNULL( d.charge_area ), 0, d.charge_area ) / q.charge_area_1st * 100 AS charge_rate
            ]]>
            </when>
            <when test="season != null and season == 2">
                <![CDATA[
    q.charge_area_2nd AS quota_charge_area,
	IF ( ISNULL( d.charge_area ), 0, d.charge_area ) / q.charge_area_2nd * 100 AS charge_rate
            ]]>
            </when>
            <when test="season != null and season == 3">
                <![CDATA[
    q.charge_area_3rd AS quota_charge_area,
	IF ( ISNULL( d.charge_area ), 0, d.charge_area ) / q.charge_area_3rd * 100 AS charge_rate
            ]]>
            </when>
            <when test="season != null and season == 4">
                <![CDATA[
    q.charge_area_4th AS quota_charge_area,
	IF ( ISNULL( d.charge_area ), 0, d.charge_area ) / q.charge_area_4th * 100 AS charge_rate
            ]]>
            </when>
        </choose>
        <![CDATA[
FROM
	(
	SELECT
		t.organ_id,
		o.`name` AS organ_name,
		t.contract_area,
		t.contract_area_1st,
		t.contract_area_2nd,
		t.contract_area_3rd,
		t.contract_area_4th,
		t.charge_area,
		t.charge_area_1st,
		t.charge_area_2nd,
		t.charge_area_3rd,
		t.charge_area_4th
	FROM
		marketing_quota AS t
		LEFT JOIN preOrgan AS o ON ( o.id = t.organ_id )
	WHERE
		t.is_delete = 0
		AND t.`year` = #{year, jdbcType=INTEGER}
	) AS q
	LEFT JOIN (
	SELECT
		o.id AS organ_id,
		o.`name` AS organ_name,
		SUM( t.charge_area ) AS charge_area
	FROM
		marketing_charge AS t
		LEFT JOIN preComm AS c ON ( c.id = t.comm_id )
		LEFT JOIN preOrgan AS o ON ( o.id = c.organ_id )
	WHERE
		t.is_delete = 0
		]]>
        <choose>
            <when test="season != null and season == 0 and after != null">
                <![CDATA[
                AND t.`year` >= #{year, jdbcType=INTEGER}
                ]]>
            </when>
            <when test="season != null and season > 0 and after != null and before != null">
                <![CDATA[
                AND t.`year` >= #{year, jdbcType=INTEGER}
                AND t.`month` >= #{after, jdbcType=INTEGER}
                AND t.`month` <= #{before, jdbcType=INTEGER}
                ]]>
            </when>
        </choose>
        <![CDATA[
	GROUP BY
		o.id
	) AS d ON ( d.organ_id = q.organ_id )
ORDER BY
	charge_rate DESC
        ]]>
        <if test="offset != null and limit != null">
            <![CDATA[
            LIMIT #{offset, jdbcType=INTEGER}, #{limit, jdbcType=INTEGER}
        ]]>
        </if>
    </select>
    <select id="rankingByOrgan"
            parameterType="map"
            resultMap="MarketingChargeResultMap">
        <![CDATA[
SELECT
	t.organ_id,
	o.`name` AS organ_name,
	]]>
        <choose>
            <when test="season != null and season == 0">
                <![CDATA[
	t.charge_area AS quota_charge_area,
	t.charge_area_complete AS charge_area,
	IF ( ISNULL( t.charge_area_complete ), 0, t.charge_area_complete ) / t.charge_area * 100 AS charge_rate
            ]]>
            </when>
            <when test="season != null and season == 1">
                <![CDATA[
	t.charge_area_1st AS quota_charge_area,
	t.charge_area_complete_1st AS charge_area,
	IF ( ISNULL( t.charge_area_complete_1st ), 0, t.charge_area_complete_1st ) / t.charge_area_1st * 100 AS charge_rate
            ]]>
            </when>
            <when test="season != null and season == 2">
                <![CDATA[
	t.charge_area_2nd AS quota_charge_area,
	t.charge_area_complete_2nd AS charge_area,
	IF ( ISNULL( t.charge_area_complete_2nd ), 0, t.charge_area_complete_2nd ) / t.charge_area_2nd * 100 AS charge_rate
            ]]>
            </when>
            <when test="season != null and season == 3">
                <![CDATA[
	t.charge_area_3rd AS quota_charge_area,
	t.charge_area_complete_3rd AS charge_area,
	IF ( ISNULL( t.charge_area_complete_3rd ), 0, t.charge_area_complete_3rd ) / t.charge_area_3rd * 100 AS charge_rate
            ]]>
            </when>
            <when test="season != null and season == 4">
                <![CDATA[
	t.charge_area_4th AS quota_charge_area,
	t.charge_area_complete_4th AS charge_area,
	IF ( ISNULL( t.charge_area_complete_4th ), 0, t.charge_area_complete_4th ) / t.charge_area_4th * 100 AS charge_rate
            ]]>
            </when>
        </choose>
        <![CDATA[
FROM
	marketing_quota AS t
LEFT JOIN preOrgan AS o ON ( o.id = t.organ_id )
		]]>
        <where>
            <if test="1 == 1">
                AND t.is_delete = 0
            </if>
            <if test="year != null">
                AND t.`year` >= #{year, jdbcType=INTEGER}
            </if>
        </where>
        <![CDATA[
ORDER BY
	charge_rate DESC
        ]]>
        <if test="offset != null and limit != null">
            <![CDATA[
            LIMIT #{offset, jdbcType=INTEGER}, #{limit, jdbcType=INTEGER}
        ]]>
        </if>
    </select>


    <sql id="sqlSelectBasicData">
        <![CDATA[
SELECT
	t.id,
	o.id AS organ_id,
	o.`name` AS organ_name,
	c.id AS comm_id,
	c.`name` AS comm_name,
	c.nc_number,
	c.nc_account,
	c.develop_subject,
	t.charge_area,
	t.memo
FROM
	marketing_charge AS t
	LEFT JOIN preComm AS c ON ( c.id = t.comm_id )
	LEFT JOIN preOrgan AS o ON ( o.id = c.organ_id )
        ]]>
    </sql>

    <select id="search"
            parameterType="map"
            resultMap="MarketingChargeResultMap">
        <include refid="sqlSelectBasicData"></include>
        <where>
            <if test="1 == 1">
                <![CDATA[
                  AND t.is_delete = 0
                ]]>
            </if>
            <if test="year != null">
                <![CDATA[
                  AND t.year >= #{year, jdbcType=SMALLINT}
                ]]>
            </if>
            <if test="month != null and month > 0">
                <![CDATA[
                  AND t.month = #{month, jdbcType=TINYINT}
                ]]>
            </if>
            <if test="search != null and search != ''">
                <![CDATA[
                AND (o.name LIKE CONCAT(CONCAT('%', #{search, jdbcType=VARCHAR}),'%')
                OR c.name LIKE CONCAT(CONCAT('%', #{search, jdbcType=VARCHAR}),'%'))
                ]]>
            </if>
        </where>

        <if test="offset >= 0 and limit > 0">
            <![CDATA[
              LIMIT #{offset}, #{limit}
            ]]>
        </if>
    </select>

    <select id="findById"
            parameterType="int"
            resultMap="MarketingChargeResultMap">
        <include refid="sqlSelectBasicData" />
        <![CDATA[
            WHERE t.is_delete = 0 AND t.id = #{id, jdbcType=INTEGER}
        ]]>
    </select>

    <update id="softDelete" parameterType="map">
        <![CDATA[
          UPDATE marketing_charge AS t SET t.is_delete = 1, t.modified_by = #{userCode, jdbcType=VARCHAR} WHERE id = #{id, jdbcType=INTEGER}
        ]]>
    </update>
</mapper>