<?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.MarketingProposalMapper">
    <resultMap id="MarketingProposalResultMap" type="cn.com.countrygarden.bi.main.model.MarketingProposal">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="comm_id" jdbcType="INTEGER" property="commId"/>
        <result column="comms" jdbcType="INTEGER" property="comms"/>
        <result column="proposal_date" jdbcType="DATE" property="proposalDate"/>
        <result column="is_delete" jdbcType="TINYINT" property="isDelete"/>
        <result column="result" jdbcType="VARCHAR" property="result"/>
        <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="proposal_month" jdbcType="VARCHAR" property="proposalMonth"/>
        <result column="proposal_organ_name" jdbcType="VARCHAR" property="proposalOrganName"/>
        <result column="project_type" jdbcType="VARCHAR" property="projectType"/>
        <result column="corp_type" jdbcType="VARCHAR" property="corpType"/>
        <result column="project_nature" jdbcType="VARCHAR" property="projectNature"/>
        <result column="area" jdbcType="DECIMAL" property="area"/>
        <result column="contacts" jdbcType="VARCHAR" property="contacts"/>
        <result column="organ_name" jdbcType="VARCHAR" property="organName"/>
        <result column="comm_name" jdbcType="VARCHAR" property="commName"/>
        <result column="pass_comms" jdbcType="INTEGER" property="passComms"/>
        <result column="pending_comms" jdbcType="INTEGER" property="pendingComms"/>
        <result column="shelve_comms" jdbcType="INTEGER" property="shelveComms"/>
    </resultMap>

    <select id="sum"
            fetchSize="1"
            parameterType="map"
            resultMap="MarketingProposalResultMap">
        SELECT
        sum( c.area ) AS area,
        COUNT( 1 ) AS comms
        FROM
        marketing_proposal 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.result = '通过'
                ]]>
            </if>
            <if test="afterDate != null">
                <![CDATA[
                  AND t.proposal_date >= #{afterDate}
                ]]>
            </if>
            <if test="beforeDate != null">
                <![CDATA[
                  AND t.proposal_date <= #{beforeDate}
                ]]>
            </if>
        </where>
    </select>

    <select id="findByUk" fetchSize="1" parameterType="Map" resultMap="MarketingProposalResultMap">
        SELECT
        t.id,
        t.comm_id,
        t.is_delete,
        t.proposal_date,
        t.result,
        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_proposal 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="proposalDate != null">
                AND t.proposal_date = #{proposalDate}
            </if>
            <if test="isDelete != null">
                AND t.is_delete = 0
            </if>
        </where>
        LIMIT 1
    </select>

    <select id="tendency"
            parameterType="map"
            resultMap="MarketingProposalResultMap">
        <![CDATA[
SELECT
	DATE_FORMAT( t.proposal_date, '%m' ) AS proposal_month,
	sum( IF ( t.result = "通过", 1, 0 ) ) AS pass_comms,
	sum( IF ( t.result = "待定", 1, 0 ) ) AS pending_comms,
	sum( IF ( t.result = "停止", 1, 0 ) ) AS shelve_comms,
	COUNT( 1 ) AS comms
FROM
	marketing_proposal AS t
	LEFT JOIN preComm AS c ON ( c.id = t.comm_id )
        ]]>
        <where>
            <if test="1 == 1">
                <![CDATA[
                  AND t.is_delete = 0
                ]]>
            </if>
            <if test="afterDate != null">
                <![CDATA[
                  AND t.proposal_date >= #{afterDate}
                ]]>
            </if>
        </where>
        <![CDATA[
GROUP BY
	proposal_month
        ]]>
        <if test="limit != null">
            LIMIT #{limit}
        </if>
    </select>

    <select id="tendencyByResult"
            parameterType="map"
            resultMap="MarketingProposalResultMap">
        <![CDATA[
SELECT
	DATE_FORMAT( t.proposal_date, '%m' ) AS proposal_month,
	t.result,
	COUNT( 1 ) AS comms
FROM
	marketing_proposal AS t
	LEFT JOIN preComm AS c ON ( c.id = t.comm_id )
        ]]>
        <where>
            <if test="1 == 1">
                <![CDATA[
                  AND t.is_delete = 0
                ]]>
            </if>
            <if test="afterDate != null">
                <![CDATA[
                  AND t.proposal_date >= #{afterDate}
                ]]>
            </if>
        </where>
        <![CDATA[
GROUP BY
	proposal_month,
	t.result
        ]]>
        <if test="limit != null">
            LIMIT #{limit}
        </if>
    </select>

    <sql id="sqlSearch">
        <![CDATA[
SELECT
	p.id,
	c.`name` AS comm_name,
	po.`name` AS proposal_organ_name,
	o.`name` AS organ_name,
	c.project_type,
	c.project_nature,
	c.corp_type,
	c.area AS area,
	p.proposal_date,
	p.result,
	p.memo,
	c.contacts
FROM
	marketing_proposal AS p
	LEFT JOIN preComm AS c ON ( c.id = p.comm_id )
	LEFT JOIN preOrgan AS o ON ( o.id = c.organ_id )
	LEFT JOIN preOrgan AS po ON ( po.id = c.proposal_organ_id )
        ]]>
    </sql>
    <select id="search"
            parameterType="map"
            resultMap="MarketingProposalResultMap">
        <include refid="sqlSearch"/>
        <where>
            <if test="1 == 1">
                <![CDATA[
                  AND p.is_delete = 0
                ]]>
            </if>
            <if test="after != null">
                <![CDATA[
                  AND p.proposal_date >= #{after, jdbcType = DATE}
                ]]>
            </if>
            <if test="before != null">
                <![CDATA[
                  AND p.proposal_date <= #{before, jdbcType = DATE}
                ]]>
            </if>
            <if test="result != null and result != ''">
                <![CDATA[
                  AND p.result = #{result, jdbcType = VARCHAR}
                ]]>
            </if>
            <if test="search != null and search != ''">
                <![CDATA[
                AND (
                c.`name` LIKE CONCAT(CONCAT('%', #{search, jdbcType=VARCHAR}),'%')
                OR o.`name` LIKE CONCAT(CONCAT('%', #{search, jdbcType=VARCHAR}),'%')
                OR p.`result` LIKE CONCAT(CONCAT('%', #{search, jdbcType=VARCHAR}),'%')
                OR p.`memo` LIKE CONCAT(CONCAT('%', #{search, jdbcType=VARCHAR}),'%')
                )
                ]]>
            </if>
        </where>
        <![CDATA[
        ORDER BY p.proposal_date DESC
        ]]>
        <if test="limit > 0 and offset >= 0">
            <![CDATA[
            LIMIT #{offset, jdbcType = INTEGER}, #{limit, jdbcType = INTEGER}
            ]]>
        </if>
    </select>
    <select id="findBy"
            parameterType="INTEGER"
            resultMap="MarketingProposalResultMap">
        <include refid="sqlSearch"/>
        <where>
            <if test="1 == 1">
                <![CDATA[
                  AND p.is_delete = 0
                ]]>
            </if>
            <if test="id != null">
                <![CDATA[
                  AND p.id = #{id, jdbcType = INTEGER}
                ]]>
            </if>
        </where>
    </select>

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