<?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.bailuntec.ebay_uk_report.mapper.DcEbayReportUkMapper">
  <insert id="insertReport">
        insert into dc_ebay_report_uk(day,uk_seller_account,uk_total_orders,surface_mail_orders,standard_mail_orders,private_line_orders,private_line_percent,
        oversea_surface_mail_orders,oversea_standard_mail_orders,oversea_orders,oversea_percent,margin_oversea_orders,all_margin_orders)
        select #{lastDay},
        a.sellAccount,
        ifnull(b.surfaceMailOrders,0)+ifnull(c.standardMailOrders,0)+ifnull(d.overseaSurfaceMailOrders,0)+ifnull(e.overseaStandardMailOrders,0),
        ifnull(b.surfaceMailOrders,0),
        ifnull(c.standardMailOrders,0),
        ifnull(b.surfaceMailOrders,0)+ifnull(c.standardMailOrders,0),
        case when ifnull(b.surfaceMailOrders,0)+ifnull(c.standardMailOrders,0)+ifnull(d.overseaSurfaceMailOrders,0)+ifnull(e.overseaStandardMailOrders,0) = 0 then 0
        else (ifnull(b.surfaceMailOrders,0)+ifnull(c.standardMailOrders,0))/(ifnull(b.surfaceMailOrders,0)+ifnull(c.standardMailOrders,0)+ifnull(d.overseaSurfaceMailOrders,0)+ifnull(e.overseaStandardMailOrders,0)) end,
        ifnull(d.overseaSurfaceMailOrders,0),
        ifnull(e.overseaStandardMailOrders,0),
        ifnull(d.overseaSurfaceMailOrders,0)+ifnull(e.overseaStandardMailOrders,0),
        case when ifnull(b.surfaceMailOrders,0)+ifnull(c.standardMailOrders,0)+ifnull(d.overseaSurfaceMailOrders,0)+ifnull(e.overseaStandardMailOrders,0) = 0 then 0
        else (ifnull(d.overseaSurfaceMailOrders,0)+ifnull(e.overseaStandardMailOrders,0))/(ifnull(b.surfaceMailOrders,0)+ifnull(c.standardMailOrders,0)+ifnull(d.overseaSurfaceMailOrders,0)+ifnull(e.overseaStandardMailOrders,0)) end,
        case when round((ifnull(b.surfaceMailOrders,0)+ifnull(c.standardMailOrders,0)+ifnull(d.overseaSurfaceMailOrders,0)+ifnull(e.overseaStandardMailOrders,0))/2,0)-ifnull(d.overseaSurfaceMailOrders,0)-ifnull(e.overseaStandardMailOrders,0) <![CDATA[<=]]> 0 then 0
        else (round((ifnull(b.surfaceMailOrders,0)+ifnull(c.standardMailOrders,0)+ifnull(d.overseaSurfaceMailOrders,0)+ifnull(e.overseaStandardMailOrders,0))/2,0)-ifnull(d.overseaSurfaceMailOrders,0)-ifnull(e.overseaStandardMailOrders,0)) * 1.5 end,
        case when round((ifnull(b.surfaceMailOrders,0)+ifnull(c.standardMailOrders,0)+ifnull(d.overseaSurfaceMailOrders,0)+ifnull(e.overseaStandardMailOrders,0))/2,0)-ifnull(d.overseaSurfaceMailOrders,0)-ifnull(e.overseaStandardMailOrders,0) <![CDATA[<=]]> 0 then ifnull(a.allMarginOrders,0)
        else (round((ifnull(b.surfaceMailOrders,0)+ifnull(c.standardMailOrders,0)+ifnull(d.overseaSurfaceMailOrders,0)+ifnull(e.overseaStandardMailOrders,0))/2,0)-ifnull(d.overseaSurfaceMailOrders,0)-ifnull(e.overseaStandardMailOrders,0)) * 1.5 + ifnull(a.allMarginOrders,0) end
        from (select g.config_value sellAccount,ifnull(k.all_margin_orders,0) allMarginOrders from dc_ebay_report_uk_config g
        left join dc_ebay_report_uk k on g.config_value = k.uk_seller_account and k.day=#{twoDaysAgo} where g.type = 1) a
        left join (
        select count(1) surfaceMailOrders,t1.seller_account from
        (select distinct o.seller_account,o.origin_order_id,o.logistics_warehouse_code,o.logistics_method_code,p.logistics_order_id
        from dc_base_oms_order o
        inner join dc_base_oms_pick p on o.bailun_order_id=p.bailun_order_id
        inner join dc_ebay_report_uk_config c1 on c1.type=1 and o.seller_account=c1.config_value
        inner join dc_ebay_report_uk_config c2 on c2.type=2 and p.logistics_order_id = c2.config_value
        where o.platform_type='Ebay' and o.website='UK' and o.bailun_payment_status!='Canceled'
        and o.paid_time<![CDATA[>=]]>#{lastDay} and o.paid_time<![CDATA[<]]>#{today}) t1
        group by t1.seller_account
        ) b on a.sellAccount = b.seller_account
        left join (
        select count(1) standardMailOrders,t2.seller_account from
        (select distinct o.seller_account,o.origin_order_id,o.logistics_warehouse_code,o.logistics_method_code,p.logistics_order_id
        from dc_base_oms_order o
        inner join dc_base_oms_pick p on o.bailun_order_id=p.bailun_order_id
        inner join dc_ebay_report_uk_config c1 on c1.type=1 and o.seller_account=c1.config_value
        inner join dc_ebay_report_uk_config c2 on c2.type=3 and p.logistics_order_id = c2.config_value
        where o.platform_type='Ebay' and o.website='UK' and o.bailun_payment_status!='Canceled'
        and o.paid_time<![CDATA[>=]]>#{lastDay} and o.paid_time<![CDATA[<]]>#{today}) t2
        group by t2.seller_account
        ) c on a.sellAccount = c.seller_account
        left join (
        select count(1) overseaSurfaceMailOrders,t3.seller_account from
        (select distinct o.seller_account,o.origin_order_id,o.logistics_warehouse_code,o.logistics_method_code,p.logistics_order_id
        from dc_base_oms_order o
        inner join dc_base_oms_pick p on o.bailun_order_id=p.bailun_order_id
        inner join dc_ebay_report_uk_config c1 on c1.type=1 and o.seller_account=c1.config_value
        inner join dc_ebay_report_uk_config c2 on c2.type=4 and p.logistics_order_id = c2.config_value
        where o.platform_type='Ebay' and o.website='UK' and o.bailun_payment_status!='Canceled'
        and o.paid_time<![CDATA[>=]]>#{lastDay} and o.paid_time<![CDATA[<]]>#{today}) t3
        group by t3.seller_account
        ) d on a.sellAccount = d.seller_account
        left join (
        select count(1) overseaStandardMailOrders,t4.seller_account from
        (select distinct o.seller_account,o.origin_order_id,o.logistics_warehouse_code,o.logistics_method_code,p.logistics_order_id
        from dc_base_oms_order o
        inner join dc_base_oms_pick p on o.bailun_order_id=p.bailun_order_id
        inner join dc_ebay_report_uk_config c1 on c1.type=1 and o.seller_account=c1.config_value
        inner join dc_ebay_report_uk_config c2 on c2.type=5 and p.logistics_order_id = c2.config_value
        where o.platform_type='Ebay' and o.website='UK' and o.bailun_payment_status!='Canceled'
        and o.paid_time<![CDATA[>=]]>#{lastDay} and o.paid_time<![CDATA[<]]>#{today}) t4
        group by t4.seller_account
        ) e on a.sellAccount = e.seller_account
  </insert>
</mapper>