using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;

namespace Bailun.DC.Web.Areas.Reports.Controllers
{
    [Area("Reports")]
    public class InventoryController : Base.BaseController
    {
        private readonly IHostingEnvironment _hostingEnvironment;

        public InventoryController(IHostingEnvironment hostingEnvironment)
        {
            _hostingEnvironment = hostingEnvironment;
        }

        /// <summary>
        /// 数据仓页面
        /// </summary>
        /// <returns></returns>
        public IActionResult DataWareHouse()
        {
            return View();
        }

        /// <summary>
        /// 数据仓数据
        /// </summary>
        /// <param name="request"></param>
        /// <param name="warehousetype"></param>
        /// <param name="warehousecode"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="monitorstatus"></param>
        /// <param name="cleanstatus"></param>
        /// <param name="infringestatus"></param>
        /// <param name="skutip"></param>
        /// <param name="storestart"></param>
        /// <param name="storeend"></param>
        /// <param name="skucategory"></param>
        /// <param name="searchkey"></param>
        /// <returns></returns>
        public string DataWareHouseJson(Models.BtTableParameter request, string warehousetype, int? areaid, string warehousecode, DateTime? start, DateTime? end, int? monitorstatus, int? cleanstatus, int? infringestatus, string skutip, int? storestart, int? storeend, int? skucategory, string searchkey,string skucategoryids,string groupname,string skubrand,int? is_redundancy)
        {
            var service = new Services.SkuMonitorServices();

            var total = 0;
            //var obj = service.List(request, warehousetype, areaid, warehousecode, start, end, monitorstatus, cleanstatus, infringestatus, skutip, storestart, storeend, skucategory, searchkey, skucategoryids,groupname, ref total);

            var obj = service.List2(request, warehousetype, areaid, warehousecode, start, end, monitorstatus, cleanstatus, infringestatus, skutip, storestart, storeend, skucategory, searchkey, skucategoryids, groupname, skubrand, is_redundancy, ref total);

            var countM = new Models.dc_sku_monitor();//service.ListCount(warehousetype, areaid, warehousecode, start, end, monitorstatus, cleanstatus, infringestatus, skutip, storestart, storeend, skucategory, skucategoryids, searchkey,groupname);

            countM.skuname = "总计：";
            
            var list = obj.Select(p => new {
                p.id,
                p.warehousetype,
                p.warehouse_code,
                p.warehousename,
                p.sku,
                category_name = p.categoryname,
                p.skuname,
                status = (p.monitorstatus)==1?"正在监控":"停止监控",
                clearancestatus = (p.clearancestatus)==1?"正在清货":"否",
                tortstatus = (p.tortstatus)==1?"已侵权":"否",
                p.str_skutag,
                buyprice = (p.buyprice??0).ToString("N2"),
                usable_stock = (p.usable_stock??0),
                amount_stock = (p.amount_stock??0).ToString("N2"),
                quantity_purchase = (p.quantity_purchase??0),
                purchase_amount = (p.purchase_amount??0).ToString("N2"),
                quantity_transfer = (p.quantity_transfer??0),
                amount_transit = (p.amount_transit??0).ToString("N2"),
                quantity_out_stock = (p.quantity_out_stock??0),
                oneday_total_sales = (p.oneday_total_sales??0),
                amount_onedaysale = (p.amount_onedaysale??0).ToString("N2"),
                sevenday_total_sales = (p.sevenday_total_sales??0),
                fourteenday_total_sales = (p.fourteenday_total_sales??0),
                thirtyday_total_sales = (p.thirtyday_total_sales??0),

                sevenday_average_sales = (p.sevenday_average_sales??0),
                fourteenday_average_sales = (p.fourteenday_average_sales??0),
                thirtyday_average_sales = (p.thirtyday_average_sales??0),
                skucreatetime = p.skucreatetime.HasValue?p.skucreatetime.Value.ToString("yyyy-MM-dd HH:mm"):"",
                amount_sevendaysale = (p.amount_sevendaysale??0).ToString("N2"),
                groupname = string.IsNullOrEmpty(p.groupname)?"":p.groupname.Trim(),
                quantity_unshipped = p.quantity_unshipped??0,
                
                p.skubrand,
                is_redundancy = (p.is_redundancy.HasValue&&p.is_redundancy.Value==1?"是":"否")
            });

            return JsonConvert.SerializeObject(new { total = total, rows = list,count_row = new {
                skuname = "正在加载...",
                usable_stock = (countM.usable_stock??0),
                amount_stock = (countM.amount_stock??0).ToString("N2"),
                quantity_purchase = (countM.quantity_purchase??0),
                purchase_amount = (countM.purchase_amount??0).ToString("N2"),
                quantity_transfer = (countM.quantity_transfer??0),
                amount_transit = (countM.amount_transit??0).ToString("N2"),
                quantity_out_stock = (countM.quantity_out_stock??0),
                oneday_total_sales = (countM.oneday_total_sales??0),
                amount_onedaysale = (countM.amount_onedaysale??0).ToString("N2"),

                sevenday_total_sales = (countM.sevenday_total_sales??0),
                fourteenday_total_sales = (countM.fourteenday_total_sales??0),
                thirtyday_total_sales = (countM.thirtyday_total_sales??0),

                sevenday_average_sales = (countM.sevenday_average_sales??0).ToString("N4"),
                fourteenday_average_sales = (countM.fourteenday_average_sales??0).ToString("N4"),
                thirtyday_average_sales = (countM.thirtyday_average_sales??0),
                amount_sevendaysale = (countM.amount_sevendaysale??0).ToString("N2"),
            } });
        }

