WEBUI/WMS.BZWeb/Areas/CPManager/Controllers/ReportController.cs

using MathNet.Numerics.Statistics;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Data;
using System.Web;
using WMS.BZModels.Dto.CP.BaseMatinfoDtos;
using WMS.BZModels.Dto.CP.BillInvDtos;
using WMS.BZModels.Dto.CP.ReportDtos;
using WMS.BZModels.Dto.CP.SysConDtos;
using WMS.BZServices.CP;
using WMS.Info;
using WMS.Util;

namespace WMS.BZWeb.Areas.CPManager.Controllers
{
    [Area("CPManager")]
    public class ReportController : MvcControllerBase
    {
        private readonly ReportService _report;
        private readonly SysConService _syscon;

        public ReportController(ReportService report, SysConService syscon)
        {
            _report = report;
            _syscon = syscon;
        }
        #region 页面
        public IActionResult Index()
        {
            return View();
        }
        public IActionResult cellreportIndex()
        {
            return View();
        }

        public IActionResult StockKeepReportIndex()
        {
            return View();
        }

        public IActionResult MatNameNetWeightReport()
        {
            return View();
        }
        #endregion
        public ActionResult GetPageList(string pagination, string queryJson)
        {
            Pagination paginationobj = InitPagination(pagination);
            var query = new ReportQueryDto();
            if (!string.IsNullOrEmpty(queryJson))
            {
                query = JsonConvert.DeserializeObject<ReportQueryDto>(queryJson);
            }

            var lists = _report.GetPageList(paginationobj, query ?? new ReportQueryDto());

            var jsonData = new
            {
                rows = lists.Result,
                total = lists.TotalPage,
                page = lists.PageIndex,
                records = lists.TotalNum
            };
            return Success(jsonData);
        }

        [HttpPost]
        public IActionResult ExportExcel(string fileName, string queryJson, string exportField)
        {
            var query = new ReportQueryDto();
            try
            {         
                if (!string.IsNullOrEmpty(queryJson))
                {
                    query = JsonConvert.DeserializeObject<ReportQueryDto>(queryJson);
                }
            }
            catch (Exception ex)
            {
            }
            //设置导出格式
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.Title = HttpUtility.UrlDecode(fileName);
            excelconfig.TitleFont = "微软雅黑";
            excelconfig.TitlePoint = 15;
            excelconfig.FileName = HttpUtility.UrlDecode(fileName) + ".xls";
            excelconfig.IsAllSizeColumn = true;
            excelconfig.ColumnEntity = new List<ColumnModel>();

            List<jfGridModel> columnList = exportField.ToList<jfGridModel>();
            if (columnList.Count == 0)
            {
                throw new Exception("未找到表头");
            }
            //行数据
            var lists = _report.GetList(query ?? new ReportQueryDto());
            var dataJson = JsonConvert.SerializeObject(lists);
            DataTable rowData = dataJson.ToTable();
            //写入Excel表头
            Dictionary<string, string> exportFieldMap = new Dictionary<string, string>();
            if (!string.IsNullOrEmpty(exportField))
            {
                string[] exportFields = exportField.Split(',');
                foreach (var field in exportFields)
                {
                    if (!exportFieldMap.ContainsKey(field))
                    {
                        exportFieldMap.Add(field, "1");
                    }
                }
            }

            foreach (var columnModel in columnList)
            {

                excelconfig.ColumnEntity.Add(new ColumnModel()
                {
                    // Column =string.IsNullOrEmpty(columnModel.sortname)? columnModel.name: columnModel.sortname,
                    Column = columnModel.name,
                    ExcelColumn = columnModel.label,
                    Alignment = columnModel.align,
                    Width = columnModel.name.Length
                });

            }
            return File(ExcelHelper.NewExportMemoryStream(rowData, excelconfig), "application/ms-excel", excelconfig.FileName);
        }


