using System;
using System.Collections.Generic;
using System.Text;
using AutoTurnOver.Models;
using System.Linq;
using System.IO;
using System.Data;
using AutoTurnOver.Utility;
using System.Threading.Tasks;
using AutoTurnOver.DB;
using Dapper;
using AutoTurnOver.Models.Report;

namespace AutoTurnOver.Services
{
    /// <summary>
    /// 自动周转
    /// </summary>
    public class SkuAutoTurnServices
    {
        /// <summary>
        /// 获取自动周转分页
        /// </summary>
        /// <param name="page">当前页</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="total">符合条件的总记录数</param>
        /// <param name = "order" > 排序类型 </ param >
        /// <param name="sort">排序字段</param>
        /// <returns></returns>
        public List<dc_auto_turnover_list_dto> List(Condition_AutoTurnOver m, int offset, int limit, ref int total, string order = "", string sort = "", bool isSum = false)
        {
            if (!string.IsNullOrWhiteSpace(m.categoryIds))
            {
                m.categoryModels = ApiServices.GetNewClientNodesByIds(m.categoryIds.Split(',').Select(s => int.Parse(s)).ToList());
            }
            return DB.dc_auto_turnover.List(m, offset, limit, ref total, order, sort, isSum);
        }

        public List<dc_base_order_data_source_dto> GetOrderDataSource(dc_base_order_data_source_search_dto m, int offset, int limit, ref int total)
        {
            return DB.dc_auto_turnover.GetOrderDataSource(m, offset, limit, ref total);
        }

        public IEnumerable<string> GetLabelList()
        {
            return DB.dc_auto_turnover.GetLabelList();
        }

        #region 特殊销售设置

        /// <summary>
        /// 获取特殊销售设置分页列表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public List<dc_auto_config_promotion> List(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            return DB.dc_auto_turnover.ListConfigPromotion(m, offset, limit, ref total);
        }

        /// <summary>
        /// 查询未来指定时间范围内的特殊销量数据
        /// </summary>
        /// <param name="days"></param>
        /// <returns></returns>
        public IEnumerable<dc_auto_config_promotion> List(string sku, string warehousecode, int days)
        {
            var now = DateTime.Now;
            return DB.dc_auto_turnover.ListConfigPromotion(sku, warehousecode, now, now.AddDays(days));
        }


        /// <summary>
        /// 保存销售设置
        /// </summary>
        /// <param name="m">销售设置model</param>
        /// <param name="username">当前操作人</param>
        /// <returns></returns>
        public string SaveSaleSetting(Models.dc_auto_config_promotion m, string username)
        {
            if (string.IsNullOrWhiteSpace(m.bailun_sku))
            {
                return "Sku不能为空！";
            }

            if (string.IsNullOrWhiteSpace(m.warehouse_code))
            {
                return "请选择仓库！";
            }

            if (m.count <= 0)
            {
                return "增量必须大于0！";
            }

            var objWarehouse = DB.common.GetWareHouseByCode(m.warehouse_code);
            if (objWarehouse == null)
            {
                return "系统找不到该仓库。";
            }

            m.warehouse_name = objWarehouse.warehouse_name;

            if (m.promotion_time < DateTime.Parse(DateTime.Now.ToShortDateString()).AddDays(1))
            {
                return "销售日期必须大于今天！";
            }

            return DB.dc_auto_turnover.SaveSaleSetting(m, username);
        }

        public void AddsSaleSetting(List<dc_auto_config_promotion_input> datas)
        {
            foreach (var m in datas)
            {
                if (string.IsNullOrWhiteSpace(m.sku))
                {
                    throw new Exception("Sku不能为空！");
                }

                if (string.IsNullOrWhiteSpace(m.warehousecode))
                {
                    throw new Exception("请选择仓库！");
                }

                if (m.count <= 0)
                {
                    throw new Exception("增量必须大于0！");
                }

                var objWarehouse = DB.common.GetWareHouseByCode(m.warehousecode);
                if (objWarehouse == null)
                {
                    throw new Exception("系统找不到该仓库");
                }

                m.warehouse_name = objWarehouse.warehouse_name;

                if (m.time < DateTime.Parse(DateTime.Now.ToShortDateString()).AddDays(1))
                {
                    throw new Exception("销售日期必须大于今天");
                }
            }


            DB.dc_auto_turnover.AddsSaleSetting(datas);
        }

        /// <summary>
        /// 获取特殊销售设置详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public dc_auto_config_promotion GetSaleSettingById(int id)
        {
            return DB.dc_auto_turnover.GetSaleSettingById(id);
        }

        #endregion

        #region 市场因素设置