        [HttpPost]
        [BrowseLog("Bailun_data", "访问【百伦数据中心】->【库存】->【库存周转报表】->【数据仓】->【查询】页面",0)]
        public JsonResult DataWareHouseCountJson(Models.BtTableParameter request, string warehousetype, int? areaid, string warehousecode, DateTime? start, DateTime? end, int? monitorstatus, int? cleanstatus, int? infringestatus, string skutip, int? storestart, int? storeend, int? skucategory, string searchkey, string skucategoryids, string groupname,string skubrand,int? is_redundancy)
        {
            var service = new Services.SkuMonitorServices();
            var countM = service.ListCount(warehousetype, areaid, warehousecode, start, end, monitorstatus, cleanstatus, infringestatus, skutip, storestart, storeend, skucategory, skucategoryids, searchkey, groupname,skubrand, is_redundancy);

            countM.skuname = "总计：";

            return Json(new {
                count_row = new {
                    skuname = "总计",
                    usable_stock = (countM.usable_stock ?? 0),
                    amount_stock = (countM.amount_stock ?? 0).ToString("N2"),
                    quantity_purchase = (countM.quantity_purchase ?? 0),
                    purchase_amount = (countM.purchase_amount ?? 0).ToString("N2"),
                    quantity_transfer = (countM.quantity_transfer ?? 0),
                    amount_transit = (countM.amount_transit ?? 0).ToString("N2"),
                    quantity_out_stock = (countM.quantity_out_stock ?? 0),
                    oneday_total_sales = (countM.oneday_total_sales ?? 0),
                    amount_onedaysale = (countM.amount_onedaysale ?? 0).ToString("N2"),

                    sevenday_total_sales = (countM.sevenday_total_sales ?? 0),
                    fourteenday_total_sales = (countM.fourteenday_total_sales ?? 0),
                    thirtyday_total_sales = (countM.thirtyday_total_sales ?? 0),

                    sevenday_average_sales = (countM.sevenday_average_sales ?? 0).ToString("N4"),
                    fourteenday_average_sales = (countM.fourteenday_average_sales ?? 0).ToString("N4"),
                    thirtyday_average_sales = (countM.thirtyday_average_sales ?? 0),
                    amount_sevendaysale = (countM.amount_sevendaysale ?? 0).ToString("N2")
                }
            });
        }


        public ActionResult ExportDataWarehouse(string warehousetype, int? areaid, string warehousecode, DateTime? start, DateTime? end, int? monitorstatus, int? cleanstatus, int? infringestatus, string skutip, int? storestart, int? storeend, int? skucategory, string searchkey, string skucategoryids, string groupname, string skubrand,int? is_redundancy)
        {
            var service = new Services.SkuMonitorServices();

            var total = 0;
            //var obj = service.List(request, warehousetype, areaid, warehousecode, start, end, monitorstatus, cleanstatus, infringestatus, skutip, storestart, storeend, skucategory, searchkey, skucategoryids,groupname, ref total);

            var obj = service.List2(new Models.BtTableParameter { offset = 0, limit = 0 }, warehousetype, areaid, warehousecode, start, end, monitorstatus, cleanstatus, infringestatus, skutip, storestart, storeend, skucategory, searchkey, skucategoryids, groupname, skubrand, is_redundancy, ref total);

            var colNames = new List<string> { "分组名称","仓库类型", "仓库名称", "sku", "产品分类","品牌"
            ,"中文名称","监控状态","清货状态","侵权状态","SKU标签"
            ,"采购价","库存数量","库存金额","采购在途数量","采购在途金额"
            ,"调拨在途数量","调拨在途金额","待发货数量","缺货数量","昨日销量"
            ,"近七日日均销量","近十四日均销量","近三十日均销量","7天总销售额","是否冗余"};
            
            var list = new List<string>();

            foreach (var item in obj)
            {
                var str = item.groupname+"|"+item.warehousetype + "|" +item.warehousename + "|" +item.sku + "|" +item.categoryname + "|" + item.skubrand+"|"+
                    item.skuname + "|" +((item.monitorstatus)==1?"正在监控":"停止监控") + "|"+ ((item.clearancestatus)==1?"正在清货":"否") + "|" +((item.tortstatus)==1?"已侵权":"否") + "|" +item.str_skutag + "|" +
                    item.buyprice + "|" +(item.usable_stock??0) + "|" +(item.amount_stock??0) + "|" +(item.quantity_purchase??0) + "|" +(item.purchase_amount??0) + "|" +
                    (item.quantity_transfer??0) + "|" +(item.amount_transit??0) + "|" +(item.quantity_unshipped??0) + "|" +(item.quantity_out_stock??0) + "|" +(item.oneday_total_sales??0) + "|" +
                    (item.sevenday_average_sales??0) + "|" +(item.fourteenday_average_sales??0) + "|" +(item.thirtyday_average_sales??0) + "|" + (item.amount_sevendaysale??0)+"|"+(item.is_redundancy.HasValue && item.is_redundancy.Value==1?"是":"否")
                    ;

                list.Add(str);
            }

            var guid = Guid.NewGuid().ToString();
            var filename = "数据仓 " + DateTime.Now.ToString("yyyyMMddHHmmss");
            var filepath = _hostingEnvironment.WebRootPath + "\\Files\\Report\\" + DateTime.Now.ToString("yyyy-MM-dd") + "\\";
            ToCSV(list, colNames, guid, filepath);

            var ms = new System.IO.MemoryStream();
            using (var f = new System.IO.FileStream(filepath + guid + ".csv", System.IO.FileMode.Open))
            {
                f.CopyTo(ms);
            }

            ms.Position = 0;
            return File(ms, "text/csv", filename + ".csv");
        }

