<?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.ContractMapper">
    <select id="getPage" resultType="com.fjhx.sale.entity.contract.vo.ContractVo">
        SELECT
            *
        FROM
        (
            SELECT
                t1.*,
                CASE
                WHEN ( t1.amount - t1.sumClaimMoney ) &lt;= 0 THEN
                20
                WHEN t1.sumClaimMoney = 0 THEN
                0 ELSE 10
            END AS refundStatusNew
        FROM
        (
            SELECT
            t1.*,
            ( SELECT IFNULL( SUM( money ), 0 ) FROM claim_contract WHERE contract_id = t1.id ) AS sumClaimMoney
        FROM
            contract t1
            ) t1
        ) t1
        ${ew.customSqlSegment}
    </select>

    <select id="getProfitClearingPage" resultType="com.fjhx.sale.entity.contract.vo.ContractBudgetVo">
        SELECT
            t1.id AS contractId,
            t1.*
        FROM
            contract t1
            ${ew.customSqlSegment}
    </select>

    <select id="getProfitBudgetPage" resultType="com.fjhx.sale.entity.contract.vo.ContractBudgetVo">
        SELECT
            (select GROUP_CONCAT(id) from contract_product WHERE contract_id = t1.id) AS contractProductIds,
            t1.id AS contractId,
            t1.`code`,
            t1.buy_corporation_id ,
            t1.user_name,
            t1.amount,
            t1.currency,
            t1.currency_rate_json,
            t2.*
        FROM
            contract t1
        LEFT JOIN contract_budget t2 ON t1.id = t2.contract_id
        ${ew.customSqlSegment}
    </select>

    <select id="getNoPackContractByCustomerId" resultType="com.fjhx.sale.entity.contract.vo.ContractVo">
        SELECT
            *
        FROM
            (
                SELECT
                    t1.*,
                    t2.quantity AS cpQuantity,
                    ( SELECT IFNULL( SUM( quantity ), 0 ) FROM pack_detail_product WHERE 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
        AND t1.`status` &gt;=30
        AND t1.`status` &lt;99
        GROUP BY t1.id
    </select>

    <select id="getSalesTotal" resultType="com.fjhx.sale.entity.contract.vo.ContractVo">
        SELECT
            SUM(amount) amount,
            currency,
            count(*) contractCount
        FROM
            contract
        WHERE
            buy_corporation_id = #{id} and status = 30 and del_flag  = 0
        GROUP BY
            buy_corporation_id
    </select>

    <select id="getAmount" resultType="com.fjhx.sale.entity.contract.vo.ContractVo">
        SELECT
            SUM(amount) amount,
            DATE_FORMAT(create_time,'%Y-%m') time
        FROM
            contract
        WHERE
              buy_corporation_id = #{id}
                and (DATE_FORMAT(create_time,'%Y-%m') >= #{beginTime} and #{endTime} >= DATE_FORMAT(create_time,'%Y-%m'))
        GROUP BY
            TIME
    </select>

    <select id="contractStatistics" resultType="com.fjhx.sale.entity.sale.vo.SaleQuotationVo">
        SELECT
            COUNT(*) count,
            ifnull(c.`status`,-1) status,
            ifnull(DATE_FORMAT(sq.create_time,'%Y-%m'),-1) time
        FROM
            bytesailing_customer.customer c
              left  JOIN  contract sq ON sq.buy_corporation_id = c.id
            ${ew.customSqlSegment}
    </select>
    <select id="countrySalesStatistics" resultType="com.fjhx.sale.entity.contract.dto.ContractDto">
        SELECT
            sum(cc.amount) amount,
            COUNT(cc.buy_country_id) count,
            ai.name countryName

        FROM
            contract cc
                LEFT JOIN bytesailing_base.customize_area ai ON cc.buy_country_id = ai.id
            ${ew.customSqlSegment}
    </select>

    <select id="customSalesStatistics" resultType="com.fjhx.sale.entity.contract.dto.ContractDto">
        SELECT
            sum(cc.amount) amount,
            bc.name corporationName
        FROM
            contract cc
                LEFT JOIN bytesailing_customer.customer bc ON cc.buy_corporation_id = bc.id
            ${ew.customSqlSegment}
    </select>

    <select id="salesmanSalesStatistics" resultType="com.fjhx.sale.entity.contract.dto.ContractDto">
        SELECT
            SUM(cc.amount) amount,
            su.nick_name userName
        FROM
            contract cc
                LEFT JOIN bytesailing_base.sys_user su ON cc.create_user = su.user_id
            ${ew.customSqlSegment}
    </select>

    <select id="productSalesStatistics" resultType="com.fjhx.sale.entity.contract.dto.ContractDto">
        SELECT
            SUM(cp.amount) amount,
            pi.name productName
        FROM
            contract_product cp
                LEFT JOIN bytesailing_item.product_info pi ON cp.product_id = pi.id
            ${ew.customSqlSegment}
    </select>
    <select id="salesVolumeStatistics" resultType="com.fjhx.sale.entity.contract.dto.ContractDto">

        SELECT
            SUM(cp.quantity) quantity,
            pi.name productName
        FROM
            contract_product cp
                LEFT JOIN bytesailing_item.product_info pi ON cp.product_id = pi.id
            ${ew.customSqlSegment}
    </select>


</mapper>
