private string AddFilters(
List <SqlParameter> parameters,
SearchLegalPartyQuery searchLegalPartyQueryExclusions, bool isPinWithSitusAsAddressType,
bool?isActive, DateTime?effectiveDate)
{
var builder = new StringBuilder();
if (isActive.HasValue)
{
builder.Append(" AND EffStatus=@isActive");
parameters.Add(new SqlParameter("@isActive", SqlDbType.VarChar, 1)
{
Value = isActive.Value ? 'A' : 'I'
});
_logger.LogDebug($"@isActive={isActive}");
}
if (effectiveDate.HasValue)
{
builder.Append(" AND (BegEffDate<=@effectiveDate OR BegEffDate IS NULL)");
parameters.Add(new SqlParameter("@effectiveDate", SqlDbType.DateTime)
{
Value = effectiveDate.Value
});
_logger.LogDebug($"@effectiveDate={effectiveDate}");
}
if (searchLegalPartyQueryExclusions.RevenueObjectIdIsNotNull == true)
{
builder.Append(" AND RevObjId IS NOT NULL");
}
if (searchLegalPartyQueryExclusions.AppraisalSiteIdIsNotNull == true)
{
builder.Append(" AND AppraisalSiteId IS NOT NULL");
}
if (!string.IsNullOrEmpty(searchLegalPartyQueryExclusions.AddressType))
{
builder.Append(" AND AddrType = @AddressType");
parameters.Add(new SqlParameter("@AddressType", SqlDbType.VarChar, 10)
{
Value = searchLegalPartyQueryExclusions.AddressType
});
_logger.LogDebug($"@AddressType={searchLegalPartyQueryExclusions.AddressType}");
if (isPinWithSitusAsAddressType)
{
builder.Append(" AND (PrimeAddress = @isPrimeAddress OR PrimeAddress IS NULL)");
parameters.Add(new SqlParameter("@isPrimeAddress", SqlDbType.Bit)
{
Value = 1
});
builder.Append(" AND PrimeOwner = @isPrimeOwner");
parameters.Add(new SqlParameter("@isPrimeOwner", SqlDbType.Bit)
{
Value = 1
});
builder.Append(" AND (AddrRoleEffStatus = @addrRoleEffStatus OR AddrRoleEffStatus IS NULL)");
parameters.Add(new SqlParameter("@addrRoleEffStatus", SqlDbType.Char)
{
Value = "A"
});
}
}
if (searchLegalPartyQueryExclusions.MineralIsNotNullWithValue.HasValue)
{
builder.Append(" AND Mineral = @isMineral");
parameters.Add(new SqlParameter("@isMineral", SqlDbType.Bit)
{
Value = (bool)searchLegalPartyQueryExclusions.MineralIsNotNullWithValue
});
_logger.LogDebug($"@isMineral={searchLegalPartyQueryExclusions.MineralIsNotNullWithValue.Value}");
}
return(builder.ToString());
}
// refactored from the repository ///////////////////////////////////////////
private IEnumerable <SearchLegalParty> SearchLegalParties(string searchText, SearchLegalPartyQuery searchLegalPartyQueryExclusions,
DateTime?effectiveDate, List <SearchLegalParty> results)
{
// isPin() function return value could change based on the search results
bool isPin = IsPin(searchText, results);
string effStatusActive = "A";
var isPinWithSitusAsAddressType = isPin && searchLegalPartyQueryExclusions.AddressType == "situs";
searchText = isPin ? searchText : new ContainSearchTextSqlTransformer(searchText, null).GetSqlFriendly();
var searchCompText = searchText.Replace("\"", "").Trim();
_logger.LogDebug($"Records returned from database before filtering: {results.Count}");
if (isPin)
{
// if it is a pin refilter results for only active legal party roles
results = results.Where(r => r.EffectiveStatus == effStatusActive).ToList();
if (searchText.StartsWith("0"))
{
// filter for leading zero pin results
results = results.Where(r => r.Pin.ToLower().EndsWith(searchText.ToLower())).ToList();
}
}
if (!isPinWithSitusAsAddressType)
{
if (isPin &&
(searchLegalPartyQueryExclusions.AddressType == "situs"))
{
isPinWithSitusAsAddressType = true;
}
}
if (isPinWithSitusAsAddressType)
{
var list = new List <SearchLegalParty>();
results = results.Where(x => x.PrimeOwner == true).ToList(); //returning only primeowner
results.GroupBy(x => new { x.RevenueObjectId, x.LegalPartyRole }).ToList().ForEach(g =>
{
var maxEffectiveDate = g.Max(x => x.AddressRoleEffectiveDate);
if (maxEffectiveDate.HasValue)
{
var legalPartyAddressesOfSameDate = g.Where(x =>
x.AddressRoleEffectiveDate.HasValue &&
x.AddressRoleEffectiveDate.Value == maxEffectiveDate.Value)
.ToList();
var maxId = legalPartyAddressesOfSameDate.Max(x => x.AddressRoleId);
list.AddRange(legalPartyAddressesOfSameDate.Where(x => x.AddressRoleId == maxId));
}
else
{
// This is for Legal Party with RevObj with no situs addr.
// Maybe this legal party does not yet have a situs addr assigned yet.
list.AddRange(g);
}
});
results = list;
}
results = DuplicateRevenueObjectCleanup(results, effectiveDate);
if (searchLegalPartyQueryExclusions.RevenueObjectIsActive.HasValue)
{
var flag = searchLegalPartyQueryExclusions.RevenueObjectIsActive == true ? "A" : "I";
results = results.Where(x => x.RevenueObjectEffectiveStatus == flag).ToList();
}
if (searchLegalPartyQueryExclusions.CoalesceIfDuplicateAddress.HasValue &&
searchLegalPartyQueryExclusions.CoalesceIfDuplicateAddress == true)
{
results = DuplicateLegalPartyCleanup(results);
}
if (isPinWithSitusAsAddressType && (results.Count > 0)) //Possibly AIN, GEOCODE,TAG multiple result can be returned.Hence picking only the search result that is a pin.
{
if (results.Any(x => string.Equals(x.Pin, searchCompText, StringComparison.OrdinalIgnoreCase)))
{
var desiredResult = results.Where(x => string.Equals(x.Pin, searchCompText, StringComparison.OrdinalIgnoreCase)).ToList();
foreach (var rec in desiredResult)
{
var latestPin = rec.PinLatest?.Trim();
if (latestPin != searchCompText && latestPin != rec.Pin?.Trim())
{
rec.Pin = $"{latestPin}({rec.Pin?.Trim()})";
}
}
}
}
if (!isPin)
{
//if search is not pin based, not showing the inactive owner
foreach (var rec in results)
{
if (rec.EffectiveStatus != effStatusActive)
{
rec.Pin = string.Empty;
rec.GeoCode = string.Empty;
rec.Ain = string.Empty;
}
}
}
return(results);
}