public SummaryDataResponse GetSummary(SummaryDataRequest request) { var data = Helpers.LoadData(request.FileName); SummaryDataResponse response = new SummaryDataResponse() { FileName = data.TableName, ColumnName = request.ColumnName, Operation = request.Operation, }; if (request.ColumnName != "*" && request.GroupColumnName == "*") { response.Value = Helpers.ReturnResultStatsColumn(request.ColumnName, request.Operation, data); } else if (request.ColumnName == "*" && request.GroupColumnName == "*") { response.ListValue = new List <GroupByList>(); foreach (DataColumn item in data.Columns) { GroupByList groupByList = new GroupByList(); groupByList.Name = item.ColumnName; groupByList.Result = Helpers.ReturnResultStatsColumn(item.ColumnName, request.Operation, data); response.ListValue.Add(groupByList); } } else { response.ListValue = Helpers.ReturnResultStatsList(request, data); } return(response); }
public Select(IFormatter parameters, string tableAlias = "") { Query = SqlQuery.Select; Formatter = parameters; TableAlias = tableAlias; Columns = new ColumnsListAggregation(Formatter); Join = new JoinList(Formatter); Where = new WhereList(Formatter); OrderBy = new OrderByList(Formatter); GroupBy = new GroupByList(Formatter, Columns); }
public void GroupBySimpleListAlias() { ColumnsListAggregation c = new ColumnsListAggregation(global::SqlBuilder.Format.MsSQL); GroupByList g = new GroupByList(global::SqlBuilder.Format.MsSQL, c); g.Append(false, "a", "b", "c"); string result = g.GetSql("t"); string sql = "[t].[a], [t].[b], [t].[c]"; Assert.AreEqual(sql, result); }
public void GroupBySimpleList3() { ColumnsListAggregation c = new ColumnsListAggregation(global::SqlBuilder.Format.MsSQL); GroupByList g = new GroupByList(global::SqlBuilder.Format.MsSQL, c); g.Append(true, "a", "b", "c"); string result = g.GetSql(); string sql = "[a], [b], [c]"; Assert.AreEqual(sql, result); Assert.AreEqual(c.Count, g.Count); }
public void GroupBySimpleListAlias() { NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql; ColumnsListAggregation c = new ColumnsListAggregation(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter); GroupByList g = new GroupByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter, c); g.Append(false, "a", "b", "c"); string result = g.GetSql("t"); string sql = "[t].[a], [t].[b], [t].[c]"; Assert.Equal(sql, result); }
public void GroupBySimpleListAlias() { SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql; ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter); GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c); g.Append(false, "a", "b", "c"); string result = g.GetSql("t"); string sql = "[t].[a], [t].[b], [t].[c]"; Assert.AreEqual(sql, result); }
public void GroupByRaw2() { SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql; ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter); GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c); g.Raw("[t].[column]", "[g].[guid]"); string result = g.GetSql(); string sql = "[t].[column], [g].[guid]"; Assert.AreEqual(sql, result); }
public void GroupByRaw2() { NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql; ColumnsListAggregation c = new ColumnsListAggregation(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter); GroupByList g = new GroupByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter, c); g.Raw("[t].[column]", "[g].[guid]"); string result = g.GetSql(); string sql = "[t].[column], [g].[guid]"; Assert.Equal(sql, result); }
public void GroupBySimpleList3() { SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql; ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter); GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c); g.Append(true, "a", "b", "c"); string result = g.GetSql(); string sql = "[a], [b], [c]"; Assert.AreEqual(sql, result); Assert.AreEqual(c.Count, g.Count); }
public string BuildSQL(string condition, string having, params string[] sorts) { string sqlstr = SelectClause + " " + FromClause; if (condition != null && condition.Trim().Length > 0) { if (WhereClause == null || WhereClause.Trim().Length <= 0) { sqlstr += " where " + condition; } else { sqlstr += " where ( " + condition + " ) and ( " + WhereClause + " )"; } } else if (WhereClause != null && WhereClause.Trim().Length > 0) { sqlstr += " where " + WhereClause; } if (GroupByList != null && GroupByList.Trim().Length > 0) { sqlstr += " group by " + GroupByList; if (having != null && having.Trim().Length > 0) { //用户指定了条件并且有分组,则用户指定条件合并到HAVING子句中 if (HavingClause == null || HavingClause.Trim().Length <= 0) { sqlstr += " having " + having; } else { sqlstr += " having ( " + having + " ) and ( " + HavingClause + " )"; } } else if (HavingClause != null && HavingClause.Trim().Length > 0) { sqlstr += " having " + HavingClause; } } if (sorts.Length > 0) { sqlstr += " " + string.Join(",", sorts); } else if (OrderByList != null && OrderByList.Trim().Length > 0) { sqlstr += " order by " + OrderByList; } return(sqlstr); }
public void GroupByAggregation2() { ColumnsListAggregation c = new ColumnsListAggregation(global::SqlBuilder.Format.MsSQL); GroupByList g = new GroupByList(global::SqlBuilder.Format.MsSQL, c); g.FuncMin("sm"); string result1 = g.GetSql(); string sql1 = "[sm]"; string result2 = c.GetSql(); string sql2 = "MIN([sm])"; Assert.AreEqual(sql1, result1); Assert.AreEqual(sql2, result2); Assert.AreEqual(c.Count, g.Count); }
public void GroupByAggregation1() { SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql; ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter); GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c); g.FuncMax("sm"); string result1 = g.GetSql(); string sql1 = "[sm]"; string result2 = c.GetSql(); string sql2 = "MAX([sm])"; Assert.AreEqual(sql1, result1); Assert.AreEqual(sql2, result2); Assert.AreEqual(c.Count, g.Count); }
public void GroupByAggregation2() { NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql; ColumnsListAggregation c = new ColumnsListAggregation(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter); GroupByList g = new GroupByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter, c); g.FuncMin("sm"); string result1 = g.GetSql(); string sql1 = "[sm]"; string result2 = c.GetSql(); string sql2 = "MIN([sm])"; Assert.Equal(sql1, result1); Assert.Equal(sql2, result2); Assert.Equal(c.Count, g.Count); }
public void GroupByAggregationMany() { ColumnsListAggregation c = new ColumnsListAggregation(global::SqlBuilder.Format.MsSQL); GroupByList g = new GroupByList(global::SqlBuilder.Format.MsSQL, c); g.FuncSum("sm", "asm"); g.FuncMax("mx", "amx"); g.FuncMin("mn", "amn"); string result1 = g.GetSql(); string sql1 = "[sm], [mx], [mn]"; string result2 = c.GetSql(); string sql2 = "SUM([sm]) as 'asm', MAX([mx]) as 'amx', MIN([mn]) as 'amn'"; Assert.AreEqual(sql1, result1); Assert.AreEqual(sql2, result2); Assert.AreEqual(c.Count, g.Count); }
public void GroupByAlias2() { NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql; ColumnsListAggregation c = new ColumnsListAggregation(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter); GroupByList g = new GroupByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter, c); g.Append(false, "c1"); g.SetTableAlias("oh2"); g.Append(false, "c2"); g.SetTableAlias("oh3"); g.Append(false, "c3"); g.SetTableAlias(); g.Append(false, "c4"); g.Raw("[t].[column]"); string result = g.GetSql(); string sql = "[c1], [oh2].[c2], [oh3].[c3], [c4], [t].[column]"; Assert.Equal(sql, result); }
public void GroupByAggregationMany() { SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql; ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter); GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c); g.FuncSum("sm", "asm"); g.FuncMax("mx", "amx"); g.FuncMin("mn", "amn"); g.FuncCount("fg", "acn"); string result1 = g.GetSql(); string sql1 = "[sm], [mx], [mn], [fg]"; string result2 = c.GetSql(); string sql2 = "SUM([sm]) as 'asm', MAX([mx]) as 'amx', MIN([mn]) as 'amn', COUNT([fg]) as 'acn'"; Assert.AreEqual(sql1, result1); Assert.AreEqual(sql2, result2); Assert.AreEqual(c.Count, g.Count); }
public void GroupByAlias2() { SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql; ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter); GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c); g.Append(false, "c1"); g.SetTableAlias("oh2"); g.Append(false, "c2"); g.SetTableAlias("oh3"); g.Append(false, "c3"); g.SetTableAlias(); g.Append(false, "c4"); g.Raw("[t].[column]"); string result = g.GetSql(); string sql = "[c1], [oh2].[c2], [oh3].[c3], [c4], [t].[column]"; Assert.AreEqual(sql, result); }
private void LoadComboBox() { GroupByList.Clear(); GroupThenList.Clear(); foreach (GroupingMode mode in Enum.GetValues(typeof(GroupingMode)).Cast <GroupingMode>()) { GroupThenList.Add(mode); GroupByList.Add(mode); } if (Application.MainDocument.Grids.ActiveSystem == null) { GroupByList.Remove(GroupingMode.GridIntersection); GroupByList.Remove(GroupingMode.Level); GroupThenList.Remove(GroupingMode.GridIntersection); GroupThenList.Remove(GroupingMode.Level); } comboBoxGroupBy.SelectedIndex = 0; comboBoxThenBy.SelectedIndex = 0; }
public Query GroupBy(object ColumnName, bool ColumnNameIsLiteral, SortDirection SortDirection) { if (_ListGroupBy == null) _ListGroupBy = new GroupByList(); _ListGroupBy.Add(new GroupBy(ColumnName, ColumnNameIsLiteral, SortDirection)); return this; }
public Query GroupBy(string ColumnName) { if (_ListGroupBy == null) _ListGroupBy = new GroupByList(); _ListGroupBy.Add(new GroupBy(ColumnName)); return this; }
public void GroupBy(string tableName, string fieldName) { GroupByList.Add(Adapter.Field(tableName, fieldName)); }
public Query GroupBy(string tableName, string columnName) { if (_ListGroupBy == null) _ListGroupBy = new GroupByList(); _ListGroupBy.Add(new GroupBy(tableName, columnName)); return this; }
public Query GroupBy(object value, bool valueIsLiteral, SortDirection sortDirection) { if (_ListGroupBy == null) _ListGroupBy = new GroupByList(); _ListGroupBy.Add(new GroupBy(value, valueIsLiteral, sortDirection)); return this; }
public Query GroupBy(string columnName, SortDirection sortDirection) { if (_ListGroupBy == null) _ListGroupBy = new GroupByList(); _ListGroupBy.Add(new GroupBy(columnName, sortDirection)); return this; }
public override void WriteToStream(IndentStream stream) { stream.Write("SELECT"); if (TopCount != null) { stream.Write(" "); TopCount.WriteToStream(stream); } stream.Write(" "); foreach (var column in Columns.Select((value, idx) => new { value, idx })) { if (column.idx != 0) { stream.Write(", "); } column.value.WriteToStream(stream); } if (IntoTable != null) { stream.WriteLine(); stream.Write("INTO "); IntoTable.WriteToStream(stream); } if (FromSourceList.Count > 0) { stream.WriteLine(); stream.Write("FROM "); stream.Indent++; for (int i = 0; i < FromSourceList.Count; i++) { if (i != 0) { stream.WriteLine(", "); } var fromSource = FromSourceList[i]; fromSource.WriteToStream(stream); } stream.Indent--; } if (ForXmlExpr != null) { stream.WriteLine(); ForXmlExpr.WriteToStream(stream); } if (PivotExpr != null) { stream.WriteLine(); PivotExpr.WriteToStream(stream); } if (WhereExpr != null) { stream.WriteLine(); stream.Write("WHERE "); WhereExpr.WriteToStream(stream); } if (GroupByList.Count > 0) { stream.WriteLine(); stream.Write("GROUP BY "); GroupByList.WriteToStreamWithComma(stream); } if (Having != null) { stream.WriteLine(); Having.WriteToStream(stream); } if (OrderByList.Count > 0) { stream.WriteLine(); stream.Write("ORDER BY "); OrderByList.WriteToStreamWithComma(stream); } if (OptionExpr != null) { stream.WriteLine(); OptionExpr.WriteToStream(stream); } if (UnionSelectList != null && UnionSelectList.Count > 0) { stream.WriteLine(); UnionSelectList.WriteToStream(stream); } if (IsSemicolon) { stream.Write(" ;"); } }
public Query GroupBy(string TableName, string ColumnName, SortDirection SortDirection) { if (_ListGroupBy == null) _ListGroupBy = new GroupByList(); _ListGroupBy.Add(new GroupBy(TableName, ColumnName, SortDirection)); return this; }
private Query GroupBy(GroupBy GroupBy) { if (_ListGroupBy == null) _ListGroupBy = new GroupByList(); _ListGroupBy.Add(GroupBy); return this; }
public CashierOrdersViewModel(IMessageBoxService messageBoxService, IUIVisualizerService uiVisualizerService, IOrderManager orderManager, ITableManager tableManager, IReportOrderItemEditionManager reportOrderEdition) : base(messageBoxService, uiVisualizerService, orderManager) { // do initialization try { if (tableManager == null) { throw new ArgumentNullException("tableManager"); } _tableManager = tableManager; this.DisplayName = "Orders Management"; TableList = new ObservableCollection <Table>(_tableManager.GetList().OrderBy(o => o.State)); if (reportOrderEdition == null) { throw new ArgumentNullException("reportOrderEdition"); } _reportOrderEdition = reportOrderEdition; // initialize command this.RefreshTablesCommand = new CommandBase <Table>(o => this.ExecuteRefreshTablesCommand(o)); this.ShowCancelledProductCommand = new CommandBase <object>(o => this.ExecuteShowCancelledProductCommand()); this.SelectGroupByCommand = new CommandBase <object>(o => this.ExecuteSelectGroupByCommand()); this.CheckoutCommand = new CommandBase <Order>(o => this.ExecuteCheckoutCommand(o), o => this.CanExecuteCheckoutCommand(o)); this.CancelCommand = new CommandBase <Order>(o => this.ExecuteCancelCommand(o), o => this.CanExecuteCancelCommand(o)); this.BillCommand = new CommandBase <Order>(o => this.ExecuteBillCommand(o), o => this.CanExecuteBillCommand(o)); this.PrintCommand = new CommandBase <Order>(o => this.ExecutePrintCommand(o), o => this.CanExecutePrintCommand(o)); this.SelectDiscountCommand = new CommandBase <Order>(o => this.ExecuteSelectDiscountCommand(o), o => this.CanExecuteSelectDiscountCommand(o)); this.ServiceChargeCommand = new CommandBase <Order>(o => this.ExecuteServiceChargeCommand(o), o => this.CanExecuteServiceChargeCommand(o)); this.EnableVatCommand = new CommandBase <Order>(o => this.ExecuteEnableVatCommand(o), o => this.CanExecuteEnableVatCommand(o)); // available discount list DiscountList = new List <Discount>(); DiscountList.Add(new Discount("0%", 0)); DiscountList.Add(new Discount("5%", 5)); DiscountList.Add(new Discount("10%", 10)); DiscountList.Add(new Discount("15%", 15)); DiscountList.Add(new Discount("20%", 20)); DiscountList.Add(new Discount("25%", 25)); DiscountList.Add(new Discount("30%", 30)); GroupByList.Add(new ReportGroupBy("Thu tu goi", "EditionInfo")); GroupByList.Add(new ReportGroupBy("Mon an", "ProductName")); SelectedGroupBy = GroupByList[0]; //Mediator.Instance.RegisterHandler<T>("Updated" + typeof(T).Name + "Successfully", HandleReceivedMessage); //Mediator.Instance.RegisterHandler<T>("Created" + typeof(T).Name + "Successfully", HandleReceivedMessage); Mediator.Instance.Register(this); if (SelectedItem != null) { (ModelManager as IOrderManager).FetchOrderItems(SelectedItem); CollectionViewSource.GetDefaultView(SelectedItem.OrderItems).Filter = o => IsShowCancelledProduct || ((OrderItem)o).IsCancelled == false; ReportEditionOrderItemList = _reportOrderEdition.GetEdititonReportOfOrder(SelectedItem); CollectionView view = (CollectionView)CollectionViewSource.GetDefaultView(ReportEditionOrderItemList); PropertyGroupDescription groupDescription = new PropertyGroupDescription(SelectedGroupBy.PropertyName); view.GroupDescriptions.Clear(); view.GroupDescriptions.Add(groupDescription); } } catch (Exception ex) { this.MessageBoxService.ShowError(this.GetType().FullName + System.Reflection.MethodBase.GetCurrentMethod().Name + ": " + ex.Message); } }