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

namespace Bailun.DC.MonthShippingIncoming
{
    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.Day == 1 && now.Hour == 1 && now.Minute == 45)  //每个月的1日1点45分启动
                {
                    Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    var start = DateTime.Parse(now.AddMonths(-1).ToShortDateString());

                    Init(start);

                    Console.WriteLine("任务运行完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        public void Init(DateTime day)
        {
            var list = new List<dc_month_shipping_incoming>();

            //获取订单数据
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var sql = $@"select sum(t2.amount_sales*t1.quantity_shipped) as amount_sales,if(t6.financecategoryname is null,'',t6.financecategoryname) as financecategoryname,t2.order_currency,t2.platform_type,t2.website from dc_base_oms_pick t1
                            join dc_base_oms_sku t2 on t1.bailun_order_id = t2.bailun_order_id  and t1.bailun_sku=t2.bailun_sku  and t2.bailun_order_status != 'Canceled' and t2.has_scalp = 0 and ((t2.platform_type!='FBA' and t2.bailun_order_status!='CantHandle') or t2.platform_type='FBA') and t2.has_scalp = 0 and t2.has_innersale = 0  and t2.has_delete=0 and t2.company_id=1
							left join dc_base_sku t5 on t2.bailun_sku=t5.bailun_sku
							left join dc_base_sku_finance_category t6 on t6.bailuncategoryid=t5.bailun_category_id
                            where t1.has_delete=0 and t1.shipping_status = 'TotalShipping' and t1.company_id=1 and t1.shipping_time>='{day.ToString("yyyy-MM-dd")}' and t1.shipping_time<'{day.AddMonths(1).ToString("yyyy-MM-dd")}'
                  group by t6.financecategoryname,t2.order_currency,t2.platform_type,t2.website";

                var obj = cn.Query<Model.mPlatformIncoming>(sql, null, null, true, 2 * 60).AsList();
                foreach(var item in obj)
                {
                    list.Add(new dc_month_shipping_incoming { 
                        amount = item.amount_sales,
                        createtime =DateTime.Now,
                        currency = item.order_currency,
                        financecategoryname = item.financecategoryname,
                        month = day.ToString("yyyy-MM"),
                        platform_type = item.platform_type,
                        website = item.website
                    });
                }
            }

            if(list.Count>0)
            {
                var sql = "insert dc_month_shipping_incoming (amount,createtime,currency,financecategoryname,month,platform_type,website) values ";

                foreach(var item in list)
                {
                    sql += $"({item.amount},'{item.createtime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.currency}','{item.financecategoryname}','{item.month}','{item.platform_type}','{item.website}'),";
                }

                sql = sql.Substring(0, sql.Length - 1);

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

                    cn.Execute("delete from dc_month_shipping_incoming where month='"+day.ToString("yyyy-MM")+ "' and platform_type!='FBA'");

                    cn.Execute(sql);
                }
            }
        }
    }
}
