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

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 在库货运费单价
    /// </summary>
    public class dc_base_transfer_freight_dao : connectionHelper
    {
        /// <summary>
        /// 同步百伦调拨操作日志
        /// </summary>
        /// <param name="type">1 = 打包  2 = 交运 3 =确认交运 </param>
        public static void SynchroOrderBailunTransferLog(int type)
        {
            var task_name = $"SynchroOrderBailunTransferLog_{type}_v2";
            var conn = _connection;
            // 查询最后一次成功抓取的记录
            var last_task_synchro_log = conn.QuerySingleOrDefault<task_synchro_log>(" select  * from task_synchro_log where task_name=@task_name and status=1 order by end_time desc limit 1 ", new { task_name = task_name });

            var new_task_synchro_log = new task_synchro_log 
            {
                create_date = DateTime.Now,
                end_time = DateTime.Now,
                status = 0,
                task_name = task_name
            };

            if (last_task_synchro_log != null)
            {
                new_task_synchro_log.start_time = last_task_synchro_log.end_time.AddMinutes(-1);
            }
            else
            {
                new_task_synchro_log.start_time = DateTime.Now.AddYears(-1);
            }

            new_task_synchro_log.id = conn.Insert(new_task_synchro_log) ?? 0;
            int count = 0;

            var thisTime = new_task_synchro_log.start_time;
            var endTime = new_task_synchro_log.end_time;
            while (thisTime.ToDayHome() < endTime.ToDayEnd())
            {
                var datas = ApiUtility.GetOrderBailunTransferLog(thisTime, type);
                foreach (var orderItem in datas)
                {
                    try
                    {
                        var oms_order_data = new order_transfer_status_log
                        {
                            action = orderItem.state,
                            bailun_sku = orderItem.sku,
                            warehouse_code = orderItem.targetWareCode,
                            data_id = orderItem.id,
                            date = orderItem.creationTime,
                            is_delete = orderItem.isDeleted ? 1 : 0,
                            gmt_create_date = DateTime.Now,
                            gmt_update_date = DateTime.Now,
                            system_order_no = orderItem.orderCode,
                            update_date = orderItem.lastModificationTime,
                            count = orderItem.count
                        };

                        count++;

                        var oldData = conn.QuerySingleOrDefault<order_transfer_status_log>(" select * from order_transfer_status_log where data_id=@data_id ", new
                        {
                            data_id = oms_order_data.data_id
                        });
                        if (oldData != null)
                        {
                            oms_order_data.id = oldData.id;
                            oms_order_data.gmt_create_date = oldData.gmt_create_date;
                            conn.Update(oms_order_data);
                        }
                        else
                        {
                            conn.Insert(oms_order_data);
                        }

                        if (oms_order_data.action == (int)order_transfer_status_log_action_enum.入库)
                        {
                            //
                            Calculation(oms_order_data);
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        Console.WriteLine(ex.StackTrace);
                    }


                }

                thisTime = thisTime.AddDays(1);
            }


            new_task_synchro_log.count = count;
            new_task_synchro_log.status = 1;
            conn.Update(new_task_synchro_log);
        }

        /// <summary>
        /// 计算加权平均运费
        /// </summary>
        /// <param name="log_data"></param>
        public static void Calculation(order_transfer_status_log log_data)
        {
            CalculationSingle(log_data.bailun_sku, log_data.warehouse_code);
        }

        /// <summary>
        /// 初始化数据
        /// </summary>
        public static void Init()
        {
            var sql = @"select t1.bailun_sku,t1.warehouse_code,t1.usable_stock from dc_base_stock as t1
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
where t2.hq_type!='国内仓' ";
            var datas = _connection.Query<stock_dto>(sql).ToList();
            foreach (var log_data in datas)
            {
                CalculationSingle(log_data.bailun_sku, log_data.warehouse_code);


            }
        }

        // 计算单个
        public static void CalculationSingle(string bailun_sku,string warehouse_code)
        {
            try
            {
                dc_base_transfer_freight_log data_log = new dc_base_transfer_freight_log
                {
                    action_date = new DateTime(2021, 02, 22),
                    action_log_id = "",
                    bailun_sku = bailun_sku,
                    order_no = "init",
                    update_time = DateTime.Now,
                    warehouse_code = warehouse_code
                };


                var history_avg_list = _connection.Query<transfer_price_dto>(@"
select t2.sku ,t1.targetwareno,t2.deliverycount,t2.storagecount,t1.estimatedcost,t3.deliverycount as 'order_deliverycount' from dc_base_transfer_info as t1
left join dc_base_transfer_info_skus as t2 on t1.dataid = t2.dataid
left join ( select dataid,sum(deliverycount) as 'deliverycount' from dc_base_transfer_info_skus GROUP BY dataid ) as t3 on  t1.dataid = t3.dataid
where t1.isdeleted=0 and t1.stateid not in (5,11,12) 
and t2.deliverycount > t2.storagecount
and t1.isexception = 0
and t1.expectarrivaltime >='1991-01-01' and t2.sku=@sku and t1.targetwareno=@targetwareno and t1.estimatedcost>0  order by t1.creationtime DESC 
", new
                {
                    sku = data_log.bailun_sku,
                    targetwareno = data_log.warehouse_code
                }).AsList();
                // 在途部分的总价
                data_log.freight = history_avg_list.Sum(s => s.estimatedcost / s.order_deliverycount * (s.deliverycount - s.storagecount));
                var sum_quantity = history_avg_list.Sum(s => s.deliverycount - s.storagecount);


                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("bailun_sku", bailun_sku);
                parameters.Add("warehouse_code", warehouse_code);

                // 在库库存分析
                var stock = _connection.QueryFirstOrDefault<int?>(" select usable_stock from dc_base_stock as t1 where t1.bailun_sku=@bailun_sku and t1.warehouse_code=@warehouse_code  ", parameters) ?? 0;
                if (stock > 0)
                {
                    var logs = _connection.Query<order_transfer_status_log>(" select * from order_transfer_status_log as t1 where  t1.bailun_sku=@bailun_sku and t1.warehouse_code=@warehouse_code and is_delete=0 ", parameters).ToList();
                    if (logs != null && logs.Count >= 1)
                    {
                        // 按实际排降序取够库存
                        var stock_orders = new List<transfer_price_dto>();
                        foreach (var item in logs.OrderByDescending(s => s.date))
                        {

                            if (stock > 0)
                            {
                                var use_stock = stock > item.count ? item.count : stock;
                                if (use_stock > 0)
                                {
                                    stock -= use_stock;
                                    // 查询相关调拨单的运费单价
                                    var temp_transfer_price_data = _connection.QueryFirstOrDefault<transfer_price_dto>(@"
select t1.estimatedcost,t3.deliverycount as 'order_deliverycount' from dc_base_transfer_info as t1
left join dc_base_transfer_info_skus as t2 on t1.dataid = t2.dataid
left join ( select dataid,sum(deliverycount) as 'deliverycount' from dc_base_transfer_info_skus GROUP BY dataid ) as t3 on  t1.dataid = t3.dataid
where t1.`code`=@code limit 1
", new { code = item.system_order_no });
                                    stock_orders.Add(new transfer_price_dto { estimatedcost = temp_transfer_price_data.estimatedcost,order_deliverycount  = temp_transfer_price_data.order_deliverycount,deliverycount = use_stock });
                                }



                            }

                        }
                        var stock_freight = stock_orders.Sum(s => s.estimatedcost / s.order_deliverycount * s.deliverycount);
                        var stock_sum_quantity = stock_orders.Sum(s => s.deliverycount);
                        data_log.formula = $" ( 在途总运费 {Math.Round(data_log.freight)} + 在库总运费 {Math.Round(stock_freight)} ) / ( 在途总数量 {sum_quantity} + 在库总数量 {stock_sum_quantity }  )";
                     
                        data_log.freight += stock_freight;
                        sum_quantity += stock_sum_quantity;
                        data_log.freight_unit_price = Math.Round(data_log.freight / sum_quantity, 2);

                    }
                }
                else
                {
                    data_log.formula = $" ( 在途总运费 {Math.Round(data_log.freight)} + 在库总运费 0 ) / ( 在途总数量 {sum_quantity} + 在库总数量 0 )";
                }

                _connection.Insert(data_log);
                var mainData = new dc_base_transfer_freight
                {
                    bailun_sku = data_log.bailun_sku,
                    warehouse_code = data_log.warehouse_code,
                    freight_unit_price = data_log.freight_unit_price,
                    freight = data_log.freight,
                    update_time = DateTime.Now,
                    formula = data_log.formula??""

                };
                mainData.id = _connection.QuerySingleOrDefault<int?>(" select id from dc_base_transfer_freight where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                {
                    bailun_sku = mainData.bailun_sku,
                    warehouse_code = mainData.warehouse_code,
                }) ?? 0;
                if (mainData.id <= 0)
                {
                    _connection.Insert(mainData);

                }
                else
                {
                    _connection.Update(mainData);
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        public class stock_dto
        {
            public string bailun_sku { get; set; }
            public string warehouse_code { get; set; }
            public int usable_stock { get; set; }
        }

        public class transfer_price_dto
        {
            public string sku { get; set; }
            public string targetwareno { get; set; }
            public int deliverycount { get; set; }
            public int storagecount { get; set; }

            /// <summary>
            /// 订单总运费
            /// </summary>
            public decimal estimatedcost { get; set; }

            /// <summary>
            /// 订单总发货数量
            /// </summary>
            public int order_deliverycount { get; set; }
        }
    }
}
