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); }