/// <summary> /// Generate a query for the specified 'filter'. /// </summary> /// <param name="context"></param> /// <param name="filter"></param> /// <returns></returns> public static IQueryable <Entities.PimsLease> GenerateLeaseQuery(this PimsContext context, Entity.Models.LeaseFilter filter, bool loadPayments = false) { filter.ThrowIfNull(nameof(filter)); var query = context.PimsLeases.AsNoTracking(); query = query.GenerateCommonLeaseQuery(filter, loadPayments); return(query); }
/// <summary> /// Generate an SQL statement for the specified 'user' and 'filter'. /// </summary> /// <param name="query"></param> /// <param name="filter"></param> /// <returns></returns> private static IQueryable <Entities.PimsLease> GenerateCommonLeaseQuery(this IQueryable <Entities.PimsLease> query, Entity.Models.LeaseFilter filter, bool loadPayments = false) { filter.ThrowIfNull(nameof(filter)); if (!string.IsNullOrWhiteSpace(filter.TenantName)) { query = query.Where(l => l.PimsLeaseTenants.Any(tenant => tenant.Person != null && EF.Functions.Like(tenant.Person.Surname + ", " + tenant.Person.FirstName + ", " + tenant.Person.MiddleNames, $"%{filter.TenantName}%")) || l.PimsLeaseTenants.Any(tenant => tenant.Organization != null && EF.Functions.Like(tenant.Organization.OrganizationName, $"%{filter.TenantName}%"))); } if (!string.IsNullOrWhiteSpace(filter.PinOrPid)) { var pinOrPidValue = filter.PinOrPid.Replace("-", "").Trim().TrimStart('0'); query = query.Where(l => l.PimsPropertyLeases.Any(pl => pl != null && (EF.Functions.Like(pl.Property.Pid.ToString(), $"%{pinOrPidValue}%") || EF.Functions.Like(pl.Property.Pin.ToString(), $"%{pinOrPidValue}%")))); } if (!string.IsNullOrWhiteSpace(filter.LFileNo)) { query = query.Where(l => EF.Functions.Like(l.LFileNo, $"%{filter.LFileNo}%")); } if (!string.IsNullOrWhiteSpace(filter.Address)) { query = query.Where(l => l.PimsPropertyLeases.Any(pl => pl != null && (EF.Functions.Like(pl.Property.Address.StreetAddress1, $"%{filter.Address}%") || EF.Functions.Like(pl.Property.Address.StreetAddress2, $"%{filter.Address}%") || EF.Functions.Like(pl.Property.Address.StreetAddress3, $"%{filter.Address}%") || EF.Functions.Like(pl.Property.Address.MunicipalityName, $"%{filter.Address}%") ))); } if (filter.IsReceivable == true) { query = query.Where(l => l.LeasePayRvblTypeCode == "RCVBL"); } if (filter.NotInStatus.Count > 0) { query = query.Where(l => !filter.NotInStatus.Contains(l.LeaseStatusTypeCode)); } if (filter.ExpiryAfterDate.HasValue) { // additional terms may "extend" the original expiry date. query = query.Where(l => (l.OrigExpiryDate >= filter.ExpiryAfterDate.Value || l.OrigExpiryDate == null) || l.PimsLeaseTerms.Any(t => t.TermExpiryDate >= filter.ExpiryAfterDate.Value || t.TermExpiryDate == null)); } if (filter.StartBeforeDate.HasValue) { query = query.Where(l => l.OrigStartDate <= filter.StartBeforeDate); } if (filter.Programs.Count > 0) { query = query.Where(l => filter.Programs.Any(p => p == l.LeaseProgramTypeCode)); } if (filter.LeaseStatusTypes.Count > 0) { query = query.Where(l => filter.LeaseStatusTypes.Any(p => p == l.LeaseStatusTypeCode)); } if (filter.ExpiryStartDate != null && filter.ExpiryEndDate != null) { query = query.Where(l => l.OrigExpiryDate >= filter.ExpiryStartDate && l.OrigExpiryDate <= filter.ExpiryEndDate); } else if (filter.ExpiryStartDate != null) { query = query.Where(l => l.OrigExpiryDate >= filter.ExpiryStartDate); } else if (filter.ExpiryEndDate != null) { query = query.Where(l => l.OrigExpiryDate <= filter.ExpiryEndDate); } if (filter.RegionType.HasValue) { query = query.Where(l => l.RegionCode == filter.RegionType); } if (!string.IsNullOrWhiteSpace(filter.Details)) { query = query.Where(l => EF.Functions.Like(l.LeaseDescription, $"%{filter.Details}%") || EF.Functions.Like(l.LeaseNotes, $"%{filter.Details}%")); } if (filter.Sort?.Any() == true) { query = query.OrderByProperty(filter.Sort); } else { query = query.OrderBy(l => l.LFileNo); } query = query.Include(l => l.PimsPropertyLeases) .ThenInclude(p => p.Property) .ThenInclude(p => p.Address) .Include(l => l.PimsPropertyLeases) .Include(l => l.PimsPropertyImprovements) .Include(l => l.LeaseProgramTypeCodeNavigation) .Include(l => l.LeasePurposeTypeCodeNavigation) .Include(l => l.LeaseStatusTypeCodeNavigation) .Include(l => l.PimsLeaseTenants) .ThenInclude(t => t.Person) .Include(l => l.PimsLeaseTenants) .ThenInclude(t => t.Organization) .Include(p => p.RegionCodeNavigation) .Include(l => l.PimsLeaseTerms); if (loadPayments) { query = query.Include(l => l.PimsLeaseTerms) .ThenInclude(l => l.PimsLeasePayments); } return(query); }