using Dapper;
using Microsoft.Extensions.Hosting;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Bailun.DC.SaveWHSalesService
{
    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)
        {
            try
            {
                var now = DateTime.Now;

                if (now.Hour == 1 && now.Minute == 1)
                {
                    Save();
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        public bool Save()
        {
            Console.WriteLine("进去方法，" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

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

                    cn.Execute("delete from dc_mid_skuwarehouse_sales where day='" + DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + "'",null,null,30*60);

                    var page = 1;
                    var pagesize = 5000;
                    var listcount = cn.QueryFirstOrDefault<int>("select count(id) from dc_base_stock");
                    var resultcount = 0;

                    while (page == 1 || (resultcount == pagesize && resultcount != 0))
                    {
                        var strlimit = (((page - 1) * pagesize) + "," + pagesize);
                        var sql = $@"select t1.bailun_sku,t1.sku_title_cn 'bailun_sku_name',t1.warehouse_code,t3.warehouse_name,t3.hq_type 'warehouse_type',t1.usable_stock 'stock_count',t1.usable_stock*t1.unit_price 'stock_amount',t1.create_time 'sku_developtime',
                                    t4.amount_sales 'aliexpress_saleamount',t4.sales_count 'aliexpress_salecount',t5.amount_sales 'amazon_saleamount',t5.sales_count 'amazon_salecount',t6.amount_sales 'fba_saleamount',t6.sales_count 'fba_salecount',t7.amount_sales 'ebay_saleamount',t7.sales_count 'ebay_salecount',t8.amount_sales 'amazon2B_saleamount',t8.sales_count 'amazon2B_salecount',t9.amount_sales 'jollychic_saleamount',t9.sales_count 'jollychic_salecount',t10.amount_sales 'joom_saleamount',t10.sales_count 'joom_salecount',t11.amount_sales 'marketing_saleamount',t11.sales_count 'marketing_salecount',t12.amount_sales 'mymall_saleamount',t12.sales_count 'mymall_salecount',t13.amount_sales 'opensky_saleamount',t13.sales_count 'opensky_salecount',t14.amount_sales 'priceminister_saleamount',t14.sales_count 'priceminister_salecount',t15.amount_sales 'qoo10_saleamount',t15.sales_count 'qoo10_salecount',t16.amount_sales 'shopee_saleamount',t16.sales_count 'shopee_salecount',t17.amount_sales 'shopify_saleamount',t17.sales_count 'shopify_salecount',t18.amount_sales 'walmart_saleamount',t18.sales_count 'walmart_salecount',t19.amount_sales 'wish_saleamount',t19.sales_count 'wish_salecount'
                                    from (select t01.bailun_sku,t01.warehouse_code,t01.usable_stock,t02.sku_title_cn,t02.unit_price,t02.product_code,t02.bailun_category_id,t02.bailun_category_name,t02.create_time from dc_base_stock t01 
                                    join dc_base_sku t02 on t01.bailun_sku=t02.bailun_sku and t02.company_id=1
                                    limit {strlimit}) t1 
                                    join dc_base_warehouse t3 on t1.warehouse_code=t3.warehouse_code
                                    left join view_sku_sales t4 on t4.platform_type='Aliexpress' and t1.bailun_sku=t4.bailun_sku and t1.warehouse_code=t4.warehouse_code
                                    left join view_sku_sales t5 on t5.platform_type='Amazon' and t1.bailun_sku=t5.bailun_sku and t1.warehouse_code=t5.warehouse_code
                                    left join view_sku_sales t6 on t6.platform_type='FBA' and t1.bailun_sku=t6.bailun_sku and t1.warehouse_code=t6.warehouse_code
                                    left join view_sku_sales t7 on t7.platform_type='Ebay' and t1.bailun_sku=t7.bailun_sku and t1.warehouse_code=t7.warehouse_code 
                                    left join view_sku_sales t8 on t8.platform_type='Amazon2B' and t1.bailun_sku=t8.bailun_sku and t1.warehouse_code=t8.warehouse_code 
                                    left join view_sku_sales t9 on t9.platform_type='Jollychic' and t1.bailun_sku=t9.bailun_sku and t1.warehouse_code=t9.warehouse_code
                                    left join view_sku_sales t10 on t10.platform_type='Joom' and t1.bailun_sku=t10.bailun_sku and t1.warehouse_code=t10.warehouse_code
                                    left join view_sku_sales t11 on t11.platform_type='MARKETING' and t1.bailun_sku=t11.bailun_sku and t1.warehouse_code=t11.warehouse_code
                                    left join view_sku_sales t12 on t12.platform_type='MyMall' and t1.bailun_sku=t12.bailun_sku and t1.warehouse_code=t12.warehouse_code
                                    left join view_sku_sales t13 on t13.platform_type='OpenSky' and t1.bailun_sku=t13.bailun_sku and t1.warehouse_code=t13.warehouse_code
                                    left join view_sku_sales t14 on t14.platform_type='PriceMinister' and t1.bailun_sku=t14.bailun_sku and t1.warehouse_code=t14.warehouse_code
                                    left join view_sku_sales t15 on t15.platform_type='Qoo10' and t1.bailun_sku=t15.bailun_sku and t1.warehouse_code=t15.warehouse_code
                                    left join view_sku_sales t16 on t16.platform_type='Shopee' and t1.bailun_sku=t16.bailun_sku and t1.warehouse_code=t16.warehouse_code
                                    left join view_sku_sales t17 on t17.platform_type='shopify' and t1.bailun_sku=t17.bailun_sku and t1.warehouse_code=t17.warehouse_code
                                    left join view_sku_sales t18 on t18.platform_type='Walmart' and t1.bailun_sku=t18.bailun_sku and t1.warehouse_code=t18.warehouse_code
                                    left join view_sku_sales t19 on t19.platform_type='Wish' and t1.bailun_sku=t19.bailun_sku and t1.warehouse_code=t19.warehouse_code";
                        

                        var liststock = cn.Query<Models.Warehouse.dc_mid_skuwarehouse_sales>(sql, null, null, true, 30 * 60);

                        resultcount = liststock.AsList().Count;

                        var str = @"insert dc_mid_skuwarehouse_sales (bailun_sku,bailun_sku_name,warehouse_code,warehouse_name,warehouse_type,stock_count,stock_amount,sku_developtime,aliexpress_salecount,aliexpress_saleamount,amazon_salecount,amazon_saleamount,fba_salecount,fba_saleamount,ebay_salecount,ebay_saleamount,amazon2B_salecount,amazon2B_saleamount,jollychic_salecount,jollychic_saleamount,joom_salecount,joom_saleamount,marketing_salecount,marketing_saleamount,mymall_salecount,mymall_saleamount,opensky_salecount,opensky_saleamount,priceminister_salecount,priceminister_saleamount,qoo10_salecount,qoo10_saleamount,shopee_salecount,shopee_saleamount,shopify_salecount,shopify_saleamount,walmart_salecount,walmart_saleamount,wish_salecount,wish_saleamount,day,createtime,salecount) values ";
                        foreach (var item in liststock)
                        {
                            str += $@"('{item.bailun_sku}','{item.bailun_sku_name.Replace("'", "")}','{item.warehouse_code}','{item.warehouse_name}','{item.warehouse_type}',{item.stock_count},{(item.stock_amount ?? 0)},'{item.sku_developtime.ToString("yyyy-MM-dd HH:mm:ss")}',{item.aliexpress_salecount??0},{item.aliexpress_saleamount ?? 0},{(item.amazon_salecount ?? 0)},{item.amazon_saleamount ?? 0},{item.fba_salecount??0},{item.fba_saleamount ?? 0},{item.ebay_salecount ?? 0},{item.ebay_saleamount ?? 0},{item.amazon2B_salecount ?? 0},{item.amazon2B_saleamount ?? 0},{item.jollychic_salecount ?? 0},{item.jollychic_saleamount ?? 0},{item.joom_salecount ?? 0},{item.joom_saleamount ?? 0},{item.marketing_salecount ?? 0},{item.marketing_saleamount ?? 0},{item.mymall_salecount ?? 0},{item.mymall_saleamount ?? 0},{item.opensky_salecount ?? 0},{item.opensky_saleamount ?? 0},{item.priceminister_salecount ?? 0},{item.priceminister_saleamount ?? 0},{item.qoo10_salecount ?? 0},{item.qoo10_saleamount ?? 0},{item.shopee_salecount ?? 0},{item.shopee_saleamount ?? 0},{item.shopify_salecount ?? 0},{item.shopify_saleamount ?? 0},{item.walmart_salecount ?? 0},{item.walmart_saleamount ?? 0},{item.wish_salecount ?? 0},{item.wish_saleamount ?? 0},'{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',{(item.aliexpress_salecount ?? 0) + (item.amazon_salecount ?? 0) + (item.fba_salecount ?? 0) + (item.ebay_salecount ?? 0) + (item.amazon2B_salecount ?? 0) + (item.jollychic_salecount ?? 0) + (item.joom_salecount ?? 0) + (item.marketing_salecount ?? 0) + (item.mymall_salecount ?? 0) + (item.opensky_salecount ?? 0) + (item.priceminister_salecount ?? 0) + (item.qoo10_salecount ?? 0) + (item.shopee_salecount ?? 0) + (item.shopify_salecount ?? 0) + (item.walmart_salecount ?? 0) + (item.wish_salecount ?? 0)}),";
                        }
                        if (resultcount > 0)
                        {
                            str = str.Substring(0, str.Length - 1);

                            cn.Execute(str, null, null, 5 * 60);
                            Console.WriteLine("当前页：" + page + ",每页记录：" + pagesize + "；" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        }

                        page++;
                    }

                    Console.WriteLine("运行成功，时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("出现异常：" + ex.Message);
                return false;
            }
        }

    }
}
