C# WhereSqlClauseBuilder.AppendItem方法代码示例

		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);
		}

		/// <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;
			}
		}

		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;
 }

		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());
 }