/// <summary>
/// If we really don't know anything about the module (which is likely with custom modules)
/// the best we can do is see whether any of its text fields matches the search text.
/// </summary>
/// <param name="moduleName">The name of the module to search.</param>
/// <param name="escapedSearchText">The text to search for, escaped for MySQL.</param>
/// <returns>A query string.</returns>
private static string ConstructQueryTextForUnknownModule(string moduleName, string escapedSearchText)
{
StringBuilder queryBuilder = new StringBuilder();
if (RestAPIWrapper.GetActivitiesLinks(moduleName, Objective.Email).Count() > 0)
{
string tableName = moduleName.ToLower();
foreach (string fieldName in RestAPIWrapper.GetCharacterFields(moduleName))
{
switch (fieldName)
{
case "name":
case "first_name":
case "last_name":
queryBuilder.Append($"{tableName}.{fieldName} LIKE '%{escapedSearchText}%' OR ");
break;
}
}
queryBuilder.Append($"{tableName}.id in (select eabr.bean_id from email_addr_bean_rel eabr ")
.Append("INNER JOIN email_addresses ea on eabr.email_address_id = ea.id ")
.Append($"where eabr.bean_module = '{moduleName}' ")
.Append($" and ea.email_address LIKE '{escapedSearchText}')");
}
return(queryBuilder.ToString());
}
/// <summary>
/// If the search text supplied comprises two space-separated tokens, these are possibly a first and last name;
/// if only one token, it's likely an email address, but might be a first or last name.
/// This query explores those possibilities.
/// </summary>
/// <param name="moduleName">The name of the module to search.</param>
/// <param name="emailAddress">The portion of the search text which may be an email address.</param>
/// <param name="firstName">The portion of the search text which may be a first name</param>
/// <param name="lastName">The portion of the search text which may be a last name</param>
/// <returns>If the module has fields 'first_name' and 'last_name', then a potential query string;
/// else an empty string.</returns>
private static string ConstructQueryTextWithFirstAndLastNames(
string moduleName,
string emailAddress,
string firstName,
string lastName)
{
List <string> fieldNames = RestAPIWrapper.GetCharacterFields(moduleName);
string result = string.Empty;
string logicalOperator = firstName == lastName ? "OR" : "AND";
if (fieldNames.Contains("first_name") && fieldNames.Contains("last_name"))
{
string lowerName = moduleName.ToLower();
result = $"({lowerName}.first_name LIKE '%{firstName}%' {logicalOperator} {lowerName}.last_name LIKE '%{lastName}%') OR ({lowerName}.id in (select eabr.bean_id from email_addr_bean_rel eabr INNER JOIN email_addresses ea on eabr.email_address_id = ea.id where eabr.bean_module = '{moduleName}' and ea.email_address LIKE '%{emailAddress}%'))";;
}
return(result);
}
/// <summary>
/// Construct suitable query text to query the module with this name for potential connection with this search text.
/// </summary>
/// <remarks>
/// Refactored from a horrible nest of spaghetti code. I don't yet fully understand this.
/// TODO: Candidate for further refactoring to reduce complexity.
/// </remarks>
/// <param name="searchText">The text entered to search for.</param>
/// <param name="moduleName">The name of the module to search.</param>
/// <param name="fields">The list of fields to pull back, which may be modified by this method.</param>
/// <returns>A suitable search query</returns>
/// <exception cref="CouldNotConstructQueryException">if no search string could be constructed.</exception>
private static string ConstructQueryTextForModuleName(string searchText, string moduleName, List <string> fields)
{
string queryText = string.Empty;
List <string> searchTokens = searchText.Split(new char[] { ' ' }).ToList();
var escapedSearchText = RestAPIWrapper.MySqlEscape(searchText);
var firstTerm = RestAPIWrapper.MySqlEscape(searchTokens.First());
var lastTerm = RestAPIWrapper.MySqlEscape(searchTokens.Last());
string logicalOperator = firstTerm == lastTerm ? "OR" : "AND";
switch (moduleName)
{
case ContactSyncing.CrmModule:
queryText = ConstructQueryTextWithFirstAndLastNames(moduleName, escapedSearchText, firstTerm, lastTerm);
AddFirstLastAndAccountNames(fields);
break;
case "Leads":
queryText = ConstructQueryTextWithFirstAndLastNames(moduleName, escapedSearchText, firstTerm, lastTerm);
AddFirstLastAndAccountNames(fields);
break;
case "Cases":
queryText = $"(cases.name LIKE '%{escapedSearchText}%' OR cases.case_number LIKE '{escapedSearchText}')";
foreach (string fieldName in new string[] { "name", "case_number" })
{
fields.Add(fieldName);
}
break;
case "Bugs":
queryText = $"(bugs.name LIKE '%{escapedSearchText}%' {logicalOperator} bugs.bug_number LIKE '{escapedSearchText}')";
foreach (string fieldName in new string[] { "name", "bug_number" })
{
fields.Add(fieldName);
}
break;
case "Accounts":
queryText = "(accounts.name LIKE '%" + firstTerm + "%') OR (accounts.id in (select eabr.bean_id from email_addr_bean_rel eabr INNER JOIN email_addresses ea on eabr.email_address_id = ea.id where eabr.bean_module = 'Accounts' and ea.email_address LIKE '%" + escapedSearchText + "%'))";
AddFirstLastAndAccountNames(fields);
break;
default:
List <string> fieldNames = RestAPIWrapper.GetCharacterFields(moduleName);
if (fieldNames.Contains("first_name") && fieldNames.Contains("last_name"))
{
/* This is Ian's suggestion */
queryText = ConstructQueryTextWithFirstAndLastNames(moduleName, escapedSearchText, firstTerm, lastTerm);
foreach (string fieldName in new string[] { "first_name", "last_name" })
{
fields.Add(fieldName);
}
}
else
{
queryText = ConstructQueryTextForUnknownModule(moduleName, escapedSearchText);
foreach (string candidate in new string[] { "name", "description" })
{
if (fieldNames.Contains(candidate))
{
fields.Add(candidate);
}
}
}
break;
}
if (string.IsNullOrEmpty(queryText))
{
throw new CouldNotConstructQueryException(moduleName, searchText);
}
return(queryText);
}