/// <summary> /// /// </summary> /// <param name="fillAssignees"></param> /// <param name="action"></param> /// <returns></returns> public WfProcessCurrentInfoCollection Load(bool fillAssignees, Action<WhereSqlClauseBuilder> action) { action.NullCheck("action"); WfProcessCurrentInfoCollection result = new WfProcessCurrentInfoCollection(); WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder(); action(whereBuilder); string fieldNames = ORMapping.GetSelectFieldsNameSql<WfProcessInstanceData>("Data"); string sql = string.Format("SELECT {0} FROM WF.PROCESS_INSTANCES WHERE {1}", fieldNames, whereBuilder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; result.LoadFromDataView(table.DefaultView); if (fillAssignees) FillAssignees(result); return result; }
private void BuildCondition(QueryCondition qc) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); if (this.deepOption == 1) { builder.AppendCondition("C.ContainerID", this.scope.ID); } else { if (deepOption == 2) { builder.AppendItem("C.ContainerID", this.scope.ID, "<>"); } } builder.NormalFor("C.Status").NormalFor("S.Status").NormalFor("SM.Status").NormalFor("AUS.Status"); var timeBulder1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("C."); var timeBulder2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("S."); var time3 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("AUS."); var time4 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("SM."); var conditionClause = new ConnectiveSqlClauseCollection(builder, timeBulder1, timeBulder2, time3, time4).ToSqlString(TSqlBuilder.Instance); if (string.IsNullOrEmpty(qc.WhereClause) == false) qc.WhereClause += " AND (" + conditionClause + ")"; else qc.WhereClause = conditionClause; }
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(); }
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 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; }
//计算薪资 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; }
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("TASK_GUID", Request.QueryString["id"]); dataSourceMain.Condition = where; }
protected override void OnBuildQueryCondition(QueryCondition qc) { base.OnBuildQueryCondition(qc); qc.SelectFields = "*"; qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.AUAdminScopeItemSnapshot_Current" : "SC.AUAdminScopeItemSnapshot"; if (string.IsNullOrEmpty(qc.OrderByClause)) qc.OrderByClause = "AUScopeItemName ASC"; WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendCondition("SchemaType", this.scopeType).NormalFor("Status"); var timeBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(); var allCondition = new ConnectiveSqlClauseCollection(builder, timeBuilder).ToSqlString(TSqlBuilder.Instance); if (string.IsNullOrEmpty(qc.WhereClause)) { qc.WhereClause = allCondition; } else { qc.WhereClause = qc.WhereClause + "AND (" + allCondition + ")"; } }
/// <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 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; }
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; }
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 static IEnumerable<SCSimpleObject> QueryParents(string unitID, bool includingSelf, DateTime timePoint) { if (string.IsNullOrEmpty(unitID) == false) { string timeString = timePoint == DateTime.MinValue ? TSqlBuilder.Instance.DBCurrentTimeFunction : TSqlBuilder.Instance.FormatDateTime(timePoint); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder().AppendCondition("ID", unitID); string sql = string.Format(sqlTemplate, timeString, where.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, ConnectionName).Tables[0]; DataView view = new DataView(table); view.Sort = "ID"; SCSimpleObjectCollection tempParents = new SCSimpleObjectCollection(); FillAllParents(unitID, view, includingSelf, tempParents); tempParents.Reverse(); return tempParents; } else { return emptyResult; } }
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 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 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 DeleteReportFee(String reportID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(ReportFeeDBConst.ReportID, reportID); String sql = String.Format("DELETE FROM {0} WHERE {1} ", ReportFeeDBConst.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 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; }
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 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 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; }
/// <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 = containerIDs.ToArray(); SCContainerAndPermissionCollection result = null; var roleIDs = (from r in new OguUser(userID).Roles.GetAllRoles() select r.ID).ToArray(); if (ids.Length > 0 && roleIDs.Length > 0) { var timeConditon1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("A."); var condition = new WhereSqlClauseBuilder(); condition.AppendItem("A.Status", (int)SchemaObjectStatus.Normal); InSqlClauseBuilder inSql = new InSqlClauseBuilder("A.ContainerID"); inSql.AppendItem(ids); InSqlClauseBuilder inSqlRole = new InSqlClauseBuilder("A.MemberID"); inSqlRole.AppendItem(roleIDs); var sql = string.Format( "SELECT A.* FROM SC.Acl_Current A WHERE {0} ORDER BY SortID ", new ConnectiveSqlClauseCollection(timeConditon1, condition, inSql, inSqlRole).ToSqlString(TSqlBuilder.Instance)); result = new SCContainerAndPermissionCollection(); AUCommon.DoDbAction(() => { 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; }
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; } }
private static JobCollection FetchStandByJobs(int batchCount) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("ENABLED", "1"); builder.AppendItem("JOB_STATUS", (int)JobStatus.StandBy); return JobBaseAdapter.Instance.UPDLOCKLoadJobs(batchCount, builder); }
public String CreatePersonCode(String departID) { DepartmentInfo departInfo = DepartmentInfoAdapter.Instance.LoadDepartmentInfo(departID); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(PersonInfoDBConst.DepartmentID, departID); String sql = String.Format("SELECT ISNULL(MAX(PERSON_CODE) + 1,'{0}01') FROM {1} WHERE {2}", departInfo.DepartmentCode, PersonInfoDBConst.TableName, builder.ToSqlString()); DataTable dt = _DataHelper.GetDataTable(sql); return dt.Rows[0][0].ToString(); }
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); }
public SCOperationLogCollection LoadByResourceID(string resourceID) { resourceID.CheckStringIsNullOrEmpty("resourceID"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("ResourceID", resourceID); return Load(builder); }
public static Conditions.SCConditionCollection LoadConditions(string[] ids) { if (ids.Length > 0) { var mIdIn = new InSqlClauseBuilder("OwnerID"); mIdIn.AppendItem(ids); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("Status", (int)SchemaObjectStatus.Normal); return(Adapters.SCConditionAdapter.Instance.Load(new ConnectiveSqlClauseCollection(mIdIn, where), DateTime.MinValue)); } else { return(new Conditions.SCConditionCollection()); } }
/// <summary> /// 根据对象拼Insert语句 /// </summary> /// <typeparam name="T">对象类型</typeparam> /// <param name="graph">对象</param> /// <param name="mapping">映射关系</param> /// <param name="builder">生成Sql语句类型的Builder如TSqlBuilder或PlSqlBuilder</param> /// <param name="ignoreProperties">忽略的字段</param> /// <returns>根据传入的对象和对象映射时需要忽略的字段以及类定义上的表名,生成完整的Insert语句</returns> public static string GetUpdateSql <T>(T graph, ORMappingItemCollection mapping, ISqlBuilder builder, params string[] ignoreProperties) { ExceptionHelper.FalseThrow <ArgumentNullException>(graph != null, "graph"); ExceptionHelper.FalseThrow <ArgumentNullException>(mapping != null, "mapping"); ExceptionHelper.FalseThrow <ArgumentNullException>(builder != null, "builder"); using (ORMappingContext context = ORMappingContext.GetContext()) { UpdateSqlClauseBuilder updateBuilder = GetUpdateSqlClauseBuilder(graph, mapping, ignoreProperties); WhereSqlClauseBuilder whereBuilder = GetWhereSqlClauseBuilderByPrimaryKey(graph, mapping); return(string.Format("UPDATE {0} SET {1} WHERE {2}", mapping.TableName, updateBuilder.ToSqlString(builder), whereBuilder.ToSqlString(builder))); } }
private FixedTimeTaskCollection InnerQueryBatch(TimeSpan timeScope, int batchCount) { string batchClause = batchCount >= 0 ? "TOP " + batchCount : string.Empty; WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("START_TIME", string.Format("DATEADD(second, -{0}, GETUTCDATE())", (int)timeScope.TotalSeconds), ">=", true); builder.AppendItem("START_TIME", string.Format("DATEADD(second, {0}, GETUTCDATE())", (int)timeScope.TotalSeconds), "<", true); builder.AppendTenantCode(); string sql = string.Format("SELECT {0} * FROM {1} WITH(UPDLOCK, READPAST) WHERE {2} ORDER BY SORT_ID ASC", batchClause, this.GetTableName(), builder.ToSqlString(TSqlBuilder.Instance)); return(this.QueryData(sql)); }
/// <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); }
protected override void OnBuildQueryCondition(QueryCondition qc) { qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.SchemaOrganizationSnapshot_Current O INNER JOIN SC.SchemaRelationObjectsSnapshot_Current R ON O.ID = R.ObjectID" : "SC.SchemaOrganizationSnapshot O INNER JOIN SC.SchemaRelationObjectsSnapshot R ON O.ID = R.ObjectID"; if (this.fileterRole) { qc.FromClause += TimePointContext.Current.UseCurrentTime ? " INNER JOIN SC.Acl_Current AC ON AC.ContainerID = O.ID INNER JOIN SC.SchemaRoleSnapshot_Current RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot_Current UC ON RS.ID = UC.ContainerID " : " INNER JOIN SC.Acl AC ON AC.ContainerID = O.ID INNER JOIN SC.SchemaRoleSnapshot RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot UC ON RS.ID = UC.ContainerID "; } qc.SelectFields = "DISTINCT O.*,R.FullPath"; if (string.IsNullOrEmpty(qc.OrderByClause)) { qc.OrderByClause = "R.InnerSort ASC"; } qc.WhereClause.IsNotEmpty((s) => qc.WhereClause += " AND "); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal); builder.AppendItem("O.Status", (int)SchemaObjectStatus.Normal); var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R."); var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O."); var schemaCondition = DataSourceUtil.SchemaTypeCondition("R.ChildSchemaType", "Organizations"); var allConditions = new ConnectiveSqlClauseCollection(builder, timeCondition, timeCondition2, schemaCondition); if (this.fileterRole && this.userID != null && this.permissions != null) { allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("AC.")); allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("RS.")); allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("UC.")); builder.AppendItem("AC.Status", (int)SchemaObjectStatus.Normal); builder.AppendItem("RS.Status", (int)SchemaObjectStatus.Normal); builder.AppendItem("UC.Status", (int)SchemaObjectStatus.Normal); builder.AppendItem("UC.UserID", this.userID); InSqlClauseBuilder inSql1 = new InSqlClauseBuilder("AC.ContainerPermission"); inSql1.AppendItem(this.permissions); allConditions.Add(inSql1); } qc.WhereClause += allConditions.ToSqlString(TSqlBuilder.Instance); }
private String ConvertCommonExpToCommon(String yearMonth, String feeID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(FeeMonthInfoDBConst.FeeID, feeID); FeeInfo feeInfo = FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder).FirstOrDefault(); if (feeInfo.FeeType.Equals(FeeType.Common)) { if (!String.IsNullOrEmpty(feeInfo.CalculateExp)) { return(String.Concat("(", this.ConvertCommonExp(yearMonth, feeInfo.CalculateExp), ")")); } return("[" + feeID + "]"); } return(feeInfo.CalculateExp); }
protected override string GetUpdateSql(WfDelegation data, ORMappingItemCollection mappings, Dictionary <string, object> context, string[] ignoreProperties) { UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings, "ApplicationName", "ProgramName"); uBuilder.AppendItem("APPLICATION_NAME", data.ApplicationName.IsNotEmpty() ? data.ApplicationName : string.Empty); uBuilder.AppendItem("PROGRAM_NAME", data.ProgramName.IsNotEmpty() ? data.ProgramName : string.Empty); WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings, "ApplicationName", "ProgramName"); wBuilder.AppendItem("APPLICATION_NAME", data.ApplicationName.IsNotEmpty() ? data.ApplicationName : string.Empty); wBuilder.AppendItem("PROGRAM_NAME", data.ProgramName.IsNotEmpty() ? data.ProgramName : string.Empty); return(string.Format("UPDATE {0} SET {1} WHERE {2}", mappings.TableName, uBuilder.ToSqlString(TSqlBuilder.Instance), wBuilder.ToSqlString(TSqlBuilder.Instance))); }
public SchemaObjectCollection LoadAUScope(string auID, string scope, bool normalRelation, bool normalObject, DateTime timePoint) { auID.NullCheck("auID"); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder().NormalFor("O.Status").NormalFor("S.Status"); where.AppendCondition("R.ContainerID", auID); where.AppendCondition("S.ScopeSchemaType", scope); if (normalRelation) where.NormalFor("R.Status"); if (normalObject) { where.NormalFor("O.Status"); } return LoadAUScope(where, timePoint); }
protected override void OnBuildQueryCondition(QueryCondition qc) { base.OnBuildQueryCondition(qc); WhereSqlClauseBuilder wb = new WhereSqlClauseBuilder(); wb.AppendItem("SynchronizeID", this.syncID); if (string.IsNullOrEmpty(qc.WhereClause)) { qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance); } else { qc.WhereClause = " AND " + wb.ToSqlString(TSqlBuilder.Instance); } }
public static CheckLockResult CheckLock(string lockID, string personID) { ExceptionHelper.CheckStringIsNullOrEmpty(lockID, "lockID"); ExceptionHelper.CheckStringIsNullOrEmpty(personID, "personID"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("LOCK_ID", lockID); builder.AppendTenantCode(); string sql = string.Format("SELECT *, GETDATE() AS [CURRENT_TIME] FROM WF.LOCK WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql).Tables[0]; return(new CheckLockResult(personID, table)); }
protected void ObjectDataSourceSelecting(object sender, ObjectDataSourceSelectingEventArgs e) { if (e.ExecutingSelectCount == false) { e.InputParameters["authType"] = (WfApplicationAuthType)int.Parse(this.rdoList.SelectedValue); if (views.ActiveViewIndex == 1) { if (string.IsNullOrEmpty(this.lastUser.Value)) { e.Cancel = true; } else { e.InputParameters["appName"] = null; e.InputParameters["programName"] = null; e.InputParameters["authType"] = WfApplicationAuthType.None; WfApplicationAuthCollection authInfo = WfApplicationAuthAdapter.Instance.GetUserApplicationAuthInfo(new OguUser(lastUser.Value)); string condition = "1=2"; if (authInfo.Count > 0) { ConnectiveSqlClauseCollection allMatch = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or); foreach (var item in authInfo) { WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("APPLICATION_NAME", item.ApplicationName); where.AppendItem("PROGRAM_NAME", item.ProgramName); where.AppendItem("AUTH_TYPE", item.AuthType); allMatch.Add(where); } condition = allMatch.ToSqlString(TSqlBuilder.Instance); } else { e.Cancel = true; } e.InputParameters["where"] = condition; } } } }
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 WfClientProcessDescriptorInfoPageQueryResult QueryProcessDescriptorInfo(int startRowIndex, int maximumRows, string where, string orderBy, int totalCount) { OperationContext.Current.FillContextToOguServiceContext(); if (orderBy.IsNullOrEmpty()) { orderBy = "MODIFY_TIME DESC"; } string selectFields = "PROCESS_KEY, APPLICATION_NAME, PROGRAM_NAME, PROCESS_NAME, ENABLED, CREATE_TIME, CREATOR_ID, CREATOR_NAME, MODIFY_TIME, MODIFIER_ID, MODIFIER_NAME, IMPORT_TIME, IMPORT_USER_ID, IMPORT_USER_NAME"; QueryCondition qc = new QueryCondition( startRowIndex, maximumRows, selectFields, ORMapping.GetMappingInfo(typeof(WfProcessDescriptorInfo)).TableName, orderBy); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendTenantCode(); if (where.IsNotEmpty()) { where += " AND "; } where += builder.ToSqlString(TSqlBuilder.Instance); qc.WhereClause = where; CommonAdapter adapter = new CommonAdapter(WfProcessDescriptorInfoAdapter.Instance.ConnectionName); WfProcessDescriptorInfoCollection processInfo = adapter.SplitPageQuery <WfProcessDescriptorInfo, WfProcessDescriptorInfoCollection>(qc, ref totalCount); WfClientProcessDescriptorInfoCollection clientInfo = new WfClientProcessDescriptorInfoCollection(); WfClientProcessDescriptorInfoConverter.Instance.ServerToClient(processInfo, clientInfo); WfClientProcessDescriptorInfoPageQueryResult result = new WfClientProcessDescriptorInfoPageQueryResult(); result.TotalCount = totalCount; result.QueryResult.CopyFrom(clientInfo); return(result); }
/// <summary> /// 序列化 /// </summary> /// <param name="obj"></param> /// <param name="serializer"></param> /// <returns></returns> public override IDictionary <string, object> Serialize(object obj, JavaScriptSerializer serializer) { WhereSqlClauseBuilder whereBuilder = (WhereSqlClauseBuilder)obj; Dictionary <string, object> result = new Dictionary <string, object>(); result.AddNonDefaultValue("LogicOperator", whereBuilder.LogicOperator); var itemsList = new ArrayList(); foreach (var item in whereBuilder) { itemsList.Add(item); } result["List"] = itemsList; return(result); }
public override void UpdateOperation() { DataView ouusers = new DataView(this.Context.OriginalData.Tables["OU_USERS"], string.Empty, "PARENT_GUID,USER_GUID", DataViewRowState.ModifiedCurrent); Database db = DatabaseFactory.Create(this.Context.InitialParams.AccreditAdminConnectionName); foreach (DataRowView drs in ouusers) { UpdateSqlClauseBuilder uBuilder = new UpdateSqlClauseBuilder(); uBuilder.AppendItem("DISPLAY_NAME", drs["DISPLAY_NAME"].ToString()); uBuilder.AppendItem("OBJ_NAME", drs["OBJ_NAME"].ToString()); uBuilder.AppendItem("ALL_PATH_NAME", drs["ALL_PATH_NAME"].ToString()); uBuilder.AppendItem("INNER_SORT", drs["INNER_SORT"].ToString()); uBuilder.AppendItem("GLOBAL_SORT", drs["GLOBAL_SORT"].ToString()); uBuilder.AppendItem("ORIGINAL_SORT", drs["ORIGINAL_SORT"].ToString()); uBuilder.AppendItem("SEARCH_NAME", drs["SEARCH_NAME"].ToString()); uBuilder.AppendItem("MODIFY_TIME", TSqlBuilder.Instance.DBCurrentTimeFunction, "=", true); uBuilder.AppendItem("STATUS", drs["STATUS"].ToString());//by majun 20120216 WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); wBuilder.AppendItem("PARENT_GUID", drs["PARENT_GUID"].ToString()); wBuilder.AppendItem("USER_GUID", drs["USER_GUID"].ToString()); //wBuilder.AppendItem("STATUS", drs["STATUS"].ToString());//by majun 20120216 string updatesql = string.Format("UPDATE OU_USERS SET {0} where {1}", uBuilder.ToSqlString(TSqlBuilder.Instance), wBuilder.ToSqlString(TSqlBuilder.Instance)); try { int count = db.ExecuteNonQuery(CommandType.Text, updatesql); if (count == 1) { this.UpdateCount++; //this.Context.InitialParams.Log.Write(string.Format("OU_USERS表 PARENT_GUID是{0},USER_GUID是{1}的记录更新成功,ALL_PATH_NAME是'{2}'", //drs["PARENT_GUID"].ToString(), drs["USER_GUID"].ToString(), drs["ALL_PATH_NAME"]).ToString()); } } catch (Exception ex) { this.Context.InitialParams.Log.Write(string.Format("OU_USERS表 PARENT_GUID是{0}USER_GUID 是{1}的记录执行更新时出错,错误是{2}", drs["PARENT_GUID"].ToString(), drs["USER_GUID"].ToString(), ex.Message)); } } }
private static ConnectiveSqlClauseCollection GetConnectiveSqlClauseBuilderFromMapping( object condition, ConditionMappingItemCollection mapping, bool ignoreDefaultValue, AdjustConditionValueDelegate acv, string[] ignoreProperties) { ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(); WhereSqlClauseBuilder whereBuilder = GetWhereSqlClauseBuilderFromMapping(condition, mapping.FilterByType <ConditionMappingItem>(), ignoreDefaultValue, acv, ignoreProperties); connectiveBuilder.Add(whereBuilder); FillInSqlClauseBuilderFromMapping(connectiveBuilder, condition, mapping.FilterByType <InConditionMappingItem>(), ignoreDefaultValue, acv, ignoreProperties); return(connectiveBuilder); }
internal static bool ValidateCodeNameWithContainer(string codeName, string[] schemaNames, string objectID, string parentID, bool normalOnly, bool ignoreVersions, DateTime timePoint) { var objects = SchemaObjectAdapter.Instance.LoadByCodeNameAndSchema(schemaNames.ToArray(), new string[] { codeName }, true, false, DateTime.MinValue); bool result = false; if (objects.Count == 0) { result = true; } else { WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("ContainerID", parentID); if (normalOnly) { where.AppendItem("Status", (int)SchemaObjectStatus.Normal); } InSqlClauseBuilder inSql = new InSqlClauseBuilder("MemberID"); inSql.AppendItem((from o in objects select o.ID).ToArray()); // 只取当前关系 var relations = SCMemberRelationAdapter.Instance.Load(new ConnectiveSqlClauseCollection(where, inSql), timePoint); bool exist = false; foreach (string schemaName in schemaNames) { foreach (SCMemberRelation o in relations) { if (o.ID != objectID && o.MemberSchemaType == schemaName) { exist = true; break; } } } result = exist == false; } return(result); }
private void ExecuteQuery() { LastQueryRowCount = -1; var whereCondition = (HtmlInputHidden)this.FindControlByID("whereCondition", true); var activityID = this.ActivityID; if (activityID.IsNotEmpty()) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem <string>("OWNER_ACTIVITY_ID", activityID); whereCondition.Value = builder.ToSqlString(TSqlBuilder.Instance); } LastQueryRowCount = -1; this.processGrid.SelectedKeys.Clear(); this.processGrid.PageIndex = 0; }
public SchemaObjectCollection LoadScopeItems(string scopeType, bool normalOnly, DateTime timePoint) { string sql = "SELECT * FROM SC.SchemaObject WHERE "; WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); if (normalOnly) { where.NormalFor("Status"); } where.AppendItem("SchemaType", scopeType); var timeBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint); sql += new ConnectiveSqlClauseCollection(timeBuilder, where).ToSqlString(TSqlBuilder.Instance); return(LoadSchemaObjects(sql)); }
/// <summary> /// 根据下级名称,载入指定的管理单元 /// </summary> /// <param name="conditions">条件集,O对象表,M关系表,S对象的快照表</param> /// <param name="snapshotTable">快照表的名称</param> /// <param name="normalOnly">是否仅包含正常对象</param> /// <param name="timePoint">时间点</param> /// <returns></returns> private SchemaObjectCollection LoadContainers(IConnectiveSqlClause conditions, bool normalOnly, DateTime timePoint) { string sql = @"SELECT O.* FROM SC.SchemaObject O INNER JOIN SC.SchemaMembers M ON O.ID = M.ContainerID AND O.SchemaType = M.ContainerSchemaType WHERE 1=1 AND "; var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "O."); var timeCondition3 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "M."); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); if (normalOnly) where.NormalFor("M.Status").NormalFor("O.Status"); sql += new ConnectiveSqlClauseCollection(timeCondition2, timeCondition3, where, conditions).ToSqlString(TSqlBuilder.Instance); return LoadSchemaObjects(sql); }
internal static SCRelationObject GetParentOURelation(string ouId) { if (ouId == PC.SCOrganization.RootOrganizationID) { return(null); } else { WhereSqlClauseBuilder wb = new WhereSqlClauseBuilder(); wb.AppendItem("Status", (int)SchemaObjectStatus.Normal); wb.AppendItem("ParentSchemaType", "Organizations"); wb.AppendItem("ChildSchemaType", "Organizations"); wb.AppendItem("ObjectID", ouId); return(PC.Adapters.SchemaRelationObjectAdapter.Instance.Load(wb, DateTime.MinValue).FirstOrDefault()); } }
protected override void OnBuildQueryCondition(QueryCondition qc) { qc.SelectFields = "*"; if (string.IsNullOrEmpty(qc.OrderByClause)) { qc.OrderByClause = "VersionStartTime DESC"; } qc.WhereClause.IsNotEmpty((s) => qc.WhereClause += " AND "); var builder = new WhereSqlClauseBuilder(); builder.AppendItem("Status", (int)SchemaObjectStatus.Normal); var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(); qc.WhereClause += new ConnectiveSqlClauseCollection(builder, timeCondition).ToSqlString(TSqlBuilder.Instance); }
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { //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(); //} e.InputParameters["bossMode"] = this.Request.QueryString["view"] == "boss"; }
internal EmailAttachmentCollection LoadAttachments(string messageID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("MESSAGE_ID", messageID); builder.AppendTenantCode(); string sql = string.Format("SELECT * FROM MSG.EMAIL_ATTACHMENTS WHERE {0} ORDER BY SORT_ID", builder.ToSqlString(TSqlBuilder.Instance)); EmailAttachmentCollection result = new EmailAttachmentCollection(); ORMapping.DataViewToCollection(result, DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0].DefaultView); return(result); }
/// <summary> /// 查询用户所属的角色 /// </summary> /// <param name="schemaTypes"></param> /// <param name="appCodeName"></param> /// <param name="userIDs"></param> /// <param name="includingDeleted"></param> /// <param name="timePoint"></param> /// <returns></returns> public SchemaObjectCollection QueryUserBelongToRoles(string[] schemaTypes, string appCodeName, string[] userIDs, bool includingDeleted, DateTime timePoint) { SchemaObjectCollection result = new SchemaObjectCollection(); InSqlClauseBuilder userBuilder = new InSqlClauseBuilder("UC.UserID"); userBuilder.AppendItem(userIDs); if (userBuilder.IsEmpty == false) { InSqlClauseBuilder schemaBuilder = new InSqlClauseBuilder("MA.MemberSchemaType"); schemaBuilder.AppendItem(schemaTypes); WhereSqlClauseBuilder appBuilder = new WhereSqlClauseBuilder(); if (appCodeName.IsNotEmpty()) { appBuilder.AppendItem("A.CodeName", appCodeName); } IConnectiveSqlClause extraBuilder = CreateStatusAndTimePointBuilder(includingDeleted, timePoint, "SC.", "A.", "MA.", "UC."); string resourcePath = "QueryUserBelongToRoles_Current.sql"; if (timePoint != DateTime.MinValue && includingDeleted == true) { resourcePath = "QueryUserBelongToRoles.sql"; } string sqlTemplate = ResourceHelper.LoadStringFromResource(Assembly.GetExecutingAssembly(), string.Concat("MCS.Library.SOA.DataObjects.Security.Adapters.Templates.", resourcePath)); ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(userBuilder, schemaBuilder, appBuilder, extraBuilder); string sql = string.Format(sqlTemplate, connectiveBuilder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0]; result.LoadFromDataView(table.DefaultView, (row, obj) => { obj.Tag = row["AppID"].ToString(); }); } return(result); }
//add czq 2013-06-17 internal PartlyCollection <ProcessInstance> LoadListByCreateUserID(Filter.WorkFlowFilter filter) { WhereSqlClauseBuilder where = filter.ConvertToWhereBuilder(); where.AppendItem("Isdeleted", 0); QueryCondition qc = new QueryCondition( filter.RowIndex, filter.PageSize, " * ", ORMapping.GetTableName(typeof(ProcessInstance)), " Createtime desc", where.ToSqlString(TSqlBuilder.Instance) ); PartlyCollection <ProcessInstance> result = GetPageSplitedCollection(qc); return(result); }
//我的流程分页查询 public PartlyCollection <ProcessInstance> LoadListByCreateUserID(int pageIndex, int pageSize, Dictionary <string, string> whereString) { string tableName = ORMapping.GetTableName(typeof(ProcessInstance)); #region Where语句 StringBuilder burWhere = new StringBuilder(); burWhere.Append("1=1 "); if (whereString.ContainsKey("UserID") && !string.IsNullOrEmpty(whereString["UserID"])) { burWhere.AppendFormat(" AND {1}.UserID='{0}'", whereString["UserID"], tableName); } if (whereString.ContainsKey("InstanceName") && !string.IsNullOrEmpty(whereString["InstanceName"]))//标题 { burWhere.AppendFormat(" AND {1}.InstanceName like '%{0}%'", WhereSqlClauseBuilder.EscapeLikeString(whereString["InstanceName"]), tableName); } if (whereString.ContainsKey("CreatedTime") && !string.IsNullOrEmpty(whereString["CreatedTime"]))//创建时间 { //创建时间开始 burWhere.AppendFormat(" AND {0}.CreateTime>= Convert(NVARCHAR(10),'{1}',120) ", tableName, whereString["CreatedTime"]); //创建时间结束 burWhere.AppendFormat(" AND {0}.CreateTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)", tableName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } if (whereString.ContainsKey("LastUpdatedTime") && !string.IsNullOrEmpty(whereString["LastUpdatedTime"]))//接收时间 { //最后更新时间开始 burWhere.AppendFormat(" AND {0}.LastUpdatedTime>= Convert(NVARCHAR(10),'{1}',120) ", tableName, whereString["LastUpdatedTime"]); //最后更新时间结束 burWhere.AppendFormat(" AND {0}.LastUpdatedTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)", tableName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } if (whereString.ContainsKey("ProcessCode") && !string.IsNullOrEmpty(whereString["ProcessCode"]))//流程类型 { burWhere.AppendFormat(" AND {1}.ProcessCode='{0}'", whereString["ProcessCode"], tableName); } if (whereString.ContainsKey("Status") && !string.IsNullOrEmpty(whereString["Status"]))//状态 { burWhere.AppendFormat(" AND {1}.Status='{0}'", whereString["Status"], tableName); } burWhere.AppendFormat(" AND {0}", NotDeleted); #endregion PartlyCollection <ProcessInstance> result = GetPageSplitedCollection(pageIndex, pageSize, burWhere.ToString()); return(result); }
/// <summary> /// 通过实体的CodeName加载实体字段对象集合 /// </summary> /// <param name="codeName">实体的CodeName</param> /// <returns>该CodeName对应的实体字段对象集合</returns> public List <DynamicEntityField> LoadByRefferanceCodeName(string codeName) { List <DynamicEntityField> entityFields = new List <DynamicEntityField>(); string.IsNullOrEmpty(codeName).TrueThrow("codeName不得为空"); InSqlClauseBuilder inbuilder = new InSqlClauseBuilder("ReferenceEntityCodeName"); inbuilder.AppendItem(codeName); WhereSqlClauseBuilder wherebuilder = new WhereSqlClauseBuilder(); wherebuilder.AppendItem("Status", 1); ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And); connectiveBuilder.Add(inbuilder); connectiveBuilder.Add(wherebuilder); var timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(); connectiveBuilder.Add(timePointBuilder); StringBuilder sql = new StringBuilder(); sql.AppendFormat(@"SELECT ID FROM {0} WHERE {1}", TableName, connectiveBuilder.ToSqlString(TSqlBuilder.Instance) ); DataSet fieldIDs = DbHelper.RunSqlReturnDS(sql.ToString(), this.GetConnectionName()); if (fieldIDs.Tables.Count != 0) { foreach (DataRow item in fieldIDs.Tables[0].Rows) { var id = item[0].ToString(); var dbResult = DESchemaObjectAdapter.Instance.Load(id) as DynamicEntityField; entityFields.Add(dbResult); } } return(entityFields); }
protected override void AfterInnerDelete(WfMatrixDefinition data, Dictionary <string, object> context) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("MATRIX_DEF_KEY", data.Key); builder.AppendTenantCode(typeof(WfMatrixDimensionDefinition)); string sql = string.Format("{0} {1}", DELETE_DIMENSION_SQL_CLAUSE, builder.ToSqlString(TSqlBuilder.Instance)); DbHelper.RunSql(sql, GetConnectionName()); CacheNotifyData notifyData = new CacheNotifyData(typeof(WfMatrixDefinitionCache), CalculateCacheKey(data.Key), CacheNotifyType.Invalid); UdpCacheNotifier.Instance.SendNotifyAsync(notifyData); MmfCacheNotifier.Instance.SendNotify(notifyData); }
public UserTask LoadSingleUserTaskByID(string taskID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("TASK_GUID", taskID); string sql = string.Format("SELECT TOP (1) * FROM WF.USER_TASK WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; UserTask userTask = new UserTask(); if (table.Rows.Count > 0) { ORMapping.DataRowToObject(table.Rows[0], userTask); } return(userTask); }