<?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.marketing.QuotaMapper">
  <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.marketing.Quota">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="year" jdbcType="SMALLINT" property="year" />
    <result column="organ_id" jdbcType="VARCHAR" property="organId" />
    <result column="is_delete" jdbcType="TINYINT" property="isDelete" />
    <result column="contract_area" jdbcType="DECIMAL" property="contractArea" />
    <result column="contract_area_1st" jdbcType="DECIMAL" property="contractArea1st" />
    <result column="contract_area_2nd" jdbcType="DECIMAL" property="contractArea2nd" />
    <result column="contract_area_3rd" jdbcType="DECIMAL" property="contractArea3rd" />
    <result column="contract_area_4th" jdbcType="DECIMAL" property="contractArea4th" />
    <result column="charge_area" jdbcType="DECIMAL" property="chargeArea" />
    <result column="charge_area_complete" jdbcType="DECIMAL" property="chargeAreaComplete" />
    <result column="charge_area_1st" jdbcType="DECIMAL" property="chargeArea1st" />
    <result column="charge_area_complete_1st" jdbcType="DECIMAL" property="chargeAreaComplete1st" />
    <result column="charge_area_2nd" jdbcType="DECIMAL" property="chargeArea2nd" />
    <result column="charge_area_complete_2nd" jdbcType="DECIMAL" property="chargeAreaComplete2nd" />
    <result column="charge_area_3rd" jdbcType="DECIMAL" property="chargeArea3rd" />
    <result column="charge_area_complete_3rd" jdbcType="DECIMAL" property="chargeAreaComplete3rd" />
    <result column="charge_area_4th" jdbcType="DECIMAL" property="chargeArea4th" />
    <result column="charge_area_complete_4th" jdbcType="DECIMAL" property="chargeAreaComplete4th" />
    <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" />
  </resultMap>

  <select fetchSize="1" id="findByUk" parameterType="Map" resultMap="BaseResultMap">
    SELECT q.*
    FROM marketing_quota q
    <where>
      <if test="year !=null">
        AND q.year = #{year} AND q.is_delete = 0
      </if>
      <if test="organId != null">
        AND q.organ_id = #{organId}
      </if>
      <if test="isDelete != null">
        AND t.is_delete = 0
      </if>
    </where>
    LIMIT 1
  </select>

  <select
          id="sum"
          fetchSize="1"
          parameterType="map"
          resultMap="BaseResultMap">
    SELECT
    sum( t.contract_area ) AS contract_area,
    sum( t.charge_area ) AS charge_area,
    sum( t.charge_area_1st ) AS charge_area_1st,
    sum( t.charge_area_2nd ) AS charge_area_2nd,
    sum( t.charge_area_3rd ) AS charge_area_3rd,
    sum( t.charge_area_4th ) AS charge_area_4th,
    sum( t.contract_area_1st ) AS contract_area_1st,
    sum( t.contract_area_2nd ) AS contract_area_2nd,
    sum( t.contract_area_3rd ) AS contract_area_3rd,
    sum( t.contract_area_4th ) AS contract_area_4th
    FROM
    marketing_quota AS t
    <where>
      <if test="1 == 1">
        <![CDATA[
                  AND t.is_delete = 0
                ]]>
      </if>
      <if test="year != null">
        <![CDATA[
                  AND t.year = #{year}
                ]]>
      </if>
      <if test="organId != null and organId!=''">
        <![CDATA[
                  AND t.organ_id >= #{organId}
                ]]>
      </if>
    </where>
  </select>

  <sql id="sqlSelectBasicData">
    <![CDATA[
SELECT
	t.id,
	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_complete_1st,
	t.charge_area_2nd,
	t.charge_area_complete_2nd,
	t.charge_area_3rd,
	t.charge_area_complete_3rd,
	t.charge_area_4th,
	t.charge_area_complete_4th
FROM
	marketing_quota AS t
	LEFT JOIN preOrgan AS o ON ( o.id = t.organ_id )
        ]]>
  </sql>

  <select id="search"
          parameterType="map"
          resultMap="BaseResultMap">
    <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}
                ]]>
      </if>
      <if test="search != null and search != ''">
        <![CDATA[
                AND o.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="BaseResultMap">
    <include refid="sqlSelectBasicData" />
    <![CDATA[
            WHERE t.is_delete = 0 AND t.id = #{id, jdbcType=INTEGER}
        ]]>
  </select>

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

</mapper>