<?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="shop.alien.mapper.LawFirmPaymentMapper">

    <!-- 律所子表关联律所表查询结果映射 -->
    <resultMap id="LawFirmPaymentVOResultMap" type="shop.alien.entity.store.vo.LawFirmPaymentVO">
        <!-- 律所子表字段 -->
        <id column="payment_id" property="id" />
        <result column="payment_firm_id" property="firmId" />
        <result column="payment_account" property="paymentAccount" />
        <result column="payment_address" property="address" />
        <result column="payment_delete_flag" property="deleteFlag" />
        <result column="payment_created_time" property="createdTime" />
        <result column="payment_created_user_id" property="createdUserId" />
        <result column="payment_updated_time" property="updatedTime" />
        <result column="payment_updated_user_id" property="updatedUserId" />
        <!-- 律所表字段 -->
        <result column="firm_name" property="firmName" />
        <result column="credit_code" property="creditCode" />
        <result column="short_name" property="shortName" />
        <result column="phone" property="phone" />
        <result column="email" property="email" />
        <result column="director_name" property="directorName" />
        <result column="director_phone" property="directorPhone" />
        <result column="status" property="status" />
        <result column="certification_status" property="certificationStatus" />
        <result column="platform_commission_ratio" property="platformCommissionRatio" />
    </resultMap>

    <!-- 分页查询律所子表关联律所表 -->
    <select id="selectPaymentPageWithFirm" resultMap="LawFirmPaymentVOResultMap">
        SELECT
            p.id AS payment_id,
            p.firm_id AS payment_firm_id,
            p.payment_account,
            p.address AS payment_address,
            p.delete_flag AS payment_delete_flag,
            p.created_time AS payment_created_time,
            p.created_user_id AS payment_created_user_id,
            p.updated_time AS payment_updated_time,
            p.updated_user_id AS payment_updated_user_id,
            f.firm_name,
            f.credit_code,
            f.short_name,
            f.phone,
            f.email,
            f.director_name,
            f.director_phone,
            f.status,
            f.platform_commission_ratio,
            f.certification_status
        FROM
            law_firm_payment p
        LEFT JOIN law_firm f ON f.id = p.firm_id AND f.delete_flag = 0
        WHERE
            p.delete_flag = 0
        <if test="firmId != null">AND p.firm_id = #{firmId}</if>
        <if test="paymentAccount != null and paymentAccount != ''">AND p.payment_account LIKE CONCAT('%', #{paymentAccount}, '%')</if>
        <if test="firmName != null and firmName != ''">AND f.firm_name LIKE CONCAT('%', #{firmName}, '%')</if>
        <if test="creditCode != null and creditCode != ''">AND f.credit_code = #{creditCode}</if>
        <if test="status != null">AND f.status = #{status}</if>
        <if test="certificationStatus != null">AND f.certification_status = #{certificationStatus}</if>
        <if test="directorName != null and directorName != ''">AND f.director_name LIKE CONCAT('%', #{directorName}, '%')</if>
        <if test="createdTimeStart != null and createdTimeStart != ''">AND p.created_time &gt;= #{createdTimeStart}</if>
        <if test="createdTimeEnd != null and createdTimeEnd != ''">AND p.created_time &lt;= #{createdTimeEnd}</if>
        ORDER BY f.id ASC, p.created_time DESC
    </select>

    <!-- 查询律所子表关联律所表总数 -->
    <select id="selectPaymentPageWithFirmCount" resultType="java.lang.Long">
        SELECT COUNT(*)
        FROM
            law_firm_payment p
        LEFT JOIN law_firm f ON f.id = p.firm_id AND f.delete_flag = 0
        WHERE
            p.delete_flag = 0
        <if test="firmId != null">AND p.firm_id = #{firmId}</if>
        <if test="paymentAccount != null and paymentAccount != ''">AND p.payment_account LIKE CONCAT('%', #{paymentAccount}, '%')</if>
        <if test="firmName != null and firmName != ''">AND f.firm_name LIKE CONCAT('%', #{firmName}, '%')</if>
        <if test="creditCode != null and creditCode != ''">AND f.credit_code = #{creditCode}</if>
        <if test="status != null">AND f.status = #{status}</if>
        <if test="certificationStatus != null">AND f.certification_status = #{certificationStatus}</if>
        <if test="directorName != null and directorName != ''">AND f.director_name LIKE CONCAT('%', #{directorName}, '%')</if>
        <if test="createdTimeStart != null and createdTimeStart != ''">AND p.created_time &gt;= #{createdTimeStart}</if>
        <if test="createdTimeEnd != null and createdTimeEnd != ''">AND p.created_time &lt;= #{createdTimeEnd}</if>
    </select>

</mapper>

