using SqlSugar;
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using WIDESEA_Core;
using WIDESEA_Core.BaseRepository;
using WIDESEA_Core.DB.Models;
using WIDESEA_Core.Helper;
using WIDESEA_Core.HostedService;
using WIDESEA_DTO.Stock;
using WIDESEA_IStockService;
using WIDESEA_Model.Models;
using System.Linq;
using WIDESEA_Common.WareHouseEnum;
using Magicodes.ExporterAndImporter.Core;
using Magicodes.ExporterAndImporter.Excel;
using WIDESEA_Common.LocationEnum;
using WIDESEA_Core.Utilities;

namespace WIDESEA_StockService
{
    public partial class MaterielnfoStatisticsService : IMaterielnfoStatisticsService
    {
        public readonly IUnitOfWorkManage _unitOfWorkManage;
        public readonly SqlSugarClient _dbBase;

        public MaterielnfoStatisticsService(IUnitOfWorkManage unitOfWorkManage) {
            _unitOfWorkManage = unitOfWorkManage;
            _dbBase = unitOfWorkManage.GetDbClient();
        }

        public virtual PageGridData<MaterielnfoStatisticsDTO> GetPageGridData(PageDataOptions options)
        {
            try
            {

                List<MaterielnfoStatisticsDTO> materielnfoStatistics = new List<MaterielnfoStatisticsDTO>();
                ISugarQueryable<Dt_StockInfoDetail> sugarQueryable1 = _dbBase.Queryable<Dt_StockInfoDetail>();

                string whereStoDe = GetDataRole(typeof(Dt_StockInfoDetail));
                sugarQueryable1 = sugarQueryable1.Where(whereStoDe);


                string whereSto = GetDataRole(typeof(Dt_StockInfo));
                ISugarQueryable<Dt_StockInfo> sugarQueryable = _dbBase.Queryable<Dt_StockInfo>().Where(whereSto);
                if (!string.IsNullOrEmpty(options.Wheres))
                {
                    try
                    {
                        List<SearchParameters> searchParametersList = options.Wheres.DeserializeObject<List<SearchParameters>>();
                        if (searchParametersList.Count > 0)
                        {
                            {
                                SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfoDetail.MaterielCode).FirstLetterToLower());
                                if (searchParameters != null)
                                {
                                    sugarQueryable1 = sugarQueryable1.Where(x => x.MaterielCode.Contains(searchParameters.Value));
                                }
                            }
                            {
                                SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfo.WarehouseId).FirstLetterToLower());
                                if (searchParameters != null) {
                                    sugarQueryable = sugarQueryable.Where(x => x.WarehouseId.Equals(searchParameters.Value));                             
                                }
                            }
                            {
                                SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfo.StockStatus).FirstLetterToLower());
                                if(searchParameters!= null)
                                {
                                    sugarQueryable = sugarQueryable.Where(x => x.StockStatus.Equals(searchParameters.Value));
                                }
                            }
                            {
                                SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfoDetail.MaterielName).FirstLetterToLower());
                                if (searchParameters != null)
                                {
                                    sugarQueryable1 = sugarQueryable1.Where(x => x.MaterielName.Contains(searchParameters.Value));
                                }
                            }

                        }

                    }
                    catch(Exception ex)
                    {
                    
                    }
                }
                    

                ISugarQueryable<MaterielnfoStatisticsDTO> sugarQueryable2 = sugarQueryable1.InnerJoin(sugarQueryable, (b, a) => b.StockId == a.Id).Where((b,a)=>a.LocationCode!="" && a.LocationCode!=null).Select((b, a)
                    => new MaterielnfoStatisticsDTO
                    {
                        WarehouseId = a.WarehouseId,
                        MaterielCode = b.MaterielCode,
                        MaterielName = b.MaterielName,
                        StockQuantity =(decimal) b.StockQuantity,
                        OutboundQuantity =(decimal) b.OutboundQuantity,
                        StockStatus = a.StockStatus,
                        Unit = b.Unit,
                        Remark = a.Remark,
                        
                    });
                // 获取所有数据
                var allData = sugarQueryable2.ToList();

                // 使用字典进行分组汇总
                var groupedData = new Dictionary<string, MaterielnfoStatisticsDTO>();
                foreach (var item in allData)
                {
                    // 确保初始值为decimal类型
                    if (groupedData.ContainsKey(item.MaterielCode))
                    {
                        groupedData[item.MaterielCode].StockQuantity += Convert.ToDecimal(item.StockQuantity);
                        groupedData[item.MaterielCode].OutboundQuantity += Convert.ToDecimal(item.OutboundQuantity);
                    }
                    else
                    {
                        groupedData[item.MaterielCode] = new MaterielnfoStatisticsDTO
                        {
                            WarehouseId = item.WarehouseId,
                            MaterielCode = item.MaterielCode,
                            MaterielName = item.MaterielName,
                            StockQuantity = Convert.ToDecimal(item.StockQuantity),
                            OutboundQuantity = Convert.ToDecimal(item.OutboundQuantity),
                            StockStatus = item.StockStatus,
                            Unit = item.Unit,
                            Remark = item.Remark
                        };
                    }
                }

                // 转换为列表
                materielnfoStatistics = groupedData.Values.ToList();

                // 分页处理
                int startIndex = (options.Page - 1) * options.Rows;
                int endIndex = Math.Min(startIndex + options.Rows, materielnfoStatistics.Count);
                materielnfoStatistics = materielnfoStatistics.GetRange(startIndex, endIndex - startIndex);

                int count = groupedData.Count;

                return new PageGridData<MaterielnfoStatisticsDTO>(count, materielnfoStatistics);
                
            }
            catch(Exception ex) 
            {
            
            }
            return new PageGridData<MaterielnfoStatisticsDTO>();
        }

        public virtual WebResponseContent Export(PageDataOptions options)
        {
            WebResponseContent content = new WebResponseContent();
            try
            {
                Type t = typeof(MaterielnfoStatisticsDTO);

                string savePath = AppDomain.CurrentDomain.BaseDirectory + $"ExcelExport";
                IExporter exporter = new ExcelExporter();
                options.Page = 1;
                options.Rows = 30;
                options.Order = "desc";
                options.Sort = "MaterielCode";
                List<MaterielnfoStatisticsDTO> materielnfoStatistics = new List<MaterielnfoStatisticsDTO>();
                ISugarQueryable<Dt_StockInfoDetail> sugarQueryable1 = _dbBase.Queryable<Dt_StockInfoDetail>();

                string whereStoDe = GetDataRole(typeof(Dt_StockInfoDetail));
                sugarQueryable1 = sugarQueryable1.Where(whereStoDe);


                string whereSto = GetDataRole(typeof(Dt_StockInfo));
                ISugarQueryable<Dt_StockInfo> sugarQueryable = _dbBase.Queryable<Dt_StockInfo>().Where(whereSto);
                if (!string.IsNullOrEmpty(options.Wheres))
                {
                    try
                    {
                        List<SearchParameters> searchParametersList = options.Wheres.DeserializeObject<List<SearchParameters>>();
                        if (searchParametersList.Count > 0)
                        {
                            {
                                SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfoDetail.MaterielCode).FirstLetterToLower());
                                if (searchParameters != null)
                                {
                                    sugarQueryable1 = sugarQueryable1.Where(x => x.MaterielCode.Contains(searchParameters.Value));
                                }
                            }
                            {
                                SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfo.WarehouseId).FirstLetterToLower());
                                if (searchParameters != null)
                                {
                                    sugarQueryable = sugarQueryable.Where(x => x.WarehouseId.Equals(searchParameters.Value));
                                }
                            }
                            {
                                SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfo.StockStatus).FirstLetterToLower());
                                if (searchParameters != null)
                                {
                                    sugarQueryable = sugarQueryable.Where(x => x.StockStatus.Equals(searchParameters.Value));
                                }
                            }
                            {
                                SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfoDetail.MaterielName).FirstLetterToLower());
                                if (searchParameters != null)
                                {
                                    sugarQueryable1 = sugarQueryable1.Where(x => x.MaterielName.Contains(searchParameters.Value));
                                }
                            }

                        }

                    }
                    catch (Exception ex)
                    {

                    }
                }


                ISugarQueryable<MaterielnfoStatisticsDTO> sugarQueryable2 = sugarQueryable1.InnerJoin(sugarQueryable, (b, a) => b.StockId == a.Id).Where((b, a) => a.LocationCode != "" && a.LocationCode != null).Select((b, a)
                    => new MaterielnfoStatisticsDTO
                    {
                        WarehouseId = a.WarehouseId,
                        MaterielCode = b.MaterielCode,
                        MaterielName = b.MaterielName,
                        StockQuantity = (decimal)b.StockQuantity,
                        OutboundQuantity =(decimal) b.OutboundQuantity,
                        StockStatus = a.StockStatus,
                        Unit = b.Unit,
                        Remark = a.Remark,

                    });
                // 获取所有数据
                var allData = sugarQueryable2.ToList();

                // 使用字典进行分组汇总
                var groupedData = new Dictionary<string, MaterielnfoStatisticsDTO>();
                foreach (var item in allData)
                {
                    if (groupedData.ContainsKey(item.MaterielCode))
                    {
                        groupedData[item.MaterielCode].StockQuantity += item.StockQuantity;
                        groupedData[item.MaterielCode].OutboundQuantity += item.OutboundQuantity;
                    }
                    else
                    {
                        groupedData[item.MaterielCode] = new MaterielnfoStatisticsDTO
                        {
                            WarehouseId = item.WarehouseId,
                            MaterielCode = item.MaterielCode,
                            MaterielName = item.MaterielName,
                            StockQuantity = item.StockQuantity,
                            OutboundQuantity = item.OutboundQuantity,
                            StockStatus = item.StockStatus,
                            Unit = item.Unit,
                            Remark = item.Remark
                        };
                    }
                }

                // 转换为列表
                materielnfoStatistics = groupedData.Values.ToList();

                byte[] data = exporter.ExportAsByteArray(materielnfoStatistics).Result;

                string fileName = "物料分类统计.xlsx";

                FileHelper.WriteFile(savePath, fileName, data);

                content = WebResponseContent.Instance.OK(data: savePath + "\\" + fileName);
            }
            catch (Exception ex)
            {
                content = WebResponseContent.Instance.Error(ex.Message);
            }
            return content;
        }
        private string GetDataRole(Type type)
        {
            try
            {
                UserRole? userRole = PermissionDataHostService.UserRoles.FirstOrDefault(x => x.UserId == App.User.UserId);
                if (userRole == null)
                    throw new Exception($"无权限");

                if (type.IsAssignableFrom(typeof(BaseWarehouseEntity)) || type.GetProperty(nameof(BaseWarehouseEntity.WarehouseId)) != null)
                {
                    if (userRole.WarehouseIds.Count > 0)
                    {
                        return $"{nameof(BaseWarehouseEntity.WarehouseId)} in ({userRole.WarehouseIds.Serialize().Replace("[", "").Replace("]", "")})";
                    }

                    else
                        return $"1 != 1";
                }
                else
                {
                    return "1 = 1";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"无权限，{ex.Message}");
            }
        }
    }
}