public async Task<IEnumerable<dynamic>> List(ProjectsQueryParameters searchParameters)
{
var userId = User.GetUserId();
var user = await _userManager.FindByIdAsync(userId);
return await _projectsManager.ListAsync(user, searchParameters);
}
public async Task<IEnumerable<dynamic>> ListAsync(AppUser currentUser, ProjectsQueryParameters queryParameters)
{
try
{
bool isAdmin = false;
if (currentUser != null)
{
isAdmin = (from user in _dbContext.UserRoles
join role in _dbContext.Roles on user.RoleId equals role.Id
where user.UserId == currentUser.Id && role.Name.ToLower() == AppRole.AdminsRole
select user).Any();
}
double searchValueDouble = 0;
double.TryParse(queryParameters.ProjectsSearch, out searchValueDouble);
if (queryParameters.IsFilterActivate())
{
queryParameters.ItemsCount = 99999;
queryParameters.PageNr = 0;
}
var data = (from prj in _dbContext.ProjectsListAggregated
join customer in _dbContext.Customers on prj.CustomerId equals customer.Id into customer1
from customer in customer1.Where(c => string.IsNullOrEmpty(queryParameters.CustomersSearch) || c.Name.ToLower().Contains(queryParameters.CustomersSearch)).DefaultIfEmpty()
join cat in _dbContext.SyndicCategories on prj.SyndicCategoryId equals cat.Id into cat1
from cat in cat1.Where(c => string.IsNullOrEmpty(queryParameters.ProjectsSearch) ||
c.Name.ToLower().Contains(queryParameters.ProjectsSearch) ||
c.Label.ToLower().Contains(queryParameters.ProjectsSearch) ||
prj.QuotationNumber.ToLower().Contains(queryParameters.ProjectsSearch) ||
(searchValueDouble > 0 && prj.QuotationAmountHT == searchValueDouble) ||
(searchValueDouble > 0 && prj.QuotationAmountTTC == searchValueDouble) ||
(searchValueDouble > 0 && prj.QuotationAmountVAT == searchValueDouble) ||
prj.QuotationPurpose.ToLower().Contains(queryParameters.ProjectsSearch)
).DefaultIfEmpty()
join tl in _dbContext.Users on prj.TeamLeaderId equals tl.Id into tl1
from tl in tl1.DefaultIfEmpty()
join po in _dbContext.Users on prj.PaperOwnerId equals po.Id into po1
from po in po1.DefaultIfEmpty()
join at in _dbContext.Users on prj.AssignedToId equals at.Id into at1
from at in at1.DefaultIfEmpty()
join pn in _dbContext.PropertyNames on prj.PropertyNameId equals pn.Id into pn1
from pn in pn1.Where(c => string.IsNullOrEmpty(queryParameters.ProjectsSearch) ||
(
prj.QuotationNumber.ToLower().Contains(queryParameters.ProjectsSearch) ||
c.Name.ToLower().Contains(queryParameters.ProjectsSearch) ||
(searchValueDouble > 0 && prj.QuotationAmountHT == searchValueDouble) ||
(searchValueDouble > 0 && prj.QuotationAmountTTC == searchValueDouble) ||
(searchValueDouble > 0 && prj.QuotationAmountVAT == searchValueDouble) ||
prj.QuotationPurpose.ToLower().Contains(queryParameters.ProjectsSearch) ||
cat != null
)).DefaultIfEmpty()
where prj.IsArchived == queryParameters.IncludeArchived &&
(!queryParameters.CompanyId.HasValue || prj.CompanyId == queryParameters.CompanyId) &&
(!queryParameters.OpenRemarksOnly.HasValue || prj.HasOpenRemarks == queryParameters.OpenRemarksOnly) &&
(!queryParameters.Waranty5percentOnly.HasValue || prj.Waranty5Percent == queryParameters.Waranty5percentOnly) &&
(!queryParameters.BigWorkOnly.HasValue || prj.BigWork == queryParameters.BigWorkOnly) &&
(!queryParameters.ToPlan.HasValue || (prj.ActiveStateId == "-1-SEC-QuotationDatas") || (prj.ActiveStateId == "-2-COM-Assignments") || (prj.ActiveStateId == "-3-SEC-SendOrderConfirmation")) &&
(!queryParameters.InvoiceWaited.HasValue ||
(
(prj.ActiveStateId == "-5-SEC-ACE-WorkPlan") || (prj.ActiveStateId == "-6A-PartialInvoice") || (prj.ActiveStateId == "-8-SEC-EndOfWork-Invoice") &&
_invoiceManager.GetToBeInvoiced(prj.Id, prj.QuotationAmountTTC) == 0
)) &&
(queryParameters.ProjectsSearch == null ||
(
prj.QuotationNumber.ToLower().Contains(queryParameters.ProjectsSearch) ||
(searchValueDouble > 0 && prj.QuotationAmountHT == searchValueDouble) ||
(searchValueDouble > 0 && prj.QuotationAmountTTC == searchValueDouble) ||
(searchValueDouble > 0 && prj.QuotationAmountVAT == searchValueDouble) ||
prj.QuotationPurpose.ToLower().Contains(queryParameters.ProjectsSearch) ||
cat != null ||
pn != null
)) &&
(string.IsNullOrEmpty(queryParameters.InvoicesSearch) || _dbContext.ProjectInvoices.Any(i => i.ProjectId == prj.Id && i.InvoiceNumber.ToLower().Contains(queryParameters.InvoicesSearch))) &&
(string.IsNullOrEmpty(queryParameters.CustomersSearch) || customer != null)
select new
{
prj.Id,
Company = new Company { Id = prj.CompanyId, Name = prj.CompanyName },
prj.QuotationNumber,
prj.Decadal,
CustomerName = customer == null ? null : customer.Name,
prj.QuotationAmountHT,
prj.QuotationAmountTTC,
prj.QuotationAmountVAT,
prj.QuotationPurpose,
Category = cat == null ? null : cat.Name,
CE = tl == null ? null : tl.FullName,
StartDate = prj.StartWorkDate,
EndWorkDate = prj.EndWorkDate,
PropertyName = pn == null ? null : pn.Name,
prj.CreatedOn,
prj.AccountingYear,
prj.IsArchived,
AssignedTo = at == null ? null : at.FullName,
PaperOwner = po == null ? null : po.FullName,
ToBePaid = _invoiceManager.GetToBePaid(prj.Id, prj.QuotationAmountTTC),
ToBeInvoiced = _invoiceManager.GetToBeInvoiced(prj.Id, prj.QuotationAmountTTC),
InvoicesInfo = _dbContext.ProjectInvoices.Where(i => i.ProjectId == prj.Id).Select(i => new { i.InvoiceDate, i.InvoiceNumber }),
LawyerStateInvoices = _dbContext.ProjectInvoices.Where(i => i.ProjectId == prj.Id && i.Status == InvoiceStatus.Lawyer).Select(i => new { i.InvoiceDate, i.InvoiceNumber }),
LastInvoiceDate = string.Join("$$$", _dbContext.ProjectInvoices.Where(i => i.ProjectId == prj.Id).OrderByDescending(i => i.InvoiceDate).Select(i => i.InvoiceDate)),
LastInvoiceNumber = string.Join("<br>", _dbContext.ProjectInvoices.Where(i => i.ProjectId == prj.Id).OrderByDescending(i => i.InvoiceDate).Select(i => i.InvoiceNumber)),
prj.ActiveStateId,
prj.HasOpenRemarks,
prj.InvoiceWaiting
});
if (string.IsNullOrEmpty(queryParameters.OrderBy))
{
data = data.OrderByDescending(p => p.CreatedOn);
}
else
{
var orderParams = queryParameters.OrderBy.Split(new[] { ':' }, StringSplitOptions.RemoveEmptyEntries).Select(s => s.Replace("\"", "")).ToArray();
if (orderParams.Length == 2 && orderParams[1].Trim().ToLower() == "desc")
{
data = data.OrderByDescending(orderParams[0]);
}
else
{
data = data.OrderBy(orderParams[0]);
}
}
var qqq = data.Skip((queryParameters.PageNr ?? 0) * (queryParameters.ItemsCount ?? 0))
.Take(queryParameters.ItemsCount ?? 10000)
.ToList()
.Select(p => new
{
p.Id,
p.Company,
p.QuotationNumber,
p.Decadal,
p.CustomerName,
p.QuotationAmountHT,
p.QuotationAmountTTC,
p.QuotationAmountVAT,
p.QuotationPurpose,
p.Category,
p.CE,
p.StartDate,
p.EndWorkDate,
p.CreatedOn,
p.AccountingYear,
p.IsArchived,
p.ToBePaid,
p.ToBeInvoiced,
p.AssignedTo,
p.PaperOwner,
p.PropertyName,
p.ActiveStateId,
p.InvoicesInfo,
p.LawyerStateInvoices,
p.LastInvoiceDate,
p.LastInvoiceNumber,
p.HasOpenRemarks,
p.InvoiceWaiting,
Status = _workflowDefinition.GetWorkflowDefinition(p.Company.Name).States.Values.FirstOrDefault(s => s.Id == p.ActiveStateId)?.DisplayName,
States = _workflowDefinition.GetWorkflowDefinition(p.Company.Name).States.Values.Cast<State>().Select(st => new { st.Id, st.CategoryName, st.IsActive, st.IsCompleted, st.DisplayName }),
})
.Where(p => currentUser == null || isAdmin || (p.AssignedTo != null && p.AssignedTo.Equals(currentUser.FullName)));
return qqq;
}
catch (Exception ex)
{
await NotificationManager.SendErrorMessageAsync(ex);
return null;
}
}
public async Task<IEnumerable<dynamic>> ListAll(ProjectsQueryParameters searchParameters)
{
return await _projectsManager.ListAsync(null, searchParameters);
}