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);
}
/// <summary>
///
/// </summary>
/// <param name="dbId"></param>
/// <param name="database"></param>
/// <returns></returns>
public bool TryDb(Guid dbId, out RdbDescriptor database)
{
if (!_initialized)
{
Init();
}
return(_dicById.TryGetValue(dbId, out database));
}
private static DbParameter CreateParameter(RdbDescriptor db, string parameterName, object value)
{
var p = db.CreateParameter();
p.ParameterName = parameterName;
p.Value = value;
return(p);
}
private DbParameter CreateParameter(RdbDescriptor rdb, string parameterName, object value, DbType dbType)
{
var p = rdb.CreateParameter();
p.ParameterName = parameterName;
p.Value = value;
p.DbType = dbType;
return(p);
}
public bool TryGetDbView(RdbDescriptor db, string dbViewId, out DbView dbView)
{
if (!_initialized)
{
Init();
}
if (!_dicById.ContainsKey(db))
{
dbView = null;
return false;
}
return _dicById[db].TryGetValue(dbViewId, out dbView);
}
public bool TryGetDbTable(RdbDescriptor db, string dbTableId, out DbTable dbTable)
{
if (!_initialized)
{
Init();
}
if (!_dicById.ContainsKey(db))
{
dbTable = null;
return(false);
}
return(_dicById[db].TryGetValue(dbTableId, out dbTable));
}
public bool TryGetDbTableColumn(RdbDescriptor database, string tableColumnId, out DbTableColumn dbTableColumn)
{
if (!_initialized)
{
Init();
}
if (!_dicById.ContainsKey(database))
{
dbTableColumn = null;
return(false);
}
return(_dicById[database].TryGetValue(tableColumnId, out dbTableColumn));
}
public bool TryGetDbViewColumn(RdbDescriptor database, string viewColumnId, out DbViewColumn dbViewColumn)
{
if (!_initialized)
{
Init();
}
if (!_dicById.ContainsKey(database))
{
dbViewColumn = null;
return false;
}
return _dicById[database].TryGetValue(viewColumnId, out dbViewColumn);
}
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>
/// 根据数据库索引该库的所有视图
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public IReadOnlyDictionary<string, DbView> this[RdbDescriptor db]
{
get
{
if (!_initialized)
{
Init();
}
if (!_dicById.ContainsKey(db))
{
return new Dictionary<string, DbView>(StringComparer.OrdinalIgnoreCase);
}
return _dicById[db];
}
}
/// <summary>
/// 根据数据库索引该库的全部表
/// </summary>
/// <param name="db">数据库模型实例</param>
/// <returns></returns>
public IReadOnlyDictionary <string, DbTable> this[RdbDescriptor db]
{
get
{
if (!_initialized)
{
Init();
}
if (!_dicById.ContainsKey(db))
{
return(new Dictionary <string, DbTable>(StringComparer.OrdinalIgnoreCase));
}
return(_dicById[db]);
}
}
public bool TryGetDbViewColumns(RdbDescriptor database, DbView view, out IReadOnlyDictionary<string, DbViewColumn> dbViewColumns)
{
if (!_initialized)
{
Init();
}
if (!_dic.ContainsKey(database))
{
dbViewColumns = new Dictionary<string, DbViewColumn>(StringComparer.OrdinalIgnoreCase);
return false;
}
Dictionary<string, DbViewColumn> outDic;
var r = _dic[database].TryGetValue(view, out outDic);
dbViewColumns = outDic;
return r;
}
public bool TryGetDbTableColumns(RdbDescriptor database, DbTable table, out IReadOnlyDictionary <string, DbTableColumn> dbTableColumns)
{
if (!_initialized)
{
Init();
}
if (!_dic.ContainsKey(database))
{
dbTableColumns = new Dictionary <string, DbTableColumn>(StringComparer.OrdinalIgnoreCase);
return(false);
}
Dictionary <string, DbTableColumn> outDic;
var r = _dic[database].TryGetValue(table, out outDic);
dbTableColumns = outDic;
return(r);
}
/// <summary>
///
/// </summary>
/// <param name="anyLogs"></param>
public void Log(IAnyLog[] anyLogs)
{
// 本组命令类型所对应的数据库表
const string tableId = "[dbo][AnyLog]";
RdbDescriptor db = GetAnyLogDb();
DbTable dbTable;
if (!db.TryGetDbTable(tableId, out dbTable))
{
throw new AnycmdException("意外的数据库表标识" + tableId);
}
// 当前命令表模式克隆得到的新表
var dt = db.NewTable(dbTable);
foreach (var log in anyLogs)
{
// 将当前命令转化DataRow，一个命令对应一行
var row = log.ToDataRow(dt);
dt.Rows.Add(row);
}
db.WriteToServer(dt);
}
public RdbContext(RdbDescriptor rdb, string nameOrConnectionString, DbCompiledModel model)
: base(nameOrConnectionString, model)
{
_rdb = rdb;
}
/// <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 RdbContext(RdbDescriptor rdb, string nameOrConnectionString, DbCompiledModel model)
: base(nameOrConnectionString, model)
{
_rdb = rdb;
}
public RdbContext(RdbDescriptor rdb, DbConnection existingConnection, DbCompiledModel model,
bool contextOwnsConnection) : base(existingConnection, model, contextOwnsConnection)
{
_rdb = rdb;
}
/// <summary>
///
/// </summary>
/// <param name="dbId"></param>
/// <param name="database"></param>
/// <returns></returns>
public bool TryDb(Guid dbId, out RdbDescriptor database)
{
if (!_initialized)
{
Init();
}
return _dicById.TryGetValue(dbId, out database);
}
public RdbContext(RdbDescriptor rdb, ObjectContext objectContext, bool dbContextOwnsObjectContext)
: base(objectContext, dbContextOwnsObjectContext)
{
_rdb = rdb;
}
public RdbContext(RdbDescriptor rdb, DbConnection existingConnection, DbCompiledModel model,
bool contextOwnsConnection)
: base(existingConnection, model, contextOwnsConnection)
{
_rdb = rdb;
}
public RdbContext(RdbDescriptor rdb, ObjectContext objectContext, bool dbContextOwnsObjectContext)
: base(objectContext, dbContextOwnsObjectContext)
{
_rdb = rdb;
}
/// <summary>
/// 将给定的数据查询筛选器列表转化为原生sql查询条件字符串返回
/// </summary>
/// <param name="rdb"></param>
/// <param name="filters">数据查询筛选器</param>
/// <param name="alias">别名，空字符串或null表示无别名</param>
/// <param name="prams"></param>
/// <returns></returns>
public string FilterString(RdbDescriptor rdb, List <FilterData> filters, string alias, out List <DbParameter> prams)
{
bool useAlias = !string.IsNullOrEmpty(alias);
prams = new List <DbParameter>();
if (filters == null || filters.Count == 0)
{
return(string.Empty);
}
var result = new StringBuilder();
//type=string
#region string
var stringList = from f in filters where f.type == "string" select f;
int i = 0;
foreach (var filter in stringList)
{
var comparision = GetComparison(filter.comparison);
result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field);
if (comparision == "like")
{
result.Append(" ").Append("like").Append(" @").Append(filter.field).Append(i).Append("");
string value = string.Empty;
if (filter.value != null)
{
value = filter.value.ToString();
}
if (!value.StartsWith("%") && !value.EndsWith("%"))
{
value = "%" + value + "%";
}
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), value));
}
else
{
result.Append(comparision).Append("@").Append(filter.field).Append(i);
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value));
}
result.Append(" and ");
i++;
}
#endregion
//type=guid
#region guid
var guidList = from f in filters where f.type == "guid" select f;
i = 0;
foreach (var filter in guidList)
{
var comparision = GetComparison(filter.comparison);
result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field);
result.Append(comparision).Append("@").Append(filter.field).Append(i);
if (filter.value.GetType() != typeof(Guid))
{
throw new ValidationException("'" + filter.value + "'不是Guid类型的");
}
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value));
result.Append(" and ");
i++;
}
#endregion
//type=boolean
#region boolean
i = 0;
var booleanList = from f in filters where f.type == "boolean" select f;
foreach (var filter in booleanList)
{
var v = filter.value.ToString();
bool b;
if (!bool.TryParse(v, out b))
{
throw new ValidationException("'" + filter.value + "'不是boolean类型的");
}
result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append("=@").Append(filter.field).Append(i.ToString(CultureInfo.InvariantCulture)).Append(" and ");
var p = CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), v);
prams.Add(p);
i++;
}
#endregion
//type=null
#region null
i = 0;
var nullList = from f in filters where f.type == "null" select f;
foreach (var filter in nullList)
{
result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append(GetComparison(filter.comparison)).Append(" and ");
i++;
}
#endregion
//type=numeric
#region numeric
i = 0;
var numericList = from f in filters where f.type == "numeric" group f by f.field into g select g;
foreach (var g in numericList)
{
result.Append("( ");
string iiStr = string.Empty;
int j = 0;
foreach (var filter in g)
{
iiStr += alias + "." + filter.field + GetComparison(filter.comparison) + "@" + filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture) + " and ";
if (filter.value.GetType() != typeof(int))
{
throw new ValidationException("'" + filter.value + "'不是int类型的");
}
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture), filter.value));
j++;
}
result.Append(iiStr.Substring(0, iiStr.Length - " and".Length));
result.Append(" )");
result.Append(" and ");
i++;
}
#endregion
//type=date
#region date
i = 0;
var dateList = from f in filters where f.type == "date" group f by f.field into g select g;
foreach (var g in dateList)
{
int j = 0;
result.Append("( ");
string iiStr = string.Empty;
foreach (var filter in g)
{
iiStr += alias + "." + filter.field + GetComparison(filter.comparison) + " @" + filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture) + ")" + " and ";
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture), filter.value));
j++;
}
result.Append(iiStr.Substring(0, iiStr.Length - " and".Length));
result.Append(" )");
result.Append(" and ");
i++;
}
#endregion
//type=list :["1","2"]
#region list
i = 0;
var listList = from f in filters where f.type == "list" select f;
foreach (var filter in listList)
{
result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append(" in @").Append(filter.field).Append(i.ToString(CultureInfo.InvariantCulture)).Append(" and ");
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value.ToString().Replace("[", "( ").Replace("]", " )").Replace("\"", "'")));
i++;
}
#endregion
return(result.ToString().Substring(0, result.Length - " and".Length));
}
public IList <DbView> GetDbViews(RdbDescriptor db)
{
return(_acDomain.RetrieveRequiredService <IRdbMetaDataService>().GetDbViews(db));
}
/// <summary>
/// 将给定的数据查询筛选器列表转化为原生sql查询条件字符串返回
/// </summary>
/// <param name="rdb"></param>
/// <param name="filters">数据查询筛选器</param>
/// <param name="alias">别名，空字符串或null表示无别名</param>
/// <param name="prams"></param>
/// <returns></returns>
public string FilterString(RdbDescriptor rdb, List<FilterData> filters, string alias, out List<DbParameter> prams)
{
bool useAlias = !string.IsNullOrEmpty(alias);
prams = new List<DbParameter>();
if (filters == null || filters.Count == 0)
return string.Empty;
var result = new StringBuilder();
//type=string
#region string
var stringList = from f in filters where f.type == "string" select f;
int i = 0;
foreach (var filter in stringList)
{
var comparision = GetComparison(filter.comparison);
result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field);
if (comparision == "like")
{
result.Append(" ").Append("like").Append(" @").Append(filter.field).Append(i).Append("");
string value = string.Empty;
if (filter.value != null)
{
value = filter.value.ToString();
}
if (!value.StartsWith("%") && !value.EndsWith("%"))
{
value = "%" + value + "%";
}
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), value));
}
else
{
result.Append(comparision).Append("@").Append(filter.field).Append(i);
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value));
}
result.Append(" and ");
i++;
}
#endregion
//type=guid
#region guid
var guidList = from f in filters where f.type == "guid" select f;
i = 0;
foreach (var filter in guidList)
{
var comparision = GetComparison(filter.comparison);
result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field);
result.Append(comparision).Append("@").Append(filter.field).Append(i);
if (filter.value.GetType() != typeof(Guid))
{
throw new ValidationException("'" + filter.value + "'不是Guid类型的");
}
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value));
result.Append(" and ");
i++;
}
#endregion
//type=boolean
#region boolean
i = 0;
var booleanList = from f in filters where f.type == "boolean" select f;
foreach (var filter in booleanList)
{
var v = filter.value.ToString();
bool b;
if (!bool.TryParse(v, out b))
{
throw new ValidationException("'" + filter.value + "'不是boolean类型的");
}
result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append("=@").Append(filter.field).Append(i.ToString(CultureInfo.InvariantCulture)).Append(" and ");
var p = CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), v);
prams.Add(p);
i++;
}
#endregion
//type=null
#region null
i = 0;
var nullList = from f in filters where f.type == "null" select f;
foreach (var filter in nullList)
{
result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append(GetComparison(filter.comparison)).Append(" and ");
i++;
}
#endregion
//type=numeric
#region numeric
i = 0;
var numericList = from f in filters where f.type == "numeric" group f by f.field into g select g;
foreach (var g in numericList)
{
result.Append("( ");
string iiStr = string.Empty;
int j = 0;
foreach (var filter in g)
{
iiStr += alias + "." + filter.field + GetComparison(filter.comparison) + "@" + filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture) + " and ";
if (filter.value.GetType() != typeof(int))
{
throw new ValidationException("'" + filter.value + "'不是int类型的");
}
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture), filter.value));
j++;
}
result.Append(iiStr.Substring(0, iiStr.Length - " and".Length));
result.Append(" )");
result.Append(" and ");
i++;
}
#endregion
//type=date
#region date
i = 0;
var dateList = from f in filters where f.type == "date" group f by f.field into g select g;
foreach (var g in dateList)
{
int j = 0;
result.Append("( ");
string iiStr = string.Empty;
foreach (var filter in g)
{
iiStr += alias + "." + filter.field + GetComparison(filter.comparison) + " @" + filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture) + ")" + " and ";
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture), filter.value));
j++;
}
result.Append(iiStr.Substring(0, iiStr.Length - " and".Length));
result.Append(" )");
result.Append(" and ");
i++;
}
#endregion
//type=list :["1","2"]
#region list
i = 0;
var listList = from f in filters where f.type == "list" select f;
foreach (var filter in listList)
{
result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append(" in @").Append(filter.field).Append(i.ToString(CultureInfo.InvariantCulture)).Append(" and ");
prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value.ToString().Replace("[", "( ").Replace("]", " )").Replace("\"", "'")));
i++;
}
#endregion
return result.ToString().Substring(0, result.Length - " and".Length);
}
public IList<DbViewColumn> GetViewColumns(RdbDescriptor db)
{
return _acDomain.RetrieveRequiredService<IRdbMetaDataService>().GetViewColumns(db);
}
public IList<DbTable> GetDbTables(RdbDescriptor db)
{
return _acDomain.RetrieveRequiredService<IRdbMetaDataService>().GetDbTables(db);
}
/// <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());
}
protected RdbContext(RdbDescriptor rdb, DbCompiledModel model)
: base(model)
{
_rdb = rdb;
}
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);
}
public RdbContext(RdbDescriptor rdb, DbConnection existingConnection, bool contextOwnsConnection)
: base(existingConnection, contextOwnsConnection)
{
_rdb = rdb;
}
/// <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()));
}
private static DbParameter CreateParameter(RdbDescriptor db, string parameterName, object value, DbType dbType)
{
var p = db.CreateParameter();
p.ParameterName = parameterName;
p.Value = value;
p.DbType = dbType;
return p;
}
public IList <DbTableColumn> GetTableColumns(RdbDescriptor db)
{
return(_acDomain.RetrieveRequiredService <IRdbMetaDataService>().GetTableColumns(db));
}
public RdbContext(RdbDescriptor rdb, DbConnection existingConnection, bool contextOwnsConnection)
: base(existingConnection, contextOwnsConnection)
{
_rdb = rdb;
}
protected RdbContext(RdbDescriptor rdb, DbCompiledModel model) : base(model)
{
_rdb = rdb;
}
public RdbContext(RdbDescriptor rdb, string nameOrConnectionString) : base(nameOrConnectionString)
{
_rdb = rdb;
}
/// <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()));
}
protected RdbContext(RdbDescriptor rdb)
: base()
{
_rdb = rdb;
}
protected RdbContext(RdbDescriptor rdb)
: base()
{
_rdb = rdb;
}
public RdbContext(RdbDescriptor rdb, string nameOrConnectionString)
: base(nameOrConnectionString)
{
_rdb = rdb;
}
