/// <summary>
///
/// </summary>
/// <param name="condition"></param>
/// <param name="orderByBuilder"></param>
/// <param name="timePoint"></param>
/// <param name="mappings"></param>
/// <returns></returns>
protected TCollection InnerLoadByBuilder(string condition, OrderBySqlClauseBuilder orderByBuilder, DateTime timePoint, ORMappingItemCollection mappings)
{
string sql = VersionedQueryInContextBuilder <T, TCollection> .Instance.GetLoadSqlByBuilder(condition, orderByBuilder, timePoint, GetQueryTableName(mappings, timePoint));
TCollection result = QueryData(mappings, sql);
this.AfterLoad(result);
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="condition"></param>
/// <param name="orderByBuilder"></param>
/// <param name="mappings"></param>
/// <param name="tableName"></param>
/// <param name="action"></param>
protected void RegisterLoadByBuilderInContext(string condition, OrderBySqlClauseBuilder orderByBuilder, Action <TCollection> action, string tableName, ORMappingItemCollection mappings)
{
QueryInContextBuilder <T, TCollection> .Instance.RegisterLoadByBuilderInContext(
this.GetSqlContext(),
condition,
orderByBuilder,
(collection) => this.AfterLoad(collection),
(collection) => action(collection),
(row) => this.CreateNewData(row),
tableName,
mappings);
}
/// <summary>
/// 根据外界的builder加载数据
/// </summary>
/// <param name="builder"></param>
/// <param name="orderByBuilder"></param>
/// <returns></returns>
public virtual TCollection LoadByBuilder(IConnectiveSqlClause builder, OrderBySqlClauseBuilder orderByBuilder)
{
builder.NullCheck("builder");
TCollection result = null;
PerformanceMonitorHelper.GetDefaultMonitor().WriteExecutionDuration(string.Format("LoadByBuilder({0})", this.GetType().FullName), () =>
{
result = InnerLoadByBuilder(builder.ToSqlString(TSqlBuilder.Instance),
orderByBuilder,
this.GetQueryMappingInfo());
});
return(result);
}
/// <summary>
/// 得到字符串的拼音
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public List <string> GetPinYin(string data)
{
data = data.Trim();
StringBuilder select = new StringBuilder(64);
StringBuilder from = new StringBuilder(64);
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
OrderBySqlClauseBuilder orderByBuilder = new OrderBySqlClauseBuilder();
for (int i = 0; i < data.Length; i++)
{
char ch = data[i];
if (ch < ' ' || ch > 'z')
{
string strAlias = "P" + i.ToString();
select.AppendWithSplitChars(strAlias + ".PinYin", ", ");
from.AppendWithSplitChars("SC.PinYin " + strAlias, ", ");
wBuilder.AppendItem(strAlias + ".HZ", ch.ToString());
orderByBuilder.AppendItem(strAlias + ".Weight", FieldSortDirection.Ascending);
}
}
List <string> result = new List <string>();
if (select.Length > 0)
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3}",
select.ToString(), from.ToString(),
wBuilder.ToSqlString(TSqlBuilder.Instance),
orderByBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
result.Add(DataRowToPinYin(data, row));
}
}
else
{
result.Add(DataRowToPinYin(data, null));
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="inCondition"></param>
/// <param name="mappings"></param>
/// <returns></returns>
public TCollection LoadByInBuilder(InLoadingCondition inCondition, ORMappingItemCollection mappings = null)
{
inCondition.NullCheck("inCondition");
inCondition.BuilderAction.NullCheck("BuilderAction");
if (mappings == null)
{
mappings = this.GetQueryMappingInfo();
}
TCollection result = default(TCollection);
PerformanceMonitorHelper.GetDefaultMonitor().WriteExecutionDuration(string.Format("LoadByInBuilder({0})", this.GetType().FullName), () =>
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder(inCondition.DataField);
inCondition.BuilderAction(inBuilder);
string condition = string.Empty;
if (inBuilder.IsEmpty == false)
{
ConnectiveSqlClauseCollection builder = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And,
inBuilder, new WhereSqlClauseBuilder().AppendTenantCode(typeof(T)));
condition = builder.ToSqlString(TSqlBuilder.Instance);
OrderBySqlClauseBuilder orderByBuilder = null;
if (inCondition.OrderByAction != null)
{
orderByBuilder = new OrderBySqlClauseBuilder();
inCondition.OrderByAction(orderByBuilder);
}
result = InnerLoadByBuilder(condition, orderByBuilder, mappings);
}
else
{
result = new TCollection();
}
});
return(result);
}
/// <summary>
/// 按条件加载指定有效时间点对象集合
/// </summary>
/// <param name="whereAction">where委托</param>
/// <param name="orderByAction">orderBy委托</param>
/// <param name="dateTime">有效时间点</param>
/// <returns></returns>
public TCollection LoadDataByTime(Action <ConnectiveSqlClauseCollection> whereAction, Action <OrderBySqlClauseBuilder> orderByAction, DateTime dateTime)
{
var qc = GetQueryConditionByTime(whereAction, dateTime);
OrderBySqlClauseBuilder orderByBuilder = new OrderBySqlClauseBuilder();
if (orderByAction != null)
{
orderByAction(orderByBuilder);
}
qc.OrderByClause = orderByBuilder.ToSqlString(TSqlBuilder.Instance);
TCollection result = qc.GetData <T, TCollection>(GetConnectionName());
AfterLoad(result);
return(result);
}
/// <summary>
/// 获得指定ID的副本 和 它之前的一切副本 按照时间正排序
/// </summary>
/// <param name="id">id</param>
/// <returns>MaterialList</returns>
public MaterialList GetPreMaterialsCopyVersion(string id)
{
ExceptionHelper.CheckStringIsNullOrEmpty(id, "id");
WhereSqlClauseBuilder whereSqlClause = new WhereSqlClauseBuilder();
whereSqlClause.AppendItem("PARENT_ID",
string.Format("(SELECT PARENT_ID FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "=", true);
whereSqlClause.AppendItem("MODIFY_TIME",
string.Format("(SELECT MODIFY_TIME FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "<=", true);
whereSqlClause.AppendTenantCode(typeof(Material));
OrderBySqlClauseBuilder orderBySqlClause = new OrderBySqlClauseBuilder();
orderBySqlClause.AppendItem("MODIFY_TIME", FieldSortDirection.Ascending);
string sql = string.Format(@"SELECT * FROM WF.MATERIAL
WHERE {0} AND VERSION_TYPE = {1} ORDER BY {2}" ,
whereSqlClause.ToSqlString(TSqlBuilder.Instance),
Convert.ToInt16(MaterialVersionType.CopyVersion),
orderBySqlClause.ToSqlString(TSqlBuilder.Instance));
MaterialList materials = new MaterialList();
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName()))
{
while (dr.Read())
{
Material material = new Material();
ORMapping.DataReaderToObject(dr, material);
materials.Add(material);
}
}
}
return(materials);
}
/// <summary>
/// 按照条件加载对象
/// </summary>
/// <param name="whereAction">筛选条件</param>
/// <param name="orderByAction">排序条件</param>
/// <returns>对象集合</returns>
public TCollection Load(Action <WhereSqlClauseBuilder> whereAction, Action <OrderBySqlClauseBuilder> orderByAction)
{
whereAction.NullCheck("whereAction");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
whereAction(builder);
OrderBySqlClauseBuilder orderByBuilder = null;
if (orderByAction != null)
{
orderByBuilder = new OrderBySqlClauseBuilder();
orderByAction(orderByBuilder);
}
return(InnerLoadByBuilder(builder.ToSqlString(TSqlBuilder.Instance), orderByBuilder));
}
/// <summary>
/// 按条件加载当前有效数据对象
/// </summary>
/// <param name="whereAction">where委托</param>
/// <param name="orderByAction">orderBy委托</param>
/// <returns></returns>
public TCollection LoadCurrentData(Action <WhereSqlClauseBuilder> whereAction, Action <OrderBySqlClauseBuilder> orderByAction)
{
string sql = GetSqlByWhereAction(whereAction, "*");
if (orderByAction != null)
{
OrderBySqlClauseBuilder orderByBuilder = new OrderBySqlClauseBuilder();
orderByAction(orderByBuilder);
if (orderByBuilder.Count > 0)
{
sql = sql + string.Format(" ORDER BY {0}", orderByBuilder.ToSqlString(TSqlBuilder.Instance));
}
}
TCollection result = QueryData(sql);
AfterLoad(result);
return(result);
}
protected TCollection InnerLoadByBuilder(string condition, OrderBySqlClauseBuilder orderByBuilder, ORMappingItemCollection mappings)
{
string sql = string.Format("SELECT * FROM {0}", mappings.TableName);
if (condition.IsNotEmpty())
{
sql = sql + string.Format(" WHERE {0}", condition);
}
if (orderByBuilder != null)
{
sql = sql + string.Format(" ORDER BY {0}", orderByBuilder.ToSqlString(TSqlBuilder.Instance));
}
TCollection result = QueryData(sql);
AfterLoad(result);
return(result);
}
/// <summary>
/// 根据UserID查询用户的待办个数和摘要列表。同时传递originalServerTag。如果originalServerTag和目前Server端的ServerTag不匹配，
/// 则重新查询，否则不执行查询且返回数据
/// </summary>
/// <param name="userID"></param>
/// <param name="searchParams"></param>
/// <returns></returns>
public UserTaskCountAndSimpleListModel QueryTaskCountAndSimpleList(string userID, UserTaskSearchParams searchParams)
{
userID.CheckStringIsNullOrEmpty("userID");
UserTaskCountAndSimpleListModel model = new UserTaskCountAndSimpleListModel();
model.ServerTag = GetServerTag(userID);
if (model.ServerTag != searchParams.OriginalServerTag)
{
model.CountData = UserTaskAdapter.Instance.GetUserTaskCount(userID);
OrderBySqlClauseBuilder orderBuilder = new OrderBySqlClauseBuilder().AppendItem("DELIVER_TIME", FieldSortDirection.Descending);
model.TaskData = UserTaskAdapter.Instance.LoadUserTasks("WF.USER_TASK", CreateTaskBuilder(userID), orderBuilder, searchParams.Top, true);
model.NotifyData = UserTaskAdapter.Instance.LoadUserTasks("WF.USER_TASK", CreateNotifyBuilder(userID), orderBuilder, searchParams.Top, true);
}
return(model);
}
/// <summary>
/// 由资源ID查询
/// </summary>
/// <param name="resourceIDs">资源ID</param>
/// <returns>查询结果</returns>
public MaterialList LoadMaterialsByResourceID(params string[] resourceIDs)
{
ExceptionHelper.TrueThrow <ArgumentNullException>(resourceIDs == null, "resourceIDs");
MaterialList result = new MaterialList();
if (resourceIDs.Length != 0)
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("RESOURCE_ID");
inBuilder.AppendItem(resourceIDs);
OrderBySqlClauseBuilder orderClause = new OrderBySqlClauseBuilder();
orderClause.AppendItem("SORT_ID", FieldSortDirection.Ascending);
string sql = string.Format("SELECT * FROM WF.MATERIAL WHERE {0} AND VERSION_TYPE = '{1}' ORDER BY {2}",
inBuilder.AppendTenantCodeSqlClause(typeof(Material)).ToSqlString(TSqlBuilder.Instance),
Convert.ToInt32(MaterialVersionType.Normal).ToString(),
orderClause.ToSqlString(TSqlBuilder.Instance));
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName()))
{
while (dr.Read())
{
Material material = new Material();
ORMapping.DataReaderToObject(dr, material);
result.Add(material);
}
}
}
DecorateMaterials(result);
}
return(result);
}
/// <summary>
/// 根据页面参数，select字句，from子句，条件对象，排序条件构造分页查询
/// </summary>
/// <param name="prp"></param>
/// <param name="select">SQL语句的select部分。如果为空，则使用*</param>
/// <param name="from">SQL语句的from部分。如果为空，则使用返回对象的ORTableMapping中TableName属性</param>
/// <param name="conditionObject"></param>
/// <param name="orderByBuilder"></param>
/// <returns></returns>
public PagedQueryResult <T, TCollection> Query(IPageRequestParams prp, string select, string from, object conditionObject, IEnumerable <IOrderByRequestItem> orderByBuilder)
{
prp.NullCheck("prp");
int totalCount = 0;
string where = ConditionObjectToWhereString(conditionObject);
string orderBy = string.Empty;
if (orderByBuilder != null)
{
OrderBySqlClauseBuilder wrappedBuilder = OrderBySqlClauseBuilder.CreateWrapperBuilder(orderByBuilder);
orderBy = wrappedBuilder.ToSqlString(TSqlBuilder.Instance);
}
PagedQueryResult <T, TCollection> result = this.InnerQuery(prp.ToRowIndex(), prp.PageSize, prp.Top, select, from, where, orderBy, totalCount);
this.OnAfterQuery(result.PagedData);
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="loadingCondition"></param>
/// <param name="action"></param>
/// <param name="timePoint">时间点</param>
/// <param name="tableName"></param>
/// <param name="mappings"></param>
public void LoadInContext(WhereLoadingCondition loadingCondition, Action <TCollection> action, DateTime timePoint, string tableName = null, ORMappingItemCollection mappings = null)
{
loadingCondition.NullCheck("loadingCondition");
loadingCondition.BuilderAction.NullCheck("BuilderAction");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
loadingCondition.BuilderAction(builder);
builder.AppendTenantCode(typeof(T));
OrderBySqlClauseBuilder orderByBuilder = null;
if (loadingCondition.OrderByAction != null)
{
orderByBuilder = new OrderBySqlClauseBuilder();
loadingCondition.OrderByAction(orderByBuilder);
}
this.RegisterLoadByBuilderInContext(builder.ToSqlString(TSqlBuilder.Instance), orderByBuilder, action, timePoint, tableName, mappings);
}
private TCollection InnerLoadByBuilder(string condition, OrderBySqlClauseBuilder orderByBuilder)
{
Dictionary <string, object> context = new Dictionary <string, object>();
ORMappingItemCollection mappings = GetMappingInfo(context);
string sql = string.Format("SELECT * FROM {0}", mappings.TableName);
if (condition.IsNotEmpty())
{
sql = sql + string.Format(" WHERE {0}", condition);
}
if (orderByBuilder != null && orderByBuilder.Count > 0)
{
sql = sql + string.Format(" ORDER BY {0}", orderByBuilder.ToSqlString(TSqlBuilder.Instance));
}
TCollection result = QueryData(sql);
AfterLoad(result);
return(result);
}
/// <summary>
/// 得到字符串的拼音
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public List<string> GetPinYin(string data)
{
data = data.Trim();
StringBuilder select = new StringBuilder(64);
StringBuilder from = new StringBuilder(64);
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
OrderBySqlClauseBuilder orderByBuilder = new OrderBySqlClauseBuilder();
for (int i = 0; i < data.Length; i++)
{
char ch = data[i];
if (ch < ' ' || ch > 'z')
{
string strAlias = "P" + i.ToString();
select.AppendWithSplitChars(strAlias + ".PinYin", ", ");
from.AppendWithSplitChars("SC.PinYin " + strAlias, ", ");
wBuilder.AppendItem(strAlias + ".HZ", ch.ToString());
orderByBuilder.AppendItem(strAlias + ".Weight", FieldSortDirection.Ascending);
}
}
List<string> result = new List<string>();
if (select.Length > 0)
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3}",
select.ToString(), from.ToString(),
wBuilder.ToSqlString(TSqlBuilder.Instance),
orderByBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
result.Add(DataRowToPinYin(data, row));
}
else
result.Add(DataRowToPinYin(data, null));
return result;
}
/// <summary>
/// 获得指定ID的副本 和 它之前的一切副本 按照时间正排序
/// </summary>
/// <param name="id">id</param>
/// <returns>MaterialList</returns>
public MaterialList GetPreMaterialsCopyVersion(string id)
{
ExceptionHelper.CheckStringIsNullOrEmpty(id, "id");
WhereSqlClauseBuilder whereSqlClause = new WhereSqlClauseBuilder();
whereSqlClause.AppendItem("PARENT_ID",
string.Format("(SELECT PARENT_ID FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "=", true);
whereSqlClause.AppendItem("MODIFY_TIME",
string.Format("(SELECT MODIFY_TIME FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "<=", true);
whereSqlClause.AppendTenantCode(typeof(Material));
OrderBySqlClauseBuilder orderBySqlClause = new OrderBySqlClauseBuilder();
orderBySqlClause.AppendItem("MODIFY_TIME", FieldSortDirection.Ascending);
string sql = string.Format(@"SELECT * FROM WF.MATERIAL
WHERE {0} AND VERSION_TYPE = {1} ORDER BY {2}",
whereSqlClause.ToSqlString(TSqlBuilder.Instance),
Convert.ToInt16(MaterialVersionType.CopyVersion),
orderBySqlClause.ToSqlString(TSqlBuilder.Instance));
MaterialList materials = new MaterialList();
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName()))
{
while (dr.Read())
{
Material material = new Material();
ORMapping.DataReaderToObject(dr, material);
materials.Add(material);
}
}
}
return materials;
}
/// <summary>
/// 由资源ID查询
/// </summary>
/// <param name="resourceIDs">资源ID</param>
/// <returns>查询结果</returns>
public MaterialList LoadMaterialsByResourceID(params string[] resourceIDs)
{
ExceptionHelper.TrueThrow<ArgumentNullException>(resourceIDs == null, "resourceIDs");
MaterialList result = new MaterialList();
if (resourceIDs.Length != 0)
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("RESOURCE_ID");
inBuilder.AppendItem(resourceIDs);
OrderBySqlClauseBuilder orderClause = new OrderBySqlClauseBuilder();
orderClause.AppendItem("SORT_ID", FieldSortDirection.Ascending);
string sql = string.Format("SELECT * FROM WF.MATERIAL WHERE {0} AND VERSION_TYPE = '{1}' ORDER BY {2}",
inBuilder.AppendTenantCodeSqlClause(typeof(Material)).ToSqlString(TSqlBuilder.Instance),
Convert.ToInt32(MaterialVersionType.Normal).ToString(),
orderClause.ToSqlString(TSqlBuilder.Instance));
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName()))
{
while (dr.Read())
{
Material material = new Material();
ORMapping.DataReaderToObject(dr, material);
result.Add(material);
}
}
}
DecorateMaterials(result);
}
return result;
}
public UserTaskCollection LoadUserTasks(string tableName, IConnectiveSqlClause wBuilder, OrderBySqlClauseBuilder orderByBuilder, int count)
{
UserTaskCollection result = new UserTaskCollection();
string top = string.Empty;
if (count >= 0)
{
top = string.Format("TOP {0}", count);
}
if (wBuilder.IsEmpty == false)
{
string sql = string.Format("SELECT {0} * FROM {1} WHERE {2}",
top,
tableName,
wBuilder.ToSqlString(TSqlBuilder.Instance));
if (orderByBuilder.Count > 0)
{
sql += " ORDER BY " + orderByBuilder.ToSqlString(TSqlBuilder.Instance);
}
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
UserTask task = new UserTask();
ORMapping.DataRowToObject(row, task);
result.Add(task);
}
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="connectiveBuilder"></param>
/// <param name="orderByBuilder"></param>
public ConnectiveLoadingCondition(IConnectiveSqlClause connectiveBuilder, OrderBySqlClauseBuilder orderByBuilder)
{
this.ConnectiveBuilder = connectiveBuilder;
this.OrderByBuilder = orderByBuilder;
}
/// <summary>
/// 生成子表与父表联接查询语句
/// </summary>
/// <param name="type">实体类型</param>
/// <param name="whereAction">Where条件</param>
/// <param name="orderByAction">OrderBy条件</param>
/// <returns>SQL语句</returns>
public static string GetSubClassSelectSql(Type type, Action <WhereSqlClauseBuilder> whereAction = null, Action <OrderBySqlClauseBuilder> orderByAction = null)
{
StringBuilder sqlStr = new StringBuilder();
//生成连接查询语句
ORMappingItemCollection mappings = ORMapping.GetMappingInfo(type);
ORMappingItem primaryItem = mappings.Find(p => p.PrimaryKey);
sqlStr.Append(String.Format(" SELECT * FROM {0} ", mappings.TableName));
//遍历父类，找出父表，生成SELECT语句
Type baseType = type.BaseType;
while (baseType != typeof(object))
{
//如果不存在TableMapping特性则跳过
if (baseType != null)
{
var tableMapping = (ORTableMappingAttribute)Attribute.GetCustomAttribute(baseType, typeof(ORTableMappingAttribute), true);
if (tableMapping == null)
{
baseType = baseType.BaseType;
continue;
}
//创建内联接查询语句
sqlStr.Append(String.Format(" INNER JOIN {0} ON {1}.{2} = {0}.{2} ", tableMapping.TableName, mappings.TableName, primaryItem.DataFieldName));
}
if (baseType != null)
{
baseType = baseType.BaseType;
}
}
//Where子句处理
if (whereAction != null)
{
//生成Where子句
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
whereAction(builder);
//将Where子句添加到SQL字串
if (builder.Count > 0)
{
sqlStr.Append(String.Format(" WHERE {0} ", builder.ToSqlString(TSqlBuilder.Instance)));
}
}
//OrderBy子句处理
if (orderByAction != null)
{
//生成OrderBy子句
OrderBySqlClauseBuilder orderByBuilder = new OrderBySqlClauseBuilder();
orderByAction(orderByBuilder);
//将OrderBy子句添加到SQL字串
if (orderByBuilder.Count > 0)
{
sqlStr.Append(String.Format(" ORDER BY {0} ", orderByBuilder.ToSqlString(TSqlBuilder.Instance)));
}
}
return(sqlStr.ToString());
}
