<select id="getContractList" resultType="com.lqb.web.entity.Contract" parameterType="com.lqb.web.entity.Contract">
        select a.id , a.contract_num,date_format(a.start_date,'%Y-%m-%d') AS  startDate, date_format(a.end_date,'%Y-%m-%d') AS  endDate,
        date_format(a.rent_start_date,'%Y-%m-%d') AS  rentStartDate,a.state,a.type,a.reson,
        a.room_type as roomType,a.price as price ,a.remarks as remarks,a.create_date as createDate,a.create_by as createBy,a.pay_type1 as payType1,
        a.pay_type2 as payType2,b.mansion_id as mansionId,b.`name` as ` rentRoom.name`,b.`area` as `rentRoom.area`,
        c.`name` as `tenantry.name`,c.`contact` as `tenantry.contact`,c.`mobile` as `tenantry.mobile`,c.`company_type` as `tenantry.companyType`,c.`employee_count` as `tenantry.employeeCount`,
        e.`name` as `letter.name`,e.`company_address` as `letter.companyAddress`,a.deposit,u.name AS createUser
        FROM contract a
        left JOIN rent_room b ON a.`id`=b.`contract_id`
        left JOIN tenantry c ON a.`id`=c.`contract_id`
        left JOIN letter e ON a.`id`=e.`contract_id`
        left join user u on a.create_by = u.id
        <where>
            (c.state!=-1 OR  c.state is NULL)

            <if test="name != null">
                AND a.name like #{name}
            </if>
            <if test="createBy != null">
                and a.create_by = #{createBy}
            </if>
            <if test="mansionId!= null">
                AND b.mansion_id = #{mansionId}
            </if>
            <if test="letter != null and letter.name != null  and letter.name != ''">
                and e.name like #{letter.name}
            </if>

            <if test="tenantry != null and tenantry.name != null  and tenantry.name != ''">
                and c.name like #{tenantry.name}
            </if>

            <if test="area != '' and area != null ">
              and  area = #{area}
            </if>
            <if test="minArea != '' and minArea != null and maxArea != '' and maxArea != null">
                <![CDATA[
                and  b.area >= #{minArea}
                AND  b.area <= #{maxArea}
               ]]>
            </if>
            <if test="startDate != '' and startDate != null and endDate != '' and endDate != null">
                <![CDATA[
              and (DATE_FORMAT(a.start_date, '%Y-%m-%d') BETWEEN  DATE_FORMAT(#{startDate}, '%Y-%m-%d') AND DATE_FORMAT(#{endDate}, '%Y-%m-%d')
              OR DATE_FORMAT(a.end_date, '%Y-%m-%d') BETWEEN  DATE_FORMAT(#{startDate}, '%Y-%m-%d') AND DATE_FORMAT(#{endDate}, '%Y-%m-%d')
              OR DATE_FORMAT(#{startDate}, '%Y-%m-%d') BETWEEN  DATE_FORMAT(a.start_date, '%Y-%m-%d') AND DATE_FORMAT(a.end_date, '%Y-%m-%d')
              OR DATE_FORMAT(#{endDate}, '%Y-%m-%d') BETWEEN  DATE_FORMAT(a.start_date, '%Y-%m-%d') AND DATE_FORMAT(a.end_date, '%Y-%m-%d'))
                 ]]>
            </if>

          <if test="startDate == null and endDate != null">
            <![CDATA[
              AND DATE_FORMAT(end_date, '%Y-%m-%d') <=  DATE_FORMAT(#{endDate}, '%Y-%m-%d')
             ]]>
          </if>
            <if test="chooseStartDate != '' and chooseStartDate != null and chooseEndDate != '' and chooseEndDate != null">
                <![CDATA[
              and (DATE_FORMAT(a.start_date, '%Y-%m-%d') BETWEEN  DATE_FORMAT(#{chooseStartDate}, '%Y-%m-%d') AND DATE_FORMAT(#{chooseEndDate}, '%Y-%m-%d')
              OR DATE_FORMAT(a.end_date, '%Y-%m-%d') BETWEEN  DATE_FORMAT(#{chooseStartDate}, '%Y-%m-%d') AND DATE_FORMAT(#{chooseEndDate}, '%Y-%m-%d')
              OR DATE_FORMAT(#{chooseStartDate}, '%Y-%m-%d') BETWEEN  DATE_FORMAT(a.start_date, '%Y-%m-%d') AND DATE_FORMAT(a.end_date, '%Y-%m-%d')
              OR DATE_FORMAT(#{chooseEndDate}, '%Y-%m-%d') BETWEEN  DATE_FORMAT(a.start_date, '%Y-%m-%d') AND DATE_FORMAT(a.end_date, '%Y-%m-%d'))
                 ]]>
            </if>

            <if test="payType1 != '' and payType1 != null ">
              AND pay_type1 = #{payType1}
            </if>

            <if test="payType2 != '' and payType2 != null ">
                AND pay_type2 = #{payType2}
            </if>

            <if test="roomType != '' and roomType != null ">
              AND room_type = #{roomType}
            </if>

            <if test="mansionId != '' and mansionId != null ">
                AND b.mansion_id = #{mansionId}
            </if>

            <if test="states != null">
                AND a.state in <foreach  item="item" collection="states" index="index"  open="(" separator="," close=")">#{item}</foreach>
            </if>

        </where>
        ORDER BY a.`create_date` DESC
        Limit #{startPos},#{pageSize}
    </select>