<?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.form.mapper.employee.EmployeeAnalysisMapper">

    <select id="getMail" resultType="java.math.BigDecimal">
        SELECT sum(a.count)
        FROM (SELECT count(1) AS `count`
              FROM enterprise_message t1
                       JOIN enterprise_mailbox t2
                            ON t1.mailbox_id = t2.id
                  ${ew.customSqlSegment}
              UNION
              SELECT
                  count ( 1 ) AS `count`
              FROM
                  personal_message t1
                  JOIN personal_mailbox t2
              ON t1.mailbox_id = t2.id
                  ${ew.customSqlSegment}) AS a
    </select>
    <select id="getQuotationStatistics" resultType="java.util.Map">
        SELECT if(count(1) is null, 0, count(1))                                 AS `count`,
               if(sum(sq.amount * sq.rate) is null, 0, sum(sq.amount * sq.rate)) AS sumAmount
        FROM sale_quotation sq
            ${ew.customSqlSegment}
    </select>
    <select id="getContractStatistics" resultType="java.util.Map">
        SELECT if(count(1) is null, 0, count(1))                             AS `count`,
               if(sum(c.amount * c.rate) is null, 0, sum(c.amount * c.rate)) AS sumAmount
        FROM contract c
            ${ew.customSqlSegment}
    </select>
    <select id="getClaimStatistics" resultType="java.util.Map">
        SELECT if(count(1) is null, 0, count(1))                                 AS `count`,
               if(sum(c.amount * arw.rate) is null, 0, sum(c.amount * arw.rate)) AS sumAmount
        FROM claim c
                 left join claim_contract cc on cc.claim_id = c.id
                 LEFT JOIN contract c1 on cc.contract_id = c1.id
                 left join bytesailing_account.account_running_water arw on c.business_id = arw.id
                 left join bytesailing_customer.customer c2 on c1.buy_corporation_id = c2.id
            ${ew.customSqlSegment}
    </select>
    <select id="getPurchaseAmount" resultType="java.math.BigDecimal">
        SELECT SUM(p.amount)
        FROM purchase p
            ${ew.customSqlSegment}
    </select>
</mapper>
