OA系统

开发平台：
C#

Query.cs：源码内容
							﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using OThinker.H3.WorkItem;
using OThinker.H3.WorkflowTemplate;
using OThinker.H3.Instance;
namespace OThinker.H3.Portal
{
    /// <summary>
    /// 原来查询数据库
    /// </summary>
    public class Query
    {
        public static System.Data.DataTable QueryWorkflow(string[] Units)
        {
            string sql =
                "SELECT " +
                    OThinker.H3.WorkflowTemplate.WorkflowClause.TableName + "." + OThinker.H3.WorkflowTemplate.WorkflowClause.PropertyName_WorkflowPackage + " AS " + OThinker.H3.WorkflowTemplate.WorkflowTemplate.PropertyName_WorkflowPackageName + ", " + 
                    OThinker.H3.WorkflowTemplate.WorkflowClause.TableName + "." + OThinker.H3.WorkflowTemplate.WorkflowClause.PropertyName_WorkflowName + " AS " +OThinker.H3.WorkflowTemplate.WorkflowTemplate.PropertyName_WorkflowName + ", " + 
                    OThinker.H3.WorkflowTemplate.WorkflowClause.TableName + "." + OThinker.H3.WorkflowTemplate.WorkflowClause.PropertyName_DefaultVersion + " AS " + OThinker.H3.WorkflowTemplate.WorkflowTemplate.PropertyName_WorkflowVersion + " " +
                "FROM " +
                    OThinker.H3.WorkflowTemplate.WorkflowClause.TableName + " INNER JOIN " + OThinker.H3.Acl.WorkflowAcl.TableName + " " +
                "ON " +
                    OThinker.H3.WorkflowTemplate.WorkflowClause.TableName + "." + OThinker.H3.WorkflowTemplate.WorkflowClause.PropertyName_WorkflowPackage + "=" + OThinker.H3.Acl.WorkflowAcl.TableName + "." + OThinker.H3.Acl.WorkflowAcl.PropertyName_WorkflowPackage + " AND " +
                    OThinker.H3.WorkflowTemplate.WorkflowClause.TableName + "." + OThinker.H3.WorkflowTemplate.WorkflowClause.PropertyName_WorkflowName + "=" + OThinker.H3.Acl.WorkflowAcl.TableName + "." + OThinker.H3.Acl.WorkflowAcl.PropertyName_WorkflowName + " AND " +
                    OThinker.H3.WorkflowTemplate.WorkflowClause.TableName + "." + OThinker.H3.WorkflowTemplate.WorkflowClause.PropertyName_StateFlag + "&" + OThinker.H3.WorkflowTemplate.WorkflowState.ORIGINATOR_VISIBLE_MASK + ">0" + " AND " +
                    "(" + OThinker.H3.Acl.WorkflowAcl.PropertyName_Administrator + "=1" + " OR " + OThinker.H3.Acl.WorkflowAcl.PropertyName_CreateInstance + "=1)" + " AND " +
                    GroupIn(OThinker.H3.Acl.WorkflowAcl.PropertyName_UserID, Units);
            return QueryTable(sql);
        }
        public static System.Data.DataTable QueryInstanceByData(
            string WorkflowPackage,
            string WorkflowName,
            int WorkflowVersion,
            string DataItem, 
            string Comparison, 
            string Originator)
        {
            OThinker.H3.WorkflowTemplate.WorkflowTemplate template = OThinker.H3.Server.Engine.WorkflowManager.GetWorkflow(WorkflowPackage, WorkflowName, WorkflowVersion);
            if (template == null)
            {
                return null;
            }
            
            if (template.GetDataItem(DataItem) == null)
            {
                return null;
            }
            string dataSql = 
                "SELECT " + OThinker.H3.Data.InternalData.PropertyName_InstanceId + " " + 
                "FROM [" + template.DataTableName + "] " + 
                "WHERE [" + DataItem + "]" + Comparison;
            string[] conditions = new string[]{
                ((Originator == null)?null: (Instance.InstanceContext.PropertyName_Originator + "='" + Originator + "'")), 
                Instance.InstanceContext.PropertyName_ObjectID + " IN (" + dataSql + ")"};
            string sql = GroupSql(InstanceContext.TableName, conditions, InstanceContext.PropertyName_CreatedTime, false);
            return QueryTable(sql);
        }
        public static System.Data.DataTable QueryToken(string InstanceId)
        {
            if (InstanceId == null || InstanceId == "")
            {
                return null;
            }
            else
            {
                string[] conditions = new string[] { OThinker.H3.Instance.Token.PropertyName_InstanceId + "='" + InstanceId + "'" };
                string sql = GroupSql(OThinker.H3.Instance.Token.TableName, conditions, Token.PropertyName_TokenId, true);
                return QueryTable(sql);
            }
        }
        public static System.Data.DataTable QueryHandledInstance(
            string WorkflowPackage,
            string WorkflowName,
            int WorkflowVersion,
            OThinker.H3.Instance.InstanceState InstanceState,
            System.DateTime CreatedTime_From,
            System.DateTime CreatedTime_To,
            string Participant)
        {
            if (Participant == null || Participant == "")
            {
                return null;
            }
            // 经办的条件
            string handledCondition = 
                "[" + InstanceContext.PropertyName_ObjectID + "] IN (" +
                    "SELECT DISTINCT [" + WorkItem.WorkItem.PropertyName_InstanceId + "] FROM [" + WorkItem.WorkItem.TableName + "] WHERE " + 
                        "[" + WorkItem.WorkItem.PropertyName_Participant + "]='" + Participant + "'" + " AND " + 
                        "[" + WorkItem.WorkItem.PropertyName_State + "]=" + (int)WorkItem.WorkItemState.Finished + ")";
            // 非发起的条件
            string notOriginateCondition = InstanceContext.PropertyName_Originator + "!='" + Participant + "'";
            string[] conditions = new string[]{
                handledCondition, 
                notOriginateCondition, 
                ((WorkflowPackage == null)?null:(InstanceContext.PropertyName_WorkflowPackage + "='" + WorkflowPackage + "'")), 
                ((WorkflowName == null)? null: (InstanceContext.PropertyName_WorkflowName + "='" + WorkflowName + "'")), 
                ((WorkflowVersion == WorkflowTemplate.WorkflowTemplate.NullWorkflowVersion || WorkflowVersion == OThinker.H3.WorkflowTemplate.WorkflowTemplate.AllWorkflowVersion)? null: (WorkItem.WorkItem.PropertyName_WorkflowVersion + "='" + WorkflowVersion + "'")), 
                BuildState(InstanceState), 
                ((CreatedTime_From == System.DateTime.MinValue)?null: (InstanceContext.PropertyName_CreatedTime + ">'" + FormatFrom(CreatedTime_From) + "'")), 
                ((CreatedTime_To == System.DateTime.MaxValue)?null:(InstanceContext.PropertyName_CreatedTime + "<='" + FormatTo(CreatedTime_To) + "'")), 
            };
            string sql = GroupSql(InstanceContext.TableName, conditions, InstanceContext.PropertyName_CreatedTime, false);
            return QueryTable(sql);
        }
        /// <summary>
        /// 如果输入为Initiated、Starting:、Running、Finishing或者Complete，那么安装这些状态进行查找
        /// 如果输入为Exceptional，则检索Exceptional == true
        /// 如果输入为Suspended，则检索Suspended == true
        /// 如果输入为Unfinished，则检索State != Complete
        /// 如果输入为Unspecified，则无检索条件
        /// </summary>
        /// <param name="State"></param>
        /// <returns></returns>
        private static string BuildState(InstanceState InstanceState)
        {
            switch (InstanceState)
            {
                case OThinker.H3.Instance.InstanceState.Initiated:
                case OThinker.H3.Instance.InstanceState.Starting:
                case OThinker.H3.Instance.InstanceState.Running:
                case OThinker.H3.Instance.InstanceState.Finishing:
                case OThinker.H3.Instance.InstanceState.Complete:
                    return InstanceContext.PropertyName_State + "=" + (int)InstanceState;
                case OThinker.H3.Instance.InstanceState.Exceptional:
                    // 如果输入为Exceptional状态，那么检索Exceptional属性是否为true
                    return InstanceContext.PropertyName_Exceptional + "=" + 1;
                case OThinker.H3.Instance.InstanceState.Suspended:
                    // 如果输入为Suspended状态，那么检索Suspended属性是否为true
                    return InstanceContext.PropertyName_Suspended + "=" + 1;
                case OThinker.H3.Instance.InstanceState.Unfinished:
                    return InstanceContext.PropertyName_State + "!=" + (int)Instance.InstanceState.Complete;
                case OThinker.H3.Instance.InstanceState.Unspecified:
                    return null;
                default:
                    throw new NotImplementedException();
            }
        }
        public static System.Data.DataTable QueryInstance(
            string[] Instances,
            string[] Originators,
            string OU,
            string ParentInstanceID,
            OThinker.H3.Instance.InstanceState InstanceState,
            string WorkflowPackage,
            string WorkflowName,
            int WorkflowVersion,
            System.DateTime CreatedTime_From,
            System.DateTime CreatedTime_To,
            string InstanceName,
            PriorityType Priority,
            OThinker.Data.BoolMatchValue Approval,
            OThinker.Data.BoolMatchValue Elapsed)
        {
            string instanceCondition = GroupIn(InstanceContext.PropertyName_ObjectID, Instances);
            string originatorCondition = GroupIn(InstanceContext.PropertyName_Originator, Originators);
            string stateCondition = BuildState(InstanceState);
            string[] conditions = new string[]{
                instanceCondition, 
                originatorCondition, 
                ((OU == null)?null: (InstanceContext.PropertyName_OrgUnit + "='" + OU + "'")), 
                ((ParentInstanceID == null)?null:(InstanceContext.PropertyName_ParentInstanceID + "='" + ParentInstanceID + "'")), 
                stateCondition, 
                ((WorkflowPackage == null)?null:(InstanceContext.PropertyName_WorkflowPackage + "='" + WorkflowPackage + "'")), 
                ((WorkflowName == null)? null: (InstanceContext.PropertyName_WorkflowName + "='" + WorkflowName + "'")), 
                ((WorkflowVersion == WorkflowTemplate.WorkflowTemplate.NullWorkflowVersion || WorkflowVersion == OThinker.H3.WorkflowTemplate.WorkflowTemplate.AllWorkflowVersion)? null: (WorkItem.WorkItem.PropertyName_WorkflowVersion + "='" + WorkflowVersion + "'")),
                ((CreatedTime_From == System.DateTime.MinValue)?null: (InstanceContext.PropertyName_CreatedTime + ">'" + FormatFrom(CreatedTime_From) + "'")), 
                ((CreatedTime_To == System.DateTime.MaxValue)?null:(InstanceContext.PropertyName_CreatedTime + "<='" + FormatTo(CreatedTime_To) + "'")), 
                ((InstanceName==null)?null:(InstanceContext.PropertyName_InstanceName  + " LIKE '%" + InstanceName + "%'")), 
                ((Priority == PriorityType.Unspecified)?null: (InstanceContext.PropertyName_Priority + "=" + (int)Priority)), 
                ((Approval == OThinker.Data.BoolMatchValue.Unspecified)? null:InstanceContext.PropertyName_Approval + "=" +(int)Approval), 
                ((Elapsed == OThinker.Data.BoolMatchValue.Unspecified)? null:InstanceContext.PropertyName_PlanFinishTime + (Elapsed == OThinker.Data.BoolMatchValue.True? "<" : ">=") + "'" +System.DateTime.Now + "'")
                };
            string sql = GroupSql(InstanceContext.TableName, conditions, InstanceContext.PropertyName_CreatedTime, false);
            return QueryTable(sql);
        }
        public static System.Data.DataTable QueryInstanceByEstimatedElapsed(
            string Originator,
            OThinker.Data.BoolMatchValue EstimatedElapsed)
        {
            string stateCondition = BuildState(InstanceState.Unfinished);
            string[] conditions = new string[]{
                stateCondition, 
                ((Originator == null)?null:(InstanceContext.PropertyName_Originator + "='" + Originator + "'")), 
                ((EstimatedElapsed == OThinker.Data.BoolMatchValue.Unspecified)? null:InstanceContext.PropertyName_PlanFinishTime + (EstimatedElapsed == OThinker.Data.BoolMatchValue.True? "<" : ">=") + "[" + InstanceContext.PropertyName_EstimatedFinishTime + "]")};
            string sql = GroupSql(InstanceContext.TableName, conditions, InstanceContext.PropertyName_EstimatedFinishTime, false);
            return QueryTable(sql);
        }
        public static System.Data.DataTable QueryInstanceByRecurrences(
            string Originator,
            int LeastRecurrences)
        {
            string stateCondition = BuildState(InstanceState.Unfinished);
            string[] conditions = new string[]{
                stateCondition, 
                ((Originator == null)?null:(InstanceContext.PropertyName_Originator + "='" + Originator + "'")), 
                ((LeastRecurrences <= 0)?null: InstanceContext.PropertyName_Recurrences + ">=" + LeastRecurrences)};
            string sql = GroupSql(InstanceContext.TableName, conditions, InstanceContext.PropertyName_Recurrences, false);
            return QueryTable(sql);
        }
        public static System.Data.DataTable QueryWorkItem(
            string WorkflowPackage,
            string WorkflowName,
            int WorkflowVersion,
            string ActivityName,
            string[] Instances,
            string[] Participants,
            System.DateTime From,
            System.DateTime To,
            OThinker.H3.WorkItem.WorkItemState State,
            OThinker.H3.Instance.PriorityType Priority,
            string InstanceName,
            string Receiptor,
            OThinker.Data.BoolMatchValue Approval, 
            OThinker.Data.BoolMatchValue Elapsed)
        {
            string instanceCondition = GroupIn(WorkItem.WorkItem.PropertyName_InstanceId, Instances);
            string participantCondition = GroupIn(WorkItem.WorkItem.PropertyName_Participant, Participants);
            string stateCondition = null;
            switch (State)
            {
                case WorkItemState.Waiting:
                case WorkItemState.Working:
                case WorkItemState.Finished:
                case WorkItemState.Canceled:
                    stateCondition = WorkItem.WorkItem.PropertyName_State + "=" + (int)State;
                    break;
                case WorkItemState.Unfinished:
                    stateCondition = WorkItem.WorkItem.PropertyName_State + "!=" + (int)WorkItemState.Finished + " AND " + WorkItem.WorkItem.PropertyName_State + "!=" + (int)WorkItemState.Canceled;
                    break;
                case WorkItemState.NotCanceled:
                    stateCondition = WorkItem.WorkItem.PropertyName_State + "!=" + (int)WorkItemState.Canceled;
                    break;
                case WorkItemState.Unspecified:
                    break;
                default:
                    throw new NotImplementedException();
            }
            string[] conditions =
                new string[]{
                    ((WorkflowPackage == null)? null: (WorkItem.WorkItem.PropertyName_WorkflowPackage + "='" + WorkflowPackage + "'")), 
                    ((WorkflowName == null)? null: (WorkItem.WorkItem.PropertyName_WorkflowName + "='" + WorkflowName + "'")), 
                    ((WorkflowVersion == WorkflowTemplate.WorkflowTemplate.NullWorkflowVersion|| WorkflowVersion == OThinker.H3.WorkflowTemplate.WorkflowTemplate.AllWorkflowVersion)? null: (WorkItem.WorkItem.PropertyName_WorkflowVersion + "='" + WorkflowVersion + "'")), 
                    ((ActivityName == null)? null: (OThinker.H3.WorkItem.WorkItem.PropertyName_ActivityName + "='" + ActivityName + "'")), 
                    instanceCondition, 
                    participantCondition, 
                    ((From == System.DateTime.MinValue)?null: (WorkItem.WorkItem.PropertyName_ReceiveTime + ">'" + FormatFrom(From) + "'")), 
                    ((To == System.DateTime.MaxValue)?null: (WorkItem.WorkItem.PropertyName_ReceiveTime + "<='" + FormatTo(To) + "'")), 
                    stateCondition, 
                    ((Priority == OThinker.H3.Instance.PriorityType.Unspecified)?null: OThinker.H3.WorkItem.WorkItem.PropertyName_Priority + "=" + (int)Priority), 
                    ((InstanceName == null)? null: (WorkItem.WorkItem.PropertyName_InstanceName + " LIKE '%" + InstanceName + "%'")), 
                    ((Receiptor == null)?null: WorkItem.WorkItem.PropertyName_Receiptor + "='" + Receiptor + "'"),
                    ((Approval == OThinker.Data.BoolMatchValue.Unspecified)? null:WorkItem.WorkItem.PropertyName_Approval + "=" +(int)Approval), 
                    ((Elapsed == OThinker.Data.BoolMatchValue.Unspecified)? null:WorkItem.WorkItem.PropertyName_PlanFinishTime + (Elapsed == OThinker.Data.BoolMatchValue.True? "<" : ">=") + "'" +System.DateTime.Now + "'")};
            string sql = GroupSql(WorkItem.WorkItem.TableName, conditions, WorkItem.WorkItem.PropertyName_ReceiveTime, false);
            return QueryTable(sql);
        }
        /// <summary>
        /// 将搜索条件组合成ColumnName IN ('...', '...', ... )这种模式
        /// </summary>
        /// <param name="ColumnName"></param>
        /// <param name="Values"></param>
        /// <returns>如果Values为null，那么返回null，如果Length为0，则返回 IN ()，如果为1，则返回ColumnName=...，否则返回ColumnName IN ('...', '...', ...)</returns>
        private static string GroupIn(string ColumnName, string[] Values)
        {
            if (Values == null)
            {
                return null;
            }
            else if (Values.Length == 0)
            {
                return "1=2";
            }
            else if (Values.Length == 1)
            {
                if (Values[0] == null)
                {
                    return ColumnName + " is null";
                }
                else
                {
                    return ColumnName + "='" + Values[0] + "'";
                }
            }
            else
            {
                System.Text.StringBuilder builder = new System.Text.StringBuilder();
                foreach (string v in Values)
                {
                    if (builder.Length != 0)
                    {
                        builder.Append(", ");
                    }
                    if (v == null)
                    {
                        builder.Append("null");
                    }
                    else
                    {
                        builder.Append("'" + v + "'");
                    }
                }
                builder.Insert(0, ColumnName + " IN (");
                builder.Append(")");
                return builder.ToString();
            }
        }
        private static string GroupSql(string TableName, string[] Conditions, string OrderBy, bool Ascending)
        {
            return "SELECT * FROM " + TableName + " " + GroupConditions(Conditions) + (OrderBy == null ? null : " ORDER BY " + OrderBy + (Ascending ? null : " DESC"));
        }
        private static string GroupConditions(string[] Conditions)
        {
            if (Conditions == null || Conditions.Length == 0)
            {
                return null;
            }
            else
            {
                System.Text.StringBuilder builder = new System.Text.StringBuilder();
                foreach (string condition in Conditions)
                {
                    if (condition != null && condition != "")
                    {
                        if (builder.Length != 0)
                        {
                            builder.Append(" AND ");
                        }
                        builder.Append(condition);
                    }
                }
                if (builder.Length != 0)
                {
                    builder.Insert(0, "WHERE ");
                }
                return builder.ToString();
            }
        }
        private static System.Data.DataTable QueryTable(string Sql)
        {
            OThinker.Data.Database.CommandFactory factory = new OThinker.Data.Database.CommandFactory(OThinker.H3.Configs.Config.Current.DataBase.DBType, OThinker.H3.Configs.Config.Current.DataBase.DBConnString);
            OThinker.Data.Database.ICommand command = factory.CreateCommand();
            System.Data.DataTable table = command.ExecuteDataTable(Sql);
            command.Commit();
            return table;
        }
        private static System.DateTime FormatFrom(System.DateTime From)
        {
            // 检查参数的合法性，如果不合法则重新设置参数
            System.DateTime from;
            // 将Sql的DateTime里面的最小值转换成System.DateTime类型
            System.DateTime sqlMin = System.DateTime.Parse(System.Data.SqlTypes.SqlDateTime.MinValue.ToString());
            if (sqlMin > From)
            {
                from = sqlMin;
            }
            else
            {
                from = From;
            }
            return from;
        }
        private static System.DateTime FormatTo(System.DateTime To)
        {
            // 检查参数的合法性，如果不合法则重新设置参数
            System.DateTime to;
            // 将Sql的DateTime里面的最大值转换成System.DateTime类型
            System.DateTime sqlMax = System.DateTime.Parse(System.Data.SqlTypes.SqlDateTime.MaxValue.ToString());
            if (sqlMax < To)
            {
                to = sqlMax;
            }
            else
            {
                to = To;
            }
            return to;
        }
    }
}

						