using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using Dapper;
using AutoTurnOver.Models.Report;
using AutoTurnOver.Models;
using Bailun.ServiceFabric;

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 独立站自动转海外仓备货
    /// </summary>
    public class lm_dao : connectionHelper
    {
        /// <summary>
        /// 换仓备货
        /// </summary>
        public static void ChangeWarehouseStockUp()
        {
            var abroad_warehouse_list = new List<string> { "AU4PXBL", "US4PXBL", "BL4PXCA" };
            // 找出最近30t天有在lm仓出单的sku
            var bailun_skus = _connection.Query<dc_base_oms_sku>(" select * from dc_base_oms_sku_30 where warehouse_code='GZBLLM' and paid_time>=@btime ", new
            {
                btime = DateTime.Now.AddDays(-30)
            }, commandTimeout: 0).ToList();

            if (bailun_skus != null && bailun_skus.Count >= 1)
            {
                // 找出这些sku是否有在海外仓有库存或者发货过
                var stockDatas = _connection.Query<dc_base_stock_warehouse_dto>($@" select t1.*,t2.area_name from dc_base_stock as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
where t2.hq_type in ('海外仓','第三方仓') and t1.warehouse_code in @warehouse_codes and t1.bailun_sku in @bailun_skus and (t1.usable_stock>0 || t1.shipped_stock>0) ", new
                {
                    bailun_skus = bailun_skus.Select(s => s.bailun_sku).ToList(),
                    warehouse_codes = abroad_warehouse_list
                }, commandTimeout: 0).ToList();
                if (stockDatas != null && stockDatas.Count >= 1)
                {
                    // 把这些国家的单转移到海外仓
                    foreach (var item in stockDatas.GroupBy(s => s.warehouse_code))
                    {
                        var country_code = _connection.QueryFirstOrDefault<string>(" select `code` from dc_base_country where `name`=@name ", new
                        {
                            name = item.FirstOrDefault().area_name
                        });
                        // 把这个国家的单，都转到海外仓
                        _connection.Execute(" update dc_base_oms_sku_30 set warehouse_code=@warehouse_code where bailun_order_id in @bailun_order_ids and bailun_sku in @bailun_skus and receipt_country=@receipt_country ", new
                        {
                            warehouse_code = item.Key,
                            bailun_order_ids = bailun_skus.Select(s => s.bailun_order_id).ToList(),
                            bailun_skus = item.Select(s => s.bailun_sku).ToList(),
                            receipt_country = country_code
                        }, commandTimeout: 0);
                        // 把这个国家的单，都转到海外仓
                        _connection.Execute(" update dc_base_oms_sku set warehouse_code=@warehouse_code where bailun_order_id in @bailun_order_ids and bailun_sku in @bailun_skus and receipt_country=@receipt_country ", new
                        {
                            warehouse_code = item.Key,
                            bailun_order_ids = bailun_skus.Select(s => s.bailun_order_id).ToList(),
                            bailun_skus = item.Select(s => s.bailun_sku).ToList(),
                            receipt_country = country_code
                        }, commandTimeout: 0);

                        // 查询迁移成功的单
                        var successDatas = _connection.Query<dc_base_oms_sku>(" select * from dc_base_oms_sku_30 where  bailun_order_id in @bailun_order_ids and bailun_sku in @bailun_skus and warehouse_code=@warehouse_code  ", new
                        {
                            warehouse_code = item.Key,
                            bailun_order_ids = bailun_skus.Select(s => s.bailun_order_id).ToList(),
                            bailun_skus = item.Select(s => s.bailun_sku).ToList(),
                        }).ToList();
                        if (successDatas != null && successDatas.Count >= 1)
                        {
                            foreach (var itemOrder in successDatas)
                            {
                                dc_base_oms_transfer_warehouse_log log_data = new dc_base_oms_transfer_warehouse_log
                                {
                                    bailun_order_id = itemOrder.bailun_order_id,
                                    bailun_sku = itemOrder.bailun_sku,
                                    create_time = DateTime.Now,
                                    from_warehouse_code = "GZBLLM",
                                    to_warehouse_code = item.Key
                                };
                                log_data.id = _connection.QueryFirstOrDefault<int?>(" select id from dc_base_oms_transfer_warehouse_log where bailun_order_id=@bailun_order_id and bailun_sku=@bailun_sku and to_warehouse_code=@to_warehouse_code and from_warehouse_code=@from_warehouse_code ", new
                                {
                                    bailun_order_id = log_data.bailun_order_id,
                                    bailun_sku = log_data.bailun_sku,
                                    to_warehouse_code = log_data.to_warehouse_code,
                                    from_warehouse_code = log_data.from_warehouse_code,
                                }) ?? 0;
                                if (log_data.id <= 0)
                                {
                                    _connection.Insert(log_data);
                                }
                            }
                        }

                    }
                }
            }


        }

        /// <summary>
        /// 生成调拨计划
        /// </summary>
        public static List<dc_auto_purchase_advise_detailed> GenerateTransferPlan(List<dc_auto_purchase_advise_detailed> original_datas)
        {
            // 清理未执行的调拨推送
            _connection.Execute(" delete from dc_sys_allot_plan where status='待推送' ");
            var skuStocks = new List<dc_base_stock_auto>();
            foreach (var item in original_datas)
            {
                // 查询转仓记录
                var dc_base_oms_transfer_warehouse_log_item = _connection.QueryFirstOrDefault<dc_base_oms_transfer_warehouse_log>(" select * from dc_base_oms_transfer_warehouse_log where from_warehouse_code='GZBLLM' and bailun_sku=@bailun_sku and to_warehouse_code=@to_warehouse_code ", new
                {
                    bailun_sku = item.bailun_sku,
                    to_warehouse_code = item.warehouse_code
                });
                // 有过转仓记录
                if (dc_base_oms_transfer_warehouse_log_item != null)
                {
                    // 查询国内仓的库存和在途
                    if (!skuStocks.Any(s => s.bailun_sku == item.bailun_sku))
                    {
                        var itemSkuStock = _connection.QueryFirstOrDefault<dc_base_stock>(" select * from dc_base_stock where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                        {
                            bailun_sku = item.bailun_sku,
                            warehouse_code = "GZBLLM"
                        });

                        if (itemSkuStock != null)
                        {
                            // 查询这个sku在国内仓是否冗余
                            // 查询国内仓周转表
                            var auto_data = _connection.QueryFirstOrDefault<Models.dc_auto_turnover>(" select * from dc_auto_turnover where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                            {
                                warehouse_code = "GZBLLM",
                                bailun_sku = item.bailun_sku
                            });
                            var autoStock = itemSkuStock.ToJson().ToObject<dc_base_stock_auto>();
                            autoStock.redundant_quantity = 0 - ((int)auto_data.quantity_init_advise);
                            if (autoStock.redundant_quantity <= 0)
                            {
                                continue;
                            }
                            skuStocks.Add(autoStock);
                        }
                    }

                    // 有库存或者在途数据
                    if (skuStocks.Any(s => s.bailun_sku == item.bailun_sku))
                    {
                        var skuStockItem = skuStocks.FirstOrDefault(s => s.bailun_sku == item.bailun_sku);
                        if (skuStockItem.redundant_quantity > 0)
                        {
                            var transferStock = 0;
                            if (skuStockItem.redundant_quantity >= item.quantity_final_advise)
                            {
                                transferStock = item.quantity_final_advise;
                                skuStockItem.redundant_quantity -= transferStock;
                                item.quantity_final_advise = 0;
                                item.remarks = "由于lm的国内仓有冗余库存，等国内仓调就行了";
                            }
                            else
                            {
                                transferStock = skuStockItem.redundant_quantity;
                                item.quantity_final_advise -= transferStock;
                                skuStockItem.redundant_quantity = 0;
                                item.remarks = "由于lm的国内仓有冗余库存，从国内仓调一部分";
                            }

                            _connection.Insert(new dc_sys_allot_plan
                            {
                                bailun_sku = item.bailun_sku,
                                warehouse_code_from = "GZBLLM",
                                create_time = DateTime.Now,
                                push_time = null,
                                question = transferStock,
                                status = "待推送",
                                trans_sys_no = "",
                                warehouse_code_to = item.warehouse_code,
                            });
                        }

                    }
                }


            }

            return original_datas;

        }

        /// <summary>
        /// 推送调拨计划
        /// </summary>
        public static void PustTransferPlan()
        {
            // 查询所有待推送的计划
            var plans = _connection.Query<dc_sys_allot_plan>($@" select t1.* from dc_sys_allot_plan as t1 
left join dc_base_stock as t2  on t1.bailun_sku  =t2.bailun_sku and t1.warehouse_code_from = t2.warehouse_code
where t1.`status`='待推送' and t2.usable_stock>=t1.question ").ToList();
            if(plans!=null && plans.Count >= 1)
            {
                var groupList = plans.GroupBy(s => new { s.warehouse_code_from, s.warehouse_code_to }).ToList();
                foreach (var item in groupList)
                {
                    try
                    {
                        ApiUtility.AimsCreateAllotSchedule(new Models.ApiDto.AimsCreateAllotScheduleRequestDto { 
                        TargetWareNo = item.Key.warehouse_code_to,
                        SourceWareNo = item.Key.warehouse_code_from
                        });
                        _connection.Execute(" update dc_sys_allot_plan set status='已推送' where id=@ids ", new
                        {
                            ids = item.Select(s => s.id).ToList()
                        });
                    }
                    catch (Exception ex)
                    {
                       
                        _connection.Execute(" update dc_sys_allot_plan set status='推送失败',error_stack_trace=@error_stack_trace ,error_message=@error_message where id=@ids ", new {
                            ids = item.Select(s=>s.id).ToList(),
                            error_message = ex.Message,
                            error_stack_trace = ex.StackTrace
                        });
                    }
                }
            }
        }
    }
}