        /// <summary>
        /// 获取市场因素设置分页列表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public List<dc_auto_config_correction> CorrectionList(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            return DB.dc_auto_turnover.ListCorrection(m, offset, limit, ref total);
        }

        /// <summary>
        /// 查询未来指定时间范围内的特殊销量数据
        /// </summary>
        /// <param name="days"></param>
        /// <returns></returns>
        public IEnumerable<dc_auto_config_correction> CorrectionList(string sku, string warehousecode, int days)
        {
            var now = DateTime.Now;
            return DB.dc_auto_turnover.ListCorrection(sku, warehousecode, now, now.AddDays(days));
        }


        /// <summary>
        /// 保存销售设置
        /// </summary>
        /// <param name="m">销售设置model</param>
        /// <param name="username">当前操作人</param>
        /// <returns></returns>
        public string SaveCorrection(Models.dc_auto_config_correction m, string username)
        {
            if (string.IsNullOrWhiteSpace(m.bailun_sku))
            {
                return "Sku不能为空！";
            }

            if (string.IsNullOrWhiteSpace(m.warehouse_code))
            {
                return "请选择仓库！";
            }

            if (m.param <= 0)
            {
                return "比例必须大于0！";
            }

            var objWarehouse = DB.common.GetWareHouseByCode(m.warehouse_code);
            if (objWarehouse == null)
            {
                return "系统找不到该仓库。";
            }

            m.warehouse_name = objWarehouse.warehouse_name;

            if (m.start_time < DateTime.Parse(DateTime.Now.ToShortDateString()).AddDays(1))
            {
                return "开始时间必须大于今天！";
            }

            if (m.end_time < DateTime.Parse(DateTime.Now.ToShortDateString()).AddDays(1))
            {
                return "结束时间必须大于今天！";
            }

            if (DateTime.Parse(m.end_time.ToShortDateString()) < DateTime.Parse(m.start_time.ToShortDateString()))
            {
                return "结束时间不能小于开始时间！";
            }


            return DB.dc_auto_turnover.SaveCorrection(m, username);
        }



        /// <summary>
        /// 获取市场因素设置详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public dc_auto_config_correction GetCorrectionById(int id)
        {
            return DB.dc_auto_turnover.GetCorrectionById(id);
        }

        #endregion

        #region 预估库存

        /// <summary>
        /// 获取预估库存详情
        /// </summary>
        /// <param name="sku">sku编码</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <returns></returns>
        public dc_auto_inventory GetAutoInventoryBySkuWH(string sku, string warehousecode)
        {
            return DB.dc_auto_turnover.GetAutoInventoryBySkuWH(sku, warehousecode);
        }

        #endregion

        public AutoTurnOver.Models.dc_auto_turnover GetModel(string sku, string wCode)
        {
            return DB.dc_auto_turnover.GetModel(sku, wCode);
        }

        /// <summary>
        /// 查询SKU基础配置数据
        /// </summary>
        /// <returns></returns>
        public List<dc_sku_config_dto> SkuConfigList()
        {
            return DB.dc_auto_turnover.SkuConfigList();
        }

        /// <summary>
        /// 查询SKU基础配置数据
        /// </summary>
        /// <returns></returns>
        public List<new_supplier_delivery_dto> SkuNewSupplierDeliveryList()
        {
            return DB.dc_auto_turnover.SkuNewSupplierDeliveryList();
        }

        public string Export(Condition_AutoTurnOver m, string order, string sort, out int orows)
        {
            var fileName = AppContext.BaseDirectory + $@"周转表-{DateTime.Now.ToString("yyyyMMddHHmmss")}{Guid.NewGuid()}.csv";



            var total = 0;
            var service = new Services.SkuAutoTurnServices();


            int page = 1;
            int rows = 250000;
            orows = 0;
            while (true)
            {
                var list = service.List(m, (page - 1) * rows, rows, ref total, order, sort);
                if (list == null || list.Count <= 0) break;

                orows += list.Count();
                DataTable table = new DataTable();
                string[] cols = new string[] { "Sku","商品编码", "内部编码", "sku标题", "仓库编码", "仓库名称","仓库所在国家", "MOQ",
                "采购在途",
                    "调拨在途(非实时)","调拨在途(调拨单)","调拨在途(采购临时)","调拨在途(暂存)",
                    "昨日销量", "明日销量", "未来7天日均", "未来14天日均", "建议采购数", "活动促销数量",
                "实际库存", "安全库存", "周转天数", "供应商交期", "质检入库天数", "调拨头程天数", "缺货数量","监控状态","供应商","采购员","交期异常",
                "过去7天日均","过去14天日均","过去30天日均","重量","单价","实时库存","ebay最近7天日均销量","ebay最近14天日均销量","ebay最近30天日均销量"
                ,"速卖通最近7天日均销量","速卖通最近14天日均销量","速卖通最近30天日均销量"
                ,"已发货库存","开发时间","是否侵权","停止监控时间","标签","停售","最近7天+预测销量","amazon最近7天日均销量","amazon最近14天日均销量","amazon最近30天日均销量","品牌","简易分类"
                ,"采购陆运在途数量","采购海运在途数量","采购空运在途数量","采购铁路在途数量"
                ,"调拨陆运在途数量","调拨海运在途数量","调拨空运在途数量","调拨铁路在途数量","首次入库时间","供应链累计销量","销量预测规则","默认头程运输方式","默认头程渠道","计算时间",
                "日均出单次数","首次出单日期","首次出单订单号",
                "备货数量","产品类型","头程平均值取值来源","Walmart最近7天日均销量","Walmart最近14天日均销量","Walmart最近30天日均销量","shopify最近7天日均销量","shopify最近14天日均销量","shopify最近30天日均销量",
                "平均交期","配置交期",
                "库存可用天数","库存可用日期", "库存+在途可用天数","库存+在途可用日期","在途+库存可卖天数总目标销量","供应链长度总目标销量","最近7天的备货数量","清货状态","可消耗天数","FBA站点SKU","FBA分类标签"
            };
                foreach (var item in cols)
                {
                    table.Columns.Add(item);
                }

                foreach (var itemData in list)
                {
                    DataRow row = table.NewRow();
                    row["最近7天的备货数量"] = itemData.orders_7;
                    row["在途+库存可卖天数总目标销量"] = itemData.full_stock_use_days_turnover_sales;
                    row["供应链长度总目标销量"] = itemData.turnover_days_config_turnover_sales;
                    row["可消耗天数"] = itemData.digestion_days;
                    row["库存可用天数"] = itemData.stock_use_days;
                    row["库存可用日期"] = itemData.stock_use_date.ToString("yyyy-MM-dd");
                    row["库存+在途可用天数"] = itemData.full_stock_use_days;
                    row["库存+在途可用日期"] = itemData.full_stock_use_date.ToString("yyyy-MM-dd");


                    row["平均交期"] = itemData.supplier_delivery;
                    row["配置交期"] = itemData.supplier_delivery_config;

                    row["Walmart最近7天日均销量"] = itemData.history_sevenday_sales_walmart;
                    row["Walmart最近14天日均销量"] = itemData.history_fourteenday_sales_walmart;
                    row["Walmart最近30天日均销量"] = itemData.history_thirtyday_sales_walmart;

                    row["计算时间"] = itemData.gmt_modified.ToString("yyyy-MM-dd");
                    row["默认头程运输方式"] = itemData.head_transport_type;
                    row["默认头程渠道"] = itemData.head_transport_logistics_code;
                    row["头程平均值取值来源"] = itemData.transfer_delivery_source;

                    row["备货数量"] = itemData.stock_up_sales;
                    row["产品类型"] = itemData.product_type_desc;

                    row["日均出单次数"] = itemData.orders_avg;
                    row["首次出单日期"] = itemData.first_order_date;
                    row["首次出单订单号"] = itemData.first_order_bailun_order_id;


                    row["仓库所在国家"] = itemData.area_name;
                    row["销量预测规则"] = itemData.dc_auto_sales_forecast_title;
                    row["采购陆运在途数量"] = itemData.quantity_land_purchase;
                    row["采购海运在途数量"] = itemData.quantity_ocean_purchase;
                    row["采购空运在途数量"] = itemData.quantity_air_purchase;
                    row["采购铁路在途数量"] = itemData.quantity_railway_purchase;
                    row["调拨陆运在途数量"] = itemData.quantity_land_transfer;
                    row["调拨海运在途数量"] = itemData.quantity_ocean_transfer;
                    row["调拨空运在途数量"] = itemData.quantity_air_transfer;
                    row["调拨铁路在途数量"] = itemData.quantity_railway_transfer;

                    row["Sku"] = itemData.bailun_sku;
                    row["商品编码"] = itemData.product_code;
                    row["内部编码"] = itemData.product_inner_code;
                    row["sku标题"] = itemData.sku_title;
                    row["仓库编码"] = itemData.warehouse_code;
                    row["仓库名称"] = itemData.db_warehouse_name;
                    row["MOQ"] = itemData.quantity_minimum_order;
                    row["采购在途"] = itemData.quantity_purchase;

                    row["调拨在途(非实时)"] = itemData.quantity_transfer;
                    row["调拨在途(调拨单)"] = itemData.quantity_transfer_order;
                    row["调拨在途(采购临时)"] = itemData.quantity_transfer_temp_schedule;
                    row["调拨在途(暂存)"] = itemData.quantity_transfer_temporary_storage;

                    row["昨日销量"] = itemData.oneday_sales;
                    row["明日销量"] = itemData.forecast_oneday_sales2;
                    row["未来7天日均"] = itemData.forecast_sevenday_sales;
                    row["未来14天日均"] = itemData.forecast_fourteenday_sales;
                    row["建议采购数"] = itemData.quantity_final_advise;
                    row["活动促销数量"] = itemData.quantity_promotion;
                    row["实际库存"] = itemData.quantity_inventory;
                    row["安全库存"] = itemData.quantity_safe_inventory;
                    row["周转天数"] = itemData.turnover_days;
                    row["供应商交期"] = itemData.supplier_delivery;
                    row["质检入库天数"] = itemData.inspection_delivery;
                    row["调拨头程天数"] = itemData.transfer_delivery;
                    row["缺货数量"] = itemData.quantity_out_stock;
                    row["监控状态"] = itemData.monitor_status == 1 ? "停止监控" : "监控中";
                    row["供应商"] = itemData.supplier_name;
                    row["采购员"] = itemData.buyer_name;
                    row["交期异常"] = itemData.delivery_days_error;
                    row["过去7天日均"] = itemData.history_sevenday_sales;
                    row["过去14天日均"] = itemData.history_fourteenday_sales;
                    row["过去30天日均"] = itemData.history_thirtyday_sales;
                    row["重量"] = itemData.weight;
                    row["单价"] = itemData.unit_price;
                    row["实时库存"] = itemData.usable_stock;
                    row["ebay最近7天日均销量"] = itemData.history_sevenday_sales_ebay;
                    row["ebay最近14天日均销量"] = itemData.history_fourteenday_sales_ebay;
                    row["ebay最近30天日均销量"] = itemData.history_thirtyday_sales_ebay;
                    row["速卖通最近7天日均销量"] = itemData.history_sevenday_sales_aliexpress;
                    row["速卖通最近14天日均销量"] = itemData.history_fourteenday_sales_aliexpress;
                    row["速卖通最近30天日均销量"] = itemData.history_thirtyday_sales_aliexpress;
                    row["amazon最近7天日均销量"] = itemData.history_sevenday_sales_amazon;
                    row["amazon最近14天日均销量"] = itemData.history_fourteenday_sales_amazon;
                    row["amazon最近30天日均销量"] = itemData.history_thirtyday_sales_amazon;
                    row["shopify最近7天日均销量"] = itemData.history_sevenday_sales_shopify;
                    row["shopify最近14天日均销量"] = itemData.history_fourteenday_sales_shopify;
                    row["shopify最近30天日均销量"] = itemData.history_thirtyday_sales_shopify;
                    row["已发货库存"] = itemData.shipped_stock;
                    row["开发时间"] = (itemData.develop_time == null ? "" : itemData.develop_time.Value.ToString("yyyy-MM-dd"));
                    row["是否侵权"] = itemData.has_tort;
                    row["停止监控时间"] = itemData.stop_monitor_create == null ? "" : itemData.stop_monitor_create.Value.ToString("yyyy-MM-dd HH:mm:ss");
                    //row["动态备货规则组"] = itemData.group_name;
                    row["标签"] = itemData.sku_label;
                    row["停售"] = itemData.sku_status_str;
                    row["最近7天+预测销量"] = itemData.sales_details == null ? "" : itemData.sales_details.Replace("[", "").Replace("]", "");
                    row["品牌"] = itemData.brand;
                    row["简易分类"] = itemData.category_simple_name;
                    row["首次入库时间"] = (itemData.first_inbound_date == null ? "" : itemData.first_inbound_date.Value.ToString("yyyy-MM-dd"));
                    row["供应链累计销量"] = itemData.turnover_sales;
                    row["清货状态"] = itemData.clearance_status == 1 ? "清货" : "";
                    row["FBA站点SKU"] = itemData.fba_site_sku;
                    row["FBA分类标签"] = itemData.fba_category_label;
                    //row["FBA平台asin"] = itemData.fba_asin;
                    //row["FBA备注销售"] = itemData.fba_sale_remark;
                    //row["FBA预计断货时间"] = itemData.fba_out_of_stock_predict_time;
                    //row["FBA最近即将入库数量"] = itemData.fba_put_in_qty;
                    //row["FBA最近即将入库时间"] = itemData.fba_put_in_time;
                    //row["FBA是否需要空运"] = itemData.fba_need_airlift_str;
                    table.Rows.Add(row);
                }


                CsvFileHelper.SaveCSV(table, fileName, page == 1);
                page++;
            }

            //var memory = new MemoryStream();
            //using (var stream = new FileStream(fileName, FileMode.Open))
            //{
            //    stream.CopyTo(memory);
            //}
            //memory.Position = 0;

            //return memory;
            return fileName;
        }

        /// <summary>
        /// 导销量模板
        /// </summary>
        /// <param name="m"></param>
        /// <param name="order"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public string ExportSalesTemplate(Condition_AutoTurnOver m, string order, string sort, out int orows)
        {
            orows = 0;
            var service = new Services.SkuAutoTurnServices();
            int total = 0;
            var list = service.List(m, 0, int.MaxValue, ref total, "", "");
            var fileName = AppContext.BaseDirectory + $@"周转表-{DateTime.Now.ToString("yyyyMMddHHmmss")}{Guid.NewGuid()}.csv";
            var table = new DataTable();
            table.Columns.Add("SKU");
            table.Columns.Add("标题");
            table.Columns.Add("仓库");
            table.Columns.Add("仓库编码");
            table.Columns.Add("仓库类型");
            table.Columns.Add("昨日销量");
            table.Columns.Add("7日日均");
            table.Columns.Add("14日日均");
            table.Columns.Add("30日日均");
            var thisDate = DateTime.Now;
            for (int i = 0; i < 16; i++)
            {
                var btime = thisDate.GetWeekFirstDayMon();
                var etime = btime.GetWeekLastDaySun();
                table.Columns.Add($"预测{btime.ToString("yyyy-MM-dd")}~{etime.ToString("yyyy-MM-dd")}销量");
                thisDate = thisDate.AddDays(7);
            }

            foreach (var item in list)
            {
                var row = table.NewRow();
                row["SKU"] = item.bailun_sku;
                row["标题"] = item.sku_title;
                row["仓库"] = item.db_warehouse_name;
                row["仓库编码"] = item.warehouse_code;
                row["仓库类型"] = item.warehouse_type;
                row["昨日销量"] = item.oneday_sales;
                row["7日日均"] = item.history_sevenday_sales;
                row["14日日均"] = item.history_fourteenday_sales;
                row["30日日均"] = item.history_thirtyday_sales;

                orows++;
                table.Rows.Add(row);
            }
            CsvFileHelper.SaveCSV(table, fileName);
            return fileName;
        }

        /// <summary>
        /// 生产数据
        /// </summary>
        /// <returns></returns>
        public async void GenerateData()
        {
            //try
            //{
            //    var memory = Export(new UserData { UserAccount = "admin" }, new Condition_AutoTurnOver { warehouse_code = "GZBLWH" }, "", "");
            //    var fileData = await QiNiuCloudHelper.UploadAsync(memory, $"zhouzhuangshuj_{DateTime.Now.ToString("yyyyMMddHHmmss")}.csv");
            //    ApiServices.QiYeJiQiRenMsPush(new QiYeJiQiRenMsDto { msgtype = "text", text = new QiYeJiQiRenMsDto.text_dto { content = "广州01周转数据：" + fileData } });
            //}
            //catch (Exception ex)
            //{
            //    Console.WriteLine(ex.Message);
            //}


        }


        /// <summary>
        /// 获取历史节点趋势图
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public charts_line_dto GetHistoryDataNodes(string sku, string warehousecode)
        {
            return DB.dc_auto_turnover.GetHistoryDataNodes(sku, warehousecode);
        }

        public void ResetPurchaseAdvise()
        {
            var conn = MyMySqlConnection._connection;
            // 如果遇到出周转建议的时候，则停止
            var last_data = conn.QuerySingleOrDefault<DateTime>(" select MAX(create_time) from dc_auto_purchase_advise ");
            if ((DateTime.Now - last_data).TotalMinutes <= 20)
            {
                return;
            }

            var task_name = "ResetPurchaseAdvise_v1";

            // 查询最后一次成功抓取的记录
            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(-20),
                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.AddMinutes(-60);
            }

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

            var bailun_sku_warehouse_codes = new List<string>();
            var page = 1;
            var rows = 1000;
            var btime = DateTime.Now;
            while (true)
            {
                var datas = conn.Query<dc_base_purchase_details>($" select * from dc_base_purchase_details where update_time>=@btime and update_time<=@etime limit {(page - 1) * rows},{(rows)} ", new
                {
                    btime = new_task_synchro_log.start_time,
                    etime = new_task_synchro_log.end_time
                }).ToList();
                if (datas == null || datas.Count <= 0)
                {
                    break;
                }
                page++;

                foreach (var item in datas)
                {
                    bailun_sku_warehouse_codes.Add($"{item.bailun_sku}{item.warehouse_code}");
                    //添加到队列，重新刷数据
                    AutoTurnOver.DB.dc_auto_turnover.ResetPurchaseAdviseSingle(item.bailun_sku, item.warehouse_code, "采购单更新");

                    count++;
                }

            }

            if (bailun_sku_warehouse_codes != null && bailun_sku_warehouse_codes.Count >= 1)
            {
                // 等待周转跑完
                AutoTurnOver.DB.dc_auto_turnover.WaitTurnoverQueueTask();
                //                // 清理已经删除的数据
                //                conn.Execute(@" update  dc_auto_purchase_advise_detailed set status=-1 where bailun_sku_warehouse_code in @bailun_sku_warehouse_codes and main_id in (
                //	select id from dc_auto_purchase_advise where buy_sys_plan_no not in ('补海运')
                //) ", new
                //                {
                //                    bailun_sku_warehouse_codes = bailun_sku_warehouse_codes
                //                }, commandTimeout: 0);
                // 出周转建议
                PurchaseAdviseServices.Generate($"采购单更新，刷新周转 {DateTime.Now.ToString("HH:mm")}", btime, is_bak: false);
            }


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

        public void ResetPurchaseAdviseByTransfer()
        {
            var conn = MyMySqlConnection._connection;
            // 如果遇到出周转建议的时候，则停止
            var last_data = conn.QuerySingleOrDefault<DateTime>(" select MAX(create_time) from dc_auto_purchase_advise ");
            if ((DateTime.Now - last_data).TotalMinutes <= 20)
            {
                return;
            }

            var task_name = "ResetPurchaseAdviseByTransfer_v1";

            // 查询最后一次成功抓取的记录
            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(-20),
                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 = new DateTime(2021, 1, 1);
            }

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

            var bailun_sku_warehouse_codes = new List<string>();
            var page = 1;
            var rows = 1000;
            var btime = DateTime.Now;
            while (true)
            {
                var datas = conn.Query<dc_base_transfer_info_sku_dto>($" select t2.sku as 'bailun_sku',t1.targetwareno as 'warehouse_code' from dc_base_transfer_info as t1 left join dc_base_transfer_info_skus as t2 on t1.dataid=t2.dataid where t1.timestamp>=@btime and t1.timestamp<=@etime limit {(page - 1) * rows},{(rows)} ", new
                {
                    btime = new_task_synchro_log.start_time,
                    etime = new_task_synchro_log.end_time
                }).ToList();
                if (datas == null || datas.Count <= 0)
                {
                    break;
                }
                page++;

                foreach (var item in datas)
                {
                    bailun_sku_warehouse_codes.Add($"{item.bailun_sku}{item.warehouse_code}");
                    //添加到队列，重新刷数据
                    AutoTurnOver.DB.dc_auto_turnover.ResetPurchaseAdviseSingle(item.bailun_sku, item.warehouse_code, "调拨单更新");

                    count++;
                }

            }
            if (bailun_sku_warehouse_codes != null && bailun_sku_warehouse_codes.Count >= 1)
            {
                // 等待周转跑完
                AutoTurnOver.DB.dc_auto_turnover.WaitTurnoverQueueTask();
                // 清理已经删除的数据
                //                conn.Execute(@" update  dc_auto_purchase_advise_detailed set status=-1 where bailun_sku_warehouse_code in @bailun_sku_warehouse_codes and main_id in (
                //	select id from dc_auto_purchase_advise where buy_sys_plan_no not in ('补海运')
                //)  ", new
                //                {
                //                    bailun_sku_warehouse_codes = bailun_sku_warehouse_codes
                //                }, commandTimeout: 0);

                // 出周转建议
                PurchaseAdviseServices.Generate($"调拨单刷新 {DateTime.Now.ToString("HH:mm")}", btime, is_bak: false);
            }


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

        public class dc_base_transfer_info_sku_dto
        {
            public string bailun_sku { get; set; }
            public string warehouse_code { get; set; }
        }

        public transfer_profit_result_dto TransferProfitList(string bailun_sku, string warehouse_code)
        {
            return DB.dc_auto_turnover.TransferProfitList(bailun_sku, warehouse_code);
        }

        public List<dc_report_profit_analysis_dto> ProfitAnalysis(Condition_AutoTurnOver m, int offset, int limit, ref int total, string order = "", string sort = "")
        {
            if (!string.IsNullOrWhiteSpace(m.categoryIds))
            {
                m.categoryModels = ApiServices.GetNewClientNodesByIds(m.categoryIds.Split(',').Select(s => int.Parse(s)).ToList());
            }
            return DB.dc_auto_turnover.ProfitAnalysis(m, offset, limit, ref total, order, sort);
        }
        public void BatchPushPurchaseAdvise(string ids, UserData user)
        {
            DB.dc_auto_turnover.BatchPushPurchaseAdvise(ids, user);
        }


        public string ProfitAnalysisExport(ana_search_dto m, out int orows)
        {
            try
            {

                var fileName = AppContext.BaseDirectory + $@"周转利润分析-{DateTime.Now.ToString("yyyyMMddHHmmss")}{Guid.NewGuid()}.xlsx";
                int page = 1;
                int rows = 100000;

                if (m.page > 0)
                {
                    page = m.page;
                    rows = m.rows;
                }

                orows = 0;
                int total = 0;
                var offset = (page - 1) * rows;
                var limit = rows;
                var list = ProfitAnalysis(m, offset, limit, ref total, "", "");
                orows += list.Count();

                DataTable table = new DataTable();
                string[] cols = new string[] { "sku","产品名称","商品编码","品牌", "国家", "仓库类型", "仓库", "默认调拨方式", "180天日均",  "90天日均", "30天日均","7天日均","加权日均销量","预测日均销量", "采购单价"
                ,"空运票数占比","空运数量占比"  , "单位重量", "实际建议周转数",
                        "实际建议周转金额",  "实际周转数",  "实际周转运费",   "实际运费单价",  "海运周转数（推荐海运）", "海运周转金额", "海运周转运费", "空运周转数（推荐空运）", "空运周转金额",
                        "空运周转运费","实际-海运运费差值", "实际-海运周转采购金额差值"  ,"实际-空运运费差值", "实际-空运周转采购金额差值", "海运-空运运费差值", "海运-空运周转采购金额差值", "安全库存数量", "空运周转天数", "实际周转天数",
                        "海运周转天数", "30天预测销量海运运费-实际周转运费金额", "30天预测销量空运运费-实际周转运费金额", "30天预测销量空运运费-海运周转运费金额"
                        , "差值(实际-海运)/实际周转采购金额占比"   , "差值(海运-空运)/实际周转采购金额占比" , "差值(空运-实际)/实际周转采购金额占比"
                        , "海运与实际运费差额/库存差额"   , "空运与实际运费差额/库存差额"  , "30天销量海运与实际运费差额/库存金额"  , "30天销量空运与实际运费差额/库存金额" , "补海运周转数","补海运周转金额",
                        "采购在途","调拨在途(采购临时)","调拨在途(暂存)","调拨在途(调拨单)","可用天数","拟下单日期","可用日期","监控状态"
};
                foreach (var item in cols)
                {
                    table.Columns.Add(item);
                }

                foreach (var itemData in list)
                {
                    DataRow row = table.NewRow();

                    row["监控状态"] = itemData.monitor_status == 1 ? "停止监控" : "正常监控";
                    row["采购在途"] = itemData.quantity_purchase;
                    row["调拨在途(采购临时)"] = itemData.quantity_transfer_temp_schedule;
                    row["调拨在途(暂存)"] = itemData.quantity_transfer_temporary_storage;
                    row["调拨在途(调拨单)"] = itemData.quantity_transfer_order;
                    row["可用天数"] = itemData.stock_available_days;
                    row["拟下单日期"] = itemData.now.ToString("yyyy-MM-dd");
                    row["可用日期"] = itemData.stock_available_date.ToString("yyyy-MM-dd");


                    row["空运票数占比"] = itemData.order_count_3_radio;
                    row["空运数量占比"] = itemData.product_count_3_radio;

                    row["sku"] = itemData.bailun_sku;
                    row["产品名称"] = itemData.product_name;
                    row["商品编码"] = itemData.product_code;
                    row["品牌"] = itemData.brand;
                    row["国家"] = itemData.country;
                    row["仓库类型"] = itemData.warehouse_type;
                    row["仓库"] = itemData.warehouse_name;
                    row["默认调拨方式"] = itemData.transport_type;
                    row["实际周转天数"] = itemData.turnover_days;

                    row["180天日均"] = itemData.sales_avg_180;
                    row["90天日均"] = itemData.sales_avg_90;
                    row["30天日均"] = itemData.sales_avg_30;
                    row["7天日均"] = itemData.sales_avg_7;
                    row["加权日均销量"] = itemData.daily_weighted_sales;
                    row["预测日均销量"] = itemData.forecast_daily_weighted_sales;
                    row["采购单价"] = itemData.unit_price;
                    row["单位重量"] = itemData.weight;
                    row["实际建议周转数"] = itemData.quantity_final_advise;
                    row["实际建议周转金额"] = itemData.quantity_final_advise_amount;
                    row["实际周转数"] = itemData.turnover;
                    row["实际周转运费"] = itemData.freight_price;
                    row["实际运费单价"] = itemData.freight_unit_price;
                    row["海运周转数（推荐海运）"] = itemData.quantity_final_advise_ocean;
                    row["海运周转金额"] = itemData.quantity_final_advise_ocean_amount;
                    row["海运周转运费"] = itemData.freight_price_ocean;
                    row["空运周转数（推荐空运）"] = itemData.quantity_final_advise_air;
                    row["空运周转金额"] = itemData.quantity_final_advise_air_amount;
                    row["空运周转运费"] = itemData.freight_price_air;
                    row["实际-海运运费差值"] = itemData.freight_price_ocean_difference_amount;
                    row["实际-海运周转采购金额差值"] = itemData.quantity_final_ocean_difference_amount;
                    row["实际-空运运费差值"] = itemData.freight_price_air_difference_amount;
                    row["实际-空运周转采购金额差值"] = itemData.quantity_final_air_difference_amount;
                    row["海运-空运运费差值"] = itemData.freight_price_ocean_air_difference;
                    row["海运-空运周转采购金额差值"] = itemData.quantity_final__ocean_air_difference_amount;
                    row["安全库存数量"] = itemData.quantity_safe_inventory;
                    row["空运周转天数"] = itemData.turnover_days_air;
                    row["海运周转天数"] = itemData.turnover_days_ocean;
                    row["30天预测销量海运运费-实际周转运费金额"] = itemData.freight_price_ocean_30_difference;
                    row["30天预测销量空运运费-实际周转运费金额"] = itemData.freight_price_air_30_difference;
                    row["30天预测销量空运运费-海运周转运费金额"] = itemData.freight_price_air_ocean_30_difference;
                    row["差值(实际-海运)/实际周转采购金额占比"] = itemData.freight_price_ocean_difference_amount_radio;
                    row["差值(海运-空运)/实际周转采购金额占比"] = itemData.freight_price_ocean_air_difference_radio;
                    row["差值(空运-实际)/实际周转采购金额占比"] = itemData.freight_price_air_difference_amount_radio;
                    row["海运与实际运费差额/库存差额"] = itemData.freight_stock_amount_ocean;
                    row["空运与实际运费差额/库存差额"] = itemData.freight_stock_amount_air;
                    row["30天销量海运与实际运费差额/库存金额"] = itemData.freight_stock_amount_ocean_30;
                    row["30天销量空运与实际运费差额/库存金额"] = itemData.freight_stock_amount_air_30;
                    row["补海运周转数"] = itemData.ocean_purchase;
                    row["补海运周转金额"] = itemData.ocean_purchase_amount;

                    table.Rows.Add(row);
                }

                EPPlusHelper.ExportExcel(table, fileName);



                return fileName;
            }
            catch (Exception)
            {
                throw;
            }

        }

        #region 导入FBA站点sku和标签
        /// <summary>
        /// 导入FBA站点sku和标签
        /// </summary>
        /// <param name="fileInfo"></param>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool ImportFBASiteSku(Stream fileInfo, UserData user)
        {
            var data_set = EPPlusHelper.ReadExcel(fileInfo);
            var table = data_set.Tables[0];
            var list = new List<dc_base_sku_fba_mapping>();
            foreach (DataRow row in table.Rows)
            {
                list.Add(new dc_base_sku_fba_mapping
                {
                    warehouse_code = row["仓库编码"].ToString(),
                    bailun_sku = row["Sku"].ToString(),
                    country_code = row["站点"].ToString(),
                    site_sku = row["站点SKU"].ToString(),
                    category_label = row["fba-sku分类标签"].ToString(),
                    creation_time = DateTime.Now,
                    create_user_name = user.UserName
                });
            }
            var count = list.Count(x => string.IsNullOrWhiteSpace(x.warehouse_code));
            if (count > 0) throw new Exception("仓库编码不能为空");
            count = list.Count(x => string.IsNullOrWhiteSpace(x.bailun_sku));
            if (count > 0) throw new Exception("SKU编码不能为空");
            count = list.Count(x => string.IsNullOrWhiteSpace(x.country_code));
            if (count > 0) throw new Exception("站点编码不能为空");
            count = list.Count(x => string.IsNullOrWhiteSpace(x.site_sku));
            if (count > 0) throw new Exception("站点sku编码不能为空");
            foreach (var item in list)
            {
                dc_base_sku_fba_mapping_dao.Save(item);
            }
            return true;
        }
        #endregion
    }
}
