<?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="com.huiju.estateagents.property.dao.TpBillOrderMapper">

    <select id="getBillOrderAll" resultType="com.huiju.estateagents.property.model.TpBillOrder">
        SELECT
        tbo.*,
        tbs.pay_remark as payRemark,
        tbs.pay_type as payType,
        tbs.create_time
        FROM
        tp_bill_order tbo LEFT JOIN tp_bill_statement tbs on tbo.tp_bill_invoice_id = tbs.bill_invoice_id
        LEFT JOIN tp_bill tb on tbo.tp_bill_id = tb.id
        <where>
            tbo.org_id = #{map.orgId}
            and tbo.community_id = #{map.communityId}
                <if test="map.orderBumber != null">
                    and tbo.order_bumber like CONCAT('%',#{map.orderBumber},'%')
                </if>
                <if test="map.billId != null">
                    and tbo.tp_bill_id like CONCAT('%',#{map.billId},'%')
                </if>
                <if test="map.billName != null and map.billName != ''">
                    and tb.bill_name like CONCAT('%',#{map.billName},'%')
                </if>
                <if test="map.billInvoiceId != null">
                    and tbo.tp_bill_invoice_id like CONCAT('%',#{map.billInvoiceId},'%')
                </if>
                <if test="map.payPhone != null and map.payPhone != ''">
                    and tbo.pay_phone like CONCAT('%', #{map.payPhone},'%')
                </if>
                <if test="map.orderStatus != null">
                    and tbo.order_status = #{map.orderStatus}
                </if>
                <if test="map.payType != null">
                    and tbs.pay_type = #{map.payType}
                </if>
                <if test="map.startCreateDate != null and map.startCreateDate !=''">
                    and tbs.create_time >= #{map.startCreateDate}
                </if>
                <if test="map.endCreateDate != null and map.endCreateDate !=''">
                    and tbs.create_time &lt;= #{map.endCreateDate}
                </if>
        </where>
        group by tbo.order_bumber
        order by
        <if test="map.sortKey != null and map.sortKey != ''">
            ${map.sortKey} ${map.sortOrder},
        </if>
        tbo.create_date DESC
    </select>
    <select id="getBillHouse" resultType="java.lang.Integer">
        SELECT
            count( * )
        FROM
            tp_bill_order t
        WHERE
            t.org_id = #{orgId}
            AND t.community_id = #{communityId}
            AND t.order_status = 1
        <if test="startDate != null and startDate != ''">
            AND DATE_FORMAT(t.create_date, '%Y-%m-%d') &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != ''">
            AND DATE_FORMAT(t.update_date, '%Y-%m-%d') &lt;= #{endDate}
        </if>
    </select>
    <select id="getBillMapList" resultType="java.util.Map">
        SELECT
            t.update_date AS createDate,
            s.nickname,
            '' AS content
        FROM
            tp_bill_order t
            INNER JOIN ta_person s ON t.create_user = s.person_id
        WHERE
            t.org_id = #{orgId}
            AND t.community_id = #{communityId}
            AND t.order_status = 1
        <if test="startDate != null and startDate != ''">
            AND DATE_FORMAT(t.create_date, '%Y-%m-%d') &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != ''">
            AND DATE_FORMAT(t.update_date, '%Y-%m-%d') &lt;= #{endDate}
        </if>
    </select>

</mapper>
