public IList <ExceptionLog> GetPlistExceptionLogs(List <FilterData> filters, PagingInput paging)
{
paging.Valid();
var filterStringBuilder = _acDomain.RetrieveRequiredService <ISqlFilterStringBuilder>();
RdbDescriptor db = GetExceptionLogDb();
List <DbParameter> prams;
var filterString = filterStringBuilder.FilterString(db, filters, "t", out prams);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where " + filterString;
}
var sql =
@"select top({0}) *
from (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,* FROM {3} as t"
+ filterString + ") a WHERE a.RowNumber > {4}";
var countSql = "select count(*) from ExceptionLog as t" + filterString;
var exceptionLogs = new List <ExceptionLog>();
var reader = db.ExecuteReader(
string.Format(sql, paging.PageSize, paging.SortField, paging.SortOrder, "ExceptionLog", paging.PageSize * paging.PageIndex), prams.ToArray());
while (reader.Read())
{
exceptionLogs.Add(ExceptionLog.Create(reader));
}
paging.Total = (int)db.ExecuteScalar(countSql, prams.Select(p => ((ICloneable)p).Clone()).Cast <DbParameter>().ToArray());
reader.Close();
return(exceptionLogs);
}
public IAnyLog Get(Guid id)
{
RdbDescriptor db = GetAnyLogDb();
const string sql = "select * from AnyLog where Id=@Id";
var reader = db.ExecuteReader(sql, CreateParameter(db, "Id", id, DbType.Guid));
AnyLog anyLog = null;
if (reader.Read())
{
anyLog = AnyLog.Create(reader);
}
reader.Close();
return(anyLog);
}
/// <summary>
/// 根据目录获取给定节点和本体的数据，如果传入的目录为空表示获取本节点的数据
/// <remarks>本节点通常是中心节点</remarks>
/// </summary>
/// <param name="ontology"></param>
/// <param name="db">模型</param>
/// <param name="filters"></param>
/// <param name="selectElements">sql select语句的选择列集合</param>
/// <param name="pagingData"></param>
/// <returns>
/// 数据记录列表，数据记录表现为字典形式，键是数据元素编码值是相应数据元素对应的数据项值
/// </returns>
private DataTuple GetPlistInfoItems(
OntologyDescriptor ontology,
RdbDescriptor db, OrderedElementSet selectElements, List <FilterData> filters,
PagingInput pagingData)
{
if (string.IsNullOrEmpty(pagingData.SortField))
{
pagingData.SortField = "IncrementId";
}
if (string.IsNullOrEmpty(pagingData.SortOrder))
{
pagingData.SortOrder = "asc";
}
var elements = ontology.Elements;
if (filters != null)
{
for (int i = 0; i < filters.Count; i++)
{
var filter = filters[i];
if (elements.ContainsKey(filter.field))
{
// TODO:根据数据属性优化查询，比如对于身份证件号来说如果输入的值长度
// 为20或18的话可以将like替换为等于
filter.type = "string";
var element = elements[filter.field];
if (element.Element.IsEnabled != 1)
{
continue;
}
if (element.Element.InfoDicId.HasValue)
{
filter.comparison = "eq";
}
else
{
filter.comparison = "like";
}
}
else
{
filters.RemoveAt(i);
}
}
}
var tableName = ontology.Ontology.EntityTableName;
var sbSqlPredicate = new StringBuilder();
var l = sbSqlPredicate.Length;
var pQueryList = new List <DbParameter>();
List <DbParameter> pFilters;
var filterString = _filterStringBuilder.FilterString(db, filters, null, out pFilters);
if (!string.IsNullOrEmpty(filterString))
{
foreach (var pFilter in pFilters)
{
object obj = pFilter.Value;
if (obj == null)
{
obj = DBNull.Value;
}
var p = db.CreateParameter();
p.ParameterName = pFilter.ParameterName;
p.Value = obj;
pQueryList.Add(p);
}
if (sbSqlPredicate.Length != l)
{
sbSqlPredicate.Append(" and ");
}
sbSqlPredicate.Append(filterString);
}
string sqlPredicateString = string.Empty;
if (sbSqlPredicate.Length > 0)
{
sqlPredicateString = sbSqlPredicate.ToString();
}
var sqlText = new StringBuilder();
OrderedElementSet elementList;
if (selectElements == null || selectElements.Count == 0)
{
elementList = new OrderedElementSet {
ontology.Elements["id"]
};
}
else
{
elementList = selectElements;
}
sqlText.Append("SELECT TOP {0} ");
int len = sqlText.Length;
foreach (var element in elementList)
{
if (sqlText.Length != len)
{
sqlText.Append(",");
}
sqlText.Append("[").Append(element.Element.FieldCode).Append("]");
}
sqlText.Append(" FROM (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,");
len = sqlText.Length;
foreach (var element in elementList)
{
if (sqlText.Length != len)
{
sqlText.Append(",");
}
sqlText.Append("[").Append(element.Element.FieldCode).Append("]");
}
sqlText.Append(" FROM {3} where ");
if (ontology.Ontology.IsLogicalDeletionEntity)
{
sqlText.Append("DeletionStateCode = 0");
}
else
{
sqlText.Append("1 = 1");
}
if (!string.IsNullOrEmpty(sqlPredicateString))
{
sqlText.Append(" and ").Append(sqlPredicateString);
}
sqlText.Append(") a WHERE a.RowNumber > {4}");
string sqlQuery = string.Format(
sqlText.ToString(),
pagingData.PageSize.ToString(CultureInfo.InvariantCulture),
pagingData.SortField,
pagingData.SortOrder,
tableName,
(pagingData.SkipCount).ToString(CultureInfo.InvariantCulture));
pagingData.Count(() =>
{
string where = ontology.Ontology.IsLogicalDeletionEntity ? "where DeletionStateCode = 0" : "";
string sqlCount = string.Format("select count(1) from {0} {1}", tableName, where);
if (!string.IsNullOrEmpty(sqlPredicateString))
{
sqlCount = sqlCount + " and " + sqlPredicateString;
}
return((int)db.ExecuteScalar(
sqlCount, pQueryList.Select(p => ((ICloneable)p).Clone()).Cast <DbParameter>().ToArray()));
});
var list = new List <object[]>();
var reader = db.ExecuteReader(sqlQuery, pQueryList.ToArray());
while (reader.Read())
{
var values = new object[elementList.Count];
for (int i = 0; i < elementList.Count; i++)
{
values[i] = reader.GetValue(i);
}
list.Add(values);
}
reader.Close();
return(new DataTuple(elementList, list.ToArray()));
}
/// <summary>
/// 根据给定的本体码和信息标识获取本节点两条数据
/// </summary>
/// <param name="ontology">本体</param>
/// <param name="db"></param>
/// <param name="infoIds">多列联合信息标识字典，键必须不区分大小写</param>
/// <param name="selectElements">选择元素</param>
/// <returns></returns>
private TowInfoTuple GetTop2InfoItemSet(OntologyDescriptor ontology,
RdbDescriptor db, IEnumerable <InfoItem> infoIds, OrderedElementSet selectElements)
{
if (infoIds == null || !infoIds.Any())
{
return(new TowInfoTuple(null, null));
}
var sb = new StringBuilder();
var sqlParameters = new List <DbParameter>();
var elementList = selectElements.ToList();
sb.Append("select top 2 ");
int l = sb.Length;
foreach (var element in elementList)
{
if (sb.Length != l)
{
sb.Append(",");
}
sb.Append("t.[").Append(element.Element.FieldCode).Append("]");
}
sb.Append(" from [").Append(ontology.Ontology.EntityTableName).Append("] as t where");
if (ontology.Ontology.IsLogicalDeletionEntity)
{
sb.Append(" t.DeletionStateCode=0 ");
}
else
{
sb.Append(" 1=1 ");
}
foreach (var element in infoIds)
{
sb.Append(" and t.[").Append(element.Element.Element.FieldCode)
.Append("]=").Append("@").Append(element.Element.Element.FieldCode);
object obj = element.Value;
if (obj == null)
{
obj = DBNull.Value;
}
sqlParameters.Add(CreateParameter(db, element.Element.Element.FieldCode, obj, DbType.String));
}
var infoValue1 = new List <InfoItem>();
var infoValue2 = new List <InfoItem>();
using (var reader = db.ExecuteReader(sb.ToString(), sqlParameters.ToArray()))
{
if (reader.Read())
{
for (int i = 0; i < elementList.Count; i++)
{
infoValue1.Add(InfoItem.Create(elementList[i], reader.GetValue(i).ToString()));
}
}
if (reader.Read())
{
for (int i = 0; i < elementList.Count; i++)
{
infoValue2.Add(InfoItem.Create(elementList[i], reader.GetValue(i).ToString()));
}
}
reader.Close();
}
return(new TowInfoTuple(infoValue1.ToArray(), infoValue2.ToArray()));
}
public IList <OperationLog> GetPlistOperationLogs(Guid?targetId,
DateTime?leftCreateOn, DateTime?rightCreateOn
, List <FilterData> filters, PagingInput paging)
{
paging.Valid();
var filterStringBuilder = _acDomain.RetrieveRequiredService <ISqlFilterStringBuilder>();
RdbDescriptor db = GetOperationLogDb();
List <DbParameter> prams;
var filterString = filterStringBuilder.FilterString(db, filters, "t", out prams);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where " + filterString + "{0}";
}
else
{
filterString = " where 1=1 {0}";
}
if (targetId.HasValue)
{
filterString = string.Format(filterString, " and t.TargetID=@TargetId {0}");
}
if (leftCreateOn.HasValue)
{
filterString = string.Format(filterString, " and t.CreateOn >= @leftCreateOn");
}
if (rightCreateOn.HasValue)
{
filterString = string.Format(filterString, " and t.CreateOn < @rightCreateOn");
}
else
{
filterString = string.Format(filterString, string.Empty);
}
var sql =
@"select top({0}) *
from (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,* FROM {3} as t"
+ filterString + ") a WHERE a.RowNumber > {4}";
var countSql = "select count(*) from OperationLog as t" + filterString;
var operationLogs = new List <OperationLog>();
if (prams == null)
{
prams = new List <DbParameter>();
}
if (targetId.HasValue)
{
prams.Add(CreateParameter(db, "TargetId", targetId.Value, DbType.Guid));
}
if (leftCreateOn.HasValue)
{
prams.Add(CreateParameter(db, "leftCreateOn", leftCreateOn.Value, DbType.DateTime));
}
if (rightCreateOn.HasValue)
{
prams.Add(CreateParameter(db, "rightCreateOn", rightCreateOn.Value, DbType.DateTime));
}
var reader = db.ExecuteReader(
string.Format(sql, paging.PageSize, paging.SortField, paging.SortOrder, "OperationLog", paging.PageSize * paging.PageIndex), prams.ToArray());
while (reader.Read())
{
operationLogs.Add(OperationLog.Create(reader));
}
paging.Total = (int)db.ExecuteScalar(countSql, prams.Select(p => ((ICloneable)p).Clone()).Cast <DbParameter>().ToArray());
reader.Close();
return(operationLogs);
}
/// <summary>
/// 根据给定的本体码和信息标识获取本节点两条数据
/// </summary>
/// <param name="ontology">本体</param>
/// <param name="db"></param>
/// <param name="infoIds">多列联合信息标识字典，键必须不区分大小写</param>
/// <param name="selectElements">选择元素</param>
/// <returns></returns>
private TowInfoTuple GetTop2InfoItemSet(OntologyDescriptor ontology,
RdbDescriptor db, IEnumerable<InfoItem> infoIds, OrderedElementSet selectElements)
{
if (infoIds == null || !infoIds.Any())
{
return new TowInfoTuple(null, null);
}
var sb = new StringBuilder();
var sqlParameters = new List<DbParameter>();
var elementList = selectElements.ToList();
sb.Append("select top 2 ");
int l = sb.Length;
foreach (var element in elementList)
{
if (sb.Length != l)
{
sb.Append(",");
}
sb.Append("t.[").Append(element.Element.FieldCode).Append("]");
}
sb.Append(" from [").Append(ontology.Ontology.EntityTableName).Append("] as t where");
if (ontology.Ontology.IsLogicalDeletionEntity)
{
sb.Append(" t.DeletionStateCode=0 ");
}
else
{
sb.Append(" 1=1 ");
}
foreach (var element in infoIds)
{
sb.Append(" and t.[").Append(element.Element.Element.FieldCode)
.Append("]=").Append("@").Append(element.Element.Element.FieldCode);
object obj = element.Value;
if (obj == null)
{
obj = DBNull.Value;
}
sqlParameters.Add(CreateParameter(db, element.Element.Element.FieldCode, obj, DbType.String));
}
var infoValue1 = new List<InfoItem>();
var infoValue2 = new List<InfoItem>();
using (var reader = db.ExecuteReader(sb.ToString(), sqlParameters.ToArray()))
{
if (reader.Read())
{
for (int i = 0; i < elementList.Count; i++)
{
infoValue1.Add(InfoItem.Create(elementList[i], reader.GetValue(i).ToString()));
}
}
if (reader.Read())
{
for (int i = 0; i < elementList.Count; i++)
{
infoValue2.Add(InfoItem.Create(elementList[i], reader.GetValue(i).ToString()));
}
}
reader.Close();
}
return new TowInfoTuple(infoValue1.ToArray(), infoValue2.ToArray());
}
/// <summary>
/// 根据目录获取给定节点和本体的数据，如果传入的目录为空表示获取本节点的数据
/// <remarks>本节点通常是中心节点</remarks>
/// </summary>
/// <param name="ontology"></param>
/// <param name="db">模型</param>
/// <param name="filters"></param>
/// <param name="selectElements">sql select语句的选择列集合</param>
/// <param name="pagingData"></param>
/// <returns>
/// 数据记录列表，数据记录表现为字典形式，键是数据元素编码值是相应数据元素对应的数据项值
/// </returns>
private DataTuple GetPlistInfoItems(
OntologyDescriptor ontology,
RdbDescriptor db, OrderedElementSet selectElements, List<FilterData> filters,
PagingInput pagingData)
{
if (string.IsNullOrEmpty(pagingData.SortField))
{
pagingData.SortField = "IncrementId";
}
if (string.IsNullOrEmpty(pagingData.SortOrder))
{
pagingData.SortOrder = "asc";
}
var elements = ontology.Elements;
if (filters != null)
{
for (int i = 0; i < filters.Count; i++)
{
var filter = filters[i];
if (elements.ContainsKey(filter.field))
{
// TODO:根据数据属性优化查询，比如对于身份证件号来说如果输入的值长度
// 为20或18的话可以将like替换为等于
filter.type = "string";
var element = elements[filter.field];
if (element.Element.IsEnabled != 1)
{
continue;
}
if (element.Element.InfoDicId.HasValue)
{
filter.comparison = "eq";
}
else
{
filter.comparison = "like";
}
}
else
{
filters.RemoveAt(i);
}
}
}
var tableName = ontology.Ontology.EntityTableName;
var sbSqlPredicate = new StringBuilder();
var l = sbSqlPredicate.Length;
var pQueryList = new List<DbParameter>();
List<DbParameter> pFilters;
var filterString = _filterStringBuilder.FilterString(db, filters, null, out pFilters);
if (!string.IsNullOrEmpty(filterString))
{
foreach (var pFilter in pFilters)
{
object obj = pFilter.Value;
if (obj == null)
{
obj = DBNull.Value;
}
var p = db.CreateParameter();
p.ParameterName = pFilter.ParameterName;
p.Value = obj;
pQueryList.Add(p);
}
if (sbSqlPredicate.Length != l)
{
sbSqlPredicate.Append(" and ");
}
sbSqlPredicate.Append(filterString);
}
string sqlPredicateString = string.Empty;
if (sbSqlPredicate.Length > 0)
{
sqlPredicateString = sbSqlPredicate.ToString();
}
var sqlText = new StringBuilder();
OrderedElementSet elementList;
if (selectElements == null || selectElements.Count == 0)
{
elementList = new OrderedElementSet { ontology.Elements["id"] };
}
else
{
elementList = selectElements;
}
sqlText.Append("SELECT TOP {0} ");
int len = sqlText.Length;
foreach (var element in elementList)
{
if (sqlText.Length != len)
{
sqlText.Append(",");
}
sqlText.Append("[").Append(element.Element.FieldCode).Append("]");
}
sqlText.Append(" FROM (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,");
len = sqlText.Length;
foreach (var element in elementList)
{
if (sqlText.Length != len)
{
sqlText.Append(",");
}
sqlText.Append("[").Append(element.Element.FieldCode).Append("]");
}
sqlText.Append(" FROM {3} where ");
if (ontology.Ontology.IsLogicalDeletionEntity)
{
sqlText.Append("DeletionStateCode = 0");
}
else
{
sqlText.Append("1 = 1");
}
if (!string.IsNullOrEmpty(sqlPredicateString))
{
sqlText.Append(" and ").Append(sqlPredicateString);
}
sqlText.Append(") a WHERE a.RowNumber > {4}");
string sqlQuery = string.Format(
sqlText.ToString(),
pagingData.PageSize.ToString(CultureInfo.InvariantCulture),
pagingData.SortField,
pagingData.SortOrder,
tableName,
(pagingData.SkipCount).ToString(CultureInfo.InvariantCulture));
pagingData.Count(() =>
{
string where = ontology.Ontology.IsLogicalDeletionEntity ? "where DeletionStateCode = 0" : "";
string sqlCount = string.Format("select count(1) from {0} {1}", tableName, where);
if (!string.IsNullOrEmpty(sqlPredicateString))
{
sqlCount = sqlCount + " and " + sqlPredicateString;
}
return (int)db.ExecuteScalar(
sqlCount, pQueryList.Select(p => ((ICloneable)p).Clone()).Cast<DbParameter>().ToArray());
});
var list = new List<object[]>();
var reader = db.ExecuteReader(sqlQuery, pQueryList.ToArray());
while (reader.Read())
{
var values = new object[elementList.Count];
for (int i = 0; i < elementList.Count; i++)
{
values[i] = reader.GetValue(i);
}
list.Add(values);
}
reader.Close();
return new DataTuple(elementList, list.ToArray());
}
