using AutoTurnOver.DB;
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using MySql.Data.MySqlClient;
using AutoTurnOver.Models;
using Dapper;

namespace AutoTurnOver.Services
{
    /// <summary>
    /// 采购平均指标计算
    /// </summary>
    public class PurchaseAverageTargetServices
    {
        /// <summary>
        /// 取数基数
        /// </summary>
        public static int _seed_count = 5;

        public static void Calculation()
        {
            MyMySqlConnection conn = connectionHelper._connection;

            var task_log = new dc_task_log
            {
                StartTime = DateTime.Now,
                Type = 1,
                Count = 0
            };

            var taskID = conn.Insert(task_log);
            task_log.ID = taskID.Value;

            try
            {

                // 获取最近2天发送变化的SKU 用于计算
                var chageSkuList = AveragePurchase.GetChangeSku(30);
                foreach (var skuItem in chageSkuList)
                {
                    // 判断是否jit
                    var has_jit = ((skuItem.buyer_name == "张莹霞" || skuItem.buyer_name == "张莹霞1") && skuItem.warehouse_code == "GZBLWH");

                    dc_average_purchase data = new dc_average_purchase()
                    {
                        bailun_sku = skuItem.bailun_sku,
                        supplier_id = skuItem.supplier_id ?? 0,
                        warehouse_code = skuItem.warehouse_code
                    };



                    // 查询相关采购单
                    var purchaseList = AveragePurchase.GetPurchaseList(skuItem, _seed_count).ToList();


                    //查出来的采购单数量
                    var purchase_order_count = purchaseList.Select(s => s.purchase_id).Distinct().Count();

                    //有相关采购单 (要至少有3个单，才算平均数)
                    if (purchaseList != null && purchase_order_count >= 3)
                    {
                        data.supplier_name = purchaseList[0].supplier_name;

                        //计算平均价格
                        data.price = purchaseList.Sum(s => s.price ?? 0) / purchaseList.Count;

                        // 计算平均到货天数
                        // 计算总交期
                        int delivery_days = 0;
                        // 查询相关的到货记录
                        var arrivalList = AveragePurchase.GetArrivalList(purchaseList.Select(s => s.purchase_id), skuItem.bailun_sku);
                        foreach (var itemArrivalList in arrivalList.GroupBy(s => s.purchase_id))
                        {
                            var tempPurchase = purchaseList.SingleOrDefault(s => s.purchase_id == itemArrivalList.Key);

                            if (tempPurchase.pay_type == 1)
                            {
                                delivery_days += (int)Math.Ceiling((itemArrivalList.Min(s => s.update_time) - tempPurchase.pay_time).Value.TotalHours / 24);
                            }
                            else
                            {
                                delivery_days += (int)Math.Ceiling((itemArrivalList.Min(s => s.update_time) - tempPurchase.confirm_time).Value.TotalHours / 24);
                            }
                        }

                        // 平均采购交期
                        var temp_delivery_days = delivery_days / purchaseList.Count;
                        //够5个单才算平均值给系统用
                        // 如果是海外仓暂时不算，否则算出来30几天的平均值，影响计划单交期调整的推进
                        if (purchase_order_count >= 5 && skuItem.hq_type == "国内仓")
                        {
                            data.delivery_days = temp_delivery_days;
                        }
                        else
                        {
                            // 不够5个单不算这个值
                            data.delivery_days = -1;
                        }
                        data.virtual_delivery_days = temp_delivery_days;
                        data.payment_type = purchaseList.Any(s => s.pay_type == 1) ? 1 : 2;

                        if (has_jit)
                        {
                            data.delivery_days = 3;
                            data.payment_type = 2; // 先货后款
                        }

                        //写入数据库
                        AveragePurchase.Save(data, conn);

                        task_log.Count++;
                    }
                    else
                    {
                        // 不够3个单，不该有平均数
                        data.delivery_days = -1;
                        data.supplier_name = "";

                        if (has_jit)
                        {
                            data.delivery_days = 3;
                            data.payment_type = 2; // 先货后款
                        }
                        AveragePurchase.Save(data, conn);
                    }




                }

                task_log.CompleteTime = DateTime.Now;
            }
            catch (Exception ex)
            {
                task_log.ErrorMsg = ex.Message + "--------" + ex.StackTrace;
            }

            conn.Update(task_log);
        }