        [HttpPost]
        public IActionResult ExportLocationUsageReportExcel(string fileName, string queryJson, string exportField)
        {            
            //设置导出格式
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.Title = HttpUtility.UrlDecode(fileName);
            excelconfig.TitleFont = "微软雅黑";
            excelconfig.TitlePoint = 15;
            excelconfig.FileName = HttpUtility.UrlDecode(fileName) + ".xls";
            excelconfig.IsAllSizeColumn = true;
            excelconfig.ColumnEntity = new List<ColumnModel>();

            List<jfGridModel> columnList = exportField.ToList<jfGridModel>();
            if (columnList.Count == 0)
            {
                throw new Exception("未找到表头");
            }
            //行数据
            var lists = _report.GetLocationUsageReportList();
            var dataJson = JsonConvert.SerializeObject(lists);
            DataTable rowData = dataJson.ToTable();
            //写入Excel表头
            Dictionary<string, string> exportFieldMap = new Dictionary<string, string>();
            if (!string.IsNullOrEmpty(exportField))
            {
                string[] exportFields = exportField.Split(',');
                foreach (var field in exportFields)
                {
                    if (!exportFieldMap.ContainsKey(field))
                    {
                        exportFieldMap.Add(field, "1");
                    }
                }
            }

            foreach (var columnModel in columnList)
            {

                excelconfig.ColumnEntity.Add(new ColumnModel()
                {
                    // Column =string.IsNullOrEmpty(columnModel.sortname)? columnModel.name: columnModel.sortname,
                    Column = columnModel.name,
                    ExcelColumn = columnModel.label,
                    Alignment = columnModel.align,
                    Width = columnModel.name.Length
                });

            }
            return File(ExcelHelper.NewExportMemoryStream(rowData, excelconfig), "application/ms-excel", excelconfig.FileName);
        }

        [HttpPost]
        public IActionResult ExportStockKeepReportExcel(string fileName, string queryJson, string exportField)
        {
            var query = new BillInvNowQueryDto();
            try
            {
               
                if (!string.IsNullOrEmpty(queryJson))
                {
                    query = JsonConvert.DeserializeObject<BillInvNowQueryDto>(queryJson);
                }
            }
            catch (Exception ex)
            {
            }
            //设置导出格式
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.Title = HttpUtility.UrlDecode(fileName);
            excelconfig.TitleFont = "微软雅黑";
            excelconfig.TitlePoint = 15;
            excelconfig.FileName = HttpUtility.UrlDecode(fileName) + ".xls";
            excelconfig.IsAllSizeColumn = true;
            excelconfig.ColumnEntity = new List<ColumnModel>();

            List<jfGridModel> columnList = exportField.ToList<jfGridModel>();
            if (columnList.Count == 0)
            {
                throw new Exception("未找到表头");
            }
            //行数据
            var lists = _report.GetStockKeepReports(query ?? new BillInvNowQueryDto());
            var dataJson = JsonConvert.SerializeObject(lists);
            DataTable rowData = dataJson.ToTable();
            //写入Excel表头
            Dictionary<string, string> exportFieldMap = new Dictionary<string, string>();
            if (!string.IsNullOrEmpty(exportField))
            {
                string[] exportFields = exportField.Split(',');
                foreach (var field in exportFields)
                {
                    if (!exportFieldMap.ContainsKey(field))
                    {
                        exportFieldMap.Add(field, "1");
                    }
                }
            }

            foreach (var columnModel in columnList)
            {

                excelconfig.ColumnEntity.Add(new ColumnModel()
                {
                    // Column =string.IsNullOrEmpty(columnModel.sortname)? columnModel.name: columnModel.sortname,
                    Column = columnModel.name,
                    ExcelColumn = columnModel.label,
                    Alignment = columnModel.align,
                    Width = columnModel.name.Length
                });

            }
            return File(ExcelHelper.NewExportMemoryStream(rowData, excelconfig), "application/ms-excel", excelconfig.FileName);
        }

