public List <DictionaryModel> GetDictionary(DictionaryType type)
{
if (type.GetHashCode() < 10)
{
string query = $"SELECT Id, Title FROM {type.ToString()} WHERE Deleted = 0";
var data = hrDb.Dictionaries.FromSqlRaw(query);
var res = data.Select(d => new DictionaryModel()
{
Id = d.Id,
Title = d.Title
}).ToList();
return(res);
}
else
{
string query = $"SELECT Id, Title FROM {type.ToString()} WHERE Deleted = 0";
var data = Db.Dictionaries.FromSqlRaw(query).ToList();
var res = data.Select(d => new DictionaryModel()
{
Id = d.Id,
Title = d.Title
}).ToList();
return(res);
}
}
/// <summary>
/// Выполнение запроса.
/// </summary>
/// <param name="dictionaryType">Названия справочников, реализующих <see cref="DictionaryEntity{TKey}"/>.</param>
/// <param name="httpRequest">Представляет входящую сторону отдельного HTTP-запроса.</param>
/// <returns>Справочник.</returns>
public async Task <List <dynamic> > ExecuteAsync(DictionaryType dictionaryType, HttpRequest httpRequest)
{
var repo = Uow.GetRepository();
var entityClrType = repo.GetEntityClrType(dictionaryType.ToString());
dynamic entity = Activator.CreateInstance(entityClrType);
IQueryable <dynamic> dictionary = repo.AsQueriable(entity);
var dictionaryClrType = ExecuteCast(entityClrType, dictionary);
var filteredDictionary = ExecuteFilter(entityClrType, dictionaryClrType, httpRequest.Query);
return(await filteredDictionary.Cast <dynamic>().ToListAsync());
//var repo = Uow.GetRepository();
//var entityClrType = repo.GetEntityClrType(dictionaryType.ToString());
//dynamic entity = Activator.CreateInstance(entityClrType);
//IQueryable<dynamic> dictionary = repo.AsQueriable(entity);
//var dictionaryClrType = ExecuteCast(entityClrType, dictionary);
//var my = (IQueryable) ExecuteFilter(entityClrType, dictionaryClrType, httpRequest.Query);
//var result = await my.Cast<dynamic>().ToListAsync();
//return null;
}
public async Task <DictionaryEntity <int> > ExecuteAsync(DictionaryType dictionaryType, int id)
{
var repo = Uow.GetRepository();
var entityClrType = repo.GetEntityClrType(dictionaryType.ToString());
dynamic entity = Activator.CreateInstance(entityClrType);
IQueryable dicData = repo.AsQueriable(entity);
return(await dicData.Cast <DictionaryEntity <int> >().FirstOrDefaultAsync(r => r.Id == id));
}
public async Task <List <dynamic> > ExecuteAsync(DictionaryType dictionaryType)
{
var repo = Uow.GetRepository();
var entityClrType = repo.GetEntityClrType(dictionaryType.ToString());
dynamic entity = Activator.CreateInstance(entityClrType);
IQueryable dictionary = repo.AsQueriable(entity);
return(await dictionary.Cast <dynamic>().ToListAsync());
}
public async Task <List <DictionaryEntity <int> > > ExecuteAsync(DictionaryType dictionaryType, List <string> codes)
{
var repo = Uow.GetRepository();
var entityClrType = repo.GetEntityClrType(dictionaryType.ToString());
dynamic entity = Activator.CreateInstance(entityClrType);
IQueryable dicData = repo.AsQueriable(entity);
return(await dicData.Cast <DictionaryEntity <int> >().Where(r => codes.Contains(r.Code)).ToListAsync());
}
public async Task <List <DictionaryEntity <int> > > ExecuteAsync(DictionaryType dictionaryType)
{
var repo = Uow.GetRepository();
var entityClrType = repo.GetEntityClrType(dictionaryType.ToString());
dynamic entity = Activator.CreateInstance(entityClrType);
IQueryable dictionaries = repo.AsQueriable(entity);
var query = await dictionaries.Cast <DictionaryEntity <int> >().AsQueryable().ToListAsync();
return(query.Where(r => r.GetType().GetProperty("ParentId")?.GetValue(r, null) == null).ToList());
}
/// <summary>
/// Calls the database to retrieve a single dictionary term based on its specific Term ID.
/// </summary>
/// <param name="termId">The ID of the Term to be retrieved</param>
/// <param name="dictionary">The dictionary to retreive the Term from.
/// Valid values are
/// Term - Dictionary of Cancer Terms
/// drug - Drug Dictionary
/// genetic - Dictionary of Genetics Terms
/// </param>
/// <param name="language">The Term's desired language.
/// Supported values are:
/// en - English
/// es - Spanish
/// </param>
/// <param name="audience">Target audieince for the definition.</param>
/// <param name="version">String identifying which vereion of the API to match.</param>
/// <returns></returns>
public DataTable GetTerm(int termId, DictionaryType dictionary, Language language, AudienceType audience, String version)
{
log.DebugFormat("Enter GetTerm( {0}, {1}, {2}, {3}, {4} ).", termId, dictionary, language, audience, version);
DataTable results = null;
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@TermID", SqlDbType.Int)
{
Value = termId
},
new SqlParameter("@Dictionary", SqlDbType.NVarChar)
{
Value = dictionary.ToString()
},
new SqlParameter("@Language", SqlDbType.NVarChar)
{
Value = language.ToString()
},
new SqlParameter("@Audience", SqlDbType.NVarChar)
{
Value = audience.ToString()
},
new SqlParameter("@ApiVers", SqlDbType.NVarChar)
{
Value = version
},
};
using (SqlConnection conn = SqlHelper.CreateConnection(DBConnectionString))
{
results = SqlHelper.ExecuteDatatable(conn, CommandType.StoredProcedure, SP_GET_DICTIONARY_TERM, parameters);
}
return(results);
}
/// <summary>
/// Calls the database to search for terms matching searchText. Results are sorted by the matched term name or alias.
/// </summary>
/// <param name="searchText">text to search for.</param>
/// <param name="includeTypes">A filter for the types of name aliases to include. Multiple values are separated by the pipe character (|).
/// If no filter is supplied, the result </param>
/// <param name="offset">Offset into the list of matches for the first result to return.</param>
/// <param name="numResults">The maximum number of results to return. Must be at least 10.</param>
/// <param name="dictionary">The dictionary to retreive the Term from.
/// Valid values are
/// Term - Dictionary of Cancer Terms
/// drug - Drug Dictionary
/// genetic - Dictionary of Genetics Terms
/// </param>
/// <param name="language">The Term's desired language.
/// Supported values are:
/// en - English
/// es - Spanish
/// </param>
/// <param name="version">String identifying which vereion of the JSON structure to retrieve.</param>
/// <returns>DataTable containing a list of matching records. Results are sorted by the matching term name.</returns>
public SearchResults Expand(String searchText, String[] includeTypes, int offset, int numResults, DictionaryType dictionary, Language language, AudienceType audience, String version)
{
log.DebugFormat("Enter Expand( {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7} ).", searchText, includeTypes, offset, numResults, dictionary, language, audience, version);
DataTable results;
// Set up table parameter for specific types to include.
DataTable includeFilter = new DataTable("includes");
includeFilter.Columns.Add("NameType");
Array.ForEach(includeTypes, typeName => includeFilter.Rows.Add(typeName));
SqlParameter matchCountParam = new SqlParameter("@matchCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
int matchCount;
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@searchText", SqlDbType.NVarChar)
{
Value = searchText
},
new SqlParameter("@IncludeTypes", SqlDbType.Structured)
{
Value = includeFilter
},
new SqlParameter("@offset", SqlDbType.Int)
{
Value = offset
},
new SqlParameter("@maxResults", SqlDbType.Int)
{
Value = numResults
},
new SqlParameter("@Dictionary", SqlDbType.NVarChar)
{
Value = dictionary.ToString()
},
new SqlParameter("@Language", SqlDbType.NVarChar)
{
Value = language.ToString()
},
new SqlParameter("@Audience", SqlDbType.NVarChar)
{
Value = audience.ToString()
},
new SqlParameter("@ApiVers", SqlDbType.NVarChar)
{
Value = version
},
matchCountParam
};
using (SqlConnection conn = SqlHelper.CreateConnection(DBConnectionString))
{
results = SqlHelper.ExecuteDatatable(conn, CommandType.StoredProcedure, SP_EXPAND_DICTIONARY, parameters);
// There's some unresolved weirdness with matchCountParam.Value coming back as NULL even though
// the value is set unconditionally. This appears to have been due to retrieving the value
// after the connection had been closed. But, since that's not definite, check that the parameter
// value is not null (or DBNull) and if so, log an error and retrieve a value that will allow
// execution to continue.
if (DBNull.Value.Equals(matchCountParam.Value) || matchCountParam.Value == null)
{
log.Warn("Expand() encountered null when attempting to retrieve the @matchCount parameter.");
matchCount = int.MaxValue;
}
else
{
matchCount = (int)matchCountParam.Value;
}
}
return(new SearchResults(results, matchCount));
}
/// <summary>
/// Calls the database to search for terms matching searchText. This method is intended for use with autosuggest
/// and returns a maximum of 10 results
/// </summary>
/// <param name="searchText">text to search for.</param>
/// <param name="searchType">The type of search to perform.
/// Valid values are:
/// Begins - Search for terms beginning with searchText.
/// Contains - Search for terms containing searchText.
/// Magic - Search for terms beginning with searchText, followed by those containing searchText.
/// </param>
/// <param name="numResults">Maximum number of results to return.</param>
/// <param name="dictionary">The dictionary to retreive the Term from.
/// Valid values are
/// Term - Dictionary of Cancer Terms
/// drug - Drug Dictionary
/// genetic - Dictionary of Genetics Terms
/// </param>
/// <param name="language">The Term's desired language.
/// Supported values are:
/// en - English
/// es - Spanish
/// </param>
/// <param name="audience">The desired target audience - Patient or Health professional</param>
/// <param name="version">String identifying which vereion of the JSON structure to retrieve.</param>
/// <returns>DataTable containing a list of matching records.</returns>
public SuggestionResults SearchSuggest(String searchText, SearchType searchType, int numResults, DictionaryType dictionary, Language language, AudienceType audience, String version)
{
log.DebugFormat("Enter SearchSuggest( {0}, {1}, {2}, {3}, {4}, {5}, {6} ).", searchText, searchType, numResults, dictionary, language, audience, version);
DataTable results = null;
SqlParameter matchCountParam = new SqlParameter("@matchCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
int matchCount;
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@searchText", SqlDbType.NVarChar)
{
Value = searchText
},
new SqlParameter("@searchType", SqlDbType.NVarChar)
{
Value = searchType.ToString()
},
new SqlParameter("@maxResults", SqlDbType.Int)
{
Value = numResults
},
new SqlParameter("@Dictionary", SqlDbType.NVarChar)
{
Value = dictionary.ToString()
},
new SqlParameter("@Language", SqlDbType.NVarChar)
{
Value = language.ToString()
},
new SqlParameter("@Audience", SqlDbType.NVarChar)
{
Value = audience.ToString()
},
new SqlParameter("@ApiVers", SqlDbType.NVarChar)
{
Value = version
},
matchCountParam
};
using (SqlConnection conn = SqlHelper.CreateConnection(DBConnectionString))
{
results = SqlHelper.ExecuteDatatable(conn, CommandType.StoredProcedure, SP_SEARCH_SUGGEST_DICTIONARY, parameters);
// There's some unresolved weirdness with matchCountParam.Value coming back as NULL even though
// the value is set unconditionally. This appears to have been due to retrieving the value
// after the connection had been closed. But, since that's not definite, check that the parameter
// value is not null (or DBNull) and if so, log an error and retrieve a value that will allow
// execution to continue.
if (DBNull.Value.Equals(matchCountParam.Value) || matchCountParam.Value == null)
{
log.Warn("SearchSuggest() encountered null when attempting to retrieve the @matchCount parameter.");
matchCount = int.MaxValue;
}
else
{
matchCount = (int)matchCountParam.Value;
}
}
return(new SuggestionResults(results, matchCount));
}
/// <summary>
/// Performs a search for terms with names matching searchText. Results are sorted by the matching term name.
/// </summary>
/// <param name="searchText">text to search for.</param>
/// <param name="searchType">The type of search to perform.
/// Valid values are:
/// Begins - Search for terms beginning with searchText.
/// Contains - Search for terms containing searchText.
/// Magic - Search for terms beginning with searchText, followed by those containing searchText.
/// </param>
/// <param name="offset">Offset into the list of matches for the first result to return.</param>
/// <param name="numResults">The maximum number of results to return. Must be at least 10.</param>
/// <param name="dictionary">The dictionary to retreive the Term from.
/// Valid values are
/// Term - Dictionary of Cancer Terms
/// drug - Drug Dictionary
/// genetic - Dictionary of Genetics Terms
/// </param>
/// <param name="language">The Term's desired language.
/// Supported values are:
/// en - English
/// es - Spanish
/// </param>
/// <param name="audience">The desired target audience - Patient or Health professional</param>
/// <param name="version">String identifying which vereion of the JSON structure to retrieve.</param>
/// <returns>DataTable containing a list of matching records. Results are sorted by the matching term name.</returns>
public SearchResults Search(String searchText, SearchType searchType, int offset, int numResults, DictionaryType dictionary, Language language, AudienceType audience, String version)
{
log.DebugFormat("Enter Search( {0}, {1}, {2}, {3}, {4}, {5}, {6} ).", searchText, offset, numResults, dictionary, language, audience, version);
DataTable results = null;
switch (searchType)
{
case SearchType.Begins:
searchText += "%";
break;
case SearchType.Contains:
searchText = "%" + searchText + "%";
break;
case SearchType.Exact:
break;
default:
{
String message = String.Format("Unsupport search type '{0}'.", searchType);
log.Error(message);
throw new ArgumentException(message);
}
}
SqlParameter matchCountParam = new SqlParameter("@matchCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
int matchCount;
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@searchText", SqlDbType.NVarChar)
{
Value = searchText
},
new SqlParameter("@offset", SqlDbType.Int)
{
Value = offset
},
new SqlParameter("@maxResults", SqlDbType.Int)
{
Value = numResults
},
new SqlParameter("@Dictionary", SqlDbType.NVarChar)
{
Value = dictionary.ToString()
},
new SqlParameter("@Language", SqlDbType.NVarChar)
{
Value = language.ToString()
},
new SqlParameter("@Audience", SqlDbType.NVarChar)
{
Value = audience.ToString()
},
new SqlParameter("@ApiVers", SqlDbType.NVarChar)
{
Value = version
},
matchCountParam
};
using (SqlConnection conn = SqlHelper.CreateConnection(DBConnectionString))
{
results = SqlHelper.ExecuteDatatable(conn, CommandType.StoredProcedure, SP_SEARCH_DICTIONARY, parameters);
// There's some unresolved weirdness with matchCountParam.Value coming back as NULL even though
// the value is set unconditionally. This appears to have been due to retrieving the value
// after the connection had been closed. But, since that's not definite, check that the parameter
// value is not null (or DBNull) and if so, log an error and attempt to mretrieve a value that
// will allow execution to continue.
if (DBNull.Value.Equals(matchCountParam.Value) || matchCountParam.Value == null)
{
log.Warn("Search() encountered null when attempting to retrieve the @matchCount parameter.");
matchCount = int.MaxValue;
}
else
{
matchCount = (int)matchCountParam.Value;
}
}
return(new SearchResults(results, matchCount));
}
/// <summary>
/// Чтение словаря
/// </summary>
/// <param name="directory">Путь к файлу</param>
/// <param name="type">Тип считываемого словаря</param>
private void ReadDictionary(string directory, DictionaryType type)
{
string dictionaryName = type.ToString() + ".txt";
_dictionaryCollection[type] = Utils.FileReader.Read(Path.Combine(directory, dictionaryName), Encoding.UTF8);
}
/// <summary>
/// �����ݿ�ɾ��һ���ֵ���
/// </summary>
/// <param name="type">�ֵ�����</param>
/// <param name="key">����</param>
/// <returns></returns>
public static bool RemoveItem(DictionaryType type, string key)
{
/*
DictDelete
@type char(1),
@key varchar(50)
*/
try
{
Database.ExecuteNonQuery(CommandType.StoredProcedure, "DictDelete",
new SqlParameter[]{
Database.MakeInParam("@type",SqlDbType.Char,1,type.ToString()),
Database.MakeInParam("@key",SqlDbType.VarChar,50,key)
}
);
}
catch
{
return false;
}
return true;
}
/// <summary>
/// �����ݿ��ȡ�ֵ��б�
/// </summary>
/// <param name="type">�ֵ�����</param>
/// <returns>ArrayList</returns>
public static ArrayList List(DictionaryType type)
{
/*
DictList
@type char(1)
*/
ArrayList list = new ArrayList();
SqlDataReader reader = null;
try
{
DictionaryItem dicItem;
reader = Database.ExecuteReader(CommandType.StoredProcedure, "DictList",
new SqlParameter[] { Database.MakeInParam("@type", SqlDbType.Char, 1, type.ToString()) });
while (reader.Read())
{
/*Type, [Key], [Value]*/
dicItem = new DictionaryItem(type);
dicItem.Key = reader.GetString(1);
dicItem.Value = reader.GetString(2);
list.Add(dicItem);
}
reader.Close();
}
catch
{
//
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return list;
}
/// <summary>
/// �����ݿ��ȡһ���ֵ���
/// </summary>
/// <param name="type">�ֵ�����</param>
/// <param name="key">����</param>
/// <returns></returns>
public static DictionaryItem GetItem(DictionaryType type, string key)
{
/*
DictGet
@type char(1),
@key varchar(50)
*/
DictionaryItem dic = null;
SqlDataReader reader = null;
try
{
/*Type, [Key], [Value]*/
reader = Database.ExecuteReader(CommandType.StoredProcedure, "DictGet",
new SqlParameter[] {
Database.MakeInParam("@type",SqlDbType.Char,1,type.ToString()),
Database.MakeInParam("@key",SqlDbType.VarChar,50,key)
});
if (reader.Read())
{
dic = new DictionaryItem(type);
dic.Key = reader.GetString(1);
dic.Value = reader.GetString(2);
}
reader.Close();
}
catch
{
//
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return dic;
}
/// <summary>
/// 根据指定类型获取所有类型数据
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public Result <List <Base_TypeDictionary> > GetTypeListByType(DictionaryType type)
{
Result <List <Base_TypeDictionary> > result = new Result <List <Base_TypeDictionary> >();
try
{
var list = DataOperateBasic <Base_TypeDictionary> .Get().GetList(i => i.Type == type.ToString()).ToList();
result.Data = list;
result.AllRowsCount = list.Count();
result.Flag = EResultFlag.Success;
}
catch (Exception ex)
{
result.Data = null;
result.Flag = EResultFlag.Failure;
result.Exception = new ExceptionEx(ex, "GetTypeListByType");
}
return(result);
}