        /// <summary>
        /// Sku库存分析报表（财务）
        /// </summary>
        /// <returns></returns>
        public ActionResult SkuInventory()
        {
            return View();
        }

        /// <summary>
        /// Sku库存分析报表数据
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="sku">sku编码</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="date">月份</param>
        /// <returns></returns>
        public string ListSkuInventoryJson(Models.BtTableParameter parameter,string sku,string warehouse,DateTime? date)
        {
            var total = 1;

            var service = new Services.InventoryServices();
            var obj = service.ListSkuStockAnalyze(parameter, sku, warehouse, date, ref total);

            var countM = service.ListSkuStockAnalyzeCount(sku, warehouse, date);
            countM.bailun_sku = "总计：";
            countM.exportamount = Math.Round(countM.exportamount ?? 0, 2, MidpointRounding.AwayFromZero);
            countM.inboundamount = Math.Round(countM.inboundamount ?? 0, 2, MidpointRounding.AwayFromZero);
            countM.instant_inventory_amount = Math.Round(countM.instant_inventory_amount ?? 0, 2, MidpointRounding.AwayFromZero);
            countM.purchase_inbound_amount = Math.Round(countM.purchase_inbound_amount ?? 0, 2, MidpointRounding.AwayFromZero);
            countM.transfer_inbound_amount = Math.Round(countM.transfer_inbound_amount ?? 0, 2, MidpointRounding.AwayFromZero);

            foreach (var item in obj)
            {
                item.exportamount = item.exportamount ?? 0;
                item.inboundamount = item.inboundamount ?? 0;
                item.instant_inventory_amount = item.instant_inventory_amount ?? 0;
                item.inv_age_0to1_month = item.inv_age_0to1_month ?? 0;
                item.inv_age_1to2_year = item.inv_age_1to2_year ?? 0;
                item.inv_age_1to3_month = item.inv_age_1to3_month ?? 0;
                item.inv_age_2to3_year = item.inv_age_2to3_year ?? 0;
                item.inv_age_3to6_month = item.inv_age_3to6_month ?? 0;
                item.inv_age_6to12_month = item.inv_age_6to12_month ?? 0;
                item.inv_age_gt3_year = item.inv_age_gt3_year ?? 0;
                item.month_total_exports = item.month_total_exports ?? 0;

                item.purchase_price = item.purchase_price ?? 0;
                item.quantity_begin_month_inventory = item.quantity_begin_month_inventory ?? 0;
                item.quantity_damaged = item.quantity_damaged ?? 0;
                item.quantity_end_month_inventory = item.quantity_end_month_inventory ?? 0;
                item.quantity_purchase_inbound = item.quantity_purchase_inbound ?? 0;
                item.quantity_inner_purchase = item.quantity_inner_purchase ?? 0;
                item.quantity_instant_inventory = item.quantity_instant_inventory ?? 0;
                item.quantity_overflow = item.quantity_overflow ?? 0;
                item.quantity_purchase_transit = item.quantity_purchase_transit ?? 0;

                item.quantity_sales = item.quantity_sales ?? 0;
                item.quantity_sku_changed = item.quantity_sku_changed ?? 0;
                item.quantity_transfer_transit = item.quantity_transfer_transit ?? 0;
                item.turnover_date = item.turnover_date ?? 0;
                item.turnover_rate = item.turnover_rate ?? 0;
            }

            return  JsonConvert.SerializeObject(new { total = total,rows = obj, count_row = countM});
        }

