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

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 子母sku配置
    /// </summary>
    public class dc_base_mother_sku_dao : connectionHelper
    {
        /// <summary>
        /// 分页查询母sku
        /// </summary>
        /// <param name="search"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<dc_base_mother_sku_dto> Page(dc_base_mother_sku_search_dto search, int offset, int limit, ref int total)
        {
            var sql = " select t1.*,t2.warehouse_name from dc_base_mother_sku as t1 left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code where 1=1 ";
            var sqlCount = " select count(1) from dc_base_mother_sku as t1 left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code where 1=1  ";
            DynamicParameters parameters = new DynamicParameters();
            if (search != null)
            {
                if (!string.IsNullOrWhiteSpace(search.sku))
                {
                    sql += " and t1.sku =@sku ";
                    sqlCount += " and t1.sku =@sku ";
                    parameters.Add("sku", search.sku);
                }

                if (!string.IsNullOrWhiteSpace(search.warehousecode))
                {
                    sql += " and t1.warehouse_code =@warehousecode ";
                    sqlCount += " and t1.warehouse_code =@warehousecode ";
                    parameters.Add("warehousecode", search.warehousecode);
                }

                if (!string.IsNullOrWhiteSpace(search.warehousetype))
                {
                    sql += " and t2.hq_type = @hq_type ";
                    sqlCount += " and t2.hq_type = @hq_type ";
                    parameters.Add("hq_type", search.warehousetype);
                }
                if (search.warehousearea > 0)
                {
                    sql += " and t2.area_id = @area_id ";
                    sqlCount += " and t2.area_id = @area_id ";
                    parameters.Add("area_id", search.warehousearea);
                }

                if (!string.IsNullOrWhiteSpace(search.bailun_sku))
                {
                    sql += " and EXISTS ( select * from dc_base_mother_sku_map as t_map where t1.sku_warehouse_code = t_map.mother_sku_warehouse_code and t_map.bailun_sku=@bailun_sku ) ";
                    sqlCount += " and EXISTS ( select * from dc_base_mother_sku_map as t_map where t1.sku_warehouse_code = t_map.mother_sku_warehouse_code and t_map.bailun_sku=@bailun_sku ) ";
                    parameters.Add("bailun_sku", search.bailun_sku);
                }
            }

            total = _connection.QueryFirstOrDefault<int>(sqlCount, parameters);

            sql += $" limit {offset},{limit} ";
            var datas = _connection.Query<dc_base_mother_sku_dto>(sql, parameters).AsList();
            if (datas != null && datas.Count >= 1)
            {
                var map_datas = _connection.Query<dc_base_mother_sku_map_dto>(@"  select t1.*,t2.warehouse_name,ifnull(t3.stock,0) as 'storage_capacity' from dc_base_mother_sku_map as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code  = t2.warehouse_code 
left join dc_base_storage_capacity as t3 on t1.bailun_sku_warehouse_code = t3.bailun_sku_warehouse_code
where t1.mother_sku_warehouse_code in @mother_sku_warehouse_codes  ", new
                {
                    mother_sku_warehouse_codes = datas.Select(s => s.sku_warehouse_code).ToList()
                }).ToList();
                foreach (var item in datas)
                {
                    item.maps = map_datas.Where(s => s.mother_sku_warehouse_code == item.sku_warehouse_code).ToList();
                }
            }
            return datas;
        }

        /// <summary>
        /// 分页查询母sku
        /// </summary>
        /// <param name="search"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<dc_base_mother_sku_log_dto> LogPage(dc_base_mother_sku_log_search_dto search, int offset, int limit, ref int total)
        {
            var sql = " select t1.*,t2.warehouse_name from dc_base_mother_sku_log as t1 left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code where 1=1 ";
            var sqlCount = " select count(1) from dc_base_mother_sku_log as t1 left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code where 1=1  ";
            DynamicParameters parameters = new DynamicParameters();
            if (search != null)
            {
                if (!string.IsNullOrWhiteSpace(search.sku))
                {
                    sql += " and t1.sku =@sku ";
                    sqlCount += " and t1.sku =@sku ";
                    parameters.Add("sku", search.sku);
                }

                if (!string.IsNullOrWhiteSpace(search.warehousecode))
                {
                    sql += " and t1.warehouse_code =@warehousecode ";
                    sqlCount += " and t1.warehouse_code =@warehousecode ";
                    parameters.Add("warehousecode", search.warehousecode);
                }
            }

            total = _connection.QueryFirstOrDefault<int>(sqlCount, parameters);
            sql += " order by t1.id desc ";
            sql += $" limit {offset},{limit} ";
            var datas = _connection.Query<dc_base_mother_sku_log_dto>(sql, parameters).AsList();
            return datas;
        }


        /// <summary>
        /// 保存数据
        /// </summary>
        public static int Save(dc_base_mother_sku data, UserData user)
        {
            var id = 0;
            data.sku = ("M-" + data.sku);
            data.sku_warehouse_code = $"{data.sku}{data.warehouse_code}";
            if (data.id <= 0)
            {
                // 查询是否存在
                var oldData = _connection.QueryFirstOrDefault(" select * from dc_base_mother_sku where sku=@sku ", new { sku = data.sku });
                if (oldData != null)
                {
                    return oldData.id;
                }
                id = _connection.Insert(data) ?? 0;

                _connection.Insert(new dc_base_mother_sku_log
                {
                    action = "新增母sku资料",
                    create_user = user.UserName,
                    create_date = DateTime.Now,
                    sku_warehouse_code = data.sku_warehouse_code,
                    warehouse_code = data.warehouse_code,
                    sku = data.sku
                });

                //默认开启库容监控
                // 添加库容监控
                _connection.Insert<dc_base_storage_capacity_config>(new dc_base_storage_capacity_config
                {
                    bailun_sku = data.sku,
                    bailun_sku_warehouse_code = data.sku_warehouse_code,
                    status = 1,
                    warehouse_code = data.warehouse_code
                });
            }
            else
            {

                throw new Exception("不支持修改");
                //_connection.Update(data);
            }

            return id;

        }


        /// <summary>
        /// 修改状态
        /// </summary>
        /// <param name="data"></param>
        /// <param name="user"></param>
        public static void UpdateStatus(update_status_input_dto data, UserData user)
        {
            var oldData = _connection.QueryFirstOrDefault<dc_base_mother_sku>(" select * from dc_base_mother_sku where id=@id ", new { id = data.id });
            if (oldData == null)
            {
                throw new Exception("数据不存在");
            }
            if (oldData.status == data.status)
            {
                throw new Exception("状态未发送变化，无需修改");
            }
            oldData.status = data.status;

            _connection.Update(oldData);

            _connection.Insert(new dc_base_mother_sku_log
            {
                action = (oldData.status == -1 ? "停用" : "启用"),
                create_date = DateTime.Now,
                create_user = user.UserName,
                sku = oldData.sku,
                sku_warehouse_code = oldData.sku_warehouse_code,
                warehouse_code = oldData.warehouse_code
            });

        }


        /// <summary>
        /// 移除子sku
        /// </summary>
        /// <param name="input_data"></param>
        public static void RemoveMap(remove_map_input_dto input_data, UserData user)
        {
            if(input_data.map_ids==null || input_data.map_ids.Count <= 0)
            {
                return;
            }
            var conn = MyMySqlConnection._connection;
            conn.Open();
            using (var t = conn.BeginTransaction())
            {
                try
                {
                    foreach (var item in input_data.map_ids)
                    {
                        var map_data = conn.QueryFirstOrDefault<dc_base_mother_sku_map_dto>(" select t1.*,t2.warehouse_name from dc_base_mother_sku_map as t1 left join dc_base_warehouse as t2 on t1.warehouse_code  = t2.warehouse_code where t1.id=@id ", new { id = item });
                        conn.Insert(new dc_base_mother_sku_log
                        {
                            action = $"移除子sku 【{map_data.bailun_sku}】  【{map_data.warehouse_code}】 【{map_data.warehouse_name}】 ",
                            warehouse_code = map_data.mother_sku_warehouse_code,
                            create_date = DateTime.Now,
                            create_user = user.UserName,
                            sku = map_data.mother_sku,
                            sku_warehouse_code = map_data.mother_sku_warehouse_code
                        });

                        conn.Execute(" delete from dc_base_mother_sku_map where id=@id ", new { id = item });

                        ResetStorageCapacity(map_data.mother_sku, map_data.mother_warehouse_code, user);
                    }

                    t.Commit();
                }
                catch (Exception ex)
                {
                    t.Rollback();
                }



            }

        }

        /// <summary>
        /// 设置主sku
        /// </summary>
        /// <param name="input_data"></param>
        public static void SetMainMap(remove_map_input_dto input_data, UserData user)
        {

            var conn = MyMySqlConnection._connection;
            conn.Open();
            using (var t = conn.BeginTransaction())
            {
                try
                {
                    foreach (var item in input_data.map_ids)
                    {
                        var map_data = conn.QueryFirstOrDefault<dc_base_mother_sku_map_dto>(" select t1.*,t2.warehouse_name from dc_base_mother_sku_map as t1 left join dc_base_warehouse as t2 on t1.warehouse_code  = t2.warehouse_code where t1.id=@id ", new { id = item });
                        conn.Insert(new dc_base_mother_sku_log
                        {
                            action = $"设置主sku 【{map_data.bailun_sku}】  【{map_data.warehouse_code}】 【{map_data.warehouse_name}】 ",
                            warehouse_code = map_data.mother_sku_warehouse_code,
                            create_date = DateTime.Now,
                            create_user = user.UserName,
                            sku = map_data.mother_sku,
                            sku_warehouse_code = map_data.mother_sku_warehouse_code
                        });

                        conn.Execute(" update  dc_base_mother_sku_map set is_main=0 where mother_sku_warehouse_code=@mother_sku_warehouse_code ", new { mother_sku_warehouse_code = map_data.mother_sku_warehouse_code });
                        conn.Execute(" update  dc_base_mother_sku_map set is_main=1 where id=@id ", new { id = item });

                        //ResetStorageCapacity(map_data.mother_sku, map_data.mother_warehouse_code, user);
                    }

                    t.Commit();
                }
                catch (Exception ex)
                {
                    t.Rollback();
                }



            }

        }

        /// <summary>
        /// 批量设置母sku
        /// </summary>
        /// <param name="input_data"></param>
        public static void BatchSet(batch_set_input_dto input_data, UserData user)
        {
            var mother_data = _connection.QueryFirstOrDefault<dc_base_mother_sku>(" select * from dc_base_mother_sku where id=@id ", new { id = input_data.mother_id });
            var conn = MyMySqlConnection._connection;
            conn.Open();
            using (var t = conn.BeginTransaction())
            {
                try
                {

                    foreach (var item in input_data.ids)
                    {
                        var trunData = conn.QueryFirstOrDefault<AutoTurnOver.Models.dc_auto_turnover>(" select * from dc_auto_turnover where id=@id ", new { id = item });
                        // 查询是否有旧记录
                        var oldLog = conn.QueryFirstOrDefault<dc_base_mother_sku_map>(" select * from dc_base_mother_sku_map where bailun_sku_warehouse_code=@bailun_sku_warehouse_code ", new
                        {
                            bailun_sku_warehouse_code = $"{trunData.bailun_sku}{trunData.warehouse_code}"
                        });
                        if (oldLog != null)
                        {
                            // 跳过
                            //// 先移除
                            //conn.Insert(new dc_base_mother_sku_log
                            //{
                            //    action = $"移除子sku 【{oldLog.bailun_sku}】【{oldLog.warehouse_code}】，设置到新的sku 【{mother_data.sku}】",
                            //    sku_warehouse_code = oldLog.mother_sku_warehouse_code,
                            //    create_date = DateTime.Now,
                            //    create_user = user.UserName,
                            //    sku = oldLog.mother_sku,
                            //    warehouse_code = oldLog.mother_warehouse_code
                            //});

                            //conn.Execute(" delete from dc_base_mother_sku_map where id=@id ", new { id = oldLog.id });
                        }
                        else
                        {
                            // 添加日志
                            conn.Insert(new dc_base_mother_sku_log
                            {
                                action = $"新增子sku  【{trunData.bailun_sku}】【{trunData.warehouse_code}】",
                                sku_warehouse_code = mother_data.sku_warehouse_code,
                                create_date = DateTime.Now,
                                create_user = user.UserName,
                                sku = mother_data.sku,
                                warehouse_code = mother_data.warehouse_code
                            });
                            conn.Insert(new dc_base_mother_sku_map
                            {
                                bailun_sku = trunData.bailun_sku,
                                warehouse_code = trunData.warehouse_code,
                                mother_sku = mother_data.sku,
                                mother_sku_warehouse_code = mother_data.sku_warehouse_code,
                                mother_warehouse_code = mother_data.warehouse_code,
                                bailun_sku_warehouse_code = $"{trunData.bailun_sku}{trunData.warehouse_code}",
                                is_main = input_data.is_main
                            });

                            ResetTurnOver(mother_data.sku, mother_data.warehouse_code);
                        }

                    }

                    t.Commit();
                }
                catch (Exception ex)
                {
                    t.Rollback();
                    throw;
                }

            }
            ResetStorageCapacity(mother_data.sku, mother_data.warehouse_code, user);
        }

        /// <summary>
        /// 搜索母sku
        /// </summary>
        /// <returns></returns>
        public static List<dc_base_mother_sku_dto> SearchSku(string name)
        {
            return _connection.Query<dc_base_mother_sku_dto>(" select  t1.*,t2.warehouse_name from dc_base_mother_sku as t1 left join dc_base_warehouse as t2 on t1.warehouse_code=t2.warehouse_code where t1.sku like @name ", new { name = $"%{name}%" }).ToList();
        }

        /// <summary>
        /// 刷新库容
        /// </summary>
        public static void ResetStorageCapacity(string mother_sku, string warehouse_code, UserData user)
        {
            var mother_sku_warehouse_code = $"{mother_sku}{warehouse_code}";
            // 子sku
            var skus = _connection.Query<dc_base_mother_sku_map>(" select * from dc_base_mother_sku_map where mother_sku_warehouse_code=@mother_sku_warehouse_code ", new
            {
                mother_sku_warehouse_code = mother_sku_warehouse_code
            }).ToList();
            var storage_capacity = 0;
            if (skus != null && skus.Count >= 1)
            {
                storage_capacity = _connection.QueryFirstOrDefault<int?>(" select sum(stock) from dc_base_storage_capacity where bailun_sku_warehouse_code in @bailun_sku_warehouse_codes ", new
                {
                    bailun_sku_warehouse_codes = skus.Select(s => s.bailun_sku_warehouse_code).ToList()
                }) ?? 0;
            }
            else
            {
                storage_capacity = 0;
            }

            _connection.Execute(" update dc_base_mother_sku set storage_capacity=@storage_capacity where sku_warehouse_code=@sku_warehouse_code ", new
            {
                storage_capacity = storage_capacity,
                sku_warehouse_code = mother_sku_warehouse_code
            });

            // 新增日志
            _connection.Insert(new dc_base_mother_sku_log
            {
                action = $"更新合并库容 {storage_capacity}",
                create_date = DateTime.Now,
                create_user = user.UserName,
                sku = mother_sku,
                sku_warehouse_code = mother_sku_warehouse_code,
                warehouse_code = warehouse_code
            });
        }

        /// <summary>
        /// 更新合并库容
        /// </summary>
        public static void ResetStorageCapacityByMapSku(string bailun_sku, string warehouse_code, UserData user)
        {
            // 查询是否有母sku
            var mother_sku_map_data = _connection.QueryFirstOrDefault<dc_base_mother_sku_map>(" select * from dc_base_mother_sku_map where bailun_sku_warehouse_code=@bailun_sku_warehouse_code ", new
            {
                bailun_sku_warehouse_code = $"{bailun_sku}{warehouse_code}"
            });
            if (mother_sku_map_data != null)
            {
                ResetStorageCapacity(mother_sku_map_data.mother_sku, mother_sku_map_data.mother_warehouse_code, user);
            }
        }

        /// <summary>
        /// 刷新周转表
        /// </summary>
        public static void ResetTurnOver(string mother_sku, string monther_warehouse_code)
        {
            var conn = _connection;
            // 查询是否是母sku
            var sku_warehouse_code = $"{mother_sku}{monther_warehouse_code}";
            var monther_sku_data = conn.QueryFirstOrDefault<dc_base_mother_sku>(" select * from dc_base_mother_sku where sku_warehouse_code=@sku_warehouse_code ", new { sku_warehouse_code = sku_warehouse_code });
            if (monther_sku_data == null) return;

            // 查询所有子sku
            var map_sku_datas = conn.Query<dc_base_mother_sku_map>(" select * from dc_base_mother_sku_map where mother_sku_warehouse_code=@mother_sku_warehouse_code ", new { mother_sku_warehouse_code = sku_warehouse_code }).ToList();

            if (map_sku_datas == null || map_sku_datas.Count <= 0)
            {
                return;
            }

            // 查询所有子sku的周转数据
            List<Models.dc_auto_turnover> turn_datas = conn.Query<Models.dc_auto_turnover>(" select * from  dc_auto_turnover where bailun_sku_warehouse_code in @bailun_sku_warehouse_codes ", new
            {
                bailun_sku_warehouse_codes = map_sku_datas.Select(s => s.bailun_sku_warehouse_code).ToList()
            }).ToList();

            var turn_first_data = turn_datas.OrderByDescending(s=>s.daily_weighted_sales).FirstOrDefault();
            var warehouse_data = conn.QueryFirstOrDefault<dc_base_warehouse>(" select * from dc_base_warehouse where warehouse_code=@warehouse_code ", new { warehouse_code = monther_warehouse_code });
            var mother_turn_data = new Models.dc_auto_turnover()
            {
                abroad_inbound_config_delivery = turn_first_data.abroad_inbound_config_delivery,
                hq_type = warehouse_data.hq_type,
                abroad_inbound_delivery = turn_first_data.abroad_inbound_delivery,
                abroad_inbound_delivery_source = turn_first_data.abroad_inbound_delivery_source,
                adv_uid = "",
                auto_replace_logistics_task_id = turn_first_data.auto_replace_logistics_task_id,
                average_overdue = turn_first_data.average_overdue,
                average_supplier_delivery = turn_first_data.average_supplier_delivery,
                bailun_category_id = turn_first_data.bailun_category_id,
                bailun_sku = mother_sku,
                bailun_sku_warehouse_code = sku_warehouse_code,
                buyer_name = turn_first_data.buyer_name,
                daily_weighted_sales = turn_datas.Sum(s => s.daily_weighted_sales),
                dc_auto_sales_forecast_config_id = turn_first_data.dc_auto_sales_forecast_config_id,
                dc_auto_sales_forecast_stage_config_id = turn_first_data.dc_auto_sales_forecast_stage_config_id,
                dc_auto_sales_forecast_title = turn_first_data.dc_auto_sales_forecast_title,
                first_order_bailun_order_id = turn_first_data.first_order_bailun_order_id,
                first_order_date = turn_datas.Min(s => s.first_order_date),
                forecast_fourteenday_sales = turn_datas.Sum(s => s.forecast_fourteenday_sales),
                forecast_oneday_sales = turn_datas.Sum(s => s.forecast_oneday_sales),
                forecast_sevenday_sales = turn_datas.Sum(s => s.forecast_sevenday_sales),
                forecast_turnoverday_sales = turn_datas.Sum(s => s.forecast_turnoverday_sales),
                gmt_create = DateTime.Now,
                gmt_modified = DateTime.Now,
                head_transport_logistics_code = turn_first_data.head_transport_logistics_code,
                head_transport_type = turn_first_data.head_transport_type,
                history_fourteenday_sales = turn_datas.Sum(s => s.history_fourteenday_sales),
                history_fourteenday_sales_aliexpress = turn_datas.Sum(s => s.history_fourteenday_sales_aliexpress),
                history_fourteenday_sales_amazon = turn_datas.Sum(s => s.history_fourteenday_sales_amazon),
                history_fourteenday_sales_ebay = turn_datas.Sum(s => s.history_fourteenday_sales_ebay),
                history_sevenday_sales = turn_datas.Sum(s => s.history_sevenday_sales),
                history_sevenday_sales_aliexpress = turn_datas.Sum(s => s.history_sevenday_sales_aliexpress),
                history_sevenday_sales_amazon = turn_datas.Sum(s => s.history_sevenday_sales_amazon),
                history_sevenday_sales_ebay = turn_datas.Sum(s => s.history_sevenday_sales_ebay),
                history_thirtyday_sales = turn_datas.Sum(s => s.history_thirtyday_sales),
                history_thirtyday_sales_aliexpress = turn_datas.Sum(s => s.history_thirtyday_sales_aliexpress),
                history_thirtyday_sales_amazon = turn_datas.Sum(s => s.history_thirtyday_sales_amazon),
                history_thirtyday_sales_ebay = turn_datas.Sum(s => s.history_thirtyday_sales_ebay),
                inspection_config_delivery = turn_first_data.inspection_config_delivery,
                inspection_delivery = turn_first_data.inspection_delivery,
                inventory_available_days = turn_datas.Min(s => s.inventory_available_days),
                is_modify_quantity_init_advise = null,
                is_mother_sku = 1,
                is_out_stock = turn_datas.Max(s => s.is_out_stock),
                orders_avg = turn_datas.Sum(s => s.orders_avg),
                original_quantity_init_advise = turn_datas.Sum(s => s.original_quantity_init_advise),
                payment_before_delivery = turn_first_data.payment_before_delivery,
                product_inner_code = turn_first_data.product_inner_code,
                product_code = turn_first_data.product_code,
                promotions = turn_first_data.promotions,
                purchase_advice_id = "",
                purchase_create_order_days = turn_first_data.purchase_create_order_days,
                quantity_begin_advise = turn_datas.Sum(s => s.quantity_begin_advise),
                quantity_final_advise = turn_datas.Sum(s => s.quantity_final_advise),
                quantity_inbound = turn_datas.Sum(s => s.quantity_inbound),
                quantity_init_advise = turn_datas.Sum(s => s.quantity_init_advise),
                quantity_inventory = turn_datas.Sum(s => s.quantity_inventory),
                quantity_minimum_order = turn_first_data.quantity_minimum_order,
                quantity_out_stock = turn_datas.Sum(s => s.quantity_out_stock),
                quantity_promotion = 0,
                quantity_purchase = turn_datas.Sum(s => s.quantity_purchase),
                quantity_safe_inventory = turn_datas.Sum(s => s.quantity_safe_inventory),
                quantity_transfer = turn_datas.Sum(s => s.quantity_transfer),
                remark = "",
                sales_upper_limit = turn_datas.Sum(s => s.sales_upper_limit),
                sku_title = turn_first_data.sku_title,
                status = turn_datas.Max(s => s.status),
                stock_up_days = turn_first_data.stock_up_days,
                stock_up_sales = turn_datas.Sum(s => s.stock_up_sales),
                supplementary_sales = turn_datas.Sum(s => s.supplementary_sales),
                suppliers_name = turn_first_data.suppliers_name,
                warehouse_name = warehouse_data.warehouse_name,
                warehouse_code = warehouse_data.warehouse_code,
                turnover_sales = turn_datas.Sum(s => s.turnover_sales),
                turnover_inbound = turn_datas.Sum(s => s.turnover_inbound),
                turnover_days = turn_first_data.turnover_days,
                transfer_sail_days = turn_first_data.transfer_sail_days,
                transfer_order_days = turn_first_data.transfer_order_days,
                transfer_delivery_source = turn_first_data.transfer_delivery_source,
                transfer_delivery = turn_first_data.transfer_delivery,
                transfer_config_delivery = turn_first_data.transfer_config_delivery,
                transfer_bale_delivery_source = turn_first_data.transfer_bale_delivery_source,
                transfer_bale_delivery = turn_first_data.transfer_bale_delivery,
                transfer_bale_config_delivery = turn_first_data.transfer_bale_config_delivery,
                tag_id = turn_first_data.tag_id,
                supplier_delivery = turn_first_data.supplier_delivery,
                sales_details_formula = turn_first_data.sales_details_formula,
                advised_details = MergeList(turn_datas.Select(s => s.advised_details.ToObj<List<decimal>>()).ToList()).ToJson(),
                inbound_details = MergeList(turn_datas.Select(s => s.inbound_details.ToObj<List<decimal>>()).ToList()).ToJson(),
                inventory_details = MergeList(turn_datas.Select(s => s.inventory_details.ToObj<List<decimal>>()).ToList()).ToJson(),
                sales_explain_details = turn_first_data.sales_explain_details,
                sales_details = MergeList(turn_datas.Select(s => s.sales_details.ToObj<List<decimal>>()).ToList()).ToJson(),
                short_supply_details = MergeList(turn_datas.Select(s => s.short_supply_details.ToObj<List<decimal>>()).ToList()).ToJson()
            };
            mother_turn_data.id = conn.QueryFirstOrDefault<int?>(" select id from dc_auto_turnover where bailun_sku_warehouse_code=@bailun_sku_warehouse_code ", new { bailun_sku_warehouse_code = sku_warehouse_code }) ?? 0;
            if (mother_turn_data.id > 0)
            {
                conn.Update(mother_turn_data);
            }
            else
            {
                conn.Insert(mother_turn_data);
            }


            var dc_auto_sales_list = new List<dc_auto_sales>();
            foreach (var item in turn_datas)
            {
                var item_dc_auto_sales = conn.QuerySingleOrDefault<dc_auto_sales>(" select * from dc_auto_sales where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                {
                    bailun_sku = item.bailun_sku,
                    warehouse_code = item.warehouse_code
                });
                dc_auto_sales_list.Add(item_dc_auto_sales);
            }
            var first_auto_sales_data = dc_auto_sales_list.FirstOrDefault();
            var mother_dc_auto_sales = new dc_auto_sales
            {
                average_sales = dc_auto_sales_list.Sum(s => s.average_sales),
                bailun_sku = mother_turn_data.bailun_sku,
                b_variable = first_auto_sales_data.b_variable,
                category_id = first_auto_sales_data.category_id,
                details = MergeList(dc_auto_sales_list.Select(s => s.details.ToObj<List<decimal>>()).ToList()).ToJson(),
                eightday_sales = dc_auto_sales_list.Sum(s => s.eightday_sales),
                eighteenday_sales = dc_auto_sales_list.Sum(s => s.eighteenday_sales),
                elevenday_sales = dc_auto_sales_list.Sum(s => s.elevenday_sales),
                fifteenday_sales = dc_auto_sales_list.Sum(s => s.fifteenday_sales),
                fit_forecast_formula = first_auto_sales_data.fit_forecast_formula,
                fiveday_sales = dc_auto_sales_list.Sum(s => s.fiveday_sales),
                forecast_eightday_sales = dc_auto_sales_list.Sum(s => s.forecast_eightday_sales),
                forecast_eighteenday_sales = dc_auto_sales_list.Sum(s => s.forecast_eighteenday_sales),
                forecast_elevenday_sales = dc_auto_sales_list.Sum(s => s.forecast_elevenday_sales),
                forecast_fifteenday_sales = dc_auto_sales_list.Sum(s => s.forecast_fifteenday_sales),
                forecast_fiveday_sales = dc_auto_sales_list.Sum(s => s.forecast_fiveday_sales),
                forecast_fourteenday_sales = dc_auto_sales_list.Sum(s => s.forecast_fourteenday_sales),
                forecast_fourthday_sales = dc_auto_sales_list.Sum(s => s.forecast_fourthday_sales),
                forecast_nineday_sales = dc_auto_sales_list.Sum(s => s.forecast_nineday_sales),
                forecast_nineteenday_sales = dc_auto_sales_list.Sum(s => s.forecast_nineteenday_sales),
                forecast_oneday_sales = dc_auto_sales_list.Sum(s => s.forecast_oneday_sales),
                forecast_sevenday_sales = dc_auto_sales_list.Sum(s => s.forecast_sevenday_sales),
                forecast_seventeenday_sales = dc_auto_sales_list.Sum(s => s.forecast_seventeenday_sales),
                forecast_sixday_sales = dc_auto_sales_list.Sum(s => s.forecast_sixday_sales),
                forecast_sixteenday_sales = dc_auto_sales_list.Sum(s => s.forecast_sixteenday_sales),
                forecast_tenday_sales = dc_auto_sales_list.Sum(s => s.forecast_tenday_sales),
                forecast_thirtyday_sales = dc_auto_sales_list.Sum(s => s.forecast_thirtyday_sales),
                forecast_threeday_sales = dc_auto_sales_list.Sum(s => s.forecast_threeday_sales),
                forecast_thridteenday_sales = dc_auto_sales_list.Sum(s => s.forecast_thridteenday_sales),
                forecast_today_sales = dc_auto_sales_list.Sum(s => s.forecast_today_sales),
                forecast_twelveday_sales = dc_auto_sales_list.Sum(s => s.forecast_twelveday_sales),
                forecast_twentyday_sales = dc_auto_sales_list.Sum(s => s.forecast_twentyday_sales),
                forecast_twenty_eightday_sales = dc_auto_sales_list.Sum(s => s.forecast_twenty_eightday_sales),
                forecast_twenty_fiveday_sales = dc_auto_sales_list.Sum(s => s.forecast_twenty_fiveday_sales),
                forecast_twenty_fourthday_sales = dc_auto_sales_list.Sum(s => s.forecast_twenty_fourthday_sales),
                forecast_twenty_nineday_sales = dc_auto_sales_list.Sum(s => s.forecast_twenty_nineday_sales),
                forecast_twenty_oneday_sales = dc_auto_sales_list.Sum(s => s.forecast_twenty_oneday_sales),
                forecast_twenty_seveneday_sales = dc_auto_sales_list.Sum(s => s.forecast_twenty_seveneday_sales),
                forecast_twenty_sixday_sales = dc_auto_sales_list.Sum(s => s.forecast_twenty_sixday_sales),
                forecast_twenty_threeday_sales = dc_auto_sales_list.Sum(s => s.forecast_twenty_threeday_sales),
                forecast_twenty_twoday_sales = dc_auto_sales_list.Sum(s => s.forecast_twenty_twoday_sales),
                forecast_twoday_sales = dc_auto_sales_list.Sum(s => s.forecast_twoday_sales),
                fourteenday_sales = dc_auto_sales_list.Sum(s => s.fourteenday_sales),
                fourthday_sales = dc_auto_sales_list.Sum(s => s.fourthday_sales),
                gmt_create = DateTime.Now,
                gmt_modified = DateTime.Now,
                k_variable = first_auto_sales_data.k_variable,
                nineday_sales = dc_auto_sales_list.Sum(s => s.nineday_sales),
                nineteenday_sales = dc_auto_sales_list.Sum(s => s.nineteenday_sales),
                oneday_sales = dc_auto_sales_list.Sum(s => s.oneday_sales),
                sevenday_sales = dc_auto_sales_list.Sum(s => s.sevenday_sales),
                sixday_sales = dc_auto_sales_list.Sum(s => s.sixday_sales),
                sixteenday_sales = dc_auto_sales_list.Sum(s => s.sixteenday_sales),
                seventeenday_sales = dc_auto_sales_list.Sum(s => s.seventeenday_sales),
                tenday_sales = dc_auto_sales_list.Sum(s => s.tenday_sales),
                thirtyday_sales = dc_auto_sales_list.Sum(s => s.thirtyday_sales),
                threeday_sales = dc_auto_sales_list.Sum(s => s.threeday_sales),
                thridteenday_sales = dc_auto_sales_list.Sum(s => s.thridteenday_sales),
                twelveday_sales = dc_auto_sales_list.Sum(s => s.twelveday_sales),
                twentyday_sales = dc_auto_sales_list.Sum(s => s.twentyday_sales),
                twenty_eightday_sales = dc_auto_sales_list.Sum(s => s.twenty_eightday_sales),
                twenty_fiveday_sales = dc_auto_sales_list.Sum(s => s.twenty_fiveday_sales),
                twenty_fourthday_sales = dc_auto_sales_list.Sum(s => s.twenty_fourthday_sales),
                twenty_nineday_sales = dc_auto_sales_list.Sum(s => s.twenty_nineday_sales),
                twenty_oneday_sales = dc_auto_sales_list.Sum(s => s.twenty_oneday_sales),
                twenty_seveneday_sales = dc_auto_sales_list.Sum(s => s.twenty_seveneday_sales),
                twenty_sixday_sales = dc_auto_sales_list.Sum(s => s.twenty_sixday_sales),
                twenty_threeday_sales = dc_auto_sales_list.Sum(s => s.twenty_threeday_sales),
                twenty_twoday_sales = dc_auto_sales_list.Sum(s => s.twenty_twoday_sales),
                twoday_sales = dc_auto_sales_list.Sum(s => s.twoday_sales),
                warehouse_code = mother_turn_data.warehouse_code,
                title = first_auto_sales_data.title,
                forecast_formula = first_auto_sales_data.forecast_formula,
                warehouse_name = mother_turn_data.warehouse_name

            };

            mother_dc_auto_sales.id = conn.QueryFirstOrDefault<int?>(" select id from dc_auto_sales where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
            {
                bailun_sku = mother_dc_auto_sales.bailun_sku,
                warehouse_code = mother_dc_auto_sales.warehouse_code
            }) ?? 0;
            if (mother_dc_auto_sales.id > 0)
            {
                conn.Update(mother_dc_auto_sales);
            }
            else
            {
                conn.Insert(mother_dc_auto_sales);
            }

            var dc_auto_inbound_list = new List<dc_auto_inbound>();
            foreach (var item in turn_datas)
            {
                var inbound_data = _connection.QueryFirstOrDefault<dc_auto_inbound>(" select * from dc_auto_inbound where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new { bailun_sku = item.bailun_sku, warehouse_code = item.warehouse_code });
                dc_auto_inbound_list.Add(inbound_data);
            }
            var first_inbound_data = dc_auto_inbound_list.FirstOrDefault();
            var mother_inbound_data = new dc_auto_inbound
            {
                warehouse_code = mother_dc_auto_sales.warehouse_code,
                bailun_sku = mother_dc_auto_sales.bailun_sku,
                actual_inventory = dc_auto_inbound_list.Sum(s => s.actual_inventory),
                forecast_eightday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_eightday_inbound),
                forecast_eighteenday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_eighteenday_inbound),
                forecast_elevenday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_elevenday_inbound),
                forecast_fifteenday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_fifteenday_inbound),
                forecast_fiveday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_fiveday_inbound),
                forecast_fourteenday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_fourteenday_inbound),
                forecast_fourthday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_fourthday_inbound),
                forecast_nineday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_nineday_inbound),
                forecast_nineteenday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_nineteenday_inbound),
                forecast_oneday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_oneday_inbound),
                forecast_sevenday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_sevenday_inbound),
                forecast_seventeenday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_seventeenday_inbound),
                forecast_sixday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_sixday_inbound),
                forecast_sixteenday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_sixteenday_inbound),
                forecast_tenday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_tenday_inbound),
                forecast_thirtyday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_thirtyday_inbound),
                forecast_threeday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_threeday_inbound),
                forecast_thridteenday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_thridteenday_inbound),
                forecast_today_inbound = dc_auto_inbound_list.Sum(s => s.forecast_today_inbound),
                forecast_twelveday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twelveday_inbound),
                forecast_twentyday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twentyday_inbound),
                forecast_twenty_eightday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twenty_eightday_inbound),
                forecast_twenty_fiveday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twenty_fiveday_inbound),
                forecast_twenty_fourthday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twenty_fourthday_inbound),
                forecast_twenty_nineday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twenty_nineday_inbound),
                forecast_twenty_oneday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twenty_oneday_inbound),
                forecast_twenty_seveneday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twenty_seveneday_inbound),
                forecast_twenty_sixday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twenty_sixday_inbound),
                forecast_twenty_threeday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twenty_threeday_inbound),
                forecast_twenty_twoday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twenty_twoday_inbound),
                forecast_twoday_inbound = dc_auto_inbound_list.Sum(s => s.forecast_twoday_inbound),
                gmt_create = DateTime.Now,
                gmt_modified = DateTime.Now,
                relation = $"[{(string.Join(",", MergeStringList(dc_auto_inbound_list.Select(s => s.relation.Replace("[", "").Replace("]", "").Split(',').ToList()).ToList())))}]",
                details = MergeList(dc_auto_inbound_list.Select(s => s.details.ToObj<List<decimal>>()).ToList()).ToJson()
            };

            mother_inbound_data.id = conn.QueryFirstOrDefault<int?>(" select id from dc_auto_inbound where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
            {
                bailun_sku = mother_dc_auto_sales.bailun_sku,
                warehouse_code = mother_dc_auto_sales.warehouse_code
            }) ?? 0;
            if (mother_inbound_data.id > 0)
            {
                conn.Update(mother_inbound_data);
            }
            else
            {
                conn.Insert(mother_inbound_data);
            }

        }

        public static void ResetTurnOverByMapSku(string bailun_sku, string warehouse_code)
        {
            // 查询是否有母sku
            var mother_sku_map_data = _connection.QueryFirstOrDefault<dc_base_mother_sku_map>(" select * from dc_base_mother_sku_map where bailun_sku_warehouse_code=@bailun_sku_warehouse_code ", new
            {
                bailun_sku_warehouse_code = $"{bailun_sku}{warehouse_code}"
            });
            if (mother_sku_map_data != null)
            {
                ResetTurnOver(mother_sku_map_data.mother_sku, mother_sku_map_data.mother_warehouse_code);
            }
        }

        public static List<decimal> MergeList(List<List<decimal>> datas)
        {
            var r = new List<decimal>();
            for (int i = 0; i < datas.Max(s => s.Count); i++)
            {
                decimal val = 0;
                foreach (var item in datas)
                {
                    if (item.Count - 1 >= i)
                    {
                        val += item[i];
                    }
                }
                r.Add(val);
            }

            return r;
        }

        public static List<string> MergeStringList(List<List<string>> datas)
        {
            var r = new List<string>();
            for (int i = 0; i < datas.Max(s => s.Count); i++)
            {
                List<string> val = new List<string>();
                foreach (var item in datas)
                {
                    if (item.Count - 1 >= i)
                    {
                        val.AddRange(item[i].Split('*'));
                    }
                }
                r.Add(string.Join("*", val));
            }

            return r;
        }

        /// <summary>
        /// 获取子sku
        /// </summary>
        /// <returns></returns>
        public static List<dc_base_mother_sku_map> GetMapSkus(string mother_sku, string monther_warehouse_code)
        {
            return _connection.Query<dc_base_mother_sku_map>(" select * dc_base_mother_sku where mother_sku_warehouse_code=@mother_sku_warehouse_code  ", new
            {
                mother_sku_warehouse_code = $"{mother_sku}{monther_warehouse_code}"
            }).ToList();
        }

        /// <summary>
        /// 刷新 母sku周转数据
        /// </summary>
        public static void SynchroCalculationTurnOver()
        {
            var task_name = $"SynchroCalculationTurnOver_v2";
            var conn = MyMySqlConnection._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.AddMinutes(-2),
                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.AddDays(-10);
            }

            new_task_synchro_log.id = conn.Insert(new_task_synchro_log) ?? 0;
            int count = 0;
            int page = 1;
            int rows = 1000;
            while (true)
            {
                var datas = conn.Query<dc_base_mother_sku_map>($@" select t2.* from dc_auto_turnover as t1 
INNER join dc_base_mother_sku_map as t2 on t1.bailun_sku_warehouse_code = t2.bailun_sku_warehouse_code
where t1.gmt_modified>=@btime and t1.gmt_modified <=@etime -- and t1.daily_weighted_sales>1
order by t1.gmt_modified asc 
 limit {(page - 1) * rows},{rows}  ", new { btime = new_task_synchro_log.start_time, etime = new_task_synchro_log.end_time }, commandTimeout: 0).ToList();
                if (datas.Count <= 0) { break; }
                page++;
                foreach (var orderItem in datas)
                {
                    try
                    {

                        ResetTurnOver(orderItem.mother_sku, orderItem.mother_warehouse_code);
                        count++;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        Console.WriteLine(ex.StackTrace);
                    }


                }

            }


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

        /// <summary>
        /// 自动生成数据
        /// 用一个sku 在监控中 都是fba仓
        /// </summary>
        public static void GenerateData()
        {
            var group_list = _connection.Query<temp_turnover_dto>(@"select t1.bailun_sku,GROUP_CONCAT(CONCAT(t1.warehouse_code,'_',t1.daily_weighted_sales,'_',t1.id))  as 'warehouse_codes' from dc_auto_turnover as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code =t2.warehouse_code
left join dc_auto_config_sku_warehouse as t3 on t1.bailun_sku = t3.bailun_sku and t1.warehouse_code = t3.warehouse_code
where t2.hq_type in ('fba仓')  and ifnull(t3.`status`,0)=0 GROUP BY t1.bailun_sku HAVING count(1)>1
").ToList();

            foreach (var item in group_list)
            {
                try
                {
                    var mainData = item.warehouse_datas.OrderByDescending(s => s.daily_weighted_sales).FirstOrDefault();
                    var mother_id = 0;
                    mother_id = Save(new dc_base_mother_sku { sku = item.bailun_sku, warehouse_code = mainData.warehouse_code }, new UserData() { UserName = "system" });
                    // 销量最高的做主sku
                    BatchSet(new batch_set_input_dto { mother_id = mother_id, ids = item.warehouse_datas.Where(s => s.id != mainData.id).Select(s => s.id).ToList() }, new UserData() { UserName = "system" });
                    BatchSet(new batch_set_input_dto { mother_id = mother_id, ids = new List<int> { mainData.id }, is_main = 1 }, new UserData() { UserName = "system" });
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"子母sku刷新 {item.bailun_sku} "+ex.Message);
                    Console.WriteLine($"子母sku刷新 {item.bailun_sku} "+ex.StackTrace);
                    //Console.WriteLine($"子母sku刷新 {item.bailun_sku} "+ item.ToJson());
                }
               
                
            }
        }

        public class temp_turnover_dto
        {
            public int id { get; set; }
            public string bailun_sku { get; set; }
            public string warehouse_codes { get; set; }
            public List<warehouse_data_dto> warehouse_datas
            {
                get
                {
                    List<warehouse_data_dto> datas = new List<warehouse_data_dto>();
                    datas.AddRange(warehouse_codes.Split(',').Select(s =>
                    {
                        var temp_datas = s.Split('_');
                        return new warehouse_data_dto()
                        {
                            warehouse_code = temp_datas[0],
                            daily_weighted_sales = decimal.Parse(temp_datas[1]),
                            id = int.Parse(temp_datas[2]),
                        };
                    }).ToList());
                    return datas;
                }
            }
            public class warehouse_data_dto
            {
                public decimal daily_weighted_sales { get; set; }
                public string warehouse_code { get; set; }
                public int id { get; set; }
            }

        }
    }
}