        [HttpPost]
        public IActionResult ExportCellListReportExcel(string fileName, string queryJson, string exportField)
        {
            var query = new ReportQueryDto();
            try
            {
                if (!string.IsNullOrEmpty(queryJson))
                {
                    query = JsonConvert.DeserializeObject<ReportQueryDto>(queryJson);
                }
            }
            catch (Exception ex)
            {
            }
            //设置导出格式
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.Title = HttpUtility.UrlDecode(fileName);
            excelconfig.TitleFont = "微软雅黑";
            excelconfig.TitlePoint = 15;
            excelconfig.FileName = HttpUtility.UrlDecode(fileName) + ".xls";
            excelconfig.IsAllSizeColumn = true;
            excelconfig.ColumnEntity = new List<ColumnModel>();

            List<jfGridModel> columnList = exportField.ToList<jfGridModel>();
            if (columnList.Count == 0)
            {
                throw new Exception("未找到表头");
            }
            //行数据
            var lists = _report.GetList(query ?? new ReportQueryDto());
            var dataJson = JsonConvert.SerializeObject(lists);
            DataTable rowData = dataJson.ToTable();
            //写入Excel表头
            Dictionary<string, string> exportFieldMap = new Dictionary<string, string>();
            if (!string.IsNullOrEmpty(exportField))
            {
                string[] exportFields = exportField.Split(',');
                foreach (var field in exportFields)
                {
                    if (!exportFieldMap.ContainsKey(field))
                    {
                        exportFieldMap.Add(field, "1");
                    }
                }
            }

            foreach (var columnModel in columnList)
            {

                excelconfig.ColumnEntity.Add(new ColumnModel()
                {
                    // Column =string.IsNullOrEmpty(columnModel.sortname)? columnModel.name: columnModel.sortname,
                    Column = columnModel.name,
                    ExcelColumn = columnModel.label,
                    Alignment = columnModel.align,
                    Width = columnModel.name.Length
                });

            }
            return File(ExcelHelper.NewExportMemoryStream(rowData, excelconfig), "application/ms-excel", excelconfig.FileName);
        }

        [HttpPost]
        public IActionResult ExportMatNameNetWeightCategoryExcel(string fileName, string queryJson, string exportField)
        {
            var query = new BillInvNowQueryDto();
            try
            {
                if (!string.IsNullOrEmpty(queryJson))
                {
                    query = JsonConvert.DeserializeObject<BillInvNowQueryDto>(queryJson);
                }
            }
            catch (Exception ex)
            {
            }
            //设置导出格式
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.Title = HttpUtility.UrlDecode(fileName);
            excelconfig.TitleFont = "微软雅黑";
            excelconfig.TitlePoint = 15;
            excelconfig.FileName = HttpUtility.UrlDecode(fileName) + ".xls";
            excelconfig.IsAllSizeColumn = true;
            excelconfig.ColumnEntity = new List<ColumnModel>();

            List<jfGridModel> columnList = exportField.ToList<jfGridModel>();
            if (columnList.Count == 0)
            {
                throw new Exception("未找到表头");
            }
            //行数据
            var lists = _report.GetMatNameNetWeightCategorys(query ?? new BillInvNowQueryDto());
            var dataJson = JsonConvert.SerializeObject(lists);
            DataTable rowData = dataJson.ToTable();
            //写入Excel表头
            Dictionary<string, string> exportFieldMap = new Dictionary<string, string>();
            if (!string.IsNullOrEmpty(exportField))
            {
                string[] exportFields = exportField.Split(',');
                foreach (var field in exportFields)
                {
                    if (!exportFieldMap.ContainsKey(field))
                    {
                        exportFieldMap.Add(field, "1");
                    }
                }
            }

            foreach (var columnModel in columnList)
            {

                excelconfig.ColumnEntity.Add(new ColumnModel()
                {
                    // Column =string.IsNullOrEmpty(columnModel.sortname)? columnModel.name: columnModel.sortname,
                    Column = columnModel.name,
                    ExcelColumn = columnModel.label,
                    Alignment = columnModel.align,
                    Width = columnModel.name.Length
                });

            }
            return File(ExcelHelper.NewExportMemoryStream(rowData, excelconfig), "application/ms-excel", excelconfig.FileName);
        }

