public async Task <ActionResult <int> > Count([FromBody] StaffReport_StaffReportFilterDTO filter) { if (UnAuthorization) { return(Forbid()); } var query = DataContext.AppUser.Where(p => !p.DeletedAt.HasValue); if (filter.Username.HasValue) { query = query.Where(p => p.Username, filter.Username); } if (filter.StaffId.HasValue) { query = query.Where(p => p.Id, filter.StaffId); } if (filter.OrganizationId.HasValue) { query = query.Where(p => p.OrganizationId, filter.OrganizationId); } if (filter.CreatedAt.HasValue) { query = query.Where(p => p.CreatedAt, filter.CreatedAt); } return(query.Count()); }
public async Task <ActionResult <object> > List([FromBody] StaffReport_StaffReportFilterDTO filter) { if (UnAuthorization) { return(Forbid()); } var query = DataContext.AppUser.Where(p => !p.DeletedAt.HasValue); if (filter.Username.HasValue) { query = query.Where(p => p.Username, filter.Username); } if (filter.StaffId.HasValue) { query = query.Where(p => p.Id, filter.StaffId); } if (filter.OrganizationId.HasValue) { query = query.Where(p => p.OrganizationId, filter.OrganizationId); } if (filter.CreatedAt.HasValue) { query = query.Where(p => p.CreatedAt, filter.CreatedAt); } return(query.Select(p => new { Username = p.Username, DisplayName = p.DisplayName, OrganizationName = p.Organization == null ? "" : p.Organization.Name, //CountLead = p.CustomerLeadCreators == null ? 0 : p.CustomerLeadCreators.Where(x => !x.DeletedAt.HasValue).Count(), //CountOpportunityComplete = p.OpportunityCreators == null ? 0 : p.OpportunityCreators // .Where(x => !x.DeletedAt.HasValue) // .Where(x => x.CreatorId == p.Id).Count(), //Ratio = ( // p.CustomerLeadCreators.Count() == 0 // ) ? 0 : // (p.OpportunityCreators == null ? 0.00 : p.OpportunityCreators.Where(x => x.CreatorId == p.Id && x.CustomerLeadId.HasValue).Count()) * 100 // / p.CustomerLeadCreators.Count() // , CountContact = p.ContractAppUsers == null ? 0 : p.ContractAppUsers.Where(x => !x.DeletedAt.HasValue).Count(), CountOrder = (p.CustomerSalesOrderSalesEmployees == null ? 0 : p.CustomerSalesOrderSalesEmployees.Where(x => !x.DeletedAt.HasValue).Count()) + (p.DirectSalesOrderSaleEmployees == null ? 0 : p.DirectSalesOrderSaleEmployees.Where(x => !x.DeletedAt.HasValue).Count()), RevenueOrder = (p.CustomerSalesOrderSalesEmployees == null ? 0 : p.CustomerSalesOrderSalesEmployees.Where(x => !x.DeletedAt.HasValue).Sum(p => p.Total)) + (p.DirectSalesOrderSaleEmployees == null ? 0 : p.DirectSalesOrderSaleEmployees.Where(x => !x.DeletedAt.HasValue).Sum(p => p.Total)), CountContract = p.ContractAppUsers == null ? 0 : p.ContractAppUsers.Where(x => !x.DeletedAt.HasValue).Count(), RevenueContract = p.ContractAppUsers == null ? 0 : p.ContractAppUsers.Where(x => !x.DeletedAt.HasValue).Sum(p => p.Total), TotalTicket = p.TicketUsers == null ? 0 : p.TicketUsers.Where(x => !x.DeletedAt.HasValue).Count(), CountTicketClosed = p.TicketUsers == null ? 0 : p.TicketUsers.Where(x => !x.DeletedAt.HasValue).Where(p => p.TicketStatusId == Enums.TicketStatusEnum.CLOSED.Id).Count(), CountTicketFail = p.TicketUsers == null ? 0 : p.TicketUsers.Where(x => !x.DeletedAt.HasValue).Where(p => p.TicketStatusId == Enums.SLAStatusEnum.Fail.Id).Count(), }).ToList()); }
public async Task <ActionResult> Export([FromBody] StaffReport_StaffReportFilterDTO filter) { if (UnAuthorization) { return(Forbid()); } var info_vn = System.Globalization.CultureInfo.GetCultureInfo("vi-VN"); var query = DataContext.AppUser.Where(p => !p.DeletedAt.HasValue); if (filter.Username.HasValue) { query = query.Where(p => p.Username, filter.Username); } if (filter.StaffId.HasValue) { query = query.Where(p => p.Id, filter.StaffId); } if (filter.OrganizationId.HasValue) { query = query.Where(p => p.OrganizationId, filter.OrganizationId); } if (filter.CreatedAt.HasValue) { query = query.Where(p => p.CreatedAt, filter.CreatedAt); } var data = query.Select(p => new StaffReportExport { Username = p.Username, DisplayName = p.DisplayName, OrganizationName = p.Organization == null ? "" : p.Organization.Name, //CountLead = p.CustomerLeadCreators == null ? 0 : p.CustomerLeadCreators.Where(x => !x.DeletedAt.HasValue).Count(), //CountOpportunityComplete = p.CustomerLeadCreators == null ? 0 : p.CustomerLeadCreators // .Where(x => !x.DeletedAt.HasValue) // .Where(x => x.CreatorId == p.Id).Count(), //Ratio = ( // p.CustomerLeadCreators.Count() == 0 // ) ? 0 : // (p.OpportunityCreators == null ? 0.00 : p.OpportunityCreators.Where(x => x.CreatorId == p.Id && x.CustomerLeadId.HasValue).Count()) * 100 // / p.CustomerLeadCreators.Count() // , CountContact = p.ContractAppUsers == null ? 0 : p.ContractAppUsers.Where(x => !x.DeletedAt.HasValue).Count(), CountOrder = (p.CustomerSalesOrderSalesEmployees == null ? 0 : p.CustomerSalesOrderSalesEmployees.Where(x => !x.DeletedAt.HasValue).Count()) + (p.DirectSalesOrderSaleEmployees == null ? 0 : p.DirectSalesOrderSaleEmployees.Where(x => !x.DeletedAt.HasValue).Count()), RevenueOrder = (p.CustomerSalesOrderSalesEmployees == null ? 0 : p.CustomerSalesOrderSalesEmployees.Where(x => !x.DeletedAt.HasValue).Sum(p => p.Total)) + (p.DirectSalesOrderSaleEmployees == null ? 0 : p.DirectSalesOrderSaleEmployees.Where(x => !x.DeletedAt.HasValue).Sum(p => p.Total)), CountContract = p.ContractAppUsers == null ? 0 : p.ContractAppUsers.Where(x => !x.DeletedAt.HasValue).Count(), RevenueContract = p.ContractAppUsers == null ? 0 : p.ContractAppUsers.Where(x => !x.DeletedAt.HasValue).Sum(p => p.Total), TotalTicket = p.TicketUsers == null ? 0 : p.TicketUsers.Where(x => !x.DeletedAt.HasValue).Count(), CountTicketClosed = p.TicketUsers == null ? 0 : p.TicketUsers.Where(x => !x.DeletedAt.HasValue).Where(p => p.TicketStatusId == Enums.TicketStatusEnum.CLOSED.Id).Count(), CountTicketFail = p.TicketUsers == null ? 0 : p.TicketUsers.Where(x => !x.DeletedAt.HasValue).Where(p => p.TicketStatusId == Enums.SLAStatusEnum.Fail.Id).Count(), CreatedAt = p.CreatedAt }).ToList(); var maxDate = data.Max(p => p.CreatedAt); var minDate = data.Min(p => p.CreatedAt); DateTime Start = filter.CreatedAt?.GreaterEqual == null ? minDate : filter.CreatedAt.GreaterEqual.Value; DateTime End = filter.CreatedAt?.LessEqual == null ? maxDate : filter.CreatedAt.LessEqual.Value; var total = new StaffTotalReportExport { TotalLead = data.Sum(p => p.CountLead), TotalOpportunity = data.Sum(p => p.CountOpportunityComplete), TotalCustomer = data.Sum(p => p.CountCustomer), TotalCompany = data.Sum(p => p.CountCompany), TotalContact = data.Sum(p => p.CountContact), TotalOrder = data.Sum(p => p.CountOrder), TotalRevenueOrder = data.Sum(p => p.RevenueOrder), TotalContract = data.Sum(p => p.CountContract), TotalRevenueContract = data.Sum(p => p.RevenueContract), TotalTicket = data.Sum(p => p.TotalTicket), TotalTicketClosed = data.Sum(p => p.CountTicketClosed), TotalTicketFail = data.Sum(p => p.CountTicketFail), }; long STT = 0; foreach (var item in data) { item.STT = STT + 1; STT++; } string path = "Templates/StaffReport.xlsx"; byte[] arr = System.IO.File.ReadAllBytes(path); MemoryStream input = new MemoryStream(arr); MemoryStream output = new MemoryStream(); dynamic Data = new ExpandoObject(); Data.NOW = StaticParams.DateTimeNow.ToString("dd-MM-yyyy"); Data.Start = Start.AddHours(CurrentContext.TimeZone).ToString("dd-MM-yyyy"); Data.End = End.AddHours(CurrentContext.TimeZone).ToString("dd-MM-yyyy"); Data.data = data; Data.total = total; using (var document = StaticParams.DocumentFactory.Open(input, output, "xlsx")) { document.Process(Data); }; return(File(output.ToArray(), "application/octet-stream", "BaoCaoNhanVien.xlsx")); }