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;
using System.Data;

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)  //凌晨5：01分启动
                {
                    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"));
                }
                else if (now.Hour % 8 == 0 && now.Minute == 1)
                {
                    var start = DateTime.Parse(now.AddMonths(-1).ToShortDateString());
                    while (start.AddDays(1) < DateTime.Now)
                    {
                        Console.WriteLine(start);
                        Init(start, start.AddDays(1));
                        SaveMoneyFlowCount(start, start.AddDays(1));
                        Console.WriteLine("重跑 " + start.ToString("yyyy-MM-dd HH:mm:ss")+" 完成");
                        start = start.AddDays(1);
                    }
                }

            }
            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?";
            var lgurl = "http://api.fee.bailuntec.com/fee/api/cost/api/logisticsCostList?";

            url += "&startDate=" + start.ToString("yyyy-MM-dd") + "&endDate=" + end.AddDays(-1).ToString("yyyy-MM-dd");
            cwurl += "BeginRepayTime=" + start.ToString("yyyy-MM-dd") + "&EndRepayTime=" + end.AddDays(-1).ToString("yyyy-MM-dd");
            lgurl += "startDate=" + start.ToString("yyyy-MM-dd") + "&endDate=" + end.AddDays(-1).ToString("yyyy-MM-dd");
        
            //利息支出
            var listInterest = ListInterestExpense(cwurl);
            //管理成品
            var list = ListFinanceManageCost(start, start, "", "", "", null);//ListOtherCost(url);
            //物流费支出
            var listlg = ListLogisticFee(start,start);//ListLogisticFee(lgurl);


            decimal costTotal = 0;
            costTotal = listInterest.Count > 0 ? listInterest.Sum(a => a.RepayInterestRMB):0;
            
            //管理成本 只取付款主体为广州百伦供应链科技有限公司、香港百伦科技有限公司、广州电子服装仓、阳山仓、深圳仓、广州哥戈尔生活科技有限公司、广州迪致美容科技有限公司的数据
            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 || a.company_value == 7).ToList();
            if (list.Count > 0)
            {
                costTotal += list.Sum(a => a.amount_rmb);
            }

            //支出 物流费支出
            if (listlg!=null && listlg.Count() > 0)
            {
                costTotal += listlg.Sum(a => a.amount_rmb);
            }


            //支出 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 in ('newCost','IncomeAndExpenditure')))";

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

                //Add by Allan at 20200704, 成品退货收款 放在支出里面，改为负数
                sql = $"select sum(-cashier_paymoneyrmb) as amount from dc_base_finance_cashier where cashier_type=2 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")}' and companymain_value_from !=14 and companymain_value_from!=48";
                var objFinishRefund = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);
                costTotal += (objFinishRefund ?? 0);
                //End Add

                //收入   提现到账的资金  去掉1688的收入
                sql = $"select sum(other_to_cny_money) amount from dc_base_finance_cashierdetail where collection_platform!=1 and 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?";
            var lgurl = "http://api.fee.bailuntec.com/fee/api/cost/api/logisticsCostList?";

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

            //利息支出
            var listInterest = ListInterestExpense(cwurl);
            //管理成品
            var list = ListFinanceManageCost(start,start,"","","",null);//ListOtherCost(url);
            //物流费支出
            var listlg = ListLogisticFee(start, start); //ListLogisticFee(lgurl);

            decimal costTotal = 0;
            costTotal = listInterest.Count > 0 ? listInterest.Sum(a => a.RepayInterestRMB) : 0;

            //管理成本 只取付款主体为广州百伦供应链科技有限公司、香港百伦科技有限公司、广州电子服装仓、阳山仓、深圳仓、广州哥戈尔生活科技有限公司、广州迪致美容科技有限公司的数据
            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 || a.company_value == 7).ToList();
            if (list.Count > 0)
            {
                costTotal += list.Sum(a => a.amount_rmb);
            }

            //支出 物流费支出
            if (listlg != null && listlg.Count() > 0)
            {
                costTotal += listlg.Sum(a => a.amount_rmb);
            }
            
            //采购下单
            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 in ('newCost','IncomeAndExpenditure')))";
                var objPayed = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);

                //Add by Allan at 20200704, 成品退货收款 放在支出里面，改为负数
                sql = $"select sum(-cashier_paymoneyrmb) as amount from dc_base_finance_cashier where cashier_type=2 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")}' and companymain_value_from !=14 and companymain_value_from!=48";
                var objFinishRefund = cn.QueryFirstOrDefault<decimal?>(sql, null, null, 2 * 60);
                costTotal += (objFinishRefund ?? 0);
                //End Add

                //到货入库
                sql = $@"select sum(t1.count*t2.unit_price) amount from dc_base_purchase_inbound 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))
                        from dc_daily_stock ds left join dc_base_sku s on ds.bailun_sku = s.bailun_sku
                        join dc_base_warehouse w on ds.warehouse_code = w.warehouse_code and w.hq_type!='耗材仓'
                        where ds.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_rmb) from dc_platform_balance where delstatus=0 and day='{start.ToString("yyyy-MM-dd")}'";
                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;
        }

        /// <summary>
        /// 管理成本列表（从数据中心表获取数据）
        /// </summary>
        /// <param name="start">付款开始时间</param>
        /// <param name="end">付款结束时间</param>
        /// <returns></returns>
        public List<Models.dc_base_finance_managecost> ListFinanceManageCost(DateTime start, DateTime end, string feesupertype, string feesubtype, string departmentname, int? paycompanyid)
        {
            var sqlparam = new DynamicParameters();
            var list = new List<Models.dc_base_finance_managecost>();

            var sql = $"select * from dc_base_finance_managecost where pay_time>='{start.ToString("yyyy-MM-dd")}' and pay_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";

            if (!string.IsNullOrEmpty(feesupertype))
            {
                sql += " and fee_super_type=@type";
                sqlparam.Add("type", feesupertype);
            }

            if (!string.IsNullOrEmpty(feesubtype))
            {
                sql += " and fee_sub_type=@subtype";
                sqlparam.Add("subtype", feesubtype);
            }

            if (!string.IsNullOrEmpty(departmentname))
            {
                sql += " and department_name=@department_name";
                sqlparam.Add("department_name", departmentname);
            }


            if (paycompanyid.HasValue && paycompanyid.Value > 0)
            {
                if (paycompanyid.Value == 53)
                {
                    sql += " and company_value in (53,59,60,61,66)";
                }
                else
                {
                    sql += " and company_value=" + paycompanyid.Value;
                }


            }

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

                list = cn.Query<dc_base_finance_managecost>(sql, sqlparam, null, true, 2 * 60).ToList();
                return list.ToList();
            }
        }

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

        /// <summary>
        /// 物流费用
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List<Models.dc_base_finance_logistics> ListLogisticFee(DateTime start, DateTime end)
        {
            var sqlparam = new DynamicParameters();
            var list = new List<Models.dc_base_finance_logistics>();

            var sql = $"select no,amount_rmb from dc_base_finance_logistics where pay_time>='{start.ToString("yyyy-MM-dd")}' and pay_time<'{end.AddDays(1).ToString("yyyy-MM-dd")}'";
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                list = cn.Query<dc_base_finance_logistics>(sql, sqlparam, null, true, 2 * 60).ToList();
                return list.ToList();
            }
        }

        #endregion
    }


}