        public ActionResult GetLocationUsageReportList()
        {
            var lists = _report.GetLocationUsageReportList();
            Pagination paginationobj = InitPagination(null);
            var tunnellists = _syscon.GetTunnelList(paginationobj, new TunnelQueryDto());
            if (tunnellists != null && tunnellists.Result.Any())
            {
                //更新巷道状态
                lists.ForEach(s =>
                {
                    var conf = tunnellists.Result.Where(m => m.Tunnel == s.Tunnel).ToList();
                    if (conf != null)
                    {
                        if (conf.Where(o => o.Name.Contains("入库")).Any())
                        {
                            s.InStates = conf.Where(o => o.Name.Contains("入库")).First().StatuName;
                        }
                        if (conf.Where(o => o.Name.Contains("出库")).Any())
                        {
                            s.OutStates = conf.Where(o => o.Name.Contains("出库")).First().StatuName;
                        }
                    }
                });
            }
            //var totallists= new List<dynamic>(){     new  {  UnitName="总货位",qty= lists.Sum(s=>s.AllLocationTotal)},
            // new   {  UnitName="有效货位",qty= lists.Sum(s=>s.CanUseLocation)},
            // new  { UnitName="空余货位",qty= lists.Sum(s=>s.SpareLocation)},
            // new  {  UnitName="锁定货位",qty= lists.Sum(s=>s.LockLocation)},
            // new  {  UnitName="停用货位",qty= lists.Sum(s=>s.StopLocation)},
            // new  {  UnitName="有料货位",qty= lists.Sum(s=>s.MaterilLocation)}};

            lists.Add(new LocationUsageReportViewDto()
            {

                Tunnel = "",
                WarehouseName = "合计：",
                AllLocationTotal = lists.Sum(s => s.AllLocationTotal),
                CanUseLocation = lists.Sum(s => s.CanUseLocation),
                SpareLocation = lists.Sum(s => s.SpareLocation),
                LockLocation = lists.Sum(s => s.LockLocation),
                StopLocation = lists.Sum(s => s.StopLocation),
                MaterilLocation = lists.Sum(s => s.MaterilLocation),

            });
            return Success(lists);
        }

        public IActionResult GetStockKeepReportList(string pagination, string queryJson)
        {
            Pagination paginationobj = InitPagination(pagination);
            var query = new BillInvNowQueryDto();
            if (!string.IsNullOrEmpty(queryJson))
            {
                query = JsonConvert.DeserializeObject<BillInvNowQueryDto>(queryJson);
            }

            var lists = _report.GetStockKeepReportList(paginationobj, query ?? new BillInvNowQueryDto());
            var jsonData = new
            {
                rows = lists.Result,
                total = lists.TotalPage,
                page = lists.PageIndex,
                records = lists.TotalNum
            };
            return Success(jsonData);
        }
        /// <summary>
        /// 获取货位使用率
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="queryJson"></param>
        /// <returns></returns>
        public ActionResult GetCellList(string pagination, string queryJson)
        {
            Pagination paginationobj = InitPagination(pagination);
            var query = new ReportQueryDto();
            if (!string.IsNullOrEmpty(queryJson))
            {
                query = JsonConvert.DeserializeObject<ReportQueryDto>(queryJson);
            }
            var lists = _report.GetPageList(paginationobj, query ?? new ReportQueryDto());
            var jsonData = new
            {
                rows = lists.Result,
                total = lists.TotalPage,
                page = lists.PageIndex,
                records = lists.TotalNum
            };
            return Success(jsonData);

        }

        public ActionResult GetMatNameNetWeightCategory(string pagination, string queryJson)
        {
            Pagination paginationobj = InitPagination(pagination);
            var query = new BillInvNowQueryDto();
            if (!string.IsNullOrEmpty(queryJson))
            {
                query = JsonConvert.DeserializeObject<BillInvNowQueryDto>(queryJson);
            }

            var lists = _report.GetMatNameNetWeightCategory(paginationobj, query ?? new BillInvNowQueryDto());

            var jsonData = new
            {
                rows = lists.Result,
                total = lists.TotalPage,
                page = lists.PageIndex,
                records = lists.TotalNum
            };
            return Success(jsonData);
        }

    }
}