        /// <summary>
        /// 设置Sku的分组信息
        /// </summary>
        /// <param name="s"></param>
        /// <param name="groupname"></param>
        /// <returns></returns>
        [HttpPost]
        [BrowseLog("Bailun_data", "触发【百伦数据中心】->【库存】->【库存周转报表】->【数据仓】->【设置分组】操作", 1)]
        public JsonResult SaveGroupName(string s,string groupname)
        {
            if (string.IsNullOrWhiteSpace(groupname))
            {
                return Json(new {
                    success = false,
                    msg = "分组名不能为空！"
                });
            }

            var arr = s.Split(';').Where(a => !string.IsNullOrWhiteSpace(a)).ToList();
            if (arr.Count <= 0)
            {
                return Json(new
                {
                    success = false,
                    msg = "请选择需要设置分组的SKU。"
                });
            }


            var result = new Services.SkuMonitorServices().SaveGroupName(arr, groupname,0,"");

            return Json(new {
                success = string.IsNullOrEmpty(result),
                msg = result,
            });
        }

        /// <summary>
        /// 上传Sku分组文件
        /// </summary>
        /// <returns></returns>
        [BrowseLog("Bailun_data", "触发【百伦数据中心】->【库存】->【库存周转报表】->【数据仓】->【上传分组文件】操作", 1)]
        public JsonResult UploadSkuGroup()
        {
            if (Request.Form.Files.Count == 0)
            {
                return Json(new
                {
                    success = false,
                    msg = "请上传文件！"
                });
            }

            var file = Request.Form.Files[0];
            Dictionary<string, DataTable> dic = Base.NpolHelper.ExcelToDataTable(file.OpenReadStream(), file.FileName, true);
            if (dic.Count > 0)
            {
                var tb = dic.FirstOrDefault();
                var list = new List<Tuple<string,string,string,string>>();
                var service = new Services.FinanceReportServices();
                var listRate = new List<Tuple<string, decimal>>();
                var commonServices = new Services.CommonServices();

                for (var i = 0; i < tb.Value.Rows.Count; i++)
                {
                    var row = tb.Value.Rows[i];

                    try
                    {
                        var warehousecode = row["仓库编码"].ToString();
                        var sku = row["SKU"].ToString();
                        var groupname = row["分组名称"].ToString();
                        var updateuser = row["修改人"].ToString();

                        if (string.IsNullOrEmpty(warehousecode))
                        {
                            return Json(new {
                                success = false,
                                msg = "仓库编码不能为空！"
                            });
                        }
                        if (string.IsNullOrEmpty(sku))
                        {
                            return Json(new
                            {
                                success = false,
                                msg = "SKU不能为空！"
                            });
                        }
                        if (string.IsNullOrEmpty(groupname))
                        {
                            return Json(new
                            {
                                success = false,
                                msg = "分组名称不能为空！"
                            });
                        }
                        if (string.IsNullOrEmpty(updateuser))
                        {
                            return Json(new
                            {
                                success = false,
                                msg = "修改人不能为空！"
                            });
                        }

                        list.Add(new Tuple<string, string, string, string>(warehousecode,sku,groupname,updateuser));
                    }
                    catch (Exception ex)
                    {
                        return Json(new
                        {
                            success = false,
                            msg = "上传出现异常，异常信息：" + ex.Message
                        });
                    }
                }

                var result = new Services.SkuMonitorServices().SaveGroupName(list, 0, "");

                return Json(new {
                    success = string.IsNullOrEmpty(result),
                    msg = result
                });
            }
            else
            {
                return Json(new
                {
                    success = false,
                    msg = "请上传文件"
                });
            }
        }


        #region 数据仓销量分析报表

        public ActionResult WarehouseSaleReport()
        {
            return View();
        }

