<?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.resident.ResidentContractMapper">
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.resident.ResidentContract">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="organ_id" jdbcType="VARCHAR" property="organId"/>
        <result column="organ_name" jdbcType="VARCHAR" property="organName"/>
        <result column="comm_id" jdbcType="VARCHAR" property="commId"/>
        <result column="comm_name" jdbcType="VARCHAR" property="commName"/>
        <result column="nc_system_number" jdbcType="VARCHAR" property="ncSystemNumber"/>
        <result column="sign_time" jdbcType="TIMESTAMP" property="signTime"/>
        <result column="contract_code" jdbcType="VARCHAR" property="contractCode"/>
        <result column="contract_type" jdbcType="VARCHAR" property="contractType"/>
        <result column="party_b" jdbcType="VARCHAR" property="partyB"/>
        <result column="taxpayer_number" jdbcType="VARCHAR" property="taxpayerNumber"/>
        <result column="address" jdbcType="VARCHAR" property="address"/>
        <result column="phone" jdbcType="VARCHAR" property="phone"/>
        <result column="bank" jdbcType="VARCHAR" property="bank"/>
        <result column="account" jdbcType="VARCHAR" property="account"/>
        <result column="start_date" jdbcType="DATE" property="startDate"/>
        <result column="end_date" jdbcType="DATE" property="endDate"/>
        <result column="fixed_amount" jdbcType="DECIMAL" property="fixedAmount"/>
        <result column="float_amount" jdbcType="VARCHAR" property="floatAmount"/>
        <result column="payment_method" jdbcType="VARCHAR" property="paymentMethod"/>
        <result column="status" jdbcType="VARCHAR" property="status"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <result column="gmt_create" jdbcType="TIMESTAMP" property="gmtCreate"/>
        <result column="created_by" jdbcType="VARCHAR" property="createdBy"/>
        <result column="gmt_modified" jdbcType="TIMESTAMP" property="gmtModified"/>
        <result column="modified_by" jdbcType="VARCHAR" property="modifiedBy"/>
        <result column="contract_type_name" jdbcType="VARCHAR" property="contractTypeName"/>
        <result column="contract_parent_code" jdbcType="VARCHAR" property="contractParentCode"/>
        <result column="payment_method_name" jdbcType="VARCHAR" property="paymentMethodName"/>
        <result column="bip_approval_number" jdbcType="VARCHAR" property="bipApprovalNumber"/>
        <result column="is_final_signed" jdbcType="BIT" property="isFinalSigned"/>
        <result column="payments" jdbcType="INTEGER" property="payments"/>
        <result column="paid_payments" jdbcType="INTEGER" property="paidPayments"/>
    </resultMap>
    <resultMap id="PartyBResultMap" type="cn.com.countrygarden.bi.main.model.resident.ResidentContractParty">
        <result column="party_b" jdbcType="VARCHAR" property="partyB"/>
        <result column="taxpayer_number" jdbcType="VARCHAR" property="taxpayerNumber"/>
        <result column="address" jdbcType="VARCHAR" property="address"/>
        <result column="phone" jdbcType="VARCHAR" property="phone"/>
        <result column="bank" jdbcType="VARCHAR" property="bank"/>
        <result column="account" jdbcType="VARCHAR" property="account"/>
    </resultMap>

    <sql id="selectAll">
    <![CDATA[
      SELECT c.id,
        c.organ_id,
        c.organ_name,
        c.comm_id,
        c.comm_name,
        c.nc_system_number,
        c.sign_time,
        c.contract_code,
        c.contract_type,
        c.party_b,
        c.taxpayer_number,
        c.address,
        c.phone,
        c.bank,
        c.account,
        c.start_date,
        c.end_date,
        c.fixed_amount,
        c.float_amount,
        c.payment_method,
        c.status,
        c.remark,
        c.gmt_create,
        c.created_by,
        c.gmt_modified,
        c.modified_by,
        c.bip_approval_number,
        c.is_final_signed,
        ct.name AS contract_type_name,
        ct.parent_code AS contract_parent_code,
        pm.name AS payment_method_name,
        0 AS payments,
        0 AS paidPayments
        FROM resident_contract AS c
        INNER JOIN resident_contract_type AS ct ON c.contract_type = ct.code AND ct.is_delete=0
        INNER JOIN resident_payment_method AS pm ON c.payment_method = pm.code AND pm.is_delete=0
        ]]>
  </sql>

    <select id="findBy" resultMap="BaseResultMap">
        <include refid="selectAll"></include>
        <where>
            <if test="organId!=null and organId!=''">
                <![CDATA[
          AND c.organ_id = #{organId}
        ]]>
            </if>
            <if test="commId!=null and commId!=''">
                <![CDATA[
          AND c.comm_id = #{commId}
        ]]>
            </if>
            <if test="startTime!=null and endTime!=null">
                <![CDATA[
          AND ( (c.start_date >= #{startTime} AND c.start_date<=#{endTime})
            OR (c.end_date >=#{startTime} AND c.end_date<=#{endTime})
            OR (c.start_date<=#{startTime} AND c.end_date>=#{endTime})
          )
        ]]>
            </if>
            <if test="contractCode!=null and contractCode!=''">
                <![CDATA[
          AND c.contract_code = #{contractCode}
        ]]>
            </if>

            <if test="contractStatus!=null and contractStatus=='NORMAL'">
                <![CDATA[
          AND (c.status = 'NORMAL' OR c.status = 'EXPIRED_APPLY' OR c.status = 'TERMINATED_APPLY')
        ]]>
            </if>
            <if test="contractStatus!=null and contractStatus!='' and contractStatus!='NORMAL'">
                <![CDATA[
          AND c.status = #{contractStatus}
        ]]>
            </if>

            <if test="organCodes!=null">
                <![CDATA[
            AND c.organ_id IN
        ]]>
                <foreach item="item" index="index" collection="organCodes" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="commIds!=null">
                <![CDATA[
            AND c.comm_id IN
        ]]>
                <foreach item="item" index="index" collection="commIds" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

        </where>
        <![CDATA[
        ORDER BY c.gmt_create DESC
    ]]>
    </select>

    <select id="exportPartyB" resultMap="PartyBResultMap">
    <![CDATA[

SELECT
	c.party_b,
	c.taxpayer_number,
	c.address,
	c.phone,
	c.bank,
	c.account
FROM
	resident_contract AS c
GROUP BY
	c.party_b,
	c.taxpayer_number,
	c.address,
	c.phone,
	c.bank,
	c.account

        ]]>
  </select>
</mapper>