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}_v1";
            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;
            }

            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)
        {
            dc_base_transfer_freight_log data_log = new dc_base_transfer_freight_log
            {
                action_date = log_data.gmt_create_date,
                action_log_id = log_data.data_id.ToString(),
                bailun_sku = log_data.bailun_sku,
                order_no = log_data.system_order_no,
                update_time = DateTime.Now,
                warehouse_code = log_data.warehouse_code
            };

            try
            {
                // 查询该流水是否已经计算过
                var oldId = _connection.QuerySingleOrDefault<int>(" select id from dc_base_transfer_freight_log where action_log_id=@action_log_id limit 1 ", new { action_log_id = data_log.action_log_id });
                if (oldId > 0)
                {
                    return;
                }

                var history_avg_list = _connection.Query<transfer_price_dto>(@"
select t2.sku ,t1.targetwareno,t2.deliverycount,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 t2.sku=@sku and t1.targetwareno=@targetwareno and t1.estimatedcost>0 and t1.creationtime<=@date and t1.stateid=5 and t1.isdeleted=0  order by t1.creationtime DESC
", new
                {
                    sku = data_log.bailun_sku,
                    targetwareno = data_log.warehouse_code,
                    date = data_log.action_date
                }).AsList();
                data_log.freight_unit_price = history_avg_list.Average(s => s.estimatedcost / s.order_deliverycount);

                // 记录到最新运费表
                dc_base_transfer_freight new_data = new dc_base_transfer_freight()
                {
                    bailun_sku = data_log.bailun_sku,
                    freight_unit_price = data_log.freight_unit_price,
                    update_time = DateTime.Now,
                    warehouse_code = data_log.warehouse_code,
                    id = (_connection.QueryFirstOrDefault<int?>(" select id from dc_base_transfer_freight where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                    {
                        bailun_sku = data_log.bailun_sku,
                        warehouse_code = data_log.warehouse_code
                    }) ?? 0)
                };

                if (new_data.id > 0)
                {
                    _connection.Update(new_data);
                }
                else
                {
                    _connection.Insert(new_data);
                }
            }
            catch (Exception ex)
            {
                data_log.remarks = ex.Message + "---" + ex.StackTrace;
            }
            _connection.Insert(data_log);


        }

        /// <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)
            {
                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 = log_data.bailun_sku,
                        order_no = "init",
                        update_time = DateTime.Now,
                        warehouse_code = log_data.warehouse_code
                    };


                    var history_avg_list = _connection.Query<transfer_price_dto>(@"
select t2.sku ,t1.targetwareno,t2.deliverycount,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 t2.sku=@sku and t1.targetwareno=@targetwareno and t1.estimatedcost>0 and t1.stateid=5 and t1.isdeleted=0 order by t1.creationtime DESC 
", new
                    {
                        sku = data_log.bailun_sku,
                        targetwareno = data_log.warehouse_code
                    }).AsList();
                    data_log.freight_unit_price = history_avg_list.Average(s => s.estimatedcost / s.order_deliverycount);
                    data_log.id = _connection.QuerySingleOrDefault<int?>(" select * from dc_base_transfer_freight_log where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new {
                        bailun_sku = data_log.bailun_sku,
                        warehouse_code = data_log.warehouse_code,
                    }) ??0;
                    if (data_log.id <= 0)
                    {
                        _connection.Insert(data_log);
                        _connection.Insert(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,
                            update_time = DateTime.Now
                        });
                    }
                  
                }
                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; }

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

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