public IList <LookupItem> Lookup(DataEntityType type,
string filter, int top)
{
EnsureConnected();
string sql = _queryBuilder.BuildLookup(type, filter, top);
List <LookupItem> items = new List <LookupItem>();
using (IDbCommand cmd = _connection.CreateCommand())
{
cmd.CommandText = sql;
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
items.Add(new LookupItem
{
Id = reader.GetInt32(0),
Name = reader.GetString(1)
});
}
}
_connection.Close();
return(items);
}
public string BuildLookup(DataEntityType type, string filter, int top)
{
string filterx = _filter.Apply(filter);
string table = GetTableName(type);
string field = GetTableLookupFieldName(type);
string displayField = field.EndsWith("x")
? field.Substring(0, field.Length - 1)
: field;
StringBuilder sb = new StringBuilder("SELECT DISTINCT ");
sb.Append(ET("id"))
.Append(',')
.Append(ET(displayField))
.Append(" AS n FROM ")
.AppendLine(ET(table));
switch (type)
{
case DataEntityType.Partner:
sb.AppendLine("INNER JOIN actPartner ON " +
"person.id=actPartner.partnerId");
break;
}
if (!string.IsNullOrEmpty(filterx))
{
sb.Append("WHERE ")
.Append(ETP(table, field))
.Append(" LIKE '%")
.Append(SqlHelper.SqlEncode(filterx))
.AppendLine("%'");
}
sb.Append("ORDER BY ").Append(ET(displayField));
if (top > 0)
{
sb.Append(" LIMIT ").Append(top);
}
sb.AppendLine(";");
return(sb.ToString());
}
private static string GetTableLookupFieldName(DataEntityType type)
{
return(new[]
{
"labelx",
"namex",
"namex",
"namex",
"locationx",
"namex",
"namex",
"namex",
"namex",
"namex",
"namex",
"namex",
"namex",
// virtual
"namex"
}[(int)type]);
}
private static string GetTableName(DataEntityType type)
{
return(new[]
{
"act",
"actType",
"actSubtype",
"archive",
"book",
"bookType",
"bookSubtype",
"category",
"company",
"family",
"person",
"place",
"profession",
// virtual
"person"
}[(int)type]);
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param>NA</param>
public DatastructureOrderCheck()
{
appliedTo = DataEntityType.Datastructure;
}
public static IDataEntityType GetDataEntityType(this Type type)
{
return(DataEntityType.GetDataEntityType(type));
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param>NA</param>
public DatastructureMatchCheck()
{
appliedTo = DataEntityType.Datastructure;
}
