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