public void Should_return_empty_if_orderBy_is_null()
{
_orderBy.Add(null);
string result = _builder.ToString();
result.ShouldBeEqualTo("");
}
public void Add_with_index_adds_orderby()
{
OrderBy orderBy = new OrderBy("foo asc");
orderBy.Add("bar desc");
orderBy.Add(0, "fooBar desc");
orderBy.Sql().MustBe("order by fooBar desc,foo asc,bar desc");
}
public List <OrderBy <Activity> > DynamicOrderBy(string orderBy, int?EntityId = null)
{
var OrderByList = new List <OrderBy <Activity> >();
if (!string.IsNullOrEmpty(orderBy))
{
List <string> orders = orderBy.Split(',').ToList();
orders.ForEach(x =>
{
if (x.Contains("["))
{
var ComplexOrder = x.Split(' ').ToList();
switch (ComplexOrder[0].Trim())
{
case "[Reviews.Rate]":
OrderByList.Add(OrderBy <Activity> .Add(y => y.Reviews.Where(z => !z.IsDeleted).OrderBy(z => z.Rate).Select(z => z.Rate).Sum(), ComplexOrder.Count > 1 ? ComplexOrder[1].DecodeDirection() : OrderDirection.ASC));
break;
}
}
else
{
OrderByList.Add(OrderBy <Activity> .Add(x));
}
});
}
return(OrderByList);
}
public Part AddOrderBy(string value, string name)
{
var part = new Part(value, name);
OrderBy.Add(part);
return(part);
}
public static void ConstructTableObjectFromPostRequest(JSONTable jt, ref BaseTable bt, ref int pageIndex, ref int pageSize, ref int totalRows,
ref SqlFilter whereFilter, ref BaseFilter join, ref OrderBy orderBy)
{
pageIndex = jt.PageIndex;
pageSize = jt.PageSize;
totalRows = jt.TotalRows;
bt = (BaseTable)DatabaseObjects.GetTableObject(jt.TableName);
ColumnList selCols = new ColumnList();
foreach (JTableSelectColumn col in jt.JSelectColumns)
{
selCols.Add(bt.TableDefinition.ColumnList.GetByCodeName(col.ColumnName), true);
}
bt.SelectedColumns.Clear();
bt.SelectedColumns.AddRange(selCols);
if ((jt.JOrderByList != null))
{
foreach (JOrderBy jOrderBy in jt.JOrderByList)
{
orderBy = new OrderBy(true, false);
orderBy.Add(bt.TableDefinition.ColumnList.GetByCodeName(jOrderBy.ColumnName), OrderByItem.ToOrderDir(jOrderBy.OrderDirection));
}
}
if (jt.JWhereClause != null && jt.JWhereClause.Trim() != "")
{
whereFilter = new SqlFilter(jt.JWhereClause);
}
}
public void CommonUse()
{
// some times the select can be static in son place (the service of a DAO)
Select s = new Select("f.Name, f.Description, b.Descriptoin").From("Foo f join f.Bar b");
// Create a new where by checking some contition
Where where = new Where();
// check something and set a condition of where clause
where.And("f.Name like :pName");
// check something else and set another condition of where clause
where.And("b.Asociated > :pAso");
// Inject the where to the select
s.From().SetWhere(where);
// Create a basic order by
OrderBy order = new OrderBy().Add("b.Asociated", true);
// Check some condition and add an order
order.Add("f.Name");
// Inject the OrderBy to the select
s.From().SetOrderBy(order);
s.From().OrderBy("b.Other");
// And: The winner is....
string expected = "select f.Name, f.Description, b.Descriptoin from Foo f join f.Bar b where ((f.Name like :pName) and (b.Asociated > :pAso)) order by b.Asociated desc, f.Name, b.Other";
Assert.AreEqual(expected, s.Clause);
}
public PageView(NameValueCollection nvc)
{
if (!string.IsNullOrEmpty(nvc["page"]))
{
PageIndex = Convert.ToInt32(nvc["page"]) - 1;
}
else
{
PageIndex = 0;
}
PageSize = !string.IsNullOrEmpty(nvc["rp"]) ? Convert.ToInt32(nvc["rp"]) : 35;
if (!string.IsNullOrEmpty(nvc["sortname"]) && nvc["sortname"] != "undefined")
{
OrderByType type = nvc["sortorder"] == "desc" ? OrderByType.Desc : OrderByType.Asc;
OrderBy.Add(nvc["sortname"], type);
}
if (!string.IsNullOrEmpty(nvc["qtype"]))
{
QuickLuanch = new QuickLuanch
{
FieldName = nvc["qtype"],
Operator = (nvc["qop"] == "Eq" ? Operator.Eq : Operator.Like),
Value = nvc["query"]
};
}
}
public void Add_adds_orderby()
{
OrderBy orderBy = new OrderBy("foo asc");
orderBy.Add("bar desc");
orderBy.Sql().MustBe("order by foo asc,bar desc");
}
public void AddOrderBy(string key, string column)
{
if (OrderBy == null)
{
OrderBy = new Dictionary <string, string>();
}
OrderBy.Add(key, column);
}
public CargoQueryExpression ThenOrderBy(Expression expression, bool descending = false)
{
Debug.Assert(expression != null);
var newInst = (CargoQueryExpression)MemberwiseClone();
newInst.OrderBy = OrderBy.Add(new OrderByExpression(expression, descending));
return(newInst);
}
// GET api/<controller>/5
public DBModel.Person_FaceInfoModel Get(int id)
{
/******************************************使用示例******************************************/
//根据ID查询 Person_FaceInfoModel 对应表的所有记录
DBModel.Person_FaceInfoModel addModel = new DBModel.Person_FaceInfoModel()
{
//给字段赋值
};
//新增一条数据
YEasyModel.ModelDAL.Insert(addModel);
//根据主键ID修改一条数据
YEasyModel.ModelDAL.Update(addModel);
//修改一条数据，指定条件、更新字段
YEasyModel.ModelDAL.Update <DBModel.Person_FaceInfoModel>(addModel, q => q.FaceID == 1);
YEasyModel.ModelDAL.Update <DBModel.Person_FaceInfoModel>(addModel, q => q.FaceID == 1, q => q.FacePath, q => q.Remark);
//根据ID查询 Person_FaceInfoModel 对应表的所有记录
YEasyModel.ModelDAL.GetModel <DBModel.Person_FaceInfoModel>(id);
//查询所有数据
YEasyModel.ModelDAL.Select <DBModel.Person_FaceInfoModel>();
//根据FaceID查询单条记录
YEasyModel.ModelDAL.SelectSingleRecord <DBModel.Person_FaceInfoModel>(q => q.FaceID == id);
//按条件查询数据
YEasyModel.ModelDAL.Select <DBModel.Person_FaceInfoModel>(q => q.Create_Date > DateTime.Now.AddDays(-1) &&
q.Create_Date < DateTime.Now && q.Remark != "");
//按条件查询、指定字段1、字段2...
YEasyModel.ModelDAL.SelectSingleRecord <DBModel.Person_FaceInfoModel>(q => q.FaceID == id, null, q => q.Person_ID, q => q.FacePath);
//创建字段排序规则
var order = new OrderBy <DBModel.Person_FaceInfoModel>();
order.Add(q => q.Create_Date, OrderByEnum.asc);
//查询所有数据+排序
YEasyModel.ModelDAL.Select <DBModel.Person_FaceInfoModel>(null, order, null);
//删除指定ID数据
YEasyModel.ModelDAL.Delete <DBModel.Person_FaceInfoModel>(1);
//删除指定条件的数据
YEasyModel.ModelDAL.Delete <DBModel.Person_FaceInfoModel>(q => q.FacePath == "" && q.Remark == "");
DataTable dt = new DataTable();//查询数据
//DataTable数据转实体列表
YEasyModel.ModelUtil.DataTableParse <DBModel.Person_FaceInfoModel>(dt);
//DataRow数据转实体
YEasyModel.ModelUtil.DataRowParse <DBModel.Person_FaceInfoModel>(dt.Rows[0]);
return(YEasyModel.ModelDAL.Select <DBModel.Person_FaceInfoModel>(q => q.FaceID == id)[0]);//根据FaceID查询记录
}
public void ToRowCount()
{
Select s = new Select("f.Name, f.Description, b.Descriptoin").From("Foo f");
s.From().Join("f.Bar b");
Where where = new Where();
where.And("f.Name like :pName");
where.And("b.Asociated > :pAso");
s.From().SetWhere(where);
OrderBy order = new OrderBy().Add("b.Asociated", true);
order.Add("f.Name");
s.From().SetOrderBy(order);
DetachedDynQuery ddq = new DetachedDynQuery(s);
DetachedQuery drc = (DetachedQuery)ddq.TransformToRowCount();
Assert.AreEqual("select count(*) from Foo f join f.Bar b where ((f.Name like :pName) and (b.Asociated > :pAso))",
drc.Hql);
}
private bool ParseOrderByExpression(MethodCallExpression expression, string order = null)
{
UnaryExpression unary = (UnaryExpression)expression.Arguments[1];
LambdaExpression lambdaExpression = (LambdaExpression)unary.Operand;
if (!string.IsNullOrEmpty(order))
{
order = " " + order.Trim();
}
MemberExpression body = lambdaExpression.Body is MemberExpression ?
(MemberExpression)lambdaExpression.Body : ((UnaryExpression)lambdaExpression.Body).Operand as MemberExpression;
if (body != null)
{
var exp = new List <string>();
exp.Add(body.Member.Name);
while (body.Expression is MemberExpression)
{
body = (MemberExpression)body.Expression;
exp.Insert(0, body.Member.Name);
}
if (OrderBy == null)
{
OrderBy = new List <string>();
}
OrderBy.Add(string.Format("{0}{1}", string.Join(".", exp), order));
return(true);
}
return(false);
}
// Get the filters' data for UOMNameFilter.
protected virtual void PopulateUOMNameFilter(string selectedValue, int maxItems)
{
this.UOMNameFilter.Items.Clear();
// Set up the WHERE and the ORDER BY clause by calling the CreateWhereClause_UOMNameFilter function.
// It is better to customize the where clause there.
// Setup the WHERE clause,
WhereClause wc = this.CreateWhereClause_UOMNameFilter();
// Setup the static list items
// Add the All item.
this.UOMNameFilter.Items.Insert(0, new ListItem(this.Page.GetResourceValue("Txt:All", "FPCEstimate"), "--ANY--"));
OrderBy orderBy = new OrderBy(false, false);
orderBy.Add(UOMTable.UOMName, OrderByItem.OrderDir.Asc);
string[] values = UOMTable.GetValues(UOMTable.UOMName, wc, orderBy, maxItems);
foreach (string itemValue in values)
{
// Create the item and add to the list.
string fvalue;
if ( UOMTable.UOMName.IsColumnValueTypeBoolean()) {
fvalue = itemValue;
}else {
fvalue = UOMTable.UOMName.Format(itemValue);
}
ListItem item = new ListItem(fvalue, itemValue);
this.UOMNameFilter.Items.Add(item);
}
// Set the selected value.
MiscUtils.SetSelectedValue(this.UOMNameFilter, selectedValue);
}
// Fill the UOMID list.
protected virtual void PopulateUOMIDDropDownList(string selectedValue, int maxItems)
{
this.UOMID.Items.Clear();
// 1. Setup the static list items
// Add the Please Select item.
this.UOMID.Items.Insert(0, new ListItem(this.Page.GetResourceValue("Txt:PleaseSelect", "FPCEstimate"), "--PLEASE_SELECT--"));
// 2. Set up the WHERE and the ORDER BY clause by calling the CreateWhereClause_UOMIDDropDownList function.
// It is better to customize the where clause there.
WhereClause wc = CreateWhereClause_UOMIDDropDownList();
// Create the ORDER BY clause to sort based on the displayed value.
OrderBy orderBy = new OrderBy(false, false);
orderBy.Add(UOMTable.UOMName, OrderByItem.OrderDir.Asc);
System.Collections.Generic.IDictionary<string, object> variables = new System.Collections.Generic.Dictionary<string, object> ();
// 3. Read a total of maxItems from the database and insert them into the UOMIDDropDownList.
UOMRecord[] itemValues = null;
if (wc.RunQuery)
{
int counter = 0;
int pageNum = 0;
FormulaEvaluator evaluator = new FormulaEvaluator();
do
{
itemValues = UOMTable.GetRecords(wc, orderBy, pageNum, maxItems);
foreach (UOMRecord itemValue in itemValues)
{
// Create the item and add to the list.
string cvalue = null;
string fvalue = null;
if (itemValue.UOMIDSpecified)
{
cvalue = itemValue.UOMID.ToString().ToString();
if (counter < maxItems && this.UOMID.Items.FindByValue(cvalue) == null)
{
Boolean _isExpandableNonCompositeForeignKey = ScopeTable.Instance.TableDefinition.IsExpandableNonCompositeForeignKey(ScopeTable.UOMID);
if(_isExpandableNonCompositeForeignKey && ScopeTable.UOMID.IsApplyDisplayAs)
fvalue = ScopeTable.GetDFKA(itemValue, ScopeTable.UOMID);
if ((!_isExpandableNonCompositeForeignKey) || (String.IsNullOrEmpty(fvalue)))
fvalue = itemValue.Format(UOMTable.UOMName);
if (fvalue == null || fvalue.Trim() == "")
fvalue = cvalue;
ListItem newItem = new ListItem(fvalue, cvalue);
this.UOMID.Items.Add(newItem);
counter += 1;
}
}
}
pageNum++;
}
while (itemValues.Length == maxItems && counter < maxItems);
}
// 4. Set the selected value (insert if not already present).
if (selectedValue != null &&
selectedValue.Trim() != "" &&
!MiscUtils.SetSelectedValue(this.UOMID, selectedValue) &&
!MiscUtils.SetSelectedDisplayText(this.UOMID, selectedValue))
{
// construct a whereclause to query a record with UOM.UOMID = selectedValue
CompoundFilter filter2 = new CompoundFilter(CompoundFilter.CompoundingOperators.And_Operator, null);
WhereClause whereClause2 = new WhereClause();
filter2.AddFilter(new BaseClasses.Data.ColumnValueFilter(UOMTable.UOMID, selectedValue, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, false));
whereClause2.AddFilter(filter2, CompoundFilter.CompoundingOperators.And_Operator);
// Execute the query
try
{
UOMRecord[] rc = UOMTable.GetRecords(whereClause2, new OrderBy(false, false), 0, 1);
System.Collections.Generic.IDictionary<string, object> vars = new System.Collections.Generic.Dictionary<string, object> ();
// if find a record, add it to the dropdown and set it as selected item
if (rc != null && rc.Length == 1)
{
string fvalue = ScopeTable.UOMID.Format(selectedValue);
ListItem item = new ListItem(fvalue, selectedValue);
item.Selected = true;
this.UOMID.Items.Add(item);
}
}
catch
{
}
}
}
// Get the filters' data for SiteNameFilter.
protected virtual void PopulateSiteNameFilter(string selectedValue, int maxItems)
{
this.SiteNameFilter.Items.Clear();
// Set up the WHERE and the ORDER BY clause by calling the CreateWhereClause_SiteNameFilter function.
// It is better to customize the where clause there.
// Setup the WHERE clause,
WhereClause wc = this.CreateWhereClause_SiteNameFilter();
// Setup the static list items
OrderBy orderBy = new OrderBy(false, false);
orderBy.Add(VwSiteView.SiteName, OrderByItem.OrderDir.Asc);
string[] values = VwSiteView.GetValues(VwSiteView.SiteName, wc, orderBy, maxItems);
foreach (string itemValue in values)
{
// Create the item and add to the list.
string fvalue;
if ( VwSiteView.SiteName.IsColumnValueTypeBoolean()) {
fvalue = itemValue;
}else {
fvalue = VwSiteView.SiteName.Format(itemValue);
}
ListItem item = new ListItem(fvalue, itemValue);
this.SiteNameFilter.Items.Add(item);
}
// Set the selected value.
MiscUtils.SetSelectedValue(this.SiteNameFilter, selectedValue);
}
// Get the filters' data for ParentCatIDFilter.
protected virtual void PopulateParentCatIDFilter(string selectedValue, int maxItems)
{
this.ParentCatIDFilter.Items.Clear();
// Set up the WHERE and the ORDER BY clause by calling the CreateWhereClause_ParentCatIDFilter function.
// It is better to customize the where clause there.
//Setup the WHERE clause.
WhereClause wc =this.CreateWhereClause_ParentCatIDFilter();
// Setup the static list items
// Add the All item.
this.ParentCatIDFilter.Items.Insert(0, new ListItem(this.Page.GetResourceValue("Txt:All", "FPCEstimate"), "--ANY--"));
OrderBy orderBy = new OrderBy(false, false);
orderBy.Add(CategoryTable.CatName, OrderByItem.OrderDir.Asc);
System.Collections.Generic.IDictionary<string, object> variables = new System.Collections.Generic.Dictionary<string, object> ();
string noValueFormat = Page.GetResourceValue("Txt:Other", "FPCEstimate");
CategoryRecord[] itemValues = null;
if (wc.RunQuery)
{
int counter = 0;
int pageNum = 0;
FormulaEvaluator evaluator = new FormulaEvaluator();
do
{
itemValues = CategoryTable.GetRecords(wc, orderBy, pageNum, maxItems);
foreach (CategoryRecord itemValue in itemValues)
{
// Create the item and add to the list.
string cvalue = null;
string fvalue = null;
if (itemValue.CatIDSpecified)
{
cvalue = itemValue.CatID.ToString();
if (counter < maxItems && this.ParentCatIDFilter.Items.FindByValue(cvalue) == null)
{
Boolean _isExpandableNonCompositeForeignKey = CategoryTable.Instance.TableDefinition.IsExpandableNonCompositeForeignKey(CategoryTable.ParentCatID);
if(_isExpandableNonCompositeForeignKey && CategoryTable.ParentCatID.IsApplyDisplayAs)
fvalue = CategoryTable.GetDFKA(itemValue, CategoryTable.ParentCatID);
if ((!_isExpandableNonCompositeForeignKey) || (String.IsNullOrEmpty(fvalue)))
fvalue = itemValue.Format(CategoryTable.CatName);
if (fvalue == null || fvalue.Trim() == "") fvalue = cvalue;
ListItem newItem = new ListItem(fvalue, cvalue);
this.ParentCatIDFilter.Items.Add(newItem);
counter += 1;
}
}
}
pageNum++;
}
while (itemValues.Length == maxItems && counter < maxItems);
}
// Set the selected value.
MiscUtils.SetSelectedValue(this.ParentCatIDFilter, selectedValue);
}