        /// <summary>
        /// 数据仓销量分析
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="startcount">销量开始</param>
        /// <param name="endcount">销量结束</param>
        /// <param name="start">开发开始时间</param>
        /// <param name="end">开发结束时间</param>
        /// <returns></returns>
        [BrowseLog("Bailun_data", "访问【百伦数据中心】->【库存】->【库存周转报表】->【数据仓销售分析】->【查询】页面", 0)]
        public string WarehouseSaleReportJson(Models.BtTableParameter parameter,string warehousetype,string warehousecode,int? startcount,int? endcount,DateTime? devstart,DateTime? devend,DateTime? start,DateTime? end)
        {
            var total = 0;
            var services = new Services.InventoryServices();

            var obj = services.ListWarehouseSales(parameter, warehousetype, warehousecode, startcount, endcount, devstart, devend,start,end, ref total);
            var objCount = services.ListWarehouseSalesCount(warehousetype, warehousecode, startcount, endcount, devstart, devend,start,end);

            var list = obj.Select(a => new {
                aliexpress_saleamount = (a.aliexpress_saleamount??0).ToString("N2"),
                aliexpress_salecount = (a.aliexpress_salecount??0).ToString("N0"),
                amazon2B_saleamount = (a.amazon2B_saleamount??0).ToString("N2"),
                amazon2B_salecount = (a.amazon2B_salecount??0).ToString("N0"),
                amazon_saleamount = (a.amazon_saleamount??0).ToString("N2"),
                amazon_salecount = (a.amazon_salecount??0).ToString("N0"),

                ebay_saleamount = (a.ebay_saleamount??0).ToString("N2"),
                ebay_salecount = (a.ebay_salecount??0).ToString("N0"),
                fba_saleamount = (a.fba_saleamount??0).ToString("N2"),
                fba_salecount = (a.fba_salecount??0).ToString("N0"),
                jollychic_saleamount = (a.jollychic_saleamount??0).ToString("N2"),
                jollychic_salecount = (a.jollychic_salecount??0).ToString("N0"),
                joom_saleamount = (a.joom_saleamount??0).ToString("N2"),
                joom_salecount = (a.joom_salecount??0).ToString("N0"),
                marketing_saleamount = (a.marketing_saleamount??0).ToString("N2"),
                marketing_salecount = (a.marketing_salecount??0).ToString("N0"),
                mymall_saleamount = (a.mymall_saleamount??0).ToString("N2"),
                mymall_salecount = (a.mymall_salecount??0).ToString("N0"),

                opensky_saleamount = (a.opensky_saleamount??0).ToString("N2"),
                opensky_salecount = (a.opensky_salecount??0).ToString("N0"),
                priceminister_saleamount = (a.priceminister_saleamount??0).ToString("N2"),
                priceminister_salecount = (a.priceminister_salecount??0).ToString("N0"),
                qoo10_saleamount = (a.qoo10_saleamount??0).ToString("N2"),
                qoo10_salecount = (a.qoo10_salecount??0).ToString("N0"),
                shopee_saleamount = (a.shopee_saleamount??0).ToString("N2"),
                shopee_salecount = (a.shopee_salecount??0).ToString("N0"),
                shopify_saleamount = (a.shopify_saleamount??0).ToString("N2"),
                shopify_salecount = (a.shopify_salecount??0).ToString("N0"),

                stock_amount = (a.stock_amount??0).ToString("N2"),
                stock_count = (a.stock_count).ToString("N0"),
                walmart_saleamount = (a.walmart_saleamount??0).ToString("N2"),
                walmart_salecount = (a.walmart_salecount??0).ToString("N0"),
                a.warehouse_code,
                a.warehouse_name,
                a.warehouse_type,
                wish_saleamount = (a.wish_saleamount??0).ToString("N2"),
                wish_salecount = (a.wish_salecount??0).ToString("N0"),
                salecount = a.salecount.ToString("N0"),
            });


            return JsonConvert.SerializeObject(new {
                total = total,
                rows = list,
                count_row = new {
                    warehouse_name = "合计",
                    aliexpress_saleamount = (objCount.aliexpress_saleamount ?? 0).ToString("N2"),
                    aliexpress_salecount = (objCount.aliexpress_salecount ?? 0).ToString("N0"),
                    amazon2B_saleamount = (objCount.amazon2B_saleamount ?? 0).ToString("N2"),
                    amazon2B_salecount = (objCount.amazon2B_salecount ?? 0).ToString("N0"),
                    amazon_saleamount = (objCount.amazon_saleamount ?? 0).ToString("N2"),
                    amazon_salecount = (objCount.amazon_salecount ?? 0).ToString("N0"),

                    ebay_saleamount = (objCount.ebay_saleamount ?? 0).ToString("N2"),
                    ebay_salecount = (objCount.ebay_salecount ?? 0).ToString("N0"),
                    fba_saleamount = (objCount.fba_saleamount ?? 0).ToString("N2"),
                    fba_salecount = (objCount.fba_salecount ?? 0).ToString("N0"),
                    jollychic_saleamount = (objCount.jollychic_saleamount ?? 0).ToString("N2"),
                    jollychic_salecount = (objCount.jollychic_salecount ?? 0).ToString("N0"),
                    joom_saleamount = (objCount.joom_saleamount ?? 0).ToString("N2"),
                    joom_salecount = (objCount.joom_salecount ?? 0).ToString("N0"),
                    marketing_saleamount = (objCount.marketing_saleamount ?? 0).ToString("N2"),
                    marketing_salecount = (objCount.marketing_salecount ?? 0).ToString("N0"),
                    mymall_saleamount = (objCount.mymall_saleamount ?? 0).ToString("N2"),
                    mymall_salecount = (objCount.mymall_salecount ?? 0).ToString("N0"),

                    opensky_saleamount = (objCount.opensky_saleamount ?? 0).ToString("N2"),
                    opensky_salecount = (objCount.opensky_salecount ?? 0).ToString("N0"),
                    priceminister_saleamount = (objCount.priceminister_saleamount ?? 0).ToString("N2"),
                    priceminister_salecount = (objCount.priceminister_salecount ?? 0).ToString("N0"),
                    qoo10_saleamount = (objCount.qoo10_saleamount ?? 0).ToString("N2"),
                    qoo10_salecount = (objCount.qoo10_salecount ?? 0).ToString("N0"),
                    shopee_saleamount = (objCount.shopee_saleamount ?? 0).ToString("N2"),
                    shopee_salecount = (objCount.shopee_salecount ?? 0).ToString("N0"),
                    shopify_saleamount = (objCount.shopify_saleamount ?? 0).ToString("N2"),
                    shopify_salecount = (objCount.shopify_salecount ?? 0).ToString("N0"),

                    stock_amount = (objCount.stock_amount ?? 0).ToString("N2"),
                    stock_count = (objCount.stock_count).ToString("N0"),
                    walmart_saleamount = (objCount.walmart_saleamount ?? 0).ToString("N2"),
                    walmart_salecount = (objCount.walmart_salecount ?? 0).ToString("N0"),
                    wish_saleamount = (objCount.wish_saleamount ?? 0).ToString("N2"),
                    wish_salecount = (objCount.wish_salecount ?? 0).ToString("N0"),
                    salecount = objCount.salecount.ToString("N0"),
                }
            });

        }

