<?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.yunzhi.marketing.eContract.mapper.TaContractMapper">

    <select id="listContractForAdmin" resultType="com.yunzhi.marketing.eContract.entity.TaContract">
        SELECT
            *
        FROM
            ta_contract t
        where
            t.org_id = #{orgId}
            <if test="contractName != null and contractName != ''">
                AND t.contract_name LIKE CONCAT( '%', #{contractName}, '%' )
            </if>
            <if test="signatoryName != null and signatoryName != ''">
                AND t.signatory_name LIKE CONCAT( '%', #{signatoryName}, '%' )
            </if>
            <if test="customerName != null and customerName != ''">
                AND t.customer_name LIKE CONCAT( '%', #{customerName}, '%' )
            </if>
            <if test="archives != null">
                AND t.archives = #{archives}
            </if>
            <if test="archivesStartDate != null">
                AND t.archives_time &gt;= #{archivesStartDate}
            </if>
            <if test="archivesEndDate != null">
                AND t.archives_time &lt;= #{archivesEndDate}
            </if>
            <choose>
                <when test="signatoryStatus == 0">
                    AND t.signatory_time is NULL
                </when>
                <when test="signatoryStatus == 1">
                    AND t.signatory_time is not NULL
                </when>
                <otherwise></otherwise>
            </choose>
            <choose>
                <when test="customerStatus == 0">
                    AND t.customer_time is NULL
                </when>
                <when test="customerStatus == 1">
                    AND t.customer_time is not NULL
                </when>
                <otherwise></otherwise>
            </choose>
        ORDER BY
        t.signatory_time DESC
    </select>

    <select id="listContractForChannel" resultType="com.yunzhi.marketing.eContract.entity.TaContract">
        SELECT
            t.*,
            t2.company_name,
            t2.company_code,
            t3.`name` orgName,
            t4.`name` miniAppName,
            t6.channel_proxy_name
        FROM
            ta_contract t
            LEFT JOIN ta_company t2 ON t.company_id = t2.company_id
            LEFT JOIN ta_org t3 ON t.org_id = t3.org_id
            LEFT JOIN ta_miniapp t4 ON t.org_id = t4.org_id
            LEFT JOIN ta_channel_app_relation t5 ON t.org_id = t5.org_id
            LEFT JOIN ta_channel_proxy t6 ON t5.channel_id = t6.channel_id
        <where>
            t.target_type != 'auth'
            <if test=" bo.companyName != null and bo.companyName != ''">
                AND t2.company_name like CONCAT('%',#{bo.companyName} , '%')
            </if>
            <if test=" bo.settlement != null and bo.settlement ">
                AND t.settlement = #{bo.settlement}
            </if>
            <if test=" bo.settlement != null and bo.settlement == false ">
                AND (t.settlement = #{bo.settlement} or t.settlement is null)
            </if>
            <if test=" bo.companyCode != null and bo.companyCode != ''">
                AND t2.company_code like CONCAT('%',#{bo.companyCode} , '%')
            </if>
            <if test=" bo.orgName != null and bo.orgName != ''">
                AND t3.`name` like CONCAT('%',#{bo.orgName} , '%')
            </if>
            <if test=" bo.miniAppName != null and bo.miniAppName != ''">
                AND t4.`name` like CONCAT('%',#{bo.miniAppName} , '%')
            </if>
            <if test=" bo.contractId != null">
                AND t.contract_id like CONCAT('%',#{bo.contractId} , '%')
            </if>
            <if test=" bo.contractName != null and bo.contractName != ''">
                AND t.contract_name like CONCAT('%',#{bo.contractName} , '%')
            </if>
            <if test=" bo.channelProxyName != null and bo.channelProxyName != ''">
                AND t6.channel_proxy_name like CONCAT('%',#{bo.channelProxyName} , '%')
            </if>
            <if test=" bo.signatoryStartTime != null">
                AND t.signatory_time &gt;= #{bo.signatoryStartTime}
            </if>
            <if test=" bo.signatoryEndTime != null ">
                AND t.signatory_time &lt;= #{bo.signatoryEndTime}
            </if>
            <if test=" bo.settlementStartTime != null">
                AND t.settlement_time &gt;= #{bo.settlementStartTime}
            </if>
            <if test=" bo.settlementEndTime != null">
                AND t.settlement_time &lt;= #{bo.settlementEndTime}
            </if>
        </where>
        ORDER BY
            t.signatory_time DESC
    </select>

    <select id="selectContractInfoByRaiseId" resultType="com.yunzhi.marketing.eContract.entity.TaContractBusiness">
        SELECT
            t.*,
            c.contract_template_address,
            s.signature_id,
            d.fadada_code
        FROM
            ta_contract_business t
        LEFT JOIN ta_contract_template c ON t.contract_template_id = c.contract_template_id
        LEFT JOIN ta_company_seal s ON t.seal_id = s.seal_id
        left JOIN ta_company d on t.company_id = d.company_id
        WHERE
        t.target_type = "raise"
        AND t.target_id = #{raiseId}
        AND t.`status` = 1
    </select>
</mapper>
