public static CosmosElement ToCosmosElement(OrderByContinuationToken orderByContinuationToken)
{
CosmosElement compositeContinuationToken = CompositeContinuationToken.ToCosmosElement(orderByContinuationToken.CompositeContinuationToken);
List <CosmosElement> orderByItemsRaw = new List <CosmosElement>();
foreach (OrderByItem orderByItem in orderByContinuationToken.OrderByItems)
{
orderByItemsRaw.Add(OrderByItem.ToCosmosElement(orderByItem));
}
CosmosArray orderByItems = CosmosArray.Create(orderByItemsRaw);
CosmosElement filter = orderByContinuationToken.Filter == null?CosmosNull.Create() : (CosmosElement)CosmosString.Create(orderByContinuationToken.Filter);
CosmosObject cosmosObject = CosmosObject.Create(
new Dictionary <string, CosmosElement>()
{
{ PropertyNames.CompositeToken, compositeContinuationToken },
{ PropertyNames.OrderByItems, orderByItems },
{ PropertyNames.Rid, CosmosString.Create(orderByContinuationToken.Rid) },
{ PropertyNames.SkipCount, CosmosNumber64.Create(orderByContinuationToken.SkipCount) },
{ PropertyNames.Filter, filter },
});
return(cosmosObject);
}
private OrderByContext CreateOrderByContextForDistinctRowWithoutGroupBy(SelectCommand selectCommand, GroupByContext groupByContext)
{
if (!groupByContext.GetItems().Any() && selectCommand.Projections.IsDistinctRow())
{
int index = 0;
ICollection <OrderByItem> orderByItems = new LinkedList <OrderByItem>();
foreach (var projectionSegment in selectCommand.Projections.GetProjections())
{
if (projectionSegment is ColumnProjectionSegment columnProjectionSegment)
{
var columnOrderByItemSegment = new ColumnOrderByItemSegment(columnProjectionSegment.GetColumn(), OrderDirectionEnum.ASC);
OrderByItem item = new OrderByItem(columnOrderByItemSegment);
item.SetIndex(index);
orderByItems.Add(item);
index++;
}
}
if (orderByItems.Any())
{
return(new OrderByContext(orderByItems, true));
}
}
return(null);
}
} // _Ordered_Comparer_impl_ class
public static DataSet getOrdered(DataSet dataSet, params OrderByItem[] orderByItems)
{
List <Row> values = new List <Row>();
IComparer <Row> valueComparator = null;
if (orderByItems != null && orderByItems.Length != 0)
{
int[] sortIndexes = new int[orderByItems.Length];
for (int i = 0; i < orderByItems.Length; i++)
{
OrderByItem item = orderByItems[i];
int index_of = dataSet.indexOf(item.getSelectItem());
sortIndexes[i] = index_of;
}
values = readDataSetFull(dataSet);
if (values.IsEmpty())
{
return(new EmptyDataSet(dataSet.getSelectItems()));
}
valueComparator = ObjectComparator.getComparator();
// create a comparator for doing the actual sorting/ordering
IComparer <Row> comparator = new _Ordered_Comparer_impl_(valueComparator, orderByItems);
}
values.Sort(valueComparator); // Collections.sort(data, comparator);
dataSet = new InMemoryDataSet(values);
return(dataSet);
} // getOrdered()
} // orderBy()
public Query orderBy(params String[] orderByTokens)
{
foreach (string orderByToken_item in orderByTokens)
{
String orderByToken = orderByToken_item.Trim();
OrderByItem.Direction direction;
if (orderByToken.ToUpper().EndsWith("DESC"))
{
direction = OrderByItem.Direction.DESC;
orderByToken = orderByToken.Substring(0, orderByToken.Length - 4).Trim();
}
else if (orderByToken.ToUpper().EndsWith("ASC"))
{
direction = OrderByItem.Direction.ASC;
orderByToken = orderByToken.Substring(0, orderByToken.Length - 3).Trim();
}
else
{
direction = OrderByItem.Direction.ASC;
}
OrderByItem orderByItem = new OrderByItem(findSelectItem(orderByToken, true), direction);
orderBy(orderByItem);
}
return(this);
} // orderBy()
public void Reverse()
{
var item = new OrderByItem("field1", SortOrder.Ascending);
item.Reverse();
Assert.AreEqual("field1 desc", item.ToString());
Assert.AreEqual(SortOrder.Descending, item.Order);
}
protected void ParseOrderByItem(OrderByItem item)
{
builder.AppendName(item.FieldAlias);
if (item.Ordering == OrderingType.Descending)
{
builder.Append(" DESC");
}
}
/// <summary>
/// 是否已存在
/// </summary>
/// <param name="column">排序列</param>
/// <param name="tableAlias">表别名</param>
/// <returns></returns>
protected bool Exists(string column, string tableAlias)
{
var item = new OrderByItem(column, prefix: tableAlias);
return(_items.Exists(t =>
t.Column.ToLower() == item.Column.ToLower() &&
(item.Prefix.IsEmpty() || t.Prefix?.ToLower() == item.Prefix?.ToLower())));
}
public void AddOrderBy(string tableName, string columnName, OrderByType sortType)
{
OrderByItem orderBy = new OrderByItem {
Table = tableName, Name = columnName, Type = sortType
};
OrderByPart.Add(orderBy);
}
private void Expand(OrderByItem orderByItem)
{
ExpandExpression(orderByItem.Expression);
if (orderByItem.ExplicitOrder)
{
builder.Append(" ").Append(orderByItem.Order == Order.Ascending ? "asc" : "desc");
}
}
private void SetGroupByForDistinctRow(SelectCommandContext selectCommandContext)
{
for (int index = 0; index < selectCommandContext.GetProjectionsContext().GetExpandProjections().Count; index++)
{
OrderByItem orderByItem = new OrderByItem(new IndexOrderByItemSegment(-1, -1, index, OrderDirectionEnum.ASC, OrderDirectionEnum.ASC));
orderByItem.SetIndex(index);
selectCommandContext.GetGroupByContext().GetItems().Add(orderByItem);
}
}
public void Clone()
{
var item = new OrderByItem("field1", SortOrder.Descending);
var clone = item.Clone();
Assert.IsTrue(item.Equals(clone));
clone.Reverse();
Assert.IsFalse(item.Equals(clone));
clone.Reverse();
Assert.IsTrue(item.Equals(clone));
}
public void IsEmpty()
{
var clause = new OrderByClause();
Assert.IsTrue(clause.IsEmpty);
var item = new OrderByItem("field1", SortOrder.Descending);
clause.Add(item);
Assert.IsFalse(clause.IsEmpty);
}
public void Create()
{
var item = new OrderByItem("field1", SortOrder.Ascending);
Assert.AreEqual("field1", item.GetSql());
Assert.AreEqual("field1", item.ToString());
item.Order = SortOrder.Descending;
Assert.AreEqual("field1 desc", item.ToString());
Assert.IsFalse(item.IsEmpty);
}
public void Create()
{
var item = new OrderByItem("field1", SortOrder.Ascending);
var clause = new OrderByClause("field1 asc");
Assert.AreEqual(item, clause);
clause.Add("field2 desc");
Assert.AreEqual("field1,field2 desc", clause.ToString());
clause = new OrderByClause("field1 desc, field2");
Assert.AreEqual("field1 desc,field2", clause.ToString());
}
public void IsEmpty()
{
var item = new OrderByItem();
Assert.IsTrue(item.IsEmpty);
item.Field = "test";
item.Order = SortOrder.Ascending;
Assert.IsFalse(item.IsEmpty);
Assert.AreEqual("test", item.GetSql());
item.Order = SortOrder.Descending;
Assert.AreEqual("test desc", item.GetSql());
}
public GroupByContext CreateGroupByContext(SelectCommand selectCommand)
{
if (selectCommand.GroupBy == null)
{
return(new GroupByContext(new LinkedList <OrderByItem>(), 0));
}
ICollection <OrderByItem> groupByItems = new LinkedList <OrderByItem>();
foreach (var item in selectCommand.GroupBy.GetGroupByItems())
{
var orderByItem = new OrderByItem(item);
if (item is IndexOrderByItemSegment indexOrderByItemSegment)
{
orderByItem.SetIndex(indexOrderByItemSegment.GetColumnIndex());
}
groupByItems.Add(orderByItem);
}
return(new GroupByContext(groupByItems, selectCommand.GroupBy.GetStopIndex()));
}
private StatementSpecCompiled MakeSpec(SelectClauseSpecCompiled selectClauseSpec,
InsertIntoDesc insertIntoDesc,
IList <ExprNode> groupByNodes,
ExprNode optionalHavingNode,
OutputLimitSpec outputLimitSpec,
IList <OrderByItem> orderByList)
{
return(new StatementSpecCompiled(null, // on trigger
null, // create win
null, // create index
null, // create var
null, // create agg var
null, // create schema
insertIntoDesc,
SelectClauseStreamSelectorEnum.ISTREAM_ONLY,
selectClauseSpec,
new StreamSpecCompiled[0], // stream specs
null, // outer join
null,
optionalHavingNode,
outputLimitSpec,
OrderByItem.ToArray(orderByList),
null,
null,
null,
null,
null,
CollectionUtil.EMPTY_STRING_ARRAY,
new Attribute[0],
null,
null,
null,
null,
null,
null,
null,
null,
null,
new GroupByClauseExpressions(ExprNodeUtility.ToArray(groupByNodes)),
null,
null
));
}
public static void ConstructDataSourceObjectFromPostRequest(JSONDataSource jsonDS, ref SqlBuilderColumnSelection requestedCols, ref SqlBuilderColumnSelection workingSelCols,
ref SqlBuilderColumnSelection distinctSelCols, ref OrderBy orderBy, ref KeylessVirtualTable table)
{
DataSource ds = new DataSource();
ds.Initialize(jsonDS.Name, DatabaseObjects.GetTableObject(jsonDS.Table), jsonDS.PageSize, jsonDS.PageIndex, jsonDS.GenerateTotal);
if ((jsonDS.JSelectItems != null))
{
foreach (JDataSourceSelectItem jsonSItem in jsonDS.JSelectItems)
{
ds.AddSelectItem(ConstructSelectItemFromPostRequest(jsonSItem));
}
}
requestedCols = new SqlBuilderColumnSelection(jsonDS.ExpandForeignKeyColumns, jsonDS.IsDistinct);
workingSelCols = new SqlBuilderColumnSelection(jsonDS.ExpandForeignKeyColumns, jsonDS.IsDistinct);
distinctSelCols = new SqlBuilderColumnSelection(jsonDS.ExpandForeignKeyColumns, jsonDS.IsDistinct);
List <BaseColumn> columnsList = null;
if (jsonDS.isTotalRecordArray)
{
columnsList = ds.CreateColumnSelectionsForTotal(ref requestedCols, ref workingSelCols, ref distinctSelCols);
}
else
{
columnsList = ds.CreateColumnSelections(ref requestedCols, ref workingSelCols, ref distinctSelCols);
}
table = ds.CreateVirtualTable(columnsList.ToArray());
if ((jsonDS.JOrderByList != null))
{
foreach (JOrderBy jsonOrderBy in jsonDS.JOrderByList)
{
ds.AddAggregateOrderBy(jsonOrderBy.ColumnName, OrderByItem.ToOrderDir(jsonOrderBy.OrderDirection));
}
}
ds.UpdateOrderBy(columnsList);
orderBy = ds.OrderBy;
}
public void Add()
{
IOrderByClause clause = new OrderByClause("field1 desc, field2");
var item = new OrderByItem("field3", SortOrder.Descending);
clause.Add(item);
Assert.AreEqual("field1 desc,field2,field3 desc", clause.ToString());
// can't add existing field
Assert.Throws <ArgumentException>(() =>
{
clause.Add("field2");
});
clause.AddAlways("field1", SortOrder.Ascending);
Assert.AreEqual("field2,field3 desc,field1", clause.ToString());
item.Reverse();
Assert.AreEqual("field2,field3,field1", clause.ToString());
}
public OrderByContext CreateOrderBy(SelectCommand selectCommand, GroupByContext groupByContext)
{
if (null == selectCommand.OrderBy || !selectCommand.OrderBy.GetOrderByItems().Any())
{
OrderByContext orderByContext = CreateOrderByContextForDistinctRowWithoutGroupBy(selectCommand, groupByContext);
return(null != orderByContext ? orderByContext : new OrderByContext(groupByContext.GetItems(), groupByContext.GetItems().Any()));
}
ICollection <OrderByItem> orderByItems = new LinkedList <OrderByItem>();
foreach (var orderByItemSegment in selectCommand.OrderBy.GetOrderByItems())
{
OrderByItem orderByItem = new OrderByItem(orderByItemSegment);
if (orderByItemSegment is IndexOrderByItemSegment indexOrderByItemSegment)
{
orderByItem.SetIndex(indexOrderByItemSegment.GetColumnIndex());
}
orderByItems.Add(orderByItem);
}
return(new OrderByContext(orderByItems, false));
}
/// <summary>
/// Builds the order by collection.
/// </summary>
/// <param name="reportOrderBys">The report order bys.</param>
/// <param name="context">The context.</param>
/// <param name="settings">Conversion settings.</param>
/// <returns>List{OrderByItem}.</returns>
internal static List <OrderByItem> BuildOrderBy(IEntityCollection <ReportOrderBy> reportOrderBys, FromEntityContext context, ReportToQueryConverterSettings settings)
{
List <OrderByItem> orderByItems = new List <OrderByItem>();
if (context.Settings.ConditionsOnly)
{
return(orderByItems); // don't bother converting them (e.g. for use in security)
}
foreach (ReportOrderBy reportOrderBy in reportOrderBys.OrderBy(rob => rob.OrderPriority))
{
ScalarExpression expression = BuildExpression(reportOrderBy.OrderByExpression, context);
if (expression == null)
{
EventLog.Application.WriteWarning("the order expression id: {0} is invalid.", reportOrderBy.OrderByExpression != null ? reportOrderBy.OrderByExpression.Id.ToString() : "null");
continue;
}
//the orderby expression is ColumnReferenceExpression, the referenced report column must exists
if (context.ColumnReferenceMap.ContainsKey(expression.ExpressionId) &&
context.ReportColumnMap.ContainsKey(context.ColumnReferenceMap[expression.ExpressionId]))
{
OrderByItem orderByItem = new OrderByItem
{
Direction =
reportOrderBy.ReverseOrder ?? false
? OrderByDirection.Descending
: OrderByDirection.Ascending,
Expression = expression
};
orderByItems.Add(orderByItem);
}
}
return(orderByItems);
}
public void RemoveUnusedColumns_EnsureUnusedOrderbyRemoved( )
{
SelectColumn col1 = new SelectColumn( );
SelectColumn col2 = new SelectColumn( );
SelectColumn col3 = new SelectColumn( );
OrderByItem order1 = new OrderByItem {
Expression = new ColumnReference {
ColumnId = col1.ColumnId
}, Direction = OrderByDirection.Ascending
};
OrderByItem order2 = new OrderByItem {
Expression = new ColumnReference {
ColumnId = col2.ColumnId
}, Direction = OrderByDirection.Descending
};
StructuredQuery query = new StructuredQuery( );
query.SelectColumns.Add(col1);
query.SelectColumns.Add(col2);
query.SelectColumns.Add(col3);
query.OrderBy.Add(order1);
query.OrderBy.Add(order2);
ClientAggregate agg = new ClientAggregate( );
agg.AggregatedColumns.Add(new ReportAggregateField {
ReportColumnId = col2.ColumnId, AggregateMethod = AggregateMethod.Max
});
StructuredQuery result = ReportRollupHelper.RemoveUnusedColumns(query, agg);
Assert.That(result.SelectColumns.Count, Is.EqualTo(1), "Cols");
Assert.That(result.SelectColumns [0].ColumnId, Is.EqualTo(col2.ColumnId));
Assert.That(result.OrderBy.Count, Is.EqualTo(1), "Order");
Assert.That(result.OrderBy [0].Direction, Is.EqualTo(OrderByDirection.Descending));
}
} // constructor
public int Compare(Row o1, Row o2)
{
for (int i = 0; i < _sortIndexes.Length; i++)
{
int sortIndex = _sortIndexes[i];
Object sortObj1 = o1.getValue(sortIndex);
Object sortObj2 = o2.getValue(sortIndex);
int compare = _valueComparator.Compare((Row)sortObj1, (Row)sortObj2);
if (compare != 0)
{
OrderByItem orderByItem = _orderByItems[i];
bool ascending = orderByItem.isAscending();
if (ascending)
{
return(compare);
}
else
{
return(compare * -1);
}
}
}
return(0);
} // Compare()
private QueryNode QueryData(string program_job_no, string status,
DependencyObjectCollection doc_no, string site_no, string warehouse_no, string warseHouseControl)
{
OrderByItem[] orderByItem = null;
QueryProperty property = null;
bool bcInvControl = UtilsClass.IsBCInventoryManagement(this.GetService <IQueryService>());
if (bcInvControl)
{
property = OOQL.CreateProperty("BC_INVENTORY.QTY");
}
else
{
property = OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY");
}
//策略
if (warseHouseControl == "1") //1.生效日期先进先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.EFFECTIVE_DATE", SortType.Asc) }
}
;
else if (warseHouseControl == "2") //2.生效日期后进先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.EFFECTIVE_DATE", SortType.Desc) }
}
;
else if (warseHouseControl == "3") //3.不限定
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.LOT_CODE", SortType.Asc) }
}
;
else if (warseHouseControl == "4") //4.先到期先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.INEFFECTIVE_DATE", SortType.Asc) }
}
;
else if (warseHouseControl == "5") //5.允许出库日早者先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.ALLOW_ISSUE_DATE", SortType.Asc) }
}
;
else if (warseHouseControl == "6") //6.可用量少的先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem(property, SortType.Asc) };
}
else if (warseHouseControl == "7") //6.可用量少的先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem(property, SortType.Desc) };
}
//组织单号
List <ConstantsQueryProperty> docNos = new List <ConstantsQueryProperty>();
foreach (DependencyObject item in doc_no)
{
docNos.Add(OOQL.CreateConstants(item["doc_no"].ToStringExtension()));
}
QueryNode node = null;
if (program_job_no == "5")//20170328 modi by wangyq for P001-170327001去掉&& status == "A"
{
node = GetSalesDeliveryNode(site_no, docNos, warseHouseControl);
}
else if (program_job_no.StartsWith("7") && status == "A")
{
node = GetMONode(site_no, program_job_no, docNos, warseHouseControl);
}
else if (program_job_no.StartsWith("7") && status == "S")
{
node = GetIssueReceiptNode(site_no, docNos, warseHouseControl);
}
else if (program_job_no.StartsWith("11") && status == "S")
{
node = GeTransferDocNode(site_no, docNos, warseHouseControl);
}
else if (program_job_no.StartsWith("4")) //20170629 add by zhangcn for P001-170606002 //20170719 modi by shenbao for 拿掉status == "A"
{
node = GePurchaseReturnNode(site_no, docNos, warseHouseControl); //20170629 add by zhangcn for P001-170606002
}
else if (program_job_no.StartsWith("5-1")) //20170829 add by shenbao for P001-170717001
{
node = GetSalesOrderDocNode(site_no, docNos, warseHouseControl);
}
List <QueryProperty> pubProperties = new List <QueryProperty>()
{
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateProperty("QuerySource.PLANT_CODE", "site_no"),
OOQL.CreateProperty("QuerySource.ITEM_CODE", "item_no"),
OOQL.CreateProperty("QuerySource.ITEM_NAME", "item_name"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.STOCK_UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.SOURCE_QTY"), OOQL.CreateConstants(0), "SOURCE_QTY")
};
//查询
if (bcInvControl)
{
pubProperties.AddRange(new QueryProperty[] {
OOQL.CreateProperty("BC_INVENTORY.BARCODE_NO", "barcode_no"),
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"),
OOQL.CreateConstants(UtilsClass.SpaceValue, "inventory_management_features"),//20170328 modi by wangyq for P001-170327001 old:string.Empty
Formulas.IsNull(OOQL.CreateProperty("BC_INVENTORY.CreateDate"), OOQL.CreateConstants(OrmDataOption.EmptyDateTime.Date), "first_storage_date"),
Formulas.IsNull(OOQL.CreateProperty("BC_INVENTORY.QTY"), OOQL.CreateConstants(0), "inventory_qty"),
Formulas.IsNull(Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("QuerySource.ITEM_ID")
, OOQL.CreateProperty("QuerySource.STOCK_UNIT_ID")
, OOQL.CreateProperty("BC_INVENTORY.QTY")
, OOQL.CreateProperty("QuerySource.UNIT_ID")
, OOQL.CreateConstants(0) }), OOQL.CreateConstants(0), "conversion_qty"),
Formulas.RowNumber("sort_no", OOQL.Over(new QueryProperty[] { OOQL.CreateProperty("QuerySource.ITEM_CODE") },
orderByItem
))
});
node = OOQL.Select(
pubProperties
)
.From(node, "QuerySource")
.LeftJoin("BC_INVENTORY")
.On(OOQL.CreateProperty("QuerySource.ITEM_ID") == OOQL.CreateProperty("BC_INVENTORY.ITEM_ID")
& Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())) == OOQL.CreateProperty("BC_INVENTORY.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE")
.On(OOQL.CreateProperty("BC_INVENTORY.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("BC_INVENTORY.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID"))
.LeftJoin("ITEM_LOT")
.On(OOQL.CreateProperty("BC_INVENTORY.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"))
.LeftJoin("PLANT") //20170719 add by shenbao for P001-170717001
.On(OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.Where(((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(warehouse_no))
| OOQL.CreateConstants(warehouse_no) == OOQL.CreateConstants(""))
& OOQL.CreateProperty("BC_INVENTORY.QTY") > OOQL.CreateConstants(0)
& OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_PROPERTY") == OOQL.CreateConstants("1") //20170717 add by shenbao for P001-170717001
& OOQL.CreateProperty("WAREHOUSE.INCLUDED_AVAILABLE_QTY") == OOQL.CreateConstants(true) //20170717 add by shenbao for P001-170717001
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no)); //20170719 add by shenbao for P001-170717001
}
else
{
pubProperties.AddRange(new QueryProperty[] {
OOQL.CreateConstants("", "barcode_no"),
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.LAST_RECEIPT_DATE"), OOQL.CreateConstants(OrmDataOption.EmptyDateTime.Date), "first_storage_date"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY"), OOQL.CreateConstants(0), "inventory_qty"),
Formulas.IsNull(Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("QuerySource.ITEM_ID")
, OOQL.CreateProperty("QuerySource.STOCK_UNIT_ID")
, OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY")
, OOQL.CreateProperty("QuerySource.UNIT_ID")
, OOQL.CreateConstants(0) }), OOQL.CreateConstants(0), "conversion_qty"),
Formulas.RowNumber("sort_no", OOQL.Over(new QueryProperty[] { OOQL.CreateProperty("QuerySource.ITEM_CODE") },
orderByItem
))
});
node = OOQL.Select(
pubProperties
)
.From(node, "QuerySource")
.LeftJoin("ITEM_WAREHOUSE_BIN")
.On(OOQL.CreateProperty("QuerySource.ITEM_ID") == OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_ID")
& Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())) == OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE")
.On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID"))
.LeftJoin("ITEM_LOT")
.On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"))
.LeftJoin("PLANT") //20170719 add by shenbao for P001-170717001
.On(OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.Where(((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(warehouse_no))
| OOQL.CreateConstants(warehouse_no) == OOQL.CreateConstants(""))
& OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY") > OOQL.CreateConstants(0)
& OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.BO_ID.RTK") == OOQL.CreateConstants("OTHER")
& OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_PROPERTY") == OOQL.CreateConstants("1") //20170717 add by shenbao for P001-170717001
& OOQL.CreateProperty("WAREHOUSE.INCLUDED_AVAILABLE_QTY") == OOQL.CreateConstants(true) //20170717 add by shenbao for P001-170717001
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no)); //20170719 add by shenbao for P001-170717001
}
return(node);
}
private string _buildHasPage(SqlFilter filter)
{
foreach (string select in filter.Selects)
{
SelectMethods[select]();
}
BuildOrderBy(filter.Orders);
BuildGroupBy(filter.Groups);
BuildJoin(filter.Joins);
string strWhere = "", strFrom = "";
StringBuilder sql = new StringBuilder();
sql.Append("select ");
sql.Append(string.Join(",", SelectPart.ToArray()));
_buildFromAndWhere(filter, out strFrom, out strWhere);
sql.Append(strFrom).Append(strWhere);
if (GroupByPart.Count > 0)
{
sql.Append(" group by ");
for (int i = 0; i < GroupByPart.Count; i++)
{
GroupByItem item = GroupByPart[i];
if (i > 0)
{
sql.Append(",");
}
if (!string.IsNullOrEmpty(item.Table))
{
sql.Append(item.Table).Append(".").Append(item.Name);
}
else
{
sql.Append(item.Name);
}
}
}
if (OrderByPart.Count > 0)
{
sql.Append(" order by ");
for (int i = 0; i < OrderByPart.Count; i++)
{
OrderByItem item = OrderByPart[i];
if (i > 0)
{
sql.Append(",");
}
switch (item.Type)
{
case OrderByType.ASC:
sql.Append(item.Table).Append(".").Append(item.Name);
sql.Append(" asc ");
break;
case OrderByType.DESC:
sql.Append(item.Table).Append(".").Append(item.Name);
sql.Append(" desc ");
break;
case OrderByType.RANDOM:
sql.Append(" newid() ");
break;
}
}
}
if (filter.Page > 0)
{
sql.Append(" limit " + filter.PageSize.ToString() + " ");
}
sql.Append(" offset ").Append(((filter.Page - 1) * filter.PageSize));
return(sql.ToString());
}
private bool GetOrderValuesCaseSensitiveFromTables(SelectCommandContext selectCommandContext, SchemaMetaData schemaMetaData, OrderByItem eachOrderByItem)
{
foreach (var simpleTableSegment in selectCommandContext.GetAllTables())
{
var tableName = simpleTableSegment.GetTableName().GetIdentifier().GetValue();
var tableMetaData = schemaMetaData.Get(tableName);
IDictionary <String, ColumnMetaData> columns = tableMetaData.GetColumns();
var orderByItemSegment = eachOrderByItem.GetSegment();
if (orderByItemSegment is ColumnOrderByItemSegment columnOrderByItemSegment)
{
String columnName = columnOrderByItemSegment.GetColumn().GetIdentifier().GetValue();
if (columns.ContainsKey(columnName))
{
return(columns[columnName].CaseSensitive);
}
}
else if (orderByItemSegment is IndexOrderByItemSegment indexOrderByItemSegment)
{
int columnIndex = indexOrderByItemSegment.GetColumnIndex();
String columnName = _streamDataReader.GetColumnName(columnIndex);
if (columns.ContainsKey(columnName))
{
return(columns[columnName].CaseSensitive);
}
}
else
{
return(false);
}
}
return(false);
}
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 DataFlowOpInitializeResult Initialize(DataFlowOpInitializateContext context)
{
if (context.InputPorts.IsEmpty())
{
throw new ArgumentException("Select operator requires at least one input stream");
}
if (context.OutputPorts.Count != 1)
{
throw new ArgumentException("Select operator requires one output stream but produces " + context.OutputPorts.Count + " streams");
}
DataFlowOpOutputPort portZero = context.OutputPorts[0];
if (portZero.OptionalDeclaredType != null && !portZero.OptionalDeclaredType.IsUnderlying)
{
_submitEventBean = true;
}
// determine adapter factories for each type
int numStreams = context.InputPorts.Count;
_adapterFactories = new EventBeanAdapterFactory[numStreams];
for (int i = 0; i < numStreams; i++)
{
EventType eventType = context.InputPorts.Get(i).TypeDesc.EventType;
_adapterFactories[i] = context.StatementContext.EventAdapterService.GetAdapterFactoryForType(eventType);
}
// Compile and prepare execution
//
StatementContext statementContext = context.StatementContext;
EPServicesContext servicesContext = context.ServicesContext;
AgentInstanceContext agentInstanceContext = context.AgentInstanceContext;
// validate
if (select.InsertIntoDesc != null)
{
throw new ExprValidationException("Insert-into clause is not supported");
}
if (select.SelectStreamSelectorEnum != SelectClauseStreamSelectorEnum.ISTREAM_ONLY)
{
throw new ExprValidationException("Selecting remove-stream is not supported");
}
ExprNodeSubselectDeclaredDotVisitor visitor = StatementSpecRawAnalyzer.WalkSubselectAndDeclaredDotExpr(select);
GroupByClauseExpressions groupByExpressions = GroupByExpressionHelper.GetGroupByRollupExpressions(
servicesContext.Container,
select.GroupByExpressions,
select.SelectClauseSpec,
select.HavingExprRootNode,
select.OrderByList,
visitor);
if (!visitor.Subselects.IsEmpty())
{
throw new ExprValidationException("Subselects are not supported");
}
IDictionary <int, FilterStreamSpecRaw> streams = new Dictionary <int, FilterStreamSpecRaw>();
for (int streamNum = 0; streamNum < select.StreamSpecs.Count; streamNum++)
{
var rawStreamSpec = select.StreamSpecs[streamNum];
if (!(rawStreamSpec is FilterStreamSpecRaw))
{
throw new ExprValidationException("From-clause must contain only streams and cannot contain patterns or other constructs");
}
streams.Put(streamNum, (FilterStreamSpecRaw)rawStreamSpec);
}
// compile offered streams
IList <StreamSpecCompiled> streamSpecCompileds = new List <StreamSpecCompiled>();
for (int streamNum = 0; streamNum < select.StreamSpecs.Count; streamNum++)
{
var filter = streams.Get(streamNum);
var inputPort = FindInputPort(filter.RawFilterSpec.EventTypeName, context.InputPorts);
if (inputPort == null)
{
throw new ExprValidationException(
string.Format("Failed to find stream '{0}' among input ports, input ports are {1}", filter.RawFilterSpec.EventTypeName, GetInputPortNames(context.InputPorts).Render(", ", "[]")));
}
var eventType = inputPort.Value.Value.TypeDesc.EventType;
var streamAlias = filter.OptionalStreamName;
var filterSpecCompiled = new FilterSpecCompiled(eventType, streamAlias, new IList <FilterSpecParam>[] { Collections.GetEmptyList <FilterSpecParam>() }, null);
var filterStreamSpecCompiled = new FilterStreamSpecCompiled(filterSpecCompiled, select.StreamSpecs[0].ViewSpecs, streamAlias, StreamSpecOptions.DEFAULT);
streamSpecCompileds.Add(filterStreamSpecCompiled);
}
// create compiled statement spec
SelectClauseSpecCompiled selectClauseCompiled = StatementLifecycleSvcUtil.CompileSelectClause(select.SelectClauseSpec);
// determine if snapshot output is needed
OutputLimitSpec outputLimitSpec = select.OutputLimitSpec;
_isOutputLimited = outputLimitSpec != null;
if (iterate)
{
if (outputLimitSpec != null)
{
throw new ExprValidationException("Output rate limiting is not supported with 'iterate'");
}
outputLimitSpec = new OutputLimitSpec(OutputLimitLimitType.SNAPSHOT, OutputLimitRateType.TERM);
}
var mergedAnnotations = AnnotationUtil.MergeAnnotations(statementContext.Annotations, context.OperatorAnnotations);
var orderByArray = OrderByItem.ToArray(select.OrderByList);
var outerJoinArray = OuterJoinDesc.ToArray(select.OuterJoinDescList);
var streamSpecArray = streamSpecCompileds.ToArray();
var compiled = new StatementSpecCompiled(null, null, null, null, null, null, null, SelectClauseStreamSelectorEnum.ISTREAM_ONLY,
selectClauseCompiled, streamSpecArray, outerJoinArray, select.FilterExprRootNode, select.HavingExprRootNode, outputLimitSpec,
orderByArray, ExprSubselectNode.EMPTY_SUBSELECT_ARRAY, ExprNodeUtility.EMPTY_DECLARED_ARR, ExprNodeUtility.EMPTY_SCRIPTS, select.ReferencedVariables,
select.RowLimitSpec, CollectionUtil.EMPTY_STRING_ARRAY, mergedAnnotations, null, null, null, null, null, null, null, null, null, groupByExpressions, null, null);
// create viewable per port
var viewables = new EPLSelectViewable[context.InputPorts.Count];
_viewablesPerPort = viewables;
foreach (var entry in context.InputPorts)
{
EPLSelectViewable viewable = new EPLSelectViewable(entry.Value.TypeDesc.EventType);
viewables[entry.Key] = viewable;
}
var activatorFactory = new ProxyViewableActivatorFactory
{
ProcCreateActivatorSimple = filterStreamSpec =>
{
EPLSelectViewable found = null;
foreach (EPLSelectViewable sviewable in viewables)
{
if (sviewable.EventType == filterStreamSpec.FilterSpec.FilterForEventType)
{
found = sviewable;
}
}
if (found == null)
{
throw new IllegalStateException("Failed to find viewable for filter");
}
EPLSelectViewable viewable = found;
return(new ProxyViewableActivator(
(agentInstanceContext2, isSubselect, isRecoveringResilient) =>
new ViewableActivationResult(
viewable,
new ProxyStopCallback(() => { }),
null,
null,
null,
false,
false,
null)));
}
};
// for per-row deliver, register select expression result callback
OutputProcessViewCallback optionalOutputProcessViewCallback = null;
if (!iterate && !_isOutputLimited)
{
_deliveryCallback = new EPLSelectDeliveryCallback();
optionalOutputProcessViewCallback = this;
}
// prepare
EPStatementStartMethodSelectDesc selectDesc = EPStatementStartMethodSelectUtil.Prepare(compiled, servicesContext, statementContext, false, agentInstanceContext, false, activatorFactory, optionalOutputProcessViewCallback, _deliveryCallback);
// start
_selectResult = (StatementAgentInstanceFactorySelectResult)selectDesc.StatementAgentInstanceFactorySelect.NewContext(agentInstanceContext, false);
// for output-rate-limited, register a dispatch view
if (_isOutputLimited)
{
_selectResult.FinalView.AddView(new EPLSelectUpdateDispatchView(this));
}
// assign strategies to expression nodes
EPStatementStartMethodHelperAssignExpr.AssignExpressionStrategies(
selectDesc,
_selectResult.OptionalAggegationService,
_selectResult.SubselectStrategies,
_selectResult.PriorNodeStrategies,
_selectResult.PreviousNodeStrategies,
null,
null,
_selectResult.TableAccessEvalStrategies);
EventType outputEventType = selectDesc.ResultSetProcessorPrototypeDesc.ResultSetProcessorFactory.ResultEventType;
_agentInstanceContext = agentInstanceContext;
return(new DataFlowOpInitializeResult(new GraphTypeDesc[] { new GraphTypeDesc(false, true, outputEventType) }));
}
public static GroupByClauseExpressions GetGroupByRollupExpressions(
IList<GroupByClauseElement> groupByElements,
SelectClauseSpecRaw selectClauseSpec,
ExprNode optionalHavingNode,
IList<OrderByItem> orderByList,
ExpressionCopier expressionCopier)
{
if (groupByElements == null || groupByElements.Count == 0) {
return null;
}
// walk group-by-elements, determine group-by expressions and rollup nodes
var groupByExpressionInfo = GroupByToRollupNodes(groupByElements);
// obtain expression nodes, collect unique nodes and assign index
IList<ExprNode> distinctGroupByExpressions = new List<ExprNode>();
IDictionary<ExprNode, int> expressionToIndex = new Dictionary<ExprNode, int>();
foreach (var exprNode in groupByExpressionInfo.Expressions) {
var found = false;
for (var i = 0; i < distinctGroupByExpressions.Count; i++) {
ExprNode other = distinctGroupByExpressions[i];
// find same expression
if (ExprNodeUtilityCompare.DeepEquals(exprNode, other, false)) {
expressionToIndex.Put(exprNode, i);
found = true;
break;
}
}
// not seen before
if (!found) {
expressionToIndex.Put(exprNode, distinctGroupByExpressions.Count);
distinctGroupByExpressions.Add(exprNode);
}
}
// determine rollup, validate it is either (not both)
var hasGroupingSet = false;
var hasRollup = false;
foreach (var element in groupByElements) {
if (element is GroupByClauseElementGroupingSet) {
hasGroupingSet = true;
}
if (element is GroupByClauseElementRollupOrCube) {
hasRollup = true;
}
}
// no-rollup or grouping-sets means simply validate
ExprNode[] groupByExpressions = distinctGroupByExpressions.ToArray();
if (!hasRollup && !hasGroupingSet) {
return new GroupByClauseExpressions(groupByExpressions);
}
// evaluate rollup node roots
var nodes = groupByExpressionInfo.Nodes;
var perNodeCombinations = new object[nodes.Count][];
var context = new GroupByRollupEvalContext(expressionToIndex);
try {
for (var i = 0; i < nodes.Count; i++) {
GroupByRollupNodeBase node = nodes[i];
var combinations = node.Evaluate(context);
perNodeCombinations[i] = new object[combinations.Count];
for (var j = 0; j < combinations.Count; j++) {
perNodeCombinations[i][j] = combinations[j];
}
}
}
catch (GroupByRollupDuplicateException ex) {
if (ex.Indexes.Length == 0) {
throw new ExprValidationException(
"Failed to validate the group-by clause, found duplicate specification of the overall grouping '()'");
}
var writer = new StringWriter();
var delimiter = "";
for (var i = 0; i < ex.Indexes.Length; i++) {
writer.Write(delimiter);
writer.Write(
ExprNodeUtilityPrint.ToExpressionStringMinPrecedenceSafe(groupByExpressions[ex.Indexes[i]]));
delimiter = ", ";
}
throw new ExprValidationException(
"Failed to validate the group-by clause, found duplicate specification of expressions (" +
writer +
")");
}
// enumerate combinations building an index list
var combinationEnumeration = new CombinationEnumeration(perNodeCombinations);
var combination = new SortedSet<int>();
var indexList = new LinkedHashSet<MultiKeyArrayInt>();
while (combinationEnumeration.MoveNext()) {
combination.Clear();
object[] combinationOA = combinationEnumeration.Current;
foreach (var indexes in combinationOA) {
var indexarr = (int[]) indexes;
foreach (var anIndex in indexarr) {
combination.Add(anIndex);
}
}
var indexArr = CollectionUtil.IntArray(combination);
indexList.Add(new MultiKeyArrayInt(indexArr));
}
// obtain rollup levels
var rollupLevels = new int[indexList.Count][];
var count = 0;
foreach (var mk in indexList) {
rollupLevels[count++] = mk.Keys;
}
var numberOfLevels = rollupLevels.Length;
if (numberOfLevels == 1 && rollupLevels[0].Length == 0) {
throw new ExprValidationException(
"Failed to validate the group-by clause, the overall grouping '()' cannot be the only grouping");
}
// obtain select-expression copies for rewrite
var expressions = selectClauseSpec.SelectExprList;
var selects = new ExprNode[numberOfLevels][];
for (var i = 0; i < numberOfLevels; i++) {
selects[i] = new ExprNode[expressions.Count];
for (var j = 0; j < expressions.Count; j++) {
SelectClauseElementRaw selectRaw = expressions[j];
if (!(selectRaw is SelectClauseExprRawSpec)) {
throw new ExprValidationException(
"Group-by with rollup requires that the select-clause does not use wildcard");
}
var compiled = (SelectClauseExprRawSpec) selectRaw;
selects[i][j] = CopyVisitExpression(compiled.SelectExpression, expressionCopier);
}
}
// obtain having-expression copies for rewrite
ExprNode[] optHavingNodeCopy = null;
if (optionalHavingNode != null) {
optHavingNodeCopy = new ExprNode[numberOfLevels];
for (var i = 0; i < numberOfLevels; i++) {
optHavingNodeCopy[i] = CopyVisitExpression(optionalHavingNode, expressionCopier);
}
}
// obtain orderby-expression copies for rewrite
ExprNode[][] optOrderByCopy = null;
if (orderByList != null && orderByList.Count > 0) {
optOrderByCopy = new ExprNode[numberOfLevels][];
for (var i = 0; i < numberOfLevels; i++) {
optOrderByCopy[i] = new ExprNode[orderByList.Count];
for (var j = 0; j < orderByList.Count; j++) {
OrderByItem element = orderByList[j];
optOrderByCopy[i][j] = CopyVisitExpression(element.ExprNode, expressionCopier);
}
}
}
return new GroupByClauseExpressions(
groupByExpressions,
rollupLevels,
selects,
optHavingNodeCopy,
optOrderByCopy);
}
public SatisfiedOrderByBuilderImpl(FunctionType function, Column column,
Query query, GroupedQueryBuilder queryBuilder) : base(queryBuilder)
{
orderByitem = new OrderByItem(new SelectItem(function, column));
query.orderBy(orderByitem);
}