        /// <summary>
        /// 数据仓sku销量分析
        /// </summary>
        /// <param name="warehousecode"></param>
        /// <param name="startcount"></param>
        /// <param name="endcount"></param>
        /// <param name="devstart"></param>
        /// <param name="devend"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult SkuWarehouseSaleReport(string warehousecode, int? startcount, int? endcount, DateTime? devstart, DateTime? devend, DateTime? start, DateTime? end)
        {

            ViewBag.warehousecode = warehousecode;
            ViewBag.startcount = startcount.HasValue ? startcount.Value + "" : "";
            ViewBag.endcount = endcount.HasValue ? endcount.Value + "" : "";
            ViewBag.devstart = devstart.HasValue ? devstart.Value.ToString("yyyy-MM-dd") : "";
            ViewBag.devend = devend.HasValue ? devend.Value.ToString("yyyy-MM-dd") : "";
            ViewBag.start = start.HasValue ? start.Value.ToString("yyyy-MM-dd") : "";
            ViewBag.end = end.HasValue ? end.Value.ToString("yyyy-MM-dd") : "";

            ViewBag.listWarehouse = new Services.WareHouseServices().List("", null).OrderBy(a => a.warehouse_name).ToList();


            return View();
        }

        /// <summary>
        /// 数据仓sku销量分析 数据
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehousecode">仓库编码</param>
        /// <param name="startcount">销量开始</param>
        /// <param name="endcount">销量结束</param>
        /// <param name="devstart">开发开始时间</param>
        /// <param name="devend">开发结束时间</param>
        /// <param name="start">统计开始时间</param>
        /// <param name="end">统计结束时间</param>
        /// <returns></returns>
        public string SkuWarehouseSaleReportJson(Models.BtTableParameter parameter, string warehousecode, int? startcount, int? endcount, DateTime? devstart, DateTime? devend, DateTime? start, DateTime? end,string bailun_sku)
        {
            var total = 0;
            var services = new Services.InventoryServices();

            var obj = services.ListSkuWarehouseSales(parameter, warehousecode, startcount, endcount, devstart, devend, start, end,bailun_sku, ref total);
            var objCount = services.ListSkuWarehouseSalesCount(warehousecode, startcount, endcount, devstart, devend, start, end, bailun_sku);

            var list = obj.Select(a => new {
                aliexpress_saleamount = (a.aliexpress_saleamount ?? 0).ToString("N2"),
                aliexpress_salecount = (a.aliexpress_salecount ?? 0).ToString("N0"),
                amazon2B_saleamount = (a.amazon2B_saleamount ?? 0).ToString("N2"),
                amazon2B_salecount = (a.amazon2B_salecount ?? 0).ToString("N0"),
                amazon_saleamount = (a.amazon_saleamount ?? 0).ToString("N2"),
                amazon_salecount = (a.amazon_salecount ?? 0).ToString("N0"),

                ebay_saleamount = (a.ebay_saleamount ?? 0).ToString("N2"),
                ebay_salecount = (a.ebay_salecount ?? 0).ToString("N0"),
                fba_saleamount = (a.fba_saleamount ?? 0).ToString("N2"),
                fba_salecount = (a.fba_salecount ?? 0).ToString("N0"),
                jollychic_saleamount = (a.jollychic_saleamount ?? 0).ToString("N2"),
                jollychic_salecount = (a.jollychic_salecount ?? 0).ToString("N0"),
                joom_saleamount = (a.joom_saleamount ?? 0).ToString("N2"),
                joom_salecount = (a.joom_salecount ?? 0).ToString("N0"),
                marketing_saleamount = (a.marketing_saleamount ?? 0).ToString("N2"),
                marketing_salecount = (a.marketing_salecount ?? 0).ToString("N0"),
                mymall_saleamount = (a.mymall_saleamount ?? 0).ToString("N2"),
                mymall_salecount = (a.mymall_salecount ?? 0).ToString("N0"),

                opensky_saleamount = (a.opensky_saleamount ?? 0).ToString("N2"),
                opensky_salecount = (a.opensky_salecount ?? 0).ToString("N0"),
                priceminister_saleamount = (a.priceminister_saleamount ?? 0).ToString("N2"),
                priceminister_salecount = (a.priceminister_salecount ?? 0).ToString("N0"),
                qoo10_saleamount = (a.qoo10_saleamount ?? 0).ToString("N2"),
                qoo10_salecount = (a.qoo10_salecount ?? 0).ToString("N0"),
                shopee_saleamount = (a.shopee_saleamount ?? 0).ToString("N2"),
                shopee_salecount = (a.shopee_salecount ?? 0).ToString("N0"),
                shopify_saleamount = (a.shopify_saleamount ?? 0).ToString("N2"),
                shopify_salecount = (a.shopify_salecount ?? 0).ToString("N0"),

                stock_amount = (a.stock_amount ?? 0).ToString("N2"),
                stock_count = (a.stock_count).ToString("N0"),
                walmart_saleamount = (a.walmart_saleamount ?? 0).ToString("N2"),
                walmart_salecount = (a.walmart_salecount ?? 0).ToString("N0"),
                a.warehouse_code,
                a.warehouse_name,
                a.warehouse_type,
                wish_saleamount = (a.wish_saleamount ?? 0).ToString("N2"),
                wish_salecount = (a.wish_salecount ?? 0).ToString("N0"),

                a.bailun_sku,
                a.bailun_sku_name,
                day = a.day.HasValue?a.day.Value.ToString("yyyy-MM-dd"):"",
                sku_developtime = a.sku_developtime.ToString("yyyy-MM-dd HH:mm:ss"),
                salecount = a.salecount.ToString("N0")
            });


            return JsonConvert.SerializeObject(new
            {
                total = total,
                rows = list,
                count_row = new
                {
                    warehouse_name = "合计",
                    aliexpress_saleamount = (objCount.aliexpress_saleamount ?? 0).ToString("N2"),
                    aliexpress_salecount = (objCount.aliexpress_salecount ?? 0).ToString("N0"),
                    amazon2B_saleamount = (objCount.amazon2B_saleamount ?? 0).ToString("N2"),
                    amazon2B_salecount = (objCount.amazon2B_salecount ?? 0).ToString("N0"),
                    amazon_saleamount = (objCount.amazon_saleamount ?? 0).ToString("N2"),
                    amazon_salecount = (objCount.amazon_salecount ?? 0).ToString("N0"),

                    ebay_saleamount = (objCount.ebay_saleamount ?? 0).ToString("N2"),
                    ebay_salecount = (objCount.ebay_salecount ?? 0).ToString("N0"),
                    fba_saleamount = (objCount.fba_saleamount ?? 0).ToString("N2"),
                    fba_salecount = (objCount.fba_salecount ?? 0).ToString("N0"),
                    jollychic_saleamount = (objCount.jollychic_saleamount ?? 0).ToString("N2"),
                    jollychic_salecount = (objCount.jollychic_salecount ?? 0).ToString("N0"),
                    joom_saleamount = (objCount.joom_saleamount ?? 0).ToString("N2"),
                    joom_salecount = (objCount.joom_salecount ?? 0).ToString("N0"),
                    marketing_saleamount = (objCount.marketing_saleamount ?? 0).ToString("N2"),
                    marketing_salecount = (objCount.marketing_salecount ?? 0).ToString("N0"),
                    mymall_saleamount = (objCount.mymall_saleamount ?? 0).ToString("N2"),
                    mymall_salecount = (objCount.mymall_salecount ?? 0).ToString("N0"),

                    opensky_saleamount = (objCount.opensky_saleamount ?? 0).ToString("N2"),
                    opensky_salecount = (objCount.opensky_salecount ?? 0).ToString("N0"),
                    priceminister_saleamount = (objCount.priceminister_saleamount ?? 0).ToString("N2"),
                    priceminister_salecount = (objCount.priceminister_salecount ?? 0).ToString("N0"),
                    qoo10_saleamount = (objCount.qoo10_saleamount ?? 0).ToString("N2"),
                    qoo10_salecount = (objCount.qoo10_salecount ?? 0).ToString("N0"),
                    shopee_saleamount = (objCount.shopee_saleamount ?? 0).ToString("N2"),
                    shopee_salecount = (objCount.shopee_salecount ?? 0).ToString("N0"),
                    shopify_saleamount = (objCount.shopify_saleamount ?? 0).ToString("N2"),
                    shopify_salecount = (objCount.shopify_salecount ?? 0).ToString("N0"),

                    stock_amount = (objCount.stock_amount ?? 0).ToString("N2"),
                    stock_count = (objCount.stock_count).ToString("N0"),
                    walmart_saleamount = (objCount.walmart_saleamount ?? 0).ToString("N2"),
                    walmart_salecount = (objCount.walmart_salecount ?? 0).ToString("N0"),
                    wish_saleamount = (objCount.wish_saleamount ?? 0).ToString("N2"),
                    wish_salecount = (objCount.wish_salecount ?? 0).ToString("N0"),
                    salecount = objCount.salecount.ToString("N0"),
                }
            });

        }

