/// <summary> /// Gets Where condition for filtering by the country. When using separated database, materializes the nested query on the main DB. /// </summary> private string GetCountryCondition(TextSimpleFilter filter) { string originalQuery = filter.WhereCondition; if (String.IsNullOrEmpty(originalQuery)) { return string.Empty; } // Query with AccountInfo context has to be used in order to be able to determine DB context of the query (otherwise the materialization would not perform). var query = AccountInfoProvider.GetAccounts() .WhereIn("AccountCountryID", CountryInfoProvider .GetCountries() .Where(originalQuery) .Column(CountryInfo.TYPEINFO.IDColumn) ); if (filter.FilterOperator == WhereBuilder.NOT_LIKE || filter.FilterOperator == WhereBuilder.NOT_EQUAL) { query = query.Or(new WhereCondition().WhereNull("AccountCountryID")); } query.EnsureParameters(); return query.Parameters.Expand(query.WhereCondition); }
/// <summary> /// Gets SQL Where condition for a state. /// </summary> private string GetStateCondition(TextSimpleFilter filter) { string originalQuery = filter.WhereCondition; if (!String.IsNullOrEmpty(originalQuery)) { originalQuery = String.Format("ContactStateID IN (SELECT StateID FROM CMS_State WHERE {0})", originalQuery); if (filter.FilterOperator == WhereBuilder.NOT_LIKE || filter.FilterOperator == WhereBuilder.NOT_EQUAL) { originalQuery = SqlHelper.AddWhereCondition("ContactStateID IS NULL", originalQuery, "OR"); } } return originalQuery; }
/// <summary> /// Gets Where condition for filtering by the state. When using separated database, materializes the nested query on the other DB. /// </summary> private string GetStateCondition(TextSimpleFilter filter) { string originalQuery = filter.WhereCondition; if (String.IsNullOrEmpty(originalQuery)) { return string.Empty; } // Query with ContactInfo context has to be used in order to be able to determine DB context of the query (otherwise the materialization would not perform). var query = ContactInfoProvider.GetContacts() .WhereIn("ContactStateID", StateInfoProvider .GetStates() .Where(originalQuery) .Column(StateInfo.TYPEINFO.IDColumn) ); if (filter.FilterOperator == WhereBuilder.NOT_LIKE || filter.FilterOperator == WhereBuilder.NOT_EQUAL) { query = query.Or(new WhereCondition().WhereNull("ContactStateID")); } query.EnsureParameters(); return query.Parameters.Expand(query.WhereCondition); }
/// <summary> /// Gets SQL Where condition for owner's full name. /// </summary> private string GetOwnerCondition(TextSimpleFilter filter) { string originalQuery = filter.WhereCondition; if (!String.IsNullOrEmpty(originalQuery)) { originalQuery = String.Format("ContactOwnerUserID IN (SELECT UserID FROM CMS_User WHERE {0})", originalQuery); if (filter.FilterOperator == "NOT LIKE" || filter.FilterOperator == "<>") { originalQuery = SqlHelperClass.AddWhereCondition("ContactOwnerUserID IS NULL", originalQuery, "OR"); } } return originalQuery; }