/// <summary>
/// 获取给定条目的给定类型的命令
/// </summary>
/// <param name="commandType"></param>
/// <param name="ontology"></param>
/// <param name="n">条数</param>
/// <param name="sortField"></param>
/// <param name="sortOrder"></param>
/// <returns>命令消息集合</returns>
public IList <MessageEntity> GetTopNCommands(MessageTypeKind commandType, OntologyDescriptor ontology, int n, string sortField, string sortOrder)
{
if (ontology == null)
{
return(new List <MessageEntity>());
}
if (commandType == MessageTypeKind.Invalid || commandType == MessageTypeKind.AnyCommand)
{
return(new List <MessageEntity>());
}
var db = this.GetCommandDb(ontology);
var sql =
@"select top " + n.ToString(CultureInfo.InvariantCulture) +
" * from [" + GetTableName(commandType) + "] as c where lower(c.Ontology)=@Ontology order by c." + sortField + " " + sortOrder;
var pOntology = CreateParameter(db, "Ontology", ontology.Ontology.Code.ToLower(), DbType.String);
IList <MessageEntity> list = new List <MessageEntity>();
using (var reader = db.ExecuteReader(sql, pOntology))
{
while (reader.Read())
{
list.Add(CommandRecord.Create(ontology.Host, commandType, reader));
}
reader.Close();
return(list);
}
}
public MessageEntity GetCommand(MessageTypeKind commandType, OntologyDescriptor ontology, Guid id)
{
var db = this.GetCommandDb(ontology);
var queryString =
@"select * from " + GetTableName(commandType) + " as a where a.Id=@Id";
using (var reader = db.ExecuteReader(queryString, CreateParameter(db, "Id", id, DbType.Guid)))
{
if (reader.Read())
{
return(CommandRecord.Create(ontology.Host, commandType, reader));
}
}
return(null);
}
/// <summary>
/// 根据节点分页获取命令
/// </summary>
/// <typeparam name="T">命令类型参数</typeparam>
/// <param name="commandType"></param>
/// <param name="ontology">本体</param>
/// <param name="catalogCode">目录码</param>
/// <param name="actionCode">动作码，空值表示忽略本查询条件</param>
/// <param name="nodeId">节点标识，空值表示忽略本查询条件</param>
/// <param name="localEntityId">本地实体标识</param>
/// <param name="pageIndex">页索引</param>
/// <param name="pageSize">页尺寸</param>
/// <param name="sortField">排序字段</param>
/// <param name="sortOrder">排序方向</param>
/// <param name="total">总记录数</param>
/// <returns></returns>
public IList <MessageEntity> GetPlistCommands(MessageTypeKind commandType,
OntologyDescriptor ontology, string catalogCode, string actionCode, Guid?nodeId, string localEntityId,
int pageIndex, int pageSize, string sortField, string sortOrder, out Int64 total)
{
var tableName = GetTableName(commandType);
var queryString =
@"select top " + pageSize.ToString(CultureInfo.InvariantCulture) + " * from (SELECT ROW_NUMBER() OVER(ORDER BY " + sortField + " " + sortOrder + ") AS RowNumber,* from " + tableName +
@" as a where a.Ontology=@Ontology {0}) b
where b.RowNumber>" + (pageSize * pageIndex).ToString(CultureInfo.InvariantCulture);
var countQs =
@"select count(Id) from " + tableName + @" as a
where a.Ontology=@Ontology {0}";
if (!string.IsNullOrEmpty(actionCode))
{
queryString = string.Format(queryString, " and a.Verb=@Verb {0}");
countQs = string.Format(countQs, " and a.Verb=@Verb {0}");
}
if (!string.IsNullOrEmpty(localEntityId))
{
queryString = string.Format(queryString, " and a.LocalEntityID=@LocalEntityId {0}");
countQs = string.Format(countQs, " and a.LocalEntityID=@LocalEntityId {0}");
}
if (!string.IsNullOrEmpty(catalogCode))
{
queryString = string.Format(queryString, " and a.CatalogCode like @CatalogCode {0}");
countQs = string.Format(countQs, " and a.CatalogCode like @CatalogCode {0}");
}
if (nodeId.HasValue)
{
queryString = string.Format(queryString, " and a.ClientID=@ClientId");
countQs = string.Format(countQs, " and a.ClientID=@ClientId");
}
else
{
queryString = string.Format(queryString, "");
countQs = string.Format(countQs, "");
}
var db = this.GetCommandDb(ontology);
var parms = new List <DbParameter> {
CreateParameter(db, "Ontology", ontology.Ontology.Code, DbType.String)
};
if (!string.IsNullOrEmpty(actionCode))
{
parms.Add(CreateParameter(db, "Verb", actionCode, DbType.String));
}
if (nodeId.HasValue)
{
parms.Add(CreateParameter(db, "ClientId", nodeId.Value, DbType.Guid));
}
if (!string.IsNullOrEmpty(localEntityId))
{
parms.Add(CreateParameter(db, "LocalEntityId", localEntityId, DbType.String));
}
if (!string.IsNullOrEmpty(catalogCode))
{
parms.Add(CreateParameter(db, "CatalogCode", catalogCode + "%", DbType.String));
}
var pArray = parms.ToArray();
IList <MessageEntity> list = new List <MessageEntity>();
using (var reader = this.GetCommandDb(ontology).ExecuteReader(queryString, pArray))
{
while (reader.Read())
{
list.Add(CommandRecord.Create(ontology.Host, commandType, reader));
}
}
total = (int)this.GetCommandDb(ontology).ExecuteScalar(countQs, parms.Select(p => ((ICloneable)p).Clone()).Cast <DbParameter>().ToArray());
return(list);
}