        /// <summary>
        /// 导出数据仓销量分析报表
        /// </summary>
        /// <param name="warehousecode"></param>
        /// <param name="startcount"></param>
        /// <param name="endcount"></param>
        /// <param name="devstart"></param>
        /// <param name="devend"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="bailun_sku"></param>
        /// <returns></returns>
        public ActionResult ExportSkuWarehouseSale(string warehousecode, int? startcount, int? endcount, DateTime? devstart, DateTime? devend, DateTime? start, DateTime? end, string bailun_sku)
        {
            var total = 0;
            var services = new Services.InventoryServices();

            var obj = services.ListSkuWarehouseSales(new Models.BtTableParameter { offset = 0,limit=0 }, warehousecode, startcount, endcount, devstart, devend, start, end, bailun_sku, ref total);

            var colNames = new List<string>() { "仓库名称","Sku","库存数量","库存金额", "速卖通销量"
                ,"速卖通销售额","亚马逊销量","亚马逊销售额","FBA销量","FBA销售额"
                ,"Ebay销量","Ebay销售额","Wish销量","Wish销售额"
                ,"亚马逊2B销量","亚马逊2B销售额","jollychic销量","jollychic销售额","Joom销量"
                ,"Joom销售额","Marketing销量","Marketing销售额","mymall销量","mymall销售额"
                ,"opensku销量","opensky销售额","priceminister销量","priceminister销售额"
                ,"qoo10销量","qoo10销售额","shopee销量","shopee销售额","shopify销量"
                ,"shopify销售额","walmart销量","walmart销售额","总销量"
            };

            var list = new List<string>();

            foreach (var item in obj)
            {
                var str = item.warehouse_name + "|" + item.bailun_sku + "|" + item.stock_count + "|" + item.stock_amount + "|" + (item.aliexpress_salecount ?? 0) + "|" +
                    (item.aliexpress_saleamount ?? 0) + "|" + (item.amazon_salecount ?? 0) + "|" + (item.amazon_saleamount ?? 0) + "|" + (item.fba_salecount ?? 0) + "|" + (item.fba_saleamount ?? 0) + "|" +
                    (item.ebay_salecount ?? 0) + "|" + (item.ebay_saleamount ?? 0) + "|" + (item.wish_salecount ?? 0) + "|" + (item.wish_saleamount ?? 0) + "|" +
                    (item.amazon2B_salecount ?? 0) + "|" + (item.amazon2B_saleamount ?? 0) + "|" + (item.jollychic_salecount ?? 0) + "|" + (item.jollychic_saleamount ?? 0) + "|" + (item.joom_salecount ?? 0) + "|" +
                    (item.joom_saleamount ?? 0) + "|" + (item.marketing_salecount ?? 0) + "|" + (item.marketing_saleamount ?? 0) + "|" + (item.mymall_salecount ?? 0) + "|" + (item.mymall_saleamount ?? 0) + "|" +
                    (item.opensky_salecount ?? 0) + "|" + (item.opensky_saleamount ?? 0) + "|" + (item.priceminister_salecount ?? 0) + "|" + (item.priceminister_saleamount ?? 0) + "|" +
                    (item.qoo10_salecount ?? 0) + "|" + (item.qoo10_saleamount ?? 0) + "|" + (item.shopee_salecount ?? 0) + "|" + (item.shopee_saleamount ?? 0) + "|" + (item.shopify_salecount ?? 0) + "|" +
                    (item.shopify_saleamount ?? 0) + "|" + (item.walmart_salecount ?? 0) + "|" + (item.walmart_saleamount ?? 0) + "|" + (item.salecount);

                list.Add(str);
            }

            var guid = Guid.NewGuid().ToString();
            var filename = "数据仓销量分析报表 " + DateTime.Now.ToString("yyyyMMddHHmmss");
            var filepath = _hostingEnvironment.WebRootPath + "\\Files\\Report\\" + DateTime.Now.ToString("yyyy-MM-dd") + "\\";
            ToCSV(list, colNames, guid, filepath);

            var ms = new System.IO.MemoryStream();
            using (var f = new System.IO.FileStream(filepath + guid + ".csv", System.IO.FileMode.Open))
            {
                f.CopyTo(ms);
            }

            ms.Position = 0;
            return File(ms, "text/csv", filename + ".csv");

        }

        #endregion

        #region 进销存报表

        /// <summary>
        /// 仓库进销存
        /// </summary>
        /// <returns></returns>
        public ActionResult StockSellWarehouse()
        {
            return View();
        }

        /// <summary>
        /// 仓库进销存Json
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehousetype">仓库类型</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="start">期初时间</param>
        /// <param name="end">期末时间</param>
        /// <returns></returns>
        public string StockSellWarehouseJson(Models.BtTableParameter parameter,string warehousetype,string warehouse,DateTime start,DateTime end)
        {
            var total = 0;
            //var obj = new Services.InventoryServices().

            return "";
        }

        /// <summary>
        /// Sku进销存报表
        /// </summary>
        /// <returns></returns>
        public ActionResult StockSellSku()
        {
            return View();
        }


        #endregion

    }
}
 