public async Task <ActionResult> Export([FromBody] OpportunityReport_OpportunityReportFilterDTO filter) { if (UnAuthorization) { return(Forbid()); } var info_vn = System.Globalization.CultureInfo.GetCultureInfo("vi-VN"); if (!ModelState.IsValid) { throw new BindException(ModelState); } // Lấy danh sách Opportunity var query = from t1 in DataContext.Opportunity select t1; if (filter.OpportunityName != null) { query = query.Where(p => p.Name, filter.OpportunityName); } if (filter.OpportunityAmount != null) { query = query.Where(p => p.Amount, filter.OpportunityAmount); } if (filter.OpportunityItemId != null) { query = query.Where(p => p.Amount, filter.OpportunityAmount); } if (filter.OpportunityCompanyId != null) { query = query.Where(p => p.CompanyId, filter.OpportunityCompanyId); } if (filter.OpportunitySaleStageId != null) { query = query.Where(p => p.SaleStageId, filter.OpportunitySaleStageId); } if (filter.OpportunityProbabilityId != null) { query = query.Where(p => p.ProbabilityId, filter.OpportunityProbabilityId); } if (filter.OpportunityClosingDate != null) { query = query.Where(p => p.ClosingDate, filter.OpportunityClosingDate); } if (filter.OpportunityCreatedAt != null) { query = query.Where(p => p.CreatedAt, filter.OpportunityCreatedAt); } if (filter.AppUserId != null) { query = query.Where(p => p.AppUserId, filter.AppUserId); } //Tìm kiếm theo OpportunityItemId if (filter.OpportunityItemId != null) { if (filter.OpportunityItemId.Equal.HasValue) { query = from q in query join ar in DataContext.OpportunityItemMapping on q.Id equals ar.OpportunityId where ar.ItemId == filter.OpportunityItemId.Equal.Value select q; } } var data = query.Distinct().OrderBy(p => p.CreatedAt).ThenBy(p => p.Name).Skip(filter.Skip).Take(filter.Take).Select(p => new OpportunityReport_OpportunityDTO { Id = p.Id, Name = p.Name, ProbabilityName = p.Probability == null ? "" : p.Probability.Name, Amount = p.Amount == null ? "" : p.Amount.Value.ToString(), ClosingDate = p.ClosingDate, CreatedAt = p.CreatedAt, SaleStageName = p.SaleStage == null ? "" : p.SaleStage.Name, CompanyName = p.Company == null ? "" : p.Company.Name, CountItem = p.OpportunityItemMappings == null ? "" : p.OpportunityItemMappings.Count().ToString(), TotalRevenueOfItem = p.OpportunityItemMappings == null ? "" : p.OpportunityItemMappings.Sum(p => (p.RequestQuantity * p.SalePrice)).ToString(), AppUserName = p.AppUser == null ? "" : p.AppUser.DisplayName, ResultName = p.PotentialResult == null ? "" : p.PotentialResult.Name, ContactName = p.OpportunityContactMappings == null ? "" : string.Join(',', p.OpportunityContactMappings.Select(p => p.Contact.Name)), ForecastAmount = p.ForecastAmount.HasValue ? p.ForecastAmount.ToString() : "", LeadSourceName = p.LeadSource == null ? "" : p.LeadSource.Name, OpportunityItemMappings = p.OpportunityItemMappings == null ? null : p.OpportunityItemMappings.Select(p => new OpportunityReport_OpportunityItemMappingDTO { OpportunityId = p.OpportunityId, ItemId = p.ItemId, UnitOfMeasureId = p.UnitOfMeasureId, Quantity = p.Quantity, DiscountPercentage = p.DiscountPercentage, RequestQuantity = p.RequestQuantity, PrimaryPrice = p.PrimaryPrice, SalePrice = p.SalePrice, Discount = p.Discount, VAT = p.VAT, VATOther = p.VATOther, Amount = p.Amount, Factor = p.Factor, Item = p.Item == null ? null : new OpportunityReport_ItemDTO { Id = p.Item.Id, Name = p.Item.Name }, UnitOfMeasure = p.UnitOfMeasure == null ? null : new OpportunityReport_UnitOfMeasureDTO { Name = p.UnitOfMeasure.Name } }).ToList() }).ToList(); OpportunityReport_OpportunityTotalDTO Result = new OpportunityReport_OpportunityTotalDTO(); Result.TotalAmount = data.Sum(p => string.IsNullOrEmpty(p.Amount) ? 0 : decimal.Parse(p.Amount)).ToString(); Result.TotalItem = data.Sum(p => string.IsNullOrEmpty(p.CountItem) ? 0 : decimal.Parse(p.CountItem)).ToString(); Result.TotalForecastAmount = data.Sum(p => string.IsNullOrEmpty(p.ForecastAmount) ? 0 : decimal.Parse(p.ForecastAmount)).ToString(); var maxDate = data.Max(p => p.CreatedAt).HasValue ? data.Max(p => p.CreatedAt).Value : default(DateTime); var minDate = data.Min(p => p.CreatedAt).HasValue ? data.Min(p => p.CreatedAt).Value : default(DateTime); DateTime Start = filter.OpportunityCreatedAt?.GreaterEqual == null ? minDate : filter.OpportunityCreatedAt.GreaterEqual.Value; DateTime End = filter.OpportunityCreatedAt?.LessEqual == null ? maxDate : filter.OpportunityCreatedAt.LessEqual.Value; //Lấy danh sách Opportunity var LtsItemIds = new List <long>(); var OpportunityReportExports = new List <OpportunityReportExport>(); foreach (var opportunity in data.OrderByDescending(p => p.CreatedAt)) { if (opportunity.OpportunityItemMappings.Any()) { foreach (var item in opportunity.OpportunityItemMappings) { OpportunityReportExport obj = new OpportunityReportExport(); obj.Name = opportunity.Name; obj.CompanyName = opportunity.CompanyName; obj.ContactName = opportunity.ContactName; obj.ClosingDate = opportunity.ClosingDate.HasValue ? opportunity.ClosingDate.Value.ToString("dd/MM/yyyy") : ""; obj.ProbabilityName = opportunity.ProbabilityName; obj.ItemName = item.Item == null ? null : item.Item.Name; obj.UnitOfMeasureName = item.UnitOfMeasure == null ? null : item.UnitOfMeasure.Name; obj.SalePrice = item.SalePrice.HasValue ? string.Format(info_vn, "{0:c}", item.SalePrice.Value) : ""; obj.RequestQuantity = item.RequestQuantity; LtsItemIds.Add(item.ItemId); obj.SaleStageName = opportunity.SaleStageName; obj.Amount = string.Format(info_vn, "{0:c}", string.IsNullOrEmpty(opportunity.Amount) ? (decimal)0.00 : decimal.Parse(opportunity.Amount)); obj.ForecastAmount = string.Format(info_vn, "{0:c}", string.IsNullOrEmpty(opportunity.ForecastAmount) ? (decimal)0.00 : decimal.Parse(opportunity.ForecastAmount)); obj.LeadSourceName = opportunity.LeadSourceName; obj.AppUserName = opportunity.AppUserName; obj.Result = opportunity.ResultName; OpportunityReportExports.Add(obj); } } else { OpportunityReportExport obj = new OpportunityReportExport(); obj.Name = opportunity.Name; obj.CompanyName = opportunity.CompanyName; obj.ContactName = opportunity.ContactName; obj.ClosingDate = opportunity.ClosingDate.HasValue ? opportunity.ClosingDate.Value.ToString("dd/MM/yyyy") : ""; obj.ProbabilityName = opportunity.ProbabilityName; obj.SaleStageName = opportunity.SaleStageName; obj.Amount = string.Format(info_vn, "{0:c}", string.IsNullOrEmpty(opportunity.Amount) ? (decimal)0.00 : decimal.Parse(opportunity.Amount)); obj.ForecastAmount = string.Format(info_vn, "{0:c}", string.IsNullOrEmpty(opportunity.ForecastAmount) ? (decimal)0.00 : decimal.Parse(opportunity.ForecastAmount)); obj.LeadSourceName = opportunity.LeadSourceName; obj.AppUserName = opportunity.AppUserName; obj.Result = opportunity.ResultName; OpportunityReportExports.Add(obj); } } var STT = 1; foreach (var item in OpportunityReportExports) { item.STT = STT; STT++; } string path = "Templates/OpportunityReport.xlsx"; byte[] arr = System.IO.File.ReadAllBytes(path); MemoryStream input = new MemoryStream(arr); MemoryStream output = new MemoryStream(); dynamic Data = new ExpandoObject(); Data.Start = Start.AddHours(CurrentContext.TimeZone).ToString("dd-MM-yyyy"); Data.End = End.AddHours(CurrentContext.TimeZone).ToString("dd-MM-yyyy"); Data.OpportunityReportExports = OpportunityReportExports; Data.TotalAmount = string.Format(info_vn, "{0:c}", decimal.Parse(Result.TotalAmount)); Data.TotalItem = LtsItemIds.Distinct().Count(); Data.TotalForecastAmount = String.Format(info_vn, "{0:c}", decimal.Parse(Result.TotalForecastAmount)); using (var document = StaticParams.DocumentFactory.Open(input, output, "xlsx")) { document.Process(Data); }; return(File(output.ToArray(), "application/octet-stream", "BaoCaoCoHoi.xlsx")); }
public async Task <ActionResult <OpportunityReport_OpportunityTotalDTO> > Total([FromBody] OpportunityReport_OpportunityReportFilterDTO filter) { if (UnAuthorization) { return(Forbid()); } if (!ModelState.IsValid) { throw new BindException(ModelState); } List <long> ItemIds = new List <long>(); // Lấy danh sách Opportunity var query = from t1 in DataContext.Opportunity select t1; if (filter.OpportunityName != null) { query = query.Where(p => p.Name, filter.OpportunityName); } if (filter.OpportunityAmount != null) { query = query.Where(p => p.Amount, filter.OpportunityAmount); } if (filter.OpportunityItemId != null) { query = query.Where(p => p.Amount, filter.OpportunityAmount); } if (filter.OpportunityCompanyId != null) { query = query.Where(p => p.CompanyId, filter.OpportunityCompanyId); } if (filter.OpportunitySaleStageId != null) { query = query.Where(p => p.SaleStageId, filter.OpportunitySaleStageId); } if (filter.OpportunityProbabilityId != null) { query = query.Where(p => p.ProbabilityId, filter.OpportunityProbabilityId); } if (filter.OpportunityClosingDate != null) { query = query.Where(p => p.ClosingDate, filter.OpportunityClosingDate); } if (filter.OpportunityCreatedAt != null) { query = query.Where(p => p.CreatedAt, filter.OpportunityCreatedAt); } if (filter.AppUserId != null) { query = query.Where(p => p.AppUserId, filter.AppUserId); } //Tìm kiếm theo OpportunityItemId if (filter.OpportunityItemId != null) { if (filter.OpportunityItemId.Equal.HasValue) { query = from q in query join ar in DataContext.OpportunityItemMapping on q.Id equals ar.OpportunityId where ar.ItemId == filter.OpportunityItemId.Equal.Value select q; } } var data = query.Distinct().OrderBy(p => p.ClosingDate).ThenBy(p => p.Name) //.Skip(filter.Skip) //.Take(filter.Take) .Select(p => new OpportunityReport_OpportunityDTO { Id = p.Id, Name = p.Name, ProbabilityName = p.Probability == null ? "" : p.Probability.Name, Amount = p.Amount == null ? "" : p.Amount.Value.ToString(), ClosingDate = p.ClosingDate, SaleStageName = p.SaleStage == null ? "" : p.SaleStage.Name, CompanyName = p.Company == null ? "" : p.Company.Name, CountItem = p.OpportunityItemMappings == null ? "" : p.OpportunityItemMappings.Count().ToString(), TotalRevenueOfItem = p.OpportunityItemMappings == null ? "" : p.OpportunityItemMappings.Sum(p => (p.RequestQuantity * p.SalePrice)).ToString(), OpportunityItemMappings = p.OpportunityItemMappings == null ? null : p.OpportunityItemMappings.Select(p => new OpportunityReport_OpportunityItemMappingDTO { OpportunityId = p.OpportunityId, ItemId = p.ItemId, UnitOfMeasureId = p.UnitOfMeasureId, Quantity = p.Quantity, DiscountPercentage = p.DiscountPercentage, RequestQuantity = p.RequestQuantity, PrimaryPrice = p.PrimaryPrice, SalePrice = p.SalePrice, Discount = p.Discount, VAT = p.VAT, VATOther = p.VATOther, Amount = p.Amount, Factor = p.Factor, Item = p.Item == null ? null : new OpportunityReport_ItemDTO { Id = p.Item.Id, Name = p.Item.Name }, UnitOfMeasure = p.UnitOfMeasure == null ? null : new OpportunityReport_UnitOfMeasureDTO { Name = p.UnitOfMeasure.Name } }).ToList() }).ToList(); // Tính tổng số sản phẩm khác nhau foreach (var p in data) { var ids = p.OpportunityItemMappings.Select(p => p.ItemId); ItemIds.AddRange(ids); } OpportunityReport_OpportunityTotalDTO OpportunityReport_OpportunityTotalDTO = new OpportunityReport_OpportunityTotalDTO(); OpportunityReport_OpportunityTotalDTO.TotalAmount = data.Sum(p => string.IsNullOrEmpty(p.Amount) ? 0 : decimal.Parse(p.Amount)).ToString(); OpportunityReport_OpportunityTotalDTO.TotalItem = ItemIds.Distinct().Count().ToString(); OpportunityReport_OpportunityTotalDTO.TotalRevenue = data.Sum(p => string.IsNullOrEmpty(p.TotalRevenueOfItem) ? 0 : decimal.Parse(p.TotalRevenueOfItem)).ToString(); return(OpportunityReport_OpportunityTotalDTO); }