protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = "SC.OperationLog";
qc.SelectFields = "*";
if (string.IsNullOrEmpty(qc.OrderByClause))
qc.OrderByClause = "CreateTime DESC";
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
if (this.catelog.IsNotEmpty())
{
builder.AppendItem("SchemaType", this.catelog);
}
if (this.operationType.IsNotEmpty())
{
builder.AppendItem("OperationType", operationType);
}
if (builder.IsEmpty == false && qc.WhereClause.IsNotEmpty())
{
qc.WhereClause += " AND ";
}
qc.WhereClause += builder.ToSqlString(TSqlBuilder.Instance);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.SchemaUserSnapshot_Current O INNER JOIN SC.UserAndContainerSnapshot_Current R ON O.ID = R.UserID" : "SC.SchemaUserSnapshot O INNER JOIN SC.UserAndContainerSnapshot R ON O.ID = R.UserID";
qc.SelectFields = "O.*";
if (string.IsNullOrEmpty(this.orderBy))
qc.OrderByClause = "R.VersionStartTime DESC";
else
qc.OrderByClause = orderBy;
base.OnBuildQueryCondition(qc);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
var timeCondition1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
builder.AppendItem("R.ContainerID", this.containerId);
InnerBuildWhere(builder);
builder.AppendItem("O.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
var allConditions = new ConnectiveSqlClauseCollection(timeCondition1, timeCondition2, builder);
if (string.IsNullOrEmpty(qc.WhereClause))
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance);
}
else
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
}
}
public DataView Query(int startRowIndex, int maximumRows, string appName, string programName, WfApplicationAuthType authType, string where, string orderBy, ref int totalCount)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
if (string.IsNullOrEmpty(appName) == false)
{
builder.AppendItem("APPLICATION_NAME", appName);
if (string.IsNullOrEmpty(programName) == false)
{
builder.AppendItem("PROGRAM_NAME", programName);
}
}
if (authType != WfApplicationAuthType.None)
{
builder.AppendItem("AUTH_TYPE", authType.ToString());
}
string sql = builder.ToSqlString(TSqlBuilder.Instance);
if (string.IsNullOrEmpty(where) == false)
{
sql = (string.IsNullOrEmpty(sql) ? where : sql + " AND (" + where + ")");
}
return base.Query(startRowIndex, maximumRows, sql, orderBy, ref totalCount);
}
//计算薪资
public Int32 CalculatePayMonthInfo(String yearMonth)
{
int result = 0;
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(FeeMonthInfoDBConst.YearMonth, yearMonth);
builder.AppendItem(FeeMonthInfoDBConst.UseFlag, Status.True.ToString("D"));
builder.AppendItem(FeeMonthInfoDBConst.FeeType, FeeType.Parameter.ToString("D"), "<>");
builder.AppendItem(FeeMonthInfoDBConst.FeeType, FeeType.Tax.ToString("D"), "<>");
builder.AppendItem(String.Concat("ISNULL(",FeeMonthInfoDBConst.CalculateExp,",'')<>''"));
List<FeeInfo> feeList = FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder);
foreach (FeeMonthInfo feeMonthInfo in feeList.Where(fe => !String.IsNullOrEmpty(fe.CalculateExp)))
{
String caculateExp = FeeInfoAdapter.Instance.ConvertExp(yearMonth, feeMonthInfo.CalculateExp);
//if (feeMonthInfo.FeeType.Equals(FeeType.Tax))
//{
// caculateExp = "ISNULL(DBO.CACULATETAX(''" + yearMonth + "'',''" + FeeInfoAdapter.Instance.GetTaxFeeID(yearMonth, feeMonthInfo.FeeID) + "''," + FeeInfoAdapter.Instance.ConvertExp(yearMonth, FeeInfoAdapter.Instance.GetTaxFeeCalculateExp(yearMonth, feeMonthInfo.FeeID)) + "),0)";
//}
//else
//{
// caculateExp = FeeInfoAdapter.Instance.ConvertExp(yearMonth, feeMonthInfo.CalculateExp);
//}
String sql = String.Format(@"DECLARE @SQL VARCHAR(8000)
SELECT @SQL=ISNULL(@SQL + '],[','') + Convert(nvarchar(32),FEE_ID) FROM FEE_MONTH F WHERE (F.FEE_TYPE={5} OR F.FEE_TYPE={6}) AND ISNULL(CALCULATE_EXP,'')='' AND YEAR_MONTH={0}
EXEC ('INSERT INTO PAY_MONTH(YEAR_MONTH,PERSON_ID,PERSON_NAME,FEE_ID,FEE_NAME,FEE_VALUE,PAY_MONEY,FORMULA)
SELECT ''{0}'' AS ''YEAR_MONTH'', PERSON_ID, PERSON_NAME, ''{1}'' AS ''FEE_ID'', ''{2}'' AS ''FEE_NAME'', {3} AS ''FEE_VALUE'', {3} AS ''PAY_MONEY'',''{4}'' AS ''FORMULA''
FROM (SELECT PERSON_ID, PERSON_NAME, FEE_ID,PAY_MONEY FROM PAY_MONTH WHERE YEAR_MONTH=''{0}'')P PIVOT(MAX(PAY_MONEY) FOR FEE_ID IN ([' + @SQL + ']))B') "
, yearMonth, feeMonthInfo.FeeID, feeMonthInfo.FeeName, caculateExp, caculateExp.Replace("'", ""), FeeType.Common.ToString("D"), FeeType.Parameter.ToString("D"));
result += _DataHelper.ExecuteSql(sql);
}
return result;
}
/// <summary>
/// 获取与指定对象关联的条件历史
/// </summary>
/// <param name="id">对象ID</param>
/// <param name="type">类型，如果为<see langword="null"/>则不限。</param>
/// <returns></returns>
public IEnumerable<SCCondition> GetConditionHistoryEntries(string id, string type)
{
SCConditionCollection entries = new SCConditionCollection();
using (var context = DbHelper.GetDBContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("OwnerID", id);
if (type != null)
{
builder.AppendItem("Type", type);
}
var sql = @"SELECT * FROM [SC].[Conditions] WHERE " + builder.ToSqlString(TSqlBuilder.Instance) + " ORDER BY [VersionEndTime] DESC, SortID ASC";
var cmd = db.GetSqlStringCommand(sql);
using (var dr = db.ExecuteReader(cmd))
{
ORMapping.DataReaderToCollection(entries, dr);
}
return entries;
}
}
public System.Collections.Generic.Stack<SchemaNavPathNode> Query(Guid id, DateTime timePoint)
{
System.Collections.Generic.Stack<SchemaNavPathNode> stack = new Stack<SchemaNavPathNode>();
StringBuilder sb = new StringBuilder();
var db = DbHelper.GetDBDatabase();
SchemaNavPathNode node;
do
{
node = null;
sb.Clear();
sb.Append(TimePointContext.Current.UseCurrentTime ? @"
SELECT R.ObjectID, R.ParentID, O.ID, O.Name, O.DisplayName,O.SchemaType
FROM SC.SchemaOrganizationSnapshot_Current AS O INNER JOIN SC.SchemaRelationObjects_Current AS R ON O.ID = R.ObjectID
WHERE " : @"
SELECT R.ObjectID, R.ParentID, O.ID, O.Name, O.DisplayName,O.SchemaType
FROM SC.SchemaOrganizationSnapshot AS O INNER JOIN SC.SchemaRelationObjects AS R ON O.ID = R.ObjectID
WHERE ");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem<int>("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem<string>("R.ParentSchemaType", "Organizations");
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "O.");
builder.AppendItem<int>("O.Status", (int)SchemaObjectStatus.Normal);
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "R.");
builder.AppendItem<int>("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem<string>("R.ObjectID", id.ToString());
var schemaConditon1 = DataSourceUtil.SchemaTypeCondition("R.ParentSchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
var schemaConditon2 = DataSourceUtil.SchemaTypeCondition("O.SchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
var schemaConditon3 = DataSourceUtil.SchemaTypeCondition("O.ChildSchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
sb.Append(new ConnectiveSqlClauseCollection(builder, timeCondition, timeCondition2, schemaConditon1, schemaConditon2, schemaConditon3).ToSqlString(TSqlBuilder.Instance));
using (var dr = db.ExecuteReader(System.Data.CommandType.Text, sb.ToString()))
{
if (dr.Read())
{
node = new SchemaNavPathNode()
{
ID = dr.GetString(0),
ParentId = dr.GetString(1),
Name = dr.GetString(2),
Description = dr.GetString(3),
NodeType = dr.GetString(4)
};
stack.Push(node);
id = Guid.Parse(node.ParentId);
}
}
} while (node != null && node.ID != SCOrganization.RootOrganizationID);
return stack;
}
public Int32 DeleteReportFee(String reportID, String feeID)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(ReportFeeDBConst.ReportID, reportID);
builder.AppendItem(ReportFeeDBConst.FeeID, feeID);
String sql = String.Format("DELETE FROM {0} WHERE {1} ", ReportFeeDBConst.TableName, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public SCConditionCollection GetRoleConditions(string roleId)
{
var role = DbUtil.GetEffectiveObject<SCRole>(roleId);
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("OwnerID", role.ID);
where.AppendItem("Status", (int)SchemaObjectStatus.Normal);
return PC.Adapters.SCConditionAdapter.Instance.Load(where, DateTime.MinValue);
}
protected override void OnConnectConditions(ConnectiveSqlClauseCollection allConditions)
{
base.OnConnectConditions(allConditions);
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("OU."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O."));
WhereSqlClauseBuilder where1 = new WhereSqlClauseBuilder();
where1.AppendItem("OU.Status", (int)SchemaObjectStatus.Normal);
where1.AppendItem("O.Status", (int)SchemaObjectStatus.Normal);
allConditions.Add(where1);
}
public Int32 DeletePersonBaseFeeInfo(String yearMonth, String personID)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(PersonBaseFeeInfoDBConst.PersonID, personID);
if (!String.IsNullOrEmpty(yearMonth))
{
builder.AppendItem(PersonBaseFeeMonthInfoDBConst.YearMonth, yearMonth);
}
String sql = String.Format("DELETE FROM {0} WHERE {1} ", String.IsNullOrEmpty(yearMonth) ? PersonBaseFeeInfoDBConst.TableName : ORMapping.GetMappingInfo<PersonBaseFeeMonthInfo>().TableName, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
private string BuildFrom(string fullPath, string scopeType)
{
string sql = @"SELECT QM.MemberID FROM SC.SchemaRelationObjectsSnapshot QR INNER JOIN SC.SchemaMembersSnapshot QM ON QR.ObjectID= QM.ContainerID WHERE ";
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder().NormalFor("QR.Status").NormalFor("QM.Status");
where.AppendItem("QM.ContainerSchemaType", AUCommon.SchemaAdminUnit);
where.AppendItem("QM.MemberSchemaType", AUCommon.SchemaAUAdminScope);
where.AppendItem("QR.FullPath", TSqlBuilder.Instance.EscapeLikeString(fullPath) + "%", "LIKE");
var time1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("QR.");
var time2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("QM.");
return sql + new ConnectiveSqlClauseCollection(time1, time2, where).ToSqlString(TSqlBuilder.Instance);
}
private static WhereSqlClauseBuilder GenerateBuilder()
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("ID", UuidHelper.NewUuidString());
builder.AppendItem("Name", "Turtle");
builder.AppendItem("Age", 250);
builder.LogicOperator = LogicOperatorDefine.Or;
return builder;
}
/// <summary>
/// 根据userID和一组ContainerID，加载该Member所拥有的权限
/// </summary>
/// <param name="userID"></param>
/// <param name="containerIDs"></param>
/// <returns></returns>
public SCContainerAndPermissionCollection LoadCurrentContainerAndPermissions(string userID, IEnumerable<string> containerIDs)
{
var ids = (from s in containerIDs select s).ToArray();
SCContainerAndPermissionCollection result = null;
if (ids.Length > 0)
{
var timeConditon1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("A.");
var timeConditon2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("U.");
var condition = new WhereSqlClauseBuilder();
condition.AppendItem("A.Status", (int)SchemaObjectStatus.Normal);
condition.AppendItem("U.Status", (int)SchemaObjectStatus.Normal);
condition.AppendItem("U.UserID", userID);
InSqlClauseBuilder inSql = new InSqlClauseBuilder("A.ContainerID");
inSql.AppendItem(ids);
var sql = string.Format(
"SELECT A.* FROM SC.Acl_Current A INNER JOIN SC.UserAndContainerSnapshot_Current U ON U.ContainerID = A.MemberID WHERE {0} ORDER BY SortID ",
new ConnectiveSqlClauseCollection(timeConditon1, condition, inSql).ToSqlString(TSqlBuilder.Instance));
result = new SCContainerAndPermissionCollection();
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName()))
{
while (reader.Read())
{
string containerID = (string)reader["ContainerID"];
string permission = (string)reader["ContainerPermission"];
if (result.ContainsKey(containerID, permission) == false)
{
result.Add(new SCContainerAndPermission()
{
ContainerID = containerID,
ContainerPermission = permission
});
}
}
return result;
}
}
}
else
{
result = new SCContainerAndPermissionCollection();
}
return result;
}
/// <summary>
/// 根据ownerID和type加载ConditionOwner对象
/// </summary>
/// <param name="ownerID"></param>
/// <param name="type"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SCConditionCollection Load(string ownerID, string type, DateTime timePoint)
{
if (type.IsNullOrEmpty())
type = "Default";
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("OwnerID", ownerID);
builder.AppendItem("Type", type);
SCConditionCollection conditions = this.Load(builder, timePoint);
return conditions;
}
public Decimal CalculatePersonBaseFeeDepartmentProjectInfo(String yearMonth, String personID, String feeID)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
if (!String.IsNullOrEmpty(yearMonth))
{
builder.AppendItem(PersonBaseFeeDepartmentProjectMonthInfoDBConst.YearMonth, yearMonth);
}
builder.AppendItem(PersonBaseFeeDepartmentProjectInfoDBConst.PersonID, personID);
builder.AppendItem(PersonBaseFeeDepartmentProjectInfoDBConst.FeeID, feeID);
String sql = String.Format("SELECT ISNULL(SUM(STATION_MONEY),0) FROM {0} WHERE {1} "
, String.IsNullOrEmpty(yearMonth) ? PersonBaseFeeDepartmentProjectInfoDBConst.TableName : ORMapping.GetMappingInfo<PersonBaseFeeDepartmentProjectMonthInfo>().TableName
, builder.ToSqlString());
DataTable dt = _DataHelper.GetDataTable(sql);
return Decimal.Parse(dt.Rows[0][0].ToString());
}
public WfClientProcessCurrentInfoPageQueryResult QueryProcesses(WfClientProcessQueryCondition condition, int startRowIndex, int maximumRows, string orderBy, int totalCount)
{
condition.NullCheck("condition");
OperationContext.Current.FillContextToOguServiceContext();
WfProcessQueryCondition serverCondition = null;
WfClientProcessQueryConditionConverter.Instance.ClientToServer(condition, ref serverCondition);
if (orderBy.IsNullOrEmpty())
{
orderBy = "START_TIME DESC";
}
ConnectiveSqlClauseCollection connective = serverCondition.ToSqlBuilder();
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("COMMITTED", "1");
builder.AppendTenantCode();
connective.Add(builder);
QueryCondition qc = new QueryCondition(startRowIndex, maximumRows,
ORMapping.GetSelectFieldsNameSql <WfProcessCurrentInfo>(),
ORMapping.GetMappingInfo(typeof(WfProcessCurrentInfo)).TableName,
orderBy);
qc.WhereClause += connective.ToSqlString(TSqlBuilder.Instance);
return(QueryProcessInfo(qc, totalCount));
}
/// <summary>
/// 根据模式类型，查询ID类型和ID和时间点检索对象
/// </summary>
/// <param name="schemaType">表示模式类型的字符串</param>
/// <param name="idType">表示ID类型的<see cref="SnapshotQueryIDType"/>值之一</param>
/// <param name="id">对象的ID</param>
/// <param name="timePoint"></param>
/// <returns></returns>
public DESchemaObjectBase LoadByID(string schemaType, DESnapshotQueryIDType idType, string id, DateTime timePoint)
{
schemaType.CheckStringIsNullOrEmpty("schemaType");
id.CheckStringIsNullOrEmpty("id");
DESchemaDefine schema = DESchemaDefine.GetSchema(schemaType);
var timeConditon = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "SN.");
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
EnumItemDescriptionAttribute attr = EnumItemDescriptionAttribute.GetAttribute(idType);
whereBuilder.AppendItem("SN." + attr.ShortName, id);
string sql = string.Format("SELECT SO.* FROM DE.SchemaObject SO INNER JOIN {0} SN ON SO.ID = SN.ID WHERE {1}",
schema.SnapshotTable, new ConnectiveSqlClauseCollection(whereBuilder, timeConditon).ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
DESchemaObjectBase result = null;
if (table.Rows.Count > 0)
{
result = SchemaExtensions.CreateObject(schemaType);
result.FromString((string)table.Rows[0]["Data"]);
ORMapping.DataRowToObject(table.Rows[0], result);
}
return(result);
}
private static WhereSqlClauseBuilder GetWhereSqlClauseBuilderFromMapping(
object condition,
ConditionMappingItemCollection mapping,
bool ignoreDefaultValue,
AdjustConditionValueDelegate acv)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
foreach (ConditionMappingItem item in mapping)
{
object data = GetValueFromObject(item, condition);
if (data != null)
{
if (ignoreDefaultValue == false || (ignoreDefaultValue == true && IsTypeDefaultValue(item, data) == false))
{
bool ignored = false;
data = AdjustValueBeforeAppendToBuilder(item, data);
if (acv != null)
{
data = acv(item.PropertyName, data, ref ignored);
}
if (ignored == false)
{
builder.AppendItem(item.DataFieldName, data, item.Operation, item.Template, item.IsExpression);
}
}
}
}
return(builder);
}
/// <summary>
/// 按照ResourceID来更新数据
/// </summary>
/// <param name="resourceID"></param>
/// <param name="assignees"></param>
public virtual void Update(string resourceID, TTaskAssigneeCollection assignees)
{
resourceID.CheckStringIsNullOrEmpty("resourceID");
assignees.NullCheck("assignees");
StringBuilder strB = new StringBuilder();
ORMappingItemCollection mappings = GetMappingInfo(new Dictionary <string, object>());
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
wBuilder.AppendItem("RESOURCE_ID", resourceID);
wBuilder.AppendTenantCode();
strB.AppendFormat("DELETE {0} WHERE {1}",
mappings.TableName,
wBuilder.ToSqlString(TSqlBuilder.Instance));
foreach (TTaskAssignee assignee in assignees)
{
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
InsertSqlClauseBuilder builder = ORMapping.GetInsertSqlClauseBuilder(assignee, mappings);
strB.AppendFormat("INSERT INTO {0}{1}",
mappings.TableName,
builder.ToSqlString(TSqlBuilder.Instance));
}
DbHelper.RunSqlWithTransaction(strB.ToString(), GetConnectionName());
}
private static string PrepareUpdateData(SCLock lockData, bool forceOverride)
{
ORMappingItemCollection mappingInfo = GetMappingInfo();
UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(lockData, mappingInfo);
WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(lockData, mappingInfo);
if (forceOverride == false)
{
wBuilder.AppendItem("LockTime", "DATEADD(SECOND, -EffectiveTime, GETDATE())", "<", true);
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat("UPDATE {0} SET {1} WHERE {2}",
mappingInfo.TableName,
uBuilder.ToSqlString(TSqlBuilder.Instance),
wBuilder.ToSqlString(TSqlBuilder.Instance));
sql.Append(TSqlBuilder.Instance.DBStatementSeperator);
sql.Append("SELECT @@ROWCOUNT");
sql.Append(TSqlBuilder.Instance.DBStatementSeperator);
sql.AppendFormat("SELECT * FROM {0} WHERE LockID = {1}",
GetMappingInfo().TableName,
TSqlBuilder.Instance.CheckUnicodeQuotationMark(lockData.LockID));
return(sql.ToString());
}
public SchemaObjectCollection LoadScopeItems(string[] ids, string scopeType, bool normalOnly, DateTime timePoint)
{
ids.NullCheck("ids");
string sql = "SELECT * FROM SC.SchemaObject WHERE ";
if (ids.Length > 0)
{
InSqlClauseBuilder inSql = new InSqlClauseBuilder("ID").AppendItem(ids);;
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
if (normalOnly)
{
where.NormalFor("Status");
}
where.AppendItem("SchemaType", scopeType);
var timeBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
sql += new ConnectiveSqlClauseCollection(inSql, timeBuilder, where).ToSqlString(TSqlBuilder.Instance);
return(LoadSchemaObjects(sql));
}
else
{
return(new SchemaObjectCollection());
}
}
/// <summary>
/// 更新一个容器下的Acl信息
/// </summary>
/// <param name="containerID"></param>
/// <param name="aclItems"></param>
public void Update(DEAclContainer container)
{
container.NullCheck("container");
container.FillMembersProperties();
ORMappingItemCollection mappings = this.GetMappingInfo();
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("ContainerID", container.ContainerID);
string sql = this.GetUpdateSql(container.ContainerID, container.Members);
using (TransactionScope scope = TransactionScopeFactory.Create())
{
DBTimePointActionContext.Current.TimePoint = (DateTime)DbHelper.RunSqlReturnScalar(
sql, this.GetConnectionName());
//foreach (SCAclItem aclItem in container.Members)
//{
// SCSnapshotBasicAdapter.Instance.UpdateCurrentSnapshot(mappings.TableName,
// mappings.TableName + "_Current",
// ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(aclItem, mappings));
//}
scope.Complete();
}
}
public int GetMaxInnerSort(string id, string[] childSchemaTypes, DateTime timePoint)
{
IConnectiveSqlClause timeBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("ParentID", id);
InSqlClauseBuilder inSql = null;
if (childSchemaTypes != null && childSchemaTypes.Length > 0)
{
inSql = new InSqlClauseBuilder("ChildSchemaType").AppendItem(childSchemaTypes);
}
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(where, timeBuilder);
if (inSql != null)
{
connectiveBuilder.Add(inSql);
}
string sql = "SELECT ISNULL(MAX(InnerSort), 0) AS C FROM " + GetMappingInfo().TableName + " WHERE " + connectiveBuilder.ToSqlString(TSqlBuilder.Instance);
int result = (int)DbHelper.RunSqlReturnScalar(sql, this.GetConnectionName());
return(result);
}
private static StringBuilder BuildSqlClause(WfMatrixQueryParamCollection queryParams)
{
var matrixIdParam = queryParams.Find(p => p.QueryName == DB_FIELD_MATRIX_ID);
string inSqlClause = string.Empty;
foreach (var para in queryParams)
{
if (para == matrixIdParam)
{
continue; //|| string.IsNullOrEmpty(para.QueryValue) 空值也作为条件
}
inSqlClause = BuilderSubSqlClause(para, matrixIdParam.QueryValue, inSqlClause);
}
StringBuilder result = new StringBuilder(SELECT_SQL_CLAUSE);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("R." + matrixIdParam.QueryName, matrixIdParam.QueryValue);
builder.AppendTenantCode("R.TENANT_CODE");
result.Append(builder.ToSqlString(TSqlBuilder.Instance));
if (!string.IsNullOrEmpty(inSqlClause))
{
result.AppendFormat(" AND R.{0} IN ({1})", DB_FIELD_MATRIX_ROW_ID, inSqlClause);
}
return(result);
}
public int Delete(string wfMatrixID)
{
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
whereBuilder.AppendItem(DB_FIELD_MATRIX_ID, wfMatrixID);
whereBuilder.AppendTenantCode();
StringBuilder strBuilder = new StringBuilder();
strBuilder.Append(DELETE_SQL_CLAUSE);
strBuilder.Append(whereBuilder.ToSqlString(TSqlBuilder.Instance));
strBuilder.Append(TSqlBuilder.Instance.DBStatementSeperator);
strBuilder.Append(DELETE_MR_SQL_CLAUSE);
strBuilder.Append(whereBuilder.ToSqlString(TSqlBuilder.Instance));
strBuilder.Append(TSqlBuilder.Instance.DBStatementSeperator);
strBuilder.Append(DELETE_MC_SQL_CLAUSE);
strBuilder.Append(whereBuilder.ToSqlString(TSqlBuilder.Instance));
strBuilder.Append(TSqlBuilder.Instance.DBStatementSeperator);
strBuilder.Append(DELETE_MU_SQL_CLAUSE);
strBuilder.Append(whereBuilder.ToSqlString(TSqlBuilder.Instance));
return(DbHelper.RunSqlWithTransaction(strBuilder.ToString(), ConnectionDefine.DBConnectionName));
}
/// <summary>
/// 获取可用的排序号
/// </summary>
/// <param name="userID">用户ID</param>
/// <returns></returns>
public int GetMaxSort(string userID)
{
ExceptionHelper.TrueThrow <ArgumentNullException>(null == userID, "userID");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("USER_ID", DeluxeIdentity.CurrentUser.ID);
builder.AppendTenantCode(typeof(TaskCategory));
int result = 0;
string strSQL = string.Format("SELECT MAX(INNER_SORT_ID) AS NUM FROM WF.USER_TASK_CATEGORY WHERE {0}",
builder.ToSqlString(TSqlBuilder.Instance));
object num = DbHelper.RunSqlReturnScalar(strSQL);
if (num.ToString() != string.Empty)
{
result = (int)num;
//当排序号以达到最大值999时，仍返回最大值
if (result < 999)
{
result++;
}
}
return(result);
}
public void Update(string processID, WfProcessCurrentAssigneeCollection assignees)
{
processID.CheckStringIsNullOrEmpty("processID");
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
wBuilder.AppendItem("PROCESS_ID", processID);
wBuilder.AppendTenantCodeSqlClause(typeof(WfProcessCurrentActivity));
string sqlDelete = string.Format(
"DELETE WF.PROCESS_CURRENT_ASSIGNEES WHERE {0}",
wBuilder.ToSqlString(TSqlBuilder.Instance));
StringBuilder strB = new StringBuilder();
foreach (WfProcessCurrentAssignee assignee in assignees)
{
if (strB.Length > 0)
{
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
}
strB.Append(ORMapping.GetInsertSql(assignee, TSqlBuilder.Instance));
}
string sql = sqlDelete;
if (strB.Length > 0)
{
sql += TSqlBuilder.Instance.DBStatementSeperator + strB.ToString();
}
DbHelper.RunSqlWithTransaction(sql, GetConnectionName());
}
private static SqlCommand ReadUpdateSql(string table, Action <List <string> > whereColunmAction, Action <List <string> > updateColumnAction)
{
SqlCommand cmd = new SqlCommand();
List <string> whereColunms = new List <string>();
whereColunmAction(whereColunms);
List <string> updateColumns = new List <string>();
updateColumnAction(updateColumns);
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
whereColunms.ForEach(w =>
{
var p = AddParameter(cmd, w);
where.AppendItem(w, p.ParameterName, "=", true);
});
UpdateSqlClauseBuilder update = new UpdateSqlClauseBuilder();
updateColumns.ForEach(u =>
{
var p = AddParameter(cmd, u);
update.AppendItem(u, p.ParameterName, "=", true);
}
);
string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", table,
update.ToSqlString(TSqlBuilder.Instance),
where.ToSqlString(TSqlBuilder.Instance));
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.UpdatedRowSource = UpdateRowSource.None;
return(cmd);
}
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("TASK_GUID", Request.QueryString["id"]);
dataSourceMain.Condition = where;
}
private string BuildWhereCondition()
{
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(this.Condition,
new AdjustConditionValueDelegate(AdjustQueryConditionValue));
builder.AppendItem("SEND_TO_USER", DeluxeIdentity.CurrentUser.ID);
WhereSqlClauseBuilder whereExpireTime = new WhereSqlClauseBuilder(LogicOperatorDefine.Or);
whereExpireTime.AppendItem("EXPIRE_TIME", "null", "is", true);
whereExpireTime.AppendItem("EXPIRE_TIME", "getdate()", ">", true);
WhereSqlClauseBuilder whereStatus = new WhereSqlClauseBuilder();
whereStatus.AppendItem("STATUS", ((int)TaskStatus.Yue).ToString());
var conditionGroup1 = new ConnectiveSqlClauseCollection(whereExpireTime, whereStatus);
WhereSqlClauseBuilder whereStatus2 = new WhereSqlClauseBuilder();
whereStatus2.AppendItem("STATUS", ((int)TaskStatus.Ban).ToString());
//collection2.Add(collection1);
ConnectiveSqlClauseCollection collection2 = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or, conditionGroup1, whereStatus2);
var allConditions = new ConnectiveSqlClauseCollection(builder, collection2);
return(allConditions.ToSqlString(TSqlBuilder.Instance));
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = TimePointContext.Current.UseCurrentTime ? @"SC.UserAndContainerSnapshot_Current C
INNER JOIN SC.SchemaUserSnapshot_Current U ON U.ID =C.UserID
INNER JOIN SC.SchemaRoleSnapshot_Current R ON R.ID = C.ContainerID
INNER JOIN SC.SchemaMembersSnapshot_Current S ON S.MemberID = R.ID
INNER JOIN SC.SchemaApplicationSnapshot_Current A ON A.ID = S.ContainerID" : @"SC.UserAndContainerSnapshot C
INNER JOIN SC.SchemaUserSnapshot U ON U.ID =C.UserID
INNER JOIN SC.SchemaRoleSnapshot R ON R.ID = C.ContainerID
INNER JOIN SC.SchemaMembersSnapshot S ON S.MemberID = R.ID
INNER JOIN SC.SchemaApplicationSnapshot A ON A.ID = S.ContainerID";
qc.SelectFields = "R.*, A.ID AS AppID,A.DisplayName AS AppName,A.Name As AppName2,A.CodeName as AppCodeName";
base.OnBuildQueryCondition(qc);
WhereSqlClauseBuilder wb = new WhereSqlClauseBuilder();
wb.AppendItem("C.UserID", this.userId);
if (string.IsNullOrWhiteSpace(orderBy))
{
qc.OrderByClause = "A.DisplayName";
}
else
{
qc.OrderByClause = orderBy;
}
if (string.IsNullOrWhiteSpace(qc.WhereClause))
{
qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance);
}
else
{
qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
}
}
public SchemaObjectCollection QueryApplications(string[] schemaTypes, bool includingDeleted, DateTime timePoint)
{
SchemaObjectCollection result = new SchemaObjectCollection();
ConnectiveSqlClauseCollection timeConditionC = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "SC.");
InSqlClauseBuilder schemaBuilder = new InSqlClauseBuilder("SC.SchemaType");
schemaBuilder.AppendItem(schemaTypes);
WhereSqlClauseBuilder builderC = new WhereSqlClauseBuilder();
if (includingDeleted == false)
builderC.AppendItem("SC.Status", (int)SchemaObjectStatus.Normal);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(schemaBuilder, builderC, timeConditionC);
string sql = string.Format("SELECT SC.*" +
"\nFROM SC.SchemaObject SC" +
"\nWHERE {0}", connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView);
return result;
}
private void GridViewDataBind()
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
TreeNode tn = this.trvProjectClass.SelectedNode;
if (tn != null)
{
String projectClassId = tn.Value;
if (!String.IsNullOrEmpty(projectClassId))
{
builder.AppendItem(ProjectInfoDBConst.ProjectClassID, projectClassId + "%", "LIKE");
}
else
{
btnEditProjectClass.Enabled = false;
}
}
else
{
btnEditProjectClass.Enabled = false;
}
List <ProjectInfo> infoList = ProjectInfoAdapter.Instance.GetProjectInfoList(builder);
GridViewControl.GridViewDataBind <ProjectInfo>(this.gvList, infoList);
//this.gvList.DataSource = projectList;
//this.gvList.DataBind();
}
public IEnumerable <ReferenceHistoryEntry> GetReferenceHistoryEntries(string objId)
{
ReferenceHistoryEntryCollection list = new ReferenceHistoryEntryCollection();
WhereSqlClauseBuilder where1 = new WhereSqlClauseBuilder();
where1.AppendItem("R.ObjectID", objId);
WhereSqlClauseBuilder where2 = new WhereSqlClauseBuilder();
where2.AppendItem("R.ParentID", objId);
var sql = string.Format(@"SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName
FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ParentID = O.ID
WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {0}
UNION
SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName
FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ObjectID = O.ID
WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {1} ORDER BY VersionStartTime ASC
", where1.ToSqlString(TSqlBuilder.Instance), where2.ToSqlString(TSqlBuilder.Instance));
using (var context = DbHelper.GetDBContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
var cmd = db.GetSqlStringCommand(sql);
using (var dr = db.ExecuteReader(cmd))
{
ORMapping.DataReaderToCollection(list, dr);
}
}
return(list);
}
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
var allConditions = new ConnectiveSqlClauseCollection(this.DeluxeSearch.GetCondition());
var condition = this.CurrentAdvancedSearchCondition;
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(condition);
string categoryID = Request.QueryString["categoryID"];
if (!string.IsNullOrEmpty(categoryID))
{
//DECategory dcg = CategoryAdapter.Instance.GetByID(categoryID);
//dcg.FullPath += "%";
if (categoryID != "undefined")
{
builder.AppendItem("SchemaType", categoryID);
}
}
allConditions.Add(builder);
this.dataSourceMain.Condition = allConditions;
}
private static ConnectiveSqlClauseCollection SchemaStrict(ConnectiveSqlClauseCollection c, string prefix, PC.StandardObjectSchemaType schema)
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem(prefix + "SchemaType", schema);
c.Add(where);
return c;
}
/// <summary>
/// 根据<see cref="IConnectiveSqlClause"/>指定的条件和时间点载入对象
/// </summary>
/// <param name="condition">表示条件的<see cref="IConnectiveSqlClause"/></param>
/// <param name="timePoint">时间点 - 或 - <see cref="DateTime.MinValue"/>表示当前时间</param>
/// <returns>一个<see cref="DESchemaObjectCollection"/>，包含条件指定的对象。</returns>
public DESchemaObjectCollection Load(IConnectiveSqlClause condition, DateTime timePoint, bool loadNormalStatus = true)
{
var timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
DESchemaObjectCollection result = new DESchemaObjectCollection();
if (condition.IsEmpty == false)
{
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(condition, timePointBuilder);
if (loadNormalStatus)
{
WhereSqlClauseBuilder statusBuilder = new WhereSqlClauseBuilder();
statusBuilder.AppendItem("Status", (int)SchemaObjectStatus.Normal);
connectiveBuilder.Add(statusBuilder);
}
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
VersionedObjectAdapterHelper.Instance.FillData(GetMappingInfo().TableName, connectiveBuilder, this.GetConnectionName(),
reader =>
{
result.LoadFromDataReader(reader);
});
}
}
return(result);
}
/// <summary>
/// 读取没有安排到任务中的JobID的集合
/// </summary>
/// <param name="batchCount"></param>
/// <returns></returns>
public IList <string> LoadUnTaskedJobs(int batchCount, TimeSpan timeOffset)
{
string sqlTemplate = "SELECT {0} J.JOB_ID FROM WF.JOBS J LEFT JOIN WF.SYS_NOT_RUNNING_TASK T WITH(UPDLOCK, READPAST) ON J.JOB_ID = T.RESOURCE_ID WHERE (T.STATUS IS NULL) AND {1} ORDER BY J.CREATE_TIME";
string top = batchCount >= 0 ? "TOP " + batchCount : string.Empty;
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("ENABLED", "1");
WhereSqlClauseBuilder builder2 = new WhereSqlClauseBuilder(LogicOperatorDefine.Or);
builder2.AppendItem("LAST_START_EXE_TIME", string.Format("DATEADD(SECOND, -{0}, GETDATE())", (int)timeOffset.TotalSeconds), "<", true);
builder2.AppendItem("LAST_START_EXE_TIME", "NULL", "IS", true);
string sql = string.Format(sqlTemplate, top, new ConnectiveSqlClauseCollection(builder, builder2).ToSqlString(TSqlBuilder.Instance));
List <string> result = new List <string>(800);
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
DataTable table = db.ExecuteDataSet(CommandType.Text, sql).Tables[0];
foreach (DataRow row in table.Rows)
{
result.Add(row["JOB_ID"].ToString());
}
}
return(result);
}
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
if (this.IsPostBack == false && string.IsNullOrEmpty(this.Request.QueryString["id"]) == false)
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("ID", this.Request.QueryString["id"]);
this.dataSourceMain.Condition = where;
}
else
{
//if (this.AdvanceSearchEnabled)
{
var condition = this.CurrentAdvancedSearchCondition;
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(condition);
this.dataSourceMain.Condition = new ConnectiveSqlClauseCollection(builder, this.DeluxeSearch.GetCondition());
}
//else
//{
// this.dataSourceMain.Condition = this.DeluxeSearch.GetCondition();
//}
}
}
public static void IfEmptyFillDefault(this WhereSqlClauseBuilder builder)
{
if (builder.IsEmpty)
{
builder.AppendItem("1 = 1");
}
}
public TaxInfo LoadTaxInfo(String yearMonth, String taxID)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(TaxInfoDBConst.TaxID, taxID);
return(GetTaxInfoList(yearMonth, builder).FirstOrDefault());
}
private void BindDataGrid()
{
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(this.UserUploadCondition);
if (this.processUsers.ReadOnly)
{
this.ObjectDataSourceUploadLogs.Condition = builder;
}
else
{
ConnectiveSqlClauseCollection collection = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And);
collection.Add(builder);
WhereSqlClauseBuilder userwherebuilder = new WhereSqlClauseBuilder();
userwherebuilder.LogicOperator = LogicOperatorDefine.Or;
foreach (var item in this.processUsers.SelectedOuUserData)
{
userwherebuilder.AppendItem("OPERATOR_ID", item.ID);
}
collection.Add(userwherebuilder);
this.ObjectDataSourceUploadLogs.Condition = collection.ToSqlString(TSqlBuilder.Instance);
}
this.ObjectDataSourceUploadLogs.LastQueryRowCount = -1;
this.DeluxeGridUploadLog.PageIndex = 0;
this.DeluxeGridUploadLog.SelectedKeys.Clear();
this.DeluxeGridUploadLog.DataBind();
}
public AUSchemaCategory LoadByID(string id, DateTime timePoint)
{
var conditions = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("ID", id);
conditions.Add(where);
AUSchemaCategory cate = null;
AUCommon.DoDbAction(() =>
{
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
VersionedObjectAdapterHelper.Instance.FillData(ORMapping.GetMappingInfo(typeof(AUSchemaCategory)).TableName, conditions, this.GetConnectionName(),
reader =>
{
if (reader.Read())
{
cate = new AUSchemaCategory();
ORMapping.DataReaderToObject <AUSchemaCategory>(reader, cate);
}
});
}
});
return(cate);
}
internal OAMQMessages LoadByProcessInstanceID(int processInstanceID)
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
//where.AppendItem("Status", 0);
//where.AppendItem("Flowmess", 1);
where.AppendItem("Viewtype", -2);
where.AppendItem("FlowID", processInstanceID);
StringBuilder sql = new StringBuilder();
sql.AppendFormat(" SELECT * FROM {0} WHERE {1} ORDER BY Sendertime ASC",
ORMapping.GetTableName(typeof(OAMQMessages)),
where.ToSqlString(TSqlBuilder.Instance));
List <OAMQMessages> result = ExecuteQuery(sql.ToString());
return(result.Count > 0 ? result[0] : null);
}
public List <ReportFee> GetReportFeeList(string reportID)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(ReportFeeDBConst.ReportID, reportID);
return(GetReportFeeList(builder));
}
/// <summary>
/// 获得指定ID的Material
/// </summary>
/// <param name="materialID">指定ID</param>
/// <returns>MaterialList</returns>
public MaterialList LoadMaterialByMaterialID(string materialID)
{
ExceptionHelper.CheckStringIsNullOrEmpty(materialID, "materialID");
MaterialList materials = new MaterialList();
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
wBuilder.AppendItem("ID", materialID);
wBuilder.AppendTenantCode(typeof(Material));
string sql = "SELECT * FROM WF.MATERIAL WHERE " + wBuilder.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);
materials.Add(material);
}
}
}
DecorateMaterials(materials);
return(materials);
}
public SchemaObjectCollection LoadCurrentUsers(string ownerID)
{
ownerID.CheckStringIsNullOrEmpty("ownerID");
var timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(DateTime.MinValue);
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
whereBuilder.AppendItem("C.OwnerID", ownerID);
timePointBuilder.Add(whereBuilder);
string sql = string.Format(
"SELECT SC.* FROM {0} SC INNER JOIN SC.ConditionCalculateResult C ON SC.ID = C.UserID WHERE {1}",
ORMapping.GetMappingInfo(typeof(SchemaObjectBase)).TableName, timePointBuilder.ToSqlString(TSqlBuilder.Instance));
SchemaObjectCollection result = new SchemaObjectCollection();
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
result.LoadFromDataView(DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0].DefaultView);
}
return result;
}
public WfClientUserOperationLogPageQueryResult QueryUserOperationLogByProcessID(string processID, int startRowIndex, int maximumRows, string orderBy, int totalCount)
{
processID.CheckStringIsNullOrEmpty("processID");
OperationContext.Current.FillContextToOguServiceContext();
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("PROCESS_ID", processID);
builder.AppendTenantCode();
if (orderBy.IsNullOrEmpty())
{
orderBy = "ID DESC";
}
QueryCondition qc = new QueryCondition(startRowIndex, maximumRows,
"*",
ORMapping.GetMappingInfo(typeof(UserOperationLog)).TableName,
orderBy);
qc.WhereClause = builder.ToSqlString(TSqlBuilder.Instance);
return(QueryUserOperationLog(qc, totalCount));
}
/// <summary>
/// 根据ResourceID和ClassName加载相关的数据
/// </summary>
/// <typeparam name="TRelative"></typeparam>
/// <typeparam name="TRelativeCollection"></typeparam>
/// <param name="resourceID"></param>
/// <param name="className"></param>
/// <returns></returns>
public TRelativeCollection LoadRelativeData <TRelative, TRelativeCollection>(
string resourceID, string className)
where TRelative : GenericFormRelativeData, new()
where TRelativeCollection : EditableDataObjectCollectionBase <TRelative>, new()
{
resourceID.CheckStringIsNullOrEmpty("resourceID");
className.CheckStringIsNullOrEmpty("className");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("RESOURCE_ID", resourceID).AppendItem("CLASS", className);
builder.AppendTenantCode(typeof(TRelative));
string sql = string.Format("SELECT * FROM WF.GENERIC_FORM_RELATIVE_DATA WHERE {0} ORDER BY SORT_ID",
builder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
TRelativeCollection result = new TRelativeCollection();
ORMapping.DataViewToCollection(result, table.DefaultView);
foreach (TRelative item in result)
{
XmlDocument xmlDoc = XmlHelper.CreateDomDocument(item.XmlContent);
XmlHelper.DeserializeToObject(xmlDoc, item);
}
return(result);
}
public IEnumerable<ReferenceHistoryEntry> GetReferenceHistoryEntries(string objId)
{
ReferenceHistoryEntryCollection list = new ReferenceHistoryEntryCollection();
WhereSqlClauseBuilder where1 = new WhereSqlClauseBuilder();
where1.AppendItem("R.ObjectID", objId);
WhereSqlClauseBuilder where2 = new WhereSqlClauseBuilder();
where2.AppendItem("R.ParentID", objId);
var sql = string.Format(@"SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName
FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ParentID = O.ID
WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {0}
UNION
SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName
FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ObjectID = O.ID
WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {1} ORDER BY VersionStartTime ASC
", where1.ToSqlString(TSqlBuilder.Instance), where2.ToSqlString(TSqlBuilder.Instance));
using (var context = DbHelper.GetDBContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
var cmd = db.GetSqlStringCommand(sql);
using (var dr = db.ExecuteReader(cmd))
{
ORMapping.DataReaderToCollection(list, dr);
}
}
return list;
}
/// <summary>
/// 根据ownerID和type加载ConditionOwner对象
/// </summary>
/// <param name="ownerID"></param>
/// <param name="type"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SCConditionCollection Load(string ownerID, string type, DateTime timePoint)
{
if (type.IsNullOrEmpty())
{
type = "Default";
}
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("OwnerID", ownerID);
builder.AppendItem("Type", type);
SCConditionCollection conditions = this.Load(builder, timePoint);
return(conditions);
}
private void BindTaskList()
{
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
whereBuilder.AppendItem("ENABLED", "1");
whereBuilder.AppendItem("JOB_STATUS", 0);
string category = txtCategory.Text.Trim();
string name = txtName.Text.Trim();
if (string.IsNullOrEmpty(category) == false)
whereBuilder.AppendItem("JOB_CATEGORY", category);
if (string.IsNullOrEmpty(name) == false)
whereBuilder.AppendItem("JOB_NAME", name);
this.WfJobs = JobBaseAdapter.Instance.UPDLOCKLoadJobs(decimal.ToInt32(numericUpDown1.Value), whereBuilder);
this.dataGridView1.DataSource = this.WfJobs;
}
public ProjectInfo LoadProjectInfo(String projectId)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(ProjectInfoDBConst.ProjectID, projectId);
return(GetProjectInfoList(builder).FirstOrDefault());
}
public Int32 DeleteDepartmentInfo(String deptID)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(DepartmentInfoDBConst.DepartmentID, deptID);
String sql = String.Format("DELETE FROM {0} WHERE {1} ", DepartmentInfoDBConst.TableName, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public Int32 ChangeStatus(String projectClassId, Int32 userFlag)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(ProjectClassInfoDBConst.ProjectClassID, projectClassId);
String sql = String.Format("UPDATE {0} SET USE_FLAG={1} WHERE {2} ", ProjectClassInfoDBConst.TableName, userFlag, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public Int32 ChangeDimission(String personID, bool dimission)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(PersonInfoDBConst.PersonID, personID);
String sql = String.Format("UPDATE {0} SET DIMISSION={1} WHERE {2} ", PersonInfoDBConst.TableName, dimission ? 1 : 0, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public Int32 DeleteUserInfo(String userId)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(UserInfoDBConst.UserId, userId);
String sql = String.Format("DELETE FROM {0} WHERE {1} ", UserInfoDBConst.TableName, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public Int32 ChangeStatus(String userId, Int32 status)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(UserInfoDBConst.UserId, userId);
String sql = String.Format("UPDATE {0} SET LOGOUT={1} WHERE {2} ", UserInfoDBConst.TableName, status, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public Int32 ChangePassword(String userId, String password)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(UserInfoDBConst.UserId, userId);
String sql = String.Format("UPDATE {0} SET PASSWORD='******' WHERE {2} ", UserInfoDBConst.TableName, CryptoHelper.Encode(password), builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
if (this.IsPostBack == false && this.Request["id"] != null)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("SynchronizeID", this.Request["id"]);
this.dataSourceMain.Condition = builder;
}
}