/// <summary>
/// 根据标签代码获得标签项
/// </summary>
/// <param name="TagClassCode"></param>
/// <param name="tagName"></param>
/// <returns></returns>
internal DataTable GetTagByTagClassCode(string TagClassCode, string tagCode, string tagValue)
{
if (!string.IsNullOrEmpty(tagCode))
{
return(this.UnitOfWork.ToDataTable(DBBuilder.Define(SYSTagSql.GetTagByTagClassCode_TagCode, new
{
ClassCode = TagClassCode,
tagCode = tagCode,
SystemID = systemID,
})));
}
else if (!string.IsNullOrEmpty(tagValue))
{
return(this.UnitOfWork.ToDataTable(DBBuilder.Define(SYSTagSql.GetTagByTagClassCode_TagValue, new
{
ClassCode = TagClassCode,
tagValue = tagValue,
SystemID = systemID,
})));
}
else
{
return(null);
}
}
/// <summary>
/// 附件读取
/// </summary>
/// <param name="id">附件id</param>
/// <param name="action">文件流操作</param>
/// <param name="unitOfWork">操作单元</param>
public void ReadFile(long?id, Action <Stream> action, IUnitOfWork unitOfWork = null)
{
if (!id.HasValue)
{
id = this.ID;
}
if (!(id > 0) || action == null)
{
return;
}
var builder = DBBuilder.Define(@"SELECT FileContent.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM STDAttach Where ID=@ID", new { ID = id });
(unitOfWork ?? this.UnitOfWork).ToReader(builder, reader =>
{
using (var sqlReader = (reader as IWrappedDataReader).Reader as SqlDataReader)
{
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
using (var stream = new SqlFileStream(sqlReader.GetString(0), sqlReader.GetSqlBytes(1).Buffer, FileAccess.Read))
{
action(stream);
}
}
}
}
});
}
/// <summary>
/// 根据贴入对象和标签值查找标签贴入数据和匹配程度
/// </summary>
/// <param name="targetID"></param>
/// <param name="tagIDs"></param>
/// <returns>查询语句</returns>
internal string GetTargetObjectIDWithGroupByTargetIDAndTagIDs(long targetID, long[] tagIDs, out int tagClassCount)
{
if (tagIDs == null || tagIDs.Length == 0)
{
tagIDs = new long[] { 0 }
}
;
//对输入标签按"标签组"分组
StringBuilder sb = new StringBuilder();
var kvs = this.UnitOfWork.ToDataTable(DBBuilder.Define("select TagClassID,ID from SYSTag where id in(" + ALConvert.ToString(tagIDs) + ")"))
.AsEnumerable()
.GroupBy(d => d.Field <int>("TagClassID"));
tagClassCount = kvs.Count();
foreach (var kv in kvs)
{
//获取当前"标签组"的标签
var ids = ALConvert.ToString(kv.Select(d => d.Field <int>("ID")));
sb.AppendFormat(@"max(case when TagID in ({0}) then 1 else 0 end)+", ids);
}
return(string.Format(SYSTagApplySql.GetTargetObjectIDWithGroupSql,
targetID,
sb.ToString(),
this.systemID.HasValue ? ("and SystemID=" + this.systemID) : ""));
}
/// <summary>
/// 保存文件
/// </summary>
/// <param name="outputStream">需要保存的文件</param>
/// <param name="id">附件id</param>
/// <param name="unitOfWork">操作单元</param>
public void WriteFile(Stream outputStream, long?id = null)
{
if (!id.HasValue)
{
id = this.ID;
}
if (!(id > 0) || outputStream == null)
{
return;
}
var builder = DBBuilder.Define(@"UPDATE STDAttach SET FileContent=CAST('' as varbinary(max)) WHERE ID=@ID; SELECT FileContent.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM STDAttach Where ID=@ID", new { ID = id });
this.UnitOfWork.ToReader(builder, reader =>
{
using (var sqlReader = (reader as IWrappedDataReader).Reader as SqlDataReader)
{
while (sqlReader.Read())
{
using (var stream = new SqlFileStream(sqlReader.GetString(0), sqlReader.GetSqlBytes(1).Buffer, FileAccess.Write))
{
outputStream.CopyTo(stream);
}
}
}
});
}
public FieldSpec(string propertyName, IEnumerable <TProperty> propertyValues)
{
if (propertyValues == null || !propertyValues.Any())
{
var spec = new FalseSpec <TEntity>();
Lambda = spec.Lambda;
Sql = new FalseSpec <TEntity>().Sql;
}
else if (propertyValues.Count() == 1)
{
var prop = typeof(TEntity).GetProperty(propertyName);
var entity = Expression.Parameter(typeof(TEntity), "entity");
var left = Expression.Constant(propertyValues.First());
var right = IsNullableType(prop.PropertyType) ? Expression.Property(Expression.Property(entity, prop), "Value") : Expression.Property(entity, prop);
var exp = Expression.Equal(left, right);
Lambda = Expression.Lambda <Func <TEntity, bool> >(exp, entity);
//Sql = DBBuilder.Define().Field(DataEntityUtils.Entity(typeof(TEntity)).TableAttribute.TableName, propertyName).Eq().Value(propertyValues.First());
Sql = DBBuilder.Define().Field(null, propertyName).Eq().Value(propertyValues.First());
}
else
{
var prop = typeof(TEntity).GetProperty(propertyName);
var entity = Expression.Parameter(typeof(TEntity), "entity");
var left = Expression.Constant(propertyValues.ToList());
var right = IsNullableType(prop.PropertyType) ? Expression.Property(Expression.Property(entity, prop), "Value") : Expression.Property(entity, prop);
var exp = Expression.Call(left, "Contains", null, right);
Lambda = Expression.Lambda <Func <TEntity, bool> >(exp, entity);
//Sql = DBBuilder.Define().Field(DataEntityUtils.Entity(typeof(TEntity)).TableAttribute.TableName, propertyName).In(d => d.Value(propertyValues));
Sql = DBBuilder.Define().Field(null, propertyName).In(d => d.Value(propertyValues));
}
}
public virtual bool Modify(TEntity entity, ISpecification <TEntity> specification)
{
if (this.OnModify != null)
{
OnModify(ref entity, ref specification);
}
DBBuilder condition;
if (specification.Lambda != null)
{
var ids = this.Entities.Where(specification.Lambda).Select(d => d.ID).ToList();
if (ids.Count > 0)
{
condition = DBBuilder.Define().Field(_entityDefinition.Value.TableAttribute.TableName, "ID").In(builder => builder.Value(ids.ToArray()));
}
else
{
condition = new FalseSpec <TEntity>().Sql;
}
}
else
{
condition = specification.Sql;
}
var fieldAndValues = GetFieldAndValue(entity, true, false);
var dbBuilder = condition.IsCommand ? condition : DBBuilder.Update(_entityDefinition.Value.TableAttribute.TableName, fieldAndValues).Where(condition);
var result = this.UnitOfWork.Execute(dbBuilder);
return(result > 0);
}
/// <summary>
/// 更新指定标签项的排序
/// </summary>
/// <param name="tagClassID"></param>
private void UpdateChildTagByParant(long?tagClassID)
{
this.UnitOfWork.Execute(DBBuilder.Define(SYSTagSql.UpdateChildTagByParant, new
{
TagClassID = tagClassID,
SystemID = systemID,
}));
}
public DataTable GetTagByTagClass(string TagClass)
{
return(this.UnitOfWork.ToDataTable(DBBuilder.Define(SYSTagSql.GetTagByTagClass, new
{
ClassName = TagClass,
SystemID = systemID,
})));
}
/// <summary>
/// 删除指定标签的贴入数据
/// </summary>
/// <param name="tagID"></param>
/// <returns></returns>
internal int DeleteByTagID(long tagID)
{
return(this.UnitOfWork.Execute(DBBuilder.Define(SYSTagApplySql.DeleteByTagIDSql, new
{
TagID = tagID,
SystemID = systemID,
})));
}
public virtual string Sql()
{
if (SortList == null || SortList.Count == 0)
{
return(string.Empty);
}
return(string.Join(",", SortList.Select(item => string.Format("{0} {1}", DBBuilder.Define().Field(null, item.Key), item.Value ? "asc" : "desc"))));
}
/// <summary>
/// 根据标签代码获得标签项
/// </summary>
/// <param name="TagClassCode"></param>
/// <returns></returns>
internal DataTable GetTagByTagClassCode(string TagClassCode)
{
return(this.UnitOfWork.ToDataTable(DBBuilder.Define(SYSTagSql.GetTagByTagClassCode, new
{
ClassCode = TagClassCode,
SystemID = systemID,
})));
}
/// <summary>
/// 得到指定标签组，指定标签类型的标签
/// </summary>
/// <param name="classCodePrefix">目录前缀</param>
/// <param name="displayLevel">标签类型</param>
/// <returns></returns>
public DataTable GetTagClass(string classCodePrefix, int?displayLevel)
{
return(this.UnitOfWork.ToDataTable(DBBuilder.Define(SYSTagClassSql.TagClassByCodePrefix, new
{
GroupCode = classCodePrefix,
DisplayLevel = displayLevel,
SystemID = systemID,
})));
}
/// <summary>
/// 根据标签类型获得标签
/// </summary>
/// <param name="TagClassID"></param>
/// <param name="ParentTagID"></param>
/// <returns></returns>
public DataTable GetTagByTagClassID(long TagClassID, long?ParentTagID)
{
return(this.UnitOfWork.ToDataTable(DBBuilder.Define(SYSTagSql.GetTagByTagClassID, new
{
TagClassID = TagClassID,
ParentID = ParentTagID,
SystemID = systemID,
})));
}
/// <summary>
/// 根据父节点查找【下一级】子节点
/// </summary>
/// <param name="TagClassCode"></param>
/// <param name="ParentName"></param>
/// <returns></returns>
internal DataTable GetTagsByParentID(string TagClassCode, string ParentName)
{
return(this.UnitOfWork.ToDataTable(DBBuilder.Define(SYSTagSql.GetTagsByParentName, new
{
ClassCode = TagClassCode,
ParentName = ParentName,
SystemID = systemID,
})));
}
/// <summary>
/// 得到指定用户可用权限
/// </summary>
/// <param name="tagOperate"></param>
/// <returns></returns>
internal DataTable GetAuthorityByCurrentUser(EnumSYSTagOperate tagOperate, long userID)
{
return(this.UnitOfWork.ToDataTable(DBBuilder.Define(SYSTagAuthoritySql.GetAuthorityByCurrentUserSql, new
{
CurrentUserID = userID,
AcceptOperate = tagOperate,
SystemID = systemID,
})));
}
/// <summary>
/// 删除全部已贴入标签
/// </summary>
/// <param name="targetID"></param>
/// <param name="targetObjectID"></param>
/// <returns></returns>
internal int DeleteByCompanyID(long targetID, long targetObjectID)
{
return(this.UnitOfWork.Execute(DBBuilder.Define(SYSTagApplySql.DeleteByCompanyIDSql, new
{
TargetID = targetID,
TargetObjectID = targetObjectID,
SystemID = systemID,
})));
}
/// <summary>
/// 取出Tag表中的一批数据
/// </summary>
/// <param name="classCodePrefix">目录前缀</param>
/// <returns></returns>
internal DataTable GetTagClass(string classCodePrefix, long?displayLevel)
{
return(this.UnitOfWork.ToDataTable(DBBuilder.Define(SYSTagSql.TagByCodePrefix, new
{
ClassCode = classCodePrefix ?? string.Empty,
DisplayLevel = displayLevel,
SystemID = systemID,
})));
}
/// <summary>
/// 这里需要处理object的类型
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
public ISpecification <TEntity> Compile <TEntity>() where TEntity : class
{
string value = _config.FilterValue;
Expression <Func <TEntity, bool> > lambda = item => true;
DBBuilder sql = DBBuilder.Define("1=1");
var param = Expression.Parameter(typeof(TEntity), "item");
var prorerty = Expression.Property(param, _propertyName);
var valueExpress = Convert(value, typeof(TEntity).GetProperty(_propertyName).PropertyType);
switch (_operationType)
{
case EnumFilterOperation.Equal:
lambda = Expression.Lambda <Func <TEntity, bool> >(Expression.Equal(prorerty, valueExpress), param);
sql = DBBuilder.Define().Field(null, _propertyName).Eq().Value(value);
break;
case EnumFilterOperation.NotEqual:
lambda = Expression.Lambda <Func <TEntity, bool> >(Expression.NotEqual(prorerty, valueExpress), param);
sql = DBBuilder.Define().Field(null, _propertyName).Ne().Value(value);
break;
case EnumFilterOperation.GreaterThan:
lambda = Expression.Lambda <Func <TEntity, bool> >(Expression.GreaterThan(prorerty, valueExpress), param);
sql = DBBuilder.Define().Field(null, _propertyName).Gt().Value(value);
break;
case EnumFilterOperation.GreaterThanOrEqual:
lambda = Expression.Lambda <Func <TEntity, bool> >(Expression.GreaterThanOrEqual(prorerty, valueExpress), param);
sql = DBBuilder.Define().Field(null, _propertyName).Ge().Value(value);
break;
case EnumFilterOperation.LessThan:
lambda = Expression.Lambda <Func <TEntity, bool> >(Expression.LessThan(prorerty, valueExpress), param);
sql = DBBuilder.Define().Field(null, _propertyName).Lt().Value(value);
break;
case EnumFilterOperation.LessThanOrEqual:
lambda = Expression.Lambda <Func <TEntity, bool> >(Expression.LessThanOrEqual(prorerty, valueExpress), param);
sql = DBBuilder.Define().Field(null, _propertyName).Le().Value(value);
break;
case EnumFilterOperation.Like:
lambda = Expression.Lambda <Func <TEntity, bool> >(Expression.Call(prorerty, typeof(string).GetMethod("Contains"), valueExpress), param);
sql = DBBuilder.Define().Field(null, _propertyName).Like().Value(value);
break;
case EnumFilterOperation.NotLike:
lambda = Expression.Lambda <Func <TEntity, bool> >(Expression.IsFalse(Expression.Call(prorerty, typeof(string).GetMethod("Contains"), valueExpress)), param);
sql = DBBuilder.Define().Field(null, _propertyName).Not().Like().Value(value);
break;
default:
throw new ArgumentOutOfRangeException();
}
return(new Spec <TEntity>(lambda, sql));
}
/// <summary>
/// 查找指定对象已贴入的标签项
/// </summary>
/// <param name="targetID"></param>
/// <param name="targetObjectID"></param>
/// <param name="tagClassID"></param>
/// <param name="tagGroupID"></param>
/// <param name="DisplayLevel"></param>
/// <returns>查询语句</returns>
internal DataTable GetTagsByTargetIDAndTargetObjectID(long targetID, long targetObjectID, long?tagClassID, long?tagGroupID, int?DisplayLevel)
{
return(this.UnitOfWork.ToDataTable(DBBuilder.Define(SYSTagApplySql.GetTagsByTargetIDAndTargetObjectIDSql, new
{
TargetID = targetID,
TargetObjectID = targetObjectID,
TagClassID = tagClassID,
TagGroupID = tagGroupID,
DisplayLevel = DisplayLevel,
SystemID = systemID,
})));
}
protected DBBuilder GetSql(ISpecification <TEntity> originalSpecification)
{
var original = originalSpecification.Sql;
if (original != null)
{
return(DBBuilder.Define(original).Not(original));
}
else
{
return(original);
}
}
public EntitySpec(TEntity query)
{
IDataEntity info = query as IDataEntity;
if (info != null)
{
Sql = GetWhereCondition(info);
}
else
{
Sql = DBBuilder.Define();
}
}
public EntitySpec(Action <TEntity> matchingCriteria)
{
query = Activator.CreateInstance <TEntity>();
matchingCriteria(query);
IDataEntity info = query as IDataEntity;
if (info != null)
{
Sql = GetWhereCondition(info);
}
else
{
Sql = DBBuilder.Define();
}
}
/// <summary>
/// 更新标签
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public bool Save(SYSTagClass info)
{
bool flag = (info.ID.HasValue ? (Func <SYSTagClass, bool>) this.Modify : this.Add)(info);
if (flag)
{
this.UnitOfWork.Execute(DBBuilder.Define(SYSTagClassSql.SaveSql, new
{
TagClass = info.ClassName,
TagClassID = info.ID,
SystemID = systemID,
}));
}
return(flag);
}
/// <summary>
/// 查找令牌
/// </summary>
/// <param name="systemID">系统编号</param>
/// <param name="accessToken">令牌代码，传NULL则查找系统令牌</param>
/// <param name="checkExpires">是否包含超时的令牌</param>
public TokenSpecification(long systemID, string accessToken = null, bool checkExpires = true)
{
Sql = DBBuilder.Define()
.Eq(new { SystemID = systemID })
.And(d => d.Gt(new { ExpiresIn = DateTime.Now }), checkExpires);
if (string.IsNullOrEmpty(accessToken))
{
Sql.IsNull("AccessToken");
}
else
{
Sql.Eq(new { AccessToken = accessToken });
}
}
override protected DBBuilder GetSql(ISpecification <T> leftSide, ISpecification <T> rightSide)
{
var left = leftSide.Sql;
var right = rightSide.Sql;
if (left != null && right != null)
{
return /*left.FragmentExists(DBBuilder.FragmentKey.Where) ?
* DBBuilder.Define(left).FragmentAppend(DBBuilder.FragmentKey.Select, DBBuilder.Define().Or(right)) :
*/(DBBuilder.Define(left).Or(right));
}
else
{
return(left);
}
}
/// <summary>
/// 查找令牌
/// </summary>
/// <param name="systemID">系统编号</param>
/// <param name="passportID">用户代码，传NULL则查找系统令牌</param>
/// <param name="checkExpires">是否包含超时的令牌</param>
public TokenSpecification(long systemID, long?passportID = null, bool checkExpires = true)
{
Sql = DBBuilder.Define()
.Eq(new { SystemID = systemID })
.And(d => d.Gt(new { ExpiresIn = DateTime.Now }), checkExpires)
.And(d =>
{
if (passportID == null)
{
d.IsNull("PassportID");
}
else
{
d.Eq(new { PassportID = passportID });
}
});
}
/// <summary>
/// 查找指定对象已贴入的标签项编号
/// </summary>
/// <param name="targetID"></param>
/// <param name="targetObjectID"></param>
/// <returns></returns>
internal List <long> GetTagIDValuesByTargetIDAndTargetObjectID(long targetID, long targetObjectID)
{
return(this.UnitOfWork.ToDataTable(DBBuilder.Define(this.GetTagIDsByTargetIDAndTargetObjectID(targetID, targetObjectID)))
.AsEnumerable().Select(d => (long)d[0]).ToList());
}
/// <summary>
/// 定义基于Sql表达式的规约
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
public Spec(string sql = null, dynamic param = null)
{
Sql = DBBuilder.Define(sql, param);
}
public TrueSpec() : base(DBBuilder.Define("1=1"), exp => true)
{
}
protected DBBuilder GetWhereCondition(IDataEntity dataEntity)
{
DBBuilder builder = DBBuilder.Define("1=1 ");
if (dataEntity == null)
{
return(builder);
}
string tableName = dataEntity.Property.TableAttribute.TableName;
foreach (PropertyInfo p in dataEntity.Property.PropertyInfos)
{
if (dataEntity.Property.ColumnAttributes[p.Name].IsExtend)
{
continue;
}
if (!string.IsNullOrEmpty(dataEntity.Property.ColumnAttributes[p.Name].WhereCondition))
{
//如果设置了查询表达式则用这个查询表达式并且忽略其本身的值
builder.Append(string.Format("and ({0}) ", dataEntity.Property.ColumnAttributes[p.Name].WhereCondition));
continue;
}
object value = p.GetValue(dataEntity, null);
if (value != null) //只查询付值的字段
{
if (Type.GetTypeCode(p.PropertyType) == TypeCode.String || p.PropertyType.FullName.Contains("Guid")) //是字符串时使用参数方式查询
{
if (dataEntity.Property.ColumnAttributes[p.Name].IsExact) //精确查询
{
builder.Append(dbAdapter => string.Format("and {0}.{1} = {2} ", dbAdapter.FormatTable(tableName), dbAdapter.FormatField(p.Name), dbAdapter.FormatParameter(p.Name)));
builder.Parameters.Add(p.Name, value);
}
else //模糊查询
{
builder.Append(dbAdapter => string.Format("and {0}.{1} like {2} ", dbAdapter.FormatTable(tableName), dbAdapter.FormatField(p.Name), dbAdapter.FormatParameter(p.Name)));
builder.Parameters.Add(p.Name, string.Format("%{0}%", value));
}
}
else if (p.PropertyType.FullName.Contains("DateTime"))
{
builder.Append(dbAdapter => string.Format("and " + dbAdapter.FormatFunction("datediff", "{0}", "{1}.{2}", "{3}") + "=0 ", dataEntity.Property.ColumnAttributes[p.Name].DateTimePart, dbAdapter.FormatTable(tableName), dbAdapter.FormatField(p.Name), dbAdapter.FormatParameter(p.Name)));
builder.Parameters.Add(p.Name, value, DbType.DateTime);
}
else if (p.PropertyType.FullName.Contains("Boolean"))
{
builder.Append(dbAdapter => string.Format("and {0}.{1} = {2} ", dbAdapter.FormatTable(tableName), dbAdapter.FormatField(p.Name), Convert.ToBoolean(value) ? 1 : 0));
}
else
{
builder.Append(dbAdapter => string.Format("and {0}.{1} = {2} ", dbAdapter.FormatTable(tableName), dbAdapter.FormatField(p.Name), value));
}
}
}
if (dataEntity.Property.DbParameters != null && dataEntity.Property.DbParameters.ParameterNames.Count() > 0)
{
builder.Parameters.AddExpandoParams(dataEntity.Property.DbParameters);
}
return(builder);
}
