public async Task <ActionResult <object> > List([FromBody] ContractReport_ContractReportFilterDTO filter) { if (UnAuthorization) { return(Forbid()); } var query = DataContext.Contract.Where(p => !p.DeletedAt.HasValue).AsQueryable(); if (filter.Code.HasValue) { query = query.Where(p => p.Code, filter.Code); } if (filter.ContractTypeId.HasValue) { query = query.Where(p => p.ContractTypeId, filter.ContractTypeId); } if (filter.TotalValue.HasValue) { query = query.Where(p => p.TotalValue, filter.TotalValue); } if (filter.CompanyId.HasValue) { query = query.Where(p => p.CompanyId, filter.CompanyId); } if (filter.OpportunityId.HasValue) { query = query.Where(p => p.OpportunityId, filter.OpportunityId); } if (filter.OrganizationId.HasValue) { query = query.Where(p => p.OrganizationId, filter.OrganizationId); } if (filter.AppUserId.HasValue) { query = query.Where(p => p.AppUserId, filter.AppUserId); } if (filter.ExpirationDate.HasValue) { query = query.Where(p => p.ExpirationDate, filter.ExpirationDate); } if (filter.CreatedAt.HasValue) { query = query.Where(p => p.CreatedAt, filter.CreatedAt); } if (filter.PaymentPercentage.HasValue) { query = query.Where(p => p.ContractPaymentHistories.Sum(p => p.PaymentPercentage) >= filter.PaymentPercentage.GreaterEqual && p.ContractPaymentHistories.Sum(p => p.PaymentPercentage) <= filter.PaymentPercentage.LessEqual); } return(query.Select(p => new { Id = p.Id, Code = p.Code, ContractTypeName = p.ContractType == null ? "" : p.ContractType.Name, TotalValue = p.TotalValue, ValidityDate = p.ValidityDate, ExpirationDate = p.ExpirationDate, PaymentAmount = p.ContractPaymentHistories.Sum(p => p.PaymentAmount), PaymentPercentage = p.ContractPaymentHistories.Sum(p => p.PaymentPercentage), CompanyName = p.Company == null ? "" : p.Company.Name, OpportunityName = p.Opportunity == null ? "" : p.Opportunity.Name, OrganizationName = p.Organization == null ? "" : p.Organization.Name, AppUserName = p.AppUser == null ? "" : p.AppUser.DisplayName, }).Skip(filter.Skip).Take(filter.Take).ToList()); }
public async Task <ActionResult> Export([FromBody] ContractReport_ContractReportFilterDTO filter) { if (UnAuthorization) { return(Forbid()); } if (!ModelState.IsValid) { throw new BindException(ModelState); } var query = DataContext.Contract.Where(p => !p.DeletedAt.HasValue).AsQueryable(); if (filter.Code.HasValue) { query = query.Where(p => p.Code, filter.Code); } if (filter.ContractTypeId.HasValue) { query = query.Where(p => p.ContractTypeId, filter.ContractTypeId); } if (filter.TotalValue.HasValue) { query = query.Where(p => p.TotalValue, filter.TotalValue); } if (filter.CompanyId.HasValue) { query = query.Where(p => p.CompanyId, filter.CompanyId); } if (filter.OpportunityId.HasValue) { query = query.Where(p => p.OpportunityId, filter.OpportunityId); } if (filter.OrganizationId.HasValue) { query = query.Where(p => p.OrganizationId, filter.OrganizationId); } if (filter.AppUserId.HasValue) { query = query.Where(p => p.AppUserId, filter.AppUserId); } if (filter.ExpirationDate.HasValue) { query = query.Where(p => p.ExpirationDate, filter.ExpirationDate); } if (filter.CreatedAt.HasValue) { query = query.Where(p => p.CreatedAt, filter.CreatedAt); } if (filter.PaymentPercentage.HasValue) { query = query.Where(p => p.ContractPaymentHistories.Sum(p => p.PaymentPercentage) >= filter.PaymentPercentage.GreaterEqual && p.ContractPaymentHistories.Sum(p => p.PaymentPercentage) <= filter.PaymentPercentage.LessEqual); } int STT = 1; List <ContractReportExport> data = new List <ContractReportExport>(); foreach (var p in query) { var ContractReportExport = new ContractReportExport(); ContractReportExport.STT = (STT + 1).ToString(); ContractReportExport.Code = p.Code; ContractReportExport.CreatedAt = p.CreatedAt; ContractReportExport.ContractTypeName = p.ContractType == null ? "" : p.ContractType.Name; ContractReportExport.TotalValue = string.Format(System.Globalization.CultureInfo.GetCultureInfo("vi-VN"), "{0:c}", p.TotalValue); ContractReportExport.ValidityDate = p.ValidityDate.ToString("dd-MM-yyyy"); ContractReportExport.ExpirationDate = p.ExpirationDate.ToString("dd-MM-yyyy"); ContractReportExport.PaymentAmount = string.Format(System.Globalization.CultureInfo.GetCultureInfo("vi-VN"), "{0:c}", p.ContractPaymentHistories.Sum(p => p.PaymentAmount)); ContractReportExport.PaymentPercentage = p.ContractPaymentHistories.Sum(p => p.PaymentPercentage); ContractReportExport.CompanyName = p.Company == null ? "" : p.Company.Name; ContractReportExport.OpportunityName = p.Opportunity == null ? "" : p.Opportunity.Name; ContractReportExport.OrganizationName = p.Organization == null ? "" : p.Organization.Name; ContractReportExport.AppUserName = p.AppUser == null ? "" : p.AppUser.DisplayName; data.Add(ContractReportExport); } 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.CreatedAt?.GreaterEqual == null ? minDate : filter.CreatedAt.GreaterEqual.Value; DateTime End = filter.CreatedAt?.LessEqual == null ? maxDate : filter.CreatedAt.LessEqual.Value; string path = "Templates/ContractReport.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.data = data; using (var document = StaticParams.DocumentFactory.Open(input, output, "xlsx")) { document.Process(Data); }; return(File(output.ToArray(), "application/octet-stream", "BaoCaoTicket.xlsx")); }