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

namespace Bailun.DC.DailyPayAndIncoming
{
    /// <summary>
    /// 每日收支统计
    /// </summary>
    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 == 5 && now.Minute == 01)  //每天 23：59分启动
                {
                    Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    var start = DateTime.Parse(now.AddDays(-1).ToShortDateString());

                    Init(start, start.AddDays(1));

                    SaveMoneyFlowCount(start, start.AddDays(1));
                    
                    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 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") + "&endDate=" + end.ToString("yyyy-MM-dd");
            cwurl += "BeginRepayTime=" + start.ToString("yyyy-MM-dd") + "&EndRepayTime=" + end.ToString("yyyy-MM-dd");
            
            //利息支出
            var listInterest = ListInterestExpense(cwurl);
            //管理成品
            var list = ListOtherCost(url);

            decimal costTotal = 0;
            costTotal = listInterest.Count > 0 ? listInterest.Sum(a => a.RepayInterestRMB):0;
            
            //管理成本 只取付款主体为广州百伦供应链科技有限公司、香港百伦科技有限公司、广州电子服装仓、阳山仓、深圳仓的数据
            list = list.Where(a => a.companyValue == 1 || a.companyValue == 2 || a.companyValue == 5 || a.companyValue == 8 || a.companyValue == 7).ToList();
            if (list.Count > 0)
            {
                costTotal += list.Sum(a => a.amountRmb);
            }


            //支出 2、成品采购+半成品采购+百伦（广州、香港）支付给哈倪曼和拉古娜的费用+百伦管理费用
            var sql = $@"select sum(cashier_paymoneyrmb) as amount from dc_base_finance_cashier where cashier_status=1 and cashier_type=1 and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'  and ((sourcecode in ('Buy','SemiFinishedProduct') and companymain_value_from !=14 and companymain_value_from!=48) or (tradeb_bjectname in ('广州哈倪蔓生物科技有限公司','广州拉古娜生物科技有限公司','广州美甲生产仓') and companymain_value_from in (1,2,5,7,8) and sourcecode='newCost'))";

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

                var obj = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);

                //收入   提现到账的资金
                sql = $"select sum(other_to_cny_money) amount from dc_base_finance_cashierdetail where daozhang_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and daozhang_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'";

                var objIncoming = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);

                var m = new dc_daily_pay_income()
                {
                    amount_income = (objIncoming ?? 0),
                    amount_payed = costTotal+(obj ?? 0),
                    createtime = DateTime.Now,
                    record_time = DateTime.Parse(start.ToShortDateString()),
                };

                sql = $"delete from dc_daily_pay_income where record_time='{m.record_time.ToString("yyyy-MM-dd")}'";
                cn.Execute(sql);

                sql = $"insert dc_daily_pay_income (amount_income,amount_payed,createtime,record_time) values ({m.amount_income},{m.amount_payed},'{m.createtime.ToString("yyyy-MM-dd HH:mm:ss")}','{m.record_time.ToString("yyyy-MM-dd")}')";

                cn.Execute(sql,null,null,2*60);
            }

        }

        //现金流明细    采购下单、财务付款、到货、库存、售出、应收款、放款在途、银行到账
        public void SaveMoneyFlowCount(DateTime start,DateTime end)
        {
            //支出包含两部分，一部分是百伦管理费，一部分是产品成本支出
            //管理成本  广州百伦、香港百伦、电子服装厂、阳山仓、深圳仓的管理成本
            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") + "&endDate=" + end.ToString("yyyy-MM-dd");
            cwurl += "BeginRepayTime=" + start.ToString("yyyy-MM-dd") + "&EndRepayTime=" + end.ToString("yyyy-MM-dd");

            //利息支出
            var listInterest = ListInterestExpense(cwurl);
            //管理成品
            var list = ListOtherCost(url);

            decimal costTotal = 0;
            costTotal = listInterest.Count > 0 ? listInterest.Sum(a => a.RepayInterestRMB) : 0;
            list = list.Where(a => a.companyValue == 1 || a.companyValue == 2 || a.companyValue == 5 || a.companyValue == 8 || a.companyValue == 7).ToList();
            if (list.Count > 0)
            {
                costTotal += list.Sum(a => a.amountRmb);
            }


            //采购下单
            var sql = $"select sum(amount_paid) amount from dc_base_purchase_details where create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}' and `status`>=0";

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

                //采购下单
                var objPurchase = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);

                //财务付款 支出 2、成品采购+半成品采购+百伦（广州、香港）支付给哈倪曼和拉古娜的费用+百伦管理费用
                sql = $@"select sum(cashier_paymoneyrmb) as amount from dc_base_finance_cashier where cashier_status=1 and cashier_type=1 and cashier_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and cashier_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'  and ((sourcecode in ('Buy','SemiFinishedProduct') and companymain_value_from !=14 and companymain_value_from!=48) or (tradeb_bjectname in ('广州哈倪蔓生物科技有限公司','广州拉古娜生物科技有限公司','广州美甲生产仓') and companymain_value_from in (1,2,5,7,8) and sourcecode='newCost'))";
                var objPayed = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);

                //到货
                sql = $@"select sum(t1.count*t2.unit_price) amount from dc_base_purchase_arrival t1
                        join dc_base_purchase_details t2 on t1.purchase_id=t2.purchase_id and t1.bailun_sku=t2.bailun_sku
                        where t1.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'";
                var objArrival = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);

                //库存   每日晚上12点10分跑每日库存服务
                sql = $@"select sum(ds.unit_price * (ds.usable_stock + ds.occupy_stock) * r.exchange_rate)
                        from dc_daily_stock ds left join dc_base_sku s on ds.bailun_sku = s.bailun_sku
                        left join dc_base_warehouse w on ds.warehouse_code = w.warehouse_code
                        left join dc_exchange_rate r on s.currency = r.init_curreny
                        where ds.record_time = '{start.ToString("yyyy-MM-dd")}' and r.final_curreny = 'CNY' and r.record_time = '{start.ToString("yyyy-MM-dd")}'";
                var objStock = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);


                //售出
                sql = $"select sum(bailun_sku_quantity_ordered*bailun_sku_unit_price) as amount from dc_base_oms_sku where has_delete=0 and company_id=1 and bailun_order_status!='Canceled' and create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'";
                var objSale = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);


                //应收款  当前发货的销售额
                sql = $@"select sum(amount) amount from (select sum(t1.quantity_shipped*t2.amount_sales*t2.seller_order_exchange_rate) amount from dc_base_oms_pick t1
                        join dc_base_oms_sku t2 on t1.bailun_sku=t2.bailun_sku and t1.bailun_order_id=t2.bailun_order_id and t2.has_scalp=0 and t2.has_delete=0 and t2.has_innersale=0 and t2.bailun_order_status!='Canceled' and t2.has_innersale=0 and t2.company_id=1 and (t2.platform_type!='FBA' and t2.bailun_order_status!='CantHandle')
                        where t1.shipping_status='TotalShipping' and t1.shipping_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t1.shipping_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'
                        union all
                        select sum(t2.amount_sales*t2.seller_order_exchange_rate) amount from dc_base_oms_order t2 where t2.bailun_order_status!='Canceled' and t2.platform_type='FBA' and t2.bailun_payment_status='TotalPay' and t2.create_time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and t2.create_time<'{end.ToString("yyyy-MM-dd HH:mm:ss")}') tb";
                var objReceivable = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);

                //放款在途
                sql = $@"select sum(money*other_to_cny_exchange_rate) amount from dc_base_finance_cashierdetail where status=1 and createTime>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and createTime<'{end.ToString("yyyy-MM-dd HH:mm:ss")}'";
                var objWithdrawOnWay = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);

                //银行到账  
                sql = $"select sum(balance_rmb) amount from dc_bank_account_balance where company_value in (2,1,39,32,26,12,42,34,38,22,29,8,27,5,40,33,7) and bank_name != 'payoneer' and bank_name!='PayPal' and bank_name!='Beepay' and bank_name!='P卡' and statistical_time='{start.ToString("yyyy-MM-dd")}'";
                var objIncoming = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);

                var m = new dc_daily_companymoneystatus()
                {
                    amount_arrivaled = objArrival??0,
                    amount_incoming = objIncoming??0,
                    amount_payed = (objPayed??0)+ costTotal,
                    amount_purchase = objPurchase??0,
                    amount_receivable = objReceivable??0,
                    amount_sale = objSale??0,
                    amount_stock = objStock??0,
                    amount_withdrawonway = objWithdrawOnWay??0,

                    createtime = DateTime.Now,
                    record_time = DateTime.Parse(start.ToShortDateString())
                };

                sql = $"delete from dc_daily_companymoneystatus where record_time='{m.record_time.ToString("yyyy-MM-dd")}'";
                cn.Execute(sql);

                sql = "insert dc_daily_companymoneystatus (amount_arrivaled,amount_incoming,amount_payed,amount_purchase,amount_receivable,amount_sale,amount_stock,amount_withdrawonway,createtime,record_time) values ";
                sql += ($@"({m.amount_arrivaled},{m.amount_incoming},{m.amount_payed},{m.amount_purchase},{m.amount_receivable},{m.amount_sale},{m.amount_stock},{m.amount_withdrawonway},'{m.createtime.ToString("yyyy-MM-dd HH:mm:ss")}','{m.record_time.ToString("yyyy-MM-dd")}')");

                cn.Execute(sql, null, null, 2 * 60);
            }

        }


        #region 管理成本
        /// <summary>
        /// 获取管理成本明细
        /// </summary>
        /// <param name="url">访问地址</param>
        /// <returns></returns>
        public List<Models.Api.mOtherFee> ListOtherCost(string url)
        {
            var list = new List<Models.Api.mOtherFee>();
            var result = Common.HttpHelper.NetHelper.Request(url);
            if (!string.IsNullOrEmpty(result))
            {
                var obj = Newtonsoft.Json.JsonConvert.DeserializeObject<Models.Api.mListOtherFee>(result);
                if (obj != null)
                {
                    list = obj.data;
                }
            }

            return list;
        }

        /// <summary>
        /// 管理成本--利息支出    
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        public List<Models.Api.mInterestExpense> ListInterestExpense(string url)
        {
            var list = new List<Models.Api.mInterestExpense>();

            var result = Common.HttpHelper.NetHelper.Request(url);
            if (!string.IsNullOrEmpty(result))
            {
                var obj = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.Api.mInterestExpense>>(result);
                if (obj.Count > 0)
                {
                    list = obj;
                }
            }

            return list;
        }


        #endregion
    }


}
