using AutoTurnOver.Models.auto;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AutoTurnOver.Utility;

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 周系数计算结果
    /// </summary>
    public class dc_base_week_coefficient_dao : connectionHelper
    {
        /// <summary>
        /// 刷新周系数
        /// </summary>
        public static void ResetData()
        {
            var read_conn = MyMySqlConnection._connection_read_only;
            var conn = MyMySqlConnection._connection;
            var page = 1;
            var rows = 1000;
            var weeks = 4;
            var date28 = DateTime.Now.AddDays(-(4*7)).ToDayHome();
            while (true)
            {
                var skus = read_conn.Query<bailun_sku_dto>($" select bailun_sku,warehouse_code from dc_auto_turnover  limit {(page - 1) * rows},{rows} ").ToList();
                if(skus==null || skus.Count <= 0)
                {
                    break;
                }
                foreach (var item in skus)
                {
                    try
                    {
                        // 查询最近28天总销量
                        var week_date_datas = read_conn.Query<week_date_dto>($@"
select date_format(paid_time, '%Y-%m-%d') as 'date',sum(bailun_sku_quantity_ordered) as 'ordered' from dc_base_oms_sku_30  as t1 
where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and  t1.bailun_order_status != 'Canceled'
        and t1.has_delete = 0
        and t1.has_scalp = 0
        and t1.has_buyer_remark = 0
        and t1.has_platsku_remark = 0 
				and t1.paid_time>=@btime
				GROUP BY date_format(paid_time, '%Y-%m-%d') ",new {
                            bailun_sku = item.bailun_sku,
                            warehouse_code = item.warehouse_code,
                            btime = date28
                        });

                        var week_coefficient_datas = new List<week_coefficient_dto>();
                        // 计算每周，每天的加权系数
                        for (int i = 1; i <= weeks; i++)
                        {
                            var btime = date28.AddDays((i - 1)*7);
                            var etime = btime.AddDays(6);
                            // 计算本周总销量
                            var sumData = week_date_datas.Where(s => s.date >= btime && s.date <= etime).ToList();
                            var sumDataVal = sumData.Sum(s => s.ordered);
                            var sumDataAvgVal = ((decimal)sumDataVal) / 7M;
                            for (int j = 0; j <= 6; j++)
                            {
                                if (sumDataAvgVal <= 0)
                                {
                                    week_coefficient_datas.Add(new week_coefficient_dto { week = j, coefficient = 0M, formula = " ( 0 / 0 )" });
                                }
                                else
                                {
                                    // 计算当天的销量
                                    var day_val = sumData.Where(s => (int)s.date.DayOfWeek == j).FirstOrDefault();
                                    var day_sa = 0M;
                                    if (day_val != null)
                                    {
                                        day_sa = day_val.ordered;
                                    }
                                    week_coefficient_datas.Add(new week_coefficient_dto { week = j, coefficient = Math.Round(((dynamic)day_sa) / (decimal)sumDataAvgVal, 2), formula = $" ( {day_sa} / ( {sumDataVal} / 7 ) ) " });
                                }

                            }
                        }

                        for (int i = 0; i <= 6; i++)
                        {
                            var weekDatas = week_coefficient_datas.Where(s => s.week == i);
                            var avg = weekDatas.Select(s => s.coefficient).Average();
                            //var has_val = weekDatas.Select(s => s.coefficient).Average();
                            var data = new dc_base_week_coefficient()
                            {
                                bailun_sku = item.bailun_sku,
                                warehouse_code = item.warehouse_code,
                                bailun_sku_warehouse_code = $"{item.bailun_sku}{item.warehouse_code}",
                                week = i,
                                coefficient = avg,
                                update_date = DateTime.Now,
                                formula = "(" + string.Join("+", weekDatas.Select(s=>s.formula)) + " )  /  "+ weekDatas.Count()
                            };


                            data.id = read_conn.QueryFirstOrDefault<int?>(" select id from dc_base_week_coefficient where bailun_sku_warehouse_code=@bailun_sku_warehouse_code and week=@week ", new
                            {
                                bailun_sku_warehouse_code = data.bailun_sku_warehouse_code,
                                week = data.week
                            }) ?? 0;
                            if (data.id > 0)
                            {
                                conn.Update(data);
                            }
                            else
                            {
                                conn.Insert(data);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($" {item.bailun_sku} {item.warehouse_code} 计算异常： "+ex.Message);
                        Console.WriteLine($" {item.bailun_sku} {item.warehouse_code} 计算异常： "+ex.StackTrace);
                    }
                    

                }
                page++;
            }
        }

        public class bailun_sku_dto
        {
            public string bailun_sku { get; set; }
            public string warehouse_code { get; set; }
        }
        public class week_date_dto
        {
            public DateTime date { get; set; }
            public int ordered { get; set; }
        }
        public class week_coefficient_dto
        {
            /// <summary>
            /// 周几
            /// </summary>
            public int week { get; set; }
            // 系数
            public decimal coefficient { get; set; }
            public string formula { get; set; }
        }
    }


}