        public static void CalculationTransfer(string bailun_sku = "")
        {

            MyMySqlConnection conn = connectionHelper._connection;

            var task_log = new dc_task_log
            {
                StartTime = DateTime.Now,
                Type = 2,
                Count = 0
            };

            var taskID = conn.Insert(task_log);
            task_log.ID = taskID.Value;

            try
            {

                // 获取最近2天发送变化的SKU 用于计算
                var chageSkuList = AveragePurchase.GetTransferChangeSku(30, bailun_sku);
                foreach (var skuItem in chageSkuList)
                {
                    if (skuItem.warehouse_code == "MMDUSFBA")
                    {

                    }
                    var base_wa = conn.QueryFirstOrDefault<string>(" select t2.`code` from dc_base_warehouse as t1 left join dc_base_country as t2 on t1.area_name = t2.`name` where warehouse_code=@warehouse_code ", new { skuItem.warehouse_code });

                    var purchaseWList = AveragePurchase.GetPurchaseListByWode(skuItem, _seed_count).ToList();


                    dc_average_warehouse dataWarehouse = new dc_average_warehouse()
                    {
                        bailun_sku = skuItem.bailun_sku,
                        warehouse_code = skuItem.warehouse_code
                    };
                    if (purchaseWList != null && purchaseWList.Count() >= 1)
                    {
                        // 计算平均入库天数
                        // 查询相关的入库记录
                        var inboundList = AveragePurchase.GetInboundList(purchaseWList.Select(s => s.purchase_id), skuItem.bailun_sku);
                        if (inboundList != null && inboundList.Count() >= 1)
                        {
                            dataWarehouse.inbound_days = (int)inboundList.Sum(s => Math.Ceiling((s.update_time - s.arrival_time).TotalHours / 24)) / inboundList.Count();
                        }
                        else
                        {
                            dataWarehouse.inbound_days = 0;
                        }
                    }


                    // 计算平均海运调拨天数
                    // 查询最近5次海运（已完成）记录
                    //var transport1List = AveragePurchase.GetTransportList(skuItem.bailun_sku, skuItem.warehouse_code, "海运", "", _seed_count);
                    //var transport1List = ApiUtility.GetLmsOrderTransfer(new Models.ApiDto.api_lms_order_transfer_sku_requst_dto
                    //{
                    //    bailun_sku = skuItem.bailun_sku,
                    //    warehouse_code = skuItem.warehouse_code,
                    //    orders = 10,
                    //    transfer_type = 2
                    //});
                    //if (transport1List != null && transport1List.Count() >= 1)
                    //{
                    //    dataWarehouse.transfer_type_1_days = (decimal)transport1List.Sum(s => (s.sign_time - s.shipping_time).TotalDays) / transport1List.Count();
                    //    dataWarehouse.transfer_type_1_days = (decimal)transport1List.Sum(s => (s.put_time - s.sign_time).TotalDays) / transport1List.Count();
                    //}


                    // 计算平均空运调拨天数
                    // 查询最近5次空运（已完成）记录
                    //var transport2List = AveragePurchase.GetTransportList(skuItem.bailun_sku, skuItem.warehouse_code, "空运", "", _seed_count);
                    //var transport2List = ApiUtility.GetLmsOrderTransfer(new Models.ApiDto.api_lms_order_transfer_sku_requst_dto
                    //{
                    //    bailun_sku = skuItem.bailun_sku,
                    //    warehouse_code = skuItem.warehouse_code,
                    //    orders = _seed_count,
                    //    transfer_type = 3
                    //});
                    //if (transport2List != null && transport2List.Count() >= 1)
                    //{
                    //    dataWarehouse.transfer_type_2_days = (decimal)transport2List.Sum(s => (s.put_time - s.shipping_time).TotalDays) / transport2List.Count();
                    //}

                    //头程天数
                    // 查询是否有默认渠道
                    var dc_base_head_transport_config_data = conn.QuerySingleOrDefault<dc_base_head_transport_config>(" select * from dc_base_head_transport_config  where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and is_delete=0 ", new
                    {
                        bailun_sku = skuItem.bailun_sku,
                        warehouse_code = skuItem.warehouse_code
                    }) ?? new dc_base_head_transport_config { };
                    //var transport3List = AveragePurchase.GetTransportList(skuItem.bailun_sku, skuItem.warehouse_code, dc_base_head_transport_config_data.head_transport, dc_base_head_transport_config_data.logistics_code, _seed_count);
                    var transfer_type = 0;
                    if (dc_base_head_transport_config_data.head_transport == "陆运")
                    {
                        transfer_type = 1;
                    }
                    else if (dc_base_head_transport_config_data.head_transport == "海运")
                    {
                        transfer_type = 2;
                    }
                    else if (dc_base_head_transport_config_data.head_transport == "空运")
                    {
                        transfer_type = 3;
                    }
                    //var transport3List = ApiUtility.GetLmsOrderTransfer(new Models.ApiDto.api_lms_order_transfer_sku_requst_dto
                    //{
                    //    bailun_sku = skuItem.bailun_sku,
                    //    warehouse_code = skuItem.warehouse_code,
                    //    orders = 10,
                    //    logistics_code = dc_base_head_transport_config_data.logistics_code,
                    //    transfer_type = transfer_type
                    //});

                    var lmsAvg = ApiUtility.GetLmsOrderTransferAvg(new Models.ApiDto.api_lms_order_transfer_sku_requst_dto
                    {
                        //bailun_sku = skuItem.bailun_sku,
                        transfer_type = transfer_type,
                        warehouse_code = skuItem.warehouse_code,
                        logistics_code = dc_base_head_transport_config_data.logistics_code,
                        country = base_wa
                    });

                    if (lmsAvg != null && lmsAvg.Count >= 3)
                    {
                        dataWarehouse.transfer_bale_delivery = 1;
                        dataWarehouse.transfer_delivery = ((lmsAvg.Sum(s => s.avg_sign_days) - lmsAvg.Max(s => s.avg_sign_days) - lmsAvg.Min(s => s.avg_sign_days)) / (lmsAvg.Count - 2));
                    }
                    else if (lmsAvg != null && lmsAvg.Count >= 1)
                    {
                        dataWarehouse.transfer_bale_delivery = 1;
                        dataWarehouse.transfer_delivery = lmsAvg.Select(s => s.avg_sign_days).Average();
                    }

                    if (lmsAvg.Where(s => s.avg_put_days != null).Count() >= 3)
                    {
                        dataWarehouse.abroad_inbound_delivery = ((lmsAvg.Where(s => s.avg_put_days!=null).Sum(s => s.avg_put_days.Value) - lmsAvg.Max(s => s.avg_put_days.Value) - lmsAvg.Min(s => s.avg_put_days.Value)) / (lmsAvg.Count - 2));
                    }
                    else if (lmsAvg.Where(s => s.avg_put_days != null).Count() >= 1)
                    {

                        dataWarehouse.abroad_inbound_delivery = lmsAvg.Where(s => s.avg_put_days != null).Select(s => s.avg_put_days.Value).Average();
                    }
                    else
                    {
                        dataWarehouse.abroad_inbound_delivery = 0;
                    }

                   

                    // 大于两条记录，才计算平均值，否则没有意义
                    //if (transport3List != null && transport3List.Count() >= 2)
                    //{
                    //    dataWarehouse.transfer_bale_delivery = 0;
                    //    dataWarehouse.transfer_delivery = (decimal)transport3List.Sum(s => (s.sign_time - s.shipping_time).TotalDays) / transport3List.Count();
                    //    dataWarehouse.abroad_inbound_delivery = (decimal)transport3List.Sum(s => (s.put_time - s.sign_time).TotalDays) / transport3List.Count();
                    //}

                    AveragePurchase.Save(dataWarehouse, conn);


                }

                task_log.CompleteTime = DateTime.Now;
            }
            catch (Exception ex)
            {
                task_log.ErrorMsg = ex.Message + "--------" + ex.StackTrace;
            }

            conn.Update(task_log);
        }

        public static IEnumerable<dc_average_purchase> GetListByWCode(string sku, string warehouse_code)
        {
            return AveragePurchase.GetListByWCode(sku, warehouse_code);
        }

        public static IEnumerable<dc_average_purchase> GetList(string supplier_ids, string bailun_skus)
        {
            return AveragePurchase.GetList(supplier_ids, bailun_skus);
        }

        public static dc_average_warehouse GetWarehouse(string sku, string warehouse_code)
        {
            return AveragePurchase.GetWarehouse(sku, warehouse_code);
        }



    }


}
