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

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 转仓
    /// </summary>
    public class dc_aims_transfer_warehouse_dao : connectionHelper
    {
        public static List<dc_aims_transfer_warehouse_log_dto> LogList(dc_aims_transfer_warehouse_log_search_dto search_data, int offset, int limit, ref int total)
        {
            var sql = @"select t1.*
from dc_aims_transfer_warehouse_log as t1 
left join dc_base_warehouse as t3 on t1.to_warehouse_code = t3.warehouse_code
where 1=1 ";
            var countSql = " select count(1) from dc_aims_transfer_warehouse_log as t1 left join dc_base_warehouse as t3 on t1.to_warehouse_code = t3.warehouse_code where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(search_data.warehousecode))
            {
                sql += " and t1.to_warehouse_code=@warehouse_code ";
                countSql += " and t1.to_warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", search_data.warehousecode);
            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousetype))
            {
                sql += " and t3.hq_type = @hq_type ";
                countSql += " and t3.hq_type = @hq_type ";
                parameters.Add("hq_type", search_data.warehousetype);
            }
            if (search_data.warehousearea > 0)
            {
                sql += " and t3.area_id = @area_id ";
                countSql += " and t3.area_id = @area_id ";
                parameters.Add("area_id", search_data.warehousearea);
            }
            if (search_data.start_date != null)
            {
                sql += " and t1.`create_date`>=@btime ";
                countSql += " and t1.`create_date`>=@btime ";
                parameters.Add("btime", search_data.start_date);
            }
            if (search_data.end_date != null)
            {
                sql += " and t1.`create_date`<=@etime ";
                countSql += " and t1.`create_date`<=@etime ";
                parameters.Add("etime", search_data.end_date);
            }
            if (!string.IsNullOrWhiteSpace(search_data.bailun_sku))
            {
                sql += " and t1.bailun_sku in @bailun_sku ";
                countSql += " and t1.bailun_sku in @bailun_sku ";
                parameters.Add("bailun_sku", search_data.bailun_sku.Split(','));
            }

            sql += " order by t1.id desc  ";
            total = _connection.QueryFirst<int>(countSql, parameters, commandTimeout: 0);
            sql += " limit " + offset + "," + limit;
            var obj = _connection.Query<dc_aims_transfer_warehouse_log_dto>(sql, parameters, buffered: false, commandTimeout: 0);
            return obj.AsList();
        }

        /// <summary>
        /// 转移仓库
        /// </summary>
        /// <param name="input_data"></param>
        public static void TransferWarehouse(dc_aims_transfer_warehouse_input_dto input_data, UserData user)
        {
            using (var conn = MyMySqlConnection._connection)
            {
                conn.Open();
                using (var t = conn.BeginTransaction())
                {
                    var to_warehouse_name = conn.QueryFirstOrDefault<string>(" select warehouse_name from dc_base_warehouse where warehouse_code=@warehouse_code ", new { warehouse_code = input_data.to_warehouse_code });
                    foreach (var item in input_data.turnover_ids)
                    {
                        var turnover_data = conn.QueryFirstOrDefault<AutoTurnOver.Models.dc_auto_turnover>(" select * from dc_auto_turnover where id=@id ", new { id = item });

                        // 添加一条日志
                        conn.Insert(new dc_aims_transfer_warehouse_log
                        {
                            bailun_sku = turnover_data.bailun_sku,
                            create_date = DateTime.Now,
                            create_user = user.UserName,
                            from_warehouse_code = turnover_data.warehouse_code,
                            from_warehouse_name = turnover_data.warehouse_name,
                            to_warehouse_code = input_data.to_warehouse_code,
                            to_warehouse_name = to_warehouse_name,
                            status = (int)dc_aims_transfer_warehouse_log_dto.status_enum.待处理,
                            error_message = "",
                            error_stack_trace = ""
                        }, t);

                    }

                    t.Commit();
                }
            }

        }

        /// <summary>
        /// 执行转仓操作
        /// </summary>
        public static void TransferWarehouseTask()
        {
            List<dc_aims_transfer_warehouse_log> logs = _connection.Query<dc_aims_transfer_warehouse_log>(" select * from dc_aims_transfer_warehouse_log where `status` not in @not_status  ",new {
                not_status = new List<int> { (int)dc_aims_transfer_warehouse_log_dto.status_enum.失败, (int)dc_aims_transfer_warehouse_log_dto.status_enum.完成, (int)dc_aims_transfer_warehouse_log_dto.status_enum.刷新周转数据 }
            }).AsList();
            if (logs != null && logs.Count>=1)
            {
                foreach (var item in logs)
                {
                    TransferWarehouseAction(item);
                }
            }
        }

        /// <summary>
        /// 转仓任务
        /// </summary>
        public static void TransferWarehouseAction(dc_aims_transfer_warehouse_log logData)
        {
            using (var conn = MyMySqlConnection._connection)
            {
                conn.Open();
                using (var t = conn.BeginTransaction())
                {
                    try
                    {
                        var from_turnover_data = conn.QueryFirstOrDefault<AutoTurnOver.Models.dc_auto_turnover>(" select * from dc_auto_turnover where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code  ", new { bailun_sku = logData.bailun_sku, warehouse_code = logData.from_warehouse_code });

                        //检查是否已经存在数据
                        var new_turnover_data_count = conn.QueryFirstOrDefault<int>(" select count(1) from dc_auto_turnover where  warehouse_code=@warehouse_code and bailun_sku=@bailun_sku ", new
                        {
                            warehouse_code = logData.to_warehouse_code,
                            bailun_sku = from_turnover_data.bailun_sku
                        });
                        if (new_turnover_data_count >= 1)
                        {
                            throw new Exception($" {logData.bailun_sku} - {logData.to_warehouse_code} 已存在，请勿重复操作");
                        }


                        logData.status = (int)dc_aims_transfer_warehouse_log_dto.status_enum.转移最新30天销量;
                        _connection.Update(logData);


                        // 查询历史销量
                        var dc_aims_transfer_warehouse_sales = conn.Query<dc_aims_transfer_warehouse_sales>(@"
select 
t1.bailun_sku,
t1.warehouse_code,
CONCAT(t1.bailun_sku,t1.warehouse_code) as 'bailun_sku_warehouse_code',
DATE_FORMAT(create_time,'%Y-%m-%d') as 'date',
DATE_FORMAT(create_time,'%Y-%m-%d') as 'date_str',
sum( t1.bailun_sku_quantity_ordered) as 'sales'
from dc_base_oms_sku_30 as t1
where  t1.bailun_sku=@bailun_sku and t1.warehouse_code=@warehouse_code
  and has_fba_s = 0
    and has_delete = 0
   and has_scalp = 0
  and has_buyer_remark = 0
 and has_platsku_remark = 0
  and has_innersale = 0
and bailun_order_status !='Canceled'
 and platform_order_type != '手工单'
 and t1.create_time>=@date30
 GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d')
",new { bailun_sku = logData.bailun_sku, warehouse_code  = logData.from_warehouse_code, date30  = DateTime.Now.AddDays(-31)}).AsList();

                        if(dc_aims_transfer_warehouse_sales != null && dc_aims_transfer_warehouse_sales.Count >= 1)
                        {
                            foreach (var item in dc_aims_transfer_warehouse_sales)
                            {
                                item.warehouse_code = logData.to_warehouse_code;
                                item.bailun_sku_warehouse_code = $"{logData.bailun_sku}{logData.to_warehouse_code}";
                                item.id = conn.QuerySingleOrDefault<int?>(" select * from dc_aims_transfer_warehouse_sales where bailun_sku_warehouse_code=@bailun_sku_warehouse_code and date_str=@date_str ", new {
                                    bailun_sku_warehouse_code = item.warehouse_code,
                                    date_str = item.date_str
                                }) ?? 0;
                                if (item.id > 0)
                                {
                                    conn.Update(item,t);
                                }
                                else
                                {
                                    conn.Insert(item, t);
                                }
                            }

                            logData.status = (int)dc_aims_transfer_warehouse_log_dto.status_enum.刷新周转数据;
                            _connection.Update(logData);

                            _connection.Execute(" insert into dc_base_stock(`bailun_sku`,`warehouse_code`) values(@bailun_sku,@warehouse_code) ",new { logData.bailun_sku, warehouse_code = logData.to_warehouse_code });

                            _connection.Insert(new dc_base_queue
                            {
                                submit_date = DateTime.Now,
                                error_message = "",
                                consume_date = null,
                                error_stack_trace = "",
                                message = new { bailun_sku = logData.bailun_sku, warehouse_code = logData.to_warehouse_code }.ToJson(),
                                type = "刷新周转表",
                                remarks = "转仓任务"
                            });


                        }
                        else
                        {
                            throw new Exception("未查询到来源仓最近30天有销量");
                        }

                     
                    }
                    catch (Exception ex)
                    {
                        logData.status = (int)dc_aims_transfer_warehouse_log_dto.status_enum.失败;
                        logData.error_message = ex.Message;
                        logData.error_stack_trace = ex.StackTrace;
                        conn.Update(logData,t);
                    }
                   
                    t.Commit();
                }
            }
        }

        /// <summary>
        /// 监听完成情况
        /// </summary>
        public static void MonitorCompleteStatus()
        {
            List<dc_aims_transfer_warehouse_log> logs = _connection.Query<dc_aims_transfer_warehouse_log>(" select * from dc_aims_transfer_warehouse_log where `status` in @not_status  ", new
            {
                not_status = new List<int> { (int)dc_aims_transfer_warehouse_log_dto.status_enum.刷新周转数据 }
            }).AsList();
            if (logs != null && logs.Count >= 1)
            {
                foreach (var item in logs)
                {
                    // 查询该仓库是否已经有数据了
                    var new_turnover_data_count = _connection.QueryFirstOrDefault<int>(" select count(1) from dc_auto_turnover where  warehouse_code=@warehouse_code and bailun_sku=@bailun_sku ", new
                    {
                        warehouse_code = item.to_warehouse_code,
                        bailun_sku = item.bailun_sku
                    });
                    if (new_turnover_data_count >= 1)
                    {
                        item.status = (int)dc_aims_transfer_warehouse_log_dto.status_enum.完成;
                        _connection.Update(item);
                    }
                }
            }
        }


    }

    
}
