<?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.fjhx.sale.mapper.sample.SampleMapper">
    <select id="getPage" resultType="com.fjhx.sale.entity.sample.vo.SampleVo">
        SELECT *
        FROM
            (SELECT t1.*,
                    IFNULL(t1.amount, 0) * IFNULL(t1.rate, 0) AS amountCNY,
                    CASE
                        WHEN ( IFNULL( t1.amount, 0 ) * IFNULL( t1.rate, 0 ) - t1.sumClaimMoney ) &lt;=  0 THEN
                            20
                        WHEN t1.sumClaimMoney = 0 THEN
                            0
                        ELSE 10
                        END                                   AS refundStatusNew
             FROM (SELECT t1.*,
                          (SELECT IFNULL(SUM(IFNULL(cc.money, 0) * IFNULL(arw.rate, 1)), 0)
                           FROM claim_contract cc
                                    left join claim c on cc.claim_id = c.id
                                    LEFT JOIN bytesailing_account.account_running_water arw on c.business_id = arw.id
                           WHERE cc.contract_id = t1.id) AS sumClaimMoney
                   FROM sample t1) t1) t1
            ${ew.customSqlSegment}
    </select>

    <select id="sampleHandoverList" resultType="com.fjhx.sale.entity.contract.vo.ContractProductVo">
        SELECT
            t1.quantity,
            t1.create_time AS ct,
            t1.id,
            t1.sample_id sampleId,
            t1.product_id,
            t2.`code` AS `contractCode`,
            t2.user_name AS userName,
            t2.versions AS contractVersion,
            t2.buy_corporation_id buyCorporationId,
            <!-- 需要采购的合同数量-->
            t1.expend_quantity AS expendQuantity,
            <!-- 交接单时间-->
            t3.sampleTime,
            <!-- 合同审批通过后的时间（每次合同审批后都会修改时间 所以取update_time） -->
            t2.update_time updateTime,
            ( SELECT create_time FROM claim_contract WHERE contract_id = t2.id ORDER BY create_time DESC LIMIT 1 ) AS claimTime,
            t2.create_user
        FROM
            sample_product t1
            LEFT JOIN sample t2 ON t1.sample_id = t2.id
            <!-- 列表中只显示存在交接单的数据 获取到交接单时间最单的一条-->
            left join(SELECT business_id,create_time sampleTime from bytesailing_base.file_info GROUP BY business_id HAVING MAX(create_time)) t3 on
            t2.id = t3.business_id
        ${ew.customSqlSegment}
    </select>

</mapper>
