/// <summary>
/// 根据orgID获取用户信息
/// </summary>
/// <param name="orgid">wd_org.orgID</param>
/// <returns></returns>
public PartlyCollection <WD_User> GetSearchOrgUser(string orgid)
{
PartlyCollection <WD_User> result = new PartlyCollection <WD_User>();
string sqlCommand = string.Empty;
if (orgid != null)
{
sqlCommand = string.Format(@"
SELECT b.employeeCode as Wd_UserID,b.username as LoginName,b.employeeName as Name,
b.orgName as OrgName,b.orgID as Wd_OrgID,b.jobID as JobID,b.joinUnitDate as StartTime, NULL as EndTime,
b.jobName as JobTitle
FROM dbo.wd_user_org_rel a
INNER JOIN dbo.Wd_User b ON a.username = b.username
WHERE b.unitID ='{0}' AND a.username IS NOT NULL and b.employeeStatus='2'", SqlTextHelper.SafeQuote(orgid));
DataSet ds = DbHelper.RunSqlReturnDS(sqlCommand, ConnectionName);
if (ds != null)
{
DataTable table = ds.Tables[0];
if (table != null && table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
WD_User view = new WD_User();
ORMapping.DataRowToObject <WD_User>(row, view);
result.Add(view);
}
}
}
}
return(result);
}
/// <summary>
/// 根据条件查询草稿箱
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="dt">查询条件</param>
/// <param name="recordCount">记录条数</param>
/// <returns></returns>
public PartlyCollection <BBizProcess> GetWorkflowDraftByRelatedUserID(WorkFlowFilter filter)
{
filter.ExcludeProcessCode = GetExcludeProcessCode();
PartlyCollection <BBizProcess> processInstanceList = VBizProcessDraftAdapter.Instance.LoadList(filter);
return(processInstanceList);
}
/// <summary>
/// 根据当前的Org信息,获取子节点
/// </summary>
/// <param name="orgs">如果参数为null,则返回顶级列表</param>
/// <returns></returns>
public PartlyCollection <WdOrg> GetChildOrg(WdOrg orgs = null)
{
PartlyCollection <WdOrg> result = new PartlyCollection <WdOrg>();
string sqlCommand = string.Empty;
if (orgs == null)
{
sqlCommand = "SELECT parentUnitID ParentID,OrgID, OrgName,ShortName,[order] OrderID, FullPath FROM dbo.wd_org WHERE isRoot = 1 AND parentStatus = 1 ORDER BY [order]";
}
else
{
sqlCommand = string.Format("SELECT parentUnitID ParentID,OrgID, OrgName,ShortName,[order] OrderID FROM dbo.wd_org WHERE parentUnitID = {0} AND parentStatus = 1 ORDER BY [order]", orgs.OrgID);;
}
DataSet ds = DbHelper.RunSqlReturnDS(sqlCommand, ConnectionName);
if (ds != null)
{
DataTable table = ds.Tables[0];
if (table != null && table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
WdOrg view = new WdOrg();
ORMapping.DataRowToObject <WdOrg>(row, view);
result.Add(view);
}
}
}
return(result);
}
/// <summary>
/// 获取用户信息
/// </summary>
/// <param name="keyword">用户名称</param>
/// <param name="deptName">部门名称</param>
/// <param name="jobName">岗位名称</param>
/// <returns></returns>
public PartlyCollection <WD_User> GetSearchOrgUsers(string keyword, string deptName, string jobName)
{
PartlyCollection <WD_User> result = new PartlyCollection <WD_User>();
string sqlCommand = string.Empty;
{
sqlCommand = string.Format(@"SELECT DISTINCT TOP(100) b.employeeCode as Wd_UserID,b.username as LoginName,b.employeeName as Name,
b.orgName as OrgName,b.orgID as Wd_OrgID,b.jobID as JobID,b.joinUnitDate as StartTime, NULL as EndTime,
b.jobName as JobTitle
FROM dbo.Wd_User b
WHERE (b.username LIKE '%{0}%' or b.employeeName LIKE '%{0}%')
and b.unitName LIKE '%{1}%'
and b.jobName LIKE '%{2}%' and b.employeeStatus='2'",
string.IsNullOrEmpty(keyword) == false ? SqlTextHelper.SafeQuote(keyword) : keyword,
string.IsNullOrEmpty(deptName) == false ? SqlTextHelper.SafeQuote(deptName) : deptName,
string.IsNullOrEmpty(jobName) == false ? SqlTextHelper.SafeQuote(jobName) : jobName);
DataSet ds = DbHelper.RunSqlReturnDS(sqlCommand, ConnectionName);
if (ds != null)
{
DataTable table = ds.Tables[0];
if (table != null && table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
WD_User view = new WD_User();
ORMapping.DataRowToObject <WD_User>(row, view);
result.Add(view);
}
}
}
}
return(result);
}
/// <summary>
/// 根据orgID获取用户信息
/// </summary>
/// <param name="orgid">wd_org.orgID</param>
/// <returns></returns>
public PartlyCollection <WD_User> GetSearchOrgUser(string orgid)
{
PartlyCollection <WD_User> list = null;
list = WdOrgAdapter.Instance.GetSearchOrgUser(orgid);
return(list);
}
/// <summary>
/// 根据条件获取流程列表（查询流程）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="whereString">查询条件</param>
/// <param name="recordCount"></param>
/// <returns></returns>
public PartlyCollection <VAdminPInstanceAndStakeHolder> GetWorkflowInstanceListForAdmin(WorkFlowFilter filter)
{
filter.ExcludeProcessCode = GetExcludeProcessCode();
PartlyCollection <VAdminPInstanceAndStakeHolder> processInstanceList = VAdminPinstaneAndStakeHolderAdapter.Instance.LoadList(filter);
return(processInstanceList);
}
/// <summary>
/// 获取用户信息
/// </summary>
/// <param name="keyword">用户名称</param>
/// <param name="deptName">部门名称</param>
/// <param name="jobName">岗位名称</param>
/// <returns></returns>
public PartlyCollection <WD_User> GetSearchOrgUsers(string keyword, string deptName, string jobName)
{
PartlyCollection <WD_User> list = null;
list = WdOrgAdapter.Instance.GetSearchOrgUsers(keyword, deptName, jobName);
return(list);
}
/// <summary>
/// 根据条件查询我的已办
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="dt">查询条件</param>
/// <returns></returns>
public PartlyCollection <VPInstanceAndApprovalLog> GetWorkflowInstanceListByRelatedUserID(WorkFlowFilter filter)
{
// 排除一些既定的流程不显示
filter.ExcludeProcessCode = GetExcludeProcessCode();
PartlyCollection <VPInstanceAndApprovalLog> VPInstanceAndApprovalLogList = VPInstanceAndApprovalLogAdapter.Instance.LoadList(filter);
return(VPInstanceAndApprovalLogList);
}
public static PartlyCollection <LogEntity> GetLogsListII(PagenationDataFilter filter, string connectionName)
{
WhereSqlClauseBuilder where = filter.ConvertToWhereBuilder();
string sqlFormat = @"With tmplLog as(
SELECT row_number() over (order by CreateTime desc) AS ROW_INDEX, * FROM
(
SELECT * ,(Title + Source+ CONVERT( VARCHAR(MAX),[Message])) as KeyWord
FROM [APPLICATION_LOG]
) AS T
WHERE 1=1 AND ({0})
)
SELECT * FROM tmplLog
WHERE
ROW_INDEX BETWEEN {1} AND {2} AND ({0});
SELECT count (*) AS TOTALCOUNT FROM ( SELECT *,(Title + Source+ CONVERT( VARCHAR(MAX),[Message])) as KeyWord
from [APPLICATION_LOG]
)T
WHERE 1=1 AND ({0})";
int rowIndex = filter.RowIndex;
int pageSize = filter.PageSize;
string whereStr = where.ToSqlString(TSqlBuilder.Instance);
string sql = string.Format(sqlFormat,
string.IsNullOrEmpty(whereStr) ? "1=1" : whereStr,
rowIndex,
rowIndex + pageSize);
DataSet ds = DbHelper.RunSqlReturnDS(sql, connectionName);
List <LogEntity> list = new List <LogEntity>();
int count = 0;
foreach (DataRow row in ds.Tables[0].Rows)
{
string message = DataConverter.ChangeType <object, string>(row["Message"]);
LogEntity item = new LogEntity(message);
//item.ActivityID = DataConverter.ChangeType<object, Guid>(row["EventID"], typeof(Guid));
item.EventID = DataConverter.ChangeType <object, int>(row["EventID"]);
item.LogEventType = DataConverter.ChangeType <object, TraceEventType>(row["TraceEventType"]);
item.MachineName = DataConverter.ChangeType <object, string>(row["MachineName"]);
item.Priority = DataConverter.ChangeType <object, LogPriority>(row["LogPriority"]);
item.Source = DataConverter.ChangeType <object, string>(row["Source"]);
item.StackTrace = DataConverter.ChangeType <object, string>(row["StackTrace"]);
item.TimeStamp = DataConverter.ChangeType <object, DateTime>(row["CreateTime"]);
item.Title = DataConverter.ChangeType <object, string>(row["Title"]);
item.CreatorName = DataConverter.ChangeType <object, string>(row["CreatorName"]);
list.Add(item);
}
count = DataConverter.ChangeType <object, int>(ds.Tables[1].Rows[0][0]);
PartlyCollection <LogEntity> result = PartlyCollection <LogEntity> .Create(list, count);
return(result);
}
/// <summary>
/// 根据查询条件获取流程列表（我的流程）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="whereString">查询条件</param>
/// <param name="recordCount"></param>
/// <returns></returns>
public PartlyCollection <WorkflowInstance> GetWorkflowInstanceListByCreateUserID(WorkFlowFilter filter)
{
filter.ExcludeProcessCode = GetExcludeProcessCode();
PartlyCollection <ProcessInstance> processInstanceList = ProcessInstanceAdapter.Instance.LoadListByCreateUserID(filter);
PartlyCollection <WorkflowInstance> result = new PartlyCollection <WorkflowInstance>();
foreach (ProcessInstance processInstance in processInstanceList)
{
result.Add(new WorkflowInstance(processInstance));
}
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);
}
public PartlyCollection <BPrivilege> GetResearchResult(PrivilegeFilter filter)
{
WhereSqlClauseBuilder where = filter.ConvertToWhereBuilder();
where.AppendItem("Isdeleted", 0);
QueryCondition qc = new QueryCondition(
filter.RowIndex,
filter.PageSize,
" * ",
ORMapping.GetTableName <BPrivilege>(),
" Createtime ",
where.ToSqlString(TSqlBuilder.Instance)
);
PartlyCollection <BPrivilege> result = GetPageSplitedCollection(qc);
return(result);
}
/// <summary>
/// 筛选角色信息
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
internal PartlyCollection <BRoleinfo> GetRoleList(RoleFilter filter)
{
WhereSqlClauseBuilder where = filter.ConvertToWhereBuilder();
where.AppendItem("isdeleted", 0);
QueryCondition qc = new QueryCondition(
filter.RowIndex,
filter.PageSize,
" * ",
ORMapping.GetTableName <BRoleinfo>(),
" Createtime ",
where.ToSqlString(TSqlBuilder.Instance)
);
PartlyCollection <BRoleinfo> result = GetPageSplitedCollection(qc);
return(result);
}
public List <Employee> GetUserList(UserFilter filter, out int itemCount)
{
WhereSqlClauseBuilder where = filter.ConvertToWhereBuilder();
where.AppendItem("employeeStatus", 3, "<>");
QueryCondition qc = new QueryCondition(
filter.RowIndex,
filter.PageSize,
" * ",
ORMapping.GetTableName <Employee>(),
" orgName asc,username asc ",
where.ToSqlString(TSqlBuilder.Instance)
);
PartlyCollection <Employee> tuple = GetPageSplitedCollection(qc);
itemCount = tuple.TotalCount;
return(tuple.SubCollection);
}
/// <summary>
/// 按where 条件 且返回分页
/// </summary>
/// <param name="where"></param>
/// <param name="rowIndex">行序号， 基于0</param>
/// <param name="pageSize">分页大小</param>
/// <param name="sortField">排序字段</param>
/// <returns></returns>
public PartlyCollection <T> GetList(WhereSqlClauseBuilder where, int rowIndex, int pageSize, string sortField = "ID")
{
if (where == null)
{
throw new ArgumentNullException("where");
}
if (string.IsNullOrEmpty(sortField))
{
sortField = "ID";
}
QueryCondition qc = new QueryCondition(
rowIndex,
pageSize,
" * ",
ORMapping.GetTableName(typeof(T)),
SqlTextHelper.SafeQuote(sortField),
where.ToSqlString(TSqlBuilder.Instance)
);
PartlyCollection <T> result = GetPageSplitedCollection(qc);
return(result);
}
internal PartlyCollection <BBizProcess> LoadList(WorkFlowFilter filter)
{
StringBuilder strSql = new StringBuilder();
WhereSqlClauseBuilder where = filter.ConvertToWhereBuilder();
where.AppendItem("Isdeleted", 0);
where.AppendItem("ProcessType", DBNull.Value);
//string sql =new VTodoWorkAndUser().TodoViewSql;
QueryCondition qc = new QueryCondition(
filter.RowIndex,
filter.PageSize,
"*",
ORMapping.GetTableName(typeof(BBizProcess)),
" Createtime desc",
where.ToSqlString(TSqlBuilder.Instance)
);
PartlyCollection <VBizProcess> tuple = GetPageSplitedCollection(qc);
PartlyCollection <BBizProcess> result = new PartlyCollection <BBizProcess>();
foreach (VBizProcess vBizProcess in tuple)
{
result.Add(new BBizProcess
{
TaskID = vBizProcess.TaskID,
ProcessType = vBizProcess.ProcessType,
Title = vBizProcess.Title,
ParentID = vBizProcess.ParentID,
Status = vBizProcess.Status,
IsCurrentVersion = vBizProcess.IsCurrentVersion,
AlarmAction = vBizProcess.AlarmAction,
AlarmPlanSplitDate = vBizProcess.AlarmPlanSplitDate
});
}
return(result);
}
public PartlyCollection <WdOrg> GetChildOrg(WdOrg orgs = null)
{
PartlyCollection <WdOrg> result = WdOrgAdapter.Instance.GetChildOrg(orgs);
return(result);
}
//流程分页查询
public PartlyCollection <ProcessInstance> LoadList(int pageIndex, int pageSize, Dictionary <string, string> whereString)
{
StringBuilder strSql = new StringBuilder();
string ProcessInstanceTableName = ORMapping.GetTableName(typeof(ProcessInstance));
string StakeHolderTableName = ORMapping.GetTableName(typeof(StakeHolder));
string userID = string.Empty;
strSql.Append("1=1");
strSql.AppendFormat(" AND {0}.IsDeleted<>1 AND {1}.IsDeleted<>1 ", ProcessInstanceTableName, StakeHolderTableName);
if (whereString.ContainsKey("UserID") && !string.IsNullOrEmpty(whereString["UserID"]))
{
userID = whereString["UserID"];
strSql.AppendFormat(" AND {0}.UserID = '{1}' ",
StakeHolderTableName,
userID);
}
strSql.AppendFormat("AND {1}.ProcessInstanceID = {0}.ID", ProcessInstanceTableName, StakeHolderTableName);
#region where条件
if (whereString.ContainsKey("InstanceName") && !string.IsNullOrEmpty(whereString["InstanceName"]))//标题
{
strSql.AppendFormat(" AND {1}.InstanceName like '%{0}%'", WhereSqlClauseBuilder.EscapeLikeString(whereString["InstanceName"]),
ProcessInstanceTableName);
}
if (whereString.ContainsKey("UserName") && !string.IsNullOrEmpty(whereString["UserName"]))//创建人
{
strSql.AppendFormat(" AND ({1}.UserName LIKE '%{0}%')", WhereSqlClauseBuilder.EscapeLikeString(whereString["UserName"]), ProcessInstanceTableName);
}
if (whereString.ContainsKey("LastUpdatedTime") && !string.IsNullOrEmpty(whereString["LastUpdatedTime"]))//处理时间
{
//处理时间开始
strSql.AppendFormat(" AND {0}.LastUpdatedTime>= Convert(NVARCHAR(10),'{1}',120) ",
ProcessInstanceTableName,
whereString["LastUpdatedTime"]);
//处理时间结束
strSql.AppendFormat(" AND {0}.LastUpdatedTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)",
ProcessInstanceTableName,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (whereString.ContainsKey("CreatedTime") && !string.IsNullOrEmpty(whereString["CreatedTime"]))//接收时间
{
//接收时间开始
strSql.AppendFormat(" AND {0}.CreateTime>= Convert(NVARCHAR(10),'{1}',120) ", ProcessInstanceTableName, whereString["CreatedTime"]);
//接收时间结束
strSql.AppendFormat(" AND {0}.CreateTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)", ProcessInstanceTableName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (whereString.ContainsKey("Status") && !string.IsNullOrEmpty(whereString["Status"]))//状态
{
strSql.AppendFormat(" AND {1}.Status='{0}'", whereString["Status"],
ProcessInstanceTableName);
}
if (whereString.ContainsKey("ProcessCode") && !string.IsNullOrEmpty(whereString["ProcessCode"]))//流程类型
{
strSql.AppendFormat(" AND {1}.ProcessCode='{0}'", whereString["ProcessCode"],
ProcessInstanceTableName);
}
#endregion
string selectSQL = string.Format("(SELECT DISTINCT {0}.* FROM {0},{1} WHERE {2}) as temp", ProcessInstanceTableName, StakeHolderTableName, strSql.ToString());
string selectFields = "DISTINCT *";
QueryCondition qc = new QueryCondition(pageIndex, pageSize, selectFields, selectSQL, "temp.CreateTime DESC");
PartlyCollection <ProcessInstance> result = GetPageSplitedCollection(qc);
return(result);
}
public PartlyCollection <VOAMQMeaages> GetOAMQList(OAMQFilter filter)
{
PartlyCollection <VOAMQMeaages> OAMQMessages = VOAMQListAdapter.Instance.GetList(filter);
return(OAMQMessages);
}
