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

namespace Bailun.DC.DailyPlatformReceivable
{
    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 == 21)  //凌晨1：21分启动
                {
                    Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    var start = DateTime.Parse(now.AddDays(-1).ToShortDateString());

                    Init(start);

                    Console.WriteLine("任务运行完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                else if (now.Hour == 5 && now.Minute == 11)
                {
                    var start = now.AddDays(-30);
                    while (start.AddDays(1) < DateTime.Now)
                    {
                        Console.WriteLine(start);
                        Init(start);
                        start = start.AddDays(1);
                    }
                }


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

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

                //Ebay 预收账款=期初预收余额+本期收款（订单付款金额）-发货金额-退款（未发货的）

                //期初预收余额
                var sql = $"select * from dc_daily_receivable where day='{day.AddDays(-1).ToString("yyyy-MM-dd")}' and platform='Ebay'";
                var objBefore = cn.QueryFirstOrDefault<dc_daily_receivable>(sql);
                //本期收款
                sql = $"select sum(amount_sales*seller_order_exchange_rate) amount from dc_base_oms_order where platform_type='Ebay' and paid_time>='{day.ToString("yyyy-MM-dd")}' and paid_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' and company_id=1 and bailun_order_status!='Canceled' and has_scalp=0 and has_innersale=0";
                var EbaySales = cn.QueryFirstOrDefault<decimal?>(sql)??0;
                //发货金额
                sql = $@"select sum(t1.amount_sales*t2.quantity_shipped*t1.seller_order_exchange_rate) amount 
                        from dc_base_oms_pick t2
                        join dc_base_oms_sku t1 on t1.bailun_order_id=t2.bailun_order_id and t1.bailun_sku=t2.bailun_sku and t1.has_scalp=0 and t1.has_delete=0 and t1.has_innersale=0 and t1.bailun_order_status!='Canceled' and t1.has_innersale=0 and t1.company_id=1 and t1.platform_type='Ebay'
                        where t2.has_delete=0 and t2.shipping_status='TotalShipping' and t2.company_id=1 and t2.shipping_time>='{day.ToString("yyyy-MM-dd")}' and t2.shipping_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' ";
                var EbayShipping = cn.QueryFirstOrDefault<decimal?>(sql)??0;
                //退款(未发货的)
                sql = $"select sum(amount_refund_rmb) amount from dc_base_crm_refund where platform_type = 'Ebay' and company_id = 1 and is_deleted = 0 and is_freeze = 0 and shipping_status = 'UnShipping' and refund_time>= '{day.ToString("yyyy-MM-dd")}' and refund_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}'";
                var EbayRefund = cn.QueryFirstOrDefault<decimal?>(sql)??0;

                var m_ebay = new dc_daily_receivable {
                    day = day,
                    amount_end = (objBefore != null ? objBefore.amount_end : 0) + EbaySales - EbayShipping - EbayRefund,
                    amount_start = (objBefore != null ? objBefore.amount_end : 0),
                    amount_sale_pay = EbaySales,
                    amount_shipping = EbayShipping,
                    amount_refund = EbayRefund,

                    amount_incoming = 0,
                    amount_other = 0,
                    amount_platformfee = 0,
                    amount_sale_shipping = 0,
                    createtime = DateTime.Now,

                    lastupdatetime = DateTime.Now,
                    lastupdateuserid = 0,
                    lastupdateusername = "system",
                    platform = "Ebay",
                };

                //非Ebay   应收账款=期初应收余额+本期销售收入-平台扣费-放款金额-退款金额+其他
                sql = $@"select * from (
                        select t1.platform_type,sum(t1.amount_sales*t1.seller_order_exchange_rate*t2.quantity_shipped) amount,sum(t1.cost_platform_fee*t1.seller_order_exchange_rate*t2.quantity_shipped) cost_platform_fee,0 as cost_fba_fee 
                        from dc_base_oms_pick t2
                        join dc_base_oms_sku t1 on t1.bailun_order_id=t2.bailun_order_id and t1.bailun_sku=t2.bailun_sku and t1.has_scalp=0 and t1.has_delete=0 and t1.has_innersale=0 and t1.bailun_order_status!='Canceled' and t1.has_innersale=0 and t1.company_id=1 and t1.platform_type!='Ebay' and t1.platform_type!='FBA'
                        where t2.has_delete=0 and t2.shipping_status='TotalShipping' and t2.company_id=1 and t2.shipping_time>='{day.ToString("yyyy-MM-dd")}' and t2.shipping_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' group by platform_type
                        union all
                        select platform_type,sum(amount_sales*seller_order_exchange_rate) amount,sum((cost_platform_fee+cost_fba_fee)*seller_order_exchange_rate) cost_platform_fee,sum(cost_fba_fee*seller_order_exchange_rate) cost_fba_fee from dc_base_oms_order where platform_type='FBA' and create_time>='{day.ToString("yyyy-MM-dd")}' and create_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' and company_id=1 and bailun_order_status!='Canceled' and has_scalp=0 and has_innersale=0
                        ) tb";
                var listAmount = cn.Query<platformamount>(sql);

                var listPlatform = cn.Query<string>("select platform_type from dc_base_oms_order group by platform_type").Where(a=>a.ToLower()!= "amazon2b" && a.ToLower()!= "fba" && a.ToLower()!="ebay").ToList();

                //退款
                sql = $"select sum(amount_refund_rmb) as amount,platform_type from dc_base_crm_refund where platform_type!='Ebay' and company_id=1 and is_deleted=0 and is_freeze=0 and shipping_status='TotalShipping' and refund_time>='{day.ToString("yyyy-MM-dd")}' and refund_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' GROUP BY platform_type;";
                var listRefund = cn.Query<platformamount>(sql);

                //提现金额
                sql = $@"select sum(t1.other_to_cny_money) amount,t2.english_name as platform_type from dc_base_finance_cashierdetail t1 
                        join dc_base_finance_salesplatform t2 on t1.salesl_platform=t2.platform_id and t2.platform_id!=100
                        where t1.status=2 and daozhang_time>='{day.ToString("yyyy-MM-dd")}' and daozhang_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' group by salesl_platform";
                var listIncome = cn.Query<platformamount>(sql);

                var listM = new List<dc_daily_receivable>() { m_ebay };
                foreach (var item in listPlatform)
                {
                    //期初
                    sql = $"select * from dc_daily_receivable where day='{day.AddDays(-1).ToString("yyyy-MM-dd")}' and platform='{item}'";
                    objBefore = cn.QueryFirstOrDefault<dc_daily_receivable>(sql);

                    var obj = listAmount.Where(a => a.platform_type == item).FirstOrDefault();

                    if (item.ToLower() == "amazon")
                    {
                        var objAmazon = listAmount.Where(a => a.platform_type.ToLower() == "fba" || a.platform_type.ToLower() == "amazon");
                        if (objAmazon.Count() > 0)
                        {
                            obj = new platformamount {
                                amount = objAmazon.Sum(a=>a.amount),
                                cost_platform_fee = objAmazon.Sum(a=>a.cost_platform_fee),
                                platform_type = item,
                            };
                        }
                    }

                    var m = new dc_daily_receivable {
                        platform = item,
                        amount_start = objBefore!=null? objBefore.amount_end:0,
                        amount_other = 0,
                        amount_platformfee = obj!=null?obj.cost_platform_fee:0,
                        amount_sale_shipping = obj!=null?obj.amount:0,

                        amount_sale_pay = 0,
                        amount_shipping = 0,

                        amount_incoming = 0,
                        amount_refund = 0,
                        amount_end = 0,

                        createtime = DateTime.Now,
                        day = day,
                        lastupdatetime = DateTime.Now,
                        lastupdateuserid = 0,
                        lastupdateusername = "system",
                    };

                    //平台扣费
                    if (item.ToLower() == "amazon")
                    {
                        //广告费
                        sql = $"select sum(exchange_rate*cost) amount from dc_base_finance_amazon_ad_product t1 where t1.company_id=1 and t1.report_date>='{day.ToString("yyyy-MM-dd")}' and t1.report_date<'{day.AddDays(1).ToString("yyyy-MM-dd")}'";
                        var ad = cn.QueryFirstOrDefault<decimal?>(sql)??0;
                        m.amount_platformfee += ad;
                    }



                    //放款金额
                    var objIncome = listIncome.Where(a => a.platform_type == item).FirstOrDefault();
                    if (item.ToLower() == "amazon")
                    {
                        var objAmazonIncome = listIncome.Where(a => a.platform_type.ToLower() == "fba" || a.platform_type.ToLower() == "amazon");

                        if (objAmazonIncome.Count() > 0)
                        {
                            objIncome = new platformamount
                            {
                                platform_type = item,
                                amount = objAmazonIncome.Sum(a => a.amount),
                            };
                        }
                    }
                    m.amount_incoming += (objIncome != null ? objIncome.amount : 0);


                    //退款金额
                    var objRefund = listRefund.Where(a => a.platform_type == item).FirstOrDefault();
                    if (item.ToLower() == "amazon")
                    {
                        var objAmazonRefund = listRefund.Where(a => a.platform_type.ToLower() == "fba" || a.platform_type.ToLower() == "amazon");

                        if(objAmazonRefund.Count()>0)
                        {
                            objRefund = new platformamount {
                                platform_type = item,
                                amount = objAmazonRefund.Sum(a=>a.amount),
                            };
                        }
                    }

                    if (objRefund != null)
                    {
                        m.amount_refund = objRefund.amount;
                    }

                    m.amount_end = m.amount_start + m.amount_sale_shipping - m.amount_platformfee - m.amount_incoming - m.amount_refund + m.amount_other;
                    listM.Add(m);
                }

                cn.Execute("delete from dc_daily_receivable where day='" + day.ToString("yyyy-MM-dd") + "'");

                //保存数据
                foreach (var item in listM)
                {
                    sql = $"insert dc_daily_receivable (platform,day,amount_start,amount_sale_pay,amount_shipping,amount_refund,amount_sale_shipping,amount_platformfee,amount_incoming,amount_other,amount_end,createtime,lastupdatetime,lastupdateuserid,lastupdateusername) values ('{item.platform}','{item.day.ToString("yyyy-MM-dd")}',{item.amount_start},{item.amount_sale_pay},{item.amount_shipping},{item.amount_refund},{item.amount_sale_shipping},{item.amount_platformfee},{item.amount_incoming},{item.amount_other},{item.amount_end},'{item.createtime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.lastupdatetime.ToString("yyyy-MM-dd HH:mm:ss")}',{item.lastupdateuserid},'{item.lastupdateusername}')";
                    cn.Execute(sql);
                }
            }


        }
    }

    public class platformamount
    {
        /// <summary>
        /// 平台类型
        /// </summary>
        public string platform_type { get; set; }

        /// <summary>
        /// 销售额
        /// </summary>
        public decimal amount { get; set; }

        /// <summary>
        /// 平台费
        /// </summary>
        public decimal cost_platform_fee { get; set; }
        
    }
}
