protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = TimePointContext.Current.UseCurrentTime ? @"SC.UserAndContainerSnapshot_Current C
INNER JOIN SC.SchemaUserSnapshot_Current U ON U.ID =C.UserID
INNER JOIN SC.SchemaRoleSnapshot_Current R ON R.ID = C.ContainerID
INNER JOIN SC.SchemaMembersSnapshot_Current S ON S.MemberID = R.ID
INNER JOIN SC.SchemaApplicationSnapshot_Current A ON A.ID = S.ContainerID" : @"SC.UserAndContainerSnapshot C
INNER JOIN SC.SchemaUserSnapshot U ON U.ID =C.UserID
INNER JOIN SC.SchemaRoleSnapshot R ON R.ID = C.ContainerID
INNER JOIN SC.SchemaMembersSnapshot S ON S.MemberID = R.ID
INNER JOIN SC.SchemaApplicationSnapshot A ON A.ID = S.ContainerID";
qc.SelectFields = "R.*, A.ID AS AppID,A.DisplayName AS AppName,A.Name As AppName2,A.CodeName as AppCodeName";
base.OnBuildQueryCondition(qc);
WhereSqlClauseBuilder wb = new WhereSqlClauseBuilder();
wb.AppendItem("C.UserID", this.userId);
if (string.IsNullOrWhiteSpace(orderBy))
{
qc.OrderByClause = "A.DisplayName";
}
else
{
qc.OrderByClause = orderBy;
}
if (string.IsNullOrWhiteSpace(qc.WhereClause))
{
qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance);
}
else
{
qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
}
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
base.OnBuildQueryCondition(qc);
WhereSqlClauseBuilder wb = new WhereSqlClauseBuilder();
wb.AppendItem("LogID", this.syncID);
if (string.IsNullOrEmpty(qc.WhereClause))
qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance);
else
qc.WhereClause = " AND " + wb.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;
}
}
/// <summary>
///
/// </summary>
/// <param name="fillAssignees"></param>
/// <param name="action"></param>
/// <returns></returns>
public WfProcessCurrentInfoCollection Load(bool fillAssignees, Action<WhereSqlClauseBuilder> action)
{
action.NullCheck("action");
WfProcessCurrentInfoCollection result = new WfProcessCurrentInfoCollection();
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
action(whereBuilder);
string fieldNames = ORMapping.GetSelectFieldsNameSql<WfProcessInstanceData>("Data");
string sql = string.Format("SELECT {0} FROM WF.PROCESS_INSTANCES WHERE {1}",
fieldNames,
whereBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView);
if (fillAssignees)
FillAssignees(result);
return result;
}
public static IEnumerable<SCSimpleObject> QueryParents(string unitID, bool includingSelf, DateTime timePoint)
{
if (string.IsNullOrEmpty(unitID) == false)
{
string timeString = timePoint == DateTime.MinValue ? TSqlBuilder.Instance.DBCurrentTimeFunction : TSqlBuilder.Instance.FormatDateTime(timePoint);
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder().AppendCondition("ID", unitID);
string sql = string.Format(sqlTemplate, timeString, where.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, ConnectionName).Tables[0];
DataView view = new DataView(table);
view.Sort = "ID";
SCSimpleObjectCollection tempParents = new SCSimpleObjectCollection();
FillAllParents(unitID, view, includingSelf, tempParents);
tempParents.Reverse();
return tempParents;
}
else
{
return emptyResult;
}
}
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);
}
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);
}
public Int32 ChangeStatus(String userId, Int32 status)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(UserInfoDBConst.UserId, userId);
String sql = String.Format("UPDATE {0} SET LOGOUT={1} WHERE {2} ", UserInfoDBConst.TableName, status, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public Int32 ChangePassword(String userId, String password)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(UserInfoDBConst.UserId, userId);
String sql = String.Format("UPDATE {0} SET PASSWORD='******' WHERE {2} ", UserInfoDBConst.TableName, CryptoHelper.Encode(password), builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public Int32 ChangeDimission(String personID, bool dimission)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(PersonInfoDBConst.PersonID, personID);
String sql = String.Format("UPDATE {0} SET DIMISSION={1} WHERE {2} ", PersonInfoDBConst.TableName, dimission ? 1 : 0, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public Int32 DeleteDepartmentInfo(String deptID)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(DepartmentInfoDBConst.DepartmentID, deptID);
String sql = String.Format("DELETE FROM {0} WHERE {1} ", DepartmentInfoDBConst.TableName, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public Int32 ChangeStatus(String projectClassId, Int32 userFlag)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(ProjectClassInfoDBConst.ProjectClassID, projectClassId);
String sql = String.Format("UPDATE {0} SET USE_FLAG={1} WHERE {2} ", ProjectClassInfoDBConst.TableName, userFlag, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public Int32 DeleteUserInfo(String userId)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(UserInfoDBConst.UserId, userId);
String sql = String.Format("DELETE FROM {0} WHERE {1} ", UserInfoDBConst.TableName, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public Int32 DeleteReportFee(String reportID)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(ReportFeeDBConst.ReportID, reportID);
String sql = String.Format("DELETE FROM {0} WHERE {1} ", ReportFeeDBConst.TableName, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
return result;
}
public String CreatePersonCode(String departID)
{
DepartmentInfo departInfo = DepartmentInfoAdapter.Instance.LoadDepartmentInfo(departID);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(PersonInfoDBConst.DepartmentID, departID);
String sql = String.Format("SELECT ISNULL(MAX(PERSON_CODE) + 1,'{0}01') FROM {1} WHERE {2}", departInfo.DepartmentCode, PersonInfoDBConst.TableName, builder.ToSqlString());
DataTable dt = _DataHelper.GetDataTable(sql);
return dt.Rows[0][0].ToString();
}
/// <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 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;
}
/// <summary>
/// 获取与制定对象关联的
/// </summary>
/// <param name="id"></param>
/// <param name="schemaType"></param>
/// <returns></returns>
public IEnumerable<HistoryEntry> GetAllHistoryEntry(string id, ref string schemaType)
{
HistoryEntryCollection entries = new HistoryEntryCollection();
schemaType = null;
using (var context = DbHelper.GetDBContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("ID", id);
var sql = "SELECT TOP 1 SchemaType FROM [SC].[SchemaObjectSnapshot] WHERE " + builder.ToSqlString(TSqlBuilder.Instance);
sql += @"
SELECT [VersionStartTime]
,[VersionEndTime]
,[CreatorID]
,[CreatorName]
,[Status]
FROM [SC].[SchemaObjectSnapshot]
WHERE
" + builder.ToSqlString(TSqlBuilder.Instance) + " ORDER BY VersionStartTime DESC";
var cmd = db.GetSqlStringCommand(sql);
using (var dr = db.ExecuteReader(cmd))
{
if (dr.Read())
{
schemaType = dr.GetString(0);
if (dr.NextResult())
{
ORMapping.DataReaderToCollection(entries, dr);
}
else
{
cmd.Cancel();
}
}
}
return entries;
}
}
protected override string GetUpdateSql(WeChatFriend data, ORMappingItemCollection mappings, Dictionary <string, object> context)
{
UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings, "OpenID");
WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings);
string sql = string.Format("UPDATE {0} SET {1} WHERE {2}",
mappings.TableName,
uBuilder.ToSqlString(TSqlBuilder.Instance),
wBuilder.ToSqlString(TSqlBuilder.Instance));
return(sql);
}
/// <summary>
/// 拿到已存在的验证码信息，如果不存在，返回NULL
/// </summary>
/// <param name="authenticationID"></param>
/// <returns></returns>
public AuthenticationCode Load(string authenticationID)
{
authenticationID.CheckStringIsNullOrEmpty("AUTHENTICATION_ID");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("AUTHENTICATION_ID", authenticationID);
string sql = string.Format("SELECT *, CASE WHEN EXPIRE_TIME > GETDATE() THEN 1 ELSE 0 END IS_VALID FROM {0} WHERE {1}",
this.GetMappingInfo().TableName, builder.ToSqlString(TSqlBuilder.Instance));
return(this.Query(sql));
}
public bool ExistsProcessKey(string processKey)
{
processKey.CheckStringIsNullOrEmpty("processKey");
WhereSqlClauseBuilder builder = PrepareProcessKeyWhereBuilder(processKey);
string sql = string.Format("SELECT COUNT(*) FROM WF.PROCESS_DESCRIPTORS WHERE {0}",
builder.ToSqlString(TSqlBuilder.Instance));
int count = (int)DbHelper.RunSqlReturnScalar(sql, this.GetConnectionName());
return(count > 0);
}
public List <ProcessNode> LoadList(int processID, string processType)
{
string ProcessNodeTableName = ORMapping.GetTableName(typeof(ProcessNode));
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("processID", processID);
where.AppendItem("processType", processType);
string sqlCommand = string.Format("SELECT * FROM {0} WHERE {1} AND {2} ORDER BY NodeSeq ASC",
ProcessNodeTableName,
where.ToSqlString(TSqlBuilder.Instance), NotDeleted);
return(ExecuteQuery(sqlCommand));
}
public AMSEventChannelCollection LoadEventAndChannel(string eventID)
{
eventID.CheckStringIsNullOrEmpty("eventID");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("EventID", eventID);
string sql = string.Format("SELECT * FROM AMS.EventsChannels WHERE {0} ORDER BY IsDefault DESC",
builder.ToSqlString(TSqlBuilder.Instance));
return(this.QueryData <AMSEventChannel, AMSEventChannelCollection>(ORMapping.GetMappingInfo <AMSEventChannel>(), sql));
}
public override void Delete(WfRelativeProcess data)
{
ORMappingItemCollection mapping = ORMapping.GetMappingInfo <WfRelativeProcess>();
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
wBuilder.AppendItem("PROCESS_ID", data.ProcessID);
wBuilder.AppendTenantCodeSqlClause(typeof(WfProcessCurrentActivity));
string sql = string.Format("DELETE {0} WHERE {1}", mapping.TableName, wBuilder.ToSqlString(TSqlBuilder.Instance));
DbHelper.RunSql(sql, GetConnectionName());
}
public void DeleteExpiredTasks(TimeSpan expiredTime)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("START_TIME", string.Format("DATEADD(second, -{0}, GETUTCDATE())", (int)expiredTime.TotalSeconds), "<", true);
builder.AppendTenantCode();
string sql = string.Format("DELETE {0} WHERE {1}",
this.GetTableName(),
builder.ToSqlString(TSqlBuilder.Instance));
DbHelper.RunSql(sql, this.GetConnectionName());
}
private static int eventContainer_UpdateUserTask(UserTask task, UserTaskIDType idType, UserTaskFieldDefine fields, Dictionary <object, object> context)
{
ExceptionHelper.FalseThrow <ArgumentNullException>(task != null, "task");
WhereSqlClauseBuilder wBuilder = GetWhereSqlClauseBuilderByUserTask(task, idType);
UpdateSqlClauseBuilder uBuilder = GetUpdateSqlClauseBuilderByUserTask(task, fields);
string sql = string.Format("UPDATE WF.USER_TASK SET {0} WHERE {1}",
uBuilder.ToSqlString(TSqlBuilder.Instance),
wBuilder.ToSqlString(TSqlBuilder.Instance));
return(DbHelper.RunSql(sql, GetConnectionName()));
}
public List <OAMQMessages> LoadList(int count)
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("Status", 0);
StringBuilder sql = new StringBuilder();
sql.AppendFormat(" SELECT TOP {0} * FROM {1} WHERE {2} ORDER BY Sendertime ASC",
count,
ORMapping.GetTableName(typeof(OAMQMessages)),
where.ToSqlString(TSqlBuilder.Instance));
return(ExecuteQuery(sql.ToString()));
}
public void DeleteList(int processInstanceId)
{
string tableName = ORMapping.GetTableName(typeof(ProcessNodeInstance));
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("ProcessInstanceID", processInstanceId);
string sqlCommand = string.Format("delete from {0} where {1}", tableName,
where.ToSqlString(TSqlBuilder.Instance));
ExecuteQuery(sqlCommand);
}
public List<UserInfo> GetUserInfoList(WhereSqlClauseBuilder builder)
{
String sql = String.Format("SELECT * FROM {0} WHERE {1} ", UserInfoDBConst.TableName, builder.ToSqlString());
DataTable dt = _DataHelper.GetDataTable(sql);
List<UserInfo> result = new List<UserInfo>();
for (Int32 i = 0; i < dt.Rows.Count; i++)
{
UserInfo info = new UserInfo();
ORMapping.DataRowToObject(dt.Rows[i], info);
result.Add(info);
}
return result;
}
public List <ProcessNodeInstance> LoadListByBizID(int bizProcessID)
{
string tableName = ORMapping.GetTableName(typeof(ProcessNodeInstance));
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("BizProcessID", bizProcessID);
string sqlCommand = string.Format("select * from {0} where {1} AND {2} ORDER BY NodeSeq ASC", tableName,
where.ToSqlString(TSqlBuilder.Instance), NotDeleted);
return(ExecuteQuery(sqlCommand));
}
/// <summary>
/// 创建逻辑删除语句
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="data">传入的对象</param>
/// <returns>逻辑删除语句</returns>
public static string GetLogicDeleteSql <T>(T data)
{
ORMappingItemCollection mappings = ORMapping.GetMappingInfo <T>();
//创建Where子句表达式
WhereSqlClauseBuilder builder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings);
ExceptionHelper.FalseThrow(builder.Count > 0, "必须为对象{0}指定关键字", typeof(T));
return(String.Format(" UPDATE {0} SET ValidStatus=0 WHERE {1} {2}",
mappings.TableName,
builder.ToSqlString(TSqlBuilder.Instance),
TSqlBuilder.Instance.DBStatementSeperator));
}
private static string GetUpdateSql <T>(T graph, DataModel mapping, ISqlBuilder builder, string[] ignorProperties)
{
ExceptionHelper.FalseThrow <ArgumentNullException>(graph != null, "graph");
ExceptionHelper.FalseThrow <ArgumentNullException>(mapping != null, "mapping");
ExceptionHelper.FalseThrow <ArgumentNullException>(builder != null, "builder");
UpdateSqlClauseBuilder updateBuilder = GetUpdateSqlClauseBuilder(graph, mapping, ignorProperties);
WhereSqlClauseBuilder whereBuilder = GetWhereSqlClauseBuilderByPrimaryKey(graph, mapping);
return(string.Format("UPDATE {0} SET {1} WHERE {2}",
mapping.TableName,
updateBuilder.ToSqlString(builder),
whereBuilder.ToSqlString(builder)));
}
public JobType GetJobType(string jobid)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("JOB_ID", jobid);
builder.AppendTenantCode();
string sql = string.Format(@"SELECT JOB_TYPE FROM WF.JOBS WHERE {0}",
builder.ToSqlString(TSqlBuilder.Instance));
object obj = DbHelper.RunSqlReturnScalar(sql);
return((JobType)Enum.Parse(typeof(JobType), obj.ToString()));
}
public Int32 DeletePersonInfo(String personID)
{
using (TransactionScope scope = TransactionScopeFactory.Create(TransactionScopeOption.Required))
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(PersonInfoDBConst.PersonID, personID);
String sql = String.Format("DELETE FROM {0} WHERE {1} ", PersonInfoDBConst.TableName, builder.ToSqlString());
int result = _DataHelper.ExecuteSql(sql);
PersonBaseFeeDepartmentProjectInfoAdapter.Instance.DeletePersonBaseFeeDepartmentProjectInfo(null, builder);
PersonBaseFeeInfoAdapter.Instance.DeletePersonBaseFeeInfo(null, personID);
scope.Complete();
return result;
}
}
public PartlyCollection <ApprovalLog> LoadList(int processInstanceId)
{
StringBuilder sql = new StringBuilder();
sql.Append(ORMapping.GetSelectSql(typeof(ApprovalLog), TSqlBuilder.Instance));
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("ProcessInstanceID", processInstanceId);
sql.AppendFormat(" where {0} AND {1}", where.ToSqlString(TSqlBuilder.Instance), NotDeleted);
sql.Append(" ORDER BY CompletedTime desc");
return((PartlyCollection <ApprovalLog>)ExecuteQuery(sql.ToString()));
}
public AMSAdmin CheckPassword(string logonName, string password)
{
logonName.CheckStringIsNullOrEmpty("logonName");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("LogonName", logonName);
builder.AppendItem("[Password]", CalculatePassword(password));
string sql = string.Format("SELECT * FROM {0} WHERE {1}",
this.GetTableName(), builder.ToSqlString(TSqlBuilder.Instance));
return(this.QueryData(sql).SingleOrDefault());
}
private SysTaskCollection InnerQueryBatch(int batchCount)
{
string batchClause = batchCount >= 0 ? "TOP " + batchCount : string.Empty;
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("1", 1);
builder.AppendTenantCode();
string sql = string.Format("SELECT {0} * FROM WF.SYS_NOT_RUNNING_TASK WITH(UPDLOCK, READPAST) WHERE {1} ORDER BY SORT_ID ASC",
batchClause, builder.ToSqlString(TSqlBuilder.Instance));
return(this.QueryData(sql));
}
protected override string GetUpdateSql(SysTaskProcess data, ORMappingItemCollection mappings, Dictionary <string, object> context)
{
UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings, "UpdateTag");
uBuilder.AppendItem("UPDATE_TAG", "UPDATE_TAG + 1", "=", true);
WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings);
wBuilder.AppendItem("UPDATE_TAG", data.UpdateTag);
return(string.Format("UPDATE {0} SET {1} WHERE {2}",
mappings.TableName,
uBuilder.ToSqlString(TSqlBuilder.Instance),
wBuilder.ToSqlString(TSqlBuilder.Instance)));
}
// Retrieve
IList <T> IRetrievable <T> .RetrieveAll()
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
string isDeletedField = "ISDELETED"; //TODO , 通过ORM 反射找到对应的Field; 默认暂时认为都是使用ISDELETED字段
where.AppendItem(isDeletedField, true);
string sqlString = ORMapping.GetSelectSql <T>(TSqlBuilder.Instance)
+ " where " + where.ToSqlString(TSqlBuilder.Instance);
return(this.ExecuteQuery(sqlString, null));
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
var groupID = WebUtility.GetRequestQueryValue("groupID", "");
if (groupID.IsNotEmpty())
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("g.GroupID", groupID);
gridDataSource.Condition = builder.ToSqlString(TSqlBuilder.Instance);
gridMain.DataBind();
}
}
}
/// <summary>
/// 获取用户操作权限
/// </summary>
/// <param name="userID">用户id</param>
/// <param name="privilegeType">权限类型</param>
/// <param name="privilegeType">权限类型</param>
/// <returns></returns>
public IList <VUserPrivilege> GetByUserIDAndType(string userID, string privilegeType)
{
string sql = ORMapping.GetSelectSql <VUserPrivilege>(TSqlBuilder.Instance);
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("userid", SqlTextHelper.SafeQuote(userID));
if (null != privilegeType && privilegeType != "")
{
where.AppendItem("privilegeType", privilegeType);
}
sql += " where " + where.ToSqlString(TSqlBuilder.Instance);
return(ExecuteQuery(sql));
}
public TodoWork Load(int piid, int userID)
{
string tableName = ORMapping.GetTableName(typeof(TodoWork));
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("ProcessInstanceID", piid);
where.AppendItem("userID", userID);
string sqlCommand = string.Format("select top 1 * from {0} where {1} AND {2}", tableName,
where.ToSqlString(TSqlBuilder.Instance), NotDeleted);
List <TodoWork> result = ExecuteQuery(sqlCommand);
return(result.Count > 0 ? result[0] : null);
}
protected override string GetUpdateSql(WfProcessDescriptorDimension data, ORMappingItemCollection mappings, Dictionary <string, object> context)
{
UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings);
uBuilder.AppendTenantCode();
WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings);
wBuilder.AppendTenantCode();
return(string.Format("UPDATE {0} SET {1} WHERE {2}",
mappings.TableName,
uBuilder.ToSqlString(TSqlBuilder.Instance),
wBuilder.ToSqlString(TSqlBuilder.Instance)));
}
private static int DeleteRelatedData(string wfMatrixID)
{
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
whereBuilder.AppendItem(DB_FIELD_MATRIX_ID, wfMatrixID);
whereBuilder.AppendTenantCode();
StringBuilder strBuilder = new StringBuilder();
strBuilder.Append(DELETE_MR_SQL_CLAUSE);
strBuilder.Append(whereBuilder.ToSqlString(TSqlBuilder.Instance));
strBuilder.Append(TSqlBuilder.Instance.DBStatementSeperator);
strBuilder.Append(DELETE_MC_SQL_CLAUSE);
strBuilder.Append(whereBuilder.ToSqlString(TSqlBuilder.Instance));
strBuilder.Append(TSqlBuilder.Instance.DBStatementSeperator);
strBuilder.Append(DELETE_MU_SQL_CLAUSE);
strBuilder.Append(whereBuilder.ToSqlString(TSqlBuilder.Instance));
return(DbHelper.RunSql(strBuilder.ToString()));
}
protected override string GetUpdateSql(T data, ORMappingItemCollection mappings, Dictionary <string, object> context)
{
UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings, "UpdateTime");
uBuilder.AppendItem("UpdateTime", "GETDATE()", "=", true);
WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings);
string sql = string.Format("UPDATE {0} SET {1} WHERE {2}",
mappings.TableName,
uBuilder.ToSqlString(TSqlBuilder.Instance),
wBuilder.ToSqlString(TSqlBuilder.Instance));
return(sql);
}
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());
}
protected override string GetUpdateSql(TxProcess data, ORMappingItemCollection mappings, Dictionary <string, object> context, string[] ignoreProperties)
{
UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings, ignoreProperties);
string serilizedProcess = GetSerilizedProcess(data);
uBuilder.AppendItem("DATA", serilizedProcess);
context["SerilizedProcess"] = serilizedProcess;
WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data);
return(string.Format("UPDATE {0} SET {1} WHERE {2}", this.GetTableName(),
uBuilder.ToSqlString(TSqlBuilder.Instance), wBuilder.ToSqlString(TSqlBuilder.Instance)));
}
public ProcessInstance LoadByBizProcessID(int bizProcessID)
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("BizProcessID", bizProcessID);
StringBuilder sql = new StringBuilder();
sql.AppendFormat("SELECT * FROM {0} WHERE {1} AND {2} ",
ORMapping.GetTableName(typeof(ProcessInstance)),
where.ToSqlString(TSqlBuilder.Instance), NotDeleted
);
List <ProcessInstance> List = ExecuteQuery(sql.ToString());
return(List == null || List.Count == 0 ? null : List[0]); //返回第一个
}
/// <summary>
/// 移动到已经完成系统任务中
/// </summary>
/// <param name="taskID">被移动的任务的ID</param>
/// <param name="status">重置任务的状态</param>
public SysAccomplishedTask MoveToCompletedSysTask(SysTask task, SysTaskStatus status, string statusText)
{
SysAccomplishedTask result = null;
PerformanceMonitorHelper.GetDefaultMonitor().WriteExecutionDuration(string.Format("MoveToAccomplished({0})", this.GetType().FullName), () =>
{
Dictionary <string, object> context = new Dictionary <string, object>();
ORMappingItemCollection mappings = GetMappingInfo(context);
WhereSqlClauseBuilder builder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(task, mappings);
ORMappingItemCollection mappingsCompleted = ORMapping.GetMappingInfo <SysAccomplishedTask>();
SysAccomplishedTask taskCompleted = new SysAccomplishedTask(task);
taskCompleted.Status = status;
taskCompleted.StatusText = statusText;
StringBuilder sql = new StringBuilder();
sql.AppendFormat("DELETE FROM {0} WHERE {1}", mappingsCompleted.TableName, builder.ToSqlString(TSqlBuilder.Instance));
sql.Append(TSqlBuilder.Instance.DBStatementSeperator);
sql.Append(GetMoveSysTaskSql(taskCompleted, context));
sql.Append(TSqlBuilder.Instance.DBStatementSeperator);
sql.Append("IF @@ROWCOUNT = 0");
sql.Append(TSqlBuilder.Instance.DBStatementSeperator);
sql.Append("\t" + ORMapping.GetInsertSql(taskCompleted, mappingsCompleted, TSqlBuilder.Instance));
sql.Append(TSqlBuilder.Instance.DBStatementSeperator);
sql.AppendFormat("DELETE FROM {0} WHERE {1}", mappings.TableName, builder.ToSqlString(TSqlBuilder.Instance));
using (TransactionScope scope = TransactionScopeFactory.Create())
{
DbHelper.RunSql(sql.ToString(), this.GetConnectionName());
scope.Complete();
}
result = taskCompleted;
});
return(result);
}
protected void ShowToDoList()
{
lblTitle.Text = "待办事项";
//LDM 在这里设置页面的页大小，原来的代码设定为固定值20
UserSettings userSettings = UserSettings.GetSettings(DeluxeIdentity.CurrentUser.ID);
GridViewTask.PageSize = userSettings.GetPropertyValue("CommonSettings", "ToDoListPageSize", this.GridViewTask.PageSize);
WhereSqlClauseBuilder wscb = new WhereSqlClauseBuilder();
wscb.AppendItem("SEND_TO_USER", DeluxeIdentity.CurrentUser.ID);
whereCondition.Value = wscb.ToSqlString(TSqlBuilder.Instance);
//保存原始待办人ID到控件中
this.hiddenOriginalUserID.Value = DeluxeIdentity.CurrentUser.ID;
ExecQuery();
}
/// <summary>
/// 是否存在且没有归档
/// </summary>
/// <param name="resourceID"></param>
/// <returns></returns>
public bool ExistsAndNotArchived(string resourceID)
{
resourceID.CheckStringIsNullOrEmpty("resourceID");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("RESOURCE_ID", resourceID);
builder.AppendTenantCode(typeof(AppCommonInfo));
string sql = string.Format("SELECT ARCHIVE_STATUS FROM WF.APPLICATIONS_COMMON_INFO WHERE {0}",
builder.ToSqlString(TSqlBuilder.Instance));
object result = DbHelper.RunSqlReturnScalar(sql, GetConnectionName());
return(result != null && result.ToString() != "1");
}
public String CreateProjectClassId(String parentClassId)
{
String sql;
if (String.IsNullOrEmpty(parentClassId))
{
sql = String.Format("SELECT ISNULL(MAX(PROJECT_CLASS_ID) + 1,1) FROM {0} WHERE ISNULL(PARENT_CLASS_ID,'')=''", ProjectClassInfoDBConst.TableName);
}
else
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(ProjectClassInfoDBConst.ParentClassID, parentClassId);
sql = String.Format("SELECT ISNULL(MAX(PROJECT_CLASS_ID) + 1,'{0}01') FROM {1} WHERE {2}", parentClassId, ProjectClassInfoDBConst.TableName, builder.ToSqlString());
}
DataTable dt = _DataHelper.GetDataTable(sql);
return dt.Rows[0][0].ToString();
}
public List <TaxInfo> GetTaxInfoList(String yearMonth, WhereSqlClauseBuilder builder)
{
String sql = String.Format("SELECT * FROM {0} WHERE {1} ORDER BY QUANTUM_START"
, String.IsNullOrEmpty(yearMonth) ? TaxInfoDBConst.TableName : ORMapping.GetMappingInfo <TaxMonthInfo>().TableName
, builder.ToSqlString());
DataTable dt = _DataHelper.GetDataTable(sql);
List <TaxInfo> result = new List <TaxInfo>();
for (Int32 i = 0; i < dt.Rows.Count; i++)
{
TaxInfo info = String.IsNullOrEmpty(yearMonth) ? new TaxInfo() : new TaxMonthInfo();
ORMapping.DataRowToObject(dt.Rows[i], info);
result.Add(info);
}
return(result);
}
public String CreateDepartmentCode(String parentDeptID)
{
String sql;
if(String.IsNullOrEmpty(parentDeptID))
{
sql = String.Format("SELECT ISNULL(MAX(DEPARTMENT_CODE) + 1,1) FROM {0} WHERE ISNULL(PARENT_ID,'')=''", DepartmentInfoDBConst.TableName);
}
else
{
DepartmentInfo departInfo = this.LoadDepartmentInfo(parentDeptID);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(DepartmentInfoDBConst.ParentID, parentDeptID);
sql = String.Format("SELECT ISNULL(MAX(DEPARTMENT_CODE) + 1,'{0}01') FROM {1} WHERE {2}", departInfo.DepartmentCode, DepartmentInfoDBConst.TableName, builder.ToSqlString());
}
DataTable dt = _DataHelper.GetDataTable(sql);
return dt.Rows[0][0].ToString();
}
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);
}
/// <summary>
/// 返回流程所有的已流转过的活动节点
/// </summary>
/// <param name="processID"></param>
/// <returns></returns>
public WfProcessCurrentActivityCollection Load(string processID)
{
processID.CheckStringIsNullOrEmpty(processID);
WfProcessCurrentActivityCollection result = new WfProcessCurrentActivityCollection();
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
wBuilder.AppendItem("PROCESS_ID", processID);
wBuilder.AppendTenantCodeSqlClause(typeof(WfProcessCurrentActivity));
string sql = string.Format("SELECT * FROM WF.PROCESS_CURRENT_ACTIVITIES WHERE {0}",
wBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView);
return result;
}
public Int32 ChangeStatus(String yearMonth, String feeID, Int32 userFlag)
{
FeeInfo feeInfo = this.LoadFeeInfo(yearMonth, feeID);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(FeeInfoDBConst.FeeID, feeID);
if (!String.IsNullOrEmpty(yearMonth))
{
builder.AppendItem(FeeMonthInfoDBConst.YearMonth, yearMonth);
}
String sql = String.Format("UPDATE {0} SET USE_FLAG={1} WHERE {2} ", String.IsNullOrEmpty(yearMonth) ? FeeInfoDBConst.TableName : ORMapping.GetMappingInfo<FeeMonthInfo>().TableName, userFlag, builder.ToSqlString());
using (TransactionScope scope = TransactionScopeFactory.Create(TransactionScopeOption.Required))
{
int result = _DataHelper.ExecuteSql(sql);
if (!String.IsNullOrEmpty(feeInfo.ParentID))
{
this.UpdateCalculateExp(yearMonth, feeInfo.ParentID);
}
scope.Complete();
return result;
}
}
public WfClientUserOperationLogPageQueryResult QueryUserOperationLogByResourceID(string resourceID, int startRowIndex, int maximumRows, string orderBy, int totalCount)
{
resourceID.CheckStringIsNullOrEmpty("resourceID");
OperationContext.Current.FillContextToOguServiceContext();
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("RESOURCE_ID", resourceID);
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);
}
public void Update(string processID, WfProcessCurrentActivityCollection pcas)
{
processID.CheckStringIsNullOrEmpty("processID");
pcas.NullCheck("pcas");
StringBuilder strB = new StringBuilder();
InSqlClauseBuilder deleteActivityIDs = new InSqlClauseBuilder("ACTIVITY_ID");
foreach (WfProcessCurrentActivity pca in pcas)
{
if (strB.Length > 0)
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.Append(ORMapping.GetInsertSql(pca, TSqlBuilder.Instance));
deleteActivityIDs.AppendItem(pca.ActivityID);
}
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
wBuilder.AppendItem("PROCESS_ID", processID);
wBuilder.AppendTenantCodeSqlClause(typeof(WfProcessCurrentActivity));
string sqlDelete = string.Format("DELETE WF.PROCESS_CURRENT_ACTIVITIES WHERE {0}",
wBuilder.ToSqlString(TSqlBuilder.Instance));
if (deleteActivityIDs.Count > 0)
sqlDelete += string.Format(" AND {0}", deleteActivityIDs.ToSqlString(TSqlBuilder.Instance));
string sql = sqlDelete;
if (strB.Length > 0)
sql += TSqlBuilder.Instance.DBStatementSeperator + strB.ToString();
DbHelper.RunSqlWithTransaction(sql, GetConnectionName());
}