C# WhereSqlClauseBuilder.AppendTenantCode方法代码示例

        /// <summary>
        /// 增加ROLE_ID的查询条件
        /// </summary>
        /// <param name="roleID"></param>
        /// <returns></returns>
        public static string ToRoleIDCriteria(this string roleID)
        {
            roleID.CheckStringIsNullOrEmpty("roleID");

            WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();

            builder.AppendItem("ROLE_ID", roleID);
            builder.AppendTenantCode();

            return builder.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);
		}

        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>
        /// 根据角色ID判断哪些角色有了扩展属性定义
        /// </summary>
        /// <param name="roleIDs"></param>
        /// <returns></returns>
        public Dictionary<string, bool> AreExist(IEnumerable<string> roleIDs)
        {
            roleIDs.NullCheck("roleIDs");

            Dictionary<string, bool> result = new Dictionary<string, bool>();

            InSqlClauseBuilder builder = new InSqlClauseBuilder();

            foreach (string roleID in roleIDs)
            {
                if (result.ContainsKey(roleID) == false)
                {
                    result.Add(roleID, false);
                    builder.AppendItem(roleID);
                }
            }

            if (result.Count > 0)
            {
                WhereSqlClauseBuilder tenantBuilder = new WhereSqlClauseBuilder();

                tenantBuilder.AppendTenantCode();

                string tenantCriteria = string.Empty;

                if (tenantBuilder.IsEmpty == false)
                    tenantCriteria = " AND " + tenantBuilder.ToSqlString(TSqlBuilder.Instance);

                string sql = string.Format("SELECT ROLE_ID FROM WF.ROLE_PROPERTIES_DEFINITIONS WHERE ROLE_ID IN ({0}){1} GROUP BY ROLE_ID",
                    builder.ToSqlString(TSqlBuilder.Instance), tenantCriteria);

                DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];

                foreach (DataRow row in table.Rows)
                    result[row["ROLE_ID"].ToString()] = true;
            }

            return result;
        }

        /// <summary>
        /// 根据SQL获取category
        /// </summary>
        /// <param name="where"></param>
        /// <param name="orderby"></param>
        /// <param name="selector"></param>
        /// <returns></returns>
        public TaskCategoryCollection GetCategoriesByWhereCondition(string where, string orderby, List<string> selector)
        {
            string sql = string.Format("SELECT");

            if (selector != null && selector.Count > 0)
            {
                foreach (string sel in selector)
                {
                    sql += TSqlBuilder.Instance.CheckQuotationMark(sel, true);
                }
            }
            else
                sql += " * ";

            WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();

            builder.AppendItem("USER_ID", DeluxeIdentity.CurrentUser.ID);
            builder.AppendTenantCode(typeof(TaskCategory));

            sql += string.Format("FROM WF.USER_TASK_CATEGORY WHERE {0}",
                builder.ToSqlString(TSqlBuilder.Instance));

            if (where != string.Empty && where != null)
                sql += string.Format("AND ({0})", where);

            if (orderby != string.Empty && orderby != null)
                sql += string.Format("ORDER BY {0}", orderby);

            DataView dv = DbHelper.RunSqlReturnDS(sql).Tables[0].DefaultView;
            TaskCategoryCollection tcc = new TaskCategoryCollection();

            tcc.LoadFromDataView(dv);

            return tcc;
        }

        public EmailMessageCollection LoadCandidateMessages(int batchCount)
        {
            string topDesp = batchCount >= 0 ? string.Format(" TOP {0} ", batchCount) : string.Empty;

            WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();

            builder.AppendItem("1", 1);
            builder.AppendTenantCode();

            string sql = string.Format("SELECT {0}* FROM MSG.EMAIL_MESSAGES WITH (UPDLOCK READPAST) WHERE {1} ORDER BY SORT_ID",
                topDesp,
                builder.ToSqlString(TSqlBuilder.Instance));

            EmailMessageCollection result = new EmailMessageCollection();

            DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];

            foreach (DataRow row in table.Rows)
            {
                EmailMessage message = new EmailMessage();

                DataRowToEmailMessage(row, message);

                result.Add(message);
            }

            return result;
        }

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

        private void SelectExcutQuery()
        {
            if (string.IsNullOrEmpty(this.tb_AllKeys.Text) == false)
            {
                InSqlClauseBuilder inBuilder = new InSqlClauseBuilder();
                inBuilder.DataField = "PROCESS_KEY";
                inBuilder.AppendItem(this.tb_AllKeys.Text.Split(','));

                WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();

                whereBuilder.AppendTenantCode();

                this.BindGrid(new ConnectiveSqlClauseCollection(LogicOperatorDefine.And, whereBuilder, inBuilder));
            }
        }

        private void ExecuteQuery()
        {
            WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
            string appName = string.IsNullOrEmpty(txtApplicationName.Text) == true ? "全部" : txtApplicationName.Text;
            if (appName.IsNotEmpty())
            {
                if ("全部" == appName)
                {
                    if (!RolesDefineConfig.GetConfig().IsCurrentUserInRoles("ProcessAdmin"))
                    {
                        InSqlClauseBuilder inBuilder = new InSqlClauseBuilder();
                        inBuilder.AppendItem(AllApplicationNames());
                        builder.AppendItem("APPLICATION_NAME", inBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance), "", true);
                    }
                }
                else
                {
                    if ("其它" == appName)
                    {
                        builder.AppendItem("APPLICATION_NAME", " ");
                    }
                    else
                    {
                        builder.AppendItem("APPLICATION_NAME", appName);
                    }
                }
            }

            if (!string.IsNullOrEmpty(txtProgramName.Text))
                builder.AppendItem("PROGRAM_NAME", "%" + TSqlBuilder.Instance.CheckQuotationMark(txtProgramName.Text, false) + "%", "like");

            if (!string.IsNullOrEmpty(txtProcessKey.Text))
                builder.AppendItem("PROCESS_KEY", "%" + TSqlBuilder.Instance.CheckQuotationMark(txtProcessKey.Text, false) + "%", "like");

            if (!string.IsNullOrEmpty(txtProcessName.Text))
                builder.AppendItem("PROCESS_NAME", "%" + TSqlBuilder.Instance.CheckQuotationMark(txtProcessName.Text, false) + "%", "like");

            if (!string.IsNullOrEmpty(ddlEnabled.SelectedValue))
                builder.AppendItem("ENABLED", TSqlBuilder.Instance.CheckQuotationMark(ddlEnabled.SelectedValue, false));

            builder.AppendTenantCode();

            this.BindGrid(builder);
        }

        private static DataTable LoadAddresses(string messageID)
        {
            WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();

            builder.AppendItem("MESSAGE_ID", messageID);
            builder.AppendTenantCode();

            string sql = string.Format("SELECT * FROM MSG.EMAIL_ADDRESSES WHERE {0}",
                builder.ToSqlString(TSqlBuilder.Instance));

            return DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
        }

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

        private static EmailMessage LoadEmailMessage(string messageID, string tableName)
        {
            messageID.CheckStringIsNullOrEmpty("messageID");

            WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();

            builder.AppendItem("MESSAGE_ID", messageID);
            builder.AppendTenantCode();

            string sql = string.Format("SELECT * FROM {0} WHERE {1}",
                tableName,
                builder.ToSqlString(TSqlBuilder.Instance));

            DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];

            (table.Rows.Count > 0).FalseThrow("不能找到MESSAGE_ID为{0}的记录", messageID);

            EmailMessage message = new EmailMessage();

            DataRowToEmailMessage(table.Rows[0], message);

            return message;
        }

        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>
        /// 删除EmailMessage
        /// </summary>
        /// <param name="action"></param>
        public void Delete(Action<WhereSqlClauseBuilder> action)
        {
            action.NullCheck("action");

            WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();

            action(builder);
            builder.AppendTenantCode();

            StringBuilder strB = new StringBuilder();

            using (StringWriter writer = new StringWriter(strB))
            {
                WriteDeleteEmailAddressesSql(writer, builder);
                WriteDeleteEmailAttachmentsSql(writer, builder);
                writer.WriteLine("DELETE MSG.EMAIL_MESSAGES WHERE {0}",
                    builder.ToSqlString(TSqlBuilder.Instance));
            }

            DbHelper.RunSqlWithTransaction(strB.ToString(), GetConnectionName());
        }

        private static string GetUpdateSql(WfPersistQueue pq)
        {
            ORMappingItemCollection mappingInfo = ORMapping.GetMappingInfo(pq.GetType());

            UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(pq, mappingInfo, "UpdateTag");

            uBuilder.AppendItem("UPDATE_TAG", "WF.PROCESS_INSTANCES.UPDATE_TAG", "=", true);

            WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();

            wBuilder.AppendItem("PROCESS_ID", pq.ProcessID);
            wBuilder.AppendTenantCode();

            string sql = string.Format("UPDATE {0} SET {1} FROM {0} INNER JOIN WF.PROCESS_INSTANCES ON INSTANCE_ID = PROCESS_ID WHERE {2}",
                mappingInfo.TableName,
                uBuilder.ToSqlString(TSqlBuilder.Instance),
                wBuilder.ToSqlString(TSqlBuilder.Instance));

            return sql;
        }