public UserTaskCollection LoadUserTasksByActivity(string activityID, Action <InSqlClauseBuilder> action)
{
action.NullCheck("action");
InSqlClauseBuilder builder = new InSqlClauseBuilder();
action(builder);
string sql = string.Format("SELECT * FROM WF.USER_TASK WHERE ACTIVITY_ID = {0}",
TSqlBuilder.Instance.CheckUnicodeQuotationMark(activityID));
if (builder.Count > 0)
{
sql += " AND " + builder.ToSqlStringWithInOperator(TSqlBuilder.Instance);
}
UserTaskCollection result = new UserTaskCollection();
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
UserTask task = new UserTask();
ORMapping.DataRowToObject(row, task);
result.Add(task);
}
return(result);
}
public UserTaskCollection LoadUserTasks(Action <WhereSqlClauseBuilder> action)
{
action.NullCheck("action");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
action(builder);
string sql = string.Format("SELECT * FROM WF.USER_TASK WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance));
UserTaskCollection result = new UserTaskCollection();
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
UserTask task = new UserTask();
ORMapping.DataRowToObject(row, task);
result.Add(task);
}
return(result);
}
public StartWorkflowJobCollection LoadValidData()
{
var result = new StartWorkflowJobCollection();
var ds = DbHelper.RunSqlReturnDS(LOAD_VALID_JOB_SQL_CLAUSE, this.GetConnectionName());
ORMapping.DataViewToCollection(result, ds.Tables[0].DefaultView);
List <string> jobIDs = new List <string>();
result.ForEach(d => jobIDs.Add(d.JobID));
JobScheduleWithJobIDCollection schedules = JobScheduleAdapter.Instance.LoadByJobID(jobIDs.ToArray());
foreach (JobBase job in result)
{
IList <JobScheduleWithJobID> list = schedules.FindAll(s => s.JobID == job.JobID);
foreach (JobScheduleWithJobID schdule in list)
{
job.Schedules.Add(schdule);
}
}
return(result);
}
/// <summary>
/// 根据ResourceID和ClassName加载相关的数据
/// </summary>
/// <typeparam name="TRelative"></typeparam>
/// <typeparam name="TRelativeCollection"></typeparam>
/// <param name="resourceID"></param>
/// <param name="className"></param>
/// <returns></returns>
public TRelativeCollection LoadRelativeData <TRelative, TRelativeCollection>(
string resourceID, string className)
where TRelative : GenericFormRelativeData, new()
where TRelativeCollection : EditableDataObjectCollectionBase <TRelative>, new()
{
resourceID.CheckStringIsNullOrEmpty("resourceID");
className.CheckStringIsNullOrEmpty("className");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("RESOURCE_ID", resourceID).AppendItem("CLASS", className);
builder.AppendTenantCode(typeof(TRelative));
string sql = string.Format("SELECT * FROM WF.GENERIC_FORM_RELATIVE_DATA WHERE {0} ORDER BY SORT_ID",
builder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
TRelativeCollection result = new TRelativeCollection();
ORMapping.DataViewToCollection(result, table.DefaultView);
foreach (TRelative item in result)
{
XmlDocument xmlDoc = XmlHelper.CreateDomDocument(item.XmlContent);
XmlHelper.DeserializeToObject(xmlDoc, item);
}
return(result);
}
public SOARolePropertyDefinitionCollection LoadByRoleID(string roleID)
{
roleID.CheckStringIsNullOrEmpty("roleID");
string sql = string.Format("SELECT * FROM WF.ROLE_PROPERTIES_DEFINITIONS WHERE {0} ORDER BY SORT_ORDER",
roleID.ToRoleIDCriteria());
using (TransactionScope scope = TransactionScopeFactory.Create(TransactionScopeOption.Suppress))
{
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
SOARolePropertyDefinitionCollection result = new SOARolePropertyDefinitionCollection();
foreach (DataRow row in table.Rows)
{
SOARolePropertyDefinition property = new SOARolePropertyDefinition();
ORMapping.DataRowToObject(row, property);
result.Add(property);
}
return(result);
}
}
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);
}
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]);
}
private bool CheckScheduleInJob(string schID)
{
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
whereBuilder.AppendItem("SCHEDULE_ID", schID);
whereBuilder.AppendTenantCode(typeof(JobSchedule));
DataSet ds = DbHelper.RunSqlReturnDS(
JobBaseAdapter.SELECT_JOB_SCHEDULES_ID_CLAUSE + whereBuilder.ToSqlString(TSqlBuilder.Instance),
GetConnectionName());
return(ds.Tables[0].Rows.Count != 0 ? true : false);
}
/// <summary>
/// 调用本方法，获取有Mapping关系的内部用户;
/// </summary>
/// <returns></returns>
public DataTable GetMappingRelationUsers()
{
string sql = @"SELECT EXT_INT_USERMAPPING.EXT_USERID,EXT_INT_USERMAPPING.USERID, USERS.LOGON_NAME,USERS.LAST_NAME,USERS.FIRST_NAME
FROM USERS,EXT_INT_USERMAPPING
WHERE USERS.GUID = EXT_INT_USERMAPPING.USERID;";
DataSet ds = DbHelper.RunSqlReturnDS(sql, GetConnectionName());
if (ds != null && ds.Tables.Count == 1)
{
return(ds.Tables[0]);
}
return(null);
}
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);
}
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 bool Exists(Action <WhereSqlClauseBuilder> whereAction, ORMappingItemCollection mappings)
{
whereAction.NullCheck("whereAction");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
whereAction(builder);
builder.AppendTenantCode(typeof(T));
string sql = string.Format("SELECT TOP 1 * FROM {0}", mappings.TableName);
if (builder.Count > 0)
{
sql = sql + string.Format(" WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance));
}
return((int)DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0].Rows.Count > 0);
}
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);
}
public InvalidAssigneeCollection Load(IConnectiveSqlClause builder)
{
InvalidAssigneeCollection result = new InvalidAssigneeCollection();
string sql = SelectSQL;
if (builder.IsEmpty == false)
{
sql = string.Format("{0} WHERE {1}",
SelectSQL,
builder.AppendTenantCodeSqlClause(typeof(InvalidAssignee)).ToSqlString(TSqlBuilder.Instance));
}
DataTable dt = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
ORMapping.DataViewToCollection(result, dt.DefaultView);
return(result);
}
public UploadFileHistory Load(int id)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("ID", id);
builder.AppendTenantCode(typeof(UploadFileHistory));
string sql = string.Format("SELECT * FROM WF.UPLOAD_FILE_HISTORY WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, AppLogSettings.GetConfig().ConnectionName).Tables[0];
ExceptionHelper.FalseThrow(table.Rows.Count > 0, "不能找到ID为{0}的UploadFileHistory的数据");
UploadFileHistory history = new UploadFileHistory();
ORMapping.DataRowToObject(table.Rows[0], history);
return(history);
}
protected TDataCollection QueryData <TData, TDataCollection>(ORMappingItemCollection mapping, string sql)
where TData : new()
where TDataCollection : EditableDataObjectCollectionBase <TData>, new()
{
TDataCollection result = new TDataCollection();
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
TData data = new TData();
ORMapping.DataRowToObject(row, mapping, data);
result.Add(data);
}
return(result);
}
/// <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>
/// 根据CategoryID获取Category集合
/// </summary>
/// <param name="categoryID"></param>
/// <returns></returns>
public TaskCategoryCollection GetCategoriesByCategoryIDs(params string[] categoryID)
{
string strSQL = string.Empty;
TaskCategoryCollection taskCategories = new TaskCategoryCollection();
if (categoryID.Length > 0)
{
InSqlClauseBuilder inSQL = new InSqlClauseBuilder("CATEGORY_GUID");
inSQL.AppendItem(categoryID);
strSQL = string.Format("SELECT * FROM WF.USER_TASK_CATEGORY WHERE {0} ORDER BY INNER_SORT_ID",
inSQL.AppendTenantCodeSqlClause(typeof(TaskCategory)).ToSqlString(TSqlBuilder.Instance));
DataView dv = DbHelper.RunSqlReturnDS(strSQL, ConnectionDefine.DBConnectionName).Tables[0].DefaultView;
taskCategories.LoadFromDataView(dv);
}
return(taskCategories);
}
/// <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, ConnectionDefine.DBConnectionName).Tables[0].DefaultView;
TaskCategoryCollection tcc = new TaskCategoryCollection();
tcc.LoadFromDataView(dv);
return(tcc);
}
public UserTaskCollection LoadUserTasks(string tableName, IConnectiveSqlClause wBuilder, OrderBySqlClauseBuilder orderByBuilder, int count, bool nolock = false)
{
UserTaskCollection result = new UserTaskCollection();
string top = string.Empty;
if (count >= 0)
{
top = string.Format("TOP {0}", count);
}
if (wBuilder.IsEmpty == false)
{
if (nolock)
{
tableName += " (NOLOCK)";
}
string sql = string.Format("SELECT {0} * FROM {1} WHERE {2}",
top,
tableName,
wBuilder.ToSqlString(TSqlBuilder.Instance));
if (orderByBuilder.Count > 0)
{
sql += " ORDER BY " + orderByBuilder.ToSqlString(TSqlBuilder.Instance);
}
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
UserTask task = new UserTask();
ORMapping.DataRowToObject(row, task);
result.Add(task);
}
}
return(result);
}
public bool CheckGroupManager(WfGroup group, IUser manger)
{
string sql = string.Format("SELECT * FROM WF.GROUP_USERS WHERE GROUP_ID = {0} AND USER_ID= {1}",
TSqlBuilder.Instance.CheckQuotationMark(group.GroupID, true), TSqlBuilder.Instance.CheckQuotationMark(manger.ID, true));
DataSet ds = DbHelper.RunSqlReturnDS(sql, GetConnectionName());
if (ds == null)
{
return(false);
}
if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 根据RoleID加载行信息
/// </summary>
/// <param name="roleID"></param>
/// <param name="role"></param>
/// <param name="definition">列定义</param>
/// <returns></returns>
public SOARolePropertyRowCollection LoadByRoleID(string roleID, IRole role, SOARolePropertyDefinitionCollection definition)
{
roleID.CheckStringIsNullOrEmpty("roleID");
definition.NullCheck("definition");
StringBuilder strB = new StringBuilder();
strB.AppendFormat("SELECT * FROM WF.ROLE_PROPERTIES_ROWS WHERE {0} ORDER BY ROW_NUMBER", roleID.ToRoleIDCriteria());
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.AppendFormat("SELECT * FROM WF.ROLE_PROPERTIES_CELLS WHERE {0} ORDER BY PROPERTIES_ROW_ID", roleID.ToRoleIDCriteria());
SOARolePropertyRowCollection result = new SOARolePropertyRowCollection(role);
using (TransactionScope scope = TransactionScopeFactory.Create(TransactionScopeOption.Suppress))
{
DataSet ds = DbHelper.RunSqlReturnDS(strB.ToString(), GetConnectionName());
Dictionary <int, SOARolePropertyValueCollection> propertyValues = SOARolePropertyValueCollection.LoadAndGroup(ds.Tables[1].DefaultView, definition);
foreach (DataRow row in ds.Tables[0].Rows)
{
SOARolePropertyRow property = new SOARolePropertyRow(role);
ORMapping.DataRowToObject(row, property);
SOARolePropertyValueCollection values = null;
if (propertyValues.TryGetValue(property.RowNumber, out values))
{
property.Values.CopyFrom(values);
}
result.Add(property);
}
}
return(result);
}
protected TCollection QueryData(ORMappingItemCollection mapping, string sql)
{
TCollection result = new TCollection();
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
T data = CreateNewData(row);
ORMapping.DataRowToObject(row, mapping, data);
if (data is ILoadableDataEntity)
{
((ILoadableDataEntity)data).Loaded = true;
}
result.Add(data);
}
return(result);
}
public UserTaskCollection LoadUserTasks(string tableName, IConnectiveSqlClause builder)
{
UserTaskCollection result = new UserTaskCollection();
if (builder.IsEmpty == false)
{
string sql = string.Format("SELECT * FROM {0} WHERE {1}",
tableName, builder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
UserTask task = new UserTask();
ORMapping.DataRowToObject(row, task);
result.Add(task);
}
}
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);
}