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;

        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 == 23 && now.Minute == 59)  //每天 23：59分启动
                {
                    Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    Init(DateTime.Parse(now.ToShortDateString()), DateTime.Parse(now.ToShortDateString()));

                    Console.WriteLine("任务运行完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }

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

                url += "&startDate=" + start.ToString("yyyy-MM-dd HH:mm:ss") + "&endDate=" + end.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");

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

                //管理成本
                var list = new Bailun.DC.Services.FinanceReportServices().ListOtherCost(url);

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



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

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

                }

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

                #endregion


                #region 头程物流

                //已付款
                sqlpay = $"select sum(cashier_paymoneyrmb) cashier_paymoneyrmb from dc_base_finance_cashier where cashier_type=1 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;


                //发生额
                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_tail*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 != 'Canceled' and t2.has_scalp = 0 and t2.has_delete = 0 and t2.has_innersale = 0 and t2.company_id = 1 and t2.cost_tail > 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 一级供应商

                //付款额
                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(t1.quantity_shipped*t2.bailun_sku_unit_price) from 
                            (select quantity_shipped, origin_order_id, bailun_sku from dc_base_oms_pick where has_delete = 0 and shipping_status = 'TotalShipping' and shipping_time >= '{start.ToString("yyyy-MM-dd HH:mm:ss")}' and shipping_time < '{end.ToString("yyyy-MM-dd HH:mm:ss")}') t1
                            join dc_base_oms_sku t2 on t1.origin_order_id = t2.origin_order_id and t1.bailun_sku = t2.bailun_sku and t2.has_delete = 0";
                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



                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 + " 保存成功！");
                }
            }

        }
    }
}
