<?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.MarketingContractMapper">
    <resultMap id="MarketingContractResultMap" type="cn.com.countrygarden.bi.main.model.MarketingContract">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <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="contract_area" jdbcType="DECIMAL" property="contractArea"/>
        <result column="quota_contract_area" jdbcType="DECIMAL" property="quotaContractArea"/>
        <result column="contract_rate" jdbcType="DECIMAL" property="contractRate"/>
        <result column="contract_date" jdbcType="DATE" property="contractDate"/>
        <result column="state" jdbcType="VARCHAR" property="state"/>
        <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="contract_month" jdbcType="VARCHAR" property="contractMonth"/>
        <result column="organ_name" jdbcType="VARCHAR" property="organName"/>
        <result column="comm_name" jdbcType="VARCHAR" property="commName"/>
        <result column="project_type" jdbcType="VARCHAR" property="projectType"/>
        <result column="corp_type" jdbcType="VARCHAR" property="corpType"/>
    </resultMap>

    <select id="findByUk"
            fetchSize="1"
            parameterType="Integer"
            resultMap="MarketingContractResultMap">
        SELECT
        t.id,
        t.comm_id,
        t.is_delete,
        t.contract_area,
        t.contract_date,
        t.state,
        t.gmt_create,
        t.create_by,
        t.gmt_modified,
        t.modified_by,
        c.`name` AS comm_name,
        o.`name` AS organ_name
        FROM
        marketing_contract 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="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"
            fetchSize="1"
            parameterType="map"
            resultMap="MarketingContractResultMap">
        SELECT
        COUNT( DISTINCT o.id ) AS organs,
        SUM( t.contract_area ) AS contract_area,
        COUNT( 1 ) AS comms
        FROM
        marketing_contract 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="1 == 1">
                <![CDATA[
                  AND t.is_delete = 0 AND t.state <> 'release'
                ]]>
            </if>
            <if test="afterDate != null">
                <![CDATA[
                  AND t.contract_date >= #{afterDate}
                ]]>
            </if>
            <if test="beforeDate != null">
                <![CDATA[
                  AND t.contract_date <= #{beforeDate}
                ]]>
            </if>
        </where>
    </select>

    <select id="sumBy"
            parameterType="map"
            resultMap="MarketingContractResultMap">
        <![CDATA[
SELECT
        ]]>
        <choose>
            <when test="by != null and by == 'corpType'">
                <![CDATA[
                  c.corp_type,
                ]]>
            </when>
            <when test="by != null and by == 'projectType'">
                <![CDATA[
                  c.project_type,
                ]]>
            </when>
            <otherwise>

            </otherwise>
        </choose>

        <![CDATA[
        COUNT( DISTINCT o.id ) AS organs,
        SUM( t.contract_area ) AS contract_area,
        COUNT( 1 ) AS comms
        FROM
        marketing_contract 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="1 == 1">
                <![CDATA[
                  AND t.is_delete = 0 AND t.state <> '已解约'
                ]]>
            </if>
            <if test="afterDate != null">
                <![CDATA[
                  AND t.contract_date >= #{afterDate}
                ]]>
            </if>
            <if test="beforeDate != null">
                <![CDATA[
                  AND t.contract_date <= #{beforeDate}
                ]]>
            </if>
        </where>
        <choose>
            <when test="by != null and by == 'corpType'">
                <![CDATA[
                  GROUP BY c.corp_type
                ]]>
            </when>
            <when test="by != null and by == 'projectType'">
                <![CDATA[
                  GROUP BY c.project_type
                ]]>
            </when>
            <otherwise>

            </otherwise>
        </choose>
    </select>

    <select id="ranking"
            parameterType="map"
            resultMap="MarketingContractResultMap">
        <![CDATA[
SELECT
	o.`name` AS organ_name,
	o.id AS organ_id,
	SUM( t.contract_area ) AS contract_area
FROM
	marketing_contract 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="1 == 1">
                <![CDATA[
                  AND t.is_delete = 0 AND t.state <> '已解约'
                ]]>
            </if>
            <if test="afterDate != null">
                <![CDATA[
                  AND t.contract_date >= #{afterDate}
                ]]>
            </if>
        </where>
        <![CDATA[
GROUP BY
	organ_name
ORDER BY
	contract_area DESC
        ]]>
        <if test="limit != null">
            LIMIT #{limit}
        </if>
    </select>

    <select id="rankingByOrgan"
            parameterType="map"
            resultMap="MarketingContractResultMap">
        <![CDATA[
SELECT
	q.organ_id,
	q.organ_name,
	IF ( ISNULL( d.contract_area ), 0, d.contract_area ) AS contract_area,
	]]>
        <choose>
            <when test="season != null and season == 0">
                <![CDATA[
    q.contract_area AS quota_contract_area,
	IF ( ISNULL( d.contract_area ), 0, d.contract_area ) / q.contract_area * 100 AS contract_rate
            ]]>
            </when>
            <when test="season != null and season == 1">
                <![CDATA[
    q.contract_area_1st AS quota_contract_area,
	IF ( ISNULL( d.contract_area ), 0, d.contract_area ) / q.contract_area_1st * 100 AS contract_rate
            ]]>
            </when>
            <when test="season != null and season == 2">
                <![CDATA[
    q.contract_area_2nd AS quota_contract_area,
	IF ( ISNULL( d.contract_area ), 0, d.contract_area ) / q.contract_area_2nd * 100 AS contract_rate
            ]]>
            </when>
            <when test="season != null and season == 3">
                <![CDATA[
    q.contract_area_3rd AS quota_contract_area,
	IF ( ISNULL( d.contract_area ), 0, d.contract_area ) / q.contract_area_3rd * 100 AS contract_rate
            ]]>
            </when>
            <when test="season != null and season == 4">
                <![CDATA[
    q.contract_area_4th AS quota_contract_area,
	IF ( ISNULL( d.contract_area ), 0, d.contract_area ) / q.contract_area_4th * 100 AS contract_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}
	) AS q
	LEFT JOIN (
	SELECT
		o.id AS organ_id,
		SUM( t.contract_area ) AS contract_area
	FROM
		marketing_contract 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.contract_date >= #{after, jdbcType=INTEGER}
                ]]>
            </when>
            <when test="season != null and season > 0 and after != null and before != null">
                <![CDATA[
                AND t.contract_date >= #{after, jdbcType=VARCHAR}
                AND t.contract_date <= #{before, jdbcType=VARCHAR}
                ]]>
            </when>
        </choose>
        <![CDATA[
		AND t.state <> '已解约'
	GROUP BY
		o.id
	) AS d ON ( d.organ_id = q.organ_id )
ORDER BY
	contract_rate DESC
        ]]>
        <if test="offset != null and limit != null">
            <![CDATA[
            LIMIT #{offset, jdbcType=INTEGER}, #{limit, jdbcType=INTEGER}
        ]]>
        </if>
    </select>

    <select id="findLastContract"
            fetchSize="1"
            parameterType="map"
            resultMap="MarketingContractResultMap">
        <![CDATA[








        SELECT
        t.*
        FROM
        marketing_contract 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
        AND t.state <> '已解约'
        ORDER BY
        t.contract_date DESC,
        t.id DESC
        LIMIT 1








        ]]>
    </select>

    <select id="tendency"
            parameterType="map"
            resultMap="MarketingContractResultMap">
        <![CDATA[
SELECT
	DATE_FORMAT( t.contract_date, '%m' ) AS contract_month,
	SUM( t.contract_area ) AS contract_area
FROM
	marketing_contract AS t
        ]]>
        <where>
            <if test="1 == 1">
                <![CDATA[
                  AND t.is_delete = 0 AND t.state <> '已解约'
                ]]>
            </if>
            <if test="afterDate != null">
                <![CDATA[
                  AND t.contract_date >= #{afterDate}
                ]]>
            </if>
        </where>
        <![CDATA[
GROUP BY
	contract_month
ORDER BY
	contract_month
        ]]>
        <if test="limit != null">
            LIMIT #{limit}
        </if>
    </select>

    <sql id="sqlSelectBasicColumn">
        <![CDATA[
SELECT
	t.id,
	t.comm_id,
	t.is_delete,
	t.contract_area,
	t.contract_date,
	t.state,
	t.gmt_create,
	t.create_by,
	t.gmt_modified,
	t.modified_by,
	c.`name` AS comm_name,
	c.project_type,
	c.corp_type,
	o.`name` AS organ_name
FROM
	marketing_contract 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="findById"
            parameterType="int"
            resultMap="MarketingContractResultMap">
            <include refid="sqlSelectBasicColumn" />
        <![CDATA[
            WHERE t.is_delete = 0 AND t.id = #{id, jdbcType=INTEGER}
        ]]>
    </select>

    <select id="listProjectByOrgan"
            parameterType="map"
            resultMap="MarketingContractResultMap">
        <include refid="sqlSelectBasicColumn" />
        <where>
            <if test="1 == 1">
                <![CDATA[
            AND t.is_delete = 0 AND t.state <> '已解约'
                ]]>
            </if>
            <if test="organId != null">
                <![CDATA[
                  AND o.id = #{organId, jdbcType=INTEGER}
                ]]>
            </if>
            <if test="season != null and season = 0 and after != null">
                <![CDATA[
                  AND t.contract_date >= #{after, jdbcType=VARCHAR}
                ]]>
            </if>
            <if test="season != null and season > 0 and after != null and before != null">
                <![CDATA[
                AND t.contract_date >= #{after, jdbcType=VARCHAR} AND t.contract_date <= #{before, jdbcType=VARCHAR}
                ]]>
            </if>
        </where>
        <![CDATA[
        ORDER  BY t.contract_date DESC
        ]]>
        <if test="offset != null and limit != null">
            <![CDATA[
            LIMIT #{offset, jdbcType=INTEGER}, #{limit, jdbcType=INTEGER}
            ]]>
        </if>
    </select>


    <select id="search"
            parameterType="cn.com.countrygarden.bi.main.model.MarketingContract"
            resultMap="MarketingContractResultMap">
        <![CDATA[
SELECT
	t.id,
	t.comm_id,
	t.is_delete,
	t.contract_area,
	t.contract_date,
	t.state,
	t.gmt_create,
	t.create_by,
	t.gmt_modified,
	t.modified_by,
	c.`name` AS comm_name,
	o.`name` AS organ_name,
	c.project_type,
	c.corp_type
FROM
	marketing_contract 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="1 == 1">
                <![CDATA[
                AND t.is_delete = 0
                ]]>
            </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>
            <if test="state != null and state != ''">
                <![CDATA[
                  AND t.state = #{state, jdbcType=VARCHAR}
                ]]>
            </if>
            <if test="projectType != null and projectType != ''">
                <![CDATA[
                  AND t.project_type = #{projectType, jdbcType=VARCHAR}
                ]]>
            </if>
            <if test="corpType != null and corpType != ''">
                <![CDATA[
                  AND t.corp_type = #{corpType, jdbcType=VARCHAR}
                ]]>
            </if>
            <if test="after != null">
                <![CDATA[
                  AND t.contract_date >= #{after, jdbcType=DATE}
                ]]>
            </if>
            <if test="before != null">
                <![CDATA[
                  AND t.contract_date <= #{before, jdbcType=DATE}
                ]]>
            </if>
        </where>

        <![CDATA[
ORDER BY
	t.contract_date DESC
        ]]>

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

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