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(-25);
                    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 and bailun_interception_status in ('None','Failed')";
                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;

                sql = $"select * from dc_daily_receivable where day='{day.ToString("yyyy-MM-dd")}' and platform='Ebay'";
                var m_ebay = cn.QueryFirstOrDefault<dc_daily_receivable>(sql);

                if (m_ebay == null)
                {
                    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",
                    };
                }
                else
                {
                    m_ebay.amount_start = (objBefore != null ? objBefore.amount_end : 0);
                    m_ebay.amount_sale_pay = EbaySales;
                    m_ebay.amount_shipping = EbayShipping;
                    m_ebay.amount_refund = EbayRefund;

                    m_ebay.amount_end = m_ebay.amount_start + EbaySales - EbayShipping - EbayRefund + m_ebay.amount_other;

                    m_ebay.lastupdatetime = DateTime.Now;
                    m_ebay.lastupdateuserid = 0;
                    m_ebay.lastupdateusername = "system";
                }


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

                //期初预收余额
                var sql_shopify = $"select * from dc_daily_receivable where day='{day.AddDays(-1).ToString("yyyy-MM-dd")}' and platform='shopify'";
                var objBefore_shopify = cn.QueryFirstOrDefault<dc_daily_receivable>(sql_shopify);
                //本期收款
                sql_shopify = $"select sum(amount_sales*seller_order_exchange_rate) amount from dc_base_oms_order where platform_type='shopify' 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 and bailun_interception_status in ('None','Failed')";
                var shopifySales = cn.QueryFirstOrDefault<decimal?>(sql_shopify) ?? 0;
                //发货金额
                sql_shopify = $@"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='shopify'
                        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 shopifyShipping = cn.QueryFirstOrDefault<decimal?>(sql_shopify) ?? 0;
                //退款(未发货的)
                sql_shopify = $"select sum(amount_refund_rmb) amount from dc_base_crm_refund where platform_type = 'shopify' 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 shopifyRefund = cn.QueryFirstOrDefault<decimal?>(sql_shopify) ?? 0;

                sql = $"select * from dc_daily_receivable where day='{day.ToString("yyyy-MM-dd")}' and platform='shopify'";
                var m_shopify = cn.QueryFirstOrDefault<dc_daily_receivable>(sql);

                if (m_shopify == null)
                {
                    m_shopify = new dc_daily_receivable
                    {
                        day = day,
                        amount_end = (objBefore_shopify != null ? objBefore_shopify.amount_end : 0) + shopifySales - shopifyShipping - shopifyRefund,
                        amount_start = (objBefore_shopify != null ? objBefore_shopify.amount_end : 0),
                        amount_sale_pay = shopifySales,
                        amount_shipping = shopifyShipping,
                        amount_refund = shopifyRefund,

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

                        lastupdatetime = DateTime.Now,
                        lastupdateuserid = 0,
                        lastupdateusername = "system",
                        platform = "shopify",
                    };
                }
                else
                {
                    m_shopify.amount_start = (objBefore_shopify != null ? objBefore_shopify.amount_end : 0);
                    m_shopify.amount_sale_pay = shopifySales;
                    m_shopify.amount_shipping = shopifyShipping;
                    m_shopify.amount_refund = shopifyRefund;

                    m_shopify.amount_end = m_shopify.amount_start + shopifySales - shopifyShipping - shopifyRefund + m_shopify.amount_other;

                    m_shopify.lastupdatetime = DateTime.Now;
                    m_shopify.lastupdateuserid = 0;
                    m_shopify.lastupdateusername = "system";
                }



                //非Ebay\shopify   应收账款=期初应收余额+本期销售收入-平台扣费-放款金额-退款金额+其他
                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!='shopify' 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);

                //非Ebay   平台费，按付款时间
                sql = $@"select * from (
                        select t1.platform_type,sum(t1.cost_platform_fee*t1.seller_order_exchange_rate*t1.bailun_sku_quantity_ordered) cost_platform_fee,0 as cost_fba_fee 
                        from dc_base_oms_sku t1 where 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!='shopify' and t1.platform_type!='FBA' and t1.paid_time>='{day.ToString("yyyy-MM-dd")}' and t1.paid_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' and t1.bailun_interception_status in ('None','Failed') and (t1.platform_type!='FBA' and t1.bailun_order_status!='CantHandle')
                        group by platform_type
                        union all
                        select platform_type,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 and bailun_interception_status in ('None','Failed')
                        ) tb";
                var listPlatformFee = 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" && a.ToLower() != "shopify").ToList();

                //退款
                sql = $"select sum(amount_refund_rmb) as amount,platform_type from dc_base_crm_refund where platform_type!='Ebay' and platform_type!='shopify' 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.exchange_money*other_to_cny_exchange_rate) 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 createTime>='{day.ToString("yyyy-MM-dd")}' and createTime<'{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, m_shopify };
                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.ToLower() == item.ToLower()).FirstOrDefault();

                    var objPlatformFee = listPlatformFee.Where(a => a.platform_type.ToLower() == item.ToLower()).FirstOrDefault();

                    if (item.ToLower() == "amazon")
                    {
                        var objAmazon = listAmount.Where(a => a.platform_type.ToLower() == "fba" || a.platform_type.ToLower() == "amazon");
                        var objPlatformFee_s = listPlatformFee.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 = objPlatformFee_s.Sum(a=>a.cost_platform_fee),
                                platform_type = item,
                            };

                            objPlatformFee.cost_platform_fee = objPlatformFee_s.Sum(a => a.cost_platform_fee);
                        }
                        
                    }

                    sql = $"select * from dc_daily_receivable where day='{day.ToString("yyyy-MM-dd")}' and platform='{item}'";
                    var m = cn.QueryFirstOrDefault<dc_daily_receivable>(sql);
                    if (m == null)
                    {
                        m = new dc_daily_receivable
                        {
                            platform = item,
                            amount_start = objBefore != null ? objBefore.amount_end : 0,
                            amount_other = 0,
                            amount_platformfee = objPlatformFee != null ? objPlatformFee.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",
                        };
                    }
                    else
                    {
                        m.amount_start = objBefore != null ? objBefore.amount_end : 0;
                        m.amount_platformfee = objPlatformFee != null ? objPlatformFee.cost_platform_fee : 0;
                        m.amount_sale_shipping = obj != null ? obj.amount : 0;

                        m.lastupdatetime = DateTime.Now;
                        m.lastupdateuserid = 0;
                        m.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.ToLower() == item.ToLower()).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.ToLower() == item.ToLower()).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);
                }

                //更新资产负债表的应收账款

                //Ebay+shopify
                var listEbayAmount = listM.Where(a => a.platform.ToLower() == "ebay" || a.platform.ToLower() == "shopify");
                //其他平台
                var listOtherAmount = listM.Where(a => a.platform.ToLower() != "ebay" && a.platform.ToLower() != "shopify");

                new Bailun.DC.Services.FinanceReportServices().UpdateLogisticsEndAmount(day, (listOtherAmount.Count() > 0 ? listOtherAmount.Sum(a => a.amount_end) : 0) - (listEbayAmount.Count() > 0 ? listEbayAmount.Sum(a => a.amount_end) : 0), "accountsReceivableUpdate");

            }


        }
        
    }

    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; }
        
    }
}
