public void Exec_AggregateAvg()
{
const double expected = 55.5922;
// overload #1
double result = new
Select(Aggregate.Avg("UnitPrice"))
.From(Product.Schema)
.ExecuteScalar <double>();
Assert.AreEqual(expected, result);
// overload #2
result = new
Select(Aggregate.Avg(Product.UnitPriceColumn))
.From(Product.Schema)
.ExecuteScalar <double>();
Assert.AreEqual(expected, result);
// overload #3
result = new
Select(Aggregate.Avg("UnitPrice", "AverageUnitPrice"))
.From(Product.Schema)
.ExecuteScalar <double>();
Assert.AreEqual(expected, result);
// overload #4
result = new
Select(Aggregate.Avg(Product.UnitPriceColumn, "AverageUnitPrice"))
.From(Product.Schema)
.ExecuteScalar <double>();
Assert.AreEqual(expected, result);
}
public void Exec_AggregateWithWhereNotHaving()
{
int records = new
Select(Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"))
.From("Order Details")
.Where("Quantity").IsEqualTo(120)
.GetRecordCount();
Assert.AreEqual(7, records);
}
public void Exec_SingleAggregateWithWhere()
{
int records = new
Select(Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"))
.From("Order Details")
.Where(Aggregate.Avg("UnitPrice"))
.IsGreaterThan(50)
.GetRecordCount();
Assert.AreEqual(7, records);
}
public void Exec_SimpleOr3()
{
int records = new
Select(Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"), Aggregate.Avg("Quantity"))
.From("Order Details")
.Where(Aggregate.Avg("UnitPrice"))
.IsGreaterThan(50)
.Or(Aggregate.Avg("Quantity"))
.IsGreaterThan(30)
.GetRecordCount();
Assert.AreEqual(9, records);
}
public void Acc_Exec_SimpleOr3()
{
DataProvider provider = DataService.GetInstance("NorthwindAccess");
int records = new
Select(provider, Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"), Aggregate.Avg("Quantity"))
.From("Order Details")
.Where(Aggregate.Avg("UnitPrice"))
.IsGreaterThan(50)
.Or(Aggregate.Avg("Quantity"))
.IsGreaterThan(30)
.GetRecordCount();
Assert.AreEqual(10, records);
}
public void Exec_SimpleAnd3()
{
SubSonic.SqlQuery query = new
Select(Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"), Aggregate.Avg("Quantity"))
.From("Order Details");
query
.Where(Aggregate.Avg("UnitPrice"))
.IsGreaterThan(30)
.And(Aggregate.Avg("Quantity"))
.IsGreaterThan(20);
int records = query.GetRecordCount();
Assert.AreEqual(16, records);
}
public void Acc_Exec_SimpleAnd3()
{
DataProvider provider = DataService.GetInstance("NorthwindAccess");
SubSonic.SqlQuery query = new
Select(provider, Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"), Aggregate.Avg("Quantity"))
.From("Order Details");
query
.Where(Aggregate.Avg("UnitPrice"))
.IsGreaterThan(30)
.And(Aggregate.Avg("Quantity"))
.IsGreaterThan(20);
int records = query.GetRecordCount();
string s = query.BuildSqlStatement();
Assert.AreEqual(17, records);
}
//end eta charts
protected void bind_metrics()
{
string _contactid = ((UserClass)Page.Session["user"]).UserId.ToString();
IList <string> _deliveryids = null;
_deliveryids = wwi_func.array_from_xml("xml\\contact_iso.xml", "contactlist/contact[id='" + _contactid + "']/deliveryids/deliveryid/value");
if (_deliveryids.Count == 0)
{
string _companyid = ((UserClass)Page.Session["user"]).CompanyId.ToString();
_deliveryids.Add(_companyid);
}
//object[] _in = { 406, 12346, 13776 };
DateTime _dx = DateTime.Now.AddMonths(-1);
DataTable _dt = new Select(Aggregate.Count(DAL.Logistics.ContainerTable.ContainerIDColumn),
Aggregate.Avg(DAL.Logistics.OrderTable.NumberOfPackagesColumn),
Aggregate.Min(DAL.Logistics.OrderTable.NumberOfPackagesColumn),
Aggregate.Max(DAL.Logistics.OrderTable.NumberOfPackagesColumn),
Aggregate.GroupBy(DAL.Logistics.OrderTable.EtsColumn))
.From(DAL.Logistics.Tables.DeliverySubTable)
.InnerJoin(DAL.Logistics.ContainerSubTable.OrderNumberColumn, DAL.Logistics.DeliverySubTable.OrderNumberColumn)
.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
.InnerJoin(DAL.Logistics.OrderTable.OrderIDColumn, DAL.Logistics.ContainerSubTable.OrderIDColumn)
.InnerJoin(DAL.Logistics.NameAndAddressBook.CompanyIDColumn, DAL.Logistics.OrderTable.CompanyIDColumn)
.Where(DAL.Logistics.DeliverySubTable.DeliveryAddressColumn).In(_deliveryids).And(DAL.Logistics.OrderTable.EtsColumn).IsGreaterThanOrEqualTo(_dx)
.ExecuteDataSet().Tables[0];
Series _s = this.dxchartMetrics.Series["Lowest"]; //define series
_s.DataSource = _dt;
//columns
_s.ArgumentScaleType = ScaleType.Qualitative;
_s.ArgumentDataMember = "GroupByOfETS";
XYDiagram _xy = (XYDiagram)this.dxchartMetrics.Diagram;
this.dxchartMetrics.Legend.Visible = true;
//stagger x axis lables
_xy.AxisX.Label.Staggered = false;
//rotate them.
_xy.AxisX.Label.Angle = -30;
_xy.AxisX.Label.Antialiasing = true;
//rows
_s.ValueScaleType = ScaleType.Numerical;
_s.ValueDataMembers.AddRange(new string[] { "MinOfNumberOfPackages" });
_s = this.dxchartMetrics.Series["Average"]; //define series
_s.DataSource = _dt;
//columns
_s.ArgumentScaleType = ScaleType.Qualitative;
_s.ArgumentDataMember = "GroupByOfETS";
//rows
_s.ValueScaleType = ScaleType.Numerical;
_s.ValueDataMembers.AddRange(new string[] { "AvgOfNumberOfPackages" });
_s = this.dxchartMetrics.Series["Highest"]; //define series
_s.DataSource = _dt;
//columns
_s.ArgumentScaleType = ScaleType.Qualitative;
_s.ArgumentDataMember = "GroupByOfETS";
//rows
_s.ValueScaleType = ScaleType.Numerical;
_s.ValueDataMembers.AddRange(new string[] { "MaxOfNumberOfPackages" });
this.dxchartMetrics.DataBind();
}