<?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.ProposalMapper">
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.marketing.Proposal">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="comm_id" jdbcType="INTEGER" property="commId"/>
        <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"/>
    </resultMap>



    <resultMap id="ProposalMonthDataResultMap" type="cn.com.countrygarden.bi.main.model.marketing.ProposalMonthData">
        <result column="x" jdbcType="VARCHAR" property="x"/>
        <result column="passed" jdbcType="INTEGER" property="passed"/>
        <result column="candidate" jdbcType="INTEGER" property="candidate"/>
        <result column="suspend" jdbcType="INTEGER" property="suspend"/>
        <result column="total" jdbcType="INTEGER" property="total"/>
    </resultMap>

    <select id="selectByMonth" resultMap="BaseResultMap">
        <![CDATA[
      SELECT *
      FROM marketing_proposal
      WHERE proposal_date>=#{startDate} and proposal_date<=#{endDate}
      order by comm_id, proposal_date desc
    ]]>
    </select>

    <select id="countByTimeAndStatus" resultType="java.lang.Integer">
        <![CDATA[

      SELECT *
      FROM marketing_proposal
      WHERE proposal_date>=#{startDate} and proposal_date<=#{endDate}
      order by comm_id, proposal_date desc

        ]]>
    </select>

    <select id="countCommByTime" resultType="java.lang.Integer">
    <![CDATA[

      SELECT COUNT(DISTINCT comm_id)
      FROM marketing_proposal
      WHERE proposal_date>=#{startDate} and proposal_date<=#{endDate}

        ]]>
  </select>

    <select id="tendency"
            parameterType="map"
            resultMap="ProposalMonthDataResultMap">
        <![CDATA[
SELECT
    DATE_FORMAT(t.proposal_date, '%c') AS x,
    SUM(IF(t.result = 'passed', 1, 0)) AS passed,
    SUM(IF(t.result = 'candidate', 1, 0)) AS candidate,
    SUM(IF(t.result = 'suspend', 1, 0)) AS suspend,
    COUNT(1) AS total
FROM
	marketing_proposal AS t
	LEFT JOIN comm AS c ON (c.id = t.comm_id AND c.is_delete = 0)
        ]]>
        <where>
            <if test="1 == 1">
                <![CDATA[
                  AND t.is_delete = 0
                ]]>
            </if>
            <if test="afterDate != null">
                <![CDATA[
                  AND t.proposal_date >= #{afterDate}
                ]]>
            </if>
        </where>

    </select>
</mapper>