using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using AutoTurnOver.Services;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using AutoTurnOver.Utility;
using AutoTurnOver.Models.Report;
using System.Data;
using AutoTurnOver.Common;
using System.IO;
using AutoTurnOver.Models;
using AutoTurnOver.Models.stock;
using AutoTurnOver.DB;
using Dapper;
using AutoTurnOver.Models.auto;

namespace AutoTurnOver.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class ReportsController : ControllerBase
    {
        /// <summary>ForecastSalePutinStockShort
        /// 获取预测销售+入库+库存+缺货数
        /// </summary>
        /// <param name="sku">sku编码</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <returns></returns>
        public JsonResult ForecastSalePutinStockShort(string sku, string warehousecode)
        {
            try
            {
                var services = new Services.ReportServices();
                var result = services.ForecastSalePutinStockShort(sku, warehousecode);

                var count = 0;
                foreach (var item in result)
                {
                    var arr = item.Split(',');

                    if (arr.Length > 1)
                    {
                        count = arr.Length;
                        break;
                    }
                }

                // 提前查询周系数公式 
                var dc_base_week_coefficient_datas = MyMySqlConnection._connection_read_only.Query<dc_base_week_coefficient>(" select * from dc_base_week_coefficient where bailun_sku_warehouse_code=@bailun_sku_warehouse_code ", new
                {
                    bailun_sku_warehouse_code = $"{sku}{warehousecode}"
                });
                // 提前查询销量配置 
                var sales_day_config_datas = MyMySqlConnection._connection_read_only.Query<sales_day_config>(" select * from sales_day_config where bailun_sku_warehouse_code=@bailun_sku_warehouse_code ", new
                {
                    bailun_sku_warehouse_code = $"{sku}{warehousecode}"
                });

                //[0.13, 0.12, 0.11, 0.11, 0.1, 0.09, 0.09, 0.08, 0.08, 0.07, 0.06, 0.06, 0.05, 0.04, 0.04, 0.03, 0.02, 0.02, 0.01, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]

                var turnover_date = MyMySqlConnection._connection_read_only.QueryFirstOrDefault<DateTime>(" select  gmt_modified from dc_auto_turnover where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                {
                    bailun_sku = sku,
                    warehouse_code = warehousecode
                });

                var date = new List<string>();
                var today = turnover_date.AddDays(-7);
                var weeks = new List<dynamic>();
                var sales_day_configs = new List<dynamic>();
                for (var i = 0; i < count; i++)
                {
                    var dateI = today.AddDays(i);
                    date.Add(dateI.ToString("MM/dd"));
                }
                for (var i = -7; i < count; i++)
                {
                    var dateI = today.AddDays(i + 7);
                    var date_str = dateI.ToString("yyyy-MM-dd");
                    var weekItem = dc_base_week_coefficient_datas.FirstOrDefault(s => s.week == (int)dateI.DayOfWeek);

                    if (weekItem != null)
                    {
                        weeks.Add(new { value = weekItem.coefficient, date = dateI.ToString("MM/dd"), formula = $"  ( {weekItem.formula} )", val = $"{weekItem.coefficient}" });
                    }
                    else
                    {
                        weeks.Add(new { value = 1, date = dateI.ToString("MM/dd"), formula = "-", val = "-" });
                    }
                    var sales_day_configItem = sales_day_config_datas.FirstOrDefault(s => s.date_str == date_str);
                    if (sales_day_configItem != null)
                    {
                        sales_day_configs.Add(new { date = date_str, id = sales_day_configItem.id, val = sales_day_configItem.sales });
                    }
                    else
                    {
                        sales_day_configs.Add(new { date = date_str, id = 0, val = 0 });
                    }
                }


                var names = new List<string>() { "销量", "计算公式", "计算规则", "预计入库", "可用库存", "预计缺货数量", "特殊销量", "市场因素", "建议下单数量", "人工下单数量" };
                var turnoverData = new Services.SkuAutoTurnServices().GetModel(sku, warehousecode);
                var saleVolumeData = new Services.SaleVolumeServices().GetBySkuWarehouseCode(sku, warehousecode);
                var averageTargets = PurchaseAverageTargetServices.GetListByWCode(sku, warehousecode);
                var averageTargetWarehouse = PurchaseAverageTargetServices.GetWarehouse(sku, warehousecode);
                DateTime turnoverDate = DateTime.Now.AddDays(turnoverData.turnover_days);

                // 计算到周转期前一天的累计销量
                decimal turnoverDatecumulativeSales = 0;
                var salesList = result[0].ToObj<List<decimal>>();
                for (int i = 7; i < turnoverData.turnover_days + 7 - 1; i++)
                {
                    //salesList[i] = Math.Round(salesList[i] * weeks[i].value, 0);
                    turnoverDatecumulativeSales += salesList[i];
                }

                //result[0] = salesList.ToJson();

                // 累计预计到货
                decimal turnoverDatepurchase = 0;
                var purchaseList = result[3].ToObj<List<decimal>>();
                for (int i = 7; i < turnoverData.turnover_days + 7; i++)
                {
                    turnoverDatepurchase += purchaseList[i];
                }

                // 当日预测销量
                var turnoverDateSales = (int)result[0].ToObj<List<decimal>>()[turnoverData.turnover_days + 7 - 1];
                var buyFormula = new
                {
                    turnoverDateStr = turnoverDate.ToString("yyyy-MM-dd"), // 取值日期
                    turnoverDateSales = turnoverDateSales, // 当日预测销量
                    turnoverDateStock = (int)result[4].ToObj<List<decimal>>()[turnoverData.turnover_days + 7 - 1],   // 当日预测库存,
                    turnoverDatecumulativeSales = (int)turnoverDatecumulativeSales, // 累计销量
                    nowStr = DateTime.Now.ToString("yyyy-MM-dd"), // 今天的日期
                    nowShortage = (int)result[5].ToObj<List<decimal>>()[7], // 今天的实际缺货
                    cumulativeShortageDate = turnoverDate.AddDays(-1).ToString("yyyy-MM-dd"),// 一直到周转期前一天的日期
                    cumulativeShortage = (int)result[5].ToObj<List<decimal>>()[turnoverData.turnover_days + 7 - 1],  // 一直到周转期前一天的累计缺货
                    average_overdue = Math.Max(0, turnoverData.average_overdue),
                    nowDateStock = (int)result[4].ToObj<List<decimal>>()[7],   // 今天的预测库存
                    turnoverDatepurchase = turnoverDatepurchase // 累计预计到货
                };
                return new JsonResult(new
                {
                    data = result,
                    weeks = weeks,
                    sales_day_configs = sales_day_configs,
                    turnoverData,
                    saleVolumeData,
                    days = date,
                    names,
                    averageTargets,
                    averageTargetWarehouse,
                    buyFormula
                }
                );
            }
            catch (Exception ex)
            {
                return new JsonResult(new {  s=  ex.Message,ss = ex.StackTrace });
            }
        }

        /// <summary>
        /// 实时库存
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        [UseAction]
        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【实时数据】->【jit实时缺货】->【搜索】页面", 0)]
        public JsonResult QuantityOutStockList(string bailun_sku, string warehousecode, string product_inner_code, string purchase_user, int offset, int limit, string order, string sort, string warehousetype, int? warehousearea)
        {
            var m = new quantity_out_stock_search_dto
            {
                bailun_sku = bailun_sku,
                isSum = false,
                product_inner_code = product_inner_code,
                purchase_user = purchase_user,
                warehousearea = warehousearea,
                warehousetype = warehousetype,
                warehouse_code = warehousecode
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.QuantityOutStockList(m, offset, limit, ref total, order: order, sort: sort);

            return new JsonResult(new
            {
                rows = list,
                total = total,
            });
        }

        public JsonResult QuantityOutStockFooter(string bailun_sku, string warehousecode, string product_inner_code, string purchase_user, int offset, int limit, string order, string sort, string warehousetype, int? warehousearea)
        {
            var m = new quantity_out_stock_search_dto
            {
                bailun_sku = bailun_sku,
                isSum = true,
                product_inner_code = product_inner_code,
                purchase_user = purchase_user,
                warehousearea = warehousearea,
                warehousetype = warehousetype,
                warehouse_code = warehousecode
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.QuantityOutStockList(m, offset, limit, ref total, order: order, sort: sort);

            return new JsonResult(list == null || list.Count <= 0 ? new quantity_out_stock_dto() : list[0]);
        }

        /// <summary>
        /// 导出实时缺货
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        [UseAction]
        public FileResult QuantityOutStockExport(string bailun_sku, string warehousecode, string product_inner_code, string purchase_user, int offset, int limit, string order, string sort, string warehousetype, int? warehousearea)
        {
            var m = new quantity_out_stock_search_dto
            {
                bailun_sku = bailun_sku,
                product_inner_code = product_inner_code,
                purchase_user = purchase_user,
                warehousearea = warehousearea,
                warehousetype = warehousetype,
                warehouse_code = warehousecode
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.QuantityOutStockList(m, 0, int.MaxValue, ref total, order: order, sort: sort);

            DataTable table = new DataTable();
            string[] cols = new string[] { "仓库编码", "仓库名称", "sku", "产品名称", "缺货总数量", "采购员", "缺货最后更新时间", "总在途","调拨在途最后更新时间","采购在途最后更新时间"
            };
            foreach (var item in cols)
            {
                table.Columns.Add(item);
            }

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

                row["仓库编码"] = itemData.warehouse_code;
                row["仓库名称"] = itemData.warehouse_name;
                row["sku"] = itemData.bailun_sku;
                row["产品名称"] = itemData.sku_name;
                row["缺货总数量"] = itemData.quantity_out_stock;
                row["采购员"] = itemData.buyer_name;
                row["缺货最后更新时间"] = itemData.update_time;
                row["总在途"] = itemData.quantity_ontheway;
                row["调拨在途最后更新时间"] = itemData.gmt_transfer_modified;
                row["采购在途最后更新时间"] = itemData.gmt_purchase_modified;

                table.Rows.Add(row);
            }

            var fileName = AppContext.BaseDirectory + @"Result\RealtimeStock\实时缺货数据.csv";

            DataTableHelper.SaveCSV(table, fileName);

            var memory = new MemoryStream();
            using (var stream = new FileStream(fileName, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            return File(memory, "text/csv", "实时缺货数据.csv");
        }

        /// <summary>
        /// 实时库存
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        [UseAction]
        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【实时数据】->【实时缺货明细】->【搜索】页面", 0)]
        public JsonResult QuantityOutStockDetailList(DateTime? pay_time_min, DateTime? pay_time_max, string platform_type, string shiplogistics_code, int? bailun_account_id, int? search_type, string warehousecode, string key_words, string purchase_user, int offset, int limit, string order, string sort, string warehousetype, int? warehousearea)
        {
            var m = new quantity_out_stock_detail_search_dto
            {
                search_type = search_type,
                key_words = key_words,
                purchase_user = purchase_user,
                warehousearea = warehousearea,
                warehousetype = warehousetype,
                warehouse_code = warehousecode,
                bailun_account_id = bailun_account_id,
                shiplogistics_code = shiplogistics_code,
                platform_type = platform_type,
                pay_time_max = pay_time_max,
                pay_time_min = pay_time_min
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.QuantityOutStockDetailList(m, offset, limit, ref total, order: order, sort: sort);

            return new JsonResult(new
            {
                rows = list,
                total = total,
            });
        }


        public JsonResult QuantityOutStockDetailFooter(DateTime? pay_time_min, DateTime? pay_time_max, string platform_type, string shiplogistics_code, int? bailun_account_id, int? search_type, string warehousecode, string key_words, string purchase_user, int offset, int limit, string order, string sort, string warehousetype, int? warehousearea)
        {
            var m = new quantity_out_stock_detail_search_dto
            {
                search_type = search_type,
                key_words = key_words,
                purchase_user = purchase_user,
                warehousearea = warehousearea,
                isSum = true,
                warehousetype = warehousetype,
                warehouse_code = warehousecode,
                bailun_account_id = bailun_account_id,
                shiplogistics_code = shiplogistics_code,
                platform_type = platform_type,
                pay_time_max = pay_time_max,
                pay_time_min = pay_time_min
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.QuantityOutStockDetailList(m, offset, limit, ref total, order: order, sort: sort);

            return new JsonResult(list == null || list.Count <= 0 ? new quantity_out_stock_detail_dto() : list[0]);
        }
        [BrowseLog("Bailun_aims", "触发【百伦自动周转系统】->【实时数据】->【实时缺货明细】->【导出】操作", 1)]
        public FileResult QuantityOutStockDetailExport(DateTime? pay_time_min, DateTime? pay_time_max, string platform_type, string shiplogistics_code, int? bailun_account_id, int? search_type, string warehousecode, string key_words, string purchase_user, int offset, int limit, string order, string sort, string warehousetype, int? warehousearea)
        {
            var m = new quantity_out_stock_detail_search_dto
            {
                search_type = search_type,
                key_words = key_words,
                purchase_user = purchase_user,
                warehousearea = warehousearea,
                warehousetype = warehousetype,
                warehouse_code = warehousecode,
                bailun_account_id = bailun_account_id,
                shiplogistics_code = shiplogistics_code,
                platform_type = platform_type,
                pay_time_max = pay_time_max,
                pay_time_min = pay_time_min
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.QuantityOutStockDetailList(m, 0, int.MaxValue, ref total, order: order, sort: sort);
            // Detail
            DataTable table = new DataTable();
            string[] cols = new string[] { "缺货统计时间", "平台订单号", "平台类型", "百伦订单号", "付款交易号", "站点", "付款时间", "销售账号","国家","sku","订单所有itemid",
                "产品名称", "订单sku销量", "发货数量","发货仓库编码","发货仓库", "是否缺货", "仓库总缺货", "国内仓总缺货", "其他有库存仓库", "其他仓库库存数","国内仓是否缺货","采购员",
                 "平台物流方式", "发货物流方式", "sku监控状态", "销售状态", "总在途库存数", "最先到货采购单","最快到货时间","采购单供应商","采购单阿里巴巴单号","采购单物流单号","品牌","订单最后发货时间"
            };
            foreach (var item in cols)
            {
                table.Columns.Add(item);
            }

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

                row["缺货统计时间"] = itemData.gmt_out_stock_modified == null ? "" : itemData.gmt_out_stock_modified.Value.ToString("yyyy-MM-dd HH:mm:ss");
                row["平台订单号"] = itemData.origin_order_id;
                row["平台类型"] = itemData.platform_type;
                row["百伦订单号"] = itemData.bailun_order_id;
                row["付款交易号"] = itemData.transaction_id;
                row["站点"] = itemData.website;
                row["付款时间"] = itemData.pay_time == null ? "" : itemData.pay_time.Value.ToString("yyyy-MM-dd HH:mm:ss");
                row["销售账号"] = itemData.seller_account;
                row["国家"] = itemData.receipt_country;
                row["sku"] = itemData.bailun_sku;
                row["订单所有itemid"] = itemData.items;
                row["产品名称"] = itemData.sku_name;
                row["订单sku销量"] = itemData.bailun_sku_quantity_ordered;
                row["发货数量"] = itemData.bailun_sku_quantity_shipped;
                row["发货仓库编码"] = itemData.warehouse_code;
                row["发货仓库"] = itemData.warehouse_name;
                row["是否缺货"] = itemData.is_out_stock;
                row["仓库总缺货"] = itemData.quantity_out_stock;
                row["国内仓总缺货"] = itemData.sum_quantity_out_stock;
                row["其他有库存仓库"] = itemData.other_codes;
                row["其他仓库库存数"] = itemData.other_stock;
                row["国内仓是否缺货"] = itemData.is_sum_quantity_out_stock;
                row["采购员"] = itemData.buyer_name;
                row["平台物流方式"] = itemData.bailun_require_logistics;
                row["发货物流方式"] = itemData.logistics_method_name;
                row["sku监控状态"] = itemData.monitor_status;
                row["销售状态"] = itemData.sku_status;
                row["总在途库存数"] = itemData.quantity_ontheway;
                row["最先到货采购单"] = itemData.min_buy.no;
                row["最快到货时间"] = itemData.min_buy.arrival_date.ToString("yyyy-MM-dd HH:mm:ss");
                row["采购单供应商"] = itemData.min_buy.supplier_name;
                row["采购单阿里巴巴单号"] = itemData.min_buy.alibaba_order_id;
                row["采购单物流单号"] = itemData.min_buy.logistics_order_id;
                row["品牌"] = itemData.brand;
                row["订单最后发货时间"] = itemData.shipping_deadline_real.ToString("yyyy-MM-dd HH:mm:ss");

                table.Rows.Add(row);
            }

            var fileName = AppContext.BaseDirectory + @"Result\RealtimeStock\实时缺货明细数据.csv";

            DataTableHelper.SaveCSV(table, fileName);

            var memory = new MemoryStream();
            using (var stream = new FileStream(fileName, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            return File(memory, "text/csv", "实时缺货明细数据.csv");
        }


        /// <summary>
        /// 缺货改0
        /// </summary>
        /// <param name="bailun_sku"></param>
        /// <param name="warehousecode"></param>
        /// <param name="product_inner_code"></param>
        /// <param name="purchase_user"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="order"></param>
        /// <param name="sort"></param>
        /// <param name="warehousetype"></param>
        /// <param name="warehousearea"></param>
        /// <returns></returns>
        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【配置】->【缺货改零】->【搜索】页面", 0)]
        public JsonResult OutStockList(int? search_type, string warehousecode, string key_words, string purchase_user, int offset, int limit, string order, string sort, string warehousetype, int? warehousearea)
        {
            var m = new out_stock_search_dto
            {
                search_type = search_type,
                key_words = key_words,
                purchase_user = purchase_user,
                warehousearea = warehousearea,
                warehousetype = warehousetype,
                warehouse_code = warehousecode
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.OutStockList(m, offset, limit, ref total, order: order, sort: sort);

            return new JsonResult(new
            {
                rows = list,
                total = total,
            });
        }

        /// <summary>
        /// 导出缺货改0数据
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public FileResult OutStockExport(int? search_type, string warehousecode, string key_words, string purchase_user, int offset, int limit, string order, string sort, string warehousetype, int? warehousearea)
        {
            var m = new out_stock_search_dto
            {
                search_type = search_type,
                key_words = key_words,
                purchase_user = purchase_user,
                warehousearea = warehousearea,
                warehousetype = warehousetype,
                warehouse_code = warehousecode
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.OutStockList(m, 0, int.MaxValue, ref total, order: order, sort: sort);
            // Detail
            DataTable table = new DataTable();
            string[] cols = new string[] { "商品编码", "仓库编码", "仓库名称", "sku", "产品名称", "缺货总数量", "sku监控状态", "采购员","平台物流方式","发货物流方式"
            };
            foreach (var item in cols)
            {
                table.Columns.Add(item);
            }

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

                row["商品编码"] = itemData.product_code;
                row["仓库编码"] = itemData.warehouse_code;
                row["仓库名称"] = itemData.warehouse_name;
                row["sku"] = itemData.bailun_sku;
                row["产品名称"] = itemData.sku_name;
                row["缺货总数量"] = itemData.sum_quantity_out_stock;
                row["采购员"] = itemData.buyer_name;
                row["平台物流方式"] = itemData.ebay_bailun_require_logistics;
                row["发货物流方式"] = itemData.ebay_logistics_method_name;


                table.Rows.Add(row);
            }

            var fileName = AppContext.BaseDirectory + @"Result\RealtimeStock\缺货改0数据.csv";

            DataTableHelper.SaveCSV(table, fileName);

            var memory = new MemoryStream();
            using (var stream = new FileStream(fileName, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            return File(memory, "text/csv", "缺货改0数据.csv");
        }

        /// <summary>
        /// 缺货改0
        /// </summary>
        /// <param name="bailun_sku"></param>
        /// <param name="warehousecode"></param>
        /// <param name="product_inner_code"></param>
        /// <param name="purchase_user"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="order"></param>
        /// <param name="sort"></param>
        /// <param name="warehousetype"></param>
        /// <param name="warehousearea"></param>
        /// <returns></returns>
        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【实时数据】->【回货速改】->【搜索】页面", 0)]
        public JsonResult ReturnSpeedChangeList(bool? has_available_stock, int? search_type, string warehousecode, string key_words, string purchase_user, int offset, int limit, string order, string sort, string warehousetype, int? warehousearea)
        {
            var m = new return_speed_change_search_dto
            {
                search_type = search_type,
                key_words = key_words,
                purchase_user = purchase_user,
                warehousearea = warehousearea,
                warehousetype = warehousetype,
                warehouse_code = warehousecode,
                has_available_stock = has_available_stock
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.ReturnSpeedChange(m, offset, limit, ref total, order: order, sort: sort);

            return new JsonResult(new
            {
                rows = list,
                total = total,
            });
        }

        /// <summary>
        /// 回货速补
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public FileResult ReturnSpeedChangeExport(bool? has_available_stock, int? search_type, string warehousecode, string key_words, string purchase_user, int offset, int limit, string order, string sort, string warehousetype, int? warehousearea)
        {
            var m = new return_speed_change_search_dto
            {
                search_type = search_type,
                key_words = key_words,
                purchase_user = purchase_user,
                warehousearea = warehousearea,
                warehousetype = warehousetype,
                warehouse_code = warehousecode,
                has_available_stock = has_available_stock
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.ReturnSpeedChange(m, 0, int.MaxValue, ref total, order: order, sort: sort);
            // Detail
            DataTable table = new DataTable();
            string[] cols = new string[] { "商品编码", "仓库编码", "仓库名称", "sku", "产品名称", "是否有可用库存", "sku监控状态", "采购员","平台物流方式","发货物流方式"
            };
            foreach (var item in cols)
            {
                table.Columns.Add(item);
            }

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

                row["商品编码"] = itemData.product_code;
                row["仓库编码"] = itemData.warehouse_code;
                row["仓库名称"] = itemData.warehouse_name;
                row["sku"] = itemData.bailun_sku;
                row["产品名称"] = itemData.sku_name;
                row["是否有可用库存"] = itemData.has_available_stock == 1 ? "有" : "无";
                row["采购员"] = itemData.buyer_name;
                row["平台物流方式"] = itemData.ebay_bailun_require_logistics;
                row["发货物流方式"] = itemData.ebay_logistics_method_name;


                table.Rows.Add(row);
            }

            var fileName = AppContext.BaseDirectory + @"Result\RealtimeStock\回货速补数据.csv";

            DataTableHelper.SaveCSV(table, fileName);

            var memory = new MemoryStream();
            using (var stream = new FileStream(fileName, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            return File(memory, "text/csv", "回货速补数据.csv");
        }

        /// <summary>
        /// 标记回货
        /// </summary>
        /// <param name="has_available_stock"></param>
        /// <param name="search_type"></param>
        /// <param name="warehousecode"></param>
        /// <param name="key_words"></param>
        /// <param name="purchase_user"></param>
        /// <param name="warehousetype"></param>
        /// <param name="warehousearea"></param>
        /// <returns></returns>
        [BrowseLog("Bailun_aims", "触发【百伦自动周转系统】->【配置】->【回货速改】->【标记回货】操作", 3)]
        public JsonResult MarkReturnSpeedChange(bool? has_available_stock, int? search_type, string warehousecode, string key_words, string purchase_user, string warehousetype, int? warehousearea)
        {
            var m = new return_speed_change_search_dto
            {
                search_type = search_type,
                key_words = key_words,
                purchase_user = purchase_user,
                warehousearea = warehousearea,
                warehousetype = warehousetype,
                warehouse_code = warehousecode,
                has_available_stock = has_available_stock
            };
            try
            {
                var services = new ReportServices();
                var total = services.MarkReturnSpeedChange(m);
                return new JsonResult(new { success = true, data = total });
            }
            catch (Exception ex)
            {
                return new JsonResult(new { success = false, message = ex.Message });
            }
        }

        /// <summary>
        /// 批量出信
        /// </summary>
        /// <returns></returns>
        [BrowseLog("Bailun_aims", "触发【百伦自动周转系统】->【实时数据】->【实时缺货明细】->【批量出信】操作", 1)]
        public FileResult BatchSendMessage(string bailun_order_ids, string content, string subject, string remarks)
        {
            BatchSendMessageInputDto data = new BatchSendMessageInputDto
            {
                bailun_order_ids = bailun_order_ids,
                content = content,
                remarks = remarks,
                subject = subject
            };
            var services = new ReportServices();
            var table = services.BatchSendMessage(data);
            var fileName = $"出信结果.csv";
            var fullFileName = AppContext.BaseDirectory + $@"Result\SendMessage\{fileName}";
            DataTableHelper.SaveCSV(table, fullFileName);

            var memory = new MemoryStream();
            using (var stream = new FileStream(fullFileName, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            return File(memory, "text/csv", "出信结果.csv");
        }

        /// <summary>
        /// sku 标签报表
        /// </summary>
        /// <param name="platform_type"></param>
        /// <returns></returns>
        [UseAction]
        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【报表】->【商品分类汇总】->【搜索】页面", 0)]
        public JsonResult GetSkuWebsiteSales(string platform_type, string warehousetype, int? warehousearea, string warehousecode, DateTime? btime, DateTime? etime)
        {
            bailun_sku_website_sales_search_dto search_data = new bailun_sku_website_sales_search_dto
            {
                platform_type = platform_type,
                warehousetype = warehousetype,
                etime = etime,
                btime = btime,
                warehousearea = warehousearea,
                warehouse_code = warehousecode
            };

            var list = new ReportServices().GetSkuWebsiteSales(search_data);
            return new JsonResult(new
            {
                rows = list,
                total = 0,
            });
        }

        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【报表】->【第三方海外仓统计表】->【搜索】页面", 0)]
        public JsonResult ReportStockWeekView(DateTime? btime, DateTime? etime)
        {
            report_stock_week_view_search_dto search_data = new report_stock_week_view_search_dto
            {
                etime = etime,
                btime = btime
            };

            var list = new ReportServices().ReportStockWeekView(search_data);
            return new JsonResult(new
            {
                rows = list,
                total = 0,
            });
        }

        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【报表】->【第三方海外仓统计表】->【导出】", 0)]
        public FileResult ExportTableReportStockWeekViewSkus(string web_site, string tag, string date_tag)
        {
            var list = new ReportServices().ExportTableReportStockWeekViewSkus(web_site, tag, date_tag);



            DataTable table = new DataTable();
            string[] cols = new string[] { "SKU", "仓库", "仓库名称", "站点","库存"};
            foreach (var item in cols)
            {
                table.Columns.Add(item);
            }
            foreach (var itemDataD in list)
            {
                DataRow row = table.NewRow();
                row["SKU"] = itemDataD.bailun_sku;
                row["仓库"] = itemDataD.warehouse_code;
                row["仓库名称"] = itemDataD.warehouse_name;
                row["站点"] = itemDataD.web_site;
                row["库存"] = itemDataD.stock;
                table.Rows.Add(row);
            }

            var fileName = AppContext.BaseDirectory + @"Result\RealtimeStock\第三方海外仓统计表明细.csv";

            DataTableHelper.SaveCSV(table, fileName);

            var memory = new MemoryStream();
            using (var stream = new FileStream(fileName, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            return File(memory, "text/csv", "第三方海外仓统计表.csv");
        }
        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【报表】->【第三方海外仓统计表】->【导出】", 0)]
        public FileResult ExportTableReportStockWeekView(DateTime? btime, DateTime? etime)
        {
            report_stock_week_view_search_dto search_data = new report_stock_week_view_search_dto
            {
                etime = etime,
                btime = btime
            };

            var list = new ReportServices().ReportStockWeekView(search_data);
            var time_list = new ReportServices().ReportStockWeekViewGetWeekList(search_data);




            DataTable table = new DataTable();
            string[] cols = new string[] { "站点", "标签" };
            foreach (var item in cols)
            {
                table.Columns.Add(item);
            }
            foreach (var item in time_list)
            {
                table.Columns.Add($"{item}-值");
                table.Columns.Add($"{item}-占比");
            }


            foreach (var itemDataD in list)
            {
                DataRow row = table.NewRow();
                var itemData = (IDictionary<string, object>)itemDataD;
                row["站点"] = itemData["web_site"];
                row["标签"] = itemData["tag"];

                foreach (var item in time_list)
                {
                    row[$"{item}-值"] = itemData["v_val_" + $"{item}"];
                    row[$"{item}-占比"] = itemData["ratio_" + $"{item}"];
                }

                table.Rows.Add(row);
            }

            var fileName = AppContext.BaseDirectory + @"Result\RealtimeStock\第三方海外仓统计表.csv";

            DataTableHelper.SaveCSV(table, fileName);

            var memory = new MemoryStream();
            using (var stream = new FileStream(fileName, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            return File(memory, "text/csv", "第三方海外仓统计表.csv");
        }

        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【报表】->【第三方海外仓采购汇总】->【搜索】页面", 0)]
        public JsonResult ReportPurchaseWeekView(DateTime? btime, DateTime? etime)
        {
            report_stock_week_view_search_dto search_data = new report_stock_week_view_search_dto
            {
                etime = etime,
                btime = btime
            };

            var list = new ReportServices().ReportPurchaseWeekView(search_data);
            return new JsonResult(new
            {
                rows = list,
                total = 0,
            });
        }

        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【报表】->【财务报表】->【搜索】页面", 0)]
        public JsonResult ReportFinanceView(DateTime? btime, DateTime? etime)
        {
            report_stock_week_view_search_dto search_data = new report_stock_week_view_search_dto
            {
                etime = etime,
                btime = btime
            };

            var list = new ReportServices().ReportFinanceView(search_data);
            return new JsonResult(new
            {
                rows = list,
                total = 0,
            });
        }

        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【报表】->【第三方海外仓采购汇总】->【导出】", 0)]
        public FileResult ExportTableReportPurchaseWeekView(DateTime? btime, DateTime? etime)
        {
            report_stock_week_view_search_dto search_data = new report_stock_week_view_search_dto
            {
                etime = etime,
                btime = btime
            };

            var list = new ReportServices().ReportPurchaseWeekView(search_data);
            var time_list = new ReportServices().ReportStockWeekViewGetWeekList(search_data);


            DataTable table = new DataTable();
            string[] cols = new string[] { "站点", "标签" };
            foreach (var item in cols)
            {
                table.Columns.Add(item);
            }
            foreach (var item in time_list)
            {
                table.Columns.Add($"{item}");
            }


            foreach (var itemDataD in list)
            {
                DataRow row = table.NewRow();
                var itemData = (IDictionary<string, object>)itemDataD;
                row["站点"] = itemData["web_site"];
                row["标签"] = itemData["tag"];

                foreach (var item in time_list)
                {
                    row[$"{item}"] = itemData["val_" + $"{item}"];
                }

                table.Rows.Add(row);
            }

            var fileName = AppContext.BaseDirectory + @"Result\RealtimeStock\第三方海外仓采购统计表.csv";

            DataTableHelper.SaveCSV(table, fileName);

            var memory = new MemoryStream();
            using (var stream = new FileStream(fileName, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            return File(memory, "text/csv", "第三方海外仓采购统计表.csv");
        }

        public JsonResult GetMonthList(DateTime? btime, DateTime? etime)
        {
            report_stock_week_view_search_dto search_data = new report_stock_week_view_search_dto
            {
                etime = etime,
                btime = btime
            };

            var list = new ReportServices().GetMonthList(search_data);
            return new JsonResult(list);
        }
        public JsonResult ReportStockWeekViewGetWeekList(DateTime? btime, DateTime? etime)
        {
            report_stock_week_view_search_dto search_data = new report_stock_week_view_search_dto
            {
                etime = etime,
                btime = btime
            };

            var list = new ReportServices().ReportStockWeekViewGetWeekList(search_data);
            return new JsonResult(list);
        }

        public JsonResult GetSkuWarehouseSales(string platform_type, string warehousetype, int? warehousearea, string warehousecode, DateTime? btime, DateTime? etime)
        {
            bailun_sku_website_sales_search_dto search_data = new bailun_sku_website_sales_search_dto
            {
                platform_type = platform_type,
                warehousetype = warehousetype,
                etime = etime,
                btime = btime,
                warehousearea = warehousearea,
                warehouse_code = warehousecode
            };

            var list = new ReportServices().GetSkuWarehouseSales(search_data);
            return new JsonResult(new
            {
                rows = list,
                total = 0,
            });
        }

        public FileResult ExportTableSkuWarehouse(string platform_type, string warehousetype, int? warehousearea, string warehousecode, DateTime? btime, DateTime? etime)
        {
            bailun_sku_website_sales_search_dto search_data = new bailun_sku_website_sales_search_dto
            {
                platform_type = platform_type,
                warehousetype = warehousetype,
                etime = etime,
                btime = btime,
                warehousearea = warehousearea,
                warehouse_code = warehousecode
            };



            var e_btime = DateTime.Now.AddDays(-8).ToDayHome();
            var e_etime = DateTime.Now.AddDays(-1).ToDayEnd();
            if (search_data.btime != null)
            {
                e_btime = search_data.btime.Value.ToDayHome();
            }
            if (search_data.etime != null)
            {
                e_etime = search_data.etime.Value.ToDayEnd();
            }

            var list = new ReportServices().GetSkuWarehouseSales(search_data);

            DataTable table = new DataTable();
            string[] cols = new string[] { "分类", "仓库" };
            foreach (var item in cols)
            {
                table.Columns.Add(item);
            }
            var col_this_date = e_etime;
            while (col_this_date.ToDayHome() > e_btime.ToDayHome())
            {
                table.Columns.Add($"{col_this_date.ToString("MM-dd")}-销量");
                table.Columns.Add($"{col_this_date.ToString("MM-dd")}-销售额");
                table.Columns.Add($"{col_this_date.ToString("MM-dd")}-库存");
                col_this_date = col_this_date.AddDays(-1);
            }


            foreach (var itemDataD in list)
            {
                DataRow row = table.NewRow();
                var itemData = (IDictionary<string, object>)itemDataD;
                row["分类"] = itemData["label"];
                row["仓库"] = itemData["warehouse_name"];

                var row_this_date = e_etime;
                while (row_this_date.ToDayHome() > e_btime.ToDayHome())
                {
                    row[$"{row_this_date.ToString("MM-dd")}-销量"] = itemData["v_count_" + $"{row_this_date.Month}-{row_this_date.Day}"];
                    row[$"{row_this_date.ToString("MM-dd")}-销售额"] = itemData["v_sum_" + $"{row_this_date.Month}-{row_this_date.Day}"];
                    row[$"{row_this_date.ToString("MM-dd")}-库存"] = itemData["v_stock_" + $"{row_this_date.Month}-{row_this_date.Day}"];
                    row_this_date = row_this_date.AddDays(-1);
                }

                table.Rows.Add(row);
            }

            var fileName = AppContext.BaseDirectory + @"Result\RealtimeStock\商品汇总_仓库.csv";

            DataTableHelper.SaveCSV(table, fileName);

            var memory = new MemoryStream();
            using (var stream = new FileStream(fileName, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            return File(memory, "text/csv", "商品汇总_仓库.csv");
        }

        public FileResult SkuWebsiteSalesExport(string platform_type, string label, string website, string warehouse_code, int? year, int? month, int? day)
        {
            var user = AutoUtility.GetUser();
            var memory = new ReportServices().SkuWebsiteSalesExport(new bailun_sku_website_sales_detailed_search_dto
            {
                day = day ?? 0,
                label = label,
                month = month ?? 0,
                platform_type = platform_type,
                website = website,
                warehouse_code = warehouse_code,
                year = year ?? 0
            }, AutoUtility.GetUser());
            return File(memory, "text/csv", $"{user.UserAccount}商品分类数据信息.csv");
        }
        public JsonResult GetSkuWebsiteSalesSumFooter(string platform_type, string warehousetype, int? warehousearea, string warehousecode, DateTime? btime, DateTime? etime)
        {

            bailun_sku_website_sales_search_dto search_data = new bailun_sku_website_sales_search_dto
            {
                platform_type = platform_type,
                warehousetype = warehousetype,
                has_sum = true,
                etime = etime,
                btime = btime,
                warehousearea = warehousearea,
                warehouse_code = warehousecode
            };

            var list = new ReportServices().GetSkuWebsiteSales(search_data);

            return new JsonResult(list == null || list.Count <= 0 ? new dc_base_stock_dto() : list[0]);
        }

        public JsonResult GetSkuWarehouseSalesSumFooter(string platform_type, string warehousetype, int? warehousearea, string warehousecode, DateTime? btime, DateTime? etime)
        {

            bailun_sku_website_sales_search_dto search_data = new bailun_sku_website_sales_search_dto
            {
                platform_type = platform_type,
                warehousetype = warehousetype,
                has_sum = true,
                etime = etime,
                btime = btime,
                warehousearea = warehousearea,
                warehouse_code = warehousecode
            };

            var list = new ReportServices().GetSkuWarehouseSales(search_data);

            return new JsonResult(list == null || list.Count <= 0 ? new dc_base_stock_dto() : list[0]);
        }

        [UseAction]
        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【报表】->【商品分类汇总】->【搜索】页面", 0)]
        public JsonResult SupplierSalesList(string supplier_name, int offset, int limit, string order, string sort)
        {
            var m = new supplier_sales_search_dto
            {
                supplier_name = supplier_name,
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.SupplierSalesList(m, offset, limit, ref total, order: order, sort: sort);

            return new JsonResult(new
            {
                rows = list,
                total = total,
            });
        }

        public JsonResult SupplierSalesListSumFooter(string supplier_name)
        {

            var m = new supplier_sales_search_dto
            {
                supplier_name = supplier_name,
                has_sum = true
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.SupplierSalesList(m, 0, 0, ref total, order: "", sort: "");

            return new JsonResult(list == null || list.Count <= 0 ? new supplier_sales_dto() : list[0]);
        }

        [UseAction]
        public JsonResult GetGoods(string product_inner_code, string platform_type, int offset, int limit, string order, string sort)
        {
            var m = new bailun_sku_goods_search_dto
            {
                product_inner_code = product_inner_code,
                platform_type = platform_type
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.GetGoods(m, offset, limit, ref total, order: order, sort: sort);

            return new JsonResult(new
            {
                rows = list,
                total = total,
            });
        }

        public async Task<FileResult> GetGoodsExport(string product_inner_code, string platform_type)
        {
            var user = AutoUtility.GetUser();
            var m = new bailun_sku_goods_search_dto
            {
                product_inner_code = product_inner_code,
                platform_type = platform_type
            };
            var memory = new ReportServices().GetGoodsExport(m, user);
            var fileData = await QiNiuCloudHelper.UploadAsync(memory, $"商品销售情况统计{user.UserName}-{DateTime.Now.ToString("yyyyMMddHHmmss")}导入结果.csv");
            return File(memory, "text/csv", $"{user.UserAccount}商品汇总数据信息.csv");
        }
        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【报表】->【商品销售情况统计】->【搜索】页面", 0)]
        public JsonResult GetGoodsSumFooter(string product_inner_code, string platform_type)
        {

            var m = new bailun_sku_goods_search_dto
            {
                product_inner_code = product_inner_code,
                platform_type = platform_type,
                has_sum = true
            };
            var services = new ReportServices();
            var total = 0;
            var list = services.GetGoods(m, 0, 0, ref total, order: "", sort: "");

            return new JsonResult(list == null || list.Count <= 0 ? new supplier_sales_dto() : list[0]);
        }

        /// <summary>
        /// 安全库存
        /// </summary>
        /// <param name="supplier_name"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="order"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        [BrowseLog("Bailun_aims", "访问【百伦自动周转系统】->【实时数据】->【安全库存汇总】->【搜索】页面", 0)]
        public JsonResult GetInventoryStock(string warehousetype, int offset, int limit, string order, string sort, int? warehousearea, string warehouse_code)
        {
            try
            {
                var m = new quantity_safe_inventory_search_dto
                {
                    warehousetype = warehousetype,
                    warehousearea = warehousearea,
                    warehouse_code = warehouse_code,
                    page = (offset / limit) + 1,
                    rows = limit,
                    sidx = sort,
                    sord = order,
                    is_sum = false
                };
                var services = new ReportServices();
                var page_data = services.GetInventoryStock(m);
                return new JsonResult(new
                {
                    rows = page_data.Items,
                    total = page_data.TotalItems
                });
            }
            catch (Exception ex)
            {
                return new JsonResult(new
                {
                    message = ex.Message,
                    stack_trace = ex.StackTrace
                });
            }

        }

        public JsonResult GetWipStockPage(string warehouse_code, string bailun_sku, int offset, int limit, string order, string sort)
        {
            try
            {
                var m = new dc_base_wip_stock_search_dto
                {
                    warehouse_code = warehouse_code,
                    bailun_sku = bailun_sku,
                    page = (offset / limit) + 1,
                    rows = limit,
                    sidx = sort,
                    sord = order,
                };
                var services = new ReportServices();
                var page_data = services.GetWipStockPage(m);
                return new JsonResult(new
                {
                    rows = page_data.Items,
                    total = page_data.TotalItems
                });
            }
            catch (Exception ex)
            {
                return new JsonResult(new
                {
                    message = ex.Message,
                    stack_trace = ex.StackTrace
                });
            }

        }

        public JsonResult GetSkuPage(string buyer_name, string bailun_sku, string product_inner_code, string product_code, int offset, int limit, string order, string sort)
        {
            try
            {
                var m = new dc_base_sku_search_dto
                {
                    product_code = product_code,
                    buyer_name = buyer_name,
                    bailun_sku = bailun_sku,
                    product_inner_code = product_inner_code,
                    page = (offset / limit) + 1,
                    rows = limit,
                    sidx = sort,
                    sord = order,
                };
                var services = new ReportServices();
                var page_data = services.GetSkuPage(m);
                return new JsonResult(new
                {
                    rows = page_data.Items,
                    total = page_data.TotalItems
                });
            }
            catch (Exception ex)
            {
                return new JsonResult(new
                {
                    message = ex.Message,
                    stack_trace = ex.StackTrace
                });
            }

        }

        public JsonResult GetInventoryStockSumFooter(string warehousetype, int offset, int limit, string order, string sort, int? warehousearea, string warehouse_code)
        {
            var m = new quantity_safe_inventory_search_dto
            {
                warehousetype = warehousetype,
                warehousearea = warehousearea,
                warehouse_code = warehouse_code,
                page = 1,
                rows = 1,
                sidx = sort,
                sord = order,
                is_sum = true
            };
            var services = new ReportServices();
            var list = services.GetInventoryStock(m);
            return new JsonResult(list == null || list.Items.Count <= 0 ? new quantity_safe_inventory_dto() : list.Items[0]);
        }

        /// <summary>
        /// 查询销量数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public JsonResult GetSalesTrendYear(string product_type)
        {
            var services = new Services.ReportServices();
            var result = services.GetSalesTrendYear(product_type);
            
            return new JsonResult(result);
        }
        /// <summary>
        /// 查询销量数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public JsonResult GetProductTypeList()
        {
            var services = new Services.ReportServices();
            var result = services.GetProductTypeList();
            
            return new JsonResult(result);
        }
    }
}