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

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

        private decimal hl_amount_pay=0;

        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
            {
                hl_amount_pay = 0;
                var now = DateTime.Now;

                if (now.Hour == 0 && now.Minute == 30)  //每天 0：30分启动
                {
                    var day = now;
                    Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    Init(DateTime.Parse(now.AddDays(-1).ToShortDateString()), DateTime.Parse(now.ToShortDateString()));
                    
                    Console.WriteLine("任务运行完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                else if (now.Hour==12 && now.Minute == 1)
                {
                    Console.WriteLine("重跑数据 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    var start = now.AddMonths(-2);
                    while (start.AddDays(1) < DateTime.Now)
                    {
                        new Services().Init(DateTime.Parse(start.ToShortDateString()), DateTime.Parse(start.AddDays(1).ToShortDateString()));
                        //new Bailun.DC.Services.FinanceReportServices().AddLogisticsWaitPay(start, hl_amount_pay);
                        new Bailun.DC.Services.FinanceReportServices().UpdateLogisticsWaitPay(start, 0, "admin", null);
                        start = start.AddDays(1);
                    }
                }

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


        public void Init(DateTime start,DateTime end)
        {
            var m = new dc_daily_happen_amount() {
                day = DateTime.Parse(start.ToShortDateString()),
                ac_amount_happen = 0,
                ac_amount_pay = 0,
                ac_amount_sales = 0,

                hl_amount_happen = 0,
                hl_amount_pay = 0,
                hl_amount_sales = 0,
                
                ss_amount_happen = 0,
                ss_amount_pay = 0,
                ss_amount_sales = 0,
                
                tl_amount_happen = 0,
                tl_amount_pay = 0,
                tl_amount_sales=0,
                
                tos_amount_happen = 0,
                tos_amount_pay = 0,
                tos_amount_sales = 0,

                createtime = DateTime.Now,
            };

            var sqlpay = "";
            var sqlhappen = "";
            var sqlsales = "";

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

                #region 管理成本

                var url = "http://api.fee.bailuntec.com/purchase/other/cost/api/manageCostList?o=0";
                var cwurl = "http://cw.bailuntec.com/api/api/GetRepayPlanDetails?";
                var lgurl = "http://api.fee.bailuntec.com/fee/api/cost/api/logisticsCostList?";

                url += "&startDate=" + start.ToString("yyyy-MM-dd HH:mm:ss") + "&endDate=" + start.ToString("yyyy-MM-dd HH:mm:ss");
                cwurl += "BeginRepayTime=" + start.ToString("yyyy-MM-dd HH:mm:ss") + "&EndRepayTime=" + end.ToString("yyyy-MM-dd HH:mm:ss");
                //lgurl += "startDate=" + start.ToString("yyyy-MM-dd") + "&endDate=" + end.AddDays(-1).ToString("yyyy-MM-dd");

                //利息支出
                var listInterest = new Bailun.DC.Services.FinanceReportServices().ListInterestExpense(cwurl);

                //管理成本
                var list = new Bailun.DC.Services.FinanceReportServices().ListFinanceManageCost(start, start, "", "", "", null); //new Bailun.DC.Services.FinanceReportServices().ListOtherCost(url);
                //物流费支出
                //var listlg = ListLogisticFee(lgurl);



                var listFeeType = new List<string> { "推广费", "税费", "平台费用", "服务费", "其他" };

                list = list.Where(a => a.company_name.Contains("广州歌戈儿生活科技有限公司") || a.company_value == 5 || a.company_value == 1 || a.company_value == 3 || a.company_value == 8 || a.company_value == 2 ).ToList();

                if (list.Count > 0)
                {
                    m.ac_amount_pay += list.Sum(a => a.amount_rmb);
                    m.ac_amount_happen += list.Sum(a => a.amount_rmb);

                    var objtemp = list.Where(a => a.company_value == 5 || a.company_value == 2).Where(b => listFeeType.Contains(b.fee_super_type));
                    if (objtemp.Count() > 0)
                    {
                        m.ac_amount_sales = objtemp.Sum(a => a.amount_rmb);
                    }

                }

                if (listInterest.Count > 0)
                {
                    m.ac_amount_pay += listInterest.Sum(a => a.RepayInterestRMB);
                    m.ac_amount_happen += listInterest.Sum(a => a.RepayInterestRMB);
                }

                //支出 物流费支出
                //if (listlg.Count() > 0)
                //{
                //    m.ac_amount_pay += listlg.Sum(a => a.amountRmb);
                //    m.ac_amount_happen += listlg.Sum(a => a.amountRmb);
                //}

                #endregion


                #region 头程物流
                // and cashier_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'
                //已付款
                sqlpay = $"select sum(if (cashier_type = 2,-cashier_paymoneyrmb,cashier_paymoneyrmb)) cashier_paymoneyrmb from dc_base_finance_cashier where cashier_type in (1, 2) and cashier_status = 1 and sourcecode in ('newCost', 'oldbuy') and type_name = '销售费用/物流费' and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'";
                m.hl_amount_pay += cn.QueryFirstOrDefault<decimal?>(sqlpay, null, null, 2 * 60)??0;

                //Add by Allan at 20200220，已付款要减去收款金额
                //var sqlrecevice = $@"select sum(cashier_paymoneyrmb) cashier_paymoneyrmb from dc_base_finance_cashier where cashier_type=2 and cashier_status=1 and sourcecode in ('newCost','oldbuy') and type_name='销售费用/物流费' and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'";
                //m.hl_amount_pay -= cn.QueryFirstOrDefault<decimal?>(sqlrecevice, null, null, 2 * 60) ?? 0;


                //发生额
                sqlhappen = $@"select sum(tb.cost_first) from (select DISTINCT channel_id,box_id,cost_first from dc_base_cost_first where operation_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and operation_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' order by channel_id,box_id) tb";
                m.hl_amount_happen += cn.QueryFirstOrDefault<decimal?>(sqlhappen, null, null, 2 * 60) ?? 0;

                //销售额
                sqlsales = $@"select sum(amount) amount from (
                            select sum(t1.quantity_shipped* t2.cost_first) amount 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.has_delete = 0 and t2.has_scalp = 0 and t2.has_innersale = 0
                            where t1.shipping_time >= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.shipping_time < '{end.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.shipping_status = 'TotalShipping'
                            union all
                            select sum(cost_first) amount from dc_base_oms_order t1 where t1.company_id = 1 and t1.platform_type = 'FBA' and t1.bailun_order_status != 'Canceled' and t1.create_time >= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time < '{end.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.has_scalp = 0 and t1.has_innersale = 0 and t1.bailun_interception_status in ('None', 'Failed')
                            ) tb";
                m.hl_amount_sales += cn.QueryFirstOrDefault<decimal?>(sqlsales, null, null, 2 * 60)??0;

                #endregion

                #region 尾程物流

                //已付款
                m.tl_amount_pay = m.hl_amount_pay;
                
                //发生额
                sqlhappen = $@"select sum(t2.cost_logistics*t1.quantity_shipped) from dc_base_oms_pick t1 
                            join dc_base_oms_sku t2 on t1.bailun_sku = t2.bailun_sku and t1.origin_order_id = t2.origin_order_id and t2.bailun_order_status != 'CantHandle' and t2.bailun_order_status != 'Canceled' and t2.has_scalp = 0 and t2.has_delete = 0 and t2.has_innersale = 0 and t2.bailun_interception_status in ('None', 'Failed') and t2.company_id = 1 and t2.cost_logistics > 0 
                            where t1.shipping_time >= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.shipping_time < '{end.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.has_delete = 0 and t1.company_id = 1 and t1.shipping_status = 'TotalShipping'";
                m.tl_amount_happen += cn.QueryFirstOrDefault<decimal?>(sqlhappen,null,null,2*60)??0;

                //销售额
                sqlsales = $"select sum(cost_logistics*bailun_sku_quantity_ordered) from dc_base_oms_sku t1 where paid_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and paid_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and bailun_order_status!='Canceled' and has_scalp=0 and has_delete=0 and has_innersale=0 and company_id=1 and cost_logistics>0 ";
                m.tl_amount_sales += cn.QueryFirstOrDefault<decimal?>(sqlsales, null, null, 2 * 60)??0;

                #endregion

                #region 一级供应商
                //select sum(cashier_paymoneyrmb) cashier_paymoneyrmb from dc_base_finance_cashier where cashier_type = 1 and cashier_status = 1 and sourcecode = 'Buy' and cashier_time>= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}'
                //付款额
                sqlpay = $"select sum(cashier_paymoneyrmb) cashier_paymoneyrmb from dc_base_finance_cashier where cashier_type=1 and cashier_status=1 and sourcecode ='Buy' and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'";
                m.tos_amount_pay += cn.QueryFirstOrDefault<decimal?>(sqlpay, null, null, 2 * 60)??0;
                
                //发生额
                sqlhappen = $"select sum(unit_price*quantity_purchase) from dc_base_purchase_details where status>-1 and create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and ispush=0";
                m.tos_amount_happen += cn.QueryFirstOrDefault<decimal?>(sqlhappen, null, null, 2 * 60)??0;

                //销售额
                sqlsales = $@"select sum(amount) from (
                                select sum(t1.cost_product*t2.quantity_shipped) amount
                                from dc_base_oms_sku t1
                                join dc_base_oms_pick t2 on t1.bailun_order_id=t2.bailun_order_id and t1.bailun_sku=t2.bailun_sku and t2.has_delete=0 and t2.shipping_status='TotalShipping' and t2.company_id=1
                                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!='FBA' and t1.bailun_order_status!='CantHandle') or t1.platform_type='FBA')
                                 and t2.shipping_time>='{start.ToString("yyyy-MM-dd")}' and t2.shipping_time<'{end.ToString("yyyy-MM-dd")}'
                                union all 
                                select sum(t1.cost_product*t1.bailun_sku_quantity_shipped) amount
                                from dc_base_oms_sku t1
                                where t1.has_delete=0 and t1.company_id=1 and t1.bailun_order_status!='Canceled' and t1.create_time>='{start.ToString("yyyy-MM-dd")}' and t1.create_time<'{end.ToString("yyyy-MM-dd")}' 
                                and t1.platform_type='FBA'
                                ) tb";
                m.tos_amount_sales += cn.QueryFirstOrDefault<decimal?>(sqlsales, null, null, 2 * 60)??0;
                #endregion

                #region 二级供应商

                //付款额  取财务系统—出纳单列表当期费用系统中广州百伦，香港百伦对广州哈倪蔓生物科技有限公司、 广州拉古娜生物科技有限公司、 HONGKONG KULE INTERNATIONAL TRADING LIMITED、LEIKESAER INFORMATION SERVICE LIMITED、 广州市花都区花城滋柔贸易商行的应付账款付款金额
                sqlpay = $@"select sum(cashier_paymoneyrmb) from dc_base_finance_cashier where cashier_type=1 and cashier_status=1 and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and companymain_value_from in (1,2) and tradeb_bjectname in ('广州哈倪蔓生物科技有限公司','广州拉古娜生物科技有限公司','HONGKONG KULE INTERNATIONAL TRADING LIMITED','LEIKESAER INFORMATION SERVICE LIMITED','广州市花都区花城滋柔贸易商行')";
                m.ss_amount_pay += cn.QueryFirstOrDefault<decimal?>(sqlpay, null, null, 2 * 60)??0;

                //发生额
                sqlhappen = $@"select sum(quantity_purchase*unit_price) from dc_semi_purchase_info where create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and status>-1";
                m.ss_amount_happen += cn.QueryFirstOrDefault<decimal?>(sqlhappen, null, null, 2 * 60)??0;

                //销售额
                m.ss_amount_sales += m.tos_amount_sales;
                #endregion

                //已付款
                hl_amount_pay = m.hl_amount_pay;

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

                var result = cn.Insert(m);
                if (!result.HasValue || result.Value <= 0)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "保存失败，请重试！");
                }
                else
                {
                    Console.WriteLine(m.day + " 保存成功！");

                    var lg_result = new Bailun.DC.Services.FinanceReportServices().AddLogisticsWaitPay(m.day, m.hl_amount_pay);
                    if (string.IsNullOrEmpty(lg_result))
                    {
                        Console.WriteLine("生成物流供应商应付款余额记录成功 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else
                    {
                        Console.WriteLine("生成物流供应商应付款余额记录失败： " + result + " " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    }

                }
            }

        }

        /// <summary>
        /// 获取物流费列表
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        public List<Models.Api.mLogisticFee> ListLogisticFee(string url)
        {
            var list = new List<Models.Api.mLogisticFee>();
            var result = Common.HttpHelper.NetHelper.Request(url);
            if (!string.IsNullOrEmpty(result))
            {
                var obj = Newtonsoft.Json.JsonConvert.DeserializeObject<Models.Api.ListLogisticFee>(result);
                if (obj != null)
                {
                    list = obj.data;
                }
            }

            return list;
        }
    }
}
