using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using Dapper;
using Bailun.DC.Common;
using System.Linq;
using Microsoft.Extensions.Hosting;
using System.Threading;
using System.Threading.Tasks;
using Bailun.DC.Models;

namespace Bailun.DC.DailyItemNoProfitRate
{
    public class Services : BackgroundService
    {
        private Timer _timer;

        protected override Task ExecuteAsync(CancellationToken stoppingToken)
        {
            _timer = new Timer(DoWork, null, TimeSpan.Zero, TimeSpan.FromMinutes(1));
            return Task.CompletedTask;
        }

        private void DoWork(object state)
        {
            var now = DateTime.Now;
            var day = DateTime.Parse(now.AddDays(-1).ToShortDateString());

            try
            {
                
                if ((now.Hour == 17 && now.Minute == 15))  //17:05启动
                {
                    Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    
                    Init(day.AddDays(-20),day);

                    Console.WriteLine("开始生成一天的itemno利润率" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    OneDayItemNoProfitRate(day.AddDays(-1), day);

                    Console.WriteLine("任务运行完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine("出现异常，10分钟后重跑"+DateTime.Now);
                Thread.Sleep(10 * 60);

                Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                Init(day.AddDays(-20), day);

                Console.WriteLine("开始生成一天的itemno利润率" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                OneDayItemNoProfitRate(day.AddDays(-1), day);

            }

            Dispose();
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">截止日期</param>
        public void Init(DateTime start,DateTime end)
        {
            Console.WriteLine("开始获取数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            var listOrders = new List<Models.dc_mid_itemno_profit_order>();
            var listItemAdGMV = new List<Models.dc_daily_itemno_ad_gmv>();
            var listAdEbay = new List<Models.dc_base_finance_ebay>();                   //ebay广告费
            var listAdAmazon = new List<Models.dc_base_finance_amazon_ad_product>();    //亚马逊广告费
            var listWithDraw = GetPlatformLossWithdraw().Where(a=>a.english_name.ToLower()=="ebay").FirstOrDefault();
            var list = new List<Models.dc_daily_item_profit_rate>();
            var listBaseOrder = new List<dc_mid_itemno_profit_order>();

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                listBaseOrder = cn.Query<dc_mid_itemno_profit_order>($@"select distinct t3.id as order_sku_id,t1.bailun_order_id,t1.origin_order_id,t1.item_id,t1.platform_sku,t1.platform_sku_quantity_ordered,t3.bailun_sku,t3.paid_time,(t3.amount_sales*t1.platform_sku_quantity_ordered) as amount_sales,(t3.cost_product*t1.platform_sku_quantity_ordered) cost_product,(t3.cost_platform_fee*t1.platform_sku_quantity_ordered) cost_platform_fee,(t3.cost_paypal_fee*t1.platform_sku_quantity_ordered) cost_paypal_fee,(t3.cost_fba_fee*t1.platform_sku_quantity_ordered) cost_fba_fee,(t3.cost_first*t1.platform_sku_quantity_ordered) cost_first,(t3.cost_logistics*t1.platform_sku_quantity_ordered) cost_logistics,(t3.cost_handle_platform*t1.platform_sku_quantity_ordered) cost_handle_platform,(t3.cost_handle_bailun*t1.platform_sku_quantity_ordered) cost_handle_bailun,t3.warehouse_code,t3.shipping_status,t3.seller_order_exchange_rate,t1.platform_type,t1.website,t1.bailun_account_id,t3.seller_other_exchange_rate from dc_base_oms_platform_sku t1
                    join dc_base_sku_mapping_online t2 on t1.item_id=t2.item_id and t2.item_sku=t1.platform_sku and t1.website=t2.site
                    join dc_base_oms_sku_30 t3 on t1.bailun_order_id=t3.bailun_order_id and t2.sku_code=t3.bailun_sku and t2.account_id=t3.bailun_account_id
                    where t1.platform_type in ('Ebay','FBA','Amazon') and t3.has_delete=0 and t3.company_id=1 and t1.paid_time>='{start.AddHours(-16).ToString("yyyy-MM-dd")}' and t1.paid_time<'{end.AddHours(16).AddDays(1).ToString("yyyy-MM-dd")}'", null, null, true, 6 * 60).ToList();
            }

            if(listBaseOrder.Count>0)
            {
                var sql = $@"insert dc_mid_itemno_profit_order (day,order_sku_id,bailun_order_id,origin_order_id,item_id,platform_sku,platform_sku_quantity_ordered,count,bailun_sku,paid_time,amount_sales,cost_product,cost_platform_fee,cost_paypal_fee,cost_fba_fee,cost_first,cost_logistics,cost_handle_platform,cost_handle_bailun,warehouse_code,shipping_status,seller_order_exchange_rate,platform_type,website,bailun_account_id,platform_paid_time,seller_other_exchange_rate) values 
                    ";



                //先保存订单明细
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    cn.Execute($"delete from dc_mid_itemno_profit_order where day='{end.ToString("yyyy-MM-dd")}'");

                    var str = "";
                    var index = 1;
                    foreach (var item in listBaseOrder)
                    {
                        item.count = 1;  //设置默认数量为 1

                        //转换为平台站点时间
                        var platform_paid_time = ToPlatformTime(item.website.ToUpper(),item.paid_time);

                        str += $"('{end.ToString("yyyy-MM-dd")}',{item.order_sku_id},'{item.bailun_order_id}','{item.origin_order_id}','{item.item_id}','{item.platform_sku}',{item.platform_sku_quantity_ordered},{item.count},'{item.bailun_sku}','{item.paid_time.ToString("yyyy-MM-dd HH:mm:ss")}',{item.amount_sales},{item.cost_product},{item.cost_platform_fee},{item.cost_paypal_fee},{item.cost_fba_fee},{item.cost_first},{item.cost_logistics},{item.cost_handle_platform},{item.cost_handle_bailun},'{item.warehouse_code}','{item.shipping_status}','{item.seller_order_exchange_rate}','{item.platform_type}','{item.website}','{item.bailun_account_id}','{platform_paid_time.ToString("yyyy-MM-dd HH:mm:ss")}',{item.seller_other_exchange_rate}),";
                        if (index % 1000 == 0 || index == list.Count)
                        {
                            //保存数据
                            if (!string.IsNullOrEmpty(str))
                            {
                                str = str.Substring(0, str.Length - 1);
                            }

                            cn.Execute(sql + str, null, null, 6 * 60);
                            str = "";

                            Console.WriteLine("保存基础订单数据，index:" + index + "；总记录数：" + listBaseOrder.Count + "；" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                        }
                        index++;
                    }

                    listBaseOrder = new List<dc_mid_itemno_profit_order>();

                    Console.WriteLine("完成保存基础订单数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    //更新提现损耗字段 只有ebay的
                    var withdrawpercent = 0.0000M;
                    if (listWithDraw != null && listWithDraw.Loss_withdrawal_percent.HasValue)
                    {
                        withdrawpercent = listWithDraw.Loss_withdrawal_percent.Value;
                    }
                    cn.Execute($@"update dc_mid_itemno_profit_order set losswithdraw=amount_sales*seller_order_exchange_rate*{withdrawpercent} where day='{end.ToString("yyyy-MM-dd")}' and platform_type='ebay'", null, null, 6 * 60);
                    Console.WriteLine("完成提现损耗字段更新" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    //更新是否预估尾程费
                    cn.Execute($@"update dc_mid_itemno_profit_order t1
                            join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                            set t1.is_forecast=1 
                            where (t1.shipping_status='UnShipping' or t2.area_id=18) and t1.day='{end.ToString("yyyy-MM-dd")}'", null, null, 6 * 60);
                    Console.WriteLine("完成尾程字段是否预估更新" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    //删除多余的订单数据，按平台时间过滤
                    cn.Execute($"delete from dc_mid_itemno_profit_order where day ='{end.ToString("yyyy-MM-dd")}' and (platform_paid_time<'{start.ToString("yyyy-MM-dd")}' or platform_paid_time>'{end.AddDays(1).ToString("yyyy-MM-dd")}')");

                }
                Console.WriteLine("完成订单基础数据保存 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }


            SaveProfitRate(start, end);
        }

        public void SaveProfitRate(DateTime start,DateTime end)
        {
            var listOrders = new List<Models.dc_mid_itemno_profit_order>();
            var listItemAdGMV = new List<Models.dc_daily_itemno_ad_gmv>();
            var listAdEbay = new List<Models.dc_base_finance_ebay>();                   //ebay广告费
            var listAdAmazon = new List<Models.dc_base_finance_amazon_ad_product>();    //亚马逊广告费
            var listWithDraw = GetPlatformLossWithdraw().Where(a => a.english_name.ToLower() == "ebay").FirstOrDefault();
            var list = new List<Models.dc_daily_item_profit_rate>();
            var listBaseOrder = new List<dc_mid_itemno_profit_order>();

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                //取出订单明细
                var sql = $"select * from dc_mid_itemno_profit_order where day>='{end.ToString("yyyy-MM-dd")}' and day<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";
                listOrders = cn.Query<Models.dc_mid_itemno_profit_order>(sql, null, null, true, 6 * 60).AsList();
                Console.WriteLine("完成取出订单数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                //取出item no的广告GMV
                listItemAdGMV = cn.Query<Models.dc_daily_itemno_ad_gmv>($"select * from dc_daily_itemno_ad_gmv  where sale_date>='{start.ToString("yyyy-MM-dd")}' and sale_date<'{end.AddDays(1).ToString("yyyy-MM-dd")}'").AsList();
                Console.WriteLine("完成取出itemno的广告GMV数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                //取出所有ebay的广告费
                listAdEbay = cn.Query<Models.dc_base_finance_ebay>($@"select * from dc_base_finance_ebay where gmt_date>='{start.ToString("yyyy-MM-dd")}' and gmt_date<'{end.AddDays(1).ToString("yyyy-MM-dd")}' and account_entry_type='FeeAd'").ToList();
                Console.WriteLine("完成取出ebay广告费数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                //取出所有亚马逊的广告费
                listAdAmazon = cn.Query<Models.dc_base_finance_amazon_ad_product>($@"select * from dc_base_finance_amazon_ad_product where report_date>='{start.ToString("yyyy-MM-dd")}' and report_date<'{end.AddDays(1).ToString("yyyy-MM-dd")}'").ToList();
                Console.WriteLine("完成取出亚马逊广告数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }

            var listItemId = listOrders.Select(a => a.item_id).ToList();
            listItemId.AddRange(listItemAdGMV.Select(a => a.item_id));
            listItemId.AddRange(listAdEbay.Select(a => a.item_id));
            listItemId.AddRange(listAdAmazon.Select(a => a.asin));
            listItemId = listItemId.Distinct().ToList();  //去重

            //以item维度计算出销售额，销售成本，广告GMV等数据
            foreach (var item in listItemId)
            {
                var m = new Models.dc_daily_item_profit_rate
                {
                    createtime = DateTime.Now,
                    day = end,
                    item_id = item,

                };

                var objOrders = listOrders.Where(a => a.item_id == item);

                decimal objadrate;  //广告订单GMV占比

                m.amount_sales = objOrders?.Sum(a => a.amount_sales * a.seller_order_exchange_rate) ?? 0;
                m.amount_sales_ad_gmv = listItemAdGMV.Where(a => a.item_id == item)?.Sum(a => a.sales_amount * a.sales_rate) ?? 0;
                m.cost_ad = listAdEbay.Where(a => a.item_id == item)?.Sum(a => a.exchange_rate * a.gross_amount) ?? 0 + listAdAmazon.Where(a => a.asin == item)?.Sum(a => a.cost * a.exchange_rate) ?? 0;
                m.cost_sales = objOrders?.Sum(a => a.cost_fba_fee * a.seller_order_exchange_rate + a.cost_first + a.cost_handle_bailun + a.cost_handle_platform + a.cost_logistics + a.cost_paypal_fee * a.seller_order_exchange_rate + (a.cost_platform_fee * (a.platform_type.ToLower()=="ebay"?a.seller_other_exchange_rate:a.seller_order_exchange_rate)) + a.cost_product) ?? 0;

                //广告订单GMV占比
                objadrate = m.amount_sales > 0 ? m.amount_sales_ad_gmv / m.amount_sales : 0;

                //全部订单利润率=（过去20天OMS订单销售额-过去20天广告费-过去20天全部订单销售成本）/过去20天OMS订单销售额
                m.rate_all = m.amount_sales > 0 ? (m.amount_sales - m.cost_ad - m.cost_sales) / m.amount_sales : 0;

                //过去20天自然订单GMV=总GMV-广告GMV
                //过去20天自然订单销售成本 = 过去20天OMS订单总销售成本（不计广告费）*（1 - 广告订单GMV占比）
                //自然订单GMV - 过去20天自然订单销售成 = 自然订单利润
                //自然订单利润 / 过去20天自然订单GMV = 自然订单利润率
                m.rate_order = (m.amount_sales<=m.amount_sales_ad_gmv?0:((m.amount_sales - m.amount_sales_ad_gmv) > 0 ? ((m.amount_sales - m.amount_sales_ad_gmv) - (m.cost_sales * (1 - objadrate))) / (m.amount_sales - m.amount_sales_ad_gmv) : 0));  //如果广告GMV大于订单GMV，则自然订单利润率为0

                //过去20天广告GMV/过去20天OMS订单销量额=广告订单GMV占比
                //过去20天OMS订单总销售成本（不计广告费）*广告订单GMV占比=过去20天广告订单总销售成本
                //广告GMV-过去20天广告订单总销售成本-广告费=广告订单利润
                //广告订单利润/广告GMV=广告订单利润率
                m.rate_order_ad = m.amount_sales_ad_gmv > 0 ? ((m.amount_sales_ad_gmv - (m.cost_sales * objadrate) - m.cost_ad) / m.amount_sales_ad_gmv) : 0;

                list.Add(m);
            }
            Console.WriteLine("完成数据计算，得出利润率" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            //合并保存item的销售额，销售成本，广告GMV，利润，利润率数据
            if (list.Count > 0)
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }
                    cn.Execute("delete from dc_daily_item_profit_rate where day='" + list.FirstOrDefault().day + "'");

                    var sql = $@"insert dc_daily_item_profit_rate (item_id,amount_sales,cost_ad,cost_sales,amount_sales_ad_gmv,rate_order_ad,rate_order,rate_all,day) values ";
                    var str = "";
                    var index = 1;
                    foreach (var item in list)
                    {
                        str += $"('{item.item_id}',{item.amount_sales},{item.cost_ad},{item.cost_sales},{item.amount_sales_ad_gmv},{item.rate_order_ad},{item.rate_order},{item.rate_all},'{item.day.ToString("yyyy-MM-dd")}'),";
                        if (index % 1000 == 0 || index == list.Count)
                        {
                            //保存数据
                            if (!string.IsNullOrEmpty(str))
                            {
                                str = str.Substring(0, str.Length - 1);
                            }

                            cn.Execute(sql + str, null, null, 6 * 60);
                            str = "";

                            Console.WriteLine("保存数据，index:" + index + "；总记录数：" + list.Count + "；" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                        }
                        index++;
                    }

                }
            }
        }

        /// <summary>
        /// 生成一天的itemno利润率
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        public void OneDayItemNoProfitRate(DateTime start,DateTime end)
        {
            Console.WriteLine("开始OneDay获取数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            var listOrders = new List<Models.dc_mid_itemno_profit_order>();
            var listItemAdGMV = new List<Models.dc_daily_itemno_ad_gmv>();
            var listAdEbay = new List<Models.dc_base_finance_ebay>();                   //ebay广告费
            var listAdAmazon = new List<Models.dc_base_finance_amazon_ad_product>();    //亚马逊广告费
            var listWithDraw = GetPlatformLossWithdraw().Where(a => a.english_name.ToLower() == "ebay").FirstOrDefault();
            var list = new List<dc_daily_item_oneday_profit_rate>();
            var listBaseOrder = new List<dc_mid_itemno_profit_order>();


            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                //取出订单明细
                listOrders = cn.Query<Models.dc_mid_itemno_profit_order>($"select * from dc_mid_itemno_profit_order where platform_paid_time>='{end.ToString("yyyy-MM-dd")}' and platform_paid_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'").AsList();
                Console.WriteLine("完成取出订单数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                //取出item no的广告GMV
                listItemAdGMV = cn.Query<Models.dc_daily_itemno_ad_gmv>($"select * from dc_daily_itemno_ad_gmv  where sale_date>='{start.ToString("yyyy-MM-dd")}' and sale_date<'{end.AddDays(1).ToString("yyyy-MM-dd")}'").AsList();
                Console.WriteLine("完成取出itemno的广告GMV数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                //取出所有ebay的广告费
                listAdEbay = cn.Query<Models.dc_base_finance_ebay>($@"select * from dc_base_finance_ebay where gmt_date>='{start.ToString("yyyy-MM-dd")}' and gmt_date<'{end.AddDays(1).ToString("yyyy-MM-dd")}' and account_entry_type='FeeAd'").ToList();
                Console.WriteLine("完成取出ebay广告费数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                //取出所有亚马逊的广告费
                listAdAmazon = cn.Query<Models.dc_base_finance_amazon_ad_product>($@"select * from dc_base_finance_amazon_ad_product where report_date>='{start.ToString("yyyy-MM-dd")}' and report_date<'{end.AddDays(1).ToString("yyyy-MM-dd")}'").ToList();
                Console.WriteLine("完成取出亚马逊广告数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }

            var listItemId = listOrders.Select(a => a.item_id).ToList();
            listItemId.AddRange(listItemAdGMV.Select(a => a.item_id));
            listItemId.AddRange(listAdEbay.Select(a => a.item_id));
            listItemId.AddRange(listAdAmazon.Select(a => a.asin));
            listItemId = listItemId.Distinct().ToList();  //去重

            //以item维度计算出销售额，销售成本，广告GMV等数据
            foreach (var item in listItemId)
            {
                var m = new dc_daily_item_oneday_profit_rate
                {
                    createtime = DateTime.Now,
                    day = end,
                    item_id = item,

                };

                var objOrders = listOrders.Where(a => a.item_id == item);

                decimal objadrate;  //广告订单GMV占比

                m.amount_sales = objOrders?.Sum(a => a.amount_sales * a.seller_order_exchange_rate) ?? 0;
                m.amount_sales_ad_gmv = listItemAdGMV.Where(a => a.item_id == item)?.Sum(a => a.sales_amount * a.sales_rate) ?? 0;
                m.cost_ad = listAdEbay.Where(a => a.item_id == item)?.Sum(a => a.exchange_rate * a.gross_amount) ?? 0 + listAdAmazon.Where(a => a.asin == item)?.Sum(a => a.cost * a.exchange_rate) ?? 0;
                m.cost_sales = objOrders?.Sum(a => a.cost_fba_fee * a.seller_order_exchange_rate + a.cost_first + a.cost_handle_bailun + a.cost_handle_platform + a.cost_logistics + a.cost_paypal_fee * a.seller_order_exchange_rate + (a.cost_platform_fee * (a.platform_type.ToLower() == "ebay" ? a.seller_other_exchange_rate : a.seller_order_exchange_rate)) + a.cost_product) ?? 0;

                //广告订单GMV占比
                objadrate = m.amount_sales > 0 ? m.amount_sales_ad_gmv / m.amount_sales : 0;

                //全部订单利润率=（过去20天OMS订单销售额-过去20天广告费-过去20天全部订单销售成本）/过去20天OMS订单销售额
                m.rate_all = m.amount_sales > 0 ? (m.amount_sales - m.cost_ad - m.cost_sales) / m.amount_sales : 0;

                //过去20天自然订单GMV=总GMV-广告GMV
                //过去20天自然订单销售成本 = 过去20天OMS订单总销售成本（不计广告费）*（1 - 广告订单GMV占比）
                //自然订单GMV - 过去20天自然订单销售成 = 自然订单利润
                //自然订单利润 / 过去20天自然订单GMV = 自然订单利润率
                m.rate_order = (m.amount_sales_ad_gmv>m.amount_sales?0:((m.amount_sales - m.amount_sales_ad_gmv) > 0 ? m.amount_sales - m.amount_sales_ad_gmv - (m.cost_sales * (1 - objadrate)) / (m.amount_sales - m.amount_sales_ad_gmv) : 0));

                //过去20天广告GMV/过去20天OMS订单销量额=广告订单GMV占比
                //过去20天OMS订单总销售成本（不计广告费）*广告订单GMV占比=过去20天广告订单总销售成本
                //广告GMV-过去20天广告订单总销售成本-广告费=广告订单利润
                //广告订单利润/广告GMV=广告订单利润率
                m.rate_order_ad = m.amount_sales_ad_gmv > 0 ? ((m.amount_sales_ad_gmv - (m.cost_sales * objadrate) - m.cost_ad) / m.amount_sales_ad_gmv) : 0;

                list.Add(m);
            }
            Console.WriteLine("完成数据计算，得出利润率" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            //合并保存item的销售额，销售成本，广告GMV，利润，利润率数据
            if (list.Count > 0)
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }
                    cn.Execute("delete from dc_daily_item_oneday_profit_rate where day='" + list.FirstOrDefault().day + "'");

                    var sql = $@"insert dc_daily_item_oneday_profit_rate (item_id,amount_sales,cost_ad,cost_sales,amount_sales_ad_gmv,rate_order_ad,rate_order,rate_all,day) values ";
                    var str = "";
                    var index = 1;
                    foreach (var item in list)
                    {
                        str += $"('{item.item_id}',{item.amount_sales},{item.cost_ad},{item.cost_sales},{item.amount_sales_ad_gmv},{item.rate_order_ad},{item.rate_order},{item.rate_all},'{item.day.ToString("yyyy-MM-dd")}'),";
                        if (index % 1000 == 0 || index == list.Count)
                        {
                            //保存数据
                            if (!string.IsNullOrEmpty(str))
                            {
                                str = str.Substring(0, str.Length - 1);
                            }

                            cn.Execute(sql + str, null, null, 6 * 60);
                            str = "";

                            Console.WriteLine("保存数据，index:" + index + "；总记录数：" + list.Count + "；" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                        }
                        index++;
                    }

                }
            }
        }

        /// <summary>
        /// 获取平台提现损耗的比例
        /// </summary>
        /// <returns></returns>
        private List<dc_platform_and_site> GetPlatformLossWithdraw()
        {
            using (var cn = new MySql.Data.MySqlClient.MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                return cn.Query<dc_platform_and_site>("select english_name,Loss_withdrawal_percent from dc_platform_and_site where type=1").AsList();
            }
        }

        /// <summary>
        /// 北京时间转平台时间
        /// </summary>
        /// <param name="website"></param>
        /// <param name="time"></param>
        /// <returns></returns>
        private DateTime ToPlatformTime(string website,DateTime time)
        {
            switch(website)
            {
                case "UK":  //英国
                    return time.AddHours(-7);

                case "CA":  //加拿大和美国取太平洋时间
                case "US":
                    return time.AddHours(-15);
                case "AU":  //澳大利亚
                    return time.AddHours(2);
                case "DE":  //德国
                case "ES":  //西班牙
                case "FR":  //法国
                case "NL":  //荷兰
                case "SE":  //瑞典
                case "IT":  //意大利
                    return time.AddHours(-6);
                case "JP":  //日本
                    return time.AddHours(1);
                case "ID":  //印度尼西亚
                case "MY":  //马来西亚
                case "PH":  //菲律宾
                case "SG":  //新加坡
                case "CN":  //中国
                    return time;
                case "TH":  //泰国
                    return time.AddHours(-1);

                default:
                    return time;
            }
        }
    }
}
