<?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.contract.ContractProductMapper">

    <select id="getPage" resultType="com.fjhx.sale.entity.contract.vo.ContractProductVo">
        SELECT
        *
        FROM
        (
        SELECT
        t1.id,
        t2.create_time AS contractTime,
        t1.contract_id,
        t1.product_id,
        t2.`code` AS contractCode,
        t2.user_name AS userName,
        t2.create_user AS salesmanId,
        t2.version AS contractVersion,
        t1.expend_quantity AS expendQuantity,
        ( SELECT create_time FROM claim_contract WHERE contract_id = t2.id ORDER BY create_time DESC LIMIT 1 ) AS
        claimTime,
        t2.contract_template_id AS contractTemplateId,
        t1.quantity,
        (
        SELECT
        IF
        (
        sum( pd.count ) IS NULL,
        0,
        sum( pd.count ))
        FROM
        bytesailing_purchase.purchase_detail pd
        LEFT JOIN bytesailing_purchase.purchase p ON pd.purchase_id = p.id
        WHERE
        p.purchase_status = 10
        AND pd.data_resource_id = t1.id
        ) AS startPurchaseCount,
        (
        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 = t2.id
        ) / t2.amount * 100 AS ad,
        t2.advance_ratio AS advanceRatio
        FROM
        contract_product t1
        LEFT JOIN contract t2
        LEFT JOIN claim_contract co ON co.contract_id = t2.id ON t1.contract_id = t2.id
        ${ew.customSqlSegment}
        ) t1
        <where>
            <if test="isArrival neq null and isArrival==1 ">
                t1.ad &lt; t1.advanceRatio
            </if>
            <if test="isArrival neq null and isArrival==2 ">
                t1.ad > t1.advanceRatio
            </if>
        </where>
    </select>

    <select id="getEHSDPage" resultType="com.fjhx.sale.entity.contract.vo.ContractProductVo">
        SELECT *
        FROM (SELECT IFNULL(t3.`status`, 0) AS                                                                   qualityStatus,
                     t1.create_time         AS                                                                   ct,
                     "外销合同"             AS                                                                   orderType,
                     t1.id,
                     t1.product_model       AS                                                                   productModel,
                     t1.product_name        AS                                                                   productName,
                     t1.contract_id,
                     t1.product_id,
                     t2.`code`              AS                                                                   `contractCode`,
                     t2.user_name           AS                                                                   userName,
                     t2.version             AS                                                                   contractVersion,
                     t1.expend_quantity     AS                                                                   expendQuantity,
                     t2.sell_corporation_id,
                     t2.buy_corporation_id,
                     (SELECT create_time
                      FROM claim_contract
                      WHERE contract_id = t2.id
                      ORDER BY create_time DESC                                                                  LIMIT 1) AS claimTime FROM
                    contract_product t1
                LEFT JOIN contract t2
        ON t1.contract_id = t2.id
            LEFT JOIN ehsd_quality t3 ON t1.id = t3.business_id
        WHERE
            t2.`status` &gt;= 30
          AND t2.`status`
         &lt; 88
          AND t1.expend_quantity
            > 0
        UNION ALL
        SELECT IFNULL(t3.`status`, 0) AS                                                                   qualityStatus,
               t1.create_time         AS                                                                   ct,
               "样品单"               AS                                                                   orderType,
               t1.id,
               t1.product_model       AS                                                                   productModel,
               t1.product_name        AS                                                                   productName,
               t1.sample_id,
               t1.product_id,
               t2.`code`              AS                                                                   `contractCode`,
               t2.user_name           AS                                                                   userName,
               t2.versions            AS                                                                   contractVersion,
               t1.expend_quantity     AS                                                                   expendQuantity,
               t2.sell_corporation_id,
               t2.buy_corporation_id,
               (SELECT create_time
                FROM claim_contract
                WHERE contract_id = t2.id
                ORDER BY create_time DESC                                                                  LIMIT 1 ) AS claimTime
        FROM
            sample_product t1
            LEFT JOIN sample t2
        ON t1.sample_id = t2.id
            LEFT JOIN ehsd_quality t3 ON t1.id = t3.business_id
        WHERE
            t2.`status` &gt;= 30
          AND t2.`status`
         &lt; 88
          AND t1.expend_quantity
            > 0
            ) t1 ${ew.customSqlSegment}
    </select>

    <select id="getEHSDQualityPage" resultType="com.fjhx.sale.entity.contract.vo.ContractProductVo">
        SELECT *
        FROM (SELECT t1.create_time     AS ct,
                     "外销合同"         AS orderType,
                     t1.id,
                     t1.product_model   AS productModel,
                     t1.product_name    AS productName,
                     t1.contract_id,
                     t1.product_id,
                     t2.`code`          AS `contractCode`,
                     t2.user_name       AS userName,
                     t2.version         AS contractVersion,
                     t1.expend_quantity AS expendQuantity,
                     t3.`status`        AS qualityStatus,
                     t3.create_time     AS qualityTime,
                     t3.user_name       AS qualityUserName,
                     t2.buy_corporation_id,
                     t3.summary         AS summary
              FROM contract_product t1
                       LEFT JOIN contract t2 ON t1.contract_id = t2.id
                       LEFT JOIN ehsd_quality t3 ON t1.id = t3.business_id
              WHERE t2.`status` &gt;= 30
                AND t2.`status` &lt; 88
              UNION ALL
              SELECT t1.create_time     AS ct,
                     "样品单"           AS orderType,
                     t1.id,
                     t1.product_model   AS productModel,
                     t1.product_name    AS productName,
                     t1.sample_id,
                     t1.product_id,
                     t2.`code`          AS `contractCode`,
                     t2.user_name       AS userName,
                     t2.versions        AS contractVersion,
                     t1.expend_quantity AS expendQuantity,
                     t3.`status`        AS qualityStatus,
                     t3.create_time     AS qualityTime,
                     t3.user_name       AS qualityUserName,
                     t2.buy_corporation_id
                  t3.summary AS summary
              FROM sample_product t1
                       LEFT JOIN sample t2 ON t1.sample_id = t2.id
                       LEFT JOIN ehsd_quality t3 ON t1.id = t3.business_id
              WHERE t2.`status` &gt;= 30
                AND t2.`status` &lt; 88) t1
            ${ew.customSqlSegment}
    </select>

    <select id="getListByIds" resultType="com.fjhx.sale.entity.contract.vo.ContractProductVo">
        SELECT t1.id,
               t1.contract_id,
               t1.product_id,
               t2.`code`    AS    contractCode,
               t2.user_name AS    userName,
               t2.version   AS    contractVersion,
               t1.expend_quantity expendQuantity,
               t1.quantity
        FROM contract_product t1
                 LEFT JOIN contract t2 ON t1.contract_id = t2.id
            ${ew.customSqlSegment}
    </select>

    <select id="getNoPackContractProductById" resultType="com.fjhx.sale.entity.contract.vo.ContractProductVo">
        SELECT
        *
        FROM
        (
        SELECT
        t2.*,
        t1.id AS contractId,
        t1.`code` AS contractCode,
        t2.quantity AS cpQuantity,
        t2.id AS contractProductId,
        ( SELECT IFNULL( SUM( pd.pack_quantity * pdd.quantity ), 0 ) FROM pack_detail_product pdd LEFT JOIN pack_detail
        pd ON pdd.pack_detail_id = pd.id WHERE pdd.contract_product_id = t2.id ) AS sumPackQuantity
        FROM
        contract t1
        LEFT JOIN contract_product t2 ON t1.id = t2.contract_id
        <where>
            <if test="customerId neq null and customerId neq '' ">
                buy_corporation_id = #{customerId}
            </if>
        </where>
        ) t1
        WHERE t1.sumPackQuantity &lt; t1.cpQuantity
        <if test="contractIds neq null and contractIds.size() > 0">
            <foreach collection="contractIds" item="contractId" open="AND contractId IN (" separator="," close=")">
                #{contractId}
            </foreach>
        </if>
    </select>

    <select id="contractHandoverPage" resultType="com.fjhx.sale.entity.contract.vo.ContractProductVo">
        SELECT
        t1.create_time AS ct,
        t1.id,
        t1.contract_id,
        t1.product_id,
        t2.`code` AS `contractCode`,
        t2.user_name AS userName,
        t2.version 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
        FROM
        contract_product t1
        LEFT JOIN contract t2 ON t1.contract_id = t2.id
        <!-- 列表中只显示存在交接单的数据 获取到交接单时间最单的一条-->
        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>
