/// <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;
}
private void BuildCondition(QueryCondition qc)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
if (this.deepOption == 1)
{
builder.AppendCondition("C.ContainerID", this.scope.ID);
}
else
{
if (deepOption == 2)
{
builder.AppendItem("C.ContainerID", this.scope.ID, "<>");
}
}
builder.NormalFor("C.Status").NormalFor("S.Status").NormalFor("SM.Status").NormalFor("AUS.Status");
var timeBulder1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("C.");
var timeBulder2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("S.");
var time3 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("AUS.");
var time4 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("SM.");
var conditionClause = new ConnectiveSqlClauseCollection(builder, timeBulder1, timeBulder2, time3, time4).ToSqlString(TSqlBuilder.Instance);
if (string.IsNullOrEmpty(qc.WhereClause) == false)
qc.WhereClause += " AND (" + conditionClause + ")";
else
qc.WhereClause = conditionClause;
}
private void BindDataGrid()
{
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(this.UserUploadCondition);
if (this.processUsers.ReadOnly)
{
this.ObjectDataSourceUploadLogs.Condition = builder;
}
else
{
ConnectiveSqlClauseCollection collection = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And);
collection.Add(builder);
WhereSqlClauseBuilder userwherebuilder = new WhereSqlClauseBuilder();
userwherebuilder.LogicOperator = LogicOperatorDefine.Or;
foreach (var item in this.processUsers.SelectedOuUserData)
{
userwherebuilder.AppendItem("OPERATOR_ID", item.ID);
}
collection.Add(userwherebuilder);
this.ObjectDataSourceUploadLogs.Condition = collection.ToSqlString(TSqlBuilder.Instance);
}
this.ObjectDataSourceUploadLogs.LastQueryRowCount = -1;
this.DeluxeGridUploadLog.PageIndex = 0;
this.DeluxeGridUploadLog.SelectedKeys.Clear();
this.DeluxeGridUploadLog.DataBind();
}
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);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.SchemaUserSnapshot_Current O INNER JOIN SC.UserAndContainerSnapshot_Current R ON O.ID = R.UserID" : "SC.SchemaUserSnapshot O INNER JOIN SC.UserAndContainerSnapshot R ON O.ID = R.UserID";
qc.SelectFields = "O.*";
if (string.IsNullOrEmpty(this.orderBy))
qc.OrderByClause = "R.VersionStartTime DESC";
else
qc.OrderByClause = orderBy;
base.OnBuildQueryCondition(qc);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
var timeCondition1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
builder.AppendItem("R.ContainerID", this.containerId);
InnerBuildWhere(builder);
builder.AppendItem("O.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
var allConditions = new ConnectiveSqlClauseCollection(timeCondition1, timeCondition2, builder);
if (string.IsNullOrEmpty(qc.WhereClause))
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance);
}
else
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
}
}
public SchemaObjectCollection QueryApplications(string[] schemaTypes, bool includingDeleted, DateTime timePoint)
{
SchemaObjectCollection result = new SchemaObjectCollection();
ConnectiveSqlClauseCollection timeConditionC = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "SC.");
InSqlClauseBuilder schemaBuilder = new InSqlClauseBuilder("SC.SchemaType");
schemaBuilder.AppendItem(schemaTypes);
WhereSqlClauseBuilder builderC = new WhereSqlClauseBuilder();
if (includingDeleted == false)
builderC.AppendItem("SC.Status", (int)SchemaObjectStatus.Normal);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(schemaBuilder, builderC, timeConditionC);
string sql = string.Format("SELECT SC.*" +
"\nFROM SC.SchemaObject SC" +
"\nWHERE {0}", connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView);
return result;
}
//计算薪资
public Int32 CalculatePayMonthInfo(String yearMonth)
{
int result = 0;
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(FeeMonthInfoDBConst.YearMonth, yearMonth);
builder.AppendItem(FeeMonthInfoDBConst.UseFlag, Status.True.ToString("D"));
builder.AppendItem(FeeMonthInfoDBConst.FeeType, FeeType.Parameter.ToString("D"), "<>");
builder.AppendItem(FeeMonthInfoDBConst.FeeType, FeeType.Tax.ToString("D"), "<>");
builder.AppendItem(String.Concat("ISNULL(",FeeMonthInfoDBConst.CalculateExp,",'')<>''"));
List<FeeInfo> feeList = FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder);
foreach (FeeMonthInfo feeMonthInfo in feeList.Where(fe => !String.IsNullOrEmpty(fe.CalculateExp)))
{
String caculateExp = FeeInfoAdapter.Instance.ConvertExp(yearMonth, feeMonthInfo.CalculateExp);
//if (feeMonthInfo.FeeType.Equals(FeeType.Tax))
//{
// caculateExp = "ISNULL(DBO.CACULATETAX(''" + yearMonth + "'',''" + FeeInfoAdapter.Instance.GetTaxFeeID(yearMonth, feeMonthInfo.FeeID) + "''," + FeeInfoAdapter.Instance.ConvertExp(yearMonth, FeeInfoAdapter.Instance.GetTaxFeeCalculateExp(yearMonth, feeMonthInfo.FeeID)) + "),0)";
//}
//else
//{
// caculateExp = FeeInfoAdapter.Instance.ConvertExp(yearMonth, feeMonthInfo.CalculateExp);
//}
String sql = String.Format(@"DECLARE @SQL VARCHAR(8000)
SELECT @SQL=ISNULL(@SQL + '],[','') + Convert(nvarchar(32),FEE_ID) FROM FEE_MONTH F WHERE (F.FEE_TYPE={5} OR F.FEE_TYPE={6}) AND ISNULL(CALCULATE_EXP,'')='' AND YEAR_MONTH={0}
EXEC ('INSERT INTO PAY_MONTH(YEAR_MONTH,PERSON_ID,PERSON_NAME,FEE_ID,FEE_NAME,FEE_VALUE,PAY_MONEY,FORMULA)
SELECT ''{0}'' AS ''YEAR_MONTH'', PERSON_ID, PERSON_NAME, ''{1}'' AS ''FEE_ID'', ''{2}'' AS ''FEE_NAME'', {3} AS ''FEE_VALUE'', {3} AS ''PAY_MONEY'',''{4}'' AS ''FORMULA''
FROM (SELECT PERSON_ID, PERSON_NAME, FEE_ID,PAY_MONEY FROM PAY_MONTH WHERE YEAR_MONTH=''{0}'')P PIVOT(MAX(PAY_MONEY) FOR FEE_ID IN ([' + @SQL + ']))B') "
, yearMonth, feeMonthInfo.FeeID, feeMonthInfo.FeeName, caculateExp, caculateExp.Replace("'", ""), FeeType.Common.ToString("D"), FeeType.Parameter.ToString("D"));
result += _DataHelper.ExecuteSql(sql);
}
return result;
}
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("TASK_GUID", Request.QueryString["id"]);
dataSourceMain.Condition = where;
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
base.OnBuildQueryCondition(qc);
qc.SelectFields = "*";
qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.AUAdminScopeItemSnapshot_Current" : "SC.AUAdminScopeItemSnapshot";
if (string.IsNullOrEmpty(qc.OrderByClause))
qc.OrderByClause = "AUScopeItemName ASC";
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendCondition("SchemaType", this.scopeType).NormalFor("Status");
var timeBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder();
var allCondition = new ConnectiveSqlClauseCollection(builder, timeBuilder).ToSqlString(TSqlBuilder.Instance);
if (string.IsNullOrEmpty(qc.WhereClause))
{
qc.WhereClause = allCondition;
}
else
{
qc.WhereClause = qc.WhereClause + "AND (" + allCondition + ")";
}
}
/// <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;
}
}
public IEnumerable<ReferenceHistoryEntry> GetReferenceHistoryEntries(string objId)
{
ReferenceHistoryEntryCollection list = new ReferenceHistoryEntryCollection();
WhereSqlClauseBuilder where1 = new WhereSqlClauseBuilder();
where1.AppendItem("R.ObjectID", objId);
WhereSqlClauseBuilder where2 = new WhereSqlClauseBuilder();
where2.AppendItem("R.ParentID", objId);
var sql = string.Format(@"SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName
FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ParentID = O.ID
WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {0}
UNION
SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName
FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ObjectID = O.ID
WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {1} ORDER BY VersionStartTime ASC
", where1.ToSqlString(TSqlBuilder.Instance), where2.ToSqlString(TSqlBuilder.Instance));
using (var context = DbHelper.GetDBContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
var cmd = db.GetSqlStringCommand(sql);
using (var dr = db.ExecuteReader(cmd))
{
ORMapping.DataReaderToCollection(list, dr);
}
}
return list;
}
private static ConnectiveSqlClauseCollection SchemaStrict(ConnectiveSqlClauseCollection c, string prefix, PC.StandardObjectSchemaType schema)
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem(prefix + "SchemaType", schema);
c.Add(where);
return c;
}
public SchemaObjectCollection LoadCurrentUsers(string ownerID)
{
ownerID.CheckStringIsNullOrEmpty("ownerID");
var timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(DateTime.MinValue);
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
whereBuilder.AppendItem("C.OwnerID", ownerID);
timePointBuilder.Add(whereBuilder);
string sql = string.Format(
"SELECT SC.* FROM {0} SC INNER JOIN SC.ConditionCalculateResult C ON SC.ID = C.UserID WHERE {1}",
ORMapping.GetMappingInfo(typeof(SchemaObjectBase)).TableName, timePointBuilder.ToSqlString(TSqlBuilder.Instance));
SchemaObjectCollection result = new SchemaObjectCollection();
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
result.LoadFromDataView(DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0].DefaultView);
}
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;
}
}
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 + ")";
}
}
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);
}
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 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 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 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 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 System.Collections.Generic.Stack<SchemaNavPathNode> Query(Guid id, DateTime timePoint)
{
System.Collections.Generic.Stack<SchemaNavPathNode> stack = new Stack<SchemaNavPathNode>();
StringBuilder sb = new StringBuilder();
var db = DbHelper.GetDBDatabase();
SchemaNavPathNode node;
do
{
node = null;
sb.Clear();
sb.Append(TimePointContext.Current.UseCurrentTime ? @"
SELECT R.ObjectID, R.ParentID, O.ID, O.Name, O.DisplayName,O.SchemaType
FROM SC.SchemaOrganizationSnapshot_Current AS O INNER JOIN SC.SchemaRelationObjects_Current AS R ON O.ID = R.ObjectID
WHERE " : @"
SELECT R.ObjectID, R.ParentID, O.ID, O.Name, O.DisplayName,O.SchemaType
FROM SC.SchemaOrganizationSnapshot AS O INNER JOIN SC.SchemaRelationObjects AS R ON O.ID = R.ObjectID
WHERE ");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem<int>("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem<string>("R.ParentSchemaType", "Organizations");
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "O.");
builder.AppendItem<int>("O.Status", (int)SchemaObjectStatus.Normal);
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "R.");
builder.AppendItem<int>("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem<string>("R.ObjectID", id.ToString());
var schemaConditon1 = DataSourceUtil.SchemaTypeCondition("R.ParentSchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
var schemaConditon2 = DataSourceUtil.SchemaTypeCondition("O.SchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
var schemaConditon3 = DataSourceUtil.SchemaTypeCondition("O.ChildSchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
sb.Append(new ConnectiveSqlClauseCollection(builder, timeCondition, timeCondition2, schemaConditon1, schemaConditon2, schemaConditon3).ToSqlString(TSqlBuilder.Instance));
using (var dr = db.ExecuteReader(System.Data.CommandType.Text, sb.ToString()))
{
if (dr.Read())
{
node = new SchemaNavPathNode()
{
ID = dr.GetString(0),
ParentId = dr.GetString(1),
Name = dr.GetString(2),
Description = dr.GetString(3),
NodeType = dr.GetString(4)
};
stack.Push(node);
id = Guid.Parse(node.ParentId);
}
}
} while (node != null && node.ID != SCOrganization.RootOrganizationID);
return stack;
}
/// <summary>
/// 根据userID和一组ContainerID，加载该Member所拥有的权限
/// </summary>
/// <param name="userID"></param>
/// <param name="containerIDs"></param>
/// <returns></returns>
public SCContainerAndPermissionCollection LoadCurrentContainerAndPermissions(string userID, IEnumerable<string> containerIDs)
{
var ids = containerIDs.ToArray();
SCContainerAndPermissionCollection result = null;
var roleIDs = (from r in new OguUser(userID).Roles.GetAllRoles() select r.ID).ToArray();
if (ids.Length > 0 && roleIDs.Length > 0)
{
var timeConditon1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("A.");
var condition = new WhereSqlClauseBuilder();
condition.AppendItem("A.Status", (int)SchemaObjectStatus.Normal);
InSqlClauseBuilder inSql = new InSqlClauseBuilder("A.ContainerID");
inSql.AppendItem(ids);
InSqlClauseBuilder inSqlRole = new InSqlClauseBuilder("A.MemberID");
inSqlRole.AppendItem(roleIDs);
var sql = string.Format(
"SELECT A.* FROM SC.Acl_Current A WHERE {0} ORDER BY SortID ",
new ConnectiveSqlClauseCollection(timeConditon1, condition, inSql, inSqlRole).ToSqlString(TSqlBuilder.Instance));
result = new SCContainerAndPermissionCollection();
AUCommon.DoDbAction(() =>
{
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName()))
{
while (reader.Read())
{
string containerID = (string)reader["ContainerID"];
string permission = (string)reader["ContainerPermission"];
if (result.ContainsKey(containerID, permission) == false)
{
result.Add(new SCContainerAndPermission()
{
ContainerID = containerID,
ContainerPermission = permission
});
}
}
}
}
});
return result;
}
else
{
result = new SCContainerAndPermissionCollection();
}
return result;
}
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
if (this.IsPostBack == false && this.Request["id"] != null)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("SynchronizeID", this.Request["id"]);
this.dataSourceMain.Condition = builder;
}
}
private static JobCollection FetchStandByJobs(int batchCount)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("ENABLED", "1");
builder.AppendItem("JOB_STATUS", (int)JobStatus.StandBy);
return JobBaseAdapter.Instance.UPDLOCKLoadJobs(batchCount, builder);
}
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();
}
public SCConditionCollection GetRoleConditions(string roleId)
{
var role = DbUtil.GetEffectiveObject<SCRole>(roleId);
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("OwnerID", role.ID);
where.AppendItem("Status", (int)SchemaObjectStatus.Normal);
return PC.Adapters.SCConditionAdapter.Instance.Load(where, DateTime.MinValue);
}
public SCOperationLogCollection LoadByResourceID(string resourceID)
{
resourceID.CheckStringIsNullOrEmpty("resourceID");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("ResourceID", resourceID);
return Load(builder);
}
public static Conditions.SCConditionCollection LoadConditions(string[] ids)
{
if (ids.Length > 0)
{
var mIdIn = new InSqlClauseBuilder("OwnerID");
mIdIn.AppendItem(ids);
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("Status", (int)SchemaObjectStatus.Normal);
return(Adapters.SCConditionAdapter.Instance.Load(new ConnectiveSqlClauseCollection(mIdIn, where), DateTime.MinValue));
}
else
{
return(new Conditions.SCConditionCollection());
}
}
/// <summary>
/// 根据对象拼Insert语句
/// </summary>
/// <typeparam name="T">对象类型</typeparam>
/// <param name="graph">对象</param>
/// <param name="mapping">映射关系</param>
/// <param name="builder">生成Sql语句类型的Builder如TSqlBuilder或PlSqlBuilder</param>
/// <param name="ignoreProperties">忽略的字段</param>
/// <returns>根据传入的对象和对象映射时需要忽略的字段以及类定义上的表名，生成完整的Insert语句</returns>
public static string GetUpdateSql <T>(T graph, ORMappingItemCollection mapping, ISqlBuilder builder, params string[] ignoreProperties)
{
ExceptionHelper.FalseThrow <ArgumentNullException>(graph != null, "graph");
ExceptionHelper.FalseThrow <ArgumentNullException>(mapping != null, "mapping");
ExceptionHelper.FalseThrow <ArgumentNullException>(builder != null, "builder");
using (ORMappingContext context = ORMappingContext.GetContext())
{
UpdateSqlClauseBuilder updateBuilder = GetUpdateSqlClauseBuilder(graph, mapping, ignoreProperties);
WhereSqlClauseBuilder whereBuilder = GetWhereSqlClauseBuilderByPrimaryKey(graph, mapping);
return(string.Format("UPDATE {0} SET {1} WHERE {2}",
mapping.TableName,
updateBuilder.ToSqlString(builder),
whereBuilder.ToSqlString(builder)));
}
}
private FixedTimeTaskCollection InnerQueryBatch(TimeSpan timeScope, int batchCount)
{
string batchClause = batchCount >= 0 ? "TOP " + batchCount : string.Empty;
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("START_TIME", string.Format("DATEADD(second, -{0}, GETUTCDATE())", (int)timeScope.TotalSeconds), ">=", true);
builder.AppendItem("START_TIME", string.Format("DATEADD(second, {0}, GETUTCDATE())", (int)timeScope.TotalSeconds), "<", true);
builder.AppendTenantCode();
string sql = string.Format("SELECT {0} * FROM {1} WITH(UPDLOCK, READPAST) WHERE {2} ORDER BY SORT_ID ASC",
batchClause,
this.GetTableName(),
builder.ToSqlString(TSqlBuilder.Instance));
return(this.QueryData(sql));
}
/// <summary>
/// 得到字符串的拼音
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public List <string> GetPinYin(string data)
{
data = data.Trim();
StringBuilder select = new StringBuilder(64);
StringBuilder from = new StringBuilder(64);
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
OrderBySqlClauseBuilder orderByBuilder = new OrderBySqlClauseBuilder();
for (int i = 0; i < data.Length; i++)
{
char ch = data[i];
if (ch < ' ' || ch > 'z')
{
string strAlias = "P" + i.ToString();
select.AppendWithSplitChars(strAlias + ".PinYin", ", ");
from.AppendWithSplitChars("SC.PinYin " + strAlias, ", ");
wBuilder.AppendItem(strAlias + ".HZ", ch.ToString());
orderByBuilder.AppendItem(strAlias + ".Weight", FieldSortDirection.Ascending);
}
}
List <string> result = new List <string>();
if (select.Length > 0)
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3}",
select.ToString(), from.ToString(),
wBuilder.ToSqlString(TSqlBuilder.Instance),
orderByBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
result.Add(DataRowToPinYin(data, row));
}
}
else
{
result.Add(DataRowToPinYin(data, null));
}
return(result);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.SchemaOrganizationSnapshot_Current O INNER JOIN SC.SchemaRelationObjectsSnapshot_Current R ON O.ID = R.ObjectID" : "SC.SchemaOrganizationSnapshot O INNER JOIN SC.SchemaRelationObjectsSnapshot R ON O.ID = R.ObjectID";
if (this.fileterRole)
{
qc.FromClause += TimePointContext.Current.UseCurrentTime ?
" INNER JOIN SC.Acl_Current AC ON AC.ContainerID = O.ID INNER JOIN SC.SchemaRoleSnapshot_Current RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot_Current UC ON RS.ID = UC.ContainerID "
: " INNER JOIN SC.Acl AC ON AC.ContainerID = O.ID INNER JOIN SC.SchemaRoleSnapshot RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot UC ON RS.ID = UC.ContainerID ";
}
qc.SelectFields = "DISTINCT O.*,R.FullPath";
if (string.IsNullOrEmpty(qc.OrderByClause))
{
qc.OrderByClause = "R.InnerSort ASC";
}
qc.WhereClause.IsNotEmpty((s) => qc.WhereClause += " AND ");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("O.Status", (int)SchemaObjectStatus.Normal);
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
var schemaCondition = DataSourceUtil.SchemaTypeCondition("R.ChildSchemaType", "Organizations");
var allConditions = new ConnectiveSqlClauseCollection(builder, timeCondition, timeCondition2, schemaCondition);
if (this.fileterRole && this.userID != null && this.permissions != null)
{
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("AC."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("RS."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("UC."));
builder.AppendItem("AC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("RS.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.UserID", this.userID);
InSqlClauseBuilder inSql1 = new InSqlClauseBuilder("AC.ContainerPermission");
inSql1.AppendItem(this.permissions);
allConditions.Add(inSql1);
}
qc.WhereClause += allConditions.ToSqlString(TSqlBuilder.Instance);
}
private String ConvertCommonExpToCommon(String yearMonth, String feeID)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem(FeeMonthInfoDBConst.FeeID, feeID);
FeeInfo feeInfo = FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder).FirstOrDefault();
if (feeInfo.FeeType.Equals(FeeType.Common))
{
if (!String.IsNullOrEmpty(feeInfo.CalculateExp))
{
return(String.Concat("(", this.ConvertCommonExp(yearMonth, feeInfo.CalculateExp), ")"));
}
return("[" + feeID + "]");
}
return(feeInfo.CalculateExp);
}
protected override string GetUpdateSql(WfDelegation data, ORMappingItemCollection mappings, Dictionary <string, object> context, string[] ignoreProperties)
{
UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings, "ApplicationName", "ProgramName");
uBuilder.AppendItem("APPLICATION_NAME", data.ApplicationName.IsNotEmpty() ? data.ApplicationName : string.Empty);
uBuilder.AppendItem("PROGRAM_NAME", data.ProgramName.IsNotEmpty() ? data.ProgramName : string.Empty);
WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings, "ApplicationName", "ProgramName");
wBuilder.AppendItem("APPLICATION_NAME", data.ApplicationName.IsNotEmpty() ? data.ApplicationName : string.Empty);
wBuilder.AppendItem("PROGRAM_NAME", data.ProgramName.IsNotEmpty() ? data.ProgramName : string.Empty);
return(string.Format("UPDATE {0} SET {1} WHERE {2}",
mappings.TableName,
uBuilder.ToSqlString(TSqlBuilder.Instance),
wBuilder.ToSqlString(TSqlBuilder.Instance)));
}
public SchemaObjectCollection LoadAUScope(string auID, string scope, bool normalRelation, bool normalObject, DateTime timePoint)
{
auID.NullCheck("auID");
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder().NormalFor("O.Status").NormalFor("S.Status");
where.AppendCondition("R.ContainerID", auID);
where.AppendCondition("S.ScopeSchemaType", scope);
if (normalRelation)
where.NormalFor("R.Status");
if (normalObject)
{
where.NormalFor("O.Status");
}
return LoadAUScope(where, timePoint);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
base.OnBuildQueryCondition(qc);
WhereSqlClauseBuilder wb = new WhereSqlClauseBuilder();
wb.AppendItem("SynchronizeID", this.syncID);
if (string.IsNullOrEmpty(qc.WhereClause))
{
qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance);
}
else
{
qc.WhereClause = " AND " + wb.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));
}
protected void ObjectDataSourceSelecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
if (e.ExecutingSelectCount == false)
{
e.InputParameters["authType"] = (WfApplicationAuthType)int.Parse(this.rdoList.SelectedValue);
if (views.ActiveViewIndex == 1)
{
if (string.IsNullOrEmpty(this.lastUser.Value))
{
e.Cancel = true;
}
else
{
e.InputParameters["appName"] = null;
e.InputParameters["programName"] = null;
e.InputParameters["authType"] = WfApplicationAuthType.None;
WfApplicationAuthCollection authInfo = WfApplicationAuthAdapter.Instance.GetUserApplicationAuthInfo(new OguUser(lastUser.Value));
string condition = "1=2";
if (authInfo.Count > 0)
{
ConnectiveSqlClauseCollection allMatch = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or);
foreach (var item in authInfo)
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("APPLICATION_NAME", item.ApplicationName);
where.AppendItem("PROGRAM_NAME", item.ProgramName);
where.AppendItem("AUTH_TYPE", item.AuthType);
allMatch.Add(where);
}
condition = allMatch.ToSqlString(TSqlBuilder.Instance);
}
else
{
e.Cancel = true;
}
e.InputParameters["where"] = condition;
}
}
}
}
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()));
}
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);
}
/// <summary>
/// 序列化
/// </summary>
/// <param name="obj"></param>
/// <param name="serializer"></param>
/// <returns></returns>
public override IDictionary <string, object> Serialize(object obj, JavaScriptSerializer serializer)
{
WhereSqlClauseBuilder whereBuilder = (WhereSqlClauseBuilder)obj;
Dictionary <string, object> result = new Dictionary <string, object>();
result.AddNonDefaultValue("LogicOperator", whereBuilder.LogicOperator);
var itemsList = new ArrayList();
foreach (var item in whereBuilder)
{
itemsList.Add(item);
}
result["List"] = itemsList;
return(result);
}
public override void UpdateOperation()
{
DataView ouusers = new DataView(this.Context.OriginalData.Tables["OU_USERS"], string.Empty, "PARENT_GUID,USER_GUID", DataViewRowState.ModifiedCurrent);
Database db = DatabaseFactory.Create(this.Context.InitialParams.AccreditAdminConnectionName);
foreach (DataRowView drs in ouusers)
{
UpdateSqlClauseBuilder uBuilder = new UpdateSqlClauseBuilder();
uBuilder.AppendItem("DISPLAY_NAME", drs["DISPLAY_NAME"].ToString());
uBuilder.AppendItem("OBJ_NAME", drs["OBJ_NAME"].ToString());
uBuilder.AppendItem("ALL_PATH_NAME", drs["ALL_PATH_NAME"].ToString());
uBuilder.AppendItem("INNER_SORT", drs["INNER_SORT"].ToString());
uBuilder.AppendItem("GLOBAL_SORT", drs["GLOBAL_SORT"].ToString());
uBuilder.AppendItem("ORIGINAL_SORT", drs["ORIGINAL_SORT"].ToString());
uBuilder.AppendItem("SEARCH_NAME", drs["SEARCH_NAME"].ToString());
uBuilder.AppendItem("MODIFY_TIME", TSqlBuilder.Instance.DBCurrentTimeFunction, "=", true);
uBuilder.AppendItem("STATUS", drs["STATUS"].ToString());//by majun 20120216
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
wBuilder.AppendItem("PARENT_GUID", drs["PARENT_GUID"].ToString());
wBuilder.AppendItem("USER_GUID", drs["USER_GUID"].ToString());
//wBuilder.AppendItem("STATUS", drs["STATUS"].ToString());//by majun 20120216
string updatesql = string.Format("UPDATE OU_USERS SET {0} where {1}", uBuilder.ToSqlString(TSqlBuilder.Instance), wBuilder.ToSqlString(TSqlBuilder.Instance));
try
{
int count = db.ExecuteNonQuery(CommandType.Text, updatesql);
if (count == 1)
{
this.UpdateCount++;
//this.Context.InitialParams.Log.Write(string.Format("OU_USERS表 PARENT_GUID是{0},USER_GUID是{1}的记录更新成功，ALL_PATH_NAME是'{2}'",
//drs["PARENT_GUID"].ToString(), drs["USER_GUID"].ToString(), drs["ALL_PATH_NAME"]).ToString());
}
}
catch (Exception ex)
{
this.Context.InitialParams.Log.Write(string.Format("OU_USERS表 PARENT_GUID是{0}USER_GUID 是{1}的记录执行更新时出错,错误是{2}",
drs["PARENT_GUID"].ToString(), drs["USER_GUID"].ToString(), ex.Message));
}
}
}
private static ConnectiveSqlClauseCollection GetConnectiveSqlClauseBuilderFromMapping(
object condition,
ConditionMappingItemCollection mapping,
bool ignoreDefaultValue,
AdjustConditionValueDelegate acv,
string[] ignoreProperties)
{
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection();
WhereSqlClauseBuilder whereBuilder = GetWhereSqlClauseBuilderFromMapping(condition, mapping.FilterByType <ConditionMappingItem>(), ignoreDefaultValue, acv, ignoreProperties);
connectiveBuilder.Add(whereBuilder);
FillInSqlClauseBuilderFromMapping(connectiveBuilder, condition, mapping.FilterByType <InConditionMappingItem>(), ignoreDefaultValue, acv, ignoreProperties);
return(connectiveBuilder);
}
internal static bool ValidateCodeNameWithContainer(string codeName, string[] schemaNames, string objectID, string parentID, bool normalOnly, bool ignoreVersions, DateTime timePoint)
{
var objects = SchemaObjectAdapter.Instance.LoadByCodeNameAndSchema(schemaNames.ToArray(), new string[] { codeName }, true, false, DateTime.MinValue);
bool result = false;
if (objects.Count == 0)
{
result = true;
}
else
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("ContainerID", parentID);
if (normalOnly)
{
where.AppendItem("Status", (int)SchemaObjectStatus.Normal);
}
InSqlClauseBuilder inSql = new InSqlClauseBuilder("MemberID");
inSql.AppendItem((from o in objects select o.ID).ToArray());
// 只取当前关系
var relations = SCMemberRelationAdapter.Instance.Load(new ConnectiveSqlClauseCollection(where, inSql), timePoint);
bool exist = false;
foreach (string schemaName in schemaNames)
{
foreach (SCMemberRelation o in relations)
{
if (o.ID != objectID && o.MemberSchemaType == schemaName)
{
exist = true;
break;
}
}
}
result = exist == false;
}
return(result);
}
private void ExecuteQuery()
{
LastQueryRowCount = -1;
var whereCondition = (HtmlInputHidden)this.FindControlByID("whereCondition", true);
var activityID = this.ActivityID;
if (activityID.IsNotEmpty())
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem <string>("OWNER_ACTIVITY_ID", activityID);
whereCondition.Value = builder.ToSqlString(TSqlBuilder.Instance);
}
LastQueryRowCount = -1;
this.processGrid.SelectedKeys.Clear();
this.processGrid.PageIndex = 0;
}
public SchemaObjectCollection LoadScopeItems(string scopeType, bool normalOnly, DateTime timePoint)
{
string sql = "SELECT * FROM SC.SchemaObject WHERE ";
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
if (normalOnly)
{
where.NormalFor("Status");
}
where.AppendItem("SchemaType", scopeType);
var timeBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
sql += new ConnectiveSqlClauseCollection(timeBuilder, where).ToSqlString(TSqlBuilder.Instance);
return(LoadSchemaObjects(sql));
}
/// <summary>
/// 根据下级名称，载入指定的管理单元
/// </summary>
/// <param name="conditions">条件集,O对象表，M关系表，S对象的快照表</param>
/// <param name="snapshotTable">快照表的名称</param>
/// <param name="normalOnly">是否仅包含正常对象</param>
/// <param name="timePoint">时间点</param>
/// <returns></returns>
private SchemaObjectCollection LoadContainers(IConnectiveSqlClause conditions, bool normalOnly, DateTime timePoint)
{
string sql = @"SELECT O.* FROM SC.SchemaObject O
INNER JOIN SC.SchemaMembers M ON O.ID = M.ContainerID AND O.SchemaType = M.ContainerSchemaType
WHERE 1=1 AND ";
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "O.");
var timeCondition3 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "M.");
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
if (normalOnly)
where.NormalFor("M.Status").NormalFor("O.Status");
sql += new ConnectiveSqlClauseCollection(timeCondition2, timeCondition3, where, conditions).ToSqlString(TSqlBuilder.Instance);
return LoadSchemaObjects(sql);
}
internal static SCRelationObject GetParentOURelation(string ouId)
{
if (ouId == PC.SCOrganization.RootOrganizationID)
{
return(null);
}
else
{
WhereSqlClauseBuilder wb = new WhereSqlClauseBuilder();
wb.AppendItem("Status", (int)SchemaObjectStatus.Normal);
wb.AppendItem("ParentSchemaType", "Organizations");
wb.AppendItem("ChildSchemaType", "Organizations");
wb.AppendItem("ObjectID", ouId);
return(PC.Adapters.SchemaRelationObjectAdapter.Instance.Load(wb, DateTime.MinValue).FirstOrDefault());
}
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.SelectFields = "*";
if (string.IsNullOrEmpty(qc.OrderByClause))
{
qc.OrderByClause = "VersionStartTime DESC";
}
qc.WhereClause.IsNotEmpty((s) => qc.WhereClause += " AND ");
var builder = new WhereSqlClauseBuilder();
builder.AppendItem("Status", (int)SchemaObjectStatus.Normal);
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder();
qc.WhereClause += new ConnectiveSqlClauseCollection(builder, timeCondition).ToSqlString(TSqlBuilder.Instance);
}
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
//if (this.AdvanceSearchEnabled)
{
var condition = this.CurrentAdvancedSearchCondition;
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(condition);
this.dataSourceMain.Condition = new ConnectiveSqlClauseCollection(builder, this.DeluxeSearch.GetCondition());
}
//else
//{
// this.dataSourceMain.Condition = this.DeluxeSearch.GetCondition();
//}
e.InputParameters["bossMode"] = this.Request.QueryString["view"] == "boss";
}
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>
/// 查询用户所属的角色
/// </summary>
/// <param name="schemaTypes"></param>
/// <param name="appCodeName"></param>
/// <param name="userIDs"></param>
/// <param name="includingDeleted"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SchemaObjectCollection QueryUserBelongToRoles(string[] schemaTypes, string appCodeName, string[] userIDs, bool includingDeleted, DateTime timePoint)
{
SchemaObjectCollection result = new SchemaObjectCollection();
InSqlClauseBuilder userBuilder = new InSqlClauseBuilder("UC.UserID");
userBuilder.AppendItem(userIDs);
if (userBuilder.IsEmpty == false)
{
InSqlClauseBuilder schemaBuilder = new InSqlClauseBuilder("MA.MemberSchemaType");
schemaBuilder.AppendItem(schemaTypes);
WhereSqlClauseBuilder appBuilder = new WhereSqlClauseBuilder();
if (appCodeName.IsNotEmpty())
{
appBuilder.AppendItem("A.CodeName", appCodeName);
}
IConnectiveSqlClause extraBuilder = CreateStatusAndTimePointBuilder(includingDeleted, timePoint,
"SC.", "A.", "MA.", "UC.");
string resourcePath = "QueryUserBelongToRoles_Current.sql";
if (timePoint != DateTime.MinValue && includingDeleted == true)
{
resourcePath = "QueryUserBelongToRoles.sql";
}
string sqlTemplate = ResourceHelper.LoadStringFromResource(Assembly.GetExecutingAssembly(), string.Concat("MCS.Library.SOA.DataObjects.Security.Adapters.Templates.", resourcePath));
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(userBuilder, schemaBuilder, appBuilder, extraBuilder);
string sql = string.Format(sqlTemplate, connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView, (row, obj) =>
{
obj.Tag = row["AppID"].ToString();
});
}
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);
}
/// <summary>
/// 通过实体的CodeName加载实体字段对象集合
/// </summary>
/// <param name="codeName">实体的CodeName</param>
/// <returns>该CodeName对应的实体字段对象集合</returns>
public List <DynamicEntityField> LoadByRefferanceCodeName(string codeName)
{
List <DynamicEntityField> entityFields = new List <DynamicEntityField>();
string.IsNullOrEmpty(codeName).TrueThrow("codeName不得为空");
InSqlClauseBuilder inbuilder = new InSqlClauseBuilder("ReferenceEntityCodeName");
inbuilder.AppendItem(codeName);
WhereSqlClauseBuilder wherebuilder = new WhereSqlClauseBuilder();
wherebuilder.AppendItem("Status", 1);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And);
connectiveBuilder.Add(inbuilder);
connectiveBuilder.Add(wherebuilder);
var timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder();
connectiveBuilder.Add(timePointBuilder);
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"SELECT ID FROM {0} WHERE {1}",
TableName,
connectiveBuilder.ToSqlString(TSqlBuilder.Instance)
);
DataSet fieldIDs = DbHelper.RunSqlReturnDS(sql.ToString(), this.GetConnectionName());
if (fieldIDs.Tables.Count != 0)
{
foreach (DataRow item in fieldIDs.Tables[0].Rows)
{
var id = item[0].ToString();
var dbResult = DESchemaObjectAdapter.Instance.Load(id) as DynamicEntityField;
entityFields.Add(dbResult);
}
}
return(entityFields);
}
protected override void AfterInnerDelete(WfMatrixDefinition data, Dictionary <string, object> context)
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("MATRIX_DEF_KEY", data.Key);
builder.AppendTenantCode(typeof(WfMatrixDimensionDefinition));
string sql = string.Format("{0} {1}",
DELETE_DIMENSION_SQL_CLAUSE,
builder.ToSqlString(TSqlBuilder.Instance));
DbHelper.RunSql(sql, GetConnectionName());
CacheNotifyData notifyData = new CacheNotifyData(typeof(WfMatrixDefinitionCache), CalculateCacheKey(data.Key), CacheNotifyType.Invalid);
UdpCacheNotifier.Instance.SendNotifyAsync(notifyData);
MmfCacheNotifier.Instance.SendNotify(notifyData);
}
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);
}
