public void Test_InternalOr()
{
ConditionBuilder builder = new ConditionBuilder();
builder.And(x =>
{
x.Or(y =>
{
y.Equal("C.MemberId", "007")
.In("C.CheckFlag", new[] { 3, 4 })
.In("C.Flag", new[] { 1, 2 });
})
.Or(y =>
{
y.NotEqual("C.MemberId", "007")
.Equal("C.CheckFlag", 4)
.Equal("C.Flag", 2);
});
});
var result = builder.ToString();
var param = builder.GetParamDict().ToJson();
Console.WriteLine(result);
Console.WriteLine(param);
}
/// <summary>
/// 获取父节点列表Sql语句
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="fieldName">字段名</param>
/// <param name="fieldValue">字段值</param>
/// <param name="order">排序</param>
/// <param name="idOnly">是否只需要主键</param>
/// <param name="conditionBuilder">条件生成器</param>
/// <returns></returns>
public string GetParentsByCode(string tableName, string fieldName, string fieldValue, string order, bool idOnly = false,
Action <IConditionBuilder> conditionBuilder = null)
{
StringBuilder sb = new StringBuilder();
if (idOnly)
{
sb.AppendFormat("select {0} ", SqlBusinessLogicOptions.FieldId);
}
else
{
sb.Append("select * ");
}
sb.AppendFormat(" from {0} ", tableName);
ConditionBuilder builder = new ConditionBuilder();
builder.AppendRaw("(LEFT({0}, LEN({1})) = {1}) ", builder.AddParameter(fieldName, fieldValue), fieldName);
conditionBuilder?.Invoke(builder);
sb.Append(builder.ToString());
_currentParamDict = builder.GetParamDict();
sb.AppendFormat(" order by {0}", order);
return(sb.ToString());
}
public void RefreshDisplayGrid( )
{
DisplayGridView.TableName = this.TableName;
DisplayGridView.Columns.Clear();
DisplayGridView.ColumnConfigs = this.ColumnList;
DisplayGridView.InitColumns();
#region Script
if (String.IsNullOrWhiteSpace(this.TableName))
{
if (String.IsNullOrWhiteSpace(Script) == false)
{
DataSet ds = ConnectionManager.DatabaseHelper.RunQuery(Script);
if (ds != null && ds.Tables.Count > 0)
{
this.DisplayGridCtrl.DataSource = ds.Tables[0];
this.DisplayGridCtrl.RefreshDataSource();
DisplayGridView.ShowCustomization();
}
}
return;
}
#endregion
if (ABCDataLib.Tables.CachingProvider.LookupTables.ContainsKey(this.TableName))
{
this.DisplayGridCtrl.DataSource = ABCDataLib.Tables.CachingProvider.LookupTables[this.TableName];
}
else
{
ConditionBuilder strBuilder = new ConditionBuilder();
strBuilder.Append(String.Format(@"SELECT TOP 5 * FROM {0} ", this.TableName));
if (ABCDataLib.Tables.StructureProvider.IsExistABCStatus(this.TableName))
{
strBuilder.AddCondition(ABCDataLib.Generation.QueryGenerator.GenerateCondition(this.TableName, DataDefine.ColumnType.ABCStatus));
}
strBuilder.Append(String.Format(@" ORDER BY {0} DESC", ABCDataLib.Tables.StructureProvider.GetPrimaryKeyColumn(this.TableName)));
try
{
DataSet ds = ConnectionManager.DatabaseHelper.RunQuery(strBuilder.ToString());
if (ds != null && ds.Tables.Count > 0)
{
this.DisplayGridCtrl.DataSource = ds.Tables[0];
}
}
catch (Exception ex)
{
}
}
this.DisplayGridCtrl.RefreshDataSource();
DisplayGridView.ShowCustomization();
}
public void Test_AppendRaw()
{
ConditionBuilder builder = new ConditionBuilder();
builder.AppendRaw("A.CreateTime like '%123456%'").AppendRaw("A.CreateTime like '%123456%'");
var result = builder.ToString();
var param = builder.GetParamDict().ToJson();
Console.WriteLine(result);
Console.WriteLine(param);
}
public void Test_CustomParam()
{
ConditionBuilder builder = new ConditionBuilder();
builder.AppendRaw("(LEFT({0}, LEN({1})) = {1}",
builder.AddParameter("ParentID", "BB2A1B4A-5E9E-33D5-697B-39E37974ACC4"), "ParentID")
.Equal("IsDeleted", 1).Equal("Status", 1);
var result = builder.ToString();
var param = builder.GetParamDict().ToJson();
Console.WriteLine(result);
Console.WriteLine(param);
}
public void Test_Barcode()
{
ConditionBuilder builder = new ConditionBuilder();
builder.Equal("A.MerchantID", Guid.NewGuid())
.Contains("A.Barcode", "123456")
.Contains("B.CustomBC", "111100")
.Contains("C.Name", "Test")
.Contains("A.Editor", "TT");
var result = builder.ToString();
var param = builder.GetParamDict().ToJson();
Console.WriteLine(result);
Console.WriteLine(param);
}
public void Test_ChildIn()
{
ConditionBuilder builder = new ConditionBuilder();
builder.In("A.ID", "select Id from User");
builder.In("A.ID", new[] { 1, 2, 3, 4, 56, 7 });
builder.And(x =>
{
x.Contains("A.Name", "测试");
});
var result = builder.ToString();
var param = builder.GetParamDict().ToJson();
Console.WriteLine(result);
Console.WriteLine(param);
}
public void Test_Or()
{
ConditionBuilder builder = new ConditionBuilder();
builder.Equal("A.MerchantID", Guid.NewGuid())
.OrEqual("B.MerchantID", Guid.NewGuid())
.Equal("C.ID", Guid.Empty)
.And(c =>
{
c.Contains("B.Name", "测试用户")
.Contains("C.Name", "007");
});
var result = builder.ToString();
var param = builder.GetParamDict().ToJson();
Console.WriteLine(result);
Console.WriteLine(param);
}
public void Test_Block()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
ConditionBuilder builder = new ConditionBuilder();
builder.Append("A.CreateTime", SqlOperator.Equal, DateTime.Now);
builder.Between("B.CreateTime", DateTime.Now, DateTime.Now.AddDays(1));
//builder.Block(RelationType.And, childBuilder);
builder.Or(child =>
{
child.Equal("D.CreateTime", DateTime.Now);
child.Equal("E.CreateTime", DateTime.Now);
child.Equal("E.CreateTime", DateTime.Now);
child.And(c2 =>
{
c2.Append("HH.CreateTime", SqlOperator.Equal, 5);
});
}).Or(child =>
{
child.Append(RelationType.And, "E.CreateTime", SqlOperator.Equal, DateTime.Now);
});
builder.Append(RelationType.And, "F.CreateTime", SqlOperator.Equal, DateTime.Now);
builder.Append(RelationType.And, "A.ID", SqlOperator.In, new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 });
builder.Append(RelationType.And, "B.ID", SqlOperator.In, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
builder.AppendRaw("E.CreateTime like '%123456%'");
builder.Between("H.CreateTime", DateTime.Now, DateTime.Now.AddDays(5));
var result = builder.ToString();
stopwatch.Stop();
var param = builder.GetParamDict().ToJson();
Console.WriteLine(result);
Console.WriteLine(param);
Console.WriteLine(stopwatch.Elapsed.Milliseconds);
}
/// <summary>
/// 获取子节点列表Sql语句
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkFieldName">主键字段名</param>
/// <param name="pkFieldValues">主键字段值数组</param>
/// <param name="parentFieldName">父节点字段名</param>
/// <param name="order">排序</param>
/// <param name="idOnly">是否只需要主键</param>
/// <param name="conditionBuilder">条件生成器</param>
/// <returns></returns>
public string GetChildrens(string tableName, string pkFieldName, string[] pkFieldValues, string parentFieldName, string order,
bool idOnly = false, Action <IConditionBuilder> conditionBuilder = null)
{
StringBuilder sb = new StringBuilder();
ConditionBuilder builder = new ConditionBuilder();
builder.In(pkFieldName, pkFieldValues);
conditionBuilder?.Invoke(builder);
sb.AppendFormat(
@"with Tree as (select {0} from {1} {2} union all select ResourceTree.{0} from {1} as ResourceTree inner join Tree as A on A.{3} = ResourceTree.{4}) select {0} from Tree",
idOnly ? pkFieldName : "*", tableName, builder.ToString(), pkFieldName, parentFieldName);
_currentParamDict = builder.GetParamDict();
sb.AppendFormat(" order by {0} ", order);
return(sb.ToString());
}
public string GetConditions() => ConditionBuilder.ToString();
public void Search( )
{
if (BindingObject != null && GridCtrl != null)
{
if (BindingObject.DataManager.MainObject == null)
{
return;
}
BusinessObject mainObj = BindingObject.DataManager.MainObject.DataObject as BusinessObject;
BusinessObjectController ctrl = BusinessControllerFactory.GetBusinessController(mainObj.AATableName);
ConditionBuilder strBuilder = BindingObject.GenerateQuery(true);
if (strBuilder == null || String.IsNullOrWhiteSpace(strBuilder.ToString()))
{
return;
}
if (SearchPanel != null)
{
SearchPanel.GetSearchQuery(strBuilder, SearchPanel);
}
if (BindingObject.DataManager.Screen.UIManager.View.DataField != null)
{
GEVouchersInfo config = VoucherProvider.GetConfig(BindingObject.TableName, BindingObject.DataManager.Screen.UIManager.View.DataField.STViewNo);
if (config != null && !String.IsNullOrWhiteSpace(config.ConditionString))
{
strBuilder.AddCondition(config.ConditionString);
}
}
if (DataStructureProvider.IsTableColumn(BindingObject.TableName, ABCCommon.ABCConstString.colApprovalStatus))
{
if (chkNotYetApproved.Checked)
{
strBuilder.AddCondition(String.Format("{0}<>'{1}'", ABCCommon.ABCConstString.colApprovalStatus, ABCCommon.ABCConstString.ApprovalTypeApproved));
}
}
if (DataStructureProvider.IsTableColumn(BindingObject.TableName, ABCCommon.ABCConstString.colLockStatus))
{
if (chkLocked.Checked)
{
strBuilder.AddCondition(String.Format("{0}='{1}'", ABCCommon.ABCConstString.colLockStatus, ABCCommon.ABCConstString.LockStatusLocked));
}
}
if (DataStructureProvider.IsTableColumn(BindingObject.TableName, ABCCommon.ABCConstString.colJournalStatus))
{
if (chkNotYetPosted.Checked)
{
strBuilder.AddCondition(String.Format("{0}<>'{1}'", ABCCommon.ABCConstString.colJournalStatus, ABCCommon.ABCConstString.PostStatusPosted));
}
}
object datasource = null;
Type objType = BusinessObjectFactory.GetBusinessObjectType(GridCtrl.TableName);
if (objType != null)
{
Type typeABCList = typeof(ABCList <>).MakeGenericType(objType);
MethodInfo method;
if (typeABCList != null)
{
datasource = ABCBusinessEntities.ABCDynamicInvoker.CreateInstanceObject(typeABCList);
method = typeABCList.GetMethod("LoadData", new Type[] { typeof(List <BusinessObject>) });
method.Invoke(datasource, new object[] { ctrl.GetListByQuery(strBuilder.ToString()) });
}
}
binding.DataSource = datasource;
binding.ResetBindings(false);
this.GridCtrl.GridDefaultView.BestFitColumns();
for (int i = 0; i < this.GridCtrl.GridDefaultView.DataRowCount; i++)
{
BusinessObject obj = this.GridCtrl.GridDefaultView.GetRow(i) as BusinessObject;
if (obj != null && obj.GetID() == mainObj.GetID())
{
this.GridCtrl.GridDefaultView.FocusedRowHandle = i;
return;
}
}
}
}